New

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         }

191         public int CheckLogin(string name, string pass)
192         {
193             SqlConnection con = da.GetConnect();
194             SqlCommand cmd = new SqlCommand("select *from Users Where Username='"+name+"' and Password='"+pass+"'",con);
195             con.Open();
196             SqlDataReader dr = cmd.ExecuteReader();
197             if (dr.Read())
198             {
199                 if (dr[2].ToString() == "admin")
200                 {
201                     return 1;
202                 }
203                 else
204                 {
205                     return 2;
206                 }
207             }
208             else
209             {
210                 return -1;
211             }
212         }

213         public string TinhDiemTB(string MaSV, string MaHK)
214         {
215             SqlConnection con = da.GetConnect();
216             con.Open();
217             //view tc
218             string sql1 = " select 'sotinchi'=sum(SoTC) " +
219                                " from KetQua kq,HocPhan hp,NhomHocPhan nhp,KeHoachGiangDay khgd " +
220                                " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khgd.MaKHGD=nhp.MaKHGD and MaSV='"+MaSV+"' and MaHK='"+MaHK+"' " +
221                                " group by MaSV ";
222             SqlCommand cmd1 = new SqlCommand(sql1, con);
223             string tc = cmd1.ExecuteScalar().ToString();
224             // view diem10
225             string sql2 = " select 'cuoiky'=sum(DiemTk10*SoTC) " +
226                                " from KetQua kq,NhomHocPhan nhp,HocPhan hp,KeHoachGiangDay khdt " +
227                                " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khdt.MaKHGD=nhp.MaKHGD and MaSV='"+MaSV+"' and MaHK='"+MaHK+"' " +
228                                " group by MaSV ";
229             SqlCommand cmd2 = new SqlCommand(sql2, con);
230             string diem10 = cmd2.ExecuteScalar().ToString();
231             //view diem4
232             string sql3 = " select 'cuoiky'=sum(DiemTk4*SoTC) " +
233                                " from KetQua kq,NhomHocPhan nhp,HocPhan hp,KeHoachGiangDay khdt " +
234                                " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khdt.MaKHGD=nhp.MaKHGD and MaSV='"+MaSV+"' and MaHK='"+MaHK+"' " +
235                                " group by MaSV ";
236             SqlCommand cmd3 = new SqlCommand(sql3, con);
237             string diem4 = cmd3.ExecuteScalar().ToString();
238
239
240             string ketqua="";
241             ketqua = Convert.ToDouble(diem10) / Convert.ToDouble(tc) + "," + Convert.ToDouble(diem4) / Convert.ToDouble(tc) ;
242             return ketqua;
243         }

244         public string TinhDiemTB(string MaSV)
245         {
246             SqlConnection con = da.GetConnect();
247             con.Open();
248             //view tc
249             string sql1 = " select 'sotinchi'=sum(SoTC) " +
250                                " from KetQua kq,HocPhan hp,NhomHocPhan nhp,KeHoachGiangDay khgd " +
251                                " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khgd.MaKHGD=nhp.MaKHGD and MaSV='" + MaSV + "'" +
252                                " group by MaSV ";
253             SqlCommand cmd1 = new SqlCommand(sql1, con);
254             string tc = cmd1.ExecuteScalar().ToString();
255             // view diem10
256             string sql2 = " select 'cuoiky'=sum(DiemTk10*SoTC) " +
257                                " from KetQua kq,NhomHocPhan nhp,HocPhan hp,KeHoachGiangDay khdt " +
258                                " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khdt.MaKHGD=nhp.MaKHGD and MaSV='" + MaSV + "'" +
259                                " group by MaSV ";
260             SqlCommand cmd2 = new SqlCommand(sql2, con);
261             string diem10 = cmd2.ExecuteScalar().ToString();
262             //view diem4
263             string sql3 = " select 'cuoiky'=sum(DiemTk4*SoTC) " +
264                                " from KetQua kq,NhomHocPhan nhp,HocPhan hp,KeHoachGiangDay khdt " +
265                                " where kq.MaNhomHP=nhp.MaNhomHP and nhp.MaHP=hp.MaHP and khdt.MaKHGD=nhp.MaKHGD and MaSV='" + MaSV + "'" +
266                                " group by MaSV ";
267             SqlCommand cmd3 = new SqlCommand(sql3, con);
268             string diem4 = cmd3.ExecuteScalar().ToString();
269
270
271             string ketqua = "";
272             ketqua = Convert.ToDouble(diem10) / Convert.ToDouble(tc) + "," + Convert.ToDouble(diem4) / Convert.ToDouble(tc);
273             return ketqua;
274         }

275         public bool Proc_Insert(string proc,string test)
276         {
277             SqlConnection con = da.GetConnect();
278             con.Open();
279             SqlCommand cmd = new SqlCommand(proc, con);
280             cmd.CommandType = CommandType.StoredProcedure;
281             cmd.Parameters.Add("@test", SqlDbType.NVarChar).Value = test;
282             SqlDataReader dr = cmd.ExecuteReader();
283             if (dr.Read())
284             {
285                 return false;
286             }
287             return true;
288         }

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         }

12         public SqlConnection GetConnect()
13         {
14             return new SqlConnection("server=TY-PC;User id=sa;password=04091990;database=CNPM_1");
15         }

16         public DataTable GetTable(string sql)
17         {
18             SqlConnection con = GetConnect();
19             SqlDataAdapter ad = new SqlDataAdapter(sql, con);
20             DataTable dt = new DataTable();
21             ad.Fill(dt);
22             return dt;
23             con.Close();
24         }

25         public void ExcuteNonQuery(string sql)
26         {
27             SqlConnection con = GetConnect();
28             con.Open();
29             SqlCommand cmd = new SqlCommand(sql, con);
30             cmd.ExecuteNonQuery();
31             cmd.Dispose();
32             con.Close();
33         }

33         public override bool NewGenerator {
34             get {
35                 return true;
36             }
37             set {
38                 // Do nothing
39             }
40         }