/*[10:51] Леушина Наталья Николаевна
--2801-----------------ГБУЗ НО «ДГП №39 Советского района г. Н.Новгорода» отчет с 01.01.2021 по 31.12.2021
select * from v_lpu
where lpu_name like '%39%'
[10:51] Леушина Наталья Николаевна
аналогичная проблема - заказчик добавил в данный инцидент

[10:51] Леушина Наталья Николаевна
НО Ковернинская ЦРБ

[10:51] Леушина Наталья Николаевна
НО Княгининская ЦРБ*/
--frm30R3_2100_2020_pg.rptdesign
--frm30r3_20_2100
--EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
with params as
(
	select
	cast ('2801' as varchar) as  p_lpulist,
	cast (NULL as varchar)  as  p_lpufiliallist,
	cast(NULL as bigint) as p_lpubuilding_id,
	cast(NULL as bigint) as p_lpuunit_id, 
	cast(NULL as bigint) as p_lpusection_id, 
	cast(NULL as bigint) as p_medpersonal_id,
	cast(to_timestamp('01.01.2021','dd.mm.yyyy') as timestamp) as p_begdate, 
	cast(to_timestamp('31.01.2021','dd.mm.yyyy') as timestamp) as p_enddate, 
	cast(2 as bigint) as p_includedopdisp, 
	cast(2 as bigint) as p_includeprofosm, 
	cast(2 as bigint) as p_includedisporp, 
	cast(2 as bigint) as p_includeteendisp, 
	cast(NULL as bigint) as p_treatmentclass_id, 
	cast(NULL as bigint) as p_paytype_id, 
	cast(1 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
)

select 
  	'TAP' as EvnType,
  	EVPL.Evn_id as Evn_id,
  	Lu.LpuUnit_Name,
  	Lut.LpuUnitType_SysNick,
  	LS.LpuSection_Name,
  	LSProf.LpuSectionProfile_Code,
  	LS.LpuSectionType_id,
  	MSF.Post_id,
  	--EPL.Person_id,
  	EVPL.Person_id,
  	--EPL.Person_Age,
  	EVPL.Evn_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
  	lu.LpuUnitProfile_fid,
	LS.PalliativeType_id,lb.LpuBuilding_name,Ls.FRMPSubdivision_id
	from Lpu_W as L 
	inner join dbo.EvnPL as EPL  on EPL.Lpu_id = L.Lpu_id  
	  	and EPL.Evn_setDT < p_EndDate + interval '1 day'  
  		and (EPL.Evn_disDT >= p_BegDate or EPL.Evn_disDT is null)
	inner join dbo.EvnVizitPL as EVPL  on EVPL.Evn_pid = EPL.Evn_id 
  		and EVPL.Evn_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)
  		and (EVPL.Evn_deleted = 1 or EVPL.Evn_deleted 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 dbo.LpuSection as LS  on EVPL.LpuSection_id = LS.LpuSection_id
	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 dbo.LpuSectionProfile LSProf ON LSProf.LpuSectionProfile_id = LS.LpuSectionProfile_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
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)
