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