function delete()
{
$db = new database();
$query = "DELETE FROM Computer WHERE id={$this->id}";
$db->execute($query);
$db = null;
}
public function gc($maxlifetime)
{
$db = new database();
$db->query("DELETE FROM tbl_session\n WHERE session_lastaccesstime < DATE_SUB(NOW(),\n INTERVAL " . $maxlifetime . " SECOND)");
$db->execute();
return true;
}
function show_page_result()
{
$row_num = ($this->numPage - 1) * $this->pagesize;
//表示每一页从第几条数据开始显示
$row_num = $row_num . ",";
$SQL = "SELECT * FROM `test` LIMIT {$row_num} {$this->pagesize}";
$db = new database();
$query = $db->execute($SQL);
while ($row = mysql_fetch_array($query)) {
echo "<b>" . $row[name] . " | " . $row[sex] . "<hr>";
}
$db = null;
}
private function isMaintenanceNeeded()
{
global $config;
global $db;
// TODO: remove this loading after legacy maintenance code has been deleted
if (!isset($db)) {
require_once dirname(dirname(dirname(__FILE__))) . '/classes/config.php';
$config = new config();
require_once dirname(dirname(dirname(__FILE__))) . '/classes/db.php';
$db = new database();
}
$this->lockTable('misc_data');
$today = date('Y-m-d');
$last_maintenance = '0000-00-0000';
// check last time where maintenance was performed
$query = $db->SQL('SELECT `last_maintenance` FROM `misc_data` LIMIT 1');
$lastMaintenanceSaved = false;
while ($row = $db->fetchRow($query)) {
$lastMaintenanceSaved = true;
if (isset($row['last_maintenance'])) {
$last_maintenance = $row['last_maintenance'];
}
}
$db->free($query);
// save new maintenance timestamp
if ($lastMaintenanceSaved) {
$query = $db->prepare('UPDATE `misc_data` SET `last_maintenance`=?');
$db->execute($query, $today);
$db->free($query);
} else {
$query = $db->prepare('INSERT INTO `misc_data` (`last_maintenance`) VALUES (?)');
$db->execute($query, $today);
$db->free($query);
}
$this->unlockTables();
// daily maintenance
return strcasecmp($today, $last_maintenance) !== 0;
}
function get_oneuser($field, $value)
{
$field_array = array("id", "name");
//查询方式
if (in_array($field, $field_array)) {
$sql = "SELECT * FROM `{$this->usertable}` FROM {$field}='{$value}'";
$db = new database();
$res = $db->execute($sql);
$obj_user = mysql_fetch_object($res);
return $obj_user;
} else {
echo "查询方式不对";
}
}
function video()
{
$db = new database();
$db->connect();
$this->id = $db->execute("SELECT max(video_id) as maximum from video");
while ($this->row = mysqli_fetch_array($this->id)) {
if (empty($this->row['maximum'])) {
$this->id_no = "VID00001";
} else {
if (intval(substr($this->row['maximum'], 8)) == 99999) {
$str = substr($this->row['maximum'], 0, 8);
++$str;
$this->id_no = $str . '00001';
} else {
$this->id_no = ++$this->row['maximum'];
}
}
}
return $this->id_no;
}
public function logEvent($what, $data)
{
$defineWhat = array("ST" => "Scanned Ticket", "DS" => "Duplicate scan", "IS" => "Invalid scan (barcode not in database)", "NU" => "No username specified");
$db = new database();
if (isset($_GET['user'])) {
$user = $user = filter_input(INPUT_GET, 'user', FILTER_SANITIZE_SPECIAL_CHARS);
} else {
$user = '';
}
$db = new database();
$db->query("INSERT INTO tbl_log (who, what, data, timestamp, username) VALUES (?, ?, ?, NOW(), ?)");
$db->bind(1, sha1($_SERVER['REMOTE_ADDR']));
$db->bind(2, $what);
$db->bind(3, $data);
$db->bind(4, $user);
try {
$db->execute();
} catch (Exception $e) {
return "Database error: " . $e->getMessage();
}
}
<?php
function __autoload($class_name)
{
include_once $class_name . ".class.php";
}
$numq = "SELECT * FROM `test`";
$db = new database();
$res = $db->execute($numq);
//总数据条数
$nums = $db->num_rows($res);
$pagesize = 3;
if (!empty($_GET['pagesize'])) {
$pagesize = $_GET['pagesize'];
}
$page = new page($pagesize, $nums);
//$page->show_page_way_1();
$page->show_page_way_3();
$page->show_page_result();
$message = $orm->message;
$destination_response = $orm->message;
//remove the temporary permission
$p->delete($permission, 'temp');
//get the destination_uuid
if (strlen($destination_response['uuid']) > 0) {
$destination_uuid = $destination_response['uuid'];
}
//redirect the user
if ($action == "add") {
$_SESSION["message"] = $text['message-add'];
// billing
if (file_exists($_SERVER['DOCUMENT_ROOT'] . PROJECT_PATH . "/app/billing/app_config.php")) {
$db2 = new database();
$db2->sql = "select currency, billing_uuid, balance from v_billings where type_value='{$destination_accountcode}'";
$db2->result = $db2->execute();
$default_currency = strlen($_SESSION['billing']['currency']['text']) ? $_SESSION['billing']['currency']['text'] : 'USD';
$billing_currency = strlen($db2->result[0]['currency']) ? $db2->result[0]['currency'] : $default_currency;
$destination_sell_current_currency = currency_convert($destination_sell, $billing_currency, $currency);
$billing_uuid = $db2->result[0]['billing_uuid'];
$balance = $db2->result[0]['balance'];
unset($db2->sql, $db2->result);
$balance -= $destination_sell_current_currency;
$db2->sql = "update v_billings set balance = {$balance}, old_balance = {$balance} where type_value='{$destination_accountcode}'";
$db2->result = $db2->execute();
unset($db2->sql, $db2->result);
$billing_invoice_uuid = uuid();
$user_uuid = check_str($_SESSION['user_uuid']);
$settled = 1;
$mc_gross = -1 * $destination_sell_current_currency;
$post_payload = serialize($_POST);
if (permission_exists('recording_download')) {
echo "<a href=\"" . PROJECT_PATH . "/app/recordings/recordings.php?a=download&type=rec&t=bin&filename=" . base64_encode($recording_file_path) . "\" title='" . $text['label-download'] . "'>" . $v_link_label_download . "</a>";
}
echo "\t</td>\n";
} else {
echo "\t<td valign='top' align='center' class='" . $row_style[$c] . "'> </td>\n";
}
}
echo "\t<td valign='top' class='" . $row_style[$c] . "' style='text-align: center;' nowrap='nowrap'>" . $tmp_start_epoch . "</td>\n";
echo "\t<td valign='top' class='" . $row_style[$c] . "' style='text-align: right;'>" . ($row['tta'] > 0 ? $row['tta'] . "s" : " ") . "</td>\n";
echo "\t<td valign='top' class='" . $row_style[$c] . "' style='text-align: center;'>" . gmdate("G:i:s", $seconds) . "</td>\n";
if (file_exists($_SERVER["PROJECT_ROOT"] . "/app/billing/app_config.php")) {
$database->table = "v_xml_cdr";
$accountcode = strlen($row["accountcode"]) ? $row["accountcode"] : $_SESSION[domain_name];
$database->sql = "SELECT currency FROM v_billings WHERE type_value='{$accountcode}'";
$database->result = $database->execute();
$billing_currency = strlen($database->result[0]['currency']) ? $database->result[0]['currency'] : 'USD';
$billing_currency = strlen($database->result[0]['currency']) ? $database->result[0]['currency'] : (strlen($_SESSION['billing']['currency']['text']) ? $_SESSION['billing']['currency']['text'] : 'USD');
unset($database->sql);
unset($database->result);
$sell_price = strlen($row['call_sell']) ? $row['call_sell'] : 0;
$lcr_direction = strlen($row['direction']) ? $row['direction'] : "outbound";
$xml_string = trim($row["xml"]);
$json_string = trim($row["json"]);
if (strlen($xml_string) > 0) {
$format = "xml";
}
if (strlen($json_string) > 0) {
$format = "json";
}
try {
public function attemptToInstall()
{
require_once '../install/sql.php';
$db = new database();
$db->query($sql);
try {
$db->execute();
} catch (Exception $e) {
return returnError("Database error: " . $e->getMessage());
}
$lockfile = 'inc/dbinstalled.lck';
clearstatcache();
if (file_exists(ABSPATH . 'inc/dbinstalled.lck')) {
unlink($lockfile);
}
$handle = fopen(ABSPATH . $lockfile, 'w') or die('Cannot open file: ' . $lockfile);
fwrite($handle, date(DATE_FORMAT));
fclose();
}
public function save($obj)
{
$where = $this->get_pk_condition($obj);
$obj = $this->remove_pk($obj);
$setsql = $this->implode($obj);
//$sql = "UPDATE " . $this->table . " SET " . $setsql . " WHERE " . $where;
$table = $this->table;
$sql = "UPDATE {$table} SET {$setsql} WHERE {$where};";
return database::execute($sql);
}
<?php
#insert section
session_start();
require_once "../classes/database.php";
require_once "../classes/retrival.php";
$db = new database();
$db->connect();
if (isset($_REQUEST['Submit'])) {
$s_name = $_REQUEST['name'];
$s_details = $_REQUEST['detail'];
$c_name = $_REQUEST['course'];
$query = "SELECT * from course where c_name='{$c_name}'";
$result = $db->execute($query);
if (mysqli_num_rows($result) > 0) {
while ($row = mysqli_fetch_array($result)) {
$c = $row['course_id'];
// echo $c;
}
$c_id = $GLOBALS['c'];
$_SESSION['course_id'] = $c_id;
$rt = new retrieval();
$s_id = $rt->section();
$query = "INSERT INTO section(section_id, course_id, s_name, s_detail)\n\t\t\t VALUES('{$s_id}', '{$c_id}', '{$s_name}', '{$s_details}')";
if (empty($db->execute($query))) {
echo "0";
} else {
echo "1";
}
} else {
echo "not working";
public function getUser($uid)
{
$db = new database();
$db->query("SELECT username, email, uid, status, rank, created\n FROM tbl_user\n WHERE uid = ?");
$db->bind(1, $uid);
try {
$db->execute();
} catch (Exception $e) {
return returnError("Database error: " . $e->getMessage());
}
return $db->single();
}
function insertPost()
{
$title = $_POST['title'];
$content = $_POST['content'];
$date = date("Y-m-d H:i:s");
$user = $_SESSION['username'];
$hidden = 'false';
if (isset($title, $content)) {
include_once 'database.class.php';
$dsn = new database();
$dsn->query("INSERT INTO news (title, member, date, content, hidden) VALUES ('{$title}','{$user}','{$date}','{$content}','{$hidden}')");
$dsn->execute();
}
}
public function searchByName($string)
{
$db = new database();
$db->query("SELECT * FROM tbl_ticket WHERE CONCAT_WS(' ',tbl_ticket.firstname,tbl_ticket.lastname) LIKE ?");
$db->bind(1, '%' . $string . '%');
$db->execute();
return $db->resultSet();
}
public function countRows($table)
{
$database = new database();
$database->query("SELECT COUNT(*) AS num FROM {$table}");
$database->execute();
return $database->single()->num;
}
<?php
# login verification
require_once '../classes/database.php';
?>
<?php
if (isset($_POST['Submit'])) {
$user = $_POST['user'];
//echo $_POST['pass'] ;
$pass = md5($_POST['pass']);
$query = "SELECT * from users where user='******'";
$db = new database();
$db->connect();
$result = $db->execute($query);
while ($row = mysqli_fetch_array($result)) {
if (strcmp($row['user'], $user) == 0 && strcmp($pass, $row['password']) == 0) {
session_start();
$_SESSION['user'] = $user;
echo "1";
}
}
$db->disconnect();
} else {
echo "0";
}
// 初始化
try {
database::init($config);
} catch (DbException $dbe) {
echo 'can not connect to database' . $dbe;
}
// 主要四个方法:
// database::execute($sql)
// database::fetch_all($sql)
// database::fetch_one($sql)
try {
// 开始事务
database::begin_tx();
// 执行sql语句, 增删改操作
$afr = database::execute("insert into product values (14, 'xiaomi 2s 32G BK', 'xiaomi 2s 32G', 2199)");
$afr = database::execute("insert into producta values (15, 'xiaomi 2s 32G CDMA', 'xiaomi 2s CDMA', 2199)");
// 提交事务
database::commit();
} catch (Exception $e) {
// 事务回滚
database::rollback();
echo "error happen tx has benn rollback {$br}";
}
// 查询数据库, 返回所有行
$products = database::fetch_all("select * from product" . database::paging(1, 100));
$psize = count($products);
echo "product size: {$psize} {$br}";
foreach ($products as $prod) {
echo $prod['product_id'] . "," . $prod['product_name'] . "," . $prod['price'] . $br;
}
// 查询数据库, 返回一行
function process()
{
global $DB;
global $website;
global $events;
global $theme;
set_time_limit(0);
setlocale(LC_ALL, $_SESSION['navigate_install_locale']);
$lang = navigate_install_load_language();
switch ($_REQUEST['process']) {
case 'verify_zip':
sleep(1);
if (!file_exists('package.zip')) {
die(json_encode($lang['missing_package']));
} else {
$zip = new ZipArchive();
if ($zip->open('package.zip') !== TRUE) {
die(json_encode($lang['invalid_package']));
} else {
$zip->close();
die(json_encode(true));
}
}
break;
case 'extract_zip':
$npath = getcwd() . NAVIGATE_FOLDER;
$npath = str_replace('\\', '/', $npath);
if (!file_exists($npath)) {
mkdir($npath);
}
if (file_exists($npath)) {
$zip = new ZipArchive();
if ($zip->open('package.zip') === TRUE) {
$zip->extractTo($npath);
$zip->close();
copy($npath . '/crossdomain.xml', dirname($npath) . '/crossdomain.xml');
die(json_encode(true));
} else {
die(json_encode($lang['extraction_failed']));
}
}
die(json_encode($lang['folder_not_exists']));
break;
case 'chmod':
sleep(1);
// chmod the directories recursively
$npath = getcwd() . NAVIGATE_FOLDER;
if (!navigate_install_chmodr($npath, 0755)) {
die(json_encode($lang['chmod_failed']));
} else {
die(json_encode(true));
}
break;
case 'verify_database':
if ($_REQUEST['PDO_DRIVER'] == 'mysql' || $_REQUEST['PDO_DRIVER'] == 'mysql-socket') {
try {
$dsn = "mysql:host=" . $_REQUEST['PDO_HOSTNAME'] . ";port=" . $_REQUEST['PDO_PORT'] . ';charset=utf8';
if ($_REQUEST['PDO_DRIVER'] == "mysql-socket") {
$dsn = "mysql:unix_socket=" . $_REQUEST['PDO_SOCKET'] . ";charset=utf8";
}
$db_test = @new PDO($dsn, $_REQUEST['PDO_USERNAME'], $_REQUEST['PDO_PASSWORD']);
if (!$db_test) {
echo json_encode(array('error' => $lang['database_connect_error']));
} else {
$create_database_privilege = false;
$drop_database_privilege = false;
$stm = $db_test->query('SHOW DATABASES;');
$rs = $stm->fetchAll(PDO::FETCH_COLUMN, 'Database');
$rs = array_diff($rs, array('mysql', 'information_schema'));
$stm = $db_test->query('SHOW PRIVILEGES;');
$privileges = $stm->fetchAll(PDO::FETCH_ASSOC);
for ($p = 0; $p < count($privileges); $p++) {
if ($privileges[$p]['Privilege'] == 'Create') {
if (strpos($privileges[$p]['Context'], 'Databases') !== false) {
$create_database_privilege = true;
}
}
if ($privileges[$p]['Privilege'] == 'Drop') {
if (strpos($privileges[$p]['Context'], 'Databases') !== false) {
$drop_database_privilege = true;
}
}
}
if ($create_database_privilege && $drop_database_privilege) {
// check if we are really allowed to create databases
$dbname = 'navigate_test_' . time();
$create_result = $db_test->exec('CREATE DATABASE ' . $dbname);
if ($create_result) {
$db_test->exec('DROP DATABASE ' . $dbname);
}
if (!$create_result) {
$create_database_privilege = false;
}
}
$db_test = NULL;
echo json_encode(array('databases' => array_values($rs), 'create_database_privilege' => $create_database_privilege));
}
} catch (Exception $e) {
echo json_encode(array('error' => $e->getMessage()));
}
} else {
echo json_encode(array('error' => $lang['database_driver_error']));
}
exit;
break;
case 'database_create':
$DB = new database();
if (!$DB->connect()) {
// try to create the database automatically
if (PDO_DRIVER == 'mysql') {
if (PDO_DATABASE != '') {
if (PDO_HOSTNAME != "") {
$dsn = "mysql:host=" . PDO_HOSTNAME . ";port=" . PDO_PORT . ";charset=utf8";
} else {
$dsn = "mysql:unix_socket=" . PDO_SOCKET . ";charset=utf8";
}
$db_test = new PDO($dsn, PDO_USERNAME, PDO_PASSWORD);
$db_test->exec('CREATE DATABASE IF NOT EXISTS `' . PDO_DATABASE . '` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;');
$db_test = NULL;
}
if (!$DB->connect()) {
echo json_encode(array('error' => $DB->get_last_error()));
} else {
echo json_encode(array('ok' => $lang['database_created']));
}
}
} else {
echo json_encode(array('ok' => $lang['database_exists']));
}
exit;
break;
case 'database_import':
$DB = new database();
if (!$DB->connect()) {
die(json_encode(array('error' => $DB->get_last_error())));
}
try {
$sql = file_get_contents('navigate.sql');
$sql = str_replace("{#!NAVIGATE_FOLDER!#}", NAVIGATE_PARENT . NAVIGATE_FOLDER, $sql);
$sql = explode("\n\n", $sql);
// can't do it in one step => SQLSTATE[HY000]: General error: 2014
foreach ($sql as $sqlline) {
$sqlline = trim($sqlline);
if (empty($sqlline)) {
continue;
}
if (!@$DB->execute($sqlline)) {
$error = $DB->get_last_error();
}
if (!empty($error)) {
break;
}
}
} catch (Exception $e) {
$error = $e->getMessage();
}
if (!empty($error) && false) {
echo json_encode(array('error' => $error));
} else {
echo json_encode(array('ok' => $lang['done']));
}
exit;
break;
case 'create_account':
// create admin
try {
$DB = new database();
if (!$DB->connect()) {
die(json_encode(array('error' => $DB->get_last_error())));
}
$user = new user();
$user->id = 0;
$user->username = $_SESSION['NAVIGATE-SETUP']['ADMIN_USERNAME'];
$user->set_password($_SESSION['NAVIGATE-SETUP']['ADMIN_PASSWORD']);
$user->email = $_SESSION['NAVIGATE-SETUP']['ADMIN_EMAIL'];
$user->profile = 1;
$user->skin = 'cupertino';
$user->language = $_SESSION['navigate_install_lang'];
$user->blocked = 0;
$user->timezone = 'UTC';
$user->date_format = 'Y-m-d H:i';
$user->decimal_separator = ',';
$user->thousands_separator = '';
$user->attempts = 0;
$user->cookie_hash = '';
$user->activation_key = '';
$ok = $user->insert();
if (!$ok) {
throw new Exception($lang['error']);
}
// create default website details
$website = new website();
$website->create_default();
$_SESSION['NAVIGATE-SETUP']['WEBSITE_DEFAULT'] = $website->id;
echo json_encode(array('ok' => $lang['done']));
} catch (Exception $e) {
echo json_encode(array('error' => $e->getMessage()));
}
exit;
break;
case 'install_default_theme':
try {
$DB = new database();
if (!$DB->connect()) {
die(json_encode(array('error' => $DB->get_last_error())));
}
if (@$_SESSION['NAVIGATE-SETUP']['DEFAULT_THEME'] == 'theme_kit') {
$website = new website();
$website->load($_SESSION['NAVIGATE-SETUP']['WEBSITE_DEFAULT']);
$website->theme = 'theme_kit';
$website->languages = array('en' => array('language' => 'en', 'variant' => '', 'code' => 'en', 'system_locale' => 'en_US.utf8'), 'es' => array('language' => 'es', 'variant' => '', 'code' => 'es', 'system_locale' => 'es_ES.utf8'));
$website->languages_published = array('en', 'es');
$website->save();
// default objects (first user, no events bound...)
$user = new user();
$user->load(1);
$events = new events();
$zip = new ZipArchive();
$zip_open_status = $zip->open(NAVIGATE_PATH . '/themes/theme_kit.zip');
if ($zip_open_status === TRUE) {
$zip->extractTo(NAVIGATE_PATH . '/themes/theme_kit');
$zip->close();
$theme = new theme();
$theme->load('theme_kit');
$theme->import_sample($website);
}
echo json_encode(array('ok' => $lang['done']));
} else {
// user does not want to install the default theme
echo json_encode(array('ok' => $lang['not_selected']));
}
} catch (Exception $e) {
echo json_encode(array('error' => $e->getMessage()));
}
exit;
break;
case 'apache_htaccess':
try {
$nvweb = dirname($_SERVER['REQUEST_URI']) . NAVIGATE_FOLDER . '/web/nvweb.php';
$nvweb = str_replace('//', '/', $nvweb);
$data = array();
$data[] = 'Options +FollowSymLinks';
$data[] = 'Options -Indexes';
$data[] = 'RewriteEngine On';
$data[] = 'RewriteBase /';
$data[] = 'RewriteCond %{REQUEST_FILENAME} !-f';
$data[] = 'RewriteCond %{REQUEST_FILENAME} !-d';
$data[] = 'RewriteRule ^(.+) ' . $nvweb . '?route=$1 [QSA]';
$data[] = 'RewriteRule ^$ ' . $nvweb . '?route=nv.empty [L,QSA]';
$ok = @file_put_contents(dirname(NAVIGATE_PATH) . '/.htaccess', implode("\n", $data));
if (!$ok) {
throw new Exception($lang['unexpected_error']);
}
echo json_encode('true');
} catch (Exception $e) {
echo json_encode(array('error' => $e->getMessage()));
}
exit;
break;
}
}
<?php
#cousrse addition
require_once "../classes/database.php";
require_once "../classes/retrival.php";
if (isset($_REQUEST['Submit'])) {
$c_name = $_REQUEST['name'];
$c_category = $_REQUEST['category'];
$c_level = $_REQUEST['level'];
$c_price = $_REQUEST['price'];
$db = new database();
$db->connect();
$rt = new retrieval();
$c_id = $rt->courses();
$query = "INSERT INTO course(course_id,c_name,c_category,c_level,c_price) VALUES('{$c_id}','{$c_name}','{$c_category}','{$c_level}','{$c_level}')";
if (empty($db->execute($query))) {
echo "0";
} else {
echo "1";
}
$db->disconnect();
} else {
echo "0";
}
<?php
require_once '../inc/bootstrap.php';
if (!DEBUG) {
die("DEBUG MODE MUST BE ENABLED. THIS TEST WILL DESTROY DATA!!");
}
$db = new database();
$db->query("TRUNCATE tbl_user; TRUNCATE tbl_session; TRUNCATE tbl_log;");
try {
$db->execute();
} catch (Exception $e) {
return array("Database error: " . $e->getMessage(), 1);
}
$user = new user();
$json = $user->register('First User', '*****@*****.**', 'test', 'test');
//Pass, activate, make admin
var_dump($user);
$user = '';
$user = new user();
$json .= $user->register('Duplicate Email', 'test@test', 'test', 'test');
//Fail
var_dump($user);
$user = '';
$user = new user();
$json .= $user->register('Password Mismatch', '*****@*****.**', 'test', 'tset');
//Fail
var_dump($user);
$user = '';
$user = new user();
$json .= $user->register('Second User', '*****@*****.**', 'test', 'test');
//Pass
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(">", ">", str_replace("<", "<", $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);
}
}
function multiExe($sql)
{
if (!$this->connection->multi_query($sql)) {
echo "Multi query failed: (" . $this->connection->errno . ") " . $this->connection->error;
}
}
function getError()
{
return mysqli_error($this->connection);
}
}
// require("../resources/config.php");
/* create the object*/
$data = new database("");
$sql = "CREATE DATABASE IF NOT EXISTS courseBuilder";
$data->execute($sql);
$data = new database("courseBuilder");
echo "Clean old data<br>";
$sql = "DROP TABLE IF EXISTS CourseCompleted;\n\t\t\tDROP TABLE IF EXISTS Prerequisite;\n\t\t\tDROP TABLE IF EXISTS SpePrereq;\n\t\t\tDROP TABLE IF EXISTS Classes;\n\t\t\tDROP TABLE IF EXISTS Students;\n\t\t\tDROP TABLE IF EXISTS ProgramsRequirement;\n\t\t\tDROP TABLE IF EXISTS Electives;\n\t\t\tDROP TABLE IF EXISTS Courses;";
// $data->multiExe($sql);
// if (!$data->connection->multi_query($sql)) {
// echo "Drop table failed: (" . $data->connection->errno . ") " . $data->connection->error;
// }
echo "<br>";
echo "importing data";
if (!$data->connection->multi_query($courseBuilderDataSql)) {
echo "Drop table failed: (" . $data->connection->errno . ") " . $data->connection->error;
}
echo "<br>";
echo "done";
