protected void Grid1_RowCommand(object sender, GridCommandEventArgs e)
{
Window3.Hidden = false;
if (e.CommandName == "Action1")
{
object[] keys = Grid1.DataKeys[e.RowIndex];
int ID = Convert.ToInt32(keys[0].ToString());//获取ID
盘点进度状态.Text = keys[1].ToString();
//完成状态,盘点单号,名称,盘点方式,清查范围,发布方,起始时间,截止时间
//盘点进度单号.Text = keys[2].ToString();
盘点进度项目名称.Text = keys[3].ToString();
盘点进度盘点方式.Text = keys[4].ToString();
盘点进度盘点范围.Text = keys[5].ToString();
//盘点进度发布方.Text = keys[6].ToString();
string 计划日期 = Convert.ToDateTime(keys[7]).ToLongDateString().ToString() + "----" + Convert.ToDateTime(keys[8]).ToLongDateString().ToString();
盘点进度计划时间.Text = 计划日期;
盘点进度备注.Text = keys[9].ToString();
盘点进度发布日期.Text = Convert.ToDateTime(keys[7]).ToLongDateString().ToString();
List <School盘点进度> dts = new List <School盘点进度>();
List <School盘点进度> 查询盘点进度 = bll.查询盘点进度(ID, 盘点进度盘点范围.Text);
School盘点进度 model1 = new School盘点进度();
int 数量总数 = 查询盘点进度[查询盘点进度.Count - 1].管理数量总数;
int 盘点总数 = 查询盘点进度[查询盘点进度.Count - 1].已经盘点总数;
double bfb1 = Convert.ToDouble(盘点总数) / Convert.ToDouble(数量总数) * 100;
int bfbthis1 = Convert.ToInt32(bfb1);
model1.ID = 9999;
model1.称 = "总进度";
model1.百分比 = bfbthis1.ToString();
model1.管理数量 = 数量总数.ToString();
model1.已经盘点 = 盘点总数.ToString();
model1.完成状态 = "进行中";
model1.盘点结束 = "未完成";
//model1.管理数量 = "0";
dts.Add(model1);
foreach (School盘点进度 item in 查询盘点进度)
{
dts.Add(item);
}
//DataTable table = DataSourceUtil.GetDataTable();
Grid2.DataSource = dts;
Grid2.DataBind();
}
}
public List <School盘点进度> 查询盘点进度(int sid, string 清查范围)
{
StringBuilder sb = new StringBuilder();
if (清查范围 == "个人")
{
sb.Append(" SELECT bumen1.ID , bumen1.名称, bumen1.学校ID, t.管理数量, p.已经盘点,pdjs.盘点结束 from ");
sb.Append(" (SELECT b.ID, b.姓名 as 名称, b.学校ID FROM 用户表 AS b,(select 清查范围,清查ID from 盘点任务表 where ID = 102)");
sb.Append(" as tab4 where charindex(','+CAST(b.ID AS nvarchar(50))+',',tab4.清查ID)>0 ) as bumen1 LEFT OUTER JOIN (SELECT COUNT(*) AS 管理数量, 归属教师ID ");
sb.Append("FROM 办公设备信息表 as tab1,(select 清查范围,清查ID from 盘点任务表 where ID =" + sid);
sb.Append(" ) as tab2 WHERE (归属学校 =1) and charindex(','+CAST(tab1.归属教师ID AS nvarchar(50))+',',tab2.清查ID)>0 ");
sb.Append(" GROUP BY 归属教师ID) AS t ON bumen1.ID = t.归属教师ID LEFT OUTER JOIN (SELECT COUNT(*) AS 已经盘点, 办公设备信息表_1.归属教师ID ");
sb.Append(" FROM 办公设备信息表 AS 办公设备信息表_1 ,(select 清查范围,清查ID from 盘点任务表 where ID =" + sid + ") as tab3 ");
sb.Append("WHERE (盘点 = 1) AND (归属学校 =1) and charindex(','+CAST(办公设备信息表_1.归属教师ID AS nvarchar(50))+',',tab3.清查ID)>0 GROUP BY 归属教师ID) AS p ON bumen1.ID ");
sb.Append(" = p.归属教师ID LEFT OUTER JOIN (SELECT COUNT(盘点) AS 盘点结束, 办公设备信息表_5.归属教师ID ");
sb.Append(" FROM 办公设备信息表 AS 办公设备信息表_5 ,(select 清查范围,清查ID from 盘点任务表 where ID =" + sid + ") as tab5 ");
sb.Append(" WHERE (办公设备信息表_5.盘点 >0) AND (归属学校 =1) and charindex(','+CAST(办公设备信息表_5.归属教师ID AS nvarchar(50))+',',tab5.清查ID)>0 ");
sb.Append("GROUP BY 归属教师ID) AS pdjs ON bumen1.ID = pdjs.归属教师ID");
}
else if (清查范围 == "部门")
{
sb.Append("SELECT bumen1.ID , bumen1.名称, bumen1.学校ID, t.管理数量, p.已经盘点, pdjs.盘点结束 ");
sb.Append("from (SELECT b.ID, b.名称, b.学校ID FROM 一级部门表 AS b,");
sb.Append("(select 清查范围,清查ID from 盘点任务表 where ID = " + sid + ") as tab4 where charindex(','+CAST(b.ID AS nvarchar(50))+',',tab4.清查ID)>0 ) as bumen1 ");
sb.Append("LEFT OUTER JOIN (SELECT COUNT(*) AS 管理数量, 归属部门 FROM 办公设备信息表 as tab1,(select 清查范围,清查ID from 盘点任务表 where ID =" + sid + ") as tab2 ");
sb.Append("WHERE (归属学校 = 1) and charindex(','+CAST(tab1.归属部门 AS nvarchar(50))+',',tab2.清查ID)>0 GROUP BY 归属部门) AS t ON bumen1.ID = t.归属部门 ");
sb.Append("LEFT OUTER JOIN (SELECT COUNT(*) AS 已经盘点, 办公设备信息表_1.归属部门 FROM 办公设备信息表 AS 办公设备信息表_1 ,");
sb.Append("(select 清查范围,清查ID from 盘点任务表 where ID = " + sid + ") as tab3 WHERE (盘点 = 1) AND (归属学校 = 1) ");
sb.Append("and charindex(','+CAST(办公设备信息表_1.归属部门 AS nvarchar(50))+',',tab3.清查ID)>0 GROUP BY 归属部门) AS p ON bumen1.ID = p.归属部门 ");
sb.Append("LEFT OUTER JOIN (SELECT COUNT(*) AS 盘点结束, 办公设备信息表_5.归属部门 FROM 办公设备信息表 AS 办公设备信息表_5 ,");
sb.Append("(select 清查范围,清查ID from 盘点任务表 where ID = " + sid + ") as tab5 WHERE (盘点 > 0) AND (归属学校 = 1) ");
sb.Append("and charindex(','+CAST(办公设备信息表_5.归属部门 AS nvarchar(50))+',',tab5.清查ID)>0 GROUP BY 归属部门) AS pdjs ON bumen1.ID = pdjs.归属部门");
}
else
{
sb.Append("SELECT b.ID, b.名称, b.学校ID, t.管理数量, p.已经盘点,pdjs.盘点结束 ");
sb.Append("FROM 一级部门表 AS b LEFT OUTER JOIN (SELECT COUNT(*) AS 管理数量, 归属部门 ");
sb.Append(" FROM 办公设备信息表 WHERE (归属学校 = 1) GROUP BY 归属部门) AS t ON b.ID = t.归属部门 LEFT OUTER JOIN ");
sb.Append("(SELECT COUNT(*) AS 已经盘点, 归属部门 FROM 办公设备信息表 AS 办公设备信息表_1 WHERE (盘点 = 1) AND (归属学校 = 1) GROUP BY 归属部门)");
sb.Append("AS p ON b.ID = p.归属部门 LEFT OUTER JOIN (SELECT COUNT(*) AS 盘点结束,办公设备信息表_5.归属部门 FROM 办公设备信息表 AS 办公设备信息表_5 ");
sb.Append("WHERE 盘点>0 AND (归属学校 = 1) GROUP BY 归属部门) AS pdjs ON b.ID = pdjs.归属部门 ");
}
int GLSLZS = 0;
int YJPDZC = 0;
SqlDataReader read = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sb.ToString());
List <School盘点进度> list = new List <School盘点进度>();
while (read.Read())
{
School盘点进度 model = new School盘点进度();
model.ID = Convert.ToInt32(read["ID"]);
model.称 = read["名称"].ToString();
model.学校ID = 1;
model.管理数量 = read["管理数量"].ToString();
string 管理数量ls = read["管理数量"].ToString();
if (管理数量ls == "")
{
model.管理数量 = "无";
model.百分比 = "0";
}
else
{
model.管理数量int = Convert.ToInt32(model.管理数量);
GLSLZS += Convert.ToInt32(model.管理数量);
model.管理数量 = model.管理数量int.ToString();
}
string 已经盘点ls = read["已经盘点"].ToString();
if (已经盘点ls == "")
{
model.已经盘点int = 0;
model.已经盘点 = "0";
if (管理数量ls == "")
{
model.已经盘点 = "无";
}
}
else
{
model.已经盘点int = Convert.ToInt32(已经盘点ls);
YJPDZC += Convert.ToInt32(已经盘点ls);
model.已经盘点 = model.已经盘点int.ToString();
}
model.盘点结束 = read["盘点结束"].ToString();
if (管理数量ls == "")
{
model.盘点结束 = "已完成";
}
else
{
if (管理数量ls != "" && 已经盘点ls != "0")
{
if (model.管理数量int == model.已经盘点int)
{
model.盘点结束 = "已完成";
}
else
{
model.盘点结束 = "未完成";
}
}
else
{
model.盘点结束 = "未完成";
}
}
if (管理数量ls != "")
{
if (model.已经盘点int != 0)
{
double bfb = Convert.ToDouble(model.已经盘点int) / Convert.ToDouble(model.管理数量int) * 100;
int bfbthis = Convert.ToInt32(bfb);
model.百分比 = bfbthis.ToString();
}
else
{
model.百分比 = "0";
}
}
model.管理数量总数 = GLSLZS;
model.已经盘点总数 = YJPDZC;
list.Add(model);
}
return(list);
}
