ebid/java/kr/co/udapsoft/common/commonSign/driver/OBidSuccessSignDriver.java

/**
 * @(#)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")+"님이 참여하신 다음의 입찰에 낙찰업체로 선정되었음을 알려드립니다.<br>");
	             	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	             	mailContent.append("<br>");
	             	mailContent.append("1.프로젝트 : " + bid_Master.get("DEPT_NM") + "<br>");
	             	mailContent.append("2.공고번호 : " + bid_Master.get("BID_NO") + "<br>");
	             	mailContent.append("3.공고건명 : " + bid_Master.get("BID_MASTER_NM") + "<br>");
	             	mailContent.append("4.낙찰금액 : " + row.get("TOT_AMT") + CURRENCY_GB + "<br>" );
	             	mailContent.append("5.입찰담당자 : " +bid_Master.get("BUSEO_NM")+" / "+ bid_Master.get("USER_NAME") +" (연락처 : " + bid_Master.get("PO_EMPNM") +         	", 이메일 : " + bid_Master.get("E_MAIL") +")<br>");
	             	mailContent.append("</font><br>");
	             	mailContent.append("<font style='color:red; width:auto; font-size:13px;'>[유의사항]</font><br>");
	             	mailContent.append("<font style='text-decoration:underline; width:auto; font-size:12px;'>");
	             	mailContent.append("본 낙찰통지를 받은 후 낙찰업체는 당사가 계약서에 서명을 요청한 날로부터 15일 내에 <br>");
	             	mailContent.append("계약 관련 제반 서류(수입인지, 근재보험증권, 계약이행증권)를 제출하여 계약서에 서명하시기 바랍니다.<br>");
	             	mailContent.append("<br>");
	             	mailContent.append("만약 낙찰 업체가 서명 요청을 받은 날로부터 15일 내에 계약 서명 및 제반서류를 제출하지 못 할 경우<br>");
	             	mailContent.append("당사의 판단에 따라 해당 낙찰 및 계약이 취소될 수 있습니다.<br>");
	             	mailContent.append("</font>");
	            	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이앤씨(주) 입니다.<br>");
	             	mailContent.append("당사에서 실시한 입찰 결과를 다음과 같이 통보드립니다.<br>");
	             	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	             	mailContent.append("<br>");
	             	mailContent.append("1.프로젝트 : " + bid_Master.get("DEPT_NM") + "<br>");
	             	mailContent.append("2.공고건명 : " + bid_Master.get("BID_MASTER_NM") + "<br>");
	             	mailContent.append("3.입찰결과 : 미선정 <br>" );
	             	mailContent.append("<br>");
	             	mailContent.append("성실히 입찰에 참여해 주심에 깊은 감사의 뜻을 전하며 귀사에 무궁한 발전이 있기를 기원합니다.<br>");
	             	mailContent.append("</font>");
	            	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("아래와 같이 시행결의가 완료되었음을 알려드립니다.<br>");
	        mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	        mailContent.append("<br>");
	        mailContent.append("1.프로젝트 : " + bid_Master.get("DEPT_NM")  + "<br>");
	        mailContent.append("2.발주번호 : " + bid_Master.get("DEMANDED_NO")   + "<br>");
	        mailContent.append("3.발주건명 : " + bid_Master.get("DEMANDED_TITLE") + "<br>");
	        mailContent.append("4.외주담당자 : " + bid_Master.get("USER_NAME") + "<br>" );
	        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") + "<br>" );
		        	mailContent.append("6.낙찰금액 : " + SuppInfo.getRow(i).get("TOT_AMT") + SuppInfo.getRow(i).get("CURRENCY_GB") + "<br>" );
	        	}
	        }
	        mailContent.append("7.계약기간 : " +bid_Master.get("WORK_FROM_DT")+" ~ "+ bid_Master.get("WORK_END_DT")+ "<br>");
	        mailContent.append("8.비고  <br>");
	        mailContent.append("&nbsp;1)계약서는 시행결의 승인 후 1~2일 내 낙찰업체에 전달됩니다.   <br>");
	        mailContent.append("&nbsp;2)계약 완료 후 계약서 및 관련 서류는 전자조달시스템의 외주-계약결과에서 조회 가능합니다.   <br>");
	        mailContent.append("&nbsp;3)공사의 경우 계약 완료 전까지 현장 개설 불가합니다.  <br>");
	        mailContent.append("</font><br>");
	        //외주담당자 
	        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;
		}
}