/** * 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); }