B
    2Bf¬  ã               @   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edd„ ƒZd d!„ Zd"d#„ ZdS )$ÚIncidentConnectionNc          
   C   sX   yt jddddddd| _W n6 t jk
rR } zt|ƒ | j ¡  W d d }~X Y nX d S )NZtcsdbZtcsadminztcsadmin.2012z198.251.74.100Z5432z-c search_path=hawa_consume)ZdbnameÚuserÚpasswordÚhostÚportÚoptions)Úpsycopg2ÚconnectÚconnZOperationalErrorÚprintÚclose)ÚselfÚerr© r   úbC:\Users\usuario\Documents\Python Projects\pythonProject\fastapiHawa\models\incident_connection.pyÚ__init__   s
    zIncidentConnection.__init__c          	   C   s:   | j  ¡ &}| d¡ |jdkr(| ¡ ng }|S Q R X d S )Nz SELECT * FROM "incidentes" r   )r   ÚcursorÚexecuteÚrowcountÚfetchall)r   ÚcurÚdatar   r   r   Úread_all   s    
zIncidentConnection.read_allc          	   C   sF   | j  ¡ 2}| d| ¡ d f¡ |jdkr4| ¡ ng }|S Q R X d S )Nz7SELECT * FROM "tickets_by_sla" WHERE upper(sla) LIKE %sú%r   )r   r   r   Úupperr   r   )r   Ú
sla_prefixr   r   r   r   r   Úread_all_tickets_by_sla   s    z*IncidentConnection.read_all_tickets_by_slac          	   C   s:   | j  ¡ &}| d¡ |jdkr(| ¡ ng }|S Q R X d S )NzSELECT * FROM "tickets_by_sla" r   )r   r   r   r   r   )r   r   r   r   r   r   Úread_all_tickets_noparams_sla   s    
z0IncidentConnection.read_all_tickets_noparams_slac          	   C   s:   | j  ¡ &}| d¡ |jdkr(| ¡ ng }|S Q R X d S )Nz#SELECT * FROM tickets_by_sla_c_t_s r   )r   r   r   r   r   )r   r   r   r   r   r   Ú$read_all_tickets_no_params_sla_c_t_s&   s    
z7IncidentConnection.read_all_tickets_no_params_sla_c_t_sc          	   C   sF   | j  ¡ 2}| d|||||f¡ |jdkr4| ¡ ng }|S Q R X d 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   r   r   r   )r   Úanio1Úanio2Úmesr   r   r   r   r   Úread_ticket_rep_and_comp_year.   s    z0IncidentConnection.read_ticket_rep_and_comp_yearc          	   C   s:   | j  ¡ &}| d¡ |jdkr(| ¡ ng }|S Q R X d S )Nz^SELECT *
                        FROM
                            tickets_cad_y_nocad_by_aniosr   )r   r   r   r   r   )r   r   r   r   r   r   Úread_ticket_cad_y_nocad_yearE   s
    z/IncidentConnection.read_ticket_cad_y_nocad_yearc          	   C   sD   | j  ¡ 0}| d||||f¡ |jdkr2| ¡ ng }|S Q R X d 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   Úread_tendencia_by_tipoO   s    z)IncidentConnection.read_tendencia_by_tipoc          	   C   s:   | j  ¡ &}| d¡ |jdkr(| ¡ ng }|S Q R X d S )Nz.SELECT * FROM view_tendencia_tickets_all_aniosr   )r   r   r   r   r   )r   r   r   r   r   r   Ú!read_tendencias_tickets_all_anios\   s    
z4IncidentConnection.read_tendencias_tickets_all_aniosc          	   C   s:   | j  ¡ &}| d¡ |jdkr(| ¡ ng }|S Q R X d S )Nz"select * from vista_calificacionesr   )r   r   r   r   r   )r   r   r   r   r   r   Úread_all_calificationse   s    
z)IncidentConnection.read_all_calificationsc          	   C   s>   | j  ¡ *}| d|f¡ |jdkr,| ¡ ng }|S Q R X d S )Nz/SELECT * FROM "incidentes" WHERE categoria = %sr   )r   r   r   r   r   )r   ZcategoryNamer   r   r   r   r   Úread_by_categoryk   s    z#IncidentConnection.read_by_categoryc          	   C   sr   |d }|  d¡}| j ¡ L}| dd|d  ¡  d d|d  ¡  d f¡ |jdkr`| ¡ ng }|S Q R X d 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   é   )Úsplitr   r   r   r   r   r   )r   Útechnician_nameZnombresr   r   r   r   r   Úread_by_technicians   s    
0z%IncidentConnection.read_by_technicianc          	   C   s:   | j  ¡ &}| d¡ |jdkr(| ¡ ng }|S Q R X d S )Nz%SELECT * FROM incidentes_summary_viewr   )r   r   r   r   r   )r   r   r   r   r   r   Úread_total_incidents_by_group|   s    
z0IncidentConnection.read_total_incidents_by_groupc          	   C   s:   | j  ¡ &}| d¡ |jdkr(| ¡ ng }|S Q R X d S )Nz&select * from sla_cads_nocads_percentsr   )r   r   r   r   r   )r   r   r   r   r   r   Úread_sla_cads_nocads_percents‚   s    
z0IncidentConnection.read_sla_cads_nocads_percentsc             C   s$   dd|› i}t j| |d}| ¡ S )NÚAuthorizationzBearer )Úheaders)r   ÚgetÚjson)ÚurlÚtokenr1   Úresponser   r   r   Úget_data_in_url‰   s    z"IncidentConnection.get_data_in_urlc          	   C   s0   | j  ¡ }| d|¡ | j  ¡  W d Q R X d S )Nz>INSERT INTO "incident"(name,phone) VALUES(%(name)s, %(phone)s))r   r   r   Z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%   r&   r'   r(   r-   r.   r/   Ústaticmethodr7   r8   r9   r   r   r   r   r      s$   	
		r   )Úfastapir   r	   r   r   r   r   r   Ú<module>   s   