public DataTable GetAllDatabaseData()
{
Dictionary<string, string> tabs = new Dictionary<string, string>()
{
{"Websites Loaded", "webpage_log"},
{"Websites Actions", "webpage_actions"},
{"OSIRT Actions", "osirt_actions" },
{"Attachments", "attachments" },
{"Videos", "videos" },
};
DataTable merged = new DataTable();
foreach (string table in tabs.Values)
{
string columns = DatabaseTableHelper.GetTableColumns(table);
DataTable data = GetRowsFromColumns(table: table, columns: columns);
merged.Merge(data, true, MissingSchemaAction.Add);
}
DataTable dt = new DatabaseHandler().GetRowsFromColumns("case_notes", "", "date", "time", "note");
merged.Merge(dt, true, MissingSchemaAction.Add);
merged.TableName = "merged";
DataView view = new DataView(merged);
view.Sort = "date asc, time asc";
DataTable sortedTable = view.ToTable();
return sortedTable;
}
public static DataTable Search(string databaseTable, string pattern)
{
if (databaseTable == "all")
{
tableToSearch = GetMergedDataTable();
}
else
{
DatabaseHandler dbHandler = new DatabaseHandler();
tableToSearch = dbHandler.GetAllRows(databaseTable);
}
SetColumnNames();
DataRow[] dataRows = tableToSearch.Select(BuildQueryString(pattern));
if (dataRows.Any()) //can't copy to dataTable if there are no DataRows
{
tableToSearch = dataRows.CopyToDataTable();
}
else
{
tableToSearch = null;
}
return tableToSearch;
}
public static string GetFormattedPage(string table, string exportPath, string containerName, string gscp, bool isHtmlReport)
{
DatabaseHandler db = new DatabaseHandler();
string columns = DatabaseTableHelper.GetTableColumns(table);
string page = DatatableToHtml.ConvertToHtml(db.GetRowsFromColumns(table: table, columns: columns), exportPath, containerName);
return ReplaceReportDetails(page, gscp, isHtmlReport);
}
private void EnableTablesToPrint()
{
DatabaseHandler dbHandler = new DatabaseHandler();
var checkboxes = uiReportSelectionGroupBox.GetChildControls<CheckBox>();
foreach (CheckBox cb in checkboxes)
{
string table = cb.Tag.ToString();
if (dbHandler.TableIsEmpty(table))
{
cb.Enabled = false;
cb.Checked = false;
}
}
}
public static string GetCaseDetails()
{
DataTable table = new DatabaseHandler().GetRowsFromColumns("case_details", "", "investigating_agency", "operation_name", "case_reference", "evidence_reference", "notes");
StringBuilder stringBuilder = new StringBuilder();
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
string columnName = column.ColumnName.Replace("_", " ").ToLower();
string html = $@"<p><strong>{CultureInfo.CurrentCulture.TextInfo.ToTitleCase(columnName)}</strong>: {row[column]} </p>";
stringBuilder.Append(html);
}
}
return stringBuilder.ToString();
}
private string GetExistingCaseNotes()
{
DataTable table = new DatabaseHandler().GetRowsFromColumns("case_notes", "", "date", "time", "note");
StringBuilder stringBuilder = new StringBuilder();
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
string cellValue = row[column].ToString();
string toAppend = column.ColumnName == "note" ? Environment.NewLine + cellValue : cellValue + " ";
stringBuilder.Append(toAppend);
}
stringBuilder.Append(Environment.NewLine + Environment.NewLine);
}
return stringBuilder.ToString();
}
private static DataTable GetMergedDataTable()
{
DatabaseHandler db = new DatabaseHandler();
DataTable merged = new DataTable();
foreach (string table in DatabaseTableHelper.GetTables())
{
string columns = DatabaseTableHelper.GetTableColumns(table);
DataTable dt = db.GetRowsFromColumns(table: table, columns: columns);
merged.Merge(dt, true, MissingSchemaAction.Add);
}
merged.TableName = "merged";
DataView view = new DataView(merged);
view.Sort = "date asc, time asc";
DataTable sortedTable = view.ToTable();
return sortedTable;
}
private static string GetCaseNotesToHtml()
{
DataTable table = new DatabaseHandler().GetRowsFromColumns("case_notes", "", "date", "time", "note");
StringBuilder stringBuilder = new StringBuilder();
foreach (DataRow row in table.Rows)
{
string html = $@"
<div id='note'>
<p class='date-time'>{row["date"]} {row["time"]}</p>
<hr class='underline'>
<p class='note'>{row["note"]}</p>
</div>
";
stringBuilder.Append(html);
}
return stringBuilder.ToString();
}
protected override void OsirtGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
//Don't want this to execute when the column header/row is clicked (OOB)
if (e.RowIndex < 0 || e.ColumnIndex < 0)
return;
if (Rows[e.RowIndex].Cells[e.ColumnIndex] is DataGridViewCheckBoxCell)
{
DataGridViewCheckBoxCell column = (DataGridViewCheckBoxCell)Rows[e.RowIndex].Cells[e.ColumnIndex];
if (column.Value != null)
{
bool isChecked = (bool)column.Value;
string id = Rows[e.RowIndex].Cells["id"].Value.ToString();
string query = $"UPDATE {TableName} SET print = '{(!isChecked)}' WHERE id='{id}'";
DatabaseHandler db = new DatabaseHandler();
db.ExecuteNonQuery(query); //TODO: Place an Update method in the db handler
}
}
}
public void CreateTables()
{
List<string> tables = new List<string>();
foreach (var kv in tablesAndColumns)
{
string insert = $@"CREATE TABLE IF NOT EXISTS {kv.Key} (id INTEGER PRIMARY KEY, print BOOLEAN, {kv.Value.Replace(",", " TEXT,")} ";
insert += "TEXT)";
tables.Add(insert);
}
tables.Add(@"CREATE TABLE IF NOT EXISTS case_details (investigating_officer TEXT, investigating_agency TEXT,
operation_name TEXT, case_reference TEXT PRIMARY KEY, evidence_reference TEXT, hash_function TEXT, notes TEXT, hashed_password TEXT)");
tables.Add(@"CREATE TABLE IF NOT EXISTS case_notes (id INTEGER PRIMARY KEY, date TEXT, time TEXT, note TEXT)");
DatabaseHandler handler = new DatabaseHandler();
foreach (string table in tables)
{
handler.ExecuteNonQuery(table);
}
}
private void AuditLogForm_FormClosing(object sender, FormClosingEventArgs e)
{
//re-update the database so print is all true again
//perhaps wrap this in a wait window... "performing audit log closing functions... etc"
DatabaseHandler db = new DatabaseHandler();
db.ExecuteNonQuery("UPDATE webpage_log SET print = 'true'");
db.ExecuteNonQuery("UPDATE webpage_actions SET print = 'true'");
db.ExecuteNonQuery("UPDATE osirt_actions SET print = 'true'");
db.ExecuteNonQuery("UPDATE attachments SET print = 'true'");
db.ExecuteNonQuery("UPDATE videos SET print = 'true'");
//can't UPDATE multiple tables... look into transactions:
//http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005
//http://www.jokecamp.com/blog/make-your-sqlite-bulk-inserts-very-fast-in-c/
}
/// <summary>
/// Logs the specified
/// </summary>
/// <param name="log"></param>
public static void Log(BaseLog log)
{
Type logType = log.GetType();
Dictionary<string, string> toLog = new Dictionary<string, string>
{
{"print", "true"},
{"date", log.Date},
{"time", log.Time},
{"action", log.Action.ToString().SplitAtCapitalLetter()}
};
DatabaseHandler handler = new DatabaseHandler();
if (logType == typeof(WebpageActionsLog))
{
//webpage_actions (date TEXT, time TEXT, action TEXT, url TEXT, file_name TEXT, hash TEXT, note TEXT
WebpageActionsLog webpageAction = (WebpageActionsLog)log;
toLog.Add("url", webpageAction.Url);
toLog.Add("file", webpageAction.File);
toLog.Add("hash", webpageAction.Hash);
toLog.Add("note", webpageAction.Note);
handler.Insert("webpage_actions", toLog);
}
else if (logType == typeof(WebsiteLog))
{
//webpage_log (date TEXT, time TEXT, action TEXT, url TEXT)"
WebsiteLog webSiteLog = (WebsiteLog)log;
toLog.Add("url", webSiteLog.Url);
handler.Insert("webpage_log", toLog);
}
else if (logType == typeof(AttachmentsLog))
{
//date TEXT, time TEXT, action, file TEXT , hash TEXT, notes TEXT)
AttachmentsLog attachLog = (AttachmentsLog)log;
toLog.Add("file", attachLog.File);
toLog.Add("hash", attachLog.Hash);
toLog.Add("note", attachLog.Note);
handler.Insert("attachments", toLog);
}
else if(logType == typeof(OsirtActionsLog))
{
//osirt_actions (id INTEGER PRIMARY KEY, print BOOLEAN, date TEXT, time TEXT, action TEXT, file TEXT, hash TEXT)"
OsirtActionsLog osirtAction = (OsirtActionsLog)log;
toLog.Add("hash", osirtAction.Hash);
toLog.Add("file", osirtAction.File);
handler.Insert("osirt_actions", toLog);
}
else if (logType == typeof(VideoLog))
{
//date TEXT, time TEXT, action, file TEXT , hash TEXT, notes TEXT)
VideoLog attachLog = (VideoLog)log;
toLog.Add("file", attachLog.File);
toLog.Add("hash", attachLog.Hash);
toLog.Add("note", attachLog.Note);
handler.Insert("videos", toLog);
}
}
/// <summary>
/// Gets the number of rows for this particular tab in the DataGridView
/// </summary>
private int TotalRowCount()
{
DatabaseHandler db = new DatabaseHandler();
int count = db.GetTotalRowsFromTable(TableName);
return count;
}
private void GoToPage(int page)
{
DatabaseHandler db = new DatabaseHandler();
Table = db.GetPaginatedDataTable(TableName, page);
PopulateGrid(Table);
}
private void AddCaseDetailsToDb()
{
DatabaseHandler handler = new DatabaseHandler();
handler.Insert("case_details", caseDetails);
}
private DataTable GetMergedDataTable()
{
DatabaseHandler db = new DatabaseHandler();
DataTable merged = new DataTable();
foreach (string table in GetSelectedTables())
{
string columns = DatabaseTableHelper.GetTableColumns(table);
DataTable dt = db.GetRowsFromColumns(table: table, columns: columns);
merged.Merge(dt, true, MissingSchemaAction.Add);
}
//added
if(uiMergeCaseNotesCheckBox.Checked)
{
DataTable dt = new DatabaseHandler().GetRowsFromColumns("case_notes", "", "date", "time", "note");
merged.Merge(dt, true, MissingSchemaAction.Add);
}
//end ad
merged.TableName = "merged";
DataView view = new DataView(merged);
view.Sort = "date asc, time asc";
DataTable sortedTable = view.ToTable();
return sortedTable;
}
private void UpdateCaseTableWithPassword(string hash)
{
DatabaseHandler dbHandler = new DatabaseHandler();
dbHandler.ExecuteNonQuery($"UPDATE case_details SET hashed_password = '******'");
}
