public bool LayThongTinSinhVien(int intUserID, ref SinhVien objSinhVien)
{
try
{
using (var db = new Project_16HCB_CSDLEntities())
{
SqlParameter paMSSV = new SqlParameter("@MSSV", intUserID);
var result = db.Database.SqlQuery <SinhVien>("exec sp_getThongTinSinhVien @MSSV", paMSSV).ToList();
objSinhVien = result[0];
var lstVanTay = db.VANTAYs.Where(i => i.C_userID == intUserID).ToList();
List <VanTay> lstTemp = new List <VanTay>();
for (int i = 0; i < lstVanTay.Count(); i++)
{
VanTay objVanTayDTO = new VanTay();
objVanTayDTO._id = lstVanTay[i].C_id;
objVanTayDTO._mavantay = lstVanTay[i].C_maVanTay;
objVanTayDTO._url = lstVanTay[i].C_url;
objVanTayDTO._userid = intUserID;
lstTemp.Add(objVanTayDTO);
}
objSinhVien._lstVanTay = lstTemp;
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool TimKiemSinhVien(SinhVien sv, ref List <SinhVien> lstSinhVien, string strTuKhoa, int intPageIndex, int intPageSize)
{
try
{
using (var db = new Project_16HCB_CSDLEntities())
{
SqlParameter paTuKhoa = new SqlParameter("@KEYSEARCH", strTuKhoa);
SqlParameter paLop = new SqlParameter("@SCHOOLCLASSID", sv._malop);
SqlParameter paKhoa = new SqlParameter("@DIVIONID", sv._makhoa);
SqlParameter paDaLay = new SqlParameter("@GETFINGER", (sv._dalay >= 0 ? 1 : 0));
SqlParameter paPageIndex = new SqlParameter("@PAGEINDEX", intPageIndex);
SqlParameter paPageSize = new SqlParameter("@PAGESIZE", intPageSize);
var result = db.Database.SqlQuery <SinhVien>("exec sp_timkiemThongTinSinhVien @KEYSEARCH,@SCHOOLCLASSID,@DIVIONID,@GETFINGER,@PAGEINDEX,@PAGESIZE",
paTuKhoa,
paLop,
paKhoa,
paDaLay,
paPageIndex,
paPageSize).ToList();
lstSinhVien = result;
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public List <MonHoc> LayDanhSachMonHoc(ref string errorMsg)
{
var lstResult = new List <MonHoc>();
string sql = "exec usp_layDanhSachMonHoc @errorMsg out";
errorMsg = "";
using (var db = new Project_16HCB_CSDLEntities())
{
SqlParameter pErrorMsg = new SqlParameter("@errorMsg", System.Data.SqlDbType.NVarChar, 4000)
{
Direction = System.Data.ParameterDirection.Output
};
try
{
lstResult = db.Database.SqlQuery <MonHoc>(sql, pErrorMsg).ToList();
}
catch (Exception ex)
{
if (pErrorMsg.Value != null)
{
errorMsg = pErrorMsg.Value.ToString();
}
else
{
errorMsg = ex.Message;
}
}
return(lstResult);
}
}
// GET: api/HocKy
public List <HOCKY> Get()
{
using (var db = new Project_16HCB_CSDLEntities())
{
List <HOCKY> dsHocKy = new List <HOCKY>();
dsHocKy = db.HOCKies.Select(h => h).ToList();
return(dsHocKy);
}
}
// GET: api/NamHocHK
public List <MONHOC_HOCKY> Get()
{
using (var db = new Project_16HCB_CSDLEntities())
{
List <MONHOC_HOCKY> dsMonHocHocKy = new List <MONHOC_HOCKY>();
dsMonHocHocKy = db.MONHOC_HOCKY.Select(h => h).ToList();
return(dsMonHocHocKy);
}
}
public int convertUserId(int userid)
{
using (var db = new Project_16HCB_CSDLEntities())
{
SqlParameter parameter1 = new SqlParameter("@userid", userid);
var result = db.Database.SqlQuery <int>("exec ConverUserIDToMSSV @userid", parameter1).FirstOrDefault();
return(result);
}
}
public List <HOCPHAN> GetDanhSachHP(int mssv)
{
using (var db = new Project_16HCB_CSDLEntities())
{
SqlParameter parameter1 = new SqlParameter("@MSSV", mssv);
var result = db.Database.SqlQuery <HOCPHAN>("exec GetDanhSachHP @MSSV", parameter1).ToList();
return(result);
}
}
public List <InfoDiemDanhContainer> GetInfoDiemDanh(int mssv, int tkb)
{
using (var db = new Project_16HCB_CSDLEntities())
{
SqlParameter parameter1 = new SqlParameter("@MSSV", mssv);
SqlParameter parameter2 = new SqlParameter("@MaTKB", tkb);
var result = db.Database.SqlQuery <InfoDiemDanhContainer>("exec GetInfoDiemDanh @MSSV, @MaTKB", parameter1, parameter2).ToList();
return(result);
}
}
public ActionResult Login([System.Web.Http.FromBody] string username, [System.Web.Http.FromBody] string password)
{
Response.AppendHeader("Access-Control-Allow-Origin", "*");
using (var db = new Project_16HCB_CSDLEntities())
{
var account = db.ACCOUNTs.Where(n => n.C_username == username &&
n.C_password == password).FirstOrDefault();
object rs;
if (account != null)
{
rs = new { data = account, status = "OK" };
}
else
{
rs = new { data = account, status = "FALSE" };
}
return(Json(account));
//SqlConnection con = new SqlConnection(z.Database.Connection.ConnectionString);
//con.Open();
//SqlCommand cmd = new SqlCommand("select * from Bank", con);
//SqlDataReader dr = cmd.ExecuteReader();
//List<Bank> list = new List<Bank>();
//while (dr.Read())
//{
// Bank b = new Bank();
// b.id = (int)dr["id"];
// b.NameBank = (string)dr["NameBank"];
// b.DiaChi = (string)dr["DiaChi"];
// list.Add(b);
//}
//con.Close();
//return Json(list);
//SqlCommand cmd = new SqlCommand("sp_getATM", con);
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add(new SqlParameter("@id", username));
//SqlDataReader dr = cmd.ExecuteReader();
//List<Bank> list = new List<Bank>();
//while (dr.Read())
//{
// Bank b = new Bank();
// b.id = (int)dr["id"];
// b.NameBank = (string)dr["NameBank"];
// b.DiaChi = (string)dr["DiaChi"];
// list.Add(b);
//}
//con.Close();
//return Json(list);
}
}
public HttpResponseMessage XemDanhSachKhoa()
{
HttpResponseMessage res = null;
using (var db = new Project_16HCB_CSDLEntities())
{
var result = db.Database.SqlQuery <Khoa>("exec sp_getKhoa").ToList();
res = Request.CreateResponse(HttpStatusCode.OK, JsonConvert.SerializeObject(result));
}
return(res);
}
public List <ReportStudentResult> ReportStudent(int Nam = -1, int HocKy = -1, int MonHoc = -1, int LopHoc = -1)
{
using (var db = new Project_16HCB_CSDLEntities())
{
List <ReportStudentResult> results = new List <ReportStudentResult>();
results = db.Database.SqlQuery <ReportStudentResult>("sp_ReportStudent @Nam, @HocKy, @MonHoc, @LopHoc",
new SqlParameter("Nam", Nam),
new SqlParameter("HocKy", HocKy),
new SqlParameter("MonHoc", MonHoc),
new SqlParameter("LopHoc", LopHoc)).ToList();
return(results);
}
}
//public bool ThemSinhVien(SinhVien sv)
//{
// try
// {
// int CurID;
// using (var db = new Project_16HCB_CSDLEntities())
// {
// SqlParameter paUSERNAME = new SqlParameter("@USERNAME", sv._username);
// SqlParameter paEMAIL = new SqlParameter("@EMAIL", sv._email);
// SqlParameter paSDT = new SqlParameter("@SDT", sv._sdt);
// SqlParameter paCMND = new SqlParameter("@CMND", sv._cmnd);
// SqlParameter paNGAYSINH = new SqlParameter("@NGAYSINH", sv._ngaysinh);
// SqlParameter paDIACHI = new SqlParameter("@DIACHI", sv._diachi);
// SqlParameter paMSSV = new SqlParameter("@MSSV", sv._mssv);
// SqlParameter paHDT = new SqlParameter("@HEDAOTAO", sv._hedaotao);
// SqlParameter paTRANGTHAI = new SqlParameter("@TRANGTHAI", sv._trangthai);
// SqlParameter paMALOP = new SqlParameter("@MALOP", sv._malop);
// var result = db.Database.SqlQuery<SinhVien>("exec sp_themUser @USERNAME,@EMAIL,@SDT,@CMND,@NGAYSINH,@DIACHI,@MSSV,@HEDAOTAO,@TRANGTHAI,@MALOP",
// paUSERNAME,
// paEMAIL,
// paSDT,
// paCMND,
// paNGAYSINH,
// paDIACHI,
// paMSSV,
// paHDT,
// paTRANGTHAI,
// paMALOP).FirstOrDefault();
// CurID = Convert.ToInt32(result);
// }
// using (var db = new Project_16HCB_CSDLEntities())
// {
// int intSoVanTay = sv._lstVanTay.Count;
// for (int i = 0; i < intSoVanTay; i++)
// {
// SqlParameter paUSERID = new SqlParameter("@USERID", CurID);
// SqlParameter paMAVANTAY = new SqlParameter("@MAVANTAY", sv._lstVanTay[i]._mavantay);
// SqlParameter paURL = new SqlParameter("@URL", sv._lstVanTay[i]._url);
// var result = db.Database.SqlQuery<SinhVien>("exec sp_themVanTay @USERID,@MAVANTAY,@URL",
// paUSERID,
// paMAVANTAY,
// paURL).FirstOrDefault();
// }
// }
// return true;
// }
// catch (Exception ex)
// {
// return false;
// }
//}
public bool CapNhatSinhVien(SinhVien sv)
{
try
{
using (var db = new Project_16HCB_CSDLEntities())
{
//Cập nhật user
var objUser = db.USERS.Where(i => i.C_userId == sv._userid).FirstOrDefault();
objUser.C_username = sv._username;
objUser.C_email = sv._email;
objUser.C_sdt = sv._sdt;
objUser.C_cmnd = sv._cmnd;
string[] arrtemp = sv._ngaysinh.Split('/');
objUser.C_ngaysinh = Convert.ToDateTime(arrtemp[1] + "/" + arrtemp[0] + "/" + arrtemp[2]);
objUser.C_diachi = sv._diachi;
db.SaveChanges();
//Cập nhật sinh viên
var objSinhVien = db.SINHVIENs.Where(i => i.C_userId == sv._userid).FirstOrDefault();
objSinhVien.C_heDaoTao = sv._hedaotao;
objSinhVien.C_ngayBatDau = DateTime.Now;
objSinhVien.C_trangThai = sv._trangthai;
objSinhVien.C_maLop = sv._malop;
db.SaveChanges();
//Cập nhật vân tay
var lstVanTay = db.VANTAYs.Where(i => i.C_userID == sv._userid).ToList();
int intSoVanTay = lstVanTay.Count();
for (int i = 0; i < intSoVanTay; i++)
{
var objVanTay = lstVanTay[i];
objVanTay.C_maVanTay = sv._lstVanTay[i]._mavantay;
objVanTay.C_url = sv._lstVanTay[i]._url;
objVanTay.C_ngayTao = DateTime.Now;
objVanTay.C_maVanTay = Md5Hash(Convert.ToString(sv._userid));
}
db.SaveChanges();
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool KiemTraSVTonTai(int mssv)
{
using (var db = new Project_16HCB_CSDLEntities())
{
SqlParameter parameter1 = new SqlParameter("@MSSV", mssv);
var result = db.Database.SqlQuery <int>("exec kiemtraSVTonTai @MSSV", parameter1).FirstOrDefault();
if (result != 0)
{
return(true);
}
else
{
return(false);
}
}
}
// GET: api/LopHoc
public List <LOPHOC> Get(int NamHoc = -1, int HocKy = -1, int MonHoc = -1)
{
var listLopHoc = new List <LOPHOC>();
using (var db = new Project_16HCB_CSDLEntities())
{
listLopHoc = (from hk in db.HOCKies
join mhhk in db.MONHOC_HOCKY on hk.C_id equals mhhk.C_maHK
join gvmh in db.GIAOVIEN_MONHOC on mhhk.C_id equals gvmh.C_idMH_HK
join tkb in db.THOIKHOABIEUx on gvmh.C_id equals tkb.C_idGV_MH
join lh in db.LOPHOCs on tkb.C_idLop equals lh.C_id
where ((NamHoc == -1 || hk.C_nam == NamHoc) &&
(HocKy == -1 || hk.C_id == HocKy) &&
(MonHoc == -1 || mhhk.C_maMH == MonHoc))
select lh).Distinct().ToList();
}
return(listLopHoc);
}
public bool XoaSinhVien(int intUserID)
{
try
{
using (var db = new Project_16HCB_CSDLEntities())
{
//Xóa sinh viên
var objSinhVien = db.SINHVIENs.Where(i => i.C_userId == intUserID && i.C_daXoa == false).FirstOrDefault();
objSinhVien.C_daXoa = true;
db.SaveChanges();
}
using (var db = new Project_16HCB_CSDLEntities())
{
//Xóa user
var objUser = db.USERS.Where(i => i.C_userId == intUserID).FirstOrDefault();
objUser.C_daXoa = true;
db.SaveChanges();
}
using (var db = new Project_16HCB_CSDLEntities())
{
//Xóa vân tay
var lstVanTay = db.VANTAYs.Where(i => i.C_userID == intUserID).ToList();
int intSoVanTay = lstVanTay.Count();
for (int i = 0; i < intSoVanTay; i++)
{
var objVanTay = lstVanTay[i];
objVanTay.C_daXoa = true;
}
db.SaveChanges();
}
return(true);
}
catch (Exception)
{
return(false);
}
}
public List <ReportDanhSachHocSinhNghiModel> ReportDanhSachHocSinhNghi(DateTime?TU_NGAY = null, DateTime?DEN_NGAY = null)
{
if (TU_NGAY == null || DEN_NGAY == null)
{
throw new Exception("VUI LÒNG CHỌN TỪ NGÀY ĐẾN NGÀY");
}
using (var db = new Project_16HCB_CSDLEntities())
{
List <ReportDanhSachHocSinhNghiModel> results = new List <ReportDanhSachHocSinhNghiModel>();
results = db.Database.SqlQuery <ReportDanhSachHocSinhNghiModel>("SP_DanhSacHocSinhNghi @TU_NGAY, @DEN_NGAY ",
new SqlParameter("TU_NGAY", TU_NGAY),
new SqlParameter("DEN_NGAY", DEN_NGAY)).ToList();
foreach (var data in results)
{
data.ngay_nghihoc_text = data.ngay_nghihoc.ToString("dd/MM/yyyy");
}
return(results);
}
}
public HttpResponseMessage Login([FromBody] object info)
{
HttpResponseMessage respMsg;
var obj = JObject.Parse(info.ToString());
var username = obj["username"].ToObject <string>();
var password = obj["password"].ToObject <string>();
if (info == null || username == "" || password == "")
{
respMsg = Request.CreateResponse(HttpStatusCode.BadRequest,
JsonConvert.SerializeObject(new { msg = "Chưa nhập thông tin đăng nhập" }));
}
else
{
using (var db = new Project_16HCB_CSDLEntities())
{
SqlParameter pReturnVal = new SqlParameter("@returnVal", System.Data.SqlDbType.Int)
{
Direction = System.Data.ParameterDirection.Output
};
SqlParameter pUsername = new SqlParameter("@username", System.Data.SqlDbType.NVarChar, 50)
{
Value = username
};
SqlParameter pPassword = new SqlParameter("@password", System.Data.SqlDbType.NVarChar, 50)
{
Value = password
};
SqlParameter pErrorMsg = new SqlParameter("@errorMsg", System.Data.SqlDbType.NVarChar, 4000)
{
Direction = System.Data.ParameterDirection.Output
};
string sql = "EXEC @returnVal = usp_login @username, @password, @errorMsg OUT";
USER user;
try
{
user = db.Database.SqlQuery <USER>(sql, pReturnVal, pUsername, pPassword, pErrorMsg)
.FirstOrDefault();
var resPhieuDiem = db.PHIEU_DIEM.Count();
var resTraPhieuDiemSV = db.THONGTINTRAPHIEUDIEMs.Where(n => n.C_masv == user.C_userId).Count();
respMsg = Request.CreateResponse(HttpStatusCode.OK,
JsonConvert.SerializeObject(new { user, resPhieuDiem, resTraPhieuDiemSV }));
}
catch (Exception ex)
{
if (pReturnVal.Value == null) // Internal sql server error
{
respMsg = Request.CreateResponse(HttpStatusCode.InternalServerError,
JsonConvert.SerializeObject(new { msg = "SQL Server: "
+ pErrorMsg.Value.ToString() }));
}
else
{
respMsg = Request.CreateResponse(HttpStatusCode.BadRequest,
JsonConvert.SerializeObject(new { msg = GetErrorMsg((int)pReturnVal.Value) }));
}
}
}
}
return(respMsg);
}
public bool ThemSinhVien(SinhVien sv)
{
try
{
using (var db = new Project_16HCB_CSDLEntities())
{
//Thêm user
USER objUser = new USER();
objUser.C_username = sv._username;
objUser.C_email = sv._email;
objUser.C_sdt = sv._sdt;
objUser.C_cmnd = sv._cmnd;
string[] arrtemp = sv._ngaysinh.Split('/');
objUser.C_ngaysinh = Convert.ToDateTime(arrtemp[1] + "/" + arrtemp[0] + "/" + arrtemp[2]);
objUser.C_diachi = sv._diachi;
objUser.C_loaiUS = 1;
objUser.C_daXoa = false;
db.USERS.Add(objUser);
db.SaveChanges();
//try
//{
// db.USERS.Add(objUser);
// db.SaveChanges();
//}
//catch (System.Data.Entity.Validation.DbEntityValidationException dbEx)
//{
// foreach (var validationErrors in dbEx.EntityValidationErrors)
// {
// foreach (var validationError in validationErrors.ValidationErrors)
// {
// System.Console.WriteLine("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);
// }
// }
//}
sv._userid = objUser.C_userId;
}
using (var db = new Project_16HCB_CSDLEntities())
{
//Thêm sinh viên
SINHVIEN objSinhVien = new SINHVIEN();
objSinhVien.C_userId = sv._userid;
objSinhVien.C_heDaoTao = sv._hedaotao;
objSinhVien.C_ngayBatDau = DateTime.Now;
objSinhVien.C_trangThai = sv._trangthai;
objSinhVien.C_maLop = sv._malop;
objSinhVien.C_daXoa = false;
db.SINHVIENs.Add(objSinhVien);
db.SaveChanges();
}
using (var db = new Project_16HCB_CSDLEntities())
{
//Thêm vân tay
int intSoVanTay = sv._lstVanTay.Count;
for (int i = 0; i < intSoVanTay; i++)
{
VANTAY objVanTay = new VANTAY();
objVanTay.C_userID = sv._userid;
objVanTay.C_url = sv._lstVanTay[i]._url;
objVanTay.C_ngayTao = DateTime.Now;
objVanTay.C_daXoa = false;
objVanTay.C_maVanTay = Md5Hash(Convert.ToString(sv._userid));
db.VANTAYs.Add(objVanTay);
}
db.SaveChanges();
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}