DROP PROCEDURE IF EXISTS public.sp_getsetdrugapplicationlist;

CREATE OR REPLACE PROCEDURE public.sp_getsetdrugapplicationlist(
    "Xml" text DEFAULT NULL,
    "IsNew" boolean DEFAULT TRUE,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT NULL,
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml             xml          := "Xml" :: xml;
    p_isnew           boolean      := "IsNew";
    p_id              uuid         := "ID";
    p_queuename       varchar(100) := "QueueName";
    p_datetime        timestamp    := COALESCE("dateTime", CURRENT_TIMESTAMP);
    p_user            uuid         := "User";
    v_result          text;
    v_contentencoding varchar(255) = 'utf-8';
    v_type            varchar(255) := 'DRUG_APPLICATION_LIST';
    v_operationtype   varchar(6);
    v_ownerid         uuid         := p_id;
    c_insert          varchar(6)   := 'Insert';
    c_update          VARCHAR(6)   := 'Update';
    c_delete          VARCHAR(6)   := 'Delete';
    c_ftload          VARCHAR(6)   := 'FTLoad';
BEGIN
    
    IF (p_xml IS NULL) THEN
        
        v_operationtype := CASE WHEN p_id IS NULL THEN c_ftload ELSE c_update END;
        
        SELECT XMLELEMENT
               (
                   NAME "DRUG_APPLICATION_LIST",
                   XMLELEMENT
                   (
                       NAME "header",
                       XMLFOREST
                       (
                           v_operationtype AS "TYPE"
                       )
                   ),
                   XMLELEMENT
                   (
                       NAME "body",
                       XMLELEMENT
                       (
                           NAME "DRUG_APPLICATION_LIST_ITEMS",
                           (SELECT XMLELEMENT
                                   (
                                       NAME "DRUG_APPLICATION_LIST_ITEM",
                                       XMLFOREST
                                       (
                                           dali.drugapplicationlistitemid AS "ID",
                                           TO_CHAR(dal.begindate, 'DD.MM.YYYY') AS "BeginDate",
                                           TO_CHAR(COALESCE(dali.enddate, dal.enddate), 'DD.MM.YYYY') AS "EndDate",
                                           dali.price AS "Price",
                                           dali.requestcode AS "RequestCode",
                                           mnn.otherid AS "CMnnID",
                                           trn.otherid AS "CTrnID",
                                           dali.groupsofdrugsclassification AS "GroupsOfDrugsClassification",
                                           ebt.code AS "BenefitType",
                                           dv.value AS "DoseView",
                                           mf.code AS "MedicationForms",
                                           dali.packquantity AS "PackQuantity",
                                           dal.isactive AS "IsAvctive",
                                           dali.electroniccertificate AS "ElectronicCertificate",
                                           dali.sectioncode AS "SectionCode",
                                           dali.comment AS "Comment",
                                           dali.persrequest AS "PersRequest",
                                           dali.dosecount AS "DoseCount",
                                           dali.consumerunits AS "ConsumerUnits",
                                           er.codeehr AS "EhrRus9a9g09e8kl",
                                           rrb.currversion AS "EhrRus9a9g09e8klVer",
                                           er1.idehr AS "EhrRus6kiqts8t99",
                                           rrb1.currversion AS "EhrRus6kiqts8t99Ver",
                                           er2.idehr AS "EhrRusH17F8UTS3N",
                                           rrb2.currversion AS "EhrRusH17F8UTS3NVer"
                                       )
                                   )
                            FROM rds.t_drug_application_list_item dali
                            JOIN rds.t_drug_application_list dal ON dal.drugapplicationlistid = dali.drugapplicationlist
                            JOIN rds.s_dlo_doseview dv ON dv.id = dali.doseview
                            LEFT JOIN pharm.t_c_mnn mnn ON mnn.cmnnid = dali.cmnn
                            LEFT JOIN pharm.t_c_trn trn ON trn.ctrnid = dali.ctrn
                            JOIN vclib.t_enums ebt ON ebt.enumid = dali.benefittype
                            LEFT JOIN ehr.t_ehr_rus_9a9g09e8kl er ON er.ehrrusid = dali.ehrrus9a9g09e8kl
                            LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                            LEFT JOIN ehr.t_ehr_rus_6kiqts8t99 er1 ON er1.ehrrusid = dali.ehrrus6kiqts8t99
                            LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = er1.ehrrusrefbooks
                            LEFT JOIN ehr.t_ehr_rus_h17f8uts3n er2 ON er2.ehrrusid = dali.ehrrush17f8uts3n
                            LEFT JOIN ehr.t_ehr_rus_ref_books rrb2 ON rrb2.ehrrusrefbooksid = er2.ehrrusrefbooks
                            LEFT JOIN pharm.t_medication_forms mf ON mf.medicationformsid = dali.medicationforms
                            WHERE (p_id IS NULL OR dali.drugapplicationlist = p_id)
                              AND EXTRACT(YEAR FROM dal.enddate) >= EXTRACT(YEAR FROM p_datetime)
                              AND dal.isactive = TRUE
                           )
                       )
                   )
               )
        INTO v_result;
        
        INSERT INTO
            rds.t_rabbit_send_object (rabbitsendobjectid,
                                      queuename,
                                      content,
                                      messgeid,
                                      headers,
                                      contentencoding,
                                      type,
                                      userid,
                                      state,
                                      createdatetime,
                                      ownerid)
        SELECT gen_random_uuid(),
               s.servername,
               public.converttexttobinaryzip(v_result, ''),
               NULL,
               NULL,
               v_contentencoding,
               v_type,
               p_user,
               0,
               p_datetime,
               v_ownerid
        FROM rds.t_sync_servers s
        WHERE (p_queuename IS NOT NULL AND s.servername = p_queuename)
           OR (p_queuename IS NULL AND s.servertype IN (0));
    
    END IF;
END;
$$