/// <summary>
/// Returns an arraylist of AuditData objects sorted in descending order by change time stamp
/// The 0 th indexed element of the arraylist contains the object with latest change time stamp and goes descedning thereafter.
/// The second parameter is optional - should be used in situations where only the context ids might not be sufficient to point out the audit entries
/// </summary>
/// <param name="contextFieldValues"></param>
/// <returns></returns>
public static ArrayList getAuditRecordsForContextIdsDB(ArrayList contextFieldValues, String contextTable)
{
ArrayList returnList = new ArrayList();
String selectCmd = " select " + AuditData.AUDIT_DATA_COL_CHANGE_DATE_TIME +
"," + AuditData.AUDIT_DATA_COL_CHANGED_BY_CMP +
"," + AuditData.AUDIT_DATA_COL_CHNAGED_BY_USER +
"," + AuditData.AUDIT_DATA_COL_FIELD_NAME +
"," + AuditData.AUDIT_DATA_COL_FIELD_VALUE +
"," + AuditData.AUDIT_DATA_COL_CONTEXT_ID1 +
"," + AuditData.AUDIT_DATA_COL_CONTEXT_ID2 +
"," + AuditData.AUDIT_DATA_COL_CONTEXT_ID3 +
"," + AuditData.AUDIT_DATA_COL_CONTEXT_FIELD_NAME1 +
"," + AuditData.AUDIT_DATA_COL_CONTEXT_FIELD_NAME2 +
"," + AuditData.AUDIT_DATA_COL_CONTEXT_FIELD_NAME3 +
"," + AuditData.AUDIT_DATA_COL_TABLE_NAME +
" from " + AuditData.AUDIT_DATA_TABLE + " where ";
for (int i = 0; i < contextFieldValues.Count; i++)
{
selectCmd += "(" + "'" + contextFieldValues[i] + "'" + "=" + AuditData.AUDIT_DATA_COL_CONTEXT_ID1 + " or " +
"'" + contextFieldValues[i] + "'" + "=" + AuditData.AUDIT_DATA_COL_CONTEXT_ID2 + " or " +
"'" + contextFieldValues[i] + "'" + "=" + AuditData.AUDIT_DATA_COL_CONTEXT_ID3 + ")";
if (i < contextFieldValues.Count - 1)
{
selectCmd += " and ";
}
}
if (contextTable != null && !contextTable.Equals(""))
{
selectCmd += " and " + AuditData.AUDIT_DATA_COL_TABLE_NAME + "=" + "'" + contextTable + "'";
}
selectCmd += "order by change_date_time DESC";
Connections cn = new Connections();
DataSet ds = cn.execQuery(selectCmd, sessionUser);
DataTable dt = ds.Tables[0];
int counter = 0;
foreach (DataRow dr in dt.Rows)
{
AuditData auditDataObj = new AuditData();
auditDataObj.setChange_date_time(dr[0].ToString());
auditDataObj.setChanged_by_cmp(dr[1].ToString());
auditDataObj.setChnaged_by_user(dr[2].ToString());
auditDataObj.setField_name(dr[3].ToString());
auditDataObj.setField_value(dr[4].ToString());
auditDataObj.setContext_id1(dr[5].ToString());
auditDataObj.setContext_id2(dr[6].ToString());
auditDataObj.setContext_id3(dr[7].ToString());
auditDataObj.setContext_field_name1(dr[8].ToString());
auditDataObj.setContext_field_name2(dr[9].ToString());
auditDataObj.setContext_field_name3(dr[10].ToString());
auditDataObj.setTable_name(dr[11].ToString());
returnList.Insert(counter, auditDataObj);
counter++;
}
cn.disconnect();
return(returnList);
}
/// <summary>
/// This method works in multiple steps -
/// 1. Invoke getAuditRecord to generate a dictionary of field names and values as passed in the command
/// 2. Invoke getContextFieldValues to get the dictionary of context fields and their respective values
/// 3. No audit entry will be created for that command where there is not context field value
/// 4. one entry of a field and its value along with the respective (upto 3) context field/s will be made into audit data table
/// </summary>
/// <param name="cmd"></param>
/// <param name="user"></param>
/// <param name="cmpId"></param>
/// <returns></returns>
public static int createAuditRecordForCommand(String cmd, String user, String cmpId)
{
Dictionary <String, String> fieldValueDict = getAuditRecord(cmd);
String tableName = "";
foreach (KeyValuePair <String, String> kvp in fieldValueDict)
{
tableName = kvp.Key.Substring(kvp.Key.IndexOf("-") + 1).Trim();
break;
}
String[] tableNameComps = tableName.Split('.');
if (tableNameComps != null && tableNameComps.Length > 1)
{
tableName = tableNameComps[tableNameComps.Length - 1].Replace("[", "").Replace("]", "").Trim();
}
Dictionary <String, String> contextFieldsNameList = getContextFieldNamesforTable(tableName);
Dictionary <String, String> contextFieldsValues = getContextFieldValues(contextFieldsNameList, cmd);
int rowsAffected = 0;
//No need to make audit entry if there is no context field values
if (contextFieldsValues != null && contextFieldsValues.Count > 0)
{
foreach (KeyValuePair <String, String> kvp in fieldValueDict)
{
AuditData auditObj = new AuditData();
auditObj.setField_value(kvp.Value);
auditObj.setField_name(kvp.Key.Substring(0, kvp.Key.IndexOf("-")));
//Insert the context field values and names for each of the audit entries
int counter = 1;
foreach (KeyValuePair <String, String> kvpVals in contextFieldsValues)
{
if (counter == 1)
{
auditObj.setContext_id1(kvpVals.Value);
auditObj.setContext_field_name1(kvpVals.Key);
}
if (counter == 2)
{
auditObj.setContext_id2(kvpVals.Value);
auditObj.setContext_field_name2(kvpVals.Key);
}
if (counter == 3)
{
auditObj.setContext_id3(kvpVals.Value);
auditObj.setContext_field_name3(kvpVals.Key);
}
counter++;
}
auditObj.setChnaged_by_user(user);
auditObj.setChanged_by_cmp(cmpId);
auditObj.setChange_date_time(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
auditObj.setTable_name(tableName);
rowsAffected += insertContextObject(auditObj);
}
}
return(rowsAffected);
}