public static DataTable EmployeeExaminationReportInfo(EmployeeTestSearchModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
return(EmployeeTestDBHelper.EmployeeExaminationReportInfo(model));
}
//public void DataBindToDept()
//{
// DataTable dt = PerformanceQueryBus.SearchDeptInfo();
// ddlDeptName.DataSource = dt;
// ddlDeptName.DataTextField = "DeptName";
// ddlDeptName.DataValueField = "ID";
// ddlDeptName.DataBind();
// ddlDeptName.Items.Add(new System.Web.UI.WebControls.ListItem("--请选择--", "0"));
// ddlDeptName.SelectedValue = "0";
//}
public void Search()
{
ReportDocument oRpt = new ReportDocument();
CrystalReportSource1.ReportDocument.Load(Server.MapPath(@"~/OperatingModel/CrystalReport/HumanManager/EmployeeExamination.rpt"));
// SetDatabaseLogon 拉模式中必须用这个方法来设置登录信息，参数一：用户名；参数二：密码；参数三：服务器；参数四：数据库名
CrystalReportViewer1.LogOnInfo.Add(ReportUtil.GetTableLogOnInfo("officedba.PerformanceSummary"));
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
EmployeeTestSearchModel searchModel = new EmployeeTestSearchModel();
searchModel.Addr = Request.Form["hidDeptID"] == null ? "" : Request.Form["hidDeptID"].ToString();
searchModel.StatusName = Request.Form["txtSearchEmployeeID"] == null ? "" : Request.Form["txtSearchEmployeeID"].ToString();
searchModel.StartDate = txtStartDate.Value;
searchModel.StartToDate = txtStartToDate.Value;
searchModel.EndDate = txtEndDate.Value;
searchModel.EndToDate = txtEndToDate.Value;
searchModel.CompanyCD = userInfo.CompanyCD;
//查询数据
CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["Creator"].Text = "\"" + "制表人:" + userInfo.EmployeeName + "\"";
CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["DeptName"].Text = "\"" + "开始日期:" + txtStartDate.Value + " 至 " + txtStartToDate.Value + "\"";
CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["PerformanceType"].Text = "\"" + "结束日期:" + txtEndDate.Value + " 至 " + txtEndToDate.Value + "\"";
DataTable dtData = EmployeeTestBus.EmployeeExaminationReportInfo(searchModel);
DataTable dt = new DataTable();
DataColumn newCol = new DataColumn("LevelType");
DataColumn newCol2 = new DataColumn("ID");
dt.Columns.Add(newCol);
dt.Columns.Add(newCol2);
if (dtData.Rows.Count > 0)
{
for (int i = 0; i < dtData.Rows.Count; i++)
{
DataRow sk = dt.NewRow();
string deptName = EmployeeTestBus.getDept(dtData.Rows[i]["LevelType"] == null ? "" : dtData.Rows[i]["LevelType"].ToString());
sk["LevelType"] = deptName;
sk["ID"] = dtData.Rows[i]["ID"] == null ? "" : dtData.Rows[i]["ID"].ToString();
dt.Rows.Add(sk);
}
}
CrystalReportSource1.ReportDocument.SetDataSource(dt);
CrystalReportSource1.DataBind();
// CrystalReportViewer1是水晶报表浏览器，下面是给该浏览器赋上对像
CrystalReportViewer1.ReportSource = CrystalReportSource1;
// CrystalReportViewer1.DataBind();
}
public void Search()
{
ReportDocument oRpt = new ReportDocument();
CrystalReportSource1.ReportDocument.Load(Server.MapPath(@"~/OperatingModel/CrystalReport/HumanManager/EmployeeConditionMonthReport.rpt"));
// SetDatabaseLogon 拉模式中必须用这个方法来设置登录信息，参数一：用户名；参数二：密码；参数三：服务器；参数四：数据库名
CrystalReportViewer1.LogOnInfo.Add(ReportUtil.GetTableLogOnInfo("officedba.PerformanceSummary"));
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
EmployeeTestSearchModel searchModel = new EmployeeTestSearchModel();
string DeptID = ddlDeptName.SelectedValue;
string dds = Request.Form.ToString();
string year = Request.Form["ddlTaskYear"] == null ? "" : Request.Form["ddlTaskYear"].ToString();
string month = Request.Form["ddlTaskNum"] == null ? "" : Request.Form["ddlTaskNum"].ToString();
string day = "";
if (month == "01" || month == "03" || month == "05" || month == "07" || month == "08" || month == "10" || month == "12")
{
day = "31";
}
else if (month == "04" || month == "06" || month == "09" || month == "11")
{
day = "30";
}
else
{
if (IsLeapYear(Convert.ToInt32(year)))
{
day = "29";
}
else
{
day = "28";
}
}
string monthStartDate = year + "-" + month + "-" + "01";
string monthEndDate = year + "-" + month + "-" + day;
//查询数据
CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["Creator"].Text = "\"" + "制表人:" + userInfo.EmployeeName + "\"";
CrystalReportSource1.ReportDocument.DataDefinition.FormulaFields["Month"].Text = "\"" + "月份:" + year + "-" + month + "\"";
DataTable dt = new DataTable();
DataColumn newCol1 = new DataColumn("UsedName"); //部门
DataColumn newCol2 = new DataColumn("Attachmentname"); //招聘人数
DataColumn newCol3 = new DataColumn("EmployeeName"); //面试人数
DataColumn newCol4 = new DataColumn("NameEn"); //报道人数
DataColumn newCol5 = new DataColumn("AccountNature"); //迟到人数
DataColumn newCol6 = new DataColumn("Height"); //早退人数
DataColumn newCol7 = new DataColumn("Weight"); //旷工次数
DataColumn newCol8 = new DataColumn("Sight"); //请假次数
DataColumn newCol9 = new DataColumn("EmployeeNo"); //迁调人数
DataColumn newCol10 = new DataColumn("CompanyCD"); //迁调人数
dt.Columns.Add(newCol1);
dt.Columns.Add(newCol2);
dt.Columns.Add(newCol3);
dt.Columns.Add(newCol4);
dt.Columns.Add(newCol5);
dt.Columns.Add(newCol6);
dt.Columns.Add(newCol7);
dt.Columns.Add(newCol8);
dt.Columns.Add(newCol9);
dt.Columns.Add(newCol10);
// 部门招聘人数
DataTable GetRequireNum = EmployeeTestBus.GetRequireNum(DeptID, monthStartDate, monthEndDate);
// 部门面试人数
DataTable GetInterviewNum = EmployeeTestBus.GetInterviewNum(DeptID, monthStartDate, monthEndDate);
// 部门报道人数
DataTable GetReportedNum = EmployeeTestBus.GetReportedNum(DeptID, monthStartDate, monthEndDate);
// 部门迟到人数
DataTable GetLatedNum = EmployeeTestBus.GetLatedNum(DeptID, monthStartDate, monthEndDate);
// 部门早退人数
DataTable GetLeaveEarlyNum = EmployeeTestBus.GetLeaveEarlyNum(DeptID, monthStartDate, monthEndDate);
// 部门旷工人次
DataTable GetAbsenteeismNum = EmployeeTestBus.GetAbsenteeismNum(DeptID, monthStartDate, monthEndDate);
// 部门请假次数
DataTable GetLeaveNum = EmployeeTestBus.GetLeaveNum(DeptID, monthStartDate, monthEndDate);
// 部门调出人数
DataTable GetDeptOutNum = EmployeeTestBus.GetDeptOutNum(DeptID, monthStartDate, monthEndDate);
// 部门调入人数
DataTable GetDeptInNum = EmployeeTestBus.GetDeptInNum(DeptID, monthStartDate, monthEndDate);
// 部门离职人数
DataTable GetSeparationNum = EmployeeTestBus.GetSeparationNum(DeptID, monthStartDate, monthEndDate);
//获取某公司的所有部门
DataTable dtData = EmployeeTestBus.GetDeptInfo(DeptID);
if (dtData.Rows.Count > 0)
{
for (int i = 0; i < dtData.Rows.Count; i++)
{
DataRow sk = dt.NewRow();
string deptName = dtData.Rows[i]["DeptName"] == null ? "" : dtData.Rows[i]["DeptName"].ToString();
sk["UsedName"] = deptName;
string ID = dtData.Rows[i]["ID"] == null ? "" : dtData.Rows[i]["ID"].ToString();
// 部门招聘人数
DataTable GetRequireNum2 = GetNewDataTable(GetRequireNum, "applyDept='" + ID + "'");
if (GetRequireNum2.Rows.Count > 0)
{
sk["Attachmentname"] = GetRequireNum2.Rows[0]["PersonCount"] == null ? "" : GetRequireNum2.Rows[0]["PersonCount"].ToString();
}
// 部门面试人数
DataTable GetInterviewNum2 = GetNewDataTable(GetInterviewNum, "DeptID='" + ID + "'");
if (GetInterviewNum2.Rows.Count > 0)
{
sk["EmployeeName"] = GetInterviewNum2.Rows[0]["InterviewNum"] == null ? "" : GetInterviewNum2.Rows[0]["InterviewNum"].ToString();
}
// 部门报道人数
DataTable GetReportedNum2 = GetNewDataTable(GetReportedNum, "DeptID='" + ID + "'");
if (GetReportedNum2.Rows.Count > 0)
{
sk["NameEn"] = GetReportedNum2.Rows[0]["ReportedNum"] == null ? "" : GetReportedNum2.Rows[0]["ReportedNum"].ToString();
}
// 部门迟到人数
DataTable GetLatedNum2 = GetNewDataTable(GetLatedNum, "DeptID='" + ID + "'");
if (GetLatedNum2.Rows.Count > 0)
{
sk["AccountNature"] = GetLatedNum2.Rows[0]["DelayManCount"] == null ? "" : GetLatedNum2.Rows[0]["DelayManCount"].ToString();
}
// 部门早退人数
DataTable GetLeaveEarlyNum2 = GetNewDataTable(GetLeaveEarlyNum, "DeptID='" + ID + "'");
if (GetLeaveEarlyNum2.Rows.Count > 0)
{
sk["Height"] = GetLeaveEarlyNum2.Rows[0]["DelayManCount"] == null ? "" : GetLeaveEarlyNum2.Rows[0]["DelayManCount"].ToString();
}
// 部门旷工人次
DataTable GetAbsenteeismNum2 = GetNewDataTable(GetAbsenteeismNum, "DeptID='" + ID + "'");
if (GetAbsenteeismNum2.Rows.Count > 0)
{
sk["Weight"] = GetAbsenteeismNum2.Rows[0]["Absentee"] == null ? "" : GetAbsenteeismNum2.Rows[0]["Absentee"].ToString();
}
// 部门请假次数
DataTable GetLeaveNum2 = GetNewDataTable(GetLeaveNum, "DeptID='" + ID + "'");
if (GetLeaveNum2.Rows.Count > 0)
{
sk["Sight"] = GetLeaveNum2.Rows[0]["leaveCount"] == null ? "" : GetLeaveNum2.Rows[0]["leaveCount"].ToString();
}
//部门迁出人数
DataTable GetDeptOutNum2 = GetNewDataTable(GetDeptOutNum, "NowDeptID='" + ID + "'");
string outNum = "0";
if (GetDeptOutNum2.Rows.Count > 0)
{
outNum = GetDeptOutNum2.Rows[0]["countQian"] == null ? "0" : GetDeptOutNum2.Rows[0]["countQian"].ToString();
}
//部门迁入人数
DataTable GetDeptInNum2 = GetNewDataTable(GetDeptInNum, "NewDeptID='" + ID + "'");
string intNum = "0";
if (GetDeptInNum2.Rows.Count > 0)
{
intNum = GetDeptInNum2.Rows[0]["countQian"] == null ? "0" : GetDeptInNum2.Rows[0]["countQian"].ToString();
}
string qianNum = (Convert.ToInt32(outNum) + Convert.ToInt32(intNum)).ToString();
if (qianNum == "0")
{
qianNum = "";
}
sk["EmployeeNo"] = qianNum;
// 部门离职人次
DataTable GetSeparationNum2 = GetNewDataTable(GetSeparationNum, "DeptID='" + ID + "'");
if (GetSeparationNum2.Rows.Count > 0)
{
sk["CompanyCD"] = GetSeparationNum2.Rows[0]["separateNum"] == null ? "" : GetSeparationNum2.Rows[0]["separateNum"].ToString();
}
dt.Rows.Add(sk);
}
}
CrystalReportSource1.ReportDocument.SetDataSource(dt);
CrystalReportSource1.DataBind();
// CrystalReportViewer1是水晶报表浏览器，下面是给该浏览器赋上对像
CrystalReportViewer1.ReportSource = CrystalReportSource1;
// CrystalReportViewer1.DataBind();
}
/// <summary>
/// 员工考试次数分析明细
/// </summary>
/// <param name="CompanyCD"></param>
/// <param name="DeptID"></param>
/// <param name="pageIndex"></param>
/// <param name="pageCount"></param>
/// <param name="ord"></param>
/// <param name="TotalCount"></param>
/// <returns></returns>
public static DataTable EmployeeTestCountPrintSelect(EmployeeTestSearchModel model, int pageIndex, int pageCount, string ord, ref int totalCount)
{
return(HumanReportDBHelper.EmployeeTestCountPrintSelect(model, pageIndex, pageCount, ord, ref totalCount));
}
public static DataTable EmployeeExaminationReportInfo(EmployeeTestSearchModel model)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" select C.DeptID AS LevelType,COUNT(C.DeptID) AS ID from officedba. EmployeeTest A ");
searchSql.AppendLine(" left outer join officedba. EmployeeTestScore B ");
searchSql.AppendLine(" on B.CompanyCD=A.CompanyCD AND B.TestNo=A.TestNo ");
searchSql.AppendLine("LEFT OUTER JOIN officedba.EmployeeInfo c ");
searchSql.AppendLine(" ON C.CompanyCD=A.CompanyCD AND B.EmployeeID=C.ID ");
searchSql.AppendLine(" where A.CompanyCD = @CompanyCD and A.Status='1'");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//部门ID
if (!string.IsNullOrEmpty(model.Addr))
{
searchSql.AppendLine(" AND C.DeptID=@Addr ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Addr", model.Addr));
}
//开始时间
if (!string.IsNullOrEmpty(model.StartDate))
{
searchSql.AppendLine(" AND A.StartDate >= @StartDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate", model.StartDate));
}
if (!string.IsNullOrEmpty(model.StartToDate))
{
searchSql.AppendLine(" AND A.StartDate <= @StartToDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartToDate", model.StartToDate));
}
//结束时间
if (!string.IsNullOrEmpty(model.EndDate))
{
searchSql.AppendLine(" AND A.EndDate >= @EndDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", model.EndDate));
}
if (!string.IsNullOrEmpty(model.EndToDate))
{
searchSql.AppendLine(" AND A.EndDate <= @EndToDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndToDate", model.EndToDate));
}
//考试状态
if (!string.IsNullOrEmpty(model.StatusName))
{
searchSql.AppendLine(" AND B.EmployeeID=@EmployeeID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EmployeeID", model.StatusName));
}
searchSql.AppendLine(" GROUP BY C.DeptID ");
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return(SqlHelper.ExecuteSearch(comm));
}
/// <summary>
/// 查询考试信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchTestInfo(EmployeeTestSearchModel model)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" SELECT ");
searchSql.AppendLine(" A.ID AS ID ");
searchSql.AppendLine(" ,A.TestNo AS TestNo ");
searchSql.AppendLine(" ,A.Title AS Title ");
searchSql.AppendLine(" ,ISNULL(B.EmployeeName, '') AS TeacherName ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.StartDate,21),'') AS StartDate ");
searchSql.AppendLine(" ,ISNULL(CONVERT(VARCHAR(10),A.EndDate,21),'') AS EndDate ");
searchSql.AppendLine(" ,A.Addr AS Addr ");
searchSql.AppendLine(" ,CASE A.Status ");
searchSql.AppendLine(" WHEN '0' THEN '未开始' ");
searchSql.AppendLine(" WHEN '1' THEN '已结束' ");
searchSql.AppendLine(" ELSE '' ");
searchSql.AppendLine(" END AS StatusName ");
searchSql.AppendLine(" ,ISNULL(A.AbsenceCount, '') AS AbsenceCount ");
searchSql.AppendLine(" FROM ");
searchSql.AppendLine(" officedba.EmployeeTest A ");
searchSql.AppendLine(" LEFT JOIN officedba.EmployeeInfo B ");
searchSql.AppendLine(" ON A.Teacher = B.ID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//添加标识参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Flag", "0"));
//考试编号
if (!string.IsNullOrEmpty(model.TestNo))
{
searchSql.AppendLine(" AND A.TestNo LIKE '%' + @TestNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@TestNo", model.TestNo));
}
//主题
if (!string.IsNullOrEmpty(model.Title))
{
searchSql.AppendLine(" AND A.Title LIKE '%' + @Title + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title));
}
//考试地点
if (!string.IsNullOrEmpty(model.Addr))
{
searchSql.AppendLine(" AND A.Addr LIKE '%' + @Addr + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Addr", model.Addr));
}
//考试负责人
if (!string.IsNullOrEmpty(model.TeacherID))
{
searchSql.AppendLine(" AND A.Teacher = @Teacher ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Teacher", model.TeacherID));
}
//开始时间
if (!string.IsNullOrEmpty(model.StartDate))
{
searchSql.AppendLine(" AND A.StartDate >= @StartDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate", model.StartDate));
}
if (!string.IsNullOrEmpty(model.StartToDate))
{
searchSql.AppendLine(" AND A.StartDate <= @StartToDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartToDate", model.StartToDate));
}
//结束时间
if (!string.IsNullOrEmpty(model.EndDate))
{
searchSql.AppendLine(" AND A.EndDate >= @EndDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndDate", model.EndDate));
}
if (!string.IsNullOrEmpty(model.EndToDate))
{
searchSql.AppendLine(" AND A.EndDate <= @EndToDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@EndToDate", model.EndToDate));
}
//考试状态
if (!string.IsNullOrEmpty(model.StatusID))
{
searchSql.AppendLine(" AND A.Status = @Status ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Status", model.StatusID));
}
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
return(SqlHelper.ExecuteSearch(comm));
}
