public ActionResult LDHome() { List <DSRCManagementSystem.Models.LDHomeModel> AsgnList = new List <DSRCManagementSystem.Models.LDHomeModel>(); try { DSRCManagementSystemEntities1 db = new DSRCManagementSystemEntities1(); DSRCManagementSystem.Models.LDHomeModel ObjAC = new DSRCManagementSystem.Models.LDHomeModel(); int userId = Convert.ToInt32(Session["UserID"].ToString()); var roleID = from c in db.UserRoles where c.UserID == userId select(int) c.RoleID; int Id = roleID.FirstOrDefault(); Session["LDMenu"] = DSRCLogic.StoredProcedures.GetUserMenuForLD(userId, Id); AsgnList = (from a in db.Trainings join t in db.Master_TrainingType on a.TrainingTypeId equals t.TrainingTypeId join o in db.Master_TrainingTechnology on a.TechnologyId equals o.TechnologyId join i in db.Users on a.InstructorId equals i.UserID // join i in db.TrainingInstructors on a.InstructorId equals i.InstructorId select new DSRCManagementSystem.Models.LDHomeModel() { TrainingId = a.TrainingId, TrainingName = t.TypeName, TechnologyName = o.TechnologyName, ScheduledDate = a.ScheduledDate, Instructor = ((i.FirstName.Length > 0 ? i.FirstName : "") + " " + (i.LastName.Length > 0 ? i.LastName : "")) }).ToList(); } catch (Exception Ex) { string actionName = this.ControllerContext.RouteData.Values["action"].ToString(); string controllerName = this.ControllerContext.RouteData.Values["controller"].ToString(); ExceptionHandlingController.ExceptionDetails(Ex, actionName, controllerName); } return(View(AsgnList.ToList())); }
public ActionResult Mylearning() { DSRCManagementSystem.Models.LDHomeModel AsgnList = new DSRCManagementSystem.Models.LDHomeModel(); try { DSRCManagementSystemEntities1 db = new DSRCManagementSystemEntities1(); DateTime todays = DateTime.Today; int userId = Convert.ToInt32(Session["UserID"].ToString()); var roleID = from c in db.UserRoles where c.UserID == userId select(int) c.RoleID; //int? roleid = db.UserRoles.Where(x => x.UserID == userId).Select(o => o.RoleID).FirstOrDefault(); //int? instructorid = db.Trainings.Where(x => x.InstructorId == userId).Select(o => o.InstructorId).FirstOrDefault(); //int Id = roleID.FirstOrDefault(); List <int?> obj = new List <int?>(); int BId = GetBranch(userId); obj = db.TrainingNominations.Where(x => x.UserId == userId).Select(o => o.TrainingId).ToList(); int j = obj.Count(); int? Training = db.TrainingNominations.Where(x => x.UserId == userId).Select(o => o.TrainingId).FirstOrDefault(); List <int?> list = new List <int?>(); list = db.TrainingNominations.Where(x => x.UserId == userId && x.IsActive == true).Select(o => o.TrainingId).ToList(); var today = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day); //AsgnList.upcomingTrainings var tempQuery = (from training in db.Trainings.Where(x => !list.Contains(x.TrainingId) && x.IsActive == true) join trainingType in db.Master_TrainingLevel on training.TrainingTypeId equals trainingType.LevelId join tech in db.Master_TrainingTechnology on training.TechnologyId equals tech.TechnologyId join user in db.Users on training.InstructorId equals user.UserID where training.ScheduledDate > today && user.BranchId == BId select new { training, trainingType, tech, user }).OrderBy(o => o.training.ScheduledDate.Value.Year).ThenBy(o => o.training.ScheduledDate.Value.Month).ThenBy(o => o.training.ScheduledDate.Value.Day); AsgnList.upcomingTrainings = new List <Models.UpcomingTraningModel>(); foreach (var item in tempQuery) { var schdDate = item.training.ScheduledDate ?? DateTime.Now.Date; var strdStartTime = item.training.StartTime.ToUpper(); var splitter = new[] { "AM", "PM" }; var tempStartTime = strdStartTime.Split(splitter, StringSplitOptions.RemoveEmptyEntries); var startTime = DateTime.Now; if (DateTime.TryParseExact(tempStartTime[0].Trim(), "h:mm", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out startTime) && strdStartTime.Contains("pm")) { if (startTime.Hour != 12) { startTime = startTime.AddHours(12); } } startTime = schdDate.AddHours(startTime.Hour).AddMinutes(startTime.Minute); if (startTime > DateTime.Now) { var training = new DSRCManagementSystem.Models.UpcomingTraningModel(); training.TrainingId = item.training.TrainingId; training.TrainingName = item.training.TrainingName; training.ScheduledDate = item.training.ScheduledDate; training.TechnologyName = item.tech.TechnologyName; // training.Instructor = ((item.user.FirstName.Length > 0 ? item.user.FirstName : "") + " " + (item.user.LastName.Length > 0 ? item.user.LastName : "")); training.Instructor = item.user.FirstName + " " + (item.user.LastName ?? ""); training.Seatingcapacity = item.training.SeatingCapacity; training.StartTime = item.training.StartTime; training.EndTime = item.training.EndTime; training.instructorid = item.training.InstructorId; training.Nominations = item.training.NumberOfNominated; training.submit = db.TrainingFeedBackCalcs.Where(x => x.TrainingId == training.TrainingId && x.Flag == true).Count(); training.pending = db.TrainingFeedBackCalcs.Where(x => x.TrainingId == training.TrainingId && x.Flag == false).Count(); AsgnList.upcomingTrainings.Add(training); } // if (item.training.NumberOfNominated == item.training.SeatingCapacity) // { // var training = new DSRCManagementSystem.Models.UpcomingTraningModel(); // training.IsSeatingCapacity = true; // } // else // { // var training = new DSRCManagementSystem.Models.UpcomingTraningModel(); // training.IsSeatingCapacity = false; // } } AsgnList.historyTrainings = (from a in db.Trainings join t in db.Master_TrainingType on a.TrainingTypeId equals t.TrainingTypeId join o in db.Master_TrainingTechnology on a.TechnologyId equals o.TechnologyId join i in db.Users on a.InstructorId equals i.UserID join tn in db.TrainingNominations on a.TrainingId equals tn.TrainingId //join tf in db.TrainingFeedBackCalcs on a.TrainingId equals tf.TrainingId where tn.UserId == userId && tn.Score > 0 //where tn.UserId == userId && a.ScheduledDate <= today && tn.Score > 0 && i.BranchId == BId select new DSRCManagementSystem.Models.HistorytrainingModel() { TrainingId = a.TrainingId, TrainingName = a.TrainingName, TechnologyName = o.TechnologyName, ScheduledDate = a.ScheduledDate, //flag=tf.Flag, Instructor = ((i.FirstName.Length > 0 ? i.FirstName : "") + " " + (i.LastName.Length > 0 ? i.LastName : "")) }).OrderByDescending(o => o.ScheduledDate.Value.Year).ThenByDescending(o => o.ScheduledDate.Value.Month).ThenByDescending(o => o.ScheduledDate.Value.Day).ToList(); foreach (var attneded in AsgnList.historyTrainings) { // var val = db.TrainingFeedBackCalcs.Where(o => o.UserId == userId && o.TrainingId == item.TrainingId && o.Flag == false).Select(o => o).ToList(); var FeedbackFlag = db.TrainingFeedBackCalcs.Where(o => o.UserId == userId && o.TrainingId == attneded.TrainingId).Select(o => o).FirstOrDefault(); //var val = db.TrainingFeedBackCalcs.Where(o => o.UserId == userId && o.TrainingId == item.TrainingId && o.Flag == true).Select(o => o).ToList(); //int FeedbackCount = val.Count(); //attneded.FeedbackCount = FeedbackCount; attneded.flag = FeedbackFlag.ChoiceTopic == 0 ? false : true; } AsgnList.unattendedTrainings = (from a in db.Trainings join t in db.Master_TrainingType on a.TrainingTypeId equals t.TrainingTypeId join o in db.Master_TrainingTechnology on a.TechnologyId equals o.TechnologyId join i in db.Users on a.InstructorId equals i.UserID join tn in db.TrainingNominations on a.TrainingId equals tn.TrainingId where tn.UserId == userId && a.ScheduledDate <= today && tn.Score < 0 select new DSRCManagementSystem.Models.HistorytrainingModel() { TrainingId = a.TrainingId, TrainingName = a.TrainingName, TechnologyName = o.TechnologyName, ScheduledDate = a.ScheduledDate, Instructor = ((i.FirstName.Length > 0 ? i.FirstName : "") + " " + (i.LastName.Length > 0 ? i.LastName : "")) }).OrderByDescending(o => o.ScheduledDate.Value.Year).ThenByDescending(o => o.ScheduledDate.Value.Month).ThenByDescending(o => o.ScheduledDate.Value.Day).ToList(); var tempTimeQuery = (from a in db.TrainingNominations.Where(x => x.IsActive == true) join t in db.Trainings on a.TrainingId equals t.TrainingId join o in db.Master_TrainingTechnology on t.TechnologyId equals o.TechnologyId join u in db.Users on t.InstructorId equals u.UserID where a.UserId == userId && a.NominationFlag == true && u.BranchId == BId select new { a, t, o, u }).OrderBy(o => o.t.ScheduledDate.Value.Year).ThenBy(o => o.t.ScheduledDate.Value.Month).ThenBy(o => o.t.ScheduledDate.Value.Day).ToList(); AsgnList.nominatedTrainings = new List <Models.NominatedTrainingModel>(); foreach (var item in tempTimeQuery) { var schdDate = item.t.ScheduledDate ?? DateTime.Now.Date; var strdStartTime = item.t.StartTime.ToUpper(); var splitter = new[] { "AM", "PM" }; var tempStartTime = strdStartTime.Split(splitter, StringSplitOptions.RemoveEmptyEntries); DateTime startTime = DateTime.Now; if (DateTime.TryParseExact(tempStartTime[0].Trim(), "h:mm", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out startTime) && strdStartTime.Contains("PM")) { if (startTime.Hour != 12) { startTime = startTime.AddHours(12); } } startTime = schdDate.AddHours(startTime.Hour).AddMinutes(startTime.Minute); if (startTime >= DateTime.Now) { var training = new DSRCManagementSystem.Models.NominatedTrainingModel(); training.NominationId = item.a.NominationId; training.TrainingId = item.a.TrainingId; training.TrainingName = item.t.TrainingName; training.TechnologyName = item.o.TechnologyName; training.ScheduledDate = item.t.ScheduledDate; training.Instructor = ((item.u.FirstName.Length > 0 ? item.u.FirstName : "") + " " + (item.u.LastName.Length > 0 ? item.u.LastName : "")); training.Starttime = item.t.StartTime; training.IsCompleted = item.a.CompletionFlag; if (training.ScheduledDate <= today) { training.test = true; } else { training.test = false; } AsgnList.nominatedTrainings.Add(training); } } AsgnList.conductedtrainings = (from rc in db.Trainings join l in db.Master_TrainingLevel on rc.LevelId equals l.LevelId join t in db.Master_TrainingTechnology on rc.TechnologyId equals t.TechnologyId join i in db.Users on rc.InstructorId equals i.UserID join n in db.TrainingNominations on rc.TrainingId equals n.TrainingId where rc.IsActive == true && rc.InstructorId == userId && (rc.StatusId == 3 || rc.StatusId == 4 || rc.StatusId == 6 || rc.StatusId == 7 || rc.StatusId == 10) && EntityFunctions.TruncateTime(rc.ScheduledDate) <= DateTime.Today.Date && i.BranchId == BId //where rc.IsActive == true && rc.InstructorId == userId && EntityFunctions.TruncateTime(rc.ScheduledDate) <= DateTime.Today.Date && i.BranchId == BId && n.CompletionFlag == false select new DSRCManagementSystem.Models.Conductedtrainingmodel() { // TrainingId = rc.TrainingId, TrainingName = rc.TrainingName, TechnologyName = t.TechnologyName, ScheduledDate = rc.ScheduledDate, IsCompleted = n.CompletionFlag, Starttime = rc.StartTime, TrainingId = rc.TrainingId, Instructor = ((i.FirstName.Length > 0 ? i.FirstName : "") + " " + (i.LastName.Length > 0 ? i.LastName : "")), submit = db.TrainingFeedBackCalcs.Where(x => x.TrainingId == rc.TrainingId && x.Flag == true).Count(), pending = db.TrainingFeedBackCalcs.Where(x => x.TrainingId == rc.TrainingId && x.Flag == false).Count(), Endtime = rc.EndTime, Nominations = rc.NumberOfNominated }).OrderByDescending(o => o.ScheduledDate.Value.Year).ThenByDescending(o => o.ScheduledDate.Value.Month).ThenByDescending(o => o.ScheduledDate.Value.Day).Distinct().ToList(); //OrderByDescending(o => o.ScheduledDate).ToList(); //OrderByDescending(o => o.ScheduledDate.Value.Year).ThenByDescending(o => o.ScheduledDate.Value.Month).ThenByDescending(o => o.ScheduledDate.Value.Day).Distinct().ToList(); // var distinct = AsgnList.conductedtrainings.Distinct(); // var distinct = AsgnList.DistinctBy(x => x.trainingid); //var email = System.Web.HttpContext.Current.Application["UserName"].ToString(); int userID = Convert.ToInt32(Session["UserID"]); // var eid = db.Users.Where(x => x.UserID.Equals(userID)).Select(x => x.EmpID).FirstOrDefault(); double totalTraineeScore = 0.0; double totalInstructorScore = 0.0; double totalabsentscore = 0.0; int totalCourseCompleted = 0, totalCourseConducted = 0, totalcourseabsent = 0; //,totalcourseabsented = 0 // if (eid != null) if (userID != null) { //int? eidTemp = Int32.Parse(eid); var TraineeScore = db.TrainingNominations.Where(x => x.UserId == userID && x.Score > 0).Select(x => x.Score); foreach (var score in TraineeScore) { if (score != 0) { totalTraineeScore += (double)score; } } var TraineeScore1 = db.TrainingNominations.Where(x => x.UserId == userID && x.Score < 0).Select(x => x.Score); foreach (var score1 in TraineeScore1) { if (score1 != 0) { totalabsentscore += (double)score1; // NegativeScore = -totalabsentscore; } } List <int?> Inslist = new List <int?>(); List <int> Inslist1 = new List <int>(); //Inslist = (from t in db.Trainings // join tn in db.TrainingNominations on t.TrainingId equals tn.TrainingId // where tn.Score != 0 && t.InstructorId==userId // select t.LevelId).ToList(); Inslist1 = db.Trainings.Where(o => o.InstructorId == userId).Select(o => o.TrainingId).ToList(); foreach (int i in Inslist1) { Inslist.Add(i); } var LevelTrainings = db.TrainingNominations.Where(o => o.Score != 0 && Inslist.Contains(o.TrainingId)).Select(o => o.TrainingId); var InstructorLevel = db.Trainings.Where(o => LevelTrainings.Contains((int?)o.TrainingId)).Select(o => o.LevelId); foreach (var levelid in InstructorLevel) { if (levelid != null) { var InsScore = db.TrainingWeightages.Where(o => o.LevelId == levelid).Select(o => o.Instructor).FirstOrDefault(); if (InsScore != null) { totalInstructorScore += Convert.ToDouble(InsScore); } else { totalInstructorScore += 0.0; } } } var statusIDs = new int[] { 3, 4, 6 }; totalCourseCompleted = //db.TrainingNominations.Where(x => x.EmpId == eidTemp) (from TN in db.TrainingNominations.Where(x => x.UserId == userId && x.Score > 0) join T in db.Trainings on TN.TrainingId equals T.TrainingId join TS in db.Master_TrainingStatus.Where(x => statusIDs.Contains(x.StatusId)) on T.StatusId equals TS.StatusId select new { TN, T, TS }).ToList().Count; //totalcourseabsented = (from TN in db.TrainingNominations.Where(x => x.EmpId == eidTemp && x.Score == 0) // join T in db.Trainings on TN.TrainingId equals T.TrainingId // join TS in db.TrainingStatus.Where(x => statusIDs.Contains(x.StatusId)) on T.StatusId equals TS.StatusId // select new { TN, T, TS }).ToList().Count; totalCourseConducted = //db.TrainingNominations.Where(x => x.EmpId == eidTemp) (from TN in db.Trainings.Where(x => x.InstructorId == userID) join T in db.Trainings on TN.TrainingId equals T.TrainingId join TS in db.Master_TrainingStatus.Where(x => statusIDs.Contains(x.StatusId)) on T.StatusId equals TS.StatusId select new { TN, T, TS }).ToList().Count; totalcourseabsent = (from n in db.TrainingNominations.Where(x => x.UserId == userID && x.Score < 0) select new { n }).ToList().Count; } ViewBag.totalCourseCompleted = totalCourseCompleted; ViewBag.totalCourseConducted = totalCourseConducted; ViewBag.totalcourseabsented = totalcourseabsent; ViewBag.CourseConductedScore = totalInstructorScore; ViewBag.CourseCompletedScore = totalTraineeScore; ViewBag.courseabsentscore = totalabsentscore; ViewBag.scor = totalInstructorScore + totalTraineeScore + totalabsentscore; db.Dispose(); } catch (Exception Ex) { string actionName = this.ControllerContext.RouteData.Values["action"].ToString(); string controllerName = this.ControllerContext.RouteData.Values["controller"].ToString(); ExceptionHandlingController.ExceptionDetails(Ex, actionName, controllerName); } return(View(AsgnList)); }