drop table if exists account

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 useracl1

drop table if exists useracl2

drop table if exists useracl3

drop table if exists rooms

drop table if exists userband

drop table if exists contact

drop table if exists addusertime

drop table if exists cash

CREATE TABLE account ( 
	userid varchar(20) binary NOT NULL ,
	loginname varchar(20) binary NOT NULL ,
	password varchar(50) binary NOT NULL ,
	type varchar(8) binary NULL ,
	addtime varchar(20) binary NOT NULL ,
	checktime varchar(20) binary NOT NULL ,
	usetime float NOT NULL ,
	datalength float NOT NULL ,
	price float NOT NULL ,
	money1 float NOT NULL ,
	discount int NOT NULL ,
	money2 float NOT NULL ,
	comment varchar(50) binary NULL ,
	PRIMARY KEY(userid)
)

CREATE TABLE history ( 
	ipaddr varchar(15) binary NOT NULL ,
	userid varchar(20) binary 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, 
	PRIMARY KEY(ipaddr,starttime)
)

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 ,
	PRIMARY KEY(ipaddr)
)

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

CREATE TABLE types ( 
	name varchar(8) binary NOT NULL ,
	price float NOT NULL ,
	orderid int NULL ,
	PRIMARY KEY(name)
)

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 NULL ,
	onlines int NULL ,
	addtime varchar(20) binary NOT NULL ,
	checked char(1) binary NOT NULL ,
	cookiemask varchar(25) binary NULL ,
	suspendtime varchar(20) binary NULL ,
	prepay int NULL ,
	PRIMARY KEY(userid)
)

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

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

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

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 rooms ( 
	loginname varchar(20) binary NOT NULL ,
	price float NOT NULL ,
	PRIMARY KEY(loginname)
)

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

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 cash (
	userid varchar(20) binary NOT NULL ,
	transtime varchar(20) binary NOT NULL ,
	money float NOT NULL ,
	flag char(1) binary NOT NULL ,
	operator varchar(20) binary NOT NULL ,
	PRIMARY KEY (transtime,operator)
)

insert into types values('天',20,2)

insert into types values('小时',2,1)

insert into types values('分钟',0.1,3)

insert into types values('包月',100,4)

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

insert into types values('流量(M)',2,5)

insert into types values('流量(G)',300,6)

insert into types values('年',800,8)

insert into module values('0001','日报表')

insert into module values('0002','月报表')

insert into module values('0003','每日开通客人清单')

insert into module values('0004','每日结帐客人清单')

insert into module values('0005','当前所有开通客人清单')

insert into module values('0006','结帐清单月报表')

insert into module values('0007','上网开号')

insert into module values('0008','结帐销号')

insert into module values('0009','用户类型结算价格修改')

insert into module values('0010','客人上网密码查询')

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

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

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

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

insert into module values('0015','换房处理')

insert into module values('0016','客人上网清单查询')

insert into module values('0017','当前正在上网客人清单')

insert into module values('0018','换房记录查询')

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

insert into module values('0020','年报表')

insert into module values('0021','更改用户属性')

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

insert into module values('0023','特殊客房单价管理')

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

insert into module values('0025','暂停（恢复）客人上网')

insert into module values('0026','现金收支表')

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