netctoss.sql

电信计费项目 该系统在Sun Solaris下开发,运行于Apache Tomcat平台,采用J2EE架构

drop sequence t_sq_wdd;
drop table t_detailwdd_1 cascade constraints;
drop table t_detailwdd_2 cascade constraints;
drop table t_detailwdd_3 cascade constraints;
drop table t_detailwdd_4 cascade constraints;
drop table t_detailwdd_5 cascade constraints;
drop table t_detailwdd_6 cascade constraints;
drop table t_detailwdd_7 cascade constraints;
drop table t_detailwdd_8 cascade constraints;
drop table t_detailwdd_9 cascade constraints;
drop table t_detailwdd_10 cascade constraints;
drop table t_detailwdd_11 cascade constraints;
drop table t_detailwdd_12 cascade constraints;
drop table t_detailwdd_13 cascade constraints;
drop table t_detailwdd_14 cascade constraints; 
drop table t_detailwdd_15 cascade constraints;
drop table t_detailwdd_16 cascade constraints;
drop table t_detailwdd_17 cascade constraints;
drop table t_detailwdd_18 cascade constraints;
drop table t_detailwdd_19 cascade constraints;
drop table t_detailwdd_20 cascade constraints;
drop table t_detailwdd_21 cascade constraints;
drop table t_detailwdd_22 cascade constraints;
drop table t_detailwdd_23 cascade constraints;
drop table t_detailwdd_24 cascade constraints;
drop table t_detailwdd_25 cascade constraints;
drop table t_detailwdd_26 cascade constraints;
drop table t_detailwdd_27 cascade constraints;
drop table t_detailwdd_28 cascade constraints;
drop table t_detailwdd_29 cascade constraints;
drop table t_detailwdd_30 cascade constraints;
drop table t_detailwdd_31 cascade constraints;

drop table t_monthwdd_1 cascade constraints;
drop table t_monthwdd_2 cascade constraints;
drop table t_monthwdd_3 cascade constraints;
drop table t_monthwdd_4 cascade constraints; 
drop table t_monthwdd_5 cascade constraints;
drop table t_monthwdd_6 cascade constraints;
drop table t_monthwdd_7 cascade constraints;
drop table t_monthwdd_8 cascade constraints;
drop table t_monthwdd_9 cascade constraints;
drop table t_monthwdd_10 cascade constraints;
drop table t_monthwdd_11 cascade constraints;
drop table t_monthwdd_12 cascade constraints;

drop table t_daywdd_1 cascade constraints;
drop table t_daywdd_2 cascade constraints;
drop table t_daywdd_3 cascade constraints;
drop table t_daywdd_4 cascade constraints;
drop table t_daywdd_5 cascade constraints;
drop table t_daywdd_6 cascade constraints;
drop table t_daywdd_7 cascade constraints;
drop table t_daywdd_8 cascade constraints;
drop table t_daywdd_9 cascade constraints;
drop table t_daywdd_10 cascade constraints;
drop table t_daywdd_11 cascade constraints;
drop table t_daywdd_12 cascade constraints;
drop table t_daywdd_13 cascade constraints;
drop table t_daywdd_14 cascade constraints;
drop table t_daywdd_15 cascade constraints;
drop table t_daywdd_16 cascade constraints;
drop table t_daywdd_17 cascade constraints;
drop table t_daywdd_18 cascade constraints;
drop table t_daywdd_19 cascade constraints;
drop table t_daywdd_20 cascade constraints;
drop table t_daywdd_21 cascade constraints;
drop table t_daywdd_22 cascade constraints;
drop table t_daywdd_23 cascade constraints;
drop table t_daywdd_24 cascade constraints;
drop table t_daywdd_25 cascade constraints;
drop table t_daywdd_26 cascade constraints;
drop table t_daywdd_27 cascade constraints;
drop table t_daywdd_28 cascade constraints;
drop table t_daywdd_29 cascade constraints;
drop table t_daywdd_30 cascade constraints;
drop table t_daywdd_31 cascade constraints;

drop table t_year_2008 cascade constraints;

drop table t_right_wdd cascade constraints;
drop table t_model_wdd cascade constraints;
drop table t_admin_wdd cascade constraints;
drop table t_daywdd cascade constraints;
drop table t_monthwdd cascade constraints;
drop table t_yearwdd cascade constraints;
drop table t_ServiceUser_wdd cascade constraints;
drop table t_price_wdd cascade constraints;
drop table t_user_wdd cascade constraints;


create sequence t_sq_wdd
increment by 1
start with 100
maxvalue 999999
nocache
nocycle;
commit;

create table t_admin_wdd(
    amd_id          number(19) primary key,				-- 管理员编码
    amd_name        varchar2(60),                   -- 管理员姓名
    login_name      varchar2(10) unique not null,	-- 登录名
    login_password  varchar2(32),                   -- 登录密码
    amd_phone       varchar2(32),                   -- 联系电话
    amd_email       varchar2(32),                   -- E-Mail
    enroll_date     date,                           -- 注册日期
    close_date      date                           
);

create table t_model_wdd(
    oid         number(19) primary key, 
    name        varchar2(32) not null unique,
    model_number number(2) not null unique        
);
create table t_right_wdd(
    admin_fid			number(19) references t_admin_wdd(amd_id) on delete cascade,
    m_fid   			number(19) references t_model_wdd(oid) on delete cascade,  
    primary key(admin_fid, m_fid)
);


create table t_user_wdd (
    user_id         number(32)	primary key,  -- 主键编码
    user_name       varchar2(32) not null,    -- 用户姓名
    login_name      varchar2(10) unique,      -- 用户账务帐号
    login_password  varchar2(32) not null,    -- 用户账务帐号密码
    user_status    	 number(2) not null,       -- 账务帐号状态（0：开通；1：暂停；2：删除）
    user_phone      varchar2(32),             -- 联系电话
    user_email      varchar2(32),             -- email
    enroll_date     date,                     -- 开通日期
    close_date      date,                     -- 暂停日期
    payment_style   number(2),                -- 付款方式(0：现金；1：银行转帐；2：邮局汇款；3：其他)
    career          varchar2(32),             -- 职业
    province     		 varchar2(32),             -- 国籍(系统中在本字段记录省份)
    gender          varchar2(8),              -- 性别
    company         varchar2(32),             -- 公司名称
    company_email   varchar2(50),             -- 公司邮箱
    post_code       varchar2(16)              -- 邮编
);
create table t_price_wdd (
	price_id number(19) primary key,
	price_name varchar2(60) not null unique,
	base_fee number(3) not null,
	rate_fee number(3,1) not null,
	price_descripte varchar2(256)
);
create table t_ServiceUser_wdd(
	service_user_id number(19) primary key,
	lab_login_name varchar2(32) not null,
	lab_login_password varchar2(16) not null,
	service_user_status number(2) not null,
	lab_ip varchar2(64) not null,
	enroll_date date,
	close_date date ,
	user_fid number(19) references t_user_wdd(user_id) on delete cascade,
	price_fid number(19) references t_price_wdd(price_id) on delete cascade
);

Create table t_daywdd(
    logout_date     date,           -- 统计时间（格式：YYYY-MM-DD HH24:00:00）
    lab_ip          varchar2(32),   -- 服务器ip
    time_duration   number(10),     -- 时长
    primary key(lab_ip, logout_date)
);
Create table t_monthwdd(
    logout_date     date,           -- 统计时间（格式：YYYY-MM-DD 00:00:00）
    lab_ip          varchar2(32),   -- 服务器ip
    time_duration   number(10),     -- 时长
    primary key(lab_ip, logout_date)
);
Create table t_yearwdd(
    logout_date     date,           -- 统计时间（格式：YYYY-MM-01 00:00:00）
    lab_ip          varchar2(32),   -- 服务器ip
    time_duration   number(10),     -- 时长
    primary key(lab_ip, logout_date)
);


Create table t_detailwdd_1 (
    login_name      varchar2(10) ,  -- 用户名
    login_ip        varchar2(32),   -- 用户登录ip
    login_date      date,           -- 登录时间
    logout_date     date,           -- 退出时间
    lab_ip          varchar2(32),   -- 服务器ip
    time_duration   number(10)      -- 时长
);
Create table t_detailwdd_2 (
    login_name      varchar2(10) ,  -- 用户名
    login_ip        varchar2(32),   -- 用户登录ip
    login_date      date,           -- 登录时间
    logout_date     date,           -- 退出时间
    lab_ip          varchar2(32),   -- 服务器ip
    time_duration   number(10)      -- 时长
);
Create table t_detailwdd_3 (
    login_name      varchar2(10) ,  -- 用户名
    login_ip        varchar2(32),   -- 用户登录ip
    login_date      date,           -- 登录时间
    logout_date     date,           -- 退出时间
    lab_ip          varchar2(32),   -- 服务器ip
    time_duration   number(10)      -- 时长
);
Create table t_detailwdd_4 (
    login_name      varchar2(10) ,  -- 用户名
    login_ip        varchar2(32),   -- 用户登录ip
    login_date      date,           -- 登录时间
    logout_date     date,           -- 退出时间
    lab_ip          varchar2(32),   -- 服务器ip
    time_duration   number(10)      -- 时长
);
Create table t_detailwdd_5 (
    login_name      varchar2(10) ,  -- 用户名
    login_ip        varchar2(32),   -- 用户登录ip
    login_date      date,           -- 登录时间
    logout_date     date,           -- 退出时间
    lab_ip          varchar2(32),   -- 服务器ip
    time_duration   number(10)      -- 时长
);
Create table t_detailwdd_6 (
    login_name      varchar2(10) ,  -- 用户名
    login_ip        varchar2(32),   -- 用户登录ip
    login_date      date,           -- 登录时间
    logout_date     date,           -- 退出时间
    lab_ip          varchar2(32),   -- 服务器ip
    time_duration   number(10)      -- 时长
);
Create table t_detailwdd_7 (
    login_name      varchar2(10) ,  -- 用户名