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