/// <summary>
/// Gets list of all referrals in database.
/// </summary>
/// <returns>List(ReferralVM) of all referrals in database</returns>
public List <ReferralVM> GetAllReferrals()
{
List <ReferralVM> ReferralVMList = new List <ReferralVM>();
//LINQ Query to pull Referrals + associated Student/Teacher data
var referralList = (from referrals in _context.Referrals
join students in _context.Students on referrals.StudentId equals students.StudentId
join teachers in _context.Teachers on referrals.TeacherId equals teachers.TeacherId
select new
{
referrals.ReferralId,
students.StudentId,
students.StudentFirstName,
students.StudentLastName,
teachers.TeacherId,
teachers.TeacherFirstName,
teachers.TeacherLastName,
referrals.DateIssued,
referrals.Description,
referrals.Handled
})
.ToList();
//Order referrals by date
referralList = referralList.OrderBy(referrals => referrals.DateIssued).ToList();
//Add query results to List<ReferralVM>
foreach (var item in referralList)
{
ReferralVM newReferral = new ReferralVM
{
ReferralId = item.ReferralId,
StudentId = item.StudentId,
StudentFirstName = item.StudentFirstName,
StudentLastName = item.StudentLastName,
TeacherId = item.TeacherId,
TeacherFirstName = item.TeacherFirstName,
TeacherLastName = item.TeacherLastName,
DateIssued = item.DateIssued,
Description = item.Description,
Handled = item.Handled
};
ReferralVMList.Add(newReferral);
}
return(ReferralVMList);
}
/// <summary>
/// Returns list of referrals attached to teacherid.
/// </summary>
/// <param name="teacherid">ID of teacher to get submitted referrals for</param>
public List <ReferralVM> GetTeacherReferrals(int teacherid)
{
//LINQ Query to pull Referrals + associated Student/Teacher data
var referralsQuery = (from referrals in _context.Referrals
join students in _context.Students on referrals.StudentId equals students.StudentId
join teachers in _context.Teachers on referrals.TeacherId equals teachers.TeacherId
select new
{
referrals.ReferralId,
students.StudentId,
students.StudentFirstName,
students.StudentLastName,
teachers.TeacherId,
teachers.TeacherFirstName,
teachers.TeacherLastName,
referrals.DateIssued,
referrals.Description,
referrals.Handled
}).Where(r => r.TeacherId == teacherid).OrderBy(r => r.Handled);
List <ReferralVM> referralsList = new List <ReferralVM>();
foreach (var r in referralsQuery)
{
ReferralVM newReferral = new ReferralVM()
{
ReferralId = r.ReferralId,
StudentId = r.StudentId,
StudentFirstName = r.StudentFirstName,
StudentLastName = r.StudentLastName,
TeacherId = r.TeacherId,
TeacherFirstName = r.TeacherFirstName,
TeacherLastName = r.TeacherLastName,
DateIssued = r.DateIssued,
Description = r.Description,
Handled = r.Handled
};
referralsList.Add(newReferral);
}
return(referralsList);
}
/// <summary>
/// Gets record of referral mapped to referralid
/// </summary>
/// <param name="referralid">ID of referral to get record of</param>
/// <returns>ViewReferralVM with data of referral</returns>
public ViewReferralVM GetReferral(int referralid)
{
var referralQuery = (from referrals in _context.Referrals
join students in _context.Students on referrals.StudentId equals students.StudentId
join teachers in _context.Teachers on referrals.TeacherId equals teachers.TeacherId
select new
{
referrals.ReferralId,
students.StudentId,
students.StudentFirstName,
students.StudentLastName,
teachers.TeacherId,
teachers.TeacherFirstName,
teachers.TeacherLastName,
referrals.DateIssued,
referrals.Description,
referrals.Handled
})
.Where(s => s.ReferralId == referralid).FirstOrDefault();
ViewReferralVM referralToView = new ViewReferralVM
{
ReferralId = referralQuery.ReferralId,
StudentId = referralQuery.StudentId,
StudentFirstName = referralQuery.StudentFirstName,
StudentLastName = referralQuery.StudentLastName,
TeacherId = referralQuery.TeacherId,
TeacherFirstName = referralQuery.TeacherFirstName,
TeacherLastName = referralQuery.TeacherLastName,
DateIssued = referralQuery.DateIssued,
Description = referralQuery.Description,
Handled = referralQuery.Handled
};
var otherReferralsQuery = (from referrals in _context.Referrals
join students in _context.Students on referrals.StudentId equals students.StudentId
join teachers in _context.Teachers on referrals.TeacherId equals teachers.TeacherId
select new
{
referrals.ReferralId,
students.StudentId,
students.StudentFirstName,
students.StudentLastName,
teachers.TeacherId,
teachers.TeacherFirstName,
teachers.TeacherLastName,
referrals.DateIssued,
referrals.Description,
referrals.Handled
})
.Where(s => s.StudentId == referralToView.StudentId && s.ReferralId != referralid).ToList();
List <ReferralVM> otherReferrals = new List <ReferralVM>();
foreach (var referral in otherReferralsQuery)
{
ReferralVM curReferral = new ReferralVM
{
ReferralId = referral.ReferralId,
StudentId = referral.StudentId,
StudentFirstName = referral.StudentFirstName,
StudentLastName = referral.StudentLastName,
TeacherId = referral.TeacherId,
TeacherFirstName = referral.TeacherFirstName,
TeacherLastName = referral.TeacherLastName,
DateIssued = referral.DateIssued,
Description = referral.Description,
Handled = referral.Handled
};
otherReferrals.Add(curReferral);
}
referralToView.OtherReferrals = otherReferrals;
return(referralToView);
}
