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 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 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 int GetLastInsertOrderID(int?userID) { int orderId = -1; string queryLastInsertId = $@"select orderId from orders where orderUser = {userID} order by orderid desc limit 1;"; reader = DbHelper.ExecQuery(queryLastInsertId, DbHelper.OpenConnection()); if (reader.Read()) { orderId = reader.GetInt32("orderId"); } reader.Close(); return(orderId); }
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 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 int?CheckItemPurchase(int?itemId, int?userId) { string query = $@"select it.itemid from orders ord inner join orderDetails ordt on ord.orderId = ordt.orderId inner join Items it on ordt.itemId = it.itemId where it.itemId = {itemId} and ord.orderuser = {userId} and ord.orderStatus = 1 limit 1"; reader = DbHelper.ExecQuery(query, DbHelper.OpenConnection()); if (reader.Read()) { itemId = reader.GetInt32("itemid"); } else { itemId = -1; } reader.Close(); return(itemId); }
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 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); }