예제 #1
0
파일: posts.php 프로젝트: habari/system
 /**
  * Returns a post or posts based on supplied parameters.
  * @todo <b>THIS CLASS SHOULD CACHE QUERY RESULTS!</b>
  *
  * @param array $paramarray An associative array of parameters, or a querystring.
  * The following keys are supported:
  * - id => a post id or array of post ids
  * - not:id => a post id or array of post ids to exclude
  * - slug => a post slug or array of post slugs
  * - not:slug => a post slug or array of post slugs to exclude
  * - user_id => an author id or array of author ids
  * - content_type => a post content type or array post content types
  * - not:content_type => a post content type or array post content types to exclude
  * - status => a post status, an array of post statuses, or 'any' for all statuses
  * - year => a year of post publication
  * - month => a month of post publication, ignored if year is not specified
  * - day => a day of post publication, ignored if month and year are not specified
  * - before => a timestamp to compare post publication dates
  * - after => a timestamp to compare post publication dates
  * - month_cts => return the number of posts published in each month
  * - criteria => a literal search string to match post content or title
  * - title => an exact case-insensitive match to a post title
  * - title_search => a search string that acts only on the post title
  * - has:info => a post info key or array of post info keys, which should be present
  * - all:info => a post info key and value pair or array of post info key and value pairs, which should all be present and match
  * - not:all:info => a post info key and value pair or array of post info key and value pairs, to exclude if all are present and match
  * - any:info => a post info key and value pair or array of post info key and value pairs, any of which can match
  * - not:any:info => a post info key and value pair or array of post info key and value pairs, to exclude if any are present and match
  * - vocabulary => an array describing parameters related to vocabularies attached to posts. This can be one of two forms:
  *   - object-based, in which an array of Term objects are passed
  *     - any => posts associated with any of the terms are returned
  *     - all => posts associated with all of the terms are returned
  *     - not => posts associated with none of the terms are returned
  *   - property-based, in which an array of vocabulary names and associated fields are passed
  *     - vocabulary_name:term => a vocabulary name and term slug pair or array of vocabulary name and term slug pairs, any of which can be associated with the posts
  *     - vocabulary_name:term_display => a vocabulary name and term display pair or array of vocabulary name and term display pairs, any of which can be associated with the posts
  *     - vocabulary_name:not:term => a vocabulary name and term slug pair or array of vocabulary name and term slug pairs, none of which can be associated with the posts
  *     - vocabulary_name:not:term_display => a vocabulary name and term display pair or array of vocabulary name and term display pairs, none of which can be associated with the posts
  *     - vocabulary_name:all:term => a vocabulary name and term slug pair or array of vocabulary name and term slug pairs, all of which must be associated with the posts
  *     - vocabulary_name:all:term_display => a vocabulary name and term display pair or array of vocabulary name and term display pairs, all of which must be associated with the posts
  * - on_query_built => a closure that accepts a Query as a parameter, allowing a plugin to alter the Query for this request directly
  * - limit => the maximum number of posts to return, implicitly set for many queries
  * - nolimit => do not implicitly set limit
  * - offset => amount by which to offset returned posts, used in conjunction with limit
  * - page => the 'page' of posts to return when paging, sets the appropriate offset
  * - count => return the number of posts that would be returned by this request
  * - orderby => how to order the returned posts
  * - groupby => columns by which to group the returned posts, for aggregate functions
  * - having => for selecting posts based on an aggregate function
  * - where => manipulate the generated WHERE clause. Currently broken, see https://trac.habariproject.org/habari/ticket/1383
  * - add_select => an array of clauses to be added to the generated SELECT clause.
  * - fetch_fn => the function used to fetch data, one of 'get_results', 'get_row', 'get_value', 'get_query'
  *
  * Further description of parameters, including usage examples, can be found at
  * http://wiki.habariproject.org/en/Dev:Retrieving_Posts
  *
  * @return Posts|Post|string An array of Post objects, or a single post object, depending on request
  */
 public static function get($paramarray = array())
 {
     static $presets;
     $select_distinct = array();
     // If $paramarray is a string, use it as a Preset
     if (is_string($paramarray)) {
         $paramarray = array('preset' => $paramarray);
     }
     // If $paramarray is a querystring, convert it to an array
     $paramarray = Utils::get_params($paramarray);
     if ($paramarray instanceof \ArrayIterator) {
         $paramarray = $paramarray->getArrayCopy();
     }
     // If a preset is defined, get the named array and merge it with the provided parameters,
     // allowing the additional $paramarray settings to override the preset
     if (isset($paramarray['preset'])) {
         if (!isset($presets)) {
             $presets = Plugins::filter('posts_get_all_presets', $presets, $paramarray['preset']);
         }
         $paramarray = Posts::merge_presets($paramarray, $presets);
     }
     // let plugins alter the param array before we use it. could be useful for modifying search results, etc.
     $paramarray = Plugins::filter('posts_get_paramarray', $paramarray);
     $join_params = array();
     $params = array();
     $fns = array('get_results', 'get_row', 'get_value', 'get_query');
     $select_ary = array();
     // Default fields to select, everything by default
     $default_fields = Plugins::filter('post_default_fields', Post::default_fields(), $paramarray);
     if (isset($paramarray['default_fields'])) {
         $param_defaults = Utils::single_array($paramarray['default_fields']);
         $default_fields = array_merge($default_fields, $param_defaults);
     }
     foreach ($default_fields as $field => $value) {
         if (preg_match('/(?:(?P<table>[\\w\\{\\}]+)\\.)?(?P<field>\\w+)(?:(?:\\s+as\\s+)(?P<alias>\\w+))?/i', $field, $fielddata)) {
             if (empty($fielddata['table'])) {
                 $fielddata['table'] = '{posts}';
             }
             if (empty($fielddata['alias'])) {
                 $fielddata['alias'] = $fielddata['field'];
             }
         }
         $select_ary[$fielddata['alias']] = "{$fielddata['table']}.{$fielddata['field']} AS {$fielddata['alias']}";
         $select_distinct[$fielddata['alias']] = "{$fielddata['table']}.{$fielddata['field']}";
     }
     // Define the WHERE sets to process and OR in the final SQL statement
     if (isset($paramarray['where']) && is_array($paramarray['where'])) {
         $wheresets = $paramarray['where'];
     } else {
         $wheresets = array(array());
     }
     /* Start building the WHERE clauses */
     $query = Query::create('{posts}');
     $query->select($select_ary);
     // If the request has a textual WHERE clause, add it to the query then continue the processing of the $wheresets
     if (isset($paramarray['where']) && is_string($paramarray['where'])) {
         $query->where()->add($paramarray['where']);
     }
     foreach ($wheresets as $paramset) {
         $where = new QueryWhere();
         $paramset = array_merge((array) $paramarray, (array) $paramset);
         if (isset($paramset['id'])) {
             $where->in('{posts}.id', $paramset['id'], 'posts_id', 'intval');
         }
         if (isset($paramset['not:id'])) {
             $where->in('{posts}.id', $paramset['not:id'], 'posts_not_id', 'intval', false);
         }
         if (isset($paramset['status']) && !self::empty_param($paramset['status'])) {
             $where->in('{posts}.status', $paramset['status'], 'posts_status', function ($a) {
                 return Post::status($a);
             });
         }
         if (isset($paramset['not:status']) && !self::empty_param($paramset['not:status'])) {
             $where->in('{posts}.status', $paramset['not:status'], 'posts_not_status', function ($a) {
                 return Post::status($a);
             }, null, false);
         }
         if (isset($paramset['content_type']) && !self::empty_param($paramset['content_type'])) {
             $where->in('{posts}.content_type', $paramset['content_type'], 'posts_content_type', function ($a) {
                 return Post::type($a);
             });
         }
         if (isset($paramset['not:content_type'])) {
             $where->in('{posts}.content_type', $paramset['not:content_type'], 'posts_not_content_type', function ($a) {
                 return Post::type($a);
             }, false);
         }
         if (isset($paramset['slug'])) {
             $where->in('{posts}.slug', $paramset['slug'], 'posts_slug');
         }
         if (isset($paramset['not:slug'])) {
             $where->in('{posts}.slug', $paramset['not:slug'], 'posts_not_slug', null, false);
         }
         if (isset($paramset['user_id']) && 0 !== $paramset['user_id']) {
             $where->in('{posts}.user_id', $paramset['user_id'], 'posts_user_id', 'intval');
         }
         if (isset($paramset['not:user_id']) && 0 !== $paramset['not:user_id']) {
             $where->in('{posts}.user_id', $paramset['not:user_id'], 'posts_not_user_id', 'intval', false);
         }
         if (isset($paramset['vocabulary'])) {
             if (is_string($paramset['vocabulary'])) {
                 $paramset['vocabulary'] = Utils::get_params($paramset['vocabulary']);
             }
             // parse out the different formats we accept arguments in into a single mutli-dimensional array of goodness
             $paramset['vocabulary'] = self::vocabulary_params($paramset['vocabulary']);
             $object_id = Vocabulary::object_type_id('post');
             if (isset($paramset['vocabulary']['all'])) {
                 $all = $paramset['vocabulary']['all'];
                 foreach ($all as $vocab => $value) {
                     foreach ($value as $field => $terms) {
                         // we only support these fields to search by
                         if (!in_array($field, array('id', 'term', 'term_display'))) {
                             continue;
                         }
                         $join_group = Query::new_param_name('join');
                         $query->join('JOIN {object_terms} ' . $join_group . '_ot ON {posts}.id = ' . $join_group . '_ot.object_id', array(), 'term2post_posts_' . $join_group);
                         $query->join('JOIN {terms} ' . $join_group . '_t ON ' . $join_group . '_ot.term_id = ' . $join_group . '_t.id', array(), 'terms_term2post_' . $join_group);
                         $query->join('JOIN {vocabularies} ' . $join_group . '_v ON ' . $join_group . '_t.vocabulary_id = ' . $join_group . '_v.id', array(), 'terms_vocabulary_' . $join_group);
                         $where->in($join_group . '_v.name', $vocab);
                         $where->in($join_group . "_t.{$field}", $terms);
                         $where->in($join_group . '_ot.object_type_id', $object_id);
                     }
                     // this causes no posts to match if combined with 'any' below and should be re-thought... somehow
                     $groupby = implode(',', $select_distinct);
                     $having = 'count(*) = ' . count($terms);
                     // @todo this seems like it's in the wrong place
                 }
             }
             if (isset($paramset['vocabulary']['any'])) {
                 $any = $paramset['vocabulary']['any'];
                 $orwhere = new QueryWhere('OR');
                 foreach ($any as $vocab => $value) {
                     foreach ($value as $field => $terms) {
                         $andwhere = new QueryWhere();
                         // we only support these fields to search by
                         if (!in_array($field, array('id', 'term', 'term_display'))) {
                             continue;
                         }
                         $join_group = Query::new_param_name('join');
                         $query->join('JOIN {object_terms} ' . $join_group . '_ot ON {posts}.id = ' . $join_group . '_ot.object_id', array(), 'term2post_posts_' . $join_group);
                         $query->join('JOIN {terms} ' . $join_group . '_t ON ' . $join_group . '_ot.term_id = ' . $join_group . '_t.id', array(), 'terms_term2post_' . $join_group);
                         $query->join('JOIN {vocabularies} ' . $join_group . '_v ON ' . $join_group . '_t.vocabulary_id = ' . $join_group . '_v.id', array(), 'terms_vocabulary_' . $join_group);
                         $andwhere->in($join_group . '_v.name', $vocab);
                         $andwhere->in($join_group . "_t.{$field}", $terms);
                         $andwhere->in($join_group . '_ot.object_type_id', $object_id);
                     }
                     $orwhere->add($andwhere);
                     // @todo this seems like it's in the wrong place
                 }
                 $where->add($orwhere);
             }
             if (isset($paramset['vocabulary']['not'])) {
                 $not = $paramset['vocabulary']['not'];
                 foreach ($not as $vocab => $value) {
                     foreach ($value as $field => $terms) {
                         // we only support these fields to search by
                         if (!in_array($field, array('id', 'term', 'term_display'))) {
                             continue;
                         }
                         $subquery_alias = Query::new_param_name('subquery');
                         $subquery = Query::create('{object_terms}')->select('object_id');
                         $subquery->join('JOIN {terms} ON {terms}.id = {object_terms}.term_id');
                         $subquery->join('JOIN {vocabularies} ON {terms}.vocabulary_id = {vocabularies}.id');
                         $subquery->where()->in("{terms}.{$field}", $terms);
                         $subquery->where()->in('{object_terms}.object_type_id', $object_id);
                         $subquery->where()->in('{vocabularies}.name', $vocab);
                         $query->join('LEFT JOIN (' . $subquery->get() . ') ' . $subquery_alias . ' ON ' . $subquery_alias . '.object_id = {posts}.id', $subquery->params(), $subquery_alias);
                         $where->add('COALESCE(' . $subquery_alias . '.object_id, 0) = 0');
                     }
                 }
             }
         }
         if (isset($paramset['criteria'])) {
             // this regex matches any unicode letters (\p{L}) or numbers (\p{N}) inside a set of quotes (but strips the quotes) OR not in a set of quotes
             preg_match_all('/(?<=")([\\p{L}\\p{N}]+[^"]*)(?=")|([\\p{L}\\p{N}]+)/u', $paramset['criteria'], $matches);
             foreach ($matches[0] as $word) {
                 $crit_placeholder = $query->new_param_name('criteria');
                 $where->add("( LOWER( {posts}.title ) LIKE :{$crit_placeholder} OR LOWER( {posts}.content ) LIKE :{$crit_placeholder})", array($crit_placeholder => '%' . MultiByte::strtolower($word) . '%'));
             }
         }
         if (isset($paramset['title'])) {
             $where->add("LOWER( {posts}.title ) LIKE :title_match", array('title_match' => MultiByte::strtolower($paramset['title'])));
         }
         if (isset($paramset['title_search'])) {
             // this regex matches any unicode letters (\p{L}) or numbers (\p{N}) inside a set of quotes (but strips the quotes) OR not in a set of quotes
             preg_match_all('/(?<=")([\\p{L}\\p{N}]+[^"]*)(?=")|([\\p{L}\\p{N}]+)/u', $paramset['title_search'], $matches);
             foreach ($matches[0] as $word) {
                 $crit_placeholder = $query->new_param_name('title_search');
                 $where->add("LOWER( {posts}.title ) LIKE :{$crit_placeholder}", array($crit_placeholder => '%' . MultiByte::strtolower($word) . '%'));
             }
         }
         // Handle field queries on posts and joined tables
         foreach ($select_ary as $field => $aliasing) {
             if (in_array($field, array('id', 'title', 'slug', 'status', 'content_type', 'user_id'))) {
                 // skip fields that we're handling a different way
                 continue;
             }
             if (isset($paramset[$field])) {
                 if (is_callable($paramset[$field])) {
                     $paramset[$field]($where, $paramset);
                 } else {
                     $where->in($field, $paramset[$field], 'posts_field_' . $field);
                 }
             }
         }
         //Done
         if (isset($paramset['all:info']) || isset($paramset['info'])) {
             // merge the two possibile calls together
             $infos = array_merge(isset($paramset['all:info']) ? $paramset['all:info'] : array(), isset($paramset['info']) ? $paramset['info'] : array());
             if (Utils::is_traversable($infos)) {
                 $pi_count = 0;
                 foreach ($infos as $info_key => $info_value) {
                     $pi_count++;
                     $infokey_field = Query::new_param_name('info_key');
                     $infovalue_field = Query::new_param_name('info_value');
                     $query->join("LEFT JOIN {postinfo} ipi{$pi_count} ON {posts}.id = ipi{$pi_count}.post_id AND ipi{$pi_count}.name = :{$infokey_field} AND ipi{$pi_count}.value = :{$infovalue_field}", array($infokey_field => $info_key, $infovalue_field => $info_value), 'all_info_' . $info_key);
                     $where->add("ipi{$pi_count}.name <> ''");
                     $query->select(array("info_{$info_key}_value" => "ipi{$pi_count}.value AS info_{$info_key}_value"));
                     $select_distinct["info_{$info_key}_value"] = "info_{$info_key}_value";
                 }
             }
         }
         //Done
         if (isset($paramset['any:info'])) {
             if (Utils::is_traversable($paramset['any:info'])) {
                 $pi_count = 0;
                 $orwhere = new QueryWhere('OR');
                 foreach ($paramset['any:info'] as $info_key => $info_value) {
                     $pi_count++;
                     if (is_array($info_value)) {
                         $infokey_field = Query::new_param_name('info_key');
                         $inwhere = new QueryWhere('');
                         $inwhere->in("aipi{$pi_count}.value", $info_value);
                         $query->join("LEFT JOIN {postinfo} aipi{$pi_count} ON {posts}.id = aipi{$pi_count}.post_id AND aipi{$pi_count}.name = :{$infokey_field} AND " . $inwhere->get(), array_merge(array($info_key), $inwhere->params()), 'any_info_' . $info_key);
                     } else {
                         $infokey_field = Query::new_param_name('info_key');
                         $infovalue_field = Query::new_param_name('info_value');
                         $query->join("LEFT JOIN {postinfo} aipi{$pi_count} ON {posts}.id = aipi{$pi_count}.post_id AND aipi{$pi_count}.name = :{$infokey_field} AND aipi{$pi_count}.value = :{$infovalue_field}", array($infokey_field => $info_key, $infovalue_field => $info_value), 'any_info_' . $info_key);
                     }
                     $orwhere->add("aipi{$pi_count}.name <> ''");
                     $query->select(array("info_{$info_key}_value" => "aipi{$pi_count}.value AS info_{$info_key}_value"));
                     $select_distinct["info_{$info_key}_value"] = "info_{$info_key}_value";
                 }
                 $where->add('(' . $orwhere->get() . ')');
             }
         }
         // Done
         if (isset($paramset['has:info'])) {
             $has_info = Utils::single_array($paramset['has:info']);
             $pi_count = 0;
             $orwhere = new QueryWhere('OR');
             foreach ($has_info as $info_name) {
                 $infoname_field = Query::new_param_name('info_name');
                 $pi_count++;
                 $query->join("LEFT JOIN {postinfo} hipi{$pi_count} ON {posts}.id = hipi{$pi_count}.post_id AND hipi{$pi_count}.name = :{$infoname_field}", array($infoname_field => $info_name), 'has_info_' . $info_name);
                 $orwhere->add("hipi{$pi_count}.name <> ''");
                 $query->select(array("info_{$info_name}_value" => "hipi{$pi_count}.value AS info_{$info_name}_value"));
                 $select_distinct["info_{$info_name}_value"] = "info_{$info_name}_value";
             }
             $where->add('(' . $orwhere->get() . ')');
         }
         //Done
         if (isset($paramset['not:all:info']) || isset($paramset['not:info'])) {
             // merge the two possible calls together
             $infos = array_merge(isset($paramset['not:all:info']) ? $paramset['not:all:info'] : array(), isset($paramset['not:info']) ? $paramset['not:info'] : array());
             if (Utils::is_traversable($infos)) {
                 $orwhere = new QueryWhere('OR');
                 foreach ($infos as $info_key => $info_value) {
                     $andwhere = new QueryWhere();
                     $andwhere->in('{postinfo}.name', $info_key);
                     $andwhere->in('{postinfo}.value', $info_value);
                     $orwhere->add($andwhere);
                 }
                 // see that hard-coded number in having()? sqlite wets itself if we use a bound parameter... don't change that
                 $subquery = Query::create('{postinfo}')->select('{postinfo}.post_id')->groupby('post_id')->having('COUNT(*) = ' . count($infos));
                 $subquery->where()->add($orwhere);
                 $where->in('{posts}.id', $subquery, 'posts_not_all_info_query', null, false);
             }
         }
         //Tested. Test fails with original code
         if (isset($paramset['not:any:info'])) {
             if (Utils::is_traversable($paramset['not:any:info'])) {
                 $subquery = Query::create('{postinfo}')->select('post_id');
                 foreach ($paramset['not:any:info'] as $info_key => $info_value) {
                     $infokey_field = $query->new_param_name('info_key');
                     $infovalue_field = $query->new_param_name('info_value');
                     //							$subquery->where()->add(" ({postinfo}.name = :{$infokey_field} AND {postinfo}.value = :{$infovalue_field} ) ", array($infokey_field => $info_key, $infovalue_field => $info_value));
                     $subquery->where('OR')->add(" ({postinfo}.name = :{$infokey_field} AND {postinfo}.value = :{$infovalue_field} ) ", array($infokey_field => $info_key, $infovalue_field => $info_value));
                 }
                 $where->in('{posts}.id', $subquery, 'posts_not_any_info', null, false);
             }
         }
         /**
          * Build the statement needed to filter by pubdate:
          * If we've got the day, then get the date;
          * If we've got the month, but no date, get the month;
          * If we've only got the year, get the whole year.
          */
         if (isset($paramset['day']) && isset($paramset['month']) && isset($paramset['year'])) {
             $start_date = sprintf('%d-%02d-%02d', $paramset['year'], $paramset['month'], $paramset['day']);
             $start_date = DateTime::create($start_date);
             $where->add('pubdate BETWEEN :start_date AND :end_date', array('start_date' => $start_date->sql, 'end_date' => $start_date->modify('+1 day -1 second')->sql));
         } elseif (isset($paramset['month']) && isset($paramset['year'])) {
             $start_date = sprintf('%d-%02d-%02d', $paramset['year'], $paramset['month'], 1);
             $start_date = DateTime::create($start_date);
             $where->add('pubdate BETWEEN :start_date AND :end_date', array('start_date' => $start_date->sql, 'end_date' => $start_date->modify('+1 month -1 second')->sql));
         } elseif (isset($paramset['year'])) {
             $start_date = sprintf('%d-%02d-%02d', $paramset['year'], 1, 1);
             $start_date = DateTime::create($start_date);
             $where->add('pubdate BETWEEN :start_date AND :end_date', array('start_date' => $start_date->sql, 'end_date' => $start_date->modify('+1 year -1 second')->sql));
         }
         if (isset($paramset['after'])) {
             $where->add('pubdate > :after_date', array('after_date' => DateTime::create($paramset['after'])->sql));
         }
         if (isset($paramset['before'])) {
             $where->add('pubdate < :before_date', array('before_date' => DateTime::create($paramset['before'])->sql));
         }
         // Concatenate the WHERE clauses
         $query->where()->add($where);
     }
     if (isset($paramset['post_join'])) {
         $post_joins = Utils::single_array($paramset['post_join']);
         foreach ($post_joins as $post_join) {
             if (preg_match('#^(\\S+)(?:\\s+as)?\\s+(\\S+)$#i', $post_join, $matches)) {
                 $query->join("LEFT JOIN {$matches[1]} {$matches[2]} ON {$matches[2]}.post_id = {posts}.id ");
             } else {
                 $query->join("LEFT JOIN {$post_join} ON {$post_join}.post_id = {posts}.id ");
             }
         }
     }
     // Only show posts to which the current user has permission
     if (isset($paramset['ignore_permissions'])) {
         $master_perm_where = new QueryWhere();
         // Set up the merge params
         $merge_params = array($join_params, $params);
         $params = call_user_func_array('array_merge', $merge_params);
     } else {
         $master_perm_where = new QueryWhere();
         // This set of wheres will be used to generate a list of post_ids that this user can read
         $perm_where = new QueryWhere('OR');
         $perm_where_denied = new QueryWhere('AND');
         // Get the tokens that this user is granted or denied access to read
         $read_tokens = isset($paramset['read_tokens']) ? $paramset['read_tokens'] : ACL::user_tokens(User::identify(), 'read', true);
         $deny_tokens = isset($paramset['deny_tokens']) ? $paramset['deny_tokens'] : ACL::user_tokens(User::identify(), 'deny', true);
         // If a user can read any post type, let him
         if (User::identify()->can('post_any', 'read')) {
             $perm_where->add('(1=1)');
         } else {
             // If a user can read his own posts, let him
             if (User::identify()->can('own_posts', 'read')) {
                 $perm_where->add('{posts}.user_id = :current_user_id', array('current_user_id' => User::identify()->id));
             }
             // If a user can read specific post types, let him
             $permitted_post_types = array();
             foreach (Post::list_active_post_types() as $name => $posttype) {
                 if (User::identify()->can('post_' . Utils::slugify($name), 'read')) {
                     $permitted_post_types[] = $posttype;
                 }
             }
             if (count($permitted_post_types) > 0) {
                 $perm_where->in('{posts}.content_type', $permitted_post_types, 'posts_permitted_types', 'intval');
             }
             // If a user can read posts with specific tokens, let him
             if (count($read_tokens) > 0) {
                 $query->join('LEFT JOIN {post_tokens} pt_allowed ON {posts}.id= pt_allowed.post_id AND pt_allowed.token_id IN (' . implode(',', $read_tokens) . ')', array(), 'post_tokens__allowed');
                 $perm_where->add('pt_allowed.post_id IS NOT NULL', array(), 'perms_join_not_null');
             }
             // If a user has access to read other users' unpublished posts, let him
             if (User::identify()->can('post_unpublished', 'read')) {
                 $perm_where->add('({posts}.status <> :status_published AND {posts}.user_id <> :current_user_id)', array('current_user_id' => User::identify()->id, 'status_published' => Post::status('published')));
             }
         }
         // If a user is denied access to all posts, do so
         if (User::identify()->cannot('post_any')) {
             $perm_where_denied->add('(1=0)');
         } else {
             // If a user is denied read access to specific post types, deny him
             $denied_post_types = array();
             foreach (Post::list_active_post_types() as $name => $posttype) {
                 if (User::identify()->cannot('post_' . Utils::slugify($name))) {
                     $denied_post_types[] = $posttype;
                 }
             }
             if (count($denied_post_types) > 0) {
                 $perm_where_denied->in('{posts}.content_type', $denied_post_types, 'posts_denied_types', 'intval', false);
             }
             // If a user is denied read access to posts with specific tokens, deny it
             if (count($deny_tokens) > 0) {
                 $query->join('LEFT JOIN {post_tokens} pt_denied ON {posts}.id= pt_denied.post_id AND pt_denied.token_id IN (' . implode(',', $deny_tokens) . ')', array(), 'post_tokens__denied');
                 $perm_where_denied->add('pt_denied.post_id IS NULL', array(), 'perms_join_null');
             }
             // If a user is denied access to read other users' unpublished posts, deny it
             if (User::identify()->cannot('post_unpublished')) {
                 $perm_where_denied->add('({posts}.status = :status_published OR {posts}.user_id = :current_user_id)', array('current_user_id' => User::identify()->id, 'status_published' => Post::status('published')));
             }
         }
         Plugins::act('post_get_perm_where', $perm_where, $paramarray);
         Plugins::act('post_get_perm_where_denied', $perm_where_denied, $paramarray);
         // If there are granted permissions to check, add them to the where clause
         if ($perm_where->count() == 0 && !$query->joined('post_tokens__allowed')) {
             $master_perm_where->add('(1=0)', array(), 'perms_granted');
         } else {
             $master_perm_where->add($perm_where, array(), 'perms_granted');
         }
         // If there are denied permissions to check, add them to the where clause
         if ($perm_where_denied->count() > 0 || $query->joined('post_tokens__denied')) {
             $master_perm_where->add($perm_where_denied, array(), 'perms_denied');
         }
     }
     $query->where()->add($master_perm_where, array(), 'master_perm_where');
     // Extract the remaining parameters which will be used onwards
     // For example: page number, fetch function, limit
     $paramarray = new SuperGlobal($paramarray);
     $extract = $paramarray->filter_keys('page', 'fetch_fn', 'count', 'orderby', 'groupby', 'limit', 'offset', 'nolimit', 'having', 'add_select');
     foreach ($extract as $key => $value) {
         ${$key} = $value;
     }
     // Calculate the OFFSET based on the page number. Requires a limit.
     if (isset($page) && is_numeric($page) && !isset($paramset['offset']) && isset($limit)) {
         $offset = (intval($page) - 1) * intval($limit);
     }
     /**
      * Determine which fetch function to use:
      * If it is specified, make sure it is valid (based on the $fns array defined at the beginning of this function);
      * Else, use 'get_results' which will return a Posts array of Post objects.
      */
     if (isset($fetch_fn)) {
         if (!in_array($fetch_fn, $fns)) {
             $fetch_fn = $fns[0];
         }
     } else {
         $fetch_fn = $fns[0];
     }
     // Add arbitrary fields to the select clause for sorting and output
     if (isset($add_select)) {
         $query->select($add_select);
     }
     /**
      * If a count is requested:
      * Replace the current fields to select with a COUNT();
      * Change the fetch function to 'get_value';
      * Remove the ORDER BY since it's useless.
      * Remove the GROUP BY (tag search added it)
      */
     if (isset($count)) {
         $query->set_select("COUNT({$count})");
         $fetch_fn = isset($paramarray['fetch_fn']) ? $fetch_fn : 'get_value';
         $orderby = null;
         $groupby = null;
         $having = null;
     }
     // If the month counts are requested, replaced the select clause
     if (isset($paramset['month_cts'])) {
         if (isset($paramset['vocabulary'])) {
             $query->set_select('MONTH(FROM_UNIXTIME(pubdate)) AS month, YEAR(FROM_UNIXTIME(pubdate)) AS year, COUNT(DISTINCT {posts}.id) AS ct');
         } else {
             $query->set_select('MONTH(FROM_UNIXTIME(pubdate)) AS month, YEAR(FROM_UNIXTIME(pubdate)) AS year, COUNT(*) AS ct');
         }
         $groupby = 'year, month';
         if (!isset($paramarray['orderby'])) {
             $orderby = 'year, month';
         }
     }
     // Remove the LIMIT if 'nolimit'
     // Doing this first should allow OFFSET to work
     if (isset($nolimit)) {
         $limit = null;
     }
     // Define the LIMIT, OFFSET, ORDER BY, GROUP BY if they exist
     if (isset($limit)) {
         $query->limit($limit);
     }
     if (isset($offset)) {
         $query->offset($offset);
     }
     if (isset($orderby)) {
         $query->orderby($orderby);
     }
     if (isset($groupby)) {
         $query->groupby($groupby);
     }
     if (isset($having)) {
         $query->having($having);
     }
     if (isset($paramarray['on_query_built'])) {
         foreach (Utils::single_array($paramarray['on_query_built']) as $built) {
             $built($query);
         }
     }
     Plugins::act('posts_get_query', $query, $paramarray);
     /* All SQL parts are constructed, on to real business! */
     /**
      * DEBUG: Uncomment the following line to display everything that happens in this function
      */
     //print_R('<pre>'.$query.'</pre>');
     //Utils::debug( $paramarray, $fetch_fn, $query, $params );
     //Session::notice($query);
     if ('get_query' == $fetch_fn) {
         return array($query->get(), $query->params());
     }
     /**
      * Execute the SQL statement using the PDO extension
      */
     DB::set_fetch_mode(\PDO::FETCH_CLASS);
     $fetch_class = 'Post';
     if (isset($paramarray['fetch_class'])) {
         $fetch_class = $paramarray['fetch_class'];
     }
     DB::set_fetch_class($fetch_class);
     $results = DB::$fetch_fn($query->get(), $query->params(), $fetch_class);
     //Utils::debug($results, $query->get(), $query->params());
     //Utils::debug( $paramarray, $fetch_fn, $query->get(), $query->params(), $results );
     //var_dump( $query );
     /**
      * Return the results
      */
     if ('get_results' != $fetch_fn) {
         // Since a single result was requested, return a single Post object.
         return $results;
     } elseif (is_array($results)) {
         // With multiple results, return a Posts array of Post objects.
         $c = __CLASS__;
         $return_value = new $c($results);
         $return_value->get_param_cache = $paramarray;
         return $return_value;
     }
 }
예제 #2
0
파일: eventlog.php 프로젝트: habari/system
 /**
  * Returns a LogEntry or EventLog array based on supplied parameters.
  * By default,fetch as many entries as pagination allows and order them in a descending fashion based on timestamp.
  *
  * @todo Cache query results.
  * @param array $paramarray An associated array of parameters, or a querystring
  * The following keys are supported:
  * - id => an entry id or array of post ids
  * - user_id => id of the logged in user for which to return entries
  * - severity => severity level for which to return entries
  * - type_id => the numeric id or array of ids for the type of entries for which which to return entries
  * - module => a name or array of names of modules for which to return entries
  * - type => a single type name or array of type names for which to return entries
  * - ip => the IP number for which to return entries
  * - criteria => a literal search string to match entry message content or a special search
  * - day => a day of entry creation, ignored if month and year are not specified
  * - month => a month of entry creation, ignored if year isn't specified
  * - year => a year of entry creation
  * - orderby => how to order the returned entries
  * - fetch_fn => the function used to fetch data, one of 'get_results', 'get_row', 'get_value'
  * - count => return the number of entries that would be returned by this request
  * - month_cts => return the number of entries created in each month
  * - nolimit => do not implicitly set limit
  * - limit => the maximum number of entries to return, implicitly set for many queries
  * - index => 
  * - offset => amount by which to offset returned entries, used in conjunction with limit
  * - where => manipulate the generated WHERE clause
  * - return_data => set to return the data associated with the entry
  * 
  * @return array An array of LogEntry objects, or a single LogEntry object, depending on request
  */
 public static function get($paramarray = array())
 {
     $params = array();
     $fns = array('get_results', 'get_row', 'get_value');
     $select_ary = array();
     $select_distinct = array();
     // Put incoming parameters into the local scope
     $paramarray = Utils::get_params($paramarray);
     if ($paramarray instanceof \ArrayIterator) {
         $paramarray = $paramarray->getArrayCopy();
     }
     $select_fields = LogEntry::default_fields();
     if (!isset($paramarray['return_data'])) {
         unset($select_fields['data']);
     }
     foreach ($select_fields as $field => $value) {
         if (preg_match('/(?:(?P<table>[\\w\\{\\}]+)\\.)?(?P<field>\\w+)(?:(?:\\s+as\\s+)(?P<alias>\\w+))?/i', $field, $fielddata)) {
             if (empty($fielddata['table'])) {
                 $fielddata['table'] = '{log}';
             }
             if (empty($fielddata['alias'])) {
                 $fielddata['alias'] = $fielddata['field'];
             }
         }
         $select_ary[$fielddata['alias']] = "{$fielddata['table']}.{$fielddata['field']} AS {$fielddata['alias']}";
         $select_distinct[$fielddata['alias']] = "{$fielddata['table']}.{$fielddata['field']}";
     }
     // Transact on possible multiple sets of where information that is to be OR'ed
     if (isset($paramarray['where']) && is_array($paramarray['where'])) {
         $wheresets = $paramarray['where'];
     } else {
         $wheresets = array(array());
     }
     $query = Query::create('{log}');
     $query->select($select_ary);
     if (isset($paramarray['where']) && is_string($paramarray['where'])) {
         $query->where()->add($paramarray['where']);
     }
     foreach ($wheresets as $paramset) {
         $where = new QueryWhere();
         $paramset = array_merge((array) $paramarray, (array) $paramset);
         if (isset($paramset['id'])) {
             $where->in('{log}.id', $paramset['id'], 'log_id', 'intval');
         }
         if (isset($paramset['user_id'])) {
             $where->in('{log}.user_id', $paramset['user_id'], 'log_user_id', 'intval');
         }
         if (isset($paramset['severity']) && 'any' != LogEntry::severity_name($paramset['severity'])) {
             $where->in('{log}.severity_id', $paramset['severity'], 'log_severity_id', function ($a) {
                 return LogEntry::severity($a);
             });
         }
         if (isset($paramset['type_id'])) {
             $where->in('{log}.type_id', $paramset['type_id'], 'log_type_id', 'intval');
         }
         if (isset($paramset['module'])) {
             $paramset['module'] = Utils::single_array($paramset['module']);
             $qry = Query::create('{log_types}');
             $qry->select('{log_types}.id')->distinct();
             $qry->where()->in('{log_types}.module', $paramset['module'], 'log_subquery_module');
             $where->in('{log}.type_id', $qry, 'log_module');
         }
         if (isset($paramset['type'])) {
             $paramset['type'] = Utils::single_array($paramset['type']);
             $qry = Query::create('{log_types}');
             $qry->select('{log_types}.id')->distinct();
             $qry->where()->in('{log_types}.type', $paramset['type'], 'log_subquery_type');
             $where->in('{log}.type_id', $qry, 'log_type');
         }
         if (isset($paramset['ip'])) {
             $where->in('{log}.ip', $paramset['ip']);
         }
         /* do searching */
         if (isset($paramset['criteria'])) {
             // this regex matches any unicode letters (\p{L}) or numbers (\p{N}) inside a set of quotes (but strips the quotes) OR not in a set of quotes
             preg_match_all('/(?<=")(\\w[^"]*)(?=")|([:\\w]+)/u', $paramset['criteria'], $matches);
             foreach ($matches[0] as $word) {
                 if (preg_match('%^id:(\\d+)$%i', $word, $special_crit)) {
                     $where->in('{log}.id', $special_crit[1], 'log_special_criteria');
                 } else {
                     $crit_placeholder = $query->new_param_name('criteria');
                     $where->add("( LOWER( {log}.message ) LIKE :{$crit_placeholder}", array($crit_placeholder => '%' . MultiByte::strtolower($word) . '%'));
                 }
             }
         }
         /**
          * Build the pubdate
          * If we've got the day, then get the date.
          * If we've got the month, but no date, get the month.
          * If we've only got the year, get the whole year.
          *
          * @todo Ensure that we've actually got all the needed parts when we query on them
          */
         if (isset($paramset['day']) && isset($paramset['month']) && isset($paramset['year'])) {
             $start_date = sprintf('%d-%02d-%02d', $paramset['year'], $paramset['month'], $paramset['day']);
             $start_date = DateTime::create($start_date);
             $where->add('timestamp BETWEEN :start_date AND :end_date', array('start_date' => $start_date->sql, 'end_date' => $start_date->modify('+1 day -1 second')->sql));
         } elseif (isset($paramset['month']) && isset($paramset['year'])) {
             $start_date = sprintf('%d-%02d-%02d', $paramset['year'], $paramset['month'], 1);
             $start_date = DateTime::create($start_date);
             $where->add('timestamp BETWEEN :start_date AND :end_date', array('start_date' => $start_date->sql, 'end_date' => $start_date->modify('+1 month -1 second')->sql));
         } elseif (isset($paramset['year'])) {
             $start_date = sprintf('%d-%02d-%02d', $paramset['year'], 1, 1);
             $start_date = DateTime::create($start_date);
             $where->add('timestamp BETWEEN :start_date AND :end_date', array('start_date' => $start_date->sql, 'end_date' => $start_date->modify('+1 year -1 second')->sql));
         }
         // Concatenate the WHERE clauses
         $query->where()->add($where);
     }
     // Default parameters.
     $orderby = 'timestamp DESC, id DESC';
     //		$limit = Options::get( 'pagination' );
     // Get any full-query parameters
     $paramarray = new SuperGlobal($paramarray);
     $extract = $paramarray->filter_keys('orderby', 'fetch_fn', 'count', 'month_cts', 'nolimit', 'index', 'limit', 'offset');
     foreach ($extract as $key => $value) {
         ${$key} = $value;
     }
     if (isset($index) && is_numeric($index)) {
         $offset = (intval($index) - 1) * intval($limit);
     }
     if (isset($fetch_fn)) {
         if (!in_array($fetch_fn, $fns)) {
             $fetch_fn = $fns[0];
         }
     } else {
         $fetch_fn = $fns[0];
     }
     if (isset($count)) {
         $query->set_select("COUNT({$count})");
         $fetch_fn = isset($paramarray['fetch_fn']) ? $fetch_fn : 'get_value';
         $orderby = null;
         $groupby = null;
         $having = null;
     }
     // If the month counts are requested, replace the select clause
     if (isset($paramset['month_cts'])) {
         // @todo shouldn't this hand back to habari to convert to DateTime so it reflects the right timezone?
         $query->set_select('MONTH(FROM_UNIXTIME(timestamp)) AS month, YEAR(FROM_UNIXTIME(timestamp)) AS year, COUNT(*) AS ct');
         $groupby = 'year, month';
         if (!isset($paramarray['orderby'])) {
             $orderby = 'year, month';
         }
     }
     if (isset($nolimit) || isset($month_cts)) {
         $limit = null;
     }
     // Define the LIMIT, OFFSET, ORDER BY, GROUP BY if they exist
     if (isset($limit)) {
         $query->limit($limit);
     }
     if (isset($offset)) {
         $query->offset($offset);
     }
     if (isset($orderby)) {
         $query->orderby($orderby);
     }
     if (isset($groupby)) {
         $query->groupby($groupby);
     }
     /*
     if(isset($paramarray['type'])) {
     	print_r($query->params());
     	print_r($query->get());die();
     }
     */
     /* All SQL parts are constructed, on to real business! */
     DB::set_fetch_mode(\PDO::FETCH_CLASS);
     DB::set_fetch_class('LogEntry');
     $results = DB::$fetch_fn($query->get(), $query->params(), 'LogEntry');
     // If the fetch callback function is not get_results,
     // return an EventLog ArrayObject filled with the results as LogEntry objects.
     if ('get_results' != $fetch_fn) {
         return $results;
     } elseif (is_array($results)) {
         $c = __CLASS__;
         $return_value = new $c($results);
         $return_value->get_param_cache = $paramarray;
         return $return_value;
     }
 }