drop table if exists login

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 ,
	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 ,
        timelength int NOT NULL ,
        datalength int NOT NULL ,
	userdept varchar(50) binary NULL ,
        daytime int NOT NULL ,
        daydata int NOT NULL ,
	mac varchar(20) binary NULL ,
	PRIMARY KEY (userid)
)

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 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 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 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)
)

ALTER TABLE users ADD daytime int NULL

ALTER TABLE users ADD daydata int NULL

ALTER TABLE users ADD mac varchar(20) binary NULL

update users set mac='' where mac is null

update users set daytime=0 where daytime is null

update users set daydata=0 where daydata is null

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

insert into module values('27','上网时间排序表')

insert into module values('28','上网流量排序表')

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 operators values('sip','系统管理员','sip','1111111111111111111111111111')
