PHP SwatDB::queryRow方法代码示例

 /**
  * Loads a set from the database with a shortname and instance
  *
  * @param string $shortname the shortname of the set
  *
  * @return boolean true if a set was successfully loaded and false if
  *                  no set was found at the specified shortname.
  */
 public function loadByShortname($shortname)
 {
     $this->checkDB();
     $instance_id = $this->instance === null ? null : $this->instance->id;
     $found = false;
     $sql = 'select * from %s where shortname = %s and instance %s %s';
     $sql = sprintf($sql, $this->table, $this->db->quote($shortname, 'text'), SwatDB::equalityOperator($instance_id), $this->db->quote($instance_id, 'integer'));
     $row = SwatDB::queryRow($this->db, $sql);
     if ($row !== null) {
         $this->initFromRow($row);
         $this->generatePropertyHashes();
         $found = true;
     }
     return $found;
 }

    private function getBoundingBox($photo_ids)
    {
        // a box is easier for now, but we might have to do a radius
        // to be more accurate
        $instance_id = $this->app->getInstance() === null ? null : $this->app->getInstanceId();
        $sql = sprintf('select
				max(PinholePhoto.gps_latitude) as max_latitude,
				min(PinholePhoto.gps_latitude) as min_latitude,
				max(PinholePhoto.gps_longitude) as max_longitude,
				min(PinholePhoto.gps_longitude) as min_longitude
			from PinholePhoto
			inner join ImageSet on PinholePhoto.image_set = ImageSet.id
			where PinholePhoto.status = %s and ImageSet.instance %s %s
				and PinholePhoto.id in (%s)', $this->app->db->quote(PinholePhoto::STATUS_PUBLISHED, 'integer'), SwatDB::equalityOperator($instance_id), $this->app->db->quote($instance_id, 'integer'), $this->app->db->implodeArray($photo_ids, 'integer'));
        $row = SwatDB::queryRow($this->app->db, $sql);
        return array('max_latitude' => $row->max_latitude, 'min_latitude' => $row->min_latitude, 'max_longitude' => $row->max_longitude, 'min_longitude' => $row->min_longitude);
    }

    protected function getInfo()
    {
        $instance_id = $this->app->getInstanceId();
        $sql = sprintf('select count(1) as post_count,
			sum(visible_comment_count) as comment_count,
			min(publish_date) as start_date from BlorgPost
				left outer join BlorgPostVisibleCommentCountView as v on
					BlorgPost.id = v.post and BlorgPost.instance = v.instance
			where BlorgPost.instance %s %s and BlorgPost.enabled = %s and
				BlorgPost.publish_date is not null', SwatDB::equalityOperator($instance_id), $this->app->db->quote($instance_id, 'integer'), $this->app->db->quote(true, 'boolean'));
        $post_info = SwatDB::queryRow($this->app->db, $sql, array('integer', 'integer', 'date'));
        $sql = sprintf('select sum(post_count) as tag_count from
			BlorgTagVisiblePostCountView as v
				inner join BlorgTag on BlorgTag.id = v.tag
			where instance %s %s', SwatDB::equalityOperator($instance_id), $this->app->db->quote($instance_id, 'integer'));
        $tag_info = SwatDB::queryRow($this->app->db, $sql, array('integer'));
        $start_date = new SwatDate($post_info->start_date);
        $now = new SwatDate();
        $start_month = $start_date->getMonth();
        $end_month = $now->getMonth();
        $start_year = $start_date->getYear();
        $end_year = $now->getYear();
        if ($start_month > $end_month) {
            $end_month += 12;
            $end_year--;
        }
        $months = $end_month - $start_month + ($end_year - $start_year) * 12;
        $days = $now->diff($start_date)->days;
        $posts_per_month = $months == 0 ? 0 : $post_info->post_count / $months;
        $comments_per_day = $days == 0 ? 0 : $post_info->comment_count / $days;
        return array('post_count' => $post_info->post_count, 'posts_per_month' => $posts_per_month, 'comment_count' => $post_info->comment_count, 'comments_per_day' => $comments_per_day, 'tag_count' => $tag_info->tag_count);
    }

    protected function getTagStats()
    {
        if (isset($this->app->memcache)) {
            $cache_key = 'PinholeStatisticsGadget.tag_stats';
            $value = $this->app->memcache->getNs('photos', $cache_key);
            if ($value !== false) {
                return $value;
            }
        }
        $sql = "select count(distinct PinholePhotoTagBinding.tag) as tag_count\n\t\t\tfrom PinholePhotoTagBinding\n\t\t\tinner join PinholePhoto on\n\t\t\t\tPinholePhoto.id = PinholePhotoTagBinding.photo\n\t\t\tinner join ImageSet on PinholePhoto.image_set = ImageSet.id\n\t\t\twhere ImageSet.instance %s %s and PinholePhoto.status = %s";
        $sql = sprintf($sql, SwatDB::equalityOperator($this->app->getInstanceId()), $this->app->db->quote($this->app->getInstanceId(), 'integer'), $this->app->db->quote(PinholePhoto::STATUS_PUBLISHED, 'integer'));
        $tag_count = SwatDB::queryOne($this->app->db, $sql);
        $sql = "select count(PinholePhotoTagBinding.photo) as tag_count,\n\t\t\t\tPinholeTag.id\n\t\t\tfrom PinholePhotoTagBinding\n\t\t\tinner join PinholeTag on PinholePhotoTagBinding.tag = PinholeTag.id\n\t\t\tinner join PinholePhoto on\n\t\t\t\tPinholePhoto.id = PinholePhotoTagBinding.photo\n\t\t\tinner join ImageSet on PinholePhoto.image_set = ImageSet.id\n\t\t\twhere ImageSet.instance %s %s and PinholePhoto.status = %s\n\t\t\t%s\n\t\t\tgroup by PinholeTag.id\n\t\t\torder by tag_count desc";
        if (!$this->app->session->isLoggedIn()) {
            $private_where_clause = sprintf('and PinholePhoto.private = %s', $this->app->db->quote(false, 'boolean'));
        } else {
            $private_where_clause = '';
        }
        $sql = sprintf($sql, SwatDB::equalityOperator($this->app->getInstanceId()), $this->app->db->quote($this->app->getInstanceId(), 'integer'), $this->app->db->quote(PinholePhoto::STATUS_PUBLISHED, 'integer'), $private_where_clause);
        $popular_tag_info = SwatDB::queryRow($this->app->db, $sql);
        if ($popular_tag_info === null) {
            $stats = new StdClass();
            $stats->tag_count = 0;
            return $stats;
        } else {
            $sql = sprintf('select title, name from PinholeTag
				where PinholeTag.id = %s', $this->app->db->quote($popular_tag_info->id, 'integer'));
            $popular_tag = SwatDB::queryRow($this->app->db, $sql);
            $stats = new StdClass();
            $stats->tag_count = $tag_count;
            $stats->popular_tag_title = $popular_tag->title;
            $stats->popular_tag_name = $popular_tag->name;
            $stats->popular_tag_count = $popular_tag_info->tag_count;
            if (isset($this->app->memcache)) {
                $this->app->memcache->setNs('photos', $cache_key, $stats);
            }
        }
        return $stats;
    }

    /**
     * Gets the date range of photos of this tag list
     *
     * @return array a two element array with the keys 'start' and 'end' and
     *                the values being two SwatDate objects. If there are no
     *                photos in the intersection of the tags in this tag list,
     *                null is returned.
     */
    public function getPhotoDateRange()
    {
        $args = func_get_args();
        $cache_key = $this->getCacheKey(__FUNCTION__, $args);
        $value = $this->app->getCacheValue($cache_key, 'photos');
        if ($value !== false) {
            return $value;
        }
        $sql = 'select
				max(convertTZ(PinholePhoto.photo_date,
					PinholePhoto.photo_time_zone)) as last_photo_date,
				min(convertTZ(PinholePhoto.photo_date,
					PinholePhoto.photo_time_zone)) as first_photo_date
			from PinholePhoto
			inner join ImageSet on PinholePhoto.image_set = ImageSet.id';
        $join_clauses = implode(' ', $this->getJoinClauses());
        if ($join_clauses != '') {
            $sql .= ' ' . $join_clauses . ' ';
        }
        $where_clause = $this->getWhereClause();
        if ($where_clause != '') {
            $sql .= ' where ' . $where_clause;
        }
        $range = SwatDB::queryRow($this->db, $sql);
        if ($range !== null) {
            $returned_range = array('start' => new SwatDate($range->first_photo_date), 'end' => new SwatDate($range->last_photo_date));
        } else {
            $returned_range = null;
        }
        $this->app->addCacheValue($returned_range, $cache_key, 'photos');
        return $returned_range;
    }

    public static function getDateRange($db, $where = null)
    {
        $date_range = SwatDB::queryRow($db, sprintf('select max(photo_date) as last_photo_date,
				min(photo_date) as first_photo_date
			from PinholePhoto where status = %s and %s', $db->quote(self::STATUS_PUBLISHED, 'integer'), $where === null ? '1 = 1' : $where));
        if ($date_range === null) {
            return null;
        } else {
            return array(new SwatDate($date_range->first_photo_date), new SwatDate($date_range->last_photo_date));
        }
    }