/// <summary>
/// Returns the contact information for a person
/// </summary>
/// <param name="context">The context to query</param>
/// <param name="personId">The person id to lookup</param>
/// <returns>The contact information for a person</returns>
public static IQueryable <ContactInfoDTO> CreateGetContactInfoByIdQuery(EcaContext context, int personId)
{
Contract.Requires(context != null, "The context must not be null.");
var emailAddressQuery = EmailAddressQueries.CreateGetEmailAddressDTOQuery(context);
var phoneNumberQuery = PhoneNumberQueries.CreateGetPhoneNumberDTOQuery(context);
var query = from person in context.People
let participantPerson = context.ParticipantPersons.Where(x => x.Participant.PersonId == person.PersonId).FirstOrDefault()
let currentParticipant = person.Participations.OrderByDescending(p => p.ParticipantStatusId).FirstOrDefault()
where person.PersonId == personId
select new ContactInfoDTO
{
EmailAddresses = emailAddressQuery.Where(x => x.PersonId == personId),
SocialMedias = person.SocialMedias.Select(x => new SocialMediaDTO
{
Id = x.SocialMediaId,
SocialMediaType = x.SocialMediaType.SocialMediaTypeName,
SocialMediaTypeId = x.SocialMediaTypeId,
Value = x.SocialMediaValue
}).OrderBy(s => s.SocialMediaType),
PhoneNumbers = phoneNumberQuery.Where(x => x.PersonId == personId),
HasContactAgreement = person.HasContactAgreement,
PersonId = person.PersonId,
ParticipantId = currentParticipant == null ? 0 : currentParticipant.ParticipantId,
ProjectId = currentParticipant == null ? 0 : currentParticipant.ProjectId,
SevisId = currentParticipant == null ? "" : currentParticipant.ParticipantPerson.SevisId
};
return(query);
}
/// <summary>
/// Returns a query to get contacts.
/// </summary>
/// <param name="context">The context to query.</param>
/// <returns>The query to get contacts.</returns>
public static IQueryable <ContactDTO> CreateContactQuery(EcaContext context)
{
Contract.Requires(context != null, "The context must not be null.");
var emailAddressQuery = EmailAddressQueries.CreateGetEmailAddressDTOQuery(context);
var phoneNumbersQuery = PhoneNumberQueries.CreateGetPhoneNumberDTOQuery(context);
var query = from contact in context.Contacts
let phoneNumbers = phoneNumbersQuery.Where(x => x.ContactId == contact.ContactId)
let emailAddresses = emailAddressQuery.Where(x => x.ContactId == contact.ContactId)
select new ContactDTO
{
EmailAddresses = emailAddresses,
EmailAddressValues = emailAddresses.Select(x => x.Address),
FullName = contact.FullName,
Id = contact.ContactId,
PhoneNumbers = phoneNumbers,
PhoneNumberValues = phoneNumbers.Select(x => x.Number),
Position = contact.Position
};
return(query);
}
/// <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);
}