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<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> 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 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 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 <DSCT> LayDSCT(string maPN)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List <DSCT> List = new List <DSCT>();
var DSCT = from p in model.CTPNsach
where p.MaPN.Trim().Equals(maPN)
select new { p.Masach, Tensach = (from q in model.Sach
where q.Masach.Trim().Equals(p.Masach)
select q.Tensach).FirstOrDefault(), p.Soluong, p.Gia };
foreach (var row in DSCT)
{
DSCT _dsct = new DSCT();
_dsct.Masach = row.Masach;
_dsct.Tensach = row.Tensach;
_dsct.soluong = row.Soluong;
_dsct.Gia = row.Gia;
List.Add(_dsct);
}
return(List);
}
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 List <DSCT> ThongKe(DateTime tungay, DateTime denngay)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List <DSCT> List = new List <DSCT>();
var _Soluongsachnhap = from p in model.PNsach
from q in model.CTPNsach
where p.Ngaynhap >= tungay && p.Ngaynhap <= denngay && q.MaPN.Equals(p.MaPN)
group q by q.Masach into g
select new
{
Masach = g.Key,
soluong = g.Sum(q => q.Soluong),
Tensach = (from x in model.Sach
where x.Masach.Equals(g.Key)
select x.Tensach).FirstOrDefault()
};
var _Soluongsachxuat = from p in model.PXsach
from q in model.CTPXsach
where p.Ngayxuat >= tungay && p.Ngayxuat <= denngay && q.MaPX.Equals(p.MaPX)
group q by q.Masach into g
select new
{
Masach = g.Key,
soluong = g.Sum(q => q.Soluong),
Tensach = (from x in model.Sach
where x.Masach.Equals(g.Key)
select x.Tensach).FirstOrDefault()
};
var _Soluongcon = from p in _Soluongsachnhap
from q in _Soluongsachxuat
where p.Masach.Equals(q.Masach)
select new
{
p.Masach,
soluong = p.soluong - q.soluong,
Tensach = (from x in model.Sach
where x.Masach.Equals(p.Masach)
select x.Tensach).FirstOrDefault()
};
var _Soluongchuaxuat = from p in _Soluongsachnhap
where !_Soluongsachxuat.Any(x => (x.Masach == p.Masach))
select new
{
p.Masach,
soluong = p.soluong,
Tensach = (from q in model.Sach
where q.Masach.Equals(p.Masach)
select q.Tensach).FirstOrDefault()
};
foreach (var row in _Soluongcon)
{
DSCT _dsct = new DSCT();
_dsct.Masach = row.Masach;
_dsct.Tensach = row.Tensach;
_dsct.soluong = row.soluong;
List.Add(_dsct);
}
foreach (var row in _Soluongchuaxuat)
{
DSCT _dsct = new DSCT();
_dsct.Masach = row.Masach;
_dsct.Tensach = row.Tensach;
_dsct.soluong = row.soluong;
List.Add(_dsct);
}
return(List);
}
public static List<DSCT> LayDSCT(string maPN)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<DSCT> List = new List<DSCT>();
var DSCT = from p in model.CTPNsach
where p.MaPN.Trim().Equals(maPN)
select new { p.Masach,Tensach=(from q in model.Sach
where q.Masach.Trim().Equals(p.Masach)
select q.Tensach).FirstOrDefault(),p.Soluong,p.Gia};
foreach(var row in DSCT)
{
DSCT _dsct = new DSCT();
_dsct.Masach = row.Masach;
_dsct.Tensach = row.Tensach;
_dsct.soluong = row.Soluong;
_dsct.Gia = row.Gia;
List.Add(_dsct);
}
return List;
}
public static List<DSCT> ThongKe(DateTime tungay, DateTime denngay)
{
DBQLPhatHanhSachEntities model = new DBQLPhatHanhSachEntities();
List<DSCT> List = new List<DSCT>();
var _Soluongsachnhap = from p in model.PNsach
from q in model.CTPNsach
where p.Ngaynhap >= tungay && p.Ngaynhap <= denngay && q.MaPN.Equals(p.MaPN)
group q by q.Masach into g
select new
{
Masach = g.Key,
soluong = g.Sum(q => q.Soluong),
Tensach = (from x in model.Sach
where x.Masach.Equals(g.Key)
select x.Tensach).FirstOrDefault()
};
var _Soluongsachxuat = from p in model.PXsach
from q in model.CTPXsach
where p.Ngayxuat >= tungay && p.Ngayxuat <= denngay && q.MaPX.Equals(p.MaPX)
group q by q.Masach into g
select new
{
Masach = g.Key,
soluong = g.Sum(q => q.Soluong),
Tensach = (from x in model.Sach
where x.Masach.Equals(g.Key)
select x.Tensach).FirstOrDefault()
};
var _Soluongcon = from p in _Soluongsachnhap
from q in _Soluongsachxuat
where p.Masach.Equals(q.Masach)
select new
{
p.Masach,
soluong = p.soluong - q.soluong,
Tensach = (from x in model.Sach
where x.Masach.Equals(p.Masach)
select x.Tensach).FirstOrDefault()
};
var _Soluongchuaxuat = from p in _Soluongsachnhap
where !_Soluongsachxuat.Any(x => (x.Masach == p.Masach))
select new
{
p.Masach,
soluong = p.soluong,
Tensach = (from q in model.Sach
where q.Masach.Equals(p.Masach)
select q.Tensach).FirstOrDefault()
};
foreach (var row in _Soluongcon)
{
DSCT _dsct = new DSCT();
_dsct.Masach = row.Masach;
_dsct.Tensach = row.Tensach;
_dsct.soluong = row.soluong;
List.Add(_dsct);
}
foreach (var row in _Soluongchuaxuat)
{
DSCT _dsct = new DSCT();
_dsct.Masach = row.Masach;
_dsct.Tensach = row.Tensach;
_dsct.soluong = row.soluong;
List.Add(_dsct);
}
return List;
}
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;
}