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 })); }