/// <summary>
/// Gets user by Password- login
/// </summary>
/// <param name="user">user- contain userName and password</param>
/// <returns>User</returns>
public static User GetUserDetailsByPassword(LoginUser user)
{
string query = $"SELECT * FROM managertasks.user JOIN managertasks.department ON user.departmentUserId=department.id WHERE password='******' AND userName='******'";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUserWithDepartment(reader));
}
return(users);
};
List <User> usersLogin = DBAccess.RunReader(query, func);
if (usersLogin != null && usersLogin.Count > 0 && user.Ip != null)
{
query = $"SET SQL_SAFE_UPDATES=0; UPDATE `managertasks`.`user`SET`userComputer` = '{user.Ip}' WHERE password = '******' ";
DBAccess.RunNonQuery(query);
return(usersLogin[0]);
}
if (usersLogin != null && usersLogin.Count > 0)
{
return(usersLogin[0]);
}
return(null);
}
public static UserWithoutPassword GetUserDetailsByPassword(LoginUser user)
{
//TODO:לעשות פונקציה שבודקת תווים מיוחדים עי סטור ופונקציה
string query = $"SELECT * FROM managertasks.user JOIN managertasks.department ON user.departmentUserId=department.id WHERE password='******' AND userName='******'";
Func <MySqlDataReader, List <UserWithoutPassword> > func = (reader) =>
{
List <UserWithoutPassword> users = new List <UserWithoutPassword>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
List <UserWithoutPassword> usersLogin = DBAccess.RunReader(query, func);
if (usersLogin != null && usersLogin.Count > 0)
{
query = $" UPDATE `managertasks`.`user`SET`userComputer` = '{user.Ip}' WHERE password = '******' ";
//return DBAccess.RunNonQuery(query) == 1;
return(usersLogin[0]);
}
return(null);
}
public static List <UserWithoutPassword> GetWorkerNotInProject(int projectId)
{
string query = $"SELECT * FROM managertasks.user WHERE id not in(SELECT id FROM projectworker WHERE projectId={projectId} GROUP BY id)";
Func <MySqlDataReader, List <UserWithoutPassword> > func = (reader) =>
{
List <UserWithoutPassword> userNotInProject = new List <UserWithoutPassword>();
while (reader.Read())
{
userNotInProject.Add(ConvertorUser.convertDBtoUser1(reader));
}
return(userNotInProject);
};
return(DBAccess.RunReader(query, func));
}
/// <summary>
/// Get all users
/// </summary>
/// <returns>List<User> </returns>
public static List <User> GetAllUsers()
{
string query = $"SELECT * FROM user u JOIN department d ON u.departmentUserId=d.id LEFT JOIN user uu ON u.managerId=uu.id ;";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUserWithManager(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
public static List <UserWithoutPassword> getUsersOfTeamLeader(int teamleaderId)
{
string query = $"SELECT * FROM managertasks.user WHERE managerId={teamleaderId}";
Func <MySqlDataReader, List <UserWithoutPassword> > func = (reader) =>
{
List <UserWithoutPassword> users = new List <UserWithoutPassword>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser1(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
/// <summary>
/// Gets simple workers not- manager, teamLeader
/// </summary>
/// <returns>List<User> users -simple workers</returns>
public static List <User> GetWorkers()
{
string query = $"SELECT * FROM managertasks.user JOIN managertasks.department ON user.departmentUserId=department.id WHERE Department NOT IN ('teamLeader','manager')";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
public static List <UserWithoutPassword> getWorkerInProject(int projectId)
{
string query = $"SELECT u.*, d.* FROM managertasks.user u JOIN managertasks.department d ON u.departmentUserId = d.id join projectworker p on u.id = p.id where p.projectId = {projectId }";
Func <MySqlDataReader, List <UserWithoutPassword> > func = (reader) =>
{
List <UserWithoutPassword> userNotInProject = new List <UserWithoutPassword>();
while (reader.Read())
{
userNotInProject.Add(ConvertorUser.convertDBtoUser(reader));
}
return(userNotInProject);
};
return(DBAccess.RunReader(query, func));
}
/// <summary>
/// Gets the users under the team leader
/// </summary>
/// <param name="teamleaderId">teamleaderId</param>
/// <returns>List<User></returns>
public static List <User> GetUsersOfTeamLeader(int teamleaderId)
{
string query = $"SELECT u.*, d.* FROM managertasks.user u LEFT JOIN managertasks.department d ON u.departmentUserId = d.id LEFT join USER uu on uu.id = u.managerId where u.managerId={teamleaderId}";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
public static List <User> GetWorkersByProjectId(int projectId)
{
string query = $"SELECT * FROM task.user JOIN task.TASK on task.user.idUser=task.task.idUser WHERE task.task.idProject={projectId};";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
public static List <User> GetAllowedWorkers(int teamHeadId)
{
string query = $"SELECT * FROM task.user JOIN task.status ON task.user.IdStatus=task.status.IdStatus WHERE task.status.statusname!='TeamHead' and task.status.statusname!='Manager' and task.user.managerId!={teamHeadId};";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
public static List <UserWithoutPassword> getUserByDepartment(string departmentName)
{
string query = $"SELECT * FROM managertasks.user JOIN managertasks.department ON user.departmentUserId=department.id WHERE Department='{departmentName}'";
Func <MySqlDataReader, List <UserWithoutPassword> > func = (reader) =>
{
List <UserWithoutPassword> users = new List <UserWithoutPassword>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
public static List <User> GetAllWorkersByTeamId(int id)
{
string query = $"SELECT * FROM task.user JOIN task.status ON task.user.IdStatus=task.status.IdStatus WHERE managerId={id};";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
public static List <User> GetAllTeamHeads()
{
string query = $"SELECT * FROM task.user JOIN task.status ON task.user.IdStatus=task.status.IdStatus WHERE task.status.statusname='TeamHead';";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
public static List <User> ConfirmPassword()
{
string query = $"SELECT * FROM managertasks.user JOIN managertasks.department ON user.departmentUserId=department.id ;";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoConfirm(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
public static User GetManager()
{
string query = "SELECT * FROM task.user JOIN task.status ON task.user.IdStatus=task.status.IdStatus WHERE task.status.statusname='Manager';";
List <User> managers = new List <User>();
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> manager = new List <User>();
while (reader.Read())
{
manager.Add(ConvertorUser.convertDBtoUser(reader));
}
return(manager);
};
managers = DBAccess.RunReader(query, func);
return(managers.Count > 0 ? managers[0] as User : null);
}
public static User GetUserDetails(int id)
{
string query = $"SELECT * FROM task.user JOIN task.status ON user.IdStatus=status.IdStatus WHERE idUser={id}";
List <User> users = new List <User>();
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> projectsWorker = new List <User>();
while (reader.Read())
{
projectsWorker.Add(ConvertorUser.convertDBtoUser(reader));
}
return(projectsWorker);
};
List <User> users1 = DBAccess.RunReader(query, func);
return(users1.Count > 0 ? users1[0] as User : null);
}
public static List <UserWithoutPassword> getWorkers()
{
//TODO:לקבל משתמשים שלא נמצאים בפרויקט
string query = $"SELECT * FROM managertasks.user JOIN managertasks.department ON user.departmentUserId=department.id WHERE Department IN ('UX','UI','development','QA')";
Func <MySqlDataReader, List <UserWithoutPassword> > func = (reader) =>
{
List <UserWithoutPassword> users = new List <UserWithoutPassword>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
public static UserWithoutPassword GetUserDetails(int id)
{
string query = $"SELECT * FROM managertasks.user JOIN managertasks.department ON user.departmentUserId=department.id WHERE user.id={id}";
List <UserWithoutPassword> users = new List <UserWithoutPassword>();
Func <MySqlDataReader, List <UserWithoutPassword> > func = (reader) =>
{
List <UserWithoutPassword> projectsWorker = new List <UserWithoutPassword>();
while (reader.Read())
{
projectsWorker.Add(ConvertorUser.convertDBtoUser(reader));
}
return(projectsWorker);
};
List <UserWithoutPassword> users1 = DBAccess.RunReader(query, func);
return(users1 != null && users1.Count > 0 ? users1[0] as UserWithoutPassword : null);
}
public static UserWithoutPassword GetUserDetailsComputerUser(string computerUser)
{
string query = $" SELECT* FROM managetasks.user JOIN managetasks.department ON user.departmentUserId = department.id WHERE userComputer = '{computerUser}'";
Func <MySqlDataReader, List <UserWithoutPassword> > func = (reader) =>
{
List <UserWithoutPassword> users = new List <UserWithoutPassword>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
List <UserWithoutPassword> usersLogin = DBAccess.RunReader(query, func);
if (usersLogin != null && usersLogin.Count > 0)
{
return(usersLogin[0]);
}
return(null);
}
public static UserWithoutPassword GetUserManager(int idUser)
{
string query = $" select uu.* from user u join user uu on u.managerId=uu.id where u.id={idUser}";
Func <MySqlDataReader, List <UserWithoutPassword> > func = (reader) =>
{
List <UserWithoutPassword> users = new List <UserWithoutPassword>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser1(reader));
}
return(users);
};
List <UserWithoutPassword> manager = DBAccess.RunReader(query, func);
if (manager != null && manager.Count > 0)
{
return(manager[0]);
}
return(null);
}
public static User GetUserDetailsComputerUser(string computerUser)
{
string query = $"USE task;SELECT * FROM task.user JOIN task.status ON user.IdStatus=status.IdStatus WHERE userComputer='{computerUser}'";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
List <User> usersLogin = DBAccess.RunReader(query, func);
if (usersLogin != null && usersLogin.Count > 0)
{
return(usersLogin[0]);
}
return(null);
}
public static User GetUserDetailsByPassword(string password, string userName)
{
//TODO:לעשות פונקציה שבודקת תווים מיוחדים עי סטור ופונקציה
string query = $"SELECT * FROM task.user JOIN task.status ON user.IdStatus=status.IdStatus WHERE password='******' and username='******'";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
List <User> usersLogin = DBAccess.RunReader(query, func);
if (usersLogin != null && usersLogin.Count > 0)
{
return(usersLogin[0]);
}
return(null);
}
/// <summary>
/// Forgot password-check if userName exists if exists send Email with link to change password
/// </summary>
/// <param name="userName">userName</param>
/// <returns>bool- true if exists false if not found</returns>
public static bool ForgetPassword(string userName)
{
string query = $"SELECT * FROM managertasks.user WHERE userName='******'";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(ConvertorUser.convertDBtoUser(reader));
}
return(users);
};
List <User> user = DBAccess.RunReader(query, func);
//Inserts into table requestpassword- userName and idRequest
if (user.Count > 0)
{
query = $" START TRANSACTION;INSERT INTO requestpassword(userName,startDate,endDate) value('{userName}','{DateTime.Now}' '{DateTime.Today.AddDays(1)}'); SELECT max(idRequest) from requestpassword; commit";
int result = Convert.ToInt32(DBAccess.RunScalar(query));
return(SendEmail("*****@*****.**", user[0].Email, "change password", $@"<a href='http://localhost:4200/changePassword/{result}'>change password</a>"));
}
return(false);
}
