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

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