3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.4 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2013 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License along with this program; if not, contact CiviCRM LLC |
21 | at info[AT]civicrm[DOT]org. If you have questions about the |
22 | GNU Affero General Public License or the licensing of CiviCRM, |
23 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
24 +--------------------------------------------------------------------+
30 * @copyright CiviCRM LLC (c) 2004-2013
34 class CRM_Mailing_BAO_Query
{
36 static $_mailingFields = NULL;
38 static function &getFields() {
39 if (!self
::$_mailingFields) {
40 self
::$_mailingFields = array();
41 $_mailingFields['mailing_id'] = array(
42 'name' => 'mailing_id',
43 'title' => 'Mailing ID',
44 'where' => 'civicrm_mailing.id',
47 return self
::$_mailingFields;
51 * if mailings are involved, add the specific Mailing fields
56 static function select(&$query) {
57 // if Mailing mode add mailing id
58 if ($query->_mode
& CRM_Contact_BAO_Query
::MODE_MAILING
) {
59 $query->_select
['mailing_id'] = "civicrm_mailing.id as mailing_id";
60 $query->_element
['mailing_id'] = 1;
62 // base table is contact, so join recipients to it
63 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] =
64 " INNER JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id ";
66 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
69 if (CRM_Utils_Array
::value('mailing_name', $query->_returnProperties
)) {
70 $query->_select
['mailing_name'] = "civicrm_mailing.name as mailing_name";
71 $query->_element
['mailing_name'] = 1;
74 // get mailing subject
75 if (CRM_Utils_Array
::value('mailing_subject', $query->_returnProperties
)) {
76 $query->_select
['mailing_subject'] = "civicrm_mailing.subject as mailing_subject";
77 $query->_element
['mailing_subject'] = 1;
81 if (CRM_Utils_Array
::value('mailing_job_status', $query->_returnProperties
)) {
82 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job'] =
83 " LEFT JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.parent_id IS NULL AND civicrm_mailing_job.is_test != 1 ";
84 $query->_select
['mailing_job_status'] = "civicrm_mailing_job.status as mailing_job_status";
85 $query->_element
['mailing_job_status'] = 1;
89 if (CRM_Utils_Array
::value('email_on_hold', $query->_returnProperties
)) {
90 $query->_select
['email_on_hold'] = "recipient_email.on_hold as email_on_hold";
91 $query->_element
['email_on_hold'] = 1;
92 $query->_tables
['recipient_email'] = $query->_whereTables
['recipient_email'] = 1;
95 // get recipient email
96 if (CRM_Utils_Array
::value('email', $query->_returnProperties
)) {
97 $query->_select
['email'] = "recipient_email.email as email";
98 $query->_element
['email'] = 1;
99 $query->_tables
['recipient_email'] = $query->_whereTables
['recipient_email'] = 1;
103 if (CRM_Utils_Array
::value('contact_opt_out', $query->_returnProperties
)) {
104 $query->_select
['contact_opt_out'] = "contact_a.is_opt_out as contact_opt_out";
105 $query->_element
['contact_opt_out'] = 1;
108 // mailing job end date / completed date
109 if (CRM_Utils_Array
::value('mailing_job_end_date', $query->_returnProperties
)) {
110 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job'] =
111 " LEFT JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.parent_id IS NULL AND civicrm_mailing_job.is_test != 1 ";
112 $query->_select
['mailing_job_end_date'] = "civicrm_mailing_job.end_date as mailing_job_end_date";
113 $query->_element
['mailing_job_end_date'] = 1;
116 if (CRM_Utils_Array
::value('mailing_recipients_id', $query->_returnProperties
)) {
117 $query->_select
['mailing_recipients_id'] = " civicrm_mailing_recipients.id as mailing_recipients_id";
118 $query->_element
['mailing_recipients_id'] = 1;
123 static function where(&$query) {
125 foreach (array_keys($query->_params
) as $id) {
126 if (!CRM_Utils_Array
::value(0, $query->_params
[$id])) {
129 if (substr($query->_params
[$id][0], 0, 8) == 'mailing_') {
130 if ($query->_mode
== CRM_Contact_BAO_QUERY
::MODE_CONTACTS
) {
131 $query->_useDistinct
= TRUE;
133 $grouping = $query->_params
[$id][3];
134 self
::whereClauseSingle($query->_params
[$id], $query);
139 static function from($name, $mode, $side) {
143 case 'civicrm_mailing_recipients':
144 $from = " $side JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id";
147 case 'civicrm_mailing_event_queue':
148 // this is tightly binded so as to do a check WRT actual job recipients ('child' type jobs)
149 $from = " INNER JOIN civicrm_mailing_event_queue ON
150 civicrm_mailing_event_queue.contact_id = civicrm_mailing_recipients.contact_id
151 AND civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.job_type = 'child'";
154 case 'civicrm_mailing':
155 $from = " $side JOIN civicrm_mailing ON civicrm_mailing.id = civicrm_mailing_recipients.mailing_id ";
158 case 'civicrm_mailing_job':
159 $from = " $side JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.is_test != 1 ";
162 case 'civicrm_mailing_event_bounce':
163 case 'civicrm_mailing_event_delivered':
164 case 'civicrm_mailing_event_opened':
165 case 'civicrm_mailing_event_reply':
166 case 'civicrm_mailing_event_unsubscribe':
167 case 'civicrm_mailing_event_forward':
168 case 'civicrm_mailing_event_trackable_url_open':
169 $from = " $side JOIN $name ON $name.event_queue_id = civicrm_mailing_event_queue.id";
172 case 'recipient_email':
173 $from = " $side JOIN civicrm_email recipient_email ON recipient_email.id = civicrm_mailing_recipients.email_id";
180 static function defaultReturnProperties($mode,
181 $includeCustomFields = TRUE
185 if ($mode & CRM_Contact_BAO_Query
::MODE_MAILING
) {
191 'mailing_subject' => 1,
192 'email_on_hold' => 1,
193 'contact_opt_out' => 1,
194 'mailing_job_status' => 1,
195 'mailing_job_end_date' => 1,
197 'contact_sub_type' => 1,
198 'mailing_recipients_id' => 1
204 static function whereClauseSingle(&$values, &$query) {
205 list($name, $op, $value, $grouping, $wildcard) = $values;
208 $fields = self
::getFields();
212 $selectedMailings = array_flip($value);
213 $value = "(" . implode(',', $value) . ")";
215 $query->_where
[$grouping][] = "civicrm_mailing.id $op $value";
217 $mailings = CRM_Mailing_BAO_Mailing
::getMailingsList();
218 foreach ($selectedMailings as $id => $dnc) {
219 $selectedMailings[$id] = $mailings[$id];
221 $selectedMailings = implode(' or ', $selectedMailings);
223 $query->_qill
[$grouping][] = "Mailing Name $op \"$selectedMailings\"";
224 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
225 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
229 $value = strtolower( addslashes( $value ) );
234 $query->_where
[$grouping][] = "LOWER(civicrm_mailing.name) $op '$value'";
235 $query->_qill
[$grouping][] = "Mailing Namename $op \"$value\"";
236 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
237 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
241 case 'mailing_date_low':
242 case 'mailing_date_high':
243 // process to / from date
244 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
245 $query->_tables
['civicrm_mailing_event_queue'] = $query->_whereTables
['civicrm_mailing_event_queue'] = 1;
246 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job'] = 1;
247 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
248 $query->dateQueryBuilder($values,
249 'civicrm_mailing_job', 'mailing_date', 'start_date', 'Mailing Delivery Date'
253 case 'mailing_delivery_status':
254 $options = CRM_Mailing_PseudoConstant
::yesNoOptions('delivered');
256 list($name, $op, $value, $grouping, $wildcard) = $values;
258 self
::mailingEventQueryBuilder($query, $values,
259 'civicrm_mailing_event_delivered',
260 'mailing_delivery_status',
261 ts('Mailing Delivery'),
265 elseif ($value == 'N') {
266 $options['Y'] = $options['N'];
267 $values = array($name, $op, 'Y', $grouping, $wildcard);
268 self
::mailingEventQueryBuilder($query, $values,
269 'civicrm_mailing_event_bounce',
270 'mailing_delivery_status',
271 ts('Mailing Delivery'),
277 case 'mailing_bounce_types':
279 $values = array($name, $op, $value, $grouping, $wildcard);
280 self
::mailingEventQueryBuilder($query, $values,
281 'civicrm_mailing_event_bounce',
283 ts('Bounce type(s)'),
284 CRM_Core_PseudoConstant
::get('CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id', array('keyColumn' => 'id', 'labelColumn' => 'name'))
288 case 'mailing_open_status':
289 self
::mailingEventQueryBuilder($query, $values,
290 'civicrm_mailing_event_opened', 'mailing_open_status', ts('Mailing: Trackable Opens'), CRM_Mailing_PseudoConstant
::yesNoOptions('open')
294 case 'mailing_click_status':
295 self
::mailingEventQueryBuilder($query, $values,
296 'civicrm_mailing_event_trackable_url_open', 'mailing_click_status', ts('Mailing: Trackable URL Clicks'), CRM_Mailing_PseudoConstant
::yesNoOptions('click')
300 case 'mailing_reply_status':
301 self
::mailingEventQueryBuilder($query, $values,
302 'civicrm_mailing_event_reply', 'mailing_reply_status', ts('Mailing: Trackable Replies'), CRM_Mailing_PseudoConstant
::yesNoOptions('reply')
306 case 'mailing_optout':
307 $valueTitle = array(1 => ts('Opt-out Requests'));
308 // include opt-out events only
309 $query->_where
[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 1";
310 self
::mailingEventQueryBuilder($query, $values,
311 'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
312 ts('Mailing: '), $valueTitle
316 case 'mailing_unsubscribe':
317 $valueTitle = array(1 => ts('Unsubscribe Requests'));
318 // exclude opt-out events
319 $query->_where
[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 0";
320 self
::mailingEventQueryBuilder($query, $values,
321 'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
322 ts('Mailing: '), $valueTitle
326 case 'mailing_forward':
327 $valueTitle = array('Y' => ts('Forwards'));
328 // since its a checkbox
330 self
::mailingEventQueryBuilder($query, $values,
331 'civicrm_mailing_event_forward', 'mailing_forward',
332 ts('Mailing: '), $valueTitle
336 case 'mailing_job_status':
337 if (!empty($value)) {
338 if ($value != 'Scheduled' && $value != 'Canceled') {
339 $query->_tables
['civicrm_mailing_event_queue'] = $query->_whereTables
['civicrm_mailing_event_queue'] = 1;
341 $query->_tables
['civicrm_mailing'] = $query->_whereTables
['civicrm_mailing'] = 1;
342 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job'] = 1;
343 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
345 $query->_where
[$grouping][] = " civicrm_mailing_job.status = '{$value}' ";
346 $query->_qill
[$grouping][] = "Mailing Job Status IS \"$value\"";
353 * add all the elements shared between Mailing search and advnaced search
360 static function buildSearchForm(&$form) {
362 $mailings = CRM_Mailing_BAO_Mailing
::getMailingsList();
364 if (!empty($mailings)) {
365 $form->add('select', 'mailing_id', ts('Mailing Name(s)'), $mailings, FALSE,
366 array('id' => 'mailing_id', 'multiple' => 'multiple', 'title' => ts('- select -'))
370 CRM_Core_Form_Date
::buildDateRange($form, 'mailing_date', 1, '_low', '_high', ts('From'), FALSE);
372 $mailingJobStatuses = array(
373 '' => ts('- select -'),
374 'Complete' => 'Complete',
375 'Scheduled' => 'Scheduled',
376 'Running' => 'Running',
377 'Canceled' => 'Canceled'
379 $form->addElement('select', 'mailing_job_status', ts('Mailing Job Status'), $mailingJobStatuses, FALSE);
381 $mailingBounceTypes = CRM_Core_PseudoConstant
::get(
382 'CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id',
383 array('keyColumn' => 'id', 'labelColumn' => 'name')
385 $form->add('select', 'mailing_bounce_types', ts('Bounce Types'), $mailingBounceTypes, FALSE,
386 array('id' => 'mailing_bounce_types', 'multiple' => 'multiple', 'title' => ts('- select -'))
390 $form->addRadio('mailing_delivery_status', ts('Delivery Status'), CRM_Mailing_PseudoConstant
::yesNoOptions('delivered'));
391 $form->addRadio('mailing_open_status', ts('Trackable Opens'), CRM_Mailing_PseudoConstant
::yesNoOptions('open'));
392 $form->addRadio('mailing_click_status', ts('Trackable URLs'), CRM_Mailing_PseudoConstant
::yesNoOptions('click'));
393 $form->addRadio('mailing_reply_status', ts('Trackable Replies'), CRM_Mailing_PseudoConstant
::yesNoOptions('reply'));
395 $form->add('checkbox', 'mailing_unsubscribe', ts('Unsubscribe Requests'));
396 $form->add('checkbox', 'mailing_optout', ts('Opt-out Requests'));
397 $form->add('checkbox', 'mailing_forward', ts('Forwards'));
399 $form->assign('validCiviMailing', TRUE);
400 $form->addFormRule(array('CRM_Mailing_BAO_Query', 'formRule'), $form);
406 * @param array $fields the input form values
407 * @param array $files the uploaded files if any
408 * @param array $options additional user data
410 * @return true if no errors, else array of errors
414 static function formRule($fields, $files, $self) {
416 // if an event filter is specified, then a mailing selector must also be specified
417 if ((CRM_Utils_Array
::value('mailing_delivery_status', $fields) ||
418 CRM_Utils_Array
::value('mailing_open_status', $fields) ||
419 CRM_Utils_Array
::value('mailing_click_status', $fields) ||
420 CRM_Utils_Array
::value('mailing_reply_status', $fields) ||
421 CRM_Utils_Array
::value('mailing_bounce_types', $fields)
423 (!CRM_Utils_Array
::value('mailing_id', $fields) &&
424 !CRM_Utils_Array
::value('mailing_date_low', $fields) &&
425 !CRM_Utils_Array
::value('mailing_date_high', $fields)
428 $errors['mailing_id'] = ts('Must specify mailing name or date');
429 // Keep search form opened in case of form rule.
430 if (is_a($self, 'CRM_Contact_Form_Search_Advanced') && !isset(CRM_Contact_BAO_Query
::$_openedPanes['Mailings'])) {
431 CRM_Contact_BAO_Query
::$_openedPanes['Mailings'] = TRUE;
432 $self->assign('openedPanes', CRM_Contact_BAO_Query
::$_openedPanes);
438 static function addShowHide(&$showHide) {
439 $showHide->addHide('MailingForm');
440 $showHide->addShow('MailingForm_show');
443 static function searchAction(&$row, $id) {}
445 static function tableNames(&$tables) {}
448 * Filter query results based on which contacts do (not) have a particular mailing event in their history.
458 static function mailingEventQueryBuilder(&$query, &$values, $tableName, $fieldName, $fieldTitle, &$valueTitles) {
459 list($name, $op, $value, $grouping, $wildcard) = $values;
461 if (empty($value) ||
$value == 'A') {
462 // don't do any filtering
467 $query->_where
[$grouping][] = $tableName . ".id is not null ";
469 elseif ($value == 'N') {
470 $query->_where
[$grouping][] = $tableName . ".id is null ";
473 if (is_array($value)) {
474 $query->_where
[$grouping][] = "$tableName.$fieldName $op (" . implode(',', $value) . ")";
475 $query->_qill
[$grouping][] = "$fieldTitle $op ". implode(', ', array_intersect_key($valueTitles, array_flip($value)));
478 $query->_qill
[$grouping][] = $fieldTitle . ' - ' . $valueTitles[$value];
481 $query->_tables
['civicrm_mailing_job'] = $query->_whereTables
['civicrm_mailing_job'] = 1;
482 $query->_tables
['civicrm_mailing_event_queue'] = $query->_whereTables
['civicrm_mailing_event_queue'] = 1;
483 $query->_tables
['civicrm_mailing_recipients'] = $query->_whereTables
['civicrm_mailing_recipients'] = 1;
484 $query->_tables
[$tableName] = $query->_whereTables
[$tableName] = 1;