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