public static List <School一级部门表> 查询一级部门不带全部()
{
try
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM dbo.一级部门表");
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();
list.Add(model);
}
return(list);
}
catch (Exception)
{
throw;
}
}
public List <School盘点报告> 查询盘点报告()
{
List <int> listbmid = new List <int>();
List <School一级部门表> modelbm = new List <School一级部门表>();
List <School盘点报告> modellist = new List <School盘点报告>();
string sqlbm = "select * from 一级部门表";
SqlDataReader read = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sqlbm.ToString());
while (read.Read())
{
School一级部门表 model = new School一级部门表();
model.ID = Convert.ToInt32(read["ID"]);
model.称 = read["名称"].ToString();
modelbm.Add(model);
}
read.Close();
for (int i = 0; i < modelbm.Count; i++)
{
School盘点报告 model = new School盘点报告();
model.部门 = modelbm[i].称;
//负责人可以在这里查询
string sql房屋及构筑物 = string.Format(" select count(一级类别名称) as 数量 from 办公设备信息表 where 归属部门 = {0} and 一级类别名称 like '{1}' and 盘点 = 0", modelbm[i].ID, "房屋及构筑物");
SqlDataReader read房屋及构筑物 = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql房屋及构筑物.ToString());
if (read房屋及构筑物.Read())
{
model.房屋及构筑物 = read房屋及构筑物["数量"].ToString() == null ? "0" : read房屋及构筑物["数量"].ToString();
if (model.房屋及构筑物 == "")
{
model.房屋及构筑物 = "0";
}
}
read房屋及构筑物.Close();
string sql通用设备 = string.Format(" select count(一级类别名称) as 数量 from 办公设备信息表 where 归属部门 = {0} and 一级类别名称 like '{1}' and 盘点 = 0", modelbm[i].ID, "通用设备");
SqlDataReader read通用设备 = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql通用设备.ToString());
if (read通用设备.Read())
{
model.通用设备 = read通用设备["数量"].ToString() == null ? "0" : read通用设备["数量"].ToString();
if (model.通用设备 == "")
{
model.通用设备 = "0";
}
}
read通用设备.Close();
string sql专用设备 = string.Format(" select count(一级类别名称) as 数量 from 办公设备信息表 where 归属部门 = {0} and 一级类别名称 like '{1}' and 盘点 = 0", modelbm[i].ID, "专用设备");
SqlDataReader read专用设备 = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql专用设备.ToString());
if (read专用设备.Read())
{
model.专用设备 = read专用设备["数量"].ToString() == null ? "0" : read专用设备["数量"].ToString();
if (model.专用设备 == "")
{
model.专用设备 = "0";
}
}
read专用设备.Close();
string sql家具用具装具 = string.Format(" select count(一级类别名称) as 数量 from 办公设备信息表 where 归属部门 = {0} and 一级类别名称 like '{1}' and 盘点 = 0", modelbm[i].ID, "家具、用具、装具");
SqlDataReader read家具用具装具 = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql家具用具装具.ToString());
if (read家具用具装具.Read())
{
model.家具用具装具 = read家具用具装具["数量"].ToString() == null ? "0" : read家具用具装具["数量"].ToString();
if (model.家具用具装具 == "")
{
model.家具用具装具 = "0";
}
}
read家具用具装具.Close();
string sql文物陈列品 = string.Format(" select count(一级类别名称) as 数量 from 办公设备信息表 where 归属部门 = {0} and 一级类别名称 like '{1}' and 盘点 = 0", modelbm[i].ID, "文物陈列品");
SqlDataReader read文物陈列品 = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql文物陈列品.ToString());
if (read文物陈列品.Read())
{
model.文物陈列品 = read文物陈列品["数量"].ToString() == null ? "0" : read文物陈列品["数量"].ToString();
if (model.文物陈列品 == "")
{
model.文物陈列品 = "0";
}
}
read文物陈列品.Close();
string sql图书 = string.Format(" select count(一级类别名称) as 数量 from 办公设备信息表 where 归属部门 = {0} and 一级类别名称 like '{1}' and 盘点 = 0", modelbm[i].ID, "图书");
SqlDataReader read图书 = DBHelper.ExecuteReader(DBHelper.ConnectionString, CommandType.Text, sql图书.ToString());
if (read图书.Read())
{
model.图书 = read图书["数量"].ToString() == null ? "0" : read图书["数量"].ToString();
if (model.图书 == "")
{
model.图书 = "0";
}
}
read图书.Close();
modellist.Add(model);
}
return(modellist);
}
