public DataTable LayHoaDonTheoTimKiem(KieuTimKiemHoaDon kieuTimKiem, string chuoiCanTim)
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
DataTable dt = new DataTable();
SetTableColumn(dt);
if (kieuTimKiem == KieuTimKiemHoaDon.THEO_KH)
{
var sps = qlSTEntity.func_HoaDon_TimTheoTenKhachHang(chuoiCanTim);
foreach (var p in sps)
{
dt.Rows.Add(p.MaHD, p.TenKH, p.TenNV, p.NgayLapHD, p.NgayNhanHang);
}
}
else
{
var sps = qlSTEntity.func_HoaDon_TimTheoTenNhanVien(chuoiCanTim);
foreach (var p in sps)
{
dt.Rows.Add(p.MaHD, p.TenKH, p.TenNV, p.NgayLapHD, p.NgayNhanHang);
}
}
return(dt);
}
public string SinhMaPBMoi(string MaCuoi)
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
string mamoi = qlSTEntity.Database.SqlQuery <string>("SELECT dbo.func_PhongBan_SinhMa()").Single().ToString().Trim();
return(mamoi);
}
public DataTable LayKhachHangTheoLoc(bool isLonHon, string Tuoi)
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
DataTable dt = new DataTable();
SetTableColumn(dt);
int tuoi = 0;
int.TryParse(Tuoi, out tuoi);
if (isLonHon == true)
{
var sps = qlSTEntity.func_KhachHang_LocTuoiLonHon(tuoi);
foreach (var p in sps)
{
dt.Rows.Add(p.MaKH, p.TenKH, p.DienThoai, p.NgaySinh, p.DiaChi);
}
}
else
{
var sps2 = qlSTEntity.func_KhachHang_LocTuoiNhoHon(tuoi);
foreach (var p in sps2)
{
dt.Rows.Add(p.MaKH, p.TenKH, p.DienThoai, p.NgaySinh, p.DiaChi);
}
}
return(dt);
}
public DataTable LayNhanVienTheoTimKiem(KieuTimKiemNhanVien kieuTimKiem, string chuoiCanTim)
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
DataTable dt = new DataTable();
SetTableColumn(dt);
try
{
System.Data.Entity.Infrastructure.DbSqlQuery <Quan_Ly_Kinh_Doanh.NHANVIEN> sps;
if (kieuTimKiem == KieuTimKiemNhanVien.THEO_TEN)
{
sps = qlSTEntity.NHANVIENs.SqlQuery("Select * from func_NhanVien_TimTheoTen('" + chuoiCanTim + "')");
}
else
{
sps = qlSTEntity.NHANVIENs.SqlQuery("Select * from func_NhanVien_TimTheoID('" + chuoiCanTim + "')");
}
foreach (var p in sps)
{
dt.Rows.Add(p.MaNV, p.Ho, p.TenLot, p.Ten,
p.NgaySinh, p.GioiTinh, p.Phong, p.LuongCB, p.DienThoai);
}
} catch (Exception e) { }
return(dt);
}
public DataTable LayPhongBanTheoTimKiem(KieuTimKiemPhongBan kieuTimKiem, string chuoiCanTim)
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
DataTable dt = new DataTable();
SetTableColumn(dt);
if (kieuTimKiem == KieuTimKiemPhongBan.THEO_TEN_PHONG)
{
var sps = qlSTEntity.func_PhongBan_TimTheoTenPhong(chuoiCanTim);
foreach (var p in sps)
{
dt.Rows.Add(p.MaPB, p.TenPhong, p.TenTruongPhong);
}
}
else
{
var sps2 = qlSTEntity.func_PhongBan_TimTheoTenTruongPhong(chuoiCanTim);
foreach (var p in sps2)
{
dt.Rows.Add(p.MaPB, p.TenPhong, p.TenTruongPhong);
}
}
return(dt);
}
//Kiểm tra kết ConnectionString
public bool CheckConnection()
{
string DtaSrc = Properties.Settings.Default.DataSource;
String DtaBs = Properties.Settings.Default.Database;
if (DtaSrc.IndexOf("192.168.") == 0)
{
if (!CheckPing(DtaSrc))
{
MessageBox.Show("Không tìm thấy server!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
using (QuanLySieuThiEntities dbContext = new QuanLySieuThiEntities(BuildConnectionString(DtaSrc, DtaBs)))
{
if (dbContext.Database.Exists())
{
MessageBox.Show("Kết nối thành công!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(true);
}
else
{
MessageBox.Show("Bạn không có quyền truy cập!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(false);
}
}
public DataTable LaySanPhamTheoTimKiem(KieuTimKiemSanPham kieuTimKiem, bool isLonHon, string chuoiCanTim)
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
DbSqlQuery <Quan_Ly_Kinh_Doanh.SANPHAM> sps;
string query = "";
if (kieuTimKiem == KieuTimKiemSanPham.THEO_TEN)
{
query = string.Format("SELECT * FROM dbo.func_SanPham_TimTheoTen(N'{0}')", chuoiCanTim);
}
else
{
if (isLonHon == true)
{
query = string.Format("SELECT * FROM dbo.func_SanPham_TimGiaLonHon(N'{0}')", chuoiCanTim);
}
else
{
query = string.Format("SELECT * FROM dbo.func_SanPham_TimGiaNhoHon(N'{0}')", chuoiCanTim);
}
}
sps = qlSTEntity.SANPHAMs.SqlQuery(query);
DataTable dt = new DataTable();
SetTableColumn(dt);
foreach (var p in sps)
{
dt.Rows.Add(p.MaSP, p.TenSP, p.DonViTinh, p.Gia);
}
return(dt);
}
string LayMaKH(string TenKH)
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
var sps = (from p in qlSTEntity.KHACHHANGs
where p.TenKH == TenKH
select p).SingleOrDefault();
return(sps.MaKH);
}
string LayMaSP(string TenSP)
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
var sps = (from p in qlSTEntity.SANPHAMs
where p.TenSP.Trim() == TenSP.Trim()
select p).SingleOrDefault();
return(sps.MaSP);
}
string LayMaPhong(string TenPhong)
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
var sps = (from p in qlSTEntity.PHONGBANs
where p.TenPhong == TenPhong
select p).SingleOrDefault();
return(sps.MaPB);
}
string LayMaNV(string TenNV)
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
var sps = (from p in qlSTEntity.NHANVIENs
where (p.Ho.Trim() + " " + p.TenLot.Trim() + " " + p.Ten.Trim()) == TenNV.Trim()
select p).SingleOrDefault();
return(sps.MaNV);
}
public DataTable LayNhanVienTheoLoc(KieuLocNhanVien kieuLoc, bool isLonHon, string Tuoi)
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
DbSqlQuery <Quan_Ly_Kinh_Doanh.NHANVIEN> sps;
DataTable dt = new DataTable();
SetTableColumn(dt);
if (kieuLoc == KieuLocNhanVien.THEO_NAM)
{
sps = qlSTEntity.NHANVIENs.SqlQuery("SELECT * FROM dbo.view_NhanVien_Nam");
foreach (var p in sps)
{
dt.Rows.Add(p.MaNV, p.Ho, p.TenLot, p.Ten,
p.NgaySinh, p.GioiTinh, p.Phong, p.LuongCB, p.DienThoai);
}
}
else if (kieuLoc == KieuLocNhanVien.THEO_NU)
{
sps = qlSTEntity.NHANVIENs.SqlQuery("SELECT * FROM dbo.view_NhanVien_Nu");
foreach (var p in sps)
{
dt.Rows.Add(p.MaNV, p.Ho, p.TenLot, p.Ten,
p.NgaySinh, p.GioiTinh, p.Phong, p.LuongCB, p.DienThoai);
}
}
else
{
int tuoi = 0;
int.TryParse(Tuoi, out tuoi);
if (isLonHon == true)
{
var sps2 = qlSTEntity.func_NhanVien_LocTuoiLonHon(tuoi);
foreach (var p in sps2)
{
dt.Rows.Add(p.MaNV, p.Ho, p.TenLot, p.Ten,
p.NgaySinh, p.GioiTinh, p.Phong, p.LuongCB, p.DienThoai);
}
}
else
{
var sps3 = qlSTEntity.func_NhanVien_LocTuoiNhoHon(tuoi);
foreach (var p in sps3)
{
dt.Rows.Add(p.MaNV, p.Ho, p.TenLot, p.Ten,
p.NgaySinh, p.GioiTinh, p.Phong, p.LuongCB, p.DienThoai);
}
}
}
return(dt);
}
public bool CapNhatChiTietHoaDon(string MaHD, string TenSP, string SoLuong, ref string err)
{
try
{
QuanLySieuThiEntities qlKDEntity = new QuanLySieuThiEntities(_conString);
string query = string.Format("EXEC dbo.usp_ChiTietHoaDon_Sua N'{0}', N'{1}', '{2}'", MaHD, LayMaSP(TenSP), SoLuong);
qlKDEntity.Database.ExecuteSqlCommand(query);
return(true);
} catch (Exception e) { }
return(false);
}
public Image LayHinhAnhSanPham(string MaSP)
{
QuanLySieuThiEntities qlKDEntity = new QuanLySieuThiEntities(_conString);
SANPHAM sp = (from p in qlKDEntity.SANPHAMs
where p.MaSP == MaSP
select p).FirstOrDefault();
MemoryStream fileAnh = new MemoryStream(sp.Hinh.ToArray());
Image img = Image.FromStream(fileAnh);
return(img);
}
public bool CapNhatPhongBan(string MaPB, string TenPhong, string TruongPhong, ref string err)
{
try
{
QuanLySieuThiEntities qlKDEntity = new QuanLySieuThiEntities(_conString);
string query = string.Format("EXEC dbo.usp_PhongBan_Sua N'{0}', N'{1}', N'{2}'", MaPB, TenPhong, LayMaNV(TruongPhong));
qlKDEntity.Database.ExecuteSqlCommand(query);
return(true);
} catch (Exception e) { }
return(false);
}
public DataTable LayNhanVienTheoSapXep(KieuSapXepNhanVien kieuSapXep, bool isTang)
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
DbSqlQuery <Quan_Ly_Kinh_Doanh.NHANVIEN> sps;
if (isTang == true)
{
switch (kieuSapXep)
{
case KieuSapXepNhanVien.THEO_TEN:
sps = qlSTEntity.NHANVIENs.SqlQuery("SELECT * FROM dbo.NHANVIEN ORDER BY Ten ASC");
break;
case KieuSapXepNhanVien.THEO_LUONG:
sps = qlSTEntity.NHANVIENs.SqlQuery("SELECT *FROM NHANVIEN ORDER BY LuongCB ASC");
break;
default:
sps = qlSTEntity.NHANVIENs.SqlQuery("SELECT * FROM dbo.NHANVIEN ORDER BY NgaySinh DESC");
break;
}
}
else
{
switch (kieuSapXep)
{
case KieuSapXepNhanVien.THEO_TEN:
sps = qlSTEntity.NHANVIENs.SqlQuery("SELECT * FROM dbo.NHANVIEN ORDER BY Ten DESC");
break;
case KieuSapXepNhanVien.THEO_LUONG:
sps = qlSTEntity.NHANVIENs.SqlQuery("SELECT *FROM NHANVIEN ORDER BY LuongCB DESC");
break;
default:
sps = qlSTEntity.NHANVIENs.SqlQuery("SELECT * FROM dbo.NHANVIEN ORDER BY NgaySinh ASC");
break;
}
}
DataTable dt = new DataTable();
SetTableColumn(dt);
foreach (var p in sps)
{
dt.Rows.Add(p.MaNV, p.Ho, p.TenLot, p.Ten,
p.NgaySinh, p.GioiTinh, p.Phong, p.LuongCB, p.DienThoai);
}
return(dt);
}
public bool ThemKhachHang(string MaKH, string TenKH, string DienThoai,
DateTime NgaySinh, string DiaChi, string GioiTinh, ref string err)
{
try
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
string query = string.Format("EXECUTE dbo.usp_KhachHang_Them N'{0}', N'{1}', N'{2}', '{3}', N'{4}', N'{5}'", MaKH, TenKH, GioiTinh, ChuanHoaNgay(NgaySinh), DiaChi, DienThoai);
qlSTEntity.Database.ExecuteSqlCommand(query);
return(true);
} catch (Exception e) { }
return(false);
}
public List <string> LayDSTenSP()
{
List <string> dsMaSP = new List <string>();
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
var sps = from p in qlSTEntity.SANPHAMs select p;
foreach (var item in sps)
{
dsMaSP.Add(item.TenSP);
}
return(dsMaSP);
}
public List <string> LayDSTenPhong()
{
List <string> dsTenPhong = new List <string>();
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
var sps = from p in qlSTEntity.PHONGBANs select p;
foreach (var item in sps)
{
dsTenPhong.Add(item.TenPhong);
}
return(dsTenPhong);
}
public bool XoaPhongBan(string MaPB, ref string err)
{
try
{
QuanLySieuThiEntities qlKDEntity = new QuanLySieuThiEntities(_conString);
string query = string.Format("EXEC dbo.usp_PhongBan_Xoa N'{0}'", MaPB);
qlKDEntity.Database.ExecuteSqlCommand(query);
return(true);
}catch (Exception e) { }
return(false);
}
public List <string> LayDSTenKH()
{
List <string> dsMaKH = new List <string>();
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
var sps = from p in qlSTEntity.KHACHHANGs select p;
foreach (var item in sps)
{
dsMaKH.Add(item.TenKH);
}
return(dsMaKH);
}
public bool KiemTraDangNhap(string MaNV, string MatKhau)
{
QuanLySieuThiEntities qlKDEntity = new QuanLySieuThiEntities(_conString);
//var dnQuery = (from p in qlKDEntity.DANGNHAPs
// where p.MaNV == MaNV && p.MatKhau == MatKhau
// select p).SingleOrDefault();
//if (dnQuery != null)
// return true;
return(false);
}
public bool ThemDangNhap(string Username, string MatKhau, string PhanQuyen, ref string err)
{
try
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
string query = string.Format("EXEC dbo.usp_DangNhap_Them N'{0}', N'{1}', N'{2}'", Username, MatKhau, PhanQuyen);
qlSTEntity.Database.ExecuteSqlCommand(query);
return(true);
}
catch (Exception e) { }
return(false);
}
public bool CapNhatHoaDon(string MaHD, string TenKH, string TenNV,
DateTime NgayLapHD, DateTime NgayNhanHang, ref string err)
{
try
{
QuanLySieuThiEntities qlKDEntity = new QuanLySieuThiEntities(_conString);
string query = string.Format("EXEC dbo.usp_HoaDon_Sua N'{0}', N'{1}', N'{2}', '{3}', '{4}'", MaHD, LayMaKH(TenKH), LayMaNV(TenNV), ChuanHoaNgay(NgayLapHD), ChuanHoaNgay(NgayNhanHang));
qlKDEntity.Database.ExecuteSqlCommand(query);
return(true);
} catch (Exception e) { }
return(false);
}
public List <string> LayDSMaNV()
{
List <string> dsMaKH = new List <string>();
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
var sps = from p in qlSTEntity.NHANVIENs select p;
foreach (var item in sps)
{
dsMaKH.Add(item.MaNV.Trim());
}
return(dsMaKH);
}
public bool XoaKhachHang(string MaKH, ref string err)
{
try
{
QuanLySieuThiEntities qlKDEntity = new QuanLySieuThiEntities(_conString);
string query = string.Format("EXECUTE dbo.usp_KhachHang_Xoa N'{0}'", MaKH);
qlKDEntity.Database.ExecuteSqlCommand(query);
return(true);
} catch (Exception e) { }
return(false);
}
public List <string> LayDSMaHD()
{
List <string> dsMaHD = new List <string>();
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
var sps = from p in qlSTEntity.HOADONs select p;
foreach (var item in sps)
{
dsMaHD.Add(item.MaHD);
}
return(dsMaHD);
}
public List <string> LayDSTenNV()
{
List <string> dsTenNV = new List <string>();
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
var sps = from p in qlSTEntity.NHANVIENs orderby p.Ten ascending select p;
foreach (var item in sps)
{
dsTenNV.Add(item.Ho.Trim() + " " + item.TenLot.Trim() + " " + item.Ten.Trim());
}
return(dsTenNV);
}
public DataTable LayKhachHang()
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
var khs = qlSTEntity.view_KhachHang.SqlQuery("SELECT * FROM dbo.view_KhachHang");
DataTable dt = new DataTable();
SetTableColumn(dt);
foreach (var p in khs)
{
dt.Rows.Add(p.MaKH, p.TenKH, p.DienThoai, p.NgaySinh, p.DiaChi, p.GioiTinh);
}
return(dt);
}
public DataTable LayChiTietHoaDon()
{
QuanLySieuThiEntities qlSTEntity = new QuanLySieuThiEntities(_conString);
var pbs = qlSTEntity.CHITIETHOADONs.SqlQuery("SELECT * FROM dbo.CHITIETHOADON");
DataTable dt = new DataTable();
SetTableColumn(dt);
foreach (var p in pbs)
{
dt.Rows.Add(p.MaHD, p.SANPHAM.TenSP, p.SoLuong);
}
return(dt);
}
