public static List<rptSinavGorevlendirme> GetKatilimOgretmenleri(int SinavOturumId)
{
using (SGContext db = new SGContext())
{
var list = (from grv in db.SinavGorevli
join otrm in db.SinavOturum on grv.SinavOturumId equals otrm.SinavOturumId
join tch in db.Teacher on grv.TeacherId equals tch.TeacherId
join tchUsr in db.User on tch.User.UserId equals tchUsr.UserId
join otrokl in db.SinavOturumOkullari on grv.SinavOturumId equals otrokl.SinavOturumId
join ogtokl in db.School on tch.SchoolId equals ogtokl.SchoolId
join okl in db.School on grv.SchoolId equals okl.SchoolId
join snv in db.Sinav on otrm.SinavId equals snv.SinavId
where otrm.SinavOturumId == SinavOturumId &&
grv.SinavGorevId == (int)SG_DAL.Enums.EnumSinavGorev.Gozetmen
select new
{
SinavAdi = snv.SinavAdi,
SinavTarihi = (DateTime)otrm.Tarih,
SinavSaati = otrm.Saat,
SinavOkulAdi = okl.Ad,
SinavOkulMebKodu = okl.MebKodu,
KadroluOlduguOkulAdi = ogtokl.Ad,
PersonelSira = grv.SiraNo,
PersonelAdSoyad = tchUsr.Ad + " " + tchUsr.Soyad,
PersonelTC = tchUsr.TCKimlik,
PersonelGorev = (SG_DAL.Enums.EnumSinavGorev)grv.SinavGorevId,
SinavOkulId = okl.SchoolId,
KatilimDurumu = grv.SinavKatilimi,
OgretmenId = tch.TeacherId,
KomisyonBaskani =
(from u1 in db.User
join t1 in db.Teacher on u1.UserId equals t1.User.UserId
join g1 in db.SinavGorevli on t1.TeacherId equals g1.TeacherId
where g1.SinavGorevId == (int)SG_DAL.Enums.EnumSinavGorev.BinaSinavKomisyonuBaskani
&& g1.SinavOturumId == SinavOturumId
&& g1.SchoolId == okl.SchoolId
&& g1.SiraNo == 1
select new { AdSoyad = u1.Ad + " " + u1.Soyad }
).FirstOrDefault().AdSoyad
,
KomisyonUyesi =
(from u1 in db.User
join t1 in db.Teacher on u1.UserId equals t1.User.UserId
join g1 in db.SinavGorevli on t1.TeacherId equals g1.TeacherId
where g1.SinavGorevId == (int)SG_DAL.Enums.EnumSinavGorev.BinaSinavKomisyonuUyesi
&& g1.SinavOturumId == SinavOturumId
&& g1.SchoolId == okl.SchoolId
&& g1.SiraNo == 2
select new { AdSoyad2 = u1.Ad + " " + u1.Soyad }
).FirstOrDefault().AdSoyad2
,
KomisyonUyesi2 =
(from u1 in db.User
join t1 in db.Teacher on u1.UserId equals t1.User.UserId
join g1 in db.SinavGorevli on t1.TeacherId equals g1.TeacherId
where g1.SinavGorevId == (int)SG_DAL.Enums.EnumSinavGorev.BinaSinavKomisyonuUyesi
&& g1.SinavOturumId == SinavOturumId
&& g1.SchoolId == okl.SchoolId
&& g1.SiraNo == 3
select new { AdSoyad3 = u1.Ad + " " + u1.Soyad }
).FirstOrDefault().AdSoyad3
}).Distinct().OrderBy(d => d.SinavOkulId).ToList();
List<rptSinavGorevlendirme> sg = new List<rptSinavGorevlendirme>();
foreach (var item in list)
{
rptSinavGorevlendirme snv = new rptSinavGorevlendirme(item.SinavAdi, item.SinavTarihi, item.SinavSaati, item.SinavOkulAdi,
item.KomisyonBaskani, item.KomisyonUyesi, item.KomisyonUyesi2, item.SinavOkulMebKodu, item.KadroluOlduguOkulAdi,
item.PersonelTC, item.PersonelAdSoyad, item.PersonelSira, item.PersonelGorev.ToString(), item.SinavOkulId, item.KatilimDurumu, item.OgretmenId);
sg.Add(snv);
}
return sg;
}
//using (SGContext db = new SGContext())
//{
// try
// {
// var list = (from tch in db.Teacher
// join user in db.User on tch.User.UserId equals user.UserId
// join grv in db.SinavGorevli on tch.TeacherId equals grv.TeacherId
// join otrm in db.SinavOturum on grv.SinavOturumId equals otrm.SinavOturumId
// where otrm.SinavOturumId == SinavOturumId &&
// grv.SinavGorevId == (int)SG_DAL.Enums.EnumSinavGorev.Gozetmen
// select new
// {
// tch.TeacherId,
// tch.Unvan,
// tch.GorevSayisi,
// tch.Kidem,
// user.UserId,
// user.Ad,
// user.Soyad,
// user.TCKimlik,
// user.Tel,
// user.Email
// });
// List<Teacher> tchList = new List<Teacher>();
// foreach (var item in list)
// {
// Teacher tc = new Teacher();
// tc.TeacherId = item.TeacherId;
// tc.GorevSayisi = item.GorevSayisi;
// tc.Kidem = item.Kidem;
// }
// return tchList.ToList();
// }
// catch (Exception)
// {
// return null;
// }
//}
}
public static List<rptSinavGorevlendirme> GetSinavGorevlendirmeForReport(int SinavOturumId)
{
using (SGContext db = new SGContext())
{
var list = (from grv in db.SinavGorevli
join otrm in db.SinavOturum on grv.SinavOturumId equals otrm.SinavOturumId
join tch in db.Teacher on grv.TeacherId equals tch.TeacherId
join tchUsr in db.User on tch.User.UserId equals tchUsr.UserId
join otrokl in db.SinavOturumOkullari on grv.SinavOturumId equals otrokl.SinavOturumId
join ogtokl in db.School on tch.SchoolId equals ogtokl.SchoolId
join okl in db.School on grv.SchoolId equals okl.SchoolId
join snv in db.Sinav on otrm.SinavId equals snv.SinavId
where otrm.SinavOturumId == SinavOturumId &&
grv.SinavGorevId == (int)SG_DAL.Enums.EnumSinavGorev.Gozetmen
select new
{
SinavAdi = snv.SinavAdi,
SinavTarihi = (DateTime)otrm.Tarih,
SinavSaati = otrm.Saat,
SinavOkulAdi = okl.Ad,
SinavOkulMebKodu = okl.MebKodu,
KadroluOlduguOkulAdi = ogtokl.Ad,
PersonelSira = grv.SiraNo,
PersonelAdSoyad = tchUsr.Ad + " " + tchUsr.Soyad,
PersonelTC = tchUsr.TCKimlik,
PersonelGorev = (SG_DAL.Enums.EnumSinavGorev)grv.SinavGorevId,
SinavOkulId = okl.SchoolId,
KatilimDurumu = grv.SinavKatilimi,
OgretmenId = grv.TeacherId,
KomisyonBaskani =
(from u1 in db.User
join t1 in db.Teacher on u1.UserId equals t1.User.UserId
join g1 in db.SinavGorevli on t1.TeacherId equals g1.TeacherId
where g1.SinavGorevId == (int)SG_DAL.Enums.EnumSinavGorev.BinaSinavKomisyonuBaskani
&& g1.SinavOturumId == SinavOturumId
&& g1.SchoolId == okl.SchoolId
&& g1.SiraNo == 1
select new { AdSoyad = u1.Ad + " " + u1.Soyad }
).FirstOrDefault().AdSoyad
,
KomisyonUyesi =
(from u1 in db.User
join t1 in db.Teacher on u1.UserId equals t1.User.UserId
join g1 in db.SinavGorevli on t1.TeacherId equals g1.TeacherId
where g1.SinavGorevId == (int)SG_DAL.Enums.EnumSinavGorev.BinaSinavKomisyonuUyesi
&& g1.SinavOturumId == SinavOturumId
&& g1.SchoolId == okl.SchoolId
&& g1.SiraNo == 2
select new { AdSoyad2 = u1.Ad + " " + u1.Soyad }
).FirstOrDefault().AdSoyad2
,
KomisyonUyesi2 =
(from u1 in db.User
join t1 in db.Teacher on u1.UserId equals t1.User.UserId
join g1 in db.SinavGorevli on t1.TeacherId equals g1.TeacherId
where g1.SinavGorevId == (int)SG_DAL.Enums.EnumSinavGorev.BinaSinavKomisyonuUyesi
&& g1.SinavOturumId == SinavOturumId
&& g1.SchoolId == okl.SchoolId
&& g1.SiraNo == 3
select new { AdSoyad3 = u1.Ad + " " + u1.Soyad }
).FirstOrDefault().AdSoyad3
}).Distinct().OrderBy(d => d.SinavOkulId).ToList();
List<rptSinavGorevlendirme> sg = new List<rptSinavGorevlendirme>();
foreach (var item in list)
{
rptSinavGorevlendirme snv = new rptSinavGorevlendirme(item.SinavAdi, item.SinavTarihi, item.SinavSaati, item.SinavOkulAdi,
item.KomisyonBaskani, item.KomisyonUyesi, item.KomisyonUyesi2, item.SinavOkulMebKodu, item.KadroluOlduguOkulAdi,
item.PersonelTC, item.PersonelAdSoyad, item.PersonelSira, item.PersonelGorev.ToString(), item.SinavOkulId, item.KatilimDurumu, item.OgretmenId);
sg.Add(snv);
}
return sg;
}
}