/// <summary> /// 所有考勤原档数据 /// </summary> /// <param name="keyword"></param> /// <param name="yearMonth"></param> /// <param name="departmentId"></param> /// <param name="jqgridparam"></param> /// <returns></returns> public DataTable GetPageList(string keyword, string yearMonth, string departmentId, ref Pagination jqgridparam) { StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); //strSql.Append($"select * from (select ha.*,be.realname from dbo.HrmsAttendance ha left join baseemployee be on ha.empno=be.empno) T WHERE 1=1 "); strSql.Append($"select * from ( select ha.ID AS ROWID,ha.CARD_ID AS CardNo,ha.clock_id as ClockId,ha.emp_id as EmpNo,ha.Sign_time as signtime,ha.mark as mark,ha.flag as flag,ha.bill_id as billid,ha.dcollecttime as collecttime,ha.eventname as eventname,be.realname from EASTRIVER.DBO.TIMERECORDS ha left join baseemployee be on ha.emp_id = be.empno ) T WHERE 1 = 1 "); if (!string.IsNullOrEmpty(yearMonth)) { strSql.Append($"AND CONVERT(varchar(100), signtime, 112) LIKE '{yearMonth}%'"); } if (!ManageProvider.Provider.Current().IsSystem) { //strSql.Append(" AND ( EmpNo IN ( SELECT ResourceId FROM BaseDataScopePermission WHERE"); //strSql.Append(" ObjectId IN ('" + ManageProvider.Provider.Current().ObjectId.Replace(",", "','") + "') "); //strSql.Append(" ) )"); //new BaseDataScopePermissionBll().GetScopeUserIdList("58e86c4c-8022-4d30-95d5-b3d0eedcc878", "", ""); strSql.Append($" AND empNo in ({new BaseDataScopePermissionBll().GetScopeUserIdList("58e86c4c-8022-4d30-95d5-b3d0eedcc878", "", "")})"); } if (!string.IsNullOrEmpty(keyword)) { string whereIn = DatabaseCommon.GetWhereIn(keyword, ',', new Regex("\\d{7}")); strSql.Append($" AND empNo in ({whereIn})"); } return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); }
// <summary> /// 所有考勤原档数据 /// /// <param name="keyword"></param> /// <param name="end"></param> /// <param name="jqgridparam"></param> /// <param name="abnorType"></param> /// <param name="start"></param> /// <returns></returns> public DataTable GridPageAnalysisListJson(string keyword, string abnorType, DateTime start, DateTime end, ref Pagination jqgridparam) { StringBuilder strSql = new StringBuilder(); List <DbParameter> parameter = new List <DbParameter>(); strSql.Append($"SELECT * FROM ( SELECT ht.*,be.RealName FROM [dbo].[HrmsAttAnalysis] ht LEFT JOIN [dbo].[BaseEmployee] be ON ht.EmpNo=be.EmpNo ) T WHERE 1=1 AND attDate BETWEEN '{start}' AND '{end}'"); switch (abnorType) { case "999": strSql.Append($" AND treatmentresult in " + $"(" + $"'{(int)HrmsConfig.AttendanceStatusEnum.旷工}'," + $"'{(int)HrmsConfig.AttendanceStatusEnum.上班未打卡}'," + $"'{(int)HrmsConfig.AttendanceStatusEnum.下班未打卡}'," + $"'{(int)HrmsConfig.AttendanceStatusEnum.迟到}'," + $"'{(int)HrmsConfig.AttendanceStatusEnum.早退}'," + $"'{(int)HrmsConfig.AttendanceStatusEnum.迟到早退}'" + $")"); break; case "1000": //综合不作处理 break; default: strSql.Append($" AND treatmentresult='{abnorType}'"); break; } //if (!string.IsNullOrEmpty(abnorType) && abnorType != "1000") //{ // strSql.Append($" AND treatmentresult='{abnorType}'"); //} //排除临时卡 // strSql.Append("AND realname not like '临时卡%'"); if (!string.IsNullOrEmpty(keyword)) { string whereIn = DatabaseCommon.GetWhereIn(keyword, ',', new Regex("\\d{7}")); strSql.Append($" AND empNo in ({whereIn})"); } if (!ManageProvider.Provider.Current().IsSystem) { strSql.Append($" AND EmpNo in ({new BaseDataScopePermissionBll().GetScopeUserIdList("58e86c4c-8022-4d30-95d5-b3d0eedcc878", "", "")})"); //strSql.Append(" AND ( EmpNo IN ( SELECT ResourceId FROM BaseDataScopePermission WHERE"); //strSql.Append(" ObjectId IN ('" + ManageProvider.Provider.Current().ObjectId.Replace(",", "','") + "') "); //strSql.Append(" ) )"); } return(Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqgridparam)); }
/// <summary> /// 分析考勤数据 /// </summary> /// <param name="yearmonth">分析月份</param> /// <param name="resourceId"></param> /// <returns></returns> public ActionResult AttDataAnalysis(string yearmonth, string resourceId) { string whereIn = DatabaseCommon.GetWhereIn(resourceId, ',', new Regex("\\d{7}")); List <BaseEmployee> baseEmployees = DataFactory.Database().FindList <BaseEmployee>($"AND IsDimission=1 AND EmpNo IN ({whereIn})"); var company = DataFactory.Database().FindEntity <BaseCompany>(ManageProvider.Provider.Current().CompanyId); _hrmsAttendnceBll.DataAnalysis(baseEmployees, company.SettlementDay, yearmonth); return(Content("1")); }