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