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