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

asterisk用 的voip记费软件

    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 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),