Hi all,
"the best tickets are the tickets not sent", so we thought about a way to reduce the number of tickets we get. As we have already many entries in the (very recommendable) osFAQ system, we decided to implement kind of a Google Suggest System, so people entering a ticket get links to some FAQ entries according to the subject they enter (starting after three typed characters), more or less (hopefully) forcing them to click on the links and read the FAQ instead of sending a ticket.
And as a pic say more than thousand words, it looks like that:
The lines in orange are the actual questions of the FAQ's and are linked directly to the respective FAQ entries.
Prerequisites:
- osFAQ by Tim Gall installed (can be found here http://osticket.com/forums/showthread.php?t=3012(http://osticket.com/forums/showthread.php?t=3012))
And here are the steps to accomplish the modification. Please note that i shuffled some files around to fit my own personal style and a couple of the instructions i give (i.e. creating a "js" folder) come according to that. And please note, that there is some german language in the code snippets, but you should find your way around nevertheless :)
And please note also, that i don't give any line numbers, as our system is already heavily modified and the line numbers wouldn't fit to a basic install of osTicket.
So please give it a try and tell me how it works. I hope i haven't forgot anything crucial. If yes, forgive me, i'll update that post as soon as possible then.
-----------------------------------------------------------------
Step 1:
As we want to search the columns "question" and "answer" of the osFAQ system and it should be able to search for several words, i decided to use MySQL's FullText search, so enable that via the mysql command
ALTER TABLE ost_faqs ADD FULLTEXT(question,answer);
and possibly followed by a
REPAIR TABLE ost_faqs QUICK;
to refresh the indices if you have already entries in the FAQ system. I had to do that from the console, as it did not work for me via PHPMyAdmin (might have been my fault as well).
-----------------------------------------------------------------
Step 2:
Create a folder called "js" in your osticket directory. Get a copy of jQuery (I'm using jquery-1.2.1.pack.js) and copy that into the newly created "js" folder. Now include that in /osticket/include/client/header.inc.php
<script type="text/javascript" src="./js/jquery-1.2.1.pack.js"></script>
-----------------------------------------------------------------
Step 3:
add the following Javascript and CSS definitions to the "head" area in /osticket/include/client/header.inc.php (feel free to move that into one of the existing javascript or CSS files, i was just too lazy to do that). If you don't like the colors of the suggest layer, here's the place to modify it.
<script type="text/javascript">
function getXMLHTTP() { //function to return the xml http object
var xmlhttp=false;
try{
xmlhttp=new XMLHttpRequest();
}
catch(e) {
try{
xmlhttp= new ActiveXObject("Microsoft.XMLHTTP");
}
catch(e){
try{
xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
}
catch(e1){
xmlhttp=false;
}
}
}
return xmlhttp;
}
function lookup(inputString) {
if(inputString.length == 0) {
// Hide the suggestion box.
$('#suggestions').hide();
} else {
$.post("faq_ajaxsearch.php", {queryString: ""+inputString+""}, function(data){
if(data.length >0) {
$('#suggestions').show();
$('#autoSuggestionsList').html(data);
}
});
}
} // lookup
</script>
<style type="text/css">
h3 {
margin: 0px;
padding: 0px;
}
.suggestionsBox {
position: relative;
left: 35px;
margin: 10px 0px 0px 0px;
width: 400px;
background-color: #212427;
-moz-border-radius: 7px;
-webkit-border-radius: 7px;
border: 2px solid #000;
color: #fff;
}
.suggestionList {
margin: 0px;
padding: 0px;
}
.suggestionList a {
color: #fe7700;
}
.suggestionList li {
margin: 0px 0px 3px 0px;
padding: 3px;
cursor: pointer;
}
.suggestionList li {
background-color: #659CD8;
}
</style>
-----------------------------------------------------------------
Step 4:
goto /osticket/include/client/open.inc.php, search for the line where the table row for the subject starts and add the onkeyup-event at the end of the input field, it should look like this:
<input type="text" name="subject" size="35" value="<?=$info?>" onkeyup="lookup(this.value);">
Directly below the line, where the error message is displayed, add the following div
<font class="error">* <?=$errors?></font>
<div class="suggestionsBox" id="suggestions" style="display: none;">
Hier sind einige Vorschläge aus den FAQ:
<div class="suggestionList" id="autoSuggestionsList">
</div>
</div>
Also change the table header line for the subject, so it stays besides the input field:
<th valign="top">Subject:</th>
-----------------------------------------------------------------
Step 5:
create a new file "faq_ajaxsearch.php" in your osticket root directory and copypaste the following php code into it. Don't forget to modify the database name, tables, user and password to the one you use. The actual search begins after the third letter typed, if you want to change that, modify the value in the if(strlen($queryString) > 3 ) line.
<?php
/*
* faq_ajaxsearch.php
*
* looks in ost_faqs if there are entries corresponding to the entered value in the Betreff input field
* If yes, it gets the ID of the answer and looks in ost_faqs_to_faqcategories for the respective category id
* Finally it builds the link to the FAQ entry from the category_id and faq_id
*
* Please note, that the columns 'answer' and 'question' needs to have a FullText index, so make sure those
* are altered beforehand ( alter table ost_faqs add FULLTEXT(answer,question); )
*/
$db = mysql_connect('localhost', 'your_mysql_user', 'your_mysql_password'); //change the configuration if required
if(!$db) {
// Show error if we cannot connect.
echo 'ERROR: Es konnte leider keine Verbindung zur Datenbank hergestellt werden.';
} else {
mysql_select_db('osticket'); //change this if required
// Is there a posted query string? If yes, do the search, if no, do nothing
if(isset($_POST)) {
$queryString = $_POST;
if(strlen($queryString) >3) {
$query = "SELECT id,question FROM ost_faqs WHERE MATCH(answer,question) AGAINST ('$queryString') AND faq_active = '1' ";
$result = mysql_query($query);
if($result) {
while ($row = mysql_fetch_array($result)) {
$resultid = $row;
$resultquestion = utf8_encode($row);
// get the category for the found answer
$sql_getcat = "select faqcategory_id from ost_faqs_to_faqcategories where faq_id = '$resultid'";
$querycat = mysql_query($sql_getcat);
while ($rowcat = mysql_fetch_array($querycat)) {
$cat_id = $rowcat;
}
// create the links pointing directly to the faq entry
echo "<li><a href=\"faq.php?cid=$cat_id&answer=$resultid#f$resultid\">$resultquestion</a></li>";
}
} else {
echo 'ERROR: Leider gab es ein Problem mit der Datenbankabfrage.';
}
} else {
// Dont do anything.
}
} else {
echo 'Das Skript sollte so nicht direkt aufgerufen werden!';
}
}
?>
-----------------------------------------------------------------
Summarized: The following files were changed/added:
/osticket/faq_ajaxsearch.php
/osticket/js/jquery-1.2.1.pack.js
/osticket/include/client/open.inc.php
/osticket/include/client/header.inc.php
Have fun
T.
ps: forgive my sometimes complicated english ;)
pps: thanks to the guys who wrote their tuts for the ajax suggestion list and the Fulltext search, lots of inspirations!