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

/**
 * @(#)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")+"님께 아래와 같은 내용으로 입찰서 제출을 요청드립니다.<br>협력업체포탈시스에 접속하시어 입찰서 작성 및 제출바랍니다.<br>입찰서 제출시 법인공인증서가 필요합니다.<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.견적마감일시 : " + bid_Master.get("BID_TO_DT_NM") + "<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>");
		                	email = true;
		                }else if("03".equals(event)){//현설의뢰
		                	mailtitle.append("* 현장설명회에 대한 공지 메일입니다.");
		                    mailtitlein.append("* 현장설명회에 대한 공지 메일입니다.");
		                 	mailContent.append(row.get("VENDOR_NAME")+"님께 아래와 같은 내용으로 현장설명회 참석을 요청드립니다.<br>협력업체포탈시스템에 접속하시어 현장설명회 내용 확인 후 참석의사를 입력해 주시기바랍니다.<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.현설일시 : " + bid_Master.get("SITEDESCDATE_NM") + "<br>");
		                 	mailContent.append("5.현설장소 : " + bid_Master.get("SITEDESCPLACE") + "<br>");
		                 	mailContent.append("6.현설담당자 : " +bid_Master.get("SITEDESC_BUSEO_NM")+" / "+ bid_Master.get("SITEDESC_CRG_NM") +" (연락처 : " + bid_Master.get("SITEDESCTELNO") + ", 이메일 : " + bid_Master.get("SITEDESCEMAIL") +")<br>");
		                 	mailContent.append("7.입찰담당자 : " +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 = "";
	     			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(" 아래와 같이 현장설명회 일정 및 참석업체 안내 드리오니 업무 참조하시기 바랍니다.  <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.현설일시 : " + bid_Master.get("SITEDESCDATE_NM") + "<br>");
                 	mailContent.append("5.현설장소 : " + bid_Master.get("SITEDESCPLACE") + "<br>");
                 	mailContent.append("6.입찰참여업체  <br>");
                 	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") + "<br>");
                 	}
                 	mailContent.append("(상기 업체별 담당자는 실제 참석 인원과 상이할 수 있습니다) <br>");
                 	mailContent.append("7.특기사항 : " + bid_Master.get("OUTER_REMARKS") + "<br>");
                 	mailContent.append("<br>");
                 	mailContent.append("일정 변경 및 기타 공지사항은 참여업체에 관련내용 통지 바랍니다 .<br>");
                 	mailContent.append("</font>");
                	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(" 아래와 같이 현장설명회 일정 및 참석업체 안내 드리오니 업무 참조하시기 바랍니다.  <br>");
		    mailContent.append("<style type='text/css'>");
	        mailContent.append("td {font-size:12px;}");
	        mailContent.append("</style>");
		    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("DEMANDED_TITLE")   + "<br>");
		    mailContent.append("4.구매담당자 : " + bid_Master.get("USER_NAME") + "<br>");
		    mailContent.append("5.현장설명회 : " + bid_Master.get("BID_INFO") + "<br>");
		    mailContent.append("6.입찰마감 : " + bid_Master.get("BID_TO_DT_NM") + " (입찰마감기한 변경 시 통보 바랍니다)<br>");
		    mailContent.append("7.견적대상업체"  );
		    mailContent.append("<table width='700' cellspacing='0' cellpadding='0' bordercolordark='#FFFFFF' bordercolorlight='#AEAFAF' border='1'>" );
	        mailContent.append("<tr><td align='Center' bgcolor='rgb(208,216,232)'>견적업체명</td><td align='Center' bgcolor='rgb(208,216,232)'>연락처</td><td align='Center' bgcolor='rgb(208,216,232)'>E-MAIL</td></tr>" );
	        
		    for(int j=0;j < SuppInfo.size();j++){
		    	 mailContent.append("<tr><td align='Left'>&nbsp;" + SuppInfo.getRow(j).get("VENDOR_NAME") + "</td><td align='Left'>&nbsp;" + SuppInfo.getRow(j).get("CORP_TEL") + "</td><td align='Left'>&nbsp;" + SuppInfo.getRow(j).get("CHARGE_EMAIL") + "</td></tr>" );
		    }
		    mailContent.append("</table>" );
		    mailContent.append("</font>");
		    
		    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");
		    }
		}		
}