public Person GetPersonByUsername(string userName, PersonIncludes personIncludes)
{
using (var unitOfWork = unitOfWorkFactory.CreateUnitOfWork())
{
var personLoginQueryable = unitOfWork.PersonLogins.GetQueryable().Where(p => p.LoginID == userName);
if (personLoginQueryable.Any())
{
int personId = personLoginQueryable.First().PersonID;
return(GetPersonById(personId, personIncludes));
}
else
{
throw new Exception("Person with given username not found");
}
}
}
public PageOfList <Person> SearchPersons(int pageNumber, PersonSearchColumn personSearchColumn, SortDirection sortDirection, int numRecordsInPage, Dictionary <PersonSearchColumn, string> searchCriteria)
{
// Search for persons using the SQL repository
// Determine the starting row
int startRow;
int totalItemCount = 0;
if (numRecordsInPage == -1)
{
startRow = 0;
}
else
{
startRow = (pageNumber - 1) * numRecordsInPage;
}
PageOfList <Person> searchResults = null;
using (var unitOfWork = unitOfWorkFactory.CreateUnitOfWork())
{
IQueryable <Person> personsQueryable = unitOfWork.Persons.GetQueryable();
foreach (KeyValuePair <PersonSearchColumn, string> entry in searchCriteria)
{
if (entry.Key == PersonSearchColumn.PersonID)
{
var personID = Int32.Parse(entry.Value);
personsQueryable = personsQueryable.Where(p => p.PersonID == personID);
}
if (entry.Key == PersonSearchColumn.FirstName)
{
personsQueryable = personsQueryable.Where(p => p.FirstName.StartsWith(entry.Value));
}
if (entry.Key == PersonSearchColumn.LastName)
{
personsQueryable = personsQueryable.Where(p => p.LastName.StartsWith(entry.Value));
}
if (entry.Key == PersonSearchColumn.EmailAddress)
{
personsQueryable = personsQueryable.Where(p => p.EmailAddress.StartsWith(entry.Value));
}
if (entry.Key == PersonSearchColumn.Phone)
{
personsQueryable = personsQueryable.Where(p => p.Phones.Any(h => h.PhoneTypeID == 1 && h.Number.StartsWith(entry.Value)));
}
if (entry.Key == PersonSearchColumn.City)
{
personsQueryable = personsQueryable.Where(p => p.Addresses.Any(pa => pa.AddressTypeID == 1 && pa.Address.City.StartsWith(entry.Value)));
}
if (entry.Key == PersonSearchColumn.State)
{
personsQueryable = personsQueryable.Where(p => p.Addresses.Any(pa => pa.AddressTypeID == 1 && pa.Address.State.StartsWith(entry.Value)));
}
if (entry.Key == PersonSearchColumn.StreetAddress)
{
personsQueryable = personsQueryable.Where(p => p.Addresses.Any(pa => pa.AddressTypeID == 1 && pa.Address.StreetAddress.StartsWith(entry.Value)));
}
if (entry.Key == PersonSearchColumn.Zip)
{
personsQueryable = personsQueryable.Where(p => p.Addresses.Any(pa => pa.AddressTypeID == 1 && pa.Address.Zip.StartsWith(entry.Value)));
}
}
// Include the related phones and addresses in the search query
personsQueryable = personsQueryable.Include("Addresses.Address").Include("Phones");
IOrderedQueryable <Person> personOrderedQueryable = null;
// Apply the sorting using the requested sort column and direction
if (sortDirection == SortDirection.Ascending)
{
if (personSearchColumn == PersonSearchColumn.PersonID)
{
personOrderedQueryable = personsQueryable.OrderBy(p => p.PersonID);
}
else if (personSearchColumn == PersonSearchColumn.FirstName)
{
personOrderedQueryable = personsQueryable.OrderBy(p => p.FirstName);
}
else if (personSearchColumn == PersonSearchColumn.LastName)
{
personOrderedQueryable = personsQueryable.OrderBy(p => p.LastName);
}
else if (personSearchColumn == PersonSearchColumn.EmailAddress)
{
personOrderedQueryable = personsQueryable.OrderBy(p => p.EmailAddress);
}
else if (personSearchColumn == PersonSearchColumn.Phone)
{
personOrderedQueryable = personsQueryable.OrderBy(p => p.Phones.FirstOrDefault().Number);
}
else if (personSearchColumn == PersonSearchColumn.City)
{
personOrderedQueryable = personsQueryable.OrderBy(p => p.Addresses.FirstOrDefault().Address.City);
}
else if (personSearchColumn == PersonSearchColumn.State)
{
personOrderedQueryable = personsQueryable.OrderBy(p => p.Addresses.FirstOrDefault().Address.State);
}
else if (personSearchColumn == PersonSearchColumn.StreetAddress)
{
personOrderedQueryable = personsQueryable.OrderBy(p => p.Addresses.FirstOrDefault().Address.StreetAddress);
}
else if (personSearchColumn == PersonSearchColumn.Zip)
{
personOrderedQueryable = personsQueryable.OrderBy(p => p.Addresses.FirstOrDefault().Address.Zip);
}
}
else
{
if (personSearchColumn == PersonSearchColumn.PersonID)
{
personOrderedQueryable = personsQueryable.OrderByDescending(p => p.PersonID);
}
else if (personSearchColumn == PersonSearchColumn.FirstName)
{
personOrderedQueryable = personsQueryable.OrderByDescending(p => p.FirstName);
}
else if (personSearchColumn == PersonSearchColumn.LastName)
{
personOrderedQueryable = personsQueryable.OrderByDescending(p => p.LastName);
}
else if (personSearchColumn == PersonSearchColumn.EmailAddress)
{
personOrderedQueryable = personsQueryable.OrderByDescending(p => p.EmailAddress);
}
else if (personSearchColumn == PersonSearchColumn.Phone)
{
personOrderedQueryable = personsQueryable.OrderByDescending(p => p.Phones.FirstOrDefault().Number);
}
else if (personSearchColumn == PersonSearchColumn.City)
{
personOrderedQueryable = personsQueryable.OrderByDescending(p => p.Addresses.FirstOrDefault().Address.City);
}
else if (personSearchColumn == PersonSearchColumn.State)
{
personOrderedQueryable = personsQueryable.OrderByDescending(p => p.Addresses.FirstOrDefault().Address.State);
}
else if (personSearchColumn == PersonSearchColumn.StreetAddress)
{
personOrderedQueryable = personsQueryable.OrderByDescending(p => p.Addresses.FirstOrDefault().Address.StreetAddress);
}
else if (personSearchColumn == PersonSearchColumn.Zip)
{
personOrderedQueryable = personsQueryable.OrderByDescending(p => p.Addresses.FirstOrDefault().Address.Zip);
}
}
// Get the number of records
int recordCount = personOrderedQueryable.Count();
// Apply the paging and make the SQL call
List <Person> persons;
if (numRecordsInPage != -1)
{
persons = personOrderedQueryable.Skip(startRow)
.Take(numRecordsInPage)
.ToList();
}
else
{
persons = personOrderedQueryable.ToList();
}
searchResults = new PageOfList <Person>(persons, pageNumber, numRecordsInPage, recordCount);
}
return(searchResults);
}
