/** * list pages browsed by one user * * @param int id of the visiting user * @param int the maximum size of the returned list * @param int maximum age of visit, in seconds * @return array a compact list of links, or NULL */ public static function list_for_user($user, $count = 3, $timeout = 259200) { global $context; // return by reference $output = NULL; // sanity check if (!$user) { return $output; } // only consider recent presence records $threshold = gmstrftime('%Y-%m-%d %H:%M:%S', time() - $timeout); // limit the scope of the request $where = "visits.active='Y'"; if (Surfer::is_logged() || Surfer::is_teased()) { $where .= " OR visits.active='R'"; } if (Surfer::is_associate() || Surfer::is_teased()) { $where .= " OR visits.active='N'"; } // select matching links $query = "SELECT * FROM " . SQL::table_name('visits') . " AS visits" . " WHERE (visits.user_id = " . SQL::escape($user) . ")" . "\tAND (visits.edit_date >= '" . SQL::escape($threshold) . "')" . "\tAND (" . $where . ")" . " ORDER BY visits.edit_date DESC LIMIT " . $count; if (!($result = SQL::query($query))) { return $output; } // empty list if (!SQL::count($result)) { return $output; } // process all items in the list $output = array(); while ($item = SQL::fetch($result)) { // identify the visited page if (!($anchor = Anchors::get($item['anchor']))) { continue; } // ensure this one is visible if (!$anchor->is_viewable()) { continue; } // url to the visited page $url = $anchor->get_url(); // title of the visited page $label = $anchor->get_title(); // list all components for this item $output[$url] = $label; } // end of processing SQL::free($result); return $output; }
/** * restrict the scope of SQL query * * @return string to be inserted into a SQL statement */ private static function get_sql_where() { // display active items $where = "sections.active='Y'"; // add restricted items to members and for trusted hosts, or if teasers are allowed if (Surfer::is_logged() || Surfer::is_trusted() || Surfer::is_teased()) { $where .= " OR sections.active='R'"; } // include hidden items for associates and for trusted hosts, or if teasers are allowed if (Surfer::is_associate() || Surfer::is_trusted() || Surfer::is_teased()) { $where .= " OR sections.active='N'"; } else { // include content from managed sections if ($my_sections = Surfer::assigned_sections()) { $where .= " OR sections.anchor IN ('section:" . join("', 'section:", $my_sections) . "')" . " OR sections.id IN (" . join(", ", $my_sections) . ")"; } } // end of active filter $where = '(' . $where . ')'; // job done return $where; }
/** * search for some keywords in all files * * Only files matching following criteria are returned: * - file is visible (active='Y') * - file is restricted (active='R'), but surfer is a logged user * - file is restricted (active='N'), but surfer is an associate * * @param string searched tokens * @param float maximum score to look at * @param int the number of items to display * @param string the list variant, if any * @return NULL on error, else an ordered array of array($score, $summary) */ public static function &search($pattern, $offset = 1.0, $count = 50, $variant = 'search') { global $context; // sanity check if (!($pattern = trim($pattern))) { $output = NULL; return $output; } // limit the scope of the request $where = "active='Y'"; if (Surfer::is_logged() || Surfer::is_teased()) { $where .= " OR active='R'"; } if (Surfer::is_associate() || Surfer::is_teased()) { $where .= " OR active='N'"; } else { // files attached to managed sections if ($my_sections = Surfer::assigned_sections()) { $where .= " OR anchor IN ('section:" . join("', 'section:", $my_sections) . "')"; // files attached to pages in managed sections $where .= " OR anchor IN (SELECT CONCAT('article:', id) FROM " . SQL::table_name('articles') . " WHERE anchor IN ('section:" . join("', 'section:", $my_sections) . "'))"; } // files attached to managed articles if ($my_articles = Surfer::assigned_articles()) { $where .= " OR anchor IN ('article:" . join("', 'article:", $my_articles) . "')"; } } // how to compute the score for files $score = "(MATCH(title, source, keywords)" . " AGAINST('" . SQL::escape($pattern) . "' IN BOOLEAN MODE)" . "/SQRT(GREATEST(1.1, DATEDIFF(NOW(), edit_date))))"; // the list of files $query = "SELECT *, " . $score . " AS score FROM " . SQL::table_name('files') . " AS files" . " WHERE (" . $score . " < " . $offset . ") AND (" . $score . " > 0)" . " AND (" . $where . ")" . " ORDER BY score DESC" . " LIMIT " . $count; // do the query $output =& Files::list_selected(SQL::query($query), $variant); return $output; }
/** * search for some keywords in all categories * * Only categories matching following criteria are returned: * - category is visible (active='Y') * - category is restricted (active='R'), but surfer is a logged user * - category is restricted (active='N'), but surfer is an associate * - an expiry date has not been defined, or is not yet passed * * @param string the search string * @param float maximum score to look at * @param int the number of items to display * @param string the list variant, if any * @return NULL on error, else an ordered array of array($score, $summary) * @see #list_selected for $variant description */ public static function &search($pattern, $offset = 1.0, $count = 50, $variant = 'search') { global $context; // sanity check if (!($pattern = trim($pattern))) { $output = NULL; return $output; } // limit the scope of the request $where = "categories.active='Y'"; if (Surfer::is_member() || Surfer::is_teased()) { $where .= " OR categories.active='R'"; } if (Surfer::is_associate() || Surfer::is_teased()) { $where .= " OR categories.active='N'"; } $where = '(' . $where . ')'; // only consider live categories $where .= ' AND ((categories.expiry_date is NULL)' . "\tOR (categories.expiry_date <= '" . NULL_DATE . "') OR (categories.expiry_date > '" . $context['now'] . "'))"; // how to compute the score for categories $score = "(MATCH(title, introduction, description, keywords)" . " AGAINST('" . SQL::escape($pattern) . "' IN BOOLEAN MODE)" . "/SQRT(GREATEST(1.1, DATEDIFF(NOW(), edit_date))))"; // the list of categories $query = "SELECT *," . " " . $score . " AS score" . " FROM " . SQL::table_name('categories') . " AS categories" . " WHERE (" . $score . " < " . $offset . ") AND (" . $score . " > 0)" . " AND " . $where . " ORDER BY score DESC" . " LIMIT " . $count; $output =& Categories::list_selected(SQL::query($query), $variant); return $output; }