public Employee GetEmployeeById(int empId)
{
query = @"SELECT emp_no, first_name, last_name
FROM employees WHERE emp_no=" + empId + ";";
DBHelper.OpenConnection();
reader = DBHelper.ExecQuery(query);
Employee emp = null;
if (reader.Read())
{
emp = ReadEmployeeInfo(reader);
}
DBHelper.CloseConnection();
return(emp);
}
//查询宿舍日常管理信息
public List <Model.Management> GetManagementDAL(string where = null, int start = 1, int end = 10)
{
List <Model.Management> ma = new List <Model.Management>();
if (DBHelper.OpenConnection())
{
SqlDataReader dr = DBHelper.ExecReader(where);
if (dr != null)
{
while (dr.Read())
{
ma.Add(new Model.Management(dr["ManagementID"].ToString(), (int)dr["dormitoryID"], dr["Hygiene"] as string, dr["Inspectors"] as string, (DateTime)dr["ManagementTime"], dr["Explain"] as string, (int)dr["State"]));
}
}
DBHelper.CloseConnection();
}
return(ma);
}
public User GetUserByUserName(string user_name)
{
query = @"select user_id, user_name, user_password, registration_date
from users where user_name=@username;";
// MySqlCommand command = new MySqlCommand(query);
connection = DBHelper.OpenConnection();
MySqlCommand command = new MySqlCommand(query, connection);
command.Parameters.AddWithValue("@username", user_name);
reader = command.ExecuteReader();
User user = null;
if (reader.Read())
{
user = new User(reader);
}
DBHelper.CloseConnection();
return(user);
}
public Customer GetCustomerByID(int?UserID)
{
if (UserID == null)
{
return(null);
}
query = $@"select * from Customers where customer_id = " + UserID + ";";
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
Customer cus = null;
if (reader.Read())
{
cus = GetCustomer(reader);
}
reader.Close();
DBHelper.CloseConnection();
return(cus);
}
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 List <Motor> GetMotorByTypeMotor(string Motor_typeID)
{
query = @"select * from Motor where Motor_TypeID = '" + Motor_typeID + "';";
//DBHelper.OpenConnection();
MySqlCommand command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
List <Motor> listmotor = null;
if (reader != null)
{
listmotor = GetListMotor(command);
}
reader.Close();
DBHelper.CloseConnection();
return(listmotor);
}
public Order GetLastOrderIdPurchase(int?userId)
{
if (userId == null)
{
return(null);
}
Order order = null;
query = @"select max(order_id) from Orders where order_customer = " + userId + " ";
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
if (reader.Read())
{
order = GetOrder(reader);
}
reader.Close();
DBHelper.CloseConnection();
return(order);
}
//根据给定条件查询学员信息
public List <Model.Student> GetStudentDAL(string whereStr = null, int start = 1, int end = 10)
{
List <Model.Student> stu = new List <Model.Student>();
if (DBHelper.OpenConnection())
{
SqlDataReader dr = DBHelper.ExecReader(whereStr);
if (dr != null)
{
while (dr.Read())
{
stu.Add(new Model.Student(dr["StudentID"].ToString(), dr["StudentName"] as string, (int)dr["Age"], dr["Gender"] as string, (int)dr["dormitoryID"], dr["Place"] as string, (DateTime)dr["Accommodationtime"], (DateTime)dr["leavetime"], dr["Phone"] as string, dr["Explains"] as string, (int)dr["State"]));
}
}
DBHelper.CloseConnection();
}
return(stu);
}
//根据给定条件查询维修信息
public List <Model.Repair> GetRepairDAL(string whereStr = null, int start = 1, int end = 10)
{
List <Model.Repair> re = new List <Model.Repair>();
if (DBHelper.OpenConnection())
{
SqlDataReader dr = DBHelper.ExecReader(whereStr);
if (dr != null)
{
while (dr.Read())
{
re.Add(new Model.Repair(dr["RepairID"].ToString(), dr["RepairPerso"] as string, dr["RepairName"] as string, (int)dr["dormitoryID"], (DateTime)dr["RepairTime"], (DateTime)dr["HandleTime"], (int)dr["Count"], dr["HandlePerso"] as string, dr["Explain"] as string, (int)dr["State"]));
}
}
DBHelper.CloseConnection();
}
return(re);
}
public Shoes GetShoesById(int?itemId)
{
if (itemId == null)
{
return(null);
}
query = $@"select it.item_id,it.item_name,itd.item_size,it.item_price,it.item_color,it.item_material,it.item_trademark,itd.item_quantity from Items it, Itemdetails itd where it.item_id = " + itemId + ";";
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
Shoes shoes = null;
if (reader.Read())
{
shoes = GetShoesInfo(reader);
}
reader.Close();
DBHelper.CloseConnection();
return(shoes);
}
public bool UpdateMoneyCustomer(int customerId, decimal Amount)
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
bool result = false;
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = "lock tables Customers write , Orders write, Items write, OrderDetail write;";
command.ExecuteNonQuery();
MySqlTransaction trans = connection.BeginTransaction();
command.Transaction = trans;
try
{
command.Parameters.Clear();
command.CommandText = @"update Customers set Money = Money - @Amount where CusID = @CusID;";
command.Parameters.AddWithValue("@Amount", Amount);
command.Parameters.AddWithValue("@CusID", customerId);
command.ExecuteNonQuery();
trans.Commit();
result = true;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
result = false;
trans.Rollback();
}
finally
{
command.CommandText = "unlock tables;";
command.ExecuteNonQuery();
DBHelper.CloseConnection();
}
return(result);
}
public List <Shoes> SearchItem(int temp)
{
switch (temp)
{
case 1:
query = $@"select it.item_id,it.item_name,itd.item_size,it.item_price,it.item_color,it.item_material,it.item_brand,itd.item_quantity from Items it, Itemdetails itd where item_id = ";
break;
}
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
List <Shoes> shoes = new List <Shoes>();
while (reader.Read())
{
shoes.Add(GetShoesInfo(reader));
}
reader.Close();
DBHelper.CloseConnection();
return(shoes);
}
//查询宿舍信息
public List <Model.Dormitory> GetDormitoryDAL(string where = null, int start = 1, int end = 10)
{
List <Model.Dormitory> dor = new List <Model.Dormitory>();
if (DBHelper.OpenConnection())
{
SqlDataReader dr = DBHelper.ExecReader("select * from [Dormitory] where [State]=0 " + where + " ORDER BY dormitoryID");
if (dr != null)
{
while (dr.Read())
{
dor.Add(new Model.Dormitory((int)dr["DormitoryID"], dr["dormitoryGrade"] as string, (int)dr["dormitoryPerso"], (int)dr["dormitoryPrice"], dr["beds"] as string, dr["DormitoryBoos"] as string, dr["Explain"] as string, (int)dr["State"], dr["ISBN"] as string));
}
}
dr.Close();
DBHelper.CloseConnection();
}
return(dor);
}
public Item GetItemBySize(string itemSize)
{
if (itemSize == null)
{
return(null);
}
query = $@"select it.itemID, it.itemName, it.itemNameEnglish, it.itemQuantity, it.itemDescription, it.itemPreview, it.itemResources ,itdls.itemSize, itdls.itemPrice from Items it, ItemDetails itdls where itdls.itemSize = " + itemSize + ";";
// 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 <Motor> GetMotorByLicensePlates(string LicensePlate)
{
query = @"select * from Motor where LicensePlate = '" + LicensePlate + "';";
//DBHelper.OpenConnection();
MySqlCommand command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
List <Motor> listmotor = null;
if (reader != null)
{
listmotor = GetListMotor(command);
}
reader.Close();
DBHelper.CloseConnection();
return(listmotor);
}
//查询来访信息
public List <Model.Visiting> GetVisitingDAL(string where = null, int start = 1, int end = 10)
{
List <Model.Visiting> vis = new List <Model.Visiting>();
if (DBHelper.OpenConnection())
{
SqlDataReader dr = DBHelper.ExecReader(where);
if (dr != null)
{
while (dr.Read())
{
vis.Add(new Model.Visiting(dr["VisitingID"].ToString(), (int)dr["dormitoryID"], dr["VisitingName"] as string, (DateTime)dr["VisitingTime"], (DateTime)dr["LikaiTime"], (int)dr["State"]));
}
}
DBHelper.CloseConnection();
}
return(vis);
}
public bool UpdateStatusOrder(int?orderId)
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
bool result = false;
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = "lock tables Customers write , Orders write, Items write, OrderDetail write;";
command.ExecuteNonQuery();
MySqlTransaction trans = connection.BeginTransaction();
command.Transaction = trans;
try
{
command.Parameters.Clear();
command.CommandText = @"Update Orders set OrderStatus = 'Complete' where OrderID = @OrderId;";
command.Parameters.AddWithValue("@OrderId", orderId);
command.ExecuteNonQuery();
trans.Commit();
result = true;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
result = false;
trans.Rollback();
}
finally
{
command.CommandText = "unlock tables;";
command.ExecuteNonQuery();
connection.Close();
}
DBHelper.CloseConnection();
return(result);
}
public int Register(string Username, string Password, string Name, string Email, string Phone, string Birthday, string Gender, string Address)
{
try
{
MySqlConnection connection = DBHelper.OpenConnection();
query = @"insert into Customers(customer_account,customer_password,customer_name, customer_email,customer_phone,customer_birthday,customer_gender,customer_address) values ('" + Username + "','" + Password + "','" + Name + "','" + Email + "','" + Phone + "','" + Birthday + "','" + Gender + "','" + Address + "');";
MySqlCommand command = new MySqlCommand(query, connection);
command.ExecuteNonQuery();
}
catch
{
Console.WriteLine("could not be registered!");
}
finally
{
DBHelper.CloseConnection();
}
return(1);
}
public int Register(string userName, string userPassword, string userAccount, string userEmail, string userPhoneNumber, string userBirthday, string userGender)
{
try
{
MySqlConnection connection = DBHelper.OpenConnection();
query = @"insert into Users(userName, userAccount, userPassWord, userEmail, userPhoneNumber, userBirthday, userGender)
values('" + userName + "','" + userAccount + "','" + userPassword + "','" + userEmail + "','" + userPhoneNumber + "','" + userBirthday + "','" + userGender + "');";
MySqlCommand command = new MySqlCommand(query, connection);
command.ExecuteNonQuery();
}
catch
{
// Console.WriteLine("could not be registered!");
}
finally
{
DBHelper.CloseConnection();
}
return(1);
}
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 Employee GetEmployeeById(int empID)
{
query = @"select emp_no, first_name, last_name
from Employees where emp_no=" + empID + ";";
//Mở kết nối đến database
MySqlCommand command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
Employee employee = null;
if (reader.Read())
{
employee = GetEmployeeInfo(reader);
}
reader.Close();
DBHelper.CloseConnection();
return(employee);
}
public List <Shoes> SearchShoesName()
{
query = @"select it.item_id,it.item_name,itd.item_size,it.item_price,it.item_color,it.item_material,it.item_trademark,itd.item_quantity from items it, itemdetails itd where it.item_id = itd.item_id;";
List <Shoes> shoes = new List <Shoes>();
try
{
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
}
catch (System.Exception)
{
Console.WriteLine("can not connect to database!");
return(null);
}
while (reader.Read())
{
shoes.Add(GetShoesInfo(reader));
}
reader.Close();
DBHelper.CloseConnection();
return(shoes);
}
public int VerifyRegister(string Username, string Email)
{
int a;
MySqlConnection connection = DBHelper.OpenConnection();
query = @"Select * from Customers where customer_account = '" + Username + "' or customer_email ='" + Email + "';";
MySqlCommand command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
if (reader.Read())
{
System.Console.WriteLine("Account or email already exists");
a = 1;
}
else
{
System.Console.WriteLine("Registration successful");
a = 2;
}
reader.Close();
DBHelper.CloseConnection();
return(a);
}
public List <Order> ShowOrderUserPaySucess(int?userId)
{
if (userId == null)
{
return(null);
}
List <Order> orders = new List <Order>();
Order or = new Order();
query = @"select ord.order_id as order_id,ordt.amount, ord.order_date, it.item_id ,it.item_name, it.item_price, cus.customer_name, cus.customer_email from
Customers cus inner join orders ord on ord.order_customer = cus.customer_id inner join Orderdetails ordt on ord.order_id = ordt.order_id
inner join Items it on ordt.item_id = it.item_id
where ord.order_customer = " + userId + " and ord.order_id = " + GetLastInsertOrderID(userId) + ";";
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
while (reader.Read())
{
orders.Add(GetOrderPurchaseSuccess(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 = ";
query = $@"select it.itemID, it.itemName, it.itemNameEnglish, it.itemQuantity, it.itemDescription, it.itemPreview, it.itemResources ,itdls.itemSize, itdls.itemPrice, from Items it, ItemDetails itdls 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 bool UpdateStatusShoppingCartById(bool isHave, int?UserID)
{
if (UserID == null)
{
return(false);
}
switch (isHave)
{
case true:
query = @"update Customers set UserShoppingCart = false where customer_id = " + UserID + ";";
break;
case false:
query = @"update Customers set UserShoppingCart = true where customer_id = " + UserID + ";";
break;
}
DBHelper.ExecNonQuery(query, DBHelper.OpenConnection());
DBHelper.CloseConnection();
return(true);
}
public List <Item> GetListItems()
{
DBHelper.OpenConnection();
// query = $@"select * from Items;";
query = $@"select it.itemID, it.itemName, it.itemNameEnglish, itdls.itemSize, itdls.itemPrice, it.itemPreview, it.itemResources, it.itemQuantity, it.itemDescription from Items it, ItemDetails itdls where it.itemID = itdls.itemID limit 10; ";
List <Item> items = new List <Item>();
try
{
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
}
catch (System.Exception)
{
Console.WriteLine("Ko the connection voi database");
return(null);
}
while (reader.Read())
{
items.Add(GetItem(reader));
}
reader.Close();
DBHelper.CloseConnection();
return(items);
}
public int ConfirmRegister(string userName, string userEmail)
{
int a;
MySqlConnection connection = DBHelper.OpenConnection();
query = $@"select * from Users where userName = '******' or userEmail = '{userEmail}';";
// query = @"Select * from Users where userName = '******' or userEmail ='" + userEmail + "';";
MySqlCommand command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
if (reader.Read())
{
// Console.WriteLine("Account or email already exists");
a = 1;
}
else
{
// Console.WriteLine("Registration successful");
a = 2;
}
reader.Close();
DBHelper.CloseConnection();
return(a);
}
public bool CreateOrder(Orders order)
{
Customer cu = new Customer();
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
// DBHelper.OpenConnection();
bool result = false;
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
command.CommandText = @"lock tables Items write, Orders write,OrderDetail write;";
command.ExecuteNonQuery();
MySqlTransaction transactions = connection.BeginTransaction();
// order.Customer = new Customer();
command.Transaction = transactions;
try
{
// int customId = order.Customer.CusID;
int orderId = 0;
command.CommandText = @"insert into Orders(OrderDate,Note,OrderStatus,CusID) values (@OrderDate,@Note,@OrderStatus,@CusID);";
command.Parameters.Clear();
command.Parameters.AddWithValue("@CusID", order.Customer.CusID);
command.Parameters.AddWithValue("@OrderStatus", order.Status);
command.Parameters.AddWithValue("@Note", order.Note);
command.Parameters.AddWithValue("@OrderDate", order.OrderDate);
command.ExecuteNonQuery();
command.CommandText = "select LAST_INSERT_ID() as OrderID";
using (reader = command.ExecuteReader())
{
if (reader.Read())
{
orderId = reader.GetInt32("OrderID");
}
}
order.OrderID = orderId;
foreach (var item in order.Items)
{
command.Parameters.Clear();
command.CommandText = @"insert into OrderDetail(OrderID,ItemID,ItemCount,Size) values (" + order.OrderID + ", " + item.ItemID + "," + item.ItemCount + ",'" + item.Size + "'" + ");";
command.ExecuteNonQuery();
}
transactions.Commit();
result = true;
}
catch (System.Exception)
{
transactions.Rollback();
throw;
}
finally
{
command.CommandText = "unlock tables";
command.ExecuteNonQuery();
DBHelper.CloseConnection();
}
return(result);
}
public bool CreateOrder(Order order)
{
if (order == null || order.ItemsList == null || order.ItemsList.Count == 0)
{
return(false);
}
bool result = true;
MySqlConnection connection = DbConfiguration.OpenConnection();
MySqlCommand cmd = connection.CreateCommand();
cmd.Connection = connection;
//Lock update all tables
cmd.CommandText = "lock tables Customers write, Orders write, Items write, OrderDetails write;";
cmd.ExecuteNonQuery();
MySqlTransaction trans = connection.BeginTransaction();
cmd.Transaction = trans;
MySqlDataReader reader = null;
if (order.OrderCustomer == null || order.OrderCustomer.CustomerName == null || order.OrderCustomer.CustomerName == "")
{
//set default customer with customer id = 1
order.OrderCustomer = new Customer()
{
CustmerId = 1
};
}
try
{
if (order.OrderCustomer.CustmerId == null)
{
//Insert new Customer
cmd.CommandText = @"insert into Customers(customer_name, customer_address)
values ('" + order.OrderCustomer.CustomerName + "','" + (order.OrderCustomer.CustomerAddress ?? "") + "');";
cmd.ExecuteNonQuery();
//Get new customer id
cmd.CommandText = "select customer_id from Customers order by customer_id desc limit 1;";
reader = cmd.ExecuteReader();
if (reader.Read())
{
order.OrderCustomer.CustmerId = reader.GetInt32("customer_id");
}
reader.Close();
}
else
{
order.OrderCustomer = (new CustomerDAL()).GetById(order.OrderCustomer.CustmerId ?? 0, DBHelper.GetConnection());
}
if (order.OrderCustomer == null || order.OrderCustomer.CustmerId == null)
{
throw new Exception("Can't find Customer!");
}
//Insert Order
cmd.CommandText = "insert into Orders(customer_id, order_status) values (@customerId, @orderStatus);";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@customerId", order.OrderCustomer.CustmerId);
cmd.Parameters.AddWithValue("@orderStatus", OrderStatus.CREATE_NEW_ORDER);
cmd.ExecuteNonQuery();
//get new Order_ID
// cmd.CommandText = "select order_id from Orders order by order_id desc limit 1;";
cmd.CommandText = "select LAST_INSERT_ID() as order_id";
reader = cmd.ExecuteReader();
if (reader.Read())
{
order.OrderId = reader.GetInt32("order_id");
}
reader.Close();
//insert Order Details table
foreach (var item in order.ItemsList)
{
if (item.ItemId == null || item.Amount <= 0)
{
throw new Exception("Not Exists Item");
}
//get unit_price
cmd.CommandText = "select unit_price from Items where item_id=@itemId";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@itemId", item.ItemId);
reader = cmd.ExecuteReader();
if (!reader.Read())
{
throw new Exception("Not Exists Item");
}
item.ItemPrice = reader.GetDecimal("unit_price");
reader.Close();
//insert to Order Details
cmd.CommandText = @"insert into OrderDetails(order_id, item_id, unit_price, quantity) values
(" + order.OrderId + ", " + item.ItemId + ", " + item.ItemPrice + ", " + item.Amount + ");";
cmd.ExecuteNonQuery();
//update amount in Items
cmd.CommandText = "update Items set amount=amount-@quantity where item_id=" + item.ItemId + ";";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@quantity", item.Amount);
cmd.ExecuteNonQuery();
}
//commit transaction
trans.Commit();
result = true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
result = false;
try
{
trans.Rollback();
}
catch
{
}
}
finally
{
//unlock all tables;
cmd.CommandText = "unlock tables;";
cmd.ExecuteNonQuery();
DBHelper.CloseConnection();
}
return(result);
}
