o
    ÿ¡f´B  ã                   @   s.   d dl mZ d dlZd dlZG dd„ dƒZdS )é    )ÚrequestsNc                   @   s¬   e Zd Zd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d„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zdd„ Zd d!„ Zed"d#„ ƒZd$d%„ Zd&d'„ ZdS )(ÚIncidentConnectionNc                 C   s(   d| _ d| _d| _d| _d| _d| _d S )Nzhawa-testdbÚhawaz1RokDXC71E$y!z172.206.54.217Ú5432z-c search_path=hawa_consume)ÚdbnameÚuserÚpasswordÚhostÚportÚschema©Úself© r   ú3/opt/lhia/hawa/python/models/incident_connection.pyÚ__init__   s   
zIncidentConnection.__init__c              
   C   s`   zt j| j| j| j| j| j| jd}|W S  t jy/ } zt	d|› ƒ W Y d}~dS d}~ww )u,   Abre una nueva conexiÃ³n a la base de datos.)r   r   r   r	   r
   Úoptionsu   Error de conexiÃ³n: N)
Úpsycopg2Úconnectr   r   r   r	   r
   r   ÚOperationalErrorÚprint)r   ÚconnÚerrr   r   r   Úopen_connection   s   ú€þz"IncidentConnection.open_connectionc                 C   s   t dƒ |r| ¡  d S d S )Nu'   Cierra la conexiÃ³n a la base de datos.)r   Úclose)r   r   r   r   r   Úclose_connection    s   ÿz#IncidentConnection.close_connectionc                 C   sr   t dƒ |  ¡ }|d ur7| ¡ }| d¡ |jdkr| ¡ ng }|  |¡ |W  d   ƒ S 1 s2w   Y  g S )Nz/Ejecuta una consulta y devuelve los resultados.z SELECT * FROM "incidentes" r   )r   r   ÚcursorÚexecuteÚrowcountÚfetchallr   ©r   r   ÚcurÚdatar   r   r   Úread_all%   s   


 üzIncidentConnection.read_allc                 C   sv   |   ¡ }|d ur9| ¡ %}| d| ¡ d f¡ |jdkr!| ¡ ng }|  |¡ |W  d   ƒ S 1 s4w   Y  g S )Nz7SELECT * FROM "tickets_by_sla" WHERE upper(sla) LIKE %sú%r   )r   r   r   Úupperr   r   r   )r   Ú
sla_prefixr   r    r!   r   r   r   Úread_all_tickets_by_sla1   s   

 üz*IncidentConnection.read_all_tickets_by_slac                 C   ój   |   ¡ }|d ur3| ¡ }| d¡ |jdkr| ¡ ng }|  |¡ |W  d   ƒ S 1 s.w   Y  g S )NzSELECT * FROM "tickets_by_sla" r   ©r   r   r   r   r   r   r   r   r   r   Úread_all_tickets_noparams_sla;   ó   


 üz0IncidentConnection.read_all_tickets_noparams_slac                 C   r'   )Nz#SELECT * FROM tickets_by_sla_c_t_s r   r(   r   r   r   r   Ú$read_all_tickets_no_params_sla_c_t_sE   r*   z7IncidentConnection.read_all_tickets_no_params_sla_c_t_sc              	   C   sv   |   ¡ }|d ur9| ¡ %}| d|||||f¡ |jdkr!| ¡ ng }|  |¡ |W  d   ƒ S 1 s4w   Y  g S )NaP  SELECT
                            i.categoria,
                            i.tipo,
                            SUM(CASE WHEN EXTRACT(YEAR FROM i.fecha_de_registro) = %s THEN 1 ELSE 0 END) AS incidentes_2019,
                            SUM(CASE WHEN EXTRACT(YEAR FROM i.fecha_de_registro) = %s THEN 1 ELSE 0 END) AS incidentes_2020
                        FROM
                            incidentes i
                        WHERE
                            EXTRACT(MONTH FROM i.fecha_de_registro) = %s
                            AND (EXTRACT(YEAR FROM i.fecha_de_registro) = %s OR EXTRACT(YEAR FROM i.fecha_de_registro) = %s)
                        GROUP BY
                            i.categoria,
                            i.tipo
                        ORDER BY
                            i.categoria,
                            i.tipor   r(   )r   Úanio1Úanio2Úmesr   r    r!   r   r   r   Úread_ticket_rep_and_comp_yearP   s   
ð
 ìz0IncidentConnection.read_ticket_rep_and_comp_yearc                 C   r'   )Nz^SELECT *
                        FROM
                            tickets_cad_y_nocad_by_aniosr   r(   r   r   r   r   Úread_ticket_cad_y_nocad_yeark   s   


 úz/IncidentConnection.read_ticket_cad_y_nocad_yearc                 C   sv   |   ¡ }|d ur9| j ¡ $}| d||||f¡ |jdkr!| ¡ ng }|  |¡ |W  d   ƒ S 1 s4w   Y  g S )Na»  SELECT tipo, titulo,
                              SUM(CASE WHEN EXTRACT(YEAR FROM fecha_de_registro) = %s THEN 1 ELSE 0 END) AS cantidad_tickets_2019,
                              SUM(CASE WHEN EXTRACT(YEAR FROM fecha_de_registro) = %s THEN 1 ELSE 0 END) AS cantidad_tickets_2020
                       FROM incidentes
                       WHERE EXTRACT(YEAR FROM fecha_de_registro) IN (%s, %s)
                       GROUP BY tipo, titulor   )r   r   r   r   r   r   r   )r   r,   r-   r   r    r!   r   r   r   Úread_tendencia_by_tipoy   s   
ú
 öz)IncidentConnection.read_tendencia_by_tipoc                 C   r'   )Nz.SELECT * FROM view_tendencia_tickets_all_aniosr   r(   r   r   r   r   Ú!read_tendencias_tickets_all_aniosŠ   r*   z4IncidentConnection.read_tendencias_tickets_all_aniosc                 C   r'   )Nz"select * from vista_calificacionesr   r(   r   r   r   r   Úread_all_califications•   r*   z)IncidentConnection.read_all_calificationsc                 C   sn   |   ¡ }|d ur5| ¡ !}| d|f¡ |jdkr| ¡ ng }|  |¡ |W  d   ƒ S 1 s0w   Y  g S )Nz/SELECT * FROM "incidentes" WHERE categoria = %sr   r(   )r   ÚcategoryNamer   r    r!   r   r   r   Úread_by_categoryŸ   s   

 üz#IncidentConnection.read_by_categoryc                 C   s¢   |   ¡ }|d urO|d }| d¡}| ¡ 2}| dd|d  ¡  d d|d  ¡  d f¡ |jdkr7| ¡ ng }|  |¡ |W  d   ƒ S 1 sJw   Y  g S )Nú zˆSELECT * FROM vista_promedio_tiempo_resolucion WHERE upper(tecnico_de_segunda_linea) LIKE %s AND upper(tecnico_de_segunda_linea) LIKE %sr#   r   é   )r   Úsplitr   r   r$   r   r   r   )r   Útechnician_namer   Únombresr    r!   r   r   r   Úread_by_technician«   s   

0
 üz%IncidentConnection.read_by_technicianc                 C   r'   )Nz%SELECT * FROM incidentes_summary_viewr   r(   r   r   r   r   Úread_total_incidents_by_group¸   r*   z0IncidentConnection.read_total_incidents_by_groupc                 C   r'   )Nz&select * from sla_cads_nocads_percentsr   r(   r   r   r   r   Úread_sla_cads_nocads_percentsÂ   r*   z0IncidentConnection.read_sla_cads_nocads_percentsc                 C   s$   dd|› i}t j| |d}| ¡ S )NÚAuthorizationzBearer )Úheaders)r   ÚgetÚjson)ÚurlÚtokenr?   Úresponser   r   r   Úget_data_in_urlÍ   s   z"IncidentConnection.get_data_in_urlc                 C   sF   | j  ¡ }| d|¡ | j  ¡  W d   ƒ d S 1 sw   Y  d S )Nz>INSERT INTO "incident"(name,phone) VALUES(%(name)s, %(phone)s))r   r   r   Úcommit)r   r!   r    r   r   r   ÚwriteÕ   s   "þzIncidentConnection.writec                 C   s   | j  ¡  d S )N)r   r   r   r   r   r   Ú__def__Ú   s   zIncidentConnection.__def__)Ú__name__Ú
__module__Ú__qualname__r   r   r   r   r"   r&   r)   r+   r/   r0   r1   r2   r3   r5   r;   r<   r=   ÚstaticmethodrE   rG   rH   r   r   r   r   r      s,    




r   )Úfastapir   r   r   r   r   r   r   Ú<module>   s    