drop table if exists addtimelist

drop table if exists usertime

drop table if exists history

drop table if exists users

drop table if exists types

drop table if exists login

drop table if exists logout

drop table if exists module

drop table if exists operation

drop table if exists operators

drop table if exists groups

drop table if exists batchs

drop table if exists batusers

drop table if exists vipuser

drop table if exists vips

drop table if exists gpaddtimelist

drop table if exists actions

drop table if exists useracl1

drop table if exists useracl2

drop table if exists useracl3

drop table if exists userband

drop table if exists returnlist

drop table if exists allowtimelist

drop table if exists tmpusers

drop table if exists contact

drop table if exists addusertime

drop table if exists specialtime

drop table if exists cash

CREATE TABLE login (
	ipaddr varchar(15) binary NOT NULL ,
	userid varchar(20) binary NOT NULL ,
	type varchar(8) binary NOT NULL ,
	remaintime int NOT NULL ,
	remaindata int NOT NULL ,
	processed char(1) binary NULL ,
	logintime varchar(20) binary NULL ,
	maskword varchar(10) binary NULL ,
	mac varchar(20) binary NULL ,
	PRIMARY KEY (ipaddr)
)

CREATE TABLE logout (
	ipaddr varchar(15) binary NOT NULL ,
	PRIMARY KEY (ipaddr)
)

CREATE TABLE history (
	userid varchar(20) binary NULL ,
	ipaddr varchar(15) binary NOT NULL ,
	type varchar(8) binary NULL ,
	starttime varchar(20) binary NOT NULL ,
	endtime varchar(20) binary NOT NULL ,
	usetime int NOT NULL ,
	datalength int NOT NULL ,
	facttime int NOT NULL ,
	factdata int NOT NULL ,
	discount int NOT NULL ,
	flag char(1) binary NOT NULL ,
	PRIMARY KEY (ipaddr,starttime)
)

CREATE TABLE users (
	userid varchar(20) binary NOT NULL ,
	loginname varchar(20) binary NOT NULL ,
	password varchar(50) binary NOT NULL ,
	type varchar(8) binary NOT NULL ,
	onlines int NOT NULL ,
	createtime varchar(20) binary NOT NULL ,
	username varchar(50) binary NULL ,
	usertel varchar(50) binary NULL ,
	comment varchar(100) binary NULL ,
	deletetime varchar(20) binary NULL ,
	checked char(1) binary NOT NULL ,
        gpid int NULL ,
	cookiemask varchar(25) binary NULL ,
	ipaddr varchar(15) binary NULL ,
	suspendtime varchar(20) binary NULL ,
	suspend char(1) binary NULL ,
	mac varchar(20) binary NULL ,
	PRIMARY KEY (userid)
)

CREATE TABLE usertime (
	userid varchar(20) binary NOT NULL ,
	starttime varchar(20) binary NOT NULL ,
	length int NOT NULL ,
	datalength int NOT NULL ,
	overed char(1) binary NOT NULL ,
	PRIMARY KEY (userid)
)

CREATE TABLE addtimelist (
	userid varchar(20) binary NOT NULL ,
	addtime varchar(20) binary NOT NULL ,
	type varchar(8) binary NOT NULL ,
	length float NOT NULL ,
	datalength float NOT NULL ,
	otherlength int NULL ,
	price float NOT NULL ,
	money1 float NOT NULL ,
	discount int NOT NULL ,
	money2 float NOT NULL ,
	flag char(1) binary NOT NULL ,
	PRIMARY KEY (userid,addtime)
)

CREATE TABLE operators (
	operator varchar(20) binary NOT NULL ,
	opername varchar(40) binary NULL ,
	password varchar(20) binary NULL ,
	acl varchar(50) binary NULL ,
	PRIMARY KEY (operator)
)

CREATE TABLE operation (
	operator varchar(20) binary NOT NULL ,
	opertime varchar(20) binary NOT NULL ,
	action varchar(100) binary NOT NULL
)

CREATE TABLE module (
	appcode varchar(4) binary NOT NULL ,
	appname varchar(50) binary NOT NULL ,
	PRIMARY KEY (appcode)
)

CREATE TABLE groups (
	gpid int NOT NULL ,
	gpname varchar(50) binary NOT NULL ,
	createtime varchar(20) binary NOT NULL ,
	comment varchar(100) binary NULL ,
	PRIMARY KEY (gpid)
)

CREATE TABLE batchs (
        batchid int NOT NULL ,      
	startuserid varchar(20) binary NOT NULL ,
	createtime varchar(20) binary NOT NULL ,
        type varchar(8) binary NOT NULL ,
        onlines int NOT NULL ,
        length int NOT NULL ,
        count int NOT NULL ,
        money1 float NOT NULL ,
        otherlength int NULL ,         
        discount int NULL ,            
        money2 float NOT NULL ,
        active char(1) binary NOT NULL ,
        gpid int NULL ,
        comment varchar(100) binary NULL ,
	PRIMARY KEY (batchid)
)

CREATE TABLE batusers (
	userid varchar(20) binary NOT NULL ,
	loginname varchar(20) binary NOT NULL ,
	password varchar(50) binary NOT NULL ,
	type varchar(8) binary NOT NULL ,
	onlines int NOT NULL ,
        length int NOT NULL ,
	createtime varchar(20) binary NOT NULL ,
        money1 float NOT NULL ,
        otherlength int NULL ,
        discount int NULL ,
        money2 float NOT NULL ,
        batchid int NOT NULL ,
        active char(1) binary NOT NULL ,
        gpid int NULL ,
	PRIMARY KEY (userid)
)

CREATE TABLE vipuser (
	userid varchar(20) binary NOT NULL ,
	viptype varchar(40) binary NOT NULL ,
	givelength int NOT NULL ,
	discount int NOT NULL ,
	PRIMARY KEY (userid)
)

CREATE TABLE vips (
        viptype varchar(40) binary NOT NULL ,
	givelength int NOT NULL ,
	discount int NOT NULL ,
	PRIMARY KEY (viptype)
)

CREATE TABLE gpaddtimelist (
	gpid int NOT NULL ,
	addtime varchar(20) binary NOT NULL ,
	length float NOT NULL ,
	otherlength int NULL ,
	count int NOT NULL ,
        money1 float NOT NULL ,
	discount int NOT NULL ,
	money2 float NOT NULL ,
	PRIMARY KEY (gpid,addtime)
)

CREATE TABLE actions (
	actionid varchar(2) binary NOT NULL ,
	actionname varchar(50) binary NOT NULL ,
	PRIMARY KEY (actionid)
)

CREATE TABLE useracl1 (
	userid varchar(20) binary NOT NULL ,
	mode char(1) binary NOT NULL ,
	icmp char(1) binary NOT NULL ,
	udp char(1) binary NOT NULL ,
	igmp char(1) binary NOT NULL ,
	checkport char(1) binary NOT NULL ,
	PRIMARY KEY (userid)
)

CREATE TABLE useracl2 (
	userid varchar(20) binary NOT NULL ,
	site varchar(50) binary NOT NULL ,
	orderid int NOT NULL ,
	PRIMARY KEY (userid,site)
)

CREATE TABLE useracl3 (
	userid varchar(20) binary NOT NULL ,
	port int NOT NULL ,
	orderid int NOT NULL ,
	PRIMARY KEY (userid,port)
)

CREATE TABLE userband (
	loginname varchar(20) binary NOT NULL ,
	bandwidth int NOT NULL ,
	bandtype int NOT NULL ,
	PRIMARY KEY (loginname)
)

CREATE TABLE returnlist (
	userid varchar(20) binary NOT NULL ,
	loginname varchar(20) binary NOT NULL ,
	returntime varchar(20) binary NOT NULL ,
	type varchar(8) binary NOT NULL ,
	length float NOT NULL ,
	price float NOT NULL ,
	money1 float NOT NULL ,
	discount int NOT NULL ,
	money2 float NOT NULL ,
	PRIMARY KEY (userid)
)

CREATE TABLE allowtimelist (
	userid varchar(20) binary NOT NULL ,
	weeknumber int NOT NULL ,
	starttime varchar(5) binary NOT NULL ,
	endtime varchar(5) binary NOT NULL ,
	PRIMARY KEY (userid,weeknumber,starttime)
)

CREATE TABLE tmpusers (
	userid varchar(20) binary NOT NULL ,
	loginname varchar(20) binary NOT NULL ,
	password varchar(50) binary NOT NULL ,
	type varchar(8) binary NOT NULL ,
	onlines int NOT NULL ,
	createtime varchar(20) binary NOT NULL ,
	username varchar(50) binary NULL ,
	usertel varchar(50) binary NULL ,
	comment varchar(100) binary NULL ,
        gpid int NULL ,
	cookiemask varchar(25) binary NULL ,
	ipaddr varchar(15) binary NULL ,
	length int NOT NULL ,
	price float NOT NULL ,
	money float NOT NULL ,
	PRIMARY KEY (userid)
)

CREATE TABLE contact (
	ipaddr varchar(15) binary NOT NULL ,
	contacttime int NOT NULL ,
	PRIMARY KEY (ipaddr)
)

CREATE TABLE addusertime (
	userid varchar(20) binary NOT NULL ,
	timelength int NOT NULL ,
	datalength int NOT NULL ,
	PRIMARY KEY (userid)
)

CREATE TABLE specialtime (
	xh int NOT NULL ,
	starttime1 int NOT NULL ,
	starttime2 int NOT NULL ,
	timelength int NOT NULL ,
	discount int NOT NULL ,
	flag char(1) binary NOT NULL ,
	PRIMARY KEY (xh)
)

CREATE TABLE cash (
	userid varchar(20) binary NOT NULL ,
	transtime varchar(20) binary NOT NULL ,
	money float NOT NULL ,
	spare float NOT NULL ,
	flag char(1) binary NOT NULL ,
	operator varchar(20) binary NOT NULL ,
	PRIMARY KEY (userid,transtime,flag)
)

CREATE TABLE types (
	typeid varchar(4) binary NOT NULL ,
	name varchar(80) binary NOT NULL ,
	price float NOT NULL ,
	typemode char(1) binary NOT NULL ,
	unit int NOT NULL ,
	limittype char(1) binary NOT NULL ,
	limithour int NOT NULL ,
	limitflow int NOT NULL ,
	overprice1 float NOT NULL ,
	overprice2 float NOT NULL ,
	maxmoney float NOT NULL ,
	PRIMARY KEY (typeid)
)

insert into types values('00','免费',0,'0',0,'0',0,0,0,0,0)

insert into types values('01','先购买后使用（按分钟计费）',0.2,'1',1,'0',0,0,0,0,0)

insert into types values('02','先购买后使用（按小时计费）',5,'1',2,'0',0,0,0,0,0)

insert into types values('03','先购买后使用（按天计费）',30,'1',3,'0',0,0,0,0,0)

insert into types values('04','先购买后使用（按月计费，无限制）',200,'1',4,'0',0,0,0,0,0)

insert into types values('05','先购买后使用（按月计费，150小时限制）',150,'1',4,'2',150,0,0,0,0)

insert into types values('06','先购买后使用（按月计费，120小时限制）',120,'1',4,'2',120,0,0,0,0)

insert into types values('07','先购买后使用（按月计费，90小时限制）',90,'1',4,'2',90,0,0,0,0)

insert into types values('08','先购买后使用（按年计费）',1000,'1',5,'0',0,0,0,0,0)

insert into types values('09','先购买后使用（按流量M计费）',1,'1',6,'0',0,0,0,0,0)

insert into types values('10','先购买后使用（按流量G计费）',600,'1',7,'0',0,0,0,0,0)

insert into types values('11','先使用后结算（按分钟结算）',0.2,'2',1,'0',0,0,0,0,0)

insert into types values('12','先使用后结算（按小时结算，每天无封顶）',5,'2',2,'0',0,0,0,0,0)

insert into types values('13','先使用后结算（按小时结算，每天30元封顶）',5,'2',2,'0',0,0,0,0,30)

insert into types values('14','先使用后结算（按天结算）',30,'2',3,'0',0,0,0,0,0)

insert into types values('15','先使用后结算（按月结算，无限制）',200,'2',4,'0',0,0,0,0,0)

insert into types values('16','先使用后结算（按月结算，150小时限制，超过部分5元/小时）',150,'2',4,'1',150,0,5,0,0)

insert into types values('17','先使用后结算（按月结算，120小时限制，超过部分5元/小时）',120,'2',4,'1',120,0,5,0,0)

insert into types values('18','先使用后结算（按月结算，90小时限制，超过部分5元/小时）',90,'2',4,'1',90,0,5,0,0)

insert into types values('19','先使用后结算（按年结算）',1000,'2',5,'0',0,0,0,0,0)

insert into types values('20','先使用后结算（按流量M结算）',1,'2',6,'0',0,0,0,0,0)

insert into types values('21','先使用后结算（按流量G结算）',600,'2',7,'0',0,0,0,0,0)

insert into module values('1001','新增操作员')

insert into module values('1002','操作员管理')

insert into module values('1003','操作员密码修改')

insert into module values('1004','操作历史记录查询')

insert into module values('1007','组上网范围管理')

insert into module values('1008','用户上网范围管理')

insert into module values('1009','组上网带宽管理')

insert into module values('1010','用户上网带宽管理')

insert into module values('2001','计费类型管理')

insert into module values('2002','注册用户')

insert into module values('2003','上网续费')

insert into module values('2004','上网结帐')

insert into module values('2005','注销用户')

insert into module values('2006','上网清单查询')

insert into module values('2007','用户查询')

insert into module values('2008','用户属性修改')

insert into module values('2009','时间类型用户到期列表')

insert into module values('2010','流量类型用户到限列表')

insert into module values('2011','本月到期用户列表')

insert into module values('2012','先使用后结算类型用户上网统计列表')

insert into module values('2013','每日注册用户清单')

insert into module values('2014','每日结帐用户清单')

insert into module values('2015','每日续费用户清单')

insert into module values('2016','每月注册用户清单')

insert into module values('2017','每月结帐用户清单')

insert into module values('2018','每月续费用户清单')

insert into module values('2019','统计日报表')

insert into module values('2020','统计月报表')

insert into module values('2021','统计年报表')

insert into module values('2022','时间段统计报表')

insert into module values('2023','恢复注销用户')

insert into module values('2024','当前正在上网用户清单')

insert into module values('2025','清除历史数据')

insert into module values('2026','更换计费类型')

insert into module values('2027','缴费清单查询')

insert into module values('2028','批量用户管理')

insert into module values('2029','用户组管理')

insert into module values('2030','VIP用户管理')

insert into module values('2031','退费处理')

insert into module values('2032','组每天允许上网时间管理')

insert into module values('2033','用户每天允许上网时间管理')

insert into module values('2034','每日退费用户清单')

insert into module values('2035','每月退费用户清单')

insert into module values('2036','自助注册确认生效')

insert into module values('2037','清除注销用户')

insert into module values('2038','现金收支查询')

insert into module values('2039','优惠时段管理')

insert into actions values('01','操作登录')

insert into actions values('02','操作退出')

insert into actions values('03','新增操作员')

insert into actions values('04','修改操作员权限')

insert into actions values('05','删除操作员')

insert into actions values('06','开通上网用户')

insert into actions values('07','注销用户')

insert into actions values('08','恢复注销用户')

insert into actions values('09','用户续费')

insert into actions values('10','用户结帐')

insert into actions values('11','添加计费类型')

insert into actions values('12','添加用户优惠等级信息')

insert into actions values('13','修改用户优惠等级信息')

insert into actions values('14','删除用户优惠等级信息')

insert into actions values('15','批量注册用户')

insert into actions values('16','批量激活用户')

insert into actions values('17','添加优惠等级')

insert into actions values('18','修改优惠等级')

insert into actions values('19','删除优惠等级')

insert into actions values('20','组续费')

insert into actions values('21','创建新用户组')

insert into actions values('22','修改用户组')

insert into actions values('23','删除用户组')

insert into actions values('24','删除组别成员')

insert into actions values('25','添加组别成员')

insert into actions values('26','激活用户')

insert into actions values('27','修改用户属性')

insert into actions values('28','更换计费类型')

insert into actions values('29','修改组上网范围')

insert into actions values('30','修改用户上网范围')

insert into actions values('31','修改计费类型')

insert into actions values('32','删除计费类型')

insert into actions values('33','添加组上网带宽')

insert into actions values('34','删除组上网带宽')

insert into actions values('35','添加用户上网带宽')

insert into actions values('36','删除用户上网带宽')

insert into actions values('37','用户退费')

insert into actions values('38','设置组每天允许上网时间')

insert into actions values('39','设置用户每天允许上网时间')

insert into actions values('40','清除注销用户')

insert into actions values('41','设置欠费状态')

insert into actions values('42','增加优惠时段')

insert into actions values('43','删除优惠时段')

insert into specialtime values(1,12,31,2,0,'1')

insert into specialtime values(2,10,1,7,0,'1')

insert into specialtime values(3,6,0,48,50,'2')

insert into specialtime values(4,0,0,7,50,'3')

insert into operators values('sip','系统管理员','sip','11111111111111111111111111111111111111111111111')
