public IQueryable <OrganisationProvider> GetOrganisationProviders(string email)
{
var userOrganisations = OrganisationProviders.FromSql(
$"select oi.* from organisation_provider oi " +
$"join organisation o on o.id = oi.organisation_id " +
$"join organisation_user ou on ou.organisation_id = o.id " +
$"join \"user\" u on ou.user_id = u.id " +
$"where lower(u.email) = lower(@email)",
new NpgsqlParameter("email", email)
)
.Where(x => x.Provider.RecruitmentCycle.Year == RecruitmentCycle.CurrentYear)
.Include(x => x.Organisation)
.Include(x => x.Provider);
return(userOrganisations);
}
public OrganisationProvider GetOrganisationProvider(string email, string providerCode)
{
var userOrganisations = OrganisationProviders.FromSql(
$"select oi.* from organisation_provider oi " +
$"join provider i on oi.provider_id = i.id " +
$"join organisation o on oi.organisation_id = o.id " +
$"join organisation_user ou on ou.organisation_id = o.id " +
$"join \"user\" u on ou.user_id = u.id " +
$"where lower(u.email) = lower(@email) and Lower(i.provider_code) = lower(@providerCode)",
new NpgsqlParameter("email", email), new NpgsqlParameter("providerCode", providerCode)
)
.Where(x => x.Provider.RecruitmentCycle.Year == RecruitmentCycle.CurrentYear)
.Include(x => x.Organisation)
.Include(x => x.Provider)
.SingleOrDefault();
return(userOrganisations);
}