创建表.sql

水费管理系统

set nocount on
go

/*
	表名：dual
	功能：用于计算表达式
*/
if exists(select * from sysobjects where type='U' and name = 'dual')
	drop table dual
go
create table dual (X char(1))
go

/*
	表名：object_id
	功能: 存储各表的序数
*/
if exists(select * from sysobjects where type='U' and name = 'object_id')
	drop table object_id
go
create table object_id(tbl_name varchar(20), 
	id int default 1, 
	code varchar(20) not null default '')
go
create index idx_tbl_name on object_id(tbl_name,code)
go

/*
	表名：代码表
	功能：存储系统中用到的各种代码，用于将各种代码映射为可读的名称等
*/
if exists(select * from sysobjects where type='U' and name='code_name')
	drop table code_name
go
create table code_name (
	id int identity primary key,
	code_type varchar(20) not null,
	code_flag varchar(10) not null default '',
	code_name varchar(50) not null,
	standard_code varchar(20) null,
	code_level tinyint not null default 0,
	state char(3) not null default '',
	seq_nbr tinyint not null, 
	constraint u_code_type__code_name unique(code_type, code_name),
	constraint u_code_type__seq_nbr unique(code_type, seq_nbr)
	)
go
create index idx_code_type on code_name(code_type)
go
create index idx_code_name on code_name(code_name)
go
create index idx_standard_code on code_name(standard_code)
go


/*
	表名：供水区域表
	功能：存储供水区域
*/
if exists(select * from sysobjects where type='U' and name='water_area')
	drop table water_area --区域表
go
create table water_area(
	id int identity primary key,
	water_area_code varchar(10) not null constraint u_water_area_code unique,
	water_area_name varchar(40) not null constraint u_water_area_name unique, 
	parent_water_area_code varchar(10) default '',
	u_time datetime default getdate(),
	c_time datetime default getdate()
	)
go
create index water_area_code on water_area(water_area_code)
go
create index parent_water_area_code on water_area(parent_water_area_code)
go


/*
*	表名：资费标准表
*/
if exists(select * from sysobjects where type='U' and name='fee_std')
	drop table fee_std
go
create table fee_std(
	id int identity primary key,
	fee_std_code varchar(10) not null constraint u_std_code unique,
	fee_std_name varchar(40) not null constraint u_std_name unique,
	fee_std_type tinyint not null,
	seq_nbr tinyint default 1, -- 顺序号
	c_time datetime default getdate(),
	u_time datetime default getdate())
go
create index fee_std_code on fee_std(fee_std_code)
go


/*
*	表名：资费标准单价表
*/
if exists(select * from sysobjects where type='U' and name='fee_std_price')
	drop table fee_std_price
go
create table fee_std_price(
	id int identity primary key,
	fee_std_code varchar(10) not null,
	begin_time datetime not null,
	unit_price money not null default 0,
	w_unit_price money not null default 0,
	price_unit tinyint not null default 0,
	c_time datetime default getdate(),
	u_time datetime default getdate()
	)
go
create index fee_std_code on fee_std_price(fee_std_code)
go

/*
*  收费员－区域关系表
*  M:N关系：表示收费员管辖区域
*/
if exists(select * from sysobjects where type='U' and name='tc_wa')
	drop table tc_wa 
go
create table tc_wa(
	id int identity primary key,
	water_area_code varchar(20) not null,
	tc_code varchar(10) not null ,
	stat_time datetime default getdate(),
	c_time datetime default getdate(),
	constraint  u_water_area_code__tc_code unique(water_area_code, tc_code) 	)
go
create index water_area_code on tc_wa(water_area_code)
go
create index tc_code on tc_wa(tc_code)
go

/*
*	表名：系统操作员表
*	功能：存储系统操作员的资料
*/
if exists(select * from sysobjects where type='U' and name='oper')
	drop table oper
go
create table oper(
	id int identity primary key,
	oper_code varchar(10) not null constraint u_oper_code unique,
	oper_name varchar(20) not null default '', 
	oper_passwd varchar(10) default '', 
	oper_init_passwd varchar(10) default '', 
	admin_flg tinyint not null default 0, -- 1　系统管理员；其它，非系统管理员
	phone varchar(20) default '',
	lock_flg tinyint default 0, -- 1 锁定；  0 解锁

	last_success_login_time datetime,
	last_fail_login_time datetime,
	u_time datetime default getdate(),
	c_time datetime default getdate()
	)
go
create index oper_code on oper(oper_code)
go

/*
	系统操作日志表
*/
if exists(select * from sysobjects where type='U' and name='oper_action_log')
	drop table oper_action_log
go
create table oper_action_log(
	id int identity primary key,
	oper_code varchar(20),
	oper_name varchar(20),
	oper_act varchar(50),
	c_time datetime default getdate()
)
go
create index oper_code on oper_action_log(oper_code)
go


/*
*	表名：用户表
*/
if exists(select * from sysobjects where type='U' and name='cust')
	drop table cust --客户信息表
go
create table cust(
	id int identity primary key,
	cust_code varchar(20) not null  unique,		-- 资费,输入客户资料时用于查找客户信息
	water_area_code varchar(10) not null,
	cust_name varchar(30) not null default '', 
	cust_type varchar(20) not null,						-- 用户类别表。用于统计目的
	stat varchar(20) not null, 								-- 正常、停用、注销
	fee_cycle_cnt tinyint not null, 					-- 收费周期月数
	fee_type varchar(20) not null, 					-- 客户交费类别：在code_name表中，code_type='FEE_TYPE',
																-- 	1 窗口交费；2　银行托收交费；3　收费员代收
	fee_flg_water tinyint default 1, 				-- 水费收取标志
	fee_flg_waste tinyint default 0, 				-- 排污费收取标志
	fee_flg_src tinyint default 0, 					-- 资源税收取标志
	fee_flg_value tinyint default 0, 				-- 增值税收取标志

	address varchar(40),
	phone varchar(20),
	bank_code varchar(20),
	bank_acct_no varchar(30), 
	tax_no varchar(30), 
	stat_time datetime default getdate(),
	c_time datetime default getdate()
	)
go
create index idx_cust_code on cust(cust_code)
go
create index idx_water_area_code on cust(water_area_code)
go

/*
*	表名：用户资料修改日志表
*/
if exists(select * from sysobjects where type='U' and name='cust_log')
	drop table cust_log --客户信息表
go
select * into cust_log from cust where 1=2
go

alter table cust_log drop column id
go
alter table cust_log add id int primary key default 0
go
create index idx_cust_code on cust_log(cust_code)
go
create index idx_water_area_code on cust_log(water_area_code)
go


/*
	表名：余额表
	功能：记录客户交费余额
*/
if exists(select * from sysobjects where type='U' and name='cust_balance')
	drop table cust_balance
go
create table cust_balance(
	id int identity primary key,
	cust_code varchar(20) not null,
	balance money not null,
	stat_time datetime default getdate(),
	c_time datetime default getdate())
go
create index cust_code on cust_balance(cust_code)
go

/*
*  表名：用户窗口交费单据表
*/
if exists(select * from sysobjects where type='U' and name='cust_win_receipt')
	drop table cust_win_receipt
go
create table cust_win_receipt(
	id int identity primary key,
	receipt_sn as id,

	cust_code varchar(20) not null,
	cust_name varchar(30) not null,

	fee_sum money, 									-- 本期水费
	d_fee_sum money, 									--优惠水费
	act_fee_sum money, 								-- 本次实交
	last_balance money, 								-- 上期余额
	this_balance money,

	oper_code varchar(20) not null,
	print_stat tinyint not null default 0, 	-- 打印状态
	print_time datetime,
	c_time datetime default getdate()
	)
go
create index cust_code on cust_win_receipt(cust_code)
go

/*
  表名：银行托收单据表
  TODO: question?
*/
if exists(select * from sysobjects where type='U' and name='cust_bank_receipt')
	drop table cust_bank_receipt
go
create table cust_bank_receipt(
	id int identity primary key,
	cust_code varchar(20) not null,
	cust_name varchar(20) not null,
	
	receipt_stat tinyint, -- 0 未打印；1 已打印托收单；2 已回单；3 退单；4 漂单
	
	fee_sum money, 									-- 本期水费
	d_fee_sum money, 									--优惠水费
	act_fee_sum money, 								-- 本次实交
	last_balance money, 								-- 上期余额
	this_balance money,								-- 本期余额
	
	oper_code varchar(20),
	print_time datetime,
	stat_time datetime default getdate(), --记录各种操作的状态
	c_time datetime default getdate()
	)
go
create index cust_code on cust_bank_receipt(cust_code)
go


/*
	客户水费细项记录表
*/
if exists(select * from sysobjects where type='U' and name='cust_fee')
	drop table cust_fee
go
create table cust_fee(
	id int identity primary key,
	cust_code varchar(20) not null,

	fee_item_name varchar(20) not null,  -- 填入：水表、污水费、资源费、增值税等项目
	fee_sum money not null,
	fee_time datetime, 
	fee_stat tinyint not null default 0, -- 交费状态: 0 欠费; 1已交; 2减免
	c_time datetime default getdate()
	)
go
create index cust_code on cust_fee(cust_code)
go

/*
	客户交费记录表
*/
if exists(select * from sysobjects where type='U' and name='cust_fee_log')
	drop table cust_fee_log
go
create table cust_fee_log(
	id int identity primary key,
	cust_code varchar(20) not null,

	fee_sum money,
	fee_type varchar(20) not null, -- 客户交费类别：窗口、收费员代收、银行托收
	fee_time datetime not null default getdate(),
	oper_code varchar(10) not null,

	c_time datetime default getdate())
go
create index cust_code on cust_fee_log(cust_code)
go

/*
	用水量表