private void SavePipeComponent(Worksheet worksheet, TagNumberParser parser, PipeComponent newPipeComponent, int rowIndex) { //Check Component Type Code exists PipeComponentType componentType = (from x in mExistingPipeComponentTypes where string.Compare(x.Code.Trim(), parser.ComponentTypeCode, true, CultureInfo.CurrentCulture) == 0 select x).FirstOrDefault(); if (componentType == null) { RaiseMessage(MessageType.Error, string.Format("WorkSheet '{0}' Line '{1}': Component Type Code '{2}' does not exist in Database. Skipping this row.", worksheet.Name, rowIndex, parser.ComponentTypeCode)); return; } //All good to Add new Pipe Component newPipeComponent.PipeComponentType = componentType; newPipeComponent.ComponentTypeId = componentType.Id; //now we can Check if the Pipe Component exist in the database using the 'Name' Property PipeComponent existingPipeComponent = mImportResult.Where(x => string.Compare(x.Name, parser.Name, true, CultureInfo.CurrentCulture) == 0).FirstOrDefault(); if (existingPipeComponent != null) { newPipeComponent = existingPipeComponent; return; // did not skip } //Check Area Numbers Match if (string.Compare(parser.AreaNumber.Trim(), newPipeComponent.Pipe.Area.AreaNumber.ToString(), true, CultureInfo.CurrentCulture) != 0) { RaiseMessage(MessageType.Error, string.Format("WorkSheet '{0}' Line '{1}': Component Area '{2}' does not match Pipe Area '{3}'. Skipping this row.", worksheet.Name, rowIndex, parser.AreaNumber, newPipeComponent.Pipe.Area.AreaNumber)); return; } newPipeComponent.SubArea = int.Parse(parser.SubArea); newPipeComponent.Number = parser.Number; mCee.PipeComponents.AddObject(newPipeComponent); //|--- S A V E try { mCee.SaveChanges(); } catch (Exception ex1) { RaiseMessage(MessageType.Error, string.Format("WorkSheet '{0}' Line '{1}': PipeComponent Name '{2}':- Exception thrown by program as follows: {3} {4}.", worksheet.Name, rowIndex, parser.Name, ex1, ex1.InnerException)); return; } mImportResult.Add(newPipeComponent); RaiseMessage(MessageType.Added, string.Format("WorkSheet '{0}' Line '{1}': Added Pipe Component Name '{2}'.", worksheet.Name, rowIndex, parser.Name)); }
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"); }