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();
}
}
