//////////////////////////////////
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 <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 <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 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 <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 <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 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 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);
}
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 bool UpdateNguyenLieu(clsNguyenLieu_DTO NguyenLieu)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("UPDATE NguyenLieu Set anh_nguyen_lieu=N'{0}', ten_nguyen_lieu=N'{1}', tong_so_luong=N'{2}',don_vi_tinh=N'{3}',don_gia=N'{4}',ma_loai_nguyen_lieu=N'{5}',trang_thai=N'{6}' where ma_nguyen_lieu=N'{7}'", NguyenLieu.AnhNguyenLieu, NguyenLieu.TenNguyenLieu, NguyenLieu.TongSoLuong, NguyenLieu.DonViTinh, NguyenLieu.DonGia, NguyenLieu.MaLoaiNguyenLieu, NguyenLieu.TrangThai, NguyenLieu.MaNguyenLieu);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
public static bool InsertNguyenLieu(clsNguyenLieu_DTO NguyenLieu)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("INSERT INTO NguyenLieu (ma_nguyen_lieu, anh_nguyen_lieu, ten_nguyen_lieu, tong_so_luong,don_vi_tinh,don_gia,ma_loai_nguyen_lieu,trang_thai ) VALUES ('{0}',N'{1}',N'{2}',N'{3}',N'{4}',N'{5}','{6}',N'{7}')", NguyenLieu.MaNguyenLieu, NguyenLieu.AnhNguyenLieu, NguyenLieu.TenNguyenLieu, NguyenLieu.TongSoLuong, NguyenLieu.DonViTinh, NguyenLieu.DonGia, NguyenLieu.MaLoaiNguyenLieu, NguyenLieu.TrangThai);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
public static bool UpNhaCungCap(clsNhaCungCap_DTO NhaCungCap)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("UPDATE NhaCungCap Set ten_nha_cung_cap =N'{0}',dia_chi =N'{1}',so_dien_thoai=N'{2}',trang_thai=N'{3}' where ma_nha_cung_cap = N'{4}' ", NhaCungCap.TenNhaCungCap, NhaCungCap.DiaChi, NhaCungCap.SoDienThoai, NhaCungCap.TrangThai, NhaCungCap.MaNhaCungCap);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
public static bool UpdateCuaHang(clsCuaHang_DTO CH) //xóa món ăn
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("UPDATE CuaHang Set ten_cua_hang = N'{0}', dia_chi = N'{1}', so_dien_thoai = N'{2}', passwifi = N'{3}', giay_phep_kinh_doanh = N'{4}' where ma_cua_hang = N'{5}' ", CH.TenCuaHang, CH.DiaChi, CH.SoDienThoai, CH.Passwifi, CH.GiayPhepKinhDoanh, CH.MaCuaHang);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
public static bool InsertDonViTinh(clsDonViTinh_DTO DonViTinh)
{
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = string.Format("INSERT INTO DonViTinh VALUES ('{0}',N'{1}',N'{2}')", DonViTinh.MaDonViTinh, DonViTinh.TenDonViTinh, DonViTinh.TrangThai);
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
int ketqua = cmd.ExecuteNonQuery();
ThaoTacDuLieu_DAO.DongKetNoi(con);
return((ketqua == 1) ? true : false);
}
public static List <clsHoaDonNhap_DTO> LayDanhSachHoaDonNhap(string From, string To)
{
List <clsHoaDonNhap_DTO> lstHD = new List <clsHoaDonNhap_DTO>();
SqlConnection con = ThaoTacDuLieu_DAO.TaoKetNoi();
string query = "SELECT * FROM HoaDonNhap";
if (From != "" && To != "")
{
query += " where ngay_tao_hoa_don >= @from and ngay_tao_hoa_don <= @to ";
}
SqlCommand cmd = ThaoTacDuLieu_DAO.TruyVan(query, con);
if (From != "" && To != "")
{
cmd.Parameters.AddWithValue("@from", DateTime.Parse(From).ToString("MM-dd-yyyy hh:mm:ss"));
cmd.Parameters.AddWithValue("@to", DateTime.Parse(To).ToString("MM-dd-yyyy hh:mm:ss"));
}
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
clsHoaDonNhap_DTO HD = new clsHoaDonNhap_DTO();
if (!reader.IsDBNull(0))
{
HD.MaHoaDon = (string)reader["ma_hoa_don_nhap"];
}
if (!reader.IsDBNull(1))
{
HD.MaNhanVien = (string)reader["ma_nhan_vien"];
}
if (!reader.IsDBNull(2))
{
HD.NgayTaoHoaDon = (DateTime)reader["ngay_tao_hoa_don"];
}
if (!reader.IsDBNull(3))
{
HD.TongTien = (float)(double)reader["tong_tien_phai_tra"];
}
if (!reader.IsDBNull(4))
{
HD.TrangThai = (bool)reader["trang_thai"];
}
lstHD.Add(HD);
}
ThaoTacDuLieu_DAO.DongKetNoi(con);
return(lstHD);
}
