src/billing/cisco_h323_db_sample_functions-postgres.sql

   1 -- Id: postgresql.conf,v 1.8.2.11 2003/07/15 11:15:43 pnixon Exp $
   2
   3 -- create plpgsql language
   4 CREATE FUNCTION "plpgsql_call_handler" () RETURNS LANGUAGE_HANDLER AS '$libdir/plpgsql' LANGUAGE C;
   5 CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";
   6
   7
   8 CREATE OR REPLACE FUNCTION chop_number(VARCHAR) RETURNS VARCHAR AS '
   9  DECLARE
  10      original_number ALIAS FOR $1;
  11      new_number VARCHAR;
  12  BEGIN
  13         new_number := split_part(original_number,''#'',2);
  14         IF new_number = '''' THEN
  15          RETURN original_number;
  16         ELSE RETURN new_number;
  17      END IF;
  18  END;
  19 ' LANGUAGE 'plpgsql';
  20
  21 CREATE OR REPLACE FUNCTION chop_number_country(VARCHAR) RETURNS VARCHAR AS '
  22  DECLARE
  23      original_number ALIAS FOR $1;
  24      new_number VARCHAR;
  25      clean_number VARCHAR;
  26  BEGIN
  27         new_number := split_part(original_number,''#'',2);
  28         IF new_number = '''' THEN
  29          clean_number := original_number;
  30         ELSE clean_number := new_number;
  31         END IF;
  32         IF substring(clean_number from 1 for 2) = ''00'' THEN
  33           RETURN substring(clean_number from 3 for 2);
  34         ELSIF substring(clean_number from 1 for 1) = ''0'' THEN
  35           RETURN '''';
  36         ELSE
  37           RETURN substring(clean_number from 1 for 2);
  38         END IF;
  39  END;
  40 ' LANGUAGE 'plpgsql';
  41
  42 CREATE OR REPLACE FUNCTION chop_number_city(VARCHAR) RETURNS VARCHAR AS '
  43  DECLARE
  44      original_number ALIAS FOR $1;
  45      new_number VARCHAR;
  46      clean_number VARCHAR;
  47  BEGIN
  48         new_number := split_part(original_number,''#'',2);
  49         IF new_number = '''' THEN
  50          clean_number := original_number;
  51         ELSE clean_number := new_number;
  52         END IF;
  53         IF substring(clean_number from 1 for 2) = ''00'' THEN
  54           RETURN substring(clean_number from 5 for 3);
  55         ELSIF substring(clean_number from 1 for 1) = ''0'' THEN
  56           RETURN substring(clean_number from 2 for 3);
  57         ELSE
  58           RETURN substring(clean_number from 3 for 3);
  59         END IF;
  60  END;
  61 ' LANGUAGE 'plpgsql';
  62
  63 CREATE OR REPLACE FUNCTION chop_number_number(VARCHAR) RETURNS VARCHAR AS '
  64  DECLARE
  65      original_number ALIAS FOR $1;
  66      new_number VARCHAR;
  67      clean_number VARCHAR;
  68  BEGIN
  69         new_number := split_part(original_number,''#'',2);
  70         IF new_number = '''' THEN
  71          clean_number := original_number;
  72         ELSE clean_number := new_number;
  73         END IF;
  74         IF substring(clean_number from 1 for 2) = ''00'' THEN
  75           RETURN substring(clean_number from 8 for 11);
  76         ELSIF substring(clean_number from 1 for 1) = ''0'' THEN
  77           RETURN substring(clean_number from 5 for 11);
  78         ELSE
  79           RETURN substring(clean_number from 6 for 11);
  80         END IF
  81  END;
  82 ' LANGUAGE 'plpgsql';
  83
  84 -- Some sample database VIEWs to simplify billing queries.
  85
  86 CREATE OR REPLACE VIEW StartVoIPd AS
  87 SELECT DISTINCT ON(h323SetupTime, CallID) * FROM StartVoIP;
  88
  89 CREATE OR REPLACE VIEW StopVoIPd AS
  90 SELECT DISTINCT ON(h323SetupTime, CallID) * FROM StopVoIP;
  91
  92
  93 CREATE OR REPLACE VIEW call_history_csps2 AS
  94 SELECT StartVoIP.h323ConnectTime, StopVoIP.h323DisconnectTime, (EXTRACT(EPOCH FROM(StopVoIP.h323DisconnectTime - StartVoIP.h323ConnectTime)))::BIGINT AS CallLength, StopVoIP.CalledStationId AS Number, StopVoIP.UserName AS UserName, StopVoIP.CallingStationId AS CallerID, StopVoIP.CallID
  95 FROM StopVoIPd AS StopVoIP LEFT OUTER JOIN StartVoIPd AS StartVoIP
  96 ON (StopVoIP.CallID = StartVoIP.CallID)
  97 WHERE StopVoIP.NASIPAddress = '212.50.54.122'
  98 ORDER BY StartVoIP.h323ConnectTime;
  99
 100 CREATE OR REPLACE VIEW call_history_csps AS
 101 SELECT CAST ((h323DisconnectTime::date AT TIME ZONE 'UTC') AS date) AS Date, CAST ((h323DisconnectTime AT TIME ZONE 'UTC') AS time without time zone) AS Time, AcctSessionTime AS Length, CalledStationId AS Number, UserName AS UserName, CallingStationId AS CallerID, CallID
 102 FROM StopVoIP
 103 WHERE NASIPAddress = '212.50.54.122';
 104
 105 CREATE OR REPLACE VIEW call_history AS
 106 SELECT CAST ((h323SetupTime::date AT TIME ZONE 'UTC') AS date) AS Date, CAST ((h323SetupTime AT TIME ZONE 'UTC') AS time without time zone) AS Time, AcctSessionTime AS Length, CalledStationId AS Number, UserName AS UserName, CallingStationId AS CallerID, H323RemoteAddress, NASIPAddress, CallID
 107 FROM StopVoIP;
 108
 109 CREATE OR REPLACE VIEW call_history AS
 110 SELECT CAST ((pots.h323SetupTime::date AT TIME ZONE 'UTC') AS date) AS Date, CAST ((pots.h323SetupTime AT TIME ZONE 'UTC') AS time without time zone) AS Time, pots.AcctSessionTime AS Length, pots.CalledStationId AS Number, ip.H323RemoteAddress AS cust_ip, ip.NASIPAddress AS gw_ip
 111 FROM StopTelephony AS pots LEFT OUTER JOIN StopVoIP AS ip
 112 ON (pots.CallID = ip.CallID);
 113
 114 CREATE OR REPLACE VIEW call_history_customer AS
 115 SELECT Date, Time, Length, Number, cust_ip, gw_ip, CustomerIP.Company AS Company
 116 FROM call_history LEFT OUTER JOIN customerip
 117 ON (call_history.cust_ip = CustomerIP.IpAddr);
 118
 119 CREATE OR REPLACE VIEW customerip AS
 120 SELECT gw.cust_gw AS IpAddr, cust.company AS Company, cust.customer AS Customer, gw.location AS Location
 121 FROM customers  AS cust, cust_gw AS gw
 122 WHERE cust.cust_id = gw.cust_id;
 123
 124 CREATE OR REPLACE VIEW VoIP AS
 125 SELECT RadAcctId AS ID, NASIPAddress AS GWIP, AcctSessionTime AS Call_Seconds, chop_number_country(CalledStationId) AS Country, chop_number_city(CalledStationId) AS City,chop_number_number(CalledStationId) AS Number, chop_number(CalledStationId) AS Original_Number, EXTRACT(YEAR FROM (h323setuptime AT TIME ZONE 'UTC')) AS Year, EXTRACT(MONTH FROM (h323setuptime AT TIME ZONE 'UTC')) AS Month, EXTRACT(DAY FROM (h323setuptime AT TIME ZONE 'UTC')) AS Day, CAST ((h323SetupTime AT TIME ZONE 'UTC') AS time without time zone) AS Time, h323DisconnectCause AS error_code, H323RemoteAddress AS Remote_IP, CallID
 126 FROM StopVoIP;
 127
 128 CREATE OR REPLACE VIEW Telephony AS
 129 SELECT RadAcctId AS ID, NASIPAddress AS GWIP, AcctSessionTime AS Call_Seconds, chop_number_country(CalledStationId) AS Country, chop_number_city(CalledStationId) AS City,chop_number_number(CalledStationId) AS Number, chop_number(CalledStationId) AS Original_Number, EXTRACT(YEAR FROM (h323setuptime AT TIME ZONE 'UTC')) AS Year, EXTRACT(MONTH FROM (h323setuptime AT TIME ZONE 'UTC')) AS Month, EXTRACT(DAY FROM (h323setuptime AT TIME ZONE 'UTC')) AS Day, CAST ((h323SetupTime AT TIME ZONE 'UTC') AS time without time zone) AS Time, h323DisconnectCause AS error_code, split_part(split_part(CiscoNASPort,':',1),' ',2) AS PRI, split_part(CiscoNASPort,':',3) AS PRI_channel, CiscoNASPort AS isdn_port, CallID AS ConfID
 130 FROM StopTelephony;
 131
 132 CREATE OR REPLACE VIEW calls AS
 133 SELECT Date, Time, Length, Number, cust_ip, gw_ip
 134 FROM call_history
 135 WHERE Length > 0
 136 ORDER BY Date, Time, Number, Length, cust_ip ASC;
 137
 138 CREATE OR REPLACE VIEW call_history_daily AS
 139 SELECT pots.h323ConnectTime, pots.AcctSessionTime, pots.CalledStationId, ip.H323RemoteAddress, pots.NASIPAddress
 140 FROM StopTelephony AS pots, StopVoIP AS ip
 141 WHERE pots.h323connecttime BETWEEN DATE'YESTERDAY' AND DATE'TODAY' AND pots.h323ConfID = ip.h323ConfID
 142 ORDER BY h323ConnectTime, CalledStationId ASC;
 143
 144 CREATE OR REPLACE VIEW call_errors AS
 145 SELECT pots.h323ConnectTime, pots.AcctSessionTime, pots.CalledStationId, ip.H323RemoteAddress, pots.NASIPAddress
 146 FROM StopTelephony  AS pots, StopVoIP AS ip
 147 WHERE pots.h323ConfID = ip.h323ConfID AND ip.h323disconnectcause <> 0 AND ip.h323disconnectcause <> 10
 148 AND ip.h323disconnectcause <> 11 AND ip.h323disconnectcause <> 13
 149 ORDER BY H323ConnectTime, CalledStationId, H323RemoteAddress ASC;
 150
 151
 152
 153 CREATE OR REPLACE FUNCTION VoIPInsertRecord(StopVoIP.UserName%TYPE, StopVoIP.NASIPAddress%TYPE, StopVoIP.AcctSessionTime%TYPE,
 154 StopVoIP.AcctInputOctets%TYPE, StopVoIP.AcctOutputOctets%TYPE, StopVoIP.CalledStationId%TYPE, StopVoIP.CallingStationId%TYPE,
 155 StopVoIP.AcctDelayTime%TYPE, StopVoIP.h323CallOrigin%TYPE, StopVoIP.h323SetupTime%TYPE, StopVoIP.h323ConnectTime%TYPE, StopVoIP.h323DisconnectTime%TYPE,
 156 StopVoIP.h323DisconnectCause%TYPE, StopVoIP.H323RemoteAddress%TYPE, StopVoIP.H323VoiceQuality%TYPE, StopVoIP.h323ConfID%TYPE) RETURNS BOOLEAN AS '
 157 DECLARE
 158     key1 ALIAS FOR $10;
 159     key2 ALIAS FOR $2;
 160     key3 ALIAS FOR $16;
 161 BEGIN
 162         PERFORM radacctid FROM StopVoIP WHERE h323SetupTime = $10 AND NASIPAddress = $2 AND CallID = $16;
 163         IF NOT FOUND THEN
 164                 INSERT into StopVoIP (
 165                 UserName, NASIPAddress, AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId,
 166                 AcctDelayTime, h323callorigin, h323setuptime, h323connecttime, h323disconnecttime, h323disconnectcause,
 167                 H323RemoteAddress, h323voicequality, CallID) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16);
 168         RETURN true;
 169         END IF;
 170         RETURN false;
 171 END;
 172 ' LANGUAGE 'plpgsql';
 173
 174 CREATE OR REPLACE FUNCTION TelephonyInsertRecord(StopTelephony.UserName%TYPE, StopTelephony.NASIPAddress%TYPE, StopTelephony.AcctSessionTime%TYPE,
 175     StopTelephony.AcctInputOctets%TYPE, StopTelephony.AcctOutputOctets%TYPE, StopTelephony.CalledStationId%TYPE, StopTelephony.CallingStationId%TYPE,
 176     StopTelephony.AcctDelayTime%TYPE, StopTelephony.CiscoNASPort%TYPE, StopTelephony.h323CallOrigin%TYPE, StopTelephony.h323SetupTime%TYPE,
 177     StopTelephony.h323ConnectTime%TYPE, StopTelephony.h323DisconnectTime%TYPE, StopTelephony.h323DisconnectCause%TYPE,
 178     StopTelephony.H323VoiceQuality%TYPE, StopTelephony.CallID%TYPE) RETURNS BOOLEAN AS '
 179 DECLARE
 180 BEGIN
 181         PERFORM radacctid FROM StopTelephony WHERE h323SetupTime = $11 AND NASIPAddress = $2 AND CallID = $16;
 182         IF NOT FOUND THEN
 183                 INSERT into StopTelephony (
 184                 UserName, NASIPAddress, AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId,
 185                 AcctDelayTime, CiscoNASPort, h323callorigin, h323setuptime, h323connecttime, h323disconnecttime, h323disconnectcause,
 186                 h323voicequality, CallID) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16);
 187         RETURN true;
 188         END IF;
 189         RETURN false;
 190 END;
 191 ' LANGUAGE 'plpgsql';