private void UpdateDynamicProperties(ComponentTestingPropertyDataAdapter adapter, ControlSystemComponent matchingComponent, int i)
{
foreach (DynamicProperty dynamicProperty in adapter.DynamicProperties.Where(x => !string.IsNullOrEmpty(x.PropertyValue)))
{
ControlSystemComponentTestingProperty matchProperty = (from x in Cee.ControlSystemComponentTestingProperties where string.Compare(x.Name, dynamicProperty.PropertyName, true, CultureInfo.CurrentCulture) == 0 select x).FirstOrDefault();
if (matchProperty == null)
{
RaiseMessage(CommonUtils.MessageType.Error, string.Format(BuildItemNotFoundInDatabaseMessage("ControlSystemComponentTestingProperty", dynamicProperty.PropertyName, i + 1)));
continue;
}
//is this property assocaited with the comp type?
ControlSystemComponentTypeTestingProperty matchCompTypeProperty = (from x in Cee.ControlSystemComponentTypeTestingProperties
where x.TestPropertyId == matchProperty.Id && x.ComponentTypeId == matchingComponent.ControlSystemComponentTypeId
select x).FirstOrDefault();
if (matchCompTypeProperty == null)
{
string controlSystemTypeName = (from x in Cee.ControlSystemComponentTypes where x.Id == matchingComponent.ControlSystemComponentTypeId select x.Name).FirstOrDefault();
string controlSystemPropertyName = (from x in Cee.ControlSystemTestingProperties where x.Id == matchProperty.Id select x.Name).FirstOrDefault();
string message = string.Format("Missing a entry in Database for the Table 'ControlSystemComponentTypeTestingProperty' Where ControlSystemComponentTyped = '{0}'({1}) And ControlSystemTestingPropertyId = '{2}' ({3}). Row {4}.",
matchingComponent.ControlSystemComponentTypeId, controlSystemTypeName, matchProperty.Id, controlSystemPropertyName, i);
RaiseMessage(CommonUtils.MessageType.Error, message);
continue;
}
ControlSystemComponentTestingPropertyValue matchPropertyValue = (from x in Cee.ControlSystemComponentTestingPropertyValues
where (x.TestPropertyId == matchProperty.Id) && (x.ComponentId == matchingComponent.Id)
select x).FirstOrDefault();
if (dynamicProperty.PropertyValue.ToLower().Trim() == NULLTEXT)
{
if (matchPropertyValue == null)
{
//do nothing
continue;
}
//delete
Cee.ControlSystemComponentTestingPropertyValues.Remove(matchPropertyValue);
}
else
{
if (PropertyValueToPropertyTypeMismatched(matchProperty.PropertyListId, matchProperty.Type, dynamicProperty, i + 1))
{
continue;
}
if (matchPropertyValue == null)
{
//create
matchPropertyValue = new ControlSystemComponentTestingPropertyValue
{
TestPropertyId = matchProperty.Id,
ComponentId = matchingComponent.Id,
Value = dynamicProperty.PropertyValue,
VerifiedUserDate = string.Format("{0} by {1}", DateTime.Now.ToString(@"dd/MM/yyyy hh:mm"), mUser.FirstLastName)
};
Cee.ControlSystemComponentTestingPropertyValues.Add(matchPropertyValue);
}
else
{
//update
matchPropertyValue.Value = dynamicProperty.PropertyValue;
matchPropertyValue.VerifiedUserDate = string.Format("{0} by {1}", DateTime.Now.ToString(@"dd/MM/yyyy hh:mm"), mUser.FirstLastName);
}
}
var pair = new PropertyNameComponentNamePair
{
ComponentName = matchingComponent.Name,
PropertyName = matchProperty.Name,
Value = dynamicProperty.PropertyValue
};
mSavedResults.Add(pair);
}
}
public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreateComponentTestingProperties && MetaData.ImportType != CommonUtils.ImportType.UpdateComponentTestingProperties)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
CanCreateProperties = true;
mUser = (from x in Cee.Users where x.Id == MetaData.UserId select x).FirstOrDefault();
if (mUser == null)
{
DbImportResult.ErrorMessages.Add(string.Format(BuildItemNotFoundInDatabaseMessage("UserId", MetaData.UserId.ToString(CultureInfo.CurrentCulture))));
return DbImportResult;
}
IList<ComponentTestingPropertyDataAdapter> importData = new List<ComponentTestingPropertyDataAdapter>();
string connString = BuildConnectionString(MetaData.FullFileName);
using (var excelConn = new OleDbConnection(connString))
{
try
{
using (var cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, TestingPropertyColumn.ControlSystemName);
excelConn.Open();
if (!WorkSheetCheckColumnNamesAreValid<TestingPropertyColumn>(GetColumnHeadersFromDataSet(cmd, (int)TestingPropertyColumn.ComponentName)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
const int STARTCOLUMN = (int)TestingPropertyColumn.ComponentName;
List<string> dynamicProperyNames = GetDynamicProperties(cmd, STARTCOLUMN);
int k = 1;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
k++;
try
{
var adapter = new ComponentTestingPropertyDataAdapter(dr, dynamicProperyNames, STARTCOLUMN + 1);
importData.Add(adapter);
}
catch (Exception ex)
{
DbImportResult.ErrorMessages.Add(string.Format("ComponentTestingPropertyDataAdapter row {0} - {1}", k, ex.Message));
}
}
excelConn.Close();
}
}
if (MetaData.ImportType == CommonUtils.ImportType.CreateComponentTestingProperties)
{
InsertData(importData);
}
else if (MetaData.ImportType == CommonUtils.ImportType.UpdateComponentTestingProperties)
{
UpdateData(importData);
}
DbImportResult.ImportedCount = mSavedResults.Count;
return DbImportResult;
}
catch (OleDbException ex)
{
DbImportResult.ErrorMessages.Add(ex.ToString());
return DbImportResult;
}
finally
{
if (excelConn.State == ConnectionState.Open)
{
excelConn.Close();
}
}
}
}
private void AddDynamicProperties(ComponentTestingPropertyDataAdapter adapter, ControlSystemComponent matchingComponent, int i)
{
foreach (DynamicProperty dynamicProperty in adapter.DynamicProperties)
{
ControlSystemComponentTestingProperty matchProperty = (from x in Cee.ControlSystemComponentTestingProperties where string.Compare(x.Name, dynamicProperty.PropertyName, true, CultureInfo.CurrentCulture) == 0 select x).FirstOrDefault();
if (matchProperty == null)
{
RaiseMessage(CommonUtils.MessageType.Error, string.Format(BuildItemNotFoundInDatabaseMessage("ControlSystemTestingProperty", dynamicProperty.PropertyName, i + 1)));
continue;
}
ControlSystemComponentTypeTestingProperty matchCompTypeProperty = (from x in Cee.ControlSystemComponentTypeTestingProperties
where x.TestPropertyId == matchProperty.Id && x.ComponentTypeId == matchingComponent.ControlSystemComponentTypeId
select x).FirstOrDefault();
if (matchCompTypeProperty == null)
{
string controlSystemTypeName = (from x in Cee.ControlSystemComponentTypes where x.Id == matchingComponent.ControlSystemComponentTypeId select x.Name).FirstOrDefault();
string controlSystemPropertyName = (from x in Cee.ControlSystemTestingProperties where x.Id == matchProperty.Id select x.Name).FirstOrDefault();
string message = string.Format("Missing a entry in Database for the Table 'ControlSystemComponentTypeTestingProperty' Where ControlSystemComponentTyped = '{0}'({1}) And ControlSystemTestingPropertyId = '{2}' ({3}). Row {4}.",
matchingComponent.ControlSystemComponentTypeId, controlSystemTypeName, matchProperty.Id, controlSystemPropertyName, i);
RaiseMessage(CommonUtils.MessageType.Error, message);
continue;
}
//check for duplicates properties on this component (not allowed).
int duplicateCount = (from x in Cee.ControlSystemComponentTestingPropertyValues where x.ComponentId == matchingComponent.ControlSystemComponentTypeId && x.TestPropertyId == matchProperty.Id select x.Id).Count();
if (duplicateCount > 0)
{
string propertyName = matchProperty.Name;
string componentName = matchingComponent.Name;
string message = string.Format("An entry already exists in Database for the Table 'ControlSystemComponentTestingPropertyValue' Where ComponentId = '{0}'({1}) And ControlSystemTestingPropertyId = '{2}' ({3}). Row {4}.",
matchingComponent.ControlSystemComponentTypeId, componentName, matchProperty.Id, propertyName, i);
RaiseMessage(CommonUtils.MessageType.Error, message);
continue;
}
if (PropertyValueToPropertyTypeMismatched(matchProperty.PropertyListId, matchProperty.Type, dynamicProperty, i + 1))
{
continue;
}
var propertyValue = new ControlSystemComponentTestingPropertyValue
{
TestPropertyId = matchProperty.Id,
Value = dynamicProperty.PropertyValue,
VerifiedUserDate = string.Format("{0} by {1}", DateTime.Now.ToString(@"dd/MM/yyyy hh:mm"), mUser.FirstLastName)
};
matchingComponent.ControlSystemComponentTestingPropertyValues.Add(propertyValue);
var pair = new PropertyNameComponentNamePair
{
ComponentName = matchingComponent.Name,
PropertyName = matchProperty.Name,
Value = dynamicProperty.PropertyValue
};
mSavedResults.Add(pair);
}
}