events.php
<?php
// prepare parameters to make a connection to our database...
// replace with the appropriate host address
$host = "172.16.118.56";
// replace mysql with the appropriate database type:
// mysql, postgres7, ibase, mssql, borland_ibase, firebird, mssqlpo, maxsql,
// oci8, oci805, oci8po, postgres, oracle, postgres64, sybase
$dbserver = 'mysql';
// replace with the appropriate username
$user = "isltest";
// replace with the appropriate password
$password = "isltest";
// replace with the appropriate database
$database = "isltest";
include_once 'adodb/adodb-errorpear.inc.php';
include_once 'adodb/adodb.inc.php';
include_once 'adodb/tohtml.inc.php';
$db = ADONewConnection($dbserver);
@$db->Connect($host, $user, $password, $database);
// *** ISL Pronto event handler ***
if ("ISL_PRONTO" == $_POST["MODULE"]) {
// define some temp variables that are obtained from POST
$pronto_sessionid = $_POST['CHATID'];
$pronto_timestamp = $_POST['TIMESTAMP'];
$pronto_serverid = $_POST['SERVERID'];
$pronto_createdtime = $_POST['createdTime'];
$pronto_event = $_POST['EVENT'];
// put all key-value pairs into a string {{key1,value1},...,{keyn,valuen}}
$array = $_POST;
$fields = "{";
foreach($array as $key => $value) {
$fields .= "{".$key.",".$value."}";
}
$fields .="}";
$inserted_date = date("Y-m-d");
$result = $db->Execute("INSERT INTO sessions_events (cpg_session_ident, event_type_code, fields, inserted_date, event_date) VALUES ('$pronto_sessionid', '$pronto_event', '$fields', '$inserted_date', '$pronto_timestamp')");
if (!is_object($result)) {
$e = ADODB_Pear_Error();
}
$array = $_POST;
foreach($array as $key => $value) {
$result = $db->Execute("INSERT INTO sessions_rawdata (cpg_session_ident, event_name, key_name, key_value) VALUES ('$pronto_sessionid', '$pronto_event', '$key', '$value')");
if (!is_object($result)) {
$e = ADODB_Pear_Error();
}
}
// In the next section we check if there exists an entry with the current CHATID.
// If yes, we get its current status code; otherwise, we create a new entry.
$result = $db->Execute("SELECT status_code FROM pronto_sessions WHERE cpg_session_ident='$pronto_sessionid'");
if (!is_object($result)) {
$e = ADODB_Pear_Error();
}
$status = $result->fields[0];
if (!$status) {
// *** SQL command to execute ***
$result = $db->Execute("INSERT INTO pronto_sessions (cpg_server_id, cpg_session_ident, status_code) VALUES ('$pronto_serverid', '$pronto_sessionid', 'NEW')");
if (!is_object($result)) {
$e = ADODB_Pear_Error();
}
$status = "NEW";
}
switch($pronto_event) {
case "START":
$pronto_location = $_POST['location'];
$pronto_customeremail = $_POST['email'];
$pronto_customername = $_POST['nick'];
$result = $db->Execute("UPDATE pronto_sessions SET cpg_server_id='$pronto_serverid', created_date='$pronto_createdtime', customer_location='$pronto_location', customer_email='$pronto_customeremail', customer_name='$pronto_customername', status_code='RUNNING' WHERE cpg_session_ident='$pronto_sessionid'");
if (!is_object($result)) {
$e = ADODB_Pear_Error();
}
break;
case "JOIN":
$pronto_issupporter = $_POST['supporter'];
if ($pronto_issupporter==1) {
// supporter join
$pronto_cpuserdomain = $_POST['OPERATOR_DOMAIN'];
$pronto_cpusername = $_POST['OPERATOR_USERNAME'];
$pronto_supporteremail = $_POST['email'];
$pronto_supportername = $_POST['nick'];
$pronto_supporterstatus = $_POST['status'];
$result = $db->Execute("UPDATE pronto_sessions SET cpg_user_domain_name='$pronto_cpuserdomain', cpg_user_name='$pronto_cpusername', supporter_email='$pronto_supporteremail', supporter_name='$pronto_supportername', supporter_status='$pronto_supporterstatus' WHERE cpg_session_ident='$pronto_sessionid'");
if (!is_object($result)) {
$e = ADODB_Pear_Error();
}
// is the supporter already in the list of supporters for this session?
$result = $db->Execute("SELECT id FROM pronto_supporters WHERE cpg_session_ident='$pronto_sessionid' AND supporter_name='$pronto_supportername' AND cpg_server_id='$pronto_serverid'");
$supporter_already_added = $result->fields[0];
if (!$supporter_already_added) {
// if not, add the supporter
$result = $db->Execute("INSERT INTO pronto_supporters (cpg_server_id, cpg_session_ident, cpg_user_domain_name, cpg_user_name, supporter_name, supporter_email) VALUES ('$pronto_serverid', '$pronto_sessionid', '$pronto_cpuserdomain', '$pronto_cpusername', '$pronto_supportername', '$pronto_supporteremail')");
if (!is_object($result)) {
$e = ADODB_Pear_Error();
}
}
} else {
// customer join
$pronto_customeremail = $_POST['email'];
$pronto_customername = $_POST['nick'];
// is the customer already in the list of customers for this session?
$result = $db->Execute("SELECT id FROM pronto_customers WHERE cpg_session_ident='$pronto_sessionid' AND customer_name='$pronto_customername' AND cpg_server_id='$pronto_serverid'");
$customer_already_added = $result->fields[0];
if (!$customer_already_added) {
// if not, add the customer
$result = $db->Execute("INSERT INTO pronto_customers (cpg_server_id, cpg_session_ident, customer_name, customer_email) VALUES ('$pronto_serverid', '$pronto_sessionid', '$pronto_customername', '$pronto_customeremail')");
if (!is_object($result)) {
$e = ADODB_Pear_Error();
}
}
}
break;
case "LEAVE":
$pronto_issupporter = $_POST['supporter'];
if ($pronto_issupporter==1) {
// supporter leave
} else {
// customer leave
}
break;
case "STOP":
$result = $db->Execute("UPDATE pronto_sessions SET status_code='FINISHED', stop_time='$pronto_timestamp' WHERE cpg_session_ident='$pronto_sessionid'");
if (!is_object($result)) {
$e = ADODB_Pear_Error();
}
break;
case "MESSAGE":
$pronto_msgsatisfied = $_POST['MSGDATA_satisfied'];
$pronto_msgcomment = $_POST['MSGDATA_comment'];
$result = $db->Execute("UPDATE pronto_sessions SET customer_satisfied='$pronto_msgsatisfied', customer_comment='$pronto_msgcomment' WHERE cpg_session_ident='$pronto_sessionid'");
if (!is_object($result)) {
$e = ADODB_Pear_Error();
}
break;
}
}
// close the database connection
@$db->Close();
// Reply needed for ISL Conference Proxy
echo "<nomessage/>";
?>
pronto_viewer.php
<?php
// load Smarty library
require('Smarty.class.php');
// set Smarty parameters
$smarty = new Smarty;
$smarty->template_dir = 'C:/xampp/htdocs/smarty/templates';
$smarty->config_dir = 'C:/xampp/htdocs/smarty/config';
$smarty->cache_dir = 'C:/xampp/smarty/cache';
$smarty->compile_dir = 'C:/xampp/smarty/templates_c';
// IMPORTANT: set force_compile to false for non-development use!
$smarty->force_compile = true;
$smarty->assign('process_tables',0);
if (isset($_GET['submit'])) {
//var_dump($_GET);
//echo '<br><br>';
// prepare parameters to make a connection to our database...
// replace with the appropriate host address
$host = "172.16.118.56";
// replace mysql with the appropriate database type:
// mysql, postgres7, ibase, mssql, borland_ibase, firebird, mssqlpo, maxsql,
// oci8, oci805, oci8po, postgres, oracle, postgres64, sybase
$dbserver = 'mysql';
// replace with the appropriate username
$user = "isltest";
// replace with the appropriate password
$password = "isltest";
// replace with the appropriate database
$database = "isltest";
include_once 'adodb/adodb-errorpear.inc.php';
include_once 'adodb/adodb.inc.php';
include_once 'adodb/tohtml.inc.php';
$db = ADONewConnection($dbserver);
$db->SetFetchMode(ADODB_FETCH_ASSOC);
@$db->Connect($host, $user, $password, $database);
// prepare conditions that are going to be used in the SELECT query
$query_conditions = array();
if (!empty($_GET['from_year']) && !empty($_GET['from_month']) && !empty($_GET['from_day'])) {
$from_timestamp = mktime($_GET['from_hour'],$_GET['from_minute'],00,$_GET['from_month'],$_GET['from_day'],$_GET['from_year']);
$query_conditions[] = "created_date>=".$from_timestamp;
}
if (!empty($_GET['to_year']) && !empty($_GET['to_month']) && !empty($_GET['to_day'])) {
$to_timestamp = mktime($_GET['to_hour'],$_GET['to_minute'],00,$_GET['to_month'],$_GET['to_day'],$_GET['to_year']);
$query_conditions[] = "created_date<=".$to_timestamp;
}
if (!empty($_GET['domain'])) {
$query_conditions[] = "cpg_user_domain_name='".$_GET['domain']."'";
}
if (!empty($_GET['user'])) {
$query_conditions[] = "cpg_user_name='".$_GET['user']."'";
}
$query_conditions_string = "";
$query_conditions_string_old = "";
foreach($query_conditions as $key => $value) {
$query_conditions_string_old = $query_conditions_string;
$query_conditions_string .= $value." AND ";
$query_conditions_string_old .= $value;
}
$query_conditions_string = $query_conditions_string_old;
echo '<b>query_conditions_string:</b><br>';
echo $query_conditions_string;
echo '<br><br>';
if (empty($query_conditions_string)) {
$result = $db->Execute("SELECT cpg_session_ident AS 'id', created_date AS 'created', session_code AS 'code', cpg_user_domain_name AS 'domain', cpg_user_name AS 'user', status_code AS 'status', start_time AS 'start time', total_connection_time AS 'length', total_transferred_bytes AS 'bytes', cpg_server_id AS 'server', desk_version AS 'desk ver', desk_ip AS 'desk ip', client_version AS 'client ver', client_ip AS 'client ip' FROM light_sessions");
} else {
$result = $db->Execute("SELECT cpg_session_ident AS 'id', created_date AS 'created', session_code AS 'code', cpg_user_domain_name AS 'domain', cpg_user_name AS 'user', status_code AS 'status', start_time AS 'start time', total_connection_time AS 'length', total_transferred_bytes AS 'bytes', cpg_server_id AS 'server', desk_version AS 'desk ver', desk_ip AS 'desk ip', client_version AS 'client ver', client_ip AS 'client ip' FROM light_sessions WHERE $query_conditions_string");
}
if (!is_object($result)) {
$e = ADODB_Pear_Error();
}
$result_array = $result->GetArray();
// handling the case when there are no results
if ($result_array) {
$smarty->assign('show_results',1);
} else {
$smarty->assign('show_results',0);
}
// store all column names in an array
$field_names = array();
for ($i = 0, $max = $result->FieldCount(); $i < $max; $i++) {
$cur_field = $result->FetchField($i);
$field_names[$i] = $cur_field->name;
}
// store all column types in an array
$field_types = array();
for ($i = 0, $max = $result->FieldCount(); $i < $max; $i++) {
$cur_field = $result->FetchField($i);
$field_types[$i] = $result->MetaType($cur_field->type);
}
$smarty->assign('field_names',$field_names);
$smarty->assign('field_types',$field_types);
$smarty->assign('search_result',$result_array);
$smarty->assign('process_tables',1);
}
$smarty->assign('year',Date("Y"));
$smarty->assign('month',Date("m"));
$smarty->assign('day',Date("d"));
$smarty->assign('hour',date("G"));
$smarty->assign('minute',date("i"));
$smarty->display('light_show.tpl');
?>