public static bool UpdateMenuItem
(int id, string name, int price, int count, string type)
{
var conn = DBUtils.GetDBConnection();
string query = $"UPDATE MonAn " +
$"SET TENMON = @name, GIATIEN = @price, SOLUONG = @count, MALOAI = @type, HinhAnh = @pic " +
$"WHERE MAMON = @id";
int affectedRows = 0;
try
{
conn.Open();
var cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@price", price);
cmd.Parameters.AddWithValue("@count", count);
cmd.Parameters.AddWithValue("@type", type);
cmd.Parameters.AddWithValue("@pic", $"Images/{id}/download.jpg");
affectedRows = cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception)
{
MessageBox.Show($"Lỗi khi cập nhật dữ liệu");
}
finally
{
conn.Close();
conn.Dispose();
}
return(affectedRows > 0);
}
private void ThanhToanClick(object sender, RoutedEventArgs e)
{
string sql = "select * from HoaDon";
SqlConnection conn = DBUtils.GetDBConnection();
//lấy danh sách hóa đơn
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
int i = 0;
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
i++;
}
}
reader.Close();
}
var mahd = "HD" + (i + 1).ToString();
var time = DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day;
sql = $"insert HoaDon values('{mahd}', '{time}', {getTotalCost()})";
cmd.CommandText = sql;
DbDataReader reader3 = cmd.ExecuteReader();
reader3.Close();
//Thêm chi tiết hóa đơn
int countOrder = Global.orders.Count;
for (int j = 0; j < countOrder; j++)
{
sql = $"insert ChiTiet values ('{mahd}', '{Global.orders[j].ID}', {Global.orders[j].SoLuong}, {Global.orders[j].PriceOfOne})";
cmd.CommandText = sql;
DbDataReader reader2 = cmd.ExecuteReader();
reader2.Close();
var sql2 = $"UPDATE MonAn set SOLUONG = SOLUONG - {Global.orders[j].SoLuong} WHERE MAMON = '{Global.orders[j].ID}'";
var command = new SqlCommand(sql2, conn);
var count = command.ExecuteNonQuery();
for (int k = 0; k < Global.products.Count; k++)
{
if (Global.products[k].ID == Global.orders[j].ID)
{
Global.products[k].Remain -= Global.orders[j].SoLuong;
}
}
}
} catch (Exception err)
{
MessageBox.Show("Error in database");
Debug.WriteLine("error: " + err.Message);
return;
}
MessageBox.Show("Đã thanh toán");
this.DialogResult = true;
this.Close();
}
public static BindingList <Product> GetAllProducts(int type, string search)
{
string sql = $"Select * from MonAn and TENMON";
SqlConnection conn = DBUtils.GetDBConnection();
try
{
conn.Open();
switch (type)
{
case 0: sql = $"Select * from MonAn"; break;
case 1: sql = $"Select * from MonAn where MALOAI = 1"; break;
case 2: sql = $"Select * from MonAn where MALOAI = 2"; break;
case 3: sql = $"Select * from MonAn where MALOAI = 3"; break;
}
if (search.Length > 0)
{
if (type == 0)
{
sql += $" where TENMON like N'%{search}%'";
}
else
{
sql += $" and TENMON like N'%{search}%'";
}
}
// Tạo một đối tượng Command.
SqlCommand cmd = new SqlCommand();
// Liên hợp Command với Connection.
cmd.Connection = conn;
cmd.CommandText = sql;
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
BindingList <Product> listProduct = new BindingList <Product>();
while (reader.Read())
{
// Chỉ số của cột Emp_ID trong câu lệnh SQL.
string id = Convert.ToString(reader.GetValue(0)).Trim(); // 0
string tenmon = Convert.ToString(reader.GetValue(1));
long giatien = Convert.ToInt64(reader.GetValue(2));
int soluong = Convert.ToInt32(reader.GetValue(3));
int loai = Convert.ToInt32(reader.GetValue(4));
string hinhanh = (string)reader.GetValue(5);
var path = System.AppDomain.CurrentDomain.BaseDirectory;
var navigate = $"..\\..\\";
var dir = System.IO.Path.Combine(path, navigate);
dir += hinhanh;
var product = new Product()
{
ID = id,
Name = tenmon,
Price = giatien,
Remain = soluong,
Type = loai,
Image = dir
};
if (product.Image == null)
{
product.Image = "Images/ExtraIcon/noimage.png";
}
listProduct.Add(product);
Global.products.Add(product);
}
conn.Close();
return(listProduct);
}
conn.Close();
return(null);
}
}
catch (Exception e1)
{
MessageBox.Show("Failed", "app");
Debug.WriteLine("Error: " + e1.Message);
}
return(null);
}