-- =============================================
-- Author:        (C) Vitacard
-- Create date: 2015-11-01
-- Description:    Загрзка контрактов
-- =============================================
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[STOR].[sp_ImportContract]') AND type in (N'P', N'PC'))
DROP PROCEDURE [STOR].[sp_ImportContract]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

declare @IdEnt int, @idContract  int

select top 1 @IdEnt = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'IMPORT_CONTRACT'
select top 1 @idContract = e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'SUPPLIER_CONTRACT'

INSERT INTO [STOR].[T_SUPPLIER_CONTRACT]
           ([SupplierContractID]
           ,[Date]
           ,[Number]
           ,[GoodsSupplier]
           ,[type_]
           ,[Comment]
		   ,[OtherID]
		   ,[SupplierContractGroup]
           ,[SupplierContractAbr]
		   ,[ContractType]
           ,[TypeOfContract])
	SELECT
           ic.SupplierContractID
           ,ic.[Date]
           ,ic.[Number]
           ,ic.[GoodsSupplier]
           ,@idContract
           ,ic.[Comment]
		   ,ic.[OtherID]
		   ,(Case when (ic.[PayType] is null or ic.[PayType] = '') then null else ic.[PayType] end)
		   ,ic.[Abr]
		   ,6
           ,0
		from [STOR].T_IMPORT_CONTRACT ic
					join [STOR].T_IMPORT_ELEMENTS ie on ie.ImportElementsID = ic.ImportElementsID
			   where ie.Error is null and ie.Users = @UserID 
			   and not exists(select * from [STOR].T_SUPPLIER_CONTRACT spc where spc.OtherID = ic.OtherID)
	    group by ic.SupplierContractID, ic.[Date],ic.[Number] ,ic.[GoodsSupplier],ic.[PaymentType],ic.[PayType],ic.[Comment],ic.[OtherID],ic.[Abr]

    declare @id int, @count int
    select @count = COUNT(*)
    from [STOR].T_IMPORT_CONTRACT ic
    		join [STOR].T_IMPORT_ELEMENTS ie on ie.ImportElementsID = ic.ImportElementsID
    where ie.Error is null and ie.Users = @UserID and not exists(select * from [STOR].[T_CONTRACT_ELEMENTS] ce where ce.LotNumber = ic.LotNumber)
    exec @id = [vclib].sp_GenerateNumber 1936, 'CONTRACT_ELEMENTS', @count

INSERT INTO [STOR].[T_CONTRACT_ELEMENTS]
           ([SupplierContract]
           ,[ContractElementsID]
           ,[Amount]
           ,[PackageType]
           ,[Price]
           ,[BillOfGoods]
           ,[Sum]
           ,[LotNumber]
		   ,[SupplierContractAbr]
		   ,[VAT])
     select
            ic.[SupplierContractID]
           ,ic.ImportElementsID
           ,ic.[Amount]
           ,ic.[PackageType]
           ,ic.[Price]
           ,ic.[BillOfGoods]
           ,ic.[Sum]
           ,@id - 1 + ie2.Ident
		   ,ic.Abr
	       ,ISNULL(ic.VAT,0)
	from [STOR].T_IMPORT_CONTRACT ic
			join [STOR].T_IMPORT_ELEMENTS ie on ie.ImportElementsID = ic.ImportElementsID
            join
            (
                select ie1.ImportElementsID, ROW_NUMBER() over (order by ie1.ImportElementsID) as Ident
                from [STOR].T_IMPORT_ELEMENTS ie1 where ie1.Error is null and ie1.Users = @UserID and type_ = @IdEnt
            ) ie2 on ie2.ImportElementsID = ie.ImportElementsID
			   where ie.Error is null and ie.Users = @UserID and not exists(select * from [STOR].[T_CONTRACT_ELEMENTS] ce where ce.LotNumber = ic.LotNumber)

 --INSERT [STOR].T_CONTRACT_STORAGE_LINK(ContractStorageLinkID,Storage,SupplierContract) 
	--SELECT newid(),
	--	s.StorageID
	--	,sc.SupplierContractID
	--FROM [STOR].[T_SUPPLIER_CONTRACT] sc
	--	join [STOR].T_STORAGE s on 1=1

delete ic
from [STOR].T_IMPORT_CONTRACT ic
join [STOR].T_IMPORT_ELEMENTS ie on ie.ImportElementsID = ic.ImportElementsID
			   where ie.Error is null and ie.Users = @UserID
delete [STOR].T_IMPORT_ELEMENTS  where Error is null and type_ = @IdEnt and Users = @UserID
END