/*==============================================================================
FICHA: Indicador N°12 - DL 1153 2023
ELABORACION: OGTI/ OGEI - MINSA - JL
==============================================================================*/
use DB_UEIS
go

--***************************************************
--				BASES DE DATOS.
--***************************************************
select *
, iif(convert(int,cod_estab) in (1231,1232,7397,7686,6218,2289,2305,3358,3359,3472,753,27857,5195,7277,11470,3,2084
								,6352,5946,5987,6206,6207,6210,6211,7633,16918,7634),1,0) filtro_aplicacion
into #renaes
from BD_BACKUP_OGEI.dbo.Renaes_20230522
where CAT_ESTAB in ('III-2','II-2','II-1','II-E','III-E','III-1') and convert(int,ambito)=1

--HIS MINSA 
select id_cita
	, convert(date,periodo) fecha_atencion
	, convert(int,renaes) renaes
	, id_tipo_doc
	, num_doc
	, id_tipedad_reg
	, edad_reg
	, id_genero
	, id_tipitem tipo_dx
	, cod_item  
	, valor_lab
into #his_minsa
from BD_BACKUP_OGEI.dbo.MEF_TramaHisMinsa_202304_20230522 with (nolock)
where sw=1															--Validacion de tipo y numero de documento
and 
( 
	 (  (cod_item in ('19100','19101','19102')) or (substring(cod_item,1,4) between 'C500' and 'C509')												)			-- Cancer de mama.  
or	 (	(cod_item='47000') or (substring(cod_item,1,4) between 'C220' and 'C229')																	)			-- Cáncer de hígado. 
or   (	(cod_item in ('43234','43600'))	or (substring(cod_item,1,4) between 'C160' and 'C169')														)			-- Cáncer de estómago 																				
or   (	(cod_item in ('57500','58100')) or (substring(cod_item,1,4) in ('C530','C531','C539'))														)			-- Cáncer de cuello uterino 																							
or	 (	(cod_item in ('44388','44389')) or (substring(cod_item,1,4) in ('c19x','C20x')) or (substring(cod_item,1,4) between 'C180' and 'C189')		)			-- Cáncer de colon y recto 
or	 (	(cod_item='11100') or (substring(cod_item,1,4) between 'C430' and 'C449')																	)			-- Cáncer de piel 
or   (	(cod_item='55700') or (substring(cod_item,1,4)='C61X')																						)			-- Cáncer de próstata 
or	 (	(cod_item='38500') or (substring(cod_item,1,4)='C963') or (substring(cod_item,1,4) between 'C810' and 'C859')								)			-- Linfoma 
or	 (	(cod_item in ('85095','85102')) or (substring(cod_item,1,4)='C901') or (substring(cod_item,1,4) between 'C910' and 'C959')					)			-- Leucemia
or	 (	(cod_item in ('32405','32400')) or (substring(cod_item,1,4) between 'C340' and 'C349')														)			-- Cáncer de pulmón
or   (	(cod_item in ('96402','96407','96549')) or (cod_item between '96409' and '96417') or (cod_item between '96420' and '96423')					)			-- Tratamiento CPMS																																)
)


--***************************************************
--					SINTAXIS
--***************************************************
create table #tabla_reporte
(
año int,
mes int,
renaes int,
den int,
num int
)

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

set @mes_inicio=1 --<=========== Mes de evaluación inicial
set @mes_eval=4 --<=========== Mes de evaluación final
set @año=2023 --<============= Año de evaluación 

while @mes_inicio<=@mes_eval
begin 

--%%%%%%%%%%%%%%%%
-- DENOOMINADOR
--%%%%%%%%%%%%%%%%
--1. Sumatoria del Total de personas identificadas con Documento de Identidad con diagnóstico de prevalente definitivo

	--1.1 Cáncer de mama
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_den_mama 
	from #his_minsa
	where (substring(cod_item,1,4) between 'C500' and 'C509')	
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item in ('19100','19101','19102') and tipo_dx='D' and valor_lab='A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and id_genero='F'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.2 Cáncer de hígado
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_den_higado
	from #his_minsa
	where (substring(cod_item,1,4) between 'C220' and 'C229')	
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item='47000' and tipo_dx='D' and valor_lab='A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.3 Cáncer de estómago
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_den_estomago
	from #his_minsa
	where (substring(cod_item,1,4) between 'C160' and 'C169')	
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item in ('43234','43600') and tipo_dx='D' and valor_lab='A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.4 Cáncer de cuello uterino
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_den_uterino
	from #his_minsa
	where (substring(cod_item,1,4) in ('C530','C531','C539'))	
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item in ('57500','58100') and tipo_dx='D' and valor_lab='A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and id_genero='F'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.5 Cáncer de colon y recto
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_den_colon
	from #his_minsa
	where ( (substring(cod_item,1,4) between 'C180' and 'C189') or (substring(cod_item,1,4) in ('C19X','C20X'))  )	
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item in ('44388','44389') and tipo_dx='D' and valor_lab='A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.6 Cáncer de piel
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_den_piel
	from #his_minsa
	where (substring(cod_item,1,4) between 'c430' and 'c449')  	
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item='11100' and tipo_dx='D' and valor_lab='A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.7 Cáncer de prostata
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_den_prostata
	from #his_minsa
	where (substring(cod_item,1,4)='c61x')  	
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item='55700' and tipo_dx='D' and valor_lab='A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and id_genero='M'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.8 Linfoma
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_den_linfoma
	from #his_minsa
	where ( (substring(cod_item,1,4)='C963') or (substring(cod_item,1,4) between 'c810' and 'c859') )
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item='38500' and tipo_dx='D' and valor_lab='A')
		)
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.9 Leucemia
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_den_leucemia
	from #his_minsa
	where ( (substring(cod_item,1,4)='C901') or (substring(cod_item,1,4) between 'c910' and 'c959') )
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item in ('85095','85102') and tipo_dx='D' and valor_lab='A')
		)
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.10 Cancer Pulmon
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_den_pulmon
	from #his_minsa
	where (substring(cod_item,1,4) between 'C340' and 'C349') 
	and tipo_dx='D'
	and (valor_lab is null or valor_lab='')
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where cod_item in ('32405','32400') and tipo_dx='D' and valor_lab='A')
		)
	and edad_reg>=18 and id_tipedad_reg='A'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--Denominador Final.
	select distinct renaes, id_tipo_doc, num_doc, den=1 into #dpcan_den
	from (
			select * from #his_minsa_den_colon
			union all select * from #his_minsa_den_estomago
			union all select * from #his_minsa_den_higado
			union all select * from #his_minsa_den_leucemia
			union all select * from #his_minsa_den_linfoma
			union all select * from #his_minsa_den_mama
			union all select * from #his_minsa_den_piel
			union all select * from #his_minsa_den_pulmon
			union all select * from #his_minsa_den_prostata
			union all select * from #his_minsa_den_uterino
	) as t
	
--%%%%%%%%%%%%%%%%
-- NUMERADOR
--%%%%%%%%%%%%%%%%

--1. Número de personas del denominador que inician tratamiento de quimioterapia y registradas en el HIS 

	--1.1 Tratamiento Quimioterapia 1. 
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_tq1
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or (cod_item between '96420' and '96423') or (cod_item in ('96402','96407','96549'))  )
	and tipo_dx='D'
	and try_convert(int,valor_lab)=1
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where (substring(cod_item,1,4) between 'C500' and 'C509') and tipo_dx='R')
		)
	and edad_reg>=18 and id_tipedad_reg='A'	
	and id_genero='F'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.2 Tratamiento Quimioterapia 2. 
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_tq2
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or (cod_item between '96420' and '96423') or (cod_item in ('96402','96407','96549'))  )
	and tipo_dx='D'
	and try_convert(int,valor_lab)=1
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where (substring(cod_item,1,4) in ('C530','C531','C539')) and tipo_dx='R')
		)
	and edad_reg>=18 and id_tipedad_reg='A'	
	and id_genero='F'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.3 Tratamiento Quimioterapia 3.
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_tq3
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or (cod_item between '96420' and '96423') or (cod_item in ('96402','96407','96549'))  )
	and tipo_dx='D'
	and try_convert(int,valor_lab)=1
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where (substring(cod_item,1,4)='C61X') and tipo_dx='R')
		)
	and edad_reg>=18 and id_tipedad_reg='A'	
	and id_genero='M'
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.4 Tratamiento Quimioterapia 4.
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_tq4
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or (cod_item between '96420' and '96423') or (cod_item in ('96402','96407','96549'))  )
	and tipo_dx='D'
	and try_convert(int,valor_lab)=1
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where ( (substring(cod_item,1,4) between 'C220' and 'C229') or (substring(cod_item,1,4) between 'C160' and 'C169')
								or (substring(cod_item,1,4) between 'C180' and 'C189') or (substring(cod_item,1,4) between 'C340' and 'C349')	
								or (substring(cod_item,1,4) between 'C430' and 'C449')	or (substring(cod_item,1,4) in ('c19x','c20x'))   )
					  and tipo_dx='R') 
		)
	and edad_reg>=18 and id_tipedad_reg='A'	
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--1.4 Tratamiento Quimioterapia 5.
	select distinct renaes, id_tipo_doc, num_doc
	into #his_minsa_tq5
	from #his_minsa
	where ( (cod_item between '96409' and '96417') or (cod_item between '96420' and '96423') or (cod_item in ('96402','96407','96549'))  )
	and tipo_dx='D'
	and try_convert(int,valor_lab)=1
	and ( id_cita in 
					(select distinct id_cita from #his_minsa
					  where ( (substring(cod_item,1,4) between 'C810' and 'C859') or (substring(cod_item,1,4) between 'C910' and 'C959')
							 or (substring(cod_item,1,4) in ('c963','c901')) )
					  and tipo_dx='R') 
		)
	and year(fecha_atencion)=@año and month(fecha_atencion)<=@mes_inicio

	--Numerador Final.
	select distinct renaes, id_tipo_doc, num_doc, num=1 into #dpcan_num
	from (
			select * from #his_minsa_tq1
			union all select * from #his_minsa_tq2
			union all select * from #his_minsa_tq3
			union all select * from #his_minsa_tq4
			union all select * from #his_minsa_tq5
	) as t

--%%%%%%%%%%%%%%%%
--	INDICADOR
--%%%%%%%%%%%%%%%%
insert into #tabla_reporte
select @año año, @mes_inicio mes, renaes, sum(den) den, sum(num) num
from (
		select a.*, isnull(b.num,0) num
		from #dpcan_den a
		left join #dpcan_num b on a.renaes=b.renaes and a.id_tipo_doc=b.id_tipo_doc and a.num_doc=b.num_doc 
) as t
group by renaes

drop table #dpcan_den
drop table #dpcan_num
drop table #his_minsa_den_colon
drop table #his_minsa_den_estomago
drop table #his_minsa_den_higado
drop table #his_minsa_den_leucemia
drop table #his_minsa_den_linfoma
drop table #his_minsa_den_mama
drop table #his_minsa_den_piel
drop table #his_minsa_den_prostata
drop table #his_minsa_den_pulmon
drop table #his_minsa_den_uterino
drop table #his_minsa_tq1
drop table #his_minsa_tq2
drop table #his_minsa_tq3
drop table #his_minsa_tq4
drop table #his_minsa_tq5

set @mes_inicio=@mes_inicio+1
end

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

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
, case when b.DIRIS like '%diris%' then b.diris else B.DESC_RED end Red
, b.desc_mred MicroRed
, b.cat_estab
, b.desc_estab eess_nombre
, b.filtro_aplicacion
, a.*
into BD_HISINDICADORES.dbo.DL1153_2023_CG12_PersonasCancerTratamientoOncologico
from #tabla_reporte a
inner join #renaes b on convert(int,a.renaes)=convert(int,b.COD_ESTAB)


select diris, Departamento, Provincia, Distrito, red, MicroRed, cat_estab
, eess_nombre, filtro_aplicacion, año, renaes, den, num
from BD_HISINDICADORES.dbo.DL1153_2023_CG12_PersonasCancerTratamientoOncologico
where mes=@mes_eval

select *
from BD_HISINDICADORES.dbo.DL1153_2023_CG12_PersonasCancerTratamientoOncologico


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