public User GetUser(string UserName)
{
QLCHXeEntity db = new QLCHXeEntity();
var result = (
from c in db.Users where c.TaiKhoan == UserName
select new
{
ID = c.ID,
TaiKhoan = c.TaiKhoan,
MatKhau = c.MatKhau,
TrangThai = c.TrangThai
}
).First();
if (result == null)
{
return(null);
}
User a = new User();
a.ID = result.ID;
a.TaiKhoan = result.TaiKhoan;
a.MatKhau = result.MatKhau;
a.TrangThai = result.TrangThai;
return(a);
}
public static List <User> LoadUsers()
{
QLCHXeEntity db = new QLCHXeEntity();
var result = (
from c in db.Users
select new
{
ID = c.ID,
TaiKhoan = c.TaiKhoan,
MatKhau = c.MatKhau,
TrangThai = c.TrangThai
}).ToList();
List <User> listReturn = new List <User>();
foreach (var item in result)
{
User user_list = new User();
user_list.ID = item.ID;
user_list.TaiKhoan = item.TaiKhoan;
user_list.MatKhau = item.MatKhau;
user_list.TrangThai = item.TrangThai;
listReturn.Add(user_list);
}
return(listReturn);
}
public static List <KhachHang> LoadKH()
{
QLCHXeEntity db = new QLCHXeEntity();
var result = (
from khdb in db.KhachHangs
select new
{
MaKhachHang = khdb.MaKhachHang,
HoTen = khdb.HoTen,
DiaChi = khdb.DiaChi,
NgaySinh = khdb.NgaySinh,
SoDienThoai = khdb.SoDienThoai,
Email = khdb.Email
}).ToList();
List <KhachHang> listReturn = new List <KhachHang>();
foreach (var item in result)
{
KhachHang kh_list = new KhachHang();
kh_list.MaKhachHang = item.MaKhachHang;
kh_list.HoTen = item.HoTen;
kh_list.DiaChi = item.DiaChi;
kh_list.NgaySinh = item.NgaySinh;
kh_list.SoDienThoai = item.SoDienThoai;
kh_list.Email = item.Email;
listReturn.Add(kh_list);
}
return(listReturn);
}
public static List <Xe> LoadXe()
{
QLCHXeEntity db = new QLCHXeEntity();
var result = (
from c in db.Xes
join hx in db.HangXes on c.MaHangXe equals hx.MaHangXe
join lx in db.LoaiXes on c.MaLoaiXe equals lx.MaLoaiXe
join ncc in db.NhaCungCaps on c.MaNhaCungCap equals ncc.MaNhaCungCap
select new
{
MaXe = c.MaXe,
MaHangXe = c.MaHangXe,
MaLoaiXe = c.MaLoaiXe,
MaNhaCungCap = c.MaNhaCungCap,
TenXe = c.TenXe,
SoLuong = c.SoLuong,
BaoHanh = c.ThongTinBaoHanh,
DonGia = c.DonGia,
GhiChu = c.GhiChu,
HangXe = hx.TenHangXe,
LoaiXe = lx.TenLoaiXe,
NhaCungCap = ncc.TenNhaCungCap,
HinhAnh = c.HinhAnh
}).ToList();
List <Xe> listReturn = new List <Xe>();
foreach (var item in result)
{
Xe xe_list = new Xe();
HangXe hx = new HangXe();
hx.TenHangXe = item.HangXe;
xe_list.HangXe = hx;
LoaiXe lx = new LoaiXe();
lx.TenLoaiXe = item.LoaiXe;
xe_list.LoaiXe = lx;
NhaCungCap ncc = new NhaCungCap();
ncc.TenNhaCungCap = item.NhaCungCap;
xe_list.NhaCungCap = ncc;
xe_list.MaXe = item.MaXe;
xe_list.MaHangXe = item.MaHangXe;
xe_list.MaLoaiXe = item.MaLoaiXe;
xe_list.MaNhaCungCap = item.MaNhaCungCap;
xe_list.TenXe = item.TenXe;
xe_list.SoLuong = item.SoLuong;
xe_list.ThongTinBaoHanh = item.BaoHanh;
xe_list.DonGia = item.DonGia;
xe_list.GhiChu = item.GhiChu;
xe_list.HinhAnh = item.HinhAnh;
listReturn.Add(xe_list);
}
return(listReturn);
}
public static object LoadMaKhachHang()
{
QLCHXeEntity db = new QLCHXeEntity();
return
((
from c in db.KhachHangs
select new { MaKhachHang = c.MaKhachHang, HoTen = c.HoTen }
).ToList());
}
public static object LoadNCC()
{
QLCHXeEntity db = new QLCHXeEntity();
return
((
from c in db.NhaCungCaps
select new { MaNhaCungCap = c.MaNhaCungCap, TenNhaCungCap = c.TenNhaCungCap }
).ToList());
}
public static object LoadLoaiXe()
{
QLCHXeEntity db = new QLCHXeEntity();
return
((
from c in db.LoaiXes
select new { MaLoaiXe = c.MaLoaiXe, TenLoaiXe = c.TenLoaiXe }
).ToList());
}
public static object LoadMaNhanVien()
{
QLCHXeEntity db = new QLCHXeEntity();
return
((
from c in db.NhanViens
select new { MaNhanVien = c.MaNhanVien, HoTen = c.HoTen }
).ToList());
}
public static object LoadMaKhuyenMai()
{
QLCHXeEntity db = new QLCHXeEntity();
return
((
from c in db.KhuyenMais
select new { MaKhuyenMai = c.MaKhuyenMai, NoiDung = c.NoiDung }
).ToList());
}
public static object DuyetMaXe()
{
QLCHXeEntity db = new QLCHXeEntity();
return
((
from x in db.Xes
join hx in db.HangXes on x.MaHangXe equals hx.MaHangXe
select new {
MaXe = x.MaXe,
HangXe = hx.TenHangXe,
TenXe = x.TenXe
}
).ToList());
}
public object loadHoaDon_ThongKe_ChuKi(DateTime fromTime, DateTime toTime)
{
QLCHXeEntity db = new QLCHXeEntity();
return
((
from c in db.HoaDons
where c.NgayBan >= fromTime && c.NgayBan <= toTime
select new
{
MaHoaDon = c.MaHoaDon,
MaKhachHang = c.MaKhachHang,
MaNhanVien = c.MaNhanVien,
NgayBan = c.NgayBan,
TongDonGia = c.TongDonGia,
}).ToList());
}
public object loadHoaDon_ThongKe_Nam(DateTime time)
{
QLCHXeEntity db = new QLCHXeEntity();
return
((
from c in db.HoaDons
where c.NgayBan.Year == time.Year
select new
{
MaHoaDon = c.MaHoaDon,
MaKhachHang = c.MaKhachHang,
MaNhanVien = c.MaNhanVien,
NgayBan = c.NgayBan,
TongDonGia = c.TongDonGia,
}).ToList());
}
public bool CheckLogin(string username, string password)
{
QLCHXeEntity context = new QLCHXeEntity();
var q = from p in context.Users
where p.TaiKhoan == username &&
p.MatKhau == password
select p;
if (q.Any())
{
return(true);
}
else
{
return(false);
}
}
public object LoadDaHetHang()
{
QLCHXeEntity db = new QLCHXeEntity();
return
((
from x in db.Xes
join hx in db.HangXes on x.MaHangXe equals hx.MaHangXe
where x.SoLuong == 0
select new
{
MaXe = x.MaXe,
HangXe = hx.TenHangXe,
TenXe = x.TenXe,
SoLuong = x.SoLuong
}
).ToList());
}
public static List <PhieuNhap> LoadKho()
{
QLCHXeEntity db = new QLCHXeEntity();
var result = (
from c in db.PhieuNhaps
join ncc in db.NhaCungCaps on c.MaNhaCungCap equals ncc.MaNhaCungCap
join nv in db.NhanViens on c.MaNhanVien equals nv.MaNhanVien
select new
{
MaPhieuNhap = c.MaPhieuNhap,
MaNhanVien = c.MaNhanVien,
NgayNhap = c.NgayNhap,
MaNhaCungCap = c.MaNhaCungCap,
TongDonGia = c.TongDonGia,
NhanVien = nv.HoTen,
NhaCungCap = ncc.TenNhaCungCap
}).ToList();
List <PhieuNhap> listReturn = new List <PhieuNhap>();
foreach (var item in result)
{
PhieuNhap phieunhap_list = new PhieuNhap();
NhanVien nv = new NhanVien();
nv.HoTen = item.NhanVien;
phieunhap_list.NhanVien = nv;
NhaCungCap ncc = new NhaCungCap();
ncc.TenNhaCungCap = item.NhaCungCap;
phieunhap_list.NhaCungCap = ncc;
phieunhap_list.MaPhieuNhap = item.MaPhieuNhap;
phieunhap_list.MaNhanVien = item.MaNhanVien;
phieunhap_list.NgayNhap = item.NgayNhap;
phieunhap_list.MaNhaCungCap = item.MaNhaCungCap;
phieunhap_list.TongDonGia = item.TongDonGia;
listReturn.Add(phieunhap_list);
}
return(listReturn);
}
public static List <ChiTietHoaDon> LoadCTHoaDon(int id)
{
QLCHXeEntity db = new QLCHXeEntity();
var result = (
from c in db.ChiTietHoaDons
join x in db.Xes on c.MaSanPham equals x.MaXe
join km in db.KhuyenMais on c.MaKhuyenMai equals km.MaKhuyenMai
where c.MaHoaDon == id
select new
{
MaHoaDon = c.MaHoaDon,
MaSanPham = c.MaSanPham,
TenXe = x.TenXe,
DonGia = c.DonGia,
SoLuong = c.SoLuong,
MaKhuyenMai = c.MaKhuyenMai,
GiamGia = c.GiamGia,
ThanhTien = c.ThanhTien,
NoiDung = km.NoiDung
}).ToList();
List <ChiTietHoaDon> listReturn = new List <ChiTietHoaDon>();
foreach (var item in result)
{
ChiTietHoaDon cthoadon_list = new ChiTietHoaDon();
cthoadon_list.MaHoaDon = item.MaHoaDon;
cthoadon_list.MaSanPham = item.MaSanPham;
cthoadon_list.TenXe = item.TenXe;
cthoadon_list.DonGia = item.DonGia;
cthoadon_list.SoLuong = item.SoLuong;
cthoadon_list.MaKhuyenMai = item.MaKhuyenMai;
cthoadon_list.GiamGia = item.GiamGia;
cthoadon_list.ThanhTien = item.ThanhTien;
listReturn.Add(cthoadon_list);
}
return(listReturn);
}
public static List <HoaDon> LoadHoaDon()
{
QLCHXeEntity db = new QLCHXeEntity();
var result = (
from c in db.HoaDons
join kh in db.KhachHangs on c.MaKhachHang equals kh.MaKhachHang
join nv in db.NhanViens on c.MaNhanVien equals nv.MaNhanVien
select new
{
MaHoaDon = c.MaHoaDon,
MaKhachHang = c.MaKhachHang,
MaNhanVien = c.MaNhanVien,
NgayBan = c.NgayBan,
TongDonGia = c.TongDonGia,
TenKhachHang = kh.HoTen,
TenNhanVien = nv.HoTen
}).ToList();
List <HoaDon> listReturn = new List <HoaDon>();
foreach (var item in result)
{
HoaDon hoadon_list = new HoaDon();
NhanVien nv = new NhanVien();
nv.HoTen = item.TenNhanVien;
hoadon_list.NhanVien = nv;
KhachHang kh = new KhachHang();
kh.HoTen = item.TenKhachHang;
hoadon_list.KhachHang = kh;
hoadon_list.MaHoaDon = item.MaHoaDon;
hoadon_list.MaNhanVien = item.MaNhanVien;
hoadon_list.MaKhachHang = item.MaKhachHang;
hoadon_list.NgayBan = item.NgayBan;
hoadon_list.TongDonGia = item.TongDonGia;
listReturn.Add(hoadon_list);
}
return(listReturn);
}
public static List <ChiTietPhieuNhap> LoadCTKho(int id)
{
QLCHXeEntity db = new QLCHXeEntity();
var result = (
from c in db.ChiTietPhieuNhaps
join x in db.Xes on c.MaSanPham equals x.MaXe
where c.MaPhieuNhap == id
select new
{
ID = c.ID,
MaPhieuNhap = c.MaPhieuNhap,
MaSanPham = c.MaSanPham,
DonGiaNhap = c.DonGiaNhap,
SoLuongNhap = c.SoLuongNhap,
Thue = c.Thue,
ThanhTien = c.ThanhTien,
TenXe = x.TenXe
}
).ToList();
List <ChiTietPhieuNhap> listReturn = new List <ChiTietPhieuNhap>();
foreach (var item in result)
{
ChiTietPhieuNhap ctphieunhap_list = new ChiTietPhieuNhap();
Xe x = new Xe();
x.TenXe = item.TenXe;
ctphieunhap_list.Xe = x;
ctphieunhap_list.ID = item.ID;
ctphieunhap_list.MaPhieuNhap = item.MaPhieuNhap;
ctphieunhap_list.MaSanPham = item.MaSanPham;
ctphieunhap_list.DonGiaNhap = item.DonGiaNhap;
ctphieunhap_list.SoLuongNhap = item.SoLuongNhap;
ctphieunhap_list.Thue = item.Thue;
ctphieunhap_list.ThanhTien = item.ThanhTien;
listReturn.Add(ctphieunhap_list);
}
return(listReturn);
}
public object LoadSapHetHang()
{
QLCHXeEntity db = new QLCHXeEntity();
return
((
from x in db.Xes
join hx in db.HangXes on x.MaHangXe equals hx.MaHangXe
where (x.SoLuong > 0 && x.SoLuong <= 10)
select new
{
MaXe = x.MaXe,
HangXe = hx.TenHangXe,
TenXe = x.TenXe,
SoLuongCon = x.SoLuong
}
).ToList());
}
public object LoadBanChay()
{
QLCHXeEntity db = new QLCHXeEntity();
return
((
from x in db.Xes
join cthd in db.ChiTietHoaDons on x.MaXe equals cthd.MaSanPham
join hx in db.HangXes on x.MaHangXe equals hx.MaHangXe
where cthd.SoLuong >= (float)(x.SoLuong / 2)
select new
{
MaXe = x.MaXe,
HangXe = hx.TenHangXe,
TenXe = x.TenXe,
TongSoLuongNhap = x.SoLuong + cthd.SoLuong,
SoLuongBan = cthd.SoLuong,
SoLuongCon = x.SoLuong
}
).ToList());
}
public static List <NhanVien> LoadNV()
{
QLCHXeEntity db = new QLCHXeEntity();
var result = (
from c in db.NhanViens
select new
{
MaNhanVien = c.MaNhanVien,
HoTen = c.HoTen,
NgaySinh = c.NgaySinh,
GioiTinh = c.GioiTinh,
DiaChi = c.DiaChi,
SoDienThoai = c.SoDienThoai,
Email = c.Email,
LuongCoBan = c.LuongCoBan,
ChucVu = c.ChucVu,
GhiChu = c.GhiChu
}).ToList();
List <NhanVien> listReturn = new List <NhanVien>();
foreach (var item in result)
{
NhanVien nv_list = new NhanVien();
nv_list.MaNhanVien = item.MaNhanVien;
nv_list.HoTen = item.HoTen;
nv_list.NgaySinh = item.NgaySinh;
nv_list.GioiTinh = item.GioiTinh;
nv_list.DiaChi = item.DiaChi;
nv_list.SoDienThoai = item.SoDienThoai;
nv_list.Email = item.Email;
nv_list.LuongCoBan = item.LuongCoBan;
nv_list.ChucVu = item.ChucVu;
nv_list.GhiChu = item.GhiChu;
listReturn.Add(nv_list);
}
return(listReturn);
}
public object LoadTonKho()
{
QLCHXeEntity db = new QLCHXeEntity();
return
((
from x in db.Xes
join ctpn in db.ChiTietPhieuNhaps on x.MaXe equals ctpn.MaSanPham
join pn in db.PhieuNhaps on ctpn.MaPhieuNhap equals pn.MaPhieuNhap
join hx in db.HangXes on x.MaHangXe equals hx.MaHangXe
where x.MaXe == ctpn.MaSanPham && ctpn.SoLuongNhap - x.SoLuong <= 10
select new
{
NgayNhap = pn.NgayNhap,
MaXe = x.MaXe,
HangXe = hx.TenHangXe,
TenXe = x.TenXe,
SoLuongNhap = ctpn.SoLuongNhap,
SoLuongConLai = x.SoLuong
}
).ToList());
}
