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; }