/** * @(#)file PoDeciSignDriver.java * @(#)author Lee beomgeun * @(#)version 1.0 * @(#)date Aug 1, 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.driver; import java.sql.CallableStatement; import java.sql.Connection; import com.udapsoft.waf.system.HandlerStorage; import kr.co.hsnc.common.base.WAFLogger; import kr.co.hsnc.common.base.WAFMessages; import kr.co.hsnc.common.logger.Logger; import kr.co.hsnc.common.sql.Row; import kr.co.hsnc.common.sql.RowSet; import kr.co.hsnc.common.sql.RowSetImpl; 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.sql.util.RowSetUtility; import kr.co.hsnc.common.util.ValueObject; import kr.co.udapsoft.common.commonSign.CommonSignInterface; import kr.co.udapsoft.common.sender.MessageSend; import kr.co.udapsoft.ebid.buyer.bid.outside.biz.InterfaceOutERPiU; import oracle.toplink.internal.ox.StrBuffer; /** * * @version 1.0 * @author Beomgeun Lee */ public class OBidSuccessSignDriver implements CommonSignInterface { /** * 테슽 파일 * */ public OBidSuccessSignDriver() { super(); } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doCallSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doCallSign(Connection connection, ValueObject signDoc) throws Exception { } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doStartSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doStartSign(Connection connection, ValueObject signDoc) throws Exception { System.out.println("!!doStartSign"); } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doFirstFinishSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doFirstFinishSign(Connection connection, ValueObject signDoc) throws Exception { boolean success = true; System.out.println("!!doFirstFinishSign"); System.out.println("### 결재 Strart"); try { saveProc(connection,signDoc); success = update_BidMaster(connection, signDoc , "10");//결재 ValueObject vo = getDemanded_kind(connection, signDoc); if("B".equals(vo.get("DEMANDED_KIND"))){ UPDATE_EBID_STATUS(connection, signDoc, vo); }else { String demandedId = InterfaceOutERPiU.getDemandedId(signDoc.get("KEY_COL1"), signDoc.get("KEY_COL2")); InterfaceOutERPiU.updateStatusPMM(demandedId, "10"); } if(success){ if("Y".equals(vo.get("ONBID_YN"))){ Send(signDoc,"10"); //낙찰업체 메일전송 2016.12.9 패찰업체 메일전송 추가. } Send_ToeTEC(signDoc,"10"); //발주의뢰자, 현설담당자, 외주 담당자 메일발송 } }catch (Exception e) { WAFLogger.error(this.getClass().getName() + " --> doFirstFinishSign() : " + e.getMessage()); WAFLogger.error(e); throw e; //2017.2.24추가 } System.out.println("### 결재 End"); } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doFinishSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doFinishSign(Connection connection, ValueObject signDoc) throws Exception { System.out.println("!!doFinishSign"); } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doRejectSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doFirstRejectSign(Connection connection, ValueObject signDoc) throws Exception { System.out.println("!!반려"); update_BidMaster(connection, signDoc , "95");//반려 } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doRejectSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doRejectSign(Connection connection, ValueObject signDoc) throws Exception { System.out.println("!!doRejectSign"); } /* * (non-Javadoc) * @see kr.co.udap.ehr.common.sign.SignInterface#doCancelSign(java.sql.Connection, com.udapsoft.common.util.ValueObject) */ public void doCancelSign(Connection connection, ValueObject signDoc) throws Exception { } /** * SP_BID_TO_OUTCONT 프로시저 호출 * @param connection * @param valueObject * @throws Exception */ public void saveProc(Connection connection, ValueObject vo) throws Exception { ValueObject rtnVo = new ValueObject(); CallableStatement cs = null; String pssql = ""; try{ pssql = "{call SP_BID_TO_OUTCONT(?,?,?,?)}"; cs = connection.prepareCall(pssql); cs.setString(1, vo.get("KEY_COL1")); cs.setString(2, vo.get("KEY_COL2")); cs.registerOutParameter(3, java.sql.Types.VARCHAR); cs.registerOutParameter(4, java.sql.Types.VARCHAR); cs.execute(); rtnVo.set("OUT_SUCCESS_CD",cs.getString(3)); rtnVo.set("OUT_MESSAGE",cs.getString(4)); System.out.println("CODE : " + cs.getString(3)); if("N".equals(cs.getString(3))){ throw new Exception(cs.getString(4)); } } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> saveProc() : " + e.getMessage()); WAFLogger.error(e); throw e; } } /** * BID_MASTER 상태값 업데이트. * @param connection * @param valueObject * @throws Exception */ public boolean update_BidMaster(Connection connection, ValueObject valueObject , String status) throws Exception { Persistent persistent = new PersistentImpl(connection); StrBuffer sqlstr = new StrBuffer(); try { sqlstr.append(" UPDATE BID_MASTER \n"); sqlstr.append(" SET STATUS_CD = ? \n"); //sqlstr.append(" ,UPD_BY = ? \n"); //sqlstr.append(" ,UPD_DATE = SYSDATE \n"); sqlstr.append(" WHERE BID_NO = ? \n"); sqlstr.append(" AND BID_DEG = ? \n"); persistent.setStatement(sqlstr.toString()); persistent.addParameter(status); //persistent.addParameter(valueObject.get("KEY_COL3")); persistent.addParameter(valueObject.get("KEY_COL1")); persistent.addParameter(valueObject.get("KEY_COL2")); persistent.execute(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> update_BidMaster() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); System.out.println("Exception : " + e.getMessage()); return false; } return true; } public void Send(ValueObject params, String event) throws Exception { HandlerStorage Stroage = new HandlerStorage(); MessageSend messageSend = new MessageSend(Stroage); RowSet SuppInfo = bid_supp_info(params); ValueObject bid_Master = bid_Master_info(params); for(int i=0;i < SuppInfo.size();i++){ Row row = SuppInfo.getRow(i); boolean email = false; String CURRENCY_GB = ""; StringBuffer mailtitle = new StringBuffer(); StringBuffer mailtitlein = new StringBuffer(); StringBuffer mailContent = new StringBuffer(); if(row.get("SUCC_YN").equals("Y")){ if(!row.get("CHARGE_EMAIL").isEmpty()){ if(!row.get("CURRENCY_GB").isEmpty()) CURRENCY_GB = " ("+row.get("CURRENCY_GB")+")"; mailtitle.append("* 참여하신 입찰의 낙찰 통보 메일입니다."); mailtitlein.append("* 참여하신 입찰의 낙찰 통보 메일입니다."); mailContent.append(row.get("VENDOR_NAME")+"님이 참여하신 다음의 입찰에 낙찰업체로 선정되었음을 알려드립니다.
"); mailContent.append(""); mailContent.append("
"); mailContent.append("1.프로젝트 : " + bid_Master.get("DEPT_NM") + "
"); mailContent.append("2.공고번호 : " + bid_Master.get("BID_NO") + "
"); mailContent.append("3.공고건명 : " + bid_Master.get("BID_MASTER_NM") + "
"); mailContent.append("4.낙찰금액 : " + row.get("TOT_AMT") + CURRENCY_GB + "
" ); mailContent.append("5.입찰담당자 : " +bid_Master.get("BUSEO_NM")+" / "+ bid_Master.get("USER_NAME") +" (연락처 : " + bid_Master.get("PO_EMPNM") + ", 이메일 : " + bid_Master.get("E_MAIL") +")
"); mailContent.append("
"); mailContent.append("[유의사항]
"); mailContent.append(""); mailContent.append("본 낙찰통지를 받은 후 낙찰업체는 당사가 계약서에 서명을 요청한 날로부터 15일 내에
"); mailContent.append("계약 관련 제반 서류(수입인지, 근재보험증권, 계약이행증권)를 제출하여 계약서에 서명하시기 바랍니다.
"); mailContent.append("
"); mailContent.append("만약 낙찰 업체가 서명 요청을 받은 날로부터 15일 내에 계약 서명 및 제반서류를 제출하지 못 할 경우
"); mailContent.append("당사의 판단에 따라 해당 낙찰 및 계약이 취소될 수 있습니다.
"); mailContent.append(""); email = true; if(email){ messageSend.sendEmail(row.get("CHARGE_EMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent); } } if(!row.get("CHAGE_HP").isEmpty()){ String smsContent = WAFMessages.getMessage("Bidder.Selection.Guide"); String from_phon_no = bid_Master.get("PO_EMPNM").replace("-", ""); String to_phon_no = row.get("CHAGE_HP").replace("-", ""); if(!smsContent.isEmpty()){ messageSend.sendSMS(to_phon_no,from_phon_no, bid_Master.get("ACCT_ID"), smsContent); //2015.9.21 입찰담당자번호 } } }else{ if(!row.get("CHARGE_EMAIL").isEmpty()){ mailtitle.append("* 입찰 결과 통보 메일입니다."); mailtitlein.append("* 입찰 결과 통보 메일입니다."); mailContent.append("안녕하십니까. SGC이앤씨(주) 입니다.
"); mailContent.append("당사에서 실시한 입찰 결과를 다음과 같이 통보드립니다.
"); mailContent.append(""); mailContent.append("
"); mailContent.append("1.프로젝트 : " + bid_Master.get("DEPT_NM") + "
"); mailContent.append("2.공고건명 : " + bid_Master.get("BID_MASTER_NM") + "
"); mailContent.append("3.입찰결과 : 미선정
" ); mailContent.append("
"); mailContent.append("성실히 입찰에 참여해 주심에 깊은 감사의 뜻을 전하며 귀사에 무궁한 발전이 있기를 기원합니다.
"); mailContent.append(""); email = true; if(email){ messageSend.sendEmail(row.get("CHARGE_EMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent); // 2016.12.9 } } }//end of fail }//end of for } /** * 견적 대상 업체 를 검색하여 리턴 * @param Params * @return */ public RowSet bid_supp_info(ValueObject param) { Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); // String sqlstr = ""; StrBuffer sqlstr = new StrBuffer(); try{ sqlstr.append(" SELECT MAX(A.CHARGE_NAME) AS CHARGE_NAME \n"); sqlstr.append(" ,MAX(A.CHARGE_EMAIL) AS CHARGE_EMAIL \n"); sqlstr.append(" ,MAX(A.CHAGE_HP) AS CHAGE_HP \n"); sqlstr.append(" ,A.VENDOR_NAME \n"); sqlstr.append(" ,MAX(NVL(A.CURRENCY_GB,B.CURRENCY_GB)) AS CURRENCY_GB \n"); sqlstr.append(" ,'0' AS TOT_AMT \n"); sqlstr.append(" ,MAX(NVL(A.SUCC_YN, 'N')) AS SUCC_YN \n"); sqlstr.append(" FROM BID_SUPP A,BID_MASTER B,BID_SUPP C \n"); sqlstr.append(" WHERE A.BID_NO = B.BID_NO \n"); sqlstr.append(" AND A.BID_DEG = B.BID_DEG \n"); sqlstr.append(" AND A.BID_NO = C.BID_NO(+) \n"); sqlstr.append(" AND A.VENDOR_CD = C.VENDOR_CD(+) \n"); sqlstr.append(" AND A.BID_NO = ? \n"); sqlstr.append(" AND C.BID_DEG(+) = ? \n"); sqlstr.append(" AND C.SUCC_YN(+) = 'Y' \n"); sqlstr.append(" AND C.VENDOR_CD IS NULL \n"); sqlstr.append(" AND NVL(A.SUCC_YN, 'N') = 'N' \n"); sqlstr.append(" GROUP BY A.VENDOR_CD, A.VENDOR_NAME \n"); sqlstr.append(" UNION ALL \n"); sqlstr.append(" SELECT A.CHARGE_NAME \n"); sqlstr.append(" ,A.CHARGE_EMAIL \n"); sqlstr.append(" ,A.CHAGE_HP \n"); sqlstr.append(" ,A.VENDOR_NAME \n"); sqlstr.append(" ,NVL(A.CURRENCY_GB,B.CURRENCY_GB) AS CURRENCY_GB \n"); sqlstr.append(" ,DECODE(A.ESTI_TOT_AMT,-1,'단가계약',F_GET_D_CURRENCY(A.ESTI_TOT_AMT,F_GET_CURRENCY_DIGIT(NVL(A.CURRENCY_GB,B.CURRENCY_GB)))) AS TOT_AMT \n"); sqlstr.append(" ,NVL(A.SUCC_YN, 'N') SUCC_YN \n"); sqlstr.append(" FROM BID_SUPP A,BID_MASTER B \n"); sqlstr.append(" WHERE A.BID_NO = B.BID_NO \n"); sqlstr.append(" AND A.BID_DEG = B.BID_DEG \n"); sqlstr.append(" AND A.BID_NO = ? \n"); sqlstr.append(" AND A.BID_DEG = ? \n"); sqlstr.append(" AND A.SUCC_YN = 'Y' \n");//2016.12.9 패찰통보 추가 2017.08.16 1차-최종차수까지 참여모든 업체 패찰메일 search.setStatement(sqlstr.toString()); search.addParameter(param.get("KEY_COL1")); search.addParameter(param.get("KEY_COL2")); search.addParameter(param.get("KEY_COL1")); search.addParameter(param.get("KEY_COL2")); search.setStatement(sqlstr.toString()); rowSet = search.execute(); } catch (WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); } finally { if(rowSet == null){ rowSet = new RowSetImpl(); } } System.out.println("### rowSet size : " + rowSet.size()); return rowSet; } /** * 발주의뢰(외주) 접수를 검색하여 리턴 * @param Params * @return */ public ValueObject bid_Master_info(ValueObject param) { Search search = new SearchImpl(); ValueObject valueobject = null; RowSet rowSet = new RowSetImpl(); StrBuffer sqlstr = new StrBuffer(); try{ sqlstr.append(" SELECT A.BID_NO, \n"); sqlstr.append(" TO_CHAR(A.BID_TO_DT,'YYYY-MM-DD HH24:MI') AS BID_TO_DT_NM, \n"); sqlstr.append(" A.PO_EMPNM, \n"); sqlstr.append(" A.DEPT_CD, \n"); sqlstr.append(" A.DEPT_NM, \n"); sqlstr.append(" A.BID_MASTER_NM, \n"); sqlstr.append(" B.USER_NAME, \n"); sqlstr.append(" B.E_MAIL, \n"); sqlstr.append(" B.ACCT_ID, \n"); sqlstr.append(" A.DEMANDED_NO, A.DEMANDED_TITLE, C.E_MAIL EMP_MAIL,SITEDESCEMAIL, \n"); sqlstr.append(" TO_CHAR(TO_DATE(A.DELI_START_DT),'YYYY-MM-DD') AS WORK_FROM_DT, \n"); sqlstr.append(" TO_CHAR(TO_DATE(A.DELI_END_DT),'YYYY-MM-DD') AS WORK_END_DT, \n"); sqlstr.append(" (SELECT ACC_DEPT_NM FROM PUB0120T WHERE USER_SABUN = B.USER_SABUN) AS BUSEO_NM \n"); sqlstr.append(" FROM BID_MASTER A, PUB0120T B, PUB0120T C \n"); sqlstr.append(" WHERE A.PUREMPNO = B.ACCT_ID \n"); sqlstr.append(" AND A.EMP_NO = C.ACCT_ID \n"); sqlstr.append(" AND A.BID_NO = ? \n"); sqlstr.append(" AND A.BID_DEG = ? \n"); search.setStatement(sqlstr.toString()); search.addParameter(param.get("KEY_COL1")); search.addParameter(param.get("KEY_COL2")); search.setStatement(sqlstr.toString()); rowSet = search.execute(); if( rowSet != null && rowSet.size() > 0 ){ valueobject = RowSetUtility.getValueObject(rowSet.getRow(0)); } } catch (WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); } return valueobject; } /** * STATUS_CD 상태값을 업데이트해준다. * @param connection * @param valueObject * @throws Exception */ public boolean UPDATE_EBID_STATUS(Connection connection, ValueObject vo, ValueObject BidMaster) throws Exception { Persistent persistent = new PersistentImpl(connection); StringBuffer sqlstr = null; try { sqlstr = new StringBuffer(); sqlstr.append(" UPDATE ETECEPC.MR_REQ_PO \n"); sqlstr.append(" SET EBID_STATUS = '05' --결재 \n"); sqlstr.append(" , REQ_STATUS = '5' --결재 \n"); sqlstr.append(" WHERE DEMANDED_ID = (SELECT DEMANDED_ID FROM BID_MASTER WHERE BID_NO = ? AND BID_DEG = ?) \n"); persistent.setStatement(sqlstr.toString()); persistent.addParameter(vo.get("KEY_COL1")); persistent.addParameter(vo.get("KEY_COL2")); persistent.execute(); } catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> updateSac01001() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); return false; } return true; } private ValueObject getDemanded_kind(Connection connection, ValueObject vo) throws Exception{ Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); ValueObject valueobject = null; StringBuffer sqlstr = new StringBuffer(); try { sqlstr.append(" SELECT DEMANDED_KIND,STATUS_CD,ONBID_YN \n"); sqlstr.append(" FROM BID_MASTER \n"); sqlstr.append(" WHERE BID_NO = ? \n"); sqlstr.append(" AND BID_DEG = ? \n"); search.setStatement(sqlstr.toString()); search.addParameter(vo.get("KEY_COL1")); search.addParameter(vo.get("KEY_COL2")); rowSet = search.execute(); if (rowSet.size() > 0) { valueobject = RowSetUtility.getValueObject(search.execute().getRow(0)); } } catch(WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); throw se; } finally { if (valueobject == null) valueobject = new ValueObject(); return valueobject; } } //2016.6.16 시행결의완료후 결재완료 알림. 발주의뢰자, 견적설명회 담당자 public void Send_ToeTEC(ValueObject params, String event) throws Exception { HandlerStorage Stroage = new HandlerStorage(); MessageSend messageSend = new MessageSend(Stroage); RowSet SuppInfo = bid_supp_info(params); RowSet relEmpInfo = relation_emp_info(params); ValueObject bid_Master = bid_Master_info(params); StringBuffer mailtitle = new StringBuffer(); StringBuffer mailtitlein = new StringBuffer(); StringBuffer mailContent = new StringBuffer(); mailtitle.append("* [알림]시행결의 승인"); mailtitlein.append("* 시행결의 승인"); mailContent.append("아래와 같이 시행결의가 완료되었음을 알려드립니다.
"); mailContent.append(""); mailContent.append("
"); mailContent.append("1.프로젝트 : " + bid_Master.get("DEPT_NM") + "
"); mailContent.append("2.발주번호 : " + bid_Master.get("DEMANDED_NO") + "
"); mailContent.append("3.발주건명 : " + bid_Master.get("DEMANDED_TITLE") + "
"); mailContent.append("4.외주담당자 : " + bid_Master.get("USER_NAME") + "
" ); for(int i=0;i < SuppInfo.size();i++){ if (SuppInfo.getRow(i).get("SUCC_YN").equals("Y")){ mailContent.append("5.낙찰업체 : " + SuppInfo.getRow(i).get("VENDOR_NAME") + "
" ); mailContent.append("6.낙찰금액 : " + SuppInfo.getRow(i).get("TOT_AMT") + SuppInfo.getRow(i).get("CURRENCY_GB") + "
" ); } } mailContent.append("7.계약기간 : " +bid_Master.get("WORK_FROM_DT")+" ~ "+ bid_Master.get("WORK_END_DT")+ "
"); mailContent.append("8.비고
"); mailContent.append(" 1)계약서는 시행결의 승인 후 1~2일 내 낙찰업체에 전달됩니다.
"); mailContent.append(" 2)계약 완료 후 계약서 및 관련 서류는 전자조달시스템의 외주-계약결과에서 조회 가능합니다.
"); mailContent.append(" 3)공사의 경우 계약 완료 전까지 현장 개설 불가합니다.
"); mailContent.append("
"); //외주담당자 messageSend.sendEmail(bid_Master.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "1"); //발주의뢰자 if(!bid_Master.get("EMP_MAIL").isEmpty()){// messageSend.sendEmail(bid_Master.get("EMP_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "1"); } //현설담당자 if(!bid_Master.get("SITEDESCEMAIL").isEmpty() && !bid_Master.get("SITEDESCEMAIL").equals(bid_Master.get("EMP_MAIL")) ){ messageSend.sendEmail(bid_Master.get("SITEDESCEMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "1"); } //유관부서 담당자 if(relEmpInfo.size() > 0){ for(int i=0;i < relEmpInfo.size();i++){ Row row = relEmpInfo.getRow(i); messageSend.sendEmail(row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "1"); } } } public RowSet relation_emp_info(ValueObject param) { Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); // String sqlstr = ""; StrBuffer sqlstr = new StrBuffer(); try{ sqlstr.append(" SELECT A.EMP_ID \n"); sqlstr.append(" , B.E_MAIL \n"); sqlstr.append(" FROM BID_DECIDE_MAIL_LIST A, PUB0120T B \n"); sqlstr.append(" WHERE A.EMP_ID = B.ACCT_ID \n"); sqlstr.append(" AND A.BID_NO = ? \n"); sqlstr.append(" AND A.BID_DEG = ? \n"); search.setStatement(sqlstr.toString()); search.addParameter(param.get("KEY_COL1")); search.addParameter(param.get("KEY_COL2")); search.setStatement(sqlstr.toString()); rowSet = search.execute(); } catch (WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); } finally { if(rowSet == null){ rowSet = new RowSetImpl(); } } System.out.println("### rowSet size : " + rowSet.size()); return rowSet; } }