public static void InsertProject(string name, string description, int max_grade, string course_id, DateTime due_date)
{
using (NpgsqlConnection con = new NpgsqlConnection(connectionString))
{
try
{
con.Open();
string project_id = RegNum.getNextValue("Project");
string sql = "INSERT INTO Projects VALUES(@id, @name, @description, @max_grade, @course_id, @due_date)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("id", project_id);
cmd.Parameters.AddWithValue("name", name);
cmd.Parameters.AddWithValue("description", description);
cmd.Parameters.AddWithValue("max_grade", max_grade);
cmd.Parameters.AddWithValue("course_id", course_id);
cmd.Parameters.AddWithValue("due_date", due_date);
cmd.ExecuteNonQuery();
foreach (Team team in GetTeams(course_id))
{
string sql_temp = "INSERT INTO ProjectsOfTeam VALUES(@project_id, null, @team_id, null)";
NpgsqlCommand cmd_temp = new NpgsqlCommand(sql_temp, con);
cmd_temp.Parameters.AddWithValue("project_id", project_id);
cmd_temp.Parameters.AddWithValue("team_id", team.getTeamID());
cmd_temp.ExecuteNonQuery();
}
}
catch (Exception msg)
{
MessageBox.Show("There was a problem while executing this action. Please contact the developers.");
}
}
}
public static void InsertUser(string userType, string name, string password, string surname, Email email)
{
using (NpgsqlConnection con = new NpgsqlConnection(connectionString))
{
try
{
con.Open();
string sql = "INSERT INTO Users VALUES(@reg_num, @name, @password, @surname, @email)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, con);
RegNum regNum = new RegNum(RegNum.getNextValue(userType));
cmd.Parameters.AddWithValue("reg_num", regNum.getRegNumString());
cmd.Parameters.AddWithValue("name", name);
cmd.Parameters.AddWithValue("password", password);
cmd.Parameters.AddWithValue("surname", surname);
cmd.Parameters.AddWithValue("email", email.getEmailAddress());
cmd.ExecuteNonQuery();
sql = "INSERT INTO " + userType + "s VALUES(@reg_num)";
cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("reg_num", regNum.getRegNumString());
cmd.ExecuteNonQuery();
}
catch (Exception msg)
{
MessageBox.Show("There was a problem while executing this action. Please contact the developers.");
return;
}
}
}
// Constructors
public Admin(RegNum registrationNumber, string password, string name, string surname, Email email)
{
this._registrationNumber = registrationNumber;
this._password = password;
this._name = name;
this._surname = surname;
this._email = email;
}
// Constructors
public Professor(RegNum registrationNumber, string password, string name, string surname, Email email, List <Course> courseList)
{
this._registrationNumber = registrationNumber;
this._password = password;
this._name = name;
this._surname = surname;
this._email = email;
this.courseList = courseList;
}
// TODO remove static from createUser in class diagram
public User createUser(string userType, RegNum reg_num, string password, string name, string surname, Email email)
{
switch (userType)
{
case UserTypes.STUDENT:
return(new Student(reg_num, password, name, surname, email));
case UserTypes.PROFESSOR:
return(new Professor(reg_num, password, name, surname, email, Database.GetCoursesForProf(reg_num.getRegNumString())));
case UserTypes.ADMIN:
return(new Admin(reg_num, password, name, surname, email));
default:
// TODO display error message
return(null);
}
}
public static void CreateTeam(List <string> students, string course_id)
{
using (NpgsqlConnection con = new NpgsqlConnection(connectionString))
{
try
{
con.Open();
string team_id = RegNum.getNextValue("Team");
string sql = "INSERT INTO teams VALUES(@team_id)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("team_id", team_id);
cmd.ExecuteNonQuery();
foreach (string stu_reg_num in students)
{
sql = "INSERT INTO StudentsTeams VALUES(@stu_reg_num, @team_id, @course_id)";
cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("stu_reg_num", stu_reg_num);
cmd.Parameters.AddWithValue("team_id", team_id);
cmd.Parameters.AddWithValue("course_id", course_id);
cmd.ExecuteNonQuery();
}
foreach (Project proj in GetProjectsForCourse(course_id))
{
sql = "INSERT INTO ProjectsOfTeam VALUES(@project_id, null, @team_id, null)";
cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("project_id", proj.getProjectID());
cmd.Parameters.AddWithValue("team_id", team_id);
cmd.ExecuteNonQuery();
}
}
catch (Exception msg)
{
MessageBox.Show("There was a problem connecting to the server.");
}
}
}
public static User GetUser(string userType, string userRegNum)
{
using (NpgsqlConnection con = new NpgsqlConnection(connectionString))
{
try
{
con.Open();
string sql = "SELECT * FROM Users WHERE reg_num=@regNum";
NpgsqlCommand cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("regNum", userRegNum);
NpgsqlDataReader results = cmd.ExecuteReader();
if (results.Read())
{
RegNum reg_num = new RegNum(results.GetString(results.GetOrdinal("reg_num")));
string name = results.GetString(results.GetOrdinal("name"));
string surname = results.GetString(results.GetOrdinal("surname"));
string password = results.GetString(results.GetOrdinal("password"));
Email email = new Email(results.GetString(results.GetOrdinal("email")));
return(UserFactory.getInstance().createUser(userType, reg_num, password, name, surname, email));
}
else
{
MessageBox.Show("No such user -- Database get user");
return(null);
}
}
catch (Exception msg)
{
MessageBox.Show("There was a problem while executing this action. Please contact the developers.");
return(null);
}
}
}
public static void UploadProject(byte[] file, string name, DateTime date, string team_id, string project_id)
{
using (NpgsqlConnection con = new NpgsqlConnection(connectionString))
{
try
{
con.Open();
string sql = "SELECT project_file_id FROM Projectsofteam WHERE team_id=@team_id AND project_id=@project_id";
NpgsqlCommand cmd = new NpgsqlCommand(sql, con);
cmd.Parameters.AddWithValue("team_id", team_id);
cmd.Parameters.AddWithValue("project_id", project_id);
NpgsqlDataReader results = cmd.ExecuteReader();
string file_id = null;
if (results.Read())
{
file_id = results[0].ToString();
}
else
{
MessageBox.Show("Could not upload file");
return;
}
results.Close();
if (string.IsNullOrEmpty(file_id))
{
string id = RegNum.getNextValue("projectfile");
sql = "INSERT INTO projectfiles VALUES(@id, @file, @name, @date)";
NpgsqlCommand cmd2 = new NpgsqlCommand(sql, con);
cmd2.Parameters.AddWithValue("id", id);
cmd2.Parameters.AddWithValue("file", file);
cmd2.Parameters.AddWithValue("name", name);
cmd2.Parameters.AddWithValue("date", date);
cmd2.ExecuteNonQuery();
sql = "UPDATE projectsofteam SET project_file_id=@project_file_id WHERE team_id=@team_id AND project_id=@project_id";
NpgsqlCommand cmd3 = new NpgsqlCommand(sql, con);
cmd3.Parameters.AddWithValue("project_file_id", id);
cmd3.Parameters.AddWithValue("team_id", team_id);
cmd3.Parameters.AddWithValue("project_id", project_id);
cmd3.ExecuteNonQuery();
}
else
{
sql = "UPDATE projectfiles SET (file, name, date)=(@file, @name, @date) WHERE id=@id";
NpgsqlCommand cmd4 = new NpgsqlCommand(sql, con);
cmd4.Parameters.AddWithValue("file", file);
cmd4.Parameters.AddWithValue("name", name);
cmd4.Parameters.AddWithValue("id", file_id);
cmd4.Parameters.AddWithValue("date", date);
cmd4.ExecuteNonQuery();
}
}
catch (Exception msg)
{
MessageBox.Show("There was a problem while executing this action. Please contact the developers.");
}
}
}