public async Task <List <AbsenceModel> > GetMonthlyOffRecordAsync(string y, string m) { using (var dbContext = new AppDbContext(builder.Options)) { var year = Int32.Parse(y); var month = Int32.Parse(m); DateTime fromTime = new DateTime(year, month, 1); DateTime toTime = new DateTime(year, month + 1, 1).AddSeconds(-1); var data = await(from record in dbContext.CheckRecord join user in dbContext.UserInfo on record.UserId equals user.Id where record.CheckedDate >= fromTime && record.CheckedDate <= toTime && !string.IsNullOrWhiteSpace(record.OffType) && record.StatusOfApproval != StatusOfApprovalEnum.REJECTED() select new AbsenceModel { UserName = user.UserName, CheckedDate = record.CheckedDate.ToString("yyyy-MM-dd"), OffType = record.OffType, OffTimeStart = record.OffTimeStart.ToString(), OffTimeEnd = record.OffTimeEnd.ToString(), OffReason = record.OffReason, StatusOfApproval = record.StatusOfApproval }).ToListAsync(); return(data); } }
public async Task <ActionResult> GetInitState() { var record = await _recordManager.GetRecordOfToday(User.FindFirstValue(ClaimTypes.NameIdentifier)); if (record != null) { bool shouldCheckInDisable = true, shouldCheckOutDisable = true; string offStatus = null; TimeSpan timeStart, timeEnd; if (record.StatusOfApproval == StatusOfApprovalEnum.APPROVED()) { if (record.OffTimeStart != null) { timeStart = record.OffTimeStart.Value; } if (record.OffTimeEnd != null) { timeEnd = record.OffTimeEnd.Value; } offStatus = $"{timeStart.ToString("h'時'")}-{timeEnd.ToString("h'時'")} {record.OffType}"; } if (record.CheckInTime == null) { shouldCheckInDisable = false; } if (record.CheckOutTime == null) { shouldCheckOutDisable = false; } return (Json( new { status = true, payload = new { shouldCheckInDisable = shouldCheckInDisable, shouldCheckOutDisable = shouldCheckOutDisable, currentDate = DateTime.Today.ToString("yyyy年MM月dd日 (ddd)"), currentTime = DateTime.Now.ToString("hh:mm:ss tt"), checkIn = record.CheckInTime, checkOut = record.CheckOutTime, offStatus = offStatus } } )); } return(Json(new { status = true, payload = new { shouldCheckInDisable = false, shouldCheckOutDisable = false, currentDate = DateTime.Today.ToString("yyyy年MM月dd日 (ddd)"), currentTime = DateTime.Now.ToString("h:mm:ss tt") } })); }
public async Task DeleteOTAsync(string id, DateTime date) { using (var dbContext = new AppDbContext(builder.Options)) { dbContext.ChangeTracker.AutoDetectChangesEnabled = false; var result = await dbContext.CheckRecord.FirstOrDefaultAsync(s => s.UserId == id && s.CheckedDate == date); if (result == null) { return; } if (result.CheckInTime == null && result.CheckOutTime == null && string.IsNullOrWhiteSpace(result.OvertimeEndTime)) { dbContext.Entry(result).State = EntityState.Deleted; } else { result.OvertimeEndTime = null; result.StatusOfApprovalForOvertime = StatusOfApprovalEnum.PENDING(); dbContext.Entry(result).State = EntityState.Modified; } await dbContext.SaveChangesAsync(); } }
public async Task DeleteRecordByRecordIdAsync(string recordId) { using (var dbContext = new AppDbContext(builder.Options)) { var record = await dbContext.CheckRecord.FirstOrDefaultAsync(s => s.Id == recordId); if (record == null) { return; } if (TypeEnum.LimitedLeaves.Contains(record.OffType) && record.StatusOfApproval == StatusOfApprovalEnum.APPROVED()) { var user = await dbContext.UserInfo.FirstOrDefaultAsync(s => s.Id == record.UserId); if (user == null) { return; } user = TypeEnum.CalcLeaves(user, record.OffTimeStart, record.OffTimeEnd, record.OffType, inverseOp: true); dbContext.Entry(user).State = EntityState.Modified; } dbContext.Entry(record).State = EntityState.Deleted; await dbContext.SaveChangesAsync(); } }
public async Task <bool> SetStatusOfApprovalAsync(string recordId, string status, bool OT = false) { using (var dbContext = new AppDbContext(builder.Options)) { try { dbContext.ChangeTracker.AutoDetectChangesEnabled = false; var record = await dbContext.CheckRecord.FirstOrDefaultAsync(s => s.Id == recordId); if (record == null) { return(false); } if (status == StatusOfApprovalEnum.APPROVED()) { if (OT) { record.StatusOfApprovalForOvertime = status; } else { var user = await dbContext.UserInfo.FirstOrDefaultAsync(s => s.Id == record.UserId); user = TypeEnum.CalcLeaves(user, record.OffTimeStart, record.OffTimeEnd, record.OffType); if (user == null) { record.StatusOfApproval = StatusOfApprovalEnum.REJECTED(); } else { dbContext.Entry(user).State = EntityState.Modified; record.StatusOfApproval = status; } } } else { if (OT) { record.StatusOfApprovalForOvertime = status; } else { record.StatusOfApproval = status; } } dbContext.Entry(record).State = EntityState.Modified; await dbContext.SaveChangesAsync(); return(true); } catch (Exception) { return(false); } } }
public async Task <bool> AddLeaveAsync(string id, OffRecordModel model) { using (var dbContext = new AppDbContext(builder.Options)) { dbContext.ChangeTracker.AutoDetectChangesEnabled = false; var recordList = new List <CheckRecord>(); var records = dbContext.CheckRecord.Where( s => s.UserId == id && s.CheckedDate >= model.CheckedDate && s.CheckedDate <= model.OffEndDate); for (var i = model.CheckedDate.Date; model.OffEndDate != null && i <= model.OffEndDate.Value.Date; i = i.AddDays(1)) { var record = records.FirstOrDefault(s => s.CheckedDate.Date == i.Date); if (record == null) { recordList.Add(new CheckRecord { UserId = id, CheckedDate = i, OffEndDate = model.OffEndDate, OffType = model.OffType, OffReason = model.OffReason, OffTimeStart = model.OffTimeStart, OffTimeEnd = model.OffTimeEnd, OffApplyDate = DateTime.Today }); } else { record.UserId = id; record.OffEndDate = model.OffEndDate; record.OffTimeStart = model.OffTimeStart; record.OffTimeEnd = model.OffTimeEnd; record.OffReason = model.OffReason; record.OffType = model.OffType; record.StatusOfApproval = StatusOfApprovalEnum.PENDING(); record.OffApplyDate = DateTime.Today; dbContext.Entry(record).State = EntityState.Modified; } } await dbContext.CheckRecord.AddRangeAsync(recordList); await dbContext.SaveChangesAsync(); return(true); } }
public async Task <bool> DeleteLeaveAsync(string id, DateTime date) { using (var dbContext = new AppDbContext(builder.Options)) { dbContext.ChangeTracker.AutoDetectChangesEnabled = false; var result = await dbContext.CheckRecord.FirstOrDefaultAsync(s => s.UserId == id && s.CheckedDate == date); if (result == null) { return(false); } if (result.StatusOfApproval == StatusOfApprovalEnum.APPROVED()) { var user = await dbContext.UserInfo.FirstOrDefaultAsync(s => s.Id == result.UserId); user = TypeEnum.CalcLeaves(user, result.OffTimeStart, result.OffTimeEnd, result.OffType, inverseOp: true); dbContext.Entry(user).State = EntityState.Modified; } if (result.CheckInTime == null && result.CheckOutTime == null && string.IsNullOrWhiteSpace(result.OvertimeEndTime)) { dbContext.Entry(result).State = EntityState.Deleted; } else { result.OffApplyDate = null; result.OffTimeStart = null; result.OffTimeEnd = null; result.OffEndDate = null; result.OffReason = null; result.OffType = null; result.StatusOfApproval = StatusOfApprovalEnum.PENDING(); dbContext.Entry(result).State = EntityState.Modified; } await dbContext.SaveChangesAsync(); return(true); } }
public async Task AddOTAsync(string id, DateTime d, string t) { using (var dbContext = new AppDbContext(builder.Options)) { var record = await dbContext.CheckRecord.FirstOrDefaultAsync(s => s.UserId == id && s.CheckedDate == d); if (record != null) { record.OvertimeEndTime = t; record.StatusOfApprovalForOvertime = StatusOfApprovalEnum.PENDING(); } else { await dbContext.CheckRecord.AddAsync(new CheckRecord { UserId = id, CheckedDate = d, OvertimeEndTime = t, StatusOfApprovalForOvertime = StatusOfApprovalEnum.PENDING(), }); } await dbContext.SaveChangesAsync(); } }
public async Task <ActionResult> ExportXlsx(string a, string b, string c, string d) { try { DateTime from = DateTime.Parse(c); DateTime to = DateTime.Parse(d); var result = await QueryRecord(new QueryModel { Id = a, Options = b, FromDate = from, ToDate = to }); using (ExcelPackage pkg = new ExcelPackage(new FileInfo($"{Guid.NewGuid()}.xlsx"))) { ExcelWorksheet worksheet = pkg.Workbook.Worksheets.Add("報表1"); for (int x = 1; x < 10; x++) { worksheet.Column(x).Width = 15; worksheet.Column(x).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; } worksheet.Cells[1, 1].Value = $"{c} - {d} 出勤狀況表"; worksheet.Cells[1, 1, 1, 11].Merge = true; worksheet.Cells[1, 1, 1, 11].Style.Font.Bold = true; worksheet.Cells[1, 1, 1, 11].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; worksheet.Cells[2, 1].Value = "日期"; worksheet.Cells[2, 2].Value = "姓名"; worksheet.Cells[2, 3].Value = "上班時間"; worksheet.Cells[2, 4].Value = "下班時間"; worksheet.Cells[2, 5].Value = "上班打卡座標"; worksheet.Cells[2, 6].Value = "下班打卡座標"; worksheet.Cells[2, 7].Value = "加班時間"; worksheet.Cells[2, 8].Value = "請假申請日期"; worksheet.Cells[2, 9].Value = "請假類別"; worksheet.Cells[2, 10].Value = "請假時間"; worksheet.Cells[2, 11].Value = "請假原因"; for (int i = 0; i < result.Count; i++) { if (result[i].StatusOfApproval == StatusOfApprovalEnum.APPROVED()) { worksheet.Cells[$"H{i + 3}"].Value = result[i].OffApplyDate; worksheet.Cells[$"I{i + 3}"].Value = result[i].OffType; worksheet.Cells[$"J{i + 3}"].Value = $"{result[i].OffTimeStart} - {result[i].OffTimeEnd}"; worksheet.Cells[$"K{i + 3}"].Value = result[i].OffReason; } else if (string.IsNullOrWhiteSpace(result[i].CheckInTime) && string.IsNullOrWhiteSpace(result[i].CheckOutTime)) { continue; } worksheet.Cells[$"A{i + 3}"].Value = result[i].CheckedDate; worksheet.Cells[$"B{i + 3}"].Value = result[i].UserName; worksheet.Cells[$"C{i + 3}"].Value = result[i].CheckInTime; worksheet.Cells[$"D{i + 3}"].Value = result[i].CheckOutTime; worksheet.Cells[$"E{i + 3}"].Value = result[i].GeoLocation1; worksheet.Cells[$"F{i + 3}"].Value = result[i].GeoLocation2; if (result[i].StatusOfApprovalForOvertime == StatusOfApprovalEnum.APPROVED()) { worksheet.Cells[$"G{i + 3}"].Value = result[i].OvertimeEndTime; } } byte[] file = pkg.GetAsByteArray(); return(File(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")); } } catch (ArgumentNullException) { return(BadRequest("Invalid parameters.")); } catch (FormatException) { return(BadRequest("Invalid parameters.")); } }
public Tuple <int, int> BulkyUpdate(List <ExcelDto> list) { using (var dbContext = new AppDbContext(builder.Options)) { dbContext.ChangeTracker.AutoDetectChangesEnabled = false; DateTime date, checkedDate; List <CheckRecord> newList = new List <CheckRecord>(); int count = 0; foreach (var i in list) { DateTime?offapplyDate; TimeSpan?checkIn, checkOut, offStart, offEnd; DateTime.TryParse(i.CheckedDate, out checkedDate); var obj = dbContext.CheckRecord.FirstOrDefault(s => s.UserId == i.Id && s.CheckedDate == checkedDate); if (i.IsInValid) { DateTime.TryParse(i.CheckInTime, out date); checkIn = date.TimeOfDay; } else { checkIn = null; } if (i.IsOutValid) { DateTime.TryParse(i.CheckOutTime, out date); checkOut = date.TimeOfDay; } else { checkOut = null; } if (i.IsOTValid) { DateTime.TryParse(i.OffApplyDate, out date); offapplyDate = date; } else { offapplyDate = null; } if (i.IsOffValid) { var off = i.OffTime.Split('-'); DateTime.TryParse(off[0], out date); offStart = date.TimeOfDay; DateTime.TryParse(off[1], out date); offEnd = date.TimeOfDay; } else { offStart = null; offEnd = null; } if (obj == null) { newList.Add(new CheckRecord { CheckedDate = checkedDate, CheckInTime = checkIn, CheckOutTime = checkOut, GeoLocation1 = i.GeoLocation1, GeoLocation2 = i.GeoLocation2, OvertimeEndTime = i.OvertimeEndTime, OffType = i.OffType, OffApplyDate = offapplyDate, OffReason = i.OffReason, OffTimeStart = offStart, OffTimeEnd = offEnd, StatusOfApproval = i.IsOffValid ? StatusOfApprovalEnum.APPROVED() : null, StatusOfApprovalForOvertime = i.IsOTValid ? StatusOfApprovalEnum.APPROVED() : null, }); continue; } obj.CheckedDate = checkedDate; obj.CheckInTime = checkIn; obj.CheckOutTime = checkOut; obj.GeoLocation1 = i.GeoLocation1; obj.GeoLocation2 = i.GeoLocation2; obj.OvertimeEndTime = i.OvertimeEndTime; obj.OffType = i.OffType; obj.OffApplyDate = offapplyDate; obj.OffReason = i.OffReason; obj.OffTimeStart = offStart; obj.OffTimeEnd = offEnd; obj.StatusOfApproval = i.IsOffValid ? StatusOfApprovalEnum.APPROVED() : null; obj.StatusOfApprovalForOvertime = i.IsOTValid ? StatusOfApprovalEnum.APPROVED() : null; dbContext.Entry(obj).State = EntityState.Modified; count++; } dbContext.CheckRecord.AddRange(newList); dbContext.SaveChanges(); return(new Tuple <int, int>(newList.Count, count)); } }
/* bug here, still yet to test */ // automatic fill up all empty records for administrator public async Task AutomaticAddRecordAsync(DateTime date, IList <UserInfo> excludeFrom = null) { using (var dbContext = new AppDbContext(builder.Options)) { dbContext.ChangeTracker.AutoDetectChangesEnabled = false; var records = dbContext.CheckRecord.Where(s => s.CheckedDate == date); var users = dbContext.UserInfo.Where( s => s.DateOfQuit == null && excludeFrom.FirstOrDefault(z => z.Id == s.Id) == null); var result = from user in users join record in records on user.Id equals record.UserId into userRecord from ur in userRecord.DefaultIfEmpty() select new { userRecords = ur, Id = user.Id }; var list = new List <CheckRecord>(); var rnd = new Random(); var checkInTime = new TimeSpan(9, 0, 0); var checkOutTime = new TimeSpan(18, 0, 0); foreach (var i in result) { if (i.userRecords == null) { list.Add(new CheckRecord { UserId = i.Id, CheckedDate = date, CheckInTime = checkInTime.Add(new TimeSpan(0, rnd.Next(-30, 30), rnd.Next(-60, 60))), CheckOutTime = checkOutTime.Add(new TimeSpan(0, rnd.Next(-30, 50), rnd.Next(-60, 60))), GeoLocation1 = "24.997671, 121.53798", GeoLocation2 = "24.997671, 121.53798" }); } else { if (!string.IsNullOrWhiteSpace(i.userRecords.OffType) && i.userRecords.StatusOfApproval == StatusOfApprovalEnum.APPROVED()) { continue; } if (i.userRecords.CheckInTime == null) { i.userRecords.CheckInTime = checkInTime.Add(new TimeSpan (0, rnd.Next(-30, 30), rnd.Next(-60, 60))); i.userRecords.GeoLocation1 = "24.997671, 121.53798"; } if (i.userRecords.CheckOutTime == null) { i.userRecords.CheckOutTime = checkOutTime.Add(new TimeSpan (0, rnd.Next(-30, 50), rnd.Next(-60, 60))); i.userRecords.GeoLocation2 = "24.997671, 121.53798"; } if (string.IsNullOrWhiteSpace(i.userRecords.GeoLocation1) && i.userRecords.CheckInTime != null) { i.userRecords.GeoLocation1 = "24.997671, 121.53798"; } if (string.IsNullOrWhiteSpace(i.userRecords.GeoLocation2) && i.userRecords.CheckOutTime != null) { i.userRecords.GeoLocation2 = "24.997671, 121.53798"; } dbContext.Entry(i.userRecords).State = EntityState.Modified; } } await dbContext.CheckRecord.AddRangeAsync(list); await dbContext.SaveChangesAsync(); } }
public async Task InsertOrUpdateRecordAsync(EditRecordModel model) { using (var dbContext = new AppDbContext(builder.Options)) { dbContext.ChangeTracker.AutoDetectChangesEnabled = false; bool isNewRecordNeedCalc = TypeEnum.LimitedLeaves.Contains(model.OffType) && model.StatusOfApproval == StatusOfApprovalEnum.APPROVED(); if (string.IsNullOrWhiteSpace(model.RecordId)) { DateTime?offApplyDate = null; if (!string.IsNullOrWhiteSpace(model.OffType)) { offApplyDate = DateTime.Today; } if (isNewRecordNeedCalc) { var user = await dbContext.UserInfo.FirstOrDefaultAsync(s => s.Id == model.UserId); user = TypeEnum.CalcLeaves(user, model.OffTimeStart, model.OffTimeEnd, model.OffType); if (user == null) { return; } dbContext.Entry(user).State = EntityState.Modified; } var r = new CheckRecord { UserId = model.UserId, CheckedDate = model.CheckedDate, CheckInTime = model.CheckInTime, CheckOutTime = model.CheckOutTime, GeoLocation1 = model.GeoLocation1, GeoLocation2 = model.GeoLocation2, OvertimeEndTime = model.OvertimeEndTime, OffType = model.OffType, OffTimeStart = model.OffTimeStart, OffTimeEnd = model.OffTimeEnd, OffReason = model.OffReason, StatusOfApproval = model.StatusOfApproval, OffApplyDate = offApplyDate }; await dbContext.CheckRecord.AddAsync(r); } else { var record = await dbContext.CheckRecord.FirstOrDefaultAsync(s => s.Id == model.RecordId); if (record != null) { bool isOldRecordNeedCalc = TypeEnum.LimitedLeaves.Contains(record.OffType) && record.StatusOfApproval == StatusOfApprovalEnum.APPROVED(); if (isOldRecordNeedCalc || isNewRecordNeedCalc) { // new data is special, and approved - var user = await dbContext.UserInfo.FirstOrDefaultAsync(s => s.Id == record.UserId); if (user == null) { return; } if (isNewRecordNeedCalc) { var newCalc = TypeEnum.CalcLeaves(user, model.OffTimeStart, model.OffTimeEnd, model.OffType); if (newCalc != null) { user = newCalc; dbContext.Entry(user).State = EntityState.Modified; } else { return; } } // old data is special and approved + if (isOldRecordNeedCalc) { user = TypeEnum.CalcLeaves(user, record.OffTimeStart, record.OffTimeEnd, record.OffType, inverseOp: true); dbContext.Entry(user).State = EntityState.Modified; } } if (string.IsNullOrWhiteSpace(record.OffType) && !string.IsNullOrWhiteSpace(model.OffType)) { record.OffApplyDate = DateTime.Today; } record.CheckInTime = model.CheckInTime; record.CheckOutTime = model.CheckOutTime; record.GeoLocation1 = model.GeoLocation1; record.GeoLocation2 = model.GeoLocation2; record.OvertimeEndTime = model.OvertimeEndTime; record.OffType = model.OffType; record.OffTimeStart = model.OffTimeStart; record.OffTimeEnd = model.OffTimeEnd; record.OffReason = model.OffReason; record.StatusOfApproval = model.StatusOfApproval; dbContext.Entry(record).State = EntityState.Modified; } } await dbContext.SaveChangesAsync(); } }