DROP PROCEDURE IF EXISTS aps.sp_rds_sentremains;

CREATE OR REPLACE PROCEDURE aps.sp_rds_sentremains(
    "Xml" text DEFAULT NULL,
    "IsNew" BOOLEAN DEFAULT TRUE,
    "ID" uuid DEFAULT NULL,
    "QueueName" VARCHAR DEFAULT NULL,
    "dateTime" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
    "User" uuid DEFAULT NULL
) AS
$$
DECLARE
    v_result          text;
    v_contentencoding varchar(255) := 'utf-8';
    v_type            varchar(255) := 'SENT_REMAIN';
    v_operationtype   varchar(255);
    v_ownerid         uuid;
    v_insert          varchar(6)   := 'Insert';
    v_update          varchar(6)   := 'Update';
    v_delete          varchar(6)   := 'Delete';
    v_ftload          varchar(6)   := 'FTLoad';
    v_ftsend          varchar(6)   := 'FTSend';
    v_rsoid           uuid         := gen_random_uuid();
    p_xml             xml          := "Xml"::xml;
    p_isnew           boolean      := "IsNew";
    p_id              uuid         := "ID";
    p_queuename       varchar      := "QueueName";
    p_datetime        timestamp    := "dateTime";
    p_user            uuid         := "User";

BEGIN
    p_datetime = COALESCE(p_datetime, CURRENT_TIMESTAMP(0));
    v_ownerid = p_id;
    
    IF p_xml IS NULL THEN
        
        IF p_id IS NULL THEN
            
            v_operationtype = v_ftsend;
        
        ELSIF
            (
                NOT EXISTS (SELECT 1
                            FROM stor.t_remains    r
                            JOIN aps.t_sent_remain sr ON sr.remains = r.remainsid AND sr.sentremainid = p_id
                           )
                ) THEN
            
            v_operationtype = v_delete;
            
            SELECT XMLELEMENT(
                       NAME "SENT_REMAIN",
                       XMLAGG
                       (
                           XMLELEMENT
                           (
                               NAME "header",
                               XMLFOREST
                               (
                                   v_operationtype AS "TYPE",
                                   sr.remains AS "ID"
                               )
                           )
                       )
                   )
            FROM aps.t_sent_remain sr
            WHERE sr.sentremainid = p_id;
        
    ELSE
        v_operationtype = v_update;
    END IF;
        
        
        IF v_operationtype = v_ftsend THEN
            
            SELECT '<SENT_REMAIN><header><TYPE>' || v_ftsend || '</TYPE></header><body><SENT_REMAINS>' ||
                   COALESCE(STRING_AGG(
                                XMLSERIALIZE(CONTENT xml_elem AS TEXT),
                                ''
                            ), '') ||
                   '</SENT_REMAINS></body></SENT_REMAIN>'
            INTO v_result
            FROM (SELECT XMLELEMENT(
                             NAME "SENT_REMAIN",
                             XMLFOREST(
                                 r.remainsid AS "ID",
                                 r.price AS "Price",
                                 r.lotnumber AS "LotNumber",
                                 r.series AS "Series",
                                 r.comment AS "Comment",
                                 TO_CHAR(r.expirationdate, 'DD.MM.YYYY') AS "ExpirationDate",
                                 r.amount AS "Amount",
                                 s.name AS "Storage",
                                 pip.pharmacy AS "Pharmacy",
                                 r.billofgoods AS "BillOfGoods",
                                 bg.medicationforms AS "MedicationForms",
                                 pt.name || '-' || abr.abr AS "PaymentType",
                                 pt.finance AS "Finance",
                                 gs.name AS "GoodsSupplier",
                                 sc.number AS "SupplierContractNumber",
                                 pkg.name AS "PackageType",
                                 (SELECT XMLAGG(
                                             XMLELEMENT(
                                                 NAME "ON_DAY",
                                                 XMLFOREST(
                                                     TO_CHAR(srd.date, 'DD.MM.YYYY') AS "Date",
                                                     srd.overin AS "OverIn",
                                                     srd.overout AS "OverOut",
                                                     srd.restin AS "RestIn",
                                                     srd.restout AS "RestOut"
                                                 )
                                             )
                                         )
                                  FROM stor.t_storage_rest_on_day srd
                                  WHERE srd.remain = r.remainsid
                                 ) AS "DAYS"
                             )
                         ) AS xml_elem
                  FROM stor.t_remains               r
                  JOIN stor.t_storage               s ON s.storageid = r.storage
                  JOIN aps.t_pharmacy_issue_place   pip ON pip.issueplaceid = s.issueplace
                  JOIN stor.t_supplier_contract     sc
                           ON sc.suppliercontractid = r.suppliercontract
                  JOIN stor.t_supplier_contract_abr abr ON abr.abr = r.suppliercontractabr
                  JOIN stor.t_payment_type          pt ON pt.code = abr.paymenttype
                  JOIN pharm.t_package_type         pkg ON pkg.packagetypeid = r.packagetype
                  JOIN stor.t_goods_supplier        gs ON gs.goodssupplierid = sc.goodssupplier
                  JOIN pharm.t_bill_of_goods        bg ON bg.billofgoodsid = r.billofgoods
                  WHERE r.amount > 0
                 ) AS sentremains;
        
        ELSIF v_operationtype <> v_delete THEN
            
            SELECT XMLELEMENT(
                       NAME "SENT_REMAIN",
                       XMLELEMENT
                       (
                           NAME "header",
                           XMLELEMENT(NAME "TYPE", v_operationtype),
                           XMLELEMENT(NAME "ID", r.remainsid)
                       ),
                       XMLELEMENT(
                           NAME "body",
                           XMLELEMENT(NAME "Price", r.price),
                           XMLELEMENT(NAME "LotNumber", r.lotnumber),
                           XMLELEMENT(NAME "Series", r.series),
                           XMLELEMENT(NAME "Comment", r.comment),
                           XMLELEMENT(NAME "ExpirationDate", TO_CHAR(r.expirationdate, 'DD.MM.YYYY')),
                           XMLELEMENT(NAME "Amount", r.amount),
                           XMLELEMENT(NAME "Storage", s.name),
                           XMLELEMENT(NAME "Pharmacy", pip.pharmacy),
                           XMLELEMENT(NAME "BillOfGoods", r.billofgoods),
                           XMLELEMENT(NAME "MedicationForms", bg.medicationforms),
                           XMLELEMENT(NAME "PaymentType", pt.name || '-' || abr.abr),
                           XMLELEMENT(NAME "Finance", pt.finance),
                           XMLELEMENT(NAME "GoodsSupplier", gs.name),
                           XMLELEMENT(NAME "SupplierContractNumber", sc.number),
                           XMLELEMENT(NAME "PackageType", pkg.name),
                           XMLELEMENT(NAME "ON_DAY",
                                XMLELEMENT(NAME "Date", COALESCE(TO_CHAR(srd.date, 'DD.MM.YYYY'),
                                                                 TO_CHAR(sr.modifieddate, 'DD.MM.YYYY'))),
                                XMLELEMENT(NAME "OverIn", COALESCE(srd.overin, 0)),
                                XMLELEMENT(NAME "OverOut", COALESCE(srd.overout, 0)),
                                XMLELEMENT(NAME "RestIn", COALESCE(srd.restin, 0)),
                                XMLELEMENT(NAME "RestOut", COALESCE(srd.restout, 0))
                           )
                       )
                   )::text
            INTO v_result
            FROM aps.t_sent_remain                 sr
            JOIN      stor.t_remains               r ON r.remainsid = sr.remains
            JOIN      pharm.t_bill_of_goods        bg ON bg.billofgoodsid = r.billofgoods
            JOIN      stor.t_storage               s ON s.storageid = r.storage
            JOIN      aps.t_pharmacy_issue_place   pip ON pip.issueplaceid = s.issueplace
            JOIN      stor.t_supplier_contract     sc ON sc.suppliercontractid = r.suppliercontract
            JOIN      stor.t_supplier_contract_abr abr ON abr.abr = r.suppliercontractabr
            JOIN      stor.t_payment_type          pt ON pt.code = abr.paymenttype
            JOIN      stor.t_goods_supplier        gs ON gs.goodssupplierid = sc.goodssupplier
            JOIN      pharm.t_package_type         pkg ON pkg.packagetypeid = r.packagetype
            LEFT JOIN stor.t_storage_rest_on_day   srd ON srd.date = sr.modifieddate AND srd.remain = sr.remains
            WHERE sr.sentremainid = p_id;
        
        END IF;
        
        INSERT INTO
            aps.t_rabbit_send_object
        (rabbitsendobjectid,
         queuename,
         content,
         messageid,
         headers,
         contentencoding,
         type,
         userid,
         state,
         createdatetime,
         ownerid)
        SELECT v_rsoid,
               p_queuename,
               converttexttobinaryzip(v_result, ''),
               NULL,
               NULL,
               v_contentencoding,
               v_type,
               p_user,
               0,
               p_datetime,
               v_ownerid;
        
        UPDATE aps.t_sent_remain
        SET rabbitsendobject = v_rsoid
        FROM aps.t_sent_remain sr
        WHERE sr.sentremainid = p_id
          AND p_id IS NOT NULL;
    
    END IF;
END;
$$ LANGUAGE plpgsql;