/**
* Looks to see if an attachment is from the helpdesk, if so we validate it and return a proper request. SMF will handle the rest.
*
* @param string &$attachRequest A resource handle.
*
* @since 2.0
*/
function shd_download_request(&$attachRequest)
{
global $smcFunc;
// Is this already a resource? Then another hook has claimed the attachment as theirs.
if (is_resource($attachRequest)) {
return;
}
// If we don't have a ticket present, it is not our attachment.
if (empty($_REQUEST['ticket'])) {
return;
}
$_REQUEST['ticket'] = (int) $_REQUEST['ticket'];
// First we check that we can see said ticket and figure out what department we're in.
$request = shd_db_query('', '
SELECT hdt.id_dept
FROM {db_prefix}helpdesk_tickets AS hdt
WHERE id_ticket = {int:ticket}
AND {query_see_ticket}', array('ticket' => $_REQUEST['ticket']));
// If there's a row, we need to process it and then issue the follow on query. If not, fall through to the next cut-off point, outside of this edit.
if ($smcFunc['db_num_rows']($request) != 0) {
list($dept) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
// Now check their permission. If they don't have permission to view in this department, bye.
shd_is_allowed_to('shd_view_attachment', $dept);
// Make sure the attachment is on this ticket, note right now we're forcing it to be "approved"
$attachRequest = shd_db_query('', '
SELECT a.id_folder, a.filename, a.file_hash, a.fileext, a.id_attach, a.attachment_type, a.mime_type, 1 AS approved, hdtr.id_member
FROM {db_prefix}attachments AS a
INNER JOIN {db_prefix}helpdesk_attachments AS hda ON (a.id_attach = hda.id_attach)
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr ON (hda.id_msg = hdtr.id_msg)
INNER JOIN {db_prefix}helpdesk_tickets AS hdt ON (hdtr.id_ticket = hdt.id_ticket)
WHERE a.id_attach = {int:attach}
AND hdt.id_ticket = {int:ticket}
LIMIT 1', array('attach' => $_REQUEST['attach'], 'ticket' => (int) $_REQUEST['ticket']));
}
}
function shd_get_unread_departments()
{
global $context, $smcFunc;
$query = shd_db_query('', '
SELECT hdd.id_dept, MAX(hdt.id_last_msg) AS last_msg, MAX(hdlr.id_msg) AS last_read
FROM {db_prefix}helpdesk_depts AS hdd
INNER JOIN {db_prefix}helpdesk_tickets AS hdt ON (hdd.id_dept = hdt.id_dept)
LEFT JOIN {db_prefix}helpdesk_log_read AS hdlr ON (hdt.id_ticket = hdlr.id_ticket AND hdlr.id_member = {int:user_id})
WHERE hdd.id_dept IN ({array_int:dept_list})
AND {query_see_ticket}
AND hdt.last_updated > {int:the_last_week}
GROUP BY hdd.id_dept', array('dept_list' => array_keys($context['dept_list']), 'the_last_week' => time() - 86400 * 7, 'user_id' => $context['user']['id']));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$row['last_read'] = (int) $row['last_read'];
if ($row['last_msg'] > $row['last_read']) {
$context['dept_list'][$row['id_dept']]['new'] = true;
}
}
}
/**
* Gather the data and prepare to display the ticket blocks.
*
* Actually performs the queries to get data for each block, subject to the parameters specified by the calling functions.
*
* It also sets up per-block pagination links, collects a variety of data (enough to populate all the columns as listed in shd_main_helpdesk,
* even if not entirely applicable, and populates it all into $context['ticket_blocks']['tickets'], extending the array that was
* already there.
*
* @see shd_main_helpdesk()
* @see shd_closed_tickets()
* @see shd_recycle_bin()
* @since 1.0
*/
function shd_helpdesk_listing()
{
global $context, $txt, $smcFunc, $user_profile, $scripturl, $settings, $user_info, $modSettings, $language;
if (!empty($context['shd_permission'])) {
shd_is_allowed_to($context['shd_permission']);
}
$block_list = array_keys($context['ticket_blocks']);
$primary_url = '?action=helpdesk;sa=' . $_REQUEST['sa'];
// First figure out the start positions of each item and sanitise them
foreach ($context['ticket_blocks'] as $block_key => $block) {
if (empty($block['viewing_as_block'])) {
$num_per_page = !empty($context['shd_preferences']['blocks_' . $block_key . '_count']) ? $context['shd_preferences']['blocks_' . $block_key . '_count'] : $context['items_per_page'];
$start = empty($_REQUEST['st_' . $block_key]) ? 0 : (int) $_REQUEST['st_' . $block_key];
$max_value = $block['count'];
// easier to read
} else {
$num_per_page = $context['items_per_page'];
$max_value = $context['items_per_page'];
$start = 0;
}
if ($start < 0) {
$start = 0;
} elseif ($start >= $max_value) {
$start = max(0, (int) $max_value - ((int) $max_value % (int) $num_per_page == 0 ? $num_per_page : (int) $max_value % (int) $num_per_page));
} else {
$start = max(0, (int) $start - (int) $start % (int) $num_per_page);
}
$context['ticket_blocks'][$block_key]['start'] = $start;
$context['ticket_blocks'][$block_key]['num_per_page'] = $num_per_page;
if ($start != 0) {
$_REQUEST['st_' . $block_key] = $start;
} elseif (isset($_REQUEST['st_' . $block_key])) {
unset($_REQUEST['st_' . $block_key]);
}
}
// Now ordering the columns, separate loop for breaking the two processes apart
$sort_methods = array('ticketid' => array('sql' => 'hdt.id_ticket'), 'ticketname' => array('sql' => 'hdt.subject'), 'replies' => array('sql' => 'hdt.num_replies'), 'allreplies' => array('sql' => '(hdt.num_replies + hdt.deleted_replies)'), 'urgency' => array('sql' => 'hdt.urgency'), 'updated' => array('sql' => 'hdt.last_updated'), 'assigned' => array('sql' => 'assigned_name', 'sql_select' => 'IFNULL(mem.real_name, 0) AS assigned_name', 'sql_join' => 'LEFT JOIN {db_prefix}members AS mem ON (hdt.id_member_assigned = mem.id_member)'), 'status' => array('sql' => 'hdt.status'), 'starter' => array('sql' => 'starter_name', 'sql_select' => 'IFNULL(mem.real_name, 0) AS starter_name', 'sql_join' => 'LEFT JOIN {db_prefix}members AS mem ON (hdt.id_member_started = mem.id_member)'), 'lastreply' => array('sql' => 'last_reply', 'sql_select' => 'IFNULL(mem.real_name, 0) AS last_reply', 'sql_join' => 'LEFT JOIN {db_prefix}members AS mem ON (hdtr_last.id_member = mem.id_member)'));
foreach ($context['ticket_blocks'] as $block_key => $block) {
$sort = isset($_REQUEST['so_' . $block_key]) ? $_REQUEST['so_' . $block_key] : (!empty($context['shd_preferences']['block_order_' . $block_key . '_block']) ? $context['shd_preferences']['block_order_' . $block_key . '_block'] : '');
if (strpos($sort, '_') > 0 && substr_count($sort, '_') == 1) {
list($sort_item, $sort_dir) = explode('_', $sort);
if (empty($sort_methods[$sort_item])) {
$sort_item = 'updated';
$sort = '';
}
if (!in_array($sort_dir, array('asc', 'desc'))) {
$sort = '';
$sort_dir = 'asc';
}
} else {
$sort = '';
$sort_item = 'updated';
$sort_dir = $_REQUEST['sa'] == 'closedtickets' || $_REQUEST['sa'] == 'recyclebin' ? 'desc' : 'asc';
// default to newest first if on recyclebin or closed tickets, otherwise oldest first
}
if ($sort != '') {
$_REQUEST['so_' . $block_key] = $sort;
} elseif (isset($_REQUEST['so_' . $block_key])) {
unset($_REQUEST['so_' . $block_key]);
}
$context['ticket_blocks'][$block_key]['sort'] = array('item' => $sort_item, 'direction' => $sort_dir, 'add_link' => $sort != '', 'sql' => array('select' => !empty($sort_methods[$sort_item]['sql_select']) ? $sort_methods[$sort_item]['sql_select'] : '', 'join' => !empty($sort_methods[$sort_item]['sql_join']) ? $sort_methods[$sort_item]['sql_join'] : '', 'sort' => $sort_methods[$sort_item]['sql'] . ' ' . strtoupper($sort_dir)), 'link_bits' => array());
}
// Having got all that, step through the blocks again to determine the full URL fragments
foreach ($context['ticket_blocks'] as $block_key => $block) {
foreach ($sort_methods as $method => $sort_details) {
$context['ticket_blocks'][$block_key]['sort']['link_bits'][$method] = ';so_' . $block_key . '=' . $method . '_' . $block['sort']['direction'];
}
}
// Figure out if the user is filtering on anything, and if so, set up containers for the extra joins, selects, pagination link fragments, etc
$_REQUEST['field'] = isset($_REQUEST['field']) ? (int) $_REQUEST['field'] : 0;
$_REQUEST['filter'] = isset($_REQUEST['filter']) ? (int) $_REQUEST['filter'] : 0;
if ($_REQUEST['field'] > 0 && $_REQUEST['filter'] > 0) {
$context['filter_fragment'] = ';field=' . $_REQUEST['field'] . ';filter=' . $_REQUEST['filter'];
$context['filter_join'] = '
INNER JOIN {db_prefix}helpdesk_custom_fields_values AS hdcfv ON (hdcfv.id_post = hdt.id_ticket AND hdcfv.id_field = {int:field} AND hdcfv.post_type = {int:type_ticket})
INNER JOIN {db_prefix}helpdesk_custom_fields AS hdcf ON (hdcf.id_field = hdcfv.id_field AND hdcf.active = {int:active})';
$context['filter_where'] = '
AND hdcfv.value = {string:filter}';
} else {
$context['filter_fragment'] = '';
$context['filter_join'] = '';
$context['filter_where'] = '';
}
// Now go actually do the whole block thang, setting up space for a list of users and tickets as we go along
$users = array();
$tickets = array();
foreach ($context['ticket_blocks'] as $block_key => $block) {
if (empty($block['display']) || !empty($block['collapsed'])) {
continue;
}
$context['ticket_blocks'][$block_key]['tickets'] = array();
// If we're filtering, we have to query it first to figure out how many rows there are in this block. It's not pretty.
if (!empty($context['filter_join'])) {
$query = shd_db_query('', '
SELECT COUNT(hdt.id_ticket)
FROM {db_prefix}helpdesk_tickets AS hdt
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr_first ON (hdt.id_first_msg = hdtr_first.id_msg)
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr_last ON (hdt.id_last_msg = hdtr_last.id_msg)
INNER JOIN {db_prefix}helpdesk_depts AS hdd ON (hdt.id_dept = hdd.id_dept)
' . (!empty($block['sort']['sql']['join']) ? $block['sort']['sql']['join'] : '') . $context['filter_join'] . '
WHERE {query_see_ticket}' . (!empty($block['where']) ? ' AND ' . $block['where'] : '') . (!empty($context['shd_department']) ? ' AND hdt.id_dept = {int:dept}' : '') . $context['filter_where'], array('dept' => $context['shd_department'], 'user' => $context['user']['id'], 'field' => $_REQUEST['field'], 'filter' => $_REQUEST['filter'], 'type_ticket' => CFIELD_TICKET, 'active' => 1));
list($context['ticket_blocks'][$block_key]['count']) = $smcFunc['db_fetch_row']($query);
$block['count'] = $context['ticket_blocks'][$block_key]['count'];
$smcFunc['db_free_result']($query);
if ($block['start'] >= $block['count']) {
$context['ticket_blocks'][$block_key]['start'] = max(0, (int) $block['count'] - ((int) $block['count'] % (int) $block['num_per_page'] == 0 ? $block['num_per_page'] : (int) $block['count'] % (int) $block['num_per_page']));
$block['start'] = $context['ticket_blocks'][$block_key]['start'];
}
}
$query = shd_db_query('', '
SELECT hdt.id_ticket, hdt.id_dept, hdd.dept_name, hdt.id_last_msg, hdt.id_member_started, hdt.id_member_updated,
hdt.id_member_assigned, hdt.subject, hdt.status, hdt.num_replies, hdt.deleted_replies, hdt.private, hdt.urgency,
hdt.last_updated, hdtr_first.poster_name AS ticket_opener, hdtr_last.poster_name AS respondent, hdtr_last.poster_time,
IFNULL(hdlr.id_msg, 0) AS log_read' . (!empty($block['sort']['sql']['select']) ? ', ' . $block['sort']['sql']['select'] : '') . '
FROM {db_prefix}helpdesk_tickets AS hdt
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr_first ON (hdt.id_first_msg = hdtr_first.id_msg)
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr_last ON (hdt.id_last_msg = hdtr_last.id_msg)
INNER JOIN {db_prefix}helpdesk_depts AS hdd ON (hdt.id_dept = hdd.id_dept)
LEFT JOIN {db_prefix}helpdesk_log_read AS hdlr ON (hdt.id_ticket = hdlr.id_ticket AND hdlr.id_member = {int:user})
' . (!empty($block['sort']['sql']['join']) ? $block['sort']['sql']['join'] : '') . $context['filter_join'] . '
WHERE {query_see_ticket}' . (!empty($block['where']) ? ' AND ' . $block['where'] : '') . (!empty($context['shd_department']) ? ' AND hdt.id_dept = {int:dept}' : '') . $context['filter_where'] . '
ORDER BY ' . (!empty($block['sort']['sql']['sort']) ? $block['sort']['sql']['sort'] : 'hdt.id_last_msg ASC') . '
LIMIT {int:start}, {int:items_per_page}', array('dept' => $context['shd_department'], 'user' => $context['user']['id'], 'start' => $block['start'], 'items_per_page' => $block['num_per_page'], 'field' => $_REQUEST['field'], 'filter' => $_REQUEST['filter'], 'type_ticket' => CFIELD_TICKET, 'active' => 1));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$is_own = $user_info['id'] == $row['id_member_started'];
censorText($row['subject']);
$new_block = array('id' => $row['id_ticket'], 'display_id' => str_pad($row['id_ticket'], $modSettings['shd_zerofill'], '0', STR_PAD_LEFT), 'dept_link' => empty($context['shd_department']) && $context['shd_multi_dept'] ? '[<a href="' . $scripturl . '?' . $context['shd_home'] . ';dept=' . $row['id_dept'] . '">' . $row['dept_name'] . '</a>] ' : '', 'link' => '<a href="' . $scripturl . '?action=helpdesk;sa=ticket;ticket=' . $row['id_ticket'] . ($_REQUEST['sa'] == 'recyclebin' ? ';recycle' : '') . '">' . $row['subject'] . '</a>', 'subject' => $row['subject'], 'status' => array('level' => $row['status'], 'label' => $txt['shd_status_' . $row['status']]), 'starter' => array('id' => $row['id_member_started'], 'name' => $row['ticket_opener']), 'last_update' => timeformat($row['last_updated']), 'assigned' => array('id' => $row['id_member_assigned']), 'respondent' => array('id' => $row['id_member_updated'], 'name' => $row['respondent']), 'urgency' => array('level' => $row['urgency'], 'label' => $row['urgency'] > TICKET_URGENCY_HIGH ? '<span class="error">' . $txt['shd_urgency_' . $row['urgency']] . '</span>' : $txt['shd_urgency_' . $row['urgency']]), 'is_unread' => $row['id_last_msg'] > $row['log_read'], 'new_href' => $row['id_last_msg'] <= $row['log_read'] ? '' : $scripturl . '?action=helpdesk;sa=ticket;ticket=' . $row['id_ticket'] . '.new' . ($_REQUEST['sa'] == 'recyclebin' ? ';recycle' : '') . '#new', 'private' => $row['private'], 'actions' => array('movedept' => !empty($context['shd_multi_dept']) && (shd_allowed_to('shd_move_dept_any', $context['shd_department']) || $is_own && shd_allowed_to('shd_move_dept_own', $context['shd_department'])) ? '<a href="' . $scripturl . '?action=helpdesk;sa=movedept;ticket=' . $row['id_ticket'] . ';home;' . $context['session_var'] . '=' . $context['session_id'] . '" title="' . $txt['shd_move_dept'] . '"><img src="' . $settings['default_images_url'] . '/simpledesk/movedept.png" alt="' . $txt['shd_move_dept'] . '" /></a>' : ''), 'num_replies' => $row['num_replies'], 'replies_href' => $scripturl . '?action=helpdesk;sa=ticket;ticket=' . $row['id_ticket'] . '.msg' . $row['id_last_msg'] . '#msg' . $row['id_last_msg'] . ($_REQUEST['sa'] == 'recyclebin' ? ';recycle' : ''), 'all_replies' => (int) $row['num_replies'] + (int) $row['deleted_replies']);
if ($row['status'] == TICKET_STATUS_CLOSED) {
$new_block['actions'] += array('resolve' => shd_allowed_to('shd_unresolve_ticket_any', $context['shd_department']) || $is_own && shd_allowed_to('shd_unresolve_ticket_own', $context['shd_department']) ? '<a href="' . $scripturl . '?action=helpdesk;sa=resolveticket;ticket=' . $row['id_ticket'] . ';home;' . $context['shd_dept_link'] . $context['session_var'] . '=' . $context['session_id'] . '" title="' . $txt['shd_ticket_unresolved'] . '"><img src="' . $settings['default_images_url'] . '/simpledesk/unresolved.png" alt="' . $txt['shd_ticket_unresolved'] . '" /></a>' : '');
} elseif ($row['status'] == TICKET_STATUS_DELETED) {
$new_block['actions'] += array('restore' => shd_allowed_to('shd_restore_ticket_any', $context['shd_department']) || $is_own && shd_allowed_to('shd_restore_ticket_own', $context['shd_department']) ? '<a href="' . $scripturl . '?action=helpdesk;sa=restoreticket;ticket=' . $row['id_ticket'] . ';home;' . $context['shd_dept_link'] . $context['session_var'] . '=' . $context['session_id'] . '" title="' . $txt['shd_ticket_restore'] . '"><img src="' . $settings['default_images_url'] . '/simpledesk/restore.png" alt="' . $txt['shd_ticket_restore'] . '" /></a>' : '', 'permadelete' => shd_allowed_to('shd_delete_recycling', $context['shd_department']) ? '<a href="' . $scripturl . '?action=helpdesk;sa=permadelete;ticket=' . $row['id_ticket'] . ';' . $context['session_var'] . '=' . $context['session_id'] . '" title="' . $txt['shd_delete_permanently'] . '" onclick="return confirm(' . JavaScriptEscape($txt['shd_delete_permanently_confirm']) . ');"><img src="' . $settings['default_images_url'] . '/simpledesk/delete.png" alt="' . $txt['shd_delete_permanently'] . '" /></a>' : '');
} else {
$langstring = '';
if (shd_allowed_to('shd_assign_ticket_any', $context['shd_department'])) {
$langstring = empty($row['id_member_assigned']) ? $txt['shd_ticket_assign'] : $txt['shd_ticket_reassign'];
} elseif (shd_allowed_to('shd_assign_ticket_own', $context['shd_department']) && (empty($row['id_member_assigned']) || $row['id_member_assigned'] == $context['user']['id'])) {
$langstring = $row['id_member_assigned'] == $context['user']['id'] ? $txt['shd_ticket_unassign'] : $txt['shd_ticket_assign_self'];
}
if (!empty($langstring)) {
$new_block['actions']['assign'] = '<a href="' . $scripturl . '?action=helpdesk;sa=assign;ticket=' . $row['id_ticket'] . ';home;' . $context['session_var'] . '=' . $context['session_id'] . '" title="' . $langstring . '"><img src="' . $settings['default_images_url'] . '/simpledesk/assign.png" alt="' . $langstring . '" /></a>';
}
$new_block['actions'] += array('resolve' => shd_allowed_to('shd_resolve_ticket_any', $context['shd_department']) || $is_own && shd_allowed_to('shd_resolve_ticket_own', $context['shd_department']) ? '<a href="' . $scripturl . '?action=helpdesk;sa=resolveticket;ticket=' . $row['id_ticket'] . ';home;' . $context['shd_dept_link'] . $context['session_var'] . '=' . $context['session_id'] . '" title="' . $txt['shd_ticket_resolved'] . '"><img src="' . $settings['default_images_url'] . '/simpledesk/resolved.png" alt="' . $txt['shd_ticket_resolved'] . '" /></a>' : '', 'tickettotopic' => empty($modSettings['shd_helpdesk_only']) && shd_allowed_to('shd_ticket_to_topic', $context['shd_department']) && ($row['deleted_replies'] == 0 || shd_allowed_to('shd_access_recyclebin')) ? '<a href="' . $scripturl . '?action=helpdesk;sa=tickettotopic;ticket=' . $row['id_ticket'] . ';' . $context['shd_dept_link'] . $context['session_var'] . '=' . $context['session_id'] . '" title="' . $txt['shd_ticket_move_to_topic'] . '"><img src="' . $settings['default_images_url'] . '/simpledesk/tickettotopic.png" alt="' . $txt['shd_ticket_move_to_topic'] . '" /></a>' : '', 'delete' => shd_allowed_to('shd_delete_ticket_any', $context['shd_department']) || $is_own && shd_allowed_to('shd_delete_ticket_own') ? '<a href="' . $scripturl . '?action=helpdesk;sa=deleteticket;ticket=' . $row['id_ticket'] . ';' . $context['shd_dept_link'] . ';' . $context['session_var'] . '=' . $context['session_id'] . '" title="' . $txt['shd_ticket_delete'] . '" onclick="return confirm(' . JavaScriptEscape($txt['shd_delete_confirm']) . ');"><img src="' . $settings['default_images_url'] . '/simpledesk/delete.png" alt="' . $txt['shd_ticket_delete'] . '" /></a>' : '');
}
$context['ticket_blocks'][$block_key]['tickets'][$row['id_ticket']] = $new_block;
$users[] = $row['id_member_started'];
$users[] = $row['id_member_updated'];
$users[] = $row['id_member_assigned'];
$tickets[$row['id_ticket']] = array();
}
$smcFunc['db_free_result']($query);
}
$users = array_unique($users);
if (!empty($users)) {
loadMemberData($users, false, 'minimal');
}
foreach ($context['ticket_blocks'] as $block_id => $block) {
if (empty($block['tickets'])) {
continue;
}
foreach ($block['tickets'] as $tid => $ticket) {
// Set up names and profile links for topic starter
if (!empty($user_profile[$ticket['starter']['id']])) {
// We found the name, so let's use their current name and profile link
$context['ticket_blocks'][$block_id]['tickets'][$tid]['starter']['name'] = $user_profile[$ticket['starter']['id']]['real_name'];
$context['ticket_blocks'][$block_id]['tickets'][$tid]['starter']['link'] = shd_profile_link($user_profile[$ticket['starter']['id']]['real_name'], $ticket['starter']['id']);
} else {
// We didn't, so keep using the name we found previously and don't make an actual link
$context['ticket_blocks'][$block_id]['tickets'][$tid]['starter']['link'] = $context['ticket_blocks'][$block_id]['tickets'][$tid]['starter']['name'];
}
// Set up names and profile links for assigned user
if ($ticket['assigned']['id'] == 0 || empty($user_profile[$ticket['assigned']['id']])) {
$context['ticket_blocks'][$block_id]['tickets'][$tid]['assigned']['name'] = $txt['shd_unassigned'];
$context['ticket_blocks'][$block_id]['tickets'][$tid]['assigned']['link'] = '<span class="error">' . $txt['shd_unassigned'] . '</span>';
} else {
$context['ticket_blocks'][$block_id]['tickets'][$tid]['assigned']['name'] = $user_profile[$ticket['assigned']['id']]['real_name'];
$context['ticket_blocks'][$block_id]['tickets'][$tid]['assigned']['link'] = shd_profile_link($user_profile[$ticket['assigned']['id']]['real_name'], $ticket['assigned']['id']);
}
// And last respondent
if ($ticket['respondent']['id'] == 0 || empty($user_profile[$ticket['respondent']['id']])) {
// Didn't find the name, so reuse what we have
$context['ticket_blocks'][$block_id]['tickets'][$tid]['respondent']['link'] = $context['ticket_blocks'][$block_id]['tickets'][$tid]['respondent']['name'];
} else {
$context['ticket_blocks'][$block_id]['tickets'][$tid]['respondent']['name'] = $user_profile[$ticket['respondent']['id']]['real_name'];
$context['ticket_blocks'][$block_id]['tickets'][$tid]['respondent']['link'] = shd_profile_link($user_profile[$ticket['respondent']['id']]['real_name'], $ticket['respondent']['id']);
}
}
}
foreach ($context['ticket_blocks'] as $block_id => $block) {
if (empty($block['display']) || empty($block['count']) && !$block['required'] && empty($block['collapsed'])) {
unset($context['ticket_blocks'][$block_id]);
}
}
$base_url = '';
foreach ($context['ticket_blocks'] as $block_id => $block) {
if ($block['sort']['add_link']) {
$base_url .= $block['sort']['link_bits'][$block['sort']['item']];
}
}
if ($_REQUEST['sa'] != 'viewblock') {
foreach ($context['ticket_blocks'] as $block_id => $block) {
$num_per_page = !empty($context['shd_preferences']['blocks_' . $block_key . '_count']) ? $context['shd_preferences']['blocks_' . $block_key . '_count'] : $context['items_per_page'];
$url_fragment = $base_url;
foreach ($block_list as $block_item) {
if ($block_item == $block_id) {
$url_fragment .= ';st_' . $block_item . '=%1$d';
} elseif (!empty($context['ticket_blocks'][$block_item]['start'])) {
$url_fragment .= ';st_' . $block_item . '=' . $context['ticket_blocks'][$block_item]['start'];
}
}
$context['start'] = $context['ticket_blocks'][$block_id]['start'];
$context['ticket_blocks'][$block_id]['page_index'] = shd_no_expand_pageindex($scripturl . $primary_url . $url_fragment . $context['shd_dept_link'] . $context['filter_fragment'] . '#shd_block_' . $block_id, $context['start'], $block['count'], $block['num_per_page'], true);
}
}
// Just need to deal with those pesky prefix fields, if there are any.
if (empty($tickets)) {
return;
}
// We're all done here.
// 1. Figure out if there are any custom fields that apply to us or not.
if ($context['shd_multi_dept'] && empty($context['shd_department'])) {
$dept_list = shd_allowed_to('access_helpdesk', false);
} else {
$dept_list = array($context['shd_department']);
}
$fields = array();
$query = $smcFunc['db_query']('', '
SELECT hdcf.id_field, can_see, field_type, field_options, placement, field_name
FROM {db_prefix}helpdesk_custom_fields AS hdcf
INNER JOIN {db_prefix}helpdesk_custom_fields_depts AS hdcfd ON (hdcfd.id_field = hdcf.id_field)
WHERE placement IN ({array_int:placement_prefix})
AND field_loc IN ({array_int:locations})
AND hdcfd.id_dept IN ({array_int:dept_list})
AND active = {int:active}
GROUP BY hdcf.id_field
ORDER BY field_order', array('locations' => array(CFIELD_TICKET, CFIELD_TICKET | CFIELD_REPLY), 'placement_prefix' => array(CFIELD_PLACE_PREFIX, CFIELD_PLACE_PREFIXFILTER), 'active' => 1, 'dept_list' => $dept_list));
$is_staff = shd_allowed_to('shd_staff', $context['shd_department']);
$is_admin = $context['user']['is_admin'] || shd_allowed_to('admin_helpdesk', $context['shd_department']);
$context['shd_filter_fields'] = array();
while ($row = $smcFunc['db_fetch_assoc']($query)) {
list($user_see, $staff_see) = explode(',', $row['can_see']);
if ($is_admin || $is_staff && $staff_see == '1' || !$is_staff && $user_see == '1') {
if (!empty($row['field_options'])) {
$row['field_options'] = unserialize($row['field_options']);
if (isset($row['field_options']['inactive'])) {
unset($row['field_options']['inactive']);
}
foreach ($row['field_options'] as $k => $v) {
if (strpos($v, '[') !== false) {
$row['field_options'][$k] = parse_bbc($v);
}
}
}
$fields[$row['id_field']] = $row;
if ($row['placement'] == CFIELD_PLACE_PREFIXFILTER) {
$context['shd_filter_fields'][$row['id_field']] = array('name' => $row['field_name'], 'options' => $row['field_options'], 'in_use' => array());
}
}
}
$smcFunc['db_free_result']($query);
if (empty($fields)) {
return;
}
// No fields to process, time to go.
// 2. Get the relevant values.
$query = $smcFunc['db_query']('', '
SELECT id_post, id_field, value
FROM {db_prefix}helpdesk_custom_fields_values
WHERE id_post IN ({array_int:tickets})
AND id_field IN ({array_int:fields})
AND post_type = {int:ticket}', array('tickets' => array_keys($tickets), 'fields' => array_keys($fields), 'ticket' => CFIELD_TICKET));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$tickets[$row['id_post']][$row['id_field']] = $row['value'];
}
// 3. Apply the values into the tickets.
if ($_REQUEST['sa'] == 'closedtickets') {
$context['filterbase'] = $scripturl . '?action=helpdesk;sa=closedtickets';
} elseif ($_REQUEST['sa'] == 'recyclebin') {
$context['filterbase'] = $scripturl . '?action=helpdesk;sa=recyclebin';
} else {
$context['filterbase'] = $scripturl . '?' . $context['shd_home'];
}
foreach ($context['ticket_blocks'] as $block_id => $block) {
if (empty($block['tickets'])) {
continue;
}
foreach ($block['tickets'] as $ticket_id => $ticket) {
if (isset($tickets[$ticket_id])) {
$prefix_filter = '';
$prefix = '';
foreach ($fields as $field_id => $field) {
if (empty($tickets[$ticket_id][$field_id])) {
continue;
}
if ($field['placement'] == CFIELD_PLACE_PREFIXFILTER) {
if (!isset($field['field_options'][$tickets[$ticket_id][$field_id]])) {
continue;
}
$prefix_filter .= '[<a href="' . $context['filterbase'] . $context['shd_dept_link'] . ';field=' . $field_id . ';filter=' . $tickets[$ticket_id][$field_id] . '">' . $field['field_options'][$tickets[$ticket_id][$field_id]] . '</a>] ';
} else {
if ($field['field_type'] == CFIELD_TYPE_CHECKBOX) {
$prefix .= !empty($tickets[$ticket_id][$field_id]) ? $txt['yes'] . ' ' : $txt['no'] . ' ';
} elseif ($field['field_type'] == CFIELD_TYPE_SELECT || $field['field_type'] == CFIELD_TYPE_RADIO) {
$prefix .= $field['field_options'][$tickets[$ticket_id][$field_id]] . ' ';
} elseif ($field['field_type'] == CFIELD_TYPE_MULTI) {
$values = explode(',', $tickets[$ticket_id][$field_id]);
foreach ($values as $value) {
$prefix .= $field['field_options'][$value] . ' ';
}
} else {
$prefix .= $tickets[$ticket_id][$field_id] . ' ';
}
}
}
// First, set aside the subject, and if there is a non category prefix, strip links from it.
$subject = $ticket['subject'];
if (!empty($prefix)) {
$prefix = '[' . trim(preg_replace('~<a (.*?)</a>~is', '', $prefix)) . '] ';
}
// Then, if we have a category prefix, prepend that to any other prefix we have.
if (!empty($prefix_filter)) {
$prefix = $prefix_filter . $prefix;
}
// Lastly, if we have some kind of prefix to put in front of this ticket, do so.
if (!empty($prefix)) {
$context['ticket_blocks'][$block_id]['tickets'][$ticket_id]['subject'] = $prefix . $subject;
$context['ticket_blocks'][$block_id]['tickets'][$ticket_id]['link'] = $prefix . '<a href="' . $scripturl . '?action=helpdesk;sa=ticket;ticket=' . $ticket_id . ($_REQUEST['sa'] == 'recyclebin' ? ';recycle' : '') . '">' . $subject . '</a>';
}
}
}
}
// 4. We've collected the list of prefix-filter fields in use, now establish which values are actually in use.
if (!empty($context['shd_filter_fields'])) {
$query = $smcFunc['db_query']('', '
SELECT id_field, value
FROM {db_prefix}helpdesk_custom_fields_values
WHERE id_field IN ({array_int:fields})', array('fields' => array_keys($context['shd_filter_fields'])));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$context['shd_filter_fields'][$row['id_field']]['in_use'][$row['value']] = true;
}
$smcFunc['db_free_result']($query);
foreach ($context['shd_filter_fields'] as $id_field => $field) {
if (empty($field['in_use'])) {
unset($context['shd_filter_fields'][$id_field]);
} else {
foreach ($field['options'] as $k => $v) {
if (!isset($field['in_use'][$k])) {
unset($context['shd_filter_fields'][$id_field]['options'][$k]);
}
}
if (empty($context['shd_filter_fields'][$id_field]['options'])) {
unset($context['shd_filter_fields'][$id_field]);
}
}
}
}
}
/**
* Handle moving a custom field up or down
*
* @since 2.0
*/
function shd_admin_custom_move()
{
global $context, $smcFunc, $modSettings;
checkSession('get');
$_REQUEST['field'] = isset($_REQUEST['field']) ? (int) $_REQUEST['field'] : 0;
$_REQUEST['direction'] = isset($_REQUEST['direction']) && in_array($_REQUEST['direction'], array('up', 'down')) ? $_REQUEST['direction'] : '';
$query = shd_db_query('', '
SELECT id_field, field_order
FROM {db_prefix}helpdesk_custom_fields', array());
if ($smcFunc['db_num_rows']($query) == 0 || empty($_REQUEST['direction'])) {
$smcFunc['db_free_result']($query);
fatal_lang_error('shd_admin_cannot_move_custom_field', false);
}
$fields = array();
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$fields[$row['field_order']] = $row['id_field'];
$fields_map[$row['id_field']] = $row['field_order'];
}
ksort($fields);
if (empty($fields_map[$_REQUEST['field']])) {
fatal_lang_error('shd_admin_cannot_move_custom_field', false);
}
$current_pos = $fields_map[$_REQUEST['field']];
$destination = $current_pos + ($_REQUEST['direction'] == 'up' ? -1 : 1);
if (empty($fields[$destination])) {
fatal_lang_error('shd_admin_cannot_move_custom_field_' . $_REQUEST['direction'], false);
}
$other_field = $fields[$destination];
shd_db_query('', '
UPDATE {db_prefix}helpdesk_custom_fields
SET field_order = {int:new_pos}
WHERE id_field = {int:field}', array('new_pos' => $destination, 'field' => $_REQUEST['field']));
shd_db_query('', '
UPDATE {db_prefix}helpdesk_custom_fields
SET field_order = {int:old_pos}
WHERE id_field = {int:other_field}', array('old_pos' => $current_pos, 'other_field' => $other_field));
redirectexit('action=admin;area=helpdesk_customfield;' . $context['session_var'] . '=' . $context['session_id']);
}
/**
* Checks dependencies of the current ticket to see if it can be closed.
*
* @return string Returns empty string if the ticket has no dependency issues, or returns the id for $txt to use as error message.
* @since 2.0
*/
function shd_check_dependencies()
{
global $context, $smcFunc;
if (!empty($modSettings['shd_disable_relationships'])) {
return '';
}
// OK, so what about any children related tickets that are still open? Eeek, could be awkward.
$query = shd_db_query('', '
SELECT COUNT(hdt.id_ticket)
FROM {db_prefix}helpdesk_relationships AS rel
INNER JOIN {db_prefix}helpdesk_tickets AS hdt ON (rel.secondary_ticket = hdt.id_ticket)
WHERE rel.primary_ticket = {int:ticket}
AND rel.rel_type = {int:parent}
AND hdt.status NOT IN ({array_int:closed_status})', array('ticket' => $context['ticket_id'], 'parent' => RELATIONSHIP_ISPARENT, 'closed_status' => array(TICKET_STATUS_CLOSED, TICKET_STATUS_DELETED)));
list($count_children) = $smcFunc['db_fetch_row']($query);
$smcFunc['db_free_result']($query);
if (!empty($count_children)) {
return 'shd_cannot_resolve_children';
}
return '';
}
/**
* Handles the actual assignment form, validates it and carries it out.
*
* Primarily this is just about receiving the form, making the same checks that {@link shd_assign()} does and then
* logging the action before passing over to {@link shd_commit_assignment()} to actually assign the ticket.
*
* @see shd_assign()
* @see shd_commit_assignment()
* @since 1.0
*/
function shd_assign2()
{
global $context, $smcFunc, $user_info, $sourcedir;
checkSession();
checkSubmitOnce('check');
if (empty($context['ticket_id'])) {
fatal_lang_error('shd_no_ticket');
}
$context['shd_return_to'] = isset($_REQUEST['home']) ? 'home' : 'ticket';
$assignee = isset($_REQUEST['to_user']) ? (int) $_REQUEST['to_user'] : 0;
// Get ticket details - and kick it out if they shouldn't be able to see it.
$query = shd_db_query('', '
SELECT id_member_started, id_member_assigned, private, subject, status, id_dept
FROM {db_prefix}helpdesk_tickets AS hdt
WHERE {query_see_ticket} AND id_ticket = {int:ticket}', array('ticket' => $context['ticket_id']));
$log_params = array();
if ($row = $smcFunc['db_fetch_row']($query)) {
list($ticket_starter, $ticket_owner, $private, $subject, $status, $dept) = $row;
// The core details that we'll be logging
$log_params = array('subject' => $subject, 'ticket' => $context['ticket_id']);
} else {
$smcFunc['db_free_result']($query);
fatal_lang_error('shd_no_ticket');
}
// Just in case, are they cancelling?
if (isset($_REQUEST['cancel'])) {
redirectexit('action=helpdesk;sa=ticket;ticket=' . $context['ticket_id']);
}
if ($status == TICKET_STATUS_CLOSED || $status == TICKET_STATUS_DELETED) {
fatal_lang_error('shd_cannot_assign', false);
}
if (shd_allowed_to('shd_assign_ticket_any', $dept)) {
if ($assignee == 0) {
shd_log_action('unassign', $log_params);
shd_commit_assignment($context['ticket_id'], 0);
} else {
$members = shd_get_possible_assignees($private, $ticket_starter, $dept);
if (in_array($assignee, $members)) {
global $user_profile;
loadMemberData($assignee, false, 'minimal');
$log_params += array('user_id' => $assignee, 'user_name' => $user_profile[$assignee]['real_name']);
shd_log_action('assign', $log_params);
shd_commit_assignment($context['ticket_id'], $assignee);
} else {
fatal_lang_error('shd_assigned_not_permitted', false);
}
}
} elseif (shd_allowed_to('shd_assign_ticket_own', $dept) && shd_allowed_to('shd_staff', $dept)) {
if ($ticket_owner == 0) {
$log_params += array('user_id' => $user_info['id'], 'user_name' => $user_info['name']);
shd_log_action('assign', $log_params);
shd_commit_assignment($context['ticket_id'], $user_info['id']);
} elseif ($ticket_starter == $user_info['id']) {
shd_log_action('unassign', $log_params);
shd_commit_assignment($context['ticket_id'], 0);
} else {
// oops, assigned to somebody else
fatal_lang_error('shd_cannot_assign_other', false);
}
} else {
fatal_lang_error('shd_cannot_assign', false);
}
}
/**
* Loads any custom fields that are active
*
* @param bool $is_ticket (default true) Whether to load custom fields based on editing a ticket or a message.
* @param int $ticketContext The appropriate value to load for; if editing a ticket this represents the ticket id, if editing a reply this represents the message id, if empty this is a new instance of either so no need to attempt loading data.
*
* @since 2.0
*/
function shd_load_custom_fields($is_ticket = true, $ticketContext = 0, $dept = 0)
{
global $sourcedir, $context, $smcFunc;
$field_values = array();
if (!empty($ticketContext)) {
$query = shd_db_query('', '
SELECT cfv.id_field, cfv.value
FROM {db_prefix}helpdesk_custom_fields_values AS cfv
WHERE cfv.id_post = {int:ticketContext}
AND cfv.post_type = {int:field_type}', array('ticketContext' => $ticketContext, 'field_type' => $is_ticket ? CFIELD_TICKET : CFIELD_REPLY));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$field_values[$row['id_field']] = $row['value'];
}
$smcFunc['db_free_result']($query);
}
// Load up our custom field defintions from the database
$custom_fields = shd_db_query('', '
SELECT cf.id_field, cf.active, cf.field_order, cf.field_name, cf.field_desc, cf.field_loc, cf.icon,
cf.field_type, cf.field_options, cf.default_value, cf.bbc, cf.can_see, cf.can_edit, cf.field_length,
cf.display_empty, cfd.required, cf.placement, cfd.id_dept
FROM {db_prefix}helpdesk_custom_fields AS cf
INNER JOIN {db_prefix}helpdesk_custom_fields_depts AS cfd ON (cf.id_field = cfd.id_field' . (!empty($dept) ? ' AND cfd.id_dept = {int:dept}' : '') . ')
WHERE cf.active = 1
AND cf.field_loc IN ({array_int:visibility})
ORDER BY cf.field_order', array('visibility' => $is_ticket ? array(CFIELD_TICKET, CFIELD_REPLY | CFIELD_TICKET) : array(CFIELD_REPLY, CFIELD_REPLY | CFIELD_TICKET), 'dept' => $dept));
$context['ticket_form']['custom_fields'] = array();
$loc = $is_ticket ? 'ticket' : $ticketContext;
$is_staff = shd_allowed_to('shd_staff', $dept);
$is_admin = shd_allowed_to('admin_helpdesk', $dept);
// this includes forum admins
// Loop through all fields and figure out where they should be.
while ($row = $smcFunc['db_fetch_assoc']($custom_fields)) {
// Can the user even see this field? If we can't see the field, it doesn't exist to us for posting purposes.
list($user_see, $staff_see) = explode(',', $row['can_see']);
list($user_edit, $staff_edit) = explode(',', $row['can_edit']);
if ($is_admin) {
$editable = true;
} elseif ($is_staff) {
if ($staff_see == 0) {
continue;
}
$editable = $staff_edit == 1;
} elseif ($user_see == 1) {
$editable = $user_edit == 1;
} else {
continue;
}
// Load up the fields and do some extra parsing
if (!isset($context['ticket_form']['custom_fields'][$loc][$row['id_field']])) {
$context['ticket_form']['custom_fields'][$loc][$row['id_field']] = array('id' => $row['id_field'], 'order' => $row['field_order'], 'location' => $row['field_loc'], 'length' => $row['field_length'], 'name' => $row['field_name'], 'desc' => parse_bbc($row['field_desc'], false), 'icon' => $row['icon'], 'options' => !empty($row['field_options']) ? unserialize($row['field_options']) : array(), 'type' => $row['field_type'], 'default_value' => $row['field_type'] == CFIELD_TYPE_LARGETEXT ? explode(',', $row['default_value']) : $row['default_value'], 'display_empty' => !empty($row['required']) ? 1 : $row['display_empty'], 'bbc' => !empty($row['bbc']), 'is_required' => $row['field_type'] == CFIELD_TYPE_MULTI ? (int) $row['required'] : !empty($row['required']), 'visible' => array($user_see, $staff_see), 'editable' => !empty($editable), 'depts' => array());
if ($row['field_type'] == CFIELD_TYPE_RADIO || $row['field_type'] == CFIELD_TYPE_MULTI) {
foreach ($context['ticket_form']['custom_fields'][$loc][$row['id_field']]['options'] as $k => $v) {
if ($k != 'inactive') {
$context['ticket_form']['custom_fields'][$loc][$row['id_field']]['options'][$k] = strpos($v, '[') !== false ? parse_bbc($v) : $v;
}
}
} elseif ($row['field_type'] == CFIELD_TYPE_SELECT) {
foreach ($context['ticket_form']['custom_fields'][$loc][$row['id_field']]['options'] as $k => $v) {
if ($k != 'inactive') {
$context['ticket_form']['custom_fields'][$loc][$row['id_field']]['options'][$k] = strpos($v, '[') !== false ? trim(strip_tags(parse_bbc($v))) : trim($v);
}
}
}
}
$context['ticket_form']['custom_fields'][$loc][$row['id_field']]['depts'][] = $row['id_dept'];
if (!empty($context['ticket_form']['custom_fields'][$loc][$row['id_field']]['options']) && empty($context['ticket_form']['custom_fields'][$loc][$row['id_field']]['options']['inactive'])) {
$context['ticket_form']['custom_fields'][$loc][$row['id_field']]['options']['inactive'] = array();
}
if (isset($field_values[$row['id_field']])) {
if ($context['ticket_form']['custom_fields'][$loc][$row['id_field']]['type'] == CFIELD_TYPE_MULTI) {
$field_values[$row['id_field']] = explode(',', $field_values[$row['id_field']]);
}
// Large text boxes may need fixing.
if ($context['ticket_form']['custom_fields'][$loc][$row['id_field']]['type'] == CFIELD_TYPE_LARGETEXT) {
require_once $sourcedir . '/Subs-Editor.php';
$field_values[$row['id_field']] = html_to_bbc($field_values[$row['id_field']]);
}
$context['ticket_form']['custom_fields'][$loc][$row['id_field']]['value'] = $field_values[$row['id_field']];
}
}
$context['ticket_form']['custom_fields_context'] = $loc;
}
/**
* Gets tickets based on supplied criteria; this is a helper function not really intended to be called directly.
*
* @todo Finish writing and documenting this function.
* @param string $query_where SQL clauses to be supplied to the query in addition to {query_see_ticket} - note 'AND' is not required at the start.
* @param array $query_where_params Key/value associative array to be injected into the query, related to $query_where.
* @param int $query_limit Number of items to limit the query to.
* @param string $query_order The clause to order tickets by, defaults to tickets by order of creation.
* @return array An array of arrays, each primary item containing the following:
* <ul>
* <li>id: Main ticket id</li>
* <li>display_id: Formatted ticket id in [0000x] format</li>
* <li>subject: Ticket subject</li>
* <li>short_subject: Shortened version of ticket subject</li>
* <li>href: Ticket href</li>
* <li>opener: array of details about the ticket starter:
* <ul>
* <li>id: user id of the person who opened the ticket</li>
* <li>name: username of the person who opened the ticket</li>
* <li>link: link to the profile of the person who opened the ticket</li>
* </ul>
* </li>
* <li>replier: array of details about the last person to reply to the ticket:
* <ul>
* <li>id: user id of the last person to reply to the ticket</li>
* <li>name: username of the last person to reply to the ticket</li>
* <li>link: link to the profile of the last person to reply to the ticket</li>
* </ul>
* </li>
* <li>assigned: array of details about the person who the ticket is assigned to:
* <ul>
* <li>id: user id of the person who the ticket is assigned to</li>
* <li>name: username of the person who the ticket is assigned to or 'Unassigned' otherwise</li>
* <li>link: link to the profile of the person who the ticket is assigned to or 'Unassigned' otherwise</li>
* </ul>
* </li>
* <li>num_replies: Number of replies in the ticket</li>
* <li>start_time: Formatted string of time the ticket was opened</li>
* <li>start_timestamp: Raw timestamp (adjusted for timezones) of ticket being opened</li>
* <li>last_time: Formatted string of time the ticket was last replied to</li>
* <li>last_timestamp: Raw timestamp (adjusted for timezones) of ticket's last reply</li>
* <li>private: Whether the ticket is private or not</li>
* <li>urgency_id: Number representing ticket urgency</li>
* <li>urgency_string: String representing ticket urgency</li>
* <li>status_id: Number representing ticket status</li>
* <li>status_text: String representing ticket status</li>
* <li>department: Number representing ticket department ID</li>
* </ul>
* @since 2.0
*/
function ssi_getSDTickets($query_where, $query_where_params = array(), $query_limit = 0, $query_order = 'hdt.id_ticket ASC', $output_method = 'echo')
{
global $smcFunc, $scripturl, $txt, $modSettings;
$query_limit = (int) $query_limit;
$query = shd_db_query('', '
SELECT hdt.id_ticket, hdt.subject, hdt.num_replies, hdt.private, hdt.urgency, hdt.status, hdt.dept,
hdtr_first.poster_time AS start_time, hdt.last_updated AS last_time,
IFNULL(mem.real_name, hdtr_first.poster_name) AS starter_name, IFNULL(mem.id_member, 0) AS starter_id,
IFNULL(ma.real_name, 0) AS assigned_name, IFNULL(ma.id_member, 0) AS assigned_id,
IFNULL(mm.real_name, hdtr_last.modified_name) AS modified_name, IFNULL(mm.id_member, 0) AS modified_id
FROM {db_prefix}helpdesk_tickets AS hdt
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr_first ON (hdt.id_first_msg = hdtr_first.id_msg)
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr_last ON (hdt.id_last_msg = hdtr_last.id_msg)
LEFT JOIN {db_prefix}members AS mem ON (hdt.id_member_started = mem.id_member)
LEFT JOIN {db_prefix}members AS ma ON (hdt.id_member_assigned = ma.id_member)
LEFT JOIN {db_prefix}members AS mm ON (hdt.id_member_updated = mm.id_member)
WHERE {query_see_ticket} AND ' . $query_where . '
ORDER BY ' . $query_order . '
' . ($query_limit == 0 ? '' : 'LIMIT ' . $query_limit), array_merge($query_where_params, array()));
$tickets = array();
while ($row = $smcFunc['db_fetch_assoc']($query)) {
censorText($row['subject']);
$tickets[] = array('id' => $row['id_ticket'], 'display_id' => str_pad($row['id_ticket'], $modSettings['shd_zerofill'], '0', STR_PAD_LEFT), 'subject' => $row['subject'], 'short_subject' => shorten_subject($row['subject'], 25), 'href' => $scripturl . '?action=helpdesk;sa=ticket;ticket=' . $row['id_ticket'], 'opener' => array('id' => $row['starter_id'], 'name' => $row['starter_name'], 'link' => shd_profile_link($row['starter_name'], $row['starter_id'])), 'replier' => array('id' => $row['modified_id'], 'name' => $row['modified_name'], 'link' => shd_profile_link($row['modified_name'], $row['modified_id'])), 'assigned' => array('id' => $row['assigned_id'], 'name' => empty($row['assigned_name']) ? $txt['shd_unassigned'] : $row['assigned_name'], 'link' => empty($row['assigned_name']) ? '<span class="error">' . $txt['shd_unassigned'] . '</span>' : shd_profile_link($row['assigned_name'], $row['assigned_id'])), 'start_time' => timeformat($row['start_time']), 'start_timestamp' => forum_time(true, $row['start_time']), 'last_time' => timeformat($row['last_time']), 'last_timestamp' => forum_time(true, $row['last_time']), 'num_replies' => $row['num_replies'], 'private' => !empty($row['private']), 'urgency_id' => $row['urgency'], 'urgency_string' => $txt['shd_urgency_' . $row['urgency']], 'status_id' => $row['status'], 'status_text' => $txt['shd_status_' . $row['status']], 'department' => $row['dept']);
}
$smcFunc['db_free_result']($query);
if (empty($tickets) || $output_method != 'echo') {
return $tickets;
}
// output this stuff
echo '
<table border="0" class="ssi_table">';
foreach ($tickets as $ticket) {
echo '
<tr>
<td align="right" valign="top" nowrap="nowrap">
[', $ticket['status_text'], ']
</td>
<td valign="top">
<a href="', $ticket['href'], '">', $ticket['subject'], '</a>
', $txt['by'], ' ', $ticket['replier']['link'], '
</td>
<td align="right" nowrap="nowrap">
', $ticket['last_time'], '
</td>
</tr>';
}
echo '
</table>';
}
/**
* Provide the list of possible notification recipients.
*
* @since 2.0
*/
function shd_ajax_notify()
{
global $txt, $context, $smcFunc, $user_profile, $modSettings, $sourcedir;
$session_check = checkSession('get', '', false);
// check the session but don't die fatally.
if (!empty($session_check)) {
return $context['ajax_return'] = array('error' => $txt[$session_check]);
}
shd_load_language('sd_language/SimpleDeskNotifications');
require_once $sourcedir . '/sd_source/SimpleDesk-Notifications.php';
if (!empty($context['ticket_id'])) {
$query = shd_db_query('', '
SELECT hdt.private, hdt.id_member_started, id_member_assigned, id_dept, status
FROM {db_prefix}helpdesk_tickets AS hdt
WHERE {query_see_ticket}
AND hdt.id_ticket = {int:ticket}', array('ticket' => $context['ticket_id']));
if ($smcFunc['db_num_rows']($query) != 0) {
$ticket = $smcFunc['db_fetch_assoc']($query);
}
$smcFunc['db_free_result']($query);
}
if (empty($ticket) || !shd_allowed_to('shd_singleton_email', $ticket['id_dept']) || $ticket['status'] == TICKET_STATUS_CLOSED || $ticket['status'] == TICKET_STATUS_DELETED) {
return $context['ajax_return'] = array('error' => $txt['shd_no_ticket']);
}
// So, we need to start figuring out who's going to be notified, who won't be and who we might be interested in notifying.
$notify_list = array('being_notified' => array(), 'optional' => array(), 'optional_butoff' => array());
// Let's get all the possible actual people. The possible people who can be notified... well, they're staff.
$staff = shd_get_visible_list($ticket['id_dept'], $ticket['private'], $ticket['id_member_started'], empty($modSettings['shd_admins_not_assignable']), false);
// Let's start figuring it out then! First, get the big ol' lists.
$query = $smcFunc['db_query']('', '
SELECT id_member, notify_state
FROM {db_prefix}helpdesk_notify_override
WHERE id_ticket = {int:ticket}', array('ticket' => $context['ticket_id']));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$notify_list[$row['notify_state'] == NOTIFY_NEVER ? 'optional_butoff' : 'being_notified'][$row['id_member']] = true;
}
// Did we exclude admins? If we did, we would have scooped the list of admins. If they're in the 'not being notified but you can...' list, remove them.
if (!empty($context['list_admin_exclude'])) {
foreach ($context['list_admin_exclude'] as $user_id) {
if (isset($notify_list['optional_butoff'][$user_id])) {
unset($notify_list['optional_butoff'][$user_id]);
}
}
}
// Now we get the list by preferences. This is where it starts to get complicated.
$possible_members = array();
// People who want replies to their own ticket, without including the ticket starter because they'd know about it...
if (!empty($modSettings['shd_notify_new_reply_own']) && $context['user']['id'] != $ticket['id_member_started']) {
$possible_members[$ticket['id_member_started']]['new_reply_own'] = true;
}
// The ticket is assigned to someone and they want to be notified if it changes.
if (!empty($modSettings['shd_notify_new_reply_assigned']) && !empty($ticket['id_member_assigned']) && $context['user']['id'] != $ticket['id_member_assigned']) {
$possible_members[$ticket['id_member_assigned']]['new_reply_assigned'] = true;
}
// So, if you're staff, and you've replied to this ticket before, do you want to be notified this time?
if (!empty($modSettings['shd_notify_new_reply_previous'])) {
$query = $smcFunc['db_query']('', '
SELECT id_member
FROM {db_prefix}helpdesk_ticket_replies
WHERE id_ticket = {int:ticket}
GROUP BY id_member', array('ticket' => $context['ticket_id']));
$responders = array();
while ($row = $smcFunc['db_fetch_row']($query)) {
$responders[] = $row[0];
}
// this shouldn't be nil, ever, because we're replying, so the topic already exists so there's at least one name in there...
$smcFunc['db_free_result']($query);
$responders = array_intersect($responders, $staff);
foreach ($responders as $id) {
$possible_members[$id]['new_reply_previous'] = true;
}
}
// If you're staff, did you have 'spam my inbox every single time' selected?
if (!empty($modSettings['shd_notify_new_reply_any'])) {
foreach ($staff as $id) {
$possible_members[$id]['new_reply_any'] = true;
}
}
// Now we have the list of possibles, exclude everyone who is either set to on, or off, since we don't need to query those for preferences.
foreach ($possible_members as $id => $type_list) {
if (isset($notify_list['being_notified'][$id]) || isset($notify_list['optional_butoff'][$id])) {
unset($possible_members[$id]);
}
}
if (!empty($possible_members)) {
// Get the default preferences
$prefs = shd_load_user_prefs(false);
$pref_groups = $prefs['groups'];
$base_prefs = $prefs['prefs'];
// Build a list of users -> default prefs. We know this is for the list of possible contenders only.
$member_prefs = array();
$pref_list = array();
foreach ($possible_members as $id => $type_list) {
foreach ($type_list as $type => $value) {
$member_prefs[$id][$type] = $base_prefs['notify_' . $type]['default'];
$pref_list['notify_' . $type] = true;
}
}
// Grab pref list from DB for these users and update
$query = $smcFunc['db_query']('', '
SELECT id_member, variable, value
FROM {db_prefix}helpdesk_preferences
WHERE id_member IN ({array_int:members})
AND variable IN ({array_string:variables})', array('members' => array_keys($possible_members), 'variables' => array_keys($pref_list)));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$row['id_member'] = (int) $row['id_member'];
$variable = substr($row['variable'], 7);
if (isset($member_prefs[$row['id_member']][$variable])) {
$member_prefs[$row['id_member']][$variable] = $row['value'];
}
}
$smcFunc['db_free_result']($query);
// unset $members where member pref doesn't indicate they want it on.
foreach ($member_prefs as $id => $value) {
foreach ($value as $pref_id => $pref_item) {
if (empty($pref_item)) {
unset($possible_members[$id][$pref_id]);
}
}
}
// Now, it's possible that we have a ticket that the starter can't see, but that their preferences would indicate they'd like a reply.
// What should be done here is to remove them from the automatic list, and make them part of the ping list instead.
if (!empty($ticket['id_member_started']) && !in_array($ticket['id_member_started'], $staff)) {
$possible_members[$ticket['id_member_started']] = array();
}
// Now the clever bit, we've taken everyone who wasn't on the normal notify list, and figured out what their preferences are.
// We now traverse $possible_members by id, if the array is empty, we know none of their preferences accounted for emails - so they're possible.
// Otherwise we add them to the list of being notified.
foreach ($possible_members as $id => $list) {
if (empty($list)) {
$notify_list['optional'][$id] = true;
} else {
$notify_list['being_notified'][$id] = true;
}
}
}
// By now we have three lists that include people who will be notified, people who could be notified, and people who don't really want to be.
// Let's translate that into a list of people that we can make use of.
$members = array_merge(array_keys($notify_list['being_notified']), array_keys($notify_list['optional']), array_keys($notify_list['optional_butoff']));
if (!empty($members)) {
// Get everyone's name.
$loaded = loadMemberData($members);
foreach ($loaded as $user) {
if (!empty($user_profile[$user]) && $user_profile[$user]['is_activated'] > 0 && $user_profile[$user]['is_activated'] < 10) {
// active & not banned
$people[$user] = array('id' => $user, 'name' => $user_profile[$user]['real_name']);
}
}
// Right, now let's step through and tidy up the three lists
foreach ($notify_list as $list_type => $list_members) {
foreach ($list_members as $id_member => $data) {
if (isset($people[$id_member]) && $id_member != $context['user']['id']) {
// We really shouldn't be in this list.
$list_members[$id_member] = $people[$id_member]['name'];
} else {
unset($list_members[$id_member]);
}
}
if (!empty($list_members)) {
asort($list_members);
array_walk($list_members, 'shd_format_notify_name', $ticket['id_member_started']);
$notify_list[$list_type] = $list_members;
} else {
unset($notify_list[$list_type]);
}
}
}
if (empty($notify_list) || empty($members)) {
return $context['ajax_raw'] = '<notify><![C' . 'DATA[' . cleanXml($txt['shd_ping_none']) . ']' . ']></notify>';
} else {
ob_start();
echo '<notify><![C', 'DATA[';
$selected = array();
if (!empty($_GET['list'])) {
$_GET['list'] = explode(',', $_GET['list']);
foreach ($_GET['list'] as $id) {
if ((int) $id > 0) {
$selected[] = (int) $id;
}
}
}
if (!empty($notify_list['being_notified'])) {
echo '<span class="shd_ajax_head">', $txt['shd_ping_already_' . (count($notify_list['being_notified']) == 1 ? '1' : 'n')], '</span><br />', implode(', ', $notify_list['being_notified']);
}
if (!empty($notify_list['optional'])) {
if (!empty($notify_list['being_notified'])) {
echo '<br /><br />';
}
echo '<span class="shd_ajax_head">', $txt['shd_ping_' . (count($notify_list['optional']) == 1 ? '1' : 'n')], '</span><br />';
foreach ($notify_list['optional'] as $id => $member) {
echo '<div class="shd_ajaxnotify"><input type="checkbox" name="notify[', $id, ']" value="', $id, '"', in_array($id, $selected) ? ' checked="checked"' : '', ' class="input_check" /> ', $member, '</div>';
}
}
if (!empty($notify_list['optional_butoff'])) {
if (!empty($notify_list['being_notified']) || !empty($notify_list['optional_butoff'])) {
echo '<br /><br />';
}
echo '<span class="shd_ajax_head">', $txt['shd_ping_none_' . (count($notify_list['optional_butoff']) == 1 ? '1' : 'n')], '</span><br />';
foreach ($notify_list['optional_butoff'] as $id => $member) {
echo '<div class="shd_ajaxnotify"><input type="checkbox" name="notify[', $id, ']" value="', $id, '"', in_array($id, $selected) ? ' checked="checked"' : '', ' class="input_check" /> ', $member, '</div>';
}
}
echo ']', ']></notify>';
$content = ob_get_clean();
return $context['ajax_raw'] = cleanXml($content);
}
}
/**
* Returns the total number of items in the helpdesk log.
*
* This function gets the total number of items logged in the helpdesk log, for the purposes of establishing the number of
* pages there should be in the page-index.
*
* @param string $clause An SQL fragment that forms a WHERE clause to limit log items, e.g. to load a specific ticket or specific member's log items.
*
* @return int Number of entries in the helpdesk action log table.
* @see shd_load_action_log_entries()
* @since 1.0
*/
function shd_count_action_log_entries($clause = '')
{
global $smcFunc;
$exclude = shd_action_log_exclusions();
if (!empty($exclude)) {
if (empty($clause)) {
$clause = 'la.action NOT IN ({array_string:exclude})';
} else {
$clause .= ' AND la.action NOT IN ({array_string:exclude})';
}
}
// Without further screaming and waving, fetch the actions.
$request = shd_db_query('', '
SELECT COUNT(*)
FROM {db_prefix}helpdesk_log_action AS la
LEFT JOIN {db_prefix}members AS mem ON(mem.id_member = la.id_member)
LEFT JOIN {db_prefix}membergroups AS mg ON (mg.id_group = CASE WHEN mem.id_group = {int:reg_group_id} THEN mem.id_post_group ELSE mem.id_group END)' . (empty($clause) ? '' : '
WHERE ' . $clause), array('reg_group_id' => 0, 'exclude' => $exclude));
list($entry_count) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
return $entry_count;
}
function shd_list_get_ip_message_count($where, $where_vars = array())
{
global $smcFunc;
$request = shd_db_query('', '
SELECT COUNT(id_msg) AS message_count
FROM {db_prefix}helpdesk_ticket_replies AS hdtr
INNER JOIN {db_prefix}helpdesk_tickets AS hdt ON (hdtr.id_ticket = hdt.id_ticket)
WHERE {query_see_ticket} AND ' . $where, $where_vars);
list($count) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
return $count;
}
/**
* Handles the actual assignment form, validates it and carries it out.
*
* Primarily this is just about receiving the form, making the same checks that {@link shd_movedept()} does and then
* logging the action before updating the database.
*
* @see shd_movedept()
* @since 2.0
*/
function shd_movedept2()
{
global $context, $smcFunc, $user_info, $sourcedir, $txt, $scripturl;
checkSession();
checkSubmitOnce('check');
if (empty($context['ticket_id'])) {
fatal_lang_error('shd_no_ticket', false);
}
if (isset($_POST['send_pm']) && (!isset($_POST['pm_content']) || trim($_POST['pm_content']) == '') && (empty($modSettings['shd_helpdesk_only']) || empty($modSettings['shd_disable_pm']))) {
fatal_lang_error('shd_move_no_pm', false);
}
// Just in case, are they cancelling?
if (isset($_REQUEST['cancel'])) {
redirectexit('action=helpdesk;sa=ticket;ticket=' . $context['ticket_id']);
}
if (empty($context['shd_multi_dept'])) {
fatal_lang_error('shd_cannot_move_dept', false);
}
$dest = isset($_REQUEST['to_dept']) ? (int) $_REQUEST['to_dept'] : 0;
if (empty($dest) || !shd_allowed_to('access_helpdesk', $dest)) {
fatal_lang_error('shd_cannot_move_dept', false);
}
$context['shd_return_to'] = isset($_REQUEST['home']) ? 'home' : 'ticket';
// Get ticket details - and kick it out if they shouldn't be able to see it.
$query = shd_db_query('', '
SELECT id_member_started, subject, hdt.id_dept, dept_name
FROM {db_prefix}helpdesk_tickets AS hdt
INNER JOIN {db_prefix}helpdesk_depts AS hdd ON (hdt.id_dept = hdd.id_dept)
WHERE {query_see_ticket} AND id_ticket = {int:ticket}', array('ticket' => $context['ticket_id']));
$log_params = array();
if ($row = $smcFunc['db_fetch_row']($query)) {
list($ticket_starter, $subject, $context['current_dept'], $context['current_dept_name']) = $row;
} else {
$smcFunc['db_free_result']($query);
fatal_lang_error('shd_no_ticket');
}
$smcFunc['db_free_result']($query);
if ($context['current_dept'] == $dest) {
fatal_lang_error('shd_cannot_move_dept', false);
}
if (shd_allowed_to('shd_move_dept_any', $context['current_dept']) || shd_allowed_to('shd_move_dept_own', $context['current_dept']) && $ticket_starter == $user_info['id']) {
// Find the new department. We've already established the user can see it, but we need its name.
$query = $smcFunc['db_query']('', '
SELECT id_dept, dept_name
FROM {db_prefix}helpdesk_depts
WHERE id_dept IN ({int:dest})', array('dest' => $dest));
list($new_dept, $dept_name) = $smcFunc['db_fetch_row']($query);
$smcFunc['db_free_result']($query);
// Just before we move, call any interesting hooks. We do normally have a lot of fun staff in $context and $_POST, but the department ID and name aren't in either.
call_integration_hook('shd_hook_movedept', array(&$new_dept, &$dept_name));
$log_params = array('subject' => $subject, 'ticket' => $context['ticket_id'], 'old_dept_id' => $context['current_dept'], 'old_dept_name' => $context['current_dept_name'], 'new_dept_id' => $new_dept, 'new_dept_name' => $dept_name);
shd_log_action('move_dept', $log_params);
$smcFunc['db_query']('', '
UPDATE {db_prefix}helpdesk_tickets
SET id_dept = {int:new_dept}
WHERE id_ticket = {int:ticket}', array('new_dept' => $new_dept, 'ticket' => $context['ticket_id']));
// Now, notify the ticket starter if that's what we wanted to do.
if (isset($_POST['send_pm'])) {
require_once $sourcedir . '/Subs-Post.php';
$request = shd_db_query('pm_find_username', '
SELECT id_member, real_name
FROM {db_prefix}members
WHERE id_member = {int:user}
LIMIT 1', array('user' => $ticket_starter));
list($userid, $username) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
// Fix the content
$replacements = array('{user}' => $username, '{subject}' => $subject, '{current_dept}' => $context['current_dept_name'], '{new_dept}' => $dept_name, '{link}' => $scripturl . '?action=helpdesk;sa=ticket;ticket=' . $context['ticket_id']);
$message = str_replace(array_keys($replacements), array_values($replacements), $_POST['pm_content']);
$recipients = array('to' => array($ticket_starter), 'bcc' => array());
sendpm($recipients, $txt['shd_ticket_moved_subject'], un_htmlspecialchars($message));
}
shd_clear_active_tickets($context['current_dept']);
shd_clear_active_tickets($new_dept);
if (!empty($context['shd_return_to']) && $context['shd_return_to'] == 'home') {
redirectexit($context['shd_home'] . ';dept=' . $new_dept);
} else {
redirectexit('action=helpdesk;sa=ticket;ticket=' . $context['ticket_id']);
}
} else {
fatal_lang_error('shd_no_perm_move_dept', false);
}
}
function shd_reply_restore()
{
global $smcFunc, $user_info, $context, $sourcedir;
checkSession('get');
$_REQUEST['reply'] = empty($_REQUEST['reply']) ? 0 : (int) $_REQUEST['reply'];
if (empty($_REQUEST['reply'])) {
fatal_lang_error('shd_no_ticket', false);
}
// Check we can actually see the ticket we're restoring from, and that we can restore this reply
$query_ticket = shd_db_query('', '
SELECT hdt.id_ticket, hdt.id_dept, hdtr.id_member, hdt.id_member_started, hdt.id_member_updated, hdt.num_replies, hdt.subject, hdt.status, hdtr.message_status
FROM {db_prefix}helpdesk_tickets AS hdt
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr ON (hdt.id_ticket = hdtr.id_ticket)
WHERE {query_see_ticket}
AND hdtr.id_msg = {int:reply}
AND hdt.id_first_msg != {int:reply2}', array('reply' => $_REQUEST['reply'], 'reply2' => $_REQUEST['reply']));
if ($row = $smcFunc['db_fetch_assoc']($query_ticket)) {
$smcFunc['db_free_result']($query_ticket);
if ($row['status'] == TICKET_STATUS_DELETED || $row['status'] == TICKET_STATUS_CLOSED || $row['message_status'] != MSG_STATUS_DELETED || !shd_allowed_to('shd_restore_reply_any', $row['id_dept']) && (!shd_allowed_to('shd_restore_reply_own', $row['id_dept']) || $user_info['id'] != $row['id_member'])) {
fatal_lang_error('shd_cannot_restore_reply', false);
}
$context['ticket_id'] = (int) $row['id_ticket'];
$subject = $row['subject'];
$starter = $row['id_member_started'];
$replier = $row['id_member_updated'];
$num_replies = $row['num_replies'];
} else {
$smcFunc['db_free_result']($query_ticket);
fatal_lang_error('shd_no_ticket', false);
}
// The ticket's id is in $context['ticket_id'], the reply id in $_REQUEST['reply'].
call_integration_hook('shd_hook_restorereply');
// OK, let's clear this one, hasta la vista... ticket.
shd_db_query('', '
UPDATE {db_prefix}helpdesk_ticket_replies
SET message_status = {int:msg_status_deleted}
WHERE id_msg = {int:reply}', array('msg_status_deleted' => MSG_STATUS_NORMAL, 'reply' => $_REQUEST['reply']));
// Captain's Log, stardate 18.3.10.1010
shd_log_action('restore_reply', array('ticket' => $context['ticket_id'], 'subject' => $subject, 'msg' => $_REQUEST['reply']));
// Fix the topic data
list($starter, $replier, $num_replies) = shd_recalc_ids($context['ticket_id']);
$query_reply = shd_db_query('', '
UPDATE {db_prefix}helpdesk_tickets
SET status = {int:status}
WHERE id_ticket = {int:ticket}', array('ticket' => $context['ticket_id'], 'status' => shd_determine_status('restorereply', $starter, $replier, $num_replies, $row['id_dept'])));
// Expire the cache of count(active tickets)
shd_clear_active_tickets($row['id_dept']);
redirectexit('action=helpdesk;sa=ticket;ticket=' . $context['ticket_id']);
}
/**
* Identifies the tickets related to a given ticket.
*
* Queries the system for all tickets related to the specified (typically current) ticket, subject to current user's permissions, and populates $context.
*
* The function populates $context['relationships_count'] with the number of relationships found, and $context['ticket_relationships'] contains an array of the following keys:
* - parent: Current ticket is the parent of the discovered ticket
* - child: Current ticket is a child of the discovered ticket
* - linked: Current ticket is related to discovered ticket
* - duplicated: Curent ticket is a duplicate of discovered ticket
*
* Each of the keyed arrays is an indexed array (each index representing one ticket), of which those are hash arrays containing:
* - id: id of the ticket
* - display_id: zero padded display id of the ticket
* - subject: ticket's name
* - status: status of the discovered ticket (numeric)
* - status_txt: status of the discovered ticket (textual)
*
* @since 2.0
*/
function shd_load_relationships($ticket = 0)
{
global $context, $smcFunc, $txt, $modSettings;
if ($ticket == 0) {
$ticket = $context['ticket_id'];
}
$reltypes = array(RELATIONSHIP_ISPARENT => 'parent', RELATIONSHIP_ISCHILD => 'child', RELATIONSHIP_LINKED => 'linked', RELATIONSHIP_DUPLICATED => 'duplicated');
$context['relationships_count'] = 0;
foreach ($reltypes as $type) {
$context['ticket_relationships'][$type] = array();
}
$query = shd_db_query('', '
SELECT hdt.id_ticket, hdt.subject, hdt.status, hdr.rel_type
FROM {db_prefix}helpdesk_relationships AS hdr
INNER JOIN {db_prefix}helpdesk_tickets AS hdt ON (hdr.secondary_ticket = hdt.id_ticket)
WHERE hdr.primary_ticket = {int:ticket}
AND {query_see_ticket}', array('ticket' => $ticket));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$context['relationships_count']++;
$context['ticket_relationships'][$reltypes[$row['rel_type']]][] = array('id' => $row['id_ticket'], 'display_id' => str_pad($row['id_ticket'], $modSettings['shd_zerofill'], '0', STR_PAD_LEFT), 'subject' => $row['subject'], 'status' => $row['status'], 'status_txt' => $txt['shd_status_' . $row['status']]);
}
$smcFunc['db_free_result']($query);
}
function shd_profile_show_notify_override($memID)
{
global $txt, $user_info, $scripturl, $modSettings, $smcFunc, $board, $user_profile, $context;
$context['notify_type'] = $_GET['sa'];
// We already checked it's monitor or ignore, if we didn't, we wouldn't be here!
$context['page_title'] = $txt['shd_profile_show_' . $context['notify_type'] . '_title'] . ' - ' . $user_profile[$memID]['real_name'];
$context['sub_template'] = 'shd_profile_show_notify_override';
// The active button.
$context['show_tickets_navigation'][$context['notify_type']]['active'] = true;
// Having got the general stuff out the way, let's do the specifics.
// Ticket, Name, Started By, Replies, Status, Urgency, Updated (+ Updated By?)
$context['tickets'] = array();
$query = shd_db_query('', '
SELECT hdt.id_ticket, hdt.subject, IFNULL(mem.id_member, 0) AS starter_id, IFNULL(mem.real_name, hdtr.poster_name) AS starter_name,
hdt.num_replies, hdt.status, hdt.urgency, hdt.last_updated
FROM {db_prefix}helpdesk_notify_override AS hdno
INNER JOIN {db_prefix}helpdesk_tickets AS hdt ON (hdno.id_ticket = hdt.id_ticket)
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr ON (hdt.id_first_msg = hdtr.id_msg)
LEFT JOIN {db_prefix}members AS mem ON (hdt.id_member_started = mem.id_member)
WHERE {query_see_ticket}
AND hdno.id_member = {int:user}
AND hdno.notify_state = {int:notify}
ORDER BY last_updated DESC', array('user' => $memID, 'notify' => $context['notify_type'] == 'monitor' ? NOTIFY_ALWAYS : NOTIFY_NEVER));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$row += array('id_ticket_display' => str_pad($row['id_ticket'], $modSettings['shd_zerofill'], '0', STR_PAD_LEFT), 'updated' => timeformat($row['last_updated']), 'ticket_starter' => shd_profile_link($row['starter_name'], $row['starter_id']));
$context['tickets'][] = $row;
}
}
/**
* Handles moving a topic into the helpdesk.
*
* After checking permissions, and so on, begin to actually move posts.
*
* Broadly this is done using {@link shd_create_ticket_post()}, which has hooks specifically to deal with post modification times (written in specifically to ease this function's workload)
*
* Operations:
* - get the topic information (and checking topic access permission in the process)
* - identify the status of the topic (new/with staff/with user)
* - create the new ticket from these details
* - assuming there are replies, query for them
* - step through and repost
* - send the notification PM if we're doing that
* - update the attachments table
* - update the action log
* - remove the topic from the forum
*
* @see shd_topictoticket()
* @since 1.0
*/
function shd_topictoticket2()
{
global $smcFunc, $context, $txt, $modSettings, $scripturl, $sourcedir;
checkSession();
checkSubmitOnce('check');
$_REQUEST['dept'] = isset($_REQUEST['dept']) ? (int) $_REQUEST['dept'] : 0;
if (empty($_REQUEST['dept'])) {
$_REQUEST['dept'] = -1;
}
// which is never a valid department!
if (!shd_allowed_to('shd_topic_to_ticket', $_REQUEST['dept']) || !empty($modSettings['shd_helpdesk_only']) || !empty($modSettings['shd_disable_tickettotopic'])) {
fatal_lang_error('shd_cannot_move_topic', false);
}
if (empty($_REQUEST['topic'])) {
fatal_lang_error('shd_no_topic');
}
$context['topic_id'] = (int) $_REQUEST['topic'];
// Just in case, are they cancelling?
if (isset($_REQUEST['cancel'])) {
redirectexit('topic=' . $context['topic_id']);
}
if (isset($_POST['send_pm']) && (!isset($_POST['pm_content']) || trim($_POST['pm_content']) == '')) {
fatal_lang_error('shd_move_no_pm_topic', false);
}
require_once $sourcedir . '/sd_source/Subs-SimpleDeskPost.php';
// Fetch the topic information.
$request = shd_db_query('', '
SELECT m.subject, t.id_board, t.id_member_started, m.body, t.id_first_msg, m.smileys_enabled, t.id_member_updated, t.num_replies,
m.poster_email, m.poster_name, m.poster_ip, m.poster_time, m.modified_time, m.modified_name, m.id_msg
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}messages AS m ON (m.id_msg = t.id_first_msg)
INNER JOIN {db_prefix}boards AS b ON (t.id_board = b.id_board)
WHERE {query_see_board} AND t.id_topic = {int:topic}
LIMIT 1', array('topic' => $context['topic_id']));
if ($smcFunc['db_num_rows']($request) == 0) {
fatal_lang_error('shd_move_ticket_not_created');
} else {
list($subject, $board, $owner, $body, $firstmsg, $smileys_enabled, $memberupdated, $numreplies, $postername, $posteremail, $posterip, $postertime, $modified_time, $modified_name, $smf_id_msg) = $smcFunc['db_fetch_row']($request);
}
$smcFunc['db_free_result']($request);
// Figure out what the status of the ticket should be.
$status = shd_determine_status('topictoticket', $owner, $memberupdated, $numreplies, $_REQUEST['dept']);
// Are we changing the subject?
$old_subject = $subject;
$subject = !empty($_POST['change_subject']) && !empty($_POST['subject']) ? $_POST['subject'] : $subject;
// Just before we do this, make sure we call any hooks. $context and $_POST have lots of interesting things for us.
call_integration_hook('shd_hook_topictoticket');
// All okay, it seems. Let's go create the ticket.
$msg_assoc = array();
$msgOptions = array('body' => $body, 'smileys_enabled' => !empty($smileys_enabled) ? 1 : 0, 'modified' => array('time' => $modified_time, 'name' => $modified_name), 'time' => $postertime);
$ticketOptions = array('dept' => $_REQUEST['dept'], 'subject' => $subject, 'mark_as_read' => false, 'private' => false, 'status' => $status, 'urgency' => 0, 'assigned' => 0);
$posterOptions = array('id' => $owner, 'name' => $postername, 'email' => $posteremail, 'ip' => $posterip);
shd_create_ticket_post($msgOptions, $ticketOptions, $posterOptions);
$msg_assoc[$smf_id_msg] = $msgOptions['id'];
// Ticket created, let's dig out the replies and post them in the ticket, if there are any.
if (isset($ticketOptions['id'])) {
$request = shd_db_query('', '
SELECT body, id_member, poster_time, poster_name, poster_email, poster_ip, smileys_enabled, id_msg
FROM {db_prefix}messages
WHERE id_topic = {int:topic}
AND id_msg != {int:topic_msg}', array('topic' => $context['topic_id'], 'topic_msg' => $firstmsg));
$num_replies = $smcFunc['db_num_rows']($request) + 1;
// Plus one since we want to count the main ticket post as well.
// The ID of the ticket we created
$ticket = $ticketOptions['id'];
if ($smcFunc['db_num_rows']($request) != 0) {
// Now loop through each reply and post it. Hopefully there aren't too many. *looks at clock*
while ($row = $smcFunc['db_fetch_assoc']($request)) {
$msgOptions = array('body' => $row['body'], 'smileys_enabled' => !empty($row['smileys_enabled']) ? 1 : 0);
$ticketOptions = array('id' => $ticket, 'mark_as_read' => false);
$posterOptions = array('id' => $row['id_member'], 'name' => !empty($row['poster_name']) ? $row['poster_name'] : '', 'email' => !empty($row['poster_email']) ? $row['poster_email'] : '', 'ip' => !empty($row['poster_ip']) ? $row['poster_ip'] : '');
shd_create_ticket_post($msgOptions, $ticketOptions, $posterOptions);
$msg_assoc[$row['id_msg']] = $msgOptions['id'];
}
}
// Ticket: check; Replies: check; Notfiy the topic starter, if desired.
if (isset($_POST['send_pm'])) {
require_once $sourcedir . '/Subs-Post.php';
$request = shd_db_query('pm_find_username', '
SELECT id_member, real_name
FROM {db_prefix}members
WHERE id_member = {int:user}
LIMIT 1', array('user' => $owner));
list($userid, $username) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
// Fix the content
$replacements = array('{user}' => $username, '{subject}' => $old_subject, '{link}' => $scripturl . '?action=helpdesk;sa=ticket;ticket=' . $ticket);
$message = str_replace(array_keys($replacements), array_values($replacements), $_POST['pm_content']);
$recipients = array('to' => array($owner), 'bcc' => array());
sendpm($recipients, $txt['shd_ticket_moved_subject_topic'], un_htmlspecialchars($message));
}
// And now for something completely different: attachments
if (!empty($msg_assoc)) {
// 1. Get all the attachments for these messages from the attachments table
$attachIDs = array();
$query = shd_db_query('', '
SELECT id_attach, id_msg
FROM {db_prefix}attachments
WHERE id_msg IN ({array_int:smf_msgs})', array('smf_msgs' => array_keys($msg_assoc)));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$attachIDs[] = $row;
}
$smcFunc['db_free_result']($query);
if (!empty($attachIDs)) {
// 2. Do the switch
// 2.1. Add them to SD's tables
$array = array();
foreach ($attachIDs as $attach) {
$array[] = array($attach['id_attach'], $ticket, $msg_assoc[$attach['id_msg']]);
}
$smcFunc['db_insert']('replace', '{db_prefix}helpdesk_attachments', array('id_attach' => 'int', 'id_ticket' => 'int', 'id_msg' => 'int'), $array, array('id_attach'));
// 2.2. "Remove" them from SMF's table
shd_db_query('', '
UPDATE {db_prefix}attachments
SET id_msg = 0
WHERE id_msg IN ({array_int:smf_msgs})', array('smf_msgs' => array_keys($msg_assoc)));
}
}
// Now we'll add this to the log.
$log_params = array('subject' => $subject, 'ticket' => $ticket);
shd_log_action('topictoticket', $log_params);
// Update post counts.
$request = shd_db_query('', '
SELECT id_member
FROM {db_prefix}messages
WHERE id_topic = {int:topic}', array('topic' => $context['topic_id']));
$posters = array();
while ($row = $smcFunc['db_fetch_assoc']($request)) {
if (!isset($posters[$row['id_member']])) {
$posters[$row['id_member']] = 0;
}
$posters[$row['id_member']]++;
}
$smcFunc['db_free_result']($request);
foreach ($posters as $id_member => $posts) {
updateMemberData($id_member, array('posts' => 'posts - ' . $posts));
}
// Lastly, delete the topic from the database.
shd_db_query('', '
DELETE FROM {db_prefix}topics
WHERE id_topic = {int:topic}
LIMIT 1', array('topic' => $context['topic_id']));
// And the replies, too.
shd_db_query('', '
DELETE FROM {db_prefix}messages
WHERE id_topic = {int:topic}', array('topic' => $context['topic_id']));
// Update the stats.
require_once $sourcedir . '/Subs-Post.php';
updateStats('message');
updateStats('topic');
updateLastMessages($board);
// Update board post counts.
shd_db_query('', '
UPDATE {db_prefix}boards
SET num_topics = num_topics - 1,
num_posts = num_posts - {int:num_posts}
WHERE id_board = {int:board}', array('board' => $board, 'num_posts' => $num_replies));
} else {
fatal_lang_error('shd_move_ticket_not_created', false);
}
// Send them to the ticket.
redirectexit('action=helpdesk;sa=ticket;ticket=' . $ticket);
}
/**
* Clean up tickets that have been modified by replies being altered through restore, delete, and possibly other operations.
*
* Operations:
* - Identify how many deleted and non deleted replies there are in the ticket.
* - Identify the last non deleted reply in the ticket (if there are no undeleted replies, use the ticket post itself for cohesion)
* - Update the ticket's record with the first and last posters, as well as the correct number of active and deleted replies, and whether there are any deleted replies on the ticket generally
*
* Prior to SimpleDesk 1.1, this function was located in Sources/SimpleDesk-Delete.php.
*
* @param int $ticket The ticket id to recalculate.
*
* @return array An array detailing the user id of the starter, the last replier and the number of active replies in a ticket.
*
* @since 1.0
*/
function shd_recalc_ids($ticket)
{
global $smcFunc;
$query = shd_db_query('', '
SELECT hdt.id_first_msg
FROM {db_prefix}helpdesk_tickets AS hdt
WHERE hdt.id_ticket = {int:ticket}', array('ticket' => $ticket));
list($first_msg) = $smcFunc['db_fetch_row']($query);
$smcFunc['db_free_result']($query);
$query = shd_db_query('', '
SELECT hdtr.message_status, COUNT(hdtr.message_status) AS messages, hdt.id_first_msg, MAX(hdtr.id_msg) AS id_last_msg
FROM {db_prefix}helpdesk_ticket_replies AS hdtr
INNER JOIN {db_prefix}helpdesk_tickets AS hdt ON (hdt.id_ticket = hdtr.id_ticket)
WHERE hdtr.id_msg > hdt.id_first_msg
AND hdt.id_ticket = {int:ticket}
GROUP BY hdtr.message_status', array('ticket' => $ticket));
$messages = array(MSG_STATUS_NORMAL => 0, MSG_STATUS_DELETED => 0);
$last_msg = 0;
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$first_msg = $row['id_first_msg'];
$messages[$row['message_status']] = $row['messages'];
if ($row['message_status'] == MSG_STATUS_NORMAL) {
$last_msg = $row['id_last_msg'];
}
}
$smcFunc['db_free_result']($query);
if (empty($last_msg)) {
$last_msg = $first_msg;
}
// OK, so we have the last message id and correct number of replies, which is awesome. Now we need to ensure user ids are right
$query = shd_db_query('', '
SELECT hdtr_first.id_member, hdtr_last.id_member
FROM {db_prefix}helpdesk_tickets AS hdt
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr_first ON (hdtr_first.id_msg = {int:first_msg})
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr_last ON (hdtr_last.id_msg = {int:last_msg})
WHERE hdt.id_ticket = {int:ticket}', array('first_msg' => $first_msg, 'last_msg' => $last_msg, 'ticket' => $ticket));
list($starter, $replier) = $smcFunc['db_fetch_row']($query);
$smcFunc['db_free_result']($query);
shd_db_query('', '
UPDATE {db_prefix}helpdesk_tickets
SET num_replies = {int:num_replies},
deleted_replies = {int:deleted_replies},
id_last_msg = {int:last_msg},
id_member_started = {int:starter},
id_member_updated = {int:replier},
withdeleted = {int:has_deleted}
WHERE id_ticket = {int:ticket}', array('num_replies' => $messages[MSG_STATUS_NORMAL], 'deleted_replies' => $messages[MSG_STATUS_DELETED], 'last_msg' => $last_msg, 'starter' => $starter, 'replier' => $replier, 'has_deleted' => $messages[MSG_STATUS_DELETED] > 0 ? 1 : 0, 'ticket' => $ticket));
return array($starter, $replier, $messages[MSG_STATUS_NORMAL]);
}
/**
* Handle moving a reply up and down within its category.
*
* @since 2.0
*/
function shd_admin_canned_movereply()
{
global $context, $smcFunc, $modSettings;
checkSession('get');
$_REQUEST['reply'] = isset($_REQUEST['reply']) ? (int) $_REQUEST['reply'] : 0;
$_REQUEST['direction'] = isset($_REQUEST['direction']) && in_array($_REQUEST['direction'], array('up', 'down')) ? $_REQUEST['direction'] : '';
$query = shd_db_query('', '
SELECT id_reply, reply_order
FROM {db_prefix}helpdesk_cannedreplies', array());
if ($smcFunc['db_num_rows']($query) == 0 || empty($_REQUEST['direction'])) {
$smcFunc['db_free_result']($query);
fatal_lang_error('shd_admin_cannedreplies_cannot_move_reply', false);
}
$replies = array();
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$replies[$row['reply_order']] = $row['id_reply'];
}
ksort($replies);
$replies_map = array_flip($replies);
if (empty($replies_map[$_REQUEST['reply']])) {
fatal_lang_error('shd_admin_cannedreplies_cannot_move_reply', false);
}
$current_pos = $replies_map[$_REQUEST['reply']];
$destination = $current_pos + ($_REQUEST['direction'] == 'up' ? -1 : 1);
if (empty($replies[$destination])) {
fatal_lang_error('shd_admin_cannedreplies_cannot_move_reply_' . $_REQUEST['direction'], false);
}
$other_reply = $replies[$destination];
shd_db_query('', '
UPDATE {db_prefix}helpdesk_cannedreplies
SET reply_order = {int:new_pos}
WHERE id_reply = {int:reply}', array('new_pos' => $destination, 'reply' => $_REQUEST['reply']));
shd_db_query('', '
UPDATE {db_prefix}helpdesk_cannedreplies
SET reply_order = {int:old_pos}
WHERE id_reply = {int:other_reply}', array('old_pos' => $current_pos, 'other_reply' => $other_reply));
redirectexit('action=admin;area=helpdesk_cannedreplies');
}
/**
* Handles fetching the information for the helpdesk display within the unread and unread replies pages.
*
* The content is 'appended' below the unread posts information by way of a template layer.
*
* @since 2.0
*/
function shd_unread_posts()
{
global $smcFunc, $context, $user_info, $sourcedir, $txt, $scripturl, $user_profile, $modSettings;
// Are we using Dragooon's very swish mobile theme, or other wireless? If so, disable this.
if (WIRELESS || isset($_REQUEST['thememode']) && $_REQUEST['thememode'] == 'mobile' || isset($_COOKIE['smf4m_cookie']) && $_COOKIE['smf4m_cookie'] == 'mobile') {
$modSettings['shd_disable_unread'] = true;
}
// We're only displaying this to staff. We didn't do this check on bootstrapping, no sense doing it every page load.
if (shd_allowed_to('shd_staff', 0) && empty($modSettings['shd_disable_unread'])) {
// Get the data
$context['shd_preferences'] = shd_load_user_prefs();
$context['shd_unread_info'] = array();
if (empty($context['shd_preferences']['display_unread_type']) || $context['shd_preferences']['display_unread_type'] == 'outstanding') {
// Get all the outstanding tickets
$context['block_title'] = $txt['shd_tickets_open'];
$request = shd_db_query('', '
SELECT hdt.id_ticket, hdt.subject, hdt.id_ticket, hdt.num_replies, hdt.last_updated,
hdtr_first.poster_name, hdt.urgency, hdt.status, hdt.id_member_started, hdt.id_member_assigned, hdt.id_last_msg AS log_read
FROM {db_prefix}helpdesk_tickets AS hdt
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr_first ON (hdt.id_first_msg = hdtr_first.id_msg)
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr_last ON (hdt.id_last_msg = hdtr_last.id_msg)
WHERE {query_see_ticket}
AND hdt.status IN ({array_int:status})
ORDER BY hdt.urgency DESC, hdt.last_updated', array('status' => array(TICKET_STATUS_NEW, TICKET_STATUS_PENDING_STAFF)));
} elseif ($context['shd_preferences']['display_unread_type'] == 'unread') {
// Only unread ones
$context['block_title'] = $txt['shd_unread_tickets'];
$request = shd_db_query('', '
SELECT hdt.id_ticket, hdt.subject, hdt.id_ticket, hdt.num_replies, hdt.last_updated,
hdtr_first.poster_name, hdt.urgency, hdt.status, hdt.id_member_started, hdt.id_member_assigned, IFNULL(hdlr.id_msg, 0) AS log_read
FROM {db_prefix}helpdesk_tickets AS hdt
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr_first ON (hdt.id_first_msg = hdtr_first.id_msg)
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr_last ON (hdt.id_last_msg = hdtr_last.id_msg)
LEFT JOIN {db_prefix}helpdesk_log_read AS hdlr ON (hdt.id_ticket = hdlr.id_ticket AND hdlr.id_member = {int:user})
WHERE {query_see_ticket}
AND hdt.status IN ({array_int:status})
AND (hdlr.id_msg IS NULL OR hdlr.id_msg < hdt.id_last_msg)
ORDER BY hdt.urgency DESC, hdt.last_updated', array('user' => $context['user']['id'], 'status' => array(TICKET_STATUS_NEW, TICKET_STATUS_PENDING_STAFF)));
}
if (!empty($request)) {
$members = array();
while ($row = $smcFunc['db_fetch_assoc']($request)) {
$row['id_ticket_display'] = str_pad($row['id_ticket'], $modSettings['shd_zerofill'], '0', STR_PAD_LEFT);
$row['updated'] = timeformat($row['last_updated']);
$context['shd_unread_info'][] = $row;
if ($row['id_member_started'] != 0) {
$members[] = $row['id_member_started'];
}
if ($row['id_member_assigned'] != 0) {
$members[] = $row['id_member_assigned'];
}
}
loadMemberData(array_unique($members));
foreach ($context['shd_unread_info'] as $key => $ticket) {
if (!empty($user_profile[$ticket['id_member_started']])) {
$context['shd_unread_info'][$key]['ticket_starter'] = shd_profile_link($user_profile[$ticket['id_member_started']]['member_name'], $ticket['id_member_started']);
} else {
$context['shd_unread_info'][$key]['ticket_starter'] = $ticket['poster_name'];
}
if (!empty($user_profile[$ticket['id_member_assigned']])) {
$context['shd_unread_info'][$key]['ticket_assigned'] = shd_profile_link($user_profile[$ticket['id_member_assigned']]['member_name'], $ticket['id_member_assigned']);
} else {
$context['shd_unread_info'][$key]['ticket_assigned'] = '<span class="error">' . $txt['shd_unassigned'] . '</span>';
}
}
// And set up the template too.
loadTemplate('sd_template/SimpleDesk-Unread', 'helpdesk');
$context['template_layers'][] = 'shd_unread';
}
}
// OK, time to get out of here. If we're here, it's because we have a $_REQUEST['action'] of 'unread' or 'unreadreplies', both of which
// are defined in $context['shd_unread_actions'] thanks to shd_init_actions back in Subs-SimpleDesk.php.
require_once $sourcedir . '/' . $context['shd_unread_actions'][$_REQUEST['action']][0];
$context['shd_unread_actions'][$_REQUEST['action']][1]();
}
function shd_search2()
{
global $context, $smcFunc, $txt, $modSettings, $scripturl, $sourcedir;
shd_is_allowed_to('shd_search', 0);
if (!empty($context['load_average']) && !empty($modSettings['loadavg_search']) && $context['load_average'] >= $modSettings['loadavg_search']) {
fatal_lang_error('loadavg_search_disabled', false);
}
// No, no, no... this is a bit hard on the server, so don't you go prefetching it!
if (isset($_SERVER['HTTP_X_MOZ']) && $_SERVER['HTTP_X_MOZ'] == 'prefetch') {
ob_end_clean();
header('HTTP/1.1 403 Forbidden');
die;
}
// We will need this.
require_once $sourcedir . '/sd_source/Subs-SimpleDeskSearch.php';
loadTemplate('sd_template/SimpleDesk-Search');
$context['page_title'] = $txt['shd_search_results'];
$context['linktree'][] = array('name' => $txt['shd_search_results']);
$context['search_clauses'] = array('{query_see_ticket}');
$context['search_params'] = array();
// Departments first.
$visible_depts = shd_allowed_to('access_helpdesk', false);
$using_depts = array();
if (!empty($_POST['search_dept']) && is_array($_POST['search_dept'])) {
foreach ($_POST['search_dept'] as $dept) {
if ((int) $dept > 0) {
$using_depts[] = (int) $dept;
}
}
}
if (!empty($using_depts)) {
$using_depts = array_intersect($using_depts, $visible_depts);
}
// No departments? Can't really do a lot, sorry. Bye then.
if (empty($using_depts)) {
return $context['sub_template'] = 'search_no_results';
}
// Is the selected list the same size as the list we can see? If it is, theory says that means we're picking every department we can see and don't need to exclude it.
if (count($using_depts) != count($visible_depts)) {
$context['search_clauses'][] = 'hdt.id_dept IN ({array_int:visible_depts})';
$context['search_params']['visible_depts'] = $using_depts;
// Also, we need to get the department list for displaying, only if we can actually see multiple departments at all.
if ($context['shd_multi_dept']) {
$query = $smcFunc['db_query']('', '
SELECT id_dept, dept_name
FROM {db_prefix}helpdesk_depts
WHERE id_dept IN ({array_int:dept_list})
ORDER BY dept_order', array('dept_list' => $using_depts));
$context['search_dept_list'] = array();
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$context['search_dept_list'][$row['id_dept']] = $row['dept_name'];
}
$smcFunc['db_free_result']($query);
}
}
// Ticket urgency
$using_urgency = array();
if (!empty($_POST['urgency']) && is_array($_POST['urgency'])) {
foreach ($_POST['urgency'] as $urgency) {
$urgency = (int) $urgency;
if ($urgency >= 0 && $urgency <= 5) {
// All the currently defined urgencies
$using_urgency[] = $urgency;
}
}
}
if (empty($using_urgency)) {
return $context['sub_template'] = 'search_no_results';
} else {
$using_urgency = array_unique($using_urgency);
if (count($using_urgency) < 6) {
// We have less than 6 selected urgencies, which means we actually need to filter on them, as opposed to if all 6 are selected when we don't.
$context['search_clauses'][] = 'hdt.urgency IN ({array_int:urgency})';
$context['search_params']['urgency'] = $using_urgency;
}
}
// Ticket scope
// All empty? If so, bye.
if (empty($_POST['scope_open']) && empty($_POST['scope_closed']) && empty($_POST['scope_recycle'])) {
return $context['sub_template'] = 'search_no_results';
} elseif (empty($_POST['scope_open']) || empty($_POST['scope_closed']) || empty($_POST['scope_recycle'])) {
$status = array();
if (!empty($_POST['scope_open'])) {
$status = array_merge($status, array(TICKET_STATUS_NEW, TICKET_STATUS_PENDING_STAFF, TICKET_STATUS_PENDING_USER, TICKET_STATUS_WITH_SUPERVISOR, TICKET_STATUS_ESCALATED));
}
if (!empty($_POST['scope_closed'])) {
$status = array_merge($status, array(TICKET_STATUS_CLOSED));
}
if (!empty($_POST['scope_recycle'])) {
$status = array_merge($status, array(TICKET_STATUS_DELETED));
}
$context['search_clauses'][] = 'hdt.status IN ({array_int:status})';
$context['search_params']['status'] = $status;
// That's ticket level status taken care of. We'll pick up recycled items in non recycled tickets separately since it's only relevant if you're actually searching text.
}
// Ticket starter
$starters = shd_get_named_people('starter');
if (!empty($starters)) {
$context['search_clauses'][] = 'hdt.id_member_started IN ({array_int:member_started})';
$context['search_params']['member_started'] = $starters;
}
// Ticket assigned to
$assignees = shd_get_named_people('assignee');
if (!empty($assignees)) {
$context['search_clauses'][] = 'hdt.id_member_assigned IN ({array_int:member_assigned})';
$context['search_params']['member_assigned'] = $assignees;
}
// Lastly, page number. We're doing something different to SMF's normal style here. Long and complicated, but there you go.
if (isset($_POST['page'])) {
$context['pagenum'] = (int) $_POST['page'];
}
if (empty($context['pagenum']) || $context['pagenum'] < 1) {
$context['pagenum'] = 1;
}
$number_per_page = 20;
// OK, so are there any words? If not, execute this sucker the quick way and get out to the template quick.
$context['search_terms'] = !empty($_POST['search']) ? trim($_POST['search']) : '';
// Also, did we select some text but fail to select what it was searching in? If so, kick it out.
if (!empty($context['search_terms']) && empty($_POST['search_subjects']) && empty($_POST['search_tickets']) && empty($_POST['search_replies'])) {
return $context['sub_template'] = 'search_no_results';
} elseif (!empty($context['search_terms'])) {
// We're using search terms, and we need to store the areas we're covering. Only makes sense if we're using terms though.
$context['search_params']['areas'] = array();
foreach (array('subjects', 'tickets', 'replies') as $area) {
if (!empty($_POST['search_' . $area])) {
$context['search_params']['areas'][$area] = true;
}
}
// While we're at it, see if we actually have any words to search for.
$tokens = shd_tokeniser($context['search_terms']);
$count_tokens = count($tokens);
// No actual words?
if ($count_tokens == 0) {
$context['search_terms'] = '';
unset($context['search_params']['areas']);
}
}
// Spam me not!
if (empty($_SESSION['lastsearch'])) {
spamProtection('search');
} else {
list($temp_clauses, $temp_params, $temp_terms) = unserialize($_SESSION['lastsearch']);
if ($temp_clauses != $context['search_clauses'] || $temp_params != $context['search_params'] || $temp_terms != $context['search_terms']) {
spamProtection('search');
}
}
$_SESSION['lastsearch'] = serialize(array($context['search_clauses'], $context['search_params'], $context['search_terms']));
$context['search_params']['start'] = ($context['pagenum'] - 1) * $number_per_page;
$context['search_params']['limit'] = $number_per_page;
if (empty($context['search_terms'])) {
// This is where it starts to get expensive, *sob*. We first have to query to get the number of applicable rows.
$query = shd_db_query('', '
SELECT COUNT(id_ticket)
FROM {db_prefix}helpdesk_tickets AS hdt
WHERE ' . implode(' AND ', $context['search_clauses']) . ' LIMIT 1000', $context['search_params']);
list($count) = $smcFunc['db_fetch_row']($query);
if ($count == 0) {
$smcFunc['db_free_result']($query);
return $context['sub_template'] = 'search_no_results';
}
// OK, at least one result, awesome. Are we off the end of the list?
if ($context['search_params']['start'] > $count) {
$context['search_params']['start'] = $count - $count % $number_per_page;
$context['pagenum'] = $context['search_params']['start'] / $number_per_page + 1;
$context['num_results'] = $count;
}
$query = shd_db_query('', '
SELECT hdt.id_ticket, hdt.id_dept, hdd.dept_name, hdt.subject, hdt.urgency, hdt.private, hdt.last_updated, hdtr.body,
hdtr.smileys_enabled, hdtr.id_member AS id_member, IFNULL(mem.real_name, hdtr.poster_name) AS poster_name, hdtr.poster_time
FROM {db_prefix}helpdesk_tickets AS hdt
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr ON (hdt.id_first_msg = hdtr.id_msg)
INNER JOIN {db_prefix}helpdesk_depts AS hdd ON (hdt.id_dept = hdd.id_dept)
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = hdtr.id_member)
WHERE ' . implode(' AND ', $context['search_clauses']) . '
ORDER BY hdt.last_updated DESC
LIMIT {int:start}, {int:limit}', $context['search_params']);
$context['search_results'] = array();
$page_pos = $context['search_params']['start'];
// e.g. 0 on page 1, 10 for page 2, the first item will be page_pos + 1, so ++ it before using it.
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$row['result'] = ++$page_pos;
// Increment first, then use.
$row['display_id'] = str_pad($row['id_ticket'], $modSettings['shd_zerofill'], '0', STR_PAD_LEFT);
$row['is_ticket'] = true;
// If we're here, we're only handling tickets anyway. If we're searching text we will need to know if it was a ticket or reply though.
$row['dept_link'] = !$context['shd_multi_dept'] ? '' : '[<a href="' . $scripturl . '?action=helpdesk;sa=main;dept=' . $row['id_dept'] . '">' . $row['dept_name'] . '</a>] ';
$context['search_results'][] = $row;
}
return $context['sub_template'] = 'search_results';
} else {
$context['match_all'] = empty($_POST['searchtype']) || $_POST['searchtype'] == 'all';
// Then figure out what terms are being matched.
$matches = array('subjects' => array(), 'messages' => array(), 'id_msg' => array());
// Doing subjects. Fetch all the instances that match and begin filtering as we go.
if (!empty($context['search_params']['areas']['subjects'])) {
$query = shd_db_query('', '
SELECT hdssw.id_word, hdt.id_first_msg
FROM {db_prefix}helpdesk_search_subject_words AS hdssw
INNER JOIN {db_prefix}helpdesk_tickets AS hdt ON (hdssw.id_ticket = hdt.id_ticket)
WHERE {query_see_ticket}
AND id_word IN ({array_string:tokens})', array('tokens' => $tokens));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$matches['subjects'][$row['id_first_msg']][$row['id_word']] = true;
}
$smcFunc['db_free_result']($query);
// Now go through and figure out which tickets we're interested in keeping.
if ($context['match_all']) {
foreach ($matches['subjects'] as $msg => $ticket_words) {
if (count($ticket_words) != $count_tokens) {
// How many words did we match in this subject? If it isn't the number we're expecting, ditch it.
unset($matches['subjects'][$msg]);
}
}
}
// Now, we just have a list of tickets to play with. Let's put that together in a master list.
foreach ($matches['subjects'] as $msg => $ticket_words) {
$matches['id_msg'][$msg] = true;
}
unset($matches['subjects']);
}
// Now we get the list of words that apply to tickets and replies. The process is different if we do one or both. Both, first.
if (!empty($context['search_params']['areas']['tickets']) && !empty($context['search_params']['areas']['replies'])) {
// If we're doing both replies and tickets themselves, we don't have to care too much about the message itself, except for being deleted.
$query = shd_db_query('', '
SELECT hdssw.id_word, hdt.id_first_msg
FROM {db_prefix}helpdesk_search_subject_words AS hdssw
INNER JOIN {db_prefix}helpdesk_tickets AS hdt ON (hdssw.id_ticket = hdt.id_ticket)
WHERE {query_see_ticket}
AND id_word IN ({array_string:tokens})' . (empty($_POST['scope_recycle']) || !shd_allowed_to('shd_access_recyclebin', 0) ? '
AND hdtr.message_status = {int:not_deleted}' : ''), array('tokens' => $tokens, 'not_deleted' => MSG_STATUS_NORMAL));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$matches['messages'][$row['id_first_msg']][$row['id_word']] = true;
}
$smcFunc['db_free_result']($query);
if ($context['match_all']) {
foreach ($matches['messages'] as $msg => $ticket_words) {
if (count($ticket_words) != $count_tokens) {
// How many words did we match in this subject? If it isn't the number we're expecting, ditch it.
unset($matches['messages'][$msg]);
}
}
}
// Now, we just have a list of tickets to play with. Let's put that together in a master list.
foreach ($matches['messages'] as $msg => $ticket_words) {
$matches['id_msg'][$msg] = true;
}
unset($matches['messages']);
} elseif (!empty($context['search_params']['areas']['tickets']) || !empty($context['search_params']['areas']['replies'])) {
$query = $smcFunc['db_query']('', '
SELECT hdstw.id_word, hdstw.id_msg
FROM {db_prefix}helpdesk_search_ticket_words AS hdstw
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr ON (hdstw.id_msg = hdtr.id_msg)
INNER JOIN {db_prefix}helpdesk_tickets AS hdt ON (hdtr.id_ticket = hdt.id_ticket)
WHERE id_word IN ({array_string:tokens})
AND hdstw.id_msg {raw:operator} hdt.id_first_msg' . (empty($_POST['scope_recycle']) || !shd_allowed_to('shd_access_recyclebin', 0) ? '
AND hdtr.message_status = {int:not_deleted}' : ''), array('tokens' => $tokens, 'not_deleted' => MSG_STATUS_NORMAL, 'operator' => !empty($context['search_params']['areas']['tickets']) ? '=' : '!='));
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$matches['messages'][$row['id_msg']][$row['id_word']] = true;
}
$smcFunc['db_free_result']($query);
if ($context['match_all']) {
foreach ($matches['messages'] as $ticket => $ticket_words) {
if (count($ticket_words) != $count_tokens) {
// How many words did we match in this subject? If it isn't the number we're expecting, ditch it.
unset($matches['messages'][$ticket]);
}
}
}
// Now, we just have a list of tickets to play with. Let's put that together in a master list.
foreach ($matches['messages'] as $msg => $ticket_words) {
$matches['id_msg'][$ticket] = true;
}
unset($matches['messages']);
}
// Aw, no matches?
if (empty($matches['id_msg'])) {
return $context['sub_template'] = 'search_no_results';
}
$context['search_clauses'][] = 'hdtr.id_msg IN ({array_int:msg})';
$context['search_params']['msg'] = array_keys($matches['id_msg']);
// How many results are there in total?
$query = shd_db_query('', '
SELECT COUNT(*)
FROM {db_prefix}helpdesk_tickets AS hdt
INNER JOIN {db_prefix}helpdesk_ticket_replies AS hdtr ON (hdtr.id_ticket = hdt.id_ticket)
WHERE ' . implode(' AND ', $context['search_clauses']) . ' LIMIT 1000', $context['search_params']);
list($count) = $smcFunc['db_fetch_row']($query);
if ($count == 0) {
$smcFunc['db_free_result']($query);
return $context['sub_template'] = 'search_no_results';
}
// OK, at least one result, awesome. Are we off the end of the list?
if ($context['search_params']['start'] > $count) {
$context['search_params']['start'] = $count - $count % $number_per_page;
$context['pagenum'] = $context['search_params']['start'] / $number_per_page + 1;
$context['num_results'] = $count;
}
// Get the results for displaying.
$query = shd_db_query('', '
SELECT hdt.id_ticket, hdt.id_dept, hdd.dept_name, hdt.subject, hdt.urgency, hdt.private, hdt.last_updated, hdtr.body,
hdtr.smileys_enabled, hdtr.id_member AS id_member, IFNULL(mem.real_name, hdtr.poster_name) AS poster_name, hdtr.poster_time,
hdt.id_first_msg, hdtr.id_msg
FROM {db_prefix}helpdesk_ticket_replies AS hdtr
INNER JOIN {db_prefix}helpdesk_tickets AS hdt ON (hdt.id_ticket = hdtr.id_ticket)
INNER JOIN {db_prefix}helpdesk_depts AS hdd ON (hdt.id_dept = hdd.id_dept)
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = hdtr.id_member)
WHERE ' . implode(' AND ', $context['search_clauses']) . '
ORDER BY hdt.last_updated DESC, hdtr.id_msg DESC
LIMIT {int:start}, {int:limit}', $context['search_params']);
$context['search_results'] = array();
$page_pos = $context['search_params']['start'];
// e.g. 0 on page 1, 10 for page 2, the first item will be page_pos + 1, so ++ it before using it.
while ($row = $smcFunc['db_fetch_assoc']($query)) {
$row['result'] = ++$page_pos;
// Increment first, then use.
$row['display_id'] = str_pad($row['id_ticket'], $modSettings['shd_zerofill'], '0', STR_PAD_LEFT);
$row['is_ticket'] = $row['id_msg'] == $row['id_first_msg'];
// If the message we grabbed is the first message, this is actually a ticket, not a reply to one.
$row['dept_link'] = !$context['shd_multi_dept'] ? '' : '[<a href="' . $scripturl . '?action=helpdesk;sa=main;dept=' . $row['id_dept'] . '">' . $row['dept_name'] . '</a>] ';
$context['search_results'][] = $row;
}
return $context['sub_template'] = 'search_results';
}
}
/**
* Updates a ticket's relationships.
*
* This function is responsible for adding, updating and removing relationships.
*
* Accessed through ?action=helpdesk;sa=relation;ticket=x;linkticket=y;relation=[linked|duplicated|parent|child|delete];sessvar=sessid and will redirect back to the ticket once complete.
*
* @since 2.0
*/
function shd_ticket_relation()
{
global $context, $smcFunc, $modSettings;
checkSession('request');
if (!empty($modSettings['shd_disable_relationships'])) {
fatal_lang_error('shd_relationships_are_disabled', false);
}
$otherticket = isset($_REQUEST['otherticket']) ? (int) $_REQUEST['otherticket'] : 0;
if (empty($context['ticket_id']) || empty($otherticket)) {
fatal_lang_error('shd_no_ticket', false);
}
if ($context['ticket_id'] == $otherticket) {
fatal_lang_error('shd_cannot_relate_self', false);
}
$actions = array('linked', 'duplicated', 'parent', 'child', 'delete');
$rel_action = isset($_REQUEST['relation']) && in_array($_REQUEST['relation'], $actions) ? $_REQUEST['relation'] : '';
if (empty($rel_action)) {
fatal_lang_error('shd_invalid_relation', false);
}
// Quick/consistent way to ensure permissions are adhered to and that the ticket exists. Might as well get the subject while here too.
$ticketinfo = shd_load_ticket($context['ticket_id']);
$primary_subject = $ticketinfo['subject'];
$dept = $ticketinfo['dept'];
$ticketinfo = shd_load_ticket($otherticket);
$secondary_subject = $ticketinfo['subject'];
if ($rel_action == 'delete') {
shd_is_allowed_to('shd_delete_relationships', $dept);
} else {
shd_is_allowed_to('shd_create_relationships', $dept);
}
// See if there's an existing relationship with these parameters
$query = shd_db_query('', '
SELECT rel_type
FROM {db_prefix}helpdesk_relationships
WHERE primary_ticket = {int:ticket}
AND secondary_ticket = {int:otherticket}', array('ticket' => $context['ticket_id'], 'otherticket' => $otherticket));
$new_relationship = $smcFunc['db_num_rows']($query) == 0;
list($existing_relation) = $new_relationship ? array(-1) : $smcFunc['db_fetch_row']($query);
$smcFunc['db_free_result']($query);
if ($rel_action == 'delete' && $new_relationship) {
fatal_lang_error('shd_no_relation_delete', false);
}
$log_prefix = 'rel_' . ($new_relationship || $rel_action == 'delete' ? '' : 're_');
$log_map = array('delete' => 'delete', 'linked' => 'linked', 'duplicated' => 'duplicated', 'parent' => 'child', 'child' => 'parent');
$log_assoc_ids = array('delete' => -1, 'linked' => RELATIONSHIP_LINKED, 'duplicated' => RELATIONSHIP_DUPLICATED, 'parent' => RELATIONSHIP_ISPARENT, 'child' => RELATIONSHIP_ISCHILD);
$logaction_ticket = $log_prefix . $rel_action;
$logaction_otherticket = $log_prefix . $log_map[$rel_action];
// The "from" ticket is $context['ticket_id'], $otherticket is the ticket whose id the user gives, and $rel_action sets the relationship type.
call_integration_hook('shd_hook_relations', array(&$rel_action, &$otherticket));
if ($rel_action == 'delete') {
// Delete the link
shd_db_query('', '
DELETE FROM {db_prefix}helpdesk_relationships
WHERE (primary_ticket = {int:ticket} AND secondary_ticket = {int:otherticket})
OR (primary_ticket = {int:otherticket} AND secondary_ticket = {int:ticket})', array('ticket' => $context['ticket_id'], 'otherticket' => $otherticket));
} else {
$smcFunc['db_insert']('replace', '{db_prefix}helpdesk_relationships', array('primary_ticket' => 'int', 'secondary_ticket' => 'int', 'rel_type' => 'int'), array(array($context['ticket_id'], $otherticket, $log_assoc_ids[$rel_action]), array($otherticket, $context['ticket_id'], $log_assoc_ids[$log_map[$rel_action]])), array('primary_ticket', 'secondary_ticket'));
}
// Now actually log it, main ticket first -- if it's different to the one we had before
if ($log_assoc_ids[$rel_action] != $existing_relation) {
$params = array('ticket' => $context['ticket_id'], 'otherticket' => $otherticket, 'subject' => $primary_subject, 'othersubject' => $secondary_subject);
shd_log_action($logaction_ticket, $params);
$params = array('ticket' => $otherticket, 'otherticket' => $context['ticket_id'], 'subject' => $secondary_subject, 'othersubject' => $primary_subject);
shd_log_action($logaction_otherticket, $params);
}
// See yah
redirectexit('action=helpdesk;sa=ticket;ticket=' . $context['ticket_id']);
}
/**
* Initialises the helpdesk action log.
*
* This function loads the language strings, and hands off to {@link shd_load_action_log_entries()} to perform the actual log
* generation.
*
* Before doing so, however, this function will also prepare for deletion of old entries, as well as sorting out the columns and
* ordering rules before handing control to the other function.
*
* @since 1.0
*/
function shd_admin_action_log()
{
global $context, $settings, $scripturl, $txt, $sourcedir, $smcFunc, $sort_types;
shd_load_language('sd_language/SimpleDeskLogAction');
$context['can_delete'] = allowedTo('admin_forum');
$context['displaypage'] = 30;
$context['hoursdisable'] = 24;
$context['waittime'] = time() - $context['hoursdisable'] * 3600;
// Handle deletion...
if (isset($_REQUEST['removeall']) && $context['can_delete']) {
shd_db_query('', '
DELETE FROM {db_prefix}helpdesk_log_action
WHERE log_time < {int:twenty_four_hours_wait}', array('twenty_four_hours_wait' => $context['waittime']));
} elseif (!empty($_REQUEST['remove']) && $context['can_delete']) {
shd_db_query('', '
DELETE FROM {db_prefix}helpdesk_log_action
WHERE id_action = {int:gtfo}
AND log_time < {int:twenty_four_hours_wait}', array('twenty_four_hours_wait' => $context['waittime'], 'gtfo' => (int) $_REQUEST['remove']));
}
// Do the column stuff!
$sort_types = array('action' => 'la.action', 'time' => 'la.log_time', 'member' => 'mem.real_name', 'position' => 'mg.group_name', 'ip' => 'la.ip');
// Setup the direction stuff...
$context['sort'] = isset($_REQUEST['sort']) && isset($sort_types[$_REQUEST['sort']]) ? $sort_types[$_REQUEST['sort']] : $sort_types['time'];
$context['start'] = isset($_REQUEST['start']) ? $_REQUEST['start'] : 0;
$context['order'] = isset($_REQUEST['asc']) ? 'ASC' : 'DESC';
$context['url_sort'] = isset($_REQUEST['sort']) ? ';sort=' . $_REQUEST['sort'] : '';
$context['url_order'] = isset($_REQUEST['asc']) ? ';asc' : '';
// Get all action log entries
$context['actions'] = shd_load_action_log_entries($context['start'], $context['displaypage'], $context['sort'], $context['order']);
$context['page_index'] = shd_no_expand_pageindex($scripturl . '?action=admin;area=helpdesk_info;sa=actionlog' . $context['url_sort'] . $context['url_order'], $context['start'], shd_count_action_log_entries(), $context['displaypage']);
$context['sub_template'] = 'shd_action_log';
}
