/**
 * @(#)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 oracle.toplink.internal.ox.StrBuffer;

/**
 * 
 * @version 1.0
 * @author Beomgeun Lee
 */
public class PBidSuccessSignDriver implements CommonSignInterface {

    /**
     * 테슽 파일
     * 
     */
    public PBidSuccessSignDriver() {
        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 { //2015.11.27 오류처리 
	    	success = saveProc(connection,signDoc);
	    	success = update_BidSupp(connection, signDoc , "10");//결재
	    	ValueObject vo = getDemanded_kind(connection, signDoc);
	    	ValueObject vo2 = getBidMaster(connection, signDoc);
	
	    	if(vo2.get("CNT").equals("0") ){
	    		success = update_BidMaster(connection, signDoc , "10");//결재
	    	}
	    	UPDATE_EBID_STATUS(connection, signDoc, vo);
	    	if(success){
	    		if("Y".equals(vo.get("ONBID_YN"))){
	    			Send(signDoc,"10");
	    			Send_ToeTEC(signDoc,"10"); //2020.03.23 담당자 메일발송 추가
	    		}	    		
	    	}
    	/*} catch (Exception e) {
    		 WAFLogger.error(this.getClass().getName() + " --> doFirstFinishSign() : " + e.getMessage());
             WAFLogger.error(e);
		}2016.5.18 오류있어도 계약서 생성. 오라클SEQ공번호가 너무 많이 발생함. */
    	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_BidSupp(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_PURCONT 프로시저 호출
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean saveProc(Connection connection, ValueObject vo) throws Exception {
		ValueObject rtnVo = new ValueObject();
		CallableStatement cs 	= null;
		String pssql			= "";
		try{
			pssql = "{call SP_BID_TO_PURCONT(?,?,?,?,?)}";
        	cs = connection.prepareCall(pssql);
        	cs.setString(1, vo.get("KEY_COL1"));
        	cs.setString(2, vo.get("KEY_COL2"));
        	cs.setString(3, vo.get("KEY_COL3"));
            cs.registerOutParameter(4, java.sql.Types.VARCHAR);
            cs.registerOutParameter(5, java.sql.Types.VARCHAR);
            cs.execute();
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(4));
            rtnVo.set("OUT_MESSAGE",cs.getString(5));   
            System.out.println("CODE : " + cs.getString(4));
            System.out.println("MSG : " + cs.getString(5));
            if("N".equals(cs.getString(4))){
            	throw new Exception(cs.getString(5));
            }
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> saveProc() : " + e.getMessage());
            WAFLogger.error(e);
            return false;
        }
    }
	
	
	/**
     * BID_MASTER 상태값 업데이트.
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean update_BidSupp(Connection connection, ValueObject valueObject , String status) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("  UPDATE BID_SUPP			\n");
        	sqlstr.append("  SET BID_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");
        	sqlstr.append("  AND VENDOR_CD = ?							\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.addParameter(valueObject.get("KEY_COL3"));
            persistent.execute();
            return true;
        }
        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;
        }
    }

	/**
	 * 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();
			return true;
		}
		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;
		}
	}
	
	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);
     		if(!row.get("CHARGE_EMAIL").isEmpty()){
     			boolean email = false;
     			String CURRENCY_GB = "";
         		StringBuffer mailtitle = new StringBuffer();
         		StringBuffer mailtitlein = new StringBuffer();
                StringBuffer mailContent = new StringBuffer();
                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>" );	//2022.02.15 구매팀 요청
             	mailContent.append("4.입찰담당자 : " +bid_Master.get("BUSEO_NM")+" / "+ bid_Master.get("USER_NAME") +" (연락처 : " + bid_Master.get("PO_EMPNM") + ", 이메일 : " + bid_Master.get("E_MAIL") +")<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 낙찰통보메일. 입찰담당자번호. 
	            }
     		}
     	}
	}
 /**
	 * 견적 대상 업체 를 검색하여 리턴
	 * @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																	\n");
        	sqlstr.append("	 ,A.CHARGE_EMAIL 																			\n");
        	sqlstr.append("	 ,A.CHAGE_HP 																					\n");
        	sqlstr.append("	 ,A.VENDOR_NAME 																			\n");
        	sqlstr.append("	 ,NVL(C.CURRENCY_GB,B.CURRENCY_GB) AS CURRENCY_GB 						\n");
        	//2020.01.07 낙찰통보메일에서 분할낙찰인 경우 실제 선정금액으로 낙찰금액 발송되도록 수정(송영주 과장) 
        	//sqlstr.append("	 ,DECODE(A.ESTI_TOT_AMT,-1,'단가계약',F_GET_D_CURRENCY(A.ESTI_TOT_AMT,F_GET_CURRENCY_DIGIT(NVL(C.CURRENCY_GB,B.CURRENCY_GB)))) AS TOT_AMT \n");
        	sqlstr.append("	 ,DECODE(A.ESTI_TOT_AMT,-1,'단가계약',F_GET_D_CURRENCY(DECODE(DIV_CONT_YN,'Y',A.SELECT_TOT_AMT,A.ESTI_TOT_AMT),F_GET_CURRENCY_DIGIT(NVL(C.CURRENCY_GB,B.CURRENCY_GB)))) AS TOT_AMT \n");
			sqlstr.append("	FROM BID_SUPP A,BID_MASTER B,BID_PUR_CONDI C			\n");
			sqlstr.append("	WHERE A.BID_NO = ?																		\n");
			sqlstr.append("	AND A.BID_DEG = ?																			\n");
			sqlstr.append("	AND 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.BID_DEG = C.BID_DEG																\n");
			sqlstr.append("	AND A.VENDOR_CD = C.VENDOR_CD														\n");
			sqlstr.append("	AND A.SUCC_YN = 'Y'																		\n");
			sqlstr.append("	AND A.VENDOR_CD = ?															 \n"); //2017.10.31 추가
			search.setStatement(sqlstr.toString());
			search.addParameter(param.get("KEY_COL1"));
			search.addParameter(param.get("KEY_COL2"));
			search.addParameter(param.get("KEY_COL3"));
			
			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,																												\n");
        	sqlstr.append("	    A.DEMANDED_TITLE,																											\n");
        	sqlstr.append("	    C.E_MAIL EMP_MAIL,																											\n");
        	sqlstr.append("	    SITEDESCEMAIL,																												\n");
        	sqlstr.append("	    TO_CHAR(TO_DATE(A.DELI_FROM_DY),'YYYY-MM-DD') AS WORK_FROM_DT,																\n");
        	sqlstr.append("	    TO_CHAR(TO_DATE(A.DELI_TO_DY),'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 {
        	if("B".equals(BidMaster.get("DEMANDED_KIND"))){
        		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 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();
        	}
        }
        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{
		    Persistent persistent = new PersistentImpl(connection);
	        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");
	            persistent.setStatement(sqlstr.toString());
	            persistent.addParameter(vo.get("KEY_COL1"));
	            persistent.addParameter(vo.get("KEY_COL2"));
	            rowSet = persistent.query();         
	            if (rowSet.size() > 0) {
	            	valueobject = RowSetUtility.getValueObject(persistent.query().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 ValueObject getBidMaster(Connection connection, ValueObject vo) throws Exception{
		  Persistent persistent = new PersistentImpl(connection);
		 RowSet rowSet = new RowSetImpl();
		 ValueObject valueobject = null;
		 StringBuffer sqlstr = new StringBuffer();       
		 try {
			 sqlstr.append(" SELECT COUNT(*) AS CNT         \n");
			 sqlstr.append(" FROM BID_SUPP                           \n");
			 sqlstr.append(" WHERE BID_NO = ?										\n");
			 sqlstr.append(" AND BID_DEG = ?											\n");
			 sqlstr.append(" AND      BID_STATUS_CD != '10'										\n");
			 sqlstr.append(" AND      SUCC_YN = 'Y'									\n");
			 persistent.setStatement(sqlstr.toString());
			 persistent.addParameter(vo.get("KEY_COL1"));
			 persistent.addParameter(vo.get("KEY_COL2"));
			 rowSet = persistent.query();
			 if (rowSet.size() > 0) {
				 valueobject = RowSetUtility.getValueObject(persistent.query().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;
		 }
	 }
	 
	//2020.03.23 담당자 메일발송 추가
	 public void Send_ToeTEC(ValueObject params, String event) throws Exception {
		HandlerStorage Stroage = new HandlerStorage();
		MessageSend messageSend = new MessageSend(Stroage);
     	ValueObject bid_Master = bid_Master_info(params);
     	RowSet SuppInfo = bid_supp_info2(params);
     	RowSet relEmpInfo = relation_emp_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>" );
        mailContent.append("5.계약기간 : " +bid_Master.get("WORK_FROM_DT")+" ~ "+ bid_Master.get("WORK_END_DT")+ "<br>");
        for(int i=0;i < SuppInfo.size();i++){
     		Row row = SuppInfo.getRow(i);
     		if(SuppInfo.size()==1){
     			mailContent.append("6.발주업체 : "+row.get("VENDOR_NAME")+"<br>");
     			mailContent.append("7.발주금액 : "+row.get("TOT_AMT")+"("+row.get("CURRENCY_GB")+")"+"<br>");
     		}else{
     			if(i==0)	mailContent.append("6.발주업체정보(발주업체, 발주금액)"+"<br>");
     			else		mailContent.append("  "+i+1+")"+row.get("VENDOR_NAME")+" : "+row.get("TOT_AMT")+"("+row.get("CURRENCY_GB")+")"+"<br>");
     		}
        }
        mailContent.append("</font><br>");

        //구매담당자 
        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");
        }	 
        
        //유관부서 담당자
        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 bid_supp_info2(ValueObject param) {

			Search search = new SearchImpl();
	        RowSet rowSet = new RowSetImpl();
	        
	     //   String sqlstr = "";
	        StrBuffer sqlstr = new StrBuffer();
	        try{ 
	        	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(C.CURRENCY_GB,B.CURRENCY_GB) AS CURRENCY_GB 											\n");
	        	sqlstr.append("	 		,DECODE(A.ESTI_TOT_AMT,-1,'단가계약',F_GET_D_CURRENCY(DECODE(DIV_CONT_YN,'Y',A.SELECT_TOT_AMT,A.ESTI_TOT_AMT),F_GET_CURRENCY_DIGIT(NVL(C.CURRENCY_GB,B.CURRENCY_GB)))) AS TOT_AMT \n");
				sqlstr.append("		FROM BID_SUPP A,BID_MASTER B,BID_PUR_CONDI C													\n");
				sqlstr.append("		WHERE A.BID_NO = ?																				\n");
				sqlstr.append("		AND A.BID_DEG = ?																				\n");
				sqlstr.append("		AND 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.BID_DEG = C.BID_DEG																		\n");
				sqlstr.append("		AND A.VENDOR_CD = C.VENDOR_CD																	\n");
				sqlstr.append("		AND A.SUCC_YN = 'Y'																				\n");
				sqlstr.append("	    AND A.VENDOR_CD = ?																				\n");
				sqlstr.append("		ORDER BY DECODE (DIV_CONT_YN, 'Y', A.SELECT_TOT_AMT, A.ESTI_TOT_AMT), A.VENDOR_NAME 			\n");
				search.setStatement(sqlstr.toString());
				search.addParameter(param.get("KEY_COL1"));
				search.addParameter(param.get("KEY_COL2"));
				search.addParameter(param.get("KEY_COL3"));
				
				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;
		}
		
		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;
		}
}