/*==============================================================================
FICHA: Indicador N°14 - Porcentaje de mujeres que acceden a métodos de 
planificación familiar post parto institucional
ELABORACION: DIRESA SAN MARTÍN - JCAR
==============================================================================*/
use DataHIS
go

--***************************************************
--				BASES DE DATOS.
--***************************************************
--HIS MINSA 
select id_cita
	, convert(date,FECHA_ATENCION) fecha_atencion
	, convert(int,ANIO+MES) aniomes
	, p.NUMERO_DOCUMENTO num_doc
	, TIPO_DIAGNOSTICO tipo_dx
	, CODIGO_ITEM cod_item  
	, valor_lab
into #his_minsa
from HIS_Nominal h
inner join PACIENTE p on h.ID_PACIENTE=p.ID_PACIENTE-- with (nolock)
where CODIGO_ITEM in (
					'58611'						-- Ligadura de Trompas. (Cesarea)
					,'58605'					-- Ligadura de Trompas. (Hospitalizacion)
					,'59515'					-- Atencion post parto (Cesarea) 
					,'59410'					-- Atencion post parto (Parto Vaginal) 
					,'59430'					-- Atencion post parto (Control Puerpera) 
					,'11975'					-- Insercion Implante 
					,'58300'					-- Insercion Intrauterino
					,'99208.05'					-- Metodo Inyectable Trimestral
					,'99208.02'					-- Preservativos Masculinos.
					,'99208.06'					-- Preservativos Femeninos.
				)
and ID_TIPO_DOCUMENTO=1 -- and sw=1

--insert into #his_minsa
--select id_cita
--	, convert(date,periodo) fecha_atencion
--	, convert(int,aniomes) aniomes
--	, num_doc
--	, id_tipitem tipo_dx
--	, cod_item  
--	, valor_lab
--from [lnksumatika].fed_midis.dbo.TramaHisMinsa_202212_20230124 with (nolock)
--where cod_item in (
--					'58611'						-- Ligadura de Trompas. (Cesarea)
--					,'58605'					-- Ligadura de Trompas. (Hospitalizacion)
--					,'59515'					-- Atencion post parto (Cesarea) 
--					,'59410'					-- Atencion post parto (Parto Vaginal) 
--					,'59430'					-- Atencion post parto (Control Puerpera) 
--					,'11975'					-- Insercion Implante 
--					,'58300'					-- Insercion Intrauterino
--					,'99208.05'					-- Metodo Inyectable Trimestral
--					,'99208.02'					-- Preservativos Masculinos.
--					,'99208.06'					-- Preservativos Femeninos.
--				)
--and id_tipo_doc=1 and sw=1

--RENAES
select distinct Codigo_Unico  renaes
into #renaes 
from ESTABLECIMIENTOS
where convert(int,Codigo_Sector)=7

--CNV (CERTIFICADO NACIDO VIDO) select * from cnv
select nu_doc_madre ndoc
	  , convert(date,fe_nacido) fecha_parto 
	  , Ubigeo_DOM_Madre ubigeoreniec --dpto_dom_madre+prov_dom_madre+dist_dom_madre
into #base_cnv
from CNV with (nolock)
where Tipo_Doc_Madre='DNI/LE' --and sw_dni=1
--and try_convert(int,cont_dom_madre)=92 and try_convert(int,pais_dom_madre)=33										--> Solo partos de gestantes 
and Lugar_Nacido='ESTABLECIMIENTO DE SALUD'																					--> Partos realizados en establecimientos de salud.
and convert(int,co_local) in (select distinct convert(int,renaes) from #renaes)										--> Establecimientos del MINSA

				--- eliminar duplicados
				select ndoc
					,min(fecha_parto) minfecha_parto
				into #elimina_duplicado
				from #base_cnv
				where ndoc+'-'+convert(varchar(6), month(fecha_parto),112)+ubigeoreniec
											in
											(
												select ndoc+'-'+convert(varchar(6), month(fecha_parto),112)+ubigeoreniec
												from #base_cnv
												group by ndoc+'-'+convert(varchar(6), month(fecha_parto),112)+ubigeoreniec
												having count(*) >1
											)
				group by ndoc

				delete from #base_cnv
				where convert(varchar,ndoc)+'-'+convert(varchar(8),fecha_parto,112)
				in			(
							select convert(varchar,ndoc)+'-'+convert(varchar(8),minfecha_parto,112) 
							from #elimina_duplicado
							)


--***************************************************
--					SINTAXIS
--***************************************************
create table #tabla_reporte
( 
ubigeoreniec int ,
ndoc nvarchar(10),
fecha_parto date,
fecha_puerpera date,
año int,
mes int,
den int,
num int,
num_ligadura_cesarea int,
fecha_ligadura_cesarea date,
num_ligadura_postparto int,
fecha_ligadura_postparto date,
num_cesarea_atencionpp int,
fecha_cesarea_atencionpp date,
num_postparto_atencionpp int,
fecha_postparto_atencionpp date,
num_puerpera_atencionpp int,
fecha_puerpera_atencionpp date
)

declare @mes_inicio int,
		@mes_eval int,
		@año int 

set @mes_inicio=1 --<========= Mes inicio
set @mes_eval=2 --<=========== Mes de evaluación
set @año=2023 --<============= Año de evaluación 

while @mes_inicio<=@mes_eval
begin

--%%%%%%%%%%%%%%%%
--  DENOMINADOR
--%%%%%%%%%%%%%%%%

--1.Número Total de partos institucionales de mujeres que dieron su parto 42 días antes de la evaluación
select distinct ndoc
				,fecha_parto
				,dateadd(dd,42,fecha_parto) fecha_puerpera
				,converT(int,ubigeoreniec) ubigeoreniec

into #cnv_den
from #base_cnv
where month(dateadd(dd,42,fecha_parto))=@mes_inicio
   and year(dateadd(dd,42,fecha_parto))=@año


--%%%%%%%%%%%%%%%%
-- NUMERADOR
--%%%%%%%%%%%%%%%%

--1. Número de mujeres del denominador que reciben un método anticonceptivo:

	--1.1 Ligadura de trompa(s) cuando se realiza al mismo tiempo que la cesárea
	select ndoc, fecha_parto, ubigeoreniec, max(b.fecha_atencion) fecha_ligadura_cesarea, ligadura_cesarea=1
	into #cnv_num_ligadura_cesarea
	from #cnv_den a
	inner join #his_minsa b on a.ndoc=b.num_doc
	where b.cod_item='58611' and b.tipo_dx='D'
	and ( b.fecha_atencion between a.fecha_parto and a.fecha_puerpera )
	group by ndoc, fecha_parto, ubigeoreniec

	--1.2 Ligadura de trompa(s) cuando se realiza durante la misma hospitalización post parto vagina
	select ndoc, fecha_parto, ubigeoreniec, max(b.fecha_atencion) fecha_ligadura_postparto, ligadura_postparto=1
	into #cnv_num_ligadura_postparto
	from #cnv_den a
	inner join #his_minsa b on a.ndoc=b.num_doc
	where b.cod_item='58605' and b.tipo_dx='D'
	and ( b.fecha_atencion between a.fecha_parto and a.fecha_puerpera )
	group by ndoc, fecha_parto, ubigeoreniec

	--1.3 Cesárea, incluyendo atención postparto. 
	select ndoc, fecha_parto, ubigeoreniec, max(b.fecha_atencion) fecha_cesarea_atencionpp, cesarea_atencionpp=1
	into #cnv_num_cesarea_atencionpp
	from #cnv_den a
	inner join #his_minsa b on a.ndoc=b.num_doc
	where b.cod_item='59515' and b.tipo_dx in ('R','D')
	and b.id_cita in (select distinct id_cita from #his_minsa where cod_item in ('11975','58300','99208.05','99208.02','99208.06') and tipo_dx in ('R','D'))
	and ( b.fecha_atencion between a.fecha_parto and a.fecha_puerpera )
	group by ndoc, fecha_parto, ubigeoreniec

	--1.4 Atención de parto vaginal (con o sin episiotomía), incluyendo atención postparto 
	select ndoc, fecha_parto, ubigeoreniec, max(b.fecha_atencion) fecha_postparto_atencionpp, postparto_atencionpp=1
	into #cnv_num_postparto_atencionpp
	from #cnv_den a
	inner join #his_minsa b on a.ndoc=b.num_doc
	where b.cod_item='59410' and b.tipo_dx in ('R','D')
	and b.id_cita in (select distinct id_cita from #his_minsa where cod_item in ('11975','58300','99208.05','99208.02','99208.06') and tipo_dx in ('R','D'))
	and ( b.fecha_atencion between a.fecha_parto and a.fecha_puerpera )
	group by ndoc, fecha_parto, ubigeoreniec

	--1.5 Atención postparto Control de puérpera
	select ndoc, fecha_parto, ubigeoreniec, max(b.fecha_atencion) fecha_puerpera_atencionpp, puerpera_atencionpp=1
	into #cnv_num_puerpera_atencionpp
	from #cnv_den a
	inner join #his_minsa b on a.ndoc=b.num_doc
	where b.cod_item='59430' and b.tipo_dx='D' and try_convert(int,b.valor_lab) in (1,2)
	and b.id_cita in (select distinct id_cita from #his_minsa where cod_item in ('11975','58300','99208.05','99208.02','99208.06') and tipo_dx in ('R','D'))
	and ( b.fecha_atencion between a.fecha_parto and a.fecha_puerpera )
	group by ndoc, fecha_parto, ubigeoreniec


--%%%%%%%%%%%%%%%%
--	INDICADOR
--%%%%%%%%%%%%%%%%
insert into #tabla_reporte
select a.ubigeoreniec, a.ndoc, a.fecha_parto, a.fecha_puerpera, year(fecha_puerpera) año, month(fecha_puerpera) mes
, den=1
, iif(b.ligadura_cesarea=1 or c.ligadura_postparto=1 or d.cesarea_atencionpp=1 or e.postparto_atencionpp=1 or f.puerpera_atencionpp=1,1,0) num
, isnull(b.ligadura_cesarea,0) num_ligadura_cesarea, b.fecha_ligadura_cesarea
, isnull(c.ligadura_postparto,0) num_ligadura_postparto, c.fecha_ligadura_postparto
, isnull(d.cesarea_atencionpp,0) num_cesarea_atencionpp, d.fecha_cesarea_atencionpp
, isnull(e.postparto_atencionpp,0) num_postparto_atencionpp, e.fecha_postparto_atencionpp
, isnull(f.puerpera_atencionpp,0) num_puerpera_atencionpp, f.fecha_puerpera_atencionpp
from  #cnv_den a
left join #cnv_num_ligadura_cesarea b on a.ndoc=b.ndoc and a.fecha_parto=b.fecha_parto and a.ubigeoreniec=b.ubigeoreniec
left join #cnv_num_ligadura_postparto c on a.ndoc=c.ndoc and a.fecha_parto=c.fecha_parto and a.ubigeoreniec=c.ubigeoreniec
left join #cnv_num_cesarea_atencionpp d on a.ndoc=d.ndoc and a.fecha_parto=d.fecha_parto and a.ubigeoreniec=d.ubigeoreniec
left join #cnv_num_postparto_atencionpp e on a.ndoc=e.ndoc and a.fecha_parto=e.fecha_parto and a.ubigeoreniec=e.ubigeoreniec
left join #cnv_num_puerpera_atencionpp f on a.ndoc=f.ndoc and a.fecha_parto=f.fecha_parto and a.ubigeoreniec=f.ubigeoreniec

drop table #cnv_den
drop table #cnv_num_cesarea_atencionpp
drop table #cnv_num_ligadura_cesarea
drop table #cnv_num_ligadura_postparto
drop table #cnv_num_postparto_atencionpp
drop table #cnv_num_puerpera_atencionpp

set @mes_inicio=@mes_inicio+1
end

--===================
-- REPORTE
--===================
--drop table BD_HISINDICADORES.dbo.DL1153_2023_CG14_PlanifiFamiliarPostParto

drop table TRAMAHIS_DL_CG14_2023

select 
B.diris Diris, CASE 
	WHEN B.DESC_DPTO='LIMA' AND B.DESC_PROV='LIMA' THEN 'LIMA METROPOLITANA'
	WHEN B.DESC_DPTO='LIMA' AND B.DESC_PROV<>'LIMA' THEN 'LIMA PROVINCIAS'
	ELSE B.DESC_DPTO END Departamento
, B.desc_prov Provincia
, B.desc_dist Distrito
, B.Red
, b.Ubigeo
, a.*
into TRAMAHIS_DL_CG14_2023
from #tabla_reporte a
inner join MAESTRO_UBIGEO b on CONVERT(int,a.ubigeoreniec)=CONVERT(int,b.UBIGEO) 

select Diris, Departamento, Provincia, Distrito, Red, ubigeo, mes, año 
, sum(den) denominador, sum(num) numerador
, sum(num_ligadura_cesarea)  num_ligadura_cesarea
, sum(num_ligadura_postparto) num_ligadura_postparto
, sum(num_cesarea_atencionpp) num_cesarea_atencionpp
, sum(num_postparto_atencionpp) num_postparto_atencionpp
, sum(num_puerpera_atencionpp) num_puerpera_atencionpp
from TRAMAHIS_DL_CG14_2023
group by Diris, Departamento, Provincia, Distrito, Red, ubigeo, mes, año 


drop table #base_cnv
drop table #his_minsa
drop table #elimina_duplicado
drop table #renaes
drop table #tabla_reporte
-- =D


--select año,mes, Ubigeo, Departamento, Provincia, Distrito, RED, ndoc, fecha_parto, den,num, num_ligadura_cesarea, num_ligadura_postparto,
--num_cesarea_atencionpp num_cesarea, num_postparto_atencionpp num_postparto, num_puerpera_atencionpp num_puerpera
--from TRAMAHIS_DL_CG14_2023