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

--***************************************************
--				BASES DE DATOS.
--***************************************************
--HIS MINSA 
select ID_CITA id_cita
	, Numero_Documento num_doc
	, convert(date,FECHA_ATENCION) fecha_atencion
	, convert(int,Codigo_Unico) renaes
	, ID_CONDICION_ESTABLECIMIENTO id_tipcond_estab
	, TIPO_DIAGNOSTICO tipo_dx
	, CODIGO_ITEM cod_item  
	, valor_lab
into #his_minsa
from his_nominal with (nolock)
where Id_Tipo_Documento in (1,6)  --sw=1 and 

 --***************************************************
--					SINTAXIS
--***************************************************
create table #tabla_reporte
(
anio int,
--mes int,
renaes int,
den int,
num int,
teleinterconsulta_consultante int,
teleinterconsulta_consultor int,
meta_digtel int,
logro_digtel float
)

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

set @mes_inicio=7 --<========== Mes de inicio

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

while @mes_inicio<=@mes_eval
begin

 DELETE FROM TB_F18 WHERE ANIO = @año 

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

--1. N° de Atentidos (Personas Nuevas o Reingresantes) en Establecimientos de Salud.
select distinct year(fecha_atencion) año
				, renaes
				, num_doc 
into #his_minsa_den
from #his_minsa (nolock)
where id_tipcond_estab in ('N','R')
and year(fecha_atencion)=@año
and month(fecha_atencion)<=@mes_inicio

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

--1. N° de atendidos con interconsultas realizads (consultor / consultante) registrados en el HIS MINSA.

	--1. Teleinterconsulta - Consultante.
	select distinct a.renaes
					, a.num_doc
					, teleinterconsulta_consultante=1
	into #his_minsa_consultante
	from #his_minsa a
	inner join #his_minsa_den b on a.num_doc=b.num_doc and a.renaes=b.renaes
	where cod_item in ('99499.11','99499.12') and try_convert(int,valor_lab)=1 -- Teleinterconsulta - Consultante.
	and year(fecha_atencion)=@año
	and month(fecha_atencion)<=@mes_inicio

	--SELECT * FROM #his_minsa_consultante

	--2. Teleinterconsulta - Consultor.
	select distinct	a.renaes
					, a.num_doc
					, teleinterconsulta_consultor=1
	into #his_minsa_consultor
	from #his_minsa a
	inner join #his_minsa_den b on a.num_doc=b.num_doc and a.renaes=b.renaes
	where cod_item in ('99499.11','99499.12') and try_convert(int,valor_lab)=2 -- Teleinterconsulta - Consultor.
	and year(fecha_atencion)=@año
	and month(fecha_atencion)<=@mes_inicio

	--3. Teleinterconsulta.
	select distinct a.renaes
					, a.num_doc
					, teleinterconsulta=1
	into #his_minsa_teleinterconsulta
	from #his_minsa a
	inner join #his_minsa_den b on a.num_doc=b.num_doc and a.renaes=b.renaes
	where cod_item in ('99499.11','99499.12') and try_convert(int,valor_lab) in (1,2)  -- Teleinterconsulta
	and year(fecha_atencion)=@año
	and month(fecha_atencion)<=@mes_inicio


--%%%%%%%%%%%%%%%%
--	INDICADOR
--%%%%%%%%%%%%%%%%
select @año anio,
				--@mes_inicio mes, 
			renaes
		, sum(den) den
		, sum(teleinterconsulta) num
		, sum(teleinterconsulta_consultante) teleinterconsulta_consultante
		, sum(teleinterconsulta_consultor) teleinterconsulta_consultor
into #reporte_digtel
from(
		select a.año
				, a.renaes
				, a.num_doc
				, den=1
				, isnull(d.teleinterconsulta,0)				teleinterconsulta
				, isnull(b.teleinterconsulta_consultante,0)  teleinterconsulta_consultante
				, isnull(c.teleinterconsulta_consultor,0)    teleinterconsulta_consultor
		from #his_minsa_den a
		left join #his_minsa_consultante b on a.renaes=b.renaes and a.num_doc=b.num_doc
		left join #his_minsa_consultor c on a.renaes=c.renaes and a.num_doc=c.num_doc
		left join #his_minsa_teleinterconsulta d on a.renaes=d.renaes and a.num_doc=d.num_doc
) as t
group by renaes

--SELECT * FROM #reporte_digtel

insert into #tabla_reporte
select *
, case when num>meta_digtel then 1
	   when num<=meta_digtel then round(convert(float,num)/convert(float,meta_digtel),4) end logro_digtel
from(
		select *
		, case when den<=1000						then 50 
			   when (den between 1001 and 2000)		then 120
			   when (den between 2001 and 4000)		then 210
			   when (den between 4001 and 8000)		then 300
			   when (den between 8001 and 16000)	then 360
			   when (den between 16001 and 32000)	then 360
			   when (den between 32000 and 64000)	then 480
			   when den>64000						then 640 end meta_digtel
		from #reporte_digtel
) as t


drop table #reporte_digtel
drop table #his_minsa_consultante
drop table #his_minsa_consultor
drop table #his_minsa_den
drop table #his_minsa_teleinterconsulta

set @mes_inicio=@mes_inicio+1

end


--===================
-- REPORTE
--===================
--SELECT * FROM #tabla_reporte

INSERT INTO TB_F18
SELECT e.Provincia,e.Distrito,e.Disa,e.Codigo_Red,e.Red,e.Codigo_MicroRed,e.MicroRed,e.Nombre_Establecimiento,e.Categoria,t.*
--INTO TB_F18 
FROM #tabla_reporte t 
LEFT join ESTABLECIMIENTOS e on convert(int, t.renaes)=e.Codigo_Unico
--inner join maestro_ubigeo u on t.ubigeo=u.UBIGEO
where (e.Provincia IN('MOYOBAMBA') or (e.Ubigueo_Establecimiento IN('220502') and convert(int,t.renaes) in('6348','7297','10516')))
ORDER BY t.anio asc

 delete from TB_F18 
 where  (convert(int,renaes) IN('6291','6282','6292','6293','6295','6296') and Red='RIOJA') --ubigeo='220101')


