public int IsMentor(int COURSE_ID, string MENTOR)
{
var sql = "SELECT TOP 1 ID FROM HISTORY WHERE COURSE_ID=@COURSE_ID AND MENTOR=@MENTOR";
HISTORY his = DBManager <HISTORY> .ExecuteReader(sql, new { COURSE_ID = COURSE_ID, MENTOR = MENTOR }).FirstOrDefault();
return(his == null ? 0 : 1);
}
public virtual int GetLastestCourse()
{
var sql = "SELECT TOP 1 ID FROM COURSE ORDER BY TO_MONTH DESC";
COURSE course = DBManager <COURSE> .ExecuteReader(sql).FirstOrDefault();
return(course != null ? course.ID : 0);
}
public virtual List <ASSIGN> Select(int ID = 0)
{
var sql = "SELECT * FROM ASSIGN ";
if (ID == 0)
{
return(DBManager <ASSIGN> .ExecuteReader(sql));
}
sql += " WHERE ID=@ID";
return(DBManager <ASSIGN> .ExecuteReader(sql, new { ID = ID }));
}
public virtual List <EMPLOYEE> Select(string ID = "")
{
var sql = "SELECT * FROM EMPLOYEE ";
if (string.IsNullOrWhiteSpace(ID))
{
return(DBManager <EMPLOYEE> .ExecuteReader(sql));
}
sql += " WHERE ID=@ID";
return(DBManager <EMPLOYEE> .ExecuteReader(sql, new { ID = ID }));
}
public virtual List <COURSE> Select(int ID = 0)
{
var sql = "SELECT * FROM COURSE ";
if (ID == 0)
{
return(DBManager <COURSE> .ExecuteReader(sql + "ORDER BY ID DESC"));
}
sql += " WHERE ID=@ID";
return(DBManager <COURSE> .ExecuteReader(sql, new { ID = ID }));
}
public virtual List <ACTIVITY_IMG> Select(int ID = 0)
{
var sql = "SELECT * FROM ACTIVITY_IMG ";
if (ID == 0)
{
return(DBManager <ACTIVITY_IMG> .ExecuteReader(sql));
}
sql += " WHERE DETAIL_ID=@ID";
return(DBManager <ACTIVITY_IMG> .ExecuteReader(sql, new { ID = ID }));
}
public virtual List <HIS_DETAIL> Select(int ID = 0)
{
var sql = "SELECT * FROM HIS_DETAIL ";
if (ID == 0)
{
return(DBManager <HIS_DETAIL> .ExecuteReader(sql));
}
sql += " WHERE ID=@ID";
return(DBManager <HIS_DETAIL> .ExecuteReader(sql, new { ID = ID }));
}
public List <EMPLOYEE> GetListMentee(int COURSE_ID = 0, string MENTOR = "")
{
var sql = string.Format(@"
SELECT ID,NAME FROM EMPLOYEE WHERE ID IN (SELECT DISTINCT(EMP_ID) FROM HISTORY WHERE (@COURSE_ID=0 OR COURSE_ID=@COURSE_ID)
AND (@MENTOR='' OR MENTOR=@MENTOR)
)
");
return(DBManager <EMPLOYEE> .ExecuteReader(sql, new
{
COURSE_ID = COURSE_ID,
MENTOR = MENTOR,
}));
}
public bool Login(string EMP_ID, string Password)
{
var sql = "SELECT ID,NAME,DEPARTMENT,ROLE FROM EMPLOYEE WHERE ID=@EMP_ID AND PASSWORD=@PASSWORD";
EMPLOYEE employee = DBManager <EMPLOYEE> .ExecuteReader(sql, new { EMP_ID = EMP_ID, PASSWORD = Password }).FirstOrDefault();
if (employee == null)
{
return(false);
}
HttpContext.Current.Session["Username"] = employee.ID.Trim();
HttpContext.Current.Session["Name"] = employee.NAME.Trim();
HttpContext.Current.Session["Dept"] = employee.DEPARTMENT;
HttpContext.Current.Session["Role"] = employee.ROLE;
return(true);
}
public virtual List <ASSIGN> SelectPaging(int start = 0, int end = 10)
{
var sql = "SELECT * FROM(SELECT ROW_NUMBER() OVER (order by id) AS ROWNUM, * FROM ASSIGN) as u WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;";
return(DBManager <ASSIGN> .ExecuteReader(sql, new { start = start, end = end }));
}
public List <EMPLOYEE> GetListMentor(int COURSE_ID = 0)
{
var sql = "SELECT ID,NAME FROM EMPLOYEE WHERE ID IN (SELECT DISTINCT(MENTOR) FROM HISTORY WHERE @COURSE_ID=0 OR COURSE_ID=@COURSE_ID)";
return(DBManager <EMPLOYEE> .ExecuteReader(sql, new { COURSE_ID = COURSE_ID }));
}
public List <EMPLOYEE> Search(string Key, int start = 0, int end = 10)
{
var sql = "SELECT * FROM(SELECT ROW_NUMBER() OVER (order by ID) AS ROWNUM, * FROM EMPLOYEE WHERE ID LIKE @KEY +'%' OR NAME LIKE '%' +@KEY+ '%' OR DEPARTMENT LIKE '%' +@KEY+ '%') as u WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;";
return(DBManager <EMPLOYEE> .ExecuteReader(sql, new { KEY = Key, start = start, end = end }));
}
public virtual List <EMPLOYEE> GetDepartment()
{
var sql = "SELECT DISTINCT(DEPARTMENT) FROM EMPLOYEE WHERE DEPARTMENT IS NOT NULL AND DEPARTMENT <> ''";
return(DBManager <EMPLOYEE> .ExecuteReader(sql));
}
public virtual HISTORY GetLastId(int COURSE_ID, string EMP_ID, string MENTOR)
{
var sql = "SELECT ID FROM HISTORY WHERE COURSE_ID=@COURSE_ID AND EMP_ID=@EMP_ID AND MENTOR=@MENTOR";
return(DBManager <HISTORY> .ExecuteReader(sql, new { MENTOR = MENTOR, COURSE_ID = COURSE_ID, EMP_ID = EMP_ID }).FirstOrDefault());
}
