/// <summary> /// 해당 세션ID 정보가 있는지 확인 /// </summary> public int CheckConnectLog(string SESSION_ID, string LOGIN_ID) { DataSet DS = new DataSet(); string query = @" SELECT COUNT(*) FROM COM_CONNECT_LOG WHERE SESSION_ID = @SESSION_ID AND LOGIN_ID = @LOGIN_ID"; int cnt; IDbDataParameter[] paramArray = CreateDataParameters(2); paramArray[0] = CreateDataParameter("@SESSION_ID", SqlDbType.NVarChar); paramArray[0].Value = SESSION_ID; paramArray[1] = CreateDataParameter("@LOGIN_ID", SqlDbType.NVarChar); paramArray[1].Value = LOGIN_ID; DS = DbAgentObj.Fill(query, paramArray); cnt = Convert.ToInt32(DS.Tables[0].Rows[0][0].ToString()); return(cnt); }
/// <summary> /// GetKPIStatus /// : KPI_REF_ID와 월정보를 가지고 KPI실적테이블에서 각 STATUS를 추출한다. /// </summary> /// <param name="asKpiRefID"></param> /// <param name="asMonthInfo"></param> /// <returns></returns> public DataSet GetKPIStatus(string asKpiRefID, string asMonthInfo) { IDbDataParameter[] paramArray = null; StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("SELECT ISNULL(CHECKSTATUS, 0) CHECKSTATUS, \n"); sbQuery.Append(" ISNULL(CONFIRMSTATUS, 0) CONFIRMSTATUS, \n"); sbQuery.Append(" V_CNT \n"); sbQuery.Append(" FROM ( \n"); sbQuery.Append(" SELECT MAX(CONVERT (INT, CHECKSTATUS)) CHECKSTATUS, \n"); sbQuery.Append(" MAX(CONVERT (INT, CONFIRMSTATUS)) CONFIRMSTATUS, \n"); sbQuery.Append(" COUNT(*) V_CNT \n"); sbQuery.Append(" FROM KPI_RESULT \n"); sbQuery.Append(" WHERE KPI_REF_ID= CONVERT(INT, @KPI_REF_ID) \n"); sbQuery.Append(" AND TMCODE = CONVERT(INT, @TMCODE) \n"); sbQuery.Append(" ) A \n"); paramArray = CreateDataParameters(2); paramArray[0] = CreateDataParameter("@KPI_REF_ID", SqlDbType.VarChar); paramArray[1] = CreateDataParameter("@TMCODE", SqlDbType.VarChar); paramArray[0].Value = asKpiRefID; paramArray[1].Value = asMonthInfo; return(DbAgentObj.Fill(sbQuery.ToString(), paramArray)); }
/// <summary> /// GetSearchCode /// : 평가등급 수정시 검색 /// </summary> /// <param name="asCodeID"></param> /// <returns></returns> public DataSet GetSearchCode(string asCodeID) { IDbDataParameter[] paramArray = null; StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("SELECT KPI_THRESHOLD_CODE_ID V_CODE_ID , \n"); sbQuery.Append(" KPI_THRESHOLD_TYPE V_TYPE , \n"); sbQuery.Append(" KPI_THRESHOLD_STEPNAME V_STEPNAME , \n"); sbQuery.Append(" BASE_MIN_VALUE V_MIN_VALUE , \n"); sbQuery.Append(" MARK_COLOR V_COLOR , \n"); sbQuery.Append(" MARK_IMAGE_PATH V_IMG_PATH , \n"); sbQuery.Append(" POINT V_POINT , \n"); sbQuery.Append(" SEQUENCE \n"); sbQuery.Append(" FROM COM_KPI_THRESHOLD_CODE \n"); sbQuery.Append(" WHERE 1=1 \n"); sbQuery.Append(" AND KPI_THRESHOLD_CODE_ID = CONVERT(int, @KPI_THRESHOLD_CODE_ID) \n"); sbQuery.Append(" ORDER BY \n"); sbQuery.Append(" SEQUENCE \n"); paramArray = CreateDataParameters(1); paramArray[0] = CreateDataParameter("@KPI_THRESHOLD_CODE_ID", SqlDbType.VarChar); paramArray[0].Value = asCodeID; return(DbAgentObj.Fill(sbQuery.ToString(), paramArray)); }
public DataSet GetTopMenuInfo() { StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("SELECT \n"); sbQuery.Append(" MENU_REF_ID , \n"); sbQuery.Append(" UP_MENU_ID , \n"); sbQuery.Append(" MENU_NAME , \n"); sbQuery.Append(" MENU_DIR , \n"); sbQuery.Append(" MENU_PAGE_NAME , \n"); sbQuery.Append(" MENU_PARAM , \n"); sbQuery.Append(" MENU_FULL_PATH , \n"); sbQuery.Append(" MENU_DESC , \n"); sbQuery.Append(" MENU_PRIORITY , \n"); sbQuery.Append(" MENU_TYPE , \n"); sbQuery.Append(" MENU_NAME_IMAGE_PATH , \n"); sbQuery.Append(" MENU_PREV_ICON_PATH , \n"); sbQuery.Append(" MENU_CREATE_DATE \n"); sbQuery.Append(" FROM COM_MENU_INFO \n"); sbQuery.Append(" WHERE MENU_TYPE = 'T' \n"); sbQuery.Append(" ORDER BY \n"); sbQuery.Append(" MENU_PRIORITY \n"); return(DbAgentObj.Fill(sbQuery.ToString())); }
public DataSet GetMenuInfo(int aiUpRefID) { IDbDataParameter[] paramArray = null; StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("SELECT \n"); sbQuery.Append(" MENU_REF_ID , \n"); sbQuery.Append(" UP_MENU_ID , \n"); sbQuery.Append(" MENU_NAME , \n"); sbQuery.Append(" MENU_DIR , \n"); sbQuery.Append(" MENU_PAGE_NAME , \n"); sbQuery.Append(" MENU_PARAM , \n"); sbQuery.Append(" MENU_FULL_PATH , \n"); sbQuery.Append(" MENU_DESC , \n"); sbQuery.Append(" MENU_PRIORITY , \n"); sbQuery.Append(" MENU_TYPE , \n"); sbQuery.Append(" MENU_NAME_IMAGE_PATH , \n"); sbQuery.Append(" MENU_PREV_ICON_PATH , \n"); sbQuery.Append(" MENU_CREATE_DATE \n"); sbQuery.Append(" FROM COM_MENU_INFO \n"); sbQuery.Append(" WHERE 1=1 \n"); sbQuery.Append(" AND UP_MENU_ID = @UP_MENU_ID \n"); sbQuery.Append(" ORDER BY \n"); sbQuery.Append(" MENU_PRIORITY \n"); paramArray = CreateDataParameters(1); paramArray[0] = CreateDataParameter("@UP_MENU_ID", SqlDbType.Int); paramArray[0].Value = aiUpRefID; return(DbAgentObj.Fill(sbQuery.ToString(), paramArray)); }
public DataTable SelectInPower(string cr_year) { string query = @" SELECT CR_YEAR ,CR_MONTH ,CR_MONTH || '월' AS CR_MONTH_NAME ,TARGET_FULL_RATE ,RESULT_FULL_RATE ,RESULT_DONG_RATE ,RESULT_BDONG_RATE ,TARGET_FULL_RATE / 100 AS TARGET_FULL_RATE_001 ,RESULT_FULL_RATE / 100 AS RESULT_FULL_RATE_001 ,RESULT_DONG_RATE / 100 AS RESULT_DONG_RATE_001 ,RESULT_BDONG_RATE / 100 AS RESULT_BDONG_RATE_001 ,TARGET_FULL_RATE * 100 AS TARGET_FULL_RATE_100 ,RESULT_FULL_RATE * 100 AS RESULT_FULL_RATE_100 ,RESULT_DONG_RATE * 100 AS RESULT_DONG_RATE_100 ,RESULT_BDONG_RATE * 100 AS RESULT_BDONG_RATE_100 FROM NHIT_DASHBOARD_EMP_MERGE WHERE CR_YEAR = @CR_YEAR ORDER BY CR_MONTH "; IDbDataParameter[] paramArray = CreateDataParameters(1); paramArray[0] = CreateDataParameter("@CR_YEAR", SqlDbType.NVarChar); paramArray[0].Value = cr_year; return(DbAgentObj.Fill(query, paramArray).Tables[0]); }
public DataSet GetMenuInfo() { StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("SELECT MENU_REF_ID , \n"); sbQuery.Append(" UP_MENU_ID , \n"); sbQuery.Append(" MENU_NAME , \n"); sbQuery.Append(" MENU_DIR , \n"); sbQuery.Append(" MENU_PAGE_NAME , \n"); sbQuery.Append(" MENU_PARAM , \n"); sbQuery.Append(" MENU_FULL_PATH , \n"); sbQuery.Append(" MENU_DESC , \n"); sbQuery.Append(" MENU_PRIORITY , \n"); sbQuery.Append(" MENU_TYPE , \n"); sbQuery.Append(" MENU_NAME_IMAGE_PATH , \n"); sbQuery.Append(" MENU_PREV_ICON_PATH , \n"); sbQuery.Append(" MENU_CREATE_DATE , \n"); sbQuery.Append(" CASE MENU_TYPE WHEN 'T' THEN 1 \n"); sbQuery.Append(" WHEN 'M' THEN 2 \n"); sbQuery.Append(" WHEN 'S' THEN 3 \n"); sbQuery.Append(" ELSE 4 \n"); sbQuery.Append(" END V_ORDER \n"); sbQuery.Append(" FROM COM_MENU_INFO \n"); sbQuery.Append(" ORDER BY \n"); sbQuery.Append(" V_ORDER , \n"); sbQuery.Append(" MENU_PRIORITY \n"); return(DbAgentObj.Fill(sbQuery.ToString())); }
/// <summary> /// 정보가 없으면 "" 리턴 /// </summary> public string Select_EMP_REF_ID(string LOGIN_ID) { DataSet DS = new DataSet(); string result; string query = @" SELECT EMP_REF_ID FROM COM_EMP_INFO WHERE LOGINID = @LOGIN_ID ORDER BY MODIFY_DATE DESC"; IDbDataParameter[] paramArray = CreateDataParameters(1); paramArray[0] = CreateDataParameter("@LOGIN_ID", SqlDbType.NVarChar); paramArray[0].Value = LOGIN_ID; DS = DbAgentObj.Fill(query, paramArray); if (DS.Tables[0].Rows.Count == 0) { result = ""; } else { result = DS.Tables[0].Rows[0][0].ToString(); } return(result); }
public DataTable Select_DB(object dept_ref_id) { string query = @" SELECT DEPT_REF_ID , UP_DEPT_ID , DEPT_LEVEL , DEPT_NAME , DEPT_CODE , DEPT_FLAG , DEPT_TYPE , SORT_ORDER , DEPT_DESC , CREATE_USER , UPDATE_USER FROM COM_DEPT_INFO WHERE ( DEPT_REF_ID = @DEPT_REF_ID OR @DEPT_REF_ID = 0 ) "; IDbDataParameter[] paramArray = CreateDataParameters(1); paramArray[0] = CreateDataParameter("@DEPT_REF_ID", SqlDbType.Int); paramArray[0].Value = dept_ref_id; DataTable dt = DbAgentObj.Fill(query, paramArray).Tables[0]; return(dt); }
/// <summary> /// GetDSCancelMailInfo /// : 취소시에 메일링 처리할 내용을 리턴한다. /// </summary> /// <param name="asAppRefID"></param> /// <param name="asAppStep"></param> /// <returns></returns> public DataSet GetDSCancelMailInfo(string asAppRefID, string asAppStep) { IDbDataParameter[] paramArray = null; StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("SELECT D.KPI_CODE + ' (' + D.KPI_NAME + ')' V_KPIINFO, \n"); sbQuery.Append(" F.EMP_NAME V_APP_EMP_NAME, \n"); sbQuery.Append(" F.EMP_EMAIL V_APP_EMP_EMAIL, \n"); sbQuery.Append(" E.EMP_EMAIL V_REP_EMP_EMAIL \n"); sbQuery.Append(" FROM COM_APPROVAL_INFO A, \n"); sbQuery.Append(" COM_APPROVAL_PRC B, \n"); sbQuery.Append(" KPI_INFO D, \n"); sbQuery.Append(" COM_EMP_INFO E, \n"); sbQuery.Append(" COM_EMP_INFO F \n"); sbQuery.Append(" WHERE A.APP_REF_ID = B.APP_REF_ID \n"); sbQuery.Append(" AND B.APP_STEP = CONVERT(INT,@APP_STEP) \n"); sbQuery.Append(" AND D.KPI_REF_ID = A.EVENT_ID \n"); sbQuery.Append(" AND E.EMP_REF_ID = A.REP_EMP_ID \n"); sbQuery.Append(" AND F.EMP_REF_ID = B.APP_EMP_ID \n"); sbQuery.Append(" AND A.APP_REF_ID = @APP_REF_ID \n"); paramArray = CreateDataParameters(2); paramArray[0] = CreateDataParameter("@APP_REF_ID", SqlDbType.VarChar); paramArray[1] = CreateDataParameter("@APP_STEP", SqlDbType.VarChar); paramArray[0].Value = asAppRefID; paramArray[1].Value = asAppStep; return(DbAgentObj.Fill(sbQuery.ToString(), paramArray)); }
/// <summary> /// 최상위 객체의 정보를 가지고 온다. (UP_STG_ID 이 NULL 인 개체) /// </summary> /// <returns></returns> public DataSet GetTopParents(int isContainType) { // -1->최상위부모가아님, 0->전체, 1->자식이존재함, 2->자식이존재하지않음 string query = @" SELECT * FROM ( SELECT A.ESTTERM_REF_ID , A.EST_DEPT_REF_ID , A.STG_REF_ID , NULL AS UP_STG_ID , A.VIEW_REF_ID AS STG_MAP_TYPE , B.VIEW_SEQ , A.SORT_ORDER , CASE WHEN (SELECT COUNT(*) FROM BSC_MAP_STG_PARENT WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID AND MAP_VERSION_ID = @MAP_VERSION_ID AND UP_STG_REF_ID = A.STG_REF_ID) > 0 THEN 1 ELSE 2 END AS ISCONTAIN FROM BSC_MAP_STG A JOIN BSC_VIEW_INFO B ON A.VIEW_REF_ID = B.VIEW_REF_ID WHERE A.ESTTERM_REF_ID = @ESTTERM_REF_ID AND A.EST_DEPT_REF_ID = @EST_DEPT_REF_ID AND A.MAP_VERSION_ID = @MAP_VERSION_ID AND A.STG_REF_ID NOT IN (SELECT STG_REF_ID FROM BSC_MAP_STG_PARENT WHERE ESTTERM_REF_ID = @ESTTERM_REF_ID AND EST_DEPT_REF_ID = @EST_DEPT_REF_ID AND MAP_VERSION_ID = @MAP_VERSION_ID) ) X WHERE (X.ISCONTAIN = @ISCONTAIN OR @ISCONTAIN = 0) ORDER BY X.VIEW_SEQ, X.SORT_ORDER"; IDbDataParameter[] paramArray = CreateDataParameters(4); paramArray[0] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int); paramArray[0].Value = _estterm_ref_id; paramArray[1] = CreateDataParameter("@EST_DEPT_REF_ID", SqlDbType.Int); paramArray[1].Value = _est_dept_ref_id; paramArray[2] = CreateDataParameter("@ISCONTAIN", SqlDbType.Int); paramArray[2].Value = isContainType; paramArray[3] = CreateDataParameter("@MAP_VERSION_ID", SqlDbType.Int); paramArray[3].Value = StgMaps_Data.GetMapVersionID(_estterm_ref_id, _est_dept_ref_id, _est_month, _map_version_id); DataSet ds = DbAgentObj.Fill(query, paramArray, CommandType.Text); return(ds); }
/// <summary> /// GetDDLAppGubun /// : 업무 구분 드롭다운리스트 추출 /// </summary> /// <returns></returns> public DataSet GetDDLAppGubun() { StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("SELECT BIZ_TYPE_CODE V_CODE, \n"); sbQuery.Append(" BIZ_TYPE_NAME V_NAME \n"); sbQuery.Append(" FROM COM_BIZ_INFO \n"); sbQuery.Append(" ORDER BY BIZ_TYPE_CODE DESC \n"); return(DbAgentObj.Fill(sbQuery.ToString())); }
/// <summary> /// GetDDLEstTerm /// : 평가 기간 드롭다운리스트 추출 /// </summary> /// <returns></returns> public DataSet GetDDLEstTerm() { StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("SELECT ESTTERM_REF_ID V_CODE, \n"); sbQuery.Append(" ESTTERM_NAME V_NAME \n"); sbQuery.Append(" FROM EST_TERM_INFO \n"); sbQuery.Append(" ORDER BY \n"); sbQuery.Append(" EST_STARTDATE DESC \n"); return(DbAgentObj.Fill(sbQuery.ToString())); }
public DataTable Select_Com_Dept_Type() { DataSet DS = new DataSet(); string query = @" SELECT TYPE_REF_ID FROM COM_DEPT_TYPE_INFO"; DS = DbAgentObj.Fill(query); return(DS.Tables[0]); }
/// <summary> /// GetAllType /// : 측정단계 드롭다운리스트 추출 /// </summary> /// <returns></returns> public DataSet GetAllType() { StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("SELECT DISTINCT \n"); sbQuery.Append(" CONVERT(VARCHAR,KPI_THRESHOLD_TYPE) + ' 단계' V_TYPE_NAME, \n"); sbQuery.Append(" KPI_THRESHOLD_TYPE V_TYPE \n"); sbQuery.Append(" FROM COM_KPI_THRESHOLD_CODE \n"); sbQuery.Append(" ORDER BY \n"); sbQuery.Append(" KPI_THRESHOLD_TYPE \n"); return(DbAgentObj.Fill(sbQuery.ToString())); }
public DataTable Select_Est_Position_Table(object pos_id , object pos_table_name) { string query = string.Format(@" SELECT POS_{0}_ID , POS_{0}_NAME FROM {1} ", pos_id, pos_table_name); DataTable dt = DbAgentObj.Fill(query).Tables[0]; return(dt); }
// 전사 매출/손익 public DataTable SelectJeonSa(object grp_one_code , object grp_two_code , object grp_three_code , object cr_year , object cr_month , object tg_gubun) { string query = @" SELECT GRP_ONE_CODE ,GRP_ONE_NAME ,GRP_TWO_CODE ,GRP_TWO_NAME ,GRP_THREE_CODE ,GRP_THREE_NAME ,CR_YEAR ,CR_MONTH ,CR_MONTH + '월' AS CR_MONTH_NAME ,TARGET_YEAR ,TARGET_TS ,RESULT_TS ,DAL_RATE FROM NHIT_DASHBOARD_MAIN WHERE GRP_ONE_CODE = @GRP_ONE_CODE AND GRP_TWO_CODE = @GRP_TWO_CODE AND GRP_THREE_CODE = @GRP_THREE_CODE AND (CR_YEAR = @CR_YEAR ) AND (CR_MONTH = @CR_MONTH OR @CR_MONTH ='' ) AND TG_GUBUN = @TG_GUBUN ORDER BY GRP_TWO_CODE, CR_MONTH "; IDbDataParameter[] paramArray = CreateDataParameters(6); paramArray[0] = CreateDataParameter("@GRP_ONE_CODE", SqlDbType.NVarChar); paramArray[0].Value = grp_one_code; paramArray[1] = CreateDataParameter("@GRP_TWO_CODE", SqlDbType.NVarChar); paramArray[1].Value = grp_two_code; paramArray[2] = CreateDataParameter("@GRP_THREE_CODE", SqlDbType.NVarChar); paramArray[2].Value = grp_three_code; paramArray[3] = CreateDataParameter("@CR_YEAR", SqlDbType.NVarChar); paramArray[3].Value = cr_year; paramArray[4] = CreateDataParameter("@CR_MONTH", SqlDbType.NVarChar); paramArray[4].Value = cr_month; paramArray[5] = CreateDataParameter("@TG_GUBUN", SqlDbType.NVarChar); paramArray[5].Value = tg_gubun; return(DbAgentObj.Fill(query, paramArray).Tables[0]); }
public DataTable Select_DB(object comp_id , object estterm_ref_id , object est_id , object seq , object pos_id , object pos_value , object dept_type_ref_id) { string query = @" SELECT COMP_ID , ESTTERM_REF_ID , EST_ID , SEQ , POS_ID , POS_VALUE , DEPT_TYPE_REF_ID , WEIGHT FROM EST_POS_WEIGHT WHERE ( COMP_ID = @COMP_ID OR @COMP_ID = 0 ) AND ( ESTTERM_REF_ID = @ESTTERM_REF_ID OR @ESTTERM_REF_ID = 0 ) AND ( EST_ID = @EST_ID OR @EST_ID ='' ) AND ( SEQ = @SEQ OR @SEQ = 0 ) AND ( POS_ID = @POS_ID OR @POS_ID ='' ) AND ( POS_VALUE = @POS_VALUE OR @POS_VALUE ='' ) AND ( DEPT_TYPE_REF_ID = @DEPT_TYPE_REF_ID OR @DEPT_TYPE_REF_ID = 0 ) "; IDbDataParameter[] paramArray = CreateDataParameters(7); paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int); paramArray[0].Value = comp_id; paramArray[1] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int); paramArray[1].Value = estterm_ref_id; paramArray[2] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar); paramArray[2].Value = est_id; paramArray[3] = CreateDataParameter("@SEQ", SqlDbType.Int); paramArray[3].Value = seq; paramArray[4] = CreateDataParameter("@POS_ID", SqlDbType.NVarChar); paramArray[4].Value = pos_id; paramArray[5] = CreateDataParameter("@POS_VALUE", SqlDbType.NVarChar); paramArray[5].Value = pos_value; paramArray[6] = CreateDataParameter("@DEPT_TYPE_REF_ID", SqlDbType.Int); paramArray[6].Value = dept_type_ref_id; DataTable dt = DbAgentObj.Fill(query, paramArray).Tables[0]; return(dt); }
public DataTable SelectRoot_DB() { DataSet DS = new DataSet(); string query = @" SELECT DEPT_REF_ID FROM COM_DEPT_INFO WHERE ISNULL(UP_DEPT_ID, 0) = 0 AND DEPT_FLAG = 1 "; DS = DbAgentObj.Fill(query, null); return(DS.Tables[0]); }
public DataTable Select_Est_Pos_Weight(object pos_id , object pos_table_name , object dept_type_ref_id_list , object comp_id , object estterm_ref_id , object est_id) { string query = string.Format(@" SELECT '{0}' AS POS_ID , POS.POS_{0}_ID AS POS_VALUE , POS.POS_{0}_NAME AS POS_VALUE_NAME , PW.WEIGHT , PW.SEQ , PW.DEPT_TYPE_REF_ID , DI.TYPE_NAME AS DEPT_TYPE_NAME FROM {1} POS LEFT OUTER JOIN EST_POS_WEIGHT PW ON ( POS.POS_{0}_ID = PW.POS_VALUE AND PW.POS_ID = POS_ID AND COMP_ID = @COMP_ID AND ESTTERM_REF_ID = @ESTTERM_REF_ID AND PW.DEPT_TYPE_REF_ID IN ( {2} ) AND EST_ID = @EST_ID ) LEFT OUTER JOIN COM_DEPT_TYPE_INFO DI ON ( PW.DEPT_TYPE_REF_ID=DI.TYPE_REF_ID ) ORDER BY PW.SEQ ASC ", pos_id, pos_table_name, dept_type_ref_id_list); IDbDataParameter[] paramArray = CreateDataParameters(3); paramArray[0] = CreateDataParameter("@COMP_ID", SqlDbType.Int); paramArray[0].Value = comp_id; paramArray[1] = CreateDataParameter("@ESTTERM_REF_ID", SqlDbType.Int); paramArray[1].Value = estterm_ref_id; paramArray[2] = CreateDataParameter("@EST_ID", SqlDbType.NVarChar); paramArray[2].Value = est_id; DataTable dt = DbAgentObj.Fill(query, paramArray).Tables[0]; return(dt); }
public DataTable SelectMonth_DB() { DataSet DS = new DataSet(); string query = @" SELECT CR_MONTH , CR_MONTH + '월' AS CR_MONTH_NAME FROM NHIT_DASHBOARD_MAIN GROUP BY CR_MONTH ORDER BY CR_MONTH "; DS = DbAgentObj.Fill(query, null); return(DS.Tables[0]); }
public DataTable Select_Emp_Info(object emp_ref_id) { string query = @" SELECT EMP_REF_ID , EMP_CODE , LOGINID , LOGINIP , PASSWD , EMP_NAME , EMP_EMAIL , POSITION_CLASS_CODE , POSITION_GRP_CODE , POSITION_RANK_CODE , POSITION_DUTY_CODE , POSITION_STAT_CODE , POSITION_KIND_CODE , DAILY_PHONE , CELL_PHONE , FAX_NUMBER , JOB_STATUS , ZIPCODE , ADDR_1 , ADDR_2 , SIGN_PATH , STAMP_PATH , CREATE_TYPE , CREATE_DATE , CREATE_EMP_ID , MODIFY_TYPE , MODIFY_DATE , MODIFY_EMP_ID FROM COM_EMP_INFO WHERE EMP_REF_ID = @EMP_REF_ID "; IDbDataParameter[] paramArray = CreateDataParameters(1); paramArray[0] = CreateDataParameter("@EMP_REF_ID", SqlDbType.NVarChar); paramArray[0].Value = emp_ref_id; DataTable dt = DbAgentObj.Fill(query, paramArray).Tables[0]; return(dt); }
public DataSet GetRoleInfo(int aiEmpRefID) { string query = @"SELECT ROLE_REF_ID , ROLE_NAME FROM COM_ROLE_INFO WHERE ROLE_REF_ID NOT IN (SELECT ROLE_REF_ID FROM COM_EMP_ROLE_REL WHERE EMP_REF_ID = @EMP_REF_ID) ORDER BY SORT_ORDER"; IDbDataParameter[] paramArray = CreateDataParameters(1); paramArray[0] = CreateDataParameter("@EMP_REF_ID", SqlDbType.Int); paramArray[0].Value = aiEmpRefID; return(DbAgentObj.Fill(query, paramArray)); }
/// <summary> /// 부서와 상위부서 /// </summary> public DataTable Select_Dept_UpDept() { string query = @" SELECT DEPT.DEPT_REF_ID AS DEPT_ID , DEPT.DEPT_NAME , DEPT.UP_DEPT_ID , UP_DEPT.DEPT_NAME AS UP_DEPT_NAME FROM COM_DEPT_INFO DEPT LEFT OUTER JOIN COM_DEPT_INFO UP_DEPT ON DEPT.UP_DEPT_ID = UP_DEPT.DEPT_REF_ID ORDER BY DEPT.SORT_ORDER "; DataTable dt = DbAgentObj.Fill(query).Tables[0]; return(dt); }
public DataTable SelectYear_DB() { DataSet DS = new DataSet(); string query = @" SELECT CR_YEAR , CR_YEAR + '년' AS CR_YEAR_NAME FROM NHIT_DASHBOARD_MAIN GROUP BY CR_YEAR ORDER BY CR_YEAR "; DS = DbAgentObj.Fill(query, null); return(DS.Tables[0]); }
public DataTable SelectEmpProjectDetail(object cr_year , object cr_month , object project_name) { string query = @" SELECT CR_YEAR ,CR_MONTH ,BONBU_NAME ,TEAM_NAME ,GRP_ONE_B_NAME ,GRP_ONE_C_NAME ,EMP_NAME ,RESULT_FULL ,RESULT_DONG ,RESULT_BDONG FROM NHIT_DASHBOARD_EMP WHERE CR_YEAR = @CR_YEAR AND CR_MONTH = @CR_MONTH AND PROJECT_NAME = @PROJECT_NAME ORDER BY CR_YEAR ,CR_MONTH ,BONBU_NAME ,TEAM_NAME ,GRP_ONE_B_NAME ,GRP_ONE_C_NAME ,EMP_NAME ,RESULT_FULL ,RESULT_DONG ,RESULT_BDONG "; IDbDataParameter[] paramArray = CreateDataParameters(3); paramArray[0] = CreateDataParameter("@CR_YEAR", SqlDbType.NVarChar); paramArray[0].Value = cr_year; paramArray[1] = CreateDataParameter("@CR_MONTH", SqlDbType.NVarChar); paramArray[1].Value = cr_month; paramArray[2] = CreateDataParameter("@PROJECT_NAME", SqlDbType.NVarChar); paramArray[2].Value = project_name; return(DbAgentObj.Fill(query, paramArray).Tables[0]); }
public DataTable Select_DB(object pos_id , object pos_name , object pos_table_name , object pos_type_name , object use_yn , object sort_order) { string query = @" SELECT POS_ID , POS_NAME , POS_TABLE_NAME , POS_TYPE_NAME , USE_YN , SORT_ORDER FROM EST_POSITION_INFO WHERE ( POS_ID = @POS_ID OR @POS_ID ='' ) AND ( POS_NAME = @POS_NAME OR @POS_NAME ='' ) AND ( POS_TABLE_NAME = @POS_TABLE_NAME OR @POS_TABLE_NAME ='' ) AND ( POS_TYPE_NAME = @POS_TYPE_NAME OR @POS_TYPE_NAME ='' ) AND ( USE_YN = @USE_YN OR @USE_YN ='' ) AND ( SORT_ORDER = @SORT_ORDER OR @SORT_ORDER = 0 ) "; IDbDataParameter[] paramArray = CreateDataParameters(6); paramArray[0] = CreateDataParameter("@POS_ID", SqlDbType.NVarChar); paramArray[0].Value = pos_id; paramArray[1] = CreateDataParameter("@POS_NAME", SqlDbType.NVarChar); paramArray[1].Value = pos_name; paramArray[2] = CreateDataParameter("@POS_TABLE_NAME", SqlDbType.NVarChar); paramArray[2].Value = pos_table_name; paramArray[3] = CreateDataParameter("@POS_TYPE_NAME", SqlDbType.NVarChar); paramArray[3].Value = pos_type_name; paramArray[4] = CreateDataParameter("@USE_YN", SqlDbType.NVarChar); paramArray[4].Value = use_yn; paramArray[5] = CreateDataParameter("@SORT_ORDER", SqlDbType.Int); paramArray[5].Value = sort_order; DataTable dt = DbAgentObj.Fill(query, paramArray).Tables[0]; return(dt); }
/// <summary> /// GetSearchDetail /// : 세부사항 추출쿼리 (app2000에서 사용) /// </summary> /// <param name="asAppRefID"></param> /// <returns></returns> public DataSet GetSearchDetail(string asAppRefID) { IDbDataParameter[] paramArray = null; StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("SELECT A.APP_STEP V_APP_STEP , -- 순번 \n"); sbQuery.Append(" B.EMP_NAME V_EMP_NAME , -- 성명 \n"); sbQuery.Append(" B.POSITION_DUTY_NAME V_POSITION_DUTY_NAME, -- 직책 \n"); sbQuery.Append(" B.DEPT_NAME V_DEPT_NAME , -- 부서명 \n"); sbQuery.Append(" \n"); sbQuery.Append(" CASE A.APP_STATUS WHEN 'P' THEN '대기' \n"); sbQuery.Append(" WHEN 'E' THEN '승인' \n"); sbQuery.Append(" WHEN 'C' THEN '취소' \n"); sbQuery.Append(" END V_APP_STATUS , -- 결재상태 \n"); sbQuery.Append(" A.APP_STATUS V_APP_STATUS_CD , -- 결재상태 코드 \n"); sbQuery.Append(" A.APP_DATE V_APP_DATE , -- 결재일시 \n"); sbQuery.Append(" A.APP_REMARK V_APP_REMARK -- 취소사유 \n"); sbQuery.Append(" FROM COM_APPROVAL_PRC A, \n"); sbQuery.Append(" ( \n"); sbQuery.Append(" SELECT \n"); sbQuery.Append(" A.EMP_REF_ID, \n"); sbQuery.Append(" A.EMP_NAME, \n"); sbQuery.Append(" D.POSITION_DUTY_NAME, \n"); sbQuery.Append(" B.DEPT_NAME \n"); sbQuery.Append(" FROM COM_EMP_INFO A, -- 사용자 테이블 \n"); sbQuery.Append(" COM_DEPT_INFO B, -- 부서 테이블 \n"); sbQuery.Append(" REL_DEPT_EMP C, -- 사용자, 부서코드 연결 테이블 \n"); sbQuery.Append(" COM_POSITION_DUTY D -- 직책테이블 \n"); sbQuery.Append(" WHERE A.EMP_REF_ID = C.EMP_REF_ID \n"); sbQuery.Append(" AND C.DEPT_REF_ID= B.DEPT_REF_ID \n"); sbQuery.Append(" AND D.POSITION_DUTY_CODE = A.POSITION_DUTY_CODE \n"); sbQuery.Append(" ) B \n"); sbQuery.Append(" WHERE A.APP_EMP_ID = B.EMP_REF_ID \n"); sbQuery.Append(" AND A.APP_REF_ID = @APP_REF_ID \n"); sbQuery.Append(" ORDER BY \n"); sbQuery.Append(" APP_STEP \n"); paramArray = CreateDataParameters(1); paramArray[0] = CreateDataParameter("@APP_REF_ID", SqlDbType.VarChar); paramArray[0].Value = asAppRefID; return(DbAgentObj.Fill(sbQuery.ToString(), paramArray)); }
public DataTable SelectEmpProject(object cr_year , object cr_month) { string query = @" SELECT CR_YEAR ,CR_MONTH ,BONBU_NAME ,TEAM_NAME ,GRP_ONE_B_NAME ,GRP_ONE_C_NAME ,PROJECT_NAME ,COUNT(EMP_NAME) AS EMP_COUNT FROM NHIT_DASHBOARD_EMP WHERE CR_YEAR = @CR_YEAR AND CR_MONTH = @CR_MONTH GROUP BY CR_YEAR ,CR_MONTH ,BONBU_NAME ,TEAM_NAME ,GRP_ONE_B_NAME ,GRP_ONE_C_NAME ,PROJECT_NAME ORDER BY CR_YEAR ,CR_MONTH ,BONBU_NAME ,TEAM_NAME ,GRP_ONE_B_NAME ,GRP_ONE_C_NAME ,PROJECT_NAME "; IDbDataParameter[] paramArray = CreateDataParameters(2); paramArray[0] = CreateDataParameter("@CR_YEAR", SqlDbType.NVarChar); paramArray[0].Value = cr_year; paramArray[1] = CreateDataParameter("@CR_MONTH", SqlDbType.NVarChar); paramArray[1].Value = cr_month; return(DbAgentObj.Fill(query, paramArray).Tables[0]); }
/// <summary> /// GetKPIApprovalInfo /// : KPI_REF_ID가 포함된 결재관련 정보 리턴 /// </summary> /// <param name="asKpiRefID"></param> /// <returns></returns> public DataSet GetKPIApprovalInfo(string asKpiRefID, string asAppCode, string asMonthInfo) { IDbDataParameter[] paramArray = null; StringBuilder sbQuery = new StringBuilder(); sbQuery.Append("SELECT \n"); sbQuery.Append(" DBO.FN_GETAPPSTATUS(CONVERT(INT, @KPI_REF_ID), @APP_CODE, @MONTH_INFO) V_APP_STATUS -- 전체결재상태 \n"); sbQuery.Append(" ,(SELECT MAX(CHAMPION_EMP_ID) FROM KPI_INFO WHERE KPI_REF_ID = CONVERT(INT, @KPI_REF_ID)) V_CHAM_EMP_ID -- 챔피언 \n"); sbQuery.Append(" ,COUNT(A.APP_STATUS) V_CNT \n"); sbQuery.Append(" FROM COM_APPROVAL_INFO A, -- 결재정보 \n"); sbQuery.Append(" COM_APPROVAL_PRC B, -- 결재 HISTORY 정보 \n"); sbQuery.Append(" COM_EMP_INFO C, -- 상신자 정보 \n"); sbQuery.Append(" COM_EMP_INFO D, -- 결재자 정보 \n"); sbQuery.Append(" COM_BIZ_INFO E, -- 결재타입 정보 \n"); sbQuery.Append(" ( \n"); sbQuery.Append(" SELECT BIZ_TYPE_CODE, \n"); sbQuery.Append(" MAX(APP_STEP) MAX_APP_STEP \n"); sbQuery.Append(" FROM COM_APPROVAL_LINE \n"); sbQuery.Append(" GROUP BY \n"); sbQuery.Append(" BIZ_TYPE_CODE \n"); sbQuery.Append(" ) F -- 결재선 정보 \n"); sbQuery.Append(" WHERE 1=1 \n"); sbQuery.Append(" AND A.APP_REF_ID = B.APP_REF_ID \n"); sbQuery.Append(" AND A.CUR_APP_STEP = B.APP_STEP \n"); sbQuery.Append(" AND A.REP_EMP_ID = C.EMP_REF_ID \n"); sbQuery.Append(" AND B.APP_EMP_ID = D.EMP_REF_ID \n"); sbQuery.Append(" AND A.APP_CODE = E.BIZ_TYPE_CODE \n"); sbQuery.Append(" AND E.BIZ_TYPE_CODE = F.BIZ_TYPE_CODE \n"); sbQuery.Append(" AND A.EVENT_ID = CONVERT(INT, @KPI_REF_ID) \n"); sbQuery.Append(" AND A.APP_CODE = @APP_CODE \n"); paramArray = CreateDataParameters(3); paramArray[0] = CreateDataParameter("@KPI_REF_ID", SqlDbType.VarChar); paramArray[1] = CreateDataParameter("@APP_CODE", SqlDbType.VarChar); paramArray[2] = CreateDataParameter("@MONTH_INFO", SqlDbType.VarChar); paramArray[0].Value = asKpiRefID; paramArray[1].Value = asAppCode; paramArray[2].Value = asMonthInfo; return(DbAgentObj.Fill(sbQuery.ToString(), paramArray)); }