public bool InsertPBH(DTO_PhieuBanHang obj)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var result = db.Database
.SqlQuery <String>("select MaNhanVien from dbo.NhanVien where TenNhanVien = N'" + obj.TenNhanVien + "'")
.FirstOrDefault();
PhieuBanHang phieuBanHang = new PhieuBanHang();
phieuBanHang.MaPhieuBanHang = obj.MaPhieuBanHang;
phieuBanHang.NgayBan = obj.NgayBan;
phieuBanHang.MaNhanVien = result;
phieuBanHang.TenKhachHang = obj.TenKhachHang;
phieuBanHang.SoDienThoai = obj.SoDienThoai;
phieuBanHang.TongTien = obj.TongTien;
phieuBanHang.GhiChu = obj.GhiChu;
phieuBanHang.NgayChinhSua = obj.NgayChinhSua;
db.PhieuBanHangs.Add(phieuBanHang);
if (db.SaveChanges() > 0)
{
return(true);
}
return(false);
}
}
public List <DTO_PhieuBanHang> SearchPBH(string str)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var result = (from item in db.PhieuBanHangs
join nv in db.NhanViens
on item.MaNhanVien equals nv.MaNhanVien
where item.MaPhieuBanHang.Contains(str.ToUpper()) ||
nv.TenNhanVien.Contains(str) ||
item.TongTien.ToString().Contains(str) ||
item.GhiChu.Contains(str) ||
item.TenKhachHang.Contains(str) ||
item.SoDienThoai.Contains(str)
select new DTO_PhieuBanHang
{
MaPhieuBanHang = item.MaPhieuBanHang,
NgayBan = item.NgayBan,
TenNhanVien = nv.TenNhanVien,
TenKhachHang = item.TenKhachHang,
SoDienThoai = item.SoDienThoai,
TongTien = item.TongTien,
GhiChu = item.GhiChu,
NgayChinhSua = item.NgayChinhSua
}).ToList <DTO_PhieuBanHang>();
return(result);
}
}
public int getSoLuong(string tenHangHoa)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var maHangHoa = db.Database
.SqlQuery <String>("select MaHangHoa from dbo.HangHoa where TenHangHoa = N'" + tenHangHoa + "'")
.FirstOrDefault();
var result = (from item in db.HangHoas
where item.MaHangHoa.Equals(maHangHoa)
select new DTO_HangHoa
{
MaHangHoa = item.MaHangHoa,
TenHangHoa = item.TenHangHoa,
GiaBan = item.GiaBan,
GiamGia = item.GiamGia,
SoLuongTon = item.SoLuongTon,
DonViTinh = item.DonViTinh,
MoTa = item.MoTa,
ThongSoKyThuat = item.ThongSoKyThuat,
XuatXu = item.XuatXu,
ThoiGianBaoHang = item.ThoiGianBaoHang,
HinhAnh = item.HinhAnh,
TrangThai = item.TrangThai,
TenModel = item.TenModel
}).ToList <DTO_HangHoa>();
if (result.Count > 0)
{
return((int)result.First().SoLuongTon);
}
else
{
return(0);
}
}
}
public List <PhanQuyen> showPQ()
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
return(db.PhanQuyens.ToList());
}
}
public List <DTO_PhieuNhap> search(string str)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var result = (from item in db.PhieuNhaps
join nv in db.NhanViens
on item.MaNhanVien equals nv.MaNhanVien
join ncc in db.NhaCungCaps
on item.MaNhaCungCap equals ncc.MaNhaCungCap
where item.MaPhieuNhap.Contains(str.ToUpper()) ||
nv.TenNhanVien.Contains(str) ||
item.TongTien.ToString().Contains(str) ||
ncc.TenNhaCungCap.Contains(str) ||
item.GhiChu.Contains(str) ||
item.MaPhieuNhap.Contains(str)
select new DTO_PhieuNhap
{
MaPhieuNhap = item.MaPhieuNhap,
NgayNhap = item.NgayNhap,
NgayChinhSua = item.NgayChinhSua,
TenNhanVien = nv.TenNhanVien,
TenNhaCungCap = ncc.TenNhaCungCap,
TongTien = item.TongTien,
GhiChu = item.GhiChu
}).ToList <DTO_PhieuNhap>();
return(result);
}
}
public List <DTO_PhieuNhap> searchDate(DateTime from, DateTime to)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var result = (from item in db.PhieuNhaps
join nv in db.NhanViens
on item.MaNhanVien equals nv.MaNhanVien
join ncc in db.NhaCungCaps
on item.MaNhaCungCap equals ncc.MaNhaCungCap
select new DTO_PhieuNhap
{
MaPhieuNhap = item.MaPhieuNhap,
NgayNhap = item.NgayNhap,
NgayChinhSua = item.NgayChinhSua,
TenNhanVien = nv.UserName,
TenNhaCungCap = ncc.TenNhaCungCap,
TongTien = item.TongTien,
GhiChu = item.GhiChu
})
.Where(res => (from <= res.NgayNhap && res.NgayNhap <= to) ||
(from <= res.NgayChinhSua && res.NgayChinhSua <= to))
.ToList <DTO_PhieuNhap>();
return(result);
}
}
public List <DTO_BaoCaoTonKho> showBC()
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
//var tondau = (from item in db.BaoCaoTonKhoes
// join goods in db.HangHoas on item.MaHangHoa equals goods.MaHangHoa
// select goods.SoLuongTon).ToList();
//var nhap = (from id in db.PhieuNhaps
// join details in db.ChiTietPhieuNhaps on id.MaPhieuNhap equals details.MaPhieuNhap
// where details.MaHangHoa == this.MaHangHoa
// select )
var result = (from item in db.BaoCaoTonKhoes
join goods in db.HangHoas on item.MaHangHoa equals goods.MaHangHoa
select new DTO_BaoCaoTonKho
{
MaBaoCao = item.MaBaoCao,
Thang = item.Thang,
Nam = item.Nam,
TenHangHoa = goods.TenHangHoa,
SoLuongTonDau = item.SoLuongTonDau,
SoLuongNhap = item.SoLuongNhap,
SoLuongXuat = item.SoLuongXuat,
SoLuongTonCuoi = item.SoLuongTonCuoi
}).ToList();
return(result);
}
}
public List <DTO_HangHoa> showHHDKD()
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var result = (from item in db.HangHoas
join lh in db.LoaiHangHoas on item.MaLoaiHangHoa equals lh.MaLoaiHangHoa
where item.TrangThai == true
select new DTO_HangHoa
{
MaHangHoa = item.MaHangHoa,
TenHangHoa = item.TenHangHoa,
GiaBan = item.GiaBan,
GiamGia = item.GiamGia,
SoLuongTon = item.SoLuongTon,
DonViTinh = item.DonViTinh,
MoTa = item.MoTa,
ThongSoKyThuat = item.ThongSoKyThuat,
XuatXu = item.XuatXu,
ThoiGianBaoHang = item.ThoiGianBaoHang,
HinhAnh = item.HinhAnh,
TenLoaiHangHoa = lh.TenLoaiHangHoa,
TrangThai = item.TrangThai,
strTrangThai = item.TrangThai == true ? "Đang kinh doanh" : "Ngừng kinh doanh",
TenModel = item.TenModel
}).ToList <DTO_HangHoa>();
return(result);
}
}
public DTO_HangHoa getHangHoaById(string id)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var result = (from item in db.HangHoas
join lh in db.LoaiHangHoas on item.MaLoaiHangHoa equals lh.MaLoaiHangHoa
where item.MaHangHoa.Equals(id)
select new DTO_HangHoa
{
MaHangHoa = item.MaHangHoa,
TenHangHoa = item.TenHangHoa,
GiaBan = item.GiaBan,
GiamGia = item.GiamGia,
SoLuongTon = item.SoLuongTon,
DonViTinh = item.DonViTinh,
MoTa = item.MoTa,
ThongSoKyThuat = item.ThongSoKyThuat,
XuatXu = item.XuatXu,
ThoiGianBaoHang = item.ThoiGianBaoHang,
HinhAnh = item.HinhAnh,
TrangThai = item.TrangThai,
TenLoaiHangHoa = lh.TenLoaiHangHoa,
TenModel = item.TenModel
}).ToList <DTO_HangHoa>();
if (result.Count > 0)
{
return(result.First());
}
else
{
return(null);
}
}
}
public List <DTO_PhieuChi> searchStrPC(string str)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var result = (from item in db.PhieuChis
join emp in db.NhanViens
on item.MaNhanVien equals emp.MaNhanVien
where item.MaPhieuChi.Contains(str.ToUpper()) ||
emp.TenNhanVien.Contains(str) ||
item.TongTienChi.ToString().Contains(str) ||
item.GhiChu.Contains(str) ||
item.MaPhieuNhap.Contains(str)
select new DTO_PhieuChi
{
MaPhieuChi = item.MaPhieuChi,
NgayChi = item.NgayChi,
TenNhanVien = emp.TenNhanVien,
MaPhieuNhap = item.MaPhieuNhap,
GhiChu = item.GhiChu,
TongTienChi = item.TongTienChi,
NgayChinhSua = item.NgayChinhSua
}).ToList <DTO_PhieuChi>();
return(result);
}
}
public bool SaveOrUpdate(DTO_ChiTietPhieuBanHang obj)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
ChiTietPhieuBanHang chiTietPhieuBanHang = new ChiTietPhieuBanHang();
chiTietPhieuBanHang.MaChiTietPhieuBan = obj.MaChiTietPhieuBan;
chiTietPhieuBanHang.MaPhieuBanHang = obj.MaPhieuBanHang;
chiTietPhieuBanHang.MaHangHoa = obj.MaHangHoa;
chiTietPhieuBanHang.SoLuong = obj.SoLuong;
chiTietPhieuBanHang.Gia = obj.Gia;
chiTietPhieuBanHang.ThanhTien = obj.ThanhTien;
if (db.ChiTietPhieuBanHangs.Any(e => e.MaChiTietPhieuBan == chiTietPhieuBanHang.MaChiTietPhieuBan))
{
db.ChiTietPhieuBanHangs.Attach(chiTietPhieuBanHang);
db.Entry(chiTietPhieuBanHang).State = System.Data.Entity.EntityState.Modified;
if (db.SaveChanges() > 0)
{
return(true);
}
return(false);
}
db.ChiTietPhieuBanHangs.Add(chiTietPhieuBanHang);
if (db.SaveChanges() > 0)
{
return(true);
}
return(false);
}
}
public bool update(DTO_HangHoa obj)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var resultMaLoaiHangHoa = db.Database
.SqlQuery <String>("select MaLoaiHangHoa from dbo.LoaiHangHoa where TenLoaiHangHoa = N'" + obj.TenLoaiHangHoa + "'")
.FirstOrDefault();
HangHoa hanghoa = new HangHoa();
hanghoa.MaHangHoa = obj.MaHangHoa;
hanghoa.TenHangHoa = obj.TenHangHoa;
hanghoa.GiaBan = obj.GiaBan;
hanghoa.GiamGia = obj.GiamGia;
hanghoa.SoLuongTon = obj.SoLuongTon;
hanghoa.DonViTinh = obj.DonViTinh;
hanghoa.MoTa = obj.MoTa;
hanghoa.ThongSoKyThuat = obj.ThongSoKyThuat;
hanghoa.XuatXu = obj.XuatXu;
hanghoa.ThoiGianBaoHang = obj.ThoiGianBaoHang;
hanghoa.HinhAnh = obj.HinhAnh;
hanghoa.MaLoaiHangHoa = resultMaLoaiHangHoa;
hanghoa.TrangThai = obj.TrangThai;
hanghoa.TenModel = obj.TenModel;
db.HangHoas.Attach(hanghoa);
db.Entry(hanghoa).State = System.Data.Entity.EntityState.Modified;
if (db.SaveChanges() > 0)
{
return(true);
}
return(false);
}
}
public bool insert(DTO_PhieuDatHang obj)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var resultMaNhanVien = db.Database
.SqlQuery <String>("select MaNhanVien from dbo.NhanVien where TenNhanVien = N'" + obj.TenNhanVien + "'")
.FirstOrDefault();
var resultMaNhaCungCap = db.Database
.SqlQuery <String>("select MaNhaCungCap from dbo.NhaCungCap where TenNhaCungCap = N'" + obj.TenNhaCungCap + "'")
.FirstOrDefault();
PhieuDatHang phieuDatHang = new PhieuDatHang();
phieuDatHang.MaPhieuDatHang = obj.MaPhieuDatHang;
phieuDatHang.MaNhaCungCap = resultMaNhaCungCap;
phieuDatHang.MaNhanVien = resultMaNhanVien;
phieuDatHang.NgayChinhSua = obj.NgayChinhSua;
phieuDatHang.NgayDat = obj.NgayDat;
phieuDatHang.TongTien = obj.TongTien;
phieuDatHang.GhiChu = obj.GhiChu;
db.PhieuDatHangs.Add(phieuDatHang);
if (db.SaveChanges() > 0)
{
return(true);
}
return(false);
}
}
public List <DTO_PhieuBaoHanh> searchDate(DateTime from, DateTime to)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var result = (from item in db.PhieuBaoHanhs
join nv in db.NhanViens on item.MaNhanVien equals nv.MaNhanVien
join hh in db.HangHoas on item.MaHangHoa equals hh.MaHangHoa
select new DTO_PhieuBaoHanh
{
MaPhieuBaoHanh = item.MaPhieuBaoHanh,
NgayLap = item.NgayLap,
NgayGiao = item.NgayGiao,
TenHangHoa = hh.TenHangHoa,
TenNhanVien = nv.TenNhanVien,
TenKhachHang = item.TenKhachHang,
SoDienThoai = item.SoDienThoai,
TongTien = item.TongTien,
GhiChu = item.GhiChu,
DaGiao = item.DaGiao,
NgayChinhSua = item.NgayChinhSua
})
.Where(res => (from <= res.NgayLap && res.NgayLap <= to) ||
(from <= res.NgayChinhSua && res.NgayChinhSua <= to))
.ToList <DTO_PhieuBaoHanh>();
return(result);
}
}
public void update(DTO_PhieuBaoHanh obj)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var resultMaNhanVien = db.Database
.SqlQuery <String>("select MaNhanVien from dbo.NhanVien where TenNhanVien = N'" + obj.TenNhanVien + "'")
.FirstOrDefault();
var resultMaHangHoa = db.Database
.SqlQuery <String>("select MaHangHoa from dbo.HangHoa where TenHangHoa = N'" + obj.TenHangHoa + "'")
.FirstOrDefault();
PhieuBaoHanh item = new PhieuBaoHanh();
item.MaPhieuBaoHanh = obj.MaPhieuBaoHanh;
item.NgayLap = obj.NgayLap;
item.NgayGiao = obj.NgayGiao;
item.MaNhanVien = resultMaNhanVien;
item.TenKhachHang = obj.TenKhachHang;
item.SoDienThoai = obj.SoDienThoai;
item.TongTien = obj.TongTien;
item.GhiChu = obj.GhiChu;
item.DaGiao = obj.DaGiao;
item.NgayChinhSua = obj.NgayChinhSua;
item.MaHangHoa = resultMaHangHoa;
db.PhieuBaoHanhs.Attach(item);
db.Entry(item).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
public List <DTO_PhieuBaoHanh> search(String str, bool isMergeCondition, bool daGiao)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var result = new List <DTO_PhieuBaoHanh>();
if (!isMergeCondition)
{
result = (from item in db.PhieuBaoHanhs
join nv in db.NhanViens on item.MaNhanVien equals nv.MaNhanVien
join hh in db.HangHoas on item.MaHangHoa equals hh.MaHangHoa
where item.MaPhieuBaoHanh.Contains(str.ToUpper()) ||
nv.TenNhanVien.Contains(str) ||
item.TongTien.ToString().Contains(str) ||
hh.TenHangHoa.Contains(str) ||
item.GhiChu.Contains(str) ||
item.TenKhachHang.Contains(str) ||
item.SoDienThoai.Contains(str) ||
item.MaPhieuBaoHanh.Contains(str)
select new DTO_PhieuBaoHanh
{
MaPhieuBaoHanh = item.MaPhieuBaoHanh,
NgayLap = item.NgayLap,
NgayGiao = item.NgayGiao,
TenHangHoa = hh.TenHangHoa,
TenNhanVien = nv.TenNhanVien,
TenKhachHang = item.TenKhachHang,
SoDienThoai = item.SoDienThoai,
TongTien = item.TongTien,
GhiChu = item.GhiChu,
DaGiao = item.DaGiao,
NgayChinhSua = item.NgayChinhSua
}).ToList <DTO_PhieuBaoHanh>();
}
else
{
result = (from item in db.PhieuBaoHanhs
join nv in db.NhanViens on item.MaNhanVien equals nv.MaNhanVien
join hh in db.HangHoas on item.MaHangHoa equals hh.MaHangHoa
where item.DaGiao.Value == daGiao
select new DTO_PhieuBaoHanh
{
MaPhieuBaoHanh = item.MaPhieuBaoHanh,
NgayLap = item.NgayLap,
NgayGiao = item.NgayGiao,
TenHangHoa = hh.TenHangHoa,
TenNhanVien = nv.TenNhanVien,
TenKhachHang = item.TenKhachHang,
SoDienThoai = item.SoDienThoai,
TongTien = item.TongTien,
GhiChu = item.GhiChu,
DaGiao = item.DaGiao,
NgayChinhSua = item.NgayChinhSua
}).ToList <DTO_PhieuBaoHanh>();
}
return(result);
}
}
public void delete(String id)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
ChiTietPhieuNhap ctpn = (from item in db.ChiTietPhieuNhaps
where item.MaChiTietPhieuNhap == id
select item).SingleOrDefault();
db.ChiTietPhieuNhaps.Remove(ctpn);
db.SaveChanges();
}
}
public void DeleteBC(DTO_BaoCaoTonKho obj)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
BaoCaoTonKho bc = (from item in db.BaoCaoTonKhoes
where item.MaBaoCao == obj.MaBaoCao
select item).SingleOrDefault();
db.BaoCaoTonKhoes.Remove(bc);
db.SaveChanges();
}
}
public void DeletePC(String id)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
PhieuChi nv = (from item in db.PhieuChis
where item.MaPhieuChi == id
select item).SingleOrDefault();
db.PhieuChis.Remove(nv);
db.SaveChanges();
}
}
public void delete(String id)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
PhieuBaoHanh pbh = (from item in db.PhieuBaoHanhs
where item.MaPhieuBaoHanh == id
select item).SingleOrDefault();
db.PhieuBaoHanhs.Remove(pbh);
db.SaveChanges();
}
}
public void DeleteNCC(DTO_NhaCungCap obj)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
NhaCungCap ncc = (from item in db.NhaCungCaps
where item.MaNhaCungCap == obj.MaNhaCungCap
select item).SingleOrDefault();
db.NhaCungCaps.Remove(ncc);
db.SaveChanges();
}
}
public void delete(String id)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
PhieuNhap phieunhap = (from item in db.PhieuNhaps
where item.MaPhieuNhap == id
select item).SingleOrDefault();
db.PhieuNhaps.Remove(phieunhap);
db.SaveChanges();
}
}
public Decimal sumMoneyPC(String importID)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
Decimal res = 0;
var sum = db.Database
.SqlQuery <Decimal>("select TongTien from dbo.PhieuNhap where MaPhieuNhap = '" + importID + "'")
.FirstOrDefault();
res = sum;
return(res);
}
}
public List <DTO_NhapChi> showPN()
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var result = (from item in db.PhieuNhaps
select new DTO_NhapChi
{
MaPhieuNhap = item.MaPhieuNhap
}).ToList();
return(result);
}
}
public List <DTO_HangHoa> showGoods()
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var result = (from item in db.HangHoas
select new DTO_HangHoa
{
MaHangHoa = item.MaHangHoa,
TenHangHoa = item.TenHangHoa
}).ToList();
return(result);
}
}
public List <DTO_LoaiHangHoa> show()
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
var result = (from lhh in db.LoaiHangHoas
select new DTO_LoaiHangHoa
{
MaLoaiHangHoa = lhh.MaLoaiHangHoa,
TenLoaiHangHoa = lhh.TenLoaiHangHoa,
PhanTramLoiNhuan = lhh.PhanTramLoiNhuan
}).ToList <DTO_LoaiHangHoa>();
return(result);
}
}
public void InsertNCC(DTO_NhaCungCap obj)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
this.MaNhaCungCap = obj.MaNhaCungCap;
this.TenNhaCungCap = obj.TenNhaCungCap;
this.Email = obj.Email;
this.DiaChi = obj.DiaChi;
this.SoDienThoai = obj.SoDienThoai;
db.NhaCungCaps.Add(this);
db.SaveChanges();
}
}
public bool DeletePBH(String id)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
PhieuBanHang phieuBanHang = (from item in db.PhieuBanHangs
where item.MaPhieuBanHang == id
select item).SingleOrDefault();
db.PhieuBanHangs.Remove(phieuBanHang);
if (db.SaveChanges() > 0)
{
return(true);
}
return(false);
}
}
public void UpdateNCC(DTO_NhaCungCap obj)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
this.MaNhaCungCap = obj.MaNhaCungCap;
this.TenNhaCungCap = obj.TenNhaCungCap;
this.Email = obj.Email;
this.DiaChi = obj.DiaChi;
this.SoDienThoai = obj.SoDienThoai;
db.NhaCungCaps.Attach(this);
db.Entry(this).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
public bool delete(String id)
{
using (CellphoneComponentEntities db = new CellphoneComponentEntities())
{
ChiTietPhieuDatHang chiTietPhieuDatHang = (from item in db.ChiTietPhieuDatHangs
where item.MaChiTietPhieuDat == id
select item).SingleOrDefault();
db.ChiTietPhieuDatHangs.Remove(chiTietPhieuDatHang);
if (db.SaveChanges() > 0)
{
return(true);
}
return(false);
}
}