package kr.co.kihyun.beans.entity; import java.sql.ResultSet; import java.sql.SQLException; //import java.io.ByteArrayOutputStream; //import java.io.ByteArrayInputStream; //import java.util.Arrays; // //import java.sql.Blob; // //import kr.co.kihyun.beans.entity.MDept; //import kr.co.kihyun.beans.entity.MUser; //import kr.co.kihyun.beans.entity.MoumiEntity; //import kr.co.kihyun.beans.entity.SysAuth; //import kr.co.kihyun.beans.entity.TotDoc; //import kr.co.kihyun.beans.entity.TotReport; //import kr.co.kihyun.beans.entity.TotReportProcess; //import kr.co.kihyun.beans.entity.TotReportType; // //import java.util.ArrayList; //import java.util.Calendar; //import java.util.Collections; import java.util.Date; //import java.util.HashMap; //import java.util.HashSet; //import java.util.List; //import java.util.Map; //import java.util.Set; //import javax.jdo.JDOObjectNotFoundException; //import javax.jdo.PersistenceManager; //import javax.jdo.Query; //import javax.jdo.annotations.IdentityType; //import javax.jdo.annotations.NullValue; //import javax.jdo.annotations.PersistenceCapable; //import javax.jdo.annotations.Persistent; //import kr.co.kihyun.beans.entity.ecross.IRemotePersistable; //import kr.co.kihyun.beans.entity.util.QueryImpl; //import kr.co.kihyun.io.IUploadable; //import kr.co.kihyun.lang.MString; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import kr.co.kihyun.db.DBManager; //import java.io.File; //import kr.co.kihyun.io.FileUtil; //import kr.co.kihyun.moumi.MoumiConfig; //import java.net.URLDecoder; public class MUser2 extends DBManager { private static final Logger LOG = LoggerFactory.getLogger(MUser2.class); private ResultSet rs = null; private int count = 0; private String[] deptId = null; private int[] id = null; private String[] execCode = null; private String[] name = null; private String[] ownerName = null; private Date[] startDate = null; private Date[] endDate = null; private int[] repId = null; private int tCount; private int[] sendCount; private int[] allCount; private String userNmEncpt=null; private String[] userId = null; private int[] prss = null; private int[] moumiacptLimitstrategy = null; private int[] noteCount = null; private String grade=null; private String downDocId=null; private String[] deptName=null; private String[] shareDocRegId=null; //2014.08.29 Add by KWON,HAN : 집계자료의 등록자 ID private int[] shareUserCount=null; //2014.08.29 Add by KWON,HAN : 집계자료의 사용자 공유수 private String[] shareUserId= null; //2014.08.29 Add by KWON,HAN : 집계자료의 공유자 ID /******** tmpName 구해오기 ********/ public void getGradeFlag(String dpID) { StringBuilder sql = new StringBuilder(250); try { sql.append("SELECT GRADE FROM MOUMI_DEPT WHERE ID = '"+dpID+"'"); rs = execQuery(sql); if (rs != null) { rs.last(); count = rs.getRow(); rs.beforeFirst(); } else { return; } grade = new String(); for (int i = 0; rs.next(); i++) { grade = rs.getString(1); } //System.out.println(sql); } catch (SQLException sqlEx) { LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}", new Object[] { sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage()}); throw new RuntimeException(sqlEx); } catch (Exception ex) { throw new RuntimeException(ex); } finally { try { if (rs != null) rs.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } try { if (pstmt != null) pstmt.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } try { if (con != null) con.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } } } /******** 카테고리 전체 리스트 ********/ public void getPrssRecord(String usID,String dpID,String findOption,Object filterValue,int sysAuth,String getGradeFlag) { StringBuilder sql = new StringBuilder(250); try { //System.out.println("넘어온 GRADE:::::"+getGradeFlag); String tmpAppend; if(sysAuth == 9){ tmpAppend = ""; }else if(sysAuth == 7 && getGradeFlag == null){ //tmpAppend = " AND DC.USER_ID = '"+usID+"'"; tmpAppend = "AND ((DC.USER_ID = '"+usID+"' AND DC.ACC_AUTH IN (0,1,2)) OR (DC.DEPT_ID = '"+dpID+"' AND DC.ACC_AUTH IN (1,2)) OR " + "(DC.ACC_AUTH IN (0,1,2) AND GR.ID = (SELECT GR.id " + "FROM MOUMI_MUSER US, MOUMI_DEPT DT, MOUMI_DEPT GR " + "WHERE US.DEPT_ID = DT.ID AND DT.ORGAN = GR.ID AND US.ID='"+usID+"')) \n" + " OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) ) \n" //2014.08.29 Update by KWON,HAN + " OR DC.ACC_AUTH = 3 ) \n"; }else if(sysAuth == 2 && getGradeFlag == null){ //tmpAppend = " AND DC.USER_ID = '"+usID+"'"; tmpAppend = "AND ((DC.USER_ID = '"+usID+"' AND DC.ACC_AUTH IN (0,1,2)) OR (DC.DEPT_ID = '"+dpID+"' AND DC.ACC_AUTH IN (1,2)) OR " + "(DC.ACC_AUTH IN (2) AND GR.ID = (SELECT GR.id " + "FROM MOUMI_MUSER US, MOUMI_DEPT DT, MOUMI_DEPT GR " + "WHERE US.DEPT_ID = DT.ID AND DT.ORGAN = GR.ID AND US.ID='"+usID+"')) \n" + " OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) ) \n" //2014.08.29 Update by KWON,HAN + " OR DC.ACC_AUTH = 3 ) \n"; }else{ //tmpAppend = " AND DC.USER_ID = '"+usID+"'"; tmpAppend = "AND ((DC.USER_ID = '"+usID+"' AND DC.ACC_AUTH IN (0,1,2)) OR (DC.DEPT_ID = '"+dpID+"' AND DC.ACC_AUTH IN (1,2)) \n" + " OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) ) \n" //2014.08.29 Update by KWON,HAN + ") \n"; } if(filterValue != null && findOption == null){ sql.append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.START_DATE <= CURRENT DATE \n") .append(" AND DC.END_DATE >= CURRENT DATE \n") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) \n") .append(" ORDER BY DC.ID DESC \n"); }else if(filterValue != null && findOption.equals("name")){ sql.append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.START_DATE <= CURRENT DATE \n") .append(" AND DC.END_DATE >= CURRENT DATE \n") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND DC.NAME LIKE '%"+filterValue+"%' \n") .append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) \n") .append(" ORDER BY DC.ID DESC \n"); }else if(filterValue != null && findOption.equals("executionCode")){ sql.append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.START_DATE <= CURRENT DATE \n") .append(" AND DC.END_DATE >= CURRENT DATE \n") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND DC.EXEC_CODE LIKE '%"+filterValue+"%' \n") .append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) \n") .append(" ORDER BY DC.ID DESC \n"); }else if(filterValue != null && findOption.equals("totDoc.mUser.name")){ sql.append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.START_DATE <= CURRENT DATE \n") .append(" AND DC.END_DATE >= CURRENT DATE \n") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") //.append(" AND SP_CM_DEC_FU(DC.OWNER_NAME) LIKE '%"+filterValue+"%' \n") .append(" and dc.owner_name = sp_cm_enc_fu('"+filterValue+"') \n") .append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) \n") .append(" ORDER BY DC.ID DESC \n"); }else{ sql.append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.START_DATE <= CURRENT DATE \n") .append(" AND DC.END_DATE >= CURRENT DATE \n") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) \n") .append(" ORDER BY DC.ID DESC \n"); } //System.out.println("=== MUser2.getPrssRecord =================================="); //System.out.println("sql = \n"+sql); //System.out.println("==========================================================="); rs = execQuery(sql); if (rs != null) { rs.last(); count = rs.getRow(); rs.beforeFirst(); } else { return; } userId = new String[count]; deptId = new String[count]; id = new int[count]; execCode = new String[count]; name = new String[count]; ownerName = new String[count]; startDate = new Date[count]; endDate = new Date[count]; repId = new int[count]; prss = new int[count]; moumiacptLimitstrategy = new int[count]; allCount = new int[count]; sendCount = new int[count]; noteCount = new int[count]; deptName = new String[count]; tCount = count; shareDocRegId = new String[count]; //2014.08.29 Add by KWON,HAN : 집계자료의 등록자 ID shareUserCount = new int[count]; //2014.08.29 Add by KWON,HAN : 집계자료의 사용자 공유수 shareUserId = new String[count]; //2014.08.29 Add by KWON,HAN : 집계자료의 공유자 ID for (int i = 0; rs.next(); i++) { userId[i] = rs.getString(1); deptId[i] = rs.getString(2); id[i] = rs.getInt(3); execCode[i] = rs.getString(4); name[i] = rs.getString(5); ownerName[i] = rs.getString(6); startDate[i] = rs.getDate(7); endDate[i] = rs.getDate(8); repId[i] = rs.getInt(9); prss[i] = rs.getInt(10); moumiacptLimitstrategy[i] = rs.getInt(11); allCount[i] = rs.getInt(12); sendCount[i] = rs.getInt(13); noteCount[i] = rs.getInt(14); deptName[i] = rs.getString(15); shareDocRegId[i] = rs.getString(16); //2014.08.29 Add by KWON,HAN : 집계자료의 등록자 ID shareUserCount[i] = rs.getInt(17); //2014.08.29 Add by KWON,HAN : 집계자료의 사용자 공유수 shareUserId[i] = rs.getString(18); //2014.08.29 Add by KWON,HAN : 집계자료의 공유자 ID } //System.out.println("MUser2.getPrssRecord ======================================"); //System.out.println("sql = \n" + sql); //System.out.println("==========================================================="); } catch (SQLException sqlEx) { LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}", new Object[] { sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage()}); throw new RuntimeException(sqlEx); } catch (Exception ex) { throw new RuntimeException(ex); } finally { try { if (con != null) con.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } try { if (pstmt != null) pstmt.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } try { if (rs != null) rs.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } } } /******** 카테고리 전체 리스트 ********/ public void getRegRecord(String usID,String dpID,String findOption,Object filterValue,int sysAuth,String getGradeFlag) { StringBuilder sql = new StringBuilder(250); try { //System.out.println("넘어온 GRADE:::::"+getGradeFlag); String tmpAppend; if(sysAuth == 9){ tmpAppend = ""; }else if(sysAuth == 7 && getGradeFlag == null){ //tmpAppend = " AND DC.USER_ID = '"+usID+"'"; tmpAppend = "AND ((DC.USER_ID = '"+usID+"' AND DC.ACC_AUTH IN (0,1,2)) OR (DC.DEPT_ID = '"+dpID+"' AND DC.ACC_AUTH IN (1,2)) OR " + "(DC.ACC_AUTH IN (0,1,2) AND GR.ID = (SELECT GR.id " + "FROM MOUMI_MUSER US, MOUMI_DEPT DT, MOUMI_DEPT GR " + "WHERE US.DEPT_ID = DT.ID AND DT.ORGAN = GR.ID AND US.ID='"+usID+"')) OR DC.ACC_AUTH = 3 )"; }else if(sysAuth == 2 && getGradeFlag == null){ //tmpAppend = " AND DC.USER_ID = '"+usID+"'"; tmpAppend = "AND ((DC.USER_ID = '"+usID+"' AND DC.ACC_AUTH IN (0,1,2)) OR (DC.DEPT_ID = '"+dpID+"' AND DC.ACC_AUTH IN (1,2)) OR " + "(DC.ACC_AUTH IN (2) AND GR.ID = (SELECT GR.id " + "FROM MOUMI_MUSER US, MOUMI_DEPT DT, MOUMI_DEPT GR " + "WHERE US.DEPT_ID = DT.ID AND DT.ORGAN = GR.ID AND US.ID='"+usID+"')) OR DC.ACC_AUTH = 3 )"; }else{ //tmpAppend = " AND DC.USER_ID = '"+usID+"'"; tmpAppend = "AND ((DC.USER_ID = '"+usID+"' AND DC.ACC_AUTH IN (0,1,2)) OR (DC.DEPT_ID = '"+dpID+"' AND DC.ACC_AUTH IN (1,2)))"; } if(filterValue != null && findOption == null){ sql.append("SELECT * FROM \n") .append("( \n") .append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = ac.ID \n") .append(" AND AC.PRSS = 1 \n") .append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) \n") .append(" UNION ALL \n") .append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.START_DATE >= CURRENT DATE \n") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) \n") .append(" ) A ORDER BY A.ID DESC \n"); }else if(filterValue != null && findOption.equals("name")){ sql.append("SELECT * FROM \n") .append("( \n") .append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND AC.PRSS = 1 \n") .append(" AND DC.NAME LIKE '%"+filterValue+"%' \n") .append("AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) \n") .append(" UNION ALL \n") .append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.START_DATE >= CURRENT DATE \n") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND DC.NAME LIKE '%"+filterValue+"%' \n") .append("AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) \n") .append(" ) A ORDER BY A.ID DESC \n"); }else if(filterValue != null && findOption.equals("executionCode")){ sql.append("SELECT * FROM \n") .append("( \n") .append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = ac.ID \n") .append(" AND ac.PRSS = 1 \n") .append(" AND DC.EXEC_CODE LIKE '%"+filterValue+"%' \n") .append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) \n") .append(" UNION ALL \n") .append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.START_DATE >= CURRENT DATE \n") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND DC.EXEC_CODE LIKE '%"+filterValue+"%' \n") .append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) \n") .append(" ) A ORDER BY A.ID DESC \n"); }else if(filterValue != null && findOption.equals("totDoc.mUser.name")){ sql.append("SELECT * FROM \n") .append("( \n") .append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND AC.PRSS = 1 \n") .append(" and dc.owner_name = sp_cm_enc_fu('"+filterValue+"') \n") .append("AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) \n") .append(" UNION ALL \n") .append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.START_DATE >= CURRENT DATE \n") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" and dc.owner_name = sp_cm_enc_fu('"+filterValue+"') \n") .append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) \n") .append(" ) A ORDER BY A.ID DESC \n"); }else{ sql.append("SELECT * FROM \n") .append("( \n") .append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND AC.PRSS = 1 \n") .append("AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) \n") .append(" UNION ALL \n") .append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.START_DATE >= CURRENT DATE \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND DC.TOT_RANGE = 1 \n") .append("AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) \n") .append(" ) A ORDER BY A.ID DESC \n"); } rs = execQuery(sql); if (rs != null) { rs.last(); count = rs.getRow(); rs.beforeFirst(); } else { return; } userId = new String[count]; deptId = new String[count]; id = new int[count]; execCode = new String[count]; name = new String[count]; ownerName = new String[count]; startDate = new Date[count]; endDate = new Date[count]; repId = new int[count]; prss = new int[count]; moumiacptLimitstrategy = new int[count]; allCount = new int[count]; sendCount = new int[count]; noteCount = new int[count]; deptName = new String[count]; tCount = count; shareDocRegId = new String[count]; //2014.08.29 Add by KWON,HAN : 집계자료의 등록자 ID shareUserCount = new int[count]; //2014.08.29 Add by KWON,HAN : 집계자료의 사용자 공유수 shareUserId = new String[count]; //2014.08.29 Add by KWON,HAN : 집계자료의 공유자 ID for (int i = 0; rs.next(); i++) { userId[i] = rs.getString(1); deptId[i] = rs.getString(2); id[i] = rs.getInt(3); execCode[i] = rs.getString(4); name[i] = rs.getString(5); ownerName[i] = rs.getString(6); startDate[i] = rs.getDate(7); endDate[i] = rs.getDate(8); repId[i] = rs.getInt(9); prss[i] = rs.getInt(10); moumiacptLimitstrategy[i] = rs.getInt(11); allCount[i] = rs.getInt(12); sendCount[i] = rs.getInt(13); noteCount[i] = rs.getInt(14); deptName[i] = rs.getString(15); shareDocRegId[i] = rs.getString(16); //2014.08.29 Add by KWON,HAN : 집계자료의 등록자 ID shareUserCount[i] = rs.getInt(17); //2014.08.29 Add by KWON,HAN : 집계자료의 사용자 공유수 shareUserId[i] = rs.getString(18); //2014.08.29 Add by KWON,HAN : 집계자료의 공유자 ID } //System.out.println("MUser2.getRegRecord ======================================"); //System.out.println("sql = \n" + sql); //System.out.println("==========================================================="); } catch (SQLException sqlEx) { LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}", new Object[] { sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage()}); throw new RuntimeException(sqlEx); } catch (Exception ex) { throw new RuntimeException(ex); } finally { try { if (con != null) con.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } try { if (pstmt != null) pstmt.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } try { if (rs != null) rs.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } } } public void getEndRecord(String usID,String dpID,String findOption,Object filterValue,int sysAuth,String getGradeFlag) { StringBuilder sql = new StringBuilder(250); try { //System.out.println("넘어온 GRADE:::::"+getGradeFlag); String tmpAppend; if(sysAuth == 9){ tmpAppend = ""; }else if(sysAuth == 7 && getGradeFlag == null){ //tmpAppend = " AND DC.USER_ID = '"+usID+"'"; tmpAppend = "AND ((DC.USER_ID = '"+usID+"' AND DC.ACC_AUTH IN (0,1,2)) OR (DC.DEPT_ID = '"+dpID+"' AND DC.ACC_AUTH IN (1,2)) OR " + "(DC.ACC_AUTH IN (0,1,2) AND GR.ID = (SELECT GR.id " + "FROM MOUMI_MUSER US, MOUMI_DEPT DT, MOUMI_DEPT GR " + "WHERE US.DEPT_ID = DT.ID AND DT.ORGAN = GR.ID AND US.ID='"+usID+"')) \n" + " OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) ) \n" //2014.08.29 Update by KWON,HAN + " OR DC.ACC_AUTH = 3 ) \n"; }else if(sysAuth == 2 && getGradeFlag == null){ //tmpAppend = " AND DC.USER_ID = '"+usID+"'"; tmpAppend = "AND ((DC.USER_ID = '"+usID+"' AND DC.ACC_AUTH IN (0,1,2)) OR (DC.DEPT_ID = '"+dpID+"' AND DC.ACC_AUTH IN (1,2)) OR " + "(DC.ACC_AUTH IN (2) AND GR.ID = (SELECT GR.id " + "FROM MOUMI_MUSER US, MOUMI_DEPT DT, MOUMI_DEPT GR " + "WHERE US.DEPT_ID = DT.ID AND DT.ORGAN = GR.ID AND US.ID='"+usID+"')) \n" + " OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) ) \n" //2014.08.29 Update by KWON,HAN + " OR DC.ACC_AUTH = 3 ) \n"; }else{ //tmpAppend = " AND DC.USER_ID = '"+usID+"'"; tmpAppend = "AND ((DC.USER_ID = '"+usID+"' AND DC.ACC_AUTH IN (0,1,2)) OR (DC.DEPT_ID = '"+dpID+"' AND DC.ACC_AUTH IN (1,2)) \n" + " OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) ) \n" //2014.08.29 Update by KWON,HAN + ") \n"; } if(filterValue != null && findOption == null){ sql.append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND AC.PRSS IN (4,2) \n") .append(" AND (DC.END_DATE <= CURRENT DATE OR AC.SUBMIT_LIMIT_CNT > 0 AND AC.SUBMIT_LIMIT_CNT <= FN_SNDCNT(DC.ID)) \n") .append(" ORDER BY DC.ID DESC \n"); }else if(filterValue != null && findOption.equals("name")){ sql.append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND AC.PRSS IN (4,2) \n") .append(" AND DC.NAME LIKE '%"+filterValue+"%' \n") .append(" AND (DC.END_DATE <= CURRENT DATE OR AC.SUBMIT_LIMIT_CNT > 0 AND AC.SUBMIT_LIMIT_CNT <= FN_SNDCNT(DC.ID)) \n") .append(" ORDER BY DC.ID DESC \n"); }else if(filterValue != null && findOption.equals("executionCode")){ sql.append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND AC.PRSS IN (4,2) \n") .append(" AND DC.EXEC_CODE LIKE '%"+filterValue+"%' \n") .append(" AND (DC.END_DATE <= CURRENT DATE OR AC.SUBMIT_LIMIT_CNT > 0 AND AC.SUBMIT_LIMIT_CNT <= FN_SNDCNT(DC.ID)) \n") .append(" ORDER BY DC.ID DESC \n"); }else if(filterValue != null && findOption.equals("totDoc.mUser.name")){ sql.append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME ") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND AC.PRSS IN (4,2) \n") //.append(" AND SP_CM_DEC_FU(DC.OWNER_NAME) LIKE '%"+filterValue+"%' \n") .append(" and dc.owner_name = sp_cm_enc_fu('"+filterValue+"') \n") .append(" AND (DC.END_DATE <= CURRENT DATE OR AC.SUBMIT_LIMIT_CNT > 0 AND AC.SUBMIT_LIMIT_CNT <= FN_SNDCNT(DC.ID)) \n") .append(" ORDER BY DC.ID DESC \n"); }else{ sql.append(" SELECT \n") .append(" DC.USER_ID \n") .append(" ,DC.DEPT_ID \n") .append(" ,DC.ID \n") .append(" ,COALESCE(DC.EXEC_CODE,' ') \n") .append(" ,DC.NAME \n") .append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) \n") .append(" ,DC.START_DATE \n") .append(" ,DC.END_DATE \n") .append(" ,DC.REP_ID \n") .append(" ,AC.PRSS \n") .append(" ,AC.SUBMIT_LIMIT_CNT \n") .append(" ,FN_SNDCNT(DC.ID) \n") .append(" ,FN_ALLCNT(DC.ID) \n") .append(" ,FN_NOTECNT(DC.ID) \n") .append(" ,DT.NAME \n") //20140.08.29 Add by KWON,HAN .append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' GROUP BY REG_ID ) AS SHARE_DOC_REG_ID \n") .append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS SHARE_USER_COUNT \n") .append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = '"+usID+"' ) AS SHARE_USER_ID \n") //+++++++++++++++++++++++++ .append(" FROM \n") .append(" MOUMI_TOT_DOC DC \n") .append(" ,MOUMI_DEPT DT \n") .append(" ,MOUMI_DEPT GR \n") .append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n") .append(" WHERE \n") .append(" DC.DEPT_ID = DT.ID \n") .append(" AND DT.ORGAN = GR.ID \n") .append(" AND DC.DEL_TYPE='N' \n") .append(" "+tmpAppend+" ") .append(" AND DC.TOT_RANGE = 1 \n") .append(" AND DC.ACPT_LIMIT = AC.ID \n") .append(" AND AC.PRSS IN (4,2) \n") .append(" AND (DC.END_DATE <= CURRENT DATE OR AC.SUBMIT_LIMIT_CNT > 0 AND AC.SUBMIT_LIMIT_CNT <= FN_SNDCNT(DC.ID)) \n") .append(" ORDER BY DC.ID DESC \n"); } //System.out.println("=== MUser2.getEndRecord= =================================="); //System.out.println("sql = \n"+sql); //System.out.println("==========================================================="); rs = execQuery(sql); if (rs != null) { rs.last(); count = rs.getRow(); rs.beforeFirst(); } else { return; } userId = new String[count]; deptId = new String[count]; id = new int[count]; execCode = new String[count]; name = new String[count]; ownerName = new String[count]; startDate = new Date[count]; endDate = new Date[count]; repId = new int[count]; prss = new int[count]; moumiacptLimitstrategy = new int[count]; allCount = new int[count]; sendCount = new int[count]; noteCount = new int[count]; deptName = new String[count]; tCount = count; shareDocRegId = new String[count]; //2014.08.29 Add by KWON,HAN : 집계자료의 등록자 ID shareUserCount = new int[count]; //2014.08.29 Add by KWON,HAN : 집계자료의 사용자 공유수 shareUserId = new String[count]; //2014.08.29 Add by KWON,HAN : 집계자료의 공유자 ID for (int i = 0; rs.next(); i++) { userId[i] = rs.getString(1); deptId[i] = rs.getString(2); id[i] = rs.getInt(3); execCode[i] = rs.getString(4); name[i] = rs.getString(5); ownerName[i] = rs.getString(6); startDate[i] = rs.getDate(7); endDate[i] = rs.getDate(8); repId[i] = rs.getInt(9); prss[i] = rs.getInt(10); moumiacptLimitstrategy[i] = rs.getInt(11); allCount[i] = rs.getInt(12); sendCount[i] = rs.getInt(13); noteCount[i] = rs.getInt(14); deptName[i] = rs.getString(15); shareDocRegId[i] = rs.getString(16); //2014.08.29 Add by KWON,HAN : 집계자료의 등록자 ID shareUserCount[i] = rs.getInt(17); //2014.08.29 Add by KWON,HAN : 집계자료의 사용자 공유수 shareUserId[i] = rs.getString(18); //2014.08.29 Add by KWON,HAN : 집계자료의 공유자 ID } //System.out.println(sql); } catch (SQLException sqlEx) { LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}", new Object[] { sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage()}); throw new RuntimeException(sqlEx); } catch (Exception ex) { throw new RuntimeException(ex); } finally { try { if (con != null) con.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } try { if (pstmt != null) pstmt.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } try { if (rs != null) rs.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } } } /******** tmpName 구해오기 ********/ public void getTmpName(String usID) { StringBuilder sql = new StringBuilder(250); try { sql.append(" SELECT sp_cm_dec_fu(user_nm_encpt) FROM TN_SYMUSMUM001 WHERE usrid = '"+usID+"'"); rs = execQuery(sql); if (rs != null) { rs.last(); count = rs.getRow(); rs.beforeFirst(); } else { return; } userNmEncpt = new String(); for (int i = 0; rs.next(); i++) { userNmEncpt = rs.getString(1); } //System.out.println(sql); } catch (SQLException sqlEx) { LOG.error("\nSQLState - {}\nMySQL Error Code - {}\nmessage - {}\nsql - {}", new Object[] { sqlEx.getSQLState(), sqlEx.getErrorCode(), sqlEx.getMessage()}); throw new RuntimeException(sqlEx); } catch (Exception ex) { throw new RuntimeException(ex); } finally { try { if (con != null) con.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } try { if (pstmt != null) pstmt.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } try { if (rs != null) rs.close(); } catch (SQLException ex) { //31.오류 상황 대응 부재_CWE-390 Add by YOUNGJUN,CHO ex.printStackTrace(); //++++++++++++++++++++++++++++++++++++++++++++++++ } } } public String[] getDeptId() { //24.Public 메소드로부터 반환된 Private배열_CWE-495 : Update by KWON,HAN // return deptId; String[] ret = null; if( this.deptId != null) { ret = new String[deptId.length]; for (int i=0; i