CRM/Mailing/Selector/Browse.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  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 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  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.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2019
  32  */
  33
  34 /**
  35  * This class is used to browse past mailings.
  36  */
  37 class CRM_Mailing_Selector_Browse extends CRM_Core_Selector_Base implements CRM_Core_Selector_API {
  38
  39   /**
  40    * Array of supported links, currently null
  41    *
  42    * @var array
  43    */
  44   static $_links = NULL;
  45
  46   /**
  47    * We use desc to remind us what that column is, name is used in the tpl
  48    *
  49    * @var array
  50    */
  51   static $_columnHeaders;
  52
  53   protected $_parent;
  54
  55   /**
  56    * Class constructor.
  57    *
  58    *
  59    * @return \CRM_Mailing_Selector_Browse
  60    */
  61   public function __construct() {
  62   }
  63
  64   /**
  65    * This method returns the links that are given for each search row.
  66    *
  67    * @return array
  68    */
  69   public static function &links() {
  70     return self::$_links;
  71   }
  72
  73   /**
  74    * Getter for array of the parameters required for creating pager.
  75    *
  76    * @param $action
  77    * @param array $params
  78    */
  79   public function getPagerParams($action, &$params) {
  80     $params['csvString'] = NULL;
  81     $params['rowCount'] = CRM_Utils_Pager::ROWCOUNT;
  82     $params['status'] = ts('Mailings %%StatusMessage%%');
  83     $params['buttonTop'] = 'PagerTopButton';
  84     $params['buttonBottom'] = 'PagerBottomButton';
  85   }
  86
  87   /**
  88    * Returns the column headers as an array of tuples:
  89    * (name, sortName (key to the sort array))
  90    *
  91    * @param string $action
  92    *   The action being performed.
  93    * @param string $output
  94    *   What should the result set include (web/email/csv).
  95    *
  96    * @return array
  97    *   the column headers that need to be displayed
  98    */
  99   public function &getColumnHeaders($action = NULL, $output = NULL) {
 100     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
 101     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 102     if (!isset(self::$_columnHeaders)) {
 103       $completedOrder = NULL;
 104
 105       // Set different default sort depending on type of mailings (CRM-7652)
 106       $unscheduledOrder = $scheduledOrder = $archivedOrder = CRM_Utils_Sort::DONTCARE;
 107       if ($this->_parent->get('unscheduled')) {
 108         $unscheduledOrder = CRM_Utils_Sort::DESCENDING;
 109       }
 110       elseif ($this->_parent->get('scheduled')) {
 111         $scheduledOrder = CRM_Utils_Sort::DESCENDING;
 112       }
 113       else {
 114         // sort by completed date for archived and undefined get
 115         $completedOrder = CRM_Utils_Sort::DESCENDING;
 116       }
 117       $nameHeaderLabel = ($this->_parent->get('sms')) ? ts('SMS Name') : ts('Mailing Name');
 118
 119       self::$_columnHeaders = array(
 120         array(
 121           'name' => $nameHeaderLabel,
 122           'sort' => 'name',
 123           'direction' => CRM_Utils_Sort::DONTCARE,
 124         ),
 125       );
 126
 127       if (CRM_Core_I18n::isMultilingual()) {
 128         self::$_columnHeaders = array_merge(
 129           self::$_columnHeaders,
 130           array(
 131             array(
 132               'name' => ts('Language'),
 133               'sort' => 'language',
 134               'direction' => CRM_Utils_Sort::DONTCARE,
 135             ),
 136           )
 137         );
 138       }
 139
 140       self::$_columnHeaders = array_merge(
 141         self::$_columnHeaders,
 142         array(
 143           array(
 144             'name' => ts('Status'),
 145             'sort' => 'status',
 146             'direction' => CRM_Utils_Sort::DONTCARE,
 147           ),
 148           array(
 149             'name' => ts('Created By'),
 150             'sort' => 'created_by',
 151             'direction' => CRM_Utils_Sort::DONTCARE,
 152           ),
 153           array(
 154             'name' => ts('Created Date'),
 155             'sort' => 'created_date',
 156             'direction' => $unscheduledOrder,
 157           ),
 158           array(
 159             'name' => ts('Sent By'),
 160             'sort' => 'scheduled_by',
 161             'direction' => CRM_Utils_Sort::DONTCARE,
 162           ),
 163           array(
 164             'name' => ts('Scheduled'),
 165             'sort' => 'scheduled_date',
 166             'direction' => $scheduledOrder,
 167           ),
 168           array(
 169             'name' => ts('Started'),
 170             'sort' => 'start_date',
 171             'direction' => CRM_Utils_Sort::DONTCARE,
 172           ),
 173           array(
 174             'name' => ts('Completed'),
 175             'sort' => 'end_date',
 176             'direction' => $completedOrder,
 177           ),
 178         )
 179       );
 180
 181       if (CRM_Campaign_BAO_Campaign::isCampaignEnable()) {
 182         self::$_columnHeaders[] = array(
 183           'name' => ts('Campaign'),
 184           'sort' => 'campaign_id',
 185           'direction' => CRM_Utils_Sort::DONTCARE,
 186         );
 187       }
 188
 189       if ($output != CRM_Core_Selector_Controller::EXPORT) {
 190         self::$_columnHeaders[] = array('name' => ts('Action'));
 191       }
 192     }
 193
 194     CRM_Core_Smarty::singleton()->assign('multilingual', CRM_Core_I18n::isMultilingual());
 195     return self::$_columnHeaders;
 196   }
 197
 198   /**
 199    * Returns total number of rows for the query.
 200    *
 201    * @param string $action
 202    *
 203    * @return int
 204    *   Total number of rows
 205    */
 206   public function getTotalCount($action) {
 207     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 208     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
 209     $mailingACL = CRM_Mailing_BAO_Mailing::mailingACL();
 210
 211     // get the where clause.
 212     $params = array();
 213     $whereClause = "$mailingACL AND " . $this->whereClause($params);
 214
 215     // CRM-11919 added addition ON clauses to mailing_job to match getRows
 216     $query = "
 217    SELECT  COUNT( DISTINCT $mailing.id ) as count
 218      FROM  $mailing
 219 LEFT JOIN  $job ON ( $mailing.id = $job.mailing_id AND civicrm_mailing_job.is_test = 0 AND civicrm_mailing_job.parent_id IS NULL )
 220 LEFT JOIN  civicrm_contact createdContact   ON ( $mailing.created_id   = createdContact.id )
 221 LEFT JOIN  civicrm_contact scheduledContact ON ( $mailing.scheduled_id = scheduledContact.id )
 222     WHERE  $whereClause";
 223
 224     return CRM_Core_DAO::singleValueQuery($query, $params);
 225   }
 226
 227   /**
 228    * Returns all the rows in the given offset and rowCount.
 229    *
 230    * @param string $action
 231    *   The action being performed.
 232    * @param int $offset
 233    *   The row number to start from.
 234    * @param int $rowCount
 235    *   The number of rows to return.
 236    * @param string $sort
 237    *   The sql string that describes the sort order.
 238    * @param string $output
 239    *   What should the result set include (web/email/csv).
 240    *
 241    * @return int
 242    *   the total number of rows for this action
 243    */
 244   public function &getRows($action, $offset, $rowCount, $sort, $output = NULL) {
 245     static $actionLinks = NULL;
 246     if (empty($actionLinks)) {
 247       $cancelExtra = ts('Are you sure you want to cancel this mailing?');
 248       $deleteExtra = ts('Are you sure you want to delete this mailing?');
 249       $archiveExtra = ts('Are you sure you want to archive this mailing?');
 250
 251       $actionLinks = array(
 252         CRM_Core_Action::ENABLE => array(
 253           'name' => ts('Approve/Reject'),
 254           'url' => 'civicrm/mailing/approve',
 255           'qs' => 'mid=%%mid%%&reset=1',
 256           'title' => ts('Approve/Reject Mailing'),
 257         ),
 258         CRM_Core_Action::VIEW => array(
 259           'name' => ts('Report'),
 260           'url' => 'civicrm/mailing/report',
 261           'qs' => 'mid=%%mid%%&reset=1',
 262           'title' => ts('View Mailing Report'),
 263         ),
 264         CRM_Core_Action::UPDATE => array(
 265           'name' => ts('Re-Use'),
 266           'url' => 'civicrm/mailing/send',
 267           'qs' => 'mid=%%mid%%&reset=1',
 268           'title' => ts('Re-Send Mailing'),
 269         ),
 270         CRM_Core_Action::DISABLE => array(
 271           'name' => ts('Cancel'),
 272           'url' => 'civicrm/mailing/browse',
 273           'qs' => 'action=disable&mid=%%mid%%&reset=1',
 274           'extra' => 'onclick="if (confirm(\'' . $cancelExtra . '\')) this.href+=\'&amp;confirmed=1\'; else return false;"',
 275           'title' => ts('Cancel Mailing'),
 276         ),
 277         CRM_Core_Action::PREVIEW => array(
 278           'name' => ts('Continue'),
 279           'url' => 'civicrm/mailing/send',
 280           'qs' => 'mid=%%mid%%&continue=true&reset=1',
 281           'title' => ts('Continue Mailing'),
 282         ),
 283         CRM_Core_Action::DELETE => array(
 284           'name' => ts('Delete'),
 285           'url' => 'civicrm/mailing/browse',
 286           'qs' => 'action=delete&mid=%%mid%%&reset=1',
 287           'extra' => 'onclick="if (confirm(\'' . $deleteExtra . '\')) this.href+=\'&amp;confirmed=1\'; else return false;"',
 288           'title' => ts('Delete Mailing'),
 289         ),
 290         CRM_Core_Action::RENEW => array(
 291           'name' => ts('Archive'),
 292           'url' => 'civicrm/mailing/browse/archived',
 293           'qs' => 'action=renew&mid=%%mid%%&reset=1',
 294           'extra' => 'onclick="if (confirm(\'' . $archiveExtra . '\')) this.href+=\'&amp;confirmed=1\'; else return false;"',
 295           'title' => ts('Archive Mailing'),
 296         ),
 297         CRM_Core_Action::REOPEN => array(
 298           'name' => ts('Resume'),
 299           'url' => 'civicrm/mailing/browse',
 300           'qs' => 'action=reopen&mid=%%mid%%&reset=1',
 301           'title' => ts('Resume mailing'),
 302         ),
 303         CRM_Core_Action::CLOSE => array(
 304           'name' => ts('Pause'),
 305           'url' => 'civicrm/mailing/browse',
 306           'qs' => 'action=close&mid=%%mid%%&reset=1',
 307           'title' => ts('Pause mailing'),
 308         ),
 309       );
 310     }
 311
 312     $allAccess = TRUE;
 313     $workFlow = $showApprovalLinks = $showScheduleLinks = $showCreateLinks = FALSE;
 314     if (CRM_Mailing_Info::workflowEnabled()) {
 315       $allAccess = FALSE;
 316       $workFlow = TRUE;
 317       // supercedes all permission
 318       if (CRM_Core_Permission::check('access CiviMail')) {
 319         $allAccess = TRUE;
 320       }
 321
 322       if (CRM_Core_Permission::check('approve mailings')) {
 323         $showApprovalLinks = TRUE;
 324       }
 325
 326       if (CRM_Core_Permission::check('create mailings')) {
 327         $showCreateLinks = TRUE;
 328       }
 329
 330       if (CRM_Core_Permission::check('schedule mailings')) {
 331         $showScheduleLinks = TRUE;
 332       }
 333     }
 334     $mailing = new CRM_Mailing_BAO_Mailing();
 335
 336     $params = array();
 337
 338     $whereClause = ' AND ' . $this->whereClause($params);
 339
 340     if (empty($params)) {
 341       $this->_parent->assign('isSearch', 0);
 342     }
 343     else {
 344       $this->_parent->assign('isSearch', 1);
 345     }
 346     $rows = &$mailing->getRows($offset, $rowCount, $sort, $whereClause, $params);
 347
 348     // get the search base mailing Ids, CRM-3711.
 349     $searchMailings = $mailing->searchMailingIDs();
 350
 351     // check for delete CRM-4418
 352     $allowToDelete = CRM_Core_Permission::check('delete in CiviMail');
 353
 354     if ($output != CRM_Core_Selector_Controller::EXPORT) {
 355
 356       // create the appropriate $op to use for hook_civicrm_links
 357       $pageTypes = array('view', 'mailing', 'browse');
 358       if ($this->_parent->_unscheduled) {
 359         $pageTypes[] = 'unscheduled';
 360       }
 361       if ($this->_parent->_scheduled) {
 362         $pageTypes[] = 'scheduled';
 363       }
 364       if ($this->_parent->_archived) {
 365         $pageTypes[] = 'archived';
 366       }
 367       $opString = implode('.', $pageTypes);
 368
 369       // get languages for later conversion
 370       $languages = CRM_Core_I18n::languages();
 371
 372       foreach ($rows as $key => $row) {
 373         $actionMask = NULL;
 374         if ($row['sms_provider_id']) {
 375           $actionLinks[CRM_Core_Action::PREVIEW]['url'] = 'civicrm/sms/send';
 376         }
 377
 378         if (!($row['status'] == 'Not scheduled') && !$row['sms_provider_id']) {
 379           if ($allAccess || $showCreateLinks) {
 380             $actionMask = CRM_Core_Action::VIEW;
 381           }
 382
 383           if (!in_array($row['id'], $searchMailings)) {
 384             if ($allAccess || $showCreateLinks) {
 385               $actionMask |= CRM_Core_Action::UPDATE;
 386             }
 387           }
 388         }
 389         else {
 390           if ($allAccess || ($showCreateLinks || $showScheduleLinks)) {
 391             $actionMask = CRM_Core_Action::PREVIEW;
 392           }
 393         }
 394         if (in_array($row['status'], array(
 395           'Scheduled',
 396           'Running',
 397           'Paused',
 398         ))) {
 399           if ($allAccess ||
 400             ($showApprovalLinks && $showCreateLinks && $showScheduleLinks)
 401           ) {
 402
 403             $actionMask |= CRM_Core_Action::DISABLE;
 404             if ($row['status'] == "Paused") {
 405               $actionMask |= CRM_Core_Action::REOPEN;
 406             }
 407             else {
 408               $actionMask |= CRM_Core_Action::CLOSE;
 409             }
 410           }
 411           if ($row['status'] == 'Scheduled' &&
 412             empty($row['approval_status_id'])
 413           ) {
 414             if ($workFlow && ($allAccess || $showApprovalLinks)) {
 415               $actionMask |= CRM_Core_Action::ENABLE;
 416             }
 417           }
 418         }
 419
 420         if (in_array($row['status'], array('Complete', 'Canceled')) &&
 421           !$row['archived']
 422         ) {
 423           if ($allAccess || $showCreateLinks) {
 424             $actionMask |= CRM_Core_Action::RENEW;
 425           }
 426         }
 427
 428         // check for delete permission.
 429         if ($allowToDelete) {
 430           $actionMask |= CRM_Core_Action::DELETE;
 431         }
 432
 433         if ($actionMask == NULL) {
 434           $actionMask = CRM_Core_Action::ADD;
 435         }
 436         // get status strings as per locale settings CRM-4411.
 437         $rows[$key]['status'] = CRM_Mailing_BAO_MailingJob::status($row['status']);
 438
 439         // get language string
 440         $rows[$key]['language'] = (isset($row['language']) ? $languages[$row['language']] : NULL);
 441
 442         $validLinks = $actionLinks;
 443         if (($mailingUrl = CRM_Mailing_BAO_Mailing::getPublicViewUrl($row['id'])) != FALSE) {
 444           $validLinks[] = array(
 445             'name' => ts('Public View'),
 446             'url' => 'civicrm/mailing/view',
 447             'qs' => 'id=%%mid%%&reset=1',
 448             'title' => ts('Public View'),
 449             'fe' => TRUE,
 450           );
 451         }
 452
 453         $rows[$key]['action'] = CRM_Core_Action::formLink(
 454           $validLinks,
 455           $actionMask,
 456           array('mid' => $row['id']),
 457           "more",
 458           FALSE,
 459           $opString,
 460           "Mailing",
 461           $row['id']
 462         );
 463
 464         // unset($rows[$key]['id']);
 465         // if the scheduled date is 0, replace it with an empty string
 466         if ($rows[$key]['scheduled_iso'] == '0000-00-00 00:00:00') {
 467           $rows[$key]['scheduled'] = '';
 468         }
 469         unset($rows[$key]['scheduled_iso']);
 470       }
 471     }
 472
 473     // also initialize the AtoZ pager
 474     $this->pagerAtoZ();
 475     return $rows;
 476   }
 477
 478   /**
 479    * Name of export file.
 480    *
 481    * @param string $output
 482    *   Type of output.
 483    *
 484    * @return string
 485    *   name of the file
 486    */
 487   public function getExportFileName($output = 'csv') {
 488     return ts('CiviMail Mailings');
 489   }
 490
 491   /**
 492    * @param $parent
 493    */
 494   public function setParent($parent) {
 495     $this->_parent = $parent;
 496   }
 497
 498   /**
 499    * @param array $params
 500    * @param bool $sortBy
 501    *
 502    * @return int|string
 503    */
 504   public function whereClause(&$params, $sortBy = TRUE) {
 505     $values = $clauses = array();
 506     $isFormSubmitted = $this->_parent->get('hidden_find_mailings');
 507
 508     $title = $this->_parent->get('mailing_name');
 509     if ($title) {
 510       $clauses[] = 'name LIKE %1';
 511       if (strpos($title, '%') !== FALSE) {
 512         $params[1] = array($title, 'String', FALSE);
 513       }
 514       else {
 515         $params[1] = array($title, 'String', TRUE);
 516       }
 517     }
 518
 519     $dateClause1 = $dateClause2 = array();
 520     $from = $this->_parent->get('mailing_from');
 521     if (!CRM_Utils_System::isNull($from)) {
 522       if ($this->_parent->get('unscheduled')) {
 523         $dateClause1[] = 'civicrm_mailing.created_date >= %2';
 524       }
 525       else {
 526         $dateClause1[] = 'civicrm_mailing_job.start_date >= %2';
 527         $dateClause2[] = 'civicrm_mailing_job.scheduled_date >= %2';
 528       }
 529       $params[2] = array($from, 'String');
 530     }
 531
 532     $to = $this->_parent->get('mailing_to');
 533     if (!CRM_Utils_System::isNull($to)) {
 534       if ($this->_parent->get('unscheduled')) {
 535         $dateClause1[] = ' civicrm_mailing.created_date <= %3 ';
 536       }
 537       else {
 538         $dateClause1[] = 'civicrm_mailing_job.start_date <= %3';
 539         $dateClause2[] = 'civicrm_mailing_job.scheduled_date <= %3';
 540       }
 541       $params[3] = array($to, 'String');
 542     }
 543
 544     $dateClauses = array();
 545     if (!empty($dateClause1)) {
 546       $dateClauses[] = implode(' AND ', $dateClause1);
 547     }
 548     if (!empty($dateClause2)) {
 549       $dateClauses[] = implode(' AND ', $dateClause2);
 550     }
 551     $dateClauses = implode(' OR ', $dateClauses);
 552     if (!empty($dateClauses)) {
 553       $clauses[] = "({$dateClauses})";
 554     }
 555
 556     if ($this->_parent->get('sms')) {
 557       $clauses[] = "civicrm_mailing.sms_provider_id IS NOT NULL";
 558     }
 559     else {
 560       $clauses[] = "civicrm_mailing.sms_provider_id IS NULL";
 561     }
 562
 563     // get values submitted by form
 564     $isDraft = $this->_parent->get('status_unscheduled');
 565     $isArchived = $this->_parent->get('is_archived');
 566     $mailingStatus = $this->_parent->get('mailing_status');
 567
 568     if (!$isFormSubmitted && $this->_parent->get('scheduled')) {
 569       // mimic default behavior for scheduled screen
 570       $isArchived = 0;
 571       $mailingStatus = array('Scheduled' => 1, 'Complete' => 1, 'Running' => 1, 'Paused' => 1, 'Canceled' => 1);
 572     }
 573     if (!$isFormSubmitted && $this->_parent->get('archived')) {
 574       // mimic default behavior for archived screen
 575       $isArchived = 1;
 576     }
 577     if (!$isFormSubmitted && $this->_parent->get('unscheduled')) {
 578       // mimic default behavior for draft screen
 579       $isDraft = 1;
 580     }
 581
 582     $statusClauses = array();
 583     if ($isDraft) {
 584       $statusClauses[] = "civicrm_mailing.scheduled_id IS NULL";
 585     }
 586     if (!empty($mailingStatus)) {
 587       $statusClauses[] = "civicrm_mailing_job.status IN ('" . implode("', '", array_keys($mailingStatus)) . "')";
 588     }
 589     if (!empty($statusClauses)) {
 590       $clauses[] = "(" . implode(' OR ', $statusClauses) . ")";
 591     }
 592
 593     if (isset($isArchived)) {
 594       if ($isArchived) {
 595         $clauses[] = "civicrm_mailing.is_archived = 1";
 596       }
 597       else {
 598         $clauses[] = "(civicrm_mailing.is_archived IS NULL OR civicrm_mailing.is_archived = 0)";
 599       }
 600     }
 601
 602     if ($sortBy &&
 603       $this->_parent->_sortByCharacter !== NULL
 604     ) {
 605       $clauses[] = "name LIKE '" . strtolower(CRM_Core_DAO::escapeWildCardString($this->_parent->_sortByCharacter)) . "%'";
 606     }
 607
 608     // dont do a the below assignement when doing a
 609     // AtoZ pager clause
 610     if ($sortBy) {
 611       if (count($clauses) > 1) {
 612         $this->_parent->assign('isSearch', 1);
 613       }
 614       else {
 615         $this->_parent->assign('isSearch', 0);
 616       }
 617     }
 618
 619     $createOrSentBy = $this->_parent->get('sort_name');
 620     if (!CRM_Utils_System::isNull($createOrSentBy)) {
 621       $clauses[] = '(createdContact.sort_name LIKE %4 OR scheduledContact.sort_name LIKE %4)';
 622       $params[4] = array('%' . $createOrSentBy . '%', 'String');
 623     }
 624
 625     $createdId = $this->_parent->get('createdId');
 626     if ($createdId) {
 627       $clauses[] = "(created_id = {$createdId})";
 628       $params[5] = array($createdId, 'Integer');
 629     }
 630
 631     $campainIds = $this->_parent->get('campaign_id');
 632     if (!CRM_Utils_System::isNull($campainIds)) {
 633       if (!is_array($campainIds)) {
 634         $campaignIds = array($campaignIds);
 635       }
 636       $clauses[] = '( campaign_id IN ( ' . implode(' , ', array_values($campainIds)) . ' ) )';
 637     }
 638
 639     if ($language = $this->_parent->get('language')) {
 640       $clauses[] = "civicrm_mailing.language = %6";
 641       $params[6] = array($language, 'String');
 642     }
 643
 644     if (empty($clauses)) {
 645       return 1;
 646     }
 647
 648     return implode(' AND ', $clauses);
 649   }
 650
 651   public function pagerAtoZ() {
 652
 653     $params = array();
 654     $whereClause = $this->whereClause($params, FALSE);
 655
 656     $query = "
 657 SELECT DISTINCT UPPER(LEFT(name, 1)) as sort_name
 658 FROM civicrm_mailing
 659 LEFT JOIN civicrm_mailing_job ON (civicrm_mailing_job.mailing_id = civicrm_mailing.id)
 660 LEFT JOIN civicrm_contact createdContact ON ( civicrm_mailing.created_id = createdContact.id )
 661 LEFT JOIN civicrm_contact scheduledContact ON ( civicrm_mailing.scheduled_id = scheduledContact.id )
 662 WHERE $whereClause
 663 ORDER BY UPPER(LEFT(name, 1))
 664 ";
 665
 666     $dao = CRM_Core_DAO::executeQuery($query, $params);
 667
 668     $aToZBar = CRM_Utils_PagerAToZ::getAToZBar($dao, $this->_parent->_sortByCharacter, TRUE);
 669     $this->_parent->assign('aToZ', $aToZBar);
 670   }
 671
 672 }