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