function process_xml_cdr($db, $leg, $xml_string)
{
    //set global variable
    global $debug;
    //fix the xml by escaping the contents of <sip_full_XXX>
    $xml_string = preg_replace_callback("/<([^><]+)>(.*?[><].*?)<\\/\\g1>/", function ($matches) {
        var_dump($matches);
        return '<' . $matches[1] . '>' . str_replace(">", "&gt;", str_replace("<", "&lt;", $matches[2])) . '</' . $matches[1] . '>';
    }, $xml_string);
    //parse the xml to get the call detail record info
    try {
        xml_cdr_log($xml_string);
        $xml = simplexml_load_string($xml_string);
        xml_cdr_log("\nxml load done\n");
    } catch (Exception $e) {
        echo $e->getMessage();
        xml_cdr_log("\nfail loadxml: " . $e->getMessage() . "\n");
    }
    //prepare the database object
    require_once "resources/classes/database.php";
    $database = new database();
    $database->table = "v_xml_cdr";
    //misc
    $uuid = check_str(urldecode($xml->variables->uuid));
    $database->fields['uuid'] = $uuid;
    $database->fields['accountcode'] = check_str(urldecode($xml->variables->accountcode));
    $database->fields['default_language'] = check_str(urldecode($xml->variables->default_language));
    $database->fields['bridge_uuid'] = check_str(urldecode($xml->variables->bridge_uuid));
    //$database->fields['digits_dialed'] = check_str(urldecode($xml->variables->digits_dialed));
    $database->fields['sip_hangup_disposition'] = check_str(urldecode($xml->variables->sip_hangup_disposition));
    $database->fields['pin_number'] = check_str(urldecode($xml->variables->pin_number));
    //time
    $database->fields['start_epoch'] = check_str(urldecode($xml->variables->start_epoch));
    $start_stamp = check_str(urldecode($xml->variables->start_stamp));
    $database->fields['start_stamp'] = $start_stamp;
    $database->fields['answer_stamp'] = check_str(urldecode($xml->variables->answer_stamp));
    $database->fields['answer_epoch'] = check_str(urldecode($xml->variables->answer_epoch));
    $database->fields['end_epoch'] = check_str(urldecode($xml->variables->end_epoch));
    $database->fields['end_stamp'] = check_str(urldecode($xml->variables->end_stamp));
    $database->fields['duration'] = check_str(urldecode($xml->variables->duration));
    $database->fields['mduration'] = check_str(urldecode($xml->variables->mduration));
    $database->fields['billsec'] = check_str(urldecode($xml->variables->billsec));
    $database->fields['billmsec'] = check_str(urldecode($xml->variables->billmsec));
    //codecs
    $database->fields['read_codec'] = check_str(urldecode($xml->variables->read_codec));
    $database->fields['read_rate'] = check_str(urldecode($xml->variables->read_rate));
    $database->fields['write_codec'] = check_str(urldecode($xml->variables->write_codec));
    $database->fields['write_rate'] = check_str(urldecode($xml->variables->write_rate));
    $database->fields['remote_media_ip'] = check_str(urldecode($xml->variables->remote_media_ip));
    $database->fields['hangup_cause'] = check_str(urldecode($xml->variables->hangup_cause));
    $database->fields['hangup_cause_q850'] = check_str(urldecode($xml->variables->hangup_cause_q850));
    //call center
    $database->fields['cc_side'] = check_str(urldecode($xml->variables->cc_side));
    $database->fields['cc_member_uuid'] = check_str(urldecode($xml->variables->cc_member_uuid));
    $database->fields['cc_queue_joined_epoch'] = check_str(urldecode($xml->variables->cc_queue_joined_epoch));
    $database->fields['cc_queue'] = check_str(urldecode($xml->variables->cc_queue));
    $database->fields['cc_member_session_uuid'] = check_str(urldecode($xml->variables->cc_member_session_uuid));
    $database->fields['cc_agent'] = check_str(urldecode($xml->variables->cc_agent));
    $database->fields['cc_agent_type'] = check_str(urldecode($xml->variables->cc_agent_type));
    $database->fields['waitsec'] = check_str(urldecode($xml->variables->waitsec));
    //app info
    $database->fields['last_app'] = check_str(urldecode($xml->variables->last_app));
    $database->fields['last_arg'] = check_str(urldecode($xml->variables->last_arg));
    //conference
    $database->fields['conference_name'] = check_str(urldecode($xml->variables->conference_name));
    $database->fields['conference_uuid'] = check_str(urldecode($xml->variables->conference_uuid));
    $database->fields['conference_member_id'] = check_str(urldecode($xml->variables->conference_member_id));
    //call quality
    $rtp_audio_in_mos = check_str(urldecode($xml->variables->rtp_audio_in_mos));
    if (strlen($rtp_audio_in_mos) > 0) {
        $database->fields['rtp_audio_in_mos'] = $rtp_audio_in_mos;
    }
    //get the values from the callflow.
    $x = 0;
    foreach ($xml->callflow as $row) {
        if ($x == 0) {
            $context = check_str(urldecode($row->caller_profile->context));
            $database->fields['destination_number'] = check_str(urldecode($row->caller_profile->destination_number));
            $database->fields['context'] = $context;
            $database->fields['network_addr'] = check_str(urldecode($row->caller_profile->network_addr));
        }
        $database->fields['caller_id_name'] = check_str(urldecode($row->caller_profile->caller_id_name));
        $database->fields['caller_id_number'] = check_str(urldecode($row->caller_profile->caller_id_number));
        $x++;
    }
    unset($x);
    //store the call leg
    $database->fields['leg'] = $leg;
    //store the call direction
    $database->fields['direction'] = check_str(urldecode($xml->variables->call_direction));
    //store post dial delay, in milliseconds
    $database->fields['pdd_ms'] = check_str(urldecode($xml->variables->progress_mediamsec) + urldecode($xml->variables->progressmsec));
    //get break down the date to year, month and day
    $tmp_time = strtotime($start_stamp);
    $tmp_year = date("Y", $tmp_time);
    $tmp_month = date("M", $tmp_time);
    $tmp_day = date("d", $tmp_time);
    //get the domain values from the xml
    $domain_name = check_str(urldecode($xml->variables->domain_name));
    $domain_uuid = check_str(urldecode($xml->variables->domain_uuid));
    xml_cdr_log("\ndomain_name is `{$domain_name}`; domain_uuid is '{$domain_uuid}'\n");
    //get the domain_uuid with the domain_name
    if (strlen($domain_uuid) == 0) {
        $sql = "select domain_uuid from v_domains ";
        if (strlen($domain_name) == 0 && $context != 'public' && $context != 'default') {
            $sql .= "where domain_name = '" . $context . "' ";
        } else {
            $sql .= "where domain_name = '" . $domain_name . "' ";
        }
        $row = $db->query($sql)->fetch();
        $domain_uuid = $row['domain_uuid'];
        if (strlen($domain_uuid) == 0) {
            $sql = "select domain_name, domain_uuid from v_domains ";
            $row = $db->query($sql)->fetch();
            $domain_uuid = $row['domain_uuid'];
            if (strlen($domain_name) == 0) {
                $domain_name = $row['domain_name'];
            }
        }
    }
    //set values in the database
    $database->domain_uuid = $domain_uuid;
    $database->fields['domain_uuid'] = $domain_uuid;
    $database->fields['domain_name'] = $domain_name;
    //check whether a recording exists
    $recording_relative_path = '/archive/' . $tmp_year . '/' . $tmp_month . '/' . $tmp_day;
    if (file_exists($_SESSION['switch']['recordings']['dir'] . $recording_relative_path . '/' . $uuid . '.wav')) {
        $recording_file = $recording_relative_path . '/' . $uuid . '.wav';
    } elseif (file_exists($_SESSION['switch']['recordings']['dir'] . $recording_relative_path . '/' . $uuid . '.mp3')) {
        $recording_file = $recording_relative_path . '/' . $uuid . '.mp3';
    }
    if (isset($recording_file) && !empty($recording_file)) {
        $database->fields['recording_file'] = $recording_file;
    }
    //save to the database in xml format
    if ($_SESSION['cdr']['format']['text'] == "xml" && $_SESSION['cdr']['storage']['text'] == "db") {
        $database->fields['xml'] = check_str($xml_string);
    }
    //save to the database in json format
    if ($_SESSION['cdr']['format']['text'] == "json" && $_SESSION['cdr']['storage']['text'] == "db") {
        $database->fields['json'] = check_str(json_encode($xml));
    }
    //insert the check_str($extension_uuid)
    if (strlen($xml->variables->extension_uuid) > 0) {
        $database->fields['extension_uuid'] = check_str(urldecode($xml->variables->extension_uuid));
    }
    //billing information
    if (file_exists($_SERVER['DOCUMENT_ROOT'] . PROJECT_PATH . "/app/billing/app_config.php")) {
        $db2 = new database();
        $lcr_currency = strlen($_SESSION['billing']['currency']['text']) ? $_SESSION['billing']['currency']['text'] : 'USD';
        $accountcode = strlen(urldecode($xml->variables->accountcode)) ? check_str(urldecode($xml->variables->accountcode)) : $domain_name;
        switch (check_str(urldecode($xml->variables->call_direction))) {
            case "outbound":
                $destination_number = check_str(urldecode($xml->variables->lcr_query_digits));
                $destination_number_serie = number_series($destination_number);
                $database->fields['carrier_name'] = check_str(urldecode($xml->variables->lcr_carrier));
                $sql_rate = "SELECT v_lcr.connect_increment, v_lcr.talk_increment, v_lcr.currency FROM v_lcr, v_carriers WHERE v_carriers.carrier_name = '" . $xml->variables->lcr_carrier . "' AND v_lcr.rate=" . $xml->variables->lcr_rate . " AND v_lcr.lcr_direction = '" . check_str(urldecode($xml->variables->call_direction)) . "' AND digits IN ({$destination_number_serie}) AND v_lcr.carrier_uuid = v_carriers.carrier_uuid  ORDER BY digits DESC, rate ASC limit 1";
                $sql_user_rate = "SELECT v_lcr.currency, connect_increment, talk_increment FROM v_lcr JOIN v_billings ON v_billings.type_value='{$accountcode}' WHERE v_lcr.carrier_uuid IS NULL AND v_lcr.lcr_direction = '" . check_str(urldecode($xml->variables->call_direction)) . "' AND v_lcr.lcr_profile=v_billings.lcr_profile AND NOW() >= v_lcr.date_start AND NOW() < v_lcr.date_end AND digits IN ({$destination_number_serie}) ORDER BY digits DESC, rate ASC, date_start DESC LIMIT 1";
                if ($debug) {
                    echo "sql_rate: {$sql_rate}\n";
                    echo "sql_user_rate: {$sql_user_rate}\n";
                }
                $db2->sql = $sql_rate;
                $db2->result = $db2->execute();
                //							print_r($db2->result);
                $lcr_currency = strlen($db2->result[0]['currency']) ? check_str($db2->result[0]['currency']) : (strlen($_SESSION['billing']['currency']['text']) ? $_SESSION['billing']['currency']['text'] : 'USD');
                $lcr_rate = strlen($xml->variables->lcr_rate) ? $xml->variables->lcr_rate : 0;
                $lcr_first_increment = strlen($db2->result[0]['connect_increment']) ? check_str($db2->result[0]['connect_increment']) : 60;
                $lcr_second_increment = strlen($db2->result[0]['talk_increment']) ? check_str($db2->result[0]['talk_increment']) : 60;
                unset($db2->sql);
                unset($db2->result);
                $db2->sql = $sql_user_rate;
                $db2->result = $db2->execute();
                $lcr_user_rate = strlen($xml->variables->lcr_user_rate) ? $xml->variables->lcr_user_rate : 0.01;
                $lcr_user_first_increment = strlen($db2->result[0]['connect_increment']) ? check_str($db2->result[0]['connect_increment']) : 60;
                $lcr_user_second_increment = strlen($db2->result[0]['talk_increment']) ? check_str($db2->result[0]['talk_increment']) : 60;
                $lcr_user_currency = strlen($db2->result[0]['currency']) ? check_str($db2->result[0]['currency']) : (strlen($_SESSION['billing']['currency']['text']) ? $_SESSION['billing']['currency']['text'] : 'USD');
                unset($db2->sql);
                unset($db2->result);
                break;
            case "inbound":
                $callee_number = check_str(urldecode($row->caller_profile->destination_number));
                $callee_number_serie = number_series($callee_number);
                $sql_user_rate = "SELECT v_lcr.currency, v_lcr.rate, v_lcr.connect_increment, v_lcr.talk_increment FROM v_lcr JOIN v_billings ON v_billings.type_value='{$accountcode}' WHERE v_lcr.carrier_uuid IS NULL AND v_lcr.lcr_direction = '" . check_str(urldecode($xml->variables->call_direction)) . "' AND v_lcr.lcr_profile=v_billings.lcr_profile AND NOW() >= v_lcr.date_start AND NOW() < v_lcr.date_end AND digits IN ({$destination_number_serie}) ORDER BY digits DESC, rate ASC, date_start DESC LIMIT 1";
                if ($debug) {
                    echo "sql_user_rate: {$sql_user_rate}\n";
                }
                $db2->sql = $sql_user_rate;
                $db2->result = $db2->execute();
                // If selling rate is found, then we fill with data, otherwise rate will be 0
                $lcr_currency = strlen($db2->result[0]['currency']) ? check_str($db2->result[0]['currency']) : (strlen($_SESSION['billing']['currency']['text']) ? $_SESSION['billing']['currency']['text'] : 'USD');
                $lcr_user_rate = strlen($db2->result[0]['rate']) ? $db2->result[0]['rate'] : 0;
                $lcr_user_first_increment = strlen($db2->result[0]['connect_increment']) ? $db2->result[0]['connect_increment'] : 60;
                $lcr_user_second_increment = strlen($db2->result[0]['talk_increment']) ? $db2->result[0]['talk_increment'] : 60;
                $lcr_user_currency = strlen($db2->result[0]['currency']) ? check_str($db2->result[0]['currency']) : (strlen($_SESSION['billing']['currency']['text']) ? $_SESSION['billing']['currency']['text'] : 'USD');
                // Actually, there is no way to detect what carrier is the calling comming from using current information
                $lcr_rate = 0;
                $lcr_first_increment = 0;
                $lcr_second_increment = 0;
                unset($db2->sql);
                unset($db2->result);
                break;
            case "local":
                $destination_number = check_str(urldecode($xml->variables->lcr_query_digits));
                $destination_number_serie = number_series($destination_number);
                $sql_user_rate = "SELECT v_lcr.currency, connect_increment, talk_increment FROM v_lcr JOIN v_billings ON v_billings.type_value='{$accountcode}' WHERE v_lcr.carrier_uuid IS NULL AND v_lcr.lcr_direction = '" . check_str(urldecode($xml->variables->call_direction)) . "' AND v_lcr.lcr_profile=v_billings.lcr_profile AND NOW() >= v_lcr.date_start AND NOW() < v_lcr.date_end AND digits IN ({$destination_number_serie}) ORDER BY digits DESC, rate ASC, date_start DESC LIMIT 1";
                if ($debug) {
                    echo "sql_user_rate: {$sql_user_rate}\n";
                }
                $db2->sql = $sql_user_rate;
                $db2->result = $db2->execute();
                // If selling rate is found, then we fill with data, otherwise rate will be 0
                $lcr_currency = strlen($db2->result[0]['currency']) ? check_str($db2->result[0]['currency']) : (strlen($_SESSION['billing']['currency']['text']) ? $_SESSION['billing']['currency']['text'] : 'USD');
                $lcr_user_rate = strlen($db2->result[0]['rate']) ? ${$db2}->result[0]['rate'] : 0;
                $lcr_user_first_increment = strlen($db2->result[0]['connect_increment']) ? $db2->result[0]['connect_increment'] : 60;
                $lcr_user_second_increment = strlen($db2->result[0]['talk_increment']) ? $db2->result[0]['talk_increment'] : 60;
                $lcr_user_currency = strlen($db2->result[0]['currency']) ? check_str($db2->result[0]['currency']) : (strlen($_SESSION['billing']['currency']['text']) ? $_SESSION['billing']['currency']['text'] : 'USD');
                // Actually, internal calls have 0 cost
                $lcr_rate = 0;
                $lcr_first_increment = 0;
                $lcr_second_increment = 0;
                unset($db2->sql);
                unset($db2->result);
                break;
        }
        // Please note that we save values using LCR currency, but we discount balance in billing currency
        $time = check_str(urldecode($xml->variables->billsec));
        $call_buy = call_cost($lcr_rate, $lcr_first_increment, $lcr_second_increment, $time);
        $call_sell = call_cost($lcr_user_rate, $lcr_user_first_increment, $lcr_user_second_increment, $time);
        // Costs/Sell call are in original LCR currency, they need to be converted
        $database->fields['call_buy'] = check_str($call_buy);
        $database->fields['call_sell'] = check_str($call_sell);
        $db2->table = "v_xml_cdr";
        $db2->sql = "SELECT currency FROM v_billings WHERE type_value='{$accountcode}' LIMIT 1";
        $db2->result = $db2->execute();
        $actual_currency = strlen($lcr_currency) ? $lcr_currency : (strlen($_SESSION['billing']['currency']['text']) ? $_SESSION['billing']['currency']['text'] : 'USD');
        $billing_currency = strlen($db2->result[0]['currency']) ? $db2->result[0]['currency'] : $default_currency;
        if ($debug) {
            echo "sql: " . $db2->sql . "\n";
            echo "c " . $database->fields['carrier_name'] . "\n";
            echo "t {$time}\n";
            echo "b r:{$lcr_rate} - {$lcr_first_increment} - {$lcr_first_increment} = {$call_buy}\n";
            echo "s r:{$lcr_user_rate} - {$lcr_user_first_increment} - {$lcr_user_second_increment} = {$call_sell}\n";
            echo "lcr currency {$lcr_currency}\n";
            echo "actual currency {$actual_currency}\n";
            echo "user currency {$lcr_user_currency}\n";
            echo "billing currency {$billing_currency}\n";
        }
        unset($database->sql);
        unset($database->result);
        $sql_balance = "SELECT balance, old_balance FROM v_billings WHERE type_value='" . check_str(urldecode($xml->variables->accountcode)) . "'";
        $db2->sql = $sql_balance;
        $db2->result = $db2->execute();
        $balance = $db2->result[0]['balance'];
        $old_balance = $db2->result[0]['old_balance'];
        if ($debug) {
            echo "sql_balance: {$sql_balance}\n";
            echo "bal: {$balance}\n";
            echo "old bal: {$old_balance}\n";
        }
        // Lets convert rate from lcr_currency to billing_currency
        $billing_call_sell = currency_convert($call_sell, $billing_currency, $lcr_user_currency);
        if ($debug) {
            echo "bcs: {$billing_call_sell} {$billing_currency}\n";
        }
        // Remember that old_balance is using billing_currency
        $updated_balance = (double) $old_balance - (double) $billing_call_sell;
        unset($db2->sql);
        unset($db2->result);
        $sql_update_balance = "UPDATE v_billings SET balance={$updated_balance}, old_balance={$updated_balance} WHERE type_value='" . check_str(urldecode($xml->variables->accountcode)) . "'";
        if ($debug) {
            echo "sql_update_balance: {$sql_update_balance}\n";
        }
        $db2->sql = $sql_update_balance;
        $db2->result = $db2->execute();
        unset($db2->sql);
        unset($db2->result);
    }
    //insert xml_cdr into the db
    if (strlen($start_stamp) > 0) {
        $database->add();
        if ($debug) {
            echo $database->sql . "\n";
        }
    }
    //insert the values
    if (strlen($uuid) > 0) {
        if ($debug) {
            $time5_insert = microtime(true);
            //echo $sql."<br />\n";
        }
        try {
            $error = "false";
            //$db->exec(check_sql($sql));
        } catch (PDOException $e) {
            $tmp_dir = $_SESSION['switch']['log']['dir'] . '/xml_cdr/failed/';
            if (!file_exists($tmp_dir)) {
                mkdir($tmp_dir, 0777, true);
            }
            if ($_SESSION['cdr']['format']['text'] == "xml") {
                $tmp_file = $uuid . '.xml';
                $fh = fopen($tmp_dir . '/' . $tmp_file, 'w');
                fwrite($fh, $xml_string);
            } else {
                $tmp_file = $uuid . '.json';
                $fh = fopen($tmp_dir . '/' . $tmp_file, 'w');
                fwrite($fh, json_encode($xml));
            }
            fclose($fh);
            if ($debug) {
                echo $e->getMessage();
            }
            $error = "true";
        }
        if ($_SESSION['cdr']['storage']['text'] == "dir" && $error != "true") {
            if (strlen($uuid) > 0) {
                $tmp_time = strtotime($start_stamp);
                $tmp_year = date("Y", $tmp_time);
                $tmp_month = date("M", $tmp_time);
                $tmp_day = date("d", $tmp_time);
                $tmp_dir = $_SESSION['switch']['log']['dir'] . '/xml_cdr/archive/' . $tmp_year . '/' . $tmp_month . '/' . $tmp_day;
                if (!file_exists($tmp_dir)) {
                    mkdir($tmp_dir, 0777, true);
                }
                if ($_SESSION['cdr']['format']['text'] == "xml") {
                    $tmp_file = $uuid . '.xml';
                    $fh = fopen($tmp_dir . '/' . $tmp_file, 'w');
                    fwrite($fh, $xml_string);
                } else {
                    $tmp_file = $uuid . '.json';
                    $fh = fopen($tmp_dir . '/' . $tmp_file, 'w');
                    fwrite($fh, json_encode($xml));
                }
                fclose($fh);
            }
        }
        unset($error);
        if ($debug) {
            global $insert_time, $insert_count;
            $insert_time += microtime(true) - $time5_insert;
            //add this current query.
            $insert_count++;
        }
    }
    unset($sql);
}
Beispiel #2
0
     if (strlen($json_string) > 0) {
         $format = "json";
     }
     try {
         if ($format == 'json') {
             $array = json_decode($json_string, true);
         }
         if ($format == 'xml') {
             $array = json_decode(json_encode((array) simplexml_load_string($xml_string)), true);
         }
     } catch (Exception $e) {
         echo $e->getMessage();
     }
     $n = $lcr_direction == "inbound" ? check_str(urldecode($array["caller_profile"]["caller_id_number"])) : check_str(urldecode($array["variables"]["lcr_query_digits"]));
     $database->table = "v_lcr";
     $database->sql = "SELECT currency FROM v_lcr WHERE v_lcr.carrier_uuid IS NULL AND v_lcr.enabled='true' AND v_lcr.lcr_direction='{$lcr_direction}' AND v_lcr.digits IN (" . number_series($n) . ") ORDER BY digits DESC, rate ASC, date_start DESC LIMIT 1";
     $database->result = $database->execute();
     // print "<pre>"; print $database->sql . ":";print "[".$database->result[0]['currency']."]"; print_r($array); print "</pre>";
     $lcr_currency = is_string($database->result[0]['currency']) && strlen($database->result[0]['currency']) ? $database->result[0]['currency'] : (strlen($_SESSION['billing']['currency']['text']) ? $_SESSION['billing']['currency']['text'] : 'USD');
     //billed currency
     unset($database->sql);
     unset($database->result);
     if ($sell_price) {
         $price = currency_convert($sell_price, $billing_currency, $lcr_currency);
     } else {
         $price = 0;
     }
     echo "\t<td valign='top' class='" . $row_style[$c] . "'>" . number_format($price, 6) . " {$billing_currency}</td>\n";
     unset($sell_price, $price);
 }
 if (permission_exists("xml_cdr_pdd")) {