a
    0i[/                     @   sX   d dl Z d dlmZ dd Zdd Zdd Zd	d
 Zdd Zdd Zdd Z	dd Z
dS )    N)get_connectionc              
   C   s  d} zzLt  }|std| }|||  | }dd |D W W |rT|  S  ty } z,td|  g W  Y d}~W |r|  S d}~0  ty } z,td|  g W  Y d}~W |r|  S d}~0 0 W | r|  n| r|  0 dS )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 datosc                 S   s   g | ]}d |d iqS )Correosr    .0rowr   r   //var/www/html/src/Consultas_SQL/registration.py
<listcomp>       z0get_Correo_Solicitud_de_alta.<locals>.<listcomp>   [Error de conexión] Nz:[Error inesperado] Error obteniendo correos de empleados: r   ConnectionErrorcursorexecutefetchallcloseprint	Exception)idqueryconnr   resultsceer   r   r	   get_Correo_Solicitud_de_alta   s8    	

r   c               
   C   s   d}  zzJt  }|std| }||  | }dd |D W W |rR|  S  ty } z,td|  g W  Y d }~W |r|  S d }~0  ty } z,td|  g W  Y d }~W |r|  S d }~0 0 W | r|  n| r|  0 d S )Nz&SELECT CompanyID, Company FROM Companyr   c                 S   s   g | ]}|d  |d dqS )r      )	CompanyIDCompanyr   r   r   r   r	   r
   1   r   z!get_companies.<locals>.<listcomp>r   1   [Error inesperado] Error obteniendo compañías: r   r   r   r   r   r   r   r   r   r	   get_companies%   s8    


r!   c               
   C   s   d}  zzJt  }|std| }||  | }dd |D W W |rR|  S  ty } z,td|  g W  Y d}~W |r|  S d}~0  ty } z,td|  g W  Y d}~W |r|  S d}~0 0 W | r|  n| r|  0 dS )up   
    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   c                 S   s   g | ]}|d  |d dqS )r   r   )RequestTypeIDRequestTyper   r   r   r   r	   r
   L   r   z#get_RequestType.<locals>.<listcomp>r   Nr   r   r    r   r   r	   get_RequestType<   s8    
	

r$   c              
   C   s  d} zzNt  }|std| }||| f | }dd |D W W |rV|  S  ty } z,td|  g W  Y d}~W |r|  S d}~0  ty } z,td|  g W  Y d}~W |r|  S d}~0 0 W |r |  n| r|  0 d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   c                 S   s   g | ]}|d  |d dqS r   r   )r   namer   r   r   r   r	   r
   h   r   z,get_divisions_by_company.<locals>.<listcomp>r   N0[Error inesperado] Error obteniendo divisiones: r   )
company_idr   r   r   r   r   r   r   r   r	   get_divisions_by_companyX   s8    	

r*   c              
   C   s  d} zzNt  }|std| }||| f | }dd |D W W |rV|  S  ty } z,td|  g W  Y d}~W |r|  S d}~0  ty } z,td|  g W  Y d}~W |r|  S d}~0 0 W |r |  n| r|  0 dS )r%   z3SELECT  Division FROM Division WHERE DivisionID = ?r   c                 S   s   g | ]}d |d iqS )Divisionr   r   r   r   r   r	   r
      r   z'get_divisions_by_id.<locals>.<listcomp>r   Nr(   r   )Z
Divisionidr   r   r   r   r   r   r   r   r	   get_divisions_by_idt   s8    	

r,   c              
   C   s  d} zzPt  }|std| }||| |f | }dd |D W W |rX|  S  ty } z,td|  g W  Y d}~W |r|  S d}~0  ty } z,td|  g W  Y d}~W |r|  S d}~0 0 W |r|  n|r |  0 dS )u   
    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   c                 S   s   g | ]}|d  |d dqS r&   r   r   r   r   r	   r
      r   z;get_departments_by_company_and_division.<locals>.<listcomp>r   Nz3[Error inesperado] Error obteniendo departamentos: r   )r)   division_idr   r   r   r   r   r   r   r   r	   'get_departments_by_company_and_division   s8    	

r.   c                 C   s  |dd}|dkr| | dd| dd| dd| dd| d	d| d
d| dd| dd| dd| dd| dd| ddv rdndd nJ|dv r6| | dd| dd| dd| dd| dd| dd| dd| dd| dd| dd| d dv r*dndd! n|d"kr| | d#d| d$d| d%d| d&d| d'd| d(d| d)d| d*d| d+d| d,d| d-d| d.d| d/d| d0d| d1dv rdndd2 |S )3uQ   
    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)rA   rB   rC   rD   rE   rF   rG   rH   rJ   rK   rL   EMPLEADOEMPFirstNameEMPMiddleNameEMPLastNameEMPSecondLastNameEMPCorporateEmailEMPContactPhoneEMPEmployeeNumberEMPCompanyIDEMPDivisionIDEMPDepartamentIDEMPPositionEMPDirectSupervisorEMPPermissionTypeEMPCommentsEMPTerms)	FirstName
MiddleNameLastNameSecondLastNameCorporateEmailrJ   EmployeeNumberr   
DivisionIDDepartamentIDPositionDirectSupervisorPermissionTyperK   rL   )updateget)datarequest_typeprocessed_datar   r   r	   prepare_form_data   sf    





































rz   c              
   C   s  ztz
t  }|std| }g }g }g }g d}|  D ]`\}}|dkr>|dur>|| t|tr||vr|  }	||	 n
|| |d q>|d |d dd		| d
d		| d}
|
|
| |
d | d }|  dW W |r|  S  tyf } z<td|  ddt| fW  Y d}~W |r\|  S d}~0  tjy } z<td|  ddt| fW  Y d}~W |r|  S d}~0  ty } z<td|  ddt| fW  Y d}~W |r|  S d}~0  tyd } z<td|  ddt| fW  Y d}~W |rZ|  S d}~0 0 W |r|  n|r|  0 dS )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   )rn   r"   r   rp   rq   r0   	RequestIDN?	CreatedAtz8SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time'z#
        INSERT INTO UserRequests (z, z)
        VALUES (z
)
        zSELECT @@IDENTITYr   T"Solicitud registrada exitosamente 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: )Tr~   )r   r   r   itemsappend
isinstancestrstrip
capitalizejoinr   fetchonecommitr   r   pyodbcIntegrityError
ValueErrorr   )request_datar   r   fieldsvaluesplaceholdersZexclude_formattingfieldvalueZformatted_valuer   new_idr   Ziever   r   r   r	   insert_user_request   sx    








r   )r   Consultas_SQL.conexionr   r   r!   r$   r*   r,   r.   rz   r   r   r   r   r	   <module>   s   !C