if exists(select 1 from sysobjects where name='P_PreOrderInfo')
drop Procedure P_PreOrderInfo
go
Create Procedure P_PreOrderInfo
@RefSheetID varchar(64),
@OutShopID varchar(64),
@CustomID varchar(64),
@InShopID varchar(64),
@Operator varchar(64),
@Name varchar(64),
@Tax varchar(64),
@CertType varchar(64) ,
@CertNo varchar(64) ,
@Mobile varchar(64),
@LinkMan varchar(64),
@LinkTele varchar(64),
@Address varchar(64),
@Notes varchar(64),
@GoodsID varchar(2000),
@BarCodeID varchar(2000),
@CustomPrice varchar(2000),
@PKNum varchar(2000),
@PKName varchar(2000),
@PKSpec varchar(2000)
-- ,@a varchar(8000) out
WITH ENCRYPTION
AS BEGIN
declare @SheetID char(16);
--select @OutShopID=RationShopID,@CustomID=CustomerID from shop where ID=@ShopID
select @SheetID=max(SheetID) from PreCustomerOrder0 where left(sheetID,8)=CONVERT(varchar(100), GETDATE(), 112)
if @SheetID is null
begin
set @SheetID=CONVERT(varchar(100), GETDATE(), 112)+'00000001'
end
else
begin
set @SheetID=substring(@SheetID,1,8)+ right('0000000'+convert(varchar(8),convert(int,substring(@SheetID,9,8))+1),8)
end
declare @sql varchar(2000)
set @sql='Insert Into PreCustomerOrder0( SheetID,RefSheetID,OutShopID,CustomID,InShopID,PurDate,Editor,EditDate,Operator,'+
'Notes ,[Address],Mobile,LinkTele,LinkMan,CertType,CertNo,Tax,CertName) values('''+
@SheetID+''','''+@RefSheetID+''','''+@OutShopID+''','+@CustomID+','''+@InShopID+''',getdate(),'''+@Operator+''',getdate(),'''+@Operator
+''','''+
@Notes+''','''+@Address+''','''+@Mobile+''','''+@LinkTele+''','''+@LinkMan+''','''+@CertType+''','''+
@CertNo+''','''+@Tax+''','''+@Name+''');';
declare @tempGoodsID varchar(100)
declare @tempBarCodeID varchar(100)
declare @tempCustomPrice varchar(100)
declare @tempPKNum varchar(100)
declare @tempPKName varchar(100)
declare @tempPKSpec varchar(100)
declare @sql2 varchar(8000)
set @sql2='';
while charindex(',',@GoodsID)>0
begin
set @tempGoodsID=substring(@GoodsID,1,charindex(',',@GoodsID)-1);
set @GoodsID=substring(@GoodsID, charindex(',',@GoodsID)+1, len(@GoodsID)-charindex(',',@GoodsID) );
set @tempBarCodeID=substring(@BarCodeID,1,charindex(',',@BarCodeID)-1);
set @BarCodeID=substring(@BarCodeID,charindex(',',@BarCodeID)+1,len(@BarCodeID)-charindex(',',@BarCodeID));
set @tempCustomPrice=substring(@CustomPrice,1,charindex(',',@CustomPrice)-1);
set @CustomPrice=substring(@CustomPrice,charindex(',',@CustomPrice)+1,len(@CustomPrice)-charindex(',',@CustomPrice));
set @tempPKNum=substring(@PKNum,1,charindex(',',@PKNum)-1);
set @PKNum=substring(@PKNum,charindex(',',@PKNum)+1,len(@PKNum)-charindex(',',@PKNum));
set @tempPKName=substring(@PKName,1,charindex(',',@PKName)-1);
set @PKName=substring(@PKName,charindex(',',@PKName)+1,len(@PKName)-charindex(',',@PKName));
set @tempPKSpec=substring(@PKSpec,1,charindex(',',@PKSpec)-1);
set @PKSpec=substring(@PKSpec,charindex(',',@PKSpec)+1,len(@PKSpec)-charindex(',',@PKSpec));
set @sql2=@sql2+'Insert Into PreCustomerOrderItem0( SheetID,GoodsID,BarCodeID,CustomPrice,PKNum,PKName ,PKSpec) values('''+
@SheetID+''','''+@tempGoodsID+''','''+@tempBarCodeID+''','+@tempCustomPrice+','+@tempPKNum+','''+@tempPKName+''','''+@tempPKSpec+''');';
end
--select @a= @sql+@sql2
exec(@sql+@sql2)
end
go
declare @b varchar(8000);
Exec P_PreOrderInfo N'P113201605180006','-1','-1','020L06','1','1','2', '0','3','4','1',
'5','56','7','10140102,','6914032100010,','158.00,', '1,','F,','1*1,',@b out
select @b