/// <summary>
/// 获取某年月的考勤概况
/// </summary>
/// <param name="CompanyId">公司编号</param>
/// <param name="StaffId">员工编号</param>
/// <param name="Year">年份</param>
/// <param name="Month">月份</param>
/// <returns>考勤概况实体</returns>
public EyouSoft.Model.GovStructure.MAttendanceAbout GetAttendanceAbout(string CompanyId, string StaffId, int Year, int Month)
{
EyouSoft.Model.GovStructure.MAttendanceAbout model = null;
if (!string.IsNullOrEmpty(CompanyId) && !string.IsNullOrEmpty(StaffId) && Year > 0 && Month > 0)
{
model = new EyouSoft.Model.GovStructure.MAttendanceAbout();
return(dal.GetAttendanceAbout(CompanyId, StaffId, Year, Month));
}
return(model);
}
/// <summary>
/// 获取考勤管理集合信息
/// </summary>
/// <param name="CompanyId">公司编号</param>
/// <param name="FileNumber">员工号(为空时不作条件)</param>
/// <param name="StaffName">员工名字(为空时不作条件)</param>
/// <param name="DepartIds">部门ID集合(如：1,2,3)</param>
/// <param name="Department">部门(为空时不作条件)</param>
/// <param name="CompanyId">公司编号</param>
/// <param name="PageSize"></param>
/// <param name="PageIndex"></param>
/// <param name="RecordCount"></param>
/// <returns></returns>
public IList <EyouSoft.Model.GovStructure.MAttendanceAbout> GetList(string FileNumber, string StaffName, string DepartIds, string Department, string CompanyId, int PageSize, int PageIndex, ref int RecordCount)
{
IList <EyouSoft.Model.GovStructure.MAttendanceAbout> ResultList = null;
string tableName = "view_GovAttendance";
string identityColumnName = "StaffId";
//Punctuality全勤 = 0,Late迟到=1,LeaveEarly早退=2,Absenteeism旷工=3,Vacation请假=4,OverTime加班=5,Travel出差=6,Rest休假=7,Suspension停职=8
string fields = "[StaffId],FileNumber,CompanyId,[StaffName],DepartId,DepartName,[Punctuality],[Late],[LeaveEarly],[Absenteeism],[Vacation],[OverTime],Travel,Rest,Suspension ";
string query = string.Format(" [CompanyId]='{0}'", CompanyId);
if (!string.IsNullOrEmpty(StaffName))
{
query = query + string.Format(" AND [StaffName] LIKE '%{0}%'", StaffName);
}
if (!string.IsNullOrEmpty(FileNumber))
{
query = query + string.Format(" AND [FileNumber] LIKE '%{0}%'", FileNumber);
}
if (!string.IsNullOrEmpty(DepartIds))
{
query = query + string.Format(" AND DepartId in ({0}) ", DepartIds);
}
if (!string.IsNullOrEmpty(Department) && string.IsNullOrEmpty(DepartIds))
{
query = query + string.Format(" AND [DepartName] LIKE '%{0}%'", Department);
}
string orderByString = " [IssueTime] DESC";
using (IDataReader dr = EyouSoft.Toolkit.DAL.DbHelper.ExecuteReader(_db, PageSize, PageIndex, ref RecordCount, tableName, identityColumnName, fields, query, orderByString))
{
ResultList = new List <EyouSoft.Model.GovStructure.MAttendanceAbout>();
while (dr.Read())
{
EyouSoft.Model.GovStructure.MAttendanceAbout model = new EyouSoft.Model.GovStructure.MAttendanceAbout()
{
StaffName = dr.IsDBNull(dr.GetOrdinal("StaffName")) ? "" : dr.GetString(dr.GetOrdinal("StaffName")),
StaffId = dr.IsDBNull(dr.GetOrdinal("StaffId")) ? "" : dr.GetString(dr.GetOrdinal("StaffId")),
FileNumber = dr.IsDBNull(dr.GetOrdinal("FileNumber")) ? "" : dr.GetString(dr.GetOrdinal("FileNumber")),
DepartName = dr.IsDBNull(dr.GetOrdinal("DepartName")) ? "" : dr.GetString(dr.GetOrdinal("DepartName")),
Absenteeism = dr.IsDBNull(dr.GetOrdinal("Absenteeism")) ? 0 : dr.GetInt32(dr.GetOrdinal("Absenteeism")),
Late = dr.IsDBNull(dr.GetOrdinal("Late")) ? 0 : dr.GetInt32(dr.GetOrdinal("Late")),
LeaveEarly = dr.IsDBNull(dr.GetOrdinal("LeaveEarly")) ? 0 : dr.GetInt32(dr.GetOrdinal("LeaveEarly")),
Punctuality = dr.IsDBNull(dr.GetOrdinal("Punctuality")) ? 0 : dr.GetInt32(dr.GetOrdinal("Punctuality")),
Vacation = dr.IsDBNull(dr.GetOrdinal("Vacation")) ? 0 : dr.GetDecimal(dr.GetOrdinal("Vacation")),
OverTime = dr.IsDBNull(dr.GetOrdinal("OverTime")) ? 0 : dr.GetDecimal(dr.GetOrdinal("OverTime")),
Travel = dr.IsDBNull(dr.GetOrdinal("Travel")) ? 0 : dr.GetDecimal(dr.GetOrdinal("Travel")),
Rest = dr.IsDBNull(dr.GetOrdinal("Rest")) ? 0 : dr.GetDecimal(dr.GetOrdinal("Rest")),
Suspension = dr.IsDBNull(dr.GetOrdinal("Suspension")) ? 0 : dr.GetDecimal(dr.GetOrdinal("Suspension"))
};
ResultList.Add(model);
model = null;
}
};
return(ResultList);
}
/// <summary>
/// 获取某年月的考勤概况
/// </summary>
/// <param name="CompanyId">公司编号</param>
/// <param name="StaffId">员工编号</param>
/// <param name="Year">年份</param>
/// <param name="Month">月份</param>
/// <returns>考勤概况实体</returns>
public EyouSoft.Model.GovStructure.MAttendanceAbout GetAttendanceAbout(string CompanyId, string StaffId, int Year, int Month)
{
EyouSoft.Model.GovStructure.MAttendanceAbout model = null;
DateTime AddDate = new DateTime(Year, Month, 1);
StringBuilder StrSql = new StringBuilder(); //at.[DepartmentId],
StrSql.Append("SELECT at.[ID] AS StaffId, at.CompanyId, at.[Name] AS [StaffName],"); //Punctuality准点 = 0,Late迟到=1,LeaveEarly早退=2,Absenteeism旷工=3,Vacation请假=4,OverTime加班=5,Travel出差=6,Rest休假=7,Suspension停职=8
StrSql.AppendFormat(" (SELECT Count(1) FROM tbl_GovAttendance d WHERE d.[StaffId] = at.[id] AND d.[AttendanceType] = 0 AND datediff(mm, '{0}', d.[AttendanceTime]) = 0) AS Punctuality,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT Count(1) FROM tbl_GovAttendance e WHERE e.[StaffId] = at.[id] AND e.[AttendanceType] = 1 AND datediff(mm, '{0}', e.[AttendanceTime]) = 0) AS Late,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT Count(1) FROM tbl_GovAttendance f WHERE f.[StaffId] = at.[id] AND f.[AttendanceType] = 2 AND datediff(mm, '{0}', f.[AttendanceTime]) = 0) AS LeaveEarly,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT Count(1) FROM tbl_GovAttendance g WHERE g.[StaffId] = at.[id] AND g.[AttendanceType] = 3 AND datediff(mm, '{0}', g.[AttendanceTime]) = 0) AS Absenteeism,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT isNull(SUM(TimeCount),0) FROM tbl_GovAttendance h WHERE h.[StaffId] = at.[id] AND h.[AttendanceType] = 4 AND datediff(mm, '{0}', h.[AttendanceTime]) = 0) AS Vacation,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT isNull(SUM(TimeCount),0) FROM tbl_GovAttendance m WHERE m.[StaffId] = at.[id] AND m.[AttendanceType] = 5 AND datediff(mm,'{0}', m.[AttendanceTime]) = 0) AS OverTime,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT isNull(SUM(TimeCount),0) FROM tbl_GovAttendance n WHERE n.[StaffId] = at.[id] AND n.[AttendanceType] = 6 AND datediff(mm,'{0}', n.[AttendanceTime]) = 0) AS Travel,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT isNull(SUM(TimeCount),0) FROM tbl_GovAttendance o WHERE o.[StaffId] = at.[id] AND o.[AttendanceType] = 7 AND datediff(mm,'{0}', o.[AttendanceTime]) = 0) AS Rest,", AddDate.ToShortDateString());
StrSql.AppendFormat(" (SELECT isNull(SUM(TimeCount),0) FROM tbl_GovAttendance p WHERE p.[StaffId] = at.[id] AND p.[AttendanceType] = 8 AND datediff(mm,'{0}', p.[AttendanceTime]) = 0) AS Suspension", AddDate.ToShortDateString());
StrSql.AppendFormat(" FROM tbl_GovFile AS at WHERE at.ID='{0}' AND at.CompanyId='{1}' and at.IsDelete = '0'", StaffId, CompanyId);
DbCommand dc = this._db.GetSqlStringCommand(StrSql.ToString());
using (IDataReader dr = EyouSoft.Toolkit.DAL.DbHelper.ExecuteReader(dc, this._db))
{
while (dr.Read())
{
model = new EyouSoft.Model.GovStructure.MAttendanceAbout()
{
StaffName = dr.IsDBNull(dr.GetOrdinal("StaffName")) ? "" : dr.GetString(dr.GetOrdinal("StaffName")),
StaffId = dr.IsDBNull(dr.GetOrdinal("StaffId")) ? "" : dr.GetString(dr.GetOrdinal("StaffId")),
Absenteeism = dr.IsDBNull(dr.GetOrdinal("Absenteeism")) ? 0 : dr.GetInt32(dr.GetOrdinal("Absenteeism")), //旷工=3
Late = dr.IsDBNull(dr.GetOrdinal("Late")) ? 0 : dr.GetInt32(dr.GetOrdinal("Late")), //迟到=1
LeaveEarly = dr.IsDBNull(dr.GetOrdinal("LeaveEarly")) ? 0 : dr.GetInt32(dr.GetOrdinal("LeaveEarly")), //早退=2
Punctuality = dr.IsDBNull(dr.GetOrdinal("Punctuality")) ? 0 : dr.GetInt32(dr.GetOrdinal("Punctuality")), //准点 = 0
Vacation = dr.IsDBNull(dr.GetOrdinal("Vacation")) ? 0 : dr.GetDecimal(dr.GetOrdinal("Vacation")), //请假=4
OverTime = dr.IsDBNull(dr.GetOrdinal("OverTime")) ? 0 : dr.GetDecimal(dr.GetOrdinal("OverTime")), //加班=5
Travel = dr.IsDBNull(dr.GetOrdinal("Travel")) ? 0 : dr.GetDecimal(dr.GetOrdinal("Travel")), //出差=6
Rest = dr.IsDBNull(dr.GetOrdinal("Rest")) ? 0 : dr.GetDecimal(dr.GetOrdinal("Rest")), //休假=7
Suspension = dr.IsDBNull(dr.GetOrdinal("Suspension")) ? 0 : dr.GetDecimal(dr.GetOrdinal("Suspension")) //停职=8
};
}
};
return(model);
}
