3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2018
34 class CRM_Logging_ReportSummary
extends CRM_Report_Form
{
37 protected $_logTables = array();
42 * Clause used in the final run of buildQuery but not when doing preliminary work.
44 * (We do this to all the api to run this report since it doesn't call postProcess).
48 protected $logTypeTableClause;
51 * The log table currently being processed.
55 protected $currentLogTable;
60 public function __construct() {
61 // don’t display the ‘Add these Contacts to Group’ button
62 $this->_add2groupSupported
= FALSE;
64 $dsn = defined('CIVICRM_LOGGING_DSN') ? DB
::parseDSN(CIVICRM_LOGGING_DSN
) : DB
::parseDSN(CIVICRM_DSN
);
65 $this->loggingDB
= $dsn['database'];
67 // used for redirect back to contact summary
68 $this->cid
= CRM_Utils_Request
::retrieve('cid', 'Integer');
70 $this->_logTables
= array(
71 'log_civicrm_contact' => array(
74 'log_civicrm_email' => array(
76 'log_type' => 'Contact',
78 'log_civicrm_phone' => array(
80 'log_type' => 'Contact',
82 'log_civicrm_address' => array(
84 'log_type' => 'Contact',
86 'log_civicrm_note' => array(
88 'entity_table' => TRUE,
89 'bracket_info' => array(
90 'table' => 'log_civicrm_note',
91 'column' => 'subject',
94 'log_civicrm_note_comment' => array(
96 'table_name' => 'log_civicrm_note',
98 'table' => 'log_civicrm_note',
99 'join' => "entity_log_civireport.entity_id = fk_table.id AND entity_log_civireport.entity_table = 'civicrm_note'",
101 'entity_table' => TRUE,
102 'bracket_info' => array(
103 'table' => 'log_civicrm_note',
104 'column' => 'subject',
107 'log_civicrm_group_contact' => array(
108 'fk' => 'contact_id',
109 'bracket_info' => array(
110 'entity_column' => 'group_id',
111 'table' => 'log_civicrm_group',
114 'action_column' => 'status',
115 'log_type' => 'Group',
117 'log_civicrm_entity_tag' => array(
119 'bracket_info' => array(
120 'entity_column' => 'tag_id',
121 'table' => 'log_civicrm_tag',
124 'entity_table' => TRUE,
126 'log_civicrm_relationship' => array(
127 'fk' => 'contact_id_a',
128 'bracket_info' => array(
129 'entity_column' => 'relationship_type_id',
130 'table' => 'log_civicrm_relationship_type',
131 'column' => 'label_a_b',
134 'log_civicrm_activity_contact' => array(
135 'fk' => 'contact_id',
136 'table_name' => 'log_civicrm_activity_contact',
137 'log_type' => 'Activity',
138 'field' => 'activity_id',
139 'extra_joins' => array(
140 'table' => 'log_civicrm_activity',
141 'join' => 'extra_table.id = entity_log_civireport.activity_id',
144 'bracket_info' => array(
145 'entity_column' => 'activity_type_id',
146 'options' => CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE),
147 'lookup_table' => 'log_civicrm_activity',
150 'log_civicrm_case' => array(
151 'fk' => 'contact_id',
153 'table' => 'log_civicrm_case_contact',
154 'join' => 'entity_log_civireport.id = fk_table.case_id',
156 'bracket_info' => array(
157 'entity_column' => 'case_type_id',
158 'options' => CRM_Case_BAO_Case
::buildOptions('case_type_id', 'search'),
163 $logging = new CRM_Logging_Schema();
165 // build _logTables for contact custom tables
166 $customTables = $logging->entityCustomDataLogTables('Contact');
167 foreach ($customTables as $table) {
168 $this->_logTables
[$table] = array(
170 'log_type' => 'Contact',
174 // build _logTables for address custom tables
175 $customTables = $logging->entityCustomDataLogTables('Address');
176 foreach ($customTables as $table) {
177 $this->_logTables
[$table] = array(
178 // For join of fk_table with contact table.
179 'fk' => 'contact_id',
182 'table' => 'log_civicrm_address',
183 'join' => 'entity_log_civireport.entity_id = fk_table.id',
185 'log_type' => 'Contact',
189 // Allow log tables to be extended via report hooks.
190 CRM_Report_BAO_Hook
::singleton()->alterLogTables($this, $this->_logTables
);
192 parent
::__construct();
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';
200 * Adjust query for the activity_contact table.
202 * As this is just a join table the ID we REALLY care about is the activity id.
204 * @param string $tableName
205 * @param string $tableKey
206 * @param string $fieldName
207 * @param string $field
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";
218 if ($fieldName == 'log_grouping') {
219 if ($this->currentLogTable
!= 'log_civicrm_activity_contact') {
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) ";
227 public function where() {
228 // reset where clause as its called multiple times, every time insert sql is built.
229 $this->_whereClauses
= array();
232 $this->_where
.= " AND (entity_log_civireport.log_action != 'Initialization')";
238 * @param string $entity
242 public function getLogType($entity) {
243 if (!empty($this->_logTables
[$entity]['log_type'])) {
244 return $this->_logTables
[$entity]['log_type'];
246 $logType = ucfirst(substr($entity, strrpos($entity, '_') +
1));
257 * @return mixed|null|string
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'];
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";
271 $entityID = CRM_Core_DAO
::singleValueQuery($sql, array(
273 CRM_Utils_Date
::isoToMysql($logDate),
276 2 => array($id, 'Integer'),
283 if ($entityID && $logDate &&
284 array_key_exists('table', $this->_logTables
[$entity]['bracket_info'])
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, array(
291 1 => array(CRM_Utils_Date
::isoToMysql($logDate), 'Timestamp'),
292 2 => array($entityID, 'Integer'),
296 if (array_key_exists('options', $this->_logTables
[$entity]['bracket_info']) &&
299 return CRM_Utils_Array
::value($entityID, $this->_logTables
[$entity]['bracket_info']['options']);
314 * @return null|string
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, array(
320 1 => array($id, 'Integer'),
321 2 => array($connId, 'String'),
325 case 'log_civicrm_group_contact':
326 if ($oldAction !== 'Update') {
327 $newAction = $oldAction;
329 if ($oldAction == 'Insert') {
330 $newAction = 'Added';
340 * Build the temporary tables for the query.
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)";
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)";
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)";
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);
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;
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']);
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')
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);
394 $this->currentLogTable
= '';
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 = array();
402 while ($dao->fetch()) {
403 $type = $this->getLogType($dao->log_type
);
404 if (!array_key_exists($type, $replaceWith)) {
405 $replaceWith[$type] = array();
407 $replaceWith[$type][] = $dao->log_type
;
409 foreach ($replaceWith as $type => $tables) {
410 if (!empty($tables)) {
411 $replaceWith[$type] = implode("','", $tables);
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);
423 $this->logTypeTableClause
= $logTypeTableClause;
427 * Common processing, also via api/unit tests.
429 public function beginPostProcessCommon() {
430 parent
::beginPostProcessCommon();
431 $this->buildTemporaryTables();
435 * Build the report query.
437 * We override this in order to be able to run from the api.
439 * @param bool $applyLimit
443 public function buildQuery($applyLimit = TRUE) {
444 if (!$this->logTypeTableClause
) {
445 return parent
::buildQuery($applyLimit);
447 // note the group by columns are same as that used in alterDisplay as $newRows - $key
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
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(array(
459 'modified_contact_civireport.',
460 'altered_by_contact_civireport.',
461 ), 'entity_log_civireport.', $sql);
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');