//通过level值获得select--用于随机组卷
public DataTable sel_level(int level)
{
string sql = "select ID[val],name[text] from subjectClass where valid=1 and pId=2 and levelID=@level";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@level", level)
};
return(SqlHelper.ExcuteDataTable(sql, param));
}//通过pid值获得select--用于随机组卷
}//通过pid值获得select--用于随机组卷
public DataTable sel_pid(int pid)
{
string sql = "select ID[val],name[text] from subjectClass where valid=1 and pId=@pid";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@pid", pid)
};
return(SqlHelper.ExcuteDataTable(sql, param));
}
//获得树数据-用于题库录入
public DataTable getSelTree(int pid)
{
string sql = "select id,cast(a.leved as varchar)+'-'+a.name[name],case when leved=4 then snav else null end[nav],leved,cast(case when exists(select * from subjectClass where valid=1 and a.id=pId and id<>a.pId) then 1 else 0 end as bit)[isParent] from subjectClass a where valid=1 and leved<>0 and pId=@pid";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@pid", pid)
};
return(SqlHelper.ExcuteDataTable(sql, param));
}
//获得树数据
public DataTable getManTree(int pid)
{
string sql = "select a.id,cast(a.leved as varchar)+'-'+a.name+'('+b.name+')-'+cast(a.total as varchar)+'题' as name,a.name[mc],leved,levelID,a.total,a.valid,cast(case when exists(select * from subjectClass where a.id=pId and id<>a.pId) then 1 else 0 end as bit)[isParent] from subjectClass a left join subjectLevel b on a.levelID=b.ID where pid=@pid";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@pid", pid)
};
return(SqlHelper.ExcuteDataTable(sql, param));
}
//获得树数据
public DataTable getTree(int pid)
{
string sql = "select id,name,cast(case when exists(select * from subjectClass where valid=1 and a.id=pId and id<>a.pId) then 1 else 0 end as bit)[isParent] from subjectClass a where valid=1 and leved<>0 and pId=@pid";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@pid", pid)
};
return(SqlHelper.ExcuteDataTable(sql, param));
}
//查看科目系列
public DataTable subjectTree(int sid)
{
string sql = "DECLARE @BOSS hierarchyid SELECT @BOSS=orgNode FROM subjectClass WHERE ID=@sid and valid=1 SELECT id,name+'('+cast(total as varchar)+'题)'[name],pid FROM subjectClass WHERE orgNode.IsDescendantOf(@BOSS)=1 and valid=1 and leved<>2 and total>0";
SqlParameter[] param = new SqlParameter[] {
new SqlParameter("@sid", sid)
};
return(SqlHelper.ExcuteDataTable(sql, param));
}
//选择题库
public DataTable seleSubject(string eid)
{
string sql = "select id,name,pid,learns from subjectClass where valid=1 and leved in(1,2) and total>0 and (levelID=0 or levelID=(select ISNULL(levelID,0) from employee where uniqueID=@eid))";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@eid", eid)
};
return(SqlHelper.ExcuteDataTable(sql, param));
}
/// <summary>
/// 组卷获得部门 用于组卷 获得参考对象
/// </summary>20170401
/// <param name="companyID"></param>
/// <returns></returns>
public DataTable get_zj_BM(int companyID)
{
string sql = "select ID,name,must,number from department where valid=1 and [type]=1 and isExam=1 and companyID=@companyID";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@companyID", companyID)
};
return(SqlHelper.ExcuteDataTable(sql, param));
}
public DataTable getxzbm(string pxzbm)
{
string sql = "select xzbm[val],name[text] from vp_xzbm where pxzbm=@pxzbm";
SqlParameter[] pars =
{
new SqlParameter("@pxzbm", pxzbm)
};
return(SqlHelper.ExcuteDataTable(sql, pars));
}
/// <summary>
/// 我的题库list，通过eid获得我的题库table
/// </summary>
/// <param name="eid"></param>
/// <returns></returns>
public DataTable getMyLearnTab(string eid)
{
string sql = "select id,[sid],sname,[level],createTime,learns,counts,total,inlearn from v_learnSubject where eid=@eid order by createTime desc";
SqlParameter[] pars =
{
new SqlParameter("@eid", eid)
};
return(SqlHelper.ExcuteDataTable(sql, pars));
}
//职称注册情况统计
public DataTable reportJob(int companyID)
{
string strSQL = "select name1,name2,zcrs,rzrs from [v_reportJob] where companyID=@companyID order by id1";
SqlParameter[] pars =
{
new SqlParameter("@companyID", companyID)
};
return(SqlHelper.ExcuteDataTable(strSQL, pars));
}
/// <summary>
/// 获得c_bm表里数据
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public DataTable getcbm(int type)
{
string sql = "select type_bm[val],name[text] from c_bm where [type]=@type";
SqlParameter[] pars =
{
new SqlParameter("@type", type)
};
return(SqlHelper.ExcuteDataTable(sql, pars));
}
public DataTable getcompany(string xzbm)
{
string sql = "select ID[val],name[text] from company where CityID=@CityID";
SqlParameter[] pars =
{
new SqlParameter("@CityID", xzbm)
};
return(SqlHelper.ExcuteDataTable(sql, pars));
}
//注册情况统计
public DataTable registerReport(string whereStr)
{
string strSQL = "select name,name2,must,zcrs,rzrs,zcl from v_registerReport";
if (!string.IsNullOrEmpty(whereStr))
{
strSQL += " where " + whereStr;
}
return(SqlHelper.ExcuteDataTable(strSQL, null));
}
public DataTable getSelect(int companyID, int pid)
{
string sql = "select id[val],name[text] from department where valid=1 and companyID=@companyID and pid=@pid";
SqlParameter[] pars = new SqlParameter[]
{
new SqlParameter("@companyID", companyID),
new SqlParameter("@pid", pid)
};
return(SqlHelper.ExcuteDataTable(sql, pars));
}
//学习情况统计
public DataTable learnReport(string TimeStart, string TimeEnd, int companyID, string whereStr)
{
string strSQL = "select name,name2,must,number,xxrs,xxcs,wx,xxl from f_learnReport(@TimeStart,@TimeEnd,@companyID)";
if (!string.IsNullOrEmpty(whereStr))
{
strSQL += " where " + whereStr;
}
SqlParameter[] param =
{
new SqlParameter("@TimeStart", TimeStart),
new SqlParameter("@TimeEnd", TimeEnd),
new SqlParameter("@companyID", companyID),
};
return(SqlHelper.ExcuteDataTable(strSQL, param));
}
//考试情况统计
public DataTable examReport(string TimeStart, string TimeEnd, decimal passScore, string whereStr)
{
string strSQL = "select name,name2,number,yk,sk,wk,kscs,ksl,hgl from f_examrport(@TimeStart,@TimeEnd,@passScore)";
if (!string.IsNullOrEmpty(whereStr))
{
strSQL += " where " + whereStr;
}
SqlParameter[] pars =
{
new SqlParameter("@TimeStart", TimeStart),
new SqlParameter("@TimeEnd", TimeEnd),
new SqlParameter("@passScore", passScore)
};
return(SqlHelper.ExcuteDataTable(strSQL, pars));
}
public string getName(int sid)
{
string sql = "select name from subjectClass where ID=@sid";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@sid", sid)
};
DataTable dt = SqlHelper.ExcuteDataTable(sql, param);
if (dt != null && dt.Rows.Count > 0)
{
return(dt.Rows[0]["name"].ToString());
}
else
{
return(null);
}
}
//获得职称
public DataTable getzc(int companyID, int pid)
{
string sql = "select ID[val],jobName[text] from JobInfo where valid=1 and companyID=@companyID";
SqlParameter[] param = null;
if (pid != -1)
{
sql += " and levelID=@pid";
param = new SqlParameter[]
{
new SqlParameter("@companyID", companyID),
new SqlParameter("@pid", pid)
};
}
else
{
param = new SqlParameter[] { new SqlParameter("@companyID", companyID) };
}
return(SqlHelper.ExcuteDataTable(sql, param));
}
public DataTable getAllJb()
{
string sql = "select ID[val],name[text] from subjectLevel";
return(SqlHelper.ExcuteDataTable(sql, null));
}
