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

--***************************************************
--				BASES DE DATOS.
--***************************************************
select *
into #renaes
from ESTABLECIMIENTOS
where Categoria_Establecimiento in ('III-2','II-2','II-1','II-E','III-E','III-1') and convert(int,Codigo_Sector)=7

--HIS MINSA 
select id_cita
	, convert(date,FECHA_ATENCION) fecha_atencion
	, convert(int,e.Codigo_Unico) renaes
	, p.ID_TIPO_DOCUMENTO id_tipo_doc
	, p.NUMERO_DOCUMENTO num_doc
	, h.TIPO_EDAD id_tipedad_reg
	, edad_reg
	, p.GENERO id_genero
	, 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
left join ESTABLECIMIENTOS e on h.ID_ESTABLECIMIENTO=e.Id_Establecimiento
--with (nolock)
where --sw=1															--Validacion de tipo y numero de documento
--and 
( 
	 ((CODIGO_ITEM in ('19100','19101','19102')) or (substring(CODIGO_ITEM,1,4) between 'C500' and 'C509'))			-- Cancer de mama.  
or	 ((CODIGO_ITEM='47000') or (substring(CODIGO_ITEM,1,4) between 'C220' and 'C229'))			-- Cáncer de hígado. 
or   ((CODIGO_ITEM in ('43234','43600'))	or (substring(CODIGO_ITEM,1,4) between 'C160' and 'C169'))			-- Cáncer de estómago 																				
or   ((CODIGO_ITEM in ('57500','58100')) or (substring(CODIGO_ITEM,1,4) in ('C530','C531','C539')))			-- Cáncer de cuello uterino 																							
or	 ((CODIGO_ITEM in ('44388','44389')) or (substring(CODIGO_ITEM,1,4) in ('c19x','C20x')) or (substring(CODIGO_ITEM,1,4) between 'C180' and 'C189'))			-- Cáncer de colon y recto 
or	 ((CODIGO_ITEM='11100') or (substring(CODIGO_ITEM,1,4) between 'C430' and 'C449'))			-- Cáncer de piel 
or   ((CODIGO_ITEM='55700') or (substring(CODIGO_ITEM,1,4)='C61X'))			-- Cáncer de próstata 
or	 ((CODIGO_ITEM='38500') or (substring(CODIGO_ITEM,1,4)='C963') or (substring(CODIGO_ITEM,1,4) between 'C810' and 'C859'))			-- Linfoma 
or	 ((CODIGO_ITEM in ('85095','85102')) or (substring(CODIGO_ITEM,1,4)='C901') or (substring(CODIGO_ITEM,1,4) between 'C910' and 'C959'))			-- Leucemia
or	 ((CODIGO_ITEM in ('32405','32400')) or (substring(CODIGO_ITEM,1,4) between 'C340' and 'C349'))			-- Cáncer de pulmón
or   ((CODIGO_ITEM in ('96402','96407','96549')) or (CODIGO_ITEM between '96409' and '96417') or (CODIGO_ITEM between '96420' and '96423'))			-- Tratamiento CPMS																																)
)


--***************************************************
--					SINTAXIS
--***************************************************
declare @mes_eval int,
		@año int 

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

--%%%%%%%%%%%%%%%%
-- 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_eval

	--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_eval

	--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_eval

	--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_eval

	--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_eval

	--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_eval

	--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_eval

	--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_eval

	--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_eval

	--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_eval

	--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_eval

	--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_eval

	--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_eval

	--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_eval

	--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_eval

	--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
--%%%%%%%%%%%%%%%%
select @año año, renaes, id_tipo_doc, num_doc, den, num
into #tabla_reporte
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

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

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

drop table TRAMAHIS_DL_CG12_2023

select 
B.Disa Diris, CASE 
	WHEN B.Departamento='LIMA' AND B.Provincia='LIMA' THEN 'LIMA METROPOLITANA'
	WHEN B.Departamento='LIMA' AND B.Provincia<>'LIMA' THEN 'LIMA PROVINCIAS'
	ELSE B.Departamento END Departamento
, B.Provincia Provincia
, B.Distrito Distrito
, case when b.Disa like '%diris%' then b.Disa else B.Red end Red
, b.MicroRed MicroRed
, b.Categoria_Establecimiento cat_estab
, b.Nombre_Establecimiento eess_nombre
, a.*
into TRAMAHIS_DL_CG12_2023
from #tabla_reporte a
inner join #renaes b on convert(int,a.renaes)=convert(int,b.Codigo_Unico)

select Diris, Departamento,	Provincia,	Distrito,	Red,	MicroRed,	cat_estab,	eess_nombre, año,	renaes
, sum(den) den, sum(num) num
from TRAMAHIS_DL_CG12_2023
group by Diris, Departamento,	Provincia,	Distrito,	Red,	MicroRed,	cat_estab,	eess_nombre, año,	renaes


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

--select año, Provincia, Distrito, red, MicroRed, renaes, eess_nombre, id_tipo_doc, num_doc,den,num from TRAMAHIS_DL_CG12_2023