public DataSet timKiemTieuDe(string search)
{
return(db.ExecuteQueryDataSet("select MaTieuDe,TieuDe,NhomTacGia,TheLoaiNghienCuu,QuanLy " +
"from NoiDungChinh, NhomTacGia, TheLoaiNghienCuu, QuanLy " + "where NhomTacGia.MaNhom=NoiDungChinh.NhomTacGia " +
"AND TheLoaiNghienCuu.MaTheLoai=NoiDungChinh.TheLoaiNghienCuu AND QuanLy.idQuanLy=NoiDungChinh.QuanLy AND " +
" (MaTieuDe like '%" + search + "%'" + " OR" +
" TieuDe like '%" + search + "%'" + " OR NhomTacGia like '%" + search + "%'" + " OR TheLoaiNghienCuu like '%" + search + "%')", CommandType.Text));
}
public DataSet LaySinhVien(string Khoa, string ChuyenNganh, string Lop)
{
string sqlString = "Select SinhVien.MSSV, SinhVien.HoVaTen, SinhVien.QueQuan, CONVERT( VARCHAR, SinhVien.NgaySinh, 105 ), SinhVien.CMND, SinhVien.GioiTinh, SinhVien.DiaChi, SinhVien.NienKhoa, SinhVien.MaLop, Lop.TenLop, ChuyenNganh.TenChuyenNganh, Khoa.TenKhoa " +
" From SinhVien,Lop,ChuyenNganh,Khoa" +
" Where SinhVien.MaLop = Lop.MaLop And Lop.MaChuyenNganh = ChuyenNganh.MaChuyenNganh And ChuyenNganh.MaKhoa = Khoa.MaKhoa And Khoa.TenKhoa Like N'%" + Khoa +
"%' And ChuyenNganh.TenChuyenNganh Like N'%" + ChuyenNganh + "%' And Lop.TenLop Like N'%" + Lop + "%'";
return(dbMain.ExecuteQueryDataSet(sqlString, CommandType.Text));
}
public DataSet LayTTBill()
{
SqlCommand sqlCmd = new SqlCommand(@"EXEC sp_Load_Bill", db.conn)
{
CommandType = CommandType.Text
};
return(db.ExecuteQueryDataSet(sqlCmd));
}
public List <Ban> DanhsachBan()
{
List <Ban> lBanAn = new List <Ban>();
DataSet dt = db.ExecuteQueryDataSet("select * from BanAn", CommandType.Text);
foreach (DataRow dr in dt.Tables[0].Rows)
{
Ban b = new Ban(dr);
lBanAn.Add(b);
}
return(lBanAn);
}
public List <BAN> LayBan()
{
/* (EntityModels)
* var kq = from tt in qlCF.BANs
* where tt.TrangThaiXoa == false
* select new { tt.IDBan, tt.TenBan, tt.TrangThai, tt.SucChua };
*/
List <BAN> lstBan = new List <BAN>();
DBMain db = new DBMain();
string query = "Select IDBan, TenBan, TrangThai, SucChua " +
"From BAN " +
"Where TrangThaiXoa = 0";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
foreach (DataRow item in kq.Rows)
{
BAN ban = new BAN();
ban.IDBan = (int)item["IDBan"];
ban.TenBan = item["TenBan"].ToString();
ban.TrangThai = (bool)item["TrangThai"];
ban.SucChua = (int)item["SucChua"];
lstBan.Add(ban);
}
return(lstBan);
}
public DataTable LayChiTietHoaDon(string IDHoaDon)
{
/* (EntityModels)
* var queryCTHoaDon = from cthd in qlCF.CHITIETHOADONs
* join ma in qlCF.MONANs on cthd.IDMonAn equals ma.IDMonAn
* where cthd.IDHoaDon == IDHoaDon
* select new { cthd.IDHoaDon, ma.TenMon, ma.GiaTien, cthd.SoLuong, ThanhTien = ma.GiaTien * cthd.SoLuong };
*/
DBMain db = new DBMain();
string query = "Select CHITIETHOADON.IDHoaDon, TenMon, GiaTien, SoLuong, (GiaTien * SoLuong) as ThanhTien " +
"From CHITIETHOADON join MONAN on CHITIETHOADON.IDMonAN = MONAN.IDMonAn " +
"Where CHITIETHOADON.IDHoaDon = '" + IDHoaDon + "'";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]
{
new DataColumn("IDHoaDon"),
new DataColumn("TenMon"),
new DataColumn("GiaTien"),
new DataColumn("SoLuong"),
new DataColumn("ThanhTien"),
});;
foreach (DataRow i in kq.Rows)
{
dt.Rows.Add(i["IDHoaDon"].ToString(), i["TenMon"].ToString(), (int)i["GiaTien"], (int)i["SoLuong"], (int)i["ThanhTien"]);
}
return(dt);
}
public bool DoiMatKhau(string oldPass, string newPass, string idNhanVien)
{
//QuanLyNhaHangProjectEntities ql = new QuanLyNhaHangProjectEntities();
//var query = ql.TAIKHOANs.Where(x => x.IDNhanVien == idNhanVien).SingleOrDefault();
//if(query != null)
//{
// if (query.MatKhau == oldPass)
// {
// query.MatKhau = newPass;
// ql.SaveChanges();
// }
// else return false;
//}
DBMain db = new DBMain();
string f = "";
string query = "Select * from TAIKHOAN Where IDNhanVien = '" + idNhanVien + "' And MatKhau = '" + oldPass + "'";
if (db.ExecuteQueryDataSet(query, CommandType.Text).Rows.Count >= 1)
{
query = "Update TAIKHOAN Set MatKhau = '" + newPass + "' Where IDNhanVien = '" + idNhanVien + "' And MatKhau = '" + oldPass + "'";
return(db.MyExecuteNonQuery(query, CommandType.Text, ref f));
}
else
{
return(false);
}
}
public string LayCongViecById(int id)
{
DBMain db = new DBMain();
string query = "select TenCongViec from CONGVIEC where IDCongViec = '" + id + "'";
return(db.ExecuteQueryDataSet(query, CommandType.Text).Rows[0]["TenCongViec"].ToString());
}
public DataSet SearchCutomer(string CustomerName, string IdentityCard)
{
DBMain db = new DBMain();
string sql = "Exec sp_SearchCustomer ";
if (CustomerName != null)
{
sql += " " + CustomerName;
if (IdentityCard != null)
{
sql += "," + IdentityCard;
}
}
else
{
if (IdentityCard != null && IdentityCard != "")
{
sql += "null," + IdentityCard;
}
else
{
sql = "select * from Customers";
}
}
return(db.ExecuteQueryDataSet(sql, CommandType.Text));
}
public int LayIDHoaDonTheoBan(int idBan)
{
DataTable dt = db.ExecuteQueryDataSet($"select * from HoaDon where IDBanAn = '{idBan}' and HoaDon.TinhTrang = 0", CommandType.Text).Tables[0];
int id = -1; //ID Hóa đơn mặc định không tìm thấy
//Kiểm tra xem dt có dữ liệu hay không ?
if (dt.Rows.Count > 0)
{
id = (int)dt.Rows[0]["IDHoaDon"];
}
return(id);
//try
//{
// QuanLyCaPheEntities qlcp = new QuanLyCaPheEntities();
// var hd = (from b in qlcp.HoaDons
// where b.IDBanAn == idBan
// && b.TinhTrang == false
// select b).SingleOrDefault();
// if (hd != null)
// {
// return hd.IDHoaDon;
// }
// else return -1;
//}
//catch
//{
// return -1;
//}
}
public DataSet ListOfInvoicesHasPaid()
{
DBMain db = new DBMain();
string sql = "Select * from vi_InvoicesHasPaid";
return(db.ExecuteQueryDataSet(sql, CommandType.Text));
}
public NHANVIEN LayNhanVienByUserName(string username)
{
/*
* var query = ql.TAIKHOANs.Where(x => x.TaiKhoan1 == username)
* .Join(ql.NHANVIENs, x => x.IDNhanVien, y => y.IDNhanVien, (x, y) => y)
* .SingleOrDefault();
*/
DBMain db = new DBMain();
string query = "Select NHANVIEN.IDNhanVien, Ho, Ten, DiaChi, NgaySinh, Email, SDT, IDCongViec, HinhNV " +
"From NHANVIEN join TAIKHOAN on NHANVIEN.IDNhanVien = TAIKHOAN.IDNhanVien " +
"Where TAIKHOAN.TaiKhoan = '" + username + "'";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
NHANVIEN nv = new NHANVIEN();
if (kq != null)
{
nv.IDNhanVien = kq.Rows[0]["IDNhanVien"].ToString();
nv.Ho = kq.Rows[0]["Ho"].ToString();
nv.Ten = kq.Rows[0]["Ten"].ToString();
nv.DiaChi = kq.Rows[0]["DiaChi"].ToString();
nv.NgaySinh = (DateTime)kq.Rows[0]["NgaySinh"];
nv.Email = kq.Rows[0]["Email"].ToString();
nv.SDT = kq.Rows[0]["SDT"].ToString();
nv.IDCongViec = (int)kq.Rows[0]["IDCongViec"];
nv.HinhNV = kq.Rows[0]["HinhNV"].ToString();
}
return(nv);
}
/// <summary>
/// Lấy danh sách danh thu
/// </summary>
/// <param name="idBan"></param>
public DataSet LayDanhThu(DateTime ngayTaoHoaDon, DateTime ngayKetThucHoaDon)
{
string strSQL = $"select IDHoaDon, TenBan, NgayTaoHoaDon, NgayKetThucHoaDon, GiamGia,TongTien from HoaDon join BanAn on HoaDon.IDBanAn = BanAn.IDBanAn where HoaDon.TinhTrang = 1 and cast(NgayTaoHo" +
$"aDon as date) >= '{ngayTaoHoaDon.Year}-{ngayTaoHoaDon.Month}-{ngayTaoHoaDon.Day}' and cast(NgayKetthucHoaDon as Date) <= '{ngayKetThucHoaDon.Year}-{ngayKetThucHoaDon.Month}-{ngayKetThucHoaDon.Day} '";
return(db.ExecuteQueryDataSet(strSQL, CommandType.Text));
}
public DataSet layHS(string MaNV, ref string err)
{
return(db.ExecuteQueryDataSet("SELECT NhanVien.MaNV, NhanVien.HoVaTen, NhanVien.NgaySinh, NhanVien.DiaChi, NhanVien.GioiTinh, PhongBan.TenPhong, Luong.LuongCoBan, Luong.LuongTHuong " +
"FROM NhanVien inner join PhongBan on NhanVien.Phong = PhongBan.MaPhong inner join Luong on NhanVien.MaNV = Luong.MaNV" +
" WHERE NhanVien.MaNV = '" + MaNV +
"' ", CommandType.Text));
}
public List <MONAN> LayDanhMucMonAn(int tab)
{
/* (EntityModels)
* var queryMA = from nu in ql.MONANs
* where nu.IDDanhMuc == tab && nu.TrangThai == false
* select nu;
*/
DBMain db = new DBMain();
List <MONAN> lstNuoc = new List <MONAN>();
string query = "Select * From MONAN Where IDDanhMuc = '"
+ tab + "' And TrangThai = 0";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
foreach (DataRow item in kq.Rows)
{
MONAN nu = new MONAN();
nu.IDMonAn = item["IDMonAn"].ToString();
nu.IDDanhMuc = (int)item["IDDanhMuc"];
nu.GiaTien = (int)item["GiaTien"];
nu.TenMon = item["TenMon"].ToString();
nu.HinhMA = item["HinhMA"].ToString();
lstNuoc.Add(nu);
}
return(lstNuoc);
}
public HOADON LayHoaDonID(string ID) //Không sử dụng
{
/* (EntityModels)
* var hoadon = (ql.HOADONs.Where(x => x.IDHoaDon == ID).Select(x => x)).SingleOrDefault();
*/
DBMain db = new DBMain();
string query = "Select * " +
"From HOADON " +
"Where IDHoaDon = '" + ID + "'";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
HOADON hd = new HOADON();
if (query != null)
{
hd.IDHoaDon = kq.Rows[0]["IDHoaDon"].ToString();
hd.IDBan = (int)kq.Rows[0]["IDBan"];
hd.IDNhanVien = kq.Rows[0]["IDNhanVien"].ToString();
hd.KhuyenMai = kq.Rows[0]["KhuyenMai"].ToString();
hd.NgayLap = (DateTime)kq.Rows[0]["NgayLap"];
if (kq.Rows[0]["NgayThanhToan"].ToString() != "")
{
hd.NgayThanhToan = (DateTime)kq.Rows[0]["NgayThanhToan"];
}
else
{
hd.NgayThanhToan = null;
}
hd.TongTien = (int)kq.Rows[0]["TongTien"];
}
return(hd);
}
public DataSet Chart()
{
DBMain db = new DBMain();
string sql = "Exec sp_Revenue5Month ";
return(db.ExecuteQueryDataSet(sql, CommandType.Text));
}
public DataSet LayTTKH(string SDT, ref string error)
{
SqlCommand sqlCmd = new SqlCommand(@"select * from fn_Customer_DisCount(N'" + SDT + "') as Customer_DisCount", db.conn)
{
CommandType = CommandType.Text
};
return(db.ExecuteQueryDataSet(sqlCmd));
}
public DataSet LayTTCategory()
{
SqlCommand sqlCmd = new SqlCommand(@"EXEC Load_Category", db.conn)
{
CommandType = CommandType.Text
};
return(db.ExecuteQueryDataSet(sqlCmd));
}
public DataSet LayNguoiThan(string MSSV, string TenSV, string Khoa, string ChuyenNganh, string Lop)//Co them TenSV,TenKhoa,TenChuyenNganh,TenLop de thuan tien tim kiem
{
string sqlString = "select SinhVien.MSSV,SinhVien.HoVaTen,ThanNhan.HoVaTen,ThanNhan.DiaChi,ThanNhan.SDT,ThanNhan.QuanHe,Khoa.TenKhoa,ChuyenNganh.TenChuyenNganh,Lop.TenLop" +
" from ThanNhan,SinhVien,Lop,ChuyenNganh,Khoa Where ThanNhan.MSSV=SinhVien.MSSV And SinhVien.MaLop=Lop.MaLop" +
" And Lop.MaChuyenNganh=ChuyenNganh.MaChuyenNganh And ChuyenNganh.MaKhoa = Khoa.MaKhoa" +
" And SinhVien.MSSV Like '%" + MSSV + "%' And SinhVien.HoVaTen Like N'%" + TenSV + "%' And Khoa.TenKhoa Like N'%" + Khoa + "%' And ChuyenNganh.TenChuyenNganh Like N'%" + ChuyenNganh + "%' And Lop.TenLop Like N'%" + Lop + "%'";
return(dbMain.ExecuteQueryDataSet(sqlString, CommandType.Text));
}
public DataSet LayDrinks(string CatagoryID)
{
SqlCommand sqlCmd = new SqlCommand(@"EXEC sp_Load_Drink N'" + CatagoryID + "'", db.conn)
{
CommandType = CommandType.Text
};
return(db.ExecuteQueryDataSet(sqlCmd));
}
int LayIdMonNuocMoiNhat()
{ //Entity
//QuanLyNhaHangProjectEntities ql = new QuanLyNhaHangProjectEntities();
//var m = ql.MONANs.OrderByDescending(x => x.IDMonAn).FirstOrDefault();
//return int.Parse(m.IDMonAn.Split('U')[1]);
DBMain db = new DBMain();
string query = "Select top(1) IDMonAn From MONAN Order By IDMonAn Desc";
string kq = db.ExecuteQueryDataSet(query, CommandType.Text).Rows[0].Field <String>("IDMonAn");
return(int.Parse(kq.Split('A')[1]));
}
public string LayIDNhanVien(string taikhoan)
{
//QuanLyNhaHangProjectEntities qlCF = new QuanLyNhaHangProjectEntities();
//TAIKHOAN tk = (from TK in qlCF.TAIKHOANs
// where TK.TaiKhoan1 == taikhoan
// select TK).SingleOrDefault();
//if (tk != null)
// return tk.IDNhanVien;
DBMain db = new DBMain();
string query = "Select IDNhanVien From TAIKHOAN Where TaiKhoan = '" + taikhoan + "'";
return(db.ExecuteQueryDataSet(query, CommandType.Text).Rows[0][0].ToString());
}
public string LayIDMonNuoc(string TenMon)
{
/*
* string id = (from nu in ql.MONANs
* where nu.TenMon == TenMon
* select nu.IDMonAn).SingleOrDefault();
*/
DBMain db = new DBMain();
string query = "Select IDMonAn " +
"From MONAN " +
"Where TenMon = N'" + TenMon + "'";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
return(kq.Rows[0]["IDMonAn"].ToString());
}
public int LayDonGia(string IDMonAn)
{
/*
* int gia = (from nu in ql.MONANs
* where nu.IDMonAn == IDMonAn
* select nu.GiaTien).SingleOrDefault();
*/
DBMain db = new DBMain();
string query = "Select GiaTien " +
"From MONAN " +
"Where IDMonAn = '" + IDMonAn + "'";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
return((int)kq.Rows[0]["GiaTien"]);
}
public bool TrangThai(int IDBan)
{
/* (EntityModels)
* BAN kq = (from tt in qlCF.BANs
* where tt.IDBan == IDBan
* select tt).SingleOrDefault();
*/
DBMain db = new DBMain();
string query = "Select TrangThai " +
"From BAN " +
"Where IDBan = '" + IDBan + "'";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
return((bool)kq.Rows[0]["TrangThai"]);
}
// //2 hàm trên hỗ trợ việc đưa thời gian vào 2 datetimepicker khi mới mở form
// public bool CheckExist()
// {
// QuanLyNhaHangProjectEntities ql = new QuanLyNhaHangProjectEntities();
// if (ql.HOADONs.Count() > 0)
// return true;
// return false;
// }
public DateTime layNgayLap(string ID)
{
// DateTime result = ql.HOADONs.Where(x => x.IDHoaDon == ID).Select(x => x.NgayLap).FirstOrDefault();
// if (result != default)
// {
// return result;
// }
// return DateTime.Now;
DBMain db = new DBMain();
string query = "Select NgayLap From HOADON Where IDHoaDon = '" + ID + "'";
string rs = db.ExecuteQueryDataSet(query, CommandType.Text).Rows[0]["NgayLap"].ToString();
return(DateTime.Now);
}
public DataTable LayChiTietHoaDonBan(int IDBan)
{
/* (EntityModels)
* var IDHoaDon = qlCF.HOADONs
* .Where(x => x.IDBan == IDBan)
* .Where(x => x.NgayLap < DateTime.Now)
* .Where(x => x.NgayThanhToan == null)
* .Select(x => x.IDHoaDon).SingleOrDefault();
*/
DBMain db = new DBMain();
string query = "Select IDHoaDon " +
"From HOADON " +
"Where NgayLap < '" + DateTime.Now + "' And IDBan = '" + IDBan + "' And NgayThanhToan is null";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
string IDHoaDon = kq.Rows[0]["IDHoaDon"].ToString();
return(BLChiTietHoaDon.Instance.LayChiTietHoaDon(IDHoaDon));
}
public DataSet LayDiem(string tenMon, string maMon, string HoVaTen, string MSSV, string Khoa, string ChuyenNganh, string Lop)
{
//Thay đổi
string sqlString = "select SinhVien.MSSV, SinhVien.HoVaTen, Khoa.TenKhoa, ChuyenNganh.TenChuyenNganh, Lop.TenLop, "
+ "BangDiem.MaMon, BangDiem.TenMon, BangDiem.SoTinChi, BangDiem.TenKhoa, BangDiem.DiemTB, BangDiem.TrangThai "
+ "from SinhVien inner join Lop on SinhVien.MaLop = Lop.MaLop "
+ "inner join ChuyenNganh on ChuyenNganh.MaChuyenNganh = Lop.MaChuyenNganh "
+ "inner join Khoa on Khoa.MaKhoa = ChuyenNganh.MaKhoa "
+ "inner join (select SinhVien.MSSV, MonHoc.MaMon, MonHoc.SoTinChi, MonHoc.TenMon, Khoa.TenKhoa, Diem.DiemTB, Diem.TrangThai "
+ "from MonHoc inner join Khoa on MonHoc.MaKhoa = Khoa.MaKhoa "
+ "inner join Diem on Diem.MaMon = MonHoc.MaMon "
+ "inner join SinhVien on SinhVien.MSSV = Diem.MSSV) as BangDiem "
+ "on bangdiem.MSSV = SinhVien.MSSV "
+ "Where SinhVien.MSSV Like '%" + MSSV + "%' And SinhVien.HoVaTen Like N'%" + HoVaTen
+ "%' And BangDiem.MaMon Like '%" + maMon + "%' And BangDiem.TenMon Like N'%" + tenMon
+ "%' And Khoa.TenKhoa Like N'%" + Khoa + "%' And ChuyenNganh.TenChuyenNganh Like N'%" + ChuyenNganh + "%' And Lop.TenLop Like N'%" + Lop + "%'";
return(dbMain.ExecuteQueryDataSet(sqlString, CommandType.Text));
}
// //Hàm lấy thời gian lập của bill gần nhất
public DateTime TimeOfLastBill()
{
// DateTime result = ql.HOADONs.OrderByDescending(x => x.NgayLap).Select(x=>x.NgayLap).FirstOrDefault();
// if (result != default)
// {
// return result;
// }
// return DateTime.Now;
DBMain db = new DBMain();
string query = "Select top(1) NgayLap From HOADON Order By NgayLap Desc";
var kq = db.ExecuteQueryDataSet(query, CommandType.Text);
if (kq.Rows.Count > 0)
{
return(((DateTime)kq.Rows[0]["NgayLap"]).AddDays(1).Date);
}
return(DateTime.Now.Date);
}