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

asterisk用 的voip记费软件

    mailtype 					CHAR(50),
    fromemail 					CHAR(70),
    fromname 					CHAR(70),
    subject 					CHAR(70),
    messagetext 				LONGTEXT,
    messagehtml 				LONGTEXT,
    UNIQUE cons_cc_templatemail_mailtype (mailtype)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;




CREATE TABLE cc_tariffgroup (
    id 								INT NOT NULL AUTO_INCREMENT,
    iduser 							INT DEFAULT 0 NOT NULL,
    idtariffplan 					INT DEFAULT 0 NOT NULL,
    tariffgroupname 				CHAR(50) NOT NULL,
    lcrtype 						INT DEFAULT 0 NOT NULL,
    creationdate  					TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    removeinterprefix				INT DEFAULT 0 NOT NULL,
	id_cc_package_offer 			BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;


CREATE TABLE cc_tariffgroup_plan (
    idtariffgroup 						INT NOT NULL,
    idtariffplan 						INT NOT NULL,
    PRIMARY KEY (idtariffgroup, idtariffplan)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;


CREATE TABLE cc_tariffplan (
    id 								INT NOT NULL AUTO_INCREMENT,
    iduser 							INT DEFAULT 0 NOT NULL,
    tariffname 						CHAR(50) NOT NULL,
    creationdate 					TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    startingdate 					TIMESTAMP,
    expirationdate				 	TIMESTAMP,
    description 					MEDIUMTEXT,
    id_trunk 						INT DEFAULT 0,
    secondusedreal 					INT DEFAULT 0,
    secondusedcarrier 				INT DEFAULT 0,
    secondusedratecard 				INT DEFAULT 0,
    reftariffplan 					INT DEFAULT 0,
    idowner 						INT DEFAULT 0,
    dnidprefix 						CHAR(30) NOT NULL DEFAULT 'all',
	calleridprefix 					CHAR(30) NOT NULL DEFAULT 'all',
    PRIMARY KEY (id),
    UNIQUE cons_cc_tariffplan_iduser_tariffname (iduser,tariffname)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;


CREATE TABLE cc_card (
    id 								BIGINT NOT NULL AUTO_INCREMENT,
    creationdate 					TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    firstusedate 					TIMESTAMP,
    expirationdate 					TIMESTAMP,
    enableexpire 					INT DEFAULT 0,
    expiredays 						INT DEFAULT 0,
    username 						CHAR(50) NOT NULL,
    useralias 						CHAR(50) NOT NULL,
    userpass 						CHAR(50) NOT NULL,
    uipass 							CHAR(50),
    credit 							DECIMAL(15,5) DEFAULT 0 NOT NULL,
    tariff 								INT DEFAULT 0,
    id_didgroup 						INT DEFAULT 0,
    activated 							CHAR(1) DEFAULT 'f' NOT NULL,
    lastname 						CHAR(50),
    firstname 						CHAR(50),
    address 						CHAR(100),
    city 							CHAR(40),
    state 							CHAR(40),
    country 						CHAR(40),
    zipcode 						CHAR(20),
    phone 							CHAR(20),
    email 							CHAR(70),
    fax 							CHAR(20),
    inuse 							INT DEFAULT 0,
    simultaccess 					INT DEFAULT 0,
    currency 						CHAR(3) DEFAULT 'USD',
    lastuse  						TIMESTAMP,
    nbused 							INT DEFAULT 0,
    typepaid 						INT DEFAULT 0,
    creditlimit 					INT DEFAULT 0,
    voipcall 						INT DEFAULT 0,
    sip_buddy 						INT DEFAULT 0,
    iax_buddy	 					INT DEFAULT 0,
    language 						CHAR(5) DEFAULT 'en',
    redial 							CHAR(50),
    runservice 						INT DEFAULT 0,
	nbservice 						INT DEFAULT 0,
    id_campaign 					INT DEFAULT 0,
    num_trials_done 				BIGINT DEFAULT 0,
    callback 						CHAR(50),
	vat 							FLOAT DEFAULT 0 NOT NULL,
	servicelastrun 					TIMESTAMP,
	initialbalance 					DECIMAL(15,5) DEFAULT 0 NOT NULL,
	invoiceday 						INT DEFAULT 1,
	autorefill 						INT DEFAULT 0,
    loginkey 						CHAR(40),
    activatedbyuser 				CHAR(1) DEFAULT 't' NOT NULL,
	id_subscription_fee 			INT DEFAULT 0,
	mac_addr						CHAR(17) DEFAULT '00-00-00-00-00-00' NOT NULL,
    PRIMARY KEY (id),
    UNIQUE cons_cc_card_username (username),
    UNIQUE cons_cc_card_useralias (useralias)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;


CREATE TABLE cc_ratecard (
    id 									INT NOT NULL AUTO_INCREMENT,
    idtariffplan 						INT DEFAULT 0 NOT NULL,
    dialprefix 							CHAR(30) NOT NULL,
    destination 						CHAR(50) NOT NULL,
    buyrate 							FLOAT DEFAULT 0 NOT NULL,
    buyrateinitblock 					INT DEFAULT 0 NOT NULL,
    buyrateincrement 					INT DEFAULT 0 NOT NULL,
    rateinitial 						FLOAT DEFAULT 0 NOT NULL,
    initblock 							INT DEFAULT 0 NOT NULL,
    billingblock						INT DEFAULT 0 NOT NULL,
    connectcharge 						FLOAT DEFAULT 0 NOT NULL,
    disconnectcharge 					FLOAT DEFAULT 0 NOT NULL,
    stepchargea 						FLOAT DEFAULT 0 NOT NULL,
    chargea 							FLOAT DEFAULT 0 NOT NULL,
    timechargea 						INT DEFAULT 0 NOT NULL,
    billingblocka 						INT DEFAULT 0 NOT NULL,
    stepchargeb 						FLOAT DEFAULT 0 NOT NULL,
    chargeb 							FLOAT DEFAULT 0 NOT NULL,
    timechargeb 						INT DEFAULT 0 NOT NULL,
    billingblockb 						INT DEFAULT 0 NOT NULL,
    stepchargec 						FLOAT DEFAULT 0 NOT NULL,
    chargec 							FLOAT DEFAULT 0 NOT NULL,
    timechargec 						INT DEFAULT 0 NOT NULL,
    billingblockc 						INT DEFAULT 0 NOT NULL,
    startdate 							TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    stopdate 							TIMESTAMP,
    starttime 							smallINT(5) unsigned DEFAULT '0',
    endtime 							smallINT(5) unsigned DEFAULT '10079',
    id_trunk 							INT DEFAULT -1,
    musiconhold 						CHAR(100) NOT NULL,
	freetimetocall_package_offer 		INT NOT NULL DEFAULT 0,
	id_outbound_cidgroup 				INT NOT NULL DEFAULT -1,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;
CREATE INDEX ind_cc_ratecard_dialprefix ON cc_ratecard (dialprefix);


CREATE TABLE cc_logrefill (
    id 									INT NOT NULL AUTO_INCREMENT,
    date 								TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    credit 								FLOAT NOT NULL,
    card_id 							bigint NOT NULL,
    reseller_id 						bigint,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;


CREATE TABLE cc_logpayment (
    id 									INT NOT NULL AUTO_INCREMENT,
    date 								TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    payment 							FLOAT NOT NULL,
    card_id 							bigint NOT NULL,
    reseller_id 						BIGINT,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;



CREATE TABLE cc_trunk (
    id_trunk 							INT NOT NULL AUTO_INCREMENT,
    trunkcode 							CHAR(20) NOT NULL,
    trunkprefix 						CHAR(20),
    providertech 						CHAR(20) NOT NULL,
    providerip 							CHAR(80) NOT NULL,
    removeprefix 						CHAR(20),
    secondusedreal 						INT DEFAULT 0,
    secondusedcarrier 					INT DEFAULT 0,
    secondusedratecard 					INT DEFAULT 0,
    creationdate 						TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    failover_trunk 						INT,
    addparameter 						CHAR(120),
    id_provider 						INT,
    PRIMARY KEY (id_trunk)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;




CREATE TABLE cc_sip_buddies (
    id 									INT NOT NULL AUTO_INCREMENT,
    id_cc_card 							INT DEFAULT 0 NOT NULL,
    name 								CHAR(80) DEFAULT '' NOT NULL,
    accountcode 						CHAR(20),
    regexten 							CHAR(20),
    amaflags 							CHAR(7),
    callgroup 							CHAR(10),
    callerid 							CHAR(80),
    canreinvite 						CHAR(3) DEFAULT 'yes',
    context 							CHAR(80),
    DEFAULTip 							CHAR(15),
    dtmfmode 							CHAR(7)  DEFAULT 'RFC2833' NOT NULL,	 
    fromuser 							CHAR(80),
    fromdomain 							CHAR(80),
    host 								CHAR(31) DEFAULT '' NOT NULL,
    insecure 							CHAR(4),
    language 							CHAR(2),
    mailbox 							CHAR(50),
    md5secret 							CHAR(80),
    nat 								CHAR(3) DEFAULT 'yes',
    permit 								CHAR(95),
    deny 								CHAR(95),
    mask 								CHAR(95),
    pickupgroup 						CHAR(10),
    port 								CHAR(5) DEFAULT '' NOT NULL,
    qualify 							CHAR(3) DEFAULT 'yes',
    restrictcid 						CHAR(1),
    rtptimeout 							CHAR(3),
    rtpholdtimeout 						CHAR(3),
    secret 								CHAR(80),
    type 								CHAR(6) DEFAULT 'friend' NOT NULL,
    username 							CHAR(80) DEFAULT '' NOT NULL,
    disallow 							CHAR(100) DEFAULT 'all',
    allow 								CHAR(100) DEFAULT 'gsm,ulaw,alaw',
    musiconhold 						CHAR(100),
    regseconds 							INT DEFAULT 0 NOT NULL,
    ipaddr 								CHAR(15) DEFAULT '' NOT NULL,
    cancallforward 						CHAR(3) DEFAULT 'yes',
    fullcontact 						VARCHAR(80) DEFAULT NULL,
    setvar 								VARCHAR(100) NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    UNIQUE cons_cc_sip_buddies_name (name)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;


CREATE TABLE cc_iax_buddies (
    id 								INT NOT NULL AUTO_INCREMENT,
    id_cc_card 						INT DEFAULT 0 NOT NULL,
    name 							CHAR(80) DEFAULT '' NOT NULL,
    accountcode 					CHAR(20),
    regexten 						CHAR(20),
    amaflags 						CHAR(7),
    callgroup 						CHAR(10),
    callerid 						CHAR(80),
    canreinvite 					CHAR(3) DEFAULT 'yes',
    context 						CHAR(80),
    DEFAULTip 						CHAR(15),
    dtmfmode 						CHAR(7)  DEFAULT 'RFC2833' NOT NULL,	 
    fromuser 						CHAR(80),
    fromdomain 						CHAR(80),
    host 							CHAR(31) DEFAULT '' NOT NULL,
    insecure 						CHAR(4),
    language 						CHAR(2),