/**
* Returns tickets by searching for its properties.
*
* @param array $info An array of properties to search for.
* @param boolean $munge Munge the query (?)
* @param boolean $perowner Group the results per owner?
*
* @return array An array of ticket information hashes.
* @throws Whups_Exception
*/
public function getTicketsByProperties(array $info, $munge = true, $perowner = false)
{
if (isset($info['queue']) && !count($info['queue'])) {
return array();
}
// Search conditions.
$where = $this->_generateWhere('whups_tickets', array('ticket_id', 'type_id', 'state_id', 'priority_id', 'queue_id'), $info, 'integer');
$where2 = $this->_generateWhere('whups_tickets', array('user_id_requester'), $info, 'string');
if (empty($where)) {
$where = $where2;
} elseif (!empty($where2)) {
$where .= ' AND ' . $where2;
}
// Add summary filter if present.
if (!empty($info['summary'])) {
$where = $this->_addWhere($where, 1, 'LOWER(whups_tickets.ticket_summary) LIKE ' . $this->_db->quotestring('%' . Horde_String::lower($info['summary']) . '%'));
}
// Add date fields.
if (!empty($info['ticket_timestamp'])) {
$where = $this->_addDateWhere($where, $info['ticket_timestamp'], 'ticket_timestamp');
}
if (!empty($info['date_updated'])) {
$where = $this->_addDateWhere($where, $info['date_updated'], 'date_updated');
}
if (!empty($info['date_assigned'])) {
$where = $this->_addDateWhere($where, $info['date_assigned'], 'date_assigned');
}
if (!empty($info['date_resolved'])) {
$where = $this->_addDateWhere($where, $info['date_resolved'], 'date_resolved');
}
if (!empty($info['ticket_due'])) {
$where = $this->_addDateWhere($where, $info['ticket_due'], 'ticket_due');
}
$fields = array('ticket_id AS id', 'ticket_summary AS summary', 'user_id_requester', 'state_id AS state', 'type_id AS type', 'priority_id AS priority', 'queue_id AS queue', 'date_updated', 'date_assigned', 'date_resolved', 'version_id AS version');
$fields = $this->_prefixTableToColumns('whups_tickets', $fields) . ', whups_tickets.ticket_timestamp AS timestamp, whups_tickets.ticket_due AS due';
$tables = 'whups_tickets';
$join = '';
$groupby = 'whups_tickets.ticket_id, whups_tickets.ticket_summary, whups_tickets.user_id_requester, whups_tickets.state_id, whups_tickets.type_id, whups_tickets.priority_id, whups_tickets.queue_id, whups_tickets.ticket_timestamp, whups_tickets.ticket_due, whups_tickets.date_updated, whups_tickets.date_assigned, whups_tickets.date_resolved';
// State filters.
if (isset($info['category'])) {
if (is_array($info['category'])) {
$cat = '';
foreach ($info['category'] as $category) {
if (!empty($cat)) {
$cat .= ' OR ';
}
$cat .= 'whups_states.state_category = ' . $this->_db->quotestring($category);
}
$cat = ' AND (' . $cat . ')';
} else {
$cat = isset($info['category']) ? ' AND whups_states.state_category = ' . $this->_db->quotestring($info['category']) : '';
}
} else {
$cat = '';
}
// Type filters.
if (isset($info['type_id'])) {
if (is_array($info['type_id'])) {
$t = array();
foreach ($info['type_id'] as $type) {
$t[] = 'whups_tickets.type_id = ' . $this->_db->quotestring($type);
}
$t = ' AND (' . implode(' OR ', $t) . ')';
} else {
$t = isset($info['type_id']) ? ' AND whups_tickets.type_id = ' . $this->_db->quotestring($info['type_id']) : '';
}
$this->_addWhere($where, $t, $t);
}
$nouc = isset($info['nouc']) ? " AND whups_states.state_category <> 'unconfirmed'" : '';
$nores = isset($info['nores']) ? " AND whups_states.state_category <> 'resolved'" : '';
$nonew = isset($info['nonew']) ? " AND whups_states.state_category <> 'new'" : '';
$noass = isset($info['noass']) ? " AND whups_states.state_category <> 'assigned'" : '';
$uc = isset($info['uc']) ? " AND whups_states.state_category = 'unconfirmed'" : '';
$res = isset($info['res']) ? " AND whups_states.state_category = 'resolved'" : '';
$new = isset($info['new']) ? " AND whups_states.state_category = 'new'" : '';
$ass = isset($info['ass']) ? " AND whups_states.state_category = 'assigned'" : '';
// If there are any state filters, add them in.
if ($nouc || $nores || $nonew || $noass || $uc || $res || $new || $ass || $cat) {
$where = $this->_addWhere($where, 1, "(whups_tickets.type_id = whups_states.type_id AND whups_tickets.state_id = whups_states.state_id{$nouc}{$nores}{$nonew}{$noass}{$uc}{$res}{$new}{$ass}{$cat})");
}
// Initialize join clauses.
$join = '';
// Handle owner properties.
if (isset($info['owner'])) {
$join .= ' INNER JOIN whups_ticket_owners ON whups_tickets.ticket_id = whups_ticket_owners.ticket_id AND ';
if (is_array($info['owner'])) {
$clauses = array();
foreach ($info['owner'] as $owner) {
$clauses[] = 'whups_ticket_owners.ticket_owner = ' . $this->_db->quotestring($owner);
}
$join .= '(' . implode(' OR ', $clauses) . ')';
} else {
$join .= 'whups_ticket_owners.ticket_owner = ' . $this->_db->quotestring($info['owner']);
}
}
if (isset($info['notowner'])) {
if ($info['notowner'] === true) {
// Filter for tickets with no owner.
$join .= ' LEFT JOIN whups_ticket_owners ON whups_tickets.ticket_id = whups_ticket_owners.ticket_id AND whups_ticket_owners.ticket_owner IS NOT NULL';
} else {
$join .= ' LEFT JOIN whups_ticket_owners ON whups_tickets.ticket_id = whups_ticket_owners.ticket_id AND whups_ticket_owners.ticket_owner = ' . $this->_db->quotestring($info['notowner']);
}
$where = $this->_addWhere($where, 1, 'whups_ticket_owners.ticket_id IS NULL');
}
if ($munge) {
$myqueues = $GLOBALS['registry']->hasMethod('tickets/listQueues') == $GLOBALS['registry']->getApp();
$myversions = $GLOBALS['registry']->hasMethod('tickets/listVersions') == $GLOBALS['registry']->getApp();
$fields = "{$fields}, " . 'whups_types.type_name AS type_name, ' . 'whups_states.state_name AS state_name, ' . 'whups_states.state_category AS state_category, ' . 'whups_priorities.priority_name AS priority_name';
$join .= ' INNER JOIN whups_types ON whups_tickets.type_id = whups_types.type_id' . ' INNER JOIN whups_states ON whups_tickets.state_id = whups_states.state_id' . ' INNER JOIN whups_priorities ON whups_tickets.priority_id = whups_priorities.priority_id' . ' INNER JOIN whups_states state2 ON whups_tickets.type_id = state2.type_id';
$groupby .= ', whups_types.type_name, whups_states.state_name, whups_states.state_category';
if ($myversions) {
$versions = array();
$fields .= ', whups_versions.version_name AS version_name' . ', whups_versions.version_description AS version_description' . ', whups_versions.version_active AS version_active';
$join .= ' LEFT JOIN whups_versions ON whups_tickets.version_id = whups_versions.version_id';
$groupby .= ', whups_versions.version_name, whups_versions.version_description, whups_versions.version_active, whups_tickets.version_id';
}
if ($myqueues) {
$queues = array();
$fields .= ', whups_queues.queue_name AS queue_name';
$join .= ' INNER JOIN whups_queues ON whups_tickets.queue_id = whups_queues.queue_id';
$groupby .= ', whups_queues.queue_name';
}
$groupby .= ', whups_priorities.priority_name';
}
if ($perowner) {
$join .= ' LEFT JOIN whups_ticket_owners ON whups_tickets.ticket_id = whups_ticket_owners.ticket_id';
$fields .= ', whups_ticket_owners.ticket_owner AS owner';
$groupby .= ', whups_ticket_owners.ticket_owner';
}
$query = "SELECT {$fields} FROM {$tables}{$join} " . (!empty($where) ? "WHERE {$where} " : '') . 'GROUP BY ' . $groupby;
try {
$info = $this->_db->selectAll($query);
} catch (Horde_Db_Exception $e) {
throw new Whups_Exception($e);
}
if (!count($info)) {
return array();
}
$info = $this->_fromBackend($info);
$tickets = array();
foreach ($info as $ticket) {
if ($munge) {
if (!$myqueues) {
if (!isset($queues[$ticket['queue']])) {
$queues[$ticket['queue']] = $GLOBALS['registry']->call('tickets/getQueueDetails', array($ticket['queue']));
}
$ticket['queue_name'] = $queues[$ticket['queue']]['name'];
if (isset($queues[$ticket['queue']]['link'])) {
$ticket['queue_link'] = $queues[$ticket['queue']]['link'];
}
}
if (!$myversions) {
if (!isset($versions[$ticket['version']])) {
$versions[$ticket['version']] = $GLOBALS['registry']->call('tickets/getVersionDetails', array($ticket['version']));
}
$ticket['version_name'] = $versions[$ticket['version']]['name'];
if (isset($versions[$ticket['version']]['link'])) {
$ticket['version_link'] = $versions[$ticket['version']]['link'];
}
}
$ticket['requester_formatted'] = Whups::formatUser($ticket['user_id_requester'], false, true, true);
}
$tickets[$ticket['id']] = $ticket;
}
foreach ($this->getOwners(array_keys($tickets)) as $id => $owners) {
$tickets[$id]['owners'] = $owners;
foreach ($owners as $owner) {
$tickets[$id]['owners_formatted'][] = Whups::formatUser($owner, false, true, true);
}
}
$attributes = $this->getTicketAttributesWithNames(array_keys($tickets));
foreach ($attributes as $row) {
$attribute_id = 'attribute_' . $row['attribute_id'];
try {
$tickets[$row['id']][$attribute_id] = Horde_Serialize::unserialize($row['attribute_value'], Horde_Serialize::JSON);
} catch (Horde_Serialize_Exception $e) {
$tickets[$row['id']][$attribute_id] = $row['attribute_value'];
}
$tickets[$row['id']][$attribute_id . '_name'] = $row['attribute_name'];
}
return array_values($tickets);
}
