function getPageSearchResults($qArray = array())
{
if (empty($qArray)) {
if (isset($GLOBALS["QUERY"]) && !empty($GLOBALS["QUERY"])) {
$qArray = $GLOBALS["QUERY"];
} else {
return false;
}
}
// --------------------
// --> $select
// --------------------
$mq = "";
// -- URIs to ignore when searching
// 's.ignore' set in field "Ignore URI Matches" in "Search" tab of control pages
if (isset($_SESSION["searchConfig"]["s.ignore"])) {
foreach (array_filter(array_map("trim", explode("\n", $_SESSION["searchConfig"]["s.ignore"]))) as $noSearch) {
$mq .= " AND `uri` NOT " . ($noSearch[0] == "*" ? "REGEXP '" . substr($noSearch, 1) . "'" : " LIKE '%{$noSearch}%'");
}
}
// -- List only records based on field 'status'
// 's.orphans' set in field "Show Orphans" in "Search" tab of control pages
if (isset($_SESSION["searchConfig"]["s.orphans"])) {
$mq .= $_SESSION["searchConfig"]["s.orphans"] == "show" ? " AND (`status`='OK' OR `status`='Orphan')" : " AND `status`='OK'";
}
// -- excluded terms
foreach ($qArray["not"] as $not) {
$shot = search::mysqlFormat($not, true);
$mq .= " AND (`body` " . $shot . ") AND (`title` " . $shot . ") AND (`keywords` " . $shot . ")";
}
// -- required terms
foreach ($qArray["and"] as $and) {
$shot = search::mysqlFormat($and, false);
$mq .= " AND ((`body` " . $shot . ") OR (`title` " . $shot . ") OR (`keywords` " . $shot . "))";
}
/*
// strange, seems to make search slower... (all the rexexps?)
// optional terms
if (!count($qArray["and"])) {
foreach ($qArray["or"] as $or) {
$shot = search::mysqlFormat($or, false);
$mq .= " AND ((`body` ".$shot.") OR (`title` ".$shot.") OR (`keywords` ".$shot."))";
}
}
*/
//TODO: rationalize call to $_MIME->get_ctype
// $_MIME = new OS_TypeList(); defined in config.php
// -- filetype
foreach ($qArray["typey"] as $types) {
$types = trim(preg_replace("/^filetype:/", "", $types));
if (preg_match("/\\w+/", $types)) {
$mq .= " AND (`ctype`='" . (($types = $_MIME->get_ctype($types)) ? $types : "none") . "')";
}
}
//TODO: rationalize call to $_MIME->get_ctype
// $_MIME = new OS_TypeList(); defined in config.php
// -- filetype
foreach ($qArray["typen"] as $types) {
$types = trim(preg_replace("/^filetype:/", "", $types));
if (preg_match("/\\w+/", $types)) {
$mq .= " AND (`ctype`!='" . (($types = $_MIME->get_ctype($types)) ? $types : "none") . "')";
}
}
$sql = "SELECT * " . ", CONCAT(`body`,`title`,`keywords`) AS `data` " . "FROM `" . $this->table . "` " . "WHERE (`unlist`='false')" . $mq . ";";
//echo "<p>$sql</p>";
// return $this->query($sql); // too slow!!!
return mysql_unbuffered_query($sql);
// mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don't have to wait until the complete SQL query has been performed. The benefits of mysql_unbuffered_query() come at a cost: you cannot use mysql_num_rows() and mysql_data_seek() on a result set returned from mysql_unbuffered_query(), until all rows are fetched. You also have to fetch all result rows from an unbuffered SQL query before you can send a new SQL query to MySQL, using the same link_identifier.
}
function getSqlForUserSearch($qArray = array(), $status = "active", $inst = "NOR")
{
if (empty($qArray)) {
if (isset($GLOBALS["QUERY"]) && !empty($GLOBALS["QUERY"])) {
$qArray = $GLOBALS["QUERY"];
} else {
return false;
}
}
$where = $wNOT = $wAND = $cvAND = $wOR = array();
// -------------------------
// Construct 'WHERE status=...'
// -------------------------
// NB: LM.lm_key should correspond to appointment start date
// NB: LM.lm_status should correspond to appointment end date
// NB: for 'active' it should be enough to check that any end date is in the future
switch ($status) {
case "active":
//$where[] = "LM.lm_key<=CURDATE()"; // start date in past
$where[] = "(CURDATE()<=LM.lm_status) OR (LM.lm_status='')";
// end date in future
break;
case "passive":
case "expired":
$where[] = "LM.lm_status<=CURDATE()";
// end date in past
break;
}
// We are only interested in Records from Nordita Stockholm era:
$where[] = "'2007-01-01'<=LM.lm_status";
// -------------------------
// Construct 'WHERE inst=...'
// -------------------------
// NB: 'combodb.zzz_avatars.av_institute' may not be reliable!
if ($inst) {
if ($inst == "NOR") {
$where[] = "UM.um_uid='" . MYPEAR_NORDITA_EMPLOYEES . "'";
} else {
$where[] = "AV.av_institute REGEXP '" . mb_strtoupper($inst) . "'";
}
}
// ------------------------------------------
// Need to make separate search in CV records
// ------------------------------------------
/*
$cvs = array();
$cvarray = $this->getCvArray(1);
if (!empty($cvarray)) {
foreach ($cvarray as $id => $cv) {
if (!empty($cv["plain"])) {
$cvs[$id] = $cv["plain"];
}
}
}
*/
foreach (functions::callMethod('people', 'lib/people', 'getPeopleArray', 'all') as $id => $record) {
$cv = isset($record["nw_cv"]["plain"]) ? strtolower($record["nw_cv"]["plain"]) : "";
// NOT (-terms)
foreach ($qArray["not"] as $lg) {
if (preg_match("/" . $lg . "/", $cv)) {
$wNOT[$record["nw_id_ea"]] = "`av_id`<>'" . $record["nw_id_ea"] . "'";
}
}
// AND (+terms)
//foreach (array_merge($qArray["_and"],$qArray["_or"]) as $lg) {
foreach ($qArray["_and"] as $lg) {
if (preg_match("/" . $lg . "/", $cv)) {
$cvAND[$record["nw_id_ea"]] = "`av_id`='" . $record["nw_id_ea"] . "'";
}
}
// OR (optional terms)
if (!count($qArray["_and"])) {
foreach ($qArray["_or"] as $lg) {
if (preg_match("/" . $lg . "/", $cv)) {
$wOR[$record["nw_id_ea"]] = "`av_id`='" . $record["nw_id_ea"] . "'";
}
}
}
}
//echo "cvAND";debug::rr($cvAND);echo "wOR";debug::rr($wOR);echo "wNOT";debug::rr($wNOT);echo "where";debug::rr($where);echo "<hr>";
// -------------------------
// Construct 'WHERE user=...'
// NB "_and","_or" fields have had any phrases eliminated
// --------------------
// Nordita branch
$wtmp = array();
if (preg_match("/stockholm/i", strtolower($qArray["original"]))) {
$wtmp[] = "(AV.av_phone!='') AND (AV.av_phone NOT LIKE '%+45%')";
}
if (preg_match("/copenhagen/i", strtolower($qArray["original"]))) {
$wtmp[] = "AV.av_phone LIKE '%+45%'";
}
if (count($wtmp)) {
$w1[] = "(" . join(") OR (", $wtmp) . ")";
}
// --------------------
// NOT (-terms)
$wtmp = array();
foreach ($qArray["not"] as $lg) {
$shot = search::mysqlFormat($lg, true);
foreach ($this->searchableFields as $field) {
$wNOT[] = "`" . $field . "` " . $shot . "";
}
}
if (count($wNOT) == 1) {
$where[] = array_pop($wNOT);
} elseif (count($wNOT) > 1) {
$where[] = "" . join(") AND (", $wNOT) . "";
}
// --------------------
// AND (+terms)
foreach ($qArray["_and"] as $lg) {
$wtmp = array();
$shot = search::mysqlFormat($lg, false);
foreach ($this->searchableFields as $field) {
$wtmp[] = "`" . $field . "` " . $shot . "";
}
$wtmp = array_merge($wtmp, $cvAND);
if (count($wtmp)) {
$wAND[] = "(" . join(") OR (", $wtmp) . ")";
}
}
// --------------------
// OR (optional terms)
if (!count($qArray["_and"])) {
foreach ($qArray["_or"] as $lg) {
$shot = search::mysqlFormat($lg, false);
foreach ($this->searchableFields as $field) {
$wOR[] = "`" . $field . "` " . $shot . "";
}
}
}
// --------------------
if (count($wOR) == 1) {
$where[] = array_pop($wOR);
} elseif (count($wOR) > 1) {
$where[] = "(" . join(") OR (", $wOR) . ")";
}
//echo "wOR";debug::rr($wOR);debug::rr($where);
// --------------------
// -- Assemble SQL SELECT query
// --------------------
return "SELECT " . " AV.*, " . " UM.um_status, " . " LOWER(LM.lm_value) AS lm_value, " . " LM.lm_key, " . " LM.lm_status, " . " LM.lm_option, " . " MDB.acc_kth, " . " MDB.acc_su, " . " MDB.maildrop, " . " MDB.user " . "FROM " . " zzz_avatars AS AV " . " LEFT JOIN zzz_unit_members AS UM ON AV.av_id=UM.um_avid " . " LEFT JOIN zzz_lists AS L ON AV.av_id=L.l_parent " . " LEFT JOIN zzz_list_members AS LM ON L.l_id=LM.lm_lid " . " LEFT JOIN maildb.master AS MDB ON AV.av_id=MDB.m_myorg " . "WHERE " . " (UM.um_uid='" . MYPEAR_NORDITA_EMPLOYEES . "') " . " AND (UM.um_status REGEXP '" . MYPEAR_REGEXP_IS_EMPLOYEE_OR_VISITOR . "') " . " AND (LM.lm_option REGEXP '" . MYPEAR_REGEXP_NORDITA_IS_HOME_INSTITUTE . "') " . (count($where) ? " AND (" . join(") AND (", $where) . ")" : "") . (count($wAND) ? " AND ( (" . join(") AND (", $wAND) . ") )" : "");
}
