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.
803 lines
46 KiB
803 lines
46 KiB
/* |
|
* 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<DeptVO> deptList = (List<DeptVO>) 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; |
|
} |
|
}
|
|
|