/*
* 강의 자료실 리스트 페이지
* 작성자 : 이재하
* 기능 : 강의 자료실 리스트 페이지 호출
* */
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 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"));
}