<?php
//=======================================
//###################################
// Kayako Infotech Ltd. - SWIFT Framework
//
// Source Copyright 2001-2008 Kayako Infotech Ltd.
// Unauthorized reproduction is not allowed
// License Number: $%LICENSE%$
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//                   www.kayako.com
//###################################
//=======================================

if (!defined("INSWIFT")) {
	trigger_error("Unable to process " . htmlentities($_SERVER['PHP_SELF']), E_USER_ERROR);
}

/**
* Processes Search Rules
*/
function processSearchRules($searchrules, $rulematch = "all", $ticketfilterid = 0)
{
	global $dbCore, $_SWIFT;

	if (!_is_array($searchrules))
	{
		return false;
	}

	if ($rulematch == "all")
	{
		$oper = "AND";
	} else {
		$oper = "OR";
	}

	$ticketidjoin = $_sql = $ticketidlist = $customfieldidlist = array();

	$_start = fetchmicrotime();
	
	foreach ($searchrules as $key=>$val)
	{
		switch ($val[0])
		{
			case "entiremessage":
				/* Added by PARCSPOT SEKIGUCHI 2009.07.10 */
				if (!$val[2]) { return false; }
//				$ticketidjoin = array_merge($ticketidjoin, doTicketSearch($val[2], false, true));
				$ticketidjoin = array_merge($ticketidjoin, _searchContents($val[2]));
				break;
			case "subject":
				$_sql[] = buildSearchSQL("subject", $val[1], $val[2]);
				break;
			case "fullname":
				$ticketidjoin = array_merge($ticketidjoin, _searchFullname($val[2]));
				break;
			case "email":
				$ticketidjoin = array_merge($ticketidjoin, _searchTicketEmail($val[2]));
				break;
			case "groupname":
				$ticketidjoin = array_merge($ticketidjoin, _searchGroupName($val[2]));
				break;
			case "notes":
				$ticketidjoin = array_merge($ticketidjoin, searchTicketNotes(NOTE_TICKET, $val[2]));
				break;
			case "usernotes":
				$ticketidjoin = array_merge($ticketidjoin, searchTicketNotes(NOTE_USER, $val[2]));
				break;
			case "departmentid":
				$_sql[] = buildSearchSQL("departmentid", $val[1], $val[2]);
				break;
			case "ticketstatusid":
				$_sql[] = buildSearchSQL("ticketstatusid", $val[1], $val[2]);
				break;
			case "tgroupid":
				$_sql[] = buildSearchSQL("tgroupid", $val[1], $val[2]);
				break;
			case "priorityid":
				$_sql[] = buildSearchSQL("priorityid", $val[1], $val[2]);
				break;
			case "ownerstaffid":
				$_sql[] = buildSearchSQL("ownerstaffid", $val[1], $val[2]);
				break;
			case "flagtype":
				$_sql[] = buildSearchSQL("flagtype", $val[1], $val[2]);
				break;
			case "slaplanid":
				$_sql[] = buildSearchSQL("slaplanid", $val[1], $val[2]);
				break;
			case "dateline":
				$_sql[] = buildSearchSQL("dateline", $val[1], iif(!empty($val[2]), strtotime($val[2]), DATENOW));
				break;
			case "duetime":
				$_sql[] = buildSearchSQL("duetime", $val[1], iif(!empty($val[2]), strtotime($val[2]), DATENOW));
				break;
			case "datelinerange":
				$_sql[] = buildRangeSQL("dateline", $val[2]);
				break;
			case "duetimerange":
				$_sql[] = buildRangeSQL("duetime", $val[2]);
				break;
			case "lastactivityrange":
				$_sql[] = buildRangeSQL("lastactivity", $val[2]);
				break;
			case "lastuserreplytimerange":
				$_sql[] = buildRangeSQL("lastuserreplytime", $val[2]);
				break;
			case "laststaffreplytimerange":
				$_sql[] = buildRangeSQL("laststaffreplytime", $val[2]);
				break;
			case "escalatedonrange":
				$_sql[] = buildRangeSQL("escalatedtime", $val[2]);
				break;
			case "lastactivity":
				$_sql[] = buildSearchSQL("lastactivity", $val[1], iif(!empty($val[2]), strtotime($val[2]), DATENOW));
				break;
			case "lastuserreplytime":
				$_sql[] = buildSearchSQL("lastuserreplytime", $val[1], iif(!empty($val[2]), strtotime($val[2]), DATENOW));
				break;
			case "laststaffreplytime":
				$_sql[] = buildSearchSQL("laststaffreplytime", $val[1], iif(!empty($val[2]), strtotime($val[2]), DATENOW));
				break;
			case "escalatedon":
				$_sql[] = buildSearchSQL("escalatedtime", $val[1], iif(!empty($val[2]), strtotime($val[2]), DATENOW));
				break;
			case "isphonecall":
				$_sql[] = buildSearchSQL("isphonecall", $val[1], $val[2]);
				break;
			case "isescalated":
				$_sql[] = buildSearchSQL("isescalated", $val[1], $val[2]);
				break;
			case "hasattachments":
				$_sql[] = buildSearchSQL("hasattachments", $val[1], $val[2]);
				break;
			case "hasnotes":
				$_sql[] = buildSearchSQL("hasnotes", $val[1], $val[2]);
				break;
				//Mahesh: Hasbilling added for advance search.
			case "hasbilling":
				$_sql[] = buildSearchSQL("hasbilling", $val[1], $val[2]);
				break;
			case "isemailed":
				$_sql[] = buildSearchSQL("isemailed", $val[1], $val[2]);
				break;
			case "hasattachments":
				$_sql[] = buildSearchSQL("hasattachments", $val[1], $val[2]);
				break;
		}

		$cfieldsearch = substr($val[0], 0, 7);
		if ($cfieldsearch == "cfield_")
		{
			// Search on custom field
			$customfieldid = intval(substr($val[0], 7));
			$ticketidjoin = array_merge($ticketidjoin, searchTicketCustomFields(array($customfieldid), $val[2]));
		}
	}

	if (_is_array($ticketidjoin))
	{
		$_sql[] = "`ticketid` IN (". buildIN($ticketidjoin) .")";
	}

	if (!count($_sql))
	{
		return false;
	}

	$_assigns = getAssignedDepartments(MODULE_TICKETS);
	if (!count($_assigns))
	{
		return false;
	}

	if (!empty($ticketfilterid))
	{
		$_SWIFT["settings"]["t_resultlimit"] = 10000;
	}

	$query = "SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE (". implode($oper, $_sql).") AND `departmentid` IN (". buildIN($_assigns) .") ORDER BY `lastactivity` DESC LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";";
	$dbCore->query($query);
	while ($dbCore->nextRecord())
	{
		$ticketidlist[] = $dbCore->Record["ticketid"];
	}
	if (!count($ticketidlist))
	{
		return false;
	}
	$_end = fetchmicrotime();

	$ticketsearchid = storeTicketSearch($ticketidlist, $_SWIFT["language"]["smixed"], ($_end-$_start), "", array(), $ticketfilterid);

	return $ticketsearchid;
}

/**
* Build SQL over date range
*/
function buildRangeSQL($field, $rangetype)
{
    require_once ("./includes/functions_date.php");

	switch ($rangetype)
	{
		case "today":
		    // Today
			$_start = floorDate(DATENOW);
			$_end   = ceilDate(DATENOW);
			break;
		case "yesterday":
		    // Yesterday
			$_start = floorDate(strtotime("-1 day"));
			$_end   = ceilDate($_start);
			break;
		case "thisweek":
			// Current week to date
			// date(w) = current day offset from Sunday, the first day of the week.
			$_start = floorDate(DATENOW - (DATE_ONE_DAY * date("w")));
			$_end   = DATENOW;
			break;
		case "lastweek":
            // Last 7 days
			$_start = floorDate(strtotime("-1 week"));
			$_end   = DATENOW;
		break;
		case "thismonth":
		    // Current month to date
			$_start = firstOfTheMonth(DATENOW);
			$_end   = DATENOW;
			break;
		case "lastmonth":
		    // Last 30 days
			$_start = floorDate(strtotime("-1 month"));
			$_end   = DATENOW;
			break;
		case "lastthreemonths":
		    // Last 90 days
			$_start = floorDate(strtotime("-3 month"));
			$_end   = DATENOW;
			break;
		case "lastsixmonths":
		    // Last 180 days
			$_start = floorDate(strtotime("-6 month"));
			$_end   = DATENOW;
			break;
		case "thisyear":
		    // Current year to date
			$_start = mktime(0, 0, 0, 1, 1, date("Y"));
			$_end   = DATENOW;
			break;
		case "lastyear":
		    // Last 365 days
			$_start = floorDate(strtotime("-1 year"));
			$_end   = DATENOW;
			break;
	}
	
	$sql = "(`". addslashes($field) ."` > '". intval($_start) ."' AND `". addslashes($field) ."` < '". intval($_end) ."')";

	return $sql;

}

/**
* Searches the custom fields for the given query
*/
function searchTicketCustomFields($customfieldidlist, $query)
{
	global $dbCore, $_SWIFT;

	$ticketidlist = array();
	require_once ("./includes/functions_sqlsearch.php");

	$searchjoin = buildSQLSearch("fieldvalue", $query);
	if (!$searchjoin)
	{
		return $ticketidlist;
	}

	$dbCore->query("SELECT `customfieldid`, `typeid` FROM `". TABLE_PREFIX ."customfieldvalues` WHERE `customfieldid` IN (". buildIN($customfieldidlist) .") AND ".$searchjoin." ORDER BY `customfieldvalueid` DESC LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		if (!in_array($dbCore->Record["typeid"], $ticketidlist))
		{
			$ticketidlist[] = $dbCore->Record["typeid"];
		}
	}

	return $ticketidlist;
}

/**
* Build the sql for the given field
*/
function buildSearchSQL($fieldname, $oper, $value)
{
	if ($oper == OP_NOTEQUAL)
	{
		// Not Equal
		$prefix = " != '";
		$suffix = "'";
	} else if ($oper == OP_CONTAINS) {
		$prefix = " LIKE '%";
		$suffix = "%'";
	} else if ($oper == OP_NOTCONTAINS) {
		$prefix = " NOT LIKE '%";
		$suffix = "%'";
	} else if ($oper == OP_GREATER) {
		$prefix = " > '";
		$suffix ="'";
	} else if ($oper == OP_LESS) {
		$prefix = " < '";
		$suffix ="'";
	} else {
		// Equal
		$prefix = " = '";
		$suffix = "'";
	}

	$sql = "`". addslashes($fieldname) ."`".$prefix.addslashes($value).$suffix;

	return $sql;
}

/**
* Searches the contents
* @author PARCSPOT SEKIGUCHI <sekiguchi@parcspot.com>
* added 2009.07.10
*/
function _searchContents($query)
{
	global $dbCore, $_SWIFT;

	$ticketidlist = array();
	require_once ("./includes/functions_sqlsearch.php");

	$searchjoin = buildSQLSearch("contents", $query);
	if (!$searchjoin)
	{
		return $ticketidlist;
	}

/*
	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE ".$searchjoin." LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		$ticketidlist[] = $dbCore->Record["ticketid"];
	}
*/

//	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."ticketposts` WHERE ".$searchjoin." LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."ticketposts` WHERE ".$searchjoin.";");

	while ($dbCore->nextRecord())
	{
//		if (!in_array($dbCore->Record["ticketid"], $ticketidlist))
//		{
			$ticketidlist[] = $dbCore->Record["ticketid"];
//		}
	}

	return $ticketidlist;
}

/**
* Searches the full name
*/
function _searchFullname($query)
{
	global $dbCore, $_SWIFT;

	$ticketidlist = array();

	require_once ("./includes/functions_sqlsearch.php");

	$searchjoin = buildSQLSearch("fullname", $query);
	if (!$searchjoin)
	{
		return $ticketidlist;
	}

	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE ".$searchjoin." LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		$ticketidlist[] = $dbCore->Record["ticketid"];
	}

	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."ticketposts` WHERE ".$searchjoin." LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		if (!in_array($dbCore->Record["ticketid"], $ticketidlist))
		{
			$ticketidlist[] = $dbCore->Record["ticketid"];
		}
	}

	return $ticketidlist;
}

/**
* Searches the email
*/
function _searchTicketEmail($query)
{
	global $dbCore, $_SWIFT;

	$ticketidlist = array();

	require_once ("./includes/functions_sqlsearch.php");

	$searchjoin = buildSQLSearch("email", $query);
	if (!$searchjoin)
	{
		return $ticketidlist;
	}

	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE ".$searchjoin." ORDER BY `lastactivity` DESC LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		$ticketidlist[] = $dbCore->Record["ticketid"];
	}

	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."ticketposts` WHERE ".$searchjoin." LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		if (!in_array($dbCore->Record["ticketid"], $ticketidlist))
		{
			$ticketidlist[] = $dbCore->Record["ticketid"];
		}
	}

	return $ticketidlist;
}


/**
* Searches the groups
*/
function _searchGroupName($query)
{
	global $dbCore, $_SWIFT;

	$ticketidlist = $useridlist = $useremaillist = $usergroupidlist = array();

	require_once ("./includes/functions_sqlsearch.php");

	$searchjoin = buildSQLSearch("title", $query);
	if (!$searchjoin)
	{
		return $ticketidlist;
	}

	$dbCore->query("SELECT `usergroupid` FROM `". TABLE_PREFIX ."usergroups` WHERE ".$searchjoin." LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		$usergroupidlist[] = $dbCore->Record["usergroupid"];
	}

	$dbCore->query("SELECT `userid` FROM `". TABLE_PREFIX ."users` WHERE `usergroupid` IN (". buildIN($usergroupidlist) .");");
	while ($dbCore->nextRecord())
	{
		$useridlist[] = $dbCore->Record["userid"];
	}

	$dbCore->query("SELECT `email` FROM `". TABLE_PREFIX ."useremails` WHERE `userid` IN (". buildIN($useridlist) .");");
	while ($dbCore->nextRecord())
	{
		$useremaillist[] = $dbCore->Record["email"];
	}

	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE `email` IN (". buildIN($useremaillist) .");");
	while ($dbCore->nextRecord())
	{
		$ticketidlist[] = $dbCore->Record["ticketid"];
	}

	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."ticketposts` WHERE `email` IN (". buildIN($useremaillist) .");");
	while ($dbCore->nextRecord())
	{
		if (!in_array($dbCore->Record["ticketid"], $ticketidlist))
		{
			$ticketidlist[] = $dbCore->Record["ticketid"];
		}
	}

	return $ticketidlist;
}
/**
* Searches the ticket notes and returns the ticket ids, in case of user notes, all tickets under the user is returned
*/
function searchTicketNotes($anotetype, $query)
{
	global $dbCore, $_SWIFT;

	$ticketidlist = $notetype = $useridlist = array();

	require_once ("./includes/functions_sqlsearch.php");

	$searchjoin = buildSQLSearch("notes", $query);
	if (!$searchjoin)
	{
		return $ticketidlist;
	}

	if ($anotetype & NOTE_TICKET)
	{
		$notetype[] = NOTE_TICKET;
	}
	if ($anotetype & NOTE_USER)
	{
		$notetype[] = NOTE_USER;
	}
	if (!count($notetype))
	{
		return $ticketidlist;
	}

	$dbCore->query("SELECT `ticketnoteid`, `typeid`, `notetype`, `forstaffid` FROM `". TABLE_PREFIX ."ticketnotes` WHERE `notetype` IN (". buildIN($notetype) .") AND ".$searchjoin." ORDER BY `dateline` DESC LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		if ($dbCore->Record["forstaffid"] == 0 || $dbCore->Record["forstaffid"] == $_SWIFT["staff"]["staffid"]) // Make sure this note belongs to only this staff
		{
			if ($dbCore->Record["notetype"] == NOTE_TICKET && in_array(NOTE_TICKET, $notetype))
			{
				$ticketidlist[] = $dbCore->Record["typeid"];
			} else if ($dbCore->Record["notetype"] == NOTE_USER && in_array(NOTE_USER, $notetype)) {
				$useridlist[] = $dbCore->Record["typeid"];
			}
		}
	}

	if (count($useridlist))
	{
		$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE `userid` IN (". buildIN($useridlist) .") ORDER BY `lastactivity` DESC LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
		while ($dbCore->nextRecord())
		{
			if (!in_array($dbCore->Record["ticketid"], $ticketidlist))
			{
				$ticketidlist[] = $dbCore->Record["ticketid"];
			}
		}
	}

	return $ticketidlist;
}

/**
* Search ticket by a user
*/
function searchTicketsByUser($userid, $ticketstatusid = false)
{
	global $dbCore, $_SWIFT;

	$_start = fetchmicrotime();
	$_assigns = getAssignedDepartments();

	if ($ticketstatusid)
	{
		$_ticketstatusidlist = array();
		$_ticketstatusidlist[] = $ticketstatusid;
	} else {
		$_ticketstatusidlist = getDisplayTicketStatusList(true);
	}

	$ticketidlist = array();
	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE `departmentid` IN (". buildIN($_assigns) .") AND `ticketstatusid` IN (". buildIN($_ticketstatusidlist) .") AND `userid` = '". intval($userid) ."' ORDER BY `lastactivity` DESC LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		$ticketidlist[] = $dbCore->Record["ticketid"];
	}
	$_end = fetchmicrotime();

	$ticketsearchid = storeTicketSearch($ticketidlist, $_SWIFT["language"]["smixed"], ($_end-$_start));

	return $ticketsearchid;
}

/**
* Search ticket by a user group
*/
function searchTicketsByUserGroup($usergroupid, $ticketstatusid = false)
{
	global $dbCore, $_SWIFT;

	$_start = fetchmicrotime();
	$_assigns = getAssignedDepartments();

	if ($ticketstatusid)
	{
		$_ticketstatusidlist = array();
		$_ticketstatusidlist[] = $ticketstatusid;
	} else {
		$_ticketstatusidlist = getDisplayTicketStatusList(true);
	}

	$useridlist = array();
	// First get all users under this group
	$dbCore->query("SELECT `userid` FROM `". TABLE_PREFIX ."users` WHERE `usergroupid` = '". intval($usergroupid) ."';");
	while ($dbCore->nextRecord())
	{
		$useridlist[] = $dbCore->Record["userid"];
	}

	$ticketidlist = array();
	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE `departmentid` IN (". buildIN($_assigns) .") AND `ticketstatusid` IN (". buildIN($_ticketstatusidlist) .") AND `userid` IN (". buildIN($useridlist) .") ORDER BY `lastactivity` DESC LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		$ticketidlist[] = $dbCore->Record["ticketid"];
	}
	$_end = fetchmicrotime();

	$ticketsearchid = storeTicketSearch($ticketidlist, $_SWIFT["language"]["smixed"], ($_end-$_start));

	return $ticketsearchid;
}

/**
* Get new/updated tickets since the staff member's last login
*/
function getNewAndUpdatedTickets($new = true)
{
	global $dbCore, $_SWIFT;

	$_start = fetchmicrotime();
	$_assigns = getAssignedDepartments();
	$_ticketstatusidlist = getDisplayTicketStatusList();

	$ticketidlist = array();
	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE `departmentid` IN (". buildIN($_assigns) .") AND `ticketstatusid` IN (". buildIN($_ticketstatusidlist) .") AND `" . ($new ? "dateline" : "lastactivity") . "` > ". intval($_SWIFT["staff"]["lastvisit"]) ." ORDER BY `" . ($new ? "dateline" : "lastactivity") . "` DESC LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		$ticketidlist[] = $dbCore->Record["ticketid"];
	}
	$_end = fetchmicrotime();

	$ticketsearchid = storeTicketSearch($ticketidlist, $_SWIFT["language"][($new ? "newtickets" : "updatedtickets")], ($_end-$_start));

	return $ticketsearchid;
}

/**
* The main function that carries out the ticket search
*/
function doTicketSearch($query, $insubject = false, $returnids = false)
{
	global $dbCore, $_SWIFT;

	$_start = fetchmicrotime();
	// Strip HTML Tags
	$query = strip_tags($query);

	// Strips white spaces, words containing asterisks, new lines and all symbols (Except for wildcard character)
	$stopdata = array("#\s+#s", "#(\r\n|\r|\n)#s", "/[^a-zA-Z0-9\-\_\s\*\+\&\#;]/");
	$replacewith = array(" ", " ", "");
	$query = trim(strtolower(preg_replace($stopdata, $replacewith, $query)));

	$query = preg_replace('/&#(x[a-f0-9]+|[0-9]+);/i', ' &#$1; ', $query);

	// Ok, now explode it according to delimiter
	$wordlist = explode(SEARCH_DELIM, $query);
	if (!count($wordlist))
	{
		return array();
	}


	$excludedwords = $wildcardwords = $requiredwords = $globalwords = $excludedwordidlist = $rejectedwords = $querywords = array();
	foreach ($wordlist as $key=>$val)
	{
		if (trim($val) == "")
		{
			continue;
		}

		$wordlength = strlen($val);
		$wordaction = substr($val, 0, 1);
		if ($wordaction == "-" || $wordaction == "+")
		{
			$wordlength--;
		}
		if (($wordlength < $_SWIFT["settings"]["tickets_minwordlength"] || $wordlength > $_SWIFT["settings"]["tickets_maxwordlength"]) && !strstr($val, "*"))
		{
			$rejectedwords[] = iif($wordaction=="-"||$wordaction=="+", substr($val,1), $val);
			continue;
		} else {
			$querywords[] = iif($wordaction=="-"||$wordaction=="+", substr($val,1), $val);
		}

		if ($wordaction == "-")
		{
			// Add to excluded words
			$excludedwords[] = preg_replace("/[^a-zA-Z0-9\-\_]/", "", substr($val, 1));
		} else if (strstr($val, "*")) {
			// Wildcard word
			$wildcardwords[] = $val;
		} else if ($wordaction == "+") {
			$requiredwords[] = preg_replace("/[^a-zA-Z0-9\-\_]/", "", substr($val, 1));
		} else {
			// Always a required word
			$requiredwords[] = preg_replace("/[^a-zA-Z0-9\-\_\&\#;]/", "", trim($val));
		}
	}

	$_doexcluded = count($excludedwords);

	// Lets first get the word ids for required words
	if (!count($requiredwords) && !count($wildcardwords))
	{
		return array();
	}

	$joinwordlist = array_merge($excludedwords, $wildcardwords, $requiredwords);
	$wordidlist = getTicketWordIDs($joinwordlist);
	if (!$wordidlist)
	{
		return array();
	}

	if ($_doexcluded)
	{
		foreach ($wordidlist as $key=>$val)
		{
			if (!in_array($key, $excludedwords))
			{
				// its a global word
				$globalwords[$key] = $val;
			} else {
				$excludedwordidlist[$key] = $val;
			}
		}
	} else {
		$globalwords = $wordidlist;
	}

	// Get the tickets from global words
	$_ticketidsearchresult = getTicketIDFromWords($globalwords, $insubject);
	$_globalticketidlist = $_ticketidsearchresult[0];

	if ($_doexcluded)
	{
		$_exticketidsearchresult = getTicketIDFromWords($excludedwordidlist, $insubject);
		$_excludedticketidlist = $_exticketidsearchresult[0];
		if ($_excludedticketidlist)
		{
			foreach ($_globalticketidlist as $key=>$val)
			{
				if (in_array($val, $_excludedticketidlist))
				{
					unset($_globalticketidlist[$key]);
				}
			}
		}
	}

	if (!count($_globalticketidlist))
	{
		return array();
	}

	$_end = fetchmicrotime();
	if ($returnids == true)
	{
		return $_globalticketidlist;
	}
	$ticketsearchid = storeTicketSearch($_globalticketidlist, $query, ($_end-$_start), array(), $_ticketidsearchresult[1]);

	return $ticketsearchid;
}

/**
* Search the Creator
*/
function doMenuCreatorSearch($query)
{
	global $dbCore, $_SWIFT;

	if (empty($query))
	{
		return false;
	}

	$query = strip_tags($query);
	$_start = fetchmicrotime();

	$_fullnamelist = _searchFullname($query);

	$_emaillist = _searchTicketEmail($query);

	$_ticketidlist = $_fullnamelist;
	if (_is_array($_emaillist))
	{
		foreach ($_emaillist as $key=>$val)
		{
			if (!in_array($val, $_ticketidlist))
			{
				$_ticketidlist[] = $val;
			}
		}
	}

	$_end = fetchmicrotime();
	$ticketsearchid = storeTicketSearch($_ticketidlist, $query, ($_end-$_start), $rejectedwords);

	return $ticketsearchid;
}

/**
* Gets the ticket ids from given word list
*/
function getTicketIDFromWords($wordidlist, $insubject = false)
{
	global $dbCore, $_SWIFT;

	$_assigns = getAssignedDepartments(MODULE_TICKETS);
	if (!count($_assigns))
	{
		return false;
	}

	$_ticketidlist = $_ticketranklist = $_ticketwordranklist = $_ranklist = array();

	$_wordcount = count($wordidlist);

	// We get the ticketids along with the number of time the word appears in the ticket
	if ($_SWIFT["settings"]["t_cntrelevance"] == 1)
	{
		$dbCore->query("SELECT tickets.ticketid, COUNT(*) AS wordrank FROM `". TABLE_PREFIX ."ticketpostindex` AS ticketpostindex LEFT JOIN `". TABLE_PREFIX ."ticketposts` AS ticketposts ON (ticketposts.ticketpostid = ticketpostindex.ticketpostid) LEFT JOIN `". TABLE_PREFIX ."tickets` AS tickets ON (tickets.ticketid = ticketposts.ticketid) WHERE tickets.departmentid IN (". buildIN($_assigns) .") AND ticketpostindex.ticketwordid IN (". buildIN($wordidlist) .") GROUP BY ticketpostindex.ticketpostid ORDER BY wordrank DESC LIMIT 500;");
		while ($dbCore->nextRecord())
		{
			$_ticketwordranklist[$dbCore->Record["ticketid"]] = intval($dbCore->Record["wordrank"]);
		}
	}

	// Get the ticketid, ticketpostid and the number of words that appear in the ticket.
	$dbCore->query("SELECT ticketposts.ticketid, COUNT(*) AS rank FROM `". TABLE_PREFIX ."ticketpostindex` AS ticketpostindex LEFT JOIN `". TABLE_PREFIX ."ticketposts` AS ticketposts ON (ticketposts.ticketpostid = ticketpostindex.ticketpostid) LEFT JOIN `". TABLE_PREFIX ."tickets` AS tickets ON (tickets.ticketid = ticketposts.ticketid) WHERE ticketpostindex.ticketwordid IN (". buildIN($wordidlist) .") AND tickets.departmentid IN (". buildIN($_assigns) .") GROUP BY ticketposts.ticketid ORDER BY rank DESC LIMIT ". (intval($_SWIFT["settings"]["t_resultlimit"])+50) .";");
	while ($dbCore->nextRecord())
	{
		$_ticketidlist[] = $dbCore->Record["ticketid"];
		$_ticketranklist[$dbCore->Record["ticketid"]] = $dbCore->Record["rank"]+(($_ticketwordranklist[$dbCore->Record["ticketid"]]/$_wordcount)*100);
	}


	$_ceil = @max($_ticketranklist);
	$_floor = @min($_ticketranklist);

	foreach ($_ticketranklist as $key=>$val)
	{
		$_ranklist[$key] = round(($val/$_ceil)*100);
	}

	unset($_ticketranklist);
	arsort($_ranklist);

	return array($_ticketidlist, $_ranklist);
}

/**
* Get the ticket word ids from given words
*/
function getTicketWordIDs($wordlist)
{
	global $dbCore, $_SWIFT;

	if (!_is_array($wordlist))
	{
		return false;
	}

	$_ticketwordidlist = $_wordlist = $_wildcardwordlist = array();
	foreach ($wordlist as $key=>$val)
	{
		if (isset($_SWIFT["ticketwordcache"][$val]))
		{
			$_ticketwordidlist[$val] = $_SWIFT["ticketwordcache"][$val];
			unset($wordlist[$key]);
		} else if (strstr($val, "*")) {
			// Its a wildcard, do not cache this into words...
			$wildcardword = str_replace("*", "%", $val);
			$_wildcardwordlist[] = $wildcardword;
			$_wildcardsql .= " OR `contents` LIKE '". $dbCore->escape($wildcardword) ."'";
		} else {
			$_wordlist[] = $val; // Usual add it
		}
		
	}

	if (count($_wordlist))
	{
		// Fetch the word ids
		$dbCore->query("SELECT * FROM `". TABLE_PREFIX ."ticketwords` WHERE `contents` IN (". buildIN($_wordlist) .");");
		while ($dbCore->nextRecord())
		{
			$_ticketwordidlist[$dbCore->Record["contents"]] = $dbCore->Record["ticketwordid"];
			$_SWIFT["ticketwordcache"][$dbCore->Record["contents"]] = $dbCore->Record["ticketwordid"]; // Add to Cache too
		}
	}

	if (!empty($_wildcardsql))
	{
		$dbCore->query("SELECT * FROM `". TABLE_PREFIX ."ticketwords` WHERE 0 ".$_wildcardsql.";");
		while ($dbCore->nextRecord())
		{
			if (!isset($_ticketwordidlist[$dbCore->Record["contents"]]))
			{
				$_ticketwordidlist[$dbCore->Record["contents"]] = $dbCore->Record["ticketwordid"];
				$_SWIFT["ticketwordcache"][$dbCore->Record["contents"]] = $dbCore->Record["ticketwordid"]; // Add to Cache too
			}
		}
	}

	return $_ticketwordidlist;
}

/**
* Search the last replier for tickets
*/
function searchLastReplier($query)
{
	global $dbCore, $_SWIFT;

	$_query = str_replace("*", "%", $query);
	$_ticketidlist = array();

	$_assigns = getAssignedDepartments(MODULE_TICKETS);
	
	$_start = fetchmicrotime();
	$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE `departmentid` IN (". buildIN($_assigns) .") AND `lastreplier` LIKE '". $dbCore->escape($_query) ."' LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		$_ticketidlist[] = $dbCore->Record["ticketid"];
	}
	$_end = fetchmicrotime();

	if (!count($_ticketidlist))
	{
		return false;
	}

	$ticketsearchid = storeTicketSearch($_ticketidlist, $query, ($_end-$_start));

	return $ticketsearchid;
}

/**
* Searches the fullname field in tickets table
*/
function searchFullName($query)
{
	global $dbCore, $_SWIFT;

	$_query = str_replace("*", "%", $query);
	$_ticketidlist = array();

	$_assigns = getAssignedDepartments(MODULE_TICKETS);
	
	$_start = fetchmicrotime();
	$dbCore->query("SELECT DISTINCT(ticketposts.ticketid) FROM `". TABLE_PREFIX ."ticketposts` AS ticketposts LEFT JOIN `". TABLE_PREFIX ."tickets` AS tickets ON (ticketposts.ticketid = tickets.ticketid AND tickets.departmentid IN (". buildIN($_assigns) .")) WHERE ticketposts.fullname LIKE '". $dbCore->escape($_query) ."' LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
	while ($dbCore->nextRecord())
	{
		$_ticketidlist[] = $dbCore->Record["ticketid"];
	}
	$_end = fetchmicrotime();

	if (!count($_ticketidlist))
	{
		return false;
	}

	$ticketsearchid = storeTicketSearch($_ticketidlist, $query, ($_end-$_start));

	return $ticketsearchid;
}

/**
* Searches a ticket with given ticket id.
*/
function searchTicketID($ticketid)
{
	global $dbCore, $_SWIFT;

	// Maybe its just a direct ticket id?
	if (is_numeric($ticketid))
	{
		$_ticketid = $dbCore->queryFetch("SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE `ticketid` = '". intval($ticketid) ."';");
		if (!empty($_ticketid["ticketid"]))
		{
			return array("type"=> SEARCH_TICKETID, "ticketid" => $_ticketid["ticketid"]);
		} else {
			return false;
		}
	} else {
		// Most probably a mask..
		if (strstr($ticketid, '*'))
		{
			// Could be multiple searches
			$searchquery = str_replace("*", "%", $ticketid);
			$_ticketidlist = array();
			$_start = fetchmicrotime();
			$dbCore->query("SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE `ticketmaskid` LIKE '". $dbCore->escape($searchquery) ."' LIMIT ". intval($_SWIFT["settings"]["t_resultlimit"]) .";");
			while ($dbCore->nextRecord())
			{
				$_ticketidlist[] = $dbCore->Record["ticketid"];
			}
			$_end = fetchmicrotime();

			if (!count($_ticketidlist))
			{
				return false;
			}

			$timetaken = number_format(($_end-$_start), 5);

			$ticketsearchid = storeTicketSearch($_ticketidlist, $ticketid, $timetaken);
			if (!$ticketsearchid)
			{
				return false;
			}

			return array("type"=> SEARCH_TICKETIDLIST, "ticketsearchid" => $ticketsearchid);
		} else {
			// Has to be a direct ticket mask id
			$_ticketmaskid = $dbCore->queryFetch("SELECT `ticketid` FROM `". TABLE_PREFIX ."tickets` WHERE `ticketmaskid` = '". $dbCore->escape($ticketid) ."';");
			if (!empty($_ticketmaskid["ticketid"]))
			{
				return array("type"=> SEARCH_TICKETID, "ticketid" => $_ticketmaskid["ticketid"]);
			} else {
				return false;
			}
		}
	}

	return false;
}

/**
* Stores the ticket search for permanent usage.. till cleaned up by cron.
*/
function storeTicketSearch($ticketidlist, $keywords, $timetaken, $rejectedwords = false, $ranklist = array(), $ticketfilterid = 0)
{
	global $dbCore, $_SWIFT;

	if (!_is_array($ticketidlist))
	{
		return false;
	}

	if (is_array($rejectedwords))
	{
		foreach ($rejectedwords as $key=>$val)
		{
			$rejectstr .= $val." ";
		}
	}

	$dbCore->query("INSERT INTO `". TABLE_PREFIX ."ticketsearches` (`staffid`, `dateline`, `keywords`, `resultscount`, `timetaken`, `ticketidlist`, `rejectedwords`, `relevancelist`, `ticketfilterid`) VALUES ('". intval($_SWIFT["staff"]["staffid"]) ."', '". DATENOW ."', '". $dbCore->escape($keywords) ."', '". count($ticketidlist) ."', '". $dbCore->escape($timetaken) ."', '". $dbCore->escape(serialize($ticketidlist)) ."', '". $dbCore->escape($rejectstr) ."', '". $dbCore->escape(serialize($ranklist)) ."', '". intval($ticketfilterid) ."');");
	$ticketsearchid = $dbCore->lastInsertId();

	return $ticketsearchid;
}


?>