public static List <Project> getAllProjects()
{
string query = $"SELECT * FROM task_managment.projects";
Func <MySqlDataReader, List <Project> > func = (reader) =>
{
List <Project> projects = new List <Project>();
while (reader.Read())
{
projects.Add(GlobalLogic.InitProject(reader));
}
return(projects);
};
return(DBAccess.RunReader(query, func));
}
public static List <User> getAllTeamLeaders()
{
string query = $"SELECT * FROM task_managment.users WHERE status=2";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> teamLeaders = new List <User>();
while (reader.Read())
{
teamLeaders.Add(GlobalLogic.InitUser(reader));
}
return(teamLeaders);
};
return(DBAccess.RunReader(query, func));
}
public static List <User> GetWorkersDeatails(int teamLeaderId)
{
string query = $"SELECT * FROM task_managment.users WHERE manager={teamLeaderId}";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> workers = new List <User>();
while (reader.Read())
{
workers.Add(GlobalLogic.InitUser(reader));
}
return(workers);
};
return(DBAccess.RunReader(query, func));
}
public static List <Project> GetProjectDeatails(int teamLeaderId)
{
string query = $"SELECT * FROM task_managment.projects WHERE team_leader={teamLeaderId}";
Func <MySqlDataReader, List <Project> > func = (reader) =>
{
List <Project> projects = new List <Project>();
while (reader.Read())
{
projects.Add(GlobalLogic.InitProject(reader));
}
return(projects);
};
return(DBAccess.RunReader(query, func));
}
public static List <User> GetAllUsers()
{
string query = $"SELECT * FROM task_managment.users where is_active=1 && status!=1";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
users.Add(GlobalLogic.InitUser(reader));
}
return(users);
};
return(DBAccess.RunReader(query, func));
}
public static List <User> GetWorkersForProject(int teamLeaderId)
{
string query = $"SELECT u.* FROM users u JOIN user_projects up ON u.user_id= up.user_id" +
$" WHERE project_id IN (SELECT project_id from projects WHERE team_leader= {teamLeaderId})" +
$" GRUP BY u.user_name; ";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> workers = new List <User>();
while (reader.Read())
{
workers.Add(GlobalLogic.InitUser(reader));
}
return(workers);
};
return(DBAccess.RunReader(query, func));
}
public static List <Email> GetEmailDetailsQuery()
{
string teamLeaderMail = null;
List <Email> emails = new List <Email>();
string query = $"SELECT * FROM projects;";
List <Project> projectList = new List <Project>();
Func <MySqlDataReader, List <Project> > func = (reader) =>
{
List <Project> projects = new List <Project>();
while (reader.Read())
{
projects.Add(GlobalLogic.InitProject(reader));
}
return(projects);
};
projectList = DBAccess.RunReader(query, func);
projectList.ForEach(project =>
{
teamLeaderMail = null;
var q = $"SELECT u.user_id,u.email FROM users u join user_projects up ON u.user_id=up.user_id" +
$" LEFT JOIN daily_presence dp ON dp.user_project_id=up.user_project_id" +
$" WHERE up.project_id={project.Id} AND u.status>2 " +
$" AND p.end_date <= NOW() + INTERVAL 1 DAY" +
$" GROUP BY u.email, up.allocated_hours, u.user_id" +
$" HAVING up.allocated_hours >" +
$" HOUR(SEC_TO_TIME(SUM(TIME_TO_SEC(dp.end) - TIME_TO_SEC(dp.start)))) + (MINUTE(SEC_TO_TIME(SUM(TIME_TO_SEC(dp.end) - TIME_TO_SEC(dp.start))))) / 100";
List <userEmail> userList = new List <userEmail>();
userEmail u = new userEmail()
{
email = "dsfsaf", userId = 1
};
Func <MySqlDataReader, List <userEmail> > f = (reader) =>
{
List <userEmail> users = new List <userEmail>();
while (reader.Read())
{
users.Add(new userEmail
{
userId = reader.GetInt32(0),
email = reader.GetString(1),
});
}
return(users);
};
userList = DBAccess.RunReader(q, f);
if (userList != null && userList.Count > 0)
{
if (teamLeaderMail == null)
{
q = $"SELECT us.email FROM users us" +
$" join users u on us.user_id=u.manager " +
$"where u.user_id={userList[0].userId};";
teamLeaderMail = (string)DBAccess.RunScalar(q);
}
List <string> emailList = new List <string>();
userList.ForEach(user =>
{
emailList.Add(user.email);
});
emails.Add(new Email()
{
projectName = project.Name,
endDate = project.EndDate,
teamLeaderEmail = teamLeaderMail,
employeesEmail = emailList,
});
}
});
return(emails);
}
