/*
* res : callback goi lai truyen tu main
* code : phan loai chuc nang ( them/sua+xoa) . Xem phan constant
* */
public DialogKhoa(Response <Int32> res, int code, khoa l)
{
this.response = res;
InitializeComponent();
setViewControl(code);
mKhoa = l;
}
public DataTable GetAllsinhvien(khoa khoa)
{
string sqlSelectSinhVien = @"SELECT sv.id ,
Masv ,
sv.Tensv ,
k.Tenkhoa ,
lp.Tenlop ,
( CASE sv.Gioitinh
WHEN 1 THEN N'Nam'
ELSE N'Nữ'
END ) AS Gioitinh ,
sv.Diachi,sv.Image
FROM dbo.SinhVien sv ,
dbo.Lop lp ,
dbo.Khoa k
WHERE sv.Malop = lp.Malop
AND lp.Makhoa = k.Makhoa AND k.Makhoa='" + khoa.MAKHOA + "'";
SqlConnection con = dc.getConnection();
adt = new SqlDataAdapter(sqlSelectSinhVien, con);
DataTable dt = new DataTable();
adt.Fill(dt);
return(dt);
}
public void Xoakhoa(string tenkhoa)
{
khoa xoakhoa = (from tk in data.khoas
where tk.ten == tenkhoa
select tk).Single();
data.khoas.DeleteOnSubmit(xoakhoa);
data.SubmitChanges();
}
public void Capnhatkhoa(string tenkhoacu, string tenkhoamoi)
{
khoa xoakhoa = (from tk in data.khoas
where tk.ten == tenkhoacu
select tk).Single();
xoakhoa.ten = tenkhoamoi;
data.SubmitChanges();
}
private void gridKhoa_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
if (user.Quyen == Constants.CODE_QUYEN_SV)
{
return;
}
khoa item = (khoa)gridKhoa.CurrentRow.DataBoundItem;
new DialogKhoa(this, DialogController.CODE_UPDATE, item).Show();
}
private khoa getDataFromView()
{
String ten = txtTenLop.Text;
String ma = txtMaLop.Text;
khoa khoa = new khoa();
khoa.tenkhoa = ten;
khoa.makhoa = ma;
return(khoa);
}
public DataTable LayTatCaCacMonHocThuocKhoa(khoa k)
{
string sqlLayTatCaCacMonHocThuocKhoa = "SELECT mh.Mamh ,mh.Tenmh,mh.Loaimonhoc,k.Makhoa FROM dbo.Monhoc mh LEFT JOIN dbo.Khoa k ON k.Makhoa = mh.Makhoa WHERE mh.Loaimonhoc IN( N'Đại cương', N'Chuyên nghành') AND (k.Makhoa IS NULL OR k.Makhoa='" + k.MAKHOA + "')";
SqlConnection con = dc.getConnection();
adt = new SqlDataAdapter(sqlLayTatCaCacMonHocThuocKhoa, con);
con.Open();
DataTable dt = new DataTable();
adt.Fill(dt);
con.Close();
return(dt);
}
public ActionResult DeleteFaculty(int id)
{
try
{
khoa k = db.khoas.SingleOrDefault(m => m.Makhoa == id);
db.khoas.DeleteOnSubmit(k);
db.SubmitChanges();
TempData["message"] = "success";
} catch (Exception ex) {
TempData["message"] = "fail";
}
TempData["message2"] = "delete";
return(RedirectToAction("Faculty"));
}
private void btnAdd_Click(object sender, EventArgs e)
{
khoa khoa = getDataFromView();
bool result = new KhoaSerivice().add(khoa);
if (result)
{
this.Hide();
//refresh list
response.onResponse(Constants.CODE_KHOA);
}
else
{
MessageBox.Show("Khong the them vao danh sach");
}
}
public DataTable Laytatcasinhvientheotungkhoa(khoa kh)
{
string Laytatcasinhvientheotungkhoa = @"SELECT sv.id , sv.Masv ,sv.Tensv,kh.Tenkhoa,lp.Tenlop,( CASE sv.Gioitinh
WHEN 1 THEN N'Nam'
ELSE N'Nữ'
END ) AS Gioitinh,sv.Diachi,sv.Image
FROM dbo.SinhVien sv
INNER JOIN dbo.Lop lp ON lp.Malop = sv.Malop
INNER JOIN dbo.Khoa kh ON kh.Makhoa = lp.Makhoa
WHERE kh.Makhoa = '" + kh.MAKHOA + "'";
SqlConnection con = dc.getConnection();
adt = new SqlDataAdapter(Laytatcasinhvientheotungkhoa, con);
DataTable dt = new DataTable();
adt.Fill(dt);
return(dt);
}
public void Themkhoa(string tenkhoa)
{
int dem = (from tk in data.khoas
select tk).ToList().Last().idkhoa;
khoa themkhoa = new khoa();
if (dem == 0)
{
themkhoa.idkhoa = 1;
}
else
{
themkhoa.idkhoa = short.Parse((dem + 1).ToString());
}
themkhoa.ten = tenkhoa;
data.khoas.InsertOnSubmit(themkhoa);
data.SubmitChanges();
}
public bool deleteKhoa(khoa k)
{
string sqlDeleteKhoa = "DELETE FROM Khoa WHERE id =@ID";
SqlConnection con = dc.getConnection();
cm = new SqlCommand(sqlDeleteKhoa, con);
try
{
con.Open();
cm.Parameters.Add("@ID", SqlDbType.Int).Value = k.ID;
cm.ExecuteNonQuery();
con.Close();
}
catch (Exception e)
{
return(false);
}
return(true);
}
private List <khoa> getListKhoaFromExcel(String path)
{
List <khoa> listKhoa = new List <khoa>();
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
if (xlWorkSheet != null)
{
range = xlWorkSheet.UsedRange;
int row = range.Rows.Count;
int col = range.Columns.Count;
for (int r = 2; r <= row; r++)
{
khoa itemKhoa = new khoa();
bool badRow = false;
for (int c = 1; c <= col; c++)
{
String content = "" + (range[r, c] as Range).Value2;
if (content.Equals(""))
{
badRow = true;
break;
}
if (c == 1)
{
itemKhoa.makhoa = content;
}
else
{
itemKhoa.tenkhoa = content;
}
}
if (!badRow)
{
listKhoa.Add(itemKhoa);
}
}
}
return(listKhoa);
}
public int Laytongsosinhvientrongmotkhoa(khoa kh)
{
string sqlLaytongsosinhvientrongmotkhoa = @"SELECT COUNT(sv.id) AS tongsv
FROM dbo.SinhVien sv
INNER JOIN dbo.Lop lp ON lp.Malop = sv.Malop
INNER JOIN dbo.Khoa kh ON kh.Makhoa = lp.Makhoa
WHERE kh.Makhoa = '" + kh.MAKHOA + "'";
SqlConnection con = dc.getConnection();
con.Open();
cm = new SqlCommand(sqlLaytongsosinhvientrongmotkhoa, con);
SqlDataReader reader = cm.ExecuteReader();
int Tongsinhvien = 0;
while (reader.Read())
{
Tongsinhvien = Convert.ToInt32(reader["tongsv"].ToString());
}
return(Tongsinhvien);
}
public ActionResult EditFaculty(khoa k)
{
if (Session["LoggedAD"] == null || Session["LoggedAD"].ToString() == "")
{
return(RedirectToAction("Login", "Admin"));
}
try
{
khoa kk = db.khoas.SingleOrDefault(n => n.Makhoa == k.Makhoa);
kk.Tenkhoa = k.Tenkhoa == null ? kk.Tenkhoa : k.Tenkhoa;
db.SubmitChanges();
TempData["message"] = "success";
}
catch (Exception e)
{
TempData["message"] = "fail";
}
TempData["message2"] = "edit";
return(RedirectToAction("Faculty"));
}
public bool insertKhoa(khoa k)
{
string sqlInsertKhoa = "INSERT INTO Khoa(Makhoa,Tenkhoa) VALUES(@MAKHOA,@TENKHOA)";
SqlConnection con = dc.getConnection();
cm = new SqlCommand(sqlInsertKhoa, con);
try
{
con.Open();
cm.Parameters.Add("@MAKHOA", SqlDbType.NVarChar).Value = k.MAKHOA;
cm.Parameters.Add("@TENKHOA", SqlDbType.NVarChar).Value = k.TENKHOA;
cm.ExecuteNonQuery();
con.Close();
}
catch (Exception e)
{
return(false);
}
return(true);
}
public ActionResult AddFaculty(khoa k)
{
if (Session["LoggedAD"] == null || Session["LoggedAD"].ToString() == "")
{
return(RedirectToAction("Login", "Admin"));
}
try
{
db.khoas.InsertOnSubmit(k);
db.SubmitChanges();
TempData["message"] = "success";
}
catch (Exception ex)
{
TempData["message"] = "fail";
}
TempData["message2"] = "add";
return(RedirectToAction("Faculty"));
}
public bool updateKhoa(khoa k)
{
string sqlInsertKhoa = "UPDATE Khoa SET Makhoa=@MAKHOA ,Tenkhoa=@TENKHOA WHERE id =@ID";
SqlConnection con = dc.getConnection();
cm = new SqlCommand(sqlInsertKhoa, con);
try
{
con.Open();
cm.Parameters.Add("@MAKHOA", SqlDbType.NVarChar).Value = k.MAKHOA;
cm.Parameters.Add("@TENKHOA", SqlDbType.NVarChar).Value = k.TENKHOA;
cm.Parameters.Add("@ID", SqlDbType.Int).Value = k.ID;
cm.ExecuteNonQuery();
con.Close();
}
catch (Exception e)
{
return(false);
}
return(true);
}
public DataTable Layketquahoctaptheotungkhoa(khoa kh)
{
string Layketquahoctaptheotungkhoa = @"SELECT kq.id, kq.Masv ,
sv.Tensv ,
lp.Tenlop,
kh.Tenkhoa,
mh.Tenmh,
mh.Kyhoc,
kq.Diem
FROM dbo.Ketquahoctap kq
INNER JOIN dbo.SinhVien sv ON sv.Masv = kq.Masv
INNER JOIN dbo.Lop lp ON lp.Malop = sv.Malop
INNER JOIN dbo.Khoa kh ON kh.Makhoa = lp.Makhoa
INNER JOIN dbo.Monhoc mh ON mh.Mamh = kq.Mamh
WHERE kh.Makhoa ='" + kh.MAKHOA + "'";
SqlConnection con = dc.getConnection();
adt = new SqlDataAdapter(Layketquahoctaptheotungkhoa, con);
DataTable dt = new DataTable();
adt.Fill(dt);
return(dt);
}
public bool insertKhoa(khoa k)
{
return(khoa_dal.insertKhoa(k));
}
public bool updateKhoa(khoa k)
{
return(khoa_dal.updateKhoa(k));
}
public bool deleteKhoa(khoa k)
{
return(khoa_dal.deleteKhoa(k));
}
public bool add(khoa lop)
{
return(khoaController.insert(lop));
}
public bool update(khoa lop)
{
return(khoaController.update(lop));
}
public DataTable Layketquahoctaptheotungkhoa(khoa kh)
{
return(kqht_dal.Layketquahoctaptheotungkhoa(kh));
}
public DataTable LayTatCaCacMonHocThuocKhoa(khoa k)
{
return(kqht_dal.LayTatCaCacMonHocThuocKhoa(k));
}
public DataTable Laytatcasinhvientheotungkhoa(khoa kh)
{
return(sv_dal.Laytatcasinhvientheotungkhoa(kh));
}
public int Laytongsosinhvientrongmotkhoa(khoa kh)
{
return(sv_dal.Laytongsosinhvientrongmotkhoa(kh));
}
public DataTable GetAllsinhvien(khoa khoa)
{
return(sv_dal.GetAllsinhvien(khoa));
}
public bool delete(khoa lop)
{
return(khoaController.delete(lop));
}
