-- frm30R3_2101_2020_pg.rptdesign
with 
  param as (select
   $1::text as p_lpulist, 
   $2::text as p_lpufiliallist, 
   $3::int8 as p_lpubuilding_id,
   $4::int8 as p_lpuunit_id, 
   $5::int8 as p_lpusection_id, 
   $6::int8 as p_medpersonal_id, 
   to_timestamp($7,'dd.mm.yyyy')::timestamp as p_begdate, 
   to_timestamp($8,'dd.mm.yyyy')::timestamp as p_enddate, 
   $9::int8 as p_includedopdisp, 
   $10::int8 as p_includeprofosm, 
   $11::int8 as p_includedisporp, 
   $12::int8 as p_includeteendisp, 
   $13::int8 as p_treatmentclass_id, 
   $14::int8 as p_paytype_id, 
   $15::int8 as p_isguzonly
  ),
  Lpu_W as(
	select Lpu_id
	from param inner join dbo.v_Lpu as L on true
		inner join dbo.Org as O on O.Org_id = L.Org_id
	where true
		and(L.Lpu_id in(select id::int8 as id from unnest(string_to_array(p_LpuList, ',')) as id)
			or L.Lpu_pid in(select id::int8 as id from unnest(string_to_array(p_LpuList, ',')) as id)
			or (coalesce(rtrim(p_LpuList), '') = '' and coalesce(L.Lpu_IsTest, 1) = 1)
		)
		and L.Lpu_begDate < p_EndDate::date + interval'1year'
		and coalesce(L.Lpu_endDate, date'infinity') >= p_BegDate
		and
			case
				when dbo.GetRegion() = 59 and L.Lpu_id in (13002556,10011362) or O.Okfs_id in (2,4,5)
					then 2
				else 1 end >= coalesce(p_IsGUZOnly,1)
), 
LpuSection_W as(
	select
		LS.LpuSection_id,
		LS.LpuSection_Code,
		LS.LpuSection_Name,
		LS.LpuSectionType_id,
		LSProf.LpuSectionProfile_Code,
		LUT.LpuUnitType_SysNick,
		LU.LpuUnit_Name,
		LU.LpuUnitProfile_fid,
		LS.PalliativeType_id,
    lb.LpuBuilding_name,
    LS.FRMPSubdivision_id
	from param inner join dbo.LpuSection as LS on true
		left join dbo.LpuUnit as LU on LS.LpuUnit_id = LU.LpuUnit_id
		left join dbo.LpuUnitType as LUT on LUT.LpuUnitType_id = LU.LpuUnitType_id
		left join dbo.LpuBuilding as LB on LU.LpuBuilding_id = LB.LpuBuilding_id
		left join Lpu_W as L on LB.Lpu_id = L.Lpu_id 
		left join dbo.LpuSectionProfile as LSProf on LSProf.LpuSectionProfile_id = LS.LpuSectionProfile_id
	where true
		and coalesce(LS.LpuSection_Deleted, 1) = 1
		and (LS.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
		and (LU.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
		and (LB.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
		and (LB.LpuFilial_id in(
			select id::int8 as id from unnest(string_to_array(p_LpuFilialList, ',')) as id)
			or coalesce(rtrim(p_LpuFilialList), '') = '')
		and L.Lpu_id > 0
),
vizits as (
-- ТАП
select 
	'TAP' as EvnType,
	EVPL.Evn_id as Evn_id,
	LS.LpuUnit_Name,
	LS.LpuUnitType_SysNick,
	ls.FRMPSubdivision_id,
	LS.LpuSection_id,
	LS.LpuSection_Name,
	LS.LpuSectionProfile_Code,
	LS.LpuSectionType_id,
	MSF.Post_id,
	EVPL.Person_id,
	EVPL.Evn_setDT as EvnDate,
	EVPL.PersonEvn_id,
	EVPL.Server_id,
	EVPL.TreatmentClass_id,
	ST.ServiceType_SysNick,
	EVPL.Diag_id,
	UC.UslugaComplex_Code as VizitUsluga,
	LS.LpuUnitProfile_fid,
	LS.PalliativeType_id,ls.LpuBuilding_name
from param 
  --inner join Lpu_W as L on true
	inner join dbo.EvnPL as EPL on true-- EPL.Lpu_id = L.Lpu_id
	inner join dbo.EvnVizitPL as EVPL on EVPL.Evn_pid = EPL.Evn_id
	inner join LpuSection_W as LS on EVPL.LpuSection_id = LS.LpuSection_id
	inner join dbo.v_MedStaffFact as MSF on MSF.MedStaffFact_id = EVPL.MedStaffFact_id
	left join persis.Post as p on p.id = MSF.Post_id
	left join dbo.v_UslugaComplex as UC on UC.UslugaComplex_id = EVPL.UslugaComplex_id 
	left join dbo.v_ServiceType as ST on ST.ServiceType_id = EVPL.ServiceType_id
where true
  and epl.evn_deleted = 1
	and evpl.evn_deleted = 1
	and p.PostKind_id = 6
	and EPL.EvnClass_id in (3,6)
	and EPL.Evn_setDT <= p_EndDate + interval '1 day' - interval '1 minute'
	and coalesce(EPL.Evn_disDT, p_BegDate) >= p_BegDate
	and EVPL.EvnClass_id in (11,13)
	and EVPL.Evn_setDT between p_BegDate and p_EndDate + interval '1 day' - interval '1 minute'
  and (EVPL.PayType_id =  p_PayType_id or (EVPL.PayType_id is null and p_PayType_id = -2) or p_PayType_id is null)
	and (EVPL.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
  and (EVPL.TreatmentClass_id =  p_TreatmentClass_id or (EVPL.TreatmentClass_id is null and p_TreatmentClass_id = -2) or p_TreatmentClass_id is null)
-- ДВН, ДДС, ПОВН, МОН
union all 
select 
	case
		when EPLD.DispClass_id = 5 then 'POVN'
		when EPLD.DispClass_id in (1,2) then 'DVN'
		when EPLD.DispClass_id in (6,9,10,11,12) then 'MON'
		when EPLD.DispClass_id in (3,4,7,8) then 'DDS'
		end as EvnType,
	EVD.Evn_id as Evn_id,
	LS.LpuUnit_Name,
	LS.LpuUnitType_SysNick,
	ls.FRMPSubdivision_id,
	LS.LpuSection_id,
	LS.LpuSection_Name,
	LS.LpuSectionProfile_Code,
	LS.LpuSectionType_id,
	MSF.Post_id,
	EPLD.Person_id,
	EVD.Evn_setDT as EvnDate,
	EVD.PersonEvn_id,
	EVD.Server_id,
	case when EPLD.DispClass_id in (1,2, 3,4,7,8) then 7 else 6 end as TreatmentClass_id,
	'polka' as ServiceType_SysNick,
	EVD.Diag_id,
	null as VizitUsluga,
	LS.LpuUnitProfile_fid,
	LS.PalliativeType_id,
  ls.LpuBuilding_name
from param 
  --inner join Lpu_W as L on true
	inner join dbo.EvnPLDisp as EPLD on true -- EPLD.Lpu_id = L.Lpu_id
	inner join dbo.EvnVizitDisp as EVD on EPLD.Evn_id = EVD.Evn_pid 
	inner join LpuSection_W as LS on EVD.LpuSection_id = LS.LpuSection_id
	inner join dbo.v_MedStaffFact as MSF on MSF.MedStaffFact_id = EVD.MedStaffFact_id
	left join persis.Post as p on p.id = MSF.Post_id
	left join dbo.v_DopDispInfoConsent as DDIC on DDIC.DopDispInfoConsent_id = EVD.DopDispInfoConsent_id
	left join dbo.v_SurveyTypeLink as STL on STL.SurveyTypeLink_id = DDIC.SurveyTypeLink_id
	left join dbo.v_SurveyType as ST on ST.SurveyType_id = STL.SurveyType_id
where true
	and(ST.SurveyType_Name ilike '%осмотр%врач%'
		or EPLD.DispClass_id in (4,8)
		or EVD.Diag_id > 0 and EPLD.DispClass_id between 11 and 12)
	and p.PostKind_id = 6
	and EPLD.EvnClass_id in (7,8,9,101,103,104)
	and EPLD.Evn_setDT <= p_EndDate  + interval '1 day' - interval '1 minute'
	and coalesce(EPLD.Evn_disDT, p_BegDate) >= p_BegDate
	and(
		p_IncludeDopDisp = 2 and EPLD.DispClass_id in (1,2) and coalesce(p_TreatmentClass_id, 7) = 7
		or p_IncludeProfOsm = 2 and EPLD.DispClass_id = 5 and coalesce(p_TreatmentClass_id, 6) = 6
		or p_IncludeTeenDisp = 2 and EPLD.DispClass_id in (6,9,10,11,12)
			and coalesce(p_TreatmentClass_id, 6) = 6
		or p_IncludeDispOrp = 2 and EPLD.DispClass_id in (3,4,7,8)
			and coalesce(p_TreatmentClass_id, 7) = 7
	) 
	and (coalesce(EPLD.PayType_id, -2) = p_PayType_id or p_PayType_id is null)
	and EVD.EvnClass_id between 14 and 15 -- 14 - ДВН, ПОВН, МОН; 15 - ДДС
	and EVD.Evn_setDT between p_BegDate and p_EndDate  + interval '1 day' - interval '1 minute'
	and EVD.Evn_setDT >=  coalesce(EPLD.EvnPLDisp_consDt, p_BegDate)
	and (EVD.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)

)

select
coalesce(COUNT(e.Evn_id), 0) as val3_1,
coalesce(COUNT(case when Post_id in (120,10209,116) then e.Evn_id end), 0) as val4_1,
coalesce(COUNT(case when E.FRMPSubdivision_id in (10149,10409,10408)  then Evn_id end), 0) as val3_2,
coalesce(COUNT(case when E.FRMPSubdivision_id in (10149,10409,10408) and Post_id in (120,10209,116) then Evn_id end), 0) as val4_2,
coalesce(COUNT(case when E.FRMPSubdivision_id in (10149,10409,10408)  and peredv.AttributeValue_Value=6 and E.EvnDate between AttributeSignValue_begDate and coalesce(AttributeSignValue_endDate,p_enddate) then Evn_id end), 0) as val3_21,
coalesce(COUNT(case when E.FRMPSubdivision_id in (10149,10409,10408) and Post_id in (120,10209,116)  and peredv.AttributeValue_Value=6 and E.EvnDate between AttributeSignValue_begDate and coalesce(AttributeSignValue_endDate,p_enddate) then Evn_id end), 0) as val4_21,
coalesce(COUNT(case when E.FRMPSubdivision_id=10407 then Evn_id end), 0) as val3_3,
coalesce(COUNT(case when E.FRMPSubdivision_id=10407 and Post_id in (120,10209,116) then Evn_id end), 0) as val4_3,
coalesce(COUNT(case when E.FRMPSubdivision_id=10407  and peredv.AttributeValue_Value=7 and E.EvnDate between AttributeSignValue_begDate and coalesce(AttributeSignValue_endDate,p_enddate) then Evn_id end), 0) as val3_31,
coalesce(COUNT(case when E.FRMPSubdivision_id=10407 and Post_id in (120,10209,116) and peredv.AttributeValue_Value=7 and E.EvnDate between AttributeSignValue_begDate and coalesce(AttributeSignValue_endDate,p_enddate) then Evn_id end), 0) as val4_31,  
coalesce(COUNT(case when E.FRMPSubdivision_id=10342  then Evn_id end), 0) as val3_4,
coalesce(COUNT(case when E.FRMPSubdivision_id=10342 and Post_id in (120,10209,116) then Evn_id end), 0) as val4_4,  
coalesce(COUNT(case when E.FRMPSubdivision_id=10342  and PersonAddress.KLAreaType_id = 2  then Evn_id end), 0) as val3_41,
coalesce(COUNT(case when  Post_id in (120,10209,116)  and E.FRMPSubdivision_id=10342 and PersonAddress.KLAreaType_id = 2 then Evn_id end), 0) as val4_41, 
coalesce(COUNT(case when E.FRMPSubdivision_id=10342 and dbo.AgeYMD(Person.Person_BirthDay, E.EvnDate, 1) >= 18 then Evn_id end), 0) as val3_42, 
coalesce(COUNT(case when Post_id in (120,10209,116)  and E.FRMPSubdivision_id=10342 and dbo.AgeYMD(Person.Person_BirthDay, E.EvnDate, 1) >= 18 then Evn_id end), 0) as val4_42,
coalesce(COUNT(case when E.FRMPSubdivision_id=10342 and dbo.AgeYMD(Person.Person_BirthDay, E.EvnDate, 1) between 0 and 17 then Evn_id end), 0) as val3_43,
coalesce(COUNT(case when Post_id in (120,10209,116)  and E.FRMPSubdivision_id=10342 and dbo.AgeYMD(Person.Person_BirthDay, E.EvnDate, 1) between 0 and 17 then Evn_id end), 0) as val4_43,
coalesce(COUNT(case when E.FRMPSubdivision_id in (10208,10207,10459,10286) then Evn_id end ), 0) as val3_5,
coalesce(COUNT(case when E.FRMPSubdivision_id in (10208,10207,10459,10286) and Post_id in (120,10209,116) then Evn_id end ), 0) as val4_5,
coalesce(COUNT(case when 	E.FRMPSubdivision_id in (10208,10207,10459,10286) and peredv.AttributeValue_Value=1 and E.EvnDate between AttributeSignValue_begDate and coalesce(AttributeSignValue_endDate,p_enddate)  then Evn_id end ), 0) as val3_51, 
coalesce(COUNT(case when 	Post_id in (120,10209,116) 	and E.FRMPSubdivision_id in (10208,10207,10459,10286) and peredv.AttributeValue_Value=1 and E.EvnDate between AttributeSignValue_begDate and coalesce(AttributeSignValue_endDate,p_enddate) then Evn_id end ), 0) as val4_51
from param 
  inner join vizits e on true
  inner join dbo.v_Person_all Person  on E.PersonEvn_id = Person.PersonEvn_id
  and Person.Server_id = E.Server_id
  and E.Post_id not in (115,142) -- исключены "115 Зубной врач", "142 Гигиенист стоматологический" - идут в таб 2700
left join dbo.Address PersonAddress  on PersonAddress.Address_id = coalesce(Person.PAddress_id, Person.UAddress_id)
left join lateral
(
select mob.AttributeValue_Value,
				ASV.AttributeSignValue_id,
				ASV.AttributeSignValue_TablePKey,
				ASV.AttributeSignValue_begDate,
				ASV.AttributeSignValue_endDate
			from
				v_AttributeSignValue ASV
				inner join v_AttributeSign ats on ats.AttributeSign_id = ASV.AttributeSign_id
				left join lateral
				(
						select
						AV.AttributeSignValue_id,
						AV.AttributeValue_id,
						A.Attribute_SysNick,
						AV.AttributeValue_ValueIdent as AttributeValue_Value,						
						AttributeValue_ValueText
					from
						v_AttributeValue AV 
						inner join v_Attribute A  on A.Attribute_id = AV.Attribute_id
					where (1=1) and AV.AttributeSignValue_id=ASV.AttributeSignValue_id
					and lower(A.Attribute_SysNick)='mobileunittype'
				) mob on true
			where
				lower(ats.AttributeSign_TableName) like 'dbo.lpusection'
				and ASV.AttributeSignValue_TablePKey=e.LpuSection_id
				and ats.AttributeSign_id=14
				and  mob.AttributeValue_Value in (1,6,7)
) peredv on true

