public void removeFile(string docId)
{
string query = "SELECT FILE_ID FROM OP_FILE "
+ "WHERE DOC_ID='" + docId + "'";
string uploadFolder = System.IO.Directory.GetCurrentDirectory() + @"\wwwroot\upload";
string fName;
var willDeleteList = _commonDao.SelectList(query);
for (int i = 0; i < willDeleteList.Count; ++i)
{
if (willDeleteList[i]["FILE_ID"].Equals(""))
{
continue;
}
fName = Path.Combine(uploadFolder, willDeleteList[i]["FILE_ID"]);
File.Delete(fName);
}
query = "DELETE FROM OP_FILE"
+ " WHERE DOC_ID='" + docId + "'";
using var transaction2 = _context.Database.BeginTransaction();
_commonDao.Delete(query);
transaction2.Commit();
}
public List <Dictionary <string, string> > getCode(string group_nm)
{
string query = "SELECT A.GROUP_ID, A.CODE_ID, A.CODE_NM " +
"FROM OP_CODE A JOIN OP_CODE_GROUP B ON " +
"A.GROUP_ID = B.GROUP_ID " +
"WHERE B.GROUP_NM=@group_nm:VARCHAR";
var groupName = new Dictionary <string, string>()
{
{ "group_nm", group_nm }
};
List <Dictionary <string, string> > resultList = _commonDao.SelectList(query, groupName);
return(resultList);
}
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 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 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 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 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 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"));
}