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());
}
}
