public bool ThaoTacVoiKhachHangg(clsKhachHang_DTO KH, string command)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = "";
if (command == "Add")
{
query = "INSERT INTO KhachHang (ma_khach_hang, ten_khach_hang, gioi_tinh, ngay_sinh, so_dien_thoai, dia_chi, trang_thai) VALUES (@ma, @ten, @gioiTinh, @ngaySinh, @sdt, @diaChi, @trangThai)";
}
else if (command == "Update")
{
query = "UPDATE KhachHang SET ten_khach_hang = @ten, gioi_tinh = @gioiTinh, ngay_sinh = @ngaySinh, so_dien_thoai = @sdt, dia_chi = @diaChi, trang_thai = @trangThai WHERE ma_khach_hang = @ma";
}
//else if (command == "Delete")
// query += string.Format("UPDATE KhachHang SET trang_thai = {0} WHERE ma_khach_hang = '{1}'", KH.TrangThai, KH.MaKhachHang);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
if (command == "Add" || command == "Update")
{
cmd.Parameters.AddWithValue("@ma", KH.MaKhachHang);
cmd.Parameters.AddWithValue("@ten", KH.TenKhachHang);
cmd.Parameters.AddWithValue("@gioiTinh", KH.GioiTinh);
cmd.Parameters.AddWithValue("@ngaySinh", KH.NgaySinh);
cmd.Parameters.AddWithValue("@sdt", KH.SoDienThoai);
cmd.Parameters.AddWithValue("@diaChi", KH.DiaChi);
cmd.Parameters.AddWithValue("@trangThai", KH.TrangThai);
}
return(cmd.ExecuteNonQuery() > 0);
}
public List <clsTrangThaiBanPhanCong_DTO> LayDSTrangThaiBPC()
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = "SELECT * FROM TrangThaiBanPhanCong";
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
SqlDataReader reader = cmd.ExecuteReader();
List <clsTrangThaiBanPhanCong_DTO> listTTBPC = new List <clsTrangThaiBanPhanCong_DTO>();
while (reader.Read())
{
clsTrangThaiBanPhanCong_DTO TTBPC = new clsTrangThaiBanPhanCong_DTO();
if (!reader.IsDBNull(0))
{
TTBPC.MaTrangThaiBanPhanCong = (int)reader[0];
}
if (!reader.IsDBNull(1))
{
TTBPC.TenTrangThaiBanPhanCong = (string)reader[1];
}
listTTBPC.Add(TTBPC);
}
ThaoTacDuLieu_DAO.DongKetNoi(con);
return(listTTBPC);
}
public static List <clsLoaiNguyenLieu_DTO> LayDSLoaiNguyenLieu(string ChuoiTimKiem)
{
string query = string.Format("select * from LoaiNguyenLieu");
if (ChuoiTimKiem != "")
{
query += ChuoiTimKiem;
}
List <clsLoaiNguyenLieu_DTO> dsMonAn = new List <clsLoaiNguyenLieu_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
SqlDataReader rddtMA = cmd.ExecuteReader();
while (rddtMA.Read())
{
clsLoaiNguyenLieu_DTO itemLoaiNguyenLieu = new clsLoaiNguyenLieu_DTO();
if (!rddtMA.IsDBNull(0))
{
itemLoaiNguyenLieu.MaLoaiNguyenLieu = (string)rddtMA[0];
}
if (!rddtMA.IsDBNull(1))
{
itemLoaiNguyenLieu.TenLoaiNguyenLieu = (string)rddtMA[1];
}
if (!rddtMA.IsDBNull(2))
{
itemLoaiNguyenLieu.TrangThai = (bool)rddtMA[2];
}
dsMonAn.Add(itemLoaiNguyenLieu);
}
ThaoTacDuLieu_DAO.DongKetNoi(con);
return(dsMonAn);
}
public bool ThaoTacVoiTaiKhoan(clsTaiKhoan_DTO TK, string command)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = "";
if (command == "Add")
{
query = "INSERT INTO TaiKhoan(ma_dang_nhap, mat_khau, ngay_tao, trang_thai)"
+ " VALUES(@ma_dang_nhap, @mat_khau, @ngay_tao, @trang_thai)";
}
else if (command == "Update")
{
query = "UPDATE TaiKhoan SET mat_khau = @mat_khau, ngay_tao = @ngay_tao, trang_thai = @trang_thai WHERE ma_dang_nhap = @ma_dang_nhap";
}
else if (command == "Delete")
{
query = string.Format("UPDATE TaiKhoan SET trang_thai = {0} WHERE ma_dang_nhap = '{1}'", TK.TrangThai, TK.MaDangNhap);
}
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
if (command == "Add" || command == "Update")
{
cmd.Parameters.AddWithValue("@ma_dang_nhap", TK.MaDangNhap);
cmd.Parameters.AddWithValue("@mat_khau", TK.MatKhau);
cmd.Parameters.AddWithValue("@ngay_tao", TK.NgayTao);
//cmd.Parameters.AddWithValue("@ma_quyen_dang_nhap", TK.MaQuyenDangNhap);
//cmd.Parameters.AddWithValue("@ma_khan_cap", TK.MaKhanCap);
cmd.Parameters.AddWithValue("@trang_thai", TK.TrangThai);
}
return(cmd.ExecuteNonQuery() > 0);
}
public bool ThaoTacVoiDoiTuongTheKhachHang(clsTheKhachHang TKH, string command)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = "";
if (command == "Add")
{
query = "INSERT INTO TheKhachHang(ma_the, ma_khach_hang, ma_loai_the, ngay_dang_ky, trang_thai) VALUES(@ma_the, @ma_khach_hang, @ma_loai_the, @ngay_dang_ky, @trang_thai)";
}
else if (command == "Update")
{
query = "UPDATE TheKhachHang SET ma_khach_hang = @ma_khach_hang, ma_loai_the = @ma_loai_the, ngay_dang_ky = @ngay_dang_ky, trang_thai = @trang_thai WHERE ma_the = @ma_the";
}
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
if (command == "Add" || command == "Update")
{
cmd.Parameters.AddWithValue("@ma_the", TKH.MaThe);
cmd.Parameters.AddWithValue("@ma_khach_hang", TKH.MaKhachHang);
cmd.Parameters.AddWithValue("@ma_loai_the", TKH.MaLoaiThe);
cmd.Parameters.AddWithValue("@ngay_dang_ky", TKH.NgayDangKy);
cmd.Parameters.AddWithValue("@trang_thai", TKH.TrangThai);
}
return(cmd.ExecuteNonQuery() > 0);
}
public static List <clsLoaiMonAn_DTO> LaydsLoaiMonAn()
{
List <clsLoaiMonAn_DTO> dsLoaiMonAn = new List <clsLoaiMonAn_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("select * from LoaiMonAn");
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
SqlDataReader rddtMA = cmd.ExecuteReader();
while (rddtMA.Read())
{
clsLoaiMonAn_DTO itemLoaiMonAn = new clsLoaiMonAn_DTO();
if (!rddtMA.IsDBNull(0))
{
itemLoaiMonAn.MaLoaiMonAn = (string)rddtMA[0];
}
if (!rddtMA.IsDBNull(2))
{
itemLoaiMonAn.TenLoaiMonAn = (string)rddtMA[2];
}
if (!rddtMA.IsDBNull(1))
{
itemLoaiMonAn.MaNhomMonAn = (string)rddtMA[1];
}
//
dsLoaiMonAn.Add(itemLoaiMonAn);
}
ThaoTacDuLieu_DAO.DongKetNoi(con);
return(dsLoaiMonAn);
}
public static bool AddHoaDonBan(clsHoaDonBan HoaDonBan)
{
//(ma_hoa_don_ban, ma_nhan_vien, ngay_lap_hoa_don, tong_tien, tien_dua, tien_thoi, giao_hang, ma_khach_hang, trang_thai)
string temp = HoaDonBan.NgayLapHoaDon.ToString("MM-dd-yyyy hh:mm:ss");
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
//string query = string.Format("INSERT INTO HoaDonBan VALUES (N'{0}',N'{1}',{2},N'{3}',N'{4}',N'{5}',N'{6}',N'{7}',N'{8}')", HoaDonBan.MaHoaDon, HoaDonBan.MaNhanVien, temp, HoaDonBan.TongTien, HoaDonBan.TienDua, HoaDonBan.TienThoi, HoaDonBan.GiaoHang, HoaDonBan.MaKhachHang, HoaDonBan.TrangThai);
string query = "INSERT INTO HoaDonBan VALUES(@ma_hoa_don_ban, @ma_nhan_vien,@ngay_lap_hoa_don, @tong_tien, @tien_dua, @tien_thoi, @VAT, @giao_hang, @ma_khach_hang, @trang_thai)";
//, @ngay_lap_hoa_don
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
cmd.Parameters.AddWithValue("@ma_hoa_don_ban", HoaDonBan.MaHoaDon);
cmd.Parameters.AddWithValue("@ma_nhan_vien", HoaDonBan.MaNhanVien);
cmd.Parameters.AddWithValue("@ngay_lap_hoa_don", temp);
cmd.Parameters.AddWithValue("@tong_tien", HoaDonBan.TongTien);
cmd.Parameters.AddWithValue("@tien_dua", HoaDonBan.TienDua);
cmd.Parameters.AddWithValue("@tien_thoi", HoaDonBan.TienThoi);
cmd.Parameters.AddWithValue("@VAT", HoaDonBan.VAT);
cmd.Parameters.AddWithValue("@giao_hang", HoaDonBan.GiaoHang);
cmd.Parameters.AddWithValue("@ma_khach_hang", HoaDonBan.MaKhachHang);
cmd.Parameters.AddWithValue("@trang_thai", HoaDonBan.TrangThai);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
//////////////////////////////////
public static List <clsKichThuocMonAn_DTO> LaydsKichThuocTheoMaSanPham(string a, bool b)
{
List <clsKichThuocMonAn_DTO> dsKichThuoc = new List <clsKichThuocMonAn_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("select ChiTietMonAn.ma_kich_thuoc, KichThuocMonAn.ten_kich_thuoc,ChiTietMonAn.trang_thai from ChiTietMonAn, KichThuocMonAn where ChiTietMonAn.ma_kich_thuoc = KichThuocMonAn.ma_kich_thuoc AND ma_mon_an = '{0}' AND ChiTietMonAn.trang_thai = N'{1}' ", a, b);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
SqlDataReader rddtMA = cmd.ExecuteReader();
while (rddtMA.Read())
{
clsKichThuocMonAn_DTO itemKichThuoc = new clsKichThuocMonAn_DTO();
if (!rddtMA.IsDBNull(0))
{
itemKichThuoc.MaKichThuoc = (string)rddtMA[0];
}
if (!rddtMA.IsDBNull(1))
{
itemKichThuoc.TenKichThuoc = (string)rddtMA[1];
}
if (!rddtMA.IsDBNull(2))
{
itemKichThuoc.TrangThai = (bool)rddtMA[2];
}
//
dsKichThuoc.Add(itemKichThuoc);
}
ThaoTacDuLieu_DAO.DongKetNoi(con);
return(dsKichThuoc);
}
public List <clsChucVu_DTO> LayDanhSachChucVu()
{
List <clsChucVu_DTO> dsChucVu = new List <clsChucVu_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string cauTruyVan = string.Format("SELECT * FROM ChucVu WHERE trang_thai = {0}", 1);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(cauTruyVan, con);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
clsChucVu_DTO chucVu = new clsChucVu_DTO();
if (!reader.IsDBNull(0))
{
chucVu.MaChucVu = (string)reader["ma_chuc_vu"];
}
if (!reader.IsDBNull(1))
{
chucVu.TenChucVu = (string)reader["ten_chuc_vu"];
}
if (!reader.IsDBNull(2))
{
chucVu.TrangThai = (bool)reader["trang_thai"];
}
dsChucVu.Add(chucVu);
}
return(dsChucVu);
}
public static List <clsChucNang_DTO> LayChucNangTheoMaNhanVien(string maNhanVien)
{
List <clsChucNang_DTO> dsChucNang = new List <clsChucNang_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string Query = string.Format("SELECT ChucNang.ma_chuc_nang, ten_chuc_nang, ghi_chu, ChucNang.trang_thai FROM NhanVien, ChucNang, ChucVu, ChiTietChucNang WHERE NhanVien.ma_nhan_vien='{0}' AND NhanVien.ma_chuc_vu = ChucVu.ma_chuc_vu AND ChiTietChucNang.ma_chuc_vu = ChucVu.ma_chuc_vu AND ChiTietChucNang.ma_chuc_nang = ChucNang.ma_chuc_nang AND ChiTietChucNang.trang_thai = {1} AND ChucNang.trang_thai = {1}", maNhanVien, 1);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(Query, con);
SqlDataReader rddtSV = cmd.ExecuteReader();
while (rddtSV.Read())
{
clsChucNang_DTO chucNang = new clsChucNang_DTO();
if (!rddtSV.IsDBNull(0))
{
chucNang.MaChucNang = (string)rddtSV[0];
}
if (!rddtSV.IsDBNull(1))
{
chucNang.TenChucNang = rddtSV[1].ToString();
}
if (!rddtSV.IsDBNull(2))
{
chucNang.GhiChu = rddtSV[2].ToString();
}
if (!rddtSV.IsDBNull(3))
{
chucNang.TrangThai = (bool)rddtSV[3];
}
dsChucNang.Add(chucNang);
}
ThaoTacDuLieu_DAO.DongKetNoi(con);
return(dsChucNang);
}
public List <clsTrangThaiNV_DTO> LayDSTrangThaiNhanVien()
{
List <clsTrangThaiNV_DTO> dsTrangThaiNV = new List <clsTrangThaiNV_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string cauTruyVan = "SELECT TOP 3 * FROM TrangThaiNhanVien";
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(cauTruyVan, con);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
clsTrangThaiNV_DTO trangThaiNV = new clsTrangThaiNV_DTO();
if (!reader.IsDBNull(0))
{
trangThaiNV.MaTrangThaiNV = (int)reader[0];
}
if (!reader.IsDBNull(1))
{
trangThaiNV.TenTrangThaiNV = (string)reader[1];
}
dsTrangThaiNV.Add(trangThaiNV);
}
return(dsTrangThaiNV);
}
public List <clsQuyenDangNhap_DTO> LayQuyenDangNhapTheoMaQDN(string maQDN)
{
List <clsQuyenDangNhap_DTO> dsQuyenDangNhap = new List <clsQuyenDangNhap_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("SELECT * FROM QuyenDangNhap WHERE ma_quyen_dang_nhap LIKE '%{0}%' AND trang_thai = {1}", maQDN, 1);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
clsQuyenDangNhap_DTO quyenDN = new clsQuyenDangNhap_DTO();
if (!reader.IsDBNull(0))
{
quyenDN.MaQuyenDangNhap = (string)reader[0];
}
if (!reader.IsDBNull(1))
{
quyenDN.TenQuyenDangNhap = reader[1].ToString();
}
if (!reader.IsDBNull(2))
{
quyenDN.TrangThai = (bool)reader[2];
}
dsQuyenDangNhap.Add(quyenDN);
}
ThaoTacDuLieu_DAO.DongKetNoi(con);
return(dsQuyenDangNhap);
}
public static List <clsKichThuocMonAn_DTO> LaydsKichThuoc()
{
List <clsKichThuocMonAn_DTO> dsKichThuoc = new List <clsKichThuocMonAn_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("select * from KichThuocMonAn");
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
SqlDataReader rddtMA = cmd.ExecuteReader();
while (rddtMA.Read())
{
clsKichThuocMonAn_DTO itemKichThuoc = new clsKichThuocMonAn_DTO();
if (!rddtMA.IsDBNull(0))
{
itemKichThuoc.MaKichThuoc = (string)rddtMA[0];
}
if (!rddtMA.IsDBNull(1))
{
itemKichThuoc.TenKichThuoc = (string)rddtMA[1];
}
if (!rddtMA.IsDBNull(2))
{
itemKichThuoc.TrangThai = (bool)rddtMA[2];
}
//
dsKichThuoc.Add(itemKichThuoc);
}
ThaoTacDuLieu_DAO.DongKetNoi(con);
return(dsKichThuoc);
}
public List <clsChiTietBanPhanCong_DTO> LayLichLamViec(string maNV, DateTime dtFrom, DateTime dtTo)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = "SELECT * FROM ChiTietBanPhanCong WHERE trang_thai != 6 AND ma_nhan_vien = @ma_nhan_vien AND ngay_lam_viec >= @ngay_lam_viec_tu AND ngay_lam_viec <= @ngay_lam_viec_den ORDER BY ngay_lam_viec DESC, ma_ca_lam_viec";
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
cmd.Parameters.AddWithValue("@ma_nhan_vien", maNV);
cmd.Parameters.AddWithValue("@ngay_lam_viec_tu", dtFrom.ToString("yyyy-MM-dd"));
cmd.Parameters.AddWithValue("@ngay_lam_viec_den", dtTo.ToString("yyyy-MM-dd"));
List <clsChiTietBanPhanCong_DTO> lstCTBPC = new List <clsChiTietBanPhanCong_DTO>();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
clsChiTietBanPhanCong_DTO CTBPC = new clsChiTietBanPhanCong_DTO();
if (!reader.IsDBNull(0))
{
CTBPC.MaBanGhi = (string)reader["ma_ban_ghi"];
}
if (!reader.IsDBNull(1))
{
CTBPC.MaNhanVien = (string)reader["ma_nhan_vien"];
}
if (!reader.IsDBNull(2))
{
CTBPC.MaCaLamViec = (string)reader["ma_ca_lam_viec"];
}
if (!reader.IsDBNull(3))
{
CTBPC.NgayLamViec = (DateTime)reader["ngay_lam_viec"];
}
if (!reader.IsDBNull(4))
{
CTBPC.NhanVienTao = (string)reader["nhan_vien_tao"];
}
if (!reader.IsDBNull(5))
{
CTBPC.NgayThem = (DateTime)reader["ngay_them"];
}
if (!reader.IsDBNull(6))
{
CTBPC.CoMat = (Boolean)reader["co_mat"];
}
if (!reader.IsDBNull(7))
{
CTBPC.NghiCoPhep = (Boolean)reader["nghi_co_phep"];
}
if (!reader.IsDBNull(8))
{
CTBPC.TrangThai = (int)reader["trang_thai"];
}
lstCTBPC.Add(CTBPC);
}
ThaoTacDuLieu_DAO.DongKetNoi(con);
return(lstCTBPC);
}
public List <clsNhanVien_DTO> LayNhanVienTheoTenHoacMaa(string ten, string ma)
{
List <clsNhanVien_DTO> lstNhanVien = new List <clsNhanVien_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("SELECT * FROM NhanVien WHERE ho_ten LIKE N'%{0}%' OR ma_nhan_vien ='{1}' ", ten, ma);
return(ThaoTacDuLieu_DAO.LayDanhSachNhanVien(query, con));
}
public string LayTenKhacHangQuaMaThe(string maThe)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("SELECT ten_khach_hang FROM KhachHang, TheKhachHang WHERE KhachHang.ma_khach_hang = TheKhachHang.ma_khach_hang AND ma_the = '{0}'", maThe);
SqlCommand cmd = new SqlCommand(query, con);
return((string)cmd.ExecuteScalar());
}
public bool XoaTaiKhoanKhiXoaNhanVien(string maNV)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("UPDATE TaiKhoan SET trang_thai = 'false' WHERE ma_dang_nhap = '{0}'", maNV);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
return(cmd.ExecuteNonQuery() > 0);
}
public bool Update(string maBanGhi, int trangThai)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("UPDATE ChiTietBanPhanCong SET trang_thai = {0} WHERE ma_ban_ghi = '{1}'", trangThai, maBanGhi);
SqlCommand cmd = new SqlCommand(query, con);
return(cmd.ExecuteNonQuery() > 0);
}
public static List <clsCuaHang_DTO> LaydsCuaHang()
{
List <clsCuaHang_DTO> dsCuaHang = new List <clsCuaHang_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("select * from CuaHang");
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
SqlDataReader rddtMA = cmd.ExecuteReader();
while (rddtMA.Read())
{
clsCuaHang_DTO itemCuaHangTinh = new clsCuaHang_DTO();
if (!rddtMA.IsDBNull(0))
{
itemCuaHangTinh.MaCuaHang = (string)rddtMA[0];
}
if (!rddtMA.IsDBNull(1))
{
itemCuaHangTinh.TenCuaHang = (string)rddtMA[1];
}
if (!rddtMA.IsDBNull(2))
{
itemCuaHangTinh.Logo = (string)rddtMA[2];
}
if (!rddtMA.IsDBNull(3))
{
itemCuaHangTinh.DiaChi = (string)rddtMA[3];
}
if (!rddtMA.IsDBNull(4))
{
itemCuaHangTinh.SoDienThoai = (string)rddtMA[4];
}
if (!rddtMA.IsDBNull(5))
{
itemCuaHangTinh.Passwifi = (string)rddtMA[5];
}
if (!rddtMA.IsDBNull(6))
{
itemCuaHangTinh.GiayPhepKinhDoanh = (string)rddtMA[6];
}
if (!rddtMA.IsDBNull(7))
{
itemCuaHangTinh.TrangThai = (bool)rddtMA[7];
}
//
dsCuaHang.Add(itemCuaHangTinh);
}
ThaoTacDuLieu_DAO.DongKetNoi(con);
return(dsCuaHang);
}
public static List <clsNguyenLieu_DTO> LaydsNguyenLieu(string ChuoiTimKiem)
{
string query = string.Format("select * from NguyenLieu");
if (ChuoiTimKiem != "")
{
query += ChuoiTimKiem;
}
List <clsNguyenLieu_DTO> dsNguyenLieu = new List <clsNguyenLieu_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
SqlDataReader rddtMA = cmd.ExecuteReader();
while (rddtMA.Read())
{
clsNguyenLieu_DTO itemThuocTinhNguyenLieu = new clsNguyenLieu_DTO();
if (!rddtMA.IsDBNull(0))
{
itemThuocTinhNguyenLieu.MaNguyenLieu = (string)rddtMA[0];
}
if (!rddtMA.IsDBNull(1))
{
itemThuocTinhNguyenLieu.AnhNguyenLieu = (string)rddtMA[1];
}
if (!rddtMA.IsDBNull(2))
{
itemThuocTinhNguyenLieu.TenNguyenLieu = (string)rddtMA[2];
}
if (!rddtMA.IsDBNull(3))
{
itemThuocTinhNguyenLieu.TongSoLuong = (float)(double)rddtMA[3];
}
if (!rddtMA.IsDBNull(4))
{
itemThuocTinhNguyenLieu.DonViTinh = (string)rddtMA[4]; // mã
}
if (!rddtMA.IsDBNull(5))
{
itemThuocTinhNguyenLieu.DonGia = (float)(double)rddtMA[5];
}
if (!rddtMA.IsDBNull(6))
{
itemThuocTinhNguyenLieu.MaLoaiNguyenLieu = (string)rddtMA[6];
}
if (!rddtMA.IsDBNull(7))
{
itemThuocTinhNguyenLieu.TrangThai = (bool)rddtMA[7];
}
//
dsNguyenLieu.Add(itemThuocTinhNguyenLieu);
}
ThaoTacDuLieu_DAO.DongKetNoi(con);
return(dsNguyenLieu);
}
//Phân công
public List <clsNhanVien_DTO> LayDanhSachNhanVienTrongPhanCong(string keyword)
{
List <clsNhanVien_DTO> lstNhanVien = new List <clsNhanVien_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("SELECT * FROM NhanVien WHERE ma_trang_thai_nhan_vien = {2} AND ma_chuc_vu = '{0}' AND (ho_ten LIKE N'%{1}%' OR ma_nhan_vien = '{1}' )", "CV002", keyword, 1);
return(ThaoTacDuLieu_DAO.LayDanhSachNhanVien(query, con));
}
public string LayMaNhanVien()
{
//SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = "SELECT COUNT(*) FROM NhanVien";
//SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
//string maNV = cmd.ExecuteScalar().ToString();
return(ThaoTacDuLieu_DAO.ExecuteScalar(query).ToString());
}
public static bool AddChiTietHoaDonBan(clsChiTietHoaDonBan_DTO ChiTietHoaDonBan)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("INSERT INTO ChiTietHoaDonBan VALUES (N'{0}',N'{1}',N'{2}',N'{3}',N'{4}',N'{5}')", ChiTietHoaDonBan.MaHoaDonBan, ChiTietHoaDonBan.MaMonAn, ChiTietHoaDonBan.DonGia, ChiTietHoaDonBan.SoLuong, ChiTietHoaDonBan.GiamGia, ChiTietHoaDonBan.ThanhTien);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
public static bool InsertThanhPhanMonAn(clsThanhPhanMonAn_DTO InsertTPMon)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("INSERT INTO ThanhPhan (ma_mon_an, ma_nguyen_lieu, so_luong, don_vi) VALUES ('{0}','{1}','{2}','{3}')", InsertTPMon.MaMonAn, InsertTPMon.MaNguyenLieu, InsertTPMon.SoLuong, InsertTPMon.DonVi);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
public static bool UpLoaiNguyenLieu(clsLoaiNguyenLieu_DTO LoaiNguyenLieu)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("UPDATE MonAn Set ten_loai_nguyen_lieu =N'{0}',trang_thai =N'{1}' where ma_loai_nguyen_lieu = N'{2}' ", LoaiNguyenLieu.MaLoaiNguyenLieu, LoaiNguyenLieu.TenLoaiNguyenLieu, LoaiNguyenLieu.TrangThai);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
public static bool InsertLoaiNguyenLieu(clsLoaiNguyenLieu_DTO LoaiNguyenLieu)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("INSERT INTO LoaiNguyenLieu (ma_loai_nguyen_lieu, ten_loai_nguyen_lieu, trang_thai) VALUES ('{0}',N'{1}',N'{2}')", LoaiNguyenLieu.MaLoaiNguyenLieu, LoaiNguyenLieu.TenLoaiNguyenLieu, LoaiNguyenLieu.TrangThai);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
//Lấy giá tiền của chi tiết món ăn
public static float GiaMonAnChiTiet(string MaMonAn, string KichThuocMonAn)
{
string query = string.Format("select gia_ban from ChiTietMonAn where ma_mon_an = N'{0}' and ma_kich_thuoc= N'{1}' AND trang_thai = N'True' ", MaMonAn, KichThuocMonAn);
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
float TongSP = float.Parse(cmd.ExecuteScalar().ToString());
ThaoTacDuLieu_DAO.DongKetNoi(con);
return(TongSP);
}
public static bool UpMonAn(clsMonAn_DTO UpMon)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("UPDATE MonAn Set ten_mon_an = N'{0}', anh_mon_an =N'{1}',ghi_chu =N'{2}',ma_loai_mon_an = N'{3}',ma_don_vi_tinh=N'{4}' where ma_mon_an = N'{5}' ", UpMon.TenMonAn, UpMon.AnhMonAn, UpMon.GhiChu, UpMon.MaLoaiMon, UpMon.MaDonViTinh, UpMon.MaMonAn);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
//
public static bool UpDeleteMonAn(string MaMonAn, bool isDelete) //xóa món ăn
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("UPDATE MonAn Set trang_thai = N'{0}' where ma_mon_an = N'{1}' ", isDelete, MaMonAn);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
public static bool AddChiTietMonAn(clsChiTietMonAn_DTO ChiTietMonAn)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("INSERT INTO ChiTietMonAn(ma_mon_an, ma_kich_thuoc, gia_ban, trang_thai) VALUES (N'{0}',N'{1}','{2}',N'{3}')", ChiTietMonAn.MaMonAn, ChiTietMonAn.MaKichThuoc, ChiTietMonAn.GiaBan, ChiTietMonAn.TrangThai);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
