private static void AddDataToHierarchyColumns(IODPDataTable IODPDataTable, string fileName, DataTable allSectionsDataTable) { int rowNumber = 1; IntervalHierarchyNames sectionTableColumnNames = new IntervalHierarchyNames() { Expedition = "Exp", Site = "Site", Hole = "Hole", Core = "Core", Type = "Type", Section = "Sect" }; foreach (DataRow row in IODPDataTable.DataTable.Rows) { LithologicDescription description = new LithologicDescription(row[IODPDataTable.SampleIDColumn].ToString()); double parsedOffset = 0; Importer.StartOffsetValuesAreValid(row, IODPDataTable, ref parsedOffset); description.StartOffset = parsedOffset; Importer.EndOffsetValuesAreValid(row, IODPDataTable, ref parsedOffset); description.EndOffset = parsedOffset; LithologicIDGenerator idGenerator = new LithologicIDGenerator(); var textids = GetSectionTextIDsForDescription(allSectionsDataTable, description, sectionTableColumnNames); try { var descriptionID = idGenerator.GenerateID(description); row.BeginEdit(); row["Filename_VP"] = fileName; row["LithologicID_VP"] = descriptionID; row["ArchiveSectionTextID_VP"] = textids.Archive; row["WorkingSectionTextID_VP"] = textids.Working; row["ParentSectionTextID_VP"] = textids.Parent; row["Expedition_VP"] = description.SectionInfo.Expedition; row["Site_VP"] = description.SectionInfo.Site; row["Hole_VP"] = description.SectionInfo.Hole; row["Core_VP"] = description.SectionInfo.Core; row["Type_VP"] = description.SectionInfo.Type; row["Section_VP"] = description.SectionInfo.Section; row["SectionHalf_VP"] = description.SectionInfo.Half; row["TopOffset_VP"] = description.StartOffset.ToString(); row["BottomOffset_VP"] = description.EndOffset.ToString(); row.EndEdit(); } catch (Exception) { Log.Warning(string.Format("Row {0}: Unable to populate data for description", rowNumber.ToString())); } rowNumber++; } }
/// <summary> /// Asynchronously gets a collection of IODP measurements from a .csv file. /// </summary> /// <param name="filename">The .csv file location</param> /// <param name="columnIdentifiers">Optional parameter which specifies the file's column names</param> /// <returns>A collection of measurements</returns> public static async Task <ICollection <Measurement> > GetMeasurementsFromFileAsync(string filename, [Optional] IntervalHierarchyNames columnIdentifiers) { columnIdentifiers = columnIdentifiers ?? new IntervalHierarchyNames() { Expedition = "Exp", Site = "Site", Hole = "Hole", Core = "Core", Type = "Type", Section = "Sect", Half = "A/W", TopOffset = "Offset (cm)", BottomOffset = "Offset (cm)", ArchiveTextID = "ArchiveSectionTextID_VP", WorkingTextID = "WorkingSectionTextID_VP", ParentTextID = "ParentSectionTextID_VP", SampleID = "Sample", TextID = "Text ID", TestNumber = "Test No.", }; //TODO: need to get this some other way string InstrumentSystem = Importer.GetFileNameWithoutExtension(filename); if (InstrumentSystem == "CARB" || InstrumentSystem == "ICP") { columnIdentifiers.TopOffset = "Top offset on section (cm)"; columnIdentifiers.BottomOffset = "Bot offset on section (cm)"; } IODPDataTable iODPDataTable = Importer.ImportDataTableFromFile(filename, columnIdentifiers); ICollection <Measurement> measurements = new HashSet <Measurement>(); try { foreach (DataRow row in iODPDataTable.DataTable.Rows) { IntervalHierarchyValues parsedValues = Importer.GetHierarchyValuesFromDataRow(row, columnIdentifiers); Measurement measurement = new Measurement(); measurement.SectionInfo = new SectionInfo(parsedValues); //Creating a SectionInfo here that will be used to find the one stored in the DB. measurement.DataRow = row; measurement.InstrumentReport = ""; measurement.InstrumentSystem = InstrumentSystem; measurement.TextID = parsedValues.TextID; measurement.TestNumber = parsedValues.TestNumber; measurement.StartOffset = double.TryParse(row[columnIdentifiers.TopOffset].ToString(), out double startOffset) ? startOffset : -1; measurement.EndOffset = double.TryParse(row[columnIdentifiers.BottomOffset].ToString(), out double endOffset) ? endOffset : -1; measurements.Add(measurement); } } catch (Exception) { throw new Exception("Error creating measurement from data row"); } using (DescDBContext dbContext = new DescDBContext()) { string[] expeditions = measurements.Select(x => x.SectionInfo.Expedition).Distinct().ToArray(); ICollection <SectionInfo> sections; try { sections = await DatabaseWorkflowHandler.GetAllSectionsFromDatabaseForExpeditionAsync(dbContext, expeditions).ConfigureAwait(false); } catch (Exception) { throw new Exception("Could not get sections from the database"); } foreach (var measurement in measurements) { measurement.SectionInfo = DatabaseWorkflowHandler.GetSectionInfoFromCollection(sections, measurement.SectionInfo); } return(measurements); } }
/// <summary> /// Creates a SectionInfo object using the the hierarchal information contained in a sampleID. /// </summary> public SectionInfo(string sampleID) { GetPropertiesFromSampleHierarchy(Importer.ParseSampleID(sampleID)); }
public static async Task <bool> GetMeasurementIDForMeasurementFile(string file, string exportFilePath) { ICollection <Measurement> measurements = await MeasurementHandler.GetMeasurementsFromFileAsync(file).ConfigureAwait(true); using (DescDBContext dBContext = new DescDBContext()) { foreach (var measurement in measurements) { measurement.ID = await DatabaseWorkflowHandler.GetMeasurementIDAsync(dBContext, measurement).ConfigureAwait(true); } } //Column Names HashSet <string> columns = measurements.First().MeasurementData.Select(x => x.ColumnName).ToHashSet(); //Check if all measurements have the same Columns foreach (var measurement in measurements) { var compareColumns = measurement.MeasurementData.Select(x => x.ColumnName).ToHashSet(); if (!columns.SetEquals(compareColumns)) { return(false); } } //TODO: Construct new Datatable using (DataTable dataTable = new DataTable()) { foreach (var column in columns) { dataTable.Columns.Add(column); } dataTable.Columns.Add("MeasurementID").SetOrdinal(0); int currentRow = 0; foreach (var measurement in measurements) { dataTable.ImportRow(measurement.DataRow); var row = dataTable.Rows[currentRow]; row.BeginEdit(); row["MeasurementID"] = measurement.ID; row.EndEdit(); currentRow++; } //AddID column //Export measurements to File Importer.ExportDataTableAsNewFile(exportFilePath, dataTable); } return(true); }
public static List <DrillingDisturbanceRecord> FormatDrillingDisturbanceFile(string filename, string exportFilename) { Log.Information("--------Parsing a new file--------"); DataTable sections = SectionInfoCollection.ImportAllSections(ConfigurationManager.AppSettings["AllSectionsFile"]); ICollection <DrillingDisturbanceRecord> descriptions = new HashSet <DrillingDisturbanceRecord>(); List <DrillingDisturbanceRecord> FinalDescriptionsToAdd = new List <DrillingDisturbanceRecord>(); #region ImportDrillingDisturbances var dataTableReader = new CSVReader(); dataTableReader.ReadPath = filename; DataTable drillingDisturbances = dataTableReader.Read(); //Correct Column Names: ChangeColumn(drillingDisturbances, "Drilling disturbance intensity [rank]", "Drilling disturbance intensity rank"); ChangeColumn(drillingDisturbances, "Drilling disturbance intensity rank(read only)", "Drilling disturbance intensity rank"); ChangeColumn(drillingDisturbances, "Drilling disturbance intensity rank (read only)", "Drilling disturbance intensity rank"); ChangeColumn(drillingDisturbances, "Label ID", "Sample"); ChangeColumn(drillingDisturbances, "Top depth [m]", "Top Depth [m]"); ChangeColumn(drillingDisturbances, "Bottom depth [m]", "Bottom Depth [m]"); ChangeColumn(drillingDisturbances, "Disturbance [name]", "Disturbance"); ChangeColumn(drillingDisturbances, "File data", "File Data"); //Add additional columns if (!drillingDisturbances.Columns.Contains("Drilling disturbance comment")) { drillingDisturbances.Columns.Add("Drilling disturbance comment"); } if (!drillingDisturbances.Columns.Contains("Drilling disturbance type")) { drillingDisturbances.Columns.Add("Drilling disturbance type"); } if (!drillingDisturbances.Columns.Contains("Drilling disturbance intensity")) { drillingDisturbances.Columns.Add("Drilling disturbance intensity"); } if (!drillingDisturbances.Columns.Contains("Drilling disturbance intensity rank")) { drillingDisturbances.Columns.Add("Drilling disturbance intensity rank"); } try { //Collection of all drilling disturbances foreach (DataRow row in drillingDisturbances.Rows) { DrillingDisturbanceRecord record = new DrillingDisturbanceRecord() { Column1 = row["Column1"].ToString(), SampleID = row["Sample"].ToString(), Top_cm = row["Top [cm]"].ToString(), Bottom_cm = row["Bottom [cm]"].ToString(), TopDepth_m = row["Top Depth [m]"].ToString(), BottomDepth_m = row["Bottom Depth [m]"].ToString(), DrillingDisturbanceType = row["Drilling disturbance type"].ToString(), DrillingDisturbanceIntensity = row["Drilling disturbance intensity"].ToString(), DrillingDisturbanceIntensityRank = row["Drilling disturbance intensity rank"].ToString(), DrillingDisturbanceComment = row["Drilling disturbance comment"].ToString(), // ShipFileLinks = row["Ship File Links"].ToString(), // ShoreFileLinks = row["Shore File Links"].ToString(), FileData = row["File Data"].ToString() }; descriptions.Add(record); } } catch (Exception ex) { Log.Warning(ex.Message); Log.Warning($"Could not created disturbance records from {filename}"); return(FinalDescriptionsToAdd); } #endregion #region GetTheSectionsInCoreDescription foreach (var description in descriptions) { //Find rows where sample Id doesn't end in A OR W if (description.SampleID.EndsWith("A") || description.SampleID.EndsWith("W")) { FinalDescriptionsToAdd.Add(description); continue; } Log.Information($"{description.SampleID} is a description on the Core"); Log.Information($"{description.SampleID} TOP OFFSET: {description.Top_cm} BOTTOM OFFSET: {description.Bottom_cm} TOPDEPTH: {description.TopDepth_m} BOTTOM DEPTH: {description.BottomDepth_m}"); //At this point the description should be of the entire Core; //Parse Core information from the Sample: SectionInfo coreInfo = new SectionInfo(description.SampleID); //Find all the sections within the AllSectionsTable which overlap with the Top/Bottom offsets of the Core description var constituentSections = sections.AsEnumerable() .Where(x => x.Field <string>("Exp") == coreInfo.Expedition) .Where(x => x.Field <string>("Site") == coreInfo.Site) .Where(x => x.Field <string>("Hole") == coreInfo.Hole) .Where(x => x.Field <string>("Core") == coreInfo.Core) .Where(x => x.Field <string>("Type") == coreInfo.Type) .Where(x => (x.Field <string>("Top depth CSF-A (m)").ToDouble() >= description.TopDepth_m.ToDouble() && x.Field <string>("Top depth CSF-A (m)").ToDouble() < description.BottomDepth_m.ToDouble()) || (x.Field <string>("Bottom depth CSF-A (m)").ToDouble() > description.TopDepth_m.ToDouble() && x.Field <string>("Bottom depth CSF-A (m)").ToDouble() <= description.BottomDepth_m.ToDouble()) ) .Select(x => x) .ToHashSet(); //collection of datarows... //Create new drilling disturbance records by mashing up data between original Disturbances and sections: //Need to relook at this: Use only the top and bottom offsets from the Sample //Find the sections from the allsections table which overlap those intervals //Create new drilling disturbances with section information HashSet <DrillingDisturbanceRecord> newDrillingRecords = new HashSet <DrillingDisturbanceRecord>(); foreach (var section in constituentSections) { //Create new sampleID, All of them will be on the Archive half string newSampleID = string.Format("{0}-{1}{2}-{3}{4}-{5}-A", section["Exp"], section["Site"], section["Hole"], section["Core"], section["Type"], section["Sect"]); //Create a new drilling disturbance record var record = new DrillingDisturbanceRecord { Column1 = description.Column1, SampleID = newSampleID, Top_cm = "0", //section["Top Offset (cm)"].ToString(), Bottom_cm = Math.Round((section["Curated length (m)"].ToString().ToDouble() * 100), 2).ToString(), TopDepth_m = section["Top depth CSF-A (m)"].ToString(), BottomDepth_m = section["Bottom depth CSF-A (m)"].ToString(), DrillingDisturbanceType = description.DrillingDisturbanceType, DrillingDisturbanceIntensity = description.DrillingDisturbanceIntensityRank, DrillingDisturbanceComment = description.DrillingDisturbanceComment, DrillingDisturbanceIntensityRank = description.DrillingDisturbanceIntensityRank, ShipFileLinks = description.ShipFileLinks, ShoreFileLinks = description.ShoreFileLinks, FileData = description.FileData }; newDrillingRecords.Add(record); Log.Information($"{newSampleID}: Section Added: TOP OFFSET: {record.Top_cm} BOTTOM OFFSET: {record.Bottom_cm} TOP DEPTH: {record.TopDepth_m} BOTTOM DEPTH: {record.BottomDepth_m}"); } #endregion region #region SetTheOffsetsForBorderingSections //Set the TOP/BOTTOM offsets for core description's first and last sections to be equal to the core descriptions TOP/BOTTOM Offsets var topSection = newDrillingRecords.OrderBy(x => x.TopDepth_m).First(); var topCorrection = topSection.Top_cm.ToDouble() + (description.TopDepth_m.ToDouble() - topSection.TopDepth_m.ToDouble()) * 100; topCorrection = Math.Round(topCorrection, 2); topSection.Top_cm = topCorrection.ToString(); //Need to calculate offsets based on depths topSection.TopDepth_m = description.TopDepth_m; Log.Information($"{topSection.SampleID}: Changed TopDepth to {topSection.TopDepth_m} and TopOffset to {topSection.Top_cm}"); var bottomSection = newDrillingRecords.OrderBy(x => x.BottomDepth_m).Last(); var bottomCorrection = bottomSection.Bottom_cm.ToDouble() - (bottomSection.BottomDepth_m.ToDouble() - description.BottomDepth_m.ToDouble()) * 100; bottomCorrection = Math.Round(bottomCorrection, 2); bottomSection.Bottom_cm = bottomCorrection.ToString(); bottomSection.BottomDepth_m = description.BottomDepth_m; #endregion Log.Information($"{bottomSection.SampleID}: Changed BottomDepth to {bottomSection.BottomDepth_m} and BottomOffset to {bottomSection.Bottom_cm}"); foreach (var newSectionDescription in newDrillingRecords) { //Find all descriptions the core describer's made for this SECTION. The sample ID's should be equal var describedIntervalsOnSection = descriptions.Where(x => x.SampleID == newSectionDescription.SampleID).ToHashSet(); //If they described any intervals, pass those intervals into the algo to correctly process gap intervals if (describedIntervalsOnSection.Any()) { ICollection <DrillingDisturbanceRecord> finalRecords = CoreToSectionAlgo(newSectionDescription, describedIntervalsOnSection); FinalDescriptionsToAdd.AddRange(finalRecords); foreach (var record in finalRecords) { Log.Information($"{newSectionDescription.SampleID}: Adding to final descriptions"); } } else if (!describedIntervalsOnSection.Any()) { FinalDescriptionsToAdd.Add(newSectionDescription); Log.Information($"{newSectionDescription.SampleID}: Adding to final descriptions"); } } } //These are new descriptions to add to the file foreach (var record in FinalDescriptionsToAdd) { var offsetDifference = Math.Round(record.Bottom_cm.ToDouble() - record.Top_cm.ToDouble(), 2); var depthDifference = Math.Round((record.BottomDepth_m.ToDouble() - record.TopDepth_m.ToDouble()) * 100, 2); if (offsetDifference != depthDifference) { Log.Warning($"Error in Offsets: {record.SampleID}: TOP OFFSET: {record.Top_cm} BOTTOMOFFSET: {record.Bottom_cm} TOPDEPTH: {record.TopDepth_m} BOTTOMDEPTH: {record.BottomDepth_m}"); } else { Log.Information($"{record.SampleID}: TOP OFFSET: {record.Top_cm} BOTTOMOFFSET: {record.Bottom_cm} TOPDEPTH: {record.TopDepth_m} BOTTOMDEPTH: {record.BottomDepth_m}"); } } DataTable dt = new DataTable(); // DataColumn[] columns = new DataColumn[drillingDisturbances.Columns.Count]; // drillingDisturbances.Columns.CopyTo(columns, 0); // dt.Columns.AddRange(columns); for (int i = 0; i < drillingDisturbances.Columns.Count; i++) { dt.Columns.Add(drillingDisturbances.Columns[i].ColumnName, drillingDisturbances.Columns[i].DataType); } //Add in new corrected drilling disturbances foreach (var item in FinalDescriptionsToAdd) { DataRow row = dt.NewRow(); //row.BeginEdit(); row["Column1"] = item.Column1; row["Sample"] = item.SampleID; row["Top [cm]"] = item.Top_cm; row["Bottom [cm]"] = item.Bottom_cm; row["Top Depth [m]"] = item.TopDepth_m; row["Bottom Depth [m]"] = item.BottomDepth_m; row["Drilling disturbance type"] = item.DrillingDisturbanceType; row["Drilling disturbance intensity"] = item.DrillingDisturbanceIntensity; row["Drilling disturbance intensity rank"] = item.DrillingDisturbanceIntensityRank; row["Drilling disturbance comment"] = item.DrillingDisturbanceComment; //row["Ship File Links"] = item.ShipFileLinks; //row["Shore File Links"] = item.ShoreFileLinks; row["File Data"] = item.FileData; dt.Rows.Add(row); //row.EndEdit(); } Importer.ExportDataTableAsNewFile(exportFilename, dt); return(FinalDescriptionsToAdd); }
/// <summary> /// Converts an IODPDataTable object into a collection of Lithologic Descriptions /// </summary> /// <param name="dataTable">The datatable to convert</param> /// <returns></returns> public static Dictionary <string, LithologicDescription> ConvertDatatableToDictionary(IODPDataTable dataTable, SectionInfoCollection SectionCollection) { _ = SectionCollection ?? throw new ArgumentNullException(nameof(SectionCollection)); var LithologyCache = new Dictionary <string, LithologicDescription>(); if (dataTable == null) { return(LithologyCache); } //Add a column in the datatable to ensure consistency between files with and without descriptions: dataTable.DataTable.Columns.Add("LithologicID_VP", typeof(string)).SetOrdinal(0); foreach (DataRow dataTableRow in dataTable.DataTable.Rows) { dataTableRow["LithologicID_VP"] = "-1"; if (!Importer.DataRowContainsDescription(dataTableRow, dataTable)) { return(LithologyCache); } if (!Importer.DataRowContainsSampleIDColumn(dataTableRow, dataTable)) { return(LithologyCache); } LithologicDescription description = new LithologicDescription(dataTableRow[dataTable.SampleIDColumn].ToString()); description.SectionInfo = SectionCollection.GetExistingElseAddAndGetCurrentSection(description.SectionInfo); if (!Importer.DescriptionContainsSectionInfo(description)) { return(LithologyCache); } description.DataRow = dataTableRow; double parsedOffset = 0; if (!Importer.DataRowContainsOffsetColumns(dataTableRow, dataTable)) { return(LithologyCache); } if (!Importer.StartOffsetValuesAreValid(dataTableRow, dataTable, ref parsedOffset)) { return(LithologyCache); } description.StartOffset = parsedOffset; if (!Importer.EndOffsetValuesAreValid(dataTableRow, dataTable, ref parsedOffset)) { return(LithologyCache); } description.EndOffset = parsedOffset; LithologicIDGenerator IDGenerator = new LithologicIDGenerator(); IDGenerator.GenerateID(description); if (description.OffsetsSet()) { description.GenerateSubintervals(); } description.DataRow["LithologicID_VP"] = description.LithologicID; //Some descriptions are split in two rows. It's very uncommon, but throws an error //Only selecting the first row, despite the loss of data if (!LithologyCache.ContainsKey(description.LithologicID)) { LithologyCache.Add(description.LithologicID, description); } } return(LithologyCache); }