All Data Structures Functions Variables Pages
AutoresponderGroupedChartDataProvider.php
1 <?php
2  /*********************************************************************************
3  * Zurmo is a customer relationship management program developed by
4  * Zurmo, Inc. Copyright (C) 2017 Zurmo Inc.
5  *
6  * Zurmo is free software; you can redistribute it and/or modify it under
7  * the terms of the GNU Affero General Public License version 3 as published by the
8  * Free Software Foundation with the addition of the following permission added
9  * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
10  * IN WHICH THE COPYRIGHT IS OWNED BY ZURMO, ZURMO DISCLAIMS THE WARRANTY
11  * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
12  *
13  * Zurmo is distributed in the hope that it will be useful, but WITHOUT
14  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
15  * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
16  * details.
17  *
18  * You should have received a copy of the GNU Affero General Public License along with
19  * this program; if not, see http://www.gnu.org/licenses or write to the Free
20  * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
21  * 02110-1301 USA.
22  *
23  * You can contact Zurmo, Inc. with a mailing address at 27 North Wacker Drive
24  * Suite 370 Chicago, IL 60606. or at email address contact@zurmo.com.
25  *
26  * The interactive user interfaces in original and modified versions
27  * of this program must display Appropriate Legal Notices, as required under
28  * Section 5 of the GNU Affero General Public License version 3.
29  *
30  * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
31  * these Appropriate Legal Notices must retain the display of the Zurmo
32  * logo and Zurmo copyright notice. If the display of the logo is not reasonably
33  * feasible for technical reasons, the Appropriate Legal Notices must display the words
34  * "Copyright Zurmo Inc. 2017. All rights reserved".
35  ********************************************************************************/
36 
38  {
39  protected $autoresponder;
40 
41  public function __construct(Autoresponder $autoresponder)
42  {
43  assert('$autoresponder->id > 0');
44  $this->autoresponder = $autoresponder;
45  }
46 
47  public function getChartData()
48  {
49  $sql = static::makeSqlQuery(static::makeSearchAttributeData($this->autoresponder));
50  $row = ZurmoRedBean::getRow($sql);
51  $data = static::resolveChartDataBaseGroupElements();
52  foreach ($data as $index => $notUsed)
53  {
54  if ($row[$index] != null)
55  {
56  $data[$index] = $row[$index];
57  }
58  }
59  return $data;
60  }
61 
66  protected static function makeSearchAttributeData(Autoresponder $autoresponder)
67  {
68  assert('$autoresponder->id > 0');
69  $searchAttributeData = array();
70  $searchAttributeData['clauses'][1] = array(
71  'attributeName' => 'id',
72  'operatorType' => 'equals',
73  'value' => $autoresponder->id);
74  $searchAttributeData['clauses'][2] = array(
75  'attributeName' => 'autoresponderItems',
76  'relatedModelData' => array(
77  'attributeName' => 'processed',
78  'operatorType' => 'equals',
79  'value' => true,
80  ));
81  $searchAttributeData['structure'] = '1 and 2';
82  return $searchAttributeData;
83  }
84 
85  protected static function makeSqlQuery($searchAttributeData)
86  {
88  $where = null;
89  $selectDistinct = false;
90  $autoresponderTableName = Autoresponder::getTableName();
91  $autoresponderItemTableName = AutoresponderItem::getTableName();
92  $emailMessageTableName = EmailMessage::getTableName();
93  $sentDateTimeColumnName = EmailMessage::getColumnNameByAttribute('sentDateTime');
94  $joinTablesAdapter = new RedBeanModelJoinTablesQueryAdapter('Autoresponder');
95  $selectQueryAdapter = new RedBeanModelSelectQueryAdapter($selectDistinct);
96  $queuedEmailsSelectPart = "sum(CASE WHEN {$quote}{$emailMessageTableName}{$quote}.{$quote}{$sentDateTimeColumnName}" .
97  $quote . " = '0000-00-00 00:00:00' OR {$quote}{$emailMessageTableName}{$quote}" .
98  ".{$quote}{$sentDateTimeColumnName}{$quote} IS NULL THEN 1 ELSE 0 END)"; // Not Coding Standard
99  $sentEmailsSelectPart = "sum(CASE WHEN {$quote}{$emailMessageTableName}{$quote}.{$quote}{$sentDateTimeColumnName}" .
100  $quote . " > '0000-00-00 00:00:00' THEN 1 ELSE 0 END)";
101  $uniqueOpensSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_OPEN);
102  $uniqueClicksSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_CLICK);
103  $bouncedSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_BOUNCE);
104  $optedOutSelectPart = static::resolveAutoresponderTypeSubQuery(EmailMessageActivity::TYPE_UNSUBSCRIBE);
105  static::addEmailMessageDayDateClause ($selectQueryAdapter, $sentDateTimeColumnName);
106  static::addEmailMessageFirstDayOfWeekDateClause ($selectQueryAdapter, $sentDateTimeColumnName);
107  static::addEmailMessageFirstDayOfMonthDateClause($selectQueryAdapter, $sentDateTimeColumnName);
108  $selectQueryAdapter->addNonSpecificCountClause();
109  $selectQueryAdapter->addClauseByQueryString($queuedEmailsSelectPart, static::QUEUED);
110  $selectQueryAdapter->addClauseByQueryString($sentEmailsSelectPart, static::SENT);
111  $selectQueryAdapter->addClauseByQueryString("count((" . $uniqueOpensSelectPart . "))", static::UNIQUE_OPENS);
112  $selectQueryAdapter->addClauseByQueryString("count((" . $uniqueClicksSelectPart . "))", static::UNIQUE_CLICKS);
113  $selectQueryAdapter->addClauseByQueryString("count((" . $bouncedSelectPart . "))", static::BOUNCED);
114  $selectQueryAdapter->addClauseByQueryString("count((" . $optedOutSelectPart . "))", static::UNSUBSCRIBED);
115  $joinTablesAdapter->addLeftTableAndGetAliasName($autoresponderItemTableName, 'id', $autoresponderTableName, 'autoresponder_id');
116  $joinTablesAdapter->addLeftTableAndGetAliasName($emailMessageTableName, 'emailmessage_id', $autoresponderItemTableName, 'id');
117  $where = RedBeanModelDataProvider::makeWhere('Autoresponder', $searchAttributeData, $joinTablesAdapter);
118  $sql = SQLQueryUtil::makeQuery($autoresponderTableName, $selectQueryAdapter, $joinTablesAdapter, null, null, $where);
119  return $sql;
120  }
121 
122  protected static function resolveChartDataBaseGroupElements()
123  {
124  return array(self::QUEUED => 0,
125  self::SENT => 0,
126  self::UNIQUE_CLICKS => 0,
127  self::UNIQUE_OPENS => 0,
128  self::BOUNCED => 0,
129  self::UNSUBSCRIBED => 0);
130  }
131  }
132 ?>
static makeWhere($modelClassName, array $metadata, &$joinTablesAdapter)
static getColumnNameByAttribute($attributeName)
Definition: BeanModel.php:387
static getTableName()
static makeSearchAttributeData(Autoresponder $autoresponder)
Generated on Thu May 28 2020 07:10:28