/** * @param array $components * @return string */ public function makeQueryContent(array $components) { $content = null; foreach ($components as $displayAttribute) { $modelToReportAdapter = static::makeModelToReportAdapterByComponentForm($displayAttribute); $itemBuilder = new DisplayAttributeReportItemQueryBuilder($displayAttribute, $this->joinTablesAdapter, $modelToReportAdapter, $this->selectQueryAdapter, $this->currencyConversionType); $itemBuilder->resolveComponentAttributeStringContent(); } return $this->selectQueryAdapter->getSelect(); }
public function testMakeQueryWithGroupBy() { $quote = DatabaseCompatibilityUtil::getQuote(); //Test simple query $selectQueryAdapter = new RedBeanModelSelectQueryAdapter(); $selectQueryAdapter->addClause('a', 'b', 'c'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('A'); $sql = SQLQueryUtil::makeQuery('myTable', $selectQueryAdapter, $joinTablesAdapter, 2, 4, 'a = x', null, 'a.group'); $compareSql = "select {$quote}a{$quote}.{$quote}b{$quote} c "; $compareSql .= "from {$quote}myTable{$quote} "; $compareSql .= "where a = x "; $compareSql .= "group by a.group "; $compareSql .= 'limit 4 offset 2'; $this->assertEquals($compareSql, $sql); }
public static function makeQuery($tableName, RedBeanModelSelectQueryAdapter $selectQueryAdapter, RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter, $offset = null, $count = null, $where = null, $orderBy = null, $groupBy = null) { assert('is_string($tableName) && $tableName != ""'); assert('$offset === null || is_integer($offset) && $offset >= 0'); assert('$count === null || is_integer($count) && $count >= 1'); assert('$where === null || is_string ($where) && $where != ""'); assert('$orderBy === null || is_string ($orderBy) && $orderBy != ""'); assert('$groupBy === null || is_string ($groupBy) && $groupBy != ""'); $quote = DatabaseCompatibilityUtil::getQuote(); $sql = $selectQueryAdapter->getSelect(); $sql .= "from "; //Added ( ) around from tables to ensure precedence over joins. $joinFromPart = $joinTablesAdapter->getJoinFromQueryPart(); if ($joinFromPart !== null) { $sql .= "("; $sql .= "{$quote}{$tableName}{$quote}"; $sql .= ", {$joinFromPart}) "; } else { $sql .= "{$quote}{$tableName}{$quote}"; $sql .= ' '; } $sql .= $joinTablesAdapter->getJoinQueryPart(); $joinWherePart = $joinTablesAdapter->getJoinWhereQueryPart(); if ($where !== null) { $sql .= "where {$where}"; if ($joinWherePart != null) { $sql .= " and {$joinWherePart}"; } } elseif ($joinWherePart != null) { $sql .= " where {$joinWherePart}"; } if ($groupBy !== null) { $sql .= " group by {$groupBy}"; } if ($orderBy !== null) { $sql .= " order by {$orderBy}"; } if ($count !== null) { $sql .= " limit {$count}"; } if ($offset !== null) { $sql .= " offset {$offset}"; } return $sql; }
/** * Make sure the query actually runs correctly. */ public function testASingleAttributeThatRunsFrozenQueryCorrectly() { $q = DatabaseCompatibilityUtil::getQuote(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('ReportModelTestItem'); $builder = new FiltersReportQueryBuilder($joinTablesAdapter, '1'); $filter = new FilterForReportForm('AccountsModule', 'Account', Report::TYPE_ROWS_AND_COLUMNS); $filter->attributeIndexOrDerivedType = 'ReadOptimization'; $content = $builder->makeQueryContent(array($filter)); $compareContent = "({$q}ownedsecurableitem{$q}.{$q}securableitem_id{$q} = (select securableitem_id " . "from {$q}account_read{$q} where {$q}securableitem_id{$q} = {$q}ownedsecurableitem" . "{$q}.{$q}securableitem_id{$q} and {$q}munge_id{$q} in ('U" . self::$superUserId . "', 'G" . self::$everyoneGroupId . "') limit 1))"; $this->assertEquals($compareContent, $content); $this->assertEquals(1, $joinTablesAdapter->getFromTableJoinCount()); $this->assertEquals(0, $joinTablesAdapter->getLeftTableJoinCount()); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter(); $selectQueryAdapter->addClause(Account::getTableName(), 'id'); $sql = SQLQueryUtil::makeQuery(Account::getTableName(), $selectQueryAdapter, $joinTablesAdapter, null, null, $content, null, null); $rows = ZurmoRedBean::getAll($sql); $this->assertEquals(0, count($rows)); }
protected static function makeChartSqlQuery() { $quote = DatabaseCompatibilityUtil::getQuote(); $where = null; $selectDistinct = false; $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Opportunity'); Opportunity::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $sumPart = "{$quote}currencyvalue{$quote}.{$quote}value{$quote} "; $sumPart .= "* {$quote}currencyvalue{$quote}.{$quote}ratetobase{$quote}"; $selectQueryAdapter->addClause('customfield', 'value', 'stage'); $selectQueryAdapter->addClauseByQueryString("sum({$sumPart})", 'amount'); $joinTablesAdapter->addFromTableAndGetAliasName('customfield', 'stage_customfield_id', 'opportunity'); $joinTablesAdapter->addFromTableAndGetAliasName('currencyvalue', 'amount_currencyvalue_id', 'opportunity'); $groupBy = "{$quote}customfield{$quote}.{$quote}value{$quote}"; $sql = SQLQueryUtil::makeQuery('opportunity', $selectQueryAdapter, $joinTablesAdapter, null, null, $where, null, $groupBy); return $sql; }
protected static function makeUserLeaderboardSqlQuery($type) { assert('is_string($type)'); $quote = DatabaseCompatibilityUtil::getQuote(); $where = null; $selectDistinct = false; $orderBy = "points desc"; $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('GamePointTransaction'); static::resolveLeaderboardWhereClausesByType($type, $joinTablesAdapter, $where); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $selectQueryAdapter->addClause('_user', 'id', 'userid'); $selectQueryAdapter->addSummationClause('gamepointtransaction', 'value', 'points'); $joinTablesAdapter->addFromTableAndGetAliasName('gamepoint', 'gamepoint_id', 'gamepointtransaction'); $joinTablesAdapter->addFromTableAndGetAliasName('permitable', 'person_item_id', 'gamepoint', 'item_id'); $joinTablesAdapter->addFromTableAndGetAliasName('_user', 'id', 'permitable', 'permitable_id'); $groupBy = "{$quote}_user{$quote}.{$quote}id{$quote}"; $sql = SQLQueryUtil::makeQuery('gamepointtransaction', $selectQueryAdapter, $joinTablesAdapter, null, null, $where, $orderBy, $groupBy); return $sql; }
/** * @param string $beginDateTime * @param array $searchAttributeData * @return string */ protected static function makeColumnSqlQuery($beginDateTime, $searchAttributeData) { assert('is_string($beginDateTime)'); $quote = DatabaseCompatibilityUtil::getQuote(); $where = null; $selectDistinct = false; $marketingListTableName = MarketingList::getTableName(); $marketingListMemberTableName = MarketingListMember::getTableName(); $createdDateTimeColumnName = MarketingListMember::getColumnNameByAttribute('createdDateTime'); $unsubscribedColumnName = MarketingListMember::getColumnNameByAttribute('unsubscribed'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('MarketingList'); $where = RedBeanModelDataProvider::makeWhere('MarketingList', $searchAttributeData, $joinTablesAdapter); MarketingList::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $newSubscriberSelectPart = "sum(CASE WHEN {$quote}{$marketingListMemberTableName}{$quote}.{$quote}{$createdDateTimeColumnName}" . $quote . " >= '{$beginDateTime}' THEN 1 ELSE 0 END)"; $existingSubscriberSelectPart = "sum(CASE WHEN {$quote}{$marketingListMemberTableName}{$quote}.{$quote}{$createdDateTimeColumnName}" . $quote . " < '{$beginDateTime}' AND " . "{$quote}{$marketingListMemberTableName}{$quote}.{$quote}" . "{$unsubscribedColumnName}{$quote}=0 THEN 1 ELSE 0 END)"; // Not Coding Standard $selectQueryAdapter->addClauseByQueryString($newSubscriberSelectPart, static::NEW_SUBSCRIBERS_COUNT); $selectQueryAdapter->addClauseByQueryString($existingSubscriberSelectPart, static::EXISTING_SUBSCRIBERS_COUNT); $joinTablesAdapter->addLeftTableAndGetAliasName($marketingListMemberTableName, 'id', $marketingListTableName, 'marketinglist_id'); $sql = SQLQueryUtil::makeQuery($marketingListTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where); return $sql; }
/** * Query a model's table by attributeName to get the count of attribute values. * An example usage is if you want to know how many records have a certain contact state for all states. * @param $filterByAttributeName - string identifying attribute that should be filtered on. * @param $filterByAttributeValue - string of value to filter the attribute by. * @return array of atributeValue / count pairings. */ public static function getCountData($modelClassName, $attributeName, $filterByAttributeName = null, $filterByAttributeValue = null) { assert('($filterByAttributeName == null && $filterByAttributeValue == null) || ($filterByAttributeName != null && $filterByAttributeValue != null)'); $model = new $modelClassName(); $tableName = RedBeanModel::getTableName($modelClassName); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter(); if ($model->isRelation($attributeName) && $model->getRelationType($attributeName) == RedBeanModel::HAS_MANY) { assert('$attributeName == "values"'); //until we expand support on this method. $relationModelClassName = $model->getRelationModelClassName($attributeName); $attributeTableName = RedBeanModel::getTableName($relationModelClassName); $columnName = 'value'; $relationTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($attributeTableName, 'id', $tableName, $tableName . '_id'); } else { $attributeTableName = $tableName; $columnName = $model->getColumnNameByAttribute($attributeName); } $where = null; if ($filterByAttributeName != null) { $attributeModelClassName = $model->resolveAttributeModelClassName($filterByAttributeName); $filterByAttributeTableName = RedBeanModel::getTableName($attributeModelClassName); $filterByColumnName = $model->getColumnNameByAttribute($filterByAttributeName); $where = $filterByAttributeTableName . '.' . $filterByColumnName . '=' . $filterByAttributeValue; if ($filterByAttributeTableName != $tableName) { $joinTablesAdapter->addFromTableAndGetAliasName($filterByAttributeTableName, $filterByAttributeTableName . '_id', $tableName); } } $where = $where; $selectDistinct = false; $selectQueryAdapter->addClause($attributeTableName, $columnName, 'attribute'); $selectQueryAdapter->addCountClause($tableName, 'id', 'count'); $groupBy = static::getGroupBySqlPart($attributeTableName, $columnName); $sql = SQLQueryUtil::makeQuery($tableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where, null, $groupBy); return static::runQueryBySqlAndGetCountData($sql); }
/** * @param ModelJoinBuilder $builder * @param $modelAttributeToDataProviderAdapter * @param null | string $onTableAliasName * @throws NotSupportedException if the display attribute is made via select like SUM(integer) but the * adapter being used is not a summation adapter */ protected function resolveDisplayAttributeForProcessingAllJoins(ModelJoinBuilder $builder, $modelAttributeToDataProviderAdapter, $onTableAliasName = null) { assert('$modelAttributeToDataProviderAdapter instanceof RedBeanModelAttributeToDataProviderAdapter'); assert('is_string($onTableAliasName) || $onTableAliasName == null'); $tableAliasName = $builder->resolveJoins($onTableAliasName, ModelDataProviderUtil::resolveCanUseFromJoins($onTableAliasName)); if ($this->isDisplayAttributeMadeViaSelect()) { if (!$this->modelToReportAdapter instanceof ModelRelationsAndAttributesToSummableReportAdapter) { throw new NotSupportedException(); } $this->modelToReportAdapter->resolveDisplayAttributeTypeAndAddSelectClause($this->selectQueryAdapter, $this->componentForm->getResolvedAttribute(), $tableAliasName, $this->resolveColumnName($modelAttributeToDataProviderAdapter), $this->componentForm->columnAliasName, $this->getAttributeClauseQueryStringExtraPart($tableAliasName)); } else { $tableAliasName = $this->resolvedTableAliasName($modelAttributeToDataProviderAdapter, $builder); $this->selectQueryAdapter->resolveIdClause($this->resolvedModelClassName($modelAttributeToDataProviderAdapter), $tableAliasName); $this->componentForm->setModelAliasUsingTableAliasName($tableAliasName); } }
public function testDynamicallyDerivedAttributeBothOnRelatedModelWhereDifferentAttributes() { $q = DatabaseCompatibilityUtil::getQuote(); //Related createdByUser__User and related owner__User. On same related model $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('ReportModelTestItem9'); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter(); $builder = new DisplayAttributesReportQueryBuilder($joinTablesAdapter, $selectQueryAdapter); $displayAttribute = new DisplayAttributeForReportForm('ReportsTestModule', 'ReportModelTestItem9', Report::TYPE_ROWS_AND_COLUMNS); $displayAttribute->attributeIndexOrDerivedType = 'hasOne___createdByUser__User'; $displayAttribute2 = new DisplayAttributeForReportForm('ReportsTestModule', 'ReportModelTestItem9', Report::TYPE_ROWS_AND_COLUMNS); $displayAttribute2->attributeIndexOrDerivedType = 'hasOne___owner__User'; $content = $builder->makeQueryContent(array($displayAttribute, $displayAttribute2)); $compareContent = "select {$q}reportmodeltestitem{$q}.{$q}id{$q} reportmodeltestitemid "; $this->assertEquals($compareContent, $content); $this->assertEquals(0, $joinTablesAdapter->getFromTableJoinCount()); $this->assertEquals(1, $joinTablesAdapter->getLeftTableJoinCount()); $idTableAliasesAndModelClassNames = $selectQueryAdapter->getIdTableAliasesAndModelClassNames(); $this->assertCount(1, $idTableAliasesAndModelClassNames); $this->assertEquals('ReportModelTestItem', $idTableAliasesAndModelClassNames['reportmodeltestitem']); }
/** * Resolve sql query adapter for count query. * @param RedBeanModelSelectQueryAdapter $selectQueryAdapter * @return RedBeanModelSelectQueryAdapter * @throws NotSupportedException */ protected function resolveSqlQueryAdapterForCount(RedBeanModelSelectQueryAdapter $selectQueryAdapter) { if ($selectQueryAdapter->isDistinct()) { throw new NotSupportedException(); } return $this->makeSelectQueryAdapter($selectQueryAdapter->isDistinct()); }
/** * @param $selectQueryAdapter * @param bool $selectJustCount * @return string */ protected function makeSqlQueryForFetchingTotalItemCount($selectQueryAdapter, $selectJustCount = false) { $moduleClassName = $this->report->getModuleClassName(); $modelClassName = $moduleClassName::getPrimaryModelName(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName); $this->makeDisplayAttributes($joinTablesAdapter, $selectQueryAdapter); $where = $this->makeFiltersContent($joinTablesAdapter); $orderBy = $this->makeOrderBysContent($joinTablesAdapter); $groupBy = $this->makeGroupBysContentForCount($joinTablesAdapter); //Make a fresh selectQueryAdapter that only has a count clause if ($selectJustCount) { //Currently this is always expected as false. If it is true, we need to add support for SpecificCountClauses //so we know which table/id the count is on. if ($selectQueryAdapter->isDistinct()) { throw new NotSupportedException(); } $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectQueryAdapter->isDistinct()); $selectQueryAdapter->addNonSpecificCountClause(); } return SQLQueryUtil::makeQuery($modelClassName::getTableName($modelClassName), $selectQueryAdapter, $joinTablesAdapter, null, null, $where, $orderBy, $groupBy); }
/** * @param array $searchAttributeData * @param string $groupBy * @return string */ protected static function makeAutorespondersSqlQuery($searchAttributeData, $groupBy) { $quote = DatabaseCompatibilityUtil::getQuote(); $where = null; $selectDistinct = false; $itemTableName = Item::getTableName('Item'); $marketingListTableName = Autoresponder::getTableName('MarketingList'); $autoresponderTableName = Autoresponder::getTableName('Autoresponder'); $autoresponderItemTableName = AutoresponderItem::getTableName('AutoresponderItem'); $emailMessageTableName = EmailMessage::getTableName('EmailMessage'); $sentDateTimeColumnName = EmailMessage::getColumnNameByAttribute('sentDateTime'); $createdDateTimeColumnName = Item::getColumnNameByAttribute('createdDateTime'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Autoresponder'); MarketingList::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $queuedEmailsSelectPart = "sum(CASE WHEN {$quote}{$emailMessageTableName}{$quote}.{$quote}{$sentDateTimeColumnName}" . $quote . " = '0000-00-00 00:00:00' OR {$quote}{$emailMessageTableName}{$quote}" . ".{$quote}{$sentDateTimeColumnName}{$quote} IS NULL THEN 1 ELSE 0 END)"; // Not Coding Standard $sentEmailsSelectPart = "sum(CASE WHEN {$quote}{$emailMessageTableName}{$quote}.{$quote}{$sentDateTimeColumnName}" . $quote . " > '0000-00-00 00:00:00' THEN 1 ELSE 0 END)"; $uniqueOpensSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_OPEN); $uniqueClicksSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_CLICK); $bouncedSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_BOUNCE); $optedOutSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_UNSUBSCRIBE); $selectQueryAdapter->addDayDateClause($itemTableName, $createdDateTimeColumnName, static::DAY_DATE); $selectQueryAdapter->addFirstDayOfWeekDateClause($itemTableName, $createdDateTimeColumnName, static::FIRST_DAY_OF_WEEK_DATE); $selectQueryAdapter->addFirstDayOfMonthDateClause($itemTableName, $createdDateTimeColumnName, static::FIRST_DAY_OF_MONTH_DATE); $selectQueryAdapter->addNonSpecificCountClause(); $selectQueryAdapter->addClauseByQueryString($queuedEmailsSelectPart, static::QUEUED); $selectQueryAdapter->addClauseByQueryString($sentEmailsSelectPart, static::SENT); $selectQueryAdapter->addClauseByQueryString("count((" . $uniqueOpensSelectPart . "))", static::UNIQUE_OPENS); $selectQueryAdapter->addClauseByQueryString("count((" . $uniqueClicksSelectPart . "))", static::UNIQUE_CLICKS); $selectQueryAdapter->addClauseByQueryString("count((" . $bouncedSelectPart . "))", static::BOUNCED); $selectQueryAdapter->addClauseByQueryString("count((" . $optedOutSelectPart . "))", static::UNSUBSCRIBED); $joinTablesAdapter->addFromTableAndGetAliasName($marketingListTableName, 'marketinglist_id'); $joinTablesAdapter->addLeftTableAndGetAliasName($autoresponderItemTableName, 'id', $autoresponderTableName, 'autoresponder_id'); $joinTablesAdapter->addLeftTableAndGetAliasName($emailMessageTableName, 'emailmessage_id', $autoresponderItemTableName, 'id'); $where = RedBeanModelDataProvider::makeWhere('Autoresponder', $searchAttributeData, $joinTablesAdapter); $sql = SQLQueryUtil::makeQuery($autoresponderTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where, null, $groupBy); return $sql; }
/** * @param string $type * @return string */ protected static function makeUserLeaderboardCountSqlQuery($type) { assert('is_string($type)'); $quote = DatabaseCompatibilityUtil::getQuote(); $where = '_user.hidefromleaderboard is null OR _user.hidefromleaderboard = 0'; $selectDistinct = true; $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('GamePointTransaction'); static::resolveLeaderboardWhereClausesByType($type, $where); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $selectQueryAdapter->addCountClause('_user', 'id'); $joinTablesAdapter->addFromTableAndGetAliasName('gamepoint', 'gamepoint_id', 'gamepointtransaction'); $joinTablesAdapter->addFromTableAndGetAliasName('permitable', 'person_item_id', 'gamepoint', 'item_id'); $joinTablesAdapter->addFromTableAndGetAliasName('_user', 'id', 'permitable', 'permitable_id'); $sql = SQLQueryUtil::makeQuery('gamepointtransaction', $selectQueryAdapter, $joinTablesAdapter, null, null, $where); return $sql; }
/** * Gets used color by user. * @param User $user * @param string $modelClassName * @param string $attributeName * @return array */ public static function getUsedCalendarColorsByUser(User $user, $modelClassName, $attributeName) { assert('is_string($modelClassName)'); assert('is_string($attributeName)'); $selectDistinct = false; $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $selectQueryAdapter->addClause($modelClassName::getTableName(), 'color'); $metadata = array(); $metadata['clauses'] = array(1 => array('attributeName' => $attributeName, 'relatedAttributeName' => 'id', 'operatorType' => 'equals', 'value' => $user->id)); $metadata['structure'] = '1'; $where = RedBeanModelDataProvider::makeWhere($modelClassName, $metadata, $joinTablesAdapter); $sql = SQLQueryUtil::makeQuery($modelClassName::getTableName(), $selectQueryAdapter, $joinTablesAdapter, null, null, $where); $records = ZurmoRedBean::getAll($sql); $colors = array(); foreach ($records as $record) { if ($record['color'] != null && $record['color'] != '') { $colors[] = $record['color']; } } return $colors; }
/** * @param int $type * @return string */ public static function resolveAutoresponderTypeSubQuery($type) { assert('is_int($type)'); $quote = DatabaseCompatibilityUtil::getQuote(); $where = null; $selectDistinct = true; $autoresponderItemActivityTableName = AutoresponderItemActivity::getTableName('AutoresponderItemActivity'); $emailMessageActivityTableName = EmailMessageActivity::getTableName('EmailMessageActivity'); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('AutoresponderItemActivity'); $selectQueryAdapter->addClauseByQueryString("autoresponder_id"); $joinTablesAdapter->addFromTableAndGetAliasName($emailMessageActivityTableName, 'emailmessageactivity_id', $autoresponderItemActivityTableName); $where = "type = " . $type . " and {$quote}{$autoresponderItemActivityTableName}{$quote}" . ".autoresponderitem_id = autoresponderitem.id"; $sql = SQLQueryUtil::makeQuery($autoresponderItemActivityTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where); return $sql; }
/** * @param boolean $selectCount If true then make this a count query. If false, select ids from rows. * @param array $quotedExtraSelectColumnNameAndAliases - extra columns to select. * @return string - sql statement. */ public static function makeSubsetOrCountSqlQuery($tableName, RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter, $offset = null, $count = null, $where = null, $orderBy = null, $selectCount = false, $selectDistinct = false, array $quotedExtraSelectColumnNameAndAliases = array()) { assert('is_string($tableName) && $tableName != ""'); assert('$offset === null || is_integer($offset) && $offset >= 0'); assert('$count === null || is_integer($count) && $count >= 1'); assert('$where === null || is_string ($where) && $where != ""'); assert('$orderBy === null || is_string ($orderBy) && $orderBy != ""'); assert('is_bool($selectCount)'); assert('is_bool($selectDistinct)'); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); if ($selectCount) { $selectQueryAdapter->addCountClause($tableName); } else { $selectQueryAdapter->addClause($tableName, 'id', 'id'); } foreach ($quotedExtraSelectColumnNameAndAliases as $columnName => $columnAlias) { $selectQueryAdapter->addClauseWithColumnNameOnlyAndNoEnclosure($columnName, $columnAlias); } return SQLQueryUtil::makeQuery($tableName, $selectQueryAdapter, $joinTablesAdapter, $offset, $count, $where, $orderBy); }
/** * @param string $attribute * @param string $tableName * @param string $columnName * @param null|string $queryStringExtraPart * @return string * @throws NotSupportedException if the type is invalid or null */ public function resolveOrderByStringForCalculationOrModifier($attribute, $tableName, $columnName, $queryStringExtraPart = null) { assert('is_string($attribute)'); assert('is_string($columnName)'); assert('is_string($queryStringExtraPart) || $queryStringExtraPart == null'); $type = $this->getDisplayAttributeForMakingViaSelectType($attribute); if ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_COUNT) { return RedBeanModelSelectQueryAdapter::makeCountString($tableName, $columnName); } elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_SUMMMATION) { return RedBeanModelSelectQueryAdapter::makeSummationString($tableName, $columnName, $queryStringExtraPart); } elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_AVERAGE) { return RedBeanModelSelectQueryAdapter::makeAverageString($tableName, $columnName, $queryStringExtraPart); } elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_MINIMUM) { return RedBeanModelSelectQueryAdapter::makeMinimumString($tableName, $columnName, $queryStringExtraPart); } elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_MAXIMUM) { return RedBeanModelSelectQueryAdapter::makeMaximumString($tableName, $columnName, $queryStringExtraPart); } elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_DAY) { return RedBeanModelSelectQueryAdapter::makeDayModifierString($tableName, $columnName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute)); } elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_WEEK) { return RedBeanModelSelectQueryAdapter::makeWeekModifierString($tableName, $columnName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute)); } elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_MONTH) { return RedBeanModelSelectQueryAdapter::makeMonthModifierString($tableName, $columnName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute)); } elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_QUARTER) { return RedBeanModelSelectQueryAdapter::makeQuarterModifierString($tableName, $columnName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute)); } elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_YEAR) { return RedBeanModelSelectQueryAdapter::makeYearModifierString($tableName, $columnName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute)); } else { throw new NotSupportedException(); } }
/** * Gets the date of all meetings as defined by the searchAttributeData */ public function makeSqlQuery() { assert('get_class($this->model) == "Meeting"'); $modelClassName = 'Meeting'; $quote = DatabaseCompatibilityUtil::getQuote(); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName); $where = ModelDataProviderUtil::makeWhere($modelClassName, $this->searchAttributeData, $joinTablesAdapter); $selectDistinct = false; $modelClassName::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $selectQueryAdapter->addClause('meeting', 'startdatetime'); $sql = SQLQueryUtil::makeQuery('meeting', $selectQueryAdapter, $joinTablesAdapter, null, null, $where); return $sql; }
public function testAddSummationClause() { $quote = DatabaseCompatibilityUtil::getQuote(); $adapter = new RedBeanModelSelectQueryAdapter(); $this->assertEquals(0, $adapter->getClausesCount()); $adapter->addSummationClause('abc', 'c'); $this->assertEquals(1, $adapter->getClausesCount()); $compareString = "select sum(abc) c "; $this->assertEquals($compareString, $adapter->getSelect()); $adapter = new RedBeanModelSelectQueryAdapter(true); $this->assertEquals(0, $adapter->getClausesCount()); $adapter->addSummationClause('def', 'c'); $this->assertEquals(1, $adapter->getClausesCount()); $compareString = "select distinct sum(def) c "; $this->assertEquals($compareString, $adapter->getSelect()); }
public function testAddClauseByQueryString() { $adapter = new RedBeanModelSelectQueryAdapter(); $this->assertEquals(0, $adapter->getClausesCount()); $adapter->addClauseByQueryString('querystring'); $this->assertEquals(1, $adapter->getClausesCount()); $compareString = "select querystring "; $this->assertEquals($compareString, $adapter->getSelect()); //Test with aliasName $quote = DatabaseCompatibilityUtil::getQuote(); $adapter = new RedBeanModelSelectQueryAdapter(); $this->assertEquals(0, $adapter->getClausesCount()); $adapter->addClauseByQueryString('querystring', 'aliasName'); $this->assertEquals(1, $adapter->getClausesCount()); $compareString = "select querystring aliasName "; $this->assertEquals($compareString, $adapter->getSelect()); }