/**
* Get a log change.
*
* @param array $params
*
* @return array
* API Success Array
* @throws \API_Exception
* @throws \Civi\API\Exception\UnauthorizedException
*/
function civicrm_api3_logging_get($params)
{
$schema = new CRM_Logging_Schema();
$interval = empty($params['log_date']) ? NULL : $params['interval'];
$differ = new CRM_Logging_Differ($params['log_conn_id'], CRM_Utils_Array::value('log_date', $params), $interval);
return civicrm_api3_create_success($differ->getAllChangesForConnection($schema->getLogTablesForContact()));
}
/**
* Get an array of changes made in the mysql connection.
*
* @return mixed
*/
public function getAllContactChangesForConnection()
{
if (empty($this->log_conn_id)) {
return array();
}
$this->setDiffer();
try {
return $this->differ->getAllChangesForConnection($this->tables);
} catch (CRM_Core_Exception $e) {
CRM_Core_Error::statusBounce(ts($e->getMessage()));
}
}
/**
* @param $table
*
* @return array
*/
protected function diffsInTable($table)
{
$rows = array();
$differ = new CRM_Logging_Differ($this->log_conn_id, $this->log_date, $this->interval);
$diffs = $differ->diffsInTable($table, $this->cid);
// return early if nothing found
if (empty($diffs)) {
return $rows;
}
list($titles, $values) = $differ->titlesAndValuesForTable($table);
// populate $rows with only the differences between $changed and $original (skipping certain columns and NULL ↔ empty changes unless raw requested)
$skipped = array('contact_id', 'entity_id', 'id');
foreach ($diffs as $diff) {
$field = $diff['field'];
$from = $diff['from'];
$to = $diff['to'];
if ($this->raw) {
$field = "{$table}.{$field}";
} else {
if (in_array($field, $skipped)) {
continue;
}
// $differ filters out === values; for presentation hide changes like 42 → '42'
if ($from == $to) {
continue;
}
// special-case for multiple values. Also works for CRM-7251: preferred_communication_method
if (substr($from, 0, 1) == CRM_Core_DAO::VALUE_SEPARATOR && substr($from, -1, 1) == CRM_Core_DAO::VALUE_SEPARATOR || substr($to, 0, 1) == CRM_Core_DAO::VALUE_SEPARATOR && substr($to, -1, 1) == CRM_Core_DAO::VALUE_SEPARATOR) {
$froms = $tos = array();
foreach (explode(CRM_Core_DAO::VALUE_SEPARATOR, trim($from, CRM_Core_DAO::VALUE_SEPARATOR)) as $val) {
$froms[] = CRM_Utils_Array::value($val, $values[$field]);
}
foreach (explode(CRM_Core_DAO::VALUE_SEPARATOR, trim($to, CRM_Core_DAO::VALUE_SEPARATOR)) as $val) {
$tos[] = CRM_Utils_Array::value($val, $values[$field]);
}
$from = implode(', ', array_filter($froms));
$to = implode(', ', array_filter($tos));
}
if (isset($values[$field][$from])) {
$from = $values[$field][$from];
}
if (isset($values[$field][$to])) {
$to = $values[$field][$to];
}
if (isset($titles[$field])) {
$field = $titles[$field];
}
if ($diff['action'] == 'Insert') {
$from = '';
}
if ($diff['action'] == 'Delete') {
$to = '';
}
}
$rows[] = array('field' => $field . " (id: {$diff['id']})", 'from' => $from, 'to' => $to);
}
return $rows;
}
function revert($tables)
{
// FIXME: split off the table → DAO mapping to a GenCode-generated class
$daos = array('civicrm_address' => 'CRM_Core_DAO_Address', 'civicrm_contact' => 'CRM_Contact_DAO_Contact', 'civicrm_email' => 'CRM_Core_DAO_Email', 'civicrm_im' => 'CRM_Core_DAO_IM', 'civicrm_openid' => 'CRM_Core_DAO_OpenID', 'civicrm_phone' => 'CRM_Core_DAO_Phone', 'civicrm_website' => 'CRM_Core_DAO_Website', 'civicrm_contribution' => 'CRM_Contribute_DAO_Contribution', 'civicrm_note' => 'CRM_Core_DAO_Note', 'civicrm_relationship' => 'CRM_Contact_DAO_Relationship');
// get custom data tables, columns and types
$ctypes = array();
$dao = CRM_Core_DAO::executeQuery('SELECT table_name, column_name, data_type FROM civicrm_custom_group cg JOIN civicrm_custom_field cf ON (cf.custom_group_id = cg.id)');
while ($dao->fetch()) {
if (!isset($ctypes[$dao->table_name])) {
$ctypes[$dao->table_name] = array('entity_id' => 'Integer');
}
$ctypes[$dao->table_name][$dao->column_name] = $dao->data_type;
}
$differ = new CRM_Logging_Differ($this->log_conn_id, $this->log_date);
$diffs = $differ->diffsInTables($tables);
$deletes = array();
$reverts = array();
foreach ($diffs as $table => $changes) {
foreach ($changes as $change) {
switch ($change['action']) {
case 'Insert':
if (!isset($deletes[$table])) {
$deletes[$table] = array();
}
$deletes[$table][] = $change['id'];
break;
case 'Delete':
case 'Update':
if (!isset($reverts[$table])) {
$reverts[$table] = array();
}
if (!isset($reverts[$table][$change['id']])) {
$reverts[$table][$change['id']] = array('log_action' => $change['action']);
}
$reverts[$table][$change['id']][$change['field']] = $change['from'];
break;
}
}
}
// revert inserts by deleting
foreach ($deletes as $table => $ids) {
CRM_Core_DAO::executeQuery("DELETE FROM `{$table}` WHERE id IN (" . implode(', ', array_unique($ids)) . ')');
}
// revert updates by updating to previous values
foreach ($reverts as $table => $row) {
switch (TRUE) {
// DAO-based tables
case in_array($table, array_keys($daos)):
require_once str_replace('_', DIRECTORY_SEPARATOR, $daos[$table]) . '.php';
eval("\$dao = new {$daos[$table]};");
foreach ($row as $id => $changes) {
$dao->id = $id;
foreach ($changes as $field => $value) {
if ($field == 'log_action') {
continue;
}
if (empty($value) and $value !== 0 and $value !== '0') {
$value = 'null';
}
$dao->{$field} = $value;
}
$changes['log_action'] == 'Delete' ? $dao->insert() : $dao->update();
$dao->reset();
}
break;
// custom data tables
// custom data tables
case in_array($table, array_keys($ctypes)):
foreach ($row as $id => $changes) {
$inserts = array('id' => '%1');
$updates = array();
$params = array(1 => array($id, 'Integer'));
$counter = 2;
foreach ($changes as $field => $value) {
// don’t try reverting a field that’s no longer there
if (!isset($ctypes[$table][$field])) {
continue;
}
switch ($ctypes[$table][$field]) {
case 'Date':
$value = substr(CRM_Utils_Date::isoToMysql($value), 0, 8);
break;
case 'Timestamp':
$value = CRM_Utils_Date::isoToMysql($value);
break;
}
$inserts[$field] = "%{$counter}";
$updates[] = "{$field} = %{$counter}";
$params[$counter] = array($value, $ctypes[$table][$field]);
$counter++;
}
if ($changes['log_action'] == 'Delete') {
$sql = "INSERT INTO `{$table}` (" . implode(', ', array_keys($inserts)) . ') VALUES (' . implode(', ', $inserts) . ')';
} else {
$sql = "UPDATE `{$table}` SET " . implode(', ', $updates) . ' WHERE id = %1';
}
CRM_Core_DAO::executeQuery($sql, $params);
}
break;
}
}
// CRM-7353: if nothing altered civicrm_contact, touch it; this will
// make sure there’s an entry in log_civicrm_contact for this revert
if (empty($diffs['civicrm_contact'])) {
$query = "\n SELECT id FROM `{$this->db}`.log_civicrm_contact\n WHERE log_conn_id = %1 AND log_date BETWEEN DATE_SUB(%2, INTERVAL 10 SECOND) AND DATE_ADD(%2, INTERVAL 10 SECOND)\n ORDER BY log_date DESC LIMIT 1\n ";
$params = array(1 => array($this->log_conn_id, 'Integer'), 2 => array($this->log_date, 'String'));
$cid = CRM_Core_DAO::singleValueQuery($query, $params);
if (!$cid) {
return;
}
$dao = new CRM_Contact_DAO_Contact();
$dao->id = $cid;
if ($dao->find(TRUE)) {
// CRM-8102: MySQL can’t parse its own dates
$dao->birth_date = CRM_Utils_Date::isoToMysql($dao->birth_date);
$dao->deceased_date = CRM_Utils_Date::isoToMysql($dao->deceased_date);
$dao->save();
}
}
}
/**
*
* Calculate a set of diffs based on the connection_id and changes at a close time.
*
* @param array $tables
*/
public function calculateDiffsFromLogConnAndDate($tables)
{
$differ = new CRM_Logging_Differ($this->log_conn_id, $this->log_date);
$this->diffs = $differ->diffsInTables($tables);
}
/**
* Revert changes in the array of diffs in $this->diffs.
*
* @param $tables
*/
public function revert($tables)
{
// get custom data tables, columns and types
$ctypes = array();
$dao = CRM_Core_DAO::executeQuery('SELECT table_name, column_name, data_type FROM civicrm_custom_group cg JOIN civicrm_custom_field cf ON (cf.custom_group_id = cg.id)');
while ($dao->fetch()) {
if (!isset($ctypes[$dao->table_name])) {
$ctypes[$dao->table_name] = array('entity_id' => 'Integer');
}
$ctypes[$dao->table_name][$dao->column_name] = $dao->data_type;
}
$differ = new CRM_Logging_Differ($this->log_conn_id, $this->log_date);
$diffs = $differ->diffsInTables($tables);
$deletes = array();
$reverts = array();
foreach ($diffs as $table => $changes) {
foreach ($changes as $change) {
switch ($change['action']) {
case 'Insert':
if (!isset($deletes[$table])) {
$deletes[$table] = array();
}
$deletes[$table][] = $change['id'];
break;
case 'Delete':
case 'Update':
if (!isset($reverts[$table])) {
$reverts[$table] = array();
}
if (!isset($reverts[$table][$change['id']])) {
$reverts[$table][$change['id']] = array('log_action' => $change['action']);
}
$reverts[$table][$change['id']][$change['field']] = $change['from'];
break;
}
}
}
// revert inserts by deleting
foreach ($deletes as $table => $ids) {
CRM_Core_DAO::executeQuery("DELETE FROM `{$table}` WHERE id IN (" . implode(', ', array_unique($ids)) . ')');
}
// revert updates by updating to previous values
foreach ($reverts as $table => $row) {
switch (TRUE) {
// DAO-based tables
case ($tableDAO = CRM_Core_DAO_AllCoreTables::getClassForTable($table)) != FALSE:
$dao = new $tableDAO();
foreach ($row as $id => $changes) {
$dao->id = $id;
foreach ($changes as $field => $value) {
if ($field == 'log_action') {
continue;
}
if (empty($value) and $value !== 0 and $value !== '0') {
$value = 'null';
}
$dao->{$field} = $value;
}
$changes['log_action'] == 'Delete' ? $dao->insert() : $dao->update();
$dao->reset();
}
break;
// custom data tables
// custom data tables
case in_array($table, array_keys($ctypes)):
foreach ($row as $id => $changes) {
$inserts = array('id' => '%1');
$updates = array();
$params = array(1 => array($id, 'Integer'));
$counter = 2;
foreach ($changes as $field => $value) {
// don’t try reverting a field that’s no longer there
if (!isset($ctypes[$table][$field])) {
continue;
}
$fldVal = "%{$counter}";
switch ($ctypes[$table][$field]) {
case 'Date':
$value = substr(CRM_Utils_Date::isoToMysql($value), 0, 8);
break;
case 'Timestamp':
$value = CRM_Utils_Date::isoToMysql($value);
break;
case 'Boolean':
if ($value === '') {
$fldVal = 'DEFAULT';
}
}
$inserts[$field] = "%{$counter}";
$updates[] = "{$field} = {$fldVal}";
if ($fldVal != 'DEFAULT') {
$params[$counter] = array($value, $ctypes[$table][$field]);
}
$counter++;
}
if ($changes['log_action'] == 'Delete') {
$sql = "INSERT INTO `{$table}` (" . implode(', ', array_keys($inserts)) . ') VALUES (' . implode(', ', $inserts) . ')';
} else {
$sql = "UPDATE `{$table}` SET " . implode(', ', $updates) . ' WHERE id = %1';
}
CRM_Core_DAO::executeQuery($sql, $params);
}
break;
}
}
// CRM-7353: if nothing altered civicrm_contact, touch it; this will
// make sure there’s an entry in log_civicrm_contact for this revert
if (empty($diffs['civicrm_contact'])) {
$query = "\n SELECT id FROM `{$this->db}`.log_civicrm_contact\n WHERE log_conn_id = %1 AND log_date BETWEEN DATE_SUB(%2, INTERVAL 10 SECOND) AND DATE_ADD(%2, INTERVAL 10 SECOND)\n ORDER BY log_date DESC LIMIT 1\n ";
$params = array(1 => array($this->log_conn_id, 'Integer'), 2 => array($this->log_date, 'String'));
$cid = CRM_Core_DAO::singleValueQuery($query, $params);
if (!$cid) {
return;
}
$dao = new CRM_Contact_DAO_Contact();
$dao->id = $cid;
if ($dao->find(TRUE)) {
// CRM-8102: MySQL can’t parse its own dates
$dao->birth_date = CRM_Utils_Date::isoToMysql($dao->birth_date);
$dao->deceased_date = CRM_Utils_Date::isoToMysql($dao->deceased_date);
$dao->save();
}
}
}
/**
* Alter display of rows.
*
* Iterate through the rows retrieved via SQL and make changes for display purposes,
* such as rendering contacts as links.
*
* @param array $rows
* Rows generated by SQL, with an array for each row.
*/
public function alterDisplay(&$rows)
{
// cache for id → is_deleted mapping
$isDeleted = array();
$newRows = array();
foreach ($rows as $key => &$row) {
$isMerge = 0;
$baseQueryCriteria = "reset=1&log_conn_id={$row['log_civicrm_entity_log_conn_id']}";
if (!CRM_Logging_Differ::checkLogCanBeUsedWithNoLogDate($row['log_civicrm_entity_log_date'])) {
$baseQueryCriteria .= '&log_date=' . CRM_Utils_Date::isoToMysql($row['log_civicrm_entity_log_date']);
}
if ($this->cid) {
$baseQueryCriteria .= '&cid=' . $this->cid;
}
if (!isset($isDeleted[$row['log_civicrm_entity_altered_contact_id']])) {
$isDeleted[$row['log_civicrm_entity_altered_contact_id']] = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $row['log_civicrm_entity_altered_contact_id'], 'is_deleted') !== '0';
}
if (!empty($row['log_civicrm_entity_altered_contact']) && !$isDeleted[$row['log_civicrm_entity_altered_contact_id']]) {
$row['log_civicrm_entity_altered_contact_link'] = CRM_Utils_System::url('civicrm/contact/view', 'reset=1&cid=' . $row['log_civicrm_entity_altered_contact_id']);
$row['log_civicrm_entity_altered_contact_hover'] = ts("Go to contact summary");
$entity = $this->getEntityValue($row['log_civicrm_entity_id'], $row['log_civicrm_entity_log_type'], $row['log_civicrm_entity_log_date']);
if ($entity) {
$row['log_civicrm_entity_altered_contact'] = $row['log_civicrm_entity_altered_contact'] . " [{$entity}]";
}
if ($entity == 'Contact Merged') {
$deletedID = CRM_Core_DAO::singleValueQuery('
SELECT GROUP_CONCAT(contact_id) FROM civicrm_activity_contact ac
INNER JOIN civicrm_activity a
ON a.id = ac.activity_id AND a.parent_id = ' . $row['log_civicrm_entity_id'] . ' AND ac.record_type_id =
' . CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_ActivityContact', 'record_type_id', 'Activity Targets'));
if ($deletedID && !stristr($deletedID, ',')) {
$baseQueryCriteria .= '&oid=' . $deletedID;
}
$row['log_civicrm_entity_log_action'] = ts('Contact Merge');
$row = $this->addDetailReportLinksToRow($baseQueryCriteria, $row);
$isMerge = 1;
}
}
$row['altered_by_contact_display_name_link'] = CRM_Utils_System::url('civicrm/contact/view', 'reset=1&cid=' . $row['log_civicrm_entity_log_user_id']);
$row['altered_by_contact_display_name_hover'] = ts("Go to contact summary");
if ($row['log_civicrm_entity_is_deleted'] and 'Update' == CRM_Utils_Array::value('log_civicrm_entity_log_action', $row)) {
$row['log_civicrm_entity_log_action'] = ts('Delete (to trash)');
}
if ('Contact' == CRM_Utils_Array::value('log_type', $this->_logTables[$row['log_civicrm_entity_log_type']]) && CRM_Utils_Array::value('log_civicrm_entity_log_action', $row) == ts('Insert')) {
$row['log_civicrm_entity_log_action'] = ts('Update');
}
if ($newAction = $this->getEntityAction($row['log_civicrm_entity_id'], $row['log_civicrm_entity_log_conn_id'], $row['log_civicrm_entity_log_type'], CRM_Utils_Array::value('log_civicrm_entity_log_action', $row))) {
$row['log_civicrm_entity_log_action'] = $newAction;
}
$row['log_civicrm_entity_log_type'] = $this->getLogType($row['log_civicrm_entity_log_type']);
$date = CRM_Utils_Date::isoToMysql($row['log_civicrm_entity_log_date']);
if ('Update' == CRM_Utils_Array::value('log_civicrm_entity_log_action', $row)) {
$row = $this->addDetailReportLinksToRow($baseQueryCriteria, $row);
}
$key = $date . '_' . $row['log_civicrm_entity_log_type'] . '_' . $isMerge . $row['log_civicrm_entity_log_conn_id'] . '_' . $row['log_civicrm_entity_log_user_id'] . '_' . $row['log_civicrm_entity_altered_contact_id'];
$newRows[$key] = $row;
unset($row['log_civicrm_entity_log_user_id']);
unset($row['log_civicrm_entity_log_conn_id']);
}
krsort($newRows);
$rows = $newRows;
}
/**
* Alter display of rows.
*
* Iterate through the rows retrieved via SQL and make changes for display purposes,
* such as rendering contacts as links.
*
* @param array $rows
* Rows generated by SQL, with an array for each row.
*/
public function alterDisplay(&$rows)
{
// cache for id → is_deleted mapping
$isDeleted = array();
$newRows = array();
foreach ($rows as $key => &$row) {
$isMerge = 0;
$baseQueryCriteria = "reset=1&log_conn_id={$row['log_civicrm_entity_log_conn_id']}";
if (!CRM_Logging_Differ::checkLogCanBeUsedWithNoLogDate($row['log_civicrm_entity_log_date'])) {
$baseQueryCriteria .= '&log_date=' . CRM_Utils_Date::isoToMysql($row['log_civicrm_entity_log_date']);
}
if ($this->cid) {
$baseQueryCriteria .= '&cid=' . $this->cid;
}
if (!isset($isDeleted[$row['log_civicrm_entity_altered_contact_id']])) {
$isDeleted[$row['log_civicrm_entity_altered_contact_id']] = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $row['log_civicrm_entity_altered_contact_id'], 'is_deleted') !== '0';
}
if (!empty($row['log_civicrm_entity_altered_contact']) && !$isDeleted[$row['log_civicrm_entity_altered_contact_id']]) {
$row['log_civicrm_entity_altered_contact_link'] = CRM_Utils_System::url('civicrm/contact/view', 'reset=1&cid=' . $row['log_civicrm_entity_altered_contact_id']);
$row['log_civicrm_entity_altered_contact_hover'] = ts("Go to contact summary");
$entity = $this->getEntityValue($row['log_civicrm_entity_id'], $row['log_civicrm_entity_log_type'], $row['log_civicrm_entity_log_date']);
if ($entity) {
$row['log_civicrm_entity_altered_contact'] = $row['log_civicrm_entity_altered_contact'] . " [{$entity}]";
}
if ($entity == 'Contact Merged') {
// We're looking at a merge activity created against the surviving
// contact record. There should be a single activity created against
// the deleted contact record, with this activity as parent.
$deletedID = CRM_Core_DAO::singleValueQuery('
SELECT GROUP_CONCAT(contact_id) FROM civicrm_activity_contact ac
INNER JOIN civicrm_activity a
ON a.id = ac.activity_id AND a.parent_id = ' . $row['log_civicrm_entity_id'] . ' AND ac.record_type_id =
' . CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_ActivityContact', 'record_type_id', 'Activity Targets'));
if ($deletedID && !stristr($deletedID, ',')) {
$baseQueryCriteria .= '&oid=' . $deletedID;
}
$row['log_civicrm_entity_log_action'] = ts('Contact Merge');
$row = $this->addDetailReportLinksToRow($baseQueryCriteria, $row);
$isMerge = 1;
}
}
$row['altered_by_contact_display_name_link'] = CRM_Utils_System::url('civicrm/contact/view', 'reset=1&cid=' . $row['log_civicrm_entity_log_user_id']);
$row['altered_by_contact_display_name_hover'] = ts("Go to contact summary");
if ($row['log_civicrm_entity_is_deleted'] and 'Update' == CRM_Utils_Array::value('log_civicrm_entity_log_action', $row)) {
$row['log_civicrm_entity_log_action'] = ts('Delete (to trash)');
}
if ('Contact' == CRM_Utils_Array::value('log_type', $this->_logTables[$row['log_civicrm_entity_log_type']]) && CRM_Utils_Array::value('log_civicrm_entity_log_action', $row) == ts('Insert')) {
$row['log_civicrm_entity_log_action'] = ts('Update');
}
// For certain tables, we may want to look at an alternate column to
// determine which action to display, determined by the 'action_column'
// key of the entry in $this->_logTables.
if ($newAction = $this->getEntityAction($row['log_civicrm_entity_id'], $row['log_civicrm_entity_log_conn_id'], $row['log_civicrm_entity_log_type'], CRM_Utils_Array::value('log_civicrm_entity_log_action', $row))) {
$row['log_civicrm_entity_log_action'] = $newAction;
}
$row['log_civicrm_entity_log_type'] = $this->getLogType($row['log_civicrm_entity_log_type']);
$date = CRM_Utils_Date::isoToMysql($row['log_civicrm_entity_log_date']);
if ('Update' == CRM_Utils_Array::value('log_civicrm_entity_log_action', $row)) {
$row = $this->addDetailReportLinksToRow($baseQueryCriteria, $row);
}
// In the summary, we only want to show one row per entity type,
// connection ID, contact ID, and user ID, rolling up multiple
// related actions against the same entity.
$key = $date . '_' . $row['log_civicrm_entity_log_type'] . '_' . $isMerge . '_' . $row['log_civicrm_entity_log_conn_id'] . '_' . $row['log_civicrm_entity_log_user_id'] . '_' . $row['log_civicrm_entity_altered_contact_id'];
$newRows[$key] = $row;
unset($row['log_civicrm_entity_log_user_id']);
unset($row['log_civicrm_entity_log_conn_id']);
}
krsort($newRows);
$rows = $newRows;
}
