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();
}
