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