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