insert into NASYEAR values(1002,'192.168.0.200',add_months(sysdate,3),1432.5,32233,140);
insert into NASYEAR values(1003,'192.168.0.200',add_months(sysdate,4),3432.5,2833,70);
insert into NASYEAR values(1004,'192.168.0.200',add_months(sysdate,5),9432.5,32333,120);
insert into NASYEAR values(1005,'192.168.0.200',add_months(sysdate,6),9432.5,62333,800);
insert into NASYEAR values(1006,'192.168.0.200',add_months(sysdate,7),3232.5,32333,500);
insert into NASYEAR values(1007,'192.168.0.200',add_months(sysdate,8),9432.5,1333,440);
insert into NASYEAR values(1008,'192.168.0.200',add_months(sysdate,9),8432.5,52333,300);
insert into NASYEAR values(1009,'192.168.0.200',add_months(sysdate,10),9432.5,42333,800);
insert into NASYEAR values(1010,'192.168.0.200',add_months(sysdate,11),5432.5,32333,230);
insert into NASYEAR values(1011,'192.168.0.200',add_months(sysdate,12),7432.5,22333,560);


create table USERDAY
(
  ID            NUMBER primary key,
  USERNAME      VARCHAR2(30) not null,
  NASIPADDRESS  VARCHAR2(15),
  ONDATE        DATE,
  TIME_DURATION NUMBER,
  FLUX          NUMBER
);
comment on column USERDAY.USERNAME is '業務帳號';
comment on column USERDAY.NASIPADDRESS is 'nas ip 地址';
comment on column USERDAY.ONDATE is '時間 ';
comment on column USERDAY.TIME_DURATION is '時長總和';
comment on column USERDAY.FLUX is '流量總和';

create table USERMONTH
(
  ID            NUMBER primary key,
  USERNAME      VARCHAR2(30) not null,
  NASIPADDRESS  VARCHAR2(15),
  ONDATE        DATE,
  TIME_DURATION NUMBER,
  FLUX          NUMBER
);


comment on column USERMONTH.USERNAME is '業務帳號';
comment on column USERMONTH.NASIPADDRESS is 'nas ip 地址';
comment on column USERMONTH.ONDATE is '時間 ';
comment on column USERMONTH.TIME_DURATION is '時長總和';
comment on column USERMONTH.FLUX is '流量總和';
insert into usermonth values(100,'briup','192.168.0.200',sysdate,23213,213);
insert into usermonth values(101,'briup','192.168.0.200',sysdate+1,5413,123);
insert into usermonth values(102,'briup','192.168.0.200',sysdate+2,123,2413);
insert into usermonth values(103,'briup','192.168.0.200',sysdate+3,673,1423);

create table USERYEAR
(
  ID            NUMBER primary key,
  USERNAME      VARCHAR2(30) not null,
  NASIPADDRESS  VARCHAR2(15),
  ONDATE        DATE,
  TIME_DURATION NUMBER,
  FLUX          NUMBER
);

 
comment on column USERYEAR.USERNAME is '業務帳號';
comment on column USERYEAR.NASIPADDRESS is 'nasip地址';
comment on column USERYEAR.ONDATE is '時間';
comment on column USERYEAR.TIME_DURATION is '總時長';
comment on column USERYEAR.FLUX is '總流量';
insert into useryear values(100,'briup','192.168.0.200',add_months(sysdate,-1),23213,213);
insert into useryear values(101,'briup','192.168.0.200',add_months(sysdate,-1),54213,423);
insert into useryear values(102,'briup','192.168.0.200',add_months(sysdate,-1),15413,523);
insert into useryear values(103,'briup','192.168.0.200',add_months(sysdate,-1),35413,223);



create table BUSINESSMONTH
(
  ID           NUMBER  primary key,
  PRODUCT_NAME VARCHAR2(30),
  ONDATE       DATE,
  SUMFEE       NUMBER(20,2)
);

comment on column BUSINESSMONTH.ID is 'id號碼';
comment on column BUSINESSMONTH.PRODUCT_NAME is '產品名稱';
comment on column BUSINESSMONTH.ONDATE is '時間';
comment on column BUSINESSMONTH.SUMFEE is '營收之和';
insert into businessmonth values(1000,'包月制',sysdate,20003.50);
insert into businessmonth values(1001,'小時制',sysdate,30003.50);
insert into businessmonth values(1002,'幸福之家',sysdate,40003.50);
insert into businessmonth values(1003,'抄股專家',sysdate,60003.50);
insert into businessmonth values(1004,'企業高速公路',sysdate,90003.50);

create table BUSINESSYEAR
(
  ID           NUMBER primary key,
  PRODUCT_NAME VARCHAR2(30),
  ONDATE       DATE,
  SUMFEE       NUMBER(20,2)
);


comment on column BUSINESSYEAR.ID is 'id號碼';
comment on column BUSINESSYEAR.PRODUCT_NAME is '產品名稱';
comment on column BUSINESSYEAR.ONDATE is '時間';
comment on column BUSINESSYEAR.SUMFEE is '營收之和';
insert into businessyear values(1000,'包月制',sysdate,2044003.50);
insert into businessyear values(1001,'小時制',sysdate,3005503.50);
insert into businessyear values(1002,'幸福之家',sysdate,4066003.50);
insert into businessyear values(1003,'抄股專家',sysdate,6000773.50);
insert into businessyear values(1004,'企業高速公路',sysdate,9088003.50);


create table RADACCT
(
  RADACCTID          INTEGER primary key,
  ACCTSESSIONID      VARCHAR2(32) not null,
  ACCTUNIQUEID       VARCHAR2(32),
  USERNAME           VARCHAR2(32) not null,
  REALM              VARCHAR2(30),
  NASIPADDRESS       VARCHAR2(15) not null,
  NASPORTID          NUMBER(12),
  NASPORTTYPE        VARCHAR2(32),
  ACCTSTARTTIME      DATE,
  ACCTSTOPTIME       DATE,
  ACCTSESSIONTIME    NUMBER(12),
  ACCTAUTHENTIC      VARCHAR2(32),
  CONNECTINFO_START  VARCHAR2(32),
  CONNECTINFO_STOP   VARCHAR2(32),
  ACCTINPUTOCTETS    NUMBER(12),
  ACCTOUTPUTOCTETS   NUMBER(12),
  CALLEDSTATIONID    VARCHAR2(50),
  CALLINGSTATIONID   VARCHAR2(50),
  ACCTTERMINATECAUSE VARCHAR2(32),
  SERVICETYPE        VARCHAR2(32),
  FRAMEDPROTOCOL     VARCHAR2(32),
  FRAMEDIPADDRESS    VARCHAR2(15),
  ACCTSTARTDELAY     NUMBER(12),
  ACCTSTOPDELAY      NUMBER(12)
);
create unique index RADACCT_IDX1 on RADACCT (ACCTSESSIONID, USERNAME, ACCTSTARTTIME, ACCTSTOPTIME, NASIPADDRESS, FRAMEDIPADDRESS);
comment on column RADACCT.RADACCTID is 'id號碼';
comment on column RADACCT.ACCTSESSIONID is '用戶登陸的SessionID';
comment on column RADACCT.ACCTUNIQUEID is '記帳唯一ID';
comment on column RADACCT.USERNAME is '用戶名';
comment on column RADACCT.REALM is '域';
comment on column RADACCT.NASIPADDRESS is 'NAS 的IP 地址';
comment on column RADACCT.NASPORTID is 'NAS端口號';
comment on column RADACCT.NASPORTTYPE is '設備的端口類型';
comment on column RADACCT.ACCTSTARTTIME is '撥號開始時間';
comment on column RADACCT.ACCTSTOPTIME is '撥號結束時間';
comment on column RADACCT.ACCTSESSIONTIME is '用戶使用服務的時長';
comment on column RADACCT.ACCTAUTHENTIC is '計費認證方式';
comment on column RADACCT.CONNECTINFO_START is '連接開始';
comment on column RADACCT.CONNECTINFO_STOP is '連接結束';
comment on column RADACCT.ACCTINPUTOCTETS is '下載字節數量    單位: byte';
comment on column RADACCT.ACCTOUTPUTOCTETS is '上傳字節數量     單位: byte';
comment on column RADACCT.CALLEDSTATIONID is '曾經登陸用戶的網卡MAC地址';
comment on column RADACCT.CALLINGSTATIONID is '當前在線用戶網卡MAC地址';
comment on column RADACCT.ACCTTERMINATECAUSE is 'ADSL連接結束原因 ';
comment on column RADACCT.SERVICETYPE is '服務類型';
comment on column RADACCT.FRAMEDPROTOCOL is '用戶接入協議';
comment on column RADACCT.FRAMEDIPADDRESS is '用戶IP地址';
comment on column RADACCT.ACCTSTARTDELAY is '接入延遲             單位:second';
comment on column RADACCT.ACCTSTOPDELAY is '斷開延遲             單位:second';

create table RADCHECK
(
  ID        INTEGER primary key,
  USERNAME  VARCHAR2(30) not null,
  ATTRIBUTE VARCHAR2(30),
  OP        VARCHAR2(2) not null,
  VALUE     VARCHAR2(40),
  USERSID    NUMBER references USERS(ID),
  PRODUCTSID NUMBER references PRODUCTS(ID)
);
insert into radcheck values(11,'briup1','User-Password',':=','briup1',null,null);
insert into radcheck values(12,'briup','User-Password',':=','briup',null,null);

create table RADREPLY
(
  ID        INTEGER primary key,
  USERNAME  VARCHAR2(30) not null,
  ATTRIBUTE VARCHAR2(30),
  OP        VARCHAR2(2) not null,
  VALUE     VARCHAR2(40)
);


create table HV
(
	id NUMBER primary key,
	highvalue number
);

create sequence ADMINS_SEQ start with 1000;
create sequence USERS_SEQ start with 1000;
create sequence ROLES_SEQ start with 1000;
create sequence PRODUCTS_SEQ start with 1000;
create sequence RADCHECK_SEQ start with 1000;
create sequence RADREPLY_SEQ start with 1000;
CREATE SEQUENCE radacct_seq START WITH 1 INCREMENT BY 1;

drop sequence USERDAY_SEQ;
drop sequence USERMONTH_SEQ;
drop sequence USERYEAR_SEQ;
drop sequence NASDAY_SEQ;
drop sequence NASMONTH_SEQ;
drop sequence NASYEAR_SEQ;
create sequence USERDAY_SEQ start with 1000;
create sequence USERMONTH_SEQ start with 1000;
create sequence USERYEAR_SEQ start with 1000;
create sequence NASDAY_SEQ start with 1000;
create sequence NASMONTH_SEQ start with 1000;
create sequence NASYEAR_SEQ start with 1000;