// private Mobile GetMobileInfo(MySqlDataReader reader)
// {
// Mobile mobi = new Mobile();
// mobi.MobileID = reader.GetInt32("Mobile_id");
// mobi.MobileName = reader.GetString("Mobile_Name");
// mobi.MobilePrice = reader.GetDecimal("Mobile_price");
// return mobi;
// }
public List <Order> OrderHistory(int Customer_id)
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
query = $@"select ord.Order_id as Order_id,cust.Customer_Username,cust.Customer_email,mb.Mobile_id,mb.Mobile_Name,mb.Mobile_Price,
ordt.Order_date,ordt.Amount from Customer cust inner join Orders ord on ord.Customer_order = cust.Customer_id inner join Orderdetails ordt on ord.Order_id = ordt.Order_id
inner join Mobile mb on ordt.Mobile_id = mb.Mobile_id where ord.Customer_order = {Customer_id};";
MySqlCommand command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
List <Order> ListOrders = new List <Order>();
if (reader == null)
{
System.Console.WriteLine("Bạn chưa từng đặt hàng\n");
}
else
{
while (reader.Read())
{
ListOrders.Add(GetOrdered(reader));
}
}
reader.Close();
DatabaseAccess.CloseConnection();
return(ListOrders);
}
public bool CreatOrder(Order order)
{
bool result = false;
if (order == null)
{
return(result);
}
MySqlCommand command = connection.CreateCommand();
command.CommandText = @"Lock tables Orders write, Mobile write, Orderdetails write";
command.ExecuteNonQuery();
MySqlTransaction transaction = connection.BeginTransaction();
command.Transaction = transaction;
try
{
command.CommandText = "insert into Orders(Customer_order) values (@Customer_id)";
command.Parameters.AddWithValue("@Customer_id", order.OrderCustomer.CustomerID);
command.ExecuteNonQuery();
query = $@"select Order_id from Orders where Customer_order = {order.OrderCustomer.CustomerID} order by Order_id desc limit 1;";
MySqlCommand command1 = new MySqlCommand(query, connection);
reader = command1.ExecuteReader();
if (reader.Read())
{
order.OrderID = reader.GetInt32("Order_id");
}
reader.Close();
command.CommandText = "insert into OrderDetails(Order_id,Mobile_id,Amount,Order_date) values (@Order_id,@Mobile_id,@Amount,now())";
command.Parameters.Clear();
command.Parameters.AddWithValue("@Order_id", order.OrderID);
command.Parameters.AddWithValue("@Mobile_id", order.OrderMobile.MobileID);
command.Parameters.AddWithValue("@Amount", order.Amount);
command.ExecuteNonQuery();
command.CommandText = "update Mobile set Mobile_quantity = Mobile_quantity -@Amount where Mobile_id = '" + order.OrderMobile.MobileID + "';";
command.Parameters.Clear();
command.Parameters.AddWithValue("@Amount", order.Amount);
command.ExecuteNonQuery();
transaction.Commit();
result = true;
}
catch (System.Exception e)
{
transaction.Rollback();
System.Console.WriteLine(e);
return(result);
}
finally
{
command.CommandText = "unlock tables";
command.ExecuteNonQuery();
DatabaseAccess.CloseConnection();
}
return(result);
}
public Mobile GetMobilebyId(int MobileID)
{
query = @"Select * from Mobile where Mobile_id = " + MobileID + ";";
MySqlCommand command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
Mobile mobile = null;
if (reader.Read())
{
mobile = GetMobileDetail(reader);
}
reader.Close();
DatabaseAccess.CloseConnection();
return(mobile);
}
public int Register(string Username, string Password, string Email, string Address, string Name, int Age, int Phonenumber, int CMT)
{
try
{
DatabaseAccess.OpenConnection();
query = @"insert into Customer(Customer_Username,Customer_password,Customer_email,Customer_address,Customer_Name,Customer_Age,Customer_Phone,Customer_CMT)
values ('" + Username + "','" + Password + "','" + Email + "','" + Address + "','" + Name + "'," + Age + "," + Phonenumber + "," + CMT + ");";
MySqlCommand command = new MySqlCommand(query, connection);
command.ExecuteNonQuery();
}
catch { }
finally
{
DatabaseAccess.CloseConnection();
}
return(10);
}
public Customer GetCustomer(string CustomerUsername)
{
DatabaseAccess.OpenConnection();
query = @"Select * from Customer where Customer_Username = '******';";
MySqlCommand command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
Customer customer = null;
if (reader.Read())
{
customer = GetCustomerInfo(reader);
}
reader.Close();
DatabaseAccess.CloseConnection();
return(customer);
}
public List <Mobile> GetMobilebyName(string MobileName)
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
query = @"select * from Mobile where Mobile_name like '%" + MobileName + "%';";
MySqlCommand command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
List <Mobile> listMobiles = new List <Mobile>();
if (reader != null)
{
listMobiles = GetListMobiles(command);
}
reader.Close();
DatabaseAccess.CloseConnection();
return(listMobiles);
}
public int VerifyRegister(string Username, string Email)
{
int a;
query = @"Select * from Customer where Customer_Username = '******' or Customer_email ='" + Email + "';";
MySqlCommand command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
if (reader.Read())
{
System.Console.WriteLine("Tài khoản hoặc email đã tồn tại\n");
a = 1;
}
else
{
System.Console.WriteLine("Bạn có thể sử dụng tài khoản và email này\n");
a = 2;
}
reader.Close();
DatabaseAccess.CloseConnection();
return(a);
}
public int VerifyLogin(string CustomerUsername, string CustomerPassword)
{
int b;
query = @"Select * from Customer where Customer_Username = '******' and Customer_password ='******';";
MySqlCommand command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
if (reader.Read())
{
System.Console.WriteLine("Đăng nhập thành công");
b = 1;;
}
else
{
System.Console.WriteLine("Sai tên đăng nhập hoặc mật khẩu");
b = 0;
}
reader.Close();
DatabaseAccess.CloseConnection();
return(b);
}
