public List<Pipe> ImportPipes()
{
try
{
RaiseMessage(MessageType.Added, "Loading exisiting Pipes and related objects.");
mPipeRelatedObjects = GetPipeRelatedObjects();
mImportResult = mCee.Pipes.ToList(); //source of truth is the db
mDocumentTypes = mCee.DocumentTypes.ToList();
int pidDocumentTypeId = (from x in mDocumentTypes where x.Code == "P&ID" select x.Id).FirstOrDefault();
mPidDocuments = (from x in mCee.Documents where x.DocumentTypeId == pidDocumentTypeId select x).ToList();
foreach (Worksheet worksheet in mWorkbook.Worksheets)
{
//Get Range
Range range = worksheet.UsedRange;
//Load all the values to the array
Array sheetValues = (Array) range.get_Value(XlRangeValueDataType.xlRangeValueDefault);
List<CellValue> cellValues;
if (OkToImportSheet(sheetValues, worksheet.Name, mPipeCellValuesToCheck, out cellValues))
{
ImportPipeSheet(sheetValues, worksheet, cellValues);
}
else
{
RaiseMessage(MessageType.Error, string.Format("Sheet '{0}' does not contain proper Pipe data.", 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));
}
return mImportResult;
}
private PipeRelatedObjects GetPipeRelatedObjects(CmsEntities cee)
{
cee.Configuration.LazyLoadingEnabled = false;
PipeRelatedObjects pipeRelatedObjects = new PipeRelatedObjects();
var areas = (from x in cee.Areas select x).ToList().OrderBy(x => x.Name).ToList();
var classes = (from x in cee.PipeClasses orderby x.Name select x).ToList();
var sizes = (from x in cee.PipeSizes select x).ToList().OrderBy(x => int.Parse(x.Name)).ToList();
var fluidCodes = (from x in cee.PipeFluidCodes orderby x.Name select x).ToList();
var categories = (from x in cee.PipeCategories orderby x.Name select x).ToList();
var specialFeatures = (from x in cee.PipeSpecialFeatures orderby x.Name select x).ToList();
pipeRelatedObjects.Areas = areas;
pipeRelatedObjects.PipeClasses = classes;
pipeRelatedObjects.PipeSizes = sizes;
pipeRelatedObjects.PipeFluidCodes = fluidCodes;
pipeRelatedObjects.PipeCategories = categories;
pipeRelatedObjects.PipeSpecialFeatures = specialFeatures;
return pipeRelatedObjects;
}
private PipeRelatedObjects GetPipeRelatedObjects()
{
PipeRelatedObjects pipeRelatedObjects = new PipeRelatedObjects();
var areas = (from x in mCee.Areas orderby x.AreaNumber select x).ToList();
var classes = (from x in mCee.PipeClasses orderby x.Name select x).ToList();
var sizes = (from x in mCee.PipeSizes select x).ToList().OrderBy(x => int.Parse(x.Name)).ToList();
var fluidCodes = (from x in mCee.PipeFluidCodes orderby x.Name select x).ToList();
var categories = (from x in mCee.PipeCategories orderby x.Name select x).ToList();
var specialFeatures = (from x in mCee.PipeSpecialFeatures orderby x.Name select x).ToList();
pipeRelatedObjects.Areas = areas;
pipeRelatedObjects.PipeClasses = classes;
pipeRelatedObjects.PipeSizes = sizes;
pipeRelatedObjects.PipeFluidCodes = fluidCodes;
pipeRelatedObjects.PipeCategories = categories;
pipeRelatedObjects.PipeSpecialFeatures = specialFeatures;
return pipeRelatedObjects;
}
private void UpdateData(IList<PipeDataAdapter> importData)
{
if (importData.Count == 0)
{
RaiseMessage(CommonUtils.MessageType.Warning, NoDataFoundForWorkSheetMessage());
return;
}
mPipeRelatedObjects = GetPipeRelatedObjects();
for (int i = 0; i < importData.Count; i++)
{
PipeDataAdapter adapter = importData[i];
if (!adapter.IsValid())
{
foreach (string message in adapter.ErrorMessages)
{
RaiseMessage(CommonUtils.MessageType.Error, message);
}
continue;
}
IList<string> pipeNames = (from x in importData select x.Name).ToList();
if (DuplicateItemNameFoundInExcell(adapter.Name, pipeNames, adapter.RowNumber))
{
continue;
}
int adapterAreaNumber;
if (int.TryParse(adapter.AreaNumber, out adapterAreaNumber) == false)
{
RaiseMessage(CommonUtils.MessageType.Error, string.Format(BuildItemNotInValidFormatInExcelMessage("PipeClass", adapter.AreaNumber, adapter.RowNumber)));
continue;
}
int adapterSequenceNumber;
if (int.TryParse(adapter.SequenceNumber, out adapterSequenceNumber) == false)
{
RaiseMessage(CommonUtils.MessageType.Error, string.Format(BuildItemNotInValidFormatInExcelMessage("SequenceNumber", adapter.SequenceNumber, adapter.RowNumber)));
continue;
}
Pipe pipe = Cee.Pipes.FirstOrDefault(x =>
x.Area.AreaNumber == adapterAreaNumber &&
x.SequenceNo == adapterSequenceNumber &&
x.PipeClass.Name == adapter.Class &&
x.PipeSize.Name == adapter.Size &&
x.PipeFluidCode.Name == adapter.FluidCode &&
x.PipeSpecialFeature.Name == adapter.SpecialFeature &&
x.PipeCategory.Id == PIPECAT_NOT_USEDID
);
if (pipe == null)
{
RaiseMessage(CommonUtils.MessageType.Error, string.Format(BuildItemNameDoesNotExistInDbMessage(adapter.Name, adapter.RowNumber)));
continue;
}
//get Foreign Keys - we always need these as they define which pipe to get...
//AREA
int areaId;
if (GetAreaFailed(adapter.AreaNumber, adapter.RowNumber, out areaId, false))
{
continue;
}
if (areaId > 0)
{
pipe.AreaId = areaId;
}
if (GetSeqNoFailed(adapter.SequenceNumber, pipe, adapter.RowNumber)) continue;
if (GetPipeClassFailed(adapter.Class, pipe, adapter.RowNumber)) continue;
if (GetPipeSizeFailed(adapter.Size, pipe, adapter.RowNumber)) continue;
if (GetFluidCodeFailed(adapter.FluidCode, pipe, adapter.RowNumber)) continue;
if (GetSpecialFeatureFailed(adapter.SpecialFeature, pipe, adapter.RowNumber)) continue;
//do nothing for this in an update. - it is no longer used. (JB)
//pipe.CategoryId = PIPECAT_NOT_USEDID;
//PIDDocumentId
int? pidId;
base.GetDocument(adapter.PandID, adapter.RowNumber, out pidId, true);
if (!pidId.HasValue)
{
pipe.PIDDocumentId = null;
}
else
{
if (pidId.Value > 0)
{
pipe.PIDDocumentId = pidId.Value;
}
}
GetFrom(adapter.From, pipe, adapter.RowNumber);
GetTo(adapter.To, pipe, adapter.RowNumber);
if (adapter.IsActive.HasValue)
{
pipe.IsActive = adapter.IsActive.Value;
}
pipe.Description = adapter.Description;
AddUpdateRevisionHistory(pipe);
mSavedResults.Add(pipe);
}
if (mSavedResults.Count == 0)
{
RaiseMessage(CommonUtils.MessageType.Warning, string.Format("No Pipes were processed from worksheet {0}.", WorkSheetName));
}
else
{
//SAVE
Cee.SaveChanges();
foreach (Pipe pipe in mSavedResults)
{
RaiseMessage(CommonUtils.MessageType.Added, string.Format("Processed Pipe '{0}'.", pipe.Name));
}
}
}
private void ImportPipesAsync_DoWork_old(object sender, DoWorkEventArgs e)
{
try
{
Application excel = new Application();
string fileName = fileNameTextBox.Text;
if (!String.IsNullOrEmpty(fileName) && File.Exists(fileName))
{
Workbook theWorkbook = excel.Workbooks.Open(openFileDialog.FileName, 0, true, 5,
"", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true);
AddMessage(MessageType.Added, String.Format("Loading exisiting Pipes and related objects."));
mPipeRelatedObjects = GetPipeRelatedObjects();
mExistingPipes = mCee.Pipes.ToList();
mDocumentTypes = mCee.DocumentTypes.ToList();
//mPidDocuments = (from x in mDocumentTypes where x.Name == "P&ID" select x.Documents).FirstOrDefault().ToList();
var pidDocumentTypeId = (from x in mDocumentTypes where x.Name == "P&ID" select x.Id).FirstOrDefault();
mPidDocuments = (from x in mCee.Documents where x.DocumentTypeId == pidDocumentTypeId select x).ToList();
foreach (Worksheet worksheet in theWorkbook.Worksheets)
{
//Get Range
Range range = worksheet.UsedRange;
//Worksheet worksheet = (Worksheet)sheets.get_Item(1);
//Load all the values to the array
Array sheetValues = (Array)range.get_Value(XlRangeValueDataType.xlRangeValueDefault);
List<CellValue> cellValues = null;
if (OkToImportSheet(sheetValues, worksheet.Name, mPipeCellValuesToCheck, out cellValues))
{
ImportPipeSheet(sheetValues, worksheet, cellValues);
}
else
{
AddMessage(MessageType.Error, String.Format("Sheet '{0}' does not contain proper Pipe data.", worksheet.Name));
}
}
}
else
{
AddMessage(MessageType.Error, String.Format("Wrong/missing import file '{0}'.", fileName));
}
}
catch (Exception ex)
{
AddMessage(MessageType.Error, String.Format("Error occured: {0}", ex.Message));
AddMessage(MessageType.Error, String.Format("Error Stack trace: {0}", ex.StackTrace));
}
}