/**
* function get_conversation_to_display()
* This function gets the conversation from the db to display/return to the user
* @link http://blog.program-o.com/?p=1223
* @param array $convoArr - the conversation array
* @return array $orderedRows - a list of conversation line
**/
function get_conversation_to_display($convoArr)
{
global $dbConn, $dbn, $bot_name, $unknown_user;
$user_id = $convoArr['conversation']['user_id'];
$bot_id = $convoArr['conversation']['bot_id'];
$user_name = $convoArr['conversation']['user_name'];
$user_name = !empty($user_name) ? $user_name : $unknown_user;
$convoArr['conversation']['bot_name'] = $bot_name;
if (empty($bot_name)) {
$sql = "select `bot_name` from `bots` where `bot_id` = {$bot_id} limit 1;";
$row = db_fetch($sql, null, __FILE__, __FUNCTION__, __LINE__);
$bot_name = $row['bot_name'];
}
if ($convoArr['conversation']['conversation_lines'] != 0) {
$limit = " LIMIT " . $convoArr['conversation']['conversation_lines'];
} else {
$limit = "";
}
$sql = "SELECT * FROM `{$dbn}`.`conversation_log`\n WHERE\n `user_id` = '" . $convoArr['conversation']['user_id'] . "'\n AND `bot_id` = '" . $convoArr['conversation']['bot_id'] . "'\n AND `convo_id` = '" . $convoArr['conversation']['convo_id'] . "'\n ORDER BY id DESC {$limit} ";
runDebug(__FILE__, __FUNCTION__, __LINE__, "get_conversation SQL: {$sql}", 3);
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
if (count($result) > 0) {
foreach ($result as $row) {
$allrows[] = $row;
}
$orderedRows = array_reverse($allrows, false);
} else {
$orderedRows = array('id' => NULL, 'input' => "", 'response' => "", 'user_id' => $convoArr['conversation']['user_id'], 'bot_id' => $convoArr['conversation']['bot_id'], 'timestamp' => "");
}
runDebug(__FILE__, __FUNCTION__, __LINE__, "Found '" . count($result) . "' lines of conversation", 2);
return $orderedRows;
}
/**
* function intialise_censor()
* A function to build session array containing the words from the censor list in the db
**/
function initialise_censor($bot_id)
{
global $dbConn, $dbn;
//set in global config file
$_SESSION['pgo_word_censor'] = array();
$sql = "SELECT * FROM `{$dbn}`.`wordcensor` WHERE `bot_exclude` NOT LIKE '%[{$bot_id}]%'";
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
foreach ($result as $row) {
$index = $row['word_to_censor'];
$value = $row['replace_with'];
$_SESSION['pgo_word_censor'][$index] = $value;
}
}
/**
* Function buildSelOpts
*
*
* @return string
*/
function buildSelOpts()
{
global $bot_id, $bot_name, $msg;
$sql = "SELECT DISTINCT filename FROM `aiml` where `bot_id` = {$bot_id} order by `filename`;";
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
if (count($result) == 0) {
$msg = "The chatbot '{$bot_name}' has no AIML categories to clear.";
return false;
}
$out = " <!-- Start Selectbox Options -->\n";
$optionTemplate = " <option value=\"[val]\">[val]</option>\n";
foreach ($result as $row) {
if (empty($row['filename'])) {
$curOption = " <option value=\"\">{No Filename entry}</option>\n";
} else {
$curOption = str_replace('[val]', $row['filename'], $optionTemplate);
}
$out .= $curOption;
}
$out .= " <!-- End Selectbox Options -->\n";
return $out;
}
/**
* function get_user_id()
* A function to get the user id
* @param array $convoArr - the current state of the conversation array
* @return array $convoArr
**/
function get_user_id($convoArr)
{
//db globals
global $dbConn, $dbn, $unknown_user;
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Getting user ID.', 2);
//get undefined defaults from the db
$sql = "SELECT * FROM `{$dbn}`.`users` WHERE `session_id` = '" . $convoArr['conversation']['convo_id'] . "' limit 1";
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
$count = count($result);
if ($count > 0) {
$row = $result[0];
$convoArr['conversation']['user_id'] = $row['id'];
// add user name, if set
$convoArr['conversation']['user_name'] = !empty($convoArr['client_properties']['name']) ? $convoArr['client_properties']['name'] : !empty($row['user_name']) ? $row['user_name'] : $unknown_user;
$convoArr['client_properties']['name'] = $convoArr['conversation']['user_name'];
$msg = "existing";
} else {
$convoArr = intisaliseUser($convoArr);
$msg = "new";
}
runDebug(__FILE__, __FUNCTION__, __LINE__, "Getting {$msg} user id:" . $convoArr['conversation']['user_id'], 4);
runDebug(__FILE__, __FUNCTION__, __LINE__, "get_user_id SQL: {$sql}", 3);
return $convoArr;
}
/**
* Function runSearch
*
*
* @return mixed|string
*/
function runSearch()
{
global $bot_id, $form_vars, $dbConn, $group;
$groupSize = 10;
$limit = ($group - 1) * $groupSize;
$limit = $limit < 0 ? 0 : $limit;
$search_fields = array('topic', 'filename', 'pattern', 'template', 'thatpattern');
$searchTerms = array();
$searchArguments = array($bot_id);
$searchParams = array($bot_id);
foreach ($search_fields as $index) {
if (!empty($form_vars[$index])) {
$searchParams[] = "%{$form_vars[$index]}%";
array_push($searchArguments, "%" . trim($form_vars[$index]) . "%");
array_push($searchTerms, "{$index} like ?");
}
}
$searchTerms = !empty($searchTerms) ? implode(" AND ", $searchTerms) : "TRUE";
$countSQL = "SELECT count(id) FROM `aiml` WHERE `bot_id` = ? AND ({$searchTerms})";
$count = db_fetch($countSQL, $searchParams, __FILE__, __FUNCTION__, __LINE__);
$total = $count['count(id)'];
$limit = $limit >= $total ? $total - 1 - ($total - 1) % $groupSize : $limit;
$order = isset($form_vars['sort']) ? $form_vars['sort'] . " " . $form_vars['sortOrder'] : "id";
$sql = "SELECT id, topic, filename, pattern, template, thatpattern FROM `aiml` " . "WHERE `bot_id` = ? AND ({$searchTerms}) order by {$order} limit {$limit}, {$groupSize};";
$result = db_fetchAll($sql, $searchParams, __FILE__, __FUNCTION__, __LINE__);
$out = array("results" => $result, "total_records" => $total, "start_index" => 0, "page" => $limit / $groupSize + 1, "page_size" => $groupSize);
return json_encode($out);
}
/**
* function run_srai()
* This function controls the SRAI recursion calls
* @param array $convoArr - a reference to the existing conversation array
* @param string $now_look_for_this - the text to search for
* @return string $srai_parsed_template - the result of the search
**/
function run_srai(&$convoArr, $now_look_for_this)
{
global $srai_iterations, $error_response, $dbConn, $dbn;
$bot_parent_id = $convoArr['conversation']['bot_parent_id'];
$bot_id = $convoArr['conversation']['bot_id'];
if ($bot_parent_id != 0 and $bot_parent_id != $bot_id) {
$sql_bot_select = " (bot_id = '{$bot_id}' OR bot_id = '{$bot_parent_id}') ";
} else {
$sql_bot_select = " bot_id = '{$bot_id}' ";
}
$bot_id = $convoArr['conversation']['bot_id'];
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Checking for entries in the srai_lookup table.', 2);
runDebug(__FILE__, __FUNCTION__, __LINE__, "google bot_id = {$bot_id}", 2);
$lookingfor = $convoArr['aiml']['lookingfor'];
//$now_look_for_this = strtoupper($now_look_for_this);
$sql = "select `template_id` from `{$dbn}`.`srai_lookup` where `pattern` = '{$now_look_for_this}' and {$sql_bot_select};";
runDebug(__FILE__, __FUNCTION__, __LINE__, "lookup SQL = {$sql}", 2);
$row = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Result = ' . print_r($row, true), 2);
$num_rows = count($row);
if ($num_rows > 0) {
runDebug(__FILE__, __FUNCTION__, __LINE__, "Found {$num_rows} rows in lookup table: " . print_r($row, true), 2);
$template_id = $row[0]['template_id'];
runDebug(__FILE__, __FUNCTION__, __LINE__, "Found a matching entry in the lookup table. Using ID# {$template_id}.", 2);
$sql = "select `template` from `{$dbn}`.`aiml` where `id` = '{$template_id}';";
$row = db_fetch($sql, null, __FILE__, __FUNCTION__, __LINE__);
runDebug(__FILE__, __FUNCTION__, __LINE__, "Row found in AIML for ID {$template_id}: " . print_r($row, true), 2);
if (!empty($row)) {
$template = add_text_tags($row['template']);
try {
$sraiTemplate = new SimpleXMLElement($template, LIBXML_NOCDATA);
} catch (exception $e) {
trigger_error("There was a problem parsing the SRAI template as XML. Template value:\n{$template}", E_USER_WARNING);
$sraiTemplate = new SimpleXMLElement("<text>{$error_response}</text>", LIBXML_NOCDATA);
}
$responseArray = parseTemplateRecursive($convoArr, $sraiTemplate);
$response = implode_recursive(' ', $responseArray, __FILE__, __FUNCTION__, __LINE__);
runDebug(__FILE__, __FUNCTION__, __LINE__, "Returning results from stored srai lookup.", 2);
return $response;
}
} else {
runDebug(__FILE__, __FUNCTION__, __LINE__, 'No match found in lookup table.', 2);
}
runDebug(__FILE__, __FUNCTION__, __LINE__, "Nothing found in the SRAI lookup table. Looking for a direct pattern match for '{$now_look_for_this}'.", 2);
$sql = "SELECT `id`, `pattern`, `thatpattern`, `topic` FROM `{$dbn}`.`aiml` where `pattern` = :pattern and {$sql_bot_select} order by `id` asc;";
$result = db_fetchAll($sql, array(':pattern' => $now_look_for_this), __FILE__, __FUNCTION__, __LINE__);
$num_rows = count($result);
runDebug(__FILE__, __FUNCTION__, __LINE__, "Found {$num_rows} potential responses.", 2);
$allrows = array();
$i = 0;
if ($num_rows > 0) {
$tmp_rows = number_format($num_rows);
runDebug(__FILE__, __FUNCTION__, __LINE__, "FOUND: ({$tmp_rows}) potential AIML matches", 2);
//loop through results
foreach ($result as $row) {
$row['aiml_id'] = $row['id'];
$row['score'] = 0;
$row['track_score'] = '';
$allrows[] = $row;
$mu = memory_get_usage(true);
if ($mu >= MEM_TRIGGER) {
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Current operation exceeds memory threshold. Aborting data retrieval.', 0);
break;
}
}
} else {
runDebug(__FILE__, __FUNCTION__, __LINE__, "Error: FOUND NO AIML matches in DB", 1);
$allrows[$i]['aiml_id'] = "-1";
$allrows[$i]['bot_id'] = "-1";
$allrows[$i]['pattern'] = "no results";
$allrows[$i]['thatpattern'] = '';
$allrows[$i]['topic'] = '';
}
//unset all irrelvant matches
$allrows = unset_all_bad_pattern_matches($convoArr, $allrows, $now_look_for_this);
$arCount = count($allrows);
if ($arCount == 0) {
runDebug(__FILE__, __FUNCTION__, __LINE__, "Error: FOUND NO AIML matches in DB", 1);
$allrows[$i]['aiml_id'] = "-1";
$allrows[$i]['bot_id'] = "-1";
$allrows[$i]['pattern'] = "no results";
$allrows[$i]['thatpattern'] = '';
$allrows[$i]['topic'] = '';
}
//score the relevant matches
$allrows = score_matches($convoArr, $allrows, $now_look_for_this);
//get the highest
$allrows = get_highest_scoring_row($convoArr, $allrows, $lookingfor);
if (isset($allrows['aiml_id']) && $allrows['aiml_id'] > 0) {
$aiml_id = $allrows['aiml_id'];
$pattern = $allrows['pattern'];
$sql = "select `template` from `{$dbn}`.`aiml` where `id` = :id limit 1;";
$row = db_fetch($sql, array(':id' => $aiml_id), __FILE__, __FUNCTION__, __LINE__);
$template = add_text_tags($row['template']);
try {
$sraiTemplate = new SimpleXMLElement($template, LIBXML_NOCDATA);
} catch (exception $e) {
trigger_error("There was a problem parsing the SRAI template as XML. Template value:\n{$template}", E_USER_WARNING);
$sraiTemplate = new SimpleXMLElement("<text>{$error_response}</text>", LIBXML_NOCDATA);
}
$responseArray = parseTemplateRecursive($convoArr, $sraiTemplate);
$response = implode_recursive(' ', $responseArray, __FILE__, __FUNCTION__, __LINE__);
try {
// code to try here
$sql = "insert into `{$dbn}`.`srai_lookup` (`id`, `bot_id`, `pattern`, `template_id`) values(null, :bot_id, :pattern, :template_id);";
$sth = $dbConn->prepare($sql);
$sth->bindValue(':bot_id', $bot_id);
$sth->bindValue(':pattern', $pattern);
$sth->bindValue(':template_id', $aiml_id);
$sth->execute();
$affectedRows = $sth->rowCount();
if ($affectedRows > 0) {
runDebug(__FILE__, __FUNCTION__, __LINE__, "Successfully inserted entry for '{$pattern}'.", 1);
}
} catch (Exception $e) {
//something to handle the problem here, usually involving $e->getMessage()
$err = $e->getMessage();
runDebug(__FILE__, __FUNCTION__, __LINE__, "Unable to insert entry for '{$pattern}'! Error = {$err}.", 1);
runDebug(__FILE__, __FUNCTION__, __LINE__, "SQL = {$sql}", 1);
}
runDebug(__FILE__, __FUNCTION__, __LINE__, "Returning results from stored srai lookup.", 2);
return $response;
}
runDebug(__FILE__, __FUNCTION__, __LINE__, "Running SRAI {$srai_iterations} on {$now_look_for_this}", 3);
runDebug(__FILE__, __FUNCTION__, __LINE__, $convoArr['aiml']['html_template'], 4);
//number of srai iterations - will stop recursion if it is over 10
$srai_iterations++;
runDebug(__FILE__, __FUNCTION__, __LINE__, "Incrementing srai iterations to {$srai_iterations}", 4);
if ($srai_iterations > 10) {
runDebug(__FILE__, __FUNCTION__, __LINE__, "ERROR - Too much recursion breaking out", 1);
$convoArr['aiml']['parsed_template'] = $error_response;
return $error_response;
}
//$tmp_convoArr = array();
$tmp_convoArr = $convoArr;
if (!isset($tmp_convoArr['stack'])) {
$tmp_convoArr = load_blank_stack($tmp_convoArr);
}
if (!isset($tmp_convoArr['topic'])) {
$tmp_convoArr['topic'] = array();
$tmp_convoArr['topic'][1] = '';
}
$tmp_convoArr['aiml'] = array();
$tmp_convoArr['that'][1][1] = "";
//added
$tmp_convoArr['aiml']['parsed_template'] = "";
$tmp_convoArr['aiml']['lookingfor'] = $now_look_for_this;
$tmp_convoArr['aiml']['pattern'] = $now_look_for_this;
$tmp_convoArr['aiml']['thatpattern'] = $convoArr['aiml']['thatpattern'];
$tmp_convoArr = get_aiml_to_parse($tmp_convoArr);
$tmp_convoArr = parse_matched_aiml($tmp_convoArr, "srai");
$srai_parsed_template = $tmp_convoArr['aiml']['parsed_template'];
runDebug(__FILE__, __FUNCTION__, __LINE__, "SRAI Found. Returning '{$srai_parsed_template}'", 2);
$convoArr['client_properties'] = $tmp_convoArr['client_properties'];
$convoArr['topic'] = $tmp_convoArr['topic'];
$convoArr['stack'] = $tmp_convoArr['stack'];
$srai_iterations--;
runDebug(__FILE__, __FUNCTION__, __LINE__, "Decrementing srai iterations to {$srai_iterations}", 4);
return $srai_parsed_template . " ";
}
/**
* function load_that(()
* A function to load the previous bot responses into the convoArr['that'] array
*
* @link http://blog.program-o.com/?p=1283
* @param array $convoArr - the current state of the conversation array
* @return array $convoArr (updated)
*/
function load_that($convoArr)
{
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Loading the THAT array.', 2);
global $dbConn, $dbn, $remember_up_to, $bot_id;
$remember_up_to = !empty($convoArr['conversation']['remember_up_to']) ? $convoArr['conversation']['remember_up_to'] : $remember_up_to;
$user_id = $convoArr['conversation']['user_id'];
$bot_id = !empty($convoArr['conversation']['bot_id']) ? $convoArr['conversation']['bot_id'] : $bot_id;
$limit = $remember_up_to;
$sql = "select `input`, `response` from `{$dbn}`.`conversation_log` where `user_id` = {$user_id} and `bot_id` = {$bot_id} order by `id` desc limit {$limit};";
// desc
runDebug(__FILE__, __FUNCTION__, __LINE__, "Getting conversation log entries for the current user. SQL:\n{$sql}", 3);
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
if ($result) {
$tmpThatRows = array();
$tmpInputRows = array();
$tmpThat = array();
$tmpInput = array();
$puncuation = array(',', '?', ';', '!');
foreach ($result as $row) {
$tmpThatRows[] = $row['response'];
$tmpInputRows[] = $row['input'];
}
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Inputs returned:' . print_r($tmpInputRows, true), 1);
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Loading previous responses into the ~THAT~ array.', 4);
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Responses returned:' . print_r($tmpThatRows, true), 1);
$tmpThatRows = array_reverse($tmpThatRows);
foreach ($tmpThatRows as $row) {
$row = str_replace($puncuation, '.', $row);
$tmpThat[] = explode('.', $row);
}
array_unshift($tmpThat, NULL);
unset($tmpThat[0]);
foreach ($tmpThat as $index => $value) {
$value = implode_recursive(' ', $value, __FILE__, __FUNCTION__, __LINE__);
$value = clean_that($value, __FILE__, __FUNCTION__, __LINE__);
$convoArr = push_on_front_convoArr('that', $value, $convoArr);
}
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Loading previous user inputs into the ~INPUT~ array.', 4);
$tmpInputRows = array_reverse($tmpInputRows);
foreach ($tmpInputRows as $row) {
$row = str_replace($puncuation, '.', $row);
$tmpInput[] = explode('.', $row);
}
array_unshift($tmpThat, NULL);
unset($tmpThat[0]);
foreach ($tmpInput as $index => $value) {
$value = implode_recursive(' ', $value, __FILE__, __FUNCTION__, __LINE__);
$value = clean_that($value, __FILE__, __FUNCTION__, __LINE__);
$convoArr = push_on_front_convoArr('input', $value, $convoArr);
}
} else {
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Couldn\'t find any previous inputs or responses.', 4);
}
return $convoArr;
}
/**
* Function getBotList
*
*
* @return string
*/
function getBotList()
{
global $dbConn, $dbn, $bot_id;
$botOptions = '';
$sql = 'SELECT `bot_name`, `bot_id` FROM `bots` order by `bot_id`;';
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
foreach ($result as $row) {
$bn = $row['bot_name'];
$bi = $row['bot_id'];
$sel = $bot_id == $bi ? ' selected="selected"' : '';
$botOptions .= " <option{$sel} value=\"{$bi}\">{$bn}</option>\n";
}
return $botOptions;
}
/**
* function load_spelling_list
* Gets all missspelled words and their corrections from the DB, loading them into a session variable.
*
* @internal param $ (none)
* @return void (void)
*/
function load_spelling_list()
{
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Loading the spellcheck list from the DB.', 2);
global $dbConn, $dbn;
$_SESSION['spellcheck'] = array();
$sql = "SELECT `missspelling`, `correction` FROM `{$dbn}`.`spellcheck`;";
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
$num_rows = count($result);
if ($num_rows > 0) {
foreach ($result as $row) {
$missspelling = IS_MB_ENABLED ? mb_strtolower($row['missspelling']) : strtolower($row['missspelling']);
$correction = $row['correction'];
$_SESSION['spellcheck'][$missspelling] = $correction;
}
}
}
/**
* Returns an HTML form for selecting a chatbot from the database
*
*
* @return string
*/
function getChangeList()
{
global $dbConn, $template;
$bot_id = isset($_SESSION['poadmin']['bot_id']) ? $_SESSION['poadmin']['bot_id'] : 0;
$botId = $bot_id;
$sql = "SELECT * FROM `bots` ORDER BY bot_name";
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
$options = ' <option value="new">Add New Bot</option>' . "\n";
foreach ($result as $row) {
$options .= "<!-- bot ID = {$row['bot_id']}, {$botId} -->\n";
if ($botId == $row['bot_id']) {
$sel = ' selected="selected"';
} else {
$sel = '';
}
$bot_id = $row['bot_id'];
$bot_name = $row['bot_name'];
$options .= " <option value=\"{$bot_id}\"{$sel}>{$bot_name}</option>\n";
}
$options = rtrim($options);
$form = $template->getSection('ChangeBot');
$form = str_replace('[options]', $options, $form);
return $form;
}
/**
* Function updateBot
*
*
* @return string
*/
function updateBot()
{
global $bot_id, $bot_name, $post_vars;
$msg = "";
if (!empty($post_vars['newEntryName'])) {
$newEntryNames = $post_vars['newEntryName'];
$newEntryValues = $post_vars['newEntryValue'];
$sql = "Insert into `botpersonality` (`id`, `bot_id`, `name`, `value`) values (null, {$bot_id}, :name, :value);";
$params = array();
foreach ($newEntryNames as $index => $key) {
$value = $newEntryValues[$index];
if (empty($value)) {
continue;
}
$params[] = array(':name' => $key, ':value' => $value);
}
$rowsAffected = db_write($sql, $params, true, __FILE__, __FUNCTION__, __LINE__);
if ($rowsAffected > 0) {
$msg = empty($msg) ? "Bot personality added. \n" : $msg;
} else {
$msg = 'Error updating bot personality.';
}
}
$sql = "SELECT * FROM `botpersonality` where `bot_id` = {$bot_id};";
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
$rows = array();
$insertParams = array();
$updateParams = array();
foreach ($result as $row) {
$name = $row['name'];
$value = $row['value'];
$rows[$name] = array('id' => $row['id'], 'value' => $value);
}
$insertSQL = "Insert into `botpersonality` (`id`, `bot_id`, `name`, `value`) values (null, {$bot_id}, :name, :value);";
$updateSQL = "update `botpersonality` set `value` = :value where `id` = :id;";
$exclude = array('bot_id', 'func', 'action', 'newEntryName', 'newEntryValue');
$values = '';
foreach ($post_vars as $key => $value) {
if (in_array($key, $exclude)) {
continue;
}
if (!isset($rows[$key])) {
$insertParams[] = array(':name' => $key, ':value' => $value);
} else {
$oldValue = $rows[$key]['value'];
if ($value != $oldValue) {
$curId = $rows[$key]['id'];
$updateParams[] = array(':value' => $value, ':id' => $curId);
}
}
}
if (empty($insertParams) && empty($updateParams)) {
return 'No changes found.';
}
$affectedRows = !empty($updateParams) ? db_write($updateSQL, $updateParams, true, __FILE__, __FUNCTION__, __LINE__) : 0;
$affectedRows += !empty($updateParams) ? db_write($insertSQL, $insertParams, true, __FILE__, __FUNCTION__, __LINE__) : 0;
if ($affectedRows > 0) {
$msg = 'Bot Personality Updated.';
} else {
$msg = "Something went wrong! Affected rows = {$affectedRows}.";
}
return $msg;
}
/**
* Function getCheckboxes
*
*
* @return string
*/
function getCheckboxes()
{
global $bot_id, $bot_name, $msg;
$sql = "SELECT DISTINCT filename FROM `aiml` where `bot_id` = {$bot_id} order by `filename`;";
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
if (count($result) == 0) {
$msg = "The chatbot '{$bot_name}' has no AIML categories to download. Please select another bot.";
return false;
}
$out = "";
$checkboxTemplate = <<<endRow
<div class="cbCell">
<input id="[file_name_id]" name="filenames[]" type="checkbox" class="cbFiles" value="[file_name]">
<label for="[file_name_id]"> [file_name]</label>
</div>
endRow;
$rowCount = 0;
foreach ($result as $row) {
if (empty($row['filename'])) {
$row['filename'] = 'unnamed_AIML.aiml';
}
$file_name = $row['filename'];
$file_name_id = str_replace('.', '_', $file_name);
$curCheckbox = str_replace('[file_name]', $file_name, $checkboxTemplate);
$curCheckbox = str_replace('[file_name_id]', $file_name_id, $curCheckbox);
$out .= $curCheckbox;
$rowCount++;
}
return rtrim($out);
}
/**
* Function runSpellSearch
*
*
* @return string
*/
function runSpellSearch()
{
global $dbConn, $template, $post_vars;
$i = 0;
$search = trim($post_vars['search']);
$sql = "SELECT * FROM `spellcheck` WHERE `missspelling` LIKE '%{$search}%' OR `correction` LIKE '%{$search}%' LIMIT 50";
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
$htmltbl = '<table>
<thead>
<tr>
<th class="sortable">missspelling</th>
<th class="sortable">Correction</th>
<th>Action</th>
</tr>
</thead>
<tbody>';
foreach ($result as $row) {
$i++;
$misspell = strtoupper($row['missspelling']);
$correction = strtoupper($row['correction']);
$id = $row['id'];
$group = round($id / 50);
$action = "<a href=\"index.php?page=spellcheck&action=edit&id={$id}&group={$group}#{$id}\"><img src=\"images/edit.png\" border=0 width=\"15\" height=\"15\" alt=\"Edit this entry\" title=\"Edit this entry\" /></a>\n <a href=\"index.php?page=spellcheck&action=del&id={$id}&group={$group}#{$id}\" onclick=\"return confirm('Do you really want to delete this missspelling? You will not be able to undo this!')\";><img src=\"images/del.png\" border=0 width=\"15\" height=\"15\" alt=\"Edit this entry\" title=\"Edit this entry\" /></a>";
$htmltbl .= "<tr valign=top>\n <td>{$misspell}</td>\n <td>{$correction}</td>\n <td align=center>{$action}</td>\n </tr>";
}
$htmltbl .= "</tbody></table>";
if ($i >= 50) {
$msg = "Found more than 50 results for '<b>{$search}</b>', please refine your search further";
} elseif ($i == 0) {
$msg = "Found 0 results for '<b>{$search}</b>'. You can use the form below to add that entry.";
$htmltbl = "";
} else {
$msg = "Found {$i} results for '<b>{$search}</b>'";
}
$htmlresults = "<div id=\"pTitle\">{$msg}</div>" . $htmltbl;
return $htmlresults;
}
/**
* Parses the AIML <set> tag, storing it's value in the database
*
* @param array $convoArr
* @param SimpleXMLElement $element
* @param string $parentName
* @param int $level
* @return string
*/
function parse_set_tag(&$convoArr, $element, $parentName, $level)
{
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Parsing the SET tag.', 2);
global $dbConn, $dbn, $user_name, $remember_up_to;
$var_value = tag_to_string($convoArr, $element, $parentName, $level, 'element');
$bot_id = $convoArr['conversation']['bot_id'];
$user_id = $convoArr['conversation']['user_id'];
$var_name = (string) $element->attributes()->name;
$var_name = $var_name == '*' ? $convoArr['star'][1] : $var_name;
for ($n = 2; $n <= $remember_up_to; $n++) {
$var_name = $var_name == "*{$n}" ? $convoArr['star'][$n] : $var_name;
}
$vn_type = gettype($var_name);
runDebug(__FILE__, __FUNCTION__, __LINE__, "var_name = {$var_name} and is type: {$vn_type}", 4);
if ($var_name == 'name') {
$user_name = $var_value;
$escaped_var_value = $var_value;
$sql = "UPDATE `{$dbn}`.`users` set `user_name` = '{$escaped_var_value}' where `id` = {$user_id};";
runDebug(__FILE__, __FUNCTION__, __LINE__, "Updating user name in the DB. SQL:\n{$sql}", 3);
$sth = $dbConn->prepare($sql);
$sth->execute();
$numRows = $sth->rowCount();
$sql = "select `user_name` from `{$dbn}`.`users` where `id` = {$user_id} limit 1;";
runDebug(__FILE__, __FUNCTION__, __LINE__, "Checking the users table to see if the value has changed. - SQL:\n{$sql}", 3);
$row = db_fetch($sql, null, __FILE__, __FUNCTION__, __LINE__);
$rowCount = count($row);
if ($rowCount != 0) {
$tmp_name = $row['user_name'];
runDebug(__FILE__, __FUNCTION__, __LINE__, "The value for the user's name is {$tmp_name}.", 4);
}
} else {
$convoArr['client_properties'][$var_name] = $var_value;
}
$lc_var_name = IS_MB_ENABLED ? mb_strtolower($var_name) : strtolower($var_name);
if ($lc_var_name == 'topic') {
$convoArr['topic'][1] = $var_value;
}
$sql = "select `value` from `{$dbn}`.`client_properties` where `user_id` = {$user_id} and `bot_id` = {$bot_id} and `name` = '{$var_name}';";
runDebug(__FILE__, __FUNCTION__, __LINE__, "Checking the client_properties table for the value of {$var_name}. - SQL:\n{$sql}", 3);
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
$rowCount = count($result);
/** @noinspection PhpSillyAssignmentInspection */
$var_name = $var_name;
$var_name = str_replace("'", "\\'", $var_name);
/** @noinspection PhpSillyAssignmentInspection */
$var_value = $var_value;
$var_value = str_replace("'", "\\'", $var_value);
if ($rowCount == 0) {
$sql = "insert into `{$dbn}`.`client_properties` (`id`, `user_id`, `bot_id`, `name`, `value`)\n values (NULL, {$user_id}, {$bot_id}, '{$var_name}', '{$var_value}');";
runDebug(__FILE__, __FUNCTION__, __LINE__, "No value found for {$var_name}. Inserting {$var_value} into the table.", 4);
} else {
$sql = "update `{$dbn}`.`client_properties` set `value` = '{$var_value}' where `user_id` = {$user_id} and `bot_id` = {$bot_id} and `name` = '{$var_name}';";
runDebug(__FILE__, __FUNCTION__, __LINE__, "Value found for {$var_name}. Updating the table to {$var_value}.", 4);
}
runDebug(__FILE__, __FUNCTION__, __LINE__, "Saving to DB - SQL:\n{$sql}", 3);
$sth = $dbConn->prepare($sql);
$sth->execute();
$rowCount = $sth->rowCount();
$response = $var_value;
$convoArr['client_properties'][$var_name] = $var_value;
runDebug(__FILE__, __FUNCTION__, __LINE__, "Value for {$var_name} has ben set. Returning {$var_value}.", 4);
return $response;
}
/**
* function find_aiml_matches()
* This function builds the sql to use to get a match from the tbl
* @param array $convoArr - conversation array
* @return array $convoArr
**/
function find_aiml_matches($convoArr)
{
global $dbConn, $dbn, $error_response, $use_parent_bot;
runDebug(__FILE__, __FUNCTION__, __LINE__, "Finding the aiml matches from the DB", 4);
$i = 0;
//TODO convert to get_it
$bot_id = $convoArr['conversation']['bot_id'];
$bot_parent_id = $convoArr['conversation']['bot_parent_id'];
$default_aiml_pattern = $convoArr['conversation']['default_aiml_pattern'];
#$lookingfor = get_convo_var($convoArr,"aiml","lookingfor");
$convoArr['aiml']['lookingfor'] = str_replace(' ', ' ', $convoArr['aiml']['lookingfor']);
$lookingfor = trim(strtoupper($convoArr['aiml']['lookingfor']));
//get the first and last words of the cleaned user input
$lastInputWord = get_last_word($lookingfor);
$firstInputWord = get_first_word($lookingfor);
//get the stored topic
$storedtopic = get_topic($convoArr);
runDebug(__FILE__, __FUNCTION__, __LINE__, "Stored topic = '{$storedtopic}'", 4);
//get the cleaned user input
$lastthat = isset($convoArr['that'][1][1]) ? $convoArr['that'][1][1] : '';
//build like patterns
if ($lastthat != '') {
$thatPatternSQL = " OR " . make_like_pattern($lastthat, 'thatpattern');
$thatPatternSQL = rtrim($thatPatternSQL, ' OR');
} else {
$thatPattern = '';
$thatPatternSQL = '';
}
//get the word count
$word_count = wordsCount_inSentence($lookingfor);
if ($bot_parent_id != 0 and $bot_parent_id != $bot_id) {
$sql_bot_select = " (bot_id = '{$bot_id}' OR bot_id = '{$bot_parent_id}') ";
} else {
$sql_bot_select = " bot_id = '{$bot_id}' ";
}
if (!empty($storedtopic)) {
$topic_select = "AND (`topic`='' OR `topic`='{$storedtopic}')";
} else {
$topic_select = '';
}
if ($word_count == 1) {
//if there is one word do this
$sql = "SELECT `id`, `pattern`, `thatpattern`, `topic` FROM `{$dbn}`.`aiml` WHERE\n {$sql_bot_select} AND (\n `pattern` = '_' OR\n `pattern` = '*' OR\n `pattern` = '{$lookingfor}' OR\n `pattern` = '{$default_aiml_pattern}'\n {$thatPatternSQL}\n ) {$topic_select} order by `topic` desc, `pattern` asc, `thatpattern` asc,`id` asc;";
} else {
//otherwise do this
$sql_add = make_like_pattern($lookingfor, 'pattern');
$sql = "SELECT `id`, `bot_id`, `pattern`, `thatpattern`, `topic` FROM `{$dbn}`.`aiml` WHERE\n {$sql_bot_select} AND (\n `pattern` = '_' OR\n `pattern` = '*' OR\n `pattern` = '{$lookingfor}' OR {$sql_add} OR\n `pattern` = '{$default_aiml_pattern}'\n {$thatPatternSQL}\n ) {$topic_select}\n order by `topic` desc, `pattern` asc, `thatpattern` asc,`id` asc;";
}
runDebug(__FILE__, __FUNCTION__, __LINE__, "Core Match AIML sql: {$sql}", 3);
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
$num_rows = count($result);
if ($result && $num_rows > 0) {
$tmp_rows = number_format($num_rows);
runDebug(__FILE__, __FUNCTION__, __LINE__, "FOUND: ({$num_rows}) potential AIML matches", 2);
$tmp_content = date('H:i:s') . ": SQL:\n{$sql}\nRows = {$tmp_rows}\n\n";
//loop through results
foreach ($result as $row) {
$row['aiml_id'] = $row['id'];
$row['bot_id'] = $bot_id;
$row['score'] = 0;
$row['track_score'] = '';
$allrows[] = $row;
$mu = memory_get_usage(true);
if ($mu >= MEM_TRIGGER) {
runDebug(__FILE__, __FUNCTION__, __LINE__, 'Current operation exceeds memory threshold. Aborting data retrieval.', 0);
break;
}
}
} else {
runDebug(__FILE__, __FUNCTION__, __LINE__, "Error: FOUND NO AIML matches in DB", 1);
$allrows[$i]['aiml_id'] = "-1";
$allrows[$i]['bot_id'] = $bot_id;
$allrows[$i]['pattern'] = "no results";
$allrows[$i]['thatpattern'] = '';
$allrows[$i]['topic'] = '';
}
return $allrows;
}
/**
* Function getuserConvo
*
* * @param $id
* @param $showing
* @return mixed|string
*/
function getuserConvo($id, $showing)
{
global $dbConn;
$bot_name = isset($_SESSION['poadmin']['bot_name']) ? $_SESSION['poadmin']['bot_name'] : 'Bot';
$bot_id = isset($_SESSION['poadmin']['bot_id']) ? $_SESSION['poadmin']['bot_id'] : 0;
$nameList = getUserNames();
$user_name = $nameList[$id];
switch ($showing) {
case "today":
$sqladd = "AND DATE(`timestamp`) = '" . date('Y-m-d') . "'";
$title = "Today's ";
break;
case "previous week":
$lastweek = strtotime("-1 week");
$sqladd = "AND DATE(`timestamp`) >= '" . $lastweek . "'";
$title = "Last week's ";
break;
case "previous 2 weeks":
$lasttwoweek = strtotime("-2 week");
$sqladd = "AND DATE(`timestamp`) >= '" . $lasttwoweek . "'";
$title = "Last two week's ";
break;
case "previous month":
$lastmonth = strtotime("-1 month");
$sqladd = "AND DATE(`timestamp`) >= '" . $lastmonth . "'";
$title = "Last month's ";
break;
case "previous 6 months":
$lastsixmonth = strtotime("-6 month");
$sqladd = "AND DATE(`timestamp`) >= '" . $lastsixmonth . "'";
$title = "Last six month's ";
break;
case "past 12 months":
$lastyear = strtotime("-1 year");
$sqladd = "AND DATE(`timestamp`) >= '" . $lastyear . "'";
$title = "Last twelve month's ";
break;
case "all time":
$sql = "";
$title = "All ";
break;
default:
$sqladd = "";
$title = "Last ";
}
$lasttimestamp = "";
$i = 1;
//get undefined defaults from the db
$sql = "SELECT * FROM `conversation_log` WHERE `bot_id` = '{$bot_id}' AND `user_id` = {$id} {$sqladd} ORDER BY `id` ASC";
$list = "<hr><br/><h4>{$title} conversations for user: {$id}</h4>";
$list .= "<div class=\"convolist\">";
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
foreach ($result as $row) {
$thisdate = date("Y-m-d", strtotime($row['timestamp']));
if ($thisdate != $lasttimestamp) {
if ($i > 1) {
if ($showing == "last 20") {
break;
}
}
$date = date("Y-m-d");
$list .= "<hr><br/><h4>Conversation#{$i} {$thisdate}</h4>";
$i++;
}
$list .= "<br><span style=\"color:DARKBLUE;\">{$user_name}: " . $row['input'] . "</span>";
$list .= "<br><span style=\"color:GREEN;\">{$bot_name}: " . $row['response'] . "</span>";
$lasttimestamp = $thisdate;
}
$list .= "</div>";
$list = str_ireplace('<script', '<script', $list);
return $list;
}
/***************************************
* http://www.program-o.com
* PROGRAM O
* Version: 2.4.8
* FILE: getbots.php
* AUTHOR: Elizabeth Perreau and Dave Morton
* DATE: MAY 17TH 2014
* DETAILS: Searches the database for all active chatbots, returning a JSON encoded array of ID/name pairs
***************************************/
$time_start = microtime(true);
$script_start = $time_start;
$last_timestamp = $time_start;
$thisFile = __FILE__;
require_once "config/global_config.php";
//load shared files
require_once _LIB_PATH_ . 'PDO_functions.php';
include_once _LIB_PATH_ . "error_functions.php";
include_once _LIB_PATH_ . 'misc_functions.php';
ini_set('error_log', _LOG_PATH_ . 'getbots.error.log');
$dbConn = db_open();
$sql = "select `bot_id`, `bot_name` from `{$dbn}`.`bots`;";
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
$bots = array('bots' => array());
foreach ($result as $row) {
$bot_id = $row['bot_id'];
$bot_name = $row['bot_name'];
$bots['bots'][$bot_id] = $bot_name;
}
header('Content-type: application/json');
$out = json_encode($bots);
exit($out);
/**
* Function getAdminsOpts
*
*
* @return string
*/
function getAdminsOpts()
{
global $dbn, $dbConn;
$out = " <!-- Start List of Current Admin Accounts -->\n";
$optionTemplate = " <option value=\"[val]\">[key]</option>\n";
$sql = 'SELECT id, user_name FROM myprogramo order by user_name;';
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
foreach ($result as $row) {
$user_name = $row['user_name'];
$id = $row['id'];
$curOption = str_replace('[key]', $row['user_name'], $optionTemplate);
$curOption = str_replace('[val]', $row['id'], $curOption);
$out .= $curOption;
}
$out .= " <!-- End List of Current Admin Accounts -->\n";
return $out;
}
/**
* Function runWordCensorSearch
*
*
* @return string
*/
function runWordCensorSearch()
{
global $dbConn, $template, $request_vars;
$search = trim($request_vars['search']);
$sql = "SELECT * FROM `wordcensor` WHERE `word_to_censor` LIKE '%{$search}%' OR `replace_with` LIKE '%{$search}%' LIMIT 50";
$htmltbl = ' <table>
<thead>
<tr>
<th class="sortable">word_to_censor</th>
<th class="sortable">Correction</th>
<th>Action</th>
</tr>
</thead>
<tbody>';
$result = db_fetchAll($sql, null, __FILE__, __FUNCTION__, __LINE__);
$i = 0;
foreach ($result as $row) {
$i++;
$word_to_censor = IS_MB_ENABLED ? mb_strtoupper($row['word_to_censor']) : strtoupper($row['word_to_censor']);
$replace_with = IS_MB_ENABLED ? mb_strtoupper($row['replace_with']) : strtoupper($row['replace_with']);
$id = $row['censor_id'];
$group = round($id / 50);
$action = "<a href=\"index.php?page=wordcensor&action=edit&censor_id={$id}&group={$group}#{$id}\"><img src=\"images/edit.png\" border=0 width=\"15\" height=\"15\" alt=\"Edit this entry\" title=\"Edit this entry\" /></a>\n <a href=\"index.php?page=wordcensor&action=delete&censor_id={$id}&group={$group}#{$id}\" onclick=\"return confirm('Do you really want to delete this entry? You will not be able to undo this!')\";><img src=\"images/del.png\" border=0 width=\"15\" height=\"15\" alt=\"Delete this entry\" title=\"Delete this entry\" /></a>";
$htmltbl .= "<tr valign=top>\n <td>{$word_to_censor}</td>\n <td>{$replace_with}</td>\n <td align=center>{$action}</td>\n </tr>";
}
$htmltbl .= "</tbody></table>";
if ($i >= 50) {
$msg = "Found more than 50 results for '<b>{$search}</b>', please refine your search further";
} elseif ($i == 0) {
$msg = "Found 0 results for '<b>{$search}</b>'. You can use the form below to add that entry.";
$htmltbl = "";
} else {
$msg = "Found {$i} results for '<b>{$search}</b>'";
}
$htmlresults = "<div id=\"pTitle\">{$msg}</div>" . $htmltbl;
return $htmlresults;
}
