public IEnumerable <Object> GetNewMonHocByMaKhoa(string MSSV, string _maKhoa)
{
dbMonHocContext = new SinhVienEntities();
if (_maKhoa == "admin")
{
var MonHoc = from mn in dbMonHocContext.tbl_DMMonHoc.AsEnumerable()
select new
{
MaMH = mn.MaMH,
DangKy = (dbMonHocContext.tbl_Diem.Where(d => d.MSSV == MSSV && d.MaMH == mn.MaMH)).ToList().Count != 0 ? true : false
};
return(MonHoc);
}
else
{
var MonHoc = from mn in dbMonHocContext.tbl_DMMonHoc.AsEnumerable()
where mn.Khoa == _maKhoa
select new
{
MaMH = mn.MaMH,
DangKy = (dbMonHocContext.tbl_Diem.Where(d => d.MSSV == MSSV && d.MaMH == mn.MaMH)).ToList().Count != 0 ? true : false
};
return(MonHoc);
}
}
public IEnumerable <Object> GetDataKetQuaByKetQuaTN(bool _KetQua)
{
dbContext = new SinhVienEntities();
var ds = from diem in dbContext.tbl_Diem
join SinhVien in dbContext.tbl_SinhVien
on diem.MSSV equals SinhVien.MSSV
where SinhVien.KetQuaTN == _KetQua
join MonHoc in dbContext.tbl_DMMonHoc
on diem.MaMH equals MonHoc.MaMH
select new
{
MSSV = diem.MSSV,
Ho = SinhVien.Ho,
Ten = SinhVien.Ten,
NTNS = SinhVien.NTNS,
GioiTinh = SinhVien.GioiTinh,
MaKhoa = SinhVien.MaKhoa,
MonHoc = diem.MaMH,
Diem = diem.Diem,
DTB = SinhVien.DTB,
KetQuaTN = SinhVien.KetQuaTN,
XepLoaiTN = SinhVien.XepLoaiTN,
};
return(ds);
}
public List <tbl_DangNhap> GetAllUser()
{
dbContext = new SinhVienEntities();
var user = (from us in dbContext.tbl_DangNhap
select us).ToList();
return(user);
}
private void LoadMaKhoa()
{
SinhVienEntities = new SinhVienEntities();
foreach (var item in SinhVienEntities.tbl_Khoa)
{
cbMaKhoa.Properties.Items.Add(item.MaKhoa);
}
}
public List <tbl_SinhVien> GetAllSinhVien()
{
dbContext = new SinhVienEntities();
var sinhvien = (from sv in dbContext.tbl_SinhVien
select sv).ToList();
return(sinhvien);
}
public List <string> GetMSSV()
{
dbContext = new SinhVienEntities();
var MSSV = (from sv in dbContext.tbl_SinhVien
select sv.MSSV).ToList();
return(MSSV);
}
public List <tbl_Khoa> GetAllKhoa()
{
dbKhoaContext = new SinhVienEntities();
var listKhoa = (from k in dbKhoaContext.tbl_Khoa
select k).ToList();
return(listKhoa);
}
public void DeleteSinhVienByMSSV(string _MSSV)
{
dbContext = new SinhVienEntities();
var sinhvien = dbContext.tbl_SinhVien.Where(sv => sv.MSSV == _MSSV).FirstOrDefault();
dbContext.tbl_SinhVien.Remove(sinhvien);
dbContext.SaveChanges();
}
public List <tbl_SinhVien> GetObjectByID(string ID)
{
dbContext = new SinhVienEntities();
var dbSV = (from sv in dbContext.tbl_SinhVien
where sv.MSSV == ID
select sv).ToList();
return(dbSV);
}
public List <tbl_Khoa> GetKhoaByMaKhoa(string _MaKhoa)
{
dbKhoaContext = new SinhVienEntities();
var tblKhoa = from k in dbKhoaContext.tbl_Khoa
where k.MaKhoa == _MaKhoa
select k;
return(tblKhoa.ToList());
}
public string GetFullNameByUser(string user)
{
dbContext = new SinhVienEntities();
var userName = (from us in dbContext.tbl_DangNhap
where us.TenDangNhap == user
select us.TenDangNhap).FirstOrDefault();
return(userName.ToString());
}
public List <tbl_SinhVien> GetAllSinhVienNotActive(string currentSV)
{
dbContext = new SinhVienEntities();
var sinhvien = (from sv in dbContext.tbl_SinhVien
where sv.isUserLogin != true || sv.MSSV == currentSV
select sv).ToList();
return(sinhvien);
}
public List <tbl_Khoa> UpdateKhoaByMaKhoa(string MaKhoa)
{
dbKhoaContext = new SinhVienEntities();
var khoa = (from k in dbKhoaContext.tbl_Khoa
where k.MaKhoa == MaKhoa
select k).ToList();
return(khoa);
}
public List <tbl_DangNhap> GetObjectByID(string ID)
{
dbContext = new SinhVienEntities();
var dsSV = from sv in dbContext.tbl_DangNhap
where sv.TenDangNhap == ID
select sv;
return(dsSV.ToList());
}
public int GetTotalSinhVienByMaKhoa(string MaKhoa)
{
dbKhoaContext = new SinhVienEntities();
var total = (from sv in dbKhoaContext.tbl_SinhVien
where sv.MaKhoa == MaKhoa
select sv).ToArray().Length;
return(total);
}
public int GetTotalDiemByMonHoc(string _MaMH)
{
dbContext = new SinhVienEntities();
var total = from diem in dbContext.tbl_Diem
where diem.MaMH == _MaMH
select diem;
return(total.ToList().Count);
}
//XÉT TN
public List <tbl_Diem> GetMonHocByMSSV(string _MSSV)
{
dbContext = new SinhVienEntities();
var MonHoc = from mh in dbContext.tbl_Diem
where mh.MSSV == _MSSV
select mh;
return(MonHoc.ToList());
}
public List <tbl_DMMonHoc> GetAllMonHoc()
{
dbMonHocContext = new SinhVienEntities();
var MonHoc = from mn in dbMonHocContext.tbl_DMMonHoc
select mn;
return(MonHoc.ToList());
}
public void DeleteUserByUsername(string username)
{
dbContext = new SinhVienEntities();
var user = (from us in dbContext.tbl_DangNhap
where us.TenDangNhap == username
select us).FirstOrDefault();
dbContext.tbl_DangNhap.Remove(user);
dbContext.SaveChanges();
}
public void DeactiveLogin(string MSSV)
{
dbContext = new SinhVienEntities();
var sinhvien = (from sv in dbContext.tbl_SinhVien
where sv.MSSV == MSSV
select sv).FirstOrDefault();
sinhvien.isUserLogin = false;
dbContext.SaveChanges();
}
public void DeleteSinhVienInDiemByMSSV(string _MSSV)
{
dbContext = new SinhVienEntities();
var diem = from d in dbContext.tbl_Diem
where d.MSSV == _MSSV
select d;
dbContext.tbl_Diem.RemoveRange(diem);
dbContext.SaveChanges();
}
public List <tbl_SinhVien> GetAllSinhVienNotAdmin()
{
dbContext = new SinhVienEntities();
var sinhvien = (from sv in dbContext.tbl_SinhVien
where sv.MSSV != "admin"
orderby sv.MaKhoa ascending
select sv).ToList();
return(sinhvien);
}
public void DeleteMonHocByMaMH(string _MaMH)
{
//tbl_DMMonHoc deleteMonHoc = null;
using (dbMonHocContext = new SinhVienEntities())
{
var deleteMonHoc = dbMonHocContext.tbl_DMMonHoc.Where(d => d.MaMH == _MaMH).FirstOrDefault();
dbMonHocContext.tbl_DMMonHoc.Remove(deleteMonHoc);
dbMonHocContext.SaveChanges();
}
}
public List <tbl_DMMonHoc> GetMonHocByMaMH(string _maMH)
{
dbMonHocContext = new SinhVienEntities();
var MonHoc = from mh in dbMonHocContext.tbl_DMMonHoc
where mh.MaMH == _maMH
select mh;
return(MonHoc.ToList());
}
//XÉT TN
public void UpdateKetQuaTN(string MSSV, string XepLoai, double?DiemTB, bool KetQuaTN)
{
dbContext = new SinhVienEntities();
var sinhvien = (from sv in dbContext.tbl_SinhVien
where sv.MSSV == MSSV
select sv).FirstOrDefault();
sinhvien.XepLoaiTN = XepLoai;
sinhvien.DTB = DiemTB;
sinhvien.KetQuaTN = KetQuaTN;
dbContext.SaveChanges();
}
public IEnumerable <Object> GetAllSinhVienWithFullName()
{
dbContext = new SinhVienEntities();
var sinhvien = from sv in dbContext.tbl_SinhVien.AsEnumerable()
select new
{
MSSV = sv.MSSV,
HoTen = sv.Ho + " " + sv.Ten
};
return(sinhvien);
}
public void UpdateKhoaFromGrid(string _MaKhoa, string _TenKhoa)
{
dbKhoaContext = new SinhVienEntities();
tbl_Khoa khoa = new tbl_Khoa
{
MaKhoa = _MaKhoa,
TenKhoa = _TenKhoa,
};
dbKhoaContext.tbl_Khoa.Add(khoa);
dbKhoaContext.SaveChanges();
}
public bool UpdateDiemToDataBase(string MSSV, string MaMH, float Diem)
{
using (dbContext = new SinhVienEntities())
{
var diem = (from d in dbContext.tbl_Diem
where d.MSSV == MSSV && d.MaMH == MaMH
select d).FirstOrDefault();
diem.Diem = Diem;
dbContext.SaveChanges();
return(true);
}
}
public int UpdateThongTinDangKyMonHoc(string _MSSV, DataRow index)
{
int result = 0;
string _MaMH = index.ItemArray[0].ToString();
bool _Diem = bool.Parse(index.ItemArray[1].ToString());
dbContext = new SinhVienEntities();
//Check
//(1) Nếu điểm = true (chưa đăng ký: Vì cellChanged tức là giá trị trước khi change là false) -> INSERT with Diem = NULL
//(2) Nếu có điểm: -> không cho Hủy đăng ký.
//(3) Nếu điểm == null: -> có thể Hủy đăng ký. =>Remove.
if (_Diem == true)
{
//TODO: (1) Insert
tbl_Diem dtDiem = new tbl_Diem
{
MSSV = _MSSV,
MaMH = _MaMH,
Diem = null
};
dbContext.tbl_Diem.Add(dtDiem);
dbContext.SaveChanges();
result = 1; // "Đã đăng ký môn học";
}
else
{
//TODO :
//Ktra Diem != null -> Không cho hủy đăng ký
var _diem = dbContext.tbl_Diem.Where(d => d.MSSV == _MSSV &&
d.MaMH == _MaMH).FirstOrDefault();
if (_diem.Diem != null)
{
//TODO (2)
result = 2; // "Môn học này đã có điểm, không thể hủy đăng ký";
}
else
{
//TODO (3)
dbContext.tbl_Diem.Remove(_diem);
dbContext.SaveChanges();
result = 3; //"Hủy đăng ký thành công";
}
}
return(result);
}
public void UpdateGridViewToDataBase(DataRow db)
{
dbContext = new SinhVienEntities();
tbl_SinhVien sv = new tbl_SinhVien
{
MSSV = db.ItemArray[0].ToString(),
Ho = db.ItemArray[1].ToString(),
Ten = db.ItemArray[2].ToString(),
GioiTinh = (db.ItemArray[3].ToString() == "Nam")? true: false,
NTNS = DateTime.Parse(db.ItemArray[4].ToString()),
NoiSinh = db.ItemArray[5].ToString(),
MaKhoa = db.ItemArray[6].ToString(),
};
dbContext.tbl_SinhVien.Add(sv);
dbContext.SaveChanges();
}
