@nyear integer,
@nmonth integer
)
as
declare @firstday t_date;
declare @lastday t_date;
declare @iemptypeid integer;
declare @idepartmentid integer;
declare @fbaseamt t_amount;
declare @ftax t_amount;
declare @icount integer;
declare @icount2 integer;
declare @ifreeid integer;
declare @idebitsubid integer;
declare @icreditsubid integer;
declare @iddepartmentid integer;
declare @icdepartmentid integer;
declare @idempid integer;
declare @icempid integer;
declare @idprojectid integer;
declare @icprojectid integer;
declare @idgoodsid integer;
declare @icgoodsid integer;
declare @idtraderid integer;
declare @ictraderid integer;
declare @local_tab table(emptypeid integer,
departmentid integer) ;
declare @temp_feebase table(freeid integer not null,
emptypeid integer,
departmentid integer,
debitsubid integer,
creditsubid integer,
ddepartmentid integer,
cdepartmentid integer,
dempid integer,
cempid integer,
dprojectid integer,
cprojectid integer,
dgoodsid integer,
cgoodsid integer,
dtraderid integer,
ctraderid integer)
begin
execute ag_getyearmonthrange @nyear,@nmonth,@firstday output,@lastday output;
insert into @local_tab
select emptypeid,departmentid
from aw_wgcalcfee
where freeid=1
declare aw_fillwgcalcfee_cursor cursor for
select *
from @local_tab
open aw_fillwgcalcfee_cursor
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
while @@fetch_status = 0
begin
select @icount2=count(empid)
from l_employ
where [email protected] and [email protected]
and (closed=0 or (closed=1 and diminishedday>[email protected] and diminishedday<[email protected]))
if (@icount2=0)
begin
delete from aw_wgcalcfee where [email protected] and [email protected]
and [email protected] and [email protected];
end
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
end
close aw_fillwgcalcfee_cursor
-- deallocate aw_fillwgcalcfee_cursor
delete from @local_tab
insert into @temp_feebase
select freeid,emptypeid,departmentid,debitsubid,creditsubid,ddepartmentid,cdepartmentid,
dempid,cempid,dprojectid,cprojectid,dgoodsid,cgoodsid,dtraderid,ctraderid
from aw_wgfeebase
where freeid<4
declare aw_feebase_cursor cursor for
select *
from @temp_feebase
open aw_feebase_cursor
fetch next from aw_feebase_cursor
into @ifreeid,@iemptypeid,@idepartmentid,@idebitsubid,@icreditsubid,@iddepartmentid,@icdepartmentid,
@idempid,@icempid,@idprojectid,@icprojectid,@idgoodsid,@icgoodsid,@idtraderid,@ictraderid
while @@fetch_status = 0
begin
select @icount=count(nyear)
from aw_wgcalcfee
where [email protected] and [email protected] and [email protected]
and [email protected] and [email protected]
select @icount2=count(empid)
from l_employ
where [email protected] and [email protected]
and (closed=0 or (closed=1 and diminishedday>[email protected] and diminishedday<[email protected]))
if ((@icount=0) and (@icount2>0))
begin
insert into aw_wgcalcfee (nyear,nmonth,freeid,emptypeid,departmentid,debitsubid,
creditsubid,ddepartmentid,cdepartmentid,dempid,cempid,
dprojectid,cprojectid,dgoodsid,cgoodsid,dtraderid,ctraderid)
values (@nyear,@nmonth,@ifreeid,@iemptypeid,@idepartmentid,@idebitsubid,@icreditsubid,
@iddepartmentid,@icdepartmentid,@idempid,@icempid,@idprojectid,
@icprojectid,@idgoodsid,@icgoodsid,@idtraderid,@ictraderid);
end
fetch next from aw_feebase_cursor
into @ifreeid,@iemptypeid,@idepartmentid,@idebitsubid,@icreditsubid,@iddepartmentid,@icdepartmentid,
@idempid,@icempid,@idprojectid,@icprojectid,@idgoodsid,@icgoodsid,@idtraderid,@ictraderid
end
close aw_feebase_cursor
deallocate aw_feebase_cursor
select @ftax=datavalue from am_sysparam where paramid=612;
insert into @local_tab
select emptypeid,departmentid from aw_wgcalcfee
where [email protected] and [email protected] and freeid=1
open aw_fillwgcalcfee_cursor
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
while @@fetch_status = 0
begin
select @fbaseamt=sum(feebaseamt) from aw_monthwagefix
where empid in (select empid from l_employ
where [email protected]
and [email protected])
and [email protected] and [email protected]
update aw_wgcalcfee set [email protected],[email protected],amount=dbo.fn_round2(@[email protected]/100)
where [email protected] and [email protected] and freeid=1
and [email protected] and [email protected];
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
end
close aw_fillwgcalcfee_cursor
delete from @local_tab
select @ftax=datavalue from am_sysparam where paramid=613;
insert into @local_tab
select emptypeid,departmentid from aw_wgcalcfee
where [email protected] and [email protected] and freeid=2
open aw_fillwgcalcfee_cursor
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
while @@fetch_status = 0
begin
select @fbaseamt=sum(feebaseamt) from aw_monthwagefix
where empid in (select empid from l_employ
where [email protected]
and [email protected])
and [email protected] and [email protected]
update aw_wgcalcfee set [email protected],[email protected],amount=dbo.fn_round2(@[email protected]/100)
where [email protected] and [email protected] and freeid=2
and [email protected] and [email protected];
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
end
close aw_fillwgcalcfee_cursor
delete from @local_tab
select @ftax=datavalue from am_sysparam where paramid=614;
insert into @local_tab
select emptypeid,departmentid from aw_wgcalcfee
where [email protected] and [email protected] and freeid=3
open aw_fillwgcalcfee_cursor
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
while @@fetch_status = 0
begin
select @fbaseamt=sum(feebaseamt) from aw_monthwagefix
where empid in (select empid from l_employ
where [email protected]
and [email protected])
and [email protected] and [email protected]
update aw_wgcalcfee set [email protected],[email protected],amount=dbo.fn_round2(@[email protected]/100)
where [email protected] and [email protected] and freeid=3
and [email protected] and [email protected];
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
end
close aw_fillwgcalcfee_cursor
delete from @local_tab
deallocate aw_fillwgcalcfee_cursor
end
--www.52mvc.com