/// <summary>
/// Adds audit information to the data source using the new wrapper functionality. If the update fails
/// an exception will be passed back to the calling routine
/// </summary>
/// <param name="passedUniqueID">The unique ID of the record</param>
/// <param name="passedAction">The action taken on that record</param>
/// <param name="connectionString">The string for connecting to the data source</param>
public void AddToAudit(string passedUniqueID, string passedAction, string connectionString)
{
New_Wrapper.DataHandler MyHandler = new New_Wrapper.DataHandler();
DateTime dateT;
string dateOf;
dateT = DateTime.Parse(DateTime.Now.ToShortDateString());
dateOf = dateT.ToString("yyyyMMdd");
DateTime timeT;
string timeOf;
timeT = DateTime.Parse(DateTime.Now.ToShortTimeString());
timeOf = timeT.ToString("HH:mm:ss");
try
{
string tempId = Guid.NewGuid().ToString();
MyHandler.InsertData(connectionString, "INSERT INTO tblAudit (AuditID, AuditDate, AuditTime, PID, RequestID, AuditAction) VALUES ('" + tempId + "', '" + dateOf + "', '" + timeOf + "', '" + Environment.UserName.ToString() + "', '" + passedUniqueID + "', '" + passedAction + "')");
}
catch (Exception ex)
{
throw new Exception("DMBT-GT01a - unable to store the audit information - " + ex.Message);
}
}
/// <summary>
/// Check whether there are matching records that match the specified filter
/// </summary>
/// <param name="provider">The connection string for the data source</param>
/// <param name="password">The encrypted data source password</param>
/// <param name="query">The SQL query for filtering the data</param>
/// <param name="connectionType">The type of data connection (i.e. ADO or SQL)</param>
/// <returns></returns>
public DataSet matchFound(string provider, string password, string query, string connectionType)
{
New_Wrapper.DataHandler myHandler = new New_Wrapper.DataHandler();
switch (connectionType)
{
// case "ADO":
// myHandler = new New_Wrapper.DataHandler(provider, password, query); // password to connect ADO to Access no longer required.
// break;
case "SQL":
myHandler = new New_Wrapper.DataHandler(provider, query);
break;
default:
throw new Exception("Unrecognised connection type.");
}
DataSet temp = myHandler.CreateDataset();
myHandler.CloseConnection();
myHandler = null;
return(temp);
}
/// <summary>
/// Output a CSV file from the DataTable returned from the OutputScannedData Method.
/// </summary>
/// <param name="outputLocation">Where the file should be output too.</param>
/// <param name="fileName">new name for the output file.</param>
/// <param name="cleanedData">provide a datatable.</param>
private void ReturnReformatDataTableAsCsv(DataTable cleanedData, string outputLocation, string fileName)
{
New_Wrapper.DataHandler csvHandler = new New_Wrapper.DataHandler();
csvHandler.GenerateCSV(cleanedData, outputLocation, fileName, "#");
}
///// <summary>
///// Operates on every row and column in the supplied data source.
///// </summary>
///// <param name="filePath">FilePath to where the excel or csv file is located.</param>
///// <param name="outputFormat">What data structure the output should be.</param>
///// <param name="inputExtention">File extension of the input file.</param>
///// <param name="sheetName">Optional Param for the sheet name if input is of type Excel</param>
///// <returns></returns>
//private DataTable OutputScannedData(string filePath, OutputFormat outputFormat, FileType inputExtention, string sheetName = "Sheet1")
//{
// string strFileName = filePath;
// New_Wrapper.DataHandler myHandler = new New_Wrapper.DataHandler();
// string fileLocation = Path.GetDirectoryName(strFileName);
// DataTable cleanedData = new DataTable();
// if (inputExtention != FileType.csv)
// {
// cleanedData = myHandler.ReturnExcelSheetAsDataTable(strFileName, sheetName, true);
// }
// else
// {
// cleanedData = myHandler.ReturnCSVAsDataTable(strFileName, true);
// }
// if (cleanedData.Columns.Count > 11)
// {
// int i = 0;
// for (int j = cleanedData.Columns.Count - 1; j >= 10; j--)
// {
// if (cleanedData.Columns[j].ColumnName.Contains(Path.GetFileNameWithoutExtension(strFileName)))
// {
// cleanedData.Columns.RemoveAt(j);
// }
// i++;
// }
// }
// cleanedData.Columns.Add("NewReference", typeof(string));
// cleanedData.Columns.Add("HOD", typeof(string));
// ReferenceParser.ReferenceParser rp = new ReferenceParser.ReferenceParser();
// foreach (DataRow dr in cleanedData.Rows)
// {
// bool found = false;
// foreach (DataColumn dc in cleanedData.Columns)
// {
// if (found == false)
// {
// dr["NewReference"] = "";
// dr["HOD"] = "";
// foreach (string s in rp.Parse(dr[dc.ColumnName].ToString(), true, true))
// {
// if (s.Trim() != "")
// {
// string[] splitRefHod = s.Split(new string[] { "??$??" }, StringSplitOptions.None);
// dr["NewReference"] = splitRefHod[0];
// dr["HOD"] = splitRefHod[1];
// found = true;
// break;
// }
// else
// {
// found = false;
// dr["NewReference"] = "";
// dr["HOD"] = "";
// }
// }
// }
// }
// }
// return cleanedData;
//}
/// <summary>
/// Overload - Operatate on defined columns provided by a list.
/// </summary>
/// <param name="filePath">FilePath to where the excel or csv file is located.</param>
/// <param name="outputFormat">What data structure the output should be.</param>
/// <param name="inputExtention">File extension of the input file.</param>
/// <param name="columnHeaders">List of columns headers to parse for references.</param>
/// <param name="sheetName">Optional Param for the sheet name if input is of type Excel</param>
/// <returns>Datatable</returns>
private DataTable OutputScannedData(string filePath, OutputFormat outputFormat, FileType inputExtention, List <string> columnHeaders, string sheetName = "Sheet1")
{
string strFileName = filePath;
New_Wrapper.DataHandler myHandler = new New_Wrapper.DataHandler();
string fileLocation = Path.GetDirectoryName(strFileName);
DataTable cleanedData = new DataTable();
if (inputExtention != FileType.csv)
{
cleanedData = myHandler.ReturnExcelSheetAsDataTable(strFileName, sheetName, true);
}
else
{
cleanedData = myHandler.ReturnCSVAsDataTable(strFileName, true);
}
if (cleanedData.Columns.Count > 11)
{
int i = 0;
for (int j = cleanedData.Columns.Count - 1; j >= 10; j--)
{
if (cleanedData.Columns[j].ColumnName.Contains(Path.GetFileNameWithoutExtension(strFileName)))
{
cleanedData.Columns.RemoveAt(j);
}
i++;
}
}
cleanedData.Columns.Add("NewReference", typeof(string));
cleanedData.Columns.Add("HOD", typeof(string));
ReferenceParser.ReferenceParser rp = new ReferenceParser.ReferenceParser();
foreach (DataRow dr in cleanedData.Rows)
{
bool found = false;
foreach (DataColumn dc in cleanedData.Columns)
{
if (columnHeaders.Contains(dc.ColumnName))
{
if (found == false)
{
dr["NewReference"] = "";
dr["HOD"] = "";
foreach (string s in rp.Parse(dr[dc.ColumnName].ToString(), true, true))
{
if (s.Trim() != "")
{
string[] splitRefHod = s.Split(new string[] { "??$??" }, StringSplitOptions.None);
dr["NewReference"] = splitRefHod[0];
dr["HOD"] = splitRefHod[1];
found = true;
break;
}
else
{
found = false;
dr["NewReference"] = "";
dr["HOD"] = "";
}
}
}
}
}
}
return(cleanedData);
}