update-a2billing-v1.0.2-to-v1.0.6-mysql.sql

asterisk用 的voip记费软件

--
-- A2Billing database - update database schema - v1.0.2 to update to v1.0.6
--

/*

To create the database : 

mysql -u root -p"root password" < UPDATE-a2billing-v1.0.2-to-v1.0.6-mysql.sql

*/



ALTER TABLE cc_call ADD COLUMN id_did INT;


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)
) TYPE=MyISAM;



ALTER TABLE cc_card ADD COLUMN id_didgroup BIGINT;
ALTER TABLE cc_card ALTER COLUMN id_didgroup SET DEFAULT 0;
UPDATE cc_card SET id_didgroup = '0';


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




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,
    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,
    id_trunk INT DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE cons_did_cc_did (did)
);

-- 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,	
    phonenumber CHAR(50) NOT NULL,
    priority INT DEFAULT 0 NOT NULL,
    id_cc_card BIGINT NOT NULL,
    id_cc_did BIGINT NOT NULL,	
    destination TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    activated INT DEFAULT 1 NOT NULL,
    secondusedreal INT DEFAULT 0,	
    id_trunk INT DEFAULT 0,
    PRIMARY KEY (id)
);


-- ALTER TABLE cc_call ADD COLUMN didcall INT;
-- ALTER TABLE cc_call ALTER COLUMN didcall SET DEFAULT 0;
-- UPDATE cc_call SET didcall = '0';
-- Didcall : -1 if not didcall, store the cc_did_id if the call is from a DID



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,
    chargetype INT DEFAULT 0,    
    description MEDIUMTEXT,
    PRIMARY KEY (id)
);


--
-- Country table : Store the iso country list
--

CREATE TABLE cc_country (
    id BIGINT NOT NULL AUTO_INCREMENT,
    countrycode CHAR(80) NOT NULL,
    countryname CHAR(80) NOT NULL,
    PRIMARY KEY (id)
);



INSERT INTO cc_country VALUES (1, 'AFG', 'Afghanistan');
INSERT INTO cc_country VALUES (2, 'ALB', 'Albania');
INSERT INTO cc_country VALUES (3, 'DZA', 'Algeria');
INSERT INTO cc_country VALUES (4, 'ASM', 'American Samoa');
INSERT INTO cc_country VALUES (5, 'AND', 'Andorra');
INSERT INTO cc_country VALUES (6, 'AGO', 'Angola');
INSERT INTO cc_country VALUES (7, 'AIA', 'Anguilla');
INSERT INTO cc_country VALUES (8, 'ATA', 'Antarctica');
INSERT INTO cc_country VALUES (9, 'ATG', 'Antigua And Barbuda');
INSERT INTO cc_country VALUES (10, 'ARG', 'Argentina');
INSERT INTO cc_country VALUES (11, 'ARM', 'Armenia');
INSERT INTO cc_country VALUES (12, 'ABW', 'Aruba');
INSERT INTO cc_country VALUES (13, 'AUS', 'Australia');
INSERT INTO cc_country VALUES (14, 'AUT', 'Austria');
INSERT INTO cc_country VALUES (15, 'AZE', 'Azerbaijan');
INSERT INTO cc_country VALUES (16, 'BHS', 'Bahamas');
INSERT INTO cc_country VALUES (17, 'BHR', 'Bahrain');
INSERT INTO cc_country VALUES (18, 'BGD', 'Bangladesh');
INSERT INTO cc_country VALUES (19, 'BRB', 'Barbados');
INSERT INTO cc_country VALUES (20, 'BLR', 'Belarus');
INSERT INTO cc_country VALUES (21, 'BEL', 'Belgium');
INSERT INTO cc_country VALUES (22, 'BLZ', 'Belize');
INSERT INTO cc_country VALUES (23, 'BEN', 'Benin');
INSERT INTO cc_country VALUES (24, 'BMU', 'Bermuda');
INSERT INTO cc_country VALUES (25, 'BTN', 'Bhutan');
INSERT INTO cc_country VALUES (26, 'BOL', 'Bolivia');
INSERT INTO cc_country VALUES (27, 'BIH', 'Bosnia And Herzegovina');
INSERT INTO cc_country VALUES (28, 'BWA', 'Botswana');
INSERT INTO cc_country VALUES (29, 'BV', 'Bouvet Island');
INSERT INTO cc_country VALUES (30, 'BRA', 'Brazil');
INSERT INTO cc_country VALUES (31, 'IO', 'British Indian Ocean Territory');
INSERT INTO cc_country VALUES (32, 'BRN', 'Brunei Darussalam');
INSERT INTO cc_country VALUES (33, 'BGR', 'Bulgaria');
INSERT INTO cc_country VALUES (34, 'BFA', 'Burkina Faso');
INSERT INTO cc_country VALUES (35, 'BDI', 'Burundi');
INSERT INTO cc_country VALUES (36, 'KHM', 'Cambodia');
INSERT INTO cc_country VALUES (37, 'CMR', 'Cameroon');
INSERT INTO cc_country VALUES (38, 'CAN', 'Canada');
INSERT INTO cc_country VALUES (39, 'CPV', 'Cape Verde');
INSERT INTO cc_country VALUES (40, 'CYM', 'Cayman Islands');
INSERT INTO cc_country VALUES (41, 'CAF', 'Central African Republic');
INSERT INTO cc_country VALUES (42, 'TCD', 'Chad');
INSERT INTO cc_country VALUES (43, 'CHL', 'Chile');
INSERT INTO cc_country VALUES (44, 'CHN', 'China');
INSERT INTO cc_country VALUES (45, 'CXR', 'Christmas Island');
INSERT INTO cc_country VALUES (46, 'CCK', 'Cocos (Keeling) Islands');
INSERT INTO cc_country VALUES (47, 'COL', 'Colombia');
INSERT INTO cc_country VALUES (48, 'COM', 'Comoros');
INSERT INTO cc_country VALUES (49, 'COG', 'Congo');
INSERT INTO cc_country VALUES (50, 'COD', 'Congo, The Democratic Republic Of The');
INSERT INTO cc_country VALUES (51, 'COK', 'Cook Islands');
INSERT INTO cc_country VALUES (52, 'CRI', 'Costa Rica');
INSERT INTO cc_country VALUES (54, 'HRV', 'Croatia');
INSERT INTO cc_country VALUES (55, 'CUB', 'Cuba');