查询各种资料.sql

水费管理系统

/*
	功能：查询用户费用信息
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_qry_cust_fee')
	drop proc wsp_qry_cust_fee
go
create proc wsp_qry_cust_fee(@cust_code varchar(20), @begin_time datetime, @end_time datetime)
as
begin

	-- 查检查询时间的有效性
	-- 进行查询
	select id, cust_code, fee_item_name, fee_sum, fee_time, fee_stat 
		from cust_fee
		where cust_code = @cust_code
				and fee_time between @begin_time and @end_time
end
go

/*
	功能: 根据时间查询用户费用信息
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_qry_cust_fee_by_time')
	drop proc wsp_qry_cust_fee_by_time
go
create proc wsp_qry_cust_fee_by_time(@cust_code varchar(20))
as
begin

	-- 查检查询时间的有效性
	-- 进行查询
	select id, cust_code, fee_item_name, fee_sum, fee_time, fee_stat 
		from cust_fee
		where cust_code = @cust_code
end
go


/*
	功能：查询用户欠费信息
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_qry_cust_own_fee')
	drop proc wsp_qry_cust_own_fee
go
create proc wsp_qry_cust_own_fee(@cust_code varchar(20), @begin_time datetime, @end_time datetime)
as
begin
	-- 查检查询时间的有效性
	if @begin_time is null
		set @begin_time = '1900-01-01'

	if @end_time is null
		set @end_time = '9999-12-31'

	-- 进行查询
	select id, cust_code, fee_item_name, fee_sum, fee_time, fee_stat 
		from cust_fee
		where cust_code = @cust_code
				and fee_stat = 0
end
go

/*
	功能：查询用户缴费记录
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_qry_cust_fee_log')
	drop proc wsp_qry_cust_fee_log
go
create proc wsp_qry_cust_fee_log(@cust_code varchar(20), @begin_time datetime, @end_time datetime)
as
begin
	-- 查检查询时间的有效性
	if @begin_time is null
		set @begin_time = '1900-01-01'

	if @end_time is null
		set @end_time = '9999-12-31'

	-- 进行查询
	select cust_code, fee_sum, fee_type, fee_time, oper_code
		from cust_fee_log
		where cust_code = @cust_code
				and fee_time between @begin_time and @end_time
end
go

/*
	功能：根据指定的区域代码查询抄表单
*/
if exists(select * from sysobjects where type='P' and name = 'wsp_qry_read_sheet')
	drop proc wsp_qry_read_sheet
go
create proc wsp_qry_read_sheet(@water_area_code varchar(20) = null, @print_flg tinyint = 2)
as
if @print_flg not in (0,1,2)
begin
	return @print_flg
end
if @water_area_code is null
begin
	if @print_flg = 2
	begin
		select * 
			from v_read_sheet 
			where input_flg = 0
	end
	else
	begin
		select * 
			from v_read_sheet 
			where print_flg = @print_flg
				 and input_flg = 0 
	end
end
else
begin
	if @print_flg = 2
	begin
		select * 
			from v_read_sheet 
			where water_area_code = @water_area_code
				 and input_flg = 0 
	end
	else
	begin
		select * 
			from v_read_sheet 
			where water_area_code = @water_area_code 
					and print_flg = @print_flg
					 and input_flg = 0 
	end
end
return @@rowcount
go