crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/tools/extensions/org.civicrm.search.activity/ActivitySearch.php

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