CRM/Logging/ReportSummary.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   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-2018
  32  * $Id$
  33  */
  34 class CRM_Logging_ReportSummary extends CRM_Report_Form {
  35   protected $cid;
  36
  37   protected $_logTables = array();
  38
  39   protected $loggingDB;
  40
  41   /**
  42    * The log table currently being processed.
  43    *
  44    * @var string
  45    */
  46   protected $currentLogTable;
  47
  48   /**
  49    * Class constructor.
  50    */
  51   public function __construct() {
  52     // don’t display the ‘Add these Contacts to Group’ button
  53     $this->_add2groupSupported = FALSE;
  54
  55     $dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN);
  56     $this->loggingDB = $dsn['database'];
  57
  58     // used for redirect back to contact summary
  59     $this->cid = CRM_Utils_Request::retrieve('cid', 'Integer');
  60
  61     $this->_logTables = array(
  62       'log_civicrm_contact' => array(
  63         'fk' => 'id',
  64       ),
  65       'log_civicrm_email' => array(
  66         'fk' => 'contact_id',
  67         'log_type' => 'Contact',
  68       ),
  69       'log_civicrm_phone' => array(
  70         'fk' => 'contact_id',
  71         'log_type' => 'Contact',
  72       ),
  73       'log_civicrm_address' => array(
  74         'fk' => 'contact_id',
  75         'log_type' => 'Contact',
  76       ),
  77       'log_civicrm_note' => array(
  78         'fk' => 'entity_id',
  79         'entity_table' => TRUE,
  80         'bracket_info' => array(
  81           'table' => 'log_civicrm_note',
  82           'column' => 'subject',
  83         ),
  84       ),
  85       'log_civicrm_note_comment' => array(
  86         'fk' => 'entity_id',
  87         'table_name' => 'log_civicrm_note',
  88         'joins' => array(
  89           'table' => 'log_civicrm_note',
  90           'join' => "entity_log_civireport.entity_id = fk_table.id AND entity_log_civireport.entity_table = 'civicrm_note'",
  91         ),
  92         'entity_table' => TRUE,
  93         'bracket_info' => array(
  94           'table' => 'log_civicrm_note',
  95           'column' => 'subject',
  96         ),
  97       ),
  98       'log_civicrm_group_contact' => array(
  99         'fk' => 'contact_id',
 100         'bracket_info' => array(
 101           'entity_column' => 'group_id',
 102           'table' => 'log_civicrm_group',
 103           'column' => 'title',
 104         ),
 105         'action_column' => 'status',
 106         'log_type' => 'Group',
 107       ),
 108       'log_civicrm_entity_tag' => array(
 109         'fk' => 'entity_id',
 110         'bracket_info' => array(
 111           'entity_column' => 'tag_id',
 112           'table' => 'log_civicrm_tag',
 113           'column' => 'name',
 114         ),
 115         'entity_table' => TRUE,
 116       ),
 117       'log_civicrm_relationship' => array(
 118         'fk' => 'contact_id_a',
 119         'bracket_info' => array(
 120           'entity_column' => 'relationship_type_id',
 121           'table' => 'log_civicrm_relationship_type',
 122           'column' => 'label_a_b',
 123         ),
 124       ),
 125       'log_civicrm_activity_contact' => array(
 126         'fk' => 'contact_id',
 127         'table_name' => 'log_civicrm_activity_contact',
 128         'log_type' => 'Activity',
 129         'field' => 'activity_id',
 130         'extra_joins' => array(
 131           'table' => 'log_civicrm_activity',
 132           'join' => 'extra_table.id = entity_log_civireport.activity_id',
 133         ),
 134
 135         'bracket_info' => array(
 136           'entity_column' => 'activity_type_id',
 137           'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE),
 138           'lookup_table' => 'log_civicrm_activity',
 139         ),
 140       ),
 141       'log_civicrm_case' => array(
 142         'fk' => 'contact_id',
 143         'joins' => array(
 144           'table' => 'log_civicrm_case_contact',
 145           'join' => 'entity_log_civireport.id = fk_table.case_id',
 146         ),
 147         'bracket_info' => array(
 148           'entity_column' => 'case_type_id',
 149           'options' => CRM_Case_BAO_Case::buildOptions('case_type_id', 'search'),
 150         ),
 151       ),
 152     );
 153
 154     $logging = new CRM_Logging_Schema();
 155
 156     // build _logTables for contact custom tables
 157     $customTables = $logging->entityCustomDataLogTables('Contact');
 158     foreach ($customTables as $table) {
 159       $this->_logTables[$table] = array(
 160         'fk' => 'entity_id',
 161         'log_type' => 'Contact',
 162       );
 163     }
 164
 165     // build _logTables for address custom tables
 166     $customTables = $logging->entityCustomDataLogTables('Address');
 167     foreach ($customTables as $table) {
 168       $this->_logTables[$table] = array(
 169         // For join of fk_table with contact table.
 170         'fk' => 'contact_id',
 171         'joins' => array(
 172           // fk_table
 173           'table' => 'log_civicrm_address',
 174           'join' => 'entity_log_civireport.entity_id = fk_table.id',
 175         ),
 176         'log_type' => 'Contact',
 177       );
 178     }
 179
 180     // Allow log tables to be extended via report hooks.
 181     CRM_Report_BAO_Hook::singleton()->alterLogTables($this, $this->_logTables);
 182
 183     parent::__construct();
 184   }
 185
 186   public function groupBy() {
 187     $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';
 188   }
 189
 190   /**
 191    * Adjust query for the activity_contact table.
 192    *
 193    * As this is just a join table the ID we REALLY care about is the activity id.
 194    *
 195    * @param string $tableName
 196    * @param string $tableKey
 197    * @param string $fieldName
 198    * @param string $field
 199    *
 200    * @return string
 201    */
 202   public function selectClause(&$tableName, $tableKey, &$fieldName, &$field) {
 203     if ($this->currentLogTable == 'log_civicrm_activity_contact' && $fieldName == 'id') {
 204       $alias = "{$tableName}_{$fieldName}";
 205       $select[] = "{$tableName}.activity_id as $alias";
 206       $this->_selectAliases[] = $alias;
 207       return "activity_id";
 208     }
 209     if ($fieldName == 'log_grouping') {
 210       if ($this->currentLogTable != 'log_civicrm_activity_contact') {
 211         return 1;
 212       }
 213       $mergeActivityID = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Contact Merged');
 214       return " IF (entity_log_civireport.log_action = 'Insert' AND extra_table.activity_type_id = $mergeActivityID , GROUP_CONCAT(entity_log_civireport.contact_id), 1) ";
 215     }
 216   }
 217
 218   public function where() {
 219     // reset where clause as its called multiple times, every time insert sql is built.
 220     $this->_whereClauses = array();
 221
 222     parent::where();
 223     $this->_where .= " AND (entity_log_civireport.log_action != 'Initialization')";
 224   }
 225
 226   public function postProcess() {
 227     $this->beginPostProcess();
 228     $rows = array();
 229
 230     $tempColumns = "id int(10),  log_civicrm_entity_log_grouping varchar(32)";
 231     if (!empty($this->_params['fields']['log_action'])) {
 232       $tempColumns .= ", log_action varchar(64)";
 233     }
 234     $tempColumns .= ", log_type varchar(64), log_user_id int(10), log_date timestamp";
 235     if (!empty($this->_params['fields']['altered_contact'])) {
 236       $tempColumns .= ", altered_contact varchar(128)";
 237     }
 238     $tempColumns .= ", altered_contact_id int(10), log_conn_id varchar(17), is_deleted tinyint(4)";
 239     if (!empty($this->_params['fields']['display_name'])) {
 240       $tempColumns .= ", display_name varchar(128)";
 241     }
 242
 243     // temp table to hold all altered contact-ids
 244     $sql = "CREATE TEMPORARY TABLE civicrm_temp_civireport_logsummary ( {$tempColumns} ) ENGINE=HEAP";
 245     CRM_Core_DAO::executeQuery($sql);
 246     $this->addToDeveloperTab($sql);
 247
 248     $logTypes = CRM_Utils_Array::value('log_type_value', $this->_params);
 249     unset($this->_params['log_type_value']);
 250     if (empty($logTypes)) {
 251       foreach (array_keys($this->_logTables) as $table) {
 252         $type = $this->getLogType($table);
 253         $logTypes[$type] = $type;
 254       }
 255     }
 256
 257     $logTypeTableClause = '(1)';
 258     if ($logTypeTableValue = CRM_Utils_Array::value("log_type_table_value", $this->_params)) {
 259       $logTypeTableClause = $this->whereClause($this->_columns['log_civicrm_entity']['filters']['log_type_table'],
 260         $this->_params['log_type_table_op'], $logTypeTableValue, NULL, NULL);
 261       unset($this->_params['log_type_table_value']);
 262     }
 263
 264     foreach ($this->_logTables as $entity => $detail) {
 265       if ((in_array($this->getLogType($entity), $logTypes) &&
 266           CRM_Utils_Array::value('log_type_op', $this->_params) == 'in') ||
 267         (!in_array($this->getLogType($entity), $logTypes) &&
 268           CRM_Utils_Array::value('log_type_op', $this->_params) == 'notin')
 269       ) {
 270         $this->currentLogTable = $entity;
 271         $sql = $this->buildQuery(FALSE);
 272         $sql = str_replace("entity_log_civireport.log_type as", "'{$entity}' as", $sql);
 273         $sql = "INSERT IGNORE INTO civicrm_temp_civireport_logsummary {$sql}";
 274         CRM_Core_DAO::executeQuery($sql);
 275         $this->addToDeveloperTab($sql);
 276       }
 277     }
 278
 279     $this->currentLogTable = '';
 280
 281     // add computed log_type column so that we can do a group by after that, which will help
 282     // alterDisplay() counts sync with pager counts
 283     $sql = "SELECT DISTINCT log_type FROM civicrm_temp_civireport_logsummary";
 284     $dao = CRM_Core_DAO::executeQuery($sql);
 285     $this->addToDeveloperTab($sql);
 286     $replaceWith = array();
 287     while ($dao->fetch()) {
 288       $type = $this->getLogType($dao->log_type);
 289       if (!array_key_exists($type, $replaceWith)) {
 290         $replaceWith[$type] = array();
 291       }
 292       $replaceWith[$type][] = $dao->log_type;
 293     }
 294     foreach ($replaceWith as $type => $tables) {
 295       if (!empty($tables)) {
 296         $replaceWith[$type] = implode("','", $tables);
 297       }
 298     }
 299
 300     $sql = "ALTER TABLE civicrm_temp_civireport_logsummary ADD COLUMN log_civicrm_entity_log_type_label varchar(64)";
 301     CRM_Core_DAO::executeQuery($sql);
 302     $this->addToDeveloperTab($sql);
 303     foreach ($replaceWith as $type => $in) {
 304       $sql = "UPDATE civicrm_temp_civireport_logsummary SET log_civicrm_entity_log_type_label='{$type}', log_date=log_date WHERE log_type IN('$in')";
 305       CRM_Core_DAO::executeQuery($sql);
 306       $this->addToDeveloperTab($sql);
 307     }
 308
 309     // note the group by columns are same as that used in alterDisplay as $newRows - $key
 310     $this->limit();
 311     $this->orderBy();
 312     $sql = "{$this->_select}
 313 FROM civicrm_temp_civireport_logsummary entity_log_civireport
 314 WHERE {$logTypeTableClause}
 315 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, log_civicrm_entity_log_grouping
 316 {$this->_orderBy}
 317 {$this->_limit} ";
 318     $sql = str_replace('modified_contact_civireport.display_name', 'entity_log_civireport.altered_contact', $sql);
 319     $sql = str_replace('modified_contact_civireport.id', 'entity_log_civireport.altered_contact_id', $sql);
 320     $sql = str_replace(array(
 321       'modified_contact_civireport.',
 322       'altered_by_contact_civireport.',
 323     ), 'entity_log_civireport.', $sql);
 324     $this->buildRows($sql, $rows);
 325     $this->addToDeveloperTab($sql);
 326
 327     // format result set.
 328     $this->formatDisplay($rows);
 329
 330     // assign variables to templates
 331     $this->doTemplateAssignment($rows);
 332
 333     // do print / pdf / instance stuff if needed
 334     $this->endPostProcess($rows);
 335   }
 336
 337   /**
 338    * Get log type.
 339    *
 340    * @param string $entity
 341    *
 342    * @return string
 343    */
 344   public function getLogType($entity) {
 345     if (!empty($this->_logTables[$entity]['log_type'])) {
 346       return $this->_logTables[$entity]['log_type'];
 347     }
 348     $logType = ucfirst(substr($entity, strrpos($entity, '_') + 1));
 349     return $logType;
 350   }
 351
 352   /**
 353    * Get entity value.
 354    *
 355    * @param int $id
 356    * @param $entity
 357    * @param $logDate
 358    *
 359    * @return mixed|null|string
 360    */
 361   public function getEntityValue($id, $entity, $logDate) {
 362     if (!empty($this->_logTables[$entity]['bracket_info'])) {
 363       if (!empty($this->_logTables[$entity]['bracket_info']['entity_column'])) {
 364         $logTable = !empty($this->_logTables[$entity]['table_name']) ? $this->_logTables[$entity]['table_name'] : $entity;
 365         if (!empty($this->_logTables[$entity]['bracket_info']['lookup_table'])) {
 366           $logTable = $this->_logTables[$entity]['bracket_info']['lookup_table'];
 367         }
 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
 373         $entityID = CRM_Core_DAO::singleValueQuery($sql, array(
 374           1 => array(
 375             CRM_Utils_Date::isoToMysql($logDate),
 376             'Timestamp',
 377           ),
 378           2 => array($id, 'Integer'),
 379         ));
 380       }
 381       else {
 382         $entityID = $id;
 383       }
 384
 385       if ($entityID && $logDate &&
 386         array_key_exists('table', $this->_logTables[$entity]['bracket_info'])
 387       ) {
 388         $sql = "
 389 SELECT {$this->_logTables[$entity]['bracket_info']['column']}
 390 FROM  `{$this->loggingDB}`.{$this->_logTables[$entity]['bracket_info']['table']}
 391 WHERE  log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
 392         return CRM_Core_DAO::singleValueQuery($sql, array(
 393           1 => array(CRM_Utils_Date::isoToMysql($logDate), 'Timestamp'),
 394           2 => array($entityID, 'Integer'),
 395         ));
 396       }
 397       else {
 398         if (array_key_exists('options', $this->_logTables[$entity]['bracket_info']) &&
 399           $entityID
 400         ) {
 401           return CRM_Utils_Array::value($entityID, $this->_logTables[$entity]['bracket_info']['options']);
 402         }
 403       }
 404     }
 405     return NULL;
 406   }
 407
 408   /**
 409    * Get entity action.
 410    *
 411    * @param int $id
 412    * @param int $connId
 413    * @param $entity
 414    * @param $oldAction
 415    *
 416    * @return null|string
 417    */
 418   public function getEntityAction($id, $connId, $entity, $oldAction) {
 419     if (!empty($this->_logTables[$entity]['action_column'])) {
 420       $sql = "select {$this->_logTables[$entity]['action_column']} from `{$this->loggingDB}`.{$entity} where id = %1 AND log_conn_id = %2";
 421       $newAction = CRM_Core_DAO::singleValueQuery($sql, array(
 422         1 => array($id, 'Integer'),
 423         2 => array($connId, 'String'),
 424       ));
 425
 426       switch ($entity) {
 427         case 'log_civicrm_group_contact':
 428           if ($oldAction !== 'Update') {
 429             $newAction = $oldAction;
 430           }
 431           if ($oldAction == 'Insert') {
 432             $newAction = 'Added';
 433           }
 434           break;
 435       }
 436       return $newAction;
 437     }
 438     return NULL;
 439   }
 440
 441 }