/** * @(#)file BidPlanSignDriver.java * @(#)author Son hyungmin * @(#)version 1.0 * @(#)date 2014-09-29 * @(#)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.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 oracle.toplink.internal.ox.StrBuffer; /** * * @version 1.0 * @author Beomgeun Lee */ public class PBidPlanSignDriver implements CommonSignInterface { /** * 테슽 파일 * */ public PBidPlanSignDriver() { 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 { WAFLogger.debug("!!doCallSign"); } /* * (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 { WAFLogger.debug("!!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; WAFLogger.debug("!!doFirstFinishSign"); ValueObject vo = new ValueObject(); //String SITEDESC_FLAG = getSignSitedesc_flag(connection, signDoc); vo = getDemanded_kind(connection, signDoc); //signDoc.set("SITEDESC_FLAG", SITEDESC_FLAG); if("Y".equals(vo.get("ONBID_YN"))){ String status = ""; if("Y".equals(vo.get("SITEDESC_FLAG"))){ status = "03";//현설의뢰 }else{ status = "04";//견적의뢰 } success = updateMaster(connection, signDoc , status); }else{ /*2014-12-08 오프라인 추가*/ success = updateMaster(connection, signDoc , "06"); } if(!"Y".equals(vo.get("SITEDESC_FLAG"))){ UPDATE_EBID_STATUS(connection, signDoc,vo); } if(success){ Send(signDoc,vo.get("SITEDESC_FLAG")); if("Y".equals(vo.get("ONBID_YN"))){ Send_ToEtec(signDoc,vo.get("SITEDESC_FLAG")); //2020.03.23 담당자 메일발송 추가 } } WAFLogger.debug("### 결재 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 { WAFLogger.debug("!!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 { WAFLogger.debug("!!반려"); updateMasterReject(connection, signDoc); } /* * (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 { WAFLogger.debug("!!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 { WAFLogger.debug("!!doCancelSign"); } /** * STATUS_CD 상태값을 업데이트해준다. * @param connection * @param valueObject * @throws Exception */ public boolean updateMaster(Connection connection, ValueObject vo, String status) throws Exception { Persistent persistent = new PersistentImpl(connection); StringBuffer sqlstr = null; try { sqlstr = new StringBuffer(); sqlstr.append(" UPDATE BID_MASTER \n"); sqlstr.append(" SET STATUS_CD = ? \n"); if("06".equals(status)){ sqlstr.append(" , OPEN_DT = SYSDATE \n"); } sqlstr.append(" , BID_APP_DATE = SYSDATE \n"); sqlstr.append(" , BID_APP_NO = ? \n"); sqlstr.append(" WHERE BID_NO = ? \n"); sqlstr.append(" AND BID_DEG = ? \n"); persistent.setStatement(sqlstr.toString()); persistent.addParameter(status); persistent.addParameter(vo.get("SIGN_INTERFACE_ID")); 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; } /** * STATUS_CD 상태값을 업데이트해준다. * @param connection * @param valueObject * @throws Exception */ public boolean updateMasterReject(Connection connection, ValueObject vo) throws Exception { Persistent persistent = new PersistentImpl(connection); StringBuffer sqlstr = null; try { sqlstr = new StringBuffer(); sqlstr.append(" UPDATE BID_MASTER \n"); sqlstr.append(" SET STATUS_CD = '94' \n"); sqlstr.append(" WHERE BID_NO = ? \n"); sqlstr.append(" 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; } /** * 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 { if("B".equals(BidMaster.get("DEMANDED_KIND"))){ sqlstr = new StringBuffer(); sqlstr.append(" UPDATE ETECEPC.MR_REQ_PO \n"); sqlstr.append(" SET EBID_STATUS = '03' --입찰 \n"); sqlstr.append(" WHERE BUYREQNO = (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(); }else{ /* * 2018.10.31 이인오 삭제 재확인필요 sqlstr = new StringBuffer(); sqlstr.append(" UPDATE PMM_DEMANDED_REQUEST \n"); sqlstr.append(" SET EBID_STATUS_CD = ? \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(BidMaster.get("STATUS_CD")); 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; } /** * 아이템 INSERT * @param connection * @param valueObject * @throws Exception */ public boolean insert_BidItem(Connection connection, ValueObject valueObject) throws Exception { Persistent persistent = new PersistentImpl(connection); StrBuffer sqlstr = new StrBuffer(); try { sqlstr.append("INSERT INTO BID_ITEM \n"); sqlstr.append(" ( BID_NO \n"); sqlstr.append(" ,BID_DEG \n"); sqlstr.append(" ,MATERIAL_CD \n"); sqlstr.append(" ,DEMANDED_QTY \n"); sqlstr.append(" ,STD ) \n"); sqlstr.append("SELECT ? AS A \n"); sqlstr.append(" , ? AS B \n"); sqlstr.append(" ,MATERIAL_CD \n"); sqlstr.append(" ,1 \n"); sqlstr.append(" ,'식' \n"); sqlstr.append(" FROM BID_OUT_ITEM_MASTER \n"); sqlstr.append(" WHERE DEPTH=4 \n"); persistent.setStatement(sqlstr.toString()); persistent.addParameter(valueObject.get("KEY_COL1")); persistent.addParameter(valueObject.get("KEY_COL2")); persistent.execute(); }catch(Exception e) { WAFLogger.error(this.getClass().getName() + " --> insert_BidItem() : " + e.getMessage()); WAFLogger.error("SQL : " + sqlstr); WAFLogger.error(e); WAFLogger.debug("Exception : " + e.getMessage()); 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,SITEDESC_FLAG \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; } } private String getSignSitedesc_flag(Connection connection, ValueObject vo) throws Exception{ Search search = new SearchImpl(); RowSet rowSet = new RowSetImpl(); String SITEDESC_FLAG = ""; StringBuffer sqlstr = new StringBuffer(); try { sqlstr.append(" SELECT SITEDESC_FLAG \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) { SITEDESC_FLAG = RowSetUtility.getValueObject(search.execute().getRow(0)).get("SITEDESC_FLAG"); } } catch(WAFSQLException se) { Logger.err.println("SQL : \n" + se.getStatement()); Logger.err.println("PARAM : \n" + se.getParameter()); throw se; } finally { if (SITEDESC_FLAG == null) SITEDESC_FLAG = ""; return SITEDESC_FLAG; } } public void Send(ValueObject params, String event) throws Exception { HandlerStorage Stroage = new HandlerStorage(); MessageSend messageSend = new MessageSend(Stroage); if("Y".equals(event)){ event = "03"; }else{ event = "04";//견적의뢰 } 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); if(!row.get("CHARGE_EMAIL").isEmpty()){ if("Y".equals(bid_Master.get("ONBID_YN"))){ boolean email = false; StringBuffer mailtitle = new StringBuffer(); StringBuffer mailtitlein = new StringBuffer(); StringBuffer mailContent = new StringBuffer(); if("04".equals(event)){//견적의뢰 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.견적마감일시 : " + bid_Master.get("BID_TO_DT_NM") + "
"); 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(""); email = true; }else if("03".equals(event)){//현설의뢰 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.현설일시 : " + bid_Master.get("SITEDESCDATE_NM") + "
"); mailContent.append("5.현설장소 : " + bid_Master.get("SITEDESCPLACE") + "
"); mailContent.append("6.현설담당자 : " +bid_Master.get("SITEDESC_BUSEO_NM")+" / "+ bid_Master.get("SITEDESC_CRG_NM") +" (연락처 : " + bid_Master.get("SITEDESCTELNO") + ", 이메일 : " + bid_Master.get("SITEDESCEMAIL") +")
"); mailContent.append("7.입찰담당자 : " +bid_Master.get("BUSEO_NM")+" / "+ bid_Master.get("USER_NAME") +" (연락처 : " + bid_Master.get("PO_EMPNM") + ", 이메일 : " + bid_Master.get("E_MAIL") +")
"); 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 = ""; if("Y".equals(bid_Master.get("ONBID_YN"))){ if("04".equals(event)){//견적의뢰 smsContent = WAFMessages.getMessage("Submit.Estimate.request"); }else if("03".equals(event)){//현설의뢰 smsContent = WAFMessages.getMessage("Description.Announcements.site"); } }else{ if("Y".equals(bid_Master.get("SITEDESC_FLAG"))){ /*2014-12-08 추가 * 오프라인 현설의뢰 SMS 발송*/ smsContent = ""; } } String from_phon_no = bid_Master.get("PO_EMPNM").replace("-", ""); String to_phon_no = row.get("CHAGE_HP").replace("-", ""); //받는사람 row.get("CHAGE_HP").replace("-", ""); if(!smsContent.isEmpty()){ messageSend.sendSMS(to_phon_no,from_phon_no, bid_Master.get("PUREMPNO"), smsContent); //2015.9.21 입찰담당자 번호. } } } //2020.03.23 담당자 메일발송 추가로 아래 주석 //Send_to_etec(SuppInfo, bid_Master, event); //현설담당자 메일발송 } /** * 견적 대상 업체 를 검색하여 리턴 * @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 A.CHARGE_NAME,A.CHARGE_EMAIL,A.CHAGE_HP,A.VENDOR_NAME, B.CORP_TEL \n"); sqlstr.append(" FROM BID_SUPP A, SUPP_INFO B \n"); sqlstr.append(" WHERE A.VENDOR_CD = B.CORP_ID 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(); } } WAFLogger.debug("### 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(" A.BID_DEG, \n"); sqlstr.append(" A.DEMANDED_ID, \n"); sqlstr.append(" A.DEMANDED_NO, \n"); sqlstr.append(" A.DEPT_CD, \n"); sqlstr.append(" A.DEPT_NM, \n"); sqlstr.append(" A.DEMANDED_TITLE, \n"); sqlstr.append(" A.DEMANDED_KIND, \n"); sqlstr.append(" A.DEMANDED_DATE, \n"); sqlstr.append(" A.REQUEST_DATE, \n"); sqlstr.append(" A.REQ_DEPT_CD, \n"); sqlstr.append(" A.REQ_DEPT_NM, \n"); sqlstr.append(" A.EMP_NO, \n"); sqlstr.append(" A.EMP_NM, \n"); sqlstr.append(" A.PAY_ITEM, \n"); sqlstr.append(" A.COST_CODE, \n"); sqlstr.append(" A.COST_TYPE, \n"); sqlstr.append(" A.CONT_AMT, \n"); sqlstr.append(" A.ACT_AMT, \n"); sqlstr.append(" A.ACT_RATE, \n"); sqlstr.append(" A.RECEIPT_DATE, \n"); sqlstr.append(" A.ONBID_YN, \n"); sqlstr.append(" A.CMPT_CD, \n"); sqlstr.append(" A.SUCC_CD, \n"); sqlstr.append(" A.NOTI_DY, \n"); sqlstr.append(" A.SITEDESC_FLAG, \n"); sqlstr.append(" A.SITEDESCCHRGPRSNID, \n"); sqlstr.append(" NVL(A.SITEDESC_CRG_NM,(SELECT USER_NAME FROM PUB0120T WHERE ACCT_ID = A.SITEDESCCHRGPRSNID)) AS SITEDESC_CRG_NM, \n"); sqlstr.append(" A.SITEDESCTELNO, \n"); sqlstr.append(" A.SITEDESCEMAIL, \n"); sqlstr.append(" A.SITEDESCDATE, \n"); sqlstr.append(" TO_CHAR(A.SITEDESCDATE,'YYYY-MM-DD HH24:MI') AS SITEDESCDATE_NM, \n"); sqlstr.append(" A.SITEDESCPLACE, \n"); sqlstr.append(" A.BID_FROM_DT, \n"); sqlstr.append(" A.BID_TO_DT, \n"); sqlstr.append(" TO_CHAR(A.BID_TO_DT,'YYYY-MM-DD HH24:MI') AS BID_TO_DT_NM, \n"); sqlstr.append(" A.PUREMPNO, \n"); sqlstr.append(" A.PO_EMPNM, \n"); sqlstr.append(" A.OPEN_EMPNM, \n"); sqlstr.append(" A.OPEN_EMPID, \n"); sqlstr.append(" A.OPEN_DT, \n"); sqlstr.append(" A.TBE_FLAG, \n"); sqlstr.append(" A.DELIVERY_CONDITION, \n"); sqlstr.append(" A.DELIVERY_PLACE, \n"); sqlstr.append(" A.INNER_REMARKS, \n"); sqlstr.append(" A.OUTER_REMARKS, \n"); sqlstr.append(" A.ATT_ID, \n"); sqlstr.append(" A.BID_APP_DATE, \n"); sqlstr.append(" A.BID_APP_NO, \n"); sqlstr.append(" A.CONT_APP_DATE, \n"); sqlstr.append(" A.CONT_APP_NO, \n"); sqlstr.append(" A.DOC_ETC_IN, \n"); sqlstr.append(" A.DOC_ETC_OUT, \n"); sqlstr.append(" A.BID_MASTER_NM, \n"); sqlstr.append(" A.POSTPONE_REASON, \n"); sqlstr.append(" A.CANCEL_REASON, \n"); sqlstr.append(" A.MODIFY_REASON, \n"); sqlstr.append(" A.FIB_REASON, \n"); sqlstr.append(" A.OUT_ITEM_CD, \n"); sqlstr.append(" A.STATUS_CD, \n"); sqlstr.append(" A.DIV_PO_YN, \n"); sqlstr.append(" A.CURRENCY_GB, \n"); sqlstr.append(" A.CURRENCY_GB_YN, \n"); sqlstr.append(" A.PBC_CBE_TYPE_CD, \n"); sqlstr.append(" A.PBC_INSTALL_YN, \n"); sqlstr.append(" A.PBC_COMMISS_YN, \n"); sqlstr.append(" A.PBC_SPETOOL_YN, \n"); sqlstr.append(" A.PBC_TEST_YN, \n"); sqlstr.append(" A.PBC_DOC_YN, \n"); sqlstr.append(" A.PBC_PACK_YN, \n"); sqlstr.append(" A.PBC_LANDTRAN_YN, \n"); sqlstr.append(" A.DELIVERY_CONDITION_YN, \n"); sqlstr.append(" A.PBC_FEE_YN, \n"); sqlstr.append(" A.PBC_OPTION_COMMENT, \n"); sqlstr.append(" A.PBC_OPTION_YN, \n"); sqlstr.append(" A.CBC_DAMAGE_RATE, \n"); sqlstr.append(" A.CBC_DAMAGE_MAXRATE, \n"); sqlstr.append(" A.CBC_FIRST_RATE, \n"); sqlstr.append(" A.PBC_FIRST_CONDI, \n"); sqlstr.append(" A.CPC_MID_RATE, \n"); sqlstr.append(" A.PBC_MID_CONDI, \n"); sqlstr.append(" A.PBC_REST_RATE, \n"); sqlstr.append(" A.PBC_REST_CONDI, \n"); sqlstr.append(" A.PBC_FRSTGUAR_RATE, \n"); sqlstr.append(" A.PBC_FRSTGUAR_CONDI, \n"); sqlstr.append(" A.CONTFUL_RATE, \n"); sqlstr.append(" A.CONTFUL_CONDI, \n"); sqlstr.append(" A.WARR_RATE, \n"); sqlstr.append(" A.WARR_CONDI, \n"); sqlstr.append(" A.PBC_DEFECT_CONDI, \n"); sqlstr.append(" A.CBC_DEFECT_DAY, \n"); sqlstr.append(" A.CBC_DERECT_TERM_CD, \n"); sqlstr.append(" A.DELI_FROM_DY, \n"); sqlstr.append(" A.DELI_TO_DY, \n"); sqlstr.append(" A.OBC_CONT_TYPE_CD, \n"); sqlstr.append(" A.OBC_FRST_CASH_RATE, \n"); sqlstr.append(" A.OBC_MID_CASH_RATE, \n"); sqlstr.append(" A.OBC_MID_BILL_RATE, \n"); sqlstr.append(" A.OBC_FIN_CASH_RATE, \n"); sqlstr.append(" A.OBC_FIN_BILL_RATE, \n"); sqlstr.append(" A.OBC_FRST_CONDI, \n"); sqlstr.append(" A.OBC_MID_PAYDAY, \n"); sqlstr.append(" A.OBC_FIN_PAYDAY, \n"); sqlstr.append(" A.OBC_FRST_CNT_CONDI, \n"); sqlstr.append(" A.OBC_MID_REQ_MONTH, \n"); sqlstr.append(" A.OBC_MID_CNT, \n"); sqlstr.append(" A.OBC_FIN_CONDI, \n"); sqlstr.append(" A.OBC_EMP_RATE, \n"); sqlstr.append(" A.OBC_EMP_YN, \n"); sqlstr.append(" A.OBC_PENSION_RATE, \n"); sqlstr.append(" A.OBC_PENSION_YN, \n"); sqlstr.append(" A.OBC_HEALTH_RATE, \n"); sqlstr.append(" A.OBC_HEALTH_YN, \n"); sqlstr.append(" A.OBC_ELDER_RATE, \n"); sqlstr.append(" A.OBC_ELDER_YN, \n"); sqlstr.append(" A.OBC_WORK_RATE, \n"); sqlstr.append(" A.OBC_WORK_YN, \n"); sqlstr.append(" A.OBC_RESIGN_RATE, \n"); sqlstr.append(" A.OBC_RESIGN_YN, \n"); sqlstr.append(" A.OBC_SAFE_RATE, \n"); sqlstr.append(" A.OBC_SAFE_YN, \n"); sqlstr.append(" A.OBC_ENV_RATE, \n"); sqlstr.append(" A.OBC_ENV_YN, \n"); sqlstr.append(" A.OBC_SITE_RATE, \n"); sqlstr.append(" A.OBC_SITE_YN, \n"); sqlstr.append(" A.OBC_DUTY_RATE, \n"); sqlstr.append(" A.OBC_DUTY_YN, \n"); sqlstr.append(" A.PAY_DIV, \n"); sqlstr.append(" A.MATERIAL_GB, \n"); sqlstr.append(" A.DELI_START_DT, \n"); sqlstr.append(" A.DELI_END_DT, \n"); sqlstr.append(" A.DELI_END_PLACE, \n"); sqlstr.append(" A.KRW_APPR_YN, \n"); sqlstr.append(" B.USER_NAME, \n"); sqlstr.append(" B.E_MAIL, \n"); sqlstr.append(" D.E_MAIL EMP_MAIL, \n"); sqlstr.append(" DECODE(NVL(A.SITEDESC_FLAG, 'N'), 'N', '미실시', TO_CHAR(A.SITEDESCDATE, 'YYYY-MM-DD HH24:MI') || ' / ' || A.SITEDESCPLACE) BID_INFO, \n"); sqlstr.append(" (SELECT ACC_DEPT_NM FROM PUB0120T WHERE USER_SABUN = B.USER_SABUN) AS BUSEO_NM, \n"); sqlstr.append(" C.EVAL_EMPNM, \n"); sqlstr.append(" C.EVAL_TEL, \n"); sqlstr.append(" (SELECT ACC_DEPT_NM FROM PUB0120T WHERE USER_SABUN = (SELECT USER_SABUN FROM PUB0120T WHERE ACCT_ID = C.EVAL_EMPID)) AS TBE_BUSEO_NM , \n"); sqlstr.append(" (SELECT ACC_DEPT_NM FROM PUB0120T WHERE USER_SABUN = (SELECT USER_SABUN FROM PUB0120T WHERE ACCT_ID = A.SITEDESCCHRGPRSNID)) AS SITEDESC_BUSEO_NM \n"); sqlstr.append(" FROM BID_MASTER A, PUB0120T B , TBE_INFO C , PUB0120T D \n"); sqlstr.append(" WHERE A.PUREMPNO = B.ACCT_ID \n"); sqlstr.append(" AND A.BID_NO = C.BID_NO(+) \n"); sqlstr.append(" AND A.BID_DEG = C.BID_DEG(+) \n"); sqlstr.append(" AND A.EMP_NO = D.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; } //2016.5.26 입찰계획 완료후 현설담당자에게 메일발송 (윤현석과장 요청) public void Send_to_etec(RowSet SuppInfo, ValueObject bid_Master, String event) throws Exception { HandlerStorage Stroage = new HandlerStorage(); MessageSend messageSend = new MessageSend(Stroage); if(!"03".equals(event)){ return ; } //현설일때만 처리 boolean email = false; StringBuffer mailtitle = new StringBuffer(); StringBuffer mailtitlein = new StringBuffer(); StringBuffer mailContent = new StringBuffer(); if(!bid_Master.get("SITEDESCEMAIL").isEmpty()){ if("Y".equals(bid_Master.get("ONBID_YN"))){ mailtitle.append("* 현장설명회에 대한 공지 메일입니다."); mailtitlein.append("* 현장설명회에 대한 공지 메일입니다."); mailContent.append(" 아래와 같이 현장설명회 일정 및 참석업체 안내 드리오니 업무 참조하시기 바랍니다.
"); 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.현설일시 : " + bid_Master.get("SITEDESCDATE_NM") + "
"); mailContent.append("5.현설장소 : " + bid_Master.get("SITEDESCPLACE") + "
"); mailContent.append("6.입찰참여업체
"); for(int i=0;i < SuppInfo.size();i++){ Row row = SuppInfo.getRow(i); mailContent.append(" -" + row.get("VENDOR_NAME") + ", " +row.get("CHARGE_NAME") + ", " +row.get("CHAGE_HP") + ", " +row.get("CHARGE_EMAIL") + "
"); } mailContent.append("(상기 업체별 담당자는 실제 참석 인원과 상이할 수 있습니다)
"); mailContent.append("7.특기사항 : " + bid_Master.get("OUTER_REMARKS") + "
"); mailContent.append("
"); mailContent.append("일정 변경 및 기타 공지사항은 참여업체에 관련내용 통지 바랍니다 .
"); mailContent.append(""); email = true; } } if(email){ messageSend.sendEmail(bid_Master.get("SITEDESCEMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent); } } //2020.03.23 담당자 메일발송 추가 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); 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("
"); mailContent.append("1.프로젝트 명 : " + bid_Master.get("DEPT_NM") + "
"); mailContent.append("2.입찰번호 : " + bid_Master.get("BID_NO") + "
"); mailContent.append("3.발주명 : " + bid_Master.get("DEMANDED_TITLE") + "
"); mailContent.append("4.구매담당자 : " + bid_Master.get("USER_NAME") + "
"); mailContent.append("5.현장설명회 : " + bid_Master.get("BID_INFO") + "
"); mailContent.append("6.입찰마감 : " + bid_Master.get("BID_TO_DT_NM") + " (입찰마감기한 변경 시 통보 바랍니다)
"); mailContent.append("7.견적대상업체" ); mailContent.append("" ); mailContent.append("" ); for(int j=0;j < SuppInfo.size();j++){ mailContent.append("" ); } mailContent.append("
견적업체명연락처E-MAIL
 " + SuppInfo.getRow(j).get("VENDOR_NAME") + " " + SuppInfo.getRow(j).get("CORP_TEL") + " " + SuppInfo.getRow(j).get("CHARGE_EMAIL") + "
" ); mailContent.append(""); if(!bid_Master.get("E_MAIL").isEmpty()) { //구매담당자 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"); } } }