
     hB                     4    d dl mZ d dlZd dlZ G d d      Zy)    )requestsNc                       e Zd ZdZd Zd Zd Zd Zd Zd Z	d Z
d	 Zd
 Zd Zd Zd Zd Zd Zd Zd Zed        Zd Zd Zy)IncidentConnectionNc                 X    d| _         d| _        d| _        d| _        d| _        d| _        y )Ntcsdbtcsadminztcsadmin.2012z198.251.74.1005432z-c search_path=hawa_consume)dbnameuserpasswordhostportschemaselfs    ;/opt/lhia/hawa/backend/python/models/incident_connection.py__init__zIncidentConnection.__init__   s,    	'$		3    c                    	 t        j                  | j                  | j                  | j                  | j
                  | j                  | j                        }|S # t         j                  $ r}t        d|        Y d}~yd}~ww xY 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errs      r   open_connectionz"IncidentConnection.open_connection   sq    	##{{YYYYYYD K(( 	(./	s   AA B.BBc                 @    t        d       |r|j                          y y )Nu'   Cierra la conexión a la base de datos.)r   close)r   r   s     r   close_connectionz#IncidentConnection.close_connection    s    ;<JJL r   c                    t        d       | j                         }|_|j                         5 }|j                  d       |j                  dkD  r|j                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)Nz/Ejecuta una consulta y devuelve los resultados.z SELECT * FROM "incidentes" r   )r   r   cursorexecuterowcountfetchallr    r   r   curdatas       r   read_allzIncidentConnection.read_all%   s    CD##% #>?),)9s||~r%%d+	 
 
 s   AA??B	c                 (   | j                         }|r|j                         5 }|j                  d|j                         dz   f       |j                  dkD  r|j                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)Nz7SELECT * FROM "tickets_by_sla" WHERE upper(sla) LIKE %s%r   )r   r"   r#   upperr$   r%   r    )r   
sla_prefixr   r'   r(   s        r   read_all_tickets_by_slaz*IncidentConnection.read_all_tickets_by_sla1   s    ##% #Y\f\l\l\nqt\t[vw),)9s||~r%%d+	 
 	
 	s   ABBc                    | j                         }|_|j                         5 }|j                  d       |j                  dkD  r|j	                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)NzSELECT * FROM "tickets_by_sla" r   r   r"   r#   r$   r%   r    r&   s       r   read_all_tickets_noparams_slaz0IncidentConnection.read_all_tickets_noparams_sla;   sz    ##% #AB),)9s||~r%%d+	 
 	
 	   AA44A>c                    | j                         }|_|j                         5 }|j                  d       |j                  dkD  r|j	                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)Nz#SELECT * FROM tickets_by_sla_c_t_s r   r0   r&   s       r   $read_all_tickets_no_params_sla_c_t_sz7IncidentConnection.read_all_tickets_no_params_sla_c_t_sE   sz    ##% #EF),)9s||~r%%d+	 
 	
 	r2   c           	         | j                         }|e|j                         5 }|j                  d|||||f       |j                  dkD  r|j	                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)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   r0   )r   anio1anio2mesr   r'   r(   s          r   read_ticket_rep_and_comp_yearz0IncidentConnection.read_ticket_rep_and_comp_yearP   s    ##% # &  E3u5!7" *-)9s||~r%%d+) * 	+* 	s   AA::Bc                    | j                         }|_|j                         5 }|j                  d       |j                  dkD  r|j	                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)Nz^SELECT *
                        FROM
                            tickets_cad_y_nocad_by_aniosr   r0   r&   s       r   read_ticket_cad_y_nocad_yearz/IncidentConnection.read_ticket_cad_y_nocad_yeark   s    ##% # < = *-)9s||~r%%d+  	 	r2   c                     | j                         }|n| j                  j                         5 }|j                  d||||f       |j                  dkD  r|j                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)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   r6   r7   r   r'   r(   s         r   read_tendencia_by_tipoz)IncidentConnection.read_tendencia_by_tipoy   s    ##%!!# 
s 0 E5%02 *-)9s||~r%%d+
 
 	
 	s   A
BBc                    | j                         }|_|j                         5 }|j                  d       |j                  dkD  r|j	                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)Nz.SELECT * FROM view_tendencia_tickets_all_aniosr   r0   r&   s       r   !read_tendencias_tickets_all_aniosz4IncidentConnection.read_tendencias_tickets_all_anios   sz    ##% #PQ),)9s||~r%%d+	 
 	
 	r2   c                    | j                         }|_|j                         5 }|j                  d       |j                  dkD  r|j	                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)Nz"select * from vista_calificacionesr   r0   r&   s       r   read_all_calificationsz)IncidentConnection.read_all_califications   sz    ##% #DE),)9s||~r%%d+	 
 	
 	r2   c                    | j                         }|a|j                         5 }|j                  d|f       |j                  dkD  r|j	                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)Nz/SELECT * FROM "incidentes" WHERE categoria = %sr   r0   )r   categoryNamer   r'   r(   s        r   read_by_categoryz#IncidentConnection.read_by_category   s    ##% #QT`Sbc),)9s||~r%%d+	 
 	
 	s   AA66B c                    | j                         }||dz   }|j                  d      }|j                         5 }|j                  dd|d   j	                         z   dz   d|d   j	                         z   dz   f       |j
                  dkD  r|j                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)N zSELECT * 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(   s         r   read_by_technicianz%IncidentConnection.read_by_technician   s-   ##%+C/O%++C0G #  k  nq  t{  |}  t~  tD  tD  tF  nF  IL  nL  MP  QX  YZ  Q[  Qa  Qa  Qc  Mc  dg  Mg  mh  i),)9s||~r%%d+	 
 	
 	s   A6B;;Cc                    | j                         }|_|j                         5 }|j                  d       |j                  dkD  r|j	                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)Nz%SELECT * FROM incidentes_summary_viewr   r0   r&   s       r   read_total_incidents_by_groupz0IncidentConnection.read_total_incidents_by_group   sz    ##% #GH),)9s||~r%%d+	 
 	
 	r2   c                    | j                         }|_|j                         5 }|j                  d       |j                  dkD  r|j	                         ng }| j                  |       |cd d d        S g S # 1 sw Y   g S xY w)Nz&select * from sla_cads_nocads_percentsr   r0   r&   s       r   read_sla_cads_nocads_percentsz0IncidentConnection.read_sla_cads_nocads_percents   sz    ##% #HI),)9s||~r%%d+	 
 	
 	r2   c                 ^    dd| i}t        j                  | |      }|j                         S )NAuthorizationzBearer )headers)r   getjson)urltokenrR   responses       r   get_data_in_urlz"IncidentConnection.get_data_in_url   s/    "geW$56<<W5}}r   c                     | j                   j                         5 }|j                  d|       | j                   j                          d d d        y # 1 sw Y   y xY w)Nz>INSERT INTO "incident"(name,phone) VALUES(%(name)s, %(phone)s))r   r"   r#   commit)r   r(   r'   s      r   writezIncidentConnection.write   sI    YY 	#KK\]abII	 	 	s   -AAc                 8    | j                   j                          y )N)r   r   r   s    r   __def__zIncidentConnection.__def__   s    		r   )__name__
__module____qualname__r   r   r   r    r)   r.   r1   r4   r9   r;   r=   r?   rA   rD   rK   rM   rO   staticmethodrX   r[   r]    r   r   r   r      su    D4 
	6
"
  
r   r   )fastapir   r   r   rb   r   r   <module>rd      s      W Wr   