CRM/Report/Form/ActivitySummary.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.3                                                |
   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; function __construct() {
  40     $this->_columns = array(
  41       'civicrm_contact' =>
  42       array(
  43         'dao' => 'CRM_Contact_DAO_Contact',
  44         'fields' =>
  45         array(
  46           'id' =>
  47           array(
  48             'required' => TRUE,
  49             'no_display' => TRUE,
  50           ),
  51           'sort_name' =>
  52           array('title' => ts('Contact Name'),
  53             'default' => TRUE,
  54             'no_repeat' => TRUE,
  55           ),
  56         ),
  57         'filters' =>
  58         array(
  59           'sort_name' =>
  60           array('title' => ts('Contact Name'),
  61           ),
  62         ),
  63         'group_bys' =>
  64         array(
  65           'sort_name' =>
  66           array(
  67             'name' => 'id',
  68             'title' => ts('Contact'),
  69             'default' => TRUE,
  70           ),
  71         ),
  72         'order_bys' =>
  73         array(
  74           'sort_name' =>
  75           array('title' => ts('Contact Name')),
  76         ),
  77         'grouping' => 'contact-fields',
  78       ),
  79       'civicrm_email' =>
  80       array(
  81         'dao' => 'CRM_Core_DAO_Email',
  82         'fields' =>
  83         array(
  84           'email' =>
  85           array(
  86             'title' => 'Email',
  87             'default' => TRUE,
  88           ),
  89         ),
  90         'order_bys' =>
  91         array(
  92           'email' =>
  93           array('title' => ts('Email')),
  94         ),
  95         'grouping' => 'contact-fields',
  96       ),
  97       'civicrm_phone' =>
  98       array(
  99         'dao' => 'CRM_Core_DAO_Email',
 100         'fields' =>
 101         array(
 102           'phone' =>
 103           array('title' => 'Phone'),
 104         ),
 105         'grouping' => 'contact-fields',
 106       ),
 107       'civicrm_activity' =>
 108       array(
 109         'dao' => 'CRM_Activity_DAO_Activity',
 110         'fields' =>
 111         array(
 112           'activity_type_id' =>
 113           array('title' => ts('Activity Type'),
 114             'default' => TRUE,
 115             'type' => CRM_Utils_Type::T_STRING,
 116           ),
 117           'duration' =>
 118           array(
 119             'title' => 'Duration',
 120             'statistics' =>
 121             array(
 122               'sum' => ts('Total Duration'),
 123             ),
 124           ),
 125           'id' =>
 126           array(
 127             'title' => 'Total Activities',
 128             'required' => TRUE,
 129             'statistics' =>
 130             array(
 131               'count' => ts('Activity Count'),
 132             ),
 133           ),
 134         ),
 135         'filters' =>
 136         array(
 137           'activity_date_time' =>
 138           array('operatorType' => CRM_Report_Form::OP_DATE),
 139           'activity_type_id' =>
 140           array('title' => ts('Activity Type'),
 141             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 142             'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE),
 143           ),
 144           'status_id' =>
 145           array('title' => ts('Activity Status'),
 146             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 147             'options' => CRM_Core_PseudoConstant::activityStatus(),
 148           ),
 149           'priority_id' =>
 150           array('title' => ts('Priority'),
 151             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 152             'options' => CRM_Core_PseudoConstant::get('CRM_Activity_DAO_Activity', 'priority_id'),
 153           ),
 154         ),
 155         'group_bys' =>
 156         array(
 157           'activity_date_time' =>
 158           array('title' => ts('Activity Date'),
 159             'frequency' => TRUE,
 160           ),
 161           'activity_type_id' =>
 162           array('title' => ts('Activity Type'),
 163             'default' => TRUE,
 164           ),
 165         ),
 166         'order_bys' =>
 167         array(
 168           'activity_date_time' =>
 169           array('title' => ts('Activity Date')),
 170           'activity_type_id' =>
 171           array('title' => ts('Activity Type')),
 172         ),
 173         'grouping' => 'activity-fields',
 174         'alias' => 'activity',
 175       ),
 176     );
 177
 178     parent::__construct();
 179   }
 180
 181   function select() {
 182     $select = array();
 183     $this->_columnHeaders = array();
 184     foreach ($this->_columns as $tableName => $table) {
 185       if (array_key_exists('group_bys', $table)) {
 186         foreach ($table['group_bys'] as $fieldName => $field) {
 187           if (CRM_Utils_Array::value($fieldName, $this->_params['group_bys'])) {
 188
 189             switch (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
 190               case 'YEARWEEK':
 191                 $select[] = "DATE_SUB({$field['dbAlias']}, INTERVAL WEEKDAY({$field['dbAlias']}) DAY) AS {$tableName}_{$fieldName}_start";
 192
 193                 $select[]       = "YEARWEEK({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 194                 $select[]       = "WEEKOFYEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 195                 $field['title'] = 'Week';
 196                 break;
 197
 198               case 'YEAR':
 199                 $select[]       = "MAKEDATE(YEAR({$field['dbAlias']}), 1)  AS {$tableName}_{$fieldName}_start";
 200                 $select[]       = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 201                 $select[]       = "YEAR({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 202                 $field['title'] = 'Year';
 203                 break;
 204
 205               case 'MONTH':
 206                 $select[]       = "DATE_SUB({$field['dbAlias']}, INTERVAL (DAYOFMONTH({$field['dbAlias']})-1) DAY) as {$tableName}_{$fieldName}_start";
 207                 $select[]       = "MONTH({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 208                 $select[]       = "MONTHNAME({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 209                 $field['title'] = 'Month';
 210                 break;
 211
 212               case 'QUARTER':
 213                 $select[]       = "STR_TO_DATE(CONCAT( 3 * QUARTER( {$field['dbAlias']} ) -2 , '/', '1', '/', YEAR( {$field['dbAlias']} ) ), '%m/%d/%Y') AS {$tableName}_{$fieldName}_start";
 214                 $select[]       = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_subtotal";
 215                 $select[]       = "QUARTER({$field['dbAlias']}) AS {$tableName}_{$fieldName}_interval";
 216                 $field['title'] = 'Quarter';
 217                 break;
 218             }
 219             if (CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])) {
 220               $this->_interval = $field['title'];
 221               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['title'] = $field['title'] . ' Beginning';
 222               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['type'] = $field['type'];
 223               $this->_columnHeaders["{$tableName}_{$fieldName}_start"]['group_by'] = $this->_params['group_bys_freq'][$fieldName];
 224
 225               // just to make sure these values are transfered to rows.
 226               // since we need that for calculation purpose,
 227               // e.g making subtotals look nicer or graphs
 228               $this->_columnHeaders["{$tableName}_{$fieldName}_interval"] = array('no_display' => TRUE);
 229               $this->_columnHeaders["{$tableName}_{$fieldName}_subtotal"] = array('no_display' => TRUE);
 230             }
 231           }
 232         }
 233       }
 234       if (array_key_exists('fields', $table)) {
 235         foreach ($table['fields'] as $fieldName => $field) {
 236           if (CRM_Utils_Array::value('required', $field) ||
 237             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 238           ) {
 239             if ($tableName == 'civicrm_email') {
 240               $this->_emailField = TRUE;
 241             }
 242             if ($tableName == 'civicrm_phone') {
 243               $this->_phoneField = TRUE;
 244             }
 245             if (CRM_Utils_Array::value('statistics', $field)) {
 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 (!CRM_Utils_Array::value('activity_type_id', $this->_params['group_bys'])) {
 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
 291     $this->_from = "
 292         FROM civicrm_activity {$this->_aliases['civicrm_activity']}
 293
 294              LEFT JOIN civicrm_activity_target target_activity
 295                     ON {$this->_aliases['civicrm_activity']}.id = target_activity.activity_id
 296              LEFT JOIN civicrm_activity_assignment assignment_activity
 297                     ON {$this->_aliases['civicrm_activity']}.id = assignment_activity.activity_id
 298              LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 299                     ON ({$this->_aliases['civicrm_activity']}.source_contact_id = {$this->_aliases['civicrm_contact']}.id OR
 300                          target_activity.target_contact_id = {$this->_aliases['civicrm_contact']}.id                      OR
 301                          assignment_activity.assignee_contact_id = {$this->_aliases['civicrm_contact']}.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 (CRM_Utils_Array::value($fieldName, $this->_params['group_bys'])) {
 388               if (CRM_Utils_Array::value('chart', $field)) {
 389                 $this->assign('chartSupported', TRUE);
 390               }
 391               if (CRM_Utils_Array::value('frequency', $table['group_bys'][$fieldName]) &&
 392                 CRM_Utils_Array::value($fieldName, $this->_params['group_bys_freq'])
 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   function formRule($fields, $files, $self) {
 420     $errors = array();
 421     $contactFields = array('sort_name', 'email', 'phone');
 422     if (CRM_Utils_Array::value('group_bys', $fields)) {
 423
 424       if (CRM_Utils_Array::value('activity_type_id', $fields['group_bys']) &&
 425         !CRM_Utils_Array::value('sort_name', $fields['group_bys'])
 426       ) {
 427         foreach ($fields['fields'] as $fieldName => $val) {
 428           if (in_array($fieldName, $contactFields)) {
 429             $errors['fields'] = ts("Please select GroupBy 'Contact' to display Contact Fields");
 430             break;
 431           }
 432         }
 433       }
 434
 435       if (CRM_Utils_Array::value('activity_date_time', $fields['group_bys'])) {
 436         if (CRM_Utils_Array::value('sort_name', $fields['group_bys'])) {
 437           $errors['fields'] = ts("Please do not select GroupBy 'Activity Date' with GroupBy 'Contact'");
 438         }
 439         else {
 440           foreach ($fields['fields'] as $fieldName => $val) {
 441             if (in_array($fieldName, $contactFields)) {
 442               $errors['fields'] = ts("Please do not select any Contact Fields with GroupBy 'Activity Date'");
 443               break;
 444             }
 445           }
 446         }
 447       }
 448     }
 449     return $errors;
 450   }
 451
 452   function postProcess() {
 453     // get the acl clauses built before we assemble the query
 454     $this->buildACLClause($this->_aliases['civicrm_contact']);
 455     parent::postProcess();
 456   }
 457
 458   function alterDisplay(&$rows) {
 459     // custom code to alter rows
 460
 461     $entryFound   = FALSE;
 462     $activityType = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
 463     $flagContact  = 0;
 464
 465     $onHover = ts('View Contact Summary for this Contact');
 466     foreach ($rows as $rowNum => $row) {
 467
 468       if (array_key_exists('civicrm_contact_sort_name', $row) && $this->_outputMode != 'csv') {
 469         if ($value = $row['civicrm_contact_id']) {
 470
 471           if ($rowNum == 0) {
 472             $priviousContact = $value;
 473           }
 474           else {
 475             if ($priviousContact == $value) {
 476               $flagContact = 1;
 477               $priviousContact = $value;
 478             }
 479             else {
 480               $flagContact = 0;
 481               $priviousContact = $value;
 482             }
 483           }
 484
 485           if ($flagContact == 1) {
 486             $rows[$rowNum]['civicrm_contact_sort_name'] = "";
 487
 488             if (array_key_exists('civicrm_email_email', $row)) {
 489               $rows[$rowNum]['civicrm_email_email'] = "";
 490             }
 491             if (array_key_exists('civicrm_phone_phone', $row)) {
 492               $rows[$rowNum]['civicrm_phone_phone'] = "";
 493             }
 494           }
 495           else {
 496             $url = CRM_Utils_System::url('civicrm/contact/view',
 497               'reset=1&cid=' . $value,
 498               $this->_absoluteUrl
 499             );
 500
 501             $rows[$rowNum]['civicrm_contact_sort_name'] = "<a href='$url'>" . $row['civicrm_contact_sort_name'] . '</a>';
 502           }
 503           $entryFound = TRUE;
 504         }
 505       }
 506
 507       if (array_key_exists('civicrm_activity_activity_type_id', $row)) {
 508         if ($value = $row['civicrm_activity_activity_type_id']) {
 509
 510           $value = explode(',', $value);
 511           foreach ($value as $key => $id) {
 512             $value[$key] = $activityType[$id];
 513           }
 514
 515           $rows[$rowNum]['civicrm_activity_activity_type_id'] = implode(' , ', $value);
 516           $entryFound = TRUE;
 517         }
 518       }
 519
 520       if (!$entryFound) {
 521         break;
 522       }
 523     }
 524   }
 525 }
 526