//return all team leader
public static List <User> GetAllTeamLeader()
{
string query = $"SELECT * FROM truth_time_ct.users u join truth_time_ct.status_users s on u.idStatus = s.idStatus where s.statusName LIKE '%leader%'";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> Users = new List <User>();
while (reader.Read())
{
int p6 = 0;
int.TryParse(reader[6].ToString(), out p6);
bool p7 = reader.GetBoolean(7);
Users.Add(new User
{
IdUser = (int)reader[0],
UserName = (string)reader[1],
Password = (string)reader[2],
IdStatus = (int)reader[3],
EmailUser = (string)reader[4],
SumHours = (double)reader[5],
IdTeamLeader = p6,
IsActive = p7
});
}
return(Users);
};
return(DBUse.RunReader(query, func));
}
public static bool AddUser(User user)
{
string query = $"INSERT INTO truth_time_ct.users VALUES (0,'{user.UserName}','{user.Password}'" +
$",{user.IdStatus},'{user.EmailUser}',{user.SumHours},{user.IdTeamLeader},{user.IsActive})";
return(DBUse.RunNonQuery(query) == 1);
}
//all users by getting name of project
public static List <User> GetUsersByNameOfProject(string nameProject)
{
string query = $"SELECT DISTINCT * FROM truth_time_ct.users u join truth_time_ct.users_projects up on u.idUser=up.idUser join truth_time_ct.projects p on up.idProject=p.idProject where p.projectName={nameProject}";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> Users = new List <User>();
while (reader.Read())
{
int p6 = 0;
int.TryParse(reader[6].ToString(), out p6);
bool p7 = reader.GetBoolean(7);
Users.Add(new User
{
IdUser = (int)reader[0],
UserName = (string)reader[1],
Password = (string)reader[2],
IdStatus = (int)reader[3],
//StatusUserFK =s,
EmailUser = (string)reader[4],
SumHours = (double)reader[5],
IdTeamLeader = p6,
IsActive = p7
});
}
return(Users);
};
return(DBUse.RunReader(query, func));
}
public static bool AddUserProject(UserProject userProject)
{
string query = $"INSERT INTO truth_time_ct.users_projects VALUES (0,'{userProject.HoursProjectUser}'" +
$",{userProject.IdProject},{userProject.IdUser})";
return(DBUse.RunNonQuery(query) == 1);
}
//return all projects under specipic teamLeader
public static List <Project> GetProjectsUnderTeamLeader(int idTeamLeader)
{
string query = $"SELECT * FROM truth_time_ct.Projects WHERE idTeamLeader={idTeamLeader}";
Func <MySqlDataReader, List <Project> > func = (reader) =>
{
List <Project> Projects = new List <Project>();
while (reader.Read())
{
Projects.Add(new Project
{
IdProject = (int)reader[0],
ProjectName = (string)reader[1],
ClientName = (string)reader[2],
IdTeamLeader = (int)reader[3],
StartDate = (DateTime)reader[4],
EndDate = (DateTime)reader[5],
HoursForDevelopers = (double)reader[6],
HoursForQA = (double)reader[7],
HoursForUI_UX = (double)reader[8],
Active = reader.GetBoolean(9)
});
}
return(Projects);
};
return(DBUse.RunReader(query, func));
}
//return how many hours the users worked on spesipic project
public static int GetHoursThatWorkedOfProject(int idProject)
{
//get the hours by dateDiff between start and end date
string query = $"SELECT sum(SELECT DATEDIFF(hour, startDatePresence, endDatePresence) FROM truth_time_ct.daily_presence WHERE idProject={idProject}) from truth_time_ct.daily_presence";
return(int.Parse(DBUse.RunScalar(query).ToString()));
}
//get user by idUser
public static User GetUserByIdUser(int idUser)
{
string query = $"SELECT * FROM truth_time_ct.users WHERE idUser={idUser}";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
int p6 = 0;
int.TryParse(reader[6].ToString(), out p6);
bool p7 = reader.GetBoolean(7);
users.Add(new User
{
IdUser = (int)reader[0],
UserName = (string)reader[1],
Password = (string)reader[2],
IdStatus = (int)reader[3],
EmailUser = (string)reader[4],
SumHours = (double)reader[5],
IdTeamLeader = p6,
IsActive = p7
});
}
return(users);
};
User myUser = DBUse.RunReader(query, func)[0];
return(myUser);
}
//return all names of users and userprojects under teamleader
public static List <UserProjectHelp> GetAllUserProjectUnderTeamLeaderWithNames(int idTeamLeader)
{
string query = $"SELECT up.* FROM truth_time_ct.users_projects up join truth_time_ct.projects p on up.idProject=p.idProject and p.idTeamLeader={idTeamLeader} where p.active=true;";
List <User> allUsersUnderTeamLeader = LogicUsers.GetAllUsersUnderTeamLeader(idTeamLeader);
List <Project> allProjectUnderTeamLeader = LogicProjects.GetProjectsUnderTeamLeader(idTeamLeader);
Func <MySqlDataReader, List <UserProjectHelp> > func = (reader) =>
{
List <UserProjectHelp> users_projects_help = new List <UserProjectHelp>();
while (reader.Read())
{
users_projects_help.Add(new UserProjectHelp
{
IdUserProject = (int)reader[0],
HoursProjectUser = (int)reader[1],
IdProject = (int)reader[2],
IdUser = (int)reader[3],
NameProject = allProjectUnderTeamLeader.FirstOrDefault(p => p.IdProject == (int)reader[2]).ProjectName,
NameUser = allUsersUnderTeamLeader.FirstOrDefault(p => p.IdUser == (int)reader[3]).UserName
});
}
return(users_projects_help);
};
return(DBUse.RunReader(query, func));
}
//return all deatails of specipic user project//change
public static List <UserProjectHelp> AllDetailsUserProjectOfSpecipicUser(int idUser)
{
string query = $"" +
$"SELECT p.projectName,p.startDate,p.endDate ,up.hoursProjectUser,u.userName FROM truth_time_ct.Projects p join truth_time_ct.users_projects up on p.idProject = up.idProject" +
$" join truth_time_ct.users u on p.idTeamLeader = u.idUser" +
$" WHERE up.idUser = {idUser}";
Func <MySqlDataReader, List <UserProjectHelp> > func = (reader) =>
{
List <UserProjectHelp> userProjectHelp = new List <UserProjectHelp>();
while (reader.Read())
{
userProjectHelp.Add(new UserProjectHelp
{
NameProject = (string)reader[0],
StartDate = (DateTime)reader[1],
EndDate = (DateTime)reader[2],
HoursProjectUser = (int)reader[3],
NameTeamLeader = (string)reader[4]
});
}
return(userProjectHelp);
};
return(DBUse.RunReader(query, func));
}
//sum hours that user worked on spesipic project on specipic month
public static int SumHoursUserWorkedProjectOnSpecipicMonth(int idProject, int idUser, int?month)
{
string myMonth = DateTime.Now.Month.ToString();
if (month != null)//or hasValue
{
myMonth = month.ToString();
}
string query = $"SELECT sum(SELECT DATEDIFF(hour, startDatePresence, endDatePresence) FROM truth_time_ct.daily_presence WHERE idProject={idProject} and idUser={idUser} and MONTH(startDatePresence)=myMonth)";
return(int.Parse(DBUse.RunScalar(query).ToString()));
}
//get dictionary of days and the hours that users worked in this day on any project
public static Dictionary <string, double> GetHoursWorkedOnProjectByDays(int idProject, int month)
{
//select days and for every day the sum of hours that the users worked on specipic project on specipic month
string query = $"SELECT DAY(d.startDatePresence),sum(SELECT DATEDIFF(hour,startDatePresence,endDatePresence) FROM truth_time_ct.daily_presence d WHERE idProject={idProject} and month(startDatePresence)={month} group by(DAY(startDatePresence))) ";
Func <MySqlDataReader, Dictionary <string, double> > func = (reader) =>
{
Dictionary <string, double> DailySupply = new Dictionary <string, double>();
while (reader.Read())
{
DailySupply.Add(
(string)reader[0],
(double)reader[1]);
}
return(DailySupply);
};
return(DBUse.RunReaderDictionary(query, func));
}
public static UserProject GetSpesipicUserProject(int idUser, int idProject)
{
string query = $"SELECT * FROM truth_time_ct.users_projects where idUser={idUser} and idProject={idProject}";
Func <MySqlDataReader, List <UserProject> > func = (reader) =>
{
List <UserProject> UserProject = new List <UserProject>();
UserProject.Add(new UserProject
{
IdUserProject = (int)reader[0],
HoursProjectUser = (int)reader[1],
IdProject = (int)reader[2],
IdUser = (int)reader[3]
});
return(UserProject);
};
return(DBUse.RunReader(query, func)[0]);
}
//return all project under user()
public static List <Project> AllProjectOfSpecipicUser(int idUser)
{
string query = $"SELECT * FROM truth_time_ct.Projects p join truth_time_ct.users_projects up on p.idProject=up.idProject WHERE up.idUser={idUser}";
Func <MySqlDataReader, List <Project> > func = (reader) =>
{
List <Project> Projects = new List <Project>();
while (reader.Read())
{
Projects.Add(new Project
{
IdProject = (int)reader[0],
ProjectName = (string)reader[1],
IdTeamLeader = (int)reader[2],
});
}
return(Projects);
};
return(DBUse.RunReader(query, func));
}
//why not * up.idUserProject,up.hoursProjectUser,up.idUser,up.idProject
//return dictionary of userProject and the hours that the user worked to this userProject;
public static Dictionary <UserProject, double> GetDictionaryOfHoursThatUserWorkedOnProjectInPrecent(int idUser)
{
//for user select all data of userProjects of him with the time that he worked for that project;
string query = $"SELECT *,sum(SELECT DATEDIFF(hour, startDatePresence, endDatePresence) FROM truth_time_ct.daily_presence WHERE idProject=up.idProject and idUser={idUser}) from truth_time_ct.users_projects up WHERE up.idUser={idUser} Group By up.idUserProject,up.hoursProjectUser,up.idUser,up.idProject";
Func <MySqlDataReader, Dictionary <UserProject, double> > func = (reader) =>
{
Dictionary <UserProject, double> myDictionary = new Dictionary <UserProject, double>();
while (reader.Read())
{
myDictionary.Add(new UserProject
{
IdUserProject = (int)reader[0],
HoursProjectUser = (int)reader[1],
IdUser = (int)reader[2],
IdProject = (int)reader[3]
}, (int)reader[4]);
}
return(myDictionary);
};
return(DBUse.RunReaderDictionary(query, func));
}
//get userProject ById
public static UserProject GetUserProjectById(int idUserProject)
{
string query = $"SELECT * FROM truth_time_ct.users_projects WHERE idUserProject={idUserProject}";
Func <MySqlDataReader, List <UserProject> > func = (reader) =>
{
List <UserProject> UserProjects = new List <UserProject>();
while (reader.Read())
{
UserProjects.Add(new UserProject
{
IdUserProject = (int)reader[0],
HoursProjectUser = (int)reader[1],
IdProject = (int)reader[2],
IdUser = (int)reader[3]
});
}
return(UserProjects);
};
UserProject userProject = DBUse.RunReader(query, func)[0];
return(userProject);
}
//return x days and y hours that user worked on specipic project
public static List <HoursOfUserProjectByDays> GetDaysAndHoursUserWorkedOnProject(int idProject, int idUser)
{
string query = $"SELECT d.Day(startDatePresence),sum(SELECT DATEDIFF(hour, startDatePresence, endDatePresence)" +
$" FROM truth_time_ct.daily_presence WHERE idProject={idProject} and idUser={idUser} and DAY(startDatePresence)=d.Day(startDatePresence)) " +
$"from truth_time_ct.daily_presence d";
Func <MySqlDataReader, List <HoursOfUserProjectByDays> > func = (reader) =>
{
List <HoursOfUserProjectByDays> hoursOfUserProjectByDays = new List <HoursOfUserProjectByDays>();
while (reader.Read())
{
hoursOfUserProjectByDays.Add(new HoursOfUserProjectByDays
{
Day = (string)reader[0],
hours = (int)reader[1]
});
}
return(hoursOfUserProjectByDays);
};
return(DBUse.RunReader(query, func));
}
public static List <UserProject> GetAllUserProject()
{
string query = $"SELECT * FROM truth_time_ct.users_projects";
Func <MySqlDataReader, List <UserProject> > func = (reader) =>
{
List <UserProject> users_projects = new List <UserProject>();
while (reader.Read())
{
users_projects.Add(new UserProject
{
IdUserProject = (int)reader[0],
HoursProjectUser = (int)reader[1],
IdProject = (int)reader[2],
IdUser = (int)reader[3]
});
}
return(users_projects);
};
return(DBUse.RunReader(query, func));
}
public static List <User> GetAllUsers()
{
string query = $"SELECT * FROM truth_time_ct.users";
Func <MySqlDataReader, List <User> > func = (reader) =>
{
List <User> users = new List <User>();
while (reader.Read())
{
int p = (int)reader[0];
string p1 = (string)reader[1];
string p2 = (string)reader[2];
int p3 = (int)reader[3];
string p4 = (string)reader[4];
double p5 = (double)reader[5];
int p6 = 0;
int.TryParse(reader[6].ToString(), out p6);
bool p7 = reader.GetBoolean(7);
//StatusUser s = new StatusUser() { IdStatus = p3, StatusName = LogicStatusUsers.GetStatusName(p3) };
users.Add(new User
{
IdUser = p,
UserName = p1,
Password = p2,
IdStatus = p3,
EmailUser = p4,
SumHours = p5,
IdTeamLeader = p6,
IsActive = p7
});
}
return(users);
};
return(DBUse.RunReader(query, func));
}
//return the hours that users worked on that project;
public static int GetHoursThatWorkedOnProject(int idProject)
{
string query = $"SELECT sum(SELECT DATEDIFF(hour, startDatePresence, endDatePresence) FROM truth_time_ct.daily_presence WHERE idProject={idProject})";
return(int.Parse(DBUse.RunScalar(query).ToString()));
}
//return hours that defined for project
public static int GetHoursOfProject(int idProject)
{
string query = $"SELECT hoursForProject FROM truth_time_ct.projects WHERE idProject={idProject}";
return(int.Parse(DBUse.RunScalar(query).ToString()));
}
