a2billing-pgsql-schema-v1.3.0.sql

asterisk用 的voip记费软件

--
-- A2Billing database
--

--  Default values - Please change them to whatever you want 
 
-- 	Database name is: mya2billing
-- 	Database user is: a2billinguser
-- 	User password is: a2billing



-- 1. make sure that the Database user is GRANT to access the database in pg_hba.conf!

--     a line like this will do it
    
--     # TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
--     # Database asterisk/a2billing login with password for a non real user
--     #
--     local   mya2billing all						md5
    
--     DON'T FORGET TO RESTART Postgresql SERVER IF YOU MADE ANY MODIFICATION ON THIS FILE
    
-- 2. open a terminal and enter the below commands. We assume our superuser to be postgres.
--    Please adapt to your setup.

--     su - postgres
--     psql -f a2billing-pgsql-schema-v1.2.3.sql template1

--     NOTE: the errors you will see about missing tables are OK, it's the default behaviour of pgsql.
    
--     When prompted for the password, please enter the one you choose. In our case, it's 'a2billing'. 




SET default_with_oids = true;


CREATE TABLE cc_campaign (
    id 						BIGSERIAL NOT NULL,
    campaign_name 			TEXT NOT NULL,
    creationdate 			TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    startingdate 			TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    expirationdate 			TIMESTAMP WITHOUT TIME ZONE,
    description 			TEXT ,
    id_trunk 				BIGINT NOT NULL,	
    secondusedreal 			INTEGER DEFAULT 0,
    nb_callmade 			INTEGER DEFAULT 0,
    enable 					INTEGER DEFAULT 0 NOT NULL
);

ALTER TABLE ONLY cc_campaign
    ADD CONSTRAINT cc_campaign_pkey PRIMARY KEY (id);
ALTER TABLE ONLY cc_campaign
    ADD CONSTRAINT cons_phonelistname UNIQUE (campaign_name);



CREATE TABLE cc_phonelist (
    id 							BIGSERIAL NOT NULL,
    id_cc_campaign 				BIGINT DEFAULT 0 NOT NULL,
    id_cc_card 					BIGINT DEFAULT 0 NOT NULL,
    numbertodial 				TEXT NOT NULL,
    name 						TEXT NOT NULL,
    inuse 						INTEGER DEFAULT 0,
    enable 						INTEGER DEFAULT 1 NOT NULL,
    num_trials_done 			INTEGER DEFAULT 0,
    creationdate 				TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    last_attempt 				TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),		
    secondusedreal 				INTEGER DEFAULT 0,
    additionalinfo 				TEXT NOT NULL	
);

ALTER TABLE ONLY cc_phonelist
    ADD CONSTRAINT cc_phonelist_pkey PRIMARY KEY (id);
	
CREATE INDEX ind_cc_phonelist_numbertodial ON cc_phonelist USING btree (numbertodial);




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,
    reserved 					INTEGER DEFAULT 0,
    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);
	


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



-- chargetype : 0 - subscription fee ; 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...
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,
	currency 				CHARACTER VARYING(3) DEFAULT 'USD'::CHARACTER VARYING,
    chargetype 				INTEGER DEFAULT 0,    
    id_cc_did 				BIGINT DEFAULT 0,
	id_cc_subscription_fee 	BIGINT DEFAULT 0,
    description 			TEXT
);

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


CREATE INDEX ind_cc_charge_id_cc_card				ON cc_charge USING btree (id_cc_card);
CREATE INDEX ind_cc_charge_id_cc_subscription_fee 	ON cc_charge USING btree (id_cc_subscription_fee);
CREATE INDEX ind_cc_charge_creationdate 			ON cc_charge USING btree (creationdate);



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

CREATE TABLE cc_voucher (
    id 									BIGSERIAL NOT NULL,
    creationdate 						TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    usedate 							TIMESTAMP WITHOUT TIME ZONE,
    expirationdate 						TIMESTAMP WITHOUT TIME ZONE,	
    voucher 							TEXT NOT NULL,
    usedcardnumber 						TEXT ,
    tag 								TEXT ,	
    credit 								NUMERIC(12,4) NOT NULL,    
    activated 							BOOLEAN DEFAULT true NOT NULL,
    used 								INTEGER DEFAULT 0,
    currency 							CHARACTER VARYING(3) DEFAULT 'USD'::CHARACTER varying
);

ALTER TABLE ONLY cc_voucher
    ADD CONSTRAINT cc_voucher_pkey PRIMARY KEY (id);
ALTER TABLE ONLY cc_voucher
    ADD CONSTRAINT cons_voucher_cc_voucher UNIQUE (voucher);




CREATE TABLE cc_service (
    id 									BIGSERIAL NOT NULL,	
    name 								TEXT NOT NULL,
    amount 								double precision NOT NULL,
    period 								INTEGER NOT NULL DEFAULT 1,
    rule 								INTEGER NOT NULL DEFAULT 0,
    daynumber 							INTEGER NOT NULL DEFAULT 0,
    stopmode 							INTEGER NOT NULL DEFAULT 0,
    maxnumbercycle 						INTEGER NOT NULL DEFAULT 0,
    status 								INTEGER NOT NULL DEFAULT 0,
    numberofrun 						INTEGER NOT NULL DEFAULT 0,
    datecreate 							TIMESTAMP(0) without time zone DEFAULT NOW(),
    datelastrun 						TIMESTAMP(0) without time zone DEFAULT NOW(),
    emailreport 						TEXT ,
    totalcredit 						double precision NOT NULL DEFAULT 0,
    totalcardperform 					INTEGER NOT NULL DEFAULT 0
);
ALTER TABLE ONLY cc_service
ADD CONSTRAINT cc_service_pkey PRIMARY KEY (id);

	
CREATE TABLE cc_service_report (
    id 									BIGSERIAL NOT NULL,
    cc_service_id 						BIGSERIAL NOT NULL,
    daterun 							TIMESTAMP(0) without time zone DEFAULT NOW(),
    totalcardperform 					INTEGER,
    totalcredit 						double precision
);
ALTER TABLE ONLY cc_service_report
ADD CONSTRAINT cc_service_report_pkey PRIMARY KEY (id);





CREATE TABLE cc_callerid (