public ActionResult GetSolvetData() { int pageindex = int.Parse(Request.Params["pageindex"]); int skip = (pageindex - 1) * 6; var result = (from a in medb.ps_solvetask_main where a.isCancel == 0 select a).ToList(); //任务状态 var taskstatus = (from a in medb.ps_systemconfig where a.configCode == "SOLVETASK_STATUS" select a).ToList(); //获得消缺员信息 var role = (from i in medb.ps_role where i.roleName == "消缺员" && i.isStart == 1 select i).FirstOrDefault(); //获得已选的消缺员 var solvetor = (from a in medb.ps_user join b in medb.ps_solver_detail on a.userCode equals b.solverCode join c in medb.ps_solvetask_main on b.taskId equals c.id where a.roleId == role.id && a.userStatus == 1 && c.taskStatus != 5 select a).ToList(); //再获得未选的消缺员 string sql = "select *from ps_user where 1=1 and userstatus=1 and roleid=(select id from ps_role where rolename='消缺员')"; foreach (var v in solvetor) { sql += " and usercode!='" + v.userCode + "'"; } SqlConnection con = new SqlConnection("server=.;database=MyElectrCheck_DB;Integrated Security=true;"); if (con.State != ConnectionState.Open) { con.Open(); } SqlCommand cmd = new SqlCommand(sql, con); SqlDataReader dr = cmd.ExecuteReader(); List <ps_user> userlist = new List <ps_user>(); while (dr.Read()) { ps_user user = new ps_user() { id = int.Parse(dr["id"].ToString()), userCode = dr["userCode"].ToString(), userName = dr["userName"].ToString() }; userlist.Add(user); } //关闭连接 dr.Close(); con.Close(); //获得工作单据信息 var worktype = (from s in medb.ps_systemconfig where s.configCode == "DOC_TYPE" select s).ToList(); DataSource ds = new DataSource(); ds.taskmainlist = result.Skip(skip).Take(6).ToList(); ds.syslist = taskstatus; ds.userlist1 = userlist; ds.worktypelist = worktype; ds.datacount = result.Count(); string jsonstr = JsonConvert.SerializeObject(ds); return(Content(jsonstr)); }
public ActionResult GetData() { //首先查询巡检员子表中状态不等于完成的所有巡检员的信息 var inspectors = (from a in medc.ps_inspectiontask_main join b in medc.ps_inspector_detail on a.id equals b.taskId where a.taskStatus != 4 select b).ToList(); //再将已查到的巡检员与ps_user表的角色名称为巡检员的用户相比较 var userlist = (from u in medc.ps_user join r in medc.ps_role on u.roleId equals r.id where r.roleName == "巡检员" && u.userStatus == 1 && r.isStart == 1 select u).ToList(); List <ps_user> list2 = new List <ps_user>(); List <ps_user> list3 = new List <ps_user>(); //最后筛选出user表中没有的巡检员信息 foreach (var u in userlist) { foreach (var v in inspectors) { if (u.userCode == v.inspectorCode) { list2.Add(u); } } } string sql = " select *from ps_user where 1=1 and roleid=(select id from ps_role where rolename='巡检员' and isstart=1) and userstatus=1"; for (var i = 0; i < list2.Count(); i++) { sql += " and usercode!='" + list2[i].userCode + "'"; } SqlConnection con = new SqlConnection("server=.;database=MyElectrCheck_DB;Integrated Security=true;"); if (con.State != ConnectionState.Open) { con.Open(); } SqlCommand cmd = new SqlCommand(sql, con); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { ps_user u = new ps_user() { id = int.Parse(dr["id"].ToString()), userCode = dr["userCode"].ToString(), userName = dr["userName"].ToString() }; list3.Add(u); } dr.Close(); con.Close(); DataSource ds = new DataSource(); if (list3.Count() > 0) { ds.userlist2 = list3; } return(Json(ds)); }
//获得消缺人员与缺陷列表信息 public ActionResult GetSolvelistData() { int pageindex = int.Parse(Request.Params["pageindex"]); //获得消缺员信息 var role = (from i in medb.ps_role where i.roleName == "消缺员" && i.isStart == 1 select i).FirstOrDefault(); var role2 = (from a in medb.ps_role where a.roleName == "线路管理员" && a.isStart == 1 select a).FirstOrDefault(); //获得已选的消缺员 var solvetor = (from a in medb.ps_user join b in medb.ps_solver_detail on a.userCode equals b.solverCode join c in medb.ps_solvetask_main on b.taskId equals c.id where a.roleId == role.id && a.userStatus == 1 && c.taskStatus != 5 select a).ToList(); //再获得未选的消缺员 string sql = "select *from ps_user where 1=1 and userstatus=1 and roleid=(select id from ps_role where rolename='消缺员')"; foreach (var v in solvetor) { sql += " and usercode!='" + v.userCode + "'"; } SqlConnection con = new SqlConnection("server=.;database=MyElectrCheck_DB;Integrated Security=true;"); if (con.State != ConnectionState.Open) { con.Open(); } SqlCommand cmd = new SqlCommand(sql, con); SqlDataReader dr = cmd.ExecuteReader(); List <ps_user> userlist = new List <ps_user>(); while (dr.Read()) { ps_user user = new ps_user() { id = int.Parse(dr["id"].ToString()), userCode = dr["userCode"].ToString(), userName = dr["userName"].ToString() }; userlist.Add(user); } //关闭连接 dr.Close(); con.Close(); //获得工作单据类型信息 var worklist = (from a in medb.ps_systemconfig where a.configCode == "DOC_TYPE" && a.isStart == 1 select a).ToList(); //获得任务负责人信息 var tasklist = (from a in medb.ps_user where a.roleId == role2.id && a.userStatus == 1 select a).ToList(); // SqlCommand cmd2 = new SqlCommand(sql, con); //dr = cmd2.ExecuteReader(); // List<ps_inspectiontask_detail> idlist = new List<ps_inspectiontask_detail>(); // while (dr.Read()) // { // ps_inspectiontask_detail id = new ps_inspectiontask_detail() // { // id = int.Parse(dr["id"].ToString()), // lineCode = dr["lineCode"].ToString(), // poleCode = dr["poleCode"].ToString(), // bugLevelName = dr["bugLevelName"].ToString(), // bugTypeName = dr["bugTypeName"].ToString(), // bugDesc = dr["bugDesc"].ToString(), // discovererName = dr["discovererName"].ToString(), // discoverTime = Convert.ToDateTime(dr["discoverTime"].ToString()) // }; // idlist.Add(id); // } //关闭连接 // dr.Close(); DataSource ds = new DataSource(); ds.userlist1 = userlist; ds.userlist2 = solvetor; ds.userlist3 = tasklist; ds.syslist = worklist; // int datacount = idlist.Count(); //int pagecount = (int)Math.Ceiling(datacount / 4.0); //if (pageindex <= pagecount && pageindex > 0) //{ // int skip = (pageindex - 1) * 4; // ds.taskdetlist = idlist.Skip(skip).Take(4).ToList(); // ds.datacount = pagecount; //} string jsonstr = JsonConvert.SerializeObject(ds); return(Content(jsonstr)); }
public ActionResult GetUpdData() { //获得session中保存的值 int id = int.Parse(Session["taskid"].ToString()); var result = (from a in medc.ps_inspectiontask_main where a.id == id select a).FirstOrDefault(); //首先查询巡检员子表中状态不等于完成的所有巡检员的信息 var inspectors = (from a in medc.ps_inspector_detail join b in medc.ps_inspectiontask_main on a.taskId equals b.id where b.taskStatus != 4 select a).ToList(); List <ps_user> list = new List <ps_user>(); string sql = " select *from ps_user where 1=1 and roleid=(select id from ps_role where rolename='巡检员' and isstart=1) and userstatus=1"; for (var i = 0; i < inspectors.Count(); i++) { sql += " and usercode!='" + inspectors[i].inspectorCode + "'"; } SqlConnection con = new SqlConnection("server=.;database=MyElectrCheck_DB;Integrated Security=true;"); if (con.State != ConnectionState.Open) { con.Open(); } SqlCommand cmd = new SqlCommand(sql, con); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { ps_user u = new ps_user() { id = int.Parse(dr["id"].ToString()), userCode = dr["userCode"].ToString(), userName = dr["userName"].ToString() }; list.Add(u); } dr.Close(); //首先查询出巡检任务为完成的巡检任务信息 var ins = (from i in medc.ps_inspectiontask_main where i.taskStatus != 4 && i.isCancel != 1 select i).ToList(); sql = "select *from ps_line_info where isStart=1 "; if (ins.Count() > 0) { foreach (var v in ins) { if (result.lineId != v.lineId) { sql += " and id!=" + v.lineId + " "; } } } List <ps_line_info> linelist = new List <ps_line_info>(); SqlCommand cmd2 = new SqlCommand(sql, con); dr = cmd2.ExecuteReader(); while (dr.Read()) { ps_line_info lines = new ps_line_info() { id = int.Parse(dr["id"].ToString()), lineCode = dr["lineCode"].ToString(), lineName = dr["lineName"].ToString(), startPole = int.Parse(dr["startPole"].ToString()), endPole = int.Parse(dr["endPole"].ToString()), startPoleCode = dr["startPoleCode"].ToString(), endPoleCode = dr["endPoleCode"].ToString() }; linelist.Add(lines); } dr.Close(); con.Close(); DataSource ds = new DataSource(); ds.taskmain = result; ds.pslist = list; ds.lilist = linelist; string jsonstr = JsonConvert.SerializeObject(ds); return(Content(jsonstr)); }