/// <summary>
/// Retrieves the social media dto with the given id.
/// </summary>
/// <param name="id">The id of the email address.</param>
/// <returns>The email address dto.</returns>
public async Task <EmailAddressDTO> GetByIdAsync(int id)
{
var dto = await EmailAddressQueries.CreateGetEmailAddressDTOByIdQuery(this.Context, id).FirstOrDefaultAsync();
logger.Info("Retrieved the email address dto with the given id [{0}].", id);
return(dto);
}
/// <summary>
/// Retrieves the email address dto with the given id.
/// </summary>
/// <param name="id">The id of the email Address.</param>
/// <returns>The email address dto.</returns>
public EmailAddressDTO GetById(int id)
{
var dto = EmailAddressQueries.CreateGetEmailAddressDTOByIdQuery(this.Context, id).FirstOrDefault();
logger.Info("Retrieved the social media dto with the given id [{0}].", id);
return(dto);
}
/// <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);
}
/// <summary>
/// Returns a query to get biographical data for the dependents of the participant with the given id.
/// </summary>
/// <param name="context">The context to query.</param>
/// <param name="participantId">The participant id.</param>
/// <returns>The query to get biographical data of a participant's dependents.</returns>
public static IQueryable <DependentBiographicalDTO> CreateGetParticipantDependentsBiographicalQuery(EcaContext context, int participantId)
{
Contract.Requires(context != null, "The context must not be null.");
var maleGenderCode = Gender.SEVIS_MALE_GENDER_CODE_VALUE;
var femaleGenderCode = Gender.SEVIS_FEMALE_GENDER_CODE_VALUE;
var familyMemberIds = context.Participants
.Where(x => x.ParticipantId == participantId && x.PersonId.HasValue)
.SelectMany(x => x.Person.Family.Select(f => f.DependentId));
var locationsQuery = LocationQueries.CreateGetLocationsQuery(context);
var emailAddressQuery = EmailAddressQueries.CreateGetEmailAddressDTOQuery(context);
var query = from dependent in context.PersonDependents
let gender = context.Genders.Where(x => x.GenderId == dependent.GenderId).FirstOrDefault()
let sevisGender = gender != null && (gender.SevisGenderCode == maleGenderCode || gender.SevisGenderCode == femaleGenderCode) ? gender.SevisGenderCode : null
let residenceCountry = context.Locations.Where(x => x.LocationId == dependent.PlaceOfResidenceId).FirstOrDefault()
let residenceSevisCountry = residenceCountry != null ? residenceCountry.BirthCountry : null
let residenceSevisCountryCode = residenceSevisCountry != null ? residenceSevisCountry.CountryCode : null
let birthCity = context.Locations.Where(x => x.LocationId == dependent.PlaceOfBirthId).FirstOrDefault()
let birthCountry = birthCity != null ? birthCity.Country : null
let sevisBirthCountry = birthCountry != null ? birthCountry.BirthCountry : null
let sevisBirthCountryCode = sevisBirthCountry != null ? sevisBirthCountry.CountryCode : null
let birthDate = dependent.DateOfBirth
let numberOfCitizenships = dependent.CountriesOfCitizenship.Count()
let countryOfCitizenship = dependent.CountriesOfCitizenship.OrderByDescending(x => x.IsPrimary).FirstOrDefault()
let countryOfCitizenshipLocation = countryOfCitizenship != null ? countryOfCitizenship.Location : null
let sevisCountryOfCitizenship = countryOfCitizenshipLocation != null ? countryOfCitizenshipLocation.BirthCountry : null
let sevisCountryOfCitizenshipCode = sevisCountryOfCitizenship != null ? sevisCountryOfCitizenship.CountryCode : null
let relationship = context.DependentTypes.Where(x => x.DependentTypeId == dependent.DependentTypeId).FirstOrDefault()
let relationshipCode = relationship != null ? relationship.SevisDependentTypeCode : null
let emailAddress = dependent.EmailAddresses
.OrderByDescending(x => x.IsPrimary)
.FirstOrDefault()
let birthCountryReason = context.BirthCountryReasons.Where(x => x.BirthCountryReasonId == dependent.BirthCountryReasonId).FirstOrDefault()
where familyMemberIds.Contains(dependent.DependentId) && !dependent.IsSevisDeleted
select new DependentBiographicalDTO
{
PermanentResidenceAddressId = null,
BirthCity = birthCity.LocationName,
BirthCountryCode = sevisBirthCountryCode,
BirthCountryReasonId = dependent.BirthCountryReasonId,
BirthCountryReasonCode = birthCountryReason != null ? birthCountryReason.BirthReasonCode : null,
BirthDate = birthDate,
CitizenshipCountryCode = numberOfCitizenships == 1 ? sevisCountryOfCitizenshipCode : null,
EmailAddress = emailAddress != null ? emailAddress.Address : null,
EmailAddressId = emailAddress != null ? emailAddress.EmailAddressId : default(int?),
IsTravelingWithParticipant = dependent.IsTravellingWithParticipant,
IsDeleted = dependent.IsDeleted,
FullName = new FullNameDTO
{
FirstName = dependent.FirstName != null && dependent.FirstName.Trim().Length > 0 ? dependent.FirstName.Trim() : null,
LastName = dependent.LastName != null && dependent.LastName.Trim().Length > 0 ? dependent.LastName.Trim() : null,
Suffix = dependent.NameSuffix != null && dependent.NameSuffix.Trim().Length > 0 ? dependent.NameSuffix.Trim() : null,
PassportName = dependent.PassportName != null && dependent.PassportName.Trim().Length > 0 ? dependent.PassportName.Trim() : null,
PreferredName = dependent.PreferredName != null && dependent.PreferredName.Trim().Length > 0 ? dependent.PreferredName.Trim() : null,
},
Gender = gender != null && sevisGender != null ? sevisGender : null,
GenderId = gender.GenderId,
NumberOfCitizenships = numberOfCitizenships,
PermanentResidenceCountryCode = residenceSevisCountryCode,
PersonId = dependent.DependentId,
ParticipantId = participantId,
Relationship = relationshipCode,
DependentTypeId = relationship != null ? relationship.DependentTypeId : -1,
SevisId = dependent.SevisId
};
return(query);
}
