
    i[/                     H    S SK r S SKJr  S rS rS rS rS rS rS	 r	S
 r
g)    N)get_connectionc                 8   Sn [        5       nU(       d  [        S5      eUR                  5       nUR                  X5        UR	                  5       nU Vs/ s H
  nSUS   0PM     snU(       a  UR                  5         $ $ s  snf ! [         a2  n[        SU 35        / s SnAW(       a  UR                  5         $ $ SnAf[         a2  n[        SU 35        / s SnAW(       a  UR                  5         $ $ SnAff = f! W(       a  UR                  5         f f = f)z'
Obtiene los correos de los empleados.
z?
       SELECT Correos FROM CorreosdeModulos WHERE  ID = ?
    4   No se pudo establecer conexión con la base de datosCorreosr      [Error de conexión] Nz:[Error inesperado] Error obteniendo correos de empleados: r   ConnectionErrorcursorexecutefetchallcloseprint	Exception)idqueryconnr
   resultsrowcees           YC:\Users\victor.barrera\Documents\proyectos\elepV3\Elep\src\Consultas_SQL\registration.pyget_Correo_Solicitud_de_altar      s    E!"XYYu#//#/67wCF#w7 JJL  8 %bT*+	 JJL   J1#NO	 JJL 
 JJL sZ   AB B&B B 
C<B=C< C? =C<
C7C<C? 7C<<C? ?Dc                  >   Sn  [        5       nU(       d  [        S5      eUR                  5       nUR                  U 5        UR	                  5       nU Vs/ s H  oDS   US   S.PM     snU(       a  UR                  5         $ $ s  snf ! [         a2  n[        SU 35        / s S nAW(       a  UR                  5         $ $ S nAf[         a2  n[        SU 35        / s S nAW(       a  UR                  5         $ $ S nAff = f! W(       a  UR                  5         f f = f)Nz&SELECT CompanyID, Company FROM Companyr   r      )	CompanyIDCompanyr   1   [Error inesperado] Error obteniendo compañías: r   r   r   r
   r   r   r   r   s          r   get_companiesr   %   s    4E!"XYYu//# ELLGS!fQ8GL JJL  M %bT*+	
 JJL 	  A!EF	JJL 	 JJL Z   AB	 B)B	 B	 	
C?C "C?#D  C?C:C?D :C??D Dc                  >   Sn  [        5       nU(       d  [        S5      eUR                  5       nUR                  U 5        UR	                  5       nU Vs/ s H  oDS   US   S.PM     snU(       a  UR                  5         $ $ s  snf ! [         a2  n[        SU 35        / s SnAW(       a  UR                  5         $ $ SnAf[         a2  n[        SU 35        / s SnAW(       a  UR                  5         $ $ SnAff = f! W(       a  UR                  5         f f = f)	ud   
Obtiene las compañías desde la base de datos.
Retorna una lista de diccionarios con ID y Nombre.
zCSELECT RequestTypeID, RequestType FROM RequestType WHERE Active = 1r   r   r   )RequestTypeIDRequestTyper   Nr   r   r   s          r   get_RequestTyper$   <   s   
 RE!"XYYu//# MTTGSa&Q@GT JJL  U %bT*+	 JJL   A!EF	 JJL 
 JJL r    c                 @   Sn [        5       nU(       d  [        S5      eUR                  5       nUR                  X45        UR	                  5       nU Vs/ s H  oUS   US   S.PM     snU(       a  UR                  5         $ $ s  snf ! [         a2  n[        SU 35        / s SnAW(       a  UR                  5         $ $ SnAf[         a2  n[        SU 35        / s SnAW(       a  UR                  5         $ $ SnAff = f! W(       a  UR                  5         f f = f)	s   
Obtiene las divisiones asociadas a una compañía específica.
Retorna una lista de diccionarios con ID y Nombre.
zWSELECT DivisionID, Division FROM Division WHERE CompanyID = ? AND DivisionID != 'CLIE' r   r   r   r   namer   N0[Error inesperado] Error obteniendo divisiones: r   )
company_idr   r   r
   r   r   r   r   s           r   get_divisions_by_companyr+   X   s    
 fE!"XYYum,//#:AB'31vs1v.'B JJL  C %bT*+	 JJL   @DE	 JJL 
 JJL sZ   AB
 B*B
 B
 

D C#D $D D C;D D ;D  D Dc                 :   Sn [        5       nU(       d  [        S5      eUR                  5       nUR                  X45        UR	                  5       nU Vs/ s H
  nSUS   0PM     snU(       a  UR                  5         $ $ s  snf ! [         a2  n[        SU 35        / s SnAW(       a  UR                  5         $ $ SnAf[         a2  n[        SU 35        / s SnAW(       a  UR                  5         $ $ SnAff = f! W(       a  UR                  5         f f = f)r&   z3SELECT  Division FROM Division WHERE DivisionID = ?r   Divisionr   r   Nr)   r   )
Divisionidr   r   r
   r   r   r   r   s           r   get_divisions_by_idr/   t   s    
 BE!"XYYum,//#078SV$8 JJL  9 %bT*+	 JJL   @DE	 JJL 
 JJL sZ   AB B'B B 
C=B> C=!D  >C=C8C=D  8C==D   Dc                 B   Sn [        5       nU(       d  [        S5      eUR                  5       nUR                  X U45        UR	                  5       nU Vs/ s H  ofS   US   S.PM     snU(       a  UR                  5         $ $ s  snf ! [         a2  n[        SU 35        / s SnAW(       a  UR                  5         $ $ SnAf[         a2  n[        SU 35        / s SnAW(       a  UR                  5         $ $ SnAff = f! W(       a  UR                  5         f f = f)	ut   
Obtiene los departamentos filtrados por compañía y división.
Retorna una lista de diccionarios con ID y Nombre.
zy
        SELECT DepartamentID, Departament 
        FROM Departament 
        WHERE CompanyID = ? AND DivisionID = ?
    r   r   r   r'   r   Nz3[Error inesperado] Error obteniendo departamentos: r   )	r*   division_idr   r   r
   r   r   r   r   s	            r   'get_departments_by_company_and_divisionr2      s   
E
!"XYYu;78//#:AB'31vs1v.'B JJL  C %bT*+	 JJL   CA3GH	 JJL 
 JJL sZ   AB B+B B 
DC$D%D DC<DD <DD Dc                    USS.nUS:X  a  UR                  U R                  SS5      U R                  SS5      U R                  SS5      U R                  SS5      U R                  S	S5      U R                  S
S5      U R                  SS5      U R                  SS5      U R                  SS5      U R                  SS5      U R                  SS5      U R                  S5      S;   a  SOSS.5        U$ US;   a  UR                  U R                  SS5      U R                  SS5      U R                  SS5      U R                  SS5      U R                  SS5      U R                  SS5      U R                  SS5      U R                  SS5      U R                  SS5      U R                  SS5      U R                  S 5      S;   a  SOSS!.5        U$ US":X  Ga  UR                  U R                  S#S5      U R                  S$S5      U R                  S%S5      U R                  S&S5      U R                  S'S5      U R                  S(S5      U R                  S)S5      U R                  S*S5      U R                  S+S5      U R                  S,S5      U R                  S-S5      U R                  S.S5      U R                  S/S5      U R                  S0S5      U R                  S15      S;   a  SOSS2.5        U$ )3uI   
Prepara los datos del formulario para la inserción en la base de datos
	PENDIENTE)r"   Statusu   DISMÉXMX_BusinessName MX_LegalRepresentativeMX_PrimaryContactMX_ContactEmailMX_FiscalAddressMX_PostalCodeMX_StateMX_CityMX_RFCMX_ContactPhoneMX_CommentsMX_Terms)true1onTr   r   )BusinessNameLegalRepresentativePrimaryContactContactEmailFiscalAddress
PostalCodeStateCityRFCContactPhoneCommentsTerms)DISEUADISCANEU_BusinessNameEU_LegalRepresentativeEU_PrimaryContactEU_ContactEmailEU_FiscalAddressEU_PostalCodeEU_StateEU_CityEU_ContactPhoneEU_CommentsEU_Terms)rF   rG   rH   rI   rJ   rK   rL   rM   rO   rP   rQ   EMPLEADOEMPFirstNameEMPMiddleNameEMPLastNameEMPSecondLastNameEMPCorporateEmailEMPContactPhoneEMPEmployeeNumberEMPCompanyIDEMPDivisionIDEMPDepartamentIDEMPPositionEMPDirectSupervisorEMPPermissionTypeEMPCommentsEMPTerms)	FirstName
MiddleNameLastNameSecondLastNameCorporateEmailrO   EmployeeNumberr   
DivisionIDDepartamentIDPositionDirectSupervisorPermissionTyperP   rQ   )updateget)datarequest_typeprocessed_datas      r   prepare_form_datar      s    &N
 y  HH%6;#'88,Db#I"hh':B? HH%6;!XX&8"=((?B7XXj"-HHY+88Hb) HH%6;3((:.2KKQQR
 	j M 
-	- HH%6;#'88,Db#I"hh':B? HH%6;!XX&8"=((?B7XXj"-HHY+ HH%6;3((:.2KKQQR
 	H - 
	#."5((?B73"hh':B?"hh':B? HH%6;"hh':B?."5((?B7!XX&8"=3 $)> C"hh':B?3((:.2KKQQR
 	(     c                     [        5       nU(       d  [        S5      eUR                  5       n/ n/ n/ n/ SQnU R                  5        H  u  pxUS:w  d  M  Uc  M  UR	                  U5        [        U[        5      (       a5  Xv;  a0  UR                  5       R                  5       n	UR	                  U	5        OUR	                  U5        UR	                  S5        M     UR	                  S5        UR	                  S5        SS	R                  U5       S
S	R                  U5       S3n
UR                  X5        UR                  S5        UR                  5       S   nUR                  5         SU(       a  UR                  5         $ $ ! [         a@  n[        SU 35        SS[        U5       34s SnAW(       a  UR                  5         $ $ SnAf[        R                    a@  n[        SU 35        SS[        U5       34s SnAW(       a  UR                  5         $ $ SnAf["         a@  n[        SU 35        SS[        U5       34s SnAW(       a  UR                  5         $ $ SnAf[$         a@  n[        SU 35        SS[        U5       34s SnAW(       a  UR                  5         $ $ SnAff = f! W(       a  UR                  5         f f = f)u   
Inserta una nueva solicitud en la tabla UserRequests.
Params:
    request_data: diccionario con los datos del formulario
Returns:
    tuple: (bool, str) - (éxito, mensaje)
r   )rs   r"   r   ru   rv   r5   	RequestIDN?	CreatedAtz8SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time'z#
        INSERT INTO UserRequests (z, z)
        VALUES (z
)
        zSELECT @@IDENTITYr   r   Fu   Error de conexión: z[Error de integridad] zError: u   [Error de validación] u   Error de validación: z/[Error inesperado] Error insertando solicitud: zError inesperado: )Tz"Solicitud registrada exitosamente )r   r	   r
   itemsappend
isinstancestrstrip
capitalizejoinr   fetchonecommitr   r   pyodbcIntegrityError
ValueErrorr   )request_datar   r
   fieldsvaluesplaceholdersexclude_formattingfieldvalueformatted_valuer   new_idr   iever   s                   r   insert_user_requestr      s   C!"XYY  w )..0LE#(9e$ eS))e.M&+kkm&>&>&@OMM/2MM%(##C( 1  	k"VW##'99V#4"5 6<() *	 	u% 	*+"1%: JJL   7%bT*+,SWI666 JJL     *&rd+,By))) JJL   9't,-.s2wi888
 JJL 	  4?sCD*3q6(333JJL 	4 JJL s   AF F DF 
J/G*J/+J2 J/H<J/=J2 J/'I"J/J2 "J//J*J/J2 *J//J2 2K)r   Consultas_SQL.conexionr   r   r   r$   r+   r/   r2   r   r    r   r   <module>r      s9     1<.888BAFKr   