/**
* @inheritDoc IElementType::modifyElementsQuery()
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
*
* @return mixed
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('globalsets.name, globalsets.handle, globalsets.fieldLayoutId')->join('globalsets globalsets', 'globalsets.id = elements.id');
if ($criteria->handle) {
$query->andWhere(DbHelper::parseParam('globalsets.handle', $criteria->handle, $query->params));
}
}
/**
* Modifies an element query targeting elements of this type.
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
* @return mixed
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('venti.startDate, venti.endDate, venti.allDay, venti.isrepeat, venti.eid, venti.eventid, venti.repeat, venti.rRule, venti.summary, venti.locale, entries.postDate, entries.expiryDate')->leftJoin('venti_events venti', 'venti.eventid = elements.id')->leftJoin('entries entries', 'entries.id = eventid')->group('venti.startDate');
if ($criteria->locale) {
$query->andWhere(DbHelper::parseParam('venti.locale', $criteria->locale, $query->params));
}
if ($criteria->startDate) {
$query->andWhere(DbHelper::parseDateParam('venti.startDate', $criteria->startDate, $query->params));
}
if ($criteria->id) {
$query->andWhere(DbHelper::parseParam('venti.eventid', $criteria->eventid, $query->params));
}
if ($criteria->eventid) {
$query->andWhere(DbHelper::parseParam('venti.eventid', $criteria->eventid, $query->params));
}
if ($criteria->endDate) {
$query->andWhere(DbHelper::parseDateParam('venti.endDate', $criteria->endDate, $query->params));
}
if ($criteria->summary) {
$query->andWhere(DbHelper::parseParam('venti.summary', $criteria->summary, $query->params));
}
if ($criteria->isrepeat) {
$query->andWhere(DbHelper::parseParam('venti.isrepeat', $criteria->isrepeat, $query->params));
}
if ($criteria->rRule) {
$query->andWhere(DbHelper::parseParam('venti.rRule', $criteria->rRule, $query->params));
}
if ($criteria->eid) {
$query->andWhere(DbHelper::parseParam('venti.eid', $criteria->eid, $query->params));
}
if ($criteria->repeat) {
$query->andWhere(DbHelper::parseParam('venti.repeat', $criteria->repeat, $query->params));
}
if ($criteria->allDay) {
$query->andWhere(DbHelper::parseDateParam('venti.allDay', $criteria->allDay, $query->params));
}
if ($criteria->between) {
$dates = array();
$interval = array();
if (!is_array($criteria->between)) {
$criteria->between = ArrayHelper::stringToArray($criteria->between);
}
if (count($criteria->between) == 2) {
foreach ($criteria->between as $ref) {
if (!$ref instanceof \DateTime) {
$dates[] = DateTime::createFromString($ref, craft()->getTimeZone());
} else {
$dates[] = $ref;
}
}
if ($dates[0] > $dates[1]) {
$interval[0] = $dates[1];
$interval[1] = $dates[0];
} else {
$interval = $dates;
}
$query->andWhere('(venti.startDate BETWEEN :betweenStartDate AND :betweenEndDate) OR (:betweenStartDate BETWEEN venti.startDate AND venti.endDate)', array(':betweenStartDate' => DateTimeHelper::formatTimeForDb($interval[0]->getTimestamp()), ':betweenEndDate' => DateTimeHelper::formatTimeForDb($interval[1]->getTimestamp())));
}
}
}
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('formbuilder_entries.formId, formbuilder_entries.title, formbuilder_entries.data')->join('formbuilder_entries formbuilder_entries', 'formbuilder_entries.id = elements.id');
if ($criteria->formId) {
$query->andWhere(DbHelper::parseParam('formbuilder_entries.formId', $criteria->formId, $query->params));
}
}
/**
* Returns the data for a run chart, based on a given DB query, start/end dates, and the desired time interval unit.
*
* The query’s SELECT clause should already be set to a column aliased as `value`.
*
* The $options array can override the following defaults:
*
* - `intervalUnit` - The time interval unit to use ('hour', 'day', 'month', or 'year').
* By default, a unit will be decided automatically based on the start/end date duration.
* - `categoryLabel` - The label to use for the chart categories (times). Defaults to "Date".
* - `valueLabel` - The label to use for the chart values. Defaults to "Value".
* - `valueType` - The type of values that are being plotted ('number', 'currency', 'percent', 'time'). Defaults to 'number'.
*
* @param DbCommand $query The DB query that should be used
* @param DateTime $startDate The start of the time duration to select (inclusive)
* @param DateTime $endDate The end of the time duratio to select (exclusive)
* @param string $dateColumn The column that represents the date
* @param array|null $options Any customizations that should be made over the default options
*
* @return array
*/
public static function getRunChartDataFromQuery(DbCommand $query, DateTime $startDate, DateTime $endDate, $dateColumn, $options = array())
{
// Setup
$options = array_merge(array('intervalUnit' => null, 'categoryLabel' => Craft::t('Date'), 'valueLabel' => Craft::t('Value'), 'valueType' => 'number'), $options);
$craftTimezone = new \DateTimeZone(craft()->timezone);
$utc = new \DateTimeZone(DateTime::UTC);
if ($options['intervalUnit'] && in_array($options['intervalUnit'], array('year', 'month', 'day', 'hour'))) {
$intervalUnit = $options['intervalUnit'];
} else {
$intervalUnit = self::getRunChartIntervalUnit($startDate, $endDate);
}
switch ($intervalUnit) {
case 'year':
$sqlDateFormat = '%Y-01-01';
$phpDateFormat = 'Y-01-01';
$sqlGroup = "YEAR({$dateColumn})";
$cursorDate = new DateTime($startDate->format('Y-01-01'), $craftTimezone);
break;
case 'month':
$sqlDateFormat = '%Y-%m-01';
$phpDateFormat = 'Y-m-01';
$sqlGroup = "YEAR({$dateColumn}), MONTH({$dateColumn})";
$cursorDate = new DateTime($startDate->format('Y-m-01'), $craftTimezone);
break;
case 'day':
$sqlDateFormat = '%Y-%m-%d';
$phpDateFormat = 'Y-m-d';
$sqlGroup = "YEAR({$dateColumn}), MONTH({$dateColumn}), DAY({$dateColumn})";
$cursorDate = new DateTime($startDate->format('Y-m-d'), $craftTimezone);
break;
case 'hour':
$sqlDateFormat = '%Y-%m-%d %H:00:00';
$phpDateFormat = 'Y-m-d H:00:00';
$sqlGroup = "YEAR({$dateColumn}), MONTH({$dateColumn}), DAY({$dateColumn}), HOUR({$dateColumn})";
$cursorDate = new DateTime($startDate->format('Y-m-d'), $craftTimezone);
break;
}
// Execute the query
$results = $query->addSelect("DATE_FORMAT({$dateColumn}, '{$sqlDateFormat}') as date")->andWhere(array('and', $dateColumn . ' >= :startDate', $dateColumn . ' < :endDate'), array(':startDate' => $startDate->mySqlDateTime(), ':endDate' => $endDate->mySqlDateTime()))->group($sqlGroup)->order($dateColumn . ' asc')->queryAll();
// Assembe the data
$rows = array();
$endTimestamp = $endDate->getTimestamp();
while ($cursorDate->getTimestamp() < $endTimestamp) {
// Do we have a record for this date?
$formattedCursorDate = $cursorDate->format($phpDateFormat, $utc);
if (isset($results[0]) && $results[0]['date'] == $formattedCursorDate) {
$value = (double) $results[0]['value'];
array_shift($results);
} else {
$value = 0;
}
$rows[] = array($formattedCursorDate, $value);
$cursorDate->modify('+1 ' . $intervalUnit);
}
return array('columns' => array(array('type' => $intervalUnit == 'hour' ? 'datetime' : 'date', 'label' => $options['categoryLabel']), array('type' => $options['valueType'], 'label' => $options['valueLabel'])), 'rows' => $rows);
}
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('submissions.formId')->join('formerly_submissions submissions', 'submissions.id = elements.id');
if ($criteria->formId) {
$query->andWhere(DbHelper::parseParam('submissions.formId', $criteria->formId, $query->params));
}
if ($criteria->form) {
$query->join('formerly_forms forms', 'forms.id = submissions.formId');
$query->andWhere(DbHelper::parseParam('formerly_forms.handle', $criteria->form, $query->params));
}
}
/**
* Modifies an element query targeting elements of this type.
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
* @return mixed
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('shortlist_item.elementId, shortlist_item.elementType, shortlist_item.listId, shortlist_list.ownerId')->join('shortlist_item shortlist_item', 'shortlist_item.id = elements.id')->join('shortlist_list shortlist_list', 'shortlist_item.listId = shortlist_list.id');
if ($criteria->listId) {
$query->andWhere(DbHelper::parseParam('shortlist_item.listId', $criteria->listId, $query->params));
}
if ($criteria->elementId) {
$query->andWhere(DbHelper::parseParam('shortlist_item.elementId', $criteria->elementId, $query->params));
}
if ($criteria->ownerId) {
$query->andWhere(DbHelper::parseParam('shortlist_list.ownerId', $criteria->ownerId, $query->params));
}
}
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('
neoblocks.fieldId,
neoblocks.ownerId,
neoblocks.ownerLocale,
neoblocks.typeId,
neoblocks.collapsed
')->join('neoblocks neoblocks', 'neoblocks.id = elements.id')->leftJoin('neoblockstructures neoblockstructures', ['and', 'neoblockstructures.ownerId = neoblocks.ownerId', 'neoblockstructures.fieldId = neoblocks.fieldId', ['or', 'neoblockstructures.ownerLocale = neoblocks.ownerLocale', ['and', 'neoblockstructures.ownerLocale is null', 'neoblocks.ownerLocale is null']]])->leftJoin('structureelements structureelements', ['and', 'structureelements.structureId = neoblockstructures.structureId', 'structureelements.elementId = neoblocks.id']);
if ($criteria->fieldId) {
$query->andWhere(DbHelper::parseParam('neoblocks.fieldId', $criteria->fieldId, $query->params));
}
if ($criteria->ownerId) {
$query->andWhere(DbHelper::parseParam('neoblocks.ownerId', $criteria->ownerId, $query->params));
}
if ($criteria->ownerLocale) {
$query->andWhere(DbHelper::parseParam('neoblocks.ownerLocale', $criteria->ownerLocale, $query->params));
}
if ($criteria->typeId) {
$query->andWhere(DbHelper::parseParam('neoblocks.typeId', $criteria->typeId, $query->params));
} else {
if ($criteria->type) {
$query->join('neoblocktypes neoblocktypes', 'neoblocktypes.id = neoblocks.typeId');
$query->andWhere(DbHelper::parseParam('neoblocktypes.handle', $criteria->type, $query->params));
}
}
}
/**
* Modifies an entries query targeting entries of this type.
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
* @return mixed
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('tags.setId, tags.name')->join('tags tags', 'tags.id = elements.id');
if ($criteria->name) {
$query->andWhere(DbHelper::parseParam('tags.name', $criteria->name, $query->params));
}
if ($criteria->setId) {
$query->andWhere(DbHelper::parseParam('tags.setId', $criteria->setId, $query->params));
}
if ($criteria->set) {
$query->join('tagsets tagsets', 'tagsets.id = tags.setId');
$query->andWhere(DbHelper::parseParam('tagsets.handle', $criteria->set, $query->params));
}
}
/**
* Get definition of specifique table
*
* @param $table The name of table
* @return array
* @author Wilker
**/
public function __get($table)
{
if (!isset($this->tables[$table])) {
$this->tables[$table] = DbCommand::table_fields($table);
}
return $this->tables[$table];
}
/**
* Modifies an element query targeting elements of this type.
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
* @return mixed
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('shortlist_list.default, shortlist_list.userSlug, shortlist_list.public, shortlist_list.type, shortlist_list.ownerId, shortlist_list.ownerType')->join('shortlist_list shortlist_list', 'shortlist_list.id = elements.id');
if ($criteria->default) {
$query->andWhere(DbHelper::parseParam('shortlist_list.default', $criteria->default, $query->params));
}
if ($criteria->userSlug) {
$query->andWhere(DbHelper::parseParam('shortlist_list.userSlug', $criteria->userSlug, $query->params));
}
if ($criteria->public) {
$query->andWhere(DbHelper::parseParam('shortlist_list.public', $criteria->public, $query->params));
}
if ($criteria->ownerId) {
$query->andWhere(DbHelper::parseParam('shortlist_list.ownerId', $criteria->ownerId, $query->params));
}
if ($criteria->ownerType) {
$query->andWhere(DbHelper::parseParam('shortlist_list.ownerType', $criteria->ownerType, $query->params));
}
}
/**
* Conectar ao banco de dados
*
* @return void
* @author Wilker
*/
protected static function connect()
{
if (self::$connection !== null) {
return;
}
self::$connection = @mysql_connect(self::$DB_HOST, self::$DB_USER, self::$DB_PASSWORD);
if (!self::$connection) {
echo '<pre>';
throw new DbConnectionException("Unable to connect to database");
echo '</pre>';
}
if (!@mysql_select_db(self::$DB_DATABASE, self::$connection)) {
echo '<pre>';
throw new DbConnectionException("Unable to select " . self::$DB_DATABASE . " database");
echo '</pre>';
}
}
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('
workflow_submissions.ownerId,
workflow_submissions.draftId,
workflow_submissions.editorId,
workflow_submissions.publisherId,
workflow_submissions.status,
workflow_submissions.notes,
workflow_submissions.dateApproved,
workflow_submissions.dateRejected,
workflow_submissions.dateRevoked
')->join('workflow_submissions workflow_submissions', 'workflow_submissions.id = elements.id');
if ($criteria->ownerId) {
$query->andWhere(DbHelper::parseParam('workflow_submissions.ownerId', $criteria->ownerId, $query->params));
}
if ($criteria->draftId) {
$query->andWhere(DbHelper::parseParam('workflow_submissions.draftId', $criteria->draftId, $query->params));
}
if ($criteria->editorId) {
$query->andWhere(DbHelper::parseParam('workflow_submissions.editorId', $criteria->editorId, $query->params));
}
if ($criteria->publisherId) {
$query->andWhere(DbHelper::parseParam('workflow_submissions.publisherId', $criteria->publisherId, $query->params));
}
if ($criteria->status) {
$query->andWhere(DbHelper::parseParam('workflow_submissions.status', $criteria->status, $query->params));
}
if ($criteria->notes) {
$query->andWhere(DbHelper::parseParam('workflow_submissions.notes', $criteria->notes, $query->params));
}
if ($criteria->dateApproved) {
$query->andWhere(DbHelper::parseDateParam('workflow_submissions.dateApproved', $criteria->dateApproved, $query->params));
}
if ($criteria->dateRejected) {
$query->andWhere(DbHelper::parseDateParam('workflow_submissions.dateRejected', $criteria->dateRejected, $query->params));
}
if ($criteria->dateRevoked) {
$query->andWhere(DbHelper::parseDateParam('workflow_submissions.dateRevoked', $criteria->dateRevoked, $query->params));
}
if ($criteria->dateCreated) {
$query->andWhere(DbHelper::parseDateParam('workflow_submissions.dateCreated', $criteria->dateCreated, $query->params));
}
}
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('supertableblocks.fieldId, supertableblocks.ownerId, supertableblocks.ownerLocale, supertableblocks.typeId, supertableblocks.sortOrder')->join('supertableblocks supertableblocks', 'supertableblocks.id = elements.id');
if ($criteria->fieldId) {
$query->andWhere(DbHelper::parseParam('supertableblocks.fieldId', $criteria->fieldId, $query->params));
}
if ($criteria->ownerId) {
$query->andWhere(DbHelper::parseParam('supertableblocks.ownerId', $criteria->ownerId, $query->params));
}
if ($criteria->ownerLocale) {
$query->andWhere(DbHelper::parseParam('supertableblocks.ownerLocale', $criteria->ownerLocale, $query->params));
}
if ($criteria->type) {
$query->join('supertableblocktypes supertableblocktypes', 'supertableblocktypes.id = supertableblocks.typeId');
$query->andWhere(DbHelper::parseParam('supertableblocktypes.handle', $criteria->type, $query->params));
}
}
protected function break_relation($object)
{
if (isset($this->options['through'])) {
$rel_table = $this->options['through'];
$local_field = $this->get_local_field();
$foreign_field = $this->get_foreign_field($this->foreign_model);
$local_id = $this->local_model->primary_key_value();
$foreign_id = $object->primary_key_value();
//TODO: fix it to a more generic way
if (isset($this->options['module'])) {
$module = $this->options['module'];
DbCommand::execute("DELETE FROM `{$rel_table}` WHERE `{$local_field}` = '{$local_id}' and `{$foreign_field}` = '{$foreign_id}' and `module` = '{$module}'");
} else {
DbCommand::execute("DELETE FROM `{$rel_table}` WHERE `{$local_field}` = '{$local_id}' and `{$foreign_field}` = '{$foreign_id}'");
}
} else {
$local_field = $this->get_foreign_field($this->local_model);
$object->{$local_field} = null;
$object->save();
}
}
/**
* @inheritDoc IFieldType::modifyElementsQuery()
*
* @param DbCommand $query
* @param mixed $value
*
* @return null|false
*/
public function modifyElementsQuery(DbCommand $query, $value)
{
if ($value == 'not :empty:') {
$value = ':notempty:';
}
if ($value == ':notempty:' || $value == ':empty:') {
$alias = 'relations_' . $this->model->handle;
$operator = $value == ':notempty:' ? '!=' : '=';
$query->andWhere("(select count({$alias}.id) from {{relations}} {$alias} where {$alias}.sourceId = elements.id and {$alias}.fieldId = :fieldId) {$operator} 0", array(':fieldId' => $this->model->id));
} else {
if ($value !== null) {
return false;
}
}
}
/**
* Modifies an element query targeting elements of this type.
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
* @return mixed
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('forms.id,
forms.fieldLayoutId,
forms.redirectEntryId,
forms.name,
forms.handle,
forms.titleFormat,
forms.submitAction,
forms.submitButton,
forms.afterSubmit,
forms.afterSubmitText,
forms.submissionEnabled,
forms.displayTabTitles,
forms.redirectUrl,
forms.sendCopy,
forms.sendCopyTo,
forms.notificationEnabled,
forms.notificationFilesEnabled,
forms.notificationRecipients,
forms.notificationSubject,
forms.confirmationSubject,
forms.notificationSenderName,
forms.confirmationSenderName,
forms.notificationSenderEmail,
forms.confirmationSenderEmail,
forms.notificationReplyToEmail,
forms.formTemplate,
forms.tabTemplate,
forms.fieldTemplate,
forms.notificationTemplate,
forms.confirmationTemplate');
$query->join('amforms_forms forms', 'forms.id = elements.id');
if ($criteria->handle) {
$query->andWhere(DbHelper::parseParam('forms.handle', $criteria->handle, $query->params));
}
}
/**
* 创建SQL语句组装实例化对象
*
* @access public
* @return object
*/
public function createCommand()
{
return DbCommand::getInstance($this);
}
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect("variants.id,variants.productId,variants.isImplicit,variants.sku,variants.price,variants.width,variants.height,variants.length,variants.weight,variants.stock,variants.unlimitedStock,variants.minQty,variants.maxQty")->join('market_variants variants', 'variants.id = elements.id');
if ($criteria->sku) {
$query->andWhere(DbHelper::parseParam('variants.sku', $criteria->sku, $query->params));
}
if ($criteria->product) {
if ($criteria->product instanceof Market_ProductModel) {
$criteria->productId = $criteria->product->id;
$criteria->product = NULL;
} else {
$query->andWhere(DbHelper::parseParam('variants.productId', $criteria->product, $query->params));
}
}
if ($criteria->productId) {
$query->andWhere(DbHelper::parseParam('variants.productId', $criteria->productId, $query->params));
}
if ($criteria->isImplicit) {
$query->andWhere(DbHelper::parseParam('variants.isImplicit', $criteria->isImplicit, $query->params));
}
}
/**
* Returns the unique element IDs that match a given element query.
*
* @param DbCommand $query
* @return array
*/
private function _getElementIdsFromQuery(DbCommand $query)
{
// Get the matched element IDs, and then have the SearchService filter them.
$elementIdsQuery = craft()->db->createCommand()->select('elements.id')->from('elements elements')->group('elements.id');
$elementIdsQuery->setWhere($query->getWhere());
$elementIdsQuery->setJoin($query->getJoin());
$elementIdsQuery->params = $query->params;
return $elementIdsQuery->queryColumn();
}
/**
* @inheritDoc IFieldType::modifyElementsQuery()
*
* @param DbCommand $query
* @param mixed $value
*
* @return null|false
*/
public function modifyElementsQuery(DbCommand $query, $value)
{
if ($value !== null) {
if ($this->defineContentAttribute()) {
$handle = $this->model->handle;
$query->andWhere(DbHelper::parseParam('content.' . craft()->content->fieldColumnPrefix . $handle, $value, $query->params));
} else {
return false;
}
}
}
/**
* Modifies an element query targeting elements of this type.
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
* @return mixed
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('events.calendarId, events.startDate, events.endDate')->join('events events', 'events.id = elements.id');
if ($criteria->calendarId) {
$query->andWhere(DbHelper::parseParam('events.calendarId', $criteria->calendarId, $query->params));
}
if ($criteria->calendar) {
$query->join('events_calendars events_calendars', 'events_calendars.id = events.calendarId');
$query->andWhere(DbHelper::parseParam('events_calendars.handle', $criteria->calendar, $query->params));
}
if ($criteria->startDate) {
$query->andWhere(DbHelper::parseDateParam('events.startDate', $criteria->startDate, $query->params));
}
if ($criteria->endDate) {
$query->andWhere(DbHelper::parseDateParam('events.endDate', $criteria->endDate, $query->params));
}
}
/**
* Modifies an entries query targeting entries of this type.
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
* @return mixed
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('entries.sectionId, entries.authorId, entries.postDate, entries.expiryDate, entries_i18n.slug')->join('entries entries', 'entries.id = elements.id')->join('entries_i18n entries_i18n', 'entries_i18n.entryId = elements.id')->andWhere('entries_i18n.locale = elements_i18n.locale');
if ($criteria->slug) {
$query->andWhere(DbHelper::parseParam('entries_i18n.slug', $criteria->slug, $query->params));
}
if ($criteria->postDate) {
$query->andWhere(DbHelper::parseDateParam('entries.postDate', '=', $criteria->postDate, $query->params));
} else {
if ($criteria->after) {
$query->andWhere(DbHelper::parseDateParam('entries.postDate', '>=', $criteria->after, $query->params));
}
if ($criteria->before) {
$query->andWhere(DbHelper::parseDateParam('entries.postDate', '<', $criteria->before, $query->params));
}
}
if ($criteria->editable) {
$user = craft()->userSession->getUser();
if (!$user) {
return false;
}
$editableSectionIds = craft()->sections->getEditableSectionIds();
$query->andWhere(array('in', 'entries.sectionId', $editableSectionIds));
$noPeerConditions = array();
foreach ($editableSectionIds as $sectionId) {
if (!$user->can('editPeerEntries:' . $sectionId)) {
$noPeerConditions[] = array('or', 'entries.sectionId != ' . $sectionId, 'entries.authorId = ' . $user->id);
}
}
if ($noPeerConditions) {
array_unshift($noPeerConditions, 'and');
$query->andWhere($noPeerConditions);
}
}
if (Craft::hasPackage(CraftPackage::PublishPro)) {
if ($criteria->sectionId) {
$query->andWhere(DbHelper::parseParam('entries.sectionId', $criteria->sectionId, $query->params));
}
if ($criteria->section) {
$query->join('sections sections', 'entries.sectionId = sections.id');
$query->andWhere(DbHelper::parseParam('sections.handle', $criteria->section, $query->params));
}
}
if (Craft::hasPackage(CraftPackage::Users)) {
if ($criteria->authorId) {
$query->andWhere(DbHelper::parseParam('entries.authorId', $criteria->authorId, $query->params));
}
if ($criteria->authorGroupId || $criteria->authorGroup) {
$query->join('usergroups_users usergroups_users', 'usergroups_users.userId = entries.authorId');
if ($criteria->authorGroupId) {
$query->andWhere(DbHelper::parseParam('usergroups_users.groupId', $criteria->authorGroupId, $query->params));
}
if ($criteria->authorGroup) {
$query->join('usergroups usergroups', 'usergroups.id = usergroups_users.groupId');
$query->andWhere(DbHelper::parseParam('usergroups.handle', $criteria->authorGroup, $query->params));
}
}
}
}
/**
* @inheritDoc IElementType::modifyElementsQuery()
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
*
* @return mixed
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('assetfiles.sourceId, assetfiles.folderId, assetfiles.filename, assetfiles.kind, assetfiles.width, assetfiles.height, assetfiles.size, assetfiles.dateModified, assetfolders.path as folderPath')->join('assetfiles assetfiles', 'assetfiles.id = elements.id')->join('assetfolders assetfolders', 'assetfolders.id = assetfiles.folderId');
if (!empty($criteria->source)) {
$query->join('assetsources assetsources', 'assetfiles.sourceId = assetsources.id');
}
if ($criteria->sourceId) {
$query->andWhere(DbHelper::parseParam('assetfiles.sourceId', $criteria->sourceId, $query->params));
}
if ($criteria->source) {
$query->andWhere(DbHelper::parseParam('assetsources.handle', $criteria->source, $query->params));
}
if ($criteria->folderId) {
if ($criteria->includeSubfolders) {
$folders = craft()->assets->getAllDescendantFolders(craft()->assets->getFolderById($criteria->folderId));
$query->andWhere(DbHelper::parseParam('assetfiles.folderId', array_keys($folders), $query->params));
} else {
$query->andWhere(DbHelper::parseParam('assetfiles.folderId', $criteria->folderId, $query->params));
}
}
if ($criteria->filename) {
$query->andWhere(DbHelper::parseParam('assetfiles.filename', $criteria->filename, $query->params));
}
if ($criteria->kind) {
if (is_array($criteria->kind)) {
$query->andWhere(DbHelper::parseParam('assetfiles.kind', array_merge(array('or'), $criteria->kind), $query->params));
} else {
$query->andWhere(DbHelper::parseParam('assetfiles.kind', $criteria->kind, $query->params));
}
}
if ($criteria->width) {
$query->andWhere(DbHelper::parseParam('assetfiles.width', $criteria->width, $query->params));
}
if ($criteria->height) {
$query->andWhere(DbHelper::parseParam('assetfiles.height', $criteria->height, $query->params));
}
if ($criteria->size) {
$query->andWhere(DbHelper::parseParam('assetfiles.size', $criteria->size, $query->params));
}
// Clear out existing onPopulateElements handlers
$criteria->detachEventHandler('onPopulateElements', array($this, 'eagerLoadTransforms'));
// Are we eager-loading any transforms?
if ($criteria->withTransforms) {
$criteria->attachEventHandler('onPopulateElements', array($this, 'eagerLoadTransforms'));
}
}
/**
* Applies WHERE conditions to a DbCommand query for folders.
*
* @param DbCommand $query
* @param FolderCriteriaModel $criteria
*
* @return null
*/
private function _applyFolderConditions($query, FolderCriteriaModel $criteria)
{
$whereConditions = array();
$whereParams = array();
if ($criteria->id) {
$whereConditions[] = DbHelper::parseParam('f.id', $criteria->id, $whereParams);
}
if ($criteria->sourceId) {
$whereConditions[] = DbHelper::parseParam('f.sourceId', $criteria->sourceId, $whereParams);
}
if ($criteria->parentId) {
$whereConditions[] = DbHelper::parseParam('f.parentId', $criteria->parentId, $whereParams);
}
if ($criteria->name) {
$whereConditions[] = DbHelper::parseParam('f.name', $criteria->name, $whereParams);
}
if (!is_null($criteria->path)) {
// This folder has a comma in it.
if (strpos($criteria->path, ',') !== false) {
// Escape the comma.
$condition = DbHelper::parseParam('f.path', str_replace(',', '\\,', $criteria->path), $whereParams);
$lastKey = key(array_slice($whereParams, -1, 1, true));
// Now un-escape it.
$whereParams[$lastKey] = str_replace('\\,', ',', $whereParams[$lastKey]);
} else {
$condition = DbHelper::parseParam('f.path', $criteria->path, $whereParams);
}
$whereConditions[] = $condition;
}
if (count($whereConditions) == 1) {
$query->where($whereConditions[0], $whereParams);
} else {
array_unshift($whereConditions, 'and');
$query->where($whereConditions, $whereParams);
}
}
/**
* @inheritDoc IElementType::modifyElementsQuery()
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
*
* @return mixed
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('assetfiles.sourceId, assetfiles.folderId, assetfiles.filename, assetfiles.kind, assetfiles.width, assetfiles.height, assetfiles.size, assetfiles.dateModified')->join('assetfiles assetfiles', 'assetfiles.id = elements.id');
if ($criteria->sourceId) {
$query->andWhere(DbHelper::parseParam('assetfiles.sourceId', $criteria->sourceId, $query->params));
}
if ($criteria->folderId) {
$query->andWhere(DbHelper::parseParam('assetfiles.folderId', $criteria->folderId, $query->params));
}
if ($criteria->filename) {
$query->andWhere(DbHelper::parseParam('assetfiles.filename', $criteria->filename, $query->params));
}
if ($criteria->kind) {
if (is_array($criteria->kind)) {
$query->andWhere(DbHelper::parseParam('assetfiles.kind', array_merge(array('or'), $criteria->kind), $query->params));
} else {
$query->andWhere(DbHelper::parseParam('assetfiles.kind', $criteria->kind, $query->params));
}
}
if ($criteria->width) {
$query->andWhere(DbHelper::parseParam('assetfiles.width', $criteria->width, $query->params));
}
if ($criteria->height) {
$query->andWhere(DbHelper::parseParam('assetfiles.height', $criteria->height, $query->params));
}
if ($criteria->size) {
$query->andWhere(DbHelper::parseParam('assetfiles.size', $criteria->size, $query->params));
}
}
/**
* Returns elements based on a given query.
*
* @access private
* @param BaseElementType $elementType
* @param DbCommand $subquery
* @param bool $ordered
* @return array
*/
private function _getElementsFromQuery(BaseElementType $elementType, DbCommand $subquery, $ordered = false)
{
if ($ordered) {
$subquery->addSelect('relations.sortOrder');
}
// Only get the unique elements (no locale duplicates)
$query = craft()->db->createCommand()->select('*')->from('(' . $subquery->getText() . ') AS ' . craft()->db->quoteTableName('r'))->group('r.id');
$query->params = $subquery->params;
if ($ordered) {
$query->order('sortOrder');
}
$result = $query->queryAll();
$elements = array();
foreach ($result as $row) {
// The locale column might be null since the element_i18n table was left-joined into the query,
// In that case it should be removed from the $row array so that the default value can be used.
if (!$row['locale']) {
unset($row['locale']);
}
$elements[] = $elementType->populateElementModel($row);
}
return $elements;
}
/**
* @inheritDoc IElementType::modifyElementsQuery()
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
*
* @return bool|false|null|void
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('entries.sectionId, entries.typeId, entries.authorId, entries.postDate, entries.expiryDate')->join('entries entries', 'entries.id = elements.id')->join('sections sections', 'sections.id = entries.sectionId')->leftJoin('structures structures', 'structures.id = sections.structureId')->leftJoin('structureelements structureelements', array('and', 'structureelements.structureId = structures.id', 'structureelements.elementId = entries.id'));
if ($criteria->ref) {
$refs = ArrayHelper::stringToArray($criteria->ref);
$conditionals = array();
foreach ($refs as $ref) {
$parts = array_filter(explode('/', $ref));
if ($parts) {
if (count($parts) == 1) {
$conditionals[] = DbHelper::parseParam('elements_i18n.slug', $parts[0], $query->params);
} else {
$conditionals[] = array('and', DbHelper::parseParam('sections.handle', $parts[0], $query->params), DbHelper::parseParam('elements_i18n.slug', $parts[1], $query->params));
}
}
}
if ($conditionals) {
if (count($conditionals) == 1) {
$query->andWhere($conditionals[0]);
} else {
array_unshift($conditionals, 'or');
$query->andWhere($conditionals);
}
}
}
if ($criteria->type) {
$typeIds = array();
if (!is_array($criteria->type)) {
$criteria->type = array($criteria->type);
}
foreach ($criteria->type as $type) {
if (is_numeric($type)) {
$typeIds[] = $type;
} else {
if (is_string($type)) {
$types = craft()->sections->getEntryTypesByHandle($type);
if ($types) {
foreach ($types as $type) {
$typeIds[] = $type->id;
}
} else {
return false;
}
} else {
if ($type instanceof EntryTypeModel) {
$typeIds[] = $type->id;
} else {
return false;
}
}
}
}
$query->andWhere(DbHelper::parseParam('entries.typeId', $typeIds, $query->params));
}
if ($criteria->postDate) {
$query->andWhere(DbHelper::parseDateParam('entries.postDate', $criteria->postDate, $query->params));
} else {
if ($criteria->after) {
$query->andWhere(DbHelper::parseDateParam('entries.postDate', '>=' . $criteria->after, $query->params));
}
if ($criteria->before) {
$query->andWhere(DbHelper::parseDateParam('entries.postDate', '<' . $criteria->before, $query->params));
}
}
if ($criteria->expiryDate) {
$query->andWhere(DbHelper::parseDateParam('entries.expiryDate', $criteria->expiryDate, $query->params));
}
if ($criteria->editable) {
$user = craft()->userSession->getUser();
if (!$user) {
return false;
}
// Limit the query to only the sections the user has permission to edit
$editableSectionIds = craft()->sections->getEditableSectionIds();
$query->andWhere(array('in', 'entries.sectionId', $editableSectionIds));
// Enforce the editPeerEntries permissions for non-Single sections
$noPeerConditions = array();
foreach (craft()->sections->getEditableSections() as $section) {
if ($section->type != SectionType::Single && !$user->can('editPeerEntries:' . $section->id)) {
$noPeerConditions[] = array('or', 'entries.sectionId != ' . $section->id, 'entries.authorId = ' . $user->id);
}
}
if ($noPeerConditions) {
array_unshift($noPeerConditions, 'and');
$query->andWhere($noPeerConditions);
}
}
if ($criteria->section) {
if ($criteria->section instanceof SectionModel) {
$criteria->sectionId = $criteria->section->id;
$criteria->section = null;
} else {
$query->andWhere(DbHelper::parseParam('sections.handle', $criteria->section, $query->params));
}
}
if ($criteria->sectionId) {
$query->andWhere(DbHelper::parseParam('entries.sectionId', $criteria->sectionId, $query->params));
}
if (craft()->getEdition() >= Craft::Client) {
if ($criteria->authorId) {
$query->andWhere(DbHelper::parseParam('entries.authorId', $criteria->authorId, $query->params));
}
if ($criteria->authorGroupId || $criteria->authorGroup) {
$query->join('usergroups_users usergroups_users', 'usergroups_users.userId = entries.authorId');
if ($criteria->authorGroupId) {
$query->andWhere(DbHelper::parseParam('usergroups_users.groupId', $criteria->authorGroupId, $query->params));
}
if ($criteria->authorGroup) {
$query->join('usergroups usergroups', 'usergroups.id = usergroups_users.groupId');
$query->andWhere(DbHelper::parseParam('usergroups.handle', $criteria->authorGroup, $query->params));
}
}
}
}
/**
* @inheritDoc IElementType::modifyElementsQuery()
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
*
* @return mixed
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('users.username, users.photo, users.firstName, users.lastName, users.email, users.admin, users.client, users.locked, users.pending, users.suspended, users.archived, users.lastLoginDate, users.lockoutDate, users.preferredLocale')->join('users users', 'users.id = elements.id');
if ($criteria->admin) {
$query->andWhere(DbHelper::parseParam('users.admin', $criteria->admin, $query->params));
}
if ($criteria->client && craft()->getEdition() == Craft::Client) {
$query->andWhere(DbHelper::parseParam('users.client', $criteria->client, $query->params));
}
if ($criteria->can && craft()->getEdition() == Craft::Pro) {
// Get the actual permission ID
if (is_numeric($criteria->can)) {
$permissionId = $criteria->can;
} else {
$permissionId = craft()->db->createCommand()->select('id')->from('userpermissions')->where('name = :name', array(':name' => strtolower($criteria->can)))->queryScalar();
}
// Find the users that have that permission, either directly or through a group
$permittedUserIds = array();
// If the permission hasn't been assigned to any groups/users before, it won't have an ID. Don't bail
// though, since we still want to look for admins.
if ($permissionId) {
// Get the user groups that have that permission
$permittedGroupIds = craft()->db->createCommand()->select('groupId')->from('userpermissions_usergroups')->where('permissionId = :permissionId', array(':permissionId' => $permissionId))->queryColumn();
if ($permittedGroupIds) {
$permittedUserIds = $this->_getUserIdsByGroupIds($permittedGroupIds);
}
// Get the users that have that permission directly
$permittedUserIds = array_merge($permittedUserIds, craft()->db->createCommand()->select('userId')->from('userpermissions_users')->where('permissionId = :permissionId', array(':permissionId' => $permissionId))->queryColumn());
}
if ($permittedUserIds) {
$permissionConditions = array('or', 'users.admin = 1', array('in', 'elements.id', $permittedUserIds));
} else {
$permissionConditions = 'users.admin = 1';
}
$query->andWhere($permissionConditions);
}
if ($criteria->groupId) {
$userIds = $this->_getUserIdsByGroupIds($criteria->groupId);
if (!$userIds) {
return false;
}
$query->andWhere(array('in', 'elements.id', $userIds));
}
if ($criteria->group) {
// Get the actual group ID(s)
$groupIdsQuery = craft()->db->createCommand()->select('id')->from('usergroups');
$groupIdsQuery->where(DbHelper::parseParam('handle', $criteria->group, $groupIdsQuery->params));
$groupIds = $groupIdsQuery->queryColumn();
// In the case where the group doesn't exist.
if (!$groupIds) {
return false;
}
$userIds = $this->_getUserIdsByGroupIds($groupIds);
// In case there are no users in the groups.
if (!$userIds) {
return false;
}
$query->andWhere(array('in', 'elements.id', $userIds));
}
if ($criteria->username) {
$query->andWhere(DbHelper::parseParam('users.username', $criteria->username, $query->params));
}
if ($criteria->firstName) {
$query->andWhere(DbHelper::parseParam('users.firstName', $criteria->firstName, $query->params));
}
if ($criteria->lastName) {
$query->andWhere(DbHelper::parseParam('users.lastName', $criteria->lastName, $query->params));
}
if ($criteria->email) {
$query->andWhere(DbHelper::parseParam('users.email', $criteria->email, $query->params));
}
if ($criteria->preferredLocale) {
$query->andWhere(DbHelper::parseParam('users.preferredLocale', $criteria->preferredLocale, $query->params));
}
if ($criteria->lastLoginDate) {
$query->andWhere(DbHelper::parseDateParam('users.lastLoginDate', $criteria->lastLoginDate, $query->params));
}
}
/**
* Search for entries by location
*
* @param DbCommand $query
* @param array $params
*/
private function _searchLocation(DbCommand &$query, $params)
{
$location = $params['location'];
$radius = array_key_exists('radius', $params) ? $params['radius'] : 50;
$unit = array_key_exists('unit', $params) ? $params['unit'] : 'kilometers';
if (!is_numeric($radius)) {
$radius = (double) $radius;
}
if (!is_numeric($radius)) {
$radius = 50;
}
if (!in_array($unit, array('km', 'mi'))) {
$unit = 'km';
}
if (is_string($location)) {
$location = $this->_getLatLngFromAddress($location);
}
if (is_array($location)) {
if (!array_key_exists('lat', $location) || !array_key_exists('lng', $location)) {
$location = null;
}
} else {
return;
}
if ($location === null) {
return;
}
if ($unit === 'km') {
$earthRad = 6371;
} else {
$earthRad = 3959;
}
$this->searchLatLng = $location;
$this->searchEarthRad = $earthRad;
$table = craft()->db->tablePrefix . SimpleMap_MapRecord::TABLE_NAME;
$haversine = "({$earthRad} * acos(cos(radians({$location['lat']})) * cos(radians({$table}.lat)) * cos(radians({$table}.lng) - radians({$location['lng']})) + sin(radians({$location['lat']})) * sin(radians({$table}.lat))))";
$query->addSelect($haversine . ' AS distance')->having('distance <= ' . $radius);
}
/**
* Modifies an element query targeting elements of this type.
*
* @param DbCommand $query
* @param ElementCriteriaModel $criteria
* @return mixed
*/
public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
{
$query->addSelect('nodes.menuId, nodes.linkedEntryId, nodes.customUrl, i18n.uri linkedEntryUrl')->join('menus_nodes nodes', 'nodes.id = elements.id')->join('menus menus', 'menus.id = nodes.menuId')->leftJoin('elements_i18n i18n', 'i18n.elementId = nodes.linkedEntryId')->leftJoin('structures structures', 'structures.id = menus.structureId')->leftJoin('structureelements structureelements', array('and', 'structureelements.structureId = structures.id', 'structureelements.elementId = nodes.id'));
if ($criteria->menuId) {
$query->andWhere(DbHelper::parseParam('nodes.menuId', $criteria->menuId, $query->params));
}
if ($criteria->menu) {
$query->andWhere(DbHelper::parseParam('menus.handle', $criteria->menu, $query->params));
}
// if ($criteria->startDate)
// {
// $query->andWhere(DbHelper::parseDateParam('entries.startDate', $criteria->startDate, $query->params));
// }
//
// if ($criteria->endDate)
// {
// $query->andWhere(DbHelper::parseDateParam('entries.endDate', $criteria->endDate, $query->params));
// }
}
