public List <object> ProcessLASFile(LASFile lasFile, string origFilename, ModelImportStatus mis, Guid currentProjectID, BackgroundWorker backgroundWorker)
{
BaseImportTools bit = new BaseImportTools();
List <object> data = new List <object>();
data = bit.ImportLasFile(lasFile, origFilename, mis, currentProjectID, backgroundWorker);
return(data);
}
public LASFile GetLASFile(string inputFilename, ModelImportStatus mis)
{
LASFile lf = null;
try
{
LASFileReader lfr = new LASFileReader();
int columnOffset = 0;
int errorCode = 0;
lf = lfr.ReadLASFile(inputFilename, columnOffset, out errorCode);
if (errorCode == 0)
{
mis.finalErrorCode = ModelImportStatus.OK;
string res = "";
foreach (string nc in lf.columnHeaders)
{
res += nc + ", ";
}
mis.linesReadFromSource = lf.dataRows.Count;
// Display error mesasges if required
if (lf.errorDetails != null && lf.errorDetails.Count > 0)
{
string messageBoxText = "The file (" + inputFilename + ") was loaded, but issues were noted as follows:";
mis.AddWarningMessage(messageBoxText);
foreach (string ed in lf.errorDetails)
{
string ss = "\n" + ed;
mis.AddWarningMessage(ss);
}
}
}
else
{
string messageBoxText = "The file (" + inputFilename + ") could not be loaded. Please check that the file is " +
"accessible, is not open in another application and is in the correct format.";
mis.AddErrorMessage(messageBoxText);
string errorCodeDetails = LASErrorCodes.LookupCode(errorCode);
if (lf != null && lf.errorDetails != null)
{
foreach (string ed in lf.errorDetails)
{
string ss = "\n" + ed;
mis.AddErrorMessage(ss);
}
}
mis.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
string caption = "Error loading file " + inputFilename;
}
}
catch (Exception ex)
{
}
return(lf);
}
public List<object> ProcessLASFile(LASFile lasFile, string origFilename, ModelImportStatus mis, Guid currentProjectID, BackgroundWorker backgroundWorker)
{
BaseImportTools bit = new BaseImportTools();
List<object> data = new List<object>();
data = bit.ImportLasFile(lasFile, origFilename, mis, currentProjectID, backgroundWorker);
return data;
}
internal ModelImportStatus DoSurveyImport(string SelectedFile, string SelectedFormatFile, ImportDataMap importMap, RawFileReader rawFileReader, Guid NKDProjectID, bool doOverwrite, bool checkForDuplicates)
{
BaseImportTools bit = new BaseImportTools();
ModelImportStatus mos = new ModelImportStatus();
GeneralFileInfo gfi = new GeneralFileInfo();
gfi.GeneralFileStats(SelectedFile);
int numLines = gfi.numLines;
Stream fileStream = new FileStream(SelectedFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
//Stream fileStream = new FileStream(SelectedFile, FileMode.Open);
bit.PerformSurveyImport(mos, fileStream, null, importMap, this.backgroundWorker, NKDProjectID, ConnectionString, numLines, doOverwrite, checkForDuplicates);
return(mos);
}
public void AppendModel(ContentItem c)
{
var m = c.As <BlockModelPart>();
string statusMessage = "";
bool completed = false;
BaseImportTools bit = new BaseImportTools();
string targetFolder;
bool attmemptModelLoad;
var bmFileName = ExtractBlockModelFromZip(m.BmFileName, out targetFolder, out attmemptModelLoad);
IStorageFile bmFile = _storageProvider.GetFile(bmFileName);
Stream bmFileStream = bmFile.OpenRead();
ModelImportStatus mos = new ModelImportStatus();
try
{
// Special append method for GF requirements - contains X, Y, Z, and value to append.
// target model must have matching X, Y and Z centroids.
// auto generate a format defintion based on Goldfields typical input column data
var opts = new TransactionOptions();
opts.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
using (new TransactionScope(TransactionScopeOption.Suppress, opts))
{
mos = bit.PerformBMAppend(bmFileStream, m.BmGuid, m.Alias, m.ColumnNameToAdd, m.ColumnIndexToAdd, _users.ApplicationConnectionString, ',');
mos.importTextFileName = m.BmFileName;
mos.targetModelName = m.Alias;
}
statusMessage += string.Format("Successfully appended data to block model:\n{0} - {1}\n\nFrom file:{2}\n\n", m.Alias, m.BmGuid, m.BmFileName);
completed = true;
}
catch (Exception ex)
{
statusMessage += string.Format("Error appending data to block model:\n{0} - {1}\n\nFrom file:{2}\n\nError:\n{3}\n\n", m.Alias, m.BmGuid, m.BmFileName, ex.ToString());
}
finally {
statusMessage += mos.GenerateStringMessage();
bmFileStream.Close();
_storageProvider.DeleteFile(bmFileName);
_storageProvider.DeleteFolder(targetFolder);
}
statusMessage += string.Format("Time Elapsed: {0}\n\n", m.Processed.HasValue ? (DateTime.UtcNow - m.Processed.Value).ToString(@"hh\h\:mm\m\:ss\s\:fff\m\s") : "Unknown");
Logger.Information(statusMessage);
_users.EmailUsers(m.Recipients.SplitStringArray(), completed ? "Model Append Succeeded" : "Model Append Failed", statusMessage);
}
public void SetData(ModelImportStatus _mos)
{
mos = _mos;
string message = "";
ObservableCollection <ErrorMessages> messageList = new ObservableCollection <ErrorMessages>();
GenerateMessages(out message, out messageList, true);
//DataGridMessageList.Items.
foreach (ErrorMessages em in messageList)
{
em.SetStatusTextInfo();
}
DataGridMessageList.Items.Clear();
DataGridMessageList.ItemsSource = messageList;
MessageText.Text = message;
}
internal ModelImportStatus DoCollarImport(string SelectedFile, string SelectedFormatBMFile, ImportDataMap importMap, RawFileReader rawFileReader, Guid NKDProjectID, bool overwrite)
{
BaseImportTools bit = new BaseImportTools();
// get the current collar names in this project
List <CollarInfo> existingHoles = this.GetHolesForProject(NKDProjectID);
List <string> existingHoleNames = new List <string>();
foreach (CollarInfo ci in existingHoles)
{
existingHoleNames.Add(ci.Name);
}
ModelImportStatus mos = new ModelImportStatus();
Stream fileStream = new FileStream(SelectedFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
//Stream fileStream = new FileStream(SelectedFile, FileMode.Open);
bit.PerformCollarImport(mos, fileStream, null, importMap, this.backgroundWorker, NKDProjectID, ConnectionString, existingHoleNames, overwrite);
return(mos);
}
/// <summary>
/// Carry out the block model import
/// </summary>
/// <param name="SelectedBMFile"></param>
/// <param name="SelectedFormatBMFile"></param>
/// <param name="importMap"></param>
/// <param name="rawFileReader"></param>
/// <returns></returns>
internal bool DoBMImport(string SelectedBMFile, string SelectedFormatBMFile, ImportDataMap importMap, RawFileReader rawFileReader, string NKDProjectID, string modelAlias)
{
BaseImportTools bit = new BaseImportTools();
int cxColumnID = importMap.GetColumnIDMappedTo("CentroidX");
int cyColumnID = importMap.GetColumnIDMappedTo("CentroidY");
int czColumnID = importMap.GetColumnIDMappedTo("CentroidZ");
ColumnStats xOrigin = rawFileReader.GetDimensions(cxColumnID);
ColumnStats yOrigin = rawFileReader.GetDimensions(cyColumnID);
ColumnStats zOrigin = rawFileReader.GetDimensions(czColumnID);
int approxNumLines = xOrigin.count;
Stream bmFileStream = new FileStream(SelectedBMFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
// Stream bmFileStream = new FileStream(SelectedBMFile, FileMode.Open);
ModelImportStatus mos = new ModelImportStatus();
Guid newModelGuid = Guid.NewGuid();
Guid authorGuid = new Guid();
List <string> status = bit.PerformBMImport(mos, newModelGuid, bmFileStream, null, importMap, xOrigin.min, yOrigin.min, zOrigin.min, backgroundWorker, approxNumLines, NKDProjectID, modelAlias, authorGuid, ConnectionString);
return(true);
}
public void ProcessModel(ContentItem c)
{
var m = c.As <BlockModelPart>();
List <string> domains = null;
string targetFolder;
bool attmemptModelLoad;
var bmFileName = ExtractBlockModelFromZip(m.BmFileName, out targetFolder, out attmemptModelLoad);
ModelImportStatus mos = DoNewModelImport(bmFileName, m.FormatFileName, m.ProjectName, m.Alias, m.UserID, ref domains, targetFolder, attmemptModelLoad, m.Notes, m.Stage, m.StageMetaID);
mos.importTextFileName = bmFileName + " (from " + m.BmFileName + ")";
mos.targetModelName = m.Alias;
string msg = "";
string subject = "";
if (mos.finalErrorCode > 0)
{
subject = "Model Import Failed";
msg += string.Format("Error importing block model:\n{0}\n\n", m.Alias, m.BmFileName);
}
else
{
subject = "Model Import Succeeded";
msg += string.Format("Successfully imported block model:\n{0}\n\n", m.Alias, m.BmFileName);
}
msg += mos.GenerateStringMessage();
msg += string.Format("Time Elapsed: {0}\n\n", m.Processed.HasValue ? (DateTime.UtcNow - m.Processed.Value).ToString(@"hh\h\:mm\m\:ss\s\:fff\m\s") : "Unknown");
Logger.Information(msg);
_users.EmailUsers(m.Recipients.SplitStringArray(), subject, msg);
}
internal void AddSurveyData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap, int batchSize, Action <string, double> UpdateStatus, int approxNumLines, string connectionString, Guid NKDProjectID, bool overwrite, bool checkForDuplicates)
{
bool duplicateFound = false;
// iterate through the data lines
int ct = 1;
int linesRead = 0;
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
Dictionary <Guid, List <string> > rejectedLines = new Dictionary <Guid, List <string> >();
Dictionary <string, string> holeWarningMessages = new Dictionary <string, string>();
using (var entityObj = new NKDC(connectionString, null))
{
//entityObj.Configuration.AutoDetectChangesEnabled = false;
SurveyQueries sq = new SurveyQueries();
// get a connection to the database
try
{
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
int numCommits = 0;
SqlTransaction trans;
//trans = connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
List <SqlCommand> commands = new List <SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
try
{
sr = new StreamReader(fileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input data:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)approxNumLines;
// get the column containing the hole name
Dictionary <string, Guid> holeIDLookups = new Dictionary <string, Guid>();
int numberOfHolesAdded = 0;
ColumnMap headerCmap = importMap.FindItemsByTargetName("HeaderID");
ColumnMap depthCmap = importMap.FindItemsByTargetName("Depth");
float percentComplete = 0;
int headerIDX = headerCmap.sourceColumnNumber;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
repCount++;
percentComplete = ((float)ct / approxNumLines) * 100.0f;
bct++;
linesRead++;
if (ct >= importMap.dataStartLine)
{
string statementPart1 = "INSERT INTO " + importMap.mapTargetPrimaryTable + " ";
string clauseValues = "";
string clauseParameters = "";
List <string> items = parseTestLine(line, importMap.inputDelimiter);
// using the column map, pick out the hole name field and see if it is in the database already
string headerNameItem = items[headerIDX];
// check if this holename is a duplicate in the file
bool foundHole = false;
Guid holeID = new Guid();
bool lv = holeIDLookups.ContainsKey(headerNameItem);
if (!lv)
{
string headerGUID = ForeignKeyUtils.FindFKValueInOther(headerNameItem, headerCmap, secondaryConnection, false, "HoleName", NKDProjectID);
if (headerGUID == null)
{
// this means we have not found the specified records in the header table
// Report on issue and skip line
}
else
{
foundHole = true;
holeID = new Guid(headerGUID);
holeIDLookups.Add(headerNameItem, holeID);
}
}
else
{
holeIDLookups.TryGetValue(headerNameItem, out holeID);
foundHole = true;
}
if (!foundHole)
{
mos.AddWarningMessage("Failed to find hole " + headerNameItem + ". Skipping record at line " + linesRead + ".");
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
mos.recordsFailed++;
continue;
}
if (checkForDuplicates == true && depthCmap != null)
{
// check for duplicate depths
string d = items[depthCmap.sourceColumnNumber];
decimal dt = 0;
bool isParsed = decimal.TryParse(d, out dt);
if (isParsed)
{
List <Guid> rr = sq.CheckForDuplicate(holeID, dt, secondaryConnection);
//List<Guid> rr = sq.CheckForDuplicate(holeID, dt, entityObj);
if (rr.Count > 0)
{
duplicateFound = true;
if (!rejectedLines.ContainsKey(rr.First()))
{
rejectedLines.Add(rr.First(), items);
mos.AddWarningMessage("Duplicate depth found in survey data for hole " + headerNameItem + " at depth " + d + " on line " + linesRead);
UpdateStatus("Duplicate depth found in survey data for hole " + headerNameItem + " at depth " + d, percentComplete);
}
else
{
mos.AddWarningMessage("Duplicate depth found in survey data file for hole " + headerNameItem + " at depth " + d + " on line " + linesRead);
UpdateStatus("Duplicate depth found in survey data file for hole " + headerNameItem + " at depth " + d, percentComplete);
rejectedLines[rr.First()] = items;
}
if (!overwrite)
{
mos.recordsFailed++;
}
continue;
}
}
}
#region mappsearch
// now pick out all the mapped values
foreach (ColumnMap cmap in importMap.columnMap)
{
if (cmap.targetColumnName.Trim().Equals("HeaderID"))
{
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
clauseParameters += "\'" + holeID.ToString() + "\',";
}
else
{
bool isFKColumn = cmap.hasFKRelation;
int colID = cmap.sourceColumnNumber;
string columnValue = cmap.defaultValue;
if (colID >= 0)
{
columnValue = items[colID];
}
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
if (isFKColumn)
{
// go and search for the appropriate value from the foreign key table
string newValue = ForeignKeyUtils.FindFKValueInDictionary(columnValue, cmap, secondaryConnection, true);
columnValue = newValue;
if (newValue != null && newValue.Trim().Length > 0)
{
clauseParameters += "\'" + columnValue + "\',";
}
else
{
clauseParameters += "NULL,";
}
}
else
{
if (cmap.importDataType.Equals(ImportDataMap.NUMERICDATATYPE))
{
if (columnValue.Equals("-") || columnValue.Equals(""))
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
else
{
columnValue = "NULL";
}
}
clauseParameters += columnValue + ",";
}
else
{
//if (columnValue.Equals("-"))
//{
// if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
// {
// columnValue = cmap.defaultValue;
// }
//}
clauseParameters += "\'" + columnValue + "\',";
}
}
}
}
#endregion
// now just a hack to remove the final coma from the query
clauseParameters = clauseParameters.Substring(0, clauseParameters.Length - 1);
clauseValues = clauseValues.Substring(0, clauseValues.Length - 1);
string commandText = statementPart1 + "(" + clauseValues + ") VALUES (" + clauseParameters + ")";
//SqlCommand sqc = new SqlCommand(commandText, connection, trans);
SqlCommand sqc = new SqlCommand(commandText, connection);
numberOfHolesAdded++;
if (commitToDB)
{
try
{
sqc.ExecuteNonQuery();
}
catch (Exception ex)
{
mos.AddErrorMessage("Failed to insert items on line " + linesRead + ".");
UpdateStatus("Failed to insert items on line " + linesRead + ".", percentComplete);
mos.recordsFailed++;
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
UpdateStatus("Updating from line " + linesRead, percentComplete);
tb++;
//if (tb == transactionBatchLimit)
//{
// // commit batch, then renew the transaction
// if (commitToDB)
// {
// trans.Commit();
// numCommits++;
// // trans = null;
// trans = connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
// }
// // reset counter
// tb = 0;
//}
}
ct++;
}
}
if (tb > 0)
{
//if (commitToDB)
//{
// trans.Commit();
//}
numCommits++;
}
mos.recordsAdded = numberOfHolesAdded;
UpdateStatus("Finished writing records to database ", 100.0);
}
catch (Exception ex)
{
UpdateStatus("Error writing records to database ", 0);
mos.AddErrorMessage("Error writing records data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try
{
connection.Close();
secondaryConnection.Close();
fileStream.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
if (duplicateFound == true && overwrite == true)
{
OverwriteSurveyRecord(mos, rejectedLines, importMap, connectionString, NKDProjectID, UpdateStatus, holeWarningMessages);
}
foreach (KeyValuePair <string, string> kvp in holeWarningMessages)
{
string v = kvp.Value;
mos.AddWarningMessage(v);
}
mos.linesReadFromSource = linesRead;
}
}
internal void AddCollarData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap, int batchSize, Action <string, double> UpdateStatus, int approxNumLines, string connectionString, List <string> existingHoleNames, Guid NKDProjectID, bool overwrite)
{
bool hasDuplicateHoles = false;
// iterate through the data lines
int ct = 1;
int linesRead = 0;
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
Dictionary <string, int> uniqueHoleNames = new Dictionary <string, int>();
List <List <string> > rejectedLines = new List <List <string> >();
Dictionary <string, string> holeWarningMessages = new Dictionary <string, string>();
// this loop makes sure that any guids are properly types so that a text string for that guid can be passed into the query
foreach (ColumnMap cmap in importMap.columnMap)
{
bool isFKColumn = cmap.hasFKRelation;
if (isFKColumn)
{
cmap.importDataType = ImportDataMap.TEXTDATATYPE;
}
}
// get a connection to the database
try
{
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction();
List <SqlCommand> commands = new List <SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
try
{
sr = new StreamReader(fileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input data:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)approxNumLines;
// get the column containing the hole name
ColumnMap cmapHeader = importMap.FindItemsByTargetName("HoleName");
cmapHeader.importDataType = ImportDataMap.TEXTDATATYPE;
int headerIDX = cmapHeader.sourceColumnNumber;
int numberOfHolesAdded = 0;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
repCount++;
bct++;
linesRead++;
if (linesRead == 75)
{
bool breakHere;
int cc = 0;
double ff = Math.PI * 10;
}
if (ct >= importMap.dataStartLine)
{
string statementPart1 = "INSERT INTO " + importMap.mapTargetPrimaryTable + " ";
string clauseValues = "";
string clauseParameters = "";
List <string> items = parseTestLine(line, importMap.inputDelimiter);
// using the column map, pick out the hole name field and see if it is in the database already
string headerNameItem = items[headerIDX];
// check if this holename is a duplicate in the file
if (headerNameItem.Trim().Equals("A11A"))
{
bool b = true;
}
bool hasHolenameEntryInFile = uniqueHoleNames.ContainsKey(headerNameItem.Trim());
if (hasHolenameEntryInFile)
{
int val = uniqueHoleNames[headerNameItem.Trim()];
uniqueHoleNames[headerNameItem.Trim()] = val++;
holeWarningMessages.Add(headerNameItem + " (" + val + ")", "Hole: " + headerNameItem + " at line " + linesRead + " already exists in the input file - skipping.");
mos.recordsFailed++;
continue;
}
else
{
uniqueHoleNames.Add(headerNameItem.Trim(), 1);
}
// check if this hole exists in the database
if (existingHoleNames.Contains(headerNameItem.Trim()))
{
if (!holeWarningMessages.ContainsKey(headerNameItem))
{
holeWarningMessages.Add(headerNameItem, "Hole: " + headerNameItem + " already exists in this project. Skipping record at line " + linesRead + ".");
if (!overwrite)
{
mos.recordsFailed++;
}
}
else
{
}
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
hasDuplicateHoles = true;
rejectedLines.Add(items);
continue;
}
#region mappsearch
// now pick out all the mapped values
foreach (ColumnMap cmap in importMap.columnMap)
{
bool isFKColumn = cmap.hasFKRelation;
int colID = cmap.sourceColumnNumber;
string columnValue = cmap.defaultValue;
if (colID >= 0)
{
columnValue = items[colID];
}
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
if (isFKColumn)
{
// go and search for the appropriate value from the foreign key table
string newValue = ForeignKeyUtils.FindFKValueInDictionary(columnValue, cmap, secondaryConnection, true);
if (newValue == null)
{
clauseParameters += "NULL,";
}
else
{
clauseParameters += "\'" + newValue + "\',";
}
}
else
{
if (cmap.importDataType.Equals(ImportDataMap.NUMERICDATATYPE))
{
if (columnValue.Equals("-") || columnValue.Trim().Length == 0)
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
else
{
columnValue = "NULL";
}
}
clauseParameters += columnValue + ",";
}
else if (cmap.importDataType.Equals(ImportDataMap.TIMESTAMPDATATYPE))
{
var parsed = DateUtils.CleanDate(columnValue);
if (parsed.HasValue)
{
columnValue = "\'" + parsed.Value.ToString("yyyy-MM-dd hh:mm:ss tt") + "\'";
}
else
{
columnValue = "NULL";
}
clauseParameters += columnValue + ",";
}
else
{
if (columnValue.Equals("-") || columnValue.Trim().Length == 0)
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
}
clauseParameters += "\'" + columnValue + "\',";
}
}
}
#endregion
// now just a hack to remove the final coma from the query
clauseParameters = clauseParameters.Substring(0, clauseParameters.Length - 1);
clauseValues = clauseValues.Substring(0, clauseValues.Length - 1);
string commandText = statementPart1 + "(" + clauseValues + ") VALUES (" + clauseParameters + ")";
SqlCommand sqc = new SqlCommand(commandText, connection, trans);
numberOfHolesAdded++;
if (commitToDB)
{
try
{
sqc.ExecuteNonQuery();
}
catch (Exception ex) {
string err = "" + ex.ToString();
throw ex;
}
}
tb++;
if (tb == transactionBatchLimit)
{
// commit batch, then renew the transaction
if (commitToDB)
{
trans.Commit();
numCommits++;
// trans = null;
trans = connection.BeginTransaction();
}
// reset counter
tb = 0;
}
}
ct++;
}
}
if (tb > 0)
{
if (commitToDB)
{
trans.Commit();
}
numCommits++;
}
mos.recordsAdded = numberOfHolesAdded;
UpdateStatus("Finished writing collars to database ", 100.0);
}
catch (Exception ex)
{
UpdateStatus("Error writing collars to database ", 0);
mos.AddErrorMessage("Error writing collar data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try {
connection.Close();
secondaryConnection.Close();
fileStream.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
if (hasDuplicateHoles == true && overwrite == true)
{
OverwriteCollarRecord(mos, rejectedLines, importMap, connectionString, NKDProjectID, UpdateStatus, holeWarningMessages);
}
foreach (KeyValuePair <string, string> kvp in holeWarningMessages)
{
string v = kvp.Value;
mos.AddWarningMessage(v);
}
mos.linesReadFromSource = linesRead;
}
private void OverwriteCollarRecord(ModelImportStatus mos, List <List <string> > rejectedLines, ImportDataMap importMap, string connectionString, Guid NKDProjectID, Action <string, double> UpdateStatus, Dictionary <string, string> holeWarningMessages)
{
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
try
{
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction();
List <SqlCommand> commands = new List <SqlCommand>();
int tb = 0;
int transactionBatchLimit = 10;
// open the filestream and read the first line
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)rejectedLines.Count();
// get the column containing the hole name
ColumnMap cmapHeader = importMap.FindItemsByTargetName("HoleName");
cmapHeader.importDataType = ImportDataMap.TEXTDATATYPE;
int headerIDX = cmapHeader.sourceColumnNumber;
int numberOfHolesAdded = 0;
int linesRead = 0;
int ct = 1;
// get all fo the header IDs in one go before we try the insert
Dictionary <string, Guid> holeIDLookups = CollarQueries.FindHeaderGuidsForProject(NKDProjectID);
// this loop makes sure that any guids are properly types so that a text string for that guid can be passed into the query
foreach (ColumnMap cmap in importMap.columnMap)
{
bool isFKColumn = cmap.hasFKRelation;
if (isFKColumn)
{
cmap.importDataType = ImportDataMap.TEXTDATATYPE;
}
}
foreach (List <string> columnData in rejectedLines)
{
linesRead++;
repCount++;
bct++;
string statementPart1 = "UPDATE " + importMap.mapTargetPrimaryTable + " ";
string clauseValues = "";
// using the column map, pick out the hole name field and see if it is in the database already
string headerNameItem = columnData[headerIDX];
string headerGUID = "";
bool lv = holeIDLookups.ContainsKey(headerNameItem);
if (!lv)
{
// oops - no hole ID with this name - should not happen though!!
}
else
{
Guid holeGuid = new Guid();
holeIDLookups.TryGetValue(headerNameItem, out holeGuid);
headerGUID = holeGuid.ToString();
}
#region mappsearch
// now pick out all the mapped values
foreach (ColumnMap cmap in importMap.columnMap)
{
bool isFKColumn = cmap.hasFKRelation;
int colID = cmap.sourceColumnNumber;
string columnValue = cmap.defaultValue;
if (colID >= 0)
{
columnValue = columnData[colID];
}
string targetCol = cmap.targetColumnName;
// ignore mapped hole name and project ID columns
if (targetCol.Trim().Equals("HoleName") || targetCol.Trim().Equals("ProjectID"))
{
continue;
}
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + "=";
if (isFKColumn)
{
// go and search for the appropriate value from the foreign key table
string newValue = ForeignKeyUtils.FindFKValueInDictionary(columnValue, cmap, secondaryConnection, true);
if (newValue == null)
{
clauseValues += "NULL,";
}
else
{
clauseValues += "\'" + newValue + "\',";
}
}
else
{
if (cmap.importDataType.Equals(ImportDataMap.NUMERICDATATYPE))
{
if (columnValue.Equals("-") || columnValue.Trim().Length == 0)
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
else
{
columnValue = "NULL";
}
}
}
else if (cmap.importDataType.Equals(ImportDataMap.TIMESTAMPDATATYPE))
{
DateTime dtr = new DateTime();
bool parsed = DateTime.TryParse(columnValue, out dtr);
if (parsed)
{
columnValue = "\'" + dtr.ToString("yyyy-MM-dd hh:mm:ss tt") + "\'";
}
else
{
columnValue = "NULL";
}
}
else
{
columnValue = "\'" + columnValue + "\'";
}
clauseValues += columnValue + ",";
}
}
#endregion
// now just a hack to remove the final coma from the query
clauseValues = clauseValues.Substring(0, clauseValues.Length - 1);
string commandText = statementPart1 + "SET " + clauseValues + " WHERE HeaderID=\'" + headerGUID + "\' AND ProjectID=\'" + NKDProjectID.ToString() + "\';";
SqlCommand sqc = new SqlCommand(commandText, connection, trans);
string msg = "";
//holeWarningMessages.TryGetValue(headerNameItem, out msg);
msg = "Hole " + headerNameItem + " was overwritten with new data";
holeWarningMessages[headerNameItem] = msg;
numberOfHolesAdded++;
if (commitToDB)
{
sqc.ExecuteNonQuery();
}
tb++;
if (tb == transactionBatchLimit)
{
// commit batch, then renew the transaction
if (commitToDB)
{
trans.Commit();
numCommits++;
// trans = null;
trans = connection.BeginTransaction();
}
// reset counter
tb = 0;
}
ct++;
}
if (tb > 0)
{
if (commitToDB)
{
trans.Commit();
}
numCommits++;
}
mos.recordsUpdated = numberOfHolesAdded;
UpdateStatus("Finished writing collars to database ", 100.0);
}
catch (Exception ex)
{
UpdateStatus("Error writing collars to database ", 0);
mos.AddErrorMessage("Error writing collar data at line " + rejectedLines.Count + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try
{
connection.Close();
secondaryConnection.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
}
internal void AddCollarData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap, int batchSize, Action<string, double> UpdateStatus, int approxNumLines, string connectionString, List<string> existingHoleNames, Guid XODBProjectID, bool overwrite)
{
bool hasDuplicateHoles = false;
// iterate through the data lines
int ct = 1;
int linesRead = 0;
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
Dictionary<string,int> uniqueHoleNames = new Dictionary<string,int>();
List<List<string>> rejectedLines = new List<List<string>>();
Dictionary<string, string> holeWarningMessages = new Dictionary<string, string>();
// this loop makes sure that any guids are properly types so that a text string for that guid can be passed into the query
foreach (ColumnMap cmap in importMap.columnMap)
{
bool isFKColumn = cmap.hasFKRelation;
if (isFKColumn)
{
cmap.importDataType = ImportDataMap.TEXTDATATYPE;
}
}
// get a connection to the database
try
{
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction();
List<SqlCommand> commands = new List<SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
try
{
sr = new StreamReader(fileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input data:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)approxNumLines;
// get the column containing the hole name
ColumnMap cmapHeader = importMap.FindItemsByTargetName("HoleName");
cmapHeader.importDataType = ImportDataMap.TEXTDATATYPE;
int headerIDX = cmapHeader.sourceColumnNumber;
int numberOfHolesAdded = 0;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
repCount++;
bct++;
linesRead++;
if (linesRead == 75) {
bool breakHere;
int cc = 0;
double ff = Math.PI * 10;
}
if (ct >= importMap.dataStartLine)
{
string statementPart1 = "INSERT INTO " + importMap.mapTargetPrimaryTable + " ";
string clauseValues = "";
string clauseParameters = "";
List<string> items = parseTestLine(line, importMap.inputDelimiter);
// using the column map, pick out the hole name field and see if it is in the database already
string headerNameItem = items[headerIDX];
// check if this holename is a duplicate in the file
if(headerNameItem.Trim().Equals("A11A")){
bool b = true;
}
bool hasHolenameEntryInFile = uniqueHoleNames.ContainsKey(headerNameItem.Trim());
if (hasHolenameEntryInFile) {
int val = uniqueHoleNames[headerNameItem.Trim()];
uniqueHoleNames[headerNameItem.Trim()] = val++;
holeWarningMessages.Add(headerNameItem+" ("+val+")", "Hole: " + headerNameItem + " at line " + linesRead + " already exists in the input file - skipping.");
mos.recordsFailed++;
continue;
} else {
uniqueHoleNames.Add(headerNameItem.Trim(), 1);
}
// check if this hole exists in the database
if (existingHoleNames.Contains(headerNameItem.Trim()))
{
if (!holeWarningMessages.ContainsKey(headerNameItem))
{
holeWarningMessages.Add(headerNameItem, "Hole: " + headerNameItem + " already exists in this project. Skipping record at line " + linesRead + ".");
if(!overwrite){
mos.recordsFailed ++;
}
}
else {
}
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
hasDuplicateHoles = true;
rejectedLines.Add(items);
continue;
}
#region mappsearch
// now pick out all the mapped values
foreach (ColumnMap cmap in importMap.columnMap)
{
bool isFKColumn = cmap.hasFKRelation;
int colID = cmap.sourceColumnNumber;
string columnValue = cmap.defaultValue;
if (colID >= 0)
{
columnValue = items[colID];
}
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
if (isFKColumn)
{
// go and search for the appropriate value from the foreign key table
string newValue = ForeignKeyUtils.FindFKValueInDictionary(columnValue, cmap, secondaryConnection, true);
if (newValue == null)
{
clauseParameters += "NULL,";
}
else {
clauseParameters += "\'" + newValue + "\',";
}
}
else
{
if (cmap.importDataType.Equals(ImportDataMap.NUMERICDATATYPE))
{
if (columnValue.Equals("-") || columnValue.Trim().Length == 0)
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
else
{
columnValue = "NULL";
}
}
clauseParameters += columnValue + ",";
}
else if (cmap.importDataType.Equals(ImportDataMap.TIMESTAMPDATATYPE))
{
DateTime dtr = new DateTime();
bool parsed = DateTime.TryParse(columnValue, out dtr);
if (parsed)
{
columnValue = "\'" + dtr.ToString("yyyy-MM-dd hh:mm:ss tt") + "\'";
}
else
{
columnValue = "NULL";
}
clauseParameters += columnValue + ",";
}
else
{
if (columnValue.Equals("-") || columnValue.Trim().Length == 0)
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
}
clauseParameters += "\'" + columnValue + "\',";
}
}
}
#endregion
// now just a hack to remove the final coma from the query
clauseParameters = clauseParameters.Substring(0, clauseParameters.Length - 1);
clauseValues = clauseValues.Substring(0, clauseValues.Length - 1);
string commandText = statementPart1 + "(" + clauseValues + ") VALUES (" + clauseParameters + ")";
SqlCommand sqc = new SqlCommand(commandText, connection, trans);
numberOfHolesAdded++;
if (commitToDB)
{
try
{
sqc.ExecuteNonQuery();
}
catch (Exception ex) {
string err = "" + ex.ToString();
throw ex;
}
}
tb++;
if (tb == transactionBatchLimit)
{
// commit batch, then renew the transaction
if (commitToDB)
{
trans.Commit();
numCommits++;
// trans = null;
trans = connection.BeginTransaction();
}
// reset counter
tb = 0;
}
}
ct++;
}
}
if (tb > 0)
{
if (commitToDB)
{
trans.Commit();
}
numCommits++;
}
mos.recordsAdded = numberOfHolesAdded;
UpdateStatus("Finished writing collars to database ", 100.0);
}
catch (Exception ex)
{
UpdateStatus("Error writing collars to database ", 0);
mos.AddErrorMessage("Error writing collar data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try {
connection.Close();
secondaryConnection.Close();
fileStream.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
if (hasDuplicateHoles == true && overwrite == true)
{
OverwriteCollarRecord(mos, rejectedLines , importMap, connectionString, XODBProjectID, UpdateStatus, holeWarningMessages);
}
foreach (KeyValuePair<string, string> kvp in holeWarningMessages) {
string v = kvp.Value;
mos.AddWarningMessage(v);
}
mos.linesReadFromSource = linesRead;
}
private void OverwriteCollarRecord(ModelImportStatus mos, List<List<string>> rejectedLines, ImportDataMap importMap, string connectionString, Guid XODBProjectID, Action<string, double> UpdateStatus, Dictionary<string, string> holeWarningMessages)
{
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
try
{
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction();
List<SqlCommand> commands = new List<SqlCommand>();
int tb = 0;
int transactionBatchLimit = 10;
// open the filestream and read the first line
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)rejectedLines.Count();
// get the column containing the hole name
ColumnMap cmapHeader = importMap.FindItemsByTargetName("HoleName");
cmapHeader.importDataType = ImportDataMap.TEXTDATATYPE;
int headerIDX = cmapHeader.sourceColumnNumber;
int numberOfHolesAdded = 0;
int linesRead = 0;
int ct = 1;
// get all fo the header IDs in one go before we try the insert
Dictionary<string, Guid> holeIDLookups = CollarQueries.FindHeaderGuidsForProject(XODBProjectID);
// this loop makes sure that any guids are properly types so that a text string for that guid can be passed into the query
foreach (ColumnMap cmap in importMap.columnMap)
{
bool isFKColumn = cmap.hasFKRelation;
if (isFKColumn)
{
cmap.importDataType = ImportDataMap.TEXTDATATYPE;
}
}
foreach (List<string> columnData in rejectedLines)
{
linesRead++;
repCount++;
bct++;
string statementPart1 = "UPDATE " + importMap.mapTargetPrimaryTable + " ";
string clauseValues = "";
// using the column map, pick out the hole name field and see if it is in the database already
string headerNameItem = columnData[headerIDX];
string headerGUID = "";
bool lv = holeIDLookups.ContainsKey(headerNameItem);
if (!lv)
{
// oops - no hole ID with this name - should not happen though!!
}
else
{
Guid holeGuid = new Guid();
holeIDLookups.TryGetValue(headerNameItem, out holeGuid);
headerGUID = holeGuid.ToString();
}
#region mappsearch
// now pick out all the mapped values
foreach (ColumnMap cmap in importMap.columnMap)
{
bool isFKColumn = cmap.hasFKRelation;
int colID = cmap.sourceColumnNumber;
string columnValue = cmap.defaultValue;
if (colID >= 0)
{
columnValue = columnData[colID];
}
string targetCol = cmap.targetColumnName;
// ignore mapped hole name and project ID columns
if (targetCol.Trim().Equals("HoleName") || targetCol.Trim().Equals("ProjectID"))
{
continue;
}
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + "=";
if (isFKColumn)
{
// go and search for the appropriate value from the foreign key table
string newValue = ForeignKeyUtils.FindFKValueInDictionary(columnValue, cmap, secondaryConnection, true);
if (newValue == null)
{
clauseValues += "NULL,";
}
else
{
clauseValues += "\'" + newValue + "\',";
}
}
else
{
if (cmap.importDataType.Equals(ImportDataMap.NUMERICDATATYPE))
{
if (columnValue.Equals("-") || columnValue.Trim().Length == 0)
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
else
{
columnValue = "NULL";
}
}
}
else if (cmap.importDataType.Equals(ImportDataMap.TIMESTAMPDATATYPE))
{
DateTime dtr = new DateTime();
bool parsed = DateTime.TryParse(columnValue, out dtr);
if (parsed)
{
columnValue = "\'" + dtr.ToString("yyyy-MM-dd hh:mm:ss tt") + "\'";
}
else
{
columnValue = "NULL";
}
}
else
{
columnValue = "\'" + columnValue + "\'";
}
clauseValues += columnValue + ",";
}
}
#endregion
// now just a hack to remove the final coma from the query
clauseValues = clauseValues.Substring(0, clauseValues.Length - 1);
string commandText = statementPart1 + "SET " + clauseValues + " WHERE HeaderID=\'" + headerGUID + "\' AND ProjectID=\'" + XODBProjectID.ToString() + "\';";
SqlCommand sqc = new SqlCommand(commandText, connection, trans);
string msg = "";
//holeWarningMessages.TryGetValue(headerNameItem, out msg);
msg = "Hole "+headerNameItem + " was overwritten with new data";
holeWarningMessages[headerNameItem] = msg;
numberOfHolesAdded++;
if (commitToDB)
{
sqc.ExecuteNonQuery();
}
tb++;
if (tb == transactionBatchLimit)
{
// commit batch, then renew the transaction
if (commitToDB)
{
trans.Commit();
numCommits++;
// trans = null;
trans = connection.BeginTransaction();
}
// reset counter
tb = 0;
}
ct++;
}
if (tb > 0)
{
if (commitToDB)
{
trans.Commit();
}
numCommits++;
}
mos.recordsUpdated = numberOfHolesAdded;
UpdateStatus("Finished writing collars to database ", 100.0);
}
catch (Exception ex)
{
UpdateStatus("Error writing collars to database ", 0);
mos.AddErrorMessage("Error writing collar data at line " + rejectedLines.Count + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try
{
connection.Close();
secondaryConnection.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
}
internal void AddCoalQualityData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap,
int batchSize, Action<string, double> UpdateStatus, int approxNumLines,
string connectionString, Guid XODBProjectID, bool checkForDuplicates, bool doImportOverwrite)
{
WorkflowProcedureSequenceNumber = 1;
Guid? lastHoleID = new Guid();
string lastStage = "";
decimal lastFromDepth = -999999;
decimal lastToDepth = -999999;
bool commitToDB = true;
DateTime currentUpdateTimestamp = DateTime.UtcNow;
// first set up an assay group object - we can do this through the edm
using (var entityObj = new XODBC(connectionString, null))
{
Guid agGuid = Guid.NewGuid();
AssayGroup ag = new AssayGroup();
ag.AssayGroupID = agGuid;
ag.ProjectID = XODBProjectID;
ag.AssayGroupName = "Manual import";
ag.Comment = "From file " + importMap.mapOriginalDataFile;
ag.Entered = currentUpdateTimestamp;
ag.VersionUpdated = currentUpdateTimestamp;
entityObj.AssayGroups.AddObject(ag);
if (commitToDB)
{
entityObj.SaveChanges();
}
// set up the assay test columns - one of these for each test type
Dictionary<ColumnMap, Guid> resultsColumns = new Dictionary<ColumnMap, Guid>();
Dictionary<Guid, AssayGroupTest> assayGroups = new Dictionary<Guid, AssayGroupTest>();
ColumnMap cmProgram = null;
ColumnMap cmStage = null;
ColumnMap cmSizeFraction = null;
ColumnMap cmWashFraction = null;
foreach (ColumnMap cim in importMap.columnMap)
{
if (cim.targetColumnName.Trim().StartsWith("[RESULT"))
{
// this is a test category
resultsColumns.Add(cim, Guid.NewGuid());
}
else if (cim.targetColumnName.Trim().StartsWith("[PROGRAM"))
{
cmProgram = cim;
}
else if (cim.targetColumnName.Trim().StartsWith("[STAGE"))
{
cmStage = cim;
}
else if (cim.targetColumnName.Trim().StartsWith("[SIZE FRACTION"))
{
cmSizeFraction = cim;
}
else if (cim.targetColumnName.Trim().StartsWith("[WASH FRACTION"))
{
cmWashFraction = cim;
}
}
UpdateStatus("Setting up assay tests ", 2);
foreach (KeyValuePair<ColumnMap, Guid> kvp in resultsColumns)
{
ColumnMap cm = kvp.Key;
Guid g = kvp.Value;
AssayGroupTest xt = new AssayGroupTest();
string ss1 = "";
if (cm.sourceColumnName != null && cm.sourceColumnName.Length > 15)
{
ss1 = cm.sourceColumnName.Substring(0, 16);
}
else
{
ss1 = cm.sourceColumnName;
}
Guid pid = FindParameter("AssayTypeName", cm.sourceColumnName);
xt.ParameterID = pid;
xt.AssayTestName = ss1;
xt.AssayGroupID = agGuid;
xt.AssayGroupTestID = g;
xt.VersionUpdated = currentUpdateTimestamp;
entityObj.AssayGroupTests.AddObject(xt);
assayGroups.Add(g, xt);
if (commitToDB)
{
entityObj.SaveChanges();
}
}
// iterate through the data lines
int ct = 1;
int linesRead = 0;
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
//List<string> uniqueDomains = new List<string>();
// get a connection to the database
try
{
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
bool hasDuplicateIntervals = false;
SqlTransaction trans;
trans = connection.BeginTransaction();
List<SqlCommand> commands = new List<SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
try
{
sr = new StreamReader(fileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input data:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float pct = 0;
float bct = 1;
// report every X records
int repCount = 0;
float fNumLines = (float)approxNumLines;
Dictionary<string, Guid> holeIDLookups = new Dictionary<string, Guid>();
Dictionary<string, int> columnIDX = new Dictionary<string, int>();
int fkLookupCount = 0;
PopulateCMapShortcut("HeaderID", importMap, columnIDX);
PopulateCMapShortcut("FromDepth", importMap, columnIDX);
PopulateCMapShortcut("ToDepth", importMap, columnIDX);
PopulateCMapShortcut("SampleNumber", importMap, columnIDX);
PopulateCMapShortcut("SampleName", importMap, columnIDX);
PopulateCMapShortcut("LabSampleNumber", importMap, columnIDX);
PopulateCMapShortcut("LabBatchNumber", importMap, columnIDX);
ColumnMap headerCmap = importMap.FindItemsByTargetName("HeaderID");
AssayQueries assayQueries = new AssayQueries();
int seqNum = 1;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
repCount++;
pct = ((float)linesRead / (float)approxNumLines) * 100.0f;
bct++;
linesRead++;
if (ct >= importMap.dataStartLine)
{
// digest a row of input data
List<string> items = parseTestLine(line, importMap.inputDelimiter);
Guid holeID = new Guid();
Decimal fromDepth = new Decimal(-9999999999);
Decimal toDepth = new Decimal(-9999999999);
string sampleNumber = null;
string sampleName = null;
string labBatchNumber = null;
string labsampleNumber = null;
// find mapped values by name
int idxVal = 0;
// -- Get the hole ID foreign key relation
bool foundEntry = columnIDX.TryGetValue("HeaderID", out idxVal);
bool foundHole = false;
string holeName = "";
if (foundEntry)
{
string lookupByName = "HoleName";
string lookupValue = items[idxVal];
holeName = lookupValue;
bool lv = holeIDLookups.ContainsKey(lookupValue);
if (!lv)
{
string headerGUID = ForeignKeyUtils.FindFKValueInOther(lookupValue, headerCmap, secondaryConnection, false, lookupByName, XODBProjectID);
if (headerGUID == null)
{
// this means we have not found the specified records in the header table
// Report on issue and skip line
}
else
{
foundHole = true;
holeID = new Guid(headerGUID);
holeIDLookups.Add(lookupValue, holeID);
fkLookupCount++;
}
}
else
{
holeIDLookups.TryGetValue(lookupValue, out holeID);
foundHole = true;
}
}
if (!foundHole)
{
mos.AddErrorMessage("Failed to find hole " + holeName + ". Skipping record at line " + linesRead + ".");
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
mos.recordsFailed++;
continue;
}
else
{
bool hasFrom = false;
idxVal = 0;
foundEntry = columnIDX.TryGetValue("FromDepth", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
fromDepth = val;
hasFrom = true;
}
}
bool hasTo = false;
idxVal = 0;
foundEntry = columnIDX.TryGetValue("ToDepth", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
toDepth = val;
hasTo = true;
}
}
List<Sample> duplicateList = null;
bool isDuplicateInterval = false;
//if (checkForDuplicates)
//{
// if (hasFrom && hasTo)
// {
// // here we need to check that the hole is not duplicated
// duplicateList = assayQueries.CheckForDuplicate(holeID, fromDepth, toDepth);
// if (duplicateList.Count > 0)
// {
// isDuplicateInterval = true;
// }
// }
// if (isDuplicateInterval)
// {
// hasDuplicateIntervals = true;
// mos.AddWarningMessage("Duplicate interval for hole " + holeName + " at depth " + fromDepth + " to " + toDepth);
// UpdateStatus("Duplicate interval at " + holeName + " " + fromDepth + ", " + toDepth, pct);
// if (!doImportOverwrite)
// {
// mos.recordsFailed++;
// continue;
// }
// }
//}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleNumber", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
sampleNumber = ii;
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleName", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
sampleName = ii;
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleNumber", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
labsampleNumber = ii;
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleNumber", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
labBatchNumber = ii;
}
Sample xs = new Sample();
if (isDuplicateInterval == true)
{
xs = duplicateList.First();
}
else
{
xs.SampleID = Guid.NewGuid();
xs.SampleName = sampleName;
xs.SampleNumber = sampleNumber;
xs.FromDepth = fromDepth;
xs.ToDepth = toDepth;
xs.HeaderID = holeID;
xs.VersionUpdated = currentUpdateTimestamp;
entityObj.Samples.AddObject(xs);
}
// Now iddentify the program, Stage, Size fraction and wash fraction
// get the program text
string programType = null;
if (cmProgram != null) {
programType = items[cmProgram.sourceColumnNumber];
}
string stage = null;
if (cmStage != null)
{
stage = items[cmStage.sourceColumnNumber];
}
string sizeFraction = null;
if (cmSizeFraction != null)
{
sizeFraction = items[cmSizeFraction.sourceColumnNumber];
}
string washFraction = null;
if (cmWashFraction != null)
{
washFraction = items[cmWashFraction.sourceColumnNumber];
}
// see if the interfal has changed, wherby we will need to reset the sequence ID
if (holeID != lastHoleID)
{
if (fromDepth != lastFromDepth && toDepth != lastToDepth)
{
// new interval
WorkflowProcedureSequenceNumber = 1;
}
}
if (!stage.Trim().Equals(lastStage))
{
WorkflowProcedureSequenceNumber = 1;
}
lastHoleID = holeID;
lastFromDepth = fromDepth;
lastToDepth = toDepth;
lastStage = stage;
AssayGroupWorkflow agWorkflowProgram = GetAssayGroupWorkflow(entityObj, programType, agGuid);
AssayGroupWorkflowProcedure agWorkflowStage = GetAssayGroupWorkflowProcedure(entityObj, stage, agWorkflowProgram);
AssayGroupSubsample agSS = new AssayGroupSubsample();
agSS.AssayGroupID = agGuid;
agSS.Sequence = seqNum;
agSS.AssayGroupSubsampleID = Guid.NewGuid();
agSS.SampleAntecedentID = xs.SampleID;
agSS.OriginalSample = xs;
agSS.AssayGroupWorkflowProcedureID = agWorkflowStage.AssayGroupWorkflowProcedureID;
agSS.AssayGroupWorkflowProcedure = agWorkflowStage;
entityObj.AssayGroupSubsamples.AddObject(agSS);
entityObj.SaveChanges();
seqNum++;
AssayGroupSubsamplePrecondition agSizeFraction = GetAssayGroupPrecondition(entityObj, sizeFraction, "Size fraction", agSS.AssayGroupSubsampleID);
AssayGroupSubsamplePrecondition agWashFraction = GetAssayGroupPrecondition(entityObj, washFraction, "Wash fraction", agSS.AssayGroupSubsampleID);
// now pick out all the mapped values
// iterate over all [ASSAY RESULT] columns
bool assayUpdated = false;
bool assayAdded = false;
foreach (KeyValuePair<ColumnMap, Guid> kvp in resultsColumns)
{
ColumnMap cm = kvp.Key;
Guid g = kvp.Value; // this is the AssayGroupTestID
AssayGroupTestResult testResult = new AssayGroupTestResult();
testResult.AssayGroupSubsampleID = agSS.AssayGroupSubsampleID;
testResult.AssayGroupTestResultID = Guid.NewGuid();
testResult.AssayGroupTestID = g;
testResult.SampleID = xs.SampleID;
testResult.VersionUpdated = currentUpdateTimestamp;
testResult.LabBatchNumber = labBatchNumber;
//testResult.LabSampleNumber = labsampleNumber;
Decimal result = new Decimal();
if (items.Count >= cm.sourceColumnNumber)
{
bool parsedOK = Decimal.TryParse(items[cm.sourceColumnNumber], out result);
if (parsedOK)
{
testResult.LabResult = result;
}
else
{
testResult.LabResultText = items[cm.sourceColumnNumber];
}
}
else
{
mos.AddWarningMessage("Line " + linesRead + " contains too few columns to read " + cm.sourceColumnName);
}
entityObj.AssayGroupTestResults.AddObject(testResult);
assayAdded = true;
}
if (assayAdded == true)
{
mos.recordsAdded++;
}
if (assayUpdated)
{
mos.recordsUpdated++;
}
tb++;
}
}
if (commitToDB)
{
if (tb == transactionBatchLimit)
{
entityObj.SaveChanges();
UpdateStatus("Writing assays to DB (" + ct + " entries)", pct);
tb = 0;
}
}
ct++;
}
entityObj.SaveChanges();
}
if (hasDuplicateIntervals)
{
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
}
string numFKLookups = "FK lookups " + fkLookupCount;
mos.linesReadFromSource = ct - 1;
UpdateStatus("Finished writing coal quality data to database.", 0);
}
catch (Exception ex)
{
UpdateStatus("Error writing qualities to database ", 0);
mos.AddErrorMessage("Error writing data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try
{
connection.Close();
secondaryConnection.Close();
fileStream.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
mos.linesReadFromSource = linesRead;
}
}
internal void AddSurveyData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap, int batchSize, Action<string, double> UpdateStatus, int approxNumLines, string connectionString, Guid NKDProjectID, bool overwrite, bool checkForDuplicates)
{
bool duplicateFound = false;
// iterate through the data lines
int ct = 1;
int linesRead = 0;
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
Dictionary<Guid, List<string>> rejectedLines = new Dictionary<Guid, List<string>>();
Dictionary<string, string> holeWarningMessages = new Dictionary<string, string>();
using (var entityObj = new NKDC(connectionString, null))
{
//entityObj.Configuration.AutoDetectChangesEnabled = false;
SurveyQueries sq = new SurveyQueries();
// get a connection to the database
try
{
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
int numCommits = 0;
SqlTransaction trans;
//trans = connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
List<SqlCommand> commands = new List<SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
try
{
sr = new StreamReader(fileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input data:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)approxNumLines;
// get the column containing the hole name
Dictionary<string, Guid> holeIDLookups = new Dictionary<string, Guid>();
int numberOfHolesAdded = 0;
ColumnMap headerCmap = importMap.FindItemsByTargetName("HeaderID");
ColumnMap depthCmap = importMap.FindItemsByTargetName("Depth");
float percentComplete = 0;
int headerIDX = headerCmap.sourceColumnNumber;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
repCount++;
percentComplete = ((float)ct / approxNumLines) * 100.0f;
bct++;
linesRead++;
if (ct >= importMap.dataStartLine)
{
string statementPart1 = "INSERT INTO " + importMap.mapTargetPrimaryTable + " ";
string clauseValues = "";
string clauseParameters = "";
List<string> items = parseTestLine(line, importMap.inputDelimiter);
// using the column map, pick out the hole name field and see if it is in the database already
string headerNameItem = items[headerIDX];
// check if this holename is a duplicate in the file
bool foundHole = false;
Guid holeID = new Guid();
bool lv = holeIDLookups.ContainsKey(headerNameItem);
if (!lv)
{
string headerGUID = ForeignKeyUtils.FindFKValueInOther(headerNameItem, headerCmap, secondaryConnection, false, "HoleName", NKDProjectID);
if (headerGUID == null)
{
// this means we have not found the specified records in the header table
// Report on issue and skip line
}
else
{
foundHole = true;
holeID = new Guid(headerGUID);
holeIDLookups.Add(headerNameItem, holeID);
}
}
else
{
holeIDLookups.TryGetValue(headerNameItem, out holeID);
foundHole = true;
}
if (!foundHole)
{
mos.AddWarningMessage("Failed to find hole " + headerNameItem + ". Skipping record at line " + linesRead + ".");
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
mos.recordsFailed++;
continue;
}
if (checkForDuplicates == true && depthCmap != null)
{
// check for duplicate depths
string d = items[depthCmap.sourceColumnNumber];
decimal dt = 0;
bool isParsed = decimal.TryParse(d, out dt);
if (isParsed)
{
List<Guid> rr = sq.CheckForDuplicate(holeID, dt, secondaryConnection);
//List<Guid> rr = sq.CheckForDuplicate(holeID, dt, entityObj);
if (rr.Count > 0)
{
duplicateFound = true;
if (!rejectedLines.ContainsKey(rr.First()))
{
rejectedLines.Add(rr.First(), items);
mos.AddWarningMessage("Duplicate depth found in survey data for hole " + headerNameItem + " at depth " + d + " on line " + linesRead);
UpdateStatus("Duplicate depth found in survey data for hole " + headerNameItem + " at depth " + d, percentComplete);
}
else
{
mos.AddWarningMessage("Duplicate depth found in survey data file for hole " + headerNameItem + " at depth " + d + " on line " + linesRead);
UpdateStatus("Duplicate depth found in survey data file for hole " + headerNameItem + " at depth " + d, percentComplete);
rejectedLines[rr.First()] = items;
}
if (!overwrite)
{
mos.recordsFailed++;
}
continue;
}
}
}
#region mappsearch
// now pick out all the mapped values
foreach (ColumnMap cmap in importMap.columnMap)
{
if (cmap.targetColumnName.Trim().Equals("HeaderID"))
{
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
clauseParameters += "\'" + holeID.ToString() + "\',";
}
else
{
bool isFKColumn = cmap.hasFKRelation;
int colID = cmap.sourceColumnNumber;
string columnValue = cmap.defaultValue;
if (colID >= 0)
{
columnValue = items[colID];
}
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
if (isFKColumn)
{
// go and search for the appropriate value from the foreign key table
string newValue = ForeignKeyUtils.FindFKValueInDictionary(columnValue, cmap, secondaryConnection, true);
columnValue = newValue;
if (newValue != null && newValue.Trim().Length > 0)
{
clauseParameters += "\'" + columnValue + "\',";
}
else
{
clauseParameters += "NULL,";
}
}
else
{
if (cmap.importDataType.Equals(ImportDataMap.NUMERICDATATYPE))
{
if (columnValue.Equals("-") || columnValue.Equals(""))
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
else
{
columnValue = "NULL";
}
}
clauseParameters += columnValue + ",";
}
else
{
//if (columnValue.Equals("-"))
//{
// if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
// {
// columnValue = cmap.defaultValue;
// }
//}
clauseParameters += "\'" + columnValue + "\',";
}
}
}
}
#endregion
// now just a hack to remove the final coma from the query
clauseParameters = clauseParameters.Substring(0, clauseParameters.Length - 1);
clauseValues = clauseValues.Substring(0, clauseValues.Length - 1);
string commandText = statementPart1 + "(" + clauseValues + ") VALUES (" + clauseParameters + ")";
//SqlCommand sqc = new SqlCommand(commandText, connection, trans);
SqlCommand sqc = new SqlCommand(commandText, connection);
numberOfHolesAdded++;
if (commitToDB)
{
try
{
sqc.ExecuteNonQuery();
}
catch (Exception ex)
{
mos.AddErrorMessage("Failed to insert items on line " + linesRead + ".");
UpdateStatus("Failed to insert items on line " + linesRead + ".", percentComplete);
mos.recordsFailed++;
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
UpdateStatus("Updating from line " + linesRead, percentComplete);
tb++;
//if (tb == transactionBatchLimit)
//{
// // commit batch, then renew the transaction
// if (commitToDB)
// {
// trans.Commit();
// numCommits++;
// // trans = null;
// trans = connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
// }
// // reset counter
// tb = 0;
//}
}
ct++;
}
}
if (tb > 0)
{
//if (commitToDB)
//{
// trans.Commit();
//}
numCommits++;
}
mos.recordsAdded = numberOfHolesAdded;
UpdateStatus("Finished writing records to database ", 100.0);
}
catch (Exception ex)
{
UpdateStatus("Error writing records to database ", 0);
mos.AddErrorMessage("Error writing records data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try
{
connection.Close();
secondaryConnection.Close();
fileStream.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
if (duplicateFound == true && overwrite == true)
{
OverwriteSurveyRecord(mos, rejectedLines, importMap, connectionString, NKDProjectID, UpdateStatus, holeWarningMessages);
}
foreach (KeyValuePair<string, string> kvp in holeWarningMessages)
{
string v = kvp.Value;
mos.AddWarningMessage(v);
}
mos.linesReadFromSource = linesRead;
}
}
/// <summary>
/// Add block data to the model, provided by the supplied file stream and format defintion
/// </summary>
/// <param name="bmFileStream"></param>
/// <param name="importMap"></param>
/// <param name="blockModelGUID"></param>
/// <param name="batchSize"></param>
/// <param name="UpdateStatus"></param>
/// <param name="approxNumLines"></param>
internal List<string> AddBlockData(ModelImportStatus mos, Stream bmFileStream, ImportDataMap importMap, Guid blockModelGUID, int batchSize, Action<string, double> UpdateStatus, int numLines, string connString)
{
// iterate through the data lines
int ct = 1;
int linesRead = 0;
int total = 0;
SqlConnection connection = null;
List<string> uniqueDomains = new List<string>();
// get a connection to the database
try
{
int domainColIDX = -1;
// find the column ID for the specified Domain field, as we need to capture this list.
foreach (ColumnMap cm in importMap.columnMap) {
if (cm.targetColumnName.Trim().Equals("Domain")) {
domainColIDX = cm.sourceColumnNumber;
}
}
connection = new SqlConnection(connString);
connection.Open();
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
List<SqlCommand> commands = new List<SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
//FileStream fs = null;
try
{
//fs = new FileStream(textInputDataFile, FileMode.Open, FileAccess.Read, FileShare.Read);
sr = new StreamReader(bmFileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input model:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float pct = 0;
float bct = 1;
// report every X blocks
int repCount = 0;
int reportOnBlock = 500;
float fNumLines = (float)numLines;
bool commitToDB = true;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
linesRead++;
repCount++;
if (repCount == reportOnBlock)
{
repCount = 0;
// now report status
pct = (bct / fNumLines) * 100.0f;
UpdateStatus("Writing block " + bct + " to database", pct);
}
bct++;
if (ct >= importMap.dataStartLine)
{
string statementPart1 = "INSERT INTO " + importMap.mapTargetPrimaryTable + " ";
string clauseValues = "";
string clauseParameters = "";
List<string> items = parseTestLine(line, importMap.inputDelimiter);
// now pick out all the mapped values
foreach (ColumnMap cmap in importMap.columnMap)
{
int colID = cmap.sourceColumnNumber;
string columnValue = cmap.defaultValue;
if (colID >= 0)
{
columnValue = items[colID];
}
if (cmap.sourceColumnNumber == domainColIDX) {
if (!uniqueDomains.Contains(columnValue.Trim())) {
uniqueDomains.Add(columnValue);
}
}
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
if (cmap.importDataType.Equals(ImportDataMap.NUMERICDATATYPE))
{
if (columnValue.Equals("-"))
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
else
{
columnValue = "NULL";
}
}
clauseParameters += columnValue + ",";
}
else
{
clauseParameters += "\'" + columnValue + "\',";
}
}
// now just a hack to remove the final coma from the query
clauseParameters = clauseParameters.Substring(0, clauseParameters.Length - 1);
clauseValues = clauseValues.Substring(0, clauseValues.Length - 1);
string commandText = statementPart1 + "(" + clauseValues + ") VALUES (" + clauseParameters + ")";
SqlCommand sqc = new SqlCommand(commandText, connection, trans);
if (commitToDB)
{
sqc.ExecuteNonQuery();
}
tb++;
if (tb == transactionBatchLimit)
{
// commit batch, then renew the transaction
if (commitToDB)
{
total += tb;
trans.Commit();
numCommits++;
// trans = null;
trans = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
}
// reset counter
tb = 0;
}
}
ct++;
}
}
if (tb > 0)
{
if (commitToDB)
{
total += tb;
trans.Commit();
}
numCommits++;
}
UpdateStatus("Finished writing blocks to database ", 100.0);
}
catch (Exception ex)
{
UpdateStatus("Error writing blocks to database ", 0);
mos.AddErrorMessage("Error writing block data at line "+linesRead+":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try { connection.Close(); }
catch (Exception ex) {
mos.AddErrorMessage("Error closing conenction to database:\n"+ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
mos.RecordsImported = total;
mos.linesReadFromSource = linesRead;
return uniqueDomains;
}
internal void AddAssayData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap,
int batchSize, Action <string, double> UpdateStatus, int approxNumLines,
string connectionString, Guid NKDProjectID, bool checkForDuplicates, bool doImportOverwrite)
{
bool commitToDB = true;
DateTime currentUpdateTimestamp = DateTime.UtcNow;
// first set up an assay group object - we can do this through the edm
using (var entityObj = new NKDC(connectionString, null))
{
//entityObj.Configuration.AutoDetectChangesEnabled = false;
Guid agGuid = Guid.NewGuid();
AssayGroup ag = new AssayGroup();
ag.AssayGroupID = agGuid;
ag.ProjectID = NKDProjectID;
ag.AssayGroupName = "Manual import";
ag.Comment = "From file " + importMap.mapOriginalDataFile;
ag.Entered = currentUpdateTimestamp;
ag.VersionUpdated = currentUpdateTimestamp;
entityObj.AssayGroups.AddObject(ag);
if (commitToDB)
{
entityObj.SaveChanges();
}
// set up the assay test columns - one of these for each test type
Dictionary <ColumnMap, Guid> resultsColumns = new Dictionary <ColumnMap, Guid>();
Dictionary <Guid, AssayGroupTest> assayGroups = new Dictionary <Guid, AssayGroupTest>();
foreach (ColumnMap cim in importMap.columnMap)
{
if (cim.targetColumnName.Trim().StartsWith("[ASSAY"))
{
// this is a test category
resultsColumns.Add(cim, Guid.NewGuid());
}
}
UpdateStatus("Setting up assay tests ", 2);
foreach (KeyValuePair <ColumnMap, Guid> kvp in resultsColumns)
{
ColumnMap cm = kvp.Key;
Guid g = kvp.Value;
AssayGroupTest xt = new AssayGroupTest();
string ss1 = "";
if (cm.sourceColumnName != null && cm.sourceColumnName.Length > 15)
{
ss1 = cm.sourceColumnName.Substring(0, 16);
}
else
{
ss1 = cm.sourceColumnName;
}
Guid pid = FindParameterForAssayTypeName(cm.sourceColumnName);
xt.ParameterID = pid;
xt.AssayTestName = ss1;
xt.AssayGroupID = agGuid;
xt.AssayGroupTestID = g;
xt.VersionUpdated = currentUpdateTimestamp;
entityObj.AssayGroupTests.AddObject(xt);
assayGroups.Add(g, xt);
if (commitToDB)
{
entityObj.SaveChanges();
}
}
// iterate through the data lines
int ct = 1;
int linesRead = 0;
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
//List<string> uniqueDomains = new List<string>();
// get a connection to the database
string line = null;
try
{
int domainColIDX = -1;
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
bool hasDuplicateIntervals = false;
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction();
List <SqlCommand> commands = new List <SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
FileStream fs = null;
try
{
//fs = new FileStream(textInputDataFile, FileMode.Open, FileAccess.Read, FileShare.Read);
sr = new StreamReader(fileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input data:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
line = null;
float pct = 0;
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)approxNumLines;
Dictionary <string, Guid> holeIDLookups = new Dictionary <string, Guid>();
Dictionary <string, int> columnIDX = new Dictionary <string, int>();
int fkLookupCount = 0;
BaseImportTools.PopulateCMapShortcut(importMap, columnIDX);
ColumnMap headerCmap = importMap.FindItemsByTargetName("HeaderID");
AssayQueries assayQueries = new AssayQueries();
List <string> items = new List <string>();
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
repCount++;
pct = ((float)linesRead / (float)approxNumLines) * 100.0f;
bct++;
linesRead++;
if (ct >= importMap.dataStartLine)
{
var append = BaseImportTools.ParseTestLine(line, importMap.inputDelimiter);
if (items.Count == 0 || append.Count == importMap.MaxColumns)
{
items = append;
}
else if (items.Count < importMap.MaxColumns)
{
items[items.Count - 1] = items[items.Count - 1] + append[0];
items.AddRange(append.Skip(1));
}
if (items.Count < importMap.MaxColumns)
{
mos.AddWarningMessage(string.Format("Bad CSV file, attempted to join....{0}", linesRead));
continue;
}
else if (items.Count > importMap.MaxColumns)
{
mos.AddWarningMessage(string.Format("FAILED! Line {0}. Bad CSV file, attempted to join.", linesRead));
items.Clear();
continue;
}
// digest a row of input data
Guid holeID = new Guid();
Decimal?fromDepth = null;
Decimal?toDepth = null;
string sampleNumber = null;
string labBatchNumber = null;
string labsampleNumber = null;
Decimal?sampleMassKg = null;
Decimal?dryMassKg = null;
string standardSampleTypeName = null;
// find mapped values by name
//ColumnMap cmap = importMap.FindItemsByTargetName("HeaderID");
int idxVal = 0;
bool foundEntry = columnIDX.TryGetValue("HeaderID", out idxVal);
bool foundHole = false;
string holeName = "";
if (foundEntry)
{
string lookupByName = "HoleName";
string lookupValue = items[idxVal];
holeName = lookupValue;
bool lv = holeIDLookups.ContainsKey(lookupValue);
if (!lv)
{
string headerGUID = ForeignKeyUtils.FindFKValueInOther(lookupValue, headerCmap, secondaryConnection, false, lookupByName, NKDProjectID);
if (headerGUID == null)
{
// this means we have not found the specified records in the header table
// Report on issue and skip line
}
else
{
foundHole = true;
holeID = new Guid(headerGUID);
holeIDLookups.Add(lookupValue, holeID);
fkLookupCount++;
}
}
else
{
holeIDLookups.TryGetValue(lookupValue, out holeID);
foundHole = true;
}
}
if (!foundHole)
{
mos.AddErrorMessage("Failed to find hole " + holeName + ". Skipping record at line " + linesRead + ".");
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
mos.recordsFailed++;
continue;
}
else
{
bool hasFrom = false;
idxVal = 0;
foundEntry = columnIDX.TryGetValue("FromDepth", out idxVal);
if (foundEntry)
//cmap = importMap.FindItemsByTargetName();
//if (cmap != null)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
fromDepth = val;
hasFrom = true;
}
}
bool hasTo = false;
idxVal = 0;
foundEntry = columnIDX.TryGetValue("ToDepth", out idxVal);
if (foundEntry)
//cmap = importMap.FindItemsByTargetName("ToDepth");
//if (cmap != null)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
toDepth = val;
hasTo = true;
}
}
List <Sample> duplicateList = null;
bool isDuplicateInterval = false;
if (checkForDuplicates)
{
if (hasFrom && hasTo)
{
// here we need to check that the hole is not duplicated
duplicateList = assayQueries.CheckForDuplicate(holeID, fromDepth, toDepth);
if (duplicateList.Count > 0)
{
isDuplicateInterval = true;
}
}
if (isDuplicateInterval)
{
hasDuplicateIntervals = true;
mos.AddWarningMessage("Duplicate interval for hole " + holeName + " at depth " + fromDepth + " to " + toDepth);
UpdateStatus("Duplicate interval at " + holeName + " " + fromDepth + ", " + toDepth, pct);
if (!doImportOverwrite)
{
mos.recordsFailed++;
continue;
}
}
}
//cmap = importMap.FindItemsByTargetName("SampleNumber");
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleNumber", out idxVal);
if (foundEntry)
// if (cmap != null)
{
string ii = items[idxVal];
sampleNumber = ii;
}
//cmap = importMap.FindItemsByTargetName("LabSampleNumber");
//if (cmap != null)
idxVal = 0;
foundEntry = columnIDX.TryGetValue("LabSampleName", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
labsampleNumber = ii;
}
//cmap = importMap.FindItemsByTargetName("LabBatchNumber");
idxVal = 0;
foundEntry = columnIDX.TryGetValue("LabBatchNumber", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
labBatchNumber = ii;
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleMassKg", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
sampleMassKg = val;
}
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("DryMassKg", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
dryMassKg = val;
}
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("StandardSampleTypeName", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
standardSampleTypeName = ii;
}
Sample xs = new Sample();
if (isDuplicateInterval == true)
{
xs = duplicateList.First();
}
else
{
xs.SampleID = Guid.NewGuid();
xs.FromDepth = fromDepth;
xs.ToDepth = toDepth;
xs.HeaderID = holeID;
xs.VersionUpdated = currentUpdateTimestamp;
xs.SampleNumber = sampleNumber;
xs.SampleMassKg = sampleMassKg;
if (!string.IsNullOrWhiteSpace(standardSampleTypeName))
{
var t = entityObj.DictionarySampleTypes.Select(f => new { f.SampleTypeID, f.StandardSampleTypeName }).FirstOrDefault(f => f.StandardSampleTypeName == standardSampleTypeName);
if (t != null)
{
xs.SampleTypeID = t.SampleTypeID;
}
}
xs.DryMassKg = dryMassKg;
}
// now pick out all the mapped values
// iterate over all [ASSAY RESULT] columns
bool assayUpdated = false;
bool assayAdded = false;
var results = new List <AssayGroupTestResult>();
foreach (KeyValuePair <ColumnMap, Guid> kvp in resultsColumns)
{
ColumnMap cm = kvp.Key;
Guid g = kvp.Value; // this is the AssayGroupTestID
AssayGroupTestResult testResult = new AssayGroupTestResult();
/*bool assayResFound = false;
*
* if (isDuplicateInterval)
* {
* List<AssayGroupTestResult> testResults = assayQueries.GetDuplicateResult(xs.SampleID, cm.sourceColumnName);
* if (testResults.Count > 0)
* {
* testResult = testResults.First();
* assayResFound = true;
* }
* }*/
//if(!assayResFound)
// {
testResult.AssayGroupTestResultID = Guid.NewGuid();
testResult.AssayGroupTestID = g;
testResult.SampleID = xs.SampleID;
testResult.VersionUpdated = currentUpdateTimestamp;
//}
testResult.LabBatchNumber = labBatchNumber;
testResult.LabSampleName = labsampleNumber;
Decimal result = new Decimal();
if (items.Count >= cm.sourceColumnNumber)
{
bool parsedOK = Decimal.TryParse(items[cm.sourceColumnNumber], out result);
if (parsedOK)
{
testResult.LabResult = result;
}
testResult.LabResultText = items[cm.sourceColumnNumber];
}
else
{
mos.AddWarningMessage("Line " + linesRead + " contains too few columns to read " + cm.sourceColumnName);
}
results.Add(testResult);
//if (isDuplicateInterval == false)
//{
assayAdded = true;
//}else{
// if (!assayResFound)
// {
// entityObj.AssayGroupTestResult.Add(testResult);
// assayAdded = true;
// }
// else {
// assayUpdated = true;
// }
//}
}
var resultsToSave = (from o in results where !string.IsNullOrWhiteSpace(o.LabResultText) select o);
if (!resultsToSave.Any())
{
continue;
}
if (!isDuplicateInterval)
{
entityObj.Samples.AddObject(xs);
}
foreach (var save in resultsToSave)
{
entityObj.AssayGroupTestResults.AddObject(save);
}
if (assayAdded == true)
{
mos.recordsAdded++;
}
if (assayUpdated)
{
mos.recordsUpdated++;
}
tb++;
}
}
if (commitToDB)
{
if (tb == transactionBatchLimit)
{
entityObj.SaveChanges();
UpdateStatus("Writing assays to DB (" + ct + " entries)", pct);
tb = 0;
}
}
ct++;
//Console.WriteLine("Processing line "+ct);
items.Clear();
}
entityObj.SaveChanges();
}
if (hasDuplicateIntervals)
{
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
}
string numFKLookups = "FK lookups " + fkLookupCount;
mos.linesReadFromSource = ct - 1;
UpdateStatus("Finished writing assays to database.", 0);
}
catch (Exception ex)
{
UpdateStatus("Error writing assays to database ", 0);
mos.AddErrorMessage("Error writing assay data at line " + linesRead + ":\n" + line + "\n\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try
{
connection.Close();
secondaryConnection.Close();
fileStream.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing connection to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
mos.linesReadFromSource = linesRead;
}
}
internal void AddCoalQualityData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap,
int batchSize, Action <string, double> UpdateStatus, int approxNumLines,
string connectionString, Guid NKDProjectID, bool checkForDuplicates, bool doImportOverwrite)
{
WorkflowProcedureSequenceNumber = 1;
Guid? lastHoleID = new Guid();
string lastStage = "";
decimal lastFromDepth = -999999;
decimal lastToDepth = -999999;
bool commitToDB = true;
DateTime currentUpdateTimestamp = DateTime.UtcNow;
// first set up an assay group object - we can do this through the edm
using (var entityObj = new NKDC(connectionString, null))
{
Guid agGuid = Guid.NewGuid();
AssayGroup ag = new AssayGroup();
ag.AssayGroupID = agGuid;
ag.ProjectID = NKDProjectID;
ag.AssayGroupName = "Manual import";
ag.Comment = "From file " + importMap.mapOriginalDataFile;
ag.Entered = currentUpdateTimestamp;
ag.VersionUpdated = currentUpdateTimestamp;
entityObj.AssayGroups.AddObject(ag);
if (commitToDB)
{
entityObj.SaveChanges();
}
// set up the assay test columns - one of these for each test type
Dictionary <ColumnMap, Guid> resultsColumns = new Dictionary <ColumnMap, Guid>();
Dictionary <Guid, AssayGroupTest> assayGroups = new Dictionary <Guid, AssayGroupTest>();
ColumnMap cmProgram = null;
ColumnMap cmStage = null;
ColumnMap cmSizeFraction = null;
ColumnMap cmWashFraction = null;
foreach (ColumnMap cim in importMap.columnMap)
{
if (cim.targetColumnName.Trim().StartsWith("[RESULT"))
{
// this is a test category
resultsColumns.Add(cim, Guid.NewGuid());
}
else if (cim.targetColumnName.Trim().StartsWith("[PROGRAM"))
{
cmProgram = cim;
}
else if (cim.targetColumnName.Trim().StartsWith("[STAGE"))
{
cmStage = cim;
}
else if (cim.targetColumnName.Trim().StartsWith("[SIZE FRACTION"))
{
cmSizeFraction = cim;
}
else if (cim.targetColumnName.Trim().StartsWith("[WASH FRACTION"))
{
cmWashFraction = cim;
}
}
UpdateStatus("Setting up assay tests ", 2);
foreach (KeyValuePair <ColumnMap, Guid> kvp in resultsColumns)
{
ColumnMap cm = kvp.Key;
Guid g = kvp.Value;
AssayGroupTest xt = new AssayGroupTest();
string ss1 = "";
if (cm.sourceColumnName != null && cm.sourceColumnName.Length > 15)
{
ss1 = cm.sourceColumnName.Substring(0, 16);
}
else
{
ss1 = cm.sourceColumnName;
}
Guid pid = FindParameter("AssayTypeName", cm.sourceColumnName);
xt.ParameterID = pid;
xt.AssayTestName = ss1;
xt.AssayGroupID = agGuid;
xt.AssayGroupTestID = g;
xt.VersionUpdated = currentUpdateTimestamp;
entityObj.AssayGroupTests.AddObject(xt);
assayGroups.Add(g, xt);
if (commitToDB)
{
entityObj.SaveChanges();
}
}
// iterate through the data lines
int ct = 1;
int linesRead = 0;
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
//List<string> uniqueDomains = new List<string>();
// get a connection to the database
try
{
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
bool hasDuplicateIntervals = false;
SqlTransaction trans;
trans = connection.BeginTransaction();
List <SqlCommand> commands = new List <SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
try
{
sr = new StreamReader(fileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input data:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float pct = 0;
float bct = 1;
// report every X records
int repCount = 0;
float fNumLines = (float)approxNumLines;
Dictionary <string, Guid> holeIDLookups = new Dictionary <string, Guid>();
Dictionary <string, int> columnIDX = new Dictionary <string, int>();
int fkLookupCount = 0;
BaseImportTools.PopulateCMapShortcut(importMap, columnIDX);
ColumnMap headerCmap = importMap.FindItemsByTargetName("HeaderID");
int seqNum = 1;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
repCount++;
pct = ((float)linesRead / (float)approxNumLines) * 100.0f;
bct++;
linesRead++;
if (ct >= importMap.dataStartLine)
{
// digest a row of input data
List <string> items = BaseImportTools.ParseTestLine(line, importMap.inputDelimiter);
Guid holeID = new Guid();
Decimal fromDepth = new Decimal(-9999999999);
Decimal toDepth = new Decimal(-9999999999);
string sampleNumber = null;
string sampleName = null;
string labBatchNumber = null;
string labsampleNumber = null;
// find mapped values by name
int idxVal = 0;
// -- Get the hole ID foreign key relation
bool foundEntry = columnIDX.TryGetValue("HeaderID", out idxVal);
bool foundHole = false;
string holeName = "";
if (foundEntry)
{
string lookupByName = "HoleName";
string lookupValue = items[idxVal];
holeName = lookupValue;
bool lv = holeIDLookups.ContainsKey(lookupValue);
if (!lv)
{
string headerGUID = ForeignKeyUtils.FindFKValueInOther(lookupValue, headerCmap, secondaryConnection, false, lookupByName, NKDProjectID);
if (headerGUID == null)
{
// this means we have not found the specified records in the header table
// Report on issue and skip line
}
else
{
foundHole = true;
holeID = new Guid(headerGUID);
holeIDLookups.Add(lookupValue, holeID);
fkLookupCount++;
}
}
else
{
holeIDLookups.TryGetValue(lookupValue, out holeID);
foundHole = true;
}
}
if (!foundHole)
{
mos.AddErrorMessage("Failed to find hole " + holeName + ". Skipping record at line " + linesRead + ".");
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
mos.recordsFailed++;
continue;
}
else
{
bool hasFrom = false;
idxVal = 0;
foundEntry = columnIDX.TryGetValue("FromDepth", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
fromDepth = val;
hasFrom = true;
}
}
bool hasTo = false;
idxVal = 0;
foundEntry = columnIDX.TryGetValue("ToDepth", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
toDepth = val;
hasTo = true;
}
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleID", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
sampleNumber = ii;
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleName", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
sampleName = ii;
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("LabSampleName", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
labsampleNumber = ii;
}
idxVal = 0;
foundEntry = columnIDX.TryGetValue("LabBatchNumber", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
labBatchNumber = ii;
}
// Now iddentify the program, Stage, Size fraction and wash fraction
// get the program text
string programType = null;
if (cmProgram != null)
{
programType = items[cmProgram.sourceColumnNumber];
}
string stage = null;
if (cmStage != null)
{
stage = items[cmStage.sourceColumnNumber];
}
string sizeFraction = null;
if (cmSizeFraction != null)
{
sizeFraction = items[cmSizeFraction.sourceColumnNumber];
}
string washFraction = null;
if (cmWashFraction != null)
{
washFraction = items[cmWashFraction.sourceColumnNumber];
}
IQueryable <AssayGroupSubsample> toUpdate = null;
bool isDuplicate = false;
var washID = (from o in entityObj.Parameters where o.ParameterType == "AssayPrecondition" && o.ParameterName == "Wash fraction" select o.ParameterID).FirstOrDefault();
var sizeID = (from o in entityObj.Parameters where o.ParameterType == "AssayPrecondition" && o.ParameterName == "Size fraction" select o.ParameterID).FirstOrDefault();
if (checkForDuplicates)
{
if (hasFrom && hasTo)
{
// here we need to check that not duplicated
toUpdate =
(from o in entityObj.AssayGroupSubsamples
where
o.OriginalSample.HeaderID == holeID &&
o.OriginalSample.FromDepth == fromDepth &&
o.OriginalSample.ToDepth == toDepth &&
o.AssayGroupWorkflowProcedure.WorkflowStateName == stage &&
o.AssayGroupWorkflowProcedure.AssayGroupWorkflow.WorkflowName == programType &&
(sizeFraction.Trim() == "" || o.AssayGroupSubsamplePrecondition.Any(f => f.PreconditionName == sizeFraction && f.PreconditionParameterID == sizeID)) &&
(washFraction.Trim() == "" || o.AssayGroupSubsamplePrecondition.Any(f => f.PreconditionName == washFraction && f.PreconditionParameterID == washID))
select o);
if (toUpdate.Any())
{
isDuplicate = true;
}
}
if (isDuplicate)
{
hasDuplicateIntervals = true;
mos.AddWarningMessage("Duplicate interval for hole " + holeName + " at depth " + fromDepth + " to " + toDepth);
UpdateStatus("Duplicate interval at " + holeName + " " + fromDepth + ", " + toDepth, pct);
if (!doImportOverwrite)
{
mos.recordsFailed++;
continue;
}
else
{
foreach (var upd in toUpdate)
{
upd.Sequence = seqNum;
}
}
}
}
Sample xs = null;
if (isDuplicate == true)
{
xs = toUpdate.First().OriginalSample;
}
else
{
xs = (from o in entityObj.Samples where o.HeaderID == holeID && o.FromDepth == fromDepth && o.ToDepth == toDepth select o).FirstOrDefault();
if (xs == null)
{
xs = new Sample();
xs.SampleID = Guid.NewGuid();
xs.SampleName = sampleName;
xs.SampleNumber = sampleNumber;
xs.FromDepth = fromDepth;
xs.ToDepth = toDepth;
xs.HeaderID = holeID;
xs.VersionUpdated = currentUpdateTimestamp;
entityObj.Samples.AddObject(xs);
}
}
// see if the interfal has changed, wherby we will need to reset the sequence ID
if (holeID != lastHoleID)
{
if (fromDepth != lastFromDepth && toDepth != lastToDepth)
{
// new interval
WorkflowProcedureSequenceNumber = 1;
}
}
if (!stage.Trim().Equals(lastStage))
{
WorkflowProcedureSequenceNumber = 1;
}
lastHoleID = holeID;
lastFromDepth = fromDepth;
lastToDepth = toDepth;
lastStage = stage;
if (!isDuplicate)
{
AssayGroupWorkflow agWorkflowProgram = GetAssayGroupWorkflow(entityObj, programType, agGuid);
AssayGroupWorkflowProcedure agWorkflowStage = GetAssayGroupWorkflowProcedure(entityObj, stage, agWorkflowProgram);
AssayGroupSubsample agSS = new AssayGroupSubsample();
agSS.AssayGroupID = agGuid;
agSS.FromDepth = fromDepth;
agSS.ToDepth = toDepth;
agSS.Sequence = seqNum;
agSS.AssayGroupSubsampleID = Guid.NewGuid();
agSS.SampleAntecedentID = xs.SampleID;
agSS.OriginalSample = xs;
agSS.AssayGroupWorkflowProcedureID = agWorkflowStage.AssayGroupWorkflowProcedureID;
agSS.AssayGroupWorkflowProcedure = agWorkflowStage;
entityObj.AssayGroupSubsamples.AddObject(agSS);
entityObj.SaveChanges();
AssayGroupSubsamplePrecondition agSizeFraction = GetAssayGroupPrecondition(entityObj, sizeFraction, "Size fraction", agSS.AssayGroupSubsampleID);
AssayGroupSubsamplePrecondition agWashFraction = GetAssayGroupPrecondition(entityObj, washFraction, "Wash fraction", agSS.AssayGroupSubsampleID);
toUpdate = (new[] { agSS }).AsQueryable();
}
if (isDuplicate)
{
entityObj.SaveChanges();
}
foreach (var upd in toUpdate.ToList())
{
// now pick out all the mapped values
// iterate over all [ASSAY RESULT] columns
foreach (KeyValuePair <ColumnMap, Guid> kvp in resultsColumns)
{
ColumnMap cm = kvp.Key;
Guid g = kvp.Value; // this is the AssayGroupTestID
AssayGroupTestResult testResult = null;
Decimal result = default(decimal);
string resultText = null;
bool parsedOK = false;
if (items.Count >= cm.sourceColumnNumber)
{
parsedOK = Decimal.TryParse(items[cm.sourceColumnNumber], out result);
resultText = items[cm.sourceColumnNumber];
}
else
{
mos.AddWarningMessage("Line " + linesRead + " contains too few columns to read " + cm.sourceColumnName);
}
if (string.IsNullOrWhiteSpace(resultText))
{
continue;
}
if (!isDuplicate)
{
testResult = new AssayGroupTestResult();
testResult.AssayGroupSubsampleID = upd.AssayGroupSubsampleID;
testResult.AssayGroupTestResultID = Guid.NewGuid();
testResult.AssayGroupTestID = g;
testResult.SampleID = xs.SampleID;
testResult.LabBatchNumber = labBatchNumber;
entityObj.AssayGroupTestResults.AddObject(testResult);
testResult.VersionUpdated = currentUpdateTimestamp;
if (parsedOK)
{
testResult.LabResult = result;
}
testResult.LabResultText = resultText;
//testResult.LabSampleNumber = labsampleNumber;
mos.recordsAdded++;
}
else
{
var tempRes = (parsedOK) ? result : default(decimal?);
entityObj.AssayGroupTestResults.Where(f =>
f.AssayGroupSubsampleID == upd.AssayGroupSubsampleID &&
f.AssayGroupTest.Parameter.ParameterName == cm.sourceColumnName
)
.Update((f) => new AssayGroupTestResult
{
LabResult = tempRes,
LabResultText = resultText,
VersionUpdated = currentUpdateTimestamp
});
mos.recordsUpdated++;
}
}
}
seqNum++;
tb++;
}
}
if (commitToDB)
{
if (tb == transactionBatchLimit)
{
entityObj.SaveChanges();
UpdateStatus("Writing assays to DB (" + ct + " entries)", pct);
tb = 0;
}
}
ct++;
}
entityObj.SaveChanges();
}
if (hasDuplicateIntervals)
{
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
}
string numFKLookups = "FK lookups " + fkLookupCount;
mos.linesReadFromSource = ct - 1;
UpdateStatus("Finished writing coal quality data to database.", 0);
}
catch (Exception ex)
{
UpdateStatus("Error writing qualities to database ", 0);
mos.AddErrorMessage("Error writing data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try
{
connection.Close();
secondaryConnection.Close();
fileStream.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
mos.linesReadFromSource = linesRead;
}
}
/// <summary>
/// Import the block model. This is ultimatly carried out throught he import library which must be
/// passed the streams for the import file (and defintiion possibly).
/// </summary>
/// <param name="bmFileName"></param>
/// <param name="formatFileName"></param>
/// <param name="projectID"></param>
/// <param name="alias"></param>
/// <param name="authorGuid"></param>
/// <param name="res2"></param>
/// <param name="domains"></param>
/// <param name="targetFolder"></param>
/// <param name="attmemptModelLoad"></param>
/// <param name="notes"></param>
/// <param name="stage"></param>
/// <returns></returns>
private ModelImportStatus DoNewModelImport(string bmFileName, string formatFileName, string projectID, string alias, Guid authorGuid, ref List <string> domains, string targetFolder, bool attmemptModelLoad, string notes, string stage, Guid stageMetaID)
{
ModelImportStatus mos = new ModelImportStatus();
mos.finalErrorCode = ModelImportStatus.OK;
double _originX = -1;
double _originY = -1;
double _originZ = -1;
BaseImportTools bit = new BaseImportTools();
if (attmemptModelLoad)
{
//IStorageFile formatFile = _storageProvider.GetFile(formatFileName);
IStorageFile bmFile = _storageProvider.GetFile(bmFileName);
Stream bmFileStream = bmFile.OpenRead();
Stream bmTempFileStream = bmFile.OpenRead();
// try and automatically detect the origin coordinates from the file itself - a datamine file
// always carries the origin coords in XMORIG, YMORIG, ZMORIG
NKD.Import.FormatSpecification.ImportDataMap idm = null;
try
{
StreamReader sr = new StreamReader(bmTempFileStream);
string headerLine = "";
string firstDataLine = "";
if (sr != null)
{
headerLine = sr.ReadLine();
firstDataLine = sr.ReadLine();
bit.ParseDataLinesForOrigins(headerLine, firstDataLine, ',', out _originX, out _originY, out _originZ);
// auto generate a format defintion based on Goldfields typical input column data
idm = bit.AutoGenerateFormatDefinition(headerLine, ',');
}
sr.Close();
}
catch (Exception ex) {
mos.AddWarningMessage("Unable to auto detect origin and other format information from the file\n\n" + ex.ToString());
}
var opts = new TransactionOptions();
opts.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
using (new TransactionScope(TransactionScopeOption.Suppress, opts))
{
try
{
Guid blockModelGUID = Guid.NewGuid();
mos.modelID = blockModelGUID;
domains = bit.PerformBMImport(mos, blockModelGUID, bmFileStream, null, idm, _originX, _originY, _originZ, null, 1000, projectID, alias, authorGuid, _users.ApplicationConnectionString);
List <Tuple <string, string> > doms = new List <Tuple <string, string> >();
string domainColumnName = "Domain";
foreach (string ss in domains)
{
doms.Add(new Tuple <string, string>(domainColumnName, ss));
}
UpdateDomains(doms, blockModelGUID);
AddModelNotes(notes, blockModelGUID);
UpdateStage(blockModelGUID, stageMetaID, stage);
}
catch (Exception ex)
{
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
mos.AddErrorMessage("Error importing block model:\n" + ex.ToString());
}
}
//TODO call into import library with the stream object for the import
bmFileStream.Close();
_storageProvider.DeleteFile(bmFileName);
_storageProvider.DeleteFolder(targetFolder);
}
return(mos);
}
internal ModelImportStatus UpdateBlockData(Stream bmStream, Guid guid, string colToInsertTo, string connString)
{
ModelImportStatus mos = new ModelImportStatus();
// iterate through the data lines
int ct = 1;
int linesRead = 0;
int total = 0;
SqlConnection connection = null;
List<string> uniqueDomains = new List<string>();
// get a connection to the database
try
{
connection = new SqlConnection(connString);
connection.Open();
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
List<SqlCommand> commands = new List<SqlCommand>();
int tb = 0;
int transactionBatchLimit = 500; ;
// open the filestream and read the first line
StreamReader sr = null;
try
{
sr = new StreamReader(bmStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input model:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float bct = 1;
int repCount = 0;
bool commitToDB = true;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
linesRead++;
repCount++;
bct++;
if (ct > 1)
{
List<string> items = parseTestLine(line, ',');
string statement1 = "UPDATE X_BlockModelBlock SET "+colToInsertTo+"="+items[3]+" WHERE BlockModelID=\'"+guid+"\' AND "+
"CentroidX=" + items[0] + " AND CentroidY=" + items[1] + " AND CentroidZ=" + items[2];
SqlCommand sqc = new SqlCommand(statement1, connection, trans);
if (commitToDB)
{
sqc.ExecuteNonQuery();
}
tb++;
if (tb == transactionBatchLimit)
{
// commit batch, then renew the transaction
if (commitToDB)
{
trans.Commit();
numCommits++;
total += tb;
// trans = null;
trans = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
}
// reset counter
tb = 0;
}
}
ct++;
}
}
if (tb > 0)
{
if (commitToDB)
{
trans.Commit();
total += tb;
}
numCommits++;
}
// UpdateStatus("Finished writing blocks to database ", 100.0);
}
catch (Exception ex)
{
// UpdateStatus("Error writing blocks to database ", 0);
mos.AddErrorMessage("Error writing block data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
mos.RecordsImported = total;
try { connection.Close(); }
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
mos.linesReadFromSource = linesRead;
return mos;
}
internal ModelImportStatus BatchImportLasFiles(string[] filePaths, Guid currentProjectID)
{
ModelImportStatus finalStatus = new ModelImportStatus();
LASBatchImportTools ll = new LASBatchImportTools();
List <string> messages = new List <string>();
int importCount = 0;
int failCount = 0;
string report = "";
Dictionary <string, ModelImportStatus> mosList = new Dictionary <string, ModelImportStatus>();
bool reportStatus = false;
if (this.backgroundWorker != null)
{
reportStatus = true;
}
int fileCount = filePaths.Length;
int thisFileNum = 0;
var dataDict = new Dictionary <string, List <object> >();
foreach (string file in filePaths)
{
var data = new List <object>();
double pct = ((double)thisFileNum / (double)fileCount) * 100.0;
thisFileNum++;
if (reportStatus)
{
backgroundWorker.ReportProgress((int)pct, "Processing las file " + thisFileNum + " of " + fileCount + ", " + file);
}
ModelImportStatus mis = new ModelImportStatus();
NKD.Import.Client.Processing.LASImport li = new NKD.Import.Client.Processing.LASImport();
LASFile lf = li.GetLASFile(file, mis);
if (lf == null)
{
mis.errorMessages.Add("Failed to load LAS file " + file);
mosList.Add(file, mis);
continue;
}
data = ll.ProcessLASFile(lf, file, mis, currentProjectID, this.backgroundWorker);
string msg = "";
if (msg != null)
{
messages.Add(msg);
report += msg + "\n";
failCount++;
}
else
{
importCount++;
}
mosList.Add(file, mis);
dataDict.Add(file, data);
//if (thisFileNum % 2 == 0 || (filePaths.Length-thisFileNum) < 1) //FIXME magic number, should look at used memory and make a choice on that
//{
//insert into DB to avoid memory issues
//var subdict = dataDict;
PushToDB(dataDict);
dataDict = new Dictionary <string, List <object> >();
//PushToDB(subdict);
//subdict = null;
GC.Collect();
GC.WaitForPendingFinalizers();
//}
}
string finalReport = "Immport status:\nFiles imported:" + importCount + "\nFailed files:" + failCount + "\n\nMessages:\n";
finalReport += report;
int totRecordsAddedCount = 0;
int totLinesReadCount = 0;
foreach (KeyValuePair <string, ModelImportStatus> kvp in mosList)
{
string lfName = kvp.Key;
ModelImportStatus ms = kvp.Value;
totRecordsAddedCount += ms.recordsAdded;
totLinesReadCount += ms.linesReadFromSource;
if (ms.finalErrorCode != ModelImportStatus.OK)
{
finalStatus.finalErrorCode = ModelImportStatus.GENERAL_LOAD_ERROR;
}
foreach (string m in ms.warningMessages)
{
finalStatus.warningMessages.Add(m);
}
foreach (string m in ms.errorMessages)
{
finalStatus.errorMessages.Add(m);
}
}
finalStatus.linesReadFromSource = totLinesReadCount;
finalStatus.recordsAdded = totRecordsAddedCount;
return(finalStatus);
}
public LASFile GetLASFile(string inputFilename,ModelImportStatus mis ) {
LASFile lf = null;
try
{
LASFileReader lfr = new LASFileReader();
int columnOffset = 0;
int errorCode = 0;
lf = lfr.ReadLASFile(inputFilename, columnOffset, out errorCode);
if (errorCode == 0)
{
mis.finalErrorCode = ModelImportStatus.OK;
string res = "";
foreach (string nc in lf.columnHeaders)
{
res += nc + ", ";
}
mis.linesReadFromSource = lf.dataRows.Count;
// Display error mesasges if required
if (lf.errorDetails != null && lf.errorDetails.Count > 0)
{
string messageBoxText = "The file (" + inputFilename + ") was loaded, but issues were noted as follows:";
mis.AddWarningMessage(messageBoxText);
foreach (string ed in lf.errorDetails)
{
string ss = "\n" + ed;
mis.AddWarningMessage(ss);
}
}
}
else
{
string messageBoxText = "The file (" + inputFilename + ") could not be loaded. Please check that the file is " +
"accessible, is not open in another application and is in the correct format.";
mis.AddErrorMessage(messageBoxText);
string errorCodeDetails = LASErrorCodes.LookupCode(errorCode);
if (lf != null && lf.errorDetails != null)
{
foreach (string ed in lf.errorDetails)
{
string ss = "\n" + ed;
mis.AddErrorMessage(ss);
}
}
mis.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
string caption = "Error loading file " + inputFilename;
}
}
catch (Exception ex)
{
}
return lf;
}
internal List <object> ImportLASFile(NKD.Import.LAS.LASFile lasFile, string origFilename, ModelImportStatus mos, Guid currentProject, Action <string, double> UpdateStatus)
{
int li = origFilename.LastIndexOf("\\");
string tempHoleIDa = origFilename.Substring(li);
li = tempHoleIDa.LastIndexOf(".");
string tempHoleID = tempHoleIDa.Substring(1, li - 1);
int rowCounter = 0;
// now try and get the hole name from a header item. Typically the header name might be in
// WELL in the Well information header section
string res = lasFile.LookupWellHeaderSection("WELL");
if (res != null && res.Trim().Length > 0)
{
tempHoleID = res;
}
List <object> dataList = new List <object>();
try
{
// here we need to create the Geophyiscs data row item
var entityObj = new NKDC(BaseImportTools.XSTRING, null, false);
//entityObj.AutoDetectChangesEnabled = false; //TODO: Exhaust this, should be faster now
var physDataList = new List <Geophysics>();
//var fDataList = new List<NKD.Module.BusinessObjects.File>();
var fdDataList = new List <FileData>();
Geophysics xG = new Geophysics();
xG.FileName = origFilename;
Guid gg = Guid.NewGuid();
xG.GeophysicsID = gg;
Guid holeGuid = CollarQueries.FindHeaderGuid(tempHoleID, currentProject);
Guid resHole = new Guid();
if (!holeGuid.ToString().Equals(resHole.ToString()))
{
xG.HeaderID = holeGuid;
}
Guid unitGuid = new Guid("2395DE56-8F6F-4B0C-806C-DD2606B9902B"); //FIXME: Magic Number
UnitQueries uq = new UnitQueries();
DictionaryUnit xu = uq.FindUnits("m");
if (xu != null)
{
unitGuid = xu.UnitID;
}
xG.DimensionUnitID = unitGuid;
xG.LasVersion = string.Format("{0:N1}", lasFile.versionValue);
xG.LasWrap = lasFile.versionWrap;
xG.LasNullValue = string.Format("{0:N2}", lasFile.nullValue);
FileStream sr = null;
try
{
sr = new FileStream(lasFile.filePath, FileMode.Open);
}
catch (FileNotFoundException fex)
{
Console.WriteLine("FileNotFoundException:" + fex.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
NKD.Module.BusinessObjects.File F = new NKD.Module.BusinessObjects.File();
F.LoadFromStream(lasFile.FileName(), sr);
sr = null;
Guid fdGUID = Guid.NewGuid();
var fD = new FileData
{
Author = default(string),
FileDataID = fdGUID,
ReferenceID = xG.GeophysicsID,
TableType = "X_Geophysics",
FileName = F.FileName,
FileBytes = F.FileBytes,
FileChecksum = Hash.ComputeHash(F.FileBytes),
MimeType = MimeTypes.MimeTypeHelper.GetMimeTypeByFileName(F.FileName)
};
xG.OriginalFileDataID = fD.FileDataID;
physDataList.Add(xG);
fdDataList.Add(fD);
F = null;
// here we need to add a GeophysicsMetadata item for each column
Dictionary <string, Guid> metaDataIDLookup = new Dictionary <string, Guid>();
var unitDataList = new List <DictionaryUnit>();
var paramDataList = new List <Parameter>();
var metaDataList = new List <GeophysicsMetadata>();
foreach (string s in lasFile.columnHeaders)
{
Parameter xp = null;
xp = GetParameterIDFor(entityObj, "LAS data column", s);
//test to see if we already have the unit
string splitter = s.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries).LastOrDefault().Trim();
uq = new UnitQueries();
xu = uq.FindUnits(splitter);
if (xp != null && xu != null)
{
xp.UnitID = xu.UnitID;
//xp.Unit = xu;
}
else
{
bool xpnull = false;
if (xp == null)
{
xpnull = true;
xp = new Parameter();
Guid pg = Guid.NewGuid();
xp.ParameterID = pg;
xp.ParameterType = "LAS data column";
xp.ParameterName = s;
}
//test to see if we already have added a unit into the list
if (unitDataList.Count > 0 && xu == null)
{
xu = unitDataList.Where(c => c.StandardUnitName == splitter).FirstOrDefault();
}
if (xu == null)
{
//create new unit here store it and pass to parameters
Guid ug = Guid.NewGuid();
xu = new DictionaryUnit
{
UnitID = ug,
StandardUnitName = splitter,
CoalUnitName = splitter,
StrictlySI = false
};
unitDataList.Add(xu);
}
xp.UnitID = xu.UnitID;
if (xpnull)
{
paramDataList.Add(xp);
}
}
Guid gmid = Guid.NewGuid();
GeophysicsMetadata xgm = new GeophysicsMetadata
{
GeophysicsID = gg,
GeophysicsMetadataID = gmid,
Unit = xu.StandardUnitName,
Mnemonic = s,
ParameterID = xp.ParameterID
};
metaDataList.Add(xgm);
metaDataIDLookup.Add(s, gmid);
}
int insertCounter = 0;
var geoDataList = new List <GeophysicsData>();
foreach (LASDataRow ldr in lasFile.dataRows)
{
double depth = ldr.depth;
for (int i = 0; i < ldr.rowData.Count(); i++)
{
GeophysicsData xd1 = new GeophysicsData();
string s = lasFile.columnHeaders[i];
xd1.GeophysicsDataID = Guid.NewGuid();
Guid g = new Guid();
bool found = metaDataIDLookup.TryGetValue(s, out g);
if (found)
{
xd1.GeophysicsMetadataID = g;
}
xd1.Dimension = (decimal)depth;
xd1.MeasurementValue = (decimal)ldr.rowData[i];
geoDataList.Add(xd1);
}
insertCounter++;
rowCounter++;
}
lasFile = null;
dataList.Add(fdDataList);
dataList.Add(physDataList);
dataList.Add(unitDataList);
dataList.Add(paramDataList);
dataList.Add(metaDataList);
dataList.Add(geoDataList);
fdDataList = null;
physDataList = null;
unitDataList = null;
paramDataList = null;
metaDataIDLookup = null;
metaDataList = null;
geoDataList = null;
}
catch (Exception ex) {
mos.errorMessages.Add("Failed to complete import of LAS file: " + origFilename);
mos.errorMessages.Add("Details: " + ex.Message.ToString());
if (ex.InnerException != null)
{
mos.errorMessages.Add("Inner Exception: " + ex.InnerException.Message.ToString());
}
mos.errorMessages.Add("Row: " + rowCounter);
}
mos.recordsAdded = rowCounter;
return(dataList);
}
/// <summary>
/// Add block data to the model, provided by the supplied file stream and format defintion
/// </summary>
/// <param name="bmFileStream"></param>
/// <param name="importMap"></param>
/// <param name="blockModelGUID"></param>
/// <param name="batchSize"></param>
/// <param name="UpdateStatus"></param>
/// <param name="approxNumLines"></param>
internal List <string> AddBlockData(ModelImportStatus mos, Stream bmFileStream, ImportDataMap importMap, Guid blockModelGUID, int batchSize, Action <string, double> UpdateStatus, int numLines, string connString)
{
// iterate through the data lines
int ct = 1;
int linesRead = 0;
int total = 0;
SqlConnection connection = null;
List <string> uniqueDomains = new List <string>();
// get a connection to the database
try
{
int domainColIDX = -1;
// find the column ID for the specified Domain field, as we need to capture this list.
foreach (ColumnMap cm in importMap.columnMap)
{
if (cm.targetColumnName.Trim().Equals("Domain"))
{
domainColIDX = cm.sourceColumnNumber;
}
}
connection = new SqlConnection(connString);
connection.Open();
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
List <SqlCommand> commands = new List <SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
//FileStream fs = null;
try
{
//fs = new FileStream(textInputDataFile, FileMode.Open, FileAccess.Read, FileShare.Read);
sr = new StreamReader(bmFileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input model:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float pct = 0;
float bct = 1;
// report every X blocks
int repCount = 0;
int reportOnBlock = 500;
float fNumLines = (float)numLines;
bool commitToDB = true;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
linesRead++;
repCount++;
if (repCount == reportOnBlock)
{
repCount = 0;
// now report status
pct = (bct / fNumLines) * 100.0f;
UpdateStatus("Writing block " + bct + " to database", pct);
}
bct++;
if (ct >= importMap.dataStartLine)
{
string statementPart1 = "INSERT INTO " + importMap.mapTargetPrimaryTable + " ";
string clauseValues = "";
string clauseParameters = "";
List <string> items = parseTestLine(line, importMap.inputDelimiter);
// now pick out all the mapped values
foreach (ColumnMap cmap in importMap.columnMap)
{
int colID = cmap.sourceColumnNumber;
string columnValue = cmap.defaultValue;
if (colID >= 0)
{
columnValue = items[colID];
}
if (cmap.sourceColumnNumber == domainColIDX)
{
if (!uniqueDomains.Contains(columnValue.Trim()))
{
uniqueDomains.Add(columnValue);
}
}
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
if (cmap.importDataType.Equals(ImportDataMap.NUMERICDATATYPE))
{
if (columnValue.Equals("-"))
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
else
{
columnValue = "NULL";
}
}
clauseParameters += columnValue + ",";
}
else
{
clauseParameters += "\'" + columnValue + "\',";
}
}
// now just a hack to remove the final coma from the query
clauseParameters = clauseParameters.Substring(0, clauseParameters.Length - 1);
clauseValues = clauseValues.Substring(0, clauseValues.Length - 1);
string commandText = statementPart1 + "(" + clauseValues + ") VALUES (" + clauseParameters + ")";
SqlCommand sqc = new SqlCommand(commandText, connection, trans);
if (commitToDB)
{
sqc.ExecuteNonQuery();
}
tb++;
if (tb == transactionBatchLimit)
{
// commit batch, then renew the transaction
if (commitToDB)
{
total += tb;
trans.Commit();
numCommits++;
// trans = null;
trans = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
}
// reset counter
tb = 0;
}
}
ct++;
}
}
if (tb > 0)
{
if (commitToDB)
{
total += tb;
trans.Commit();
}
numCommits++;
}
UpdateStatus("Finished writing blocks to database ", 100.0);
}
catch (Exception ex)
{
UpdateStatus("Error writing blocks to database ", 0);
mos.AddErrorMessage("Error writing block data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try { connection.Close(); }
catch (Exception ex) {
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
mos.RecordsImported = total;
mos.linesReadFromSource = linesRead;
return(uniqueDomains);
}
internal List<object> ImportLASFile(Xstract.Import.LAS.LASFile lasFile, string origFilename, ModelImportStatus mos, Guid currentProject, Action<string, double> UpdateStatus)
{
int li = origFilename.LastIndexOf("\\");
string tempHoleIDa = origFilename.Substring(li);
li = tempHoleIDa.LastIndexOf(".");
string tempHoleID = tempHoleIDa.Substring(1, li - 1);
int rowCounter = 0;
// now try and get the hole name from a header item. Typically the header name might be in
// WELL in the Well information header section
string res = lasFile.LookupWellHeaderSection("WELL");
if (res != null && res.Trim().Length > 0) {
tempHoleID = res;
}
List<object> dataList = new List<object>();
try
{
// here we need to create the Geophyiscs data row item
var entityObj = new XODBC(BaseImportTools.XSTRING, null, false);
//entityObj.AutoDetectChangesEnabled = false; //TODO: Exhaust this, should be faster now
var physDataList = new List<Geophysics>();
//var fDataList = new List<XODB.Module.BusinessObjects.File>();
var fdDataList = new List<FileData>();
Geophysics xG = new Geophysics();
xG.FileName = origFilename;
Guid gg = Guid.NewGuid();
xG.GeophysicsID = gg;
Guid holeGuid = CollarQueries.FindHeaderGuid(tempHoleID, currentProject);
Guid resHole = new Guid();
if (!holeGuid.ToString().Equals(resHole.ToString()))
{
xG.HeaderID = holeGuid;
}
Guid unitGuid = new Guid("2395DE56-8F6F-4B0C-806C-DD2606B9902B"); //FIXME: Magic Number
UnitQueries uq = new UnitQueries();
DictionaryUnit xu = uq.FindUnits("m");
if (xu != null)
{
unitGuid = xu.UnitID;
}
xG.DimensionUnitID = unitGuid;
xG.LasVersion = string.Format("{0:N1}",lasFile.versionValue);
xG.LasWrap = lasFile.versionWrap;
xG.LasNullValue = string.Format("{0:N2}",lasFile.nullValue);
FileStream sr = null;
try
{
sr = new FileStream(lasFile.filePath, FileMode.Open);
}
catch (FileNotFoundException fex)
{
Console.WriteLine("FileNotFoundException:" + fex.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
XODB.Module.BusinessObjects.File F = new XODB.Module.BusinessObjects.File();
F.LoadFromStream(lasFile.FileName(), sr);
sr = null;
Guid fdGUID = Guid.NewGuid();
var fD = new FileData
{
Author = default(string),
FileDataID = fdGUID,
ReferenceID = xG.GeophysicsID,
TableType = "X_Geophysics",
FileName = F.FileName,
FileBytes = F.FileBytes,
FileChecksum = Hash.ComputeHash(F.FileBytes),
MimeType = MimeTypes.MimeTypeHelper.GetMimeTypeByFileName(F.FileName)
};
xG.OriginalFileDataID = fD.FileDataID;
physDataList.Add(xG);
fdDataList.Add(fD);
F = null;
// here we need to add a GeophysicsMetadata item for each column
Dictionary<string, Guid> metaDataIDLookup = new Dictionary<string, Guid>();
var unitDataList = new List<DictionaryUnit>();
var paramDataList = new List<Parameter>();
var metaDataList = new List<GeophysicsMetadata>();
foreach (string s in lasFile.columnHeaders)
{
Parameter xp = null;
xp = GetParameterIDFor(entityObj, "LAS data column", s);
//test to see if we already have the unit
string splitter = s.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries).LastOrDefault().Trim();
uq = new UnitQueries();
xu = uq.FindUnits(splitter);
if (xp != null && xu != null)
{
xp.UnitID = xu.UnitID;
//xp.Unit = xu;
}
else
{
bool xpnull = false;
if (xp == null)
{
xpnull = true;
xp = new Parameter();
Guid pg = Guid.NewGuid();
xp.ParameterID = pg;
xp.ParameterType = "LAS data column";
xp.ParameterName = s;
}
//test to see if we already have added a unit into the list
if (unitDataList.Count > 0 && xu == null)
{
xu = unitDataList.Where(c => c.StandardUnitName == splitter).FirstOrDefault();
}
if (xu == null)
{
//create new unit here store it and pass to parameters
Guid ug = Guid.NewGuid();
xu = new DictionaryUnit
{
UnitID = ug,
StandardUnitName = splitter,
CoalUnitName = splitter,
StrictlySI = false
};
unitDataList.Add(xu);
}
xp.UnitID = xu.UnitID;
if (xpnull)
{
paramDataList.Add(xp);
}
}
Guid gmid = Guid.NewGuid();
GeophysicsMetadata xgm = new GeophysicsMetadata
{
GeophysicsID = gg,
GeophysicsMetadataID = gmid,
Unit = xu.StandardUnitName,
Mnemonic = s,
ParameterID = xp.ParameterID
};
metaDataList.Add(xgm);
metaDataIDLookup.Add(s, gmid);
}
int insertCounter = 0;
var geoDataList = new List<GeophysicsData>();
foreach (LASDataRow ldr in lasFile.dataRows)
{
double depth = ldr.depth;
for (int i = 0; i < ldr.rowData.Count(); i++)
{
GeophysicsData xd1 = new GeophysicsData();
string s = lasFile.columnHeaders[i];
xd1.GeophysicsDataID = Guid.NewGuid();
Guid g = new Guid();
bool found = metaDataIDLookup.TryGetValue(s, out g);
if (found)
{
xd1.GeophysicsMetadataID = g;
}
xd1.Dimension = (decimal)depth;
xd1.MeasurementValue = (decimal)ldr.rowData[i];
geoDataList.Add(xd1);
}
insertCounter++;
rowCounter++;
}
lasFile = null;
dataList.Add(fdDataList);
dataList.Add(physDataList);
dataList.Add(unitDataList);
dataList.Add(paramDataList);
dataList.Add(metaDataList);
dataList.Add(geoDataList);
fdDataList = null;
physDataList = null;
unitDataList = null;
paramDataList = null;
metaDataIDLookup = null;
metaDataList = null;
geoDataList = null;
}
catch (Exception ex) {
mos.errorMessages.Add("Failed to complete import of LAS file: " + origFilename);
mos.errorMessages.Add("Details: " + ex.Message.ToString());
if (ex.InnerException != null)
mos.errorMessages.Add("Inner Exception: " + ex.InnerException.Message.ToString());
mos.errorMessages.Add("Row: " + rowCounter);
}
mos.recordsAdded = rowCounter;
return dataList;
}
internal ModelImportStatus UpdateBlockData(Stream bmStream, Guid guid, string colToInsertTo, string connString, char delimeter)
{
ModelImportStatus mos = new ModelImportStatus();
// iterate through the data lines
int ct = 1;
int linesRead = 0;
int total = 0;
SqlConnection connection = null;
List <string> uniqueDomains = new List <string>();
// get a connection to the database
try
{
connection = new SqlConnection(connString);
connection.Open();
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
List <SqlCommand> commands = new List <SqlCommand>();
int tb = 0;
int transactionBatchLimit = 500;;
// open the filestream and read the first line
StreamReader sr = null;
try
{
sr = new StreamReader(bmStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input model:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float bct = 1;
int repCount = 0;
bool commitToDB = true;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
linesRead++;
repCount++;
bct++;
if (ct > 1)
{
List <string> items = parseTestLine(line, delimeter);
string statement1 = "UPDATE X_BlockModelBlock SET " + colToInsertTo + "=" + items[3] + " WHERE BlockModelID=\'" + guid + "\' AND " +
"CentroidX=" + items[0] + " AND CentroidY=" + items[1] + " AND CentroidZ=" + items[2];
SqlCommand sqc = new SqlCommand(statement1, connection, trans);
if (commitToDB)
{
sqc.ExecuteNonQuery();
}
tb++;
if (tb == transactionBatchLimit)
{
// commit batch, then renew the transaction
if (commitToDB)
{
trans.Commit();
numCommits++;
total += tb;
// trans = null;
trans = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
}
// reset counter
tb = 0;
}
}
ct++;
}
}
if (tb > 0)
{
if (commitToDB)
{
trans.Commit();
total += tb;
}
numCommits++;
}
// UpdateStatus("Finished writing blocks to database ", 100.0);
}
catch (Exception ex)
{
// UpdateStatus("Error writing blocks to database ", 0);
mos.AddErrorMessage("Error writing block data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
mos.RecordsImported = total;
try { connection.Close(); }
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
mos.linesReadFromSource = linesRead;
return(mos);
}
internal void AddAssayData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap,
int batchSize, Action<string, double> UpdateStatus, int approxNumLines,
string connectionString, Guid XODBProjectID, bool checkForDuplicates, bool doImportOverwrite)
{
bool commitToDB = true;
DateTime currentUpdateTimestamp = DateTime.UtcNow;
// first set up an assay group object - we can do this through the edm
using (var entityObj = new XODBC(connectionString, null))
{
//entityObj.Configuration.AutoDetectChangesEnabled = false;
Guid agGuid = Guid.NewGuid();
AssayGroup ag = new AssayGroup();
ag.AssayGroupID = agGuid;
ag.ProjectID = XODBProjectID;
ag.AssayGroupName = "Manual import";
ag.Comment = "From file " + importMap.mapOriginalDataFile;
ag.Entered = currentUpdateTimestamp;
ag.VersionUpdated = currentUpdateTimestamp;
entityObj.AssayGroups.AddObject(ag);
if (commitToDB)
{
entityObj.SaveChanges();
}
// set up the assay test columns - one of these for each test type
Dictionary<ColumnMap, Guid> resultsColumns = new Dictionary<ColumnMap, Guid>();
Dictionary<Guid, AssayGroupTest> assayGroups = new Dictionary<Guid, AssayGroupTest>();
foreach (ColumnMap cim in importMap.columnMap)
{
if (cim.targetColumnName.Trim().StartsWith("[ASSAY"))
{
// this is a test category
resultsColumns.Add(cim, Guid.NewGuid());
}
}
UpdateStatus("Setting up assay tests ", 2);
foreach (KeyValuePair<ColumnMap, Guid> kvp in resultsColumns)
{
ColumnMap cm = kvp.Key;
Guid g = kvp.Value;
AssayGroupTest xt = new AssayGroupTest();
string ss1 = "";
if (cm.sourceColumnName != null && cm.sourceColumnName.Length > 15)
{
ss1 = cm.sourceColumnName.Substring(0, 16);
}
else
{
ss1 = cm.sourceColumnName;
}
Guid pid = FindParameterForAssayTypeName(cm.sourceColumnName);
xt.ParameterID = pid;
xt.AssayTestName = ss1;
xt.AssayGroupID = agGuid;
xt.AssayGroupTestID = g;
xt.VersionUpdated = currentUpdateTimestamp;
entityObj.AssayGroupTests.AddObject(xt);
assayGroups.Add(g, xt);
if (commitToDB)
{
entityObj.SaveChanges();
}
}
// iterate through the data lines
int ct = 1;
int linesRead = 0;
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
//List<string> uniqueDomains = new List<string>();
// get a connection to the database
try
{
int domainColIDX = -1;
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
bool hasDuplicateIntervals = false;
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction();
List<SqlCommand> commands = new List<SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
FileStream fs = null;
try
{
//fs = new FileStream(textInputDataFile, FileMode.Open, FileAccess.Read, FileShare.Read);
sr = new StreamReader(fileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input data:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float pct = 0;
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)approxNumLines;
Dictionary<string, Guid> holeIDLookups = new Dictionary<string, Guid>();
Dictionary<string, int> columnIDX = new Dictionary<string, int>();
int fkLookupCount = 0;
PopulateCMapShortcut("HeaderID", importMap, columnIDX);
PopulateCMapShortcut("FromDepth", importMap, columnIDX);
PopulateCMapShortcut("ToDepth", importMap, columnIDX);
PopulateCMapShortcut("SampleNumber", importMap, columnIDX);
PopulateCMapShortcut("LabSampleNumber", importMap, columnIDX);
PopulateCMapShortcut("LabBatchNumber", importMap, columnIDX);
ColumnMap headerCmap = importMap.FindItemsByTargetName("HeaderID");
AssayQueries assayQueries = new AssayQueries();
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
repCount++;
pct = ((float)linesRead / (float)approxNumLines) * 100.0f;
bct++;
linesRead++;
if (ct >= importMap.dataStartLine)
{
// digest a row of input data
List<string> items = parseTestLine(line, importMap.inputDelimiter);
Guid holeID = new Guid();
Decimal fromDepth = new Decimal(-9999999999);
Decimal toDepth = new Decimal(-9999999999);
string sampleNumber = null;
string labBatchNumber = null;
string labsampleNumber = null;
// find mapped values by name
//ColumnMap cmap = importMap.FindItemsByTargetName("HeaderID");
int idxVal = 0;
bool foundEntry = columnIDX.TryGetValue("HeaderID", out idxVal);
bool foundHole = false;
string holeName = "";
if (foundEntry)
{
string lookupByName = "HoleName";
string lookupValue = items[idxVal];
holeName = lookupValue;
bool lv = holeIDLookups.ContainsKey(lookupValue);
if (!lv)
{
string headerGUID = ForeignKeyUtils.FindFKValueInOther(lookupValue, headerCmap, secondaryConnection, false, lookupByName, XODBProjectID);
if (headerGUID == null)
{
// this means we have not found the specified records in the header table
// Report on issue and skip line
}
else
{
foundHole = true;
holeID = new Guid(headerGUID);
holeIDLookups.Add(lookupValue, holeID);
fkLookupCount++;
}
}
else
{
holeIDLookups.TryGetValue(lookupValue, out holeID);
foundHole = true;
}
}
if (!foundHole)
{
mos.AddErrorMessage("Failed to find hole " + holeName + ". Skipping record at line " + linesRead + ".");
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
mos.recordsFailed++;
continue;
}
else
{
bool hasFrom = false;
idxVal = 0;
foundEntry = columnIDX.TryGetValue("FromDepth", out idxVal);
if (foundEntry)
//cmap = importMap.FindItemsByTargetName();
//if (cmap != null)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
fromDepth = val;
hasFrom = true;
}
}
bool hasTo = false;
idxVal = 0;
foundEntry = columnIDX.TryGetValue("ToDepth", out idxVal);
if (foundEntry)
//cmap = importMap.FindItemsByTargetName("ToDepth");
//if (cmap != null)
{
string ii = items[idxVal];
Decimal val = 0;
bool isOk = Decimal.TryParse(ii, out val);
if (isOk)
{
toDepth = val;
hasTo = true;
}
}
List<Sample> duplicateList = null;
bool isDuplicateInterval = false;
if (checkForDuplicates)
{
if (hasFrom && hasTo)
{
// here we need to check that the hole is not duplicated
duplicateList = assayQueries.CheckForDuplicate(holeID, fromDepth, toDepth);
if (duplicateList.Count > 0)
{
isDuplicateInterval = true;
}
}
if (isDuplicateInterval)
{
hasDuplicateIntervals = true;
mos.AddWarningMessage("Duplicate interval for hole " + holeName + " at depth " + fromDepth + " to " + toDepth);
UpdateStatus("Duplicate interval at " + holeName + " " + fromDepth + ", " + toDepth, pct);
if (!doImportOverwrite)
{
mos.recordsFailed++;
continue;
}
}
}
//cmap = importMap.FindItemsByTargetName("SampleNumber");
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleNumber", out idxVal);
if (foundEntry)
// if (cmap != null)
{
string ii = items[idxVal];
sampleNumber = ii;
}
//cmap = importMap.FindItemsByTargetName("LabSampleNumber");
//if (cmap != null)
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleNumber", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
labsampleNumber = ii;
}
//cmap = importMap.FindItemsByTargetName("LabBatchNumber");
idxVal = 0;
foundEntry = columnIDX.TryGetValue("SampleNumber", out idxVal);
if (foundEntry)
{
string ii = items[idxVal];
labBatchNumber = ii;
}
Sample xs = new Sample();
if (isDuplicateInterval == true)
{
xs = duplicateList.First();
}
else
{
xs.SampleID = Guid.NewGuid();
xs.FromDepth = fromDepth;
xs.ToDepth = toDepth;
xs.HeaderID = holeID;
xs.VersionUpdated = currentUpdateTimestamp;
entityObj.Samples.AddObject(xs);
}
// now pick out all the mapped values
// iterate over all [ASSAY RESULT] columns
bool assayUpdated = false;
bool assayAdded = false;
foreach (KeyValuePair<ColumnMap, Guid> kvp in resultsColumns)
{
ColumnMap cm = kvp.Key;
Guid g = kvp.Value; // this is the AssayGroupTestID
AssayGroupTestResult testResult = new AssayGroupTestResult();
/*bool assayResFound = false;
if (isDuplicateInterval)
{
List<AssayGroupTestResult> testResults = assayQueries.GetDuplicateResult(xs.SampleID, cm.sourceColumnName);
if (testResults.Count > 0)
{
testResult = testResults.First();
assayResFound = true;
}
}*/
//if(!assayResFound)
// {
testResult.AssayGroupTestResultID = Guid.NewGuid();
testResult.AssayGroupTestID = g;
testResult.SampleID = xs.SampleID;
testResult.VersionUpdated = currentUpdateTimestamp;
//}
testResult.LabBatchNumber = labBatchNumber;
// testResult.LabSampleName = labsampleNumber;
Decimal result = new Decimal();
if (items.Count >= cm.sourceColumnNumber)
{
bool parsedOK = Decimal.TryParse(items[cm.sourceColumnNumber], out result);
if (parsedOK)
{
testResult.LabResult = result;
}
else
{
testResult.LabResultText = items[cm.sourceColumnNumber];
}
}
else
{
mos.AddWarningMessage("Line " + linesRead + " contains too few columns to read " + cm.sourceColumnName);
}
//if (isDuplicateInterval == false)
//{
entityObj.AssayGroupTestResults.AddObject(testResult);
assayAdded = true;
//}else{
// if (!assayResFound)
// {
// entityObj.AssayGroupTestResult.Add(testResult);
// assayAdded = true;
// }
// else {
// assayUpdated = true;
// }
//}
}
if (assayAdded == true)
{
mos.recordsAdded++;
}
if (assayUpdated)
{
mos.recordsUpdated++;
}
tb++;
}
}
if (commitToDB)
{
if (tb == transactionBatchLimit)
{
entityObj.SaveChanges();
UpdateStatus("Writing assays to DB (" + ct + " entries)", pct);
tb = 0;
}
}
ct++;
//Console.WriteLine("Processing line "+ct);
}
entityObj.SaveChanges();
}
if (hasDuplicateIntervals)
{
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
}
string numFKLookups = "FK lookups " + fkLookupCount;
mos.linesReadFromSource = ct - 1;
UpdateStatus("Finished writing assays to database.", 0);
}
catch (Exception ex)
{
UpdateStatus("Error writing assays to database ", 0);
mos.AddErrorMessage("Error writing assay data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try
{
connection.Close();
secondaryConnection.Close();
fileStream.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
mos.linesReadFromSource = linesRead;
}
}
internal List<object> ImportLASFile(Xstract.Import.LAS.LASFile lasFile, string origFilename, ModelImportStatus mos, Guid currentProject, Action<string, double> UpdateStatus)
{
int li = origFilename.LastIndexOf("\\");
string tempHoleIDa = origFilename.Substring(li);
li = tempHoleIDa.LastIndexOf(".");
string tempHoleID = tempHoleIDa.Substring(1, li - 1);
int rowCounter = 0;
// now try and get the hole name from a header item. Typically the header name might be in
// WELL in the Well information header section
string res = lasFile.LookupWellHeaderSection("WELL");
if (res != null && res.Trim().Length > 0) {
tempHoleID = res;
}
List<object> dataList = new List<object>();
try
{
// here we need to create the Geophyiscs data row item
var entityObj = new XODBC(BaseImportTools.XSTRING, null, false);
//entityObj.AutoDetectChangesEnabled = false; //TODO: Exhaust this, should be faster now
var physDataList = new List<Geophysics>();
//var fDataList = new List<XODB.Module.BusinessObjects.File>();
var fdDataList = new List<FileData>();
Geophysics xG = new Geophysics();
xG.FileName = origFilename;
Guid gg = Guid.NewGuid();
xG.GeophysicsID = gg;
Guid holeGuid = CollarQueries.FindHeaderGuid(tempHoleID, currentProject);
Guid resHole = new Guid();
if (!holeGuid.ToString().Equals(resHole.ToString()))
{
xG.HeaderID = holeGuid;
}
Guid unitGuid = new Guid("2395DE56-8F6F-4B0C-806C-DD2606B9902B"); //FIXME: Magic Number
UnitQueries uq = new UnitQueries();
DictionaryUnit xu = uq.FindUnits("m");
if (xu != null)
{
unitGuid = xu.UnitID;
}
xG.DimensionUnitID = unitGuid;
xG.LasVersion = string.Format("{0:N1}",lasFile.versionValue);
xG.LasWrap = lasFile.versionWrap;
xG.LasNullValue = string.Format("{0:N2}",lasFile.nullValue);
FileData fD = new FileData();
FileStream sr = null;
try
{
sr = new FileStream(lasFile.filePath, FileMode.Open);
}
catch (FileNotFoundException fex)
{
}
catch (Exception ex)
{
}
XODB.Module.BusinessObjects.File F = new XODB.Module.BusinessObjects.File();
F.LoadFromStream(lasFile.FileName(), sr);
Guid fdGUID = Guid.NewGuid();
fD.FileDataID = fdGUID;
fD.ReferenceID = xG.GeophysicsID;
fD.TableType = "X_Geophysics";
fD.FileInfo = F;
fD.FileName = F.FileName;
fD.FileChecksum = Hash.ComputeHash(fD.FileBytes);
fD.MimeType = MimeTypes.MimeTypeHelper.GetMimeTypeByFileName(fD.FileName);
xG.OriginalFileDataID = fD.FileDataID;
physDataList.Add(xG);
fdDataList.Add(fD);
//fDataList.Add(F);
//entityObj.Geophysics.AddObject(xG);
//entityObj.SaveChanges();
// here we need to add a GeophysicsMetadata item for each column
Dictionary<string, Guid> metaDataIDLookup = new Dictionary<string, Guid>();
//var bulkCopyUnit = new SqlBulkCopy(BaseImportTools.XSTRING);
//bulkCopyUnit.DestinationTableName = "X_DictionaryUnit";
var unitDataList = new List<DictionaryUnit>();
//var bulkCopyParam = new SqlBulkCopy(BaseImportTools.XSTRING);
//bulkCopyParam.DestinationTableName = "X_Parameter";
var paramDataList = new List<Parameter>();
//var bulkCopyMeta = new SqlBulkCopy(BaseImportTools.XSTRING);
//bulkCopyMeta.DestinationTableName = "X_GeophysicsMetaData";
var metaDataList = new List<GeophysicsMetadata>();
foreach (string s in lasFile.columnHeaders)
{
Parameter xp = null;
xp = GetParameterIDFor(entityObj, "LAS data column", s);
if (xp == null)
{
xp = new Parameter();
Guid pg = Guid.NewGuid();
xp.ParameterID = pg;
xp.ParameterType = "LAS data column";
xp.ParameterName = s;
//test to see if we already have the unit
string splitter = s.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries).LastOrDefault().Trim();
UnitQueries uq2 = new UnitQueries();
DictionaryUnit xu2 = uq2.FindUnits(splitter);
DictionaryUnit unitFound = null;
//test to see if we already have added a unit into the list
if (unitDataList.Count > 0)
{
unitFound = unitDataList.Where(c => c.StandardUnitName == splitter).FirstOrDefault();
//add the unit id to the parameter list
if (unitFound != null)
{
xp.UnitID = unitFound.UnitID;
xp.Unit = unitFound;
}
}
if (xu2 == null && unitFound == null)
{
//create new unit here store it and pass to parameters
Guid ug = Guid.NewGuid();
DictionaryUnit du = new DictionaryUnit();
du.UnitID = ug;
du.StandardUnitName = splitter;
du.CoalUnitName = splitter;
du.StrictlySI = false;
unitDataList.Add(du);
//add the unit id to the parameter object
xp.UnitID = du.UnitID;
xp.Unit = du;
//entityObj.DictionaryUnits.AddObject(du);
//entityObj.SaveChanges();
}
paramDataList.Add(xp);
//entityObj.Parameters.AddObject(xp);
//entityObj.SaveChanges();
}
GeophysicsMetadata xgm = new GeophysicsMetadata();
xgm.GeophysicsID = gg;
Guid gmid = Guid.NewGuid();
xgm.GeophysicsMetadataID = gmid;
//xgm.Unit = xp.Unit.StandardUnitName;
xgm.Mnemonic = s;
xgm.ParameterID = xp.ParameterID;
metaDataList.Add(xgm);
//entityObj.GeophysicsMetadatas.AddObject(xgm);
metaDataIDLookup.Add(s, gmid);
}
//non async so we ensure the data gets written in the correct order
//bulkCopyUnit.WriteToServer(unitDataList.AsDataReader());
//bulkCopyParam.WriteToServer(paramDataList.AsDataReader());
//bulkCopyMeta.WriteToServer(metaDataList.AsDataReader());
//entityObj.SaveChanges();
int insertCounter = 0;
//var bulkCopyData = new SqlBulkCopy(BaseImportTools.XSTRING);
//bulkCopyData.DestinationTableName = "X_GeophysicsData";
var geoDataList = new List<GeophysicsData>();
foreach (LASDataRow ldr in lasFile.dataRows)
{
double depth = ldr.depth;
for (int i = 0; i < ldr.rowData.Count(); i++)
{
GeophysicsData xd1 = new GeophysicsData();
string s = lasFile.columnHeaders[i];
xd1.GeophysicsDataID = Guid.NewGuid();
Guid g = new Guid();
bool found = metaDataIDLookup.TryGetValue(s, out g);
if (found)
{
xd1.GeophysicsMetadataID = g;
}
xd1.Dimension = (decimal)depth;
xd1.MeasurementValue = (decimal)ldr.rowData[i];
geoDataList.Add(xd1);
}
insertCounter++;
rowCounter++;
}
//async here as all prereqs should be inplace, blast the data in, need to handle this better on program exit
//on exit test that all waiting sql server threads have completed
//bulkCopyData.WriteToServerAsync(geoDataList.AsDataReader());
dataList.Add(fdDataList);
dataList.Add(physDataList.ToList());
dataList.Add(unitDataList);
dataList.Add(paramDataList);
dataList.Add(metaDataList);
dataList.Add(geoDataList);
//IEnumerable<EntityObject> dataList = l.ConvertAll(obj => (EntityObject)obj);
//dataList.Concat(physDataList, fdDataList, unitDataList, paramDataList, metaDataIDLookup, geoDataList);
//dataList = dataList.Concat(physDataList);
//dataList.Concat(fdDataList);
//dataList.Concat(unitDataList);
//dataList.Concat(paramDataList);
//dataList.Concat(metaDataList);
//dataList.Concat(geoDataList);
}
catch (Exception ex) {
mos.errorMessages.Add("Failed to complete import of LAS file: " + origFilename);
mos.errorMessages.Add("Details: " + ex.Message.ToString());
if (ex.InnerException != null)
mos.errorMessages.Add("Inner Exception: " + ex.InnerException.Message.ToString());
mos.errorMessages.Add("Row: " + rowCounter);
}
mos.recordsAdded = rowCounter;
return dataList;
}
