/** * 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; }