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" = ii.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" = '';
            
        UPDATE "IDB"."STORAGE_INVOICE"
        SET
            "PART" = NULL
        WHERE
            "PART" = '';

        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 
           si."InvoiceID"
           ,si."DNUMBER"
           ,si."DDATE"
           ,stor.StorageID
           ,(select ap.ResponsiblePerson 
           from APS.T_USERS u
                join STOR.T_STORAGE sto on sto.StorageID = u.Storage
                join APS.T_PHARMACY_ISSUE_PLACE pip on pip.IssuePlaceID = sto.IssuePlace
			      join APS.T_POST ap on ap.PostID = u.Post
           where pip.Pharmacy = Cast(si."ORG_TO" as varchar(100)) and ap.ResponsiblePerson is not null limit 1)
           ,si."KO_ALL"
           ,si."PART"
           ,si."SERIES"
           ,si."PRICE"
           ,si."KO_ALL" * si."PRICE"
           ,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 where sto.Number = si."ORG_FROM" 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 APS.T_PHARMACY_ISSUE_PLACE pip on pip.IssuePlaceID = sto.IssuePlace
                 where pip.Pharmacy = Cast(si."ORG_TO" as varchar(100)) limit 1) stor ON TRUE
       LEFT JOIN LATERAL (Select pf.PackageType 
                            from PHARM.T_PRODUCT_FORM pf 
                   where pf.BillOfGoods = BG.BillOfGoodsID and pf.UnitPrice = true and (pf.EhrRusMdr308 = stor.EhrRusMdr308 or (pf.EhrRusMdr308 is null and 
																																	not exists(Select ff.PackageType 
																																				from PHARM.T_PRODUCT_FORM ff 
																																				where ff.BillOfGoods = bg.BillOfGoodsID and ff.EhrRusMdr308 = stor.EhrRusMdr308))) limit 1
						) ptype 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 > 6);

        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" = ii.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" = ii.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" = ii.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;
$$