/**
* @param $modelClassName
* @param $attributeModelClassName
* @return string
* @throws NotSupportedException
*/
private function processFromJoinsForAttributeThatIsCastedUp($modelClassName, $attributeModelClassName)
{
assert('is_string($modelClassName)');
assert('is_string($attributeModelClassName)');
$attributeTableName = $attributeModelClassName::getTableName($attributeModelClassName);
$tableAliasName = $attributeTableName;
$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 (!$this->joinTablesAdapter->isTableInFromTables($castedUpAttributeTableName)) {
if ($castedDownModelClassName::getCanHaveBean()) {
$onTableAliasName = $castedDownModelClassName::getTableName($castedDownModelClassName);
} elseif ($castedDownFurtherModelClassName::getCanHaveBean()) {
$onTableAliasName = $castedDownModelClassName::getTableName($castedDownFurtherModelClassName);
} else {
throw new NotSupportedException();
}
$onTableAliasName = $this->joinTablesAdapter->addFromTableAndGetAliasName($castedUpAttributeTableName, self::resolveForeignKey($castedUpAttributeTableName), $onTableAliasName);
}
}
}
if (!$this->joinTablesAdapter->isTableInFromTables($attributeTableName)) {
$modelClassName = static::resolveModelClassNameThatCanHaveTable($modelClassName, $castedDownModelClassName);
$tableAliasName = $this->joinTablesAdapter->addFromTableAndGetAliasName($attributeTableName, self::resolveForeignKey($attributeTableName), $modelClassName::getTableName($modelClassName));
}
return $tableAliasName;
}
/**
* For a give User name, run a partial search by
* full name and retrieve user models.
*
*/
public static function getUsersByPartialFullName($partialName, $pageSize)
{
assert('is_string($partialName)');
assert('is_int($pageSize)');
$personTableName = RedBeanModel::getTableName('Person');
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('User');
$joinTablesAdapter->addFromTableAndGetAliasName($personTableName, "{$personTableName}_id");
$fullNameSql = DatabaseCompatibilityUtil::concat(array('person.firstname', '\' \'', 'person.lastname'));
$where = " (person.firstname like lower('{$partialName}%') or " . " person.lastname like lower('{$partialName}%') or " . " {$fullNameSql} like lower('{$partialName}%')) ";
return User::getSubset($joinTablesAdapter, null, $pageSize, $where, "person.firstname, person.lastname");
}
public static function getModelsByFullName($modelClassName, $fullName)
{
assert('is_string($modelClassName)');
assert('is_subclass_of($modelClassName, "Person") && $modelClassName != "Person" ||' . '$modelClassName == "User"');
assert('is_string($fullName)');
$personTableName = RedBeanModel::getTableName('Person');
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter($modelClassName);
$joinTablesAdapter->addFromTableAndGetAliasName($personTableName, "{$personTableName}_id");
$fullNameSql = DatabaseCompatibilityUtil::concat(array('person.firstname', '\' \'', 'person.lastname'));
$where = "{$fullNameSql} = '{$fullName}'";
return $modelClassName::getSubset($joinTablesAdapter, null, null, $where);
}
/**
* Given a model class name, return all the derived attributes based on the called class.
* @param string $modelClassName
*/
public static function getAllByModelClassName($modelClassName)
{
assert('$modelClassName != ""');
$derivedAttirbuteMetadataTableName = RedBeanModel::getTableName('DerivedAttributeMetadata');
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter(get_called_class());
$joinTablesAdapter->addFromTableAndGetAliasName($derivedAttirbuteMetadataTableName, "{$derivedAttirbuteMetadataTableName}_id");
$where = "{$derivedAttirbuteMetadataTableName}.modelclassname = '{$modelClassName}'";
$models = static::getSubset($joinTablesAdapter, null, null, $where);
if (count($models) == 0) {
return array();
}
return $models;
}
/**
* For a give User name, run a partial search by
* full name and retrieve user models.
* @param $partialName
* @param $pageSize
* @param $autoCompleteOptions
* @return Array
*/
public static function getUsersByPartialFullName($partialName, $pageSize, $autoCompleteOptions = null)
{
assert('is_string($partialName)');
assert('is_int($pageSize)');
static::sanitizeSearchTerm($partialName);
$personTableName = Person::getTableName();
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('User');
$joinTablesAdapter->addFromTableAndGetAliasName($personTableName, "{$personTableName}_id");
$fullNameSql = DatabaseCompatibilityUtil::concat(array('person.firstname', '\' \'', 'person.lastname'));
$where = '(_user.hidefromselecting is null OR _user.hidefromselecting = 0) and ';
$where .= " (person.firstname like lower('{$partialName}%') or " . " person.lastname like lower('{$partialName}%') or " . " {$fullNameSql} like lower('{$partialName}%')) ";
static::handleAutoCompleteOptions($joinTablesAdapter, $where, $autoCompleteOptions);
return User::getSubset($joinTablesAdapter, null, $pageSize, $where, "person.firstname, person.lastname");
}
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}";
}
/**
* For a give Contact name, run a partial search by
* full name and retrieve contact models.
*
*/
public static function getContactsByPartialFullName($partialName, $pageSize, $stateMetadataAdapterClassName = null)
{
assert('is_string($partialName)');
assert('is_int($pageSize)');
assert('$stateMetadataAdapterClassName == null || is_string($stateMetadataAdapterClassName)');
$personTableName = RedBeanModel::getTableName('Person');
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Contact');
if (!$joinTablesAdapter->isTableInFromTables('person')) {
$joinTablesAdapter->addFromTableAndGetAliasName($personTableName, "{$personTableName}_id");
}
$metadata = array('clauses' => array(), 'structure' => '');
if ($stateMetadataAdapterClassName != null) {
$stateMetadataAdapter = new $stateMetadataAdapterClassName($metadata);
$metadata = $stateMetadataAdapter->getAdaptedDataProviderMetadata();
$metadata['structure'] = '(' . $metadata['structure'] . ')';
}
$where = RedBeanModelDataProvider::makeWhere('Contact', $metadata, $joinTablesAdapter);
if ($where != null) {
$where .= 'and';
}
$where .= self::getWherePartForPartialNameSearchByPartialName($partialName);
return Contact::getSubset($joinTablesAdapter, null, $pageSize, $where, "person.firstname, person.lastname");
}
/**
* 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 testAddFromTable()
{
$quote = DatabaseCompatibilityUtil::getQuote();
$adapter = new RedBeanModelJoinTablesQueryAdapter('QueryFromModel');
$alias = $adapter->addFromTableAndGetAliasName('zz', 'somejoinid');
$this->assertEquals(1, $adapter->getFromTableJoinCount());
$this->assertEquals(0, $adapter->getLeftTableJoinCount());
$this->assertEquals('zz', $alias);
$fromPart = $adapter->getJoinFromQueryPart();
$joinPart = $adapter->getJoinQueryPart();
$wherePart = $adapter->getJoinWhereQueryPart();
$compareFromPart = "{$quote}zz{$quote}";
$compareJoinPart = null;
$compareWherePart = "{$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote}";
$this->assertEquals($compareFromPart, $fromPart);
$this->assertEquals($compareJoinPart, $joinPart);
$this->assertEquals($compareWherePart, $wherePart);
//Add second from table
$alias = $adapter->addFromTableAndGetAliasName('a', 'somejoinid');
$this->assertEquals(2, $adapter->getFromTableJoinCount());
$this->assertEquals(0, $adapter->getLeftTableJoinCount());
$this->assertEquals('a', $alias);
$fromPart = $adapter->getJoinFromQueryPart();
$joinPart = $adapter->getJoinQueryPart();
$wherePart = $adapter->getJoinWhereQueryPart();
$compareFromPart = "{$quote}zz{$quote}, {$quote}a{$quote}";
$compareJoinPart = null;
$compareWherePart = "{$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and ";
$compareWherePart .= "{$quote}a{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote}";
$this->assertEquals($compareFromPart, $fromPart);
$this->assertEquals($compareJoinPart, $joinPart);
$this->assertEquals($compareWherePart, $wherePart);
//add third from table that was a table alread added
$alias = $adapter->addFromTableAndGetAliasName('zz', 'somejoinid');
$this->assertEquals(3, $adapter->getFromTableJoinCount());
$this->assertEquals(0, $adapter->getLeftTableJoinCount());
$this->assertEquals('zz1', $alias);
$fromPart = $adapter->getJoinFromQueryPart();
$joinPart = $adapter->getJoinQueryPart();
$wherePart = $adapter->getJoinWhereQueryPart();
$compareFromPart = "{$quote}zz{$quote}, {$quote}a{$quote}, {$quote}zz{$quote} zz1";
$compareJoinPart = null;
$compareWherePart = "{$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and ";
$compareWherePart .= "{$quote}a{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and ";
$compareWherePart .= "{$quote}zz1{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote}";
$this->assertEquals($compareFromPart, $fromPart);
$this->assertEquals($compareJoinPart, $joinPart);
$this->assertEquals($compareWherePart, $wherePart);
//Add a fourth table, that is a left join.
$alias = $adapter->addLeftTableAndGetAliasName('z', 'joinid');
$this->assertEquals(3, $adapter->getFromTableJoinCount());
$this->assertEquals(1, $adapter->getLeftTableJoinCount());
$this->assertEquals('z', $alias);
$fromPart = $adapter->getJoinFromQueryPart();
$joinPart = $adapter->getJoinQueryPart();
$wherePart = $adapter->getJoinWhereQueryPart();
$compareFromPart = "{$quote}zz{$quote}, {$quote}a{$quote}, {$quote}zz{$quote} zz1";
$compareJoinPart = "left join {$quote}z{$quote} ";
$compareJoinPart .= "on {$quote}z{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}joinid{$quote} ";
$compareWherePart = "{$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and ";
$compareWherePart .= "{$quote}a{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and ";
$compareWherePart .= "{$quote}zz1{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote}";
$this->assertEquals($compareFromPart, $fromPart);
$this->assertEquals($compareJoinPart, $joinPart);
$this->assertEquals($compareWherePart, $wherePart);
//Add a fifth table, that is a left join of an existing from table.
$alias = $adapter->addLeftTableAndGetAliasName('queryfrommodel', 'joinid');
$this->assertEquals('queryfrommodel1', $alias);
$this->assertEquals(3, $adapter->getFromTableJoinCount());
$this->assertEquals(2, $adapter->getLeftTableJoinCount());
$fromPart = $adapter->getJoinFromQueryPart();
$joinPart = $adapter->getJoinQueryPart();
$wherePart = $adapter->getJoinWhereQueryPart();
$compareFromPart = "{$quote}zz{$quote}, {$quote}a{$quote}, {$quote}zz{$quote} zz1";
$compareJoinPart = "left join {$quote}z{$quote}";
$compareJoinPart .= " on {$quote}z{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}joinid{$quote}";
$compareJoinPart .= " left join {$quote}queryfrommodel{$quote} queryfrommodel1";
$compareJoinPart .= " on {$quote}queryfrommodel1{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}joinid{$quote} ";
$compareWherePart = "{$quote}zz{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and ";
$compareWherePart .= "{$quote}a{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote} and ";
$compareWherePart .= "{$quote}zz1{$quote}.{$quote}id{$quote} = {$quote}queryfrommodel{$quote}.{$quote}somejoinid{$quote}";
$this->assertEquals($compareFromPart, $fromPart);
$this->assertEquals($compareJoinPart, $joinPart);
$this->assertEquals($compareWherePart, $wherePart);
//Add a sixth table that exists already to demonstrate the alias working for a third table.
//You must use a different join id so that it creates a new alias. using cjoinid instead of joinid
$alias = $adapter->addLeftTableAndGetAliasName('queryfrommodel', 'cjoinid');
$this->assertEquals('queryfrommodel2', $alias);
//If we just use joinid, then it will not create a new alias.
$alias = $adapter->addLeftTableAndGetAliasName('queryfrommodel', 'joinid');
$this->assertEquals('queryfrommodel1', $alias);
$alias = $adapter->addLeftTableAndGetAliasName('queryfrommodel', 'cjoinid');
$this->assertEquals('queryfrommodel2', $alias);
}
/**
* @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 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;
}
/**
* Override of the parent method using RedBean.
* @param $category
* @param $languageCode
* @return array
*/
protected function loadMessagesFromDb($category, $languageCode)
{
assert('is_string($category)');
assert('is_string($languageCode)');
$sourceTableName = MessageSource::getTableName();
$joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('MessageTranslation');
$joinTablesAdapter->addFromTableAndGetAliasName($sourceTableName, "{$sourceTableName}_id");
$where = " messagesource.`category` = '{$category}' AND" . " messagetranslation.`language` = '{$languageCode}' ";
$beans = MessageTranslation::getSubset($joinTablesAdapter, null, null, $where);
$messages = array();
foreach ($beans as $bean) {
$messages[$bean->messagesource->source] = $bean->translation;
}
return $messages;
}
/**
* @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;
}
/**
* For both non related and related attributes, this method resolves whether a from join is needed. This occurs
* for example if a model attribute is castedUp. And that attribute is a relation that needs to be joined in
* order to search. Since that attribute is castedUp, the castedUp model needs to be from joined first. This
* also applies if the attribute is not a relation and just a member on the castedUp model. In that scenario,
* the castedUp model also needs to be joined.
*
* This methhod assumes if the attribute is not on the base model, that it is casted up not down from it.
*/
public static function resolveShouldAddFromTableAndGetAliasName(RedBeanModelAttributeToDataProviderAdapter $modelAttributeToDataProviderAdapter, RedBeanModelJoinTablesQueryAdapter $joinTablesAdapter)
{
$attributeTableName = $modelAttributeToDataProviderAdapter->getAttributeTableName();
$tableAliasName = $attributeTableName;
if ($modelAttributeToDataProviderAdapter->getModelClassName() == 'User' && $modelAttributeToDataProviderAdapter->getAttributeModelClassName() == 'Person') {
$modelTableName = $modelAttributeToDataProviderAdapter->getModelTableName();
if (!$joinTablesAdapter->isTableInFromTables('person')) {
$personTableName = $attributeTableName;
$joinTablesAdapter->addFromTableAndGetAliasName($personTableName, "{$personTableName}_id", $modelTableName);
}
} elseif ($modelAttributeToDataProviderAdapter->getAttributeModelClassName() != $modelAttributeToDataProviderAdapter->getModelClassName()) {
$modelClassName = $modelAttributeToDataProviderAdapter->getModelClassName();
while (get_parent_class($modelClassName) != $modelAttributeToDataProviderAdapter->getAttributeModelClassName()) {
$castedDownModelClassName = $modelClassName;
$modelClassName = get_parent_class($modelClassName);
$castedUpAttributeTableName = $modelClassName::getTableName($modelClassName);
if (!$joinTablesAdapter->isTableInFromTables($castedUpAttributeTableName)) {
$joinTablesAdapter->addFromTableAndGetAliasName($castedUpAttributeTableName, "{$castedUpAttributeTableName}_id", $castedDownModelClassName::getTableName($castedDownModelClassName));
}
}
if (!$joinTablesAdapter->isTableInFromTables($attributeTableName)) {
$tableAliasName = $joinTablesAdapter->addFromTableAndGetAliasName($attributeTableName, "{$attributeTableName}_id", $modelClassName::getTableName($modelClassName));
}
}
return $tableAliasName;
}
public function testGetAlreadyFromJoinedTableAliasName()
{
$adapter = new RedBeanModelJoinTablesQueryAdapter('QueryFromModel');
$this->assertNull($adapter->GetAlreadyFromJoinedTableAliasName('zz'));
$alias = $adapter->addFromTableAndGetAliasName('z', 'joinid');
$this->assertEquals($alias, $adapter->getAlreadyFromJoinedTableAliasName('z'));
}
