--frm30R3_2100_2020_pg.rptdesign
--frm30r3_20_2100
with params as
(
	select
	cast ($1 as varchar) as  p_lpulist,
	cast ($2 as varchar)  as  p_lpufiliallist,
	cast($3 as bigint) as p_lpubuilding_id,
	cast($4 as bigint) as p_lpuunit_id, 
	cast($5 as bigint) as p_lpusection_id, 
	cast($6 as bigint) as p_medpersonal_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, 
	cast($9 as bigint) as p_includedopdisp, 
	cast($10 as bigint) as p_includeprofosm, 
	cast($11 as bigint) as p_includedisporp, 
	cast($12 as bigint) as p_includeteendisp, 
	cast($13 as bigint) as p_treatmentclass_id, 
	cast($14 as bigint) as p_paytype_id, 
	cast($15 as bigint) as p_isguzonly
),

Lpu_W as
(
	select Lpu_id,p.*
	from params p
	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 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 dbo.LpuSection LS 
  	left join dbo.LpuUnit LU  ON LS.LpuUnit_id = LU.LpuUnit_id
  	left join dbo.LpuUnitType LUT  on LUT.LpuUnitType_id = LU.LpuUnitType_id
  	left join dbo.LpuBuilding LB  ON LU.LpuBuilding_id = LB.LpuBuilding_id
  	left join Lpu_W L  on LB.Lpu_id = L.Lpu_id 
  	left join dbo.LpuSectionProfile LSProf ON LSProf.LpuSectionProfile_id = LS.LpuSectionProfile_id
  	where (1 = 1)
		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 (',' || p_LpuFilialList  ||',' like '%,' || cast(LB.LpuFilial_id as varchar(30)) || ',%' or p_LpuFilialList is null)
		and L.Lpu_id > 0
),

EvnVizitDispDopOrp_W as -- dbo.v_EvnVizitDispDop и dbo.v_EvnVizitDispOrp
(
	select
	EvnVizit.EvnClass_id,
	EvnVizit.Evn_id as EvnVizitDispDop_id, 
	EvnVizit.Evn_pid as EvnVizitDispDop_pid,
	EvnVizit.Evn_setDT as EvnVizitDispDop_setDT,
	EvnVizit.Lpu_id,
	EvnVizit.Server_id,
	EvnVizit.PersonEvn_id,
	EvnVizit.MedStaffFact_id,
	EvnVizit.MedPersonal_id,
	EvnVizit.LpuSection_id,
	EvnVizitDisp.DopDispInfoConsent_id,
	EvnVizitDisp.Diag_id,
	EvnVizitDisp.Evn_id
 	from params
  	inner join dbo.EvnVizit EvnVizit on true
    	and EvnVizit.EvnClass_id between 14 and 15 -- 14 - ДВН, ПОВН, МОН; 15 - ДДС
		and EvnVizit.Evn_setDT between p_BegDate and p_EndDate + interval '1 day' - interval '1 minute'
		and (EvnVizit.Evn_deleted = 1 or EvnVizit.Evn_deleted is null)
  	inner join dbo.EvnVizitDisp EvnVizitDisp  on EvnVizit.Evn_id = EvnVizitDisp.Evn_id
),

EvnPLDisp_W as 
(
  	select
    EvnPLBase.EvnClass_id, 
    EvnPLBase.Evn_id AS EvnPLDisp_id, 
	EvnPLBase.Evn_setDT AS EvnPLDisp_setDT,
    EvnPLDisp.EvnPLDisp_consDT AS EvnPLDisp_consDT,
	EvnPLBase.Evn_disDT AS EvnPLDisp_disDT,
	EvnPLBase.Lpu_id,
	EvnPLDisp.DispClass_id,
	EvnPLBase.Person_id,
    EvnPLDisp.PayType_id,
	EvnPLBase.Person_Age
  	from params
  	inner join dbo.EvnPLBase EvnPLBase  ON true
    	and EvnPLBase.EvnClass_id IN (7,8,9,101,103,104) -- 9 - ДДС, остальное - МОН, ПОВН, ДВН
		and EvnPLBase.Evn_setDT < p_EndDate + interval '1 day'
		and (EvnPLBase.Evn_disDT >= p_BegDate or EvnPLBase.Evn_disDT is null)
		and (EvnPLBase.Evn_deleted = 1 or EvnPLBase.Evn_deleted is null)
  	inner join dbo.EvnPLDisp EvnPLDisp  on EvnPLBase.Evn_id = EvnPLDisp.Evn_id
),

EvnVizitPL_W as 
(
  	select
    EvnVizit.Evn_id as EvnVizitPL_id, 
	EvnVizit.Evn_pid as EvnVizitPL_pid,
	EvnVizit.Evn_setDT as EvnVizitPL_setDT,
	EvnVizit.Lpu_id,
	EvnVizit.Server_id,
	EvnVizit.PersonEvn_id,
	EvnVizit.Person_id,
	EvnVizit.MedStaffFact_id,
	EvnVizit.MedPersonal_id,
	EvnVizit.LpuSection_id,
	EvnVizitPL.Diag_id,
	EvnVizit.PayType_id,
	EvnVizitPL.TreatmentClass_id,
	EvnVizitPL.ServiceType_id,
	EvnVizitPL.UslugaComplex_id,
	EvnVizitPL.VizitClass_id
  	from params
  	inner join dbo.EvnVizit EvnVizit on true
    	and EvnVizit.EvnClass_id in (11,13)
		and EvnVizit.Evn_setDT between p_BegDate and p_EndDate + interval '1 day' - interval '1 minute'
		and (EvnVizit.Evn_deleted = 1 or EvnVizit.Evn_deleted is null)
  	inner join dbo.EvnVizitPL EvnVizitPL  on EvnVizit.Evn_id = EvnVizitPL.Evn_id
),

EvnPL_W as 
(
  	select
    EvnPL.EvnClass_id, 
	EvnPL.Evn_id AS EvnPL_id, 
	EvnPL.Evn_setDT AS EvnPL_setDT,
	EvnPL.Evn_disDT AS EvnPL_disDT,
	EvnPL.Lpu_id,
	EvnPL.MedPersonal_id,
	EvnPL.LpuSection_id,
	EvnPL.Person_id,
	EvnPL.Diag_id,
	EvnPL.DeseaseType_id
  	from params
  	inner join dbo.EvnPL EvnPL ON true
    	and EvnPL.EvnClass_id IN (3,6) -- 3 - Пол-ка, 6 - Стоматология
		and EvnPL.Evn_setDT <  p_EndDate + interval '1 day'
		and (EvnPL.Evn_disDT >=  p_BegDate or EvnPL.Evn_disDT is null)
		and (EvnPL.Evn_deleted = 1 or EvnPL.Evn_deleted is null)
),

EvnPS_W as
(
  	select
    EvnPS.Evn_id as EvnPS_id, 
    EvnPS.Evn_setDT as EvnPS_setDT,
	EvnPS.Evn_disDT as EvnPS_disDT,
	EvnPS.Lpu_id,
	EvnPS.Person_id,
    EvnPS.Server_id,
	EvnPS.PersonEvn_id,
	EvnPS.LpuSection_pid,
	EvnPS.MedPersonal_pid,
	EvnPS.MedStaffFact_pid,
	EvnPS.PayType_id,
	EvnPS.EvnPS_OutcomeDT as EvnPS_OutcomeDT,
	EvnPS.Diag_pid,
	EvnPS.PrehospWaifRefuseCause_id
  	from params
  	inner join dbo.EvnPS EvnPS on true
    	and EvnPS.EvnClass_id = 30
		and EvnPS.Evn_setDT < p_EndDate + interval '1 day'
		and EvnPS.EvnPS_OutcomeDT between p_BegDate and p_EndDate + interval '1 day' - interval '1 minute'
		and (EvnPS.Evn_deleted = 1 or EvnPS.Evn_deleted is null)
		and (EvnPS.PrehospWaifRefuseCause_id > 0 or dbo.GetRegion() in (3,60)) 
),

EvnSection_W as
(
  	select
    EvnSection.Evn_id as EvnSection_id,
	EvnSection.Evn_Index as EvnSection_Index,
	EvnSection.Evn_Count as EvnSection_Count,
	EvnSection.Evn_setDT as EvnSection_setDT,
	EvnSection.Evn_disDT as EvnSection_disDT,
	EvnSection.Lpu_id,
	EvnSection.Evn_pid as EvnSection_pid,
	EvnSection.PayType_id,
	EvnSection.LeaveType_id,
	EvnSection.LeaveType_fedid,
	EvnSection.LeaveType_prmid,
	EvnSection.Diag_id,
	EvnSection.MedPersonal_id,
	EvnSection.MedStaffFact_id,
	EvnSection.LpuSection_id,
	EvnSection.LpuSectionProfile_id,
	EvnSection.EvnSection_IsPriem,
	EvnSection.HTMedicalCareClass_id,
	EvnSection.Mes_id,
	EvnSection.EvnSection_IndexNum,
	EvnSection.MesTariff_id,
	EvnSection.MedicalCareBudgType_id,
	EvnSection.UslugaComplex_id
 	from params
  	inner join dbo.EvnSection EvnSection on true
    	and EvnSection.EvnClass_id = 32
		and EvnSection.Evn_disDT between p_BegDate and p_EndDate + interval '1 day' - interval '1 minute'
		and (EvnSection.Evn_deleted = 1 or EvnSection.Evn_deleted is null)
		and EvnSection.EvnSection_IsPriem = 2
		and EvnSection.LeaveType_prmid in (358,463) 
		and dbo.GetRegion() in (3,60) 
),

vizits as (
-- ТАП
	select 
  	'TAP' as EvnType,
  	EVPL.EvnVizitPL_id as Evn_id,
  	LS.LpuUnit_Name,
  	LS.LpuUnitType_SysNick,
  	LS.LpuSection_Name,
  	LS.LpuSectionProfile_Code,
  	LS.LpuSectionType_id,
  	MSF.Post_id,
  	--EPL.Person_id,
  	EVPL.Person_id,
  	--EPL.Person_Age,
  	EVPL.EvnVizitPL_setDT as EvnDate,
  	EVPL.PersonEvn_id,
  	EVPL.Server_id,
  	EVPL.TreatmentClass_id,
  	tcc.TreatmentClass_code,
  	ST.ServiceType_SysNick,
  	EVPL.Diag_id,
  	UC.UslugaComplex_Code as VizitUsluga,
  	LS.LpuUnitProfile_fid,
  	LS.PalliativeType_id,ls.LpuBuilding_name,Ls.FRMPSubdivision_id
	from Lpu_W as L 
	inner join EvnPL_W as EPL  on EPL.Lpu_id = L.Lpu_id  
	  	and EPL.EvnPL_setDT < p_EndDate + interval '1 day'  
  		and (EPL.EvnPL_disDT >= p_BegDate or EPL.EvnPL_disDT is null)
	inner join EvnVizitPL_W as EVPL  on EVPL.EvnVizitPL_pid = EPL.EvnPL_id
  		and EVPL.EvnVizitPL_setDT between p_BegDate and p_EndDate + interval '1 day' - interval '1 minute'
  		and (coalesce(EVPL.PayType_id, -2) = p_PayType_id or p_PayType_id is null)
  		and (EVPL.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
  		and (coalesce(EVPL.TreatmentClass_id, -2) = p_TreatmentClass_id or p_TreatmentClass_id is null)
  	inner join dbo.v_MedStaffFact MSF on MSF.MedStaffFact_id = EVPL.MedStaffFact_id
	inner join persis.Post p  on p.id = MSF.Post_id and p.PostKind_id in (1,2,10,12)
	inner join LpuSection_W as LS  on EVPL.LpuSection_id = LS.LpuSection_id
	left join dbo.v_UslugaComplex UC  on UC.UslugaComplex_id = EVPL.UslugaComplex_id 
	left join dbo.v_ServiceType ST  on ST.ServiceType_id = EVPL.ServiceType_id
left join TreatmentClass tcc on tcc.treatmentclass_id =EVPL.TreatmentClass_id
	union all

	-- КВС
	select 
	'KVS' as EvnType,
  	EPS.EvnPS_id as Evn_id,
  	LS.LpuUnit_Name,
  	LS.LpuUnitType_SysNick,
  	LS.LpuSection_Name,
  	'134' as LpuSectionProfile_Code,
  	LS.LpuSectionType_id,
  	MSF.Post_id,
  	EPS.Person_id,
  	--EPS.Person_Age,
  	EPS.EvnPS_OutcomeDT as EvnDate,
  	EPS.PersonEvn_id,
  	EPS.Server_id,
  	2 as TreatmentClass_id,
  	'1.1' as TreatmentClass_code,
  	'polka' as ServiceType_SysNick,
  	EPS.Diag_pid as Diag_id,
  	null as VizitUsluga,
  	LS.LpuUnitProfile_fid,
  	LS.PalliativeType_id,ls.LpuBuilding_name,Ls.FRMPSubdivision_id
	from Lpu_W as L 
	inner join EvnPS_W as EPS  on EPS.Lpu_id = L.Lpu_id  
  		and coalesce(p_TreatmentClass_id, 2) = 2
  		and EPS.EvnPS_setDT < p_EndDate + interval '1 day'
  		and EPS.EvnPS_OutcomeDT between p_BegDate and p_EndDate + interval '1 day' - interval '1 minute'
  		and (coalesce(EPS.PayType_id, -2) = p_PayType_id or p_PayType_id is null)
  		and (EPS.MedPersonal_pid = p_MedPersonal_id or p_MedPersonal_id is null)
  	inner join dbo.v_MedStaffFact MSF  on MSF.MedStaffFact_id = EPS.MedStaffFact_pid
	inner join persis.Post p  on p.id = MSF.Post_id and p.PostKind_id in (1,2,10,12)
	inner join LpuSection_W as LS  on EPS.LpuSection_pid = LS.LpuSection_id
	left join EvnSection_W as ES on ES.EvnSection_pid = EPS.EvnPS_id
	where (ES.LeaveType_prmid in (358,463) or EPS.PrehospWaifRefuseCause_id > 0) 

	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.EvnVizitDispDop_id as Evn_id,
  	LS.LpuUnit_Name,
  	LS.LpuUnitType_SysNick,
  	LS.LpuSection_Name,
  	LS.LpuSectionProfile_Code,
  	LS.LpuSectionType_id,
  	MSF.Post_id,
  	EPLD.Person_id,
 	-- EPLD.Person_Age,
  	EVD.EvnVizitDispDop_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,
  	case when EPLD.DispClass_id in (1,2, 3,4,7,8) then '2.2' else '2.1' end as TreatmentClass_code,
  	'polka' as ServiceType_SysNick,
  	EVD.Diag_id,
  	null as VizitUsluga,
  	LS.LpuUnitProfile_fid,
  	LS.PalliativeType_id,ls.LpuBuilding_name,Ls.FRMPSubdivision_id
	from Lpu_W as L 
	inner join EvnPLDisp_W as EPLD  on EPLD.Lpu_id = L.Lpu_id
  		and EPLD.EvnPLDisp_setDT < p_EndDate + interval '1 day'
  		and (EPLD.EvnPLDisp_disDT >= p_BegDate or EPLD.EvnPLDisp_disDT is null)
  		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)
	inner join EvnVizitDispDopOrp_W as EVD  on EPLD.EvnPLDisp_id = EVD.EvnVizitDispDop_pid 
  		and EVD.EvnVizitDispDop_setDT between p_BegDate and p_EndDate + interval '1 day' - interval '1 minute'
  		and EVD.EvnVizitDispDop_setDT >= coalesce(EPLD.EvnPLDisp_consDT, p_BegDate)
  		and (EVD.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
  	inner join dbo.v_MedStaffFact MSF on MSF.MedStaffFact_id = EVD.MedStaffFact_id
	inner join persis.Post p  on p.id = MSF.Post_id and p.PostKind_id in (1,2,10,12)
	inner join LpuSection_W as LS  on EVD.LpuSection_id = LS.LpuSection_id
	left join dbo.v_DopDispInfoConsent DDIC on DDIC.DopDispInfoConsent_id = EVD.DopDispInfoConsent_id
	left join dbo.v_SurveyTypeLink STL  on STL.SurveyTypeLink_id = DDIC.SurveyTypeLink_id
	left join dbo.v_SurveyType ST  on ST.SurveyType_id = STL.SurveyType_id
	where (ST.SurveyType_Name like '%осмотр%врач%' or EPLD.DispClass_id in (4,8) or EVD.Diag_id > 0 and EPLD.DispClass_id between 11 and 12) -- select * from dbo.v_DispClass
),

RD2100 as
(
	select RepTableRows_id,
	RepTableRows_Row as RepDiag_Row,RepTableRows_Code as RepDiag_Text2,RepTableRows_Text1 as RepDiag_Text1,RepTableRows_Text2 as RepDiag_Text3
	from rpt.RepTableRows
	where RepTableRows_SysNick='frm30R3_2021'
),

RD1100 as
(
  	select
  
    RD.RepDiag_Row,
    RD.RepDiag_Text1,
    RepDiag_Text2,
    RepDiag_Part,
	RepDiag_Stage,
    p.id as Post_From,
    p.id Post_To,
	p.frmpEntry_id as FRMP_id,
    LSProf_Code
  from
  (
	    select r.RepTableRows_Row RepDiag_Row,
	      r.RepTableRows_Text1 RepDiag_Text1,
		  r.RepTableRows_text2 RepDiag_Text2,
	      --r.RepTableRows_Code RepDiag_Text2,
		  rl.RepTableRows_id,
		  rl.RepTableLinks_UslCode,
		  rl.RepTableLinks_FromDiag, rl.RepTableLinks_ToDiag,
	      rl.RepTableLinks_FlagChar  RepDiag_Part,
		  rl.RepTableLinks_FlagInt as RepDiag_Stage,		
	      coalesce(cast(rtrim(case when rl.RepTableLinks_FlagChar = '2' then rl.RepTableLinks_UslCode else rl.RepTableLinks_FromDiag end) as bigint), -1) as Post_From,
	      coalesce(cast(rtrim(case when rl.RepTableLinks_FlagChar = '2' then rl.RepTableLinks_UslCode else rl.RepTableLinks_ToDiag end) as bigint), -1) as Post_To,
	      rtrim(case when rl.RepTableLinks_FlagChar = '2' then rl.RepTableLinks_FromDiag else '-1' end) as LSProf_Code
	 
		from rpt.RepTableRows r
		inner join rpt.RepTableLinks rl on r.RepTableRows_id=rl.RepTableRows_id
		where RepTableRows_SysNick='frm30R3_2021'
  ) RD
  inner join persis.Post P  on p.frmpEntry_id between Post_From and Post_To

  	/*union all

 	select     
    RD.RepDiag_Row,
    RD.RepDiag_Text1,
    rtrim(RD.RepDiag_Text2) as RepDiag_Text2,
    RepDiag_Part,
	RepDiag_Stage,
    coalesce(cast(rtrim(case when RepDiag_Part = 2 then RepDiag_Text3 else RepDiag_FromDiag end) as bigint), -1) as Post_From,
    coalesce(cast(rtrim(case when RepDiag_Part = 2 then RepDiag_Text3 else RepDiag_ToDiag end) as bigint), -1) as Post_To,
	null as FRMP_id,
    '-1' as LSProf_Code
  	from rpt.v_RepDiag RD 
  	where lower(RD.RepDiag_SysNick) = 'frm30r3_20'
		and RD.RepDiag_Stage = 1
		and RepDiag_Text2 = '126'*/

  	union all

  	select 
    1 as RepDiag_Row,
    'Врачи – всего' as RepDiag_Text1,
    '1' as RepDiag_Text2,
    '1' as RepDiag_Part,
	3 as RepDiag_Stage,
    p.id as Post_From,
    p.id Post_To,
	null as FRMP_id,
    '-1' as LSProf_Code
  	from persis.Post p 
  	where p.PostKind_id = 1
),

E as
(
	select 
	E.LpuSectionProfile_Code,e.LpuBuilding_name,
	E.Post_id,
	case when E.FRMPSubdivision_id in (10286, 10207, 10208)
	  then 2 else 1 end as IsStr11,  

	  case when E.LpuSectionProfile_Code = '149' or PalliativeType_id = 2 
	  --and E.LpuSection_Name not like '%Выездн%'
      then 2 else 1 end as IsStr124,

	   case when E.LpuSectionProfile_Code = '149'  and (E.LpuSection_Name like '%Выездн%' or PalliativeType_id = 2) and E.ServiceType_SysNick in ('home','ahome','neotl')
      then 2 else 1 end as IsStr125,
    case when PP.cntLgots > 0 then 2 else 1 end as IsInvalid,
	COUNT(case when coalesce(lower(E.ServiceType_SysNick), '') not in ('home','ahome','neotl')  
	    then Evn_id end) as val3, 
    COUNT(case when coalesce(lower(E.ServiceType_SysNick), '') not in ('home','ahome','neotl') and PersonAddress.KlareaType_id = 2 
      	then Evn_id end) as val4, -- сельские
    COUNT(case when coalesce(lower(E.ServiceType_SysNick), '') not in ('home','ahome','neotl') and dbo.AgeYMD(Person.Person_BirthDay, E.EvnDate, 1) between 0 and 17 
     	then Evn_id end) as val5, -- детьми 0-17 лет 
    COUNT(case when coalesce(lower(E.ServiceType_SysNick), '') not in ('home','ahome','neotl') 
			--and E.TreatmentClass_id between 1 and 4 -- 1 - 1.3   select * from TreatMentClass
			and E.TreatmentClass_code in ('1','1.1','1.2','1.3','3.0')
      		and PersonAddress.KlareaType_id = 2
    	then Evn_id end) as val6,
    COUNT(case when coalesce(lower(E.ServiceType_SysNick), '') not in ('home','ahome','neotl')
    	  	and dbo.AgeYMD(Person.Person_BirthDay, E.EvnDate, 1) >= 18 
      		--and E.TreatmentClass_id between 1 and 4 -- 1 - 1.3
    	  	and E.TreatmentClass_code in ('1','1.1','1.2','1.3','3.0')
      	then Evn_id end) as val7,
    COUNT(case when coalesce(lower(E.ServiceType_SysNick), '') not in ('home','ahome','neotl') 
      		and dbo.AgeYMD(Person.Person_BirthDay, E.EvnDate, 1) between 0 and 17 
      		--and E.TreatmentClass_id between 1 and 4 -- 1 - 1.3
      		and E.TreatmentClass_code in ('1','1.1','1.2','1.3','3.0')
      	then Evn_id end) as val8,
    COUNT(case when lower(E.ServiceType_SysNick) in ('home','ahome','neotl') 
      	then Evn_id end) as val9,
    COUNT(case when lower(E.ServiceType_SysNick) in ('home','ahome','neotl') 
      		and PersonAddress.KlareaType_id = 2  
      	then Evn_id end) as val10,
    COUNT(case when lower(E.ServiceType_SysNick) in ('home','ahome','neotl')
      		--and E.TreatmentClass_id between 1 and 4 -- 1 - 1.3
    and E.TreatmentClass_code in ('1','1.1','1.2','1.3','3.0')
      	then Evn_id end) as val11, 
    COUNT(case when lower(E.ServiceType_SysNick) in ('home','ahome','neotl') 
      		and dbo.AgeYMD(Person.Person_BirthDay, E.EvnDate, 1) between 0 and 17 
      	then Evn_id end) as val12,
    COUNT(case when lower(E.ServiceType_SysNick) in ('home','ahome','neotl') 
      		and dbo.AgeYMD(Person.Person_BirthDay, E.EvnDate, 1) between 0 and 17
      		--and E.TreatmentClass_id between 1 and 4 -- 1 - 1.3
      		and E.TreatmentClass_code in ('1','1.1','1.2','1.3','3.0')
      	then Evn_id end) as val13
  	from Vizits as E  
  	inner join dbo.v_Person_all Person  on E.PersonEvn_id = Person.PersonEvn_id
  		and Person.Server_id = E.Server_id
 	 left join dbo.Address PersonAddress  on PersonAddress.Address_id = coalesce(Person.PAddress_id, Person.UAddress_id)  
  	left join lateral (
   		select 
	  	PP.PersonPrivilege_id as cntLgots
	  	--count(PP.PersonPrivilege_id) as cntLgots
		from dbo.v_PersonPrivilege PP 
		where PP.Person_id = E.Person_id
	 		and PP.PersonPrivilege_begDate < dateadd('day', 1, E.EvnDate)
	 		and (PP.PersonPrivilege_endDate >= E.EvnDate or PP.PersonPrivilege_endDate is null)
	 		and (PP.PrivilegeType_Code between 10 and 12
	   			or PP.PrivilegeType_Code between 81 and 85
				or PP.PrivilegeType_Code in (92, 101, 122, 132, 140, 142)) limit 1
  		) PP on true

  	group by
	E.LpuSectionProfile_Code,e.LpuBuilding_name,
	E.Post_id,

	case when E.FRMPSubdivision_id in (10286, 10207, 10208)
	  then 2 else 1 end,
    case when E.LpuSectionProfile_Code = '149' or PalliativeType_id = 2 
      then 2 else 1 end,
 	case when E.LpuSectionProfile_Code = '149'  and (E.LpuSection_Name like '%Выездн%' or PalliativeType_id = 2) and E.ServiceType_SysNick in ('home','ahome','neotl')
      then 2 else 1 end,
    case when PP.cntLgots > 0 then 2 else 1 end
),
EE as (
select E.*,RD1100.RepDiag_Text2 
from E 
inner join RD1100  on E.Post_id between Post_From and Post_To
  and (rtrim(E.LpuSectionProfile_Code) = LSProf_Code or LSProf_Code = '-1')
  and (1 = case when RD1100.RepDiag_Row <> 4 then 1
               when coalesce(RD1100.FRMP_id, -1) not in (10234,10302,10304,10235) then 1 end 
 or not exists 
	                (
					select  r.RepTableRows_id
					from rpt.RepTableRows r
						inner join rpt.RepTableLinks rl on r.RepTableRows_id=rl.RepTableRows_id
						where RepTableRows_SysNick='frm30R3_2021'
						  and rl.RepTableLinks_FlagChar = '2'
						  and r.RepTableRows_Row between 5 and 123
						  and (Region_id = dbo.GetRegion() or Region_id is null and dbo.GetRegion() <> 2)
	                      and coalesce(RD1100.FRMP_id, -1) = cast(rtrim(rl.RepTableLinks_UslCode) as bigint)
	                      and rtrim(coalesce(E.LpuSectionProfile_Code, '-2')) = rtrim(rl.RepTableLinks_FromDiag)
	  
	                ) )
	 
)

select
	RD2100.RepDiag_Text1 as "RepDiag_Text1",
	RD2100.RepDiag_Text2 as "RepDiag_Text2",
	RD2100.RepDiag_Row as "RepDiag_Row",
	coalesce(SUM(Ee.val3), 0)::integer as val3,
	coalesce(SUM(Ee.val4), 0)::integer  as val4,
  	coalesce(SUM(Ee.val5), 0)::integer  as val5,
  	coalesce(SUM(Ee.val6), 0)::integer  as val6,
  	coalesce(SUM(Ee.val7), 0)::integer  as val7,
  	coalesce(SUM(Ee.val8), 0)::integer  as val8,
  	coalesce(SUM(Ee.val9), 0)::integer  as val9,
  	coalesce(SUM(Ee.val10), 0)::integer  as val10,
  	coalesce(SUM(Ee.val11), 0)::integer  as val11,
  	coalesce(SUM(Ee.val12), 0)::integer  as val12,
  	coalesce(SUM(Ee.val13), 0)::integer  as val13
from RD2100
left join EE  on ee.RepDiag_Text2 = RD2100.RepDiag_Text3
   and (RD2100.RepDiag_Text2 not in ('66','124','125') --not in ('65','99','123','124')
   and RD2100.RepDiag_Text2 not like '%.1%' and (coalesce(Ee.LpuSectionProfile_Code, '') <> '134' or RD2100.RepDiag_Text2 = '1')
	or RD2100.RepDiag_Text2 = '66' and Ee.LpuSectionProfile_Code = '134'
	or RD2100.RepDiag_Text2 = '124' and Ee.LpuSectionProfile_Code = '160'
	or RD2100.RepDiag_Text2 = '125' and Ee.IsStr124 = 2
	or RD2100.RepDiag_Text2 = '126' and Ee.IsStr125 = 2
	or RD2100.RepDiag_Text2 = '1.1' and Ee.IsStr11 = 2
	or (RD2100.RepDiag_Text2 like '%.1%' and RD2100.RepDiag_Row > 5 and IsInvalid = 2 and coalesce(Ee.LpuSectionProfile_Code, '') <> '134'))
group by 
  RD2100.RepDiag_Text1,
  RD2100.RepDiag_Text2,
  RD2100.RepDiag_Row

order by RD2100.RepDiag_Row
