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>"; }