static function dumpTickets($sql, $how = 'csv')
{
// Add custom fields to the $sql statement
$cdata = $fields = $select = array();
foreach (TicketForm::getInstance()->getFields() as $f) {
// Ignore core fields
if (in_array($f->get('name'), array('subject', 'priority'))) {
continue;
} elseif (!$f->hasData() || $f->isPresentationOnly()) {
continue;
}
$name = $f->get('name') ? $f->get('name') : 'field_' . $f->get('id');
$key = '__field_' . $f->get('id');
// Fetch ID values for ID-based data
if ($f->hasIdValue()) {
$name .= '_id';
}
$cdata[$key] = $f->get('label');
$fields[$key] = $f;
$select[] = "cdata.`{$name}` AS __field_" . $f->get('id');
}
if ($select) {
$sql = str_replace(' FROM ', ',' . implode(',', $select) . ' FROM ', $sql);
}
return self::dumpQuery($sql, array('number' => 'Ticket Number', 'created' => 'Date', 'subject' => 'Subject', 'name' => 'From', 'email' => 'From Email', 'priority_desc' => 'Priority', 'dept_name' => 'Department', 'helptopic' => 'Help Topic', 'source' => 'Source', 'status' => 'Current Status', 'effective_date' => 'Last Updated', 'duedate' => 'Due Date', 'isoverdue' => 'Overdue', 'isanswered' => 'Answered', 'assigned' => 'Assigned To', 'staff' => 'Staff Assigned', 'team' => 'Team Assigned', 'thread_count' => 'Thread Count', 'attachments' => 'Attachment Count') + $cdata, $how, array('modify' => function (&$record, $keys) use($fields) {
foreach ($fields as $k => $f) {
if (($i = array_search($k, $keys)) !== false) {
$record[$i] = $f->export($f->to_php($record[$i]));
}
}
return $record;
}));
}
function getRequestStructure($format, $data = null)
{
$supported = array("alert", "autorespond", "source", "topicId", "attachments" => array("*" => array("name", "type", "data", "encoding", "size")), "message", "ip", "priorityId");
# Fetch dynamic form field names for the given help topic and add
# the names to the supported request structure
if (isset($data['topicId']) && ($topic = Topic::lookup($data['topicId'])) && ($form = $topic->getForm())) {
foreach ($form->getDynamicFields() as $field) {
$supported[] = $field->get('name');
}
}
# Ticket form fields
# TODO: Support userId for existing user
if ($form = TicketForm::getInstance()) {
foreach ($form->getFields() as $field) {
$supported[] = $field->get('name');
}
}
# User form fields
if ($form = UserForm::getInstance()) {
foreach ($form->getFields() as $field) {
$supported[] = $field->get('name');
}
}
if (!strcasecmp($format, 'email')) {
$supported = array_merge($supported, array('header', 'mid', 'emailId', 'to-email-id', 'ticketId', 'reply-to', 'reply-to-name', 'in-reply-to', 'references', 'thread-type', 'flags' => array('bounce', 'auto-reply', 'spam', 'viral'), 'recipients' => array('*' => array('name', 'email', 'source'))));
$supported['attachments']['*'][] = 'cid';
}
return $supported;
}
}
?>
</select> <span class='error'> <?php echo $errors['assignId']; ?></span>
</td>
</tr>
<?php } ?>
</tbody>
<tbody id="dynamic-form">
<?php
if ($form) {
include(STAFFINC_DIR . 'templates/dynamic-form.tmpl.php');
}
?>
</tbody>
<tbody> <?php
$tform = TicketForm::getInstance()->getForm($_POST);
if ($_POST) $tform->isValid();
$tform->render(true);
?>
</tbody>
<tbody>
<?php
//is the user allowed to post replies??
if($thisstaff->canPostReply()) { ?>
<tr>
<th colspan="2">
<em><strong>Response</strong>: Optional response to the above issue.</em>
</th>
</tr>
<tr>
<td colspan=2>
$name = $f->get('name') ? $f->get('name') : 'field_' . $f->get('id');
$ids = $f->hasIdValue();
$fields = sprintf('`%s`=', $name) . db_input($answer->get('value'));
if ($f->hasIdValue()) {
$fields .= sprintf(',`%s_id`=', $name) . db_input($answer->getIdValue());
}
$sql = 'INSERT INTO `' . TABLE_PREFIX . 'ticket__cdata` SET ' . $fields . ', `ticket_id`=' . db_input($answer->getEntry()->get('object_id')) . ' ON DUPLICATE KEY UPDATE ' . $fields;
if (!db_query($sql) || !db_affected_rows()) {
return self::dropDynamicDataView();
}
}
}
// Add fields from the standard ticket form to the ticket filterable fields
Filter::addSupportedMatches('Ticket Data', function () {
$matches = array();
foreach (TicketForm::getInstance()->getFields() as $f) {
if (!$f->hasData()) {
continue;
}
$matches['field.' . $f->get('id')] = 'Ticket / ' . $f->getLabel();
if (($fi = $f->getImpl()) instanceof SelectionField) {
foreach ($fi->getList()->getProperties() as $p) {
$matches['field.' . $f->get('id') . '.' . $p->get('id')] = 'Ticket / ' . $f->getLabel() . ' / ' . $p->getLabel();
}
}
}
return $matches;
}, 30);
// Manage materialized view on custom data updates
Signal::connect('model.created', array('TicketForm', 'updateDynamicDataView'), 'DynamicFormEntryAnswer');
Signal::connect('model.updated', array('TicketForm', 'updateDynamicDataView'), 'DynamicFormEntryAnswer');
function _search($req)
{
global $thisstaff, $cfg;
$result = array();
$select = 'SELECT ticket.ticket_id';
$from = ' FROM ' . TICKET_TABLE . ' ticket ';
//Access control.
$where = ' WHERE ( (ticket.staff_id=' . db_input($thisstaff->getId()) . ' AND ticket.status="open" )';
if (($teams = $thisstaff->getTeams()) && count(array_filter($teams))) {
$where .= ' OR (ticket.team_id IN (' . implode(',', db_input(array_filter($teams))) . ' ) AND ticket.status="open")';
}
if (!$thisstaff->showAssignedOnly() && ($depts = $thisstaff->getDepts())) {
$where .= ' OR ticket.dept_id IN (' . implode(',', db_input($depts)) . ')';
}
$where .= ' ) ';
//Department
if ($req['deptId']) {
$where .= ' AND ticket.dept_id=' . db_input($req['deptId']);
}
//Help topic
if ($req['topicId']) {
$where .= ' AND ticket.topic_id=' . db_input($req['topicId']);
}
//Status
switch (strtolower($req['status'])) {
case 'open':
$where .= ' AND ticket.status="open" ';
break;
case 'answered':
$where .= ' AND ticket.status="open" AND ticket.isanswered=1 ';
break;
case 'overdue':
$where .= ' AND ticket.status="open" AND ticket.isoverdue=1 ';
break;
case 'closed':
$where .= ' AND ticket.status="closed" ';
break;
}
//Assignee
if (isset($req['assignee']) && strcasecmp($req['status'], 'closed')) {
$id = preg_replace("/[^0-9]/", "", $req['assignee']);
$assignee = $req['assignee'];
$where .= ' AND ( ( ticket.status="open" ';
if ($assignee[0] == 't') {
$where .= ' AND ticket.team_id=' . db_input($id);
} elseif ($assignee[0] == 's') {
$where .= ' AND ticket.staff_id=' . db_input($id);
} elseif (is_numeric($id)) {
$where .= ' AND ticket.staff_id=' . db_input($id);
}
$where .= ')';
if ($req['staffId'] && !$req['status']) {
//Assigned TO + Closed By
$where .= ' OR (ticket.staff_id=' . db_input($req['staffId']) . ' AND ticket.status="closed") ';
} elseif (isset($req['staffId'])) {
// closed by any
$where .= ' OR ticket.status="closed" ';
}
$where .= ' ) ';
} elseif ($req['staffId']) {
$where .= ' AND (ticket.staff_id=' . db_input($req['staffId']) . ' AND ticket.status="closed") ';
}
//dates
$startTime = $req['startDate'] && strlen($req['startDate']) >= 8 ? strtotime($req['startDate']) : 0;
$endTime = $req['endDate'] && strlen($req['endDate']) >= 8 ? strtotime($req['endDate']) : 0;
if ($startTime && $startTime > time() or $startTime > $endTime && $endTime > 0) {
$startTime = $endTime = 0;
}
if ($startTime) {
$where .= ' AND ticket.created>=FROM_UNIXTIME(' . $startTime . ')';
}
if ($endTime) {
$where .= ' AND ticket.created<=FROM_UNIXTIME(' . $endTime . ')';
}
//Query
$joins = array();
if ($req['query']) {
$queryterm = db_real_escape($req['query'], false);
// Setup sets of joins and queries
$joins[] = array('from' => 'LEFT JOIN ' . TICKET_THREAD_TABLE . ' thread ON (ticket.ticket_id=thread.ticket_id )', 'where' => "thread.title LIKE '%{$queryterm}%' OR thread.body LIKE '%{$queryterm}%'");
$joins[] = array('from' => 'LEFT JOIN ' . FORM_ENTRY_TABLE . ' tentry ON (tentry.object_id = ticket.ticket_id AND tentry.object_type="T")
LEFT JOIN ' . FORM_ANSWER_TABLE . ' tans ON (tans.entry_id = tentry.id AND tans.value_id IS NULL)', 'where' => "tans.value LIKE '%{$queryterm}%'");
$joins[] = array('from' => 'LEFT JOIN ' . FORM_ENTRY_TABLE . ' uentry ON (uentry.object_id = ticket.user_id
AND uentry.object_type="U")
LEFT JOIN ' . FORM_ANSWER_TABLE . ' uans ON (uans.entry_id = uentry.id
AND uans.value_id IS NULL)
LEFT JOIN ' . USER_TABLE . ' user ON (ticket.user_id = user.id)
LEFT JOIN ' . USER_EMAIL_TABLE . ' uemail ON (user.id = uemail.user_id)', 'where' => "uemail.address LIKE '%{$queryterm}%' OR user.name LIKE '%{$queryterm}%' OR uans.value LIKE '%{$queryterm}%'");
}
// Dynamic fields
$cdata_search = false;
foreach (TicketForm::getInstance()->getFields() as $f) {
if (isset($req[$f->getFormName()]) && ($val = $req[$f->getFormName()])) {
$name = $f->get('name') ? $f->get('name') : 'field_' . $f->get('id');
if ($f->getImpl()->hasIdValue() && is_numeric($val)) {
$cwhere = "cdata.`{$name}_id` = " . db_input($val);
} else {
$cwhere = "cdata.`{$name}` LIKE '%" . db_real_escape($val) . "%'";
}
$where .= ' AND (' . $cwhere . ')';
$cdata_search = true;
}
}
if ($cdata_search) {
$from .= 'LEFT JOIN ' . TABLE_PREFIX . 'ticket__cdata ' . " cdata ON (cdata.ticket_id = ticket.ticket_id)";
}
$sections = array();
foreach ($joins as $j) {
$sections[] = "{$select} {$from} {$j['from']} {$where} AND ({$j['where']})";
}
if (!$joins) {
$sections[] = "{$select} {$from} {$where}";
}
$sql = implode(' union ', $sections);
if (!($res = db_query($sql))) {
return TicketForm::dropDynamicDataView();
}
$tickets = array();
while ($row = db_fetch_row($res)) {
$tickets[] = $row[0];
}
return $tickets;
}
echo $thisclient->getName();
?>
</td></tr>-->
<?php
}
?>
</tbody>
<tbody id="dynamic-form">
<?php
if ($form) {
include CLIENTINC_DIR . 'templates/dynamic-form.tmpl.php';
}
?>
</tbody>
<tbody id="fm"><?php
$tform = TicketForm::getInstance();
if ($_POST) {
$tform->isValidForClient();
}
$tform->render(false);
?>
</tbody>
<tbody>
<?php
if ($cfg && $cfg->isCaptchaEnabled() && (!$thisclient || !$thisclient->isValid())) {
if ($_POST && $errors && !$errors['captcha']) {
$errors['captcha'] = __('Please re-enter the text again');
}
?>
<tr class="captchaRow">
<td class="required"><?php
function _search($req)
{
global $thisstaff, $cfg, $ost;
$result = array();
$criteria = array();
$select = 'SELECT ticket.ticket_id';
$from = ' FROM ' . TICKET_TABLE . ' ticket
LEFT JOIN ' . TICKET_STATUS_TABLE . ' status
ON (status.id = ticket.status_id) ';
//Access control.
$where = ' WHERE ( (ticket.staff_id=' . db_input($thisstaff->getId()) . ' AND status.state="open" )';
if (($teams = $thisstaff->getTeams()) && count(array_filter($teams))) {
$where .= ' OR (ticket.team_id IN (' . implode(',', db_input(array_filter($teams))) . ' ) AND status.state="open" )';
}
if (!$thisstaff->showAssignedOnly() && ($depts = $thisstaff->getDepts())) {
$where .= ' OR ticket.dept_id IN (' . implode(',', db_input($depts)) . ')';
}
$where .= ' ) ';
//Department
if ($req['deptId']) {
$where .= ' AND ticket.dept_id=' . db_input($req['deptId']);
$criteria['dept_id'] = $req['deptId'];
}
//Help topic
if ($req['topicId']) {
$where .= ' AND ticket.topic_id=' . db_input($req['topicId']);
$criteria['topic_id'] = $req['topicId'];
}
// Status
if ($req['statusId'] && ($status = TicketStatus::lookup($req['statusId']))) {
$where .= sprintf(' AND status.id="%d" ', $status->getId());
$criteria['status_id'] = $status->getId();
}
// Flags
if ($req['flag']) {
switch (strtolower($req['flag'])) {
case 'answered':
$where .= ' AND ticket.isanswered =1 ';
$criteria['isanswered'] = 1;
$criteria['state'] = 'open';
$where .= ' AND status.state="open" ';
break;
case 'overdue':
$where .= ' AND ticket.isoverdue =1 ';
$criteria['isoverdue'] = 1;
$criteria['state'] = 'open';
$where .= ' AND status.state="open" ';
break;
}
}
//Assignee
if ($req['assignee'] && strcasecmp($req['status'], 'closed')) {
# assigned-to
$id = preg_replace("/[^0-9]/", "", $req['assignee']);
$assignee = $req['assignee'];
$where .= ' AND ( ( status.state="open" ';
if ($assignee[0] == 't') {
$where .= ' AND ticket.team_id=' . db_input($id);
$criteria['team_id'] = $id;
} elseif ($assignee[0] == 's' || is_numeric($id)) {
$where .= ' AND ticket.staff_id=' . db_input($id);
$criteria['staff_id'] = $id;
}
$where .= ')';
if ($req['staffId'] && !$req['status']) {
//Assigned TO + Closed By
$where .= ' OR (ticket.staff_id=' . db_input($req['staffId']) . ' AND status.state IN("closed")) ';
} elseif ($req['staffId']) {
// closed by any
$where .= ' OR status.state IN("closed") ';
}
$where .= ' ) ';
} elseif ($req['staffId']) {
# closed-by
$where .= ' AND (ticket.staff_id=' . db_input($req['staffId']) . ' AND
status.state IN("closed")) ';
$criteria['state__in'] = array('closed');
$criteria['staff_id'] = $req['staffId'];
}
//dates
$startTime = $req['startDate'] && strlen($req['startDate']) >= 8 ? strtotime($req['startDate']) : 0;
$endTime = $req['endDate'] && strlen($req['endDate']) >= 8 ? strtotime($req['endDate']) : 0;
if ($endTime) {
// $endTime should be the last second of the day, not the first like $startTime
$endTime += 60 * 60 * 24 - 1;
}
if ($startTime && $startTime > time() or $startTime > $endTime && $endTime > 0) {
$startTime = $endTime = 0;
}
if ($startTime) {
$where .= ' AND ticket.created>=FROM_UNIXTIME(' . $startTime . ')';
$criteria['created__gte'] = $startTime;
}
if ($endTime) {
$where .= ' AND ticket.created<=FROM_UNIXTIME(' . $endTime . ')';
$criteria['created__lte'] = $startTime;
}
// Dynamic fields
$cdata_search = false;
foreach (TicketForm::getInstance()->getFields() as $f) {
if (isset($req[$f->getFormName()]) && ($val = $req[$f->getFormName()])) {
$name = $f->get('name') ? $f->get('name') : 'field_' . $f->get('id');
if (is_array($val)) {
$cwhere = '(' . implode(' OR ', array_map(function ($k) use($name) {
return sprintf('FIND_IN_SET(%s, `%s`)', db_input($k), $name);
}, $val)) . ')';
$criteria["cdata.{$name}"] = $val;
} else {
$cwhere = "cdata.`{$name}` LIKE '%" . db_real_escape($val) . "%'";
$criteria["cdata.{$name}"] = $val;
}
$where .= ' AND (' . $cwhere . ')';
$cdata_search = true;
}
}
if ($cdata_search) {
$from .= 'LEFT JOIN ' . TABLE_PREFIX . 'ticket__cdata ' . " cdata ON (cdata.ticket_id = ticket.ticket_id)";
}
//Query
$joins = array();
if ($req['query']) {
// Setup sets of joins and queries
if ($s = $ost->searcher) {
return $s->find($req['query'], $criteria, 'Ticket');
}
}
$sections = array();
foreach ($joins as $j) {
$sections[] = "{$select} {$from} {$j['from']} {$where} AND ({$j['where']})";
}
if (!$joins) {
$sections[] = "{$select} {$from} {$where}";
}
$sql = implode(' union ', $sections);
if (!($res = db_query($sql))) {
return TicketForm::dropDynamicDataView();
}
$tickets = array();
while ($row = db_fetch_row($res)) {
$tickets[] = $row[0];
}
return $tickets;
}
static function dumpTickets($sql, $how = 'csv')
{
// Add custom fields to the $sql statement
$cdata = $fields = $select = array();
foreach (TicketForm::getInstance()->getFields() as $f) {
// Ignore core fields
if (in_array($f->get('name'), array('subject', 'priority'))) {
continue;
} elseif (!$f->hasData() || $f->isPresentationOnly()) {
continue;
}
$name = $f->get('name') ? $f->get('name') : 'field_' . $f->get('id');
$key = '__field_' . $f->get('id');
$cdata[$key] = $f->get('label');
$fields[$key] = $f;
$select[] = "cdata.`{$name}` AS __field_" . $f->get('id');
}
//Inicio 13/04/2016 Se agrego el campo tiempo a cdata para mostrarlo en el archivo junto con el tiempo de duracion del ticket
$cdata['tiempo'] = 'Tiempo de Respuesta';
$select[] = "'Tiempo de Respuesta' AS tiempo";
//Fin 13/04/2016 Se agrego el campo tiempo a cdata para mostrarlo en el archivo junto con el tiempo de duracion del ticket
if ($select) {
$sql = str_replace(' FROM ', ',' . implode(',', $select) . ' FROM ', $sql);
}
return self::dumpQuery($sql, array('number' => __('Ticket Number'), 'ticket_created' => __('Date'), 'subject' => __('Subject'), 'name' => __('From'), 'email' => __('From Email'), 'priority_desc' => __('Priority'), 'dept_name' => __('Department'), 'helptopic' => __('Help Topic'), 'source' => __('Source'), 'status' => __('Current Status'), 'effective_date' => __('Last Updated'), 'duedate' => __('Due Date'), 'isoverdue' => __('Overdue'), 'isanswered' => __('Answered'), 'assigned' => __('Assigned To'), 'staff' => __('Agent Assigned'), 'team' => __('Team Assigned'), 'thread_count' => __('Thread Count'), 'attachments' => __('Attachment Count')) + $cdata, $how, array('modify' => function (&$record, $keys) use($fields) {
foreach ($fields as $k => $f) {
if (($i = array_search($k, $keys)) !== false) {
$record[$i] = $f->export($f->to_php($record[$i]));
}
}
//Inicio 13/04/2016 Se llena la comlumna tiempo del archivo con la duracion de cada ticket
$fecha1 = new DateTime($record[1]);
if ($_REQUEST["status"] == "open" || $_REQUEST["status"] == "assigned") {
$fecha2 = new DateTime(date("Y-m-d H:i:s"));
} else {
$fecha2 = new DateTime($record[10]);
}
$fecha = $fecha1->diff($fecha2);
if ($fecha->y > 0) {
$record[$i + 1] = $fecha->y . "A " . $fecha->m . "M " . $fecha->d . "d " . $fecha->h . "h " . $fecha->i . "m ";
} else {
if ($fecha->m > 0) {
$record[$i + 1] = $fecha->m . "M " . $fecha->d . "d " . $fecha->h . "h " . $fecha->i . "m ";
} else {
if ($fecha->d > 0) {
$record[$i + 1] = $fecha->d . "d " . $fecha->h . "h " . $fecha->i . "m ";
} else {
if ($fecha->h > 0) {
$record[$i + 1] = $fecha->h . "h " . $fecha->i . "m ";
} else {
if ($fecha->i > 0) {
$record[$i + 1] = $fecha->i . "m ";
} else {
$record[$i + 1] = '0m';
}
}
}
}
}
return $record;
//Inicio 13/04/2016 Se llena la comlumna tiempo del archivo con la duracion de cada ticket
}));
}
?>
<table width="800" cellpadding="1" cellspacing="0" border="0" id="ticketInfo">
<tr>
<td colspan="2" width="100%">
<h1>
<a href="tickets.php?id=<?php
echo $ticket->getId();
?>
" title="<?php
echo __('Reload');
?>
"><i class="refresh icon-refresh"></i></a>
<b>
<?php
$subject_field = TicketForm::getInstance()->getField('subject');
echo $subject_field->display($ticket->getSubject());
?>
</b>
<small>#<?php
echo $ticket->getNumber();
?>
</small>
<div class="pull-right">
<a class="action-button" href="tickets.php?a=print&id=<?php
echo $ticket->getId();
?>
"><i class="icon-print"></i> <?php
echo __('Print');
?>
</a>
