public override DbImportResult Import(bool canCreateProperties = false)
{
if (MetaData.ImportType != CommonUtils.ImportType.CreatePipeComponent && MetaData.ImportType != CommonUtils.ImportType.UpdatePipeComponent)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = canCreateProperties;
DbImportResult = new DbImportResult();
mExistingComponents = (from x in Cee.PipeComponents
.Include("PipeComponentType")
select x).ToList();
mExistingComponentTypes = Cee.PipeComponentTypes.ToList();
mExistingComponentProperties = Cee.PipeProperties.ToList();
mExistingEquipmentComponentTypeProperty = (from x in Cee.PipeComponentTypeProperties.Include("PipeComponentType") select x).ToList();
mExistingPropertyValues = Cee.PipePropertyValues.ToList();
mExistingManufacturers = (from x in Cee.Manufacturers.Include("Models") where x.EquipmentTypeId == (int) CommonUtils.EquipmentTypeCode.PIPE select x).ToList();
string connString = BuildConnectionString(MetaData.FullFileName);
using (var excelConn = new OleDbConnection(connString))
{
IList<PipeComponentDataAdapter> importData = new List<PipeComponentDataAdapter>();
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = PipeComponentCommandText;
if (excelConn.State != ConnectionState.Open)
{
excelConn.Open();
}
List<string> spreadSheetPropertyNames = GetPropertyColumnHeadersFromDataSet(cmd, (int) PipeComponentColumn.Model + 1);
if (spreadSheetPropertyNames.Count != spreadSheetPropertyNames.Distinct().Count())
{
DbImportResult.ErrorMessages.Add(DuplicatePropertyNamesExistMessage());
return DbImportResult;
}
if (!WorkSheetCheckColumnNamesAreValid<PipeComponentColumn>(GetColumnHeadersFromDataSet(cmd, (int) PipeComponentColumn.Model)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
using (OleDbDataReader dr = cmd.ExecuteReader())
{
mEmptyPipeLineNbrCount = 0;
int rowNumber = 1;
while (dr.Read())
{
rowNumber++;
var adapter = new PipeComponentDataAdapter(dr, mExistingComponentProperties, spreadSheetPropertyNames, rowNumber, CanCreateProperties);
if (adapter.HasEmptyPipeComponentError)
{
mEmptyPipeLineNbrCount++;
if (mEmptyPipeLineNbrCount == 3)
{
RaiseMessage(CommonUtils.MessageType.Error, string.Format("Worksheet {0}, row {1}. The third blank 'Pipe Name' in a row. Skipping work sheet.", WorkSheetName, rowNumber));
}
}
if (!adapter.IsValid())
{
foreach (string errorMessage in adapter.ErrorMessages)
{
RaiseMessage(CommonUtils.MessageType.Error, errorMessage);
}
}
else
{
importData.Add(adapter);
}
}
excelConn.Close();
}
}
if (!importData.Any())
{
RaiseMessage(CommonUtils.MessageType.Warning, NoDataFoundForWorkSheetMessage());
return DbImportResult;
}
if (MetaData.ImportType == CommonUtils.ImportType.CreatePipeComponent)
{
InsertData(importData);
}
else if (MetaData.ImportType == CommonUtils.ImportType.UpdatePipeComponent)
{
UpdateData(importData);
}
}
catch (OleDbException ex)
{
DbImportResult.ErrorMessages.Add(ex.ToString());
return DbImportResult;
}
finally
{
if (excelConn.State == ConnectionState.Open)
{
excelConn.Close();
}
}
}
DbImportResult.ImportedCount = mSavedResults.Count;
return DbImportResult;
}
private bool GetPipe(PipeComponentDataAdapter adapter, int rowIndex, out Pipe pipeOut)
{
pipeOut = null;
int areaNumber = int.Parse(adapter.PipeAreaNumber);
int seq = int.Parse(adapter.PipeSequence);
Pipe pipe = (from x in Cee.Pipes
.Include("Area")
.Include("PipeClass")
.Include("PipeSize")
.Include("PipeFluidCode")
.Include("PipeSpecialFeature")
where
x.CategoryId == PIPECAT_NOTUSED
&& x.Area.AreaNumber == areaNumber
&& x.SequenceNo == seq
&& String.Equals(x.PipeClass.Name, adapter.PipeClass, StringComparison.CurrentCultureIgnoreCase)
&& String.Equals(x.PipeSize.Name, adapter.PipeSize, StringComparison.CurrentCultureIgnoreCase)
&& String.Equals(x.PipeFluidCode.Name, adapter.PipeFluidCode, StringComparison.CurrentCultureIgnoreCase)
&& String.Equals(x.PipeSpecialFeature.Name, adapter.PipeSpecialFeature, StringComparison.CurrentCultureIgnoreCase)
select x).FirstOrDefault();
if (pipe != null)
{
pipeOut = pipe;
}
else
{
RaiseMessage(CommonUtils.MessageType.Error, string.Format("WorkSheet '{0}' Line '{1}': Could not find pipe '{2}' in database. Skipping this row.", WorkSheetName, rowIndex, adapter.PipeTag));
return true;
}
return false;
}
private void BuildPropertyValues(PipeComponent componentIn, PipeComponentDataAdapter adapter, int index)
{
foreach (var pair in adapter.PropertyValues)
{
//DOES PROPERTY EXIST?
PipeProperty property = (from x in mExistingComponentProperties where x.Name.ToLower() == pair.Key.ToLower() select x).FirstOrDefault();
if (property == null)
{
if (CanCreateProperties)
{
property = new PipeProperty {Name = pair.Key, DefaultValue = pair.Value, Description = " (created by importer)."};
mExistingComponentProperties.Add(property); //update cache
}
else
{
//ERROR!
RaiseMessage(CommonUtils.MessageType.Error, string.Format("WorkSheet '{0}' Line '{1} Tag {2} Component Name {3}' : The property does not exist.", WorkSheetName, index, adapter.Tag, adapter.ComponentName));
continue;
}
}
//CHECK PipeEquipmentComponentTypeProperty Exists
PipeComponentTypeProperty equipmentComponentTypeProperty = null;
if (mExistingEquipmentComponentTypeProperty.Any())
{
equipmentComponentTypeProperty =
(from x in mExistingEquipmentComponentTypeProperty
where x.PipeComponentType.Name.ToLower() == componentIn.PipeComponentType.Name.ToLower()
&& x.PipeProperty.Name.ToLower() == property.Name.ToLower()
select x).FirstOrDefault();
}
if (equipmentComponentTypeProperty == null)
{
if (CanCreateProperties)
{
//CREATE JOIN ROW
equipmentComponentTypeProperty = new PipeComponentTypeProperty();
equipmentComponentTypeProperty.PipeComponentType = componentIn.PipeComponentType; //note: set the object!
equipmentComponentTypeProperty.PipeProperty = property; //not set the object!
mExistingEquipmentComponentTypeProperty.Add(equipmentComponentTypeProperty); //update cache
}
else
{
//ERROR!
RaiseMessage(CommonUtils.MessageType.Warning, string.Format("WorkSheet '{0}' Row '{1} Tag {2} Component Type {3}' : The property {4} does not belong to the Component Type.",
WorkSheetName, adapter.RowNumber, adapter.Tag, componentIn.PipeComponentType.Name, property.Name));
continue;
}
}
property.PipeComponentTypeProperties.Add(equipmentComponentTypeProperty);
//CHECK PROPERTYVALUE EXISTS
PipePropertyValue propertyValue = null;
if (mExistingPropertyValues.Any())
{
propertyValue = (from x in mExistingPropertyValues
where x.PipeComponent.Name.ToLower() == componentIn.Name.ToLower()
&& x.PipeProperty.Name.ToLower() == property.Name.ToLower()
select x).FirstOrDefault();
}
if (propertyValue == null)
{
propertyValue = new PipePropertyValue();
propertyValue.PipeComponent = componentIn;
propertyValue.PipeProperty = property;
mExistingPropertyValues.Add(propertyValue); //update cache
}
//set value
if (!string.IsNullOrEmpty(pair.Value))
{
propertyValue.Value = pair.Value.ChangeNullToEmptyString();
}
componentIn.PipePropertyValues.Add(propertyValue);
}
}
private bool ChangeManufacturerModel(PipeComponentDataAdapter adapter, PipeComponent newComponent)
{
//CHANGING Manufacturer
if (!string.IsNullOrEmpty(adapter.Manufacturer))
{
if (adapter.Manufacturer.ToLower() != "null")
{
Manufacturer manufactuer = (from x in mExistingManufacturers where x.Name.ToLower() == adapter.Manufacturer.ToLower() select x).FirstOrDefault();
if (manufactuer == null)
{
RaiseMessage(CommonUtils.MessageType.Error, string.Format(BuildItemNotFoundInDatabaseMessage("Manufacturer", adapter.Manufacturer, adapter.RowNumber)));
return false;
}
newComponent.Manufacturer = manufactuer;
newComponent.ManufacturerId = manufactuer.Id;
if (adapter.Model.ToLower() != "null")
{
//CHANGING Model
if (!string.IsNullOrEmpty(adapter.Model))
{
Model model = (from x in manufactuer.Models where x.Name.ToLower() == adapter.Model.ToLower() select x).FirstOrDefault();
if (model == null)
{
RaiseMessage(CommonUtils.MessageType.Error, string.Format(BuildItemNotFoundInDatabaseMessage("Model", adapter.Model, adapter.RowNumber)));
return false;
}
newComponent.Model = model;
newComponent.ModelId = model.Id;
}
}
else
{
newComponent.Model = null;
newComponent.ModelId = null;
}
}
else
{
newComponent.Manufacturer = null;
newComponent.ManufacturerId = null;
newComponent.Model = null;
newComponent.ModelId = null;
}
}
return false;
}
private int GetParentId(BaseAttachmentDto dto, CmsEntities cee)
{
if (dto.Type.ToLower() == CommonUtils.EntityType.Document.ToString().ToLower())
{
int match = (from x in cee.Documents where x.Name.ToLower() == dto.Tag.ToLower() select x.Id).FirstOrDefault();
if (match > 0)
{
return match;
}
}
else if (dto.Type.ToLower() == CommonUtils.EntityType.Electrical.ToString().ToLower())
{
int match = (from x in cee.ElectricalEquipments where x.Name.ToLower() == dto.Tag.ToLower() select x.Id).FirstOrDefault();
if (match > 0)
{
return match;
}
}
else if (dto.Type.ToLower() == CommonUtils.EntityType.Mechanical.ToString().ToLower())
{
int match = (from x in cee.MechanicalEquipments where x.Name.ToLower() == dto.Tag.ToLower() select x.Id).FirstOrDefault();
if (match > 0)
{
return match;
}
}
else if (dto.Type.ToLower() == CommonUtils.EntityType.MobilePlant.ToString().ToLower())
{
int match = (from x in cee.MobilePlants where x.Name.ToLower() == dto.Tag.ToLower() select x.Id).FirstOrDefault();
if (match > 0)
{
return match;
}
}
else if (dto.Type.ToLower() == CommonUtils.EntityType.Instrument.ToString().ToLower())
{
int match = (from x in cee.Instruments where x.Name.ToLower() == dto.Tag.ToLower() select x.Id).FirstOrDefault();
if (match > 0)
{
return match;
}
}
else if (dto.Type.ToLower() == CommonUtils.EntityType.Issue.ToString().ToLower())
{
int result;
int match = 0;
if (int.TryParse(dto.Tag, out result))
{
match = (from x in cee.Issues where x.Id == result select x.Id).FirstOrDefault();
}
if (match > 0)
{
return match;
}
}
else if (dto.Type.ToLower() == CommonUtils.EntityType.Pipe.ToString().ToLower())
{
PipeComponentDataAdapter adapter = new PipeComponentDataAdapter(dto.Tag);
if (!adapter.ErrorMessages.Any())
{
int pipeAreaNumber = int.Parse(adapter.PipeAreaNumber);
int pipeSequence = int.Parse(adapter.PipeSequence);
int match = (from x in cee.Pipes
where x.PipeClass.Name == adapter.PipeClass.ToLower()
&& x.PipeFluidCode.Name==adapter.PipeFluidCode.ToLower()
&& x.PipeSize.Name == adapter.PipeSize
&& x.PipeSpecialFeature.Name==adapter.PipeSpecialFeature
&& x.Area.AreaNumber == pipeAreaNumber
&& x.SequenceNo == pipeSequence
select x.Id).FirstOrDefault();
if (match > 0)
{
return match;
}
}
else
{
DbImportResult.ErrorMessages.AddRange(adapter.ErrorMessages);
}
}
else if (dto.Type.ToLower() == CommonUtils.EntityType.Control.ToString().ToLower())
{
int match = (from x in cee.ControlSystems where x.Name.ToLower() == dto.Tag.ToLower() select x.Id).FirstOrDefault();
if (match > 0)
{
return match;
}
}
return 0;
}