/// <summary>
/// Gets workers that work in this project
/// </summary>
/// <param name="projectId">projectId</param>
/// <returns> List<ProjectWorker>-worker in this project</returns>
public static List <ProjectWorker> GetUsersBelongProjects(int projectId)
{
string query = $"SELECT * FROM project p JOIN projectworker pw ON p.projectId =pw.projectId JOIN user u ON pw.id =u.id WHERE pw.projectId={projectId} AND pw.isActive=true";
List <ProjectWorker> projectWorker = new List <ProjectWorker>();
Func <MySqlDataReader, List <ProjectWorker> > func = (reader) =>
{
List <ProjectWorker> projectWorkers = new List <ProjectWorker>();
while (reader.Read())
{
projectWorkers.Add(ConvertProjectWorker.convertDBtoProjectWorkersWithProjectAndUser(reader));
}
return(projectWorkers);
};
projectWorker = DBAccess.RunReader(query, func);
if (projectWorker != null)
{
foreach (var item in projectWorker)
{
query = $" SELECT sum(sumHours) FROM managertasks.presentday where id ={item.UserId} and projectId ={item.ProjectId}";
string result = DBAccess.RunScalar(query).ToString();
item.SumHoursDone = result != string.Empty ? decimal.Parse(result.ToString()) : 0;
}
}
return(projectWorker);
}
/// <summary>
/// Gets Worker projects
/// </summary>
/// <param name="id">workerId</param>
/// <returns>List<ProjectWorker>- worker projects</returns>
public static List <ProjectWorker> GetProjectsUser(int id)
{
string query = $"SELECT *,(select sum(sumHours) from presentday pd where pd.id=pw.id and pd.projectId=p.projectId group by id) as sumHoursDone FROM managertasks.projectworker pw join project p on pw.projectId = p.projectId where pw.id = {id} and p.isFinish=false ";
Func <MySqlDataReader, List <ProjectWorker> > func = (reader) =>
{
List <ProjectWorker> projectsList = new List <ProjectWorker>();
while (reader.Read())
{
projectsList.Add(ConvertProjectWorker.convertDBtoProjectWorkersWithProject(reader));
}
return(projectsList);
};
return(DBAccess.RunReader(query, func));
}
public static List <ProjectWorker> getUsersOfTeamLeader(int teamleaderId)
{
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 ";
Func <MySqlDataReader, List <ProjectWorker> > func = (reader) =>
{
List <ProjectWorker> userNotInProject = new List <ProjectWorker>();
while (reader.Read())
{
userNotInProject.Add(ConvertProjectWorker.convertDBtoProjectWorkersWithProjectAndUser(reader));
}
return(userNotInProject);
};
return(DBAccess.RunReader(query, func));
}
public static List <ProjectWorker> GetProjectsUser(int id)
{
string query = $"SELECT* FROM managertasks.projectworker pw join project p on pw.projectId = p.projectId where pw.id ={id } ";
Func <MySqlDataReader, List <ProjectWorker> > func = (reader) =>
{
List <ProjectWorker> projectsList = new List <ProjectWorker>();
while (reader.Read())
{
projectsList.Add(ConvertProjectWorker.convertDBtoProjectWorkersWithProject(reader));
}
return(projectsList);
};
return(DBAccess.RunReader(query, func));
}
public static List <ProjectWorker> getHoursAndProjectForUser(int userId)
{
string query = $"SELECT pw.projectId,pw.id,pw.hoursForProject,p.name,u.userName FROM project p JOIN projectworker pw ON p.projectId =pw.projectId JOIN user u ON pw.id =u.id WHERE pw.id={userId}";
Func <MySqlDataReader, List <ProjectWorker> > func = (reader) =>
{
List <ProjectWorker> users = new List <ProjectWorker>();
while (reader.Read())
{
users.Add(ConvertProjectWorker.convertDBtoProjectWorkersWithProjectAndUser1(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
