//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)); }