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

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