public bool AddUser(UserModel user)
{
if (!CheckIfUserExists(user))
{
try
{
var universityId = SqlDriver.Row($"SELECT universityId from universities WHERE name='{user.University}';")["universityId"].ToString();
var courseId = SqlDriver.Row($"SELECT studyProgramId from studyProgrammes WHERE program='{user.Course}';")["studyProgramId"].ToString();
if (SqlDriver.Execute("INSERT INTO users (username, first_name, last_name, email, universityId, courseId, status, password) " +
"values (@0,@1,@2,@3,@4,@5,@6,@7)",
new ArrayList()
{
user.Username, user.FirstName, user.LastName, user.Email, universityId, courseId, user.Status, _passwordHasher.CreateMD5(user.Password)
}))
{
return(true);
}
else
{
_logger.Log("Query cannot be executed");
}
}
catch (Exception ex)
{
_logger.Log("User cannot be added" + Environment.NewLine + ex.Message, "ERROR");
return(false);
}
}
return(false);
}
public void ChangeUniversity(string selectedUniversity)
{
if (!selectedUniversity.Equals(GetCurrentUniversity()))
{
var sqlGetNewUniversityID = "select universityid from universities where name ='" + selectedUniversity + "';";
var newUniversityIdFromDB = SqlDriver.Fetch(sqlGetNewUniversityID);
var newUniversityId = ((Dictionary <string, object>)newUniversityIdFromDB[0])["universityId"].ToString();
var sqlUpdateUniversityID = "update users set universityid =" + newUniversityId + " where username ='******';";
try
{
if (SqlDriver.Execute(sqlUpdateUniversityID))
{
MessageBox.Show(Messages.universityUpdateSuccess);
Logger.Log(Messages.universityUpdateSuccess);
}
else
{
MessageBox.Show(Messages.universityUpdateFailed);
Logger.Log(Messages.universityUpdateFailed);
}
}
catch (Exception ex)
{
Logger.Log(ex.Message);
}
}
else
{
MessageBox.Show(Messages.newUniversitySameAsOld);
Logger.Log(Messages.newUniversitySameAsOld);
}
}
public void ChangeStatus(string selectedStatus)
{
if (!selectedStatus.Equals(GetCurrentSetting("status")))
{
var sqlUpdateStatus = "UPDATE users SET status='" + selectedStatus + "' WHERE username='******';";
try
{
if (SqlDriver.Execute(sqlUpdateStatus))
{
MessageBox.Show(Messages.statusUpdateSuccess);
Logger.Log(Messages.statusUpdateSuccess);
}
else
{
MessageBox.Show(Messages.statusUpdateFailed);
Logger.Log(Messages.statusUpdateFailed);
}
}
catch (Exception ex)
{
Logger.Log(ex.Message);
}
}
else
{
Logger.Log(Messages.newStatusSameAsOld);
}
}
public void SaveReviews()
{
SqlDriver.Execute("INSERT INTO courseReviews (presentation,clarity,feedback,encouragement,effectiveness,satisfaction,review,date,courseId,userId) " +
"values (@0,@1,@2,@3,@4,@5,@6,@7,@8,@9)", new ArrayList()
{
Presentation, Clarity, Feedback, Encouragement, Effectiveness, Satisfaction, Review, DateTime.Now.ToString(), StudyProgramId, UserId
});
}
public void SaveReviews()
{
SqlDriver.Execute("INSERT INTO universityReviews (variety,availability,accessability,quality,unions,cost,review,date,universityId,userId) " +
"values (@0,@1,@2,@3,@4,@5,@6,@7,@8,@9)", new ArrayList()
{
Variety, Availability, Accessability, Quality, Unions, Cost, Review, DateTime.Now.ToString(), UniversityId, UserId
});
}
public void ChangeUniversity()
{
var newUniversityIdFromDB = SqlDriver.Row("SELECT universityid from universities WHERE name ='" + SelectedUniversity + "';");
var newUniversityId = newUniversityIdFromDB["universityId"].ToString();
UniversityId = Convert.ToInt32(newUniversityId);
SqlDriver.Execute("UPDATE users SET universityid =@0 WHERE userid =@1;", new ArrayList {
newUniversityId, UserId
});
}
public void ChangeCourse()
{
var newCourseIdFromDB = SqlDriver.Row("SELECT studyProgramId from studyProgrammes WHERE program ='" + SelectedCourse + "';");
var newCourseId = newCourseIdFromDB["studyProgramId"].ToString();
CourseId = Convert.ToInt32(newCourseId);
SqlDriver.Execute("UPDATE users SET courseId =@0 WHERE userid =@1;", new ArrayList {
newCourseId, UserId
});
}
public void UpdateBookmarks()
{
var sqlData = SqlDriver.Row($"SELECT bookmarks FROM users WHERE userid = {UserId}");
var newBookmarks = CourseBookmarks.Split(",");
var oldBookmarks = sqlData["bookmarks"].ToString().Split(",");
var mergedBookmarks = newBookmarks.Union(oldBookmarks).ToArray();
CourseBookmarks = String.Join(",", mergedBookmarks);
CourseBookmarks = CourseBookmarks.TrimEnd(',');
SqlDriver.Execute($"UPDATE users SET bookmarks =@0 WHERE userid=@1;", new ArrayList {
CourseBookmarks, UserId
});
}
public void SaveReply()
{
Randomize randomize = delegate()
{
Random random = new Random();
return(random.Next());
};
var id = randomize();
answerId = id;
SqlDriver.Execute("INSERT INTO answers (userId,answerId,questionId,answer,timestamp) " +
"values (@0,@1,@2,@3,@4)", new ArrayList()
{
userIdReply, answerId, questionId, answer, date
});
}
public void SaveQuestion()
{
Randomize randomize = delegate()
{
Random random = new Random();
return(random.Next());
};
var id = randomize();
questionId = id;
SqlDriver.Execute("INSERT INTO questions (userId,questionId,question,message,timestamp) " +
"values (@0,@1,@2,@3,@4)", new ArrayList()
{
userId, questionId, question, message, date
});
}
public void UpdateSetting(string setting, string currentInputSetting, string newInputSetting,
string newInputSetting2, string messageSameAsOld, string messageSuccess, string messageFail,
string messageDontMatch, string incorrectCurrentSetting)
{
if (currentInputSetting.Equals(GetCurrentSetting(setting)))
{
if (newInputSetting.Equals(GetCurrentSetting(setting)))
{
MessageBox.Show(messageSameAsOld);
Logger.Log(messageSameAsOld);
}
else if (newInputSetting.Equals(newInputSetting2))
{
var sqlUpdate = "UPDATE users SET " + setting + " ='" + newInputSetting + "' WHERE username='******';";
try
{
if (SqlDriver.Execute(sqlUpdate))
{
MessageBox.Show(messageSuccess);
Logger.Log(messageSuccess);
}
else
{
MessageBox.Show(messageFail);
Logger.Log(messageFail);
}
}
catch (Exception ex)
{
Logger.Log(ex.Message);
}
}
else
{
MessageBox.Show(messageDontMatch);
Logger.Log(messageDontMatch);
}
}
else
{
MessageBox.Show(incorrectCurrentSetting);
Logger.Log(incorrectCurrentSetting);
}
}
private void UpdatePassword(string newPassword, string username)
{
var sqlUpdatePassword = "******" +
PasswordHasher.CreateMD5(newPassword) +
"' WHERE username='******';";
try
{
if (SqlDriver.Execute(sqlUpdatePassword))
{
Logger.Log(Messages.passwordUpdateSuccess);
}
else
{
Logger.Log(Messages.passwordUpdateFailed);
}
}
catch (Exception ex)
{
Logger.Log(ex.Message);
}
}
private void SubmitUniversityReview_Click(object sender, EventArgs e)
{
var panels = new List <Panel> {
variety, availability, accessability, quality, unions, cost
};
var result = ExtractReviews(panels);
var insert = "INSERT INTO universityReviews (";
var values = "VALUES (";
foreach (var item in result)
{
insert += item.Key + ",";
values += item.Value + ",";
}
insert += "userId,universityId)";
values += GetUserId(currentUser) + "," + selectedUniversity + ")";
SqlDriver.Execute(insert + values);
// Renew grid after submitting a review.
InstantiateGrid();
InstantiateProgramsGrid(selectedUniversity);
tabsController.SelectTab(universityTab);
}
private void SubmitCourseReview_Click(object sender, EventArgs e)
{
var panels = new List <Panel> {
presentation, clarity, feedback, encouragement, effectiveness, satisfaction
};
var result = ExtractReviews(panels);
string insert = "INSERT INTO coursereviews (";
string values = "VALUES (";
foreach (var item in result)
{
insert += item.Key + ",";
values += item.Value + ",";
}
insert += "userId,courseId)";
values += GetUserId(currentUser) + "," + selectedCourse + ")";
SqlDriver.Execute(insert + values);
// Renew grid after submitting a review.
InstantiateGrid();
InstantiateProgramsGrid(selectedUniversity);
tabsController.SelectTab(universityTab);
}
private void SendUserToDb(UserModel newUser)
{
string txtSqlQuery = "INSERT INTO users (username, first_name, last_name, email, universityId, status, password) VALUES ";
txtSqlQuery += $"('{newUser.Username}', '{newUser.FirstName}', '{newUser.LastName}', '{newUser.Email}', (SELECT universityId FROM universities WHERE name = '{newUser.University}'), '{newUser.Status}', '{newUser.Password}');";
try
{
if (SqlDriver.Execute(txtSqlQuery))
{
MessageBox.Show(Messages.userCreateSuccess);
Logger.Log(Messages.userCreateSuccess);
Hide();
}
else
{
MessageBox.Show(Messages.userCreateFailed);
Logger.Log(Messages.userCreateFailed);
}
}
catch (Exception e)
{
Logger.Log(e.Message);
}
}
public void ChangeStatus()
{
SqlDriver.Execute($"UPDATE users SET status =@0 WHERE userid=@1;", new ArrayList {
SelectedStatus, UserId
});
}
public void ChangePassword(IPasswordHasher passwordHasher)
{
SqlDriver.Execute($"UPDATE users SET password =@0 WHERE userid=@1;", new ArrayList {
passwordHasher.CreateMD5(NewPassword), UserId
});
}
public void SetBookmarks()
{
SqlDriver.Execute($"UPDATE users SET bookmarks =@0 WHERE userid=@1;", new ArrayList {
CourseBookmarks, UserId
});
}
public void ChangeEmail()
{
SqlDriver.Execute($"UPDATE users SET email =@0 WHERE userid=@1;", new ArrayList {
NewEmail, UserId
});
}
public void ChangeSchoolSubjectPreferences()
{
SqlDriver.Execute($"UPDATE users SET schoolPreferences =@0 WHERE userid=@1;", new ArrayList {
SchoolSubjectPreferences, UserId
});
}
public void ChangeDirectionPreferences()
{
SqlDriver.Execute($"UPDATE users SET directionPreferences =@0 WHERE userid=@1;", new ArrayList {
DirectionPreferences, UserId
});
}
public void ChangeGroupPreferences()
{
SqlDriver.Execute($"UPDATE users SET groupPreferences =@0 WHERE userid=@1;", new ArrayList {
GroupPreferences, UserId
});
}
public void ChangeUsername()
{
SqlDriver.Execute($"UPDATE users SET username =@0 WHERE userid=@1;", new ArrayList {
NewUsername, UserId
});
}