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