a2billing-pgsql-schema-v1.3.0.sql

来自「asterisk用 的voip记费软件」· SQL 代码 · 共 1,310 行 · 第 1/5 页

    rtpholdtimeout 						CHARACTER VARYING(3),
    musiconhold 						CHARACTER VARYING(100),
    regseconds 							INTEGER DEFAULT 0 NOT NULL,
    ipaddr 								CHARACTER VARYING(15) DEFAULT ''::CHARACTER varying NOT NULL,
    cancallforward 						CHARACTER VARYING(3) DEFAULT 'yes'::CHARACTER varying,	
    fullcontact 						CHARACTER VARYING(80),
    setvar 								CHARACTER VARYING(100) DEFAULT ''::CHARACTER varying NOT NULL
);



CREATE TABLE cc_iax_buddies (
    id 									serial NOT NULL,
    id_cc_card 							INTEGER DEFAULT 0 NOT NULL,
    name 								CHARACTER VARYING(80) DEFAULT ''::CHARACTER varying NOT NULL,
    "type" 								CHARACTER VARYING(6) DEFAULT 'friend'::CHARACTER varying NOT NULL,
    username 							CHARACTER VARYING(80) DEFAULT ''::CHARACTER varying NOT NULL,	
    accountcode 						CHARACTER VARYING(20),    
    regexten 							CHARACTER VARYING(20),
    callerid 							CHARACTER VARYING(80),	
    amaflags 							CHARACTER VARYING(7),
    secret 								CHARACTER VARYING(80),
    md5secret 							CHARACTER VARYING(80),
    nat 								CHARACTER VARYING(3) DEFAULT 'yes'::CHARACTER varying NOT NULL,
    dtmfmode 							CHARACTER VARYING(7) DEFAULT 'RFC2833'::CHARACTER varying NOT NULL,	
    disallow 							CHARACTER VARYING(100) DEFAULT 'all'::CHARACTER varying,
    allow 								CHARACTER VARYING(100) DEFAULT 'gsm,ulaw,alaw'::CHARACTER varying,
    host 								CHARACTER VARYING(31) DEFAULT ''::CHARACTER varying NOT NULL,
    qualify 							CHARACTER VARYING(3) DEFAULT 'yes'::CHARACTER varying NOT NULL,
    canreinvite 						CHARACTER VARYING(3) DEFAULT 'yes'::CHARACTER varying,
    callgroup 							CHARACTER VARYING(10),
    context 							CHARACTER VARYING(80),
    defaultip 							CHARACTER VARYING(15),
    fromuser 							CHARACTER VARYING(80),    
    fromdomain 							CHARACTER VARYING(80),
    insecure 							CHARACTER VARYING(4),
    "language" 							CHARACTER VARYING(2),
    mailbox 							CHARACTER VARYING(50),
    permit 								CHARACTER VARYING(95),
    deny 								CHARACTER VARYING(95),
    mask 								CHARACTER VARYING(95),
    pickupgroup 						CHARACTER VARYING(10),
    port 								CHARACTER VARYING(5) DEFAULT ''::CHARACTER varying NOT NULL,
    restrictcid 						CHARACTER VARYING(1),
    rtptimeout 							CHARACTER VARYING(3),
    rtpholdtimeout 						CHARACTER VARYING(3),
    musiconhold 						CHARACTER VARYING(100),
    regseconds							INTEGER DEFAULT 0 NOT NULL,
    ipaddr 								CHARACTER VARYING(15) DEFAULT ''::CHARACTER varying NOT NULL,
    cancallforward 						CHARACTER VARYING(3) DEFAULT 'yes'::CHARACTER varying
);




CREATE TABLE cc_logrefill (
    id 									serial NOT NULL,
    date 								TIMESTAMP(0) without time zone DEFAULT NOW() NOT NULL,
    credit 								NUMERIC(12,4) NOT NULL,
    card_id 							BIGINT NOT NULL,
    reseller_id 						BIGINT
);


CREATE TABLE cc_logpayment (
    id 									serial NOT NULL,
    date 								TIMESTAMP(0) without time zone DEFAULT NOW() NOT NULL,
    payment 							real NOT NULL,
    card_id 							BIGINT NOT NULL,
    reseller_id 						BIGINT
);

create table cc_did_use (
    id 									serial not null ,
    id_cc_card 							BIGINT,
    id_did 								BIGINT not null,
    reservationdate						TIMESTAMP not null default NOW(),
    releasedate 						TIMESTAMP,
    activated 							INTEGER default 0,
    month_payed 						INTEGER default 0
);


INSERT INTO cc_ui_authen VALUES (2, 'admin', 'mypassword', 0, 32767, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2005-02-26 21:14:05.391501-05');
INSERT INTO cc_ui_authen VALUES (1, 'root', 'myroot', 0, 32767, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2005-02-26 20:33:27.691314-05');


INSERT INTO cc_templatemail VALUES ('signup', '[e-mail]', 'Call-Labs', 'SIGNUP CONFIRMATION', '
Thank you for registering with us
Please click on below link to activate your account.

http://call-labs.com/A2Billing_UI/signup/activate.php?key$loginkey

Please make sure you active your account by making payment to us either by
credit card, wire transfer, money order, cheque, and western union money
transfer, money Gram, and Pay pal.


Kind regards,
Call Labs
', '');

INSERT INTO cc_templatemail VALUES ('epaymentverify', '[e-mail]', 'Call-Labs', 'Epayment Gateway Security Verification Failed', 'Dear Administrator

Please check the Epayment Log, System has logged a Epayment Security failure. that may be a possible attack on epayment processing.

Time of Transaction: $time
Payment Gateway: $paymentgateway
Amount: $amount



Kind regards,
Call Labs
', '');

INSERT INTO cc_templatemail VALUES ('reminder', '[e-mail]', 'Call-Labs', 'REMINDER', '
Our record indicates that you have less than $min_credit usd in your "$card_gen" account.
We hope this message provides you with enough notice to refill your account.
We value your business, but our system can disconnect you automatically
when you reach your pre-paid balance.

Please login to your account through our website to check your account
details. Plus,
you can pay by credit card, on demand.

http://call-labs.com/A2BCustomer_UI/

If you believe this information to be incorrect please contact
[e-mail]
immediately.


Kind regards,
Call Labs
', '');

INSERT INTO cc_templatemail VALUES ('forgetpassword', '[e-mail]', 'Call-Labs', 'Login Information', 'Your login information is as below:

Your account is $card_gen

Your password is $password

Your cardalias is $cardalias

http://call-labs.com/A2BCustomer_UI/


Kind regards,
Call Labs
', '');

INSERT INTO cc_templatemail VALUES ('signupconfirmed', '[e-mail]', 'Call-Labs', 'SIGNUP CONFIRMATION', 'Thank you for registering with us

Please make sure you active your account by making payment to us either by
credit card, wire transfer, money order, cheque, and western union money
transfer, money Gram, and Pay pal.

Your account is $card_gen

Your password is $password

To go to your account :
http://call-labs.com/A2BCustomer_UI/

Kind regards,
Call Labs
', '');


INSERT INTO cc_templatemail VALUES ('payment', '[e-mail]', 'Call-Labs', 'PAYMENT CONFIRMATION', 'Thank you for shopping at Call-Labs.

Shopping details is as below.

Item Name = <b>$itemName</b>
Item ID = <b>$itemID</b>
Amount = <b>$itemAmount</b>
Payment Method = <b>$paymentMethod</b>
Status = <b>$paymentStatus</b>


Kind regards,
Call Labs
', '');

INSERT INTO cc_templatemail VALUES ('invoice', '[e-mail]', 'Call-Labs', 'A2BILLING INVOICE', 'Dear Customer.

Attached is the invoice.

Kind regards,
Call Labs
', '');

INSERT INTO cc_trunk VALUES (1, 'DEFAULT', '011', 'IAX2', '[e-mail]', '', 0, 0, 0, '2005-03-14 01:01:36', 0, '', NULL);


CREATE INDEX ind_cc_ratecard_dialprefix ON cc_ratecard USING btree (dialprefix);



ALTER TABLE ONLY cc_card
    ADD CONSTRAINT cc_card_pkey PRIMARY KEY (id);
	
ALTER TABLE ONLY cc_card
    ADD CONSTRAINT cons_username_cc_card UNIQUE (username);

ALTER TABLE ONLY cc_card
    ADD CONSTRAINT cons_useralias_cc_card UNIQUE (useralias);



ALTER TABLE ONLY cc_call
    ADD CONSTRAINT cc_call_pkey PRIMARY KEY (id);



ALTER TABLE ONLY cc_tariffgroup
    ADD CONSTRAINT cc_tariffgroup_pkey PRIMARY KEY (id);


ALTER TABLE ONLY cc_tariffplan
    ADD CONSTRAINT cc_tariffplan_pkey PRIMARY KEY (id);



ALTER TABLE ONLY cc_tariffplan
    ADD CONSTRAINT cons_iduser_tariffname UNIQUE (iduser, tariffname);


ALTER TABLE ONLY cc_tariffgroup_plan
    ADD CONSTRAINT pk_groupplan PRIMARY KEY (idtariffgroup, idtariffplan);



ALTER TABLE ONLY cc_ratecard
    ADD CONSTRAINT cc_ratecard_pkey PRIMARY KEY (id);


ALTER TABLE ONLY cc_trunk
    ADD CONSTRAINT cc_trunk_pkey PRIMARY KEY (id_trunk);

ALTER TABLE ONLY cc_sip_buddies
    ADD CONSTRAINT cc_sip_buddies_pkey PRIMARY KEY (id);


ALTER TABLE ONLY cc_sip_buddies
    ADD CONSTRAINT unique_name UNIQUE (name);



ALTER TABLE ONLY cc_iax_buddies
    ADD CONSTRAINT cc_iax_buddies_pkey PRIMARY KEY (id);


ALTER TABLE ONLY cc_iax_buddies
    ADD CONSTRAINT iax_unique_name UNIQUE (name);


SELECT pg_catalog.setval('cc_ui_authen_userid_seq', 3, true);

SELECT pg_catalog.setval('cc_trunk_id_trunk_seq', 2, true);