/// <summary>
/// 查询用户信息
/// </summary>
/// <param name="userName"></param>
/// <returns></returns>
public static List <Customer> QueryUserInformation(string userName)
{
List <Customer> qureyUsers = new List <Customer>();
SqlConnection sql = DataBaseOperations.CreateConnection();//数据库连接符
string cmdText = null;
if (userName == "null")
{
cmdText = "select * from [Customer]";//查询所有用户
}
else
{
cmdText = "select * from [Customer] where Name='" + userName + "'";//查询某个用户
}
DataSet data = DataBaseOperations.GetDataSet(cmdText, sql);
for (int i = 0; i < data.Tables[0].Rows.Count; i++)
{
Customer user = new Customer(data.Tables[0].Rows[i]["Name"].ToString(),
data.Tables[0].Rows[i]["Password"].ToString(), Convert.ToInt32(data.Tables[0].Rows[i]["CustomerSex"]),
data.Tables[0].Rows[i]["SecurityQuestion"].ToString(),
data.Tables[0].Rows[i]["SecurityQuestionAnswer"].ToString(), Convert.ToInt32(data.Tables[0].Rows[i]["LegalityState"]));
qureyUsers.Add(user);
}
return(qureyUsers);
}
/// <summary>
/// 查询订单信息（所有订单
/// orderList->Order->bookItemList->Book ①
/// 思路：
/// 1.对比当前订单与之前订单的订单号
/// 2.如果订单号相同，说明图书属于一个订单，获取订单列表中的最后一个订单，然后删除（删除是为了步骤5插入订单列表时方便操作）
/// 3.如果订单号不同，说明图书不属于同一订单，则新建一个订单
/// 4.获取到订单后，根据①创建订单对象
/// 5.将订单插入到订单列表尾部
/// </summary>
/// <returns></returns>
public List <Order> QueryOrderInformation()
{
SqlConnection sql = DataBaseOperations.CreateConnection();
string preOrderId = null; //上一个订单号
string curOrderId; //当前订单号
List <Order> orderList = new List <Order>(); //订单列表
Order order; //订单
//根据当前用户名查询订单
string cmdText =
"select a.OrderID,a.UserName,a.Address,a.Remark,a.Price,a.isValid,a.OrderDate,b.BookID,b.Amount,c.BookName,c.BookAuthor,c.BookPrice,c.BookPress,c.BookDetail,c.BookCategory,c.BookImageURL,c.ShelveDate,c.Inventory,c.Rating from [Order] a inner join [OrderDetail] b on a.OrderID=b.OrderID inner join [Book] c on b.BookID=c.BookID where a.UserName='******' order by a.OrderID desc";
DataSet dataSet = DataBaseOperations.GetDataSet(cmdText, sql);
if (dataSet != null)
{
for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
curOrderId = dataSet.Tables[0].Rows[i][0].ToString(); //获取到当前订单id
ShippingAddress shippingAddress = new ShippingAddress(dataSet.Tables[0].Rows[i][2].ToString(), dataSet.Tables[0].Rows[i][3].ToString()); //创建收货对象
//创建book对象
Book aBook = new Book(dataSet.Tables[0].Rows[i][7].ToString(),
dataSet.Tables[0].Rows[i][9].ToString(), dataSet.Tables[0].Rows[i][10].ToString(),
Convert.ToDecimal(dataSet.Tables[0].Rows[i][11]), dataSet.Tables[0].Rows[i][12].ToString(),
dataSet.Tables[0].Rows[i][13].ToString(), dataSet.Tables[0].Rows[i][14].ToString(),
dataSet.Tables[0].Rows[i][15].ToString(),
Convert.ToInt32(dataSet.Tables[0].Rows[i][17]), Convert.ToInt32(dataSet.Tables[0].Rows[i][18]), dataSet.Tables[0].Rows[i][16].ToString());
//创建bookItem对象（book对象，book的数量）
BookItem bookItem = new BookItem(aBook, Convert.ToInt32(dataSet.Tables[0].Rows[i][8]));
//图书来自不同的订单
if (!isFromSameOrder(preOrderId, curOrderId))
{
order = new Order();//新建订单，初始化bookItemList
//不同的订单需要设置 OrerId/userName/orderPrice/isValid/oderDate/shippingAddress
//相同订单的信息已经设置过了，不需要重新设置
order.SetOrderInformation(dataSet.Tables[0].Rows[i][0].ToString(),
dataSet.Tables[0].Rows[i][1].ToString(), Convert.ToDecimal(dataSet.Tables[0].Rows[i][4]),
Convert.ToInt32(dataSet.Tables[0].Rows[i][5]), dataSet.Tables[0].Rows[i][6].ToString(),
shippingAddress);
}
else
{
order = orderList[orderList.Count - 1]; //获取到订单列表的最后一个订单
orderList.RemoveAt(orderList.Count - 1); //删除这个订单信息
}
order.bookItemList.Add(bookItem); //将bookItem添加到bookItemList中
orderList.Add(order); //将order添加到orderList中
//设置订单的OrderID/UserName/价格/状态/OrderDate/ShippingAddres
preOrderId = curOrderId;//处理完一个订单后，将curOrderId赋值给preOrderId
}
}
sql.Close();//关闭sql连接
return(orderList);
}
/// <summary>
/// 用户登录
/// </summary>
/// <returns>0：用户密码正确，账号未被冻结，并且提取相应的权限值</returns>
/// <returns>1：用户密码错误</returns>
/// <returns>2：用户尚未注册</returns>
/// <returns>3：账号被冻结</returns>
public int Login()
{
SqlConnection sql = DataBaseOperations.CreateConnection();
string cmdText = "select * from Users where Name='" + Name + "'";
SqlDataReader sqlDataReader = DataBaseOperations.GetSqlDataReader(cmdText, sql);
//如果查询到该用户，匹配用户名/密码
if (sqlDataReader.HasRows) //根据查询结果里面的行数判断是否有该用户
{
sqlDataReader.Close(); //关闭sqlDataReader，必要操作
SqlDataAdapter sqlDataAdapter = DataBaseOperations.GetSqlDataAdapter(cmdText, sql);
DataSet dataSet = DataBaseOperations.GetDataSet(sqlDataAdapter);
//对用户密码进行匹配
if (dataSet.Tables[0].Rows[0]["Password"].ToString() == Password)
{
string power = dataSet.Tables[0].Rows[0]["Power"].ToString();//获取数据库中用户权限（string）
//将string权限转换为Power类型
switch (power)
{
case "Customer":
Powers = Power.Customer;
break;
case "Manager":
Powers = Power.Manager;
break;
}
if (Convert.ToInt32(dataSet.Tables[0].Rows[0]["LegalityState"]) == 0)
{
sql.Close(); //关闭sql连接
return(0); //用户密码正确，账号未被冻结，提示前台进行相应的操作
}
else
{
sql.Close(); //关闭sql连接
return(3); //账号被冻结，提示前台进行相应的操作
}
}
else
{
sql.Close(); //关闭sql连接
return(1); //用户密码错误，提示前台进行相应的操作
}
}
sql.Close(); //关闭sql连接
return(2); //用户名不存在，提示用户注册
}
public static List <Book> NewBook()
{
List <Book> newBook = new List <Book>(); //推荐书籍List
SqlConnection sql = DataBaseOperations.CreateConnection(); //sql连接符
string cmdText = "select * from Book order by ShelveDate desc "; //按照销量进行选择
DataSet data = DataBaseOperations.GetDataSet(cmdText, sql); //获取数据
sql.Close(); //关闭连接符
for (int i = 0; i < data.Tables[0].Rows.Count; i++)
{
Book tempBook = new Book(data.Tables[0].Rows[i]["BookID"].ToString(),
data.Tables[0].Rows[i]["BookName"].ToString(), data.Tables[0].Rows[i]["BookAuthor"].ToString(),
Convert.ToDecimal(data.Tables[0].Rows[i]["BookPrice"]),
data.Tables[0].Rows[i]["BookPress"].ToString(), data.Tables[0].Rows[i]["BookDetail"].ToString(),
data.Tables[0].Rows[i]["BookCategory"].ToString(), data.Tables[0].Rows[i]["BookImageURL"].ToString(),
Convert.ToInt32(data.Tables[0].Rows[i]["Inventory"].ToString()),
Convert.ToInt32(data.Tables[0].Rows[i]["Rating"]), data.Tables[0].Rows[i]["ShelveDate"].ToString());
newBook.Add(tempBook);
}
return(newBook);
}
/// <summary>
/// 根据用户输入的条件查询书籍
/// </summary>
/// <param name="queryConditions"></param>
/// <returns>List-Book对象</returns>
public static List <Book> QueryBookFromDb(string queryConditions)
{
List <Book> queryBooks = new List <Book>();
string cmdText = null;
SqlConnection sql = DataBaseOperations.CreateConnection();//连接数据库
//判定queryConditions查询条件是否为空
if (queryConditions != null)
{
cmdText = QueryBook.SqlCmdText(queryConditions);
}
DataSet data = DataBaseOperations.GetDataSet(cmdText, sql);//获取DataSet
//遍历dataset，将图书添加到queryBooks列表中
if (data != null)
{
for (int i = 0; i < data.Tables[0].Rows.Count; i++)
{
Book book = new Book();
book.bookID = data.Tables[0].Rows[i][0].ToString();
book.bookName = data.Tables[0].Rows[i][1].ToString();
book.bookAuthor = data.Tables[0].Rows[i][2].ToString();
book.bookPrice = Convert.ToDecimal(data.Tables[0].Rows[i][3]);
book.bookPress = data.Tables[0].Rows[i][4].ToString();
book.bookDetail = data.Tables[0].Rows[i][5].ToString();
book.bookCategory = data.Tables[0].Rows[i][6].ToString();
book.bookImageURL = data.Tables[0].Rows[i][7].ToString();
book.Rating = Convert.ToInt32(data.Tables[0].Rows[i][8]);
book.Inventory = Convert.ToInt32(data.Tables[0].Rows[i][10]);
queryBooks.Add(book);//将图书添加到queryBooks列表中
}
}
sql.Close();
return(queryBooks);
}