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;
        }
        public void AddCategories(List<CategoriesModel> itemList, string entityConnectionString, string instanceIdentifier, out List<CategoriesModel> listOfIncorrectRecords, out List<CategoriesModel> listOfCorrectRecords, out List<CategoriesModel> listOfDuplicateRecords, out List<CategoriesModel> listOfEditedRecords)
        {
            listOfIncorrectRecords = new List<CategoriesModel>();
            listOfCorrectRecords = new List<CategoriesModel>();
            listOfDuplicateRecords = new List<CategoriesModel>();
            listOfEditedRecords = new List<CategoriesModel>();

            var context = new ODM_1_1_1EFModel.ODM_1_1_1Entities(entityConnectionString);
            //var objContext = ((IObjectContextAdapter)context).ObjectContext;

            var variables = context.Variables.ToDictionary(p => p.VariableCode, p => p.VariableID);

            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
                {
                    //Updating status
                    BusinessObjectsUtils.UpdateCachedprocessStatusMessage(instanceIdentifier, "default", Ressources.IMPORT_STATUS_EXTRACTNG);

                    var model = new Category();
                    var listOfErrors = new List<ErrorModel>();
                    bool isRejected = false;

                    //Categories
                    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
                        {
                            if (variables.ContainsKey(item.VariableCode))
                            {
                                var variableId = variables[item.VariableCode];
                                //update model
                                model.VariableID = variableId;
                                item.VariableID = variableId.ToString();
                            }
                            else
                            {
                                var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.VariableCode, "Sites")); listOfErrors.Add(err); isRejected = true;

                                //continue;

                            }
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddCategories", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "VariableCode")); listOfErrors.Add(err); isRejected = true;
                    }
                    //DataValue
                    if (!string.IsNullOrWhiteSpace(item.DataValue))
                    {
                        double result;
                        bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.DataValue, out result);

                        if (!canConvert)
                        {
                            var err = new ErrorModel("AddCategories", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "DataValue")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            model.DataValue = result;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddCategories", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "DataValue")); listOfErrors.Add(err); isRejected = true;
                    }
                    //CategoryDescription
                    if (!string.IsNullOrWhiteSpace(item.CategoryDescription))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.CategoryDescription))
                        {
                            var err = new ErrorModel("AddCategories", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "CategoryDescription")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            model.CategoryDescription = item.CategoryDescription;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddCategories", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "CategoryDescription")); listOfErrors.Add(err); isRejected = true;
                    }

                    //var variablesID = variables
                    //                  .Where(a => a.Key == item.VariableCode)
                    //                  .Select(a => a.Value)
                    //                  .SingleOrDefault();
                    //if (variablesID == 0)
                    //{
                    //    var err = new ErrorModel("AddCategories", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.VariableCode, "Variables")); listOfErrors.Add(err); isRejected = true;
                    //}
                    //else
                    //{
                    //    model.VariableID = variablesID;
                    //}
                    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 existingItem = context.Categories.Where(a => a.VariableID == model.VariableID &&
                                                                                a.DataValue == model.DataValue &&
                                                                                a.CategoryDescription == model.CategoryDescription
                                                                                ).FirstOrDefault();

                    if (existingItem == null)
                    {
                        context.Categories.Add(model);
                        //context.SaveChanges();
                        listOfCorrectRecords.Add(item);
                    }
                    else
                    {
                        //no editing possible no unique field in upload
                        listOfDuplicateRecords.Add(item);
                    }

                }
                catch (Exception ex)
                {
                    listOfIncorrectRecords.Add(item);
                }

            }

            return;
        }
        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;
        }
        public void AddSources(List<SourcesModel> itemList, string entityConnectionString, string instanceIdentifier, out List<SourcesModel> listOfIncorrectRecords, out List<SourcesModel> listOfCorrectRecords, out List<SourcesModel> listOfDuplicateRecords, out List<SourcesModel> listOfEditedRecords)
        {
            listOfIncorrectRecords = new List<SourcesModel>();
            listOfCorrectRecords = new List<SourcesModel>();
            listOfDuplicateRecords = new List<SourcesModel>();
            listOfEditedRecords = new List<SourcesModel>();

            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 topicCategoryCV = context.TopicCategoryCVs.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 listOfErrors = new List<ErrorModel>();
                    var listOfUpdates = new List<UpdateFieldsModel>();
                    bool isRejected = false;
                    var source = new Source();

                    //set default values
                    string unk = "Unknown";
                    source.ContactName = unk;
                    source.Phone = unk;
                    source.Email = unk;
                    source.Phone = unk;
                    source.Address = unk;
                    source.City = unk;
                    source.State = unk;
                    source.ZipCode = unk;
                    source.Citation = unk;
                    source.MetadataID = 0;

                    var isometadata = new ISOMetadata();
                    isometadata.TopicCategory = unk;
                    isometadata.Title = unk;
                    isometadata.Abstract = unk;
                    isometadata.ProfileVersion = unk;
                    isometadata.MetadataLink = null;

                    //SourceCode
                    if (!string.IsNullOrWhiteSpace(item.SourceCode))
                    {
                        if (RepositoryUtils.containsNotOnlyAllowedCaracters(item.SourceCode))
                        {
                            var err = new ErrorModel("AddSource", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "SourceCode")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            source.SourceCode = item.SourceCode;
                        }

                    }
                    else
                    {
                        var err = new ErrorModel("AddSource", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "SourceCode")); listOfErrors.Add(err); isRejected = true;
                    }
                    //Organization
                    if (!string.IsNullOrWhiteSpace(item.Organization))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.Organization))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "Organization")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            source.Organization = item.Organization;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "Organization")); listOfErrors.Add(err); isRejected = true;
                    }
                    //SourceDescription
                    if (!string.IsNullOrWhiteSpace(item.SourceDescription))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.SourceDescription))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "SourceDescription")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            source.SourceDescription = item.SourceDescription;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "SourceDescription")); listOfErrors.Add(err); isRejected = true;
                    }
                    //SourceLink
                    if (!string.IsNullOrWhiteSpace(item.SourceLink))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.SourceLink))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "SourceLink")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            source.SourceLink = item.SourceLink;
                        }
                    }

                    //ContactName
                    if (!string.IsNullOrWhiteSpace(item.ContactName))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.ContactName))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "ContactName")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            source.ContactName = item.ContactName;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "ContactName")); listOfErrors.Add(err); isRejected = true;
                    }
                    //Phone
                    if (!string.IsNullOrWhiteSpace(item.Phone))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.Phone))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "Phone")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            source.Phone = item.Phone;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "Phone")); listOfErrors.Add(err); isRejected = true;
                    }
                    //Email
                    if (!string.IsNullOrWhiteSpace(item.Email))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.Email))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "Email")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            source.Email = item.Email;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "Email")); listOfErrors.Add(err); isRejected = true;
                    }
                    //Address
                    if (!string.IsNullOrWhiteSpace(item.Address))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.Address))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "Address")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            source.Address = item.Address;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "Address")); listOfErrors.Add(err); isRejected = true;
                    }
                    //City
                    if (!string.IsNullOrWhiteSpace(item.City))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.City))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "City")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            source.City = item.City;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "City")); listOfErrors.Add(err); isRejected = true;
                    }
                    //State
                    if (!string.IsNullOrWhiteSpace(item.State))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.State))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "State")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            source.State = item.State;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "State")); listOfErrors.Add(err); isRejected = true;
                    }
                    //ZipCode
                    if (!string.IsNullOrWhiteSpace(item.ZipCode))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.ZipCode))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "ZipCode")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            source.ZipCode = item.ZipCode;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "ZipCode")); listOfErrors.Add(err); isRejected = true;
                    }
                    //Citation
                    if (!string.IsNullOrWhiteSpace(item.Citation))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.Citation))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "Citation")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            source.Citation = item.Citation;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "Citation")); listOfErrors.Add(err); isRejected = true;
                    }

                    //TopicCategory
                    if (!string.IsNullOrWhiteSpace(item.TopicCategory))
                    {
                        var topicCategory = topicCategoryCV
                               .Exists(a => a.Term.ToString() == item.TopicCategory);
                        if (!topicCategory)
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.TopicCategory, "TopicCategory"));
                            listOfErrors.Add(err);
                            isRejected = true;
                        }
                        else
                        {
                            isometadata.TopicCategory = item.TopicCategory;
                        }

                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "TopicCategory")); listOfErrors.Add(err); isRejected = true;
                    }
                    //Title
                    if (!string.IsNullOrWhiteSpace(item.Title))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.Title))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "Title")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            isometadata.Title = item.Title;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "Title")); listOfErrors.Add(err); isRejected = true;
                    }
                    //Abstract
                    if (!string.IsNullOrWhiteSpace(item.Abstract))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.Abstract))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "Abstract")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            isometadata.Abstract = item.Abstract;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "Abstract")); listOfErrors.Add(err); isRejected = true;
                    }
                    //ProfileVersion
                    if (!string.IsNullOrWhiteSpace(item.ProfileVersion))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.ProfileVersion))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "ProfileVersion")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            isometadata.ProfileVersion = item.ProfileVersion;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "ProfileVersion")); listOfErrors.Add(err); isRejected = true;
                    }
                    //MetadataLink
                    if (!string.IsNullOrWhiteSpace(item.MetadataLink))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.MetadataLink))
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "MetadataLink")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            isometadata.MetadataLink = item.MetadataLink;
                        }
                    }

                    if (isRejected)
                    {
                        var sb = new StringBuilder();
                        foreach (var er in listOfErrors)
                        {
                            sb.Append(er.ErrorMessage + ";");
                        }
                        item.Errors = sb.ToString();
                        listOfIncorrectRecords.Add(item);
                        continue;
                    }

                    //Source contains info about metadata
                    //need to be added first so I can get the ID
                    var existingIsometadataItem = context.ISOMetadatas
                        .Where(a =>
                            a.TopicCategory == item.TopicCategory &&
                            a.Title == item.Title &&
                            a.Abstract == item.Abstract &&
                            a.ProfileVersion == item.ProfileVersion &&
                            a.MetadataLink == item.MetadataLink)
                        .FirstOrDefault();

                    if (existingIsometadataItem == null)
                    {

                        context.ISOMetadatas.Add(isometadata);
                        //context.SaveChanges();
                    }
                    else
                    {
                        //set source metatdata id to id of existing item
                        source.MetadataID = existingIsometadataItem.MetadataID;

                    }
                    //var metadataId = context.ISOMetadatas
                    //    .Where(a => a.MetadataLink == item.MetadataLink)
                    //    .Select(a => a.MetadataID)
                    //    .FirstOrDefault();
                    //var existingSourcesItem = context.Sources
                    //            .Where(a =>
                    //                a.SourceDescription == item.SourceDescription &&
                    //                a.SourceLink == item.SourceLink &&
                    //                a.ContactName == item.ContactName &&
                    //                a.Phone == item.Phone &&
                    //                a.Email == item.Email &&
                    //                a.Address == item.Address &&
                    //                a.City == item.City &&
                    //                a.State == item.State &&
                    //                a.ZipCode == item.ZipCode &&
                    //                a.Citation == item.Citation &&
                    //                a.ISOMetadata.TopicCategory == item.TopicCategory &&
                    //                a.ISOMetadata.Title == item.Title &&
                    //                a.ISOMetadata.Abstract == item.Abstract &&
                    //                a.ISOMetadata.ProfileVersion == item.ProfileVersion &&
                    //                a.ISOMetadata.MetadataLink == item.MetadataLink
                    //                ).FirstOrDefault();

                    var existingSourcesItem = context.Sources
                               .Where(a => a.SourceCode == source.SourceCode).FirstOrDefault();

                    if (existingSourcesItem == null)
                    {

                        var existInUpload = listOfCorrectRecords.Exists(a => a.SourceCode == item.SourceCode);
                        if (!existInUpload)
                        {
                            //update model
                            source.MetadataID = isometadata.MetadataID;

                            context.Sources.Add(source);
                            //context.SaveChanges();

                            listOfCorrectRecords.Add(item);
                        }
                        else
                        {
                            var err = new ErrorModel("AddSources", string.Format(Ressources.IMPORT_VALUE_ISDUPLICATE, "SourceCode")); listOfErrors.Add(err); isRejected = true;
                            listOfIncorrectRecords.Add(item);
                            item.Errors += err.ErrorMessage + ";";
                        }

                    }
                    else
                    {
                        if (existingSourcesItem.SourceCode != source.SourceCode) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "SourcesCode", existingSourcesItem.SourceCode.ToString(), item.SourceCode.ToString())); }
                        if (source.Organization != null && existingSourcesItem.Organization != source.Organization) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "Organization", existingSourcesItem.Organization.ToString(), item.Organization.ToString())); }
                        if (source.SourceDescription != null && existingSourcesItem.SourceDescription != source.SourceDescription) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "SourceDescription", existingSourcesItem.SourceDescription.ToString(), item.SourceDescription.ToString())); }
                        if (source.SourceLink != null && existingSourcesItem.SourceLink != source.SourceLink) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "SourceLink", existingSourcesItem.SourceLink.ToString(), item.SourceLink.ToString())); }
                        if (source.ContactName != null && existingSourcesItem.ContactName != source.ContactName) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "ContactName", existingSourcesItem.ContactName.ToString(), item.ContactName.ToString())); }
                        if (source.Phone != null && existingSourcesItem.Phone != source.Phone) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "Phone", existingSourcesItem.Phone.ToString(), item.Phone.ToString())); }
                        if (source.Email != null && existingSourcesItem.Email != source.Email) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "Email", existingSourcesItem.Email.ToString(), item.Email.ToString())); }
                        if (source.Address != null && existingSourcesItem.Address != source.Address) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "Address", existingSourcesItem.Address.ToString(), item.Address.ToString())); }
                        if (source.City != null && existingSourcesItem.City != source.City) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "City", existingSourcesItem.City.ToString(), item.City.ToString())); }
                        if (source.State != null && existingSourcesItem.State != source.State) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "State", existingSourcesItem.State.ToString(), item.State.ToString())); }
                        if (source.ZipCode != null && existingSourcesItem.ZipCode != source.ZipCode) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "ZipCode", existingSourcesItem.ZipCode.ToString(), item.ZipCode.ToString())); }
                        if (source.Citation != null && existingSourcesItem.Citation != source.Citation) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "Citation", existingSourcesItem.Citation.ToString(), item.Citation.ToString())); }
                        if (isometadata.TopicCategory != null && existingSourcesItem.ISOMetadata.TopicCategory != isometadata.TopicCategory) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "TopicCategory", existingSourcesItem.ISOMetadata.TopicCategory.ToString(), item.TopicCategory.ToString())); }
                        if (isometadata.Title != null && existingSourcesItem.ISOMetadata.Title != isometadata.Title) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "Title", existingSourcesItem.ISOMetadata.Title.ToString(), item.Title.ToString())); }
                        if (isometadata.Abstract != null && existingSourcesItem.ISOMetadata.Abstract != isometadata.Abstract) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "Abstract", existingSourcesItem.ISOMetadata.Abstract.ToString(), item.Abstract.ToString())); }
                        if (isometadata.ProfileVersion != null && existingSourcesItem.ISOMetadata.ProfileVersion != isometadata.ProfileVersion) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "ProfileVersion", existingSourcesItem.ISOMetadata.ProfileVersion.ToString(), item.ProfileVersion.ToString())); }
                        if (isometadata.MetadataLink != null && existingSourcesItem.ISOMetadata.MetadataLink != isometadata.MetadataLink) { listOfUpdates.Add(new UpdateFieldsModel("Sources", "MetadataLink", existingSourcesItem.ISOMetadata.MetadataLink.ToString(), item.MetadataLink.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;
        }
        public void AddSamples(List<SampleModel> itemList, string entityConnectionString, string instanceIdentifier, out List<SampleModel> listOfIncorrectRecords, out List<SampleModel> listOfCorrectRecords, out List<SampleModel> listOfDuplicateRecords, out List<SampleModel> listOfEditedRecords)
        {
            listOfIncorrectRecords = new List<SampleModel>();
            listOfCorrectRecords = new List<SampleModel>();
            listOfDuplicateRecords = new List<SampleModel>();
            listOfEditedRecords = new List<SampleModel>();

            var context = new ODM_1_1_1EFModel.ODM_1_1_1Entities(entityConnectionString);
            //prefetch Units for quick lookup
            //var labMethods = context.LabMethods.ToList();
            var sampleTypeCV = context.SampleTypeCVs.ToList();
            var labMethods = context.LabMethods.ToDictionary(p => p.LabMethodName, p => p.LabMethodID);
            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 listOfErrors = new List<ErrorModel>();
                    var listOfUpdates = new List<UpdateFieldsModel>();

                    bool isRejected = false;
                    var model = new Sample();

                    //set default values
                    string unk = "Unknown";
                    model.SampleType = unk;
                    model.LabMethodID = 0;

                    //SampleType
                    if (!string.IsNullOrWhiteSpace(item.SampleType))
                    {
                        var sampleType = sampleTypeCV
                               .Exists(a => a.Term.ToString() == item.SampleType);
                        if (!sampleType)
                        {
                            var err = new ErrorModel("AddSamples", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.SampleType, "SampleType"));
                            listOfErrors.Add(err);
                            isRejected = true;
                        }
                        else
                        {
                            model.SampleType = item.SampleType;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSamples", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "SampleType")); listOfErrors.Add(err); isRejected = true;
                    }

                    //LabSampleCode
                    if (!string.IsNullOrWhiteSpace(item.LabSampleCode))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.LabSampleCode))
                        {
                            var err = new ErrorModel("AddSamples", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "LabSampleCode")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                           model.LabSampleCode = item.LabSampleCode;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddSamples", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "LabSampleCode")); listOfErrors.Add(err); isRejected = true;
                    }

                    //LabMethodID
                    if (!string.IsNullOrWhiteSpace(item.LabMethodName))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.LabMethodName))
                        {
                            var err = new ErrorModel("AddSamples", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "LabMethodName")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            if (labMethods.ContainsKey(item.LabMethodName))
                               {
                                   var labMethodId = labMethods[item.LabMethodName];
                                        //update model
                                        model.LabMethodID = labMethodId;
                                        item.LabMethodID = labMethodId.ToString();
                                    }
                                    else
                                    {
                                        var err = new ErrorModel("AddSamples", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.LabMethodName, "LabMethodName")); listOfErrors.Add(err); isRejected = true;
                                    }
                                }

                    }
                    else
                    {
                        var err = new ErrorModel("AddSamples", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "LabMethodName")); 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;
                    }
                    //need to look up Id's for LabMethodId
                    //User has no concept of ID's
                    //lookup LabmethodId
                    //if (item.LabMethodName != null)
                    //{
                    //    if (labMethods.ContainsKey(item.LabMethodName))
                    //    {
                    //        var labMethodsId = labMethods[item.LabMethodName];
                    //        //update model
                    //        model.LabMethodID = labMethodsId;
                    //    }
                    //    else
                    //    {
                    //        //if CSV has LabMethidId specified convert and process
                    //        int labMethodId;
                    //        bool res = int.TryParse(item.LabMethodName, out labMethodId);
                    //        if (res)
                    //        {
                    //            //update model
                    //            model.LabMethodID = labMethodId;
                    //        }
                    //        else
                    //        {
                    //            listOfIncorrectRecords.Add(item);
                    //            continue;

                    //        }
                    //    }
                    //}
                    //else
                    //{
                    //    listOfIncorrectRecords.Add(item);
                    //    continue;
                    //}

                    //lookup duplicates
                    //var existingItem = context.Samples.Where(a => a.SampleType == model.SampleType &&
                    //                                              a.LabSampleCode == model.LabSampleCode &&
                    //                                              a.LabMethodID == a.LabMethodID
                    //                                              ).FirstOrDefault();

                    var existingItem = context.Samples.Where(a => a.LabSampleCode == model.LabSampleCode).FirstOrDefault();

                    if (existingItem == null)
                    {
                        var existInUpload = listOfCorrectRecords.Exists(a => a.LabSampleCode == item.LabSampleCode);
                        if (!existInUpload)
                        {
                            //context.Sites.Add(model);
                            //context.SaveChanges();
                            listOfCorrectRecords.Add(item);
                        }
                        else
                        {
                            var err = new ErrorModel("AddSample", string.Format(Ressources.IMPORT_VALUE_ISDUPLICATE, "LabSampleCode")); listOfErrors.Add(err); isRejected = true;
                            listOfIncorrectRecords.Add(item);
                            item.Errors += err.ErrorMessage + ";";
                        }
                    }
                    else
                    {
                        //if (existingItem.LabSampleCode != model.LabSampleCode) { listOfUpdates.Add(new UpdateFieldsModel("Sample", "LabSampleCode", existingItem.LabSampleCode.ToString(), item.LabSampleCode.ToString())); }
                        if (model.SampleType != null && existingItem.SampleType != model.SampleType) { listOfUpdates.Add(new UpdateFieldsModel("Sample", "SampleType", existingItem.SampleType.ToString(), item.SampleType.ToString())); }
                        if (model.LabMethodID != null && existingItem.LabMethodID != model.LabMethodID) { listOfUpdates.Add(new UpdateFieldsModel("Sample", "LabMethodID", existingItem.LabMethodID.ToString(), item.LabMethodID.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;
        }
        public void AddDataValues(List<DataValuesModel> itemList, string entityConnectionString, string instanceIdentifier, out List<DataValuesModel> listOfIncorrectRecords, out List<DataValuesModel> listOfCorrectRecords, out List<DataValuesModel> listOfDuplicateRecords, out List<DataValuesModel> listOfEditedRecords)
        {
            listOfIncorrectRecords = new List<DataValuesModel>();
            listOfCorrectRecords = new List<DataValuesModel>();
            listOfDuplicateRecords = new List<DataValuesModel>();
            listOfEditedRecords = new List<DataValuesModel>();

            //debug
            var timeTocomplete = new TimeSpan();
            var timeToRetrieveVars = new TimeSpan();
            var timeToFindDatavalues = new TimeSpan();
            var timeExistInUpload = new TimeSpan();
            var timeToFindDuplicates = new TimeSpan();

            int maxAllowedDuplicates = int.Parse(System.Configuration.ConfigurationManager.AppSettings["maxAllowedDuplicates"]);

            var recordsToInsert = new List<DataValue>();

            var startTime = DateTime.Now;

            var context = new ODM_1_1_1EFModel.ODM_1_1_1Entities(entityConnectionString);
            //context.Database.CommandTimeout = 10000;
            //var objContext = ((IObjectContextAdapter)context).ObjectContext;
            //get data to lookup values
            var sitesIds = context.Sites.ToDictionary(p => p.SiteCode, p => p.SiteID);
            var variablesIds = context.Variables.ToDictionary(p => p.VariableCode, p => p.VariableID);
            var offsetTypeIds = context.OffsetTypes.ToDictionary(p => p.OffsetTypeCode, p => p.OffsetTypeID);
            var censorCodeCV = context.CensorCodeCVs.ToList();
            var qualifierIds = context.Qualifiers.ToDictionary(p => p.QualifierCode, p => p.QualifierID);
            var methodIds = context.Methods.ToDictionary(p => p.MethodCode, p => p.MethodID);
            var sourceIds = context.Sources.ToDictionary(p => p.SourceCode, p => p.SourceID);
            var sampleIds = context.Samples.ToDictionary(p => p.LabSampleCode, p => p.SampleID);

            //var derivedFromIds = context.DerivedFroms.Select(p => p.DerivedFromID);
            var qualityControlLevelIds = context.QualityControlLevels.ToDictionary(p => p.QualityControlLevelCode, p => p.QualityControlLevelID);

            //get unique sitecodes to speed up search
            var uniqueSitecodes = itemList.GroupBy(g => g.SiteCode).Select(grp => new { SiteCode = grp.Key }).ToList();

            var maxCount = itemList.Count;
            var count = 0;

            var a_end = DateTime.Now;

            timeToRetrieveVars = a_end - startTime;
            Debug.WriteLine("timeToRetrieveVars:" + timeToRetrieveVars);
            try
            {
                foreach (var site in uniqueSitecodes)
                {
                    int currentSiteId = 0;
                    if (sitesIds.ContainsKey(site.SiteCode))
                    {
                        currentSiteId = sitesIds[site.SiteCode];
                        //update model

                    }
                    var a_start = DateTime.Now;
                    //var datavaluesWithSiteIDInDatabase = context.DataValues.Select(p => p).Where(f.Equals(currentSiteId)).ToList();
                    List<DataValue> datavaluesWithSiteIDInDatabase = (from d in context.DataValues.AsNoTracking()
                                                          where d.SiteID == currentSiteId
                                                          select d).ToList();
                    HashSet <DateTime> setDatetime = new HashSet<DateTime>(from d in context.DataValues.AsNoTracking()
                                                                    where d.SiteID == currentSiteId
                                                                    select d.DateTimeUTC);
                    HashSet<double> setDataValue = new HashSet<double>(from d in context.DataValues.AsNoTracking()
                                                                  where d.SiteID == currentSiteId
                                                                  select d.DataValue1);
                    HashSet<int> setVariableId = new HashSet<int>(from d in context.DataValues.AsNoTracking()
                                                                          where d.SiteID == currentSiteId
                                                                          select d.VariableID);
                    HashSet<int> setMethodId = new HashSet<int>(from d in context.DataValues.AsNoTracking()
                                                                  where d.SiteID == currentSiteId
                                                                  select d.MethodID);

                    a_end = DateTime.Now;

                    HashSet<DataValue> allValues = new HashSet<DataValue>((from d in context.DataValues.AsNoTracking()
                                                                           where d.SiteID == currentSiteId
                                                                           select d).ToList());

                    //List<HashSet<string>> lines = new List<HashSet<string>>(); //Hashset is very fast in searching duplicates
                    //datavaluesWithSiteIDInDatabase.ForEach(a=>
                    //                                            a.DataValue1.ToString() +
                    //                                            a.ValueAccuracy.ToString() +
                    //                                            a.LocalDateTime.Ticks.ToString() +
                    //                                            a.UTCOffset.ToString() +
                    //                                            a.DateTimeUTC.Ticks.ToString() +
                    //                                            a.SiteID.ToString() +
                    //                                            a.VariableID.ToString() +
                    //                                            a.OffsetValue.ToString() +
                    //                                            a.OffsetTypeID.ToString() +
                    //                                            a.CensorCode.ToString() +
                    //                                            a.QualifierID.ToString()+
                    //                                            a.MethodID.ToString() +
                    //                                            a.SourceID.ToString() +
                    //                                            a.SampleID.ToString() +
                    //                                            a.DerivedFromID.ToString() +
                    //                                            a.QualityControlLevelID.ToString()
                    //    )

                    var span = a_end - a_start;
                    timeToFindDatavalues.Add(span);
                    Debug.WriteLine("timeToRetrieve " + site.SiteCode + ": " + span);

                    BusinessObjectsUtils.UpdateCachedprocessStatusMessage(instanceIdentifier, CacheName, String.Format(Ressources.IMPORT_STATUS_PROCESSING, count, maxCount, listOfCorrectRecords.Count(), listOfIncorrectRecords.Count(), listOfDuplicateRecords.Count()));
                    #region loop through series
                    var filteredList = (from i in itemList
                                        where i.SiteCode == site.SiteCode
                                        select i);

                    //var distinctList = filteredList.Distinct().ToList();

                    //var duplicateList = from x in filteredList
                    //                    group x by x into grouped
                    //                    where grouped.Count() == 1
                    //                    select grouped.Key;

                    foreach (var item in filteredList)
                    {
                        try
                        {
                            BusinessObjectsUtils.UpdateCachedprocessStatusMessage(instanceIdentifier, CacheName, String.Format(Ressources.IMPORT_STATUS_PROCESSING, count, maxCount, listOfCorrectRecords.Count(), listOfIncorrectRecords.Count(), listOfDuplicateRecords.Count()));
                            count++;
                            #region data matching
                            bool isRejected = false;
                            var model = new DataValue();
                            var listOfErrors = new List<ErrorModel>();

                            //set default values
                            string unk = "Unknown";

                            model.ValueAccuracy = null;
                            model.OffsetValue = null;
                            model.OffsetTypeID = null;
                            model.CensorCode = "nc";
                            model.QualifierID = null;
                            model.MethodID = 0;
                            model.SampleID = null;
                            model.DerivedFromID = null;
                            model.QualityControlLevelID = -9999;

                            //DataValue
                            if (!string.IsNullOrWhiteSpace(item.DataValue))
                            {
                                double result;
                                bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.DataValue, out result);
                                //NaN can be converted properly ino a double sql field is a float and will not accept this so we need to test separately for NaN and reject
                                if (!canConvert || Double.IsNaN(result))
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "DataValue")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    model.DataValue1 = result;
                                }
                            }
                            else
                            {
                                var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "DataValue")); listOfErrors.Add(err); isRejected = true;
                            }
                            //ValueAccuracy
                            if (!string.IsNullOrWhiteSpace(item.ValueAccuracy))
                            {
                                double result;
                                bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.ValueAccuracy, out result);

                                if (!canConvert)
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "ValueAccuracy")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    model.ValueAccuracy = result;
                                }
                            }

                            //LocalDateTime
                            if (!string.IsNullOrWhiteSpace(item.LocalDateTime))
                            {
                                DateTime result;
                                bool canConvert = UniversalTypeConverter.TryConvertTo<DateTime>(item.LocalDateTime, out result);

                                if (!canConvert)
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "LocalDateTime")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    model.LocalDateTime = result;
                                }
                            }
                            else
                            {
                                var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "LocalDateTime")); listOfErrors.Add(err); isRejected = true;
                            }
                            //UTCOffset
                            if (!string.IsNullOrWhiteSpace(item.UTCOffset))
                            {
                                double result;
                                bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.UTCOffset, out result);

                                if (!canConvert)
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "UTCOffset")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    model.UTCOffset = result;
                                }
                            }
                            else
                            {
                                var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "UTCOffset")); listOfErrors.Add(err); isRejected = true;
                            }
                            //DateTimeUTC
                            if (!string.IsNullOrWhiteSpace(item.DateTimeUTC))
                            {
                                DateTime result;
                                bool canConvert = UniversalTypeConverter.TryConvertTo<DateTime>(item.DateTimeUTC, out result);

                                if (!canConvert)
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "DateTimeUTC")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    model.DateTimeUTC = result;
                                }
                            }
                            else
                            {
                                var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "DateTimeUTC")); listOfErrors.Add(err); isRejected = true;
                            }
                            //SiteCode
                            if (!string.IsNullOrWhiteSpace(item.SiteCode))
                            {
                                if (RepositoryUtils.containsSpecialCharacters(item.SiteCode))
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "SiteCode")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    if (sitesIds.ContainsKey(item.SiteCode))
                                    {
                                        var siteId = sitesIds[item.SiteCode];
                                        //update model
                                        model.SiteID = siteId;
                                        item.SiteID = siteId.ToString();
                                    }
                                    else
                                    {
                                        var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.SiteCode, "SiteCode")); listOfErrors.Add(err); isRejected = true;

                                        //continue;

                                    }

                                }
                            }
                            else
                            {
                                var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "SiteCode")); listOfErrors.Add(err); isRejected = true;
                            }
                            //VariableID
                            //VariableCode
                            if (!string.IsNullOrWhiteSpace(item.VariableCode))
                            {
                                if (RepositoryUtils.containsSpecialCharacters(item.VariableCode))
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "VariableCode")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    if (variablesIds.ContainsKey(item.VariableCode))
                                    {
                                        var variableId = variablesIds[item.VariableCode];
                                        //update model
                                        model.VariableID = variableId;
                                        item.VariableID = variableId.ToString();
                                    }
                                    else
                                    {
                                        var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.VariableCode, "VariableCode")); listOfErrors.Add(err); isRejected = true;

                                        //continue;

                                    }

                                }
                            }
                            else
                            {
                                var err = new ErrorModel("AddVariables", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "VariableCode")); listOfErrors.Add(err); isRejected = true;
                            }
                            //OffsetValue
                            if (!string.IsNullOrWhiteSpace(item.OffsetValue))
                            {
                                double result;
                                bool canConvert = UniversalTypeConverter.TryConvertTo<double>(item.OffsetValue, out result);

                                if (!canConvert)
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "OffsetValue")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    model.OffsetValue = result;
                                }
                            }
                            //OffsetTypeID
                            if (!string.IsNullOrWhiteSpace(item.OffsetTypeCode))
                            {

                                if (RepositoryUtils.containsSpecialCharacters(item.OffsetTypeCode))
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "OffsetTypeCode")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    if (offsetTypeIds.ContainsKey(item.OffsetTypeCode))
                                    {
                                        var offsetTypeId = offsetTypeIds[item.OffsetTypeCode];
                                        //update model
                                        model.OffsetTypeID = offsetTypeId;
                                        item.OffsetTypeID = offsetTypeId.ToString();
                                    }
                                    else
                                    {
                                        var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.OffsetTypeID, "OffsetType")); listOfErrors.Add(err); isRejected = true;
                                    }

                                }
                            }
                            //CensorCode
                            if (!string.IsNullOrWhiteSpace(item.CensorCode))
                            {
                                if (RepositoryUtils.containsSpecialCharacters(item.CensorCode))
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "CensorCode")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    var censorCode = censorCodeCV
                                                        .Where(a => a.Term.ToLower() == item.CensorCode.ToLower()).FirstOrDefault();
                                    if (censorCode != null)
                                    {
                                        model.CensorCode = item.CensorCode;
                                        item.CensorCode = censorCode.Term;
                                    }
                                    else
                                    {
                                        var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.CensorCode, "CensorCode")); listOfErrors.Add(err); isRejected = true;
                                    }
                                }
                            }
                            else
                            {
                                var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "CensorCode")); listOfErrors.Add(err); isRejected = true;
                            }

                            //QualifierID
                            if (!string.IsNullOrWhiteSpace(item.QualifierCode))
                            {

                                if (RepositoryUtils.containsSpecialCharacters(item.QualifierCode))
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "QualifierCode")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    if (qualifierIds.ContainsKey(item.QualifierCode))
                                    {
                                        var qualifierId = qualifierIds[item.QualifierCode];
                                        //update model
                                        model.QualifierID = qualifierId;
                                        item.QualifierID = qualifierId.ToString();
                                    }
                                    else
                                    {
                                        var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.QualifierID, "QualifierCode")); listOfErrors.Add(err); isRejected = true;
                                    }

                                }
                            }

                            //MethodID
                            if (!string.IsNullOrWhiteSpace(item.MethodCode))
                            {
                                if (RepositoryUtils.containsSpecialCharacters(item.MethodCode))
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "MethodCode")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    if (methodIds.ContainsKey(item.MethodCode))
                                    {
                                        var methodId = methodIds[item.MethodCode];
                                        //update model
                                        model.MethodID = methodId;
                                        item.MethodID = methodId.ToString();
                                    }
                                    else
                                    {
                                        var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.MethodID, "MethodCode")); listOfErrors.Add(err); isRejected = true;
                                    }

                                }
                            }
                            else
                            {
                                var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "MethodCode")); listOfErrors.Add(err); isRejected = true;
                            }
                            //SourceID
                            if (!string.IsNullOrWhiteSpace(item.SourceCode))
                            {
                                if (RepositoryUtils.containsSpecialCharacters(item.SourceCode))
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "SourceCode")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    if (sourceIds.ContainsKey(item.SourceCode))
                                    {
                                        var sourceId = sourceIds[item.SourceCode];
                                        //update model
                                        model.SourceID = sourceId;
                                        item.SourceID = sourceId.ToString();
                                    }
                                    else
                                    {
                                        var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.SourceID, "SourceCode")); listOfErrors.Add(err); isRejected = true;
                                    }

                                }
                            }
                            else
                            {
                                var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "SourceCode")); listOfErrors.Add(err); isRejected = true;
                            }
                            //SampleID- labsamplecode is unique identifier
                            if (!string.IsNullOrWhiteSpace(item.LabSampleCode))
                            {
                                if (RepositoryUtils.containsSpecialCharacters(item.LabSampleCode))
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "LabSampleCode")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    if (sampleIds.ContainsKey(item.LabSampleCode))
                                    {
                                        var sampleId = sampleIds[item.LabSampleCode];
                                        //update model
                                        model.SampleID = sampleId;
                                        item.SampleID = sampleId.ToString();
                                    }
                                    else
                                    {
                                        var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.SampleID, "LabSampleCode")); listOfErrors.Add(err); isRejected = true;
                                    }

                                }
                            }

                            //DerivedFromID
                            if (!string.IsNullOrWhiteSpace(item.DerivedFromID))
                            {
                                int result;
                                bool canConvert = UniversalTypeConverter.TryConvertTo<int>(item.DerivedFromID, out result);

                                if (!canConvert)
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "DerivedFromID")); listOfErrors.Add(err); isRejected = true;
                                }
                                else
                                {
                                    model.DerivedFromID = result;
                                }
                            }
                            //QualityControlLevelID
                            if (!string.IsNullOrWhiteSpace(item.QualityControlLevelCode))
                            {
                                if (qualityControlLevelIds.ContainsKey(item.QualityControlLevelCode))
                                {
                                    var qualityControlLevelId = qualityControlLevelIds[item.QualityControlLevelCode];
                                    //update model
                                    model.QualityControlLevelID = qualityControlLevelId;
                                    item.QualityControlLevelID = qualityControlLevelId.ToString();
                                }
                                else
                                {
                                    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.QualityControlLevelCode, "QualityControlLevelCode")); listOfErrors.Add(err); isRejected = true;

                                }
                            }
                            else
                            {
                                var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "QualityControlLevelCode")); listOfErrors.Add(err); isRejected = true;
                            }
                            //lookup siteid

                            //if (variables.ContainsKey(item.VariableCode))
                            //{
                            //    var variableId = variables[item.VariableCode];
                            //    update model
                            //    model.VariableID = variableId;
                            //}
                            //else
                            //{
                            //    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.VariableCode, "AddDataValues")); listOfErrors.Add(err); isRejected = true;

                            //}
                            //if (model.OffsetTypeID != null)
                            //{
                            //    var offsetTyperID = OffsetTypeIds
                            //                            .Exists(a => a == model.OffsetTypeID);
                            //    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.OffsetTypeID.ToString(), "OffsetTypes:")); listOfErrors.Add(err); isRejected = true;

                            //}

                            //if (model.CensorCode != "nc")
                            //{
                            //    var censorCode = censorCodeCV
                            //                            .Exists(a => a.Term == item.CensorCode);
                            //    if (!censorCode) { var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.OffsetTypeID, "AddDataValues")); listOfErrors.Add(err); isRejected = true; };

                            //}

                            //if (model.QualifierID != null)
                            //{
                            //    var censorCode = censorCodeCV
                            //                            .Exists(a => a.Term == item.CensorCode);
                            //    if (!censorCode) { var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.QualifierID, "Qualifiers")); listOfErrors.Add(err); isRejected = true; };

                            //}

                            //if (model.MethodID != null)
                            //{
                            //    var methodId = methodIds
                            //                            .Exists(a => a.MethodID == model.MethodID);
                            //    if (!methodId) { var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.MethodID, "Methods")); listOfErrors.Add(err); isRejected = true; };

                            //}

                            //if (model.SourceID != null)
                            //{
                            //    var sourceId = sourceIds
                            //                            .Exists(a => a.SourceID == model.SourceID);
                            //    if (!sourceId) { var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.SourceID, "Sources")); listOfErrors.Add(err); isRejected = true; };

                            //}

                            //if (model.SampleID != null)
                            //{
                            //    var sampleId = sampleIds
                            //                            .Exists(a => a.SampleID == model.SampleID);
                            //    if (!sampleId) { var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.SampleID, "Samples")); listOfErrors.Add(err); isRejected = true; };

                            //}

                            //if (qualityControlLevelIds.ContainsKey(item.QualityControlLevelCode))
                            //{
                            //    var qualityControlLevelId = qualityControlLevelIds[item.QualityControlLevelCode];
                            //    update model
                            //    model.QualityControlLevelID = qualityControlLevelId;
                            //}
                            //else
                            //{
                            //    var err = new ErrorModel("AddDataValues", string.Format(Ressources.IMPORT_VALUE_NOT_IN_CV, item.VariableCode, "AddDataValues")); listOfErrors.Add(err); isRejected = true;

                            //}
                            // var dataType = dataTypeCV
                            //                         .Exists(a => a.Term.ToString() == item.DataType);

                            #endregion data

                            if (isRejected)
                            {
                                var sb = new StringBuilder();
                                foreach (var er in listOfErrors)
                                {
                                    sb.Append(er.ErrorMessage + ";");
                                }
                                item.Errors = sb.ToString();
                                listOfIncorrectRecords.Add(item);
                                continue;
                            }

                            //else
                            //{
                            //    int variableId;
                            //    bool res = int.TryParse(item.VariableID, out variableId);
                            //    if (res)
                            //    {
                            //        //update model
                            //        model.VariableID = variableId;
                            //    }
                            //    else
                            //    {
                            //        listOfIncorrectRecords.Add(item);
                            //        continue;

                            //    }
                            //}
                            //Validate foreign keys
                            //var methodId = context.Methods
                            //                       .Where(a => a.MethodID == model.MethodID)
                            //                       .Select(a => a);

                            //lookup duplicates
                            //check if item with this sitecode exists in the database
                            //check in list
                            //var siteidInList = datavaluesWithSiteIDInDatabase.ToList().Find(p => p.SiteID.ToString() == item.SiteID);

                            a_start = DateTime.Now;
                            bool doesExist = false;
                            //pretest with date and datavalue if
                            var possibleInSet = setDatetime.Contains(model.DateTimeUTC) && setDataValue.Contains(model.DataValue1) && setVariableId.Contains(model.VariableID) && setMethodId.Contains(model.MethodID);
                            //var possibleInSet2 = foo.Contains(model);
                            //allValues.Add(model);
                            if (possibleInSet)
                            {

                                doesExist = allValues.Where(a =>
                                                            a.DataValue1.Equals(model.DataValue1) &&
                                                            a.ValueAccuracy.Equals(model.ValueAccuracy) &&
                                                            a.LocalDateTime.Equals(model.LocalDateTime) &&
                                                            a.UTCOffset.Equals(model.UTCOffset) &&
                                                            a.DateTimeUTC.Ticks.Equals(model.DateTimeUTC.Ticks) &&
                                                            a.SiteID.Equals(model.SiteID) &&
                                                            a.VariableID.Equals(model.VariableID) &&
                                                            a.OffsetValue.Equals(model.OffsetValue) &&
                                                            a.OffsetTypeID.Equals(model.OffsetTypeID) &&
                                                            a.CensorCode.Equals(model.CensorCode) &&
                                                            a.QualifierID.Equals(model.QualifierID) &&
                                                            a.MethodID.Equals(model.MethodID) &&
                                                            a.SourceID.Equals(model.SourceID) &&
                                                            a.SampleID.Equals(model.SampleID) &&
                                                            a.DerivedFromID.Equals(model.DerivedFromID) &&
                                                            a.QualityControlLevelID.Equals(model.QualityControlLevelID)
                                                            ).FirstOrDefault() != null;

                                a_end = DateTime.Now;

                                    span = a_end - a_start;
                                    timeToFindDuplicates = timeToFindDuplicates.Add(span);
                                    //Debug.WriteLine("timeToFindDuplicates: " + span);

                            }
                            if (!doesExist)
                            {
                                if (count % 100 == 0) Debug.WriteLine(count);
                                listOfCorrectRecords.Add(item);
                            }
                            else
                            {

                                //no editing possible no unique field in upload
                                if (listOfDuplicateRecords.Count() > maxAllowedDuplicates )
                                {
                                    throw new System.OperationCanceledException("The upload was cancelled due to a large number of duplicates (" + maxAllowedDuplicates + ") in upload. Please review data.");
                                }

                                listOfDuplicateRecords.Add(item);
                            }

                        }
                        catch (OperationCanceledException ex)
                        {
                            throw;
                        }
                        catch (Exception ex)
                        {
                            listOfIncorrectRecords.Add(item);
                        }

                    }
                    #endregion
                }
            }
            catch (OperationCanceledException ex)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw;
            }
            //context.SaveChanges();
            //Pass in cnx, tablename, and list of imports
            //RepositoryUtils.BulkInsert(context.Database.Connection.ConnectionString, "Datavalues", recordsToInsert);

            //RepositoryUtils.UpdateSeriesCatalog(context.Database.Connection.ConnectionString);
            Debug.WriteLine("timeToRetrieveVars:" + timeToRetrieveVars);
            Debug.WriteLine("timeToFindDatavalues: " + timeToFindDatavalues);
            Debug.WriteLine("timeToFindDuplicates: " + timeToFindDuplicates);
            Debug.WriteLine("timeExistInUpload: " + timeExistInUpload);
            timeTocomplete = DateTime.Now - startTime;
            Debug.WriteLine("timeTocomplete: " + timeTocomplete);
            //BusinessObjectsUtils.UpdateCachedprocessStatusMessage(instanceIdentifier, CacheName, String.Format(Ressources.IMPORT_STATUS_PROCESSING_DONE, count, maxCount));

            return;
        }
        public void AddQualityControlLevel(List<QualityControlLevelModel> itemList, string entityConnectionString, string instanceIdentifier, out List<QualityControlLevelModel> listOfIncorrectRecords, out List<QualityControlLevelModel> listOfCorrectRecords, out List<QualityControlLevelModel> listOfDuplicateRecords, out List<QualityControlLevelModel> listOfEditedRecords)
        {
            listOfIncorrectRecords = new List<QualityControlLevelModel>();
            listOfCorrectRecords = new List<QualityControlLevelModel>();
            listOfDuplicateRecords = new List<QualityControlLevelModel>();
            listOfEditedRecords = new List<QualityControlLevelModel>();

            var context = new ODM_1_1_1EFModel.ODM_1_1_1Entities(entityConnectionString);
            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 = new QualityControlLevel();
                    var listOfErrors = new List<ErrorModel>();
                    var listOfUpdates = new List<UpdateFieldsModel>();

                    bool isRejected = false;
                    count++;

                    //QualityControlLevelCode
                    if (!string.IsNullOrWhiteSpace(item.QualityControlLevelCode))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.QualityControlLevelCode))
                        {
                            var err = new ErrorModel("AddQualityControlLevel", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "QualityControlLevelCode")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            model.QualityControlLevelCode = item.QualityControlLevelCode;
                        }

                    }
                    else
                    {
                        var err = new ErrorModel("AddQualityControlLevel", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "QualityControlLevelCode")); listOfErrors.Add(err); isRejected = true;
                    }
                    //Definition
                    if (!string.IsNullOrWhiteSpace(item.Definition))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.Definition))
                        {
                            var err = new ErrorModel("AddQualityControlLevel", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "Definition")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            model.Definition = item.Definition;
                        }

                    }
                    else
                    {
                        var err = new ErrorModel("AddQualityControlLevel", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "Definition")); listOfErrors.Add(err); isRejected = true;
                    }
                    //Explanation
                    if (!string.IsNullOrWhiteSpace(item.Explanation))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.Explanation))
                        {
                            var err = new ErrorModel("AddQualityControlLevel", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "Explanation")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            model.Explanation = item.Explanation;
                        }

                    }
                    else
                    {
                        var err = new ErrorModel("AddQualityControlLevel", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "Explanation")); 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 existingItem = context.QualityControlLevels.Where(a => a.QualityControlLevelCode == model.QualityControlLevelCode
                                                                               //&& a.Definition == model.Definition &&
                                                                               //a.Explanation == model.Explanation
                                                                               ).FirstOrDefault();

                    if (existingItem == null)
                    {
                        var existInUpload = listOfCorrectRecords.Exists(a => a.QualityControlLevelCode == item.QualityControlLevelCode);
                        if (!existInUpload)
                        {
                            context.QualityControlLevels.Add(model);
                            //context.SaveChanges();
                            listOfCorrectRecords.Add(item);
                        }
                        else
                        {
                            var err = new ErrorModel("AddQualityControlLevel", string.Format(Ressources.IMPORT_VALUE_ISDUPLICATE, "QualityControlLevelCode")); listOfErrors.Add(err); isRejected = true;
                            listOfIncorrectRecords.Add(item);
                            item.Errors += err.ErrorMessage + ";";
                        }
                    }
                    else
                    {
                        //if (existingItem.QualityControlLevelCode != model.QualityControlLevelCode) { listOfUpdates.Add(new UpdateFieldsModel("QualityControlLevels", "QualityControlLevelCode", existingItem.QualityControlLevelCode.ToString(), item.QualityControlLevelCode.ToString())); }
                        if (model.Definition != null && existingItem.Definition != model.Definition) { listOfUpdates.Add(new UpdateFieldsModel("QualityControlLevels", "Definition", existingItem.Definition.ToString(), item.Definition.ToString())); }
                        if (model.Explanation != null && existingItem.Explanation != model.Explanation) { listOfUpdates.Add(new UpdateFieldsModel("QualityControlLevels", "Explanation", existingItem.Explanation.ToString(), item.Explanation.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;
        }
        public void AddOffsetTypes(List<OffsetTypesModel> itemList, string entityConnectionString, string instanceIdentifier, out List<OffsetTypesModel> listOfIncorrectRecords, out List<OffsetTypesModel> listOfCorrectRecords, out List<OffsetTypesModel> listOfDuplicateRecords, out List<OffsetTypesModel> listOfEditedRecords)
        {
            listOfIncorrectRecords = new List<OffsetTypesModel>();
            listOfCorrectRecords = new List<OffsetTypesModel>();
            listOfDuplicateRecords = new List<OffsetTypesModel>();
            listOfEditedRecords = new List<OffsetTypesModel>();

            var context = new ODM_1_1_1EFModel.ODM_1_1_1Entities(entityConnectionString);
            //prefetch Units for quick lookup
            var units = context.Units.ToDictionary(p => p.UnitsName.Trim(), p => p.UnitsID);

            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 listOfErrors = new List<ErrorModel>();
                    var listOfUpdates = new List<UpdateFieldsModel>();
                    bool isRejected = false;
                    var model = new OffsetType();

                                //OffsetUnitsID
                                if (!string.IsNullOrWhiteSpace(item.OffsetUnitsName))

                                {
                                    if (RepositoryUtils.containsSpecialCharacters(item.OffsetUnitsName))
                                    {
                                        var err = new ErrorModel("AddOffsetTypes", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "OffsetUnitsName")); listOfErrors.Add(err); isRejected = true;
                                    }
                                    else
                                    {

                                        int result;
                                        bool canConvert = UniversalTypeConverter.TryConvertTo<int>(item.OffsetUnitsName, out result);
                                        if (canConvert)//user used id
                                        {

                                            if (result != 0) model.OffsetUnitsID = result;
                                            else
                                            {
                                                var err = new ErrorModel("AddOffsetTypes", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "OffsetUnitsName")); listOfErrors.Add(err); isRejected = true;
                                            }
                                        }
                                        else
                                        {
                                            var unitsID = units
                                               .Where(a => a.Key == item.OffsetUnitsName)
                                               .Select(a => a.Value)
                                               .SingleOrDefault();
                                            if (unitsID != 0)
                                            {
                                                model.OffsetUnitsID = unitsID;
                                                item.OffsetUnitsID = unitsID.ToString();
                                            }
                                            else
                                            {
                                                var err = new ErrorModel("AddOffsetTypes", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "OffsetUnitsName")); listOfErrors.Add(err); isRejected = true;
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    var err = new ErrorModel("AddOffsetTypes", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "OffsetUnitsName")); listOfErrors.Add(err); isRejected = true;
                                }
                                //OffsetDescription
                                if (!string.IsNullOrWhiteSpace(item.OffsetDescription))
                                {
                                    if (RepositoryUtils.containsSpecialCharacters(item.OffsetDescription))
                                    {
                                        var err = new ErrorModel("AddOffsetTypes", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "OffsetDescription")); listOfErrors.Add(err); isRejected = true;
                                    }
                                    else
                                    {
                                        model.OffsetDescription = item.OffsetDescription;
                                    }
                                }
                                else
                                {
                                    model.OffsetDescription = null;
                                }

                                if (isRejected)
                                {
                                    var sb = new StringBuilder();
                                    foreach (var er in listOfErrors)
                                    {
                                        sb.Append(er.ErrorMessage + ";");
                                    }
                                    item.Errors = sb.ToString();
                                    listOfIncorrectRecords.Add(item);
                                    continue;
                                }
                                //OffsetTypeCode
                                if (!string.IsNullOrWhiteSpace(item.OffsetTypeCode))
                                {
                                    if (RepositoryUtils.containsNotOnlyAllowedCaracters(item.OffsetTypeCode))
                                    {
                                        var err = new ErrorModel("AddOffsetType", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "OffsetTypeCode")); listOfErrors.Add(err); isRejected = true;
                                    }
                                    else
                                    {
                                        model.OffsetTypeCode = item.OffsetTypeCode;
                                    }

                                }
                                else
                                {
                                    var err = new ErrorModel("AddOffsetType", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "OffsetTypeCode")); listOfErrors.Add(err); isRejected = true;
                                }
                    //lookup duplicates
                    //check if item with this variablecode exists in the database
                    var existingItem = context.OffsetTypes.Where(a => a.OffsetTypeCode == item.OffsetTypeCode).FirstOrDefault();

                    if (existingItem == null)
                    {
                        var existInUpload = listOfCorrectRecords.Exists(a => a.OffsetTypeCode == item.OffsetTypeCode);
                        if (!existInUpload)
                        {
                            //context.Sites.Add(model);
                            //context.SaveChanges();
                            listOfCorrectRecords.Add(item);
                        }
                        else
                        {
                            var err = new ErrorModel("AddOffsetType", string.Format(Ressources.IMPORT_VALUE_ISDUPLICATE, "OffsetTypeCode")); listOfErrors.Add(err); isRejected = true;
                            listOfIncorrectRecords.Add(item);
                            item.Errors += err.ErrorMessage + ";";
                        }
                    }
                    else
                    {

                        //if (existingItem.OffsetTypeCode != model.OffsetTypeCode) { listOfUpdates.Add(new UpdateFieldsModel("OffsetType", "OffsetTypeCode", existingItem.OffsetTypeCode.ToString(), item.OffsetTypeCode.ToString())); }
                        if (model.OffsetDescription != null && existingItem.OffsetDescription != model.OffsetTypeCode) { listOfUpdates.Add(new UpdateFieldsModel("OffsetType", "OffsetDescription", existingItem.OffsetDescription.ToString(), item.OffsetDescription.ToString())); }
                        if (model.OffsetUnitsID != null && existingItem.OffsetUnitsID != model.OffsetUnitsID) { listOfUpdates.Add(new UpdateFieldsModel("OffsetType", "OffsetUnitsID", existingItem.OffsetUnitsID.ToString(), item.OffsetUnitsName.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;
        }
        public void AddLabMethods(List<LabMethodModel> itemList, string entityConnectionString, string instanceIdentifier, out List<LabMethodModel> listOfIncorrectRecords, out List<LabMethodModel> listOfCorrectRecords, out List<LabMethodModel> listOfDuplicateRecords, out List<LabMethodModel> listOfEditedRecords)
        {
            listOfIncorrectRecords = new List<LabMethodModel>();
            listOfCorrectRecords = new List<LabMethodModel>();
            listOfDuplicateRecords = new List<LabMethodModel>();
            listOfEditedRecords = new List<LabMethodModel>();

            var context = new ODM_1_1_1EFModel.ODM_1_1_1Entities(entityConnectionString);
            //prefetch Units for quick lookup
            var offsetUnits = context.Units.ToDictionary(p => p.UnitsName.Trim(), p => p.UnitsID);

            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 = new LabMethod();
                    var listOfErrors = new List<ErrorModel>();
                    var listOfUpdates = new List<UpdateFieldsModel>();

                    bool isRejected = false;
                    //set default values
                    string unk = "Unknown";
                    model.LabName = unk;
                    model.LabOrganization = unk;
                    model.LabMethodName = unk;
                    model.LabMethodDescription = unk;

                    ////LabMethodCode
                    //if (!string.IsNullOrWhiteSpace(item.LabMethodCode))
                    //{
                    //    if (RepositoryUtils.containsNotOnlyAllowedCaracters(item.LabMethodCode))
                    //    {
                    //        var err = new ErrorModel("AddLabMethod", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "LabMethodCode")); listOfErrors.Add(err); isRejected = true;
                    //    }
                    //    else
                    //    {
                    //        model.LabMethodCode = item.LabMethodCode;
                    //    }

                    //}
                    //else
                    //{
                    //    var err = new ErrorModel("AddLabMethod", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "LabMethodCode")); listOfErrors.Add(err); isRejected = true;
                    //}

                    //LabName
                    if (!string.IsNullOrWhiteSpace(item.LabName))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.LabName))
                        {
                            var err = new ErrorModel("AddLabMethods", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "LabName")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            model.LabName = item.LabName;
                        }
                    }
                    else
                    {
                        item.LabName = model.LabName;
                    }
                    //LabOrganization
                    if (!string.IsNullOrWhiteSpace(item.LabOrganization))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.LabOrganization))
                        {
                            var err = new ErrorModel("AddLabMethods", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "LabOrganization")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            model.LabOrganization = item.LabOrganization;
                        }
                    }
                    else
                    {
                        item.LabOrganization = model.LabOrganization;
                    }
                    //LabMethodName
                    if (!string.IsNullOrWhiteSpace(item.LabMethodName))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.LabMethodName))
                        {
                            var err = new ErrorModel("AddLabMethods", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "LabMethodName")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            model.LabMethodName = item.LabMethodName;
                        }
                    }
                    else
                    {
                        item.LabMethodName = model.LabMethodName;
                    }
                    //LabMethodDescription
                    if (!string.IsNullOrWhiteSpace(item.LabMethodDescription))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.LabMethodDescription))
                        {
                            var err = new ErrorModel("AddLabMethods", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "LabMethodDescription")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            model.LabMethodDescription = item.LabMethodDescription;
                        }
                    }
                    else
                    {
                        item.LabMethodDescription = model.LabMethodDescription;
                    }
                    //LabMethodLink
                    if (!string.IsNullOrWhiteSpace(item.LabMethodLink))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.LabMethodLink))
                        {
                            var err = new ErrorModel("AddLabMethods", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "LabMethodLink")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            model.LabMethodLink = item.LabMethodLink;
                        }
                    }

                    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 existingItem = context.LabMethods
                                                 .Where(
                                                     a => a.LabMethodName == model.LabMethodName)
                                                          .FirstOrDefault();

                    if (existingItem == null)
                    {
                        var existInUpload = listOfCorrectRecords.Exists(a => a.LabMethodName == item.LabMethodName);
                        if (!existInUpload)
                        {
                            //context.Sites.Add(model);
                            //context.SaveChanges();
                            listOfCorrectRecords.Add(item);
                        }
                        else
                        {
                            var err = new ErrorModel("AddLabMethods", string.Format(Ressources.IMPORT_VALUE_ISDUPLICATE, "LabMethodName")); listOfErrors.Add(err); isRejected = true;
                            listOfIncorrectRecords.Add(item);
                            item.Errors += err.ErrorMessage + ";";
                        }
                    }
                    else
                    {
                        if (existingItem.LabMethodName != model.LabMethodName) { listOfUpdates.Add(new UpdateFieldsModel("LabMethod", "LabMethodName", existingItem.LabMethodName.ToString(), item.LabMethodName.ToString())); }
                        if (model.LabName != null && existingItem.LabName != model.LabName) { listOfUpdates.Add(new UpdateFieldsModel("LabMethod", "LabName", existingItem.LabName.ToString(), item.LabName.ToString())); }
                        if (model.LabOrganization != null && existingItem.LabOrganization != model.LabOrganization) { listOfUpdates.Add(new UpdateFieldsModel("LabMethod", "LabOrganization", existingItem.LabOrganization.ToString(), item.LabOrganization.ToString())); }
                        //if (model.LabMethodName != null && existingItem.LabMethodName != model.LabMethodName) { listOfUpdates.Add(new UpdateFieldsModel("LabMethod", "LabMethodName", existingItem.LabMethodCode.ToString(), item.LabMethodName.ToString())); }
                        if (model.LabMethodDescription != null && existingItem.LabMethodDescription != model.LabMethodDescription) { listOfUpdates.Add(new UpdateFieldsModel("LabMethod", "LabMethodDescription", existingItem.LabMethodDescription.ToString(), item.LabMethodDescription.ToString())); }
                        if (model.LabMethodLink != null && existingItem.LabMethodLink != model.LabMethodLink) { listOfUpdates.Add(new UpdateFieldsModel("LabMethod", "LabMethodLink", existingItem.LabMethodLink.ToString(), item.LabMethodLink.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;
        }
        public void AddGroups(List<GroupsModel> itemList, string entityConnectionString, string instanceIdentifier, out List<GroupsModel> listOfIncorrectRecords, out List<GroupsModel> listOfCorrectRecords, out List<GroupsModel> listOfDuplicateRecords, out List<GroupsModel> listOfEditedRecords)
        {
            listOfIncorrectRecords = new List<GroupsModel>();
            listOfCorrectRecords = new List<GroupsModel>();
            listOfDuplicateRecords = new List<GroupsModel>();
            listOfEditedRecords = new List<GroupsModel>();

            var context = new ODM_1_1_1EFModel.ODM_1_1_1Entities(entityConnectionString);

            var groupDescriptions = context.GroupDescriptions.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 = new Group();
                    var listOfErrors = new List<ErrorModel>();
                    bool isRejected = false;

                    //GroupID
                    if (!string.IsNullOrWhiteSpace(item.GroupID))
                    {
                         int result;
                         bool canConvert = UniversalTypeConverter.TryConvertTo<int>(item.GroupID, out result);
                         if (canConvert)//user used id
                         {
                             var groupId = groupDescriptions
                                         .Exists(a => a.GroupID == result);

                             if (!groupId)
                             {
                                 var err = new ErrorModel("AddGroups", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.GroupID, "Groups"));
                                 listOfErrors.Add(err);
                                 isRejected = true;
                             }
                             else
                             {
                                 model.GroupID = result;
                             }
                         }
                         else
                         {
                             var err = new ErrorModel("AddGroups", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "GroupID")); listOfErrors.Add(err); isRejected = true;
                         }
                    }
                    else
                    {
                        var err = new ErrorModel("AddGroups", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "GroupID")); listOfErrors.Add(err); isRejected = true;
                    }

                    //ValuerID
                    if (!string.IsNullOrWhiteSpace(item.ValueID))
                    {
                        int result;
                        bool canConvert = UniversalTypeConverter.TryConvertTo<int>(item.ValueID, out result);
                        if (canConvert)//user used id
                        {
                            var valueID = context.DataValues
                                        .Where(a => a.ValueID == result).FirstOrDefault() ;

                            if (valueID != null)
                            {
                                model.ValueID = result;
                            }
                            else
                            {
                                var err = new ErrorModel("AddGroups", string.Format(Ressources.IMPORT_VALUE_NOT_IN_DATABASE, item.ValueID, "ValueID"));
                                listOfErrors.Add(err);
                                isRejected = true;
                            }
                        }
                        else
                        {
                            var err = new ErrorModel("AddGroups", string.Format(Ressources.IMPORT_VALUE_INVALIDVALUE, "ValueID")); listOfErrors.Add(err); isRejected = true;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddGroups", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "ValueID")); 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 existingItem = context.Groups.Where(a => a.GroupID == model.GroupID &&
                                                                               a.ValueID == model.ValueID
                                                                               ).FirstOrDefault();

                    if (existingItem == null)
                    {
                        context.Groups.Add(model);
                        //context.SaveChanges();
                        listOfCorrectRecords.Add(item);
                    }
                    else
                    {
                        //no editing possible no unique field in upload
                        listOfDuplicateRecords.Add(item);
                    }

                }
                catch (Exception ex)
                {
                    listOfIncorrectRecords.Add(item);
                }

            }
            return;
        }
        public void AddGroupDescriptions(List<GroupDescriptionModel> itemList, string entityConnectionString, string instanceIdentifier, out List<GroupDescriptionModel> listOfIncorrectRecords, out List<GroupDescriptionModel> listOfCorrectRecords, out List<GroupDescriptionModel> listOfDuplicateRecords, out List<GroupDescriptionModel> listOfEditedRecords)
        {
            listOfIncorrectRecords = new List<GroupDescriptionModel>();
            listOfCorrectRecords = new List<GroupDescriptionModel>();
            listOfDuplicateRecords = new List<GroupDescriptionModel>();
            listOfEditedRecords = new List<GroupDescriptionModel>();

            var context = new ODM_1_1_1EFModel.ODM_1_1_1Entities(entityConnectionString);

            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 = new GroupDescription();
                    var listOfErrors = new List<ErrorModel>();
                    bool isRejected = false;
                    //GroupDescription
                    if (!string.IsNullOrWhiteSpace(item.GroupDescription))
                    {
                        if (RepositoryUtils.containsSpecialCharacters(item.GroupDescription))
                        {
                            var err = new ErrorModel("AddGroupDescriptions", string.Format(Ressources.IMPORT_VALUE_INVALIDCHARACTERS, "GroupDescription")); listOfErrors.Add(err); isRejected = true;
                        }
                        else
                        {
                            model.GroupDescription1 = item.GroupDescription;
                        }
                    }
                    else
                    {
                        var err = new ErrorModel("AddGroupDescriptions", string.Format(Ressources.IMPORT_VALUE_CANNOTBEEMPTY, "GroupDescription")); 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 existingItem = context.GroupDescriptions.Where(a => a.GroupDescription1 == model.GroupDescription1
                                                                               ).FirstOrDefault();

                    if (existingItem == null)
                    {
                        context.GroupDescriptions.Add(model);
                        //context.SaveChanges();
                        listOfCorrectRecords.Add(item);
                    }
                    else
                    {
                        //no editing possible no unique field in upload
                        listOfDuplicateRecords.Add(item);
                    }

                }
                catch (Exception ex)
                {
                    listOfIncorrectRecords.Add(item);
                }

            }
            return;
        }