Commit | Line | Data |
---|---|---|
6a488035 | 1 | <?php |
6a488035 TO |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
6a488035 | 5 | | | |
bc77d7c0 TO |
6 | | This work is published under the GNU AGPLv3 license with some | |
7 | | permitted exceptions and without any warranty. For full license | | |
8 | | and copyright information, see https://civicrm.org/licensing | | |
6a488035 | 9 | +--------------------------------------------------------------------+ |
d25dd0ee | 10 | */ |
6a488035 TO |
11 | |
12 | /** | |
13 | * | |
14 | * @package CRM | |
ca5cec67 | 15 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
6a488035 TO |
16 | */ |
17 | class CRM_Report_Form_Mailing_Summary extends CRM_Report_Form { | |
18 | ||
19 | protected $_summary = NULL; | |
20 | ||
be2fb01f | 21 | protected $_customGroupExtends = []; |
6a488035 | 22 | |
c233c222 BS |
23 | protected $_add2groupSupported = FALSE; |
24 | ||
be2fb01f | 25 | public $_drilldownReport = ['mailing/detail' => 'Link to Detail Report']; |
6a488035 | 26 | |
74cf4551 | 27 | /** |
73b448bf | 28 | * Class constructor. |
74cf4551 | 29 | */ |
00be9182 | 30 | public function __construct() { |
be2fb01f | 31 | $this->_columns = []; |
6a488035 | 32 | |
be2fb01f | 33 | $this->_columns['civicrm_mailing'] = [ |
6a488035 | 34 | 'dao' => 'CRM_Mailing_DAO_Mailing', |
be2fb01f CW |
35 | 'fields' => [ |
36 | 'id' => [ | |
6c288074 PN |
37 | 'name' => 'id', |
38 | 'title' => ts('Mailing ID'), | |
39 | 'required' => TRUE, | |
40 | 'no_display' => TRUE, | |
be2fb01f CW |
41 | ], |
42 | 'name' => [ | |
6a488035 TO |
43 | 'title' => ts('Mailing Name'), |
44 | 'required' => TRUE, | |
be2fb01f CW |
45 | ], |
46 | 'created_date' => [ | |
6a488035 | 47 | 'title' => ts('Date Created'), |
be2fb01f CW |
48 | ], |
49 | 'subject' => [ | |
180d3835 | 50 | 'title' => ts('Subject'), |
be2fb01f | 51 | ], |
892b1e5c | 52 | 'from_name' => [ |
53 | 'title' => ts('Sender Name'), | |
54 | ], | |
55 | 'from_email' => [ | |
56 | 'title' => ts('Sender Email'), | |
57 | ], | |
be2fb01f CW |
58 | ], |
59 | 'filters' => [ | |
60 | 'is_completed' => [ | |
6a488035 TO |
61 | 'title' => ts('Mailing Status'), |
62 | 'operatorType' => CRM_Report_Form::OP_SELECT, | |
63 | 'type' => CRM_Utils_Type::T_INT, | |
be2fb01f | 64 | 'options' => [ |
6a488035 TO |
65 | 0 => 'Incomplete', |
66 | 1 => 'Complete', | |
be2fb01f | 67 | ], |
6a488035 TO |
68 | //'operator' => 'like', |
69 | 'default' => 1, | |
be2fb01f CW |
70 | ], |
71 | 'mailing_id' => [ | |
a5ca7737 | 72 | 'name' => 'id', |
230b8080 | 73 | 'title' => ts('Mailing Name'), |
6a488035 | 74 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, |
a5ca7737 J |
75 | 'type' => CRM_Utils_Type::T_INT, |
76 | 'options' => CRM_Mailing_BAO_Mailing::getMailingsList(), | |
6a488035 | 77 | 'operator' => 'like', |
be2fb01f CW |
78 | ], |
79 | 'mailing_subject' => [ | |
230b8080 BS |
80 | 'name' => 'subject', |
81 | 'title' => ts('Mailing Subject'), | |
df28e44e | 82 | 'type' => CRM_Utils_Type::T_STRING, |
230b8080 | 83 | 'operator' => 'like', |
be2fb01f CW |
84 | ], |
85 | ], | |
86 | 'order_bys' => [ | |
87 | 'mailing_name' => [ | |
576922b4 BS |
88 | 'name' => 'name', |
89 | 'title' => ts('Mailing Name'), | |
be2fb01f CW |
90 | ], |
91 | 'mailing_subject' => [ | |
576922b4 BS |
92 | 'name' => 'subject', |
93 | 'title' => ts('Mailing Subject'), | |
be2fb01f CW |
94 | ], |
95 | ], | |
96 | ]; | |
6a488035 | 97 | |
be2fb01f | 98 | $this->_columns['civicrm_mailing_job'] = [ |
cbe30f5a | 99 | 'dao' => 'CRM_Mailing_DAO_MailingJob', |
be2fb01f CW |
100 | 'fields' => [ |
101 | 'start_date' => [ | |
6a488035 | 102 | 'title' => ts('Start Date'), |
b529e413 | 103 | 'dbAlias' => 'MIN(mailing_job_civireport.start_date)', |
be2fb01f CW |
104 | ], |
105 | 'end_date' => [ | |
6a488035 | 106 | 'title' => ts('End Date'), |
b529e413 | 107 | 'dbAlias' => 'MAX(mailing_job_civireport.end_date)', |
be2fb01f CW |
108 | ], |
109 | ], | |
110 | 'filters' => [ | |
111 | 'status' => [ | |
6a488035 TO |
112 | 'type' => CRM_Utils_Type::T_STRING, |
113 | 'default' => 'Complete', | |
114 | 'no_display' => TRUE, | |
be2fb01f CW |
115 | ], |
116 | 'is_test' => [ | |
6a488035 TO |
117 | 'type' => CRM_Utils_Type::T_INT, |
118 | 'default' => 0, | |
119 | 'no_display' => TRUE, | |
be2fb01f CW |
120 | ], |
121 | 'start_date' => [ | |
6a488035 TO |
122 | 'title' => ts('Start Date'), |
123 | 'default' => 'this.year', | |
124 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
125 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f CW |
126 | ], |
127 | 'end_date' => [ | |
6a488035 TO |
128 | 'title' => ts('End Date'), |
129 | 'default' => 'this.year', | |
130 | 'operatorType' => CRM_Report_Form::OP_DATE, | |
131 | 'type' => CRM_Utils_Type::T_DATE, | |
be2fb01f CW |
132 | ], |
133 | ], | |
134 | 'order_bys' => [ | |
135 | 'start_date' => [ | |
576922b4 | 136 | 'title' => ts('Start Date'), |
b2818554 | 137 | 'dbAlias' => 'MIN(mailing_job_civireport.start_date)', |
be2fb01f CW |
138 | ], |
139 | 'end_date' => [ | |
576922b4 BS |
140 | 'title' => ts('End Date'), |
141 | 'default_weight' => '1', | |
142 | 'default_order' => 'DESC', | |
b2818554 | 143 | 'dbAlias' => 'MAX(mailing_job_civireport.end_date)', |
be2fb01f CW |
144 | ], |
145 | ], | |
4ff9e8de | 146 | 'grouping' => 'mailing-fields', |
be2fb01f | 147 | ]; |
6a488035 | 148 | |
be2fb01f | 149 | $this->_columns['civicrm_mailing_event_queue'] = [ |
6a488035 | 150 | 'dao' => 'CRM_Mailing_DAO_Mailing', |
be2fb01f CW |
151 | 'fields' => [ |
152 | 'queue_count' => [ | |
6a488035 TO |
153 | 'name' => 'id', |
154 | 'title' => ts('Intended Recipients'), | |
be2fb01f CW |
155 | ], |
156 | ], | |
157 | ]; | |
6a488035 | 158 | |
be2fb01f | 159 | $this->_columns['civicrm_mailing_event_delivered'] = [ |
6a488035 | 160 | 'dao' => 'CRM_Mailing_DAO_Mailing', |
be2fb01f CW |
161 | 'fields' => [ |
162 | 'delivered_count' => [ | |
7ff385e8 | 163 | 'name' => 'event_queue_id', |
6a488035 | 164 | 'title' => ts('Delivered'), |
be2fb01f CW |
165 | ], |
166 | 'accepted_rate' => [ | |
fd6a6828 | 167 | 'title' => ts('Accepted Rate'), |
be2fb01f | 168 | 'statistics' => [ |
6a488035 TO |
169 | 'calc' => 'PERCENTAGE', |
170 | 'top' => 'civicrm_mailing_event_delivered.delivered_count', | |
171 | 'base' => 'civicrm_mailing_event_queue.queue_count', | |
be2fb01f CW |
172 | ], |
173 | ], | |
174 | ], | |
175 | ]; | |
6a488035 | 176 | |
be2fb01f | 177 | $this->_columns['civicrm_mailing_event_bounce'] = [ |
6a488035 | 178 | 'dao' => 'CRM_Mailing_DAO_Mailing', |
be2fb01f CW |
179 | 'fields' => [ |
180 | 'bounce_count' => [ | |
7ff385e8 | 181 | 'name' => 'event_queue_id', |
6a488035 | 182 | 'title' => ts('Bounce'), |
be2fb01f CW |
183 | ], |
184 | 'bounce_rate' => [ | |
fd6a6828 | 185 | 'title' => ts('Bounce Rate'), |
be2fb01f | 186 | 'statistics' => [ |
6a488035 TO |
187 | 'calc' => 'PERCENTAGE', |
188 | 'top' => 'civicrm_mailing_event_bounce.bounce_count', | |
189 | 'base' => 'civicrm_mailing_event_queue.queue_count', | |
be2fb01f CW |
190 | ], |
191 | ], | |
192 | ], | |
193 | ]; | |
6a488035 | 194 | |
be2fb01f | 195 | $this->_columns['civicrm_mailing_event_opened'] = [ |
6a488035 | 196 | 'dao' => 'CRM_Mailing_DAO_Mailing', |
be2fb01f CW |
197 | 'fields' => [ |
198 | 'unique_open_count' => [ | |
aa6b3363 PN |
199 | 'name' => 'id', |
200 | 'alias' => 'mailing_event_opened_civireport', | |
201 | 'dbAlias' => 'mailing_event_opened_civireport.event_queue_id', | |
202 | 'title' => ts('Unique Opens'), | |
be2fb01f CW |
203 | ], |
204 | 'unique_open_rate' => [ | |
fd6a6828 | 205 | 'title' => ts('Unique Open Rate'), |
be2fb01f | 206 | 'statistics' => [ |
aa6b3363 PN |
207 | 'calc' => 'PERCENTAGE', |
208 | 'top' => 'civicrm_mailing_event_opened.unique_open_count', | |
209 | 'base' => 'civicrm_mailing_event_delivered.delivered_count', | |
be2fb01f CW |
210 | ], |
211 | ], | |
212 | 'open_count' => [ | |
7ff385e8 | 213 | 'name' => 'event_queue_id', |
aa6b3363 | 214 | 'title' => ts('Total Opens'), |
be2fb01f CW |
215 | ], |
216 | 'open_rate' => [ | |
fd6a6828 | 217 | 'title' => ts('Total Open Rate'), |
be2fb01f | 218 | 'statistics' => [ |
6a488035 TO |
219 | 'calc' => 'PERCENTAGE', |
220 | 'top' => 'civicrm_mailing_event_opened.open_count', | |
221 | 'base' => 'civicrm_mailing_event_delivered.delivered_count', | |
be2fb01f CW |
222 | ], |
223 | ], | |
224 | ], | |
225 | ]; | |
6a488035 | 226 | |
be2fb01f | 227 | $this->_columns['civicrm_mailing_event_trackable_url_open'] = [ |
6a488035 | 228 | 'dao' => 'CRM_Mailing_DAO_Mailing', |
be2fb01f CW |
229 | 'fields' => [ |
230 | 'click_count' => [ | |
7ff385e8 | 231 | 'name' => 'event_queue_id', |
d58cca13 | 232 | 'title' => ts('Unique Clicks'), |
be2fb01f CW |
233 | ], |
234 | 'CTR' => [ | |
fd6a6828 | 235 | 'title' => ts('Click through Rate'), |
6a488035 | 236 | 'default' => 0, |
be2fb01f | 237 | 'statistics' => [ |
6a488035 TO |
238 | 'calc' => 'PERCENTAGE', |
239 | 'top' => 'civicrm_mailing_event_trackable_url_open.click_count', | |
240 | 'base' => 'civicrm_mailing_event_delivered.delivered_count', | |
be2fb01f CW |
241 | ], |
242 | ], | |
243 | 'CTO' => [ | |
fd6a6828 | 244 | 'title' => ts('Click to Open Rate'), |
6a488035 | 245 | 'default' => 0, |
be2fb01f | 246 | 'statistics' => [ |
6a488035 TO |
247 | 'calc' => 'PERCENTAGE', |
248 | 'top' => 'civicrm_mailing_event_trackable_url_open.click_count', | |
249 | 'base' => 'civicrm_mailing_event_opened.open_count', | |
be2fb01f CW |
250 | ], |
251 | ], | |
252 | ], | |
253 | ]; | |
6a488035 | 254 | |
be2fb01f | 255 | $this->_columns['civicrm_mailing_event_unsubscribe'] = [ |
6a488035 | 256 | 'dao' => 'CRM_Mailing_DAO_Mailing', |
be2fb01f CW |
257 | 'fields' => [ |
258 | 'unsubscribe_count' => [ | |
6a488035 TO |
259 | 'name' => 'id', |
260 | 'title' => ts('Unsubscribe'), | |
aa6b3363 PN |
261 | 'alias' => 'mailing_event_unsubscribe_civireport', |
262 | 'dbAlias' => 'mailing_event_unsubscribe_civireport.event_queue_id', | |
be2fb01f CW |
263 | ], |
264 | 'optout_count' => [ | |
a1ad2b04 PN |
265 | 'name' => 'id', |
266 | 'title' => ts('Opt-outs'), | |
267 | 'alias' => 'mailing_event_optout_civireport', | |
268 | 'dbAlias' => 'mailing_event_optout_civireport.event_queue_id', | |
be2fb01f CW |
269 | ], |
270 | ], | |
271 | ]; | |
272 | $this->_columns['civicrm_mailing_group'] = [ | |
b4dfc663 | 273 | 'dao' => 'CRM_Mailing_DAO_MailingGroup', |
be2fb01f CW |
274 | 'filters' => [ |
275 | 'entity_id' => [ | |
b4dfc663 JM |
276 | 'title' => ts('Groups Included in Mailing'), |
277 | 'operatorType' => CRM_Report_Form::OP_MULTISELECT, | |
278 | 'type' => CRM_Utils_Type::T_INT, | |
279 | 'options' => CRM_Core_PseudoConstant::group(), | |
be2fb01f CW |
280 | ], |
281 | ], | |
282 | ]; | |
8a4cd1f8 SL |
283 | // If we have campaigns enabled, add those elements to both the fields, filters. |
284 | $this->addCampaignFields('civicrm_mailing'); | |
f787b3c4 BT |
285 | |
286 | // Add charts support | |
287 | $this->_charts = [ | |
288 | '' => ts('Tabular'), | |
289 | 'barChart' => ts('Bar Chart'), | |
290 | ]; | |
291 | ||
6a488035 TO |
292 | parent::__construct(); |
293 | } | |
294 | ||
00be9182 | 295 | public function preProcess() { |
6a488035 TO |
296 | $this->assign('chartSupported', TRUE); |
297 | parent::preProcess(); | |
298 | } | |
299 | ||
546b78fa | 300 | /** |
fe482240 | 301 | * manipulate the select function to query count functions. |
546b78fa | 302 | */ |
00be9182 | 303 | public function select() { |
6a488035 | 304 | |
be2fb01f | 305 | $count_tables = [ |
6a488035 TO |
306 | 'civicrm_mailing_event_queue', |
307 | 'civicrm_mailing_event_delivered', | |
308 | 'civicrm_mailing_event_opened', | |
309 | 'civicrm_mailing_event_bounce', | |
310 | 'civicrm_mailing_event_trackable_url_open', | |
311 | 'civicrm_mailing_event_unsubscribe', | |
be2fb01f | 312 | ]; |
6a488035 | 313 | |
1b08b468 AS |
314 | // Define a list of columns that should be counted with the DISTINCT |
315 | // keyword. For example, civicrm_mailing_event_opened.unique_open_count | |
316 | // should display the number of unique records, whereas something like | |
317 | // civicrm_mailing_event_opened.open_count should display the total number. | |
318 | // Each string here is in the form $tableName.$fieldName, where $tableName | |
319 | // is the key in $this->_columns, and $fieldName is the key in that array's | |
320 | // ['fields'] array. | |
321 | // Reference: CRM-20660 | |
be2fb01f | 322 | $distinctCountColumns = [ |
1b08b468 AS |
323 | 'civicrm_mailing_event_queue.queue_count', |
324 | 'civicrm_mailing_event_delivered.delivered_count', | |
325 | 'civicrm_mailing_event_bounce.bounce_count', | |
326 | 'civicrm_mailing_event_opened.unique_open_count', | |
327 | 'civicrm_mailing_event_trackable_url_open.click_count', | |
328 | 'civicrm_mailing_event_unsubscribe.unsubscribe_count', | |
329 | 'civicrm_mailing_event_unsubscribe.optout_count', | |
be2fb01f | 330 | ]; |
1b08b468 | 331 | |
be2fb01f CW |
332 | $select = []; |
333 | $this->_columnHeaders = []; | |
6a488035 TO |
334 | foreach ($this->_columns as $tableName => $table) { |
335 | if (array_key_exists('fields', $table)) { | |
336 | foreach ($table['fields'] as $fieldName => $field) { | |
8cc574cf | 337 | if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) { |
6a488035 TO |
338 | |
339 | # for statistics | |
a7488080 | 340 | if (!empty($field['statistics'])) { |
6a488035 TO |
341 | switch ($field['statistics']['calc']) { |
342 | case 'PERCENTAGE': | |
343 | $base_table_column = explode('.', $field['statistics']['base']); | |
344 | $top_table_column = explode('.', $field['statistics']['top']); | |
345 | ||
346 | $select[] = "CONCAT(round( | |
347 | count(DISTINCT {$this->_columns[$top_table_column[0]]['fields'][$top_table_column[1]]['dbAlias']}) / | |
348 | count(DISTINCT {$this->_columns[$base_table_column[0]]['fields'][$base_table_column[1]]['dbAlias']}) * 100, 2 | |
349 | ), '%') as {$tableName}_{$fieldName}"; | |
350 | break; | |
351 | } | |
352 | } | |
353 | else { | |
354 | if (in_array($tableName, $count_tables)) { | |
1b08b468 AS |
355 | // Use the DISTINCT keyword appropriately, based on the contents |
356 | // of $distinct_count_columns. | |
d7860c23 AS |
357 | $distinct = ''; |
358 | if (in_array("{$tableName}.{$fieldName}", $distinctCountColumns)) { | |
1b08b468 AS |
359 | $distinct = 'DISTINCT'; |
360 | } | |
1b08b468 | 361 | $select[] = "count($distinct {$field['dbAlias']}) as {$tableName}_{$fieldName}"; |
6a488035 TO |
362 | } |
363 | else { | |
364 | $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}"; | |
365 | } | |
366 | } | |
9c1bc317 | 367 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL; |
6a488035 TO |
368 | $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title']; |
369 | } | |
370 | } | |
371 | } | |
372 | } | |
373 | ||
d1641c51 | 374 | $this->_selectClauses = $select; |
6a488035 TO |
375 | $this->_select = "SELECT " . implode(', ', $select) . " "; |
376 | //print_r($this->_select); | |
377 | } | |
378 | ||
00be9182 | 379 | public function from() { |
6a488035 TO |
380 | |
381 | $this->_from = " | |
382 | FROM civicrm_mailing {$this->_aliases['civicrm_mailing']} | |
383 | LEFT JOIN civicrm_mailing_job {$this->_aliases['civicrm_mailing_job']} | |
384 | ON {$this->_aliases['civicrm_mailing']}.id = {$this->_aliases['civicrm_mailing_job']}.mailing_id | |
385 | LEFT JOIN civicrm_mailing_event_queue {$this->_aliases['civicrm_mailing_event_queue']} | |
386 | ON {$this->_aliases['civicrm_mailing_event_queue']}.job_id = {$this->_aliases['civicrm_mailing_job']}.id | |
387 | LEFT JOIN civicrm_mailing_event_bounce {$this->_aliases['civicrm_mailing_event_bounce']} | |
388 | ON {$this->_aliases['civicrm_mailing_event_bounce']}.event_queue_id = {$this->_aliases['civicrm_mailing_event_queue']}.id | |
389 | LEFT JOIN civicrm_mailing_event_delivered {$this->_aliases['civicrm_mailing_event_delivered']} | |
390 | ON {$this->_aliases['civicrm_mailing_event_delivered']}.event_queue_id = {$this->_aliases['civicrm_mailing_event_queue']}.id | |
391 | AND {$this->_aliases['civicrm_mailing_event_bounce']}.id IS null | |
392 | LEFT JOIN civicrm_mailing_event_opened {$this->_aliases['civicrm_mailing_event_opened']} | |
393 | ON {$this->_aliases['civicrm_mailing_event_opened']}.event_queue_id = {$this->_aliases['civicrm_mailing_event_queue']}.id | |
394 | LEFT JOIN civicrm_mailing_event_trackable_url_open {$this->_aliases['civicrm_mailing_event_trackable_url_open']} | |
395 | ON {$this->_aliases['civicrm_mailing_event_trackable_url_open']}.event_queue_id = {$this->_aliases['civicrm_mailing_event_queue']}.id | |
396 | LEFT JOIN civicrm_mailing_event_unsubscribe {$this->_aliases['civicrm_mailing_event_unsubscribe']} | |
a1ad2b04 PN |
397 | ON {$this->_aliases['civicrm_mailing_event_unsubscribe']}.event_queue_id = {$this->_aliases['civicrm_mailing_event_queue']}.id AND {$this->_aliases['civicrm_mailing_event_unsubscribe']}.org_unsubscribe = 0 |
398 | LEFT JOIN civicrm_mailing_event_unsubscribe mailing_event_optout_civireport | |
399 | ON mailing_event_optout_civireport.event_queue_id = {$this->_aliases['civicrm_mailing_event_queue']}.id AND mailing_event_optout_civireport.org_unsubscribe = 1"; | |
baff85dd | 400 | |
07ac16aa | 401 | if ($this->isTableSelected('civicrm_mailing_group')) { |
402 | $this->_from .= " | |
403 | LEFT JOIN civicrm_mailing_group {$this->_aliases['civicrm_mailing_group']} | |
d1641c51 | 404 | ON {$this->_aliases['civicrm_mailing_group']}.mailing_id = {$this->_aliases['civicrm_mailing']}.id"; |
07ac16aa | 405 | } |
6a488035 TO |
406 | // need group by and order by |
407 | ||
408 | //print_r($this->_from); | |
409 | } | |
410 | ||
00be9182 | 411 | public function where() { |
be2fb01f | 412 | $clauses = []; |
6a488035 TO |
413 | //to avoid the sms listings |
414 | $clauses[] = "{$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL"; | |
415 | ||
416 | foreach ($this->_columns as $tableName => $table) { | |
417 | if (array_key_exists('filters', $table)) { | |
418 | foreach ($table['filters'] as $fieldName => $field) { | |
419 | $clause = NULL; | |
420 | if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) { | |
9c1bc317 CW |
421 | $relative = $this->_params["{$fieldName}_relative"] ?? NULL; |
422 | $from = $this->_params["{$fieldName}_from"] ?? NULL; | |
423 | $to = $this->_params["{$fieldName}_to"] ?? NULL; | |
6a488035 | 424 | |
baff85dd | 425 | $clause = $this->dateClause($this->_aliases[$tableName] . '.' . $field['name'], $relative, $from, $to, $field['type']); |
6a488035 TO |
426 | } |
427 | else { | |
9c1bc317 | 428 | $op = $this->_params["{$fieldName}_op"] ?? NULL; |
6a488035 TO |
429 | |
430 | if ($op) { | |
431 | if ($fieldName == 'relationship_type_id') { | |
432 | $clause = "{$this->_aliases['civicrm_relationship']}.relationship_type_id=" . $this->relationshipId; | |
433 | } | |
434 | else { | |
435 | $clause = $this->whereClause($field, | |
436 | $op, | |
437 | CRM_Utils_Array::value("{$fieldName}_value", $this->_params), | |
438 | CRM_Utils_Array::value("{$fieldName}_min", $this->_params), | |
439 | CRM_Utils_Array::value("{$fieldName}_max", $this->_params) | |
440 | ); | |
441 | } | |
442 | } | |
443 | } | |
444 | ||
445 | if (!empty($clause)) { | |
446 | $clauses[] = $clause; | |
447 | } | |
448 | } | |
449 | } | |
450 | } | |
451 | ||
452 | if (empty($clauses)) { | |
453 | $this->_where = "WHERE ( 1 )"; | |
454 | } | |
455 | else { | |
456 | $this->_where = "WHERE " . implode(' AND ', $clauses); | |
457 | } | |
6a488035 TO |
458 | } |
459 | ||
00be9182 | 460 | public function groupBy() { |
be2fb01f | 461 | $groupBy = [ |
bad98dd5 | 462 | "{$this->_aliases['civicrm_mailing']}.id", |
be2fb01f | 463 | ]; |
b708c08d | 464 | $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); |
6a488035 TO |
465 | } |
466 | ||
b07c960a | 467 | public function orderBy() { |
468 | parent::orderBy(); | |
469 | CRM_Contact_BAO_Query::getGroupByFromOrderBy($this->_groupBy, $this->_orderByArray); | |
470 | } | |
471 | ||
00be9182 | 472 | public function postProcess() { |
6a488035 TO |
473 | |
474 | $this->beginPostProcess(); | |
475 | ||
476 | // get the acl clauses built before we assemble the query | |
477 | $this->buildACLClause(CRM_Utils_Array::value('civicrm_contact', $this->_aliases)); | |
478 | ||
479 | $sql = $this->buildQuery(TRUE); | |
480 | ||
481 | // print_r($sql); | |
482 | ||
be2fb01f | 483 | $rows = $graphRows = []; |
6a488035 TO |
484 | $this->buildRows($sql, $rows); |
485 | ||
486 | $this->formatDisplay($rows); | |
487 | $this->doTemplateAssignment($rows); | |
488 | $this->endPostProcess($rows); | |
489 | } | |
490 | ||
74cf4551 EM |
491 | /** |
492 | * @return array | |
493 | */ | |
00be9182 | 494 | public static function getChartCriteria() { |
be2fb01f CW |
495 | return [ |
496 | 'count' => [ | |
353ffa53 | 497 | 'civicrm_mailing_event_delivered_delivered_count' => ts('Delivered'), |
6a488035 | 498 | 'civicrm_mailing_event_bounce_bounce_count' => ts('Bounce'), |
aa6b3363 PN |
499 | 'civicrm_mailing_event_opened_open_count' => ts('Total Opens'), |
500 | 'civicrm_mailing_event_opened_unique_open_count' => ts('Unique Opens'), | |
d58cca13 | 501 | 'civicrm_mailing_event_trackable_url_open_click_count' => ts('Unique Clicks'), |
6a488035 | 502 | 'civicrm_mailing_event_unsubscribe_unsubscribe_count' => ts('Unsubscribe'), |
be2fb01f CW |
503 | ], |
504 | 'rate' => [ | |
353ffa53 | 505 | 'civicrm_mailing_event_delivered_accepted_rate' => ts('Accepted Rate'), |
6a488035 | 506 | 'civicrm_mailing_event_bounce_bounce_rate' => ts('Bounce Rate'), |
aa6b3363 PN |
507 | 'civicrm_mailing_event_opened_open_rate' => ts('Total Open Rate'), |
508 | 'civicrm_mailing_event_opened_unique_open_rate' => ts('Unique Open Rate'), | |
6a488035 TO |
509 | 'civicrm_mailing_event_trackable_url_open_CTR' => ts('Click through Rate'), |
510 | 'civicrm_mailing_event_trackable_url_open_CTO' => ts('Click to Open Rate'), | |
be2fb01f CW |
511 | ], |
512 | ]; | |
6a488035 TO |
513 | } |
514 | ||
74cf4551 EM |
515 | /** |
516 | * @param $fields | |
517 | * @param $files | |
e8cf95b4 | 518 | * @param self $self |
74cf4551 EM |
519 | * |
520 | * @return array | |
521 | */ | |
6fb2b7f1 | 522 | public static function formRule($fields, $files, $self) { |
be2fb01f | 523 | $errors = []; |
6a488035 | 524 | |
a7488080 | 525 | if (empty($fields['charts'])) { |
6a488035 TO |
526 | return $errors; |
527 | } | |
528 | ||
b44e3f84 | 529 | $criteria = self::getChartCriteria(); |
6a488035 TO |
530 | $isError = TRUE; |
531 | foreach ($fields['fields'] as $fld => $isActive) { | |
be2fb01f | 532 | if (in_array($fld, [ |
353ffa53 TO |
533 | 'delivered_count', |
534 | 'bounce_count', | |
535 | 'open_count', | |
536 | 'click_count', | |
537 | 'unsubscribe_count', | |
538 | 'accepted_rate', | |
539 | 'bounce_rate', | |
540 | 'open_rate', | |
541 | 'CTR', | |
389bcebf | 542 | 'CTO', |
36497f43 | 543 | 'unique_open_rate', |
aa6b3363 | 544 | 'unique_open_count', |
be2fb01f | 545 | ])) { |
6a488035 TO |
546 | $isError = FALSE; |
547 | } | |
548 | } | |
549 | ||
550 | if ($isError) { | |
be2fb01f | 551 | $errors['_qf_default'] = ts('For Chart view, please select at least one field from %1 OR %2.', [ |
c86d4e7c SL |
552 | 1 => implode(', ', $criteria['count']), |
553 | 2 => implode(', ', $criteria['rate']), | |
554 | ]); | |
6a488035 TO |
555 | } |
556 | ||
557 | return $errors; | |
558 | } | |
559 | ||
74cf4551 EM |
560 | /** |
561 | * @param $rows | |
562 | */ | |
00be9182 | 563 | public function buildChart(&$rows) { |
6a488035 TO |
564 | if (empty($rows)) { |
565 | return; | |
566 | } | |
567 | ||
b44e3f84 | 568 | $criteria = self::getChartCriteria(); |
6a488035 | 569 | |
be2fb01f | 570 | $chartInfo = [ |
353ffa53 | 571 | 'legend' => ts('Mail Summary'), |
6a488035 TO |
572 | 'xname' => ts('Mailing'), |
573 | 'yname' => ts('Statistics'), | |
574 | 'xLabelAngle' => 20, | |
be2fb01f CW |
575 | 'tip' => [], |
576 | ]; | |
6a488035 TO |
577 | |
578 | $plotRate = $plotCount = TRUE; | |
579 | foreach ($rows as $row) { | |
be2fb01f | 580 | $chartInfo['values'][$row['civicrm_mailing_name']] = []; |
6a488035 | 581 | if ($plotCount) { |
58f49704 | 582 | foreach ($criteria['count'] as $criteriaName => $label) { |
583 | if (isset($row[$criteriaName])) { | |
584 | $chartInfo['values'][$row['civicrm_mailing_name']][$label] = $row[$criteriaName]; | |
6a488035 TO |
585 | $chartInfo['tip'][$label] = "{$label} #val#"; |
586 | $plotRate = FALSE; | |
587 | } | |
58f49704 | 588 | elseif (isset($criteria['count'][$criteriaName])) { |
589 | unset($criteria['count'][$criteriaName]); | |
6a488035 TO |
590 | } |
591 | } | |
592 | } | |
593 | if ($plotRate) { | |
58f49704 | 594 | foreach ($criteria['rate'] as $criteriaName => $label) { |
6a488035 | 595 | if (isset($row[$criteria])) { |
58f49704 | 596 | $chartInfo['values'][$row['civicrm_mailing_name']][$label] = $row[$criteriaName]; |
6a488035 TO |
597 | $chartInfo['tip'][$label] = "{$label} #val#"; |
598 | $plotCount = FALSE; | |
599 | } | |
58f49704 | 600 | elseif (isset($criteria['rate'][$criteriaName])) { |
601 | unset($criteria['rate'][$criteriaName]); | |
6a488035 TO |
602 | } |
603 | } | |
604 | } | |
605 | } | |
606 | ||
607 | if ($plotCount) { | |
b44e3f84 | 608 | $criteria = $criteria['count']; |
6a488035 TO |
609 | } |
610 | else { | |
b44e3f84 | 611 | $criteria = $criteria['rate']; |
6a488035 TO |
612 | } |
613 | ||
b44e3f84 | 614 | $chartInfo['criteria'] = array_values($criteria); |
6a488035 TO |
615 | |
616 | // dynamically set the graph size | |
b44e3f84 | 617 | $chartInfo['xSize'] = ((count($rows) * 125) + (count($rows) * count($criteria) * 40)); |
6a488035 TO |
618 | |
619 | // build the chart. | |
dc61ee93 | 620 | CRM_Utils_Chart::buildChart($chartInfo, $this->_params['charts']); |
6a488035 TO |
621 | $this->assign('chartType', $this->_params['charts']); |
622 | } | |
623 | ||
74cf4551 | 624 | /** |
ced9bfed EM |
625 | * Alter display of rows. |
626 | * | |
627 | * Iterate through the rows retrieved via SQL and make changes for display purposes, | |
628 | * such as rendering contacts as links. | |
629 | * | |
630 | * @param array $rows | |
631 | * Rows generated by SQL, with an array for each row. | |
74cf4551 | 632 | */ |
00be9182 | 633 | public function alterDisplay(&$rows) { |
6a488035 TO |
634 | $entryFound = FALSE; |
635 | foreach ($rows as $rowNum => $row) { | |
6c288074 | 636 | // CRM-16506 |
27a4d216 | 637 | if (array_key_exists('civicrm_mailing_id', $row)) { |
638 | if (array_key_exists('civicrm_mailing_name', $row)) { | |
639 | $rows[$rowNum]['civicrm_mailing_name_link'] = CRM_Report_Utils_Report::getNextUrl('mailing/detail', | |
640 | 'reset=1&force=1&mailing_id_op=eq&mailing_id_value=' . $row['civicrm_mailing_id'], | |
641 | $this->_absoluteUrl, $this->_id, $this->_drilldownReport | |
642 | ); | |
643 | $rows[$rowNum]['civicrm_mailing_name_hover'] = ts('View Mailing details for this mailing'); | |
644 | $entryFound = TRUE; | |
645 | } | |
646 | if (array_key_exists('civicrm_mailing_event_opened_open_count', $row)) { | |
647 | $rows[$rowNum]['civicrm_mailing_event_opened_open_count'] = CRM_Mailing_Event_BAO_Opened::getTotalCount($row['civicrm_mailing_id']); | |
648 | $entryFound = TRUE; | |
649 | } | |
6c288074 | 650 | } |
8a4cd1f8 SL |
651 | // convert campaign_id to campaign title |
652 | if (array_key_exists('civicrm_mailing_campaign_id', $row)) { | |
653 | if ($value = $row['civicrm_mailing_campaign_id']) { | |
654 | $rows[$rowNum]['civicrm_mailing_campaign_id'] = $this->campaigns[$value]; | |
655 | $entryFound = TRUE; | |
656 | } | |
657 | } | |
6a488035 TO |
658 | // skip looking further in rows, if first row itself doesn't |
659 | // have the column we need | |
660 | if (!$entryFound) { | |
661 | break; | |
662 | } | |
663 | } | |
664 | } | |
96025800 | 665 | |
6a488035 | 666 | } |