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_htmedicalcareclass_id,
		cast($6 as timestamp) as p_begdate,
		cast($7 as timestamp) as p_enddate
)
    select EvnPS.EvnPS_NumCard as "EvnPS_NumCard", --v2
             Person.Person_Fio as "Person_Fio", --v3
             to_char(Person.Person_BirthDay, 'DD.MM.YYYY') as "Person_BirthDay", --v4
             LS.LpuSection_FullName as "LpuSection_FullName", --v5
						 Person.Person_Snils, --СНИЛС --v6
						 a.Address_Address, --Адрес --v7
						 rtrim(case when p.PolisType_id = 4
             then Person.Person_EdNum 
               else coalesce(p.Polis_Num,'') end) as Polis_Num, --Номер полиса --v8
						 O.Org_Nick, --СМО --v9
						 rtrim(coalesce(dt.DocumentType_SysNick,'') || ' ' || coalesce(d.Document_Ser,'') || ' ' || coalesce(d.Document_Num,'') || ' ' ||
						 coalesce(to_char(d.Document_begDate,'DD.MM.YYYY'),'') || ' ' || coalesce(od_name.Org_Nick,'')) as Document, --v10
             MP.Person_Fio as "MP_fio", --v11
             to_char(es.EvnSection_setDate, 'DD.MM.YYYY') as "EvnSection_setDate", --v12
             to_char(es.EvnSection_disDate, 'DD.MM.YYYY') as "EvnSection_disDate", --v13
             case
               when es.EvnSection_setDate = es.EvnSection_disDate then 1
               else datediff('day', es.EvnSection_setDate, es.EvnSection_disDate) + case
                                                                                      when LU.LpuUnitType_id = 1 then 0
                                                                                      else 1
                                                                                    end
             end as kd_fakt, --v14
             case
               when es.EvnSection_setDate = es.EvnSection_disDate then 1
               else datediff('day', es.EvnSection_setDate, es.EvnSection_disDate) + case
                                                                                      when LU.LpuUnitType_id = 1 then 0
                                                                                      else 1
                                                                                    end
             end as kd_pay, --v15
             HTMedicalCareClassTariff_Tariff as tariff, --v16
             cast (cast (HTMedicalCareClassTariff_Tariff * case
                                                             when es.EvnSection_disDate >= '2017-08-01' and HTMfed.HTMedicalCareClass_Code is Not null then COALESCE(HTMCoeff.Value, 1)
                                                             else 1
                                                           end + case
                                                                   when (lu.LpuUnitType_id = 1 or (lu.LpuUnitType_id != 1 and es.EvnSection_disDate >= '2016-01-01')) then COALESCE(GEMO.SUMUsl, 0)
                                                                   else 0
                                                                 end as numeric (14, 2)) as CHAR (30)) as summa, --v17
             Diag.Diag_fullname as "Diag_fullname", --v18
						 coalesce(edh.EvnDirectionHTM_TalonNum, EvnPS.EvnPS_HTMTicketNum) as talon, --v19
             HTM.HTMedicalCareClass_Code as "HTMedicalCareClass_Code", --v20
             HTM.HTMedicalCareClass_Name as "HTMedicalCareClass_Name", --v21
						 
             HCT.HTMedicalCareType_Code as "HTMedicalCareType_Code", --v22
             HCT.HTMedicalCareType_Name as "HTMedicalCareType_Name", --v23
             Oper.LpuSection_FullName as "LpuSectionOper" --v24
      from v_EvnPS EvnPS
			left join v_EvnDirection ed on ed.EvnDirection_id=EvnPS.EvnDirection_id
			left join EvnDirectionHTM edh on ed.EvnDirection_id = edh.Evn_id
			left join params on 1=1
           inner join v_EvnSection ES on ES.EvnSection_pid = EvnPS.EvnPS_id and (evnps.Lpu_id = p_Lpu_id or p_lpu_id is null)
       and (es.LpuSection_id = p_LpuSection_id
       or p_LpuSection_id
           is null) and COALESCE(es.EvnSection_IsPriem, 1) = 1 and es.HTMedicalCareClass_id is not null and (es.HTMedicalCareClass_id = p_HTMedicalCareClass_id
       or p_HTMedicalCareClass_id
           is null)
           inner join v_LpuSection LS on LS.LpuSection_id = ES.LpuSection_id and (LS.LpuUnit_id = p_LpuUnit_id
       or p_LpuUnit_id
           is null)
           inner join v_LpuUnit LU on LU.LpuUnit_id = LS.lpuunit_id and (LU.LpuBuilding_id = p_LpuBuilding_id
       or p_LpuBuilding_id
           is null)
           left join v_Person_bdz Person on Person.PersonEvn_id = evnps.PersonEvn_id and person.Server_id = evnps.Server_id
					 left join Address a on a.Address_id=Person.PAddress_id
					 left join v_Polis p on p.Polis_id=Person.Polis_id
					 left join OrgSMO osmo on osmo.OrgSmo_id=p.OrgSmo_id
					 left join Org o on o.Org_id=osmo.Org_id
					 left join v_Document d on d.Document_id=Person.Document_id and d.Server_id=Person.Server_id
					 left join OrgDep od on od.OrgDep_id=d.OrgDep_id
					 left join Org od_name on od_name.Org_id=od.Org_id
					 left join v_DocumentType dt on dt.DocumentType_id=d.DocumentType_id
           inner join v_MedPersonal MP on mp.MedPersonal_id = es.MedPersonal_id and mp.Lpu_id = es.Lpu_id
           LEFT JOIN LATERAL
           (
             select HTMedicalCareClassTariff_Tariff
             from HTMedicalCareClassTariff
             where HTMedicalCareClassTariff.HTMedicalCareClass_id = es.HTMedicalCareClass_id and
                   HTMedicalCareClassTariff_begDate <= es.EvnSection_disDate and
                   (HTMedicalCareClassTariff_endDate > es.EvnSection_disDate or
                   HTMedicalCareClassTariff_endDate is null)
             order by HTMedicalCareClassTariff_begDate desc
             limit 1
           ) HTMedicalCareClassTariff on true
           inner join dbo.HTMedicalCareClass HTM on es.HTMedicalCareClass_id = HTM.HTMedicalCareClass_id
           left join fed.HTMedicalCareClass HTMfed on HTMfed.HTMedicalCareClass_id = HTM.HTMedicalCareClass_fid and HTMfed.HTMedicalCareClass_GroupCode in ('27', '28')
           left join dbo.HTMedicalCareType HCT on HCT.HTMedicalCareType_id = htm.HTMedicalCareType_id
           LEFT JOIN LATERAL
           (
             select sum(eu.EvnUsluga_Kolvo) as cnt
             from v_EvnUsluga eu
                  inner join v_UslugaComplex u on u.UslugaComplex_id = eu.UslugaComplex_id
             where eu.EvnUsluga_pid = es.EvnSection_id and
                   u.UslugaComplex_Code in ('A16.12.004.009', 'A16.12.028.003', 'A16.12.028') -- #115556
           ) ru on true
           LEFT JOIN LATERAL
           (
             select COALESCE(Av2.AttributeValue_ValueFloat, Av2.AttributeValue_ValueInt) as Value,
                    Av.AttributeValue_ValueInt as cnt
             from dbo.AttributeValue av
                  inner join v_TariffClass TariffClass on av.AttributeValue_TablePKey = TariffClass.TariffClass_id
                  inner join dbo.AttributeValue Av2 on av.AttributeValue_rid = Av2.AttributeValue_id
             where TariffClass_Code = '2017-08ВМП_стент' and
                   av.AttributeValue_ValueInt <= COALESCE(ru.cnt, 1) and
                   Av2.AttributeValue_rid is null and
                   Av2.AttributeValue_begDate <= es.EvnSection_disDate and
                   (Av2.AttributeValue_endDate >= es.EvnSection_disDate or
                   Av2.AttributeValue_endDate is null)
             order by COALESCE(Av2.AttributeValue_ValueFloat, Av2.AttributeValue_ValueInt) desc
             limit 1
           ) HTMCoeff on true -- #113527
           LEFT JOIN LATERAL
           (
             select SUM(COALESCE(UslugaComplexTariff_Tariff, 0) * COALESCE(eu.EvnUsluga_Kolvo, 1)) as SUMUsl
             from v_EvnUsluga eu
                  --inner join UslugaComplexTariff  on UslugaComplexTariff.UslugaComplexTariff_id = Tariff.UslugaComplexTariff_id
                  inner join UslugaComplex on UslugaComplex.UslugaComplex_id = eu.UslugaComplex_id and (UslugaComplex.UslugaComplex_Code in ('A18.05.002', 'A18.30.001', 'A18.05.002.002', 'A18.05.002.001', 'A18.05.002.001.001', 'A18.05.011') or (LU.LpuUnitType_id = 1 and UslugaComplex.UslugaComplex_Code in ('A18.05.004', 'A18.05.002.003', 'A18.05.003', 'A18.05.003.001', 'A18.05.011.001', 'A18.05.002.005', 'A18.05.003.002', 'A18.05.011.002', 'A18.30.001.001'))) -- #81966
                  LEFT JOIN LATERAL
                  (
                    select UslugaComplexTariff_id,
                           UslugaComplexTariff_Tariff
                    from UslugaComplexTariff
                    where UslugaComplexTariff.UslugaComplex_id = UslugaComplex.UslugaComplex_id and
                          UslugaComplexTariff.UslugaComplexTariff_begDate <= es.EvnSection_disDate and
                          (UslugaComplexTariff.UslugaComplexTariff_endDate is null or
                          UslugaComplexTariff.UslugaComplexTariff_endDate >= es.EvnSection_disDate) and
                          (UslugaComplexTariff.UslugaComplexTariff_Tariff is not null) and
                          (UslugaComplexTariff.Lpu_id is null or
                          eu.Lpu_id = UslugaComplexTariff.Lpu_id) and
                          (UslugaComplexTariff.LpuSection_id = eu.LpuSection_uid or
                          UslugaComplexTariff.LpuSection_id is null) and
                          (UslugaComplexTariff.LpuSectionProfile_id not in (35113, 35119) or
                          UslugaComplexTariff.LpuSectionProfile_id is null)
                    order by UslugaComplexTariff.Lpu_id desc,
                             UslugaComplexTariff.LpuSection_id desc,
                             UslugaComplexTariff.UslugaComplexTariff_begDate desc
                   limit 1
                  ) Tariff on true
             where eu.EvnUsluga_pid = es.EvnSection_id
           ) GEMO on true
           left join v_Diag Diag on Diag.Diag_id = es.Diag_id
           LEFT JOIN LATERAL
           (
             select LS.LpuSection_FullName
             from v_EvnUslugaOper eu
                  inner join v_LpuSection LS on ls.LpuSection_id = eu.LpuSection_uid
             where eu.EvnUslugaOper_pid = es.EvnSection_id and
                   EvnUslugaOper_IsVMT = 2
             order by eu.EvnUslugaOper_setDate
             limit 1
           ) Oper on true
      where Evnps.EvnPS_disDate between p_begdate
            and
            p_enddate
order by "Person_Fio"
