/// <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>
/// 提交订单信息
/// </summary>
/// <param name="shippingAddress">收货地址</param>
/// <param name="bookItems">用户购买的图书</param>
/// <returns>0：成功 1：失败</returns>
public int SubmitOrder(Order order)
{
//1.获取购物车中的图书 bookItems
//2.生成订单号 orderid
//3.将订单信息写入数据库
if (order != null)
{
SqlConnection sql = DataBaseOperations.CreateConnection();
string cmdText = "insert into [Order] values('" + order.orderId + "','" + order.userName + "','" +
order.shippingAddress.Address + "','" + order.shippingAddress.Remark + "','" + order.Price +
"','" + order.isValid + "','" + order.orderDate + "')";
DataBaseOperations.ReviseDataToDataBase(cmdText, sql);//将订单信息插入OrderID表中
foreach (var bookitem in order.bookItemList)
{
cmdText = "insert into [OrderDetail] values('" + order.orderId + "','" + bookitem.Book.bookID + "','" + bookitem.bookAmount + "')";
DataBaseOperations.ReviseDataToDataBase(cmdText, sql);//将订单信息插入OrderDetail表中
int bookRating = bookitem.Book.Rating + 1;
cmdText = "update [Book] set Rating='" + bookRating + "'where BookID='" + bookitem.Book.bookID + "'";
DataBaseOperations.ReviseDataToDataBase(cmdText, sql);//更新图书销量
}
sql.Close();
return(0);
}
return(1);
}
/// <summary>
/// 冻结/解冻用户账号
/// </summary>
/// <param name="customer">顾客</param>
/// <param name="state">1：冻结 </param>
/// <returns></returns>
public int RestrictOrPermitCustomerLogin(string userName, int state)
{
string cmdText1, cmdText2;
if (state == 1)
{
cmdText1 = "update Users set LegalityState=1 where Name='" + userName + "'";
cmdText2 = "update Customer set LegalityState=1 where Name='" + userName + "'";
}
else
{
cmdText1 = "update Users set LegalityState=0 where Name='" + userName + "'";
cmdText2 = "update Customer set LegalityState=0 where Name='" + userName + "'";
}
SqlConnection sql = DataBaseOperations.CreateConnection();
int state1 = DataBaseOperations.ReviseDataToDataBase(cmdText1, sql);
int state2 = DataBaseOperations.ReviseDataToDataBase(cmdText2, sql);
sql.Close();
if (state1 == 1 && state2 == 1)
{
return(0);
}
return(1);
}
/// <summary>
/// 用户注册
/// 0：成功注册
/// 1：用户已存在
/// </summary>
/// <param name="name"></param>
/// <param name="password"></param>
/// <param name="customersex"></param>
/// <param name="securityquestion"></param>
/// <param name="securityquestionanswer"></param>
/// <returns></returns>
public int Register()
{
SqlConnection sql = DataBaseOperations.CreateConnection(); //建立数据库连接
string cmdText = "select * from Users where Name='" + Name + "'";
SqlDataReader sqlDataReader = DataBaseOperations.GetSqlDataReader(cmdText, sql); //查询数据库中是否有该用户
if (sqlDataReader.HasRows) //搜索结果中有数据，说明已经注册
{
sqlDataReader.Close(); //关闭sqlDataReader(必要操作！！)
sql.Close(); //关闭sql连接
return(1); //用户名已经存在，返回1，共前台使用
}
//将用户信息写入数据库
else
{
sqlDataReader.Close();//关闭sqlDataReader(必要操作！！)
//将用户信息写入User表
cmdText = "insert into Users (Name,Password,Power,LegalityState) values ('" + Name + "','" + Password +
"','" + Powers + "','" + legalityState + "')";
DataBaseOperations.ReviseDataToDataBase(cmdText, sql);
//将用户信息写入Customer表
cmdText =
"insert into Customer (Name,Password,CustomerSex,SecurityQuestion,SecurityQuestionAnswer,LegalityState) values('" +
Name + "','" + Password + "','" + customerSex + "','" +
securityQuestion + "','" + securityQuestionAnswer + "','" + legalityState +
"')";
DataBaseOperations.ReviseDataToDataBase(cmdText, sql); //执行插入操作
sql.Close(); //关闭sql连接
return(0); //操作成功返回0，供前台使用
}
}
/// <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>
/// 从数据库中删除某本书籍
/// 成功返回0
/// 失败返回1
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public int DeleteBookFromDB(Book book)
{
SqlConnection sql = DataBaseOperations.CreateConnection();
string cmdText = "delete from Book where BookID='" + book.bookID + "'";
int state = DataBaseOperations.ReviseDataToDataBase(cmdText, sql);
sql.Close();
if (state == 1)
{
return(0);
}
return(1);
}
/// <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); //用户名不存在，提示用户注册
}
/// <summary>
/// 将书籍添加到数据库
/// 成功返回0
/// 失败返回1
/// </summary>
/// <param name="book"></param>
/// <returns></returns>
public int AddBookToDB(Book book)
{
SqlConnection sql = DataBaseOperations.CreateConnection();
string cmdText = "insert into Book values('" + book.bookID + "','" + book.bookName + "','" + book.bookAuthor +
"','" + book.bookPrice + "','" + book.bookPress + "','" + book.bookDetail + "','" +
book.bookCategory + "','" + book.bookImageURL + "','0','" + GetCurrentTime() + "','" +
book.Inventory + "')";
int state = DataBaseOperations.ReviseDataToDataBase(cmdText, sql);
sql.Close();
if (state == 1)
{
return(0);
}
return(1);
}
/// <summary>
/// 更新图书信息
/// </summary>
/// <param name="prbook"></param>
/// <param name="curbook"></param>
/// <returns></returns>
public int UpdateBook(Book curbook)
{
SqlConnection sql = DataBaseOperations.CreateConnection();
string cmdText = "update Book set BookName='" + curbook.bookName +
"',BookAuthor='" + curbook.bookAuthor + "',BookPrice='" + curbook.bookPrice +
"',BookPress='" + curbook.bookPress + "',BookDetail='" + curbook.bookDetail +
"',BookCategory='" + curbook.bookCategory + "',BookImageURL='" + curbook.bookImageURL +
"',Rating='" + curbook.Rating + "',ShelveDate='" + GetCurrentTime() + "',Inventory='" +
curbook.Inventory + "' where BookID='" + curbook.bookID + "'";
int state = DataBaseOperations.ReviseDataToDataBase(cmdText, sql);
sql.Close();
if (state == 1)
{
return(0);
}
return(1);
}
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);
}