public void AddVariables(List<VariablesModel> itemList, string entityConnectionString, string instanceIdentifier, out List<VariablesModel> listOfIncorrectRecords, out List<VariablesModel> listOfCorrectRecords, out List<VariablesModel> listOfDuplicateRecords, out List<VariablesModel> listOfEditedRecords)
{
listOfIncorrectRecords = new List<VariablesModel>();
listOfCorrectRecords = new List<VariablesModel>();
listOfDuplicateRecords = new List<VariablesModel>();
listOfEditedRecords = new List<VariablesModel>();
//var errorModel = new ErrorModel();
var context = new ODM_1_1_1EFModel.ODM_1_1_1Entities(entityConnectionString);
var objContext = ((IObjectContextAdapter)context).ObjectContext;
//read CV in to list for fster searching
var variableCV = context.VariableNameCVs.ToList();
var speciationCV = context.SpeciationCVs.ToList();
var units = context.Units.Distinct().ToDictionary(p => p.UnitsName.Trim(), p => p.UnitsID);
var sampleMediumCV = context.SampleMediumCVs.ToList();
var valueTypeCV = context.ValueTypeCVs.ToList();
var dataTypeCV = context.DataTypeCVs.ToList();
var generalCategoryCV = context.GeneralCategoryCVs.ToList();
//get all variables
var variablesInDatabase = context.Variables.Select(p => p.VariableCode.ToLower()).ToList();
var maxCount = itemList.Count;
var count = 0;
BusinessObjectsUtils.UpdateCachedprocessStatusMessage(instanceIdentifier, CacheName, String.Format(Ressources.IMPORT_STATUS_PROCESSING, count, maxCount));
foreach (var item in itemList)
{
try
{
BusinessObjectsUtils.UpdateCachedprocessStatusMessage(instanceIdentifier, CacheName, String.Format(Ressources.IMPORT_STATUS_PROCESSING, count, maxCount));
count++;
//var model = Mapper.Map<VariablesModel, Variable>(item);
var model = new Variable();
//set deafults
model.Speciation = "Not Applicable";
model.SampleMedium = "Unknown";
model.ValueType = "Unknown";
model.IsRegular = false;
model.TimeSupport = 0;
model.TimeUnitsID = 103;
model.DataType = "Unknown";
model.GeneralCategory = "Unknown";
model.NoDataValue = -9999;
var listOfErrors = new List<ErrorModel>();
var listOfUpdates = new List<UpdateFieldsModel>();
//need to look up Id's for VariableName, Speciation, VariableUnitsName, SampleMedium, ValueType, DataType, GeneralCategory, TimeUnitsName
//User has no concept of ID's
bool isRejected = false;
//VariableCode
if (!string.IsNullOrWhiteSpace(item.VariableCode))
{
if (RepositoryUtils.containsNotOnlyAllowedCaracters(item.VariableCode))
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "VariableCode")); listOfErrors.Add(err); isRejected = true;
}
else
{
model.VariableCode = item.VariableCode;
}
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "VariableCode")); listOfErrors.Add(err); isRejected = true;
}
//VariableName
if (!string.IsNullOrWhiteSpace(item.VariableName))
{
var variableName = variableCV
.Where(a => a.Term.ToString().ToLower() == item.VariableName.ToLower()).SingleOrDefault();
if (variableName == null)
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.VariableName, "variableName"));
listOfErrors.Add(err);
isRejected = true;
}
else
{
model.VariableName = variableName.Term;
}
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "VariableName")); listOfErrors.Add(err); isRejected = true;
}
//Speciation
if (!string.IsNullOrWhiteSpace(item.Speciation))
{
var speciation = speciationCV
.Where(a => a.Term.ToString().ToLower() == item.Speciation.ToLower()).SingleOrDefault();
if (speciation == null)
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.Speciation, "Speciation"));
listOfErrors.Add(err);
isRejected = true;
}
else
{
model.Speciation = item.Speciation;
}
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "Speciation")); listOfErrors.Add(err); isRejected = true;
}
//VariableUnitsName
if (!string.IsNullOrWhiteSpace(item.VariableUnitsName))
{
if (RepositoryUtils.containsSpecialCharacters(item.VariableUnitsName))
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "VariableUnitsName")); listOfErrors.Add(err); isRejected = true;
}
else
{
int result;
bool canConvert = UniversalTypeConverter.TryConvertTo<int>(item.VariableUnitsName, out result);
if (canConvert)//user used id
{
if (result != 0)
{
model.VariableUnitsID = result;
item.VariableUnitsID = result.ToString();
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "VariableUnitsName")); listOfErrors.Add(err); isRejected = true;
}
}
else
{
var variableUnitsID = units
.Where(a => a.Key.ToLower() == item.VariableUnitsName.ToLower())
.Select(a => a.Value)
.SingleOrDefault();
if (variableUnitsID != 0)
{
model.VariableUnitsID = variableUnitsID;
item.VariableUnitsID = variableUnitsID.ToString();
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "VariableUnitsName")); listOfErrors.Add(err); isRejected = true;
}
}
}
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "VariableUnitsName")); listOfErrors.Add(err); isRejected = true;
}
//SampleMedium
if (!string.IsNullOrWhiteSpace(item.SampleMedium))
{
var sampleMedium = sampleMediumCV
.Where(a => a.Term.ToString().ToLower() == item.SampleMedium.ToLower()).FirstOrDefault();
if (sampleMedium == null)
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.SampleMedium, "SampleMedium"));
listOfErrors.Add(err);
isRejected = true;
}
else
{
model.SampleMedium = sampleMedium.Term;
}
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "SampleMedium")); listOfErrors.Add(err); isRejected = true;
}
//ValueType
if (!string.IsNullOrWhiteSpace(item.ValueType))
{
var valueType = valueTypeCV
.Where(a => a.Term.ToString().ToLower() == item.ValueType.ToLower()).FirstOrDefault();
if (valueType == null)
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.ValueType, "ValueType"));
listOfErrors.Add(err);
isRejected = true;
}
else
{
model.ValueType = valueType.Term;
}
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "ValueType")); listOfErrors.Add(err); isRejected = true;
}
//IsRegular
if (!string.IsNullOrWhiteSpace(item.IsRegular))
{
bool result;
bool canConvert = UniversalTypeConverter.TryConvertTo<bool>(item.IsRegular, out result);
if (!canConvert)
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.IsRegular, "IsRegular"));
listOfErrors.Add(err);
isRejected = true;
}
else
{
model.IsRegular = result;
}
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "IsRegular")); listOfErrors.Add(err); isRejected = true;
}
//TimeSupport
if (!string.IsNullOrWhiteSpace(item.TimeSupport))
{
double result;
bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.TimeSupport, out result);
if (!canConvert)
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.TimeSupport, "TimeSupport"));
listOfErrors.Add(err);
isRejected = true;
}
else
{
model.TimeSupport = result;
}
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "TimeSupport")); listOfErrors.Add(err); isRejected = true;
}
//TimeUnitsID
if (!string.IsNullOrWhiteSpace(item.TimeUnitsName))
{
if (RepositoryUtils.containsSpecialCharacters(item.TimeUnitsName))
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "TimeUnitsName")); listOfErrors.Add(err); isRejected = true;
}
else
{
int result;
bool canConvert = UniversalTypeConverter.TryConvertTo<int>(item.TimeUnitsName, out result);
if (canConvert)//user used id
{
if (result != 0)
{
model.TimeUnitsID = result;
item.TimeUnitsID = result.ToString();
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "TimeUnitsName")); listOfErrors.Add(err); isRejected = true;
}
}
else
{
var timeUnitsID = units
.Where(a => a.Key.ToLower() == item.TimeUnitsName.ToLower())
.Select(a => a.Value)
.SingleOrDefault();
if (timeUnitsID != 0)
{
model.TimeUnitsID = timeUnitsID;
item.TimeUnitsID = timeUnitsID.ToString();
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "TimeUnitsName")); listOfErrors.Add(err); isRejected = true;
}
}
}
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "TimeUnitsName")); listOfErrors.Add(err); isRejected = true;
}
//DataType
if (!string.IsNullOrWhiteSpace(item.DataType))
{
var dataType = dataTypeCV
.Where(a => a.Term.ToString().ToLower() == item.DataType.ToLower()).FirstOrDefault();
if (dataType == null)
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.DataType, "DataType"));
listOfErrors.Add(err);
isRejected = true;
}
else
{
model.DataType = dataType.Term;
}
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "DataType")); listOfErrors.Add(err); isRejected = true;
}
//GeneralCategory
if (!string.IsNullOrWhiteSpace(item.GeneralCategory))
{
var generalCategory = generalCategoryCV
.Where(a => a.Term.ToString().ToLower() == item.GeneralCategory.ToLower()).FirstOrDefault();
if (generalCategory == null)
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.GeneralCategory, "GeneralCategory"));
listOfErrors.Add(err);
isRejected = true;
}
else
{
model.GeneralCategory = generalCategory.Term;
}
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "GeneralCategory")); listOfErrors.Add(err); isRejected = true;
}
//NoDataValue
if (!string.IsNullOrWhiteSpace(item.NoDataValue))
{
double result;
bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.NoDataValue, out result);
if (!canConvert)
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_FAILED_NOVALIDDATA, "NoDataValue"));
listOfErrors.Add(err);
isRejected = true;
}
else
{
model.NoDataValue = result;
}
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "NoDataValue")); listOfErrors.Add(err); isRejected = true;
}
if (isRejected)
{
var sb = new StringBuilder();
foreach (var er in listOfErrors)
{
sb.Append(er.ErrorMessage + ";");
}
item.Errors = sb.ToString();
listOfIncorrectRecords.Add(item);
continue;
}
//lookup duplicates
//var objectSet = objContext.CreateObjectSet<ODM_1_1_1EFModel.Variable>().EntitySet;//.EntitySet;
//check if item with this variablecode exists in the database
var doesExist = variablesInDatabase.Find(p => p == item.VariableCode.ToLower());
if (doesExist == null)
{
var existInUpload = listOfCorrectRecords.Exists(a => a.VariableCode == item.VariableCode);
if (!existInUpload)
{
//context.Sites.Add(model);
//context.SaveChanges();
listOfCorrectRecords.Add(item);
}
else
{
var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_ISDUPLICATE, "VariableCode")); listOfErrors.Add(err); isRejected = true;
listOfIncorrectRecords.Add(item);
item.Errors += err.ErrorMessage + ";";
}
}
else
{
var existingItem = context.Variables.Where(a => a.VariableCode == item.VariableCode).FirstOrDefault();
//if (existingItem.VariableCode != model.VariableCode) { listOfUpdates.Add(new UpdateFieldsModel("Variables", "VariableCode", existingItem.VariableCode.ToString(), item.VariableCode.ToString())); }
if (model.VariableName != null && existingItem.VariableName != model.VariableName) { listOfUpdates.Add(new UpdateFieldsModel("Variables", "VariableName", existingItem.VariableName.ToString(), item.VariableName.ToString())); }
if (model.Speciation != model.Speciation) { listOfUpdates.Add(new UpdateFieldsModel("Variables", "Speciation", existingItem.Speciation.ToString(), item.Speciation.ToString())); }
if (model.VariableUnitsID != model.VariableUnitsID) { listOfUpdates.Add(new UpdateFieldsModel("Variables", "VariableUnitsID", existingItem.Unit1.UnitsName.ToString(), item.VariableUnitsName.ToString())); }
if (model.SampleMedium != model.SampleMedium) { listOfUpdates.Add(new UpdateFieldsModel("Variables", "SampleMedium", existingItem.SampleMedium.ToString(), item.SampleMedium.ToString())); }
if (model.ValueType != model.ValueType) { listOfUpdates.Add(new UpdateFieldsModel("Variables", "ValueType", existingItem.ValueType.ToString(), item.ValueType.ToString())); }
if (model.IsRegular != model.IsRegular) { listOfUpdates.Add(new UpdateFieldsModel("Variables", "IsRegular", existingItem.IsRegular.ToString(), item.IsRegular.ToString())); }
if (model.TimeSupport != model.TimeSupport) { listOfUpdates.Add(new UpdateFieldsModel("Variables", "TimeSupport", existingItem.TimeSupport.ToString(), item.TimeSupport.ToString())); }
if (model.TimeUnitsID != model.TimeUnitsID) { listOfUpdates.Add(new UpdateFieldsModel("Variables", "TimeUnitsID", existingItem.Unit1.UnitsName, item.TimeUnitsName.ToString())); }
if (model.DataType != model.DataType) { listOfUpdates.Add(new UpdateFieldsModel("Variables", "DataType", existingItem.DataType.ToString(), item.DataType.ToString())); }
if (model.GeneralCategory != model.GeneralCategory) { listOfUpdates.Add(new UpdateFieldsModel("Variables", "GeneralCategory", existingItem.GeneralCategory.ToString(), item.GeneralCategory.ToString())); }
if (model.NoDataValue != model.NoDataValue) { listOfUpdates.Add(new UpdateFieldsModel("Variables", "NoDataValue", existingItem.NoDataValue.ToString(), item.NoDataValue.ToString())); }
if (listOfUpdates.Count() > 0)
{
listOfEditedRecords.Add(item);
var sb = new StringBuilder();
foreach (var u in listOfUpdates)
{
sb.Append(string.Format(Ressources.IMPORT_VALUE_UPDATED, u.ColumnName, u.CurrentValue, u.UpdatedValue + ";"));
}
item.Errors = sb.ToString();
continue;
}
else
{
listOfDuplicateRecords.Add(item);
}
}
}
catch (Exception ex)
{
listOfIncorrectRecords.Add(item);
}
}
return;
}
