CRM/Report/Form/ActivitySummary.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.5                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2014                                |
   8  +--------------------------------------------------------------------+
   9  | This file is a part of CiviCRM.                                    |
  10  |                                                                    |
  11  | CiviCRM is free software; you can copy, modify, and distribute it  |
  12  | under the terms of the GNU Affero General Public License           |
  13  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  14  |                                                                    |
  15  | CiviCRM is distributed in the hope that it will be useful, but     |
  16  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  17  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  18  | See the GNU Affero General Public License for more details.        |
  19  |                                                                    |
  20  | You should have received a copy of the GNU Affero General Public   |
  21  | License and the CiviCRM Licensing Exception along                  |
  22  | with this program; if not, contact CiviCRM LLC                     |
  23  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  24  | GNU Affero General Public License or the licensing of CiviCRM,     |
  25  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  26  +--------------------------------------------------------------------+
  27 */
  28
  29 /**
  30  *
  31  * @package CRM
  32  * @copyright CiviCRM LLC (c) 2004-2014
  33  * $Id$
  34  *
  35  */
  36 class CRM_Report_Form_ActivitySummary extends CRM_Report_Form {
  37
  38   protected $_emailField = FALSE;
  39   protected $_phoneField = FALSE;
  40
  41   /**
  42    *
  43    */
  44   /**
  45    *
  46    */
  47   function __construct() {
  48     $this->_columns = array(
  49       'civicrm_contact' => array(
  50         'dao' => 'CRM_Contact_DAO_Contact',
  51         'fields' => array(
  52           'id' => array(
  53             'required' => TRUE,
  54             'no_display' => TRUE,
  55           ),
  56           'sort_name' => array(
  57             'title' => ts('Contact Name'),
  58             'default' => TRUE,
  59             'no_repeat' => TRUE,
  60           ),
  61         ),
  62         'filters' => array(
  63           'sort_name' => array(
  64             'title' => ts('Contact Name'),
  65           ),
  66         ),
  67         'group_bys' => array(
  68           'sort_name' => array(
  69             'name' => 'id',
  70             'title' => ts('Contact'),
  71             'default' => TRUE,
  72           ),
  73         ),
  74         'order_bys' => array(
  75           'sort_name' => array(
  76             'title' => ts('Contact Name')
  77           ),
  78         ),
  79         'grouping' => 'contact-fields',
  80       ),
  81       'civicrm_email' => array(
  82         'dao' => 'CRM_Core_DAO_Email',
  83         'fields' => array(
  84           'email' => array(
  85             'title' => 'Email',
  86             'default' => TRUE,
  87           ),
  88         ),
  89         'order_bys' => array(
  90           'email' => array(
  91             'title' => ts('Email')
  92           ),
  93         ),
  94         'grouping' => 'contact-fields',
  95       ),
  96       'civicrm_phone' => array(
  97         'dao' => 'CRM_Core_DAO_Email',
  98         'fields' => array(
  99           'phone' => array(
 100             'title' => 'Phone'
 101           ),
 102         ),
 103         'grouping' => 'contact-fields',
 104       ),
 105       'civicrm_activity' => array(
 106         'dao' => 'CRM_Activity_DAO_Activity',
 107         'fields' => array(
 108           'activity_type_id' => array(
 109             'title' => ts('Activity Type'),
 110             'default' => TRUE,
 111             'type' => CRM_Utils_Type::T_STRING,
 112           ),
 113           'duration' => array(
 114             'title' => 'Duration',
 115             'statistics' => array(
 116               'sum' => ts('Total Duration'),
 117             ),
 118           ),
 119           'id' => array(
 120             'title' => 'Total Activities',
 121             'required' => TRUE,
 122             'statistics' => array(
 123               'count' => ts('Activity Count'),
 124             ),
 125           ),
 126         ),
 127         'filters' => array(
 128           'activity_date_time' => array(
 129             'operatorType' => CRM_Report_Form::OP_DATE
 130           ),
 131           'activity_type_id' => array(
 132             'title' => ts('Activity Type'),
 133             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 134             'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE),
 135           ),
 136           'status_id' => array(
 137             'title' => ts('Activity Status'),
 138             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 139             'options' => CRM_Core_PseudoConstant::activityStatus(),
 140           ),
 141           'priority_id' => array(
 142             'title' => ts('Priority'),
 143             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 144             'options' => CRM_Core_PseudoConstant::get('CRM_Activity_DAO_Activity', 'priority_id'),
 145           ),
 146         ),
 147         'group_bys' => array(
 148           'activity_date_time' => array(
 149               'title' => ts('Activity Date'),
 150               'frequency' => TRUE,
 151             ),
 152           'activity_type_id' => array(
 153             'title' => ts('Activity Type'),
 154             'default' => TRUE,
 155           ),
 156         ),
 157         'order_bys' => array(
 158           'activity_date_time' => array(
 159             'title' => ts('Activity Date')
 160           ),
 161           'activity_type_id' => array(
 162             'title' => ts('Activity Type')
 163           ),
 164         ),
 165         'grouping' => 'activity-fields',
 166         'alias' => 'activity',
 167       ),
 168     );
 169
 170     parent::__construct();
 171   }
 172
 173   function select() {
 174     $select = array();
 175     $this->_columnHeaders = array();
 176     foreach ($this->_columns as $tableName => $table) {
 177       if (array_key_exists('group_bys', $table)) {
 178         foreach ($table['group_bys'] as $fieldName => $field) {
 179           if (!empty($this->_params['group_bys'][$fieldName])) {
 180             if (isset($this->_params['group_bys_freq']) && !empty($this->_params['group_bys_freq'][$fieldName])) {
 181               switch ($this->_params['group_bys_freq'][$fieldName]) {
 182                 case 'YEARWEEK':
 183                   $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
 184
 185                   $select[] = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 186                   $select[] = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 187                   $field['title'] = 'Week';
 188                   break;
 189
 190                 case 'YEAR':
 191                   $select[] = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
 192                   $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 193                   $select[] = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 194                   $field['title'] = 'Year';
 195                   break;
 196
 197                 case 'MONTH':
 198                   $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
 199                   $select[] = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 200                   $select[] = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 201                   $field['title'] = 'Month';
 202                   break;
 203
 204                 case 'QUARTER':
 205                   $select[] = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
 206                   $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 207                   $select[] = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 208                   $field['title'] = 'Quarter';
 209                   break;
 210               }
 211               $this->_interval = $field['title'];
 212               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
 213               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
 214               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
 215
 216               // just to make sure these values are transfered to rows.
 217               // since we need that for calculation purpose,
 218               // e.g making subtotals look nicer or graphs
 219               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
 220               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
 221             }
 222           }
 223         }
 224       }
 225       if (array_key_exists('fields', $table)) {
 226         foreach ($table['fields'] as $fieldName => $field) {
 227           if (!empty($field['required']) || !empty($this->_params['fields'][$fieldName])) {
 228             if ($tableName == 'civicrm_email') {
 229               $this->_emailField = TRUE;
 230             }
 231             if ($tableName == 'civicrm_phone') {
 232               $this->_phoneField = TRUE;
 233             }
 234             if (!empty($field['statistics'])) {
 235               foreach ($field['statistics'] as $stat => $label) {
 236                 switch (strtolower($stat)) {
 237                   case 'count':
 238                     $select[] = "COUNT(DISTINCT({$field['dbAlias']})) as {$tableName}_{$fieldName}_{$stat}";
 239                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 240                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 241                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 242                     break;
 243
 244                   case 'sum':
 245                     $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}_{$stat}";
 246                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['type'] = CRM_Utils_Type::T_INT;
 247                     $this->_columnHeaders["{$tableName}_{$fieldName}_{$stat}"]['title'] = $label;
 248                     $this->_statFields[] = "{$tableName}_{$fieldName}_{$stat}";
 249                     break;
 250                 }
 251               }
 252             }
 253             elseif ($fieldName == 'activity_type_id') {
 254               if (empty($this->_params['group_bys']['activity_type_id'])) {
 255                 $select[] = "GROUP_CONCAT(DISTINCT {$field['dbAlias']}  ORDER BY {$field['dbAlias']} ) as {$tableName}_{$fieldName}";
 256               }
 257               else {
 258                 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 259               }
 260               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 261               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 262               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 263             }
 264             else {
 265               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 266               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 267               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 268               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 269             }
 270           }
 271         }
 272       }
 273     }
 274
 275     $this->_select = "SELECT " . implode(', ', $select) . " ";
 276   }
 277
 278   function from() {
 279     $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
 280     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 281     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 282     $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
 283
 284     $this->_from = "
 285         FROM civicrm_activity {$this->_aliases['civicrm_activity']}
 286
 287              LEFT JOIN civicrm_activity_contact target_activity
 288                     ON {$this->_aliases['civicrm_activity']}.id = target_activity.activity_id AND
 289                        target_activity.record_type_id = {$targetID}
 290              LEFT JOIN civicrm_activity_contact assignment_activity
 291                     ON {$this->_aliases['civicrm_activity']}.id = assignment_activity.activity_id AND
 292                        assignment_activity.record_type_id = {$assigneeID}
 293              LEFT JOIN civicrm_activity_contact source_activity
 294                     ON {$this->_aliases['civicrm_activity']}.id = source_activity.activity_id AND
 295                        source_activity.record_type_id = {$sourceID}
 296              LEFT JOIN civicrm_contact contact_civireport
 297                     ON target_activity.contact_id = contact_civireport.id
 298              LEFT JOIN civicrm_contact civicrm_contact_assignee
 299                     ON assignment_activity.contact_id = civicrm_contact_assignee.id
 300              LEFT JOIN civicrm_contact civicrm_contact_source
 301                     ON source_activity.contact_id = civicrm_contact_source.id
 302              {$this->_aclFrom}
 303              LEFT JOIN civicrm_option_value
 304                     ON ( {$this->_aliases['civicrm_activity']}.activity_type_id = civicrm_option_value.value )
 305              LEFT JOIN civicrm_option_group
 306                     ON civicrm_option_group.id = civicrm_option_value.option_group_id
 307              LEFT JOIN civicrm_case_activity
 308                     ON civicrm_case_activity.activity_id = {$this->_aliases['civicrm_activity']}.id
 309              LEFT JOIN civicrm_case
 310                     ON civicrm_case_activity.case_id = civicrm_case.id
 311              LEFT JOIN civicrm_case_contact
 312                     ON civicrm_case_contact.case_id = civicrm_case.id ";
 313
 314     if ($this->_emailField) {
 315       $this->_from .= "
 316             LEFT JOIN civicrm_email  {$this->_aliases['civicrm_email']}
 317                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 318                      {$this->_aliases['civicrm_email']}.is_primary = 1 ";
 319     }
 320
 321     if ($this->_phoneField) {
 322       $this->_from .= "
 323             LEFT JOIN civicrm_phone  {$this->_aliases['civicrm_phone']}
 324                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 325                      {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
 326     }
 327   }
 328
 329   function where() {
 330     $this->_where = " WHERE civicrm_option_group.name = 'activity_type' AND
 331                                 {$this->_aliases['civicrm_activity']}.is_test = 0 AND
 332                                 {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
 333                                 {$this->_aliases['civicrm_activity']}.is_current_revision = 1";
 334
 335     $clauses = array();
 336     foreach ($this->_columns as $tableName => $table) {
 337       if (array_key_exists('filters', $table)) {
 338
 339         foreach ($table['filters'] as $fieldName => $field) {
 340           $clause = NULL;
 341           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 342             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 343             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 344             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 345
 346             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 347           }
 348           else {
 349             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 350             if ($op) {
 351               $clause = $this->whereClause($field,
 352                 $op,
 353                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 354                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 355                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 356               );
 357             }
 358           }
 359
 360           if (!empty($clause)) {
 361             $clauses[] = $clause;
 362           }
 363         }
 364       }
 365     }
 366
 367     if (empty($clauses)) {
 368       $this->_where .= " ";
 369     }
 370     else {
 371       $this->_where .= " AND " . implode(' AND ', $clauses);
 372     }
 373
 374     if ($this->_aclWhere) {
 375       $this->_where .= " AND {$this->_aclWhere} ";
 376     }
 377   }
 378
 379   function groupBy() {
 380     $this->_groupBy = array();
 381     if (is_array($this->_params['group_bys']) &&
 382       !empty($this->_params['group_bys'])
 383     ) {
 384       foreach ($this->_columns as $tableName => $table) {
 385         if (array_key_exists('group_bys', $table)) {
 386           foreach ($table['group_bys'] as $fieldName => $field) {
 387             if (!empty($this->_params['group_bys'][$fieldName])) {
 388               if (!empty($field['chart'])) {
 389                 $this->assign('chartSupported', TRUE);
 390               }
 391               if (!empty($table['group_bys'][$fieldName]['frequency']) &&
 392                 !empty($this->_params['group_bys_freq'][$fieldName])
 393               ) {
 394
 395                 $append = "YEAR({$field['dbAlias']}),";
 396                 if (in_array(strtolower($this->_params['group_bys_freq'][$fieldName]),
 397                   array('year')
 398                 )) {
 399                   $append = '';
 400                 }
 401                 $this->_groupBy[] = "$append {$this->_params['group_bys_freq'][$fieldName]}({$field['dbAlias']})";
 402                 $append = TRUE;
 403               }
 404               else {
 405                 $this->_groupBy[] = $field['dbAlias'];
 406               }
 407             }
 408           }
 409         }
 410       }
 411
 412       $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy);
 413     }
 414     else {
 415       $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id ";
 416     }
 417   }
 418
 419   /**
 420    * @param $fields
 421    * @param $files
 422    * @param $self
 423    *
 424    * @return array
 425    */
 426   function formRule($fields, $files, $self) {
 427     $errors = array();
 428     $contactFields = array('sort_name', 'email', 'phone');
 429     if (!empty($fields['group_bys'])) {
 430
 431       if (!empty($fields['group_bys']['activity_type_id']) && empty($fields['group_bys']['sort_name'])) {
 432         foreach ($fields['fields'] as $fieldName => $val) {
 433           if (in_array($fieldName, $contactFields)) {
 434             $errors['fields'] = ts("Please select GroupBy 'Contact' to display Contact Fields");
 435             break;
 436           }
 437         }
 438       }
 439
 440       if (!empty($fields['group_bys']['activity_date_time'])) {
 441         if (!empty($fields['group_bys']['sort_name'])) {
 442           $errors['fields'] = ts("Please do not select GroupBy 'Activity Date' with GroupBy 'Contact'");
 443         }
 444         else {
 445           foreach ($fields['fields'] as $fieldName => $val) {
 446             if (in_array($fieldName, $contactFields)) {
 447               $errors['fields'] = ts("Please do not select any Contact Fields with GroupBy 'Activity Date'");
 448               break;
 449             }
 450           }
 451         }
 452       }
 453     }
 454     return $errors;
 455   }
 456
 457   function postProcess() {
 458     // get the acl clauses built before we assemble the query
 459     $this->buildACLClause($this->_aliases['civicrm_contact']);
 460     parent::postProcess();
 461   }
 462
 463   /**
 464    * @param $rows
 465    */
 466   function alterDisplay(&$rows) {
 467     // custom code to alter rows
 468
 469     $entryFound = FALSE;
 470     $activityType = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
 471     $flagContact = 0;
 472
 473     $onHover = ts('View Contact Summary for this Contact');
 474     foreach ($rows as $rowNum => $row) {
 475
 476       if (array_key_exists('civicrm_contact_sort_name', $row) && $this->_outputMode != 'csv') {
 477         if ($value = $row['civicrm_contact_id']) {
 478
 479           if ($rowNum == 0) {
 480             $priviousContact = $value;
 481           }
 482           else {
 483             if ($priviousContact == $value) {
 484               $flagContact = 1;
 485               $priviousContact = $value;
 486             }
 487             else {
 488               $flagContact = 0;
 489               $priviousContact = $value;
 490             }
 491           }
 492
 493           if ($flagContact == 1) {
 494             $rows[$rowNum]['civicrm_contact_sort_name'] = "";
 495
 496             if (array_key_exists('civicrm_email_email', $row)) {
 497               $rows[$rowNum]['civicrm_email_email'] = "";
 498             }
 499             if (array_key_exists('civicrm_phone_phone', $row)) {
 500               $rows[$rowNum]['civicrm_phone_phone'] = "";
 501             }
 502           }
 503           else {
 504             $url = CRM_Utils_System::url('civicrm/contact/view',
 505               'reset=1&cid=' . $value,
 506               $this->_absoluteUrl
 507             );
 508
 509             $rows[$rowNum]['civicrm_contact_sort_name'] = "<a href='$url'>" . $row['civicrm_contact_sort_name'] .
 510               '</a>';
 511           }
 512           $entryFound = TRUE;
 513         }
 514       }
 515
 516       if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
 517         if ($value = $row['civicrm_activity_activity_type_id']) {
 518
 519           $value = explode(',', $value);
 520           foreach ($value as $key => $id) {
 521             $value[$key] = $activityType[$id];
 522           }
 523
 524           $rows[$rowNum]['civicrm_activity_activity_type_id'] = implode(' , ', $value);
 525           $entryFound = TRUE;
 526         }
 527       }
 528
 529       if (!$entryFound) {
 530         break;
 531       }
 532     }
 533   }
 534 }
 535