private void ImportPipeComponentSheet(Array sheetValues, Worksheet worksheet, List<CellValue> cellValues)
{
try
{
RaiseMessage(MessageType.Added, "----------------------------------");
RaiseMessage(MessageType.Added, string.Format("Importing Sheet '{0}'.", worksheet.Name));
RaiseMessage(MessageType.Added, "----------------------------------");
int tagNameIndex = cellValues.Where(x => x.ExpectedValue == mTagNumberCellName).FirstOrDefault().Y;
int lineNumberIndex = cellValues.Where(x => x.ExpectedValue == mLineNumberCellName).FirstOrDefault().Y;
int pandIdIndex = cellValues.Where(x => x.ExpectedValue == mPandIDCellName).FirstOrDefault().Y;
int emptyPipesCount = 0;
int rowCount = sheetValues.GetUpperBound(0);
int columnCount = sheetValues.GetUpperBound(1);
int firstRowIndex = cellValues.Where(x => x.ExpectedValue == mPandIDCellName).FirstOrDefault().X + 1;
//Get Property Names and their column indexes
Dictionary<string, int> propertyPositions = new Dictionary<string, int>();
for (int colIndex = pandIdIndex + 1; colIndex <= columnCount; colIndex++)
{
string propertyName = sheetValues.GetValue(firstRowIndex, colIndex) == null ? string.Empty : sheetValues.GetValue(firstRowIndex, colIndex).ToString();
if (!string.IsNullOrEmpty(propertyName))
{
var property = (from x in mExistingPipeComponentProperties where x.Name.ToLower() == propertyName.Trim().ToLower() select x).FirstOrDefault();
if (property != null)
{
if (!propertyPositions.ContainsKey(property.Name))
{
propertyPositions.Add(property.Name, colIndex);
}
else
{
RaiseMessage(MessageType.Warning, string.Format("WorkSheet '{0}': Found duplicate property names '{1}'. Skipping Worksheet.", worksheet.Name, propertyName));
}
}
else
{
RaiseMessage(MessageType.Error, string.Format("WorkSheet '{0}': Property name '{1}' will be skipped as it does not exist in the Database.", worksheet.Name, propertyName));
}
}
}
for (int rowIndex = firstRowIndex; rowIndex <= rowCount; rowIndex++)
{
PipeComponent newPipeComponent = new PipeComponent();
string lineNumber = sheetValues.GetValue(rowIndex, lineNumberIndex) == null ? string.Empty : sheetValues.GetValue(rowIndex, lineNumberIndex).ToString();
string tagNumber = sheetValues.GetValue(rowIndex, lineNumberIndex) == null ? string.Empty : sheetValues.GetValue(rowIndex, tagNameIndex).ToString();
string drawing = sheetValues.GetValue(rowIndex, pandIdIndex) == null ? string.Empty : sheetValues.GetValue(rowIndex, pandIdIndex).ToString();
LineNumberParser lineNumberParser = new LineNumberParser(lineNumber);
if (!lineNumberParser.IsValid())
{
string msg = string.Format("Worksheet {0}, Row {1} : Pipe Number Format '{2}'. Reason: {3}", worksheet.Name, rowIndex, tagNumber, lineNumberParser.ErrorMessage);
RaiseMessage(MessageType.Error, msg);
continue;
}
TagNumberParser tagNumberParser = new TagNumberParser(tagNumber);
if (!tagNumberParser.IsValid())
{
string msg = string.Format("Worksheet {0}, Row {1} : Invalid Tag Number Format '{2}'. Reason: {3}", worksheet.Name, rowIndex, tagNumber, tagNumberParser.ErrorMessage);
RaiseMessage(MessageType.Error, msg);
continue;
}
if (GetPipe(worksheet, lineNumberParser, newPipeComponent, rowIndex))
{
emptyPipesCount++;
if (emptyPipesCount == 3)
{
RaiseMessage(MessageType.Warning, string.Format("WorkSheet '{0}' Line '{1}': Found 3 empty Pipes. Continuing on to next Sheet.", worksheet.Name, rowIndex));
break;
}
continue;
}
GetDrawing(worksheet, drawing, newPipeComponent, rowIndex);
AttachComponentProperties(worksheet, propertyPositions, sheetValues, newPipeComponent, rowIndex);
//SAVE
SavePipeComponent(worksheet, tagNumberParser, newPipeComponent, rowIndex);
}
RaiseMessage(MessageType.Added, string.Format("Finished importing Sheet '{0}'.", worksheet.Name));
}
catch (Exception ex)
{
RaiseMessage(MessageType.Error, string.Format("Error occured: {0}", ex.Message));
RaiseMessage(MessageType.Error, string.Format("Error Stack trace: {0}", ex.StackTrace));
}
RaiseMessage(MessageType.Added, "Finished Job");
}
private bool GetPipe(Worksheet worksheet, LineNumberParser parser, PipeComponent newPipeComponent, int rowIndex)
{
bool skipRow = false;
//Check if the Area exist in the database
Pipe pipe = (from x in mExistingPipes where string.Compare(parser.Name.Trim(), x.Name, true, CultureInfo.CurrentCulture) == 0 select x).FirstOrDefault();
if (pipe != null)
{
newPipeComponent.Pipe = pipe;
newPipeComponent.PipeId = pipe.Id;
}
else
{
RaiseMessage(MessageType.Error, string.Format("WorkSheet '{0}' Line '{1}': Could not find pipe '{2}' in database. Skipping this row.", worksheet.Name, rowIndex, parser.Name));
skipRow = true;
}
return skipRow;
}
private void ImportPipeSheet(Array sheetValues, Worksheet worksheet, List<CellValue> cellValues)
{
try
{
RaiseMessage(MessageType.Added, "----------------------------------");
RaiseMessage(MessageType.Added, string.Format("Importing Sheet '{0}'.", worksheet.Name));
RaiseMessage(MessageType.Added, "----------------------------------");
int lineNbrIndex = cellValues.Where(x => x.ExpectedValue == mLineNumberCellName).FirstOrDefault().Y;
int documentIndex = cellValues.Where(x => x.ExpectedValue == mPandIdCellName).FirstOrDefault().Y;
int emptyAreasCount = 0;
int rowCount = sheetValues.GetUpperBound(0);
int firstRowIndex = cellValues.Where(x => x.ExpectedValue == mPandIdCellName).FirstOrDefault().X + 1;
for (int rowIndex = firstRowIndex; rowIndex < rowCount; rowIndex++)
{
Pipe newPipe = new Pipe();
//split the Line Number using the '-' into parts
string lineNumber = sheetValues.GetValue(rowIndex, lineNbrIndex) == null ? string.Empty : sheetValues.GetValue(rowIndex, lineNbrIndex).ToString();
LineNumberParser parser = new LineNumberParser(lineNumber);
if (!parser.IsValid())
{
string invalidLineNumberMsg = string.Format("Worksheet {0}, Row {1} : Invalid Line Number Format '{2}'. Reason: {3}", worksheet.Name, rowIndex, lineNumber, parser.ErrorMessage);
RaiseMessage(MessageType.Error, invalidLineNumberMsg);
continue;
}
string document = sheetValues.GetValue(rowIndex, documentIndex) == null ? string.Empty : sheetValues.GetValue(rowIndex, documentIndex).ToString();
if (GetArea(worksheet, parser.AreaNumber, newPipe, rowIndex))
{
emptyAreasCount++;
if (emptyAreasCount == 3)
{
RaiseMessage(MessageType.Warning, string.Format("WorkSheet '{0}' Line '{1}': Found 3 empty Areas. Continuing on to next Sheet.", worksheet.Name, rowIndex));
break;
}
continue;
}
if (GetSeqNo(worksheet, parser.SequenceNumber, newPipe, rowIndex))
continue;
if (GetPipeClass(worksheet, parser.Spec, newPipe, rowIndex))
continue;
if (GetPipeSize(worksheet, parser.Size.ToString(), newPipe, rowIndex))
continue;
if (GetFluidCode(worksheet, parser.FluidCode, "", "", newPipe, rowIndex))
continue;
if (GetSpecialFeature(worksheet, parser.SpecialFeature, newPipe, rowIndex))
continue;
GetDocument(worksheet, document, newPipe, rowIndex);
//GetFrom(worksheet, from, newPipe, rowIndex);
//GetTo(worksheet, to, newPipe, rowIndex);
//check if the same pipe already exist
if (mImportResult.Where(x =>
x.AreaId == newPipe.AreaId &&
x.SequenceNo == newPipe.SequenceNo &&
x.ClassId == newPipe.ClassId &&
x.SizeId == newPipe.SizeId &&
x.FluidCodeId == newPipe.FluidCodeId &&
x.SpecialFeatureId == newPipe.SpecialFeatureId).FirstOrDefault() == null)
{
newPipe.CategoryId = mPipeRelatedObjects.PipeCategories[0].Id;
mCee.Pipes.AddObject(newPipe);
//|---- S A V E ---|
mCee.SaveChanges();
RaiseMessage(MessageType.Info, string.Format("WorkSheet '{0}' Line '{1}': Pipe '{2}' added.", worksheet.Name, rowIndex, newPipe.Name));
mImportResult.Add(newPipe);
}
else
{
RaiseMessage(MessageType.Warning, string.Format("WorkSheet '{0}' Line '{1}': Pipe {2}' already exist. Skipping row.", worksheet.Name, rowIndex, parser.Name));
}
}
RaiseMessage(MessageType.Added, string.Format("Finished importing Sheet '{0}'.", worksheet.Name));
}
catch (Exception ex)
{
RaiseMessage(MessageType.Error, string.Format("Error occured: {0}", ex.Message));
RaiseMessage(MessageType.Error, string.Format("Error Stack trace: {0}", ex.StackTrace));
}
}