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