/// <summary>
/// Returns the address with the given id.
/// </summary>
/// <param name="addressId">The address id.</param>
/// <returns>The address.</returns>
public AddressDTO GetAddressById(int addressId)
{
var address = AddressQueries.CreateGetAddressDTOByIdQuery(this.Context, addressId).FirstOrDefault();
logger.Info("Retreived address with the given address id [{0}].", addressId);
return(address);
}
/// <summary>
/// Returns the address with the given id.
/// </summary>
/// <param name="addressId">The address id.</param>
/// <returns>The address.</returns>
public async Task <AddressDTO> GetAddressByIdAsync(int addressId)
{
var address = await AddressQueries.CreateGetAddressDTOByIdQuery(this.Context, addressId).FirstOrDefaultAsync();
logger.Info("Retreived address with the given address id [{0}].", addressId);
return(address);
}
public Tuple <ReadOnlyCollection <CountryRegionInfo>, ReadOnlyCollection <AddressFormattingInfo> > GetCountryRegions(string languageId)
{
using (var context = new SqliteDatabaseContext(this.Context))
{
var countryRegions = AddressQueries.GetCountryRegionIds(context, languageId);
IEnumerable <string> countryRegionIds = countryRegions.Results.Select(countryRegionId => countryRegionId.CountryRegionId);
using (TempTable countryRegionIdsTempTable = TempTableHelper.CreateScalarTempTable(context, "RECID", countryRegionIds))
{
ReadOnlyCollection <CountryRegionInfo> countryRegionInfo = AddressQueries.GetCountryRegionInfo(context, languageId, countryRegionIdsTempTable).Results;
ReadOnlyCollection <AddressFormattingInfo> formattingInfos = AddressQueries.GetAddressFormatInfo(context, countryRegionIdsTempTable).Results;
return(new Tuple <ReadOnlyCollection <CountryRegionInfo>, ReadOnlyCollection <AddressFormattingInfo> >(countryRegionInfo, formattingInfos));
}
}
}
/// <summary>
/// Returns a query to get biographical information about a participant as it relates to sevis.
/// </summary>
/// <param name="context">The context to query.</param>
/// <returns>The query to get biographical information about a participant.</returns>
public static IQueryable <BiographicalDTO> CreateGetBiographicalDataQuery(EcaContext context)
{
Contract.Requires(context != null, "The context must not be null.");
var emailAddressQuery = EmailAddressQueries.CreateGetEmailAddressDTOQuery(context);
var phoneNumberQuery = PhoneNumberQueries.CreateGetPhoneNumberDTOQuery(context);
var addressQuery = AddressQueries.CreateGetAddressDTOQuery(context);
var cityLocationTypeId = LocationType.City.Id;
var maleGenderCode = Gender.SEVIS_MALE_GENDER_CODE_VALUE;
var femaleGenderCode = Gender.SEVIS_FEMALE_GENDER_CODE_VALUE;
var cityMaxLength = PersonValidator.CITY_MAX_LENGTH;
var unitedStatesCountryName = LocationServiceAddressValidator.UNITED_STATES_COUNTRY_NAME;
var hostAddressTypeId = AddressType.Host.Id;
var homeAddressTypeId = AddressType.Home.Id;
var visitingPhoneNumberTypeId = Data.PhoneNumberType.Visiting.Id;
var personalEmailTypeId = EmailAddressType.Personal.Id;
var query = from person in context.People
let gender = person.Gender
let sevisGender = (gender.SevisGenderCode == maleGenderCode || gender.SevisGenderCode == femaleGenderCode) ? gender.SevisGenderCode : null
let numberOfCitizenships = person.CountriesOfCitizenship.Count()
let countryOfCitizenship = person.CountriesOfCitizenship.FirstOrDefault()
let sevisCountryOfCitizenship = countryOfCitizenship != null ? countryOfCitizenship.BirthCountry : null
let sevisCountryOfCitizenshipCode = sevisCountryOfCitizenship != null ? sevisCountryOfCitizenship.CountryCode : null
let hasPlaceOfBirth = person.PlaceOfBirthId.HasValue && person.PlaceOfBirth.LocationTypeId == cityLocationTypeId
let placeOfBirth = hasPlaceOfBirth ? person.PlaceOfBirth : null
let birthCity = hasPlaceOfBirth && placeOfBirth.LocationName != null
? placeOfBirth.LocationName.Length > cityMaxLength
? placeOfBirth.LocationName.Substring(0, cityMaxLength)
: placeOfBirth.LocationName
: null
let hasCountryOfBirth = hasPlaceOfBirth && placeOfBirth.CountryId.HasValue
let countryOfBirth = hasCountryOfBirth ? placeOfBirth.Country : null
let sevisCountryOfBirth = (hasCountryOfBirth && countryOfBirth.BirthCountryId.HasValue) ? countryOfBirth.BirthCountry : null
let emailAddress = emailAddressQuery
.Where(x => x.PersonId.HasValue && x.PersonId == person.PersonId)
.Where(x => x.EmailAddressTypeId == personalEmailTypeId)
.OrderByDescending(x => x.IsPrimary)
.FirstOrDefault()
let phoneNumber = phoneNumberQuery
.Where(x => x.PersonId.HasValue && x.PersonId == person.PersonId)
.Where(x => x.PhoneNumberTypeId == visitingPhoneNumberTypeId)
.OrderByDescending(x => x.IsPrimary)
.FirstOrDefault()
let residenceAddressQuery = addressQuery
.Where(x => x.PersonId.HasValue && x.PersonId == person.PersonId)
.Where(x => x.Country != unitedStatesCountryName)
.Where(x => x.AddressTypeId == homeAddressTypeId)
let residenceAddressesCount = addressQuery
.Where(x => x.PersonId.HasValue && x.PersonId == person.PersonId)
.Where(x => x.AddressTypeId == homeAddressTypeId)
.Count()
let residenceAddress = residenceAddressesCount == 1 ? residenceAddressQuery.FirstOrDefault() : null
let residenceCountry = residenceAddress != null?context.Locations.Where(x => x.LocationId == residenceAddress.CountryId).FirstOrDefault() : null
let residenceSevisCountry = residenceCountry != null ? residenceCountry.BirthCountry : null
let residenceSevisCountryCode = residenceSevisCountry != null ? residenceSevisCountry.CountryCode : null
let mailAddress = addressQuery
.Where(x => x.PersonId.HasValue && x.PersonId == person.PersonId)
.Where(x => x.Country == unitedStatesCountryName)
.Where(x => x.AddressTypeId == hostAddressTypeId)
.OrderByDescending(x => x.IsPrimary)
.FirstOrDefault()
select new BiographicalDTO
{
NumberOfCitizenships = numberOfCitizenships,
PersonId = person.PersonId,
EmailAddressId = emailAddress != null ? emailAddress.Id : default(int?),
PermanentResidenceAddressId = residenceAddress != null ? residenceAddress.AddressId : default(int?),
PhoneNumberId = phoneNumber != null ? phoneNumber.Id : default(int?),
GenderId = gender.GenderId,
FullName = new FullNameDTO
{
FirstName = person.FirstName != null && person.FirstName.Trim().Length > 0 ? person.FirstName.Trim() : null,
LastName = person.LastName != null && person.LastName.Trim().Length > 0 ? person.LastName.Trim() : null,
Suffix = person.NameSuffix != null && person.NameSuffix.Trim().Length > 0 ? person.NameSuffix.Trim() : null,
MiddleName = person.MiddleName != null && person.MiddleName.Trim().Length > 0 ? person.MiddleName.Trim() : null,
PreferredName = person.Alias != null && person.Alias.Trim().Length > 0 ? person.Alias.Trim() : null,
PassportName = person.PassportName != null && person.PassportName.Trim().Length > 0 ? person.PassportName.Trim() : null,
},
BirthDate = person.DateOfBirth.HasValue &&
(!person.IsDateOfBirthEstimated.HasValue || !person.IsDateOfBirthEstimated.Value)
? person.DateOfBirth : null,
Gender = gender != null && sevisGender != null ? sevisGender : null,
BirthCity = birthCity,
BirthCountryCode = hasCountryOfBirth ? sevisCountryOfBirth.CountryCode : null,
CitizenshipCountryCode = numberOfCitizenships == 1 ? sevisCountryOfCitizenshipCode : null,
BirthCountryReasonId = null,
BirthCountryReasonCode = null,
EmailAddress = emailAddress != null ? emailAddress.Address : null,
PermanentResidenceCountryCode = residenceSevisCountryCode,
PhoneNumber = phoneNumber != null
? phoneNumber.Number
.Replace(" ", string.Empty)
.Replace("-", string.Empty)
.Replace("+", string.Empty)
.Replace("(", string.Empty)
.Replace(")", string.Empty)
: null,
MailAddress = mailAddress,
};
return(query);
}
public AddressQueryController(AddressQueries queries)
{
Queries = queries;
}
