private Hashtable construtPlantReportObj(int plantid, int type, string dataitem)
{
Hashtable table = new Hashtable();
string plantName = PlantService.GetInstance().GetPlantInfoById(plantid).name + " ";
DefineReport report1 = new DefineReport()
{
ReportType = type,
ReportName = plantName + "-" + DataReportType.getNameByCode(type),
SaveTime = DateTime.Now,
dataitem = dataitem,
PlantId = plantid
};
ReportConfig config1 = new ReportConfig()
{
sendFormat = "html",
email = "",
sendMode = "2",
reportId = 0,
plantId = plantid
};
table.Add(0, report1);
table.Add(1, config1);
return(table);
}
private Hashtable construtAllPlantsReportObj(int userid, int type, string dataitem)
{
Hashtable table = new Hashtable();
DefineReport report1 = new DefineReport()
{
ReportType = type,
ReportName = "All plants-" + DataReportType.getNameByCode(type),
SaveTime = DateTime.Now,
dataitem = dataitem,
UserId = userid
};
ReportConfig config1 = new ReportConfig()
{
tinterval = "4",
sendFormat = "html",
sendMode = "2",
email = "",
reportId = 0,
plantId = 0
};
table.Add(0, report1);
table.Add(1, config1);
return(table);
}
public ReviewReport(Guid ID, string Name, string GroupName, string PreviewViewPath, string IndexViewPath
, DataReportType DataReportType, ReviewReportStatus ReviewReportStatus
, bool AllowParentFlowProceedNextProcess, List <IDataReport> DataReports)
: base(ID, Name)
{
_dataReports = DataReports;
this.DataReportType = DataReportType;
this.ReviewReportStatus = ReviewReportStatus;
this.AllowParentFlowProceedNextProcess = AllowParentFlowProceedNextProcess;
this.GroupName = GroupName;
this.PreviewViewPath = PreviewViewPath;
this.IndexViewPath = IndexViewPath;
}
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 bool checkRowIsATitleHeader(DataReportType dbReportType, String fileTag, String Row)
{
String[] vcf_main =
{
"#CHROM",
"POS",
"ID",
"REF",
"ALT",
"QUAL",
"Date"
};
String[] vcf_ext =
{
"#CHROM",
"POS",
"ID",
"REF",
"ALT",
"QUAL",
"FILTER",
"INFO",
"FORMAT",
"NA00001",
"NA00002",
"NA00003",
"Date"
};
bool isRowATitleHeader = false;
string[] columns = Row.Split(',');
int num_matches = 0;
// Fuzzy match
if (dbReportType == DataReportType.GEONOME_VCF_MAIN)
{
for (int i = 0; i < columns.Length; i++)
{
if (columns[i].Replace("\"", "").Trim().ToLower().Equals(server_titles[i].Trim().ToLower()))
{
num_matches++;
}
}
double col_pct_match = num_matches / columns.Length;
if (col_pct_match > 0.85)
{
isRowATitleHeader = true;
}
}
num_matches = 0;
if (dbReportType == DataReportType.GENOME_VCF_EXT)
{
for (int i = 0; i < columns.Length; i++)
{
if (columns[i].Replace("\"", "").Trim().ToLower().Equals(database_titles[i].Trim().ToLower()))
{
num_matches++;
}
}
double col_pct_match = num_matches / columns.Length;
if (col_pct_match > 0.85)
{
isRowATitleHeader = true;
}
}
num_matches = 0;
if (dbReportType == DataReportType.ACCESS)
{
for (int i = 0; i < columns.Length; i++)
{
if (columns[i].Replace("\"", "").Trim().ToLower().Contains(access_titles[i].Trim().ToLower()))
{
num_matches++;
}
}
double col_pct_match = num_matches / columns.Length;
if (col_pct_match > 0.70)
{
isRowATitleHeader = true;
}
}
return isRowATitleHeader;
}
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;
}
public String normalizeField(DataReportType dbReportType, String fileTag, String rowOriginalField, int columnIdx)
{
rowOriginalField = rowOriginalField.Replace("\"", "");
String rowNormalizedField = rowOriginalField;
if (dbReportType == DataReportType.GENOME_VCF_MAIN)
{
switch (columnIdx)
{
case 1:
rowNormalizedField = normalizeFormatField(rowOriginalField);
break;
case 2:
rowNormalizedField = normalizeInfoField(rowOriginalField);
break;
case 4:
rowNormalizedField = normalizeNumericField(rowOriginalField);
break;
case 5:
rowNormalizedField = normalizeNumericField(rowOriginalField);
break;
case 6:
rowNormalizedField = normalizeNumericField(rowOriginalField);
break;
case 7:
rowNormalizedField = normalizeNumericField(rowOriginalField);
break;
case 11:
rowNormalizedField = normalizeBooleanField(rowOriginalField);
break;
case 12:
rowNormalizedField = normalizeDateTimeField(rowOriginalField, fileTag);
break;
}
}
if (dbReportType == DataReportType.GENOME_VCF_EXT)
{
switch (columnIdx)
{
case 1:
rowNormalizedField = normalizeFormatField(rowOriginalField);
break;
case 2:
rowNormalizedField = normalizeInfoField(rowOriginalField);
break;
case 5:
rowNormalizedField = normalizeNumericField(rowOriginalField);
break;
case 6:
rowNormalizedField = normalizeBooleanField(rowOriginalField);
break;
case 8:
rowNormalizedField = normalizeBooleanField(rowOriginalField);
break;
case 9:
rowNormalizedField = normalizeNumericField(rowOriginalField);
break;
case 12:
rowNormalizedField = normalizeDateTimeField(rowOriginalField, fileTag);
break;
}
}
return rowNormalizedField;
}
public bool LoadDataTableToDatabase(DataReportType dbReportType, DataTable dt)
{
SqlConnection con;
String sqlconn;
bool insertStatus = false;
try
{
try
{
sqlconn = DatabaseHelper.createConnectionString();
con = new SqlConnection(sqlconn);
}
catch (Exception io_ex)
{
throw (new Exception("ERROR : Unable to create Database SQL connection for loading data with the provided connection string"));
}
try
{
con.Open();
LogWriter.Instance.WriteToLog("Established database connection successfully for loading data");
}
catch (Exception io_ex)
{
throw (new Exception("ERROR : Unable to open the database connection for loading data"));
}
SqlBulkCopy objbulk;
try
{
//creating object of SqlBulkCopy
objbulk = new SqlBulkCopy(con);
}
catch (Exception io_ex)
{
throw (new Exception("ERROR : Unable to create database SQL bulk-write object for loading data with the provided database connection"));
}
if (objbulk != null)
{
//assigning Destination table name
if (dbReportType == DataReportType.GENOME_VCF_MAIN)
{
objbulk.DestinationTableName = "DataVCFMain";
//Mapping Table column
objbulk.ColumnMappings.Add("#CHROM", "CHROM");
objbulk.ColumnMappings.Add("POS","POS");
objbulk.ColumnMappings.Add("ID","ID");
objbulk.ColumnMappings.Add("REF","REF");
objbulk.ColumnMappings.Add("ALT","ALT");
objbulk.ColumnMappings.Add("QUAL","QUAL");
objbulk.ColumnMappings.Add("Date", "Date");
}
if (dbReportType == DataReportType.GENOME_VCF_EXT)
{
objbulk.DestinationTableName = "DataVCFExt";
//Mapping Table column
objbulk.ColumnMappings.Add("#CHROM", "CHROM");
objbulk.ColumnMappings.Add("POS", "POS");
objbulk.ColumnMappings.Add("ID", "ID");
objbulk.ColumnMappings.Add("REF", "REF");
objbulk.ColumnMappings.Add("ALT", "ALT");
objbulk.ColumnMappings.Add("QUAL", "QUAL");
objbulk.ColumnMappings.Add("FILTER", "FILTER");
objbulk.ColumnMappings.Add("INFO", "INFO");
objbulk.ColumnMappings.Add("FORMAT", "FORMAT");
objbulk.ColumnMappings.Add("NA00001", "NA00001");
objbulk.ColumnMappings.Add("NA00002", "NA00002");
objbulk.ColumnMappings.Add("NA00003", "NA00003");
objbulk.ColumnMappings.Add("Date", "Date");
}
/* Pretty print of the table
//inserting Datatable Records to DataBase
foreach (DataRow dataRow in dt.Rows)
{
foreach (var item in dataRow.ItemArray)
{
Console.Write(item);
Console.Write("\t");
}
Console.WriteLine();
}
*/
try
{
LogWriter.Instance.WriteToLog("Bulk-loading the data onto the database. Writing ...");
objbulk.WriteToServer(dt);
objbulk.Close();
con.Close();
LogWriter.Instance.WriteToLog("Successfully bulk-loaded data into database. Closed database connection.");
insertStatus = true;
}
catch (Exception io_ex)
{
con.Close();
throw (new Exception("ERROR: Unable to bulk-load the data onto the Database"));
}
}
}
catch (SqlException sql_ex)
{
Console.WriteLine(sql_ex.Message);
LogWriter.Instance.WriteToLog(sql_ex.Message);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
LogWriter.Instance.WriteToLog(ex.Message);
}
return insertStatus;
}
