コード例 #1
0
ファイル: posts.php プロジェクト: psaintlaurent/Habari
    /**
     * Returns a post or posts based on supplied parameters.
     * <b>THIS CLASS SHOULD CACHE QUERY RESULTS!</b>
     *
     * @param array $paramarry An associated array of parameters, or a querystring
     * @return array An array of Post objects, or a single post object, depending on request
     */
    public static function get($paramarray = array())
    {
        $join_params = array();
        $params = array();
        $fns = array('get_results', 'get_row', 'get_value');
        $select_ary = array();
        // Default fields to select, everything by default
        foreach (Post::default_fields() as $field => $value) {
            $select_ary[$field] = "{posts}.{$field} AS {$field}";
        }
        // Default parameters
        $orderby = 'pubdate DESC';
        // If $paramarray is a querystring, convert it to an array
        $paramarray = Utils::get_params($paramarray);
        // 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 */
        $wheres = array();
        $joins = array();
        // If the request as a textual WHERE clause, skip the processing of the $wheresets since it's empty
        if (isset($paramarray['where']) && is_string($paramarray['where'])) {
            $wheres[] = $paramarray['where'];
        } else {
            foreach ($wheresets as $paramset) {
                // Safety mechanism to prevent empty queries
                $where = array();
                $paramset = array_merge((array) $paramarray, (array) $paramset);
                // $nots= preg_grep( '%^not:(\w+)$%iu', (array) $paramset );
                if (isset($paramset['id'])) {
                    if (is_array($paramset['id'])) {
                        array_walk($paramset['id'], create_function('&$a,$b', '$a = intval($a);'));
                        $where[] = "{posts}.id IN (" . implode(',', array_fill(0, count($paramset['id']), '?')) . ")";
                        $params = array_merge($params, $paramset['id']);
                    } else {
                        $where[] = "{posts}.id = ?";
                        $params[] = (int) $paramset['id'];
                    }
                }
                if (isset($paramset['not:id'])) {
                    if (is_array($paramset['not:id'])) {
                        array_walk($paramset['not:id'], create_function('&$a,$b', '$a = intval($a);'));
                        $where[] = "{posts}.id NOT IN (" . implode(',', array_fill(0, count($paramset['not:id']), '?')) . ")";
                        $params = array_merge($params, $paramset['not:id']);
                    } else {
                        $where[] = "{posts}.id != ?";
                        $params[] = (int) $paramset['not:id'];
                    }
                }
                if (isset($paramset['status']) && $paramset['status'] != 'any' && 0 !== $paramset['status']) {
                    if (is_array($paramset['status'])) {
                        // remove 'any' from the list if we have an array
                        $paramset['status'] = array_diff($paramset['status'], array('any'));
                        array_walk($paramset['status'], create_function('&$a,$b', '$a = Post::status($a);'));
                        $where[] = "{posts}.status IN (" . implode(',', array_fill(0, count($paramset['status']), '?')) . ")";
                        $params = array_merge($params, $paramset['status']);
                    } else {
                        $where[] = "{posts}.status = ?";
                        $params[] = (int) Post::status($paramset['status']);
                    }
                }
                if (isset($paramset['content_type']) && $paramset['content_type'] != 'any' && 0 !== $paramset['content_type']) {
                    if (is_array($paramset['content_type'])) {
                        // remove 'any' from the list if we have an array
                        $paramset['content_type'] = array_diff($paramset['content_type'], array('any'));
                        array_walk($paramset['content_type'], create_function('&$a,$b', '$a = Post::type($a);'));
                        $where[] = "{posts}.content_type IN (" . implode(',', array_fill(0, count($paramset['content_type']), '?')) . ")";
                        $params = array_merge($params, $paramset['content_type']);
                    } else {
                        $where[] = "{posts}.content_type = ?";
                        $params[] = (int) Post::type($paramset['content_type']);
                    }
                }
                if (isset($paramset['not:content_type'])) {
                    if (is_array($paramset['not:content_type'])) {
                        array_walk($paramset['not:content_type'], create_function('&$a,$b', '$a = Post::type($a);'));
                        $where[] = "{posts}.content_type NOT IN (" . implode(',', array_fill(0, count($paramset['not:content_type']), '?')) . ")";
                        $params = array_merge($params, $paramset['not:content_type']);
                    } else {
                        $where[] = "{posts}.content_type != ?";
                        $params[] = (int) Post::type($paramset['not:content_type']);
                    }
                }
                if (isset($paramset['slug'])) {
                    if (is_array($paramset['slug'])) {
                        $where[] = "{posts}.slug IN (" . implode(',', array_fill(0, count($paramset['slug']), '?')) . ")";
                        $params = array_merge($params, $paramset['slug']);
                    } else {
                        $where[] = "{posts}.slug = ?";
                        $params[] = (string) $paramset['slug'];
                    }
                }
                if (isset($paramset['user_id']) && 0 !== $paramset['user_id']) {
                    if (is_array($paramset['user_id'])) {
                        array_walk($paramset['user_id'], create_function('&$a,$b', '$a = intval($a);'));
                        $where[] = "{posts}.user_id IN (" . implode(',', array_fill(0, count($paramset['user_id']), '?')) . ")";
                        $params = array_merge($params, $paramset['user_id']);
                    } else {
                        $where[] = "{posts}.user_id = ?";
                        $params[] = (int) $paramset['user_id'];
                    }
                }
                if (isset($paramset['tag']) || isset($paramset['tag_slug'])) {
                    $joins['tag2post_posts'] = ' JOIN {object_terms} ON {posts}.id = {object_terms}.object_id';
                    $joins['tags_tag2post'] = ' JOIN {terms} ON {object_terms}.term_id = {terms}.id';
                    if (isset($paramset['tag'])) {
                        if (is_array($paramset['tag'])) {
                            $where[] = "{terms}.term_display IN (" . implode(',', array_fill(0, count($paramset['tag']), '?')) . ")" . '  AND {object_terms}.object_type_id = ?';
                            $params = array_merge($params, $paramset['tag']);
                        } else {
                            $where[] = '{terms}.term_display = ? AND {object_terms}.object_type_id = ?';
                            $params[] = (string) $paramset['tag'];
                        }
                    }
                    if (isset($paramset['tag_slug'])) {
                        if (is_array($paramset['tag_slug'])) {
                            $where[] = "{terms}.term IN (" . implode(',', array_fill(0, count($paramset['tag_slug']), '?')) . ")" . ' AND {object_terms}.object_type_id = ?';
                            $params = array_merge($params, $paramset['tag_slug']);
                        } else {
                            $where[] = '{terms}.term= ? AND {object_terms}.object_type_id = ?';
                            $params[] = (string) $paramset['tag_slug'];
                        }
                    }
                    $params[] = Vocabulary::object_type_id(Tags::object_type());
                }
                if (isset($paramset['all:tag'])) {
                    $joins['tag2post_posts'] = ' JOIN {object_terms} ON {posts}.id = {object_terms}.object_id';
                    $joins['tags_tag2post'] = ' JOIN {terms} ON {object_terms}.term_id = {terms}.id';
                    if (is_array($paramset['all:tag'])) {
                        $where[] = '{terms}.term_display IN (' . Utils::placeholder_string($paramset['all:tag']) . ')' . ' AND {object_terms}.object_type_id = ?';
                        $params = array_merge($params, $paramset['all:tag']);
                        $groupby = '{posts}.id';
                        $having = 'count(*) = ' . count($paramset['all:tag']);
                    } else {
                        // this is actually the same as plain 'tag' for a single tag search - go with it
                        $where[] = '{terms}.term_display = ? AND {object_terms}.object_type_id = ?';
                        $params[] = $paramset['all:tag'];
                    }
                    $params[] = Vocabulary::object_type_id(Tags::object_type());
                }
                if (isset($paramset['all:tag_slug'])) {
                    $joins['tag2post_posts'] = ' JOIN {object_terms} ON {posts}.id = {object_terms}.object_id';
                    $joins['tags_tag2post'] = ' JOIN {terms} ON {object_terms}.term_id = {terms}.id';
                    if (is_array($paramset['all:tag_slug'])) {
                        $where[] = '{terms}.term IN (' . Utils::placeholder_string($paramset['all:tag_slug']) . ')' . ' AND {object_terms}.object_type_id = ?';
                        $params = array_merge($params, $paramset['all:tag_slug']);
                        $groupby = '{posts}.id';
                        $having = 'count(*) = ' . count($paramset['all:tag_slug']);
                    } else {
                        // this is actually the same as plain 'tag' for a single tag search - go with it
                        $where[] = '{terms}.term = ? AND {object_terms}.object_type_id = ?';
                        $params[] = $paramset['all:tag_slug'];
                    }
                    $params[] = Vocabulary::object_type_id(Tags::object_type());
                }
                if (isset($paramset['not:tag'])) {
                    $nottag = Utils::single_array($paramset['not:tag']);
                    $where[] = 'NOT EXISTS (SELECT 1
						FROM {object_terms}
						INNER JOIN {terms} ON {terms}.id = {object_terms}.term_id
						WHERE {terms}.term_display IN (' . Utils::placeholder_string($nottag) . ')
						AND {object_terms}.object_id = {posts}.id
						AND {object_terms}.object_type_id = ?)
					';
                    $params = array_merge($params, $nottag);
                    $params[] = Vocabulary::object_type_id(Tags::object_type());
                }
                if (isset($paramset['not:tag_slug'])) {
                    $nottag = Utils::single_array($paramset['not:tag_slug']);
                    $where[] = 'NOT EXISTS (SELECT 1
						FROM {object_terms}
						INNER JOIN {terms} ON {terms}.id = {object_terms}.term_id
						WHERE {terms}.term_display IN (' . Utils::placeholder_string($nottag) . ')
						AND {object_terms}.object_id = {posts}.id
						AND {object_terms}.object_type_id = ?)
					';
                    $params = array_merge($params, $nottag);
                    $params[] = Vocabulary::object_type_id(Tags::object_type());
                }
                if (isset($paramset['criteria'])) {
                    preg_match_all('/(?<=")([\\p{L}\\p{N}]+[^"]*)(?=")|([\\p{L}\\p{N}]+)/u', $paramset['criteria'], $matches);
                    foreach ($matches[0] as $word) {
                        $where[] .= "({posts}.title LIKE CONCAT('%',?,'%') OR {posts}.content LIKE CONCAT('%',?,'%'))";
                        $params[] = $word;
                        $params[] = $word;
                        // Not a typo (there are two ? in the above statement)
                    }
                }
                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++;
                            $joins['info_' . $info_key] = " LEFT JOIN {postinfo} ipi{$pi_count} ON {posts}.id = ipi{$pi_count}.post_id AND ipi{$pi_count}.name = ? AND ipi{$pi_count}.value = ?";
                            $join_params[] = $info_key;
                            $join_params[] = $info_value;
                            $where[] = "ipi{$pi_count}.name <> ''";
                            $select_ary["info_{$info_key}_value"] = "ipi{$pi_count}.value AS info_{$info_key}_value";
                        }
                    }
                }
                if (isset($paramset['any:info'])) {
                    if (Utils::is_traversable($paramset['any:info'])) {
                        $pi_count = 0;
                        $pi_where = array();
                        foreach ($paramset['any:info'] as $info_key => $info_value) {
                            $pi_count++;
                            $join_params[] = $info_key;
                            if (is_array($info_value)) {
                                $joins['any_info_' . $info_key] = " LEFT JOIN {postinfo} aipi{$pi_count} ON {posts}.id = aipi{$pi_count}.post_id AND aipi{$pi_count}.name = ? AND aipi{$pi_count}.value IN (" . Utils::placeholder_string(count($info_value)) . ")";
                                $join_params = array_merge($join_params, $info_value);
                            } else {
                                $joins['any_info_' . $info_key] = " LEFT JOIN {postinfo} aipi{$pi_count} ON {posts}.id = aipi{$pi_count}.post_id AND aipi{$pi_count}.name = ? AND aipi{$pi_count}.value = ?";
                                $join_params[] = $info_value;
                            }
                            $pi_where[] = "aipi{$pi_count}.name <> ''";
                            $select_ary["info_{$info_key}_value"] = "aipi{$pi_count}.value AS info_{$info_key}_value";
                        }
                        $where[] = '(' . implode(' OR ', $pi_where) . ')';
                    }
                }
                if (isset($paramset['has:info'])) {
                    $the_ins = array();
                    $has_info = Utils::single_array($paramset['has:info']);
                    $pi_count = 0;
                    $pi_where = array();
                    foreach ($has_info as $info_name) {
                        $pi_count++;
                        $joins['has_info_' . $info_name] = " LEFT JOIN {postinfo} hipi{$pi_count} ON {posts}.id = hipi{$pi_count}.post_id AND hipi{$pi_count}.name = ?";
                        $join_params[] = $info_name;
                        $pi_where[] = "hipi{$pi_count}.name <> ''";
                        $select_ary["info_{$info_name}_value"] = "hipi{$pi_count}.value AS info_{$info_name}_value";
                    }
                    $where[] = '(' . implode(' OR ', $pi_where) . ')';
                }
                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)) {
                        $the_ins = array();
                        foreach ($infos as $info_key => $info_value) {
                            $the_ins[] = ' ({postinfo}.name = ? AND {postinfo}.value = ? ) ';
                            $params[] = $info_key;
                            $params[] = $info_value;
                        }
                        $where[] = '
							{posts}.id NOT IN (
							SELECT post_id FROM {postinfo}
							WHERE ( ' . implode(' OR ', $the_ins) . ' )
							GROUP BY post_id
							HAVING COUNT(*) = ' . count($infos) . ' )
						';
                        // see that hard-coded number? sqlite wets itself if we use a bound parameter... don't change that
                    }
                }
                if (isset($paramset['not:any:info'])) {
                    if (Utils::is_traversable($paramset['not:any:info'])) {
                        foreach ($paramset['not:any:info'] as $info_key => $info_value) {
                            $the_ins[] = ' ({postinfo}.name = ? AND {postinfo}.value = ? ) ';
                            $params[] = $info_key;
                            $params[] = $info_value;
                        }
                        $where[] = '
							{posts}.id NOT IN (
								SELECT post_id FROM {postinfo}
								WHERE ( ' . implode(' OR ', $the_ins) . ' )
							)
						';
                    }
                }
                /**
                 * 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'])) {
                    $where[] = 'pubdate BETWEEN ? AND ?';
                    $startDate = sprintf('%d-%02d-%02d', $paramset['year'], $paramset['month'], $paramset['day']);
                    $startDate = HabariDateTime::date_create($startDate);
                    $params[] = $startDate->sql;
                    $params[] = $startDate->modify('+1 day')->sql;
                    //$params[] = date( 'Y-m-d H:i:s', mktime( 0, 0, 0, $paramset['month'], $paramset['day'], $paramset['year'] ) );
                    //$params[] = date( 'Y-m-d H:i:s', mktime( 23, 59, 59, $paramset['month'], $paramset['day'], $paramset['year'] ) );
                } elseif (isset($paramset['month']) && isset($paramset['year'])) {
                    $where[] = 'pubdate BETWEEN ? AND ?';
                    $startDate = sprintf('%d-%02d-%02d', $paramset['year'], $paramset['month'], 1);
                    $startDate = HabariDateTime::date_create($startDate);
                    $params[] = $startDate->sql;
                    $params[] = $startDate->modify('+1 month')->sql;
                    //$params[] = date( 'Y-m-d H:i:s', mktime( 0, 0, 0, $paramset['month'], 1, $paramset['year'] ) );
                    //$params[] = date( 'Y-m-d H:i:s', mktime( 23, 59, 59, $paramset['month'] + 1, 0, $paramset['year'] ) );
                } elseif (isset($paramset['year'])) {
                    $where[] = 'pubdate BETWEEN ? AND ?';
                    $startDate = sprintf('%d-%02d-%02d', $paramset['year'], 1, 1);
                    $startDate = HabariDateTime::date_create($startDate);
                    $params[] = $startDate->sql;
                    $params[] = $startDate->modify('+1 year')->sql;
                    //$params[] = date( 'Y-m-d H:i:s', mktime( 0, 0, 0, 1, 1, $paramset['year'] ) );
                    //$params[] = date( 'Y-m-d H:i:s', mktime( 0, 0, -1, 1, 1, $paramset['year'] + 1 ) );
                }
                if (isset($paramset['after'])) {
                    $where[] = 'pubdate > ?';
                    $params[] = HabariDateTime::date_create($paramset['after'])->sql;
                }
                if (isset($paramset['before'])) {
                    $where[] = 'pubdate < ?';
                    $params[] = HabariDateTime::date_create($paramset['before'])->sql;
                }
                // Concatenate the WHERE clauses
                if (count($where) > 0) {
                    $wheres[] = ' (' . implode(' AND ', $where) . ') ';
                }
            }
        }
        // Only show posts to which the current user has permission
        if (isset($paramset['ignore_permissions'])) {
            $master_perm_where = '';
        } else {
            // This set of wheres will be used to generate a list of post_ids that this user can read
            $perm_where = array();
            $perm_where_denied = array();
            $params_where = array();
            $where = array();
            // 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 his own posts, let him
            if (User::identify()->can('own_posts', 'read')) {
                $perm_where['own_posts_id'] = '{posts}.user_id = ?';
                $params_where[] = User::identify()->id;
            }
            // If a user can read any post type, let him
            if (User::identify()->can('post_any', 'read')) {
                $perm_where = array('post_any' => '(1=1)');
                $params_where = array();
            } else {
                // 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[] = '{posts}.content_type IN (' . implode(',', $permitted_post_types) . ')';
                }
                // If a user can read posts with specific tokens, let him
                if (count($read_tokens) > 0) {
                    $joins['post_tokens__allowed'] = ' LEFT JOIN {post_tokens} pt_allowed ON {posts}.id= pt_allowed.post_id AND pt_allowed.token_id IN (' . implode(',', $read_tokens) . ')';
                    $perm_where['perms_join_null'] = 'pt_allowed.post_id IS NOT NULL';
                }
            }
            // If a user is denied access to all posts, do so
            if (User::identify()->cannot('post_any')) {
                $perm_where_denied = array('(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[] = '{posts}.content_type NOT IN (' . implode(',', $denied_post_types) . ')';
                }
            }
            // If there are granted permissions to check, add them to the where clause
            if (count($perm_where) == 0 && !isset($joins['post_tokens__allowed'])) {
                // You have no grants.  You get no posts.
                $where['perms_granted'] = '(1=0)';
            } elseif (count($perm_where) > 0) {
                $where['perms_granted'] = '
					(' . implode(' OR ', $perm_where) . ')
				';
                $params = array_merge($join_params, $params, $params_where);
            }
            if (count($deny_tokens) > 0) {
                $joins['post_tokens__denied'] = ' LEFT JOIN {post_tokens} pt_denied ON {posts}.id= pt_denied.post_id AND pt_denied.token_id IN (' . implode(',', $deny_tokens) . ')';
                $perm_where_denied['perms_join_null'] = 'pt_denied.post_id IS NULL';
            }
            // If there are denied permissions to check, add them to the where clause
            if (count($perm_where_denied) > 0) {
                $where['perms_denied'] = '
					(' . implode(' AND ', $perm_where_denied) . ')
				';
            }
            $master_perm_where = implode(' AND ', $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');
        foreach ($extract as $key => $value) {
            ${$key} = $value;
        }
        // Define the LIMIT if it does not exist, unless specific posts are requested
        if (!isset($limit) && !isset($paramset['id']) && !isset($paramset['slug'])) {
            $limit = Options::get('pagination') ? (int) Options::get('pagination') : 5;
        } elseif (!isset($limit)) {
            $selected_posts = 0;
            if (isset($paramset['id'])) {
                $selected_posts += count(Utils::single_array($paramset['id']));
            }
            if (isset($paramset['slug'])) {
                $selected_posts += count(Utils::single_array($paramset['slug']));
            }
            $limit = $selected_posts > 0 ? $selected_posts : '';
        }
        // Calculate the OFFSET based on the page number
        if (isset($page) && is_numeric($page) && !isset($paramset['offset'])) {
            $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];
        }
        /**
         * Turn the requested fields into a comma-separated SELECT field clause
         */
        $select = implode(', ', $select_ary);
        /**
         * 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)) {
            $select = "COUNT({$count})";
            $fetch_fn = 'get_value';
            $orderby = '';
            $groupby = '';
            $having = '';
        }
        // If the month counts are requested, replaced the select clause
        if (isset($paramset['month_cts'])) {
            if (isset($paramset['tag']) || isset($paramset['tag_slug'])) {
                $select = 'MONTH(FROM_UNIXTIME(pubdate)) AS month, YEAR(FROM_UNIXTIME(pubdate)) AS year, COUNT(DISTINCT {posts}.id) AS ct';
            } else {
                $select = 'MONTH(FROM_UNIXTIME(pubdate)) AS month, YEAR(FROM_UNIXTIME(pubdate)) AS year, COUNT(*) AS ct';
            }
            $groupby = 'year, month';
            $orderby = 'year, month';
        }
        // Remove the LIMIT if 'nolimit' or 'month_cts' is set
        // Doing this first should allow OFFSET to work
        if (isset($nolimit) || isset($paramset['month_cts'])) {
            $limit = '';
        }
        // Define the LIMIT and add the OFFSET if it exists
        if (!empty($limit)) {
            $limit = " LIMIT {$limit}";
            if (isset($offset)) {
                $limit .= " OFFSET {$offset}";
            }
        } else {
            $limit = '';
        }
        /* All SQL parts are constructed, on to real business! */
        /**
         * Build the final SQL statement
         */
        $query = '
			SELECT DISTINCT ' . $select . '
			FROM {posts} ' . implode(' ', $joins);
        if (count($wheres) > 0) {
            $query .= ' WHERE (' . implode(" \nOR\n ", $wheres) . ')';
            $query .= $master_perm_where == '' ? '' : ' AND (' . $master_perm_where . ')';
        } elseif ($master_perm_where != '') {
            $query .= ' WHERE (' . $master_perm_where . ')';
        }
        $query .= !isset($groupby) || $groupby == '' ? '' : ' GROUP BY ' . $groupby;
        $query .= !isset($having) || $having == '' ? '' : ' HAVING ' . $having;
        $query .= ($orderby == '' ? '' : ' ORDER BY ' . $orderby) . $limit;
        /**
         * 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);
        /**
         * Execute the SQL statement using the PDO extension
         */
        DB::set_fetch_mode(PDO::FETCH_CLASS);
        DB::set_fetch_class('Post');
        $results = DB::$fetch_fn($query, $params, 'Post');
        //		Utils::debug( $paramarray, $fetch_fn, $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
ファイル: usergroups.php プロジェクト: wwxgitcat/habari
 /**
  * Returns a group or grops based on supplied parameters.
  * @todo This class should cache query results!
  *
  * @param array $paramarray An associated array of parameters, or a querystring
  * @return array An array of UserGroup objects, or a single UserGroup object, depending on request
  */
 public static function get($paramarray = array())
 {
     $params = array();
     $fns = array('get_results', 'get_row', 'get_value');
     $select = '';
     // what to select -- by default, everything
     foreach (UserGroup::default_fields() as $field => $value) {
         $select .= '' == $select ? "{groups}.{$field}" : ", {groups}.{$field}";
     }
     // defaults
     $orderby = 'id ASC';
     $nolimit = true;
     // Put incoming parameters into the local scope
     $paramarray = Utils::get_params($paramarray);
     // 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());
     }
     $wheres = array();
     $join = '';
     if (isset($paramarray['where']) && is_string($paramarray['where'])) {
         $wheres[] = $paramarray['where'];
     } else {
         foreach ($wheresets as $paramset) {
             // safety mechanism to prevent empty queries
             $where = array();
             $paramset = array_merge((array) $paramarray, (array) $paramset);
             $default_fields = UserGroup::default_fields();
             foreach (UserGroup::default_fields() as $field => $scrap) {
                 if (!isset($paramset[$field])) {
                     continue;
                 }
                 switch ($field) {
                     case 'id':
                         if (!is_numeric($paramset[$field])) {
                             continue;
                         }
                     default:
                         $where[] = "{$field} = ?";
                         $params[] = $paramset[$field];
                 }
             }
             if (count($where) > 0) {
                 $wheres[] = ' (' . implode(' AND ', $where) . ') ';
             }
         }
     }
     // Get any full-query parameters
     $possible = array('fetch_fn', 'count', 'nolimit', 'limit', 'offset');
     foreach ($possible as $varname) {
         if (isset($paramarray[$varname])) {
             ${$varname} = $paramarray[$varname];
         }
     }
     if (isset($fetch_fn)) {
         if (!in_array($fetch_fn, $fns)) {
             $fetch_fn = $fns[0];
         }
     } else {
         $fetch_fn = $fns[0];
     }
     // is a count being request?
     if (isset($count)) {
         $select = "COUNT({$count})";
         $fetch_fn = 'get_value';
         $orderby = '';
     }
     // Are we asking for a single UserGroup or possibly multiple UserGroups
     $single = false;
     if (isset($limit)) {
         $single = $limit == 1;
         $limit = " LIMIT {$limit}";
         if (isset($offset)) {
             $limit .= " OFFSET {$offset}";
         }
     }
     if (isset($nolimit)) {
         $limit = '';
     }
     $query = ' SELECT ' . $select . ' FROM {groups} ' . $join;
     if (count($wheres) > 0) {
         $query .= ' WHERE ' . implode(" \nOR\n ", $wheres);
     }
     $query .= ($orderby == '' ? '' : ' ORDER BY ' . $orderby) . $limit;
     DB::set_fetch_mode(PDO::FETCH_CLASS);
     $results = DB::$fetch_fn($query, $params, 'UserGroup');
     if ('get_results' != $fetch_fn) {
         // return the results
         return $results;
     } elseif (is_array($results)) {
         $c = __CLASS__;
         $return_value = new $c($results);
         $return_value->get_param_cache = $paramarray;
         return $return_value;
     }
 }
コード例 #3
0
ファイル: comments.php プロジェクト: psaintlaurent/Habari
    /**
     * function get
     * Returns requested comments
     * @param array An associated array of parameters, or a querystring
     * @return array An array of Comment objects, one for each query result
     *
     * <code>
     * $comments = comments::get( array ( "author" => "skippy" ) );
     * $comments = comments::get( array ( "slug" => "first-post", "status" => "1", "orderby" => "date ASC" ) );
     * </code>
     *
     **/
    public static function get($paramarray = array())
    {
        $params = array();
        $fns = array('get_results', 'get_row', 'get_value');
        $select = '';
        // what to select -- by default, everything
        foreach (Comment::default_fields() as $field => $value) {
            $select .= '' == $select ? "{comments}.{$field}" : ", {comments}.{$field}";
        }
        // defaults
        $orderby = 'date DESC';
        $limit = Options::get('pagination');
        // Put incoming parameters into the local scope
        $paramarray = Utils::get_params($paramarray);
        // 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());
        }
        $wheres = array();
        $joins = array();
        if (isset($paramarray['where']) && is_string($paramarray['where'])) {
            $wheres[] = $paramarray['where'];
        } else {
            foreach ($wheresets as $paramset) {
                // safety mechanism to prevent empty queries
                $where = array('1=1');
                $paramset = array_merge((array) $paramarray, (array) $paramset);
                if (isset($paramset['id']) && (is_numeric($paramset['id']) || is_array($paramset['id']))) {
                    if (is_numeric($paramset['id'])) {
                        $where[] = "{comments}.id= ?";
                        $params[] = $paramset['id'];
                    } else {
                        if (is_array($paramset['id']) && !empty($paramset['id'])) {
                            $id_list = implode(',', $paramset['id']);
                            // Clean up the id list - remove all non-numeric or comma information
                            $id_list = preg_replace("/[^0-9,]/", "", $id_list);
                            // You're paranoid, ringmaster! :P
                            $limit = count($paramset['id']);
                            $where[] = '{comments}.id IN (' . addslashes($id_list) . ')';
                        }
                    }
                }
                if (isset($paramset['status']) && FALSE !== $paramset['status']) {
                    if (is_array($paramset['status'])) {
                        $paramset['status'] = array_diff($paramset['status'], array('any'));
                        array_walk($paramset['status'], create_function('&$a,$b', '$a = Comment::status( $a );'));
                        $where[] = "{comments}.status IN (" . Utils::placeholder_string(count($paramset['status'])) . ")";
                        $params = array_merge($params, $paramset['status']);
                    } else {
                        $where[] = "{comments}.status= ?";
                        $params[] = Comment::status($paramset['status']);
                    }
                }
                if (isset($paramset['type']) && FALSE !== $paramset['type']) {
                    if (is_array($paramset['type'])) {
                        $paramset['type'] = array_diff($paramset['type'], array('any'));
                        array_walk($paramset['type'], create_function('&$a,$b', '$a = Comment::type( $a );'));
                        $where[] = "type IN (" . Utils::placeholder_string(count($paramset['type'])) . ")";
                        $params = array_merge($params, $paramset['type']);
                    } else {
                        $where[] = "type= ?";
                        $params[] = Comment::type($paramset['type']);
                    }
                }
                if (isset($paramset['name'])) {
                    $where[] = "name= ?";
                    $params[] = $paramset['name'];
                }
                if (isset($paramset['email'])) {
                    $where[] = "email= ?";
                    $params[] = $paramset['email'];
                }
                if (isset($paramset['url'])) {
                    $where[] = "url= ?";
                    $params[] = $paramset['url'];
                }
                if (isset($paramset['post_id'])) {
                    $where[] = "{comments}.post_id= ?";
                    $params[] = $paramset['post_id'];
                }
                if (isset($paramset['ip'])) {
                    $where[] = "ip= ?";
                    $params[] = $paramset['ip'];
                }
                /* do searching */
                if (isset($paramset['post_author'])) {
                    $joins['posts'] = ' INNER JOIN {posts} ON {comments}.post_id = {posts}.id';
                    if (is_array($paramset['post_author'])) {
                        $where[] = "{posts}.user_id IN (" . implode(',', array_fill(0, count($paramset['post_author']), '?')) . ")";
                        $params = array_merge($params, $paramset['post_author']);
                    } else {
                        $where[] = '{posts}.user_id = ?';
                        $params[] = (string) $paramset['post_author'];
                    }
                }
                if (isset($paramset['criteria'])) {
                    if (isset($paramset['criteria_fields'])) {
                        // Support 'criteria_fields' => 'author,ip' rather than 'criteria_fields' => array( 'author', 'ip' )
                        if (!is_array($paramset['criteria_fields']) && is_string($paramset['criteria_fields'])) {
                            $paramset['criteria_fields'] = explode(',', $paramset['criteria_fields']);
                        }
                    } else {
                        $paramset['criteria_fields'] = array('content');
                    }
                    $paramset['criteria_fields'] = array_unique($paramset['criteria_fields']);
                    preg_match_all('/(?<=")([\\p{L}\\p{N}]+[^"]*)(?=")|([\\p{L}\\p{N}]+)/u', $paramset['criteria'], $matches);
                    $where_search = array();
                    foreach ($matches[0] as $word) {
                        foreach ($paramset['criteria_fields'] as $criteria_field) {
                            $where_search[] .= "({comments}.{$criteria_field} LIKE CONCAT('%',?,'%'))";
                            $params[] = $word;
                        }
                    }
                    if (count($where_search) > 0) {
                        $where[] = '(' . implode(" \nOR\n ", $where_search) . ')';
                    }
                }
                /*
                 * 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
                 * @todo Ensure that the value passed in is valid to insert into a SQL date (ie '04' and not '4')
                 */
                if (isset($paramset['day'])) {
                    /* Got the full date */
                    $where[] = 'date BETWEEN ? AND ?';
                    $startDate = sprintf('%d-%02d-%02d', $paramset['year'], $paramset['month'], $paramset['day']);
                    $startDate = HabariDateTime::date_create($startDate);
                    $params[] = $startDate->sql;
                    $params[] = $startDate->modify('+1 day')->sql;
                } elseif (isset($paramset['month'])) {
                    $where[] = 'date BETWEEN ? AND ?';
                    $startDate = sprintf('%d-%02d-%02d', $paramset['year'], $paramset['month'], 1);
                    $startDate = HabariDateTime::date_create($startDate);
                    $params[] = $startDate->sql;
                    $params[] = $startDate->modify('+1 month')->sql;
                } elseif (isset($paramset['year'])) {
                    $where[] = 'date BETWEEN ? AND ?';
                    $startDate = sprintf('%d-%02d-%02d', $paramset['year'], 1, 1);
                    $startDate = HabariDateTime::date_create($startDate);
                    $params[] = $startDate->sql;
                    $params[] = $startDate->modify('+1 year')->sql;
                }
                // Concatenate the WHERE clauses
                if (count($where) > 0) {
                    $wheres[] = ' (' . implode(' AND ', $where) . ') ';
                }
            }
        }
        // Only show comments to which the current user has permission to read the associated post
        if (isset($paramset['ignore_permissions'])) {
            $master_perm_where = '';
        } else {
            // This set of wheres will be used to generate a list of comment_ids that this user can read
            $perm_where = array();
            $perm_where_denied = array();
            $params_where = array();
            $where = array();
            // every condition here will require a join with the posts table
            $joins['posts'] = 'INNER JOIN {posts} ON {comments}.post_id={posts}.id';
            // 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 his own posts, let him
            if (User::identify()->can('own_posts', 'read')) {
                $perm_where['own_posts_id'] = '{posts}.user_id = ?';
                $params_where[] = User::identify()->id;
            }
            // If a user can read any post type, let him
            if (User::identify()->can('post_any', 'read')) {
                $perm_where = array('post_any' => '(1=1)');
                $params_where = array();
            } else {
                // 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[] = '{posts}.content_type IN (' . implode(',', $permitted_post_types) . ')';
                }
                // If a user can read posts with specific tokens, let him see comments on those posts
                if (count($read_tokens) > 0) {
                    $joins['post_tokens__allowed'] = ' LEFT JOIN {post_tokens} pt_allowed ON {posts}.id= pt_allowed.post_id AND pt_allowed.token_id IN (' . implode(',', $read_tokens) . ')';
                    $perm_where['perms_join_null'] = 'pt_allowed.post_id IS NOT NULL';
                }
            }
            // If a user is denied access to all posts, do so
            if (User::identify()->cannot('post_any')) {
                $perm_where_denied = array('(0=1)');
            } 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[] = '{posts}.content_type NOT IN (' . implode(',', $denied_post_types) . ')';
                }
            }
            // If there are granted permissions to check, add them to the where clause
            if (count($perm_where) == 0 && !isset($joins['post_tokens__allowed'])) {
                // You have no grants.  You get no comments.
                $where['perms_granted'] = '(0=1)';
            } elseif (count($perm_where) > 0) {
                $where['perms_granted'] = '
					(' . implode(' OR ', $perm_where) . ')
				';
                $params = array_merge($params, $params_where);
            }
            if (count($deny_tokens) > 0) {
                $joins['post_tokens__denied'] = ' LEFT JOIN {post_tokens} pt_denied ON {posts}.id= pt_denied.post_id AND pt_denied.token_id IN (' . implode(',', $deny_tokens) . ')';
                $perm_where_denied['perms_join_null'] = 'pt_denied.post_id IS NULL';
            }
            // If there are denied permissions to check, add them to the where clause
            if (count($perm_where_denied) > 0) {
                $where['perms_denied'] = '
					(' . implode(' AND ', $perm_where_denied) . ')
				';
            }
            $master_perm_where = implode(' AND ', $where);
        }
        // Get any full-query parameters
        $possible = array('page', 'fetch_fn', 'count', 'month_cts', 'nolimit', 'limit', 'offset', 'orderby');
        foreach ($possible as $varname) {
            if (isset($paramarray[$varname])) {
                ${$varname} = $paramarray[$varname];
            }
        }
        if (isset($page) && is_numeric($page)) {
            $offset = (intval($page) - 1) * intval($limit);
        }
        if (isset($fetch_fn)) {
            if (!in_array($fetch_fn, $fns)) {
                $fetch_fn = $fns[0];
            }
        } else {
            $fetch_fn = $fns[0];
        }
        // is a count being request?
        if (isset($count)) {
            $select = "COUNT( 1 )";
            $fetch_fn = 'get_value';
            $orderby = '';
        }
        // is a count of comments by month being requested?
        $groupby = '';
        if (isset($month_cts)) {
            $select = 'MONTH(FROM_UNIXTIME(date)) AS month, YEAR(FROM_UNIXTIME(date)) AS year, COUNT({comments}.id) AS ct';
            $groupby = 'year, month';
            $orderby = 'year, month';
        }
        if (isset($limit)) {
            $limit = " LIMIT {$limit}";
            if (isset($offset)) {
                $limit .= " OFFSET {$offset}";
            }
        }
        if (isset($nolimit) || isset($month_cts)) {
            $limit = '';
        }
        // Build the final SQL statement
        $query = '
			SELECT DISTINCT ' . $select . ' FROM {comments} ' . implode(' ', $joins);
        if (count($wheres) > 0) {
            $query .= ' WHERE (' . implode(" \nOR\n ", $wheres) . ')';
            $query .= $master_perm_where == '' ? '' : ' AND (' . $master_perm_where . ')';
        } elseif ($master_perm_where != '') {
            $query .= ' WHERE (' . $master_perm_where . ')';
        }
        $query .= $groupby == '' ? '' : ' GROUP BY ' . $groupby;
        $query .= ($orderby == '' ? '' : ' ORDER BY ' . $orderby) . $limit;
        //Utils::debug( $query, $params );
        DB::set_fetch_mode(PDO::FETCH_CLASS);
        DB::set_fetch_class('Comment');
        $results = DB::$fetch_fn($query, $params, 'Comment');
        if ('get_results' != $fetch_fn) {
            // return the results
            return $results;
        } elseif (is_array($results)) {
            $c = __CLASS__;
            $return_value = new $c($results);
            $return_value->get_param_cache = $paramarray;
            return $return_value;
        }
    }
コード例 #4
0
ファイル: eventlog.php プロジェクト: wwxgitcat/habari
    /**
     * 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 $paramarry An associated array of parameters, or a querystring
     * @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 = '';
        // Put incoming parameters into the local scope
        $paramarray = Utils::get_params($paramarray);
        $select_fields = LogEntry::default_fields();
        if (!isset($paramarray['return_data'])) {
            unset($select_fields['data']);
        }
        foreach ($select_fields as $field => $value) {
            $select .= '' == $select ? "{log}.{$field}" : ", {log}.{$field}";
        }
        // Default parameters.
        $orderby = 'ORDER BY timestamp DESC, id DESC';
        $limit = Options::get('pagination');
        // Get any full-query parameters
        $possible = array('orderby', 'fetch_fn', 'count', 'month_cts', 'nolimit', 'index', 'limit', 'offset');
        foreach ($possible as $varname) {
            if (isset($paramarray[$varname])) {
                ${$varname} = $paramarray[$varname];
            }
        }
        foreach ($paramarray as $key => $value) {
            if ('orderby' == $key) {
                $orderby = ' ORDER BY ' . $value;
                continue;
            }
        }
        // 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());
        }
        $wheres = array();
        $join = '';
        if (isset($paramarray['where']) && is_string($paramarray['where'])) {
            $wheres[] = $paramarray['where'];
        } else {
            foreach ($wheresets as $paramset) {
                // Safety mechanism to prevent empty queries
                $where = array('1=1');
                $paramset = array_merge((array) $paramarray, (array) $paramset);
                if (isset($paramset['id']) && is_numeric($paramset['id'])) {
                    $where[] = "id= ?";
                    $params[] = $paramset['id'];
                }
                if (isset($paramset['user_id'])) {
                    $where[] = "user_id= ?";
                    $params[] = $paramset['user_id'];
                }
                if (isset($paramset['severity']) && 'any' != LogEntry::severity_name($paramset['severity'])) {
                    $where[] = "severity_id= ?";
                    $params[] = LogEntry::severity($paramset['severity']);
                }
                if (isset($paramset['type_id'])) {
                    if (is_array($paramset['type_id'])) {
                        $types = array_filter($paramset['type_id'], 'is_numeric');
                        if (count($types)) {
                            $where[] = 'type_id IN (' . implode(',', $types) . ')';
                        }
                    } else {
                        $where[] = 'type_id = ?';
                        $params[] = $paramset['type_id'];
                    }
                }
                if (isset($paramset['module'])) {
                    if (!is_array($paramset['module'])) {
                        $paramset['module'] = array($paramset['module']);
                    }
                    $where[] = 'type_id IN ( SELECT DISTINCT id FROM {log_types} WHERE module IN ( ' . implode(', ', array_fill(0, count($paramset['module']), '?')) . ' ) )';
                    $params = array_merge($params, $paramset['module']);
                }
                if (isset($paramset['type'])) {
                    if (!is_array($paramset['type'])) {
                        $paramset['type'] = array($paramset['type']);
                    }
                    $where[] = 'type_id IN ( SELECT DISTINCT id FROM {log_types} WHERE type IN ( ' . implode(', ', array_fill(0, count($paramset['type']), '?')) . ' ) )';
                    $params = array_merge($params, $paramset['type']);
                }
                if (isset($paramset['ip'])) {
                    $where[] = 'ip = ?';
                    $params[] = $paramset['ip'];
                }
                /* do searching */
                if (isset($paramset['criteria'])) {
                    preg_match_all('/(?<=")(\\w[^"]*)(?=")|([:\\w]+)/u', $paramset['criteria'], $matches);
                    foreach ($matches[0] as $word) {
                        if (preg_match('%^id:(\\d+)$%i', $word, $special_crit)) {
                            $where[] .= '(id = ?)';
                            $params[] = $special_crit[1];
                        } else {
                            $where[] .= "( LOWER( message ) LIKE ? )";
                            $params[] = '%' . 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'])) {
                    $where[] = 'timestamp BETWEEN ? AND ?';
                    $start_date = sprintf('%d-%02d-%02d', $paramset['year'], $paramset['month'], $paramset['day']);
                    $start_date = HabariDateTime::date_create($start_date);
                    $params[] = $start_date->sql;
                    $params[] = $start_date->modify('+1 day')->sql;
                    //$params[] = date( 'Y-m-d H:i:s', mktime( 0, 0, 0, $paramset['month'], $paramset['day'], $paramset['year'] ) );
                    //$params[] = date( 'Y-m-d H:i:s', mktime( 23, 59, 59, $paramset['month'], $paramset['day'], $paramset['year'] ) );
                } elseif (isset($paramset['month'])) {
                    $where[] = 'timestamp BETWEEN ? AND ?';
                    $start_date = sprintf('%d-%02d-%02d', $paramset['year'], $paramset['month'], 1);
                    $start_date = HabariDateTime::date_create($start_date);
                    $params[] = $start_date->sql;
                    $params[] = $start_date->modify('+1 month')->sql;
                    //$params[] = date( 'Y-m-d H:i:s', mktime( 0, 0, 0, $paramset['month'], 1, $paramset['year'] ) );
                    //$params[] = date( 'Y-m-d H:i:s', mktime( 23, 59, 59, $paramset['month'] + 1, 0, $paramset['year'] ) );
                } elseif (isset($paramset['year'])) {
                    $where[] = 'timestamp BETWEEN ? AND ?';
                    $start_date = sprintf('%d-%02d-%02d', $paramset['year'], 1, 1);
                    $start_date = HabariDateTime::date_create($start_date);
                    $params[] = $start_date->sql;
                    $params[] = $start_date->modify('+1 year')->sql;
                    //$params[] = date( 'Y-m-d H:i:s', mktime( 0, 0, 0, 1, 1, $paramset['year'] ) );
                    //$params[] = date( 'Y-m-d H:i:s', mktime( 0, 0, -1, 1, 1, $paramset['year'] + 1 ) );
                }
                $wheres[] = ' (' . implode(' AND ', $where) . ') ';
            }
        }
        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)) {
            $select = "COUNT({$count})";
            $fetch_fn = 'get_value';
            $orderby = '';
        }
        if (isset($limit)) {
            $limit = " LIMIT {$limit}";
            if (isset($offset)) {
                $limit .= " OFFSET {$offset}";
            }
        }
        // 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?
            $select = 'MONTH(FROM_UNIXTIME(timestamp)) AS month, YEAR(FROM_UNIXTIME(timestamp)) AS year, COUNT(*) AS ct';
            $groupby = 'year, month';
            $orderby = ' ORDER BY year, month';
        }
        if (isset($nolimit) || isset($month_cts)) {
            $limit = '';
        }
        $query = '
			SELECT ' . $select . '
			FROM {log} ' . $join;
        if (count($wheres) > 0) {
            $query .= ' WHERE ' . implode(" \nOR\n ", $wheres);
        }
        $query .= !isset($groupby) || $groupby == '' ? '' : ' GROUP BY ' . $groupby;
        $query .= $orderby . $limit;
        // Utils::debug( $paramarray, $fetch_fn, $query, $params );
        DB::set_fetch_mode(PDO::FETCH_CLASS);
        DB::set_fetch_class('LogEntry');
        $results = DB::$fetch_fn($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;
        }
    }
コード例 #5
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;
     }
 }
コード例 #6
0
ファイル: users.php プロジェクト: rynodivino/system
	/**
	 * Returns a user or users based on supplied parameters.
	 * @todo This class should cache query results!
	 *
	 * @param array $paramarray An associated array of parameters, or a querystring
	 * @return array An array of User objects, or a single User object, depending on request
	 */
	public static function get( $paramarray = array() )
	{
		$params = array();
		$fns = array( 'get_results', 'get_row', 'get_value' );
		$select = '';
		// what to select -- by default, everything
		foreach ( User::default_fields() as $field => $value ) {
			$select .= ( '' == $select )
				? "{users}.$field"
				: ", {users}.$field";
		}
		// defaults
		$orderby = 'id ASC';
		$nolimit = true;

		// Put incoming parameters into the local scope
		$paramarray = Utils::get_params( $paramarray );

		// 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() );
		}

		$wheres = array();
		$join = '';
		if ( isset( $paramarray['where'] ) && is_string( $paramarray['where'] ) ) {
			$wheres[] = $paramarray['where'];
		}
		else {
			foreach ( $wheresets as $paramset ) {
				// safety mechanism to prevent empty queries
				$where = array();
				$paramset = array_merge( (array) $paramarray, (array) $paramset );

				$default_fields = User::default_fields();
				foreach ( User::default_fields() as $field => $scrap ) {
					if ( !isset( $paramset[$field] ) ) {
						continue;
					}
					switch ( $field ) {
						case 'id':
							if ( !is_numeric( $paramset[$field] ) ) {
								continue;
							}
						default:
							$where[] = "{$field} = ?";
							$params[] = $paramset[$field];
					}
				}

				if ( isset( $paramset['info'] ) && is_array( $paramset['info'] ) ) {
					$join .= 'INNER JOIN {userinfo} ON {users}.id = {userinfo}.user_id';
					foreach ( $paramset['info'] as $info_name => $info_value ) {
						$where[] = '{userinfo}.name = ? AND {userinfo}.value = ?';
						$params[] = $info_name;
						$params[] = $info_value;
					}
				}

				if ( isset( $paramset['criteria'] ) ) {
					if ( isset( $paramset['criteria_fields'] ) ) {
						// Support 'criteria_fields' => 'author,ip' rather than 'criteria_fields' => array( 'author', 'ip' )
						if ( !is_array( $paramset['criteria_fields'] ) && is_string( $paramset['criteria_fields'] ) ) {
							$paramset['criteria_fields'] = explode( ',', $paramset['criteria_fields'] );
						}
					}
					else {
						$paramset['criteria_fields'] = array( 'username' );
					}
					$paramset['criteria_fields'] = array_unique( $paramset['criteria_fields'] );

					// 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 );
					$where_search = array();
					foreach ( $matches[0] as $word ) {
						foreach ( $paramset['criteria_fields'] as $criteria_field ) {
							$where_search[] .= "( LOWER( {users}.$criteria_field ) LIKE ? )";
							$params[] = '%' . MultiByte::strtolower( $word ) . '%';
						}
					}
					if ( count( $where_search ) > 0 ) {
						$where[] = '(' . implode( " \nOR\n ", $where_search ).')';
					}
				}

				if ( count( $where ) > 0 ) {
					$wheres[] = ' (' . implode( ' AND ', $where ) . ') ';
				}
			}
		}

		// Get any full-query parameters
		$possible = array( 'fetch_fn', 'count', 'nolimit', 'limit', 'offset' );
		foreach ( $possible as $varname ) {
			if ( isset( $paramarray[$varname] ) ) {
				$$varname = $paramarray[$varname];
			}
		}

		if ( isset( $fetch_fn ) ) {
			if ( ! in_array( $fetch_fn, $fns ) ) {
				$fetch_fn = $fns[0];
			}
		}
		else {
			$fetch_fn = $fns[0];
		}

		// is a count being request?
		if ( isset( $count ) ) {
			$select = "COUNT($count)";
			$fetch_fn = 'get_value';
			$orderby = '';
		}
		if ( isset( $limit ) ) {
			unset( $nolimit );
			$limit = " LIMIT $limit";
			if ( isset( $offset ) ) {
				$limit .= " OFFSET $offset";
			}
		}
		if ( isset( $nolimit ) ) {
			$limit = '';
		}

		$query = '
			SELECT ' . $select
			. ' FROM {users} '
			. $join;

		if ( count( $wheres ) > 0 ) {
			$query .= ' WHERE ' . implode( " \nOR\n ", $wheres );
		}
		$query .= ( ( $orderby == '' ) ? '' : ' ORDER BY ' . $orderby ) . $limit;
		//Utils::debug($paramarray, $fetch_fn, $query, $params);

		DB::set_fetch_mode( PDO::FETCH_CLASS );
		DB::set_fetch_class( 'User' );
		$results = DB::$fetch_fn( $query, $params, 'User' );

		if ( 'get_results' != $fetch_fn ) {
			// return the results
			return $results;
		}
		elseif ( is_array( $results ) ) {
			$c = __CLASS__;
			$return_value = new $c( $results );
			$return_value->get_param_cache = $paramarray;
			return $return_value;
		}
	}
コード例 #7
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;
     }
 }
コード例 #8
0
ファイル: posts.php プロジェクト: rynodivino/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
	 * - 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
	 * - 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 array An array of Post objects, or a single post object, depending on request
	 */
	public static function get( $paramarray = array() )
	{
		static $presets;

		// 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 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']);
			}
			if(isset($presets[$paramarray['preset']])) {
				$preset = Plugins::filter('posts_get_update_preset', $presets[$paramarray['preset']], $paramarray['preset'], $paramarray);
				$paramarray = array_merge($paramarray, $preset);
			}
		}

		// 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
		foreach ( Post::default_fields() as $field => $value ) {
			$select_ary[$field] = "{posts}.$field AS $field";
			$select_distinct[$field] = "{posts}.$field";
		}

		// Default parameters
		$orderby = 'pubdate DESC';

		// 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 */

		$wheres = array();
		$joins = array();

		// If the request as a textual WHERE clause, skip the processing of the $wheresets since it's empty
		if ( isset( $paramarray['where'] ) && is_string( $paramarray['where'] ) ) {
			$wheres[] = $paramarray['where'];
		}
		else {
			foreach ( $wheresets as $paramset ) {
				// Safety mechanism to prevent empty queries
				$where = array();
				$paramset = array_merge( (array) $paramarray, (array) $paramset );
				// $nots= preg_grep( '%^not:(\w+)$%iu', (array) $paramset );

				if ( isset( $paramset['id'] ) ) {
					if ( is_array( $paramset['id'] ) ) {
						array_walk( $paramset['id'], create_function( '&$a,$b', '$a = intval( $a );' ) );
						$where[] = "{posts}.id IN (" . implode( ',', array_fill( 0, count( $paramset['id'] ), '?' ) ) . ")";
						$params = array_merge( $params, $paramset['id'] );
					}
					else {
						$where[] = "{posts}.id = ?";
						$params[] = (int) $paramset['id'];
					}
				}
				if ( isset( $paramset['not:id'] ) ) {
					if ( is_array( $paramset['not:id'] ) ) {
						array_walk( $paramset['not:id'], create_function( '&$a,$b', '$a = intval( $a );' ) );
						$where[] = "{posts}.id NOT IN (" . implode( ',', array_fill( 0, count( $paramset['not:id'] ), '?' ) ) . ")";
						$params = array_merge( $params, $paramset['not:id'] );
					}
					else {
						$where[] = "{posts}.id != ?";
						$params[] = (int) $paramset['not:id'];
					}
				}
				if ( isset( $paramset['status'] ) && ( $paramset['status'] != 'any' ) && ( 0 !== $paramset['status'] ) ) {
					if ( is_array( $paramset['status'] ) ) {
						// remove 'any' from the list if we have an array
						$paramset['status'] = array_diff( $paramset['status'], array( 'any' ) );
						array_walk( $paramset['status'], create_function( '&$a,$b', '$a = Post::status( $a );' ) );
						$where[] = "{posts}.status IN (" . implode( ',', array_fill( 0, count( $paramset['status'] ), '?' ) ) . ")";
						$params = array_merge( $params, $paramset['status'] );
					}
					else {
						$where[] = "{posts}.status = ?";
						$params[] = (int) Post::status( $paramset['status'] );
					}
				}
				if ( isset( $paramset['content_type'] ) && ( $paramset['content_type'] != 'any' ) && ( 0 !== $paramset['content_type'] ) ) {
					if ( is_array( $paramset['content_type'] ) ) {
						// remove 'any' from the list if we have an array
						$paramset['content_type'] = array_diff( $paramset['content_type'], array( 'any' ) );
						array_walk( $paramset['content_type'], create_function( '&$a,$b', '$a = Post::type( $a );' ) );
						$where[] = "{posts}.content_type IN (" . implode( ',', array_fill( 0, count( $paramset['content_type'] ), '?' ) ) . ")";
						$params = array_merge( $params, $paramset['content_type'] );
					}
					else {
						$where[] = "{posts}.content_type = ?";
						$params[] = (int) Post::type( $paramset['content_type'] );
					}
				}
				if ( isset( $paramset['not:content_type'] ) ) {
					if ( is_array( $paramset['not:content_type'] ) ) {
						array_walk( $paramset['not:content_type'], create_function( '&$a,$b', '$a = Post::type( $a );' ) );
						$where[] = "{posts}.content_type NOT IN (" . implode( ',', array_fill( 0, count( $paramset['not:content_type'] ), '?' ) ) . ")";
						$params = array_merge( $params, $paramset['not:content_type'] );
					}
					else {
						$where[] = "{posts}.content_type != ?";
						$params[] = (int) Post::type( $paramset['not:content_type'] );
					}
				}
				if ( isset( $paramset['slug'] ) ) {
					if ( is_array( $paramset['slug'] ) ) {
						$where[] = "{posts}.slug IN (" . implode( ',', array_fill( 0, count( $paramset['slug'] ), '?' ) ) . ")";
						$params = array_merge( $params, $paramset['slug'] );
					}
					else {
						$where[] = "{posts}.slug = ?";
						$params[] = (string) $paramset['slug'];
					}
				}
				
				if ( isset( $paramset['not:slug'] ) ) {
					if ( is_array( $paramset['not:slug'] ) ) {
						$where[] = "{posts}.slug NOT IN (" . implode( ',', array_fill( 0, count( $paramset['not:slug'] ), '?' ) ) . ")";
						$params = array_merge( $params, $paramset['not:slug'] );
					}
					else {
						$where[] = "{posts}.slug != ?";
						$params[] = (string) $paramset['not:slug'];
					}
				}
				
				if ( isset( $paramset['user_id'] ) && 0 !== $paramset['user_id'] ) {
					if ( is_array( $paramset['user_id'] ) ) {
						array_walk( $paramset['user_id'], create_function( '&$a,$b', '$a = intval( $a );' ) );
						$where[] = "{posts}.user_id IN (" . implode( ',', array_fill( 0, count( $paramset['user_id'] ), '?' ) ) . ")";
						$params = array_merge( $params, $paramset['user_id'] );
					}
					else {
						$where[] = "{posts}.user_id = ?";
						$params[] = (int) $paramset['user_id'];
					}

				}

				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' );
					
					$all = array();
					$any = array();
					$not = array();
					
					if ( isset( $paramset['vocabulary']['all'] ) ) {
						$all = $paramset['vocabulary']['all'];
					}
					
					if ( isset( $paramset['vocabulary']['any'] ) ) {
						$any = $paramset['vocabulary']['any'];
					}
					
					if ( isset( $paramset['vocabulary']['not'] ) ) {
						$not = $paramset['vocabulary']['not'];
					}
					
					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;
							}
							
							$joins['term2post_posts'] = ' JOIN {object_terms} ON {posts}.id = {object_terms}.object_id';
							$joins['terms_term2post'] = ' JOIN {terms} ON {object_terms}.term_id = {terms}.id';
							$joins['terms_vocabulary'] = ' JOIN {vocabularies} ON {terms}.vocabulary_id = {vocabularies}.id';
							
							$where[] = '{vocabularies}.name = ? AND {terms}.' . $field . ' IN ( ' . Utils::placeholder_string( $terms ) . ' ) AND {object_terms}.object_type_id = ?';
							$params[] = $vocab;
							$params = array_merge( $params, $terms );
							$params[] = $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 );
						
					}
					
					foreach ( $any 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;
							}
							
							$joins['term2post_posts'] = ' JOIN {object_terms} ON {posts}.id = {object_terms}.object_id';
							$joins['terms_term2post'] = ' JOIN {terms} ON {object_terms}.term_id = {terms}.id';
							$joins['terms_vocabulary'] = ' JOIN {vocabularies} ON {terms}.vocabulary_id = {vocabularies}.id';
							
							$where[] = '{vocabularies}.name = ? AND {terms}.' . $field . ' IN ( ' . Utils::placeholder_string( $terms ) . ' ) AND {object_terms}.object_type_id = ?';
							$params[] = $vocab;
							$params = array_merge( $params, $terms );
							$params[] = $object_id;
							
						}
						
					}
					
					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;
							}
							
							$where[] = 'NOT EXISTS ( SELECT 1
								FROM {object_terms} 
								JOIN {terms} ON {terms}.id = {object_terms}.term_id 
								JOIN {vocabularies} ON {terms}.vocabulary_id = {vocabularies}.id  
								WHERE {terms}.' . $field . ' IN (' . Utils::placeholder_string( $terms ) . ')
								AND {object_terms}.object_id = {posts}.id 
								AND {object_terms}.object_type_id = ? 
								AND {vocabularies}.name = ?
							)';
							$params = array_merge( $params, array_values( $terms ) );
							$params[] = $object_id;
							$params[] = $vocab;
							
						}
						
					}
					
				}

				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 ) {
						$where[] .= "( LOWER( {posts}.title ) LIKE ? OR  LOWER( {posts}.content ) LIKE ?)";
						$params[] = '%' . MultiByte::strtolower( $word ) . '%';
						$params[] = '%' . MultiByte::strtolower( $word ) . '%';  // Not a typo (there are two ? in the above statement)
					}
				}

				if ( isset( $paramset['title'] ) ) {
					$where[] .= "LOWER( {posts}.title ) LIKE ?";
					$params[] = 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 ) {
						$where[] .= " LOWER( {posts}.title ) LIKE ? ";
						$params[] = '%' . MultiByte::strtolower( $word ) . '%';
					}
				}

				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++;
							$joins['info_' . $info_key] = " LEFT JOIN {postinfo} ipi{$pi_count} ON {posts}.id = ipi{$pi_count}.post_id AND ipi{$pi_count}.name = ? AND ipi{$pi_count}.value = ?";
							$join_params[] = $info_key;
							$join_params[] = $info_value;
							$where[] = "ipi{$pi_count}.name <> ''";

							$select_ary["info_{$info_key}_value"] = "ipi{$pi_count}.value AS info_{$info_key}_value";
							$select_distinct["info_{$info_key}_value"] = "info_{$info_key}_value";
						}
					}

				}

				if ( isset( $paramset['any:info'] ) ) {
					if ( Utils::is_traversable( $paramset['any:info'] ) ) {
						$pi_count = 0;
						$pi_where = array();
						foreach ( $paramset['any:info'] as $info_key => $info_value ) {
							$pi_count++;

							$join_params[] = $info_key;
							if ( is_array( $info_value ) ) {
								$joins['any_info_' . $info_key] = " LEFT JOIN {postinfo} aipi{$pi_count} ON {posts}.id = aipi{$pi_count}.post_id AND aipi{$pi_count}.name = ? AND aipi{$pi_count}.value IN (" .Utils::placeholder_string( count( $info_value ) ).")";
								$join_params = array_merge( $join_params, $info_value );
							}
							else {
								$joins['any_info_' . $info_key] = " LEFT JOIN {postinfo} aipi{$pi_count} ON {posts}.id = aipi{$pi_count}.post_id AND aipi{$pi_count}.name = ? AND aipi{$pi_count}.value = ?";
								$join_params[] = $info_value;
							}

							$pi_where[] = "aipi{$pi_count}.name <> ''";

							$select_ary["info_{$info_key}_value"] = "aipi{$pi_count}.value AS info_{$info_key}_value";
							$select_distinct["info_{$info_key}_value"] = "info_{$info_key}_value";
						}
						$where[] = '(' . implode( ' OR ', $pi_where ) . ')';
					}
				}

				if ( isset( $paramset['has:info'] ) ) {
					$the_ins = array();
					$has_info = Utils::single_array( $paramset['has:info'] );
					$pi_count = 0;
					$pi_where = array();
					foreach ( $has_info as $info_name ) {
						$pi_count++;
						$joins['has_info_' . $info_name] = " LEFT JOIN {postinfo} hipi{$pi_count} ON {posts}.id = hipi{$pi_count}.post_id AND hipi{$pi_count}.name = ?";
						$join_params[] = $info_name;
						$pi_where[] = "hipi{$pi_count}.name <> ''";

						$select_ary["info_{$info_name}_value"] = "hipi{$pi_count}.value AS info_{$info_name}_value";
						$select_distinct["info_{$info_name}_value"] = "info_{$info_name}_value";
					}
					$where[] = '(' . implode( ' OR ', $pi_where ) . ')';
				}

				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 ) ) {
						$the_ins = array();

						foreach ( $infos as $info_key => $info_value ) {

							$the_ins[] = ' ({postinfo}.name = ? AND {postinfo}.value = ? ) ';
							$params[] = $info_key;
							$params[] = $info_value;

						}

						$where[] = '
							{posts}.id NOT IN (
							SELECT post_id FROM {postinfo}
							WHERE ( ' . implode( ' OR ', $the_ins ) . ' )
							GROUP BY post_id
							HAVING COUNT(*) = ' . count( $infos ) . ' )
						';
						// see that hard-coded number? sqlite wets itself if we use a bound parameter... don't change that

					}

				}

				if ( isset( $paramset['not:any:info'] ) ) {

					if ( Utils::is_traversable( $paramset['not:any:info'] ) ) {

						foreach ( $paramset['not:any:info'] as $info_key => $info_value ) {

							$the_ins[] = ' ({postinfo}.name = ? AND {postinfo}.value = ? ) ';
							$params[] = $info_key;
							$params[] = $info_value;

						}

						$where[] = '
							{posts}.id NOT IN (
								SELECT post_id FROM {postinfo}
								WHERE ( ' . implode( ' OR ', $the_ins ) . ' )
							)
						';

					}

				}

				/**
				 * 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'] ) ) {
					$where[] = 'pubdate BETWEEN ? AND ?';
					$start_date = sprintf( '%d-%02d-%02d', $paramset['year'], $paramset['month'], $paramset['day'] );
					$start_date = HabariDateTime::date_create( $start_date );
					$params[] = $start_date->sql;
					$params[] = $start_date->modify( '+1 day -1 second' )->sql;
					//$params[] = date( 'Y-m-d H:i:s', mktime( 0, 0, 0, $paramset['month'], $paramset['day'], $paramset['year'] ) );
					//$params[] = date( 'Y-m-d H:i:s', mktime( 23, 59, 59, $paramset['month'], $paramset['day'], $paramset['year'] ) );
				}
				elseif ( isset( $paramset['month'] ) && isset( $paramset['year'] ) ) {
					$where[] = 'pubdate BETWEEN ? AND ?';
					$start_date = sprintf( '%d-%02d-%02d', $paramset['year'], $paramset['month'], 1 );
					$start_date = HabariDateTime::date_create( $start_date );
					$params[] = $start_date->sql;
					$params[] = $start_date->modify( '+1 month -1 second' )->sql;
					//$params[] = date( 'Y-m-d H:i:s', mktime( 0, 0, 0, $paramset['month'], 1, $paramset['year'] ) );
					//$params[] = date( 'Y-m-d H:i:s', mktime( 23, 59, 59, $paramset['month'] + 1, 0, $paramset['year'] ) );
				}
				elseif ( isset( $paramset['year'] ) ) {
					$where[] = 'pubdate BETWEEN ? AND ?';
					$start_date = sprintf( '%d-%02d-%02d', $paramset['year'], 1, 1 );
					$start_date = HabariDateTime::date_create( $start_date );
					$params[] = $start_date->sql;
					$params[] = $start_date->modify( '+1 year -1 second' )->sql;
					//$params[] = date( 'Y-m-d H:i:s', mktime( 0, 0, 0, 1, 1, $paramset['year'] ) );
					//$params[] = date( 'Y-m-d H:i:s', mktime( 0, 0, -1, 1, 1, $paramset['year'] + 1 ) );
				}

				if ( isset( $paramset['after'] ) ) {
					$where[] = 'pubdate > ?';
					$params[] = HabariDateTime::date_create( $paramset['after'] )->sql;
				}

				if ( isset( $paramset['before'] ) ) {
					$where[] = 'pubdate < ?';
					$params[] = HabariDateTime::date_create( $paramset['before'] )->sql;
				}

				// Concatenate the WHERE clauses
				if ( count( $where ) > 0 ) {
					$wheres[] = ' (' . implode( ' AND ', $where ) . ') ';
				}
			}
		}

		// Only show posts to which the current user has permission
		if ( isset( $paramset['ignore_permissions'] ) ) {
			$master_perm_where = '';
		}
		else {
			// This set of wheres will be used to generate a list of post_ids that this user can read
			$perm_where = array();
			$perm_where_denied = array();
			$params_where = array();
			$where = array();

			// 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 = array( 'post_any' => '(1=1)' );
			}
			else {
				// If a user can read his own posts, let him
				if ( User::identify()->can( 'own_posts', 'read' ) ) {
					$perm_where['own_posts_id'] = '{posts}.user_id = ?';
					$params_where[] = 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[] = '{posts}.content_type IN (' . implode( ',', $permitted_post_types ) . ')';
				}

				// If a user can read posts with specific tokens, let him
				if ( count( $read_tokens ) > 0 ) {
					$joins['post_tokens__allowed'] = ' LEFT JOIN {post_tokens} pt_allowed ON {posts}.id= pt_allowed.post_id AND pt_allowed.token_id IN ('.implode( ',', $read_tokens ).')';
					$perm_where['perms_join_null'] = 'pt_allowed.post_id IS NOT NULL';
				}

				// If a user has access to read other users' unpublished posts, let him
				if ( User::identify()->can( 'post_unpublished', 'read' ) ) {
					$perm_where[] = '({posts}.status <> ? AND {posts}.user_id <> ?)';
					$params_where[] = Post::status( 'published' );
					$params_where[] = User::identify()->id;
				}

			}

			$params_where_denied = array();
			// If a user is denied access to all posts, do so
			if ( User::identify()->cannot( 'post_any' ) ) {
				$perm_where_denied = array( '(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[] = '{posts}.content_type NOT IN (' . implode( ',', $denied_post_types ) . ')';
				}

				// If a user is denied access to read other users' unpublished posts, deny it
				if ( User::identify()->cannot( 'post_unpublished' ) ) {
					$perm_where_denied[] = '({posts}.status = ? OR {posts}.user_id = ?)';
					$params_where_denied[] = Post::status( 'published' );
					$params_where_denied[] = User::identify()->id;
				}

			}

			// This doesn't work yet because you can't pass these arrays by reference
			Plugins::act( 'post_get_perm_where', $perm_where, $params_where, $paramarray );
			Plugins::act( 'post_get_perm_where_denied', $perm_where_denied, $params_where_denied, $paramarray );
						
			// Set up the merge params
			$merge_params = array( $join_params, $params );
			
			// If there are granted permissions to check, add them to the where clause
			if ( count( $perm_where ) == 0 && !isset( $joins['post_tokens__allowed'] ) ) {
				// You have no grants.  You get no posts.
				$where['perms_granted'] = '(1=0)';
			}
			elseif ( count( $perm_where ) > 0 ) {
				$where['perms_granted'] = '
					(' . implode( ' OR ', $perm_where ) . ')
				';
				$merge_params[] = $params_where;
			}

			if ( count( $deny_tokens ) > 0 ) {
				$joins['post_tokens__denied'] = ' LEFT JOIN {post_tokens} pt_denied ON {posts}.id= pt_denied.post_id AND pt_denied.token_id IN ('.implode( ',', $deny_tokens ).')';
				$perm_where_denied['perms_join_null'] = 'pt_denied.post_id IS NULL';
			}

			// If there are denied permissions to check, add them to the where clause
			if ( count( $perm_where_denied ) > 0 ) {
				$where['perms_denied'] = '
					(' . implode( ' AND ', $perm_where_denied ) . ')
				';
				$merge_params[] = $params_where_denied;
			}
			
			// Merge the params
			$params = call_user_func_array( 'array_merge', $merge_params );

			// AND the separate permission-related WHERE clauses
			$master_perm_where = implode( ' AND ', $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;
		}

		// Define the LIMIT if it does not exist, unless specific posts are requested or we're getting the monthly counts
		if ( !isset( $limit ) && !isset( $paramset['id'] ) && !isset( $paramset['slug'] ) && !isset( $paramset['month_cts'] ) ) {
			$limit = Options::get( 'pagination' ) ? (int) Options::get( 'pagination' ) : 5;
		}
		elseif ( !isset( $limit ) ) {
			$selected_posts = 0;
			if ( isset( $paramset['id'] ) ) {
				$selected_posts += count( Utils::single_array( $paramset['id'] ) );
			}
			if ( isset( $paramset['slug'] ) ) {
				$selected_posts += count( Utils::single_array( $paramset['slug'] ) );
			}
			$limit = $selected_posts > 0 ? $selected_posts : '';
		}

		// Calculate the OFFSET based on the page number
		if ( isset( $page ) && is_numeric( $page ) && !isset( $paramset['offset'] ) ) {
			$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];
		}
		
		
		// If the orderby has a function in it, try to create a select field for it with an alias
		if ( strpos( $orderby, '(' ) !== false ) {
			$orders = explode( ',', $orderby );
			$ob_index = 0;
			foreach ( $orders as $key => $order ) {
				if ( !preg_match( '%(?P<field>.+)\s+(?P<direction>DESC|ASC)%i', $order, $order_matches ) ) {
					$order_matches = array(
						'field' => $order,
						'direction' => '',
					);
				}
				
				if ( strpos( $order_matches['field'], '(' ) !== false ) {
					$ob_index++;
					$field = 'orderby' . $ob_index;
					$select_ary[$field] = "{$order_matches['field']} AS $field";
					$select_distinct[$field] = "{$order_matches['field']} AS $field";
					$orders[$key] = $field . ' ' . $order_matches['direction'];
				}
			}
			$orderby = implode( ', ', $orders );
		}

		// Add arbitrary fields to the select clause for sorting and output
		if ( isset( $add_select ) ) {
			$select_ary = array_merge( $select_ary, $add_select );
		}
		

		/**
		 * Turn the requested fields into a comma-separated SELECT field clause
		 */
		$select = implode( ', ', $select_ary );

		/**
		 * 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 ) ) {
			$select = "COUNT($count)";
			$fetch_fn = 'get_value';
			$orderby = '';
			$groupby = '';
			$having = '';
		}

		// If the month counts are requested, replaced the select clause
		if ( isset( $paramset['month_cts'] ) ) {
			if ( isset( $paramset['vocabulary'] ) ) {
				$select = 'MONTH(FROM_UNIXTIME(pubdate)) AS month, YEAR(FROM_UNIXTIME(pubdate)) AS year, COUNT(DISTINCT {posts}.id) AS ct';
			}
			else {
				$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 = '';
		}

		// Define the LIMIT and add the OFFSET if it exists
		if ( !empty( $limit ) ) {
			$limit = " LIMIT $limit";
			if ( isset( $offset ) ) {
				$limit .= " OFFSET $offset";
			}
		}
		else {
			$limit = '';
		}

		/* All SQL parts are constructed, on to real business! */

		/**
		 * Build the final SQL statement
		 */
		$query = '
			SELECT DISTINCT ' . $select . '
			FROM {posts} ' . "\n " . implode( "\n ", $joins ) . "\n";

		if ( count( $wheres ) > 0 ) {
			$query .= ' WHERE (' . implode( " \nOR\n ", $wheres ) . ')';
			$query .= ( $master_perm_where == '' ) ? '' : ' AND (' . $master_perm_where . ')';
		}
		elseif ( $master_perm_where != '' ) {
			$query .= ' WHERE (' . $master_perm_where . ')';
		}
		$query .= ( ! isset( $groupby ) || $groupby == '' ) ? '' : ' GROUP BY ' . $groupby;
		$query .= ( ! isset( $having ) || $having == '' ) ? '' : ' HAVING ' . $having;
		$query .= ( ( $orderby == '' ) ? '' : ' ORDER BY ' . $orderby ) . $limit;

		/**
		 * 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,
				$params
			);
		}
		
		/**
		 * Execute the SQL statement using the PDO extension
		 */
		DB::set_fetch_mode( PDO::FETCH_CLASS );
		DB::set_fetch_class( 'Post' );
		$results = DB::$fetch_fn( $query, $params, 'Post' );

		//Utils::debug( $paramarray, $fetch_fn, $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;
		}
	}