/**
* 검색조건 및 초기데이타 로드
*
* @param HttpServletRequest
* @return
*/
private void loadCondition(HttpServletRequest request, UseBeforeParam pm) throws Exception {
/** ********* 검사결과 목록 생성 START ************** */
String[][] scFIELD = {{"해당없음", "해당없음"}, {"적합", "적합"}, {"부적합", "부적합"}};
request.setAttribute("v_scITEM_OUT", KJFUtil.makeSelect(scFIELD));
/** ********* 조회조건 목록 생성 END ************** */
/** ********* 처리상태 목록 생성 START ************** */
String[][] scPROC_STE = {{"1", "신규등록"}, {"2", "처리중"}, {"3", "처리완료"}};
request.setAttribute("v_scPROC_STE", KJFUtil.makeSelect(scPROC_STE));
/** ********* 조회조건 목록 생성 END ************** */
} // end loadCondition
public String execute(HttpServletRequest request, ActionForm form) throws Exception {
String cmd = request.getParameter("cmd");
user = (UserEnt) request.getSession().getAttribute("user");
// 로그인 유무 체크
if (KJFUtil.isEmpty(user)) {
throw new LoginException(request, "회원용 페이지 로그인 체크", "../member/login.do");
}
// 검색조건 설정및 체크
UseBeforeParam pm = checkPm(request, form);
loadCondition(request, pm);
// 사용전검사 조회
loadList(request, pm);
// 사용전검사 메모 조회
loadMemoList(request, pm);
// 사용전검사 기자재 조회
loadEquipData(request, pm);
// 사용전검사 현장검사관리
loadUserBeforeExe(request, pm, "1");
loadUserBeforeExe(request, pm, "2");
loadUserBeforeExe(request, pm, "3");
request.setAttribute("pm", pm);
return request.getParameter("cmd");
}
/**
* 검색조건 초기값 설정및 체크
*
* @param HttpServletRequest
* @return SYSParam
*/
private MainParam checkPm(HttpServletRequest request, ActionForm form) throws Exception {
MainParam pm = (MainParam) form;
if (KJFUtil.isEmpty(pm.getScMonth())) {
pm.setScMonth(KJFDate.getCurTime("yyyy-mm"));
}
// 파라미터 디버깅
KJFLog.debug(pm.toString(request));
return pm;
} // end checkPm
/**
* 폼 체크 메소드
*
* @param request
* @param form
* @return
* @throws Exception
*/
private IllegalityParam checkPm(HttpServletRequest request, ActionForm form) throws Exception {
IllegalityParam pm = (IllegalityParam) form;
// 파라미터 디버깅
KJFLog.debug(pm.toString(request));
// 페이징 라인
if (KJFUtil.isEmpty(pm.getRowPerPage())) {
pm.setRowPerPage(Config.props.get("ROW_PER_PAGE"));
}
return pm;
}
public String execute(HttpServletRequest request, ActionForm form) throws Exception {
user = (UserEnt) request.getSession().getAttribute("user");
// 로그인 유무 체크
if (KJFUtil.isEmpty(user)) {
throw new LoginException(request, "회원용 페이지 로그인 체크", "../member/login.do");
}
// 검색조건 설정및 체크
IllegalityParam pm = checkPm(request, form);
// 위법현황 정보
loadData(request, pm);
return request.getParameter("cmd");
}
/**
* 사용전검사 현장검사관리 조회
*
* @param request
* @param form
* @throws Exception
*/
public void loadUserBeforeExe_before(HttpServletRequest request, UseBeforeParam pm, String key)
throws Exception {
ReportDAO rDAO = new ReportDAO();
ReportEntity rEntTable1 = null;
ReportEntity rEntTable2 = null;
ReportEntity rEntTable3 = null;
// String scRECV_NUM = request.getParameter("scRECV_NUM");
String scRECV_NUM = pm.getScRecv_num();
StringBuffer sbSQL = new StringBuffer();
sbSQL.append(" SELECT UDM.SEQ, MIDCLAS||' '|| SMACLAS AS CLASS_NAME, SRL, BAS, \n");
sbSQL.append(
" UDM.CONT ,REPLACE(NVL(UCD.DETAIL_CONT,UDM.DETAIL_CONT),' ','') AS DETAIL_CONT ,UCD.ITEM_OUT \n");
sbSQL.append(" FROM PT_UB_DETAIL_MASTER UDM , PT_UB_DETAIL UCD \n");
if ("1".equals(key)) {
sbSQL.append(" WHERE UDM.ITEM = '2' AND SUBSTR(LARCLAS,0,1) = '1' \n");
} else if ("2".equals(key)) {
sbSQL.append(" WHERE UDM.ITEM = '2' AND SUBSTR(LARCLAS,0,1) = '2' \n");
} else if ("3".equals(key)) {
sbSQL.append(" WHERE UDM.ITEM = '2' AND SUBSTR(LARCLAS,0,1) = '3' \n");
}
if (!KJFUtil.isEmpty(scRECV_NUM)) {
sbSQL.append(" AND UCD.RECV_NUM(+) = '" + scRECV_NUM + "' \n");
} else {
sbSQL.append(" AND UCD.RECV_NUM(+) = '0' \n");
}
sbSQL.append(" AND UDM.SEQ = UCD.SEQ(+) \n");
sbSQL.append(" ORDER BY TO_NUMBER(ORDER_SEQ) ASC \n");
request.setAttribute("pm", pm);
if ("1".equals(key)) {
rEntTable1 = rDAO.select(sbSQL.toString());
request.setAttribute("rEntTable1", rEntTable1);
} else if ("2".equals(key)) {
rEntTable2 = rDAO.select(sbSQL.toString());
request.setAttribute("rEntTable2", rEntTable2);
} else if ("3".equals(key)) {
rEntTable3 = rDAO.select(sbSQL.toString());
request.setAttribute("rEntTable3", rEntTable3);
}
}
public String updateExcludeOnlySql(ValueObject obj, String[] fields) throws SQLException {
fields = KJFUtil.getDifferenceOfSets(default_fields, fields);
if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) {
throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : updateModifiedOnly() ");
}
PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj;
HashMap clobs = new HashMap();
if (fields == null) throw new SQLException("Field Name can not be Null");
StringBuffer setString = new StringBuffer();
boolean flag = false;
for (int i = 0; i < fields.length; i++) {
if (fields[i] == null) throw new SQLException("Field Name can not be Null");
if (default_update_field.containsKey(fields[i]) == false) {
if (flag) setString.append(",");
flag = true;
if (clobs.containsKey(fields[i])) {
setString.append(fields[i]).append("=?");
} else {
setString.append(fields[i]).append("=").append(toDB(entity.getByName(fields[i])));
}
}
}
if (flag = false) throw new SQLException("Nothing to update");
StringBuffer sb = new StringBuffer();
sb.append("update PT_KICA_ERR_LOG set ").append(setString.toString()).append(" where 1=1 ");
sb.append(" and SEQ = ").append(toDB(entity.getSEQ()));
return sb.toString();
}
public String execute(HttpServletRequest request, ActionForm form) throws Exception {
/** **** Service API 초기화 ***** */
PlatformRequest pReq = new PlatformRequest(request, Config.props.get("ENCODING"));
/** Web Server에서 XML수신 및 Parsing * */
pReq.receiveData();
/** 변수 획득 * */
vl = pReq.getVariableList();
ReportDAO rDAO = new ReportDAO();
ReportEntity rEntity = null;
String scDATE = vl.getValueAsString("scDATE");
String scDATE1 = vl.getValueAsString("scDATE1");
String scDATE2 = vl.getValueAsString("scDATE2");
String scCOM_NAME = vl.getValueAsString("scCOM_NAME");
String scRECV_NUM = vl.getValueAsString("scRECV_NUM");
String scMOT_STE = vl.getValueAsString("scMOT_STE");
String scDISPO_CONT = vl.getValueAsString("scDISPO_CONT");
String scSIDO_CODE = vl.getValueAsString("scSIDO_CODE");
String scWRT_NUM = vl.getValueAsString("scWRT_NUM");
StringBuilder sQuery = new StringBuilder();
StringBuilder sWhere = new StringBuilder();
/*
sQuery.append(" SELECT '0' AS CHECKER, PT_C.NAME, PT_M.*, ");
sQuery.append(" DECODE(DISPO_CONT, 'M00001', '등록취소', 'M00002', '영업정지', 'M00003', '과태료처분', ");
sQuery.append(" 'M00004', '시정명령', 'M00005', '형사고발', 'M00006', '경고조치', ' ') AS TMPDISPO_CONT, ");
sQuery.append(" DECODE(MOT_STE, 'C00001', '최초등록', 'C00002', '내부건의', 'C00003', '처분완료', '') AS TMPMOT_STE, ");
sQuery.append(" DECODE(D_PER_CODE, 'REQ001', '정보통신공사협회', 'REQ002', '공제조합', 'REQ003', '지방자치단체', '') AS TMPD_PER_CODE ");
sQuery.append(" FROM PT_R_COMPANY_MASTER PT_C INNER JOIN PT_M_MASTER PT_M ");
sQuery.append(" ON RTRIM(PT_C.TMP_WRT_NUM) = PT_M.TMP_WRT_NUM ");
*/
sQuery.append(" SELECT '0' AS CHECKER, PT_C.NAME, PT_M.*, ");
sQuery.append(
" PT_C1.CODE_NAME AS TMPD_PER_CODE, DECODE(PT_M.DISPO_CONT2,'',PT_C2.CODE_NAME,PT_C2.CODE_NAME ||' 및 '||PT_C2_2.CODE_NAME) AS TMPDISPO_CONT, PT_C3.CODE_NAME AS TMPMOT_STE, PT_C4.CODE_NAME AS VIOL_CONT ");
sQuery.append(" FROM (PT_R_COMPANY_MASTER PT_C INNER JOIN PT_M_MASTER PT_M ");
sQuery.append(" ON RTRIM(PT_C.TMP_WRT_NUM) = PT_M.TMP_WRT_NUM) ");
sQuery.append(" LEFT JOIN (SELECT P_CODE, CODE, CODE_NAME FROM PT_COM_CODE ");
sQuery.append(" WHERE P_CODE = 'PTMREQ' )PT_C1 ON PT_M.D_PER_CODE = PT_C1.CODE ");
sQuery.append(" LEFT JOIN (SELECT P_CODE, CODE, CODE_NAME FROM PT_COM_CODE ");
sQuery.append(" WHERE P_CODE = 'PTMPRO' )PT_C2 ON PT_M.DISPO_CONT = PT_C2.CODE ");
sQuery.append(" LEFT JOIN (SELECT P_CODE, CODE, CODE_NAME FROM PT_COM_CODE ");
sQuery.append(" WHERE P_CODE = 'PTMPRO' )PT_C2_2 ON PT_M.DISPO_CONT2 = PT_C2_2.CODE ");
sQuery.append(" LEFT JOIN (SELECT P_CODE, CODE, CODE_NAME FROM PT_COM_CODE ");
sQuery.append(" WHERE P_CODE = 'PTMSTE' )PT_C3 ON PT_M.MOT_STE = PT_C3.CODE ");
sQuery.append(" LEFT JOIN (SELECT P_CODE, CODE, CODE_NAME FROM PT_COM_CODE ");
sQuery.append(" WHERE P_CODE = 'PTMCONT' )PT_C4 ON PT_M.VIOL_CONT_CODE = PT_C4.CODE ");
sWhere.append(" WHERE 1 = 1 ");
if (!KJFUtil.isEmpty(vl.getValueAsString("scDATE"))) {
sWhere.append(" AND (PT_M." + scDATE + " BETWEEN '" + scDATE1 + "' AND '" + scDATE2 + "') ");
}
if (!KJFUtil.isEmpty(scWRT_NUM))
sWhere.append(" AND PT_M.WRT_NUM LIKE '%" + scWRT_NUM + "%' ");
if (!KJFUtil.isEmpty(scRECV_NUM))
sWhere.append(" AND PT_M.RECV_NUM = '" + scRECV_NUM + "' ");
if (!KJFUtil.isEmpty(scMOT_STE) && !scMOT_STE.equalsIgnoreCase("ALL"))
sWhere.append(" AND PT_M.MOT_STE = '" + scMOT_STE + "' ");
if (scDISPO_CONT.length() > 0 && !scDISPO_CONT.equalsIgnoreCase("ALL"))
sWhere.append(" AND PT_M.DISPO_CONT = '" + scDISPO_CONT + "' ");
if (!KJFUtil.isEmpty(scCOM_NAME))
sWhere.append(" AND PT_C.NAME LIKE '%" + scCOM_NAME + "%' ");
if (!KJFUtil.isEmpty(vl.getValueAsString("scSIDO_CODE"))) {
sWhere.append(" AND PT_M.SIDO_CODE = '" + scSIDO_CODE + "' ");
}
rEntity = rDAO.select(sQuery.toString() + sWhere.toString());
KJFMi.ReEnt2Ds(dl, rEntity, "output");
request.setAttribute("dl", dl);
return next;
}
public String execute(HttpServletRequest request, ActionForm form) throws Exception {
/** **** Service API 초기화 ***** */
PlatformRequest pReq = new PlatformRequest(request, Config.props.get("ENCODING"));
/** Web Server에서 XML수신 및 Parsing * */
pReq.receiveData();
/** 변수 획득 * */
vl = pReq.getVariableList();
String scDOC_CLASS = vl.getValueAsString("scDOC_CLASS");
String scSIDO_CODE = vl.getValueAsString("scSIDO_CODE");
ReportDAO rDAO = new ReportDAO();
ReportEntity rMasterEntity = null;
// 코드성 데이타 인 경우 항상 필드가 2개만 존재해야 함
StringBuilder sQuery = new StringBuilder();
StringBuilder sWhere = new StringBuilder();
// Deficit Codes Table
sQuery.append(" SELECT DOC_NUM1, DOC_NUM2, DOC_NUM3, DOC_NUM4 ");
sQuery.append(" FROM PT_R_DOC_NUM ");
// 이 메서드는 RECV_NUM 생성 외 다른 목적으로 사용되지 않음.
// 모든 공사업관리에 대해 접수번호를 자동생성한다.
// 시도코드+yyyy+xxxx (bsbs20109999). 12자리
scDOC_CLASS = "RECV_NUM";
sWhere.append(" WHERE 1=1 ");
sWhere.append(" AND DOC_CLASS = '" + scDOC_CLASS + "' ");
sWhere.append(" AND SD_CODE = '" + scSIDO_CODE + "' ");
rMasterEntity = rDAO.select(sQuery.toString() + sWhere.toString());
sQuery.delete(0, sQuery.length());
if (rMasterEntity.getRowCnt() > 0) {
// 연도 + 일련번호 체크
String docNum2 = rMasterEntity.getValue(0, "DOC_NUM2");
if (docNum2 == null || !docNum2.matches("^\\d{8}$")) {
// 난감한데?
}
String preYear = docNum2.substring(0, 4);
String preSeq = docNum2.substring(4, 8);
String newYear = String.valueOf(Calendar.getInstance().get(Calendar.YEAR));
String newSeq;
if (preYear.equals(newYear)) {
newSeq = KJFUtil.f_code_make(String.valueOf(Integer.parseInt(preSeq, 10) + 1), 4);
} else {
newSeq = "0001";
}
docNum2 = newYear + newSeq;
// 레코드를 삭제하고 넣으려했더니 구현이 안되어있다. 해당 메서드 찾는게 더 귀찮다. 새로 만들기.
Dataset ds = new Dataset("output");
ds.addColumn("DOC_NUM1", ColumnInfo.CY_COLINFO_STRING, 20);
ds.addColumn("DOC_NUM2", ColumnInfo.CY_COLINFO_STRING, 20);
ds.addColumn("DOC_NUM3", ColumnInfo.CY_COLINFO_STRING, 20);
ds.addColumn("DOC_NUM4", ColumnInfo.CY_COLINFO_STRING, 20);
ds.appendRow();
ds.setColumn(0, "DOC_NUM1", scSIDO_CODE);
ds.setColumn(0, "DOC_NUM2", docNum2);
ds.setColumn(0, "DOC_NUM3", "");
ds.setColumn(0, "DOC_NUM4", "");
dl.addDataset(ds);
sQuery.append(" UPDATE PT_R_DOC_NUM SET DOC_NUM2 = '" + docNum2 + "'");
rDAO = new ReportDAO();
rDAO.execute(sQuery.toString() + sWhere.toString());
} else {
String newYear = String.valueOf(Calendar.getInstance().get(Calendar.YEAR));
String newSeq = "0001";
String docNum2 = newYear + newSeq;
sQuery.append(" INSERT INTO PT_R_DOC_NUM (DOC_CLASS,SD_CODE,SGG_CODE,DOC_NUM1,DOC_NUM2) ");
sQuery.append(
" VALUES('"
+ scDOC_CLASS
+ "', '"
+ scSIDO_CODE
+ "', '"
+ scSIDO_CODE
+ "', '"
+ scSIDO_CODE
+ "', '"
+ docNum2
+ "') ");
rDAO = new ReportDAO();
rDAO.execute(sQuery.toString());
ArrayList arrField = new ArrayList();
arrField.add(scSIDO_CODE);
arrField.add(docNum2);
arrField.add(null);
arrField.add(null);
rMasterEntity.addRow(arrField);
KJFMi.ReEnt2Ds(dl, rMasterEntity, "output");
}
request.setAttribute("dl", dl);
return next;
}
public List<SearchBean> loadXmlList(
HttpServletRequest request, SearchParam pm, String searchUrl, String section_nm)
throws Exception {
List<SearchBean> xmlList = new ArrayList<SearchBean>();
// xml 파싱
XmlParser section = new XmlParser(searchUrl);
System.out.println(section.length());
for (int i = 0; i < section.length(); i++) {
// 전체 목록 수
String totalCount = KJFUtil.print(section.getInfoValue(i, "totcnt"), "0");
// 페이지별 목록 수
int rowPerPage = KJFUtil.str2int(pm.getRowPerPage());
// 현재 페이지 번호
int nowPage = 1;
nowPage = KJFUtil.isEmpty(pm.getNowPage()) ? 1 : Integer.parseInt(pm.getNowPage());
if (rowPerPage == 0) rowPerPage = Integer.parseInt(totalCount);
if ((rowPerPage * nowPage) - Integer.parseInt(totalCount) > rowPerPage) nowPage = 1;
pm.setTotalCount(totalCount);
pm.setNowPage(String.valueOf(nowPage));
// 검색 결과 XML 에서 추출 태그 변경
if (section.getName(i).indexOf(section_nm) != -1) {
System.out.println(section.getName(i));
System.out.println("에 대한 약 " + totalCount + "개 결과 중 ");
} else {
continue;
}
// doc print
if (section.isDocs(i)) {
for (int j = 0; j < section.docLength(i); j++) {
// att node들은 HashMap객체에 담겨져 있다.
// 사용할때는 att노드의 Attribute name의 value를 key값으로 넣어준다.
// xml data 중 <section name="title"> 필드 명
// i 값은 section 순서
if (section.getName(i).indexOf(section_nm) != -1) {
System.out.println(j);
SearchBean scBean = new SearchBean();
// 게시판 관련
scBean.setCt_id(section.getDocValue(i, j, "^CT_ID:"));
scBean.setBoard_seq(section.getDocValue(i, j, "^BOARD_SEQ:"));
scBean.setSubject(section.getDocValue(i, j, "^SUBJECT:"));
scBean.setContent(section.getDocValue(i, j, "^CONTENT:"));
// 웹페이지 관련
scBean.setMenu_id(section.getDocValue(i, j, "^MENU_ID:"));
scBean.setM_menu(section.getDocValue(i, j, "^M_MENU:"));
scBean.setL_menu(section.getDocValue(i, j, "^L_MENU:"));
scBean.setS_munu(section.getDocValue(i, j, "^S_MENU:"));
scBean.setTitle(section.getDocValue(i, j, "^TITLE:"));
scBean.setBody(section.getDocValue(i, j, "^CONTENT:"));
System.out.println(section.getDocValue(i, j, "^tit:"));
System.out.println(section.getDocValue(i, j, "^url:"));
xmlList.add(scBean);
}
} // end of for
}
}
/** **** 검색조건 초기값 ********** */
request.setAttribute("pm", pm);
return xmlList;
}
///////////////////////////////////////////////////////////////////////////////////
// 특정필드를 제외한 전체 필드를 수정한다.
public int updateExcludeOnly(ValueObject obj, String[] fields) throws SQLException {
fields = KJFUtil.getDifferenceOfSets(default_fields, fields);
if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) {
throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : updateModifiedOnly() ");
}
PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj;
HashMap clobs = new HashMap();
Connection conn = null;
PreparedStatement ps = null;
int result = 0;
if (fields == null) throw new SQLException("Field Name can not be Null");
StringBuffer setString = new StringBuffer();
boolean flag = false;
for (int i = 0; i < fields.length; i++) {
if (fields[i] == null) throw new SQLException("Field Name can not be Null");
if (default_update_field.containsKey(fields[i]) == false) {
if (flag) setString.append(",");
flag = true;
if (clobs.containsKey(fields[i])) {
setString.append(fields[i]).append("=?");
} else {
setString.append(fields[i]).append("=").append(toDB(entity.getByName(fields[i])));
}
}
}
if (flag = false) throw new SQLException("Nothing to update");
StringBuffer sb = new StringBuffer();
sb.append("update PT_KICA_ERR_LOG set ").append(setString.toString()).append(" where 1=1 ");
sb.append(" and SEQ = ").append(toDB(entity.getSEQ()));
KJFLog.sql(sb.toString());
try {
conn = this.getConnection();
ps = conn.prepareStatement(sb.toString());
int k = 1;
for (int i = 0; i < fields.length; i++) {
if (clobs.containsKey(fields[i])) {
ps.setString(k++, (entity.getByName(fields[i])).toString());
}
}
result = ps.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
if (ps != null) ps.close();
this.release(conn);
}
return result;
}
/**
* 영업정지 리스트 정보를 가져온다.
*
* @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);
}
/**
* 사용전검사 현장검사관리 조회
*
* @param request
* @param form
* @throws Exception
*/
public void loadUserBeforeExe(HttpServletRequest request, UseBeforeParam pm, String key)
throws Exception {
ReportDAO rDAO = new ReportDAO();
ReportEntity rEntTable1 = null;
ReportEntity rEntTable2 = null;
ReportEntity rEntTable3 = null;
ReportEntity rEntTableCnt1 = null;
ReportEntity rEntTableCnt2 = null;
ReportEntity rEntTableCnt3 = null;
// String scRECV_NUM = request.getParameter("scRECV_NUM");
String scRECV_NUM = pm.getScRecv_num();
StringBuffer sbSQL = new StringBuffer();
StringBuffer sbSQLCnt = new StringBuffer();
sbSQL.append(" SELECT UDM.SEQ, \n");
if ("2".equals(key)) {
sbSQL.append(
" MIDCLAS||' '|| SMACLAS AS CLASS_NAME_CHK, CASE WHEN INSTR(SMACLAS,'-') > 0 THEN RTRIM(SUBSTR(SMACLAS,0,INSTR(SMACLAS,'-')-1)) ");
sbSQL.append(" ELSE SMACLAS END CLASS_NAME1, ");
sbSQL.append(
" CASE WHEN INSTR(SMACLAS,'-') > 0 THEN RTRIM(SUBSTR(SMACLAS,INSTR(SMACLAS,'-')+1)) ");
sbSQL.append(" ELSE '' END CLASS_NAME2, ");
} else {
sbSQL.append(" CASE WHEN INSTR(MIDCLAS,'-') > 0 THEN RTRIM(REPLACE(MIDCLAS,'-','')) \n");
sbSQL.append(" ELSE SMACLAS END CLASS_NAME1, \n");
sbSQL.append(" CASE WHEN MIDCLAS IS NULL THEN '' ELSE SMACLAS END CLASS_NAME2, \n");
}
sbSQL.append(" SRL, BAS, UDM.CONT, \n");
sbSQL.append(
" REPLACE(NVL(UCD.DETAIL_CONT,UDM.DETAIL_CONT),' ','') AS DETAIL_CONT ,UCD.ITEM_OUT \n");
sbSQL.append(" FROM PT_UB_DETAIL_MASTER UDM , PT_UB_DETAIL UCD \n");
sbSQLCnt.append(" SELECT CLASS_NAME1,COUNT(CLASS_NAME1) AS CNT FROM ( \n");
if ("2".equals(key)) {
sbSQLCnt.append(
" SELECT CASE WHEN INSTR(SMACLAS,'-') > 0 THEN RTRIM(SUBSTR(SMACLAS,0,INSTR(SMACLAS,'-')-1)) ");
sbSQLCnt.append(" ELSE SMACLAS END CLASS_NAME1 ");
} else {
sbSQLCnt.append(
" SELECT CASE WHEN INSTR(MIDCLAS,'-') > 0 THEN RTRIM(REPLACE(MIDCLAS,'-','')) \n");
sbSQLCnt.append(" ELSE SMACLAS END CLASS_NAME1 \n");
}
sbSQLCnt.append(" FROM PT_UB_DETAIL_MASTER UDM , PT_UB_DETAIL UCD \n");
sbSQL.append(" WHERE UDM.ITEM = '2' AND SUBSTR(LARCLAS,0,1) = '" + key + "' \n");
sbSQLCnt.append(" WHERE UDM.ITEM = '2' AND SUBSTR(LARCLAS,0,1) = '" + key + "' \n");
if (!KJFUtil.isEmpty(scRECV_NUM)) {
sbSQL.append(" AND UCD.RECV_NUM(+) = '" + scRECV_NUM + "' \n");
sbSQLCnt.append(" AND UCD.RECV_NUM(+) = '" + scRECV_NUM + "' \n");
} else {
sbSQL.append(" AND UCD.RECV_NUM(+) = '0' \n");
sbSQLCnt.append(" AND UCD.RECV_NUM(+) = '0' \n");
}
sbSQL.append(" AND UDM.SEQ = UCD.SEQ(+) \n");
sbSQL.append(" ORDER BY TO_NUMBER(ORDER_SEQ) ASC \n");
sbSQLCnt.append(" AND UDM.SEQ = UCD.SEQ(+) \n");
sbSQLCnt.append(" ORDER BY TO_NUMBER(ORDER_SEQ) ASC ) GROUP BY CLASS_NAME1 \n");
request.setAttribute("pm", pm);
if ("1".equals(key)) {
rEntTable1 = rDAO.select(sbSQL.toString());
request.setAttribute("rEntTable1", rEntTable1);
rEntTableCnt1 = rDAO.select(sbSQLCnt.toString());
request.setAttribute("rEntTableCnt1", rEntTableCnt1);
} else if ("2".equals(key)) {
rEntTable2 = rDAO.select(sbSQL.toString());
request.setAttribute("rEntTable2", rEntTable2);
rEntTableCnt2 = rDAO.select(sbSQLCnt.toString());
request.setAttribute("rEntTableCnt2", rEntTableCnt2);
} else if ("3".equals(key)) {
rEntTable3 = rDAO.select(sbSQL.toString());
request.setAttribute("rEntTable3", rEntTable3);
rEntTableCnt3 = rDAO.select(sbSQLCnt.toString());
request.setAttribute("rEntTableCnt3", rEntTableCnt3);
}
}