Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
06b69b18 | 4 | | CiviCRM version 4.5 | |
6a488035 | 5 | +--------------------------------------------------------------------+ |
06b69b18 | 6 | | Copyright CiviCRM LLC (c) 2004-2014 | |
6a488035 TO |
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 | |
06b69b18 | 31 | * @copyright CiviCRM LLC (c) 2004-2014 |
6a488035 TO |
32 | * $Id$ |
33 | * | |
34 | */ | |
35 | class CRM_Logging_ReportSummary extends CRM_Report_Form { | |
36 | protected $cid; | |
37 | ||
38 | protected $_logTables = array(); | |
39 | ||
40 | protected $loggingDB; | |
41 | ||
e0ef6999 EM |
42 | /** |
43 | * | |
44 | */ | |
6a488035 | 45 | function __construct() { |
694e78fd DS |
46 | // don’t display the ‘Add these Contacts to Group’ button |
47 | $this->_add2groupSupported = FALSE; | |
48 | ||
49 | $dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN); | |
50 | $this->loggingDB = $dsn['database']; | |
51 | ||
52 | // used for redirect back to contact summary | |
53 | $this->cid = CRM_Utils_Request::retrieve('cid', 'Integer', CRM_Core_DAO::$_nullObject); | |
54 | ||
cbb9666e | 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); | |
59 | ||
6a488035 | 60 | $this->_logTables = |
cbb9666e | 61 | array( |
62 | 'log_civicrm_contact' => | |
63 | array( | |
64 | 'fk' => 'id', | |
65 | ), | |
66 | 'log_civicrm_email' => | |
67 | array( | |
68 | 'fk' => 'contact_id', | |
69 | 'log_type' => 'Contact', | |
70 | ), | |
71 | 'log_civicrm_phone' => | |
72 | array( | |
73 | 'fk' => 'contact_id', | |
74 | 'log_type' => 'Contact', | |
75 | ), | |
76 | 'log_civicrm_address' => | |
77 | array( | |
78 | 'fk' => 'contact_id', | |
79 | 'log_type' => 'Contact', | |
80 | ), | |
81 | 'log_civicrm_note' => | |
82 | array( | |
83 | 'fk' => 'entity_id', | |
84 | 'entity_table' => TRUE, | |
85 | 'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'), | |
86 | ), | |
87 | 'log_civicrm_note_comment' => | |
88 | array( | |
89 | 'fk' => 'entity_id', | |
90 | 'table_name' => 'log_civicrm_note', | |
91 | 'joins' => array( | |
92 | 'table' => 'log_civicrm_note', | |
93 | 'join' => "entity_log_civireport.entity_id = fk_table.id AND entity_log_civireport.entity_table = 'civicrm_note'" | |
94 | ), | |
95 | 'entity_table' => TRUE, | |
96 | 'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'), | |
97 | ), | |
98 | 'log_civicrm_group_contact' => | |
99 | array( | |
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', | |
104 | ), | |
105 | 'log_civicrm_entity_tag' => | |
106 | array( | |
107 | 'fk' => 'entity_id', | |
108 | 'bracket_info' => array('entity_column' => 'tag_id', 'table' => 'log_civicrm_tag', 'column' => 'name'), | |
109 | 'entity_table' => TRUE | |
110 | ), | |
111 | 'log_civicrm_relationship' => | |
112 | array( | |
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' | |
118 | ), | |
119 | ), | |
120 | 'log_civicrm_activity_for_target' => | |
121 | array( | |
122 | 'fk' => 'contact_id', | |
123 | 'table_name' => 'log_civicrm_activity', | |
124 | 'joins' => array( | |
125 | 'table' => 'log_civicrm_activity_contact', | |
126 | 'join' => "(entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$targetID})" | |
127 | ), | |
128 | 'bracket_info' => array( | |
129 | 'entity_column' => 'activity_type_id', | |
130 | 'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE) | |
131 | ), | |
132 | 'log_type' => 'Activity', | |
133 | ), | |
134 | 'log_civicrm_activity_for_assignee' => | |
135 | array( | |
136 | 'fk' => 'contact_id', | |
137 | 'table_name' => 'log_civicrm_activity', | |
138 | 'joins' => array( | |
139 | 'table' => 'log_civicrm_activity_contact', | |
140 | 'join' => "entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$assigneeID}" | |
141 | ), | |
142 | 'bracket_info' => array( | |
143 | 'entity_column' => 'activity_type_id', | |
144 | 'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE) | |
145 | ), | |
146 | 'log_type' => 'Activity', | |
147 | ), | |
148 | 'log_civicrm_activity_for_source' => | |
149 | array( | |
150 | 'fk' => 'contact_id', | |
8507f5bb | 151 | 'table_name' => 'log_civicrm_activity', |
cbb9666e | 152 | 'joins' => array( |
153 | 'table' => 'log_civicrm_activity_contact', | |
154 | 'join' => "entity_log_civireport.id = fk_table.activity_id AND fk_table.record_type_id = {$sourceID}" | |
155 | ), | |
156 | 'bracket_info' => array( | |
157 | 'entity_column' => 'activity_type_id', | |
158 | 'options' => CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE) | |
159 | ), | |
160 | 'log_type' => 'Activity', | |
161 | ), | |
162 | 'log_civicrm_case' => | |
163 | array( | |
164 | 'fk' => 'contact_id', | |
165 | 'joins' => array( | |
166 | 'table' => 'log_civicrm_case_contact', | |
167 | 'join' => 'entity_log_civireport.id = fk_table.case_id' | |
168 | ), | |
169 | 'bracket_info' => array( | |
170 | 'entity_column' => 'case_type_id', | |
42047ce4 | 171 | 'options' => CRM_Case_PseudoConstant::caseType('title', FALSE) |
cbb9666e | 172 | ), |
173 | ), | |
174 | ); | |
6a488035 | 175 | |
6a488035 | 176 | $logging = new CRM_Logging_Schema; |
694e78fd DS |
177 | |
178 | // build _logTables for contact custom tables | |
179 | $customTables = $logging->entityCustomDataLogTables('Contact'); | |
6a488035 TO |
180 | foreach ($customTables as $table) { |
181 | $this->_logTables[$table] = array('fk' => 'entity_id', 'log_type' => 'Contact'); | |
182 | } | |
183 | ||
694e78fd DS |
184 | // build _logTables for address custom tables |
185 | $customTables = $logging->entityCustomDataLogTables('Address'); | |
186 | foreach ($customTables as $table) { | |
42047ce4 | 187 | $this->_logTables[$table] = |
694e78fd DS |
188 | array( |
189 | 'fk' => 'contact_id',// for join of fk_table with contact table | |
190 | 'joins' => array( | |
191 | 'table' => 'log_civicrm_address', // fk_table | |
192 | 'join' => 'entity_log_civireport.entity_id = fk_table.id' | |
193 | ), | |
194 | 'log_type' => 'Contact' | |
195 | ); | |
196 | } | |
197 | ||
c3e3dd65 | 198 | // allow log tables to be extended via report hooks |
6b4b11c4 | 199 | CRM_Report_BAO_Hook::singleton()->alterLogTables($this, $this->_logTables); |
c3e3dd65 | 200 | |
6a488035 TO |
201 | parent::__construct(); |
202 | } | |
203 | ||
204 | function groupBy() { | |
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'; | |
206 | } | |
207 | ||
208 | function select() { | |
209 | $select = array(); | |
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); | |
219 | } | |
220 | } | |
221 | } | |
222 | } | |
223 | $this->_select = 'SELECT ' . implode(', ', $select) . ' '; | |
224 | } | |
225 | ||
226 | function where() { | |
8c544cd2 DS |
227 | // reset where clause as its called multiple times, every time insert sql is built. |
228 | $this->_whereClauses = array(); | |
42047ce4 | 229 | |
6a488035 TO |
230 | parent::where(); |
231 | $this->_where .= " AND (entity_log_civireport.log_action != 'Initialization')"; | |
232 | } | |
233 | ||
234 | function postProcess() { | |
235 | $this->beginPostProcess(); | |
236 | $rows = array(); | |
eea16664 | 237 | |
9c097caf | 238 | $tempColumns = "id int(10)"; |
a7488080 | 239 | if (!empty($this->_params['fields']['log_action'])) { |
9c097caf DS |
240 | $tempColumns .= ", log_action varchar(64)"; |
241 | } | |
242 | $tempColumns .= ", log_type varchar(64), log_user_id int(10), log_date timestamp"; | |
a7488080 | 243 | if (!empty($this->_params['fields']['altered_contact'])) { |
9c097caf DS |
244 | $tempColumns .= ", altered_contact varchar(128)"; |
245 | } | |
246 | $tempColumns .= ", altered_contact_id int(10), log_conn_id int(11), is_deleted tinyint(4)"; | |
a7488080 | 247 | if (!empty($this->_params['fields']['display_name'])) { |
9c097caf DS |
248 | $tempColumns .= ", display_name varchar(128)"; |
249 | } | |
250 | ||
6a488035 | 251 | // temp table to hold all altered contact-ids |
9c097caf | 252 | $sql = "CREATE TEMPORARY TABLE civicrm_temp_civireport_logsummary ( {$tempColumns} ) ENGINE=HEAP"; |
6a488035 TO |
253 | CRM_Core_DAO::executeQuery($sql); |
254 | ||
255 | $logDateClause = $this->dateClause('log_date', | |
cbb9666e | 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), | |
6a488035 TO |
259 | CRM_Utils_Type::T_DATE, |
260 | CRM_Utils_Array::value("log_date_from_time", $this->_params), | |
cbb9666e | 261 | CRM_Utils_Array::value("log_date_to_time", $this->_params)); |
262 | $logDateClause = $logDateClause ? "AND {$logDateClause}" : NULL; | |
6a488035 TO |
263 | |
264 | $logTypes = CRM_Utils_Array::value('log_type_value', $this->_params); | |
265 | unset($this->_params['log_type_value']); | |
cbb9666e | 266 | if (empty($logTypes)) { |
267 | foreach (array_keys($this->_logTables) as $table) { | |
6a488035 TO |
268 | $type = $this->getLogType($table); |
269 | $logTypes[$type] = $type; | |
270 | } | |
271 | } | |
272 | ||
798878cc DS |
273 | $logTypeTableClause = '(1)'; |
274 | if ($logTypeTableValue = CRM_Utils_Array::value("log_type_table_value", $this->_params)) { | |
cbb9666e | 275 | $logTypeTableClause = $this->whereClause($this->_columns['log_civicrm_entity']['filters']['log_type_table'], |
276 | $this->_params['log_type_table_op'], $logTypeTableValue, NULL, NULL); | |
798878cc DS |
277 | unset($this->_params['log_type_table_value']); |
278 | } | |
cbb9666e | 279 | |
280 | foreach ($this->_logTables as $entity => $detail) { | |
6a488035 | 281 | if ((in_array($this->getLogType($entity), $logTypes) && |
cbb9666e | 282 | CRM_Utils_Array::value('log_type_op', $this->_params) == 'in') || |
6a488035 | 283 | (!in_array($this->getLogType($entity), $logTypes) && |
cbb9666e | 284 | CRM_Utils_Array::value('log_type_op', $this->_params) == 'notin') |
285 | ) { | |
286 | $this->from($entity); | |
287 | $sql = $this->buildQuery(FALSE); | |
6a488035 TO |
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); | |
291 | } | |
292 | } | |
293 | ||
9a2a98cd DS |
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(); | |
cbb9666e | 299 | while ($dao->fetch()) { |
9a2a98cd | 300 | $type = $this->getLogType($dao->log_type); |
cbb9666e | 301 | if (!array_key_exists($type, $replaceWith)) { |
9a2a98cd | 302 | $replaceWith[$type] = array(); |
cbb9666e | 303 | } |
9a2a98cd DS |
304 | $replaceWith[$type][] = $dao->log_type; |
305 | } | |
306 | foreach ($replaceWith as $type => $tables) { | |
307 | if (!empty($tables)) { | |
308 | $replaceWith[$type] = implode("','", $tables); | |
309 | } | |
310 | } | |
2efcf0c2 | 311 | |
9a2a98cd DS |
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); | |
317 | } | |
318 | ||
319 | // note the group by columns are same as that used in alterDisplay as $newRows - $key | |
6a488035 TO |
320 | $this->limit(); |
321 | $sql = "{$this->_select} | |
322 | FROM civicrm_temp_civireport_logsummary entity_log_civireport | |
798878cc | 323 | WHERE {$logTypeTableClause} |
9a2a98cd DS |
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}"; | |
cbb9666e | 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); | |
6a488035 TO |
332 | $this->buildRows($sql, $rows); |
333 | ||
334 | // format result set. | |
335 | $this->formatDisplay($rows); | |
336 | ||
337 | // assign variables to templates | |
338 | $this->doTemplateAssignment($rows); | |
339 | ||
340 | // do print / pdf / instance stuff if needed | |
341 | $this->endPostProcess($rows); | |
342 | } | |
343 | ||
e0ef6999 EM |
344 | /** |
345 | * @param $entity | |
346 | * | |
347 | * @return string | |
348 | */ | |
cbb9666e | 349 | function getLogType($entity) { |
a7488080 | 350 | if (!empty($this->_logTables[$entity]['log_type'])) { |
6a488035 TO |
351 | return $this->_logTables[$entity]['log_type']; |
352 | } | |
353 | $logType = ucfirst(substr($entity, strrpos($entity, '_') + 1)); | |
354 | return $logType; | |
355 | } | |
356 | ||
e0ef6999 EM |
357 | /** |
358 | * @param $id | |
359 | * @param $entity | |
360 | * @param $logDate | |
361 | * | |
362 | * @return mixed|null|string | |
363 | */ | |
cbb9666e | 364 | function getEntityValue($id, $entity, $logDate) { |
a7488080 CW |
365 | if (!empty($this->_logTables[$entity]['bracket_info'])) { |
366 | if (!empty($this->_logTables[$entity]['bracket_info']['entity_column'])) { | |
0d8afee2 | 367 | $logTable = !empty($this->_logTables[$entity]['table_name']) ? $this->_logTables[$entity]['table_name'] : $entity; |
6a488035 | 368 | $sql = " |
eea16664 | 369 | SELECT {$this->_logTables[$entity]['bracket_info']['entity_column']} |
370 | FROM `{$this->loggingDB}`.{$logTable} | |
6a488035 | 371 | WHERE log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1"; |
cbb9666e | 372 | $entityID = CRM_Core_DAO::singleValueQuery($sql, array( |
373 | 1 => array( | |
374 | CRM_Utils_Date::isoToMysql($logDate), | |
375 | 'Timestamp' | |
376 | ), | |
377 | 2 => array($id, 'Integer') | |
378 | )); | |
379 | } | |
380 | else { | |
6a488035 TO |
381 | $entityID = $id; |
382 | } | |
383 | ||
384 | // since case_type_id is a varchar field with separator | |
eea16664 | 385 | if ($entity == 'log_civicrm_case') { |
cbb9666e | 386 | $entityID = explode(CRM_Case_BAO_Case::VALUE_SEPARATOR, $entityID); |
6a488035 TO |
387 | $entityID = CRM_Utils_Array::value(1, $entityID); |
388 | } | |
389 | ||
390 | if ($entityID && $logDate && array_key_exists('table', $this->_logTables[$entity]['bracket_info'])) { | |
391 | $sql = " | |
eea16664 | 392 | SELECT {$this->_logTables[$entity]['bracket_info']['column']} |
393 | FROM `{$this->loggingDB}`.{$this->_logTables[$entity]['bracket_info']['table']} | |
6a488035 | 394 | WHERE log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1"; |
cbb9666e | 395 | return CRM_Core_DAO::singleValueQuery($sql, array( |
396 | 1 => array(CRM_Utils_Date::isoToMysql($logDate), 'Timestamp'), | |
397 | 2 => array($entityID, 'Integer') | |
398 | )); | |
399 | } | |
400 | else { | |
401 | if (array_key_exists('options', $this->_logTables[$entity]['bracket_info']) && $entityID) { | |
402 | return CRM_Utils_Array::value($entityID, $this->_logTables[$entity]['bracket_info']['options']); | |
403 | } | |
6a488035 TO |
404 | } |
405 | } | |
cbb9666e | 406 | return NULL; |
6a488035 TO |
407 | } |
408 | ||
e0ef6999 EM |
409 | /** |
410 | * @param $id | |
411 | * @param $connId | |
412 | * @param $entity | |
413 | * @param $oldAction | |
414 | * | |
415 | * @return null|string | |
416 | */ | |
cbb9666e | 417 | function getEntityAction($id, $connId, $entity, $oldAction) { |
a7488080 | 418 | if (!empty($this->_logTables[$entity]['action_column'])) { |
6a488035 | 419 | $sql = "select {$this->_logTables[$entity]['action_column']} from `{$this->loggingDB}`.{$entity} where id = %1 AND log_conn_id = %2"; |
cbb9666e | 420 | $newAction = CRM_Core_DAO::singleValueQuery($sql, array( |
421 | 1 => array($id, 'Integer'), | |
422 | 2 => array($connId, 'Integer') | |
423 | )); | |
6a488035 TO |
424 | |
425 | switch ($entity) { | |
cbb9666e | 426 | case 'log_civicrm_group_contact': |
427 | if ($oldAction !== 'Update') { | |
428 | $newAction = $oldAction; | |
429 | } | |
430 | if ($oldAction == 'Insert') { | |
431 | $newAction = 'Added'; | |
432 | } | |
433 | break; | |
6a488035 TO |
434 | } |
435 | return $newAction; | |
436 | } | |
cbb9666e | 437 | return NULL; |
6a488035 TO |
438 | } |
439 | } |