ticketsearch.php

本代码是为客户联系管理而做的系统

	$saved_results  = array();
	while ($saves = $db->row_array()) {
		if ($saves[save_type] == 'search') {
			$saved_searches[$saves[id]] = $saves[save_name];
		}
	}

	if ($_REQUEST['saved_search']) {
		$saved_selected = $_REQUEST['saved_search'];
	} else {
		$saved_selected = NULL;
	}

	$saved_searches = iff(count($saved_searches) > 0, form_select('saved_search', $saved_searches, '', $saved_selected), '(None saved)  ') .
		form_radio_single('save_search_op', 'No Action', 1) . "No Action " .
		form_radio_single('save_search_op', 'Run') . "Run " .
		form_radio_single('save_search_op', 'Edit') . "Edit " .
		form_radio_single('save_search_op', 'Delete') . "Delete " .
		form_radio_single('save_search_op', 'Save') . "Update " .
		form_radio_single('save_search_op', 'Save As') . "Save as New: " .
		form_input('save_search_name');

	$table[] = array(table_thelp('<B>Saved Searches</B>', 'Tickets - Searches', 'Saved Searches'), $saved_searches);

	table_header('Search for Tickets', 'ticketsearch.php', array('do' => 'search', 'searchtype' => 'advanced'), '', 'searchform');
	table_content($columns, $table);
	table_footer('Search');
	unset($columns, $table);

	// hide search form if searching
	if ($_REQUEST['do'] == "search") {
		echo "<SCRIPT>oc('Search for Tickets');oc('Search for Tickets2');</SCRIPT>";
	}

}

echo "</form></form>";

############################################# RESULTS ############################################# 

if ($_REQUEST['do'] == "search") {

	/*******************************************************
			PART 1: BUILDING WHERE PART OF QUERY
	*******************************************************/

	////////////////////////// LIMIT BASED ON REPLIES //////////////////////////

	if ($_REQUEST['message']) {

		// message is a phrase
		if ($_REQUEST['messageoption'] == 'PHRASE') {
			$where .= " message LIKE '%" . addslashes($_REQUEST['message']) . "%'";
			
			// message is AND or OR
		} else {
			
			give_default($_REQUEST['messageoption'], 'OR');

			$words = explode(" ", $_REQUEST['message']);
			for ($i=0; $i < count($words); $i++) {
				if ($i == "0") {
					$where .= " message LIKE '%" . addslashes($words[$i]) . "%' ";
				} else {
					$where .= " $_REQUEST[messageoption] message LIKE '%" . addslashes($words[$i]) . "%' ";
				}
			}
		}
		
		if ($_REQUEST['messageowner'] == "TECH") {
			$where .= " AND techid";
		} elseif ($_REQUEST['messageowner'] == "USER") {
			$where .= " AND userid";
		}
		
		$db->query("SELECT ticketid FROM ticket_message WHERE $where");
		if ($db->num_rows() < 1) {
			// make query fail
			$reply_search = " AND 0 ";

		} else {
			
			while ($reply = $db->row_array()) {
				$reply_search[] = $reply['ticketid'];
			}
			
			$reply_search = ' AND ticket.id IN ' . array2sql($reply_search);
		}
	}
	unset($where);

	////////////////////////// START QUERY //////////////////////////

	$select = "
	SELECT ticket.*,
	ticket_cat.name AS catname,
	ticket_pri.name AS priname,
	user.id AS usrid, user.username AS username,
	tech.username AS techname,tech.id AS tech,
	COUNT(ticket_attachments.id) AS attachments
	FROM ticket
	LEFT JOIN ticket_cat ON (ticket_cat.id = ticket.category)
	LEFT JOIN ticket_pri ON (ticket_pri.id = ticket.priority)
	LEFT JOIN user ON (user.id = ticket.userid)
	LEFT JOIN tech ON (tech.id = ticket.tech)
	LEFT JOIN ticket_attachments ON (ticket_attachments.ticketid = ticket.id)
	WHERE 
	";

	if ($_REQUEST['nodisplay']) {
		$select .= " nodisplay";
		$disp = " AND nodisplay = 1";
	} else {
		$select .= " !nodisplay";
		$disp = " AND !nodisplay";
	}

	////////////////////////// RESTRICTED CATEGORIES //////////////////////////

	$select .= iff($user[cats_admin], " AND category NOT IN ($user[cats_admin]) ");

	////////////////////////// CATEGORY / PRIORITY / TECH OWNER //////////////////////////

	// category
	if (is_array($_REQUEST['category'])) {

		// need to remove -1, array search unreliable
		foreach($_REQUEST['category'] AS $key => $var) {
			if ($var == '-1') {
				unset($_REQUEST['category'][$key]);
				$_REQUEST['category'][] = '0';
			}
			if ($var == '-2') {
				$_REQUEST['category'] = array();
			}
				
		}
	
		if (count($_REQUEST['category'])) {
			$dosql = array2sql($_REQUEST['category']);
			$where .= " AND category IN $dosql";
		}

	} elseif ($_REQUEST['category']) {
		$where .= " AND category = $_REQUEST[category]";
	}

	// priority
	if (is_array($_REQUEST['priority'])) {

		// need to remove -1, array search unreliable
		foreach($_REQUEST['priority'] AS $key => $var) {
			if ($var == '-1') {
				unset($_REQUEST['priority'][$key]);
				$_REQUEST['priority'][] = '0';
			}
			if ($var == '-2') {
				$_REQUEST['priority'] = array();
			}
		}
		
		if (count($_REQUEST['priority'])) {
			$dosql = array2sql($_REQUEST['priority']);
			$where .= " AND priority IN $dosql";
		}

	} elseif ($_REQUEST['priority']) {
		$where .= " AND priority = '" . mysql_escape_string($_REQUEST[priority]) . "'";
	}

	// tech owner (array)
	if (is_array($_REQUEST['tech'])) {

		// need to remove -1, array search unreliable
		foreach($_REQUEST['tech'] AS $key => $var) {
			if ($var == '-1') {
				$where .= " AND !tech";
				$breaking++;
				break;
			}
			if ($var == '-2') {
				$breaking++;
				break;
			}
		}
	
		if (!$breaking) {
			if ($user['p_tech_view']) {
				$dosql = array2sql($_REQUEST['tech']);
				$where .= " AND tech IN $dosql";
			} else {
				$where .= " AND tech = '$user[id]'";
			}
		}
	} elseif ($_REQUEST['tech'] == 'assigned' AND $user['p_tech_view']) { // List *others'* tickets
		$where .= " AND tech != $user[id] AND tech";
	} elseif ($_REQUEST['tech']) {
		if ($_REQUEST['tech'] == '-1') {
			$where .= " AND !tech";
			$_REQUEST['tech'] = '0';
		} else {
			$where .= " AND tech = '" . mysql_escape_string($_REQUEST[tech]) . "'";
		}
	}

	////////////////////////// SPECIFY CERTAIN TICKET FIELDS //////////////////////////
	
	// on these we don't want anything if both match

	// open / closed status
	if ($_REQUEST['unresolved'] == "1" AND !$_REQUEST['resolved']) {
		$where .= " AND is_open = 1";
		$form .= form_hidden('unresolved', 1);
	} elseif ($_REQUEST['resolved'] == "1" AND !$_REQUEST['unresolved']) {
		$where .= " AND is_open = 0";
		$form .= form_hidden('resolved', 1);
	}

	// ticket locked
	if ($_REQUEST['is_locked']) {
		$where .= " AND is_locked = '1'";
	}

	// awaiting tech/user
	if ($_REQUEST['awaiting_tech'] == "1" AND !$_REQUEST['awaiting_user']) {
		$where .= " AND awaiting_tech = 1";
	} elseif ($_REQUEST['awaiting_user'] == "1" AND !$_REQUEST['awaiting_tech']) {
		$where .= " AND awaiting_tech = 0";
	}

	// ticketid range
	if (isset($_REQUEST['lowid'])) {
		$where .= " AND id >= $_REQUEST[lowid]";
	}
	if (isset($_REQUEST['highid'])) {
		$where .= " AND id <= $_REQUEST[highid]";
	}

	// user id
	if ($_REQUEST['user_owner']) {
		$userdata = $db->query_return("SELECT id FROM user WHERE username = '" . mysql_escape_string($_REQUEST['user_owner']) . "'");
		if (!$db->num_rows()) {
			$userdata = $db->query_return("SELECT id FROM user WHERE email = '" . mysql_escape_string($_REQUEST['user_owner']) . "'");
		}
		if (is_array($userdata)) {
			$where .= " AND ticket.userid = $userdata[id]";
		}
	}

	// subject
	if ($_REQUEST['subject']) {
		if ($_REQUEST['subjectoption'] == 'PHRASE') {
			$where .= " AND subject LIKE '%" . mysql_escape_string($_REQUEST['subject']) . "%'";
		} else {
			if (!($_REQUEST['subjectoption'] == 'AND' OR $_REQUEST['subjectoption'] == 'OR')) {
				$_REQUEST['subjectoption'] = 'OR';
			}
			$words = explode(" ", $_REQUEST['subject']);
			$where .= ' AND (';
			for ($i=0; $i < count($words); $i++) {
				if ($i == "0") {
					$where .= " subject LIKE '%$words[$i]%' ";
				} else {
					$where .= " $_REQUEST[subjectoption] subject LIKE '%$words[$i]%' ";
				}
			}
			$where .= ') ';
		}
	}

	////////////////////////// TIME CALCULATIONS //////////////////////////

	if ($_REQUEST['ydate_started_start']) {
		$date1 = mktime (0, 0, 0, $_REQUEST['mdate_started_start'], $_REQUEST['ddate_started_start'], $_REQUEST['ydate_started_start']);
		$where .= " AND date_opened >= '$date1'";
	}
	if ($_REQUEST['ydate_started_end']) {
		$date2 = strtotime("$_REQUEST[ydate_started_end]-$_REQUEST[mdate_started_end]-$_REQUEST[ddate_started_end] +23 hours 59 minutes 59 seconds");
		$where .= " AND date_opened < '$date2'";
	}
	if ($_REQUEST['ydate_resolved_start']) {
		$date3 = mktime (0, 0, 0, $_REQUEST['mdate_resolved_start'], $_REQUEST['ddate_resolved_start'], $_REQUEST['ydate_resolved_start']);
		$where .= " AND ((date_closed >= '$date1') OR !date_closed)";
	}
	if ($_REQUEST['ydate_resolved_end']) {
		$date4 = strtotime("$_REQUEST[ydate_resolved_end]-$_REQUEST[mdate_resolved_end]-$_REQUEST[ddate_resolved_end] +23 hours 59 minutes 59 seconds");
		$where .= " AND ((date_closed < '$date2') OR !date_closed)";
	}
	if ($_REQUEST['ydate_activity_start']) {
		$date5 = strtotime("$_REQUEST[ydate_activity_start]-$_REQUEST[mdate_activity_start]-$_REQUEST[ddate_activity_start]");
		$where .= " AND ((date_lastreply_tech >= '$date1' OR date_lastreply >= '$date1') OR !date_lastreply)";
	}
	if ($_REQUEST['ydate_activity_end']) {
		$date6 = mktime (0, 0, 0, $_REQUEST['mdate_activity_end'], $_REQUEST['ddate_activity_end'], $_REQUEST['ydate_activity_end']);
		$where .= " AND ((date_lastreply_tech < '$date2' OR date_lastreply < '$date1') OR !date_lastreply)";
	}

	////////////////////////// CUSTOM TICKET FIELDS //////////////////////////

	$db->query('SELECT * FROM ticket_def WHERE tech_viewable ORDER BY displayorder');
	while ($result = $db->row_array()) {
		$tfields[$result['name']] = $result;
		$tname = unserialize($result[display_name]);
		$tname = $tname[$settings[default_language]];
		$ticket_fields[$result['name']] = array('display_name' => $tname, 'name' => $result['name']);
	}
	if (is_array($_REQUEST[custom_fields])) {
		foreach ($_REQUEST[custom_fields] AS $tkey => $tvar) {
			if (in_array($tkey, array_keys($ticket_fields))) {
				$tmp = field_search($tfields[$tkey], 
					$_REQUEST[custom_fields][$tkey],
					$_REQUEST[custom_fields]["extra$tkey"],
					'ticket',
					$_REQUEST[custom_fields][$tkey."_match"],
					$_REQUEST[custom_fields][$tkey."_not"]
				);
				if (trim($tmp)) {
					$where .= " AND $tmp";
				}
			}
		}
	}

	/*******************************************************
			PART 2: LIMIT BY TICKETS
	*******************************************************/

	if ($settings[ticket_number]) {
		$perpage = $settings[ticket_number];
	} else {
		$perpage = 20;
	}

	if ($_REQUEST['page']) {
		$page = $_REQUEST['page'];
	} else {
		$page = 1;
	}

	$start = $perpage * $page;
	$start = $start - $perpage;

	$limit = " LIMIT $start, $perpage";

	/*******************************************************
			PART 3: ORDERING
	*******************************************************/

	$where .= $disp;
	$where .= " GROUP by ticket.id";
	$where .= " ORDER by id ASC";

	/*******************************************************
			PART 4: COLUMN DISPLAY
	*******************************************************/

	$fields = unserialize($user[fielddisplay]);

	// need to display to tech who has not configured
	if (!is_array($fields)) {
	$fields = array(
			'default' => array('category', 'priority', 'email', 'techowner', 'timetech', 'timestart'),
			'admin' => array(),
			'custom' => array(),
			'other' => array('actions')
		);
	}

	////////////////////////// DISPLAY HEADERS //////////////////////////

	if (in_array('awaitingtu', $fields['default'])) {
		$columns[] = 'Awaiting';
	}
	if (in_array('openclosed', $fields['default'])) {
		$columns[] = 'Open/Closed';
	}
	if (in_array('priority', $fields['default'])) {
		$columns[] = 'Priority';
	}
	if (in_array('category', $fields['default'])) {
		$columns[] = 'Category';
	}
	if (in_array('email', $fields['default'])) {
		$columns[] = 'User';
	}
	if (in_array('techowner', $fields['default'])) {
		$columns[] = 'Tech';
	}
	if (in_array('timestart', $fields['default'])) {
		$columns[] = 'Since Opening';
	}
	if (in_array('timetech', $fields['default'])) {
		$columns[] = 'Since Reply';
	}

	if (is_array($fields['other'])) {
		if (!in_array('actions', $fields['other'])) {
			$noact = 1;
		}
	}

	foreach ($fields['custom'] AS $key => $var) {
		if (is_array($ticket_fields['custom'.$var])) {
			$columns[] = $ticket_fields['custom'.$var]['display_name'];
			$showfield[] = $ticket_fields['custom'.$var]['name'];
			$ticket_custom[$ticket_data[name]] = field_display($ticket_fields['custom'.$var], $ticket[$ticket_data[name]]);
		}
	}

	// display headers for admin custom fields
	$sql = array2sql($fields['admin']);
	if ($sql) {
		$db->query("SELECT * FROM ticket_fielddisplay WHERE id IN $sql");
		while ($fielddisplay = $db->row_array()) {
			$columns[] = $fielddisplay[name];
			$admincols[] = $fielddisplay[code];
		}
	}

	/*******************************************************
			PART 5: RUN QUERYS
	*******************************************************/