CREATE proc up_fysum
@type char(1),
@date1 char(10),
@date2 char(10),
@useno int
as
begin
declare @i int
declare @u1 char(3),@u2 char(10)
declare @dname char(8) if @type = 'D'
begin
select @date1 = convert(char(10),getdate(),102)
select @date2 = convert(char(10),getdate(),102)
end
create table #temp_1 (no char(3) not null,
dname char(8) not null,
dyaopbm char(6) not null,
dyaopmc char(30) not null,
dyaopsl int not null,
dyaopdw char(2) null,
dyaopdj numeric(10,4) null,
dyaopgg char(10) null,
dsoufje1 money null,
ddate datetime not null ) delete mzbb..fysum where convert(char(10),ddate,102)>=@date1 and
convert(char(10),ddate,102)<=@date2
select @i=1
while @i <= @useno
begin select @u1 = 'c' +right('00'+rtrim(ltrim(convert(char(2),@i))),2)
select @u2 = 'c' +right('00'+rtrim(ltrim(convert(char(2),@i))),2)+'.yf b'
select @dname = (select dinname from system where duserid = @u1 ) select @i = @i + 1 exec('
insert #temp_1
select no = c.duserid,
dname=c.dinname,
a.dyaopbm,
a.dyaopmc,
a.dyaopsl,
a.dyaopdw,
a.dyaopdj,
a.dyaopgg,
a.dsoufje1,
ddate = convert(datetime,convert(char(10),b.ddate,102)+'' 10:00:00'')
from '+@u1+'.cfk a,'+@u2+' ,system c where a.dnbxlh = b.dnbxlh and
''f''+right(''00''+rtrim(ltrim(convert(char(2),a.dcaozybm))),2) = c.duserid
and a.dcaozybm=b.dcaozybm and substring(a.dyaopbm,1,1)<>''*''
and convert(char(10),b.ddate,102)>='''+@date1+'''
and convert(char(10),b.ddate,102)<='''+@date2+'''') end insert mzbb..fysum (no,dname,dyaopbm,dyaopmc,dyaopdw,dyaopdj,dyaopgg,ddate,dyaopsl,dsoufje1)
select no,dname,dyaopbm,dyaopmc,dyaopdw,dyaopdj,dyaopgg,ddate,
dyaopsl=sum(dyaopsl),dsoufje1=sum(dsoufje1)
from #temp_1
group by no,dname,dyaopbm,dyaopmc,dyaopdw,dyaopdj,dyaopgg,ddateend
@type char(1),
@date1 char(10),
@date2 char(10),
@useno int
as
begin
declare @i int
declare @u1 char(3),@u2 char(10)
declare @dname char(8) if @type = 'D'
begin
select @date1 = convert(char(10),getdate(),102)
select @date2 = convert(char(10),getdate(),102)
end
create table #temp_1 (no char(3) not null,
dname char(8) not null,
dyaopbm char(6) not null,
dyaopmc char(30) not null,
dyaopsl int not null,
dyaopdw char(2) null,
dyaopdj numeric(10,4) null,
dyaopgg char(10) null,
dsoufje1 money null,
ddate datetime not null ) delete mzbb..fysum where convert(char(10),ddate,102)>=@date1 and
convert(char(10),ddate,102)<=@date2
select @i=1
while @i <= @useno
begin select @u1 = 'c' +right('00'+rtrim(ltrim(convert(char(2),@i))),2)
select @u2 = 'c' +right('00'+rtrim(ltrim(convert(char(2),@i))),2)+'.yf b'
select @dname = (select dinname from system where duserid = @u1 ) select @i = @i + 1 exec('
insert #temp_1
select no = c.duserid,
dname=c.dinname,
a.dyaopbm,
a.dyaopmc,
a.dyaopsl,
a.dyaopdw,
a.dyaopdj,
a.dyaopgg,
a.dsoufje1,
ddate = convert(datetime,convert(char(10),b.ddate,102)+'' 10:00:00'')
from '+@u1+'.cfk a,'+@u2+' ,system c where a.dnbxlh = b.dnbxlh and
''f''+right(''00''+rtrim(ltrim(convert(char(2),a.dcaozybm))),2) = c.duserid
and a.dcaozybm=b.dcaozybm and substring(a.dyaopbm,1,1)<>''*''
and convert(char(10),b.ddate,102)>='''+@date1+'''
and convert(char(10),b.ddate,102)<='''+@date2+'''') end insert mzbb..fysum (no,dname,dyaopbm,dyaopmc,dyaopdw,dyaopdj,dyaopgg,ddate,dyaopsl,dsoufje1)
select no,dname,dyaopbm,dyaopmc,dyaopdw,dyaopdj,dyaopgg,ddate,
dyaopsl=sum(dyaopsl),dsoufje1=sum(dsoufje1)
from #temp_1
group by no,dname,dyaopbm,dyaopmc,dyaopdw,dyaopdj,dyaopgg,ddateend
exec('
insert #temp_1
select no = c.duserid,
dname=c.dinname,
a.dyaopbm,
a.dyaopmc,
a.dyaopsl,
a.dyaopdw,
a.dyaopdj,
a.dyaopgg,
a.dsoufje1,
ddate = convert(datetime,convert(char(10),b.ddate,102)+'' 10:00:00'')
from '+@u1+'.cfk a,'+@u2+' ,system c where a.dnbxlh = b.dnbxlh and
''f''+right(''00''+rtrim(ltrim(convert(char(2),a.dcaozybm))),2) = c.duserid
and a.dcaozybm=b.dcaozybm and substring(a.dyaopbm,1,1)<>''*''
and convert(char(10),b.ddate,102)>='''+@date1+'''
and convert(char(10),b.ddate,102)<='''+@date2+'''')