
                
            with curDT as (select localtimestamp as curDate),
                curD as (select localtimestamp::date as curDate),
                curDTY as (select date_part('year', localtimestamp) as curDate),
                RG AS (SELECT 91 as region_id),

                locCTE as (select
                    LOC3.PersonModel_id,
                    LOC3.DispOutType_id,
                    LOC3.LabelObserveChart_id,
                    LOC3.PersonDisp_id,
                    LOC3.LabelObserveChart_begDate,
                    LOC3.LabelObserveChart_endDate,
                    LOC3.LabelObserveChart_IsAutoClose,
                    LOC3.LabelObserveChart_Phone,
                    LOC3.LabelObserveChart_Email,
                    LOC3.FeedbackMethod_id,LOC3.PersonLabel_id
                from v_LabelObserveChart LOC3
                    inner join v_MedStaffFactcache MSF on MSF.MedStaffFact_id = LOC3.MedStaffFact_id
                where   MSF.Lpu_id = '150488')

            
                
                SELECT 
                PL.Person_id as "Person_id",
                PL.PersonLabel_id as "PersonLabel_id",
                PS.Server_id as "Server_id",
                PS.PersonEvn_id as "PersonEvn_id",
                PS.Sex_id as "Sex_id",
                LI.LabelInvite_id as "LabelInvite_id",
                LI.LabelInviteStatus_id as "LabelInviteStatus_id",
                LI.LabelInvite_RefuseCause as "LabelInvite_RefuseCause",
                LI.FeedbackMethod_id as "LabelInviteFeedbackMethod_id",
                to_char(LI.LabelInvite_updDT, 'YYYY-MM-DD') as "LabelInviteStatus_Date",
                RTRIM(LTRIM(coalesce(PS.Person_Phone, ''))) as "Person_Phone",
                RTRIM(LTRIM(coalesce(LOC.LabelObserveChart_Phone, ''))) as "Chart_Phone",
                LOC.LabelObserveChart_Email as "Chart_Email",
                RTRIM(LTRIM(coalesce(UPPER(PS.Person_SurName),''))) as "Person_SurName",
                RTRIM(LTRIM(coalesce(UPPER(PS.Person_FirName),''))) as "Person_FirName",
                RTRIM(LTRIM(coalesce(UPPER(PS.Person_SecName),''))) as "Person_SecName",
                
            case
                
                when datediff('day', cast(PS.Person_Birthday as date), (select curDate from curDT)) <= 31
                    then '' || datediff('day', cast(PS.Person_Birthday as date), (select curDate from curDT)) ||
                    case
                        when
                            datediff('day', cast(PS.Person_Birthday as date), (select curDate from curDT)) >= 2
                            and datediff('day', cast(PS.Person_Birthday as date), (select curDate from curDT)) <= 4
                            or
                            datediff('day', cast(PS.Person_Birthday as date), (select curDate from curDT)) >= 22
                            and datediff('day', cast(PS.Person_Birthday as date), (select curDate from curDT)) + 1 <= 24
                        then ' дня'
                        when datediff('day', cast(PS.Person_Birthday as date), (select curDate from curDT)) in (1, 21, 31)
                        then ' день'
                        else ' дней'
                    end
                when datediff('day', cast(PS.Person_Birthday as date), (select curDate from curDT)) + 1 > 31 and dbo.Age_newborn(cast(PS.Person_Birthday as date), (select curDate from curDT)) < 12
                    then '' || dbo.Age_newborn(cast(PS.Person_Birthday as date), (select curDate from curDT))::varchar || ' мес.'

                when dbo.Age_newborn(cast(PS.Person_Birthday as date), (select curDate from curDT)) >= 12 and dbo.Age_newborn(cast(PS.Person_Birthday as date), (select curDate from curDT)) < 36
                    then '' || dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT))::varchar ||
                        case
                            when dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT)) = 2 then ' года '
                            else ' год '
                        end ||
                        case
                            when (dbo.Age_newborn(cast(PS.Person_Birthday as date), (select curDate from curDT)) - (dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT)) * 12)) < 1 then ''
                            else (dbo.Age_newborn(cast(PS.Person_Birthday as date), (select curDate from curDT)) - (dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT)) * 12))::varchar || ' мес.'
                        end

                when dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT)) >= 3
                    then '' || dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT))::varchar ||
                        case
                    when
                        right(dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT))::varchar,2)::integer >= 11
                        and right(dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT))::varchar,2)::integer <= 14
                        or right(dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT))::varchar,1)::integer >= 5
                        and right(dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT))::varchar,1)::integer <= 9
                        or right(dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT))::varchar,1)::integer = 0
                    then ' лет'
                    when
                        right(dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT))::varchar,1)::integer >= 2
                        and right(dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT))::varchar,1)::integer <= 4
                    then ' года'
                    when right(dbo.Age2(cast(PS.Person_Birthday as date), (select curDate from curDT))::varchar,1)::integer = 1
                    then ' год'
                end
                    else '' || dbo.Age_newborn(cast(PS.Person_Birthday as date), (select curDate from curDT))::varchar || ' мес.'
            end
            as "PersonAge"
        ,
                to_char(PS.Person_BirthDay, 'YYYY-MM-DD') as "Person_BirthDay",
                to_char(PS.Person_deadDT, 'YYYY-MM-DD') as "Person_DeadDT",
                L.Label_id as "Label_id",
                PM.PersonModel_id as "PersonModel_id",
                PM.PersonModel_Name as "PersonModel_Name",
                LOC.LabelObserveChart_id as "Chart_id",
                to_char(LOC.LabelObserveChart_begDate, 'DD.MM.YYYY') as "Chart_begDate",
                to_char(LOC.LabelObserveChart_endDate, 'DD.MM.YYYY') as "Chart_endDate",
                LOC.LabelObserveChart_IsAutoClose as "Chart_IsAutoClose",
                LOC.PersonDisp_id as "ChartDisp_id",
                DOT.DispOutType_Name as "DispOutType_Name",
                DOT.DispOutType_Code as "DispOutType_Code",
                to_char(ci.LabelObserveChartInfo_ObserveDate, 'YYYY-MM-DD') as "lastObserveDate",
                CM1.LabelObserveChartMeasure_Value as "Rate1_Value",
                CM2.LabelObserveChartMeasure_Value as "Rate2_Value",
                CM4.LabelObserveChartMeasure_Value as "Rate4_Value",
                CR1.LabelObserveChartRate_Min as "Rate1_Min",
                CR1.LabelObserveChartRate_Max as "Rate1_Max",
                CR2.LabelObserveChartRate_Min as "Rate2_Min",
                CR2.LabelObserveChartRate_Max as "Rate2_Max",
                CR4.LabelObserveChartRate_Min as "Rate4_Min",
                CR4.LabelObserveChartRate_Max as "Rate4_Max",
                D.Diag_Code as "Diag_Code",
                D.Diag_Name as "Diag_Name",
                to_char(PD.PersonDisp_begDate, 'DD.MM.YYYY') as "PersonDisp_begDate",
                to_char(PD.PersonDisp_endDate, 'DD.MM.YYYY') as "PersonDisp_endDate",
                PDCh.PersonDisp_id as "ChartPersonDisp_id",
                PD.PersonDisp_id as "PersonDisp_id",
                case
                    when LOC.LabelObserveChart_id is null then 'new'
                    when LOC.LabelObserveChart_endDate is not null then 'off'
                    when LOC.LabelObserveChart_id is not null then 'on'
                    else 'undefined'
                END as "StatusNick",
                pcard.Lpu_id as "Lpu_id",
                coalesce(pcard_lpu.Lpu_Nick, '') as "Lpu_Nick",
                pcard.LpuRegion_Name as "AttachNum",
                coalesce(pcard_lpu.Lpu_Nick, '')||right('          ' || coalesce(pcard.LpuRegion_Name,''), 10) as "Attach",
                pcard.PersonCard_begDate as "AttachDate",
                PL.PersonLabel_disDate as "PersonLabel_disDate",
                FM.FeedbackMethod_id as "FeedbackMethod_id",
                FM.FeedbackMethod_Name as "FeedbackMethod_Name",
                case
                    when LOC.LabelObserveChart_id is null  then 10 + coalesce(LI.LabelInviteStatus_id, 0)
                    when LOC.LabelObserveChart_endDate is not null then 30
                    when LOC.LabelObserveChart_id is not null then 20 + coalesce(LOC.PersonModel_id, 0)
                end as "Status",
                coalesce(PR.PersonRefuse_IsRefuse,1) - 1 as "Person_IsRefuse",
                case  when PersonPrivilegeFed.Person_id is not null then 1 else 0 end as "Person_IsFedLgot",
                case
                    when regl.Lpu_id is null then 0
                    when regl.Lpu_id = '150488' then 1
                    else 0
                END as "Person_IsRegLgot"
                ,pers_labels.PersLabels as "PersLabels"
                
                FROM
                
                v_PersonLabel PL
                inner join v_PersonState PS on PS.Person_id = PL.Person_id
                inner join v_Label L on L.Label_id = PL.Label_id
                left join lateral(
                    select
                        LI1.LabelInvite_id,
                        LI1.LabelInviteStatus_id,
                        LI1.LabelInvite_updDT,
                        LI1.LabelInvite_RefuseCause,
                        LI1.FeedbackMethod_id
                    from
                        v_LabelInvite LI1
                        inner join v_MedStaffFactcache MSF on MSF.MedStaffFact_id = LI1.MedStaffFact_id
                    where LI1.PersonLabel_id = PL.PersonLabel_id
                      and LI1.LabelInviteStatus_id is not null
                      and MSF.Lpu_id = '150488'
                    order by LI1.LabelInvite_id desc
                    limit 1
                ) as LI on true

                left join lateral (
                    select LOC4.*
                    from locCTE LOC4
                    where LOC4.PersonLabel_id = PL.PersonLabel_id
                    order by
                        coalesce(LOC4.LabelObserveChart_endDate, (select curDate from curDT)) desc,
                        LabelObserveChart_id desc
                    limit 1
                ) LOC on true

                left join v_PersonModel PM on PM.PersonModel_id = LOC.PersonModel_id
                left join v_DispOutType DOT on DOT.DispOutType_id = LOC.DispOutType_id
                left join v_Diag D on D.Diag_id = PL.Diag_id
                left join FeedbackMethod FM on FM.FeedbackMethod_id = LOC.FeedbackMethod_id
                left join v_PersonRefuse PR ON PR.Person_id = PL.Person_id and PR.PersonRefuse_IsRefuse = 2 and PR.PersonRefuse_Year = (select curDate from curDTY)
                left join lateral(
                    select
                        CI.LabelObserveChartInfo_id,
                        CI.LabelObserveChartInfo_ObserveDate
                    from
                        v_LabelObserveChartInfo CI
                        inner join v_LabelObserveChartMeasure CM on CM.LabelObserveChartInfo_id = CI.LabelObserveChartInfo_id
                    where CI.LabelObserveChart_id = LOC.LabelObserveChart_id
                      and CI.LabelObserveChartInfo_ObserveDate >= DATEADD('DAY', -1, localtimestamp)
                    order by
                        CI.LabelObserveChartInfo_ObserveDate desc,
                        CI.TimeOfDay_id desc,
                        CI.LabelObserveChartInfo_id desc
                    limit 1
                ) CI on true
                left join v_LabelObserveChartRate CR1 on CR1.LabelObserveChart_id=LOC.LabelObserveChart_id and CR1.LabelRate_id = 1
                left join v_LabelObserveChartMeasure CM1 on CM1.LabelObserveChartRate_id = CR1.LabelObserveChartRate_id and CM1.LabelObserveChartInfo_id = CI.LabelObserveChartInfo_id
                left join v_LabelObserveChartRate CR2 on CR2.LabelObserveChart_id=LOC.LabelObserveChart_id and CR2.LabelRate_id = 2
                left join v_LabelObserveChartMeasure CM2 on CM2.LabelObserveChartRate_id = CR2.LabelObserveChartRate_id and CM2.LabelObserveChartInfo_id = CI.LabelObserveChartInfo_id
                left join v_LabelObserveChartRate CR4 on CR4.LabelObserveChart_id=LOC.LabelObserveChart_id and CR4.LabelRate_id = 4
                left join v_LabelObserveChartMeasure CM4 on CM4.LabelObserveChartRate_id = CR4.LabelObserveChartRate_id and CM4.LabelObserveChartInfo_id = CI.LabelObserveChartInfo_id
                left join lateral(
                    select
                        PP.Person_id,
                        PP.PrivilegeType_id,
                        PT.PrivilegeType_Name
                    from
                        PersonPrivilege PP
                        inner join v_PrivilegeType PT on PT.PrivilegeType_id = PP.PrivilegeType_id
                    where PT.ReceptFinance_id = 1
                      and PP.PersonPrivilege_begDate <= (select curDate from curDT)
                      and coalesce(PP.PersonPrivilege_endDate, (select curDate from curDT)) >= (select curDate from curD)
                      and PP.Person_id = PL.Person_id
                      and PersonPrivilege_deleted = 1
                    limit 1
                ) as PersonPrivilegeFed on true
                left join lateral(
                    select Lpu_id
                    from
                        PersonPrivilege t1
                        inner join PrivilegeType t2 on t2.PrivilegeType_id = t1.PrivilegeType_id
                    where t1.Person_id = PL.Person_id
                      and t2.ReceptFinance_id = 2
                      and t1.PersonPrivilege_begDate <= (select curDate from curDT)
                      and coalesce(t1.PersonPrivilege_endDate, (select curDate from curDT)) >= (select curDate from curD)
                      and PersonPrivilege_deleted = 1
                    order by (case when t1.Lpu_id = '150488' then 1 else 0 end) desc
                    limit 1
                ) as regl on true
                left join v_PersonCard pcard on pcard.Person_id = PL.Person_id and LpuAttachType_id = 1
                left join lateral
                    (
                    select org.org_nick AS lpu_nick
                    FROM dbo.lpu
                             inner JOIN RG ON COALESCE(lpu.region_id, 0::bigint) = 0 OR COALESCE(lpu.region_id, 0::bigint) = RG.region_id
                             inner JOIN dbo.v_org org ON org.org_id = lpu.org_id
                    where lpu.lpu_id = pcard.lpu_id
                    ) pcard_lpu on true
                left join v_PersonDisp PDCh on PDCh.PersonDisp_id = LOC.PersonDisp_id and PDCh.PersonDisp_endDate is null and PDCh.Lpu_id='150488'
                left join lateral(
                    select
                        PD6.PersonDisp_id,
                        PD6.PersonDisp_begDate,
                        PD6.PersonDisp_endDate
                    from v_PersonDisp PD6
                    where PD6.Person_id = PL.Person_id
                      and PD6.Diag_id=PL.Diag_id
                      and PD6.Lpu_id='150488'
                      and PD6.PersonDisp_endDate is null
                    order by PD6.PersonDisp_id desc
                    limit 1
                ) as PD on true
                
                left join lateral(
                    select string_agg(L_ls.Label_Name, '<br>' order by PL_ls.Label_id) as PersLabels
                    from
                        v_PersonLabel PL_ls
                        inner join v_Label L_ls on L_ls.Label_id=PL_ls.Label_id
                    where PL_ls.Person_id = PS.Person_id
                      and PL_ls.Label_id not in (1, 7)
                      and PL_ls.PersonLabel_disDate is null
                ) as pers_labels on true
            
                 WHERE 
                PL.Label_id = '1' and LOC.LabelObserveChart_id is null and PL.PersonLabel_disDate is null and PS.Lpu_id = '150488' and coalesce(PS.Person_SurName,'')||' '||coalesce(PS.Person_FirName,'')||' '||coalesce(PS.Person_SecName,'') ILIKE '%'||'РОВ'||'%'
                
                
                        ORDER BY
                            
                PS.Person_SurName, PS.Person_FirName, PS.Person_SecName
                
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
