public int ChangePassword(string EmpId, string pass, string newpass)
{
var sql = "UPDATE EMPLOYEE SET EMP_PW=@NEWPASS WHERE EMP_ID=@EMP_ID AND EMP_PW=@PASS";
return(DBManager <EMPLOYEE> .Execute(sql, new { NEWPASS = newpass, EMP_ID = EmpId, PASS = pass }));
}
public int ResetPassword(string EMP_ID)
{
var sql = "UPDATE EMPLOYEE SET EMP_PW='123' WHERE EMP_ID=@EMP_ID";
return(DBManager <EMPLOYEE> .Execute(sql, new { EMP_ID = EMP_ID }));
}
public int UpdateDivision(string EmpId, string Division)
{
var sql = "UPDATE EMPLOYEE SET DIVISION=@DIVISION WHERE EMP_ID=@EMP_ID";
return(DBManager <EMPLOYEE> .Execute(sql, new { DIVISION = Division, EMP_ID = EmpId }));
}
public int UpdateDepartment(string EmpId, string DEPARTMENT)
{
var sql = "UPDATE EMPLOYEE SET DEPARTMENT=@DEPARTMENT WHERE EMP_ID=@EMP_ID";
return(DBManager <EMPLOYEE> .Execute(sql, new { DEPARTMENT = DEPARTMENT, EMP_ID = EmpId }));
}
public List <EMPLOYEE> GetDivision()
{
var sql = "SELECT DISTINCT(DIVISION) FROM EMPLOYEE WHERE DIVISION IS NOT NULL AND DIVISION <> ''";
return(DBManager <EMPLOYEE> .ExecuteReader(sql));
}
public List <EMPLOYEE> GetListEmail()
{
var sql = "SELECT EMAIL FROM EMPLOYEE WHERE ROLE >= 1 AND EMAIL <> ''";
return(DBManager <EMPLOYEE> .ExecuteReader(sql));
}
public List <EMPLOYEE> Search(string Key, int start = 0, int end = 10)
{
var sql = "SELECT * FROM(SELECT ROW_NUMBER() OVER (order by EMP_ID) AS ROWNUM, * FROM EMPLOYEE WHERE EMP_ID LIKE @KEY +'%' OR EMP_NAME 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 int GetSearchCount(string Key)
{
var sql = "SELECT COUNT(1) AS CNT FROM EMPLOYEE WHERE EMP_ID LIKE @KEY +'%' OR EMP_NAME LIKE '%' +@KEY+ '%';";
return((int)DBManager <EMPLOYEE> .ExecuteScalar(sql, new { KEY = Key }));
}
public virtual int Insert(string IDEA_ID, string REP_EMP_ID, string REP_EMP_NAME, string COMMENTS, DateTime INS_DATE)
{
var sql = "INSERT INTO PRJ_REPLY(IDEA_ID,REP_EMP_ID,REP_EMP_NAME,COMMENTS,INS_DATE) VALUES(@IDEA_ID,@REP_EMP_ID,@REP_EMP_NAME,@COMMENTS,@INS_DATE)";
return(DBManager <PRJ_REPLY> .Execute(sql, new { IDEA_ID = IDEA_ID, REP_EMP_ID = REP_EMP_ID, REP_EMP_NAME = REP_EMP_NAME, COMMENTS = COMMENTS, INS_DATE = INS_DATE }));
}
public virtual int Update(string IDEA_ID, string REP_EMP_ID, string REP_EMP_NAME, string COMMENTS, DateTime INS_DATE, string ID)
{
var sql = "UPDATE PRJ_REPLY SET IDEA_ID=@IDEA_ID,REP_EMP_ID=@REP_EMP_ID,REP_EMP_NAME=@REP_EMP_NAME,COMMENTS=@COMMENTS,INS_DATE=@INS_DATE WHERE ID=@ID";
return(DBManager <PRJ_REPLY> .Execute(sql, new { IDEA_ID = IDEA_ID, REP_EMP_ID = REP_EMP_ID, REP_EMP_NAME = REP_EMP_NAME, COMMENTS = COMMENTS, INS_DATE = INS_DATE, ID = ID }));
}
public virtual int GetCount()
{
var sql = "SELECT COUNT(1) AS CNT FROM PRJ_REPLY;";
return((int)DBManager <PRJ_REPLY> .ExecuteScalar(sql));
}
public virtual List <PRJ_REPLY> SelectPaging(int start = 0, int end = 10)
{
var sql = "SELECT * FROM(SELECT ROW_NUMBER() OVER (order by id) AS ROWNUM, * FROM PRJ_REPLY) as u WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;";
return(DBManager <PRJ_REPLY> .ExecuteReader(sql, new { start = start, end = end }));
}
public dynamic GetProject(string ID)
{
var sql = "SELECT I.*, E.DEPARTMENT,E.DIVISION,E.EMP_NAME FROM PROJECT AS I INNER JOIN EMPLOYEE AS E ON I.EMP_ID=E.EMP_ID WHERE IDEA_ID=@ID";
return(DBManager <dynamic> .ExecuteReader(sql, new { ID = ID }).FirstOrDefault());
}
public List <IDEA_LIKE> GetLike1(string IDEA_ID)
{
var sql = "SELECT EMP_NAME FROM IDEA_LIKE WHERE IDEA_ID=@IDEA_ID";
return(DBManager <IDEA_LIKE> .ExecuteReader(sql, new { IDEA_ID = IDEA_ID }));
}
public int CountLike(string IDEA_ID)
{
var sql = "SELECT COUNT(1) AS CNT FROM PRJ_LIKE WHERE IDEA_ID=@IDEA_ID";
return((int)DBManager <PRJ_LIKE> .ExecuteScalar(sql, new{ IDEA_ID = IDEA_ID }));
}