public void Them(DTO_HoaDon HoaDon)
{
string strSQL = "INSERT INTO HoaDon(MaHD,MaBan,MaKH,MaTK_NV,NgayHoaDon,MaDo_Uong,DonGia,SoLuong,ThanhTien,TrangThai) VALUES ";
strSQL = strSQL + "('" + HoaDon.MaHD + "', '" + HoaDon.MaBan + "','" + HoaDon.MaKH + "','" + HoaDon.MaTK_NV + "',GETDATE(),'" + HoaDon.MaDo_Uong + "'," + HoaDon.DonGia + "," + HoaDon.SoLuong + "," + HoaDon.ThanhTien + ",'0')";
KetNoiSQL.ExecuteNonQuery(strSQL);
}
public DataTable Table_No()
{
string strSQL = "SELECT MaBan,TenBan FROM Ban WHERE MaBan NOT IN (SELECT MaBan FROM HoaDon WHERE TrangThai = '0')";
strSQL = strSQL + "UNION ALL ";
strSQL = strSQL + "SELECT a.MaBan,TenBan FROM HoaDon a INNER JOIN Ban b ON a.MaBan = b.MaBan ";
strSQL = strSQL + "WHERE TrangThai = '1' AND a.MaBan NOT IN (SELECT MaBan FROM Ban) ";
return(KetNoiSQL.GetData(strSQL));
}
public DataTable XemLaiHoaDon(DateTime ngay1, DateTime ngay2, string MaBan)
{
string strSQL = "SELECT NgayHoaDon,TenBan,TenKH,TenDo_Uong,a.DonGia,a.SoLuong,ThanhTien ";
strSQL += "FROM HoaDon a LEFT JOIN Ban b ON a.MaBan = b.MaBan ";
strSQL += "LEFT JOIN KhachHang c ON a.MaKH = c.MaKH ";
strSQL += "LEFT JOIN DoUong d ON a.MaDo_Uong = d.MaDo_Uong ";
strSQL += " WHERE CONVERT(DATETIME, NgayHoaDon,103) BETWEEN CONVERT(DATETIME, '" + ngay1 + "',103) AND CONVERT(DATETIME, '" + ngay2 + "',103) AND a.MaBan ='" + MaBan + "' AND TrangThai = '1'";
return(KetNoiSQL.GetData(strSQL));
}
public int KiemTraTrung(string MaTK_NV)
{
int i = 0;
string strSQL = "SELECT * FROM TaiKhoan_NhanVien WHERE MaTK_NV = '" + MaTK_NV + "'";
DataTable dt = KetNoiSQL.GetData(strSQL);
if (dt == null || dt.Rows.Count > 0)
{
i = 0;
}
else
{
i = 1;
}
return(i);
}
public int KiemTraTrung(string MaDo_Uong)
{
int i = 0;
string strSQL = "SELECT * FROM DoUong WHERE MaDo_Uong = '" + MaDo_Uong + "'";
DataTable dt = KetNoiSQL.GetData(strSQL);
if (dt == null || dt.Rows.Count > 0)
{
i = 0;
}
else
{
i = 1;
}
return(i);
}
public DataTable HoaDonTheoMa(string MaHD)
{
string strSQL = "SELECT * FROM HoaDon WHERE MaHD = '" + MaHD + "'";
return(KetNoiSQL.GetData(strSQL));
}
public DataTable DanhSachHoaDon(string MaBan)
{
string strSQL = "SELECT MaHD,TenDo_Uong,a.DonGia,SoLuong,ThanhTien FROM HoaDon a LEFT JOIN DoUong b ON a.MaDo_Uong = b.MaDo_Uong WHERE a.MaBan = '" + MaBan + "' AND TrangThai = '0'";
return(KetNoiSQL.GetData(strSQL));
}
public void Them(DTO_DoUong MaDo_Uong)
{
string strSQL = "INSERT INTO DoUong(MaDo_Uong,TenDo_Uong,DonGia,SoLuongTon,GhiChu) VALUES ( '" + MaDo_Uong.MaDo_Uong + "',N'" + MaDo_Uong.TenDo_Uong + "'," + MaDo_Uong.DonGia + "," + MaDo_Uong.SoLuongTon + ",N'" + MaDo_Uong.GhiChu + "')";
KetNoiSQL.ExecuteNonQuery(strSQL);
}
public DataTable DangNhapChuongTrinh(string MaTK_NV, string MatKhau, string Quyen)
{
string strSQL = "Select * From TaiKhoan_NhanVien Where MaTK_NV = '" + MaTK_NV + "' And MatKhau = '" + MatKhau + "' And Quyen = '" + Quyen + "'";
return(KetNoiSQL.GetData(strSQL));
}
public DataTable DanhSachDoUongTheoGia(string MaDo_Uong)
{
return(KetNoiSQL.GetData(string.Format("select * FROM DoUong WHERE MaDo_Uong = '" + MaDo_Uong + "'")));
}
public void Xoa(DTO_DoUong MaDo_Uong)
{
string strSQL = "DELETE DoUong where MaDo_Uong = '" + MaDo_Uong.MaDo_Uong + "'";
KetNoiSQL.ExecuteNonQuery(strSQL);
}
public void Xoa(DTO_TaiKhoan_NhanVien MaTK_NV)
{
string strSQL = "DELETE TaiKhoan_NhanVien where MaTK_NV = '" + MaTK_NV.MaTK_NV + "'";
KetNoiSQL.ExecuteNonQuery(strSQL);
}
public void Xoa(DTO_HoaDon HoaDon)
{
string strSQL = "DELETE HoaDon WHERE MAHD = '" + HoaDon.MaHD + "' ";
KetNoiSQL.ExecuteNonQuery(strSQL);
}
public DataTable LayMatKhau(string MaTK_NV)
{
return(KetNoiSQL.GetData(string.Format("select * FROM TaiKhoan_NhanVien WHERE MaTK_NV = '" + MaTK_NV + "'")));
}
public DataTable DanhSachTimKiem(string MaTK_NV, string TenTK_NV, string SoDienThoai, string DiaChi)
{
return(KetNoiSQL.GetData(string.Format("select * FROM TaiKhoan_NhanVien WHERE MaTK_NV LIKE N'%" + MaTK_NV + "%' AND TenTK_NV LIKE N'%" + TenTK_NV + "%' AND SoDienThoai LIKE N'%" + SoDienThoai + "%' AND DiaChi LIKE N'%" + DiaChi + "%'")));
}
public DataTable ThongTinTaiKhoanCaNhan(String MaTK_NV)
{
return(KetNoiSQL.GetData(string.Format("select MaTK_NV,TenTK_NV,SoDienThoai,DiaChi,Quyen FROM TaiKhoan_NhanVien WHERE MaTK_NV = '" + MaTK_NV + "'")));
}
public DataTable DanhSachTaiKhoan_NhanVien()
{
return(KetNoiSQL.GetData(string.Format("select MaTK_NV,TenTK_NV,SoDienThoai,DiaChi,Quyen FROM TaiKhoan_NhanVien")));
}
public DataTable DanhSachQuyen()
{
return(KetNoiSQL.GetData(string.Format("select * FROM Quyen")));
}
public void Sua(DTO_Ban MaBan)
{
string strSQL = string.Format(@"UPDATE Ban set MaBan = '" + MaBan.MaBan + "', TenBan = N'" + MaBan.TenBan + "',SoLuongNguoi = " + MaBan.SoLuongNguoi + ",GhiChu = N'" + MaBan.GhiChu + "' where MaBan = '" + MaBan.MaBan_Sua + "'");
KetNoiSQL.ExecuteNonQuery(strSQL);
}
public void Sua(DTO_TaiKhoan_NhanVien TK_NV)
{
string strSQL = string.Format(@"UPDATE TaiKhoan_NhanVien set MaTK_NV = '" + TK_NV.MaTK_NV + "', TenTK_NV = N'" + TK_NV.TenTK_NV + "',MatKhau = '" + TK_NV.MatKhau + "',SoDienThoai = '" + TK_NV.SoDienThoai + "',DiaChi = N'" + TK_NV.DiaChi + "',Quyen = '" + TK_NV.Quyen + "' where MaTK_NV = '" + TK_NV.MaTK_NV_Sua + "'");
KetNoiSQL.ExecuteNonQuery(strSQL);
}
public void Sua(DTO_HoaDon HoaDon)
{
string strSQL = string.Format(@"UPDATE HoaDon set MaDo_Uong = '" + HoaDon.MaDo_Uong + "',DonGia = " + HoaDon.DonGia + ",SoLuong = " + HoaDon.SoLuong + " where MaHD = '" + HoaDon.MaHD + "'");
KetNoiSQL.ExecuteNonQuery(strSQL);
}
public void Them(DTO_TaiKhoan_NhanVien TK_NV)
{
string strSQL = "INSERT INTO TaiKhoan_NhanVien(MaTK_NV,TenTK_NV,MatKhau,SoDienThoai,DiaChi,Quyen) VALUES ( '" + TK_NV.MaTK_NV + "',N'" + TK_NV.TenTK_NV + "','" + TK_NV.MatKhau + "',N'" + TK_NV.SoDienThoai + "',N'" + TK_NV.DiaChi + "','" + TK_NV.Quyen + "')";
KetNoiSQL.ExecuteNonQuery(strSQL);
}
public DataTable DanhSachDoUong()
{
return(KetNoiSQL.GetData(string.Format("select * FROM DoUong")));
}
public void Xoa(DTO_Ban MaBan)
{
string strSQL = "DELETE Ban where MaBan = '" + MaBan.MaBan + "'";
KetNoiSQL.ExecuteNonQuery(strSQL);
}
public DataTable DanhSachTimKiem(string MaDo_Uong, string TenDo_Uong, decimal DonGia, decimal SoLuongTon, string GhiChu)
{
string strSQL = string.Format("select * FROM DoUong WHERE MaDo_Uong LIKE N'%" + MaDo_Uong + "%' AND TenDo_Uong LIKE N'%" + TenDo_Uong + "%' AND DonGia LIKE '%" + DonGia + "%' AND SoLuongTon LIKE '%" + SoLuongTon + "%' AND GhiChu LIKE N'%" + GhiChu + "%'");
return(KetNoiSQL.GetData(strSQL));
}
public void Them(DTO_Ban MaBan)
{
string strSQL = "INSERT INTO Ban(MaBan,TenBan,SoLuongNguoi,GhiChu) VALUES ( '" + MaBan.MaBan + "',N'" + MaBan.TenBan + "'," + MaBan.SoLuongNguoi + ",N'" + MaBan.GhiChu + "')";
KetNoiSQL.ExecuteNonQuery(strSQL);
}
public void Sua(DTO_DoUong MaDo_Uong)
{
string strSQL = string.Format(@"UPDATE DoUong set MaDo_Uong = '" + MaDo_Uong.MaDo_Uong + "', TenDo_Uong = N'" + MaDo_Uong.TenDo_Uong + "',DonGia = " + MaDo_Uong.DonGia + ",SoLuongTon = " + MaDo_Uong.SoLuongTon + ",GhiChu = N'" + MaDo_Uong.GhiChu + "' where MaDo_Uong = '" + MaDo_Uong.MaDo_Uong_Sua + "'");
KetNoiSQL.ExecuteNonQuery(strSQL);
}
public DataTable Table_Yes()
{
string strSQL = "SELECT DISTINCT a.MaBan,TenBan FROM HoaDon a INNER JOIN Ban b ON a.MaBan = b.MaBan WHERE TrangThai = '0'";
return(KetNoiSQL.GetData(strSQL));
}
public DataTable DanhSachTimKiem(string MaBan, string TenBan, decimal SoLuongNguoi, string GhiChu)
{
string strSQL = string.Format("select * FROM Ban WHERE MaBan LIKE N'%" + MaBan + "%' AND TenBan LIKE N'%" + TenBan + "%' AND SoLuongNguoi LIKE '%" + SoLuongNguoi + "%' AND GhiChu LIKE N'%" + GhiChu + "%'");
return(KetNoiSQL.GetData(strSQL));
}
public DataTable ThongKeDoanhThu(DateTime ngay1, DateTime ngay2)
{
string strSQL = "SELECT SUM(ThanhTien) AS ThanhTien,NgayHoaDon,TenBan FROM HoaDon a LEFT JOIN Ban b ON a.MaBan = b.MaBan WHERE CONVERT(DATETIME, NgayHoaDon,103) BETWEEN CONVERT(DATETIME, '" + ngay1 + "',103) AND CONVERT(DATETIME, '" + ngay2 + "',103) AND TrangThai = '1' GROUP BY NgayHoaDon,TenBan ";
return(KetNoiSQL.GetData(strSQL));
}
