/** * @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; }