public static IEnumerable<GeocoderResult> TryGeocode(Address addressToGeocode)
{
//Do not take addresses without an AddressLineOne, the result will be high confidence but not correct
if (string.IsNullOrEmpty(addressToGeocode.AddressLineOne))
return new List<GeocoderResult>();
var parameters = new[]
{
addressToGeocode.State,
addressToGeocode.ZipCode,
addressToGeocode.City,
addressToGeocode.AddressLineOne
};
//remove . , : +
parameters = parameters.Select(p => p.Replace(".", "").Replace(",", "").Replace(":", "").Replace("+", "")).ToArray();
var query = String.Format("{0}?CountryRegion=US", ApiUrl);
if (!string.IsNullOrEmpty(parameters[0]))
query += "&adminDistrict=" + parameters[0];
if (!string.IsNullOrEmpty(parameters[1]))
query += "&postalCode=" + parameters[1];
if (!string.IsNullOrEmpty(parameters[2]))
query += "&locality=" + parameters[2];
if (!string.IsNullOrEmpty(parameters[3]))
query += "&addressLine=" + parameters[3];
return TryGeocodeUrl(query);
}
public static IEnumerable<GeocoderResult> TryGeocode(Address addressToGeocode)
{
string parameters = "";
if (!string.IsNullOrEmpty(addressToGeocode.AddressLineOne))
parameters += String.Format("&street={0}", addressToGeocode.AddressLineOne.Replace(" ", "+"));
if (!string.IsNullOrEmpty(addressToGeocode.City))
parameters += String.Format("&city={0}", addressToGeocode.City.Replace(" ", "+"));
if (!string.IsNullOrEmpty(addressToGeocode.State))
parameters += String.Format("&state={0}", addressToGeocode.State.Replace(" ", "+"));
if (!string.IsNullOrEmpty(addressToGeocode.ZipCode))
parameters += String.Format("&zip={0}", addressToGeocode.ZipCode.Replace(" ", "+"));
string restQueryUrl = GeocodeUrl + parameters;
return TryGeocodeUrl(restQueryUrl);
}
/// <summary>
/// Manipulates the input strings to existing or new entities.
/// Then it passes those entities to SuggestEntities to generate suggestions.
/// <note>This method is only used for the initial step of the Import. Once entites are generated; use SuggestEntities</note>
/// </summary>
/// <param name="rowsWithHeaders">List of string[]. The first string[] is the headers, the rest are data to be imported</param>
/// <returns>Entites with suggestions</returns>
public Suggestions ValidateThenSuggestEntities(List<string[]> rowsWithHeaders)
{
var headers = rowsWithHeaders[0];
rowsWithHeaders.RemoveAt(0);
var rows = rowsWithHeaders;
#region Assign Column placement
//Client
var clientNameCol = Array.IndexOf(headers, "Client Name");
//Location
var addressLineOneCol = Array.IndexOf(headers, "Address Line One");
var addressLineTwoCol = Array.IndexOf(headers, "Address Line Two");
var cityCol = Array.IndexOf(headers, "AdminDistrictTwo");
var stateCol = Array.IndexOf(headers, "AdminDistrictOne");
var zipCodeCol = Array.IndexOf(headers, "PostalCode");
var countryCodeCol = Array.IndexOf(headers, "Country Code");
var regionNameCol = Array.IndexOf(headers, "Region Name");
var latitudeCol = Array.IndexOf(headers, "Latitude");
var longitudeCol = Array.IndexOf(headers, "Longitude");
//Repeat
var frequencyCol = Array.IndexOf(headers, "Frequency");
var repeatEveryCol = Array.IndexOf(headers, "Repeat Every");
var startDateCol = Array.IndexOf(headers, "Start Date");
var endDateCol = Array.IndexOf(headers, "End Date");
var endAfterCol = Array.IndexOf(headers, "End After Times");
var frequencyDetailCol = Array.IndexOf(headers, "Frequency Detail");
//Contact Info
var phoneNumberValueCols = headers.Where(h => h.Contains("Phone Value")).ToArray();
var phoneNumberLabelCols = headers.Where(h => h.Contains("Phone Label")).ToArray();
var emailValueCols = headers.Where(h => h.Contains("Email Value")).ToArray();
var emailLabelCols = headers.Where(h => h.Contains("Email Label")).ToArray();
var websiteValueCols = headers.Where(h => h.Contains("Website Value")).ToArray();
var websiteLabelCols = headers.Where(h => h.Contains("Website Label")).ToArray();
var otherValueCols = headers.Where(h => h.Contains("Other Value")).ToArray();
var otherLabelCols = headers.Where(h => h.Contains("Other Label")).ToArray();
#endregion
var importRowsConcurrentDictionary = new ConcurrentDictionary<int, ImportRow>();
var rowCount = rows.Count();
Parallel.For((long)0, rowCount, rowIndex =>
{
var row = rows[(int)rowIndex];
var importRow = new ImportRow();
#region Location
var importedLocation = new Api.Models.Location();
string clientName;
//Checks if at least one location column is passed
if (new[] { addressLineOneCol, addressLineTwoCol, cityCol, stateCol, countryCodeCol, zipCodeCol, latitudeCol, longitudeCol }.Any(col => col != -1))
{
var latitude = latitudeCol != -1 ? row[latitudeCol] : null;
var longitude = longitudeCol != -1 ? row[longitudeCol] : null;
var addressLineOne = addressLineOneCol != -1 ? row[addressLineOneCol] : null;
var addressLineTwo = addressLineTwoCol != -1 ? row[addressLineTwoCol] : null;
var adminDistrictTwo = cityCol != -1 ? row[cityCol] : null;
var adminDistrictOne = stateCol != -1 ? row[stateCol] : null;
var postalCode = zipCodeCol != -1 ? row[zipCodeCol] : null;
var regionName = regionNameCol != -1 ? row[regionNameCol] : null;
//If lat/lon dont have values, try to Geocode
if (latitude == null && longitude == null)
{
var address = new Address
{
AddressLineOne = addressLineOne,
City = adminDistrictTwo,
State = adminDistrictOne,
ZipCode = postalCode
};
//Attempt to Geocode the address
var geocodeResult = BingLocationServices.TryGeocode(address).FirstOrDefault(gc => gc != null);
latitude = geocodeResult != null ? geocodeResult.Latitude : null;
longitude = geocodeResult != null ? geocodeResult.Longitude : null;
//If they still do not have values, throw error on the location
if (latitude == null && longitude == null)
importedLocation.StatusInt = (int)ImportStatus.Error;
//Matched the entire address to a location (AddressLineOne, AddressLineTwo, City, State and ZipCode)
var matchedLocation = _locations.FirstOrDefault(l => l.Value.AddressLineOne == addressLineOne && l.Value.AddressLineTwo == addressLineTwo
&& l.Value.AdminDistrictTwo == adminDistrictTwo && l.Value.AdminDistrictOne == adminDistrictOne && l.Value.PostalCode == postalCode);
if (matchedLocation.Key != Guid.Empty)
importedLocation = ConvertLocationSetRegionAndStatus(matchedLocation.Value, regionName, ImportStatus.Linked);
else
{
clientName = clientNameCol != -1 ? row[clientNameCol] : null;
//Matched the address entered and client name to a location
matchedLocation = _locations.FirstOrDefault(l => clientName != null && l.Value.ClientId != null &&
(addressLineTwo != null && (l.Value.AddressLineOne == addressLineOne && l.Value.AddressLineTwo == addressLineTwo
&& _clients.First(c => c.Key == l.Value.ClientId).Value.Name == clientName)));
if (matchedLocation.Key != Guid.Empty)
importedLocation = ConvertLocationSetRegionAndStatus(matchedLocation.Value, regionName, ImportStatus.Linked);
}
}
//If lat/lon exist, try and match based on that
if (latitude != null && longitude != null && importedLocation.StatusInt != (int)ImportStatus.Error)
{
var setError = false;
//check for invalid deciamls
Decimal convertedLatitude = 0;
Decimal convertedLongitude = 0;
Decimal tempdeciaml;
if (Decimal.TryParse(latitude, out tempdeciaml))
convertedLatitude = tempdeciaml;
else
setError = true;
if (Decimal.TryParse(longitude, out tempdeciaml))
convertedLongitude = tempdeciaml;
else
setError = true;
if (!setError)
{
var roundedLatitude = Math.Round(convertedLatitude, 6);
var roundedLongitude = Math.Round(convertedLongitude, 6);
//Try and match a location to one in the FoundOPS system
var matchedLocation = addressLineTwo != null
//Use this statement if Address Line Two is not null
? _locations.FirstOrDefault(l => l.Value.AddressLineTwo == addressLineTwo && (l.Value.Latitude != null && l.Value.Longitude != null)
&& (decimal.Round(l.Value.Latitude.Value, 6) == roundedLatitude
&& decimal.Round(l.Value.Longitude.Value, 6) == roundedLongitude))
//Use this statement if Address Line Two is null
: _locations.FirstOrDefault(l => (l.Value.Latitude != null && l.Value.Longitude != null)
&& (decimal.Round(l.Value.Latitude.Value, 6) == roundedLatitude
&& decimal.Round(l.Value.Longitude.Value, 6) == roundedLongitude));
//If a match is found, assign Linked status to all cells
if (matchedLocation.Key != Guid.Empty)
importedLocation = ConvertLocationSetRegionAndStatus(matchedLocation.Value, regionName, ImportStatus.Linked);
}
else
importedLocation.StatusInt = (int) ImportStatus.Error;
}
//If no linked location is found, it means that the location is new
if (importedLocation.StatusInt != (int)ImportStatus.Linked)
{
var status = (int) ImportStatus.New;
if (importedLocation.StatusInt == (int) ImportStatus.Error)
status = (int) ImportStatus.Error;
importedLocation = new Api.Models.Location
{
Id = Guid.NewGuid(),
AddressLineOne = addressLineOne,
AddressLineTwo = addressLineTwo ?? null,
AdminDistrictTwo = adminDistrictTwo,
AdminDistrictOne = adminDistrictOne,
PostalCode = postalCode,
CountryCode = "US",
ContactInfoSet = new List<ContactInfo>(),
Latitude = latitude,
Longitude = longitude,
StatusInt = status,
Region = SetRegion(regionName)
};
//Since the location is new, we add it to the list of locations to be searched next time we try and match
_locations.GetOrAdd(importedLocation.Id, FoundOps.Api.Models.Location.ConvertBack(importedLocation));
}
importRow.Location = importedLocation;
}
#endregion
#region Client
clientName = clientNameCol != -1 ? row[clientNameCol] : null;
//Make sure a Client is being Imported
if (clientName != null)
{
//Find an existing Client based on the Name
var existingClient = _clients.FirstOrDefault(c => c.Value.Name == clientName);
Models.Client importedClient;
//If a Client was found, set it to be imported with a Linked Status
if (existingClient.Key != Guid.Empty)
{
importedClient = FoundOps.Api.Models.Client.ConvertModel(existingClient.Value);
importedClient.StatusInt = (int)ImportStatus.Linked;
}
//If not, create a new Client and add it to _clients for matching later
else
{
importedClient = new FoundOps.Api.Models.Client
{
Id = Guid.NewGuid(),
Name = clientName,
ContactInfoSet = new List<ContactInfo>(),
StatusInt = (int)ImportStatus.New
};
//Since the client is new, we add it to the list of clients to be searched next time we try and match
_clients.GetOrAdd(importedClient.Id, FoundOps.Api.Models.Client.ConvertBack(importedClient));
}
importRow.Client = importedClient;
}
#endregion
#region Repeat
if (new[] { startDateCol, endDateCol, endAfterCol, repeatEveryCol, frequencyCol, frequencyDetailCol }.Any(col => col != -1))
{
var setError = false;
//check for invalid DateTime's
DateTime startDate = DateTime.UtcNow.Date;
DateTime? endDate = null;
DateTime tempDate;
if (startDateCol != -1 && !String.IsNullOrEmpty(row[startDateCol]))
{
if (DateTime.TryParse(row[startDateCol], out tempDate))
startDate = tempDate;
else
{
startDate = DateTime.UtcNow.Date;
setError = true;
}
}
if (endDateCol != -1 && !String.IsNullOrEmpty(row[endDateCol]))
{
if (DateTime.TryParse(row[endDateCol], out tempDate))
endDate = tempDate;
else
{
endDate = null;
setError = true;
}
}
//Check for invalid Int's
int? endAfterTimes = null;
int? repeatEveryTimes = null;
int tempInt;
if (endAfterCol != -1 && !String.IsNullOrEmpty(row[endAfterCol]))
{
if (Int32.TryParse(row[endAfterCol], out tempInt))
endAfterTimes = tempInt;
else
{
endAfterTimes = null;
setError = true;
}
}
if (repeatEveryCol != -1 && !String.IsNullOrEmpty(row[repeatEveryCol]))
{
if (Int32.TryParse(row[repeatEveryCol], out tempInt))
repeatEveryTimes = tempInt;
else
{
repeatEveryTimes = null;
setError = true;
}
}
//Create the Repeat object
var repeat = new Repeat
{
Id = Guid.NewGuid(),
StartDate = startDate,
EndDate = endDate,
EndAfterTimes = endAfterTimes,
RepeatEveryTimes = repeatEveryTimes
};
#region Frequency
var val = frequencyCol != -1 ? row[frequencyCol].ToLower() : String.Empty;
switch (val)
{
case "o":
case "once":
repeat.FrequencyInt = (int)Frequency.Once;
break;
case "d":
case "daily":
repeat.FrequencyInt = (int)Frequency.Daily;
break;
case "w":
case "weekly":
repeat.FrequencyInt = (int)Frequency.Weekly;
break;
case "m":
case "monthly":
repeat.FrequencyInt = (int)Frequency.Monthly;
break;
case "y":
case "yearly":
repeat.FrequencyInt = (int)Frequency.Yearly;
break;
default:
repeat.FrequencyInt = null;
break;
}
#endregion
#region Frequency Detail
val = frequencyDetailCol != -1 ? row[frequencyDetailCol].ToLower() : String.Empty;
val = val.Replace(" ", "");
if (repeat.Frequency == Frequency.Weekly)
{
var startDayOfWeek = repeat.StartDate.DayOfWeek;
//If it is empty assume the Start Date
if (string.IsNullOrEmpty(val))
repeat.FrequencyDetailAsWeeklyFrequencyDetail = new[] { startDayOfWeek };
else
{
var dayStrings = val.Split(',');
var daysOfWeek = new List<DayOfWeek>();
if (dayStrings.Any(s => s == "s" || s == "su" || s == "sun" || s == "sunday"))
daysOfWeek.Add(DayOfWeek.Sunday);
if (dayStrings.Any(s => s == "m" || s == "mo" || s == "mon" || s == "monday"))
daysOfWeek.Add(DayOfWeek.Monday);
if (dayStrings.Any(s => s == "t" || s == "tu" || s == "tue" || s == "tues" || s == "tuesday"))
daysOfWeek.Add(DayOfWeek.Tuesday);
if (dayStrings.Any(s => s == "w" || s == "we" || s == "wed" || s == "wednesday"))
daysOfWeek.Add(DayOfWeek.Wednesday);
if (dayStrings.Any(s => s == "r" || s == "th" || s == "tr" || s == "thur" || s == "thurs" || s == "thursday"))
daysOfWeek.Add(DayOfWeek.Thursday);
if (dayStrings.Any(s => s == "f" || s == "fr" || s == "fri" || s == "friday"))
daysOfWeek.Add(DayOfWeek.Friday);
if (dayStrings.Any(s => s == "s" || s == "sa" || s == "sat" || s == "saturday"))
daysOfWeek.Add(DayOfWeek.Saturday);
//Make sure the days include the startdate
if (!daysOfWeek.Contains(startDayOfWeek))
daysOfWeek.Add(startDayOfWeek);
repeat.FrequencyDetailAsWeeklyFrequencyDetail = daysOfWeek.OrderBy(e => (int)e).ToArray();
}
}
if (repeat.Frequency == Frequency.Monthly)
{
if (string.IsNullOrEmpty(val) || val == "date")
{
repeat.FrequencyDetailAsMonthlyFrequencyDetail = MonthlyFrequencyDetail.OnDayInMonth;
}
else if (val == "day")
{
var detailsAvailable = repeat.AvailableMonthlyFrequencyDetailTypes.ToList();
if (detailsAvailable.Count() > 1)
detailsAvailable.Remove(MonthlyFrequencyDetail.OnDayInMonth);
repeat.FrequencyDetailAsMonthlyFrequencyDetail = detailsAvailable.First();
}
}
#endregion
repeat.StatusInt = repeat.RepeatEveryTimes == null || repeat.FrequencyInt == null || setError
? (int)ImportStatus.Error
: (int)ImportStatus.New;
importRow.Repeat = repeat;
}
#endregion
#region Contact Info
//Create label and value dictionaries for Phone Number contact information
var phoneValueDictionary = phoneNumberValueCols.ToDictionary(p => Convert.ToInt32(p.Split(' ').ElementAt(2)), p => row[Array.IndexOf(headers, p)]);
var phoneLabelDictionary = phoneNumberLabelCols.ToDictionary(p => Convert.ToInt32(p.Split(' ').ElementAt(2)), p => row[Array.IndexOf(headers, p)]);
//Create label and value dictionaries for Email Address contact information
var emailValueDictionary = emailValueCols.ToDictionary(e => Convert.ToInt32(e.Split(' ').ElementAt(2)), e => row[Array.IndexOf(headers, e)]);
var emailLabelDictionary = emailLabelCols.ToDictionary(e => Convert.ToInt32(e.Split(' ').ElementAt(2)), e => row[Array.IndexOf(headers, e)]);
//Create label and value dictionaries for Website contact information
var websiteValueDictionary = websiteValueCols.ToDictionary(w => Convert.ToInt32(w.Split(' ').ElementAt(2)), w => row[Array.IndexOf(headers, w)]);
var websiteLabelDictionary = websiteLabelCols.ToDictionary(w => Convert.ToInt32(w.Split(' ').ElementAt(2)), w => row[Array.IndexOf(headers, w)]);
//Create label and value dictionaries for any other types of contact information
var otherValueDictionary = otherValueCols.ToDictionary(o => Convert.ToInt32(o.Split(' ').ElementAt(2)), o => row[Array.IndexOf(headers, o)]);
var otherLabelDictionary = otherLabelCols.ToDictionary(o => Convert.ToInt32(o.Split(' ').ElementAt(2)), o => row[Array.IndexOf(headers, o)]);
//Find which type of contact info is being imported the most
//This way we only have one loop
var maxLabel = Math.Max(Math.Max(phoneLabelDictionary.Count, emailLabelDictionary.Count), Math.Max(websiteLabelDictionary.Count, otherLabelDictionary.Count));
var maxValue = Math.Max(Math.Max(phoneValueDictionary.Count, emailValueDictionary.Count), Math.Max(websiteValueDictionary.Count, otherValueDictionary.Count));
var max = Math.Max(maxLabel, maxValue);
//Dictionary of Cantact Information to be imported for the row
var concurrentContactInfoDictionary = new ConcurrentDictionary<Guid, ContactInfo>();
Parallel.For((long)1, max + 1, contactIndex =>
{
//Phone
if (phoneLabelDictionary.Count >= contactIndex || phoneValueDictionary.Count >= contactIndex)
MatchContactInfo(phoneLabelDictionary, phoneValueDictionary, contactIndex, concurrentContactInfoDictionary, "Phone Number");
//Email
if (emailLabelDictionary.Count >= contactIndex || emailValueDictionary.Count >= contactIndex)
MatchContactInfo(emailLabelDictionary, emailValueDictionary, contactIndex, concurrentContactInfoDictionary, "Email Address");
//Website
if (websiteLabelDictionary.Count >= contactIndex || websiteValueDictionary.Count >= contactIndex)
MatchContactInfo(websiteLabelDictionary, websiteValueDictionary, contactIndex, concurrentContactInfoDictionary, "Website");
//Other
if (otherLabelDictionary.Count >= contactIndex || otherValueDictionary.Count >= contactIndex)
MatchContactInfo(otherLabelDictionary, otherValueDictionary, contactIndex, concurrentContactInfoDictionary, "Other");
});
//Once all the contact info sets are made or matched, add them to the ImportRow
importRow.ContactInfoSet.AddRange(concurrentContactInfoDictionary.Select(ci => ci.Value));
#endregion
//Add the ImportRow to the concurrent dictionary of ImportRows
importRowsConcurrentDictionary.GetOrAdd((int)rowIndex, importRow);
});
//Order the ImportRows by rowIndex
var importRows = importRowsConcurrentDictionary.OrderBy(kvp => kvp.Key).Select(kvp => kvp.Value).ToArray();
//Send the validated ImportRows to get suggestions and return
return SuggestEntites(importRows);
}