public function testAddClause()
{
$quote = DatabaseCompatibilityUtil::getQuote();
$adapter = new RedBeanModelSelectQueryAdapter();
$this->assertEquals(0, $adapter->getClausesCount());
$adapter->addClause('a', 'b', 'c');
$this->assertEquals(1, $adapter->getClausesCount());
$compareString = "select {$quote}a{$quote}.{$quote}b{$quote} c ";
$this->assertEquals($compareString, $adapter->getSelect());
$adapter = new RedBeanModelSelectQueryAdapter(true);
$this->assertEquals(0, $adapter->getClausesCount());
$adapter->addClause('a', 'b', 'c');
$this->assertEquals(1, $adapter->getClausesCount());
$compareString = "select distinct {$quote}a{$quote}.{$quote}b{$quote} c ";
$this->assertEquals($compareString, $adapter->getSelect());
//Test with multiple clauses
$quote = DatabaseCompatibilityUtil::getQuote();
$adapter = new RedBeanModelSelectQueryAdapter();
$this->assertEquals(0, $adapter->getClausesCount());
$adapter->addClause('a', 'b', 'c');
$adapter->addClause('d', 'e', 'f');
$this->assertEquals(2, $adapter->getClausesCount());
$compareString = "select {$quote}a{$quote}.{$quote}b{$quote} c, {$quote}d{$quote}.{$quote}e{$quote} f ";
$this->assertEquals($compareString, $adapter->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);
}
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));
}
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;
}
/**
* 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 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 RedBeanModelSelectQueryAdapter $selectQueryAdapter
* @param string $attribute
* @param string $tableName
* @param string $columnName
* @param string $columnAliasName
* @param null|string $queryStringExtraPart
*/
public function resolveDisplayAttributeTypeAndAddSelectClause(RedBeanModelSelectQueryAdapter $selectQueryAdapter, $attribute, $tableName, $columnName, $columnAliasName, $queryStringExtraPart = null)
{
assert('is_string($attribute)');
assert('is_string($columnAliasName)');
assert('is_string($queryStringExtraPart) || $queryStringExtraPart == null');
$type = $this->getDisplayAttributeForMakingViaSelectType($attribute);
if ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_COUNT) {
$selectQueryAdapter->addCountClause($tableName, $columnName, $columnAliasName);
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_SUMMMATION) {
$selectQueryAdapter->addSummationClause($tableName, $columnName, $columnAliasName, $queryStringExtraPart);
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_AVERAGE) {
$selectQueryAdapter->addAverageClause($tableName, $columnName, $columnAliasName, $queryStringExtraPart);
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_MINIMUM) {
$selectQueryAdapter->addMinimumClause($tableName, $columnName, $columnAliasName, $queryStringExtraPart);
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::DISPLAY_CALCULATION_MAXIMUM) {
$selectQueryAdapter->addMaximumClause($tableName, $columnName, $columnAliasName, $queryStringExtraPart);
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_DAY) {
$selectQueryAdapter->addDayClause($tableName, $columnName, $columnAliasName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute));
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_WEEK) {
$selectQueryAdapter->addWeekClause($tableName, $columnName, $columnAliasName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute));
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_MONTH) {
$selectQueryAdapter->addMonthClause($tableName, $columnName, $columnAliasName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute));
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_QUARTER) {
$selectQueryAdapter->addQuarterClause($tableName, $columnName, $columnAliasName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute));
} elseif ($type == ModelRelationsAndAttributesToSummableReportAdapter::GROUP_BY_CALCULATION_YEAR) {
$selectQueryAdapter->addYearClause($tableName, $columnName, $columnAliasName, $this->shouldDoTimeZoneAdjustmentOnModifierClause($attribute));
} else {
$selectQueryAdapter->addClause($tableName, $columnName, $columnAliasName);
}
}
/**
* 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;
}
