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