插入各种记录.sql

水费管理系统

/*
	功能: 插入水表运行记录
*/
if exists(select * from sysobjects where name='wsp_insert_wm_run_log' and type='P')
	drop proc wsp_insert_wm_run_log
go
create proc wsp_insert_wm_run_log(
	@wm_code varchar(20), 
	@fee_flg tinyint, 
	@value dec(10,4), 
	@base_value_flg tinyint, 
	@process_flg tinyint, 
	@tc_code varchar(20),
	@read_time datetime)
as
-- 检查水表的有效性
if @wm_code is null or ltrim(rtrim(@wm_code)) = ''
	or not exists(select * from water_meter where wm_code = @wm_code)
	return -1


if @fee_flg not in (0, 1)
	return -1

if @value is null or @value < 0
	return -1

if @base_value_flg is null or  @base_value_flg not in (0, 1)
	return -1

if @process_flg is null or  @process_flg not in (0, 1)
	return -1

if @read_time is null
	set @read_time = getdate()

if @tc_code is null or not exists(select * from toll_collector where tc_code = @tc_code)
	return -1

-- 插入记录
insert into wm_run_log(wm_code, read_time, fee_flg, value, base_value_flg, process_flg, tc_code)
	values(@wm_code, @read_time, @fee_flg, @value, @base_value_flg, @process_flg, @tc_code)
return 0
go


/*
	功能: 插入抄表单生成记录
*/
if exists(select * from sysobjects where name='wsp_insert_rs_log' and type='P')
	drop proc wsp_insert_rs_log
go
create proc wsp_insert_rs_log(
	@water_area_code varchar(20),
	@gen_time datetime,
	@gen_space int,
	@oper_code varchar(20),
	@c_time datetime)
as
if @water_area_code is null or not exists(select * from water_area where water_area_code = @water_area_code)
	return -1
if @gen_time is null
	set @gen_time = getdate()
if @gen_space is null
	set @gen_space = 0
if @oper_code is null or not exists (select * from oper where oper_code = @oper_code)
	return -1
if @c_time is null
	set @c_time = getdate()
insert into rs_gen_log(
	water_area_code,
	gen_time,
	gen_space,
	oper_code,
	c_time)
values(@water_area_code, @gen_time, @gen_space, @oper_code, @c_time)

go

/*
	功能: 插入收费记录
	示例: wsp_insert_cust_fee_log 'DY0001', 20, 'TC', 'admin', '2004-07-29'
*/
if exists(select * from sysobjects where name='wsp_insert_cust_fee_log' and type='P')
	drop proc wsp_insert_cust_fee_log
go
create proc wsp_insert_cust_fee_log(
	@cust_code varchar(20), 
	@fee_sum dec(10,4),
	@fee_type varchar(20),
	@oper_code varchar(20),
	@c_time datetime)
as
-- 检验各输入参数的合法性
if @cust_code is null 
	or not exists( select * from cust where cust_code = @cust_code)
begin
	print '用户代码不合法.'
	return -1
end

if @fee_sum is null or @fee_sum <= 0
begin
	print '用户费用值有误. 必须大于或等于0'
	return -1
end

if @fee_type is null or @fee_type not in ('WIN', 'TC', 'BANK')
begin
	print '用户收费类别不合法.'
	return -1
end

if @oper_code is null
	or not exists ( select * from oper where oper_code = @oper_code)
begin
	print '收费员代码有误.'
	return -1
end

if @c_time is null
	set @c_time = getdate()

begin tran
-- 插入收费记录
insert into cust_fee_log(
	cust_code,
	fee_sum,
	fee_type,
	fee_time,
	oper_code,
	c_time)
values(
	@cust_code,
	@fee_sum,
	@fee_type,
	@c_time,
	@oper_code,
	@c_time)

if @@error <> 0 
begin
	rollback
	print '向cust_fee_log表中插入收费记录时发生错误.'
	return -1
end

/*
	冲减用户的欠费
*/
-- 计算能够冲减的费用总量
declare @balance dec(10,4)
declare @fee_sum_own dec(10,4)
-- 检索用户的余额数据
select @balance = balance 
	from cust_balance
	where cust_code = @cust_code

if @@error <> 0
begin
	print '检索用户余额错误.'
	return -1
end

declare @yf table(ym varchar(20), fee_sum dec(10, 4))
declare @cz table(dt varchar(20))

insert into @yf 
	select convert(varchar(20),fee_time,112) ym, sum(all fee_sum) fee_sum
		from cust_fee 
		where cust_code = @cust_code
			  and fee_stat = 0
		group by  convert(varchar(20),fee_time,112)
		order by ym

set @balance = @fee_sum + @balance
-- 计算用户的欠费总和
select @fee_sum_own = sum(fee_sum) from @yf

-- 判断是否能够全部冲减
if @balance >= @fee_sum_own
begin
	-- 进行全部冲减
	update cust_fee 
		set fee_stat = 1 
		where cust_code = @cust_code 
			and fee_stat = 0

	set @balance = @balance - @fee_sum_own
end
else
begin
	-- 进行部分冲减
	declare @f dec(10,4), @f_s dec(10,4), @dt varchar(20)
	set @f_s = 0

	declare cur_fee cursor for select ym, fee_sum from @yf
	open cur_fee
	fetch from cur_fee into @dt, @f 
	while @@fetch_status = 0
	begin
		set @f_s = @f_s + @f
		if @f_s > @balance
		begin
			break
		end
		insert into @cz values(@dt)
		fetch from cur_fee into @dt, @f 
	end

	update cust_fee 
		set fee_stat = 1 
		where cust_code = @cust_code
		and fee_stat = 0 
		and convert(varchar(20),fee_time,112) 
		in (select dt from @cz)

	close cur_fee
	deallocate cur_fee

	set @balance = @balance - @f_s
end

-- 更新cust_balance表
update cust_balance 
	set balance = @balance 
	where cust_code = @cust_code
commit
return 0
go