function txGalleryBreakdown()
{
global $DB, $json, $C;
VerifyPrivileges(P_GALLERY, TRUE);
$out = array('status' => JSON_SUCCESS, 'breakdown' => array());
switch ($_REQUEST['group']) {
case 'category':
$categories =& $DB->FetchAll('SELECT * FROM `tx_categories` ORDER BY `name`');
$breakdown = array();
foreach ($categories as $category) {
$s = new SelectBuilder('COUNT(*) AS `amount`', 'tx_galleries');
if ($_REQUEST['status']) {
$s->AddWhere('status', ST_MATCHES, $_REQUEST['status']);
}
if ($_REQUEST['type']) {
$s->AddWhere('type', ST_MATCHES, $_REQUEST['type']);
}
$s->AddFulltextWhere('categories', $category['tag']);
$amount = $DB->Count($s->Generate(), $s->binds);
$breakdown[] = array('grouper' => htmlspecialchars($category['name']), 'amount' => number_format($amount, 0, $C['dec_point'], $C['thousands_sep']), 'sorter' => $amount);
}
usort($breakdown, 'txBreakdownCmp');
$out['breakdown'] =& $breakdown;
break;
case 'sponsor':
$s = new SelectBuilder("`name` AS `grouper`,COUNT(*) AS `amount`", 'tx_galleries');
$s->AddJoin('tx_galleries', 'tx_sponsors', 'LEFT', 'sponsor_id');
if ($_REQUEST['type']) {
$s->AddWhere('type', ST_MATCHES, $_REQUEST['type']);
}
if ($_REQUEST['status']) {
$s->AddWhere('status', ST_MATCHES, $_REQUEST['status']);
}
$s->AddGroup('tx_galleries.sponsor_id');
$s->AddOrder('amount', 'DESC');
$result = $DB->Query($s->Generate(), $s->binds);
while ($breakdown = $DB->NextRow($result)) {
$breakdown['amount'] = number_format($breakdown['amount'], 0, $C['dec_point'], $C['thousands_sep']);
$breakdown['grouper'] = $breakdown['grouper'] ? ucfirst(htmlspecialchars($breakdown['grouper'])) : '-';
$out['breakdown'][] = $breakdown;
}
$DB->Free($result);
break;
default:
$group_field = array('added' => 'DATE_FORMAT(date_added, \'%Y-%m-%d\')', 'displayed' => 'DATE_FORMAT(date_displayed, \'%Y-%m-%d\')', 'format' => 'format');
$s = new SelectBuilder("{$group_field[$_REQUEST['group']]} AS `grouper`,COUNT(*) AS `amount`", 'tx_galleries');
if ($_REQUEST['type']) {
$s->AddWhere('type', ST_MATCHES, $_REQUEST['type']);
}
if ($_REQUEST['status']) {
$s->AddWhere('status', ST_MATCHES, $_REQUEST['status']);
}
$result = $DB->Query($s->Generate() . " GROUP BY {$group_field[$_REQUEST['group']]} ORDER BY " . (in_array($_REQUEST['group'], array('added', 'displayed')) ? '`grouper`' : '`amount`') . " DESC", $s->binds);
while ($breakdown = $DB->NextRow($result)) {
$breakdown['amount'] = number_format($breakdown['amount'], 0, $C['dec_point'], $C['thousands_sep']);
$breakdown['grouper'] = $breakdown['grouper'] ? ucfirst(htmlspecialchars($breakdown['grouper'])) : '-';
$out['breakdown'][] = $breakdown;
}
$DB->Free($result);
break;
}
$type = $_REQUEST['type'] ? ucfirst(htmlspecialchars($_REQUEST['type'])) : 'Overall';
$status = $_REQUEST['status'] ? ucfirst(htmlspecialchars($_REQUEST['status'])) : '';
$by = ucfirst(htmlspecialchars($_REQUEST['group']));
$out['type'] = "{$type} {$status} Galleries By {$by}";
echo $json->encode($out);
}
function compile_accounts_tag($tag_args)
{
global $DB;
$defaults = array('category' => 'MIXED', 'ranks' => '1-25', 'storeranks' => 'false', 'storecatranks' => 'false', 'minhits' => 0, 'order' => 'unique_in_last_hour DESC');
$attrs = $this->parse_attributes($tag_args);
$attrs = array_merge($defaults, $attrs);
if (empty($attrs['var'])) {
return $this->syntax_error("accounts: missing 'var' attribute");
}
if (!preg_match('~^\\d+-\\d+$~', $attrs['ranks'])) {
return $this->syntax_error("accounts: the 'ranks' attribute must be in START-END format");
}
$attrs['var'] = $this->parse_vars($attrs['var']);
$attrs['storeranks'] = $this->to_bool($attrs['storeranks']);
$attrs['storecatranks'] = $this->to_bool($attrs['storecatranks']);
// Prepare RAND() values in order attribute
$attrs['order'] = preg_replace('~rand\\(\\)~i', 'RAND(%RAND%)', $attrs['order']);
// Pulling accounts from database using user-specified SELECT statements
if (isset($attrs['select'])) {
// TODO: User specified select statement
} else {
$s = new SelectBuilder('*,%SORTER% AS `sorter`,`tlx_accounts`.`username` AS `username`', 'tlx_accounts');
$attrs['category'] = FormatCommaSeparated($attrs['category']);
if ($this->flags['category_id']) {
$s->AddWhere('category_id', ST_MATCHES, $this->flags['category_id'], TRUE);
} else {
if (strtoupper($attrs['category']) != 'MIXED') {
$category_not_in = array();
$category_in = array();
if (!isset($GLOBALS['CATEGORY_CACHE'])) {
$GLOBALS['CATEGORY_CACHE'] =& $DB->FetchAll('SELECT * FROM `tlx_categories`', null, 'name');
}
foreach (explode(',', $attrs['category']) as $category) {
switch ($category) {
case 'MIXED':
case 'mixed':
case 'Mixed':
break;
default:
$minus = FALSE;
if (preg_match('~^-(.*)~i', $category, $matches)) {
$minus = TRUE;
$category = $matches[1];
}
if ($GLOBALS['CATEGORY_CACHE'][$category]) {
if ($minus) {
$category_not_in[] = $GLOBALS['CATEGORY_CACHE'][$category]['category_id'];
} else {
$category_in[] = $GLOBALS['CATEGORY_CACHE'][$category]['category_id'];
}
}
}
}
$s->AddWhere('category_id', ST_IN, join(',', $category_in), TRUE);
$s->AddWhere('category_id', ST_NOT_IN, join(',', $category_not_in), TRUE);
}
}
}
// Handle the order attribute
$order = trim($attrs['order']);
$direction = null;
$sorter = null;
if (strpos($order, ' ')) {
list($order, $direction) = explode(' ', $order);
}
switch (strtolower($order)) {
case 'ratings':
case 'date_added':
case 'date_activated':
case 'inactive':
$sorter = "`{$order}`";
break;
case 'average_rating':
$sorter = '`ratings_total`/`ratings`';
break;
default:
if (preg_match('~^(.*?)_(last|this|yesterday)_?(\\d+)?_?(.*)?$~', $order, $matches)) {
list($full, $field, $type, $amount, $period) = $matches;
$join = 'tlx_account_daily_stats';
if ($type == 'yesterday') {
$type = 'last';
$amount = 1;
$period = 'day';
}
if (empty($amount)) {
$amount = 1;
}
if (stristr($period, 'hour')) {
$s->AddJoin('tlx_accounts', 'tlx_account_hourly_stats', 'LEFT', 'username');
if ($field == 'productivity') {
$sorter = $amount == 24 ? "`clicks_total`/`unique_in_total`" : "(\" . SorterLastHours('clicks_%%', {$amount}) . \")/(\" . SorterLastHours('unique_in_%%', {$amount}) . \")";
} else {
$sorter = $amount == 24 ? "`{$field}" . "_total`" : "\" . SorterLastHours('{$field}" . "_%%', {$amount}) . \"";
}
if (!empty($attrs['minhits']) && is_numeric($attrs['minhits'])) {
$s->AddWhereString("({$sorter}) >= {$attrs['minhits']}");
}
} else {
if (stristr($period, 'day')) {
$s->AddJoin('tlx_accounts', 'tlx_account_daily_stats', 'LEFT', 'username');
$s->AddGroup('tlx_accounts.username');
if ($field == 'productivity') {
$sorter = $amount >= 365 ? "SUM(`clicks`)/SUM(`unique_in`)" : "SUM(IF(`date_stats` >= DATE_ADD('%TODAY%', INTERVAL -{$amount} DAY), `clicks`, 0))/SUM(IF(`date_stats` >= DATE_ADD('%TODAY%', INTERVAL -{$amount} DAY), `unique_in`, 0))";
} else {
$sorter = $amount >= 365 ? "SUM(`{$field}`)" : "SUM(IF(`date_stats` >= DATE_ADD('%TODAY%', INTERVAL -{$amount} DAY), `{$field}`, 0))";
}
if (!empty($attrs['minhits']) && is_numeric($attrs['minhits'])) {
$s->AddHavingString("`sorter` >= {$attrs['minhits']}");
}
} else {
$sorter = '`unique_in_last_hour`';
$direction = 'DESC';
}
}
} else {
$sorter = '`unique_in_last_hour`';
$direction = 'DESC';
}
break;
}
$s->AddWhere('disabled', ST_MATCHES, 0);
$s->AddWhere('status', ST_MATCHES, STATUS_ACTIVE);
$s->AddOrder('sorter', $direction);
$s->AddOrder('tlx_accounts.username');
// Set the range of accounts to select
list($start, $end) = explode('-', $attrs['ranks']);
$s->SetLimit($start - 1 . ',' . ($end - $start + 1));
// Generate the SQL query to pull accounts from the database
$query = $DB->Prepare($s->Generate(), $s->binds);
// Query replacements
$replacements = array('%SORTER%' => $sorter, '%TODAY%' => '" . MYSQL_CURDATE . "');
foreach ($replacements as $find => $replace) {
$query = str_replace($find, $replace, $query);
}
if (isset($attrs['stats'])) {
$attrs['stats'] = FormatCommaSeparated($attrs['stats']);
}
return S_PHP . " {$attrs['var']} =& LoadAccounts(\"{$query}\", '{$attrs['ranks']}', \$this->vars['fillranks'], " . ($attrs['storeranks'] === TRUE ? 'TRUE' : 'FALSE') . ", " . ($attrs['storecatranks'] === TRUE ? 'TRUE' : 'FALSE') . ", '{$attrs['stats']}'); " . E_PHP;
}
/**
* Quick user search for the link submission form
*/
function lxQuickUserSearch()
{
global $DB, $json;
$out = array('status' => JSON_SUCCESS, 'results' => array());
$select = new SelectBuilder('*', 'lx_users');
$select->AddMultiWhere(array('username', 'email'), array(ST_CONTAINS, ST_CONTAINS), array($_REQUEST['term'], $_REQUEST['term']), TRUE);
$select->AddOrder('username');
$result = $DB->Query($select->Generate(), $select->binds);
while ($account = $DB->NextRow($result)) {
ArrayHSC($account);
$out['results'][] = $account;
}
$DB->Free($result);
echo $json->encode($out);
}
function compile_pages_tag($tag_args)
{
global $DB;
$attrs = $this->parse_attributes($tag_args);
if (empty($attrs['var'])) {
return $this->syntax_error("pages: missing 'var' attribute");
}
$s = new SelectBuilder('*', 'tx_pages');
$s->AddJoin('tx_pages', 'tx_categories', 'LEFT', 'category_id');
if (isset($attrs['tags'])) {
$s->AddFulltextWhere('tags', $attrs['tags']);
}
if (isset($attrs['category'])) {
if (is_numeric($attrs['category'])) {
if ($attrs['category'] == 0) {
$s->AddWhere('tx_pages.category_id', ST_NULL, null);
} else {
$s->AddWhere('tx_pages.category_id', ST_MATCHES, $attrs['category']);
}
} else {
if ($attrs['category'][0] == '$') {
$attrs['category'] = $this->parse_vars($attrs['category']);
$s->AddWhere('tx_pages.category_id', ST_MATCHES, '%CATEGORY_ID%');
} else {
$category = $DB->Row('SELECT * FROM `tx_categories` WHERE `name`=?', array($attrs['category']));
if (!$category) {
return $this->syntax_error("pages: 'category' attribute has an invalid category name specified");
}
$s->AddWhere('tx_pages.category_id', ST_MATCHES, $category['category_id']);
}
}
}
if (isset($attrs['urlcontains'])) {
$s->AddWhere('page_url', ST_CONTAINS, $attrs['urlcontains']);
}
if (isset($attrs['amount'])) {
$s->SetLimit($attrs['amount']);
}
$s->AddOrder('build_order', 'ASC');
$query = $DB->Prepare($s->Generate(), $s->binds);
$query = str_replace("='%CATEGORY_ID%'", "\".({$attrs['category']} ? \"='\".{$attrs['category']}.\"'\" : ' IS NULL').\"", $query);
$attrs['var'] = $this->parse_vars($attrs['var']);
return S_PHP . " {$attrs['var']} =& \$GLOBALS['DB']->FetchAll(\"{$query}\"); " . E_PHP;
}
