DROP PROCEDURE IF EXISTS aps.sp_export7nosologyxml;

CREATE OR REPLACE PROCEDURE aps.sp_export7nosologyxml(
    "DateBegin" TIMESTAMP,
    "DateEnd" TIMESTAMP,
    OUT "Result" bytea
)
AS
$$
DECLARE
    v_xml       TEXT;
    p_datebegin TIMESTAMP := "DateBegin";
    p_dateend   TIMESTAMP := "DateEnd";


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 XMLAGG(
                                           XMLELEMENT(
                                               NAME "error",
                                               e.errortext || '. Серия и номер рецепта: ' || t.pres_ser_num)
                                       )
                                
                                FROM t_errors e
                                WHERE e.id = t.id
                               ) AS "Errors",
                               t.u_numb_regist_record_04 AS "u_numb_regist_record_04",
                               t.u_numb_regist_record AS "u_numb_regist_record",
                               t.gos_numb_okop_uz AS "gos_numb_okop_uz",
                               t.pres_doctor AS "pres_doctor",
                               t.pres_ser_num AS "pres_ser_num",
                               t.pres_date AS "pres_date",
                               t.okato AS "okato",
                               t.prepar_name AS "prepar_name",
                               t.mnnnamerus AS "MnnNameRus",
                               t.doza_count AS "doza_count",
                               t.terr_code AS "terr_code",
                               t.pharmacy_reg_no AS "pharmacy_reg_no",
                               t.pharmacy_date_out1 AS "pharmacy_date_out1",
                               t.lekarstv_name AS "lekarstv_name",
                               t.trnnamerus AS "TrnNameRus",
                               t.mkb_code AS "mkb_code",
                               t.mkb AS "Mkb",
                               t.lekarstv_forma AS "lekarstv_forma",
                               t.lekarstv_doza_pres AS "lekarstv_doza_pres",
                               t.lekarstv_doza_pack AS "lekarstv_doza_pack",
                               t.lekarstv_packs_out AS "lekarstv_packs_out",
                               t.operation_code AS "operation_code",
                               t.head_person AS "head_person",
                               t.organ_ispoln_vlast_sf AS "organ_ispoln_vlast_sf",
                               t.id_istochnik_finans AS "id_istochnik_finans",
                               t.fcomment AS "fcomment"
                           )
                       )
                   )
               )::TEXT
        INTO v_xml
        FROM tempdb t;
    
    ELSE
        SELECT XMLELEMENT(
                   NAME "row",
                   XMLAGG(
                       XMLFOREST(
                           t.u_numb_regist_record_04 AS "u_numb_regist_record_04",
                           t.u_numb_regist_record AS "u_numb_regist_record",
                           t.gos_numb_okop_uz AS "gos_numb_okop_uz",
                           t.pres_doctor AS "pres_doctor",
                           t.pres_ser_num AS "pres_ser_num",
                           t.okato AS "okato",
                           t.prepar_name AS "prepar_name",
                           t.doza_count AS "doza_count",
                           t.terr_code AS "terr_code",
                           t.pharmacy_reg_no AS "pharmacy_reg_no",
                           t.pharmacy_date_out1 AS "pharmacy_date_out1",
                           t.lekarstv_name AS "lekarstv_name",
                           t.mkb_code AS "mkb_code",
                           t.lekarstv_forma AS "lekarstv_forma",
                           t.lekarstv_doza_pres AS "lekarstv_doza_pres",
                           t.lekarstv_doza_pack AS "lekarstv_doza_pack",
                           t.lekarstv_packs_out AS "lekarstv_packs_out",
                           t.operation_code AS "operation_code",
                           t.head_person AS "head_person",
                           t.organ_ispoln_vlast_sf AS "organ_ispoln_vlast_sf",
                           t.id_istochnik_finans AS "id_istochnik_finans",
                           t.fcomment AS "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;', '>');
    
    "Result" = v_xml::bytea;
END;
$$
    LANGUAGE plpgsql;