protected function buildInternal()
    {
        parent::buildInternal();
        $locale = SwatI18NLocale::get();
        $item_list = $this->getItemList('integer');
        $dep = new AdminListDependency();
        $dep->setTitle(CME::_('CME credit'), CME::_('CME credits'));
        $sql = sprintf('select CMECredit.*
			from CMECredit
			where CMECredit.id in (%s)', $item_list);
        $credits = SwatDB::query($this->app->db, $sql, SwatDBClassMap::get('CMECreditWrapper'));
        foreach ($credits as $credit) {
            $data = new stdClass();
            $data->id = $credit->id;
            $data->status_level = AdminDependency::DELETE;
            $data->parent = null;
            $data->title = $credit->getTitle();
            $dep->entries[] = new AdminDependencyEntry($data);
        }
        $message = $this->ui->getWidget('confirmation_message');
        $message->content = $dep->getMessage();
        $message->content_type = 'text/xml';
        if ($dep->getStatusLevelCount(AdminDependency::DELETE) === 0) {
            $this->switchToCancelButton();
        }
    }

    protected function initSideBar()
    {
        $this->sidebar = new SiteSidebar();
        $gadget_instances = false;
        if (isset($this->app->memcache)) {
            $gadget_instances = $this->app->memcache->get('gadget_instances');
        }
        if ($gadget_instances === false) {
            $sql = sprintf('select * from GadgetInstance
				where instance %s %s
				order by displayorder', SwatDB::equalityOperator($this->app->getInstanceId()), $this->app->db->quote($this->app->getInstanceId(), 'integer'));
            $gadget_instances = SwatDB::query($this->app->db, $sql, SwatDBClassMap::get('SiteGadgetInstanceWrapper'));
            $gadget_instances->loadAllSubRecordsets('setting_values', SwatDBClassMap::get('SiteGadgetInstanceSettingValueWrapper'), 'GadgetInstanceSettingValue', 'gadget_instance');
            if (isset($this->app->memcache)) {
                $this->app->memcache->set('gadget_instances', $gadget_instances);
            }
        } else {
            $gadget_instances->setDatabase($this->app->db);
        }
        foreach ($gadget_instances as $gadget_instance) {
            $gadget = SiteGadgetFactory::get($this->app, $gadget_instance);
            $this->sidebar->add($gadget);
        }
        $this->sidebar->init();
    }

    protected function getPhoto($filename)
    {
        $sql = sprintf('select PinholePhoto.*
			from PinholePhoto
			inner join ImageSet on PinholePhoto.image_set = ImageSet.id
			where PinholePhoto.filename = %s and ImageSet.instance %s %s', $this->app->db->quote($filename, 'text'), SwatDB::equalityOperator($this->app->getInstanceId()), $this->app->db->quote($this->app->getInstanceId(), 'integer'));
        $wrapper_class = SwatDBClassMap::get('PinholePhotoWrapper');
        $photos = SwatDB::query($this->app->db, $sql, $wrapper_class);
        if (count($photos) == 0) {
            $instance = $this->app->getInstance();
            if ($instance === null) {
                $message = sprintf("Photo with filename '%s' does not exist.", $filename);
            } else {
                $message = sprintf("Photo with filename '%s' does not exist " . "in the instance '%s'.", $filename, $instance->shortname);
            }
            throw new SiteNotFoundException($message);
        }
        $photo = $photos->getFirst();
        if ($photo->private && !$this->app->session->isLoggedIn()) {
            $message = sprintf("Photo with filename '%s' is private and user " . "is not logged in.", $filename);
            throw new SiteNotFoundException($message);
        }
        $photo->setFileBase('../photos');
        return $photo;
    }

    public function loadProviders($read_only = true)
    {
        $providers_wrapper_class = SwatDBClassMap::get('CMEProviderWrapper');
        $providers_wrapper = new $providers_wrapper_class();
        $providers_wrapper->setOptions('read_only', $read_only);
        $providers = SwatDB::query($this->db, 'select * from CMEProvider', $providers_wrapper);
        $sql = sprintf('select CMEFrontMatterProviderBinding.front_matter,
				CMEFrontMatterProviderBinding.provider
			from CMEFrontMatterProviderBinding
			inner join CMEProvider on
				CMEProvider.id = CMEFrontMatterProviderBinding.provider
			where CMEFrontMatterProviderBinding.front_matter in (%s)
			order by CMEFrontMatterProviderBinding.front_matter,
				CMEProvider.displayorder, CMEProvider.id', $this->db->implodeArray($this->getIndexes(), 'integer'));
        $rows = SwatDB::query($this->db, $sql);
        $front_matter_id = null;
        foreach ($rows as $row) {
            if ($row->front_matter !== $front_matter_id) {
                $front_matter_id = $row->front_matter;
                $front_matter = $this->getByIndex($row->front_matter);
                $front_matter->providers = new $providers_wrapper_class();
                $front_matter->providers->setOptions('read_only', $read_only);
            }
            $provider = $providers->getByIndex($row->provider);
            $front_matter->providers->add($provider);
        }
        return $providers;
    }

    /**
     * Creates a new tag
     *
     * @throws SwatException if no database connection is set on this tag
     *                        entry control.
     */
    protected function insertTag($title, $index)
    {
        if ($this->app === null) {
            throw new SwatException('An application must be set on the tag entry control during ' . 'the widget init phase.');
        }
        // check to see if the tag already exists
        $instance_id = $this->app->getInstanceId();
        $sql = sprintf('select * from
			BlorgTag where lower(title) = lower(%s) and instance %s %s', $this->app->db->quote($title, 'text'), SwatDB::equalityOperator($instance_id), $this->app->db->quote($instance_id, 'integer'));
        $tags = SwatDB::query($this->app->db, $sql, SwatDBClassMap::get('BlorgTagWrapper'));
        // only insert if no tag already exists (prevents creating two tags on
        // reloading)
        if (count($tags) > 0) {
            $tag = $tags->getFirst();
        } else {
            $tag = new BlorgTag();
            $tag->setDatabase($this->app->db);
            $tag->instance = $instance_id;
            $tag->title = $title;
            $tag->save();
            if (isset($this->app->memcache)) {
                $this->app->memcache->flushNs('tags');
            }
            $message = new SwatMessage(sprintf(Blorg::_('The tag “%s” has been added.'), $tag->title));
            $this->app->messages->add($message);
        }
        $this->tag_array[$tag->shortname] = $tag->title;
        $this->selected_tag_array[$tag->shortname] = $tag->title;
    }

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

 protected function getWhereClause()
 {
     $where_clause = parent::getWhereClause();
     $instance_id = $this->app->getInstanceId();
     $where_clause .= sprintf(' and instance %s %s', SwatDB::equalityOperator($instance_id), $this->app->db->quote($instance_id, 'integer'));
     return $where_clause;
 }

    protected function buildInternal()
    {
        parent::buildInternal();
        $item_list = $this->getItemList('integer');
        $instance_id = $this->app->getInstanceId();
        $dep = new AdminListDependency();
        $dep->setTitle(Blorg::_('post'), Blorg::_('posts'));
        $sql = sprintf('select id, title, bodytext from BlorgPost
			where instance %s %s and id in (%s)
			order by publish_date desc, title', SwatDB::equalityOperator($instance_id), $this->app->db->quote($instance_id, 'integer'), $item_list);
        $posts = SwatDB::query($this->app->db, $sql, 'BlorgPostWrapper');
        $entries = array();
        foreach ($posts as $post) {
            $entry = new AdminDependencyEntry();
            $entry->id = $post->id;
            $entry->title = $post->getTitle();
            $entry->status_level = AdminDependency::DELETE;
            $entry->parent = null;
            $entries[] = $entry;
        }
        $dep->entries = $entries;
        $message = $this->ui->getWidget('confirmation_message');
        $message->content = $dep->getMessage();
        $message->content_type = 'text/xml';
        if ($dep->getStatusLevelCount(AdminDependency::DELETE) == 0) {
            $this->switchToCancelButton();
        }
    }

    /**
     * Loads this comment
     *
     * @param integer $id the database id of this comment.
     * @param SiteInstance $instance optional. The instance to load the comment
     *                                in. If the application does not use
     *                                instances, this should be null. If
     *                                unspecified, the instance is not checked.
     *
     * @return boolean true if this comment and false if it was not.
     */
    public function load($id, SiteInstance $instance = null)
    {
        $this->checkDB();
        $loaded = false;
        $row = null;
        if ($this->table !== null && $this->id_field !== null) {
            $id_field = new SwatDBField($this->id_field, 'integer');
            $sql = sprintf('select %1$s.* from %1$s
				inner join PinholePhoto on %1$s.photo = PinholePhoto.id
				inner join ImageSet on ImageSet.id = PinholePhoto.image_set
				where %1$s.%2$s = %3$s', $this->table, $id_field->name, $this->db->quote($id, $id_field->type));
            $instance_id = $instance === null ? null : $instance->id;
            if ($instance_id !== null) {
                $sql .= sprintf(' and ImageSet.instance %s %s', 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;
    }

    /**
     * Creates a new tag
     *
     * @throws SwatException if no database connection is set on this tag
     *                        entry control.
     */
    protected function insertTag($title, $index)
    {
        if ($this->app === null) {
            throw new SwatException('An application must be set on the tag entry control during ' . 'the widget init phase.');
        }
        // check to see if the tag already exists
        $instance_id = $this->app->getInstanceId();
        $sql = sprintf('select * from
			PinholeTag where lower(title) = lower(%s)
				and instance %s %s', $this->app->db->quote($title, 'text'), SwatDB::equalityOperator($instance_id), $this->app->db->quote($instance_id, 'integer'));
        $tags = SwatDB::query($this->app->db, $sql, SwatDBClassMap::get('PinholeTagDataObjectWrapper'));
        // only insert if no tag already exists (prevents creating two tags on
        // reloading)
        if (count($tags) > 0) {
            $tag_obj = $tags->getFirst();
        } else {
            $tag_obj = new PinholeTagDataObject();
            $tag_obj->setDatabase($this->app->db);
            $tag_obj->instance = $instance_id;
            $tag_obj->title = $title;
            $tag_obj->save();
            $message = new SwatMessage(sprintf(Pinhole::_('“%s” tag has been added'), SwatString::minimizeEntities($tag_obj->title)));
            $message->content_type = 'text/xml';
            $message->secondary_content = sprintf(Pinhole::_('You can <a href="Tag/Edit?id=%d">edit this tag</a> ' . 'to customize it.'), $tag_obj->id);
            $this->app->messages->add($message);
        }
        $this->tag_array[$tag_obj->name] = $tag_obj->title;
        $this->selected_tag_array[$tag_obj->name] = $tag_obj->title;
    }

    /**
     * Gets the metadata for display
     *
     * @return SwatTableModel with metadata information.
     */
    protected function getTableModel(SwatView $view)
    {
        $sql = 'select * from PinholeMetaData
			where PinholeMetaData.instance %s %s
			order by %s';
        $instance_id = $this->app->getInstanceId();
        $sql = sprintf($sql, SwatDB::equalityOperator($instance_id), $this->app->db->quote($instance_id, 'integer'), $this->getOrderByClause($view, 'visible desc, displayorder, title'));
        $metadata = SwatDB::query($this->app->db, $sql);
        $store = new SwatTableStore();
        foreach ($metadata as $data) {
            $ds = new SwatDetailsView();
            $ds->title = $data->title;
            $ds->shortname = $data->shortname;
            $ds->id = $data->id;
            $ds->visible = $data->visible;
            $ds->machine_tag = $data->machine_tag;
            if ($ds->visible) {
                $ds->group_title = 'Shown';
            } else {
                $ds->group_title = 'Not Shown';
            }
            $store->add($ds);
        }
        return $store;
    }

    protected function getSegments()
    {
        $sql = 'select * from MailingListCampaignSegment
			where %s and instance %s %s';
        $sql = sprintf($sql, $this->force_all ? '1 = 1' : sprintf('enabled = %s', $this->db->quote(true, 'boolean')), SwatDB::equalityOperator($this->getInstanceId()), $this->db->quote($this->getInstanceId(), 'integer'));
        return SwatDB::query($this->db, $sql, SwatDBClassMap::get('DeliveranceCampaignSegmentWrapper'));
    }

 protected function initFrontMatter()
 {
     $sql = sprintf('select * from CMEFrontMatter where evaluation = %s', $this->app->db->quote($this->inquisition->id, 'integer'));
     $this->front_matter = SwatDB::query($this->app->db, $sql, SwatDBClassMap::get('CMEFrontMatterWrapper'))->getFirst();
     if (!$this->front_matter instanceof CMEFrontMatter) {
         throw new AdminNotFoundException(sprintf('Evaluation with id of %s not found.', $this->id));
     }
 }

 public function finalize()
 {
     $base_sql = 'update Article set parent = %s where id = %s';
     foreach ($this->table->parents as $id => $parent) {
         $sql = sprintf($base_sql, $this->table->process->dst_db->quote($parent, 'integer'), $this->table->process->dst_db->quote($id, 'integer'));
         SwatDB::exec($this->table->process->dst_db, $sql);
     }
 }

 public function getFrontMatter()
 {
     require_once 'CME/dataobjects/CMEFrontMatterWrapper.php';
     $this->checkDB();
     $inquisition_id = $this->getInternalValue('inquisition');
     $sql = sprintf('select * from CMEFrontMatter where evaluation = %s', $this->db->quote($inquisition_id, 'integer'));
     return SwatDB::query($this->db, $sql, SwatDBClassMap::get('CMEFrontMatterWrapper'))->getFirst();
 }

    /**
     * Creates a new author index page
     *
     * @param SiteWebApplication $app the application.
     * @param SiteLayout $layout
     */
    public function __construct(SiteWebApplication $app, SiteLayout $layout, array $arguments = array())
    {
        parent::__construct($app, $layout, $arguments);
        $instance_id = $this->app->getInstanceId();
        $sql = sprintf('select * from BlorgAuthor
			where instance %s %s and visible = %s', SwatDB::equalityOperator($instance_id), $this->app->db->quote($instance_id, 'integer'), $this->app->db->quote(true, 'boolean'));
        $this->authors = SwatDB::query($this->app->db, $sql, SwatDBClassMap::get('BlorgAuthorWrapper'));
    }

    protected function getArticles()
    {
        $sql = sprintf('select title, shortname, description from Article
			where enabled = %s and visible = %s and parent is null
				and instance %s %s
			order by title asc', $this->app->db->quote(true, 'boolean'), $this->app->db->quote(true, 'boolean'), SwatDB::equalityOperator($this->app->getInstanceId()), $this->app->db->quote($this->app->getInstanceId(), 'integer'));
        $wrapper = SwatDBClassMap::get('SiteArticleWrapper');
        return SwatDB::query($this->app->db, $sql, $wrapper);
    }

    protected function validateShortname($shortname)
    {
        $sql = 'select shortname from BlorgTag
			where shortname = %s and id %s %s and instance %s %s';
        $instance_id = $this->app->getInstanceId();
        $sql = sprintf($sql, $this->app->db->quote($shortname, 'text'), SwatDB::equalityOperator($this->id, true), $this->app->db->quote($this->id, 'integer'), SwatDB::equalityOperator($instance_id), $this->app->db->quote($instance_id, 'integer'));
        $query = SwatDB::query($this->app->db, $sql);
        return count($query) == 0;
    }

    protected function validateShortname($shortname)
    {
        $instance_id = $this->app->getInstanceId();
        $sql = sprintf('select count(id) from Article
			where shortname = %s and
				instance %s %s and
				parent %s %s
				and id %s %s', $this->app->db->quote($shortname, 'text'), SwatDB::equalityOperator($instance_id), $this->app->db->quote($instance_id, 'integer'), SwatDB::equalityOperator($this->parent), $this->app->db->quote($this->parent, 'integer'), SwatDB::equalityOperator($this->id, true), $this->app->db->quote($this->id, 'integer'));
        return SwatDB::queryOne($this->app->db, $sql) == 0;
    }