public static List<SearchNXB> LayNXBtheoTimKiem(SearchNXB serach)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<SearchNXB> ListSearch = new List<SearchNXB>();
var _NXB = (from p in model.Nhaxuatban
select p);
if(serach.MaNXB!="")
{
_NXB = _NXB.Where(q => q.MaNXB.Trim().Equals(serach.MaNXB));
}
if(serach.notu!=0&&serach.noden!=0)
{
_NXB = _NXB.Where(q => q.No >= serach.notu && q.No <= serach.noden);
}
else if(serach.notu!=0&&serach.noden==0)
{
_NXB = _NXB.Where(q => q.No >= serach.notu );
}
else if (serach.notu == 0 && serach.noden != 0)
{
_NXB = _NXB.Where(q => q.No <= serach.noden);
}
foreach (var row in _NXB)
{
SearchNXB _Search = new SearchNXB();
_Search.TenNXB = row.TenNXB.Trim();
_Search.MaNXB = row.MaNXB.Trim();
_Search.Duong = row.Diachi.Trim();
_Search.Quan = row.Diachi.Trim();
ListSearch.Add(_Search);
}
return ListSearch;
}
public static bool CapNhatKhiSuachitiet(CTPNsach ctpn, long giatam, int soluongtam)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var Phieunhap = (from p in model.PNsach
where p.MaPN.Equals(ctpn.MaPN)
select p).FirstOrDefault();
var CTphieunhap = (from p in model.CTPNsach
where p.MaPN.Equals(ctpn.MaPN) && p.Masach.Equals(ctpn.Masach)
select p).FirstOrDefault();
var _Sach = (from p in model.Sach
where p.Masach.Equals(ctpn.Masach)
select p).FirstOrDefault();
try
{
CTphieunhap.Soluong = ctpn.Soluong;
CTphieunhap.Gia = ctpn.Gia;
Phieunhap.Tongtien = giatam + CTphieunhap.Gia;
_Sach.SoLuong = soluongtam + CTphieunhap.Soluong;
model.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
public static PNsach layngaydau()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var x = (from p in model.PNsach
select p).FirstOrDefault();
return x;
}
public static int SoluongDaily()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var count = (from p in model.Daily
select p).Count();
return count;
}
public static bool CapNhatKhiXoachitiet(CTPNsach ctpn)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var Phieunhap = (from p in model.PNsach
where p.MaPN.Equals(ctpn.MaPN)
select p).FirstOrDefault();
var CTphieunhap = (from p in model.CTPNsach
where p.MaPN.Equals(ctpn.MaPN) && p.Masach.Equals(ctpn.Masach)
select p).FirstOrDefault();
var _Sach = (from p in model.Sach
where p.Masach.Equals(ctpn.Masach)
select p).FirstOrDefault();
try
{
Phieunhap.Tongtien = Phieunhap.Tongtien - CTphieunhap.Gia;
_Sach.SoLuong = _Sach.SoLuong - CTphieunhap.Soluong;
model.CTPNsach.Remove(CTphieunhap);
model.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
public static List <DSCT> ListSachthu(DateTime tungay, DateTime denngay)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List <DSCT> List = new List <DSCT>();
var _sachthu = from p in model.Phieuthusach
from q in model.TTbansachDL
where p.Ngaythu >= tungay && p.Ngaythu <= denngay
where q.Maphieuthu.Equals(p.Maphieuthu)
group q by q.Masach into g
select new
{
Masach = g.Key,
Tensach = (from p in model.Sach
where p.Masach.Equals(g.Key)
select p.Tensach).FirstOrDefault(),
Tienthu = g.Sum(x => x.Tienthu)
};
foreach (var row in _sachthu)
{
DSCT _dsct = new DSCT();
_dsct.Tensach = row.Tensach;
_dsct.Tienthu = row.Tienthu;
List.Add(_dsct);
}
return(List);
}
public static List <DSCT> ListSachChi(DateTime tungay, DateTime denngay)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List <DSCT> List = new List <DSCT>();
var _sachchi = from p in model.PhieuchiNXB
from q in model.CTphieuchiNXB
where p.Ngay >= tungay && p.Ngay <= denngay
where q.Maphieuchi.Equals(p.Maphieuchi)
group q by q.Masach into g
select new
{
Masach = g.Key,
Tensach = (from p in model.Sach
where p.Masach.Equals(g.Key)
select p.Tensach).FirstOrDefault(),
Tienchi = g.Sum(x => x.Tienchi)
};
foreach (var row in _sachchi)
{
DSCT _dsct = new DSCT();
_dsct.Tensach = row.Tensach;
_dsct.Tienchi = row.Tienchi;
List.Add(_dsct);
}
return(List);
}
public static List <SearchNXB> LayNXBtheoTimKiem(SearchNXB serach)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List <SearchNXB> ListSearch = new List <SearchNXB>();
var _NXB = (from p in model.Nhaxuatban
select p);
if (serach.MaNXB != "")
{
_NXB = _NXB.Where(q => q.MaNXB.Trim().Equals(serach.MaNXB));
}
if (serach.notu != 0 && serach.noden != 0)
{
_NXB = _NXB.Where(q => q.No >= serach.notu && q.No <= serach.noden);
}
else if (serach.notu != 0 && serach.noden == 0)
{
_NXB = _NXB.Where(q => q.No >= serach.notu);
}
else if (serach.notu == 0 && serach.noden != 0)
{
_NXB = _NXB.Where(q => q.No <= serach.noden);
}
foreach (var row in _NXB)
{
SearchNXB _Search = new SearchNXB();
_Search.TenNXB = row.TenNXB.Trim();
_Search.MaNXB = row.MaNXB.Trim();
_Search.Duong = row.Diachi.Trim();
_Search.Quan = row.Diachi.Trim();
ListSearch.Add(_Search);
}
return(ListSearch);
}
public static List<DSCT> ListSachthu(DateTime tungay,DateTime denngay)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<DSCT> List = new List<DSCT>();
var _sachthu = from p in model.Phieuthusach
from q in model.TTbansachDL
where p.Ngaythu >= tungay && p.Ngaythu <= denngay
where q.Maphieuthu.Equals(p.Maphieuthu)
group q by q.Masach into g
select new
{
Masach = g.Key,
Tensach = (from p in model.Sach
where p.Masach.Equals(g.Key)
select p.Tensach).FirstOrDefault(),
Tienthu = g.Sum(x => x.Tienthu)
};
foreach (var row in _sachthu)
{
DSCT _dsct = new DSCT();
_dsct.Tensach = row.Tensach;
_dsct.Tienthu = row.Tienthu;
List.Add(_dsct);
}
return List;
}
public static List<SearchDaily> LayDaiLytheoTimKiem(SearchDaily serach)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<SearchDaily> ListSearch = new List<SearchDaily>();
var _daily = (from p in model.Daily
select p);
if(serach.Madaily!="")
{
_daily = _daily.Where(q => q.Madaily.Trim().Equals(serach.Madaily));
}
if (serach.notu != 0 && serach.noden != 0)
{
_daily = _daily.Where(q => q.No >= serach.notu && q.No <= serach.noden);
}
else if (serach.notu != 0 && serach.noden == 0)
{
_daily = _daily.Where(q => q.No >= serach.notu);
}
else if (serach.notu == 0 && serach.noden != 0)
{
_daily = _daily.Where(q => q.No <= serach.noden);
}
foreach (var row in _daily)
{
SearchDaily _Search = new SearchDaily();
_Search.Tendaily = row.Tendaily.Trim();
_Search.Madaily = row.Madaily.Trim();
_Search.Duong = row.Diachi.Trim();
_Search.Quan = row.Diachi.Trim();
ListSearch.Add(_Search);
}
return ListSearch;
}
public static bool CapNhatKhiXoachitiet(CTPNsach ctpn)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var Phieunhap = (from p in model.PNsach
where p.MaPN.Equals(ctpn.MaPN)
select p).FirstOrDefault();
var CTphieunhap = (from p in model.CTPNsach
where p.MaPN.Equals(ctpn.MaPN) && p.Masach.Equals(ctpn.Masach)
select p).FirstOrDefault();
var _Sach = (from p in model.Sach
where p.Masach.Equals(ctpn.Masach)
select p).FirstOrDefault();
try
{
Phieunhap.Tongtien = Phieunhap.Tongtien - CTphieunhap.Gia;
_Sach.SoLuong = _Sach.SoLuong - CTphieunhap.Soluong;
model.CTPNsach.Remove(CTphieunhap);
model.SaveChanges();
return true;
}
catch
{
return false;
}
}
public static List<DSCT> ListSachChi(DateTime tungay,DateTime denngay)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<DSCT> List=new List<DSCT>();
var _sachchi = from p in model.PhieuchiNXB
from q in model.CTphieuchiNXB
where p.Ngay >= tungay && p.Ngay <= denngay
where q.Maphieuchi.Equals(p.Maphieuchi)
group q by q.Masach into g
select new
{
Masach = g.Key,
Tensach = (from p in model.Sach
where p.Masach.Equals(g.Key)
select p.Tensach).FirstOrDefault(),
Tienchi = g.Sum(x => x.Tienchi)
};
foreach(var row in _sachchi)
{
DSCT _dsct = new DSCT();
_dsct.Tensach = row.Tensach;
_dsct.Tienchi = row.Tienchi;
List.Add(_dsct);
}
return List;
}
public static bool CapNhatKhiSuachitiet(CTPNsach ctpn,long giatam,int soluongtam)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var Phieunhap = (from p in model.PNsach
where p.MaPN.Equals(ctpn.MaPN)
select p).FirstOrDefault();
var CTphieunhap = (from p in model.CTPNsach
where p.MaPN.Equals(ctpn.MaPN) && p.Masach.Equals(ctpn.Masach)
select p).FirstOrDefault();
var _Sach = (from p in model.Sach
where p.Masach.Equals(ctpn.Masach)
select p).FirstOrDefault();
try
{
CTphieunhap.Soluong = ctpn.Soluong;
CTphieunhap.Gia = ctpn.Gia;
Phieunhap.Tongtien = giatam + CTphieunhap.Gia;
_Sach.SoLuong = soluongtam + CTphieunhap.Soluong;
model.SaveChanges();
return true;
}
catch
{
return false;
}
}
public static List<DSCT> DSsachbanduoctheoNXB(string manxb)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<DSCT> List = new List<DSCT>();
var _sachbanduoc = (from p in model.Phieuthusach
from q in model.TTbansachDL
where q.Maphieuthu.Equals(p.Maphieuthu)&&q.TinhTrang.Trim()!="Đã trả nợ"
group q by q.Masach into g
select new
{
Masach = g.Key,
Soluong = g.Sum(q => q.Soluong),
MaNXB=(from p in model.Sach
where p.Masach.Equals(g.Key)
select p.MaNXB).FirstOrDefault(),
Tensach=(from p in model.Sach
where p.Masach.Equals(g.Key)
select p.Tensach).FirstOrDefault()
});
_sachbanduoc = _sachbanduoc.Where(q => q.MaNXB.Equals(manxb));
foreach(var row in _sachbanduoc)
{
DSCT _dsct = new DSCT();
_dsct.Masach = row.Masach;
_dsct.Tensach = row.Tensach;
_dsct.soluong = row.Soluong;
List.Add(_dsct);
}
return List;
}
public static int Soluongphieuchi()
{
DBQLPhatHanhSachEntities moddel = new DBQLPhatHanhSachEntities();
var count = (from p in moddel.PhieuchiNXB
select p).Count();
return count;
}
public static bool CapNhatKhiThemCT(CTphieuchiNXB ctphieuchi)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var _phieuchi = (from p in model.PhieuchiNXB
where p.Maphieuchi.Equals(ctphieuchi.Maphieuchi)
select p).FirstOrDefault();
var _TTbansach = from p in model.TTbansachDL
where p.Masach.Equals(ctphieuchi.Masach)
select p;
try
{
foreach (var row in _TTbansach)
{
row.TinhTrang = "Đã trả nợ";
}
_phieuchi.Tongtien = _phieuchi.Tongtien + ctphieuchi.Tienchi;
model.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
public static List <SearchDaily> LayDaiLytheoTimKiem(SearchDaily serach)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List <SearchDaily> ListSearch = new List <SearchDaily>();
var _daily = (from p in model.Daily
select p);
if (serach.Madaily != "")
{
_daily = _daily.Where(q => q.Madaily.Trim().Equals(serach.Madaily));
}
if (serach.notu != 0 && serach.noden != 0)
{
_daily = _daily.Where(q => q.No >= serach.notu && q.No <= serach.noden);
}
else if (serach.notu != 0 && serach.noden == 0)
{
_daily = _daily.Where(q => q.No >= serach.notu);
}
else if (serach.notu == 0 && serach.noden != 0)
{
_daily = _daily.Where(q => q.No <= serach.noden);
}
foreach (var row in _daily)
{
SearchDaily _Search = new SearchDaily();
_Search.Tendaily = row.Tendaily.Trim();
_Search.Madaily = row.Madaily.Trim();
_Search.Duong = row.Diachi.Trim();
_Search.Quan = row.Diachi.Trim();
ListSearch.Add(_Search);
}
return(ListSearch);
}
public static List <DSCT> DSsachbanduoctheoNXB(string manxb)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List <DSCT> List = new List <DSCT>();
var _sachbanduoc = (from p in model.Phieuthusach
from q in model.TTbansachDL
where q.Maphieuthu.Equals(p.Maphieuthu) && q.TinhTrang.Trim() != "Đã trả nợ"
group q by q.Masach into g
select new
{
Masach = g.Key,
Soluong = g.Sum(q => q.Soluong),
MaNXB = (from p in model.Sach
where p.Masach.Equals(g.Key)
select p.MaNXB).FirstOrDefault(),
Tensach = (from p in model.Sach
where p.Masach.Equals(g.Key)
select p.Tensach).FirstOrDefault()
});
_sachbanduoc = _sachbanduoc.Where(q => q.MaNXB.Equals(manxb));
foreach (var row in _sachbanduoc)
{
DSCT _dsct = new DSCT();
_dsct.Masach = row.Masach;
_dsct.Tensach = row.Tensach;
_dsct.soluong = row.Soluong;
List.Add(_dsct);
}
return(List);
}
public static bool xoaphieuchi(PhieuchiNXB phieuchi)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var _phieuchi = (from p in model.PhieuchiNXB
where p.Maphieuchi.Equals(phieuchi.Maphieuchi)
select p).FirstOrDefault();
var _ctphieuchi = from p in model.CTphieuchiNXB
where p.Maphieuchi.Equals(phieuchi.Maphieuchi)
select p;
try
{
foreach (var row in _ctphieuchi)
{
model.CTphieuchiNXB.Remove(row);
}
model.PhieuchiNXB.Remove(_phieuchi);
model.SaveChanges();
return(true);
}
catch
{
return(false);
}
}
public static int SoluongNXB()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var count = (from p in model.Nhaxuatban
select p).Count();
return(count);
}
public static CTphieuchiNXB layCTPhieuchiTheoma(string maphieuchi)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var _CTphieuchi = (from p in model.CTphieuchiNXB
where p.Maphieuchi.Equals(maphieuchi)
select p).FirstOrDefault();
return _CTphieuchi;
}
public static List<PhieuchiNXB> DSphieuchi()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<PhieuchiNXB> List = new List<PhieuchiNXB>();
var phieuchi = from p in model.PhieuchiNXB
select p;
return List = phieuchi.ToList();
}
public static int Soluongphieuchi()
{
DBQLPhatHanhSachEntities moddel = new DBQLPhatHanhSachEntities();
var count = (from p in moddel.PhieuchiNXB
select p).Count();
return(count);
}
public static List<PNsach> LoadDSPN()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<PNsach> List = new List<PNsach>();
var DSPN = from p in model.PNsach
select p;
return List = DSPN.ToList();
}
public static PNsach LayPNtheoma(string maPN)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var _phieunhap = (from p in model.PNsach
where p.MaPN.Trim().Equals(maPN.Trim())
select p).FirstOrDefault();
return _phieunhap;
}
public static TheLoai LayTheLoaiTheoMa(string MaTheLoai)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var TheLoaiSelected = (from p in model.TheLoai
where p.MaTheLoai.Trim().Equals(MaTheLoai)
select p).FirstOrDefault();
return TheLoaiSelected;
}
public static PNsach layngaydau()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var x = (from p in model.PNsach
select p).FirstOrDefault();
return(x);
}
public static Daily Laydailytheoma(string maDaily)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var DaiLySelected = (from p in model.Daily
where p.Madaily.Trim().Equals(maDaily)
select p).FirstOrDefault();
return DaiLySelected;
}
public static int layTongSLSach()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var count = (from p in model.Sach
select p).Count();
return(count);
}
public static Nhaxuatban LayNXBtheoma(string maNXB)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var NXBSelected = (from p in model.Nhaxuatban
where p.MaNXB.Trim().Equals(maNXB)
select p).FirstOrDefault();
return NXBSelected;
}
public static List<Nhaxuatban> loadNXB()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<Nhaxuatban> List = new List<Nhaxuatban>();
var nxb = from p in model.Nhaxuatban
select p;
return List =nxb.ToList();
}
public static List<Daily> loadDaily()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<Daily> List = new List<Daily>();
var daily = from p in model.Daily
select p;
return List = daily.ToList();
}
public static int SoluongDaily()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var count = (from p in model.Daily
select p).Count();
return(count);
}
public static int TongSLPhieuNhap()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var count = (from p in model.PNsach
select p).Count();
return(count);
}
public static Nhaxuatban LayNXBtheoma(string maNXB)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var NXBSelected = (from p in model.Nhaxuatban
where p.MaNXB.Trim().Equals(maNXB)
select p).FirstOrDefault();
return(NXBSelected);
}
public static List <PNsach> LoadDSPN()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List <PNsach> List = new List <PNsach>();
var DSPN = from p in model.PNsach
select p;
return(List = DSPN.ToList());
}
public static PNsach LayPNtheoma(string maPN)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var _phieunhap = (from p in model.PNsach
where p.MaPN.Trim().Equals(maPN.Trim())
select p).FirstOrDefault();
return(_phieunhap);
}
public static List <SearchPhieuNhapPhieuChi> LayPNtheoTimkiem(SearchPhieuNhapPhieuChi Search)
{
List <SearchPhieuNhapPhieuChi> ListSearch = new List <SearchPhieuNhapPhieuChi>();
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var DSPN = (from p in model.PNsach
select p);
if (Search.MaPN != "")
{
DSPN = DSPN.Where(q => q.MaPN.Trim().Equals(Search.MaPN));
}
if (Search.MaNXB != "")
{
DSPN = DSPN.Where(q => q.MaNXB.Trim().Equals(Search.MaNXB));
}
if (Search.TinhTrang != "")
{
DSPN = DSPN.Where(q => q.TinhTrang.Trim().Equals(Search.TinhTrang));
}
if (Search.Ngaytu != null && Search.Ngayden != null)
{
if (Search.Ngaytu.ToString() != Search.Ngayden.ToString())
{
DSPN = DSPN.Where(q => q.Ngaynhap >= Search.Ngaytu && q.Ngaynhap <= Search.Ngayden);
}
else
{
DSPN = DSPN.Where(q => q.Ngaynhap == Search.Ngaytu);
}
}
if (Search.tiennhaptu != 0 && Search.tiennhapden != 0)
{
DSPN = DSPN.Where(q => q.Tongtien >= Search.tiennhaptu && q.Tongtien <= Search.tiennhapden);
}
if (Search.tiennhaptu != 0 && Search.tiennhapden == 0)
{
DSPN = DSPN.Where(q => q.Tongtien >= Search.tiennhaptu);
}
if (Search.tiennhaptu == 0 && Search.tiennhapden != 0)
{
DSPN = DSPN.Where(q => q.Tongtien <= Search.tiennhapden);
}
if (Search.tiennhaptu == Search.tiennhapden && Search.tiennhaptu != 0 && Search.tiennhapden != 0)
{
DSPN = DSPN.Where(q => q.Tongtien == Search.tiennhaptu);
}
foreach (var row in DSPN)
{
SearchPhieuNhapPhieuChi _search = new SearchPhieuNhapPhieuChi();
_search.Ngaynhap = row.Ngaynhap;
_search.TinhTrang = row.TinhTrang;
_search.Tongtien = row.Tongtien;
_search.MaPN = row.MaPN;
ListSearch.Add(_search);
}
return(ListSearch);
}
public static Sach LaySachTheoMa(string masach)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var SachSelected = (from p in model.Sach
where p.Masach.Trim().Equals(masach)
select p).FirstOrDefault();
return(SachSelected);
}
public static CTPNsach layCTPN(CTPNsach ctpn)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var CTphieunhap = (from p in model.CTPNsach
where p.MaPN.Equals(ctpn.MaPN) && p.Masach.Equals(ctpn.Masach)
select p).FirstOrDefault();
return(CTphieunhap);
}
public static List <Sach> LayDSSach()
{
List <Sach> List = new List <Sach>();
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var sach = from p in model.Sach
select p;
return(List = sach.ToList());;
}
public static CTPNsach layCTPNtheoma(string MaPN)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var _CTPN = (from p in model.CTPNsach
where p.MaPN.Trim().Equals(MaPN)
select p).FirstOrDefault();
return(_CTPN);
}
public static List<TheLoai> DsTheLoai()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<TheLoai> List=new List<TheLoai>();
var _theloai = from p in model.TheLoai
select p;
List = _theloai.ToList();
return List;
}
public static List <Daily> loadDaily()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List <Daily> List = new List <Daily>();
var daily = from p in model.Daily
select p;
return(List = daily.ToList());
}
public static Daily Laydailytheoma(string maDaily)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var DaiLySelected = (from p in model.Daily
where p.Madaily.Trim().Equals(maDaily)
select p).FirstOrDefault();
return(DaiLySelected);
}
public static TheLoai LayTheLoaiTheoMa(string MaTheLoai)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var TheLoaiSelected = (from p in model.TheLoai
where p.MaTheLoai.Trim().Equals(MaTheLoai)
select p).FirstOrDefault();
return(TheLoaiSelected);
}
public static PhieuchiNXB layphieuchitheoma(string maphieuchi)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var _phieuchi = (from p in model.PhieuchiNXB
where p.Maphieuchi.Equals(maphieuchi)
select p).FirstOrDefault();
return(_phieuchi);
}
public static List <PhieuchiNXB> DSphieuchi()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List <PhieuchiNXB> List = new List <PhieuchiNXB>();
var phieuchi = from p in model.PhieuchiNXB
select p;
return(List = phieuchi.ToList());
}
public static List <SearchPhieuNhapPhieuChi> LayPhieuchitheoTimkiem(SearchPhieuNhapPhieuChi Search)
{
List <SearchPhieuNhapPhieuChi> ListSearch = new List <SearchPhieuNhapPhieuChi>();
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var DSPhieuchi = (from p in model.PhieuchiNXB
select p);
if (Search.Maphieuchi != "")
{
DSPhieuchi = DSPhieuchi.Where(q => q.Maphieuchi.Trim().Equals(Search.Maphieuchi));
}
if (Search.MaNXB != "")
{
DSPhieuchi = DSPhieuchi.Where(q => q.MaNXB.Trim().Equals(Search.MaNXB));
}
if (Search.TinhTrang != "")
{
DSPhieuchi = DSPhieuchi.Where(q => q.TrangThai.Trim().Equals(Search.TinhTrang));
}
if (Search.Ngaytu != null && Search.Ngayden != null)
{
if (Search.Ngaytu.ToString() != Search.Ngayden.ToString())
{
DSPhieuchi = DSPhieuchi.Where(q => q.Ngay >= Search.Ngaytu && q.Ngay <= Search.Ngayden);
}
else
{
DSPhieuchi = DSPhieuchi.Where(q => q.Ngay == Search.Ngaytu);
}
}
if (Search.tiennhaptu != 0 && Search.tiennhapden != 0)
{
DSPhieuchi = DSPhieuchi.Where(q => q.Tongtien >= Search.tiennhaptu && q.Tongtien <= Search.tiennhapden);
}
if (Search.tiennhaptu != 0 && Search.tiennhapden == 0)
{
DSPhieuchi = DSPhieuchi.Where(q => q.Tongtien >= Search.tiennhaptu);
}
if (Search.tiennhaptu == 0 && Search.tiennhapden != 0)
{
DSPhieuchi = DSPhieuchi.Where(q => q.Tongtien <= Search.tiennhapden);
}
if (Search.tiennhaptu == Search.tiennhapden && Search.tiennhaptu != 0 && Search.tiennhapden != 0)
{
DSPhieuchi = DSPhieuchi.Where(q => q.Tongtien == Search.tiennhaptu);
}
foreach (var row in DSPhieuchi)
{
SearchPhieuNhapPhieuChi _search = new SearchPhieuNhapPhieuChi();
_search.Ngay = row.Ngay;
_search.TrangThai = row.TrangThai;
_search.Tongtien = row.Tongtien;
_search.Maphieuchi = row.Maphieuchi;
ListSearch.Add(_search);
}
return(ListSearch);
}
public static List <Nhaxuatban> loadNXB()
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List <Nhaxuatban> List = new List <Nhaxuatban>();
var nxb = from p in model.Nhaxuatban
select p;
return(List = nxb.ToList());
}
public static List<DTOSach>LaySachTheoTimKiem(DTOSach Search)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<DTOSach> ListSearch=new List<DTOSach>();
var DSSach = (from p in model.Sach
select p);
if(Search.Masach!="")
{
DSSach = DSSach.Where(q => q.Masach.Trim().Equals(Search.Masach));
}
if(Search.MaNXB!="")
{
DSSach = DSSach.Where(q => q.MaNXB.Trim().Equals(Search.MaNXB));
}
if(Search.MaTheLoai!="")
{
DSSach = DSSach.Where(q => q.MaTheLoai.Trim().Equals(Search.MaTheLoai));
}
//giá bán
if (Search.giatu != 0 && Search.giaden != 0)
{
DSSach = DSSach.Where(q => q.GiaBan >= Search.giatu && q.GiaBan <= Search.giaden);
}
if (Search.giatu != 0 && Search.giaden == 0)
{
DSSach = DSSach.Where(q => q.GiaBan >= Search.giatu);
}
if (Search.giatu == 0 && Search.giaden != 0)
{
DSSach = DSSach.Where(q => q.GiaBan <= Search.giaden);
}
//giá nhập
if (Search.gianhaptu != 0 && Search.gianhapden != 0)
{
DSSach = DSSach.Where(q => q.GiaNhap >= Search.gianhaptu && q.GiaNhap <= Search.gianhapden);
}
if (Search.gianhaptu != 0 && Search.gianhapden == 0)
{
DSSach = DSSach.Where(q => q.GiaNhap >= Search.gianhaptu);
}
if (Search.gianhaptu == 0 && Search.gianhapden != 0)
{
DSSach = DSSach.Where(q => q.GiaNhap <= Search.gianhapden);
}
foreach(var row in DSSach)
{
DTOSach _SearchSach = new DTOSach();
_SearchSach.Masach = row.Masach;
_SearchSach.Tensach = row.Tensach;
ListSearch.Add(_SearchSach);
}
return ListSearch;
}
public static List<SearchPhieuNhapPhieuChi> LayPhieuchitheoTimkiem(SearchPhieuNhapPhieuChi Search)
{
List<SearchPhieuNhapPhieuChi> ListSearch = new List<SearchPhieuNhapPhieuChi>();
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var DSPhieuchi = (from p in model.PhieuchiNXB
select p);
if(Search.Maphieuchi!="")
{
DSPhieuchi = DSPhieuchi.Where(q => q.Maphieuchi.Trim().Equals(Search.Maphieuchi));
}
if (Search.MaNXB != "")
{
DSPhieuchi = DSPhieuchi.Where(q => q.MaNXB.Trim().Equals(Search.MaNXB));
}
if (Search.TinhTrang != "")
{
DSPhieuchi = DSPhieuchi.Where(q => q.TrangThai.Trim().Equals(Search.TinhTrang));
}
if (Search.Ngaytu != null && Search.Ngayden != null)
{
if (Search.Ngaytu.ToString() != Search.Ngayden.ToString())
DSPhieuchi = DSPhieuchi.Where(q => q.Ngay >= Search.Ngaytu && q.Ngay <= Search.Ngayden);
else DSPhieuchi = DSPhieuchi.Where(q => q.Ngay == Search.Ngaytu);
}
if (Search.tiennhaptu != 0 && Search.tiennhapden != 0)
{
DSPhieuchi = DSPhieuchi.Where(q => q.Tongtien >= Search.tiennhaptu && q.Tongtien <= Search.tiennhapden);
}
if (Search.tiennhaptu != 0 && Search.tiennhapden == 0)
{
DSPhieuchi = DSPhieuchi.Where(q => q.Tongtien >= Search.tiennhaptu);
}
if (Search.tiennhaptu == 0 && Search.tiennhapden != 0)
{
DSPhieuchi = DSPhieuchi.Where(q => q.Tongtien <= Search.tiennhapden);
}
if (Search.tiennhaptu == Search.tiennhapden && Search.tiennhaptu != 0 && Search.tiennhapden != 0)
{
DSPhieuchi = DSPhieuchi.Where(q => q.Tongtien == Search.tiennhaptu);
}
foreach (var row in DSPhieuchi)
{
SearchPhieuNhapPhieuChi _search = new SearchPhieuNhapPhieuChi();
_search.Ngay = row.Ngay;
_search.TrangThai = row.TrangThai;
_search.Tongtien = row.Tongtien;
_search.Maphieuchi = row.Maphieuchi;
ListSearch.Add(_search);
}
return ListSearch;
}
public static List<SearchPhieuNhapPhieuChi> LayPNtheoTimkiem(SearchPhieuNhapPhieuChi Search)
{
List<SearchPhieuNhapPhieuChi> ListSearch = new List<SearchPhieuNhapPhieuChi>();
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var DSPN = (from p in model.PNsach
select p);
if(Search.MaPN!="")
{
DSPN = DSPN.Where(q => q.MaPN.Trim().Equals(Search.MaPN));
}
if(Search.MaNXB!="")
{
DSPN = DSPN.Where(q => q.MaNXB.Trim().Equals(Search.MaNXB));
}
if(Search.TinhTrang!="")
{
DSPN = DSPN.Where(q => q.TinhTrang.Trim().Equals(Search.TinhTrang));
}
if(Search.Ngaytu!=null&&Search.Ngayden!=null)
{
if (Search.Ngaytu.ToString() != Search.Ngayden.ToString())
DSPN = DSPN.Where(q => q.Ngaynhap >= Search.Ngaytu && q.Ngaynhap <= Search.Ngayden);
else DSPN = DSPN.Where(q => q.Ngaynhap == Search.Ngaytu);
}
if(Search.tiennhaptu!=0&&Search.tiennhapden!=0)
{
DSPN = DSPN.Where(q => q.Tongtien >= Search.tiennhaptu && q.Tongtien <= Search.tiennhapden);
}
if(Search.tiennhaptu!=0&&Search.tiennhapden==0)
{
DSPN = DSPN.Where(q => q.Tongtien >= Search.tiennhaptu);
}
if(Search.tiennhaptu==0&&Search.tiennhapden!=0)
{
DSPN = DSPN.Where(q => q.Tongtien <= Search.tiennhapden);
}
if(Search.tiennhaptu==Search.tiennhapden&&Search.tiennhaptu!=0&&Search.tiennhapden!=0)
{
DSPN = DSPN.Where(q => q.Tongtien == Search.tiennhaptu);
}
foreach(var row in DSPN)
{
SearchPhieuNhapPhieuChi _search = new SearchPhieuNhapPhieuChi();
_search.Ngaynhap = row.Ngaynhap;
_search.TinhTrang = row.TinhTrang;
_search.Tongtien = row.Tongtien;
_search.MaPN = row.MaPN;
ListSearch.Add(_search);
}
return ListSearch;
}
public static bool AddNXB(Nhaxuatban nxb)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
try
{
model.Nhaxuatban.Add(nxb);
model.SaveChanges();
return true;
}
catch
{
return false;
}
}
public static bool AddSach(Sach sach)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
try
{
model.Sach.Add(sach);
model.SaveChanges();
return true;
}
catch
{
return false;
}
}
public static bool AddDaily(Daily daily)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
try
{
model.Daily.Add(daily);
model.SaveChanges();
return true;
}
catch
{
return false;
}
}
public static bool SuaPhieuNhap(PNsach pnsach)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var _pnsach = (from p in model.PNsach
where p.MaPN.Trim().Equals(pnsach.MaPN.Trim())
select p).FirstOrDefault();
if (_pnsach == null)
return false;
else
{
_pnsach.MaNXB = pnsach.MaNXB;
_pnsach.Ngaynhap = pnsach.Ngaynhap;
_pnsach.Nguoigiao = pnsach.Nguoigiao;
_pnsach.TinhTrang = pnsach.TinhTrang;
model.SaveChanges();
return true;
}
}
public static List<DSCT> layDSCT(string maphieuchi)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<DSCT> List=new List<DSCT>();
var DSCT = from p in model.CTphieuchiNXB
where p.Maphieuchi.Equals(maphieuchi)
select new { p.Masach,p.Tienchi,p.Soluong,Tensach=(from q in model.Sach
where q.Masach.Equals(p.Masach)
select q.Tensach).FirstOrDefault()};
foreach(var row in DSCT)
{
DSCT _dsct = new DSCT();
_dsct.Masach = row.Masach;
_dsct.Tensach = row.Tensach;
_dsct.soluong = row.Soluong;
_dsct.Tienchi = row.Tienchi;
List.Add(_dsct);
}
return List;
}
public static bool CapNhatKhiThemCT(CTphieuchiNXB ctphieuchi)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
var _phieuchi = (from p in model.PhieuchiNXB
where p.Maphieuchi.Equals(ctphieuchi.Maphieuchi)
select p).FirstOrDefault();
var _TTbansach = from p in model.TTbansachDL
where p.Masach.Equals(ctphieuchi.Masach)
select p;
try
{
foreach(var row in _TTbansach)
{
row.TinhTrang = "Đã trả nợ";
}
_phieuchi.Tongtien = _phieuchi.Tongtien + ctphieuchi.Tienchi;
model.SaveChanges();
return true;
}
catch
{
return false;
}
}
public static List<DTOSach> ThongKe(string MaDaiLy,DateTime? Tungay,DateTime? Dengay)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<DTOSach> List=new List<DTOSach>();
if (Tungay != null && Dengay != null&& MaDaiLy=="")
{
var sachbanduoc = (from p in model.Phieuthusach
from q in model.TTbansachDL
where p.Ngaythu >= Tungay && p.Ngaythu <= Dengay
where q.Maphieuthu.Equals(p.Maphieuthu)
group q by q.Masach into g
select new
{
Masach = g.Key,
Tensach = (from p in model.Sach
where p.Masach.Equals(g.Key)
select p.Tensach).FirstOrDefault(),
soluong = g.Sum(x => x.Soluong)
});
foreach(var row in sachbanduoc)
{
DTOSach _tk = new DTOSach();
_tk.Tensach = row.Tensach;
_tk.soluong = row.soluong;
List.Add(_tk);
}
}
else if(MaDaiLy!=null&&Tungay==null&&Dengay==null)
{
var sachbanduoc = (from p in model.Phieuthusach
from q in model.TTbansachDL
where p.Madaily.Equals(MaDaiLy)
where q.Maphieuthu.Equals(p.Maphieuthu)
group q by q.Masach into g
select new
{
Masach = g.Key,
Tensach = (from p in model.Sach
where p.Masach.Equals(g.Key)
select p.Tensach).FirstOrDefault(),
soluong = g.Sum(x => x.Soluong)
});
foreach (var row in sachbanduoc)
{
DTOSach _tk = new DTOSach();
_tk.Tensach = row.Tensach;
_tk.soluong = row.soluong;
List.Add(_tk);
}
}
else if(MaDaiLy!=null&&Tungay!=null&&Dengay!=null)
{
var sachbanduoc = (from p in model.Phieuthusach
from q in model.TTbansachDL
where p.Madaily.Equals(MaDaiLy)
where p.Ngaythu >= Tungay && p.Ngaythu <= Dengay
where q.Maphieuthu.Equals(p.Maphieuthu)
group q by q.Masach into g
select new
{
Masach = g.Key,
Tensach = (from p in model.Sach
where p.Masach.Equals(g.Key)
select p.Tensach).FirstOrDefault(),
soluong = g.Sum(x => x.Soluong)
});
foreach (var row in sachbanduoc)
{
DTOSach _tk = new DTOSach();
_tk.Tensach = row.Tensach;
_tk.soluong = row.soluong;
List.Add(_tk);
}
}
return List;
}
