jskim
/
moumiKnu
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
knu project
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
4 Commits
1 Branch
0 Tags
69 MiB
 
 
 
 
 
 
Tag: Branch: Tree: main
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'main'
${ noResults }
moumiKnu/src_bak/kr/co/kihyun/service/TotDocReportService.java

318 lines
25 KiB
Raw Permalink Blame History

/*
* 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,'&nbsp;') 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(" , '&nbsp;' 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,'&nbsp;') 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);
}
}
}