/** * @(#)file SignThreadBiz.java * @(#)author DaeJin Lee * @(#)version 1.0 * @(#)date Jun 10, 2005 * @(#)since JDK 1.4.2 * * Copyright (c) www.dcchem.co.kr, Inc. * All rights reserved. * This software is the proprietary information of dcchem, Inc. */ package kr.co.udapsoft.common.commonSign; import java.sql.Connection; import kr.co.hsnc.common.base.WAFLogger; import kr.co.hsnc.common.logger.Logger; import kr.co.hsnc.common.sql.Row; import kr.co.hsnc.common.sql.RowImpl; import kr.co.hsnc.common.sql.RowSet; import kr.co.hsnc.common.sql.RowSetImpl; import kr.co.hsnc.common.sql.WAFConnection; import kr.co.hsnc.common.sql.WAFConnectionImpl; import kr.co.hsnc.common.sql.WAFSQLException; import kr.co.hsnc.common.sql.persistent.Persistent; import kr.co.hsnc.common.sql.persistent.PersistentImpl; import kr.co.hsnc.common.sql.search.Search; import kr.co.hsnc.common.sql.search.SearchImpl; import kr.co.hsnc.common.util.ValueObject; import java.text.SimpleDateFormat; import java.util.Date; import kr.co.udapsoft.common.sender.MessageSend; import com.udapsoft.waf.system.HandlerStorage; import oracle.toplink.internal.ox.StrBuffer; /** * * @version 1.0 * @author DaeJin Lee */ public class CommonMailThreadBiz { private WAFConnection conn = null; public CommonMailThreadBiz() { this.conn = new WAFConnectionImpl(); } /** * 전자결재 처리결과 수신 Biz */ public void doBiz() throws Exception { try { long time = System.currentTimeMillis(); SimpleDateFormat dayTime = new SimpleDateFormat("HHmm"); String str = dayTime.format(new Date(time)); if (str.equals("0800") || str.equals("0801")){ //오전08시 실행. try{ RowSet rowTread = getBidThread(); sendBidMail(rowTread); //당일 마감입찰 RowSet rowCont = getContThread(); sendContMail(rowCont); //계약 20일 초과 하도급미발행 //2019.11.29 추가 Start //RowSet rowPurContEnd = getPurContEndThread(); //sendContEndMail(rowPurContEnd); //구매계약 만료 30일전 알림 RowSet rowOutContEnd = getOutContEndThread(); sendContEndMail(rowOutContEnd); //외주계약 만료 30일전 알림 //2019.11.29 추가 End } catch(Exception se){ WAFLogger.error(this.getClass().getName() + " --> 입찰마감오류() : " + se.getMessage()); WAFLogger.error(se); throw se; } } try{ conn.begin(); //구매변경계약 업데이트 Row row = new RowImpl(); String maxPurContid = new String(); RowSet rowChgTread = getChgContThread(); for ( int i = 0 ; i < rowChgTread.size() ; i ++ ) { row = rowChgTread.getRow(i); //구매 변경계약 PUR2000T로 INSERT maxPurContid = getMaxPurCntId(); addChangeContItem(conn.getConnection(), row.get("DEMANDED_ID"), maxPurContid, row.get("PUR_CONT_MOD_ID")); //변경계약 아이템 저장 addChangeContPur(conn.getConnection(), row.get("DEMANDED_ID"), maxPurContid, row.get("PUR_CONT_MOD_ID") ); //변경 계약 추가 addChangeContSupport(conn.getConnection(), row.get("PUR_CONT_MOD_ID"), maxPurContid, row.get("PUR_CONT_MOD_ID")); //변경 계약 업체추가 } RowSet rowMail = getMailThread(); //발주의뢰, 변경계약 메일알림 sendMail(rowMail); updateMail(conn.getConnection(), rowMail); } catch(Exception se){ WAFLogger.error(this.getClass().getName() + " --> doBiz() : " + se.getMessage()); WAFLogger.error(se); conn.setRollback(); throw se; } finally { conn.end(); } } catch(Exception se) { WAFLogger.error(this.getClass().getName() + " --> setInsert() : " + se.getMessage()); WAFLogger.error(se); throw se; } } private RowSet getBidThread() throws Exception{ Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); StringBuffer sqlstr = new StringBuffer(); try { //개찰알림메일 sqlstr.append(" SELECT A.DEPT_CD \n"); sqlstr.append(" ,A.DEPT_NM \n"); sqlstr.append(" ,A.BID_MASTER_NM \n"); sqlstr.append(" ,A.BID_NO \n"); sqlstr.append(" ,A.BID_DEG \n"); sqlstr.append(" ,TO_CHAR(A.BID_TO_DT , 'YYYY-MM-DD HH24:MI') BID_TO_DT \n"); sqlstr.append(" ,A.PUREMPNO \n"); sqlstr.append(" ,B.USER_SABUN \n"); sqlstr.append(" ,B.USER_NAME \n"); sqlstr.append(" ,B.HAND_TEL_NO \n"); sqlstr.append(" ,B.E_MAIL \n"); sqlstr.append(" ,A.OPEN_EMPNM \n"); sqlstr.append(" FROM BID_MASTER A \n"); sqlstr.append(" ,PUB0120T B \n"); sqlstr.append(" WHERE A.PUREMPNO = B.ACCT_ID \n"); sqlstr.append(" AND A.ONBID_YN = 'Y' \n"); sqlstr.append(" AND A.STATUS_CD = '04' \n"); sqlstr.append(" AND DEMANDED_KIND != 'O' \n"); sqlstr.append(" AND TO_CHAR(BID_TO_DT, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD') \n"); search.setStatement(sqlstr.toString()); rowSet = search.execute(); } catch(WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); throw se; } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } private RowSet getContThread() throws Exception{ Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); StringBuffer sqlstr = new StringBuffer(); try { //하도급 지급보증 발급. 계약일로 부터 20일 . sqlstr.append("SELECT A.DEPT_CD \n"); sqlstr.append(" ,A.DEPT_NAME \n"); sqlstr.append(" ,A.ORD_DOC_NO \n"); sqlstr.append(" ,A.CST_DOC_NAME \n"); sqlstr.append(" ,TO_CHAR(A.ORD_CON_DATE, 'YYYY-MM-DD') ORD_CON_DATE \n"); sqlstr.append(" ,A.CON_CHARGE_ID \n"); sqlstr.append(" ,B.USER_NAME \n"); sqlstr.append(" ,B.HAND_TEL_NO \n"); sqlstr.append(" ,B.E_MAIL \n"); sqlstr.append(" ,C.VENDCD \n"); sqlstr.append(" ,C.MEMBER_NAME \n"); sqlstr.append(" ,D.PUR_CONT_MOD_ID \n"); sqlstr.append(" FROM PUR2000T A \n"); sqlstr.append(" ,PUB0120T B \n"); sqlstr.append(" ,CONT_PUR_SUPP C \n"); sqlstr.append(" ,CONT_PUR_WARR D \n"); sqlstr.append(" WHERE A.CON_CHARGE_ID = B.ACCT_ID \n"); sqlstr.append(" AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID \n"); sqlstr.append(" AND A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID(+) \n"); sqlstr.append(" AND A.STATUS_CD = '50' \n"); sqlstr.append(" AND A.TEMPLATE_CD = '15' \n"); sqlstr.append(" AND C.SIGN_SEQ = 2 \n"); sqlstr.append(" AND D.WARR_GUBUN(+) = '2' \n"); sqlstr.append(" AND D.PUR_CONT_MOD_ID IS NULL \n"); sqlstr.append(" AND TO_DATE(A.ORD_CON_DATE) + 19 > SYSDATE \n");//계약일 20일이후 부터 . 금액제한 확인 search.setStatement(sqlstr.toString()); rowSet = search.execute(); } catch(WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); throw se; } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } private RowSet getMailThread() throws Exception{ Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); StringBuffer sqlstr = new StringBuffer(); try { //발주의뢰, 변경계약 메일발송 sqlstr.append("SELECT A.MAIL_ID --발주의뢰 \n"); sqlstr.append(" ,A.SEND_GB \n"); sqlstr.append(" ,GET_DEPT_NM(B.DEPT_CD) DEPT_NM \n"); sqlstr.append(" ,B.DEMANDED_NO DEM_NO \n"); sqlstr.append(" ,B.DEMANDED_TITLE DEM_TITLE \n"); sqlstr.append(" ,A.ACCT_ID \n"); sqlstr.append(" ,A.ADDR E_MAIL \n"); sqlstr.append(" FROM T_MAIL A \n"); sqlstr.append(" ,PMM_DEMANDED_REQUEST B \n"); sqlstr.append(" WHERE A.DEM_ID = B.DEMANDED_ID \n"); sqlstr.append(" AND A.ATTR_1 = 0 \n"); sqlstr.append(" AND A.SEND_GB = 'ORD' \n"); sqlstr.append(" UNION ALL --외주변경 \n"); sqlstr.append("SELECT A.MAIL_ID \n"); sqlstr.append(" ,A.SEND_GB \n"); sqlstr.append(" ,B.DEPT_NAME DEPT_NM \n"); sqlstr.append(" ,B.CON_DOC_NO DEM_NO \n"); sqlstr.append(" ,B.BUILD_NAME DEM_TITLE \n"); sqlstr.append(" ,A.ACCT_ID \n"); sqlstr.append(" ,A.ADDR E_MAIL \n"); sqlstr.append(" FROM T_MAIL A \n"); sqlstr.append(" ,SUB2000T B \n"); sqlstr.append(" WHERE A.DEM_ID = B.SUB_CONT_MOD_ID \n"); sqlstr.append(" AND A.ATTR_1 = 0 \n"); sqlstr.append(" AND A.SEND_GB = 'OCHG' \n"); sqlstr.append(" UNION ALL --구매변경 \n"); sqlstr.append("SELECT A.MAIL_ID \n"); sqlstr.append(" ,A.SEND_GB \n"); sqlstr.append(" ,C.DEPT_NAME DEPT_NM \n"); sqlstr.append(" ,C.ORD_DOC_NO DEM_NO \n"); sqlstr.append(" ,C.CST_DOC_NAME DEM_TITLE \n"); sqlstr.append(" ,D.ACCT_ID \n"); sqlstr.append(" ,D.E_MAIL E_MAIL \n"); sqlstr.append(" FROM T_MAIL A \n"); sqlstr.append(" ,PMM_DEMANDED_REQUEST B \n"); sqlstr.append(" ,PUR1000T C \n"); sqlstr.append(" ,PUB0120T D \n"); sqlstr.append(" WHERE A.DEM_ID = B.DEMANDED_ID \n"); sqlstr.append(" AND B.DEMANDED_NO = C.REQ_DOC_NO \n"); sqlstr.append(" AND C.CON_CHARGE_ID = D.ACCT_ID \n"); sqlstr.append(" AND A.ATTR_1 = 0 \n"); sqlstr.append(" AND A.SEND_GB = 'PCHG' \n"); search.setStatement(sqlstr.toString()); rowSet = search.execute(); } catch(WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); throw se; } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } private void updateMail(Connection connection,RowSet rowTread) throws Exception{ Persistent persistent = new PersistentImpl(connection); StringBuffer sqlstr = new StringBuffer(); Row row = new RowImpl(); try { sqlstr.append("UPDATE T_MAIL \n"); sqlstr.append(" SET ATTR_1 = 1 \n"); sqlstr.append(" ,UPD_DATE = SYSDATE \n"); sqlstr.append(" WHERE MAIL_ID = ? \n"); persistent.setStatement(sqlstr.toString()); for ( int i = 0 ; i < rowTread.size() ; i ++ ) { row = rowTread.getRow(i); persistent.addParameter(row.get("MAIL_ID")); persistent.execute(); persistent.clearParameters(); } } catch(WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); throw se; } } private void sendBidMail(RowSet rowTread) throws Exception{ try { HandlerStorage Stroage = new HandlerStorage(); MessageSend messageSend = new MessageSend(Stroage); StringBuffer mailtitle = new StringBuffer(); StringBuffer mailtitlein = new StringBuffer(); StringBuffer mailContent = new StringBuffer(); Row row = new RowImpl(); mailtitle.append("*[알림]금일 입찰마감 안내입니다. "); mailtitlein.append("*[알림]금일 입찰마감 안내입니다. "); for ( int i = 0 ; i < rowTread.size() ; i ++ ) { row = rowTread.getRow(i); mailContent.append(""); mailContent.append("금일 입찰이 마감되니 확인 후 조치하여 주시기 바랍니다.
"); mailContent.append("
"); mailContent.append("프로젝트: " + row.get("DEPT_NM") + "
"); mailContent.append("입찰번호: " + row.get("BID_NO") + "-" + row.get("BID_DEG") + "
"); mailContent.append("입찰명: " + row.get("BID_MASTER_NM") + "
"); mailContent.append("마감시간: " + row.get("BID_TO_DT") + "
"); mailContent.append("
"); mailContent.append("
"); if (row.get("E_MAIL").length() > 4 ){ messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N"); } mailContent = new StringBuffer(); } } catch(Exception e) { Logger.err.println("CommonMailThreadBiz.sendBidMail() 실행중 오류가 발생 했습니다.\\n" + e.getMessage()); throw e; } } private void sendContMail(RowSet rowTread){ try { HandlerStorage Stroage = new HandlerStorage(); MessageSend messageSend = new MessageSend(Stroage); StringBuffer mailtitle = new StringBuffer(); StringBuffer mailtitlein = new StringBuffer(); StringBuffer mailContent = new StringBuffer(); Row row = new RowImpl(); mailtitle.append("*[알림]하도급 지급보증 발급을 처리해 주십시요."); mailtitlein.append("*[알림]하도급 지급보증 발급을 처리해 주십시요."); for ( int i = 0 ; i < rowTread.size() ; i ++ ) { row = rowTread.getRow(i); mailContent.append(""); mailContent.append("건설공사 표준하도급 계약이후 하도급지급보증 발급이 처리되지 않았습니다. 확인 후 조치하여 주시기 바랍니다.
"); mailContent.append("
"); mailContent.append("프로젝트: " + row.get("DEPT_NAME") + "
"); mailContent.append("계약번호: " + row.get("ORD_DOC_NO") + "
"); mailContent.append("계약명: " + row.get("CST_DOC_NAME") + "
"); mailContent.append("거래처: " + row.get("MEMBER_NAME") + "
"); mailContent.append("계약일자: " + row.get("ORD_CON_DATE") + "
"); mailContent.append(row.get("E_MAIL")); mailContent.append("
"); mailContent.append("
"); if (row.get("E_MAIL").length() > 4 ){ messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N"); } mailContent = new StringBuffer(); } } catch(Exception e) { Logger.err.println("doBiz() 실행중 오류가 발생 했습니다.\\n" + e.getMessage()); } } private void sendMail(RowSet rowTread){ try { HandlerStorage Stroage = new HandlerStorage(); MessageSend messageSend = new MessageSend(Stroage); StringBuffer mailtitle = new StringBuffer(); StringBuffer mailtitlein = new StringBuffer(); StringBuffer mailContent = new StringBuffer(); Row row = new RowImpl(); for ( int i = 0 ; i < rowTread.size() ; i ++ ) { row = new RowImpl(); row = rowTread.getRow(i); if (row.get("SEND_GB").equals("ORD")){ mailtitle.append("*[알림]발주의뢰가 등록 되었습니다. "); mailtitlein.append("*[알림]발주의뢰가 등록 되었습니다. "); row = rowTread.getRow(i); mailContent.append(""); mailContent.append("발주의뢰가 등록되었습니다.
"); mailContent.append("
"); mailContent.append("프로젝트: " + row.get("DEPT_NM") + "
"); mailContent.append("발주의뢰번호: " + row.get("DEM_NO") + "
"); mailContent.append("발주의뢰명: " + row.get("DEM_TITLE") + "
"); mailContent.append("전자조달시스템의 발주의뢰 접수 확인 바랍니다.
"); mailContent.append("
"); mailContent.append("
"); if (row.get("E_MAIL").length() > 4 ){ messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N"); } mailContent = new StringBuffer(); mailtitle = new StringBuffer(); mailtitlein = new StringBuffer(); }else if(row.get("SEND_GB").equals("PCHG")){ mailtitle.append("*[알림]변경계약이 등록 되었습니다. "); mailtitlein.append("*[알림]변경계약이 등록 되었습니다. "); row = rowTread.getRow(i); mailContent.append(""); mailContent.append("변경계약 요청건이 있습니다.
"); mailContent.append("
"); mailContent.append("프로젝트: " + row.get("DEPT_NM") + "
"); mailContent.append("계약번호: " + row.get("DEM_NO") + "
"); mailContent.append("계약명: " + row.get("DEM_TITLE") + "
"); mailContent.append("전자조달시스템의 변경계약접수 확인 바랍니다.
"); mailContent.append("
"); mailContent.append("
"); if (row.get("E_MAIL").length() > 4 ){ messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N"); } mailContent = new StringBuffer(); mailtitle = new StringBuffer(); mailtitlein = new StringBuffer(); }else if(row.get("SEND_GB").equals("OCHG")){ mailtitle.append("*[알림]변경계약이 등록 되었습니다. "); mailtitlein.append("*[알림]변경계약이 등록 되었습니다. "); row = rowTread.getRow(i); mailContent.append(""); mailContent.append("변경계약 요청건이 있습니다.
"); mailContent.append("
"); mailContent.append("프로젝트: " + row.get("DEPT_NM") + "
"); mailContent.append("계약번호: " + row.get("DEM_NO") + "
"); mailContent.append("계약명: " + row.get("DEM_TITLE") + "
"); mailContent.append("전자조달시스템의 변경계약접수 확인 바랍니다.
"); mailContent.append("
"); mailContent.append("
"); if (row.get("E_MAIL").length() > 4 ){ messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N"); } mailContent = new StringBuffer(); mailtitle = new StringBuffer(); mailtitlein = new StringBuffer(); } } } catch(Exception e) { Logger.err.println("doBiz() 실행중 오류가 발생 했습니다.\\n" + e.getMessage()); } } private RowSet getChgContThread() throws Exception{ Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); StringBuffer sqlstr = new StringBuffer(); try { //구매변경계약 sqlstr.append(" SELECT A.DEM_ID DEMANDED_ID \n"); sqlstr.append(" ,MAX(C.PUR_CONT_MOD_ID) PUR_CONT_MOD_ID \n"); sqlstr.append(" FROM T_MAIL A \n"); sqlstr.append(" ,PMM_DEMANDED_REQUEST B \n"); sqlstr.append(" ,PUR2000T C \n"); sqlstr.append(" WHERE A.DEM_ID = B.DEMANDED_ID \n"); sqlstr.append(" AND B.DEMANDED_NO = C.REQ_DOC_NO \n"); sqlstr.append(" AND A.SEND_GB = 'PCHG' \n"); sqlstr.append(" AND A.ATTR_1 = 0 \n"); sqlstr.append(" AND C.STATUS_CD = '50' \n"); sqlstr.append(" AND NVL(DIV_PO_YN,'N') != 'Y' \n"); //단가계약 아닌자료 sqlstr.append(" GROUP BY A.DEM_ID \n"); //단가계약 추가 2019.04.15 sqlstr.append(" UNION ALL \n"); sqlstr.append(" SELECT MAX(A.DEM_ID) DEMANDED_ID \n"); sqlstr.append(" ,MAX(C.PUR_CONT_MOD_ID) PUR_CONT_MOD_ID \n"); sqlstr.append(" FROM T_MAIL A \n"); sqlstr.append(" ,PMM_DEMANDED_REQUEST B \n"); sqlstr.append(" ,PUR2000T C \n"); sqlstr.append(" WHERE A.DEM_ID = B.DEMANDED_ID \n"); sqlstr.append(" AND B.DEMANDED_NO = C.REQ_DOC_NO \n"); sqlstr.append(" AND A.SEND_GB = 'PCHG' \n"); sqlstr.append(" AND A.ATTR_1 = 0 \n"); sqlstr.append(" AND C.STATUS_CD = '50' \n"); sqlstr.append(" AND DIV_PO_YN = 'Y' \n"); //단가계약 자료 sqlstr.append(" GROUP BY C.PUR_CONT_ID \n"); search.setStatement(sqlstr.toString()); rowSet = search.execute(); } catch(WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); throw se; } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } private String getMaxPurCntId() { Search search = new SearchImpl(); String sqlstr = ""; try { sqlstr = "SELECT PUR2000T_S.NEXTVAL AS MAX_PUR_CONT_MOD_ID \n" + " FROM DUAL\n"; search.setStatement(sqlstr); return search.execute().getRow(0).get("MAX_PUR_CONT_MOD_ID"); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> getMaxPurCntId() : " + e.getMessage()); WAFLogger.error("SQL : " + search.getStatement()); WAFLogger.error(e); return null; } } public boolean addChangeContPur(Connection connection, String DEMANDED_ID, String maxPurContid, String prePurContid) throws Exception { Persistent persistent = new PersistentImpl(connection); StrBuffer sqlstr = new StrBuffer(); try { sqlstr.append(" INSERT INTO PUR2000T \n"); sqlstr.append(" ( PUR_CONT_MOD_ID \n"); sqlstr.append(" , PUR_CONT_ID \n"); sqlstr.append(" , MOD_NO \n"); sqlstr.append(" , MOD_GB \n"); sqlstr.append(" , MOD_RSN \n"); sqlstr.append(" , MOD_REQ_DATE \n"); sqlstr.append(" , MOD_CORP_RSN \n"); sqlstr.append(" , DEPT_CD \n"); sqlstr.append(" , DEPT_NAME \n"); sqlstr.append(" , CON_GB \n"); sqlstr.append(" , ORD_DOC_NO \n"); sqlstr.append(" , CST_DOC_NAME \n"); sqlstr.append(" , REQ_DEPT_CD \n"); sqlstr.append(" , REQ_DATE \n"); sqlstr.append(" , REQ_USERID \n"); sqlstr.append(" , REQ_USERNM \n"); sqlstr.append(" , REQ_DOC_NO \n"); sqlstr.append(" , CST_DATE \n"); sqlstr.append(" , CON_IN_DATE \n"); sqlstr.append(" , ELEC_CON_YN \n"); sqlstr.append(" , ORD_CON_DATE \n"); sqlstr.append(" , PAY_COND_GB \n"); sqlstr.append(" , PAY_CONTENT \n"); sqlstr.append(" , LEAD_COND_GB \n"); sqlstr.append(" , LEAD_PLACE \n"); sqlstr.append(" , CORP_ID \n"); sqlstr.append(" , ORD_START_DATE \n"); sqlstr.append(" , ORD_END_DATE \n"); sqlstr.append(" , ORD_SEND_DATE \n"); sqlstr.append(" , ORD_AMT \n"); sqlstr.append(" , ORD_SUPPLY_AMT \n"); sqlstr.append(" , ORD_SURTAX_AMT \n"); sqlstr.append(" , PREPAY_AMT \n"); sqlstr.append(" , MID_AMT \n"); sqlstr.append(" , RMDR_AMT \n"); sqlstr.append(" , PREPAY_EXE_YN \n"); sqlstr.append(" , PREPAY_GUAR_AMT \n"); sqlstr.append(" , CON_GUAR_AMT_RATE \n"); sqlstr.append(" , CON_GUAR_AMT \n"); sqlstr.append(" , CON_GUAR_TERM \n"); sqlstr.append(" , DEF_GUAR_AMT_RATE \n"); sqlstr.append(" , DEF_GUAR_AMT \n"); sqlstr.append(" , DEF_GUAR_TERMS \n"); sqlstr.append(" , DEF_GUAR_TERMS_TXT \n"); sqlstr.append(" , DEF_GUAR_TERMS2 \n"); sqlstr.append(" , DEF_GUAR_TERMS_TXT2 \n"); sqlstr.append(" , DEL_RATE_GB \n"); sqlstr.append(" , CON_CHARGE_ID \n"); sqlstr.append(" , CON_CHARGE_NAME \n"); sqlstr.append(" , CON_DISPLAY_TXT \n"); sqlstr.append(" , ADD_FILE_KIND \n"); sqlstr.append(" , CORP_SIGN_DATE \n"); sqlstr.append(" , COMPANY_SIGN_DATE \n"); sqlstr.append(" , ENT_CLS_YN \n"); sqlstr.append(" , PAY_GROUP \n"); sqlstr.append(" , PAYMENT_TERMS \n"); sqlstr.append(" , ACCT_GB \n"); sqlstr.append(" , GUA_GB \n"); sqlstr.append(" , CURRENCY_GB \n"); sqlstr.append(" , PAY_ITEM \n"); sqlstr.append(" , COST_CODE \n"); sqlstr.append(" , COST_TYPE \n"); sqlstr.append(" , ONSHORE_CD \n"); sqlstr.append(" , CONTRACTID \n"); sqlstr.append(" , G_ID \n"); sqlstr.append(" , P_ID \n"); sqlstr.append(" , PREPAY_AMT_RATE \n"); sqlstr.append(" , PREPAY_PAYMENT_NM \n"); sqlstr.append(" , MID_AMT_RATE \n"); sqlstr.append(" , MID_PAYMENT_NM \n"); sqlstr.append(" , MID_PAYMENT_TXT \n"); sqlstr.append(" , RMDR_AMT_RATE \n"); sqlstr.append(" , RMDR_PAYMENT_NM \n"); sqlstr.append(" , RMDR_PAYMENT_TXT \n"); sqlstr.append(" , PREPAY_GUAR_AMT_RATE \n"); sqlstr.append(" , OVERSEA_GB \n"); sqlstr.append(" , PUR_LIST_GB \n"); sqlstr.append(" , CON_RUN_STS \n"); sqlstr.append(" , BID_NO \n"); sqlstr.append(" , BID_DEG \n"); sqlstr.append(" , STATUS_CD \n"); sqlstr.append(" , TEMPLATE_CD \n"); sqlstr.append(" , MODI_REQ_REASON \n"); sqlstr.append(" , MODI_REQ_DT \n"); sqlstr.append(" , CON_CLS_YN \n"); sqlstr.append(" , PAY_DIV \n"); sqlstr.append(" , DEM_ID \n"); sqlstr.append(" , IN_SPE_INFO \n"); sqlstr.append(" , IN_CHG_INFO \n"); sqlstr.append(" , FRST_CON_AMT \n"); sqlstr.append(" , FRST_EXE_AMT \n"); sqlstr.append(" , CHG_CON_AMT \n"); sqlstr.append(" , CHG_EXE_AMT \n"); sqlstr.append(" , CRE_BY \n"); sqlstr.append(" , CRE_DATE \n"); sqlstr.append(" , DIV_PO_YN ,ERP_NO_ECUT ) \n"); //2019.04.26 신용길부장님 요청. 같은계약은 ERP_NO_ECUT동일하게. sqlstr.append(" SELECT ? \n"); sqlstr.append(" , A.PUR_CONT_ID \n"); sqlstr.append(" , MOD_NO + 1 \n"); sqlstr.append(" , B.MOD_GB \n"); sqlstr.append(" , NULL \n"); sqlstr.append(" , NULL \n"); sqlstr.append(" , NULL \n"); sqlstr.append(" , A.DEPT_CD \n"); sqlstr.append(" , A.DEPT_NAME \n"); sqlstr.append(" , A.CON_GB \n"); sqlstr.append(" , A.ORD_DOC_NO \n"); sqlstr.append(" , A.CST_DOC_NAME \n"); sqlstr.append(" , A.REQ_DEPT_CD \n"); sqlstr.append(" , A.REQ_DATE \n"); sqlstr.append(" , A.REQ_USERID \n"); sqlstr.append(" , A.REQ_USERNM \n"); sqlstr.append(" , A.REQ_DOC_NO \n"); sqlstr.append(" , A.CST_DATE \n"); sqlstr.append(" , A.CON_IN_DATE \n"); sqlstr.append(" , A.ELEC_CON_YN \n"); sqlstr.append(" , A.ORD_CON_DATE \n"); sqlstr.append(" , A.PAY_COND_GB \n"); sqlstr.append(" , A.PAY_CONTENT \n"); sqlstr.append(" , A.LEAD_COND_GB \n"); sqlstr.append(" , A.LEAD_PLACE \n"); sqlstr.append(" , A.CORP_ID \n"); sqlstr.append(" , A.ORD_START_DATE \n"); sqlstr.append(" , TO_DATE(B.AFT_CONT_DATE,'YYYYMMDD') AS ORD_END_DATE \n"); sqlstr.append(" , A.ORD_SEND_DATE \n"); sqlstr.append(" , CASE WHEN A.ORD_SURTAX_AMT > 0 THEN SUM_ITEM_AMT + TRUNC(SUM_ITEM_AMT / 10, F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) -- 발주계약금액 \n"); sqlstr.append(" ELSE SUM_ITEM_AMT END ORD_AMT -- 발주계약금액 \n"); sqlstr.append(" , SUM_ITEM_AMT \n"); sqlstr.append(" , CASE WHEN A.ORD_SURTAX_AMT > 0 THEN TRUNC(SUM_ITEM_AMT / 10,F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) -- 부가세 \n"); sqlstr.append(" ELSE 0 END ORD_SURTAX_AMT -- 부가세 \n"); sqlstr.append(" ,TRUNC(SUM_ITEM_AMT * (PREPAY_AMT_RATE / 100), F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) PREPAY_AMT -- 선급금 \n"); sqlstr.append(" ,TRUNC(SUM_ITEM_AMT * (MID_AMT_RATE / 100),F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) MID_AMT -- 중도금 \n"); sqlstr.append(" ,TRUNC(SUM_ITEM_AMT * (RMDR_AMT_RATE / 100),F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) RMDR_AMT -- 잔금 \n"); sqlstr.append(" , A.PREPAY_EXE_YN \n"); sqlstr.append(" , A.PREPAY_GUAR_AMT \n"); sqlstr.append(" , A.CON_GUAR_AMT_RATE \n"); sqlstr.append(" , A.CON_GUAR_AMT \n"); sqlstr.append(" , A.CON_GUAR_TERM \n"); sqlstr.append(" , A.DEF_GUAR_AMT_RATE \n"); sqlstr.append(" , A.DEF_GUAR_AMT \n"); sqlstr.append(" , A.DEF_GUAR_TERMS \n"); sqlstr.append(" , A.DEF_GUAR_TERMS_TXT \n"); sqlstr.append(" , A.DEF_GUAR_TERMS2 \n"); sqlstr.append(" , A.DEF_GUAR_TERMS_TXT2 \n"); sqlstr.append(" , A.DEL_RATE_GB \n"); sqlstr.append(" , A.CON_CHARGE_ID \n"); sqlstr.append(" , A.CON_CHARGE_NAME \n"); sqlstr.append(" , A.CON_DISPLAY_TXT \n"); sqlstr.append(" , A.ADD_FILE_KIND \n"); sqlstr.append(" , A.CORP_SIGN_DATE \n"); sqlstr.append(" , A.COMPANY_SIGN_DATE \n"); sqlstr.append(" , A.ENT_CLS_YN \n"); sqlstr.append(" , A.PAY_GROUP \n"); sqlstr.append(" , A.PAYMENT_TERMS \n"); sqlstr.append(" , A.ACCT_GB \n"); sqlstr.append(" , A.GUA_GB \n"); sqlstr.append(" , A.CURRENCY_GB \n"); sqlstr.append(" , A.PAY_ITEM \n"); sqlstr.append(" , A.COST_CODE \n"); sqlstr.append(" , A.COST_TYPE \n"); sqlstr.append(" , A.ONSHORE_CD \n"); sqlstr.append(" , A.CONTRACTID \n"); sqlstr.append(" , A.G_ID \n"); sqlstr.append(" , A.P_ID \n"); sqlstr.append(" , A.PREPAY_AMT_RATE \n"); sqlstr.append(" , A.PREPAY_PAYMENT_NM \n"); sqlstr.append(" , A.MID_AMT_RATE \n"); sqlstr.append(" , A.MID_PAYMENT_NM \n"); sqlstr.append(" , A.MID_PAYMENT_TXT \n"); sqlstr.append(" , A.RMDR_AMT_RATE \n"); sqlstr.append(" , A.RMDR_PAYMENT_NM \n"); sqlstr.append(" , A.RMDR_PAYMENT_TXT \n"); sqlstr.append(" , A.PREPAY_GUAR_AMT_RATE \n"); sqlstr.append(" , A.OVERSEA_GB \n"); sqlstr.append(" , A.PUR_LIST_GB \n"); sqlstr.append(" , A.CON_RUN_STS \n"); sqlstr.append(" , A.BID_NO \n"); sqlstr.append(" , A.BID_DEG \n"); sqlstr.append(" , '00' \n"); sqlstr.append(" , NULL \n"); sqlstr.append(" , A.MODI_REQ_REASON \n"); sqlstr.append(" , A.MODI_REQ_DT \n"); sqlstr.append(" , 1 \n"); sqlstr.append(" , A.PAY_DIV \n"); sqlstr.append(" , B.DEMANDED_ID \n"); sqlstr.append(" , B.P_REMARK \n"); sqlstr.append(" , B.S_REMARK \n"); sqlstr.append(" , A.CHG_CON_AMT \n"); sqlstr.append(" , B.BEF_BUG_AMT \n"); sqlstr.append(" , A.CHG_CON_AMT \n"); sqlstr.append(" , B.AFT_BUG_AMT \n"); sqlstr.append(" , '11111' \n"); sqlstr.append(" , SYSDATE \n"); sqlstr.append(" , A.DIV_PO_YN,A.ERP_NO_ECUT \n"); sqlstr.append(" FROM PUR2000T A \n"); sqlstr.append(" , PMM_DEMANDED_REQUEST B \n"); sqlstr.append(" , ( SELECT PUR_CONT_MOD_ID \n"); sqlstr.append(" , SUM(ITEM_AMT) AS SUM_ITEM_AMT \n"); sqlstr.append(" FROM CONT_PUR_ITEM \n"); sqlstr.append(" WHERE PUR_CONT_MOD_ID = ? \n"); sqlstr.append(" GROUP BY PUR_CONT_MOD_ID ) C \n"); sqlstr.append(" WHERE A.REQ_DOC_NO = B.DEMANDED_NO \n"); sqlstr.append(" AND A.PUR_CONT_MOD_ID = ? \n"); sqlstr.append(" AND B.DEMANDED_ID = ? \n"); persistent.setStatement(sqlstr.toString()); persistent.addParameter(maxPurContid); persistent.addParameter(prePurContid); persistent.addParameter(prePurContid); persistent.addParameter(DEMANDED_ID); //System.out.println(sqlstr.toString()); persistent.execute(); return true; } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> addChangeContPur() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } } public boolean addChangeContSupport(Connection connection, String PUR_CONT_MOD_ID, String maxPurContid, String prePurContid ) throws Exception { //WAFLogger.debug("valueObject : " + valueObject); Persistent persistent = new PersistentImpl(connection); StrBuffer sqlstr = new StrBuffer(); try { sqlstr.append("INSERT INTO CONT_PUR_SUPP \n"); sqlstr.append(" ( PUR_CONT_MOD_ID \n"); sqlstr.append(" , CORP_ID \n"); sqlstr.append(" , SIGN_SEQ \n"); sqlstr.append(" , VENDCD \n"); sqlstr.append(" , MEMBER_NAME \n"); sqlstr.append(" , BOSS_NAME \n"); sqlstr.append(" , POST_CODE \n"); sqlstr.append(" , ADDRESS \n"); sqlstr.append(" , TEL_NUM \n"); sqlstr.append(" , MEMBER_SLNO \n"); sqlstr.append(" , USER_NAME \n"); sqlstr.append(" , DIVISION \n"); sqlstr.append(" , POSITION \n"); sqlstr.append(" , HP \n"); sqlstr.append(" , EMAIL \n"); sqlstr.append(" , CRE_BY \n"); sqlstr.append(" , CRE_DATE ) \n"); sqlstr.append("SELECT ? \n"); sqlstr.append(" , A.CORP_ID \n"); sqlstr.append(" , A.SIGN_SEQ \n"); sqlstr.append(" , A.VENDCD \n"); sqlstr.append(" , DECODE(SIGN_SEQ, 2, NVL(B.CORP_KOR, A.MEMBER_NAME), C.CORP_KOR) AS MEMBER_NAME \n"); sqlstr.append(" , DECODE(SIGN_SEQ, 2, NVL(B.REP_NAME, A.BOSS_NAME), C.REP_NAME) AS BOSS_NAME \n"); sqlstr.append(" , DECODE(SIGN_SEQ, 2, NVL(B.POST_NO, A.POST_CODE), C.POST_NO) AS POST_CODE \n"); sqlstr.append(" , DECODE(SIGN_SEQ, 2, NVL(B.ADDRESS, A.ADDRESS), C.ADDRESS) AS ADDRESS \n"); sqlstr.append(" , DECODE(SIGN_SEQ, 2, NVL(B.CORP_TEL, A.TEL_NUM), A.TEL_NUM) AS TEL_NUM \n"); sqlstr.append(" , DECODE(SIGN_SEQ, 2, NVL(B.COPR_NO, A.MEMBER_SLNO), C.COPR_NO) AS MEMBER_SLNO \n"); sqlstr.append(" , A.USER_NAME \n"); sqlstr.append(" , A.DIVISION \n"); sqlstr.append(" , A.POSITION \n"); sqlstr.append(" , A.HP \n"); sqlstr.append(" , A.EMAIL \n"); sqlstr.append(" , '11111' \n"); sqlstr.append(" , SYSDATE \n"); sqlstr.append(" FROM CONT_PUR_SUPP A, SUPP_INFO B , HEAD_OFFICE_INFO C \n"); sqlstr.append(" WHERE A.CORP_ID = B.CORP_ID (+) \n"); sqlstr.append(" AND A.PUR_CONT_MOD_ID= ? \n"); persistent.setStatement(sqlstr.toString()); persistent.addParameter(maxPurContid); persistent.addParameter(PUR_CONT_MOD_ID); persistent.execute(); return true; } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> addChangeContSupport() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } } /** * 변경 계약 업체 정보 추가 * @param connection * @param valueObject * @throws Exception */ public boolean addChangeContItem(Connection connection, String demanded_id, String maxPurContid, String prePurContid) throws Exception { //WAFLogger.debug("valueObject : " + valueObject); Persistent persistent = new PersistentImpl(connection); StrBuffer sqlstr = new StrBuffer(); try { sqlstr.append("INSERT INTO CONT_PUR_ITEM \n"); sqlstr.append(" ( PUR_CONT_MOD_ID \n"); sqlstr.append(" , MATERIAL_CD \n"); sqlstr.append(" , PUR_CONT_ID \n"); sqlstr.append(" , ITEM_NAME \n"); sqlstr.append(" , ITEM_SPEC \n"); sqlstr.append(" , ITEM_UNIT \n"); sqlstr.append(" , ITEM_QTY \n"); sqlstr.append(" , ITEM_PRICE \n"); sqlstr.append(" , ITEM_AMT \n"); sqlstr.append(" , CRE_BY \n"); sqlstr.append(" , CRE_DATE \n"); sqlstr.append(" ) \n"); sqlstr.append("SELECT ? \n"); sqlstr.append(" , B.MATERIAL_CD \n"); sqlstr.append(" , C.PUR_CONT_ID \n"); sqlstr.append(" , D.MATERIAL_NAME AS ITEM_NAME \n"); sqlstr.append(" , D.MATERIAL_SPEC AS ITEM_SPEC \n"); sqlstr.append(" , D.UNIT_KIND AS ITEM_UNIT \n"); sqlstr.append(" , DEMANDED_QTY \n"); sqlstr.append(" , DEMANDED_PRI \n"); sqlstr.append(" , DEMANDED_AMT \n"); sqlstr.append(" , '11111' \n"); sqlstr.append(" , SYSDATE \n"); sqlstr.append(" FROM PMM_DEMANDED_REQUEST A \n"); sqlstr.append(" , PMM_DEMANDED_ITEM B \n"); sqlstr.append(" , PUR2000T C \n"); sqlstr.append(" , PMM_COMM_MATERIAL_MASTER D \n"); sqlstr.append(" ,(SELECT MAX(A1.PUR_CONT_MOD_ID) PUR_CONT_MOD_ID \n"); sqlstr.append(" FROM PUR2000T A1, PMM_DEMANDED_REQUEST B1 \n"); sqlstr.append(" WHERE A1.REQ_DOC_NO = B1.DEMANDED_NO \n"); sqlstr.append(" AND A1.STATUS_CD = '50' \n"); sqlstr.append(" AND B1.DEMANDED_ID = ? ) E \n"); sqlstr.append(" WHERE A.DEMANDED_ID = B.DEMANDED_ID \n"); sqlstr.append(" AND A.DEMANDED_NO = C.REQ_DOC_NO \n"); sqlstr.append(" AND C.PUR_CONT_MOD_ID = E.PUR_CONT_MOD_ID \n"); sqlstr.append(" AND B.MATERIAL_CD = D.MATERIAL_CD \n"); sqlstr.append(" AND A.DEMANDED_ID= ? \n"); persistent.setStatement(sqlstr.toString()); persistent.addParameter(maxPurContid); persistent.addParameter(demanded_id); persistent.addParameter(demanded_id); persistent.execute(); return true; } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> addChangeContItem() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); throw e; } } private RowSet getPurContEndThread() throws Exception{ Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); StringBuffer sqlstr = new StringBuffer(); try { sqlstr.append(" -- 프로젝트명, 계약번호, 계약명, 거래처, 계약기간 \n"); sqlstr.append(" SELECT A.CON_DOC_NO \n"); sqlstr.append(" , A.DEPT_NAME \n"); sqlstr.append(" , A.BUILD_NAME \n"); sqlstr.append(" , A.END_WORK_DATE \n"); sqlstr.append(" , B.MEMBER_NAME \n"); sqlstr.append(" , C.E_MAIL \n"); sqlstr.append(" FROM ( \n"); sqlstr.append(" SELECT A.PUR_CONT_MOD_ID \n"); sqlstr.append(" ,A.REQ_USERID \n"); sqlstr.append(" ,A.ORD_DOC_NO AS CON_DOC_NO \n"); sqlstr.append(" ,A.DEPT_NAME \n"); sqlstr.append(" ,A.CST_DOC_NAME AS BUILD_NAME \n"); sqlstr.append(" ,'~ ' || TO_CHAR(A.ORD_END_DATE, 'YYYY-MM-DD') AS END_WORK_DATE \n"); sqlstr.append(" FROM PUR2000T A \n"); sqlstr.append(" , (SELECT PUR_CONT_ID, MAX(MOD_NO) AS MOD_NO \n"); sqlstr.append(" FROM PUR2000T \n"); sqlstr.append(" WHERE STATUS_CD = '50' \n"); sqlstr.append(" AND PUR_CONT_ID IN (SELECT PUR_CONT_ID FROM PUR2000T WHERE TEMPLATE_CD = '15') -- 건설공사 표준하도급계약서 \n"); sqlstr.append(" GROUP BY PUR_CONT_ID) B \n"); sqlstr.append(" WHERE A.PUR_CONT_ID = B.PUR_CONT_ID \n"); sqlstr.append(" AND A.MOD_NO = B.MOD_NO \n"); sqlstr.append(" AND A.STATUS_CD = '50' -- 계약완료 \n"); sqlstr.append(" AND TO_CHAR(A.ORD_END_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE + 30, 'YYYY-MM-DD')-- 30일 이후 종료 계약 \n"); sqlstr.append(" ) A, CONT_PUR_SUPP B, PUB0120T C \n"); sqlstr.append(" WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID \n"); sqlstr.append(" AND B.SIGN_SEQ = 2 \n"); sqlstr.append(" AND A.REQ_USERID = C.ACCT_ID \n"); search.setStatement(sqlstr.toString()); rowSet = search.execute(); } catch(WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); throw se; } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } private RowSet getOutContEndThread() throws Exception{ Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); StringBuffer sqlstr = new StringBuffer(); try { sqlstr.append(" -- 프로젝트명, 계약번호, 계약명, 거래처, 계약기간 \n"); sqlstr.append(" SELECT A.CON_DOC_NO \n"); sqlstr.append(" , A.DEPT_NAME \n"); sqlstr.append(" , A.BUILD_NAME \n"); sqlstr.append(" , A.END_WORK_DATE \n"); sqlstr.append(" , B.MEMBER_NAME \n"); sqlstr.append(" , C.E_MAIL \n"); sqlstr.append(" FROM ( \n"); sqlstr.append(" SELECT A.SUB_CONT_MOD_ID \n"); sqlstr.append(" ,A.REQ_USERID \n"); sqlstr.append(" ,A.CON_DOC_NO \n"); sqlstr.append(" ,A.DEPT_NAME \n"); sqlstr.append(" ,A.BUILD_NAME \n"); sqlstr.append(" ,'~ ' || TO_CHAR(A.END_WORK_DATE, 'YYYY-MM-DD') AS END_WORK_DATE \n"); sqlstr.append(" FROM SUB2000T A \n"); sqlstr.append(" , (SELECT SUB_CONT_ID, MAX(MOD_NO) AS MOD_NO \n"); sqlstr.append(" FROM SUB2000T \n"); sqlstr.append(" WHERE STATUS_CD = '50' \n"); sqlstr.append(" GROUP BY SUB_CONT_ID) B \n"); sqlstr.append(" WHERE A.SUB_CONT_ID = B.SUB_CONT_ID \n"); sqlstr.append(" AND A.MOD_NO = B.MOD_NO \n"); sqlstr.append(" AND A.STATUS_CD = '50' -- 계약완료 \n"); sqlstr.append(" AND TO_CHAR(A.END_WORK_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE + 30, 'YYYY-MM-DD')-- 30일 이후 종료 계약 \n"); sqlstr.append(" ) A, CONT_OUT_SUPP B, PUB0120T C \n"); sqlstr.append(" WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID \n"); sqlstr.append(" AND B.SIGN_SEQ = 2 \n"); sqlstr.append(" AND A.REQ_USERID = C.ACCT_ID \n"); search.setStatement(sqlstr.toString()); rowSet = search.execute(); } catch(WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); throw se; } finally { if( rowSet == null ) rowSet = new RowSetImpl(); return rowSet; } } private void sendContEndMail(RowSet rowTread){ try { HandlerStorage Stroage = new HandlerStorage(); MessageSend messageSend = new MessageSend(Stroage); StringBuffer mailtitle = new StringBuffer(); StringBuffer mailtitlein = new StringBuffer(); StringBuffer mailContent = new StringBuffer(); Row row = new RowImpl(); mailtitle.append("*[알림]계약기간 만료 예정"); mailtitlein.append("*[알림]계약기간 만료 예정"); for ( int i = 0 ; i < rowTread.size() ; i ++ ) { row = rowTread.getRow(i); mailContent.append(""); mailContent.append("다음 하도급 계약의 계약기간이 30일 후 만료 예정이오니 ERP에서 변경계약을 요청하시기 바랍니다.
"); mailContent.append("
"); mailContent.append("1.프로젝트: " + row.get("DEPT_NAME") + "
"); mailContent.append("2.계약번호: " + row.get("CON_DOC_NO") + "
"); mailContent.append("3.계약명: " + row.get("BUILD_NAME") + "
"); mailContent.append("4.계약기간: " + row.get("END_WORK_DATE") + "
"); mailContent.append("5.계약업체: " + row.get("MEMBER_NAME") + "
"); mailContent.append("6.비고:
"); mailContent.append(" 1)계약기간 만료일에 준공 예정일 경우 해당 없음.
"); mailContent.append(" 2)계약기간이 유효한 계약만 기성 신청가능하므로 실제 준공 예상 시점까지 계약기간 연장 필요.
"); mailContent.append(" 3)eTEC Standard에 따라 변경 계약에 필요한 자료를 ERP에 첨부 후 변경계약 요청.
"); mailContent.append("
"); mailContent.append("
"); if (row.get("E_MAIL").length() > 4 ){ messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "NO_LINK"); } mailContent = new StringBuffer(); } } catch(Exception e) { Logger.err.println("doBiz() 실행중 오류가 발생 했습니다.\\n" + e.getMessage()); } } }