public JsonResult SaveReport(int year, int month) { //System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch(); //sw.Start(); SQLHelper sqlHelper = SQLHelper.CreateSqlHelper(ConnEnum.HR.ToString()); var baseEntities = FormulaHelper.GetEntities <BaseEntities>(); AttendanceFO fo = FormulaHelper.CreateFO <AttendanceFO>(); List <DateTime?> shouldAttendanceDays = fo.GetShouldAttendanceDays(year, month); //应出勤日期列表 if (shouldAttendanceDays == null || shouldAttendanceDays.Count == 0) { throw new Formula.Exceptions.BusinessException("汇总的年月超过当前年月,无法汇总。"); } DateTime?monthStartDay = Convert.ToDateTime(year + "-" + month.ToString(System.Globalization.CultureInfo.InvariantCulture).PadLeft(2, '0') + "-01"); DateTime?monthEndDay = Convert.ToDateTime(monthStartDay.Value.AddMonths(1).AddDays(-1).ToShortDateString() + " 23:59:59"); var workBase = new WorkBase(); //所有员工条件日期内的每天数据 var datalist = new List <R_W_AttendanceInfo>(); //由于 直接执行几千条insert语句在虚拟机服务器执行速度过慢,所以采用插入临表,再复制的方式 #region sql模板 string deleteSql = "delete from R_W_AttendanceInfo where year='" + year + "' and month='" + month + "'"; string sql = deleteSql + @" --删除临时表 if object_id('tempdb..#R_W_AttendanceInfo') is not null Begin drop table #R_W_AttendanceInfo End --创建临时表 select top 1 * into #R_W_AttendanceInfo from R_W_AttendanceInfo delete from #R_W_AttendanceInfo --将数据插入临时表 begin print '' {0} end --从临时表复制数据到表 insert into R_W_AttendanceInfo select * from #R_W_AttendanceInfo --删除临时表 if object_id('tempdb..#R_W_AttendanceInfo') is not null Begin drop table #R_W_AttendanceInfo End "; #endregion StringBuilder sb = new StringBuilder(); var userlist = baseEntities.Set <S_A_User>().Where(a => a.IsDeleted == "0").ToList(); #region 初始化所有人的考勤数据 //根据日历和传入的起止日期添加所有人的考勤记录 都为缺勤 //var userIds = this.entities.Set<T_Employee>().Where(a => a.IsDeleted == "0").Select(a=>a.UserID).Distinct().ToList(); //var userlist = baseEntities.Set<S_A_User>().Where(a => userIds.Contains(a.ID)).ToList(); //需要只显示入职以后的考勤数据 var employeeList = this.entities.Set <T_Employee>().Where(a => a.IsDeleted == "0").ToList(); //考勤列表 var userInfo = FormulaHelper.GetUserInfo(); foreach (var user in userlist) { //需要只显示入职以后的考勤数据 var employee = employeeList.FirstOrDefault(a => a.UserID == user.ID); DateTime?joinDate = null; //入职时间 if (employee != null && employee.JoinCompanyDate.HasValue) { joinDate = employee.JoinCompanyDate.Value; } var _shouldAttendanceDays = shouldAttendanceDays; if (joinDate.HasValue) { _shouldAttendanceDays = shouldAttendanceDays.Where(a => a.Value >= joinDate.Value).ToList(); } foreach (var item in _shouldAttendanceDays) { var attendanceInfo = new R_W_AttendanceInfo(); attendanceInfo.ID = FormulaHelper.CreateGuid(); attendanceInfo.CreateDate = DateTime.Now; attendanceInfo.CreateUser = userInfo.UserName; attendanceInfo.CreateUserID = userInfo.UserID; attendanceInfo.OrgID = userInfo.UserOrgID; attendanceInfo.CompanyID = userInfo.UserCompanyID; attendanceInfo.UserID = user.ID; attendanceInfo.UserIDName = user.Name; attendanceInfo.Year = item.Value.Year; attendanceInfo.Month = item.Value.Month; attendanceInfo.Date = item; attendanceInfo.Morning = AttendenceState.Absence.ToString(); attendanceInfo.Afternoon = AttendenceState.Absence.ToString(); //this.entities.Set<R_W_AttendanceInfo>().Add(attendanceInfo); datalist.Add(attendanceInfo); } } #endregion //符合条件日期内的考勤、请假、出差数据 //V_S_W_AttendanceInfo视图会合并修改后的日期 var kqlist = entities.Set <V_S_W_AttendanceInfo>().Where(a => a.WorkDay >= monthStartDay && a.WorkDay <= monthEndDay).ToList(); var leavelist = entities.Set <T_AttendanceLeaveApply>().Where(a => !(a.StartDate < monthStartDay && a.EndDate < monthStartDay) && !(a.StartDate > monthEndDay && a.EndDate > monthEndDay) && a.FlowPhase == "End").ToList(); var outlist = entities.Set <T_AttendanceBusinessApply>().Where(a => !(a.StartDate < monthStartDay && a.EndDate < monthStartDay) && !(a.StartDate > monthEndDay && a.EndDate > monthEndDay) && a.FlowPhase == "End").ToList(); #region 将节假日考勤的记录加入 初始化数据集合,需要统计节假日加班数据 //将节假日考勤的记录加入 初始化数据集合,需要统计节假日加班数据 foreach (var item in kqlist.Where(a => !shouldAttendanceDays.Contains(a.WorkDay))) { var attendanceInfo = new R_W_AttendanceInfo(); attendanceInfo.ID = FormulaHelper.CreateGuid(); attendanceInfo.CreateDate = DateTime.Now; attendanceInfo.CreateUser = userInfo.UserName; attendanceInfo.CreateUserID = userInfo.UserID; attendanceInfo.OrgID = userInfo.UserOrgID; attendanceInfo.CompanyID = userInfo.UserCompanyID; attendanceInfo.UserID = item.UserID; attendanceInfo.UserIDName = item.UserName; attendanceInfo.Year = item.CheckDate.Value.Year; attendanceInfo.Month = item.CheckDate.Value.Month; attendanceInfo.Date = item.CheckDate; //attendanceInfo.Morning = AttendenceState.Absence.ToString(); //attendanceInfo.Afternoon = AttendenceState.Absence.ToString(); //this.entities.Set<R_W_AttendanceInfo>().Add(attendanceInfo); datalist.Add(attendanceInfo); } #endregion //判断有没有对应的枚举(普通枚举),如果删除了对应的枚举(如迟到、早退),则不判断对应逻辑(迟到早退当出勤); var cusEnums = EnumBaseHelper.GetEnumDef("HR.AttendanceState"); if (cusEnums == null) { throw new Formula.Exceptions.BusinessException("请先维护HR.AttendanceState枚举。"); } var cusEnumItems = cusEnums.EnumItem.ToList(); var hasComeLate = cusEnumItems.Any(a => a.Code == AttendenceState.ComeLate.ToString()); var hasLeaveEarly = cusEnumItems.Any(a => a.Code == AttendenceState.LeaveEarly.ToString()); var hasBusiness = cusEnumItems.Any(a => a.Code == AttendenceState.Business.ToString()); var hasLeave = cusEnumItems.Any(a => a.Code == AttendenceState.Leave.ToString()); var hasError = cusEnumItems.Any(a => a.Code == AttendenceState.Error.ToString()); foreach (var item in datalist) { #region 每天的状态逻辑 //当天考勤 var userKqList = kqlist.Where(a => a.UserID == item.UserID && item.Date.Value.Date == a.WorkDay.Value.Date).ToList(); var firstSignInkq = userKqList.Where(s => s.CheckType == CheckType.SignIn.ToString()).OrderBy(a => a.CheckDate).FirstOrDefault(); var lastSignOutkq = userKqList.Where(s => s.CheckType == CheckType.SignOut.ToString()).OrderByDescending(a => a.CheckDate).FirstOrDefault(); //当天请假 var userLeaveList = leavelist.Where(a => a.ApplyUser == item.UserID && item.Date >= a.StartDate && item.Date <= a.EndDate).ToList(); //当天外出 var userOutList = outlist.Where(a => a.ApplyUser == item.UserID && item.Date >= a.StartDate && item.Date <= a.EndDate).ToList(); if (workBase.UseAttendanceInfo) { var noonTime = DateTime.Parse(item.Date.Value.ToShortDateString() + " 12:00:00"); //迟到、早退 if (firstSignInkq != null) { item.PostTime = firstSignInkq.CheckDate; //下午签到 :下午正常,不判断迟到 if (firstSignInkq.CheckDate.Value >= noonTime) { item.Afternoon = AttendenceState.Normal.ToString(); } else { if (hasError && AttendanceFO.IsError(workBase, firstSignInkq.CheckDate)) { item.Morning = AttendenceState.Error.ToString(); } else if (hasComeLate && AttendanceFO.IsLate(workBase, firstSignInkq.CheckDate)) { item.Morning = AttendenceState.ComeLate.ToString(); } else { item.Morning = AttendenceState.Normal.ToString(); } } } if (lastSignOutkq != null) { item.LeaveTime = lastSignOutkq.CheckDate; //上午签退 :上午正常,不判断早退 if (lastSignOutkq.CheckDate.Value < noonTime) { item.Morning = AttendenceState.Normal.ToString(); } else { if (hasError && AttendanceFO.IsError(workBase, lastSignOutkq.CheckDate)) { item.Afternoon = AttendenceState.Error.ToString(); } else if (hasLeaveEarly && AttendanceFO.IsLeaveEarly(workBase, lastSignOutkq.CheckDate)) { item.Afternoon = AttendenceState.LeaveEarly.ToString(); } else { item.Afternoon = AttendenceState.Normal.ToString(); } } } } else { item.Morning = AttendenceState.Normal.ToString(); item.Afternoon = AttendenceState.Normal.ToString(); } if (userLeaveList.Count > 0 && hasLeave) { //上午请假 var amLeave = userLeaveList.FirstOrDefault(a => a.EndDate == item.Date && a.EndFlag == DayFlag.AM.ToString()); if (amLeave != null) { item.Morning = AttendenceState.Leave.ToString(); item.MorningType = amLeave.LeaveType; } //下午请假 var pmLeave = userLeaveList.FirstOrDefault(a => a.StartDate == item.Date && a.StartFlag == DayFlag.PM.ToString()); if (pmLeave != null) { item.Afternoon = AttendenceState.Leave.ToString(); item.AfternoonType = pmLeave.LeaveType; } //全天请假的情况 if (amLeave == null && pmLeave == null) { var dayLeave = userLeaveList.FirstOrDefault(); item.Morning = AttendenceState.Leave.ToString(); item.MorningType = dayLeave.LeaveType; item.Afternoon = AttendenceState.Leave.ToString(); item.AfternoonType = dayLeave.LeaveType; } } else if (userOutList.Count > 0 && hasBusiness) { //上午外出 var amOut = userOutList.FirstOrDefault(a => a.EndDate == item.Date && a.EndFlag == DayFlag.AM.ToString()); if (amOut != null) { item.Morning = AttendenceState.Business.ToString(); } //下午外出 var pmOut = userOutList.FirstOrDefault(a => a.StartDate == item.Date && a.StartFlag == DayFlag.PM.ToString()); if (pmOut != null) { item.Afternoon = AttendenceState.Business.ToString(); } //全天外出的情况 if (amOut == null && pmOut == null) { item.Morning = AttendenceState.Business.ToString(); item.Afternoon = AttendenceState.Business.ToString(); } } #endregion //生成每条考勤insertSql var dicStr = CreateInsertSql(item); dicStr = dicStr.Replace("R_W_AttendanceInfo", "#R_W_AttendanceInfo"); sb.AppendLine(dicStr); } //sw.Stop(); //TimeSpan ts2 = sw.Elapsed; //throw new Formula.Exceptions.BusinessException(string.Format("Stopwatch总共花费{0}s.", (ts2.TotalMilliseconds / 1000))); sql = string.Format(sql, sb.ToString()); sqlHelper.ExecuteNonQuery(sql); return(Json("")); }
private bool Equals(WorkBase other) { return this.Id == other.Id; }
public WorkWindow(WorkBase work) { InitializeComponent(); _panel = new WorkWindowPanel((Work)work); }