/**
* Generate query for audit table
* @param SugarBean $bean SugarBean that was changed
* @param array $changes List of changes, contains 'before' and 'after'
* @return string Audit table INSERT query
*/
protected function auditSQL(SugarBean $bean, $changes)
{
global $current_user;
$sql = "INSERT INTO " . $bean->get_audit_table_name();
//get field defs for the audit table.
require 'metadata/audit_templateMetaData.php';
$fieldDefs = $dictionary['audit']['fields'];
$values = array();
$values['id'] = $this->massageValue(create_guid(), $fieldDefs['id']);
$values['parent_id'] = $this->massageValue($bean->id, $fieldDefs['parent_id']);
$values['field_name'] = $this->massageValue($changes['field_name'], $fieldDefs['field_name']);
$values['data_type'] = $this->massageValue($changes['data_type'], $fieldDefs['data_type']);
if ($changes['data_type'] == 'text') {
$values['before_value_text'] = $this->massageValue($changes['before'], $fieldDefs['before_value_text']);
$values['after_value_text'] = $this->massageValue($changes['after'], $fieldDefs['after_value_text']);
} else {
$values['before_value_string'] = $this->massageValue($changes['before'], $fieldDefs['before_value_string']);
$values['after_value_string'] = $this->massageValue($changes['after'], $fieldDefs['after_value_string']);
}
$values['date_created'] = $this->massageValue(TimeDate::getInstance()->nowDb(), $fieldDefs['date_created']);
$values['created_by'] = $this->massageValue($current_user->id, $fieldDefs['created_by']);
$sql .= "(" . implode(",", array_keys($values)) . ") ";
$sql .= "VALUES(" . implode(",", $values) . ")";
return $sql;
}
/**
* This method gets the Audit log and formats it specifically for the API.
* @param type SugarBean $bean
* @return array
*/
public function getAuditLog(SugarBean $bean)
{
global $timedate;
if (!$bean->is_AuditEnabled()) {
return array();
}
$auditTable = $bean->get_audit_table_name();
$query = "SELECT {$auditTable}.*, users.user_name AS created_by_username\n FROM {$auditTable}, users\n WHERE {$auditTable}.created_by = users.id AND {$auditTable}.parent_id = '{$bean->id}'\n ORDER BY {$auditTable}.date_created DESC";
$db = DBManagerFactory::getInstance();
$results = $db->query($query);
if (empty($results)) {
return array();
}
$fieldDefs = $this->fieldDefs;
$return = array();
while ($row = $db->fetchByAssoc($results)) {
if (!ACLField::hasAccess($row['field_name'], $bean->module_dir, $GLOBALS['current_user']->id, $bean->isOwner($GLOBALS['current_user']->id))) {
continue;
}
//convert date
$dateCreated = $timedate->fromDbType($db->fromConvert($row['date_created'], 'datetime'), "datetime");
$row['date_created'] = $timedate->asIso($dateCreated);
//If the team_set_id field has a log entry, we retrieve the list of teams to display
if ($row['field_name'] == 'team_set_id') {
$return[] = $this->handleTeamSetField($row);
continue;
}
// look for opportunities to relate ids to name values.
if (!empty($this->genericAssocFieldsArray[$row['field_name']]) || !empty($this->moduleAssocFieldsArray[$bean->object_name][$row['field_name']])) {
foreach ($fieldDefs as $field) {
if (in_array($field['name'], array('before_value_string', 'after_value_string'))) {
$row[$field['name']] = $this->getAssociatedFieldName($row['field_name'], $row[$field['name']]);
}
}
}
$row = $this->formatRowForApi($row);
$fieldName = $row['field_name'];
$fieldType = $db->getFieldType($bean->field_defs[$row['field_name']]);
switch ($fieldType) {
case 'date':
case 'time':
case 'datetime':
$row['before'] = $this->formatDateTime($row['before'], $fieldType);
$row['after'] = $this->formatDateTime($row['after'], $fieldType);
break;
case 'enum':
case 'multienum':
$row['before'] = explode(',', str_replace('^', '', $row['before']));
$row['after'] = explode(',', str_replace('^', '', $row['after']));
break;
case 'relate':
case 'link':
// get the other side
if (isset($bean->field_defs[$fieldName]['module'])) {
$module = $bean->field_defs[$fieldName]['module'];
$otherSideBeanBefore = BeanFactory::getBean($module, $row['before']);
$otherSideBeanAfter = BeanFactory::getBean($module, $row['after']);
if ($otherSideBeanBefore instanceof SugarBean) {
$row['before'] = $otherSideBeanBefore->get_summary_text();
}
if ($otherSideBeanAfter instanceof SugarBean) {
$row['after'] = $otherSideBeanAfter->get_summary_text();
}
}
break;
}
$return[] = $row;
}
return $return;
}
/**
* Saves changes to module's audit table
*
* @param object $bean Sugarbean instance
* @param array $changes changes
* @see DBHelper::getDataChanges()
*/
public function save_audit_records(SugarBean $bean, $changes)
{
global $current_user;
$sql = "INSERT INTO " . $bean->get_audit_table_name();
//get field defs for the audit table.
require 'metadata/audit_templateMetaData.php';
$fieldDefs = $dictionary['audit']['fields'];
$values = array();
$values['id'] = $this->massageValue(create_guid(), $fieldDefs['id']);
$values['parent_id'] = $bean->dbManager->getHelper()->massageValue($bean->id, $fieldDefs['parent_id']);
$values['field_name'] = $bean->dbManager->getHelper()->massageValue($changes['field_name'], $fieldDefs['field_name']);
$values['data_type'] = $bean->dbManager->getHelper()->massageValue($changes['data_type'], $fieldDefs['data_type']);
if ($changes['data_type'] == 'text') {
$bean->fetched_row[$changes['field_name']] = $changes['after'];
$values['before_value_text'] = $bean->dbManager->getHelper()->massageValue($changes['before'], $fieldDefs['before_value_text']);
$values['after_value_text'] = $bean->dbManager->getHelper()->massageValue($changes['after'], $fieldDefs['after_value_text']);
} else {
$bean->fetched_row[$changes['field_name']] = $changes['after'];
$values['before_value_string'] = $bean->dbManager->getHelper()->massageValue($changes['before'], $fieldDefs['before_value_string']);
$values['after_value_string'] = $bean->dbManager->getHelper()->massageValue($changes['after'], $fieldDefs['after_value_string']);
}
$values['date_created'] = $bean->dbManager->getHelper()->massageValue(TimeDate::getInstance()->nowDb(), $fieldDefs['date_created']);
$values['created_by'] = $bean->dbManager->getHelper()->massageValue($current_user->id, $fieldDefs['created_by']);
$sql .= "(" . implode(",", array_keys($values)) . ") ";
$sql .= "VALUES(" . implode(",", $values) . ")";
if ($this->db->dbType == 'oci8' && $changes['data_type'] == 'text') {
$sql .= " RETURNING before_value_text, after_value_text INTO :before_value_text, :after_value_text";
$stmt = oci_parse($this->db->getDatabase(), $sql);
$err = oci_error($this->db->getDatabase());
if ($err != false) {
$GLOBALS['log']->fatal($sql . ">>" . $err['code'] . ":" . $err['message']);
return false;
}
$before_value_text_LOB = oci_new_descriptor($this->db->getDatabase(), OCI_D_LOB);
oci_bind_by_name($stmt, ":before_value_text", $before_value_text_LOB, -1, OCI_B_CLOB);
$after_value_text_LOB = oci_new_descriptor($this->db->getDatabase(), OCI_D_LOB);
oci_bind_by_name($stmt, ":after_value_text", $after_value_text_LOB, -1, OCI_B_CLOB);
oci_execute($stmt, OCI_DEFAULT);
$err = oci_error($this->db->getDatabase());
if ($err != false) {
$GLOBALS['log']->fatal($sql . ">>" . $err['code'] . ":" . $err['message']);
return false;
}
$before_value_text_LOB->save($changes['before']);
$after_value_text_LOB->save($changes['after']);
oci_commit($this->db->getDatabase());
$before_value_text_LOB->free();
$after_value_text_LOB->free();
oci_free_statement($stmt);
} else {
$bean->db->query($sql);
}
}
