--个人交款统计--------------------------------------------------------
create procedure tcxt_calculate_grsf
@code char(3),
@qrq datetime,
@zrq datetime,
@gd_sfje money output,
@gd_sfbc int output,
@gd_zfje money output,
@gd_zfbc int output,
@gd_min int output,
@gd_max int output,
@gd_zfjlh varchar(200) output,
@ls_sfje money output,
@ls_sfbc int output,
@ls_zfje money output,
@ls_zfbc int output,
@ls_min int output,
@ls_max int output,
@ls_zfjlh varchar(200) output
as
declare @zfh int,@zfstr varchar(8)
--统计固定车收费情况----------------------
select @gd_sfje=isnull(sum(sfje),0) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @gd_sfbc=count(*) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @gd_zfje=isnull(sum(sfje),0) from gdcl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @gd_zfbc=count(*) from gdcl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @gd_min=isnull(min(zid),0) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @gd_max=isnull(max(zid),0) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
set @gd_zfjlh=''
declare t1 cursor for
select zid from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
and zfrq is not null order by zid
open t1
fetch next from t1 into @zfh
while @@fetch_status=0
begin
set @zfstr=convert(char,@zfh)
set @gd_zfjlh=@gd_zfjlh+' '+rtrim(@zfstr)
fetch next from t1 into @zfh
end
close t1
deallocate t1
--统计临时车收费情况----------------------
select @ls_sfje=isnull(sum(je),0) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @ls_sfbc=count(*) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @ls_zfje=isnull(sum(je),0) from lscl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @ls_zfbc=count(*) from lscl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @ls_min=isnull(min(zid),0) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @ls_max=isnull(max(zid),0) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
set @ls_zfjlh=''
declare t1 cursor for
select zid from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
and zfrq is not null order by zid
open t1
fetch next from t1 into @zfh
while @@fetch_status=0
begin
set @zfstr=convert(char,@zfh)
set @ls_zfjlh=@ls_zfjlh+' '+rtrim(@zfstr)
fetch next from t1 into @zfh
end
close t1
deallocate t1
GO
create procedure tcxt_calculate_grsf
@code char(3),
@qrq datetime,
@zrq datetime,
@gd_sfje money output,
@gd_sfbc int output,
@gd_zfje money output,
@gd_zfbc int output,
@gd_min int output,
@gd_max int output,
@gd_zfjlh varchar(200) output,
@ls_sfje money output,
@ls_sfbc int output,
@ls_zfje money output,
@ls_zfbc int output,
@ls_min int output,
@ls_max int output,
@ls_zfjlh varchar(200) output
as
declare @zfh int,@zfstr varchar(8)
--统计固定车收费情况----------------------
select @gd_sfje=isnull(sum(sfje),0) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @gd_sfbc=count(*) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @gd_zfje=isnull(sum(sfje),0) from gdcl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @gd_zfbc=count(*) from gdcl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @gd_min=isnull(min(zid),0) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @gd_max=isnull(max(zid),0) from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
set @gd_zfjlh=''
declare t1 cursor for
select zid from gdcl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
and zfrq is not null order by zid
open t1
fetch next from t1 into @zfh
while @@fetch_status=0
begin
set @zfstr=convert(char,@zfh)
set @gd_zfjlh=@gd_zfjlh+' '+rtrim(@zfstr)
fetch next from t1 into @zfh
end
close t1
deallocate t1
--统计临时车收费情况----------------------
select @ls_sfje=isnull(sum(je),0) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @ls_sfbc=count(*) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @ls_zfje=isnull(sum(je),0) from lscl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @ls_zfbc=count(*) from lscl_fkjl where skr_code=@code and zfrq>=@qrq and zfrq<=@zrq and zfrq is not null
select @ls_min=isnull(min(zid),0) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
select @ls_max=isnull(max(zid),0) from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
set @ls_zfjlh=''
declare t1 cursor for
select zid from lscl_fkjl where skr_code=@code and sksj>=@qrq and sksj<=@zrq
and zfrq is not null order by zid
open t1
fetch next from t1 into @zfh
while @@fetch_status=0
begin
set @zfstr=convert(char,@zfh)
set @ls_zfjlh=@ls_zfjlh+' '+rtrim(@zfstr)
fetch next from t1 into @zfh
end
close t1
deallocate t1
GO
create proc tcxt_in_gdcl_fkjl
@Tmh char(9),
@Sflb char(8),
@Yhlx char(8),
@Sfje money,
@Yxrqz datetime output,
@Skr_code char(3),
@sta int output
as
declare @yxq int
if @sflb='办卡费'
begin
select @yxq=yxq from sfjg where yhlx=@yhlx
set @yxrqz=dateadd(month,@yxq,getdate())
insert gdcl_fkjl(tmh,sflb,yhlx,sfje,yxrqz,skr_code,sksj,wxbz)
values (@tmh,@sflb,@yhlx,@sfje,@yxrqz,@skr_code,getdate(),'0')
update gdcl_czda set kfz=@yxrqz where tmh=@tmh --更新卡费日期止
set @sta=0
return
end
else
begin
insert gdcl_fkjl(tmh,sflb,yhlx,sfje,yxrqz,skr_code,sksj,wxbz)
values (@tmh,@sflb,@yhlx,@sfje,@yxrqz,@skr_code,getdate(),'0') if @sflb='物管费'
begin
update gdcl_czda set wyglfz=@yxrqz where tmh=@tmh --更新物业管理费日期止
end
else
begin
update gdcl_czda set cwfz=@yxrqz where tmh=@tmh --更新车位费日期止
end
set @sta=0
return
end
GO