3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.3 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2013 |
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-2013
35 class CRM_Logging_ReportSummary
extends CRM_Report_Form
{
38 protected $_logTables = array();
42 function __construct() {
45 'log_civicrm_contact' =>
48 'log_civicrm_email' =>
49 array( 'fk' => 'contact_id',
50 'log_type' => 'Contact',
52 'log_civicrm_phone' =>
53 array( 'fk' => 'contact_id',
54 'log_type' => 'Contact',
56 'log_civicrm_address' =>
57 array( 'fk' => 'contact_id',
58 'log_type' => 'Contact',
61 array( 'fk' => 'entity_id',
62 'entity_table' => true,
63 'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'),
65 'log_civicrm_note_comment' =>
66 array( 'fk' => 'entity_id',
67 'table_name' => 'log_civicrm_note',
68 'joins' => array('table' => 'log_civicrm_note',
69 'join' => "entity_log_civireport.entity_id = fk_table.id AND entity_log_civireport.entity_table = 'civicrm_note'"),
70 'entity_table' => true,
71 'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'),
73 'log_civicrm_group_contact' =>
74 array( 'fk' => 'contact_id',
75 'bracket_info' => array('entity_column' => 'group_id', 'table' => 'log_civicrm_group', 'column' => 'title'),
76 'action_column' => 'status',
77 'log_type' => 'Group',
79 'log_civicrm_entity_tag' =>
80 array( 'fk' => 'entity_id',
81 'bracket_info' => array('entity_column' => 'tag_id', 'table' => 'log_civicrm_tag', 'column' => 'name'),
82 'entity_table' => true
84 'log_civicrm_relationship' =>
85 array( 'fk' => 'contact_id_a',
86 'bracket_info' => array('entity_column' => 'relationship_type_id', 'table' => 'log_civicrm_relationship_type', 'column' => 'label_a_b'),
88 'log_civicrm_activity_for_target' =>
89 array( 'fk' => 'target_contact_id',
90 'table_name' => 'log_civicrm_activity',
91 'joins' => array('table' => 'log_civicrm_activity_target', 'join' => 'entity_log_civireport.id = fk_table.activity_id'),
92 'bracket_info' => array('entity_column' => 'activity_type_id', 'options' => CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE)),
93 'log_type' => 'Activity',
95 'log_civicrm_activity_for_assignee' =>
96 array( 'fk' => 'assignee_contact_id',
97 'table_name' => 'log_civicrm_activity',
98 'joins' => array('table' => 'log_civicrm_activity_assignment', 'join' => 'entity_log_civireport.id = fk_table.activity_id'),
99 'bracket_info' => array('entity_column' => 'activity_type_id', 'options' => CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE)),
100 'log_type' => 'Activity',
102 'log_civicrm_activity_for_source' =>
103 array( 'fk' => 'source_contact_id',
104 'table_name' => 'log_civicrm_activity',
105 'bracket_info' => array('entity_column' => 'activity_type_id', 'options' => CRM_Core_PseudoConstant
::activityType(TRUE, TRUE, FALSE, 'label', TRUE)),
106 'log_type' => 'Activity',
108 'log_civicrm_case' =>
109 array( 'fk' => 'contact_id',
110 'joins' => array('table' => 'log_civicrm_case_contact', 'join' => 'entity_log_civireport.id = fk_table.case_id'),
111 'bracket_info' => array('entity_column' => 'case_type_id', 'options' => CRM_Case_PseudoConstant
::caseType('label', FALSE)),
115 // don’t display the ‘Add these Contacts to Group’ button
116 $this->_add2groupSupported
= FALSE;
118 $dsn = defined('CIVICRM_LOGGING_DSN') ? DB
::parseDSN(CIVICRM_LOGGING_DSN
) : DB
::parseDSN(CIVICRM_DSN
);
119 $this->loggingDB
= $dsn['database'];
121 // used for redirect back to contact summary
122 $this->cid
= CRM_Utils_Request
::retrieve('cid', 'Integer', CRM_Core_DAO
::$_nullObject);
124 $logging = new CRM_Logging_Schema
;
125 $customTables = $logging->customDataLogTables();
126 foreach ($customTables as $table) {
127 $this->_logTables
[$table] = array('fk' => 'entity_id', 'log_type' => 'Contact');
130 parent
::__construct();
134 $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';
139 $this->_columnHeaders
= array();
140 foreach ($this->_columns
as $tableName => $table) {
141 if (array_key_exists('fields', $table)) {
142 foreach ($table['fields'] as $fieldName => $field) {
143 if (CRM_Utils_Array
::value('required', $field) or CRM_Utils_Array
::value($fieldName, $this->_params
['fields'])) {
144 $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
145 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
146 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array
::value('no_display', $field);
147 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array
::value('title', $field);
152 $this->_select
= 'SELECT ' . implode(', ', $select) . ' ';
157 $this->_where
.= " AND (entity_log_civireport.log_action != 'Initialization')";
160 function postProcess() {
161 $this->beginPostProcess();
164 $tempColumns = "id int(10)";
165 if (CRM_Utils_Array
::value('log_action', $this->_params
['fields'])) {
166 $tempColumns .= ", log_action varchar(64)";
168 $tempColumns .= ", log_type varchar(64), log_user_id int(10), log_date timestamp";
169 if (CRM_Utils_Array
::value('altered_contact', $this->_params
['fields'])) {
170 $tempColumns .= ", altered_contact varchar(128)";
172 $tempColumns .= ", altered_contact_id int(10), log_conn_id int(11), is_deleted tinyint(4)";
173 if (CRM_Utils_Array
::value('display_name', $this->_params
['fields'])) {
174 $tempColumns .= ", display_name varchar(128)";
177 // temp table to hold all altered contact-ids
178 $sql = "CREATE TEMPORARY TABLE civicrm_temp_civireport_logsummary ( {$tempColumns} ) ENGINE=HEAP";
179 CRM_Core_DAO
::executeQuery($sql);
181 $logDateClause = $this->dateClause('log_date',
182 CRM_Utils_Array
::value("log_date_relative", $this->_params
),
183 CRM_Utils_Array
::value("log_date_from", $this->_params
),
184 CRM_Utils_Array
::value("log_date_to", $this->_params
),
185 CRM_Utils_Type
::T_DATE
,
186 CRM_Utils_Array
::value("log_date_from_time", $this->_params
),
187 CRM_Utils_Array
::value("log_date_to_time", $this->_params
));
188 $logDateClause = $logDateClause ?
"AND {$logDateClause}" : null;
190 $logTypes = CRM_Utils_Array
::value('log_type_value', $this->_params
);
191 unset($this->_params
['log_type_value']);
192 if ( empty($logTypes) ) {
193 foreach ( array_keys($this->_logTables
) as $table ) {
194 $type = $this->getLogType($table);
195 $logTypes[$type] = $type;
199 foreach ( $this->_logTables
as $entity => $detail ) {
200 if ((in_array($this->getLogType($entity), $logTypes) &&
201 CRM_Utils_Array
::value('log_type_op', $this->_params
) == 'in') ||
202 (!in_array($this->getLogType($entity), $logTypes) &&
203 CRM_Utils_Array
::value('log_type_op', $this->_params
) == 'notin')) {
204 $this->from( $entity );
205 $sql = $this->buildQuery(false);
206 $sql = str_replace("entity_log_civireport.log_type as", "'{$entity}' as", $sql);
207 $sql = "INSERT IGNORE INTO civicrm_temp_civireport_logsummary {$sql}";
208 CRM_Core_DAO
::executeQuery($sql);
213 $sql = "{$this->_select}
214 FROM civicrm_temp_civireport_logsummary entity_log_civireport
215 ORDER BY entity_log_civireport.log_date DESC {$this->_limit}";
216 $sql = str_replace('modified_contact_civireport.display_name', 'entity_log_civireport.altered_contact', $sql);
217 $sql = str_replace('modified_contact_civireport.id', 'entity_log_civireport.altered_contact_id', $sql);
218 $sql = str_replace(array('modified_contact_civireport.', 'altered_by_contact_civireport.'), 'entity_log_civireport.', $sql);
219 $this->buildRows($sql, $rows);
221 // format result set.
222 $this->formatDisplay($rows);
224 // assign variables to templates
225 $this->doTemplateAssignment($rows);
227 // do print / pdf / instance stuff if needed
228 $this->endPostProcess($rows);
231 function getLogType( $entity ) {
232 if (CRM_Utils_Array
::value('log_type', $this->_logTables
[$entity])) {
233 return $this->_logTables
[$entity]['log_type'];
235 $logType = ucfirst(substr($entity, strrpos($entity, '_') +
1));
239 function getEntityValue( $id, $entity, $logDate ) {
240 if (CRM_Utils_Array
::value('bracket_info', $this->_logTables
[$entity])) {
241 if (CRM_Utils_Array
::value('entity_column', $this->_logTables
[$entity]['bracket_info'])) {
242 $logTable = CRM_Utils_Array
::value('table_name', $this->_logTables
[$entity]) ?
$this->_logTables
[$entity]['table_name'] : $entity;
244 SELECT {$this->_logTables[$entity]['bracket_info']['entity_column']}
245 FROM `{$this->loggingDB}`.{$logTable}
246 WHERE log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
247 $entityID = CRM_Core_DAO
::singleValueQuery($sql, array(1 => array(CRM_Utils_Date
::isoToMysql($logDate), 'Timestamp'), 2 => array ($id, 'Integer')));
252 // since case_type_id is a varchar field with separator
253 if ($entity == 'log_civicrm_case') {
254 $entityID = explode(CRM_Case_BAO_Case
::VALUE_SEPARATOR
,$entityID);
255 $entityID = CRM_Utils_Array
::value(1, $entityID);
258 if ($entityID && $logDate && array_key_exists('table', $this->_logTables
[$entity]['bracket_info'])) {
260 SELECT {$this->_logTables[$entity]['bracket_info']['column']}
261 FROM `{$this->loggingDB}`.{$this->_logTables[$entity]['bracket_info']['table']}
262 WHERE log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1";
263 return CRM_Core_DAO
::singleValueQuery($sql, array(1 => array(CRM_Utils_Date
::isoToMysql($logDate), 'Timestamp'), 2 => array ($entityID, 'Integer')));
264 } else if (array_key_exists('options', $this->_logTables
[$entity]['bracket_info']) && $entityID) {
265 return CRM_Utils_Array
::value($entityID, $this->_logTables
[$entity]['bracket_info']['options']);
271 function getEntityAction( $id, $connId, $entity, $oldAction ) {
272 if (CRM_Utils_Array
::value('action_column', $this->_logTables
[$entity])) {
273 $sql = "select {$this->_logTables[$entity]['action_column']} from `{$this->loggingDB}`.{$entity} where id = %1 AND log_conn_id = %2";
274 $newAction = CRM_Core_DAO
::singleValueQuery($sql, array(1 => array($id, 'Integer'), 2 => array($connId, 'Integer')));
277 case 'log_civicrm_group_contact':
278 if ($oldAction !== 'Update')
279 $newAction = $oldAction;
280 if ($oldAction == 'Insert')
281 $newAction = 'Added';