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