//public static List<ChartTeamLeader> GetWorkerTeamLeaderWithHourDo(int teamleaderId)
//{
// List<UserWithoutPassword> worker = getUsersOfTeamLeader(teamleaderId);
// string query = $"select u.id, u.userName,p.projectId,p.name,pw.hoursForProject,sum(pd.sumHours) from project p join projectworker pw on pw.projectId = p.projectId join user u on u.id = pw.id left join presentday pd on pd.id = pw.id where u.managerId = {teamleaderId} group by pw.projectId ,pw.id";
// Func<MySqlDataReader, List<ChartTeamProject>> func = (reader) =>
// {
// List<ChartTeamProject> chartTeamProject = new List<ChartTeamProject>();
// while (reader.Read())
// {
// chartTeamProject.Add(ConvertProjectWorker.convertChartTeamProject(reader));
// }
// return chartTeamProject;
// };
// var projectWorker = DBAccess.RunReader(query, func);
// var projectWorkerChart= projectWorker.GroupBy(p => p.IdWorker);
// return new List<ChartTeamLeader>();
//}
//כמה שעות עשה כל עובד לפרויקט מסוים
public static List <SumHoursDoneUser> getUserProjectTeamHourDo(int idTeamLeader, int idProject)
{
string query = $"select sum(pd.sumHours),u.userName from user u join projectworker pwon pw.id = u.id join presentday pd on pd.id = u.id where u.managerId = {idTeamLeader} and pw.projectId = {idProject} and pd.projectId = 1 group by pw.projectId ,pw.id";
List <SumHoursDoneUser> projectWorker = new List <SumHoursDoneUser>();
Func <MySqlDataReader, List <SumHoursDoneUser> > func = (reader) =>
{
List <SumHoursDoneUser> projectWorkers = new List <SumHoursDoneUser>();
while (reader.Read())
{
projectWorkers.Add(ConvertSumHoursUser.convertDBtoSumHoursUserProject(reader));
}
return(projectWorkers);
};
return(projectWorker = DBAccess.RunReader(query, func));
}
public static List <SumHoursUserDoneByMonth> getSumHoursUserDoneByMonth(int id)
{
string query = $"SELECT p.name,sum(sumHours),month(timeBegin) FROM managertasks.presentday pr join project p on pr.projectId=p.projectId where id={id} and year(timeBegin)=YEAR(CURDATE()) group by month(timeBegin),pr.projectId,p.name order by month(timeBegin)";
Func <MySqlDataReader, List <SumHoursUserDoneByMonth> > func = (reader) =>
{
List <SumHoursUserDoneByMonth> dataList = new List <SumHoursUserDoneByMonth>();
while (reader.Read())
{
dataList.Add(ConvertSumHoursUser.convertDBtoSumHoursUserDoneByMonth(reader));
}
return(dataList);
};
return(DBAccess.RunReader(query, func));
}
/// <summary>
/// Gets sum of hours a worker has worked on according to his projects
/// </summary>
/// <param name="userId">workerId</param>
/// <returns>List<SumHoursDoneUser>- sum hours worked according project</returns>
public static List <SumHoursDoneUser> GetHoursForUserProjects(int userId)
{
string query = $"SELECT * FROM sumHoursForUserProject WHERE id={userId}";
Func <MySqlDataReader, List <SumHoursDoneUser> > func = (reader) =>
{
List <SumHoursDoneUser> users = new List <SumHoursDoneUser>();
while (reader.Read())
{
users.Add(ConvertSumHoursUser.convertDBtoSumHoursUser1(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
/// <summary>
/// Gets the amount of hours they did the worker to a specific team project and project.
/// </summary>
/// <param name="teamleaderId">teamLeaderId</param>
/// <param name="projectId">projectId</param>
/// <returns>List<SumHoursDoneUser>sum hours done worker</returns>
public static List <SumHoursDoneUser> GetWorkerHourDoProjects(int teamleaderId, int idProject)
{
string query = $"SELECT sum(pd.sumHours),u.userName FROM user u JOIN projectworker pw ON pw.id = u.id LEFT JOIN presentday pd ON pd.id = u.id WHERE u.managerId = {teamleaderId} AND pw.projectId = {idProject} AND pd.projectId = {idProject} GROUP BY pw.projectId ,pw.id ";
Func <MySqlDataReader, List <SumHoursDoneUser> > func = (reader) =>
{
List <SumHoursDoneUser> sumHoursDoneUser = new List <SumHoursDoneUser>();
while (reader.Read())
{
sumHoursDoneUser.Add(ConvertSumHoursUser.convertDBtoSumHoursUser(reader));
}
return(sumHoursDoneUser);
};
return(DBAccess.RunReader(query, func));
}
/// <summary>
/// Gets the amount of hours they did the worker to a specific team project and project.
/// </summary>
/// <param name="teamleaderId">teamLeaderId</param>
/// <param name="projectId">projectId</param>
/// <returns>List<SumHoursDoneUser>sum hours done worker</returns>
public static List <SumHoursDoneUser> GetSumHoursDoneForUsers(int teamleaderId, int projectId)
{
string query = $"SELECT sum(sumHours),u.userName FROM presentday p JOIN user u on u.id= p.id WHERE u.managerId ={teamleaderId} AND projectId={projectId} GROUP BY u.id";
Func <MySqlDataReader, List <SumHoursDoneUser> > func = (reader) =>
{
List <SumHoursDoneUser> users = new List <SumHoursDoneUser>();
while (reader.Read())
{
users.Add(ConvertSumHoursUser.convertDBtoSumHoursUser(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
