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 check() { if (!empty($_COOKIE['remembered'])) { $query = new SelectQuery('PersistUser'); $persist = $query->filter('MD5(CONCAT(`id`, `user_id`, `random`)) = :hash')->fetchAssoc(array(':hash' => $_COOKIE['remembered'])); if ($persist) { //Get User $User = self::getObject('BackendUser'); if (!$User instanceof DBObject) { return false; } $query = BackendUser::getQuery(); $query->filter('`backend_users`.`id` = :id'); $params = array(':id' => $persist['user_id']); $User->read(array('query' => $query, 'parameters' => $params, 'mode' => 'object')); if ($User->object) { $_SESSION['BackendUser'] = $User->object; //Remove, and reremember if (self::remember($User->object)) { $query = new DeleteQuery('PersistUser'); $query->filter('`id` = :id')->limit(1); $query->execute(array(':id' => $persist['id'])); } else { Backend::addError('Could not reremember'); } return $User->object; } else { //Backend::addError('Invalid remembered user'); } } } return false; }
public function action_filter($pageId = 1) { $query = new SelectQuery('BackendRequest'); $query->setFields(array('user_id', 'ip', 'user_agent', 'mode', 'request', 'query', 'COUNT(id) AS `occured`', 'MAX(`added`) AS `last_occured`')); $query->setGroup(array('user_id', 'ip', 'user_agent', 'mode', 'request', 'query')); $params = $queryFilter = array(); $parameters = Controller::getVar('params'); $sort = Controller::getVar('sort'); if (!empty($parameters['userId'])) { $queryFilter[] = 'user_id = :userId'; $params[':userId'] = $parameters['userId']; } if (!empty($parameters['query'])) { $queryFilter[] = "query LIKE('%{$parameters['query']}%')"; } if (!empty($parameters['ip'])) { $queryFilter[] = "ip LIKE('%{$parameters['ip']}%')"; } if (!empty($parameters['user_agent'])) { $queryFilter[] = "user_agent LIKE('%{$parameters['user_agent']}%')"; } $query->filter($queryFilter); $count = 10; if (!empty($sort['field'])) { $query->setOrder(array($sort['field'] . ' ' . $sort['order'])); } if ($pageId == 1) { $start = 0; } elseif ($pageId == 0) { $start = false; $count = false; } else { $start = floor(($pageId - 1) * $count); } $pager = array(); if ($start === 'all') { $limit = 'all'; } else { if ($start || $count) { $limit = "{$start}, {$count}"; } else { $limit = false; } } $query->limit($limit); $items = $query->fetchAll($params); $totalItems = $query->getCount($params); $pager = ''; if ($start || $count) { $pager = array('currentPage' => $pageId, 'itemCount' => count($items), 'itemTotal' => $totalItems, 'totalPages' => round(($totalItems - 1) / $count, 0)); } $retArray['pager'] = $pager; $retArray['data'] = $items; $retArray['params'] = $parameters; $retArray['sort'] = $sort; return $retArray; }
public function action_create() { if (is_post()) { $parameters = get_previous_parameters(); $object = new CommentObj(); $object = $object->fromRequest(); $object['foreign_id'] = empty($object['foreign_id']) ? reset($parameters) : $object['foreign_id']; $object['foreign_table'] = empty($object['foreign_table']) ? table_name(get_previous_area()) : $object['foreign_table']; //If we don't have a logged in user, create a dummy account if (!BackendUser::check()) { $query = new SelectQuery('BackendUser'); $query->filter('`email` = :email'); if ($old_user = Controller::getVar('user')) { $existing_user = $query->fetchAssoc(array(':email' => $old_user['email'])); } switch (true) { case $existing_user && $existing_user['confirmed'] && $existing_user['active']: //Attribute quote to user? Seems risque, actually, if I know a user's email address, I can just attribute to him. Auth first Backend::addError('Comment not added. Please login first'); return false; break; case $existing_user && !$existing_user['confirmed'] && $existing_user['active']: //Unregistered user commented before $object['user_id'] = $existing_user['id']; break; default: case !$existing_user: $user_data = array('name' => $old_user['name'], 'surname' => '', 'email' => $old_user['email'], 'website' => $old_user['website'], 'username' => $old_user['email'], 'password' => get_random(), 'confirmed' => 0, 'active' => 1); $user = self::getObject('BackendUser'); if ($user->create($user_data)) { $object['user_id'] = $user->array['id']; $url = SITE_LINK . '/?q=backend_user/confirm/' . $user->array['salt']; $app_name = ConfigValue::get('Title'); $message = <<<END Hi {$user->array['name']}! Thank you for your comment on {$app_name}. An account has automatically been created for you. To activate it, please click on the following link: {$url} Please note that you don't need to do this for your comments to show, but this account will be deleted if it isn't confirmed in a weeks time. Regards END; send_email($user->array['email'], 'Thank you for your comment.', $message); } else { Backend::addError('Could not create user to add Comment'); return false; } break; } } $object = array_filter($object, create_function('$var', 'return !is_null($var);')); Controller::setVar('obj', $object); } return parent::action_create(); }
public static function get($hook, $type = 'pre') { if (!BACKEND_WITH_DATABASE) { return false; } $params = array(':type' => $type, ':hook' => $hook); $query = new SelectQuery('Hook'); $query->leftJoin('Component', array('`hooks`.`class` = `components`.`name`'))->filter('`hooks`.`hook` = :hook')->filter('`hooks`.`type` = :type')->filter('`hooks`.`active` = 1')->filter('`components`.`active` = 1'); if (Controller::$area) { $query->filter('`global` = 1 OR `class` = :area'); $params[':area'] = Controller::$area; } if (Controller::$view && Controller::$view->mode) { $query->filter('`mode` IN (:mode, \'*\')'); $params[':mode'] = Controller::$view->mode; } $query->order('`sequence`'); return $query->fetchAll($params); }
public function action_display($id) { $query = new SelectQuery('ContentRevision'); $query->filter('`content_id` = :id')->order('`added` DESC'); $revisions = $query->fetchAll(array(':id' => $id)); $content = new ContentObj($id); if ($content->object) { $content->object->revisions = $revisions; } else { $content = false; } return $content; }
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; }
private static function permissionHolders($action = '*', $subject = '*', $subject_id = 0) { $result = false; $query = new SelectQuery('Permission'); $params = array(); if ($action != '*') { $query->filter("(`action` = :action OR `action` = '*')"); $params[':action'] = $action; } if ($subject != '*') { $query->filter("(`subject` = :subject OR `subject` = '*')"); $params[':subject'] = $subject; } if ($subject_id != '0') { $query->filter("(`subject_id` = :subject_id OR `subject_id` = 0)"); $params[':subject_id'] = $subject_id; } $result = $query->fetchAll($params); return $result; }
public static function getSitemap() { $query = new SelectQuery('Content'); $query->filter('`active` = 1'); $list = $query->fetchAll(); return array('list' => $list, 'options' => array()); }
/** * Return all users within a specific role */ public static function withRole($roles) { if (!is_array($roles)) { $roles = array($roles); } $roleObj = new RoleObj(); $query = new SelectQuery('Role'); $query->filter('`name` IN (' . implode(', ', array_pad(array(), count($roles), '?')) . ')'); $roleObj->read(array('query' => $query, 'parameters' => $roles)); if (!$roleObj->list) { return false; } $roleIds = array_flatten($roleObj->list, null, 'id'); $query = self::getQuery(); $query->distinct()->field('`' . self::getTable() . '`.*')->leftJoin('Assignment', array('`access_type` = "users"', '`access_id` = `' . self::getTable() . '`.`id`'))->filter('`role_id` IN (' . implode(', ', array_pad(array(), count($roleIds), '?')) . ')'); return $query->fetchAll($roleIds); }
public function get_permissions($component = false) { $toret = new stdClass(); //Base Permissions $parameters = array(); $query = new SelectQuery('Permission'); $query->distinct()->field(array('action', 'subject'))->filter('`active` = 1')->filter('`subject_id` = 0')->group('`subject`, `action` WITH ROLLUP'); if ($component) { $query->filter('`subject` = :component'); $parameters[':component'] = class_for_url($component); } $toret->base_perms = $query->fetchAll($parameters); //Roles $query = new SelectQuery('Role'); $query->filter('`active` = 1'); $toret->roles = $query->fetchAll(); //Activated Permissions $parameters = array(); $query = new SelectQuery('Permission', array('fields' => "CONCAT(`subject`, '::', `action`), GROUP_CONCAT(DISTINCT `role` ORDER BY `role`) AS `roles`")); $query->filter('`active` = 1')->filter('`subject_id` = 0')->filter("`role` != 'nobody'")->group('`subject`, `action`'); if ($component) { $query->filter('`subject` = :component'); $parameters[':component'] = class_for_url($component); } $permissions = $query->fetchAll($parameters, array('with_key' => 1)); $toret->permissions = array(); foreach ($permissions as $key => $value) { $toret->permissions[$key] = explode(',', current($value)); } return $toret; }
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); }