生成抄表单.sql

水费管理系统

/*
	功能：创建最后一次抄表记录视图
	未考虑在wm_run_log中找不到记录的情况，如果发生这种情况，则无法生成此水表的记录最后一次抄表记录。
*/
if exists(select * from sysobjects where type='V' and name = 'v_last_read_rec')
	drop view v_last_read_rec
go

create view v_last_read_rec
as
select	c.cust_code,
			c.water_area_code,
			c.cust_name,
			c.cust_type,
			c.stat,
			c.address,
			c.phone,
			c.bank_code,
			c.bank_acct_no,
			c.tax_no,
			
			w.wm_code,
			w.wm_seq,
			w.type_code,
			w.fee_std_code,
			w.pos_code,
			w.stat_code,
			w.fee_cycle_cnt,
			w.read_sheet_flg,
			w.init_value,
			w.max_value,

			l.read_time,
			l.fee_flg,
			l.value,
			l.tc_code
 
from  cust c 
		left join water_meter  w on  c.cust_code = w.cust_code
		left join wm_run_log l on w.wm_code = l.wm_code
where l.id = (select max(id)
					from wm_run_log l1
					where l1.wm_code = l.wm_code
					)
					
go

/*
	功能:生成指定区域,指定用户类别的用户抄表单

	调用示例:
	向in_set表中插入适当的记录
select * from in_set
	wsp_gen_read_sheet 0

	错误(TODO): 似乎未将记录插入到in_set_cust_code表中 
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_gen_read_sheet')
	drop proc wsp_gen_read_sheet
go

create proc wsp_gen_read_sheet(@read_sheet_flg tinyint = 0)
as

if not @read_sheet_flg between 0 and 2
	return -1

declare @sql varchar(1024), @sql1 varchar(1024), @sql2 varchar(1024), @sql3 varchar(1024)
declare @and_str varchar(20), @result varchar(512), @nsql nvarchar(4000)

-- 生成合适的条件语句
set @sql = ''
set @result =''

set @sql1 = '
select * 
	from v_last_read_rec r 
'
set @sql2 = 'water_area_code in (select value from in_set where spid = @@spid and value_type = ''AREA'') 
	and  cust_type in (select value from in_set where spid = @@spid and value_type = ''CUST_TYPE'')
	'
set @sql3 = case @read_sheet_flg when 0 then ' and read_sheet_flg = 0 ' when 1 then ' and read_sheet_flg = 1' when 2 then '' end
set @sql = @sql1 + ' where r.pos_code <> ''VM_POS'' and r.stat=''NORMAL'' and ' +@sql2 + @sql3
set @nsql = @sql

select * 
	into #read_sheet 
	from v_last_read_rec 
	where 1 = 2

insert into #read_sheet exec sp_executesql @nsql
if @@error <> 0
	return -1

update #read_sheet set read_sheet_flg = 0

set @sql1 = 'insert into read_sheet_log(
	id,
	cust_code,
	water_area_code,
	cust_name,
	address,
	phone,
	pos_code,
	fee_std_code,
	last_value,
	this_value,
	this_amt,
	price,
	fee_amt,
	print_flg,
	tc_code,
	read_time,
	wm_code
) select 	id,cust_code,
	water_area_code,
	cust_name,
	address,
	phone,
	pos_code,
	fee_std_code,
	last_value,
	this_value,
	this_amt,
	price,
	fee_amt,
	print_flg,
	tc_code,
	read_time,
	wm_code
 from read_sheet '
set @sql = @sql1 + ' where cust_code in (select cust_code from #read_sheet) ' + ' and input_flg = 0 '
set @nsql = @sql
set identity_insert read_sheet_log on
exec sp_executesql @nsql
if @@error <> 0
	return -1

set identity_insert read_sheet_log off


-- 清除未抄表的数据
set @sql1 = 'delete read_sheet '
set @sql = @sql1 + ' where cust_code in (select cust_code from #read_sheet) ' + ' and input_flg = 0 '
set @nsql = @sql
exec sp_executesql @nsql

-- 向read_sheet插入抄表单
insert into read_sheet(
	cust_code,
	water_area_code,
	cust_name,
	wm_code,
	address,
	phone,
	pos_code,
	fee_std_code,
	last_value,
	this_value,
	this_amt,
	price,
	fee_amt,
	print_flg,
	tc_code,
	read_time
)select 
	cust_code,
	water_area_code,
	cust_name,
	wm_code,
	address,
	phone,
	pos_code,
	fee_std_code,
	value, -- last_value
	null, -- this_value
	null, -- this_amt
	0, -- price
	0, -- fee_amt
	0, -- print_flg
	'', -- tc_code
	null
from #read_sheet

if @@error <> 0
	return -1

-- 更新单价
update read_sheet set price =  p.unit_price
	from v_fee_price p 
	where read_sheet.fee_std_code = p.fee_std_code

delete in_set_cust_code where spid = @@spid
insert into in_set_cust_code(cust_code, wm_code) select cust_code, wm_code from #read_sheet
if @@error <> 0
	return -1

return (select count(*) from #read_sheet)
go

/*
	功能: 回退生成的抄表单
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_rollback_read_sheet')
	drop proc wsp_rollback_read_sheet
go

create proc wsp_rollback_read_sheet
as
delete read_sheet 
	where wm_code in (select wm_code 
						from in_set_cust_code i 
						where spid = @@spid 
								and i.cust_code = read_sheet.cust_code 
								and i.wm_code = read_sheet.wm_code)
-- 将原来存在的抄表单记录插入回原来的表. from 'read_sheet_log'
set identity_insert read_sheet on

insert into read_sheet(	id,
	cust_code,
	water_area_code,
	cust_name,
	address,
	phone,
	pos_code,
	fee_std_code,
	last_value,
	this_value,
	this_amt,
	price,
	fee_amt,
	print_flg,
	tc_code,
	read_time,
	wm_code
) select 	
	id,
	cust_code,
	water_area_code,
	cust_name,
	address,
	phone,
	pos_code,
	fee_std_code,
	last_value,
	this_value,
	this_amt,
	price,
	fee_amt,
	print_flg,
	tc_code,
	read_time,
	wm_code
	from read_sheet_log
	where spid = @@spid

set identity_insert read_sheet off
delete read_sheet_log where spid = @@spid
delete in_set  where spid = @@spid and (value_type = 'AREA' or value_type = 'CUST_TYPE')
delete in_set_cust_code where spid = @@spid
return 0
go


/*
	功能: 提交生成的抄表单
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_commit_read_sheet')
	drop proc wsp_commit_read_sheet
go

create proc wsp_commit_read_sheet
as

-- 更新water_meter表的read_sheet_flg标志
update v_last_read_rec set read_sheet_flg = 1
	where wm_code in (select wm_code from in_set_cust_code where spid = @@spid)

delete read_sheet_log where spid = @@spid
delete in_set  where spid = @@spid and (value_type = 'AREA' or value_type = 'CUST_TYPE')
delete in_set_cust_code where spid = @@spid
return 0
go