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