/**
* count record for one anchor
*
* @param the selected anchor (e.g., 'article:12')
* @param boolean TRUE if this can be optionnally avoided
* @return the resulting count, or NULL on error
*/
public static function count_for_anchor($anchor, $optional = FALSE)
{
global $context;
// sanity check
if (!$anchor) {
return NULL;
}
// request the database only in hi-fi mode
if ($optional && $context['skins_with_details'] != 'Y') {
return NULL;
}
// select among available items
$query = "SELECT COUNT(*) as count" . " FROM " . SQL::table_name('links') . " AS links" . " WHERE links.anchor LIKE '" . SQL::escape($anchor) . "'";
return SQL::query_scalar($query);
}
/**
* locate some reference
*
* @param string the anchor (e.g., 'article:123')
* @return string longitude and latitude of the anchor, else NULL
*
* @see articles/layout_articles_as_contents.php
* @see articles/layout_articles_as_feed.php
*/
public static function locate_anchor($anchor)
{
global $context;
// the request
$query = "SELECT CONCAT(location.latitude, ', ', location.longitude) as geolocation FROM " . SQL::table_name('locations') . " AS location" . " WHERE (location.anchor LIKE '" . SQL::escape($anchor) . "') " . " ORDER BY location.edit_date DESC, location.geo_place_name LIMIT 0, 1";
// the location, if any
$output = SQL::query_scalar($query);
return $output;
}
/**
* get the unique handle associated to a section
*
* @param int or string the id or nick name of the section
* @return the associated handle, or NULL if no record matches the input parameter
*/
public static function &get_handle($id)
{
global $context;
// sanity check
if (!$id) {
$output = NULL;
return $output;
}
// ensure proper unicode encoding
$id = (string) $id;
$id = utf8::encode($id);
// cache previous answers
static $cache;
if (!is_array($cache)) {
$cache = array();
}
// cache hit
if (isset($cache[$id])) {
return $cache[$id];
}
// search by id or nick name
$query = "SELECT handle FROM " . SQL::table_name('sections') . " AS sections" . " WHERE (sections.id = " . SQL::escape((int) $id) . ") OR (sections.nick_name LIKE '" . SQL::escape($id) . "')" . " ORDER BY edit_date DESC LIMIT 1";
// do the job
$output = SQL::query_scalar($query);
// save in cache
$cache[$id] = $output;
// return by reference
return $output;
}
/**
* count records for one anchor
*
* @param string the selected anchor (e.g., 'article:12')
* @param boolean TRUE if this can be optionnally avoided
* @param array list of ids to avoid, if any
* @return int the resulting count, or NULL on error
*/
public static function count_for_anchor($anchor, $optional = FALSE, $avoid = NULL)
{
global $context;
// sanity check
if (!$anchor) {
return NULL;
}
// request the database only in hi-fi mode
if ($optional && $context['skins_with_details'] != 'Y') {
return NULL;
}
// limit the scope of the request
$where = Files::get_sql_where();
// ids to avoid
if ($avoid) {
$where .= ' AND (files.id NOT IN (' . join(',', $avoid) . '))';
}
// select among available items
$query = "SELECT COUNT(*) as count" . " FROM " . SQL::table_name('files') . " AS files" . " WHERE files.anchor LIKE '" . SQL::escape($anchor) . "' AND " . $where;
return SQL::query_scalar($query);
}
/**
* count records for one anchor
*
* Only articles matching following criteria are returned:
* - article is visible (active='Y')
* - article is restricted (active='R'), but the surfer is an authenticated member,
* or YACS is allowed to show restricted teasers
* - article is protected (active='N'), but surfer is an associate, and we are not feeding someone
* - surfer is anonymous or the variant is 'boxes', and article has been officially published
* - logged surfers are restricted to their own articles, plus published articles
* - an expiry date has not been defined, or is not yet passed
*
* @param string the selected anchor (e.g., 'section:12')
* @param boolean FALSE to include sticky pages, TRUE otherwise
* @return int the resulting count, or NULL on error
*/
public static function count_for_anchor($anchor, $without_sticky = FALSE)
{
global $context;
// sanity check
if (!$anchor) {
return NULL;
}
// restrict the query to addressable content
$where = Articles::get_sql_where();
// avoid sticky articles
if ($without_sticky) {
$where .= " AND (articles.rank >= 10000)";
}
// anonymous surfers and subscribers will see only published articles
if (!Surfer::is_member()) {
$where .= " AND NOT ((articles.publish_date is NULL) OR (articles.publish_date <= '0000-00-00'))" . " AND (articles.publish_date < '" . $context['now'] . "')";
// logged surfers that are non-associates are restricted to their own articles, plus published articles
} elseif (!Surfer::is_empowered()) {
$where .= " AND ((articles.create_id=" . Surfer::get_id() . ") OR (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'] . "'))";
// several anchors
if (is_array($anchor)) {
$items = array();
foreach ($anchor as $token) {
$items[] = "articles.anchor LIKE '" . SQL::escape($token) . "'";
}
$where_anchor = join(' OR ', $items);
// or only one
} else {
$where_anchor = "articles.anchor LIKE '" . SQL::escape($anchor) . "'";
}
// select among available items
$query = "SELECT COUNT(*) as count" . " FROM " . SQL::table_name('articles') . " AS articles" . " WHERE (" . $where_anchor . ") AND (" . $where . ")";
return SQL::query_scalar($query);
}
/**
* count dates attached to some anchor
*
* @param string the selected anchor (e.g., 'article:12')
* @return int the resulting count, or NULL on error
*/
public static function count_for_anchor($anchor)
{
global $context;
// restrict the query to addressable content
$where = Articles::get_sql_where();
// put only published pages in boxes
if (isset($variant) && $variant == 'boxes') {
$where = " AND NOT ((articles.publish_date is NULL) OR (articles.publish_date <= '0000-00-00'))" . " AND (articles.publish_date < '" . $context['now'] . "')";
// provide published pages to anonymous surfers
} elseif (!Surfer::is_logged()) {
$where = " AND NOT ((articles.publish_date is NULL) OR (articles.publish_date <= '0000-00-00'))" . " AND (articles.publish_date < '" . $context['now'] . "')";
// logged surfers that are non-associates are restricted to their own articles, plus published articles
} elseif (!Surfer::is_empowered()) {
$where = " AND ((articles.create_id=" . Surfer::get_id() . ") OR (NOT ((articles.publish_date is NULL) OR (articles.publish_date <= '0000-00-00'))" . " AND (articles.publish_date < '" . $context['now'] . "')))";
}
// now
$match = gmstrftime('%Y-%m-%d %H:%M:%S');
// select among available items
$query = "SELECT COUNT(*) as count" . " FROM " . SQL::table_name('dates') . " as dates " . ", " . SQL::table_name('articles') . " AS articles" . " WHERE ((dates.anchor_type LIKE 'article') AND (dates.anchor_id = articles.id))" . "\tAND (articles.anchor = '" . SQL::escape($anchor) . "') AND (" . $where . ")";
return SQL::query_scalar($query);
}
* Only associates can use this script, except if no switch file is present.
* Invocations are also accepted if the user table is missing or empty.
*
* If an associate user profile is created, apply it to the current surfing session.
*
* @author Bernard Paques
* @author GnapZ
* @tester Christian Loubechine
* @reference
* @license http://www.gnu.org/copyleft/lesser.txt GNU Lesser General Public License
*/
// common libraries
include_once '../shared/global.php';
// force the creation of a user profile if the user table does not exists, or is empty
$query = "SELECT count(*) FROM " . SQL::table_name('users');
if (!SQL::query_scalar($query, FALSE, $context['users_connection'])) {
$permitted = TRUE;
} elseif (!(file_exists('../parameters/switch.on') || file_exists('../parameters/switch.off'))) {
$permitted = TRUE;
} elseif (Surfer::is_associate()) {
$permitted = TRUE;
} else {
$permitted = FALSE;
}
// load localized strings
i18n::bind('control');
// load the skin
load_skin('control');
// the path to this page
$context['path_bar'] = array('control/' => i18n::s('Control Panel'));
// default page title
// create table with the appropriate structure
$query = "CREATE TABLE `" . SQL::table_name('top_uploaders') . "` (\n" . "user_id TEXT DEFAULT '',\n" . "user_label TEXT DEFAULT '',\n" . "`" . implode("` TEXT DEFAULT '', `", $all_years) . "` TEXT DEFAULT '',\n" . "total TEXT DEFAULT '')";
SQL::query($query);
// subset of targeted users
$query = "SELECT \n" . "u.id AS user_id, \n" . "CONCAT(u.full_name, ' (', u.nick_name, ')') AS user_label, \n" . "count(a.id) AS total \n" . "FROM " . SQL::table_name('users') . " AS u, " . SQL::table_name('files') . " AS a WHERE (a.edit_id = u.id) \n" . "GROUP BY u.id \n" . "ORDER BY total DESC \n" . "LIMIT 0, 100";
if ($result = SQL::query($query)) {
// one record per user
$records = 0;
while ($item = SQL::fetch($result)) {
$fields = array();
foreach ($item as $name => $value) {
$fields[] = "`" . $name . "`='" . SQL::escape($value) . "'";
}
// breakdown for recent years
foreach ($all_years as $year) {
$query = "SELECT count(id) FROM " . SQL::table_name('files') . " WHERE (edit_date LIKE '" . $year . "%') AND (edit_id = " . $item['user_id'] . ")";
if ($value = SQL::query_scalar($query)) {
$fields[] = "`" . $year . "`='" . SQL::escape($value) . "'";
} else {
$fields[] = "`" . $year . "`='0'";
}
}
$query = "INSERT INTO " . SQL::table_name('top_uploaders') . ' SET ' . implode(', ', $fields);
$records += SQL::query($query);
}
echo sprintf('%d records have been processed', $records) . BR;
}
// all done
$time = round(get_micro_time() - $context['start_time'], 2);
exit(sprintf('Script terminated in %.2f seconds.', $time) . BR);
}
