CRM/Logging/ReportSummary.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26 */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Logging_ReportSummary extends CRM_Report_Form {
  36   protected $cid;
  37
  38   protected $_logTables = array();
  39
  40   protected $loggingDB;
  41
  42   /**
  43    *
  44    */
  45   function __construct() {
  46     // don’t display the ‘Add these Contacts to Group’ button
  47     $this->_add2groupSupported = FALSE;
  48
  49     $dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN);
  50     $this->loggingDB = $dsn['database'];
  51
  52     // used for redirect back to contact summary
  53     $this->cid = CRM_Utils_Request::retrieve('cid', 'Integer', CRM_Core_DAO::$_nullObject);
  54
  55     $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
  56     $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
  57     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
  58     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
  59
  60     $this->_logTables =
  61       array(
  62         'log_civicrm_contact' =>
  63         array(
  64           'fk' => 'id',
  65         ),
  66         'log_civicrm_email' =>
  67         array(
  68           'fk' => 'contact_id',
  69           'log_type' => 'Contact',
  70         ),
  71         'log_civicrm_phone' =>
  72         array(
  73           'fk' => 'contact_id',
  74           'log_type' => 'Contact',
  75         ),
  76         'log_civicrm_address' =>
  77         array(
  78           'fk' => 'contact_id',
  79           'log_type' => 'Contact',
  80         ),
  81         'log_civicrm_note' =>
  82         array(
  83           'fk' => 'entity_id',
  84           'entity_table' => TRUE,
  85           'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'),
  86         ),
  87         'log_civicrm_note_comment' =>
  88         array(
  89           'fk' => 'entity_id',
  90           'table_name' => 'log_civicrm_note',
  91           'joins' => array(
  92             'table' => 'log_civicrm_note',
  93             'join' => "entity_log_civireport.entity_id = fk_table.id AND entity_log_civireport.entity_table = 'civicrm_note'"
  94           ),
  95           'entity_table' => TRUE,
  96           'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'),
  97         ),
  98         'log_civicrm_group_contact' =>
  99         array(
 100           'fk' => 'contact_id',
 101           'bracket_info' => array('entity_column' => 'group_id', 'table' => 'log_civicrm_group', 'column' => 'title'),
 102           'action_column' => 'status',
 103           'log_type' => 'Group',
 104         ),
 105         'log_civicrm_entity_tag' =>
 106         array(
 107           'fk' => 'entity_id',
 108           'bracket_info' => array('entity_column' => 'tag_id', 'table' => 'log_civicrm_tag', 'column' => 'name'),
 109           'entity_table' => TRUE
 110         ),
 111         'log_civicrm_relationship' =>
 112         array(
 113           'fk' => 'contact_id_a',
 114           'bracket_info' => array(
 115             'entity_column' => 'relationship_type_id',
 116             'table' => 'log_civicrm_relationship_type',
 117             'column' => 'label_a_b'
 118           ),
 119         ),
 120         'log_civicrm_activity_for_target' =>
 121         array(
 122           'fk' => 'contact_id',
 123           'table_name' => 'log_civicrm_activity',
 124           'joins' => array(
 125             'table' => 'log_civicrm_activity_contact',
 126             'join' => "(entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$targetID})"
 127           ),
 128           'bracket_info' => array(
 129             'entity_column' => 'activity_type_id',
 130             'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE)
 131           ),
 132           'log_type' => 'Activity',
 133         ),
 134         'log_civicrm_activity_for_assignee' =>
 135         array(
 136           'fk' => 'contact_id',
 137           'table_name' => 'log_civicrm_activity',
 138           'joins' => array(
 139             'table' => 'log_civicrm_activity_contact',
 140             'join' => "entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$assigneeID}"
 141           ),
 142           'bracket_info' => array(
 143             'entity_column' => 'activity_type_id',
 144             'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE)
 145           ),
 146           'log_type' => 'Activity',
 147         ),
 148         'log_civicrm_activity_for_source' =>
 149         array(
 150           'fk' => 'contact_id',
 151           'table_name' => 'log_civicrm_activity',
 152           'joins' => array(
 153             'table' => 'log_civicrm_activity_contact',
 154             'join' => "entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$sourceID}"
 155           ),
 156           'bracket_info' => array(
 157             'entity_column' => 'activity_type_id',
 158             'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE)
 159           ),
 160           'log_type' => 'Activity',
 161         ),
 162         'log_civicrm_case' =>
 163         array(
 164           'fk' => 'contact_id',
 165           'joins' => array(
 166             'table' => 'log_civicrm_case_contact',
 167             'join' => 'entity_log_civireport.id = fk_table.case_id'
 168           ),
 169           'bracket_info' => array(
 170             'entity_column' => 'case_type_id',
 171             'options' => CRM_Case_PseudoConstant::caseType('title', FALSE)
 172           ),
 173         ),
 174       );
 175
 176     $logging = new CRM_Logging_Schema;
 177
 178     // build _logTables for contact custom tables
 179     $customTables = $logging->entityCustomDataLogTables('Contact');
 180     foreach ($customTables as $table) {
 181       $this->_logTables[$table] = array('fk' => 'entity_id', 'log_type' => 'Contact');
 182     }
 183
 184     // build _logTables for address custom tables
 185     $customTables = $logging->entityCustomDataLogTables('Address');
 186     foreach ($customTables as $table) {
 187       $this->_logTables[$table] =
 188         array(
 189           'fk' => 'contact_id',// for join of fk_table with contact table
 190           'joins' => array(
 191             'table' => 'log_civicrm_address', // fk_table
 192             'join'  => 'entity_log_civireport.entity_id = fk_table.id'
 193           ),
 194           'log_type' => 'Contact'
 195         );
 196     }
 197
 198     // allow log tables to be extended via report hooks
 199     CRM_Report_BAO_Hook::singleton()->alterLogTables($this, $this->_logTables);
 200
 201     parent::__construct();
 202   }
 203
 204   function groupBy() {
 205     $this->_groupBy = 'GROUP BY entity_log_civireport.log_conn_id, entity_log_civireport.log_user_id, EXTRACT(DAY_MICROSECOND FROM entity_log_civireport.log_date), entity_log_civireport.id';
 206   }
 207
 208   function select() {
 209     $select = array();
 210     $this->_columnHeaders = array();
 211     foreach ($this->_columns as $tableName => $table) {
 212       if (array_key_exists('fields', $table)) {
 213         foreach ($table['fields'] as $fieldName => $field) {
 214           if (CRM_Utils_Array::value('required', $field) or CRM_Utils_Array::value($fieldName, $this->_params['fields'])) {
 215             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 216             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 217             $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value('no_display', $field);
 218             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 219           }
 220         }
 221       }
 222     }
 223     $this->_select = 'SELECT ' . implode(', ', $select) . ' ';
 224   }
 225
 226   function where() {
 227     // reset where clause as its called multiple times, every time insert sql is built.
 228     $this->_whereClauses = array();
 229
 230     parent::where();
 231     $this->_where .= " AND (entity_log_civireport.log_action != 'Initialization')";
 232   }
 233
 234   function postProcess() {
 235     $this->beginPostProcess();
 236     $rows = array();
 237
 238     $tempColumns = "id int(10)";
 239     if (!empty($this->_params['fields']['log_action'])) {
 240       $tempColumns .= ", log_action varchar(64)";
 241     }
 242     $tempColumns .= ", log_type varchar(64), log_user_id int(10), log_date timestamp";
 243     if (!empty($this->_params['fields']['altered_contact'])) {
 244       $tempColumns .= ", altered_contact varchar(128)";
 245     }
 246     $tempColumns .= ", altered_contact_id int(10), log_conn_id int(11), is_deleted tinyint(4)";
 247     if (!empty($this->_params['fields']['display_name'])) {
 248       $tempColumns .= ", display_name varchar(128)";
 249     }
 250
 251     // temp table to hold all altered contact-ids
 252     $sql = "CREATE TEMPORARY TABLE civicrm_temp_civireport_logsummary ( {$tempColumns} ) ENGINE=HEAP";
 253     CRM_Core_DAO::executeQuery($sql);
 254
 255     $logDateClause = $this->dateClause('log_date',
 256       CRM_Utils_Array::value("log_date_relative", $this->_params),
 257       CRM_Utils_Array::value("log_date_from", $this->_params),
 258       CRM_Utils_Array::value("log_date_to", $this->_params),
 259       CRM_Utils_Type::T_DATE,
 260       CRM_Utils_Array::value("log_date_from_time", $this->_params),
 261       CRM_Utils_Array::value("log_date_to_time", $this->_params));
 262     $logDateClause = $logDateClause ? "AND {$logDateClause}" : NULL;
 263
 264     $logTypes = CRM_Utils_Array::value('log_type_value', $this->_params);
 265     unset($this->_params['log_type_value']);
 266     if (empty($logTypes)) {
 267       foreach (array_keys($this->_logTables) as $table) {
 268         $type = $this->getLogType($table);
 269         $logTypes[$type] = $type;
 270       }
 271     }
 272
 273     $logTypeTableClause = '(1)';
 274     if ($logTypeTableValue = CRM_Utils_Array::value("log_type_table_value", $this->_params)) {
 275       $logTypeTableClause = $this->whereClause($this->_columns['log_civicrm_entity']['filters']['log_type_table'],
 276         $this->_params['log_type_table_op'], $logTypeTableValue, NULL, NULL);
 277       unset($this->_params['log_type_table_value']);
 278     }
 279
 280     foreach ($this->_logTables as $entity => $detail) {
 281       if ((in_array($this->getLogType($entity), $logTypes) &&
 282           CRM_Utils_Array::value('log_type_op', $this->_params) == 'in') ||
 283         (!in_array($this->getLogType($entity), $logTypes) &&
 284           CRM_Utils_Array::value('log_type_op', $this->_params) == 'notin')
 285       ) {
 286         $this->from($entity);
 287         $sql = $this->buildQuery(FALSE);
 288         $sql = str_replace("entity_log_civireport.log_type as", "'{$entity}' as", $sql);
 289         $sql = "INSERT IGNORE INTO civicrm_temp_civireport_logsummary {$sql}";
 290         CRM_Core_DAO::executeQuery($sql);
 291       }
 292     }
 293
 294     // add computed log_type column so that we can do a group by after that, which will help
 295     // alterDisplay() counts sync with pager counts
 296     $sql = "SELECT DISTINCT log_type FROM civicrm_temp_civireport_logsummary";
 297     $dao = CRM_Core_DAO::executeQuery($sql);
 298     $replaceWith = array();
 299     while ($dao->fetch()) {
 300       $type = $this->getLogType($dao->log_type);
 301       if (!array_key_exists($type, $replaceWith)) {
 302         $replaceWith[$type] = array();
 303       }
 304       $replaceWith[$type][] = $dao->log_type;
 305     }
 306     foreach ($replaceWith as $type => $tables) {
 307       if (!empty($tables)) {
 308         $replaceWith[$type] = implode("','", $tables);
 309       }
 310     }
 311
 312     $sql = "ALTER TABLE civicrm_temp_civireport_logsummary ADD COLUMN log_civicrm_entity_log_type_label varchar(64)";
 313     CRM_Core_DAO::executeQuery($sql);
 314     foreach ($replaceWith as $type => $in) {
 315       $sql = "UPDATE civicrm_temp_civireport_logsummary SET log_civicrm_entity_log_type_label='{$type}', log_date=log_date WHERE log_type IN('$in')";
 316       CRM_Core_DAO::executeQuery($sql);
 317     }
 318
 319     // note the group by columns are same as that used in alterDisplay as $newRows - $key
 320     $this->limit();
 321     $sql = "{$this->_select}
 322 FROM civicrm_temp_civireport_logsummary entity_log_civireport
 323 WHERE {$logTypeTableClause}
 324 GROUP BY log_civicrm_entity_log_date, log_civicrm_entity_log_type_label, log_civicrm_entity_log_conn_id, log_civicrm_entity_log_user_id, log_civicrm_entity_altered_contact_id
 325 ORDER BY log_civicrm_entity_log_date DESC {$this->_limit}";
 326     $sql = str_replace('modified_contact_civireport.display_name', 'entity_log_civireport.altered_contact', $sql);
 327     $sql = str_replace('modified_contact_civireport.id', 'entity_log_civireport.altered_contact_id', $sql);
 328     $sql = str_replace(array(
 329       'modified_contact_civireport.',
 330       'altered_by_contact_civireport.'
 331     ), 'entity_log_civireport.', $sql);
 332     $this->buildRows($sql, $rows);
 333
 334     // format result set.
 335     $this->formatDisplay($rows);
 336
 337     // assign variables to templates
 338     $this->doTemplateAssignment($rows);
 339
 340     // do print / pdf / instance stuff if needed
 341     $this->endPostProcess($rows);
 342   }
 343
 344   /**
 345    * @param $entity
 346    *
 347    * @return string
 348    */
 349   function getLogType($entity) {
 350     if (!empty($this->_logTables[$entity]['log_type'])) {
 351       return $this->_logTables[$entity]['log_type'];
 352     }
 353     $logType = ucfirst(substr($entity, strrpos($entity, '_') + 1));
 354     return $logType;
 355   }
 356
 357   /**
 358    * @param $id
 359    * @param $entity
 360    * @param $logDate
 361    *
 362    * @return mixed|null|string
 363    */
 364   function getEntityValue($id, $entity, $logDate) {
 365     if (!empty($this->_logTables[$entity]['bracket_info'])) {
 366       if (!empty($this->_logTables[$entity]['bracket_info']['entity_column'])) {
 367         $logTable = !empty($this->_logTables[$entity]['table_name']) ? $this->_logTables[$entity]['table_name'] : $entity;
 368         $sql = "
 369 SELECT {$this->_logTables[$entity]['bracket_info']['entity_column']}
 370   FROM `{$this->loggingDB}`.{$logTable}
 371  WHERE  log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
 372         $entityID = CRM_Core_DAO::singleValueQuery($sql, array(
 373           1 => array(
 374             CRM_Utils_Date::isoToMysql($logDate),
 375             'Timestamp'
 376           ),
 377           2 => array($id, 'Integer')
 378         ));
 379       }
 380       else {
 381         $entityID = $id;
 382       }
 383
 384
 385       if ($entityID && $logDate && array_key_exists('table', $this->_logTables[$entity]['bracket_info'])) {
 386         $sql = "
 387 SELECT {$this->_logTables[$entity]['bracket_info']['column']}
 388 FROM  `{$this->loggingDB}`.{$this->_logTables[$entity]['bracket_info']['table']}
 389 WHERE  log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
 390         return CRM_Core_DAO::singleValueQuery($sql, array(
 391           1 => array(CRM_Utils_Date::isoToMysql($logDate), 'Timestamp'),
 392           2 => array($entityID, 'Integer')
 393         ));
 394       }
 395       else {
 396         if (array_key_exists('options', $this->_logTables[$entity]['bracket_info']) && $entityID) {
 397           return CRM_Utils_Array::value($entityID, $this->_logTables[$entity]['bracket_info']['options']);
 398         }
 399       }
 400     }
 401     return NULL;
 402   }
 403
 404   /**
 405    * @param $id
 406    * @param $connId
 407    * @param $entity
 408    * @param $oldAction
 409    *
 410    * @return null|string
 411    */
 412   function getEntityAction($id, $connId, $entity, $oldAction) {
 413     if (!empty($this->_logTables[$entity]['action_column'])) {
 414       $sql = "select {$this->_logTables[$entity]['action_column']} from `{$this->loggingDB}`.{$entity} where id = %1 AND log_conn_id = %2";
 415       $newAction = CRM_Core_DAO::singleValueQuery($sql, array(
 416         1 => array($id, 'Integer'),
 417         2 => array($connId, 'Integer')
 418       ));
 419
 420       switch ($entity) {
 421         case 'log_civicrm_group_contact':
 422           if ($oldAction !== 'Update') {
 423             $newAction = $oldAction;
 424           }
 425           if ($oldAction == 'Insert') {
 426             $newAction = 'Added';
 427           }
 428           break;
 429       }
 430       return $newAction;
 431     }
 432     return NULL;
 433   }
 434 }