CREATE OR REPLACE PROCEDURE APS.sp_Export7NosologyXml(
    p_DateBegin TIMESTAMP,
    p_DateEnd TIMESTAMP,
    OUT p_Result bytea
)
AS
$$
DECLARE
    v_Xml TEXT;

BEGIN

    CREATE TEMPORARY TABLE t_Errors
    (
        ID        UUID,
        ErrorText VARCHAR(256),
        Name      VARCHAR(256)
    );

    CREATE TEMPORARY TABLE tempdb AS
    SELECT
        gen_random_uuid()                               AS Id,
        nslg.UniqueNumber                               AS u_numb_regist_record_04,
        nslg.Snils                                      AS Snils,
        NULL                                            AS u_numb_regist_record,
        lm.OGRN                                         AS gos_numb_okop_uz,
        rec.PostCode                                    AS pres_doctor,
        (rec.Serial || ' ' || rec.Number::VARCHAR(100)) AS pres_ser_num,
        TO_CHAR(rec.RecipeDate, 'DD.MM.YYYY')           AS pres_date,
        '734010000'                                     AS okato,
        mnn.ExportCode                                  AS prepar_name,
        mnn.NameRus                                     AS MnnNameRus,
        rec.UnitsCount::INTEGER                         AS doza_count,
        LEFT(lm.TF_OKATO, 2)                            AS terr_code,
        ph.Code                                         AS pharmacy_reg_no,
        TO_CHAR(rec.IssueDate, 'DD.MM.YYYY')            AS pharmacy_date_out1,
        bg.ExportCode                                   AS lekarstv_name,
        trn.NameRUs                                     AS TrnNameRus,
        rec.Mkb                                         AS Mkb,
        me.ExportCode                                   AS mkb_code,
        mf.RegCode                                      AS lekarstv_forma,
        bg.Active                                       AS lekarstv_doza_pres,
        rec.Dose                                        AS lekarstv_doza_pack,
        rec.UnitsCount::INTEGER                         AS lekarstv_packs_out,
        '2'::TEXT                                       AS operation_code,
        nslg.Head                                       AS head_person,
        nslg.ExecutiveAuthority                         AS organ_ispoln_vlast_sf,
        '1'                                             AS id_istochnik_finans,
        nslg.Comment                                    AS fcomment
    FROM APS.T_RECIPE rec
    JOIN APS.T_NOSOLOGY nslg ON nslg.Snils = rec.Snils
    JOIN APS.T_BENEFITS b ON b.BenefitsID = rec.Benefits
        AND b.BenefitsID = nslg.Benefits
    LEFT JOIN APS.T_LPU_MAIN lm ON lm.MCOD = rec.LpuMain
    JOIN PHARM.T_C_MNN mnn ON mnn.CMnnID = rec.CMnn
    JOIN APS.T_RECIPE_INVOICE ri ON ri.Recipe = rec.RecipeID
    JOIN STOR.T_STORAGE_INVOICE si ON si.InvoiceID = ri.InvoiceID
    JOIN STOR.T_INVOICE_ITEM ii ON ii.StorageInvoice = si.InvoiceID
    JOIN PHARM.T_BILL_OF_GOODS bg ON bg.BillOfGoodsID = ii.BillOfGoods
    JOIN PHARM.T_MEDICATION_FORMS MF ON mf.MedicationFormsID = bg.MedicationForms
    JOIN PHARM.T_C_TRN trn ON trn.CTrnID = bg.CTrn
    JOIN APS.T_MKB_EXPORT me ON me.MkbCode = rec.Mkb
    LEFT JOIN APS.T_PHARMACY ph ON ph.Code = rec.Pharmacy
    WHERE
          rec.Status = 1
      AND b.benefitViews = 19006
      AND rec.IssueDate >= p_DateBegin
      AND rec.IssueDate <= p_DateEnd;

    INSERT INTO t_Errors(Id, ErrorText)
    SELECT
        t.Id,
        'Не указано обязательное поле (u_numb_regist_record_04) - уникальный номер в записях регистра по 12 нозологиям. СНИЛС пациента в регистре - ' ||
        t.Snils
    FROM tempdb t
    WHERE
        t.u_numb_regist_record_04 IS NULL;

    INSERT INTO t_Errors(Id, ErrorText)
    SELECT
        t.Id,
        'Не указано обязательное поле (gos_numb_okop_uz) - основной государственный регистрационный номер (по ОКПО) учреждения здравоохранения, выдавшего рецепт на лекарственное средство'
    FROM tempdb t
    WHERE
        t.gos_numb_okop_uz IS NULL;

    INSERT INTO t_Errors(Id, ErrorText)
    SELECT
        t.Id,
        'Не указано обязательное поле (pres_doctor) - серия и номер рецепта'
    FROM tempdb t
    WHERE
        t.pres_doctor IS NULL;

    INSERT INTO t_Errors(Id, ErrorText)
    SELECT
        t.Id,
        'Не указано обязательное поле (pres_ser_num) - идентификационный номер врача, выписавшего рецепт'
    FROM tempdb t
    WHERE
        t.pres_ser_num IS NULL;

    INSERT INTO t_Errors(Id, ErrorText)
    SELECT
        t.Id,
        'Не указано обязательное поле (pres_date) - дата выписки рецепта'
    FROM tempdb t
    WHERE
        t.pres_date IS NULL;

    INSERT INTO t_Errors(Id, ErrorText)
    SELECT
        t.Id,
        'Не указано обязательное поле (doza_count) - выписананое количество доз лекарственного средства'
    FROM tempdb t
    WHERE
        t.doza_count IS NULL;

    INSERT INTO t_Errors(Id, ErrorText)
    SELECT
        t.Id,
        'Не указано обязательное поле (terr_code) - код территории отпуска лекарственного средства по Общероссийскому классификатору административно-территориальных образований'
    FROM tempdb t
    WHERE
        t.terr_code IS NULL;

    INSERT INTO t_Errors(Id, ErrorText)
    SELECT
        t.Id,
        'Не указано обязательное поле (organ_ispoln_vlast_sf) - орган исполнительной власти субъекта Российской Федерации (ФМБА России)'
    FROM tempdb t
    WHERE
        t.organ_ispoln_vlast_sf IS NULL;

    INSERT INTO t_Errors(Id, ErrorText)
    SELECT
        t.Id,
        'Не указано обязательное поле (MnnNameRus) - код мнн выписанного лс'
    FROM tempdb t
    WHERE
        t.MnnNameRus IS NULL;

    INSERT INTO t_Errors(Id, ErrorText)
    SELECT
        t.Id,
        'Не указано обязательное поле (MnnNameRus) - код мнн выписанного лс'
    FROM tempdb t
    WHERE
        t.MnnNameRus IS NULL;

    INSERT INTO t_Errors(ErrorText, name)
    SELECT
        'Не найдено соответсвие (prepar_name) - код мнн выписанного лс и ExportID: ' || t.MnnNameRus,
        t.MnnNameRus
    FROM tempdb t
    WHERE
        t.MnnNameRus IS NULL;

    UPDATE t_Errors e
    SET
        Id = tt.Id
    FROM t_Errors e2
    CROSS JOIN LATERAL (SELECT * FROM tempdb t WHERE t.MnnNameRus = e2.Name LIMIT 1) AS tt
    WHERE
        e2.Id = e.Id;

    INSERT INTO t_Errors(ErrorText, name)
    SELECT
        'Не найдено соответсвие (lekarstv_name) - трн отпущенного лс и ExportID : ' || t.TrnNameRus,
        t.TrnNameRus
    FROM tempdb t
    WHERE
        t.lekarstv_name IS NULL
    GROUP BY t.TrnNameRus;

    UPDATE t_Errors e
    SET
        ID = tt.ID
    FROM t_Errors e2
    CROSS JOIN LATERAL (SELECT * FROM tempdb t WHERE t.TrnNameRus = e2.Name LIMIT 1) AS tt
    WHERE
        e2.Id = e.Id;

    INSERT INTO t_Errors (ID, ErrorText)
    SELECT
        t.ID,
        'Не указано обязательное поле (mkb_code) - код заболевания по MKB-10: ' || t.Mkb
    FROM tempdb t
    WHERE
        t.Mkb IS NULL;

    INSERT INTO t_Errors (ErrorText, name)
    SELECT
        'Не найдено соответсвие в таблице MKB_EXPORT кода МКБ и ExportID: ' || t.Mkb,
        t.Mkb
    FROM tempdb t
    WHERE
        t.mkb_code IS NULL
    GROUP BY T.Mkb;

    UPDATE t_Errors e
    SET
        ID = tt.ID
    FROM t_Errors e2
    CROSS JOIN LATERAL (SELECT * FROM tempdb t WHERE t.Mkb = e.Name LIMIT 1) AS tt
    WHERE
        e2.Id = e.Id;


    INSERT INTO t_errors (ErrorText, name)
    SELECT
        'Не найдено соответсвие (lekarstv_forma) - форма выпуска лс и FormExportCode : ' || t.TrnNameRus,
        t.TrnNameRus
    FROM tempdb t
    WHERE
        t.lekarstv_forma IS NULL
    GROUP BY t.TrnNameRus;

    UPDATE t_Errors e
    SET
        ID = tt.ID
    FROM t_Errors e2
    CROSS JOIN LATERAL (SELECT * FROM tempdb t WHERE t.TrnNameRus = e2.Name LIMIT 1) AS tt
    WHERE
        e2.Id = e.Id;


    IF ((SELECT COUNT(*) FROM t_Errors) > 0) THEN

        SELECT
            XMLELEMENT(
                NAME "errorList",
                XMLAGG(
                    XMLELEMENT(
                        NAME "row",
                        XMLFOREST(
                            (SELECT
                                 XMLELEMENT(
                                     NAME "Errors",
                                     XMLAGG(
                                         XMLELEMENT(
                                             NAME "error",
                                             e.errorText || '. Серия и номер рецепта: ' || t.pres_ser_num)
                                     )
                                 )
                             FROM t_Errors e
                             WHERE
                                 e.ID = t.ID),
                            XMLELEMENT(NAME "u_numb_regist_record_04", t.u_numb_regist_record_04),
                            XMLELEMENT(NAME "u_numb_regist_record", t.u_numb_regist_record),
                            XMLELEMENT(NAME "gos_numb_okop_uz", t.gos_numb_okop_uz),
                            XMLELEMENT(NAME "pres_doctor", t.pres_doctor),
                            XMLELEMENT(NAME "pres_ser_num", t.pres_ser_num),
                            XMLELEMENT(NAME "pres_date", t.pres_date),
                            XMLELEMENT(NAME "okato", t.okato),
                            XMLELEMENT(NAME "prepar_name", t.prepar_name),
                            XMLELEMENT(NAME "MnnNameRus", t.MnnNameRus),
                            XMLELEMENT(NAME "doza_count", t.doza_count),
                            XMLELEMENT(NAME "terr_code", t.terr_code),
                            XMLELEMENT(NAME "pharmacy_reg_no", t.pharmacy_reg_no),
                            XMLELEMENT(NAME "pharmacy_date_out1", t.pharmacy_date_out1),
                            XMLELEMENT(NAME "lekarstv_name", t.lekarstv_name),
                            XMLELEMENT(NAME "TrnNameRus", t.TrnNameRus),
                            XMLELEMENT(NAME "mkb_code", t.mkb_code),
                            XMLELEMENT(NAME "Mkb", t.Mkb),
                            XMLELEMENT(NAME "lekarstv_forma", t.lekarstv_forma),
                            XMLELEMENT(NAME "lekarstv_doza_pres", t.lekarstv_doza_pres),
                            XMLELEMENT(NAME "lekarstv_doza_pack", t.lekarstv_doza_pack),
                            XMLELEMENT(NAME "lekarstv_packs_out", t.lekarstv_packs_out),
                            XMLELEMENT(NAME "operation_code", t.operation_code),
                            XMLELEMENT(NAME "head_person", t.head_person),
                            XMLELEMENT(NAME "organ_ispoln_vlast_sf", t.organ_ispoln_vlast_sf),
                            XMLELEMENT(NAME "id_istochnik_finans", t.id_istochnik_finans),
                            XMLELEMENT(NAME "fcomment", t.fcomment)
                        )
                    )
                )
            )::TEXT
        INTO v_Xml
        FROM tempdb t;

    ELSE
        SELECT
            XMLELEMENT(
                NAME "row",
                XMLAGG(
                    XMLELEMENT(NAME "u_numb_regist_record_04", t.u_numb_regist_record_04),
                    XMLELEMENT(NAME "u_numb_regist_record", t.u_numb_regist_record),
                    XMLELEMENT(NAME "gos_numb_okop_uz", t.gos_numb_okop_uz),
                    XMLELEMENT(NAME "pres_doctor", t.pres_doctor),
                    XMLELEMENT(NAME "pres_ser_num", t.pres_ser_num),
                    XMLELEMENT(NAME "okato", t.okato),
                    XMLELEMENT(NAME "prepar_name", t.prepar_name),
                    XMLELEMENT(NAME "doza_count", t.doza_count),
                    XMLELEMENT(NAME "terr_code", t.terr_code),
                    XMLELEMENT(NAME "pharmacy_reg_no", t.pharmacy_reg_no),
                    XMLELEMENT(NAME "pharmacy_date_out1", t.pharmacy_date_out1),
                    XMLELEMENT(NAME "lekarstv_name", t.lekarstv_name),
                    XMLELEMENT(NAME "mkb_code", t.mkb_code),
                    XMLELEMENT(NAME "lekarstv_forma", t.lekarstv_forma),
                    XMLELEMENT(NAME "lekarstv_doza_pres", t.lekarstv_doza_pres),
                    XMLELEMENT(NAME "lekarstv_doza_pack", t.lekarstv_doza_pack),
                    XMLELEMENT(NAME "lekarstv_packs_out", t.lekarstv_packs_out),
                    XMLELEMENT(NAME "operation_code", t.operation_code),
                    XMLELEMENT(NAME "head_person", t.head_person),
                    XMLELEMENT(NAME "organ_ispoln_vlast_sf", t.organ_ispoln_vlast_sf),
                    XMLELEMENT(NAME "id_istochnik_finans", t.id_istochnik_finans),
                    XMLELEMENT(NAME "fcomment", t.fcomment)
                )
            )::TEXT
        INTO
            v_Xml
        FROM tempdb t;

    END IF;

    v_Xml = '<?xml version="1.0"?>' || v_Xml;
    v_Xml = REPLACE(REPLACE(REPLACE(v_Xml, '&lt;', '<'), '&lt;/', '</'), '&gt;', '>');
    DROP TABLE tempdb;

    p_Result = v_xml::bytea;

    SELECT p_result;
END;
$$
    LANGUAGE plpgsql;