CRM/Report/Form/ActivitySummary.php

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