
                
            with mv as (
                select
                localtimestamp as dt
            )
            
                
                SELECT 
                -- TOP SL, FIXDATE 18.05.2023, JIRA PROMEDWEB-148004
        
            '' as "accessType",
            CCC.CmpCallCard_id as "CmpCallCard_id",
            CCC.CmpCallCard_id||''||CLC.CmpCloseCard_id as "CmpCallCard_uid",
            ETLB.LpuBuilding_id as "ETLBLpuBuilding_id",
            coalesce(CCC.MedPersonal_id, CLC.MedPersonal_id) as "MedPersonal_id",
            PS.Person_id as "Person_id",
            PS.PersonEvn_id as "PersonEvn_id",
            PS.Server_id as "Server_id",
            ET.EmergencyTeamSpec_id as "EmergencyTeamSpec_id",
            CLC.EmergencyTeamNum as "EmergencyTeamNum",
            CCC.CmpCallCardInputType_id as "CmpCallCardInputType_id",
            to_char(CCC.CmpCallCard_prmDT, 'DD.MM.YYYY') as "CmpCallCard_prmDate",
            to_char(CCC.CmpCallCard_prmDT, 'HH24:MI:SS') as "CmpCallCard_prmTime",
            to_char(CCC.CmpCallCard_Przd, 'HH24:MI:SS') as "CmpCallCard_Przd",
            coalesce(CLC.Day_num, CCC.CmpCallCard_Numv) as "CmpCallCard_Numv_int",
            ltrim(rtrim(coalesce(CLC.Day_num, CCC.CmpCallCard_Numv)||' '||coalesce(CLC.CmpCloseCard_DayNumPr, ''))) as "CmpCallCard_Numv",
            LB.LpuBuilding_Name as "LpuBuilding_Name",
            CLC.CmpCloseCard_id as "CmpCloseCard_id",
            rtrim(coalesce(CLC.Fam, PS.Person_Surname, CCC.Person_SurName)) as "Person_Surname",
            rtrim(coalesce(CLC.Name, PS.Person_Firname, CCC.Person_FirName)) as "Person_Firname",
            rtrim(coalesce(CLC.Middle, PS.Person_Secname, CCC.Person_SecName)) as "Person_Secname",
            to_char(coalesce(PS.Person_BirthDay, PSCLC.Person_BirthDay, CCC.Person_BirthDay), 'DD.MM.YYYY') as "Person_Birthday",
            coalesce(dbo.Age2(PS.Person_BirthDay, (select dt from mv)), CLC.Age) as "Person_Age",
            case when PS.Person_id is not null and coalesce(PS.Person_IsUnknown, 1) != 2 then 'true' else 'false' end as "Person_IsIdentified",
            (case when CLC.CallPovod_id is not null then coalesce(CR.CmpReason_Code||'. ', '')||CR.CmpReason_Name else coalesce(CRTalon.CmpReason_Code||'. ', '')||CRTalon.CmpReason_Name end) as "CmpReason_Name",
            case when (CCC.CmpCallCardInputType_id in (1,2)) then coalesce(Lpu.Lpu_Nick, Lpu.Lpu_Name, '') else coalesce(LpuHid.Lpu_Nick, LpuHid.Lpu_Name, '') end as "CmpLpu_Name",
            rtrim(case when CLD.diag_FullName is not null then CLD.diag_FullName else CD.CmpDiag_Code end) as "CmpDiag_Name",
            rtrim(coalesce(D.Diag_Name, '')) as "StacDiag_Name",
            coalesce(PAddr.Address_Address, rtrim(replace(CCC.CmpCallCard_PCity, '=', ''))||', '||rtrim(CCC.CmpCallCard_PUlic)||', '||rtrim(CCC.CmpCallCard_PDom)||', '||rtrim(CCC.CmpCallCard_PKvar), '') as "Person_Address",
            
                case when pls.Polis_endDate is not null and pls.Polis_endDate <= (select dt from mv)
then 'orange'
else case when ps.PersonCloseCause_id = 2 and ps.Person_closeDT is not null
        then 'red'
        else case when ps.Server_pid = 0
                then 'true'
                else 'false'
        end
end
                end as "Person_IsBDZ",
            
            to_char(ccp.CmpCallCardCostPrint_setDT, 'DD.MM.YYYY') as "CmpCallCardCostPrint_setDT",
            case when ccp.CmpCallCardCostPrint_IsNoPrint = 2 then 'Отказ от справки' when ccp.CmpCallCardCostPrint_IsNoPrint = 1 then 'Справка выдана' else '' end as "CmpCallCardCostPrint_IsNoPrintText",
            CCCInput.CmpCallCardInputType_Name as "CmpCallCardInputType_Name",
            case when
                coalesce(CCC.cmpcallcard_isMultipleChoiceChecking, 2) = 1 then 'false'
                else 'true'
            end as "cmpcallcard_isMultipleChoiceChecking",
            CASE WHEN CLC.Signatures_id is null then 1 ELSE 2 END as "IsSigned",
            CASE WHEN CLC.Signatures_id is null then 'false' ELSE 'true' END as "isSigned",
            CASE WHEN CLC.MedStaffFact_ctrid is null then 'false' ELSE 'true' END as "isConfirmed"
        
            
                FROM
                
        
            v_CmpCallCard CCC
            left join dbo.v_CmpCloseCard CLC on CLC.CmpCallCard_id = CCC.CmpCallCard_id
            left join v_CmpCallCardCostPrint ccp on ccp.CmpCallCard_id = ccc.CmpCallCard_id
            left join CmpReason CR on CR.CmpReason_id = CLC.CallPovod_id
            left join CmpReason CRTalon on CRTalon.CmpReason_id = CCC.CmpReason_id
            left join CmpResult CRES on CRES.CmpResult_id = CCC.CmpResult_id
            left join CmpLpu CL on CL.CmpLpu_id = CCC.CmpLpu_id
            left join EmergencyTeam ET on ET.EmergencyTeam_id = CCC.EmergencyTeam_id
            left join v_Lpu L on L.Lpu_id = CL.Lpu_id
            left join v_Lpu Lpu on CCC.CmpLpu_id = Lpu.Lpu_id
            left join v_Lpu LpuHid on CCC.CmpLpu_id = LpuHid.Lpu_id
            left join v_Diag CLD on CLD.Diag_id = CLC.Diag_id
            left join v_Diag D on D.Diag_id = CCC.Diag_sid
            left join v_CmpDiag CD on CD.CmpDiag_id = CCC.CmpDiag_oid
            left join v_Diag UD on UD.Diag_id = CCC.Diag_uid
            left join v_PersonState PS on CCC.Person_id = PS.Person_id
            left join v_PersonState PSCLC on CLC.Person_id = PSCLC.Person_id
            left join v_Polis pls on pls.Polis_id = ps.Polis_id
            left join Address PAddr on PAddr.Address_id = PS.PAddress_id
            left join LpuBuilding LB on LB.LpuBuilding_id = CLC.LpuBuilding_id
            left join v_LpuBuilding ETLB on ETLB.LpuBuilding_id = ET.LpuBuilding_id
            left join CmpCallCardInputType CCCInput on CCCInput.CmpCallCardInputType_id = CCC.CmpCallCardInputType_id
            left join lateral(
                select
                    CCLCR.CmpCloseCardCombo_id,
                    CCLCR.Localize
                from
                    dbo.v_CmpCloseCardRel CCLCR
                    left join dbo.v_CmpCloseCardCombo CCLCB on CCLCB.CmpCloseCardCombo_id = CCLCR.CmpCloseCardCombo_id
                where CCLCR.CmpCloseCard_id = CLC.CmpCloseCard_id
                  and CCLCB.CmpCloseCardCombo_Code = 693
                order by CCLCR.CmpCloseCardRel_id desc
                limit 1
            ) isActiveCombo on true
            left join lateral(
                select
                    CCLCR.CmpCloseCardCombo_id,
                    CCLCR.Localize
                from
                    dbo.v_CmpCloseCardRel CCLCR
                    left join dbo.v_CmpCloseCardCombo CCLCB on CCLCB.CmpCloseCardCombo_id = CCLCR.CmpCloseCardCombo_id
                    left join dbo.v_CmpCloseCardCombo parentCCLCB on parentCCLCB.CmpCloseCardCombo_id = CCLCB.Parent_id
                where CCLCR.CmpCloseCard_id = CLC.CmpCloseCard_id
                  and parentCCLCB.CmpCloseCardCombo_Code = 142
                order by CCLCR.CmpCloseCardRel_id desc
                limit 1
            ) socialCombo on true
            left join lateral(
                select
                    CCLCR.CmpCloseCardCombo_id,
                    CCLCR.Localize
                from
                    dbo.v_CmpCloseCardRel CCLCR
                    left join dbo.v_CmpCloseCardCombo CCLCB on CCLCB.CmpCloseCardCombo_id = CCLCR.CmpCloseCardCombo_id
                    left join dbo.v_CmpCloseCardCombo parentCCLCB on parentCCLCB.CmpCloseCardCombo_id = CCLCB.Parent_id
                where CCLCR.CmpCloseCard_id = CLC.CmpCloseCard_id
                  and parentCCLCB.CmpCloseCardCombo_Code = 223
                order by CCLCR.CmpCloseCardRel_id desc
                limit 1
            ) resultCombo on true
            left join lateral(
                select CCCE.CmpCallCardEvent_id
                from
                    v_CmpCallCardEvent CCCE
                    left join v_CmpCallCardEventType CCCET on CCCE.CmpCallCardEventType_id = CCCET.CmpCallCardEventType_id
                where CCCE.CmpCallCard_id = CCC.CmpCallCard_id
                  and CCCET.CmpCallCardEventType_Code = '6'
                limit 1
            ) as acceptNmpFlag on true
        
                         WHERE 
                        (1 = 1) and CCC.Lpu_id in (560101000000416) and (CLC.CmpCloseCard_id > 0) and coalesce(CLD.Diag_Code, '') not in('Z20.6','R75','Z11.4','Z71.7','Z83.0','Z21') and coalesce(CLD.Diag_Code, '') not between 'F10.0' and 'F19.9' and coalesce(CLD.Diag_Code, '') not between 'F00.0' and 'F09' and coalesce(CLD.Diag_Code, '') not between 'F20.0' and 'F99' and coalesce(CLD.Diag_Code, '') not between 'B20.0' and 'B24' and coalesce(CLD.Diag_Code, '') not between 'A50.0' and 'A64' and coalesce(CD.CmpDiag_Code, '') not in('Z20.6','R75','Z11.4','Z71.7','Z83.0','Z21') and coalesce(CD.CmpDiag_Code, '') not between 'F10.0' and 'F19.9' and coalesce(CD.CmpDiag_Code, '') not between 'F00.0' and 'F09' and coalesce(CD.CmpDiag_Code, '') not between 'F20.0' and 'F99' and coalesce(CD.CmpDiag_Code, '') not between 'B20.0' and 'B24' and coalesce(CD.CmpDiag_Code, '') not between 'A50.0' and 'A64'
                        
                
                        ORDER BY
                            
                        PS.Person_Surname,
                        CCC.Person_SurName,
                        PS.Person_Firname,
                        CCC.Person_FirName,
                        PS.Person_Secname,
                        CCC.Person_SecName
                        
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
