/** * @(#)file MenuManager.java * @(#)author chj * @(#)version 1.0 * @(#)date 2014-06-24 * @(#)since JDK 1.6 * * Copyright (c) www.udapsoft.co.kr, Inc. */ package kr.co.udapsoft.common.menu; import java.util.Hashtable; import javax.servlet.http.HttpServletRequest; import kr.co.hsnc.common.base.WAFLogger; import kr.co.hsnc.common.logger.Logger; import kr.co.hsnc.common.sql.Row; import kr.co.hsnc.common.sql.RowSet; import kr.co.hsnc.common.sql.RowSetImpl; import kr.co.hsnc.common.sql.WAFConnection; import kr.co.hsnc.common.sql.WAFConnectionImpl; import kr.co.hsnc.common.sql.search.Search; import kr.co.hsnc.common.sql.search.SearchImpl; import kr.co.hsnc.common.sql.util.RowSetUtility; import kr.co.hsnc.common.util.ValueObject; import kr.co.hsnc.j2ee.waf.controller.web.util.WebKeys; public class MenuManager { protected String ACCT_TABLE = "PUB0120T"; protected String R_TABLE = "PUB0340T"; protected String R_LIST_TABLE = "PUB0350T"; protected String PGM_TABLE = "PUB0320T"; protected String PGM_GR_TABLE = "PUB0310T"; protected String PGM_LIST_TABLE = "PUB0300T"; protected String PGM_SYS_TABLE = "PUB0390T"; protected String PIS_CODE1 = "PI1"; protected String PIS_CODE2 = "PI2"; protected String HEI_CODE = "HEI"; protected String HOUS_CODE = "HOS"; protected WAFConnection conn = null; protected HttpServletRequest request = null; protected String ACCT_ID = ""; protected String USE_SYSTEM = ""; protected int SystemSize = 0; protected RowSet SystemRowSet = null; protected int MenuGroupSize = 0; protected RowSet MenuGroupRowSet = null; protected Hashtable SubMenuTable = null; protected String systemMenuScript = "Normal"; protected String m_menu_screen = null; // EP를 통해서 foward 되는 URL public MenuManager(HttpServletRequest request) { m_menu_screen = request.getParameter("menu_screen"); // via EP conn = new WAFConnectionImpl(); this.request = request; this.SubMenuTable = new Hashtable(); ValueObject user = (ValueObject)request.getSession().getAttribute(WebKeys.SIGNED_ON_USER); if( user != null ) { // 협력업체의 경우 계정테이블을 "PUB0220T" 로 교체 if( user.get("ACCT_CAT_CODE").equalsIgnoreCase("CORPORATION") ) { ACCT_TABLE = "PUB0220T"; R_LIST_TABLE = "PUB0220T"; } ACCT_ID = user.get("ACCT_ID"); USE_SYSTEM = user.get("USE_SYSTEM"); if(isValidSystem(user.get("USE_SYSTEM"))){ USE_SYSTEM = user.get("USE_SYSTEM"); } else{ USE_SYSTEM = getValidSystem(); user.set("USE_SYSTEM", USE_SYSTEM); } } // 실행금액 체크 루틴 //if(user.get("USER_TYPE").equals("head")) //this.checkExePayment(user); this.SystemRowSet = getSystemRowSet(); this.SystemSize = this.SystemRowSet == null ? 0 : this.SystemRowSet.size(); if(m_menu_screen == null || m_menu_screen.equals("")) { // 기존소스 if( USE_SYSTEM.equals("") && SystemRowSet != null && SystemSize > 0 ) { this.USE_SYSTEM = getSystemValue(0).get("SYSTEM_ID"); } this.MenuGroupRowSet = getMenuGroupRowSet(); this.MenuGroupSize = this.MenuGroupRowSet.size(); for( int i = 0 ; i < MenuGroupSize ; i++ ) { String pgmGrID = MenuGroupRowSet.getRow(i).get("PGM_GR_ID"); RowSet temp = getSubMenuRowSet(pgmGrID); temp = filterSubMenuRowSet(temp); SubMenuTable.put(Integer.toString(i), temp); } } else { // EP로 인해 추가된 부분 - 2006.10.27 boolean bFound = false; for(int a = 0; a < SystemSize; a++) { this.USE_SYSTEM = getSystemValue(a).get("SYSTEM_ID"); this.MenuGroupRowSet = getMenuGroupRowSet(); this.MenuGroupSize = this.MenuGroupRowSet.size(); for( int i = 0 ; i < MenuGroupSize ; i++ ) { String pgmGrID = MenuGroupRowSet.getRow(i).get("PGM_GR_ID"); RowSet temp = getSubMenuRowSet(pgmGrID); temp = filterSubMenuRowSet(temp); for(int j = 0; j < temp.size(); j++) { if(temp.getRow(j).get("SCREEN_NAME").equals(m_menu_screen)) { bFound = true; break; } } SubMenuTable.put(Integer.toString(i), temp); } if(bFound) break; } if(! bFound) this.USE_SYSTEM = getSystemValue(0).get("SYSTEM_ID"); } } public String getValidSystem(){ Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); String sqlstr = ""; try { sqlstr = "SELECT DISTINCT MIN(E.SYSTEM_ID) \n" + " FROM " + ACCT_TABLE + " A, \n" + " " + R_LIST_TABLE + " B, \n" + " PUB0340T C, \n" + " PUB0300T D, \n" + " PUB0320T E \n" + " WHERE A.ACCT_ID = B.ACCT_ID \n" + " AND B.RIGHT_ID = C.RIGHT_ID \n" + " AND C.RIGHT_ID = D.RIGHT_ID \n" + " AND D.PGM_ID = E.PGM_ID \n" + " AND A.ACCT_ID = " + this.ACCT_ID + " \n"; search.setStatement(sqlstr); rowSet = search.execute(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getSystemRowSet() : " + e.getMessage()); WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); } finally { if( rowSet == null ) rowSet = new RowSetImpl(); } return rowSet.getRow(0).get(0); } public boolean isValidSystem(String USE_SYSTEM){ Search search = new SearchImpl(); RowSet rowSet = null; String sqlstr = ""; boolean returnVal = false; try { sqlstr = " SELECT COUNT(SYSTEM_ID) CNT \n" + " FROM PUB0390T \n" + " WHERE SYSTEM_ID = '"+USE_SYSTEM+"' \n" + " AND SYSTEM_CODE <> 'HEI' AND SYSTEM_CODE <> 'PI1' AND SYSTEM_CODE <> 'PI2' AND SYSTEM_CODE <> '"+HOUS_CODE+"' \n"; search.setStatement(sqlstr); rowSet = search.execute(); //2007.4.30 쿼리값이 없을때 에러나는 부분 수정 if(rowSet.size()>0 && rowSet!=null){ if(rowSet.getRow(0).getInt("CNT")>0) returnVal= true; } } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getSystemRowSet() : " + e.getMessage()); WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); } finally { if( rowSet == null ) rowSet = new RowSetImpl(); } return returnVal; } public String getSystemMenuScript(){ return this.systemMenuScript; } public String getUseSystem() { return this.USE_SYSTEM; } public int getSystemSize() { return this.SystemSize; } public ValueObject getSystemValue(int index) { return RowSetUtility.getValueObject(SystemRowSet.getRow(index)); } public int getMenuGroupSize() { return this.MenuGroupSize; } public ValueObject getMenuGroupValue(int index) { return RowSetUtility.getValueObject(MenuGroupRowSet.getRow(index)); } public int getSubMenuRowSetSize(int index) { RowSet rowSet = ((RowSet)SubMenuTable.get(Integer.toString(index))); return rowSet.size(); } public ValueObject getSubMenuRowValue(int index, int sub_index) { return RowSetUtility.getValueObject(((RowSet)SubMenuTable.get(Integer.toString(index))).getRow(sub_index)); } public String getMenuGroupOnImages() { String temp = ""; for( int i = 0 ; i < MenuGroupSize ; i++ ) { temp += "'" + MenuGroupRowSet.getRow(i).get("ON_IMAGE_PATH") + "' "; if( (i + 1) != MenuGroupSize ) temp += ", "; } return temp; } public String getMenuGroupOffImages() { String temp = ""; for( int i = 0 ; i < MenuGroupSize ; i++ ) { temp += "'" + MenuGroupRowSet.getRow(i).get("OFF_IMAGE_PATH") + "' "; if( (i + 1) != MenuGroupSize ) temp += ", "; } return temp; } public int getSubMenuWidth(int menuGroupIndex) { int maxLength = 0; int defaultWidth = 130; int subRowSetSize = getSubMenuRowSetSize(menuGroupIndex); for( int j = 0 ; j < subRowSetSize ; j++ ) { String pgmName = getSubMenuRowValue(menuGroupIndex, j).get("PGM_NAME"); byte[] bytePgmName = pgmName.getBytes(); if( bytePgmName.length > maxLength ) maxLength = bytePgmName.length; } // 기본크기는 130 // 18 byte 이상이면 1byte당 6px씩 증가 // 8 byte 이상이면 1byte당 8px씩 증가로 수정 2003.03.19 if( maxLength > 18 ) { defaultWidth += (maxLength - 18) * 6; } return defaultWidth; } /* * 모든 메뉴보기추가 */ public ValueObject getMenuTitle(){ RowSet rs = setMenuTitle(); return RowSetUtility.getValueObject(rs.getRow(0)); } public ValueObject[] getMenuData(int i){ RowSet rs = setMenuData(); ValueObject v[] = null; v = new ValueObject[rs.size()]; for(int j = 0; j getSystemRowSet() : " + e.getMessage()); WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } /** * 프로그램 그룹 구성 데이터 조회 * @return */ private RowSet getMenuGroupRowSet() { Search search = new SearchImpl(); RowSet rowSet = null; String sqlstr = ""; try { sqlstr = " SELECT PGM_GR_SEQ, SYSTEM_ID, B.* \n" + " FROM ( SELECT D.PGM_GR_ID, MIN(PGM_GR_SEQ) AS PGM_GR_SEQ , E.SYSTEM_ID \n" + " FROM " + ACCT_TABLE + " A, \n" + " " + R_LIST_TABLE + " B, \n" + " " + R_TABLE + " C, \n" + " " + PGM_LIST_TABLE + " D, \n" + " " + PGM_TABLE + " E \n" + " WHERE A.ACCT_ID = B.ACCT_ID \n" + " AND B.RIGHT_ID = C.RIGHT_ID \n" + " AND C.RIGHT_ID = D.RIGHT_ID \n" + " AND D.PGM_ID = E.PGM_ID \n" // + " AND E.SYSTEM_ID IN ( ? , decode( ? , 0, ? ) ) \n" //외주,구매,공동도급 시스템일때만 전자결재 서브메뉴를 띄운다 + " AND A.ACCT_ID = ? \n" + " GROUP BY PGM_GR_ID, E.SYSTEM_ID ) A, \n" + " " + PGM_GR_TABLE + " B \n" + " WHERE A.PGM_GR_ID = B.PGM_GR_ID \n" + " ORDER BY SYSTEM_ID, PGM_GR_SEQ \n" ; search.setStatement(sqlstr); // search.addParameter(USE_SYSTEM); // search.addParameter(USE_SYSTEM); // search.addParameter(USE_SYSTEM); search.addParameter(ACCT_ID); rowSet = search.execute(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getMenuGroupRowSet() : " + e.getMessage()); WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } private RowSet getSubMenuRowSet(String pgmGrID) { Search search = new SearchImpl(); RowSet rowSet = null; String sqlstr = ""; try { sqlstr = " SELECT D.GUBUN, E.* \n" + " FROM " + ACCT_TABLE + " A, \n" + " " + R_LIST_TABLE + " B, \n" + " " + R_TABLE + " C, \n" + " " + PGM_LIST_TABLE + " D, \n" + " " + PGM_TABLE + " E \n" + " WHERE A.ACCT_ID = B.ACCT_ID \n" + " AND B.RIGHT_ID = C.RIGHT_ID \n" + " AND C.RIGHT_ID = D.RIGHT_ID \n" + " AND D.PGM_ID = E.PGM_ID(+) \n" + " AND A.ACCT_ID = ? \n" + " AND D.PGM_GR_ID = ? \n"; // + " AND ( E.SYSTEM_ID IN ( ? ) OR D.GUBUN = 'L' ) \n"; sqlstr += " ORDER BY B.RIGHT_ID, D.PGM_GR_SEQ, D.PGM_SEQ \n" ; search.setStatement(sqlstr); search.addParameter(ACCT_ID); search.addParameter(pgmGrID); // search.addParameter(USE_SYSTEM); rowSet = search.execute(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getSubMenuRowSet() : " + e.getMessage()); WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } /** * 서브메뉴의 중복된 프로그램 필터링 * @param temp * @return */ private RowSet filterSubMenuRowSet(RowSet temp) { int size = temp.size(); boolean isLined = false; ValueObject filterInfo = new ValueObject(); RowSet rtnRowSet = new RowSetImpl(); for( int i = 0 ; i < size ; i++ ) { Row row = temp.getRow(i); if( row.get("GUBUN").equals("L") ) { if( !isLined ) { rtnRowSet.addRow(row); } isLined = true; } else { if( filterInfo.get(row.get("PGM_ID")).equals("") ) { rtnRowSet.addRow(row); filterInfo.set(row.get("PGM_ID"), "EXIST"); isLined = false; } } } return rtnRowSet; } /** * 프로그램명 리턴 * @param screenName * @return */ public static String getPgmName(String screenName) { Search search = new SearchImpl(); String sqlstr = ""; try { sqlstr = " SELECT PGM_NAME \n" + " FROM PUB0320T \n" + " WHERE SCREEN_NAME = ? \n" ; search.setStatement(sqlstr); search.addParameter(screenName); RowSet rowSet = search.execute(); if( rowSet != null && rowSet.size() > 0 ) { return rowSet.getRow(0).get("PGM_NAME"); } return ""; } catch(Exception e) { WAFLogger.error("MenuManager --> getPgmName() : " + e.getMessage()); WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(search.getParameters()); WAFLogger.error(e); return ""; } } /** * 메뉴 타이틀명 * @param screenName * @return */ private RowSet setMenuTitle() { Search search = new SearchImpl(); RowSet rowSet = null; String sqlstr = ""; try { sqlstr+= " SELECT MAX(DECODE(RN, 1, NAME||':'||SYSTEM_ID)) MENU1, \n"; sqlstr+= " MAX(DECODE(RN, 2, NAME||':'||SYSTEM_ID)) MENU2, \n"; sqlstr+= " MAX(DECODE(RN, 3, NAME||':'||SYSTEM_ID)) MENU3, \n"; sqlstr+= " MAX(DECODE(RN, 4, NAME||':'||SYSTEM_ID)) MENU4, \n"; sqlstr+= " MAX(DECODE(RN, 5, NAME||':'||SYSTEM_ID)) MENU5, \n"; sqlstr+= " MAX(DECODE(RN, 6, NAME||':'||SYSTEM_ID)) MENU6 \n"; sqlstr+= " FROM \n"; sqlstr+= " ( \n"; sqlstr+= " SELECT SYSTEM_ID,NAME, ROWNUM RN FROM PUB0390T \n"; sqlstr+= " --WHERE SYSTEM_CODE <> 'PMC' \n"; sqlstr+= " ORDER BY SYSTEM_ID \n"; sqlstr+= " ) \n"; search.setStatement(sqlstr); rowSet = search.execute(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getMenuTitle() : " + e.getMessage()); WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } /******************** * 모든 메뉴보기 추가 ********************/ private RowSet setMenuData() { Search search = new SearchImpl(); RowSet rowSet = null; String sqlstr = ""; try { sqlstr+= " SELECT DECODE(A.RK, '1', 'G') AS GB, A.*, B.PGM_ID PGM_ID2 \n"; sqlstr+= " FROM ( SELECT T2.PGM_GR_NAME, \n"; sqlstr+= " T1.PGM_NAME, \n"; sqlstr+= " T1.SCREEN_NAME, \n"; sqlstr+= " T1.SYS_ID, \n"; sqlstr+= " T1.NAME, \n"; sqlstr+= " RANK () OVER (PARTITION BY T1.SYS_ID, NAME, PGM_GR_NAME ORDER BY T1.PGM_GR_ID, T1.PGM_SEQ) RK, \n"; sqlstr+= " T1.PGM_ID \n"; sqlstr+= " FROM (SELECT D.PGM_GR_SEQ, \n"; sqlstr+= " D.PGM_SEQ, \n"; sqlstr+= " D.PGM_GR_ID, \n"; sqlstr+= " C.RIGHT_ID, \n"; sqlstr+= " F.NAME, \n"; sqlstr+= " F.SYSTEM_ID AS SYS_ID, \n"; sqlstr+= " E.* \n"; sqlstr+= " FROM PUB0340T C, -- '공통)권한' \n"; sqlstr+= " PUB0300T D, -- '공통)프로그램 목록' \n"; sqlstr+= " PUB0320T E, -- '공통)프로그램(화면정보)' \n"; sqlstr+= " PUB0390T F \n"; sqlstr+= " WHERE F.SYSTEM_ID = E.SYSTEM_ID \n"; sqlstr+= " -- AND F.SYSTEM_CODE <> 'PMC' \n"; sqlstr+= " AND C.RIGHT_ID = D.RIGHT_ID \n"; sqlstr+= " AND D.PGM_ID = E.PGM_ID(+) \n"; sqlstr+= " AND C.RIGHT_ID = '140' \n"; sqlstr+= " AND D.GUBUN = 'P') T1, \n"; sqlstr+= " PUB0310T T2 \n"; sqlstr+= " WHERE T1.PGM_GR_ID = T2.PGM_GR_ID \n"; sqlstr+= " ORDER BY T1.SYS_ID, T1.PGM_GR_SEQ, T1.PGM_GR_ID, T1.PGM_SEQ) A, \n"; sqlstr+= " (SELECT T3.PGM_ID \n"; sqlstr+= " FROM PUB0350T T1, \n"; sqlstr+= " PUB0300T T2, \n"; sqlstr+= " PUB0320T T3 \n"; sqlstr+= " WHERE T1.ACCT_ID = '" + ACCT_ID + "' \n"; sqlstr+= " AND T1.RIGHT_ID = T2.RIGHT_ID \n"; sqlstr+= " AND T3.PGM_ID = T2.PGM_ID \n"; sqlstr+= " -- AND T3.SYSTEM_CODE <> 'PMC' \n"; sqlstr+= " ) B \n"; sqlstr+= " WHERE A.PGM_ID = B.PGM_ID(+) \n"; search.setStatement(sqlstr); //search.addParameter(""); rowSet = search.execute(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getMenuData() : " + e.getMessage()); WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } /*mymenu 타이틀*/ public RowSet getMyMenuTitle(String user) { Search search = new SearchImpl(); RowSet rowSet = null; String sqlstr = ""; try { sqlstr += " SELECT D.SYSTEM_CODE \n" + " , D.NAME \n" + " , D.SYSTEM_ID \n" + " , D.SEQ \n" + " FROM PUB0300T A , \n" + " PUB0320T B , \n" + " PUB0310T C , \n" + " PUB0390T D , \n" + " PUB0350T AUTH, \n" + " MYMENU MM \n" + " WHERE A.PGM_ID = B.PGM_ID \n" + " AND A.PGM_GR_ID = C.PGM_GR_ID \n" + " AND B.SYSTEM_ID = D.SYSTEM_ID \n" + " AND A.PGM_ID = MM.PGM_ID \n" + " AND A.RIGHT_ID = AUTH.RIGHT_ID \n" + " AND AUTH.ACCT_ID = MM.ACCT_ID \n" + " AND MM.ACCT_ID = ? \n" + " GROUP BY D.SYSTEM_CODE \n" + " , D.NAME \n" + " , D.SYSTEM_ID \n" + " , D.SEQ \n" + " ORDER BY D.SEQ \n" ; search.setStatement(sqlstr); search.addParameter(user); rowSet = search.execute(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getMenuData() : " + e.getMessage()); WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } /* mymanu 메뉴 불러오기 * */ public RowSet getMyMenuData(String user) { Search search = new SearchImpl(); RowSet rowSet = null; String sqlstr = ""; try { sqlstr += " SELECT A.PGM_ID \n" + " , B.SYSTEM_CODE \n" + " , D.NAME \n" + " , C.PGM_GR_NAME \n" + " , B.PGM_NAME \n" + " , B.SCREEN_NAME \n" + " FROM PUB0300T A , \n" + " PUB0320T B , \n" + " PUB0310T C , \n" + " PUB0390T D , \n" + " PUB0350T AUTH, \n" + " MYMENU MM \n" + " WHERE A.PGM_ID = B.PGM_ID(+) \n" + " AND A.PGM_GR_ID = C.PGM_GR_ID \n" + " AND B.SYSTEM_ID = D.SYSTEM_ID(+) \n" + " AND A.PGM_ID = MM.PGM_ID \n" + " AND A.RIGHT_ID = AUTH.RIGHT_ID \n" + " AND AUTH.ACCT_ID = MM.ACCT_ID \n" + " AND MM.ACCT_ID = ? \n" + " ORDER BY D.SEQ, A.PGM_GR_SEQ, A.PGM_SEQ \n" ; search.setStatement(sqlstr); search.addParameter(user); rowSet = search.execute(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getMenuData() : " + e.getMessage()); WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } /** * DB Instance Name 리턴 * @return */ public String getDBInstanceName() { Search search = new SearchImpl(); String sqlstr = ""; try { sqlstr = " SELECT INSTANCE_NAME FROM V$INSTANCE \n" ; search.setStatement(sqlstr); RowSet rowSet = search.execute(); if( rowSet != null && rowSet.size() > 0 ) { return rowSet.getRow(0).get("INSTANCE_NAME"); } return "InstanceName Query Error(NoRow)"; } catch(Exception e) { Logger.err.println("MenuManager --> getDBInstanceName() : " + e.getMessage()); Logger.err.println("SQL : " + search.getStatement()); Logger.err.println(search.getParameters()); Logger.err.println(e); return "InstanceName Query Error(Exception)"; } } }