/// <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="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)); }
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="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="b_id"></param> /// <returns></returns> public HttpResponseMessage QueryClasses(int b_id) { string csql = "select count(c_id) from classes where b_id=" + b_id; int i = Convert.ToInt32(help.FirstRow(csql)); string sql = "select a.*,(select count(c_id) from user_detail where b_id=a.b_id) as count from classes a where b_id=" + b_id; DataTable dt = help.Totable(sql); List <Entity.user_detail> list = ConvertToEntity <Entity.user_detail> .Convert(dt); obj = new { code = "A0000", count = i, data = list }; return(help.ToJson(obj)); }
/// <summary> /// 查询查询单 /// </summary> /// <param name="us_id"></param> /// <param name="verify"></param> /// <param name="head_id"></param> /// <returns></returns> public HttpResponseMessage Query(int us_id, int verify, int head_id, int page, int count) { int p = (page - 1) * count + 1; int c = page * count; string where = ""; ///通过不同的员工权限,查询不同的数据 if (verify == 0 || verify == 1) { where = ""; } else { where = " and y_usid=" + us_id + " "; } ///查询数据的总数量 string sqlc = "select count(y_id) from yhtable where y_headid=" + head_id + " " + where; string sql = "select * from ( " + "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_headid=" + head_id + " " + where + " ) temp " + "where row between " + p + " and " + c; DataTable dt = help.Totable(sql); if (dt.Rows.Count > 0) { obj = new { code = 0, count = Convert.ToInt32(help.FirstRow(sqlc)), data = ConvertToEntity <Entity.yhtable> .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> /// 根据部门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="head_id"></param> /// <returns></returns> public HttpResponseMessage QueryCompany(int head_id, int com_id, int verify) { string sql = "select c.*,(select count(us_id) from user_detail where com_id=c.com_id) as u_count," + "(select count(b_id) from deparment where com_id=c.com_id) as b_count " + "from company c where c.head_id=" + head_id + " "; if (verify <= 3) { if (verify == 1 || verify == 2) { sql += " and c.com_id=" + com_id + " "; } } else { obj = new { code = 1, msg = "查看数据的权限不足", }; return(Zh.Tool.Json.GetJson(obj)); } DataTable dt = help.Totable(sql); if (dt.Rows.Count > 0) { obj = new { code = 0, msg = "ok", count = 200, data = ConvertToEntity <Entity.user_detail> .Convert(help.Totable(sql)) }; } else { obj = new { code = 1, msg = "没有上传公司列表信息", count = 200, data = ConvertToEntity <Entity.user_detail> .Convert(help.Totable(sql)) }; } return(Zh.Tool.Json.GetJson(obj)); }
/// <summary> /// 查询指定的题库信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public HttpResponseMessage Query(int id) { string sql = "select * from test_classify where id=" + id; DataTable dt = help.Totable(sql); if (dt.Rows.Count > 0) { obj = new { code = 0, data = ConvertToEntity <Entity.test> .Convert(dt) }; } else { obj = new { code = 1, msg = "没有查询到数据信息" }; } return(Zh.Tool.Json.GetJson(obj)); }
/// <summary> /// 通过usid查询登录会员的所有信息,包括总部的信息和权限等 /// </summary> /// <returns></returns> public List <Entity.user_detail> GetUserVal(int usid) { string sql = "select * from users a " + "left join user_detail b " + "left join head_office c on b.head_id=c.head_id on a.us_id=b.us_id " + "where a.us_id=" + usid; DataTable dt = help.Totable(sql); if (dt.Rows.Count > 0) { user = GDT_API.ConvertToEntity <Entity.user_detail> .Convert(dt); return(user); } else { return(null); } }
/// <summary> /// 查询单个用户的模块权限信息 /// </summary> /// <param name="us_id"></param> /// <returns></returns> public HttpResponseMessage Query(int us_id) { string sql = "select * from user_menu where us_id=" + us_id; DataTable dt = help.Totable(sql); if (dt.Rows.Count > 0) { obj = new { code = 0, data = ConvertToEntity <Entity.user_menu> .Convert(dt) }; } else { obj = new { code = 1, msg = "row count 0" }; } return(Zh.Tool.Json.GetJson(obj)); }
/// <summary> /// 查询我的试题库信息 /// </summary> /// <param name="us_id"></param> /// <param name="t_types">三级教育的类型 0 普通 1 厂级 2 车间 3班组</param> /// <returns></returns> public HttpResponseMessage Mytest(int us_id, int?t_types) { string where = ""; if (t_types != null) { where = " and b.t_types=" + t_types + " "; } 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 + " " + where + ""; 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)); }