--===========================================================================================
/* **************** OFICINA DE GESTIÓN DE LA INFORMACIÓN (OGEI/OGTI/MINSA) ******************
FICHA: INDICADOR N° - DL 1153 - 2024
NOMBRE: Porcentaje de niñas/niños recién nacidos de parto institucional vacunados con BCG y 
Anti hepatitis B antes del alta															   */
--===========================================================================================

--*******************************************************************************************
-- Creado por (2023)   : Jhonatan Lavi Casilla (OGEI)
-- Fecha creación      : 23/01/2023
---------------------------------------------------------------------------------------------
-- Fecha Actualización : 25/10/2023
-- Motivo              : Modificaciones en la FICHA para los Indicadores del 2024
-- Modificado por	   : Piero Romero Marín (OGEI)
--*******************************************************************************************

use BD_HISINDICADORES
go

--***************************************************
--				BASES DE DATOS.
--***************************************************
--HIS MINSA 
select
	id_cita									,
	fecha_atencion	= convert(date,periodo)	,
	renaes			= convert(int,renaes)	,
	num_doc									,
	tipo_dx			= id_tipitem			,
	cod_item								,
	valor_lab
into #his_minsa
from BD_BACKUP_OGEI.dbo.MEF_TramaHisMinsa_202309_20231023 with (nolock)
where cod_item		in ('90585',				--Vacuna BCG
						'90744') AND			--Vacuna HVB
	  id_tipo_doc	in (1,6)	 AND
	  sw = 1

--CNV
select
	distinct
	num_doc		= nu_cnv					,
	fecha_nac	= convert(date,FE_NACIDO)	,
	renaes		= convert(int,CO_LOCAL)
into #base_cnv
from BD_BACKUP_OGEI.dbo.MEF_TramaCNV_202309_20231023 with (nolock)
where					 sw_cnv = 1		AND 
	  convert(int,PESO_NACIDO) >= 1500		--Excluir menos de 1500 gr.

--Padron Nominal.
select
	distinct
	num_cnv = nu_cnv,
	num_dni = NU_DNI_MENOR
into #Padron_Nominal
from (	select
			NU_CNV		,
			NU_DNI_MENOR
		from BD_BACKUP_OGEI.dbo.MEF_TramaPadronNominal_202309_20231023
		where (sw_cnv = 1 or sw_pn = 1)	and
			   try_convert(int,nu_cnv) is not null and
			   NU_CNV <> ''
	 ) as t 
where try_convert(int,NU_DNI_MENOR) is not null and
	  NU_DNI_MENOR <> ''


--***************************************************
--					SINTAXIS
--***************************************************
create table #tabla_reporte
(	renaes		int			,
	num_cnv		nvarchar(12),
	num_dni		nvarchar(15),
	fecha_nac	date		,
	año			int			,
	mes			int			,
	den			int			,
	num			int			,
	vacuna_BCG	int			,
	fecha_BCG	date		,
	cnv_bcg		int			,
	vacuna_HVB	int			,
	fecha_HVB	date		,
	cnv_hvb		int
)

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

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

while @mes_inicio <= @mes_eval
begin

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

--1.Niños /niñas nacidas en Hospital o Instituto, registrados en el CNV en Línea en el periodo de evaluación
select
	distinct
	num_cnv		= a.num_doc					,
	b.num_dni								,
	a.fecha_nac								,
	fecha_corte = dateadd(dd,1,a.fecha_nac)	,
	a.renaes				
into #cnv_den
from #base_cnv a
left join #Padron_Nominal b on a.num_doc = b.num_cnv
where month(a.fecha_nac) = @mes_inicio and
	   year(a.fecha_nac) = @año

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

--1. Niños del denominador que recibieron vacunas HVB y BCG

		--1.1 Recibio vacunas HVB en menos de 24 horas. 

			-- Validacion con Cnv.
			select
				a.num_cnv						,
				a.fecha_nac						,
				a.renaes						,
				fecha_HVB	= b.fecha_atencion	,
				vacuna_HVB	= 1					,
				cnv			= 1
			into #cnv_numHVB_cnv
			from #cnv_den a
			inner join #his_minsa b on			   a.num_cnv = b.num_doc and
									   convert(int,a.renaes) = convert(int,b.renaes) 
			where b.cod_item = '90744' and
				 (b.fecha_atencion between a.fecha_nac and a.fecha_corte)

			-- Validacion con Dni.
			select
				a.num_cnv						,
				a.fecha_nac						,
				a.renaes						,
				fecha_HVB	= b.fecha_atencion	,
				vacuna_HVB	= 1					,
				cnv			= 0
			into #cnv_numHVB_his
			from #cnv_den a
			inner join #his_minsa b on			    a.num_dni = b.num_doc				and
										convert(int,a.renaes) = convert(int,b.renaes) 
			where	 b.cod_item = '90744'												and
					(b.fecha_atencion between a.fecha_nac and a.fecha_corte)

		-- Validacion Final.
		select
			num_cnv		,
			fecha_nac	,
			renaes		,
			fecha_HVB	= max(fecha_HVB)	,
			vacuna_HVB	= max(vacuna_HVB)	,
			cnv_hvb		= max(cnv)
		into #cnv_numHVB
		from(	select *
				from #cnv_numHVB_cnv
				union all 
				select *
				from #cnv_numHVB_his
			 ) as t
		group by num_cnv, fecha_nac, renaes

		--1.2 Recibio vacunas BCG en menos de 24 horas. 

			-- Validacion con Cnv.
			select
				a.num_cnv	,
				a.fecha_nac	,
				a.renaes	,
				fecha_BCG	= b.fecha_atencion	,
				vacuna_BCG	= 1					,
				cnv			= 1
			into #cnv_numBCG_cnv
			from #cnv_den a
			inner join #his_minsa b on				a.num_cnv = b.num_doc				and
										convert(int,a.renaes) = convert(int,b.renaes) 
			where b.cod_item = '90585' and
				 (b.fecha_atencion between a.fecha_nac and a.fecha_corte)

			-- Validacion con Dni.
			select
				a.num_cnv	,
				a.fecha_nac	,
				a.renaes	,
				fecha_BCG	= b.fecha_atencion	,
				vacuna_BCG	= 1					,
				cnv			= 0
			into #cnv_numBCG_his
			from #cnv_den a
			inner join #his_minsa b on				a.num_dni = b.num_doc and
										convert(int,a.renaes) = convert(int,b.renaes) 
			where b.cod_item = '90585' and
				 (b.fecha_atencion between a.fecha_nac and a.fecha_corte)


		-- Validacion Final.
		select
			num_cnv		,
			fecha_nac	,
			renaes		,
			fecha_BCG	= max(fecha_BCG)	,
			vacuna_BCG	= max(vacuna_BCG)	,
			cnv_bcg		= max(cnv)
		into #cnv_numBCG
		from(	select *
				from #cnv_numBCG_cnv
				union all 
				select *
				from #cnv_numBCG_his
			 ) as t
		group by num_cnv, fecha_nac, renaes


--%%%%%%%%%%%%%%%%
--	INDICADOR
--%%%%%%%%%%%%%%%%
insert into #tabla_reporte
select
	a.renaes	,
	a.num_cnv	,
	a.num_dni	,
	a.fecha_nac	,
	año			= year(a.fecha_nac)							,
	mes			= month(a.fecha_nac)						,
	den			= 1,
	num			= iif(b.vacuna_BCG=1 and c.vacuna_HVB=1,1,0),
	vacuna_BCG	= isnull(b.vacuna_BCG,0)					,
	b.fecha_BCG	,
	cnv_bcg		= isnull(B.cnv_bcg,0)						,	--Vacuna BCG
	vacuna_HVB	= isnull(c.vacuna_HVB,0)					,
	c.fecha_HVB	,
	cnv_hvb		= isnull(C.cnv_hvb,0)							--Vacuna HVB
from #cnv_den a
left join #cnv_numBCG b on a.num_cnv   = b.num_cnv	 and
						   a.fecha_nac = b.fecha_nac and
						   a.renaes	   = b.renaes
left join #cnv_numHVB c on a.num_cnv   = c.num_cnv	 and
						   a.fecha_nac = c.fecha_nac and
						   a.renaes    = c.renaes

drop table #cnv_den
drop table #cnv_numBCG
drop table #cnv_numHVB
drop table #cnv_numBCG_cnv
drop table #cnv_numBCG_his
drop table #cnv_numHVB_cnv
drop table #cnv_numHVB_his

set @mes_inicio = @mes_inicio+1
end

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

--ALTER TABLE BD_HISINDICADORES.dbo.DL1153_2023_CG07_RnVacuna 
--ADD Indicador INT NULL, Mes_Corte INT NULL;

--UPDATE a SET
--	a.Indicador = 7,
--	a.Mes_Corte = 6
--FROM BD_HISINDICADORES.dbo.DL1153_2023_CG07_RnVacuna a



--INSERT INTO BD_HISINDICADORES.dbo.DL1153_2023_CG07_RnVacuna
select 
	Diris			=	B.diris		,
	Departamento	=	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			,
	Provincia		=	B.desc_prov	,
	Distrito		=	B.desc_dist	,
	Red				=	case
							when b.DIRIS like '%diris%' then b.diris
							else B.DESC_RED
						end			,
	MicroRed		= b.desc_mred	,
	b.cat_estab						,
	eess_nombre		= b.desc_estab	,
	--filtro_aplicacion = c.tipo		,
	a.*
	--Indicador		= 7				,
	--Mes_Corte		= @mes_eval
INTO #Reporte
from #tabla_reporte a
inner join BD_BACKUP_OGEI.dbo.Renaes_20231023 b on convert(int,a.renaes) = convert(int,b.COD_ESTAB)
where b.CAT_ESTAB in ('II-1','II-2','II-E','III-1','III-2','III-E') and
	  converT(int,b.ambito) = 1


select
	Diris							,
	Departamento					,
	Provincia						,
	Distrito						,
	Red								,
	MicroRed						,
	Cat_estab						,
	eess_nombre						,
	mes								,
	año								,
	den			= sum(den)			,
	num			= sum(num)			,
	Vacuna_BCG	= sum(vacuna_BCG)	,
	Vacuna_HVB	= sum(vacuna_HVB)
from #Reporte
--WHERE Mes_Corte = 9
group by	Diris		, Departamento	, Provincia	, Distrito	, Red				, MicroRed,
			Cat_estab	, eess_nombre	, mes		, año	



drop table #base_cnv
drop table #Padron_Nominal
drop table #his_minsa
-- =D
