public void AddSites(List<SiteModel> itemList, string entityConnectionString, string instanceIdentifier, out List<SiteModel> listOfIncorrectRecords, out List<SiteModel> listOfCorrectRecords, out List<SiteModel> listOfDuplicateRecords, out List<SiteModel> listOfEditedRecords)
{
listOfIncorrectRecords = new List<SiteModel>();
listOfCorrectRecords = new List<SiteModel>();
listOfDuplicateRecords = new List<SiteModel>();
listOfEditedRecords = new List<SiteModel>();
var context = new ODM_1_1_1EFModel.ODM_1_1_1Entities(entityConnectionString);
//var objContext = ((IObjectContextAdapter)context).ObjectContext;
var LatLongDatum = context.SpatialReferences.ToDictionary(p => p.SRSName.Trim(), p => p.SpatialReferenceID);
var LatLongDatumSRSID = context.SpatialReferences.Where(p => p.SRSID != null).ToDictionary(p => p.SRSID, p => p.SpatialReferenceID);
var VerticalDatumCV = context.VerticalDatumCVs.ToList();
var SiteTypeCV = context.SiteTypeCVs.ToList();
//get all sites
var sitesInDatabase = context.Sites.Select(p => p.SiteCode.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)
{
//var item = new ODM_1_1_1EFModel.Site();
try
{
BusinessObjectsUtils.UpdateCachedprocessStatusMessage(instanceIdentifier, CacheName, String.Format(Ressources.IMPORT_STATUS_PROCESSING, count, maxCount));
count++;
//var model = Mapper.Map<SiteModel, Site>(item);
var model = new Site();
bool isRejected = false;
var listOfErrors = new List<ErrorModel>();
var listOfUpdates = new List<UpdateFieldsModel>();
//SiteCode
if (!string.IsNullOrWhiteSpace(item.SiteCode))
{
if (RepositoryUtils.containsNotOnlyAllowedCaracters(item.SiteCode))
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "SiteCode")); listOfErrors.Add(err); isRejected = true;
}
else
{
model.SiteCode = item.SiteCode;
}
}
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "SiteCode")); listOfErrors.Add(err); isRejected = true;
}
//SiteName
if (!string.IsNullOrWhiteSpace(item.SiteName))
{
if (RepositoryUtils.containsSpecialCharacters(item.SiteName))
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "SiteName")); listOfErrors.Add(err); isRejected = true;
}
else
{
model.SiteName = item.SiteName;
}
}
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "SiteName")); listOfErrors.Add(err); isRejected = true;
}
//Latitude
if (!string.IsNullOrWhiteSpace(item.Latitude))
{
double result;
bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.Latitude, out result);
if (!canConvert)
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "Latitude")); listOfErrors.Add(err); isRejected = true;
}
else
{
if (result >= -90 && result <= 90) model.Latitude = result;
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDRANGE, "Latitude", "-90 to +90")); listOfErrors.Add(err); isRejected = true;
}
}
}
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "Latitude")); listOfErrors.Add(err); isRejected = true;
}
//Longitude
if (!string.IsNullOrWhiteSpace(item.Longitude))
{
double result;
bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.Longitude, out result);
if (!canConvert)
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "Longitude")); listOfErrors.Add(err); isRejected = true;
}
else
{
if (result >= -180 && result <= 180) model.Longitude = result;
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDRANGE, "Longitude", "-180 to +180")); listOfErrors.Add(err); isRejected = true;
}
}
}
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "Longitude")); listOfErrors.Add(err); isRejected = true;
}
//#####################
//LatLongDatumID
if (!string.IsNullOrWhiteSpace(item.LatLongDatumSRSName))
{
if (RepositoryUtils.containsSpecialCharacters(item.LatLongDatumSRSName))
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "LatLongDatumSRSName")); listOfErrors.Add(err); isRejected = true;
}
else
{
if (item.LatLongDatumSRSName.ToLower() == "unknown")
{
var unknownID = context.SpatialReferences.Where(p => p.SRSName.ToLower() == "unknown").Select(p => p.SpatialReferenceID).FirstOrDefault();
model.LatLongDatumID = unknownID;
item.LatLongDatumID = unknownID.ToString();// write back to viewmodel to not have to convert again when values are committed to DB
}
else
{
int result;
bool canConvert = UniversalTypeConverter.TryConvertTo<int>(item.LatLongDatumSRSName, out result);
if (canConvert)//user used SSRID
{
var LatLongDatumID = LatLongDatumSRSID
.Where(a => a.Key == result)
.Select(a => a.Value)
.SingleOrDefault();
if (LatLongDatumID != 0)
{
model.LatLongDatumID = LatLongDatumID;
item.LatLongDatumID = LatLongDatumID.ToString();// write back to viewmodel to not have to convert again when values are committed to DB
item.LatLongDatumSRSName = LatLongDatum.Where(a => a.Value == LatLongDatumID).Select(a => a.Key).FirstOrDefault();
}
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "LatLongDatumSRSName")); listOfErrors.Add(err); isRejected = true;
}
}
else
{
var LatLongDatumID = LatLongDatum
.Where(a => a.Key.ToLower() == item.LatLongDatumSRSName.ToLower())
.Select(a => a.Value)
.SingleOrDefault();
if (LatLongDatumID != 0)
{
model.LatLongDatumID = LatLongDatumID;
item.LatLongDatumID = LatLongDatumID.ToString();// write back to viewmodel to not have to convert again when values are comitted to DB
item.LatLongDatumSRSName = LatLongDatum.Where(a => a.Value == LatLongDatumID).Select(a => a.Key).FirstOrDefault();
}
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "LatLongDatumSRSName")); listOfErrors.Add(err); isRejected = true;
}
}
}
}
}
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "LatLongDatumSRSName")); listOfErrors.Add(err); isRejected = true;
}
//#####################
//Elevation_m
if (!string.IsNullOrWhiteSpace(item.Elevation_m))
{
double result;
bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.Elevation_m, out result);
if (!canConvert)
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "Elevation_m")); listOfErrors.Add(err); isRejected = true;
}
else
{
// if (result >= -180 && result <= 180)
model.Elevation_m = result;
// else
// {
// var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDRANGE, "Longitude", "-180 to +180")); listOfErrors.Add(err); isRejected = true;
// }
}
}
else
{
model.Elevation_m = null;
}
//VerticalDatum
if (!string.IsNullOrWhiteSpace(item.VerticalDatum))
{
if (RepositoryUtils.containsInvalidCharacters(item.VerticalDatum))
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "VerticalDatum")); listOfErrors.Add(err); isRejected = true;
}
else
{
var verticalDatum = VerticalDatumCV
.Where(a => a.Term.ToString().ToLower() == item.VerticalDatum.ToLower()).FirstOrDefault();
if (verticalDatum == null)
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.VerticalDatum, "VerticalDatum"));
listOfErrors.Add(err); isRejected = true;
}
else
{
model.VerticalDatum = verticalDatum.Term;
item.VerticalDatum = verticalDatum.Term;
}
}
}
else
{
model.VerticalDatum = null;
item.VerticalDatum = null;
}
//LocalX
if (!string.IsNullOrWhiteSpace(item.LocalX))
{
double result;
bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.LocalX, out result);
if (!canConvert)
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "LocalX")); listOfErrors.Add(err); isRejected = true;
}
else
{
model.LocalX = result;
}
}
else
{
model.LocalX = null;
}
//LocalY
if (!string.IsNullOrWhiteSpace(item.LocalY))
{
double result;
bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.LocalY, out result);
if (!canConvert)
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "LocalY")); listOfErrors.Add(err); isRejected = true;
}
else
{
model.LocalY = result;
}
}
else
{
model.LocalY = null;
}
//LocalProjectionID
if (!string.IsNullOrWhiteSpace(item.LocalProjectionSRSName))
{
if (RepositoryUtils.containsSpecialCharacters(item.LocalProjectionSRSName))
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "LocalProjectionSRSName")); listOfErrors.Add(err); isRejected = true;
}
else
{
if (item.LatLongDatumSRSName.ToLower() == "unknown")
{
var unknownID = context.SpatialReferences.Where(p => p.SRSName.ToLower() == "unknown").Select(p => p.SpatialReferenceID).FirstOrDefault();
model.LatLongDatumID = unknownID;
item.LatLongDatumID = unknownID.ToString();// write back to viewmodel to not have to convert again when values are committed to DB
}
else
{
int result;
bool canConvert = UniversalTypeConverter.TryConvertTo<int>(item.LocalProjectionSRSName, out result);
if (canConvert)//user used SSRID
{
var localDatumID = LatLongDatumSRSID
.Where(a => a.Key == result)
.Select(a => a.Value)
.SingleOrDefault();
if (localDatumID != 0)
{
model.LocalProjectionID = localDatumID;
item.LocalProjectionID = localDatumID.ToString();// write back to viewmodel to not have to convert again when values are comitted to DB
item.LocalProjectionSRSName = LatLongDatum.Where(a => a.Value == localDatumID).Select(a => a.Key).FirstOrDefault();
}
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "LocalProjectionSRSName")); listOfErrors.Add(err); isRejected = true;
}
}
else
{
var localDatumID = LatLongDatum
.Where(a => a.Key.ToLower() == item.LocalProjectionSRSName.ToLower())
.Select(a => a.Value)
.SingleOrDefault();
if (localDatumID != 0)
{
model.LocalProjectionID = localDatumID;
item.LocalProjectionID = localDatumID.ToString();// write back to viewmodel to not have to convert again when values are comitted to DB
item.LocalProjectionSRSName = LatLongDatum.Where(a => a.Value == localDatumID).Select(a => a.Key).FirstOrDefault();
}
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "LocalProjectionSRSName")); listOfErrors.Add(err); isRejected = true;
}
}
}
}
}
else
{
model.LocalProjectionID = null;
}
//PosAccuracy_m
if (!string.IsNullOrWhiteSpace(item.PosAccuracy_m))
{
double result;
bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.PosAccuracy_m, out result);
if (!canConvert)
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "PosAccuracy_m")); listOfErrors.Add(err); isRejected = true;
}
else
{
// if (result >= -180 && result <= 180)
model.PosAccuracy_m = result;
// else
// {
// var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDRANGE, "Longitude", "-180 to +180")); listOfErrors.Add(err); isRejected = true;
// }
}
}
else
{
model.PosAccuracy_m = null;
}
//State
if (!string.IsNullOrWhiteSpace(item.State))
{
if (RepositoryUtils.containsSpecialCharacters(item.State))
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "State")); listOfErrors.Add(err); isRejected = true;
}
else
{
model.State = item.State;
}
}
else
{
model.State = null;
item.State = null;
}
//County
if (!string.IsNullOrWhiteSpace(item.County))
{
if (RepositoryUtils.containsSpecialCharacters(item.County))
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "County")); listOfErrors.Add(err); isRejected = true;
}
else
{
model.County = item.County;
}
}
else
{
model.County = null;
item.County = null;
}
//Comments
if (!string.IsNullOrWhiteSpace(item.Comments))
{
if (RepositoryUtils.containsSpecialCharacters(item.Comments))
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "Comments")); listOfErrors.Add(err); isRejected = true;
}
else
{
model.Comments = item.Comments;
}
}
else
{
model.Comments = null;
item.Comments = null;
}
//SiteType
if (!string.IsNullOrWhiteSpace(item.SiteType))
{
//var siteType = SiteTypeCV
// .Exists(a => a.Term.ToString().ToLower() == item.SiteType.ToLower());
var siteType = SiteTypeCV
.Where(a => a.Term.ToString().ToLower() == item.SiteType.ToLower()).FirstOrDefault();
if (siteType == null)
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.SiteType, "SiteType"));
listOfErrors.Add(err);
isRejected = true;
}
else
{
//using this insures correct spelling
model.SiteType = siteType.Term;
item.SiteType = siteType.Term;
}
}
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "SiteType")); listOfErrors.Add(err); isRejected = true;
}
//general rules check If one of these fields are included, then so should the rest: LocalX, LocalY, LocalSRSName
if (model.LocalX != null || model.LocalY != null || model.LocalProjectionID != null)
{
if (!(model.LocalX != null && model.LocalY != null && model.LocalProjectionID != null))
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_LOCALVALUE_NOT_COMPLETE)); listOfErrors.Add(err); isRejected = true;
}
}
if (model.Elevation_m != null )
{
if (model.VerticalDatum == null)
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_ELEVATION_VERTICALDATUM)); 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;
}
//check for duplicates first in database then in upload if a duplicate site is found the record will be rejected.
//check in list
var doesExist = sitesInDatabase.Find(p =>p == item.SiteCode.ToLower());
//var j = context.Sites.Find(s.SiteCode);
if (doesExist == null)
{
var existInUpload = listOfCorrectRecords.Exists(a => a.SiteCode.ToLower() == item.SiteCode.ToLower());
if (!existInUpload)
{
//context.Sites.Add(model);
//context.SaveChanges();
listOfCorrectRecords.Add(item);
}
else
{
var err = new ErrorModel("AddSites", string.Format(Ressources.IMPORT_VALUE_ISDUPLICATE,"SiteCode")); listOfErrors.Add(err); isRejected = true;
listOfIncorrectRecords.Add(item);
item.Errors += err.ErrorMessage + ";";
}
}
else
{
// var editedFields = new List<string>();
//retrieve all DatabaseRepository from db
var existingItem = context.Sites.Where(a => a.SiteCode.ToLower() == item.SiteCode.ToLower()).FirstOrDefault();
//if (existingItem.SiteCode != model.SiteCode) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "SiteCode", existingItem.SiteCode.ToString(), item.SiteCode.ToString())); }
if (model.Latitude != null && existingItem.Latitude != model.Latitude) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "Latitude", existingItem.Latitude.ToString(), item.Latitude.ToString())); }
if (model.Longitude != null && existingItem.Longitude != model.Longitude) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "Longitude", existingItem.Longitude.ToString(), item.Longitude.ToString())); }
if (model.LatLongDatumID != null && existingItem.LatLongDatumID != model.LatLongDatumID) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "LatLongDatumSRSName", existingItem.SpatialReference.SRSName.ToString(), item.LatLongDatumSRSName.ToString())); }
if (model.Elevation_m != null && existingItem.Elevation_m != model.Elevation_m) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "Elevation_m", existingItem.Elevation_m.ToString(), item.Elevation_m.ToString())); }
if (model.VerticalDatum != null && existingItem.VerticalDatum != model.VerticalDatum) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "VerticalDatum", existingItem.VerticalDatum != null ? existingItem.VerticalDatum.ToString() : String.Empty, item.VerticalDatum.ToString())); }
if (model.LocalX != null && existingItem.LocalX != model.LocalX) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "LocalX", existingItem.LocalX.ToString(), item.LocalX.ToString())); }
if (model.LocalY != null && existingItem.LocalY != model.LocalY) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "LocalY", existingItem.LocalY.ToString(), item.LocalY.ToString())); }
if (model.LocalProjectionID != null && existingItem.LocalProjectionID != model.LocalProjectionID) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "LocalProjectionSRSName", existingItem.SpatialReference1 != null ? existingItem.SpatialReference1.ToString() : String.Empty, item.LocalProjectionSRSName.ToString())); }
if (model.PosAccuracy_m != null && existingItem.PosAccuracy_m != model.PosAccuracy_m) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "PosAccuracy_m", existingItem.PosAccuracy_m.ToString(), item.PosAccuracy_m.ToString())); }
if (model.State != null && existingItem.State != model.State) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "State", existingItem.State, item.State)); }
if (model.County != null && existingItem.County != model.County) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "County", existingItem.County, item.County)); }
if (model.Comments != null && existingItem.Comments != model.Comments) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "Comments", existingItem.Comments, item.Comments)); }
if (model.SiteType != null && existingItem.SiteType != model.SiteType) { listOfUpdates.Add(new UpdateFieldsModel("Sites", "SiteType", existingItem.SiteType, item.SiteType)); }
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);
}
//var modifiedEntries = this.ObjectStateManager.GetObjectStateEntries(EntityState.Modified);
}
////check if entry with this key exists
//object value;
//var key = Utils.GetEntityKey(objectSet, d);
//if (!objContext.TryGetObjectByKey(key, out value))
//{
// try
// {
// var objContext = ((IObjectContextAdapter)context).ObjectContext;
//objContext.Connection.Open();
//objContext.ExecuteStoreCommand("SET IDENTITY_INSERT [dbo].[Sites] ON");
//objContext.AddObject(objectSet.Name, d);
//objContext.SaveChanges();
// objContext.Connection.Close();
//}
//catch (Exception ex)
//{
// throw;
//}
//}
//else
//{
//context.MyEntities.Attach(myEntity);
// listOfDuplicateRecords.Add(s);
//}
}
//Datum not in CV
catch (KeyNotFoundException ex)
{
listOfIncorrectRecords.Add(item);
}
catch (Exception ex)
{
listOfIncorrectRecords.Add(item);
}
}
return;
}
