    select
        emdr."EMDRegistry_id",
        case
            when emdr."EMDRegistry_ObjectName" like 'proxy%' then 'ProxyOuterEMD'
            else emdr."EMDRegistry_ObjectName"
        end as "EMDRegistry_ObjectName",
        case
            when emdr."EMDRegistry_ObjectName" like 'proxy%' then emdr."EMDRegistry_id"
            else emdr."EMDRegistry_ObjectID"
        end as "EMDRegistry_ObjectID",
        case
            when dtl."EMDDocumentTypeLocal_OID" in ('161', '162', '165') and now() <= '2023-06-30'
                then concat(dtl."EMDDocumentTypeLocal_Name", '<br>Дата начала регистрации СЭМД в РЭМД ЕГИСЗ - "30.06.2023"')
            else dtl."EMDDocumentTypeLocal_Name"
        end as "EMDDocumentTypeLocal_Name",
        case
            when emdr."EMDRegistry_ObjectName" like 'proxy%'
                then cast(emdr."EMDRegistry_ObjectID" as varchar)
            else emdr."EMDRegistry_Num"
        end as "EMDRegistry_Num",
        to_char(emdr."EMDRegistry_EMDDate", 'DD.MM.YYYY') as "EMDRegistry_EMDDate",
        to_char(version."EMDVersion_insDT", 'DD.MM.YYYY') as "EMDVersion_insDT",
        emdr."Person_id",
        emdr."pmUser_insID",
        case
            when COALESCE(version."EMDVersion_IsReady", 0) > 1 then 'Да'
            else 'Нет'
        end as shipmentstatus,
        -- уже подписано МО или нет
        case when dtl."EMDDocumentTypeLocal_IsNeedSignatures" = 1 and sign."MedStaffFact_id" is not null
            then 1 else 0
        end as "isLpuSignNeeded",
        -- ожидается подпись МО
        dtl."EMDDocumentTypeLocal_IsNeedSignatures" as "IsNeedSignatures",
        MPSign."PMUser_Name" as "MedPersonal_Fio",
        sign."MedStaffFact_id",
        almp.cnt as "MinSignCount",
        emds.cnt as "SignCount",
        version."EMDVersion_id",
        emdvs."EMDVersionStatus_Code",
        dtl."EMDDocumentTypeLocal_OID",
        --  современная возможность получения кол-ва записей без учета LIMIT
        count(*) OVER() AS "totalCount"
    from
        "EMD"."EMDRegistry" emdr
        inner join 	(
            select distinct
                "EMDRegistry_ObjectName",
                "EMDRegistry_ObjectID",
                first_value("EMDRegistry_id") over(partition by "EMDRegistry_ObjectName", "EMDRegistry_ObjectID" order by "EMDRegistry_insDT" desc) as "EMDRegistry_id"
            from "EMD"."EMDRegistry" emdrz where  1=1  and ((emdrz."EMDRegistry_deleted" = 1 or emdrz."EMDRegistry_deleted" is null) and
        (emdrz."EMDRegistry_isSpoiled" = 1 or emdrz."EMDRegistry_isSpoiled" is null))
                and emdrz."EMDRegistry_EMDDate" >= '2023-01-01 00:00:00'
                and emdrz."EMDRegistry_EMDDate" <= '2023-06-01 23:59:59'
             and
                 "EMDRegistry_ObjectID" = emdrz."EMDRegistry_ObjectID"
                and "EMDRegistry_ObjectName" = emdrz."EMDRegistry_ObjectName"
        ) z on emdr."EMDRegistry_id" = z."EMDRegistry_id"
        inner join "EMD"."EMDDocumentTypeLocal" dtl on dtl."EMDDocumentTypeLocal_id" = emdr."EMDDocumentTypeLocal_id"
            and dtl."EMDDocumentTypeLocal_IsREMD" = 2 and dtl."EMDDocumentType_id" is not null
        inner join "EMD"."EMDDocumentType" dt on dt."EMDDocumentType_id" = dtl."EMDDocumentType_id"
        left join lateral (
            SELECT emdr."EMDRegistry_id",
                   emdv."EMDVersion_id",
                   emdv."EMDVersion_IsReady",
                   emdv."EMDVersion_RegistrationDate",
                   emdv."EMDVersion_insDT"
            FROM "EMD"."EMDVersion" emdv
            WHERE emdr."EMDRegistry_id" = emdv."EMDRegistry_id"
                -- Без учета файлов в формате JSON
                AND emdv."EMDFileFormat_id" != 3
            ORDER BY emdv."EMDVersion_id" DESC NULLS LAST
            LIMIT 1) version on true
        left join "EMD"."EMDVersionStatus" emdvs on version."EMDVersion_IsReady" = emdvs."EMDVersionStatus_id"
        left join lateral(
            SELECT
                emdv."EMDVersion_id",
                emds."MedStaffFact_id",
                emds."Signatures_id",
                emdv."EMDVersion_insDT",
                emdv."EMDVersion_RegistrationDate"
            FROM "EMD"."EMDVersion" emdv
            left join "EMD"."EMDSignatures" emds on emds."EMDVersion_id" = emdv."EMDVersion_id"
            WHERE emdv."EMDRegistry_id" = emdr."EMDRegistry_id"
                AND emds."Signatures_id" is not null
            ORDER BY emdv."EMDVersion_id" desc, emds."EMDSignatures_insDT" desc
            LIMIT 1
        ) sign on true
        left join lateral(
            SELECT
                (emds."MedPersonal_SurName" || ' ' || emds."MedPersonal_FirName" || ' ' || emds."MedPersonal_SecName") as "PMUser_Name"
            FROM "EMD"."EMDVersion" emdv
            left join "EMD"."EMDSignatures" emds on emds."EMDVersion_id" = emdv."EMDVersion_id"
            WHERE emdv."EMDRegistry_id" = emdr."EMDRegistry_id"
                AND emds."Signatures_id" is not null
            ORDER BY emdv."EMDVersion_id" desc, emds."EMDSignatures_insDT" desc
            LIMIT 1
        ) MPSign on true
        -- кол-во требуемых и имеющихся подписей
        inner join lateral (
            select
                count("EMDSignatures_id") as cnt
            from
                "EMD"."EMDSignatures"
            where
                "EMDVersion_id" = version."EMDVersion_id"
        ) emds on true
        inner join lateral (
            select SUM (tmp."EMDSignatureRules_MinCount") as cnt from (
                select distinct
                    sr."EMDPersonRole_id",
                    sr."EMDSignatureRules_MinCount"
                from
                    "EMD"."ApprovalList" al
                    inner join "EMD"."ApprovalListMedPersonal" almp on almp."ApprovalList_id" = al."ApprovalList_id"
                    inner join "EMD"."EMDSignatureRules" sr on sr."EMDPersonRole_id" = almp."EMDPersonRole_id"
                        and dt."EMDDocumentType_id" = sr."EMDDocumentType_id"
                where
                    al."ApprovalList_ObjectName" = emdr."EMDRegistry_ObjectName"
                    and al."ApprovalList_ObjectId" = emdr."EMDRegistry_ObjectID"
            ) tmp
        ) almp on true
    where
        sign."Signatures_id" IS NOT null
         and ((emdr."EMDRegistry_deleted" = 1 or emdr."EMDRegistry_deleted" is null) and
        (emdr."EMDRegistry_isSpoiled" = 1 or emdr."EMDRegistry_isSpoiled" is null))
                and emdr."EMDRegistry_EMDDate" >= '2023-01-01 00:00:00'
                and emdr."EMDRegistry_EMDDate" <= '2023-06-01 23:59:59'
              and 1=1 
    LIMIT 100 OFFSET 0
