public List <dynamic> GetHistory(string mentor = "", string mentee = "", int course_id = 0, string dept = "", int page = 1)
{
var start = (page - 1) * 10 + 1;
var end = start + 10 - 1;
var sql = string.Format(@"
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY H.EMP_ID) AS ROWNUM,
E.NAME AS EMP_NAME, PICTURE,DEPARTMENT,ARCHIEVEMENT,H.MENTOR AS MENTOR1,S.NAME AS SUBJECT,
C.NAME AS PERIOD, SCORE,RESULT_LEVEL,D.*,
(SELECT COUNT(1) FROM ACTIVITY_IMG AS IMG WHERE D.ID=IMG.DETAIL_ID) AS ACTIVITY
FROM COURSE AS C
INNER JOIN HISTORY AS H ON C.ID=H.COURSE_ID
INNER JOIN HIS_DETAIL AS D ON H.ID=D.HIS_ID
INNER JOIN EMPLOYEE AS E ON E.ID=H.EMP_ID
LEFT JOIN SUBJECT AS S ON S.ID=D.SUB_ID
WHERE (@MENTOR='' OR H.MENTOR=@MENTOR) AND (@EMP_ID='' OR H.EMP_ID=@EMP_ID) AND (@COURSE_ID=0 OR H.COURSE_ID=@COURSE_ID)
AND (@DEPT='' OR DEPARTMENT LIKE '%'+@DEPT+'%')) AS U
WHERE ROWNUM BETWEEN @start AND @end ORDER BY ROWNUM;
");
return(DBManager <HISTORY> .ExecuteDynamic(sql, new
{
MENTOR = mentor,
COURSE_ID = course_id,
EMP_ID = mentee,
DEPT = dept,
start = start,
end = end
}));
}
public virtual List <dynamic> GetCourseByID(int ID, int start = 0, int end = 10)
{
var sql = string.Format(@"SELECT * FROM(SELECT ROW_NUMBER() OVER (order by C.id) AS ROWNUM, H.*, C.NAME as COURSE, E.NAME as EMP_NAME,DEPARTMENT,
(SELECT TOP 1 NAME FROM EMPLOYEE WHERE ID=H.MENTOR) AS TEACHER
FROM COURSE AS C LEFT JOIN HISTORY AS H ON C.ID=H.COURSE_ID LEFT JOIN EMPLOYEE AS E ON H.EMP_ID=E.ID
WHERE C.ID=@ID
) as u WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;");
return(DBManager <dynamic> .ExecuteDynamic(sql, new { ID = ID, start = start, end = end }));
}
public List <dynamic> GetHistorySimpleExport(string mentor = "", string mentee = "", int course_id = 0, string dept = "")
{
var sql = string.Format(@"
SELECT ROW_NUMBER() OVER (order by COURSE_ID desc) AS ROWNUM,C.NAME AS COURSE,H.COURSE_ID,E.ID,E.DEPARTMENT,E.NAME,H.RESULT_LEVEL,SCORE
FROM COURSE AS C
INNER JOIN HISTORY AS H ON C.ID=H.COURSE_ID
INNER JOIN EMPLOYEE AS E ON E.ID=H.EMP_ID
WHERE (@MENTOR='' OR H.MENTOR=@MENTOR) AND (@EMP_ID='' OR H.EMP_ID=@EMP_ID) AND (@COURSE_ID=0 OR H.COURSE_ID=@COURSE_ID)
AND (@DEPARTMENT='' OR DEPARTMENT LIKE '%'+@DEPARTMENT+'%')
");
return(DBManager <HISTORY> .ExecuteDynamic(sql, new
{
MENTOR = mentor,
COURSE_ID = course_id,
EMP_ID = mentee,
DEPARTMENT = dept
}));
}
public List <dynamic> GetHistoryExport(string mentor = "", string mentee = "", int course_id = 0, string dept = "")
{
var sql = string.Format(@"
SELECT
E.NAME AS EMP_NAME, PICTURE,DEPARTMENT,H.MENTOR AS MENTOR1,S.NAME AS SUBJECT,ARCHIEVEMENT,
C.NAME AS PERIOD, SCORE,RESULT_LEVEL,D.*
FROM COURSE AS C
INNER JOIN HISTORY AS H ON C.ID=H.COURSE_ID
INNER JOIN HIS_DETAIL AS D ON H.ID=D.HIS_ID
INNER JOIN EMPLOYEE AS E ON E.ID=H.EMP_ID
LEFT JOIN SUBJECT AS S ON S.ID=D.SUB_ID
WHERE (@MENTOR='' OR H.MENTOR=@MENTOR) AND (@EMP_ID='' OR H.EMP_ID=@EMP_ID) AND (@COURSE_ID=0 OR H.COURSE_ID=@COURSE_ID)
AND (@DEPT='' OR DEPARTMENT LIKE '%'+@DEPT+'%')
");
return(DBManager <HISTORY> .ExecuteDynamic(sql, new
{
MENTOR = mentor,
COURSE_ID = course_id,
EMP_ID = mentee,
DEPT = dept
}));
}
public List <dynamic> GetHistorySimple(string mentor = "", string mentee = "", int course_id = 0, string dept = "", int page = 1)
{
var start = (page - 1) * 10 + 1;
var end = start + 10 - 1;
var sql = string.Format(@"
SELECT * FROM (
SELECT ROW_NUMBER() OVER (order by COURSE_ID desc) AS ROWNUM,C.NAME AS COURSE,H.COURSE_ID,E.ID,E.DEPARTMENT,E.NAME,H.RESULT_LEVEL,SCORE
FROM COURSE AS C
INNER JOIN HISTORY AS H ON C.ID=H.COURSE_ID
INNER JOIN EMPLOYEE AS E ON E.ID=H.EMP_ID
WHERE (@MENTOR='' OR H.MENTOR=@MENTOR) AND (@EMP_ID='' OR H.EMP_ID=@EMP_ID) AND (@COURSE_ID=0 OR H.COURSE_ID=@COURSE_ID)
AND (@DEPARTMENT='' OR DEPARTMENT LIKE '%'+@DEPARTMENT+'%')) AS U
WHERE ROWNUM BETWEEN @start AND @end ORDER BY ROWNUM");
return(DBManager <HISTORY> .ExecuteDynamic(sql, new
{
MENTOR = mentor,
COURSE_ID = course_id,
EMP_ID = mentee,
DEPARTMENT = dept,
start = start,
end = end
}));
}