public IActionResult UpdateUser([FromBody] Dictionary <string, object> formData)
{
var model = new TKB();
model.id = int.Parse(formData["id"].ToString());
model.ten = formData["ten"].ToString();
var hinhanh = formData["hinhanh"];
if (hinhanh != null)
{
var arrData = hinhanh.ToString().Split(';');
if (arrData.Length == 3)
{
var savePath = $@"assets/images/{arrData[0]}";
model.hinhanh = $"{savePath}";
SaveFileFromBase64String(savePath, arrData[2]);
}
}
else
{
var tkb = _itemBusiness.GetDatabyID("" + model.id);
model.hinhanh = tkb.hinhanh;
}
var kq = _itemBusiness.Update(model);
return(Ok(kq));
}
private void btnAll_Click(object sender, EventArgs e)
{
cn.Open();
string 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";
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();
}
private void btnAll_Click(object sender, EventArgs e)
{
cn.Open();
string sql = "select TKB.MaPhong, TKB.MaLH, TKB.TietBD, TKB.TietKT, TKB.Ngay, MP.TenNgMuon, TP.TenNgTra , MP.Note from TKBieu TKB, MuonPhong MP, TraPhong TP where TKB.MaMP like MP.MaMuonPhong and TKB.MaTP like TP.MaTraPhong";
List <TKB> list = new List <TKB>();
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader dr = cmd.ExecuteReader();
int IDPhong, IDLop, TietBD, TietKT;
DateTime NgayMuon;
string NguoiMuon, NguoiTra, LyDo;
while (dr.Read())
{
IDPhong = dr.GetInt32(0);
IDLop = dr.GetInt32(1);
TietBD = dr.GetInt32(2);
TietKT = dr.GetInt32(3);
NgayMuon = dr.GetDateTime(4);
NguoiMuon = dr.GetString(5);
NguoiTra = dr.GetString(6);
LyDo = dr.GetString(7);
TKB tkbieu = new TKB(IDPhong, IDLop, TietBD, TietKT, NgayMuon, NguoiMuon, NguoiTra, LyDo);
list.Add(tkbieu);
}
dr.Close();
dgvTKB.DataSource = list;
cn.Close();
}
private void btnAll_Click(object sender, EventArgs e)
{
cn.Open();
string 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";
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 int delete(string code)
{
using (QLTTEntities qltt = new QLTTEntities())
{
TKB data = qltt.TKBs.Where(p => p.MALH == code).FirstOrDefault();
if (data != null)
{
qltt.TKBs.Remove(data);
qltt.SaveChanges();
return(1);
}
}
return(0);
}
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 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 DataTable get(string code)
{
using (QLTTEntities qltt = new QLTTEntities())
{
TKB data = qltt.TKBs.Where(p => p.MALH == code).FirstOrDefault();
if (data != null)
{
DataTable rtnTable = new DataTable();
rtnTable.Columns.Add("MALOP", typeof(string));
rtnTable.Columns.Add("MAPHONG", typeof(int));
rtnTable.Columns.Add("MALH", typeof(string));
rtnTable.Rows.Add(data.MALOP, data.MAPHONG, data.MALH);
return(rtnTable);
}
}
return(null);
}
public IActionResult CreateTintuc([FromBody] Dictionary <string, object> formData)
{
var model = new TKB();
model.ten = formData["ten"].ToString();
model.hinhanh = formData["hinhanh"].ToString();
if (model.hinhanh != null)
{
var arrData = model.hinhanh.Split(';');
if (arrData.Length == 3)
{
var savePath = $@"assets/images/{arrData[0]}";
model.hinhanh = $"{savePath}";
SaveFileFromBase64String(savePath, arrData[2]);
}
}
//model.id = Guid.NewGuid().ToString();
_itemBusiness.Create(model);
return(Ok(model));
}
public bool Create(TKB model)
{
string msgError = "";
try
{
var result = _dbHelper.ExecuteScalarSProcedureWithTransaction(out msgError, "sp_tkb_create",
/* "@id", model.id,*/
"@ten", model.ten,
"@hinhanh", model.hinhanh);
if ((result != null && !string.IsNullOrEmpty(result.ToString())) || !string.IsNullOrEmpty(msgError))
{
throw new Exception(Convert.ToString(result) + msgError);
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
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 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);
}
}
public bool Create(TKB model)
{
return(_res.Create(model));
}
public bool Update(TKB model)
{
return(_res.Update(model));
}
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();
}
