CRM/Logging/ReportSummary.php

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