public Task <AttRateAndPercent> GetAttRateAndPercent(int batchID, int studentID) { return(Task.Run(() => { using (MIUEntities db = new MIUEntities()) { AttRateAndPercent res = new AttRateAndPercent(); try { List <int> ids = db.Terms.Where(x => x.BatchID == batchID).Select(x => x.ID).ToList <int>(); IQueryable <TermDetail> termDetailInfo = null; termDetailInfo = db.TermDetails.Where(x => ids.Contains(x.TermID)); var firstSubmissionDates = termDetailInfo.Where(x => x.Term.StartDate < DateTime.Now && x.FirstSubmission >= DateTime.Now); var finalSubmissionDates = termDetailInfo.Where(x => x.FirstResult < DateTime.Now && x.FinalSubmission >= DateTime.Now); //var firstSubmissionDates = tds.Where(x => x.Term.StartDate < DateTime.Now && x.FirstSubmission >= DateTime.Now); //var finalSubmissionDates = tds.Where(x => x.FirstResult < DateTime.Now && x.FinalSubmission >= DateTime.Now); if (firstSubmissionDates != null && firstSubmissionDates.Count() > 0) { var submission = firstSubmissionDates.FirstOrDefault(); res.AttendanceRateTermName = submission.Term.TermName; res.CurrentTermId = submission.Term.ID; res.AttenddanceRatePercent = GetAttendanceRateForDashboard(submission.Term.ID, studentID); } else if (finalSubmissionDates != null && finalSubmissionDates.Count() > 0) { var submission = finalSubmissionDates.FirstOrDefault(); res.AttendanceRateTermName = submission.Term.TermName; res.CurrentTermId = submission.Term.ID; res.AttenddanceRatePercent = GetAttendanceRateForDashboard(submission.Term.ID, studentID); } } catch (Exception) { } return res; } })); }
public Task <AttendanceGrid> GetReportAttendance(int batchID, int studentID) { return(Task.Run(async() => { using (MIUEntities db = new MIUEntities()) { //try //{ if (batchID != 0) { string modulecodelist = ""; AttendanceGrid grid = new AttendanceGrid(); //var terms = db.Terms.AsNoTracking().Where(x => x.BatchID == batchID).Select(x => new { ID = x.ID, TermName = x.TermName, StartDate = x.StartDate, sortOrer = x.sortOrder }).OrderBy(y => y.sortOrer).ToList(); List <Term> terms = db.Terms.AsNoTracking().Where(x => x.BatchID == batchID).OrderBy(y => y.sortOrder).ToList(); AttRateAndPercent attRateAndPercent = new AttRateAndPercent(); attRateAndPercent = await GetAttRateAndPercent(batchID, studentID); grid.AttendanceRateTermName = attRateAndPercent.AttendanceRateTermName; grid.AttendanceRatePercent = attRateAndPercent.AttenddanceRatePercent; List <TermInfo> terminfo = new List <TermInfo>(); foreach (var data in terms) { TermInfo info = new TermInfo(); PropertyCopier <Term, TermInfo> .Copy(data, info); //info.IsFavorite = db.FavoriteLists.Any(a => a.FavoriteID == data.ID && a.UserID == userID && a.FavoriteType == "Announcement"); terminfo.Add(info); } var termlist = db.Terms.AsNoTracking().Where(x => x.BatchID == batchID).Select(x => x.ID).ToList(); var frommonth = DateTime.Now.AddMonths(-24); var tomonth = DateTime.Now.AddMonths(24); var attendances = db.Attendances.AsNoTracking().Where(x => x.Date >= frommonth && x.Date <= tomonth && termlist.Contains(x.TermID)).ToList(); grid.TermDetail = terminfo; //For Module Code List Of Header foreach (var term in terms) { List <TermDetail> tds = db.TermDetails.AsNoTracking().Where(td => td.TermID == term.ID && td.Attendence == true).ToList(); modulecodelist = ""; foreach (var td in tds) { if (modulecodelist != "") { modulecodelist += ","; } modulecodelist += td.Module.ModuleCode; } foreach (TermInfo item in grid.TermDetail) { if (item.ID == term.ID) { item.ModuleCodeList = modulecodelist; } } //grid.ModuleCodeList.Add(modulecodelist); int count = attendances.Where(a => a.TermID == term.ID).GroupBy(a => a.Month).Count(); grid.MonthList.Add(count); } //For Row of Grid List <UserInfo> stdList = new List <UserInfo>(); if (studentID != 0) { stdList = (from a in db.BatchDetails join b in db.Users on a.StudentID equals b.ID where a.BatchID == batchID && b.ID == studentID && b.UserType == 1 && b.IsDelete != true select new UserInfo { ID = b.ID, FullName = b.FullName, LoginName = b.LoginName, ProfilePicture = b.ProfilePicture, ProfilePicture2 = b.ProfilePicture2 }).ToList(); } else { stdList = (from a in db.BatchDetails join b in db.Users on a.StudentID equals b.ID where a.BatchID == batchID && b.UserType == 1 && b.IsDelete != true select new UserInfo { ID = b.ID, FullName = b.FullName, LoginName = b.LoginName, ProfilePicture = b.ProfilePicture, ProfilePicture2 = b.ProfilePicture2 }).ToList(); //stdList = db.BatchDetails.AsNoTracking().Where(x => x.BatchID == batchID).Select(x => x.User).Where(x => x.UserType == 1 && x.IsDelete == null || x.IsDelete != true).ToList(); } //var frommonth = DateTime.Now.AddMonths(-6); //var tomonth = DateTime.Now.AddMonths(6); //var attendances = db.Attendances.Where(x => x.Date >= frommonth && x.Date <= tomonth); //decimal SutdentCountYear1 = 0; //decimal SutdentCountYear2 = 0; //decimal StudentLastYear = 0; //decimal StudentFoundation = 0; if (stdList.Count() > 0) { foreach (UserInfo student in stdList) { //AttendanceGridRow row = new AttendanceGridRow(); //row.StudentName = student.FullName; //row.StudentID = student.LoginName; var attendance = attendances.Where(x => x.StudentID == student.ID); decimal TotalYear1 = 0; decimal TotalYear2 = 0; decimal TotalLastYear = 0; decimal TotalFoundation = 0; int Year2Count = 0; int LastYearCount = 0; int FoundationCount = 0; foreach (var term in terms) { int percentageValue1 = 0; int percentageValue2 = 0; //double attendingCount = 0; double totalCount1 = 0; double totalCount2 = 0; double totalCount = 0; double attendingTotalCount = 0; int daysOfFirstMonth = endOfMonth(term.StartDate.Value.Month, term.StartDate.Value.Year) - term.StartDate.Value.Day; int firstMonth = daysOfFirstMonth > 5 ? term.StartDate.Value.Month : term.StartDate.Value.AddMonths(1).Month; totalCount1 = attendance.Where(x => x.TermID == term.ID && x.Month <= firstMonth).Select(y => y.ID).Count(); totalCount2 = attendance.Where(x => x.TermID == term.ID && x.Month > firstMonth).Select(y => y.ID).Count(); totalCount = attendance.Where(x => x.TermID == term.ID).Select(y => y.ID).Count(); AttendanceCell cell1 = new AttendanceCell(); AttendanceCell cell2 = new AttendanceCell(); AttendanceCell totalcell = new AttendanceCell(); totalCount1 = attendance.Where(x => x.TermID == term.ID && x.Month <= firstMonth).Select(y => y.ID).Count(); if (totalCount1 > 0) { var attendingList1 = attendance.Where(x => x.TermID == term.ID && x.IsAttending == true && x.Month <= firstMonth).Select(y => y.ID).Count(); if (attendingList1 != 0 && attendingList1 > 0) { attendingTotalCount += attendingList1; percentageValue1 = (int)Math.Round(((attendingList1 / totalCount1) * 100)); } } if (totalCount2 > 0) { var attendingList2 = attendance.Where(x => x.TermID == term.ID && x.IsAttending == true && x.Month > firstMonth).Select(y => y.ID).Count(); if (attendingList2 != 0 && attendingList2 > 0) { //attendingCount = count2; attendingTotalCount += attendingList2; percentageValue2 = (int)Math.Round(((attendingList2 / totalCount2) * 100)); //percentageValue2 = (int)(((attendingCount / totalCount2) * 100)); //percentagedouble2 = (count2 / totalCount2) * 100; } } cell1.Value = percentageValue1; cell2.Value = percentageValue2; //totalcell.Value = (int)Math.Round((((double)percentageValue1+(double)percentageValue2)*100)/200); //totalcell.Value = (int)Math.Round((((double)percentagedouble1 + (double)percentagedouble2) * 100) / 200); if (totalCount > 0) { //totalCount = attendance.Where(x => x.TermID == term.ID).Count(); totalcell.Value = (int)Math.Round(((attendingTotalCount / totalCount) * 100)); if (term.TermCode == "Term-2" || term.TermCode == "Term-3" || term.TermCode == "Term-4" || term.TermCode == "Term-5") { TotalYear1 += totalcell.Value; } else if (term.TermCode == "Term-6" || term.TermCode == "Term-7" || term.TermCode == "Term-8" || term.TermCode == "Term-9") { Year2Count++; TotalYear2 += totalcell.Value; } else if (term.TermCode == "Term-10" || term.TermCode == "Term-11" || term.TermCode == "Term-12" || term.TermCode == "Term-13") { LastYearCount++; TotalLastYear += totalcell.Value; } else { FoundationCount++; TotalFoundation += totalcell.Value; } } else { totalcell.Value = 0; if (term.TermCode == "Term-2" || term.TermCode == "Term-3" || term.TermCode == "Term-4" || term.TermCode == "Term-5") { TotalYear1 += totalcell.Value; } else if (term.TermCode == "Term-6" || term.TermCode == "Term-7" || term.TermCode == "Term-8" || term.TermCode == "Term-9") { Year2Count++; TotalYear2 += totalcell.Value; } else if (term.TermCode == "Term-10" || term.TermCode == "Term-11" || term.TermCode == "Term-12" || term.TermCode == "Term-13") { LastYearCount++; TotalLastYear += totalcell.Value; } else { FoundationCount++; TotalFoundation += totalcell.Value; } } //row.cells.Add(cell1); //row.cells.Add(cell2); //row.cells.Add(totalcell); grid.StudentID = student.ID; grid.StudentName = student.FullName; grid.LoginName = student.LoginName; grid.BatchName = term.Batch.BatchName; grid.CourseName = term.Batch.Course.CourseName; grid.ProfilePicture = MIUFileServer.GetFileUrl("ProfileImages", student.ProfilePicture); grid.ProfilePicture2 = MIUFileServer.GetFileUrl("ProfileImages", student.ProfilePicture2); //grid.ProfilePicture2 = student.ProfilePicture2; foreach (TermInfo item in grid.TermDetail) { if (item.ID == term.ID) { //item.StudentID = student.ID; //item.StudentName = student.FullName; item.First = percentageValue1; item.Second = percentageValue2; item.Total = totalcell.Value; } } } //decimal a = 0; //decimal b = 0; //decimal c = 0; //decimal d = 0; //if (TotalYear1 > 0) //{ // a = Math.Round(TotalYear1 / 4); //} //if (TotalYear2 > 0) //{ // b = Math.Round(TotalYear2 / Year2Count); //} //if (TotalFoundation > 0) //{ // c = Math.Round(TotalFoundation / FoundationCount); //} //if (TotalLastYear > 0) //{ // d = Math.Round(TotalLastYear / LastYearCount); //} //if (a >= 70) //{ // SutdentCountYear1++; //} //if (b >= 70) //{ // SutdentCountYear2++; //} //if (c >= 70) //{ // StudentFoundation++; //} //if (d >= 70) //{ // StudentLastYear++; //} //grid.Rows.Add(row); } } int index = 0; foreach (var item in grid.TermDetail) { if (index == 0) { item.YearName = "Foundation"; } else if (index > 0 && index <= 4) { item.YearName = "Year 1"; } else if (index > 4 && index <= 8) { item.YearName = "Year 2"; } else { item.YearName = ""; } index++; } return grid; } else { return null; } //} //catch (Exception) //{ // return null; //} } })); }
public Task <List <StudentTimeTable> > GetStudentTimeTable(int userID, int batchID, string date) { return(Task.Run(() => { DateTime dateTime = DateTime.Now; using (MIUEntities db = new MIUEntities()) { List <LecturerTimeTable> timeTables = new List <LecturerTimeTable>(); List <StudentTimeTable> StudentTimeTables = new List <StudentTimeTable>(); CommonDAL commonDAL = new CommonDAL(); AttRateAndPercent attRateAndPercent = commonDAL.GetAttRateAndPercent(batchID, userID); if (date != "\"\"" && !String.IsNullOrEmpty(date)) { dateTime = Convert.ToDateTime(date); } if (attRateAndPercent.CurrentTermId != 0) { //timeTables = (from p in db.vProgramPlans // join u in db.Users on p.LectureID equals u.ID // join b in db.Batches on p.BatchID equals b.ID // join m in db.Modules on p.ModuleID equals m.ID // join c in db.Courses on b.CourseID equals c.ID // join t in db.TimeTableDetails on p.TermDetailID equals t.TermDetailID // where b.ID == batchID && DbFunctions.TruncateTime(t.Date) == dateTime.Date // select new LecturerTimeTable // { // LecturerID = u.ID, // LoginName = u.LoginName, // FullName = u.FullName, // ProfilePicture = u.ProfilePicture, // ProfilePicture2 = u.ProfilePicture2, // CourseName = c.CourseName, // ModuleCode = m.ModuleCode, // ModuleName = m.ModuleName, // StartTime = t.StartTime, // EndTime = t.EndTime, // TimeTableDate = t.Date // }).ToList(); StudentTimeTables = (from td in db.TimeTableDetails join ts in db.TimeSettings on td.TimeSettingID equals ts.ID join trd in db.TermDetails on td.TermDetailID equals trd.ID join m in db.Modules on trd.ModuleID equals m.ID join u in db.Users on trd.LectureID equals u.ID join t in db.Attendances //.Where(t => t.TermID == attRateAndPercent.CurrentTermId && t.StudentID == userID) on td.ID equals t.TimeTableDetailID where t.StudentID == userID //td.TermID == attRateAndPercent.CurrentTermId //&& //td.TermDetailID == t.TermDetailID //&& t.TermID == attRateAndPercent.CurrentTermId && DbFunctions.TruncateTime(td.Date) == dateTime.Date orderby td.ID select new StudentTimeTable { TimeTableDetailID = td.ID, Section = ts.Section, StartTime = td.StartTime.ToString(), EndTime = td.EndTime.ToString(), TimeTableDate = td.Date, LectureID = trd.LectureID, LectureName = u.FullName, ProfilePicture = u.ProfilePicture, ModuleID = m.ID, ModuleName = m.ModuleName, IsAttending = t.IsAttending, Attendance = t.IsAttending == true ? "Attended" : "Missed" }).ToList(); } //string strnow = DateTime.Now.ToString("hh:mm tt", CultureInfo.InvariantCulture); //DateTime now = DateTime.ParseExact(strnow, "hh:mm tt", CultureInfo.InvariantCulture); foreach (var data in StudentTimeTables) { data.ProfilePicture = MIUFileServer.GetFileUrl("ProfileImages", data.ProfilePicture); if (data.TimeTableDate.Value.Date > DateTime.Now.Date) { data.Attendance = ""; } else if (data.TimeTableDate.Value.Date == DateTime.Now.Date) { TimeSpan start; TimeSpan end; TimeSpan now = DateTime.Now.TimeOfDay; if (data.StartTime.Contains("PM")) { string starttime = data.StartTime.Replace("PM", ""); string[] startStr = starttime.Split(':'); startStr[0] = (int.Parse(startStr[0]) + 12).ToString(); start = new TimeSpan(int.Parse(startStr[0]), int.Parse(startStr[1]), 0); } else { string starttime = data.StartTime.Replace("AM", ""); string[] startStr = starttime.Split(':'); startStr[0] = (int.Parse(startStr[0])).ToString(); start = new TimeSpan(int.Parse(startStr[0]), int.Parse(startStr[1]), 0); } if (data.EndTime.Contains("PM")) { string endtime = data.EndTime.Replace("PM", ""); string[] endStr = endtime.Split(':'); endStr[0] = (int.Parse(endStr[0]) + 12).ToString(); end = new TimeSpan(int.Parse(endStr[0]), int.Parse(endStr[1]), 0); } else { string endtime = data.EndTime.Replace("AM", ""); string[] endStr = endtime.Split(':'); endStr[0] = (int.Parse(endStr[0])).ToString(); end = new TimeSpan(int.Parse(endStr[0]), int.Parse(endStr[1]), 0); } if ((now < start) && (now < end)) { data.Attendance = ""; } } //string strstart = DateTime.Now.ToString("dd-MMM-yy") + " " + data.StartTime.ToString(); //string strend = DateTime.Now.ToString("dd-MMM-yy") + " " + data.EndTime.ToString(); //DateTime end = DateTime.ParseExact(strend, "hh:mm:ss tt", CultureInfo.InvariantCulture); //DateTime start = DateTime.ParseExact(strstart, "hh:mm:ss tt", CultureInfo.InvariantCulture); //if (DbFunctions.TruncateTime(data.TimeTableDate) == DateTime.Now.Date && start >= now && end <= now) //{ // data.Attendance = "Attending"; //} //else if((DbFunctions.TruncateTime(data.TimeTableDate) == DateTime.Now.Date && end > now) || data.TimeTableDate > DateTime.Now) //{ // data.Attendance = ""; //} } return StudentTimeTables; } })); }