public string GetLastId()
{
var sql = "SELECT TOP 1 IDEA_ID FROM PROJECT ORDER BY IDEA_ID DESC";
PROJECT prj = DBManager <PROJECT> .ExecuteReader(sql).FirstOrDefault();
if (prj == null)
{
return(string.Empty);
}
return(prj.IDEA_ID);
}
public bool CheckLikeIdea(string EMP_ID, string IDEA_ID)
{
var sql = "SELECT IDEA_ID FROM IDEA_LIKE WHERE IDEA_ID=@IDEA_ID AND EMP_ID=@EMP_ID";
var result = DBManager <IDEA_LIKE> .ExecuteReader(sql, new { EMP_ID = EMP_ID, IDEA_ID = IDEA_ID });
if (result == null || result.Count == 0)
{
return(false);
}
return(true);
}
public string IsExist(string ID)
{
var sql = "SELECT TOP 1 IDEA_ID FROM PROJECT WHERE IDEA_ID=@ID";
PROJECT prj = DBManager <PROJECT> .ExecuteReader(sql, new { ID = ID }).FirstOrDefault();
if (prj == null)
{
return(string.Empty);
}
return(prj.IDEA_ID);
}
public List <EMPLOYEE> GetDepartment(string Div = "")
{
var sql = " SELECT distinct(RTRIM(LTRIM(DEPARTMENT))) as DEPARTMENT FROM EMPLOYEE WHERE DEPARTMENT IS NOT NULL AND DEPARTMENT <> ''";
if (string.IsNullOrWhiteSpace(Div))
{
return(DBManager <EMPLOYEE> .ExecuteReader(sql));
}
sql += " AND DIVISION=@DIV";
return(DBManager <EMPLOYEE> .ExecuteReader(sql, new { DIV = Div }));
}
public virtual List <IDEA> Select(string ID = "")
{
var sql = "SELECT * FROM IDEA ";
if (string.IsNullOrWhiteSpace(ID))
{
return(DBManager <IDEA> .ExecuteReader(sql));
}
sql += " WHERE ID=@ID";
return(DBManager <IDEA> .ExecuteReader(sql, new { ID = ID }));
}
public virtual List <PROJECT_PLAN> Select(string ID = "")
{
var sql = "SELECT * FROM PROJECT_PLAN ";
if (string.IsNullOrWhiteSpace(ID))
{
return(DBManager <PROJECT_PLAN> .ExecuteReader(sql));
}
sql += " WHERE IDEA_ID=@ID ORDER BY PLAN_DATE DESC";
return(DBManager <PROJECT_PLAN> .ExecuteReader(sql, new { ID = ID }));
}
public virtual List <EMPLOYEE> Select(string ID = "")
{
var sql = "SELECT * FROM EMPLOYEE ";
if (ID == "")
{
return(DBManager <EMPLOYEE> .ExecuteReader(sql));
}
sql += " WHERE EMP_ID=@ID";
return(DBManager <EMPLOYEE> .ExecuteReader(sql, new { ID = ID }));
}
public List <string> GetKPIUnit()
{
var sql = "SELECT DISTINCT(KPI_UNIT) FROM PROJECT WHERE KPI_UNIT IS NOT NULL";
List <PROJECT> prjs = DBManager <PROJECT> .ExecuteReader(sql);
List <string> units = new List <string>();
foreach (var prj in prjs)
{
units.Add(prj.KPI_UNIT);
}
return(units);
}
public bool Login(string EMP_ID, string Password)
{
var sql = "SELECT EMP_ID,EMP_NAME,DIVISION,DEPARTMENT,ROLE FROM EMPLOYEE WHERE EMP_ID=@EMP_ID AND EMP_PW=@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.EMP_ID.Trim();
HttpContext.Current.Session["Name"] = employee.EMP_NAME.Trim();
HttpContext.Current.Session["Dept"] = employee.DEPARTMENT.Trim();
HttpContext.Current.Session["Division"] = employee.DIVISION.Trim();
HttpContext.Current.Session["Role"] = employee.ROLE;
return(true);
}
public List <PROJECT> Search(string div, string dep, string grade, int start = 0, int end = 10)
{
var condition = string.Empty;
if (!string.IsNullOrWhiteSpace(div))
{
condition += "AND DIVISION='" + div + "' ";
}
if (!string.IsNullOrWhiteSpace(dep))
{
condition += "AND DEPARTMENT='" + dep + "' ";
}
if (!string.IsNullOrWhiteSpace(grade))
{
condition += "AND GRADE='" + grade + "' ";
}
var sql = string.Format(@"SELECT * FROM(SELECT ROW_NUMBER() OVER (order by IDEA_ID desc) AS ROWNUM, IDEA_ID,IDEA_TITLE,NAME,EMP_ID,
INS_DT FROM PROJECT AS P WHERE NOT EXISTS(SELECT IDEA_ID FROM KPI AS PL WHERE P.IDEA_ID=PL.IDEA_ID AND RESULT_VALUE > 0) condition) as u
WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;");
return(DBManager <PROJECT> .ExecuteReader(sql.Replace("condition", condition), new { start = start, end = end }));
}
public string GenerateId()
{
string id = DateTime.Now.ToString("yyMMdd");
string sql = "select top 1 ID from IDEA WHERE ID LIKE @id + '%' order by ID desc";
IDEA idea = DBManager <IDEA> .ExecuteReader(sql, new { id = id }).FirstOrDefault();
if (idea == null)
{
id = id + "0001";
}
else
{
string str = idea.ID.Trim().Substring(6);
string num = (Convert.ToInt32(str) + 1).ToString();
for (int i = 0; i < str.Length - num.Length; i++)
{
id += "0";
}
id += num.ToString();
}
return(id);
}
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 <PRJ_REPLY> GetReplyName(string IDEA_ID)
{
var sql = "SELECT REP_EMP_NAME FROM PRJ_REPLY WHERE IDEA_ID=@IDEA_ID";
return(DBManager <PRJ_REPLY> .ExecuteReader(sql, new { IDEA_ID = IDEA_ID }));
}
public dynamic GetIdea(string ID)
{
var sql = "SELECT I.*, E.DEPARTMENT,E.DIVISION,E.EMP_NAME FROM IDEA AS I INNER JOIN EMPLOYEE AS E ON I.EMP_ID=E.EMP_ID WHERE ID=@ID";
return(DBManager <dynamic> .ExecuteReader(sql, new { ID = ID }).FirstOrDefault());
}
public virtual List <KPI> SelectPaging(int start = 0, int end = 10)
{
var sql = "SELECT * FROM(SELECT ROW_NUMBER() OVER (order by id) AS ROWNUM, * FROM KPI) as u WHERE RowNum >= @start AND RowNum < @end ORDER BY RowNum;";
return(DBManager <KPI> .ExecuteReader(sql, new { start = start, end = end }));
}
public List <EMPLOYEE> GetListEmail()
{
var sql = "SELECT EMAIL FROM EMPLOYEE WHERE ROLE >= 1 AND EMAIL <> ''";
return(DBManager <EMPLOYEE> .ExecuteReader(sql));
}
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 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 }));
}
