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 List <Items> GetItemsByID(int?ItemID) { if (ItemID == null) { return(null); } if (connection == null) { connection = DbHelper.OpenConnection(); } if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } query = $"select * from Items;"; MySqlCommand command = new MySqlCommand(query, connection); List <Items> items = null; using (reader = command.ExecuteReader()) { items = new List <Items>(); while (reader.Read()) { items.Add(GetItem(reader)); } } connection.Close(); return(items); }
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 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 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 bool CreateShoppingCart(Order order) { bool result = false; if (order == null) { return(result); } MySqlConnection connection = DbHelper.OpenConnection(); MySqlCommand command = connection.CreateCommand(); command.CommandText = @"lock tables Orders write, Items write, OrderDetails write"; command.ExecuteNonQuery(); MySqlTransaction transaction = connection.BeginTransaction(); command.Transaction = transaction; try { command.CommandText = "insert into Orders(orderUser,orderStatus) values (@userId,@OrderStatus)"; command.Parameters.AddWithValue("@userId", order.OrderUser.UserId); command.Parameters.AddWithValue("@OrderStatus", order.OrderStatus); command.ExecuteNonQuery(); string queryLastInsertId = $@"select orderId from orders where orderUser = {order.OrderUser.UserId} order by orderid desc limit 1;"; MySqlCommand selectLastId = new MySqlCommand(queryLastInsertId, connection); using (reader = selectLastId.ExecuteReader()) { if (reader.Read()) { order.OrderId = reader.GetInt32("orderId"); } } command.Parameters.Clear(); command.CommandText = "insert into OrderDetails(orderId,itemId) values (@orderId,@itemId)"; command.Parameters.AddWithValue("@orderId", order.OrderId); command.Parameters.AddWithValue("@itemId", order.OrderItem.ItemId); command.ExecuteNonQuery(); transaction.Commit(); result = true; } catch (System.Exception e) { transaction.Rollback(); Console.WriteLine(e); return(result); } finally { command.CommandText = "unlock tables"; command.ExecuteNonQuery(); connection.Close(); } return(result); }
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 bool CreateOrder(Order order) { bool result = false; if (order == null) { return(result); } MySqlConnection connection = DbHelper.OpenConnection(); MySqlCommand command = connection.CreateCommand(); command.CommandText = @"lock tables Users write, Orders write, Items write, OrderDetails write"; command.ExecuteNonQuery(); MySqlTransaction transaction = connection.BeginTransaction(); command.Transaction = transaction; try { string queryLastInsertId = $@"select orderId from orders where orderUser = {order.OrderUser.UserId} order by orderid desc limit 1;"; MySqlCommand selectLastId = new MySqlCommand(queryLastInsertId, connection); using (reader = selectLastId.ExecuteReader()) { if (reader.Read()) { order.OrderId = reader.GetInt32("orderId"); } } command.Parameters.Clear(); command.CommandText = $@"UPDATE Orders SET orderStatus = 1, orderDate = NOW() where orderUser = {order.OrderUser.UserId} and orderId = {order.OrderId};"; command.ExecuteNonQuery(); command.Parameters.Clear(); command.CommandText = $@"UPDATE Users SET userBalance = {order.OrderUser.UserBalance} where userId = {order.OrderUser.UserId}"; command.ExecuteNonQuery(); transaction.Commit(); result = true; } catch (System.Exception e) { transaction.Rollback(); Console.WriteLine(e); return(result); } finally { command.CommandText = "unlock tables"; command.ExecuteNonQuery(); connection.Clone(); } return(result); }
public Customers Login(string userName, string password) { if (userName == null || password == null) { return(null); } Regex regex = new Regex("[a-zA-Z0-9_]"); MatchCollection matchCollectionCustomersname = regex.Matches(userName); MatchCollection matchCollectionPassword = regex.Matches(password); if (matchCollectionCustomersname.Count < userName.Length || matchCollectionPassword.Count < password.Length) { return(null); } try { if (connection == null) { connection = DbHelper.OpenConnection(); } else if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } } catch (System.Exception) { return(null); } query = @"select * from Customers where userName = '******' and userPassword = '******';"; MySqlCommand command = new MySqlCommand(query, connection); Customers customer = null; using (reader = command.ExecuteReader()) { if (reader.Read()) { customer = GetCustomer(reader); } } connection.Close(); // if (user != null) // { // CinemaDAL cinemaDAL = new CinemaDAL(); // Cinema cine = cinemaDAL.GetCinemaByCineId(user.Cine.CineId); // } return(customer); }
public bool DeleteAllItemInShoppingCartByUserID(int?userId) { bool result = false; int orderId = -1; MySqlConnection connection = DbHelper.OpenConnection(); MySqlCommand command = connection.CreateCommand(); command.CommandText = @"lock tables Users write, Orders write, Items write, OrderDetails write"; command.ExecuteNonQuery(); MySqlTransaction transaction = connection.BeginTransaction(); command.Transaction = transaction; try { string queryLastInsertId = $@"select max(orderId) as orderId from orders where orderUser = {userId} order by orderid desc limit 1;"; MySqlCommand selectLastId = new MySqlCommand(queryLastInsertId, connection); using (reader = selectLastId.ExecuteReader()) { if (reader.Read()) { orderId = reader.GetInt32("orderId"); } } command.Parameters.Clear(); command.CommandText = $@"DELETE FROM orderDetails where orderId = {orderId}"; command.ExecuteNonQuery(); command.Parameters.Clear(); command.CommandText = $@"DELETE FROM orders where orderId = {orderId}"; command.ExecuteNonQuery(); transaction.Commit(); result = true; } catch (System.Exception e) { transaction.Rollback(); Console.WriteLine(e); return(result); } finally { command.CommandText = "unlock tables"; command.ExecuteNonQuery(); connection.Clone(); } return(result); }
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 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 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 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); }
public ItemsDAL() { connection = DbHelper.OpenConnection(); }
// OrderDetailDAL orderDetailDAL = new OrderDetailDAL(); public OrderDAL() { connection = DbHelper.OpenConnection(); }
public CustomersDAL() { connection = DbHelper.OpenConnection(); }