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