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 LayDanhSachHocSinhDuDKGiaHanDangKyHoc()
{
List <HoaDon> dsHD = HDRepo.GetAlls();
List <HocSinh> dsHS = HocSinhRepo.GetAlls();
List <DangKyHoc> dsDK = DKHocRepo.GetAlls();
var dsHSDKThangTruoc = (from hs in dsHS
join hd in dsHD on hs.MaHS equals hd.HocSinh
join dk in dsDK on hd.MaHD equals dk.HoaDon
where (dk.NgayDangKy.Month == (DateTime.Today.AddMonths(-1).Month) && dk.NgayDangKy.Year == (DateTime.Today.AddMonths(-1).Year))
select hs).Distinct();
var dsHSDKThangNay = from hs in dsHS
join hd in dsHD on hs.MaHS equals hd.HocSinh
join dk in dsDK on hd.MaHD equals dk.HoaDon
where (dk.NgayDangKy.Month == DateTime.Now.Month && dk.NgayDangKy.Year == DateTime.Now.Year)
select hs;
var dsHSDuDkGiaHan = from hs in dsHSDKThangTruoc.ToList()
where !(from hs1 in dsHSDKThangNay.ToList()
select hs1.MaHS).Contains(hs.MaHS)
select new
{
hs.MaHS,
hs.TenHS,
hs.NgaySinh,
hs.LopHanhChinh.TenLopHC,
hs.NgayNhapHoc,
hs.DiaChi,
hs.TenChaMe,
hs.SDTChaMe
};
return(GenericServices.ToDataTable(dsHSDuDkGiaHan.ToList()));
}
public static DataTable cacLopcoHocSinhdangKy()
{
List<DangKyHoc> dsdkHoc = dkhocRepo.GetAllsCurrent();
List<LopDangKy> dslDK = LopDangKyRepo.LayDanhSachLopDangKy();
var IDlopVaCount = from Dk in dsdkHoc
group Dk by new
{
ID = Dk.LopDangKy,
Thang = Dk.NgayDangKy.Month,
Nam = Dk.NgayDangKy.Year
}
into g
where (g.Key.Thang == DateTime.Now.Month && g.Key.Nam == DateTime.Now.Year)
select new
{
ID = g.Key.ID,
Count = g.Count()
};
var dsLop = from IDLop in IDlopVaCount.ToList()
join Lop in dslDK on IDLop.ID equals Lop.MaLopDangKy
select new
{
IDLop.ID,
Lop.TenLopDangKy,
Lop.CLB1.TenCLB,
Lop.LichHoc,
Lop.NamHoc,
IDLop.Count,
};
return GenericServices.ToDataTable(dsLop.ToList());
}
//lấy danh sách học sinh theo năm và tháng
public static DataTable DanhsachLopDangKyVaSoHocSinh(int Nam, int Thang)
{
List<DangKyHoc> dsdkHoc = dkhocRepo.GetAlls();
List<LopDangKy> dslDK = LopDangKyRepo.LayDanhSachLopDangKy();
var IDlopVaCount = from Dk in dsdkHoc
group Dk by new
{
ID = Dk.LopDangKy,
Thang = Dk.NgayDangKy.Month,
Nam = Dk.NgayDangKy.Year
}
into g
where (g.Key.Thang == Thang && g.Key.Nam == Nam)
select new
{
ID = g.Key.ID,
Count = g.Count()
};
var dsLop = from IDLop in IDlopVaCount.ToList()
join Lop in dslDK on IDLop.ID equals Lop.MaLopDangKy
select new
{
//IDLop.ID,
TenVaMaLop = Lop.MaLopDangKy +" - "+ Lop.TenLopDangKy + " Học thứ " + Lop.LichHoc,
//Lop.CLB1.TenCLB,
//Lop.LichHoc,
//Lop.NamHoc,
IDLop.Count,
};
return GenericServices.ToDataTable(dsLop.ToList());
}
public static DataTable LayDanhSachHoaDonTheoMaHocSinh(string MaHS)
{
var dsHS = from hs in HsRepo.GetAlls()
join hd in HDrepo.GetAlls() on hs.MaHS equals hd.HocSinh
where (hd.NgayTao.Value.Month == DateTime.Now.Month && hd.NgayTao.Value.Year == DateTime.Now.Year && MaHS == hs.MaHS)
select new
{
hd.MaHD,
hd.NgayTao,
hd.TinhTrang
};
return(GenericServices.ToDataTable(dsHS.ToList()));
}
public static DataTable LayDanhSachToanBoLopDangKy()
{
List<LopDangKy> dsLopDangKyFromDataBase = LopDangKyRepo.LayDanhSachLopDangKy();
var dsLopDangKy = from ldk in dsLopDangKyFromDataBase
select new
{
ldk.MaLopDangKy,
ldk.TenLopDangKy,
ldk.HocPhi,
ldk.LichHoc,
ldk.CLB1.TenCLB,
ldk.CLB,
ldk.NamHoc
};
return GenericServices.ToDataTable(dsLopDangKy.ToList());
}
public static DataTable GetAlls()
{
var dsHS = from hs in HocSinhRepo.GetAlls()
select new
{
hs.MaHS,
hs.TenHS,
hs.NgaySinh,
hs.NgayNhapHoc,
hs.LopHanhChinh.TenLopHC,
hs.TenChaMe,
hs.DiaChi,
hs.SDTChaMe
};
return(GenericServices.ToDataTable(dsHS.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 LayHocSinhDaDangKyTheoMaLopDangKy(string MaLopDangKy)
{
List <HoaDon> dsHD = HDRepo.GetAllCurrent();
List <HocSinh> dsHS = HocSinhRepo.GetAlls();
List <DangKyHoc> dsDK = DKHocRepo.GetAllsCurrent();
var dsHSDaDK = (from hs in dsHS
join hd in dsHD on hs.MaHS equals hd.HocSinh
join dk in dsDK on hd.MaHD equals dk.HoaDon
where (hd.NgayTao.Value.Month == DateTime.Now.Month && hd.NgayTao.Value.Year == DateTime.Now.Year && MaLopDangKy == dk.LopDangKy)
select new
{
hs.MaHS,
hs.TenHS,
hs.NgaySinh,
hs.LopHanhChinh.TenLopHC,
hs.NgayNhapHoc,
hs.DiaChi,
hs.TenChaMe,
hs.SDTChaMe
}).Distinct();
return(GenericServices.ToDataTable(dsHSDaDK.ToList()));
}
public static DataTable LayDanhCacLopDangKyCuaMotHocSinh(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.MaLopDangKy,
ldk.TenLopDangKy,
ldk.CLB1.TenCLB,
ldk.CLB,
ldk.NamHoc,
ldk.HocPhi,
ldk.LichHoc
};
return GenericServices.ToDataTable(dsLDKMaHSDaThamGiaTrongThang.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());
}
public static DataTable DanhSachCLBvaSoHocSinh(int Nam, int Thang)
{
List <DangKyHoc> dsDKHoc = dkHocRepo.GetAlls();
List <LopDangKy> dsLopDK = LopDangKyRepo.LayDanhSachLopDangKy();
List <CLB> dsCLB = CLBRepo.GetAlls();
var MaCLBvaCount = from dk in dsDKHoc
join ldk in dsLopDK on dk.LopDangKy equals ldk.MaLopDangKy
join clb in dsCLB on ldk.CLB equals clb.MaCLB
group clb by new
{
ID = clb.MaCLB + " - " + clb.TenCLB,
Thang = dk.NgayDangKy.Month,
Nam = dk.NgayDangKy.Year
}
into g
where (g.Key.Thang == Thang && g.Key.Nam == Nam)
select new
{
ID = g.Key.ID,
Count = g.Count()
};
return(GenericServices.ToDataTable(MaCLBvaCount.ToList()));
}