-- =============================================
-- Author:        (C) Vitacard
-- Create date: 2015-11-01
-- Description:    Загрзка контрактов
-- =============================================
DROP PROCEDURE IF EXISTS stor.sp_ImportContract;

CREATE PROCEDURE stor.sp_ImportContract
(
	"UserID" uuid
)
    language plpgsql
as
$$
Begin 

    declare 
      "IdEnt" integer := (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'IMPORT_CONTRACT' limit 1); 
      "idContract" integer := (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'SUPPLIER_CONTRACT' limit 1);
      "id" integer;
      "count" integer;

    Begin
        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;
        
        "count" := (select 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));

		CALL vclib.sp_GenerateNumber(1936, 'CONTRACT_ELEMENTS', "count","id");
        
         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
               ,coalesce(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 from STOR.T_IMPORT_CONTRACT ic
            using STOR.T_IMPORT_ELEMENTS ie 
        where ie.ImportElementsID = ic.ImportElementsID and ie.Error is null and ie.Users = "UserID";
        
        delete from STOR.T_IMPORT_ELEMENTS  where Error is null and type_ = "IdEnt" and Users = "UserID";
    end;
End;
$$;