public Rating CheckItemRatedByUserId(int?userId, int?itemId) { if (userId == null) { return(null); } query = $@"SELECT * FROM ratings where userId ={userId} and itemId = {itemId};"; try { reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); } catch (System.Exception) { Console.WriteLine("Không thể kết nối tới cơ sở dữ liệu"); return(null); } Rating rating = null; if (reader.Read()) { rating = GetRating(reader); } reader.Close(); DbHelper.CloseConnection(); return(rating); }
public User GetUserByUserNameAndPassWord(string username, string password) { if ((username == null) || (password == null)) { return(null); } Regex regex = new Regex("[a-zA-Z0-9_]"); MatchCollection matchCollectionUserName = regex.Matches(username); MatchCollection matchCollectionPassword = regex.Matches(password); if (matchCollectionUserName.Count < username.Length || matchCollectionPassword.Count < password.Length) { return(null); } query = $@"select * from Users where userAccount = '{username}' and userPassword = '******'"; try { reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); } catch (System.Exception) { Console.WriteLine("Không thể kết nối tới cơ sở dữ liệu"); return(null); } User user = null; if (reader.Read()) { user = GetUser(reader); } reader.Close(); DbHelper.CloseConnection(); return(user); }
public List <Item> ShowShopingCartByUserId(int?userId) { if (userId == null) { return(null); } List <Item> listItems = new List <Item>(); query = $@"select it.itemId, it.itemName, it.itemPrice from orders ord inner join orderDetails ordt on ord.orderId = ordt.orderId inner join Items it on ordt.itemId = it.itemId where ord.orderUser = {userId} and ord.orderStatus = 0 ;"; try { reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); } catch (System.Exception) { Console.WriteLine("Không thể kết nối tới cơ sở dữ liệu"); return(null); } while (reader.Read()) { listItems.Add(GetItemShoppingCart(reader)); } DbHelper.CloseConnection(); return(listItems); }
public bool UpdateRateItem(Rating rating) { if (rating == null) { return(false); } query = $@"UPDATE Ratings SET ratingStars = {rating.RatingStars}, ratingTitle = '{rating.RatingTitle}', ratingContent = '{rating.RatingContent}', ratingDate = NOW() WHERE itemID = {rating.ItemId} and userID = {rating.UserId};"; try { int numberEffect = DbHelper.ExecNonQuery(query, DbHelper.OpenConnection()); if (numberEffect == 0) { return(false); } } catch (System.Exception) { return(false); } finally { DbHelper.CloseConnection(); } return(true); }
public List <Order> ShowAllItemOrdered(int?userId) { if (userId == null) { return(null); } List <Order> listOrders = new List <Order>(); query = $@"select it.itemId, it.itemName, ord.orderDate from orders ord inner join orderDetails ordt on ord.orderId = ordt.orderId inner join Items it on ordt.itemId = it.itemId where ord.orderUser = {userId} and ord.orderStatus = 1 group by it.itemName"; try { reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); } catch (System.Exception) { Console.WriteLine("Không thể kết nối tới cơ sở dữ liệu"); return(null); } while (reader.Read()) { listOrders.Add(GetOrder(reader)); } DbHelper.CloseConnection(); return(listOrders); }
public int GetTotalPage() { query = @"select count(*) / 10 from items;"; var command = new MySqlCommand(query, DbHelper.OpenConnection()); int count = Convert.ToInt32(command.ExecuteScalar()); DbHelper.CloseConnection(); return(count); }
public List <Item> PagingItems(int pageNo, int itemPerPAge) { DbHelper.OpenConnection(); query = $@"select * from items limit {pageNo},{itemPerPAge}"; List <Item> items = new List <Item>(); reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); while (reader.Read()) { items.Add(GetItem(reader)); } reader.Close(); DbHelper.CloseConnection(); return(items); }
public bool DeleteItemInShoppingCartByIdItem(int?itemId) { if (itemId == null) { return(false); } query = $@"DELETE FROM orderDetails where itemId = {itemId};"; MySqlConnection connection = DbHelper.OpenConnection(); if (DbHelper.ExecNonQuery(query, connection) == 0) { DbHelper.CloseConnection(); return(false); } DbHelper.CloseConnection(); return(true); }
public User GetUserById(int?userId) { if (userId == null) { return(null); } query = $@"select * from Users where userId = {userId};"; reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); User user = null; if (reader.Read()) { user = GetUser(reader); } reader.Close(); DbHelper.CloseConnection(); return(user); }
public List <Rating> GetAllRating(int?itemId) { if (itemId == null) { return(null); } query = $@"select * from ratings where itemId = {itemId}"; reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); List <Rating> listRatings = new List <Rating>(); while (reader.Read()) { listRatings.Add(GetRating(reader)); } DbHelper.CloseConnection(); return(listRatings); }
public Order GetLastOrderIdPurchase(int?userId) { if (userId == null) { return(null); } Order order = null; query = $@"select max(orderId) from orders where orderUser = {userId} "; reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); if (reader.Read()) { order = GetOrder(reader); } reader.Close(); DbHelper.CloseConnection(); return(order); }
public Item GetAnItemById(int?itemId) { if (itemId == null) { return(null); } DbHelper.OpenConnection(); query = $"select * from items where itemId = {itemId}"; reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); Item item = null; if (reader.Read()) { item = GetItem(reader); } reader.Close(); DbHelper.CloseConnection(); return(item); }
public bool UpdateStatusShoppingCartById(bool isHave, int?userId) { if (userId == null) { return(false); } switch (isHave) { case true: query = $@"update Users set userShoppingCart = false where userId = {userId}"; break; case false: query = $@"update Users set userShoppingCart = true where userId = {userId}"; break; } DbHelper.ExecNonQuery(query, DbHelper.OpenConnection()); DbHelper.CloseConnection(); return(true); }
public List <Order> ShowOrderUserPaySucess(int?userId) { if (userId == null) { return(null); } List <Order> orders = new List <Order>(); query = $@"select ord.orderId as orderId, ord.orderDate, it.itemId ,it.itemName, it.itemPrice, us.userName, us.userEmail from users us inner join orders ord on ord.orderUser = us.userId inner join orderDetails ordt on ord.orderId = ordt.orderId inner join Items it on ordt.itemId = it.itemId where ord.orderUser = {userId} and ord.orderId = {GetLastInsertOrderID(userId)}"; reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); while (reader.Read()) { orders.Add(GetOrderPurchaseSucess(reader)); } reader.Close(); DbHelper.CloseConnection(); return(orders); }
public List <Item> SearchITem(int temp) { DbHelper.OpenConnection(); switch (temp) { case 1: query = $"select * from items where itemId = "; break; } reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); List <Item> items = new List <Item>(); while (reader.Read()) { items.Add(GetItem(reader)); } reader.Close(); DbHelper.CloseConnection(); return(items); }
public List <Item> SearchItemName() { query = @"select * from items;"; List <Item> items = new List <Item>(); try { reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); } catch (System.Exception) { Console.WriteLine("Không thể kết nối tới cơ sở dữ liệu"); return(null); } while (reader.Read()) { items.Add(GetItem(reader)); } reader.Close(); DbHelper.CloseConnection(); return(items); }
public bool RateItem(Rating rating) { if (rating == null) { return(false); } query = $@"insert into Ratings values ({rating.ItemId},{rating.UserId},'{rating.RatingStars}','{rating.RatingTitle}','{rating.RatingContent}',NOW());"; try { DbHelper.ExecNonQuery(query, DbHelper.OpenConnection()); } catch (System.Exception) { return(false); } finally { DbHelper.CloseConnection(); } return(true); }