protected function resolveExtraSql(RedBeanModelJoinTablesQueryAdapter &$joinTablesAdapter, &$where)
{
if ($where != '') {
$where .= ' and ';
}
$user = Yii::app()->user->userModel;
$quote = DatabaseCompatibilityUtil::getQuote();
$starredTableName = StarredUtil::getStarredTableName($this->modelClassName);
$extraOnQueryPart = "and {$quote}{$starredTableName}{$quote}.{$quote}user_id{$quote} = {$user->id}";
$starredTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($starredTableName, 'id', null, 'model_id', $extraOnQueryPart);
$where .= "{$quote}{$starredTableAliasName}{$quote}.{$quote}user_id{$quote} = {$user->id}";
}
protected function resolveExtraSql(RedBeanModelJoinTablesQueryAdapter &$joinTablesAdapter, &$where)
{
$user = Yii::app()->user->userModel;
$quote = DatabaseCompatibilityUtil::getQuote();
if ($where != '') {
$where .= ' and ';
}
$modelClassName = $this->modelClassName;
$starredClassName = StarredUtil::getStarredModelClassName($modelClassName);
$starredTableName = $starredClassName::getTableName();
$modelIdColumnName = $modelClassName::getTableName() . '_id';
$baseStarredTableName = BaseStarredModel::getTableName();
$baseStarredColumnName = $baseStarredTableName . '_id';
$starredTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($starredTableName, 'id', null, $modelIdColumnName);
$baseStarredTableAliasName = $joinTablesAdapter->addFromTableAndGetAliasName($baseStarredTableName, $baseStarredColumnName, $starredTableAliasName);
$where .= "{$quote}{$baseStarredTableAliasName}{$quote}.{$quote}_user_id{$quote} = {$user->id}";
}
/**
* @param $onTableAliasName
* @param $modelClassName
* @param $attributeModelClassName
* @return null|string
* @throws NotSupportedException
*/
private function processLeftJoinsForAttributeThatIsCastedUp($onTableAliasName, $modelClassName, $attributeModelClassName)
{
assert('is_string($onTableAliasName)');
assert('is_string($modelClassName)');
assert('is_string($attributeModelClassName)');
$attributeTableName = $attributeModelClassName::getTableName($attributeModelClassName);
$castedDownModelClassName = $modelClassName;
while (get_parent_class($modelClassName) != $attributeModelClassName && get_parent_class($modelClassName) != 'RedBeanModel') {
$castedDownFurtherModelClassName = $castedDownModelClassName;
$castedDownModelClassName = $modelClassName;
$modelClassName = get_parent_class($modelClassName);
if ($modelClassName::getCanHaveBean()) {
$castedUpAttributeTableName = $modelClassName::getTableName($modelClassName);
/**
if ($castedDownModelClassName::getCanHaveBean())
{
$resolvedTableJoinIdName = $castedDownModelClassName::getTableName($castedDownModelClassName);
}
elseif ($castedDownFurtherModelClassName::getCanHaveBean())
{
$resolvedTableJoinIdName = $castedDownModelClassName::getTableName($castedDownFurtherModelClassName);
}
else
{
throw new NotSupportedException();
}
* */
$onTableAliasName = $this->joinTablesAdapter->addLeftTableAndGetAliasName($castedUpAttributeTableName, self::resolveForeignKey($castedUpAttributeTableName), $onTableAliasName);
//,
//$resolvedTableJoinIdName);
}
}
//Add left table if it is not already added
$modelClassName = static::resolveModelClassNameThatCanHaveTable($modelClassName, $castedDownModelClassName);
$onTableAliasName = $this->joinTablesAdapter->addLeftTableAndGetAliasName($attributeTableName, self::resolveForeignKey($attributeTableName), $onTableAliasName);
//,
//$modelClassName::getTableName($modelClassName));
return $onTableAliasName;
}
/**
* 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);
}
public function testAddLeftTableAndGetAliasNameWithSpecifiedOnTableAliasName()
{
$quote = DatabaseCompatibilityUtil::getQuote();
$adapter = new RedBeanModelJoinTablesQueryAdapter('QueryFromModel');
$alias = $adapter->addLeftTableAndGetAliasName('zz', 'joinid');
$this->assertEquals('zz', $alias);
$this->assertEquals(0, $adapter->getFromTableJoinCount());
$this->assertEquals(1, $adapter->getLeftTableJoinCount());
$fromPart = $adapter->getJoinFromQueryPart();
$joinPart = $adapter->getJoinQueryPart();
$wherePart = $adapter->getJoinWhereQueryPart();
$compareFromPart = null;
$compareJoinPart = "left join {$quote}zz{$quote} ";
$compareJoinPart .= "on {$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}joinid{$quote} ";
$compareWherePart = null;
$this->assertEquals($compareFromPart, $fromPart);
$this->assertEquals($compareJoinPart, $joinPart);
$this->assertEquals($compareWherePart, $wherePart);
//Now add a specified onTableAliasName
$alias = $adapter->addLeftTableAndGetAliasName('xyz', 'ajoinid', 'zz');
$this->assertEquals('xyz', $alias);
$this->assertEquals(0, $adapter->getFromTableJoinCount());
$this->assertEquals(2, $adapter->getLeftTableJoinCount());
$fromPart = $adapter->getJoinFromQueryPart();
$joinPart = $adapter->getJoinQueryPart();
$wherePart = $adapter->getJoinWhereQueryPart();
$compareFromPart = null;
$compareJoinPart = "left join {$quote}zz{$quote} ";
$compareJoinPart .= "on {$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}joinid{$quote} ";
$compareJoinPart .= "left join {$quote}xyz{$quote} ";
$compareJoinPart .= "on {$quote}xyz{$quote}.{$quote}id{$quote} = {$quote}zz{$quote}.{$quote}ajoinid{$quote} ";
$compareWherePart = null;
$this->assertEquals($compareFromPart, $fromPart);
$this->assertEquals($compareJoinPart, $joinPart);
$this->assertEquals($compareWherePart, $wherePart);
}
/**
* @param User $user
* @param RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter
* @param $where
* @param $selectDistinct
* @throws NotSupportedException
*/
public static function resolveReadPermissionsOptimizationToSqlQuery(User $user, RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter, &$where, &$selectDistinct)
{
assert('$where == null || is_string($where)');
assert('is_bool($selectDistinct)');
$modelClassName = get_called_class();
$moduleClassName = $modelClassName::getModuleClassName();
//Currently only adds munge if the module is securable and this model supports it.
if (static::hasReadPermissionsOptimization() && $moduleClassName != null && is_subclass_of($moduleClassName, 'SecurableModule')) {
$permission = PermissionsUtil::getActualPermissionDataForReadByModuleNameForUser($moduleClassName);
if (($permission == Permission::NONE || $permission == Permission::DENY) && !static::bypassReadPermissionsOptimizationToSqlQueryBasedOnWhere($where)) {
$quote = DatabaseCompatibilityUtil::getQuote();
$modelAttributeToDataProviderAdapter = new OwnedSecurableItemIdToDataProviderAdapter($modelClassName, null);
$builder = new ModelJoinBuilder($modelAttributeToDataProviderAdapter, $joinTablesAdapter);
$ownedTableAliasName = $builder->resolveJoins();
$ownerColumnName = static::getForeignKeyName('OwnedSecurableItem', 'owner');
$mungeIds = AllPermissionsOptimizationUtil::getMungeIdsByUser($user);
if ($where != null) {
$where = '(' . $where . ') and ';
}
if (count($mungeIds) > 0 && $permission == Permission::NONE) {
$extraOnQueryPart = " and {$quote}munge_id{$quote} in ('" . join("', '", $mungeIds) . "')";
$mungeTableName = ReadPermissionsOptimizationUtil::getMungeTableName($modelClassName);
$mungeTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($mungeTableName, 'securableitem_id', $ownedTableAliasName, 'securableitem_id', $extraOnQueryPart);
$where .= "({$quote}{$ownedTableAliasName}{$quote}.{$quote}{$ownerColumnName}{$quote} = {$user->id} OR ";
// Not Coding Standard
$where .= "{$quote}{$mungeTableName}{$quote}.{$quote}munge_id{$quote} IS NOT NULL)";
// Not Coding Standard
$selectDistinct = true;
//must use distinct since adding munge table query.
} elseif ($permission == Permission::DENY) {
$where .= "{$quote}{$ownedTableAliasName}{$quote}.{$quote}{$ownerColumnName}{$quote} = {$user->id}";
// Not Coding Standard
} else {
throw new NotSupportedException();
}
}
}
}
/**
* @param array $searchAttributeData
* @param string $groupBy
* @return string
*/
protected static function makeAutorespondersSqlQuery($searchAttributeData, $groupBy)
{
$quote = DatabaseCompatibilityUtil::getQuote();
$where = null;
$selectDistinct = false;
$itemTableName = Item::getTableName();
$marketingListTableName = MarketingList::getTableName();
$autoresponderTableName = Autoresponder::getTableName();
$autoresponderItemTableName = AutoresponderItem::getTableName();
$emailMessageTableName = EmailMessage::getTableName();
$sentDateTimeColumnName = EmailMessage::getColumnNameByAttribute('sentDateTime');
$createdDateTimeColumnName = Item::getColumnNameByAttribute('createdDateTime');
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Autoresponder');
$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);
MarketingList::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct);
$sql = SQLQueryUtil::makeQuery($autoresponderTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where, null, $groupBy);
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 resolveJoinsForRelatedAttributeAndGetRelationAttributeTableAliasName(RedBeanModelAttributeToDataProviderAdapter $modelAttributeToDataProviderAdapter, RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter, $onTableAliasName)
{
assert('$modelAttributeToDataProviderAdapter->getRelationType() != RedBeanModel::MANY_MANY');
assert('is_string($onTableAliasName)');
if ($modelAttributeToDataProviderAdapter->getRelationType() == RedBeanModel::HAS_MANY || $modelAttributeToDataProviderAdapter->getRelationType() == RedBeanModel::HAS_MANY_BELONGS_TO) {
$onTableJoinIdName = 'id';
$tableJoinIdName = $onTableAliasName . '_id';
//HAS_MANY have the potential to produce more than one row per model, so we need
//to signal the query to be distinct.
if ($modelAttributeToDataProviderAdapter->getRelationType() == RedBeanModel::HAS_MANY) {
$joinTablesAdapter->setSelectDistinctToTrue();
}
} elseif ($modelAttributeToDataProviderAdapter->getRelationType() == RedBeanModel::HAS_ONE_BELONGS_TO) {
$tableJoinIdName = $onTableAliasName . '_id';
$onTableJoinIdName = 'id';
} else {
$onTableJoinIdName = $modelAttributeToDataProviderAdapter->getColumnName();
$tableJoinIdName = 'id';
}
$relationTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($modelAttributeToDataProviderAdapter->getRelationTableName(), $onTableJoinIdName, $onTableAliasName, $tableJoinIdName);
$relationAttributeTableAliasName = $relationTableAliasName;
//the second left join check being performed is if you
//are in a contact filtering on related account email as an example.
if ($modelAttributeToDataProviderAdapter->getRelatedAttributeModelClassName() != $modelAttributeToDataProviderAdapter->getRelationModelClassName()) {
$relationAttributeTableName = $modelAttributeToDataProviderAdapter->getRelatedAttributeTableName();
//Handling special scenario for casted down Person. Todo: Automatically determine a
//casted down scenario instead of specifically looking for Person.
if ($modelAttributeToDataProviderAdapter->getRelatedAttributeModelClassName() == 'Person') {
$onTableJoinIdName = "{$relationAttributeTableName}_id";
} elseif (get_parent_class($modelAttributeToDataProviderAdapter->getRelationModelClassName()) == $modelAttributeToDataProviderAdapter->getRelatedAttributeModelClassName()) {
$onTableJoinIdName = "{$relationAttributeTableName}_id";
} else {
$onTableJoinIdName = "{$modelAttributeToDataProviderAdapter->getRelatedAttributeColumnName()}" . "_{$relationAttributeTableName}_id";
}
$relationAttributeTableAliasName = $joinTablesAdapter->addLeftTableAndGetAliasName($relationAttributeTableName, $onTableJoinIdName, $relationTableAliasName);
}
return $relationAttributeTableAliasName;
}
