/** * @param array $searchAttributeData * @param string $groupBy * @return string */ protected static function makeCampaignsSqlQuery($searchAttributeData, $groupBy) { $quote = DatabaseCompatibilityUtil::getQuote(); $where = null; $selectDistinct = false; $campaignTableName = Campaign::getTableName('Campaign'); $campaignItemTableName = CampaignItem::getTableName('CampaignItem'); $itemTableName = Item::getTableName('Item'); $emailMessageTableName = EmailMessage::getTableName('EmailMessage'); $sentDateTimeColumnName = EmailMessage::getColumnNameByAttribute('sentDateTime'); $createdDateTimeColumnName = Item::getColumnNameByAttribute('createdDateTime'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Campaign'); $where = RedBeanModelDataProvider::makeWhere('Campaign', $searchAttributeData, $joinTablesAdapter); Campaign::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::resolveCampaignTypeSubQuery(EmailMessageActivity::TYPE_OPEN); $uniqueClicksSelectPart = static::resolveCampaignTypeSubQuery(EmailMessageActivity::TYPE_CLICK); $bouncedSelectPart = static::resolveCampaignTypeSubQuery(EmailMessageActivity::TYPE_BOUNCE); $optedOutSelectPart = static::resolveCampaignTypeSubQuery(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->addLeftTableAndGetAliasName($campaignItemTableName, 'id', $campaignTableName, 'campaign_id'); $joinTablesAdapter->addLeftTableAndGetAliasName($emailMessageTableName, 'emailmessage_id', $campaignItemTableName, 'id'); $sql = SQLQueryUtil::makeQuery($campaignTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where, null, $groupBy); return $sql; }
/** * @param array $searchAttributeData * @param string $groupBy * @return string */ protected static function makeCampaignsSqlQuery($searchAttributeData, $groupBy) { $quote = DatabaseCompatibilityUtil::getQuote(); $where = null; $selectDistinct = false; $campaignTableName = Campaign::getTableName(); $campaignItemTableName = CampaignItem::getTableName(); $emailMessageTableName = EmailMessage::getTableName(); $sentDateTimeColumnName = EmailMessage::getColumnNameByAttribute('sentDateTime'); $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Campaign'); $where = RedBeanModelDataProvider::makeWhere('Campaign', $searchAttributeData, $joinTablesAdapter); Campaign::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct); $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct); $uniqueOpensSelectPart = static::resolveCampaignTypeSubQuery(EmailMessageActivity::TYPE_OPEN); $uniqueClicksSelectPart = static::resolveCampaignTypeSubQuery(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->addLeftTableAndGetAliasName($campaignItemTableName, 'id', $campaignTableName, 'campaign_id'); $joinTablesAdapter->addLeftTableAndGetAliasName($emailMessageTableName, 'emailmessage_id', $campaignItemTableName, 'id'); $sql = SQLQueryUtil::makeQuery($campaignTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where, null, $groupBy); return $sql; }