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

CREATE PROCEDURE stor.sp_ImportInvoice
(
	"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 = 'STORAGE_INVOICE_ADD' limit 1);
		"Item" integer := (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'INVOICE_ITEM' limit 1);
	
		Begin
		    INSERT INTO STOR.T_STORAGE_INVOICE
			           (InvoiceID
			           ,Number
			           ,Date
			           ,State
			           ,Storage
			           ,Creator
			           ,CreateDate
			           ,HoldingUser
			           ,HoldingDate
			           ,PaymentType
			           ,InventoryDocument
			           ,type_
			           ,ResponsiblePerson
					   ,Loaded
					   ,ShipmentDate
					   ,ShipmentDocumentNumber
					   ,SupplierContract
					   ,MarkSchemes) 
			     select
			           ii.InvoiceId
			           ,ii.InvoiceNumber
			           ,ii.InvoiceDate
			           ,0
			           ,ii.Storage
			           ,ie.Users
			           ,ie.LoadDate
			           ,null
			           ,null
			           ,sca.PaymentType
			           ,null
			           ,"Add"
			           ,ii.Post
					   ,1
			           ,ii.ShipmentDate
			           ,ii.ShipmentDocumentNumber
					   ,case when MAX(ii.sgtin) is not null then max(cast(ii.SupplierContract as varbinary(36))) end
					   ,case when MAX(ii.sgtin) is not null then (select s.MarkSchemes from STOR.T_SUPPLIER s where max(ii.sender) = s.Supplier and ii.storage = s.storage limit 1) end
				from STOR.T_IMPORT_INVOICE ii
					join STOR.T_IMPORT_ELEMENTS ie on ie.ImportElementsID = ii.ImportElementsID
					join STOR.T_SUPPLIER_CONTRACT sc on sc.SupplierContractID = ii.SupplierContract
					join STOR.T_SUPPLIER_CONTRACT_ABR sca on sca.Abr = sc.SupplierContractAbr
		        where ie.Users = "UserID" and
		        -- накладная не принимается, если есть хотя бы одна ошибка
		        not exists
		        (
		            select *
		            from STOR.T_IMPORT_INVOICE ii1 
		                join STOR.T_IMPORT_ELEMENTS ie1 on ie1.ImportElementsID = ii1.ImportElementsID 
		            where ie1.Error is not null and ie1.Users = "UserID" and ii1.InvoiceId = ii.InvoiceId
		        )
			    group by ii.InvoiceId, ii.InvoiceNumber, ii.InvoiceDate, ii.Storage, ie.Users, ie.LoadDate, sca.PaymentType, ii.Post,ii.ShipmentDate,ii.ShipmentDocumentNumber;
		
		    INSERT INTO STOR.T_STORAGE_INVOICE_ADD
			           (InvoiceWriteOffBetweenStorage
			           ,InvoiceID
					   ,Sender)
			    select
			          null
			          ,ii.InvoiceId
			          ,MAX(cast(ii.Sender as varbinary(36)))
				from STOR.T_IMPORT_INVOICE ii
		    		 join STOR.T_IMPORT_ELEMENTS ie on ie.ImportElementsID = ii.ImportElementsID
		    		 join STOR.T_SUPPLIER_CONTRACT sc on sc.SupplierContractID = ii.SupplierContract
					 join STOR.T_SUPPLIER_CONTRACT_ABR sca on sca.Abr = sc.SupplierContractAbr
				where ie.Users = "UserID" and
		        -- накладная не принимается, если есть хотя бы одна ошибка
		        not exists
		        (
		            select *
		            from STOR.T_IMPORT_INVOICE ii1 
		                join STOR.T_IMPORT_ELEMENTS ie1 on ie1.ImportElementsID = ii1.ImportElementsID 
		            where ie1.Error is not null and ie1.Users = "UserID" and ii1.InvoiceId = ii.InvoiceId
		        )
				group by ii.InvoiceId, ii.InvoiceNumber, ii.InvoiceDate, ii.Storage, ie.Users, ie.LoadDate, sca.PaymentType, ii.Post;
		
		    INSERT INTO STOR.T_INVOICE_ITEM
			           (StorageInvoice
			           ,Remains
			           ,ElementID
					   ,SupplierContract
					   ,StorageInvoiceAdd
					   ,SupplierContractAbr
					   ,MarkCheck
					   ,ScanerCheck
					   ,QuantityInPackage
			           ,Amount
			           ,LotNumber
			           ,Series
			           ,Price
			           ,ExpirationDate
			           ,Comment
			           ,TotalApplicationCount
			           ,BillOfGoods
			           ,PackageType
			           ,TotalPackageType
			           ,VAT
			           ,type_
					   ,GTINSerialNumber
					   ,ConversionId
					   ,SellingPrice)
			     SELECT
			           ii.InvoiceId
			           ,null
			           ,ii.ImportElementsID
					   ,ii.SupplierContract
					   ,ii.InvoiceId
					   ,sc.SupplierContractAbr
					   ,0
					   ,0
					   ,1
			           ,ii.Amount
			           ,ii.LotNumber
			           ,ii.Series
			           ,ii.Price
			           ,ii.ExpirationDate
			           ,ii.Comment
			           ,ii.Amount
			           ,ii.BillOfGoods
			           ,ii.PackageType
			           ,ii.PackageType
			           ,coalesce(ii.VAT,0)
			           ,"Item"
					   ,coalesce(ii.sgtin,'')
					   ,uuid_generate_v4()
					   ,0
			from STOR.T_IMPORT_INVOICE ii
				join STOR.T_SUPPLIER_CONTRACT sc on sc.SupplierContractID = ii.SupplierContract
				join STOR.T_IMPORT_ELEMENTS ie on ie.ImportElementsID = ii.ImportElementsID
		    where ie.Users = "UserID" and
		        -- накладная не принимается, если есть хотя бы одна ошибка
		        not exists
		        (
		            select *
		            from STOR.T_IMPORT_INVOICE ii1 
		                join STOR.T_IMPORT_ELEMENTS ie1 on ie1.ImportElementsID = ii1.ImportElementsID 
		            where ie1.Error is not null and ie1.Users = "UserID" and ii1.InvoiceId = ii.InvoiceId
		        );
		
		    -- накладная не принимается, если есть хотя бы одна ошибка
		    delete from STOR.T_IMPORT_INVOICE ii
				using STOR.T_IMPORT_ELEMENTS ie
		    where ie.ImportElementsID = ii.ImportElementsID and ie.Users = "UserID" and
		        -- накладная не принимается, если есть хотя бы одна ошибка
		        not exists
		        (
		            select *
		            from STOR.T_IMPORT_INVOICE ii1 
		                join STOR.T_IMPORT_ELEMENTS ie1 on ie1.ImportElementsID = ii1.ImportElementsID 
		            where ie1.Error is not null and ie1.Users = "UserID" and ii1.InvoiceId = ii.InvoiceId
		        );
		
		    
		
		    delete from STOR.T_IMPORT_ELEMENTS ie 
		    where type_ = "IdEnt" and ie.Users = "UserID" and
		        -- накладная не принимается, если есть хотя бы одна ошибка
		        not exists 
		        (
		            select *
		            from STOR.T_IMPORT_INVOICE ii
		            where ii.ImportElementsID = ie.ImportElementsID
		        );
	end;	
End;
$$;