update-a2billing-v1.2.3-to-v1.3.0-mysql.sql

asterisk 計費模塊

UPDATE cc_country SET countryprefix = '687' WHERE id = '152';
UPDATE cc_country SET countryprefix = '64' WHERE id = '153';
UPDATE cc_country SET countryprefix = '505' WHERE id = '154';
UPDATE cc_country SET countryprefix = '227' WHERE id = '155';
UPDATE cc_country SET countryprefix = '234' WHERE id = '156';
UPDATE cc_country SET countryprefix = '683' WHERE id = '157';
UPDATE cc_country SET countryprefix = '672' WHERE id = '158';
UPDATE cc_country SET countryprefix = '1670' WHERE id = '159';
UPDATE cc_country SET countryprefix = '47' WHERE id = '160';
UPDATE cc_country SET countryprefix = '968' WHERE id = '161';
UPDATE cc_country SET countryprefix = '92' WHERE id = '162';
UPDATE cc_country SET countryprefix = '680' WHERE id = '163';
UPDATE cc_country SET countryprefix = '970' WHERE id = '164';
UPDATE cc_country SET countryprefix = '507' WHERE id = '165';
UPDATE cc_country SET countryprefix = '675' WHERE id = '166';
UPDATE cc_country SET countryprefix = '595' WHERE id = '167';
UPDATE cc_country SET countryprefix = '51' WHERE id = '168';
UPDATE cc_country SET countryprefix = '63' WHERE id = '169';
UPDATE cc_country SET countryprefix = '48' WHERE id = '171';
UPDATE cc_country SET countryprefix = '351' WHERE id = '172';
UPDATE cc_country SET countryprefix = '1787' WHERE id = '173';
UPDATE cc_country SET countryprefix = '974' WHERE id = '174';
UPDATE cc_country SET countryprefix = '262' WHERE id = '175';
UPDATE cc_country SET countryprefix = '40' WHERE id = '176';
UPDATE cc_country SET countryprefix = '7' WHERE id = '177';
UPDATE cc_country SET countryprefix = '250' WHERE id = '178';
UPDATE cc_country SET countryprefix = '290' WHERE id = '179';
UPDATE cc_country SET countryprefix = '1869' WHERE id = '180';
UPDATE cc_country SET countryprefix = '1758' WHERE id = '181';
UPDATE cc_country SET countryprefix = '508' WHERE id = '182';
UPDATE cc_country SET countryprefix = '1784' WHERE id = '183';
UPDATE cc_country SET countryprefix = '685' WHERE id = '184';
UPDATE cc_country SET countryprefix = '378' WHERE id = '185';
UPDATE cc_country SET countryprefix = '239' WHERE id = '186';
UPDATE cc_country SET countryprefix = '966' WHERE id = '187';
UPDATE cc_country SET countryprefix = '221' WHERE id = '188';
UPDATE cc_country SET countryprefix = '248' WHERE id = '189';
UPDATE cc_country SET countryprefix = '232' WHERE id = '190';
UPDATE cc_country SET countryprefix = '65' WHERE id = '191';
UPDATE cc_country SET countryprefix = '421' WHERE id = '192';
UPDATE cc_country SET countryprefix = '386' WHERE id = '193';
UPDATE cc_country SET countryprefix = '677' WHERE id = '194';
UPDATE cc_country SET countryprefix = '252' WHERE id = '195';
UPDATE cc_country SET countryprefix = '27' WHERE id = '196';
UPDATE cc_country SET countryprefix = '34' WHERE id = '198';
UPDATE cc_country SET countryprefix = '94' WHERE id = '199';
UPDATE cc_country SET countryprefix = '249' WHERE id = '200';
UPDATE cc_country SET countryprefix = '597' WHERE id = '201';
UPDATE cc_country SET countryprefix = '268' WHERE id = '203';
UPDATE cc_country SET countryprefix = '46' WHERE id = '204';
UPDATE cc_country SET countryprefix = '41' WHERE id = '205';
UPDATE cc_country SET countryprefix = '963' WHERE id = '206';
UPDATE cc_country SET countryprefix = '886' WHERE id = '207';
UPDATE cc_country SET countryprefix = '992' WHERE id = '208';
UPDATE cc_country SET countryprefix = '255' WHERE id = '209';
UPDATE cc_country SET countryprefix = '66' WHERE id = '210';
UPDATE cc_country SET countryprefix = '228' WHERE id = '212';
UPDATE cc_country SET countryprefix = '690' WHERE id = '213';
UPDATE cc_country SET countryprefix = '676' WHERE id = '214';
UPDATE cc_country SET countryprefix = '1868' WHERE id = '215';
UPDATE cc_country SET countryprefix = '216' WHERE id = '216';
UPDATE cc_country SET countryprefix = '90' WHERE id = '217';
UPDATE cc_country SET countryprefix = '993' WHERE id = '218';
UPDATE cc_country SET countryprefix = '1649' WHERE id = '219';
UPDATE cc_country SET countryprefix = '688' WHERE id = '220';
UPDATE cc_country SET countryprefix = '256' WHERE id = '221';
UPDATE cc_country SET countryprefix = '380' WHERE id = '222';
UPDATE cc_country SET countryprefix = '971' WHERE id = '223';
UPDATE cc_country SET countryprefix = '44' WHERE id = '224';
UPDATE cc_country SET countryprefix = '1' WHERE id = '225';
UPDATE cc_country SET countryprefix = '598' WHERE id = '227';
UPDATE cc_country SET countryprefix = '998' WHERE id = '228';
UPDATE cc_country SET countryprefix = '678' WHERE id = '229';
UPDATE cc_country SET countryprefix = '58' WHERE id = '230';
UPDATE cc_country SET countryprefix = '84' WHERE id = '231';
UPDATE cc_country SET countryprefix = '1284' WHERE id = '232';
UPDATE cc_country SET countryprefix = '808' WHERE id = '233';
UPDATE cc_country SET countryprefix = '591' WHERE id = '234';
UPDATE cc_country SET countryprefix = '967' WHERE id = '236';
UPDATE cc_country SET countryprefix = '260' WHERE id = '238';
UPDATE cc_country SET countryprefix = '263' WHERE id = '239';
UPDATE cc_country SET countryprefix = '670' WHERE id = '243';




CREATE TABLE cc_alarm (
    id 									BIGINT NOT NULL AUTO_INCREMENT,
    name 								TEXT NOT NULL,
    periode 							INT NOT NULL DEFAULT 1,
    type 								INT NOT NULL DEFAULT 1,
    maxvalue 							FLOAT NOT NULL,
    minvalue 							FLOAT NOT NULL DEFAULT -1,
    id_trunk 							INT,
    status 								INT NOT NULL DEFAULT 0,
    numberofrun 						INT NOT NULL DEFAULT 0,
    numberofalarm 						INT NOT NULL DEFAULT 0,    
    datecreate 							TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,	
    datelastrun 						TIMESTAMP,
    emailreport 						VARCHAR(50),
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;

 CREATE TABLE cc_alarm_report (
    id 									BIGINT NOT NULL AUTO_INCREMENT,
    cc_alarm_id 						BIGINT NOT NULL,
    calculatedvalue 					FLOAT NOT NULL,
    daterun 							TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;




CREATE TABLE cc_callback_spool (
    id 								BIGINT NOT NULL AUTO_INCREMENT,
    uniqueid 						VARCHAR(40),
    entry_time 						TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status 							VARCHAR(80),
    server_ip 						VARCHAR(40),
    num_attempt 					INT NOT NULL DEFAULT 0,
    last_attempt_time 				TIMESTAMP,
    manager_result 					VARCHAR(60),
    agi_result 						VARCHAR(60),
    callback_time 					TIMESTAMP,
    channel 						VARCHAR(60),
    exten 							VARCHAR(60),
    context 						VARCHAR(60),
    priority 						VARCHAR(60),
    application 					VARCHAR(60),
    data 							VARCHAR(60),
    timeout 						VARCHAR(60),
    callerid 						VARCHAR(60),
    variable 						VARCHAR(100),
    account 						VARCHAR(60),
    async 							VARCHAR(60),
    actionid 						VARCHAR(60),
	id_server						INT,
	id_server_group					INT,
    PRIMARY KEY (id),
    UNIQUE cc_callback_spool_uniqueid_key (uniqueid)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;

CREATE TABLE cc_server_manager (
    id 								BIGINT NOT NULL AUTO_INCREMENT,
	id_group						INT DEFAULT 1,
    server_ip 						VARCHAR(40),
    manager_host 					VARCHAR(50),
    manager_username 				VARCHAR(50),
    manager_secret 					VARCHAR(50),
	lasttime_used		 			TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;

INSERT INTO cc_server_manager (id_group, server_ip, manager_host, manager_username, manager_secret) VALUES (1, 'localhost', 'localhost', 'myasterisk', 'mycode');


CREATE TABLE cc_server_group (
	id 								BIGINT NOT NULL AUTO_INCREMENT,
	name 							VARCHAR(60),
	description						MEDIUMTEXT,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;
INSERT INTO cc_server_group (id, name, description) VALUES (1, 'default', 'default group of server');



CREATE TABLE cc_invoices (
    id 								INT NOT NULL AUTO_INCREMENT,    
    cardid 							bigint NOT NULL,
    orderref 						VARCHAR(50),
    invoicecreated_date 			TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    cover_startdate 				TIMESTAMP,
    cover_enddate 					TIMESTAMP,	
    amount float 					DEFAULT 0.0,
    tax float 						DEFAULT 0.0,
    total float 					DEFAULT 0.0,
    invoicetype 					int,
    filename 						VARCHAR(250),
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;
CREATE INDEX ind_cc_invoices ON cc_invoices (cover_startdate);

CREATE TABLE cc_invoice_history (
    id 								INT NOT NULL AUTO_INCREMENT,    
    invoiceid 						int NOT NULL,	
    invoicesent_date 				TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    invoicestatus 					INT,    
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;
CREATE INDEX ind_cc_invoice_history ON cc_invoice_history (invoicesent_date);






CREATE TABLE cc_package_offer (
    id 					BIGINT NOT NULL AUTO_INCREMENT,
    creationdate 		TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    label 				VARCHAR(70) NOT NULL,
    packagetype 		INT NOT NULL,
	billingtype 		INT NOT NULL,
	startday 			INT NOT NULL,
	freetimetocall 		INT NOT NULL,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;
-- packagetype : Free minute + Unlimited ; Free minute ; Unlimited ; Normal
-- billingtype : Monthly ; Weekly 
-- startday : according to billingtype ; if monthly value 1-31 ; if Weekly value 1-7 (Monday to Sunday) 


CREATE TABLE cc_card_package_offer (
    id 					BIGINT NOT NULL AUTO_INCREMENT,
	id_cc_card 			BIGINT NOT NULL,
	id_cc_package_offer BIGINT NOT NULL,
    date_consumption 	TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	used_secondes 		BIGINT NOT NULL,
    PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_bin;
CREATE INDEX ind_cc_card_package_offer_id_card 			ON cc_card_package_offer (id_cc_card);
CREATE INDEX ind_cc_card_package_offer_id_package_offer ON cc_card_package_offer (id_cc_package_offer);
CREATE INDEX ind_cc_card_package_offer_date_consumption ON cc_card_package_offer (date_consumption);


ALTER TABLE cc_tariffgroup 	ADD COLUMN id_cc_package_offer 			BIGINT NOT NULL DEFAULT 0;
ALTER TABLE cc_ratecard 	ADD COLUMN freetimetocall_package_offer INT NOT NULL DEFAULT 0;
ALTER TABLE cc_call 		ADD COLUMN id_card_package_offer 		INT DEFAULT 0;


CREATE TABLE cc_subscription_fee (
    id 								BIGINT NOT NULL AUTO_INCREMENT,
    label 							TEXT NOT NULL,
    fee 							float DEFAULT 0 NOT NULL,
	currency 						CHAR(3) DEFAULT 'USD',
    status 							INT DEFAULT '0' NOT NULL,