public static DataTable Get_userable()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("select * from user_info");
return(DBhelp.GetDataTable(stringBuilder.ToString()));
}
public static DataTable Get_fanview()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("select Machine_id,数据载入时间,功率,额定风速,扫风面积,抗最大风速 from machine_data");
return(DBhelp.GetDataTable(stringBuilder.ToString()));
}
public static DataTable ReaderInfoManage(string reader_id)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("select * from reader_info where reader_id LIKE '%");
stringBuilder.Append(reader_id + "%'");
return(DBhelp.GetDataTable(stringBuilder.ToString()));
}
public static DataTable Statistic()
{
StringBuilder statistic = new StringBuilder();
statistic.Append("select * from lend_list");
DataTable data = DBhelp.GetDataTable(statistic.ToString());
return(data);
}
//查询施工状况
public static DataTable GetAccomplish()
{
StringBuilder querystr = new StringBuilder();
querystr.Append("select Meter_id as 维护机器ID,isnull(convert(varchar(50),DATEDIFF(DY,开始维护时间,convert(date,GETDATE(),23))),'未开工') as 已施工天数 from config where Meter_malfunc = 0");
DataTable dt = DBhelp.GetDataTable(querystr.ToString());
return(dt);
}
public static DataTable GetPlan()
{
StringBuilder querystr = new StringBuilder();
querystr.Append("select Meter_id as 维护机器ID, isnull(convert(varchar(50),计划维护时长),'故障还未申报') as 计划维护时长,isnull(convert(varchar(50),开始维护时间),' ') as 开始维护时间 from config where Meter_malfunc = 0");
DataTable dt = DBhelp.GetDataTable(querystr.ToString());
return(dt);
}
//查询config中mal为0的所有数据
public static DataTable GetMainTainPlan()
{
StringBuilder querystr = new StringBuilder();
querystr.Append("select Meter_id as 机器ID,Meter_type as 机器类型,Meter_addr as 所处位置,Meter_malfunc as 是否故障 from config");
DataTable dt = DBhelp.GetDataTable(querystr.ToString());
return(dt);
}
public static DataTable GetOrder()
{
string querystr = "select sernum,supplier_info.Supplier_name,component_info.Component_name,Order_num " +
"from order_list " +
"left join supplier_info on order_list.Supplier_id = supplier_info.Supplier_id " +
"left join component_info on order_list.Component_id = component_info.Component_id";
DataTable dt = DBhelp.GetDataTable(querystr);
return(dt);
}
public static DataTable UserTop()
{
StringBuilder userTop = new StringBuilder();
userTop.Append("SELECT top(5) reader_info.name as '用户名',count(reader_info.name) as '借书最多的用户' " +
"FROM reader_info LEFT JOIN lend_list ON reader_info.reader_id = lend_list.reader_id " +
"group by reader_info.name " +
"order by 借书最多的用户 desc");
DataTable data = DBhelp.GetDataTable(userTop.ToString());
return(data);
}
public static DataTable BookTop()
{
StringBuilder bookTop = new StringBuilder();
bookTop.Append("SELECT top(5) book_info.name as '书名',count(book_info.name) as '被借最多的书'" +
" FROM book_info LEFT JOIN lend_list ON book_info.book_id = lend_list.book_id " +
"group by book_info.name " +
"order by 被借最多的书 desc");
DataTable data = DBhelp.GetDataTable(bookTop.ToString());
return(data);
}
//查询供应商信息
public static DataTable QuerySupplier(string sup_id, string sup_name, string sup_addr)
{
StringBuilder querySup = new StringBuilder();
querySup.Append("select * from supplier_info where ");
querySup.Append("Supplier_id LIKE '%");
querySup.Append(sup_id);
querySup.Append("%' and Supplier_name LIKE '%");
querySup.Append(sup_name);
querySup.Append("%' and Supplier_addr LIKE '%");
querySup.Append(sup_addr + "%'");
DataTable dt = DBhelp.GetDataTable(querySup.ToString());
return(dt);
}
//查询书籍信息
public static DataTable QueryBook(string book_id, string author, string name, string press)
{
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("select * from book_info where ");
queryBuilder.Append("book_id LIKE '%");
queryBuilder.Append(book_id);
queryBuilder.Append("%' and author LIKE '%");
queryBuilder.Append(author);
queryBuilder.Append("%' and name LIKE '%");
queryBuilder.Append(name);
/*queryBuilder.Append("' and name = '");
* queryBuilder.Append(name);*/
queryBuilder.Append("%' and publish LIKE '%");
queryBuilder.Append(press + "%'");
DataTable data = DBhelp.GetDataTable(queryBuilder.ToString());
return(data);
}