1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
<?php echo '<?xml version="1.0" encoding="iso-8895-15"?>' ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>pgETS</title>
<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>
<h1>ETS Call Accounting - Query Results</h1>
<?php
$local_prefix = "040";
$dbspec = "dbname=pgets user=pgets_web password=gandhi";
$n_order = (int) $HTTP_GET_VARS['order'];
$n_direction = (int) $HTTP_GET_VARS['direction'];
$order = $n_order ? "DESC" : "ASC";
if (isset($HTTP_GET_VARS['sdate'])) {
$sdate = addslashes($HTTP_GET_VARS['sdate']);
} else if (isset($HTTP_GET_VARS['sday'])) {
$sday = (int) $HTTP_GET_VARS['sday'];
$smon = (int) $HTTP_GET_VARS['smon'];
$syear = (int) $HTTP_GET_VARS['syear'];
$sdate = sprintf("%04s-%02s-%02s", $syear, $smon, $sday);
} else {
$sdate = "";
}
if (isset($HTTP_GET_VARS['edate'])) {
$edate = addslashes($HTTP_GET_VARS['edate']);
} else if (isset($HTTP_GET_VARS['eday'])) {
$eday = (int) $HTTP_GET_VARS['eday'];
$emon = (int) $HTTP_GET_VARS['emon'];
$eyear = (int) $HTTP_GET_VARS['eyear'];
$edate = sprintf("%04s-%02s-%02s", $eyear, $emon, $eday);
} else {
$edate = "";
}
if (isset($HTTP_GET_VARS['special'])) {
if ($HTTP_GET_VARS['special'] == 1)
$sdate = date("Y-m-01");
}
if ($edate != "" || $sdate != "") {
echo "<p><i>";
if ($edate != "" && $sdate != "") {
echo "Showing entries between $sdate (incl) and $edate (excl).";
} else if ($edate != "") {
echo "Showing entries before $edate (excl).";
} else if ($sdate != "") {
echo "Showing entries after $sdate (incl).";
}
echo "</p></i>";
}
$db = pg_pconnect($dbspec);
$where = "WHERE 1=1";
if ($n_direction == 1) $where = $where." AND incoming='f'";
if ($n_direction == 2) $where = $where." AND incoming='t'";
if ($sdate != "") $where = $where." AND _timestamp >= '$sdate'";
if ($edate != "") $where = $where." AND _timestamp < '$edate'";
$q = pg_query($db, "SELECT CASE WHEN SUBSTRING(remote_msn FROM 1 FOR 3)='".$local_prefix."' THEN SUBSTRING(TRIM(remote_msn) FROM 4) ELSE TRIM(remote_msn) END, CASE WHEN incoming='t' THEN 'Incoming' ELSE 'Outgoing' END, participant, TO_CHAR(_timestamp, 'DD.MM.YYYY HH24:MI'), CASE WHEN duration > 60 THEN duration/60||'m '||duration%60||'s' ELSE duration||'s' END FROM pgets_accounting ".$where." ORDER BY _timestamp ".$order);
$num = pg_numrows($q);
if ($num == 0) {
echo "<p><b>No entries found.</b></p>\n";
} else {
echo "<table summary=\"Phone calls\" cellspacing=\"0\" cellpadding=\"2\">\n<tr class=\"theader\"><th>Remote MSN</th><th><a href=\"pgets.php?sdate=$sdate&edate=$edate&order=$n_order&direction=".(($n_direction+1)%3)."\">Direction</a></th><th>P.</th><th><a href=\"pgets.php?sdate=$sdate&edate=$edate&order=".(1-$n_order)."&direction=$n_direction\">Start time</a></th><th>Duration</th></tr>\n";
$c = 0;
for ($i = 0; $i < $num; $i++) {
$r = pg_fetch_row($q, $i);
$c = 1-$c;
echo "<tr class=\"line$c\">";
for ($j=0; $j < count($r); $j++) {
echo "<td> $r[$j] </td>";
}
echo "</tr>\n";
}
echo "</table>\n";
$q = pg_query($db, "SELECT COUNT(*),SUM(duration) FROM pgets_accounting $where");
$r = pg_fetch_row($q, 0);
if ($r[1] > 3600) {
$sum = (int) ($r[1]/3600)."h ".(int)($r[1] % 3600 / 60)."m ".(int)($r[1] % 60)."s";
} else if ($r[1] > 60) {
$sum = (int)($r[1]/60)."m ".(int)($r[1] % 60)."s";
} else {
$sum = $r[1]."s";
}
echo "<p><b>$r[0] items, $sum total duration.</b></p>";
}
?>
<hr/>
<div class="grey"><i>Generated: <?php echo date("r") ?></i></div>
<div class="grey"><i>$Id$</i></div>
</body>
</html>
|
