function au_callrates()
{
global $dbh, $db_name, $db_table_name, $group_by_field, $where, $result_limit, $graph_col_title;
/**************************** Config ****************************************************/
$au_call_rates = array("Local / Nat'l" => "(dst LIKE '4%' OR dst LIKE '07%' OR dst LIKE '02%' OR dst LIKE '03%' OR dst LIKE '08%' OR dst LIKE '3%')", "Mobile" => "(dst LIKE '04%')", "one3Call" => "(dst LIKE '13%')", "Int'l" => "(dst LIKE '001%')");
$au_callrates_csv_file = '/var/www/asterisk-cdr-viewer/include/plugins/au_callrates.csv';
/****************************************************************************************/
$au_bill_tototal_q = "SELECT {$group_by_field} AS group_by_field FROM {$db_table_name} {$where} GROUP BY group_by_field ORDER BY group_by_field ASC LIMIT {$result_limit}";
$au_callrates_total = array();
foreach (array_keys($au_call_rates) as $key) {
$au_call_rates_total["{$key}"] = 0;
}
$au_call_rates_total["summ"] = 0;
echo '<p class="center title">Australia rates ( plugin example )</p><table class="cdr">
<tr>
<th>' . $graph_col_title . '</th>
<th colspan=5>CALLS TO</th>
</tr>
<tr><th> </th>';
foreach (array_keys($au_call_rates) as $key) {
echo "<th>{$key}</th>";
}
echo "<th>TOTAL<br/>(inc GST)</th></tr>";
try {
$sth = $dbh->query($au_bill_tototal_q);
if (!$sth) {
echo "\nPDO::errorInfo():\n";
print_r($dbh->errorInfo());
}
$result = $sth->fetchAll(PDO::FETCH_NUM);
$sth = NULL;
foreach ($result as $row) {
$summ = 0;
echo "<tr class=\"record\">";
echo "<td>" . $row[0] . "</td>";
foreach (array_keys($au_call_rates) as $key) {
$au_bill_ch_q = "SELECT dst, billsec FROM {$db_table_name} {$where} and {$group_by_field} = '" . $row[0] . "' and " . $au_call_rates["{$key}"];
$summ_local = 0;
$sth2 = $dbh->query($au_bill_ch_q);
if (!$sth2) {
echo "\nPDO::errorInfo():\n";
print_r($dbh->errorInfo());
}
while ($bill_row = $sth2->fetch(PDO::FETCH_NUM)) {
$rates = callrates($bill_row[0], $bill_row[1], $au_callrates_csv_file);
$summ_local += $rates[4];
}
$sth2 = NULL;
$au_call_rates_total["{$key}"] += $summ_local;
$summ += $summ_local;
formatMoney($summ_local);
}
$au_call_rates_total["summ"] += $summ;
formatMoney($summ);
echo "</tr>";
}
} catch (PDOException $e) {
print $e->getMessage();
}
echo "<tr class=\"chart_data\">";
echo "<td>Total</td>";
foreach (array_keys($au_call_rates_total) as $key) {
formatMoney($au_call_rates_total["{$key}"]);
}
echo "</tr>";
echo "</table>";
}
formatChannel($row['channel']);
if (isset($display_column['clid']) and $display_column['clid'] == 1) {
formatClid($row['clid']);
}
formatChannel($row['dstchannel']);
formatDisposition($row['disposition'], $row['amaflags']);
formatDuration($row['duration'], $row['billsec']);
if (isset($display_column['billsec']) and $display_column['billsec'] == 1) {
formatBillSec($row['billsec']);
}
formatUserField($row['userfield']);
if (isset($display_column['accountcode']) and $display_column['accountcode'] == 1) {
formatAccountCode($row['accountcode']);
}
if (isset($_REQUEST['use_callrates']) && $_REQUEST['use_callrates'] == 'true') {
$rates = callrates($row['dst'], $row['billsec'], $callrate_csv_file);
$rate_total += $rates[4];
formatMoney($rates[4]);
echo "<td>" . htmlspecialchars($rates[2]) . "</td>\n";
}
echo " <td></td>\n";
echo " <td></td>\n";
echo " </tr>\n";
}
} catch (PDOException $e) {
print $e->getMessage();
}
if (isset($_REQUEST['use_callrates']) && $_REQUEST['use_callrates'] == 'true') {
echo "<tr><td colspan='{$total_rows}' align='right'>Total: </td>";
formatMoney($rate_total);
echo "</b></td></tr>\n";
function my_callrates()
{
global $dbh, $db_name, $db_table_name, $group_by_field, $where, $result_limit, $graph_col_title, $callrate_csv_fileName;
/**************************** Config ****************************************************/
$my_call_rates = array("Городские" => "(dst LIKE '2%' OR dst LIKE '7%') and (LENGTH(dst)=7)", "Мобильные" => "(dst LIKE '89%') and (LENGTH(dst)=11)", "Область" => "(dst LIKE '8351%') and (LENGTH(dst)=11)", "Столицы" => "(dst LIKE '8495%' OR dst LIKE '8499%' OR dst LIKE '8812%') and (LENGTH(dst)=11)", "Россия" => "(dst LIKE '8%') and (dst NOT LIKE '89%' and dst NOT LIKE '79%' and dst NOT LIKE '8351%' and dst NOT LIKE '8495%' and dst NOT LIKE '8499%' and dst NOT LIKE '8812%' and dst NOT LIKE '8800%') and (LENGTH(dst)=11)");
$my_callrates_csv_file = dirname(__FILE__) . '/' . $callrate_csv_fileName;
/****************************************************************************************/
$my_bill_tototal_q = "SELECT {$group_by_field} AS group_by_field FROM {$db_name}.{$db_table_name} {$where} GROUP BY group_by_field ORDER BY group_by_field ASC LIMIT {$result_limit}";
$my_callrates_total = array();
foreach (array_keys($my_call_rates) as $key) {
$my_call_rates_total["{$key}"] = 0;
}
$my_call_rates_total["summ"] = 0;
echo '<p class="center title">Детализация звонков - Расход денежных средств</p><table class="cdr">
<tr>
<th>' . $graph_col_title . '</th>
<th colspan=5>Направление</th>
</tr>
<tr><th> </th>';
foreach (array_keys($my_call_rates) as $key) {
echo "<th>{$key}</th>";
}
echo "<th>Итого</th></tr>";
try {
$sth = $dbh->query($my_bill_tototal_q);
if (!$sth) {
echo "\nPDO::errorInfo():\n";
print_r($dbh->errorInfo());
}
$result = $sth->fetchAll(PDO::FETCH_NUM);
$sth = NULL;
foreach ($result as $row) {
$summ = 0;
echo "<tr class=\"record\">";
echo '<td style="text-align:center;">' . $row[0] . "</td>";
foreach (array_keys($my_call_rates) as $key) {
$my_bill_ch_q = "SELECT dst, billsec FROM {$db_name}.{$db_table_name} {$where} and {$group_by_field} = '" . $row[0] . "' and " . $my_call_rates["{$key}"];
$summ_local = 0;
$sth2 = $dbh->query($my_bill_ch_q);
if (!$sth2) {
echo "\nPDO::errorInfo():\n";
print_r($dbh->errorInfo());
}
while ($bill_row = $sth2->fetch(PDO::FETCH_NUM)) {
$rates = callrates($bill_row[0], $bill_row[1], $my_callrates_csv_file);
$summ_local += $rates[4];
}
$sth2 = NULL;
$my_call_rates_total["{$key}"] += $summ_local;
$summ += $summ_local;
formatMoney($summ_local);
}
$my_call_rates_total["summ"] += $summ;
formatMoney($summ);
echo "</tr>";
}
} catch (PDOException $e) {
print $e->getMessage();
}
echo "<tr class=\"chart_data total\">";
echo "<td>Всего</td>";
foreach (array_keys($my_call_rates_total) as $key) {
formatMoney($my_call_rates_total["{$key}"]);
}
echo "</tr>";
echo "</table>";
}
