DROP PROCEDURE IF EXISTS sp_getsetrequestcatalog;

CREATE PROCEDURE sp_getsetrequestcatalog(
    "Xml" text DEFAULT NULL
, "IsNew" integer = 1
, "ID" uuid DEFAULT NULL::uuid
, "QueueName" character varying DEFAULT 'RDSSync'::character varying
, "dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
, "User" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    DECLARE
        _operationtype varchar(255);
    BEGIN
        IF "Xml" IS NOT NULL
        THEN
            -- Получаем тип операции
            _operationtype := (XPATH('/DRUG_APPLICATION_LIST/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);
            
            -- Generate table #DRUG_APPLICATION_LIST_ITEM
            DROP TABLE IF EXISTS temp_drug_application_list_item;
            CREATE TEMP TABLE temp_drug_application_list_item
            AS
                SELECT (XPATH('/DRUG_APPLICATION_LIST_ITEM/ID[1]/text()', x))[1]::text::uuid                       AS id
                     , TO_TIMESTAMP((XPATH('/DRUG_APPLICATION_LIST_ITEM/BeginDate[1]/text()', x))[1]::text,
                                    'DD.MM.YYYY')                                                                  AS begindate
                     , TO_TIMESTAMP((XPATH('/DRUG_APPLICATION_LIST_ITEM/EndDate[1]/text()', x))[1]::text,
                                    'DD.MM.YYYY')                                                                  AS enddate
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/Price[1]/text()', x))[1]::text::decimal(31, 10)         AS price
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/RequestCode[1]/text()', x))[1]::text::int               AS requestcode
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/CMnnID[1]/text()', x))[1]::text::int                    AS cmnnid
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/CTrnID[1]/text()', x))[1]::text::int                    AS ctrnid
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/GroupsOfDrugsClassification[1]/text()',
                              x))[1]::text::int                                                                    AS groupsofdrugsclassification
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/BenefitType[1]/text()', x))[1]::text::int               AS benefittype
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/DoseView[1]/text()', x))[1]::varchar(255)               AS doseview
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/MedicationForms[1]/text()', x))[1]::text::int           AS medicationforms
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/PackQuantity[1]/text()', x))[1]::text::int              AS packquantity
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/IsActive[1]/text()', x))[1]::text::boolean              AS isactive
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/ElectronicCertificate[1]/text()',
                              x))[1]::text::boolean                                                                AS electroniccertificate
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/SectionCode[1]/text()', x))[1]::varchar(255)            AS sectioncode
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/Comment[1]/text()', x))[1]::varchar(255)                AS comment
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/DoseCount[1]/text()', x))[1]::text::decimal(5, 2)       AS dosecount
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/ConsumerUnits[1]/text()', x))[1]::text::decimal(31, 10) AS consumerunits
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/Preparation[1]/text()', x))[1]::varchar(255)            AS preparation
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/PersRequest[1]/text()', x))[1]::text::boolean           AS persrequest
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/EhrRus9a9g09e8kl[1]/text()', x))[1]::varchar(52)        AS ehrrus9a9g09e8kl
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/EhrRus9a9g09e8klVer[1]/text()', x))[1]::varchar(8)      AS ehrrus9a9g09e8klver
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/EhrRus6kiqts8t99[1]/text()', x))[1]::text::int          AS ehrrus6kiqts8t99
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/EhrRus6kiqts8t99Ver[1]/text()', x))[1]::varchar(8)      AS ehrrus6kiqts8t99ver
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/EhrRusH17F8UTS3N[1]/text()', x))[1]::text::int          AS ehrrush17f8uts3n
                     , (XPATH('/DRUG_APPLICATION_LIST_ITEM/EhrRusH17F8UTS3NVer[1]/text()', x))[1]::varchar(8)      AS ehrrush17f8uts3nver
                FROM (SELECT UNNEST(XPATH(
                    '/DRUG_APPLICATION_LIST/body/DRUG_APPLICATION_LIST_ITEMS/DRUG_APPLICATION_LIST_ITEM', x)) AS x
                      FROM (SELECT "Xml"::xml AS x
                           ) root
                     ) drug_application_list_item;
            
            -- Загружаем новые DoseView
            INSERT INTO akuz.s_dlo_doseview(value)
            SELECT TRIM(dli.doseview)
            FROM temp_drug_application_list_item dli
            WHERE NOT EXISTS
                      (SELECT * FROM akuz.s_dlo_doseview dv WHERE TRIM(dv.value) = TRIM(dli.doseview)
                      )
            GROUP BY TRIM(dli.doseview);
            
            -- Обновляем имеющиеся BASE_DRUG_REQUEST_ITEM
            UPDATE akuz.t_base_drug_request_item bdri
            SET begindate        = dli.begindate
              , price            = dli.price
              , requestcode      = dli.requestcode
              , applistbegindate = dli.begindate
              , applistenddate   = dli.enddate
              , medicationforms  = mf.medicationformsid
              , persrequest      = COALESCE(dli.persrequest, '0')
            FROM temp_drug_application_list_item dli
            LEFT JOIN pharm.t_medication_forms   mf ON mf.code = dli.medicationforms AND mf.active = '1'
            WHERE dli.id = bdri.basedrugrequestitemid
              AND bdri.type_ = 1233;
            
            -- Обновляем имеющиеся REQUEST_CATALOG
            UPDATE akuz.t_request_catalog rc
            SET createdate                  = dli.begindate
              , doseview                    = dv.id
              , groupsofdrugsclassification = dli.groupsofdrugsclassification
              , cmnn                        = mnn.cmnnid
              , ctrn                        = trn.ctrnid
              , benefittype                 = ebt.enumid
              , packquantity                = dli.packquantity
              , isactive                    = COALESCE(dli.isactive, '1')
              , electroniccertificate       = COALESCE(dli.electroniccertificate, '0')
              , comment                     = dli.comment
              , sectioncode                 = dli.sectioncode
              , dosecount=dli.dosecount
              , ehrrus9a9g09e8kl            = ehr.ehrrusid
              , ehrrus6kiqts8t99            = ehr1.ehrrusid
              , ehrrush17f8uts3n            = ehr2.ehrrusid
              , consumerunits               = dli.consumerunits
            FROM temp_drug_application_list_item dli
            LEFT JOIN vclib.t_enums              ebt ON ebt.code = dli.benefittype AND ebt.enumtypeid = 242
            LEFT JOIN LATERAL
                          (
                          SELECT sd.id FROM akuz.s_dlo_doseview sd WHERE TRIM(sd.value) = TRIM(dli.doseview) LIMIT 1
                          )                      dv ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT m.cmnnid FROM pharm.t_c_mnn m WHERE m.otherid = dli.cmnnid AND m.active = '1' LIMIT 1
                          )                      mnn ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT t.ctrnid FROM pharm.t_c_trn t WHERE t.otherid = dli.ctrnid AND t.active = '1' LIMIT 1
                          )                      trn ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_9a9g09e8kl     er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.codeehr = dli.ehrrus9a9g09e8kl
                            AND rrb.currversion = dli.ehrrus9a9g09e8klver
                          LIMIT 1
                          )                      ehr ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_6kiqts8t99     er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = dli.ehrrus6kiqts8t99
                            AND rrb.currversion = dli.ehrrus6kiqts8t99ver
                          LIMIT 1
                          )                      ehr1 ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_h17f8uts3n     er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = dli.ehrrush17f8uts3n
                            AND rrb.currversion = dli.ehrrush17f8uts3nver
                          LIMIT 1
                          )                      ehr2 ON TRUE
            WHERE dli.id = rc.basedrugrequestitemid
              AND dv.id IS NOT NULL;
            
            -- Загружаем новые BASE_DRUG_REQUEST_ITEM
            INSERT INTO
                akuz.t_base_drug_request_item(basedrugrequestitemid,
                                              begindate,
                                              price,
                                              medicationforms,
                                              requestcode,
                                              applistbegindate,
                                              applistenddate,
                                              type_,
                                              persrequest)
            SELECT dli.id,
                   dli.begindate,
                   dli.price,
                   mf.medicationformsid,
                   dli.requestcode,
                   dli.begindate,
                   dli.enddate,
                   1233,
                   COALESCE(dli.persrequest, '0')
            FROM temp_drug_application_list_item dli
            LEFT JOIN pharm.t_medication_forms   mf ON mf.code = dli.medicationforms AND mf.active = '1'
            WHERE NOT EXISTS
                      (SELECT *
                       FROM akuz.t_base_drug_request_item bdri
                       WHERE bdri.basedrugrequestitemid = dli.id
                      );
            
            -- Загружаем новые REQUEST_CATALOG
            INSERT INTO
                akuz.t_request_catalog(basedrugrequestitemid,
                                       createdate,
                                       doseview,
                                       groupsofdrugsclassification,
                                       cmnn,
                                       ctrn,
                                       benefittype,
                                       packquantity,
                                       isactive,
                                       electroniccertificate,
                                       sectioncode,
                                       comment,
                                       dosecount,
                                       ehrrus9a9g09e8kl,
                                       ehrrus6kiqts8t99,
                                       ehrrush17f8uts3n,
                                       consumerunits)
            SELECT dli.id,
                   dli.begindate,
                   dv.id,
                   dli.groupsofdrugsclassification,
                   mnn.cmnnid,
                   trn.ctrnid,
                   ebt.enumid,
                   dli.packquantity,
                   COALESCE(dli.isactive, '1'),
                   COALESCE(dli.electroniccertificate, '0'),
                   dli.sectioncode,
                   dli.comment,
                   dli.dosecount,
                   ehr.ehrrusid,
                   ehr1.ehrrusid,
                   ehr2.ehrrusid,
                   dli.consumerunits
            FROM temp_drug_application_list_item dli
            LEFT JOIN vclib.t_enums              ebt ON ebt.code = dli.benefittype AND ebt.enumtypeid = 242
            LEFT JOIN LATERAL
                          (
                          SELECT sd.id FROM akuz.s_dlo_doseview sd WHERE TRIM(sd.value) = TRIM(dli.doseview) LIMIT 1
                          )                      dv ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT m.cmnnid FROM pharm.t_c_mnn m WHERE m.otherid = dli.cmnnid AND m.active = '1' LIMIT 1
                          )                      mnn ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT t.ctrnid FROM pharm.t_c_trn t WHERE t.otherid = dli.ctrnid AND t.active = '1' LIMIT 1
                          )                      trn ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_9a9g09e8kl     er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.codeehr = dli.ehrrus9a9g09e8kl
                            AND rrb.currversion = dli.ehrrus9a9g09e8klver
                          LIMIT 1
                          )                      ehr ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_6kiqts8t99     er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = dli.ehrrus6kiqts8t99
                            AND rrb.currversion = dli.ehrrus6kiqts8t99ver
                          LIMIT 1
                          )                      ehr1 ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_h17f8uts3n     er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = dli.ehrrush17f8uts3n
                            AND rrb.currversion = dli.ehrrush17f8uts3nver
                          LIMIT 1
                          )                      ehr2 ON TRUE
            WHERE NOT EXISTS
                (SELECT * FROM akuz.t_request_catalog rc WHERE rc.basedrugrequestitemid = dli.id
                )
              AND dv.id IS NOT NULL;
            
            IF _operationtype = 'FTLoad'
            THEN
                -- Закрываем REQUEST_CATALOG которых нет
                UPDATE akuz.t_request_catalog
                SET isactive = '0'
                WHERE isactive = '1'
                  AND NOT EXISTS
                    (SELECT * FROM temp_drug_application_list_item WHERE id = basedrugrequestitemid
                    );
                
                -- Закрываем BASE_DRUG_REQUEST_ITEM которых нет
                UPDATE akuz.t_base_drug_request_item
                SET applistenddate = NOW()::timestamp
                WHERE applistenddate > NOW()::timestamp
                  AND NOT EXISTS (SELECT * FROM temp_drug_application_list_item WHERE id = basedrugrequestitemid);
            END IF;
        
        END IF;
    END;
END;
$$;;
