/// <summary>
/// 获取留言板总数信息
/// </summary>
/// <returns></returns>
public static List <MBCategories> GetMBCategories()
{
const string sql = "SELECT UserMessageBoard.CategoriesId,MessageBoardCategories.CategoriesTitle,count(*) as CategoriesSum " +
" FROM MessageBoardCategories " +
" INNER JOIN UserMessageBoard " +
" ON UserMessageBoard.CategoriesId = MessageBoardCategories.CategoriesId " +
" GROUP BY UserMessageBoard.CategoriesId,MessageBoardCategories.CategoriesTitle " +
" ORDER BY CategoriesSum DESC ";
List <MBCategories> mbCategoriesList = new List <MBCategories>();
DataTable reader = SqlDBHelper.GetDataTable(sql);
if (reader != null)
{
for (int i = 0; i < reader.Rows.Count; i++)
{
MBCategories mbCategories = new MBCategories();
mbCategories.cetegoriesId = (int)reader.Rows[i]["CategoriesId"];
mbCategories.cetegoriesTitle = (string)reader.Rows[i]["CategoriesTitle"];
mbCategories.sum = (int)reader.Rows[i]["CategoriesSum"];
mbCategoriesList.Add(mbCategories);
}
reader.Dispose();
return(mbCategoriesList);
}
else
{
return(null);
}
}
public static DataTable GetMessageBoardIn()
{
const string sql = "SELECT UserMessageBoard.MessageBoardId,[Users].[Name], UserMessageBoard.[Title],UserMessageBoard.[Article],UserMessageBoard.[URL], UserMessageBoard.[ComTime], MessageBoardCategories.[CategoriesTitle]"
+ "FROM [UserMessageBoard] INNER JOIN [Users] ON UserMessageBoard.[Id] = Users.[Id] INNER JOIN [MessageBoardCategories] ON UserMessageBoard.[CategoriesId] = MessageBoardCategories.[CategoriesId]";
return(SqlDBHelper.GetDataTable(sql));
}
/// <summary>
/// 加载FormBorrow窗体的DATATable表格
/// </summary>
/// <returns></returns>
public DataTable dataBorrowLoad()
{
string StrSql = @"select ID,borrow.F_proID,GoodsName,PropertyClass.CalssName,Borrow_sum,NowDate,Borrow_Date,Predict_date,Predict_money,borrow.Money,Company,CustomerID,borrow.Detail
from Borrow, Goods, PropertyClass where borrow.F_proID = Goods.F_proID and PropertyClass.ClassID = Goods.ClassID";
return(SqlDBHelper.GetDataTable(StrSql));
}
public DataTable ItemLoad(string id)
{
string StrSql = @"select F_proID,GoodsID,GoodsName,SumOld,CalssName,OldInfo,Type,MakeName,OldMonth,IntoDate,RepName,Sub_Month,DepName,State,Month_Money,AddType,Quantity ,JingCanZhi,OrderUnit,Price,Detail,Money,Pro_value
from Goods,Department,PropertyClass,Repertory where Goods.DepID=Department.DepID and Goods.ClassID=PropertyClass.ClassID and Repertory.RepID=Goods.RepID and GoodsName='" + id + "'";
return(SqlDBHelper.GetDataTable(StrSql));
}
public static DataTable GetAllUsersInfo()
{
const string sql = "SELECT dbo.[Users].[Id], dbo.[Users].[LoginId], dbo.[Users].[Name], dbo.[Users].[Address], dbo.[Users].[Phone], dbo.[Users].[Mail], dbo.UserRoles.[Name] AS RolesName, dbo.UserStates.[Name] AS StatesName, dbo.[Users].LastOnlineTime, dbo.[Users].[PersonalSynopsis]"
+ "FROM dbo.[Users] INNER JOIN dbo.UserRoles ON dbo.[Users].[UserRoleId] = dbo.[UserRoles].[Id] INNER JOIN dbo.[UserStates] ON dbo.[Users].[UserRoleId] = dbo.UserStates.[Id]";
return(SqlDBHelper.GetDataTable(sql));
}
public DataTable SelectListByWhere(string strWhere)
{
string StrSql = "SELECT * FROM UserInfo ";
if (strWhere != "")
{
StrSql += " where " + strWhere;
}
return(SqlDBHelper.GetDataTable(StrSql));
}
public DataTable SetPro(string id)
{
string StrSql = @"select PropertyClass.CalssName, GoodsName,ReduceGoods.ID,ReduceGoods.F_proID,Reduce_Sum,Mode,Reduce_date,Why,ClearIncome,ClearCost ,CustomerName,ReduceGoods.Detail from ReduceGoods,Goods,Customer,PropertyClass where ReduceGoods.F_proID=Goods.F_proID and Customer.CustomerID=reduceGoods.CustomerID and PropertyClass.ClassID=Goods.ClassID";
DataTable dt = SqlDBHelper.GetDataTable(StrSql);
foreach (DataRow item in dt.Rows)
{
if (id == item["CalssName"].ToString())
{
StrSql = " select GoodsName,ReduceGoods.ID,ReduceGoods.F_proID,Reduce_Sum,Mode,Reduce_date,Why,ClearIncome,ClearCost ,CustomerName, PropertyClass.CalssName,ReduceGoods.Detail from ReduceGoods,Goods,Customer,PropertyClass where ReduceGoods.F_proID=Goods.F_proID and Customer.CustomerID=reduceGoods.CustomerID and PropertyClass.ClassID=Goods.ClassID and CalssName ='" + id + "'";
}
else
{
StrSql = " select GoodsName,ReduceGoods.ID,ReduceGoods.F_proID,Reduce_Sum,Mode,Reduce_date,Why,ClearIncome,ClearCost ,CustomerName,PropertyClass.CalssName,ReduceGoods.Detail from ReduceGoods,Goods,Customer,PropertyClass where ReduceGoods.F_proID=Goods.F_proID and Customer.CustomerID=reduceGoods.CustomerID and PropertyClass.ClassID=Goods.ClassID and GoodsName ='" + id + "'";
}
}
return(SqlDBHelper.GetDataTable(StrSql));
}
public DataTable ServiceLoad(string id)
{
string StrSql = @"select ID,GoodsName,CalssName,Service_sum,Service_Date,Predict_date,NowDate,Cost,NowCost,Company,Breakdown,Service_txt,Service.Detail
from Service,Goods,PropertyClass where Goods.F_proID=Service.F_proID and PropertyClass.ClassID=Goods.ClassID";
DataTable dt = SqlDBHelper.GetDataTable(StrSql);
foreach (DataRow item in dt.Rows)
{
if (id == item["CalssName"].ToString())
{
StrSql = @"select ID,GoodsName,CalssName,Service_sum,Service_Date,Predict_date,NowDate,Cost,NowCost,Company,Breakdown,Service_txt,Service.Detail
from Service, Goods, PropertyClass where Goods.F_proID = Service.F_proID and PropertyClass.ClassID = Goods.ClassID and CalssName='" + id + "'";
}
else
{
StrSql = @"select ID,GoodsName,CalssName,Service_sum,Service_Date,Predict_date,NowDate,Cost,NowCost,Company,Breakdown,Service_txt,Service.Detail
from Service, Goods, PropertyClass where Goods.F_proID = Service.F_proID and PropertyClass.ClassID = Goods.ClassID and GoodsName='" + id + "'";
}
}
return(SqlDBHelper.GetDataTable(StrSql));
}
public DataTable BorrowdataLoad(string id)
{
string StrSql = string.Format(@"select ID,borrow.F_proID,GoodsName,PropertyClass.CalssName,NowDate,Borrow_sum,Borrow_Date,Predict_date,Predict_money,borrow.Money,Company,CustomerID,borrow.Detail
from Borrow,Goods,PropertyClass where borrow.F_proID=Goods.F_proID and PropertyClass.ClassID=Goods.ClassID");
DataTable dt = SqlDBHelper.GetDataTable(StrSql);
foreach (DataRow item in dt.Rows)
{
if (id == item["CalssName"].ToString())
{
StrSql = string.Format(@"select ID,borrow.F_proID,GoodsName,PropertyClass.CalssName,NowDate,Borrow_sum,Borrow_Date,Predict_date,Predict_money,borrow.Money,Company,CustomerID,borrow.Detail
from Borrow,Goods,PropertyClass where borrow.F_proID=Goods.F_proID and PropertyClass.ClassID=Goods.ClassID and
CalssName = '{0}'", id);
}
else
{
StrSql = string.Format(@"select ID,borrow.F_proID,GoodsName,PropertyClass.CalssName,NowDate,Borrow_sum,Borrow_Date,Predict_date,Predict_money,borrow.Money,Company,CustomerID,borrow.Detail
from Borrow,Goods,PropertyClass where borrow.F_proID=Goods.F_proID and PropertyClass.ClassID=Goods.ClassID and
GoodsName = '{0}'", id);
}
}
return(SqlDBHelper.GetDataTable(StrSql));
}
public DataTable SelectListByWhere(string strWhere)
{
string StrSql = "select * from Department";
return(SqlDBHelper.GetDataTable(StrSql));
}
public static DataTable GetCategories()
{
const string sql = "SELECT * FROM MessageBoardCategories";
return(SqlDBHelper.GetDataTable(sql));
}
public DataTable comDepID()
{
string StrSql = "select Department.DepID, DepName from Customer,Department where Customer.DepID=Department.DepID";
return(SqlDBHelper.GetDataTable(StrSql));
}
/// <summary>
/// 按时间搜索
/// </summary>
public DataTable dateCilct(string date, string deta)
{
string StrSql = " select * from Borrow where NowDate>'" + date + "' and NowDate<'" + deta + "'";
return(SqlDBHelper.GetDataTable(StrSql));
}
public static DataTable GetMessageBoard()
{
const string sql = "SELECT * FROM UserMessageBoard";
return(SqlDBHelper.GetDataTable(sql));
}
public DataTable SelectListByWhere(string strWhere)
{
string StrSql = "select * from PropertyClass";
return(SqlDBHelper.GetDataTable(StrSql));
}
public DataTable role()
{
string StrSql = "select Role.RoleID, RoleName from Customer,Role where Customer.RoleID=Role.RoleID";
return(SqlDBHelper.GetDataTable(StrSql));
}
public DataTable SelectListByWhere(string strWhere)
{
string StrSql = string.Format("select * from Role");
return(SqlDBHelper.GetDataTable(StrSql));
}
public DataTable dataLoad()
{
string StrSql = " select GoodsName,ReduceGoods.ID,ReduceGoods.F_proID,Reduce_Sum,Mode,Reduce_date,Why,ClearIncome,ClearCost ,CustomerName,ReduceGoods.Detail from ReduceGoods,Goods,Customer where ReduceGoods.F_proID=Goods.F_proID and Customer.CustomerID=reduceGoods.CustomerID";
return(SqlDBHelper.GetDataTable(StrSql));
}
public static DataTable GetRoles()
{
const string sql = "SELECT * FROM UserRoles";
return(SqlDBHelper.GetDataTable(sql));
}
/// <summary>
/// 查询部门
/// </summary>
/// <returns></returns>
public DataTable comDepIDLoad()
{
string StrSql = "select * from Department";
return(SqlDBHelper.GetDataTable(StrSql));
}
/// <summary>
/// 资产分类
/// </summary>
/// <returns></returns>
public DataTable comClassID()
{
string StrSql = "select * from PropertyClass";
return(SqlDBHelper.GetDataTable(StrSql));
}
public DataTable BorrowLoad(string id)
{
string StrSql = "select F_proID from Goods where GoodsName='" + id + "'";
return(SqlDBHelper.GetDataTable(StrSql));
}
/// <summary>
/// 折旧方法
/// </summary>
/// <returns></returns>
public DataTable comOldInfo()
{
string StrSql = "select * from Repertory";
return(SqlDBHelper.GetDataTable(StrSql));
}