--regpl_patientcare_pg.rptdesign
with params as (
    select 
        $1::int8 as p_lpu_id,
        $2::int8 as p_lpubuilding_id,
        $3::int8 as p_lpuunit_id,
        $4::int8 as p_lpusection_id,
        $5::int8 as p_lpusectionprofile_id,
        $6::int8 as p_medpersonal_id,
        $7::int8 as p_sex_id,
        $8::int4 as p_begpersonage,
        $9::int4 as p_endpersonage,
        $10::int8 as p_addresstype_id,
        $11::int8 as p_paytype_id,
        $12::int8 as p_socstatus_id,
        dbo.getdiagcodebyid($13::int8) as p_begdiag_code,
        dbo.getdiagcodebyid($14::int8) as p_enddiag_code,
        $15::int8 as p_strana,
        $16::int8 as p_onlypersoncard_yn,
        $17::int8 as p_nozastrah,
        to_timestamp($18, 'dd.mm.yyyy') as p_begdate,
        to_timestamp($19, 'dd.mm.yyyy') as p_enddate
),
LpuP as (
    select 
        L.Lpu_id,
        l.Lpu_Nick,
        coalesce(LpuPr.Lpu_id, L.Lpu_id) as Lpu_pid
    from params 
    join v_Lpu L on true
    left join lateral (
        select 
            LpuPr.Lpu_id, 
            LpuPr.Lpu_pid
        from Lpu LpuPr 
        where LpuPr.Lpu_id = L.Lpu_pid
            and cast(LpuPr.Lpu_begDate as date) <= p_EndDate
        limit 1
    ) LpuPr on true
    where (cast(L.Lpu_endDate as date) >= p_BegDate or L.Lpu_endDate is null)        
        and (
            (L.Lpu_id = p_Lpu_id or LpuPr.Lpu_id = p_Lpu_id) 
            and p_OnlyPersonCard_YN = 1
            or p_Lpu_id is null
            or p_OnlyPersonCard_YN = 2
            or p_OnlyPersonCard_YN is null
        )
),
pl as (
select  evn.lpu_id,  
        evn.EvnClass_id, 
        evn.person_id, 
        evn.evnpl_id,
        evn.MedicalCareBudgType_id, 
        evn.MedPersonal_id, 
        evn.LpuSection_id,
        evnpl_setdate,evnpl_disdate,
				evn.personevn_id,
				evn.server_id
        from params 
        inner join v_evnpl evn on true
        inner join  LpuP  on LpuP.lpu_id = evn.lpu_id
        inner join v_personstate as ps on ps.person_id = evn.person_id
        where evnpl_disdate between p_begdate and p_enddate 
        and evnclass_id in (3, 6, 35, 60)
        and evnpl_isfinish=2
        and date_part('year', age(evnpl_disdate, ps.Person_BirthDay)) between coalesce(p_BegPersonAge, 0) and coalesce(p_EndPersonAge, 200)  
	--and evn.person_id in (620501,403514,211957)
			/*select 
        evn.lpu_id,  
        Evn.EvnClass_id, 
        evn.person_id, 
        evn.evn_id as evnpl_id,  
        EvnPL.MedicalCareBudgType_id, 
        EvnPL.MedPersonal_id, 
        EvnPL.LpuSection_id,
        cast(evn.evn_setdt as date) as evnpl_setDate,  
        cast(evn.evn_disdt as date) as evnpl_disDate
    from params 
    join EvnClass on true
    join Evn   
        on EvnClass.EvnClass_id = Evn.EvnClass_id 
        and Evn.EvnClass_id in (3, 6, 35, 60)
    join LpuP 
        on LpuP.lpu_id = evn.lpu_id
    join EvnPLBase 
        on Evn.Evn_id = EvnPLBase.Evn_id
    join v_EvnPL as EvnPL 
        on EvnPLBase.Evn_id = EvnPL.EvnPL_id
    join v_personstate as ps 
        on ps.person_id = EvnPLBase.person_id
    where cast(evn.evn_disdt as date) between p_begdate and p_enddate  
        and Evn.Evn_deleted = 1
        and EvnPLBase.EvnPLBase_IsFinish = 2 --случай законченный       
        and date_part('year', age(EvnPL.evnpl_disDate, ps.Person_BirthDay)) between coalesce(p_BegPersonAge, 0) and coalesce(p_EndPersonAge, 200)
        */        
),
SL as (
    select 
        evnpl.lpu_id, 
        lp.lpu_nick, 
        EvnPL.EvnClass_id, 
        EvnPL.Person_id, 
        PersCard.Lpu_id MO_prikr,
        EvnPL.EvnPL_id, 
        EvnPL.MedicalCareBudgType_id, 
        evnpl.evnpl_disDate, 
        evnpl.EvnPL_setDate,  
        EvnPL.MedPersonal_id,
        pr.LpuSectionProfile_Name, 
        --NStatus.KLCountry_id
        ns.KLCountry_id
        --coalesce(NStatus.KLCountry_id,643) KLCountry_id
    from params
    join Pl EvnPL on true
    join LpuP lp 
        on lp.lpu_id = evnpl.lpu_id
    join v_LpuSection ls  
        on ls.LpuSection_id = EvnPL.LpuSection_id
        and (ls.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
    join v_LpuBuilding lb 
        on ls.LpuBuilding_id = lb.LpuBuilding_id
        and (lb.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
    join v_LpuUnit lu 
        on ls.LpuUnit_id = lu.LpuUnit_id
        and (lu.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
    join v_LpuSectionProfile pr  
        on pr.LpuSectionProfile_id = ls.LpuSectionProfile_id
        and (pr.LpuSectionProfile_id = p_LpuSectionProfile_id or p_LpuSectionProfile_id is null)
    join v_PersonState ps
        on ps.Person_id = Evnpl.Person_id     
    
    left join lateral
        (
        select pa.personevn_upddt ,ns.NationalityStatus_upddt,ns.klcountry_id      
        from
				v_Person_all as pa
				left join NationalityStatus ns on ns.NationalityStatus_id = pa.NationalityStatus_id
				where  pa.Personevn_id = Evnpl.PersonEvn_id
				and pa.server_id =EvnPl.server_id 
				--where  pa.Person_id = ps.Person_id
				--and pa.PersonEvnClass_id = 23
				--and cast(pa.personevn_begdt as date)<=evnpl.evnpl_disDate
        ) ns on true
    left join lateral (
        select  
            PersonCard_id, 
            LpuRegion_id, 
            PersonCard.Lpu_id
        from v_PersonCard_all PersonCard 
        where PersonCard.Person_id = EvnPL.Person_id
            and (
                (PersonCard.personcard_begDate<=EvnPL.EvnPL_setDate) 
                and (PersonCard.personcard_endDate >=EvnPL.EvnPL_setDate or PersonCard.personcard_endDate is null)
            )
            and LpuAttachType_id = 1
        order by PersonCard.personcard_begDate desc
        limit 1
    ) PersCard on true
    where 1 = 1
        and 2 = 
            case 
                when p_Lpu_id is not null then 
                    case 
                        when (EvnPL.Lpu_id = p_Lpu_id and p_OnlyPersonCard_YN = 1) then 2
                        when (PersCard.Lpu_id = p_Lpu_id and p_OnlyPersonCard_YN = 2) then 2 
                    end
                when p_Lpu_id is null then 2 
            end     
        and ((case when coalesce(ns.KLCountry_id,643) = 643 then 2 else 1 end) = p_Strana or p_Strana is null)
),
tap as (
    select 
        EvnUsluga.EvnUsluga_setDT,
        concat_ws(' ', ps.person_surname, ps.person_firname, ps.person_secname) as Person_FIO,
        ps.Person_birthday,
        date_part('year', age(evnpl.evnpl_disDate, ps.Person_BirthDay)) Person_Age,
        Sex.Sex_Name,
        ps.Person_Phone,
        EvnPL.EvnPL_setDate,
        --EvnPL.EvnPL_disDate,
        dg.Diag_FullName,
        EvnPL.lpu_nick,
        EvnPL.MO_prikr,
        dg.LpuSectionProfile_Name,
        EvnPL.EvnPL_id,
        vizit.evcount EvnVizitPL_Count,
        pt.PayType_Name,
        UslugaComplex.UslugaComplex_Code,
        UslugaComplex.UslugaComplex_Name,
        EvnUsluga.EvnUsluga_Kolvo,
        EvnPL.EvnClass_id,
        EvnUsluga.EvnUsluga_IsVizitCode,
        Evnpl.MedPersonal_id,
        EvnPL.Lpu_id,
        ps.PAddress_id,  
        ps.UAddress_id,
        ps.SocStatus_id, 
        ps.Document_id,
        ps.Person_id,   
        EvnUsluga.MedStaffFact_id EvnUslugaMedStaffFact_id, 
        UslugaComplex.UslugaComplex_id,
        EvnUsluga.EvnUsluga_setDate,
        evnpl.EvnPL_disDate,
        date_part('year', age(EvnPL.EvnPL_setDate, ps.Person_Birthday)) age,
        EvnUsluga.LpuSection_uid,
        EvnUsluga.PayType_id,
        mc.MedicalCareBudgType_id,
        mc.MedicalCareBudgType_Name MedicalCareBudgType,
        EvnPL.KLCountry_id,
				vizit.ev_id
    from params
    join sl EvnPL on true
    join v_PersonState ps 
        on ps.Person_id = Evnpl.Person_id     
    join Sex 
        on coalesce(ps.Sex_id, 1) = Sex.Sex_id
        and (ps.Sex_id = p_Sex_id or p_Sex_id is null) 
    -- Услуги
    left join v_EvnUsluga as EvnUsluga 
        on EvnPL.EvnPL_id = EvnUsluga.EvnUsluga_rid
        and EvnUsluga.EvnClass_id not in (74, 75, 76, 77, 83, 100, 197, 203, 213)
        and EvnUsluga.EvnUsluga_setDate is not null                 -- #PROMEDWEB-10660
    /*join PayType pt 
        on pt.PayType_id = EvnUsluga.PayType_id
        and (pt.PayType_id = p_PayType_id or p_PayType_id is null)  -- вид оплаты
        */
    left join v_UslugaComplex as UslugaComplex 
        on EvnUsluga.UslugaComplex_id = UslugaComplex.UslugaComplex_id 
    left join v_MedicalCareBudgType mc  
        on mc.MedicalCareBudgType_id = EvnPL.MedicalCareBudgType_id
    left join v_MedPersonal as MedPersonal  
        on MedPersonal.MedPersonal_id = EvnPL.MedPersonal_id
        and MedPersonal.Lpu_id = EvnPL.Lpu_id 
    left join lateral (
        select  ev.paytype_id ,
            dg.Diag_FullName,
            lp.LpuSectionProfile_Name,
            dg.Diag_Code
        from v_evnvizitpl ev 
        join v_diag dg  
            on dg.Diag_id=ev.Diag_id
        join v_LpuSectionProfile lp 
            on lp.LpuSectionProfile_id = ev.LpuSectionProfile_id
            and (ev.LpuSectionProfile_id = p_LpuSectionProfile_id or p_LpuSectionProfile_id is null)
        where EvnPL.EvnPL_id = ev.EvnVizitPL_pid 
            and (ev.EvnVizitPL_Count - 1) = ev.EvnVizitPL_Index
        limit 1
    ) dg on true
    join PayType pt 
        on pt.PayType_id = dg.PayType_id
    left join lateral (
        select 
            count(ev.evnvizitpl_id) evcount, max(ev.evnvizitpl_id) ev_id
        from v_evnvizitpl ev 
        where EvnPL.EvnPL_id = ev.EvnVizitPL_pid 
    ) vizit on true
    where (dg.Diag_Code >= p_BegDiag_Code or p_BegDiag_Code is null)
        and (dg.Diag_Code <= p_EndDiag_Code or p_EndDiag_Code is null)
        and (ps.SocStatus_id = p_SocStatus_id or p_SocStatus_id is null)
        and (MedPersonal.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
        and (pt.PayType_id = p_PayType_id or p_PayType_id is null)  -- вид оплаты
)
select tap.KLCountry_id,
    row_number() over (order by 
        tap.Person_FIO,
        tap.Person_birthday, 
        tap.Person_id,
        tap.EvnPL_id, 
        tap.UslugaComplex_Code
    ) as "RecAll_Num",
    tap.Person_id as "Person_id",
    tap.Person_FIO as "Person_FIO",
    tap.Person_birthday as "Person_birthday",
    tap.Person_Age as "Person_Age",
    tap.Sex_Name as "Sex_Name",    
    tap.Person_Phone as "Person_Phone",
    ua.Address_Address reg,
    pa.Address_Address prog,
    ss.SocStatus_name as "SocStatus_name",
    kl.KLCountry_name as "KLCountry_name",  
    lpu_pr.lpu_nick as "MO_prikr",
    case when strahov.polis_id is not null then 'Застрахован' else 'Не застрахован' end stahov,
    to_char(tap.EvnPL_setDate::date, 'DD.MM.YYYY') "EvnPL_setDate",
    to_char(tap.EvnPL_disDate::date, 'DD.MM.YYYY') "EvnPL_disDate",
    MedPersonal.Person_Fin as medpers,
    tap.Diag_FullName as "Diag_FullName",
    tap.lpu_nick "MO_section",
    tap.LpuSectionProfile_Name as "LpuSectionProfile_Name",
    tap.EvnPL_id as "EvnPL_id",
    tap.EvnVizitPL_Count as "EvnVizitPL_Count",
    tap.PayType_Name as "PayType_Name",
    tap.MedicalCareBudgType as "MedicalCareBudgType",
    dt.DocumentType_Name as "DocumentType_Name",
    tap.UslugaComplex_Code as "UslugaComplex_Code",
    tap.UslugaComplex_Name as "UslugaComplex_Name",
    tap.EvnUsluga_Kolvo as "EvnUsluga_Kolvo", 
    coalesce((
        case 
            when tap.PayType_id in (3, 6) then 
                case 
                    when QuoteUnitType_id = 1 then coalesce(tarifbudg.MedicalCareBudgTypeTariff_Value, 0) * coalesce(tap.EvnUsluga_Kolvo, 1)
                    when QuoteUnitType_id = 3 then coalesce(tap.EvnVizitPL_Count, 0) * coalesce(tarifbudg.MedicalCareBudgTypeTariff_Value, 0) * coalesce(tap.EvnUsluga_Kolvo, 1)  
                end
            when tap.PayType_id = 1 then 
                case 
                    when tap.EvnClass_id = 6 then 
                        coalesce(tap.EvnUsluga_Kolvo, 1)
                        * coalesce(UED.UslugaComplexTariff_UED, 0) 
                        * coalesce(UET.AttributeValue_ValueFloat, 0)
                        * case when coalesce(tap.EvnUsluga_IsVizitCode, 1) = 2 then coalesce(STOM.AttributeValue_ValueFloat, 1) else 1 end
                    else 
                        coalesce(Tariff.UslugaComplexTariff_Tariff, 0)
                        * coalesce(tap.EvnUsluga_Kolvo, 1)
                        * case when UCAT.UslugaComplexAttributeType_Code is not null then coalesce(AttributeValue.AttributeValue_ValueFloat, 1) else 1 end 
                end 
       end), 0) as "UslugaComplex_summa",
    coalesce(reg.RegistryData_ItogSum, 0) as "RegistryData_ItogSum",
    tarifbudg.MedicalCareBudgTypeTariff_Value as "MedicalCareBudgTypeTariff_Value"
from params 
join tap on true
left join v_MedPersonal as MedPersonal  
    on MedPersonal.MedPersonal_id = tap.MedPersonal_id
    and MedPersonal.Lpu_id = tap.Lpu_id 
left join v_Address ua  
    on tap.UAddress_id = ua.Address_id
left join v_Address pa  
    on tap.PAddress_id = pa.Address_id
left join Address  
    on Address.Address_id = coalesce(tap.PAddress_id, tap.UAddress_id)

left join lateral (
					select org.org_nick as lpu_nick
					from lpu
					join org on org.org_id=lpu.org_id
					where lpu_id=tap.mo_prikr
				) lpu_pr on true

/*left join v_lpu lpu_pr   
    on lpu_pr.lpu_id = tap.MO_prikr*/

left join lateral (
    select 
        RD.RegistryData_ItogSum
    from r45.v_RegistryData RD  
    where RD.Evn_id = tap.Ev_id
		and rd.RegistryData_ItogSum>0
    limit 1
) reg on true

/*left join lateral (
    select 
        RD.RegistryData_ItogSum
    from r12.v_RegistryData RD  
    where RD.Evn_rid = tap.EvnPL_id
    limit 1
) reg on true*/

left join lateral (
    select 
        medicalcarebudgtypetariff_id,
        MedicalCareBudgType_id,
        Lpu_id,
        PayType_id,
        QuoteUnitType_id,
        tb.MedicalCareBudgTypeTariff_Value
    from v_MedicalCareBudgTypeTariff tb 
    where PayType_id in (3, 6)
        and tb.MedicalCareBudgType_id = tap.MedicalCareBudgType_id
        and 2 = 
            case 
                when tb.Lpu_id is null then 2
                when tb.Lpu_id is not null and tb.lpu_id=p_Lpu_id then 2 
            end
        and (MedicalCareBudgTypeTariff_endDT > p_enddate or MedicalCareBudgTypeTariff_endDT is null)
    limit 1
) tarifbudg on true
left join lateral (
    select  
        OMSSprTerr_id, 
        OMSSprTerr_Code 
    from OMSSprTerr as OMSSprTerr 
    where (Address.KLCountry_id = OMSSprTerr.KLCountry_id or OMSSprTerr.KLCountry_id is null) 
        and (Address.KLRGN_id = OMSSprTerr.KLRGN_id or OMSSprTerr.KLRGN_id is null) 
        and (Address.KLSubRGN_id = OMSSprTerr.KLSubRGN_id or OMSSprTerr.KLSubRGN_id is null) 
        and (Address.KLCity_id = OMSSprTerr.KLCity_id or OMSSprTerr.KLCity_id is null) 
        and (Address.KLTown_id = OMSSprTerr.KLTown_id or OMSSprTerr.KLTown_id is null)
    order by OMSSprTerr_id 
    limit 1
) OMSSprTerr on true -- Территория регистрации
left join SocStatus ss 
    on tap.SocStatus_id = ss.SocStatus_id
left join KLCountry kl 
    on kl.KLCountry_id = tap.KLCountry_id
left join Document doc 
    on doc.Document_id = tap.Document_id
left join DocumentType dt 
    on dt.DocumentType_id = doc.DocumentType_id
-- Застрахован
left join lateral (
    select  
        PersonPolis.Polis_id
    from v_PersonPolis as PersonPolis
    where PersonPolis.Person_id = tap.Person_id
        and ((
                dbo.AgeYMD(tap.Person_BirthDay, tap.evnpl_disDate, 2) > 2
                and cast(PersonPolis.Polis_begDate as date) <= cast(tap.evnpl_disDate as date)
                and (cast(PersonPolis.Polis_endDate as date) >= cast(tap.evnpl_disDate as date) or PersonPolis.Polis_endDate is null)
            ) or (
                dbo.AgeYMD(tap.Person_BirthDay, tap.evnpl_disDate, 2) <= 2 
                and cast(PersonPolis.Polis_begDate as date) <= cast(dateadd('month',2,tap.Person_BirthDay) as date)
                and (cast(PersonPolis.Polis_endDate as date) >= cast(dateadd('month',2,tap.Person_BirthDay) as date) or PersonPolis.Polis_endDate is null)
            )
        ) 
    order by PersonPolis.Polis_begDate desc
    limit 1
/*
    Если это ребенок до 2х месяцев, то берется полис не на момент вызова, 
    а полис который добавили в течении 2х месяцев с момента рождения, поскольку у новорожденных детей его нет.
*/
) strahov on true
-- Для суммы
left join v_MedStaffFact as MedStaffFact   
    on MedStaffFact.MedStaffFact_id = tap.EvnUslugaMedStaffFact_id
left join MedSpecOms 
    on MedSpecOms.MedSpecOms_id = MedStaffFact.MedSpecOms_id
left join fed.MedSpec as ms  
    on MedSpecOms.MedSpec_id = ms.MedSpec_id
left join lateral (
    select  
        UslugaComplexTariff_id, 
        UslugaComplexTariff_Tariff
    from UslugaComplexTariff 
    where UslugaComplexTariff.UslugaComplex_id = tap.UslugaComplex_id
        and (UslugaComplexTariff.mesagegroup_id = case when age >= 18 then 1 else 2 end or UslugaComplexTariff.mesagegroup_id is null)
        and UslugaComplexTariff.PayType_id = 1
        and UslugaComplexTariff.UslugaComplexTariff_begDate <= tap.EvnPL_disDate 
        and (UslugaComplexTariff.UslugaComplexTariff_endDate is null or UslugaComplexTariff.UslugaComplexTariff_endDate >= tap.EvnPL_disDate)
        and (UslugaComplexTariff.LpuSection_id = tap.LpuSection_uid or UslugaComplexTariff.LpuSection_id is null)
        and (UslugaComplexTariff.LpuSectionProfile_id not in (35113, 35119) or UslugaComplexTariff.LpuSectionProfile_id is null)
        and ((tap.EvnPL_disDate >= '2015-06-01' and coalesce(UslugaComplexTariff.LpuUnitType_id, 1) not in (13, 14)) or tap.EvnPL_disDate < '2015-06-01')
        and UslugaComplexTariff_Tariff is not null   
    order by 
        UslugaComplexTariff.Lpu_id desc, 
        UslugaComplexTariff.LpuSection_id desc, 
        UslugaComplexTariff.UslugaComplexTariff_begDate desc
    limit 1
) Tariff on true
left join lateral (
    select 
        UslugaComplexTariff_id, 
        UslugaComplexTariff_UED
    from UslugaComplexTariff 
    where UslugaComplexTariff.UslugaComplex_id = tap.UslugaComplex_id
        and (UslugaComplexTariff.mesagegroup_id=case when age >= 18 then 1 else 2 end or UslugaComplexTariff.mesagegroup_id is null) -- #64516
        and UslugaComplexTariff.PayType_id = 1
        and UslugaComplexTariff.Lpu_id is null 
        and UslugaComplexTariff.UslugaComplexTariff_begDate <= cast(tap.EvnPL_disDate as date)
        and (UslugaComplexTariff.UslugaComplexTariff_endDate is null or UslugaComplexTariff.UslugaComplexTariff_endDate >= cast(tap.EvnPL_disDate as date))  ---#85457
        and (UslugaComplexTariff.LpuSection_id = tap.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 ((tap.EvnPL_disDate >= '2015-06-01' and coalesce(UslugaComplexTariff.LpuUnitType_id,1) not in (13,14)) or tap.EvnPL_disDate < '2015-06-01')
    order by UslugaComplexTariff.UslugaComplexTariff_begDate desc
    limit 1
) UED on true
left join lateral (  
    select   
        UslugaComplexAttributeType_Code 
    from UslugaComplexAttribute  
    left join UslugaComplexAttributeType  
        on UslugaComplexAttribute.UslugaComplexAttributeType_id = UslugaComplexAttributeType.UslugaComplexAttributeType_id
    where tap.UslugaComplex_id = UslugaComplexAttribute.UslugaComplex_id
        and UslugaComplexAttributeType_Code in ('11', '32')  -- #75321
    limit 1
) UCAT on true
left join lateral (
    select 
        count(case when UslugaComplexAttribute.UslugaComplexAttributeType_id = 35 then 1 end) as attr_35
    from UslugaComplexAttribute  
    where UslugaComplexAttribute.UslugaComplex_id = tap.UslugaComplex_id
        and (UslugaComplexAttribute_begDate <= tap.EvnPL_disDate or UslugaComplexAttribute_begDate is null) 
        and (UslugaComplexAttribute_endDate >= tap.EvnPL_disDate or UslugaComplexAttribute_endDate is null)
) attr on true
left join lateral (
    select
        coalesce(av1.AttributeValue_ValueFloat, av1.AttributeValue_ValueInt) as AttributeValue_ValueFloat
    from AttributeValue av
    left join dbo.Attribute  
        on av.Attribute_id = Attribute.Attribute_id
    left join v_TariffClass TariffClass 
        on av.AttributeValue_TablePKey = TariffClass.TariffClass_id
    left join dbo.AttributeValue av1  
        on av.AttributeValue_rid = av1.AttributeValue_id
    where Attribute_Code = 103 -- атрибут МО
        and TariffClass_Code = '2015-01ПолкаК' -- вид тарифа
        and av.AttributeValue_ValueIdent = p_Lpu_id
        and (av1.AttributeValue_enddate >= tap.Evnpl_disdate or av1.AttributeValue_enddate is null)
        and av1.AttributeValue_begdate <= tap.Evnpl_disdate
) AttributeValue on true
left join lateral(
    select 
        av1.AttributeValue_ValueFloat
    from AttributeValue av
    left join dbo.Attribute 
        on av.Attribute_id=Attribute.Attribute_id
    left join v_TariffClass TariffClass 
        on av.AttributeValue_TablePKey = TariffClass.TariffClass_id
    left join dbo.AttributeValue av1 
        on av.AttributeValue_rid = av1.AttributeValue_id
    where Attribute_Code = 111 -- атрибут Базовые ставки УЕТ, 
        and TariffClass_Code = '2015-01БСУЕТ' -- вид тарифа
        and av.AttributeValue_ValueIdent = case when ms.MedSpec_pid = 196 then 3 else case when age >= 18 then 1 else 2 end end 
            and (av1.AttributeValue_enddate >= tap.Evnpl_disdate or av1. AttributeValue_enddate is null)
            and av1.AttributeValue_begdate <= tap.Evnpl_disdate
) UET on true
left join lateral(
    select
        coalesce(av1.AttributeValue_ValueFloat, av1.AttributeValue_ValueInt) AttributeValue_ValueFloat 
    from AttributeValue av
    left join dbo.Attribute 
        on av.Attribute_id = Attribute.Attribute_id
    left join v_TariffClass TariffClass 
        on av.AttributeValue_TablePKey = TariffClass.TariffClass_id
    left join dbo.AttributeValue av1 
        on av.AttributeValue_rid = av1.AttributeValue_id
    where Attribute_Code = 103 -- атрибут МО
        and TariffClass_Code = '2015-01СтомК' -- вид тарифа
        and av.AttributeValue_ValueIdent = p_Lpu_id
        and (av1.AttributeValue_enddate >= tap.Evnpl_disdate or av1.AttributeValue_enddate is null)
        and av1.AttributeValue_begdate <= tap.Evnpl_disdate
) STOM on true
where 1 = 1
    and (OmsSprTerr.OMSSprTerr_Code = p_AddressType_id or p_AddressType_id is null)
   -- and ((strahov.Polis_id is null and kl.KLCountry_name = 'РОССИЯ') or kl.KLCountry_name <> 'РОССИЯ' or kl.KLCountry_name is null)
    /*and 2 = 
        case 
            when p_PayType_id is null and strahov.Polis_id is null then 2
            when p_PayType_id is not null and tap.PayType_id = p_PayType_id then 2  
        end            -- вид оплаты 
    */
  and 2= case when strahov.Polis_id is null and p_nozastrah=2 then 2
    --when p_nozastrah=1 then 2 end 
    when p_nozastrah=1 and ((coalesce(tap.KLCountry_id,643) = 643 and strahov.Polis_id is null) or coalesce(tap.KLCountry_id,643) <> 643) then 2 end
