a2billing-mysql-schema-mysql.3.x-4.x_v1.3.0.sql

asterisk用 的voip记费软件

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


/* 
Usage:

mysql -u root -p"root password" < a2billing-mysql-schema-MYSQL.3.X-4.X_v1.2.4.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 (