/// <summary>
/// 获取考勤管理集合信息
/// </summary>
/// <param name="PageSize"></param>
/// <param name="PageIndex"></param>
/// <param name="RecordCount"></param>
/// <param name="CompanyId">公司编号</param>
/// <param name="ArchiveNo">员工号(为空时不作条件)</param>
/// <param name="StaffName">员工名字(为空时不作条件)</param>
/// <param name="DepartmentId">部门ID(为0时取所有)</param>
/// <param name="CompanyId">公司编号</param>
/// <returns></returns>
public IList <EyouSoft.Model.AdminCenterStructure.AttendanceAbout> GetList(int PageSize, int PageIndex, ref int RecordCount, string ArchiveNo, string StaffName, int DepartmentId, int CompanyId)
{
IList <EyouSoft.Model.AdminCenterStructure.AttendanceAbout> ResultList = null;
string tableName = "view_AttendanceInfo";
string identityColumnName = "StaffNo";
string fields = "[StaffNo],ArchiveNo,[StaffName],[Punctuality],[Late],[LeaveEarly],[Absenteeism],[Vacation],[Out],[Group],[AskLeave],[OverTime],[DepartmentXML]";
string query = string.Format("[CompanyId]={0}", CompanyId);
if (!string.IsNullOrEmpty(StaffName))
{
query = query + string.Format(" AND [StaffName] LIKE '%{0}%'", EyouSoft.Toolkit.Utils.ReplaceXmlSpecialCharacter(StaffName));
}
if (!string.IsNullOrEmpty(ArchiveNo))
{
query = query + string.Format(" AND [ArchiveNo] LIKE '%{0}%'", ArchiveNo);
}
if (DepartmentId > 0)
{
query = query + string.Format(" AND EXISTS(SELECT 1 FROM dbo.fn_split(DepartmentId,',') WHERE [VALUE]='{0}') ", DepartmentId);
}
string orderByString = " [StaffNo] DESC";
using (IDataReader dr = DbHelper.ExecuteReader(_db, PageSize, PageIndex, ref RecordCount, tableName, identityColumnName, fields, query, orderByString))
{
ResultList = new List <EyouSoft.Model.AdminCenterStructure.AttendanceAbout>();
while (dr.Read())
{
EyouSoft.Model.AdminCenterStructure.AttendanceAbout model = new EyouSoft.Model.AdminCenterStructure.AttendanceAbout()
{
StaffName = dr.IsDBNull(dr.GetOrdinal("StaffName")) ? "" : dr.GetString(dr.GetOrdinal("StaffName")),
StaffNo = dr.IsDBNull(dr.GetOrdinal("StaffNo")) ? 0 : dr.GetInt32(dr.GetOrdinal("StaffNo")),
ArchiveNo = dr.IsDBNull(dr.GetOrdinal("ArchiveNo")) ? "" : dr.GetString(dr.GetOrdinal("ArchiveNo")),
Absenteeism = dr.IsDBNull(dr.GetOrdinal("Absenteeism")) ? 0 : dr.GetInt32(dr.GetOrdinal("Absenteeism")),
Group = dr.IsDBNull(dr.GetOrdinal("Group")) ? 0 : dr.GetInt32(dr.GetOrdinal("Group")),
Late = dr.IsDBNull(dr.GetOrdinal("Late")) ? 0 : dr.GetInt32(dr.GetOrdinal("Late")),
LeaveEarly = dr.IsDBNull(dr.GetOrdinal("LeaveEarly")) ? 0 : dr.GetInt32(dr.GetOrdinal("LeaveEarly")),
Out = dr.IsDBNull(dr.GetOrdinal("Out")) ? 0 : dr.GetInt32(dr.GetOrdinal("Out")),
Punctuality = dr.IsDBNull(dr.GetOrdinal("Punctuality")) ? 0 : dr.GetInt32(dr.GetOrdinal("Punctuality")),
Vacation = dr.IsDBNull(dr.GetOrdinal("Vacation")) ? 0 : dr.GetInt32(dr.GetOrdinal("Vacation")),
AskLeave = dr.IsDBNull(dr.GetOrdinal("AskLeave")) ? 0 : dr.GetInt32(dr.GetOrdinal("AskLeave")),
OverTime = dr.IsDBNull(dr.GetOrdinal("OverTime")) ? 0 : dr.GetDecimal(dr.GetOrdinal("OverTime"))
};
model.DepartmentList = GetDepartmentList(dr["DepartmentXML"].ToString());
ResultList.Add(model);
model = null;
}
};
return(ResultList);
}
/// <summary>
/// 获取某年月的考勤概况
/// </summary>
/// <param name="CompanyId">公司编号</param>
/// <param name="StaffNo">员工编号</param>
/// <param name="Year">年份</param>
/// <param name="Month">月份</param>
/// <returns>考勤概况实体</returns>
public EyouSoft.Model.AdminCenterStructure.AttendanceAbout GetAttendanceAbout(int CompanyId, int StaffNo, int Year, int Month)
{
EyouSoft.Model.AdminCenterStructure.AttendanceAbout model = null;
DateTime AddDate = new DateTime(Year, Month, 1);
StringBuilder StrSql = new StringBuilder();
StrSql.Append("SELECT at.[id] AS [StaffNo], at.companyid, at.[DepartmentId], at.[UserName] AS [StaffName],");
StrSql.AppendFormat(" (SELECT Count(1) FROM tbl_AttendanceInfo d WHERE d .[StaffNo] = at.[id] AND d .[WorkStatus] = 0 AND datediff(mm, '{0}', d .[AddDate]) = 0) AS Punctuality,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT Count(1) FROM tbl_AttendanceInfo e WHERE e.[StaffNo] = at.[id] AND e.[WorkStatus] = 1 AND datediff(mm, '{0}', e.[AddDate]) = 0) AS Late,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT Count(1) FROM tbl_AttendanceInfo f WHERE f.[StaffNo] = at.[id] AND f.[WorkStatus] = 2 AND datediff(mm, '{0}', f.[AddDate]) = 0) AS LeaveEarly,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT Count(1) FROM tbl_AttendanceInfo g WHERE g.[StaffNo] = at.[id] AND g.[WorkStatus] = 3 AND datediff(mm, '{0}', g.[AddDate]) = 0) AS Absenteeism,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT Count(1) FROM tbl_AttendanceInfo h WHERE h.[StaffNo] = at.[id] AND h.[WorkStatus] = 4 AND datediff(mm, '{0}', h.[AddDate]) = 0) AS Vacation,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT Count(1) FROM tbl_AttendanceInfo j WHERE j.[StaffNo] = at.[id] AND j.[WorkStatus] = 5 AND datediff(mm, '{0}', j.[AddDate]) = 0) AS Out,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT Count(1) FROM tbl_AttendanceInfo k WHERE k.[StaffNo] = at.[id] AND k.[WorkStatus] = 6 AND datediff(mm, '{0}', k.[AddDate]) = 0) AS [Group],", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT Count(1) FROM tbl_AttendanceInfo l WHERE l.[StaffNo] = at.[id] AND l.[WorkStatus] = 7 AND datediff(mm, '{0}', l.[AddDate]) = 0) AS AskLeave,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT SUM(OutTime) FROM tbl_AttendanceInfo m WHERE m.[StaffNo] = at.[id] AND m.[WorkStatus] = 8 AND datediff(mm,'{0}', m.[AddDate]) = 0) AS OverTime", AddDate.ToShortDateString());
StrSql.AppendFormat(" FROM [tbl_PersonnelInfo] AS at WHERE at.[IsLeave] = 0 AND Id={0}", StaffNo);
DbCommand dc = this._db.GetSqlStringCommand(StrSql.ToString());
using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db))
{
while (dr.Read())
{
model = new EyouSoft.Model.AdminCenterStructure.AttendanceAbout()
{
StaffName = dr.IsDBNull(dr.GetOrdinal("StaffName")) ? "" : dr.GetString(dr.GetOrdinal("StaffName")),
StaffNo = dr.IsDBNull(dr.GetOrdinal("StaffNo")) ? 0 : dr.GetInt32(dr.GetOrdinal("StaffNo")),
Absenteeism = dr.IsDBNull(dr.GetOrdinal("Absenteeism")) ? 0 : dr.GetInt32(dr.GetOrdinal("Absenteeism")),
Group = dr.IsDBNull(dr.GetOrdinal("Group")) ? 0 : dr.GetInt32(dr.GetOrdinal("Group")),
Late = dr.IsDBNull(dr.GetOrdinal("Late")) ? 0 : dr.GetInt32(dr.GetOrdinal("Late")),
LeaveEarly = dr.IsDBNull(dr.GetOrdinal("LeaveEarly")) ? 0 : dr.GetInt32(dr.GetOrdinal("LeaveEarly")),
Out = dr.IsDBNull(dr.GetOrdinal("Out")) ? 0 : dr.GetInt32(dr.GetOrdinal("Out")),
Punctuality = dr.IsDBNull(dr.GetOrdinal("Punctuality")) ? 0 : dr.GetInt32(dr.GetOrdinal("Punctuality")),
Vacation = dr.IsDBNull(dr.GetOrdinal("Vacation")) ? 0 : dr.GetInt32(dr.GetOrdinal("Vacation")),
AskLeave = dr.IsDBNull(dr.GetOrdinal("AskLeave")) ? 0 : dr.GetInt32(dr.GetOrdinal("AskLeave")),
OverTime = dr.IsDBNull(dr.GetOrdinal("OverTime")) ? 0 : dr.GetDecimal(dr.GetOrdinal("OverTime"))
};
}
};
return(model);
}
/// <summary>
/// 得到当月考勤统计
/// </summary>
public string GetMonthAttendance(int StaffNo)
{
EyouSoft.BLL.AdminCenterStructure.AttendanceInfo bllAttendanceInfo = new EyouSoft.BLL.AdminCenterStructure.AttendanceInfo();
EyouSoft.Model.AdminCenterStructure.AttendanceAbout modelAttendanceAbout = bllAttendanceInfo.GetAttendanceAbout(CurrentUserCompanyID, StaffNo, Year, Month);
if (modelAttendanceAbout != null)
{
return("准点<strong>" + modelAttendanceAbout.Punctuality + "</strong>天,"
+ "迟到<strong>" + modelAttendanceAbout.Late + "</strong>天,"
+ "早退<strong>" + modelAttendanceAbout.LeaveEarly + "</strong>天,"
+ "旷工<strong>" + modelAttendanceAbout.Absenteeism + "</strong>天,"
+ "休假<strong>" + modelAttendanceAbout.Vacation + "</strong>天,"
+ "请假<strong>" + modelAttendanceAbout.AskLeave + "</strong>天,"
+ "加班<strong>" + modelAttendanceAbout.OverTime + "</strong>小时,"
+ "外出<strong>" + modelAttendanceAbout.Out + "</strong>天，"
+ "出团<strong>" + modelAttendanceAbout.Group + "</strong>天");
}
else
{
return("");
}
}
