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