维护水表的存储过程.sql

水费管理系统

/*
	功能：增加水表
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_add_wm')
	drop proc wsp_add_wm
go

create proc wsp_add_wm(
	@cust_code varchar(20), 
	@type_code varchar(20), 
	@fee_std_code varchar(10),
	@pos_code varchar(20),
	@stat_code varchar(20),
	@parent_wm_code varchar(20),
	@init_value decimal(10,4),
	@max_value decimal(10,4),
	@oper_code varchar(20),
	@fee_cycle_cnt tinyint = 1,
	@last_read_time datetime = null,
	@c_time datetime = null,
	@u_time datetime = null
)
as
/*验证参数的有效性*/
declare @row_cnt int
declare @error_var int, @rowcount_var int

-- 验证用户的存在性
select @row_cnt = count(*)
	from cust
	where cust_code = @cust_code

select @error_var = @@error, @rowcount_var = @@rowcount
if @error_var <> 0
begin
	print '读取用户资料时发生错误.'
	return -1
end

if @row_cnt = 0
begin
	print '不存在指定的用户代码: ' + @cust_code + '.'
	return -1
end

if @row_cnt > 1
begin
	print '用户资料发生完整性错误. 读取到的用户数资料数大于1, 用户数为: ' + @row_cnt + '.'
	return -1
end

-- 验证用户类型的存在性
if dbo.uf_is_valid_code(@type_code, 'WM_TYPE') = 0
begin
	print '水表类型代码不正确. 水表类型代码为: '  + @type_code + '.'
	return -1
end

-- 验证用水表位置的存在性
if dbo.uf_is_valid_code(@pos_code, 'WM_POS') = 0
begin
	print '水表位置代码不正确.水表位置代码为: '  + @pos_code + '.'
	return -1
end

-- 验证水表状态的存在性
if dbo.uf_is_valid_code(@stat_code, 'WM_STAT') = 0
begin
	print '水表状态代码不正确.水表状态代码为: '  + @stat_code + '.'
	return -1
end

-- 验证资费标准的存在性
select @row_cnt = count(*)
	from fee_std
	where fee_std_code = @fee_std_code

select @error_var = @@error, @rowcount_var = @@rowcount
if @error_var <> 0
begin
	print '水表的资费标准代码不正确. 资费标准代码为: ' + @fee_std_code + '.'
	return -1
end

-- 验证收费周期数，范围： 1 - 12个月
if @fee_cycle_cnt < 1 or @fee_cycle_cnt > 12
begin
	print '收费周期个数不正确. 范围为：1 - 12 个月. 周期个数为：' + convert(varchar(20),@fee_cycle_cnt) + '.'
	return -1
end

-- 验证水表初始值与最大值。约束条件：①初始值 <= 最大值；②初始值>=0 and 最大值 > 0
if @init_value < 0 or @max_value < 0 or @init_value > @max_value
begin
	print '水表初始值或最大值不正确：①初始值 <= 最大值；②初始值>=0 and 最大值 > 0. 初始值为：' + convert(varchar(20), @init_value) + '. 最大值为：' + convert(varchar(20), @max_value) + '.'
	return -1
end 

-- 查检@c_time, @u_time
if @c_time is null
	set @c_time = getdate()

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


/*插入水表数据*/
-- 生成水表序号

-- 开始事务
declare @tran_cnt int
select @tran_cnt = @@trancount
if @tran_cnt = 0 
begin
	begin tran mytran
end 
else
begin
	save tran mytran
end

declare @wm_seq int
exec @wm_seq = get_nextid 'water_meter', @cust_code
if @wm_seq <= 0 or @wm_seq > 255
begin
	print '生成水表序号错误.水表序号范围为:1 - 255. 序号为：' + convert(varchar(20), @wm_seq)
	rollback tran mytran
	return -1
end
insert into water_meter(
		cust_code, wm_seq, 	type_code, 	pos_code, fee_std_code, 	stat_code, 
		init_value, max_value, fee_cycle_cnt, last_read_time, 
		c_time, u_time)
	values(
		@cust_code, @wm_seq, @type_code, @pos_code, @fee_std_code, @stat_code,
		@init_value, @max_value, @fee_cycle_cnt, @last_read_time,
		@c_time, @u_time)

select @error_var = @@error, @rowcount_var = @@rowcount
if @error_var <> 0
begin
	print '写入水表资料时发生错误. 错误代码: ' + convert(varchar(20), @error_var) 
	rollback tran mytran
	return -1
end

/*向wm_run_log表中插入水表运行基数记录。base_value_flg = 1 */
insert into wm_run_log(wm_code, read_time, fee_flg, value, base_value_flg, process_flg, tc_code)
		select wm_code, getdate(), 0, init_value, 1, 0, @oper_code
			from water_meter 
			where cust_code = @cust_code 
					and wm_seq = @wm_seq
select @error_var = @@error, @rowcount_var = @@rowcount
if @error_var <> 0
begin
	print '写入水表运行记录时发生错误. 错误代码: ' + convert(varchar(20), @error_var)
	rollback tran mytran
	return -1
end

commit
return 0
go


/*
	功能：修改水表水表资料
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_modi_wm')
	drop proc wsp_modi_wm
go

create proc wsp_modi_wm(
	@wm_code varchar(20),
	@type_code varchar(20), 
	@fee_std_code varchar(10),
	@pos_code varchar(20),
	@init_value decimal(10,4),
	@max_value decimal(10,4),
	@fee_cycle_cnt tinyint = 1
)
as
/*验证参数的有效性*/
declare @row_cnt int
declare @error_var int, @rowcount_var int

-- 验证用户类型的存在性
if dbo.uf_is_valid_code(@type_code, 'WM_TYPE') = 0
begin
	print '水表类型代码不正确. 水表类型代码为: '  + @type_code + '.'
	return -1
end

-- 验证用水表位置的存在性
if dbo.uf_is_valid_code(@pos_code, 'WM_POS') = 0
begin
	print '水表位置代码不正确.水表位置代码为: '  + @pos_code + '.'
	return -1
end

-- 验证资费标准的存在性
select @row_cnt = count(*)
	from fee_std
	where fee_std_code = @fee_std_code

select @error_var = @@error, @rowcount_var = @@rowcount
if @error_var <> 0
begin
	print '水表的资费标准代码不正确. 资费标准代码为: ' + @fee_std_code + '.'
	return -1
end

-- 验证收费周期数，范围： 1 - 12个月
if @fee_cycle_cnt < 1 or @fee_cycle_cnt > 12
begin
	print '收费周期个数不正确. 范围为：1 - 12 个月. 周期个数为：' + convert(varchar(20),@fee_cycle_cnt) + '.'
	return -1
end

-- 验证水表初始值与最大值。约束条件：①初始值 <= 最大值；②初始值>=0 and 最大值 > 0
if @init_value < 0 or @max_value < 0 or @init_value > @max_value
begin
	print '水表初始值或最大值不正确：①初始值 <= 最大值；②初始值>=0 and 最大值 > 0. 初始值为：' + convert(varchar(20), @init_value) + '. 最大值为：' + convert(varchar(20), @max_value) + '.'
	return -1
end 


-- 读取修改以前的资料,和修改后的资料比较,找出修改的部分
declare  @old_record table (
	type_code varchar(20) not null, 
	fee_std_code varchar(10) not null,
	pos_code varchar(20) not null, -- 从code_name表中取得,code_type='WM_POS'
	init_value decimal(10,4) not null, -- 此初始值对于新增加的客户是必须的，会自动加入到水表运行的初始记录当中;如果没有录入初始值,必须通过其它方式录入
	max_value decimal(10,4) not null, -- 最大量度值，用于水表计数翻转时用水量的计算

	fee_cycle_cnt tinyint default 1) --　水表抄表时间间隔

insert into @old_record(
		type_code, fee_std_code, pos_code, init_value, max_value, fee_cycle_cnt
		)
	select type_code, fee_std_code, pos_code, init_value, max_value, fee_cycle_cnt
		from water_meter 
		where wm_code = @wm_code


select @error_var = @@error, @rowcount_var = @@rowcount
if @error_var <> 0
begin
	print '读取用户资料时发生错误.'
	return -1
end

if @rowcount_var <> 1
begin
	print '读取的水表资料不唯一.'  + convert(varchar(20), @rowcount_var)
	return -1
end

-- 开始事务
declare @tran_cnt int
select @tran_cnt = @@trancount
if @tran_cnt = 0 
begin
	begin tran mytran
end 
else
begin
	save tran mytran
end

-- 记录修改日志
insert into water_meter_log(cust_code, wm_seq, wm_code, stat_code,
		type_code, fee_std_code, pos_code, init_value, max_value, fee_cycle_cnt
		) 
		select cust_code, wm_seq, wm_code, stat_code, type_code, fee_std_code, pos_code, init_value, max_value, fee_cycle_cnt 
			from water_meter 
			where wm_code = @wm_code

if @@error <> 0
begin
	rollback tran mytran
	return -1
end

insert into @old_record select distinct * from @old_record

if (select count(*) from @old_record) = 3
begin
	print '水表资料没有发生变化.'
	return 0
end

-- 更新水表运行基础值
declare @base_value_flg tinyint, @value decimal(10,4), @id int
select @base_value_flg = base_value_flg, @value = value, @id = id
	from wm_run_log
	where wm_code = @wm_code 
			and c_time = (select max(c_time) from wm_run_log where wm_code = @wm_code)
select @error_var = @@error, @rowcount_var = @@rowcount

if @error_var <> 0
begin
	rollback tran mytran
	print '读取水表运行记录时发生错误.'
	return -1
end

if @rowcount_var <> 1
begin
	rollback tran mytran
	print '读取的水表运行记录数不唯一. 记录数为:'  + convert(varchar(20), @rowcount_var) + '.'
	return -1
end

if @value <> @init_value
	update wm_run_log 
		set value = @init_value
			where wm_code = @wm_code 
				and c_time = (select max(c_time) from wm_run_log where wm_code = @wm_code)
if @error_var <> 0
begin
	rollback tran mytran
	print '更新水表运行记录时发生错误.'
	return -1
end

if @rowcount_var <> 1
begin
	rollback tran mytran
	print '更新的水表运行记录数不唯一. 记录数为:'  + convert(varchar(20), @rowcount_var) + '.'
	return -1
end

-- 修改水表资料
update water_meter 
	set type_code = @type_code, fee_std_code = @fee_std_code, 
		pos_code = @pos_code, 
		init_value = @init_value, max_value = @max_value, 
		fee_cycle_cnt = @fee_cycle_cnt
	where wm_code = @wm_code

if @@error <> 0
begin
	rollback tran mytran
	return -1
end

return 0
go

/*
	功能: 注销水表
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_drop_wm')
	drop proc wsp_drop_wm
go

create proc wsp_drop_wm(@wm_code varchar(20), @tc_code varchar(20), @last_read_value decimal(10,4), @last_read_time datetime)
as
-- 检查水表的存在性
declare @stat_code varchar(20)
declare @error_var int, @rowcount_var int
select @stat_code = stat_code 
	from water_meter
	where wm_code = @wm_code

select @error_var = @@error, @rowcount_var = @@rowcount
if @error_var <> 0
begin
	print '读取水表资料时发生错误. 错误代码：' + convert(varchar(20), @error_var) +'.'
	return -1
end

if @rowcount_var <> 1
begin
	print '没有读取到水表资料.'
	return -1
end

-- 检查水表状态
if @stat_code = 'DROP'
begin
	print '水表已经注销.'
	return -1
end

-- 检查此水表是否是总表
declare @row_cnt int
select @row_cnt = count(*) 
	from water_meter
	where parent_wm_code = @wm_code

select @error_var = @@error, @rowcount_var = @@rowcount
if @error_var <> 0
begin
	print '检查水表是否为总表时发生错误. 错误代码：' + convert(varchar(20), @error_var) +'.'
	rollback tran mytran
	return -1
end

if @row_cnt <> 0
begin
	print '此水表为总表,而且还有未注销的子表. 不能删除.'
	return -1
end

if @last_read_value <0
begin
	print '水表抄表值有误!'
	return -1
end

-- 抄表时间必须大于或等于最后一次记录的时间
declare @last_log_time datetime

select @last_log_time = max(read_time) 
	from wm_run_log 
	where wm_code = @wm_code

if @last_log_time is null
begin
	print '系统数据错误，没有记录的最后运行时间!'
	return -1
end

if @last_log_time > @last_read_time
begin
	print '抄表时间有误!'
	return -1
end

/*注销水表*/
-- 开始事务
declare @tran_cnt int
select @tran_cnt = @@trancount
if @tran_cnt = 0 
begin
	begin tran mytran
end 
else
begin
	save tran mytran
end
-- 记录最后一次抄表值
insert into wm_run_log(wm_code, read_time, fee_flg, value, base_value_flg, process_flg, tc_code)
	values(@wm_code, @last_read_time, 0, @last_read_value, 0, 0, @tc_code)

select @error_var = @@error, @rowcount_var = @@rowcount
if @error_var <> 0
begin
	print '记录水表运行记录时发生错误. 错误代码：' + convert(varchar(20), @error_var) +'.'
	rollback tran mytran
	return -1
end

-- 记录修改日志
insert into water_meter_log(cust_code, wm_seq, wm_code, stat_code,
		type_code, fee_std_code, pos_code, init_value, max_value, fee_cycle_cnt
		) 
		select cust_code, wm_seq, wm_code, stat_code, type_code, fee_std_code, pos_code, init_value, max_value, fee_cycle_cnt 
			from water_meter 
			where wm_code = @wm_code

select @error_var = @@error, @rowcount_var = @@rowcount
if @error_var <> 0
begin
	print '记录水表修改日志时发生错误. 错误代码：' + convert(varchar(20), @error_var) +'.'
	rollback tran mytran
	return -1
end


--　更新水表状态
update water_meter set stat_code = 'DROP' where wm_code = @wm_code

select @error_var = @@error, @rowcount_var = @@rowcount
if @error_var <> 0
begin
	print '修改水表状态时发生错误. 错误代码：' + convert(varchar(20), @error_var) +'.'
	rollback tran mytran
	return -1
end

commit

go



/*
	查询水表运行记录：存储过程
*/
if exists(select * from sysobjects where type='V' and name = 'v_wm_log')
	drop view v_wm_log
go

create view v_wm_log
as
select w.cust_code, l.* 
	from water_meter w, wm_run_log l 
	where w.wm_code = l.wm_code
go

if exists(select * from sysobjects where type = 'P' and name = 'wsp_wm_run_log')
	drop proc wsp_wm_run_log
go

create proc wsp_wm_run_log(@cust_code varchar(20))
as
select * from v_wm_log where cust_code = @cust_code
go

if exists(select * from sysobjects where type = 'P' and name = 'wsp_wm_run_log_by_time')
	drop proc wsp_wm_run_log_by_time
go

create proc wsp_wm_run_log_by_time(@cust_code varchar(20), @start_time datetime, @end_time datetime)
as
select * 
	from v_wm_log 
	where cust_code = @cust_code
		and read_time between @start_time and @end_time
go