-- =============================================
-- Author:        (C) Vitacard
-- Create date: 2015-12-15
-- Description:    Загрузка накладных
-- =============================================
DROP PROCEDURE IF EXISTS stor.sp_loadimportinvoice;

CREATE PROCEDURE stor.sp_loadimportinvoice(
    "UserID" uuid
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    DECLARE
        "IdEnt" integer := (SELECT e.entityid FROM vclib.t_entities e WHERE e.entname = 'IMPORT_INVOICE' LIMIT 1);
        "Add"   integer := (SELECT e.entityid
                            FROM vclib.t_entities e
                            WHERE e.entname = 'INVOICE_WRITE_OFF_BETWEEN_STORAGE'
                            LIMIT 1
                           );
        "Bet"   integer := (SELECT e.entityid FROM vclib.t_entities e WHERE e.entname = 'STORAGE_INVOICE_ADD' LIMIT 1);
    
    BEGIN
        -- Генерируем идентификаторы для накладных
        DROP TABLE IF EXISTS invoiceids;
        CREATE TEMP TABLE invoiceids
        (
            fname varchar(200),
            id    uuid
        );
        
        INSERT INTO invoiceids(fname, id)
        SELECT si.filenamecolumn, gen_random_uuid()
        FROM IDB.storage_invoice si
        GROUP BY si.filenamecolumn;
        
        UPDATE IDB.storage_invoice si
        SET invoiceid = ii.id
        FROM invoiceids ii
        WHERE ii.fname = si.filenamecolumn;
        
        -- Добавляем уникальный идентификатор
        UPDATE IDB.storage_invoice SET id = gen_random_uuid();
        UPDATE IDB.storage_invoice SET gtin = NULL WHERE gtin = '';
        UPDATE IDB.storage_invoice SET sgtin = NULL WHERE sgtin = '';
        
        -- Импорт
        INSERT INTO
            stor.t_import_elements
            (importelementsid, error, users, loaddate, type_, filename)
        SELECT si.id
             , NULL
             , "UserID"
             , NOW()
             , "IdEnt"
             , si.filenamecolumn
        FROM IDB.storage_invoice si;
        
        INSERT INTO
            stor.t_import_invoice
        ( invoiceid
        , invoicenumber
        , invoicedate
        , storage
        , post
        , amount
        , lotnumber
        , series
        , price
        , sum
        , expirationdate
        , comment
        , billofgoods
        , packagetype
        , importelementsid
        , suppliercontract
        , vat
        , gtin
        , sgtin
        , sender
        , shipmentdate
        , shipmentdocumentnumber)
        SELECT si.invoiceid
             , si.dnumber
             , si.ddate
             , stor.storageid
             , (SELECT ap.responsibleperson
                FROM stor.t_user_storage_link usl
                JOIN akuz.t_users             u ON usl.users = u.userid
                JOIN akuz.t_post              ap ON ap.resourceid = u.post
                WHERE usl.storage = stor.storageid
                  AND ap.responsibleperson IS NOT NULL
                LIMIT 1
               )
             , CAST(REPLACE(si.ko_all, ',', '.') AS decimal(12, 2))
             , si.part
             , si.series
             , CAST(REPLACE(si.price, ',', '.') AS decimal(12, 2))
             ,
            CAST(REPLACE(si.ko_all, ',', '.') AS decimal(12, 2)) * CAST(REPLACE(si.price, ',', '.') AS decimal(12, 2))
             , si.expdate
             , ''
             , bg.billofgoodsid
             , (SELECT pf.packagetype
                FROM pharm.t_product_form pf
                WHERE pf.billofgoods = bg.billofgoodsid
                  AND pf.unitprice = '1'
                LIMIT 1
               )
             , si.id
             , (SELECT sc.suppliercontractid FROM stor.t_supplier_contract sc WHERE sc.otherid = si.idgk LIMIT 1)
             , si.vat
             , si.gtin
             , si.sgtin
             , (SELECT sto.storageid
                FROM stor.t_storage         sto
                JOIN akuz.t_lpu_issue_place lip ON lip.issueplaceid = sto.issueplace
                JOIN akuz.t_lpu_main        lp ON lp.lpuid = lip.lpumain
                WHERE lp.lpucode + sto.number = CAST(si.org_to AS varchar(100))
                LIMIT 1
               )
             , si.ddate
             , si.dnumber
        FROM IDB.storage_invoice                                                                                     si
        LEFT JOIN LATERAL (SELECT b.billofgoodsid FROM pharm.t_bill_of_goods b WHERE b.otherid = si.nomk_ls LIMIT 1) bg
                      ON TRUE
        LEFT JOIN LATERAL (SELECT sto.storageid
                           FROM stor.t_storage         sto
                           JOIN akuz.t_lpu_issue_place lip ON lip.issueplaceid = sto.issueplace
                           JOIN akuz.t_lpu_main        lp ON lp.lpuid = lip.lpumain
                           WHERE lp.lpucode + sto.number = CAST(si.org_to AS varchar(100))
                           LIMIT 1)                                                                                  stor
                      ON TRUE;
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Не указано поле Номер накладной.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.importelementsid = ii.importelementsid
          AND ii.invoicenumber IS NULL;
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Не указано поле НДС.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.importelementsid = ii.importelementsid AND ii.vat IS NULL
           OR ii.vat > 5;
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Не указано поле Дата накладной.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.
                  importelementsid = ii.importelementsid
          AND ii.invoicedate IS NULL;
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Не указано поле Количество.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.
                  importelementsid = ii.importelementsid
          AND ii.amount IS NULL;
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Не указано поле Серия.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.
                  importelementsid = ii.importelementsid
          AND ii.series IS NULL;
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Не указано поле Цена.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.
                  importelementsid = ii.importelementsid
          AND ii.price IS NULL;
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Не указано поле Срок годности.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.
                  importelementsid = ii.importelementsid
          AND ii.expirationdate IS NULL;
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Не найден склад по указанным данным - ' || si.org_to::VARCHAR(100) || '
          '
        FROM stor.t_import_invoice ii
        JOIN IDB.storage_invoice   si ON si.id = ie.importelementsid
        WHERE ie.importelementsid = ii.importelementsid
          AND ii.storage IS NULL;
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Не найден сотрудник по указанным данным.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.
                  importelementsid = ii.importelementsid
          AND ii.post IS NULL;
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Не найден препарат по указанным данным - ' || si.nomk_ls::VARCHAR(500) || '
          '
        FROM stor.t_import_invoice ii
        JOIN IDB.storage_invoice   si ON si.id = ie.importelementsid
        WHERE ie.importelementsid = ii.importelementsid
          AND ii.billofgoods IS NULL;
        
        
        UPDATE stor.t_import_elements ie
        SET error =
                COALESCE(ie.error, '') || 'У препарата не указан gtin или не равен тому что указан в dbf.Препарат - ' ||
                si.nomk_ls::VARCHAR(500) || '
          '
        FROM stor.t_import_invoice ii
        JOIN IDB.storage_invoice   si ON si.
                                             id = ie.importelementsid
        JOIN pharm.t_bill_of_goods bg ON bg.
                                             billofgoodsid = ii.billofgoods
        WHERE ie.importelementsid = ii.importelementsid
          AND ii.sgtin IS NOT NULL
          AND (ii.gtin IS NULL OR bg.gtin IS NULL OR bg.gtin <> ii.gtin);
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Не найден вид упаковки товара по указанным данным.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.
                  importelementsid = ii.importelementsid
          AND ii.packagetype IS NULL;
        
        UPDATE stor.t_import_elements ie
        SET error =
                COALESCE(ie.error, '') || 'Не найден гос.контракт по указанным данным - ' || si.idgk::VARCHAR(100) || '
          '
        FROM stor.t_import_invoice ii
        JOIN IDB.storage_invoice   si ON si.
                                             id = ie.importelementsid
        WHERE ie.importelementsid = ii.importelementsid
          AND ii.suppliercontract IS NULL;
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Данная накладная уже загружена.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.
                  importelementsid = ii.importelementsid
          AND EXISTS(SELECT 1
                     FROM stor.t_storage_invoice s
                     WHERE s.number = ii.invoicenumber
                       AND EXTRACT(YEAR FROM s.date) = EXTRACT(YEAR FROM ii.invoicedate)
                       AND s.storage = ii.storage
                    );
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Данная накладная уже загружена, но склады не равны.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.
                  importelementsid = ii.importelementsid
          AND EXISTS(SELECT
                     FROM stor.t_storage_invoice s
                     WHERE s.number = ii.invoicenumber
                       AND EXTRACT(YEAR FROM s.date) = EXTRACT(YEAR FROM ii.invoicedate)
                       AND s.storage <> ii.storage
                    );
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Данный элемент уже загружен, но цены не равны.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.
                  importelementsid = ii.importelementsid
          AND EXISTS(SELECT 1
                     FROM stor.t_invoice_item    iitem
                     JOIN stor.t_storage_invoice si ON si.invoiceid = iitem.storageinvoice
                     WHERE si.number = ii.invoicenumber
                       AND EXTRACT(YEAR FROM si.date) = EXTRACT(YEAR FROM ii.invoicedate)
                       AND si.storage = ii.storage
                       AND iitem.billofgoods = ii.billofgoods
                       AND ii.price <> iitem.price
                       AND ii.vat = iitem.vat
                    );
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Данный элемент уже загружен, но НДС не равны.
          '
        FROM stor.t_import_invoice ii
        WHERE ie.
                  importelementsid = ii.importelementsid
          AND EXISTS(SELECT 1
                     FROM stor.t_invoice_item    iitem
                     JOIN stor.t_storage_invoice si ON si.invoiceid = iitem.storageinvoice
                     WHERE si.number = ii.invoicenumber
                       AND EXTRACT(YEAR FROM si.date) = EXTRACT(YEAR FROM ii.invoicedate)
                       AND si.storage = ii.storage
                       AND iitem.billofgoods = ii.billofgoods
                       AND ii.price = iitem.price
                       AND ii.vat <> iitem.vat
                    );
        
        UPDATE stor.t_import_elements ie
        SET error = COALESCE(ie.error, '') || 'Не найдена соответствующая запись по препарату, цене и НДС в гос-контракте
          '
        FROM stor.t_import_invoice ii
        WHERE ie.
                  importelementsid = ii.importelementsid
          AND NOT EXISTS
            (SELECT 1
             FROM stor.t_contract_elements ce
             WHERE ce.suppliercontract = ii.suppliercontract
               AND ce.billofgoods = ii.billofgoods
               AND ce.price = ii.price
               AND ce.vat = ii.vat
            );
        
        DROP TABLE IF EXISTS temp_invoice_item;
        CREATE TEMP TABLE temp_invoice_item
        (
            price            decimal(12, 2),
            amount           decimal(20, 6),
            suppliercontract uuid,
            billofgoods      uuid,
            vat              integer,
            storageinvoice   uuid
        );
        
        INSERT INTO
            temp_invoice_item(price, amount, suppliercontract, billofgoods, vat, storageinvoice)
        SELECT price, amount, t.suppliercontract, billofgoods, vat, storageinvoice
        FROM stor.t_invoice_item    t
        JOIN stor.t_storage_invoice s ON s.invoiceid = t.storageinvoice
        WHERE s.type_ IN ("Add", "Bet")
          AND EXISTS(SELECT 1
                     FROM stor.t_import_elements ie
                     JOIN stor.t_import_invoice  ii ON ie.importelementsid = ii.importelementsid
                     WHERE ii.suppliercontract = t.suppliercontract
                    );
        
        DROP TABLE IF EXISTS temp_storage_invoice;
        CREATE TEMP TABLE temp_storage_invoice
        (
            invoiceid uuid,
            type_     integer
        );
        
        INSERT INTO temp_storage_invoice(invoiceid, type_)
        SELECT invoiceid, type_
        FROM stor.t_storage_invoice s
        WHERE s.invoiceid IN (SELECT t.storageinvoice FROM temp_invoice_item t);
        
        UPDATE stor.t_import_elements main
        SET error = COALESCE(main.error, '') || 'Итоговая сумма элементов, данной накладной и уже загруженных накладных, превышает итоговое количество по гос. контракту.
          '
        FROM stor.t_import_invoice impinv
        WHERE main.
                  importelementsid = impinv.importelementsid
          AND (main.error IS NULL OR main.error = '')
          AND EXISTS (SELECT *
                      FROM stor.t_contract_elements ce
                      LEFT JOIN (SELECT ii.price,
                                        SUM(ii.amount) AS sum,
                                        ii.suppliercontract,
                                        ii.billofgoods,
                                        ii.vat
                                 FROM temp_invoice_item    ii
                                 JOIN temp_storage_invoice si ON si.invoiceid = ii.storageinvoice
                                 WHERE si.type_ = "Add" -- прибавляем только приход
                                 GROUP BY ii.price, ii.suppliercontract, ii.billofgoods, ii.vat
                                )                   inc
                                    ON
                                    inc.suppliercontract =
                                    ce.suppliercontract AND
                                    inc.billofgoods =
                                    ce.billofgoods AND
                                    inc.price =
                                    ce.price AND
                                    inc.vat =
                                    ce.vat
                      LEFT JOIN (SELECT ii1.price,
                                        SUM(ii1.amount) AS sum,
                                        ii1.suppliercontract,
                                        ii1.billofgoods,
                                        ii1.vat
                                 FROM temp_invoice_item    ii1
                                 JOIN temp_storage_invoice si ON si.invoiceid = ii1.storageinvoice
                                 WHERE si.type_ = "Bet" -- отнимаем только возвратные
                                 GROUP BY ii1.price, ii1.suppliercontract, ii1.billofgoods, ii1.vat
                                )                   outc
                                    ON
                                    outc.suppliercontract =
                                    ce.suppliercontract AND
                                    outc.billofgoods =
                                    ce.billofgoods AND
                                    outc.price =
                                    ce.price AND
                                    outc.vat =
                                    ce.vat
                      LEFT JOIN (SELECT impi.price,
                                        SUM(impi.amount) AS sum,
                                        impi.suppliercontract,
                                        impi.billofgoods,
                                        impi.vat
                                 FROM stor.t_import_elements impe
                                 JOIN stor.t_import_invoice  impi
                                          ON impe.importelementsid = impi.importelementsid
                                 WHERE impe.error IS NULL
                                 GROUP BY impi.price,
                                          impi.suppliercontract,
                                          impi.billofgoods,
                                          impi.vat
                                )                   inpe
                                    ON
                                    inpe.suppliercontract =
                                    ce.suppliercontract AND
                                    inpe.billofgoods =
                                    ce.billofgoods AND
                                    inpe.price =
                                    ce.price
                      WHERE ce.amount <
                            (COALESCE(inc.sum, 0) +
                             COALESCE(inpe.sum, 0) -
                             COALESCE(outc.sum, 0))
                        AND ce.suppliercontract = impinv.suppliercontract
                        AND ce.price = impinv.price
                        AND ce.vat = impinv.vat
                        AND ce.billofgoods = impinv.billofgoods
                     );
        
        UPDATE stor.t_import_elements main
        SET error = COALESCE(main.error, '') || 'Итоговая стоимость элементов, данной накладной и уже загруженных накладных, превышает итоговую сумму по гос. контракту.
          '
        FROM stor.t_import_invoice impinv
        WHERE main.
                  importelementsid = impinv.importelementsid
          AND (main.error IS NULL OR main.error = '')
          AND EXISTS
            (SELECT 1
             FROM stor.t_supplier_contract sc
             LEFT JOIN (SELECT ii.price, SUM(ii.amount * ii.amount) AS sum, ii.suppliercontract, ii.billofgoods
                        FROM temp_invoice_item    ii
                        JOIN temp_storage_invoice si ON si.invoiceid = ii.storageinvoice
                        WHERE si.type_ = "Add" -- прибавляем только приход
                        GROUP BY ii.price, ii.suppliercontract, ii.billofgoods
                       )                   inc ON inc.suppliercontract = sc.suppliercontractid
             LEFT JOIN (SELECT ii1.price, SUM(ii1.amount * ii1.price) AS sum, ii1.suppliercontract, ii1.billofgoods
                        FROM temp_invoice_item    ii1
                        JOIN temp_storage_invoice si ON si.invoiceid = ii1.storageinvoice
                        WHERE si.type_ = "Bet" -- отнимаем только возвратные
                        GROUP BY ii1.price, ii1.suppliercontract, ii1.billofgoods
                       )                   outc ON outc.suppliercontract = sc.suppliercontractid
             LEFT JOIN (SELECT impi.price, SUM(impi.amount * impi.price) AS sum, impi.suppliercontract, impi.billofgoods
                        FROM stor.t_import_elements impe
                        JOIN stor.t_import_invoice  impi ON impe.importelementsid = impi.importelementsid
                        WHERE impe.error IS NULL
                        GROUP BY impi.price, impi.suppliercontract, impi.billofgoods
                       )                   inpe ON inpe.suppliercontract = sc.suppliercontractid
             WHERE sc.sum IS NOT NULL
               AND sc.sum > 0
               AND sc.sum < (COALESCE(inc.sum, 0) + COALESCE(inpe.sum, 0) - COALESCE(outc.sum, 0))
               AND sc.suppliercontractid = impinv.suppliercontract
            );
        
        DROP TABLE temp_storage_invoice;
        DROP TABLE temp_invoice_item;
        
        UPDATE stor.t_import_elements ie
        SET error =
                COALESCE(ie.error, '') || 'Указанное количество - ' || ii.amount::VARCHAR(100) || ', для препарата ' ||
                bg.otherid::VARCHAR(100) || ', не соответствует коэффициенту дробления - ' || bg.rate::VARCHAR(100) || '
          '
        FROM stor.t_import_invoice ii
        JOIN pharm.t_bill_of_goods bg ON bg.
                                             billofgoodsid = ii.billofgoods
        WHERE ie.importelementsid = ii.importelementsid
          AND (ii.amount - CAST(ii.amount AS integer)) > 0
          AND (
            bg.rate = 0 OR
            bg.rate = 1 OR
            ((ii.amount - CAST(ii.amount AS int)) % (1 / CAST(bg.rate AS decimal))) <> 0
            );
    END;
END;
$$;
