set @cout_i=1
set @Yf_filed=''
set @updatefiled=''
set @updateTatolHl='Sum_hl = '
set @updateTatolDs='Sum_ds= '
set @updateTatolXj='Sum_xj= '
set @nf=''
set @field_Names=''
--生成字段
if month(@Begin_Date)=1
begin
while @cout_i<=12
begin --取 year(@Begin_Date)+[1..12]
if @cout_i<10
begin
set @yf='0'+convert(char,@cout_i)
end
else
begin
set @yf=rtrim(convert(char,@cout_i))
end
set @nf=rtrim(convert(char,year(@Begin_Date)))+@yf
set @field_Names=@field_Names+rtrim(@nf)+' and '
set @Yf_filed=rtrim(@Yf_filed)+'M'+rtrim(@nf)+'A'+' money null,'+
'M'+rtrim(@nf)+'B'+' money null,'+
'M'+rtrim(@nf)+'C'+' money null,'
set @updatefiled=rtrim(@updatefiled)+'M'+rtrim(@nf)+'C='+'isnull(M'+rtrim(@nf)+'A,0)+'+'isnull(M'+rtrim(@nf)+'B,0),'
set @updateTatolHl=rtrim(@updateTatolHl)+'+isnull(M'+rtrim(@nf)+'A,0)'
set @updateTatolDs=rtrim(@updateTatolDs)+'+isnull(M'+rtrim(@nf)+'B,0)'
set @updateTatolXj=rtrim(@updateTatolXj)+'+isnull(M'+rtrim(@nf)+'C,0)'
set @cout_i=@cout_i+1
end
end
else
begin
set @cout_i=month(@Begin_Date)
while @cout_i<=12
begin --year(@Begin_Date)+[month(@Begin_Date)..12]...
if @cout_i<10
begin
set @yf='0'+convert(char,@cout_i)
end
else
begin
set @yf=rtrim(convert(char,@cout_i))
end
set @nf=rtrim(convert(char,year(@Begin_Date)))+@yf
set @field_Names=@field_Names+rtrim(@nf)+' and '
set @Yf_filed=rtrim(@Yf_filed)+'M'+rtrim(@nf)+'A'+' money null,'+
'M'+rtrim(@nf)+'B'+' money null,'+
'M'+rtrim(@nf)+'C'+' money null,'
set @updatefiled=rtrim(@updatefiled)+'M'+rtrim(@nf)+'C='+'isnull(M'+rtrim(@nf)+'A,0)+'+'isnull(M'+rtrim(@nf)+'B,0),'
set @updateTatolHl=rtrim(@updateTatolHl)+'+isnull(M'+rtrim(@nf)+'A,0)'
set @updateTatolDs=rtrim(@updateTatolDs)+'+isnull(M'+rtrim(@nf)+'B,0)'
set @updateTatolXj=rtrim(@updateTatolXj)+'+isnull(M'+rtrim(@nf)+'C,0)'
set @cout_i=@cout_i+1
end
set @cout_i=1
while @cout_i<= month(@Begin_Date)-1
begin --取 year(@end_date)+[1..month(@Begin_Date)-1]
if @cout_i<10
begin
set @yf='0'+convert(char,@cout_i)
end
else
begin
set @yf=rtrim(convert(char,@cout_i))
end
set @nf=rtrim(convert(char,year(@begin_Date)+1))+@yf
set @field_Names=@field_Names+rtrim(@nf)+' and '
set @Yf_filed=rtrim(@Yf_filed)+'M'+rtrim(@nf)+'A'+' money null,'+
'M'+rtrim(@nf)+'B'+' money null,'+
'M'+rtrim(@nf)+'C'+' money null,'
set @updatefiled=rtrim(@updatefiled)+'M'+rtrim(@nf)+'C='+'isnull(M'+rtrim(@nf)+'A,0)+'+'isnull(M'+rtrim(@nf)+'B,0),'
set @updateTatolHl=rtrim(@updateTatolHl)+'+isnull(M'+rtrim(@nf)+'A,0)'
set @updateTatolDs=rtrim(@updateTatolDs)+'+isnull(M'+rtrim(@nf)+'B,0)'
set @updateTatolXj=rtrim(@updateTatolXj)+'+isnull(M'+rtrim(@nf)+'C,0)'
set @cout_i=@cout_i+1
end
end
While exists (select * from sysobjects where id = object_id('[dbo].['+@Filename+']') )
Begin
Select @File_I=@File_I + 1
set @Filename='Temp'+CONVERT(char, @File_I)
end --生成temp+n 表名称
declare @Field_string varchar(2000) --用于生成temp+n 表
set @Field_string=''
set @Field_string='Create Table '+rtrim(@Filename) +'(Cuno varchar(20) null,cuname varchar(20) null,'+rtrim(@Yf_filed)+'bmno varchar(20) null,bmname varchar(20) null,sum_hl money null,sum_Ds money null,sum_xj money null )';
EXEC(@Field_string)
--插入地区信息 --在发货单中发生的地区
--select @tj_bmno as bmno,(select shortname from sdnet where selfno=@tj_bmno) as bmname,selfno,shortname from sdnet where selfno like rtrim(@tj_bmno)+'_%' and flagInout='1'
set @Field_string='INSERT INTO '+ rtrim(@Filename)+' (bmno,bmname,cuno,cuname)
select '+ char(39)+rtrim(@tj_bmno)+char(39)+ ' as bmno,(select shortname from sdnet where selfno='+ char(39)+rtrim(@tj_bmno)+char(39)+') as bmname,selfno,shortname from sdnet where selfno like '+char(39)+ rtrim(@tj_bmno)+'_%'+char(39)+ ' and flagInout='+char(39)+'1'+char(39)
EXEC(@Field_string)
--生成数据游标
Declare Cur_SJ Scroll Cursor For select substring(cuselfno,1,9) as subselfno,skdate,flagInstead,sum(isnull(amt,0)) as amt from
(
select bmno,sdnet.selfno as cuselfno ,cuno,substring(convert(char,skdate,102),1,4)+substring(convert(char,skdate,102),6,2) as skdate,
amt,flagInstead from skd
left outer join sdnet
on sdnet.sdnet_no=skd.cuno where
(skd.skdate>= @begin_date and
skd.skdate<= @end_date and
len(ltrim(rtrim(skd.poster)))<>0 and
type='收款单'
)
)
lsskd where substring(cuselfno,1,6)=@tj_bmno
group by substring(cuselfno,1,9),skdate,flagInstead
declare @cuno varchar(20) , --地区编号
@skdate varchar(8), --日期
@amt money, --金额
@Instead varchar(10) --代收标志
Declare @field_update varchar(2000) --用于将数据倒入 temp+n表
set @field_update=''
open Cur_SJ
Select @cout_i=1
While (@cout_i<=@@Cursor_Rows)
Begin
FETCH ABSOLUTE @cout_i FROM Cur_SJ into @cuno,@skdate,@Instead,@amt
begin
if rtrim(@Instead)='否'
set @field_update= 'update '+ RTRIM(@Filename) +' set M'+rtrim(@skdate)+'A='+rtrim(ltrim(convert(char,@amt)))+' where cuno='+char(39)+rtrim(@cuno)+char(39)
else
set @field_update= 'update '+ RTRIM(@Filename) +' set M'+rtrim(@skdate)+'B='+rtrim(ltrim(convert(char,@amt)))+' where cuno='+char(39)+rtrim(@cuno)+char(39)
-- select @amt
-- select @Instead
EXEC(@field_update)
end
SELECT @cout_i = @cout_i +1
End
Close Cur_SJ
Deallocate Cur_SJ
set @updatefiled='update '+rtrim(@Filename)+ ' set '+ substring(rtrim(@updatefiled),1,len(rtrim(@updatefiled))-1)
--小计amt
EXEC(@updatefiled) --将数据倒入 temp+n表
set @updateTatolHl ='update '+rtrim(@Filename)+ ' set '+rtrim(@updateTatolHl)
set @updateTatolDs ='update '+rtrim(@Filename)+ ' set '+rtrim(@updateTatolDs)
set @updateTatolXj ='update '+rtrim(@Filename)+ ' set '+rtrim(+@updateTatolXj)