public IActionResult GetPreviousMonthTimeSheetDataAndConfiguration(TimeSheetDetailQueryModelByInstructor query) { /* Objective: The Web API returns one parent TimeSheet and related TimeSheetDetail for the current system month */ /* The information also embeds the SessionSynopsis data for listbox binding */ Thread.Sleep(2000); List <object> timeSheetDetailList = new List <object>(); List <object> timeSheetData = new List <object>(); object response; List <TimeSheet> oneTimeSheetQueryResult = new List <TimeSheet>(); //string userLoginId = _userManager.GetUserName(User); //string userLoginName = _userManager.GetUserName(User); //int userInfoId = Database.UserInfo.Single(input => input.Email == email).UserInfoId; string email = _userManager.GetUserName(User); string userFullName = Database.UserInfo.Single(input => input.Email == email).FullName; int userInfoId = Database.UserInfo.Single(input => input.Email == email).UserInfoId; if (query.Month != null && query.Month != 0) { //oneTimeSheetQueryResult = Database.TimeSheets // .Include(input => input.Instructor) // .Where(input => (input.InstructorId == userInfoId) && // (input.MonthAndYear.Month == query.Month) && // (input.MonthAndYear.Year == query.Year)).AsNoTracking().ToList<TimeSheet>(); oneTimeSheetQueryResult = Database.TimeSheets .Include(input => input.Instructor) .Where(input => (input.InstructorId == userInfoId) && (input.MonthAndYear.Month == query.Month) && //&& (input.MonthAndYear.Year == DateTime.Now.Year)).AsNoTracking() .ToList <TimeSheet>(); } else { //int currentMonth = DateTime.Now.Month; //DateTime currentDate = DateTime.Now; //string GetCurrentMonthAndYear = string.Format("1/{0}/{1}", currentDate.Month, currentDate.Year); //DateTime MonthYear = DateTime.ParseExact(GetCurrentMonthAndYear, "d/M/yyyy", CultureInfo.InvariantCulture); //int prevMths = DateTime prev = DateTime.Now; //prev = prev.AddMonths(-3); //DateTime currentDate = DateTime.ParseExact(currentDate, "d/M/yyyy", CultureInfo.InvariantCulture); // Concate the 1 with the month and year in date of lesson //DateTime f = DateTime.Now; //f = f.AddMonths(-2); //&& (input.MonthAndYear.Month == f.Month) // This is where we set the condition to get past three months records oneTimeSheetQueryResult = Database.TimeSheets .Include(input => input.Instructor) //.Include(input => input.) .Where(input => (input.InstructorId == userInfoId) && ((input.MonthAndYear.Month >= prev.Month - 3) && (input.MonthAndYear.Month <= prev.Month - 1)) && (input.MonthAndYear.Year == DateTime.Now.Year)).AsNoTracking() .ToList <TimeSheet>(); //.FirstOrDefault(); } if (oneTimeSheetQueryResult == null) { response = new { timeSheet = timeSheetData, timeSheetDetails = timeSheetDetailList }; return(new JsonResult(response)); } foreach (var oneTimeSheetResult in oneTimeSheetQueryResult) { //oneTimeSheetData = new //{ // timeSheetId = oneTimeSheetQueryResult.TimeSheetId, // instructorName = oneTimeSheetQueryResult.Instructor.FullName, // year = oneTimeSheetQueryResult.MonthAndYear.Year, // month = oneTimeSheetQueryResult.MonthAndYear.Month, // instructorId = oneTimeSheetQueryResult.InstructorId, // createdAt = oneTimeSheetQueryResult.CreatedAt, // updatedAt = oneTimeSheetQueryResult.UpdatedAt, // approvedAt = oneTimeSheetQueryResult.ApprovedAt //}; timeSheetData.Add(new { timeSheetId = oneTimeSheetResult.TimeSheetId, instructorName = oneTimeSheetResult.Instructor.FullName, year = oneTimeSheetResult.MonthAndYear.Year, month = oneTimeSheetResult.MonthAndYear.Month, instructorId = oneTimeSheetResult.InstructorId, createdAt = oneTimeSheetResult.CreatedAt, updatedAt = oneTimeSheetResult.UpdatedAt, approvedAt = oneTimeSheetResult.ApprovedAt } ); } List <TimeSheetDetail> timeSheetDetailsQueryResult = new List <TimeSheetDetail>(); if (oneTimeSheetQueryResult != null) { foreach (var _oneTimeSheetQueryResult in oneTimeSheetQueryResult) { var listTimeSheet = Database.TimeSheetDetails .Where(input => input.TimeSheetId == _oneTimeSheetQueryResult.TimeSheetId) .AsNoTracking().ToList <TimeSheetDetail>(); foreach (var _listTimeSheet in listTimeSheet) { timeSheetDetailsQueryResult.Add(_listTimeSheet); } } //timeSheetDetailsQueryResult = Database.TimeSheetDetails // .Where(input => input.TimeSheetId == oneTimeSheetQueryResult[0].TimeSheetId) // .AsNoTracking().ToList<TimeSheetDetail>(); } //The following block of LINQ code is used for testing purpose to sort the //timesheetdetail information by lesson dates. var sortedTimeSheetDetailList = from e in timeSheetDetailsQueryResult select new { timeSheetDetailId = e.TimeSheetDetailId, dateOfLesson = e.DateOfLesson, officialTimeIn = e.OfficialTimeInMinutes, officialTimeOut = e.OfficialTimeOutMinutes, actualTimeIn = e.TimeInInMinutes, officialTimeInHHMM = ConvertFromMinutesToHHMM(e.OfficialTimeInMinutes), actualTimeOut = e.TimeOutInMinutes, officialTimeOutHHMM = ConvertFromMinutesToHHMM(e.OfficialTimeOutMinutes), wageRatePerHour = e.WageRatePerHour, ratePerHour = e.RatePerHour, customerAccountName = e.AccountName, sessionSynopsisNames = e.SessionSynopsisNames, createdBy = userFullName, /*If the record is filled with time-in and time-out and a signature has been obtained, * this record is not editable*/ locked = ((e.TimeInInMinutes != null) && (e.SignedStatus == true)) ? true : false, updateable = (e.SignedStatus == false) ? true : false, signedStatus = e.SignedStatus, actualTimeInHHMM = (e.TimeInInMinutes != null) ? ConvertFromMinutesToHHMM(e.TimeInInMinutes) : "", actualTimeOutHHMM = (e.TimeOutInMinutes != null) ? ConvertFromMinutesToHHMM(e.TimeOutInMinutes) : "", comments = e.Comments, isReplacement = e.IsReplacementInstructor, updatedAt = e.UpdatedAt } into temp orderby temp.dateOfLesson ascending select temp; foreach (var oneTimeSheetDetail in timeSheetDetailsQueryResult) { timeSheetDetailList.Add(new { timeDetailSheetId = oneTimeSheetDetail.TimeSheetDetailId, dateOfLesson = oneTimeSheetDetail.DateOfLesson, officialTimeIn = oneTimeSheetDetail.OfficialTimeInMinutes, officialTimeOut = oneTimeSheetDetail.OfficialTimeOutMinutes, actualTimeIn = oneTimeSheetDetail.TimeInInMinutes, actualTimeOut = oneTimeSheetDetail.TimeOutInMinutes, wageRatePerHour = oneTimeSheetDetail.WageRatePerHour, ratePerHour = oneTimeSheetDetail.RatePerHour, customerAccountName = oneTimeSheetDetail.AccountName, sessionSynopsisNames = oneTimeSheetDetail.SessionSynopsisNames }); }//end of foreach loop which builds the timeSheetDetailList List container . //Obtain SessionSynopsis data var sessionSynopsisList = Database.SessionSynopses .Where(input => input.IsVisible == true) .Select(input => new { sessionSynopsisId = input.SessionSynopsisId, sessionSynopsisName = input.SessionSynopsisName }).ToList(); response = new { timeSheet = timeSheetData, timeSheetDetails = sortedTimeSheetDetailList, // timeSheetDetailList = sortedTimeSheetDetailList, //sessionSynopsisList = sessionSynopsisList }; return(new JsonResult(response)); }//End of GetPreviousMonthTimeSheetDataAndConfiguration
public IActionResult GetTimeSheetAndTimeSheetDetails(TimeSheetDetailQueryModelByInstructor query) { List <object> timeSheetDetailList = new List <object>(); object oneTimeSheetData = null; object response; var oneTimeSheetQueryResult = Database.TimeSheets .Include(input => input.Instructor) .Where(input => (input.InstructorId == query.InstructorId) && (input.MonthAndYear.Month == query.Month) && (input.MonthAndYear.Year == query.Year)).AsNoTracking().FirstOrDefault(); if (oneTimeSheetQueryResult == null) { response = new { timeSheet = oneTimeSheetData, timeSheetDetails = timeSheetDetailList }; return(new JsonResult(response)); } oneTimeSheetData = new { timeSheetId = oneTimeSheetQueryResult.TimeSheetId, instructorName = oneTimeSheetQueryResult.Instructor.FullName, year = oneTimeSheetQueryResult.MonthAndYear.Year, month = oneTimeSheetQueryResult.MonthAndYear.Month, instructorId = oneTimeSheetQueryResult.InstructorId, createdAt = oneTimeSheetQueryResult.CreatedAt, updatedAt = oneTimeSheetQueryResult.UpdatedAt, approvedAt = oneTimeSheetQueryResult.ApprovedAt }; List <TimeSheetDetail> timeSheetDetailsQueryResult = new List <TimeSheetDetail>(); if (oneTimeSheetQueryResult != null) { timeSheetDetailsQueryResult = Database.TimeSheetDetails .Where(input => input.TimeSheetId == oneTimeSheetQueryResult.TimeSheetId) .AsNoTracking().ToList <TimeSheetDetail>(); } //The following block of LINQ code is used for testing purpose to sort the //timesheetdetail information by lesson dates. var sortedTimeSheetDetailList = from e in timeSheetDetailsQueryResult select new { timeDetailSheetId = e.TimeSheetDetailId, dateOfLesson = e.DateOfLesson, officialTimeIn = e.OfficialTimeInMinutes, officialTimeOut = e.OfficialTimeOutMinutes, actualTimeIn = e.TimeInInMinutes, actualTimeOut = e.TimeOutInMinutes, wageRatePerHour = e.WageRatePerHour, ratePerHour = e.RatePerHour, customerAccountName = e.AccountName, sessionSynopsisNames = e.SessionSynopsisNames } into temp orderby temp.dateOfLesson ascending select temp; foreach (var oneTimeSheetDetail in timeSheetDetailsQueryResult) { timeSheetDetailList.Add(new { timeDetailSheetId = oneTimeSheetDetail.TimeSheetDetailId, dateOfLesson = oneTimeSheetDetail.DateOfLesson, officialTimeIn = oneTimeSheetDetail.OfficialTimeInMinutes, officialTimeOut = oneTimeSheetDetail.OfficialTimeOutMinutes, actualTimeIn = oneTimeSheetDetail.TimeInInMinutes, actualTimeOut = oneTimeSheetDetail.TimeOutInMinutes, wageRatePerHour = oneTimeSheetDetail.WageRatePerHour, ratePerHour = oneTimeSheetDetail.RatePerHour, customerAccountName = oneTimeSheetDetail.AccountName, sessionSynopsisNames = oneTimeSheetDetail.SessionSynopsisNames }); }//end of foreach loop which builds the timeSheetDetailList List container . response = new { timeSheet = oneTimeSheetData, timeSheetDetails = sortedTimeSheetDetailList }; return(new JsonResult(response)); }//End of GetTimeSheetAndTimeSheetDetails
public IActionResult GetCurrentMonthTimeSheetDataAndConfiguration(TimeSheetDetailQueryModelByInstructor query) { /* Objective: The Web API returns one parent TimeSheet and related TimeSheetDetail for the current system month */ /* The information also embeds the SessionSynopsis data for listbox binding */ Thread.Sleep(2000); List <object> timeSheetDetailList = new List <object>(); object oneTimeSheetData = null; object response; TimeSheet oneTimeSheetQueryResult = new TimeSheet(); string email = _userManager.GetUserName(User); int userInfoId = Database.UserInfo.Single(input => input.Email == email).UserInfoId; if (query.Month != null) { oneTimeSheetQueryResult = Database.TimeSheets .Include(input => input.Instructor) .Where(input => (input.InstructorId == userInfoId) && (input.MonthAndYear.Month == query.Month) && (input.MonthAndYear.Year == query.Year)).AsNoTracking().FirstOrDefault(); } else { oneTimeSheetQueryResult = Database.TimeSheets .Include(input => input.Instructor) .Where(input => (input.InstructorId == userInfoId) && (input.MonthAndYear.Month == DateTime.Now.Month) && (input.MonthAndYear.Year == DateTime.Now.Year)).AsNoTracking().FirstOrDefault(); } if (oneTimeSheetQueryResult == null) { response = new { timeSheet = oneTimeSheetData, timeSheetDetails = timeSheetDetailList }; return(new JsonResult(response)); } oneTimeSheetData = new { timeSheetId = oneTimeSheetQueryResult.TimeSheetId, instructorName = oneTimeSheetQueryResult.Instructor.FullName, year = oneTimeSheetQueryResult.MonthAndYear.Year, month = oneTimeSheetQueryResult.MonthAndYear.Month, instructorId = oneTimeSheetQueryResult.InstructorId, createdAt = oneTimeSheetQueryResult.CreatedAt, updatedAt = oneTimeSheetQueryResult.UpdatedAt, approvedAt = oneTimeSheetQueryResult.ApprovedAt }; List <TimeSheetDetail> timeSheetDetailsQueryResult = new List <TimeSheetDetail>(); if (oneTimeSheetQueryResult != null) { timeSheetDetailsQueryResult = Database.TimeSheetDetails .Where(input => input.TimeSheetId == oneTimeSheetQueryResult.TimeSheetId) .AsNoTracking().ToList <TimeSheetDetail>(); } //The following block of LINQ code is used for testing purpose to sort the //timesheetdetail information by lesson dates. var sortedTimeSheetDetailList = from e in timeSheetDetailsQueryResult select new { timeSheetDetailId = e.TimeSheetDetailId, dateOfLesson = e.DateOfLesson, officialTimeIn = e.OfficialTimeInMinutes, officialTimeOut = e.OfficialTimeOutMinutes, actualTimeIn = e.TimeInInMinutes, officialTimeInHHMM = ConvertFromMinutesToHHMM(e.OfficialTimeInMinutes), actualTimeOut = e.TimeOutInMinutes, officialTimeOutHHMM = ConvertFromMinutesToHHMM(e.OfficialTimeOutMinutes), wageRatePerHour = e.WageRatePerHour, ratePerHour = e.RatePerHour, customerAccountName = e.AccountName, sessionSynopsisNames = e.SessionSynopsisNames, /*If the record is filled with time-in and time-out and a signature has been obtained, this record is not editable*/ locked = ((e.TimeInInMinutes != null) && (e.SignedStatus == true)) ? true : false, updateable = (e.SignedStatus == false) ? true : false, signedStatus = e.SignedStatus, actualTimeInHHMM = (e.TimeInInMinutes != null) ? ConvertFromMinutesToHHMM(e.TimeInInMinutes) : "", actualTimeOutHHMM = (e.TimeOutInMinutes != null) ? ConvertFromMinutesToHHMM(e.TimeOutInMinutes) : "", comments = e.Comments, isReplacement = e.IsReplacementInstructor, updatedAt = e.UpdatedAt } into temp orderby temp.dateOfLesson ascending select temp; foreach (var oneTimeSheetDetail in timeSheetDetailsQueryResult) { timeSheetDetailList.Add(new { timeDetailSheetId = oneTimeSheetDetail.TimeSheetDetailId, dateOfLesson = oneTimeSheetDetail.DateOfLesson, officialTimeIn = oneTimeSheetDetail.OfficialTimeInMinutes, officialTimeOut = oneTimeSheetDetail.OfficialTimeOutMinutes, actualTimeIn = oneTimeSheetDetail.TimeInInMinutes, actualTimeOut = oneTimeSheetDetail.TimeOutInMinutes, wageRatePerHour = oneTimeSheetDetail.WageRatePerHour, ratePerHour = oneTimeSheetDetail.RatePerHour, customerAccountName = oneTimeSheetDetail.AccountName, sessionSynopsisNames = oneTimeSheetDetail.SessionSynopsisNames }); }//end of foreach loop which builds the timeSheetDetailList List container . //Obtain SessionSynopsis data var sessionSynopsisList = Database.SessionSynopses .Where(input => input.IsVisible == true) .Select(input => new { sessionSynopsisId = input.SessionSynopsisId, sessionSynopsisName = input.SessionSynopsisName }).ToList(); response = new { timeSheet = oneTimeSheetData, timeSheetDetailList = sortedTimeSheetDetailList, sessionSynopsisList = sessionSynopsisList }; return(new JsonResult(response)); }//End of GetCurrentMonthTimeSheetDataAndConfiguration