DROP PROCEDURE IF EXISTS aps.sp_rds_sentremains;

CREATE OR REPLACE PROCEDURE aps.sp_rds_sentremains(
    "Xml" xml 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"::text;
    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 XMLELEMENT
                   (
                       NAME "SENT_REMAIN",
                       XMLELEMENT(NAME "header", XMLELEMENT(NAME "TYPE", v_ftsend)),
                       XMLELEMENT
                       (
                           NAME "body",
                           XMLELEMENT
                           (
                               NAME "SENT_REMAINS",
                               (SELECT XMLAGG
                                       (
                                           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"
                                               ),
                                               XMLELEMENT
                                               (
                                                   NAME "DAYS",
                                                   (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
                                                   )
                                               )
                                           )
                                       )
                               )
                           )
                       )
                   )::text
            INTO v_result
            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;
        
        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,
               dbo.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;