/* * 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.util.Date; 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.AcptDoc; 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.TotReport; import kr.co.kihyun.beans.entity.TotReportProcess; import kr.co.kihyun.beans.entity.UserHistory; import kr.co.kihyun.lang.MString; import kr.co.kihyun.moumi.report.MReport; import kr.co.kihyun.service.vo.DeptVO; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import kr.co.kihyun.db.CommonDBManager; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.math.BigDecimal; import kr.co.kihyun.moumi.MoumiConfig; /** * * @author Kts */ public class TotReportService extends BaseService { private static final Logger LOG = LoggerFactory.getLogger(TotReportService.class); /** * 기본생성자 */ public TotReportService() { } /** * 생성자 * * @param pm PersistenceManager */ public TotReportService(PersistenceManager pm) { super(pm); } /** * 검색조건쿼리와 레코드개수조회쿼리를 생성하는 메소드(접수자료) * * @param params - 검색조건맵 * @참조페이지 - report_list.jsp */ public void createAcceptReportListSearchQuery(Map params) { StringBuilder sbSearch = new StringBuilder(); // String userId = (String) params.get("userId"); String findOption = (String) params.get("findOption"); String filterValue = (String) params.get("filterValue"); // MUser mUser = pm.getObjectById(MUser.class, userId); // SysAuth sysAuth = mUser.getSysAuth(); SysAuth sysAuth = (SysAuth) params.get("sysAuth"); sbSearch.append(" SELECT \n"); sbSearch.append(" DC.PERIODICAL_DOC_YN AS periodicalDocYn \n"); sbSearch.append(" ,DC.APPEND_TYPE AS appendType \n"); sbSearch.append(" ,DC.EXEC_CODE AS execCode \n"); sbSearch.append(" ,DC.NAME AS docName \n"); sbSearch.append(" ,DT.NAME AS deptName \n"); sbSearch.append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) AS docOwnerName \n"); sbSearch.append(" ,SP_CM_DEC_FU(RT.OWNER_NAME) AS OwnerName \n"); sbSearch.append(" ,RT.USER_ID AS userID \n"); sbSearch.append(" ,DC.START_DATE AS startDate \n"); sbSearch.append(" ,DC.END_DATE AS endDate \n"); sbSearch.append(" ,RT.PRSS AS prss \n"); sbSearch.append(" ,NVL(RT.DOWN_DOC_ID,0) AS downDocId \n"); sbSearch.append(" ,RT.ID AS id \n"); // FN_SNDCNT 함수의 리턴 타입이 varchar 로 String 이다. 멤버변수 completeReportCount 는 Integer 로 선언되어 있어서 쿼리에서 타입 캐스팅을 수행한다. 2014.11.25 by YOUNGJUN,CHO //sbSearch.append(" ,FN_SNDCNT(DC.ID) AS completeReportCount \n"); sbSearch.append(" ,cast(FN_SNDCNT(DC.ID) as int) AS completeReportCount \n"); sbSearch.append(" ,AC.SUBMIT_LIMIT_CNT AS submitLimitCnt \n"); // sbSearch.append(" , ROWNUMBER() OVER() AS rowNo \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_DOC DC \n"); sbSearch.append(" ,MOUMI_TOT_REPORT RT \n"); sbSearch.append(" ,MOUMI_DEPT DT \n"); sbSearch.append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n"); sbSearch.append(" , ( \n"); sbSearch.append(" SELECT DEPT.GRADE \n"); sbSearch.append(" , DEPT.ID \n"); sbSearch.append(" , DEPT.UPPER_DEPT \n"); sbSearch.append(" , DEPT.ORGAN \n"); sbSearch.append(" , USR.DEPT_ID \n"); // 겸임자의 현재 선택부서확인을 위해 추가. 2014.12.12 Add by YOUNGJUN,CHO sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_DEPT DEPT \n"); sbSearch.append(" , MOUMI_MUSER USR \n"); sbSearch.append(" WHERE USR.DEPT_ID = DEPT.ID \n"); sbSearch.append(" AND USR.ID = :userId \n"); sbSearch.append(" ) MY \n"); sbSearch.append(" WHERE \n"); sbSearch.append(" RT.DOC_ID = DC.ID \n"); sbSearch.append(" AND DC.DEPT_ID = DT.ID \n"); sbSearch.append(" AND DC.DEL_TYPE='N' \n"); sbSearch.append(" AND RT.DEL_TYPE='N' \n"); sbSearch.append(" AND ( \n"); sbSearch.append(" ( \n"); sbSearch.append(" RT.USER_ID IS NULL \n"); sbSearch.append(" AND \n"); sbSearch.append(" ( \n"); sbSearch.append(" (MY.GRADE IS NULL AND RT.DEPT_ID IN (MY.ID, MY.UPPER_DEPT, MY.ORGAN) ) \n"); sbSearch.append(" OR (MY.GRADE IS NOT NULL AND RT.DEPT_ID = MY.ID ) \n"); sbSearch.append(" ) \n"); sbSearch.append(" ) \n"); //sbSearch.append(" OR RT.USER_ID = :userId \n"); sbSearch.append(" OR (RT.USER_ID = :userId AND RT.DEPT_ID = MY.DEPT_ID) \n"); // 겸임자의 현재 선택부서확인을 위해 추가. 2014.12.12 Add by YOUNGJUN,CHO //해당부서의 관리자도 볼 수 있도록 한다. 2014.12.29 Add by KWON,HAN // if( sysAuth.equals(SysAuth.SYSADM) || sysAuth.equals(SysAuth.SUB_SYSADM)) { //SYSADM:9, SUB_SYSADM:7 //모든 사용자가 담당자가 지정된 접수자료 또는 재입력 요청자료를 조회할 수 있도록 개선 sbSearch.append(" OR (RT.DEPT_ID = :deptId AND RT.PRSS IN (0, 3)) \n"); // } sbSearch.append(" ) \n"); sbSearch.append(" AND RT.PRSS IN (0,1,2,3) \n"); sbSearch.append(" AND DC.END_DATE >= SYSDATE \n"); sbSearch.append(" AND DC.GROUP_ID = 2 \n"); sbSearch.append(" AND DC.START_DATE <= SYSDATE \n"); sbSearch.append(" AND DC.ACPT_LIMIT = AC.ID AND AC.PRSS != 1 \n"); sbSearch.append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) \n"); if (!MString.isNull(findOption) && !MString.isNull(filterValue)) { if (findOption.equals("totDoc.name")) { sbSearch.append(" AND DC.NAME LIKE '%' || :filterValue || '%' \n"); } else if (findOption.equals("totDoc.executionCode")) { sbSearch.append(" AND DC.EXEC_CODE LIKE '%' || :filterValue || '%' \n"); } else if (findOption.equals("totDoc.mUser.name")) { sbSearch.append(" AND DC.OWNER_NAME = SP_CM_ENC_FU(:filterValue) \n"); // sbSearch.append(" AND SP_CM_DEC_FU(DC.OWNER_NAME) LIKE '%' || :filterValue || '%' \n"); } } //레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장 this.setCountQuery(sbSearch); //조건맵을 멤버변수에 저장 this.setParams(params); sbSearch.append(" ORDER BY DC.END_DATE,RT.ID ASC \n"); //검색쿼리를 멤버변수에 저장 this.setSearchQuery(sbSearch); LOG.debug("접수자료 query={}", sbSearch.toString()); } /** * 검색조건쿼리와 레코드개수조회쿼리를 생성하는 메소드(제출진행자료) * * @param params - 검색조건맵 * @참조페이지 - report_list.jsp */ public void createProcessingReportListSearchQuery(Map params) { StringBuilder sbSearch = new StringBuilder(); String findOption = (String) params.get("findOption"); String filterValue = (String) params.get("filterValue"); sbSearch.append(" SELECT \n"); sbSearch.append(" DC.PERIODICAL_DOC_YN AS periodicalDocYn \n"); sbSearch.append(" ,DC.APPEND_TYPE AS appendType \n"); sbSearch.append(" ,DC.EXEC_CODE AS execCode \n"); sbSearch.append(" ,DC.NAME AS docName \n"); sbSearch.append(" ,DT.NAME AS deptName \n"); sbSearch.append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) AS docOwnerName \n"); sbSearch.append(" ,SP_CM_DEC_FU(RT.OWNER_NAME) AS ownerName \n"); sbSearch.append(" ,DC.START_DATE AS startDate \n"); sbSearch.append(" ,DC.END_DATE AS endDate \n"); sbSearch.append(" ,RT.PRSS AS prss \n"); sbSearch.append(" ,NVL(RT.DOWN_DOC_ID,0) AS downDocId \n"); sbSearch.append(" ,RT.ID AS id \n"); sbSearch.append(" ,RT.USER_ID AS userId \n"); sbSearch.append(" ,RT.SUBMIT_DATE AS submitDate \n"); sbSearch.append(" ,AC.SUBMIT_LIMIT_CNT AS submitLimitCnt \n"); sbSearch.append(" ,RT.APPRO AS appro \n"); sbSearch.append(" ,AC.PRSS AS docPrss \n"); sbSearch.append(" ,NVL((SELECT AC.PRSS \n"); sbSearch.append(" FROM MOUMI_ACPT_LIMIT_STRATEGY AC, MOUMI_TOT_DOC DC \n"); sbSearch.append(" WHERE DC.ACPT_LIMIT = AC.ID AND RT.DOWN_DOC_ID = DC.ID AND DC.GROUP_ID=2),0) AS subDocPrss \n"); // 제출수를 출력하기 위해 추가. FN_SNDCNT 함수의 리턴 타입이 varchar 로 String 이다. 멤버변수 completeReportCount 는 Integer 로 선언되어 있어서 쿼리에서 타입 캐스팅을 수행한다. 2014.11.25 by YOUNGJUN,CHO sbSearch.append(" ,cast(FN_SNDCNT(DC.ID) as int) AS completeReportCount \n"); // sbSearch.append(" , ROWNUMBER() OVER() AS rowNo \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_DOC DC \n"); sbSearch.append(" ,MOUMI_TOT_REPORT RT \n"); sbSearch.append(" ,MOUMI_DEPT DT \n"); sbSearch.append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n"); sbSearch.append(" WHERE \n"); sbSearch.append(" RT.DOC_ID = DC.ID \n"); sbSearch.append(" AND DC.DEPT_ID = DT.ID \n"); sbSearch.append(" AND DC.DEL_TYPE='N' \n"); sbSearch.append(" AND RT.DEL_TYPE='N' \n"); sbSearch.append(" AND RT.PRSS IN (4,5) \n"); sbSearch.append(" AND DC.ACPT_LIMIT = AC.ID \n"); sbSearch.append(" AND DC.GROUP_ID = 2 \n"); if( params.get("organId").equals( params.get("deptId")) && (SysAuth) params.get("sysAuth") == SysAuth.SUB_SYSADM) { sbSearch.append(" AND (RT.DEPT_ID in (select id from moumi_dept where organ = :organId and grade is null and sel_type = 'Y')) \n"); }else{ sbSearch.append(" AND RT.DEPT_ID = :deptId \n"); } if (!MString.isNull(findOption) && !MString.isNull(filterValue)) { if (findOption.equals("totDoc.name")) { sbSearch.append(" AND DC.NAME LIKE '%' || :filterValue || '%' \n"); } else if (findOption.equals("totDoc.executionCode")) { sbSearch.append(" AND DC.EXEC_CODE LIKE '%' || :filterValue || '%' \n"); } else if (findOption.equals("totDoc.mUser.name")) { sbSearch.append(" AND DC.OWNER_NAME = SP_CM_ENC_FU(:filterValue) \n"); } else if (findOption.equals("totDoc.mUser.name2")) { sbSearch.append(" AND RT.OWNER_NAME = SP_CM_ENC_FU(:filterValue) \n"); } } //레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장 this.setCountQuery(sbSearch); //조건맵을 멤버변수에 저장 this.setParams(params); sbSearch.append(" ORDER BY CASE WHEN DC.END_DATE < SYSDATE THEN 0 ELSE 1 END DESC \n"); sbSearch.append(" ,DC.END_DATE ASC \n"); //검색쿼리를 멤버변수에 저장 this.setSearchQuery(sbSearch); // LOG.debug("제출진행 query={}", sbSearch.toString()); } /** * 검색조건쿼리와 레코드개수조회쿼리를 생성하는 메소드(제출완료) * 2014.12.15 Update by KWON,HAN : 속도개선을 하기 위해 유니온 으로 변경함 * * @param params - 검색조건맵 * @param reportType - COMP.내제출자료, END.전체제출자료 * @참조페이지 - report_list.jsp */ public void createReportListSearchQuery(Map params, String reportType) { StringBuilder sbSearch = new StringBuilder(); String findOption = (String) params.get("findOption"); String filterValue = (String) params.get("filterValue"); sbSearch.append(" SELECT \n"); sbSearch.append(" DC.PERIODICAL_DOC_YN AS periodicalDocYn \n"); sbSearch.append(" ,DC.APPEND_TYPE AS appendType \n"); sbSearch.append(" ,DC.EXEC_CODE AS execCode \n"); sbSearch.append(" ,DC.NAME AS docName \n"); sbSearch.append(" ,DT.NAME AS deptName \n"); sbSearch.append(" ,RDT.NAME AS rtDeptName \n"); sbSearch.append(" ,SP_CM_DEC_FU(DC.OWNER_NAME) AS docOwnerName \n"); sbSearch.append(" ,SP_CM_DEC_FU(RT.OWNER_NAME) AS ownerName \n"); sbSearch.append(" ,DC.START_DATE AS startDate \n"); sbSearch.append(" ,DC.END_DATE AS endDate \n"); sbSearch.append(" ,DC.REG_DATE AS regDate \n"); sbSearch.append(" ,RT.PRSS AS prss \n"); sbSearch.append(" ,RT.DOWN_DOC_ID AS downDocId \n"); sbSearch.append(" ,RT.ID AS id \n"); sbSearch.append(" ,RT.USER_ID AS userId \n"); sbSearch.append(" ,RT.SUBMIT_DATE AS submitDate \n"); sbSearch.append(" ,AC.SUBMIT_LIMIT_CNT AS submitLimitCnt \n"); sbSearch.append(" ,RT.APPRO AS appro \n"); sbSearch.append(" ,RT.SECU_YN AS secuYn \n"); // sbSearch.append(" , ROWNUMBER() OVER() AS rowNo \n"); sbSearch.append(" , AC.ACPT_TYPE AS docAcptType \n"); sbSearch.append(" , AC.PRSS AS docPrss \n"); // 제출수를 출력하기 위해 추가. FN_SNDCNT 함수의 리턴 타입이 varchar 로 String 이다. 멤버변수 completeReportCount 는 Integer 로 선언되어 있어서 쿼리에서 타입 캐스팅을 수행한다. 2014.11.25 by YOUNGJUN,CHO sbSearch.append(" ,cast(FN_SNDCNT(DC.ID) as int) AS completeReportCount \n"); //20140.08.29 Add by KWON,HAN sbSearch.append(" , ( SELECT REG_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = RT.ID AND DOC_REPORT_TYPE = 'R' AND DEL_YN = 'N' GROUP BY REG_ID ) AS shareDocRegId \n"); sbSearch.append(" , ( SELECT COUNT(USER_ID) FROM MOUMI_SHARE_DOC WHERE DOC_ID = RT.ID AND DOC_REPORT_TYPE = 'R' AND DEL_YN = 'N' ) AS shareUserCount \n"); sbSearch.append(" , ( SELECT USER_ID FROM MOUMI_SHARE_DOC WHERE DOC_ID = RT.ID AND DOC_REPORT_TYPE = 'R' AND DEL_YN = 'N' AND USER_ID = :userId ) AS shareUserId \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_DOC DC \n"); sbSearch.append(" , ( SELECT RT.ID \n"); sbSearch.append(" , RT.DOC_ID \n"); sbSearch.append(" , RT.OWNER_NAME \n"); sbSearch.append(" , RT.PRSS \n"); sbSearch.append(" , RT.DOWN_DOC_ID \n"); sbSearch.append(" , RT.DEPT_ID \n"); sbSearch.append(" , RT.USER_ID \n"); sbSearch.append(" , RT.SUBMIT_DATE \n"); sbSearch.append(" , RT.APPRO \n"); sbSearch.append(" , RT.SECU_YN \n"); sbSearch.append(" FROM MOUMI_TOT_REPORT RT \n"); sbSearch.append(" WHERE RT.DEL_TYPE='N' \n"); sbSearch.append(" AND RT.PRSS IN (6,12,14) \n"); if (reportType.equals("COMP")) { //내제출자료 sbSearch.append(" AND RT.USER_ID = :userId \n"); } else if (reportType.equals("END")) { if( params.get("organId").equals( params.get("deptId")) && (SysAuth) params.get("sysAuth") == SysAuth.SUB_SYSADM) { sbSearch.append(" AND (RT.DEPT_ID in (select id from moumi_dept where organ = :organId and grade is null and sel_type = 'Y')) \n"); }else{ sbSearch.append(" AND RT.DEPT_ID = :deptId \n"); } //일반사용자인 경우에는 자신이 작성하지 않은 보안문서는 제외함. if ((SysAuth) params.get("sysAuth") == SysAuth.TOTPER) { sbSearch.append(" AND ( RT.SECU_YN = 'N' OR ( RT.USER_ID = :userId AND RT.SECU_YN = 'Y' ) ) \n"); } sbSearch.append(" UNION \n"); sbSearch.append(" SELECT RT.ID \n"); sbSearch.append(" , RT.DOC_ID \n"); sbSearch.append(" , RT.OWNER_NAME \n"); sbSearch.append(" , RT.PRSS \n"); sbSearch.append(" , RT.DOWN_DOC_ID \n"); sbSearch.append(" , RT.DEPT_ID \n"); sbSearch.append(" , RT.USER_ID \n"); sbSearch.append(" , RT.SUBMIT_DATE \n"); sbSearch.append(" , RT.APPRO \n"); sbSearch.append(" , RT.SECU_YN \n"); sbSearch.append(" FROM MOUMI_TOT_REPORT RT \n"); sbSearch.append(" , ( SELECT DOC_ID FROM MOUMI_SHARE_DOC WHERE DOC_REPORT_TYPE = 'R' AND DEL_YN = 'N' AND USER_ID = :userId ) Z \n"); sbSearch.append(" WHERE RT.DEL_TYPE='N' \n"); sbSearch.append(" AND RT.PRSS IN (6,12,14) \n"); sbSearch.append(" AND RT.ID = Z.DOC_ID \n"); //일반사용자인 경우에는 자신이 작성하지 않은 보안문서는 제외함. if ((SysAuth) params.get("sysAuth") == SysAuth.TOTPER) { sbSearch.append(" AND ( RT.SECU_YN = 'N' OR ( RT.USER_ID = :userId AND RT.SECU_YN = 'Y' ) ) \n"); } } sbSearch.append(" ) RT \n"); sbSearch.append(" ,MOUMI_DEPT DT \n"); sbSearch.append(" ,MOUMI_DEPT RDT \n"); sbSearch.append(" ,MOUMI_ACPT_LIMIT_STRATEGY AC \n"); sbSearch.append(" WHERE \n"); sbSearch.append(" RT.DOC_ID = DC.ID \n"); sbSearch.append(" AND DC.DEPT_ID = DT.ID \n"); sbSearch.append(" AND RT.DEPT_ID = RDT.ID \n"); sbSearch.append(" AND DC.GROUP_ID = 2 \n"); sbSearch.append(" AND DC.DEL_TYPE='N' \n"); sbSearch.append(" AND RT.SUBMIT_DATE BETWEEN TO_DATE(:stDate,'YYYY-MM-DD') AND TO_DATE(:edDate,'YYYY-MM-DD') + 1 \n"); sbSearch.append(" AND DC.ACPT_LIMIT = AC.ID \n"); if (!MString.isNull(findOption) && !MString.isNull(filterValue)) { if (findOption.equals("totDoc.name")) { sbSearch.append(" AND DC.NAME LIKE '%' || :filterValue || '%' \n"); } else if (findOption.equals("totDoc.executionCode")) { sbSearch.append(" AND DC.EXEC_CODE LIKE '%' || :filterValue || '%' \n"); } else if (findOption.equals("totDoc.mUser.name")) { sbSearch.append(" AND DC.OWNER_NAME = SP_CM_ENC_FU(:filterValue) \n"); } else if (findOption.equals("totDoc.mUser.name2")) { sbSearch.append(" AND RT.OWNER_NAME = SP_CM_ENC_FU(:filterValue) \n"); } } //레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장 this.setCountQuery(sbSearch); //조건맵을 멤버변수에 저장 this.setParams(params); sbSearch.append(" ORDER BY \n"); sbSearch.append(" CASE WHEN RT.SUBMIT_DATE IS NULL THEN 0 ELSE 1 END DESC \n"); sbSearch.append(" , RT.SUBMIT_DATE DESC \n"); //검색쿼리를 멤버변수에 저장 this.setSearchQuery(sbSearch); LOG.debug("제출자료 TotReportService.createReportListSearchQuery ======================\nquery=\n{}\n===============================", sbSearch.toString()); LOG.debug("params={}", params); LOG.debug("this.params={}", this.params); } /** * 제출자료에 대한 부서공유/부서공유취소를 처리하는 메소드 * * @param reportId - 제출자료ID * @param appro - 0.개인자료(부서공유취소), 1.부서공유 * @참조페이지 - report_list.jsp */ public void updateApproForReport(Long reportId, int appro) { TotReport report = pm.getObjectById(TotReport.class, reportId); report.setAppro(appro); pm.makePersistent(report); } /** * 제출자료에 대한 보안여부를 처리하는 메소드 * * @param reportId - 제출자료ID * @param secuYn - Y.보안, N.비보안 * @참조페이지 - report_list.jsp, report_view.jsp */ public void updateSecuYnForReport(Long reportId, String secuYn) { TotReport report = pm.getObjectById(TotReport.class, reportId); report.setSecuYn(secuYn); pm.makePersistent(report); } /** * 제출자료에 대한 진행상태를 수정하는 메소드 * * @param reportId - 제출자료ID * @param prss - TotReportProcess * @참조페이지 - report_view.jsp */ public void updatePrssForReport(Long reportId, TotReportProcess prss) { TotReport report = pm.getObjectById(TotReport.class, reportId); report.setProcess(prss); pm.makePersistent(report); } /** * 제출자료에 대한 제출완료로 처리하는 메소드 * * @param reportId - 제출자료ID * @param accDocCd - 문서번호 * @param deptName - 부서명 */ public void procCompleteForReport(Long reportId, int accDocCd, String deptName) { TotReport report = pm.getObjectById(TotReport.class, reportId); report.setProcess(TotReportProcess.COMP); report.setRegDate(new Date()); AcptDoc acptDoc = null; if (accDocCd > 0 && !MString.isNull(deptName)) { acptDoc = new AcptDoc(deptName, accDocCd); } report.setAcptDoc(acptDoc); pm.makePersistent(report); } /** * 제출자료에 대하여 초기화처리하는 메소드 * @param reportId - 제출자료ID */ public void procInitialForReport(Long reportId) { TotReport report = pm.getObjectById(TotReport.class, reportId); report.clearAttachment(); report.setData(null); report.setProcess(TotReportProcess.values()[MReport.ACCEPT_LIST]); report.setNote(null); report.setUser(null); pm.makePersistent(report); } /** * 부서초기화 시 기관코드로 업데이트 17.06.20 by jskim * 제출자료에 대하여 부서를 기관으로 초기화처리하는 메소드 * @param reportId - 제출자료ID * @param organId - 기관ID */ public void procInitialForReport(Long reportId, MDept organId) { TotReport report = pm.getObjectById(TotReport.class, reportId); report.clearAttachment(); report.setData(null); report.setProcess(TotReportProcess.values()[MReport.ACCEPT_LIST]); report.setNote(null); report.setUser(null); report.setDept(organId); pm.makePersistent(report); } /** * 제출자료에 대하여 초기화처리하는 메소드 * @param reportId - 제출자료ID */ public void procInitialForReport(Long reportId,Long docId) { TotReport report = pm.getObjectById(TotReport.class, reportId); //20170919 부서초기화시 첨부파일경로 삭제 by wonseok Lee updateAttachmentsPath(docId,reportId,report.getDept()); report.clearAttachment(); report.setData(null); report.setProcess(TotReportProcess.values()[MReport.ACCEPT_LIST]); report.setNote(null); report.setUser(null); pm.makePersistent(report); } /** * 부서초기화 시 기관코드로 업데이트 17.06.20 by jskim * 제출자료에 대하여 부서를 기관으로 초기화처리하는 메소드 * @param reportId - 제출자료ID * @param organId - 기관ID */ public void procInitialForReport(Long reportId, MDept organId,Long docId) { TotReport report = pm.getObjectById(TotReport.class, reportId); //20170919 부서초기화시 첨부파일경로 삭제 by wonseok Lee updateAttachmentsPath(docId,reportId,report.getDept()); report.clearAttachment(); report.setData(null); report.setProcess(TotReportProcess.values()[MReport.ACCEPT_LIST]); report.setNote(null); report.setUser(null); report.setDept(organId); pm.makePersistent(report); } /** * 제출자료 초기화시 첨부파일 PATH를 reset(null) 한다. by wonseok Lee 20170919 * @param reportId - 제출자료ID * @참조페이지 - report_modify_temp.jsp * */ public void updateAttachmentsPath(Long docId,Long reportId,String deptId){ CommonDBManager acdbm = new CommonDBManager(); try { StringBuilder sql = new StringBuilder(250); sql.delete(0, sql.length()); sql.append("UPDATE MOUMI_TOT_REPORT SET ATTACHMENTS_PATH = null WHERE DEL_TYPE = 'N' and ID = ? "); acdbm.execUpdate(sql.toString(), reportId); acdbm.pstmt.close(); sql.delete(0, sql.length()); sql.append("UPDATE MOUMI_TOT_REPORT_VERSION SET ATTACHMENTS_PATH = null WHERE REPORT_ID = ? AND ID=(SELECT MAX(ID) FROM MOUMI_TOT_REPORT_VERSION WHERE DEL_TYPE = 'N' AND REPORT_ID = ?)"); acdbm.execUpdate(sql.toString(),reportId, reportId); acdbm.pstmt.close(); acdbm.commit(); String attacheFileDir = MoumiConfig.getFileDirectory()+"/"+docId+"/"+reportId+"/"+deptId+"/"; LOG.debug("attacheFileDir["+attacheFileDir+"]"); File Dir = new File(attacheFileDir); if(Dir.isDirectory()){ File[] files = Dir.listFiles(); if(files.length>0){ for(File f: files){ if(f.isFile()) { f.delete(); LOG.debug("f.getName()["+f.getName()+"]"); } }//end of for } } } catch (Exception ex) { acdbm.rollback(); ex.printStackTrace(); }finally{ acdbm.execClose(); } } /** * 제출자료에 대하여 회수요청을 처리하는 메소드 * 제출자료에 대한 집계자료의 자동회수여부를 확인하여 * 자동이면 제출자료의 진행상태를 3.재입력요청으로 변경하고 * 수동이면 제출자료의 진행상태를 14.회수요청으로 변경한다. * * @param reportId - 제출자료ID * @param prss - 제출자료의 진행상태(3.재입력요청, 14.회수요청) * @param note - 회수요청사유 * @참조페이지 - popup_recall_note.jsp */ public void procRecallReport(Long reportId, TotReportProcess prss, String note, MUser mUser) { TotReport report = pm.getObjectById(TotReport.class, reportId); report.setProcess(prss); report.setNote(note); report.setUser(mUser); pm.makePersistent(report); } /** * 제출자료에 대한 담당자를 변경하는 메소드 * * @param reportIds - Long Array 제출자료id * @param fromUser - String 원담당자 * @param toUser - String 변경담당자 */ public void procChangeOwnerForReport(Long[] reportIds, String fromUser, String toUser) { MUser mFromUser = pm.getObjectById(MUser.class, fromUser); MUser mToUser = pm.getObjectById(MUser.class, toUser); for (Long reportId : reportIds) { procChangeOwnerForReport(reportId, mFromUser, mToUser); } } /** * 제출자료에 대한 담당자를 변경하는 메소드 * * @param reportIds - String Array 제출자료id * @param fromUser - String 원담당자 * @param toUser - String 변경담당자 */ public void procChangeOwnerForReport(String[] reportIds, String fromUser, String toUser) { MUser mFromUser = pm.getObjectById(MUser.class, fromUser); MUser mToUser = pm.getObjectById(MUser.class, toUser); for (String reportId : reportIds) { procChangeOwnerForReport(new Long(reportId), mFromUser, mToUser); } } /** * 제출자료에 대한 담당자를 변경하는 메소드 * * @param reportId - Long 제출자료id * @param fromUser - String 원담당자 * @param toUser - String 변경담당자 */ public void procChangeOwnerForReport(Long reportId, String fromUser, String toUser) { procChangeOwnerForReport(reportId, pm.getObjectById(MUser.class, fromUser), pm.getObjectById(MUser.class, toUser)); } /** * 제출자료에 대한 담당자를 변경하는 메소드 * * @param reportId - Long 제출자료id * @param fromUser - MUser 원담당자 * @param toUser - MUser 변경담당자 */ public void procChangeOwnerForReport(Long reportId, MUser fromUser, MUser toUser) { TotReport totReport = pm.getObjectById(TotReport.class, reportId); totReport.setUser(toUser); MDept dept = toUser.getUpperDept(pm); totReport.setDept(dept); if (totReport.getSlaveTotDoc() != null) { TotDoc totDoc = pm.getObjectById(TotDoc.class, totReport.getSlaveTotDoc().getId()); totDoc.setUser(toUser); pm.makePersistent(totDoc); } pm.makePersistent(totReport); UserHistory userHistory = new UserHistory(totReport.getTotDoc(), fromUser, toUser, dept); 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("", reportId, 'R'); //문서 구분(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. * @return 접수 자료 건수 */ public int getTotReportCount(String id, String dept) { // query 파라미터 map 클래스 선언. Map params = new HashMap(); params.put("id", id); params.put("dept", dept); // 사용자의 grade, organ, upper_dept 조회. StringBuilder sbDept = new StringBuilder(); sbDept.append("SELECT nvl(grade,0) grade, organ, upper_dept upperDept 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()); params.put("organ", deptList.get(0).getOrgan()); params.put("upperDept", deptList.get(0).getUpperDept()); //System.out.println("====> id : " + params.get("id")); //System.out.println("====> dept : " + params.get("dept")); //System.out.println("====> grade : " + params.get("grade")); //System.out.println("====> organ : " + params.get("organ")); //System.out.println("====> upperDept : " + params.get("upperDept")); StringBuilder sbQuery = new StringBuilder(); //if(params.get("grade") == null) { if(params.get("grade").equals(0)) { // grade 가 null 인 경우 쿼리에서 nvl 하여 0으로 변경하여 조회. sbQuery.append("SELECT COUNT(*) FROM MOUMI_TOT_REPORT RT, MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC WHERE RT.DOC_ID=DC.ID AND DC.DEL_TYPE='N' \n"); sbQuery.append("AND RT.DEL_TYPE='N' AND RT.DOC_TYPE=0 AND RT.PRSS IN (0,1,2,3) \n"); sbQuery.append("AND (DC.END_DATE IS NOT NULL AND DC.END_DATE > SYSDATE) \n"); sbQuery.append("AND DC.START_DATE < SYSDATE \n"); sbQuery.append("AND DC.ACPT_LIMIT = AC.ID \n"); sbQuery.append("AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) \n"); //sbQuery.append("AND (((RT.DEPT_ID='"+dept+"' OR RT.DEPT_ID='"+organ+"' OR RT.DEPT_ID='"+upper+"') and rt.user_id is null) or (rt.user_id = ('"+id+"'))) \n"); sbQuery.append("AND (((RT.DEPT_ID=:dept OR RT.DEPT_ID=:organ OR RT.DEPT_ID=:upperDept) and rt.user_id is null) or (rt.user_id = (:id))) \n"); //} else if(params.get("grade") != null) { } else if(!params.get("grade").equals(0)) { // grade 가 null 이 아닌 경우. sbQuery.append("SELECT COUNT(*) FROM MOUMI_TOT_REPORT RT, MOUMI_TOT_DOC DC, MOUMI_ACPT_LIMIT_STRATEGY AC WHERE RT.DOC_ID=DC.ID AND DC.DEL_TYPE='N' \n"); sbQuery.append("AND RT.DEL_TYPE='N' AND RT.DOC_TYPE=0 AND RT.PRSS IN (0,1,2,3) \n"); sbQuery.append("AND (DC.END_DATE IS NOT NULL AND DC.END_DATE>SYSDATE) \n"); sbQuery.append("AND DC.START_DATE < SYSDATE \n"); sbQuery.append("AND DC.ACPT_LIMIT = AC.ID \n"); sbQuery.append("AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) \n"); //sbQuery.append("AND ((rt.dept_id in ('"+dept+"') and rt.user_id is null) or (rt.user_id = ('"+id+"'))) \n"); sbQuery.append("AND ((rt.dept_id in (:dept) and rt.user_id is null) or (rt.user_id = (:id))) \n"); } Query query = pm.newQuery("javax.jdo.query.SQL",sbQuery.toString()); // System.out.println("====> getTotReportCount : " + sbQuery.toString()); List list = (List) query.executeWithMap(params); int totReportCount = (Integer) list.get(0); // System.out.println("reportCnt::::::::::" + totReportCount); return totReportCount; } /** * 집계자료에서 결재대기중(진행), 결재완료 되었거나 연계등록 상태인 제출자료의 갯수를 조회한다. * @param docID : 집계자료의 문서 ID. * @return 결제진행, 결제완료 되었거나 연계등록 상태인 제출자료를 카운트한 값을 리턴한다. */ public int getUnModifiableTotReportCount(Long docID) { int count = 0; Map params = new HashMap(); params.put("docID", docID); params.put("prssApprove", TotReportProcess.APPROVE.ordinal()); // 결재대기중 params.put("prssComp", TotReportProcess.COMP.ordinal()); // 결재완료 StringBuilder sbQuery = new StringBuilder(); sbQuery.append("select count(1) \n"); sbQuery.append("from moumi_tot_report \n"); sbQuery.append("where \n"); sbQuery.append(" doc_id = :docID \n"); sbQuery.append(" and ( \n"); sbQuery.append(" prss in ( :prssApprove, :prssComp ) \n"); //연계등록도 수정가능하게 //sbQuery.append(" or down_doc_id is not null \n"); // 연계등록 상태이면 null 이 아니다.(moumi_tot_doc 의 id 가 저장된다.) sbQuery.append(" ) \n"); sbQuery.append(" and del_type = 'N' \n"); // System.out.println("====> getTotReportApproveCompCount sbQuery : \n" + sbQuery.toString()); Query query = pm.newQuery("javax.jdo.query.SQL",sbQuery.toString()); List list = (List) query.executeWithMap(params); count = ((BigDecimal)list.get(0)).intValue(); //System.out.println("====> count : " + count); return count; } /** * 제출자료에서 결재대기중(진행), 결재완료된 제출자료의 갯수를 조회한다. * @param id : 제출자료의 문서 ID. * @return 결제진행, 결제완료된 제출자료를 카운트한 값을 리턴한다. */ public int getUnModifiableCountByReportId(Long id) { int count = 0; Map params = new HashMap(); params.put("id", id); params.put("prssApprove", TotReportProcess.APPROVE.ordinal()); // 결재대기중 params.put("prssComp", TotReportProcess.COMP.ordinal()); // 결재완료 StringBuilder sbQuery = new StringBuilder(); sbQuery.append("select count(1) \n"); sbQuery.append(" from moumi_tot_report \n"); sbQuery.append(" where id = :id \n"); sbQuery.append(" and prss in ( :prssApprove, :prssComp ) \n"); sbQuery.append(" and del_type = 'N' \n"); Query query = pm.newQuery("javax.jdo.query.SQL",sbQuery.toString()); List list = (List) query.executeWithMap(params); count = ((BigDecimal)list.get(0)).intValue(); //LOG.debug("TotReportService.getUnModifiableCountByReportId ======================\nquery=\n{}\n===============================", sbQuery.toString()); //LOG.debug("params={}", params); //LOG.debug("count={}", count); return count; } }