// TBD : SourceHostIP and ActorHostIP are now saved as empty string, What Ip value will be saved is to be disscussed and implement
// As per discussion we are saving ip address of the machine where code is deployed.
public void TransferHistoryRecordsToAccessLogs(string[] config)
{
DataSet dsHistoryTabletbl = new DataSet();
StringBuilder InsertQuery = new StringBuilder();
InsertQuery.Append(" Insert All ");
CounterRecords = 0;
try
{
// get ip address of the machine where code is deployed
string ip = string.Empty;
ip = Dns.GetHostByName(Dns.GetHostName()).AddressList[0].ToString();
// to access config file for dyname Letter_Generation_ID_ColmunName and ELG_Request_ID_ColmunName
ExeConfigurationFileMap fileMap = new ExeConfigurationFileMap();
fileMap.ExeConfigFilename = config[0];
Configuration configFile = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None);
AppSettingsSection section = (AppSettingsSection)configFile.GetSection("appSettings");
ConnectionStringsSection connSection = (ConnectionStringsSection)configFile.GetSection("connectionStrings");
dsHistoryTabletbl = GetHistoryTableDetails();
string LastEntryDateTimeAccesslog_E10_Access = GetLastEntryDateTimeAccesslog().ToString();
for (int i = 0; i < dsHistoryTabletbl.Tables[0].Rows.Count; i++)
{
DataSet dsHistoryRecords = new DataSet();
using (OracleConnection conn = new OracleConnection(Constants.connectionString))
{
using (OracleCommand cmd = new OracleCommand(" Select * from " + dsHistoryTabletbl.Tables[0].Rows[i]["History_Table_Name"] + " where HISTORYTIMESTAMP>to_date( '" + LastEntryDateTimeAccesslog_E10_Access + "', 'yyyy-mm-dd hh24:mi:ss')", conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
LogMessage("Database connection created successfully");
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(dsHistoryRecords);
conn.Close();
if (dsHistoryTabletbl != null)
{
if (dsHistoryRecords.Tables.Count > 0)
{
LogMessage("Total Number of records fetched from Hitory table " + dsHistoryTabletbl.Tables[0].Rows[i]["History_Table_Name"] + " : " + dsHistoryRecords.Tables[0].Rows.Count.ToString());
}
}
for (int j = 0; j < dsHistoryRecords.Tables[0].Rows.Count; j++)
{
DateTime dtHISTORYTIMESTAMP = Convert.ToDateTime(dsHistoryRecords.Tables[0].Rows[j]["HISTORYTIMESTAMP"]);
string HISTORYTIMESTAMP = "TO_CHAR(TO_TIMESTAMP('" + dtHISTORYTIMESTAMP.ToString("yyyy-MM-dd HH:mm:ss") + "', 'YYYY-mm-DD HH24:MI:SS'),'YYYY-mm-DD\"T\"HH24:MI:SS.ff7')||tz_offset(sessiontimezone)";
try
{
string Letter_Generation_ID = string.Empty;
string ELG_Request_ID = string.Empty;
string OUSERID = string.Empty;
string HISTORYID = string.Empty;
if (dsHistoryRecords.Tables[0].Rows[j][dsHistoryTabletbl.Tables[0].Rows[i]["Letter_Generation_ID"].ToString()] != null)
{
Letter_Generation_ID = dsHistoryRecords.Tables[0].Rows[j][dsHistoryTabletbl.Tables[0].Rows[i]["Letter_Generation_ID"].ToString()].ToString();
}
if (dsHistoryRecords.Tables[0].Rows[j][dsHistoryTabletbl.Tables[0].Rows[i]["ELG_Request_ID"].ToString()] != null)
{
ELG_Request_ID = dsHistoryRecords.Tables[0].Rows[j][dsHistoryTabletbl.Tables[0].Rows[i]["ELG_Request_ID"].ToString()].ToString();
}
if (dsHistoryRecords.Tables[0].Rows[j]["OUSERID"] != null)
{
OUSERID = dsHistoryRecords.Tables[0].Rows[j]["OUSERID"].ToString();
}
if (dsHistoryRecords.Tables[0].Rows[j]["HISTORYID"] != null)
{
HISTORYID = dsHistoryRecords.Tables[0].Rows[j]["HISTORYID"].ToString();
}
string DetailText = "User " + OUSERID + " generated letter containing protected information with Letter_Generation_ID=" + Letter_Generation_ID + ", ELG_Request_ID=" + ELG_Request_ID + ", History_ID= " + HISTORYID + "";
InsertQuery.Append(" INTO Accesslog (\"LEVEL\",\"EVENTCODE\",\"SOURCEHOSTIP\",\"ACTORHOSTIP\",\"ACTOR\",\"DETAILTEXT\",\"EVENTTIME\") VALUES ('INFO','E10:Access','" + ip + "','" + ip + "','" + OUSERID + "','" + DetailText + "'," + HISTORYTIMESTAMP + ") ");
CounterRecords++;
}
catch (Exception)
{
LogMessage(" Exception occured in generating insert query with history records from " + dsHistoryTabletbl.Tables[0].Rows[i]["History_Table_Name"].ToString());
throw;
}
// Start inserting into database if InsertQuery has 1000 insert entries.
if (CounterRecords == 1000)
{
InsertQuery.Append(" Select * from Dual ");
if (InsertQuery.ToString().ToUpper().Contains("INTO ACCESSLOG"))
{
ExecuteNonQuery(InsertQuery.ToString());
LogMessage("Total Number of records inserted into Accesslog table : " + CounterRecords);
}
CounterRecords = 0;
InsertQuery = new StringBuilder();
InsertQuery.Append(" Insert All ");
}
}
}
}
}
// Insert entries into database.
InsertQuery.Append(" Select * from Dual ");
if (InsertQuery.ToString().ToUpper().Contains("INTO ACCESSLOG"))
{
ExecuteNonQuery(InsertQuery.ToString());
LogMessage("Total Number of records inserted into Accesslog table : " + CounterRecords);
}
}
catch (Exception exe)
{
LogMessage("Error Occurred while transfering history records into Accesslog table");
LogMessage("Error Message : " + exe.Message);
throw exe;
}
}
