/**
* 사용전검사 기자재 조회
*
* @param request
* @param form
* @throws Exception
*/
private void loadEquipData(HttpServletRequest request, UseBeforeParam pm) throws Exception {
ReportDAO rDAO = new ReportDAO();
ReportEntity eEntity = null;
String RECV_NUM = pm.getScRecv_num();
String SIDO_CODE = user.getSIDO_CODE();
String SIGUNGU_CODE = user.getSIGUNGU_CODE();
StringBuffer sbSQL = new StringBuffer();
sbSQL.append(" SELECT \n");
sbSQL.append(" RECV_NUM, SIDO_CODE, SIGUNGU_CODE ,CIV_RECV_NUM , \n");
sbSQL.append(" TYPE_PROC_NO_CA,TYPE_PROC_NM_CA,FREQUENCY_SCOPE_CA,WIRE_TYPE_CA, \n");
sbSQL.append(" TYPE_PROC_NO_MA,TYPE_PROC_NM_MA,FREQUENCY_SCOPE_MA,WIRE_TYPE_MA, \n");
sbSQL.append(
" TYPE_PROC_NO_SMA ,TYPE_PROC_NM_SMA,FREQUENCY_SCOPE_SMA, WIRE_TYPE_SMA,INS_DT,'U' AS EQU_CMD \n");
sbSQL.append(" FROM PT_UB_EQUIPMENT \n");
sbSQL.append(" WHERE RECV_NUM = ? \n");
sbSQL.append(" AND SIDO_CODE = ? \n");
sbSQL.append(" AND SIGUNGU_CODE = ? \n");
rDAO.setValue(1, RECV_NUM);
rDAO.setValue(2, SIDO_CODE);
rDAO.setValue(3, SIGUNGU_CODE);
eEntity = rDAO.select(sbSQL.toString());
request.setAttribute("eEntity", eEntity);
}
/**
* 사용전검사 조회
*
* @param request
* @param form
* @throws Exception
*/
private void loadList(HttpServletRequest request, UseBeforeParam pm) throws Exception {
ReportDAO rDAO = new ReportDAO();
ReportEntity rEntity = null;
// String scRECV_NUM = request.getParameter("scRECV_NUM");
String scRECV_NUM = pm.getScRecv_num();
String SIDO_CODE = user.getSIDO_CODE(); // request.getParameter("SIDO_CODE");
String SIGUNGU_CODE = user.getSIGUNGU_CODE(); // request.getParameter("SIGUNGU_CODE");
String selectSQL =
" SELECT UU.*, CC.CODE_NAME, SM.SERVER_ADDR, SM.SEND_SYS_ID,SM.RECV_SYS_ID, SM.SERVER_YN"
+ " FROM PT_UB_USEBEFORE UU LEFT JOIN PT_COM_CODE CC ON CC.CODE = UU.USE AND CC.P_CODE = 'BLDDIV' "
+ " LEFT JOIN PT_S_SYSVAR_MASTER SM ON SM.SIGUNGU_CODE = UU.SIGUNGU_CODE \n";
String whereSQL =
"WHERE UU.RECV_NUM = '"
+ scRECV_NUM
+ "' \n"
+ " AND UU.SIDO_CODE = '"
+ SIDO_CODE
+ "' \n"
+ " AND UU.SIGUNGU_CODE = '"
+ SIGUNGU_CODE
+ "' ";
rEntity = rDAO.select(selectSQL + whereSQL);
/** **** 검색조건 초기값 ********** */
request.setAttribute("pm", pm);
request.setAttribute("rEntity", rEntity);
}
/**
* 사용전검사 메모 조회
*
* @param request
* @param form
* @throws Exception
*/
private void loadMemoList(HttpServletRequest request, UseBeforeParam pm) throws Exception {
ReportDAO rDAO = new ReportDAO();
ReportEntity rEntity = null;
// String scRECV_NUM = request.getParameter("scRECV_NUM");
String scRECV_NUM = pm.getScRecv_num();
String SIDO_CODE = user.getSIDO_CODE();
String SIGUNGU_CODE = user.getSIGUNGU_CODE();
String selectSQL =
" SELECT SEQ, MEMO_DT, WRT_TIME, MEMO_CONT, WRT_NAME, INS_DT FROM PT_UB_MEMO \n";
String whereSQL =
"WHERE RECV_NUM = '"
+ scRECV_NUM
+ "' \n"
+ " AND SIDO_CODE = '"
+ SIDO_CODE
+ "' \n"
+ " AND SIGUNGU_CODE = '"
+ SIGUNGU_CODE
+ "' ";
rEntity = rDAO.select(selectSQL + whereSQL);
/** **** 검색조건 초기값 ********** */
request.setAttribute("pm", pm);
request.setAttribute("mEntity", rEntity);
}
/**
* 영업정지 리스트 정보를 가져온다.
*
* @param request
* @param form
* @throws Exception
*/
public void loadData(HttpServletRequest request, IllegalityParam pm) throws Exception {
ReportEntity rEntity = null;
ReportDAO rDAO = new ReportDAO();
int i = 1;
StringBuffer sbSQL = new StringBuffer();
sbSQL.append(" SELECT \n");
sbSQL.append(" PT_C.COI_WRT_NUM, \n"); // 공사업등록번호
sbSQL.append(" PT_M.TMP_WRT_NUM, \n"); // 가등록번호
sbSQL.append(" PT_M.WRT_NUM, \n"); // 등록번호
sbSQL.append(" PT_M.VIOL_DT, \n"); // 위법일자
sbSQL.append(" PT_C1.CODE_NAME AS VIOL_CONT, \n"); // 위법내용
sbSQL.append(" PT_M.DISPO_DT, \n"); // 처분일자
sbSQL.append(" PT_S.BUSISUSP_START_DT, \n"); // 시작일자
sbSQL.append(" PT_S.BUSISUSP_END_DT, \n"); // 종료일자
sbSQL.append(" PT_M.MOT_STE, \n"); // 진행상태
sbSQL.append(" PT_C2.CODE_NAME AS MOT_STE_NM \n"); // 진행상태
sbSQL.append(" FROM (PT_R_COMPANY_MASTER PT_C INNER JOIN PT_M_MASTER PT_M \n");
sbSQL.append(" ON RTRIM(PT_C.TMP_WRT_NUM) = PT_M.TMP_WRT_NUM) \n");
sbSQL.append(" INNER JOIN ( \n");
sbSQL.append(" SELECT TMP_WRT_NUM, WRT_NUM, \n");
sbSQL.append(" BUSISUSP_START_DT, BUSISUSP_END_DT \n");
sbSQL.append(" FROM PT_M_SUSPENSION \n");
sbSQL.append(" ) PT_S ON PT_M.TMP_WRT_NUM = PT_S.TMP_WRT_NUM \n");
sbSQL.append(" AND PT_M.WRT_NUM = PT_S.WRT_NUM \n");
sbSQL.append(" LEFT JOIN ( \n");
sbSQL.append(" SELECT P_CODE, CODE, CODE_NAME \n");
sbSQL.append(" FROM PT_COM_CODE \n");
sbSQL.append(" WHERE P_CODE = 'PTMCONT' \n");
sbSQL.append(" ) PT_C1 ON PT_M.VIOL_CONT_CODE = PT_C1.CODE \n");
sbSQL.append(" LEFT JOIN ( \n");
sbSQL.append(" SELECT P_CODE, CODE, CODE_NAME \n");
sbSQL.append(" FROM PT_COM_CODE \n");
sbSQL.append(" WHERE P_CODE = 'PTMSTE' \n");
sbSQL.append(" ) PT_C2 ON PT_M.MOT_STE = PT_C2.CODE \n");
sbSQL.append(" WHERE PT_C.COI_WRT_NUM = ? \n"); // 공사업등록번호
sbSQL.append(" AND PT_C.MANA_NUM = ? \n"); // 사업자 번호
sbSQL.append(" ORDER BY PT_M.VIOL_DT \n"); // 정렬: 위법일자
rDAO.setValue(i++, user.getREG_NUM());
rDAO.setValue(i++, user.getCOM_NUM());
/* ************************** 페이징 관련 START **************************/
StringBuffer sbCntSQL = new StringBuffer();
sbCntSQL.append(" SELECT COUNT(*) CNT \n");
sbCntSQL.append(" FROM (PT_R_COMPANY_MASTER PT_C INNER JOIN PT_M_MASTER PT_M \n");
sbCntSQL.append(" ON RTRIM(PT_C.TMP_WRT_NUM) = PT_M.TMP_WRT_NUM) \n");
sbCntSQL.append(" INNER JOIN ( \n");
sbCntSQL.append(" SELECT TMP_WRT_NUM, WRT_NUM \n");
sbCntSQL.append(" FROM PT_M_SUSPENSION \n");
sbCntSQL.append(" ) PT_S ON PT_M.TMP_WRT_NUM = PT_S.TMP_WRT_NUM \n");
sbCntSQL.append(" AND PT_M.WRT_NUM = PT_S.WRT_NUM \n");
sbCntSQL.append(" WHERE PT_C.COI_WRT_NUM = ? \n"); // 공사업등록번호
sbCntSQL.append(" AND PT_C.MANA_NUM = ? \n"); // 사업자 번호
// 전체 목록 수
String totalCount = "";
// 페이지별 목록 수
int rowPerPage = KJFUtil.str2int(pm.getRowPerPage());
// 현재 페이지 번호
int nowPage = 1;
nowPage = KJFUtil.isEmpty(pm.getNowPage()) ? 1 : Integer.parseInt(pm.getNowPage());
rEntity = rDAO.select(sbCntSQL.toString());
totalCount = rEntity.getValue(0, "CNT");
if (rowPerPage == 0) rowPerPage = Integer.parseInt(totalCount); // 추가
if ((rowPerPage * nowPage) - Integer.parseInt(totalCount) > rowPerPage) nowPage = 1;
pm.setTotalCount(totalCount);
pm.setNowPage(String.valueOf(nowPage));
/* *************************** 페이징 관련 END **************************/
rEntity = rDAO.select(sbSQL.toString(), nowPage, rowPerPage);
/** **** 검색조건 초기값 ********** */
request.setAttribute("pm", pm);
request.setAttribute("rEntity", rEntity);
}
/**
* 공사업 등록기준 정보를 가져온다.
*
* @param request
* @param form
* @throws Exception
*/
public void loadData(HttpServletRequest request, RegMgrParam pm) throws Exception {
ReportEntity rEntity = null;
ReportDAO rDAO = new ReportDAO();
int i = 1;
StringBuffer sbSQL = new StringBuffer();
sbSQL.append(" SELECT \n");
sbSQL.append(" PT_M.COI_WRT_NUM, \n"); // 공사업등록번호
sbSQL.append(" PT_H.CHGBRE_SEQ, \n"); // 변경일련번호
sbSQL.append(" PT_M.NAME, \n"); // 회사명
sbSQL.append(" PT_S.RECV_NUM, \n"); // 접수번호
sbSQL.append(" PT_S.RECV_DT, \n"); // 접수일자
sbSQL.append(" PT_S.PROC_LIM, \n"); // 처리기한
sbSQL.append(" PT_MI.NM, \n"); // 담당자
sbSQL.append(" PT_H.MOT_STE, \n"); // 진행상태 코드
sbSQL.append(" PT_C1.CODE_NAME AS MOT_STE_NM \n"); // 진행상태 코드 명
sbSQL.append(
" FROM (PT_R_COMPANY_MASTER PT_M INNER JOIN PT_R_BASIC_CHANGE_HISTORY PT_H \n");
sbSQL.append(" ON RTRIM(PT_M.TMP_WRT_NUM) = PT_H.TMP_WRT_NUM) \n");
sbSQL.append(" INNER JOIN ( \n");
sbSQL.append(" SELECT RECV_NUM, \n"); // 접수번호
sbSQL.append(" RECV_DT, \n"); // 접수일자
sbSQL.append(" PROC_LIM, \n"); // 처리기한
sbSQL.append(" WRT_ID \n"); // 담당자
sbSQL.append(" FROM PT_R_BASIC_STATEMENT \n");
sbSQL.append(" ) PT_S ON PT_H.RECV_NUM = PT_S.RECV_NUM \n");
sbSQL.append(" LEFT JOIN ( \n");
sbSQL.append(" SELECT OFFI_ID, NM \n");
sbSQL.append(" FROM PT_MI_USER \n");
sbSQL.append(" ) PT_MI ON PT_S.WRT_ID = PT_MI.OFFI_ID \n");
// 처리상태 코드 명
sbSQL.append(" LEFT JOIN ( \n");
sbSQL.append(" SELECT P_CODE, CODE, CODE_NAME \n");
sbSQL.append(" FROM PT_COM_CODE \n");
sbSQL.append(" WHERE P_CODE = 'REGPROC' \n");
sbSQL.append(" ) PT_C1 ON PT_H.MOT_STE = PT_C1.CODE \n");
sbSQL.append(" WHERE PT_M.COI_WRT_NUM = ? \n"); // 공사업등록번호
sbSQL.append(" AND PT_M.MANA_NUM = ? \n"); // 사업자 번호
rDAO.setValue(i++, user.getREG_NUM());
rDAO.setValue(i++, user.getCOM_NUM());
/* ************************** 페이징 관련 START **************************/
StringBuffer sbCntSQL = new StringBuffer();
sbCntSQL.append(" SELECT COUNT(*) CNT \n");
sbCntSQL.append(
" FROM (PT_R_COMPANY_MASTER PT_M INNER JOIN PT_R_BASIC_CHANGE_HISTORY PT_H \n");
sbCntSQL.append(" ON RTRIM(PT_M.TMP_WRT_NUM) = PT_H.TMP_WRT_NUM) \n");
sbCntSQL.append(" INNER JOIN ( \n");
sbCntSQL.append(" SELECT RECV_NUM \n"); // 접수번호
sbCntSQL.append(" FROM PT_R_BASIC_STATEMENT \n");
sbCntSQL.append(" ) PT_S ON PT_H.RECV_NUM = PT_S.RECV_NUM \n");
sbCntSQL.append(" WHERE PT_M.COI_WRT_NUM = ? \n"); // 공사업등록번호
sbCntSQL.append(
" AND PT_M.MANA_NUM = ? \n"); // 사업자 번호
// 전체 목록 수
String totalCount = "";
// 페이지별 목록 수
int rowPerPage = KJFUtil.str2int(pm.getRowPerPage());
// 현재 페이지 번호
int nowPage = 1;
nowPage = KJFUtil.isEmpty(pm.getNowPage()) ? 1 : Integer.parseInt(pm.getNowPage());
rEntity = rDAO.select(sbCntSQL.toString());
totalCount = rEntity.getValue(0, "CNT");
if (rowPerPage == 0) rowPerPage = Integer.parseInt(totalCount); // 추가
if ((rowPerPage * nowPage) - Integer.parseInt(totalCount) > rowPerPage) nowPage = 1;
pm.setTotalCount(totalCount);
pm.setNowPage(String.valueOf(nowPage));
/* *************************** 페이징 관련 END **************************/
rEntity = rDAO.select(sbSQL.toString(), nowPage, rowPerPage);
/** **** 검색조건 초기값 ********** */
request.setAttribute("pm", pm);
request.setAttribute("rEntity", rEntity);
}