/// <summary> /// 按部门获取考勤汇总信息集合 /// </summary> /// <param name="CompanyId">公司编号</param> /// <param name="SearchInfo">考勤查询实体</param> /// <returns></returns> public IList <EyouSoft.Model.GovStructure.MGovAttendanceByDepartment> GetAttendanceByDepartmentList(string CompanyId, EyouSoft.Model.GovStructure.MSearchInfo SearchInfo) { if (!SearchInfo.Year.HasValue || SearchInfo.Year.Value == 0) { SearchInfo.Year = DateTime.Now.Year; } if (!SearchInfo.Month.HasValue || SearchInfo.Month.Value == 0) { SearchInfo.Month = DateTime.Now.Month; } IList <EyouSoft.Model.GovStructure.MGovAttendanceByDepartment> list = null; if (!string.IsNullOrEmpty(CompanyId)) { list = new List <EyouSoft.Model.GovStructure.MGovAttendanceByDepartment>(); list = dal.GetAttendanceByDepartmentList(CompanyId, SearchInfo); } return(list); }
/// <summary> /// 按部门获取考勤汇总信息集合 /// </summary> /// <param name="CompanyId">公司编号</param> /// <param name="SearchInfo">考勤查询实体</param> /// <returns></returns> public IList <EyouSoft.Model.GovStructure.MGovAttendanceByDepartment> GetAttendanceByDepartmentList(string CompanyId, EyouSoft.Model.GovStructure.MSearchInfo SearchInfo) { IList <EyouSoft.Model.GovStructure.MGovAttendanceByDepartment> ResultList = null; string StrSql = string.Format("SELECT DepartId,DepartName FROM tbl_ComDepartment WHERE CompanyId='{0}' ", CompanyId); if (SearchInfo.DepartId != null && SearchInfo.DepartId > 0) { StrSql += string.Format(" AND DepartId={0}", SearchInfo.DepartId); } StrSql += " ORDER BY IssueTime DESC"; DbCommand dc = this._db.GetSqlStringCommand(StrSql); using (IDataReader dr = EyouSoft.Toolkit.DAL.DbHelper.ExecuteReader(dc, this._db)) { ResultList = new List <EyouSoft.Model.GovStructure.MGovAttendanceByDepartment>(); while (dr.Read()) { EyouSoft.Model.GovStructure.MGovAttendanceByDepartment model = new EyouSoft.Model.GovStructure.MGovAttendanceByDepartment() { DepartmentName = dr.IsDBNull(dr.GetOrdinal("DepartName")) ? "" : dr.GetString(dr.GetOrdinal("DepartName")), DepartId = dr.IsDBNull(dr.GetOrdinal("DepartId")) ? "" : dr.GetInt32(dr.GetOrdinal("DepartId")).ToString(), }; model.PersonList = GetPersonnelList(CompanyId, model.DepartId, SearchInfo); ResultList.Add(model); model = null; } }; return(ResultList); }
/// <summary> /// 根据部门获取所有员工信息 /// </summary> /// <param name="CompanyId">公司编号</param> /// <param name="DepartmentId">部门编号</param> /// <returns></returns> private IList <EyouSoft.Model.GovStructure.MGovAttendanceList> GetPersonnelList(string CompanyId, string DepartId, EyouSoft.Model.GovStructure.MSearchInfo SearchInfo) { DateTime SearchAddDate = new DateTime(SearchInfo.Year.Value, SearchInfo.Month.Value, 1); IList <EyouSoft.Model.GovStructure.MGovAttendanceList> ResultList = null; string StrSql = string.Format("SELECT ID,Name,FileNumber,(SELECT StaffId,AttendanceType,AttendanceTime FROM tbl_GovAttendance WHERE DATEDIFF(month,AttendanceTime,'{0}')=0 FOR XML RAW,ROOT('ROOT')) AS AttendanceInfoXML FROM tbl_GovFile WHERE CompanyId='{1}' AND EXISTS(SELECT 1 FROM tbl_GovFileDept WHERE DepartId={2} AND FileId=tbl_GovAttendance.ID)", SearchAddDate.ToShortDateString(), CompanyId, DepartId); if (!string.IsNullOrEmpty(SearchInfo.FileNumber)) { StrSql += string.Format(" AND FileNumber LIKE '%{0}%'", SearchInfo.FileNumber); } if (!string.IsNullOrEmpty(SearchInfo.StaffName)) { StrSql += string.Format(" AND Name LIKE '%{0}%'", SearchInfo.StaffName); } DbCommand dc = this._db.GetSqlStringCommand(StrSql); using (IDataReader dr = EyouSoft.Toolkit.DAL.DbHelper.ExecuteReader(dc, this._db)) { ResultList = new List <EyouSoft.Model.GovStructure.MGovAttendanceList>(); while (dr.Read()) { EyouSoft.Model.GovStructure.MGovAttendanceList model = new EyouSoft.Model.GovStructure.MGovAttendanceList() { Name = dr.IsDBNull(dr.GetOrdinal("Name")) ? "" : dr.GetString(dr.GetOrdinal("Name")), ID = dr.IsDBNull(dr.GetOrdinal("ID")) ? "" : dr.GetString(dr.GetOrdinal("ID")), FileNumber = dr.IsDBNull(dr.GetOrdinal("FileNumber")) ? "" : dr.GetString(dr.GetOrdinal("FileNumber")), AttendanceList = this.GetAttendanceList(dr["AttendanceInfoXML"].ToString()) }; ResultList.Add(model); model = null; } }; return(ResultList); }
/// <summary> /// 按条件获取员工考勤列表 /// </summary> /// <param name="CompanyId">公司编号</param> /// <param name="SearchInfo">考勤查询实体</param> /// <returns></returns> public IList <EyouSoft.Model.GovStructure.MGovAttendanceList> GetList(string CompanyId, EyouSoft.Model.GovStructure.MSearchInfo SearchInfo) { IList <EyouSoft.Model.GovStructure.MGovAttendanceList> ResultList = null; DateTime SearchAddDate = DateTime.Now; if (SearchInfo != null && SearchInfo.Year != null && SearchInfo.Month != null) { SearchAddDate = new DateTime(SearchInfo.Year.Value, SearchInfo.Month.Value, 1); } StringBuilder StrSql = new StringBuilder(); StrSql.Append("SELECT a.ID,a.Name,a.FileNumber,a.CompanyId,a.DepartId,a.IssueTime,b.DepartName,"); StrSql.AppendFormat(" (SELECT StaffId,AttendanceType,AttendanceTime,Subject,TimeCount FROM tbl_GovAttendance WHERE StaffId=a.ID and DATEDIFF(month,AttendanceTime,'{0}')=0 FOR XML RAW,ROOT('ROOT')) AS AttendanceInfoXML", SearchAddDate.ToShortDateString()); StrSql.AppendFormat(" FROM tbl_GovFile a LEFT JOIN tbl_ComDepartment as b on b.DepartId=a.DepartId where a.CompanyId='{0}' and a.IsDelete = '0' ", CompanyId); if (SearchInfo != null) { if (!string.IsNullOrEmpty(SearchInfo.DepartIds)) { StrSql.AppendFormat(" AND a.DepartId in ({0}) ", SearchInfo.DepartIds); } if (!string.IsNullOrEmpty(SearchInfo.DepartName) && string.IsNullOrEmpty(SearchInfo.DepartIds)) { StrSql.AppendFormat(" AND b.DepartName like '%{0}%' ", SearchInfo.DepartName); } if (!string.IsNullOrEmpty(SearchInfo.ID)) { StrSql.AppendFormat(" AND a.ID='{0}' ", SearchInfo.ID); } if (!string.IsNullOrEmpty(SearchInfo.FileNumber)) { StrSql.AppendFormat(" AND a.FileNumber LIKE '%{0}%'", SearchInfo.FileNumber); } if (!string.IsNullOrEmpty(SearchInfo.StaffName)) { StrSql.AppendFormat(" AND a.Name LIKE '%{0}%'", SearchInfo.StaffName); } } StrSql.Append(" ORDER BY a.IssueTime DESC"); DbCommand dc = this._db.GetSqlStringCommand(StrSql.ToString()); using (IDataReader dr = EyouSoft.Toolkit.DAL.DbHelper.ExecuteReader(dc, this._db)) { ResultList = new List <EyouSoft.Model.GovStructure.MGovAttendanceList>(); while (dr.Read()) { EyouSoft.Model.GovStructure.MGovAttendanceList model = new EyouSoft.Model.GovStructure.MGovAttendanceList() { Name = dr.IsDBNull(dr.GetOrdinal("Name")) ? "" : dr.GetString(dr.GetOrdinal("Name")), DepartName = dr.IsDBNull(dr.GetOrdinal("DepartName")) ? "" : dr.GetString(dr.GetOrdinal("DepartName")), ID = dr.IsDBNull(dr.GetOrdinal("ID")) ? "" : dr.GetString(dr.GetOrdinal("ID")), FileNumber = dr.IsDBNull(dr.GetOrdinal("FileNumber")) ? "" : dr.GetString(dr.GetOrdinal("FileNumber")), AttendanceList = this.GetAttendanceList(dr["AttendanceInfoXML"].ToString()) }; ResultList.Add(model); model = null; } }; return(ResultList); }