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