/* * 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.Map; import javax.jdo.PersistenceManager; import kr.co.kihyun.lang.MString; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * * @author KWON,HAN */ public class TotDocReportService extends BaseService { /** * 기본생성자 */ public TotDocReportService() { } /** * 생성자 * * @param pm PersistenceManager */ public TotDocReportService(PersistenceManager pm) { super(pm); } private static final Logger LOG = LoggerFactory.getLogger(TotDocReportService.class); /** * 검색조건쿼리와 레코드개수조회쿼리를 생성하는 메소드(제출완료) * * @param params - 검색조건맵 * - userId * - deptId * - findOption * - filterValue * @param docType - ALL:전체, PRSS:집계진행자료, REG:미진행자료, END:종료자료, * @param exeType - A:전체, D:집계자료, R:제출자료 * @참조페이지 - doc_list.jsp */ public void createDocReportListSearchQuery(Map params, String docType, String exeType ) { 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"); // --------------------------------------------------------------------- // 집계자료 // --------------------------------------------------------------------- sbSearch.append(" SELECT X.* FROM ( \n"); sbSearch.append(" SELECT \n"); sbSearch.append(" '1' AS orderIndex \n"); sbSearch.append(" , 'D' AS docReportType \n"); sbSearch.append(" , CASE WHEN DC.START_DATE <= CURRENT DATE \n"); sbSearch.append(" AND DC.END_DATE >= CURRENT DATE \n"); sbSearch.append(" AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID)) THEN '1.1.PRSS' \n"); sbSearch.append(" WHEN ( 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 >= CURRENT DATE AND (AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT <> FN_SNDCNT(DC.ID)) ) THEN '1.2.REG' \n"); sbSearch.append(" WHEN AC.PRSS IN (4,2) \n"); sbSearch.append(" AND (DC.END_DATE <= CURRENT DATE OR AC.SUBMIT_LIMIT_CNT > 0 AND AC.SUBMIT_LIMIT_CNT <= FN_SNDCNT(DC.ID)) THEN '1.3.END' \n"); sbSearch.append(" ELSE '1.4.undefined' \n"); sbSearch.append(" END AS docReportSatus \n"); sbSearch.append(" , 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(" , DC.PERIODICAL_DOC_YN AS periodicalDocYn \n"); sbSearch.append(" , DC.APPEND_TYPE AS appendType \n"); sbSearch.append(" , FN_SNDCNT(DC.ID)||'/'||FN_ALLCNT(DC.ID)||'('||FN_NOTECNT(DC.ID)||')' AS reportDeptName \n"); sbSearch.append(" , ' ' AS reportUserName \n"); sbSearch.append(" , NULL AS submitDate \n"); sbSearch.append(" , 0 AS downDocId \n"); sbSearch.append(" , 0 AS reportPrss \n"); sbSearch.append(" , 0 AS appro \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"); sbSearch.append(" AND ( DC.USER_ID = :userId ) \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.DEPT_ID = DT.ID \n"); //sbSearch.append(" --AND DT.ORGAN = GR.ID \n"); sbSearch.append(" AND DC.ACPT_LIMIT = AC.ID \n"); if (!MString.isNull(docType)) { if (docType.equals("PRSS")) { sbSearch.append(" AND DC.START_DATE <= CURRENT DATE \n"); sbSearch.append(" AND DC.END_DATE >= CURRENT DATE \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 >= CURRENT DATE 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 <= CURRENT DATE OR AC.SUBMIT_LIMIT_CNT > 0 AND AC.SUBMIT_LIMIT_CNT <= FN_SNDCNT(DC.ID) ) \n"); //자료를 전체를 선택하고 제출의 상태를 선택한 경우 ... } else if (!"ALL".equals(docType)) { sbSearch.append(" AND 1=2 \n"); } } if (!MString.isNull(exeType)) { sbSearch.append(" AND ( 'D' = '" + exeType + "' OR 'A' = '" + exeType + "' ) \n"); } // --------------------------------------------------------------------- // 제출자료 // --------------------------------------------------------------------- // TotReportService.createAcceptReportListSearchQuery() : 접수자료 // TotReportService.createProcessingReportListSearchQuery() : 제출진행자료 // TotReportService.createReportListSearchQuery() : 제출완료 // --------------------------------------------------------------------- String reportType = docType; sbSearch.append(" UNION ALL \n"); sbSearch.append(" SELECT \n"); sbSearch.append(" '2' AS orderIndex \n"); sbSearch.append(" , 'R' AS docReportType \n"); sbSearch.append(" , CASE WHEN RT.PRSS IN (0,1,2,3) \n"); sbSearch.append(" AND DC.START_DATE <= CURRENT DATE \n"); sbSearch.append(" AND DC.END_DATE >= CURRENT DATE \n"); sbSearch.append(" AND ( AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID) ) THEN '2.1.RETURN' \n"); sbSearch.append(" WHEN RT.PRSS IN (4,5) THEN '2.2.TEMP' \n"); sbSearch.append(" WHEN RT.PRSS IN (6,12,14) THEN '2.3.COMP' \n"); sbSearch.append(" ELSE '2.4.undefined' \n"); sbSearch.append(" END AS docReportSatus \n"); //sbSearch.append(" , DC.USER_ID AS userId \n"); //sbSearch.append(" , DC.DEPT_ID AS deptId \n"); //sbSearch.append(" --, DC.ID \n"); sbSearch.append(" , RT.USER_ID AS userId \n"); sbSearch.append(" , RT.DEPT_ID AS deptId \n"); sbSearch.append(" , RT.ID \n"); sbSearch.append(" , COALESCE(DC.EXEC_CODE,' ') AS execCode \n"); sbSearch.append(" , DC.NAME /* AS docName */ \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(" , DC.PERIODICAL_DOC_YN AS periodicalDocYn \n"); sbSearch.append(" , DC.APPEND_TYPE AS appendType \n"); sbSearch.append(" , DT2.NAME AS reportDeptName \n"); sbSearch.append(" , SP_CM_DEC_FU(RT.OWNER_NAME) AS reportUserName \n"); sbSearch.append(" , RT.SUBMIT_DATE AS submitDate \n"); sbSearch.append(" , NVL(RT.DOWN_DOC_ID,0) AS downDocId \n"); sbSearch.append(" , RT.PRSS AS reportPrss \n"); sbSearch.append(" , RT.APPRO \n"); //+ //sbSearch.append(" --, RT.USER_ID AS userId \n"); //+ //sbSearch.append(" --, AC.ACPT_TYPE AS docAcptType \n"); //+ //sbSearch.append(" --, RT.SECU_YN AS secuYn \n"); //+ //sbSearch.append(" --, ROWNUMBER() OVER() AS rowNo \n"); sbSearch.append(" FROM MOUMI_TOT_DOC DC \n"); sbSearch.append(" , MOUMI_TOT_REPORT RT \n"); sbSearch.append(" , MOUMI_DEPT DT2 \n"); sbSearch.append(" , MOUMI_DEPT DT \n"); sbSearch.append(" , MOUMI_ACPT_LIMIT_STRATEGY AC \n"); // if (!MString.isNull(reportType)) { // if (docType.equals("RETURN")) { // 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(" FROM 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 RT.DEL_TYPE = 'N' \n"); sbSearch.append(" AND ( RT.USER_ID = :userId ) \n"); sbSearch.append(" AND RT.DEPT_ID = DT2.ID \n"); sbSearch.append(" AND RT.DOC_ID = DC.ID \n"); sbSearch.append(" AND DC.DEL_TYPE = 'N' \n"); sbSearch.append(" AND DC.DEPT_ID = DT.ID \n"); sbSearch.append(" AND DC.ACPT_LIMIT = AC.ID \n"); if (!MString.isNull(findOption) && !MString.isNull(filterValue)) { //if (findOption.equals("totDoc.name")) { if (findOption.equals("name")) { sbSearch.append(" AND DC.NAME LIKE '%' || :filterValue || '%' \n"); //} else if (findOption.equals("totDoc.executionCode")) { } 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"); } else if (findOption.equals("totDoc.mUser.name2")) { sbSearch.append(" AND SP_CM_DEC_FU(RT.OWNER_NAME) LIKE '%' || :filterValue || '%' \n"); } } // if (!MString.isNull(reportType)) { // if (docType.equals("RETURN")) { // 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(" ) \n"); // } // } if (!MString.isNull(reportType)) { if ("ALL".equals(reportType)) { sbSearch.append(" AND ( \n"); sbSearch.append(" ( RT.PRSS IN (0,1,2,3) \n"); sbSearch.append(" AND DC.START_DATE <= CURRENT DATE \n"); sbSearch.append(" AND DC.END_DATE >= CURRENT DATE \n"); sbSearch.append(" AND ( AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID) ) \n"); sbSearch.append(" ) \n"); sbSearch.append(" OR ( RT.PRSS IN (4,5) ) \n"); sbSearch.append(" OR ( RT.PRSS IN (6,12,14) ) \n"); sbSearch.append(" ) \n"); } else if (reportType.equals("RETURN")) { sbSearch.append(" AND RT.PRSS IN (0,1,2,3) \n"); sbSearch.append(" AND DC.START_DATE <= CURRENT DATE \n"); sbSearch.append(" AND DC.END_DATE >= CURRENT DATE \n"); sbSearch.append(" AND ( AC.SUBMIT_LIMIT_CNT = 0 OR AC.SUBMIT_LIMIT_CNT > FN_SNDCNT(DC.ID) ) \n"); } else if (reportType.equals("TEMP")) { sbSearch.append(" AND RT.PRSS IN (4,5) \n"); //sbSearch.append(" --AND RT.DEPT_ID = :deptId \n"); //인사이동이 있으면 현재부서로는 조회할 수 없다 } else if (reportType.equals("COMP")) { sbSearch.append(" AND RT.PRSS IN (6,12,14) \n"); //자료를 전체를 선택하고 집계의 상태를 선택한 경우 ... } else if (!"ALL".equals(reportType)) { sbSearch.append(" AND 1=2 \n"); } } if (!MString.isNull(exeType)) { sbSearch.append(" AND ( 'R' = '" + exeType + "' OR 'A' = '" + exeType + "' ) \n"); } //레코드갯수를 구하는 부모클래스의 함수를 호출하기 위해서 멤버변수에 저장 //페이징처리함수 호출전에 등록 필요. this.setCountQuery(sbSearch); this.setParams(params); sbSearch.append(" ) X \n"); //sbSearch.append("--ORDER BY 1, 3, 6 DESC \n"); //1:orderIndex, 3:docReportSatus, 6:DC.ID or RT.ID sbSearch.append(" ORDER BY X.orderIndex \n"); sbSearch.append(" , X.docReportSatus \n"); sbSearch.append(" , CASE WHEN X.docReportSatus != '2.1.RETURN' \n"); sbSearch.append(" AND X.docReportSatus != '2.2.TEMP' \n"); sbSearch.append(" AND X.docReportSatus != '2.3.COMP' THEN X.id ELSE NULL END DESC \n"); sbSearch.append(" , CASE WHEN X.docReportSatus = '2.1.RETURN' THEN X.endDate ELSE NULL END ASC \n"); sbSearch.append(" , CASE WHEN X.docReportSatus = '2.1.RETURN' THEN X.id ELSE NULL END ASC \n"); sbSearch.append(" , CASE WHEN X.docReportSatus = '2.2.TEMP' THEN ( CASE WHEN X.endDate < SYSDATE THEN 0 ELSE 1 END ) ELSE NULL END DESC \n"); sbSearch.append(" , CASE WHEN X.docReportSatus = '2.2.TEMP' THEN X.endDate ELSE NULL END ASC \n"); sbSearch.append(" , CASE WHEN X.docReportSatus = '2.3.COMP' THEN ( CASE WHEN X.submitDate IS NULL THEN 0 ELSE 1 END ) ELSE NULL END DESC \n"); sbSearch.append(" , CASE WHEN X.docReportSatus = '2.3.COMP' THEN X.submitDate ELSE NULL END DESC \n"); //페이징처리쿼리생성함수 호출 this.setSearchQuery(sbSearch); if( LOG.isDebugEnabled() ) { LOG.debug("DocReportService.createDocReportListSearchQuery ======================\nquery=\n{}\n===============================", sbSearch.toString()); LOG.debug("params={}", params); } } }