public static DataTable LayDanhSachLopHCVaSoLuongHS()
{
List <LopHanhChinh> dsLopHC = LopHCRepo.GetAlls();
List <HocSinh> dsHS = HocSinhRepo.GetAlls();
var dsLopHCVaSoHS = from hs in dsHS
select new
{
hs.LopHC
} into x
group x by new { x.LopHC } into g
select new
{
g.Key.LopHC,
Soluong = g.Count()
};
var dsComplete = dsLopHC.FullOuterJoin(dsLopHCVaSoHS.ToList(), a => a.MaLopHC, b => b.LopHC, (a, b, MaLopHC) =>
new
{
a.MaLopHC,
a.TenLopHC,
a.NienKhoa,
a.TenGiaoVienChuNhiem,
b?.Soluong
});
return(GenericServices.ToDataTable(dsComplete.ToList()));
}
public static DataTable LayDanhSachHocSinh()
{
List <DangKyHoc> DsDkHoc = DKHocRepo.GetAllsCurrent();
List <HoaDon> DsHoaDon = HDRepo.GetAllCurrent();
List <HocSinh> dsHS = HocSinhRepo.GetAlls();
var dsHSvaSoLopDaDangKy = from hs in dsHS
join hd in DsHoaDon on hs.MaHS equals hd.HocSinh
join dk in DsDkHoc on hd.MaHD equals dk.HoaDon
where (dk.NgayDangKy.Month == DateTime.Now.Month && dk.NgayDangKy.Year == DateTime.Now.Year)
select new
{
hs.MaHS,
hd.TinhTrang
} into x
group x by new { x.MaHS } into g
select new
{
g.First().MaHS,
g.First().TinhTrang,
SoluongLopDaDangKy = g.Count()
};
var dsHSList = dsHS.FullOuterJoin(dsHSvaSoLopDaDangKy.ToList(), a => a.MaHS, b => b.MaHS, (a, b, MaHS) =>
new
{
MaHS,
a.TenHS,
a.NgaySinh,
a.LopHanhChinh.TenLopHC,
a.NgayNhapHoc,
a.DiaChi,
a.TenChaMe,
a.SDTChaMe,
b?.SoluongLopDaDangKy,
b?.TinhTrang
}
);
return(GenericServices.ToDataTable(dsHSList.ToList()));
}
public static DataTable LayDanhSachHoaDonTheoNamvaThang(int Nam, int Thang)
{
List <DangKyHoc> DsDkHoc = DkHocRepo.GetAlls();
List <HoaDon> DsHoaDon = HDrepo.GetAlls();
List <HocSinh> dsHS = HsRepo.GetAlls();
List <LopDangKy> dsLDK = LDKRepo.LayDanhSachLopDangKy();
var IdHoaDonvaCount = from hs in dsHS
join hd in DsHoaDon on hs.MaHS equals hd.HocSinh
join dk in DsDkHoc on hd.MaHD equals dk.HoaDon
join ldk in dsLDK on dk.LopDangKy equals ldk.MaLopDangKy
where (dk.NgayDangKy.Month == Thang && dk.NgayDangKy.Year == Nam)
select new
{
hd.MaHD,
hs.MaHS,
hs.TenHS,
hs.NgaySinh,
hs.NgayNhapHoc,
hs.LopHanhChinh.TenLopHC,
ldk.HocPhi,
hd.TinhTrang
} into x
group x by new { x.MaHS } into g
select new
{
MaHocSinh = g.Key.MaHS,
MaHoaDon = g.First().MaHD,
TenHocSinh = g.First().TenHS,
NgaySinhCuaHS = g.First().NgaySinh,
NgayNhapHocCuaHS = g.First().NgayNhapHoc,
TenLopHanhChinh = g.First().TenLopHC,
TinhTrangHocPhi = g.First().TinhTrang,
SoluongLopDangKy = g.Count(),
TongHocPhi = g.Sum(m => m.HocPhi)
};
return(GenericServices.ToDataTable(IdHoaDonvaCount.ToList()));
}
public static DataTable LayDanhSachLopDangKy(string MaHS)
{
List<LopDangKy> dslopDK = LopDangKyRepo.LayDanhSachLopDangKy();
List<HoaDon> dsHD = dsHDRepo.GetAllCurrent();
List<HocSinh> dsHS = hsRepo.GetAlls();
List<DangKyHoc> dsDK = dkhocRepo.GetAllsCurrent();
var dsLDKMaHSDaThamGiaTrongThang = from ldk in dslopDK
join dk in dsDK on ldk.MaLopDangKy equals dk.LopDangKy
join hd in dsHD on dk.HoaDon equals hd.MaHD
join hs in dsHS on hd.HocSinh equals hs.MaHS
where (hs.MaHS == MaHS && (dk.NgayDangKy.Month == DateTime.Now.Month && dk.NgayDangKy.Year == DateTime.Now.Year))
select new
{
ldk.CLB,
ldk.TenLopDangKy,
ldk.LichHoc,
ldk.MaLopDangKy
};
var dsLDKHSChuaDangKy = from ldk in dslopDK
where !(from ldk1 in dsLDKMaHSDaThamGiaTrongThang.ToList()
select ldk1.MaLopDangKy).Contains(ldk.MaLopDangKy)
select ldk;
var dsComplete = from ldk in dsLDKHSChuaDangKy.ToList()
where !dsLDKMaHSDaThamGiaTrongThang.ToList().Any(x => x.LichHoc == ldk.LichHoc) &&
!(dsLDKMaHSDaThamGiaTrongThang.ToList().Any(x => x.CLB == ldk.CLB) && dsLDKMaHSDaThamGiaTrongThang.Any(x => x.TenLopDangKy == ldk.TenLopDangKy))
select new
{
ldk.MaLopDangKy,
ldk.TenLopDangKy,
ldk.CLB1.TenCLB,
ldk.CLB,
ldk.NamHoc,
ldk.HocPhi,
ldk.LichHoc
};
return GenericServices.ToDataTable(dsComplete.ToList());
}