a2billing-mysql-schema-mysql.5.x-v1.3.0.sql

asterisk用 的voip记费软件

--
-- A2Billing database script - Create user & create database for MYSQL 5.X
--

-- Usage:
-- mysql -u root -p"root password" < a2billing-mysql-schema-MYSQL.5.X-v1.2.0.sql 


--
-- A2Billing database - Create database schema
--
 



CREATE TABLE cc_didgroup (
    id 								BIGINT NOT NULL AUTO_INCREMENT,
    iduser 							INT DEFAULT 0 NOT NULL,
    didgroupname 					CHAR(50) NOT NULL,    
    creationdate   					TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;


CREATE TABLE cc_did_use (
    id 								BIGINT NOT NULL AUTO_INCREMENT,
    id_cc_card 						BIGINT ,
    id_did 							BIGINT NOT NULL,
    reservationdate 				TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    releasedate 					TIMESTAMP,
    activated 						INT	DEFAULT 0,
    month_payed 					INT DEFAULT 0,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;

CREATE TABLE cc_did (
    id 								BIGINT NOT NULL AUTO_INCREMENT,	
    id_cc_didgroup 					BIGINT NOT NULL,
    id_cc_country 					INT NOT NULL,    
    activated 						INT DEFAULT '1' NOT NULL,
    reserved 						INT DEFAULT '0',
    iduser 							INT DEFAULT '0' NOT NULL,
    did 							CHAR(50) NOT NULL,
    creationdate  					TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    startingdate  					TIMESTAMP,
    expirationdate 					TIMESTAMP,
    description 					MEDIUMTEXT,
    secondusedreal 					INT DEFAULT 0,
    billingtype 					INT DEFAULT 0,
    fixrate 						FLOAT DEFAULT 0 NOT NULL,
    PRIMARY KEY (id),
    UNIQUE cons_cc_did_did (did)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;


-- billtype: 0 = fix per month + dialoutrate, 1= fix per month, 2 = dialoutrate, 3 = free



CREATE TABLE cc_did_destination (
    id 									BIGINT NOT NULL AUTO_INCREMENT,	
    destination 						CHAR(50) NOT NULL,
    priority 							INT DEFAULT 0 NOT NULL,
    id_cc_card 							BIGINT NOT NULL,
    id_cc_did 							BIGINT NOT NULL,	
    creationdate  						TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    activated 							INT DEFAULT 1 NOT NULL,
    secondusedreal 						INT DEFAULT 0,	
    voip_call 							INT DEFAULT 0,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;




CREATE TABLE cc_charge (
    id 									BIGINT NOT NULL AUTO_INCREMENT,
    id_cc_card 							BIGINT NOT NULL,
    iduser 								INT DEFAULT '0' NOT NULL,
    creationdate 						TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    amount 								FLOAT DEFAULT 0 NOT NULL,
	currency 							CHAR(3) DEFAULT 'USD',
    chargetype 							INT DEFAULT 0,    
    description 						MEDIUMTEXT,
    id_cc_did 							BIGINT DEFAULT 0,
	id_cc_subscription_fee				BIGINT DEFAULT 0,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;

CREATE INDEX ind_cc_charge_id_cc_card				ON cc_charge (id_cc_card);
CREATE INDEX ind_cc_charge_id_cc_subscription_fee 	ON cc_charge (id_cc_subscription_fee);
CREATE INDEX ind_cc_charge_creationdate 			ON cc_charge (creationdate);



CREATE TABLE cc_paypal (
    id 								INT (11) NOT NULL AUTO_INCREMENT,
    payer_id 						VARCHAR(60) DEFAULT NULL,
    payment_date 					VARCHAR(50) DEFAULT NULL,
    txn_id 							VARCHAR(50) DEFAULT NULL,
    first_name 						VARCHAR(50) DEFAULT NULL,
    last_name 						VARCHAR(50) DEFAULT NULL,
    payer_email 					VARCHAR(75) DEFAULT NULL,
    payer_status 					VARCHAR(50) DEFAULT NULL,
    payment_type 					VARCHAR(50) DEFAULT NULL,
    memo 							TINYTEXT,
    item_name 						VARCHAR(127) DEFAULT NULL,
    item_number 					VARCHAR(127) DEFAULT NULL,
    quantity 						INT (11) NOT NULL DEFAULT '0',
    mc_gross 						DECIMAL(9,2) DEFAULT NULL,
    mc_fee 							DECIMAL(9,2) DEFAULT NULL,
    tax 							DECIMAL(9,2) DEFAULT NULL,
    mc_currency 					CHAR(3) DEFAULT NULL,
    address_name 					VARCHAR(255) NOT NULL DEFAULT '',
    address_street 					VARCHAR(255) NOT NULL DEFAULT '',
    address_city 					VARCHAR(255) NOT NULL DEFAULT '',
    address_state 					VARCHAR(255) NOT NULL DEFAULT '',
    address_zip 					VARCHAR(255) NOT NULL DEFAULT '',
    address_country 				VARCHAR(255) NOT NULL DEFAULT '',
    address_status 					VARCHAR(255) NOT NULL DEFAULT '',
    payer_business_name 			VARCHAR(255) NOT NULL DEFAULT '',
    payment_status					VARCHAR(255) NOT NULL DEFAULT '',
    pending_reason 					VARCHAR(255) NOT NULL DEFAULT '',
    reason_code 					VARCHAR(255) NOT NULL DEFAULT '',
    txn_type 						VARCHAR(255) NOT NULL DEFAULT '',
    PRIMARY KEY  (id),
    UNIQUE KEY txn_id (txn_id),
    KEY txn_id_2 (txn_id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;



CREATE TABLE cc_voucher (
    id 										BIGINT NOT NULL AUTO_INCREMENT,   
    creationdate 							TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    usedate 								TIMESTAMP,
    expirationdate 							TIMESTAMP,
    voucher 								CHAR(50) NOT NULL,
    usedcardnumber 							CHAR(50),
    tag 									CHAR(50),
    credit 									FLOAT DEFAULT 0 NOT NULL,
    activated 								CHAR(1) DEFAULT 'f' NOT NULL,
    used 									INT DEFAULT 0,    
    currency 								CHAR(3) DEFAULT 'USD',
    PRIMARY KEY (id),
    UNIQUE cons_cc_voucher_voucher (voucher)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;



CREATE TABLE cc_service (
    id 										BIGINT NOT NULL AUTO_INCREMENT,	
    name 									CHAR(100) NOT NULL, 
    amount 									FLOAT NOT NULL,	
    period 									INT NOT NULL DEFAULT '1',	
    rule 									INT NOT NULL DEFAULT '0',
    daynumber 								INT NOT NULL DEFAULT '0',
    stopmode 								INT NOT NULL DEFAULT '0',
    maxnumbercycle 							INT NOT NULL DEFAULT '0',	
    status 									INT NOT NULL DEFAULT '0',	
    numberofrun 							INT NOT NULL DEFAULT '0',	
    datecreate 								TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    datelastrun 							TIMESTAMP,
    emailreport 							CHAR(100) NOT NULL,
    totalcredit 							FLOAT NOT NULL DEFAULT '0',
    totalcardperform 						INT NOT NULL DEFAULT '0',
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;
	


CREATE TABLE cc_service_report (
    id 										BIGINT NOT NULL AUTO_INCREMENT,
    cc_service_id 							BIGINT NOT NULL,
    daterun 								TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    totalcardperform 						INT ,
    totalcredit 							FLOAT ,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;



CREATE TABLE cc_callerid (
    id 										BIGINT NOT NULL AUTO_INCREMENT,
    cid 									CHAR(100) NULL,
    id_cc_card 								BIGINT NOT NULL,
    activated 								CHAR(1) DEFAULT 't' NOT NULL,
    PRIMARY KEY (id),
    UNIQUE cons_cc_callerid_cid (cid)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;


CREATE TABLE cc_ui_authen (
    userid 									BIGINT NOT NULL AUTO_INCREMENT,
    login 									CHAR(50) NOT NULL,
    password 								CHAR(50) NOT NULL,
    groupid 								INT ,
    perms 									INT ,
    confaddcust 							INT ,
    name 									CHAR(50),
    direction 								CHAR(80),
    zipcode 								CHAR(20),
    state 									CHAR(20),
    phone 									CHAR(30),
    fax 									CHAR(30),
    datecreation 							TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (userid),
    UNIQUE cons_cc_ui_authen_login (login)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;



CREATE TABLE cc_call (
    id 									bigINT (20) NOT NULL AUTO_INCREMENT,
    sessionid 							char(40) NOT NULL,
    uniqueid 							char(30) NOT NULL,
    username 							char(40) NOT NULL,
    nasipaddress 						char(30) DEFAULT NULL,
    starttime 							timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    stoptime 							timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    sessiontime 						INT (11) DEFAULT NULL,
    calledstation 						char(30) DEFAULT NULL,
    startdelay 							INT (11) DEFAULT NULL,
    stopdelay 							INT (11) DEFAULT NULL,
    terminatecause 						char(20) DEFAULT NULL,
    usertariff 							char(20) DEFAULT NULL,
    calledprovider 						char(20) DEFAULT NULL,
    calledcountry 						char(30) DEFAULT NULL,
    calledsub 							char(20) DEFAULT NULL,
    calledrate 							FLOAT DEFAULT NULL,
    sessionbill 						FLOAT DEFAULT NULL,
    destination 						char(40) DEFAULT NULL,
    id_tariffgroup 						INT (11) DEFAULT NULL,
    id_tariffplan 						INT (11) DEFAULT NULL,
    id_ratecard 						INT (11) DEFAULT NULL,
    id_trunk 							INT (11) DEFAULT NULL,
    sipiax 								INT (11) DEFAULT '0',
    src 								char(40) DEFAULT NULL,
    id_did 								INT (11) DEFAULT NULL,
    buyrate 							DECIMAL(15,5) DEFAULT 0,
    buycost 							DECIMAL(15,5) DEFAULT 0,
	id_card_package_offer 				INT (11) DEFAULT 0,
    PRIMARY KEY  (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;


CREATE TABLE cc_templatemail (
    mailtype 						CHAR(50),
    fromemail 						CHAR(70),
    fromname 						CHAR(70),