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

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