with params as(
	select
		cast($1 as bigint) as p_lpu_id,
		cast($2 as bigint) as p_lpubuilding_id,
		cast($3 as bigint) as p_lpuunit_id,
		cast($4 as bigint) as p_lpusection_id,
		cast($5 as bigint) as p_medpersonal_id,
		cast($6 as timestamp) as p_begdate,
		cast($7 as timestamp) as p_enddate
),

uet as(
	select
		VizitPLStom.EvnVizitPLStom_setDate,
		sum(uet.EvnDiagPLStom_Uet) as PLStom_Uet
	from params
		inner join v_EvnVizitPLStom as VizitPLStom on (VizitPLStom.Lpu_id = p_lpu_id or p_lpu_id is null) 
		inner join v_EvnPLStom as PLStom 
			on VizitPLStom.EvnVizitPLStom_pid = PLStom.EvnPLStom_id 
		inner join v_personstate as ps on ps.person_id = PLStom.person_id
		inner join v_LpuSection as LpuSection
			on VizitPLStom.LpuSection_id = LpuSection.LpuSection_id
		left join lateral(
			select
				VizitPLStom.EvnVizitPLStom_id,
				EDPLS.EvnDiagPLStom_id,
				round(cast(
					case
						when coalesce(EDPLS.EvnDiagPLStom_IsClosed, 1) = 1
							then 0
						when EDPLS.Mes_id is not null
							then mes.Mes_KoikoDni -- УЕТ (норматив по КСГ)
						else coalesce(UET1.EvnUslugaStom_summa, 0)
							+ coalesce(UET2.EvnUslugaStom_summa, 0) -- УЕТ (факт по ОМС)
							+ coalesce(UET3.EvnUslugaCommon_summa, 0) -- УЕТ услуги посещения
					end as numeric), 2) as EvnDiagPLStom_Uet
			from v_EvnDiagPLStom as EDPLS 
				left join lateral(
					select
						sum(eus.EvnUslugaStom_summa) as EvnUslugaStom_summa
					from v_EvnUslugaStom as eus
						inner join v_PayType as pt on pt.PayType_id = eus.PayType_id
					where eus.EvnDiagPLStom_id = EDPLS.EvnDiagPLStom_id
						and pt.PayType_SysNick = 'oms'
						and coalesce(eus.EvnUslugaStom_IsAllMorbus, 1) = 1
						and coalesce(eus.EvnUslugaStom_IsVizitCode, 1) = 1
				) as UET1 on true
				left join lateral(
					select  sum(eus.EvnUslugaStom_summa) as EvnUslugaStom_summa
					from v_EvnUslugaStom as eus 
						inner join v_PayType pt on pt.PayType_id = eus.PayType_id
					where eus.EvnUslugaStom_rid = EDPLS.EvnDiagPLStom_rid
						and pt.PayType_SysNick = 'oms'
						and eus.EvnUslugaStom_IsAllMorbus = 2 
						and coalesce(eus.EvnUslugaStom_IsVizitCode, 1) = 1
				) as UET2 on true
				left join lateral(
					select  sum(eus.EvnUslugaCommon_summa) as EvnUslugaCommon_summa
					from v_EvnUslugaCommon as eus 
					where eus.EvnUslugaCommon_rid = EDPLS.EvnDiagPLStom_rid
					and VizitPLStom.EvnVizitPLStom_setDate = eus.EvnUslugaCommon_setDate
				) as UET3 on true
				left join v_MesOld mes on mes.Mes_id = EDPLS.Mes_id
			where VizitPLStom.EvnVizitPLStom_id = EDPLS.EvnDiagPLStom_pid
		)uet on true
	where true
		and EvnVizitPLStom_setDate between p_begdate and p_enddate
		and PLStom.EvnPLStom_IsFinish = 2
		and (VizitPLStom.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
		and (LpuSection.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
		and (VizitPLStom.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
		and (LpuSection.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
	group by EvnVizitPLStom_setDate
)

select 
	to_char(VizitPLStom.EvnVizitPLStom_setDate,'dd.mm.yyyy') as v1,
	count(distinct PLStom.EvnPLStom_id) as v2,
	count(distinct
		case
			when VizitPLStom.EvnVizitPLStom_IsPrimaryVizit = 2
				then PLStom.EvnPLStom_id
			end) as v3,
	count(distinct
		case
			when VizitPLStom.EvnVizitPLStom_IsPrimaryVizit = 2
					and extract(year from age(PLStom.EvnPLStom_disDate, ps.Person_BirthDay)) < 15
				then PLStom.EvnPLStom_id
			end) as v4,
	--5-9 
    count(
		case
			when UslugaComplex_Code in(
					'A16.07.002','A16.07.002.001','A16.07.002.002','A16.07.002.003','A16.07.002.004',
					'A16.07.002.005','A16.07.002.006','A16.07.002.007','A16.07.002.008','A16.07.002.010',
					'A16.07.002.011','A16.07.002.012','A16.07.002.013','A16.07.002.014','A16.07.002.015',
					'A16.07.002.016','A16.07.003,A16.07.004','A16.07.031','A16.07.032','A16.07.033',
					'A16.07.052')
				then VizitPLStom.EvnVizitPLStom_id
			end) as v5,
    count(distinct
		case
			when DiagStom.Diag_Code in('K02.0','K02.1','K02.2','K02.3','K02.4','K02.8','K02.9')
					and UslugaComplex_Code in(
					'A16.07.002','A16.07.002.001','A16.07.002.002','A16.07.002.003','A16.07.002.004',
					'A16.07.002.005','A16.07.002.006','A16.07.002.007','A16.07.002.008','A16.07.002.010',
					'A16.07.002.011','A16.07.002.012','A16.07.002.013','A16.07.002.014','A16.07.002.015',
					'A16.07.002.016','A16.07.003,A16.07.004','A16.07.031','A16.07.032','A16.07.033',
					'A16.07.052') 
					and tooth.type_tooth = 1
				then VizitPLStom.EvnVizitPLStom_id
			end) as v6,
    count(distinct
		case
			when DiagStom.Diag_Code in('K02.0','K02.1','K02.2','K02.3','K02.4','K02.8','K02.9')
					and UslugaComplex_Code in(
					'A16.07.002','A16.07.002.001','A16.07.002.002','A16.07.002.003','A16.07.002.004',
					'A16.07.002.005','A16.07.002.006','A16.07.002.007','A16.07.002.008','A16.07.002.010',
					'A16.07.002.011','A16.07.002.012','A16.07.002.013','A16.07.002.014','A16.07.002.015',
					'A16.07.002.016','A16.07.003,A16.07.004','A16.07.031','A16.07.032','A16.07.033',
					'A16.07.052') 
					and tooth.type_tooth = 2
				then VizitPLStom.EvnVizitPLStom_id
			end) as v7,
    count(distinct
		case
			when DiagStom.Diag_Code in(
					'K04.0','K04.1','K04.2','K04.3','K04.4','K04.5','K04.6','K04.7','K04.8','K04.9')
					and UslugaComplex_Code in(
					'A16.07.002','A16.07.002.001','A16.07.002.002','A16.07.002.003','A16.07.002.004',
					'A16.07.002.005','A16.07.002.006','A16.07.002.007','A16.07.002.008','A16.07.002.010',
					'A16.07.002.011','A16.07.002.012','A16.07.002.013','A16.07.002.014','A16.07.002.015',
					'A16.07.002.016','A16.07.003,A16.07.004','A16.07.031','A16.07.032','A16.07.033',
					'A16.07.052')
					and tooth.type_tooth = 1
				then VizitPLStom.EvnVizitPLStom_id
			end) as v8,
    count(distinct
		case
			when DiagStom.Diag_Code in(
					'K04.0','K04.1','K04.2','K04.3','K04.4','K04.5','K04.6','K04.7','K04.8','K04.9')
					and UslugaComplex_Code in(
					'A16.07.002','A16.07.002.001','A16.07.002.002','A16.07.002.003','A16.07.002.004',
					'A16.07.002.005','A16.07.002.006','A16.07.002.007','A16.07.002.008','A16.07.002.010',
					'A16.07.002.011','A16.07.002.012','A16.07.002.013','A16.07.002.014','A16.07.002.015',
					'A16.07.002.016','A16.07.003,A16.07.004','A16.07.031','A16.07.032','A16.07.033',
					'A16.07.052')
					and tooth.type_tooth = 2
				then VizitPLStom.EvnVizitPLStom_id
			end) as v9,
    count(distinct
		case
			when DiagStom.Diag_Code in(
					'K04.0','K04.1','K04.2','K04.3','K04.4','K04.5','K04.6','K04.7','K04.8','K04.9')
				--	and UslugaComplex_code not in ('A16.07.001.001','A16.07.001.002','A16.07.001.003')
				AND usl_del.EvnUslugaStom_id IS null
					then VizitPLStom.EvnVizitPLStom_id
			end) as v10,
    count(distinct
		case
			when UslugaComplex_Code in ('A16.07.002.007','A16.07.002.008')
				then VizitPLStom.EvnVizitPLStom_id
			end) as v11,
    count(distinct
		case
			when DiagStom.Diag_Code in ('K05.2','K05.3','K05.4','K05.5','K05.6')
					and PLStom.EvnPLStom_disDate between p_begdate and p_enddate
				then VizitPLStom.EvnVizitPLStom_id
			end) as v12,
    count(distinct
		case
			when PLStom.EvnPLStom_disDate between p_begdate and p_enddate
					and DiagStom.Diag_Code in(
						'A36.8', 'A38', 'A50.9', 'A51.3', 'A52.9', 'A69.0', 'A69.1', 'B00.2', 'B01.8', 
						'B05.8', 'B06.8', 'B37.0', 'K12.0', 'K12.1', 'K13.1', 'K13.2', 'K13.3', 'K13.4',
						'K13.5', 'K13.6', 'K13.7', 'K13.0', 'K14.0', 'K14.1', 'K14.2', 'K14.3', 'K14.4', 
						'K14.5', 'K14.6', 'K14.8', 'K14.9', 'L10.0', 'L10.1', 'L10.2', 'L10.3', 'L10.4', 
						'L10.5', 'L10.8', 'L10.9', 'L43.0', 'L43.1', 'L43.2', 'L43.3', 'L43.8', 'L43.9', 
						'L51.0', 'L51.1', 'L51.2', 'L51.8', 'L51.9', 'M32.0', 'M32.1', 'M32.8', 'M32.9') 
				then VizitPLStom.EvnVizitPLStom_id
			end) as v13,
	count(distinct
		case
			when UslugaComplex_Code IN ('A16.07.001.001','A16.07.001.006')
				then VizitPLStom.EvnVizitPLStom_id
			end) as v14,
	count(distinct
		case
			when UslugaComplex_Code IN ('A16.07.001.001','A16.07.001.006')
					and DiagStom.Diag_Code in ('K05.2','K05.3','K05.4','K05.5','K05.6')
				then VizitPLStom.EvnVizitPLStom_id
			end) as v15,
	count(distinct
		case
			when UslugaComplex_Code in (/*'A16.07.001.001',*/'A16.07.001.002', 'A16.07.001.003',
			'A16.07.001','A16.07.001.004','A16.07.001.005')
				then VizitPLStom.EvnVizitPLStom_id end) v16,
	coalesce(sum(
		case
			when UslugaComplex.UslugaComplex_Code in(
					'A15.03.007', 'A15.04.002', 'A15.07.001', 'A16.01.003', 'A16.01.008.001', 'A16.01.009',
					'A16.01.009.001', 'A16.01.009.002', 'A16.01.010', 'A16.01.011', 'A16.01.012', 
					'A16.01.012.005', 'A16.01.013', 'A16.01.016','A16.01.017', 'A16.01.018', 'A16.01.023', 
					'A16.03.007', 'A16.03.015', 'A16.04.018', 'A16.04.018.001', 'A16.07.001.005', 
					'A16.06.002', 'A16.07.007', 'A16.07.011', 'A16.07.013','A16.07.014', 'A16.07.015', 
					'A16.07.016', 'A16.07.017', 'A16.07.017.001', 'A16.07.017.002', 'A16.07.017.003', 
					'A16.07.017.004', 'A16.07.024', 'A16.07.026', 'A16.07.026.001', 'A16.07.038',
					'A16.07.038.001', 'A16.07.040', 'A16.07.042', 'A16.07.043', 'A16.07.044',
					'A16.07.044.001', 'A16.07.045', 'A16.07.058', 'A16.07.059', 'A16.07.060', 'A16.22.012',
					'A16.22.012.001', 'A16.30.032','A17.07.003')
					and not (DiagStom.Diag_Code between 'K02.0' and 'K02.09'
						or DiagStom.Diag_Code between 'K04.0' and 'K04.9')
				then coalesce(EvnUslugaStom.EvnUslugaStom_Kolvo, 0)
			else 0 end), 0) as v17,
	count(distinct
		case
			when PLStom.SanationStatus_id = 2
					and PLStom.EvnPLStom_disDate between p_begdate and p_enddate
				then VizitPLStom.EvnVizitPLStom_id
			end) as v18,
    count(distinct
		case
			when PLStom.EvnPLStom_disDate between p_begdate and p_enddate
					and Vizittype.VizitType_SysNick = 'prof'
				then PLStom.EvnPLStom_id
			end) as v19,
    count(distinct
		case
			when PLStom.EvnPLStom_disDate between p_begdate and p_enddate
					and EvnPLStom_ISSan = 2
					and VizitType.VizitType_SysNick = 'prof'
				then PLStom.EvnPLStom_id
			end) as v20,
	count(distinct
		case
			when PLStom.EvnPLStom_disDate between p_begdate and p_enddate
					and EvnPLStom_ISSan = 2
					and PLStom.SanationStatus_id in (1,2)
					and VizitType.VizitType_SysNick = 'prof' 
				then PLStom.EvnPLStom_id
			end) as v21,
   count(distinct
		case
			when PLStom.EvnPLStom_isFinish = 2
					and PLStom.EvnPLStom_disDate between p_begdate and p_enddate
					and PLStom.SanationStatus_id < 3
					and VizitType.VizitType_SysNick = 'prof'
				then PLStom.EvnPLStom_id
			end) as v22, 
	max(PLStom_Uet) as v23
from params
    inner join v_EvnVizitPLStom as VizitPLStom on (VizitPLStom.Lpu_id = p_lpu_id or p_lpu_id is null)
	inner join v_EvnPLStom as PLStom on VizitPLStom.EvnVizitPLStom_pid = PLStom.EvnPLStom_id 
	inner join v_personstate as ps on ps.person_id = PLStom.person_id
	inner join v_LpuSection as LpuSection on VizitPLStom.LpuSection_id = LpuSection.LpuSection_id
	left join v_VizitType as Vizittype on Vizittype.Vizittype_id = VizitPLStom.Vizittype_id 
	left join lateral(	
		select
			EDPLS.Diag_id,
			case
				when Tooth.Tooth_Code between '11' and '18'
						or Tooth.Tooth_Code between '21' and '28'
						or Tooth.Tooth_Code between '31' and '38' 
						or Tooth.Tooth_Code between '41' and '48'
					then 1--Постоянные: учитывается номер зуба: 11-18, 21-28, 31-38, 41-48
				when Tooth.Tooth_Code between '51' and '55'
						or Tooth.Tooth_Code between '61' and '65'
						or Tooth.Tooth_Code between '71' and '75' 
						or Tooth.Tooth_Code between '81' and '85'
					then 2 --Молочные: учитывается номер зуба: 51-55, 61-65, 71-75, 81-85
				end type_tooth
			from v_EvnDiagPLStom as EDPLS 
				left join v_Tooth as Tooth on Tooth.Tooth_id = EDPLS.Tooth_id
			where VizitPLStom.EvnVizitPLStom_id = EDPLS.EvnDiagPLStom_pid
			limit 1
		)tooth on true
	left join v_Diag as DiagStom on DiagStom.Diag_id = tooth.Diag_id
	left join v_EvnUSluga as EvnUSluga on VizitPLStom.EvnVizitPLStom_id = EvnUSluga.EvnUsluga_pid
	left join v_EvnUSlugaStom as EvnUslugaStom on EvnUslugaStom.EvnUslugaStom_id = EvnUsluga.EvnUsluga_id
		and EvnUslugaStom.lpu_id=EvnUSluga.lpu_id
	left join v_UslugaComplex as UslugaComplex
		on UslugaComplex.UslugaComplex_id = EvnUslugaStom.UslugaComplex_id
	left join lateral (
				SELECT eu.EvnUSlugaStom_id FROM v_EvnUSlugaStom eu
				left join v_UslugaComplex as uc
					on uc.UslugaComplex_id = eu.UslugaComplex_id
				WHERE  eu.EvnUslugaStom_rid=PLStom.EvnPLStom_id
				AND UslugaComplex_Code in ('A16.07.001','A16.07.001.001','A16.07.001.002', 'A16.07.001.003',
				'A16.07.001.004','A16.07.001.005','A16.07.001.006')
				limit 1 
				) usl_del on true
	left join uet on uet.EvnVizitPLStom_setDate = VizitPLStom.EvnVizitPLStom_setDate
where true
	and VizitPLStom.EvnVizitPLStom_setDate between p_begdate and p_enddate
	and PLStom.EvnPLStom_IsFinish = 2
	and  (VizitPLStom.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
	and (LpuSection.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
	and (VizitPLStom.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
	and (LpuSection.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
group by VizitPLStom.EvnVizitPLStom_setDate
order by 1
