}
$_SESSION["pr_sql_export"] = "SELECT {$FG_COL_QUERY} FROM {$FG_TABLE_NAME} WHERE {$FG_TABLE_CLAUSE}";
$QUERY = "SELECT DATE(t1.starttime) AS day, sum(t1.sessiontime) AS calltime, sum(t1.sessionbill) AS cost, count(*) as nbcall FROM {$FG_TABLE_NAME} WHERE " . $FG_TABLE_CLAUSE . " GROUP BY day ORDER BY day";
//extract(DAY from calldate)
if (!$nodisplay) {
$res = $DBHandle->Execute($QUERY);
if ($res) {
$num = $res->RecordCount();
for ($i = 0; $i < $num; $i++) {
$list_total_day[] = $res->fetchRow();
}
}
if ($FG_DEBUG == 3) {
echo "<br>Clause : {$FG_TABLE_CLAUSE}";
}
$nb_record = $instance_table->Table_count($DBHandle, $FG_TABLE_CLAUSE);
if ($FG_DEBUG >= 1) {
var_dump($list);
}
}
if ($nb_record <= $FG_LIMITE_DISPLAY) {
$nb_record_max = 1;
} else {
if ($nb_record % $FG_LIMITE_DISPLAY == 0) {
$nb_record_max = intval($nb_record / $FG_LIMITE_DISPLAY);
} else {
$nb_record_max = intval($nb_record / $FG_LIMITE_DISPLAY) + 1;
}
}
$smarty->display('main.tpl');
// #### HELP SECTION
function isFKDataExists()
{
$processed = $this->getProcessed();
$tableCount = count($this->FG_FK_TABLENAMES);
$clauseCount = count($this->FG_FK_EDITION_CLAUSE);
$rowcount = 0;
if ($tableCount == $clauseCount && $clauseCount > 0) {
for ($i = 0; $i < $tableCount; $i++) {
if ($processed['id'] != "" || !is_null($processed['id'])) {
$instance_table = new Table($this->FG_FK_TABLENAMES[$i]);
$rowcount = $rowcount + $instance_table->Table_count($this->DBHandle, $this->FG_FK_EDITION_CLAUSE[$i], $processed['id']);
}
}
}
$this->FG_FK_RECORDS_COUNT = $rowcount;
return $rowcount > 0;
}
}
}
$instance_table = new Table($FG_TABLE_NAME, $FG_COL_QUERY);
if (!$nodisplay) {
$list = $instance_table->Get_list($DBHandle, $FG_TABLE_CLAUSE, $order, $sens, null, null, $FG_LIMITE_DISPLAY, $current_page * $FG_LIMITE_DISPLAY, $SQL_GROUP);
$res = $DBHandle->Execute($QUERY_TOTAL);
if ($res) {
$num = $res->RecordCount();
for ($i = 0; $i < $num; $i++) {
$list_total[] = $res->fetchRow();
}
}
if ($FG_DEBUG == 3) {
echo "<br>Clause : {$FG_TABLE_CLAUSE}";
}
$nb_record = $instance_table->Table_count($DBHandle);
if ($FG_DEBUG >= 1) {
var_dump($list);
}
}
//end IF nodisplay
if ($nb_record <= $FG_LIMITE_DISPLAY) {
$nb_record_max = 1;
} else {
if ($nb_record % $FG_LIMITE_DISPLAY == 0) {
$nb_record_max = intval($nb_record / $FG_LIMITE_DISPLAY);
} else {
$nb_record_max = intval($nb_record / $FG_LIMITE_DISPLAY) + 1;
}
}
if ($FG_DEBUG == 3) {
$RateEngine = new RateEngine();
// $RateEngine -> webui = 0;
// LOOKUP RATE : FIND A RATE FOR THIS DESTINATION
$A2B -> agiconfig['use_dnid']=1;
$A2B -> agiconfig['say_timetocall']=0;
$A2B -> agiconfig['say_balance_after_auth']=0;
$A2B -> extension = $A2B -> dnid = $A2B -> destination = $caller_areacode.$A2B -> apply_rules($A2B -> CallerID);
$QUERY = "SELECT callback FROM cc_callerid WHERE cid=$A2B->destination AND (callback=0 OR activated='f')";
$result = $A2B -> instance_table -> SQLExec ($A2B->DBHandle, $QUERY);
$instance_table = new Table("cc_callback_spool");
$FG_TABLE_CLAUSE = "callerid='{$A2B->config['callback']['callerid']}' and exten_leg_a='{$A2B->destination}' and timediff(now(),entry_time)<{$A2B->config['callback']['sec_avoid_repeate']}";
$FG_NB_RECORD = $instance_table -> Table_count ($A2B -> DBHandle, $FG_TABLE_CLAUSE);
if (!is_array($result) && $FG_NB_RECORD == 0) {
$resfindrate = $RateEngine->rate_engine_findrates($A2B, $A2B -> destination, $A2B -> tariff);
// IF FIND RATE
if ($resfindrate!=0) {
//$RateEngine -> debug_st = 1;
$res_all_calcultimeout = $RateEngine->rate_engine_all_calcultimeout($A2B, $A2B->credit);
if ($res_all_calcultimeout){
// MAKE THE CALL
$channeloutcid = $RateEngine->rate_engine_performcall($agi, $A2B->destination, $A2B, 9);
if ($channeloutcid) {
$channel = $channeloutcid[0];
$sep = ($A2B->config['global']['asterisk_version'] == "1_2" || $A2B->config['global']['asterisk_version'] == "1_4")?'|':',';
$HD_Form->FG_EDITION_CLAUSE = "id_cc_card='" . $_SESSION["card_id"] . "' AND id = " . $id;
}
$list = $HD_Form->perform_action($form_action);
// #### HEADER SECTION
$smarty->display('main.tpl');
// #### HELP SECTION
if ($form_action == 'list') {
echo $CC_help_list_did;
}
// #### TOP SECTION PAGE
$HD_Form->create_toppage($form_action);
$instance_table = new Table($HD_Form->FG_TABLE_NAME, $HD_Form->FG_COL_QUERY);
$instance_table_phonenumberdid = new Table($HD_Form->FG_TABLE_NAME, $HD_Form->FG_COL_QUERY);
$list_phonenumberdid = $instance_table_phonenumberdid->Get_list($HD_Form->DBHandle, $HD_Form->FG_TABLE_CLAUSE, $order, $sens, null, null, $limite, $current_record);
$nb_record = count($list_phonenumberdid);
$nb_record = $instance_table->Table_count($HD_Form->DBHandle, $HD_Form->FG_TABLE_CLAUSE);
if ($nb_record <= $FG_LIMITE_DISPLAY) {
$nb_record_max = 1;
} else {
if ($nb_record % $FG_LIMITE_DISPLAY == 0) {
$nb_record_max = intval($nb_record / $FG_LIMITE_DISPLAY);
} else {
$nb_record_max = intval($nb_record / $FG_LIMITE_DISPLAY) + 1;
}
}
$instance_table_country = new Table("cc_country, cc_did", "cc_country.id, countryname");
$FG_TABLE_CLAUSE = "id_cc_country=cc_country.id and cc_did.reserved=0 group by cc_country.id, countryname ";
$list_country = $instance_table_country->Get_list($HD_Form->DBHandle, $FG_TABLE_CLAUSE, "countryname", "ASC", null, null, null, null);
$nb_country = count($list_country);
if (!isset($new_did_page) || $new_did_page == "") {
$new_did_page = 0;
function EmailInvoice($id, $invoice_type = 1)
{
//getpost_ifset(array('customer', 'posted', 'Period', 'cardid','exporttype','choose_billperiod','id','invoice_type'));
if ($invoice_type == "") {
$invoice_type = 1;
}
if ($invoice_type == 1) {
$cardid = $id;
if ($cardid == "") {
exit("Invalid ID");
}
}
if ($invoice_type == 2) {
if ($id == "" || !is_numeric($id)) {
exit(gettext("Invalid ID"));
}
}
if ($invoice_type == 1) {
$invoice_heading = gettext("Unbilled Details");
$invocie_top_heading = gettext("Unbilled Invoice Details for Card Number");
} else {
$invoice_heading = gettext("Billed Details");
$invocie_top_heading = gettext("Billed Invoice Details for Card Number");
}
$DBHandle = DbConnect();
$num = 0;
if ($invoice_type == 1) {
$QUERY = "Select username, vat, t1.id from cc_card t1 where t1.id = {$cardid}";
} else {
$QUERY = "Select username, vat, t1.id from cc_card t1, cc_invoices t2 where t1.id = t2.cardid and t2.id = {$id}";
}
$res_user = $DBHandle->Execute($QUERY);
if ($res_user) {
$num = $res_user->RecordCount();
}
if ($num > 0) {
$userRecord = $res_user->fetchRow();
$customer = $userRecord[0];
$vat = $userRecord[1];
$customerID = $userRecord[2];
} else {
exit(gettext("No User found"));
}
if (!isset($current_page) || $current_page == "") {
$current_page = 0;
}
// this variable specifie the debug type (0 => nothing, 1 => sql result, 2 => boucle checking, 3 other value checking)
$FG_DEBUG = 0;
// The variable FG_TABLE_NAME define the table name to use
$FG_TABLE_NAME = "cc_call t1";
// The variable Var_col would define the col that we want show in your table
// First Name of the column in the html page, second name of the field
$FG_TABLE_COL = array();
$FG_TABLE_COL[] = array(gettext("Calldate"), "starttime", "18%", "center", "SORT", "19", "", "", "", "", "", "display_dateformat");
$FG_TABLE_COL[] = array(gettext("Source"), "src", "10%", "center", "SORT", "30");
$FG_TABLE_COL[] = array(gettext("Callednumber"), "calledstation", "18%", "right", "SORT", "30", "", "", "", "", "", "");
$FG_TABLE_COL[] = array(gettext("Destination"), "destination", "18%", "center", "SORT", "30", "", "", "", "", "", "remove_prefix");
$FG_TABLE_COL[] = array(gettext("Duration"), "sessiontime", "8%", "center", "SORT", "30", "", "", "", "", "", "display_minute");
if (!(isset($customer) && $customer > 0) && !(isset($entercustomer) && $entercustomer > 0)) {
$FG_TABLE_COL[] = array(gettext("Cardused"), "username", "11%", "center", "SORT", "30");
}
$FG_TABLE_COL[] = array(gettext("Cost"), "sessionbill", "9%", "center", "SORT", "30", "", "", "", "", "", "display_2bill");
$FG_TABLE_DEFAULT_ORDER = "t1.starttime";
$FG_TABLE_DEFAULT_SENS = "DESC";
// This Variable store the argument for the SQL query
$FG_COL_QUERY = 't1.starttime, t1.src, t1.calledstation, t1.destination, t1.sessiontime ';
if (!(isset($customer) && $customer > 0) && !(isset($entercustomer) && $entercustomer > 0)) {
$FG_COL_QUERY .= ', t1.username';
}
$FG_COL_QUERY .= ', t1.sessionbill';
if (LINK_AUDIO_FILE == 'YES') {
$FG_COL_QUERY .= ', t1.uniqueid';
}
$FG_COL_QUERY_GRAPH = 't1.callstart, t1.duration';
// The variable LIMITE_DISPLAY define the limit of record to display by page
$FG_LIMITE_DISPLAY = 500;
// Number of column in the html table
$FG_NB_TABLE_COL = count($FG_TABLE_COL);
//This variable will store the total number of column
$FG_TOTAL_TABLE_COL = $FG_NB_TABLE_COL;
if ($FG_DELETION || $FG_EDITION) {
$FG_TOTAL_TABLE_COL++;
}
if ($FG_DEBUG == 3) {
echo "<br>Table : {$FG_TABLE_NAME} \t- \tCol_query : {$FG_COL_QUERY}";
}
$instance_table = new Table($FG_TABLE_NAME, $FG_COL_QUERY);
$instance_table_graph = new Table($FG_TABLE_NAME, $FG_COL_QUERY_GRAPH);
if (is_null($order) || is_null($sens)) {
$order = $FG_TABLE_DEFAULT_ORDER;
$sens = $FG_TABLE_DEFAULT_SENS;
}
if ($posted == 1) {
$SQLcmd = '';
$SQLcmd = do_field($SQLcmd, 'src', 'src');
$SQLcmd = do_field($SQLcmd, 'dst', 'calledstation');
}
$date_clause = '';
// Period (Month-Day)
if (DB_TYPE == "postgres") {
$UNIX_TIMESTAMP = "";
} else {
$UNIX_TIMESTAMP = "UNIX_TIMESTAMP";
}
$lastdayofmonth = date("t", strtotime($tostatsmonth . '-01'));
if ($Period == "Month") {
if ($frommonth && isset($fromstatsmonth)) {
$date_clause .= " AND {$UNIX_TIMESTAMP}(t1.starttime) >= {$UNIX_TIMESTAMP}('{$fromstatsmonth}-01')";
}
if ($tomonth && isset($tostatsmonth)) {
$date_clause .= " AND {$UNIX_TIMESTAMP}(t1.starttime) <= {$UNIX_TIMESTAMP}('" . $tostatsmonth . "-{$lastdayofmonth} 23:59:59')";
}
} else {
if ($fromday && isset($fromstatsday_sday) && isset($fromstatsmonth_sday) && isset($fromstatsmonth_shour) && isset($fromstatsmonth_smin)) {
$date_clause .= " AND {$UNIX_TIMESTAMP}(t1.starttime) >= {$UNIX_TIMESTAMP}('{$fromstatsmonth_sday}-{$fromstatsday_sday} {$fromstatsmonth_shour}:{$fromstatsmonth_smin}')";
}
if ($today && isset($tostatsday_sday) && isset($tostatsmonth_sday) && isset($tostatsmonth_shour) && isset($tostatsmonth_smin)) {
$date_clause .= " AND {$UNIX_TIMESTAMP}(t1.starttime) <= {$UNIX_TIMESTAMP}('{$tostatsmonth_sday}-" . sprintf("%02d", intval($tostatsday_sday)) . " {$tostatsmonth_shour}:{$tostatsmonth_smin}')";
}
}
if (strpos($SQLcmd, 'WHERE') > 0) {
$FG_TABLE_CLAUSE = substr($SQLcmd, 6) . $date_clause;
} elseif (strpos($date_clause, 'AND') > 0) {
$FG_TABLE_CLAUSE = substr($date_clause, 5);
}
if (isset($customer) && $customer > 0) {
if (strlen($FG_TABLE_CLAUSE) > 0) {
$FG_TABLE_CLAUSE .= " AND ";
}
$FG_TABLE_CLAUSE .= "t1.username='******'";
} else {
if (isset($entercustomer) && $entercustomer > 0) {
if (strlen($FG_TABLE_CLAUSE) > 0) {
$FG_TABLE_CLAUSE .= " AND ";
}
$FG_TABLE_CLAUSE .= "t1.username='******'";
}
}
if (strlen($FG_TABLE_CLAUSE) > 0) {
$FG_TABLE_CLAUSE .= " AND ";
}
if ($invoice_type == 1) {
$FG_TABLE_CLAUSE .= "t1.starttime >(Select CASE WHEN max(cover_enddate) IS NULL THEN '0001-01-01 01:00:00' ELSE max(cover_enddate) END from cc_invoices WHERE cardid = '{$cardid}')";
} else {
$FG_TABLE_CLAUSE .= "t1.starttime >(Select cover_startdate from cc_invoices where id ='{$id}') AND t1.stoptime <(Select cover_enddate from cc_invoices where id ='{$id}') ";
}
if (!$nodisplay) {
$list = $instance_table->Get_list($DBHandle, $FG_TABLE_CLAUSE, $order, $sens, null, null, $FG_LIMITE_DISPLAY, $current_page * $FG_LIMITE_DISPLAY);
}
$_SESSION["pr_sql_export"] = "SELECT {$FG_COL_QUERY} FROM {$FG_TABLE_NAME} WHERE {$FG_TABLE_CLAUSE}";
/************************/
$QUERY = "SELECT substring(t1.starttime,1,10) AS day, sum(t1.sessiontime) AS calltime, sum(t1.sessionbill) AS cost, count(*) as nbcall FROM {$FG_TABLE_NAME} WHERE " . $FG_TABLE_CLAUSE . " GROUP BY substring(t1.starttime,1,10) ORDER BY day";
//extract(DAY from calldate)
if (!$nodisplay) {
$list_total_day = $instance_table->SQLExec($DBHandle, $QUERY);
$nb_record = $instance_table->Table_count($DBHandle, $FG_TABLE_CLAUSE);
}
// GROUP BY DESTINATION FOR THE INVOICE
$QUERY = "SELECT destination, sum(t1.sessiontime) AS calltime, \n\tsum(t1.sessionbill) AS cost, count(*) as nbcall FROM {$FG_TABLE_NAME} WHERE " . $FG_TABLE_CLAUSE . " GROUP BY destination";
if (!$nodisplay) {
$list_total_destination = $instance_table->SQLExec($DBHandle, $QUERY);
}
//end IF nodisplay
/************************************************ DID Billing Section *********************************************/
// Fixed + Dial = 0 ; Fixed = 1 ; Dail = 2 ; Free = 3
// 1. Billing Type:: All DID Calls that have DID Type 0 and 2
if ($invoice_type == 1) {
$QUERY = "SELECT t1.amount, t1.creationdate, t1.description, t3.countryname, t2.did, t1.currency " . " FROM cc_charge t1 LEFT JOIN (cc_did t2, cc_country t3 ) ON ( t1.id_cc_did = t2.id AND t2.id_cc_country = t3.id ) " . " WHERE (t1.chargetype = 1 OR t1.chargetype = 2) AND t1.id_cc_card = " . $cardid . " AND t1.creationdate >(Select CASE WHEN max(cover_enddate) IS NULL THEN '0001-01-01 01:00:00' ELSE max(cover_enddate) END from cc_invoices)";
} else {
$QUERY = "SELECT t1.amount, t1.creationdate, t1.description, t3.countryname, t2.did, t1.currency " . " FROM cc_charge t1 LEFT JOIN (cc_did t2, cc_country t3 ) ON ( t1.id_cc_did = t2.id AND t2.id_cc_country = t3.id ) " . " WHERE (t1.chargetype = 2 OR t1.chargetype = 1) AND t1.id_cc_card = " . $customerID . " AND t1.creationdate > (Select cover_startdate from cc_invoices where id ='{$id}') AND t1.creationdate <(Select cover_enddate from cc_invoices where id ='{$id}')";
}
if (!$nodisplay) {
$list_total_did = $instance_table->SQLExec($DBHandle, $QUERY);
}
/*************************************************CHARGES SECTION START ************************************************/
// Charge Types
// Connection charge for DID setup = 1
// Monthly Charge for DID use = 2
// Subscription fee = 3
// Extra charge = 4
if ($invoice_type == 1) {
$QUERY = "SELECT t1.id_cc_card, t1.iduser, t1.creationdate, t1.amount, t1.chargetype, t1.id_cc_did, t1.currency, t1.description" . " FROM cc_charge t1, cc_card t2 WHERE (t1.chargetype <> 1 AND t1.chargetype <> 2) " . " AND t2.username = '******' AND t1.id_cc_card = t2.id AND t1.creationdate >= (Select CASE WHEN max(cover_enddate) is NULL " . " THEN '0001-01-01 01:00:00' ELSE max(cover_enddate) END from cc_invoices) Order by t1.creationdate";
} else {
$QUERY = "SELECT t1.id_cc_card, t1.iduser, t1.creationdate, t1.amount, t1.chargetype, t1.id_cc_did, t1.currency, t1.description" . " FROM cc_charge t1, cc_card t2 WHERE (t1.chargetype <> 2 AND t1.chargetype <> 1)" . " AND t2.username = '******' AND t1.id_cc_card = t2.id AND " . " t1.creationdate >(Select cover_startdate from cc_invoices where id ='{$id}') " . " AND t1.creationdate <(Select cover_enddate from cc_invoices where id ='{$id}')";
}
if (!$nodisplay) {
$list_total_charges = $instance_table->SQLExec($DBHandle, $QUERY);
}
/*************************************************CHARGES SECTION END ************************************************/
if ($nb_record <= $FG_LIMITE_DISPLAY) {
$nb_record_max = 1;
} else {
if ($nb_record % $FG_LIMITE_DISPLAY == 0) {
$nb_record_max = intval($nb_record / $FG_LIMITE_DISPLAY);
} else {
$nb_record_max = intval($nb_record / $FG_LIMITE_DISPLAY) + 1;
}
}
/*************************************************************/
if (isset($customer) && $customer > 0 || isset($entercustomer) && $entercustomer > 0) {
$FG_TABLE_CLAUSE = "";
if (isset($customer) && $customer > 0) {
$FG_TABLE_CLAUSE = " username='******' ";
} elseif (isset($entercustomer) && $entercustomer > 0) {
$FG_TABLE_CLAUSE = " username='******' ";
}
$instance_table_customer = new Table("cc_card", "id, username, lastname, firstname, address, city, state, country, zipcode, phone, email, fax, activated, creationdate");
$info_customer = $instance_table_customer->Get_list($DBHandle, $FG_TABLE_CLAUSE, "id", "ASC", null, null, null, null);
}
if ($invoice_type == 1) {
$QUERY = "Select CASE WHEN max(cover_enddate) is NULL THEN '0001-01-01 01:00:00' ELSE max(cover_enddate) END from cc_invoices WHERE cardid = " . $cardid;
} else {
$QUERY = "Select cover_enddate,cover_startdate from cc_invoices where id ='{$id}'";
}
if (!$nodisplay) {
$invoice_dates = $instance_table->SQLExec($DBHandle, $QUERY);
if ($invoice_dates[0][0] == '0001-01-01 01:00:00') {
$invoice_dates[0][0] = $info_customer[0][13];
}
}
require '../Public/pdf-invoices/html2pdf/html2fpdf.php';
ob_start();
$currencies_list = get_currencies();
//For DID DIAL & Fixed + Dial
$totalcost = 0;
if (is_array($list_total_destination) && count($list_total_destination) > 0) {
$mmax = 0;
$totalcall = 0;
$totalminutes = 0;
foreach ($list_total_destination as $data) {
if ($mmax < $data[1]) {
$mmax = $data[1];
}
$totalcall += $data[3];
$totalminutes += $data[1];
$totalcost += $data[2];
}
}
?>
<table cellpadding="0" align="center">
<tr>
<td align="center">
<img src="<?php
echo Images_Path;
?>
/asterisk01.jpg" align="middle">
</td>
</tr>
</table>
<br>
<center><h4><font color="#FF0000"><?php
echo $invocie_top_heading;
?>
<?php
echo $info_customer[0][1];
?>
</font></h4></center>
<br>
<br>
<table cellspacing="0" cellpadding="2" align="center" width="80%" >
<tr>
<td colspan="2" bgcolor="#FFFFCC"><font size="5" color="#FF0000"><?php
echo $invoice_heading;
?>
</font></td>
</tr>
<tr>
<td valign="top" colspan="2"></td>
</tr>
<tr>
<td width="35%"> </td>
<td > </td>
</tr>
<tr>
<td width="35%" ><font color="#003399"><?php
echo gettext("Name");
?>
:</font> </td>
<td ><font color="#003399"><?php
echo $info_customer[0][3] . " " . $info_customer[0][2];
?>
</font></td>
</tr>
<tr>
<td width="35%" ><font color="#003399"><?php
echo gettext("Card Number");
?>
:</font></td>
<td ><font color="#003399"><?php
echo $info_customer[0][1];
?>
</font> </td>
</tr>
<?php
if ($invoice_type == 1) {
?>
<tr>
<td width="35%" ><font color="#003399"><?php
echo gettext("From Date");
?>
:</font></td>
<td><font color="#003399"><?php
echo display_dateonly($invoice_dates[0][0]);
?>
</font></td>
</tr>
<?php
} else {
?>
<tr>
<td width="35%" ><font color="#003399"><?php
echo gettext("From Date");
?>
:</font></td>
<td ><font color="#003399"><?php
echo display_dateonly($invoice_dates[0][1]);
?>
</font></td>
</tr>
<tr>
<td width="35%" ><font color="#003399"><?php
echo gettext("To Date");
?>
:</font></td>
<td><font color="#003399"><?php
echo display_dateonly($invoice_dates[0][0]);
?>
</font></td>
</tr>
<?php
}
?>
</table>
<table align="center" width="80%">
<?php
if (is_array($list_total_destination) && count($list_total_destination) > 0) {
?>
<tr>
<td colspan="4" align="center"><font> <b><?php
echo gettext("By Destination");
?>
</b></font> </td>
</tr>
<tr bgcolor="#CCCCCC">
<td width="29%"><font color="#003399"><b><?php
echo gettext("Destination");
?>
</b></font></td>
<td width="38%" ><font color="#003399"><b><?php
echo gettext("Duration");
?>
</b></font> </td>
<td width="12%" align="center" ><font color="#003399"><b><?php
echo gettext("Calls");
?>
</b></font></td>
<td align="right"><font color="#003399"><b><?php
echo gettext("Amount") . " (" . BASE_CURRENCY . ")";
?>
</b></font></td>
</tr>
<?php
$i = 0;
foreach ($list_total_destination as $data) {
$i = ($i + 1) % 2;
$tmc = $data[1] / $data[3];
if (!isset($resulttype) || $resulttype == "min") {
$tmc = sprintf("%02d", intval($tmc / 60)) . ":" . sprintf("%02d", intval($tmc % 60));
} else {
$tmc = intval($tmc);
}
if (!isset($resulttype) || $resulttype == "min") {
$minutes = sprintf("%02d", intval($data[1] / 60)) . ":" . sprintf("%02d", intval($data[1] % 60));
} else {
$minutes = $data[1];
}
if ($mmax > 0) {
$widthbar = intval($data[1] / $mmax * 200);
}
?>
<tr class="invoice_rows">
<td width="29%" ><font color="#003399"><?php
echo $data[0];
?>
</font></td>
<td width="38%" ><font color="#003399"><?php
echo $minutes;
?>
</font></td>
<td width="12%" align="right" ><font color="#003399"><?php
echo $data[3];
?>
</font> </td>
<td align="right" ><font color="#003399"><?php
display_2bill($data[2]);
?>
</font></td>
</tr>
<?php
}
if (!isset($resulttype) || $resulttype == "min") {
$total_tmc = sprintf("%02d", intval($totalminutes / $totalcall / 60)) . ":" . sprintf("%02d", intval($totalminutes / $totalcall % 60));
$totalminutes = sprintf("%02d", intval($totalminutes / 60)) . ":" . sprintf("%02d", intval($totalminutes % 60));
} else {
$total_tmc = intval($totalminutes / $totalcall);
}
?>
<tr >
<td width="29%" > </td>
<td width="38%" > </td>
<td width="12%" > </td>
<td > </td>
</tr>
<tr bgcolor="#CCCCCC">
<td width="29%" ><font color="#003399"><?php
echo gettext("TOTAL");
?>
</font></td>
<td width="38%" ><font color="#003399"><?php
echo $totalminutes;
?>
</font></td>
<td width="12%" align="right"><font color="#003399"><?php
echo $totalcall;
?>
</font></td>
<td align="right" ><font color="#003399"><?php
display_2bill($totalcost - $totalcost_did);
?>
</font> </td>
</tr>
<tr >
<td width="29%"> </td>
<td width="38%"> </td>
<td width="12%"> </td>
<td > </td>
</tr>
<?php
}
?>
</table>
<table align="center" width="80%">
<!-- Start Here ****************************************-->
<?php
$mmax = 0;
$totalcall = 0;
$totalminutes = 0;
$totalcost_day = 0;
if (is_array($list_total_day) && count($list_total_day) > 0) {
foreach ($list_total_day as $data) {
if ($mmax < $data[1]) {
$mmax = $data[1];
}
$totalcall += $data[3];
$totalminutes += $data[1];
$totalcost_day += $data[2];
}
?>
<tr>
<td colspan="4" align="center"><b><?php
echo gettext("By Date");
?>
</b> </td>
</tr>
<tr bgcolor="#CCCCCC">
<td width="29%"><font color="#003399"><b><?php
echo gettext("Date");
?>
</b> </font></td>
<td width="38%" ><font color="#003399"><b><?php
echo gettext("Duration");
?>
</b> </font></td>
<td width="12%" align="center" ><font color="#003399"><b><?php
echo gettext("Calls");
?>
</b> </font></td>
<td width="21%" align="right"><font color="#003399"><b><?php
echo gettext("Amount") . " (" . BASE_CURRENCY . ")";
?>
</b> </font></td>
</tr>
<?php
$i = 0;
foreach ($list_total_day as $data) {
$i = ($i + 1) % 2;
$tmc = $data[1] / $data[3];
if (!isset($resulttype) || $resulttype == "min") {
$tmc = sprintf("%02d", intval($tmc / 60)) . ":" . sprintf("%02d", intval($tmc % 60));
} else {
$tmc = intval($tmc);
}
if (!isset($resulttype) || $resulttype == "min") {
$minutes = sprintf("%02d", intval($data[1] / 60)) . ":" . sprintf("%02d", intval($data[1] % 60));
} else {
$minutes = $data[1];
}
if ($mmax > 0) {
$widthbar = intval($data[1] / $mmax * 200);
}
?>
<tr class="invoice_rows">
<td width="29%" ><font color="#003399"><?php
echo $data[0];
?>
</font></td>
<td width="38%" ><font color="#003399"><?php
echo $minutes;
?>
</font></td>
<td width="12%" align="right"><font color="#003399"><?php
echo $data[3];
?>
</font></td>
<td width="21%" align="right" ><font color="#003399"><?php
display_2bill($data[2]);
?>
</font></td>
</tr>
<?php
}
if (!isset($resulttype) || $resulttype == "min") {
$total_tmc = sprintf("%02d", intval($totalminutes / $totalcall / 60)) . ":" . sprintf("%02d", intval($totalminutes / $totalcall % 60));
$totalminutes = sprintf("%02d", intval($totalminutes / 60)) . ":" . sprintf("%02d", intval($totalminutes % 60));
} else {
$total_tmc = intval($totalminutes / $totalcall);
}
?>
<tr >
<td width="29%" > </td>
<td width="38%" > </td>
<td width="12%" > </td>
<td width="21%" > </td>
</tr>
<tr bgcolor="#CCCCCC">
<td width="29%" ><font color="#003399"><?php
echo gettext("TOTAL");
?>
</font></td>
<td width="38%" ><font color="#003399"><?php
echo $totalminutes;
?>
</font></td>
<td width="12%" align="right" ><font color="#003399"><?php
echo $totalcall;
?>
</font> </td>
<td width="21%" align="right" ><font color="#003399"><?php
display_2bill($totalcost_day);
?>
</font></td>
</tr>
<tr >
<td width="29%"> </td>
<td width="38%"> </td>
<td width="12%"> </td>
<td width="21%"> </td>
</tr>
<?php
}
?>
<!-- END HERE ******************************************-->
</table>
<table align="center" width="80%">
<?php
if (is_array($list_total_did) && count($list_total_did) > 0) {
?>
<tr>
<td>
<table width="100%" align="left" cellpadding="0" cellspacing="0">
<tr>
<td colspan="5" align="center"><font><b><?php
echo gettext("DID Billing");
?>
</b></font> </td>
</tr>
<tr bgcolor="#CCCCCC">
<td width="20%"> <font color="#003399"><b><?php
echo gettext("Charge Date");
?>
</b></font></td>
<td width="13%" ><font color="#003399"><b><?php
echo gettext("DID");
?>
</b></font></td>
<td width="14%" ><font color="#003399"><b><?php
echo gettext("Country");
?>
</b></font> </td>
<td width="41%" ><font color="#003399"><b><?php
echo gettext("Description");
?>
</b></font></td>
<td width="12%" align="right"><font color="#003399"><b><?php
echo gettext("Amount") . " (" . BASE_CURRENCY . ")";
?>
</b></font> </td>
</tr>
<?php
$i = 0;
$totaldidcost = 0;
foreach ($list_total_did as $data) {
$totaldidcost = $totaldidcost + convert_currency($currencies_list, $data[0], $data[5], BASE_CURRENCY);
?>
<tr class="invoice_rows">
<td width="20%" ><font color="#003399"><?php
echo $data[1];
?>
</font></td>
<td width="13%" ><font color="#003399"> <?php
echo $data[4];
?>
</font> </td>
<td width="14%" ><font color="#003399"> <?php
echo $data[3];
?>
</font></td>
<td width="41%" ><font color="#003399"><?php
echo $data[2];
?>
</font> </td>
<td width="12%" align="right" ><font color="#003399"><?php
convert_currency($currencies_list, $data[0], $data[5], BASE_CURRENCY) . " " . BASE_CURRENCY;
?>
</font></td>
</tr>
<?php
}
$totalcost = $totalcost + $totaldidcost;
?>
<tr >
<td width="20%" > </td>
<td width="13%" > </td>
<td width="14%" > </td>
<td width="41%" > </td>
<td width="12%" > </td>
</tr>
<tr bgcolor="#CCCCCC" >
<td width="20%" ><font color="#003399"><?php
echo gettext("TOTAL");
?>
</font></td>
<td ><font color="#003399"> </font></td>
<td width="14%" ><font color="#003399"> </font> </td>
<td width="41%" ><font color="#003399"> </font> </td>
<td width="12%" align="right" ><font color="#003399"><?php
display_2bill($totaldidcost);
?>
</font> </td>
</tr>
<tr>
<td width="20%"> </td>
<td width="13%"> </td>
<td width="14%"> </td>
<td width="41%"> </td>
<td width="12%"> </td>
</tr>
</table>
</td>
</tr>
<?php
}
?>
<tr>
<td>
<!-------------------------EXTRA CHARGE START HERE ---------------------------------->
<?php
$i = 0;
$extracharge_total = 0;
if (is_array($list_total_charges) && count($list_total_charges) > 0) {
?>
<table width="100%" align="left" cellpadding="0" cellspacing="0">
<tr>
<td colspan="4" align="center"><font><b><?php
echo gettext("Extra Charges");
?>
</b></font> </td>
</tr>
<tr bgcolor="#CCCCCC">
<td width="20%"> <font color="#003399"><b><?php
echo gettext("Date");
?>
</b></font></td>
<td width="19%" ><font color="#003399"><b><?php
echo gettext("Type");
?>
</b></font></td>
<td width="43%" ><font color="#003399"><b><?php
echo gettext("Description");
?>
</b></font> </td>
<td width="18%" align="right"><font color="#003399"><b><?php
echo gettext("Amount") . " (" . BASE_CURRENCY . ")";
?>
</b></font> </td>
</tr>
<?php
foreach ($list_total_charges as $data) {
$extracharge_total = $extracharge_total + convert_currency($currencies_list, $data[3], $data[6], BASE_CURRENCY);
?>
<tr class="invoice_rows">
<td width="20%" ><font color="#003399"><?php
echo $data[2];
?>
</font></td>
<td width="19%" ><font color="#003399">
<?php
if ($data[4] == 1) {
echo gettext("Setup Charges");
}
if ($data[4] == 2) {
echo gettext("DID Montly Use");
}
if ($data[4] == 3) {
echo gettext("Subscription Fee");
}
if ($data[4] == 4) {
echo gettext("Extra Charges");
}
?>
</font> </td>
<td width="43%" ><font color="#003399"><?php
echo $data[7];
?>
</font></td>
<td width="18%" align="right" ><font color="#003399"><?php
echo convert_currency($currencies_list, $data[3], $data[6], BASE_CURRENCY) . " " . BASE_CURRENCY;
?>
</font></td>
</tr>
<?php
}
//for loop end here
?>
<tr >
<td width="20%" > </td>
<td width="19%" > </td>
<td width="43%" > </td>
<td width="18%" > </td>
</tr>
<tr bgcolor="#CCCCCC" >
<td width="20%" ><font color="#003399"><?php
echo gettext("TOTAL");
?>
</font></td>
<td ><font color="#003399"> </font></td>
<td width="43%" ><font color="#003399"> </font> </td>
<td width="18%" align="right" ><font color="#003399"><?php
echo display_2bill($extracharge_total);
?>
</font> </td>
</tr>
<tr >
<td width="20%"> </td>
<td width="19%"> </td>
<td width="43%"> </td>
<td width="18%"> </td>
</tr>
</table>
<?php
}
//if check end here
$totalcost = $totalcost + $extracharge_total;
?>
<!-----------------------------EXTRA CHARGE END HERE ------------------------------->
</td>
</tr>
<tr>
<td><img src="<?php
echo Images_Path;
?>
/spacer.jpg" align="middle" height="30px"></td>
</tr>
<tr bgcolor="#CCCCCC" >
<td align="right" width="100%"><font color="#003399"><b><?php
echo gettext("Total");
?>
= <?php
display_2bill($totalcost);
?>
</b></font></td>
</tr>
<tr bgcolor="#CCCCCC" >
<td align="right" width="100%"><font color="#003399"><b><?php
echo gettext("VAT");
?>
= <?php
$prvat = $vat / 100 * $totalcost;
display_2bill($prvat);
?>
</b></font></td>
</tr>
<tr>
<td><img src="<?php
echo Images_Path;
?>
/spacer.jpg" align="middle" height="30px"></td>
</tr>
<tr bgcolor="#CCCCCC" >
<td align="right" width="100%"><font color="#003399"><b><?php
echo gettext("Grand Total");
?>
= <?php
echo display_2bill($totalcost + $prvat);
?>
</b></font></td>
</tr>
<tr>
<td><img src="<?php
echo Images_Path;
?>
/spacer.jpg" align="middle" height="30px"></td>
</tr>
</table>
<table cellspacing="0" cellpadding="2" width="80%" align="center">
<tr>
<td colspan="3"> </td>
</tr>
<tr>
<td align="left">Status : <?php
if ($info_customer[0][12] == 't') {
?>
<img src="<?php
echo Images_Path;
?>
/connected.jpg">
<?php
} else {
?>
<img src="<?php
echo Images_Path;
?>
/terminated.jpg">
<?php
}
?>
</td>
</tr>
<tr>
<td align="left"> <img src="<?php
echo Images_Path;
?>
/connected.jpg"> <?php
echo gettext("Connected");
?>
<img src="<?php
echo Images_Path;
?>
/terminated.jpg"> <?php
echo gettext("Disconnected");
?>
</td>
</table>
<?php
$html = ob_get_contents();
// delete output-Buffer
ob_end_clean();
$pdf = new HTML2FPDF();
$pdf->DisplayPreferences('HideWindowUI');
$pdf->AddPage();
$pdf->WriteHTML($html);
$stream = $pdf->Output('UnBilledDetails_' . date("d/m/Y-H:i") . '.pdf', 'S');
//================================Email Template Retrival Code ===================================
$QUERY = "SELECT mailtype, fromemail, fromname, subject, messagetext, messagehtml FROM cc_templatemail WHERE mailtype='invoice' ";
$res = $DBHandle->Execute($QUERY);
$num = 0;
if ($res) {
$num = $res->RecordCount();
}
if (!$num) {
echo "<br>Error : No email Template Found";
exit;
}
for ($i = 0; $i < $num; $i++) {
$listtemplate[] = $res->fetchRow();
}
list($mailtype, $from, $fromname, $subject, $messagetext, $messagehtml) = $listtemplate[0];
if ($FG_DEBUG == 1) {
echo "<br><b>mailtype : </b>{$mailtype}</br><b>from:</b> {$from}</br><b>fromname :</b> {$fromname}</br><b>subject</b> : {$subject}</br><b>ContentTemplate:</b></br><pre>{$messagetext}</pre></br><hr>";
}
//================================================================================================
$ok = send_email_attachment($from, $info_customer[0][10], $subject, $messagetext, 'UnBilledDetails_' . date("d/m/Y-H:i") . '.pdf', $stream);
return $ok;
}
}
$FG_TABLE_CLAUSE_DID = $FG_TABLE_CLAUSE . " AND t1.sipiax in (2,3)";
if (!$nodisplay) {
$list_did = $instance_table->Get_list($DBHandle, $FG_TABLE_CLAUSE_DID, $order, $sens, null, null, $FG_LIMITE_DISPLAY, $current_page * $FG_LIMITE_DISPLAY);
}
$_SESSION["pr_sql_export"] = "SELECT {$FG_COL_QUERY} FROM {$FG_TABLE_NAME} WHERE {$FG_TABLE_CLAUSE}";
/************************/
$QUERY = "SELECT substring(t1.starttime,1,10) AS day, sum(t1.sessiontime) AS calltime, sum(t1.sessionbill) AS cost, count(*) as nbcall FROM {$FG_TABLE_NAME} WHERE " . $FG_TABLE_CLAUSE . " GROUP BY substring(t1.starttime,1,10) ORDER BY day";
//extract(DAY from calldate)
//echo "$QUERY";
if (!$nodisplay) {
$list_total_day = $instance_table->SQLExec($DBHandle, $QUERY);
if ($FG_DEBUG == 3) {
echo "<br>Clause : {$FG_TABLE_CLAUSE}";
}
$nb_record = $instance_table->Table_count($DBHandle, $FG_TABLE_CLAUSE_NORMAL);
$nb_record_did = $instance_table->Table_count($DBHandle, $FG_TABLE_CLAUSE_DID);
if ($FG_DEBUG >= 1) {
var_dump($list);
}
}
//end IF nodisplay
// GROUP BY DESTINATION FOR THE INVOICE
$QUERY = "SELECT destination, sum(t1.sessiontime) AS calltime, \nsum(t1.sessionbill) AS cost, count(*) as nbcall FROM {$FG_TABLE_NAME} WHERE " . $FG_TABLE_CLAUSE . " GROUP BY destination";
if (!$nodisplay) {
$list_total_destination = $instance_table->SQLExec($DBHandle, $QUERY);
}
//end IF nodisplay
if ($nb_record <= $FG_LIMITE_DISPLAY) {
$nb_record_max = 1;
} else {
