
                
            with mv as (
                select
                localtimestamp as dt
            )
            
                
                SELECT 
                -- TOP SL, FIXDATE 18.05.2023, JIRA PROMEDWEB-148004
        
            ER.EvnRecept_id as "EvnRecept_id",
            ER.Person_id as "Person_id",
            ER.PersonEvn_id as "PersonEvn_id",
            ER.Server_id as "Server_id",
            Lpu.Lpu_Nick as "Lpu_Nick",
            Lpu.Lpu_id as "Lpu_id",
            ER.Drug_id as "Drug_id",
            ER.Drug_rlsid as "Drug_rlsid",
            ER.DrugComplexMnn_id as "DrugComplexMnn_id",
            ER.ReceptDelayType_id as "ReceptDelayType_id",
            ER.OrgFarmacy_oid as "OrgFarmacy_oid",
            RecOt.ReceptOtov_id as "ReceptOtov_id",
            concat(RecOtP.Person_SurName || ' ', RecOtP.Person_FirName || ' ', RecOtP.Person_SecName, ' / ' || RecOtP.Post_Name) as "PersonWork_FioPost",
            puc2.pmUser_Name as "ROSignPmUser_Name",
            (case when ER.EvnRecept_IsOtvSigned = 2 or (ER.pmUser_signOtvID is not null and ER.EvnRecept_signOtvDT is not null)
                    then 2
                    else 1
                end) as "EvnRecept_IsOtvSigned",
            case
                when ER.ReceptDelayType_id in ('1','2','3','5') then ER_RDT.ReceptDelayType_Name
                when (
    ER.ReceptDelayType_id is null and
    (
            (RV.ReceptValidType_id = 1 and ((ER.EvnRecept_setDate + RV.ReceptValid_Value * interval '1 day') >= (select dt from mv))) or
            (RV.ReceptValidType_id = 2 and ((ER.EvnRecept_setDate + RV.ReceptValid_Value * 30 * interval '1 day') >= (select dt from mv)))
    ) and
    ((ER.EvnRecept_otpDT is null) and (ER.EvnRecept_obrDT is null))
)
then 'Выписан'
                when
((RV.ReceptValidType_id = 1 and
((ER.EvnRecept_setDate + RV.ReceptValid_Value * interval '1 day') < (select dt from mv))) or
(RV.ReceptValidType_id = 2 and ((ER.EvnRecept_setDate + RV.ReceptValid_Value * 30 * interval '1 day') < (select dt from mv))))
then 'Просрочен'
else ''
            end as "ReceptDelayType_Name",
            (select Org_Name from v_OrgFarmacy ER_OF where ER_OF.OrgFarmacy_id = ER.OrgFarmacy_oid limit 1) as "OrgFarmacy_oName",
            (
                case when ER.ReceptDelayType_id  > 0
                then (select ReceptDelayType_Name from ReceptDelayType ER_RDT where ER_RDT.ReceptDelayType_id = ER.ReceptDelayType_id) || coalesce(' '||coalesce((select Org_Name from v_OrgFarmacy ER_OF where ER_OF.OrgFarmacy_id = ER.OrgFarmacy_oid), '') || case when ER.ReceptDelayType_id = 3 and Wr.ReceptWrong_Decr is not null then ' (' || Wr.ReceptWrong_Decr || ')' else '' end, '')
                else ''
                end
            ) as "Delay_info",
            to_char(ER.EvnRecept_setDate, 'DD.MM.YYYY') as "EvnRecept_setDate",
            to_char(COALESCE(ER.EvnRecept_otpDT,RecOt.EvnRecept_otpDate), 'DD.MM.YYYY') as "EvnRecept_otpDate",
            to_char(COALESCE(ER.EvnRecept_obrDT,RecOt.EvnRecept_obrDate), 'DD.MM.YYYY') as "EvnRecept_obrDate",
            case when ((ER.EvnRecept_otpDT is not null) and (ER.EvnRecept_obrDT is not null)) then date_part('day', ER.EvnRecept_otpDT - ER.EvnRecept_obrDT) else null end as "ServePeriod",
            RTRIM(wdcit.WhsDocumentCostItemType_Name) as "WhsDocumentCostItemType_Name",
            RTRIM(drugFin.DrugFinance_Name) as "DrugFinance_Name",
            coalesce(RecOtovSum.recSum,0) as "ReceptOtovSum",
            RTRIM(ER.EvnRecept_Ser) as "EvnRecept_Ser",
            RTRIM(ER.EvnRecept_Num) as "EvnRecept_Num",
            ROUND(ER.EvnRecept_Kolvo, 3) as "EvnRecept_Kolvo",
            (RTRIM(ERMP.Person_Fio) || ' (' || coalesce(Lpu.Lpu_Nick,'') || ')') as "MedPersonal_Fio",
            RTRIM(COALESCE(ERDrugRls.Drug_Name, ERDrug.Drug_Name, DCM.DrugComplexMnn_RusName, ER.EvnRecept_ExtempContents)) as "Drug_Name",
            RTRIM(DrugNomen.DrugNomen_Code) as "DrugNomen_Code",
            
                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",
            
            CASE WHEN (ER.EvnRecept_IsSigned = 2 or (ER.pmUser_signID is not null and ER.EvnRecept_signDT is not null)
            ) THEN 'true' ELSE 'false' END as "EvnRecept_IsSigned",
            CASE WHEN ER.EvnRecept_IsPrinted = 2 THEN 'true' ELSE 'false' END as "EvnRecept_IsPrinted",
            RecF.ReceptForm_id as "ReceptForm_id",
            RecF.ReceptForm_Code as "ReceptForm_Code",
            RecT.ReceptType_Code as "ReceptType_Code",
            RecT.ReceptType_Name as "ReceptType_Name",
            CASE WHEN ER.EvnRecept_isImport = 2 THEN 'true' ELSE 'false' END as "EvnRecept_isImport",
            CASE WHEN ER_RDT.ReceptDelayType_Code = 4 THEN 'true' ELSE 'false' END as "Recept_MarkDeleted",
            ER.ReceptRemoveCauseType_id as "ReceptRemoveCauseType_id",
            pmUC.PMUser_Name as "PMUser_Name",
            coalesce(wdcit.MorbusType_id, 1) as "MorbusType_id",
            DocUc.DocumentUc_id as "DocumentUc_id",
            case
                when ER.ReceptDelayType_id IS Not null then null -- Рецепт имеет статус
                when RV.ReceptValid_Code = 1 then to_char(dateadd('month', 1, ER.EvnRecept_setDate), 'DD.MM.YYYY') -- ReceptValid_Name = 'Месяц'
                when RV.ReceptValid_Code = 2 then to_char(dateadd('month', 3, ER.EvnRecept_setDate), 'DD.MM.YYYY') -- ReceptValid_Name = 'Три месяца'
                when RV.ReceptValid_Code = 3 then to_char(dateadd('day', 14, ER.EvnRecept_setDate), 'DD.MM.YYYY') -- ReceptValid_Name = '14 дней'
                when RV.ReceptValid_Code = 4 then to_char(dateadd('day', 5, ER.EvnRecept_setDate), 'DD.MM.YYYY') -- when ReceptValid_Name = '5 дней'
                when RV.ReceptValid_Code = 5 then to_char(dateadd('month', 2, ER.EvnRecept_setDate), 'DD.MM.YYYY') -- ReceptValid_Name = 'Два месяца'
                when RV.ReceptValid_Code = 7 then to_char(dateadd('day', 10, ER.EvnRecept_setDate), 'DD.MM.YYYY') -- ReceptValid_Name = '10 дней'
                when RV.ReceptValid_Code = 8 then to_char(dateadd('day', 60, ER.EvnRecept_setDate), 'DD.MM.YYYY') -- ReceptValid_Name = '60 дней'
                when RV.ReceptValid_Code = 9 then to_char(dateadd('day', 30, ER.EvnRecept_setDate), 'DD.MM.YYYY') -- ReceptValid_Name = '30 дней'
                when RV.ReceptValid_Code = 10 then to_char(dateadd('day', 90, ER.EvnRecept_setDate), 'DD.MM.YYYY') -- ReceptValid_Name = '90 дней'
                when RV.ReceptValid_Code = 11 then to_char(dateadd('day', 15, ER.EvnRecept_setDate), 'DD.MM.YYYY') -- ReceptValid_Name = '15 дней'
                else
 to_char(ER.EvnRecept_setDate, 'DD.MM.YYYY')
            end as "EvnRecept_DateCtrl",
            case
                when ER.ReceptDelayType_id IS Not null then 0 -- Рецепт имеет статус
                when RV.ReceptValid_Code = 1 and dateadd('month', 1, ER.EvnRecept_setDate) < localtimestamp then 1 -- ReceptValid_Name = 'Месяц'
                when RV.ReceptValid_Code = 2 and dateadd('month', 3, ER.EvnRecept_setDate) < localtimestamp then 1 -- ReceptValid_Name = 'Три месяца'
                when RV.ReceptValid_Code = 3 and dateadd('day', 14, ER.EvnRecept_setDate) < localtimestamp then 1 -- ReceptValid_Name = '14 дней'
                when RV.ReceptValid_Code = 4 and dateadd('day', 5, ER.EvnRecept_setDate)  < localtimestamp then 1 -- when ReceptValid_Name = '5 дней'
                when RV.ReceptValid_Code = 5 and dateadd('month', 2, ER.EvnRecept_setDate)  < localtimestamp then 1 -- ReceptValid_Name = 'Два месяца'
                when RV.ReceptValid_Code = 7 and dateadd('day', 10, ER.EvnRecept_setDate)  < localtimestamp then 1 -- ReceptValid_Name = '10 дней'
                when RV.ReceptValid_Code = 8 and dateadd('day', 60, ER.EvnRecept_setDate)  < localtimestamp then 1 -- ReceptValid_Name = '60 дней'
                when RV.ReceptValid_Code = 9 and dateadd('day', 30, ER.EvnRecept_setDate)  < localtimestamp then 1 -- ReceptValid_Name = '30 дней'
                when RV.ReceptValid_Code = 10 and dateadd('day', 90, ER.EvnRecept_setDate)  < localtimestamp then 1 -- ReceptValid_Name = '90 дней'
                when RV.ReceptValid_Code = 11 and dateadd('day', 15, ER.EvnRecept_setDate)  < localtimestamp then 1 -- ReceptValid_Name = '15 дней'
                else 0
            end as "EvnRecept_Shelf",
            case when Wr.ReceptWrong_id is not null then 1 else 0 end as "EvnRecept_IsWrong",
            case when ((RV.ReceptValidType_id = 1 and ((ER.EvnRecept_setDate + RV.ReceptValid_Value * interval '1 day') >= (select dt from mv))) or (RV.ReceptValidType_id = 2 and ((ER.EvnRecept_setDate + (RV.ReceptValid_Value * 30) * interval '1 day') >= (select dt from mv))))
                then 0
                else 1
            end as "inValidRecept"
        
                ,RTRIM(PS.Person_SurName) as "Person_Surname"
                ,RTRIM(PS.Person_FirName) as "Person_Firname"
                ,RTRIM(PS.Person_SecName) as "Person_Secname"
                ,to_char(PS.Person_Birthday, 'DD.MM.YYYY') as "Person_Birthday"
                ,to_char(PS.Person_deadDT, 'DD.MM.YYYY') as "Person_deadDT"
            
            
                FROM
                
         dbo.v_PersonState PS  inner join v_EvnRecept ER on ER.Person_id = PS.Person_id
            left join v_Lpu lpu on lpu.Lpu_id = er.lpu_id
            left join dbo.v_ReceptValid RV on RV.ReceptValid_id = ER.ReceptValid_id
        
            left join v_ReceptForm RecF on RecF.ReceptForm_id = ER.ReceptForm_id
            left join v_ReceptType RecT on RecT.ReceptType_id = ER.ReceptType_id
            left join v_Drug ERDrug on ERDrug.Drug_id = ER.Drug_id
            left join v_Polis pls on pls.Polis_id = ps.Polis_id
            left join rls.v_Drug ERDrugRls on ERDrugRls.Drug_id = ER.Drug_rlsid
            left join rls.v_DrugComplexMnn DCM on DCM.DrugComplexMnn_id = ER.DrugComplexMnn_id
            left join rls.v_DrugNomen DrugNomen on DrugNomen.Drug_id = ER.Drug_rlsid or DrugNomen.DrugNomen_Code = ERDrug.Drug_CodeG::varchar
            left join dbo.v_WhsDocumentCostItemType wdcit on wdcit.WhsDocumentCostItemType_id = ER.WhsDocumentCostItemType_id
            left join dbo.v_DrugFinance drugFin on drugFin.DrugFinance_id = ER.DrugFinance_id
            left join v_ReceptDelayType ER_RDT on ER_RDT.ReceptDelayType_id = ER.ReceptDelayType_id
            left join v_pmUserCache pmUC on pmUC.PMUser_id = ER.pmUser_updID
            left join v_DocumentUcStr DUS on DUS.EvnRecept_id = ER.EvnRecept_id
            left join v_pmUserCache puc2 on puc2.pmUser_id = ER.pmUser_signotvID
            left join lateral(
                select
                    RecOt_t.ReceptOtov_id,
                    RecOt_t.EvnRecept_otpDate,
                    RecOt_t.EvnRecept_obrDate
                from ReceptOtov RecOt_t
                where RecOt_t.EvnRecept_id = ER.EvnRecept_id
                order by RecOt_t.ReceptOtov_insDT desc
                limit 1
            ) as RecOt on true
            -- добавляем ФИО и должность сотрудника, который обеспечил рецепт
            left join lateral(
                select
                    PS.Person_SurName,
                    PS.Person_FirName,
                    PS.Person_SecName,
                    PT.Post_Name
                from ReceptOtov RecOt_p
                join v_PersonWork PW on PW.PersonWork_id = RecOt_p.PersonWork_id
                left join v_PersonState PS on PS.Person_id = PW.Person_id
                left join v_Post PT on PT.Post_id = PW.Post_id
                where RecOt_p.EvnRecept_id = ER.EvnRecept_id
                order by RecOt_p.ReceptOtov_insDT desc
                limit 1
            ) as RecOtP on true
            left join lateral(
                select Person_Fio
                from v_MedPersonal
                where MedPersonal_id = ER.MedPersonal_id
                limit 1
            ) as ERMP on true
            left join lateral(
                select sum((ROt.EvnRecept_Price * ROt.EvnRecept_Kolvo)) as recSum
                from ReceptOtov ROt
                where ROt.EvnRecept_id = ER.EvnRecept_id
            ) as RecOtovSum on true
            left join lateral(
                select dus.DocumentUc_id
                from
                    ReceptOtov ROtov
                    left join DocumentUcStr dus on dus.ReceptOtov_id = ROtov.ReceptOtov_id
                where ROtov.EvnRecept_id = ER.EvnRecept_id
                limit 1
            ) as DocUc on true
        
            left join lateral(
                select
                    Wr_t.ReceptWrong_id,
                    Wr_t.ReceptWrong_Decr
                from ReceptWrong Wr_t
                where Wr_t.EvnRecept_id = ER.EvnRecept_id
                order by Wr_t.ReceptWrong_updDT desc
                limit 1
            ) as Wr on true
        
                    left join v_DrugMnn DM on DM.DrugMnn_id = ERDrug.DrugMnn_id
                    left join lateral (
                        select _DMC.ACTMATTERS_ID
                        from rls.v_DrugMnnCode _DMC
                        where _DMC.DrugMnnCode_Code = DM.DrugMnn_Code::varchar
                        order by _DMC.DrugMnnCode_id
                        limit 1
                    ) as DMC on true
            
                    left join rls.v_DrugComplexMnnName DCMN on DCMN.DrugComplexMnnName_id = DCM.DrugComplexMnnName_id
            
                         WHERE 
                        (1 = 1) AND ( DMC.ACTMATTERS_ID = '36' OR DCMN.ACTMATTERS_ID = '36' ) and coalesce(ER.EvnRecept_Is7Noz, 1) = '1'
                        
                
                        ORDER BY
                            
                        PS.Person_SurName,
                        PS.Person_FirName,
                        PS.Person_SecName,
                        ER.EvnRecept_Num
                        
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
