/// <summary> /// This function takes FiltersModel and an empty list of AttDeptSummary as input and /// gives a populated AttDeptSummary as output. If there is any dept selected in the filter /// it will iterate through all the departments if there is no dept selected it will try to /// find all the depts of the company. /// </summary> /// <param name="fm"> FiltersModel </param> /// <param name="attDeptList">Empty List of AttDeptSummary</param> /// <returns>Populated List of AttDeptSummary</returns> public List<AttDeptSummary> GetListForAttDepartmentsSummary(FiltersModel fm, string _dateFrom,string _dateTo) { List<AttDeptSummary> attDeptList = new List<AttDeptSummary>(); TAS2013Entities db = new TAS2013Entities(); //To get the query from the db QueryBuilder qb = new QueryBuilder(); //Get the Department filters List<FiltersAttributes> departments = new List<FiltersAttributes>(); departments = fm.DepartmentFilter; //if more then 0 it means some departments are selected now we iterate over them and see //how many employee does one dept has. The table empView is the most suitable db for this case. if (departments.Count > 0) { //do nothing still cater for it in future there might be a need } //if there is no department list in the filter then first get the company's department and then do the same as above. else { foreach (var comp in fm.CompanyFilter) { int compID = Convert.ToInt16(comp.ID); List<Department> depts = db.Departments.Where(aa => aa.CompanyID == compID).ToList(); foreach (var dept in depts) departments.Add(new FiltersAttributes() { ID=dept.DeptID + "", FilterName=dept.DeptName }); } } //NOw run the departments if they are from the department filter or from the company itself foreach (var dept in departments) { DataTable dt = qb.GetValuesfromDB("select * from EmpView where DeptID=" + dept.ID); List<EmpView> EmView = dt.ToList<EmpView>(); //2015-01-24 foreach (DateTime day in EachDay(Convert.ToDateTime(_dateFrom), Convert.ToDateTime(_dateTo))) { AttDeptSummary singleInstance = new AttDeptSummary(); singleInstance.Department = dept.FilterName; singleInstance.TotalStrength = EmView.Count(); singleInstance.Total = singleInstance.TotalStrength; singleInstance.Location = EmView.FirstOrDefault().LocName; singleInstance.Section = EmView.FirstOrDefault().SectionName; singleInstance.Company = EmView.FirstOrDefault().CompName; singleInstance.CardSwapped = 0; singleInstance.Absent = 0; singleInstance.OnLeave = 0; string dynamicQueryForEmps = GetDynamicQueryForEmps(EmView); string queryForCardSwapped = "select * from AttData where (TimeIn is not null AND AttDate = '" + day + "') AND "; string queryForAbsent = "select * from AttData where (StatusAB = 1 AND AttDate = '" + day + "') AND "; string queryOnLeave = "select * from AttData where (StatusHL = 1 OR StatusLeave =1 AND StatusSL=1 AND AttDate = '" + day + "') AND "; queryForCardSwapped = queryForCardSwapped + dynamicQueryForEmps; singleInstance.CardSwapped = qb.GetValuesfromDB(queryForCardSwapped).ToList<AttData>().Count(); queryForAbsent = queryForAbsent + dynamicQueryForEmps; singleInstance.Absent = qb.GetValuesfromDB(queryForAbsent).ToList<AttData>().Count(); queryOnLeave = queryOnLeave + dynamicQueryForEmps; singleInstance.OnLeave = qb.GetValuesfromDB(queryOnLeave).ToList<AttData>().Count(); //foreach (var emp in EmView) //{ //s= singleInstance.CardSwapped + db.AttDatas.Where(aa => aa.TimeIn != null && aa.AttDate == day && aa.EmpID == emp.EmpID).Count(); //singleInstance.Absent = singleInstance.Absent+ db.AttDatas.Where(aa => aa.StatusAB == true && aa.AttDate == day && aa.EmpID == emp.EmpID).Count(); //singleInstance.OnLeave = singleInstance.OnLeave + db.AttDatas.Where(aa => (aa.StatusHL == true || aa.StatusLeave == true || aa.StatusSL == true) && aa.AttDate == day && aa.EmpID == emp.EmpID).Count(); //} singleInstance.date = day; attDeptList.Add(singleInstance); } } return attDeptList; }
/// <summary> /// This function takes FiltersModel and an empty list of AttDeptSummary as input and /// gives a populated AttDeptSummary as output. If there is any dept selected in the filter /// it will iterate through all the departments if there is no dept selected it will try to /// find all the depts of the company. /// </summary> /// <param name="fm"> FiltersModel </param> /// <param name="attDeptList">Empty List of AttDeptSummary</param> /// <returns>Populated List of AttDeptSummary</returns> public List <AttDeptSummary> GetListForAttDepartmentsSummary(FiltersModel fm, string _dateFrom, string _dateTo) { List <AttDeptSummary> attDeptList = new List <AttDeptSummary>(); TAS2013Entities db = new TAS2013Entities(); //To get the query from the db QueryBuilder qb = new QueryBuilder(); //Get the Department filters List <FiltersAttributes> departments = new List <FiltersAttributes>(); departments = fm.DepartmentFilter; //if more then 0 it means some departments are selected now we iterate over them and see //how many employee does one dept has. The table empView is the most suitable db for this case. if (departments.Count > 0) { //do nothing still cater for it in future there might be a need } //if there is no department list in the filter then first get the company's department and then do the same as above. else { foreach (var comp in fm.CompanyFilter) { int compID = Convert.ToInt16(comp.ID); List <Department> depts = db.Departments.Where(aa => aa.CompanyID == compID).ToList(); foreach (var dept in depts) { departments.Add(new FiltersAttributes() { ID = dept.DeptID + "", FilterName = dept.DeptName }); } } } //NOw run the departments if they are from the department filter or from the company itself foreach (var dept in departments) { DataTable dt = qb.GetValuesfromDB("select * from EmpView where DeptID=" + dept.ID); List <EmpView> EmView = dt.ToList <EmpView>(); //2015-01-24 foreach (DateTime day in EachDay(Convert.ToDateTime(_dateFrom), Convert.ToDateTime(_dateTo))) { AttDeptSummary singleInstance = new AttDeptSummary(); singleInstance.Department = dept.FilterName; singleInstance.TotalStrength = EmView.Count(); singleInstance.Total = singleInstance.TotalStrength; singleInstance.Location = EmView.FirstOrDefault().LocName; singleInstance.Section = EmView.FirstOrDefault().SectionName; singleInstance.Company = EmView.FirstOrDefault().CompName; singleInstance.CardSwapped = 0; singleInstance.Absent = 0; singleInstance.OnLeave = 0; string dynamicQueryForEmps = GetDynamicQueryForEmps(EmView); string queryForCardSwapped = "select * from AttData where (TimeIn is not null AND AttDate = '" + day + "') AND "; string queryForAbsent = "select * from AttData where (StatusAB = 1 AND AttDate = '" + day + "') AND "; string queryOnLeave = "select * from AttData where (StatusHL = 1 OR StatusLeave =1 AND StatusSL=1 AND AttDate = '" + day + "') AND "; queryForCardSwapped = queryForCardSwapped + dynamicQueryForEmps; singleInstance.CardSwapped = qb.GetValuesfromDB(queryForCardSwapped).ToList <AttData>().Count(); queryForAbsent = queryForAbsent + dynamicQueryForEmps; singleInstance.Absent = qb.GetValuesfromDB(queryForAbsent).ToList <AttData>().Count(); queryOnLeave = queryOnLeave + dynamicQueryForEmps; singleInstance.OnLeave = qb.GetValuesfromDB(queryOnLeave).ToList <AttData>().Count(); //foreach (var emp in EmView) //{ //s= singleInstance.CardSwapped + db.AttDatas.Where(aa => aa.TimeIn != null && aa.AttDate == day && aa.EmpID == emp.EmpID).Count(); //singleInstance.Absent = singleInstance.Absent+ db.AttDatas.Where(aa => aa.StatusAB == true && aa.AttDate == day && aa.EmpID == emp.EmpID).Count(); //singleInstance.OnLeave = singleInstance.OnLeave + db.AttDatas.Where(aa => (aa.StatusHL == true || aa.StatusLeave == true || aa.StatusSL == true) && aa.AttDate == day && aa.EmpID == emp.EmpID).Count(); //} singleInstance.date = day; attDeptList.Add(singleInstance); } } return(attDeptList); }