CRM/Logging/ReportSummary.php

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