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