private function query_all_possible_IDs($data = array(), $sort = 'sent', $order = 'DESC', $sortorder = '', $cache_key = '')
{
$ids = array();
$__folders = array();
$match = '';
$direction = $attachment = $size = $folders = '';
$tag_id_list = '';
$a = "";
$id = "";
$fields = array("@(subject,body)", "@from", "@to", "@subject", "@body", "@attachment_types");
$emailfilter = $this->assemble_email_address_filter();
$session = Registry::get('session');
$i = 0;
while (list($k, $v) = each($data['match'])) {
if ($v == "@attachment_types") {
list($k, $v) = each($data['match']);
$i++;
if ($v == "any") {
$data['match'][$i - 1] = "";
$data['match'][$i] = "";
$a = "attachments > 0 AND ";
}
}
if (substr($v, 0, 7) == "http://") {
$v = preg_replace("/\\./", "X", $v);
$data['match'][$i] = preg_replace("/http\\:\\/\\//", "__URL__", $v);
}
if (!in_array($v, $fields) && $i > 0 && strchr($v, "@")) {
if (substr($v, 0, 1) == "@") {
$v = substr($v, 1, strlen($v) - 1);
if ($data['match'][$i - 1] == "@from") {
$data['match'][$i - 1] = "@fromdomain";
}
if ($data['match'][$i - 1] == "@to") {
$data['match'][$i - 1] = "@todomain";
}
}
$data['match'][$i] = $this->fix_email_address_for_sphinx($v);
}
$i++;
}
$match = implode(" ", $data['match']);
if ($emailfilter) {
if (strlen($match) > 2) {
$match = "( {$match} ) & {$emailfilter}";
} else {
$match = $emailfilter;
}
}
if ($match == " ") {
$match = "";
}
if ($data['sort'] == 'from' || $data['sort'] == 'subj') {
$sortorder = '';
}
$date = fixup_date_condition('sent', $data['date1'], $data['date2']);
if ($date) {
$date .= " AND ";
}
if (isset($data['direction']) && $data['direction'] != '') {
$direction = "direction = " . $data['direction'] . " AND ";
}
if (isset($data['size']) && $data['size']) {
$data['size'] = preg_replace("/\\s/", "", $data['size']);
if (preg_match("/^(\\>|\\<)\\={0,}\\d{1,}\$/", $data['size'])) {
$size = "size " . $data['size'] . " AND ";
}
}
if (isset($data['attachment_type']) && strstr($data['attachment_type'], 'any')) {
$a = "attachments > 0 AND ";
} else {
if (isset($data['has_attachment']) && $data['has_attachment'] == 1) {
$attachment = "attachments > 0 AND ";
}
}
if (isset($data['id']) && $data['id']) {
$data['id'] = preg_replace("/ /", ",", substr($data['id'], 1, strlen($data['id'])));
$id = " id IN (" . $data['id'] . ") AND ";
}
if (ENABLE_FOLDER_RESTRICTIONS == 1) {
$s = explode(" ", $data['folders']);
while (list($k, $v) = each($s)) {
if (in_array($v, $session->get("folders"))) {
array_push($__folders, $v);
}
}
if (count($__folders) > 0) {
$folders = "folder IN (" . implode(",", $__folders) . ") AND ";
} else {
$folders = "folder IN (" . implode(",", $session->get("folders")) . ") AND ";
}
}
if (isset($data['tag']) && $data['tag']) {
$id_list = $this->get_sphinx_id_list($data['tag'], SPHINX_TAG_INDEX, 'tag');
$query = $this->sphx->query("SELECT id FROM " . SPHINX_MAIN_INDEX . " WHERE {$folders} id IN ({$id_list}) {$sortorder} LIMIT 0," . MAX_SEARCH_HITS . " OPTION max_matches=" . MAX_SEARCH_HITS);
} else {
if (isset($data['note']) && $data['note']) {
$id_list = $this->get_sphinx_id_list($data['note'], SPHINX_NOTE_INDEX, 'note');
$query = $this->sphx->query("SELECT id FROM " . SPHINX_MAIN_INDEX . " WHERE {$folders} id IN ({$id_list}) {$sortorder} LIMIT 0," . MAX_SEARCH_HITS . " OPTION max_matches=" . MAX_SEARCH_HITS);
} else {
if (ENABLE_FOLDER_RESTRICTIONS == 1 && isset($data['extra_folders']) && $data['extra_folders']) {
$ids_in_extra_folders = $this->get_sphinx_id_list_by_extra_folders($data['extra_folders']);
$query = $this->sphx->query("SELECT id FROM " . SPHINX_MAIN_INDEX . " WHERE {$a} {$id} {$date} {$attachment} {$direction} {$size} MATCH('{$match}') AND id IN ({$ids_in_extra_folders}) {$sortorder} LIMIT 0," . MAX_SEARCH_HITS . " OPTION max_matches=" . MAX_SEARCH_HITS);
} else {
$query = $this->sphx->query("SELECT id FROM " . SPHINX_MAIN_INDEX . " WHERE {$a} {$id} {$date} {$attachment} {$direction} {$size} {$folders} MATCH('{$match}') {$sortorder} LIMIT 0," . MAX_SEARCH_HITS . " OPTION max_matches=" . MAX_SEARCH_HITS);
}
}
}
$total_found = $query->total_found;
if (ENABLE_SYSLOG == 1) {
syslog(LOG_INFO, sprintf("sphinx query: '%s' in %.2f s, %d hits, %d total found", $query->query, $query->exec_time, $query->num_rows, $total_found));
}
/*
* build an id list
*/
$q = "";
if (isset($query->rows)) {
foreach ($query->rows as $a) {
array_push($ids, $a['id']);
if ($q) {
$q .= ",?";
} else {
$q = "?";
}
}
}
/*
* if the query was requested to be sorted by sender or subject, then sphinx cannot do
* that, so we assemble the list of all sphinx IDs matching the query
*/
if ($data['sort'] == 'from' || $data['sort'] == 'subj') {
$fs_query = $this->db->query("SELECT id FROM " . TABLE_META . " WHERE id IN ({$q}) ORDER BY `{$sort}` {$order}", $ids);
$ids = array();
foreach ($fs_query->rows as $q) {
array_push($ids, $q['id']);
}
}
if (MEMCACHED_ENABLED && $cache_key) {
$memcache = Registry::get('memcache');
$memcache->add($cache_key, array('ts' => time(), 'total_hits' => count($ids), 'ids' => $ids, 'total_found' => $total_found), 0, MEMCACHED_TTL);
}
return array($total_found, $ids);
}
public function search_audit($data = array())
{
$where = '';
$arr = $results = array();
$from = 0;
$sort = "ts";
$order = "DESC";
$sortorder = "ORDER BY ts DESC";
$date1 = $date2 = 0;
$q = '';
$session = Registry::get('session');
if ($data['sort'] == "user") {
$sort = "email";
}
if ($data['sort'] == "ipaddr") {
$sort = "ipaddr";
}
if ($data['sort'] == "ref") {
$sort = "meta_id";
}
if ($data['sort'] == "action") {
$sort = "action";
}
if ($data['sort'] == "description") {
$sort = "description";
}
if ($data['order'] == 1) {
$order = "ASC";
}
$sortorder = "ORDER BY `{$sort}` {$order}";
if (isset($data['action']) && $data['action'] != ACTION_ALL) {
$where .= " AND ( " . $this->append_search_criteria("action", $data['action'], $arr) . " )";
}
if (isset($data['ipaddr']) && $data['ipaddr']) {
$where .= " AND ( " . $this->append_search_criteria("ipaddr", $data['ipaddr'], $arr) . " )";
}
if (isset($data['user']) && $data['user']) {
$where .= " AND ( " . $this->append_search_criteria("email", $data['user'], $arr) . " )";
}
if (isset($data['ref']) && $data['ref']) {
$where .= " AND ( " . $this->append_search_criteria("meta_id", $data['ref'], $arr) . " )";
}
if (Registry::get('admin_user') == 0 && RESTRICTED_AUDITOR == 1) {
$auditdomains = $session->get("auditdomains");
while (list($k, $v) = each($auditdomains)) {
if ($q) {
$q .= ",";
}
$q .= "?";
array_push($arr, $v);
}
$where .= " AND domain IN ({$q}) ";
reset($session->get("auditdomains"));
}
if (isset($data['date1'])) {
$date1 = $data['date1'];
}
if (isset($data['date2'])) {
$date2 = $data['date2'];
}
$date = fixup_date_condition('ts', $date1, $date2);
if ($date) {
$where .= " AND {$date} ";
}
if ($where) {
$where = " WHERE " . substr($where, 5, strlen($where));
}
$from = $data['page_len'] * $data['page'];
if ($where) {
$query = $this->db->query("SELECT COUNT(*) AS count FROM " . TABLE_AUDIT . " {$where}", $arr);
$n = $query->row['count'];
if (ENABLE_SYSLOG == 1) {
syslog(LOG_INFO, sprintf("audit query: '%s' in %.2f s, %d hits", $query->query, $query->exec_time, $query->row['count']));
}
} else {
$n = MAX_AUDIT_HITS;
}
if ($n > 0) {
if ($n > MAX_AUDIT_HITS) {
$n = MAX_AUDIT_HITS;
}
$query = $this->db->query("SELECT * FROM " . TABLE_AUDIT . " {$where} {$sortorder} LIMIT {$from}," . $data['page_len'], $arr);
$this->session->set("audit_query", array('where' => $where, 'sortorder' => $sortorder, 'arr' => $arr));
if (ENABLE_SYSLOG == 1) {
syslog(LOG_INFO, sprintf("audit query: '%s', param: '%s' in %.2f s, %d hits", $query->query, implode(' ', $arr), $query->exec_time, $query->num_rows));
}
if (isset($query->rows)) {
foreach ($query->rows as $a) {
$a['description'] = preg_replace("/\"/", "'", $a['description']);
$results[] = array('id' => $a['meta_id'], 'piler_id' => isset($m[$a['meta_id']]) ? $m[$a['meta_id']] : '', 'action' => $a['action'], 'email' => $a['email'], 'date' => date(DATE_TEMPLATE . " H:i", $a['ts']), 'ipaddr' => DEMO_MODE == 1 ? anonimize_ip_addr($a['ipaddr']) : $a['ipaddr'], 'description' => $a['description'], 'shortdescription' => make_short_string($a['description'], MAX_CGI_FROM_SUBJ_LEN));
}
}
}
return array($n, $results);
}
