/**
  * @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;
 }
 protected static function makeChartSqlQuery()
 {
     $quote = DatabaseCompatibilityUtil::getQuote();
     $where = null;
     $selectDistinct = false;
     $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Opportunity');
     Opportunity::resolveReadPermissionsOptimizationToSqlQuery(Yii::app()->user->userModel, $joinTablesAdapter, $where, $selectDistinct);
     $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
     $sumPart = "{$quote}currencyvalue{$quote}.{$quote}value{$quote} ";
     $sumPart .= "* {$quote}currencyvalue{$quote}.{$quote}ratetobase{$quote}";
     $selectQueryAdapter->addClause('customfield', 'value', 'stage');
     $selectQueryAdapter->addClauseByQueryString("sum({$sumPart})", 'amount');
     $joinTablesAdapter->addFromTableAndGetAliasName('customfield', 'stage_customfield_id', 'opportunity');
     $joinTablesAdapter->addFromTableAndGetAliasName('currencyvalue', 'amount_currencyvalue_id', 'opportunity');
     $groupBy = "{$quote}customfield{$quote}.{$quote}value{$quote}";
     $sql = SQLQueryUtil::makeQuery('opportunity', $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;
     $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 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;
 }
 public function testAddClauseByQueryString()
 {
     $adapter = new RedBeanModelSelectQueryAdapter();
     $this->assertEquals(0, $adapter->getClausesCount());
     $adapter->addClauseByQueryString('querystring');
     $this->assertEquals(1, $adapter->getClausesCount());
     $compareString = "select querystring ";
     $this->assertEquals($compareString, $adapter->getSelect());
     //Test with aliasName
     $quote = DatabaseCompatibilityUtil::getQuote();
     $adapter = new RedBeanModelSelectQueryAdapter();
     $this->assertEquals(0, $adapter->getClausesCount());
     $adapter->addClauseByQueryString('querystring', 'aliasName');
     $this->assertEquals(1, $adapter->getClausesCount());
     $compareString = "select querystring aliasName ";
     $this->assertEquals($compareString, $adapter->getSelect());
 }