private void Thongtincanhan_Load(object sender, EventArgs e)
{
try
{
cn.OpenConn();
string sql = "select * from CANBO where MACB = '" + UsertName + "'";
rdr = cn.executeSQL(sql);
while (rdr.Read())
{
lbmacb.Text = rdr["MACB"].ToString();
lbmakhoa.Text = rdr["MAKHOA"].ToString();
lbhoten.Text = rdr["HOTEN"].ToString();
lbngsinh.Text = rdr["NGAYSINH"].ToString();
lbgioitinh.Text = rdr["GIOITINH"].ToString();
lbchucvu.Text = rdr["CHUCVU"].ToString();
lbemail.Text = rdr["EMAIL"].ToString();
//lbmk.Text = rdr["MATKHAU"].ToString();
lbqhan.Text = rdr["QUYENHAN"].ToString();
}
}
catch (Exception EX)
{
MessageBox.Show("Lỗi " + EX);
}
finally
{
cn.CloseConn();
}
}
public int KTdangnhap(string USER, string PASSWORD)
{
cn.OpenConn();
cm = new SqlCommand("select MACB from CANBO where MACB='" + USER + "' and MATKHAU='" + PASSWORD + "'", cn.con);
dr = cm.ExecuteReader();
if (dr.Read())
{
return(1);
}
else
{
return(0);
}
cm.Dispose();
dr.Dispose();
cn.CloseConn();
}
private void btnSua_Click(object sender, EventArgs e)
{
Connection cn = new Connection();
cn.OpenConn();
string mamh = txtmamon.Text;
string macn = cbmacn.SelectedValue.ToString();
string tenmh = txttenmon.Text;
string stc = numsotc.Text;
if (mamh == "" || tenmh == "" || stc == "")
{
MessageBox.Show("Thông tin môn học không được bỏ trống!!!");
}
if (numsotc.Value == 0)
{
errorProvider1.SetError(numsotc, "Số tín chỉ phải lớn hơn không!");
return;
}
cm = new SqlCommand("select MAMH from MONHOC where MAMH='" + mamh + "'", cn.con);
string ma = cm.ExecuteScalar() as string;
if (mamh == ma)
{
DialogResult dlr = MessageBox.Show("Bạn có chắc muốn sửa?", "THÔNG BÁO", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (DialogResult.OK == dlr)
{
string sqlsua = "update MONHOC set MAMH='" + mamh + "',MACN=N'" + macn + "',TENMH=N'" + tenmh + "',SOTC='" + stc + "' where MAMH='" + mamh + "'";
SqlCommand cmd = new SqlCommand(sqlsua, cn.con);
try
{
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.CloseConn();
MessageBox.Show("Sửa thành công");
}
catch
{
cmd.Dispose();
cn.CloseConn();
MessageBox.Show("Sửa thất bại!");
}
}
}
else
{
MessageBox.Show("Không trùng mã môn học!");
}
hienthi();
Loadcbmacn();
errorProvider1.Clear();
//dgvthongtin.DataSource = danhsachmonhoc();
}
private void btnSua_Click(object sender, EventArgs e)
{
Connection cn = new Connection();
cn.OpenConn();
string macb = cbmacb.SelectedValue.ToString();
string malop = txtmalop.Text;
string tgbd = dptgbd.Value.ToString("yyyy-MM-dd");
string tgkt = dptgkt.Value.ToString("yyyy-MM-dd");
DateTime bd = Convert.ToDateTime(dptgbd.Value.ToString());
DateTime kt = Convert.ToDateTime(dptgkt.Value.ToString());
int b = int.Parse(bd.Year.ToString());
int k = int.Parse(kt.Year.ToString());
//int nam = k - b;
if (k < b)
{
errorProvider1.SetError(dptgkt, "Năm kết thúc phải sau năm bắt đầu!");
return;
}
//cm = new SqlCommand("select MALOP from COVAN where MALOP='" + malop + "'", cn.con);
//string ma1 = cm.ExecuteScalar() as string;
//if (malop == ma1)
//{
DialogResult dlr = MessageBox.Show("Bạn có chắc muốn sửa?", "THÔNG BÁO", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (DialogResult.OK == dlr)
{
string sqlsua = "update COVAN set MACB='" + macb + "',MALOP='" + malop + "',THOIGIANBD='" +
tgbd + "',THOIGIANKT='" + tgkt + "' where ID=" + dgvthongtin.CurrentRow.Cells["ID"].Value.ToString();
SqlCommand cmd = new SqlCommand(sqlsua, cn.con);
try
{
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.CloseConn();
MessageBox.Show("Sửa thành công");
}
catch
{
cmd.Dispose();
cn.CloseConn();
MessageBox.Show("Sửa thất bại!");
}
}
//}
//else
// MessageBox.Show("Lỗi!");
hienthi();
errorProvider1.Clear();
}
public string XinChao(string MACB)
{
Connection cn = new Connection();
cn.OpenConn();
SqlCommand cm = new SqlCommand("select HOTEN from CANBO where MACB= '" + MACB + "' ", cn.con);
string hoten = cm.ExecuteScalar() as string;
cn.CloseConn();
return(hoten);
}
private void btnThem_Click(object sender, EventArgs e)
{
Connection cn = new Connection();
cn.OpenConn();
string mamh = txtmamon.Text;
string macn = cbmacn.SelectedValue.ToString();
string tenmh = txttenmon.Text;
string stc = numsotc.Text;
if (mamh == "" || tenmh == "" || stc == "")
{
MessageBox.Show("Thông tin môn học không được bỏ trống!!!");
}
cm = new SqlCommand("select MAMH from MONHOC where MAMH='" + mamh + "'", cn.con);
string ma = cm.ExecuteScalar() as string;
if (mamh == ma)
{
errorProvider1.SetError(txtmamon, "Mã đã tồn tại!");
return;
}
if (numsotc.Value == 0)
{
errorProvider1.SetError(numsotc, "Số tín chỉ phải lớn hơn không!");
return;
}
else
{
string sqlthem = "insert into MONHOC values('" + mamh + "',N'" + macn + "',N'" + tenmh + "',N'" + stc + "')";
SqlCommand cmd = new SqlCommand(sqlthem, cn.con);
try
{
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.CloseConn();
MessageBox.Show("Thêm thành công");
}
catch
{
cmd.Dispose();
cn.CloseConn();
MessageBox.Show("Thêm thất bại!");
}
}
hienthi();
Loadcbmacn();
errorProvider1.Clear();
//dgvthongtin.DataSource = danhsachmonhoc();
}
private void btnXoa_Click(object sender, EventArgs e)
{
Connection cn = new Connection();
cn.OpenConn();
string mssv = txtmssv.Text;
string mamon = cbmamh.SelectedValue.ToString();
string cc = txtdiemcc.Text;
string gk = txtdiemgk.Text;
string ck = txtdiemck.Text;
string he10 = txthe10.Text;
string he4 = txthe4.Text;
string chu = txtchu.Text;
string lanhoc = txtlanhoc.Text;
if (cc == "" || gk == "" || ck == "" || he10 == "" || he4 == "" || chu == "" || lanhoc == "")
{
warning.Play();
lbChuy.Text = "Vui lòng chọn thông tin cần xóa!!!";
}
else
{
DialogResult dlr = MessageBox.Show("Bạn có chắc muốn xóa?", "THÔNG BÁO", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (DialogResult.OK == dlr)
{
string sqlxoa = "delete HOC where MSSV='" + mssv + "' and MAMH='" + mamon + "'";
SqlCommand cmd = new SqlCommand(sqlxoa, cn.con);
try
{
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.CloseConn();
MessageBox.Show("Xóa thành công");
lbChuy.Text = "";
txtdiemcc.Clear();
txtdiemgk.Clear();
txtdiemck.Clear();
txthe10.Clear();
txthe4.Clear();
txtchu.Clear();
//txtlanhoc.Clear();
}
catch
{
cmd.Dispose();
cn.CloseConn();
error.Play();
MessageBox.Show("Xóa thất bại!");
}
}
}
hienthi();
}
private void btnThem_Click(object sender, EventArgs e)
{
Connection cn = new Connection();
cn.OpenConn();
string manganh = txtmanganh.Text;
string makhoa = cbmakhoa.SelectedValue.ToString();
string tennganh = txttennganh.Text;
cm = new SqlCommand("select MANGANH from NGANH where MANGANH='" + manganh + "'", cn.con);
string ma = cm.ExecuteScalar() as string;
if (txtmanganh.TextLength < 8)
{
errorProvider1.SetError(txtmanganh, "Mã ngành gồm 8 chữ số!");
return;
}
else
{
errorProvider1.Clear();
}
if (manganh == "" || tennganh == "")
{
MessageBox.Show("Thông tin ngành không được bỏ trống!!!");
}
else
if (manganh == ma)
{
error.Play();
MessageBox.Show("Trùng mã ngành, thêm thất bại");
}
else
{
string sqlthem = "insert into NGANH values('" + manganh + "',N'" + makhoa + "',N'" + tennganh + "')";
SqlCommand cmd = new SqlCommand(sqlthem, cn.con);
try
{
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.CloseConn();
MessageBox.Show("Thêm thành công");
}
catch
{
cmd.Dispose();
cn.CloseConn();
MessageBox.Show("Thêm thất bại!");
}
}
hienthi();
//dgvthongtin.DataSource = danhsachkhoa();
}
public void Loadcbmakhoa()
{
Connection cn = new Connection();
cn.OpenConn();
cm = new SqlCommand("select MAKHOA from KHOA", cn.con);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
cbmakhoa.DataSource = ds.Tables[0];
cbmakhoa.ValueMember = "MAKHOA";
}
private void phansachtheoSLM()
{
Connection cn = new Connection();
cn.OpenConn();
string sqlht = "SELECT DISTINCT SINHVIEN.MSSV,HOTEN,MONHOC.MAMH,SOTC,DIEMCC,DIEMGK,DIEMCK,DIEMHE10,DIEMHE4,DIEMCHU,LANHOC FROM HOC,SINHVIEN,MONHOC where MONHOC.MAMH = 'TH0001' ";
//string sqlht = "select * from sach oder by solanmuon asc";
SqlDataAdapter da = new SqlDataAdapter(sqlht, cn.con);
crythongkediemall rpt = new crythongkediemall();
crystalReportViewer1.ReportSource = rpt;
cn.CloseConn();
}
public void Loadcbmanganh()
{
Connection cn = new Connection();
cn.OpenConn();
cm = new SqlCommand("select MANGANH from NGANH", cn.con);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
cbmanganh.DataSource = ds.Tables[0];
cbmanganh.ValueMember = "MANGANH";
}
public void Loadcbmandaotao()
{
Connection cn = new Connection();
cn.OpenConn();
cm = new SqlCommand("select MADT from BACDAOTAO", cn.con);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
cbmadt.DataSource = ds.Tables[0];
cbmadt.ValueMember = "MADT";
}
public DataTable hienthiTK(string sqlht)
{
Connection cn = new Connection();
cn.OpenConn();
SqlDataAdapter da = new SqlDataAdapter(sqlht, cn.con);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
cn.CloseConn();
}
private void btnThem_Click(object sender, EventArgs e)
{
Connection cn = new Connection();
cn.OpenConn();
string macb = cbmacb.SelectedValue.ToString();
string malop = txtmalop.Text;
string tgbd = dptgbd.Value.ToString("yyyy-MM-dd");
//string tgkt = "";
DateTime bd = Convert.ToDateTime(dptgbd.Text);
DateTime Ngt = DateTime.Today;
TimeSpan day = Ngt.Subtract(bd);
string kq = day.Days.ToString();
//MessageBox.Show(kq.ToString());
if (kq != "0")
{
errorProvider1.SetError(dptgbd, "Ngày bắt đầu phải là ngày hiện tại!");
return;
}
if (KiemTraCVHT(malop) > 0)
{
MessageBox.Show("Mã lớp " + malop + " đã có giáo viên cố vấn");
}
else
{
string sqlthem = "insert into COVAN values('" + macb + "','" + malop + "','" + tgbd + "',NULL,0)";
SqlCommand cmd = new SqlCommand(sqlthem, cn.con);
try
{
cmd.ExecuteNonQuery();
cmd.Dispose();
cn.CloseConn();
MessageBox.Show("Thêm thành công");
}
catch
{
cmd.Dispose();
cn.CloseConn();
MessageBox.Show("Thêm thất bại!");
}
}
hienthi();
errorProvider1.Clear();
}
private void hienthi()
{
Connection cn = new Connection();
cn.OpenConn();
string sqlht = "select distinct malop as 'Mã lớp',tennganh as 'Tên ngành',TENDT as 'Hệ đào tạo',tenlop as 'Tên lớp' from LOP, nganh, BACDAOTAO where nganh.manganh = lop.manganh and LOP.MADT = BACDAOTAO.MADT";
SqlCommand cmd = new SqlCommand(sqlht, cn.con);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dgvthongtin.DataSource = dt;
cn.CloseConn();
}
private void hienthi()
{
Connection cn = new Connection();
cn.OpenConn();
string sqlht = "select distinct MACB as 'Mã Cán Bộ',TENKHOA as 'Tên Khoa',HOTEN as 'Họ Tên',NGAYSINH as 'Ngày Sinh',GIOITINH as 'Giới Tính',CHUCVU as 'Chức vụ',CANBO.EMAIL as 'Email',QUYENHAN as 'Quyền Hạn' from CANBO,KHOA where CANBO.MAKHOA = KHOA.MAKHOA and QUYENHAN!='admin'";
SqlCommand cmd = new SqlCommand(sqlht, cn.con);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dgvthongtin.DataSource = dt;
cn.CloseConn();
}
private void hienthi()
{
Connection cn = new Connection();
cn.OpenConn();
string sqlht = "select DISTINCT macn as 'Mã CN',tennganh as 'Tên ngành', tencn as 'Tên CN' from NGANH,chuyennganh where nganh.manganh = chuyennganh.manganh";
SqlCommand cmd = new SqlCommand(sqlht, cn.con);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dgvthongtin.DataSource = dt;
cn.CloseConn();
}
private void hienthi()
{
Connection cn = new Connection();
cn.OpenConn();
string sqlht = "select distinct MAMH as 'Mã môn',TENCN as 'Tên CN',TENMH as 'Tên môn',SOTC as 'Số TC' from MONHOC,CHUYENNGANH where MONHOC.MACN = CHUYENNGANH.MACN";
SqlCommand cmd = new SqlCommand(sqlht, cn.con);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dgvthongtin.DataSource = dt;
cn.CloseConn();
}
public int KiemTraCVHT(string maLop)
{
String sqlht = @"SELECT * FROM COVAN WHERE MALOP = '" + maLop + "'AND GETDATE() BETWEEN THOIGIANBD AND THOIGIANKT UNION ALL SELECT* FROM COVAN WHERE THOIGIANKT iS NULL AND MALOP = '" + maLop + "'";
Connection cn = new Connection();
cn.OpenConn();
SqlDataAdapter da = new SqlDataAdapter(sqlht, cn.con);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt.Rows.Count);
cn.CloseConn();
}
private void hienthi()
{
Connection cn = new Connection();
cn.OpenConn();
string sqlht = "Select Distinct MANGANH as 'Mã Ngành',TENKHOA as 'Tên Khoa',TENNGANH as 'Tên Ngành' from KHOA, NGANH where KHOA.MAKHOA = NGANH.MAKHOA";
SqlCommand cmd = new SqlCommand(sqlht, cn.con);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dgvthongtin.DataSource = dt;
cn.CloseConn();
}
private void hienthi()
{
Connection cn = new Connection();
cn.OpenConn();
string sqlht = "select MACB,MAKHOA,HOTEN,NGAYSINH,GIOITINH,CHUCVU,EMAIL,QUYENHAN from CANBO where QUYENHAN!='admin'";
SqlCommand cmd = new SqlCommand(sqlht, cn.con);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dgvthongtin.DataSource = dt;
cn.CloseConn();
}
private void hienthi()
{
Connection cn = new Connection();
cn.OpenConn();
string sqlht = "select * from NGANH";
SqlCommand cmd = new SqlCommand(sqlht, cn.con);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dgvthongtin.DataSource = dt;
cn.CloseConn();
}
public void Loadcbmachuyennganh1()
{
Connection cn = new Connection();
cn.OpenConn();
cm = new SqlCommand("select distinct CHUYENNGANH.MACN,TENCN from CANBO,KHOA,NGANH,SINHVIEN,LOP,CHUYENNGANH where CHUYENNGANH.MACN=SINHVIEN.MACN AND CANBO.MAKHOA=KHOA.MAKHOA and KHOA.MAKHOA=NGANH.MAKHOA and NGANH.MANGANH=LOP.MANGANH and LOP.MALOP=SINHVIEN.MALOP and CANBO.MACB='" + frmForm1.UsertName + "' ", cn.con);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
CBB.DataSource = ds.Tables[0];
CBB.ValueMember = "MACN";
CBB.DisplayMember = "TENCN";
}
public void Loadcbmachuyennganh()
{
Connection cn = new Connection();
cn.OpenConn();
cm = new SqlCommand("select MACN,TENCN from CHUYENNGANH", cn.con);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
cbmacn.DataSource = ds.Tables[0];
cbmacn.ValueMember = "MACN";
cbmacn.DisplayMember = "TENCN";
}
public void Loadcbmalop()
{
Connection cn = new Connection();
cn.OpenConn();
cm = new SqlCommand("select DISTINCT LOP.MALOP,tenlop from CANBO,KHOA,NGANH,LOP where CANBO.MAKHOA=KHOA.MAKHOA and KHOA.MAKHOA=NGANH.MAKHOA and NGANH.MANGANH=LOP.MANGANH and CANBO.MACB='" + frmForm1.UsertName + "'", cn.con);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
cbmalop.DataSource = ds.Tables[0];
cbmalop.ValueMember = "MALOP";
cbmalop.DisplayMember = "tenlop";
}
private void hienthi()
{
Connection cn = new Connection();
cn.OpenConn();
//CANBO.MACB = '" + frmForm1.UsertName + "'
string sqlht = @"select HOTEN as 'Tên CB', MALOP as 'Mã lớp', THOIGIANBD as 'TG Bắt đầu', THOIGIANKT as 'TG Kết thúc', ID from COVAN,CANBO where COVAN.MACB = CANBO.MACB ";
SqlCommand cmd = new SqlCommand(sqlht, cn.con);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dgvthongtin.DataSource = dt;
cn.CloseConn();
}
public void LoadcbmaCB()
{
Connection cn = new Connection();
cn.OpenConn();
cm = new SqlCommand(@"select DISTINCT MACB, HOTEN from CANBO where QUYENHAN = 'covan' ", cn.con);
//cm = new SqlCommand(@"select MACB from CANBO where QUYENHAN = 'covan' ", cn.con);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
cbmacb.DataSource = ds.Tables[0];
cbmacb.ValueMember = "MACB";
cbmacb.DisplayMember = "HOTEN";
}
private void hienthi()
{
Connection cn = new Connection();
cn.OpenConn();
string sqlht = "select DISTINCT MSSV,tenlop,tencn as 'Tên chuyên ngành',SINHVIEN.HOTEN,SINHVIEN.NGAYSINH,SINHVIEN.GIOITINH,CMND,NIENKHOA,SINHVIEN.DIACHI from CANBO,KHOA,NGANH,SINHVIEN,LOP,CHUYENNGANH where CHUYENNGANH.MACN=SINHVIEN.MACN AND CANBO.MAKHOA=KHOA.MAKHOA and KHOA.MAKHOA=NGANH.MAKHOA and NGANH.MANGANH=LOP.MANGANH and LOP.MALOP=SINHVIEN.MALOP and CANBO.MACB='" + frmForm1.UsertName + "' and sinhvien.macn='" + CBB.SelectedValue + "'";
SqlCommand cmd = new SqlCommand(sqlht, cn.con);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dgvthongtin.DataSource = dt;
cn.CloseConn();
}
public void Loadcbmachuyennganh()
{
try
{
Connection cn = new Connection();
cn.OpenConn();
cm = new SqlCommand(@"select DISTINCT CHUYENNGANH.MACN,TENCN from COVAN,LOP,CANBO,NGANH,CHUYENNGANH where CANBO.MACB = COVAN.MACB and COVAN.MALOP = LOP.MALOP and LOP.MANGANH = NGANH.MANGANH and NGANH.MANGANH = CHUYENNGANH.MANGANH and CANBO.MACB='" + frmForm1.UsertName + "' AND LOP.MALOP='" + cbmalop.SelectedValue.ToString() + "' ", cn.con);
SqlDataAdapter da = new SqlDataAdapter(cm);
DataSet ds = new DataSet();
da.Fill(ds);
cbmacn.DataSource = ds.Tables[0];
cbmacn.ValueMember = "MACN";
cbmacn.DisplayMember = "TENCN";
}
catch { }
}
private void hienthi()
{
Connection cn = new Connection();
cn.OpenConn();
string sqlht = @"select distinct MSSV, TENMH as 'Tên MH', DIEMCC, DIEMGK, DIEMCK, DIEMHE10, DIEMHE4, DIEMCHU as 'Điểm chữ', LANHOC as 'Lần học' from COVAN,CANBO,LOP,NGANH,CHUYENNGANH,MONHOC,HOC
where CANBO.MACB=COVAN.MACB and COVAN.MALOP = LOP.MALOP and LOP.MANGANH = NGANH.MANGANH and NGANH.MANGANH=CHUYENNGANH.MANGANH
and CHUYENNGANH.MACN = MONHOC.MACN and MONHOC.MAMH = HOC.MAMH and CANBO.MACB='" + frmForm1.UsertName + "' AND HOC.MAMH=MONHOC.MAMH AND MSSV='" + txtmssv.Text + "' and chuyennganh.macn='" + frmQuanlysinhvien.cn + "'";
SqlCommand cmd = new SqlCommand(sqlht, cn.con);
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
dgvthongtin.DataSource = dt;
cn.CloseConn();
}