public DataTable ShowGV()
{
string sql = "select * from tbl_GIAOVIEN";
DataTable dt = new DataTable();
dt = kn.GetTable(sql);
return(dt);
}
public DataTable ShowHang()
{
string sql = "select * from Hang2";
DataTable dt = new DataTable();
dt = data.GetTable(sql);
return(dt);
}
public DataTable ShowNhaCungCap()
{
string sql = "select * from NhaCungCap2";
DataTable dt = new DataTable();
dt = data.GetTable(sql);
return(dt);
}
//Lấy thông tin
public DataTable onLoadHoaDon()
{
string sql = "select * from HoaDon2";
DataTable dt = new DataTable();
dt = data.GetTable(sql);
return(dt);
}
public DataTable ShowMH()
{
string sql = "select * from tbl_MONHOC";
DataTable dt = new DataTable();
dt = da.GetTable(sql);
return(dt);
}
public DataTable ShowTrinhDo()
{
string sql = "select * from TrinhDo2";
DataTable dt = new DataTable();
dt = data.GetTable(sql);
return(dt);
}
public DataTable ShowTiepThi()
{
string sql = "select KhachHang2.MaNV , NhanVien2.TenNV , COUNT(*)as'TiepThi' from KhachHang2 inner join NhanVien2 on KhachHang2.MaNV = NhanVien2.MaNV GROUP BY KhachHang2.MaNV , NhanVien2.TenNV";
DataTable dt = new DataTable();
dt = data.GetTable(sql);
return(dt);
}
public DataTable ShowLichSuGia()
{
string sql = "select ls.MaHH, ls.NgayBatDau, ls.NgayKetThuc, ls.DonGia, ls.NgayCapNhat , h.TenHang from LichSuGia2 ls , Hang2 h where ls.MaHH = h.MaHH";
DataTable dt = new DataTable();
dt = data.GetTable(sql);
return(dt);
}
public DataTable searchHoaDonById(String maHD)
{
string sql = "select * from HoaDonView hdv where hdv.MaHD like '%" + maHD + "%'";
DataTable dt = new DataTable();
dt = data.GetTable(sql);
return(dt);
}
public DataTable ShowKhoa()
{
string sql = " select * from tbl_KHOA";
DataTable dt = new DataTable();
dt = kn.GetTable(sql);
return(dt);
}
public DataTable ShowDoanhThu()
{
string sql = "select * from DoanhThu2";
DataTable dt = new DataTable();
dt = data.GetTable(sql);
return(dt);
}
public DataTable ShowNghanh()
{
string sql = " select * from tbl_NGHANH";
DataTable dt = new DataTable();
dt = kn.GetTable(sql);
return(dt);
}
public DataTable ShowData()
{
string sql = " select Distinct sv.MaSV,sv.TenSV,sv.GioiTinh,sv.SDT, sv.DiaChi, sv.Email ,sv.NgaySinh,sv.MaLop from tbl_SINHVIEN sv ,tbl_LOP lp "
+ " Where sv.MaLop = lp.MaLop and sv.MaLop = '' and MaSV = '' ";
DataTable dt = new DataTable();
dt = kn.GetTable(sql);
return(dt);
}
public DataTable showTK()
{
try
{
string sql = @"select * from tbl_NGUOIDUNG";
DataTable dt = new DataTable();
dt = kn.GetTable(sql);
return(dt);
}
catch (Exception)
{
throw;
}
}
public DataTable GetMaSVByMaLop(string key)
{
try
{
string sql = @" select Distinct sv.MaSV ,sv.TenSV
from tbl_SINHVIEN sv ,tbl_LOP lp
Where sv.MaLop = lp.MaLop and sv.MaLop like N'%" + key + "%'";
DataTable dt = new DataTable();
dt = kn.GetTable(sql);
return(dt);
}
catch (Exception)
{
throw;
}
}
public DataTable ShowChiTietHD()
{
string sql = "select * from ChiTietHoaDonView";
DataTable dt = new DataTable();
dt = data.GetTable(sql);
return(dt);
}
public DataTable showSV()
{
string sql = " select sv.MaSV, sv.TenSV, sv.GioiTinh, sv.SDT, sv.DiaChi, sv.Email ,sv.NgaySinh from tbl_SINHVIEN";
DataTable dt = new DataTable();
dt = kn.GetTable(sql);
return(dt);
}
//public string MaSV { get;set;}
//public string TenSV { get; set; }
//public string GioiTinh { get; set; }
//public int SDT { get; set; }
//public DateTime NgaySinh { get; set; }
//public string MaLop { get; set; }
public DataTable GetAllSV()
{
string sql = " select * from tbl_SINHVIEN";
DataTable dt = new DataTable();
dt = da.GetTable(sql);
return(dt);
}
public DataTable Search(string key)
{
DataTable dt = new DataTable();
try
{
string sql = string.Empty;
if (key == "")
{
sql = @" Select distinct A.MaNghanh,A.TenNghanh,A.SoLop,B.MaKhoa,A.SDT,A.Email,A.DiaChi
from tbl_NGHANH A
INNER JOIN tbl_KHOA B
ON A.MaKhoa = B.MaKhoa
WHERE B.MaKhoa = '' AND B.TenKhoa='' AND A.MaNghanh='' AND A.TenNghanh='' ";
}
else
{
sql = @" Select distinct A.MaNghanh,A.TenNghanh,A.SoLop,B.MaKhoa,A.SDT,A.Email,A.DiaChi
from tbl_NGHANH A
INNER JOIN tbl_KHOA B
ON A.MaKhoa = B.MaKhoa
WHERE ";
if (!string.IsNullOrEmpty(key))
{
sql += " B.MaKhoa = '" + key + "' ";
}
if (!string.IsNullOrEmpty(key))
{
sql += " OR B.TenKhoa like N'%" + key + "%' ";
}
if (!string.IsNullOrEmpty(key))
{
sql += " OR A.MaNghanh = '" + key + "' ";
}
if (!string.IsNullOrEmpty(key))
{
sql += " OR A.TenNghanh like N'%" + key + "%' ";
}
}
sql += " GROUP BY A.MaNghanh,A.TenNghanh,A.SoLop,B.MaKhoa,A.SDT,A.Email,A.DiaChi ";
dt = kn.GetTable(sql);
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public DataTable showDiem()
{
// string sql = "select distinct d.MaSV, d.MaLop, d.MaMH,d.DiemTP,d.DiemThi,d.LanThi,d.HocKi,d.SoTietNghi from tbl_DIEM d";
string sql = @"SELECT DISTINCT d.MaSV, d.MaLop, d.MaMH, d.DiemQTHeS1,d.DiemQTHeS2,
Round(((d.DiemQTHeS1 + d.DiemQTHeS2*2)/3),1) AS DiemTP
,d.DiemThi,d.LanThi,d.HocKi,d.SoTietNghi
,Round(((Round(((d.DiemQTHeS1 + d.DiemQTHeS2*2)/3),1)*3+d.DiemThi*7)/10),1) as DiemTBHP
, CASE WHEN Round(((Round(((d.DiemQTHeS1 + d.DiemQTHeS2*2)/3),1)*3+d.DiemThi*7)/10),1) >=9 THEN 'A+'
WHEN Round(((Round(((d.DiemQTHeS1 + d.DiemQTHeS2*2)/3),1)*3+d.DiemThi*7)/10),1) between 8.5 AND 8.9 THEN 'A'
WHEN Round(((Round(((d.DiemQTHeS1 + d.DiemQTHeS2*2)/3),1)*3+d.DiemThi*7)/10),1) between 8.0 AND 8.4 THEN 'B+'
WHEN Round(((Round(((d.DiemQTHeS1 + d.DiemQTHeS2*2)/3),1)*3+d.DiemThi*7)/10),1) between 7.0 AND 7.9 THEN 'B'
WHEN Round(((Round(((d.DiemQTHeS1 + d.DiemQTHeS2*2)/3),1)*3+d.DiemThi*7)/10),1) between 6.5 AND 6.9 THEN 'C+'
WHEN Round(((Round(((d.DiemQTHeS1 + d.DiemQTHeS2*2)/3),1)*3+d.DiemThi*7)/10),1) between 5.5 AND 6.4 THEN 'C'
WHEN Round(((Round(((d.DiemQTHeS1 + d.DiemQTHeS2*2)/3),1)*3+d.DiemThi*7)/10),1) between 4.0 AND 5.4 THEN 'D'
WHEN Round(((Round(((d.DiemQTHeS1 + d.DiemQTHeS2*2)/3),1)*3+d.DiemThi*7)/10),1) < 4.0 THEN 'F'
END AS DiemChuTBHP
FROM tbl_DIEM d
GROUP BY d.MaSV, d.MaLop, d.MaMH,d.DiemTP,d.DiemThi,d.LanThi,d.HocKi,d.SoTietNghi,d.DiemTBHP ,d.DiemQTHeS1,d.DiemQTHeS2 ";
DataTable dt = new DataTable();
dt = kn.GetTable(sql);
return(dt);
}
public DataTable ShowLop()
{
string sql = " select * from tbl_LOP";
DataTable dt = new DataTable();
dt = da.GetTable(sql);
return(dt);
}