public int UpdateAuditTrail(DataModificationAction action) { //DataPusher not included List <AuditTrailInsertData> auditTrails = new List <AuditTrailInsertData>() { new AuditTrailInsertData { Action = (int)action, Fields = new List <AuditTrailEntry>(), RefId = this.WebForm.RefId, TableRowId = this.WebForm.TableRowId } }; return(this.UpdateAuditTrail(auditTrails)); }
public string GetAuditTrail() { string qry = @" SELECT m.id, l.id, u.fullname, m.eb_createdby, m.eb_createdat, m.actiontype, l.tablename, l.fieldname, l.idrelation, l.oldvalue, l.newvalue FROM eb_audit_master m LEFT JOIN eb_audit_lines l ON m.id = l.masterid LEFT JOIN eb_users u ON m.eb_createdby = u.id WHERE m.formid = @formid AND m.dataid = @dataid ORDER BY m.id DESC, l.tablename, l.idrelation;"; //0 id, 1 id, 2 fullname, 3 eb_createdby, 4 eb_createdat, 5 actiontype, 6 tablename, 7 fieldname, 8 idrelation, 9 oldvalue, 10 newvalue DbParameter[] parameters = new DbParameter[] { DataDB.GetNewParameter("formid", EbDbTypes.String, this.WebForm.RefId), DataDB.GetNewParameter("dataid", EbDbTypes.Int32, this.WebForm.TableRowId) }; EbDataTable dt = this.DataDB.DoQuery(qry, parameters); Dictionary <int, FormTransaction> Trans = new Dictionary <int, FormTransaction>(); Dictionary <int, int> m_id_map = new Dictionary <int, int>(); int counter = 1; TableSchema _table = null; ColumnSchema _column = null; Dictionary <string, string> DictVmAll = new Dictionary <string, string>(); foreach (EbDataRow dr in dt.Rows) { int m_id = Convert.ToInt32(dr["id"]); if (!m_id_map.ContainsKey(m_id)) { m_id_map.Add(m_id, counter++); } m_id = m_id_map[m_id]; DataModificationAction action = (DataModificationAction)Convert.ToInt32(dr["actiontype"]); if (action == DataModificationAction.Updated) { string TableName = Convert.ToString(dr["tablename"]); if (_table == null || !_table.TableName.Equals(TableName)) { _table = this.WebForm.FormSchema.Tables.FirstOrDefault(tbl => tbl.TableName == TableName); if (_table == null)//no such table - skipping invalid Audit Trail entry { continue; } } if (_table.TableType == WebFormTableTypes.Normal) { _column = _table.Columns.FirstOrDefault(col => col.ColumnName == Convert.ToString(dr["fieldname"])); if (_column == null)//no such control - skipping invalid Audit Trail entry { continue; } if (_column.Control.DoNotPersist || _column.Control.Hidden) { continue; } } } if (!Trans.ContainsKey(m_id)) { Trans.Add(m_id, new FormTransaction() { ActionType = action.ToString().Replace("_", " "), CreatedBy = Convert.ToString(dr["fullname"]), CreatedById = Convert.ToString(dr["eb_createdby"]), CreatedAt = Convert.ToDateTime(dr["eb_createdat"]).ConvertFromUtc(this.WebForm.UserObj.Preference.TimeZone).ToString(this.WebForm.UserObj.Preference.GetShortDatePattern() + " " + this.WebForm.UserObj.Preference.GetShortTimePattern(), CultureInfo.InvariantCulture) }); } if (action != DataModificationAction.Updated) { continue; } string[] rel_ids = Convert.ToString(dr["idrelation"]).Split('-'); string new_val = dr.IsDBNull(10) ? null : Convert.ToString(dr["newvalue"]); string old_val = dr.IsDBNull(9) ? null : Convert.ToString(dr["oldvalue"]); if (_table.TableType != WebFormTableTypes.Normal) { Dictionary <string, string> new_val_dict = (new_val == null || new_val == "[null]") ? null : JsonConvert.DeserializeObject <Dictionary <string, string> >(new_val); Dictionary <string, string> old_val_dict = (old_val == null || old_val == "[null]") ? null : JsonConvert.DeserializeObject <Dictionary <string, string> >(old_val); if (!Trans[m_id].GridTables.ContainsKey(_table.TableName)) { Trans[m_id].GridTables.Add(_table.TableName, new FormTransactionTable() { Title = _table.Title }); for (int i = 0; i < _table.Columns.Count; i++) { EbControl _control = _table.Columns.ElementAt(i).Control; if (_control.DoNotPersist || _control.Hidden) { continue; } if (_control is EbDGColumn _dgcol) { Trans[m_id].GridTables[_table.TableName].ColumnMeta.Add(new FormTransactionMetaInfo() { Index = i, Title = _dgcol.Title ?? _control.Label, IsNumeric = _dgcol is EbDGNumericColumn }); } else { Trans[m_id].GridTables[_table.TableName].ColumnMeta.Add(new FormTransactionMetaInfo() { Index = i, Title = _control.Label, IsNumeric = _control is EbNumeric }); } } } int curid = Convert.ToInt32(rel_ids[1]); FormTransactionTable TblRef = Trans[m_id].GridTables[_table.TableName]; if (!TblRef.NewRows.ContainsKey(curid) && !TblRef.EditedRows.ContainsKey(curid) && !TblRef.DeletedRows.ContainsKey(curid)) { //TblRef.Rows.Add(curid, new FormTransactionRow() { }); if (new_val_dict == null) { TblRef.DeletedRows.Add(curid, new FormTransactionRow() { }); } else if (old_val_dict == null) { TblRef.NewRows.Add(curid, new FormTransactionRow() { }); } else { TblRef.EditedRows.Add(curid, new FormTransactionRow() { }); } } FormTransactionRow curRow = TblRef.NewRows.ContainsKey(curid) ? TblRef.NewRows[curid] : TblRef.DeletedRows.ContainsKey(curid) ? TblRef.DeletedRows[curid] : TblRef.EditedRows[curid]; foreach (ColumnSchema __column in _table.Columns) { if (__column.Control.DoNotPersist || __column.Control.Hidden) { continue; } bool IsModified = false; if (new_val_dict?.ContainsKey(__column.ColumnName) == true && old_val_dict?.ContainsKey(__column.ColumnName) == true) { if (new_val_dict[__column.ColumnName] != old_val_dict[__column.ColumnName]) { IsModified = true; } } string a = old_val_dict?.ContainsKey(__column.ColumnName) == true ? old_val_dict[__column.ColumnName] : null; string b = new_val_dict?.ContainsKey(__column.ColumnName) == true ? new_val_dict[__column.ColumnName] : null; this.PreProcessTransationData(DictVmAll, _table, __column, ref a, ref b); curRow.Columns.Add(__column.ColumnName, new FormTransactionEntry() { OldValue = a, NewValue = b, IsModified = IsModified, IsNumeric = __column.Control is EbDGNumericColumn }); } } else { if (old_val == new_val) { continue; } if (!Trans[m_id].Tables.ContainsKey(_table.TableName)) { Trans[m_id].Tables.Add(_table.TableName, new FormTransactionRow() { }); } this.PreProcessTransationData(DictVmAll, _table, _column, ref old_val, ref new_val); FormTransactionEntry curtrans = new FormTransactionEntry() { OldValue = old_val, NewValue = new_val, IsModified = true, Title = string.IsNullOrEmpty(_column.Control.Label) ? _column.ColumnName : _column.Control.Label }; Trans[m_id].Tables[_table.TableName].Columns.Add(_column.ColumnName, curtrans); } } try { this.PostProcessTransationData(Trans, DictVmAll); } catch (Exception e) { Console.WriteLine("Exception in AuditTrail.PostProcessTransationData. Message : " + e.Message + "\nStackTrace : " + e.StackTrace); } return(JsonConvert.SerializeObject(Trans)); }