/// <summary> /// 得到考勤汇总表数据 /// </summary> private void BindDateAllList() { EyouSoft.BLL.AdminCenterStructure.AttendanceInfo bllAttendanceInfo = new EyouSoft.BLL.AdminCenterStructure.AttendanceInfo(); EyouSoft.Model.AdminCenterStructure.SearchInfo modelSearchInfo = new EyouSoft.Model.AdminCenterStructure.SearchInfo(); modelSearchInfo.Year = Year; modelSearchInfo.Month = Month; modelSearchInfo.DepartMentId = DepartmentID; modelSearchInfo.ArchiveNo = WorkerNum; modelSearchInfo.StaffName = Name; IList <EyouSoft.Model.AdminCenterStructure.AttendanceByDepartment> listAttendanceByDepartment = bllAttendanceInfo.GetAttendanceByDepartmentList(CurrentUserCompanyID, modelSearchInfo); if (listAttendanceByDepartment != null && listAttendanceByDepartment.Count > 0) { bool flag = true; for (int i = 0; i < listAttendanceByDepartment.Count; i++) { if (listAttendanceByDepartment[i].PersonList.Count != 0) { flag = false; break; } } if (flag) { this.crptCollectAllList.EmptyText = "<table width=\"800\" border=\"1\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\" bordercolor=\"#000000\" style=\"border-collapse:collapse; line-height:16px;\"><tr><td colspan=\"34\"><div style=\"text-align:center; margin-top:75px; margin-bottom:75px;\">没有相关的数据!</span></div></td></tr></table>"; } else { this.crptCollectAllList.DataSource = listAttendanceByDepartment; this.crptCollectAllList.DataBind(); } } }
/// <summary> /// 得到个人考勤列表数据 /// </summary> private void BindDataPersonalList() { EyouSoft.BLL.AdminCenterStructure.AttendanceInfo bllAttendanceInfo = new EyouSoft.BLL.AdminCenterStructure.AttendanceInfo(); EyouSoft.Model.AdminCenterStructure.SearchInfo modelSearchInfo = new EyouSoft.Model.AdminCenterStructure.SearchInfo(); modelSearchInfo.Year = Year; modelSearchInfo.Month = Month; modelSearchInfo.DepartMentId = DepartmentID; modelSearchInfo.ArchiveNo = WorkerNum; modelSearchInfo.StaffName = Name; IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> listPersonnelInfo = bllAttendanceInfo.GetList(CurrentUserCompanyID, modelSearchInfo); if (listPersonnelInfo != null && listPersonnelInfo.Count > 0) { this.crptPersonalList.DataSource = listPersonnelInfo; this.crptPersonalList.DataBind(); } else { this.crptPersonalList.EmptyText = "<table width=\"800\" border=\"0\" align=\"center\" cellpadding=\"0\" cellspacing=\"0\" bordercolor=\"#000000\" style=\"border-collapse:collapse; line-height:16px;\"><tr><td colspan=\"7\"><div style=\"text-align:center; margin-top:75px; margin-bottom:75px;\">没有相关的数据!</span></div></td></tr></table>"; } }
/// <summary> /// 根据部门获取所有员工信息 /// </summary> /// <param name="CompanyId">公司编号</param> /// <param name="DepartmentId">部门编号</param> /// <returns></returns> private IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> GetPersonnelList(int CompanyId, int DepartmentId, EyouSoft.Model.AdminCenterStructure.SearchInfo SearchInfo) { DateTime SearchAddDate = new DateTime(SearchInfo.Year.Value, SearchInfo.Month.Value, 1); IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> ResultList = null; string StrSql = string.Format("SELECT ID,UserName,ArchiveNo,(SELECT StaffNo,WorkStatus,AddDate FROM tbl_AttendanceInfo WHERE DATEDIFF(month,AddDate,'{0}')=0 FOR XML RAW,ROOT('ROOT')) AS AttendanceInfoXML FROM tbl_PersonnelInfo WHERE CompanyId={1} AND EXISTS(SELECT 1 FROM dbo.fn_split(DepartmentId,',') WHERE [VALUE]='{2}')", SearchAddDate.ToShortDateString(), CompanyId, DepartmentId); if (!string.IsNullOrEmpty(SearchInfo.ArchiveNo)) { StrSql += string.Format(" AND ArchiveNo LIKE '%{0}%'", SearchInfo.ArchiveNo); } if (!string.IsNullOrEmpty(SearchInfo.StaffName)) { StrSql += string.Format(" AND UserName LIKE '%{0}%'", SearchInfo.StaffName); } DbCommand dc = this._db.GetSqlStringCommand(StrSql); using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db)) { ResultList = new List <EyouSoft.Model.AdminCenterStructure.PersonnelInfo>(); while (dr.Read()) { EyouSoft.Model.AdminCenterStructure.PersonnelInfo model = new EyouSoft.Model.AdminCenterStructure.PersonnelInfo() { UserName = dr.IsDBNull(dr.GetOrdinal("UserName")) ? "" : dr.GetString(dr.GetOrdinal("UserName")), Id = dr.IsDBNull(dr.GetOrdinal("Id")) ? 0 : dr.GetInt32(dr.GetOrdinal("Id")), ArchiveNo = dr.IsDBNull(dr.GetOrdinal("ArchiveNo")) ? "" : dr.GetString(dr.GetOrdinal("ArchiveNo")), 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.AdminCenterStructure.AttendanceByDepartment> GetAttendanceByDepartmentList(int CompanyId, EyouSoft.Model.AdminCenterStructure.SearchInfo SearchInfo) { IList <EyouSoft.Model.AdminCenterStructure.AttendanceByDepartment> ResultList = null; string StrSql = string.Format("SELECT ID,DepartName FROM tbl_CompanyDepartment WHERE CompanyId={0} ", CompanyId); if (SearchInfo.DepartMentId.HasValue && SearchInfo.DepartMentId.Value > 0) { StrSql += string.Format(" AND ID={0}", SearchInfo.DepartMentId); } StrSql += " ORDER BY IssueTime DESC"; DbCommand dc = this._db.GetSqlStringCommand(StrSql); using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db)) { ResultList = new List <EyouSoft.Model.AdminCenterStructure.AttendanceByDepartment>(); while (dr.Read()) { EyouSoft.Model.AdminCenterStructure.AttendanceByDepartment model = new EyouSoft.Model.AdminCenterStructure.AttendanceByDepartment() { DepartmentName = dr.IsDBNull(dr.GetOrdinal("DepartName")) ? "" : dr.GetString(dr.GetOrdinal("DepartName")), DepartmentId = dr.IsDBNull(dr.GetOrdinal("ID")) ? 0 : dr.GetInt32(dr.GetOrdinal("ID")), }; model.PersonList = GetPersonnelList(CompanyId, model.DepartmentId, SearchInfo); ResultList.Add(model); model = null; } }; return(ResultList); }
/// <summary> /// 按年份,月份获取员工考勤列表 /// </summary> /// <param name="CompanyId">公司编号</param> /// <param name="SearchInfo">考勤查询试题</param> /// <returns></returns> public IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> GetList(int CompanyId, EyouSoft.Model.AdminCenterStructure.SearchInfo SearchInfo) { IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> ResultList = new List <EyouSoft.Model.AdminCenterStructure.PersonnelInfo>(); DateTime SearchAddDate = new DateTime(SearchInfo.Year.Value, SearchInfo.Month.Value, 1); StringBuilder StrSql = new StringBuilder(); StrSql.Append("SELECT Id,UserName,ArchiveNo,"); StrSql.Append(" (SELECT Id,DepartName FROM tbl_CompanyDepartment WHERE ID IN(SELECT [VALUE] from dbo.fn_split(a.DepartmentId,',')) FOR XML RAW,ROOT('ROOT'))AS DepartmentXML,"); StrSql.AppendFormat(" (SELECT StaffNo,WorkStatus,AddDate FROM tbl_AttendanceInfo WHERE DATEDIFF(month,AddDate,'{0}')=0 FOR XML RAW,ROOT('ROOT')) AS AttendanceInfoXML", SearchAddDate.ToShortDateString()); StrSql.AppendFormat(" FROM tbl_PersonnelInfo a WHERE CompanyId={0} ", CompanyId); if (SearchInfo.DepartMentId.HasValue && SearchInfo.DepartMentId.Value > 0) { StrSql.AppendFormat(" AND EXISTS(SELECT 1 FROM dbo.fn_split(DepartmentId,',') WHERE [VALUE]='{0}')", SearchInfo.DepartMentId); } if (!string.IsNullOrEmpty(SearchInfo.ArchiveNo)) { StrSql.AppendFormat(" AND ArchiveNo LIKE '%{0}%'", SearchInfo.ArchiveNo); } if (!string.IsNullOrEmpty(SearchInfo.StaffName)) { StrSql.AppendFormat(" AND UserName LIKE '%{0}%'", SearchInfo.StaffName); } StrSql.Append(" ORDER BY IssueTime DESC"); DbCommand dc = this._db.GetSqlStringCommand(StrSql.ToString()); using (IDataReader dr = DbHelper.ExecuteReader(dc, this._db)) { ResultList = new List <EyouSoft.Model.AdminCenterStructure.PersonnelInfo>(); while (dr.Read()) { EyouSoft.Model.AdminCenterStructure.PersonnelInfo model = new EyouSoft.Model.AdminCenterStructure.PersonnelInfo() { UserName = dr.IsDBNull(dr.GetOrdinal("UserName")) ? "" : dr.GetString(dr.GetOrdinal("UserName")), Id = dr.IsDBNull(dr.GetOrdinal("Id")) ? 0 : dr.GetInt32(dr.GetOrdinal("Id")), ArchiveNo = dr.IsDBNull(dr.GetOrdinal("ArchiveNo")) ? "" : dr.GetString(dr.GetOrdinal("ArchiveNo")), AttendanceList = this.GetAttendanceList(dr["AttendanceInfoXML"].ToString()) }; model.DepartmentList = GetDepartmentList(dr["DepartmentXML"].ToString()); ResultList.Add(model); model = null; } }; return(ResultList); }
/// <summary> /// 按年份,月份获取员工考勤列表 /// </summary> /// <param name="CompanyId">公司编号</param> /// <param name="SearchInfo">考勤查询实体</param> /// <returns></returns> public IList <EyouSoft.Model.AdminCenterStructure.PersonnelInfo> GetList(int CompanyId, EyouSoft.Model.AdminCenterStructure.SearchInfo 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; } return(idal.GetList(CompanyId, SearchInfo)); }