Commit | Line | Data |
---|---|---|
6a488035 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
4 | | CiviCRM version 4.3 | | |
5 | +--------------------------------------------------------------------+ | |
6 | | Copyright CiviCRM LLC (c) 2004-2013 | | |
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-2013 | |
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 | ||
42 | function __construct() { | |
43 | $this->_logTables = | |
44 | array( | |
45 | 'log_civicrm_contact' => | |
46 | array( 'fk' => 'id', | |
47 | ), | |
48 | 'log_civicrm_email' => | |
49 | array( 'fk' => 'contact_id', | |
50 | 'log_type' => 'Contact', | |
51 | ), | |
52 | 'log_civicrm_phone' => | |
53 | array( 'fk' => 'contact_id', | |
54 | 'log_type' => 'Contact', | |
55 | ), | |
56 | 'log_civicrm_address' => | |
57 | array( 'fk' => 'contact_id', | |
58 | 'log_type' => 'Contact', | |
59 | ), | |
60 | 'log_civicrm_note' => | |
61 | array( 'fk' => 'entity_id', | |
62 | 'entity_table' => true, | |
63 | 'bracket_info' => array('table' => 'log_civicrm_note', 'column' => 'subject'), | |
64 | ), | |
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'), | |
72 | ), | |
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', | |
78 | ), | |
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 | |
83 | ), | |
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'), | |
87 | ), | |
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', | |
94 | ), | |
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', | |
101 | ), | |
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', | |
107 | ), | |
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)), | |
112 | ), | |
113 | ); | |
114 | ||
115 | // don’t display the ‘Add these Contacts to Group’ button | |
116 | $this->_add2groupSupported = FALSE; | |
117 | ||
118 | $dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN); | |
119 | $this->loggingDB = $dsn['database']; | |
120 | ||
121 | // used for redirect back to contact summary | |
122 | $this->cid = CRM_Utils_Request::retrieve('cid', 'Integer', CRM_Core_DAO::$_nullObject); | |
123 | ||
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'); | |
128 | } | |
129 | ||
130 | parent::__construct(); | |
131 | } | |
132 | ||
133 | function groupBy() { | |
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'; | |
135 | } | |
136 | ||
137 | function select() { | |
138 | $select = array(); | |
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); | |
148 | } | |
149 | } | |
150 | } | |
151 | } | |
152 | $this->_select = 'SELECT ' . implode(', ', $select) . ' '; | |
153 | } | |
154 | ||
155 | function where() { | |
156 | parent::where(); | |
157 | $this->_where .= " AND (entity_log_civireport.log_action != 'Initialization')"; | |
158 | } | |
159 | ||
160 | function postProcess() { | |
161 | $this->beginPostProcess(); | |
162 | $rows = array(); | |
163 | // temp table to hold all altered contact-ids | |
164 | $sql = " | |
165 | CREATE TEMPORARY TABLE | |
166 | civicrm_temp_civireport_logsummary ( id int(10), | |
167 | log_action varchar(64), | |
168 | log_type varchar(64), | |
169 | log_user_id int(10), | |
170 | log_date timestamp, | |
171 | altered_contact varchar(128), | |
172 | altered_contact_id int(10), | |
173 | log_conn_id int(11), | |
174 | is_deleted tinyint(4), | |
175 | display_name varchar(128) ) ENGINE=HEAP"; | |
176 | CRM_Core_DAO::executeQuery($sql); | |
177 | ||
178 | $logDateClause = $this->dateClause('log_date', | |
179 | CRM_Utils_Array::value("log_date_relative", $this->_params), | |
180 | CRM_Utils_Array::value("log_date_from", $this->_params), | |
181 | CRM_Utils_Array::value("log_date_to", $this->_params), | |
182 | CRM_Utils_Type::T_DATE, | |
183 | CRM_Utils_Array::value("log_date_from_time", $this->_params), | |
184 | CRM_Utils_Array::value("log_date_to_time", $this->_params)); | |
185 | $logDateClause = $logDateClause ? "AND {$logDateClause}" : null; | |
186 | ||
187 | $logTypes = CRM_Utils_Array::value('log_type_value', $this->_params); | |
188 | unset($this->_params['log_type_value']); | |
189 | if ( empty($logTypes) ) { | |
190 | foreach ( array_keys($this->_logTables) as $table ) { | |
191 | $type = $this->getLogType($table); | |
192 | $logTypes[$type] = $type; | |
193 | } | |
194 | } | |
195 | ||
196 | foreach ( $this->_logTables as $entity => $detail ) { | |
197 | if ((in_array($this->getLogType($entity), $logTypes) && | |
198 | CRM_Utils_Array::value('log_type_op', $this->_params) == 'in') || | |
199 | (!in_array($this->getLogType($entity), $logTypes) && | |
200 | CRM_Utils_Array::value('log_type_op', $this->_params) == 'notin')) { | |
201 | $this->from( $entity ); | |
202 | $sql = $this->buildQuery(false); | |
203 | $sql = str_replace("entity_log_civireport.log_type as", "'{$entity}' as", $sql); | |
204 | $sql = "INSERT IGNORE INTO civicrm_temp_civireport_logsummary {$sql}"; | |
205 | CRM_Core_DAO::executeQuery($sql); | |
206 | } | |
207 | } | |
208 | ||
209 | $this->limit(); | |
210 | $sql = "{$this->_select} | |
211 | FROM civicrm_temp_civireport_logsummary entity_log_civireport | |
212 | ORDER BY entity_log_civireport.log_date DESC {$this->_limit}"; | |
213 | $sql = str_replace('modified_contact_civireport.display_name', 'entity_log_civireport.altered_contact', $sql); | |
214 | $sql = str_replace('modified_contact_civireport.id', 'entity_log_civireport.altered_contact_id', $sql); | |
215 | $sql = str_replace(array('modified_contact_civireport.', 'altered_by_contact_civireport.'), 'entity_log_civireport.', $sql); | |
216 | $this->buildRows($sql, $rows); | |
217 | ||
218 | // format result set. | |
219 | $this->formatDisplay($rows); | |
220 | ||
221 | // assign variables to templates | |
222 | $this->doTemplateAssignment($rows); | |
223 | ||
224 | // do print / pdf / instance stuff if needed | |
225 | $this->endPostProcess($rows); | |
226 | } | |
227 | ||
228 | function getLogType( $entity ) { | |
229 | if (CRM_Utils_Array::value('log_type', $this->_logTables[$entity])) { | |
230 | return $this->_logTables[$entity]['log_type']; | |
231 | } | |
232 | $logType = ucfirst(substr($entity, strrpos($entity, '_') + 1)); | |
233 | return $logType; | |
234 | } | |
235 | ||
236 | function getEntityValue( $id, $entity, $logDate ) { | |
237 | if (CRM_Utils_Array::value('bracket_info', $this->_logTables[$entity])) { | |
238 | if (CRM_Utils_Array::value('entity_column', $this->_logTables[$entity]['bracket_info'])) { | |
239 | $logTable = CRM_Utils_Array::value('table_name', $this->_logTables[$entity]) ? $this->_logTables[$entity]['table_name'] : $entity; | |
240 | $sql = " | |
241 | SELECT {$this->_logTables[$entity]['bracket_info']['entity_column']} | |
242 | FROM `{$this->loggingDB}`.{$logTable} | |
243 | WHERE log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1"; | |
244 | $entityID = CRM_Core_DAO::singleValueQuery($sql, array(1 => array(CRM_Utils_Date::isoToMysql($logDate), 'Timestamp'), 2 => array ($id, 'Integer'))); | |
245 | } else { | |
246 | $entityID = $id; | |
247 | } | |
248 | ||
249 | // since case_type_id is a varchar field with separator | |
250 | if ($entity == 'log_civicrm_case') { | |
251 | $entityID = explode(CRM_Case_BAO_Case::VALUE_SEPARATOR,$entityID); | |
252 | $entityID = CRM_Utils_Array::value(1, $entityID); | |
253 | } | |
254 | ||
255 | if ($entityID && $logDate && array_key_exists('table', $this->_logTables[$entity]['bracket_info'])) { | |
256 | $sql = " | |
257 | SELECT {$this->_logTables[$entity]['bracket_info']['column']} | |
258 | FROM `{$this->loggingDB}`.{$this->_logTables[$entity]['bracket_info']['table']} | |
259 | WHERE log_date <= %1 AND id = %2 ORDER BY log_date DESC LIMIT 1"; | |
260 | return CRM_Core_DAO::singleValueQuery($sql, array(1 => array(CRM_Utils_Date::isoToMysql($logDate), 'Timestamp'), 2 => array ($entityID, 'Integer'))); | |
261 | } else if (array_key_exists('options', $this->_logTables[$entity]['bracket_info']) && $entityID) { | |
262 | return CRM_Utils_Array::value($entityID, $this->_logTables[$entity]['bracket_info']['options']); | |
263 | } | |
264 | } | |
265 | return null; | |
266 | } | |
267 | ||
268 | function getEntityAction( $id, $connId, $entity, $oldAction ) { | |
269 | if (CRM_Utils_Array::value('action_column', $this->_logTables[$entity])) { | |
270 | $sql = "select {$this->_logTables[$entity]['action_column']} from `{$this->loggingDB}`.{$entity} where id = %1 AND log_conn_id = %2"; | |
271 | $newAction = CRM_Core_DAO::singleValueQuery($sql, array(1 => array($id, 'Integer'), 2 => array($connId, 'Integer'))); | |
272 | ||
273 | switch ($entity) { | |
274 | case 'log_civicrm_group_contact': | |
275 | if ($oldAction !== 'Update') | |
276 | $newAction = $oldAction; | |
277 | if ($oldAction == 'Insert') | |
278 | $newAction = 'Added'; | |
279 | break; | |
280 | } | |
281 | return $newAction; | |
282 | } | |
283 | return null; | |
284 | } | |
285 | } |