创建表.sql

水费管理系统

*/
if exists(select * from sysobjects where type='U' and name='cw_amt')
	drop table cw_amt
go
create table cw_amt(
	id int identity primary key,
	wm_code varchar(20),
	water_amt decimal(10,4) default 0,
	read_time datetime, 
	process_flg tinyint default 0, -- 用以标志是否进行过处理
	c_time datetime default getdate())
go
create index wm_code on cw_amt(wm_code)
go


/*
	水量减免表
*/
if exists(select * from sysobjects where type='U' and name='cw_amt_d')
	drop table cw_amt_d
go
create table cw_amt_d(
	id int identity primary key,
	wm_code varchar(20),
	d_water_amt decimal(10,4) default 0, -- 减免水量
	d_time datetime default getdate(), -- 减免时间
	reason varchar(20) default '', -- 减免原因
	cw_amt_id int not null,
	process_flg tinyint default 0,
	c_time datetime default getdate())
go
create index wm_code on cw_amt_d(wm_code)
go

/*
	水表表
	母表标志问题：
	1、有子表的是母表（子表个数可以为0）
	2、母表和子表之间有一定的关系
	3、计算总量时，所有的顶级母表用量之和即为总量
	4、
*/
if exists(select * from sysobjects where type='U' and name='water_meter')
	drop table water_meter
go
create table water_meter(
	id int identity primary key,
	cust_code varchar(20) not null, 
	wm_seq tinyint not null default 1,
	wm_code as convert(varchar(20),cust_code + replicate('0', 3 - len(ltrim(str(wm_seq)) )) + ltrim(str(wm_seq))),
	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'
	stat_code varchar(20) not null, -- 正常:0、停用:1、注销:3、新增:4:、换表:5
	parent_wm_code varchar(20),
	init_value decimal(10,4) not null, -- 此初始值对于新增加的客户是必须的，会自动加入到水表运行的初始记录当中;如果没有录入初始值,必须通过其它方式录入
	max_value decimal(10,4) not null, -- 最大量度值，用于水表计数翻转时用水量的计算

	fee_cycle_cnt tinyint default 1, --　水表抄表时间间隔
	last_read_time datetime default getdate(), -- 上一次抄表时间
	read_sheet_flg tinyint default 0, -- 0: 没有已经打打印但尚未回单的抄表单；1：有已经打印但尚未回单的抄表单
	c_time datetime default getdate(),
	u_time datetime default getdate()) -- 系统更新记录信息时,记录更新时间;在水表初次建立时,应该等于c_time
go
create index idx_cust_code_on_water_meter on water_meter(cust_code)
create index idx_parent_wm_code on water_meter(parent_wm_code)
go

if exists(select * from sysobjects where type='U' and name='water_meter_log')
	drop table water_meter_log
go
select * into water_meter_log from water_meter where 1 = 2
go
alter table water_meter_log alter column id int
go

/*
	水表运行记录表
*/
if exists(select * from sysobjects where type='U' and name='wm_run_log')
	drop table wm_run_log
go
create table wm_run_log(
	id int identity primary key,
	wm_code varchar(20) not null,
	
	read_time datetime not null, 
	fee_flg tinyint default 0, -- 是否已经计算费用
	value decimal(10,4) not null,
	base_value_flg tinyint default 0, -- 1 基础值 0 非基础值
	process_flg tinyint default 0, --用以标志是否已经进行过处理
	tc_code varchar(20), -- 记录抄表员代码，在录入时从tc_wa表中取得。
	c_time datetime default getdate()
	)
go

create index wm_code on wm_run_log(wm_code)
go
create index read_time on wm_run_log(read_time)
go


/*
	抄表员/收费员信息表
*/
if exists(select * from sysobjects where type='U' and name='toll_collector')
	drop table toll_collector
go
create table toll_collector(
	id int identity primary key,
	tc_code varchar(10) unique, 
	tc_name varchar(20) unique,
	sex char(1),
	phone varchar(20),
	c_time datetime default getdate(),
	u_time datetime default getdate())
go
create index idx_tc_code on toll_collector(tc_code)
go

/*
	收费员交款记录表
*/
if exists(select * from sysobjects where type='U' and name='tc_fee_log')
	drop table tc_fee_log
go
create table tc_fee_log(
	id int identity primary key,
	tc_code varchar(10) not null,
	cust_code varchar(20), 
	fee_amt money not null, --金额
	input_flg tinyint default 0,
	oper_code varchar(10),
	sn int default 0,
	c_time datetime default getdate()
	)
go

create index idx_sn on tc_fee_log(sn)
go
create index idx_tc_code on tc_fee_log(tc_code)
go


/*
 客户费用减免记录表。
*/
if exists(select * from sysobjects where type='U' and name='cust_fee_d')
	drop table cust_fee_d
go
create table cust_fee_d(
	id int identity primary key,
	cust_code varchar(20),
	
	fee_item_name varchar(20),
	d_sum money not null, 
	process_flg tinyint default 0,
	reason varchar(20) not null,
	cust_fee_id int not null,
	c_time datetime default getdate()
	)
go
create index cust_code on cust_fee_d(cust_code)
go

/*
  客户费用通知单表：客户基本资料　＋　各水表费用资料
*/
if exists(select * from sysobjects where type='U' and name='fee_inform')
	drop table fee_inform
go
create table fee_inform(
	cust_code varchar(20),
	cust_name varchar(20),
	address varchar(40) default '',
	
	fee_sum money default 0,
	oper_code varchar(10),
	stat tinyint default 0, -- 1. 已打印；0  未打印
	print_time datetime,
	c_time datetime default getdate()
	)
go
create index cust_code on fee_inform(cust_code)
go

/*
	资费标准表时间表
*/
if exists(select * from sysobjects where type='U' and name='fee_std_time')
	drop table fee_std_time
go

select fee_std_code,begin_time, unit_price, w_unit_price, price_unit into fee_std_time
	from fee_std_price where 1=2
go
create index fee_std_code on fee_std_time(fee_std_code)
go

/*
	资费单价时间段表
*/
if exists(select * from sysobjects where type='U' and name='fee_price')
	drop table fee_price
go

create table fee_price(fee_std_code varchar(10), bt datetime, et datetime, price money ,w_price money)
go
create index fee_std_code on fee_price(fee_std_code)
go

/*
	表名：换表日志
*/
if exists(select * from sysobjects where type='U' and name='change_wm_log')
	drop table change_wm_log
go
create table change_wm_log(
	id int primary key,
	wm_code varchar(20) not null,
	old_wm_value decimal(10,4) not null,
	new_wm_base_value decimal(10,4) not null default 0,
	tc_code varchar(20) not null,
	oper_code varchar(20) not null,
	c_time datetime default getdate())
go
create index idx_wm_code on change_wm_log(wm_code)
go

/*
	表名：in_set表
	功能: 用于wsp_gen_read_sheet存储过程
*/
if exists(select * from sysobjects where type='U' and name='in_set')
	drop table in_set
go
create table in_set(
	spid int default @@spid, 
	value varchar(256) default '',
	value_int int default 0, 
	value_type varchar(20) default '')
go
create index idx_spid on in_set(spid)
go

/*
	表名:抄表单表
*/
if exists(select * from sysobjects where type='U' and name='read_sheet')
	drop table read_sheet
go
create table read_sheet(
	-- 用户基本资料
	id int identity primary key,
	cust_code varchar(20),
	water_area_code varchar(20),
	cust_name varchar(30),
	wm_code varchar(20),
	address varchar(40),
	phone varchar(20),

	-- 水表资料
	pos_code varchar(20),
	fee_std_code varchar(20),
	last_value dec(10,4),
	this_value dec(10,4),
	this_amt dec(10,4),
	price money, 
	fee_amt money,
	print_flg tinyint default 0,
	input_flg tinyint default 0,
	tc_code varchar(10),
	read_time datetime)

go
create index idx_cust_code on read_sheet(cust_code)
go

/*
	表名：in_set_cust_code表
	功能: 用于存储wsp_gen_read_sheet生成的read_sheet(id)过程
*/
if exists(select * from sysobjects where type='U' and name='in_set_cust_code')
	drop table in_set_cust_code
go
create table in_set_cust_code(
	spid int default @@spid, 
	cust_code varchar(20),
	wm_code varchar(20) 
	)

go
create index idx_cust_code on in_set_cust_code(cust_code)
go
create index idx_wm_code on in_set_cust_code(wm_code)
go

/*
	表名:read_sheet_log
	功能: 用于存储
*/
if exists(select * from sysobjects where type='U' and name='read_sheet_log')
	drop table read_sheet_log
go
select * into read_sheet_log from read_sheet where 1 =2
go
alter table read_sheet_log add spid int default @@spid
go

/*
	表名: rs_gen_log
	功能: 区域抄表单生成记录表
*/
if exists(select * from sysobjects where type='U' and name='rs_gen_log')
	drop table rs_gen_log
go
create table rs_gen_log(
	id int identity primary key,
	water_area_code varchar(20),
	gen_time datetime,
	gen_space int,
	oper_code varchar(20),
	c_time datetime default getdate())
go
create index idx_water_area_code on rs_gen_log(water_area_code)
go

/*
	表名: tc_handin_receipt
	功能: 收费员交费单据表
*/
if exists(select * from sysobjects where type='U' and name='tc_handin_receipt')
	drop table tc_handin_receipt
go
create table tc_handin_receipt(
	id int identity primary key,
	tc_code varchar(20),
	tc_name varchar(20),
	fee_sum dec(10,4),
	oper_code varchar(20),
	oper_time datetime,
	handin_sn int)
go
create index idx_handin_sn on tc_handin_receipt(handin_sn)
go

set nocount off
go