public bool LoadDataReportFileToDatabase(DataReportType dbReportType, string dbReportFilename)
{
String folderFilename = dbReportType.ToString().ToLower() + "/" + Path.GetFileName(dbReportFilename);
bool insertStatus = false;
// Handle various delimitters for files
char[] delimitters = { '\n' }; // { '\n', '\r' };
string[] delimitters_str = { "\\n" }; // { "\\n", "\\r" };
String delimitters_sofar = "";
for (int i = 0; i < delimitters.Length; i++)
{
char delimitter = delimitters[i];
string delimitter_str = delimitters_str[i];
if (i == 0)
{
delimitters_sofar += delimitter_str;
}
else
{
delimitters_sofar += " , " + delimitter_str;
}
LogWriter.Instance.WriteToLog("Extracting the file : ( " + folderFilename + " ) with delimitter[" + delimitter_str + "]");
DataTable tbl = ExtractFileToDataTable(dbReportType, dbReportFilename, delimitter);
if (tbl != null & tbl.Rows.Count > 0)
{
LogWriter.Instance.WriteToLog("Completed extracting the file : ( " + folderFilename + " ) with delimitter[" + delimitter_str + "]");
insertStatus = (new DatabaseStore()).LoadDataTableToDatabase(dbReportType, tbl);
break;
}
else
{
if (i < delimitters.Length - 1)
{
LogWriter.Instance.WriteToLog("WARNING: ( " + folderFilename + " ) - Unable to load records into database with delimitters[" + delimitters_sofar + "]");
}
if (i == delimitters.Length - 1)
{
LogWriter.Instance.WriteToLog("ERROR: ( " + folderFilename + " ) - Unable to load records into database with delimitters[" + delimitters_sofar + "]");
}
}
}
return insertStatus;
}
private bool processDataFilesFolder(DataReportType dbReportType)
{
// Get Root Path for DB Files [ data_files_folder ]
string s_dbFilesFolder = null;
bool allFilesProcessed = true;
try
{
s_dbFilesFolder = AppConfigHandler.Instance.getDBFilesConfig("data_files_folder");
if (s_dbFilesFolder == null || (s_dbFilesFolder != null && s_dbFilesFolder.Trim().Length == 0))
{
throw (new IOException("ERROR : The start folder for processing Database Reporting files is not provided in the Config"));
}
// Create the DBReport Folder Path
s_dbFilesFolder += "/" + dbReportType.ToString().ToLower() + "/";
string[] dbFiles = null;
dbFiles = Directory.GetFiles(s_dbFilesFolder, "*.*", SearchOption.AllDirectories);
if (dbFiles != null)
{
if (dbFiles.Length == 0)
{
Console.WriteLine("No files to process in [" + dbReportType.ToString().ToLower() + "]");
LogWriter.Instance.WriteToLog("No files to process in [" + dbReportType.ToString().ToLower() + "]");
}
else
{
foreach (string fileName in dbFiles)
{
String folderFilename = dbReportType.ToString().ToLower() + "/" + Path.GetFileName(fileName);
LogWriter.Instance.WriteToLog("Processing : ( " + folderFilename + " )");
Console.WriteLine("\t" + Path.GetFileName(fileName));
bool DBFileProcessedSuccessfully = false;
DBFileProcessedSuccessfully = (new DataFilesReader()).LoadDataReportFileToDatabase(dbReportType, fileName);
if (DBFileProcessedSuccessfully)
{
LogWriter.Instance.WriteToLog("Successfully loaded ( " + folderFilename + " ) into database");
string s_dbFilesProcessedFolder = null;
s_dbFilesProcessedFolder = AppConfigHandler.Instance.getDBFilesConfig("processed_files_folder");
if (s_dbFilesProcessedFolder == null || (s_dbFilesProcessedFolder != null && s_dbFilesProcessedFolder.Trim().Length == 0))
{
LogWriter.Instance.WriteToLog("The [processed] folder is not provided in the config-file. Using the database-report files folder (data_files_folder) from config-file to create the [processed] folder");
s_dbFilesProcessedFolder = AppConfigHandler.Instance.getDBFilesConfig("data_files_folder") + "/" + "processed" + "/";
}
s_dbFilesProcessedFolder += "/" + dbReportType.ToString().ToLower() + "/";
try
{
Directory.CreateDirectory(@s_dbFilesProcessedFolder);
}
catch (IOException io_ex)
{
throw new IOException("ERROR: Unable to create folder [" + dbReportType.ToString().ToLower() + "] in [processed]");
}
string moveTargetFilePath = s_dbFilesProcessedFolder + Path.GetFileName(fileName);
if (File.Exists(moveTargetFilePath))
File.Delete(moveTargetFilePath);
try
{
File.Move(@fileName, moveTargetFilePath);
LogWriter.Instance.WriteToLog("Processed and moved the file ( " + Path.GetFileName(fileName) + " ) from [" + dbReportType.ToString().ToLower() + "] to [processed/" + dbReportType.ToString().ToLower() + "]");
}
catch (IOException io_ex)
{
throw (new IOException("ERROR : Processed but unable to move the file ( " + Path.GetFileName(fileName) + " ) from [" + dbReportType.ToString().ToLower() + "] to [processed/" + dbReportType.ToString().ToLower() + "]"));
}
}
else
{
LogWriter.Instance.WriteToLog("ERROR: ( " + folderFilename + " ) - Unable to load records into database ( Check delimitter )");
LogWriter.Instance.WriteToLog("ERROR: ( " + folderFilename + " ) - File not processed and moved from [" + dbReportType.ToString().ToLower() + "] to [processed/" + dbReportType.ToString().ToLower() + "]");
allFilesProcessed = false;
}
}
}
}
}
catch (Exception ex)
{
LogWriter.Instance.WriteToLog(ex.Message);
}
return allFilesProcessed;
}
private DataTable ExtractFileFormatToDataTable(DataReportType dbReportType, string dbFileFormatName, char delimitter)
{
//Creating object of datatable
DataTable tbl = new DataTable();
String folderFilename = dbReportType.ToString().ToLower() + "/" + Path.GetFileName(dbFileFormatName);
int expected_columns = 0;
if (dbReportType == DataReportType.GENOME_VCF_MAIN)
{
tbl.Columns.Add("#CHROM");
tbl.Columns.Add("POS");
tbl.Columns.Add("ID");
tbl.Columns.Add("REF");
tbl.Columns.Add("ALT");
tbl.Columns.Add("QUAL");
tbl.Columns.Add("Date");
}
if (dbReportType == DataReportType.GENOME_VCF_EXT)
{
tbl.Columns.Add("#CHROM");
tbl.Columns.Add("POS");
tbl.Columns.Add("ID");
tbl.Columns.Add("REF");
tbl.Columns.Add("ALT");
tbl.Columns.Add("QUAL");
tbl.Columns.Add("FILTER");
tbl.Columns.Add("INFO");
tbl.Columns.Add("FORMAT");
tbl.Columns.Add("NA00001");
tbl.Columns.Add("NA00002");
tbl.Columns.Add("NA00003");
tbl.Columns.Add("Date");
}
expected_columns = tbl.Columns.Count;
try
{
//Reading All text
string Read = File.ReadAllText(dbFileFormatName);
String fileName = Path.GetFileName(dbFileFormatName);
String fileTag = "MSSQL";
if (fileName.StartsWith("ORACLE"))
{
fileTag = "ORACLE";
}
LogWriter.Instance.WriteToLog("Normalizing and checking field values in records...");
// Avoid inserting null rows.
//spliting row after new line
int rowNum = 0;
bool processRow = true;
bool isRowATitleHeader = false;
foreach (string Row in Read.Split(delimitter))
{
isRowATitleHeader = false;
if (rowNum == 0)
{
isRowATitleHeader = checkRowIsATitleHeader(dbReportType, fileTag, Row);
if (isRowATitleHeader)
{
LogWriter.Instance.WriteToLog("Fuzzy title pattern-recognition match : ( " + folderFilename + " ) - detected a title in the first row ( skipping ) ");
}
else
{
LogWriter.Instance.WriteToLog("Fuzzy title pattern-recognition match : ( " + folderFilename + " ) - did not detect a title in the first row ( continuing processing ) ");
}
}
if (!isRowATitleHeader)
{
if (!string.IsNullOrEmpty(Row) && Row.Replace(",", "").Trim().Length > 0)
{
string OriginalFileRec;
string[] FileRecs = Row.Split(',');
string[] ModifiedFileRecs = null;
int total_idx = FileRecs.Length;
for (int i = 0; i < total_idx; i++)
{
FileRecs[i] = FileRecs[i].Replace("\n", "");
FileRecs[i] = FileRecs[i].Replace("\r", "");
}
processRow = true;
if (dbReportType == DataReportType.GENOME_VCF_MAIN)
{
if (total_idx != expected_columns)
{
processRow = false;
}
else
{
ModifiedFileRecs = new string[expected_columns];
}
}
if (dbReportType == DataReportType.GENOME_VCF_EXT)
{
if (total_idx != expected_columns)
{
processRow = false;
}
else
{
ModifiedFileRecs = new string[expected_columns];
}
}
if (processRow)
{
int count = 0;
int current_idx = 0;
while (current_idx < total_idx)
{
string columnName = tbl.Columns[current_idx].ColumnName;
OriginalFileRec = FileRecs[current_idx];
string err_prefix = "ERROR: ( " + folderFilename + " ) - Record contains a field (" + columnName + " @ row:" + (rowNum + 1) + " | column:" + (count + 1) + ") with ";
string err_suffix = "(" + OriginalFileRec + ")";
current_idx++;
String modifiedFileRec = (new SchemaFieldNormalizer()).normalizeField(dbReportType, fileTag, OriginalFileRec, count);
// Sanity check for DateTime
if (modifiedFileRec.StartsWith("EMPTY") && columnName.ToLower().Equals("date"))
{
modifiedFileRec = err_prefix + " NULL/EMPTY DateTime";
}
// Not a valid column data. Leave the column blank and it will be inserted as NULL in MSSQL Database
if (!modifiedFileRec.Equals("EMPTY") && !modifiedFileRec.StartsWith("ERROR"))
{
ModifiedFileRecs[count] = modifiedFileRec;
}
else
{
if (modifiedFileRec.StartsWith("ERROR"))
{
modifiedFileRec = modifiedFileRec.Replace("ERROR:", err_prefix);
if (OriginalFileRec.Trim().Length > 0)
{
modifiedFileRec += "(" + OriginalFileRec + ")";
}
LogWriter.Instance.WriteToLog(modifiedFileRec + " : [" + Row + "] ( skipping )");
processRow = false;
break;
}
}
count++;
}
if (processRow)
{
tbl.Rows.Add();
for (int mf = 0; mf < ModifiedFileRecs.Length; mf++)
{
tbl.Rows[tbl.Rows.Count - 1][mf] = ModifiedFileRecs[mf];
}
}
else
{
}
}
else
{
LogWriter.Instance.WriteToLog("ERROR: ( " + folderFilename + " ) - Record (@ row:" + (rowNum + 1) + ") contains incorrect number of columns [found:" + total_idx + " | expected:" + expected_columns + "] : [" + Row + "] ( skipping )");
}
}
}
rowNum++;
}
LogWriter.Instance.WriteToLog("Completed records normalization and checking stage ( rows:" + rowNum + " | columns:" + expected_columns + " )");
}
catch (IOException io_ex)
{
LogWriter.Instance.WriteToLog("ERROR : " + io_ex.Message);
}
catch (Exception ex)
{
LogWriter.Instance.WriteToLog("ERROR : " + ex.Message);
}
return tbl;
}