CRM/Contact/Form/Search/Custom/ActivitySearch.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2020                                |
   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-2020
  32  */
  33 class CRM_Contact_Form_Search_Custom_ActivitySearch extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {
  34
  35   protected $_formValues;
  36   protected $_aclFrom = NULL;
  37   protected $_aclWhere = NULL;
  38
  39   /**
  40    * Class constructor.
  41    *
  42    * @param array $formValues
  43    */
  44   public function __construct(&$formValues) {
  45     $this->_formValues = self::formatSavedSearchFields($formValues);
  46
  47     /**
  48      * Define the columns for search result rows
  49      */
  50     $this->_columns = [
  51       ts('Name') => 'sort_name',
  52       ts('Status') => 'activity_status',
  53       ts('Activity Type') => 'activity_type',
  54       ts('Activity Subject') => 'activity_subject',
  55       ts('Scheduled By') => 'source_contact',
  56       ts('Scheduled Date') => 'activity_date',
  57       ' ' => 'activity_id',
  58       '  ' => 'activity_type_id',
  59       '   ' => 'case_id',
  60       ts('Location') => 'location',
  61       ts('Duration') => 'duration',
  62       ts('Details') => 'details',
  63       ts('Assignee') => 'assignee',
  64     ];
  65
  66     $this->_groupId = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup',
  67       'activity_status',
  68       'id',
  69       'name'
  70     );
  71
  72     //Add custom fields to columns array for inclusion in export
  73     $groupTree = CRM_Core_BAO_CustomGroup::getTree('Activity');
  74
  75     //use simplified formatted groupTree
  76     $groupTree = CRM_Core_BAO_CustomGroup::formatGroupTree($groupTree);
  77
  78     //cycle through custom fields and assign to _columns array
  79     foreach ($groupTree as $key) {
  80       foreach ($key['fields'] as $field) {
  81         $fieldlabel = $key['title'] . ": " . $field['label'];
  82         $this->_columns[$fieldlabel] = $field['column_name'];
  83       }
  84     }
  85   }
  86
  87   /**
  88    * @param CRM_Core_Form $form
  89    */
  90   public function buildForm(&$form) {
  91
  92     /**
  93      * You can define a custom title for the search form
  94      */
  95     $this->setTitle('Find Latest Activities');
  96
  97     /**
  98      * Define the search form fields here
  99      */
 100     // Allow user to choose which type of contact to limit search on
 101     $form->add('select', 'contact_type', ts('Find...'), CRM_Core_SelectValues::contactType());
 102
 103     // Text box for Activity Subject
 104     $form->add('text',
 105       'activity_subject',
 106       ts('Activity Subject')
 107     );
 108
 109     // Select box for Activity Type
 110     $activityType = ['' => ' - select activity - '] + CRM_Core_PseudoConstant::activityType();
 111
 112     $form->add('select', 'activity_type_id', ts('Activity Type'),
 113       $activityType,
 114       FALSE
 115     );
 116
 117     // textbox for Activity Status
 118     $activityStatus = ['' => ' - select status - '] + CRM_Core_PseudoConstant::activityStatus();
 119
 120     $form->add('select', 'activity_status_id', ts('Activity Status'),
 121       $activityStatus,
 122       FALSE
 123     );
 124
 125     // Activity Date range
 126     $form->add('datepicker', 'start_date', ts('Activity Date From'), [], FALSE, ['time' => FALSE]);
 127     $form->add('datepicker', 'end_date', ts('...through'), [], FALSE, ['time' => FALSE]);
 128
 129     // Contact Name field
 130     $form->add('text', 'sort_name', ts('Contact Name'));
 131
 132     /**
 133      * If you are using the sample template, this array tells the template fields to render
 134      * for the search form.
 135      */
 136     $form->assign('elements', [
 137       'contact_type',
 138       'activity_subject',
 139       'activity_type_id',
 140       'activity_status_id',
 141       'start_date',
 142       'end_date',
 143       'sort_name',
 144     ]);
 145   }
 146
 147   /**
 148    * Define the smarty template used to layout the search form and results listings.
 149    */
 150   public function templateFile() {
 151     return 'CRM/Contact/Form/Search/Custom/ActivitySearch.tpl';
 152   }
 153
 154   /**
 155    * Construct the search query.
 156    *
 157    * @param int $offset
 158    * @param int $rowcount
 159    * @param null $sort
 160    * @param bool $includeContactIDs
 161    * @param bool $justIDs
 162    *
 163    * @return string
 164    */
 165   public function all(
 166     $offset = 0, $rowcount = 0, $sort = NULL,
 167     $includeContactIDs = FALSE, $justIDs = FALSE
 168   ) {
 169
 170     // SELECT clause must include contact_id as an alias for civicrm_contact.id
 171     if ($justIDs) {
 172       $select = 'contact_a.id as contact_id';
 173     }
 174     else {
 175       $select = '
 176                 contact_a.id                as contact_id,
 177                 contact_a.sort_name         as sort_name,
 178                 contact_a.contact_type      as contact_type,
 179                 activity.id                 as activity_id,
 180                 activity.activity_type_id   as activity_type_id,
 181                 contact_b.sort_name         as source_contact,
 182                 ov1.label                   as activity_type,
 183                 activity.subject            as activity_subject,
 184                 activity.activity_date_time as activity_date,
 185                 ov2.label                   as activity_status,
 186                 cca.case_id                 as case_id,
 187                 activity.location           as location,
 188                 activity.duration           as duration,
 189                 activity.details            as details,
 190                 assignment.activity_id      as assignment_activity,
 191                 contact_c.display_name      as assignee
 192                 ';
 193     }
 194
 195     $from = $this->from();
 196
 197     $where = $this->where($includeContactIDs);
 198
 199     if (!empty($where)) {
 200       $where = "WHERE $where";
 201     }
 202
 203     // add custom group fields to SELECT and FROM clause
 204     $groupTree = CRM_Core_BAO_CustomGroup::getTree('Activity');
 205
 206     foreach ($groupTree as $key) {
 207       if (!empty($key['extends']) && $key['extends'] == 'Activity') {
 208         $select .= ", " . $key['table_name'] . ".*";
 209         $from .= " LEFT JOIN " . $key['table_name'] . " ON " . $key['table_name'] . ".entity_id = activity.id";
 210       }
 211     }
 212     // end custom groups add
 213
 214     $sql = " SELECT $select FROM   $from $where ";
 215
 216     //no need to add order when only contact Ids.
 217     if (!$justIDs) {
 218       // Define ORDER BY for query in $sort, with default value
 219       if (!empty($sort)) {
 220         if (is_string($sort)) {
 221           $sort = CRM_Utils_Type::escape($sort, 'String');
 222           $sql .= " ORDER BY $sort ";
 223         }
 224         else {
 225           $sql .= ' ORDER BY ' . trim($sort->orderBy());
 226         }
 227       }
 228       else {
 229         $sql .= 'ORDER BY contact_a.sort_name, activity.activity_date_time DESC, activity.activity_type_id, activity.status_id, activity.subject';
 230       }
 231     }
 232     else {
 233       //CRM-14107, since there could be multiple activities against same contact,
 234       //we need to provide GROUP BY on contact id to prevent duplicacy on prev/next entries
 235       $sql .= 'GROUP BY contact_a.id
 236 ORDER BY contact_a.sort_name';
 237     }
 238
 239     if ($rowcount > 0 && $offset >= 0) {
 240       $offset = CRM_Utils_Type::escape($offset, 'Int');
 241       $rowcount = CRM_Utils_Type::escape($rowcount, 'Int');
 242       $sql .= " LIMIT $offset, $rowcount ";
 243     }
 244     return $sql;
 245   }
 246
 247   /**
 248    * Alters the date display in the Activity Date Column. We do this after we already have
 249    * the result so that sorting on the date column stays pertinent to the numeric date value
 250    * @param $row
 251    */
 252   public function alterRow(&$row) {
 253     $row['activity_date'] = CRM_Utils_Date::customFormat($row['activity_date'], '%B %E%f, %Y %l:%M %P');
 254   }
 255
 256   /**
 257    * Regular JOIN statements here to limit results to contacts who have activities.
 258    * @return string
 259    */
 260   public function from() {
 261     $this->buildACLClause('contact_a');
 262     $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
 263     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 264     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 265     $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
 266
 267     $from = "
 268         civicrm_activity activity
 269             LEFT JOIN civicrm_activity_contact target
 270                  ON activity.id = target.activity_id AND target.record_type_id = {$targetID}
 271             JOIN civicrm_contact contact_a
 272                  ON contact_a.id = target.contact_id
 273             JOIN civicrm_option_value ov1
 274                  ON activity.activity_type_id = ov1.value AND ov1.option_group_id = 2
 275             JOIN civicrm_option_value ov2
 276                  ON activity.status_id = ov2.value AND ov2.option_group_id = {$this->_groupId}
 277             LEFT JOIN civicrm_activity_contact sourceContact
 278                  ON activity.id = sourceContact.activity_id AND sourceContact.record_type_id = {$sourceID}
 279             JOIN civicrm_contact contact_b
 280                  ON sourceContact.contact_id = contact_b.id
 281             LEFT JOIN civicrm_case_activity cca
 282                  ON activity.id = cca.activity_id
 283             LEFT JOIN civicrm_activity_contact assignment
 284                  ON activity.id = assignment.activity_id AND assignment.record_type_id = {$assigneeID}
 285             LEFT JOIN civicrm_contact contact_c
 286                  ON assignment.contact_id = contact_c.id {$this->_aclFrom}";
 287
 288     return $from;
 289   }
 290
 291   /**
 292    * WHERE clause is an array built from any required JOINS plus conditional filters based on search criteria field values.
 293    *
 294    * @param bool $includeContactIDs
 295    *
 296    * @return string
 297    */
 298   public function where($includeContactIDs = FALSE) {
 299     $clauses = [];
 300
 301     // add contact name search; search on primary name, source contact, assignee
 302     $contactname = $this->_formValues['sort_name'];
 303     if (!empty($contactname)) {
 304       $dao = new CRM_Core_DAO();
 305       $contactname = $dao->escape($contactname);
 306       $clauses[] = "(contact_a.sort_name LIKE '%{$contactname}%' OR
 307                            contact_b.sort_name LIKE '%{$contactname}%' OR
 308                            contact_c.display_name LIKE '%{$contactname}%')";
 309     }
 310
 311     $subject = $this->_formValues['activity_subject'];
 312
 313     if (!empty($this->_formValues['contact_type'])) {
 314       $clauses[] = "contact_a.contact_type LIKE '%{$this->_formValues['contact_type']}%'";
 315     }
 316
 317     if (!empty($subject)) {
 318       $dao = new CRM_Core_DAO();
 319       $subject = $dao->escape($subject);
 320       $clauses[] = "activity.subject LIKE '%{$subject}%'";
 321     }
 322
 323     if (!empty($this->_formValues['activity_status_id'])) {
 324       $clauses[] = "activity.status_id = {$this->_formValues['activity_status_id']}";
 325     }
 326
 327     if (!empty($this->_formValues['activity_type_id'])) {
 328       $clauses[] = "activity.activity_type_id = {$this->_formValues['activity_type_id']}";
 329     }
 330
 331     if (!empty($this->_formValues['start_date'])) {
 332       $clauses[] = "activity.activity_date_time >= '{$this->_formValues['start_date']} 00:00:00'";
 333     }
 334
 335     if (!empty($this->_formValues['end_date'])) {
 336       $clauses[] = "activity.activity_date_time <= '{$this->_formValues['end_date']} 23:59:59'";
 337     }
 338
 339     if ($includeContactIDs) {
 340       $contactIDs = [];
 341       foreach ($this->_formValues as $id => $value) {
 342         if ($value &&
 343           substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
 344         ) {
 345           $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
 346         }
 347       }
 348
 349       if (!empty($contactIDs)) {
 350         $contactIDs = implode(', ', $contactIDs);
 351         $clauses[] = "contact_a.id IN ( $contactIDs )";
 352       }
 353     }
 354
 355     if ($this->_aclWhere) {
 356       $clauses[] = " {$this->_aclWhere} ";
 357     }
 358     return implode(' AND ', $clauses);
 359   }
 360
 361   /*
 362    * Functions below generally don't need to be modified
 363    */
 364
 365   /**
 366    * @inheritDoc
 367    */
 368   public function count() {
 369     $sql = $this->all();
 370
 371     $dao = CRM_Core_DAO::executeQuery($sql);
 372     return $dao->N;
 373   }
 374
 375   /**
 376    * @param int $offset
 377    * @param int $rowcount
 378    * @param null $sort
 379    * @param bool $returnSQL Not used; included for consistency with parent; SQL is always returned
 380    *
 381    * @return string
 382    */
 383   public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = TRUE) {
 384     return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
 385   }
 386
 387   /**
 388    * @return array
 389    */
 390   public function &columns() {
 391     return $this->_columns;
 392   }
 393
 394   /**
 395    * @param $title
 396    */
 397   public function setTitle($title) {
 398     if ($title) {
 399       CRM_Utils_System::setTitle($title);
 400     }
 401     else {
 402       CRM_Utils_System::setTitle(ts('Search'));
 403     }
 404   }
 405
 406   /**
 407    * @return null
 408    */
 409   public function summary() {
 410     return NULL;
 411   }
 412
 413   /**
 414    * @param string $tableAlias
 415    */
 416   public function buildACLClause($tableAlias = 'contact') {
 417     list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
 418   }
 419
 420   /**
 421    * Format saved search fields for this custom group.
 422    *
 423    * Note this is a function to facilitate the transition to jcalendar for
 424    * saved search groups. In time it can be stripped out again.
 425    *
 426    * @param array $formValues
 427    *
 428    * @return array
 429    */
 430   public static function formatSavedSearchFields($formValues) {
 431     $dateFields = [
 432       'start_date',
 433       'end_date',
 434     ];
 435     foreach ($formValues as $element => $value) {
 436       if (in_array($element, $dateFields) && !empty($value)) {
 437         $formValues[$element] = date('Y-m-d', strtotime($value));
 438       }
 439     }
 440
 441     return $formValues;
 442   }
 443
 444 }