public JsonResult GetMonthList(QueryBuilder qb) { var user = FormulaHelper.GetUserInfo(); var year = int.Parse(qb.Items.FirstOrDefault(a => a.Field == "Year").Value.ToString()); var enumServcie = FormulaHelper.GetService <IEnumService>(); var columns = enumServcie.GetEnumDataSource("HR.AttendanceLegends"); #region 初始化DataTable DataTable table = new DataTable(); table.Columns.Add("ID"); table.Columns.Add("Year"); table.Columns.Add("Month"); table.Columns.Add("Workdays"); foreach (var column in columns) { table.Columns.Add(column.Value); } #endregion AttendanceFO fo = FormulaHelper.CreateFO <AttendanceFO>(); var dataList = entities.Set <R_W_AttendanceInfo>().Where(a => a.Year == year && a.UserID == user.UserID).ToList(); for (int i = 1; i <= 12; i++) { List <DateTime?> shouldAttendanceDays = fo.GetShouldAttendanceDays(year, i); var workdays = shouldAttendanceDays.Count; var row = table.NewRow(); row["ID"] = i; row["Year"] = year; row["Month"] = i; row["Workdays"] = workdays; table.Rows.Add(row); foreach (var column in columns) { //Morning 存的考勤状态,MorningType存的当Morning为请假时的请假类别 var _am = dataList.Where(a => a.Month == i && (a.Morning == column.Value || a.MorningType == column.Value)).Count(); var _pm = dataList.Where(a => a.Month == i && (a.Afternoon == column.Value || a.AfternoonType == column.Value)).Count(); var _count = Convert.ToDouble(_am + _pm) * 0.5; row[column.Value] = _count; } } var rtn = new Dictionary <string, object>(); rtn["data"] = table; rtn["total"] = qb.TotolCount; rtn["year"] = year; return(Json(rtn)); }
public override JsonResult GetList(QueryBuilder qb) { var year = System.DateTime.Now.Year; var yearqb = qb.Items.FirstOrDefault(a => a.Field == "Year"); if (yearqb != null) { year = Convert.ToInt32(yearqb.Value); qb.Items.Remove(yearqb); } var enumServcie = FormulaHelper.GetService <IEnumService>(); var columns = enumServcie.GetEnumDataSource("HR.AttendanceLegends"); #region 初始化DataTable DataTable table = new DataTable(); table.Columns.Add("ID"); table.Columns.Add("Name"); table.Columns.Add("DeptName"); table.Columns.Add("Workdays"); table.Columns.Add("AnnualLeavedays"); foreach (var column in columns) { table.Columns.Add(column.Value); } #endregion var baseEntities = FormulaHelper.GetEntities <BaseEntities>(); var userList = baseEntities.Set <S_A_User>().Where(a => a.IsDeleted == "0").Where(qb).ToList(); var dataList = entities.Set <R_W_AttendanceInfo>().Where(a => a.Year == year).ToList(); var annualLeaveList = entities.Set <S_W_AttendanceAnnualLeave>().Where(a => a.Year == year).ToList(); AttendanceFO fo = FormulaHelper.CreateFO <AttendanceFO>(); //List<DateTime?> shouldAttendanceDays = fo.GetShouldAttendanceDays(year); //var workdays = shouldAttendanceDays.Count; foreach (var user in userList) { var row = table.NewRow(); row["ID"] = user.ID; row["Name"] = user.Name; row["DeptName"] = user.DeptName; row["Workdays"] = 0;// workdays; row["AnnualLeavedays"] = annualLeaveList.Where(a => a.UserID == user.ID).Sum(a => a.Days); table.Rows.Add(row); foreach (var column in columns) { //Morning 存的考勤状态,MorningType存的当Morning为请假时的请假类别 var _am = dataList.Where(a => a.UserID == user.ID && (a.Morning == column.Value || a.MorningType == column.Value)).Count(); var _pm = dataList.Where(a => a.UserID == user.ID && (a.Afternoon == column.Value || a.AfternoonType == column.Value)).Count(); var _count = Convert.ToDouble(_am + _pm) * 0.5; row[column.Value] = _count; } } var rtn = new Dictionary <string, object>(); rtn["data"] = table; rtn["total"] = qb.TotolCount; rtn["year"] = year; return(Json(rtn)); }
public JsonResult GetPersonMonthList(QueryBuilder qb, string Year, string Month) { var year = System.DateTime.Now.Year; if (!string.IsNullOrEmpty(Year)) { year = Convert.ToInt32(Year); } var month = System.DateTime.Now.Month; if (!string.IsNullOrEmpty(Month)) { month = Convert.ToInt32(Month); } var enumServcie = FormulaHelper.GetService <IEnumService>(); var columns = enumServcie.GetEnumDataSource("HR.AttendanceLegends"); #region 初始化DataTable DataTable table = new DataTable(); table.Columns.Add("ID"); table.Columns.Add("Name"); table.Columns.Add("DeptName"); table.Columns.Add("Workdays"); foreach (var column in columns) { table.Columns.Add(column.Value); } #endregion var baseEntities = FormulaHelper.GetEntities <BaseEntities>(); var userList = baseEntities.Set <S_A_User>().Where(a => a.IsDeleted == "0").Where(qb).ToList(); var dataList = entities.Set <R_W_AttendanceInfo>().Where(a => a.Year == year && a.Month == month).ToList(); //需要只显示入职以后的考勤数据 var employeeList = this.entities.Set <T_Employee>().Where(a => a.IsDeleted == "0").ToList(); AttendanceFO fo = FormulaHelper.CreateFO <AttendanceFO>(); List <DateTime?> shouldAttendanceDays = fo.GetShouldAttendanceDays(year, month); var workdays = shouldAttendanceDays.Count; 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; } if (joinDate.HasValue) { workdays = shouldAttendanceDays.Where(a => a.Value >= joinDate.Value).Count(); } var row = table.NewRow(); row["ID"] = user.ID; row["Name"] = user.Name; row["DeptName"] = user.DeptName; row["Workdays"] = workdays; table.Rows.Add(row); foreach (var column in columns) { //Morning 存的考勤状态,MorningType存的当Morning为请假时的请假类别 var _am = dataList.Where(a => a.UserID == user.ID && (a.Morning == column.Value || a.MorningType == column.Value)).Count(); var _pm = dataList.Where(a => a.UserID == user.ID && (a.Afternoon == column.Value || a.AfternoonType == column.Value)).Count(); var _count = Convert.ToDouble(_am + _pm) * 0.5; row[column.Value] = _count; } } var rtn = new Dictionary <string, object>(); rtn["data"] = table; rtn["total"] = qb.TotolCount; rtn["year"] = year; rtn["month"] = month; return(Json(rtn)); }
protected override void BeforeSave(Dictionary <string, string> dic, Base.Logic.Domain.S_UI_Form formInfo, bool isNew) { var fo = new AttendanceFO(); var users = dic.GetValue("UserIDs"); var dates = dic.GetValue("StartDate"); var datee = dic.GetValue("EndDate"); if (string.IsNullOrEmpty(dates) || string.IsNullOrEmpty(datee)) { throw new Formula.Exceptions.BusinessException("请输入日期范围"); } var startDate = Convert.ToDateTime(dates); var endDate = Convert.ToDateTime(datee); var morning = dic.GetValue("Morning"); var morningType = dic.GetValue("MorningType"); var afternoon = dic.GetValue("Afternoon"); var afternoonType = dic.GetValue("AfternoonType"); var userIDs = users.Split(','); //需要只插入入职以后的考勤数据 var employeeList = this.BusinessEntities.Set <T_Employee>().Where(a => a.IsDeleted == "0" && userIDs.Contains(a.UserID)).ToList(); for (DateTime date = startDate; date <= endDate; date = date.AddDays(1)) { if (!fo.IsWorkDay(date)) { continue; } foreach (var userID in userIDs) { //需要只插入入职以后的考勤数据 var employee = employeeList.FirstOrDefault(a => a.UserID == userID); DateTime?joinDate = null; //入职时间 if (employee != null && employee.JoinCompanyDate.HasValue) { joinDate = employee.JoinCompanyDate.Value; } if (joinDate.HasValue && date < joinDate) { continue; } var rdata = this.BusinessEntities.Set <R_W_AttendanceInfo>().FirstOrDefault(a => a.UserID == userID && a.Date.HasValue && a.Date.Value == date); if (rdata == null) { rdata = new R_W_AttendanceInfo(); rdata.CreateDate = DateTime.Now; rdata.CreateUser = this.CurrentUserInfo.UserName; rdata.CreateUserID = this.CurrentUserInfo.UserID; rdata.OrgID = this.CurrentUserInfo.UserOrgID; rdata.CompanyID = this.CurrentUserInfo.UserCompanyID; rdata.ID = FormulaHelper.CreateGuid(); rdata.UserID = userID; rdata.UserIDName = FormulaHelper.GetUserInfoByID(userID).UserName; rdata.Year = date.Year; rdata.Month = date.Month; this.BusinessEntities.Set <R_W_AttendanceInfo>().Add(rdata); } else { rdata.ModifyDate = DateTime.Now; rdata.ModifyUser = this.CurrentUserInfo.UserName; rdata.ModifyUserID = this.CurrentUserInfo.UserID; } rdata.Date = date; rdata.Morning = morning; rdata.MorningType = morningType; rdata.Afternoon = afternoon; rdata.AfternoonType = afternoonType; } } }
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("")); }