public string InsertCommentRecsroom()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
string query = "";
query = "INSERT INTO OP_BBS " +
"VALUES(NOTICE_SEQ.NEXTVAL" +
", NULL" +
", '" + _codeMngTool.getCode("BBS", "RECSROOM") + "'" +
", NULL" +
", TO_CHAR(SYSDATE, 'yyyy/mm/dd hh:mi')" +
", 0" +
", @content:VARCHAR" +
$", '{userInfo.user_id}'" +
", NULL" +
", NULL" +
", NULL" +
", NULL" +
", @bbs_id:VARCHAR)";
//cud 처리할 때는 트랜잭션 시작해주어야함
using var transaction = _context.Database.BeginTransaction();
_commonDao.Insert(query, Request.Form);
transaction.Commit();
return("ok");
}
public string UpdateRecsroom([FromBody] Recsroom Recsroom)
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
Dictionary <string, string> param = new Dictionary <string, string>();
// Recsroom 데이터 파싱
param.Add("SelectSubj", Recsroom.SelectSubj);
param.Add("Title", Recsroom.Title);
param.Add("OthbcAt", Recsroom.OthbcAt);
param.Add("Content", Recsroom.Content);
param.Add("bbs_id", Recsroom.Bbs_id);
param.Add("AtchFileId", Recsroom.AtchFileId);
param.Add("user_id", userInfo.user_id);
string query = "";
query = "UPDATE OP_BBS SET " +
"TITLE = @Title:VARCHAR" +
", UPDATE_DT = TO_CHAR(SYSDATE, 'yyyy/mm/dd hh:mi')" +
", CONTENTS = @Content:VARCHAR" +
", UPDUSR = @user_id:VARCHAR" +
", DOC_ID = @AtchFileId:VARCHAR" +
", OTHBC_AT = @OthbcAt:VARCHAR WHERE BBS_ID = @bbs_id:NUMBER";
//cud 처리할 때는 트랜잭션 시작해주어야함
using var transaction = _context.Database.BeginTransaction();
_commonDao.Update(query, param);
transaction.Commit();
return("ok");
}
public IActionResult UpdateFormRecsroom()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
ViewData["name"] = userInfo.name;
ViewData["user_id"] = userInfo.user_id;
ViewData["pageNm"] = "강의 자료실";
ViewData["command"] = "UPDATE";
ViewData["author"] = userInfo.author;
ViewBag.SEARCH_TYPE = Request.Form["search_type"];
ViewBag.SEARCH_TXT = Request.Form["search_txt"].ToString().Replace("\\", "\\\\");
Dictionary <string, string> param = new Dictionary <string, string>();
ViewBag.ACDMC_NO = Request.Form["selectedSubj"];
ViewBag.YEAR_HAKGI = Request.Form["selectedYearhakgi"];
if (!userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")))
{
Response.WriteAsync("<script language=\"javascript\">alert('Invalid Author!!');</script>");
Response.WriteAsync("<script language=\"javascript\">window.location=\"/Main/Main\"</script>");
}
param.Add("page", Request.Form["page"]);
string sql = "SELECT * FROM ("
+ "SELECT A.ACDMC_NO AS SelectSubj"
+ ", A.TITLE"
+ ", A.OTHBC_AT"
+ ", A.CONTENTS"
+ ", A.REGIST_DT "
+ ", B.NAME "
+ ", A.BBS_ID "
+ ", A.RDCNT "
+ ", A.DOC_ID "
+ ", LEAD(BBS_ID) OVER(ORDER BY BBS_ID) AS NEXT_ID "
+ ", LEAD(TITLE) OVER(ORDER BY BBS_ID) AS NEXT_TITLE "
+ ", LAG(BBS_ID) OVER(ORDER BY BBS_ID) AS PREV_ID "
+ ", LAG(TITLE) OVER(ORDER BY BBS_ID) AS PREV_TITLE "
+ "FROM OP_BBS A "
+ "JOIN OP_USER B "
+ "ON A.REGISTER = B.USER_ID "
+ "WHERE 1=1 "
+ "AND ACDMC_NO = @selectedSubj:VARCHAR "
+ "AND BBS_CODE = '" + _codeMngTool.getCode("BBS", "RECSROOM") + "' "
+ "ORDER BY BBS_ID DESC)"
+ "WHERE BBS_ID = @BBS_ID:VARCHAR";
var result = _commonDao.SelectOne(sql, Request.Form);
ViewBag.result = result;
ViewBag.param = param;
ViewBag.SelectPageList = "/Recsroom/SelectPageListRecsroom";
ViewBag.Select = "/Recsroom/SelectRecsroom";
ViewBag.InsertForm = "/Recsroom/InsertFormRecsroom";
ViewBag.Insert = "/Recsroom/UpdateRecsroom";
return(View("/Views/LctSport/BoardQnaWriteStdPage.cshtml"));
}
/*
* 강의 과제 작성 페이지
* 작성자 : 김정원
* 기능 : 과제 작성 페이지 호출
* */
public IActionResult InsertFormTask()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
ViewData["name"] = userInfo.name;
ViewData["user_id"] = userInfo.user_id;
ViewData["fs_at"] = userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")) ? "Y" : "N"; // 교수 여부
ViewData["pageNm"] = "과제 제출";
ViewData["author"] = userInfo.author;
ViewData["command"] = "INSERT";
Dictionary <string, string> param = new Dictionary <string, string>();
ViewBag.ACDMC_NO = Request.Form["selectedSubj"];
ViewBag.YEAR_HAKGI = Request.Form["selectedYearhakgi"];
if (!userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")))
{
Response.WriteAsync("<script language=\"javascript\">alert('Invalid Author!!');</script>");
Response.WriteAsync("<script language=\"javascript\">window.location=\"/Main/Main\"</script>");
}
param.Add("page", Request.Form["page"]);
ViewBag.param = param;
ViewBag.SelectPageList = "/Task/SelectPageListTask";
ViewBag.Select = "/Task/SelectTask";
ViewBag.InsertForm = "/Task/InsertFormTask";
ViewBag.Insert = "/Task/InsertTask";
return(View("/Views/LctSport/Task/TaskInsertStdPage.cshtml"));
}
public string DeleteQNA()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
string query = "";
query = "SELECT FILE_ID, A.DOC_ID FROM OP_FILE A "
+ "JOIN OP_BBS B "
+ "ON A.DOC_ID=B.DOC_ID "
+ "AND BBS_ID='" + Request.Form["bbs_id"] + "'";
var removeFiles = _commonDao.SelectList(query);
for (int i = 0; i < removeFiles.Count; ++i)
{
_fileMngTool.removeFile(removeFiles[i]["DOC_ID"]);
}
query = "DELETE FROM OP_BBS WHERE BBS_ID = @bbs_id:NUMBER";
//cud 처리할 때는 트랜잭션 시작해주어야함
using var transaction = _context.Database.BeginTransaction();
string resultCode = "ok";
if (_commonDao.Delete(query, Request.Form) == 0)
{
resultCode = "false";
}
transaction.Commit();
return(resultCode);
}
public IActionResult Login()
{
string alertMsg = "";
switch (Convert.ToInt32(Request.Query["alertLogin"]))
{
case 1:
alertMsg = "로그인 정보가 틀렸습니다.";
break;
case 2:
alertMsg = "로그인 정보가 만료되었습니다.";
break;
}
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
if (userInfo != null)
{
return(Redirect("/Main"));
}
ViewData["AlertMsg"] = alertMsg;
return(View());
}
/*
* 강의 공지사항 작성 페이지
* 작성자 : 김정원
* 기능 : 공지사항 작성 페이지 호출
* */
public IActionResult InsertFormRecsroom()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
ViewData["name"] = userInfo.name;
ViewData["user_id"] = userInfo.user_id;
ViewData["pageNm"] = "강의 자료실";
ViewData["command"] = "INSERT";
ViewData["author"] = userInfo.author;
ViewBag.SEARCH_TYPE = Request.Form["search_type"];
ViewBag.SEARCH_TXT = Request.Form["search_txt"].ToString().Replace("\\", "\\\\");
Dictionary <string, string> param = new Dictionary <string, string>();
ViewBag.ACDMC_NO = Request.Form["selectedSubj"];
ViewBag.YEAR_HAKGI = Request.Form["selectedYearhakgi"];
//if (!userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")))
//{
// Response.WriteAsync("<script language=\"javascript\">alert('Invalid Author!!');</script>");
// Response.WriteAsync("<script language=\"javascript\">window.location=\"/Main/Main\"</script>");
//}
param.Add("page", Request.Form["page"]);
ViewBag.param = param;
ViewBag.SelectPageList = "/Recsroom/SelectPageListRecsroom";
ViewBag.Select = "/Recsroom/SelectRecsroom";
ViewBag.InsertForm = "/Recsroom/InsertFormRecsroom";
ViewBag.Insert = "/Recsroom/InsertRecsroom";
return(View("/Views/LctSport/BoardQnaWriteStdPage.cshtml"));
}
public string PrsentrTask()
{
string resultCode = "ok";
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
string query = "";
query = $"SELECT COUNT(*) AS CNT FROM OP_TAKES A NATURAL JOIN OP_SECTION B WHERE A.ID = '{userInfo.user_id}' AND ACDMC_NO = '" + Request.Form["selectedSubj"] + "'";
// 해당 강의를 수강하는 학생이 아니라면
if (!_commonDao.SelectOne(query)["CNT"].Equals("1"))
{
resultCode = "false";
}
query = "INSERT INTO OP_TASK_PRSENTR VALUES(" +
"@task_id:VARCHAR " +
$", '{userInfo.user_id}' " +
", @doc_id:VARCHAR " +
", @prsentr_title:VARCHAR " +
", @prsentr_content:VARCHAR " +
", 'Y'" +
", SYSDATE)";
//cud 처리할 때는 트랜잭션 시작해주어야함
using var transaction = _context.Database.BeginTransaction();
try {
if (_commonDao.Insert(query, Request.Form) == 0)
{
resultCode = "false";
}
// 한번 제출한 과제는 insert 시 에러가 나기 때문에 catch 문으로 받고 업데이트문으로 바꾸어준다.
} catch (OracleException e) {
query = "UPDATE OP_TASK_PRSENTR SET " +
"DOC_ID = @doc_id:VARCHAR " +
", TITLE = @prsentr_title:VARCHAR " +
", CONTENT = @prsentr_content:VARCHAR " +
" WHERE TASK_SEQ = @task_id:VARCHAR" +
$" AND REGISTER = '{userInfo.user_id}'";
if (_commonDao.Insert(query, Request.Form) == 0)
{
resultCode = "false";
}
}
transaction.Commit();
return(resultCode);
}
public string DeleteTask()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
//cud 처리할 때는 트랜잭션 시작해주어야함
using var transaction = _context.Database.BeginTransaction();
string resultCode = "ok";
string query = "";
if (userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")))
{
if (!userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")))
{
Response.WriteAsync("<script language=\"javascript\">alert('Invalid Author!!');</script>");
Response.WriteAsync("<script language=\"javascript\">window.location=\"/Task/SelectPageListTask\"</script>");
}
query = $"DELETE FROM OP_TASK_PRSENTR WHERE TASK_SEQ = @task_id:NUMBER";
// 과제에 딸려 있는 제출 내역들을 먼저 삭제하고
if (_commonDao.Delete(query, Request.Form) <= 0)
{
resultCode = "false";
}
// 과제를 삭제한다.
query = "DELETE FROM OP_TASK WHERE TASK_SEQ = @task_id:NUMBER";
}
else
{
query = $"DELETE FROM OP_TASK_PRSENTR WHERE REGISTER = '{userInfo.user_id}' AND TASK_SEQ = @task_id:NUMBER";
}
if (_commonDao.Delete(query, Request.Form) <= 0)
{
resultCode = "false";
}
if (resultCode.Equals("false"))
{
transaction.Rollback();
}
else
{
transaction.Commit();
}
return(resultCode);
}
public override void OnActionExecuting(ActionExecutingContext context)
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(context.HttpContext.Session, "userInfo");
if (userInfo == null)
{
context.Result = new RedirectToRouteResult(
new RouteValueDictionary
{
{ "controller", "Login" },
{ "action", "Login" },
{ "alertLogin", 2 }
});
}
}
public string InsertCommentQNA()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
Dictionary <string, string> param = new Dictionary <string, string>();
string src = Request.Form["content"].ToString();
if (src != null)
{
src = src.Replace("&", "&");
src = src.Replace("<", "<");
src = src.Replace(">", ">");
src = src.Replace("\"", """);
src = src.Replace("\'", "'");
}
param.Add("bbs_id", Request.Form["bbs_id"]);
param.Add("content", src);
string query = "";
query = "INSERT INTO OP_BBS " +
"VALUES(NOTICE_SEQ.NEXTVAL" +
", NULL" +
", '" + _codeMngTool.getCode("BBS", "QNA") + "'" +
", NULL" +
", TO_CHAR(SYSDATE, 'yyyy/mm/dd hh:mi')" +
", 0" +
", @content:VARCHAR" +
$", '{userInfo.user_id}'" +
", NULL" +
", NULL" +
", NULL" +
", NULL" +
", @bbs_id:VARCHAR)";
//cud 처리할 때는 트랜잭션 시작해주어야함
using var transaction = _context.Database.BeginTransaction();
_commonDao.Insert(query, param);
transaction.Commit();
return("ok");
}
public string InsertTask([FromBody] Task Task)
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
Dictionary <string, string> param = new Dictionary <string, string>();
// Task 데이터 파싱
param.Add("SelectSubj", Task.SelectSubj);
param.Add("Title", Task.Title);
param.Add("Content", Task.Content);
param.Add("Sdate", Task.Sdate);
param.Add("Edate", Task.Edate);
param.Add("Adit_sdate", Task.Adit_sdate);
param.Add("Adit_edate", Task.Adit_edate);
param.Add("AtchFileId", Task.AtchFileId);
param.Add("PresentForm", Task.PresentForm);
param.Add("FileCpctyLlmt", Task.FileCpctyLlmt);
param.Add("user_id", userInfo.user_id);
string query = "";
query = "INSERT INTO OP_TASK " +
"VALUES(Task_SEQ.NEXTVAL" +
", @SelectSubj:VARCHAR" +
", @Title:VARCHAR" +
", TO_DATE(@Sdate:VARCHAR, 'YYYYMMDDHH24MI')" +
", TO_DATE(@Edate:VARCHAR, 'YYYYMMDDHH24MI')" +
", TO_DATE(@Adit_sdate:VARCHAR, 'YYYYMMDDHH24MI')" +
", TO_DATE(@Adit_edate:VARCHAR, 'YYYYMMDDHH24MI')" +
", '1'" +
", @Content:VARCHAR" +
", @PresentForm:VARCHAR" +
", @AtchFileId:VARCHAR" +
", @FileCpctyLlmt:VARCHAR" +
", @user_id:VARCHAR)";
//cud 처리할 때는 트랜잭션 시작해주어야함
using var transaction = _context.Database.BeginTransaction();
_commonDao.Insert(query, param);
transaction.Commit();
return("ok");
}
public string UpdateTask([FromBody] Task Task)
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
Dictionary <string, string> param = new Dictionary <string, string>();
// Task 데이터 파싱
param.Add("SelectSubj", Task.SelectSubj);
param.Add("Title", Task.Title);
param.Add("Content", Task.Content);
param.Add("Sdate", Task.Sdate);
param.Add("Edate", Task.Edate);
param.Add("Adit_sdate", Task.Adit_sdate);
param.Add("Adit_edate", Task.Adit_edate);
param.Add("AtchFileId", Task.AtchFileId);
param.Add("PresentForm", Task.PresentForm);
param.Add("FileCpctyLlmt", Task.FileCpctyLlmt);
param.Add("TaskId", Task.TaskId);
param.Add("user_id", userInfo.user_id);
string query = "";
query = "UPDATE OP_TASK SET " +
"TITLE = @Title:VARCHAR" +
", BEGIN_TMLMT = TO_DATE(@Sdate:VARCHAR, 'YYYYMMDDHH24MI')" +
", END_TMLMT = TO_DATE(@Edate:VARCHAR, 'YYYYMMDDHH24MI')" +
", BEGIN_ADIT_TMLMT = TO_DATE(@Adit_sdate:VARCHAR, 'YYYYMMDDHH24MI')" +
", END_ADIT_TMLMT = TO_DATE(@Adit_edate:VARCHAR, 'YYYYMMDDHH24MI')" +
", CONTENT = @Content:VARCHAR" +
", DOC_ID = @AtchFileId:VARCHAR" +
", PRESENT_FORM = @PresentForm:VARCHAR" +
", FILE_CPCTY_LMT = @FileCpctyLlmt:VARCHAR" +
" WHERE TASK_SEQ = @TaskId:NUMBER";
//cud 처리할 때는 트랜잭션 시작해주어야함
using var transaction = _context.Database.BeginTransaction();
_commonDao.Update(query, param);
transaction.Commit();
return("ok");
}
public string InsertRecsroom([FromBody] Recsroom Recsroom)
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
Dictionary <string, string> param = new Dictionary <string, string>();
// Recsroom 데이터 파싱
param.Add("SelectSubj", Recsroom.SelectSubj);
param.Add("Title", Recsroom.Title);
param.Add("OthbcAt", Recsroom.OthbcAt);
param.Add("Content", Recsroom.Content);
param.Add("AtchFileId", Recsroom.AtchFileId);
param.Add("user_id", userInfo.user_id);
string query = "";
query = "INSERT INTO OP_BBS " +
"VALUES(NOTICE_SEQ.NEXTVAL" +
", @SelectSubj:VARCHAR" +
", " + _codeMngTool.getCode("BBS", "RECSROOM") +
", @Title:VARCHAR" +
", TO_CHAR(SYSDATE, 'yyyy/mm/dd hh:mi')" +
", 0" +
", @Content:VARCHAR" +
", @user_id:VARCHAR" +
", @AtchFileId:VARCHAR" +
", @OthbcAt:VARCHAR" +
", NULL" +
", NULL" +
", NULL)";
//cud 처리할 때는 트랜잭션 시작해주어야함
using var transaction = _context.Database.BeginTransaction();
_commonDao.Insert(query, param);
transaction.Commit();
return("ok");
}
public string DeleteRecsroom()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
if (!userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")))
{
Response.WriteAsync("<script language=\"javascript\">alert('Invalid Author!!');</script>");
Response.WriteAsync("<script language=\"javascript\">window.location=\"/Recsroom/SelectPageListRecsroom\"</script>");
}
string query = "";
query = "SELECT FILE_ID, A.DOC_ID FROM OP_FILE A "
+ "JOIN OP_BBS B "
+ "ON A.DOC_ID=B.DOC_ID "
+ "AND BBS_ID='" + Request.Form["bbs_id"] + "'";
var removeFiles = _commonDao.SelectList(query);
for (int i = 0; i < removeFiles.Count; ++i)
{
_fileMngTool.removeFile(removeFiles[i]["DOC_ID"]);
}
query = "DELETE FROM OP_BBS WHERE BBS_ID = @bbs_id:NUMBER";
//cud 처리할 때는 트랜잭션 시작해주어야함
using var transaction = _context.Database.BeginTransaction();
string resultCode = "ok";
if (_commonDao.Delete(query, Request.Form) == 0)
{
resultCode = "false";
}
transaction.Commit();
return(resultCode);
}
public string UpdateNotice([FromBody] Notice notice)
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
Dictionary <string, string> param = new Dictionary <string, string>();
if (!userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")))
{
Response.WriteAsync("<script language=\"javascript\">alert('Invalid Author!!');</script>");
Response.WriteAsync("<script language=\"javascript\">window.location=\"/Notice/SelectPageListNotice\"</script>");
}
// Notice 데이터 파싱
param.Add("SelectSubj", notice.SelectSubj);
param.Add("Title", notice.Title);
param.Add("OthbcAt", notice.OthbcAt);
param.Add("Content", notice.Content);
param.Add("bbs_id", notice.Bbs_id);
param.Add("AtchFileId", notice.AtchFileId);
param.Add("user_id", userInfo.user_id);
string query = "";
query = "UPDATE OP_BBS SET " +
"TITLE = @Title:VARCHAR" +
", UPDATE_DT = TO_CHAR(SYSDATE, 'yyyy/mm/dd hh:mi')" +
", CONTENTS = @Content:VARCHAR" +
", UPDUSR = @user_id:VARCHAR" +
", DOC_ID = @AtchFileId:VARCHAR" +
", OTHBC_AT = @OthbcAt:VARCHAR WHERE BBS_ID = @bbs_id:NUMBER";
//cud 처리할 때는 트랜잭션 시작해주어야함
using var transaction = _context.Database.BeginTransaction();
_commonDao.Update(query, param);
transaction.Commit();
return("ok");
}
public IActionResult DoLogin()
{
if (!Request.Form["login_id"].Equals(""))
{
string query = "SELECT USER_ID" +
", NAME" +
", AUTHOR" +
", BIRTHDAY" +
", PHONE" +
", EMAIL" +
" FROM OP_USER" +
" WHERE USER_ID = @login_id:CHAR" +
" AND PASSWORD = @login_pass:VARCHAR";
// 쿼리 결과가 딕셔너리로 반환됨
// SelectOne은 단 한건의 결과만 반환됨, 나머지는 날라감
Dictionary <string, string> result = _commonDao.SelectOne(query, Request.Form);
if (!result["USER_ID"].Equals(""))
{
UserModel userInfo = new UserModel();
userInfo.user_id = result["USER_ID"];
userInfo.name = result["NAME"];
userInfo.author = result["AUTHOR"];
userInfo.birthday = result["BIRTHDAY"];
userInfo.phone = result["PHONE"];
userInfo.email = result["EMAIL"];
SessionExtensionTool.SetObject(HttpContext.Session, "userInfo", userInfo);
return(Redirect("/Main/Main"));
}
}
return(RedirectToAction("Login", new { alertLogin = 1 }));
}
public IActionResult SelectPageListStandInqire()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
ViewData["name"] = userInfo.name; // 이름
ViewData["user_id"] = userInfo.user_id; // 유저 ID(학번)
ViewBag.userInfo = userInfo;
ViewData["author"] = userInfo.author;
string query = "";
// 최신 학기에 수강한 과목의 목록
query = "SELECT A.* "
+ "FROM OP_TAKES A "
+ " JOIN(SELECT Distinct(YEAR) AS YEAR, SEMESTER "
+ " FROM( "
+ " SELECT FIRST_VALUE(YEAR) over(ORDER BY YEAR DESC, SEMESTER DESC) AS YEAR, "
+ " FIRST_VALUE(SEMESTER) over(ORDER BY YEAR DESC, SEMESTER DESC) AS SEMESTER"
+ " FROM OP_SECTION)) B "
+ " ON A.YEAR = B.YEAR AND A.SEMESTER = B.SEMESTER "
+ $"WHERE A.ID = '{userInfo.user_id}'";
var recentlyTakeList = _commonDao.SelectList(query);
ViewBag.RecentlyGradeAt = "N";
// 최신 연도, 학기
query = "SELECT Distinct(YEAR) AS YEAR, SEMESTER "
+ "FROM( "
+ " SELECT FIRST_VALUE(YEAR) over(ORDER BY YEAR DESC, SEMESTER DESC) AS YEAR, "
+ " FIRST_VALUE(SEMESTER) over(ORDER BY YEAR DESC, SEMESTER DESC) AS SEMESTER"
+ " FROM OP_SECTION)";
var recentYearHakgi = _commonDao.SelectOne(query);
ViewBag.RecentYear = recentYearHakgi["YEAR"];
ViewBag.RecentHakgi = recentYearHakgi["SEMESTER"];
for (int i = 0; i < recentlyTakeList.Count; i++)
{
var recentlyTake = recentlyTakeList[i];
// 수강한 과목을 가져와서 성적이 입력되어있지 않거나 -처리 되어 있지 않은 경우 성적이 입력 된 것임
if (!recentlyTake["GRADE"].Equals("") && !recentlyTake["GRADE"].Equals("-"))
{
ViewBag.RecentlyGradeAt = "Y";
break;
}
}
query = "SELECT AAAA.* "
+ "FROM(SELECT AAA.*, "
+ " TRUNC(CASE "
+ " WHEN AVG_JUMSU <= 4.5 AND AVG_JUMSU >= 3.8 THEN((AVG_JUMSU - 1) * (40 / 3.5)) + 60 "
+ " ELSE((AVG_JUMSU - 1) * 10) + 64 END, 2) AS PERCENTAGE,"
+ " RANK() OVER(PARTITION BY AAA.YEAR, AAA.SEMESTER ORDER BY AAA.AVG_JUMSU DESC) || ' / ' || "
+ " (SELECT COUNT(DISTINCT ID) FROM OP_TAKES WHERE YEAR = AAA.YEAR AND SEMESTER = AAA.SEMESTER) AS RANK "
+ " FROM( "
+ " SELECT AA.YEAR, "
+ " AA.SEMESTER, "
+ " AA.ID, "
+ " AA.TOTAL_CREDITS, "
+ " AA.TOTAL_GRADE_JUMSU, "
+ " TRUNC(AA.TOTAL_GRADE_JUMSU / AA.TOTAL_CREDITS, 2) AS AVG_JUMSU "
+ " FROM( "
+ " SELECT A.YEAR, "
+ " A.SEMESTER, "
+ " A.ID, "
+ " SUM(C.CREDITS) AS TOTAL_CREDITS, "
+ " SUM(CASE "
+ " WHEN GRADE = 'A+' THEN 4.5 "
+ " WHEN GRADE = 'A' THEN 4 "
+ " WHEN GRADE = 'B+' THEN 3.5 "
+ " WHEN GRADE = 'B' THEN 3 "
+ " WHEN GRADE = 'C+' THEN 2.5 "
+ " WHEN GRADE = 'C' THEN 2 "
+ " WHEN GRADE = 'D+' THEN 1.5 "
+ " WHEN GRADE = 'D' THEN 1 "
+ " ELSE 0 END * C.CREDITS) AS TOTAL_GRADE_JUMSU "
+ " FROM OP_TAKES A "
+ " JOIN( "
+ " SELECT ID, YEAR, SEMESTER, COURSE_ID "
+ " FROM OP_TAKES) B "
+ " ON A.ID = B.ID AND A.YEAR = B.YEAR AND A.SEMESTER = B.SEMESTER AND A.COURSE_ID = B.COURSE_ID "
+ " JOIN OP_COURSE C "
+ " ON A.COURSE_ID = C.COURSE_ID AND B.COURSE_ID = C.COURSE_ID "
+ " GROUP BY A.ID, A.YEAR, A.SEMESTER "
+ " ORDER BY A.YEAR DESC, A.SEMESTER DESC) AA) AAA "
+ " ) AAAA "
+ $"WHERE AAAA.ID = '{userInfo.user_id}' ORDER BY YEAR DESC, SEMESTER DESC ";
var rankList = _commonDao.SelectList(query);
ViewBag.RankList = rankList;
return(View("/Views/LrnResult/SelectPageListStandInqire.cshtml"));
}
public IActionResult LctGnrlz()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
ViewData["name"] = userInfo.name;
ViewData["user_id"] = userInfo.user_id;
ViewData["author"] = userInfo.author;
ViewData["Title"] = HttpContext.Session.GetString("_Key");
ViewBag.userinfo = userInfo;
Dictionary <string, string> param = new Dictionary <string, string>();
string sql = "";
string acdmc_no = "";
//강의 개수 체크
sql = "SELECT COUNT(*) AS TAKES_CNT "
+ "FROM OP_TAKES "
+ "WHERE ID=" + userInfo.user_id;
int takesCnt = 0;
// Form이 존재하지 않으면 오류가 나기 때문에 분기해주어야한다.
if (Request.HasFormContentType && !Request.Form["selectedSubj"].ToString().Equals(""))
{
acdmc_no = Request.Form["selectedSubj"];
if (userInfo.author.Equals("1000"))
{
sql = "SELECT BUILDING,ROOM_NUMBER,ACDMC_NO,TITLE,NAME,DAY1,DAY2,PERIOD1,PERIOD2,A.YEAR,A.SEMESTER,C.ID " +
"FROM OP_SECTION A JOIN OP_COURSE B ON A.COURSE_ID=B.COURSE_ID " +
"JOIN OP_TEACHES C ON A.SEC_ID=C.SEC_ID and A.COURSE_ID=C.COURSE_ID and A.SEMESTER=C.SEMESTER and A.YEAR=C.YEAR " +
"JOIN OP_USER D on C.ID=D.USER_ID " +
"JOIN OP_TIME_SLOT E on A.TIME_SLOT_ID=E.TIME_SLOT_ID " +
"WHERE ID=" + userInfo.user_id + " AND ACDMC_NO='" + acdmc_no + "'";
}
else
{
sql = "SELECT BUILDING,ROOM_NUMBER,ACDMC_NO,TITLE,NAME,DAY1,DAY2,PERIOD1,PERIOD2,A.YEAR,A.SEMESTER,C.ID " +
"FROM OP_SECTION A JOIN OP_COURSE B ON A.COURSE_ID=B.COURSE_ID " +
"JOIN OP_TAKES C ON A.SEC_ID=C.SEC_ID and A.COURSE_ID=C.COURSE_ID and A.SEMESTER=C.SEMESTER and A.YEAR=C.YEAR " +
"JOIN OP_USER D on C.ID=D.USER_ID " +
"JOIN OP_TIME_SLOT E on A.TIME_SLOT_ID=E.TIME_SLOT_ID " +
"WHERE ID=" + userInfo.user_id + " AND ACDMC_NO='" + acdmc_no + "'";
}
param = _commonDao.SelectOne(sql);
param.Add("page", "1");
ViewBag.param = param;
ViewBag.selectedSubj = param;
ViewBag.ResultList = param;
ViewBag.YEAR_HAKGI = param["YEAR"] + "," + param["SEMESTER"];
ViewBag.ACDMC_NO = param["ACDMC_NO"];
// Form이 없거나 과목을 선택하지 않고 페이지에 넘어오는 경우
}
else
{
// 디폴트 과목을 선택함
if (userInfo.author.Equals("1000"))
{
int teachesCnt = 0;
sql = "SELECT COUNT(*) AS TEACHES_CNT FROM OP_TEACHES WHERE ID=" + userInfo.user_id;
teachesCnt = Convert.ToInt32(_commonDao.SelectOne(sql)["TEACHES_CNT"]);
if (teachesCnt == 0)
{
return(Redirect("/Views/Main.cshtml"));
}
sql = "SELECT BUILDING,ROOM_NUMBER,ACDMC_NO,TITLE,NAME,DAY1,DAY2,PERIOD1,PERIOD2,A.YEAR YEAR,A.SEMESTER SEMESTER,C.ID " +
"FROM OP_SECTION A JOIN OP_COURSE B ON A.COURSE_ID=B.COURSE_ID " +
"JOIN OP_TEACHES C ON A.SEC_ID=C.SEC_ID and A.COURSE_ID=C.COURSE_ID and A.SEMESTER=C.SEMESTER and A.YEAR=C.YEAR " +
"JOIN OP_USER D on C.ID=D.USER_ID " +
"JOIN OP_TIME_SLOT E on A.TIME_SLOT_ID=E.TIME_SLOT_ID " +
"WHERE ID=" + userInfo.user_id;
}
else
{
takesCnt = Convert.ToInt32(_commonDao.SelectOne(sql, param)["TAKES_CNT"]);
if (takesCnt == 0)
{
return(Redirect("/Views/Main.cshtml"));
}
sql = "SELECT BUILDING,ROOM_NUMBER,ACDMC_NO,TITLE,NAME,DAY1,DAY2,PERIOD1,PERIOD2,A.YEAR,A.SEMESTER,C.ID " +
"FROM OP_SECTION A JOIN OP_COURSE B ON A.COURSE_ID=B.COURSE_ID " +
"JOIN OP_TAKES C ON A.SEC_ID = C.SEC_ID and A.COURSE_ID = C.COURSE_ID and A.SEMESTER = C.SEMESTER and A.YEAR = C.YEAR " +
"JOIN OP_USER D on C.ID=D.USER_ID " +
"JOIN OP_TIME_SLOT E on A.TIME_SLOT_ID=E.TIME_SLOT_ID " +
"WHERE ID=" + userInfo.user_id;
}
param = _commonDao.SelectOne(sql);
param.Add("page", "1");
ViewBag.param = param;
ViewBag.selectedSubj = param;
ViewBag.ResultList = param;
ViewBag.YEAR_HAKGI = param["YEAR"] + "," + param["SEMESTER"];
ViewBag.ACDMC_NO = param["ACDMC_NO"];
}
//공지사항 4개 채워 넣는 부분
if (acdmc_no.Equals("")) //Form 도착 없이 이동한 경우
{
sql = "SELECT COUNT(*) AS NOTICE_CNT " +
"FROM OP_BBS " +
"WHERE ACDMC_NO='" + param["ACDMC_NO"] + "' AND BBS_CODE=1000 AND REF_ID IS NULL";
int noticeCnt = Convert.ToInt32(_commonDao.SelectOne(sql)["NOTICE_CNT"]);
if (noticeCnt > 0)
{
sql = "SELECT ACDMC_NO,TITLE,REGIST_DT,CONTENTS,REGISTER,OTHBC_AT,BBS_ID " +
"FROM OP_BBS " +
"WHERE ACDMC_NO='" + param["ACDMC_NO"] + "' AND BBS_CODE=1000 AND REF_ID IS NULL" + " ORDER BY BBS_ID DESC";
var noticeList = _commonDao.SelectList(sql);
ViewBag.noticeList = noticeList;
}
ViewBag.noticeCount = noticeCnt;
}
else
{
sql = "SELECT COUNT(*) AS NOTICE_CNT " +
"FROM OP_BBS " +
"WHERE ACDMC_NO='" + acdmc_no + "' AND BBS_CODE=1000 AND REF_ID IS NULL";
int noticeCnt = Convert.ToInt32(_commonDao.SelectOne(sql)["NOTICE_CNT"]);
if (noticeCnt > 0)
{
sql = "SELECT ACDMC_NO,TITLE,REGIST_DT,CONTENTS,REGISTER,OTHBC_AT,BBS_ID " +
"FROM OP_BBS " +
"WHERE ACDMC_NO='" + acdmc_no + "' AND BBS_CODE=1000 AND REF_ID IS NULL" + " ORDER BY BBS_ID DESC";
var noticeList = _commonDao.SelectList(sql);
ViewBag.noticeList = noticeList;
}
ViewBag.noticeCount = noticeCnt;
}
//자료실 4개 가져오기
if (acdmc_no.Equals("")) //Form 도착 없이 이동한 경우
{
sql = "SELECT COUNT(*) AS RECS_CNT " +
"FROM OP_BBS " +
"WHERE ACDMC_NO='" + param["ACDMC_NO"] + "' AND OTHBC_AT='Y' AND BBS_CODE=3000 AND REF_ID IS NULL";
int recsCnt = Convert.ToInt32(_commonDao.SelectOne(sql)["RECS_CNT"]);
if (recsCnt > 0)
{
sql = "SELECT ACDMC_NO,TITLE,REGIST_DT,CONTENTS,REGISTER,OTHBC_AT,BBS_ID " +
"FROM OP_BBS " +
"WHERE ACDMC_NO='" + param["ACDMC_NO"] + "' AND OTHBC_AT='Y' AND BBS_CODE=3000 AND REF_ID IS NULL" + " ORDER BY BBS_ID DESC";
var recsList = _commonDao.SelectList(sql);
ViewBag.recsList = recsList;
}
ViewBag.recsCount = recsCnt;
}
else
{
sql = "SELECT COUNT(*) AS RECS_CNT " +
"FROM OP_BBS " +
"WHERE ACDMC_NO='" + acdmc_no + "' AND BBS_CODE=3000 AND OTHBC_AT='Y' AND REF_ID IS NULL";
int recsCnt = Convert.ToInt32(_commonDao.SelectOne(sql)["RECS_CNT"]);
if (recsCnt > 0)
{
sql = "SELECT ACDMC_NO,TITLE,REGIST_DT,CONTENTS,REGISTER,OTHBC_AT,BBS_ID " +
"FROM OP_BBS " +
"WHERE ACDMC_NO='" + acdmc_no + "' AND OTHBC_AT='Y' AND BBS_CODE=3000 AND REF_ID IS NULL" + " ORDER BY BBS_ID DESC";
var recsList = _commonDao.SelectList(sql);
ViewBag.recsList = recsList;
}
ViewBag.recsCount = recsCnt;
}
//Q&A 4개 가져오기
if (acdmc_no.Equals("")) //Form 도착 없이 이동한 경우
{
sql = "SELECT COUNT(*) AS QNA_CNT " +
"FROM OP_BBS " +
"WHERE ACDMC_NO='" + param["ACDMC_NO"] + "' AND OTHBC_AT='Y' AND BBS_CODE=2000 AND REF_ID IS NULL";
int qnaCnt = Convert.ToInt32(_commonDao.SelectOne(sql)["QNA_CNT"]);
if (qnaCnt > 0)
{
sql = "SELECT ACDMC_NO,TITLE,REGIST_DT,CONTENTS,REGISTER,OTHBC_AT,BBS_ID " +
"FROM OP_BBS " +
"WHERE ACDMC_NO='" + param["ACDMC_NO"] + "' AND OTHBC_AT='Y' AND BBS_CODE=2000 AND REF_ID IS NULL" + " ORDER BY BBS_ID DESC";
var qnaList = _commonDao.SelectList(sql);
ViewBag.qnaList = qnaList;
}
ViewBag.qnaCount = qnaCnt;
}
else
{
sql = "SELECT COUNT(*) AS QNA_CNT " +
"FROM OP_BBS " +
"WHERE ACDMC_NO='" + acdmc_no + "' AND OTHBC_AT='Y' AND BBS_CODE=2000 AND REF_ID IS NULL";
int qnaCnt = Convert.ToInt32(_commonDao.SelectOne(sql)["QNA_CNT"]);
if (qnaCnt > 0)
{
sql = "SELECT ACDMC_NO,TITLE,REGIST_DT,CONTENTS,REGISTER,OTHBC_AT,BBS_ID " +
"FROM OP_BBS " +
"WHERE ACDMC_NO='" + acdmc_no + "' AND OTHBC_AT='Y' AND BBS_CODE=2000 AND REF_ID IS NULL" + " ORDER BY BBS_ID DESC";
var qnaList = _commonDao.SelectList(sql);
ViewBag.qnaList = qnaList;
}
ViewBag.qnaCount = qnaCnt;
}
//과제 개수
if (acdmc_no.Equals("")) //Form 도착 없이 이동한 경우
{
sql = "SELECT COUNT(*) AS TASK_CNT " +
"FROM OP_TASK " +
"WHERE ACDMC_NO='" + param["ACDMC_NO"] + "'";
}
else
{
sql = "SELECT COUNT(*) AS TASK_CNT " +
"FROM OP_TASK " +
"WHERE ACDMC_NO='" + acdmc_no + "'";
}
int taskCnt = Convert.ToInt32(_commonDao.SelectOne(sql)["TASK_CNT"]);
ViewBag.taskCount = taskCnt;
return(View("/Views/LctGnrlz/LctGnrlz.cshtml"));
}
public IActionResult SelectTask()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
Dictionary <string, string> param = new Dictionary <string, string>();
ViewData["name"] = userInfo.name;
ViewData["user_id"] = userInfo.user_id;
ViewData["pageNm"] = "과제 제출";
ViewData["author"] = userInfo.author;
ViewData["fs_at"] = userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")) ? "Y" : "N";
ViewBag.ACDMC_NO = Request.Form["selectedSubj"];
ViewBag.YEAR_HAKGI = Request.Form["selectedYearhakgi"];
param.Add("page", Request.Form["page"]);
string sql = "SELECT A.ACDMC_NO AS SelectSubj"
+ ", A.TITLE"
+ ", A.CONTENT"
+ ", A.PRESENT_FORM"
+ ", A.FILE_CPCTY_LMT"
+ ", TO_CHAR(A.BEGIN_TMLMT, 'YYYY-MM-DD hh24:mi:ss') AS BEGIN_TMLMT "
+ ", TO_CHAR(A.END_TMLMT, 'YYYY-MM-DD hh24:mi:ss') AS END_TMLMT "
+ ", TO_CHAR(A.BEGIN_ADIT_TMLMT, 'YYYY-MM-DD hh24:mi:ss') AS BEGIN_ADIT_TMLMT "
+ ", TO_CHAR(A.END_ADIT_TMLMT, 'YYYY-MM-DD hh24:mi:ss') AS END_ADIT_TMLMT "
+ ", CASE WHEN A.BEGIN_TMLMT <= SYSDATE THEN CASE WHEN A.END_TMLMT >= SYSDATE THEN 'Y' ELSE 'N' END ELSE 'N' END AS B1 "
+ ", CASE WHEN A.BEGIN_ADIT_TMLMT <= SYSDATE THEN CASE WHEN A.END_ADIT_TMLMT >= SYSDATE THEN 'Y' ELSE 'N' END ELSE 'N' END AS B2 "
+ ", B.NAME "
+ ", A.TASK_SEQ "
+ ", A.DOC_ID "
+ "FROM OP_TASK A "
+ "JOIN OP_USER B "
+ "ON A.REGISTER = B.USER_ID "
+ "AND ACDMC_NO = @selectedSubj:VARCHAR "
+ "WHERE TASK_SEQ = @task_id:VARCHAR";
var result = _commonDao.SelectOne(sql, Request.Form);
//첨부파일 읽어오기
if (result["DOC_ID"] != "")
{
sql = "SELECT FILE_NAME,FILE_EXTSN,FILE_ID FROM OP_FILE A JOIN OP_TASK B ON A.DOC_ID=B.DOC_ID"
+ $" WHERE A.DOC_ID='{result["DOC_ID"]}'";
int fcount = 0;
var fileList = _commonDao.SelectList(sql);
fcount = fileList.Count;
ViewBag.fileList = fileList;
ViewBag.fileCount = fcount;
}
// 학생 관련 부분
if (ViewData["fs_at"].Equals("N"))
{
sql = $"SELECT TASK_SEQ, REGISTER, DOC_ID, TITLE, CONTENT, PRSENTR_AT, REGIST_DT FROM OP_TASK_PRSENTR WHERE REGISTER = '{userInfo.user_id}' AND TASK_SEQ = @task_id:VARCHAR";
}
else
{
sql = $"SELECT TASK_SEQ, REGISTER, DOC_ID, TITLE, CONTENT, PRSENTR_AT, REGIST_DT FROM OP_TASK_PRSENTR WHERE TASK_SEQ = @task_id:VARCHAR ORDER BY REGISTER ASC";
}
// 학생들이 과제 제출한 부분
var resultPrsentList = _commonDao.SelectList(sql, Request.Form);
// 학생 과제 수정 시 기존 내용을 유지하면서 수정 폼을 띄우기 위한 구분값을 설정한다.
// 해당 if문이 참일 경우는 학생일 경우를 가정하고 실행시키기 때문에 배열의 0번 인덱스에 해당 학생의 과제정보가 있을 것이라고 판단한다.
if (Request.Form.ContainsKey("prsentr_at") && Request.Form["prsentr_at"] == "N")
{
resultPrsentList[0]["PRSENTR_AT"] = "N";
}
// 학생이 업로드한 파일 리스트를 담는 리스트
List <List <Dictionary <string, string> > > studentFileList = new List <List <Dictionary <string, string> > >();
for (int i = 0; i < resultPrsentList.Count; i++)
{
var resultPrsentr = resultPrsentList[i];
//첨부파일 읽어오기
if (resultPrsentr["DOC_ID"] != "")
{
sql = "SELECT FILE_NAME,FILE_EXTSN,FILE_ID FROM OP_FILE A JOIN OP_TASK_PRSENTR B ON A.DOC_ID=B.DOC_ID"
+ $" WHERE A.DOC_ID='{resultPrsentr["DOC_ID"]}'";
int fcount2 = 0;
var fileList2 = _commonDao.SelectList(sql);
fcount2 = fileList2.Count;
studentFileList.Add(fileList2);
resultPrsentr["fileCount"] = fcount2.ToString();
}
}
ViewBag.resultPrsentList = resultPrsentList;
ViewBag.studentFileList = studentFileList;
ViewBag.result = result;
ViewBag.param = param;
ViewBag.Select = "/Task/SelectTask";
ViewBag.SelectPageList = "/Task/SelectPageListTask";
ViewBag.UpdateForm = "/Task/UpdateFormTask";
ViewBag.Delete = "/Task/DeleteTask";
ViewBag.Prsentr = "/Task/PrsentrTask";
return(View("/Views/LctSport/Task/TaskViewPage.cshtml"));
}
public List <Dictionary <string, List <Dictionary <string, string> > > > YearhakgiAtnlcSbjectList()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
Dictionary <string, string> param = new Dictionary <string, string>();
// 학기 리스트
List <Dictionary <string, string> > yearhakgiList = null;
// 학기 정보 리스트
List <Dictionary <string, List <Dictionary <string, string> > > > resultList = new List <Dictionary <string, List <Dictionary <string, string> > > >();
param["USER_ID"] = userInfo.user_id;
if (userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")))
{
string sql = "";
// 학기명
sql = "SELECT YEAR || '년도 ' || SEMESTER || '학기' AS LABEL"
+ " ,YEAR || ',' || SEMESTER AS YEAR_HAKGI "
+ ", YEAR, SEMESTER "
+ "FROM OP_TEACHES "
+ $"WHERE ID = '{userInfo.user_id}' "
+ "GROUP BY YEAR, SEMESTER "
+ "ORDER BY YEAR DESC, SEMESTER DESC";
yearhakgiList = _commonDao.SelectList(sql);
for (int i = 0; i < yearhakgiList.Count; i++)
{
// 과목명
sql = "SELECT B.ACDMC_NO, A.SEMESTER, A.YEAR, D.TITLE || ' (' || B.ACDMC_NO || ') - ' || C.NAME AS LABEL" +
", A.YEAR || ',' || A.SEMESTER AS YEAR_HAKGI"
+ " FROM OP_TEACHES A"
+ " JOIN OP_SECTION B"
+ " ON A.COURSE_ID = B.COURSE_ID AND A.SEC_ID = B.SEC_ID AND A.SEMESTER = B.SEMESTER AND A.YEAR = B.YEAR"
+ " JOIN OP_USER C"
+ " ON A.ID = C.USER_ID"
+ " JOIN OP_COURSE D"
+ " ON A.COURSE_ID = D.COURSE_ID"
+ $" WHERE A.ID = '{userInfo.user_id}'"
+ " AND A.semester = '" + yearhakgiList[i]["SEMESTER"] + "'"
+ " AND A.YEAR = '" + yearhakgiList[i]["YEAR"] + "'"
+ " ORDER BY A.COURSE_ID";
// 학기 당 과목리스트
List <Dictionary <string, string> > subjList = null;
subjList = _commonDao.SelectList(sql);
subjList.Add(yearhakgiList[i]);
// 반환 리스트
Dictionary <string, List <Dictionary <string, string> > > result = new Dictionary <string, List <Dictionary <string, string> > >();
result["subjList"] = subjList;
resultList.Add(result);
}
}
else if (userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "STUDENT")))
{
string sql = "";
// 학기명
sql = "SELECT YEAR || '년도 ' || SEMESTER || '학기' AS LABEL"
+ " ,YEAR || ',' || SEMESTER AS YEAR_HAKGI "
+ ", YEAR, SEMESTER "
+ "FROM OP_TAKES "
+ $"WHERE ID = '{userInfo.user_id}' "
+ "GROUP BY YEAR, SEMESTER "
+ "ORDER BY YEAR DESC, SEMESTER DESC";
yearhakgiList = _commonDao.SelectList(sql);
for (int i = 0; i < yearhakgiList.Count; i++)
{
// 과목명
sql = "SELECT B.ACDMC_NO, A.SEMESTER, A.YEAR, D.TITLE || ' (' || B.ACDMC_NO || ') - ' || (SELECT BB.NAME FROM OP_TEACHES AA JOIN OP_USER BB ON AA.ID=BB.USER_ID WHERE COURSE_ID=D.COURSE_ID AND SEMESTER=B.SEMESTER AND YEAR=B.YEAR AND SEC_ID=B.SEC_ID) AS LABEL" +
", A.YEAR || ',' || A.SEMESTER AS YEAR_HAKGI"
+ " FROM OP_TAKES A"
+ " JOIN OP_SECTION B"
+ " ON A.COURSE_ID = B.COURSE_ID AND A.SEC_ID = B.SEC_ID AND A.SEMESTER = B.SEMESTER AND A.YEAR = B.YEAR"
+ " JOIN OP_USER C"
+ " ON A.ID = C.USER_ID"
+ " JOIN OP_COURSE D"
+ " ON A.COURSE_ID = D.COURSE_ID"
+ $" WHERE A.ID = '{userInfo.user_id}'"
+ " AND A.semester = '" + yearhakgiList[i]["SEMESTER"] + "'"
+ " AND A.YEAR = '" + yearhakgiList[i]["YEAR"] + "'"
+ " ORDER BY A.COURSE_ID";
// 학기 당 과목리스트
List <Dictionary <string, string> > subjList = null;
subjList = _commonDao.SelectList(sql);
subjList.Add(yearhakgiList[i]);
// 반환 리스트
Dictionary <string, List <Dictionary <string, string> > > result = new Dictionary <string, List <Dictionary <string, string> > >();
result["subjList"] = subjList;
resultList.Add(result);
}
}
return(resultList);
}
/*
* 강의 과제 리스트 페이지
* 작성자 : 김정원
* 기능 : 과제 리스트 페이지 호출
* */
public IActionResult SelectPageListTask()
{
Dictionary <string, string> param = new Dictionary <string, string>();
// User 정보 파싱
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
ViewData["name"] = userInfo.name; // 이름
ViewData["user_id"] = userInfo.user_id; // 유저 ID(학번)
ViewData["fs_at"] = userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")) ? "Y" : "N"; // 교수 여부
ViewData["pageNm"] = "과제 제출";
ViewData["author"] = userInfo.author;
string sql = "";
// 과제 개수 체크
sql = "SELECT COUNT(*) AS TASK_CNT "
+ "FROM OP_TASK A "
+ "JOIN OP_USER B "
+ "ON A.REGISTER = B.USER_ID "
/*+ "JOIN OP_FILE C "
+ "ON A.DOC_ID = C.DOC_ID AND C.FILE_NUM = 1 "*/
+ "WHERE ACDMC_NO = @selectedSubj:VARCHAR";
int bbsCnt = 0;
// Form이 존재하지 않으면 오류가 나기 때문에 분기해주어야한다.
if (Request.HasFormContentType && !Request.Form["selectedSubj"].ToString().Equals(""))
{
bbsCnt = Convert.ToInt32(_commonDao.SelectOne(sql, Request.Form)["TASK_CNT"]);
ViewBag.YEAR_HAKGI = Request.Form["selectedYearhakgi"];
ViewBag.ACDMC_NO = Request.Form["selectedSubj"];
// Form이 없거나 과목을 선택하지 않고 과제 페이지에 넘어오는 경우
}
else
{
// 디폴트 과목을 선택함
string sql2 = userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR"))
? "SELECT * FROM (SELECT ROWNUM, AA.* FROM (SELECT B.ACDMC_NO AS selectedSubj, YEAR || ',' || SEMESTER AS YEAR_HAKGI"
+ $" FROM OP_TEACHES A NATURAL JOIN OP_SECTION B WHERE A.ID = '{userInfo.user_id}' ORDER BY YEAR DESC, SEMESTER DESC, COURSE_ID) AA) AAA WHERE ROWNUM = 1"
: "SELECT * FROM (SELECT ROWNUM, AA.* FROM (SELECT B.ACDMC_NO AS selectedSubj, YEAR || ',' || SEMESTER AS YEAR_HAKGI"
+ $" FROM OP_TAKES A NATURAL JOIN OP_SECTION B WHERE A.ID = '{userInfo.user_id}' ORDER BY YEAR DESC, SEMESTER DESC, COURSE_ID) AA) AAA WHERE ROWNUM = 1";
param = _commonDao.SelectOne(sql2);
param.Add("selectedSubj", param["SELECTEDSUBJ"]);
bbsCnt = Convert.ToInt32(_commonDao.SelectOne(sql, param)["TASK_CNT"]);
ViewBag.YEAR_HAKGI = param["YEAR_HAKGI"];
ViewBag.ACDMC_NO = param["selectedSubj"];
}
// 만약 조회된 과제가 있으면 값을 가져온다.
if (bbsCnt > 0)
{
sql = "SELECT * "
+ "FROM(SELECT ROWNUM AS RNUM, AA.* "
+ " FROM(SELECT A.TITLE, "
+ " B.NAME AS REGISTER, "
+ " A.TASK_SEQ, "
+ " TO_CHAR(A.BEGIN_TMLMT, 'YYYY-MM-DD hh24:mi:ss') AS BEGIN_TMLMT, "
+ " TO_CHAR(A.END_TMLMT, 'YYYY-MM-DD hh24:mi:ss') AS END_TMLMT, "
+ " TO_CHAR(A.BEGIN_ADIT_TMLMT, 'YYYY-MM-DD hh24:mi:ss') AS BEGIN_ADIT_TMLMT, "
+ " TO_CHAR(A.END_ADIT_TMLMT, 'YYYY-MM-DD hh24:mi:ss') AS END_ADIT_TMLMT, "
+ " CASE WHEN A.BEGIN_TMLMT <= SYSDATE THEN CASE WHEN A.END_TMLMT >= SYSDATE THEN 'Y' ELSE 'N' END ELSE 'N' END AS B1, "
+ " CASE WHEN A.BEGIN_ADIT_TMLMT <= SYSDATE THEN CASE WHEN A.END_ADIT_TMLMT >= SYSDATE THEN 'Y' ELSE 'N' END ELSE 'N' END AS B2 "
+ " FROM OP_TASK A "
+ " JOIN OP_USER B "
+ " ON A.REGISTER = B.USER_ID "
+ " WHERE ACDMC_NO = @selectedSubj:VARCHAR "
+ " ORDER BY TASK_SEQ DESC) AA) AAA WHERE 1 = 1 ";
// Form이 존재하지 않으면 오류가 나기 때문에 분기해주어야한다.
if (Request.HasFormContentType)
{
var resultList = _commonDao.SelectList(sql, Request.Form);
ViewBag.ResultList = resultList;
// Form이 없거나 과목을 선택하지 않고 과제 페이지에 넘어오는 경우
}
else
{
var resultList = _commonDao.SelectList(sql, param);
ViewBag.ResultList = resultList;
}
for (int i = 0; i < ViewBag.ResultList.Count; i++)
{
if (ViewData["fs_at"].Equals("Y"))
{
sql = "SELECT("
+ "SELECT COUNT(*) AS PRSENTR_CNT FROM OP_TASK_PRSENTR WHERE TASK_SEQ = '81') || ' / ' || "
+ "(SELECT COUNT(*) TOTAL_TAKES FROM OP_TAKES A "
+ "JOIN OP_SECTION B ON A.SEC_ID = B.SEC_ID and A.COURSE_ID = B.COURSE_ID and A.SEMESTER = B.SEMESTER and A.YEAR = B.YEAR "
+ "WHERE B.ACDMC_NO = 'H030-3-9876-01') AS PRSENTR_AT "
+ "FROM DUAL ";
}
else
{
sql = $"SELECT PRSENTR_AT FROM OP_TASK_PRSENTR WHERE REGISTER = '{userInfo.user_id}'";
}
if (Request.HasFormContentType)
{
ViewBag.ResultList[i]["PRSENTR_AT"] = _commonDao.SelectOne(sql, Request.Form)["PRSENTR_AT"].Equals("") ? "미제출" : _commonDao.SelectOne(sql, Request.Form)["PRSENTR_AT"];
}
else
{
ViewBag.ResultList[i]["PRSENTR_AT"] = _commonDao.SelectOne(sql, param)["PRSENTR_AT"].Equals("") ? "미제출" : _commonDao.SelectOne(sql, param)["PRSENTR_AT"];
}
}
}
param.Add("page", "1");
ViewBag.ResultCnt = bbsCnt;
ViewBag.param = param;
ViewBag.SelectPageList = "/Task/SelectPageListTask";
ViewBag.Select = "/Task/SelectTask";
ViewBag.InsertForm = "/Task/InsertFormTask";
return(View("/Views/LctSport/Task/BoardListTaskPage.cshtml"));
}
public IActionResult UpdateFormTask()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
ViewData["name"] = userInfo.name;
ViewData["user_id"] = userInfo.user_id;
ViewData["pageNm"] = "과제 제출";
ViewData["command"] = "UPDATE";
ViewData["author"] = userInfo.author;
Dictionary <string, string> param = new Dictionary <string, string>();
ViewBag.ACDMC_NO = Request.Form["selectedSubj"];
ViewBag.YEAR_HAKGI = Request.Form["selectedYearhakgi"];
if (!userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")))
{
Response.WriteAsync("<script language=\"javascript\">alert('Invalid Author!!');</script>");
Response.WriteAsync("<script language=\"javascript\">window.location=\"/Main/Main\"</script>");
}
param.Add("page", Request.Form["page"]);
string sql = "SELECT A.ACDMC_NO AS SelectSubj"
+ ", A.TITLE"
+ ", A.CONTENT"
+ ", A.PRESENT_FORM"
+ ", A.FILE_CPCTY_LMT"
+ ", A.DOC_ID "
+ ", TO_CHAR(A.BEGIN_TMLMT, 'MONTH DD, YYYY hh24:mi:ss', 'NLS_DATE_LANGUAGE=ENGLISH') AS SDATE "
+ ", TO_CHAR(A.BEGIN_TMLMT, 'hh24') AS STIMEHOUR "
+ ", TO_CHAR(A.BEGIN_TMLMT, 'mi') AS STIMEMIN "
+ ", TO_CHAR(A.END_TMLMT, 'MONTH DD, YYYY hh24:mi:ss', 'NLS_DATE_LANGUAGE=ENGLISH') AS EDATE "
+ ", TO_CHAR(A.END_TMLMT, 'hh24') AS ETIMEHOUR "
+ ", TO_CHAR(A.END_TMLMT, 'mi') AS ETIMEMIN "
+ ", TO_CHAR(A.BEGIN_ADIT_TMLMT, 'MONTH DD, YYYY hh24:mi:ss', 'NLS_DATE_LANGUAGE=ENGLISH') AS ADIT_SDATE "
+ ", TO_CHAR(A.BEGIN_ADIT_TMLMT, 'hh24') AS ADIT_STIMEHOURE "
+ ", TO_CHAR(A.BEGIN_ADIT_TMLMT, 'mi') AS ADIT_STIMEMIN "
+ ", TO_CHAR(A.END_ADIT_TMLMT, 'MONTH DD, YYYY hh24:mi:ss', 'NLS_DATE_LANGUAGE=ENGLISH') AS ADIT_EDATE "
+ ", TO_CHAR(A.END_ADIT_TMLMT, 'hh24') AS ADIT_ETIMEHOUR "
+ ", TO_CHAR(A.END_ADIT_TMLMT, 'mi') AS ADIT_ETIMEMIN "
+ ", B.NAME "
+ ", A.TASK_SEQ "
+ "FROM OP_TASK A "
+ "JOIN OP_USER B "
+ "ON A.REGISTER = B.USER_ID "
+ "AND ACDMC_NO = @selectedSubj:VARCHAR "
+ "WHERE TASK_SEQ = @task_id:VARCHAR";
var result = _commonDao.SelectOne(sql, Request.Form);
ViewBag.result = result;
ViewBag.param = param;
ViewBag.SelectPageList = "/Task/SelectPageListTask";
ViewBag.Select = "/Task/SelectTask";
ViewBag.InsertForm = "/Task/InsertFormTask";
ViewBag.Insert = "/Task/UpdateTask";
return(View("/Views/LctSport/Task/TaskInsertStdPage.cshtml"));
}
/*
* 강의 자료실 리스트 페이지
* 작성자 : 이재하
* 기능 : 강의 자료실 리스트 페이지 호출
* */
public IActionResult SelectPageListRecsroom()
{
Dictionary <string, string> param = new Dictionary <string, string>();
// User 정보 파싱
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
ViewData["name"] = userInfo.name; // 이름
ViewData["user_id"] = userInfo.user_id; // 유저 ID(학번)
ViewData["fs_at"] = userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")) ? "Y" : "N"; // 교수 여부
ViewData["pageNm"] = "강의 자료실";
ViewData["author"] = userInfo.author;
string sql = "";
string searchCondition = "";
if (Request.HasFormContentType && Request.Form.ContainsKey("search_txt"))
{
if (Request.Form["search_type"].Equals("TLE"))
{
searchCondition += " AND A.TITLE LIKE '%" + Request.Form["search_txt"] + "%' ";
}
else if (Request.Form["search_type"].Equals("CNT"))
{
searchCondition += " AND A.CONTENTS LIKE '%" + Request.Form["search_txt"] + "%' ";
}
else if (Request.Form["search_type"].Equals("AUT"))
{
searchCondition += " AND B.NAME LIKE '%" + Request.Form["search_txt"] + "%' ";
}
else
{
searchCondition += " AND ( A.TITLE LIKE '%" + Request.Form["search_txt"] + "%' "
+ " OR A.CONTENTS LIKE '%" + Request.Form["search_txt"] + "%' "
+ " OR B.NAME LIKE '%" + Request.Form["search_txt"] + "%') ";
}
}
// 게시판글 개수 체크
sql = "SELECT COUNT(*) AS BBS_CNT "
+ "FROM OP_BBS A "
+ "JOIN OP_USER B "
+ "ON A.REGISTER = B.USER_ID "
/*+ "JOIN OP_FILE C "
+ "ON A.DOC_ID = C.DOC_ID AND C.FILE_NUM = 1 "*/
+ "WHERE BBS_CODE = '" + _codeMngTool.getCode("BBS", "RECSROOM") + "' "
+ "AND ACDMC_NO = @selectedSubj:VARCHAR" + searchCondition;
int bbsCnt = 0;
// Form이 존재하지 않으면 오류가 나기 때문에 분기해주어야한다.
if (Request.HasFormContentType && !Request.Form["selectedSubj"].ToString().Equals(""))
{
param.Add("page", Request.Form["page"]);
bbsCnt = Convert.ToInt32(_commonDao.SelectOne(sql, Request.Form)["BBS_CNT"]);
ViewBag.YEAR_HAKGI = Request.Form["selectedYearhakgi"];
ViewBag.ACDMC_NO = Request.Form["selectedSubj"];
ViewBag.SEARCH_TYPE = Request.Form["search_type"];
ViewBag.SEARCH_TXT = Request.Form["search_txt"].ToString().Replace("\\", "\\\\");
// Form이 없거나 과목을 선택하지 않고 강의자료실 페이지에 넘어오는 경우
}
else
{
// 디폴트 과목을 선택함
string sql2 = userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR"))
? "SELECT * FROM (SELECT ROWNUM, AA.* FROM (SELECT B.ACDMC_NO AS selectedSubj, YEAR || ',' || SEMESTER AS YEAR_HAKGI"
+ $" FROM OP_TEACHES A NATURAL JOIN OP_SECTION B WHERE A.ID = '{userInfo.user_id}' ORDER BY YEAR DESC, SEMESTER DESC, COURSE_ID) AA) AAA WHERE ROWNUM = 1"
: "SELECT * FROM (SELECT ROWNUM, AA.* FROM (SELECT B.ACDMC_NO AS selectedSubj, YEAR || ',' || SEMESTER AS YEAR_HAKGI"
+ $" FROM OP_TAKES A NATURAL JOIN OP_SECTION B WHERE A.ID = '{userInfo.user_id}' ORDER BY YEAR DESC, SEMESTER DESC, COURSE_ID) AA) AAA WHERE ROWNUM = 1";
param = _commonDao.SelectOne(sql2);
param.Add("selectedSubj", param["SELECTEDSUBJ"]);
param.Add("page", "1");
bbsCnt = Convert.ToInt32(_commonDao.SelectOne(sql, param)["BBS_CNT"]);
ViewBag.YEAR_HAKGI = param["YEAR_HAKGI"];
ViewBag.ACDMC_NO = param["selectedSubj"];
ViewBag.SEARCH_TYPE = "ALL";
ViewBag.SEARCH_TXT = "";
}
// 만약 조회된 공지사항이 있으면 값을 가져온다.
if (bbsCnt > 0)
{
sql = "SELECT * " +
"FROM (SELECT ROWNUM AS RNUM, TITLE, REGISTER, REGIST_DT, DECODE(OTHBC_AT, 'Y', '공개', 'N', '비공개') AS OTHBC, RDCNT, BBS_ID, DOC_ID, FILE_ID, EACH_CMTCNT " +
"FROM (SELECT A.TITLE, " +
"B.NAME AS REGISTER, " +
"A.REGIST_DT, " +
"A.OTHBC_AT, " +
"A.RDCNT, " +
"A.BBS_ID, " +
"A.DOC_ID, " +
"C.FILE_ID, " +
"NVL2(D.EACH_CMTCNT, " +
"' (' || D.EACH_CMTCNT || ')', NULL) AS EACH_CMTCNT " +
"FROM OP_BBS A " +
"JOIN OP_USER B " +
"ON A.REGISTER = B.USER_ID LEFT JOIN OP_FILE C on A.DOC_ID = C.DOC_ID " +
"JOIN (SELECT A.BBS_ID, A.REF_ID, (SELECT COUNT(REF_ID) FROM OP_BBS WHERE REF_ID = A.BBS_ID GROUP BY REF_ID HAVING REF_ID IS NOT NULL) AS EACH_CMTCNT " +
"FROM OP_BBS " +
"A WHERE A.BBS_CODE = '" + _codeMngTool.getCode("BBS", "RECSROOM") + "' " +
"AND REF_ID IS NULL) D " +
"ON A.BBS_ID = D.BBS_ID " +
"WHERE BBS_CODE = '" + _codeMngTool.getCode("BBS", "RECSROOM") + "' " +
"AND ACDMC_NO = @selectedSubj:VARCHAR " + searchCondition +
"ORDER BY BBS_ID DESC, REGIST_DT DESC) AA) AAA WHERE 1 = 1 "
+ (param.ContainsKey("page") ? "AND RNUM > " + (Convert.ToInt32(param["page"]) - 1) + " * 10 " : "AND RNUM > 0 ")
+ (param.ContainsKey("page") ? "AND RNUM <= " + param["page"] + "0" : "AND RNUM <= 10");
// Form이 존재하지 않으면 오류가 나기 때문에 분기해주어야한다.
if (Request.HasFormContentType)
{
var resultList = _commonDao.SelectList(sql, Request.Form);
ViewBag.ResultList = resultList;
// Form이 없거나 과목을 선택하지 않고 공지사항 페이지에 넘어오는 경우
}
else
{
var resultList = _commonDao.SelectList(sql, param);
ViewBag.ResultList = resultList;
}
}
ViewBag.ResultCnt = bbsCnt;
ViewBag.param = param;
ViewBag.SelectPageList = "/Recsroom/SelectPageListRecsroom";
ViewBag.Select = "/Recsroom/SelectRecsroom";
ViewBag.InsertForm = "/Recsroom/InsertFormRecsroom";
ViewBag.Insert = "/Recsroom/InsertRecsroom";
return(View("/Views/LctSport/BoardListStdPage.cshtml"));
}
public IActionResult DoLogout()
{
SessionExtensionTool.SetObject(HttpContext.Session, "userInfo", null);
return(Redirect("/"));
}
public IActionResult ScreInqire()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
ViewData["user_id"] = userInfo.user_id;
ViewData["name"] = userInfo.name;
ViewData["Title"] = HttpContext.Session.GetString("_Key");
ViewData["phone"] = userInfo.phone;
ViewData["author"] = userInfo.author;
string sql = "";
//지도교수 정보
sql = "SELECT C.EMAIL AS I_EMAIL, A.PHONE AS S_PHONE, C.NAME AS I_NAME, I_ID "
+ "FROM OP_USER A JOIN OP_ADVISOR B "
+ "ON A.USER_ID=B.S_ID "
+ "JOIN OP_USER C ON B.I_ID=C.USER_ID "
+ "WHERE S_ID='" + userInfo.user_id + "'";
var advisorList = _commonDao.SelectOne(sql);
ViewBag.advisorList = advisorList;
sql = "SELECT DISTINCT YEAR,SEMESTER "
+ "FROM OP_TAKES A JOIN OP_COURSE B "
+ "ON A.COURSE_ID=B.COURSE_ID "
+ "WHERE ID='" + userInfo.user_id + "' ORDER BY A.YEAR, A.SEMESTER";
var yearHakgiList = _commonDao.SelectList(sql);
sql = "SELECT * "
+ "FROM OP_TAKES A JOIN OP_COURSE B "
+ "ON A.COURSE_ID=B.COURSE_ID "
+ "JOIN OP_SECTION OS on A.SEC_ID = OS.SEC_ID and A.COURSE_ID = OS.COURSE_ID and A.SEMESTER = OS.SEMESTER and A.YEAR = OS.YEAR "
+ "WHERE ID='" + userInfo.user_id + "' ORDER BY A.YEAR, A.SEMESTER";
var gradeList = _commonDao.SelectList(sql);
for (int i = 0; i < yearHakgiList.Count; ++i)
{
double sum = 0.0;
double grade = 0.0, gradeF = 0.0;
double softGrade = 0.0, softGradeF = 0.0;
double notSoftGrade = 0.0, notSoftGradeF = 0.0;
int sbjSum = 0, sbjSumF = 0;
int softSum = 0, softSumF = 0, notSoftSum = 0, notSoftSumF = 0;
for (int j = 0; j < gradeList.Count; ++j)
{
if (yearHakgiList[i]["YEAR"] == gradeList[j]["YEAR"] && yearHakgiList[i]["SEMESTER"] == gradeList[j]["SEMESTER"])
{
if (gradeList[j]["GRADE"] != "-")
{
sbjSumF += Convert.ToInt32(gradeList[j]["CREDITS"]);
}
if (getGrade(gradeList[j]["GRADE"]) != 0.0)
{
sum += Convert.ToDouble(gradeList[j]["CREDITS"]);
}
gradeF += getGrade(gradeList[j]["GRADE"]) * Convert.ToInt32(gradeList[j]["CREDITS"]);
if (gradeList[j]["GRADE"] != "F")
{
sbjSum += Convert.ToInt32(gradeList[j]["CREDITS"]); grade += (getGrade(gradeList[j]["GRADE"]) * Convert.ToInt32(gradeList[j]["CREDITS"]));
}
if (gradeList[j]["DEPT_NAME"] == "소프트웨어학부" && gradeList[j]["GRADE"] != "-")
{
softSumF += Convert.ToInt32(gradeList[j]["CREDITS"]);
softGradeF += getGrade(gradeList[j]["GRADE"]) * Convert.ToInt32(gradeList[j]["CREDITS"]);
if (gradeList[j]["GRADE"] != "F")
{
softSum += Convert.ToInt32(gradeList[j]["CREDITS"]); softGrade += getGrade(gradeList[j]["GRADE"]) * Convert.ToInt32(gradeList[j]["CREDITS"]);
}
}
else if (gradeList[j]["DEPT_NAME"] != "소프트웨어학부" && gradeList[j]["GRADE"] != "-")
{
notSoftSumF += Convert.ToInt32(gradeList[j]["CREDITS"]);
notSoftGradeF += getGrade(gradeList[j]["GRADE"]) * Convert.ToInt32(gradeList[j]["CREDITS"]);
if (gradeList[j]["GRADE"] != "F")
{
notSoftSum += Convert.ToInt32(gradeList[j]["CREDITS"]); notSoftGrade += getGrade(gradeList[j]["GRADE"]) * Convert.ToInt32(gradeList[j]["CREDITS"]);
}
}
}
}
yearHakgiList[i]["SUM"] = sum.ToString();
if (sbjSumF == 0)
{
yearHakgiList[i]["AVG_GRADE_F"] = string.Format("{0:0.0#}", gradeF);
}
else
{
yearHakgiList[i]["AVG_GRADE_F"] = string.Format("{0:0.0#}", gradeF / sbjSumF);
}
if (sbjSum == 0)
{
yearHakgiList[i]["AVG_GRADE"] = string.Format("{0:0.0#}", grade);
}
else
{
yearHakgiList[i]["AVG_GRADE"] = string.Format("{0:0.0#}", grade / sbjSum);
}
if (softSumF == 0)
{
yearHakgiList[i]["SOFT_GRADE_F"] = string.Format("{0:0.0#}", softGradeF);
}
else
{
yearHakgiList[i]["SOFT_GRADE_F"] = string.Format("{0:0.0#}", softGradeF / softSumF);
}
if (softSum == 0)
{
yearHakgiList[i]["SOFT_GRADE"] = string.Format("{0:0.0#}", softGrade);
}
else
{
yearHakgiList[i]["SOFT_GRADE"] = string.Format("{0:0.0#}", softGrade / softSum);
}
if (notSoftSumF == 0)
{
yearHakgiList[i]["NOT_SOFT_GRADE_F"] = string.Format("{0:0.0#}", notSoftGradeF);
}
else
{
yearHakgiList[i]["NOT_SOFT_GRADE_F"] = string.Format("{0:0.0#}", notSoftGradeF / notSoftSumF);
}
if (notSoftSumF == 0)
{
yearHakgiList[i]["NOT_SOFT_GRADE"] = string.Format("{0:0.0#}", notSoftGrade);
}
else
{
yearHakgiList[i]["NOT_SOFT_GRADE"] = string.Format("{0:0.0#}", notSoftGrade / notSoftSum);
}
}
ViewBag.yearHakgiList = yearHakgiList;
ViewBag.yearHakgiCount = yearHakgiList.Count;
ViewBag.gradeList = gradeList;
ViewBag.gradeCount = gradeList.Count;
return(View("/Views/LrnResult/ScreInqire.cshtml"));
}
public IActionResult ShowCalendar() {
UserModel userInfo = SessionExtensionTool.GetObject<UserModel>(HttpContext.Session, "userInfo");
if (userInfo == null) {
return RedirectToAction("/Login", new { alertLogin = 2 });
}
DateTime nowDt = DateTime.Now;
if (nowDt.DayOfWeek == DayOfWeek.Monday)
ViewBag.DayOfWeek = 0;
else if (nowDt.DayOfWeek == DayOfWeek.Thursday)
ViewBag.DayOfWeek = 1;
else if (nowDt.DayOfWeek == DayOfWeek.Wednesday)
ViewBag.DayOfWeek = 2;
else if (nowDt.DayOfWeek == DayOfWeek.Thursday)
ViewBag.DayOfWeek = 3;
else if (nowDt.DayOfWeek == DayOfWeek.Friday)
ViewBag.DayOfWeek = 4;
else if (nowDt.DayOfWeek == DayOfWeek.Saturday)
ViewBag.DayOfWeek = 5;
ViewData["name"] = userInfo.name;
ViewData["user_id"] = userInfo.user_id;
ViewData["author"] = userInfo.author;
ViewData["Today"] = DateTime.Now.ToString().Split(' ')[0];
ViewBag.userinfo = userInfo;
string sql = "SELECT COUNT(*) AS TAKES_CNT "
+ "FROM OP_TAKES "
+ "WHERE ID=" + userInfo.user_id;
int takes_cnt = 0;
takes_cnt = Convert.ToInt32(_commonDao.SelectOne(sql)["TAKES_CNT"]);
if (userInfo.author.Equals("1000")) {
sql = "SELECT DISTINCT A.YEAR FROM OP_TEACHES A "
+ "JOIN OP_USER B ON A.ID = B.USER_ID "
+ "JOIN OP_SECTION C on A.SEC_ID = C.SEC_ID and A.COURSE_ID = C.COURSE_ID and A.SEMESTER = C.SEMESTER and A.YEAR = C.YEAR "
+ "JOIN OP_COURSE D ON C.COURSE_ID = D.COURSE_ID "
+ "JOIN OP_TIME_SLOT E on C.TIME_SLOT_ID = E.TIME_SLOT_ID "
+ "WHERE ID=" + userInfo.user_id+" ORDER BY A.YEAR DESC";
} else {
sql = "SELECT DISTINCT A.YEAR FROM OP_TAKES A "
+ "JOIN OP_USER B ON A.ID = B.USER_ID "
+ "JOIN OP_SECTION C on A.SEC_ID = C.SEC_ID and A.COURSE_ID = C.COURSE_ID and A.SEMESTER = C.SEMESTER and A.YEAR = C.YEAR "
+ "JOIN OP_COURSE D ON C.COURSE_ID = D.COURSE_ID "
+ "JOIN OP_TIME_SLOT E on C.TIME_SLOT_ID = E.TIME_SLOT_ID "
+ "WHERE ID=" + userInfo.user_id+ " ORDER BY A.YEAR DESC";
}
var yearList = _commonDao.SelectList(sql);
ViewBag.yearList = yearList;
ViewBag.yearCount = yearList.Count;
var hakgiList = new List<Dictionary<string, string>>();
hakgiList.Add(new Dictionary<string, string>());
hakgiList.Add(new Dictionary<string, string>());
hakgiList[0].Add("SEMESTER", "1");
hakgiList[1].Add("SEMESTER", "2");
ViewBag.hakgiList = hakgiList;
ViewBag.hakgiCount = hakgiList.Count;
bool isSelected = false;
if (Request.HasFormContentType && !Request.Form["year"].ToString().Equals("")) {
for (int i = 0; i < yearList.Count; ++i) {
if (Request.Form["year"] == yearList[i]["YEAR"]) {
ViewBag.selectedYear = Request.Form["year"];
ViewBag.selectedYearIndex = i;
isSelected = true;
}
}
}
if (!isSelected) {
ViewBag.selectedYear = yearList[0]["YEAR"];
ViewBag.selectedYearIndex = 0;
}
isSelected = false;
if (Request.HasFormContentType && !Request.Form["hakgi"].ToString().Equals("")) {
for (int i = 0; i < hakgiList.Count; ++i) {
if (Request.Form["hakgi"] == hakgiList[i]["SEMESTER"]) {
ViewBag.selectedHakgi = Request.Form["hakgi"];
ViewBag.selectedHakgiIndex = i;
isSelected = true;
}
}
}
if (!isSelected) {
ViewBag.selectedHakgi = hakgiList[0]["SEMESTER"];
ViewBag.selectedHakgiIndex = 0;
}
if (takes_cnt > 0 && userInfo.author.Equals("2000")) {
sql = "SELECT TITLE,NAME,DAY1,DAY2,PERIOD1,PERIOD2,BUILDING,ROOM_NUMBER,USER_ID,ACDMC_NO,A.ID,A.YEAR,A.SEMESTER, "
+ "(SELECT OP_ADVISOR.I_ID FROM OP_USER JOIN OP_ADVISOR ON OP_USER.USER_ID=OP_ADVISOR.I_ID WHERE OP_ADVISOR.S_ID=A.ID) AS ADVISOR_ID, "
+ "(SELECT NAME FROM OP_USER JOIN OP_ADVISOR ON OP_USER.USER_ID=OP_ADVISOR.I_ID WHERE OP_ADVISOR.S_ID=A.ID) AS ADVISOR, "
+ "(SELECT EMAIL FROM OP_USER JOIN OP_ADVISOR ON OP_USER.USER_ID=OP_ADVISOR.I_ID WHERE OP_ADVISOR.S_ID=A.ID) AS AD_EMAIL, "
+ "(SELECT PHONE FROM OP_USER JOIN OP_ADVISOR ON OP_USER.USER_ID=OP_ADVISOR.I_ID WHERE OP_ADVISOR.S_ID=A.ID) AS AD_PHONE "
+ "FROM OP_TAKES A "
+ "JOIN OP_TEACHES B "
+ "ON A.SEC_ID=B.SEC_ID AND A.COURSE_ID=B.COURSE_ID AND A.SEMESTER=B.SEMESTER AND A.YEAR=B.YEAR "
+ "JOIN OP_SECTION C "
+ "ON A.SEC_ID=C.SEC_ID AND A.COURSE_ID=C.COURSE_ID AND A.SEMESTER=C.SEMESTER AND A.YEAR=C.YEAR "
+ "JOIN OP_COURSE D ON A.COURSE_ID=D.COURSE_ID "
+ "JOIN OP_USER E ON E.USER_ID=B.ID "
+ "JOIN OP_TIME_SLOT F on C.TIME_SLOT_ID = F.TIME_SLOT_ID ";
if (Request.HasFormContentType && !Request.Form["year"].ToString().Equals("")) sql += "WHERE A.ID=" + userInfo.user_id + " AND A.YEAR=" + Request.Form["year"] + " AND A.SEMESTER=" + Request.Form["hakgi"];
else sql += "WHERE A.ID=" + userInfo.user_id + " AND A.YEAR=" + yearList[0]["YEAR"] + " AND A.SEMESTER=" + hakgiList[0]["SEMESTER"];
var resultList = _commonDao.SelectList(sql);
ViewBag.ResultList = resultList;
ViewBag.ResultCount = resultList.Count;
}
sql = "SELECT COUNT(*) AS TEACHES_CNT "
+ "FROM OP_TEACHES "
+ "WHERE ID=" + userInfo.user_id;
int teaches_cnt = 0;
teaches_cnt = Convert.ToInt32(_commonDao.SelectOne(sql)["TEACHES_CNT"]);
if (teaches_cnt > 0 && userInfo.author.Equals("1000")) {
sql = "SELECT TITLE,ACDMC_NO,DAY1,DAY2,PERIOD1,PERIOD2,BUILDING,ROOM_NUMBER,NAME,ID,A.YEAR,A.SEMESTER FROM OP_TEACHES A "
+ "JOIN OP_USER B ON A.ID = B.USER_ID "
+ "JOIN OP_SECTION C on A.SEC_ID = C.SEC_ID and A.COURSE_ID = C.COURSE_ID and A.SEMESTER = C.SEMESTER and A.YEAR = C.YEAR "
+ "JOIN OP_COURSE D ON C.COURSE_ID = D.COURSE_ID "
+ "JOIN OP_TIME_SLOT E on C.TIME_SLOT_ID = E.TIME_SLOT_ID ";
if (Request.HasFormContentType && !Request.Form["year"].ToString().Equals("")) sql += "WHERE A.ID=" + userInfo.user_id + " AND A.YEAR=" + Request.Form["year"] + " AND A.SEMESTER=" + Request.Form["hakgi"];
else sql += "WHERE A.ID=" + userInfo.user_id + " AND A.YEAR=" + yearList[0]["YEAR"] + " AND A.SEMESTER=" + hakgiList[0]["SEMESTER"];
var resultList2 = _commonDao.SelectList(sql);
ViewBag.ResultListTeacher = resultList2;
ViewBag.TeachesCount = resultList2.Count;
}
return View("/Views/LctCalendar/LctCalendar.cshtml");
}
public IActionResult SelectRecsroom()
{
UserModel userInfo = SessionExtensionTool.GetObject <UserModel>(HttpContext.Session, "userInfo");
Dictionary <string, string> param = new Dictionary <string, string>();
ViewData["name"] = userInfo.name;
ViewData["user_id"] = userInfo.user_id;
ViewData["pageNm"] = "강의 자료실";
ViewData["fs_at"] = userInfo.author.Equals(_codeMngTool.getCode("AUTHOR", "PROFESSOR")) ? "Y" : "N";
ViewData["author"] = userInfo.author;
ViewBag.ACDMC_NO = Request.Form["selectedSubj"];
ViewBag.YEAR_HAKGI = Request.Form["selectedYearhakgi"];
ViewBag.SEARCH_TYPE = Request.Form["search_type"];
ViewBag.SEARCH_TXT = Request.Form["search_txt"].ToString().Replace("\\", "\\\\");
// 조회수 증가 쿼리
string sql = "UPDATE OP_BBS SET RDCNT = (SELECT RDCNT+1 AS RDCNT FROM OP_BBS WHERE BBS_ID = @BBS_ID:VARCHAR) WHERE BBS_ID = @BBS_ID:VARCHAR";
//cud 처리할 때는 트랜잭션 시작해주어야함
using var transaction = _context.Database.BeginTransaction();
_commonDao.Update(sql, Request.Form);
transaction.Commit();
param.Add("page", Request.Form["page"]);
sql = "SELECT * FROM ("
+ "SELECT A.ACDMC_NO AS SelectSubj"
+ ", A.TITLE"
+ ", A.OTHBC_AT"
+ ", A.CONTENTS"
+ ", A.REGIST_DT "
+ ", B.NAME "
+ ", A.BBS_ID "
+ ", A.RDCNT "
+ ", DOC_ID "
+ ", LEAD(BBS_ID) OVER(ORDER BY BBS_ID) AS NEXT_ID "
+ ", LEAD(TITLE) OVER(ORDER BY BBS_ID) AS NEXT_TITLE "
+ ", LAG(BBS_ID) OVER(ORDER BY BBS_ID) AS PREV_ID "
+ ", LAG(TITLE) OVER(ORDER BY BBS_ID) AS PREV_TITLE "
+ "FROM OP_BBS A "
+ "JOIN OP_USER B "
+ "ON A.REGISTER = B.USER_ID "
+ "WHERE 1=1 "
+ "AND ACDMC_NO = @selectedSubj:VARCHAR "
+ "AND BBS_CODE = '" + _codeMngTool.getCode("BBS", "RECSROOM") + "' "
+ "ORDER BY BBS_ID DESC)"
+ "WHERE BBS_ID = @BBS_ID:VARCHAR";
var result = _commonDao.SelectOne(sql, Request.Form);
// Comment 개수
sql = "SELECT COUNT(*) AS COMMENT_CNT FROM OP_BBS WHERE REF_ID = @BBS_ID:VARCHAR";
var commentCnt = _commonDao.SelectOne(sql, Request.Form)["COMMENT_CNT"];
ViewBag.commentCnt = commentCnt;
// Comment 정보
sql = "SELECT A.BBS_ID, B.NAME, A.REGIST_DT, A.CONTENTS " +
"FROM OP_BBS A JOIN OP_USER B " +
"ON A.REGISTER = B.USER_ID WHERE A.REF_ID = @BBS_ID:VARCHAR " +
"ORDER BY BBS_ID";
var commentList = _commonDao.SelectList(sql, Request.Form);
ViewBag.commentList = commentList;
ViewBag.result = result;
ViewBag.param = param;
ViewBag.Select = "/Recsroom/SelectRecsroom";
ViewBag.SelectPageList = "/Recsroom/SelectPageListRecsroom";
ViewBag.UpdateForm = "/Recsroom/UpdateFormRecsroom";
ViewBag.Insert = "/Recsroom/InsertRecsroom";
ViewBag.InsertComment = "/Recsroom/InsertCommentRecsroom";
ViewBag.DeleteComment = "/Recsroom/DeleteCommentRecsroom";
ViewBag.Delete = "/Recsroom/DeleteRecsroom";
int fcount = 0;
//첨부파일 읽어오기
if (result["DOC_ID"] != "")
{
sql = "SELECT FILE_NAME,FILE_EXTSN,FILE_ID FROM OP_FILE A JOIN OP_BBS B ON A.DOC_ID=B.DOC_ID"
+ " WHERE A.DOC_ID='" + result["DOC_ID"] + "'";
var fileList = _commonDao.SelectList(sql);
fcount = fileList.Count;
ViewBag.fileList = fileList;
}
ViewBag.fileCount = fcount;
return(View("/Views/LctSport/BoardViewStdPage.cshtml"));
}
