protected function initReport()
{
$quarter = SiteApplication::initVar('quarter', null, SiteApplication::VAR_GET);
if ($quarter === null || preg_match('/^2[0-9]{3}-0[1-4]$/', $quarter) === 0) {
throw new AdminNotFoundException('Invalid quarter.');
}
list($year, $quarter) = explode('-', $quarter, 2);
$start_month = (intval($quarter) - 1) * 3 + 1;
$quarter = new SwatDate();
$quarter->setTime(0, 0, 0);
$quarter->setDate($year, $start_month, 1);
$quarter->setTZ($this->app->default_time_zone);
$quarter->toUTC();
$type = SiteApplication::initVar('type', null, SiteApplication::VAR_GET);
$provider = new CMEProvider();
$provider->setDatabase($this->app->db);
if (!$provider->loadByShortname($type)) {
throw new AdminNotFoundException('Invalid CME provider.');
}
$sql = sprintf('select * from QuizReport
where quarter = %s and provider = %s', $this->app->db->quote($quarter->getDate(), 'date'), $this->app->db->quote($provider->id, 'integer'));
$this->report = SwatDB::query($this->app->db, $sql, SwatDBClassMap::get('CMEQuizReportWrapper'))->getFirst();
if (!$this->report instanceof CMEQuizReport) {
throw new AdminNotFoundException(sprintf('Report not found for quarter %s.', $quarter->getDate()));
}
$this->report->setFileBase('../../system/quiz-report-updater');
if (!file_exists($this->report->getFilePath())) {
throw new AdminNotFoundException(sprintf('Report file ‘%s’ not found', $this->report->getFilePath()));
}
}
/**
* Gets earned CME credits to include in the quarterly report
*
* Credits are included if and only if:
*
* - the credit is earned
* - the provider is the specified provider
* - the earned date is within the quarter
* - the account is not deleted
*
* @return array
*/
protected function getEarnedCredits()
{
$sql = sprintf('select AccountEarnedCMECredit.* from AccountEarnedCMECredit
inner join Account
on AccountEarnedCMECredit.account = Account.id
inner join CMECredit
on AccountEarnedCMECredit.credit = CMECredit.id
inner join CMEFrontMatter
on CMECredit.front_matter = CMEFrontMatter.id
where CMEFrontMatter.id in (
select CMEFrontMatterProviderBinding.front_matter
from CMEFrontMatterProviderBinding
where CMEFrontMatterProviderBinding.provider = %s
)
and convertTZ(earned_date, %s) >= %s
and convertTZ(earned_date, %s) < %s
and Account.delete_date is null', $this->app->db->quote($this->provider->id, 'integer'), $this->app->db->quote($this->app->config->date->time_zone, 'text'), $this->app->db->quote($this->start_date->getDate(), 'date'), $this->app->db->quote($this->app->config->date->time_zone, 'text'), $this->app->db->quote($this->end_date->getDate(), 'date'));
$earned_credits = SwatDB::query($this->app->db, $sql, SwatDBClassMap::get('CMEAccountEarnedCMECreditWrapper'));
// efficiently load accounts
$accounts = $this->loadAccounts($earned_credits);
// load addresses
$addresses = $this->loadAccountAddresses($accounts);
// efficiently load credits
$credits = $this->loadCredits($earned_credits);
// sort earned credits (sorting is application specific)
$earned_credits_array = $earned_credits->getArray();
usort($earned_credits_array, array($this, 'compareEarnedCredit'));
return $earned_credits_array;
}
protected function initPosts($year, $month_name)
{
if (!array_key_exists($month_name, BlorgPageFactory::$months_by_name)) {
throw new SiteNotFoundException('Page not found.');
}
// Date parsed from URL is in locale time.
$month = BlorgPageFactory::$months_by_name[$month_name];
$date = new SwatDate();
$date->setTZ($this->app->default_time_zone);
$date->setDate($year, $month, 1);
$date->setTime(0, 0, 0);
$memcache = isset($this->app->memcache) ? $this->app->memcache : null;
$loader = new BlorgPostLoader($this->app->db, $this->app->getInstance(), $memcache);
$loader->addSelectField('title');
$loader->addSelectField('bodytext');
$loader->addSelectField('shortname');
$loader->addSelectField('publish_date');
$loader->addSelectField('author');
$loader->addSelectField('comment_status');
$loader->addSelectField('visible_comment_count');
$loader->setLoadFiles(true);
$loader->setLoadTags(true);
$loader->setWhereClause(sprintf('enabled = %s and
date_trunc(\'month\', convertTZ(publish_date, %s)) =
date_trunc(\'month\', timestamp %s)', $this->app->db->quote(true, 'boolean'), $this->app->db->quote($this->app->default_time_zone->getName(), 'text'), $this->app->db->quote($date->getDate(), 'date')));
$loader->setOrderByClause('publish_date desc');
$this->posts = $loader->getPosts();
if (count($this->posts) == 0) {
throw new SiteNotFoundException('Page not found.');
}
}
public function convertData($data)
{
$data = parent::convertData($data);
if ($data === null && !$this->convert_null_to_now) {
return null;
}
$date = new SwatDate($data);
$date->setTZbyID($this->src_tz_id);
$date->convertTZbyID($this->dst_tz_id);
$data = $date->getDate();
return $data;
}
protected function saveDBData()
{
$values = $this->ui->getValues(array('title', 'name', 'event', 'archived'));
$this->tag->title = $values['title'];
$this->tag->name = $values['name'];
$this->tag->event = $values['event'];
$this->tag->archived = $values['archived'];
if ($this->id === null) {
$now = new SwatDate();
$this->tag->createdate = $now->getDate();
}
$flush_cache = $this->tag->isModified() && $this->tag->id !== null;
$this->tag->save();
$this->addToSearchQueue();
if (isset($this->app->memcache) && $flush_cache) {
$this->app->memcache->flushNs('photos');
}
$message = new SwatMessage(sprintf(Pinhole::_('“%s” has been saved.'), $this->tag->title));
$this->app->messages->add($message);
}
protected function initMonths($year, $page)
{
// Date parsed from URL is in locale time.
$date = new SwatDate();
$date->setTZ($this->app->default_time_zone);
$date->setDate($year, 1, 1);
$date->setTime(0, 0, 0);
$memcache = isset($this->app->memcache) ? $this->app->memcache : null;
$this->loader = new BlorgPostLoader($this->app->db, $this->app->getInstance(), $memcache);
$this->loader->addSelectField('title');
$this->loader->addSelectField('bodytext');
$this->loader->addSelectField('shortname');
$this->loader->addSelectField('publish_date');
$this->loader->addSelectField('author');
$this->loader->addSelectField('comment_status');
$this->loader->addSelectField('visible_comment_count');
$this->loader->setWhereClause(sprintf('enabled = %s and
date_trunc(\'year\', convertTZ(publish_date, %s)) =
date_trunc(\'year\', timestamp %s)', $this->app->db->quote(true, 'boolean'), $this->app->db->quote($this->app->default_time_zone->getName(), 'text'), $this->app->db->quote($date->getDate(), 'date')));
$this->loader->setOrderByClause('publish_date desc');
$offset = ($page - 1) * self::MAX_POSTS;
$this->loader->setRange(self::MAX_POSTS, $offset);
$posts = $this->loader->getPosts();
foreach ($posts as $post) {
$publish_date = clone $post->publish_date;
$publish_date->convertTZ($this->app->default_time_zone);
$month = $publish_date->getMonth();
if (!array_key_exists($month, $this->months)) {
$this->months[$month] = array();
}
$this->months[$month][] = $post;
}
if (count($this->months) == 0) {
throw new SiteNotFoundException('Page not found');
}
}
public function getPostByDateAndShortname(SwatDate $date, $shortname)
{
$post = false;
if ($this->memcache !== null) {
$key = $date->formatLikeIntl('yyyyMMdd') . $shortname;
$key = $this->getPostCacheKey($key);
$post = $this->memcache->getNs('posts', $key);
}
if ($post === false) {
$sql = $this->getSelectClause();
$sql .= $this->getWhereClause();
$sql .= sprintf(' and BlorgPost.shortname = %s and
date_trunc(\'month\', convertTZ(publish_date, %s)) =
date_trunc(\'month\', timestamp %s)', $this->db->quote($shortname, 'text'), $this->db->quote($date->getTimezone()->getName(), 'text'), $this->db->quote($date->getDate(), 'date'));
$this->db->setLimit(1, 0);
$post_wrapper = SwatDBClassMap::get('BlorgPostWrapper');
$posts = SwatDB::query($this->db, $sql, $post_wrapper);
if (in_array('author', $this->fields)) {
$this->loadPostAuthors($posts);
}
if ($this->load_files) {
$this->loadPostFiles($posts);
}
if ($this->load_tags) {
$this->loadPostTags($posts);
}
$post = $posts->getFirst();
if ($this->memcache !== null) {
$this->memcache->setNs('posts', $key, $post);
}
} else {
if ($post !== null) {
$post->setDatabase($this->db);
}
}
return $post;
}
/**
* Loads a post by a date and the post's shortname
*
* @param SwatDate $date the date the <i>publish_date<i> of the post. Only
* the year and month fields are used for comparison.
* @param string $shortname the shortname of the post to load.
* @param SiteInstance $instance optional. The instance to load the post in.
* If the site does not use instances, this
* should be null.
*
* @return boolean true if this post was loaded from the given publish_date
* and shortname and false if it was not.
*/
public function loadByDateAndShortname(SwatDate $date, $shortname, SiteInstance $instance = null)
{
$this->checkDB();
$loaded = false;
$row = null;
if ($this->table !== null) {
$instance_id = $instance === null ? null : $instance->id;
$sql = sprintf('select * from %s
where shortname = %s and
date_trunc(\'month\',
convertTZ(publish_date, %s)) =
date_trunc(\'month\', timestamp %s) and
instance %s %s', $this->table, $this->db->quote($shortname, 'text'), $this->db->quote($date->getTimezone->getName(), 'text'), $this->db->quote($date->getDate(), 'date'), SwatDB::equalityOperator($instance_id), $this->db->quote($instance_id, 'integer'));
$rs = SwatDB::query($this->db, $sql, null);
$row = $rs->fetchRow(MDB2_FETCHMODE_ASSOC);
}
if ($row !== null) {
$this->initFromRow($row);
$this->generatePropertyHashes();
$loaded = true;
}
return $loaded;
}
protected function getQuarterEarnedCredits(CMEProvider $provider, $year, $quarter)
{
$start_month = ($quarter - 1) * 3 + 1;
$start_date = new SwatDate();
$start_date->setTime(0, 0, 0);
$start_date->setDate($year, $start_month, 1);
$start_date->setTZ($this->default_time_zone);
$end_date = clone $start_date;
$end_date->addMonths(3);
$sql = sprintf('select count(1)
from AccountCMEProgressCreditBinding
inner join AccountCMEProgress on
AccountCMEProgressCreditBinding.progress = AccountCMEProgress.id
inner join AccountEarnedCMECredit on
AccountEarnedCMECredit.account = AccountCMEProgress.account
and AccountCMEProgressCreditBinding.credit =
AccountEarnedCMECredit.credit
inner join CMECredit on
CMECredit.id = AccountEarnedCMECredit.credit
inner join Account on AccountCMEProgress.account = Account.id
where CMECredit.front_matter in (
select CMEFrontMatterProviderBinding.front_matter
from CMEFrontMatterProviderBinding
where CMEFrontMatterProviderBinding.provider = %s
)
and convertTZ(earned_date, %s) >= %s
and convertTZ(earned_date, %s) < %s
and Account.delete_date is null', $this->db->quote($provider->id, 'integer'), $this->db->quote($this->config->date->time_zone, 'text'), $this->db->quote($start_date->getDate(), 'date'), $this->db->quote($this->config->date->time_zone, 'text'), $this->db->quote($end_date->getDate(), 'date'));
return SwatDB::queryOne($this->db, $sql);
}
protected function getPhotos()
{
// load the photos uploaded in the last day
$date = new SwatDate();
$date->subtractDays(1);
$date->toUTC();
$instance_id = $this->app->getInstance() === null ? null : $this->app->getInstanceId();
$sql = sprintf('select PinholePhoto.* from PinholePhoto
inner join ImageSet on PinholePhoto.image_set = ImageSet.id
where ImageSet.instance %s %s and PinholePhoto.dav_upload = %s
and PinholePhoto.upload_date > %s
order by original_filename asc', SwatDB::equalityOperator($instance_id), $this->app->db->quote($instance_id, 'integer'), $this->app->db->quote(true, 'boolean'), $this->app->db->quote($date->getDate(), 'date'));
$photos = SwatDB::query($this->app->db, $sql, SwatDBClassMap::get('PinholeSimplePhotoWrapper'));
return $photos;
}
protected function getResponses()
{
// Get all rows from AccountCMEProgress where the credit was earned in
// the past quarter. This uses a join between AccountCMEProgress and
// AccountEarnedCMECredit through AccountCMEProgressCreditBinding.
// All of this is necessary because we clone the Inquisition for each
// account and so we can't just look up CMEFrontMatter.evaluation.
$progress_ids_sql = sprintf('select progress
from AccountCMEProgressCreditBinding
inner join AccountCMEProgress on
AccountCMEProgressCreditBinding.progress = AccountCMEProgress.id
inner join AccountEarnedCMECredit on
AccountEarnedCMECredit.account = AccountCMEProgress.account
and AccountCMEProgressCreditBinding.credit =
AccountEarnedCMECredit.credit
inner join CMECredit on
CMECredit.id = AccountEarnedCMECredit.credit
inner join Account on AccountCMEProgress.account = Account.id
where CMECredit.front_matter in (
select CMEFrontMatterProviderBinding.front_matter
from CMEFrontMatterProviderBinding
where CMEFrontMatterProviderBinding.provider = %s
)
and convertTZ(earned_date, %s) >= %s
and convertTZ(earned_date, %s) < %s
and Account.delete_date is null', $this->app->db->quote($this->provider->id, 'integer'), $this->app->db->quote($this->app->config->date->time_zone, 'text'), $this->app->db->quote($this->start_date->getDate(), 'date'), $this->app->db->quote($this->app->config->date->time_zone, 'text'), $this->app->db->quote($this->end_date->getDate(), 'date'));
$sql = 'select InquisitionResponse.*
from InquisitionResponse
inner join AccountCMEProgress on
AccountCMEProgress.evaluation = InquisitionResponse.inquisition
and AccountCMEProgress.account = InquisitionResponse.account
where AccountCMEProgress.id in (' . $progress_ids_sql . ')';
$responses = SwatDB::query($this->app->db, $sql, SwatDBClassMap::get('InquisitionResponseWrapper'));
// efficiently load response values
$values = $responses->loadAllSubRecordsets('values', SwatDBClassMap::get('InquisitionResponseValueWrapper'), 'InquisitionResponseValue', 'response');
// wrappers for effecient loading of question bindings and questions on
// response values and evaluations.
$question_wrapper = SwatDBClassMap::get('InquisitionQuestionWrapper');
$question_binding_wrapper = SwatDBClassMap::get('InquisitionInquisitionQuestionBindingWrapper');
// efficiently load response value question bindings
$question_binding_sql = 'select * from InquisitionInquisitionQuestionBinding
where id in (%s)';
$question_bindings = $values->loadAllSubDataObjects('question_binding', $this->app->db, $question_binding_sql, $question_binding_wrapper);
// and response value questions
if ($question_bindings instanceof $question_binding_wrapper) {
$question_sql = 'select * from InquisitionQuestion
where id in (%s)';
$questions = $question_bindings->loadAllSubDataObjects('question', $this->app->db, $question_sql, $question_wrapper);
}
// efficiently load response evaluations
$evaluation_sql = 'select * from Inquisition where id in (%s)';
$evaluations = $responses->loadAllSubDataObjects('inquisition', $this->app->db, $evaluation_sql, SwatDBClassMap::get('CMEEvaluationWrapper'));
// efficiently load evaluation question bindings
if ($evaluations instanceof CMEEvaluationWrapper) {
$question_binding_sql = sprintf('select * from InquisitionInquisitionQuestionBinding
where inquisition in (%s)
order by inquisition, displayorder', $this->app->db->implodeArray($evaluations->getIndexes(), 'integer'));
$question_bindings = SwatDB::query($this->app->db, $question_binding_sql, $question_binding_wrapper);
$evaluations->attachSubRecordset('question_bindings', $question_binding_wrapper, 'inquisition', $question_bindings);
// efficiently load evaluations questions
if ($question_bindings instanceof $question_binding_wrapper) {
$question_sql = 'select * from InquisitionQuestion
where id in (%s)';
$questions = $question_bindings->loadAllSubDataObjects('question', $this->app->db, $question_sql, $question_wrapper);
// efficiently load evaluation question options
if ($questions instanceof $question_wrapper) {
$options = $questions->loadAllSubRecordsets('options', SwatDBClassMap::get('InquisitionQuestionOptionWrapper'), 'InquisitionQuestionOption', 'question', '', 'displayorder');
}
}
}
$response_array = array();
foreach ($responses as $response) {
// filter out responses for evaluations with no questions
if (count($response->inquisition->question_bindings) > 0) {
$response_array[] = $response;
}
}
return $response_array;
}
protected function resetQuiz(SwatForm $form)
{
// response can be null when refreshing the quiz page immediately after
// resetting a quiz, or resetting it in another window, and attempting
// to reset a second time.
if (!$this->front_matter->resettable || !$this->response instanceof InquisitionResponse) {
return;
}
$now = new SwatDate();
$now->toUTC();
$sql = sprintf('update InquisitionResponse set
reset_date = %s where id = %s', $this->app->db->quote($now->getDate(), 'date'), $this->app->db->quote($this->response->id, 'integer'));
SwatDB::exec($this->app->db, $sql);
}
public static function getPhotoCountPerDay(SiteWebApplication $app, SwatDate $date)
{
if (isset($app->memcache)) {
$cache_key = sprintf('PinholeCalendarGadget.count.%s.%s', $date->formatLikeIntl('yyyy-MM'), $app->session->isLoggedIn() ? 'private' : 'public');
$count = $app->memcache->getNs('photos', $cache_key);
if ($count !== false) {
return $count;
}
}
$sql = "select count(PinholePhoto.id) as photo_count,\n\t\t\t\tdate_part('day', max(convertTZ(PinholePhoto.photo_date,\n\t\t\t\t\tPinholePhoto.photo_time_zone))) as photo_day\n\t\t\tfrom PinholePhoto\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\tand PinholePhoto.photo_date is not null\n\t\t\t\tand convertTZ(PinholePhoto.photo_date,\n\t\t\t\t\tPinholePhoto.photo_time_zone) >= %s\n\t\t\t\tand convertTZ(PinholePhoto.photo_date,\n\t\t\t\t\tPinholePhoto.photo_time_zone) < %s\n\t\t\t\t%s\n\t\t\tgroup by date_part('day', convertTZ(PinholePhoto.photo_date,\n\t\t\t\tPinholePhoto.photo_time_zone))";
$end_date = clone $date;
$end_date->addMonths(1);
if (!$app->session->isLoggedIn()) {
$private_where_clause = sprintf('and PinholePhoto.private = %s', $app->db->quote(false, 'boolean'));
} else {
$private_where_clause = '';
}
$sql = sprintf($sql, SwatDB::equalityOperator($app->getInstanceId()), $app->db->quote($app->getInstanceId(), 'integer'), $app->db->quote(PinholePhoto::STATUS_PUBLISHED), $app->db->quote($date->getDate(), 'date'), $app->db->quote($end_date->getDate(), 'date'), $private_where_clause);
$days = SwatDB::query($app->db, $sql);
$day_count = array();
foreach ($days as $day) {
$day_count[$day->photo_day] = $day->photo_count;
}
if (isset($app->memcache)) {
$app->memcache->setNs('photos', $cache_key, $day_count);
}
return $day_count;
}
