public static ZipInfo ToZipInfo(this EFDAL.Entity.Zip zip)
{
var retval = new ZipInfo()
{
ID = zip.ZipId,
Name = zip.Name,
City = zip.City,
State = zip.State,
Country = zip.Country ?? "US",
};
if (zip.Latitude != null && zip.Longitude != null)
{
retval.Latitude = zip.Latitude.Value;
retval.Longitude = zip.Longitude.Value;
}
return(retval);
}
private List <EFDAL.Entity.Zip> Lookup(string term)
{
try
{
var retval = new List <EFDAL.Entity.Zip>();
if (string.IsNullOrEmpty(term))
{
return(retval);
}
//Strip comma in case have not enter state yet
term = term.TrimEnd(new char[] { ',' });
//Now search for the different location formats
using (var context = new GeoLocationEntities())
{
//If numeric then assume it is zip
if (int.TryParse(term, out int zipCode))
{
//If zip code then return zips
retval.AddRange(
context.Zip
.Where(x => x.Name.Contains(term))
.ToList());
Logger.LogInfo($"GetLookup: Path1, term={term}, Count={retval.Count}");
return(retval);
}
//Check Canada postal code
//If found one then return it
var cpostTerm = term.Replace(" ", string.Empty);
if (cpostTerm.Length == 6)
{
var cpost = context.CanadaPostalCode.FirstOrDefault(x => x.PostalCode == cpostTerm);
if (cpost != null)
{
retval.Add(new EFDAL.Entity.Zip
{
City = cpost.City,
Latitude = cpost.Latitude,
Longitude = cpost.Longitude,
Name = cpost.PostalCode,
Country = "Canada",
});
return(retval);
}
}
//If two strings with comma then assume city state
var arr = term.Split(',');
if (arr.Length == 2)
{
var cityValue = arr[0].Trim();
var stateValue = arr[1].Trim();
var zipValue = string.Empty;
//If there is numeric ZIP on the end then split it out too
var arr2 = stateValue.Split(' ');
if (arr2.Length == 2 && arr2[1].ToInt32() > 0)
{
stateValue = arr2[0].Trim();
zipValue = arr2[1].Trim();
}
//If there is a ZIP then use it
//Zips can span cities so there may be multiple
var zipItems = context.Zip
.Where(x => x.Name == zipValue)
.ToList();
EFDAL.Entity.Zip singleZip = null;
//If there is a ZIP code included and not a single match
//determine if can match city, state
if (!string.IsNullOrEmpty(zipValue) && zipItems.Count > 1)
{
var lmabda = zipItems.Where(x => x.City.ToLower() == cityValue.ToLower());
if (lmabda.Count() == 1)
{
singleZip = lmabda.First();
}
}
//If the "state" value is 6 chars, check if it is Canadian
//Check Canada postal code
//If found one then return it
var cpostTerm2 = stateValue.Replace(" ", string.Empty);
if (cpostTerm2.Length == 6)
{
var cpost = context.CanadaPostalCode.FirstOrDefault(x => x.PostalCode == cpostTerm2);
if (cpost != null && (string.IsNullOrEmpty(cityValue) || cityValue.ToLower() == cpost.City.ToLower()))
{
retval.Add(new EFDAL.Entity.Zip
{
City = cpost.City,
Latitude = cpost.Latitude,
Longitude = cpost.Longitude,
Name = cpost.PostalCode,
});
return(retval);
}
}
if (singleZip == null && zipItems.Count == 1)
{
singleZip = zipItems.FirstOrDefault();
}
else if (singleZip == null) //Look for specific city, state
{
singleZip = context.Zip.FirstOrDefault(x => x.City == cityValue && x.State == stateValue);
if (singleZip == null)
{
singleZip = context.Zip.FirstOrDefault(x => x.City.Contains(cityValue) && x.State == stateValue);
}
}
//If the ZIP was found then return it
if (singleZip != null)
{
retval.Add(singleZip);
}
//If it was not found then look again, less specifically
if (!retval.Any())
{
var state = context.State.FirstOrDefault(x => x.Name.Contains(stateValue) || x.Abbr == stateValue);
if (state != null)
{
stateValue = state.Abbr;
}
//If (city AND state) match OR (city and ZIP) match OR (city, state zip)
var list = (from z in context.Zip
join c in context.City on new { z.City, z.State } equals new { City = c.Name, c.State } into q
from c in q.DefaultIfEmpty()
where ((z.City.Contains(cityValue) && (z.State.Contains(stateValue))) ||
(z.City.Contains(cityValue) && (z.Name.Contains(stateValue))))
orderby c.Population descending
select new { z, c })
.ToList()
.Select(x => new EFDAL.Entity.Zip {
City = x.z.City, State = x.z.State, Population = x.c?.Population, Latitude = x.z.Latitude, Longitude = x.z.Longitude
}) //, Name = x.Name
.Distinct(new ZipComparer())
.OrderByDescending(x => x.Population)
.Take(50)
.ToList();
retval.AddRange(list);
}
Logger.LogInfo($"GetLookup: Path2, term={term}, Count={retval.Count}");
}
else
{
//Group by city/state and sum population so can order by largest overall population, not individual zip code
var list = (from z in context.Zip
join c in context.City on new { z.City, z.State } equals new { City = c.Name, c.State } into q
from c in q.DefaultIfEmpty()
where (z.City.Contains(term) || z.Name.Contains(term) || z.State.Contains(term))
orderby c.Population descending
select new { z, c })
.ToList()
.Select(x => new EFDAL.Entity.Zip {
City = x.z.City, State = x.z.State, Population = x.c?.Population, Latitude = x.z.Latitude, Longitude = x.z.Longitude
}) //, Name = x.Name
.OrderByDescending(x => x.Population)
.Distinct(new ZipComparer())
.Take(50)
.ToList();
retval.AddRange(list);
Logger.LogInfo($"GetLookup: Path3, term={term}, Count={retval.Count}");
}
return(retval);
}
}
catch (Exception ex)
{
Logger.LogError(ex);
throw;
}
}
