public IEnumerable <CourseInstance> GetCurrent([FromBody] RequestObject req)
{
var courses = new List <CourseInstance>();
var query = @"DECLARE @ProfessorId INT;
SET @ProfessorId = @Set_ProfessorId;
SELECT c.ID
,c.Title
,c.Descr
,c.IsActive
,ca.ID AS CourseAssignmentId
FROM Courses c
JOIN CoursesAssignments ca ON c.ID = ca.CourseID
JOIN AcademicTerms at ON at.ID = ca.AcademicTermID
WHERE ca.ProfessorID = @ProfessorId
AND c.IsActive = 1
AND GETDATE() BETWEEN at.StartDate AND at.EndDate";
var cmd = new SqlCommand(query);
cmd.Parameters.AddWithValue("@Set_ProfessorId", req.Id);
var sdt = AdoHelper.GetDataTable(cmd, _connString);
if (!sdt.ErrorFound)
{
foreach (DataRow dr in sdt.DataTable.Rows)
{
var c = new CourseInstance();
int id;
if (int.TryParse(dr["ID"].ToString(), out id))
{
c.Id = id;
}
c.Title = dr["Title"].ToString();
c.Descr = dr["Descr"].ToString();
c.IsActive = dr["IsActive"].ToString() == "1";
int caid;
if (int.TryParse(dr["CourseAssignmentId"].ToString(), out caid))
{
c.CourseAssignmentId = caid;
}
courses.Add(c);
}
}
return(courses);
}
/// <summary>
/// Get the current academic term id
/// </summary>
/// <returns></returns>
private int?GetCurrentAcademicTermId()
{
var query = @"select a.ID from AcademicTerms a
where GETDATE() between a.StartDate and a.EndDate";
var cmd = new SqlCommand(query);
var sdt = AdoHelper.GetDataTable(cmd, _connString);
if (!sdt.ErrorFound && sdt.DataTable.Rows.Count > 0)
{
var termId = sdt.DataTable.Rows[0]["ID"].ToString();
int id;
if (int.TryParse(termId, out id))
{
return(id);
}
return(null);
}
return(null);
}
