public List <ClassHangHoa> TimTheoLoaiHH(string tenLHH)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
List <ClassHangHoa> lstHH = new List <ClassHangHoa>();
db.DeferredLoadingEnabled = false;
var ttHH = from n in db.HangHoas
join u in db.HangSanXuats on n.MaHangSanXuat equals u.MaHangSanXuat
join l in db.LoaiSanPhams on n.MaLoaiHangHoa equals l.MaLoaiSanPham
where l.TenLoaiSanPham == tenLHH
select new
{
n.MaHangHoa,
n.TenHangHoa,
n.GiaNhap,
n.GiaBan,
n.SoLuongCon,
n.TrangThai,
n.ChuThich,
n.Image,
u.TenHangSanXuat,
l.TenLoaiSanPham
};
foreach (var i in ttHH)
{
ClassHangHoa HHNew = new ClassHangHoa();
HHNew.MaHangHoa = i.MaHangHoa;
HHNew.TenHangHoa = i.TenHangHoa;
HHNew.LoaiHangHoa = i.TenLoaiSanPham;
HHNew.HangSanXuat = i.TenHangSanXuat;
HHNew.GiaNhap = i.GiaNhap;
HHNew.GiaBan = i.GiaBan;
HHNew.SoLuongCon = i.SoLuongCon;
HHNew.TrangThai = i.TrangThai;
HHNew.ChuThich = i.ChuThich;
if (i.Image != null)
{
HHNew.Image = ByteArrayToImage(i.Image.ToArray());
}
lstHH.Add(HHNew);
}
//classHH = lstHH;
return(lstHH);
}
}
public bool Xoa(int ma)
{
try
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
HangSanXuat hangSX = db.HangSanXuats.Where(p => p.MaHangSanXuat == ma).SingleOrDefault();
db.HangSanXuats.DeleteOnSubmit(hangSX);
db.SubmitChanges();
return(true);
}
}
catch
{
return(false);
}
}
public void LapChiTietPhieuNhap(int MaPN, int maHangHoa, int soLuongCon, int soLuongNhap, int giaCapNhat, int tTien, string ghiChu, bool tinhTrang)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
ChiTietPhieuNhap chiTietNew = new ChiTietPhieuNhap();
chiTietNew.MaPhieuNhap = MaPN;
chiTietNew.MaHH = maHangHoa;
chiTietNew.SoLuongCon = soLuongCon;
chiTietNew.SoLuongNhap = soLuongNhap;
chiTietNew.GiaCapNhat = giaCapNhat;
chiTietNew.TongTien = tTien;
chiTietNew.GhiChu = ghiChu;
chiTietNew.TinhTrang = tinhTrang;
db.ChiTietPhieuNhaps.InsertOnSubmit(chiTietNew);
db.SubmitChanges();
}
}
public bool XoaTaiKhoan(int m_ID)
{
try
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
User taiKhoan = db.Users.Where(p => p.ID == m_ID).SingleOrDefault();
db.Users.DeleteOnSubmit(taiKhoan);
db.SubmitChanges();
return(true);
}
}
catch
{
return(false);
}
}
public bool Xoa(int maLSP)
{
try
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
LoaiSanPham loaiSP = db.LoaiSanPhams.Where(p => p.MaLoaiSanPham == maLSP).SingleOrDefault();
db.LoaiSanPhams.DeleteOnSubmit(loaiSP);
db.SubmitChanges();
return(true);
}
}
catch
{
return(false);
}
}
public void CapNhatSoLuong(int maHH, int tong, int giaMoi)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
// MessageBox.Show(maNV + "");
HangHoa hangHoa = db.HangHoas.Where(p => p.MaHangHoa == maHH).SingleOrDefault();
if (hangHoa != null)
{
// MessageBox.Show(ghiChu + "");
hangHoa.SoLuongCon = tong;
hangHoa.GiaNhap = giaMoi;
hangHoa.GiaBan = giaMoi + 50000;
db.SubmitChanges();
}
}
}
//trả về list user
public List <User> KiemTraDangNhap(string tenDangNhap, string matKhau, int quyen)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
List <User> accout = new List <User>();
//kiểm tra điều kiện và truyền vào list
accout = db.Users.Where(p => p.TenDangNhap == tenDangNhap &&
p.Password == matKhau &&
p.TinhTrang == true &&
p.Quyen == quyen).ToList();
//accout = db.Users.Select(p => p).ToList();
return(accout);
}
}
public bool Xoa(int maHH)
{
try
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
HangHoa delHH = db.HangHoas.Where(p => p.MaHangHoa == maHH).SingleOrDefault();
db.HangHoas.DeleteOnSubmit(delHH);
db.SubmitChanges();
return(true);
}
}
catch
{
return(false);
}
}
public void LapChiTietHoaDon(int maHD, int maHangHoa, int soLuongMua, int tTien, string ghiChu)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
int giaNhap = db.HangHoas.Where(p => p.MaHangHoa == maHangHoa).SingleOrDefault().GiaBan;
ChiTietHoaDon chiTietNew = new ChiTietHoaDon();
chiTietNew.MaHoaDon = maHD;
chiTietNew.MaHangHoa = maHangHoa;
chiTietNew.GiaNhap = giaNhap;
chiTietNew.SoLuong = soLuongMua;
chiTietNew.TongTien = tTien;
chiTietNew.GhiChu = ghiChu;
db.ChiTietHoaDons.InsertOnSubmit(chiTietNew);
db.SubmitChanges();
}
}
public bool Sua(int maNV, string tenNV, string chucVu, DateTime ngaySinh, string gioiTinh, int cmnd,
string email, DateTime ngayVaoLam, DateTime?ngayNghiLam, string diaChi, string soDT, Boolean tinhTrang, string chuThich)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
// MessageBox.Show(maNV + "");
NhanVien nhanVien = db.NhanViens.Where(p => p.MaNhanVien == maNV).SingleOrDefault();
var kiemTraNV = db.NhanViens.Where(p => p.CMND.Equals(cmnd) || p.Email.Equals(email) || p.SoDT.Equals(soDT));
if (kiemTraNV.Count() <= 1)
{
int maChucVu = GetChucVu(chucVu);
//int maHangSX = GetMaHangSanXuat(hangSanXuat);
if (nhanVien != null)
{
nhanVien.MaNhanVien = maNV;
nhanVien.TenNhanVien = tenNV;
nhanVien.CMND = cmnd;
nhanVien.NgaySinh = ngaySinh;
nhanVien.GioiTinh = gioiTinh;
nhanVien.Email = email;
nhanVien.NgayVaoLam = ngayVaoLam;
nhanVien.NgayNghiLam = ngayNghiLam;
nhanVien.ChucVu = maChucVu;
nhanVien.DiaChi = diaChi;
nhanVien.SoDT = soDT;
nhanVien.TinhTrang = tinhTrang;
nhanVien.GhiChu = chuThich;
nhanVien.Anh = null;;
db.SubmitChanges();
return(true);
}
else
{
MessageBox.Show("null");
return(false);
}
}
else
{
return(false);
}
}
}
public List <ClassHangHoa> GetHangHoa()
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
List <ClassHangHoa> lstHH = new List <ClassHangHoa>();
db.DeferredLoadingEnabled = false;
var ttHH = from n in db.HangHoas
join u in db.HangSanXuats on n.MaHangSanXuat equals u.MaHangSanXuat
join l in db.LoaiSanPhams on n.MaLoaiHangHoa equals l.MaLoaiSanPham
where n.TrangThai == true
select new
{
n.MaHangHoa,
n.TenHangHoa,
n.GiaNhap,
n.GiaBan,
n.SoLuongCon,
n.TrangThai,
n.ChuThich,
n.Image,
u.TenHangSanXuat,
l.TenLoaiSanPham
};
foreach (var i in ttHH)
{
ClassHangHoa HHNew = new ClassHangHoa();
HHNew.MaHangHoa = i.MaHangHoa;
HHNew.TenHangHoa = i.TenHangHoa;
HHNew.LoaiHangHoa = i.TenLoaiSanPham;
HHNew.HangSanXuat = i.TenHangSanXuat;
HHNew.GiaNhap = i.GiaNhap;
HHNew.GiaBan = i.GiaBan;
HHNew.SoLuongCon = i.SoLuongCon;
HHNew.TrangThai = i.TrangThai;
HHNew.ChuThich = i.ChuThich;
lstHH.Add(HHNew);
}
return(lstHH);
}
}
public bool Xoa(int maNV)
{
try
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
//MessageBox.Show( "thuc hien xoa");
NhanVien delNV = db.NhanViens.Where(p => p.MaNhanVien == maNV).SingleOrDefault();
db.NhanViens.DeleteOnSubmit(delNV);
db.SubmitChanges();
return(true);
}
}
catch
{
//MessageBox.Show("thuc hien xoa"+ex);
return(false);
}
}
public bool Xoa(int maKH)
{
try
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
//MessageBox.Show( "thuc hien xoa");
KhachHang delKH = db.KhachHangs.Where(p => p.MaKhachHang == maKH).SingleOrDefault();
db.KhachHangs.DeleteOnSubmit(delKH);
db.SubmitChanges();
return(true);
}
}
catch
{
//MessageBox.Show("thuc hien xoa"+ex);
return(false);
}
}
public void SuaTK(int m_ID, int maNV, string tenDN, string m_MK, string quyen, string chuThich, bool tinhTrang)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
int quyenUS = GetQuyen(quyen);
User editUser = db.Users.Where(p => p.ID.Equals(m_ID)).SingleOrDefault();
if (editUser != null)
{
editUser.ID = m_ID;
editUser.MaNhanVien = maNV;
editUser.TenDangNhap = tenDN;
editUser.Password = m_MK;
editUser.Quyen = quyenUS;
editUser.ChuThich = chuThich;
editUser.TinhTrang = tinhTrang;
db.SubmitChanges();
}
}
}
public void Hien(int maHH)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
HangHoa editHH = db.HangHoas.Where(p => p.MaHangHoa == maHH).SingleOrDefault();
try
{
if (editHH != null)
{
editHH.TrangThai = true;
db.SubmitChanges();
}
}
catch
{
}
}
}
public List <string> ListTimTheoMa()
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
List <string> lstTenLSP = new List <string>();
var tenLSP = from n in db.LoaiSanPhams
select n.MaLoaiSanPham;
try
{
foreach (int i in tenLSP)
{
lstTenLSP.Add(i.ToString());
}
return(lstTenLSP);
}
catch
{
return(null);
}
}
}
public bool SuaPhieu(int maPN, int maHH, int giaMoi, string chuThich)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
// MessageBox.Show(maNV + "");
ChiTietPhieuNhap ctPhieuNhap = db.ChiTietPhieuNhaps.Where(p => p.MaPhieuNhap == maPN && p.MaHH == maHH).SingleOrDefault();
if (ctPhieuNhap != null)
{
ctPhieuNhap.GiaCapNhat = giaMoi;
ctPhieuNhap.GhiChu = chuThich;
db.SubmitChanges();
return(true);
}
else
{
return(false);
}
}
}
public List <string> ListTimTheoTen()
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
List <string> lstTenHH = new List <string>();
var tenHH = from n in db.HangHoas
select n.TenHangHoa;
try
{
foreach (string i in tenHH)
{
lstTenHH.Add(i);
}
return(lstTenHH);
}
catch
{
return(null);
}
}
}
public List <string> ListTimTheoMa()
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
List <string> lstManHD = new List <string>();
var tenHH = from n in db.HoaDons
select n.MaHoaDon;
try
{
foreach (int i in tenHH)
{
lstManHD.Add(i.ToString());
}
return(lstManHD);
}
catch
{
return(null);
}
}
}
public List <string> ListTimTheoTen()
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
List <string> lstTenNV = new List <string>();
var tenNV = from n in db.KhachHangs
select n.TenKhachHang;
try
{
foreach (string i in tenNV)
{
lstTenNV.Add(i);
}
return(lstTenNV);
}
catch
{
return(null);
}
}
}
public void Them(int maHangHoa, string tenHangHoa, string loaiHangHoa, string hangSanXuat, int giaNhap, int giaBan, int soLuongCon, bool trangThai, string chuThich)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
int maLoaiHH = GetMaLoaiHH(loaiHangHoa);
int maHangSX = GetMaHangSanXuat(hangSanXuat);
HangHoa hangHoaNew = new HangHoa();
hangHoaNew.MaHangHoa = maHangHoa;
hangHoaNew.TenHangHoa = tenHangHoa;
hangHoaNew.MaLoaiHangHoa = maLoaiHH;
hangHoaNew.MaHangSanXuat = maHangSX;
hangHoaNew.GiaNhap = giaNhap;
hangHoaNew.GiaBan = giaBan;
hangHoaNew.SoLuongCon = soLuongCon;
hangHoaNew.TrangThai = trangThai;
hangHoaNew.ChuThich = chuThich;
hangHoaNew.Image = null;
db.HangHoas.InsertOnSubmit(hangHoaNew);
db.SubmitChanges();
}
}
public List <ClassHoaDon> TimTheoKH(string keyTimKiem)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
List <ClassHoaDon> lstHD = new List <ClassHoaDon>();
db.DeferredLoadingEnabled = false;
var ttHD = from h in db.HoaDons
join n in db.NhanViens on h.MaNhanVien equals n.MaNhanVien
join k in db.KhachHangs on h.MaKhachHang equals k.MaKhachHang
where k.MaKhachHang.ToString().Equals(keyTimKiem) ||
k.TenKhachHang.Equals(keyTimKiem) ||
k.SDT.Equals(keyTimKiem) ||
k.Email.Equals(keyTimKiem)
select new
{
HoaDon = h,
NhanVien = n,
KhachHang = k
};
foreach (var i in ttHD)
{
ClassHoaDon hoaDonNew = new ClassHoaDon();
hoaDonNew.MaHoaDon = i.HoaDon.MaHoaDon;
hoaDonNew.TenKhachHang = i.KhachHang.TenKhachHang;
hoaDonNew.TenNhanVien = i.NhanVien.TenNhanVien;
hoaDonNew.TongTien = i.HoaDon.TongTien;
hoaDonNew.NgayLapHoaDon = i.HoaDon.NgayLapHoaDon;
hoaDonNew.GhiChu = i.HoaDon.GhiChu;
lstHD.Add(hoaDonNew);
}
return(lstHD);
}
}
public List <ClassKhachHang> GetKhachHang()
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
List <ClassKhachHang> lstKH = new List <ClassKhachHang>();
db.DeferredLoadingEnabled = false;
var ttKH = from k in db.KhachHangs
join l in db.LoaiKhachHangs on k.LoaiKhachHang equals l.MaLoaiKhachHang
select new
{
KhachHang = k,
LoaiKhachHang = l
};
foreach (var i in ttKH)
{
// MessageBox.Show(""+i);
ClassKhachHang KHNew = new ClassKhachHang();
KHNew.MaKhachHang = i.KhachHang.MaKhachHang;
KHNew.TenKhachHang = i.KhachHang.TenKhachHang;
KHNew.Ngaysinh = i.KhachHang.Ngaysinh;
KHNew.GioiTinh = i.KhachHang.GioiTinh;
KHNew.Email = i.KhachHang.Email;
KHNew.DiaChi = i.KhachHang.DiaChi;
KHNew.SDT = i.KhachHang.SDT;
KHNew.LoaiKhachHang = i.LoaiKhachHang.TenLoaiKhachHang;
KHNew.GhiChu = i.KhachHang.GhiChu;
if (i.KhachHang.Image != null)
{
KHNew.Image = ByteArrayToImage(i.KhachHang.Image.ToArray());
}
lstKH.Add(KHNew);
}
classKH = lstKH;
return(lstKH);
}
}
public bool HuyPhieu(int maPN)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
// MessageBox.Show(maNV + "");
PhieuNhap phieuNhap = db.PhieuNhaps.Where(p => p.ID == maPN).SingleOrDefault();
if (phieuNhap != null)
{
// MessageBox.Show(ghiChu + "");
phieuNhap.TinhTrang = null;
phieuNhap.GhiChu = "Phiếu đã hủy";
db.SubmitChanges();
return(true);
}
else
{
return(false);
}
}
}
public bool DoiMatKhau(string tenTK, string nMKCu, string nMKMoi)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
User editUser = db.Users.Where(p => p.TenDangNhap.Equals(tenTK) &&
p.Password.Equals(nMKCu) &&
p.Quyen != 1).SingleOrDefault();
try
{ if (editUser != null)
{
editUser.Password = nMKMoi;
db.SubmitChanges();
return(true);
}
return(false); }
catch
{
return(false);
}
}
}
public bool Sua(int maKH, string tenKH, DateTime ngaySinhKH, string gioiTinh,
string email, string diaChi, string soDT, string loaiKH, string chuThich)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
// MessageBox.Show(maNV + "");
KhachHang khachHang = db.KhachHangs.Where(p => p.MaKhachHang == maKH).SingleOrDefault();
var kiemTraNV = db.NhanViens.Where(p => p.Email.Equals(email) || p.SoDT.Equals(soDT));
if (kiemTraNV.Count() <= 1)
{
int maloaiKH = GetLoaiKH(loaiKH);
//int maHangSX = GetMaHangSanXuat(hangSanXuat);
if (khachHang != null)
{
khachHang.MaKhachHang = maKH;
khachHang.TenKhachHang = tenKH;
khachHang.Ngaysinh = ngaySinhKH;
khachHang.GioiTinh = gioiTinh;
khachHang.Email = email;
khachHang.DiaChi = diaChi;
khachHang.SDT = soDT;
khachHang.LoaiKhachHang = maloaiKH;
khachHang.GhiChu = chuThich;
db.SubmitChanges();
return(true);
}
else
{
MessageBox.Show("null");
return(false);
}
}
else
{
return(false);
}
}
}
public List <string> ListTimTheoMa()
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
List <string> lstMaHSX = new List <string>();
var tenHSX = from n in db.HangSanXuats
select n.MaHangSanXuat;
try
{
foreach (int i in tenHSX)
{
// MessageBox.Show("2");
lstMaHSX.Add(i.ToString());
}
return(lstMaHSX);
}
catch
{
return(null);
}
}
}
public string LayLaiMatKhau(string strEmail)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
var tenTK = from n in db.NhanViens
from u in db.Users
where n.MaNhanVien == u.MaNhanVien && n.Email.Equals(strEmail)
select u.Password;
try
{
foreach (string i in tenTK)
{
return(i);
}
}
catch
{
return("");
}
}
return("");
}
public string KiemTraEmail(string strEmail)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
var tenTK = from n in db.NhanViens
from u in db.Users
where n.MaNhanVien == u.MaNhanVien && n.Email.Equals(strEmail)
select u.TenDangNhap;
try
{
foreach (string i in tenTK)
{
return(i);
}
}
catch
{
return("");
}
}
return("");
}
public bool XacNhanPhieu(int maPN, int maNV, DateTime ngayXacNhan)
{
using (CSDLQuanLyQuanAoDataContext db = new CSDLQuanLyQuanAoDataContext())
{
// MessageBox.Show(maNV + "");
PhieuNhap phieuNhap = db.PhieuNhaps.Where(p => p.ID == maPN).SingleOrDefault();
if (phieuNhap != null)
{
// MessageBox.Show(ghiChu + "");
phieuNhap.NVXacNhan = maNV;
phieuNhap.NgayXacNhan = ngayXacNhan;
phieuNhap.TinhTrang = false;
db.SubmitChanges();
return(true);
}
else
{
return(false);
}
}
}
