public IQueryable <Staff> Search(StaffSearchModel searchModel)
{
IQueryable <Staff> staffs = this.context.Staff;
if (!string.IsNullOrWhiteSpace(searchModel.Nr))
{
staffs = staffs.Where(c => c.nr.Contains(searchModel.Nr.Trim()));
}
if (!string.IsNullOrWhiteSpace(searchModel.NrAct))
{
staffs = staffs.Where(c => c.nr.Equals(searchModel.NrAct.Trim()));
}
if (!string.IsNullOrWhiteSpace(searchModel.Name))
{
staffs = staffs.Where(c => c.name.Contains(searchModel.Name.Trim()));
}
if (!string.IsNullOrWhiteSpace(searchModel.Id))
{
staffs = staffs.Where(c => c.id.Contains(searchModel.Id.Trim()));
}
if (searchModel.Sex.HasValue)
{
staffs = staffs.Where(c => c.sex.Equals(searchModel.Sex));
}
if (searchModel.JobTitleId.HasValue)
{
staffs = staffs.Where(c => c.jobTitleId.Equals(searchModel.JobTitleId));
}
if (searchModel.companyId.HasValue)
{
staffs = staffs.Where(c => c.companyId.Equals(searchModel.companyId));
}
if (searchModel.departmentId.HasValue)
{
staffs = staffs.Where(c => c.departmentId.Equals(searchModel.departmentId));
}
if (searchModel.CompanyEmployAtFrom.HasValue)
{
staffs = staffs.Where(c => c.companyEmployAt > searchModel.CompanyEmployAtFrom);
}
if (searchModel.CompanyEmployAtTo.HasValue)
{
staffs = staffs.Where(c => c.companyEmployAt < searchModel.CompanyEmployAtTo);
}
if (searchModel.IsOnTrial.HasValue)
{
staffs = staffs.Where(c => c.isOnTrial.Equals(searchModel.IsOnTrial));
}
return(staffs);
}
public ActionResult Search([Bind(Include = "Nr, Name, Id, Sex, JobTitleId, CompanyId, DepartmentId, CompanyEmployAtFrom, CompanyEmployAtTo, IsOnTrial")] StaffSearchModel q)
{
int pageIndex = 0;
int.TryParse(Request.QueryString.Get("page"), out pageIndex);
pageIndex = PagingHelper.GetPageIndex(pageIndex);
IStaffService ss = new StaffService(Settings.Default.db);
IPagedList <Staff> staffs = ss.Search(q).ToPagedList(pageIndex, Settings.Default.pageSize);
Staff staff = new Staff();
staff.companyId = Convert.ToInt16(q.companyId);
staff.departmentId = Convert.ToInt16(q.departmentId);
//防止SetSexList 抛出异常
if (q.Sex.HasValue)
{
staff.sex = Convert.ToString(q.Sex);
}
staff.jobTitleId = q.JobTitleId;
staff.isOnTrial = Convert.ToBoolean(q.IsOnTrial);
SetDropDownList(staff);
ViewBag.Query = q;
return(View("Index", staffs));
}
public ActionResult Index(int?page)
{
int pageIndex = PagingHelper.GetPageIndex(page);
StaffSearchModel q = new StaffSearchModel();
IStaffService ss = new StaffService(Settings.Default.db);
IPagedList <Staff> staffs = ss.Search(q).ToPagedList(pageIndex, Settings.Default.pageSize);
ViewBag.Query = q;
SetDropDownList(null);
return(View(staffs));
}
public ActionResult ExportReport([Bind(Include = "CompanyId,DepartmentId")] StaffSearchModel q)
{
var msg = new ReportMessage();
if (Request.QueryString["type"] == "100")
{
msg = new ReportExcelHelper(Settings.Default.db).ExportExtraSumReport(
DateTime.Parse(Request.QueryString["startDate"]),
DateTime.Parse(Request.QueryString["endDate"]), q);
}
else
{
msg = new ReportExcelHelper(Settings.Default.db).ExportHandledAttendDetail(
DateTime.Parse(Request.QueryString["startDate"]),
DateTime.Parse(Request.QueryString["endDate"]), q);
}
return(Json(msg, JsonRequestBehavior.AllowGet));
}
public ActionResult Edit(string nr)
{
IStaffService ss = new StaffService(Settings.Default.db);
Staff staff = ss.FindByNr(nr);
SetDropDownList(staff);
StaffSearchModel q = new StaffSearchModel();
try
{
q.companyId = staff.companyId;
q.departmentId = staff.departmentId;
}
catch (Exception)
{
q.companyId = null;
q.departmentId = null;
}
ViewBag.Query = q;
return(View(staff));
}
/// <summary>
/// 导出汇总表ABCD表
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="searchModel"></param>
/// <returns></returns>
public ReportMessage ExportExtraSumReport(DateTime startDate, DateTime endDate, StaffSearchModel searchModel)
{
ReportMessage msg = new ReportMessage();
string fileName = "考勤加班汇总表.xlsx";
try
{
IStaffService ss = new StaffService(this.DbString);
string tmpFile = FileHelper.CreateFullTmpFilePath(fileName, false);
msg.Content = FileHelper.GetDownloadTmpFilePath(tmpFile);
List <WorkSumExcelModel> records = new ExcelReportService(this.DbString).GetSumExcelModels(startDate, endDate, searchModel);
List <WorkSumExcelType> reportTypes = new List <WorkSumExcelType>()
{
WorkSumExcelType.Orign, WorkSumExcelType.MinusHolidayWork, WorkSumExcelType.MinusThreasholdWork, WorkSumExcelType.JobAward
};
FileInfo fileInfo = new FileInfo(tmpFile);
List <WorkAndRest> wrs = new WorkAndRestService(this.DbString).GetByDateSpan(startDate, endDate);
int OriginAttendDays = wrs.Where(s => s.IsWorkDay).Count();
using (ExcelPackage ep = new ExcelPackage(fileInfo))
{
foreach (var type in reportTypes)
{
ExcelWorksheet sheet = ep.Workbook.Worksheets.Add(EnumHelper.GetDescription(type));
List <int> holidaysColoms = new List <int>();
// 写Title
#region 写Title
// 基本信息title
#region 基本信息Title
sheet.Cells[1, 1].Value = startDate.Year;
sheet.Cells[1, 2].Value = startDate.Month;
sheet.Cells[1, 4].Value = startDate.ToString("yyyy-M-d");
sheet.Cells[1, 5].Value = endDate.ToString("yyyy-M-d");
sheet.Cells[1, 6].Value = "法定节假日";
sheet.Cells[2, 1].Value = "年";
sheet.Cells[2, 2].Value = "月";
sheet.Cells[2, 4].Value = "出勤天数";
sheet.Cells[2, 5].Value = OriginAttendDays;
sheet.Cells[2, 6].Value = "休息天数";
sheet.Cells[3, 1].Value = "工号";
sheet.Cells[3, 2].Value = "姓名";
sheet.Cells[3, 3].Value = "所属公司";
sheet.Cells[3, 4].Value = "部门";
sheet.Cells[3, 5].Value = "单位";
sheet.Cells[3, 6].Value = "职务";
sheet.Cells[3, 7].Value = "人员类别";
#endregion
// 日期title
#region 日期title
int i = 8;
for (var s = startDate; s <= endDate; s = s.AddDays(1))
{
bool isWorkday = wrs.Where(q => q.dateAt == s).FirstOrDefault().IsWorkDay;
if (!isWorkday)
{
holidaysColoms.Add(i);
}
sheet.Cells[1, i].Value = isWorkday ? "N" : "S";
sheet.Cells[2, i].Value = DateTimeHelper.GetShortDayOfWeek(s);
sheet.Cells[3, i].Value = s.Day;
i += 1;
}
#endregion
// 统计部分的title
#region 统计部分的title
sheet.Cells[3, i].Value = "延时加班工时";
sheet.Cells[3, i + 1].Value = "双休加班工时";
sheet.Cells[3, i + 2].Value = "假日加班工时";
sheet.Cells[3, i + 3].Value = "加班合计";
sheet.Cells[3, i + 4].Value = "白班";
sheet.Cells[3, i + 5].Value = "夜班";
sheet.Cells[3, i + 6].Value = "轮班费";
sheet.Cells[3, i + 7].Value = "出勤工时";
sheet.Cells[3, i + 8].Value = "事假";
sheet.Cells[3, i + 9].Value = "病假";
sheet.Cells[3, i + 10].Value = "产假";
sheet.Cells[3, i + 11].Value = "丧假";
sheet.Cells[3, i + 12].Value = "轮休";
sheet.Cells[3, i + 13].Value = "公假";
sheet.Cells[3, i + 14].Value = "年休";
sheet.Cells[3, i + 15].Value = "新进";
sheet.Cells[3, i + 16].Value = "旷工";
sheet.Cells[3, i + 17].Value = "离职";
sheet.Cells[3, i + 18].Value = "放班";
sheet.Cells[3, i + 19].Value = "延时费";
sheet.Cells[3, i + 20].Value = "双休费";
sheet.Cells[3, i + 21].Value = "节日费";
sheet.Cells[3, i + 22].Value = "加班费合计";
sheet.Cells[3, i + 23].Value = "独生子女费";
sheet.Cells[3, i + 24].Value = "病假扣款";
sheet.Cells[3, i + 25].Value = "工龄";
sheet.Cells[3, i + 26].Value = "事假扣款";
sheet.Cells[3, i + 27].Value = "漏打卡";
sheet.Cells[3, i + 28].Value = "迟到早退";
sheet.Cells[3, i + 29].Value = "用工";
sheet.Cells[3, i + 30].Value = "备注";
#endregion
#endregion
/// 写表内容
#region 写表内容
for (int j = 0; j < records.Count; j++)
{
WorkSumExcelModel record = records[j];
// 写 员工基本信息
#region 写 员工基本信息
sheet.Cells[4 + j, 1].Value = record.Staff.nr;
sheet.Cells[4 + j, 2].Value = record.Staff.name;
sheet.Cells[4 + j, 3].Value = record.Staff.companyName;
sheet.Cells[4 + j, 4].Value = record.Staff.parenDepartName;
sheet.Cells[4 + j, 5].Value = record.Staff.departmentName;
sheet.Cells[4 + j, 6].Value = record.Staff.jobTitleName;
sheet.Cells[4 + j, 7].Value = record.Staff.staffTypeName;
#endregion
// 写 日期数据
#region 写 日期数据
for (int jj = 0; jj < record.Items.Count; jj++)
{
sheet.Cells[4 + j, 8 + jj].Value = record.Items[jj].DisplayValue(type);
}
#endregion
/// 写汇总信息
#region 写汇总信息
// 加班汇总
sheet.Cells[4 + j, i].Value = record.WorkExtraHourDisplay(type);
sheet.Cells[4 + j, i + 1].Value = record.WeekendExtraHourDisplay(type);
sheet.Cells[4 + j, i + 2].Value = record.HolidayExtraHourDisplay(type);
sheet.Cells[4 + j, i + 3].Value = record.TotalExtraHourDisplay(type);
// 白班、夜班、轮班费
sheet.Cells[4 + j, i + 4].Value = string.Empty;
sheet.Cells[4 + j, i + 5].Value = string.Empty;
sheet.Cells[4 + j, i + 6].Value = string.Empty;
// 出勤工时
sheet.Cells[4 + j, i + 7].Value = record.AttendHour;
// 基本假期
sheet.Cells[4 + j, i + 8].Value = record.HolidayAbsence;
sheet.Cells[4 + j, i + 9].Value = record.SickAbsence;
sheet.Cells[4 + j, i + 10].Value = record.MaternityAbsence;
sheet.Cells[4 + j, i + 11].Value = record.FuneralAbsence;
sheet.Cells[4 + j, i + 12].Value = record.TurnAbsence;
sheet.Cells[4 + j, i + 13].Value = record.PublicAbsence;
sheet.Cells[4 + j, i + 14].Value = record.YearAbsence;
// 新进、旷工、离职、放班
sheet.Cells[4 + j, i + 15].Value = 0 - record.NewAbsence;
sheet.Cells[4 + j, i + 16].Value = string.Empty; //record.WorkAbsence;
sheet.Cells[4 + j, i + 17].Value = 0 - record.ResignAbsence;
sheet.Cells[4 + j, i + 18].Value = record.HolidayWork;
// 费用
sheet.Cells[4 + j, i + 19].Value = string.Empty; //record.WorkExtraSalary;
sheet.Cells[4 + j, i + 20].Value = string.Empty; //record.WeekendExtraSalary;
sheet.Cells[4 + j, i + 21].Value = string.Empty; //record.HolidayExtraSalary;
sheet.Cells[4 + j, i + 22].Value = string.Empty; //record.TotalExtraSalary;
sheet.Cells[4 + j, i + 23].Value = string.Empty; //record.ChildSalary;
sheet.Cells[4 + j, i + 24].Value = string.Empty; //record.SickMinSalary;
// 工龄
sheet.Cells[4 + j, i + 25].Value = string.Empty;
// 其它 事假扣款 漏打卡 迟到早退 用工 备注
sheet.Cells[4 + j, i + 26].Value = string.Empty; //record.HolidayMinSalary;
sheet.Cells[4 + j, i + 27].Value = string.Empty;
sheet.Cells[4 + j, i + 28].Value = string.Empty;
sheet.Cells[4 + j, i + 29].Value = string.Empty;
sheet.Cells[4 + j, i + 30].Value = "1";
#endregion
}
#endregion
//设置格式
#region 设置格式
foreach (var v in sheet.Cells)
{
v.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
v.AutoFitColumns();
v.Style.ShrinkToFit = true;
}
for (int r = 1; r <= sheet.Dimension.End.Row; r++)
{
// 设置节假日的背景
for (int c = 1; c <= sheet.Dimension.End.Column; c++)
{
if (holidaysColoms.Contains(c))
{
sheet.Cells[r, c].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
sheet.Cells[r, c].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Orange);
}
}
}
#endregion
}
/// 保存
ep.Save();
}
msg.Success = true;
}
catch (Exception ex)
{
LogUtil.Logger.Error(ex.Message, ex);
msg.Content = ex.Message;
}
return(msg);
}
/// <summary>
/// 导出处理过的考勤记录
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="searchModel"></param>
/// <returns></returns>
public ReportMessage ExportHandledAttendDetail(DateTime startDate, DateTime endDate, StaffSearchModel searchModel)
{
ReportMessage msg = new ReportMessage();
string fileName = "考勤记录表.xlsx";
try
{
string tmpFile = FileHelper.CreateFullTmpFilePath(fileName, false);
msg.Content = FileHelper.GetDownloadTmpFilePath(tmpFile);
FileInfo fileInfo = new FileInfo(tmpFile);
List <WorkSumExcelModel> records = new ExcelReportService(this.DbString).GetSumExcelModels(startDate, endDate, searchModel);
List <WorkAndRest> wrs = new WorkAndRestService(this.DbString).GetByDateSpan(startDate, endDate);
int OriginAttendDays = wrs.Where(s => s.IsWorkDay).Count();
List <string[]> excelRecords = new List <string[]>();
foreach (var r in records)
{
IShiftScheduleService ss = new ShiftSheduleService(this.DbString);
List <ShiftScheduleView> shifts = ss.GetShiftsByStaffAndDateSpan(r.Staff.nr, startDate, endDate);
// 每个Items代表一天
foreach (var rr in r.Items)
{
List <AttendanceRecordDetailView> atts = new List <AttendanceRecordDetailView>();
// 是否被减过加班时间
if (rr.IsMinusedHolidayWorkHour || rr.IsMinusedThresholdHour)
{
if (rr.MinuseHolidayWorkAndThresHoldLeftExtraHour == 0)
{
if (rr.WorkAndRest.IsWorkDay)
{
// 看看是不是有排班，根据排班的情况造数据，开始结束有25-45分钟的冗余，
// 就是下班到打卡设备的时间
// var shift = ss.GetFirstShiftByStaffAndDate(rr.Staff.nr, rr.DateTime);
var shift = shifts.Where(s => s.fullEndAt.Value.Date == rr.DateTime).FirstOrDefault();
if (shift == null)
{
// break;
}
else
{
DateTime startTime = shift.fullStartAt.Value.AddMinutes(0 - new Random().Next(25, 45));
DateTime endTime = shift.fullEndAt.Value.AddHours(rr.MinuseHolidayWorkAndThresHoldLeftExtraHour).AddMinutes(new Random().Next(25, 45));
if (rr.MinuseHolidayWorkAndThresHoldLeftExtraHour > 0)
{
int u = 0;
}
excelRecords.Add(new string[6] {
rr.Staff.departmentName, rr.Staff.nr, rr.Staff.name, startTime.ToString("yyyy-MM-dd"), startTime.ToString("HH:mm"), "001"
});
excelRecords.Add(new string[6] {
rr.Staff.departmentName, rr.Staff.nr, rr.Staff.name, endTime.ToString("yyyy-MM-dd"), endTime.ToString("HH:mm"), "001"
});
}
}
else
{
// 直接跳过这一天
// break;
}
}
}
else
{
// 如果没有就使用原始的出勤时间
IAttendanceRecordDetailService s = new AttendanceRecordDetailService(this.DbString);
atts = s.GetDetailsViewByStaffAndDateWithExtrawork(rr.Staff.nr, rr.DateTime);
foreach (var a in atts)
{
excelRecords.Add(new string[6] {
rr.Staff.departmentName, rr.Staff.nr, rr.Staff.name, a.recordAtDateStr, a.recordAtTimeStr, a.device
});
}
}
}
}
using (ExcelPackage ep = new ExcelPackage(fileInfo))
{
ExcelWorksheet sheet = ep.Workbook.Worksheets.Add("Sheet1");
string[] titles = new string[6] {
"机构名称", "人员编号", "姓名", "刷卡日期", "刷卡时间", "设备编号"
};
for (var i = 0; i < titles.Length; i++)
{
sheet.Cells[1, i + 1].Value = titles[i];
}
int row = 2;
foreach (var er in excelRecords)
{
for (var i = 0; i < titles.Length; i++)
{
sheet.Cells[row, i + 1].Value = er[i];
}
row += 1;
}
ep.Save();
}
msg.Success = true;
}
catch (Exception ex)
{
LogUtil.Logger.Error(ex.Message, ex);
msg.Content = ex.Message;
}
return(msg);
}
public IQueryable <Staff> Search(StaffSearchModel searchModel)
{
return(staffRep.Search(searchModel));
}
/// <summary>
/// 将Detail的数据计算到Cal中
/// 将班次结束点日期等于date日期，结束点时间小于等于date时间的班次考勤进行计算
/// </summary>
/// <param name="dateTime">计算的时间</param>
/// <param name="shiftCodes">班次代码</param>
/// <param name="searchModel">需要计算的员工的查询条件, **NOT IMPLEMENT**</param>
public void CalculateAttendRecord(DateTime dateTime, List <string> shiftCodes = null, StaffSearchModel searchModel = null)
{
DataContext dc = new DataContext(this.DbString);
/// 判断配置
SystemSetting setting = dc.Context.GetTable <SystemSetting>().FirstOrDefault();
if (setting == null)
{
throw new SystemSettingNotSetException();
}
/// 查找员工的需要被计算的排班
//IStaffService staffService = new StaffService(this.DbString);
//List<Staff> staffs = staffService.Search(searchModel).ToList();
var allShiftShedulesQ = dc.Context.GetTable <ShiftScheduleView>().Where(s => s.fullEndAt.Value <= dateTime && s.fullEndAt.Value.Date.Equals(dateTime.Date));
if (shiftCodes != null && shiftCodes.Count > 0)
{
if (shiftCodes.Count > 1)
{
allShiftShedulesQ = allShiftShedulesQ.Where(s => shiftCodes.Contains(s.code));
}
else
{
allShiftShedulesQ = allShiftShedulesQ.Where(s => s.code.Equals(shiftCodes.First()));
}
}
List <ShiftScheduleView> allShiftShedules = allShiftShedulesQ.ToList();
Dictionary <string, List <ShiftScheduleView> > allStaffShitSchedules = new Dictionary <string, List <ShiftScheduleView> >();
foreach (var ssv in allShiftShedules)
{
if (!allStaffShitSchedules.ContainsKey(ssv.staffNr))
{
allStaffShitSchedules.Add(ssv.staffNr, new List <ShiftScheduleView>());
}
allStaffShitSchedules[ssv.staffNr].Add(ssv);
}
Dictionary <string, List <AttendanceRecordCal> > staffAttendCals = new Dictionary <string, List <AttendanceRecordCal> >();
/// 循环每一个员工
foreach (var staffShiftShedule in allStaffShitSchedules)
{
Dictionary <DateTime, double> totalWorkingHours = new Dictionary <DateTime, double>();
Dictionary <DateTime, List <AttendanceExceptionType> > exceptions = new Dictionary <DateTime, List <AttendanceExceptionType> >();
// 获取今日、昨日的所有排班
List <ShiftScheduleView> staffShitSchedules = staffShiftShedule.Value; // dc.Context.GetTable<ShiftScheduleView>()
// .Where(s => s.staffNr.Equals(staff.nr) && (s.scheduleAt.Date.Equals(date.Date) || (s.scheduleAt.Equals(date.Date.AddDays(-1)) && s.shiftType.Equals((int)ShiftType.Tommorrow)))).ToList();
if (staffShitSchedules.Count == 0)
{
//TODO UPDATE OR CREATE
//AttendanceRecordCal recordCal = new AttendanceRecordCal() { staffNr = staff.nr, actWorkingHour = 0, oriWorkingHour = 0, attendanceDate = date, createdAt = DateTime.Now, isManualCal = false, remark = "没有排班" };
//recordCal.actWorkingHour = recordCal.oriWorkingHour = 0;
/// 如果没有排班则跳过, 写入出勤时间为0 ?
break;
}
foreach (var shift in staffShitSchedules)
{
DateTime shiftDate = shift.fullStartAt.Value.Date;// dateTime.Date.AddDays(shift.shiftType.Equals((int)ShiftType.Today) ? 0 : -1);
if (!totalWorkingHours.ContainsKey(shiftDate))
{
totalWorkingHours.Add(shiftDate, 0);
}
exceptions[shiftDate] = new List <AttendanceExceptionType>();
// DateTime shiftStart = shift.scheduleAt.AddDays(shift.shiftType.Equals((int)ShiftType.Today) ? 0 : -1).Add(shift.startAt);
DateTime shiftStart = shift.fullStartAt.Value; //shift.scheduleAt.Add(shift.startAt);
DateTime shiftEnd = shift.fullEndAt.Value; //shift.scheduleAt.AddDays(shift.shiftType.Equals((int)ShiftType.Today) ? 0 : 1).Add(shift.endAt);
DateTime sq = shiftStart.AddMinutes(0 - setting.validAttendanceRecordTime.Value);
DateTime eq = shiftEnd.AddMinutes(setting.validAttendanceRecordTime.Value);
List <AttendanceRecordDetail> records = dc.Context.GetTable <AttendanceRecordDetail>().Where(s => s.staffNr.Equals(staffShiftShedule.Key) && s.recordAt >= sq && s.recordAt <= eq).OrderBy(s => s.recordAt).ToList();
//staff.AttendanceRecordDetail.Where(s => s.recordAt >= sq && s.recordAt <= eq).ToList().OrderBy(s => s.recordAt).ToList();
if (records.Count == 0)
{
/// TODO UPDATE OR CREATE
/// 创建异常，班次旷工，考勤的时间为 0
exceptions[shiftDate].Add(AttendanceExceptionType.ShiftAbsence);
}
else
{
/// 清洗短时间内的重复打卡数据
MarkRepeatRecord(records, (float)setting.repeatAttendanceRecordTime.Value);
List <AttendanceRecordDetail> validRecrods = records.Where(s => s.isRepeatedData == false).ToList();
if (validRecrods.Count == 1)
{
/// TODO UPDATE OR CREATE
/// 创建异常，考勤记录不完整，考勤的时间为 0
exceptions[shiftDate].Add(AttendanceExceptionType.MessRecord);
}
else
{
// 判断迟到
DateTime firstAt = validRecrods.First().recordAt;
if (firstAt.AddMinutes(0 - setting.lateExceptionTime.Value) > shiftStart)
{
/// TODO UPDATE OR CREATE
/// 创建异常，迟到
exceptions[shiftDate].Add(AttendanceExceptionType.Late);
}
DateTime lastAt = validRecrods.Last().recordAt;
if (lastAt.AddMinutes(setting.earlyLeaveExceptionTime.Value) < shiftEnd)
{
/// TODO UPDATE OR CREATE
/// 创建早退，迟到
exceptions[shiftDate].Add(AttendanceExceptionType.EarlyLeave);
}
if (validRecrods.Count % 2 == 1)
{
/// TODO UPDATE OR CREATE
/// 创建异常，考勤记录不完整，考勤的时间为 0
exceptions[shiftDate].Add(AttendanceExceptionType.MessRecord);
}
for (int i = 0; i < validRecrods.Count - 1; i += 2)
{
totalWorkingHours[shiftDate] += (validRecrods[i + 1].recordAt - validRecrods[i].recordAt).TotalHours;
}
}
}
}
staffAttendCals.Add(staffShiftShedule.Key, new List <AttendanceRecordCal>());
// 创建, 计算过的出勤时间
foreach (var dic in totalWorkingHours)
{
AttendanceRecordCal cal = new AttendanceRecordCal()
{
attendanceExceptions = exceptions[dic.Key],
createdAt = DateTime.Now,
oriWorkingHour = Math.Round(dic.Value, 2),
actWorkingHour = Math.Round(dic.Value, 2),
staffNr = staffShiftShedule.Key,
attendanceDate = dic.Key
};
staffAttendCals[staffShiftShedule.Key].Add(cal);
}
}
//using (TransactionScope scope = new TransactionScope())
//{
DataContext subDc = new DataContext(this.DbString);
List <AttendanceRecordCal> insertCals = new List <AttendanceRecordCal>();
foreach (var dic in staffAttendCals)
{
// string nrsq = string.Format(",{0},", string.Join(",", staffAttendCals.Keys));
// string dateq = string.Format(",{0},", string.Join(",", staffAttendCals[dic.Key].Select(ss => ss.attendanceDate.ToString("yyyy-MM-dd")).ToList()));
/// 手动修改的不会被修改实际值
//List<AttendanceRecordCal> _updateCals = subDc.Context.GetTable<AttendanceRecordCal>()
// .Where(s => staffAttendCals.Keys.Contains(s.staffNr)
// && (staffAttendCals[dic.Key].Select(ss => ss.attendanceDate).ToList()
// .Contains(s.attendanceDate))).ToList();
//List<AttendanceRecordCal> _updateCals = subDc.Context.GetTable<AttendanceRecordCal>()
// .AsEnumerable()
//.Join(staffAttendCals.Keys,s=>s.staffNr,ci=>ci, (s,ci)=> s)
//.Join(staffAttendCals[dic.Key].Select(ss => ss.attendanceDate).ToList(),sss=>sss.attendanceDate,cci=>cci,(sss,cci)=>sss).ToList();
List <AttendanceRecordCal> _updateCals = new List <AttendanceRecordCal>();
// IQueryable<AttendanceRecordCal> _updateCalsQ = dc.Context.GetTable<AttendanceRecordCal>()
//.Where(s => nrsq.IndexOf("," + s.staffNr + ",") != -1);
IQueryable <AttendanceRecordCal> _updateCalsQ = dc.Context.GetTable <AttendanceRecordCal>()
.Where(s => s.staffNr.Equals(dic.Key));
if (staffAttendCals[dic.Key].Count == 0)
{
}
else if (staffAttendCals[dic.Key].Count == 1)
{
_updateCals = _updateCalsQ.Where(s => s.attendanceDate.Equals(staffAttendCals[dic.Key].First().attendanceDate)).ToList();
}
else
{
_updateCals = _updateCalsQ.Where(s => staffAttendCals[dic.Key].Select(ss => ss.attendanceDate).ToList().Contains(s.attendanceDate)).ToList();
}
//.Where(ss => dateq.IndexOf("," + ss.attendanceDate.ToString("yyyy-MM-dd") + ",") != -1).ToList();
foreach (var u in _updateCals)
{
var c = dic.Value.Where(d => d.attendanceDate.Equals(u.attendanceDate)).FirstOrDefault();
if (c != null)
{
u.oriWorkingHour = u.actWorkingHour = c.oriWorkingHour;
u.isManualCal = false;
if (c != null)
{
u.oriWorkingHour = c.oriWorkingHour;
if (u.isManualCal == false)
{
u.actWorkingHour = c.oriWorkingHour;
u.isManualCal = false;
}
u.createdAt = DateTime.Now;
u.isException = c.isException;
u.exceptionCodes = c.exceptionCodes;
}
}
}
List <AttendanceRecordCal> _insertCals = dic.Value.Where(s => !_updateCals.Select(ss => ss.attendanceDate).Contains(s.attendanceDate)).ToList();
insertCals.AddRange(_insertCals);
/// scope 完成
// scope.Complete();
// }
}
subDc.Context.GetTable <AttendanceRecordCal>().InsertAllOnSubmit(insertCals);
subDc.Context.SubmitChanges();
}
public void CalculateAttendRecordWithExtrawork(DateTime datetime, List <string> shiftCodes = null, StaffSearchModel searchModel = null)
{
// 是否是休息日
IWorkAndRestService wars = new WorkAndRestService(this.DbString);
WorkAndRest wr = wars.FindByDate(datetime);
bool isRestDay = wars.IsRestDay(wr);
SystemSetting setting = new SystemSettingService(this.DbString).Find();
DataContext dc = new DataContext(this.DbString);
// 计算在职人员的考勤
List <Staff> staffs = dc.Context.GetTable <Staff>().Where(s => (s.workStatus == (int)WorkStatus.OnWork) || (s.workStatus == (int)WorkStatus.OffWork && s.resignAt >= datetime)).ToList();
foreach (Staff staff in staffs)
{
double workdayHour = 0;
double extraHour = 0;
// 异常情况
List <AttendanceExceptionType> exceptions = new List <AttendanceExceptionType>();
if (staff.nr.Equals("201471"))
{
string s = "201471";
}
// 获取已经结束的排班
List <ShiftScheduleView> shifts = dc.Context.GetTable <ShiftScheduleView>().Where(s => s.staffNr.Equals(staff.nr) && s.fullStartAt.Value.Date == datetime.Date && s.fullEndAt <= DateTime.Now).ToList();
if (shifts.Count > 0)
{
foreach (var shift in shifts)
{
DateTime validSQ = shift.fullStartAt.Value.AddMinutes(0 - setting.validAttendanceRecordTime.Value);
DateTime validEQ = shift.fullEndAt.Value.AddMinutes(0 + setting.validAttendanceRecordTime.Value);
DateTime sq = validSQ;
DateTime eq = shift.shiftType == (int)ShiftType.Today ? datetime.Date.AddDays(1).AddHours(8) : datetime.Date.AddDays(1).AddHours(23);
ShiftScheduleView nextShift = new ShiftSheduleService(this.DbString).GetNextShiftScheduleView(staff.nr, shift.fullEndAt.Value);
if (nextShift != null)
{
if ((nextShift.fullStartAt.Value.Date - shift.fullStartAt.Value.Date).TotalDays < 2)
{
eq = nextShift.fullStartAt.Value.AddMinutes(0 - setting.validAttendanceRecordTime.Value);
}
}
List <AttendanceRecordDetail> records = new AttendanceRecordDetailService(this.DbString).GetByStaffAndTimespan(staff.nr, sq, eq);
if (records.Count == 0)
{
// 旷工，可能他有请假。。。
exceptions.Add(AttendanceExceptionType.Absence);
}
else
{
/// 清洗数据
MarkRepeatRecordByMinute(records, (float)setting.repeatAttendanceRecordTime.Value);
records = records.Where(s => s.isRepeatedData == false).ToList();
// 如果考勤记录不配对，如 进-出 的偶数被，则是打开记录不完整
// 但是这不影响计算
if (records.Count % 2 != 0)
{
exceptions.Add(AttendanceExceptionType.MessRecord);
}
/// 开始计算
/// 早到的使用排班开始时间，晚到的算迟到，提早走的算早退
/// 此处不涉及到多次进出-计算目前只能算出大概，很难精确
/// 打卡迟了肯定算迟到
/// 如果早退了一般不会来加班了
// 是否有早打卡？
// int hasEarlyCount= records.Where(s => s.recordAt < shift.fullStartAt.Value).Count();
if (records.Count == 1)
{
exceptions.Add(AttendanceExceptionType.MessRecord);
/// 以下都是推测，都是要人为的调整的
DateTime recordAt = records[0].recordAt;
// 如果是早来，迟走，可能是忘记打卡了
if (recordAt < shift.fullStartAt.Value || (records.Last().recordAt >= shift.fullEndAt.Value && recordAt <= validEQ))
{
/// 如果早来超过一个小时，可能是加班
if (recordAt <= shift.fullStartAt.Value.AddHours(-1))
{
extraHour = (shift.fullStartAt.Value - recordAt).TotalHours;
}
workdayHour = (shift.fullEndAt.Value - shift.fullStartAt.Value).TotalHours;
}// 如果迟走超过一个小时，可能是加班，即使此处误判，也需要加班单存在的
else if (recordAt >= shift.fullEndAt.Value.AddHours(1))
{
exceptions.Add(AttendanceExceptionType.ExtraWork);
workdayHour = (shift.fullEndAt.Value - shift.fullStartAt.Value).TotalHours;
extraHour = (recordAt - shift.fullEndAt.Value).TotalHours;
}
else
{
// 如果只有一次打卡记录在应该的工作区间内，则需要人工手动调整了！
}
}
else
{
DateTime firstD = records.First().recordAt;
DateTime lastD = records.Last().recordAt;
// 正常上班，无加班
// 1. 如果走时间在排班结束范围外内，则算整个排班时间
if (lastD >= shift.fullEndAt.Value &&
lastD <= validEQ)
{
if (firstD <= shift.fullStartAt)
{
/// 如果早来超过一个小时，可能是加班
if (firstD <= shift.fullStartAt.Value.AddHours(-1))
{
extraHour = (shift.fullStartAt.Value - firstD).TotalHours;
}
// 正常
workdayHour = (shift.fullEndAt.Value - shift.fullStartAt.Value).TotalHours;
}
else
{
// 迟到
exceptions.Add(AttendanceExceptionType.Late);
workdayHour = (shift.fullEndAt.Value - firstD).TotalHours;
}
}
else
{
if (lastD < shift.fullEndAt.Value)
{
// 早退
exceptions.Add(AttendanceExceptionType.EarlyLeave);
if (firstD < shift.fullStartAt)
{
/// 如果早来超过一个小时，可能是加班
if (firstD <= shift.fullStartAt.Value.AddHours(-1))
{
extraHour = (shift.fullStartAt.Value - firstD).TotalHours;
}
workdayHour = (lastD - shift.fullStartAt.Value).TotalHours;
}
else
{
// 迟到+早退
exceptions.Add(AttendanceExceptionType.Late);
workdayHour = (lastD - firstD).TotalHours;
}
}
else
{
if (lastD >= shift.fullEndAt.Value.AddHours(1))
{
exceptions.Add(AttendanceExceptionType.ExtraWork);
workdayHour = (shift.fullEndAt.Value - shift.fullStartAt.Value).TotalHours;
extraHour = (lastD - shift.fullEndAt.Value).TotalHours;
}
}
}
}
}
}
}
else
{
// 如果没有排班怎么计算？
// 如果没有排班，应该都是加班
// TODO, 根据目前和德晋HR了解情况，会排班，所以这种情况暂时不考虑
}
/// 暂时对没有排班的不处理
if (shifts.Count > 0)
{
// 基本工时都是 8 小时，不分部门和职称
if (workdayHour > 8)
{
workdayHour = 8;
}
/// 如果是双休或节假日都算加班！无论是否有排班
if (isRestDay)
{
extraHour += workdayHour;
workdayHour = 0;
}
extraHour = Math.Round(extraHour, 1);
workdayHour = Math.Round(workdayHour, 1);
// 如果是成型课 或者 行政课的司机，则加班不减0.5h，其它的都减
if (extraHour > 0)
{
exceptions.Add(AttendanceExceptionType.ExtraWork);
if (staff.IsMinusExtraWorkHour)
{
extraHour -= 0.5;
if (extraHour < 0)
{
extraHour = 0;
}
}
ExtraWorkRecord extraRecord = new ExtraWorkRecordService(this.DbString).FindByStaffNrAndDete(staff.nr, datetime.Date);
if (extraRecord == null)
{
exceptions.Add(AttendanceExceptionType.ExtraWorkNoRecord);
}
else
{
if (extraHour != extraRecord.duration)
{
exceptions.Add(AttendanceExceptionType.ExtraWorkHourNotMatch);
}
if (wr.dateType != extraRecord.ExtraWorkType.systemCode)
{
exceptions.Add(AttendanceExceptionType.ExtraWorkTypeNotMatch);
}
}
}
DataContext comitDC = new DataContext(this.DbString);
AttendanceRecordCal calRecord = comitDC.Context.GetTable <AttendanceRecordCal>().FirstOrDefault(s => s.staffNr.Equals(staff.nr) && s.attendanceDate.Equals(datetime.Date));
exceptions = exceptions.Distinct().ToList();
int?extraType = extraHour == 0 ? null : wr.dateType;
if (calRecord == null)
{
comitDC.Context.GetTable <AttendanceRecordCal>().InsertOnSubmit(new AttendanceRecordCal()
{
staffNr = staff.nr,
attendanceDate = datetime.Date,
oriWorkingHour = workdayHour,
actWorkingHour = workdayHour,
oriExtraWorkingHour = extraHour,
actExtraWorkingHour = extraHour,
extraworkType = extraType,
attendanceExceptions = exceptions.Distinct().ToList(),
createdAt = DateTime.Now
});
}
else
{
calRecord.extraworkType = extraType;
calRecord.oriWorkingHour = calRecord.actWorkingHour = workdayHour;
calRecord.oriExtraWorkingHour = calRecord.actExtraWorkingHour = extraHour;
calRecord.createdAt = DateTime.Now;
calRecord.attendanceExceptions = exceptions;
}
comitDC.Context.SubmitChanges();
}
}
}
static void Main(string[] args)
{
ReportExcelHelper helper = new ReportExcelHelper(Settings.Default.db);
StaffSearchModel searchModel = new StaffSearchModel();
searchModel.StaffNrs = new List <string>()
{
"200268", "200835", "202274", "202348", "202487"
};
//ReportMessage msg= helper.ExportExtraSumReport(DateTime.Parse("2016-8-1"), DateTime.Parse("2016-8-31"),searchModel);
ReportMessage msg = helper.ExportHandledAttendDetail(DateTime.Parse("2016-8-1"), DateTime.Parse("2016-8-31"), searchModel);
Console.WriteLine(msg.Success);
Console.WriteLine(msg.Content);
//Console.WriteLine(DateTime.Now.DayOfWeek.ToString());
//DateTime st = DateTime.Parse("2016-9-1");
//DateTime et = DateTime.Parse("2016-9-2 1:00");
//Console.WriteLine((et - st).TotalDays);
//Console.WriteLine(new TimeSpan(7, 8, 0).ToString("hh\\:mm"));
//Staff s = new Staff();
//s.PropertyChanging += S_PropertyChanging;
//s.PropertyChanged += S_PropertyChanged;
//s.address = "a";
//s.address = "b";
//DataContext dc = new DataContext(Settings.Default.db);
//List<string> staffNrs = new List<string>();
//List<DateTime> datetime = new List<DateTime>();
//for(int i = 0; i < 3000; i++)
//{
// staffNrs.Add(i.ToString()+"n");
// datetime.Add(DateTime.Now.AddDays(0 - i).Date);
//}
//string nrsq = string.Format(",{0},", string.Join(",", staffNrs));
//string dateq = string.Format(",{0},", string.Join(",", datetime.Select(ss => ss.ToString("yyyy-MM-dd")).ToList()));
//IQueryable<AttendanceRecordCal> cq = dc.Context.GetTable<AttendanceRecordCal>()
// .Where(s => nrsq.IndexOf("," + s.staffNr + ",") != -1)
// .Where(ss=> dateq.IndexOf("," + ss.attendanceDate.ToString("yyyy-MM-dd") + ",")!=-1).Take(20);
////List<AttendanceRecordCal> _updateCals = dc.Context.GetTable<AttendanceRecordCal>()
//// .AsEnumerable()
//// .Join(staffNrs, s => s.staffNr, ci => ci, (s, ci) => s)
//// .Join(datetime, sss => sss.attendanceDate, cci => cci, (sss, cci) => sss).ToList();
//// List<AttendanceRecordDetailView> _updateCals = dc.Context.GetTable<AttendanceRecordDetailView>()
//// .AsEnumerable()
//// .Join(staffNrs, s => s.staffNr, ci => ci, (s, ci) => s)
////.Join(datetime, sss => sss.recordAtDate, cci => cci, (sss, cci) => sss)
//// .ToList();
//IQueryable<AttendanceRecordDetailView> q = dc.Context.GetTable<AttendanceRecordDetailView>()
// .Where(s => ("," + string.Join(",", staffNrs.ToArray()) + ",").IndexOf("," + s.staffNr + ",") != -1).Take(20);
//List<AttendanceRecordDetailView> li = q.ToList();
Console.Read();
//try
//{
// int.Parse("&*^Df");
//}
//catch (Exception ex)
//{
// LogUtil.Logger.Error("服务运行时出错", ex);
//}
//Console.WriteLine(" 1. create staff \n 2.create shiftshedule\n 3.create record\n 4.calcualte staff attendance \n 5. PRESS q to quit.");
//string ke = Console.ReadLine();
//while (!ke.Equals("q"))
//{
// Cmd(ke);
// ke = Console.ReadLine();
//}
//Console.WriteLine("quiting....press ENTER....");
//Console.Read();
}
/// <summary>
///
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="searchModel"></param>
/// <param name="maxExtraTotalHour"></param>
/// <returns></returns>
public List <WorkSumExcelModel> GetSumExcelModels(DateTime startDate, DateTime endDate, StaffSearchModel searchModel, double maxExtraTotalHour = 36)
{
List <WorkSumExcelModel> records = new List <WorkSumExcelModel>();
DataContext dc = new DataContext(this.DbString);
IQueryable <StaffView> staffQ = dc.Context.GetTable <StaffView>();
IQueryable <AttendanceRecordCalView> attQ = dc.Context.GetTable <AttendanceRecordCalView>();
IQueryable <AbsenceRecordView> abQ = dc.Context.GetTable <AbsenceRecordView>();
staffQ = staffQ.Where(s => (s.workStatus == (int)WorkStatus.OnWork) ||
(s.workStatus == (int)WorkStatus.OffWork && (s.resignAt == null || s.resignAt >= startDate)));
attQ = attQ.Where(s => (s.workStatus == (int)WorkStatus.OnWork) ||
(s.workStatus == (int)WorkStatus.OffWork && (s.resignAt == null || s.resignAt >= startDate)));
abQ = abQ.Where(s => (s.workStatus == (int)WorkStatus.OnWork) ||
(s.workStatus == (int)WorkStatus.OffWork && (s.resignAt == null || s.resignAt >= startDate)));
if (searchModel.companyId.HasValue)
{
staffQ = staffQ.Where(s => s.companyId == searchModel.companyId.Value);
attQ = attQ.Where(s => s.companyId == searchModel.companyId.Value);
abQ = abQ.Where(s => s.companyId == searchModel.companyId.Value);
}
if (searchModel.departmentId.HasValue)
{
staffQ = staffQ.Where(s => s.departmentId == searchModel.departmentId.Value);
attQ = attQ.Where(s => s.departmentId == searchModel.departmentId.Value);
abQ = abQ.Where(s => s.departmentId == searchModel.departmentId.Value);
}
if (searchModel.StaffNrs != null && searchModel.StaffNrs.Count > 0)
{
staffQ = staffQ.Where(s => searchModel.StaffNrs.Contains(s.nr));
attQ = attQ.Where(s => searchModel.StaffNrs.Contains(s.nr));
abQ = abQ.Where(s => searchModel.StaffNrs.Contains(s.nr));
}
attQ = attQ.Where(s => s.attendanceDate >= startDate && s.attendanceDate <= endDate);
abQ = abQ.Where(s => s.absenceDate >= startDate && s.absenceDate <= endDate);
List <StaffView> staffs = staffQ.ToList();
List <AttendanceRecordCalView> attends = attQ.ToList();
List <AbsenceRecordView> abs = abQ.ToList();
List <WorkAndRest> wrs = new WorkAndRestService(this.DbString).GetByDateSpan(startDate, endDate);
int OriginAttendDays = wrs.Where(s => s.IsWorkDay).Count();
double OriginAttendHours = OriginAttendDays * WORK_DAY_HOUR;
// 初始化系统预定义缺勤类型
List <AbsenceType> abTypes = new AbsenceTypeService(this.DbString).GetSystemTypes();
Dictionary <SystemAbsenceType, int> abTypesDic = new Dictionary <SystemAbsenceType, int>();
foreach (var abT in abTypes)
{
abTypesDic.Add((SystemAbsenceType)abT.systemCode.Value, abT.id);
}
foreach (var staff in staffs)
{
WorkSumExcelModel sumModel = new WorkSumExcelModel()
{
Staff = staff, Items = new List <WorkSumExcelItemModel>()
};
for (var date = startDate; date <= endDate; date = date.AddDays(1))
{
AttendanceRecordCalView at = attends.FirstOrDefault(s => s.staffNr.Equals(staff.nr) && s.attendanceDate == date);
AbsenceRecordView ab = abs.FirstOrDefault(s => s.staffNr.Equals(staff.nr) && s.absenceDate == date);
WorkAndRest wr = wrs.FirstOrDefault(s => s.dateAt == date);
sumModel.Items.Add(new WorkSumExcelItemModel()
{
Id = Guid.NewGuid().ToString(), Staff = staff, DateTime = date, WorkAndRest = wr, AttendanceRecordCal = at, AbsenceRecord = ab
});
}
IEnumerable <AttendanceRecordCalView> baseATQ = attends.Where(s => s.staffNr.Equals(staff.nr));
IEnumerable <AbsenceRecordView> baseABQ = abs.Where(s => s.staffNr.Equals(staff.nr));
// 节假日总时间的统计
#region 节假日总时间的统计
// 放班- -
double?HolidayWork = sumModel.Items.Where(s => s.AbsenceRecord != null && s.AbsenceRecord.absenceTypeId == abTypesDic[SystemAbsenceType.HolidayWork]).Sum(s => s.AbsenceRecord.duration);
sumModel.HolidayWork = HolidayWork.HasValue ? HolidayWork.Value : 0;
double?HolidayAbsence = sumModel.Items.Where(s => s.AbsenceRecord != null && s.AbsenceRecord.absenceTypeId == abTypesDic[SystemAbsenceType.HolidayAbsence]).Sum(s => s.AbsenceRecord.duration);
sumModel.HolidayAbsence = HolidayAbsence.HasValue ? HolidayAbsence.Value : 0;
double?SickAbsence = sumModel.Items.Where(s => s.AbsenceRecord != null && s.AbsenceRecord.absenceTypeId == abTypesDic[SystemAbsenceType.SickAbsence]).Sum(s => s.AbsenceRecord.duration);
sumModel.SickAbsence = SickAbsence.HasValue ? SickAbsence.Value : 0;
double?MaternityAbsence = sumModel.Items.Where(s => s.AbsenceRecord != null && s.AbsenceRecord.absenceTypeId == abTypesDic[SystemAbsenceType.MaternityAbsence]).Sum(s => s.AbsenceRecord.duration);
sumModel.MaternityAbsence = MaternityAbsence.HasValue ? MaternityAbsence.Value : 0;
double?FuneralAbsence = sumModel.Items.Where(s => s.AbsenceRecord != null && s.AbsenceRecord.absenceTypeId == abTypesDic[SystemAbsenceType.FuneralAbsence]).Sum(s => s.AbsenceRecord.duration);
sumModel.FuneralAbsence = FuneralAbsence.HasValue ? FuneralAbsence.Value : 0;
double?TurnAbsence = sumModel.Items.Where(s => s.AbsenceRecord != null && s.AbsenceRecord.absenceTypeId == abTypesDic[SystemAbsenceType.TurnAbsence]).Sum(s => s.AbsenceRecord.duration);
sumModel.TurnAbsence = TurnAbsence.HasValue ? TurnAbsence.Value : 0;
double?PublicAbsence = sumModel.Items.Where(s => s.AbsenceRecord != null && s.AbsenceRecord.absenceTypeId == abTypesDic[SystemAbsenceType.PublicAbsence]).Sum(s => s.AbsenceRecord.duration);
sumModel.PublicAbsence = PublicAbsence.HasValue ? PublicAbsence.Value : 0;
double?YearAbsence = sumModel.Items.Where(s => s.AbsenceRecord != null && s.AbsenceRecord.absenceTypeId == abTypesDic[SystemAbsenceType.YearAbsence]).Sum(s => s.AbsenceRecord.duration);
sumModel.YearAbsence = YearAbsence.HasValue ? YearAbsence.Value : 0;
double?WorkAbsence = sumModel.Items.Where(s => s.AbsenceRecord != null && s.AbsenceRecord.absenceTypeId == abTypesDic[SystemAbsenceType.WorkAbsence]).Sum(s => s.AbsenceRecord.duration);
sumModel.WorkAbsence = WorkAbsence.HasValue ? WorkAbsence.Value : 0;
// 新进
sumModel.NewAbsence = sumModel.Items.Where(s => s.IsNew == true && s.WorkAndRest.IsWorkDay == true).Count() * WORK_DAY_HOUR;
// 离职
sumModel.ResignAbsence = sumModel.Items.Where(s => s.IsResign == true && s.WorkAndRest.IsWorkDay == true).Count() * WORK_DAY_HOUR;
#endregion
// 出勤工时
#region 出勤工时
sumModel.AttendHour = OriginAttendHours - sumModel.Items.Where(s => s.AbsenceRecord != null && abTypesDic.Where(ss => ss.Key != SystemAbsenceType.HolidayWork).Select(ss => ss.Value).Contains(s.AbsenceRecord.absenceTypeId)).Sum(s => s.AbsenceRecord.duration) - sumModel.NewAbsence - sumModel.ResignAbsence;
#endregion
// 原始记录的加班统计
#region 原始记录的加班统计
double?WorkExtraHour = baseATQ.Where(s => s.extraworkType == (int)SystemExtraType.WorkExtra).Sum(s => s.actExtraWorkingHour);
sumModel.OriWorkExtraHour = WorkExtraHour.HasValue ? WorkExtraHour.Value : 0;
double?WeekendExtraHour = baseATQ.Where(s => s.extraworkType == (int)SystemExtraType.WeekendExtra).Sum(s => s.actExtraWorkingHour);
sumModel.OriWeekendExtraHour = WeekendExtraHour.HasValue ? WeekendExtraHour.Value : 0;
double?HolidayExtraHour = baseATQ.Where(s => s.extraworkType == (int)SystemExtraType.HolidayExtra).Sum(s => s.actExtraWorkingHour);
sumModel.OriHolidayExtraHour = HolidayExtraHour.HasValue ? HolidayExtraHour.Value : 0;
#endregion
// 计算统计！！！！
#region
// 计算减去放班的加班总计
// 计算A->B，减去放班的统计 PlanA, 可以根据其他的总计，舍弃这个这个方式
//#region 计算A->B，减去放班的统计
//double workHoliday = sumModel.HolidayWork;
//if (workHoliday >= 0)
//{
// if (sumModel.OriHolidayExtraHour >= workHoliday)
// {
// sumModel.MinusedHolidayWorkHolidayLeftExtraHour = sumModel.OriHolidayExtraHour - workHoliday;
// workHoliday = 0;
// }
// else
// {
// sumModel.MinusedHolidayWorkHolidayLeftExtraHour = 0;
// workHoliday = workHoliday - sumModel.OriHolidayExtraHour;
// }
// if (workHoliday >= 0)
// {
// if (sumModel.OriWeekendExtraHour >= workHoliday)
// {
// sumModel.MinusedHolidayWorkWeekendLeftExtraHour = sumModel.OriWeekendExtraHour - workHoliday;
// workHoliday = 0;
// }
// else
// {
// sumModel.MinusedHolidayWorkWeekendLeftExtraHour = 0;
// workHoliday = workHoliday - sumModel.OriWeekendExtraHour;
// }
// if (workHoliday >= 0)
// {
// if (sumModel.OriWorkExtraHour >= workHoliday)
// {
// sumModel.MinusedHolidayWorkWorkLeftExtraHour = sumModel.OriWorkExtraHour - workHoliday;
// workHoliday = 0;
// }
// else
// {
// sumModel.MinusedHolidayWorkWorkLeftExtraHour = 0;
// workHoliday = workHoliday - sumModel.OriWorkExtraHour;
// }
// }
// }
//}
//#endregion
// 计算
// 分别计算单个的 去掉放班的时间
#region 分别计算单个的 去掉放班 的时间
if (sumModel.HolidayWork > 0)
{
double factor = sumModel.HolidayWork;
// 先将 Items 排序，
// 按 type 降序，即 节假日、双休、平时，
// 按 加班量 降序
var q = sumModel.Items.Where(s => s.AttendanceRecordCal != null)
.OrderByDescending(s => s.DateTime)
// .OrderByDescending(s => s.AttendanceRecordCal.actExtraWorkingHour)
.OrderByDescending(s => s.AttendanceRecordCal.extraworkType);
List <WorkSumExcelItemModel> sortedItems = q.ToList();
foreach (var item in sortedItems)
{
if (factor <= 0)
{
sumModel.Items.FirstOrDefault(s => s.Id == item.Id).MinusedHolidayWorkHour = 0;
}
else
{
if (factor <= item.AttendanceRecordCal.actExtraWorkingHour)
{
double xfactor = item.AttendanceRecordCal.actExtraWorkingHour.Value - factor;
if (xfactor >= 4)
{
sumModel.Items.FirstOrDefault(s => s.Id == item.Id).MinusedHolidayWorkHour = factor;
}
else
{
sumModel.Items.FirstOrDefault(s => s.Id == item.Id).MinusedHolidayWorkHour = item.AttendanceRecordCal.actExtraWorkingHour.Value;
}
factor = 0;
}
else
{
sumModel.Items.FirstOrDefault(s => s.Id == item.Id).MinusedHolidayWorkHour = item.AttendanceRecordCal.actExtraWorkingHour.Value;
factor = factor - item.AttendanceRecordCal.actExtraWorkingHour.Value;
}
}
}
}
#endregion
// 计算减去放班的加班总计
// 计算A->B，减去放班的统计 PlanB
#region 计算A->B，减去放班的统计
double?MinusedHolidayWorkWorkLeftExtraHour = sumModel.Items.Where(s => s.AttendanceRecordCal != null && s.AttendanceRecordCal.extraworkType == (int)SystemExtraType.WorkExtra).Sum(s => s.MinuseHolidayWorkLeftExtraHour);
sumModel.MinusedHolidayWorkWorkLeftExtraHour = MinusedHolidayWorkWorkLeftExtraHour.HasValue ? MinusedHolidayWorkWorkLeftExtraHour.Value : 0;
double?MinusedHolidayWorkWeekendLeftExtraHour = sumModel.Items.Where(s => s.AttendanceRecordCal != null && s.AttendanceRecordCal.extraworkType == (int)SystemExtraType.WeekendExtra).Sum(s => s.MinuseHolidayWorkLeftExtraHour);
sumModel.MinusedHolidayWorkWeekendLeftExtraHour = MinusedHolidayWorkWeekendLeftExtraHour.HasValue ? MinusedHolidayWorkWeekendLeftExtraHour.Value : 0;
double?MinusedHolidayWorkHolidayLeftExtraHour = sumModel.Items.Where(s => s.AttendanceRecordCal != null && s.AttendanceRecordCal.extraworkType == (int)SystemExtraType.HolidayExtra).Sum(s => s.MinuseHolidayWorkLeftExtraHour);
sumModel.MinusedHolidayWorkHolidayLeftExtraHour = MinusedHolidayWorkHolidayLeftExtraHour.HasValue ? MinusedHolidayWorkHolidayLeftExtraHour.Value : 0;
#endregion
// 分别计算单个的 去掉放班的时间
#region 分别计算单个的 打于maxExtraTotalHour 的时间
if (sumModel.MinusedHolidayLeftTotalExtraHour > maxExtraTotalHour)
{
double factor = sumModel.MinusedHolidayLeftTotalExtraHour - maxExtraTotalHour;
// 先将 Items 排序，
// 按 type 降序，即 节假日、双休、平时，
// 按 加班量减掉放班的剩余量 降序
var q = sumModel.Items.Where(s => s.AttendanceRecordCal != null)
//.OrderBy(s => s.DateTime)
.OrderByDescending(s => s.MinuseHolidayWorkLeftExtraHour)
.OrderByDescending(s => s.AttendanceRecordCal.extraworkType);
List <WorkSumExcelItemModel> sortedItems = q.ToList();
foreach (var item in sortedItems)
{
if (factor <= 0)
{
sumModel.Items.FirstOrDefault(s => s.Id == item.Id).MinusedThresholdHour = 0;
}
else
{
if (factor <= item.MinuseHolidayWorkLeftExtraHour)
{
//sumModel.Items.FirstOrDefault(s => s.Id == item.Id).MinusedThresholdHour = factor;
//factor = 0;
double xfactor = item.MinuseHolidayWorkLeftExtraHour - factor;
if (xfactor >= 4)
{
sumModel.Items.FirstOrDefault(s => s.Id == item.Id).MinusedThresholdHour = factor;
}
else
{
sumModel.Items.FirstOrDefault(s => s.Id == item.Id).MinusedThresholdHour = item.MinuseHolidayWorkLeftExtraHour;
}
factor = 0;
}
else
{
sumModel.Items.FirstOrDefault(s => s.Id == item.Id).MinusedThresholdHour = item.MinuseHolidayWorkLeftExtraHour;
factor = factor - item.MinuseHolidayWorkLeftExtraHour;
}
}
}
}
#endregion
#region 统计加班
// 计算B->C，减去多余加班的统计
#region 计算B->C，减去多余加班的统计
double?MinusedThresholdWorkLeftExtraHour = sumModel.Items.Where(s => s.AttendanceRecordCal != null && s.AttendanceRecordCal.extraworkType == (int)SystemExtraType.WorkExtra).Sum(s => s.MinuseHolidayWorkAndThresHoldLeftExtraHour);
sumModel.MinusedThresholdWorkLeftExtraHour = MinusedThresholdWorkLeftExtraHour.HasValue ? MinusedThresholdWorkLeftExtraHour.Value : 0;
double?MinusedThresholdWeekendLeftExtraHour = sumModel.Items.Where(s => s.AttendanceRecordCal != null && s.AttendanceRecordCal.extraworkType == (int)SystemExtraType.WeekendExtra).Sum(s => s.MinuseHolidayWorkAndThresHoldLeftExtraHour);
sumModel.MinusedThresholdWeekendLeftExtraHour = MinusedThresholdWeekendLeftExtraHour.HasValue ? MinusedThresholdWeekendLeftExtraHour.Value : 0;
double?MinusedThresholdHolidayLeftExtraHour = sumModel.Items.Where(s => s.AttendanceRecordCal != null && s.AttendanceRecordCal.extraworkType == (int)SystemExtraType.HolidayExtra).Sum(s => s.MinuseHolidayWorkAndThresHoldLeftExtraHour);
sumModel.MinusedThresholdHolidayLeftExtraHour = MinusedThresholdHolidayLeftExtraHour.HasValue ? MinusedThresholdHolidayLeftExtraHour.Value : 0;
#endregion
// 计算C->D，被减去多余加班的统计
#region 计算C->D，被减去多余加班的统计
double?MinusedThresholdWorkExtraHour = sumModel.Items.Where(s => s.AttendanceRecordCal != null && s.AttendanceRecordCal.extraworkType == (int)SystemExtraType.WorkExtra).Sum(s => s.MinusedThresholdHour);
sumModel.MinusedThresholdWorkExtraHour = MinusedThresholdWorkExtraHour.HasValue ? MinusedThresholdWorkExtraHour.Value : 0;
double?MinusedThresholdWeekendExtraHour = sumModel.Items.Where(s => s.AttendanceRecordCal != null && s.AttendanceRecordCal.extraworkType == (int)SystemExtraType.WeekendExtra).Sum(s => s.MinusedThresholdHour);
sumModel.MinusedThresholdWeekendExtraHour = MinusedThresholdWeekendExtraHour.HasValue ? MinusedThresholdWeekendExtraHour.Value : 0;
double?MinusedThresholdHolidayExtraHour = sumModel.Items.Where(s => s.AttendanceRecordCal != null && s.AttendanceRecordCal.extraworkType == (int)SystemExtraType.HolidayExtra).Sum(s => s.MinusedThresholdHour);
sumModel.MinusedThresholdHolidayExtraHour = MinusedThresholdHolidayExtraHour.HasValue ? MinusedThresholdHolidayExtraHour.Value : 0;
#endregion
#endregion
#endregion
records.Add(sumModel);
}
return(records);
}
