Run

15         public void ChuanBi3Table(string MaHK)
16         {
17             //Table 1
18             string sql1 = " select MaSV,'sotinchi'=sum(SoTC) " +
19                        " from KetQua kq,HocPhan hp,NhomHocPhan nhp,KeHoachGiangDay khgd " +
20                        " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khgd.MaKHGD=nhp.MaKHGD and MaHK='" + MaHK + "' " +
21                        " group by MaSV ";
22             DataTable dt1 = da.GetTable(sql1);
23             for (int i = 0; i < dt1.Rows.Count; i++)
24             {
25                 string sql_1 = "Insert into Loptc values('" + dt1.Rows[i][0] + "'," + dt1.Rows[i][1] + ")";
26                 da.ExcuteNonQuery(sql_1);
27             }
28             //Table 2
29             string sql2 = "select MaSV,'cuoiky'=sum(DiemTk10*SoTC) " +
30                            " from KetQua kq,NhomHocPhan nhp,HocPhan hp,KeHoachGiangDay khdt " +
31                            " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khdt.MaKHGD=nhp.MaKHGD and MaHK='" + MaHK + "' " +
32                            " group by MaSV ";
33             DataTable dt2 = da.GetTable(sql2);
34             for (int i = 0; i < dt2.Rows.Count; i++)
35             {
36                 string sql_2 = "Insert into Lopdiem10 values('" + dt2.Rows[i][0] + "'," + dt2.Rows[i][1] + ")";
37                 da.ExcuteNonQuery(sql_2);
38             }
39             //Table 3
40             string sql3 = " select Lopdiem10.MaSV,'TrungBinhCuoiKyHe10'=cuoiky/sotinchi " +
41                            " from Lopdiem10 left join Loptc on Loptc.MaSV=Lopdiem10.MaSV";
42             DataTable dt3 = da.GetTable(sql3);
43             for (int i = 0; i < dt3.Rows.Count; i++)
44             {
45                 string sql_3 = "Insert into DTB values('" + dt3.Rows[i][0] + "'," + dt3.Rows[i][1] + ")";
46                 da.ExcuteNonQuery(sql_3);
47             }
48         }

49         public void ChuanBi3Table()
50         {
51             //Table 1
52             string sql1 = " select MaSV,'sotinchi'=sum(SoTC) " +
53                        " from KetQua kq,HocPhan hp,NhomHocPhan nhp,KeHoachGiangDay khgd " +
54                        " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khgd.MaKHGD=nhp.MaKHGD " +
55                        " group by MaSV ";
56             DataTable dt1 = da.GetTable(sql1);
57             for (int i = 0; i < dt1.Rows.Count; i++)
58             {
59                 string sql_1 = "Insert into Loptc values('" + dt1.Rows[i][0] + "'," + dt1.Rows[i][1] + ")";
60                 da.ExcuteNonQuery(sql_1);
61             }
62             //Table 2
63             string sql2 = "select MaSV,'cuoiky'=sum(DiemTk10*SoTC) " +
64                            " from KetQua kq,NhomHocPhan nhp,HocPhan hp,KeHoachGiangDay khdt " +
65                            " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khdt.MaKHGD=nhp.MaKHGD " +
66                            " group by MaSV ";
67             DataTable dt2 = da.GetTable(sql2);
68             for (int i = 0; i < dt2.Rows.Count; i++)
69             {
70                 string sql_2 = "Insert into Lopdiem10 values('" + dt2.Rows[i][0] + "'," + dt2.Rows[i][1] + ")";
71                 da.ExcuteNonQuery(sql_2);
72             }
73             //Table 3
74             string sql3 = " select Lopdiem10.MaSV,'TrungBinhCuoiKyHe10'=cuoiky/sotinchi " +
75                            " from Lopdiem10 left join Loptc on Loptc.MaSV=Lopdiem10.MaSV";
76             DataTable dt3 = da.GetTable(sql3);
77             for (int i = 0; i < dt3.Rows.Count; i++)
78             {
79                 string sql_3 = "Insert into DTB values('" + dt3.Rows[i][0] + "'," + dt3.Rows[i][1] + ")";
80                 da.ExcuteNonQuery(sql_3);
81             }
82         }

83         public DataTable XemDiemLop(string MaLop)
84         {
85             SqlConnection con = new SqlConnection();
86             ChuanBi3Table();
87             string sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
88                           " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
89                             " where MaLop='" + MaLop + "'";
90             DataTable dt4 = da.GetTable(sql4);
91             da.ExcuteNonQuery("delete from Loptc where MaSV in ( select MaSV from Loptc )");
92             da.ExcuteNonQuery("delete from Lopdiem10 where MaSV in ( select MaSV from Lopdiem10 )");
93             da.ExcuteNonQuery("delete from DTB where MaSV in ( select MaSV from DTB )");
94             return dt4;
95         }

96         public DataTable ThongKeLop(string MaHK,string MaLop,int loai)
97         {
98             SqlConnection con = da.GetConnect();
99             ChuanBi3Table(MaHK);
100             //Truy van cuoi cung
101             string sql4;
102             switch (loai)
103             {
104                 case 0:
105                     sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
106                            " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
107                            " where MaLop='" + MaLop + "' and TrungBinhCuoiKyHe10 >= 9";
108                     break;
109                 case 1:
110                     sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
111                            " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
112                            " where MaLop='" + MaLop + "' and TrungBinhCuoiKyHe10 >=8 and TrungBinhCuoiKyHe10 < 9";
113                     break;
114                 case 2:
115                     sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
116                            " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
117                            " where MaLop='" + MaLop + "' and TrungBinhCuoiKyHe10 >=6.5 and TrungBinhCuoiKyHe10 < 8";
118                     break;
119                 case 3:
120                     sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
121                            " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
122                            " where MaLop='" + MaLop + "' and TrungBinhCuoiKyHe10 >=5 and TrungBinhCuoiKyHe10 < 6.5";
123                     break;
124                 case 4:
125                     sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
126                            " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
127                            " where MaLop='" + MaLop + "' and TrungBinhCuoiKyHe10 >=3.5 and TrungBinhCuoiKyHe10 < 5";
128                     break;
129                 default:
130                     sql4 = "select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 " +
131                            " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV " +
132                            " where MaLop='" + MaLop + "' and TrungBinhCuoiKyHe10 < 3.5";
133                     break;
134             }
135             DataTable dt4 = da.GetTable(sql4);
136             da.ExcuteNonQuery("delete from Loptc where MaSV in ( select MaSV from Loptc )");
137             da.ExcuteNonQuery("delete from Lopdiem10 where MaSV in ( select MaSV from Lopdiem10 )");
138             da.ExcuteNonQuery("delete from DTB where MaSV in ( select MaSV from DTB )");
139             return dt4;
140         }

141         public DataTable ThongKeKhoiLop(string MaHK, string MaKhoiLop, int loai)
142         {
143             SqlConnection con = da.GetConnect();
144             ChuanBi3Table(MaHK);
145             string sql4;
146             switch (loai)
147             {
148                 case 0:
149                     sql4 = "select sv.MaSV,TenSV,sv.MaLop,TrungBinhCuoiKyHe10 " +
150                            " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV,KhoiLop kl,Lop l " +
151                            " where sv.MaLop=l.MaLop and l.MaKhoiLop=kl.MaKhoiLop " +
152                            " and kl.MaKhoiLop='" + MaKhoiLop + "' and TrungBinhCuoiKyHe10 >=9 ";
153                     break;
154                 case 1:
155                     sql4 = "select sv.MaSV,TenSV,sv.MaLop,TrungBinhCuoiKyHe10 " +
156                            " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV,KhoiLop kl,Lop l " +
157                            " where sv.MaLop=l.MaLop and l.MaKhoiLop=kl.MaKhoiLop " +
158                            " and kl.MaKhoiLop='" + MaKhoiLop + "' and TrungBinhCuoiKyHe10 >=8 and TrungBinhCuoiKyHe10 < 9";
159                     break;
160                 case 2:
161                     sql4 = "select sv.MaSV,TenSV,sv.MaLop,TrungBinhCuoiKyHe10 " +
162                            " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV,KhoiLop kl,Lop l " +
163                            " where sv.MaLop=l.MaLop and l.MaKhoiLop=kl.MaKhoiLop " +
164                            " and kl.MaKhoiLop='" + MaKhoiLop + "' and TrungBinhCuoiKyHe10 >=6.5 and TrungBinhCuoiKyHe10 < 8";
165                     break;
166                 case 3:
167                     sql4 = "select sv.MaSV,TenSV,sv.MaLop,TrungBinhCuoiKyHe10 " +
168                            " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV,KhoiLop kl,Lop l " +
169                            " where sv.MaLop=l.MaLop and l.MaKhoiLop=kl.MaKhoiLop " +
170                            " and kl.MaKhoiLop='" + MaKhoiLop + "' and TrungBinhCuoiKyHe10 >=5 and TrungBinhCuoiKyHe10 < 6.5";
171                     break;
172                 case 4:
173                     sql4 = "select sv.MaSV,TenSV,sv.MaLop,TrungBinhCuoiKyHe10 " +
174                            " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV,KhoiLop kl,Lop l " +
175                            " where sv.MaLop=l.MaLop and l.MaKhoiLop=kl.MaKhoiLop " +
176                            " and kl.MaKhoiLop='" + MaKhoiLop + "' and TrungBinhCuoiKyHe10 >=3.5 and TrungBinhCuoiKyHe10 < 5";
177                     break;
178                 default:
179                     sql4 = "select sv.MaSV,TenSV,sv.MaLop,TrungBinhCuoiKyHe10 " +
180                            " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV,KhoiLop kl,Lop l " +
181                            " where sv.MaLop=l.MaLop and l.MaKhoiLop=kl.MaKhoiLop " +
182                            " and kl.MaKhoiLop='" + MaKhoiLop + "' and TrungBinhCuoiKyHe10 < 3.5";
183                     break;
184             }
185             DataTable dt4 = da.GetTable(sql4);
186             da.ExcuteNonQuery("delete from Loptc where MaSV in ( select MaSV from Loptc )");
187             da.ExcuteNonQuery("delete from Lopdiem10 where MaSV in ( select MaSV from Lopdiem10 )");
188             da.ExcuteNonQuery("delete from DTB where MaSV in ( select MaSV from DTB )");
189             return dt4;
190         }

289         public DataTable XemDiemLop(string MaHK,string MaLop)
290         {
291             SqlConnection con = new SqlConnection();
292             ChuanBi3Table(MaHK);
293             string sql4="select sv.MaSV,TenSV,TrungBinhCuoiKyHe10 "+
294                           " from DTB left join SinhVien sv on DTB.MaSV=sv.MaSV "+
295                             " where MaLop='"+MaLop+"'";
296             DataTable dt4 = da.GetTable(sql4);
297             da.ExcuteNonQuery("delete from Loptc where MaSV in ( select MaSV from Loptc )");
298             da.ExcuteNonQuery("delete from Lopdiem10 where MaSV in ( select MaSV from Lopdiem10 )");
299             da.ExcuteNonQuery("delete from DTB where MaSV in ( select MaSV from DTB )");
300             return dt4;
301         }

77         private void btnXuatRaExcel_Click(object sender, EventArgs e)
78         {
79              try
80             {
81
82                 Excel.Application objExcelApp = new Excel.Application();
83                 Excel.Workbook objExcelWorkbook = objExcelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
84                 Excel.Worksheet objSheet = (Excel.Worksheet)objExcelWorkbook.Worksheets[1];
85                 //objSheet.Cells.Interior.Color.ToString() = 'blue';
86                 objSheet = (Excel.Worksheet)objExcelWorkbook.Sheets[1];
87                 objSheet.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
88                 objSheet.Cells[2, 5] = "BẢNG ĐIỂM CỦA LỚP";
89                 objSheet.Cells[3, 3] = "Mã Học Kỳ : "+cmbMaHK.SelectedValue.ToString()+"";
90                 objSheet.Cells[4, 3] = "Mã Lớp : " + cmbMaLop.SelectedValue.ToString() + "";
91                 objSheet.Cells[5, 4] = "Mã Sinh Viên";
92                 objSheet.Cells[5, 5] = "Tên Sinh Viên";
93                 objSheet.Cells[5, 6] = "Điểm Trung Bình";
94                 //select sv.MaSV,TenSV,TrungBinhCuoiKyHe10
95
96                 for (int i = 0; i < dgvKetQua.Rows.Count ; i++)
97                 {
98                     for (int j = 0; j < dgvKetQua.Columns.GetColumnCount(DataGridViewElementStates.Displayed); j++)
99                     {
100                         objSheet.Cells[i + 6, j + 4] = dgvKetQua.Rows[i].Cells[j].Value.ToString();
101                         objSheet.Columns.AutoFit();
102                     }
103                 }
104                 objExcelApp.Visible = true;
105             }
106             catch (Exception ex)
107             {
108                 MessageBox.Show(ex.Message);
109             }
110         }

14         static void Main()
15         {
16             Application.EnableVisualStyles();
17             Application.SetCompatibleTextRenderingDefault(false);
18             Application.Run(new Form9());
19         }

30         private void btbatdauthi_Click(object sender, EventArgs e)
31         {
32             try
33             {
34                 if (tbten.Text == "" || tbmathisinh.Text == "")
35                 {
36                     MessageBox.Show("Bạn phải điền đầy đủ thông tin");
37                 }
38                 else
39                 {
40                     string TenThSinh = tbten.Text;
41                     string MaThiSinh = tbmathisinh.Text;
42                     string strSQL = "insert THISINHTHI values('" + MaThiSinh + "','" + TenThSinh + "')";
43                     SqlCommand ObjCmd = new SqlCommand(strSQL, ObjCon);
44                     ObjCmd.ExecuteNonQuery();
45                     if (MessageBox.Show("Bạn có chắc chắn thi không ?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
46                     {
47
48                         string dienten = this.tbten.Text;//chuyen thong tin sang form thithat
49                         string dienma = this.tbmathisinh.Text;
50                         frmThiThat thithat = new frmThiThat(dienten, dienma);
51                         thithat.ShowDialog();
52                         this.Close();
53
54
55                     }
56
57                 }
58             }
59             catch
60             {
61                 MessageBox.Show("Mã thí sinh trùng", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
62             }
63
64     }

67         private void btnthem_Click(object sender, EventArgs e)
68         {
69             try
70             {
71             string mats = textBox1.Text;
72             string hoten = textBox2.Text;
73             string ngaysinh = textBox3.Text;
74             string gioitinh = textBox4.Text;
75             string quequan = textBox5.Text;
76             string sodt = textBox6.Text;
77             string email = textBox7.Text;
78
79             string strSQL = "insert SINHVIEN values('" + mats + "','" + hoten + "','" + ngaysinh + "','" + gioitinh + "','" + quequan + "','" + sodt + "','" + email + "')";
80             sqlcmd = new SqlCommand(strSQL, sqlcon);
81             sqlcmd.ExecuteNonQuery();
82             MessageBox.Show("B?n dã dang ký thành công");
83             load();
84             }
85                 catch
86             {
87                 MessageBox.Show("Trùng mã sinh viên !!!");
88                 }
89
90         }