/** * list sections for one user * * @param int the id of the target user * @param int the offset from the start of the list; usually, 0 or 1 * @param int the number of items to display * @param string the list variant, if any * @return mixed the outcome of the layout * * @see shared/codes.php */ public static function &list_by_date_for_user($user_id, $offset = 0, $count = 10, $variant = 'full') { global $context; // limit the scope of the request for watched sections --that are not also managed $where = "(sections.active='Y'"; if (Surfer::is_logged()) { $where .= " OR sections.active='R'"; } if (Surfer::is_associate()) { $where .= " OR sections.active='N'"; } $where .= ')'; // strip dead sections if (Surfer::get_id() != $user_id && !Surfer::is_associate()) { $where .= " AND ((sections.expiry_date is NULL) " . "OR (sections.expiry_date <= '" . NULL_DATE . "') OR (sections.expiry_date > '" . $context['now'] . "'))"; } // look for watched sections with sub-queries $query = "(SELECT sections.* FROM (SELECT DISTINCT CAST(SUBSTRING(members.anchor, 9) AS UNSIGNED) AS target FROM " . SQL::table_name('members') . " AS members WHERE (members.member LIKE 'user:"******"') AND (members.anchor LIKE 'section:%')) AS ids" . ", " . SQL::table_name('sections') . " AS sections" . " WHERE (sections.id = ids.target)" . "\tAND " . $where . ")"; // include sections assigned to this surfer if ($these_items = Surfer::assigned_sections($user_id)) { $query = "(SELECT sections.* FROM " . SQL::table_name('sections') . " AS sections" . " WHERE sections.id IN (" . join(', ', $these_items) . "))" . " UNION " . $query; } // include sections owned by this surfer $query = "(SELECT sections.* FROM " . SQL::table_name('sections') . " AS sections" . " WHERE sections.owner_id = " . $user_id . ")" . " UNION " . $query; // finalize the query $query .= " ORDER BY edit_date DESC, title LIMIT " . $offset . ',' . $count; // use existing listing facility $output =& Sections::list_selected(SQL::query($query), $variant); return $output; }
/** * restrict the scope of SQL query * * @return string to be inserted into a SQL statement */ public static function get_sql_where() { // display active items $where = "articles.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 articles.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 articles.active='N'"; } else { // include articles from managed sections if ($my_sections = Surfer::assigned_sections()) { $where .= " OR articles.anchor IN ('section:" . join("', 'section:", $my_sections) . "')"; } // include managed pages for editors if ($my_articles = Surfer::assigned_articles()) { $where .= " OR articles.id IN (" . join(', ', $my_articles) . ")"; } } // 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; }
/** * list the articles by rating sum related to a given category or to any other anchor * * Actually list articles by rating sum, then by date. Note that articles are never ranked into a category list. * * Only articles matching following criteria are returned: * - article is visible (active='Y') * - article is restricted (active='R'), but surfer is a logged user * - article is restricted (active='N'), but surfer is an associate * - article has been officially published * - an expiry date has not been defined, or is not yet passed * * @param the target anchor * @param int the offset from the start of the list; usually, 0 or 1 * @param int the number of items to display * @param string the list variant, if any * @return NULL on error, else an ordered array with $url => ($prefix, $label, $suffix, $icon) * * @see categories/print.php * @see categories/view.php */ public static function &list_articles_by_rating_for_anchor($anchor, $offset = 0, $count = 10, $variant = NULL, $lang = false) { global $context; // locate where we are if (!$variant) { $variant = $anchor; } // limit the scope of the request $where = "(articles.active='Y'"; if (Surfer::is_logged()) { $where .= " OR articles.active='R'"; } if (Surfer::is_empowered('S')) { $where .= " OR articles.active='N'"; } // include managed sections if ($my_sections = Surfer::assigned_sections()) { $where .= " OR articles.anchor IN ('section:" . join("', 'section:", $my_sections) . "')"; } // include managed pages for editors if ($my_articles = Surfer::assigned_articles()) { $where .= " OR articles.id IN (" . join(', ', $my_articles) . ")"; } $where .= ")"; // limit the scope by language if ($lang) { $where .= " AND ( articles.language='" . SQL::escape($lang) . "' OR articles.language='')"; } // see only published articles in categories $where .= " AND NOT ((articles.publish_date is NULL) OR (articles.publish_date <= '0000-00-00'))" . " AND (articles.publish_date < '" . $context['now'] . "')"; // only consider live articles $where .= " AND ((articles.expiry_date is NULL) " . "OR (articles.expiry_date <= '" . NULL_DATE . "') OR (articles.expiry_date > '" . $context['now'] . "'))"; // the list of articles $query = "SELECT articles.*" . " FROM (" . SQL::table_name('members') . " AS members" . ", " . SQL::table_name('articles') . " AS articles)" . " WHERE (members.anchor LIKE '" . SQL::escape($anchor) . "')" . "\tAND (members.member_type LIKE 'article')" . "\tAND (articles.id = members.member_id)" . "\tAND " . $where . " ORDER BY rating_sum, edit_date DESC LIMIT " . $offset . ',' . $count; // use existing listing facility $output =& Articles::list_selected(SQL::query($query), $variant); return $output; }