public static function search(TableCtl $controller, $term, $filter = false)
{
$object = call_user_func(array(get_class($controller), 'getObject'));
if (!$object) {
return false;
}
$terms = preg_split('/[ ,]/', $term);
if (!count($terms)) {
return false;
}
//Check for results containing the word
$search = array();
foreach ($terms as $oneTerm) {
$search[] = '`word` LIKE CONCAT("%", ?, "%")';
}
//Check for results with the exact word
$search[] = '`word` IN (' . implode(', ', array_fill(0, count($terms), '?')) . ')';
$search = '(' . implode(') OR (', $search) . ')';
$params = array_merge(array($object->getSource()), $terms, $terms);
$query = new SelectQuery(get_called_class());
$query->field('DISTINCT `' . $object->getMeta('table') . '`.*')->leftJoin(get_class($controller), '`' . $object->getMeta('table') . '`.`' . $object->getMeta('id_field') . '` = `table_id`')->filter('`table` = ?')->filter($search)->order('`count` DESC, `sequence`');
if ($filter) {
if (is_array($filter)) {
foreach ($filter as $one_fil) {
$query->filter($one_fil);
}
} else {
$query->filter($filter);
}
}
$result = $query->fetchAll($params);
return $result;
}
public static function remember($user)
{
//We need a user, but we won't remember the admin user.
//if ($user && $user->id > 0 && !in_array('superadmin', $user->roles)) {
if ($user && $user->id > 0) {
$random = get_random('number');
$persist = new PersistUserObj();
$data = array('user_id' => $user->id, 'random' => $random);
if ($persist->create($data)) {
$query = new SelectQuery('PersistUser');
$query->field('MD5(CONCAT(`id`, `user_id`, `random`))')->filter('`id`= :id');
$hash = $query->fetchColumn(array(':id' => $persist->array['id']));
if (setcookie('remembered', $hash, time() + 60 * 60 * 24 * 14, WEB_SUB_FOLDER)) {
return true;
} else {
Backend::addError('Could not set cookie to remember login');
$query = new DeleteQuery('PersistUser');
$query->filter('`id` = :id')->limit(1);
$query->execute(array(':id' => $persist->array['id']));
}
} else {
Backend::addError('Could not remember login');
}
} else {
Backend::addError('Invalid user to remember');
}
return false;
}
public static function getComments($table = false, $table_id = false, $limit = false)
{
$query = new SelectQuery('Comment');
$query->field(array('`comments`.*, `backend_users`.`username`, `backend_users`.`email`'))->leftJoin('BackendUser', '`comments`.`user_id` = `backend_users`.`id`')->filter('`comments`.`active` = 1')->order('IF(`comments`.`in_reply_to` = 0, `comments`.`id`, `comments`.`in_reply_to`) DESC');
$params = array();
if ($table) {
$query->filter('`comments`.`foreign_table` = :table');
$params[':table'] = $table;
}
if ($table_id) {
$query->filter('`comments`.`foreign_id` = :table_id');
$params[':table_id'] = $table_id;
}
if ($limit) {
$query->limit($limit);
}
return $query->fetchAll($params);
}
public static function get($id, array $options = array())
{
$tag = Tag::retrieve($id, 'dbobject');
if (!$tag || !$tag->array) {
return false;
}
$links = self::getObject($tag->array['foreign_table']);
list($query, $params) = $links->getSelectSQL();
if (!$query instanceof SelectQuery) {
return false;
}
$query_links = new SelectQuery('TagLink');
$query_links->field('`foreign_id`')->filter('`tag_id` = :tag_id');
if (array_key_exists('active', $links->getMeta('fields'))) {
$query_links->filter('`active` = 1');
}
$order = $query_links->getOrder();
if (empty($order) && array_key_exists('added', $links->getMeta('fields'))) {
$query_links->order('`added` DESC');
}
$start = array_key_exists('start', $options) ? $options['start'] : 0;
$count = array_key_exists('count', $options) ? $options['count'] : Value::get('list_length', 5);
$query->field(':tag_id AS `tag_id`')->filter('`' . $links->getMeta('id_field') . '` IN (' . $query_links . ')')->limit("{$start}, {$count}");
$params = array(':tag_id' => $tag->getMeta('id'));
$links->load(array('mode' => 'list', 'query' => $query, 'parameters' => $params));
$tag->array['list'] = $links->list;
$tag->array['list_count'] = $links->list_count;
return $tag;
}
public static function userVisits($user_id)
{
$query = new SelectQuery('BackendRequest');
$query->field('COUNT(*) AS `visits`')->filter('`user_id` = :user_id')->group('`user_id`');
return $query->fetchColumn(array(':user_id' => $user_id));
}
public static function hook_init()
{
//Check for any system locks
$query = new SelectQuery('BackendLock');
$query->field('`name`')->filter('`type` = :type')->filter('`locked` = 1')->filter('`expire` > NOW()');
while ($lock_name = $query->fetchColumn(array(':type' => self::LOCK_SYSTEM))) {
$lock = BackendLock::retrieve($lock_name, 'dbobject');
if (!$lock->check()) {
//A Lock isn't available, so the request must be aborted.
Controller::whoops('Service Unavailable', array('message' => 'System Offline until ' . $lock->array['expire'] . '. Locked under ' . $lock->array['name'], 'code_hint' => 503));
header('X-Backend-Lock: ' . $lock->array['name']);
header('X-Backend-Lock-Expire: ' . $lock->array['expire']);
}
}
}
public static function userStats()
{
$msg = array();
$query = new SelectQuery('BackendUser');
$query->field('COUNT(*) AS `Total`, SUM(IF(TO_DAYS(NOW()) - TO_DAYS(`added`) < 7, 1, 0)) AS `New`')->filter('`active` = 1')->filter('`confirmed` = 1');
if ($stats = $query->fetchAssoc()) {
$msg[] = 'There are a total of ' . $stats['Total'] . ' **active** users,
of which ' . $stats['New'] . ' signed up in the last 7 days';
}
$query = new SelectQuery('BackendUser');
$query->field('COUNT(*) AS `Total`, SUM(IF(TO_DAYS(NOW()) - TO_DAYS(`added`) < 7, 1, 0)) AS `New`')->filter('`active` = 1')->filter('`confirmed` = 1');
if ($stats = $query->fetchAssoc()) {
$msg[] = 'There are a total of ' . $stats['Total'] . ' **unconfirmed** users,
of which ' . $stats['New'] . ' signed up in the last 7 days';
}
$msg = implode(PHP_EOL . PHP_EOL, $msg);
send_email(ConfigValue::get('author.Email', ConfigValue::get('application.Email', 'info@' . SITE_DOMAIN)), 'User stats for ' . Backend::get('Title'), $msg);
return true;
}
public function getSelectSQL($options = array())
{
//Check the DB Connection
$this->error_msg = false;
if (!$this->checkConnection()) {
if (class_exists('BackendError', false)) {
BackendError::add(get_class($this) . ': DB Connection Error', 'getSelectSQL');
}
$this->error_msg = 'DB Connection Error';
return false;
}
$mode = array_key_exists('mode', $options) ? $options['mode'] : 'list';
$query = new SelectQuery($this, array('connection' => $this->db));
//Fields
$fields = array_key_exists('fields', $options) ? $options['fields'] : array();
if (empty($fields)) {
$query->field("`{$this->meta['table']}`.*");
} else {
$query->field($fields);
}
//Joins
$joins = array_key_exists('joins', $options) ? $options['joins'] : array();
if (count($joins)) {
foreach ($joins as $join) {
if (is_array($join)) {
$query->joinArray($join);
}
}
}
$q_params = array();
if (!empty($options['conditions'])) {
$query->filter($options['conditions']);
}
//Mode specific
$limit = false;
switch ($mode) {
case 'object':
case 'array':
case 'full_object':
if (!empty($this->meta['id'])) {
$query->filter("`{$this->meta['table']}`.`{$this->meta['id_field']}` = :{$this->meta['table']}_id");
$q_params[":{$this->meta['table']}_id"] = $this->meta['id'];
} else {
$query->limit(empty($limit) ? 1 : $limit);
}
break;
case 'list':
if (array_key_exists('limit', $options) && $options['limit'] != 'all') {
$query->limit($options['limit']);
}
break;
}
//Parameters
if (array_key_exists('parameters', $options)) {
if (is_array($options['parameters'])) {
$q_params = array_merge($q_params, $options['parameters']);
} else {
$q_params[] = $options['parameters'];
}
} else {
if (!empty($this->meta['parameters'])) {
if (is_array($this->meta['parameters'])) {
$q_params = array_merge($q_params, $this->meta['parameters']);
} else {
$q_params[] = $parameters;
}
}
}
//Filters
if (array_key_exists('filters', $options)) {
$query->filter($options['filters']);
} else {
if (!empty($this->meta['filters'])) {
$query->filter($this->meta['filters']);
}
}
//Order
if (array_key_exists('order', $options)) {
$query->order($options['order']);
} else {
if (!empty($this->meta['order'])) {
$query->order($this->meta['order']);
}
}
//Group
if (array_key_exists('group', $options)) {
$query->group($options['group']);
} else {
if (!empty($this->meta['group'])) {
$query->group($this->meta['group']);
}
}
//Check Ownership
if (array_key_exists('owner_id', $this->meta['fields'])) {
if ($user = BackendUser::check()) {
if (!in_array('superadmin', $user->roles)) {
$query->filter("`{$this->meta['table']}`.`owner_id` = :owner_id");
$q_params[':owner_id'] = $user->id;
}
}
}
return array($query, $q_params);
}