public static StatusMessage LocationsCSV(string FilePath, Guid? LocationTypeKey = null, bool SkipGeocoding = false ) { Guid LocTypeKey; if (LocationTypeKey == null) { LocTypeKey = Constants.DefaultLocationTypeKey; } else { LocTypeKey = (Guid)LocationTypeKey; } var locationTypeService = new LocationTypeService(); var locType = locationTypeService.GetLocationType(LocTypeKey); StatusMessage Msg = new StatusMessage(); Msg.ObjectName = FilePath; var MsgDetails = new StringBuilder(); string FullPath = Mindfly.Files.GetMappedPath(FilePath); DataTable tableCSV = ConvertCSVtoDataTable(FullPath, true); var iCounter = 0; int RowsTotal = tableCSV.Rows.Count; int RowsSuccess = 0; int RowsFailure = 0; int GeocodeCount = 0; bool NeedsMaintenance = false; if (SkipGeocoding) { GeocodeCount = MAX_GEOCODE + 1; } foreach (DataRow row in tableCSV.Rows) { iCounter++; string locName = row.Field<string>("LocationName"); var rowImportStatus = ImportRow(row, locType, GeocodeCount, tableCSV.Columns, out GeocodeCount); Msg.InnerStatuses.Add(rowImportStatus); if (rowImportStatus.Success) { RowsSuccess++; if (StringExtensions.IsNullOrWhiteSpace(rowImportStatus.Code)) { MsgDetails.AppendLine(string.Format("'{0}' was imported successfully.", locName)); } else { MsgDetails.AppendLine(string.Format("'{0}' was imported with some issues: {1}", locName, rowImportStatus.Message)); if (rowImportStatus.Code.Contains("GeocodingProblem") || rowImportStatus.Code.Contains("UnableToUpdateDBGeography")) { NeedsMaintenance = true; } } } else { MsgDetails.AppendLine(string.Format("Error importing '{0}' : {1}", locName, rowImportStatus.Message)); RowsFailure++; } if (rowImportStatus.RelatedException != null) { LogHelper.Error(typeof(Import), string.Format("Error on '{0}'", locName), rowImportStatus.RelatedException); } } //Compile final status message Msg.Message += string.Format( "Out of {0} total locations, {1} were imported successfully and {2} failed.", RowsTotal, RowsSuccess, RowsFailure); if (NeedsMaintenance) { Msg.Message += " Some rows encountered issues which might be fixed by running some maintenance tasks."; } Msg.MessageDetails = MsgDetails.ToString(); Msg.Success = true; LogHelper.Info(typeof(Import), string.Format("Final Status importing file '{0}': {1} : {2}", FilePath, Msg.Message, Msg.MessageDetails)); return Msg; }
private static StatusMessage ImportRow(DataRow row, LocationType LocType, int GeocodeCount, DataColumnCollection ImportColumns, out int geocodeCountReturn) { string locName = row.Field<string>("LocationName"); var ReturnMsg = new StatusMessage(); ReturnMsg.ObjectName = locName; ReturnMsg.Success = true; var Msg = new StringBuilder(); var geocodeCountNew = GeocodeCount; //Create new Location for row var newLoc = new Location(locName, LocType.Key); Repositories.LocationRepo.Insert(newLoc); try { //Default Props var locationTypeService = new LocationTypeService(); var defaultLocType = locationTypeService.GetLocationType(Constants.DefaultLocationTypeKey); foreach (var prop in defaultLocType.Properties) { string colName = prop.Alias; if (ImportColumns.Contains(colName)) { newLoc.AddPropertyData(colName, row.Field<object>(colName)); } else { newLoc.AddPropertyData(colName, null); Msg.AppendLine(string.Concat("Data for '", colName, "' was not included in the import file.")); } } //Custom Props if (LocType.Key != defaultLocType.Key) { foreach (var prop in LocType.Properties) { string colName = prop.Alias; if (ImportColumns.Contains(colName)) { newLoc.AddPropertyData(colName, row.Field<object>(colName)); } else { newLoc.AddPropertyData(colName, null); Msg.AppendLine(string.Concat("Data for '", colName, "' was not included in the import file.")); } } } // SAVE properties of new location to db try { Repositories.LocationRepo.Update(newLoc); } catch (Exception eSave) { ReturnMsg.Success = false; ReturnMsg.RelatedException = eSave; ReturnMsg.Code = ReturnMsg.Code != "" ? ReturnMsg.Code + ",InsertError" : "InsertError"; Msg.AppendLine("There was a problem saving the new location data."); } //Check for Lat/Long values - import if present if (ImportColumns.Contains("Latitude")) { int convertedInt = 0; Int32.TryParse(row.Field<string>("Latitude"), out convertedInt); newLoc.Latitude = convertedInt; } if (ImportColumns.Contains("Longitude")) { int convertedInt = 0; Int32.TryParse(row.Field<string>("Longitude"), out convertedInt); newLoc.Longitude = convertedInt; } //If Lat/Long are both 0... attempt geocoding if (newLoc.Latitude == 0 && newLoc.Longitude == 0) { //TODO: make dynamic based on provider limit if (GeocodeCount >= MAX_GEOCODE) { ReturnMsg.Success = true; ReturnMsg.Code = "GeocodingProblem"; Msg.AppendLine( "This address exceeded the limits for geo-coding in a batch. Please run maintenance to update geo-codes later."); } else { try { var newCoordinate = DoGeocoding.GetCoordinateForAddress(newLoc.Address); newLoc.Latitude = newCoordinate.Latitude; newLoc.Longitude = newCoordinate.Longitude; geocodeCountNew++; } catch (Exception e1) { ReturnMsg.Success = true; ReturnMsg.RelatedException = e1; ReturnMsg.Code = "GeocodingProblem"; Msg.AppendLine( "There was a problem geo-coding the address. Please run maintenance to update geo-codes later."); LogHelper.Error( typeof(Import), string.Format("Geo-coding error while importing '{0}'", ReturnMsg.ObjectName), e1); } } } // SAVE properties of new location to db again try { Repositories.LocationRepo.Update(newLoc); } catch (Exception eSave) { ReturnMsg.Success = false; ReturnMsg.RelatedException = eSave; ReturnMsg.Code = ReturnMsg.Code != "" ? ReturnMsg.Code + ",InsertError" : "InsertError"; Msg.AppendLine("There was a problem saving the new location data."); } // UPDATE Geography DB field using Lat/Long try { if (newLoc.Latitude != 0 && newLoc.Longitude != 0) { Repositories.LocationRepo.UpdateDbGeography(newLoc); } else { newLoc.DbGeogNeedsUpdated = true; Repositories.LocationRepo.Update(newLoc); } } catch (Exception eGeoDB) { ReturnMsg.Success = true; ReturnMsg.RelatedException = eGeoDB; ReturnMsg.Code = ReturnMsg.Code != "" ? ReturnMsg.Code + ",UnableToUpdateDBGeography" : "UnableToUpdateDBGeography"; Msg.AppendLine("Unable to update the coordinates in the database."); } } catch (Exception ex) { ReturnMsg.Success = false; ReturnMsg.RelatedException = ex; ReturnMsg.Code = ReturnMsg.Code != "" ? ReturnMsg.Code + ",UnknownException" : "UnknownException"; Msg.AppendLine(ex.Message); LogHelper.Error(typeof(Import), string.Format("ImportRow: Error importing location '{0}'", locName), ex); } ReturnMsg.Message = Msg.ToString(); geocodeCountReturn = geocodeCountNew; return ReturnMsg; }