/**
* @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 $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);
}
public function testAddNonSpecificCountClause()
{
$adapter = new RedBeanModelSelectQueryAdapter();
$this->assertEquals(0, $adapter->getClausesCount());
$adapter->addNonSpecificCountClause();
$this->assertEquals(1, $adapter->getClausesCount());
$compareString = "select count(*) ";
$this->assertEquals($compareString, $adapter->getSelect());
}
