public List <用户单位表> 查询二级结构(int 一级结构ID)
{
string sql = string.Format("SELECT * from dbo.用户_单位表 where 一级结构 ={0} order by 单位属性 desc", 一级结构ID);
SqlDataReader read = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql.ToString());
List <用户单位表> listyhdw = new List <用户单位表>();
while (read.Read())
{
用户单位表 model = new 用户单位表();
model.ID = Convert.ToInt32(read["ID"].ToString());
model.称 = read["名称"].ToString();
model.成本中心 = read["成本中心"].ToString();
listyhdw.Add(model);
}
read.Close();
return(listyhdw);
}
public List <用户单位表> 查询二级单位()
{
string sql = " SELECT * FROM 用户_单位表 where 一级结构 = 1";
SqlDataReader read = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql.ToString());
List <用户单位表> list = new List <用户单位表>();
用户单位表 modelx = new 用户单位表();
modelx.ID = 0;
modelx.称 = "全部";
list.Add(modelx);
while (read.Read())
{
用户单位表 model = new 用户单位表();
model.ID = Convert.ToInt32(read["ID"]);
model.称 = read["名称"].ToString();
list.Add(model);
}
return(list);
}
//根据用户二级部门查询所在二级部门
public List <用户单位表> 查询用户二级单位(int ID)
{
string sql = string.Format("SELECT * FROM 用户_单位表 where ID = {0}", ID);
SqlDataReader read = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql.ToString());
List <用户单位表> list = new List <用户单位表>();
//用户单位表 modelx = new 用户单位表();
//modelx.ID = 0;
//modelx.名称 = "全部";
//list.Add(modelx);
while (read.Read())
{
用户单位表 model = new 用户单位表();
model.ID = Convert.ToInt32(read["ID"]);
model.称 = read["名称"].ToString();
list.Add(model);
}
return(list);
}
private void ResolveSubTree(用户单位表 Row, TreeNode treeNode)
{
if (Row.ID > 0)
{
// 如果是目录，则默认展开
List <部门表> listbm = bll.查询三级结构(Row.ID);
foreach (部门表 row in listbm)
{
if (row.ID > 0)
{
TreeNode node = new TreeNode();
node.IconUrl = @"~/res/icon/asterisk_red.png";
node.EnableClickEvent = true;
node.Text = row.称;
node.NodeID = row.ID + "-三级";
treeNode.Nodes.Add(node);
//ResolveSubTree(row, node);
}
}
}
}
public List <盘点统计> 查询盘点统计(int 盘点主表ID, string 盘点任务名称)
{
string sql = string.Format(" SELECT * FROM 用户_单位表 order by ID");
SqlDataReader read = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql.ToString());
List <用户单位表> list = new List <用户单位表>();
while (read.Read())
{
用户单位表 model = new 用户单位表();
model.ID = Convert.ToInt32(read["ID"]);
model.称 = read["名称"].ToString();
list.Add(model);
}
List <盘点统计> listpd = new List <盘点统计>();
foreach (用户单位表 item in list)
{
盘点统计 model = new 盘点统计();
model.部门 = item.称;
model.二级部门ID = item.ID;
StringBuilder sb = new StringBuilder();
//sb.Append(" SELECT ( select COUNT(*) FROM AM_已盘点设备表 AS A , dbo.AM_盘点清查主表 AS B WHERE A.盘点主表ID = B.ID AND 二级部门ID ="+item.ID);
//sb.Append(" ) AS 已盘点 ,");
//sb.Append("( SELECT COUNT(*) 总数 FROM dbo.设备_设备信息表 AS A ,部门表 AS B , 用户_单位表 AS C where A.使用单位 = B.ID and b.所属单位 = C.ID and C.ID ="+item.ID);
//sb.Append(" ) AS 设备总数");
sb.Append(" SELECT ( select COUNT(*) FROM AM_已盘点设备表 AS A ");
sb.Append(" INNER JOIN dbo.AM_盘点清查主表 AS B ON A.盘点主表ID = B.ID ");
sb.Append(" INNER JOIN dbo.AM_盘点清查主表 as c on A.盘点主表ID = c.ID ");
sb.Append(" WHERE 二级部门ID =" + item.ID);
sb.Append(" and C.盘点名称= '" + 盘点任务名称 + "'");
sb.Append(")AS 已盘点,");
sb.Append("( SELECT COUNT(*) 总数 FROM dbo.设备_设备信息表 AS A");
sb.Append(" INNER JOIN 部门表 AS B ON A.使用单位 = B.ID");
sb.Append(" INNER JOIN 用户_单位表 AS C ON b.所属单位 = C.ID");
sb.Append(" where C.ID =" + item.ID + ") AS 设备总数");
SqlDataReader readpd = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sb.ToString());
while (readpd.Read())
{
//int 已盘点 = Convert.ToInt32(readpd["已盘点"]);
//int 设备总数 = Convert.ToInt32(readpd["设备总数"]);
model.生产设备已盘点 = Convert.ToInt32(readpd["已盘点"]);
model.生产设备总数 = Convert.ToInt32(readpd["设备总数"]);
model.办公设备总数 = 0;
model.办公设备已盘点 = 0;
model.导设备总数 = 0;
model.导设备已盘点 = 0;
model.建筑物总数 = 0;
model.建筑物已盘点 = 0;
model.工装总数 = 0;
model.工装已盘点 = 0;
model.盘点任务名称 = 盘点任务名称;
}
listpd.Add(model);
}
return(listpd);
}
