3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.5 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2014 |
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-2014
35 class CRM_Logging_ReportSummary
extends CRM_Report_Form
{
38 protected $_logTables = array();
45 function __construct() {
46 // don’t display the ‘Add these Contacts to Group’ button
47 $this->_add2groupSupported
= FALSE;
49 $dsn = defined('CIVICRM_LOGGING_DSN') ? DB
::parseDSN(CIVICRM_LOGGING_DSN
) : DB
::parseDSN(CIVICRM_DSN
);
50 $this->loggingDB
= $dsn['database'];
52 // used for redirect back to contact summary
53 $this->cid
= CRM_Utils_Request
::retrieve('cid', 'Integer', CRM_Core_DAO
::$_nullObject);
55 $activityContacts = CRM_Core_OptionGroup
::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
56 $sourceID = CRM_Utils_Array
::key('Activity Source', $activityContacts);
57 $assigneeID = CRM_Utils_Array
::key('Activity Assignees', $activityContacts);
58 $targetID = CRM_Utils_Array
::key('Activity Targets', $activityContacts);
62 'log_civicrm_contact' =>
66 'log_civicrm_email' =>
69 'log_type' => 'Contact',
71 'log_civicrm_phone' =>
74 'log_type' => 'Contact',
76 'log_civicrm_address' =>
79 'log_type' => 'Contact',
84 'entity_table' => TRUE,
85 'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'),
87 'log_civicrm_note_comment' =>
90 'table_name' => 'log_civicrm_note',
92 'table' => 'log_civicrm_note',
93 'join' => "entity_log_civireport.entity_id = fk_table.id AND entity_log_civireport.entity_table = 'civicrm_note'"
95 'entity_table' => TRUE,
96 'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'),
98 'log_civicrm_group_contact' =>
100 'fk' => 'contact_id',
101 'bracket_info' => array('entity_column' => 'group_id', 'table' => 'log_civicrm_group', 'column' => 'title'),
102 'action_column' => 'status',
103 'log_type' => 'Group',
105 'log_civicrm_entity_tag' =>
108 'bracket_info' => array('entity_column' => 'tag_id', 'table' => 'log_civicrm_tag', 'column' => 'name'),
109 'entity_table' => TRUE
111 'log_civicrm_relationship' =>
113 'fk' => 'contact_id_a',
114 'bracket_info' => array(
115 'entity_column' => 'relationship_type_id',
116 'table' => 'log_civicrm_relationship_type',
117 'column' => 'label_a_b'
120 'log_civicrm_activity_for_target' =>
122 'fk' => 'contact_id',
123 'table_name' => 'log_civicrm_activity',
125 'table' => 'log_civicrm_activity_contact',
126 'join' => "(entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$targetID})"
128 'bracket_info' => array(
129 'entity_column' => 'activity_type_id',
130 'options' => CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE)
132 'log_type' => 'Activity',
134 'log_civicrm_activity_for_assignee' =>
136 'fk' => 'contact_id',
137 'table_name' => 'log_civicrm_activity',
139 'table' => 'log_civicrm_activity_contact',
140 'join' => "entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$assigneeID}"
142 'bracket_info' => array(
143 'entity_column' => 'activity_type_id',
144 'options' => CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE)
146 'log_type' => 'Activity',
148 'log_civicrm_activity_for_source' =>
150 'fk' => 'contact_id',
151 'table_name' => 'log_civicrm_activity',
153 'table' => 'log_civicrm_activity_contact',
154 'join' => "entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$sourceID}"
156 'bracket_info' => array(
157 'entity_column' => 'activity_type_id',
158 'options' => CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE)
160 'log_type' => 'Activity',
162 'log_civicrm_case' =>
164 'fk' => 'contact_id',
166 'table' => 'log_civicrm_case_contact',
167 'join' => 'entity_log_civireport.id = fk_table.case_id'
169 'bracket_info' => array(
170 'entity_column' => 'case_type_id',
171 'options' => CRM_Case_PseudoConstant
::caseType('title', FALSE)
176 $logging = new CRM_Logging_Schema
;
178 // build _logTables for contact custom tables
179 $customTables = $logging->entityCustomDataLogTables('Contact');
180 foreach ($customTables as $table) {
181 $this->_logTables
[$table] = array('fk' => 'entity_id', 'log_type' => 'Contact');
184 // build _logTables for address custom tables
185 $customTables = $logging->entityCustomDataLogTables('Address');
186 foreach ($customTables as $table) {
187 $this->_logTables
[$table] =
189 'fk' => 'contact_id',// for join of fk_table with contact table
191 'table' => 'log_civicrm_address', // fk_table
192 'join' => 'entity_log_civireport.entity_id = fk_table.id'
194 'log_type' => 'Contact'
198 // allow log tables to be extended via report hooks
199 CRM_Report_BAO_Hook
::singleton()->alterLogTables($this, $this->_logTables
);
201 parent
::__construct();
205 $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';
210 $this->_columnHeaders
= array();
211 foreach ($this->_columns
as $tableName => $table) {
212 if (array_key_exists('fields', $table)) {
213 foreach ($table['fields'] as $fieldName => $field) {
214 if (CRM_Utils_Array
::value('required', $field) or CRM_Utils_Array
::value($fieldName, $this->_params
['fields'])) {
215 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
216 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
217 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array
::value('no_display', $field);
218 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array
::value('title', $field);
223 $this->_select
= 'SELECT ' . implode(', ', $select) . ' ';
227 // reset where clause as its called multiple times, every time insert sql is built.
228 $this->_whereClauses
= array();
231 $this->_where
.= " AND (entity_log_civireport.log_action != 'Initialization')";
234 function postProcess() {
235 $this->beginPostProcess();
238 $tempColumns = "id int(10)";
239 if (!empty($this->_params
['fields']['log_action'])) {
240 $tempColumns .= ", log_action varchar(64)";
242 $tempColumns .= ", log_type varchar(64), log_user_id int(10), log_date timestamp";
243 if (!empty($this->_params
['fields']['altered_contact'])) {
244 $tempColumns .= ", altered_contact varchar(128)";
246 $tempColumns .= ", altered_contact_id int(10), log_conn_id int(11), is_deleted tinyint(4)";
247 if (!empty($this->_params
['fields']['display_name'])) {
248 $tempColumns .= ", display_name varchar(128)";
251 // temp table to hold all altered contact-ids
252 $sql = "CREATE TEMPORARY TABLE civicrm_temp_civireport_logsummary ( {$tempColumns} ) ENGINE=HEAP";
253 CRM_Core_DAO
::executeQuery($sql);
255 $logDateClause = $this->dateClause('log_date',
256 CRM_Utils_Array
::value("log_date_relative", $this->_params
),
257 CRM_Utils_Array
::value("log_date_from", $this->_params
),
258 CRM_Utils_Array
::value("log_date_to", $this->_params
),
259 CRM_Utils_Type
::T_DATE
,
260 CRM_Utils_Array
::value("log_date_from_time", $this->_params
),
261 CRM_Utils_Array
::value("log_date_to_time", $this->_params
));
262 $logDateClause = $logDateClause ?
"AND {$logDateClause}" : NULL;
264 $logTypes = CRM_Utils_Array
::value('log_type_value', $this->_params
);
265 unset($this->_params
['log_type_value']);
266 if (empty($logTypes)) {
267 foreach (array_keys($this->_logTables
) as $table) {
268 $type = $this->getLogType($table);
269 $logTypes[$type] = $type;
273 $logTypeTableClause = '(1)';
274 if ($logTypeTableValue = CRM_Utils_Array
::value("log_type_table_value", $this->_params
)) {
275 $logTypeTableClause = $this->whereClause($this->_columns
['log_civicrm_entity']['filters']['log_type_table'],
276 $this->_params
['log_type_table_op'], $logTypeTableValue, NULL, NULL);
277 unset($this->_params
['log_type_table_value']);
280 foreach ($this->_logTables
as $entity => $detail) {
281 if ((in_array($this->getLogType($entity), $logTypes) &&
282 CRM_Utils_Array
::value('log_type_op', $this->_params
) == 'in') ||
283 (!in_array($this->getLogType($entity), $logTypes) &&
284 CRM_Utils_Array
::value('log_type_op', $this->_params
) == 'notin')
286 $this->from($entity);
287 $sql = $this->buildQuery(FALSE);
288 $sql = str_replace("entity_log_civireport.log_type as", "'{$entity}' as", $sql);
289 $sql = "INSERT IGNORE INTO civicrm_temp_civireport_logsummary {$sql}";
290 CRM_Core_DAO
::executeQuery($sql);
294 // add computed log_type column so that we can do a group by after that, which will help
295 // alterDisplay() counts sync with pager counts
296 $sql = "SELECT DISTINCT log_type FROM civicrm_temp_civireport_logsummary";
297 $dao = CRM_Core_DAO
::executeQuery($sql);
298 $replaceWith = array();
299 while ($dao->fetch()) {
300 $type = $this->getLogType($dao->log_type
);
301 if (!array_key_exists($type, $replaceWith)) {
302 $replaceWith[$type] = array();
304 $replaceWith[$type][] = $dao->log_type
;
306 foreach ($replaceWith as $type => $tables) {
307 if (!empty($tables)) {
308 $replaceWith[$type] = implode("','", $tables);
312 $sql = "ALTER TABLE civicrm_temp_civireport_logsummary ADD COLUMN log_civicrm_entity_log_type_label varchar(64)";
313 CRM_Core_DAO
::executeQuery($sql);
314 foreach ($replaceWith as $type => $in) {
315 $sql = "UPDATE civicrm_temp_civireport_logsummary SET log_civicrm_entity_log_type_label='{$type}', log_date=log_date WHERE log_type IN('$in')";
316 CRM_Core_DAO
::executeQuery($sql);
319 // note the group by columns are same as that used in alterDisplay as $newRows - $key
321 $sql = "{$this->_select}
322 FROM civicrm_temp_civireport_logsummary entity_log_civireport
323 WHERE {$logTypeTableClause}
324 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
325 ORDER BY log_civicrm_entity_log_date DESC {$this->_limit}";
326 $sql = str_replace('modified_contact_civireport.display_name', 'entity_log_civireport.altered_contact', $sql);
327 $sql = str_replace('modified_contact_civireport.id', 'entity_log_civireport.altered_contact_id', $sql);
328 $sql = str_replace(array(
329 'modified_contact_civireport.',
330 'altered_by_contact_civireport.'
331 ), 'entity_log_civireport.', $sql);
332 $this->buildRows($sql, $rows);
334 // format result set.
335 $this->formatDisplay($rows);
337 // assign variables to templates
338 $this->doTemplateAssignment($rows);
340 // do print / pdf / instance stuff if needed
341 $this->endPostProcess($rows);
349 function getLogType($entity) {
350 if (!empty($this->_logTables
[$entity]['log_type'])) {
351 return $this->_logTables
[$entity]['log_type'];
353 $logType = ucfirst(substr($entity, strrpos($entity, '_') +
1));
362 * @return mixed|null|string
364 function getEntityValue($id, $entity, $logDate) {
365 if (!empty($this->_logTables
[$entity]['bracket_info'])) {
366 if (!empty($this->_logTables
[$entity]['bracket_info']['entity_column'])) {
367 $logTable = !empty($this->_logTables
[$entity]['table_name']) ?
$this->_logTables
[$entity]['table_name'] : $entity;
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 $entityID = CRM_Core_DAO
::singleValueQuery($sql, array(
374 CRM_Utils_Date
::isoToMysql($logDate),
377 2 => array($id, 'Integer')
385 if ($entityID && $logDate && array_key_exists('table', $this->_logTables
[$entity]['bracket_info'])) {
387 SELECT {$this->_logTables[$entity]['bracket_info']['column']}
388 FROM `{$this->loggingDB}`.{$this->_logTables[$entity]['bracket_info']['table']}
389 WHERE log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
390 return CRM_Core_DAO
::singleValueQuery($sql, array(
391 1 => array(CRM_Utils_Date
::isoToMysql($logDate), 'Timestamp'),
392 2 => array($entityID, 'Integer')
396 if (array_key_exists('options', $this->_logTables
[$entity]['bracket_info']) && $entityID) {
397 return CRM_Utils_Array
::value($entityID, $this->_logTables
[$entity]['bracket_info']['options']);
410 * @return null|string
412 function getEntityAction($id, $connId, $entity, $oldAction) {
413 if (!empty($this->_logTables
[$entity]['action_column'])) {
414 $sql = "select {$this->_logTables[$entity]['action_column']} from `{$this->loggingDB}`.{$entity} where id = %1 AND log_conn_id = %2";
415 $newAction = CRM_Core_DAO
::singleValueQuery($sql, array(
416 1 => array($id, 'Integer'),
417 2 => array($connId, 'Integer')
421 case 'log_civicrm_group_contact':
422 if ($oldAction !== 'Update') {
423 $newAction = $oldAction;
425 if ($oldAction == 'Insert') {
426 $newAction = 'Added';