/*
	功能: 插入水表運行記錄
*/
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
-- 檢驗各輸入?yún)?shù)的合法性
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表中插入收費記錄時發(fā)生錯誤.'
	return -1
end

/*
	沖減用戶的欠費
*/
-- 計算能夠沖減的費用總量
declare @balance dec(10,4)
declare @fee_sum_own dec(10,4)
-- 檢索用戶的余額數(shù)據(jù)
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