# MySQL dump 6.4
#
# Host: localhost    Database: ispdata
#--------------------------------------------------------
# Server version	3.22.27

#
# Table structure for table 'AccessFlags'
#
CREATE TABLE AccessFlags (
  FlagID char(1) DEFAULT '' NOT NULL,
  FlagName varchar(32) DEFAULT '' NOT NULL,
  PRIMARY KEY (FlagID)
);

#
# Table structure for table 'AccountTypes'
#
CREATE TABLE AccountTypes (
  AccountTypeName varchar(15) DEFAULT '' NOT NULL,
  Description varchar(30),
  GenerateSlip tinyint(4),
  GenerateMail tinyint(4),
  DefaultDuration int(11),
  Charge double(16,4),
  AccountTypeID int(11) DEFAULT '0' NOT NULL auto_increment,
  PRIMARY KEY (AccountTypeName),
  UNIQUE AccountTypeID (AccountTypeID)
);

#
# Table structure for table 'Answers'
#
CREATE TABLE Answers (
  AnswerID int(11) DEFAULT '0' NOT NULL auto_increment,
  QuestionID int(11) DEFAULT '0' NOT NULL,
  Answer text NOT NULL,
  Answerer varchar(32) DEFAULT '' NOT NULL,
  Atime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY (AnswerID),
  KEY QuestionID (QuestionID)
);

#
# Table structure for table 'Changes'
#
CREATE TABLE Changes (
  ChangeID int(11) DEFAULT '0' NOT NULL auto_increment,
  ChangeUser varchar(32) DEFAULT '' NOT NULL,
  ChangeDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  ChangeText text NOT NULL,
  CustomerID int(11) DEFAULT '0' NOT NULL,
  IPAddress varchar(16) DEFAULT '' NOT NULL,
  PRIMARY KEY (ChangeID),
  KEY CustomerID (CustomerID)
);

#
# Table structure for table 'Dealer'
#
CREATE TABLE Dealer (
  DealerID int(11) DEFAULT '0' NOT NULL auto_increment,
  DealerName varchar(50),
  DealerLogin varchar(50) DEFAULT '' NOT NULL,
  DealerPassword varchar(50),
  MasterAccountID int(11),
  Karma int(11) DEFAULT '0' NOT NULL,
  ResellerCommission float(6,4) DEFAULT '0.0000' NOT NULL,
  AgentCommission float(6,4) DEFAULT '0.0000' NOT NULL,
  CreditCardNum varchar(20) DEFAULT '' NOT NULL,
  CreditCardExp date DEFAULT '0000-00-00' NOT NULL,
  Agent tinyint(4) DEFAULT '0' NOT NULL,
  ABN varchar(16) DEFAULT '' NOT NULL,
  JoiningFee float(4,2) DEFAULT '0.00' NOT NULL,
  JoiningFeeComm float(6,4) DEFAULT '0.0000' NOT NULL,
  DealerIP varchar(16) DEFAULT '' NOT NULL,
  PRIMARY KEY (DealerID),
  UNIQUE DealerLogin (DealerLogin)
);

#
# Table structure for table 'Exchanges'
#
CREATE TABLE Exchanges (
  ExchangeID int(11) DEFAULT '0' NOT NULL auto_increment,
  ExchangeName varchar(32) DEFAULT '' NOT NULL,
  PRIMARY KEY (ExchangeID),
  UNIQUE ExchangeName (ExchangeName)
);

#
# Table structure for table 'Invoices'
#
CREATE TABLE Invoices (
  PaymentID int(11) DEFAULT '0' NOT NULL auto_increment,
  CustomerID int(11),
  PaymentType varchar(50),
  PaymentAmount double(6,2),
  PaymentDate datetime,
  RecallPhone varchar(50),
  Purchase varchar(50),
  RecallName varchar(50),
  ResellerOriginal mediumint(9) DEFAULT '0' NOT NULL,
  ResellerPayment mediumint(9) DEFAULT '0' NOT NULL,
  ResellerUser mediumint(9) DEFAULT '0' NOT NULL,
  identifier varchar(32) DEFAULT '' NOT NULL,
  ResellerCommission float(4,2) DEFAULT '0.00' NOT NULL,
  AgentCommission float(4,2) DEFAULT '0.00' NOT NULL,
  Credit tinyint(4) DEFAULT '0' NOT NULL,
  GST float(4,2) DEFAULT '0.00' NOT NULL,
  PRIMARY KEY (PaymentID)
);

#
# Table structure for table 'MasterAccounts'
#
CREATE TABLE MasterAccounts (
  CustomerID int(11) DEFAULT '0' NOT NULL auto_increment,
  FirstName varchar(25) DEFAULT '' NOT NULL,
  LastName varchar(25) DEFAULT '' NOT NULL,
  Company varchar(35),
  Address varchar(64),
  Suburb varchar(25),
  State char(2),
  Postcode varchar(12),
  PhoneHome varchar(20),
  PhoneWork varchar(20),
  PhoneFax varchar(20),
  CreateDate datetime,
  StartDate datetime,
  ExpireDate datetime,
  Active tinyint(4) DEFAULT '0' NOT NULL,
  Comments mediumtext,
  LastModifyDate datetime,
  LastModifyUser varchar(32),
  PhoneMobile varchar(50),
  CreditCardNumbor varchar(50),
  CreditCardName varchar(50),
  CreditCardExp date,
  ComputerType varchar(50),
  OperatingSystem varchar(50),
  ModemType varchar(50),
  AutoBill mediumint(9),
  AutoBillType varchar(50),
  TimeRemaining int(11),
  TimePurchased int(11),
  TotalTime int(11),
  TotalKBytesOut int(11),
  TotalTimeThisWeek int(11),
  TotalKBytesThisWeek int(11),
  KByteQuota int(11),
  ExcessCharge int(11),
  KBytesRemaining int(11),
  LowCreditPoint int(11),
  ExpirePoint int(11),
  LastWarning datetime,
  LastUse datetime,
  ContactEmail varchar(50),
  VISP mediumint(9) DEFAULT '0' NOT NULL,
  Reseller mediumint(9) DEFAULT '0' NOT NULL,
  timeusedtoday smallint(6) DEFAULT '0' NOT NULL,
  timeusedmonth mediumint(9) DEFAULT '0' NOT NULL,
  perday smallint(6) DEFAULT '0' NOT NULL,
  permonth mediumint(9) DEFAULT '0' NOT NULL,
  NextCall datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  RecurDealer mediumint(9) DEFAULT '0' NOT NULL,
  QuotaAction tinyint(4) DEFAULT '0' NOT NULL,
  Browser varchar(48) DEFAULT '' NOT NULL,
  ABN varchar(16) DEFAULT '' NOT NULL,
  CompanyNumber varchar(24) DEFAULT '' NOT NULL,
  CompanyAddress varchar(255) DEFAULT '' NOT NULL,
  NextCallReason varchar(32) DEFAULT '' NOT NULL,
  Supplier tinyint(4) DEFAULT '0' NOT NULL,
  LastAccessDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  LastAccessUser varchar(32) DEFAULT '' NOT NULL,
  PRIMARY KEY (CustomerID),
  KEY FirstName (FirstName),
  KEY LastName (LastName),
  KEY NextCall (NextCall)
);

#
# Table structure for table 'NoteTypes'
#
CREATE TABLE NoteTypes (
  NoteTypeID tinyint(4) DEFAULT '0' NOT NULL auto_increment,
  NoteTypeName varchar(32) DEFAULT '' NOT NULL,
  PRIMARY KEY (NoteTypeID)
);

#
# Table structure for table 'Notes'
#
CREATE TABLE Notes (
  NoteID int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
  NoteDate datetime,
  NoteAuthor varchar(50),
  CustomerID int(11) DEFAULT '0' NOT NULL,
  NoteText mediumtext,
  NoteType tinyint(4) DEFAULT '0' NOT NULL,
  PRIMARY KEY (NoteID),
  KEY CustomerID (CustomerID)
);

#
# Table structure for table 'OutageSeverity'
#
CREATE TABLE OutageSeverity (
  OutageSeverityID tinyint(4) DEFAULT '0' NOT NULL,
  OutageSeverityName varchar(16) DEFAULT '' NOT NULL,
  UNIQUE OutageSeverityID (OutageSeverityID)
);

#
# Table structure for table 'Outages'
#
CREATE TABLE Outages (
  OutageID int(11) DEFAULT '0' NOT NULL auto_increment,
  OutageDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY (OutageID)
);

#
# Table structure for table 'PaymentTypes'
#
CREATE TABLE PaymentTypes (
  PaymentTypeID int(11) DEFAULT '0' NOT NULL auto_increment,
  PaymentTypeName varchar(16) DEFAULT '' NOT NULL,
  CreditCheck tinyint(4) DEFAULT '0' NOT NULL,
  PRIMARY KEY (PaymentTypeID)
);

#
# Table structure for table 'Payments'
#
CREATE TABLE Payments (
  PaymentID int(11) DEFAULT '0' NOT NULL auto_increment,
  CustomerID int(11) DEFAULT '0' NOT NULL,
  PaymentType varchar(50),
  PaymentAmount double(6,2),
  PaymentDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  RecallPhone varchar(50),
  Purchase varchar(50),
  RecallName varchar(50),
  ResellerOriginal mediumint(9) DEFAULT '0' NOT NULL,
  ResellerPayment mediumint(9) DEFAULT '0' NOT NULL,
  ResellerUser mediumint(9) DEFAULT '0' NOT NULL,
  identifier varchar(32) DEFAULT '' NOT NULL,
  ResellerCommission float(4,2) DEFAULT '0.00' NOT NULL,
  AgentCommission float(4,2) DEFAULT '0.00' NOT NULL,
  Credit tinyint(4) DEFAULT '0' NOT NULL,
  GST float(4,2) DEFAULT '0.00' NOT NULL,
  RequestDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY (PaymentID)
);

#
# Table structure for table 'PendingPayments'
#
CREATE TABLE PendingPayments (
  PaymentID int(11) DEFAULT '0' NOT NULL auto_increment,
  CustomerID int(11),
  PaymentType varchar(50),
  PaymentAmount double(6,2),
  PaymentDate datetime,
  RecallPhone varchar(50),
  Purchase varchar(50),
  RecallName varchar(50),
  ResellerOriginal mediumint(9) DEFAULT '0' NOT NULL,
  ResellerPayment mediumint(9) DEFAULT '0' NOT NULL,
  ResellerUser mediumint(9) DEFAULT '0' NOT NULL,
  identifier varchar(32) DEFAULT '' NOT NULL,
  ResellerCommission float(4,2) DEFAULT '0.00' NOT NULL,
  AgentCommission float(4,2) DEFAULT '0.00' NOT NULL,
  Credit tinyint(4) DEFAULT '0' NOT NULL,
  GST float(4,2) DEFAULT '0.00' NOT NULL,
  DeclinedWarning datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY (PaymentID)
);

#
# Table structure for table 'PlanTypes'
#
CREATE TABLE PlanTypes (
  PlanID int(11) DEFAULT '0' NOT NULL auto_increment,
  AccountType varchar(50) DEFAULT '' NOT NULL,
  Cost float(5,2),
  TimeRemaining mediumint(11),
  PlanBytes int(11),
  Months smallint(11),
  PlanName varchar(50),
  Filter varchar(50),
  Route varchar(50),
  MaxIdle int(11),
  MaxCon int(11),
  MaxLogins int(11),
  WeeklyDownloadLimit int(11),
  ExcessCharge int(11),
  MailBoxSize int(11),
  HourlyRate int(11),
  permonth mediumint(9) DEFAULT '0' NOT NULL,
  AccessFlags varchar(8) DEFAULT '' NOT NULL,
  Days smallint(6) DEFAULT '0' NOT NULL,
  StartMinutes smallint(6) DEFAULT '0' NOT NULL,
  EndMinutes smallint(6) DEFAULT '0' NOT NULL,
  PRIMARY KEY (PlanID),
  KEY AccountType (AccountType)
);

#
# Table structure for table 'Prefix'
#
CREATE TABLE Prefix (
  Prefix varchar(8) DEFAULT '' NOT NULL,
  ExchangeID int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (Prefix)
);

#
# Table structure for table 'PurchaseCodes'
#
CREATE TABLE PurchaseCodes (
  CodeID int(11) DEFAULT '0' NOT NULL,
  CodeName varchar(32) DEFAULT '' NOT NULL,
  Category varchar(32) DEFAULT '' NOT NULL,
  PRIMARY KEY (CodeID),
  KEY Category (Category)
);

#
# Table structure for table 'PurchaseGroup'
#
CREATE TABLE PurchaseGroup (
  PurchaseGroupID int(11) DEFAULT '0' NOT NULL auto_increment,
  PGName varchar(32) DEFAULT '' NOT NULL,
  PRIMARY KEY (PurchaseGroupID),
  UNIQUE PGName (PGName)
);

#
# Table structure for table 'Purchases'
#
CREATE TABLE Purchases (
  PurchaseID int(11) DEFAULT '0' NOT NULL auto_increment,
  SupplierID int(11) DEFAULT '0' NOT NULL,
  PurchaseDesc text NOT NULL,
  Cost float(6,2) DEFAULT '0.00' NOT NULL,
  GST float(5,2) DEFAULT '0.00' NOT NULL,
  PurchaseDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PaymentType varchar(16) DEFAULT '' NOT NULL,
  Identifier varchar(32) DEFAULT '' NOT NULL,
  SupplierCode varchar(32) DEFAULT '' NOT NULL,
  ItemOnly int(11) DEFAULT '0' NOT NULL,
  ChequeID varchar(32) DEFAULT '' NOT NULL,
  PurchaseCode int(11) DEFAULT '0' NOT NULL,
  Approved tinyint(4) DEFAULT '0' NOT NULL,
  PRIMARY KEY (PurchaseID),
  KEY Approved (Approved)
);

#
# Table structure for table 'Questions'
#
CREATE TABLE Questions (
  QuestionID int(11) DEFAULT '0' NOT NULL auto_increment,
  Question text NOT NULL,
  Answer int(11) DEFAULT '0' NOT NULL,
  Asker varchar(32) DEFAULT '' NOT NULL,
  Qtime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  PRIMARY KEY (QuestionID)
);

#
# Table structure for table 'SQLChangeLog'
#
CREATE TABLE SQLChangeLog (
  ChangeID int(11) DEFAULT '0' NOT NULL auto_increment,
  ChangeUser varchar(16) DEFAULT '' NOT NULL,
  ChangeDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  ChangeIP varchar(16) DEFAULT '' NOT NULL,
  ChangeQuery text NOT NULL,
  PRIMARY KEY (ChangeID)
);

#
# Table structure for table 'SecureIP'
#
CREATE TABLE SecureIP (
  ID int(11),
  IPAddress varchar(50),
  SecurityLevel int(11)
);

#
# Table structure for table 'SubAccounts'
#
CREATE TABLE SubAccounts (
  AccountID int(11) DEFAULT '0' NOT NULL auto_increment,
  CustomerID int(11) DEFAULT '0' NOT NULL,
  Login varchar(24) DEFAULT '' NOT NULL,
  AccountType varchar(16) DEFAULT '' NOT NULL,
  Password varchar(12) DEFAULT '1' NOT NULL,
  Active tinyint(4) DEFAULT '1' NOT NULL,
  LoginLimit int(11),
  saStartDate datetime,
  Value1 varchar(50),
  Value2 varchar(50),
  Value3 varchar(50),
  Route varchar(50),
  IdleTime int(11),
  MaxConnectTime int(11),
  Domain varchar(50),
  MailDir varchar(50),
  MailboxSize int(11),
  VISP tinyint(4) DEFAULT '1' NOT NULL,
  AccessFlags varchar(8) DEFAULT 'd' NOT NULL,
  FixedIPAddress varchar(16) DEFAULT '' NOT NULL,
  SubExpire datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  EmailEnabled tinyint(4) DEFAULT '1' NOT NULL,
  LastAttemptedPassword varchar(32) DEFAULT '' NOT NULL,
  DeadDate date DEFAULT '0000-00-00' NOT NULL,
  StartMinutes smallint(6) DEFAULT '0' NOT NULL,
  EndMinutes smallint(6) DEFAULT '0' NOT NULL,
  WebDir varchar(32) DEFAULT '' NOT NULL,
  IsPrimary tinyint(4) DEFAULT '0' NOT NULL,
  LastCLI varchar(12) DEFAULT '' NOT NULL,
  LastTerminateCause varchar(32) DEFAULT '' NOT NULL,
  ForwardEmail varchar(64),
  LastModifyDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  Alias varchar(32) DEFAULT '' NOT NULL,
  PRIMARY KEY (AccountID),
  UNIQUE Login (Login),
  KEY AccountType (AccountType),
  KEY VISP (VISP),
  KEY CustomerID (CustomerID),
  KEY Active (Active)
);

#
# Table structure for table 'Todo'
#
CREATE TABLE Todo (
  TodoID int(11) DEFAULT '0' NOT NULL auto_increment,
  TodoUser varchar(32) DEFAULT '' NOT NULL,
  TodoDateAdded datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  TodoDeadline datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  TodoDesc text NOT NULL,
  TodoStartDate datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  Priority tinyint(4) DEFAULT '0' NOT NULL,
  AddedBy tinyint(4) DEFAULT '0' NOT NULL,
  Cost float(4,2) DEFAULT '0.00' NOT NULL,
  Approved varchar(32) DEFAULT '' NOT NULL,
  Status varchar(64) DEFAULT '' NOT NULL,
  PRIMARY KEY (TodoID),
  KEY TodoUser (TodoUser,TodoDeadline)
);

#
# Table structure for table 'VISP'
#
CREATE TABLE VISP (
  VISPID int(11) DEFAULT '0' NOT NULL auto_increment,
  VISPName varchar(24) DEFAULT '' NOT NULL,
  Indial varchar(16) DEFAULT '' NOT NULL,
  EmailDomain varchar(32) DEFAULT '' NOT NULL,
  AccessFlags varchar(8) DEFAULT '' NOT NULL,
  PRIMARY KEY (VISPID),
  KEY VISPName (VISPName,Indial),
  KEY Indial (Indial)
);

#
# Table structure for table 'ops'
#
CREATE TABLE ops (
  username varchar(10) DEFAULT '' NOT NULL,
  password varchar(16) DEFAULT '' NOT NULL,
  security tinyint(4) DEFAULT '1' NOT NULL,
  PRIMARY KEY (username),
  KEY username (username)
);

#
# Table structure for table 'radacct'
#
CREATE TABLE radacct (
  NASIPAddress varchar(16),
  NASPortId int(11),
  AcctSessionId varchar(32) DEFAULT '0' NOT NULL,
  AcctStatusType varchar(16) DEFAULT '' NOT NULL,
  realtime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  UserName varchar(24) DEFAULT '' NOT NULL,
  AcctAuthentic varchar(16),
  UserService smallint(6),
  LoginService smallint(6),
  LoginHost varchar(16),
  AcctDelayTime int(11),
  AcctSessionTime int(11),
  FramedProtocol int(11),
  FramedIPAddress varchar(16),
  AcctInputOctets int(11),
  AcctOutputOctets int(11),
  AcctInputPackets int(11),
  AcctOutputPackets int(11),
  NASPortType varchar(6),
  CallingStationId varchar(10),
  BlocksUsed int(11),
  WeekNumber smallint(6) DEFAULT '0' NOT NULL,
  AcctTerminateCause varchar(50),
  ConnectInfo varchar(32) DEFAULT '' NOT NULL,
  CalledStationId varchar(10) DEFAULT '' NOT NULL,
  Tstamp timestamp(12),
  radacctid int(11) DEFAULT '0' NOT NULL,
  KEY UserName (UserName),
  KEY radacct_date_idx (realtime),
  PRIMARY KEY (AcctSessionId,AcctStatusType),
  KEY weekidx (WeekNumber)
);

#
# Table structure for table 'radacctarchive'
#
CREATE TABLE radacctarchive (
  NASIPAddress varchar(16),
  NASPortId int(11),
  AcctSessionId varchar(32) DEFAULT '0' NOT NULL,
  AcctStatusType varchar(16) DEFAULT '' NOT NULL,
  realtime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  UserName varchar(24) DEFAULT '' NOT NULL,
  AcctAuthentic varchar(16),
  UserService smallint(6),
  LoginService smallint(6),
  LoginHost varchar(16),
  AcctDelayTime int(11),
  AcctSessionTime int(11),
  FramedProtocol int(11),
  FramedIPAddress varchar(16),
  AcctInputOctets int(11),
  AcctOutputOctets int(11),
  AcctInputPackets int(11),
  AcctOutputPackets int(11),
  NASPortType varchar(6),
  CallingStationId varchar(10),
  BlocksUsed int(11),
  WeekNumber smallint(6) DEFAULT '0' NOT NULL,
  AcctTerminateCause varchar(50),
  ConnectInfo varchar(32) DEFAULT '' NOT NULL,
  CalledStationId varchar(10) DEFAULT '' NOT NULL,
  Tstamp timestamp(12),
  radacctarchiveid int(11) DEFAULT '0' NOT NULL,
  KEY UserName (UserName),
  KEY radacctarchive_date_idx (realtime),
  PRIMARY KEY (AcctSessionId,AcctStatusType),
  KEY weekidx (WeekNumber)
);

#
# Table structure for table 'testac'
#
CREATE TABLE testac (
  object_id int(11),
  object_title varchar(64)
);

#
# Table structure for table 'who'
#
CREATE TABLE who (
  RadacctID bigint(21) DEFAULT '0' NOT NULL,
  AcctSessionId varchar(32) DEFAULT '' NOT NULL,
  UserName varchar(32) DEFAULT '' NOT NULL,
  NASIPAddress varchar(15),
  NASPortId int(12),
  NASPortType varchar(32),
  AcctStatusType varchar(32),
  AcctSessionTime int(12),
  AcctAuthentic varchar(32),
  ConnectInfo varchar(32),
  AcctInputOctets int(12),
  AcctOutputOctets int(12),
  CalledStationId varchar(10) DEFAULT '' NOT NULL,
  CallingStationId varchar(10) DEFAULT '' NOT NULL,
  AcctTerminateCause varchar(32) DEFAULT '' NOT NULL,
  ServiceType varchar(32),
  FramedProtocol varchar(32),
  FramedIPAddress varchar(15) DEFAULT '' NOT NULL,
  AcctDelayTime int(12),
  Tstamp timestamp(14),
  realtime datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  AccountType varchar(16) DEFAULT '' NOT NULL,
  CustomerID int(11) DEFAULT '0' NOT NULL,
  AccDialPort smallint(6) DEFAULT '0' NOT NULL,
  StartBytes int(11) DEFAULT '0' NOT NULL,
  KEY UserName (UserName),
  KEY AccountType (AccountType),
  PRIMARY KEY (AcctSessionId)
);