public static List <TypUser> getTypeUser()
{
List <TypUser> tu = new List <TypUser>();
//try
//{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("GetTypeUsers", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Class.TypUser typeuser = new Class.TypUser();
typeuser.ID_Typ = reader.GetInt16(0);
typeuser.DescriptionTypUser = reader.GetString(1);
tu.Add(typeuser);
}
}
}
//}
//catch (Exception ex)
//{
// esito.esito = false;
// esito.Message= ex.Message;
//}
return(tu);
}
public static Result DeleteTypeUser(int idtype)
{
Result esito = new Result();
try
{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("DeleteTypeUser", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("idtype", idtype);
command.ExecuteNonQuery();
esito.result = true;
}
}
catch (Exception ex)
{
esito.result = false;
esito.Message = ex.Message;
}
return(esito);
}
public static Result UpdatePath(int idpath, string databasePathIn)
{
Result res = new Result();
try
{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("UpdatePath", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("idpath", idpath);
command.Parameters.AddWithValue("pathLabel", databasePathIn);
command.ExecuteNonQuery();
res.result = true;
}
}
catch (Exception ex)
{
res.result = false;
res.Message = ex.Message;
}
return(res);
}
public static Result InsertPath(string dbPath)
{
Result res = new Result();
//try
//{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("InsertPath", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("pathlabel", dbPath);
command.ExecuteNonQuery();
res.result = true;
}
//}
//catch (Exception ex)
//{
// esito.result = false;
// esito.Message= ex.Message;
//}
return(res);
}
public static List <Class.TypUser> GetTypeUserForTestCase(long idTestCase)
{
List <Class.TypUser> listtypeUser = new List <Class.TypUser>();
try
{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("GetTypeUserForTestCase", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Idtestcase", idTestCase);
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Class.TypUser typeUser = new Class.TypUser();
typeUser.ID_Typ = reader.GetInt32(1);
listtypeUser.Add(typeUser);
}
}
}
}
catch (Exception)
{
}
return(listtypeUser);
}
public static List <Class.Path> getPaths()
{
List <Class.Path> u = new List <Class.Path>();
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("GetDatabasePath", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@idpath", DBNull.Value);
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Class.Path dbPath = new Class.Path();
dbPath.Id_path = reader.GetInt16(0);
dbPath.DatabasePath = reader.GetString(1);
u.Add(dbPath);
}
}
}
return(u);
}
public static Class.Path getPathById(long id_path)
{
Class.Path dbpath = new Class.Path();
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("GetDatabasePath", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@idpath", id_path);
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
dbpath.Id_path = reader.GetInt16(0);
dbpath.DatabasePath = reader.GetString(1);
}
}
}
//}
//catch (Exception ex)
//{
// esito.esito = false;
// esito.Message= ex.Message;
//}
return(dbpath);
}
public static bool VerifyExistUser(string username)
{
bool found = false;
//try
//{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("SELECT username FROM user WHERE username=@username", conn);
command.Parameters.AddWithValue("@username", username);
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
found = true;
}
}
}
//}
//catch (Exception ex)
//{
// esito.esito = false;
// esito.Message= ex.Message;
//}
return(found);
}
public static List <Class.User> getUser()
{
List <Class.User> u = new List <Class.User>();
//try
//{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("GetUser", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@iduser", DBNull.Value);
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Class.User user = new Class.User();
user.Id_user = reader.GetInt64(0);
user.Name = reader.GetString(1);
user.Surname = reader.GetString(2);
user.Email = reader.GetString(3);
user.DescriptionType = reader.GetString(4);
user.YearsOfExperience = reader.GetInt16(5);
user.Username = reader.GetString(9);
if (reader["DateInsert"] != DBNull.Value)
{
user.DateInsert = reader.GetDateTime("DateInsert");
user.YearsOfExperience = (DateTime.Now.Year - user.DateInsert.Year) + user.YearsOfExperience;
}
u.Add(user);
}
}
}
//}
//catch (Exception ex)
//{
// esito.esito = false;
// esito.Message= ex.Message;
//}
return(u);
}
// checks if password is correct and returns corresponding user
public static Class.User checkPassword(string username, string password)
{
Class.User userLogin = null;
PasswordHandling pw = new PasswordHandling();
try
{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("getPassword", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("inusername", username);
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
// get salt and hash from db
string hashedPassword = reader.GetString(0);
string salt = reader.GetString(1);
// check if password is valid
pw.checkPassword(password, hashedPassword, salt);
}
}
conn.Close();
// log in if user is valid
if (pw.userIsVerified())
{
userLogin = LoginUser(username, pw.getHashedPassword());
}
pw = null;
}
}
catch (Exception)
{
return(null);
}
return(userLogin);
}
public static void writeUserList()
{
List <Class.User> userList = new List <Class.User>();
using (MySqlConnection conn = DataAccessBase.GetConnection()){
conn.Open();
MySqlCommand command = new MySqlCommand("getListOfUsers", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Class.User user = new Class.User();
user.Id_user = reader.GetInt64(0);
user.Name = reader.GetString(1);
user.Surname = reader.GetString(2);
user.Email = reader.GetString(3);
user.DescriptionType = reader.GetString(4);
user.YearsOfExperience = reader.GetInt16(5);
user.titleId = reader.GetInt32(6);
user.Username = reader.GetString(7);
user.title = reader.GetString(8);
userList.Add(user);
}
}
}
if (userList.Count > 0)
{
//System.IO.File.Exists(pathInUser)
using (System.IO.StreamWriter file = new System.IO.StreamWriter(Constant.pathUserList))
{
int amountOfUsers = 0;
file.WriteLine("UserId; title/salutation; first name;surname; username; email; user type; years of experience;");
foreach (Class.User user in userList)
{
file.WriteLine(user.Id_user + "; " + user.title + "; " + user.Name + "; " + user.Surname + "; " + user.Username + "; " + user.Email + "; " + user.DescriptionType + "; " + user.YearsOfExperience + ";");
amountOfUsers++;
}
file.WriteLine("total amount of users: " + amountOfUsers);
}
}
}
public static Result DeletePath(int idpath)
{
Result esito = new Result();
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("DeletePath", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("idpath", idpath);
command.ExecuteNonQuery();
esito.result = true;
}
return(esito);
}
public static Class.User LoginUser(string username, string password)
{
Class.User user = null;
//try
//{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("LoginUser", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("inusername", username);
command.Parameters.AddWithValue("Password", password);
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
user = new Class.User();
user.Name = reader.GetString(0);
user.Surname = reader.GetString(1);
user.Email = reader.GetString(2);
user.Type = reader.GetInt16(3);
user.YearsOfExperience = reader.GetInt16(4);
user.Id_user = reader.GetInt32(5);
user.title = reader.GetString(6);
user.Username = reader.GetString(7);
user.DescriptionType = reader.GetString(8);
}
}
}
//}
//catch (Exception ex)
//{
// esito.esito = false;
// esito.Message= ex.Message;
//}
return(user);
}
public static Result UpdateTypeUser(TypUser tu)
{
Result res = new Result();
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("UpdateTypeUser", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("idtypeuser", tu.ID_Typ);
command.Parameters.AddWithValue("TypeUser", tu.DescriptionTypUser);
command.ExecuteNonQuery();
res.result = true;
}
return(res);
}
public static Result RegisterUser(Class.User u)
{
Result res = new Result();
try
{
PasswordHandling pw = new PasswordHandling();
pw.hashPassword(u.Password);
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("RegisterUser", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("Name", u.Name);
command.Parameters.AddWithValue("Surname", u.Surname);
command.Parameters.AddWithValue("title_Id", u.titleId);
command.Parameters.AddWithValue("Password", pw.getHashedPassword());
command.Parameters.AddWithValue("Salt", pw.getSalt());
command.Parameters.AddWithValue("username", u.Username);
command.Parameters.AddWithValue("Email", u.Email);
command.Parameters.AddWithValue("Type", u.Type);
command.Parameters.AddWithValue("YearOfExperience", u.YearsOfExperience);
command.Parameters.AddWithValue("DateInsert", u.DateInsert);
command.ExecuteNonQuery();
res.result = true;
}
}
catch (Exception ex)
{
res.result = false;
res.Message = ex.Message;
}
return(res);
}
public static TypUser getTypeUser(int TypeUser)
{
Class.TypUser typeuser = new Class.TypUser();
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("GetTypeUser", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("idtypeuser", TypeUser);
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
typeuser.ID_Typ = reader.GetInt16(0);
typeuser.DescriptionTypUser = reader.GetString(1);
}
}
}
return(typeuser);
}
public static Result UpdateProfileView(long iduser, Class.User user)
{
Result res = new Result();
PasswordHandling newPassword = new PasswordHandling();
newPassword.generateSalt();
newPassword.hashPassword(user.Password);
try
{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("UpdateProfileView", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("name", user.Name);
command.Parameters.AddWithValue("Surname", user.Surname);
command.Parameters.AddWithValue("PW", newPassword.getHashedPassword());
command.Parameters.AddWithValue("Salt", newPassword.getSalt());
command.Parameters.AddWithValue("username", user.Username);
command.Parameters.AddWithValue("Email", user.Email);
command.Parameters.AddWithValue("iduser", iduser);
command.ExecuteNonQuery();
res.result = true;
}
}
catch (Exception ex)
{
res.result = false;
res.Message = ex.Message;
}
return(res);
}
public static Class.User getUserById(long id_user)
{
Class.User user = new Class.User();
//try
//{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("getUser", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@iduser", id_user);
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
user.Id_user = reader.GetInt64(0);
user.Name = reader.GetString(1);
user.Surname = reader.GetString(2);
user.Email = reader.GetString(3);
user.DescriptionType = reader.GetString(4);
user.YearsOfExperience = reader.GetInt16(5);
user.Type = reader.GetInt16(6);
user.Password = reader.GetString(8);
user.Username = reader.GetString(9);
}
}
}
//}
//catch (Exception ex)
//{
// esito.esito = false;
// esito.Message= ex.Message;
//}
return(user);
}
public static List <Title> getTitle()
{
List <Title> tu = new List <Title>();
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("GetTitles", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Class.Title titleUser = new Class.Title();
titleUser.title_Id = reader.GetInt16(0);
titleUser.title = reader.GetString(1);
tu.Add(titleUser);
}
}
}
return(tu);
}
public static Result ChangePasswordUser(string mail)
{
Result res = new Result();
PasswordHandling newPassword = new PasswordHandling();
newPassword.salt_length = 10;
newPassword.generateSalt();
string password = newPassword.getSalt();
newPassword.generateSalt();
newPassword.hashPassword(password);
string dbEmail = "mail address not in db";
string username = "******";
try
{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("ChangePassword", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("PW", newPassword.getHashedPassword());
command.Parameters.AddWithValue("salt", newPassword.getSalt());
command.Parameters.AddWithValue("Email", mail.Trim());
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["email"] != DBNull.Value)
{
dbEmail = reader.GetString(0);
}
else
{
dbEmail = "mail address not in db";
}
}
}
res.result = true;
}
}
catch (Exception ex)
{
res.result = false;
res.Message = ex.Message;
}
if (res.result) // if password has been successfully changed, send an email to user
{
if (mail == dbEmail)
{
string salutation = "Sir / Madam";
try
{
using (MySqlConnection conn = DataAccessBase.GetConnection())
{
conn.Open();
MySqlCommand command = new MySqlCommand("getUserWithEmail", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("in_email", mail);
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader["Title"] != DBNull.Value)
{
salutation = reader.GetString(0) + " ";
}
if (reader["surname"] != DBNull.Value)
{
salutation += reader.GetString(1);
}
username = reader.GetString(2);
}
}
}
}catch (Exception) {}
List <string> message = Constant.changePasswordMail(salutation, password, username);
MyEmail notification = new MyEmail();
// notification.email.Bcc.Add(notification.sender); // send a copy of the mail to the email address of the admin
notification.sendEmail(mail, message[0], message[1]);
}
else
{
}
}
return(res);
}