DROP PROCEDURE IF EXISTS STOR.sp_LoadImportInvoice;

CREATE OR REPLACE PROCEDURE STOR.sp_LoadImportInvoice(
    "UserID" uuid
)
    LANGUAGE plpgsql
AS
$$
    DECLARE
        v_Bet    INTEGER;
        v_Add    INTEGER;
        v_ImpInv INTEGER;


    BEGIN
        CREATE TEMP TABLE t_InvoiceIds
        (
            fName TEXT,
            id    uuid
        );

        SELECT
            e.EntityID
        INTO
            v_Bet
        FROM VCLib.T_ENTITIES e
        WHERE
            e.EntName = 'INVOICE_WRITE_OFF_BETWEEN_STORAGE'
        LIMIT 1;

        SELECT
            e.EntityID
        INTO
            v_Add
        FROM VCLib.T_ENTITIES e
        WHERE
            e.EntName = 'STORAGE_INVOICE_ADD'
        LIMIT 1;

        SELECT
            e.EntityID
        INTO
            v_ImpInv
        FROM VCLib.T_ENTITIES e
        WHERE
            e.EntName = 'IMPORT_INVOICE'
        LIMIT 1;

        INSERT INTO
            t_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 = id
        FROM t_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",
            CURRENT_TIMESTAMP,
            v_ImpInv,
            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 *
        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;

        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::TEXT || '.'
        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::TEXT || '.'
        FROM STOR.T_IMPORT_INVOICE ii
        JOIN IDB.STORAGE_INVOICE si ON si.ID = ii.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::TEXT
        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::TEXT
        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 DATE_PART('year', s.Date) = DATE_PART('year', 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_STORAGE_INVOICE s
                     WHERE
                           s.Number = ii.InvoiceNumber
                       AND DATE_PART('year', s.Date) = DATE_PART('year', 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 DATE_PART('year', si.Date) = DATE_PART('year', 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 DATE_PART('year', si.Date) = DATE_PART('year', 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);

        CREATE TEMPORARY TABLE temp_T_INVOICE_ITEM AS
        SELECT
            t.*
        FROM STOR.T_INVOICE_ITEM t
        JOIN STOR.T_STORAGE_INVOICE s ON s.InvoiceID = t.StorageInvoice
        WHERE
              s.type_ IN (v_Add, v_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);

        CREATE TEMPORARY TABLE temp_T_STORAGE_INVOICE AS
        SELECT *
        FROM STOR.T_STORAGE_INVOICE s
        WHERE
            s.InvoiceID IN (SELECT
                                t.StorageInvoice
                            FROM temp_T_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
                          1
                      FROM STOR.T_CONTRACT_ELEMENTS ce
                      LEFT JOIN (SELECT
                                     ii.Price,
                                     SUM(ii.Amount) AS sum,
                                     ii.SupplierContract,
                                     ii.BillOfGoods,
                                     ii.VAT
                                 FROM temp_T_INVOICE_ITEM ii
                                 JOIN temp_T_STORAGE_INVOICE si ON si.InvoiceID = ii.StorageInvoice
                                 WHERE
                                     si.type_ = v_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_T_INVOICE_ITEM ii1
                                 JOIN temp_T_STORAGE_INVOICE si ON si.InvoiceID = ii1.StorageInvoice
                                 WHERE
                                     si.type_ = v_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 impI.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.Price) AS Sum,
                                       ii.SupplierContract,
                                       ii.BillOfGoods
                                   FROM temp_T_INVOICE_ITEM ii
                                   JOIN temp_T_STORAGE_INVOICE si ON si.InvoiceID = ii.StorageInvoice
                                   WHERE
                                       si.type_ = v_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_T_INVOICE_ITEM II1
                                   JOIN temp_T_STORAGE_INVOICE si ON si.InvoiceID = ii1.StorageInvoice
                                   WHERE
                                       si.type_ = v_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.SupplierContract, impI.Price, impI.BillOfGoods) impE
                        ON impE.SupplierContract = sc.SupplierContractID
                        WHERE
                              sc.Sum IS NOT NULL
                          AND sc.Sum > 0
                          AND sc.Sum < (COALESCE(inc.Sum, 0) + COALESCE(impE.Sum, 0) - COALESCE(outc.Sum, 0))
                          AND sc.SupplierContractID = impInv.SupplierContract));


        DROP TABLE temp_T_STORAGE_INVOICE;
        DROP TABLE temp_T_INVOICE_ITEM;

        UPDATE STOR.T_IMPORT_ELEMENTS ie
        SET
            Error = COALESCE(ie.Error, '') || 'Указанное количество - ' || ii.Amount::TEXT || ', для препарата ' ||
                    bg.OtherId::TEXT || ', не соответствует коэффициенту дробления - ' || bg.Rate::TEXT
        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 - ii.Amount::INTEGER) > 0
                      AND (
                      bg.Rate = 0 OR bg.Rate = 1
                          OR ((ii.Amount - ii.Amount::INTEGER) % (1 / bg.Rate::DECIMAL)) <> 0)
                  );
    END;
$$