}
if ($res_row[0] == 4) {
$result_count_cancelled = $res_row[1];
}
if ($res_row[0] == 5) {
$result_count_congested = $res_row[1];
}
if ($res_row[0] == 6) {
$result_count_chanunavail = $res_row[1];
}
$result_count_all = $result_count_all + $res_row[1];
}
$result = $table->SQLExec($DBHandle, $QUERY_COUNT_CALL_BILL);
$result_count_calls_times = $result[0][0];
$result_count_calls_sell = a2b_round($result[0][1]);
$result_count_calls_buy = a2b_round($result[0][2]);
$result_count_calls_profit = $result_count_calls_sell - $result_count_calls_buy;
?>
<?php
echo gettext("Total Calls");
?>
: <font style="color:#EE6564;" > <?php
echo $result_count_all;
?>
</font> <br/>
:: <?php
echo gettext("Answered");
?>
: <?php
echo $result_count_answered;
public function rate_engine_updatesystem(&$A2B, &$agi, $calledstation, $doibill = 1, $didcall = 0, $callback = 0)
{
$K = $this->usedratecard;
// **************** PACKAGE PARAMETERS ****************
$id_cc_package_offer = $this->ratecard_obj[$K][45];
$additional_grace_time = $this->ratecard_obj[$K][58];
$id_card_package_offer = null;
if ($A2B->CC_TESTING) {
$sessiontime = 120;
$dialstatus = 'ANSWER';
} else {
$sessiontime = $this->answeredtime;
$dialstatus = $this->dialstatus;
}
// add grace time if the call is Answered
if ($this->dialstatus == "ANSWER" && $additional_grace_time > 0) {
$sessiontime = $sessiontime + $additional_grace_time;
}
$A2B->debug(INFO, $agi, __FILE__, __LINE__, ":[sessiontime:{$sessiontime} - id_cc_package_offer:{$id_cc_package_offer} - package2apply:" . $this->package_to_apply[$K] . "]\n\n");
if ($sessiontime > 0) {
// HANDLE FREETIME BEFORE CALCULATE THE COST
$this->freetimetocall_used = 0;
if ($this->debug_st) {
print_r($this->freetimetocall_left[$K]);
}
if ($id_cc_package_offer != -1 && $this->package_to_apply[$K] != null) {
$id_package_offer = $this->package_to_apply[$K]["id"];
switch ($this->package_to_apply[$K]["type"]) {
//Unlimited
case 0:
$this->freetimetocall_used = $sessiontime;
break;
//free calls
//free calls
case 1:
$this->freetimetocall_used = $sessiontime;
break;
//free minutes
//free minutes
case 2:
if ($this->freetimetocall_left[$K] >= $sessiontime) {
$this->freetimetocall_used = $sessiontime;
} else {
$this->freetimetocall_used = $this->freetimetocall_left[$K];
}
break;
}
$this->rate_engine_calculcost($A2B, $sessiontime, 0);
$QUERY_FIELS = 'id_cc_card, id_cc_package_offer, used_secondes';
$QUERY_VALUES = "'" . $A2B->id_card . "', '{$id_package_offer}', '{$this->freetimetocall_used}'";
$id_card_package_offer = $A2B->instance_table->Add_table($A2B->DBHandle, $QUERY_VALUES, $QUERY_FIELS, 'cc_card_package_offer', 'id');
$A2B->debug(INFO, $agi, __FILE__, __LINE__, ":[ID_CARD_PACKAGE_OFFER CREATED : {$id_card_package_offer}]:[{$QUERY_VALUES}]\n\n\n\n");
} else {
$this->rate_engine_calculcost($A2B, $sessiontime, 0);
// rate_engine_calculcost could have change the duration of the call
$sessiontime = $this->answeredtime;
if ($sessiontime > 0 && $additional_grace_time > 0) {
$sessiontime = $sessiontime + $additional_grace_time;
}
}
} else {
$sessiontime = 0;
}
$calldestination = $this->ratecard_obj[$K][5];
$id_tariffgroup = $this->ratecard_obj[$K][2];
$id_tariffplan = $this->ratecard_obj[$K][3];
$id_ratecard = $this->ratecard_obj[$K][6];
$buycost = 0;
if ($doibill == 0 || $sessiontime < $A2B->agiconfig['min_duration_2bill']) {
$cost = 0;
$buycost = abs($this->lastbuycost);
} else {
$cost = $this->lastcost;
$buycost = abs($this->lastbuycost);
}
if ($cost < 0) {
$signe = '-';
$signe_cc_call = '+';
} else {
$signe = '+';
$signe_cc_call = '-';
}
$buyrateapply = $this->ratecard_obj[$K][9];
$rateapply = $this->ratecard_obj[$K][12];
$A2B->debug(DEBUG, $agi, __FILE__, __LINE__, "[CC_RATE_ENGINE_UPDATESYSTEM: usedratecard K={$K} - (sessiontime={$sessiontime} :: dialstatus={$dialstatus} :: buycost={$buycost} :: cost={$cost} : signe_cc_call={$signe_cc_call}: signe={$signe})]");
if (strlen($this->dialstatus_rev_list[$dialstatus]) > 0) {
$terminatecauseid = $this->dialstatus_rev_list[$dialstatus];
} else {
$terminatecauseid = 0;
}
// CALLTYPE - 0 = NORMAL CALL ; 1 = VOIP CALL (SIP/IAX) ; 2= DIDCALL + TRUNK ; 3 = VOIP CALL DID ; 4 = CALLBACK call
if ($didcall) {
$calltype = 2;
} elseif ($callback) {
$calltype = 4;
$terminatecauseid = 1;
} else {
$calltype = 0;
}
$card_id = !is_numeric($A2B->id_card) ? '-1' : "'" . $A2B->id_card . "'";
$real_sessiontime = !is_numeric($this->real_answeredtime) ? 'NULL' : "'" . $this->real_answeredtime . "'";
$id_tariffgroup = !is_numeric($id_tariffgroup) ? 'NULL' : "'{$id_tariffgroup}'";
$id_tariffplan = !is_numeric($id_tariffplan) ? 'NULL' : "'{$id_tariffplan}'";
$id_ratecard = !is_numeric($id_ratecard) ? 'NULL' : "'{$id_ratecard}'";
$trunk_id = !is_numeric($this->usedtrunk) ? 'NULL' : "'" . $this->usedtrunk . "'";
$id_card_package_offer = !is_numeric($id_card_package_offer) ? 'NULL' : "'{$id_card_package_offer}'";
$calldestination = !is_numeric($calldestination) ? 'DEFAULT' : "'{$calldestination}'";
$QUERY_COLUMN = "uniqueid, sessionid, card_id, nasipaddress, starttime, sessiontime, real_sessiontime, calledstation, terminatecauseid, stoptime, sessionbill, id_tariffgroup, id_tariffplan, id_ratecard, id_trunk, src, sipiax, buycost, id_card_package_offer, dnid, destination";
$QUERY = "INSERT INTO cc_call ({$QUERY_COLUMN} {$A2B->CDR_CUSTOM_SQL}) VALUES ('" . $A2B->uniqueid . "', '" . $A2B->channel . "', " . "{$card_id}, '" . $A2B->hostname . "', ";
if ($A2B->config["global"]['cache_enabled']) {
$QUERY .= " datetime(strftime('%s', 'now') - {$sessiontime}, 'unixepoch', 'localtime')";
} else {
$QUERY .= "SUBDATE(CURRENT_TIMESTAMP, INTERVAL {$sessiontime} SECOND) ";
}
$QUERY .= ", '{$sessiontime}', {$real_sessiontime}, '{$calledstation}', {$terminatecauseid}, ";
if ($A2B->config["global"]['cache_enabled']) {
$QUERY .= "datetime('now', 'localtime')";
} else {
$QUERY .= "now()";
}
$QUERY .= " , '{$signe_cc_call}" . a2b_round(abs($cost)) . "', " . " {$id_tariffgroup}, {$id_tariffplan}, {$id_ratecard}, '" . $this->usedtrunk . "', '" . $A2B->CallerID . "', '{$calltype}', " . " '{$buycost}', {$id_card_package_offer}, '" . $A2B->dnid . "', {$calldestination} {$A2B->CDR_CUSTOM_VAL})";
if ($A2B->config["global"]['cache_enabled']) {
//insert query in the cache system
$create = false;
if (!file_exists($A2B->config["global"]['cache_path'])) {
$create = true;
}
if ($db = sqlite_open($A2B->config["global"]['cache_path'], 0666, $sqliteerror)) {
if ($create) {
sqlite_query($db, "CREATE TABLE cc_call ({$QUERY_COLUMN})");
}
sqlite_query($db, $QUERY);
sqlite_close($db);
} else {
$A2B->debug(ERROR, $agi, __FILE__, __LINE__, "[Error to connect to cache : {$sqliteerror}]\n");
}
} else {
$result = $A2B->instance_table->SQLExec($A2B->DBHandle, $QUERY, 0);
$A2B->debug(INFO, $agi, __FILE__, __LINE__, "[CC_asterisk_stop : SQL: DONE : result=" . $result . "]");
$A2B->debug(DEBUG, $agi, __FILE__, __LINE__, "[CC_asterisk_stop : SQL: {$QUERY}]");
}
if ($sessiontime > 0) {
if ($didcall == 0 && $callback == 0) {
$myclause_nodidcall = " , redial='" . $calledstation . "' ";
} else {
$myclause_nodidcall = '';
}
if (!defined($myclause_nodidcall)) {
$myclause_nodidcall = null;
}
//Update the global credit
$A2B->credit = $A2B->credit + $cost;
if ($A2B->nbused > 0) {
$QUERY = "UPDATE cc_card SET credit = credit{$signe}" . a2b_round(abs($cost)) . " {$myclause_nodidcall}, lastuse = now(), nbused = nbused + 1 WHERE username = '******'";
} else {
$QUERY = "UPDATE cc_card SET credit = credit{$signe}" . a2b_round(abs($cost)) . " {$myclause_nodidcall}, lastuse = now(), firstusedate = now(), nbused = nbused + 1 WHERE username = '******'";
}
$A2B->debug(DEBUG, $agi, __FILE__, __LINE__, "[CC_asterisk_stop 1.2: SQL: {$QUERY}]");
$result = $A2B->instance_table->SQLExec($A2B->DBHandle, $QUERY, 0);
$QUERY = "UPDATE cc_trunk SET secondusedreal = secondusedreal + {$sessiontime} WHERE id_trunk = '" . $this->usedtrunk . "'";
$A2B->debug(DEBUG, $agi, __FILE__, __LINE__, $QUERY);
$result = $A2B->instance_table->SQLExec($A2B->DBHandle, $QUERY, 0);
$QUERY = "UPDATE cc_tariffplan SET secondusedreal = secondusedreal + {$sessiontime} WHERE id = {$id_tariffplan}";
$A2B->debug(DEBUG, $agi, __FILE__, __LINE__, $QUERY);
$result = $A2B->instance_table->SQLExec($A2B->DBHandle, $QUERY, 0);
}
}
$update_msg ='<b><font color="green">'.gettext("Refill executed ").'!</font></b>';
$id_agent = $_SESSION['agent_id'];
$field_insert = "date, credit, card_id, description, refill_type,agent_id";
$value_insert = "now(), '$addcredit', '$id','$description','3','$id_agent'";
$instance_sub_table = new Table("cc_logrefill", $field_insert);
$id_refill = $instance_sub_table -> Add_table ($HD_Form -> DBHandle, $value_insert, null, null,'id');
$agent_table = new Table("cc_agent", "commission");
$agent_clause = "id = ".$id_agent;
$result_agent= $agent_table -> Get_list($HD_Form -> DBHandle,$agent_clause);
if (is_array($result_agent) && is_numeric($result_agent[0]['commission']) && $result_agent[0]['commission']>0) {
$field_insert = "id_payment, id_card, amount,description,id_agent";
$commission = a2b_round($addcredit * ($result_agent[0]['commission']/100));
$description_commission = gettext("GENERATED COMMISSION OF AN CUSTOMER REFILLED BY AN AGENT!");
$description_commission.= "\nID CARD : ".$id;
$description_commission.= "\nID REFILL : ".$id_refill;
$description_commission.= "\REFILL AMOUNT: ".$addcredit;
$description_commission.= "\nCOMMISSION APPLIED: ".$result_agent[0]['commission'];
$value_insert = "'-1', '$id', '$commission','$description_commission','$id_agent'";
$commission_table = new Table("cc_agent_commission", $field_insert);
$id_commission = $commission_table -> Add_table ($HD_Form -> DBHandle, $value_insert, null, null,"id");
$table_agent = new Table('cc_agent');
$param_update_agent = "com_balance = com_balance + '".$commission."'";
$clause_update_agent = " id='".$id_agent."'";
$table_agent -> Update_table ($HD_Form -> DBHandle, $param_update_agent, $clause_update_agent, $func_table = null);
}
$remittance_clause = "id_agent = " . $_SESSION['agent_id'] . " AND status = 0";
$result_remittance = $table_remittance->Get_list($DBHandle_max, $remittance_clause);
if (is_array($result_remittance) && sizeof($result_remittance) >= 1) {
$remittance_in_progress = true;
$remittance_value = $result_remittance[0]['amount'];
} else {
$remittance_in_progress = false;
}
$remittance_value_cur = $remittance_value / $mycur;
if (!$remittance_in_progress) {
$err_msg = '';
if ($two_currency) {
$amount_gobal_cur = a2b_round($amount * $mycur);
$amount_rounded = a2b_round($amount_gobal_cur / $mycur);
} else {
$amount_rounded = $amount_gobal_cur = a2b_round($amount);
}
if ($amount_rounded < $threshold_cur) {
$err_msg = gettext("Invalid amount, is higher than the threshold to authorize a remittance");
}
if ($amount_rounded > $commision_bal_cur) {
$err_msg = gettext("Invalid amount, is higher than your commission Accrued");
}
if ($action == "add" && empty($err_msg)) {
if ($remittance_type == "BANK") {
$type = 1;
} else {
$type = 0;
}
$table_remittance = new Table("cc_remittance_request");
$fields = "id_agent,amount,type";
/**
* Function create_refill
* @public
*/
static public function create_refill_after_payment()
{
global $A2B;
$FormHandler = FormHandler::GetInstance();
$processed = $FormHandler->getProcessed();
if ($processed['added_refill']==1) {
$id_payment = $FormHandler -> RESULT_QUERY;
// CREATE REFILL
$field_insert = "date, credit, card_id ,refill_type, description";
$date = $processed['date'];
$credit = $processed['payment'];
$card_id = $processed['card_id'];
$refill_type= $processed['payment_type'];
$description = $processed['description'];
$card_table = new Table('cc_card','vat');
$card_clause = "id = ".$card_id;
$card_result = $card_table -> Get_list($FormHandler->DBHandle, $card_clause, 0);
if(!is_array($card_result)||empty($card_result[0][0])||!is_numeric($card_result[0][0]))
$vat=0;
else
$vat = $card_result[0][0];
$credit_without_vat = $credit / (1+$vat/100);
$value_insert = " '$date' , '$credit_without_vat', '$card_id','$refill_type', '$description' ";
$instance_sub_table = new Table("cc_logrefill", $field_insert);
$id_refill = $instance_sub_table -> Add_table ($FormHandler->DBHandle, $value_insert, null, null,"id");
// REFILL CARD .. UPADTE CARD
$instance_table_card = new Table("cc_card");
$param_update_card = "credit = credit + '".$credit_without_vat."'";
$clause_update_card = " id='$card_id'";
$instance_table_card -> Update_table ($FormHandler->DBHandle, $param_update_card, $clause_update_card, $func_table = null);
//LINK THE REFILL TO THE PAYMENT .. UPADTE PAYMENT
$instance_table_pay = new Table("cc_logpayment");
$param_update_pay = "id_logrefill = '".$id_refill."'";
$clause_update_pay = " id ='$id_payment'";
$instance_table_pay-> Update_table ($FormHandler->DBHandle, $param_update_pay, $clause_update_pay, $func_table = null);
// Create invoice associated
// CREATE AND UPDATE REF NUMBER
$list_refill_type=Constants::getRefillType_List();
$refill_type = $processed['payment_type'];
$year = date("Y");
$invoice_conf_table = new Table('cc_invoice_conf','value');
$conf_clause = "key_val = 'count_$year'";
$result = $invoice_conf_table -> Get_list($FormHandler->DBHandle, $conf_clause, 0);
if (is_array($result) && !empty($result[0][0])) {
// update count
$count =$result[0][0];
if(!is_numeric($count)) $count=0;
$count++;
$param_update_conf = "value ='".$count."'";
$clause_update_conf = "key_val = 'count_$year'";
$invoice_conf_table -> Update_table ($FormHandler->DBHandle, $param_update_conf, $clause_update_conf, $func_table = null);
} else {
// insert newcount
$count=1;
$QUERY= "INSERT INTO cc_invoice_conf (key_val ,value) VALUES ( 'count_$year', '1');";
$invoice_conf_table -> SQLExec($FormHandler->DBHandle,$QUERY);
}
$field_insert = "date, id_card, title ,reference, description,status,paid_status";
if($refill_type!=0) {
$title = $list_refill_type[$refill_type][0]." ".gettext("REFILL");
} else {
$title = gettext("REFILL");
}
$description = gettext("Invoice for refill");
$reference = $year.sprintf("%08d",$count);
$value_insert = " '$date' , '$card_id', '$title','$reference','$description','1','1' ";
$instance_table = new Table("cc_invoice", $field_insert);
$id_invoice = $instance_table -> Add_table ($FormHandler->DBHandle, $value_insert, null, null,"id");
//add payment to this invoice
$field_insert = "id_invoice, id_payment";
$value_insert = "'$id_invoice' , '$id_payment'";
$instance_table = new Table("cc_invoice_payment", $field_insert);
$instance_table -> Add_table ($FormHandler->DBHandle, $value_insert, null, null);
//load vat of this card
if (!empty($id_invoice) && is_numeric($id_invoice)) {
$description = $processed['description'];
$field_insert = "date, id_invoice ,price,vat, description";
$instance_table = new Table("cc_invoice_item", $field_insert);
$value_insert = " '$date' , '$id_invoice', '$credit_without_vat','$vat','$description' ";
$instance_table -> Add_table ($FormHandler->DBHandle, $value_insert, null, null,"id");
}
}
if($processed['added_commission']==1) {
$card_id = $processed['card_id'];
$table_transaction = new Table();
$result_agent = $table_transaction -> SQLExec($FormHandler->DBHandle,"SELECT cc_card_group.id_agent FROM cc_card LEFT JOIN cc_card_group ON cc_card_group.id = cc_card.id_group WHERE cc_card.id = $card_id");
if (is_array($result_agent)&& !is_null($result_agent[0]['id_agent']) && $result_agent[0]['id_agent']>0 ) {
// test if the agent exist and get its commission
$id_agent = $result_agent[0]['id_agent'];
// update refill & payment to keep a trace of agent in the timeline
$table_refill = new Table("cc_logrefill");
$table_payment = new Table("cc_logpayment");
$param_update = "agent_id = '".$id_agent."'";
if(!empty($id_refill)){
$clause_update_refill_agent = " id ='$id_refill'";
$table_refill-> Update_table ($FormHandler->DBHandle, $param_update, $clause_update_refill_agent, $func_table = null);
}
$clause_update_payment_agent = " id ='$id_payment'";
$table_payment-> Update_table ($FormHandler->DBHandle, $param_update, $clause_update_payment_agent, $func_table = null);
$agent_table = new Table("cc_agent", "commission");
$agent_clause = "id = ".$id_agent;
$result_agent= $agent_table -> Get_list($FormHandler->DBHandle,$agent_clause);
if (is_array($result_agent) && is_numeric($result_agent[0]['commission']) && $result_agent[0]['commission']>0) {
$field_insert = "id_payment, id_card, amount,description,id_agent";
$commission = a2b_round($processed['payment'] * ($result_agent[0]['commission']/100));
$description_commission = gettext("AUTOMATICALY GENERATED COMMISSION!");
$description_commission.= "\nID CARD : ".$card_id;
$description_commission.= "\nID PAYMENT : ".$id_payment;
$description_commission.= "\nPAYMENT AMOUNT: ".$amount_paid;
$description_commission.= "\nCOMMISSION APPLIED: ".$result_agent[0]['commission'];
$value_insert = "'".$id_payment."', '$card_id', '$commission','$description_commission','$id_agent'";
$commission_table = new Table("cc_agent_commission", $field_insert);
$id_commission = $commission_table -> Add_table ($FormHandler->DBHandle, $value_insert, null, null,"id");
$table_agent = new Table('cc_agent');
$param_update_agent = "com_balance = com_balance + '".$commission."'";
$clause_update_agent = " id='".$id_agent."'";
$table_agent -> Update_table ($FormHandler->DBHandle, $param_update_agent, $clause_update_agent, $func_table = null);
}
}
}
}
public function update_callback_campaign($agi)
{
$now = time();
$username = $agi->get_variable("USERNAME", true);
$userid = $agi->get_variable("USERID", true);
$called = $agi->get_variable("CALLED", true);
$phonenumber_id = $agi->get_variable("PHONENUMBER_ID", true);
$campaign_id = $agi->get_variable("CAMPAIGN_ID", true);
$this->debug(DEBUG, $agi, __FILE__, __LINE__, "[MODE CAMPAIGN CALLBACK: USERNAME={$username} USERID={$userid} ]");
$query_rate = "SELECT cc_campaign_config.flatrate, cc_campaign_config.context FROM cc_card,cc_card_group,cc_campaignconf_cardgroup,cc_campaign_config , cc_campaign WHERE cc_card.id = {$userid} AND cc_card.id_group = cc_card_group.id AND cc_campaignconf_cardgroup.id_card_group = cc_card_group.id AND cc_campaignconf_cardgroup.id_campaign_config = cc_campaign_config.id AND cc_campaign.id = {$campaign_id} AND cc_campaign.id_campaign_config = cc_campaign_config.id";
$this->debug(DEBUG, $agi, __FILE__, __LINE__, "[QUERY SEARCH CAMPAIGN CONFIG : " . $query_rate);
$result_rate = $this->instance_table->SQLExec($this->DBHandle, $query_rate);
$cost = 0;
if ($result_rate) {
$cost = $result_rate[0][0];
$context = $result_rate[0][1];
}
if (empty($context)) {
$context = $this->config["callback"]['context_campaign_callback'];
}
if ($cost > 0) {
$signe = '-';
} else {
$signe = '+';
}
//update balance
$QUERY = "UPDATE cc_card SET credit= credit {$signe} " . a2b_round(abs($cost)) . ", lastuse = now() WHERE username = '******'";
$this->debug(DEBUG, $agi, __FILE__, __LINE__, "[UPDATE CARD : " . $QUERY);
$this->instance_table->SQLExec($this->DBHandle, $QUERY);
//dial other context
$agi->set_variable('CALLERID(name)', $phonenumber_id . ',' . $campaign_id);
$this->debug(DEBUG, $agi, __FILE__, __LINE__, "[CONTEXT TO CALL : " . $context . "]");
$agi->exec_dial("local", "1@" . $context);
$duration = time() - $now;
///create campaign cdr
$QUERY_CALL = "INSERT INTO cc_call (uniqueid, sessionid, card_id, calledstation, sipiax, sessionbill, sessiontime, stoptime, starttime {$this->CDR_CUSTOM_SQL}) VALUES ('" . $this->uniqueid . "', '" . $this->channel . "', '" . $userid . "','" . $called . "',6, " . $cost . ", " . $duration . " , CURRENT_TIMESTAMP , DATE_SUB(CURRENT_TIMESTAMP, INTERVAL {$duration} SECOND) {$this->CDR_CUSTOM_VAL})";
$this->debug(DEBUG, $agi, __FILE__, __LINE__, "[INSERT CAMPAIGN CALL : " . $QUERY_CALL);
$this->instance_table->SQLExec($this->DBHandle, $QUERY_CALL);
}
function rate_engine_updatesystem (&$A2B, &$agi, $calledstation, $doibill = 1, $didcall=0, $callback=0, $trunk_id=0, $td=NULL)
{
$K = $this->usedratecard;
// **************** PACKAGE PARAMETERS ****************
if (count($this -> ratecard_obj)>0) {
$id_cc_package_offer = $this -> ratecard_obj[$K][45];
$additional_grace_time = $this -> ratecard_obj[$K][58];
} else $id_cc_package_offer = 'NONE';
$id_card_package_offer = null;
if ($A2B -> CC_TESTING) {
$sessiontime = 120;
$dialstatus = 'ANSWERED';
} else {
$sessiontime = $this -> answeredtime;
$dialstatus = $this -> dialstatus;
}
$A2B -> debug( INFO, $agi, __FILE__, __LINE__, ":[sessiontime:$sessiontime - id_cc_package_offer:$id_cc_package_offer - package2apply:".$this ->package_to_apply[$K]."]\n\n");
if ($sessiontime > 0) {
// HANDLE FREETIME BEFORE CALCULATE THE COST
$this -> freetimetocall_used = 0;
if ($this -> debug_st) print_r($this -> freetimetocall_left[$K]);
if (($id_cc_package_offer!=-1) && ($this ->package_to_apply[$K] !=null )) {
$id_package_offer = $this ->package_to_apply[$K]["id"];
switch ($this -> package_to_apply[$K]["type"]) {
//Unlimited
case 0 :
$this->freetimetocall_used = $sessiontime;
break;
//free calls
case 1 :
$this->freetimetocall_used = $sessiontime;
break;
//free minutes
case 2 :
if ($this -> freetimetocall_left[$K] >= $sessiontime) {
$this->freetimetocall_used = $sessiontime;
} else {
$this->freetimetocall_used = $this -> freetimetocall_left[$K];
}
break;
}
$this -> rate_engine_calculcost ($A2B, $sessiontime, 0);
// rate_engine_calculcost could have change the duration of the call
$sessiontime = $this -> answeredtime;
// add grace time
if ($sessiontime>0 && $additional_grace_time>0) {
$sessiontime = $sessiontime + $additional_grace_time;
}
$QUERY_FIELS = 'id_cc_card, id_cc_package_offer, used_secondes';
$QUERY_VALUES = "'".$A2B -> id_card."', '$id_package_offer', '$this->freetimetocall_used'";
$id_card_package_offer = $A2B -> instance_table -> Add_table ($A2B -> DBHandle, $QUERY_VALUES, $QUERY_FIELS, 'cc_card_package_offer', 'id');
$A2B -> debug( INFO, $agi, __FILE__, __LINE__, ":[ID_CARD_PACKAGE_OFFER CREATED : $id_card_package_offer]:[$QUERY_VALUES]\n\n\n\n");
} else {
$this -> rate_engine_calculcost ($A2B, $sessiontime, 0);
// rate_engine_calculcost could have change the duration of the call
$sessiontime = $this -> answeredtime;
if ($sessiontime > 0 && $additional_grace_time > 0) {
$sessiontime = $sessiontime + $additional_grace_time;
}
}
} else {
$sessiontime = 0;
}
if (count($this -> ratecard_obj)>0) {
$calldestination = $this -> ratecard_obj[$K][5];
$id_tariffgroup = $this -> ratecard_obj[$K][2];
$id_tariffplan = $this -> ratecard_obj[$K][3];
$id_ratecard = $this -> ratecard_obj[$K][6];
$buyrateapply = $this -> ratecard_obj[$K][9];
$rateapply = $this -> ratecard_obj[$K][12];
}
$buycost = 0;
if ($doibill==0 || $sessiontime < $A2B->agiconfig['min_duration_2bill']) {
$cost = 0;
$buycost = abs($this -> lastbuycost);
} else {
$cost = $this -> lastcost;
$buycost = abs($this -> lastbuycost);
}
if ($cost<0) {
$signe = '-';
$signe_cc_call = '+';
} else {
$signe = '+';
$signe_cc_call = '-';
}
$A2B -> debug( DEBUG, $agi, __FILE__, __LINE__, "[CC_RATE_ENGINE_UPDATESYSTEM: usedratecard K=$K - (sessiontime=$sessiontime :: dialstatus=$dialstatus :: buycost=$buycost :: cost=$cost : signe_cc_call=$signe_cc_call: signe=$signe)]");
if (strlen($this -> dialstatus_rev_list[$dialstatus]) > 0) {
$terminatecauseid = $this -> dialstatus_rev_list[$dialstatus];
} else {
$terminatecauseid = 0;
}
// CALLTYPE - 0 = NORMAL CALL ; 1 = VOIP CALL (SIP/IAX) ; 2= DIDCALL + TRUNK ; 3 = VOIP CALL DID ; 4 = CALLBACK call
if ($didcall == 3) {
$calltype = $didcall;
} elseif ($didcall) {
$calltype = 2;
} elseif ($callback) {
$calltype = 4;
$terminatecauseid = 1;
} elseif ($A2B->recalltime) {
$calltype = 5;
} else {
$calltype = 0;
}
$card_id = (!is_numeric($A2B->id_card)) ? '-1' : "'". $A2B->id_card ."'";
$real_sessiontime = (!is_numeric($this->real_answeredtime)) ? 'NULL' : "'". $this->real_answeredtime ."'";
$id_tariffgroup = (!isset($id_tariffgroup) || !is_numeric($id_tariffgroup)) ? 'NULL' : "'$id_tariffgroup'";
$id_tariffplan = (!isset($id_tariffplan) || !is_numeric($id_tariffplan)) ? 'NULL' : "'$id_tariffplan'";
$id_ratecard = (!isset($id_ratecard) || !is_numeric($id_ratecard)) ? 'NULL' : "'$id_ratecard'";
$trunk_id = ($trunk_id) ? "'". $trunk_id ."'" : "'". $this->usedtrunk ."'";
$id_card_package_offer = (!is_numeric($id_card_package_offer)) ? 'NULL' : "'$id_card_package_offer'";
$calldestination = (!isset($calldestination) || !is_numeric($calldestination) || ($didcall && $dialstatus != 'ANSWERED')) ? -1 : $calldestination;
$card_caller = (isset($A2B->card_caller)) ? "'$A2B->card_caller'" : "'0'";
$id_did = (!isset($A2B->id_did) || !is_numeric($A2B->id_did)) ? 'NULL' : "'$A2B->id_did'";
$QUERY_COLUMN = "uniqueid, sessionid, card_id, card_caller, nasipaddress, starttime, sessiontime, real_sessiontime, calledstation, ".
" terminatecauseid, stoptime, sessionbill, id_tariffgroup, id_tariffplan, id_ratecard, " .
" id_trunk, src, sipiax, buycost, id_card_package_offer, dnid, destination, id_did";
$QUERY = "INSERT INTO cc_call ($QUERY_COLUMN) VALUES ('".$A2B->uniqueid."', '".$A2B->channel."', ".
"$card_id, $card_caller, '".$A2B->hostname."', ";
if ($A2B->config["global"]['cache_enabled']) {
$QUERY .= " datetime( strftime('%s','now') - $sessiontime, 'unixepoch','localtime')";
} else {
$QUERY .= "SUBDATE(CURRENT_TIMESTAMP, INTERVAL $sessiontime SECOND) ";
}
$QUERY .= ", '$sessiontime', $real_sessiontime, '$calledstation', $terminatecauseid, ";
if ($A2B->config["global"]['cache_enabled']) {
$QUERY .= "datetime('now','localtime')";
} else {
$QUERY .= "now()";
}
$QUERY .= " , '$signe_cc_call".a2b_round(abs($cost))."', ".
" $id_tariffgroup, $id_tariffplan, $id_ratecard, $trunk_id, '".$A2B->CallerID."', '$calltype', ".
" '$buycost', $id_card_package_offer, '".$A2B->dnid."', $calldestination, $id_did)";
if ($A2B->config["global"]['cache_enabled']) {
//insert query in the cache system
$create = false;
if (! file_exists( $A2B -> config["global"]['cache_path']))
$create = true;
if ($db = sqlite_open($A2B -> config["global"]['cache_path'], 0666, $sqliteerror)) {
if ($create)
sqlite_query($db,"CREATE TABLE cc_call ($QUERY_COLUMN)");
sqlite_query($db,$QUERY);
sqlite_close($db);
} else {
$A2B -> debug( ERROR, $agi, __FILE__, __LINE__, "[Error to connect to cache : $sqliteerror]\n");
}
} else {
$result = $A2B->instance_table -> SQLExec ($A2B -> DBHandle, $QUERY, 0);
$A2B -> debug( INFO, $agi, __FILE__, __LINE__, "[CC_asterisk_stop : SQL: DONE : result=".$result."]");
$A2B -> debug( DEBUG, $agi, __FILE__, __LINE__, "[CC_asterisk_stop : SQL: $QUERY]");
}
if ($sessiontime>0) {
if (!isset($td)) $td = (isset($this->td))?$this->td:"";
if ($didcall==0 && $callback==0) {
$myclause_nodidcall = " , redial='".$calledstation."' ";
} else {
$myclause_nodidcall='';
}
if (!defined($myclause_nodidcall)) {
$myclause_nodidcall = null;
}
//Update the global credit
$A2B -> credit = $A2B -> credit + $cost;
if ($A2B->nbused>0) {
$QUERY = "UPDATE cc_card SET credit= credit$signe".a2b_round(abs($cost))." $myclause_nodidcall, lastuse=now(), nbused=nbused+1 WHERE username='******'";
} else {
$QUERY = "UPDATE cc_card SET credit= credit$signe".a2b_round(abs($cost))." $myclause_nodidcall, lastuse=now(), firstusedate=now(), nbused=nbused+1 WHERE username='******'";
}
$A2B -> debug( DEBUG, $agi, __FILE__, __LINE__, "[CC_asterisk_stop 1.2: SQL: $QUERY]");
$result = $A2B->instance_table -> SQLExec ($A2B -> DBHandle, $QUERY, 0);
$QUERY = "SELECT period$td, UNIX_TIMESTAMP(periodexpiry$td), periodcount$td FROM cc_trunk WHERE id_trunk=$trunk_id LIMIT 1";
$result = $A2B->instance_table -> SQLExec ($A2B -> DBHandle, $QUERY);
if (is_array($result) && count($result)>0) {
$QUERY = "UPDATE cc_trunk SET secondusedreal = secondusedreal + $sessiontime, periodcount$td = ";
$tqr = " / billblocksec$td) * billblocksec$td, lastcallstoptime$td=now()";
$tqrperiod = ", periodexpiry$td = ";
$period = $result[0][0];
$periodexpiry = $result[0][1];
$periodcount = $result[0][2];
if ($period == 31) {
if ($periodexpiry <= time()) {
while ($periodexpiry <= time()) {
$preperiodexpiry = $periodexpiry;
$periodexpiry = strtotime('1 month', $periodexpiry);
$periodcount = 0;
}
} else $preperiodexpiry = strtotime('-1 month', $periodexpiry);
$tqr .= $tqrperiod . "FROM_UNIXTIME($periodexpiry) ";
} else {
$period *= 86400;
if ($period>1) {
if ($periodexpiry <= time()) {
while ($periodexpiry <= time()) $periodexpiry += 86400;
$periodexpiry += $period - 86400;
$periodcount = 0;
}
$tqr .= $tqrperiod . "FROM_UNIXTIME($periodexpiry) ";
}
$preperiodexpiry = $periodexpiry - $period;
}
if (time()-$sessiontime <= $preperiodexpiry && $period > 1 ) {
$limitsessiontime = time() - $preperiodexpiry;
} else {
$limitsessiontime = $sessiontime;
}
$QUERY .= "$periodcount + ceil($limitsessiontime" . $tqr . "WHERE id_trunk=$trunk_id";
} else $QUERY = "UPDATE cc_trunk SET secondusedreal = secondusedreal + $sessiontime WHERE id_trunk=$trunk_id";
$A2B -> debug( DEBUG, $agi, __FILE__, __LINE__, $QUERY);
$result = $A2B->instance_table -> SQLExec ($A2B -> DBHandle, $QUERY, 0);
$QUERY = "UPDATE cc_tariffplan SET secondusedreal = secondusedreal + $sessiontime WHERE id=$id_tariffplan";
$A2B -> debug( DEBUG, $agi, __FILE__, __LINE__, $QUERY);
$result = $A2B->instance_table -> SQLExec ($A2B -> DBHandle, $QUERY, 0);
}
monitor_recognize($A2B);
}