public static bool ThemTaiKhoan(TaiKhoanDTO taiKhoan)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "INSERT INTO TaiKhoan (ho_ten, mat_khau, ngay_bat_dau, loai_tai_khoan, hinh, trang_thai) VALUES (@hoTen, @matKhau, @ngayBatDau, @loaiTaiKhoan, @hinh, @trangThai)";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@hoTen", System.Data.SqlDbType.NVarChar, 255).Value = taiKhoan.HoTen;
command.Parameters.Add("@matKhau", System.Data.SqlDbType.NVarChar, 255).Value = taiKhoan.MatKhau;
command.Parameters.Add("@ngayBatDau", System.Data.SqlDbType.DateTime, 0).Value = taiKhoan.NgayBatDau;
command.Parameters.Add("@loaiTaiKhoan", System.Data.SqlDbType.Int, 0).Value = taiKhoan.LoaiTaiKhoan;
command.Parameters.Add("@hinh", System.Data.SqlDbType.NVarChar, 255).Value = taiKhoan.Hinh;
command.Parameters.Add("@TrangThai", System.Data.SqlDbType.Bit, 0).Value = taiKhoan.TrangThai;
connection.Open();
int reader = command.ExecuteNonQuery();
connection.Close();
if (reader == 1)
{
return(true);
}
return(false);
}
public static bool SuaTaiKhoan(TaiKhoanDTO taiKhoan)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "UPDATE TaiKhoan SET ho_ten=@hoTen, mat_khau=@matKhau, loai_tai_khoan=@loaiTaiKhoan, hinh=@hinh, trang_thai=@trangThai WHERE ma_tai_khoan=@maTaiKhoan";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@maTaiKhoan", System.Data.SqlDbType.Int, 0).Value = taiKhoan.MaTaiKhoan;
command.Parameters.Add("@hoTen", System.Data.SqlDbType.NVarChar, 255).Value = taiKhoan.HoTen;
command.Parameters.Add("@matKhau", System.Data.SqlDbType.NVarChar, 255).Value = taiKhoan.MatKhau;
command.Parameters.Add("@loaiTaiKhoan", System.Data.SqlDbType.Int, 0).Value = taiKhoan.LoaiTaiKhoan;
command.Parameters.Add("@hinh", System.Data.SqlDbType.NVarChar, 255).Value = taiKhoan.Hinh;
command.Parameters.Add("@trangThai", System.Data.SqlDbType.Bit, 0).Value = taiKhoan.TrangThai;
connection.Open();
int reader = command.ExecuteNonQuery();
connection.Close();
if (reader == 1)
{
return(true);
}
return(false);
}
public static TaiKhoanDTO LayThongTinTaiKhoan(int maTaiKhoan)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT ho_ten, mat_khau, ngay_bat_dau, loai_tai_khoan, hinh, trang_thai FROM TaiKhoan WHERE ma_tai_khoan=@maTaiKhoan";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@maTaiKhoan", SqlDbType.Int, 0).Value = maTaiKhoan;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
TaiKhoanDTO result = new TaiKhoanDTO();
if (reader.HasRows)
{
while (reader.Read())
{
result.HoTen = reader.GetString(0);
result.MatKhau = reader.GetString(1);
result.NgayBatDau = reader.GetDateTime(2);
result.LoaiTaiKhoan = reader.GetInt32(3);
if (!reader.IsDBNull(4))
{
result.Hinh = reader.GetString(4);
}
result.TrangThai = reader.GetBoolean(5);
}
}
connection.Close();
return(result);
}
public static List <CTPhieuNhapDTO> LayDanhSachCTPhieuNhap(int maPhieuNhap)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT ma_phieu_nhap, ma_nguyen_lieu, so_luong, don_gia, don_vi_tinh, ghi_chu FROM CTPhieuNhap WHERE ma_phieu_nhap=@maPhieuNhap";
SqlCommand command = new SqlCommand();
command.Parameters.Add("@maPhieuNhap", System.Data.SqlDbType.NVarChar, 255).Value = maPhieuNhap;
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <CTPhieuNhapDTO> result = new List <CTPhieuNhapDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
CTPhieuNhapDTO ctpn = new CTPhieuNhapDTO();
ctpn.MaPhieuNhap = reader.GetInt32(0);
ctpn.MaNguyenLieu = reader.GetInt32(1);
ctpn.SoLuong = reader.GetDouble(2);
ctpn.DonGia = reader.GetDouble(3);
ctpn.DonViTinh = reader.GetString(4);
ctpn.GhiChu = reader.GetString(5);
result.Add(ctpn);
}
}
connection.Close();
return(result);
}
public static List <ChucNang_LoaiTaiKhoanDTO> LayDanhSachChucNang_LoaiTaiKhoanTheoMaTaiKhoan(int maTaiKhoan)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT ma_chuc_nang, ChucNang_LoaiTaiKhoan.ma_loai_tai_khoan FROM ChucNang_LoaiTaiKhoan, TaiKhoan WHERE TaiKhoan.ma_tai_khoan=@maTaiKhoan AND TaiKhoan.loai_tai_khoan=ChucNang_LoaiTaiKhoan.ma_loai_tai_khoan";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@maTaiKhoan", System.Data.SqlDbType.Int, 0).Value = maTaiKhoan;
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <ChucNang_LoaiTaiKhoanDTO> result = new List <ChucNang_LoaiTaiKhoanDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
ChucNang_LoaiTaiKhoanDTO chucNang_LoaiTaiKhoan = new ChucNang_LoaiTaiKhoanDTO();
chucNang_LoaiTaiKhoan.MaChucNang = reader.GetInt32(0);
chucNang_LoaiTaiKhoan.MaLoaiTaiKhoan = reader.GetInt32(1);
result.Add(chucNang_LoaiTaiKhoan);
}
}
command.Connection.Close();
return(result);
}
public static List <TrendingMonDTO> LayDanhSachTop10MonDaBan()
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT TOP 10 ten_mon, SUM(so_luong) tong_so_luong FROM CTHoaDon INNER JOIN Mon ON mon=ma_mon INNER JOIN HoaDon ON HoaDon.ma_hoa_don=CTHoaDon.hoa_don WHERE HoaDon.trang_thai=1 GROUP BY ten_mon ORDER BY tong_so_luong DESC";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <TrendingMonDTO> result = new List <TrendingMonDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
TrendingMonDTO trendingMon = new TrendingMonDTO();
trendingMon.TenMon = reader.GetString(0);
trendingMon.SoLuong = reader.GetInt32(1);
result.Add(trendingMon);
}
}
connection.Close();
return(result);
}
public static List <NguyenLieuDTO> LayDanhSachTatCaNguyenLieu()
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT ma_nguyen_lieu, ten_nguyen_lieu, so_luong, don_vi_tinh, trang_thai FROM NguyenLieu";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <NguyenLieuDTO> result = new List <NguyenLieuDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
NguyenLieuDTO nguyenlieu = new NguyenLieuDTO();
nguyenlieu.MaNguyenLieu = reader.GetInt32(0);
nguyenlieu.TenNguyenLieu = reader.GetString(1);
nguyenlieu.SoLuong = reader.GetDouble(2);
nguyenlieu.DonViTinh = reader.GetString(3);
nguyenlieu.TrangThai = reader.GetBoolean(4);
result.Add(nguyenlieu);
}
}
connection.Close();
return(result);
}
public static List <rptHoaDon_TaiKhoanDTO> DanhSachHoaDonVuaMoiLapTrongNgay()
{
SqlConnection connection = DataProvider.GetConnection();
string query = "select HoaDon.ma_hoa_don, HoaDon.ngay_lap, TaiKhoan.ho_ten, HoaDon.tien_mat, HoaDon.tien_thua, HoaDon.tong_tien from HoaDon,TaiKhoan where HoaDon.nhan_vien_lap=TaiKhoan.ma_tai_khoan and DAY(GETDATE()) = DAY(ngay_lap) and MONTH(GETDATE()) = MONTH(ngay_lap) and YEAR(GETDATE()) = YEAR(ngay_lap)";
SqlCommand command = new SqlCommand();
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <rptHoaDon_TaiKhoanDTO> result = new List <rptHoaDon_TaiKhoanDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
rptHoaDon_TaiKhoanDTO hoaDon_TaiKhoanDTO = new rptHoaDon_TaiKhoanDTO();
hoaDon_TaiKhoanDTO.MaHoaDon = reader.GetInt32(0);
hoaDon_TaiKhoanDTO.NgayLap = reader.GetDateTime(1);
hoaDon_TaiKhoanDTO.TenNhanVien = reader.GetString(2);
hoaDon_TaiKhoanDTO.TienMat = reader.GetDouble(3);
hoaDon_TaiKhoanDTO.TienThua = reader.GetDouble(4);
hoaDon_TaiKhoanDTO.TongTien = reader.GetDouble(5);
result.Add(hoaDon_TaiKhoanDTO);
}
}
connection.Close();
return(result);
}
public static List <LoaiToppingDTO> LayDanhSachCTLoaiMon_LoaiTopping(int maLoaiMon)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT DISTINCT LoaiTopping.ma_loai_topping, LoaiTopping.ten_loai_topping FROM CTLoaiMon_LoaiTopping,LoaiTopping WHERE CTLoaiMon_LoaiTopping.ma_loai_topping=LoaiTopping.ma_loai_topping AND CTLoaiMon_LoaiTopping.ma_loai_mon=@maLoaiMon AND LoaiTopping.trang_thai=1";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@maLoaiMon", System.Data.SqlDbType.Int, 0).Value = maLoaiMon;
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <LoaiToppingDTO> result = new List <LoaiToppingDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
LoaiToppingDTO loaiTopping = new LoaiToppingDTO();
loaiTopping.MaLoaiTopping = reader.GetInt32(0);
loaiTopping.TenLoaiTopping = reader.GetString(1);
result.Add(loaiTopping);
}
}
command.Connection.Close();
return(result);
}
public static bool KiemTraDangNhap(int maTaiKhoan, string matKhau)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT mat_khau FROM TaiKhoan WHERE ma_tai_khoan=@maTaiKhoan";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@maTaiKhoan", System.Data.SqlDbType.Int, 0).Value = maTaiKhoan;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
bool result = false;
if (reader.HasRows)
{
while (reader.Read())
{
if (matKhau == reader.GetString(0))
{
result = true;
}
}
}
connection.Close();
return(result);
}
public static List <rptPhieuNhap_CTPhieuNhapDTO> DoiMaNguyenLieuThanhTenNguyenLieu(int maPhieuNhap)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT NguyenLieu.ten_nguyen_lieu, CTPhieuNhap.so_luong, CTPhieuNhap.don_vi_tinh, CTPhieuNhap.don_gia FROM CTPhieuNhap,NguyenLieu WHERE CTPhieuNhap.ma_nguyen_lieu=NguyenLieu.ma_nguyen_lieu and CTPhieuNhap.ma_phieu_nhap=@maPhieuNhap";
SqlCommand command = new SqlCommand();
command.Parameters.Add("@maPhieuNhap", System.Data.SqlDbType.Int, 0).Value = maPhieuNhap;
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <rptPhieuNhap_CTPhieuNhapDTO> result = new List <rptPhieuNhap_CTPhieuNhapDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
rptPhieuNhap_CTPhieuNhapDTO phieuNhap_CTPhieuNhapDTO = new rptPhieuNhap_CTPhieuNhapDTO();
phieuNhap_CTPhieuNhapDTO.TenNguyenLieu = reader.GetString(0);
phieuNhap_CTPhieuNhapDTO.SoLuong = reader.GetDouble(1);
phieuNhap_CTPhieuNhapDTO.DonViTinh = reader.GetString(2);
phieuNhap_CTPhieuNhapDTO.DonGia = reader.GetDouble(3);
result.Add(phieuNhap_CTPhieuNhapDTO);
}
}
connection.Close();
return(result);
}
public static bool LuuHoaDon(HoaDonDTO hoaDon)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "INSERT INTO HoaDon (nhan_vien_lap, ngay_lap, tong_tien, tien_mat, tien_thua, trang_thai) VALUES (@nhanVienLap, @ngayLap, @tongTien, @tienMat, @tienThua, @trangThai)";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@nhanVienLap", System.Data.SqlDbType.Int, 0).Value = hoaDon.NhanVienLap;
command.Parameters.Add("@ngayLap", System.Data.SqlDbType.DateTime, 0).Value = hoaDon.NgayLap;
command.Parameters.Add("@tongTien", System.Data.SqlDbType.Float, 0).Value = hoaDon.TongTien;
command.Parameters.Add("@tienMat", System.Data.SqlDbType.Float, 0).Value = hoaDon.TienMat;
command.Parameters.Add("@tienThua", System.Data.SqlDbType.Float, 0).Value = hoaDon.TienThua;
command.Parameters.Add("@trangThai", System.Data.SqlDbType.Bit, 0).Value = hoaDon.TrangThai;
connection.Open();
int reader = command.ExecuteNonQuery();
connection.Close();
if (reader > 0)
{
return(true);
}
return(false);
}
public static List <rptHoaDon_TaiKhoanDTO> LayHoaDonMaMoiNhat()
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT HoaDon.ma_hoa_don, HoaDon.ngay_lap, TaiKhoan.ho_ten, HoaDon.tien_mat, HoaDon.tien_thua, HoaDon.tong_tien FROM HoaDon,TaiKhoan where HoaDon.nhan_vien_lap=TaiKhoan.ma_tai_khoan and ma_hoa_don = (SELECT MAX(ma_hoa_don)FROM HoaDon)";
SqlCommand command = new SqlCommand();
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <rptHoaDon_TaiKhoanDTO> result = new List <rptHoaDon_TaiKhoanDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
rptHoaDon_TaiKhoanDTO hoaDon_TaiKhoanDTO = new rptHoaDon_TaiKhoanDTO();
hoaDon_TaiKhoanDTO.MaHoaDon = reader.GetInt32(0);
hoaDon_TaiKhoanDTO.NgayLap = reader.GetDateTime(1);
hoaDon_TaiKhoanDTO.TenNhanVien = reader.GetString(2);
hoaDon_TaiKhoanDTO.TienMat = reader.GetDouble(3);
hoaDon_TaiKhoanDTO.TienThua = reader.GetDouble(4);
hoaDon_TaiKhoanDTO.TongTien = reader.GetDouble(5);
result.Add(hoaDon_TaiKhoanDTO);
}
}
connection.Close();
return(result);
}
// Lấy danh sách theo khoảng thời gian
public static List <HoaDonDTO> GetListBillByTime(DateTime dateFrom, DateTime dateEnd)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT ma_hoa_don, nhan_vien_lap, ngay_lap, tong_tien, tien_mat, tien_thua, trang_thai FROM HoaDon WHERE ngay_lap BETWEEN @dateFrom AND @dateEnd";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@dateFrom", System.Data.SqlDbType.DateTime, 0).Value = dateFrom;
command.Parameters.Add("@dateEnd", System.Data.SqlDbType.DateTime, 0).Value = dateEnd;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <HoaDonDTO> result = new List <HoaDonDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
HoaDonDTO hd = new HoaDonDTO();
hd.MaHoaDon = reader.GetInt32(0);
hd.NhanVienLap = reader.GetInt32(1);
hd.NgayLap = reader.GetDateTime(2);
hd.TongTien = reader.GetDouble(3);
hd.TienMat = reader.GetDouble(4);
hd.TienThua = reader.GetDouble(5);
hd.TrangThai = reader.GetBoolean(6);
result.Add(hd);
}
}
connection.Close();
return(result);
}
// Lấy hóa đơn một ngày cụ thể
public static List <HoaDonDTO> GetListBillTimeline(DateTime timeLine)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT ma_hoa_don, nhan_vien_lap, ngay_lap, tong_tien, tien_mat, tien_thua, trang_thai FROM HoaDon WHERE DATEPART(YYYY, ngay_lap) = YEAR(@timeLine) AND DATEPART(MM, ngay_lap) = MONTH(@timeLine) AND DATEPART(DD, ngay_lap) = DAY(@timeLine)";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@timeLine", System.Data.SqlDbType.DateTime, 0).Value = timeLine;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <HoaDonDTO> result = new List <HoaDonDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
HoaDonDTO hd = new HoaDonDTO();
hd.MaHoaDon = reader.GetInt32(0);
hd.NhanVienLap = reader.GetInt32(1);
hd.NgayLap = reader.GetDateTime(2);
hd.TongTien = reader.GetDouble(3);
hd.TienMat = reader.GetDouble(4);
hd.TienThua = reader.GetDouble(5);
hd.TrangThai = reader.GetBoolean(6);
result.Add(hd);
}
}
connection.Close();
return(result);
}
public static List <LoaiTaiKhoanDTO> LayDanhSachTatCaLoaiTaiKhoan()
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT ma_loai_tai_khoan, ten_loai_tai_khoan, trang_thai FROM LoaiTaiKhoan";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <LoaiTaiKhoanDTO> result = new List <LoaiTaiKhoanDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
LoaiTaiKhoanDTO loaiTaiKhoan = new LoaiTaiKhoanDTO();
loaiTaiKhoan.MaLoaiTaiKhoan = reader.GetInt32(0);
loaiTaiKhoan.TenLoaiTaiKhoan = reader.GetString(1);
loaiTaiKhoan.TrangThai = reader.GetBoolean(2);
result.Add(loaiTaiKhoan);
}
}
connection.Close();
return(result);
}
public static List <DoanhThuDTO> LayDoanhThuHoaDon()
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT SUM(tong_tien) doanh_thu, MONTH(ngay_lap), YEAR(ngay_lap) FROM HoaDon WHERE trang_thai=1 AND YEAR(ngay_lap)=YEAR(GETDATE()) GROUP BY MONTH(ngay_lap), YEAR(ngay_lap) ORDER BY doanh_thu DESC";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <DoanhThuDTO> result = new List <DoanhThuDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
DoanhThuDTO dt = new DoanhThuDTO();
dt.Thang = reader.GetInt32(1);
dt.DoanhThu = reader.GetDouble(0);
result.Add(dt);
}
}
connection.Close();
return(result);
}
public static List <rptHoaDon_TaiKhoanDTO> DoiMaNhanVienThanhTenNhanVien(DateTime timeStart, DateTime timeEnd)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT HoaDon.ma_hoa_don, HoaDon.ngay_lap, TaiKhoan.ho_ten, HoaDon.tien_mat, HoaDon.tien_thua, HoaDon.tong_tien FROM HoaDon,TaiKhoan WHERE HoaDon.nhan_vien_lap=TaiKhoan.ma_tai_khoan and ngay_lap between @timeStart and @timeEnd";
SqlCommand command = new SqlCommand();
command.Parameters.Add("@timeStart", System.Data.SqlDbType.DateTime, 0).Value = timeStart;
command.Parameters.Add("@timeEnd", System.Data.SqlDbType.DateTime, 0).Value = timeEnd;
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <rptHoaDon_TaiKhoanDTO> result = new List <rptHoaDon_TaiKhoanDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
rptHoaDon_TaiKhoanDTO hoaDon_TaiKhoanDTO = new rptHoaDon_TaiKhoanDTO();
hoaDon_TaiKhoanDTO.MaHoaDon = reader.GetInt32(0);
hoaDon_TaiKhoanDTO.NgayLap = reader.GetDateTime(1);
hoaDon_TaiKhoanDTO.TenNhanVien = reader.GetString(2);
hoaDon_TaiKhoanDTO.TienMat = reader.GetDouble(3);
hoaDon_TaiKhoanDTO.TienThua = reader.GetDouble(4);
hoaDon_TaiKhoanDTO.TongTien = reader.GetDouble(5);
result.Add(hoaDon_TaiKhoanDTO);
}
}
connection.Close();
return(result);
}
public static bool SuaMon(MonDTO mon)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "UPDATE Mon SET ten_mon=@tenMon, loai_mon=@loaiMon, hinh=@hinh, gia_tien=@giaTien, trang_thai=@trangThai WHERE ma_mon=@maMon";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@maMon", System.Data.SqlDbType.Int, 0).Value = mon.MaMon;
command.Parameters.Add("@tenMon", System.Data.SqlDbType.NVarChar, 255).Value = mon.TenMon;
command.Parameters.Add("@loaiMon", System.Data.SqlDbType.NVarChar, 255).Value = mon.LoaiMon;
command.Parameters.Add("@hinh", System.Data.SqlDbType.NVarChar, 255).Value = mon.Hinh;
command.Parameters.Add("@giaTien", System.Data.SqlDbType.Float, 0).Value = mon.GiaTien;
command.Parameters.Add("@trangThai", System.Data.SqlDbType.Bit, 0).Value = mon.TrangThai;
connection.Open();
int reader = command.ExecuteNonQuery();
connection.Close();
if (reader == 1)
{
return(true);
}
return(false);
}
public static List <rptMon_LoaiMonDTO> DoiMaLoaiMonThanhLoaiMon(int maMon)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT Mon.ma_mon, Mon.ten_mon, LoaiMon.ten_loai_mon, Mon.gia_tien FROM Mon,LoaiMon WHERE Mon.loai_mon=LoaiMon.ma_loai_mon and Mon.ma_mon=@maMon";
SqlCommand command = new SqlCommand();
command.Parameters.Add("@maMon", System.Data.SqlDbType.DateTime, 0).Value = maMon;
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <rptMon_LoaiMonDTO> result = new List <rptMon_LoaiMonDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
rptMon_LoaiMonDTO mon_LoaiMonDTO = new rptMon_LoaiMonDTO();
mon_LoaiMonDTO.MaMon = reader.GetInt32(0);
mon_LoaiMonDTO.TenMon = reader.GetString(1);
mon_LoaiMonDTO.TenLoaiMon = reader.GetString(2);
mon_LoaiMonDTO.GiaTien = reader.GetDouble(3);
result.Add(mon_LoaiMonDTO);
}
}
connection.Close();
return(result);
}
public static bool SuaTopping(ToppingDTO topping)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "UPDATE Topping SET ten_topping=@tenTopping, loai_topping=@loaiTopping, gia_tien=@giaTien, hinh=@hinh, trang_thai=@trangThai WHERE ma_topping=@maTopping";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@maTopping", System.Data.SqlDbType.Int, 0).Value = topping.MaTopping;
command.Parameters.Add("@tenTopping", System.Data.SqlDbType.NVarChar, 255).Value = topping.TenTopping;
command.Parameters.Add("@loaiTopping", System.Data.SqlDbType.Int, 0).Value = topping.LoaiTopping;
command.Parameters.Add("@giaTien", System.Data.SqlDbType.Float, 0).Value = topping.GiaTien;
command.Parameters.Add("@hinh", System.Data.SqlDbType.NVarChar, 255).Value = topping.Hinh;
command.Parameters.Add("@trangThai", System.Data.SqlDbType.Bit, 0).Value = topping.TrangThai;
connection.Open();
int reader = command.ExecuteNonQuery();
connection.Close();
if (reader == 1)
{
return(true);
}
return(false);
}
public static List <ChiPhiDTO> LayChiPhiPhieuNhap()
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT SUM(tong_tien) chi_phi, MONTH(ngay_lap), YEAR(ngay_lap) FROM PhieuNhap WHERE trang_thai=1 AND YEAR(ngay_lap)=YEAR(GETDATE()) GROUP BY MONTH(ngay_lap), YEAR(ngay_lap) ORDER BY chi_phi DESC";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <ChiPhiDTO> result = new List <ChiPhiDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
ChiPhiDTO cp = new ChiPhiDTO();
cp.Thang = reader.GetInt32(1);
cp.ChiPhi = reader.GetDouble(0);
result.Add(cp);
}
}
connection.Close();
return(result);
}
public static bool LuuCTPhieuNhap(CTPhieuNhapDTO ctpn)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "INSERT INTO CTPhieuNhap (ma_phieu_nhap, ma_nguyen_lieu, so_luong, don_vi_tinh, don_gia, ghi_chu) VALUES (@maPhieuNhap, @maNguyenLieu, @soLuong, @donViTinh, @donGia, @ghiChu)";
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.Add("@maPhieuNhap", System.Data.SqlDbType.Int, 0).Value = ctpn.MaPhieuNhap;
command.Parameters.Add("@maNguyenLieu", System.Data.SqlDbType.Int, 0).Value = ctpn.MaNguyenLieu;
command.Parameters.Add("@soLuong", System.Data.SqlDbType.Float, 0).Value = ctpn.SoLuong;
command.Parameters.Add("@donViTinh", System.Data.SqlDbType.NVarChar, 255).Value = ctpn.DonViTinh;
command.Parameters.Add("@donGia", System.Data.SqlDbType.Float, 0).Value = ctpn.DonGia;
command.Parameters.Add("@ghiChu", System.Data.SqlDbType.NVarChar, 255).Value = ctpn.GhiChu;
connection.Open();
int reader = command.ExecuteNonQuery();
connection.Close();
if (reader > 0)
{
return(true);
}
return(false);
}
public static List <rptNhaCungCap_PhieuNhapDTO> DoiMaNhaCungCapThanhTenNhaCungCap()
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT PhieuNhap.ma_phieu_nhap,NhaCungCap.ten_nha_cung_cap, PhieuNhap.ngay_lap, PhieuNhap.tong_tien FROM NhaCungCap,PhieuNhap WHERE ma_nha_cung_cap = nha_cung_cap";
SqlCommand command = new SqlCommand();
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <rptNhaCungCap_PhieuNhapDTO> result = new List <rptNhaCungCap_PhieuNhapDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
rptNhaCungCap_PhieuNhapDTO nhaCungCap_PhieuNhap = new rptNhaCungCap_PhieuNhapDTO();
nhaCungCap_PhieuNhap.MaPhieuNhap = reader.GetInt32(0);
nhaCungCap_PhieuNhap.TenNhaCungCap = reader.GetString(1);
nhaCungCap_PhieuNhap.NgayLap = reader.GetDateTime(2);
nhaCungCap_PhieuNhap.TongTien = reader.GetDouble(3);
result.Add(nhaCungCap_PhieuNhap);
}
}
connection.Close();
return(result);
}
public static List <rptNhaCungCap_PhieuNhapDTO> DoiMaNhaCungCapThanhTenNhaCungCap(DateTime timeLine)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT PhieuNhap.ma_phieu_nhap,NhaCungCap.ten_nha_cung_cap, PhieuNhap.ngay_lap, PhieuNhap.tong_tien FROM NhaCungCap,PhieuNhap WHERE ma_nha_cung_cap = nha_cung_cap and DATEPART(YYYY, ngay_lap) = YEAR(@timeLine) AND DATEPART(MM, ngay_lap) = MONTH(@timeLine) AND DATEPART(DD, ngay_lap) = DAY(@timeLine)";
SqlCommand command = new SqlCommand();
command.Parameters.Add("@timeLine", System.Data.SqlDbType.DateTime, 0).Value = timeLine;
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <rptNhaCungCap_PhieuNhapDTO> result = new List <rptNhaCungCap_PhieuNhapDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
rptNhaCungCap_PhieuNhapDTO nhaCungCap_PhieuNhap = new rptNhaCungCap_PhieuNhapDTO();
nhaCungCap_PhieuNhap.MaPhieuNhap = reader.GetInt32(0);
nhaCungCap_PhieuNhap.TenNhaCungCap = reader.GetString(1);
nhaCungCap_PhieuNhap.NgayLap = reader.GetDateTime(2);
nhaCungCap_PhieuNhap.TongTien = reader.GetDouble(3);
result.Add(nhaCungCap_PhieuNhap);
}
}
connection.Close();
return(result);
}
public static List <MonDTO> LayDanhSachMon(int maLoaiMon, string timKiem, bool trangThai)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT ma_mon, ten_mon, loai_mon, LoaiMon.ten_loai_mon, hinh, gia_tien, Mon.trang_thai FROM Mon, LoaiMon WHERE LoaiMon.ma_loai_mon=Mon.loai_mon";
SqlCommand command = new SqlCommand();
if (maLoaiMon != 0)
{
query += " AND Mon.loai_mon=@maLoaiMon";
command.Parameters.Add("@maLoaiMon", System.Data.SqlDbType.Int, 0).Value = maLoaiMon;
}
if (timKiem != string.Empty)
{
query += " AND ten_mon LIKE N'%'+@timKiem+'%'";
command.Parameters.Add("@timKiem", System.Data.SqlDbType.NVarChar, 255).Value = timKiem;
}
if (trangThai)
{
query += " AND Mon.trang_thai=1 AND LoaiMon.trang_thai=1";
}
else
{
query += " AND Mon.trang_thai=0 AND LoaiMon.trang_thai=1";
}
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <MonDTO> result = new List <MonDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
MonDTO mon = new MonDTO();
mon.MaMon = reader.GetInt32(0);
mon.TenMon = reader.GetString(1);
mon.LoaiMon = reader.GetInt32(2);
mon.TenLoaiMon = reader.GetString(3);
if (!reader.IsDBNull(4))
{
mon.Hinh = reader.GetString(4);
}
mon.GiaTien = reader.GetDouble(5);
mon.TrangThai = reader.GetBoolean(6);
result.Add(mon);
}
}
connection.Close();
return(result);
}
public static List <TaiKhoanDTO> LayDanhSachTaiKhoan(string timKiem, int maLoaiTaiKhoan, bool trangThai)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT ma_tai_khoan, ho_ten, ngay_bat_dau, loai_tai_khoan, hinh, TaiKhoan.trang_thai FROM TaiKhoan, LoaiTaiKhoan WHERE TaiKhoan.loai_tai_khoan=LoaiTaiKhoan.ma_loai_tai_khoan";
SqlCommand command = new SqlCommand();
if (timKiem != "")
{
query += " AND ho_ten LIKE N'%'+@timKiem+'%'";
command.Parameters.Add("@timKiem", System.Data.SqlDbType.NVarChar, 255).Value = timKiem;
}
if (maLoaiTaiKhoan != 0)
{
query += " AND loai_tai_khoan=@loaiTaiKhoan";
command.Parameters.Add("@loaiTaiKhoan", System.Data.SqlDbType.Int, 0).Value = maLoaiTaiKhoan;
}
if (trangThai)
{
query += " AND LoaiTaiKhoan.trang_thai=1 AND TaiKhoan.trang_thai=1";
}
else
{
query += " AND LoaiTaiKhoan.trang_thai=1 AND TaiKhoan.trang_thai=0";
}
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <TaiKhoanDTO> result = new List <TaiKhoanDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
TaiKhoanDTO taiKhoan = new TaiKhoanDTO();
taiKhoan.MaTaiKhoan = reader.GetInt32(0);
taiKhoan.HoTen = reader.GetString(1);
taiKhoan.NgayBatDau = reader.GetDateTime(2);
taiKhoan.LoaiTaiKhoan = reader.GetInt32(3);
if (!reader.IsDBNull(4))
{
taiKhoan.Hinh = reader.GetString(4);
}
taiKhoan.TrangThai = reader.GetBoolean(5);
result.Add(taiKhoan);
}
}
connection.Close();
return(result);
}
public static int LayMaLoaiMonMoiNhat()
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT MAX(ma_loai_mon) FROM LoaiMon";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int result = Convert.ToInt32(command.ExecuteScalar());
connection.Close();
return(result);
}
public static int LayMaToppingMoiNhat()
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT MAX(ma_topping) FROM Topping";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
var result = command.ExecuteScalar();
connection.Close();
return(result is DBNull ? 0 : Convert.ToInt32(result));
}
public static List <ToppingDTO> LayDanhSachTopping(int maLoaiTopping, string timKiem, bool trangThai)
{
SqlConnection connection = DataProvider.GetConnection();
string query = "SELECT ma_topping, ten_topping, loai_topping, gia_tien, hinh, Topping.trang_thai FROM Topping, LoaiTopping WHERE LoaiTopping.ma_loai_topping=Topping.loai_topping";
SqlCommand command = new SqlCommand();
if (maLoaiTopping != 0)
{
query += " AND Topping.loai_topping=@MaLoaiTopping";
command.Parameters.Add("@MaLoaiTopping", System.Data.SqlDbType.Int, 0).Value = maLoaiTopping;
}
if (timKiem != "")
{
query += " AND ten_topping LIKE N'%'+@TimKiem+'%'";
command.Parameters.Add("@TimKiem", System.Data.SqlDbType.NVarChar, 255).Value = timKiem;
}
if (trangThai)
{
query += " AND Topping.trang_thai=1 AND LoaiTopping.trang_thai=1";
}
else
{
query += " AND Topping.trang_thai=0 AND LoaiTopping.trang_thai=1";
}
command.CommandText = query;
command.Connection = connection;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List <ToppingDTO> result = new List <ToppingDTO>();
if (reader.HasRows)
{
while (reader.Read())
{
ToppingDTO topping = new ToppingDTO();
topping.MaTopping = reader.GetInt32(0);
topping.TenTopping = reader.GetString(1);
topping.LoaiTopping = reader.GetInt32(2);
topping.GiaTien = reader.GetDouble(3);
topping.Hinh = reader.GetString(4);
topping.TrangThai = reader.GetBoolean(5);
result.Add(topping);
}
}
connection.Close();
return(result);
}
