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);
}
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;
}
/**
* 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));
}
/**
* @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);
}
protected function makeSqlQueryForGrandTotals()
{
try {
$selectQueryAdapter = $this->makeSelectQueryAdapter();
$moduleClassName = $this->report->getModuleClassName();
$modelClassName = $moduleClassName::getPrimaryModelName();
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName);
$builder = new DisplayAttributesReportQueryBuilder($joinTablesAdapter, $selectQueryAdapter, $this->report->getCurrencyConversionType());
$builder->makeQueryContent($this->getDisplayAttributesForGrandTotals());
$where = $this->makeFiltersContent($joinTablesAdapter);
$orderBy = null;
$groupBy = $this->makeGroupBysContentForGrandTotals($joinTablesAdapter);
$offset = null;
$limit = null;
$sql = SQLQueryUtil::makeQuery($modelClassName::getTableName(), $selectQueryAdapter, $joinTablesAdapter, $offset, $limit, $where, $orderBy, $groupBy);
} catch (NotSupportedException $exception) {
return null;
}
return $sql;
}
/**
* @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;
}
/**
* @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 $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 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);
}
/**
* 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;
}
/**
* @param array $searchAttributeData
* @param string $groupBy
* @return string
*/
protected static function makeAutorespondersSqlQuery($searchAttributeData, $groupBy)
{
$quote = DatabaseCompatibilityUtil::getQuote();
$where = null;
$selectDistinct = false;
$marketingListTableName = MarketingList::getTableName();
$autoresponderTableName = Autoresponder::getTableName();
$autoresponderItemTableName = AutoresponderItem::getTableName();
$emailMessageTableName = EmailMessage::getTableName();
$sentDateTimeColumnName = EmailMessage::getColumnNameByAttribute('sentDateTime');
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Autoresponder');
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
$uniqueOpensSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_OPEN);
$uniqueClicksSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_CLICK);
static::addEmailMessageDayDateClause($selectQueryAdapter, $sentDateTimeColumnName);
static::addEmailMessageFirstDayOfWeekDateClause($selectQueryAdapter, $sentDateTimeColumnName);
static::addEmailMessageFirstDayOfMonthDateClause($selectQueryAdapter, $sentDateTimeColumnName);
$selectQueryAdapter->addNonSpecificCountClause();
$selectQueryAdapter->addClauseByQueryString("count((" . $uniqueOpensSelectPart . "))", static::UNIQUE_OPENS);
$selectQueryAdapter->addClauseByQueryString("count((" . $uniqueClicksSelectPart . "))", static::UNIQUE_CLICKS);
$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);
MarketingList::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct);
$sql = SQLQueryUtil::makeQuery($autoresponderTableName, $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;
}
protected function makeSqlQueryForRowTotals()
{
$selectQueryAdapter = new RedBeanModelSelectQueryAdapter();
$moduleClassName = $this->report->getModuleClassName();
$modelClassName = $moduleClassName::getPrimaryModelName();
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName);
$builder = new DisplayAttributesReportQueryBuilder($joinTablesAdapter, $selectQueryAdapter, $this->report->getCurrencyConversionType());
$builder->makeQueryContent($this->getDisplayAttributesForGrandTotals());
$where = $this->makeFiltersContent($joinTablesAdapter);
$orderBy = null;
$builder = new GroupBysReportQueryBuilder($joinTablesAdapter);
$groupBy = $builder->makeQueryContent($this->getYAxisGroupBys());
$offset = null;
$limit = null;
return SQLQueryUtil::makeQuery($modelClassName::getTableName($modelClassName), $selectQueryAdapter, $joinTablesAdapter, $offset, $limit, $where, $orderBy, $groupBy);
}
/**
* 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;
}