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<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<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;
}
public BUSSach(DTOSach Search)
{
this.Search = Search;
}
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);
}