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_post_id, 
cast($6 as bigint) as p_medpersonal_id, 
cast($7 as bigint) as p_evnplisfinish, 
cast($8 as bigint) as p_yesno, 
cast(to_timestamp($9,'dd.mm.yyyy') as timestamp) as p_begdate, 
cast(to_timestamp($10,'dd.mm.yyyy') as timestamp) as p_enddate)

    select to_char(Calendar.Calendar_Date, 'DD.MM.YYYY') as setDate,
             LpuSection.LpuSection_Code as LpuSection_Code,
             LpuSection.LpuSection_Name as LpuSection_Name,
             MedPersonal.Person_Fio as Person_Fio,
             nullif(count(distinct EvnVizit.EvnVizitPLStom_id), 0) as v21,
             nullif(count(distinct case
                                     when (PersonAddress.KlareaType_id = 2) then EvnVizit.EvnVizitPLStom_id
                                   end), 0) as v22,
             nullif(sum(COALESCE(COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED), 0)), 0) as val2,
             nullif(sum(case
                          when (PersonAddress.KlareaType_id = 2) then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as val3,
             nullif(sum(case
                          when (EvnPL.Person_Age between 0 and 17) then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as val4,
             nullif(sum(case
                          when (EvnPL.Person_Age >= 60) then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as val5,
             nullif(sum(case
                          when (PayType.PayType_Name like '%ОМС%') then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as val17,
             nullif(sum(case
                          when (PayType.PayType_Name like any(ARRAY['Местный бюджет','Бюджет местный','Бюджет муниципальный','Бюджет региональный','МБТ (незастрахованные)','МБТ (СЗЗ)']) ) then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
													else 0
                        end), 0) as val18,
             nullif(sum(case
						              when (PayType.PayType_Name like any(ARRAY['Федеральный бюджет','Бюджет федеральный'])) then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as val18a,
             nullif(sum(case
						              when (PayType.PayType_Name like any (ARRAY['Платные услуги','Средства граждан'])) then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as val19,
             nullif(sum(case
						              when (PayType.PayType_Name like '%ДМС%') then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as val20,
             nullif(sum(case
                          when (NOT PayType.PayType_Name LIKE ANY (ARRAY['%ОМС%', 'Местный бюджет', 'Бюджет местный', 'Бюджет муниципальный', 'Бюджет региональный', 'МБТ (незастрахованные)', 'МБТ (СЗЗ)', 'Федеральный бюджет', 'Бюджет федеральный', 'Платные услуги', 'Средства граждан', '%ДМС%']) ) then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as val21,
             nullif(sum(case
                          when (SocStatus.SocStatus_Sysnick like 'rab') then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as v1,
             nullif(sum(case
                          when (SocStatus.SocStatus_Sysnick like 'nrab') then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as v2,
             nullif(sum(case
                          when (SocStatus.SocStatus_Sysnick like 'study') then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as v3,
             nullif(sum(case
                          when (SocStatus.SocStatus_Sysnick like 'pen') then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as v4,
             nullif(sum(case
                          when (SocStatus.SocStatus_Sysnick like 'bomzh') then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as v5,
             nullif(sum(case
                          when (SocStatus.SocStatus_Sysnick LIKE ANY (ARRAY['mvd', 'gtk', 'dnp', 'ra', 'fapsi', 'fsb', 'fps', 'mcs', 'fire', 'warother'])) then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as v6,
             nullif(sum(case
                          when (SocStatus.SocStatus_Sysnick like 'priz') then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as v7,
             nullif(sum(case
                          when (SocStatus.SocStatus_Sysnick like 'child_yasli') then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as v8,
             nullif(sum(case
                          when (SocStatus.SocStatus_Sysnick like 'child_doma') then COALESCE(EvnUSlugaStom.EvnUSlugaStom_Summa, UslugaComplexTariff_UED)
                          else 0
                        end), 0) as v9
      from dbo.Calendar as Calendar
			left join params on 1=1
           inner join rpt.v_EvnVizitPLStom_rpt as EvnVizit on EvnVizit.EvnVizitPLStom_setDate = Calendar.Calendar_Date and (EvnVizit.EvnVizitPLStom_setDate between p_begdate
       and p_enddate) and (Calendar.Calendar_Date between p_begdate
       and p_enddate)
           inner join dbo.v_MedPersonal as MedPersonal on EvnVizit.MedPersonal_id = MedPersonal.MedPersonal_id and ((MedPersonal.Medpersonal_id = p_medpersonal_id) or (p_medpersonal_id
           is null)) and (EvnVizit.Lpu_id = MedPersonal.Lpu_id)
           INNER JOIN LATERAL
           (
             SELECT *
             FROm v_MedStaffFact MedStaffFact
             where MedStaffFact.Medpersonal_id = MedPersonal.Medpersonal_id and
                   MedStaffFact.Lpu_id = p_lpu_id
                   and
                   (MedStaffFact.WorkData_endDate >= p_enddate
                   or
                   MedStaffFact.WorkData_endDate is null) and
                   (MedStaffFact.Post_id = p_post_id
                   or
                   p_post_id
                   is null)
			limit 1
           ) MedStaffFact on true
           inner join dbo.v_LpuSection as LpuSection on (EvnVizit.LpuSection_id = LpuSection.LpuSection_id) and ((EvnVizit.Lpu_id = p_lpu_id) or (p_lpu_id
           is null)) and ((LpuSection.LPuSection_id = p_lpusection_id) or (p_lpusection_id
           is null))
           inner join dbo.v_LpuUnit LpuUnit on LpuUnit.LpuUnit_id = LpuSection.LpuUnit_id and (LpuUnit.LpuUnit_id = p_LpuUnit_id
       or p_lpuunit_id
           is null) and (LpuUnit.LpuBuilding_id = p_lpubuilding_id
       or p_lpubuilding_id
           is null)
           --194947 12.02.2020 - заменил inner join на left так как услуги может и не быть
           left join dbo.v_EvnUSluga as EvnUSluga on EvnVizit.EvnVizitPLStom_id = EvnUSluga.EvnUsluga_pid
           left join dbo.v_EvnUSlugaStom as EvnUSlugaStom on EvnUslugaStom.EvnUslugaStom_id = EvnUsluga.EvnUsluga_id and EvnUslugaStom.lpu_id = EvnUSluga.lpu_id
           inner join dbo.v_EvnPLStom as EvnPL on EvnVizit.EvnVizitPLStom_pid = EvnPL.EvnPLStom_id and ((EvnPL.Lpu_id = p_lpu_id) or (p_lpu_id
           is null)) AND (coalesce(EvnPL.EvnPLStom_IsFinish,1) = p_evnplisfinish or p_evnplisfinish is null)
					 inner join dbo.v_EvnVizitPLStom as lastEvnVizit on EvnPL.EvnPLStom_id=lastEvnVizit.EvnVizitPLStom_pid 
					 and lastEvnVizit.EvnVizitPLStom_Index = lastEvnVizit.EvnVizitPLStom_Count - 1
					 and (coalesce(coalesce(EvnPL.EvnPLStom_IsPaid,lastEvnVizit.EvnVizitPLStom_IsPaid),1) = p_yesno or p_yesno is null) 		
					 inner join dbo.v_Person_reg_rpt as Person on Person.PersonEvn_id = EvnPL.PersonEvn_id and EvnPL.server_id = Person.server_id
           left join dbo.v_Address as PersonAddress on PersonAddress.Address_id = Person.UAddress_id
           left join dbo.Address as PersonUAddress on PersonUAddress.Address_id = Person.UAddress_id
           left join dbo.v_PayType as PayType on PayType.PayType_id = EvnVizit.PayType_id
           left join dbo.v_SocStatus as SocStatus on SocStatus.SocStatus_id = Person.SocStatus_id
           LEFT JOIN LATERAL
           (
             select UslugaComplexTariff_id,
                    UslugaComplexTariff_Tariff,
                    UslugaComplexTariff_UED
             from dbo.UslugaComplexTariff
             where 1 = 1 and
                   UslugaComplexTariff.UslugaComplex_id = EvnUSluga.UslugaComplex_id and
                   (UslugaComplexTariff.mesagegroup_id = case
                                                           when dbo.ageymd(Person.Person_Birthday, EvnPLstom_disDate, 1) >= 18 then 1
                                                           else 2
                                                         end or
                   UslugaComplexTariff.mesagegroup_id is null) and
                   UslugaComplexTariff.PayType_id = 1 and
                   UslugaComplexTariff.Lpu_id is null and
                   UslugaComplexTariff.UslugaComplexTariff_begDate <= cast (EvnPLstom_setDate as date) and
                   (UslugaComplexTariff.UslugaComplexTariff_endDate is null or
                   UslugaComplexTariff.UslugaComplexTariff_endDate >= cast (EvnPLstom_setDate as date)) and
                   (UslugaComplexTariff.LpuSection_id = EvnUsluga.LpuSection_uid or
                   UslugaComplexTariff.LpuSection_id is null) and
                   (UslugaComplexTariff.LpuSectionProfile_id not in (35113, 35119) or
                   UslugaComplexTariff.LpuSectionProfile_id is null) and
                   UslugaComplexTariff.Lpu_id is null and
                   UslugaComplexTariff_UED is not null and
                   ((EvnPLstom_disDate >= '2015-06-01' and
                   COALESCE(UslugaComplexTariff.LpuUnitType_id, 1) not IN (13, 14)) or
                   EvnPLstom_disDate < '2015-06-01')
             order by UslugaComplexTariff.UslugaComplexTariff_begDate desc
             limit 1
           ) Tariff on true
      group by Calendar.Calendar_Date,
               LpuSection.LpuSection_Code,
               LpuSection.LpuSection_Name,
               MedPersonal.Person_Fio

