WITH cte AS (
    SELECT
        EvnPS.EvnPS_id,
        EvnSection.EvnSection_id,
        --EvnSection.mes_sid,
        --EvnSection.mes_tid,
        --COALESCE(sm.MesOld_Num, tm.MesOld_Num) AS mes_code,
        EvnSection.evnsection_iswillpaid,
        --EvnSection.mestariff_id,
        --EvnSection.mesolduslugacomplex_id,
        EvnSection.diag_id,
        jsonb_build_object (
            'evnsection_pid', evnsection_pid,
            'evnsection_id', evnsection_id,
            'evnsection_setdt', evnsection_setdate,
            'evnsection_disdt', evnsection_disdate,
            'person_id', EvnSection.person_id,
            'diag_id', EvnSection.diag_id,
            'paytype_id', EvnSection.paytype_id,
            'lpusection_id', EvnSection.lpusection_id,
            'lpusectionprofile_id', LpuSection.lpusectionprofile_id,
            'lpusectionbedprofile_id', LpuSection.lpusectionbedprofile_id,
            'lpuunittype_id', LpuUnit.lpuunittype_id,
            'rehabscale_id', EvnSection.rehabscale_id,
            'evnsection_sofascalepoints', EvnSection.evnsection_sofascalepoints,
            'evnsection_absence', EvnSection.EvnSection_Absence,
            'drugtherapyscheme_ids', array(select drugtherapyscheme_id from evnsectiondrugtherapyscheme where evnsectiondrugtherapyscheme.evnsection_id = EvnSection.evnsection_id),
            'mesdop_ids', array(select mesdop_id from mesdoplink where mesdoplink.evnsection_id = EvnSection.evnsection_id)
            ) AS i 
    FROM dbo.v_EvnPS AS EvnPS
    JOIN dbo.v_EvnSection AS EvnSection ON EvnSection.EvnSection_pid = EvnPS.EvnPS_id AND EvnPS.Lpu_id = EvnSection.Lpu_id
    JOIN dbo.v_LpuSection AS LpuSection ON LpuSection.lpusection_id = EvnSection.lpusection_id
    JOIN dbo.LpuUnit AS LpuUnit ON LpuSection.lpuunit_id = LpuUnit.lpuunit_id
    JOIN dbo.v_EvnLeaveBase AS EvnLeaveBase ON EvnLeaveBase.EvnLeaveBase_pid = EvnSection.EvnSection_id and EvnLeaveBase_index = EvnLeaveBase_count - 1
    LEFT JOIN dbo.v_mesold AS sm ON sm.mes_id = EvnSection.mes_sid
    LEFT JOIN dbo.v_mesold AS tm ON tm.mes_id = EvnSection.mes_tid
    LEFT JOIN dbo.mestariff AS mt ON mt.mestariff_id = EvnSection.mestariff_id
    LEFT JOIN dbo.mesolduslugacomplex AS moc ON EvnSection.mesolduslugacomplex_id = moc.mesolduslugacomplex_id
    LEFT JOIN dbo.LeaveType ON EvnPS.leavetype_id = LeaveType.leavetype_id
    WHERE 1 = 1
  AND EvnPS.EvnPS_disDate BETWEEN '2022-01-01' AND '2022-01-31'
    AND EvnPS.Lpu_id = 7
    --AND EvnSection.paytype_id = 520101000000009
    AND EvnSection.LeaveType_id IS NOT NULL
    AND NULLIF(EvnSection.EvnSection_IsPriem, 1) IS NULL
    AND
        (
         COALESCE(evnps_disdate, evnsection_disdate) > COALESCE(sm.mes_enddt, 'INFINITY') 
         OR COALESCE(evnps_disdate, evnsection_disdate) > COALESCE(tm.mes_enddt, 'INFINITY')
         OR COALESCE(evnps_disdate, evnsection_disdate) > COALESCE(mt.mestariff_enddt, 'INFINITY')
         OR COALESCE(evnps_disdate, evnsection_disdate) > COALESCE(moc.mesolduslugacomplex_enddt, 'INFINITY')
    )
    --AND EvnSection.EvnSection_id = 520101188047665
    --LIMIT 1
    )
		,upd AS (
    SELECT
        KSG.Mes_tid AS Mes_tid,
        KSG.Mes_sid AS Mes_sid,
        KSG.Mes_kid AS Mes_kid,
        KSG.MesTariff_id AS MesTariff_id,
        KSG.MesOldUslugaComplex_id AS MesOldUslugaComplex_id,
        MO.Mes_Code AS Mes_Code,
        MO.MesOld_Num AS MesOld_Num,
        MO.Mes_Name AS Mes_Name,
        MT.MesTariff_Value AS MesTariff_Value,
        coalesce(MOKPG.Mes_Code || COALESCE('. ' || MOKPG.Mes_Name, ''),'') AS KPG,
        KSG.Error_Message AS Error_Msg,
        cte.*              
    FROM cte
    LEFT JOIN r14.evnps_ksg_calc(cte.i) KSG ON TRUE
    LEFT JOIN v_MesOld MO ON MO.Mes_id = COALESCE(KSG.Mes_tid, KSG.Mes_sid, KSG.Mes_kid)
    LEFT JOIN v_MesTariff MT ON MT.MesTariff_id = KSG.MesTariff_id
    LEFT JOIN v_MesOld MOKPG ON MOKPG.Mes_id = KSG.Mes_kid  
    WHERE COALESCE(Mes_tid, Mes_sid, Mes_kid) IS NOT NULL
    )
UPDATE dbo.evnsection AS e
SET mes_sid = u.mes_sid, mes_tid = u.mes_tid, mes_kid = u.mes_kid, MesTariff_id = u.MesTariff_id, MesOldUslugaComplex_id = u.MesOldUslugaComplex_id
FROM upd AS u
WHERE e.evn_id = u.EvnSection_id
