CREATE OR REPLACE VIEW estadisticas_asigna_vw AS 
 SELECT row_number() OVER (ORDER BY a.asigna_id) AS i, a.asigna_id, ( SELECT ((((auth_user.first_name::text || ' '::text) || auth_user.last_name::text) || ' ('::text) || auth_user.username::text) || ')'::text
           FROM auth_user
          WHERE auth_user.id = a.asigna_id) AS asigna, ( SELECT ((d.nombre::text || ' ('::text) || d.clave::text) || ')'::text
           FROM atencion_ciudadana_dependencia d
      JOIN atencion_ciudadana_dependencia_usuarios e ON d.id = e.dependencia_id
     WHERE e.user_id = a.asigna_id) AS dependencia, count(a.asigna_id) AS total, (count(a.asigna_id)::numeric * 100.0 / (( SELECT count(asigna_id) AS count
           FROM estadisticas_solicitud_vw
          WHERE dependencia_id = a.dependencia_id and año_registro = a.año_registro))::numeric)::numeric(5,2) AS porcentaje, a.dependencia_id
, año_registro
   FROM estadisticas_solicitud_vw a
  WHERE a.dependencia_id IS NOT NULL
  GROUP BY a.asigna_id, a.dependencia_id, año_registro
  ORDER BY count(a.asigna_id) DESC;

select * from estadisticas_asigna_vw where dependencia_id = 60 and año_registro = 2013;



drop view estadisticas_sexo_vw;

create view estadisticas_sexo_vw
as 
SELECT 
case when sexo = 'F' then '0' else '1' end id,
sexo clave, 
case when sexo = 'F' then 'Femenino' when sexo = 'M' then 'Masculino' end Sexo
,count(sexo) as total 
,cast((count(sexo) * 100.0 / ( select count(b.sexo) from estadisticas_solicitud_vw b where dependencia_id = a.dependencia_id
and a."año_registro" = b."año_registro"


 ) ) as decimal(5,2) )as porcentaje
, dependencia_id, "año_registro"
FROM 
  public.estadisticas_solicitud_vw a
where dependencia_id is not null and "año_registro" is not null
group by sexo, dependencia_id, "año_registro";

;
select * from estadisticas_sexo_vw a where dependencia_id = 22 and año_registro = 2014;





drop view estadisticas_fecha_registro_vw;

create view estadisticas_fecha_registro_vw
as
select meses.id, mes, d.id dependencia_id,
	(
	select count(mes_registro) 
	from estadisticas_solicitud_vw as a
	group by mes_registro,año_registro , a.dependencia_id
	having a.dependencia_id = d.id and
	mes_registro = meses.id and año_registro = 2013 ) as "2013", 
	(
	select count(mes_registro) 
	from estadisticas_solicitud_vw as a
	group by mes_registro,año_registro , a.dependencia_id
	having a.dependencia_id = d.id and 
	mes_registro = meses.id and año_registro = 2014 ) as "2014", 
	
	(
	select count(mes_registro) 
	from estadisticas_solicitud_vw as a
	group by mes_registro,año_registro , a.dependencia_id
	having a.dependencia_id = d.id and 
	mes_registro = meses.id and año_registro = 2015 ) as "2015", 
	(
	select count(mes_registro) 
	from estadisticas_solicitud_vw as a
	group by mes_registro,año_registro , a.dependencia_id
	having a.dependencia_id = d.id and 
	mes_registro = meses.id and año_registro = 2016 ) as "2016", 
	(
	select count(mes_registro) 
	from estadisticas_solicitud_vw as a
	group by mes_registro,año_registro , a.dependencia_id
	having a.dependencia_id = d.id and 
	mes_registro = meses.id and año_registro = 2017 ) as "2017", 
	(
	select count(mes_registro) 
	from estadisticas_solicitud_vw as a
	group by mes_registro,año_registro , a.dependencia_id
	having a.dependencia_id = d.id and 
	mes_registro = meses.id and año_registro = 2018 ) as "2018"

from 
(

SELECT 1 AS id, 'Enero' AS mes
UNION ALL
SELECT 2, 'Febrero'
UNION ALL
SELECT 3, 'Marzo'
UNION ALL
SELECT 4, 'Abril'
UNION ALL
SELECT 5, 'Mayo'
UNION ALL
SELECT 6, 'Junio'
UNION ALL
SELECT 7, 'Julio'
UNION ALL
SELECT 8, 'Agosto'
UNION ALL
SELECT 9, 'Septiembre'
UNION ALL
SELECT 10, 'Octubre'
UNION ALL
SELECT 11, 'Noviembre'
UNION ALL
SELECT 12, 'Diciembre') as meses,
atencion_ciudadana_dependencia D 
;



select * from estadisticas_fecha_registro_vw where dependencia_id = 22







drop view estadisticas_estatus_vw;

create view estadisticas_estatus_vw
as 
select 
case estatus_id 
when 2 then 1 
when 3 then 2 
when 1 then 3
else estatus_id
end as prioridad,

estatus_id, 
	(select nombre ||' ('|| clave || ')' from atencion_ciudadana_estatus where id = estatus_id ) Estatus, 
	(select clave from atencion_ciudadana_estatus where id = estatus_id ) Clave, 
	count(estatus_id) as total 
, dependencia_id

,cast((count(estatus_id) * 100.0 / ( select count(estatus_id) from estadisticas_solicitud_vw where dependencia_id = a.dependencia_id and año_registro = a.año_registro) ) as decimal(5,2) )as porcentaje
, año_registro
from estadisticas_solicitud_vw a
where dependencia_id is not null
group by estatus_id , dependencia_id, año_registro
order by 1
;


select * from estadisticas_estatus_vw
where dependencia_id in (60)
and año_registro in (2013)
order by total desc;






--select * from atencion_ciudadana_lugar where padre_id = 51
drop view estadisticas_localidad_vw;

create view estadisticas_localidad_vw
as
	select 
		localidad_id,
		(
		select
			nombre 
		from atencion_ciudadana_lugar b 
		where id = a.localidad_id
		) as localidad,
		count(localidad_id) as total,
		cast(
			(count(localidad_id) * 100.0 / 
				( 
				select count(localidad_id)
				from estadisticas_solicitud_vw
				where dependencia_id = a.dependencia_id
					and año_registro = a.año_registro
				)
			) as decimal(5,2) 
		) as porcentaje,
		dependencia_id,
		año_registro
	from public.estadisticas_solicitud_vw a
	group by localidad_id, dependencia_id, año_registro
		having count(localidad_id) <>0
	order by localidad_id;

select * from estadisticas_localidad_vw 
where 
dependencia_id = 60 
and año_registro = 2013;

 



;



drop view estadisticas_solicitud_vw;

create view estadisticas_solicitud_vw
as
SELECT a.id as folio, a.solicitante_id , ( SELECT atencion_ciudadana_persona.nombre
           FROM atencion_ciudadana_persona
          WHERE atencion_ciudadana_persona.id = a.solicitante_id) AS solicitante, (((a.nombre::text || ' '::text) || a.apellido_paterno::text) || ' '::text) || a.apellido_materno::text AS beneficiario, a.sexo, a.fecha_nacimiento, a.curp, a.localidad_id, ( SELECT (((( SELECT atencion_ciudadana_lugar.nombre
                   FROM atencion_ciudadana_lugar
                  WHERE atencion_ciudadana_lugar.id = b.padre_id))::text) || ' - '::text) || b.nombre::text
           FROM atencion_ciudadana_lugar b
          WHERE b.id = a.localidad_id) AS localidad, a.direccion, a.codigo_postal, a.identificacion, a.correo_electronico, a.telefono, a.celular, a.solicitud, a.dependencia_id, ( SELECT atencion_ciudadana_dependencia.nombre
           FROM atencion_ciudadana_dependencia
          WHERE atencion_ciudadana_dependencia.id = a.dependencia_id) AS dependencia, a.foto, a.estatus_id, ( SELECT ((atencion_ciudadana_estatus.nombre::text || ' ('::text) || atencion_ciudadana_estatus.clave::text) || ')'::text
           FROM atencion_ciudadana_estatus
          WHERE atencion_ciudadana_estatus.id = a.estatus_id) AS estatus, a.asigna_id, ( SELECT (auth_user.first_name::text || ' '::text) || auth_user.last_name::text
           FROM auth_user
          WHERE auth_user.id = a.asigna_id) AS asigna, a.fecha_registro::date AS fecha_registro, a.fecha_ultimo_cambio::date AS fecha_ultimo_cambio, a.nota, 
        CASE a.bandera
            WHEN true THEN 'Si'::text
            ELSE ''::text
        END AS urgente, 
	date_part('year'::text, a.fecha_registro) AS "año_registro",
	date_part('month'::text, a.fecha_registro) AS "mes_registro"
   FROM atencion_ciudadana_solicitud a
  ORDER BY a.fecha_registro::date DESC, a.estatus_id;


select * from estadisticas_solicitud_vw;









/*


drop view estadisticas_dependencia_vw;

create view estadisticas_dependencia_vw
as
select row_number() OVER (ORDER BY dependencia_id) AS i, 
dependencia_id as id, 
	
	(select nombre
from atencion_ciudadana_dependencia D 

where D.id = a.dependencia_id) as dependencia, 
	count(dependencia_id) as total
	,cast((count(dependencia_id) * 100.0 / ( select count(dependencia_id) from atencion_ciudadana_solicitud) ) as decimal(5,2) )as porcentaje

	from atencion_ciudadana_solicitud a 
	where dependencia_id is not null
	group by dependencia_id
having count(dependencia_id) > 10
	order by 5 desc
;

select * from estadisticas_dependencia_vw;






 / *




select * from estadisticas_estatus_vw --where dependencia_id = 22;



* /














select dependencia_id, count(dependencia_id) from atencion_ciudadana_solicitud group by dependencia_id;
select bandera, count(bandera) from atencion_ciudadana_solicitud group by bandera;
select asigna_id, count(asigna_id) from atencion_ciudadana_solicitud group by asigna_id;



select 
	'estatus' as tabla, 
	estatus_id as id, 
	(select nombre ||' ('|| clave || ')' from atencion_ciudadana_estatus where id = estatus_id ) concepto, 
	count(estatus_id) as total 
from atencion_ciudadana_solicitud 
group by estatus_id
union
select 'sexo', 
	case sexo when 'M' then 1 when 'F' then 0 end,
	sexo,
	count(sexo) from atencion_ciudadana_solicitud group by sexo
union
select 'localidad', 
	localidad_id, 
	(select (select nombre from atencion_ciudadana_lugar where id = b.padre_id)|| ' - '  || nombre from atencion_ciudadana_lugar b where id = a.localidad_id) localidad,
	count(localidad_id) from atencion_ciudadana_solicitud a group by localidad_id

union 
select 'fecha' tabla,
	row_number() over() as id,
	to_char(fecha_registro::date, 'dd/mm/yyyy') concepto, 
	count(fecha_registro) total 
	from atencion_ciudadana_solicitud group by fecha_registro::date
	


order by 1,4
*/