/**
  * @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);
 }
示例#3
0
 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;
 }
示例#6
0
 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());
 }
示例#12
0
 /**
  * @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;
 }
示例#15
0
 /**
  * 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;
 }
示例#17
0
 /**
  * @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());
 }