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 bool Delete(Account account)
{
string command =
$@"
DELETE gpf
FROM dbo.tblGPFSession gpf
WHERE gpf.acc_id = '{account.Id}'
DELETE coh
FROM dbo.tblCourseHistory coh
WHERE coh.acc_id = '{account.Id}'
DELETE acc
FROM dbo.tblAccount acc
WHERE acc.acc_id = '{account.Id}'
";
DatabaseService service = new DatabaseService();
int rowsAffected = service.Write(command);
service.Close();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool AddCourseToHistory(Account account, int courseId)
{
string command =
$@"
INSERT INTO dbo.tblCourseHistory (
acc_id,
crs_id
)
SELECT acc.acc_id,
'{courseId}'
FROM dbo.tblAccount acc
WHERE acc.acc_id = '{account.Id}'
";
DatabaseService service = new DatabaseService();
int rowsAffected = service.Write(command);
service.Close();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
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 bool Register(Account account)
{
if (account == null || string.IsNullOrWhiteSpace(account.Username) || string.IsNullOrWhiteSpace(account.Password))
{
return(false);
}
string command =
$@"
INSERT INTO dbo.tblAccount (
acc_username,
acc_password,
acc_first_name,
acc_last_name
)
SELECT
'{account.Username}',
'{account.Password}',
'{account.FirstName}',
'{account.LastName}'
";
/** Fields can be updated from profile after registering.
* acc_street,
* acc_city,
* acc_state,
* acc_zip,
* acc_phone,
* acc_role,
* deg_id,
* con_id
* '{account.Street}',
* '{account.City}',
* '{account.State}',
* '{account.Zip}',
* '{account.Phone}',
* '{account.Role}',
* '{account.Degree.Id}',
* '{account.Concentration.Id}'
*/
DatabaseService service = new DatabaseService();
int rowsAffected = service.Write(command);
service.Close();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
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 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 <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 <Account> GetAccounts()
{
string command = AccountSelect();
DatabaseService service = new DatabaseService();
SqlDataReader reader = service.Read(command);
List <Account> accounts = AccountListRead(reader);
service.Close();
return(accounts);
}
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 <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 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 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 SaveSession(GPFSession session)
{
string command =
$@"
INSERT INTO dbo.tblGPFSession (
acc_id,
deg_id,
con_id,
gpf_entering_year,
gpf_entering_quarter,
gpf_classes_per_quarter,
gpf_class_delivery
)
SELECT acc.acc_id,
'{session.Degree.Id}',
'{session.Concentration.Id}',
'{session.EnteringTerm.Year}',
'{session.EnteringTerm.Quarter.Value}',
'{session.ClassesPerQuarter}',
'{session.ClassDeliveryOption.Value}'
FROM dbo.tblAccount acc
WHERE acc.acc_id = '{session.Account.Id}'
";
DatabaseService service = new DatabaseService();
int rowsAffected = service.Write(command);
service.Close();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
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);
}
}
public bool RemoveCourseFromHistory(Account account, int courseId)
{
string command =
$@"
DELETE coh
FROM dbo.tblCourseHistory coh
WHERE coh.acc_id = '{account.Id}'
AND coh.crs_id = '{courseId}'
";
DatabaseService service = new DatabaseService();
int rowsAffected = service.Write(command);
service.Close();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool Update(Account account)
{
if (account == null)
{
return(false);
}
string command = $@"
UPDATE acc SET ";
if (!String.IsNullOrWhiteSpace(account.FirstName))
{
command += $" acc.acc_first_name = '{account.FirstName}',";
}
if (!String.IsNullOrWhiteSpace(account.LastName))
{
command += $" acc.acc_last_name = '{account.LastName}',";
}
if (!String.IsNullOrWhiteSpace(account.Street))
{
command += $" acc.acc_street = '{account.Street}',";
}
if (!String.IsNullOrWhiteSpace(account.City))
{
command += $" acc.acc_city = '{account.City}',";
}
if (!String.IsNullOrWhiteSpace(account.State))
{
command += $" acc.acc_state = '{account.State}',";
}
if (!String.IsNullOrWhiteSpace(account.Zip))
{
command += $" acc.acc_zip = '{account.Zip}',";
}
if (account.Role != null)
{
command += $" acc.acc_role = '{account.Role.Value}',";
}
if (account.Degree != null)
{
command += $" acc.deg_id = '{account.Degree.Id}',";
}
if (account.Concentration != null)
{
command += $" acc.con_id = '{account.Concentration.Id}'";
}
command += $@"
FROM dbo.tblAccount acc
WHERE acc.acc_id = '{account.Id}'
";
/**
* acc.acc_phone = '{account.Phone}',
* acc.acc_role = '{account.Role.Value}',
*/
DatabaseService service = new DatabaseService();
int rowsAffected = service.Write(command);
service.Close();
if (rowsAffected > 0)
{
return(true);
}
else
{
return(false);
}
}
