-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка остатков в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_rds_sentremains;
CREATE OR REPLACE PROCEDURE sp_rds_sentremains(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL,
    "QueueName" varchar(100) DEFAULT 'RDSSync',
    "dateTime" timestamp DEFAULT NULL,
    "User" uuid DEFAULT NULL,
    "PATH" text DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_operationtype text;
    v_sql           text;
BEGIN
    "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
    
    IF "PATH" IS NOT NULL THEN
        
        DROP TABLE IF EXISTS temp_remains_storage;
        CREATE TEMP TABLE temp_remains_storage
        (
            remainsid              uuid,
            storage                uuid,
            lotnumber              int,
            series                 text,
            billofgoods            uuid,
            pharmacy               int,
            comment                text,
            expirationdate         timestamp,
            amount                 numeric,
            price                  numeric,
            packagetype            uuid,
            paymenttype            text,
            finance                int,
            suppliercontract       uuid,
            suppliercontractnumber text,
            storageinvoiceadd      uuid,
            suppliercontractabr    text,
            vat                    int,
            producerprice          numeric,
            gtinserialnumber       text,
            conversionid           text,
            quantityinpackage      numeric,
            restid                 uuid,
            date                   timestamp,
            restin                 numeric,
            restout                numeric,
            overin                 numeric,
            overout                numeric,
            remain                 uuid
        );
        
        
        v_sql := FORMAT($f$
            INSERT INTO temp_remains_storage (
                remainsid, storage, lotnumber, series, billofgoods, pharmacy, comment,
                expirationdate, amount, price, packagetype, paymenttype, finance,
                suppliercontract, suppliercontractnumber, storageinvoiceadd, suppliercontractabr,
                vat, producerprice, gtinserialnumber, conversionid, quantityinpackage,
                restid, date, restin, restout, overin, overout, remain
            )
            SELECT
                r.remainsid, r.storage, r.lotnumber, r.series, r.billofgoods, pip.pharmacy, r.comment,
                r.expirationdate, r.amount, r.price, r.packagetype,
                pt.name || '-' || abr.abr, pt.finance, r.suppliercontract, sc.number,
                r.storageinvoiceadd, r.suppliercontractabr, r.vat, r.producerprice,
                r.gtinserialnumber, r.conversionid, r.quantityinpackage, d.restid, d.date,
                d.restin, d.restout, d.overin, d.overout, d.remain
            FROM %1$I.stor.t_remains r
            JOIN %1$I.pharm.t_bill_of_goods bg ON bg.billofgoodsid = r.billofgoods
            JOIN %1$I.stor.t_storage s ON s.storageid = r.storage
            JOIN %1$I.aps.t_pharmacy_issue_place pip ON pip.issueplaceid = s.issueplace
            JOIN %1$I.stor.t_storage_rest_on_day d ON d.remain = r.remainsid
            JOIN %1$I.stor.t_supplier_contract sc ON sc.suppliercontractid = r.suppliercontract
            JOIN %1$I.stor.t_supplier_contract_abr abr ON abr.abr = r.suppliercontractabr
            JOIN %1$I.stor.t_payment_type pt ON pt.code = abr.paymenttype
            JOIN %1$I.stor.t_goods_supplier gs ON gs.goodssupplierid = sc.goodssupplier
            JOIN %1$I.pharm.t_package_type pkg ON pkg.packagetypeid = r.packagetype
        $f$, "PATH");
        
        EXECUTE v_sql;
        
        -- Очистка целевых таблиц перед загрузкой
        DELETE FROM rds.t_storage_rest_on_day;
        DELETE FROM rds.t_remains;
        
        -- Загружаем новые остатки
        INSERT INTO
            rds.t_remains (remainsid,
                           storage,
                           lotnumber,
                           series,
                           billofgoods,
                           paymenttype,
                           comment,
                           expirationdate,
                           amount,
                           price,
                           pharmacy,
                           suppliercontractnumber,
                           finance,
                           drugcode,
                           lastmodifieddate)
        SELECT DISTINCT nr.remainsid,
                        nr.storage,
                        nr.lotnumber,
                        nr.series,
                        nr.billofgoods,
                        nr.paymenttype,
                        nr.comment,
                        nr.expirationdate,
                        nr.amount,
                        nr.price,
                        nr.pharmacy,
                        nr.suppliercontractnumber,
                        nr.finance,
                        bg.otherid,
                        NOW()
        FROM temp_remains_storage  nr
        JOIN pharm.t_bill_of_goods bg ON nr.billofgoods = bg.billofgoodsid
        WHERE NOT EXISTS (SELECT 1
                          FROM rds.t_remains r
                          WHERE r.remainsid = nr.remainsid
                         );
        
        INSERT INTO
            rds.t_storage_rest_on_day (restid, date, restin, restout, overin, overout, remain)
        SELECT gen_random_uuid(), -- генерируем новый UUID
               nr.date,
               nr.restin,
               nr.restout,
               nr.overin,
               nr.overout,
               nr.remainsid
        FROM temp_remains_storage nr
        WHERE NOT EXISTS (SELECT 1
                          FROM rds.t_storage_rest_on_day srd
                          WHERE srd.remain = nr.remainsid
                            AND srd.date = nr.date
                         );
    ELSE
        IF "Xml" IS NOT NULL THEN
            -- Получаем тип операции
            SELECT (XPATH('/SENT_REMAIN/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6)
            INTO v_operationtype;
            
            -- Обработка операции
            IF v_operationtype = 'FTSend' THEN
                -- Generate table Tmp_SENT_REMAINS
                DROP TABLE IF EXISTS tmp_sent_remains;
                CREATE TEMP TABLE tmp_sent_remains (
                    id uuid,
                    price numeric(12,2),
                    lotnumber integer,
                    series varchar(100),
                    comment varchar(255),
                    expirationdate date,
                    amount numeric(12,2),
                    storage varchar(255),
                    paymenttype varchar(255),
                    packagetype varchar(255),
                    goodssupplier varchar(255),
                    suppliercontractnumber varchar(255),
                    billofgoods uuid,
                    medicationforms uuid,
                    pharmacy integer,
                    finance integer,
                    date date,
                    restin numeric(12,2),
                    restout numeric(12,2),
                    overin numeric(12,2),
                    overout numeric(12,2)
                );
                INSERT INTO sent_remains_temp
                SELECT  ID,
                        PRICE,
                        LOTNUMBER,
                        SERIES,
                        coalesce(COMMENT, ''),
                        to_date(EXPIRATIONDATE, 'DD.MM.YYYY'),
                        AMOUNT,
                        STORAGE,
                        PAYMENTTYPE,
                        PACKAGETYPE,
                        GOODSSUPPLIER,
                        SUPPLIERCONTRACTNUMBER,
                        BILLOFGOODS,
                        MEDICATIONFORMS,
                        PHARMACY,
                        FINANCE,
                        to_date(DATE, 'DD.MM.YYYY'),
                        RESTIN,
                        RESTOUT,
                        OVERIN,
                        OVEROUT
                FROM XMLTABLE(
                    -- Путь к ON_DAY
                    '//SENT_REMAIN/body/SENT_REMAINS/SENT_REMAIN/DAYS/ON_DAY'
                    PASSING "Xml"
                    COLUMNS
                        id uuid PATH '../../ID[1]',
                        price numeric(12,2) PATH '../../Price[1]',
                        lotnumber integer PATH '../../LotNumber[1]',
                        series varchar(100) PATH '../../Series[1]',
                        comment varchar(255) PATH '../../Comment[1]',
                        expirationdate varchar(255) PATH '../../ExpirationDate[1]',
                        amount numeric(12,2) PATH '../../Amount[1]',
                        storage varchar(255) PATH '../../Storage[1]',
                        paymenttype varchar(255) PATH '../../PaymentType[1]',
                        packagetype varchar(255) PATH '../../PackageType[1]',
                        goodssupplier varchar(255) PATH '../../GoodsSupplier[1]',
                        suppliercontractnumber varchar(255) PATH '../../SupplierContractNumber[1]',
                        billofgoods uuid PATH '../../BillOfGoods[1]',
                        medicationforms uuid PATH '../../MedicationForms[1]',
                        pharmacy integer PATH '../../Pharmacy[1]',
                        finance integer PATH '../../Finance[1]',
                        date varchar(255)  PATH 'Date[1]',
                        restin numeric(12,2) PATH 'RestIn[1]',
                        restout numeric(12,2) PATH 'RestOut[1]',
                        overin numeric(12,2) PATH 'OverIn[1]',
                        overout numeric(12,2) PATH 'OverOut[1]'
                );

                -- Первичная загрузка
                -- Удаляем имеющиеся остатки
                DELETE FROM rds.t_storage_rest_on_day;
                DELETE FROM rds.t_remains;
                
                -- Загружаем новые остатки
                INSERT INTO
                    rds.t_remains (remainsid,
                                   storage,
                                   lotnumber,
                                   series,
                                   billofgoods,
                                   paymenttype,
                                   comment,
                                   expirationdate,
                                   amount,
                                   price,
                                   pharmacy,
                                   suppliercontractnumber,
                                   finance,
                                   drugcode,
                                   lastmodifieddate)
                SELECT DISTINCT nr.id,                     -- remainsid (UUID)
                                max(nr.storage),                -- storage (varchar)
                                max(nr.lotnumber),              -- lotnumber (int)
                                max(nr.series),                 -- series (varchar)
                                max(nr.billofgoods),            -- billofgoods (UUID)
                                max(nr.paymenttype),            -- paymenttype (varchar)
                                max(nr.comment),                -- comment (varchar)
                                max(nr.expirationdate),         -- expirationdate (date)
                                max(nr.amount),                 -- amount (numeric)
                                max(nr.price),                  -- price (numeric)
                                max(nr.pharmacy),               -- pharmacy (int)
                                max(nr.suppliercontractnumber), -- suppliercontractnumber (varchar)
                                max(nr.finance),                -- finance (int)
                                max(bg.otherid),                -- drugcode
                                NOW()                      -- lastmodifieddate
                FROM tmp_sent_remains      nr
                JOIN pharm.t_bill_of_goods bg ON nr.billofgoods = bg.billofgoodsid
                group by nr.id;
                
                -- Загружаем новые остатки на день
                INSERT INTO
                    rds.t_storage_rest_on_day (restid, date, restin, restout, overin, overout, remain)
                SELECT gen_random_uuid(), -- restid (UUID), вместо NEWID()
                       nr.date,           -- date (date or timestamp)
                       nr.restin,         -- restin (numeric)
                       nr.restout,        -- restout (numeric)
                       nr.overin,         -- overin (numeric)
                       nr.overout,        -- overout (numeric)
                       nr.id              -- remain (UUID)
                FROM tmp_sent_remains nr;
            
            ELSE
                IF v_operationtype = 'Update' THEN
                    DROP TABLE IF EXISTS tmp_sent_remain;
                    CREATE TEMP TABLE tmp_sent_remain AS
                        SELECT (XPATH('SENT_REMAIN/header/ID[1]/text()', x))[1]::text::uuid      AS id,
                                -- Rest
                                (XPATH('SENT_REMAIN/body/Price[1]/text()', x))[1]::text::numeric(12, 2)      AS price,
                                (XPATH('SENT_REMAIN/body/LotNumber[1]/text()', x))[1]::text::int             AS lotnumber,
                                (XPATH('SENT_REMAIN/body/Series[1]/text()', x))[1]::text                     AS series,
                                (XPATH('SENT_REMAIN/body/Comment[1]/text()', x))[1]::text                    AS comment,
                                TO_DATE((XPATH('SENT_REMAIN/body/ExpirationDate[1]/text()', x))[1]::text,'DD.MM.YYYY')                                          AS expirationdate,
                                (XPATH('SENT_REMAIN/body/Amount[1]/text()', x))[1]::text::numeric(12, 2)     AS amount,
                                (XPATH('SENT_REMAIN/body/Storage[1]/text()', x))[1]::text                    AS storage,
                                (XPATH('SENT_REMAIN/body/PaymentType[1]/text()', x))[1]::text                AS paymenttype,
                                (XPATH('SENT_REMAIN/body/GoodsSupplier[1]/text()', x))[1]::text              AS goodssupplier,
                                (XPATH('SENT_REMAIN/body/SupplierContractNumber[1]/text()', x))[1]::text     AS suppliercontractnumber,
                                (XPATH('SENT_REMAIN/body/PackageType[1]/text()', x))[1]::text                AS packagetype,
                                (XPATH('SENT_REMAIN/body/BillOfGoods[1]/text()', x))[1]::text::uuid          AS billofgoods,
                                (XPATH('SENT_REMAIN/body/MedicationForms[1]/text()', x))[1]::text::uuid      AS medicationforms,
                                (XPATH('SENT_REMAIN/body/Pharmacy[1]/text()', x))[1]::text::int              AS pharmacy,
                                (XPATH('SENT_REMAIN/body/Finance[1]/text()', x))[1]::text::int               AS finance,
                                -- Rest bodyn day
                                TO_DATE((XPATH('SENT_REMAIN/body/ON_DAY/Date[1]/text()', x))[1]::text, 'DD.MM.YYYY') AS date,
                                (XPATH('SENT_REMAIN/body/ON_DAY/RestIn[1]/text()', x))[1]::text::numeric(12, 2)      AS restin,
                                (XPATH('SENT_REMAIN/body/ON_DAY/RestOut[1]/text()', x))[1]::text::numeric(12, 2)     AS restout,
                                (XPATH('SENT_REMAIN/body/ON_DAY/OverIn[1]/text()', x))[1]::text::numeric(12, 2)      AS overin,
                                (XPATH('SENT_REMAIN/body/ON_DAY/OverOut[1]/text()', x))[1]::text::numeric(12, 2)     AS overout
                        FROM (SELECT UNNEST(XPATH('SENT_REMAIN', "Xml"::xml)) AS x) AS t;
                    
                    
                    -- Обновляем имеющиеся остатки
                    UPDATE rds.t_remains r
                    SET price                  = nr.price,
                        lotnumber              = nr.lotnumber,
                        series                 = nr.series,
                        comment                = coalesce(nr.comment, ''),
                        expirationdate         = nr.expirationdate,
                        amount                 = nr.amount,
                        storage                = nr.storage,
                        paymenttype            = nr.paymenttype,
                        billofgoods            = nr.billofgoods,
                        pharmacy               = nr.pharmacy,
                        suppliercontractnumber = nr.suppliercontractnumber,
                        finance                = nr.finance,
                        drugcode               = bg.otherid,
                        lastmodifieddate       = NOW()
                    FROM tmp_sent_remain       nr
                    JOIN pharm.t_bill_of_goods bg ON nr.billofgoods = bg.billofgoodsid
                    WHERE nr.id = r.remainsid;
                    
                    -- Обновляем имеющиеся остатки на день
                    UPDATE rds.t_storage_rest_on_day srd
                    SET restin  = nr.restin,
                        restout = nr.restout,
                        overin  = nr.overin,
                        overout = nr.overout
                    FROM tmp_sent_remain nr
                    WHERE nr.id = srd.remain
                      AND nr.date = srd.date;
                    
                    
                    -- Загружаем новые остатки
                    INSERT INTO
                        rds.t_remains (remainsid,
                                       storage,
                                       lotnumber,
                                       series,
                                       billofgoods,
                                       paymenttype,
                                       comment,
                                       expirationdate,
                                       amount,
                                       price,
                                       pharmacy,
                                       suppliercontractnumber,
                                       finance,
                                       drugcode,
                                       lastmodifieddate)
                    SELECT DISTINCT nr.id,
                                    nr.storage,
                                    nr.lotnumber,
                                    nr.series,
                                    nr.billofgoods,
                                    nr.paymenttype,
                                    coalesce(nr.comment, ''),
                                    nr.expirationdate,
                                    nr.amount,
                                    nr.price,
                                    nr.pharmacy,
                                    nr.suppliercontractnumber,
                                    nr.finance,
                                    bg.otherid,
                                    CURRENT_TIMESTAMP
                    FROM tmp_sent_remain       nr
                    JOIN pharm.t_bill_of_goods bg ON nr.billofgoods = bg.billofgoodsid
                    WHERE NOT EXISTS (SELECT 1
                                      FROM rds.t_remains r
                                      WHERE r.remainsid = nr.id
                                     );
                    
                    
                    -- Загружаем новые остатки на день
                    INSERT INTO
                        rds.t_storage_rest_on_day
                        (restid, date, restin, restout, overin, overout, remain)
                    SELECT gen_random_uuid() AS restid, -- уникальный идентификатор
                           nr.date,                     -- дата
                           nr.restin,
                           nr.restout,
                           nr.overin,
                           nr.overout,
                           nr.id             AS remain
                    FROM tmp_sent_remain nr
                    WHERE NOT EXISTS (SELECT 1
                                      FROM rds.t_storage_rest_on_day srd
                                      WHERE srd.remain = nr.id
                                        AND srd.date = nr.date
                                     );
                ELSE
                    IF v_operationtype = 'Delete' THEN
                        -- Generate table tmp_sent_remain_del
                        DROP TABLE IF EXISTS tmp_sent_remain_del;
                        CREATE TEMP TABLE tmp_sent_remain_del AS
                            SELECT (XPATH('./header[1]/ID[1]/text()', x))[1]::text::uuid AS id
                            FROM (SELECT UNNEST(XPATH('SENT_REMAIN', "Xml"::xml)) AS x
                                 ) AS t;
                        
                        -- Удаление остатков на день
                        DELETE
                        FROM rds.t_storage_rest_on_day r
                            USING tmp_sent_remain_del nr
                        WHERE nr.id = r.remain;
                        
                        -- Удаление остатков
                        DELETE
                        FROM rds.t_remains r
                            USING tmp_sent_remain_del nr
                        WHERE nr.id = r.remainsid;
                    ELSE
                        RAISE EXCEPTION 'Операция не поддерживается';
                        RETURN;
                    END IF;
                END IF;
            END IF;
        END IF;
    END IF;
END;
$$;
