发表于 2011-10-24 14:35 IP属地:未知
create table #temp_string_list
(
number integer null,
describe varchar(500) null,
value1 varchar(100) null,
value2 varchar(100) null,
value3 varchar(100) null,
value4 varchar(100) null,
value5 varchar(100) null,
value6 varchar(100) null,
discount integer null,
discounttype integer null,
discountamount decimal(19,4) null
)
--判断记帐金额是否为0,为0不作处理
if @amount=0
begin
select @returncode=0
goto program_end
end
--判断该流水是否已经存在
if exists(select 1 from m_card_bonus_list where streamnumber=@cardid)
begin
select @returncode=0
goto program_end
end
select @errmsg=''
if @productcount<=0
begin
select @errmsg=@errmsg+'|'+'11064'
end
--调用拆分的存储过程对变量进行赋值
if @paramer is null or rtrim(@paramer)=''
begin
select @inputstring=''
select @errmsg=@errmsg+'|'+'11063'
end
else
begin
select @inputstring=@paramer
end
select @inputstring = @inputstring + @firstsplit
select @sequence=0
while 1=1
begin
select @pos = PATINDEX( '%'+ @firstsplit+'%' , @inputstring )
if @pos = 0 break
select @sequence=@sequence+1
select @tmpstring = SUBSTRING( @inputstring , 1 , @pos - 1)
insert into #temp_string_list(number,describe) values(@sequence,@tmpstring)
select @rowcount=0
select @tmpstring = @tmpstring + @secondsplit
while 1=1
begin
select @rowcount=@rowcount+1
select @pos1 = PATINDEX( '%'+ @secondsplit+'%' , @tmpstring )
if @pos1 = 0 break
select @tmpstring1 = SUBSTRING( @tmpstring , 1 , @pos1 - 1)
if @rowcount=1
begin
update #temp_string_list set value1=@tmpstring1 where number=@sequence
end
if @rowcount=2
begin
update #temp_string_list set value2=@tmpstring1 where number=@sequence
end
if @rowcount=3 update #temp_string_list set value3=@tmpstring1 where number=@sequence
if @rowcount=4 update #temp_string_list set value4=@tmpstring1 where number=@sequence
if @rowcount=5
begin
update #temp_string_list set value5=@tmpstring1 where number=@sequence
end
select @tmpstring = SUBSTRING( @tmpstring , @pos1 + 1 , 200)
end
--执行sql语句
select @inputstring = SUBSTRING( @inputstring , @pos + 1 , 200)
end
--处理结果
update #temp_string_list
set value6=code
from #temp_string_list a ,c_product b
where convert(integer,value1)=style and convert(integer,value2)=mappingcode
--大类、小类、金额、数量、单价
--判断是否有单价×数量<>金额的数据存在
if exists (select 1 from #temp_string_list
where convert(decimal(19,2),value3) -
round(convert(decimal(19,3),value4)*
convert(decimal(19,3),value5),2) <>0)
begin
select @errmsg=@errmsg+'|'+'11064'
end
select @nowcount=count(value1),@totalamount=sum(convert(decimal(19,2),value3))
from #temp_string_list
where value6 is not null
if @totalamount<>@amount
begin
select @errmsg=@errmsg+'|'+'11064'
end
if @nowcount<>@productcount
begin
select @errmsg=@errmsg+'|'+'11065'
end
-- delete from #temp_string_list where value1<>'01'
--记录明细
insert into m_card_bonus_list
(streamnumber,style,oilamount,oil,oilprice,oiloldprice,thisboutamount,amount,discountamount,remark,remark1)
select @cardid,3,convert(decimal(19,3),value4),value6,convert(decimal(19,3),value5),convert(decimal(19,3),value5),convert(decimal(19,2),value3),convert(decimal(19,2),value3),0,@errmsg,convert(varchar(1),convert(integer,value1))
from #temp_string_list
/*
--记录明细
insert into m_card_bonus_list
(streamnumber,style,oilamount,oil,oilprice,thisboutamount,amount,discountamount,remark)
select @cardid,3,convert(decimal(19,3),value4),value6,convert(decimal(19,3),value5),convert(decimal(19,2),value3),round(@amount,2),discountamount,@errmsg
from #temp_string_list
*/
select @returncode=0
program_end:
drop table #temp_string_list
select @returncode,@returnmessage,rtrim(convert(varchar(10),@cardid))
return @returncode
END