with params as(
	select
		cast($1 as int8) as p_lpu_id,
		cast($2 as int8) as p_lpubuilding_id,
		cast($3 as int8) as p_lpuunit_id, 
		cast($4 as int8) as p_lpusection_id, 
		cast($5 as int8) as p_medpersonal_id, 
		cast($6 as int8) as p_lpuregion_id, 
		cast(to_timestamp($7,'dd.mm.yyyy') as timestamp) as p_begdate, 
		cast(to_timestamp($8,'dd.mm.yyyy') as timestamp) as p_enddate, 
		$9 as p_lpufiliallist, 
		cast($10 as int8) as p_paytype_id, 
		cast($11 as int8) as p_isguzonly
),

L as(
	select
		L.Lpu_id,
		L.UAddress_id,
		O.Okfs_id,
		params.*
	from params
		left join v_Lpu as L
			on (L.Lpu_id = p_Lpu_id or p_Lpu_id is null and 1 = 
				case
					when L.Lpu_id in (100,101) then 0
					when dbo.GetRegion() = 59 and L.Lpu_id in (13002677, 13002714, 150000) then 0
					else 1 end)
		left join Org as O on O.Org_id = L.Org_id
	where true
		and coalesce(cast(L.Lpu_begDate as date), p_EndDate) <=  p_EndDate
		and coalesce(cast(L.Lpu_endDate as date), p_BegDate) >=  p_BegDate
		and case when O.Okfs_id in (2,4,5) then 2 else 1 end >=  coalesce(p_IsGUZOnly,1)
),

RD as(
	select distinct
		t_table.num,
		t_table.row_num,
		t_table.UslugaKind_Name,
		t_table.sex,
		t_table.age,
		codes.zero_res_count,
		codes.code
	from(values
		(1,'1','Число рентгеновских профилактических исследований органов грудной клетки, всего,  в том числе выполнено:',null,null),
		(2,'1.1','   на пленочных флюорографах',null,null),
		(3,'1.1.1','   из них на передвижных пленочных флюорографических установках',null,null),
		(4,'1.2','   на цифровых флюорографах',null,null),
		(5,'1.2.1','   из них на передвижных цифровых флюорографических установках',null,null),
		(6,'1.3','   рентгенографий на пленке',null,null),
		(7,'1.4','   низкодозных компьютерных томографий',null,null),
		(8,'2','Число рентгеновских профилактических исследований молочных желез',2,18),
		(9,'2.1','   из них выполнено: на пленочных аппаратах',2,18),
		(10,'2.2','   на цифровых аппаратах и аппаратах, оснащенных системой  компьютерной радиографии',2,18),
		(11,'2.3','   на передвижных маммографических установках',2,18),
		(12,'2.4','   на аппаратах с функцией томосинтеза',2,18)
	) as t_table(num, row_num, UslugaKind_Name, sex, age)
		left join(
			select
				codes.num,
				codes.zero_res_count,
				codes.code
			from(values
				(1,null,'A06.09.007'),
				(1,null,'A06.09.005'),
				(1,null,'A06.09.007.002'),
				(2,2,'A06.09.006'),
				(2,2,'A06.09.006.999.001'),
				(3,2,'A06.09.006.999.001'),
				(4,null,'A06.09.006.001'),
				(4,1,'A06.09.006'),
				(4,1,'A06.09.006.999.001'),
				(5,1,'A06.09.006.999.001'),
				(6,null,'A06.09.007'),
				(7,null,'A06.09.005'),
				(8,null,'A06.20.004'),
				(8,null,'A06.20.004.001'),
				(8,null,'A06.20.004.006'),
				(8,null,'A06.20.004.999'),
				(8,null,'A06.20.004.007'),
				(8,null,'A06.20.008'),
				(9,null,'A06.20.004.001'),
				(9,2,'A06.20.004'),
				(9,2,'A06.20.004.999'),
				(10,null,'A06.20.004.006'),
				(10,null,'A06.20.004.007'),
				(10,1,'A06.20.004'),
				(10,1,'A06.20.004.999'),
				(11,null,'A06.20.004.999'),
				(12,null,'A06.20.008')
			) as codes(num, zero_res_count, code)
			
			union all select
				1 as num,
				null as zero_res_count,
				uc.UslugaComplex_Code
			from dbo.v_UslugaComplex as uc
			where uc.UslugaComplex_Code between 'A06.09.006' and 'A06.09.006.999.001'
		) as codes on codes.num = t_table.num
),

rep as(
	select
		UC.UslugaComplex_code,
		PS.Sex_id,
		dbo.Age(PS.Person_BirthDay,EU.EvnUsluga_setDate) as age,
		case
			when diag_digital.res_count <> 0
				then diag_digital.res_count
			else files_digital.files_count end as res_count,
		sum(coalesce(EU.EvnUsluga_Kolvo,1)) as v3,
		sum(
			case
				when dbo.Age(PS.Person_BirthDay,EU.EvnUsluga_setDate) < 18
					then coalesce(EU.EvnUsluga_Kolvo,1) end) as v4,
		sum(
			case  when rpt.WorkAge(ps.Person_BirthDay,ps.Sex_id,EU.EvnUsluga_setDate)=1
				--when PS.Sex_id = 1 and dbo.Age(PS.Person_BirthDay,EU.EvnUsluga_setDate) > 59
					--	or PS.Sex_id = 2 and dbo.Age(PS.Person_BirthDay,EU.EvnUsluga_setDate) > 54
					then coalesce(EU.EvnUsluga_Kolvo,1) end) as v5
	from v_EvnUsluga as EU 
		inner join L on L.Lpu_id = EU.lpu_id
		left join v_EvnDirection_all as ED on ED.EvnDirection_id = EU.EvnDirection_id
		left join v_EvnFuncRequest as EFR on EFR.EvnFuncRequest_pid = ED.EvnDirection_id
		left join StudyTarget as ST on ST.StudyTarget_id = coalesce(EFR.StudyTarget_id,ED.StudyTarget_id)
		inner join v_LpuSection as LS on EU.LpuSection_uid = LS.LpuSection_id
		inner join v_LpuUnit as LU on LS.LpuUnit_id = LU.LpuUnit_id
		inner join v_LpuBuilding as Building on LU.LpuBuilding_id = Building.LpuBuilding_id
		inner join v_UslugaComplex as UC on EU.UslugaComplex_id = UC.UslugaComplex_id
		inner join v_PersonState as PS on EU.Person_id = PS.Person_id
		left join v_EvnUslugaPar as EUPar on EU.EvnUsluga_id = EUPar.EvnUslugaPar_id
		left join lateral(
			select PC.LpuRegion_id
			from v_PersonCard_all as PC 
			where PS.Person_id = PC.Person_id
				and PC.Lpu_id = EU.Lpu_id
				and Pc.PersonCard_begDate <=  p_EndDate
				and coalesce(PC.PersonCard_endDate,p_BegDate) >=  p_BegDate
				and PC.LpuRegion_id = p_LpuRegion_id
			limit 1
		)PC on true
		
		left join lateral(
			select count(distinct res.EvnUslugaParAssociatedResearches_id) as res_count
			from v_EvnUslugaParAssociatedResearches as res
			where evnuslugapar_id = EUPar.EvnUslugaPar_id
		)diag_digital on true --наличие цифровых снимков #142927
		
		left join lateral(
			select count(distinct media.EvnMediaData_id) as files_count
			from EvnMediaData as media 
			where media.Evn_id = EUPar.EvnUslugaPar_id
				and (media.EvnMediaData_FileName like '%.jpg%'
					or media.EvnMediaData_FileName like '%.jpeg%'
					or media.EvnMediaData_FileName like '%.tiff%'
					or media.EvnMediaData_FileName like '%.png%')
		)files_digital on true

	where EU.EvnUsluga_setDate between p_BegDate and p_EndDate
		and coalesce(ST.StudyTarget_Code,3) = 3 --Профилактическая
		and (LU.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
		and (LU.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
		and (LS.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
		and (EU.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
		and (PC.LpuRegion_id = p_LpuRegion_id or p_LpuRegion_id is null)
		and (EU.PayType_id = p_PayType_id or p_PayType_id is null) 
		and (Building.LpuFilial_id in(
			select id::int8 as id from unnest(string_to_array(p_LpuFilialList, ',')) as id)
			or coalesce(rtrim(p_LpuFilialList), '') = '')
		and not exists(
			select ES.EvnSection_id as id
			from v_EvnSection as ES 
				inner join L on L.Lpu_id = ES.lpu_id
			where ES.EvnSection_id = EU.EvnUsluga_pid
			
			union all select evpl.EvnVizitPL_id
			from v_EvnvizitPL as evpl
				inner join v_TreatmentClass as tc on TC.TreatmentClass_id = evpl.TreatmentClass_id
					and left(TC.TreatmentClass_Code,1) != '2'
				inner join L on L.Lpu_id = evpl.lpu_id
			where EVPL.EvnVizitPL_id = EU.EvnUsluga_pid
		)
	group by
		UC.UslugaComplex_code,
		PS.Sex_id,
		dbo.Age(PS.Person_BirthDay,EU.EvnUsluga_setDate),
		case when diag_digital.res_count <> 0 then diag_digital.res_count else files_digital.files_count end
)

select
	RD.num,
	RD.row_num,
	RD.UslugaKind_Name as "UslugaKind_Name",
	coalesce(sum(rep.v3),0) as v3, 
	case when RD.num < 8 then coalesce(sum(rep.v4),0)::numeric end as v4,
	coalesce(sum(rep.v5),0) as v5
from RD 
	left join rep on rep.UslugaComplex_code = RD.code
		and (RD.Sex = rep.Sex_id or RD.Sex is null)
		and (RD.age <= rep.age or RD.age is null)
		and 1 = case
				when RD.zero_res_count = 1 and rep.res_count != 0 then 1
				when RD.zero_res_count = 2 and rep.res_count = 0 then 1
				when RD.zero_res_count is null then 1 end
group by 1,2,3
order by 1
