DROP PROCEDURE IF EXISTS public.sp_getsetdrugapplicationlist;

CREATE OR REPLACE PROCEDURE public.sp_getsetdrugapplicationlist(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "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"::boolean;
    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 '<DRUG_APPLICATION_LIST><header><TYPE>' || v_operationtype ||
               '</TYPE></header><body><DRUG_APPLICATION_LIST_ITEMS>' ||
               COALESCE(STRING_AGG(
                            XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                            ''), '')
                   ||
               '</DRUG_APPLICATION_LIST_ITEMS></body></DRUG_APPLICATION_LIST>'
        INTO v_result
        FROM (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",
                             dali.preparation AS "Preparation",
                             er.codeehr AS "EhrRus9a9g09e8kl",
                             rrb.currversion AS "EhrRus9a9g09e8klVer",
                             er1.idehr AS "EhrRus6kiqts8t99",
                             rrb1.currversion AS "EhrRus6kiqts8t99Ver",
                             er2.idehr AS "EhrRusH17F8UTS3N",
                             rrb2.currversion AS "EhrRusH17F8UTS3NVer"
                         )
                     ) AS xml_elem
              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
             ) tt;
        
        INSERT INTO
            rds.t_rabbit_send_object (rabbitsendobjectid,
                                      queuename,
                                      content,
                                      messageid,
                                      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;
$$