a2billing-pgsql-schema-v1.3.0.sql

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

    id 									BIGSERIAL NOT NULL,
    cid 								TEXT NOT NULL,
    id_cc_card 							BIGINT NOT NULL,
    activated 							BOOLEAN DEFAULT true NOT NULL
);

ALTER TABLE ONLY cc_callerid
    ADD CONSTRAINT cc_calleridd_pkey PRIMARY KEY (id);

ALTER TABLE ONLY cc_callerid
    ADD CONSTRAINT cc_callerid_cid_key UNIQUE (cid);


CREATE TABLE cc_ui_authen (
    userid 								BIGSERIAL NOT NULL,
    login 								TEXT NOT NULL,
    "password" 							TEXT NOT NULL,
    groupid 							INTEGER,
    perms 								INTEGER,
    confaddcust 						INTEGER,
    name 								TEXT ,
    direction 							TEXT ,
    zipcode 							TEXT ,
    state 								TEXT ,
    phone	 							TEXT ,
    fax 								TEXT ,
    datecreation 						TIMESTAMP with time zone DEFAULT NOW()
);

ALTER TABLE ONLY cc_ui_authen
    ADD CONSTRAINT cc_ui_authen_pkey PRIMARY KEY (userid);

ALTER TABLE ONLY cc_ui_authen
    ADD CONSTRAINT cons_cc_ui_authen_login_key UNIQUE(login);

CREATE TABLE cc_call (
    id 									BIGSERIAL NOT NULL,
    sessionid 							TEXT NOT NULL,
    uniqueid 							TEXT NOT NULL,
    username 							TEXT NOT NULL,
    nasipaddress 						TEXT ,
    starttime 							TIMESTAMP WITHOUT TIME ZONE,
    stoptime 							TIMESTAMP WITHOUT TIME ZONE,
    sessiontime 						INTEGER,
    calledstation 						TEXT ,
    startdelay 							INTEGER,
    stopdelay 							INTEGER,
    terminatecause 						TEXT ,
    usertariff 							TEXT ,
    calledprovider 						TEXT ,
    calledcountry 						TEXT ,
    calledsub 							TEXT ,
    calledrate 							double precision,
    sessionbill 						double precision,
    destination 						TEXT ,
    id_tariffgroup 						INTEGER,
    id_tariffplan 						INTEGER,
    id_ratecard 						INTEGER,
    id_trunk 							INTEGER,
    sipiax 								INTEGER DEFAULT 0,
    src 								TEXT ,
    id_did 								INTEGER,
    buyrate 							NUMERIC(15,5) DEFAULT 0,
    buycost 							NUMERIC(15,5) DEFAULT 0,
	id_card_package_offer 				INTEGER DEFAULT 0
);


CREATE TABLE cc_templatemail (
    mailtype 							TEXT ,
    fromemail 							TEXT ,
    fromname 							TEXT ,
    subject 							TEXT ,
    messagetext 						TEXT ,
    messagehtml 						TEXT 
);
ALTER TABLE ONLY cc_templatemail
    ADD CONSTRAINT cons_cc_templatemail_mailtype UNIQUE (mailtype);



CREATE TABLE cc_tariffgroup (
    id 									serial NOT NULL,
    iduser 								INTEGER DEFAULT 0 NOT NULL,
    idtariffplan 						INTEGER DEFAULT 0 NOT NULL,
    tariffgroupname 					TEXT NOT NULL,
    lcrtype 							INTEGER DEFAULT 0 NOT NULL,
    creationdate 						TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    removeinterprefix 					INTEGER DEFAULT 0 NOT NULL,
	id_cc_package_offer 				BIGINT not null default 0
);



CREATE TABLE cc_tariffgroup_plan (
    idtariffgroup 						INTEGER NOT NULL,
    idtariffplan 						INTEGER NOT NULL
);



CREATE TABLE cc_tariffplan (
    id 									serial NOT NULL,
    iduser 								INTEGER DEFAULT 0 NOT NULL,
    tariffname 							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 							INTEGER DEFAULT 0,
    secondusedreal 						INTEGER DEFAULT 0,
    secondusedcarrier 					INTEGER DEFAULT 0,
    secondusedratecard 					INTEGER DEFAULT 0,
    reftariffplan 						INTEGER DEFAULT 0,
    idowner 							INTEGER DEFAULT 0,
    dnidprefix 							TEXT NOT NULL DEFAULT 'all'::text,
    calleridprefix 						TEXT NOT NULL DEFAULT 'all'::text
);



CREATE TABLE cc_card (
    id 									BIGSERIAL NOT NULL,
    creationdate 						TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    firstusedate 						TIMESTAMP WITHOUT TIME ZONE,
    expirationdate 						TIMESTAMP WITHOUT TIME ZONE,
    enableexpire 						INTEGER DEFAULT 0,
    expiredays 							INTEGER DEFAULT 0,
    username 							TEXT NOT NULL,
    useralias 							TEXT NOT NULL,
    userpass 							TEXT NOT NULL,
    uipass 								TEXT ,
    credit 								NUMERIC(12,4) NOT NULL,
    tariff 								INTEGER DEFAULT 0,
    id_didgroup 						INTEGER DEFAULT 0,
    activated 							BOOLEAN DEFAULT false NOT NULL,
    lastname 							TEXT ,
    firstname 							TEXT ,
    address 							TEXT ,
    city 								TEXT ,
    state 								TEXT ,
    country 							TEXT ,
    zipcode 							TEXT ,
    phone 								TEXT ,
    email 								TEXT ,
    fax 								TEXT ,
    inuse 								INTEGER DEFAULT 0,
    simultaccess 						INTEGER DEFAULT 0,
    currency 							CHARACTER VARYING(3) DEFAULT 'USD'::CHARACTER varying,
    lastuse 							date DEFAULT NOW(),
    nbused 								INTEGER DEFAULT 0,
    typepaid 							INTEGER DEFAULT 0,
    creditlimit 						INTEGER DEFAULT 0,
    voipcall 							INTEGER DEFAULT 0,
    sip_buddy 							INTEGER DEFAULT 0,
    iax_buddy 							INTEGER DEFAULT 0,
    "language" 							TEXT DEFAULT 'en'::text,
    redial 								TEXT ,
    runservice 							INTEGER DEFAULT 0,
    nbservice 							INTEGER DEFAULT 0,
    id_campaign 						INTEGER DEFAULT 0,
    num_trials_done 					INTEGER DEFAULT 0,
    callback 							TEXT ,
    vat 								NUMERIC(6,3) DEFAULT 0,
    servicelastrun 						TIMESTAMP WITHOUT TIME ZONE,
    initialbalance 						NUMERIC(12,4) NOT NULL DEFAULT 0,
    invoiceday 							INTEGER DEFAULT 1,
    autorefill 							INTEGER DEFAULT 0,
    loginkey 							TEXT ,
    activatedbyuser 					BOOLEAN DEFAULT false NOT NULL,
	id_subscription_fee 				INTEGER DEFAULT 0,
	mac_addr							VARCHAR(17) DEFAULT '00-00-00-00-00-00' NOT NULL
);

CREATE TABLE cc_ratecard (
    id 									serial NOT NULL,
    idtariffplan 						INTEGER DEFAULT 0 NOT NULL,
    dialprefix 							TEXT NOT NULL,
    destination 						TEXT NOT NULL,
    buyrate 							real DEFAULT 0 NOT NULL,
    buyrateinitblock 					INTEGER DEFAULT 0 NOT NULL,
    buyrateincrement 					INTEGER DEFAULT 0 NOT NULL,
    rateinitial 						real DEFAULT 0 NOT NULL,
    initblock 							INTEGER DEFAULT 0 NOT NULL,
    billingblock 						INTEGER DEFAULT 0 NOT NULL,
    connectcharge 						real DEFAULT 0 NOT NULL,
    disconnectcharge 					real DEFAULT 0 NOT NULL,
    stepchargea 						real DEFAULT 0 NOT NULL,
    chargea 							real DEFAULT 0 NOT NULL,
    timechargea 						INTEGER DEFAULT 0 NOT NULL,
    billingblocka 						INTEGER DEFAULT 0 NOT NULL,
    stepchargeb 						real DEFAULT 0 NOT NULL,
    chargeb 							real DEFAULT 0 NOT NULL,
    timechargeb 						INTEGER DEFAULT 0 NOT NULL,
    billingblockb 						INTEGER DEFAULT 0 NOT NULL,
    stepchargec 						real DEFAULT 0 NOT NULL,
    chargec 							real DEFAULT 0 NOT NULL,
    timechargec 						INTEGER DEFAULT 0 NOT NULL,
    billingblockc 						INTEGER DEFAULT 0 NOT NULL,
    startdate 							TIMESTAMP(0) without time zone DEFAULT NOW(),
    stopdate 							TIMESTAMP(0) without time zone,
    starttime 							INTEGER NOT NULL DEFAULT 0,
    endtime 							INTEGER NOT NULL DEFAULT 10079,
    id_trunk 							INTEGER DEFAULT -1,	
    musiconhold 						CHARACTER VARYING(100),
    freetimetocall_package_offer 		INTEGER NOT NULL DEFAULT 0,
    id_outbound_cidgroup 				INTEGER NOT NULL DEFAULT -1
);



CREATE TABLE cc_trunk (
    id_trunk 							serial NOT NULL,
    trunkcode 							TEXT NOT NULL,
    trunkprefix 						TEXT ,
    providertech 						TEXT NOT NULL,
    providerip 							TEXT NOT NULL,
    removeprefix 						TEXT ,
    secondusedreal 						INTEGER DEFAULT 0,
    secondusedcarrier 					INTEGER DEFAULT 0,
    secondusedratecard 					INTEGER DEFAULT 0,
    creationdate 						TIMESTAMP(0) without time zone DEFAULT NOW(),
    failover_trunk 						INTEGER,
    addparameter 						TEXT,
    id_provider							INTEGER
);


CREATE TABLE cc_sip_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),