private void QuanLy_Load(object sender, EventArgs e)
{
cbbItem i1,i2,i3;
i1 = new cbbItem("Môn học","MaMon");
i2 = new cbbItem("Phòng", "MaPhong");
i3 = new cbbItem("Giảng viên", "MaGV");
List<cbbItem> list= new List<cbbItem>();
list.Add(i1);
list.Add(i2);
list.Add(i3);
cbbLoai.DataSource = list;
cbbLoai.DisplayMember = "Text";
cbbLoai.ValueMember = "Value";
}
public void Getdata()
{
cn.Open();
string sql;
cbbItem cbb = (cbbItem)cbbPhong.SelectedItem;
string value = cbb.Value.ToString();
if (Convert.ToInt32(value) == 0)
{
sql = "select TKB.MaPhong, P.TenPhong, LH.TenLop, M.TenMon, TKB.TietBD, TKB.TietKT, TKB.Ngay, MP.TenNgMuon, TP.TenNgTra , MP.Note from TKBieu TKB, MuonPhong MP, TraPhong TP, Phong P, LopHoc LH,Mon M where TKB.MaMP like MP.MaMP and TKB.MaTP like TP.MaTP and TKB.MaPhong like P.MaPhong and TKB.MaLH like LH.MaLH and LH.MaMon like M.MaMon";
}
else
{
sql = "select TKB.MaPhong, P.TenPhong, LH.TenLop, M.TenMon, TKB.TietBD, TKB.TietKT, TKB.Ngay, MP.TenNgMuon, TP.TenNgTra , MP.Note from TKBieu TKB, MuonPhong MP, TraPhong TP, Phong P, LopHoc LH,Mon M where TKB.MaMP like MP.MaMP and TKB.MaTP like TP.MaTP and TKB.MaPhong like P.MaPhong and TKB.MaLH like LH.MaLH and LH.MaMon like M.MaMon and TKB.MaPhong like " + value;
}
List <TKB> list = new List <TKB>();
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader dr = cmd.ExecuteReader();
int TietBD, TietKT, IDPhong;
DateTime NgayDuocChon = DateTime.ParseExact(dtpPT.Text, "dd/MM/yyyy", new CultureInfo("en-US"));
DateTime NgayMuon;
string NguoiMuon, NguoiTra, LyDo, Mon, Phong, Lop;
while (dr.Read())
{
IDPhong = dr.GetInt32(0);
Phong = dr.GetString(1);
Lop = dr.GetString(2);
Mon = dr.GetString(3);
TietBD = dr.GetInt32(4);
TietKT = dr.GetInt32(5);
NgayMuon = dr.GetDateTime(6);
NguoiMuon = dr.GetString(7);
NguoiTra = dr.GetString(8);
LyDo = dr.GetString(9);
if (NgayMuon.Date == NgayDuocChon)
{
if (NguoiTra == "Chưa trả")
{
TKB tkbieu = new TKB(IDPhong, Phong, Lop, Mon, TietBD, TietKT, NgayMuon, NguoiMuon, NguoiTra, LyDo);
list.Add(tkbieu);
}
}
}
dr.Close();
dgvTKB.DataSource = list;
cn.Close();
}
private void cbbLoai_SelectedIndexChanged(object sender, EventArgs e)
{
string sql = "";
if (cn != null && cn.State == ConnectionState.Closed)
{
cn.Open();
}
cbbItem cbb = (cbbItem)cbbLoai.SelectedItem;
string value = cbb.Value.ToString();
switch (value)
{
case "MaMon":
{
sql = "select MaMon, TenMon from Mon";
newDisp = "TenMon";
newValue = "MaMon";
break;
}
case "MaPhong":
{
sql = "select MaPhong,TenPhong from Phong";
newDisp = "TenPhong";
newValue = "MaPhong";
break;
}
case "MaGV":
{
sql = "select MaGV, TenGV from GiangVien";
newDisp = "TenGV";
newValue = "MaGV";
break;
}
}
List <cbbItem> list = new List <cbbItem>();
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataAdapter ad = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
ad.Fill(ds);
cbbChiTiet.DataSource = ds.Tables[0];
cbbChiTiet.DisplayMember = newDisp;
cbbChiTiet.ValueMember = newValue;
cn.Close();
}
public DSTraPhong()
{
InitializeComponent();
try
{
string cnstr = "Server = .; Database = QLPH; Integrated Security = true;";
cn.ConnectionString = cnstr;
if (cn != null && cn.State == ConnectionState.Closed)
{
cn.Open();
}
}
catch (SqlException)
{
MessageBox.Show("Loi ket noi !!", "Thong bao", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
cn.Close();
}
//////////////////////////////////
List <cbbItem> list = new List <cbbItem>();
cn.Open();
string name;
object v;
string sql = "select MaPhong, TenPhong from Phong";
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader dr = cmd.ExecuteReader();
cbbItem t = new cbbItem("Tất cả", "0");
list.Add(t);
while (dr.Read())
{
v = Convert.ToString(dr.GetInt32(0));
name = dr.GetString(1);
cbbItem item = new cbbItem(name, v);
list.Add(item);
}
cbbPhong.DataSource = list;
cbbPhong.DisplayMember = "Text";
cbbPhong.ValueMember = "Value";
dr.Close();
cn.Close();
}
private void btnTK_Click(object sender, EventArgs e)
{
cn.Open();
string sql = "";
DataTable dsCT = (DataTable)cbbChiTiet.DataSource;
string Ma = dsCT.Rows[cbbChiTiet.SelectedIndex][0].ToString();
cbbItem cbbL = (cbbItem)cbbLoai.SelectedItem;
string Loai = cbbL.Value.ToString();
if (Loai == "MaMon")
{
sql = "select P.TenPhong, LH.TenLop, M.TenMon, TKB.TietBD, TKB.TietKT, TKB.Ngay, MP.TenNgMuon, TP.TenNgTra, MP.Note from TKBieu TKB, MuonPhong MP, TraPhong TP, Phong P, LopHoc LH, Mon M where TKB.MaMP like MP.MaMP and TKB.MaTP like TP.MaTP and TKB.MaPhong like P.MaPhong and TKB.MaLH like LH.MaLH and LH.MaMon like M.MaMon and M.MaMon like " + Ma;
}
if (Loai == "MaPhong")
{
sql = "select P.TenPhong, LH.TenLop, M.TenMon, TKB.TietBD, TKB.TietKT, TKB.Ngay, MP.TenNgMuon, TP.TenNgTra , MP.Note from TKBieu TKB, MuonPhong MP, TraPhong TP, Phong P, LopHoc LH, Mon M where TKB.MaMP like MP.MaMP and TKB.MaTP like TP.MaTP and TKB.MaPhong like P.MaPhong and TKB.MaLH like LH.MaLH and LH.MaMon like M.MaMon and TKB.MaPhong like " + Ma;
}
if (Loai == "MaGV")
{
sql = "select P.TenPhong, LH.TenLop, M.TenMon, TKB.TietBD, TKB.TietKT, TKB.Ngay, MP.TenNgMuon, TP.TenNgTra , MP.Note from TKBieu TKB, MuonPhong MP, TraPhong TP, Phong P, LopHoc LH, Mon M where TKB.MaMP like MP.MaMP and TKB.MaTP like TP.MaTP and TKB.MaPhong like P.MaPhong and TKB.MaLH like LH.MaLH and LH.MaMon like M.MaMon and LH.MaGV like " + Ma;
}
List <TKB> list = new List <TKB>();
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader dr = cmd.ExecuteReader();
int TietBD, TietKT;
DateTime NgayMuon;
string NguoiMuon, NguoiTra, LyDo, Phong, Lop, Mon;
while (dr.Read())
{
Phong = dr.GetString(0);
Lop = dr.GetString(1);
Mon = dr.GetString(2);
TietBD = dr.GetInt32(3);
TietKT = dr.GetInt32(4);
NgayMuon = dr.GetDateTime(5);
NguoiMuon = dr.GetString(6);
NguoiTra = dr.GetString(7);
LyDo = dr.GetString(8);
TKB tkbieu = new TKB(Phong, Lop, Mon, TietBD, TietKT, NgayMuon, NguoiMuon, NguoiTra, LyDo);
list.Add(tkbieu);
}
dr.Close();
dgvTKB.DataSource = list;
cn.Close();
}
public DSTraPhong()
{
InitializeComponent();
try
{
cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnStr"].ConnectionString);
cn.Open();
}
catch (SqlException)
{
MessageBox.Show("Loi ket noi !!", "Thong bao", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
cn.Close();
}
//////////////////////////////////
List <cbbItem> list = new List <cbbItem>();
cn.Open();
string name;
object v;
string sql = "select MaPhong, TenPhong from Phong";
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader dr = cmd.ExecuteReader();
cbbItem t = new cbbItem("Tất cả", "0");
list.Add(t);
while (dr.Read())
{
v = Convert.ToString(dr.GetInt32(0));
name = dr.GetString(1);
cbbItem item = new cbbItem(name, v);
list.Add(item);
}
cbbPhong.DataSource = list;
cbbPhong.DisplayMember = "Text";
cbbPhong.ValueMember = "Value";
dr.Close();
cn.Close();
}
private void btnOK_Click(object sender, EventArgs e)
{
cbbItem cbb = (cbbItem)cbbPhong.SelectedItem;
int idroom = Convert.ToInt32(cbb.Value.ToString());
cn.Open();
SqlCommand cmd = new SqlCommand("QLPHRoomUpdate", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@IDPhong", idroom));
cmd.Parameters.Add(new SqlParameter("@MC", (radMCTot.Checked == true)?true:false));
cmd.Parameters.Add(new SqlParameter("@AT", (radATTot.Checked == true)?true:false));
cmd.Parameters.Add(new SqlParameter("@AS", (radASTot.Checked == true)?true:false));
cmd.Parameters.Add(new SqlParameter("@ML", (radMLTot.Checked == true)?true:false));
cmd.Parameters.Add(new SqlParameter("@RO", (radRDCTot.Checked == true)?true:false));
cmd.ExecuteNonQuery();
cn.Close();
if (MessageBox.Show("Cập nhật thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information) == DialogResult.OK)
{
this.Close();
}
}
public Sua(PhongHu f)
{
FPhongHu = f;
InitializeComponent();
try
{
//string cnstr = "Server = .; Database = QLPH; Integrated Security = true;";
//cn.ConnectionString = cnstr;
if (cn != null && cn.State == ConnectionState.Closed)
{
cn.Open();
}
}
catch (SqlException)
{
MessageBox.Show("Loi ket noi !!", "Thong bao", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
cn.Close();
}
////////////////////////////////////////////////////////////
List <cbbItem> list = new List <cbbItem>();
cn.Open();
string name;
object v;
string sql = "select MaPhong, TenPhong from Phong";
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
v = Convert.ToString(dr.GetInt32(0));
name = dr.GetString(1);
cbbItem item = new cbbItem(name, v);
list.Add(item);
}
dr.Close();
cbbPhong.DataSource = list;
cbbPhong.DisplayMember = "Text";
cbbPhong.ValueMember = "Value";
cbbPhong.Enabled = true;
////////////////////////////////////////////////////////
cbbItem cbb = (cbbItem)cbbPhong.SelectedItem;
string value = cbb.Value.ToString();
if (value != "")
{
if (cn != null && cn.State == ConnectionState.Closed)
{
cn.Open();
}
string sql1 = "select * from Phong where MaPhong like " + value;
bool mc, at, asang, ml, rdc;
SqlCommand cmd1 = new SqlCommand(sql1, cn);
SqlDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
mc = dr1.GetBoolean(2);
at = dr1.GetBoolean(3);
asang = dr1.GetBoolean(4);
ml = dr1.GetBoolean(5);
rdc = dr1.GetBoolean(6);
if (mc == true)
{
radMCTot.Checked = true; radMCHong.Checked = false;
}
else
{
radMCTot.Checked = false; radMCHong.Checked = true;
}
if (at == true)
{
radATTot.Checked = true; radATHong.Checked = false;
}
else
{
radATTot.Checked = false; radATHong.Checked = true;
}
if (asang == true)
{
radASTot.Checked = true; radASHong.Checked = false;
}
else
{
radASTot.Checked = false; radASHong.Checked = true;
}
if (ml == true)
{
radMLTot.Checked = true; radMLHong.Checked = false;
}
else
{
radMLTot.Checked = false; radMLHong.Checked = true;
}
if (rdc == true)
{
radRDCTot.Checked = true; radRDCHong.Checked = false;
}
else
{
radRDCTot.Checked = false; radRDCHong.Checked = true;
}
}
dr1.Close();
}
cn.Close();
}
private void cbbPhong_SelectedIndexChanged(object sender, EventArgs e)
{
if (cn != null && cn.State == ConnectionState.Closed)
{
cn.Open();
}
cbbItem cbb = (cbbItem)cbbPhong.SelectedItem;
string value = cbb.Value.ToString();
if (value != "")
{
string sql2 = "SELECT * FROM Phong WHERE MaPhong LIKE '%" + value + "'";
bool mc, at, asang, ml, rdc;
SqlCommand cmd2 = new SqlCommand(sql2, cn);
SqlDataReader dr2 = cmd2.ExecuteReader();
while (dr2.Read())
{
mc = dr2.GetBoolean(2);
at = dr2.GetBoolean(3);
asang = dr2.GetBoolean(4);
ml = dr2.GetBoolean(5);
rdc = dr2.GetBoolean(6);
if (mc == true)
{
radMCTot.Checked = true; radMCHong.Checked = false;
}
else
{
radMCTot.Checked = false; radMCHong.Checked = true;
}
if (at == true)
{
radATTot.Checked = true; radATHong.Checked = false;
}
else
{
radATTot.Checked = false; radATHong.Checked = true;
}
if (asang == true)
{
radASTot.Checked = true; radASHong.Checked = false;
}
else
{
radASTot.Checked = false; radASHong.Checked = true;
}
if (ml == true)
{
radMLTot.Checked = true; radMLHong.Checked = false;
}
else
{
radMLTot.Checked = false; radMLHong.Checked = true;
}
if (rdc == true)
{
radRDCTot.Checked = true; radRDCHong.Checked = false;
}
else
{
radRDCTot.Checked = false; radRDCHong.Checked = true;
}
}
dr2.Close();
}
cn.Close();
}
private void btnTK_Click(object sender, EventArgs e)
{
cn.Open();
string sql = "";
DataTable dsCT = (DataTable)cbbChiTiet.DataSource;
string Ma = dsCT.Rows[cbbChiTiet.SelectedIndex][0].ToString();
cbbItem cbbL = (cbbItem)cbbLoai.SelectedItem;
string Loai = cbbL.Value.ToString();
//sql = "SELECT P.TenPhong, " +
// "LH.TenLop, " +
// "M.TenMon, " +
// "GV.TenGV, " +
// "TKB.TietBD, " +
// "TKB.TietKT, " +
// "TKB.Ngay, " +
// "MP.TenNgMuon, " +
// "TP.TenNgTra, " +
// "MP.Note " +
// "FROM " +
// "TKBieu TKB, " +
// "MuonPhong MP, " +
// "TraPhong TP, " +
// "Phong P, " +
// "LopHoc LH, " +
// "Mon M, " +
// "GiangVien GV " +
// "WHERE " +
// "TKB.MaMP LIKE MP.MaMP " +
// "AND TKB.MaTP LIKE TP.MaTP " +
// "AND TKB.MaPhong LIKE P.MaPhong " +
// "AND TKB.MaLH LIKE LH.MaLH " +
// "AND GV.MaGV LIKE LH.MaGV " +
// "AND LH.MaMon LIKE M.MaMon ";
sql = "SELECT P.TenPhong, LH.TenLop, M.TenMon, GV.TenGV, TKB.TietBD,TKB.TietKT,TKB.Ngay,MP.TenNgMuon,TP.TenNgTra, MP.Note "
+"FROM TKBieu TKB, MuonPhong MP, TraPhong TP, Phong P, LopHoc LH, Mon M, GiangVien GV "
+"WHERE TKB.MaMP LIKE MP.MaMP AND TKB.MaTP LIKE TP.MaTP AND TKB.MaPhong LIKE P.MaPhong AND TKB.MaLH LIKE LH.MaLH AND GV.MaGV LIKE LH.MaGV AND LH.MaMon LIKE M.MaMon ";
if (Loai == "MaMon")
{
sql += "AND M.MaMon LIKE " + Ma;
}
if (Loai == "MaPhong")
{
sql += "AND TKB.MaPhong LIKE " + Ma;
}
if (Loai == "MaGV")
{
sql += "AND LH.MaGV LIKE " + Ma;
}
List<TKB> list = new List<TKB>();
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader dr = cmd.ExecuteReader();
int TietBD, TietKT;
DateTime NgayMuon;
string NguoiMuon, NguoiTra, LyDo, Phong, Lop, Mon, GV;
while (dr.Read())
{
Phong = dr.GetString(0);
Lop = dr.GetString(1);
Mon = dr.GetString(2);
GV = dr.GetString(3);
TietBD = dr.GetInt32(4);
TietKT = dr.GetInt32(5);
NgayMuon = dr.GetDateTime(6);
NguoiMuon = dr.GetString(7);
NguoiTra = dr.GetString(8);
LyDo = dr.GetString(9);
TKB tkbieu = new TKB(Phong, Lop, Mon, GV,TietBD, TietKT, NgayMuon, NguoiMuon, NguoiTra, LyDo);
list.Add(tkbieu);
}
dr.Close();
dgvTKB.DataSource = list;
cn.Close();
}
private void cbbPhong_SelectedIndexChanged(object sender, EventArgs e)
{
if (cn != null && cn.State == ConnectionState.Closed)
{
cn.Open();
}
cbbItem cbb = (cbbItem)cbbPhong.SelectedItem;
string value = cbb.Value.ToString();
if (value != "")
{
string sql1 = "select * from Phong where MaPhong like " + value;
bool mc, at, asang, ml, rdc;
SqlCommand cmd1 = new SqlCommand(sql1, cn);
SqlDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
mc = dr1.GetBoolean(1);
at = dr1.GetBoolean(2);
asang = dr1.GetBoolean(3);
ml = dr1.GetBoolean(4);
rdc = dr1.GetBoolean(5);
if (mc == true)
{
radMCTot.Checked = true; radMCHong.Checked = false;
}
else
{
radMCTot.Checked = false; radMCHong.Checked = true;
}
if (at == true)
{
radATTot.Checked = true; radATHong.Checked = false;
}
else
{
radATTot.Checked = false; radATHong.Checked = true;
}
if (asang == true)
{
radASTot.Checked = true; radASHong.Checked = false;
}
else
{
radASTot.Checked = false; radASHong.Checked = true;
}
if (ml == true)
{
radMLTot.Checked = true; radMLHong.Checked = false;
}
else
{
radMLTot.Checked = false; radMLHong.Checked = true;
}
if (rdc == true)
{
radRDCTot.Checked = true; radRDCHong.Checked = false;
}
else
{
radRDCTot.Checked = false; radRDCHong.Checked = true;
}
}
dr1.Close();
}
cn.Close();
}
