/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package kr.co.kihyun.service; import java.math.BigDecimal; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.jdo.PersistenceManager; import javax.jdo.Query; import kr.co.kihyun.beans.entity.MDept; import kr.co.kihyun.beans.entity.MUser; import kr.co.kihyun.beans.entity.ShareDoc; import kr.co.kihyun.beans.entity.SysAuth; import kr.co.kihyun.beans.entity.TotDoc; import kr.co.kihyun.beans.entity.TotDocProcess; import kr.co.kihyun.beans.entity.UserHistory; import kr.co.kihyun.lang.MString; import kr.co.kihyun.service.vo.DeptVO; import kr.co.kihyun.service.vo.TotDocVO; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * * @author Kts */ public class TotDocService extends BaseService { private static final Logger LOG = LoggerFactory.getLogger(TotDocService.class); /** * 기본생성자 */ public TotDocService() {} /** * 생성자 * @param pm PersistenceManager */ public TotDocService(PersistenceManager pm) { super(pm); } /** * 집계자료ID로 조회하는 메소드 * @param docId * @return */ public TotDocVO getTotDocById(Long docId) { StringBuilder sbSearch = new StringBuilder(); sbSearch.append("SELECT \n"); sbSearch.append(" DC.ID AS id \n"); sbSearch.append(" , DC.ACPT_LIMIT AS acptLimit \n"); sbSearch.append(" , DC.ACC_AUTH AS accAuth \n"); sbSearch.append(" , DC.APPEND_TYPE AS appendType \n"); sbSearch.append(" , DC.END_DATE AS endDate \n"); sbSearch.append(" , DC.EXEC_CODE AS execCode \n"); sbSearch.append(" , DC.FIX_NUM AS fixNum \n"); sbSearch.append(" , DC.FORM AS form \n"); // sbSearch.append(" , DC.GROUP AS group \n"); sbSearch.append(" , DC.HANDY_NAME AS handyName \n"); sbSearch.append(" , DC.DEL_TYPE AS delType \n"); sbSearch.append(" , DC.DEPT_ID AS deptId \n"); sbSearch.append(" , DC.USER_ID AS userId \n"); sbSearch.append(" , DC.REP_DEPT_ID AS repDeptId \n"); sbSearch.append(" , DC.REP_ID AS repId \n"); sbSearch.append(" , DC.NAME AS name \n"); sbSearch.append(" , DC.REG_DATE AS regDate \n"); sbSearch.append(" , DC.START_DATE AS startDate \n"); sbSearch.append(" , DC.TOT_RANGE AS totRange \n"); sbSearch.append(" , DC.GROUP_ID AS groupId \n"); sbSearch.append(" , DC.MNG_CODE AS mngCode \n"); sbSearch.append(" , DC.OWNER_EMAIL AS ownerEmail \n"); sbSearch.append(" , SP_CM_DEC_FU(DC.OWNER_NAME) AS ownerName \n"); sbSearch.append(" , DC.OWNER_PHONE AS ownerPhone \n"); sbSearch.append(" , DC.PERIODICAL_DOC_YN AS periodicalDocYn \n"); sbSearch.append(" , DC.AUTO_RTN_YN AS autoRtnYn \n"); // sbSearch.append(" , CASE WHEN AC.ACPT_TYPE = 'DEADLINE' THEN /* 기한종료 */ \n"); // sbSearch.append(" CASE WHEN AC.PRSS = 4 OR DC.END_DATE < SYSDATE THEN 'Y' ELSE 'N' END \n"); // sbSearch.append(" WHEN AC.ACPT_TYPE = 'SUBMIT_DATE' THEN /* 선착순종료 */ \n"); // sbSearch.append(" CASE WHEN AC.PRSS = 4 OR DC.END_DATE < SYSDATE OR \n"); // sbSearch.append(" AC.SUBMIT_LIMIT_CNT = \n"); // sbSearch.append(" (SELECT COUNT(*) FROM MOUMI_TOT_REPORT WHERE DOC_ID = DC.ID AND PRSS IN (6) )\n"); // sbSearch.append(" THEN 'Y' ELSE 'N' END \n"); // sbSearch.append(" WHEN AC.ACPT_TYPE = 'UNLIMIT' THEN /* 수동종료 */ \n"); // sbSearch.append(" CASE WHEN AC.PRSS = 4 OR DC.END_DATE < SYSDATE THEN 'Y' ELSE 'N' END \n"); // sbSearch.append(" END AS completeYn \n"); sbSearch.append(" , AC.ACPT_TYPE AS acptType \n"); sbSearch.append(" , AC.PRSS AS prss \n"); sbSearch.append(" , AC.SUBMIT_LIMIT_CNT AS submitLimitCnt \n"); sbSearch.append(" , (SELECT COUNT(*) FROM MOUMI_TOT_REPORT WHERE DOC_ID = DC.ID AND PRSS IN (6) ) AS completeReportCount \n"); sbSearch.append("FROM \n"); sbSearch.append(" MOUMI_TOT_DOC DC \n"); sbSearch.append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n"); sbSearch.append("WHERE \n"); sbSearch.append(" DC.ACPT_LIMIT = AC.ID \n"); sbSearch.append(" AND DC.ID = ? \n"); //LOG.debug("query={}", sbSearch.toString()); Query query = pm.newQuery("javax.jdo.query.SQL",sbSearch.toString()); query.setResultClass(TotDocVO.class); //LOG.debug("docId={}", docId); //v2. 6.SQL 삽입 : JDO형식이므로 해결책에 따른 prepare SQL 문으로 변경할 수 없음 List list = (List)query.execute(docId); //================ TotDocVO docVO = (TotDocVO)list.get(0); //logResultList(list); return docVO; } /** * 집계자료에 대한 자동회수여부를 수정하는 메소드 * @param docId - 집계자료id * @param autoRtnYn - 자동회수여부 */ public void updateAutoRtnYnForDoc(Long docId, String autoRtnYn) { TotDoc doc = pm.getObjectById(TotDoc.class, docId); doc.setAutoRtnYn(autoRtnYn); pm.makePersistent(doc); } /** * 집계자료에 대한 종료여부를 판단하는 메소드 * @param docId - 집계자료id * @return String - Y.집계종료, N.집계진행 */ public boolean chkIfDocEnd(Long docId) { TotDocVO docVO = getTotDocById(docId); return docVO.isComplete(); } /** * 내제출자료에 대한 자료회수요청이 가능한 지 판단하는 메소드 * 집계완료인 경우에는 자료회수요청 불가(false) * @param docId - 집계자료id * @return boolean * @참조페이지 - popup_recall_note.jsp */ public boolean chkIfPossibleForReturn(Long docId) { boolean bPossible = true; TotDocVO docVO = getTotDocById(docId); String acptType = docVO.getAcptType(); int prss = docVO.getPrss(); if( acptType.equals("SUBMIT_DATE") ) { //선착순종료 if( prss == TotDocProcess.END.ordinal() ) { //완료 //prss == 4 bPossible = false; }else{ //prss <> 4 //자신의 제출자료도 이미 완료된 상태이므로 제출완료문서갯수 - 1로 처리해서 비교한다. int submitLimitCnt = docVO.getSubmitLimitCnt(); int completeReportCount = docVO.getCompleteReportCount(); if( submitLimitCnt >= completeReportCount) { bPossible = true; }else{ bPossible = false; } } }else{ bPossible = !docVO.isComplete(); } return bPossible; } /** * 검색조건쿼리와 레코드개수조회쿼리를 생성하는 메소드(제출완료) * String findOption,Object filterValue,int sysAuth, * @param params - 검색조건맵 * - userId * - deptId * - findOption * - filterValue * @param docType - PRSS:집계진행자료, REG:미진행자료, END:종료자료 * @참조페이지 - doc_list.jsp */ public void createDocListSearchQuery(Map params, String docType) { StringBuilder sbSearch = new StringBuilder(); String userId = (String) params.get("userId"); String deptId = (String) params.get("deptId"); String findOption = (String) params.get("findOption"); String filterValue = (String) params.get("filterValue"); MUser mUser = pm.getObjectById(MUser.class, userId); SysAuth sysAuth = mUser.getSysAuth(); MDept mdept = pm.getObjectById(MDept.class, deptId); //0.ID String gradeFlag = mdept.getGrade().toString(); sbSearch.append(" SELECT DC.USER_ID AS userId \n"); sbSearch.append(" , DC.DEPT_ID AS deptId \n"); sbSearch.append(" , DC.ID \n"); sbSearch.append(" , COALESCE(DC.EXEC_CODE,' ') AS execCode \n"); sbSearch.append(" , DC.NAME \n"); sbSearch.append(" , SP_CM_DEC_FU(DC.OWNER_NAME) AS ownerName \n"); sbSearch.append(" , DC.START_DATE AS startDate \n"); sbSearch.append(" , DC.END_DATE AS endDate \n"); sbSearch.append(" , DC.REP_ID AS repId \n"); sbSearch.append(" , AC.PRSS \n"); sbSearch.append(" , AC.SUBMIT_LIMIT_CNT AS submitLimitCnt \n"); sbSearch.append(" , FN_SNDCNT(DC.ID) AS sndCnt \n"); sbSearch.append(" , FN_ALLCNT(DC.ID) AS allCnt \n"); sbSearch.append(" , FN_NOTECNT(DC.ID) AS noteCnt \n"); sbSearch.append(" , DT.NAME AS deptName \n"); sbSearch.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 shareDocRegId \n"); sbSearch.append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = DC.ID AND DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' ) AS shareUserCount \n"); sbSearch.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 = :userId ) AS shareUserId \n"); sbSearch.append(" , DC.PERIODICAL_DOC_YN AS periodicalDocYn \n"); sbSearch.append(" , DC.APPEND_TYPE AS appendType \n"); // sbSearch.append(" , ROWNUMBER() OVER() AS rowNo \n"); sbSearch.append(" FROM MOUMI_TOT_DOC DC \n"); sbSearch.append(" , MOUMI_DEPT DT \n"); sbSearch.append(" , MOUMI_DEPT GR \n"); sbSearch.append(" , MOUMI_ACPT_LIMIT_STRATEGY AC \n"); sbSearch.append(" WHERE DC.DEL_TYPE = 'N' \n"); sbSearch.append(" AND DC.TOT_RANGE = 1 \n"); if (docType.equals("END")) { if( sysAuth == SysAuth.SYSADM || sysAuth == SysAuth.SUB_SYSADM || sysAuth == SysAuth.SUB_TOTADM ) { //SYSADM:9, SUB_SYSADM:7, SUB_TOTADM:6 추가 by wonseok Lee 20171101 sbSearch.append(" AND DC.START_DATE BETWEEN TO_DATE(:stDate,'YYYY-MM-DD') AND TO_DATE(:edDate,'YYYY-MM-DD') + 1 \n"); } } if (!MString.isNull(findOption) && !MString.isNull(filterValue)) { if (findOption.equals("name")) { sbSearch.append(" AND DC.NAME LIKE '%' || :filterValue || '%' \n"); } else if (findOption.equals("executionCode")) { sbSearch.append(" AND DC.EXEC_CODE LIKE '%' || :filterValue || '%' \n"); } else if (findOption.equals("totDoc.mUser.name")) { // sbSearch.append(" AND SP_CM_DEC_FU(DC.OWNER_NAME) LIKE '%' || :filterValue || '%' \n"); sbSearch.append(" AND DC.OWNER_NAME = SP_CM_ENC_FU(:filterValue) \n"); } } sbSearch.append(" AND DC.DEPT_ID = DT.ID \n"); sbSearch.append(" AND DT.ORGAN = GR.ID \n"); sbSearch.append(" AND DC.ACPT_LIMIT = AC.ID \n"); //if( (SysAuth) params.get("sysAuth") == SysAuth.SYSADM) { //SYSADM:9 if( sysAuth == SysAuth.SYSADM) { //SYSADM:9 //Nothing sbSearch.append(" AND 1=1 \n"); //} else if( (SysAuth) params.get("sysAuth") == SysAuth.SUB_SYSADM && MString.isNull(gradeFlag) ) { //SUB_SYSADM:7 } else if( sysAuth == SysAuth.SUB_SYSADM && MString.isNull(gradeFlag) ) { //SUB_SYSADM:7 sbSearch.append(" AND ( ( DC.USER_ID = :userId AND DC.ACC_AUTH IN (0,1,2) ) \n"); sbSearch.append(" OR ( DC.DEPT_ID = :deptId AND DC.ACC_AUTH IN (1,2) ) \n"); sbSearch.append(" OR ( DC.ACC_AUTH IN (0,1,2) AND GR.ID = ( SELECT GR.id \n"); sbSearch.append(" FROM MOUMI_MUSER US \n"); sbSearch.append(" , MOUMI_DEPT DT \n"); sbSearch.append(" , MOUMI_DEPT GR \n"); sbSearch.append(" WHERE US.ID= :userId \n"); sbSearch.append(" AND US.DEPT_ID = DT.ID \n"); sbSearch.append(" AND DT.ORGAN = GR.ID ) \n"); sbSearch.append(" ) \n"); sbSearch.append(" OR DC.ACC_AUTH = 3 \n"); sbSearch.append(" OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = :userId ) ) \n"); // 미진행 자료시 연계문서는 무조건 보이게 if (docType.equals("REG")){ sbSearch.append(" or DC.ID>=1700000000 \n"); } sbSearch.append(" ) \n"); } else if( (sysAuth == SysAuth.SUB_TOTADM ) && MString.isNull(gradeFlag) ) { sbSearch.append(" AND ( ( DC.USER_ID = :userId AND DC.ACC_AUTH IN (0,1,2) ) \n"); sbSearch.append(" OR ( DC.DEPT_ID = :deptId AND DC.ACC_AUTH IN (0,1,2) ) \n"); // sbSearch.append(" OR ( DC.ACC_AUTH IN (0,1) AND GR.ID = ( SELECT GR.id \n"); // sbSearch.append(" FROM MOUMI_MUSER US \n"); // sbSearch.append(" , MOUMI_DEPT DT \n"); // sbSearch.append(" , MOUMI_DEPT GR \n"); // sbSearch.append(" WHERE US.ID= :userId \n"); // sbSearch.append(" AND US.DEPT_ID = DT.ID \n"); // sbSearch.append(" AND DT.ORGAN = GR.ID ) \n"); // sbSearch.append(" ) \n"); sbSearch.append(" OR DC.ACC_AUTH = 3 \n"); sbSearch.append(" OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = :userId ) ) \n"); // 미진행 자료시 연계문서는 무조건 보이게 if (docType.equals("REG")){ sbSearch.append(" or DC.ID>=1700000000 \n"); } sbSearch.append(" ) \n"); } else if( sysAuth == SysAuth.TOTPER && MString.isNull(gradeFlag) ) { //TOTPER:2 sbSearch.append(" AND ( ( DC.USER_ID = :userId AND DC.ACC_AUTH IN (0,1,2) ) \n"); sbSearch.append(" OR ( DC.DEPT_ID = :deptId AND DC.ACC_AUTH IN (1,2) ) \n"); sbSearch.append(" OR ( DC.ACC_AUTH IN (2) AND GR.ID = ( SELECT GR.id \n"); sbSearch.append(" FROM MOUMI_MUSER US \n"); sbSearch.append(" , MOUMI_DEPT DT \n"); sbSearch.append(" , MOUMI_DEPT GR \n"); sbSearch.append(" WHERE US.ID= :userId \n"); sbSearch.append(" AND US.DEPT_ID = DT.ID \n"); sbSearch.append(" AND DT.ORGAN = GR.ID ) \n"); sbSearch.append(" ) \n"); sbSearch.append(" OR DC.ACC_AUTH = 3 \n"); sbSearch.append(" OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = :userId ) ) \n"); // 미진행 자료시 연계문서는 무조건 보이게 if (docType.equals("REG")){ sbSearch.append(" or DC.ID>=1700000000 \n"); } sbSearch.append(" ) \n"); } else { sbSearch.append(" AND ( ( DC.USER_ID = :userId AND DC.ACC_AUTH IN (0,1,2,3) ) \n"); sbSearch.append(" OR ( DC.DEPT_ID = :deptId AND DC.ACC_AUTH IN (1,2,3) ) \n"); sbSearch.append(" OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = :userId ) ) \n"); // 미진행 자료시 연계문서는 무조건 보이게 if (docType.equals("REG"))sbSearch.append(" or DC.ID>=1700000000 \n"); sbSearch.append(" ) \n"); } if (!MString.isNull(docType)) { if (docType.equals("PRSS")) { sbSearch.append(" AND DC.START_DATE <= SYSDATE \n"); sbSearch.append(" AND DC.END_DATE >= SYSDATE AND AC.PRSS != 1 \n"); sbSearch.append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID) ) \n"); } else if (docType.equals("REG")) {//미진행자료 sbSearch.append(" AND ( ( AC.PRSS = 1 AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) ) \n"); sbSearch.append(" OR ( DC.START_DATE >= SYSDATE AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) ) \n"); sbSearch.append(" ) \n"); } else if (docType.equals("END")) { sbSearch.append(" AND AC.PRSS IN (4,2) \n"); sbSearch.append(" AND ( DC.END_DATE <= SYSDATE OR AC.SUBMIT_LIMIT_CNT > 0 AND AC.SUBMIT_LIMIT_CNT <= FN_SNDCNT(DC.ID) ) \n"); } } //레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장 //페이징처리함수 호출전에 등록 필요. this.setCountQuery(sbSearch); this.setParams(params); sbSearch.append(" ORDER BY DC.ID DESC \n"); //페이징처리쿼리생성함수 호출 this.setSearchQuery(sbSearch); // LOG.debug("TotDocService.createDocListSearchQuery ======================\nquery=\n{}\n===============================", sbSearch.toString()); // LOG.debug("docType={}", docType); // LOG.debug("params={}", params); } /** * 집계자료에 대한 담당자를 변경하는 메소드(Long[]) * * @param docIds - Long Array 집계자료id * @param fromUser - String 원담당자 * @param toUser - String 변경담당자 */ public void procChangeOwnerForDoc(Long[] docIds, String fromUser, String toUser) { MUser mFromUser = pm.getObjectById(MUser.class, fromUser); MUser mToUser = pm.getObjectById(MUser.class, toUser); for (Long docId : docIds) { procChangeOwnerForDoc(docId, mFromUser, mToUser); } } /** * 집계자료에 대한 담당자를 변경하는 메소드(String[]) * * @param docIds - String Array 집계자료id * @param fromUser - String 원담당자 * @param toUser - String 변경담당자 */ public void procChangeOwnerForDoc(String[] docIds, String fromUser, String toUser) { MUser mFromUser = pm.getObjectById(MUser.class, fromUser); MUser mToUser = pm.getObjectById(MUser.class, toUser); for (String docId : docIds) { procChangeOwnerForDoc(new Long(docId), mFromUser, mToUser); } } /** * 제출자료에 대한 담당자를 변경하는 메소드(String) * * @param docId - Long 제출자료id * @param fromUser - String 원담당자 * @param toUser - String 변경담당자 */ public void procChangeOwnerForDoc(Long docId, String fromUser, String toUser) { procChangeOwnerForDoc(docId, pm.getObjectById(MUser.class, fromUser), pm.getObjectById(MUser.class, toUser)); } /** * 집계자료에 대한 담당자를 변경하는 메소드(MUser) * * @param docId - Long 집계자료id * @param fromUser - MUser 원담당자 * @param toUser - MUser 변경담당자 */ public void procChangeOwnerForDoc(Long docId, MUser fromUser, MUser toUser) { TotDoc totDoc = pm.getObjectById(TotDoc.class, docId); totDoc.setUser(toUser); totDoc.setDept(toUser.getUpperDept(pm)); pm.makePersistent(totDoc); UserHistory userHistory = new UserHistory(totDoc, fromUser, toUser, toUser.getUpperDept(pm)); pm.makePersistent(userHistory); //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //2014.09.05 Add by KWON,HAN : 사용자공유의 등록자를 변경한다. //1.문서 ID(DOC_ID), 문서 구분(DOC_REPORT_TYPE)에 해당하는 모든 데이터를 조회한다. ShareDocService shareDocSearch = new ShareDocService(pm); List tmpList = shareDocSearch.getSearchDocIdType("", docId, 'D'); //문서 구분(D:집계문서, R:제출문서) //2.모든 데이터의 등록자를 변경된 담당자로 변경한다. if (tmpList != null) { for (int ii = 0; ii < tmpList.size(); ii++) { Object[] objData = (Object[]) tmpList.get(ii); // System.out.println("HttpTransfer2.doPost() tmpList ==========="); // System.out.println("ID = " + objData[0]); // System.out.println("USER_ID = " + objData[1]); // System.out.println("=========================================="); ShareDoc shareDocUpd = pm.getObjectById(ShareDoc.class, (Long) objData[0]); //0.ID shareDocUpd.setRegId(toUser.getId()); pm.makePersistent(shareDocUpd); } } //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ } /** * 사용자의 집계진행건수를 조회한다. * 업무포탈 연계포틀릿(집계진행 건수 조회)에 사용된다. * * @param id 사용자의 ID. * @param dept 사용자의 소속 부서 ID. * @param level 사용자의 권한레벨. * @return 집계진행자료 건수 */ public int getTotDocCount(String id, String dept, SysAuth level) { Map params = new HashMap(); params.put("id", id); params.put("dept", dept); StringBuilder sbDept = new StringBuilder(); sbDept.append("SELECT nvl(grade,0) grade FROM MOUMI_DEPT WHERE ID=:dept \n"); Query queryDept = pm.newQuery("javax.jdo.query.SQL",sbDept.toString()); queryDept.setResultClass(DeptVO.class); List deptList = (List) queryDept.executeWithMap(params); // 접수 건수를 조회하기 위한 추가 파라미터 설정. params.put("grade", deptList.get(0).getGrade()); StringBuilder sbQuery = new StringBuilder(); sbQuery.append("SELECT COUNT(*) \n"); sbQuery.append("FROM MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC, MOUMI_DEPT DT, MOUMI_DEPT GR \n"); sbQuery.append("WHERE DC.DEL_TYPE='N' AND DC.GROUP_ID=2 \n"); sbQuery.append(" AND DC.DEPT_ID = DT.ID AND DT.ORGAN = GR.ID \n"); sbQuery.append(" AND DC.ACPT_LIMIT = AC.ID \n"); sbQuery.append(" AND DC.START_DATE <= SYSDATE \n"); sbQuery.append(" AND DC.END_DATE >= SYSDATE \n"); sbQuery.append(" AND ( AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID) ) \n"); if(level == SysAuth.SYSADM) { // 시스템관리자 일때. sbQuery.append(" AND 1 = 1 \n"); } else if(level == SysAuth.SUB_SYSADM && params.get("grade").equals(0)) { // 기관의 관리자 일때. sbQuery.append(" AND ( ( DC.USER_ID = :id AND DC.ACC_AUTH IN (0,1,2) ) \n"); sbQuery.append(" OR ( DC.DEPT_ID = :dept AND DC.ACC_AUTH IN (1,2) ) \n"); sbQuery.append(" OR ( DC.ACC_AUTH IN (0,1,2) AND GR.ID = ( SELECT GR.id \n"); sbQuery.append(" FROM MOUMI_MUSER US \n"); sbQuery.append(" , MOUMI_DEPT DT \n"); sbQuery.append(" , MOUMI_DEPT GR \n"); sbQuery.append(" WHERE US.ID= :id \n"); sbQuery.append(" AND US.DEPT_ID = DT.ID \n"); sbQuery.append(" AND DT.ORGAN = GR.ID ) \n"); sbQuery.append(" ) \n"); sbQuery.append(" OR DC.ACC_AUTH = 3 \n"); sbQuery.append(" OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = :id ) ) \n"); sbQuery.append(" ) \n"); } else if(level == SysAuth.TOTPER && params.get("grade").equals(0)) { // 기관의 일반사용자 일때. sbQuery.append(" AND ( ( DC.USER_ID = :id AND DC.ACC_AUTH IN (0,1,2) ) \n"); sbQuery.append(" OR ( DC.DEPT_ID = :dept AND DC.ACC_AUTH IN (1,2) ) \n"); sbQuery.append(" OR ( DC.ACC_AUTH IN (2) AND GR.ID = ( SELECT GR.id \n"); sbQuery.append(" FROM MOUMI_MUSER US \n"); sbQuery.append(" , MOUMI_DEPT DT \n"); sbQuery.append(" , MOUMI_DEPT GR \n"); sbQuery.append(" WHERE US.ID= :id \n"); sbQuery.append(" AND US.DEPT_ID = DT.ID \n"); sbQuery.append(" AND DT.ORGAN = GR.ID ) \n"); sbQuery.append(" ) \n"); sbQuery.append(" OR DC.ACC_AUTH = 3 \n"); sbQuery.append(" OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = :id ) ) \n"); sbQuery.append(" ) \n"); } else { // 그 외 사용자 일때. sbQuery.append(" AND ( ( DC.USER_ID = :id AND DC.ACC_AUTH IN (0,1,2) ) \n"); sbQuery.append(" OR ( DC.DEPT_ID = :dept AND DC.ACC_AUTH IN (1,2) ) \n"); sbQuery.append(" OR ( DC.ID IN ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'D' AND DEL_YN = 'N' AND USER_ID = :id ) ) \n"); sbQuery.append(" ) \n"); } Query query = pm.newQuery("javax.jdo.query.SQL",sbQuery.toString()); //query.setResultClass(Integer.class); // System.out.println("getDoc_count ======>" + sbQuery.toString()); List list = (List) query.executeWithMap(params); int totDocCount = (Integer) list.get(0); // System.out.println("====> totDocCount : " + totDocCount); return totDocCount; } //20160429 추가 /** * 검색조건쿼리와 레코드개수조회쿼리를 생성하는 메소드(IR_MOUMI_ACPT_LIMIT_STRATEGY 테이블에서 IS_APLIED에 따른 데이타 조회) * @param params - 검색조건맵 * @참조페이지 - doc_list.jsp */ public void createIR_Limit_SearchQuery(Map params) { StringBuilder sbSearch = new StringBuilder(); //String findOption = (String) params.get("findOption"); sbSearch.append(" SELECT * \n"); sbSearch.append(" FROM IFDBMGR.IR_MOUMI_ACPT_LIMIT_STRATEGY \n"); sbSearch.append(" WHERE IS_APLIED = :isAplied \n"); //레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장 this.setCountQuery(sbSearch); //조건맵을 멤버변수에 저장 this.setParams(params); // LOG.debug("접수자료 query={}", sbSearch.toString()); } //20160426 추가 /** * 검색조건쿼리와 레코드개수조회쿼리를 생성하는 메소드(교육부 전송관련 IR_MOUMI_TOT_DOC 테이블에서 IS_APLIED에 따른 데이타 조회) * @param params - 검색조건맵 * @참조페이지 - doc_list.jsp */ public void createIR_TOT_DOC_SearchQuery(Map params) { StringBuilder sbSearch = new StringBuilder(); //String findOption = (String) params.get("findOption"); sbSearch.append(" SELECT * \n"); sbSearch.append(" FROM IFDBMGR.IR_MOUMI_TOT_DOC \n"); sbSearch.append(" WHERE IS_APLIED = :isAplied \n"); //레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장 this.setCountQuery(sbSearch); //조건맵을 멤버변수에 저장 this.setParams(params); //검색쿼리를 멤버변수에 저장 //this.setSearchQuery(sbSearch); // LOG.debug("접수자료 query={}", sbSearch.toString()); } //20160429 추가 /** * 검색조건쿼리와 레코드개수조회쿼리를 생성하는 메소드(IR_MOUMI_TOT_TABLE 테이블에서 IS_APLIED에 따른 데이타 조회) * @param params - 검색조건맵 * @참조페이지 - doc_list.jsp */ public void createIR_TOT_TABLE_SearchQuery(Map params) { StringBuilder sbSearch = new StringBuilder(); //String findOption = (String) params.get("findOption"); sbSearch.append(" SELECT * \n"); sbSearch.append(" FROM IFDBMGR.IR_MOUMI_TOT_TABLE \n"); sbSearch.append(" WHERE IS_APLIED = :isAplied \n"); //레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장 this.setCountQuery(sbSearch); //조건맵을 멤버변수에 저장 this.setParams(params); // LOG.debug("접수자료 query={}", sbSearch.toString()); } //20160429 추가 /** * 검색조건쿼리와 레코드개수조회쿼리를 생성하는 메소드(IR_MOUMI_TOT_ITEM 테이블에서 IS_APLIED에 따른 데이타 조회) * @param params - 검색조건맵 * @참조페이지 - doc_list.jsp */ public void createIR_TOT_ITEM_SearchQuery(Map params) { StringBuilder sbSearch = new StringBuilder(); //String findOption = (String) params.get("findOption"); sbSearch.append(" SELECT * \n"); sbSearch.append(" FROM IFDBMGR.IR_MOUMI_TOT_ITEM \n"); sbSearch.append(" WHERE IS_APLIED = :isAplied \n"); //레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장 this.setCountQuery(sbSearch); //조건맵을 멤버변수에 저장 this.setParams(params); // LOG.debug("접수자료 query={}", sbSearch.toString()); } //20160429 추가 /** * 검색조건쿼리와 레코드개수조회쿼리를 생성하는 메소드(IR_MOUMI_BOARD 테이블에서 IS_APLIED에 따른 데이타 조회) * @param params - 검색조건맵 * @참조페이지 - doc_list.jsp */ public void createIR_BOARD_SearchQuery(Map params) { StringBuilder sbSearch = new StringBuilder(); //String findOption = (String) params.get("findOption"); sbSearch.append(" SELECT * \n"); sbSearch.append(" FROM IFDBMGR.IR_MOUMI_BOARD \n"); sbSearch.append(" WHERE IS_APLIED = :isAplied \n"); //레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장 this.setCountQuery(sbSearch); //조건맵을 멤버변수에 저장 this.setParams(params); // LOG.debug("접수자료 query={}", sbSearch.toString()); } //20160429 추가 /** * 검색조건쿼리와 레코드개수조회쿼리를 생성하는 메소드(IR_MOUMI_TOT_REPORT 테이블에서 IS_APLIED에 따른 데이타 조회) * @param params - 검색조건맵 * @참조페이지 - doc_list.jsp */ public void createIR_TOT_REPORT_SearchQuery(Map params) { StringBuilder sbSearch = new StringBuilder(); //String findOption = (String) params.get("findOption"); sbSearch.append(" SELECT * \n"); sbSearch.append(" FROM IFDBMGR.IR_MOUMI_TOT_REPORT \n"); sbSearch.append(" WHERE IS_APLIED = :isAplied \n"); //레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장 this.setCountQuery(sbSearch); //조건맵을 멤버변수에 저장 this.setParams(params); // LOG.debug("접수자료 query={}", sbSearch.toString()); } }