/* * 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.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import java.util.List; import java.util.Map; import javax.jdo.PersistenceManager; import javax.jdo.Query; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * * @author Kts */ public class StatisticsService extends BaseService { private static final Logger LOG = LoggerFactory.getLogger(StatisticsService.class); /** * 기본생성자 */ public StatisticsService() {} /** * 생성자 * @param pm PersistenceManager */ public StatisticsService(PersistenceManager pm) { super(pm); } /** * 기간별(일간,월간,년간)로 조회 시 사용되는 함수 * @param params Map - 검색조건(startDate, endDate) * @return List - 결과목록 * @throws Exception * @참조페이지 - statistics.jsp */ public List getStatisticsByDate(Map params) throws Exception{ StringBuilder sbSearch = new StringBuilder(); String dateType = (String)params.get("dateType"); String stDate = (String)params.get("stDate"); String edDate = (String)params.get("edDate"); String organId = (String)params.get("organId"); String deptId = (String)params.get("deptId"); String deptSchoolType = (String)params.get("deptSchoolType"); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); SimpleDateFormat monthFormat = new SimpleDateFormat("yyyy-MM"); SimpleDateFormat yearFormat = new SimpleDateFormat("yyyy"); Date startDate = dateFormat.parse(stDate); Date endDate = dateFormat.parse(edDate); sbSearch.append(" SELECT \n"); sbSearch.append(" DD.YMD \n"); sbSearch.append(" , NVL(SS.PRSS_STATUS, 0) AS PRSS_STATUS /* 집계진행 */ \n"); sbSearch.append(" , NVL(SS.END_STATUS, 0) AS END_STATUS /* 집계종료 */ \n"); sbSearch.append(" , NVL(MM.ACCEPT, 0) AS ACCEPT /* 접수 */ \n"); sbSearch.append(" , NVL(MM.TMP, 0) AS TMP /* 제출진행 */ \n"); sbSearch.append(" , NVL(MM.COMP, 0) AS COMP /* 제출완료 */ \n"); sbSearch.append(" FROM \n"); sbSearch.append(" ( \n"); sbSearch.append(" SELECT \n"); if( dateType.equals("day") ) { sbSearch.append(" TO_CHAR(DOC.START_DATE, 'YYYY-MM-DD') AS YMD \n"); }else if( dateType.equals("month") ) { sbSearch.append(" TO_CHAR(DOC.START_DATE, 'YYYY-MM') AS YMD \n"); }else if( dateType.equals("year") ) { sbSearch.append(" TO_CHAR(DOC.START_DATE, 'YYYY') AS YMD \n"); } sbSearch.append(" , NVL(SUM(CASE WHEN REP.TYPE = 0 \n"); sbSearch.append(" AND REP.PRSS IN ( 0, 3 ) \n"); sbSearch.append(" AND DOC.STATUS = 'P' /* 집계진행 */ \n"); sbSearch.append(" THEN \n"); sbSearch.append(" 1 \n"); sbSearch.append(" END), 0) AS ACCEPT \n"); sbSearch.append(" , NVL(SUM( \n"); sbSearch.append(" CASE WHEN REP.TYPE = 0 \n"); sbSearch.append(" AND REP.PRSS = 4 \n"); sbSearch.append(" AND DOC.STATUS = 'P' /* 집계진행 */ \n"); sbSearch.append(" THEN \n"); sbSearch.append(" 1 \n"); sbSearch.append(" END), 0) AS TMP \n"); sbSearch.append(" , NVL(SUM( \n"); sbSearch.append(" CASE WHEN REP.TYPE = 0 \n"); sbSearch.append(" AND REP.PRSS IN ( 6, 12 ) \n"); sbSearch.append(" THEN \n"); sbSearch.append(" 1 \n"); sbSearch.append(" END), 0) AS COMP \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_REPORT REP \n"); sbSearch.append(" , ( \n"); sbSearch.append(" SELECT \n"); sbSearch.append(" DOC.ID \n"); sbSearch.append(" ,DOC.START_DATE \n"); sbSearch.append(" ,CASE WHEN ACPT.ACPT_TYPE = 'UNLIMIT' THEN /* 수동종료 */ \n"); sbSearch.append(" CASE WHEN ACPT.PRSS = 4 \n"); sbSearch.append(" OR ( DOC.END_DATE < SYSDATE AND ACPT.PRSS IN ( 1, 2 ) ) THEN \n"); sbSearch.append(" 'E' /* 집계종료 */ \n"); sbSearch.append(" ELSE \n"); sbSearch.append(" CASE WHEN SYSDATE BETWEEN DOC.START_DATE AND DOC.END_DATE AND ACPT.PRSS IN ( 1, 2 ) THEN \n"); sbSearch.append(" 'P' /* 집계진행 */ \n"); sbSearch.append(" END \n"); sbSearch.append(" END \n"); sbSearch.append(" WHEN ACPT.ACPT_TYPE = 'SUBMIT_DATE' THEN /* 선착순종료 */ \n"); sbSearch.append(" CASE WHEN ACPT.PRSS = 4 \n"); sbSearch.append(" OR (ACPT.SUBMIT_LIMIT_CNT <= \n"); sbSearch.append(" (SELECT \n"); sbSearch.append(" COUNT(ID) AS CNT \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_REPORT \n"); sbSearch.append(" WHERE \n"); sbSearch.append(" DOC_ID = DOC.ID \n"); sbSearch.append(" AND DEL_TYPE = 'N' \n"); sbSearch.append(" AND TYPE = 0 \n"); sbSearch.append(" AND PRSS IN (6, 7, 12) \n"); sbSearch.append(" ) \n"); sbSearch.append(" ) \n"); sbSearch.append(" THEN \n"); sbSearch.append(" 'E' /* 집계종료 */ \n"); sbSearch.append(" ELSE \n"); sbSearch.append(" CASE WHEN SYSDATE BETWEEN DOC.START_DATE AND DOC.END_DATE AND ACPT.PRSS IN ( 1, 2 ) THEN \n"); sbSearch.append(" 'P' /* 집계진행 */ \n"); sbSearch.append(" END \n"); sbSearch.append(" END \n"); sbSearch.append(" WHEN ACPT.ACPT_TYPE = 'DEADLINE' THEN /* 종료일지정 */ \n"); sbSearch.append(" CASE WHEN ACPT.PRSS = 4 \n"); sbSearch.append(" OR ( DOC.END_DATE < SYSDATE AND ACPT.PRSS = 2 ) THEN \n"); sbSearch.append(" 'E' /* 집계종료 */ \n"); sbSearch.append(" ELSE \n"); sbSearch.append(" CASE WHEN SYSDATE BETWEEN DOC.START_DATE AND DOC.END_DATE AND ACPT.PRSS IN ( 1, 2 ) THEN \n"); sbSearch.append(" 'P' /* 집계진행 */ \n"); sbSearch.append(" END \n"); sbSearch.append(" END \n"); sbSearch.append(" END AS STATUS \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_DOC DOC \n"); sbSearch.append(" , MOUMI_ACPT_LIMIT_STRATEGY ACPT \n"); sbSearch.append(" WHERE \n"); sbSearch.append(" DOC.ACPT_LIMIT = ACPT.ID \n"); sbSearch.append(" AND DOC.GROUP_ID = 2 \n"); sbSearch.append(" AND DOC.DEL_TYPE = 'N' \n"); sbSearch.append(" AND TO_CHAR(DOC.START_DATE,'YYYY-MM-DD HH24:MI:SS') BETWEEN (:startDate || ' 00:00:00') \n"); sbSearch.append(" AND ( :endDate || ' 23:59:59' ) \n"); sbSearch.append(" ) DOC \n"); if( !organId.equals("ALL") && deptId.equals("ALL") ) { sbSearch.append(" , MOUMI_DEPT DEP \n"); } sbSearch.append(" WHERE \n"); sbSearch.append(" REP.DOC_ID = DOC.ID \n"); if( !organId.equals("ALL") && deptId.equals("ALL") ) { sbSearch.append(" AND REP.DEPT_ID = DEP.ID \n"); } sbSearch.append(" AND REP.DEL_TYPE = 'N' \n"); sbSearch.append(" AND REP.PRSS IN (0, 3, 4, 6, 12) \n"); if( !deptId.equals("ALL") ) { //부서 및 학교로 특정됨 sbSearch.append(" AND REP.DEPT_ID = :deptId \n"); }else{ if( !organId.equals("ALL") ) { //기관(교육청) sbSearch.append(" AND DEP.ORGAN = :organId \n"); if( deptSchoolType.equals("D") ) { //부서 sbSearch.append(" AND DEP.GRADE IS NULL \n"); }else if( deptSchoolType.equals("S") ) { //학교 sbSearch.append(" AND DEP.GRADE IS NOT NULL \n"); } } } sbSearch.append(" GROUP BY \n"); if( dateType.equals("day") ) { sbSearch.append(" TO_CHAR(DOC.START_DATE, 'YYYY-MM-DD') \n"); }else if( dateType.equals("month") ) { sbSearch.append(" TO_CHAR(DOC.START_DATE, 'YYYY-MM') \n"); }else if( dateType.equals("year") ) { sbSearch.append(" TO_CHAR(DOC.START_DATE, 'YYYY') \n"); } sbSearch.append(" ) MM \n"); sbSearch.append(" RIGHT OUTER JOIN \n"); /* db2 에서 사용 */ // // sbSearch.append(" ( SELECT \n"); // sbSearch.append(" YMD \n"); // sbSearch.append(" FROM \n"); // sbSearch.append(" TABLE(VALUES \n"); /* db2 에서 사용 */ Calendar cal = Calendar.getInstance(); // int ymd = Calendar.DATE; String tempFormat = null; // // int loop = 0; if( dateType.equals("day") ) { ymd = Calendar.DATE; tempFormat = "yyyy-mm-dd"; }else if( dateType.equals("month") ) { ymd = Calendar.MONTH; tempFormat = "yyyy-mm"; }else if( dateType.equals("year") ) { ymd = Calendar.YEAR; tempFormat = "yyyy"; } /* db2 에서 사용 */ // Date stTempDate = tempFormat.parse(stDate); // Date edTempDate = tempFormat.parse(edDate); // while( stTempDate.before(edTempDate) || stTempDate.equals(edTempDate) ) { // if( loop > 0 ) sbSearch.append(","); // sbSearch.append("'"); // sbSearch.append(tempFormat.format(stTempDate)); // sbSearch.append("'"); // loop++; // cal.setTime(stTempDate); // cal.add(ymd, 1); // stTempDate = cal.getTime(); // } /* db2 에서 사용 */ //sbSearch.append(" /*TABLE(VALUES '2014-02', '2014-03', '2014-04', '2014-05', '2014-06'*/ \n"); //sbSearch.append(" /*TABLE(VALUES '2014-07-01', '2014-07-02', '2014-07-03', '2014-07-04', '2014-07-05'*/ \n"); //sbSearch.append(" /*, '2014-07-06', '2014-07-07', '2014-07-08', '2014-07-09', '2014-07-10'*/ \n"); sbSearch.append(" ( SELECT DISTINCT TO_CHAR(BEGIN_YMD+RNUM, :tempFormat) AS YMD \n"); sbSearch.append(" FROM (SELECT ROWNUM-1 AS RNUM , \n"); sbSearch.append(" TO_DATE(:stDatePeriod, :tempFormat) AS BEGIN_YMD, \n"); sbSearch.append(" TO_DATE(:edDatePeriod, :tempFormat) AS END_YMD \n"); sbSearch.append(" FROM TAB, TAB ) \n"); sbSearch.append(" WHERE BEGIN_YMD+RNUM <= END_YMD \n"); sbSearch.append(" ORDER BY YMD \n"); // sbSearch.append(" ) T1(YMD) \n"); sbSearch.append(" ) DD \n"); sbSearch.append(" ON DD.YMD = MM.YMD \n"); sbSearch.append(" LEFT OUTER JOIN \n"); sbSearch.append(" ( \n"); sbSearch.append(" SELECT \n"); sbSearch.append(" YMD \n"); sbSearch.append(" , NVL(SUM(DECODE(STATUS, 'P', 1)), 0) AS PRSS_STATUS /* 집계진행 */ \n"); sbSearch.append(" , NVL(SUM(DECODE(STATUS, 'E', 1)), 0) AS END_STATUS /* 집계종료 */ \n"); sbSearch.append(" FROM \n"); sbSearch.append(" ( SELECT \n"); if( dateType.equals("day") ) { sbSearch.append(" TO_CHAR(DOC.START_DATE, 'YYYY-MM-DD') AS YMD \n"); }else if( dateType.equals("month") ) { sbSearch.append(" TO_CHAR(DOC.START_DATE, 'YYYY-MM') AS YMD \n"); }else if( dateType.equals("year") ) { sbSearch.append(" TO_CHAR(DOC.START_DATE, 'YYYY') AS YMD \n"); } sbSearch.append(" ,CASE WHEN ACPT.ACPT_TYPE = 'UNLIMIT' THEN /* 수동종료 */ \n"); sbSearch.append(" CASE WHEN ACPT.PRSS = 4 \n"); sbSearch.append(" OR ( DOC.END_DATE < SYSDATE AND ACPT.PRSS IN ( 1, 2 ) ) THEN \n"); sbSearch.append(" 'E' /* 집계종료 */ \n"); sbSearch.append(" ELSE \n"); sbSearch.append(" CASE WHEN SYSDATE BETWEEN DOC.START_DATE AND DOC.END_DATE AND ACPT.PRSS IN ( 1, 2 ) THEN \n"); sbSearch.append(" 'P' /* 집계진행 */ \n"); sbSearch.append(" END \n"); sbSearch.append(" END \n"); sbSearch.append(" WHEN ACPT.ACPT_TYPE = 'SUBMIT_DATE' THEN /* 선착순종료 */ \n"); sbSearch.append(" CASE WHEN ACPT.PRSS = 4 \n"); sbSearch.append(" OR (ACPT.SUBMIT_LIMIT_CNT <= \n"); sbSearch.append(" (SELECT \n"); sbSearch.append(" COUNT(ID) AS CNT \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_REPORT \n"); sbSearch.append(" WHERE \n"); sbSearch.append(" DOC_ID = DOC.ID \n"); sbSearch.append(" AND DEL_TYPE = 'N' \n"); sbSearch.append(" AND TYPE = 0 \n"); sbSearch.append(" AND PRSS IN (6, 7, 12) \n"); sbSearch.append(" ) \n"); sbSearch.append(" ) \n"); sbSearch.append(" THEN \n"); sbSearch.append(" 'E' /* 집계종료 */ \n"); sbSearch.append(" ELSE \n"); sbSearch.append(" CASE WHEN SYSDATE BETWEEN DOC.START_DATE AND DOC.END_DATE AND ACPT.PRSS IN ( 1, 2 ) THEN \n"); sbSearch.append(" 'P' /* 집계진행 */ \n"); sbSearch.append(" END \n"); sbSearch.append(" END \n"); sbSearch.append(" WHEN ACPT.ACPT_TYPE = 'DEADLINE' THEN /* 종료일지정 */ \n"); sbSearch.append(" CASE WHEN ACPT.PRSS = 4 \n"); sbSearch.append(" OR ( DOC.END_DATE < SYSDATE AND ACPT.PRSS = 2 ) THEN \n"); sbSearch.append(" 'E' /* 집계종료 */ \n"); sbSearch.append(" ELSE \n"); sbSearch.append(" CASE WHEN SYSDATE BETWEEN DOC.START_DATE AND DOC.END_DATE AND ACPT.PRSS IN ( 1, 2 ) THEN \n"); sbSearch.append(" 'P' /* 집계진행 */ \n"); sbSearch.append(" END \n"); sbSearch.append(" END \n"); sbSearch.append(" END AS STATUS \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_DOC DOC \n"); sbSearch.append(" , MOUMI_ACPT_LIMIT_STRATEGY ACPT \n"); if( !organId.equals("ALL") && deptId.equals("ALL") ) { sbSearch.append(" , MOUMI_DEPT DEP \n"); } sbSearch.append(" WHERE \n"); sbSearch.append(" DOC.ACPT_LIMIT = ACPT.ID \n"); if( !organId.equals("ALL") && deptId.equals("ALL") ) { sbSearch.append(" AND DOC.DEPT_ID = DEP.ID \n"); } sbSearch.append(" AND DOC.GROUP_ID = 2 \n"); sbSearch.append(" AND DOC.DEL_TYPE = 'N' \n"); sbSearch.append(" AND TO_CHAR(DOC.START_DATE,'YYYY-MM-DD HH24:MI:SS') BETWEEN (:startDate || ' 00:00:00' ) \n"); sbSearch.append(" AND ( :endDate || ' 23:59:59') \n"); if( !deptId.equals("ALL") ) { //부서 및 학교로 특정됨 sbSearch.append(" AND DOC.DEPT_ID = :deptId \n"); }else{ if( !organId.equals("ALL") ) { //기관(교육청) sbSearch.append(" AND DEP.ORGAN = :organId \n"); if( deptSchoolType.equals("D") ) { //부서 sbSearch.append(" AND DEP.GRADE IS NULL \n"); }else if( deptSchoolType.equals("S") ) { //학교 sbSearch.append(" AND DEP.GRADE IS NOT NULL \n"); } } } sbSearch.append(" ) \n"); sbSearch.append(" GROUP BY YMD \n"); sbSearch.append(" ORDER BY YMD ASC \n"); sbSearch.append(" ) SS \n"); sbSearch.append(" ON DD.YMD = SS.YMD \n"); if( dateType.equals("month") ) { cal.setTime(startDate); cal.set(Calendar.DATE, cal.getActualMinimum(Calendar.DATE)); stDate = dateFormat.format(cal.getTime()); cal.setTime(endDate); cal.set(Calendar.DATE, cal.getActualMaximum(Calendar.DATE)); edDate = dateFormat.format(cal.getTime()); }else if( dateType.equals("year") ) { cal.setTime(startDate); cal.set(Calendar.MONTH, cal.getActualMinimum(Calendar.MONTH)); cal.set(Calendar.DATE, cal.getActualMinimum(Calendar.DATE)); stDate = dateFormat.format(cal.getTime()); cal.setTime(endDate); cal.set(Calendar.MONTH, cal.getActualMaximum(Calendar.MONTH)); cal.set(Calendar.DATE, cal.getActualMaximum(Calendar.DATE)); edDate = dateFormat.format(cal.getTime()); } String stDatePeriod = null; String edDatePeriod = null; if(dateType.equals("day")){ stDatePeriod = stDate; edDatePeriod = edDate; }else if( dateType.equals("month") ) { stDatePeriod = stDate.substring(0, 7); edDatePeriod = edDate.substring(0, 7); }else if(dateType.equals("year")){ stDatePeriod = stDate.substring(0, 4); edDatePeriod = edDate.substring(0, 4); } params.put("startDate", stDate); params.put("endDate", edDate); params.put("stDatePeriod", stDatePeriod); params.put("edDatePeriod", edDatePeriod); params.put("tempFormat", tempFormat); Query query = pm.newQuery("javax.jdo.query.SQL",sbSearch.toString()); if( LOG.isDebugEnabled() ) { LOG.debug("startDate={}, endDate={}", stDate, edDate); LOG.debug("query={}", sbSearch.toString()); } List list = (List)query.executeWithMap(params); return list; } /** * 검색조건을 만족하는 전체결과를 반환하는 함수 * @param params Map - 검색조건(startDate, endDate) * @return List - 결과목록 * @throws Exception * @참조페이지 - statistics.jsp */ public List getStatisticsForAll(Map params) throws Exception{ String organId = (String)params.get("organId"); String deptId = (String)params.get("deptId"); String deptSchoolType = (String)params.get("deptSchoolType"); StringBuilder sbSearch = new StringBuilder(); sbSearch.append(" SELECT \n"); sbSearch.append(" NVL(B.PRSS_STATUS, 0) AS PRSS_STATUS /* 집계진행 */ \n"); sbSearch.append(" , NVL(B.END_STATUS, 0) AS END_STATUS /* 집계종료 */ \n"); sbSearch.append(" , NVL(A.ACCEPT, 0) AS ACCEPT /* 접수 */ \n"); sbSearch.append(" , NVL(A.TMP, 0) AS TMP /* 제출진행 */ \n"); sbSearch.append(" , NVL(A.COMP, 0) AS COMP /* 제출완료 */ \n"); sbSearch.append(" FROM \n"); sbSearch.append(" ( \n"); sbSearch.append(" SELECT \n"); sbSearch.append(" NVL(SUM(CASE WHEN REP.TYPE = 0 \n"); sbSearch.append(" AND REP.PRSS IN ( 0, 3 ) \n"); sbSearch.append(" AND DOC.STATUS = 'P' /* 집계진행 */ \n"); sbSearch.append(" THEN \n"); sbSearch.append(" 1 \n"); sbSearch.append(" END), 0) AS ACCEPT \n"); sbSearch.append(" , NVL(SUM( \n"); sbSearch.append(" CASE WHEN REP.TYPE = 0 \n"); sbSearch.append(" AND REP.PRSS = 4 \n"); sbSearch.append(" AND DOC.STATUS = 'P' /* 집계진행 */ \n"); sbSearch.append(" THEN \n"); sbSearch.append(" 1 \n"); sbSearch.append(" END), 0) AS TMP \n"); sbSearch.append(" , NVL(SUM( \n"); sbSearch.append(" CASE WHEN REP.TYPE = 0 \n"); sbSearch.append(" AND REP.PRSS IN ( 6, 12 ) \n"); sbSearch.append(" THEN \n"); sbSearch.append(" 1 \n"); sbSearch.append(" END), 0) AS COMP \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_REPORT REP \n"); sbSearch.append(" , ( \n"); sbSearch.append(" SELECT \n"); sbSearch.append(" DOC.ID \n"); sbSearch.append(" ,DOC.START_DATE \n"); sbSearch.append(" ,CASE WHEN ACPT.ACPT_TYPE = 'UNLIMIT' THEN /* 수동종료 */ \n"); sbSearch.append(" CASE WHEN ACPT.PRSS = 4 \n"); sbSearch.append(" OR ( DOC.END_DATE < SYSDATE AND ACPT.PRSS IN ( 1, 2 ) ) THEN \n"); sbSearch.append(" 'E' /* 집계종료 */ \n"); sbSearch.append(" ELSE \n"); sbSearch.append(" CASE WHEN SYSDATE BETWEEN DOC.START_DATE AND DOC.END_DATE AND ACPT.PRSS IN ( 1, 2 ) THEN \n"); sbSearch.append(" 'P' /* 집계진행 */ \n"); sbSearch.append(" END \n"); sbSearch.append(" END \n"); sbSearch.append(" WHEN ACPT.ACPT_TYPE = 'SUBMIT_DATE' THEN /* 선착순종료 */ \n"); sbSearch.append(" CASE WHEN ACPT.PRSS = 4 \n"); sbSearch.append(" OR (ACPT.SUBMIT_LIMIT_CNT <= \n"); sbSearch.append(" (SELECT \n"); sbSearch.append(" COUNT(ID) AS CNT \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_REPORT \n"); sbSearch.append(" WHERE \n"); sbSearch.append(" DOC_ID = DOC.ID \n"); sbSearch.append(" AND DEL_TYPE = 'N' \n"); sbSearch.append(" AND TYPE = 0 \n"); sbSearch.append(" AND PRSS IN (6, 7, 12) \n"); sbSearch.append(" ) \n"); sbSearch.append(" ) \n"); sbSearch.append(" THEN \n"); sbSearch.append(" 'E' /* 집계종료 */ \n"); sbSearch.append(" ELSE \n"); sbSearch.append(" CASE WHEN SYSDATE BETWEEN DOC.START_DATE AND DOC.END_DATE AND ACPT.PRSS IN ( 1, 2 ) THEN \n"); sbSearch.append(" 'P' /* 집계진행 */ \n"); sbSearch.append(" END \n"); sbSearch.append(" END \n"); sbSearch.append(" WHEN ACPT.ACPT_TYPE = 'DEADLINE' THEN /* 종료일지정 */ \n"); sbSearch.append(" CASE WHEN ACPT.PRSS = 4 \n"); sbSearch.append(" OR ( DOC.END_DATE < SYSDATE AND ACPT.PRSS = 2 ) THEN \n"); sbSearch.append(" 'E' /* 집계종료 */ \n"); sbSearch.append(" ELSE \n"); sbSearch.append(" CASE WHEN SYSDATE BETWEEN DOC.START_DATE AND DOC.END_DATE AND ACPT.PRSS IN ( 1, 2 ) THEN \n"); sbSearch.append(" 'P' /* 집계진행 */ \n"); sbSearch.append(" END \n"); sbSearch.append(" END \n"); sbSearch.append(" END AS STATUS \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_DOC DOC \n"); sbSearch.append(" , MOUMI_ACPT_LIMIT_STRATEGY ACPT \n"); sbSearch.append(" WHERE \n"); sbSearch.append(" DOC.ACPT_LIMIT = ACPT.ID \n"); sbSearch.append(" AND DOC.GROUP_ID = 2 \n"); sbSearch.append(" AND DOC.DEL_TYPE = 'N' \n"); sbSearch.append(" AND TO_CHAR(DOC.START_DATE,'YYYY-MM-DD HH24:MI:SS') BETWEEN (:startDate || ' 00:00:00') \n"); sbSearch.append(" AND (:endDate || ' 23:59:59') \n"); sbSearch.append(" ) DOC \n"); if( !organId.equals("ALL") && deptId.equals("ALL") ) { sbSearch.append(" , MOUMI_DEPT DEP \n"); } sbSearch.append(" WHERE \n"); sbSearch.append(" REP.DOC_ID = DOC.ID \n"); if( !organId.equals("ALL") && deptId.equals("ALL") ) { sbSearch.append(" AND REP.DEPT_ID = DEP.ID \n"); } sbSearch.append(" AND REP.DEL_TYPE = 'N' \n"); sbSearch.append(" AND REP.PRSS IN (0, 3, 4, 6, 12) \n"); if( !deptId.equals("ALL") ) { //부서 및 학교로 특정됨 sbSearch.append(" AND REP.DEPT_ID = :deptId \n"); }else{ if( !organId.equals("ALL") ) { //기관(교육청) sbSearch.append(" AND DEP.ORGAN = :organId \n"); if( deptSchoolType.equals("D") ) { //부서 sbSearch.append(" AND DEP.GRADE IS NULL \n"); }else if( deptSchoolType.equals("S") ) { //학교 sbSearch.append(" AND DEP.GRADE IS NOT NULL \n"); } } } sbSearch.append(" ) A \n"); sbSearch.append(" , ( \n"); sbSearch.append(" SELECT \n"); sbSearch.append(" NVL(SUM(DECODE(STATUS, 'P', 1)), 0) AS PRSS_STATUS /* 집계진행 */ \n"); sbSearch.append(" , NVL(SUM(DECODE(STATUS, 'E', 1)), 0) AS END_STATUS /* 집계종료 */ \n"); sbSearch.append(" FROM \n"); sbSearch.append(" ( SELECT \n"); sbSearch.append(" CASE WHEN ACPT.ACPT_TYPE = 'UNLIMIT' THEN \n"); sbSearch.append(" CASE WHEN ACPT.PRSS = 4 \n"); sbSearch.append(" OR ( DOC.END_DATE < SYSDATE AND ACPT.PRSS IN ( 1, 2 ) ) THEN \n"); sbSearch.append(" 'E' \n"); sbSearch.append(" ELSE \n"); sbSearch.append(" CASE WHEN SYSDATE BETWEEN DOC.START_DATE AND DOC.END_DATE AND ACPT.PRSS IN ( 1, 2 ) THEN \n"); sbSearch.append(" 'P' \n"); sbSearch.append(" END \n"); sbSearch.append(" END \n"); sbSearch.append(" WHEN ACPT.ACPT_TYPE = 'SUBMIT_DATE' THEN \n"); sbSearch.append(" CASE WHEN ACPT.PRSS = 4 \n"); sbSearch.append(" OR (ACPT.SUBMIT_LIMIT_CNT <= \n"); sbSearch.append(" (SELECT \n"); sbSearch.append(" COUNT(ID) AS CNT \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_REPORT \n"); sbSearch.append(" WHERE \n"); sbSearch.append(" DOC_ID = DOC.ID \n"); sbSearch.append(" AND DEL_TYPE = 'N' \n"); sbSearch.append(" AND TYPE = 0 \n"); sbSearch.append(" AND PRSS IN (6, 7, 12) \n"); sbSearch.append(" ) \n"); sbSearch.append(" ) \n"); sbSearch.append(" THEN \n"); sbSearch.append(" 'E' \n"); sbSearch.append(" ELSE \n"); sbSearch.append(" CASE WHEN SYSDATE BETWEEN DOC.START_DATE AND DOC.END_DATE AND ACPT.PRSS IN ( 1, 2 ) THEN \n"); sbSearch.append(" 'P' \n"); sbSearch.append(" END \n"); sbSearch.append(" END \n"); sbSearch.append(" WHEN ACPT.ACPT_TYPE = 'DEADLINE' THEN \n"); sbSearch.append(" CASE WHEN ACPT.PRSS = 4 \n"); sbSearch.append(" OR ( DOC.END_DATE < SYSDATE AND ACPT.PRSS = 2 ) THEN \n"); sbSearch.append(" 'E' /* 집계종료 */ \n"); sbSearch.append(" ELSE \n"); sbSearch.append(" CASE WHEN SYSDATE BETWEEN DOC.START_DATE AND DOC.END_DATE AND ACPT.PRSS IN ( 1, 2 ) THEN \n"); sbSearch.append(" 'P' /* 집계진행 */ \n"); sbSearch.append(" END \n"); sbSearch.append(" END \n"); sbSearch.append(" END AS STATUS \n"); sbSearch.append(" FROM \n"); sbSearch.append(" MOUMI_TOT_DOC DOC \n"); sbSearch.append(" , MOUMI_ACPT_LIMIT_STRATEGY ACPT \n"); if( !organId.equals("ALL") && deptId.equals("ALL") ) { sbSearch.append(" , MOUMI_DEPT DEP \n"); } sbSearch.append(" WHERE \n"); sbSearch.append(" DOC.ACPT_LIMIT = ACPT.ID \n"); if( !organId.equals("ALL") && deptId.equals("ALL") ) { sbSearch.append(" AND DOC.DEPT_ID = DEP.ID \n"); } sbSearch.append(" AND DOC.GROUP_ID = 2 \n"); sbSearch.append(" AND DOC.DEL_TYPE = 'N' \n"); sbSearch.append(" AND TO_CHAR(DOC.START_DATE,'YYYY-MM-DD HH24:MI:SS') BETWEEN (:startDate || ' 00:00:00' ) \n"); sbSearch.append(" AND (:endDate || ' 23:59:59') \n"); if( !deptId.equals("ALL") ) { //부서 및 학교로 특정됨 sbSearch.append(" AND DOC.DEPT_ID = :deptId \n"); }else{ if( !organId.equals("ALL") ) { //기관(교육청) sbSearch.append(" AND DEP.ORGAN = :organId \n"); if( deptSchoolType.equals("D") ) { //부서 sbSearch.append(" AND DEP.GRADE IS NULL \n"); }else if( deptSchoolType.equals("S") ) { //학교 sbSearch.append(" AND DEP.GRADE IS NOT NULL \n"); } } } sbSearch.append(" ) \n"); sbSearch.append(" ) B \n"); Query query = pm.newQuery("javax.jdo.query.SQL",sbSearch.toString()); if( LOG.isDebugEnabled() ) { LOG.debug("query={}", sbSearch.toString()); } List list = (List)query.executeWithMap(params); return list; } }