public List <TeamSite> GetTeamSites([FromQuery] int id, [FromQuery] string startdate, [FromQuery] string enddate) { string query = "SELECT SessionSite FROM SessionEmployeeTable WHERE EmployeeId=@EmployeeId AND SessionDate=@Date;"; Team team = GetByIdStatic(id); List <string> dates = new List <string>(); List <TeamSite> teamsites = new List <TeamSite>(); for (DateTime dt = DateTime.Parse(startdate); dt <= DateTime.Parse(enddate); dt = dt.AddDays(1)) { dates.Add(dt.ToString("yyyy-MM-dd")); } using (SqlConnection conn = new SqlConnection(Connection.ConnString)) { try { conn.Open(); foreach (string date in dates) { TeamSite teamsite = new TeamSite { Date = date, Day = DateTime.Parse(date).DayOfWeek.ToString(), Members = new List <SessionEmployee>() }; foreach (TeamMember member in team.Members) { SessionEmployee employee = new SessionEmployee(); employee.EmployeeName = member.EmployeeName; List <string> sites = new List <string>(); sites = conn.Query <string>(query, new { date, member.EmployeeId }).ToList(); if (sites.Count < 1) { employee.SessionSite = AbsenceController.GetEmployeeStatus(member.EmployeeId, date); } else if (sites.Count > 0) { employee.SessionSite = string.Join(", ", sites); } teamsite.Members.Add(employee); } teamsites.Add(teamsite); } } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex); } } return(teamsites); }
public List <Employee> GetEmployees([FromQuery] string date) { string query = "SELECT * FROM EmployeeTable;"; using (SqlConnection conn = new SqlConnection(Connection.ConnString)) { try { conn.Open(); List <Employee> employees = conn.Query <Employee>(query).ToList(); if (employees.Count < 1) { employees.Add(new Employee { Id = 1001, Name = "Manager 1", Role = "Manager", ContractHours = 37.5, WorkPattern = "Mon,Tue,Wed,Thu,Fri" }); employees.Add(new Employee { Id = 1002, Name = "Manager 2", Role = "Manager", ContractHours = 30.0, WorkPattern = "Fri,Sat,Sun,Mon" }); employees.Add(new Employee { Id = 1003, Name = "Supervisor 1", Role = "Supervisor", ContractHours = 37.5, WorkPattern = "Mon,Tue,Wed,Thu,Fri" }); employees.Add(new Employee { Id = 1004, Name = "Supervisor 2", Role = "Supervisor", ContractHours = 37.5, WorkPattern = "Mon,Tue,Wed,Thu,Fri" }); employees.Add(new Employee { Id = 1005, Name = "Supervisor 3", Role = "Supervisor", ContractHours = 30.0, WorkPattern = "Fri,Sat,Sun,Mon" }); employees.Add(new Employee { Id = 1006, Name = "Supervisor 4", Role = "Supervisor", ContractHours = 30.0, WorkPattern = "Fri,Sat,Sun,Mon" }); employees.Add(new Employee { Id = 1007, Name = "Agent 1", Role = "Agent", ContractHours = 37.5, WorkPattern = "Mon,Tue,Wed,Thu,Fri" }); employees.Add(new Employee { Id = 1008, Name = "Agent 2", Role = "Agent", ContractHours = 37.5, WorkPattern = "Mon,Tue,Wed,Thu,Fri" }); employees.Add(new Employee { Id = 1009, Name = "Agent 3", Role = "Agent", ContractHours = 37.5, WorkPattern = "Mon,Tue,Wed,Thu,Fri" }); employees.Add(new Employee { Id = 1010, Name = "Agent 4", Role = "Agent", ContractHours = 37.5, WorkPattern = "Mon,Tue,Wed,Thu,Fri" }); employees.Add(new Employee { Id = 1011, Name = "Agent 5", Role = "Agent", ContractHours = 30.0, WorkPattern = "Fri,Sat,Sun,Mon" }); employees.Add(new Employee { Id = 1012, Name = "Agent 6", Role = "Agent", ContractHours = 30.0, WorkPattern = "Fri,Sat,Sun,Mon" }); employees.Add(new Employee { Id = 1013, Name = "Agent 7", Role = "Agent", ContractHours = 30.0, WorkPattern = "Fri,Sat,Sun,Mon" }); employees.Add(new Employee { Id = 1014, Name = "Agent 8", Role = "Agent", ContractHours = 30.0, WorkPattern = "Fri,Sat,Sun,Mon" }); string queryInitialise = "INSERT INTO EmployeeTable (Id, Name, Role, ContractHours, WorkPattern) " + "VALUES (@Id, @Name, @Role, @ContractHours, @WorkPattern);"; conn.Execute(queryInitialise, employees); } GetStatuses(employees, AbsenceController.GetAbsencesStatic(date)); return(employees); } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex); return(new List <Employee>()); } } }