生成包费用户数据.sql

水费管理系统

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

/*
	大于等于28天
*/
create proc wsp_gen_fixed_fee(@oper_code varchar(20), @days int)
as
select cust_code, wm_code, 
		getdate() read_time, 
		0 fee_flg, 
		max_value value, 
		0 base_value_flg, 
		0 process_flg, 
		@oper_code oper_code, 
		getdate() c_time,
		fee_std_code


	into #fixed_fee_rec
	from v_last_read_rec
	where pos_code = 'VM_POS'
		and datediff(day,read_time, getdate()) >= @days

-- 向wm_run_log记录运行记录
insert into wm_run_log(
	wm_code,
	read_time,
	fee_flg,
	value,
	base_value_flg,
	process_flg,
	tc_code,
	c_time
	)
select wm_code, read_time, fee_flg, value, base_value_flg, process_flg, oper_code, c_time 
	from #fixed_fee_rec

-- 向cust_fee中记录运行记录
insert into cust_fee(
		cust_code,
		fee_item_name,
		fee_sum,
		fee_time,
		fee_stat,
		c_time)

select cust_code, wm_code, value * p.unit_price, r.read_time, r.fee_flg, c_time 
	from #fixed_fee_rec r, v_fee_price p 
	where r.fee_std_code = p.fee_std_code
return 0
go