/// <summary>
/// 根据集团ID查询集团下所有部门id 及部门名称 name
/// </summary>
/// <param name="head_id"></param>
/// <returns></returns>
public HttpResponseMessage QueryAllDeparment(int head_id)
{
string sql = "select a.b_id,a.b_name from deparment a " +
"left join company b " +
"on a.com_id = b.com_id " +
"where b.head_id = " + head_id;
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <Dars> .Convert(dt)
};
}
else
{
obj = new {
code = 1,
msg = "nob"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
public HttpResponseMessage QueryUser(int us_id)
{
string sql = "select * from user_detail a " +
"left join users b on a.us_id=b.us_id where a.us_id=" + us_id;
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
msg = "成功",
data = ConvertToEntity <Entity.user_detail> .Convert(dt)
};
}
else
{
obj = new
{
code = 0,
msg = "成功",
data = ""
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询本题库我的所有错误试题
/// </summary>
/// <param name="us_id"></param>
/// <param name="classify_id"></param>
/// <returns></returns>
public HttpResponseMessage TestError(int us_id, int classify_id)
{
string sql = "select b.*,a.id as eid,a.us_id,a.test_id," +
"(select count(id) from test_error where test_id=a.test_id and us_id=" + us_id + " ) as MyTestCount," +
"(select count(id) from test_error where test_id = a.test_id) as TestCount " +
"from test_error a " +
"left join test b " +
"on a.test_id = b.id " +
"where a.us_id = " + us_id + " and a.classify_id = " + classify_id;
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <Entity.test_error> .Convert(dt)
};
}
else
{
obj = new
{
code = 0,
msg = "没有查询到错题库列表"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 获取我出错的试题列表
/// </summary>
/// <param name="us_id"></param>
/// <returns></returns>
public HttpResponseMessage TestErrorList(int us_id)
{
string sql = "select a.id,a.t_name,a.create_time," +
"(select count(id) from test_error where classify_id = a.id and us_id=" + us_id + ") as TestCount, " +
"(select count(id) from mytest where testid=a.id) as MyTestCount " +
"from test_classify a where id in ( " +
"select classify_id from test_error " +
"group by classify_id) and us_id=" + us_id;
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <Entity.test_error> .Convert(dt)
};
}
else
{
obj = new
{
code = 1,
msg = "没有查询到错题库列表"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询本套试题的所有试题编号
/// </summary>
/// <param name="testid">试题的分类ID</param>
/// <returns></returns>
public HttpResponseMessage GetAllTest(int testid)
{
string sql1 = "select id as testid,t_type,daan from test where (t_type='是非题' or t_type='判断题') and classify_id=" + testid;
string sql2 = "select id as testid,t_type,daan from test where t_type like '%单%' and classify_id=" + testid;
string sql3 = "select id as testid,t_type,daan from test where t_type like '%多%' and classify_id=" + testid;
string sql4 = "select id as testid,t_type,daan from test where t_type like '%案例%' and classify_id=" + testid;
DataTable dt1 = help.Totable(sql1);
DataTable dt2 = help.Totable(sql2);
DataTable dt3 = help.Totable(sql3);
DataTable dt4 = help.Totable(sql4);
dt1.Merge(dt2);
dt1.Merge(dt3);
dt1.Merge(dt4);
if (dt1.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <TestReturn> .Convert(dt1)
};
}
else
{
obj = new {
code = 1,
msg = "本套试题不存在或已被管理员删除"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询用户的登录日志信息
/// </summary>
/// <param name="page"></param>
/// <param name="count"></param>
/// <returns></returns>
public HttpResponseMessage QueryLoginLog(int head_id, int page, int count)
{
int p = (page - 1) * count + 1;
int c = page * count;
string sql = "select * from ( " +
"select b.real_name,a.login_time, " +
"row_number() over(order by a.login_time desc) as row " +
"from login_log a " +
"left join users b " +
"on a.us_id = b.us_id " +
"left join user_detail c on a.us_id=c.us_id " +
"where c.head_id=" + head_id + " )temp " +
"where row between " + p + " and " + c;
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <Entity.login_log> .Convert(dt)
};
}
else
{
obj = new
{
code = 0,
msg = "无数据"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询指定的部门详情
/// </summary>
/// <param name="b_id"></param>
/// <returns></returns>
public HttpResponseMessage Query_deparment(int b_id)
{
string sql = "select * from deparment where b_id=" + b_id;
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
msg = "成功",
data = ConvertToEntity <Entity.user_detail> .Convert(dt)
};
}
else
{
obj = new
{
code = 1,
msg = "没有查询到数据信息",
data = ""
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询近十天的每天的隐患数据量
/// </summary>
/// <param name="verify"></param>
/// <param name="head_id"></param>
/// <param name="com_id"></param>
/// <param name="b_id"></param>
/// <param name="c_id"></param>
/// <param name="us_id"></param>
/// <returns></returns>
public HttpResponseMessage YhCountTen(int verify, int head_id, int com_id, int b_id, int c_id, int us_id)
{
string where = "";
if (verify == 0)
{
where = " and y_usid in (select us_id from user_detail where head_id=" + head_id + ") ";
}
else if (verify == 1)
{
where = " and y_usid in (select us_id from user_detail where com_id=" + com_id + ") ";
}
else if (verify == 2)
{
where = " and y_usid in (select us_id from user_detail where b_id=" + b_id + ") ";
}
else
{
where = " and y_usid=" + us_id + " ";
}
string sql = "select CONVERT(varchar(10),dateadd(s,y_createtime,'1970-01-01'),120) as today,count(y_id) as count from yhtable " +
"where datediff(day, dateadd(s, y_createtime,'1970-01-01'),getdate())<= 10 and " +
"datediff(day, dateadd(s, y_createtime, '1970-01-01'), getdate()) >= 0 " + where + " group by CONVERT(varchar(10), dateadd(s, y_createtime, '1970-01-01'), 120)";
obj = new {
code = 0,
data = ConvertToEntity <Yh_SetData> .Convert(help.Totable(sql))
};
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询执行计划的列表
/// </summary>
/// <param name="t_id"></param>
/// <returns></returns>
public HttpResponseMessage QueryProjectlist(int t_id)
{
string sql = "select a.*,b.t_name,c.real_name as work_name,d.real_name as real_name " +
"from projectlist a left " +
"join train_project b on a.t_id = b.t_id left " +
"join users c on a.work_usid = c.us_id " +
"left join users d on a.l_us_id = d.us_id " +
"where a.t_id=" + t_id;
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <Entity.projectlist> .Convert(dt)
};
}
else
{
obj = new {
code = 1,
msg = "nob"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询用户每题的最高得分、最低得分、平均得分、在线时长（分钟）,累计参考次数、试题名称
/// 用户id编号
/// </summary>
/// <param name="us_id"></param>
/// <returns></returns>
public HttpResponseMessage QueryUserTest(int us_id)
{
string sql = "select max(a.us_score) as max,min(a.us_score) as min, " +
"avg(a.us_score) as avg,count(a.us_id) as count,a.us_id,b.t_name, " +
"(select count from on_line where us_id = 13)/ 60 as online " +
"from score a " +
"left join test_classify b on a.testclassify_id = b.id " +
"where a.us_id = " + us_id + " " +
"group by b.t_name,a.us_id";
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <userTest> .Convert(dt)
};
}
else
{
obj = new {
code = 1,
msg = "没有数据信息"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询培训计划列表
/// </summary>
/// <param name="head_id"></param>
/// <returns></returns>
public HttpResponseMessage Query(int head_id, string value)
{
string where = "";
if (value != null && value != "")
{
where += " and (a.t_name like '%" + value + "%' or a.descs like '%" + value + "%') ";
}
string sql = "select a.*,(select count(d.id) from projectlist d where d.t_id=a.t_id) as count, " +
"b.real_name as real_name,c.real_name as work_name " +
"from train_project a " +
"left join users b on a.us_id = b.us_id " +
"left join users c on a.work_usid = c.us_id " +
"where a.head_id = " + head_id + " " + where;
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <Entity.projectlist> .Convert(dt)
};
}
else
{
obj = new {
code = 1,
msg = "nob"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询指定文件信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public HttpResponseMessage Query(int id)
{
string sql = "select *,(select count(sid) from studenttime where sid=id) as lookcount," +
"(select real_name from users where us_id=loaduser) as real_name " +
"from filelist where id=" + id;
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <Entity.studenttime> .Convert(dt)
};
}
else
{
obj = new
{
code = 1,
msg = "无数据"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询集团或企业下的所有部门信息 如果是系统管理员进入，则查询集团下的 否则查询子公司下的所有部门
/// </summary>
/// <param name="head_id"></param>
/// <param name="com_id"></param>
/// <param name="verify"></param>
/// <returns></returns>
public HttpResponseMessage QueryDeparment(int head_id, int com_id, int verify)
{
string sql = "select * from deparment a " +
"left join company b " +
"left join head_office c " +
"on b.head_id = c.head_id " +
"on a.com_id = b.com_id " +
"where c.head_id = " + head_id + " ";
if (verify > 0 && com_id > 0)
{
sql += " and b.com_id=" + com_id + " ";
}
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
msg = "",
data = ConvertToEntity <Entity.classes> .Convert(dt)
};
}
else
{
obj = new {
code = 1,
msg = "没有查询到部门信息"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询单条隐患信息
/// </summary>
/// <param name="yh_no"></param>
/// <returns></returns>
public HttpResponseMessage Query(string yh_no)
{
string sql = "select *,(select real_name from users where us_id=a.yh_user_from) as yh_user_from_name," +
"(select b_name from deparment where b_id = a.yh_deparment) as yh_deparment_name," +
"(select b_name from deparment where b_id = a.yh_to_deparment) as yh_to_deparment_name," +
"(select real_name from users where us_id = a.yh_to_user) as yh_to_user_name," +
"(select real_name from users where us_id = a.yh_queren_user) as yh_queren_user_name " +
"from yinhuan a " +
"left join yinhuan_down b on a.yh_no = b.yh_no " +
"left join yinhuan_up c on a.yh_no = c.yh_no " +
"where a.yh_no = '" + yh_no + "'";
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <Entity.yinhuan_down> .Convert(dt)
};
}
else
{
obj = new
{
code = 0,
msg = "您查询的信息不存在，或已被删除"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询需要我审批的隐患单
/// </summary>
/// <param name="us_id"></param>
/// <param name="page"></param>
/// <param name="count"></param>
/// <returns></returns>
public HttpResponseMessage QueryUp(int us_id, int page, int count)
{
int p = (page - 1) * count + 1;
int c = page * count;
string sqlc = "select count(a.yh_upid) from yinhuan_up a " +
" left join yinhuan b on a.yh_no=b.yh_no " +
" where a.yh_to_userup=" + us_id + " and a.yh_state<2 and (b.yh_send_state=1 or b.yh_send_state=4 or b.yh_send_state=5)";
int xc = Convert.ToInt32(help.FirstRow(sqlc));
string sql = "select * from ( " +
"select b.*,a.yh_to_userup,a.yh_state,a.yh_time,a.yh_upid, " +
"row_number() over(order by a.yh_upid desc) as row from yinhuan_up a " +
"left join yinhuan b on a.yh_no = b.yh_no " +
"where a.yh_to_userup = " + us_id + " and a.yh_state < 2 and (b.yh_send_state=1 or b.yh_send_state=4 or b.yh_send_state=5)) temp " +
"where row between " + p + " and " + c + "";
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
count = xc,
data = ConvertToEntity <Entity.yinhuan_down> .Convert(dt)
};
}
else
{
obj = new
{
code = 1,
msg = "无数据"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询需要我审批的项目
/// </summary>
/// <param name="data">data.value,data.userInfo</param>
/// <param name="state">审批状态</param>
/// <param name="page"></param>
/// <param name="count"></param>
/// <returns></returns>
public HttpResponseMessage QueryUp(dynamic data, int state, int page, int count)
{
int p = (page - 1) * count + 1;
int c = page * count;
JObject array = data.userInfo;
string where = SQLWhere.UserSql(array);
string sql = "select * from ( " +
"select b.*,a.yh_state,a.yh_downid,a.yh_time, " +
"row_number() over(order by a.yh_upid desc) as row from yinhuan_up a " +
"left join yinhuan b on a.yh_no = b.yh_no " +
"where a.yh_to_userup in (" + where + ") and a.yh_state=" + state + " " +
")temp " +
"where row between " + p + " and " + c + "";
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
msg = "",
data = ConvertToEntity <Entity.yinhuan_down> .Convert(dt)
};
}
else
{
obj = new
{
code = 1,
msg = "没有查询到待审核数据信息"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询学习资料的学习情况 结果为文件名称、学习百分比、已学习时长、要求的总学习时长
/// </summary>
/// <param name="us_id"></param>
/// <returns></returns>
public HttpResponseMessage QueryFile(int us_id)
{
string sql = "select title,a.timelenght*100/b.studenttime as times," +
"a.timelenght,b.studenttime " +
" from studenttime a " +
"left join filelist b " +
"on a.fileid = b.id " +
"where a.usid = " + us_id + "";
Dictionary <string, string> dic = new Dictionary <string, string>();
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <userFile> .Convert(dt)
};
}
else
{
obj = new
{
code = 1,
msg = "没有数据信息"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询我的试题库信息
/// </summary>
/// <param name="us_id"></param>
/// <returns></returns>
public HttpResponseMessage Mytest(int us_id)
{
string sql = "select *,(select count(id) from test where classify_id=a.testid) as tcount " +
"from mytest a " +
"left join test_classify b " +
"on a.testid = b.id " +
"where a.usid = " + us_id + " ";
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <Entity.mytest> .Convert(dt)
};
}
else
{
obj = new
{
code = 1,
msg = "您还没有试题数据信息"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 通过隐患表的id查询隐患详细信息
/// </summary>
/// <param name="y_id"></param>
/// <returns></returns>
public HttpResponseMessage Query(int y_id)
{
string sql = "select *,row_number() over(order by y_id desc) as row, " +
"(select real_name from users where us_id = y_usid) as jiancha_name, " +
"(select real_name from users where us_id = y_headuser) as head_username, " +
"(select real_name from users where us_id = y_zguser) as zhenggai_name, " +
"(select real_name from users where us_id = y_qruser) as queren_name " +
"from yhtable where y_id=" + y_id;
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <Entity.yhtable> .Convert(dt)
};
}
else
{
obj = new {
code = 1,
msg = "nob"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 统计用户的检查数量 按年月进行分组，并查询最近12个月的数拓信息
/// </summary>
/// <param name="us_id"></param>
/// <param name="verify"></param>
/// <param name="head_id"></param>
/// <returns></returns>
public HttpResponseMessage QueryUnix(int us_id, int verify, int head_id)
{
string where = "";
if (verify != 0 && verify != 1)
{
where += " and y_usid=" + us_id + " ";
}
string sql = "SELECT top 12 cast(year(DATEADD(s,y_createtime,'1970-01-01 00:00:00')) as varchar(10))+'年'+ " +
"cast(month(DATEADD(s, y_createtime, '1970-01-01 00:00:00')) as varchar(10)) + '月' as dates, " +
"count(y_id) as count from yhtable " +
"where y_headid=" + head_id + " " + where + "" +
"group by year(DATEADD(s, y_createtime, '1970-01-01 00:00:00')), " +
"month(DATEADD(s, y_createtime, '1970-01-01 00:00:00')) " +
"order by " +
"year(DATEADD(s, y_createtime, '1970-01-01 00:00:00')), " +
"month(DATEADD(s, y_createtime, '1970-01-01 00:00:00')) asc";
DataTable dt = help.Totable(sql);
obj = new {
code = 0,
data = ConvertToEntity <Yh_group> .Convert(dt)
};
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询集团内部发布的题库列表
/// </summary>
/// <param name="head_id"></param>
/// <param name="pages"></param>
/// <param name="count"></param>
/// <returns></returns>
public HttpResponseMessage Query(int head_id, int pages, int count)
{
int p = (pages - 1) * count + 1;
int c = pages * count;
List <int> list = QueryCount(head_id, count);
int page = list[0]; //总页数
int testCount = list[1]; //总数量
string sql = "select * from( " +
"select a.*,(select count(b.id) from test b where b.classify_id=a.id) as tcount, " +
"row_number() over(order by a.id desc) as row from test_classify a " +
"where a.head_id = " + head_id + ") temp " +
"where row between " + p + " and " + c + " ";
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
page = page,
testCount = testCount,
data = ConvertToEntity <Entity.test> .Convert(dt)
};
}
else
{
obj = new
{
code = 1,
page = page,
testCount = testCount,
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 根据班组ID获取班组详细信息
/// </summary>
/// <param name="c_id"></param>
/// <returns></returns>
public HttpResponseMessage QueryClass(int c_id)
{
string sql = "select * from classes where c_id=" + c_id;
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
msg = "读取列表成功",
data = ConvertToEntity <Entity.user_detail> .Convert(dt)
};
}
else
{
obj = new
{
code = 1,
msg = "没有数据",
data = ""
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 根据部门id查询班组列表
/// </summary>
/// <param name="b_id"></param>
/// <returns></returns>
public HttpResponseMessage Query(int b_id)
{
string sql = "select b.*,(select count(a.us_id) from user_detail a where a.c_id=b.c_id ) as u_count from classes b " +
"where b.b_id=" + b_id + "";
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
msg = "读取列表成功",
data = ConvertToEntity <Entity.user_detail> .Convert(dt)
};
}
else
{
obj = new {
code = 1,
msg = "没有数据",
data = ""
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询员工所有没有过期的三级教育卡
/// </summary>
/// <param name="us_id"></param>
/// <returns></returns>
public HttpResponseMessage Query(int us_id)
{
int today = Zh.Tool.Date_Tool.TimeToInt(DateTime.Now);
string sql = "select a.*,b.real_name from card_sign a " +
"left join users b on a.us_id = b.us_id " +
"where a.us_id = " + us_id + " and(end_time = 0 or end_time > " + today + ")";
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <Entity.card_sign> .Convert(dt)
};
}
else
{
obj = new {
code = 1,
msg = "无数据"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询隐患信息 只限于查询已发布的和已完成的 也就是说send_state==1 或者==3
/// </summary>
/// <param name="data">data里携带一个值value为用户检索的数据信息,userinfo用户的信息，是一个｛｝object类型</param>
/// <returns></returns>
public HttpResponseMessage QuerySend(dynamic data, int page, int count, int yh_send_state)
{
if (yh_send_state != 1 && yh_send_state != 3)
{
obj = new {
code = 1,
msg = "该接口只限于查询已发布和已完成的信息，对应值应该为1或3。"
};
return(Zh.Tool.Json.GetJson(obj));
}
int p = (page - 1) * count + 1;
int c = page * count;
JObject array = data.userInfo;
string where = "where a.yh_id>0 ";
//where +=" and"= SQLWhere.UserSql(array);
string usersql = SQLWhere.UserSql(array);
string value = data.value;
if (value != "" && value != null)
{
where += " and a.yh_applicationName like '%" + value + "%' ";
}
where += " and (a.yh_user_from in (" + usersql + ") or a.yh_queren_user in (" + usersql + ") ) ";
where += " and a.yh_send_state=" + yh_send_state + " ";
string sql = "select * from( " +
"select a.*,b.yh_to_userup,b.yh_state as state_up,b.yh_time as time_up, " +
"c.yh_state as state_down,c.yh_time as time_down,c.yh_to_userdown, " +
"row_number() over(order by yh_id desc) as row from yinhuan a " +
"left join yinhuan_up b on a.yh_no = b.yh_no " +
"left join yinhuan_down c on a.yh_no = c.yh_no " +
"" + where + ") temp " +
"where row between " + p + " and " + c + " ";
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
msg = "成功读取数据",
count = QueryCount(data, 2, yh_send_state),
data = ConvertToEntity <Entity.yinhuan_down> .Convert(dt)
};
}
else
{
obj = new
{
code = 1,
msg = "没有查询到相关数据信息"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询员工的三级教育列表
/// </summary>
/// <param name="verify">查看者的权限</param>
/// <param name="head_id">查看者对应的集团ID</param>
/// <param name="com_id">查看者对应的子公司ID</param>
/// <param name="b_id">查看者对应的部门ID</param>
/// <param name="c_id">查看者对应的班组ID</param>
/// <param name="us_id">查看者的ID</param>
/// <param name="status">三级教育完成状态</param>
/// <returns></returns>
public HttpResponseMessage Query(int verify, int head_id, int com_id, int b_id, int c_id, int us_id, int status)
{
string sql = "select a.us_id, b.real_name,c.* from user_detail a " +
"left join users b on a.us_id = b.us_id " +
"left join card_sign c on a.us_id = c.us_id " +
"where a.head_id=" + head_id + " ";
string where = "";
if (verify == 1)
{
where += " and a.com_id=" + com_id + " ";
}
if (verify == 2)
{
where += " and a.b_id=" + b_id + " ";
}
if (verify == 3)
{
where += " and a.c_id=" + c_id + " ";
}
if (verify == 4)
{
where += " and a.us_id=" + us_id + " ";
}
if (status == 1)//查看已完成的 如果为0的话 只查看全部
{
where += " and c.c_time>0 ";
}
if (status == 2)//进行中的
{
where += " and c.c_sign='' and c.c_time=0 ";
}
if (status == 3)//未分配的
{
where += " and c.c_sign is null and c.b_sign is null and com_sign is null ";
}
where += " order by a.us_id desc ";
DataTable dt = help.Totable(sql + where);
if (dt.Rows.Count > 0)
{
obj = new
{
code = 0,
data = ConvertToEntity <Entity.card_sign> .Convert(dt)
};
}
else
{
obj = new
{
code = 1,
msg = "无数据"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询文件列表信息
/// </summary>
/// <param name="value">搜索关键词</param>
/// <param name="types">文件类型</param>
/// <param name="page">当前页码</param>
/// <param name="count">每页显示数量</param>
/// <param name="filetype">文件类型 0 普通文件 1 三级教育文件</param>
/// <param name="jibie">文件级别 0 未选择 1厂级 2 部门级 3 班组级</param>
/// <returns></returns>
public HttpResponseMessage Query(int head_id, string value, string types, int?filetype, int?jibie, int page, int count)
{
int p = (page - 1) * count + 1;
int c = page * count;
string where = "";
if (value != "" && value != null)
{
where += " and title like '%" + value + "%' ";
}
if (types != "" && types != null)
{
where += " and types like '%" + types + "%' ";
}
if (filetype != null)
{
where += " and filetype=" + filetype + " ";
}
if (jibie != null)
{
where += " and jibie=" + jibie + " ";
}
string sqlc = "select count(id) from filelist where head_id=" + head_id + " " + where;
string sql = "select * from ( " +
"select *,row_number() over(order by id desc) as row," +
"(select count(sid) from studenttime where sid = id) as lookcount, " +
"(select real_name from users where us_id=loaduser) as real_name " +
"from filelist where head_id=" + head_id + " " + where + "" +
") temp " +
"where row between " + p + " and " + c + "";
DataTable dt = help.Totable(sql);
if (dt.Rows.Count > 0)
{
int noCount = (int)help.FirstRow(sqlc);
obj = new
{
code = 0,
count = noCount,
data = ConvertToEntity <Entity.studenttime> .Convert(dt)
};
}
else
{
obj = new
{
code = 1,
msg = "无数据"
};
}
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 统计用户的考试信息
/// </summary>
/// <param name="us_id"></param>
/// <returns></returns>
public HttpResponseMessage QueryTestCount(int us_id)
{
string sql = "select avg(us_score) as avg,max(us_score) as max," +
"min(us_score) as min,count(id) as count from score where us_id=" + us_id;
DataTable dt = help.Totable(sql);
obj = new {
data = ConvertToEntity <TestTJ> .Convert(dt)
};
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 根据集团Id查询集团信息
/// </summary>
/// <param name="head_id"></param>
/// <returns></returns>
public HttpResponseMessage QueryHeadOffice(int head_id)
{
string sql = "select * from head_office where head_id=" + head_id;
obj = new {
code = "A0000",
msg = "ok",
count = 1,
data = ConvertToEntity <Entity.head_office> .Convert(help.Totable(sql))
};
return(Zh.Tool.Json.GetJson(obj));
}
/// <summary>
/// 查询试题的配置信息
/// </summary>
/// <param name="testid">试题的分类ID</param>
/// <returns></returns>
public HttpResponseMessage GetTestConfig(int testid)
{
string sql = "select type_a,type_b,type_c,type_d,defen_a,defen_b,defen_c,defen_d from test_classify where id=" + testid;
DataTable dt = help.Totable(sql);
int typeA = Convert.ToInt32(dt.Rows[0][0]);
int typeB = Convert.ToInt32(dt.Rows[0][1]);
int typeC = Convert.ToInt32(dt.Rows[0][2]);
int typeD = Convert.ToInt32(dt.Rows[0][3]);
int defen_a = Convert.ToInt32(dt.Rows[0][4]);
int defen_b = Convert.ToInt32(dt.Rows[0][5]);
int defen_c = Convert.ToInt32(dt.Rows[0][6]);
int defen_d = Convert.ToInt32(dt.Rows[0][7]);
string sqlA = "select top " + typeA + " * from test where t_type like '%是非题%' and classify_id=" + testid + " order by newid()";
string sqlB = "select top " + typeB + " * from test where t_type like '%单选题%' and classify_id=" + testid + " order by newid()";
string sqlC = "select top " + typeC + " * from test where t_type like '%多选题%' and classify_id=" + testid + " order by newid()";
string sqlD = "select top " + typeD + " * from test where t_type like '%案例%' and classify_id=" + testid + " order by newid()";
DataTable dtA = help.Totable(sqlA);
DataTable dtB = help.Totable(sqlB);
DataTable dtC = help.Totable(sqlC);
DataTable dtD = help.Totable(sqlD);
dtA.Merge(dtB);
dtA.Merge(dtC);
dtA.Merge(dtD);
int count = dtA.Rows.Count + dtB.Rows.Count + dtC.Rows.Count + dtD.Rows.Count;
if (count > 0)
{
obj = new
{
code = 0,
defen_a = defen_a,
defen_b = defen_b,
defen_c = defen_c,
defen_d = defen_d,
data = ConvertToEntity <Entity.test> .Convert(dtA)
};
}
else
{
obj = new {
code = 1,
msg = "您要查看的试题不存在，或都已经管理员删除"
};
}
return(Zh.Tool.Json.GetJson(obj));
}