CRM/Report/Form/ActivitySummary.php

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