public DataTable getAllId()
{
using (QLTTEntities qltt = new QLTTEntities())
{
var dataList = from r in qltt.LOPHOCs select r.MALOP;
DataTable rtnTable = new DataTable();
rtnTable.Columns.Add("MALOP", typeof(string));
foreach (string data in dataList)
{
rtnTable.Rows.Add(data);
}
if (rtnTable.Rows[0][0] == DBNull.Value)
{
return(null);
}
else
{
return(rtnTable);
}
}
}
public DataTable getAll()
{
try
{
using (QLTTEntities qltt = new QLTTEntities())
{
var dkList = from r in qltt.DANGKies select r;
DataTable rtnTable = new DataTable();
rtnTable.Columns.Add("MAHV", typeof(string));
rtnTable.Columns.Add("MALOP", typeof(string));
foreach (DANGKY dk in dkList)
{
rtnTable.Rows.Add(dk.MAHV, dk.MALOP);
}
return(rtnTable);
}
}
catch
{
return(null);
}
}
public int insert(string malop, int maphong, string malh)
{
try
{
using (QLTTEntities qltt = new QLTTEntities())
{
TKB data = new TKB()
{
MALOP = malop,
MAPHONG = maphong,
MALH = malh
};
qltt.TKBs.Add(data);
qltt.SaveChanges();
return(1);
}
}
catch
{
return(0);
}
}
public DataTable getAll()
{
using (QLTTEntities qltt = new QLTTEntities())
{
var phList = from r in qltt.PHONGHOCs select r;
DataTable rtnTable = new DataTable();
rtnTable.Columns.Add("MAPHONG", typeof(string));
rtnTable.Columns.Add("TANG", typeof(int));
rtnTable.Columns.Add("SOPHONG", typeof(int));
foreach (PHONGHOC ph in phList)
{
rtnTable.Rows.Add(ph.MAPHONG, ph.TANG, ph.SOPHONG);
}
if (rtnTable.Rows[0][0] == DBNull.Value)
{
return(null);
}
else
{
return(rtnTable);
}
}
}
public DataTable joinhocphi_mahv(string mahv)
{
DataTable table = new DataTable();
table.Columns.Add("MAHV", typeof(string));
table.Columns.Add("TENHV", typeof(string));
table.Columns.Add("MALOP", typeof(string));
table.Columns.Add("SOTIEN", typeof(string));
table.Columns.Add("NGTT", typeof(DateTime));
table.Columns.Add("TIENTHU", typeof(string));
table.Columns.Add("TIENNO", typeof(string));
using (QLTTEntities qltt = new QLTTEntities())
{
var k = from hv in qltt.HOCVIENs
from hp in qltt.HOCPHIs
from tt in qltt.THONGTINHOCPHIs
from lh in qltt.LOPHOCs
where hv.MAHV == tt.MAHV && lh.MAHP == hp.MAHP && tt.MALOP == lh.MALOP && hv.MAHV == mahv
select new
{
MAHV = hv.MAHV,
HOTEN = hv.HOTEN,
MALOP = lh.MALOP,
SOTIEN = hp.SOTIEN,
NGTT = tt.NGAYDONG,
TIENTHU = tt.SOTIENDADONG,
TIENNO = tt.SOTIENCONNO,
};
foreach (var item in k)
{
table.Rows.Add(item.MAHV, item.HOTEN, item.MALOP, item.SOTIEN, item.NGTT, item.TIENTHU, item.TIENNO);
}
return(table);
}
}
public int insert(string matt, string magv, DateTime ngaynhan, int tinhtrang)
{
try
{
using (QLTTEntities qltt = new QLTTEntities())
{
TT_LUONGGV data = new TT_LUONGGV()
{
MATT = matt,
MAGV = magv,
NGAYNHAN = ngaynhan,
TINHTRANG = tinhtrang
};
qltt.TT_LUONGGV.Add(data);
qltt.SaveChanges();
return(1);
}
}
catch
{
return(0);
}
}
public int update(string malh, string ngayhoc, string giohoc)
{
try
{
using (QLTTEntities qltt = new QLTTEntities())
{
LICHHOC lh = qltt.LICHHOCs.Where(p => p.MALH == malh).FirstOrDefault();
if (lh != null)
{
lh.NGAYHOC = ngayhoc;
lh.GIOHOC = giohoc;
qltt.SaveChanges();
return(1);
}
return(0);
}
}
catch
{
return(0);
}
}
public DataTable getAll()
{
using (QLTTEntities qltt = new QLTTEntities())
{
var lhList = from r in qltt.LICHHOCs select r;
DataTable rtnTable = new DataTable();
rtnTable.Columns.Add("MALH", typeof(string));
rtnTable.Columns.Add("NGAYHOC", typeof(string));
rtnTable.Columns.Add("GIOHOC", typeof(string));
foreach (LICHHOC lh in lhList)
{
rtnTable.Rows.Add(lh.MALH, lh.NGAYHOC, lh.GIOHOC);
}
if (rtnTable.Rows[0][0] == DBNull.Value)
{
return(null);
}
else
{
return(rtnTable);
}
}
}
public int update(string malop, int maphong, string malh)
{
try
{
using (QLTTEntities qltt = new QLTTEntities())
{
TKB tkb = qltt.TKBs.Where(p => p.MALH == malop).FirstOrDefault();
if (tkb != null)
{
tkb.MAPHONG = maphong;
tkb.MALH = malh;
qltt.SaveChanges();
return(1);
}
return(0);
}
}
catch
{
return(0);
}
}
/// <summary>
/// trả lại bảng chứa thông tin của học viên (chỉ chứa 1 record)
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public DataTable get(string code)
{
using (QLTTEntities qltt = new QLTTEntities())
{
HOCVIEN hv = qltt.HOCVIENs.Where(p => p.MAHV == code).FirstOrDefault();
if (hv != null)
{
DataTable rtnTable = new DataTable();
rtnTable.Columns.Add("MAHV", typeof(string));
rtnTable.Columns.Add("HOTEN", typeof(string));
rtnTable.Columns.Add("GIOITINH", typeof(int));
rtnTable.Columns.Add("NGSINH", typeof(DateTime));
rtnTable.Columns.Add("DIACHI", typeof(string));
rtnTable.Columns.Add("SDT", typeof(string));
rtnTable.Columns.Add("EMAIL", typeof(string));
rtnTable.Columns.Add("NGAYDK", typeof(DateTime));
rtnTable.Columns.Add("TINHTRANG", typeof(int));
rtnTable.Rows.Add(hv.MAHV, hv.HOTEN, hv.GIOITINH, hv.NGSINH, hv.DIACHI, hv.SDT, hv.EMAIL, hv.NGAYDK, hv.TINHTRANG);
return(rtnTable);
}
}
return(null);
}
public int update(string mahv, string malop, string sotiendadong, string sotienconno, DateTime ngaydong)
{
try
{
using (QLTTEntities qltt = new QLTTEntities())
{
THONGTINHOCPHI tt = qltt.THONGTINHOCPHIs.Where(p => p.MAHV == mahv).FirstOrDefault();
if (tt != null)
{
tt.SOTIENDADONG = sotiendadong;
tt.SOTIENCONNO = sotienconno;
tt.NGAYDONG = ngaydong;
qltt.SaveChanges();
return(1);
}
;
return(0);
}
}
catch
{
return(0);
}
}
public DataTable findDiemThiByMaLop(string maLop)
{
using (QLTTEntities qltt = new QLTTEntities())
{
var gk = from x in qltt.THIs where x.MALOP == maLop && x.MAKT == "KTGK" select x;
var ck = from x in qltt.THIs where x.MALOP == maLop && x.MAKT == "KTCK" select x;
var info = gk.FullOuterJoin(ck, g => g.MALOP, c => c.MALOP, (g, c, MALOP) => new { MAHV = g.MAHV, DIEMGK = g.DIEMTHI, DIEMCK = c.DIEMTHI });
DataTable rtnTable = new DataTable();
rtnTable.Columns.Add("MAHV", typeof(string));
rtnTable.Columns.Add("MALOP", typeof(string));
rtnTable.Columns.Add("HOTEN", typeof(string));
rtnTable.Columns.Add("DIEMGK", typeof(int));
rtnTable.Columns.Add("DIEMCK", typeof(int));
rtnTable.Columns.Add("TONGDIEM", typeof(float));
rtnTable.Columns.Add("KETQUA", typeof(int));
foreach (var i in info)
{
rtnTable.Rows.Add(i.MAHV, maLop, qltt.HOCVIENs.Where(p => p.MAHV == i.MAHV).FirstOrDefault().HOTEN, i.DIEMGK, i.DIEMCK, (i.DIEMGK + i.DIEMCK) / 2, (float)(i.DIEMGK + i.DIEMCK) / 2 >= 5 ? 1 : 0);
}
return(rtnTable);
}
}
