-- =============================================
-- 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, uuid_generate_v4()
        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 = uuid_generate_v4();
        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 = ISNULL(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 = ISNULL(ie.Error, '') || 'Не указано поле НДС.
          '
          from STOR.T_IMPORT_INVOICE ii
          where ie.ImportElementsID = ii.ImportElementsID and ii.VAT is null or ii.VAT > 3;
          
          update STOR.T_IMPORT_ELEMENTS ie
          set Error = ISNULL(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 = ISNULL(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 = ISNULL(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 = ISNULL(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 = ISNULL(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 = ISNULL(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 = ISNULL(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 = ISNULL(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 = ISNULL(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 = ISNULL(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 = ISNULL(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 = ISNULL(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 DATEPART(YEAR, s.Date) = DATEPART(YEAR, ii.InvoiceDate) and s.Storage = ii.Storage);
          
          update STOR.T_IMPORT_ELEMENTS ie
          set Error = ISNULL(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 DATEPART(YEAR, s.Date) = DATEPART(YEAR, ii.InvoiceDate) and s.Storage <> ii.Storage);
          
          update STOR.T_IMPORT_ELEMENTS ie
          set Error = ISNULL(ie.Error, '') || 'Данный элемент уже загружен, но цены не равны.
          '
          from STOR.T_IMPORT_INVOICE ii
          where ie.ImportElementsID = ii.ImportElementsID and exists(
              select * from STOR.T_INVOICE_ITEM iitem
              join STOR.T_STORAGE_INVOICE si on si.InvoiceID = iitem.StorageInvoice
                   where si.Number = ii.InvoiceNumber and DATEPART(YEAR, si.Date) = DATEPART(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 = ISNULL(ie.Error, '') || 'Данный элемент уже загружен, но НДС не равны.
          '
          from STOR.T_IMPORT_INVOICE ii
          where ie.ImportElementsID = ii.ImportElementsID and exists(
              select * from STOR.T_INVOICE_ITEM iitem
              join STOR.T_STORAGE_INVOICE si on si.InvoiceID = iitem.StorageInvoice
                   where si.Number = ii.InvoiceNumber and DATEPART(YEAR, si.Date) = DATEPART(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 = ISNULL(ie.Error, '') || 'Не найдена соответствующая запись по препарату, цене и НДС в гос-контракте
          '
          from STOR.T_IMPORT_INVOICE ii 
          where ie.ImportElementsID = ii.ImportElementsID and not exists
              (
                  select *
                  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,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 * 
          					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 main.Error = ISNULL(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 main.Error = ISNULL(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_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 = ISNULL(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;
$$;
