-- =============================================
-- Author:        (C) Vitacard
-- Create date: 2015-11-01
-- Description:    Загрзка накладных
-- =============================================
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[STOR].[sp_ImportInvoice]') AND type in (N'P', N'PC'))
DROP PROCEDURE [STOR].[sp_ImportInvoice]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [STOR].[sp_ImportInvoice] 
    @UserID uniqueidentifier
AS
BEGIN

declare @IdEnt int, @Add int, @Item int
select @IdEnt = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'IMPORT_INVOICE'
select top 1 @Add = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'STORAGE_INVOICE_ADD'
select top 1 @Item = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'INVOICE_ITEM'

    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
			   --сорокина сказала захардкодить 601
			   ,ISNULL((case when MAX(ii.sgtin) is not null then (select top 1 sms.MarkSchemes 
																from STOR.T_STORAGE_LINK_MARK_SCHEMES sms
																join STOR.T_SUPPLIER s on s.SupplierID = sms.Supplier and sms.MainSchema = 1
																where max(ii.sender) = s.Supplier and ii.storage = s.storage) end), 601)
		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
	           ,Coalesce(ii.LotNumber, 0)
	           ,ii.Series
	           ,ii.Price
	           ,ii.ExpirationDate
	           ,ii.Comment
	           ,ii.Amount
	           ,ii.BillOfGoods
	           ,ii.PackageType
	           ,ii.PackageType
	           ,ISNULL(ii.VAT,0)
	           ,@Item
			   ,ISNULL(ii.sgtin,'')
			   ,newid()
			   ,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 ii
    from [STOR].T_IMPORT_INVOICE ii
        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 ie
    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