临时表导入产品、采购价格

--导入产品表
insert into AgileCorp_Pro_Product(ProductId,ProductName,ProductTypeCode,Specification,Units,Price,Remark,EmployeeCode)
(select ProductCode,ProductName,'99' as a,Specification,Units,0 as b,'' as c,EmployeeName from aa)
--去除产品表重复数据
delete from AgileCorp_Pro_Product  where
InfoId not in
(select max(InfoId) from AgileCorp_Pro_Product group by ProductId)

--更新产品表人员编号
update AgileCorp_Pro_Product set EmployeeCode=AgileCorp_Pro_Employee.EmployeeCode
from AgileCorp_Pro_Product
inner join AgileCorp_Pro_Employee on AgileCorp_Pro_Product.EmployeeCode=AgileCorp_Pro_Employee.EmployeeName


/*-------------导入产品、采购价格共用部分开始--------------*/
declare @pt_s varchar(100)
declare @pt_table table(InfoId bigint identity(1,1),EnterDate Datetime,ProviderName varchar(50),EmployeeName varchar(50),
ProductCode varchar(100),ProductName varchar(500),Specification varchar(8000),Units varchar(100),Price float )
declare @pt_Count bigint
declare @pt_EnterDate  datetime
declare @pt_ProviderName  varchar(100)
declare @pt_EmployeeName  varchar(100)
declare @pt_ProductCode  varchar(100)
declare @pt_ProductName  varchar(100)
declare @pt_Specification  varchar(100)
declare @pt_Units  varchar(100)
declare @pt_Price  float
insert into @pt_table
select  EnterDate,ProviderName,EmployeeName,ProductCode,ProductName,Specification,Units,Price from aaa2015
select @pt_Count=Count(*) from @pt_table
/*-------------导入产品、采购价格共用部分结束--------------*/
/*-------------循环导入产品，重复的产品编号自动更新，不重复的自动插入开始--------------*/
while @pt_Count>0
begin
select @pt_EnterDate=EnterDate,@pt_ProviderName=ProviderName,@pt_EmployeeName=EmployeeName,
@pt_ProductCode=ProductCode,@pt_ProductName=ProductName,@pt_Specification=Specification,@pt_Units=Units,@pt_Price=Price from @pt_table where InfoId=@pt_Count
--插入产品
if exists(select * from  AgileCorp_Pro_Product where ProductId = @pt_ProductCode )
begin
    update     AgileCorp_Pro_Product set ProductName=@pt_ProductName,Specification=@pt_Specification,Units=@pt_Units,
    EmployeeCode=@pt_EmployeeName,Price=@pt_Price where ProductId = @pt_ProductCode
end
else
begin
    insert into AgileCorp_Pro_Product
(ProductId,ProductName,ProductTypeCode,Specification,Units,Price,Remark,EmployeeCode)
values (@pt_ProductCode,@pt_ProductName,'99',@pt_Specification,@pt_Units,@pt_Price,'',@pt_EmployeeName)
end

set @pt_Count=@pt_Count-1
end
/*-------------循环导入产品，重复的产品编号自动更新，不重复的自动插入结束--------------*/



/*-------------循环导入采购价格，编号自动生成开始--------------*/
while @pt_Count>0
begin
--导入采购价格表
select @pt_EnterDate=EnterDate,@pt_ProviderName=ProviderName,@pt_EmployeeName=EmployeeName,
@pt_ProductCode=ProductCode,@pt_ProductName=ProductName,@pt_Specification=Specification,@pt_Units=Units,@pt_Price=Price from @pt_table where InfoId=@pt_Count
exec App_s_spGetSequenceNumber 'L',1,@pt_s output ,0
insert into AgileCorp_Pro_ProcurementPrice
(ChargeCode,ProductCode,Specification,Units,Price,TotalAmount,Quantity,ProviderCode,EnterCode,EnterDate,InfoContent,Status)
values(@pt_s,@pt_ProductCode,@pt_Specification,@pt_Units,@pt_Price,0,0,@pt_ProviderName,'Manager',@pt_EnterDate,'',1)
set @pt_Count=@pt_Count-1
end
/*-------------循环导入采购价格，编号自动生成结束--------------*/

--更新采购价格供应商编号
update AgileCorp_Pro_ProcurementPrice set ProviderCode=AgileCorp_Pro_Provider.ProviderCode
from AgileCorp_Pro_ProcurementPrice
inner join AgileCorp_Pro_Provider on AgileCorp_Pro_ProcurementPrice.ProviderCode=AgileCorp_Pro_Provider.ProviderName