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

asterisk用 的voip记费软件

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

/* Default values - Please change them to whatever you want 
 
Database name is: mya2billing
Database user is: a2billinguser


USAGE :

su - postgres
psql -f UPDATE-a2billing-v1.0.2-to-v1.0.4-pgsql.sql template1
	
*/


 
ALTER TABLE cc_call ADD COLUMN id_did integer;

CREATE TABLE cc_paypal (
  id bigserial NOT NULL,
  payer_id character varying(60) default NULL,
  payment_date character varying(50) default NULL,
  txn_id character varying(50) default NULL,
  first_name character varying(50) default NULL,
  last_name character varying(50) default NULL,
  payer_email character varying(75) default NULL,
  payer_status character varying(50) default NULL,
  payment_type character varying(50) default NULL,
  memo text,
  item_name character varying(127) default NULL,
  item_number character varying(127) default NULL,
  quantity bigint NOT NULL default '0',
  mc_gross numeric(9,2) default NULL,
  mc_fee numeric(9,2) default NULL,
  tax numeric(9,2) default NULL,
  mc_currency character varying(3) default NULL,
  address_name character varying(255) NOT NULL default '',
  address_street character varying(255) NOT NULL default '',
  address_city character varying(255) NOT NULL default '',
  address_state character varying(255) NOT NULL default '',
  address_zip character varying(255) NOT NULL default '',
  address_country character varying(255) NOT NULL default '',
  address_status character varying(255) NOT NULL default '',
  payer_business_name character varying(255) NOT NULL default '',
  payment_status character varying(255) NOT NULL default '',
  pending_reason character varying(255) NOT NULL default '',
  reason_code character varying(255) NOT NULL default '',
  txn_type character varying(255) NOT NULL default ''
);

ALTER TABLE ONLY cc_paypal
ADD CONSTRAINT cc_paypal_pkey PRIMARY KEY (id);

ALTER TABLE ONLY cc_paypal
    ADD CONSTRAINT cons_txn_id_cc_paypal UNIQUE (txn_id);
	


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 bigserial NOT NULL,
    idreseller integer DEFAULT 0 NOT NULL,	
    creationdate timestamp without time zone DEFAULT now(),
    didgroupname text NOT NULL
);

ALTER TABLE ONLY cc_didgroup
    ADD CONSTRAINT cc_didgroup_pkey PRIMARY KEY (id);



CREATE TABLE cc_did (
    id bigserial NOT NULL,
	id_cc_didgroup bigint NOT NULL,
	id_cc_country integer NOT NULL,    
    activated integer DEFAULT 1 NOT NULL,
    iduser integer DEFAULT 0 NOT NULL,
    did text NOT NULL,
    creationdate timestamp without time zone DEFAULT now(),	
    startingdate timestamp without time zone DEFAULT now(),
    expirationdate timestamp without time zone,
    description text,
    secondusedreal integer DEFAULT 0,
	billingtype integer DEFAULT 0,
	fixrate numeric(12,4) NOT NULL
);

-- billtype: 0 = fix per month + dialoutrate, 1= fix per month, 2 = dialoutrate, 3 = free


ALTER TABLE ONLY cc_did
    ADD CONSTRAINT cc_did_pkey PRIMARY KEY (id);

ALTER TABLE ONLY cc_did
    ADD CONSTRAINT cons_did_cc_did UNIQUE (did);
	

DROP TABLE cc_did_destination;
CREATE TABLE cc_did_destination (
    id bigserial NOT NULL,
    destination text NOT NULL,
	priority integer DEFAULT 0 NOT NULL,
    id_cc_card bigint NOT NULL,
	id_cc_did bigint NOT NULL,	
	creationdate timestamp without time zone DEFAULT now(),
    activated integer DEFAULT 1 NOT NULL,
	secondusedreal integer DEFAULT 0,
	voip_call integer DEFAULT 0
);


ALTER TABLE ONLY cc_did_destination
    ADD CONSTRAINT cc_did_destination_pkey PRIMARY KEY (id);


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


-- HOW TO BILL : FOR a user SEARCH IF he is used a DID during the last month
and bill him according to the DID price (check DID billtype)
Then create an instance in cc_charge with topic DID_monthly_charge

CREATE TABLE cc_charge (
    id bigserial NOT NULL,
	id_cc_card bigint NOT NULL,	
    iduser integer DEFAULT 0 NOT NULL,
	creationdate timestamp without time zone DEFAULT now(),	
	amount numeric(12,4) NOT NULL,
	chargetype integer DEFAULT 0,    
    description text
);

ALTER TABLE ONLY cc_charge
    ADD CONSTRAINT cc_charge_pkey PRIMARY KEY (id);

-- chargetype : 1 - connection charge for DID setup, 2 - Montly charge for DID use, 3 - just wanted to charge you for extra, 4 - cactus renting charges, etc...


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

CREATE TABLE cc_country (
    id serial NOT NULL,
    countrycode text NOT NULL,
    countryname text NOT NULL
);

ALTER TABLE ONLY cc_country
    ADD CONSTRAINT cc_country_pkey 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');