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; StringBuilder sQuery = new StringBuilder(); StringBuilder sWhere = new StringBuilder(); StringBuilder gWhere = new StringBuilder(); sQuery.append( " SELECT MAX(SC.SIDO_NM) AS ORG_NM, '-' AS ITEM_NM, SUM(QTT) AS QTT,'-' AS PDT, \n"); sQuery.append( " '-' AS INSTAL_DT, SUM(ESTAB_AOM) AS ESTAB_AOM,'-' AS REMARK ,CE.SIDO_CODE \n"); sQuery.append(" FROM PT_C_RES_CHARGE_EQU CE, PT_SIDO_CODE SC \n"); sWhere.append(" WHERE CE.SIDO_CODE = SC.AREA_CODE \n"); gWhere.append(" GROUP BY CE.SIDO_CODE \n"); rEntity = rDAO.select(sQuery.toString() + sWhere.toString() + gWhere.toString()); KJFMi.ReEnt2Ds(dl, rEntity, "output"); request.setAttribute("dl", dl); return next; }
/** * 사용전검사 조회 * * @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 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 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); }
private void insertFileExeOnly(String CT_ID, String setCT_ID) throws Exception { ReportDAO rDAO = new ReportDAO(); ReportEntity rEntity = null; String UpSQL = " UPDATE PT_BBS_COM_FILES" + " SET CT_ID = ?" + " WHERE CT_ID = ? "; int i = 0; rDAO.setValue(++i, setCT_ID); rDAO.setValue(++i, CT_ID); rDAO.execute(UpSQL); }
/** * 사용전검사 현장검사관리 조회 * * @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); } }
/** * 삭제 * * @param Dataset ds, int arg_row * @return */ private void deleteExe(Dataset ds, int arg_row) throws Exception { StringBuilder deleteQuery = new StringBuilder(); if (ds.getId().equalsIgnoreCase("dsPT_C_RES_TRAFFIC_OUT")) { String SEQ = ds.getDeleteColumn(arg_row, "SEQ").getString(); String SIDO_CODE = vl.getValueAsString("SIDO_CODE"); ReportDAO rDAO = new ReportDAO(); deleteQuery.append( " DELETE FROM PT_C_RES_TRAFFIC_OUT WHERE SIDO_CODE = '" + SIDO_CODE + "' AND SEQ = '" + SEQ + "' "); rDAO.execute(deleteQuery.toString()); } }
/** * 문서 번호를 가져온다. * * @param String as_Table, String as_KeyCol * @return String */ public static String getSeqID(String as_KeyCol, String as_Table, String as_KeyVal) throws Exception { String sql = ""; ReportDAO rDAO = new ReportDAO(); sql = " SELECT \n" + " NVL( MAX( to_number(" + as_KeyCol + ") ), 0 ) + 1 SEQ_ID \n" + " FROM " + as_Table + " \n"; // " WHERE BOARD_SEQ ='"+as_KeyVal+"' \n" ; ReportEntity rEntity = null; rEntity = rDAO.select(sql); return rEntity.getValue(0, "SEQ_ID"); }
/** * 조건에 따른 list를 load * * @param HttpServletRequest, MainParam * @return */ private void loadSchedule(HttpServletRequest request, MainParam pm) throws Exception { ReportDAO rDAO = new ReportDAO(); ReportEntity rEntity = null; // SELECT 항목 SQL... String selectSQL = "SELECT \n" + " COUR_NAME||' ('||EDU_QUOTA||'명)' COUR_NAME, \n" + " DATE_FORMAT(DATE_FORMAT(ENTR_DATE,'%Y-%m-%d'),'%Y/%m/%d')||'~'||DATE_FORMAT(DATE_FORMAT(END_DATE,'%Y-%m-%d'),'%m/%d') PERIOD \n"; String fromSQL = "FROM \n" + " PT_EP_COUR_INFO \n"; String whereSQL = " \n " + "WHERE \n" + " ENTR_DATE LIKE '" + pm.getScMonth() + "%' \n"; String orderSQL = "ORDER BY ENTR_DATE DESC \n"; rEntity = rDAO.select(selectSQL + fromSQL + whereSQL + orderSQL); request.setAttribute("rEntity", rEntity); } // end loadList
private void insertCommentExe( String BOARD_SEQ, String setBOARD_SEQ, String CT_ID, String setCT_ID) throws Exception { ReportDAO rDAO = new ReportDAO(); ReportEntity rEntity = null; String UpSQL = " UPDATE PT_BBS_COM_COMMENT " + " SET CT_ID = ?, BOARD_SEQ = ?" + " WHERE CT_ID = ? AND BOARD_SEQ = ?"; int i = 0; rDAO.setValue(++i, setCT_ID); rDAO.setValue(++i, setBOARD_SEQ); rDAO.setValue(++i, CT_ID); rDAO.setValue(++i, BOARD_SEQ); rDAO.execute(UpSQL); }
/** * 사용전검사 현장검사관리 조회 * * @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); } }
/** * 공사업 등록기준 정보를 가져온다. * * @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); }
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; }
/** * 영업정지 리스트 정보를 가져온다. * * @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); }
private void insertBbsExe( HttpServletRequest request, ActionForm form, String max_sw, String Bbs, String TargetBbs, String CT_ID) throws Exception { ReportDAO rDAO = new ReportDAO(); ReportEntity rEntity = null; String selectSQL = " SELECT BOARD_SEQ,INDEX1,INDEX2,DEPTH,USER_ID,USER_NAME," + " USER_IP,SUBJECT,CONTENT,REPLIED_YN,SECRET_YN,NOTICE_YN,READ_NUM," + " UPD_DT,INS_DT,WRT_ID "; String fromSQL = " FROM " + Bbs + " ORDER BY BOARD_SEQ"; String BOARD_SEQ = ""; String INDEX1 = ""; String INDEX2 = ""; String DEPTH = ""; String USER_ID = ""; String USER_NAME = ""; String USER_IP = ""; String SUBJECT = ""; String CONTENT = ""; String REPLIED_YN = ""; String SECRET_YN = ""; String NOTICE_YN = ""; String READ_NUM = ""; String UPD_DT = ""; String INS_DT = ""; String WRT_ID = ""; String setBOARD_SEQ = ""; rEntity = rDAO.select(selectSQL + fromSQL); StringBuffer sbSQL = new StringBuffer(); sbSQL.append(" INSERT INTO " + TargetBbs + " ( \n"); sbSQL.append(" CT_ID, \n"); sbSQL.append(" BOARD_SEQ, \n"); sbSQL.append(" INDEX1, \n"); sbSQL.append(" INDEX2, \n"); sbSQL.append(" DEPTH, \n"); sbSQL.append(" USER_ID, \n"); sbSQL.append(" USER_NAME, \n"); sbSQL.append(" USER_IP, \n"); sbSQL.append(" SUBJECT, \n"); sbSQL.append(" CONTENT, \n"); sbSQL.append(" REPLIED_YN, \n"); sbSQL.append(" SECRET_YN, \n"); sbSQL.append(" NOTICE_YN, \n"); sbSQL.append(" READ_NUM, \n"); sbSQL.append(" UPD_DT, \n"); sbSQL.append(" INS_DT, \n"); sbSQL.append(" WRT_ID \n"); sbSQL.append(" ) \n"); sbSQL.append(" values ( \n"); sbSQL.append(" ?,?,?,?,?, \n"); sbSQL.append(" ?,?,?,?,?, \n"); sbSQL.append(" ?,?,?,?,?, \n"); sbSQL.append(" ?,? \n"); sbSQL.append(" ) \n"); for (int i = 0; i < rEntity.getRowCnt(); i++) { BOARD_SEQ = rEntity.getValue(i, "BOARD_SEQ"); INDEX1 = rEntity.getValue(i, "INDEX1"); INDEX2 = rEntity.getValue(i, "INDEX2"); DEPTH = rEntity.getValue(i, "DEPTH"); USER_ID = rEntity.getValue(i, "USER_ID"); USER_NAME = rEntity.getValue(i, "USER_NAME"); USER_IP = rEntity.getValue(i, "USER_IP"); SUBJECT = rEntity.getValue(i, "SUBJECT"); CONTENT = rEntity.getValue(i, "CONTENT"); REPLIED_YN = rEntity.getValue(i, "REPLIED_YN"); SECRET_YN = rEntity.getValue(i, "SECRET_YN"); NOTICE_YN = rEntity.getValue(i, "NOTICE_YN"); READ_NUM = rEntity.getValue(i, "READ_NUM"); UPD_DT = rEntity.getValue(i, "UPD_DT"); INS_DT = rEntity.getValue(i, "INS_DT"); WRT_ID = rEntity.getValue(i, "WRT_ID"); if ("1".equals(max_sw)) { setBOARD_SEQ = getSeqID("BOARD_SEQ", TargetBbs, BOARD_SEQ); int j = 0; rDAO.setValue(++j, CT_ID); rDAO.setValue(++j, setBOARD_SEQ); rDAO.setValue(++j, setBOARD_SEQ); rDAO.setValue(++j, INDEX2); rDAO.setValue(++j, DEPTH); rDAO.setValue(++j, USER_ID); rDAO.setValue(++j, USER_NAME); rDAO.setValue(++j, USER_IP); rDAO.setValue(++j, SUBJECT); rDAO.setValue(++j, CONTENT); rDAO.setValue(++j, REPLIED_YN); rDAO.setValue(++j, SECRET_YN); rDAO.setValue(++j, NOTICE_YN); rDAO.setValue(++j, READ_NUM); rDAO.setValue(++j, UPD_DT); rDAO.setValue(++j, INS_DT); rDAO.setValue(++j, WRT_ID); insertCommentExe(BOARD_SEQ, BOARD_SEQ, Bbs.substring(7, Bbs.length()), CT_ID); insertFileExe(BOARD_SEQ, BOARD_SEQ, Bbs.substring(7, Bbs.length()), CT_ID); } else { int j = 0; rDAO.setValue(++j, CT_ID); rDAO.setValue(++j, BOARD_SEQ); rDAO.setValue(++j, INDEX1); rDAO.setValue(++j, INDEX2); rDAO.setValue(++j, DEPTH); rDAO.setValue(++j, USER_ID); rDAO.setValue(++j, USER_NAME); rDAO.setValue(++j, USER_IP); rDAO.setValue(++j, SUBJECT); rDAO.setValue(++j, CONTENT); rDAO.setValue(++j, REPLIED_YN); rDAO.setValue(++j, SECRET_YN); rDAO.setValue(++j, NOTICE_YN); rDAO.setValue(++j, READ_NUM); rDAO.setValue(++j, UPD_DT); rDAO.setValue(++j, INS_DT); rDAO.setValue(++j, WRT_ID); insertCommentExeOnly(Bbs.substring(7, Bbs.length()), CT_ID); insertFileExeOnly(Bbs.substring(7, Bbs.length()), CT_ID); } System.out.println("갯수" + i); rDAO.execute(sbSQL.toString()); // KJFLog.log(i +" " + temp.toString()); } }
/** * 입력 * * @param HttpServletRequest * @return */ private void insertExe(HttpServletRequest request, ActionForm form) throws Exception { ReportDAO rDAO = new ReportDAO(); ReportEntity rEntity = null; String selectSQL = " SELECT A.CUD_FLAG,A.SIDO_CODE,A.COI_WRT_NUM, A.MANA_NUM, A.ASSE_DT, A.ASSE_AOM, A.APPL_TERM_START_DT, A.APPL_TERM_END_DT, A.KICA_INS_DT," + " (SELECT TMP_WRT_NUM FROM PT_R_COMPANY_MASTER WHERE COI_WRT_NUM = A.COI_WRT_NUM) AS TMP_WRT_NUM," + " (CASE WHEN (SELECT SIDO_CODE FROM PT_R_COMPANY_MASTER WHERE SIDO_CODE IS NOT NULL AND COI_WRT_NUM = A.COI_WRT_NUM) = A.SIDO_CODE THEN 'SIDO_EQ' ELSE 'SIDO_U' END) AS SIDO_CHK, " + " (CASE WHEN (SELECT MANA_NUM FROM PT_R_COMPANY_MASTER WHERE MANA_NUM IS NOT NULL AND COI_WRT_NUM = A.COI_WRT_NUM) = A.MANA_NUM THEN 'MANA_EQ' ELSE 'MANA_U' END) AS MANA_CHK "; String fromSQL = " FROM PT_TMP_WORK_CAPABILITY A"; String temp = ""; String temp_etc = ""; rEntity = rDAO.select(selectSQL + fromSQL); String MANA_CHK = ""; String SIDO_CHK = ""; for (int i = 0; i < rEntity.getRowCnt(); i++) { MANA_CHK = rEntity.getValue(i, "MANA_CHK"); SIDO_CHK = rEntity.getValue(i, "SIDO_CHK"); if ("C".equals(rEntity.getValue(i, "CUD_FLAG"))) { String SEQ = getSeqID("SEQ", "PT_R_WORK_CAPABILITY", rEntity.getValue(i, "TMP_WRT_NUM")); temp = "INSERT INTO PT_R_WORK_CAPABILITY ( SEQ, TMP_WRT_NUM, ASSE_DT, ASSE_AOM, APPL_TERM_START_DT,APPL_TERM_END_DT, INS_DT ) " + " VALUES ('" + SEQ + "','" + rEntity.getValue(i, "TMP_WRT_NUM") + "','" + rEntity.getValue(i, "ASSE_DT").substring(0, 4) + "','" + rEntity.getValue(i, "ASSE_AOM") + "','" + rEntity.getValue(i, "APPL_TERM_START_DT") + "','" + rEntity.getValue(i, "APPL_TERM_END_DT") + "','" + rEntity.getValue(i, "KICA_INS_DT") + "')"; } else if ("U".equals(rEntity.getValue(i, "CUD_FLAG"))) { temp = ""; } else if ("D".equals(rEntity.getValue(i, "CUD_FLAG"))) { temp = "DELETE FROM PT_R_WORK_CAPABILITY " + " WHERE TMP_WRT_NUM = '" + rEntity.getValue(i, "TMP_WRT_NUM") + "'"; } if (!"".equals(temp)) { System.out.println( rEntity.getValue(i, "CUD_FLAG") + " " + rEntity.getValue(i, "KICA_INS_DT") + " " + rEntity.getValue(i, "COI_WRT_NUM") + " " + i + " " + temp); } // KJFLog.log(i +" " + temp.toString()); } }