public List <Course> GetCoursesRequiredByDegree(int degreeId)
{
string command;
if (degreeId > 0)
{
command =
CourseSelect() + $@"
INNER JOIN dbo.tblDegreeRequirement drq ON crs.crs_id = drq.crs_id
WHERE drq.deg_id = '{degreeId}'
";
}
else
{
command =
CourseSelect() + $@"
INNER JOIN dbo.tblDegreeRequirement drq ON crs.crs_id = drq.crs_id
";
}
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <Course> courses = CourseListRead(reader);
service.Close();
return(courses);
}
public List <Course> GetCoursesByConcentration(int concentrationId)
{
string command;
if (concentrationId > 0)
{
command =
CourseSelect() + $@"
INNER JOIN dbo.tblConcentrationCourse ctc ON crs.crs_id = ctc.crs_id
WHERE ctc.con_id = '{concentrationId}'
";
}
else
{
command =
CourseSelect() + $@"
INNER JOIN dbo.tblConcentrationCourse ctc ON crs.crs_id = ctc.crs_id
";
}
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <Course> courses = CourseListRead(reader);
service.Close();
return(courses);
}
public List <Course> GetCoursePrereqs(int courseId)
{
string command;
if (courseId > 0)
{
command =
CourseSelect() + $@"
INNER JOIN dbo.tblCoursePrerequisite prq ON crs.crs_id = prq.prq_id
WHERE prq.crs_id = {courseId}
";
}
else
{
command =
CourseSelect() + $@"
INNER JOIN dbo.tblCoursePrerequisite prq ON crs.crs_id = prq.prq_id
";
}
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <Course> courses = CourseListRead(reader);
service.Close();
return(courses);
}
public List <GPFSession> GetSessions()
{
string command = GPFSessionSelect();
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <GPFSession> sessions = GPFSessionListRead(reader);
service.Close();
return(sessions);
}
public List <Course> GetCourses()
{
string command = CourseSelect();
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <Course> courses = CourseListRead(reader);
service.Close();
return(courses);
}
public List <Account> GetAccounts()
{
string command = AccountSelect();
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <Account> accounts = AccountListRead(reader);
service.Close();
return(accounts);
}
public List <Concentration> GetConcentrationsByDegreeId(int degreeId)
{
string command;
if (degreeId > 0)
{
command = $@"
SELECT con.con_id,
con.con_title,
con.deg_id
FROM dbo.tblConcentration con
WHERE con.deg_id = '{degreeId}'
";
}
else
{
command = $@"
SELECT con.con_id,
con.con_title,
con.deg_id
FROM dbo.tblConcentration con
";
}
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <Concentration> concentrations = new List <Concentration>();
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
Concentration concentration = new Concentration();
concentration.Id = (int)reader["con_id"];
if (reader["con_title"] != DBNull.Value)
{
concentration.Title = (string)reader["con_title"];
}
if (reader["deg_id"] != DBNull.Value)
{
concentration.DegreeId = (int)reader["deg_id"];
}
concentrations.Add(concentration);
}
}
service.Close();
return(concentrations);
}
public Account GetAccount(string username)
{
string command =
AccountSelect() + $@"
WHERE acc.acc_username = '******'
";
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
Account account = AccountRead(reader);
service.Close();
return(account);
}
public Course GetCoursesById(int courseId)
{
string command =
CourseSelect() + $@"
WHERE crs.crs_id = '{courseId}'
";
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
Course course = CourseRead(reader);
service.Close();
return(course);
}
public List <Account> GetAccountsByRole(AccountRole role)
{
string command =
AccountSelect() + $@"
WHERE acc.acc_role = '{role.Value}'
";
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <Account> accounts = AccountListRead(reader);
service.Close();
return(accounts);
}
public List <GPFSession> GetSessionsByAccountId(int accountId)
{
string command =
GPFSessionSelect() + $@"
WHERE gpf.acc_id = '{accountId}'
";
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <GPFSession> sessions = GPFSessionListRead(reader);
service.Close();
return(sessions);
}
public GPFSession GetSessionById(int sessionId)
{
string command =
GPFSessionSelect() + $@"
WHERE gpf.gpf_id = '{sessionId}'
";
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
GPFSession session = GPFSessionRead(reader);
service.Close();
return(session);
}
public List <Course> GetAllElectivesByConcentration(int excludeConcentrationId)
{
string command =
CourseSelect() + $@"
INNER JOIN dbo.tblConcentrationCourse ctc ON crs.crs_id = ctc.crs_id
WHERE ctc.con_id <> '{excludeConcentrationId}'
";
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <Course> courses = CourseListRead(reader);
service.Close();
return(courses);
}
public List <Course> GetCourseHistory(int accountId)
{
string command =
CourseSelect() + $@"
INNER JOIN dbo.tblCourseHistory coh ON crs.crs_id = coh.crs_id
WHERE coh.acc_id = '{accountId}'
";
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <Course> courses = CourseListRead(reader);
service.Close();
return(courses);
}
public Degree GetDegreeById(int degreeId)
{
string command =
DegreeSelect() + $@"
WHERE deg.deg_id = '{degreeId}'
";
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
Degree degree = DegreeRead(reader);
if (degree != null)
{
degree.Concentrations = GetConcentrationsByDegreeId(degree.Id);
}
service.Close();
return(degree);
}
public List <Degree> GetDegrees()
{
string command = DegreeSelect();
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <Degree> degrees = DegreeListRead(reader);
if (degrees != null && degrees.Count > 0)
{
foreach (var degree in degrees)
{
degree.Concentrations = GetConcentrationsByDegreeId(degree.Id);
}
}
service.Close();
return(degrees);
}
public bool Login(string username, string password)
{
string command =
AccountSelect() + $@"
WHERE acc.acc_username = '******'
AND acc.acc_password = '******'
";
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
Account account = AccountRead(reader);
service.Close();
if (account != null && account.Id > 0)
{
return(true);
}
else
{
return(false);
}
}
