/**
 * @(#)file    BidPlanBiz.java
 * @(#)author  Son Hyung Min 
 * @(#)version 1.0
 * @(#)date    2014-06-23
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.bid.purchase.biz;



import oracle.toplink.internal.ox.StrBuffer;
import weblogic.messaging.kernel.internal.Reader;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.base.WAFMessages;
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.sender.MessageSend;
import kr.co.udapsoft.common.util.Crosscert;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;

import java.sql.Clob;
import java.sql.Connection;

import com.bea.wls.ejbgen.Debug;
import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.modular.web.ibsheet7.util.IBSheet7Utils;
import com.udapsoft.waf.common.util.ClobUtils;
import com.udapsoft.waf.common.util.StringUtils;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class BidOpenBiz extends Biz {

	Crosscert Crosscert = new Crosscert();
	public BidOpenBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
		params.set("login_user", storage.getUser().get("ACCT_ID"));
		if(storage.compareEvent("FORM_SELECT")){
	            user = getOrderReqeustValueObject();
	            storage.addValueObject("Order", user);
	            /**		파일 목록 조회			 **/
		        storage.addValueObject(user.get("DOC_ETC_IN"), getfilelist(user.get("DOC_ETC_IN"),params.get("DEMANDED_ID"),user.get("DOC_ETC_IN")));
		        storage.addValueObject(user.get("DOC_ETC_OUT"), getfilelist(user.get("DOC_ETC_OUT"),params.get("DEMANDED_ID"),user.get("DOC_ETC_OUT")));
		        storage.addValueObject("SIGN_INFO", getSignInfo(params.get("DEMANDED_ID")));
		}else if(storage.compareEvent("FORM_OPEN_SELECT")){
			boolean open = bidOpen();
			if(open){
				user = getOrderReqeustValueObject();
				
				/*개찰시에 이메일 발송*/
				Send(user.get("OPEN_SABUN"));
				
				/*개찰시 TBE담당자 메일발송 2018.05.08*/
		        Send_Tbe(); 
		        
				/*개찰시 보고자 메일발송 2020.05.08*/
		        Send_Report(); 
		        
		        storage.addValueObject("Order", user);
		        storage.setSuccess(open);
		        /**		파일 목록 조회			 **/
		        storage.addValueObject(user.get("DOC_ETC_IN"), getfilelist(user.get("DOC_ETC_IN"),params.get("DEMANDED_ID"),user.get("DOC_ETC_IN")));
		        storage.addValueObject(user.get("DOC_ETC_OUT"), getfilelist(user.get("DOC_ETC_OUT"),params.get("DEMANDED_ID"),user.get("DOC_ETC_OUT")));
		        storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			}
		}else if(storage.compareEvent("FORM_OPEN_OPI_SAVE")){
			bid_master_opi_save(params);
			storage.addValueObject("Order", user);
			storage.setSuccess(true);
			storage.addValueObject(user.get("DOC_ETC_IN"), getfilelist(user.get("DOC_ETC_IN"),params.get("DEMANDED_ID"),user.get("DOC_ETC_IN")));
	        storage.addValueObject(user.get("DOC_ETC_OUT"), getfilelist(user.get("DOC_ETC_OUT"),params.get("DEMANDED_ID"),user.get("DOC_ETC_OUT")));	        
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("GET_CORP_LIST")){
			 params.set("sheet1", getCorpList());
	         storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("GET_ITEM_LIST")){
			 params.set("sheet2", geitemList(params));
	         storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("SAVE_SHEET")){
			boolean save = bidOpenSave();
            storage.setSuccess(save);
    		if(save){
    			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
    		}
		}
	}
	
	private ValueObject getfilelist(String filegb,String id,String returnid){
		ValueObject vo = new ValueObject();
		RowSet rowSet = FileManager.getFileList(filegb, id);
		String str="";
	        for(int i=0;i<rowSet.size();i++){
	        	Row row=rowSet.getRow(i);
	        	str += (i+1)+" : "+row.get("FILE_NAME")+" ("+ row.get("FILE_SIZE")+")";
	        	if(i+1!=rowSet.size()){
	        		str +="\r\n";
	        	}
	        }
	        vo.set(returnid, str);
		return vo;
	}

	/**
	 * 발주의뢰(외주) 접수를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private RowSet getBoardList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT A.BID_DEG																															\n");
        	sqlstr.append("      , B.DEPT_NM																																\n");
        	sqlstr.append("      , A.BID_NO																																\n");
        	sqlstr.append("      , A.DEMANDED_TITLE																													\n");
        	sqlstr.append("      , A.ONBID_YN																															\n");
        	sqlstr.append("      , TO_CHAR(A.REQUEST_DATE,'YYYY-MM-DD') AS REQUEST_DATE														\n");
        	sqlstr.append("      , TO_CHAR(A.RECEIPT_DATE,'YYYY-MM-DD') AS RECEIPT_DATE															\n");
        	sqlstr.append("      , A.STATUS_CD 																															\n");
        	sqlstr.append("      , F_GET_CODE_NAME('BID_STATUS_CD',STATUS_CD) AS STATUS_NM													\n");
        	sqlstr.append("      , A.DEMANDED_ID		 																												\n");
        	sqlstr.append("   FROM BID_MASTER A	, ACZ10100V B																									\n");
        	sqlstr.append("   WHERE A.DEPT_CD = B.DEPT_CD																										\n");
        	sqlstr.append("   AND A.STATUS_CD IN ('00','01','02')																									\n");
        	sqlstr.append("  AND A.DEMANDED_KIND IN ('C','P','B')	       																									\n");
        	if(!param.get("P_DEPT_CD").isEmpty())
        		sqlstr.append("   AND A.DEPT_CD = '"+param.get("P_DEPT_CD")+"'																											\n");
        	if(!param.get("p_demanded_title").isEmpty())
        		sqlstr.append("   AND A.DEMANDED_TITLE LIKE '%'|| '"+param.get("p_demanded_title")+"' ||'%'																						\n");
        	if(!param.get("P_DEMANDED_FROM_DT").isEmpty() && !param.get("P_DEMANDED_TO_DT").isEmpty())
        		sqlstr.append("   AND A.REQUEST_DATE BETWEEN  '"+param.get("P_DEMANDED_FROM_DT")+"' AND '"+param.get("P_DEMANDED_TO_DT")+"'		\n");
        	if(!param.get("P_RCEIPT_FROM_DT").isEmpty() && !param.get("P_RCEIPT_TO_DT").isEmpty())
        		sqlstr.append("   AND A.REQUEST_DATE BETWEEN  '"+param.get("P_RCEIPT_FROM_DT")+"' AND '"+param.get("P_RCEIPT_TO_DT")+"'					\n");
        	if(!param.get("P_DEMANDED_STATUS").isEmpty())
        		sqlstr.append("   AND A.STATUS_CD = '"+param.get("P_DEMANDED_STATUS")+"'																						\n");
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}
	
	/**
	 * 발주의뢰(외주) 접수 상세 / 견적대상업체 를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private RowSet getCorpList() {
		Search search = new SearchImpl();
        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.VENDOR_CD,										\n");
        	sqlstr.append("        A.VENDOR_NO,										\n");
        	sqlstr.append("        A.VENDOR_NAME,									\n");
        	sqlstr.append("        A.REPRE_NO,										\n");
        	sqlstr.append("        A.CHARGE_NAME,									\n");
        	sqlstr.append("        A.CHAGE_HP,										\n");
        	sqlstr.append("        A.CHARGE_EMAIL,									\n");
        	//sqlstr.append("        A.CURRENCY_GB,									\n");
        	//sqlstr.append("        A.EXCHANGE,									\n");
        	sqlstr.append("        NVL(NVL(C.CURRENCY_GB,A.CURRENCY_GB),'KRW') AS CURRENCY_GB,			\n");
        	sqlstr.append("        DECODE(NVL(NVL(C.CURRENCY_GB,A.CURRENCY_GB),'KRW'),'KRW',1,A.EXCHANGE) AS EXCHANGE, \n");
        	sqlstr.append("        A.DELIVERY_CONDITION,						\n");
        	sqlstr.append("        A.SITEPARTI_YN,									\n");
        	sqlstr.append("        A.TBEAPPR_YN,									\n");
        	sqlstr.append("        A.TBEEVALUATE_YN,								\n");
        	sqlstr.append("        A.CERT_DN,											\n");
        	sqlstr.append("        A.ESTI_SIGNDATA,								\n");
        	sqlstr.append("        DECODE(A.BID_RNNC_DT,NULL,DECODE(A.SIGN_DT,'','미제출',TO_CHAR(A.SIGN_DT,'YYYY-MM-DD HH24:MI:SS')),'입찰포기 ('||TO_CHAR(A.BID_RNNC_DT,'YYYY-MM-DD HH24:MI:SS')||')') AS SIGN_DT,				\n");
        	sqlstr.append("        A.ATT_ID,											\n");
        	sqlstr.append("        A.RECM_DEPT_ID,									\n");
        	sqlstr.append("        A.RECM_DEPT_NM,								\n");
        	sqlstr.append("        A.RECM_EMPID,									\n");
        	sqlstr.append("        A.RECOMMEND_REASON,						\n");
        	sqlstr.append("        DECODE(A.ESTI_TOT_AMT,-1,'단가계약',F_GET_D_CURRENCY(A.ESTI_TOT_AMT,F_GET_CURRENCY_DIGIT(NVL(C.CURRENCY_GB,'KRW')))) AS ESTI_TOT_AMT,								\n");
        	sqlstr.append("        A.WON_ESTI_TOT_AMT,						\n");
        	sqlstr.append("        A.SUCC_YN,										\n");
        	sqlstr.append("        A.RNNC_REASON,									\n");
        	sqlstr.append("        A.BID_RNNC_NM,									\n");
        	sqlstr.append("        TO_CHAR(A.BID_RNNC_DT,'YYYY-MM-DD HH24:MI') AS BID_RNNC_DT,									\n");
        	sqlstr.append("        A.FIB_REASON,									\n");
        	sqlstr.append("        A.FILE_GB,											\n");
        	sqlstr.append("        F_GET_CODE_NAME('TBE_EVAL',EVAL_RESULT) AS TBE_STATUS_NM,										\n");
        	sqlstr.append("        B.STATUS_CD AS TBE_STATUS_CD																					\n");
        	sqlstr.append("   FROM BID_SUPP A	, TBE_SUPP B, BID_PUR_CONDI 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.VENDOR_CD = B.VENDOR_CD(+)			\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.BID_NO = '"+params.get("BID_NO")+"'		\n");
        	sqlstr.append(" AND A.BID_DEG = "+params.get("BID_DEG")+"	\n");
        	if(("Y".equals(params.get("SITEDESC_FLAG")) || "예".equals(params.get("SITEDESC_FLAG")))&&"Y".equals(params.get("SITEPARTI_YN")))
        		sqlstr.append(" AND A.SITEPARTI_YN = 'Y'							\n");
        	sqlstr.append(" ORDER BY A.ESTI_TOT_AMT ASC,A.SIGN_DT DESC						\n");
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        
        return rowSet;
	}
	
	/**
	 * 발주의뢰(외주) 접수 상세를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private ValueObject getOrderReqeustValueObject() {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("       SELECT TO_CHAR(TO_DATE(A.DEMANDED_DATE,'YYYYMMDD'),'YYYY-MM-DD') AS DEMANDED_DATE,                                        \n");
		       sqlstr.append("       A.DEMANDED_TITLE,                                        \n");
		       sqlstr.append("       A.DEMANDED_KIND,                                        \n");
		       sqlstr.append("       DECODE(A.DEMANDED_KIND , 'O', '외주' , '구매') AS DEMANDED_KIND_NM  ,                                        \n");
		       sqlstr.append("       F_GET_CODE_NAME('911',A.CURRENCY_GB) AS CURRENCY_GB_NM,                                        \n");
		       sqlstr.append("       A.CURRENCY_GB,                                        \n");
		       sqlstr.append("       A.DEPT_CD,                                        \n");
		       sqlstr.append("       A.EMP_NO,                                        \n");
		       sqlstr.append("       GET_USER_BUSEO_NM(A.EMP_NO) AS BUSEO_NM,                                          \n");
		       sqlstr.append("       A.DELIVERY_CONDITION,                                        \n");
		       sqlstr.append("       A.CRE_BY,                                        \n");
		       sqlstr.append("       A.CRE_DATE,                                        \n");
		       sqlstr.append("       A.UPD_BY,                                        \n");
		       sqlstr.append("       A.UPD_DATE,                                        \n");
		       sqlstr.append("       A.DEMANDED_NO,                                        \n");
		       sqlstr.append("       A.BID_APP_DATE,                                        \n");
		       sqlstr.append("       A.BID_APP_NO,                                        \n");
		       sqlstr.append("       A.PAY_ITEM,                                        \n");
		       sqlstr.append("       F_GET_PAY_ITEM_VIEW(A.PAY_ITEM,A.COST_CODE,A.COST_TYPE,A.DEPT_CD) AS PAY_ITEM_NM,                \n");
		       sqlstr.append("       A.COST_CODE,                                        \n");
		       sqlstr.append("       A.COST_TYPE,                                        \n");
		       sqlstr.append("       TO_CHAR(A.RECEIPT_DATE,'YYYY-MM-DD') AS RECEIPT_DATE,                                        \n");
		       sqlstr.append("       DECODE(A.SITEDESC_FLAG,'Y','예','N','아니오','') AS SITEDESC_FLAG,                                        \n");
		       sqlstr.append("       A.SITEDESCCHRGPRSNID,                                        \n");
		       sqlstr.append("       F_GET_USER_NM(A.SITEDESCCHRGPRSNID) AS SITEDESCCHRGPRSNNM,                                        \n");
		       sqlstr.append("       A.SITEDESCTELNO,                                        \n");
		       sqlstr.append("       TO_CHAR(A.SITEDESCDATE,'YYYY-MM-DD HH24:MI') AS SITEDESCDATE,                                        \n");
		       sqlstr.append("       TO_CHAR(A.SITEDESCDATE,'YYYY-MM-DD') AS SITEDESCDT,                                        \n");
		       sqlstr.append("       A.SITEDESCPLACE,                                        \n");
		       sqlstr.append("       A.SITEDESCEMAIL,                                        \n");
		       sqlstr.append("       A.PUREMPNO,                                        \n");
		       sqlstr.append("       F_GET_USER_NM(A.PUREMPNO) AS PUREMPNM,                                        \n");
		       sqlstr.append("       A.INNER_REMARKS,                                        \n");
		       sqlstr.append("       A.TBE_FLAG,                                        \n");
		       sqlstr.append("       DECODE(A.TBE_FLAG,'Y','예','N','아니오','') AS TBE_FLAG_NM,                   \n");
		       sqlstr.append("       A.OUTER_REMARKS,                                        \n");
		       sqlstr.append("       A.DEMANDED_ID,                                        \n");
		       sqlstr.append("       TO_CHAR(A.REQUEST_DATE,'YYYY-MM-DD') AS REQUEST_DATE,                                        \n");
		       sqlstr.append("       A.ATT_ID,                                        \n");
		       sqlstr.append("       A.BID_NO,                                        \n");
		       sqlstr.append("       A.BID_DEG,                                        \n");
		       sqlstr.append("       F_GET_CODE_NAME('ONBID',ONBID_YN) AS ONBID_YN,                                       \n");
		       sqlstr.append("       F_GET_CODE_NAME('CMPT_CD',A.CMPT_CD) AS CMPT_NM,                                        \n");
		       sqlstr.append("       A.CMPT_CD,                                        \n");
		       sqlstr.append("       F_GET_CODE_NAME('SUCC_CD',A.SUCC_CD) AS SUCC_NM,                                        \n");
		       sqlstr.append("       A.SUCC_CD,                                        \n");
		       sqlstr.append("       TO_CHAR(TO_DATE(A.NOTI_DY,'YYYYMMDD'),'YYYY-MM-DD') AS NOTI_DY,                                        \n");
		       sqlstr.append("       A.SITEDESC_CRG_NM,                                        \n");
		       sqlstr.append("       TO_CHAR(A.BID_FROM_DT,'YYYY-MM-DD HH24:MI') AS BID_FROM_DT,                                        \n");
		       sqlstr.append("       TO_CHAR(A.BID_TO_DT,'YYYY-MM-DD HH24:MI') AS BID_TO_DT,                                        \n");
		       sqlstr.append("       A.PO_EMPNM,                                        \n");
		       sqlstr.append("       A.OPEN_EMPNM,                                        \n");
		       sqlstr.append("       A.OPEN_EMPID,                                        \n");
		       sqlstr.append("       GET_SABUN(A.OPEN_EMPID) AS OPEN_SABUN,                                        \n");
		       sqlstr.append("       TO_CHAR(TO_DATE(A.DELI_FROM_DY,'YYYYMMDD'),'YYYY-MM-DD') AS DELI_FROM_DY,                                        \n");
		       sqlstr.append("       TO_CHAR(TO_DATE(A.DELI_TO_DY,'YYYYMMDD'),'YYYY-MM-DD') AS DELI_TO_DY,                                        \n");
		       sqlstr.append("       A.DELIVERY_PLACE,                                        \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.DEPT_NM,                                        \n");
		       sqlstr.append("       A.REQ_DEPT_CD,                                        \n");
		       sqlstr.append("       A.REQ_DEPT_NM,                                        \n");
		       sqlstr.append("       A.EMP_NM,                                        \n");
		       sqlstr.append("       A.STATUS_CD,                                        \n");
		       sqlstr.append("       F_GET_CODE_NAME('BID_STATUS_CD',A.STATUS_CD) AS STATUS_NM,                                      \n");
		       sqlstr.append("       A.OPEN_DT,                                        \n");
		       sqlstr.append("       A.CONT_APP_DATE,                                        \n");
		       sqlstr.append("       A.CONT_APP_NO,                                        \n");
		       sqlstr.append("       A.CONT_AMT,                                        \n");
		       sqlstr.append("       A.ACT_AMT,                                        \n");
		       sqlstr.append("       A.PUR_AMT,                                        \n");  //2015.02.27 구매외주예산추가 mklee
		       sqlstr.append("       A.ACT_RATE,                                        \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.CURRENCY_GB_YN,                                        \n");
		       sqlstr.append("       A.DELIVERY_CONDITION_YN,                                        \n");
		       //sqlstr.append("       A.EXCHANGE                                        \n");
		       sqlstr.append("       TO_CHAR(B.SUPP_REQ_DY,'YYYY-MM-DD') AS SUPP_REQ_DY,                                        \n");
		       sqlstr.append("       B.EVAL_EMPNM,                                        \n");
		       sqlstr.append("       B.EVAL_EMPID,                                        \n");
		       sqlstr.append("       TO_CHAR(TBE_FIN_DT,'YYYY-MM-DD') AS TBE_FIN_DT,				\n");
		       sqlstr.append("       A.OUT_ITEM_CD,														\n");
		       sqlstr.append("       GET_OUT_ITEM_NM(A.OUT_ITEM_CD) AS OUT_ITEM_NM,						\n");
		       sqlstr.append("       A.CURRENCY_GB_YN,													\n");
		       sqlstr.append("       A.PBC_DEFECT_CONDI,			\n");
		       sqlstr.append("       A.CBC_DERECT_TERM_CD,			\n");
		       sqlstr.append("       F_GET_CODE_NAME('CBC_DERECT_TERM_CD',A.CBC_DERECT_TERM_CD) AS CBC_DERECT_TERM_NM,			\n");
		       sqlstr.append("       A.CBC_DEFECT_DAY,				\n");
		       sqlstr.append("       A.PBC_DEFECT_CONDI2,			\n");
		       sqlstr.append("       A.CBC_DERECT_TERM_CD2,			\n");
		       sqlstr.append("       A.CBC_DEFECT_DAY2,				\n");
		       sqlstr.append("       A.WARR_RATE,				\n");
		       sqlstr.append("       A.WARR_CONDI,				\n");
		       sqlstr.append("       A.CONTFUL_RATE,				\n");
		       sqlstr.append("       A.CONTFUL_CONDI,			\n");
		       sqlstr.append("       A.PBC_FRSTGUAR_RATE,			\n");
		       sqlstr.append("       A.PBC_FRSTGUAR_CONDI,			\n");
		       sqlstr.append("       A.PBC_REST_RATE,				\n");
		       sqlstr.append("       A.PBC_REST_CONDI,				\n");
		       sqlstr.append("       A.CPC_MID_RATE,				\n");
		       sqlstr.append("       A.PBC_MID_CONDI,				\n");
		       sqlstr.append("       A.CBC_FIRST_RATE,				\n");
		       sqlstr.append("       A.PBC_FIRST_CONDI,			\n");
		       sqlstr.append("       A.CBC_DAMAGE_RATE,			\n");
		       sqlstr.append("       A.CBC_DAMAGE_MAXRATE,			\n");
		       sqlstr.append("       F_GET_CODE_NAME('LIQ_DMG_CD', A.CBC_DAMAGE_CD) AS CBC_DAMAGE_NM,			\n");
		       sqlstr.append("       A.PBC_OPTION_COMMENT,			\n");
		       sqlstr.append("       A.PBC_OPTION_YN,				\n");
		       sqlstr.append("       A.PBC_FEE_YN,				\n");
		       sqlstr.append("       A.DELIVERY_CONDITION,			\n");
		       sqlstr.append("       F_GET_CODE_NAME('PUR_LEAD_GB',A.DELIVERY_CONDITION) AS DELIVERY_CONDITION_NM,			\n");
		       sqlstr.append("       A.DELIVERY_CONDITION_YN,			\n");
		       sqlstr.append("       A.PBC_LANDTRAN_YN,			\n");
		       sqlstr.append("       A.PBC_PACK_YN,				\n");
		       sqlstr.append("       A.PBC_DOC_YN,				\n");
		       sqlstr.append("       A.PBC_TEST_YN,				\n");
		       sqlstr.append("       A.PBC_SPETOOL_YN,				\n");
		       sqlstr.append("       A.PBC_COMMISS_YN,				\n");
		       sqlstr.append("       A.PBC_INSTALL_YN,				\n");
		       sqlstr.append("       A.PBC_CBE_TYPE_CD,			\n");
		       sqlstr.append("       A.DIV_PO_YN	,					\n");
		       sqlstr.append("       B.TBE_FIN_DT,						\n");
		       sqlstr.append("       B.STATUS_CD AS TBE_STATUS_CD,		\n");
		       sqlstr.append("       A.REBID_REASON,						\n");  //2015.3.3 재견적사유 mklee
		       sqlstr.append("       A.REBID_OUTREASON,						\n");  //2015.3.19 재견적 외부 사유 CHJ
		       sqlstr.append("       A.OPEN_OPINION,						\n");  //2015.3.27 개찰의견
		       sqlstr.append("       A.MAIN_PR_CONTENTS,					\n");
		       sqlstr.append("       A.BID_SUPP_SELECT_REASON				\n");
		       sqlstr.append("        FROM BID_MASTER A  , TBE_INFO B											\n");
		       sqlstr.append("       WHERE A.BID_NO = '"+params.get("BID_NO")+"'							\n");
		       sqlstr.append("       AND A.BID_DEG = '"+params.get("BID_DEG")+"'								\n");
		       sqlstr.append("       AND A.BID_NO = B.BID_NO(+)													\n");
			   sqlstr.append("       AND A.BID_DEG = B.BID_DEG(+)												\n");
        	search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 )
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {
        	if( valueobject == null ) valueobject = new ValueObject();
            return valueobject;
        }
	}
	
	
	/**
	 * 개찰
	 * @param Params
	 * @return
	 */
	private boolean bidOpen() {
        try{
        	conn.begin();
        	RowSet CorpList = getCorpList();
        	ValueObject valueObject = null;
        	if(CorpList.size() > 0)
        		bid_supp_esti_delete(conn.getConnection(),params);
     
        	for(int i = 0; i < CorpList.size();i++){
        		ValueObject Esti_Tot_Amt  = null;
        		valueObject = RowSetUtility.getValueObject(CorpList.getRow(i));
        		String esti_signdata = readClob(valueObject.getObject("ESTI_SIGNDATA"));
        		if(esti_signdata.length() > 0){
        			
        			String esti_signdata2 = "";
        			esti_signdata2 = Crosscert.decryptData(esti_signdata);
    				if("20971528".equals(esti_signdata2)){
    					esti_signdata2 = Crosscert.decryptDataOld(esti_signdata);
    				}
        			
	        		String[] totsum = esti_signdata2.split("╂");//합계 잘라내기
	        		String[] row = totsum[0].split("↕");
	        		
	        		for(int j=0;j < row.length;j++){
	        			String[] Data = row[j].split("┃");
	        			ValueObject bid_supp_esti = new ValueObject();
	        			if(Data[0].equals("NULL"))Data[0] = "";
	        			bid_supp_esti.set("BID_NO", Data[0]);
	        			if(Data[1].equals("NULL"))Data[1] = "";
	        			bid_supp_esti.set("BID_DEG", Data[1]);
	        			if(Data[2].equals("NULL"))Data[2] = "";
	        			bid_supp_esti.set("VENDOR_CD", Data[2]);
	        			params.set("VENDOR_CD", Data[2]);
	        			if(Data[3].equals("NULL"))Data[3] = "";
	        			bid_supp_esti.set("MATERIAL_CD", Data[3]);
	        			if(Data[4].equals("NULL"))Data[4] = "";
	        			bid_supp_esti.set("QTY", Data[4]);
	        			if(Data[5].equals("NULL"))Data[5] = "";
	        			bid_supp_esti.set("UNIT_COST", Data[5]);
	        			if(Data[6].equals("NULL"))Data[6] = "";
	        			bid_supp_esti.set("ROW_SUM_AMT", Data[6]);
	        			if(Data[7].equals("NULL"))Data[7] = "";
	        			bid_supp_esti.set("NOTI", Data[7]);
	        			
	        			bid_supp_esti_insert(conn.getConnection(),bid_supp_esti);
	        			Esti_Tot_Amt = bid_supp_esti;
	        		}
	        		//공급사 총 금액 업데이트
	        		Esti_Tot_Amt.set("ESTI_TOT_AMT", totsum[1]);
	        		Esti_Tot_Amt.set("FILE_GB", totsum[2]);
	        		
	        			
	        		bid_supp_update(conn.getConnection(),Esti_Tot_Amt);
	        		if(totsum.length > 3)
	        			BID_PUR_CONDI_insert(conn.getConnection(),totsum[3]);	
        		}
        	}
        	//개찰 시간, 개찰자 , 업데이트
        	
        	bid_master_update(conn.getConnection(),params);

        	//개찰 상태 경영정보 업데이트
        	if("B".equals(params.get("DEMANDED_KIND"))){
        		update_mrreq_po(conn.getConnection(),"04");//개찰
        	}
        	
        	return true; 
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> saveProc() : " + e.getMessage());
            WAFLogger.error(e);
            conn.setRollback();

            storage.setDetailMessage(e);
            return false;
        }
        finally {
            conn.end();
        }
	}
	
    /**
     * Clob Object에서 데이터를 읽어 리턴한다.
     * @param object
     * @return
     * @throws Exception
     */
    private String readClob(Object object) throws Exception {

        if( object == null ) {
        
            return "";
        }
        Clob clob = (Clob)object;
        return ClobUtils.getClob(clob);
    }
    
    /**
     * 견적 금액 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void bid_supp_esti_delete(Connection connection, ValueObject valueObject) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append(" DELETE BID_SUPP_ESTI WHERE BID_NO = ? AND BID_DEG = ? 	\n");
        	persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("BID_NO"));
            persistent.addParameter(valueObject.get("BID_DEG"));
            persistent.execute();
            
            persistent = new PersistentImpl(connection);
            sqlstr = new StrBuffer();
            sqlstr.append(" DELETE BID_PUR_CONDI WHERE BID_NO = ? AND BID_DEG = ? 	\n");
        	persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("BID_NO"));
            persistent.addParameter(valueObject.get("BID_DEG"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> bid_supp_esti_delete() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
    /**
     * 견적 금액 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void bid_supp_esti_insert(Connection connection, ValueObject valueObject) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append(" INSERT INTO BID_SUPP_ESTI ( BID_NO,BID_DEG,MATERIAL_CD,VENDOR_CD,QTY,ROW_SUM_AMT,UNIT_COST,NOTI )			\n");
        	sqlstr.append("        VALUES ( ? , ? , ? , ? , ? , ? , ? , ? )		\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("BID_NO"));
            persistent.addParameter(valueObject.get("BID_DEG"));
            persistent.addParameter(valueObject.get("MATERIAL_CD"));
            persistent.addParameter(valueObject.get("VENDOR_CD"));
            persistent.addParameter(valueObject.get("QTY"));
            persistent.addParameter(valueObject.get("ROW_SUM_AMT"));
            persistent.addParameter(valueObject.get("UNIT_COST"));
            persistent.addParameter(valueObject.get("NOTI"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> bid_supp_esti_insert() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
	
	/**
     * 견적대상업체 총금액 업데이트
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void bid_supp_update(Connection connection, ValueObject valueObject) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("UPDATE BID_SUPP	\n");
        	sqlstr.append("SET ESTI_TOT_AMT = ?	\n");
        	sqlstr.append(",FILE_GB = ?	\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(valueObject.get("ESTI_TOT_AMT"));
            persistent.addParameter(valueObject.get("FILE_GB"));
            persistent.addParameter(storage.getUser().get("ACCT_ID"));
            persistent.addParameter(valueObject.get("BID_NO"));
            persistent.addParameter(valueObject.get("BID_DEG"));
            persistent.addParameter(valueObject.get("VENDOR_CD"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> bid_supp_update() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
	
	/**
     * 견적대상업체 총금액 업데이트
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void bid_master_update(Connection connection, ValueObject valueObject) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        String status_code = "04";
        try {
        	sqlstr.append("UPDATE BID_MASTER	\n");
        	sqlstr.append("SET   OPEN_DT = SYSDATE	\n");
        	sqlstr.append("     ,OPEN_EMPID = ?	\n");
        	sqlstr.append("     ,OPEN_EMPNM = (SELECT USER_NAME FROM PUB0120T WHERE ACCT_ID = ?)	\n");
        	sqlstr.append("     ,OPEN_OPINION = ?	\n");  //2015.3.27 개찰의견
        	sqlstr.append("     ,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");
        	if("예".equals(valueObject.get("TBE_FLAG")) || "Y".equals(valueObject.get("TBE_FLAG"))){
        		if("10".equals(valueObject.get("TBE_STATUS_CD"))){//TBE가 끝난 경우
        			status_code = "06";//낙찰업체선정
        		}else{
        			status_code = "05";//기술검토중
        		}
        	}else if("아니오".equals(valueObject.get("TBE_FLAG")) || "N".equals(valueObject.get("TBE_FLAG"))){
        		status_code = "06";//낙찰업체선정
        	}
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(storage.getUser().get("ACCT_ID"));
            persistent.addParameter(storage.getUser().get("ACCT_ID"));
            persistent.addParameter(valueObject.get("OPEN_OPINION"));  //2015.3.27 개찰의견
            persistent.addParameter(status_code);
            persistent.addParameter(storage.getUser().get("ACCT_ID"));
            persistent.addParameter(valueObject.get("BID_NO"));
            persistent.addParameter(valueObject.get("BID_DEG"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> bid_master_update() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
	
	public void bid_master_opi_save(ValueObject valueObject) throws Exception {
	   	conn.begin();
        Persistent persistent = new PersistentImpl( conn.getConnection());
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("UPDATE BID_MASTER	\n");
        	sqlstr.append("SET   OPEN_OPINION = ?	\n");  //2015.3.27 개찰의견
        	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(valueObject.get("OPEN_OPINION"));  //2015.3.27 개찰의견
            persistent.addParameter(storage.getUser().get("ACCT_ID"));
            persistent.addParameter(valueObject.get("BID_NO"));
            persistent.addParameter(valueObject.get("BID_DEG"));
            
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> bid_master_update() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
        finally {
            conn.end();
        }
    }
	
	/**
	 * 입찰내역 리스트 조회
	 * @param Params
	 * @return
	 */
	private RowSet geitemList(ValueObject param) {
		Search search = new SearchImpl();
        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.MATERIAL_CD,          \n");
        	sqlstr.append("           TO_CHAR(TO_DATE(A.REQUEST_DATE,'YYYYMMDD'),'YYYY-MM-DD') AS REQUEST_DATE,          \n");
        	sqlstr.append("           A.CONT_UNIT_AMT,          \n");
        	sqlstr.append("           A.ACT_UNIT_AMT,          \n");
        	sqlstr.append("           A.DEMANDED_PRI,          \n");
        	sqlstr.append("           A.DEMANDED_QTY,          \n");
        	sqlstr.append("           B.MATERIAL_SPEC AS STD,          \n");
        	sqlstr.append("           B.MATERIAL_NAME,          \n");
        	sqlstr.append("           F_GET_UNIT_KIND_NAME(A.MATERIAL_CD) AS UNIT_KIND          \n");
        	sqlstr.append("      FROM BID_ITEM A , PMM_COMM_MATERIAL_MASTER B          \n");
        	sqlstr.append("      WHERE A.MATERIAL_CD = B.MATERIAL_CD          \n");
        	sqlstr.append("        AND A.BID_NO = ?																														\n");
        	sqlstr.append("        AND A.BID_DEG = ?																														\n");	
        	sqlstr.append("        ORDER BY A.MATERIAL_CD																												\n");
			search.setStatement(sqlstr.toString());
			search.addParameter(param.get("BID_NO"));
			search.addParameter(param.get("BID_DEG"));
            rowSet = search.execute();
        }catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }finally{    
            if(rowSet == null)rowSet = new RowSetImpl();
        }
        return rowSet;
	}
	
	/**
     * 견적대상업체 총금액 업데이트
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void BID_PUR_CONDI_insert(Connection connection, String Data) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	String[] ColData = Data.split("┃");
        	sqlstr.append("INSERT INTO BID_PUR_CONDI 					\n");
        	sqlstr.append("       (  BID_NO 										\n");
        	sqlstr.append("         ,BID_DEG 										\n");
        	sqlstr.append("         ,VENDOR_CD	 								\n");
        	sqlstr.append("         ,CURRENCY_GB_YN	 						\n");
        	sqlstr.append("         ,CURRENCY_GB	 							\n");
        	sqlstr.append("         ,INSTALL_YN 									\n");
        	sqlstr.append("         ,COMMISS_YN	 							\n");
        	sqlstr.append("         ,SPETOOL_YN	 								\n");
        	sqlstr.append("         ,TEST_YN 										\n");
        	sqlstr.append("         ,DOC_YN	 									\n");
        	sqlstr.append("         ,PACK_YN 										\n");
        	sqlstr.append("         ,LANDTRAN_YN 								\n");
        	sqlstr.append("         ,DELIVERY_CONDITION_YN				\n");
        	sqlstr.append("         ,DELIVERY_CONDITION						\n");
        	sqlstr.append("         ,FEE_NOTI										\n");
        	sqlstr.append("         ,OPTION_NOTI								\n");
        	sqlstr.append("         ,QUOT_NO										\n");		
        	sqlstr.append("         ,PAYCONDI_YN								\n");
        	sqlstr.append("         ,PAYCONDI_NOT_NOTI						\n");
        	sqlstr.append("         ,CONTFUL_YN									\n");
        	sqlstr.append("         ,CONTFUL_NOT_NOTI						\n");
        	sqlstr.append("         ,FRSTGUAR_YN								\n");
        	sqlstr.append("         ,FRSTGUAR_NOT_NOTI						\n");
        	sqlstr.append("         ,WARR_YN										\n");
        	sqlstr.append("         ,WARR_NOT_NOTI							\n");
        	sqlstr.append("         ,DEFECT_YN									\n");
        	sqlstr.append("         ,DEFECT_NOT_NOTI							\n");
        	sqlstr.append("         ,BID_CONDI_NOTI 							\n");
        	sqlstr.append("         ,CBC_FIRST_RATE 							\n");
        	sqlstr.append("         ,PBC_FIRST_CONDI 							\n");
        	sqlstr.append("         ,CPC_MID_RATE 								\n");
        	sqlstr.append("         ,PBC_MID_CONDI 							\n");
        	sqlstr.append("         ,PBC_REST_RATE 							\n");
        	sqlstr.append("         ,PBC_REST_CONDI 							\n");
        	sqlstr.append("       ) VALUES (									 	\n");
        	sqlstr.append("		  ?													\n");
        	sqlstr.append("         ,? 												\n");
        	sqlstr.append("         ,?										 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,?		 	\n");
        	sqlstr.append("         ,?		 	\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("         ,? 			\n");
        	sqlstr.append("       ) 													\n");
        	persistent.setStatement(sqlstr.toString());
        	persistent.addParameter(params.get("BID_NO"));
        	persistent.addParameter(params.get("BID_DEG"));
        	persistent.addParameter(params.get("VENDOR_CD"));
        	
        	persistent.addParameter(Datareturn(ColData[0]) );
        	persistent.addParameter(Datareturn(ColData[1]) );
        	persistent.addParameter(Datareturn(ColData[2]) );
        	persistent.addParameter(Datareturn(ColData[3]) );
        	persistent.addParameter(Datareturn(ColData[4]) );
        	persistent.addParameter(Datareturn(ColData[5]) );
        	persistent.addParameter(Datareturn(ColData[6]) );
        	persistent.addParameter(Datareturn(ColData[7]) );
        	persistent.addParameter(Datareturn(ColData[8]) );
        	persistent.addParameter(Datareturn(ColData[9]) );
        	persistent.addParameter(Datareturn(ColData[10]));
        	persistent.addParameter(Datareturn(ColData[11]));
        	persistent.addParameter(Datareturn(ColData[12]));
        	persistent.addParameter(Datareturn(ColData[13]));
        	persistent.addParameter(Datareturn(ColData[14]));
        	persistent.addParameter(Datareturn(ColData[15]));
        	persistent.addParameter(Datareturn(ColData[16]));
        	persistent.addParameter(Datareturn(ColData[17]));
        	persistent.addParameter(Datareturn(ColData[18]));
        	persistent.addParameter(Datareturn(ColData[19]));
        	persistent.addParameter(Datareturn(ColData[20]));
        	persistent.addParameter(Datareturn(ColData[21]));
        	persistent.addParameter(Datareturn(ColData[22]));
        	persistent.addParameter(Datareturn(ColData[23]));
        	persistent.addParameter(Datareturn(ColData[24]));
        	persistent.addParameter(Datareturn(ColData[25]));
        	persistent.addParameter(Datareturn(ColData[26]));
        	persistent.addParameter(Datareturn(ColData[27]));
        	persistent.addParameter(Datareturn(ColData[28]));
        	persistent.addParameter(Datareturn(ColData[29]));
        	persistent.addParameter(Datareturn(ColData[30]));

        	
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> BID_PUR_CONDI_insert() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
	
	
	/**
     * 데이터 String NULL 일경우 공백처리
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public String Datareturn(String Data) throws Exception {
		if("NULL".equals(Data))Data ="";
		return Data;
    }
	
	/**
	 * 경영정보 발주의뢰 상태값 업데이트
	 * @return
	 */
	public void update_mrreq_po(Connection connection,String ebid_staus_code) throws Exception {
		StrBuffer sqlstr = new StrBuffer();
		Persistent pstmt = new PersistentImpl(connection);
        try{
        		sqlstr.append("	UPDATE ETECEPC.MR_REQ_PO							\n");
				sqlstr.append("	SET EBID_STATUS = ?									\n");
				sqlstr.append("	WHERE BUYREQNO = ?									\n");
		       	pstmt.setStatement(sqlstr.toString());
		       	pstmt.addParameter(ebid_staus_code);
				pstmt.addParameter(params.get("DEMANDED_ID"));
				pstmt.execute();
	        }catch (Exception e){
				WAFLogger.error(e);
				throw new Exception(e);
	        }
	}
	
	/**
	 * 발주의뢰 상태값 업데이트
	 * @return
	 */
	public boolean update_mrreq_po(Connection connection,String ebid_staus_code,String status_code) throws Exception {
		StrBuffer sqlstr = new StrBuffer();
		Persistent pstmt = new PersistentImpl(connection);
        try{
        		sqlstr.append("	UPDATE ETECEPC.MR_REQ_PO							\n");
				sqlstr.append("	SET EBID_STATUS = ?									\n");
				if("9".equals(status_code))
					sqlstr.append("	, REQ_STATUS = ?										\n");
				sqlstr.append("	WHERE BUYREQNO = ?									\n");
		       	pstmt.setStatement(sqlstr.toString());
		       	pstmt.addParameter(ebid_staus_code);
		       	if("9".equals(status_code))
		       		pstmt.addParameter("9");
				pstmt.addParameter(params.get("DEMANDED_ID"));
				pstmt.execute();
				return true;
	        }catch (Exception e){
				WAFLogger.error(e);
				throw new Exception(e);
	        }
	}

	/**
	 * 발주의뢰 결재 정보 조회
	 * @param DEMANDED_ID
	 * @return
	 */
	private ValueObject getSignInfo(String DEMANDED_ID) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject vo = new ValueObject();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("SELECT DEMANDED_ID, SIGN_CLASS_CD, DOC_ID, YEAR, SIGN_DECI_CLASS, APPROVAL_DATE, APPROVAL_SABUN, APPROVAL_USER                  \n");
        	sqlstr.append("FROM (                                                                                                                    \n");
        	sqlstr.append("          SELECT A.DEMANDED_ID, C.SIGN_CLASS_CD, C.DOC_ID,TO_CHAR(C.CRE_DATE,'YYYYMMDD') YEAR,  SIGN_DECI_CLASS, APPROVAL_DATE, APPROVAL_SABUN, APPROVAL_USER  \n");
        	sqlstr.append("          FROM PMM_DEMANDED_REQUEST A, ETECEPC.MR_REQ_PO B, SIGN001T C                                              \n");
        	sqlstr.append("          WHERE A.DEMANDED_ID = B.BUYREQNO                                                      \n");
        	sqlstr.append("          AND B.APPR_ID = C.SIGN_INTERFACE_ID                                                                             \n");
        	sqlstr.append("          AND  C.SIGN_CLASS_CD = 'ebid_mrD'                                                                                    \n");
        	sqlstr.append("          AND C.SIGN_DECI_CLASS IN ( '14', '24')                                                                          \n");
        	sqlstr.append("          UNION ALL                                                                                                       \n");
        	sqlstr.append("          SELECT  A.DEMANDED_ID,  SIGN_CLASS_CD, DOC_ID, TO_CHAR(B.CRE_DATE,'YYYYMMDD') YEAR, SIGN_DECI_CLASS, APPROVAL_DATE, APPROVAL_SABUN, APPROVAL_USER    \n");
        	sqlstr.append("          FROM PMM_DEMANDED_REQUEST A, SIGN001T B                                                                   \n");
        	sqlstr.append("          WHERE  A.DEMANDED_KIND IN ('C', 'P',  'O')                                                                            \n");
        	sqlstr.append("          AND  B.SIGN_CLASS_CD = 'prD'                                                                                    \n");
        	sqlstr.append("          AND B.SIGN_DECI_CLASS IN ( '14', '24')                                                                          \n");
        	sqlstr.append("          AND A.DEMANDED_ID =  REGEXP_REPLACE(B.KEY_COL1,'[^0-9]')                                                                                  \n");
        	sqlstr.append(") A                                                                                                                       \n");
        	sqlstr.append("WHERE 1=1                                                                                                                 \n");
        	sqlstr.append("AND A.DEMANDED_ID = ?                                                                                                     \n");
        	sqlstr.append("ORDER BY DEMANDED_ID                                                                                                      \n");

        	
        	search.setStatement(sqlstr.toString());
        	search.addParameter(DEMANDED_ID);
            rowSet = search.execute();
            if(rowSet.size()>0){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return vo;
	}
	
	
	public void Send(String openSabun) throws Exception {
		MessageSend messageSend = new MessageSend(storage);
		
     	RowSet SuppInfo = bid_supp_info(openSabun);
     	ValueObject email_info = email_info(params);
     	
 		StringBuffer mailtitle = new StringBuffer();
 		StringBuffer mailtitlein = new StringBuffer();
        StringBuffer mailContent = new StringBuffer();
     	
        mailtitle.append("[견적개찰] "+ email_info.get("DEPT_NM") + ", " +email_info.get("DEMANDED_TITLE")+" 개찰알림");
     	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.프로젝트 : " + email_info.get("DEPT_NM") + "<br>");
     	mailContent.append("2.입 찰 명 : " + email_info.get("BID_MASTER_NM") + " ( " + email_info.get("BID_NO") +" )<br>");
     	mailContent.append("3.실행예산 : " + email_info.get("CURRENCY_GB")+" " + email_info.get("ACT_AMT") + "<br>");
     	mailContent.append("4.구매구분 : " +email_info.get("DEMANDED_KIND_NM")+"<br>");
     	mailContent.append("5.담 당 자 : " +email_info.get("GRADE_NAME")+" "+ email_info.get("USER_NAME") +" (사내번호 : " + email_info.get("TEL_NO") + ")<br>");
     	mailContent.append("</font>");
     	
     	//개찰자에 따른 세팅한 담당자에게 메일 발송
     	for(int i=0;i < SuppInfo.size();i++){
     		Row row = SuppInfo.getRow(i);             	
            messageSend.sendEmail(row.get("CHARGE_EMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "Y");
     	}
     	
     	//2020.02.20 개찰시 구매담당자에게 메일 발송(개찰자와 구매담당자가 다른 경우) - 송영주 과장 요청
     	//if(!email_info.get("PUR_EMAIL").isEmpty() && !email_info.get("E_MAIL").equals(email_info.get("PUR_EMAIL"))){
     	//	messageSend.sendEmail(email_info.get("PUR_EMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "Y");
     	//}
	}
	
	public void Send_Tbe() throws Exception {
		MessageSend messageSend = new MessageSend(storage);
		
     	ValueObject email_info = email_info(params);
     	
     	StringBuffer mailtitle = new StringBuffer();
        StringBuffer mailtitlein = new StringBuffer();
        StringBuffer mailContent = new StringBuffer();
             	
        mailtitle.append("[입찰마감]"+ email_info.get("DEPT_NM") + ", " +email_info.get("DEMANDED_TITLE")+" 입찰마감 알림");
        mailtitlein.append("* 견적서 입찰마감 알림 메일입니다.");
        mailContent.append("다음의 입찰 건에 대해 입찰이 마감되었음을 알려 드립니다.<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.프로젝트 : " + email_info.get("DEPT_NM") + "<br>");
        mailContent.append("2.입 찰 명 : " + email_info.get("DEMANDED_TITLE") + " ( " + email_info.get("DEMANDED_NO") +" )<br>");
        mailContent.append("5.담 당 자 : " +email_info.get("GRADE_NAME")+" "+ email_info.get("USER_NAME") +" (사내번호 : " + email_info.get("TEL_NO") + ")<br>");
        mailContent.append("</font>");
        
        if(!email_info.get("TBE_EMAIL").isEmpty()){
        	messageSend.sendEmail(email_info.get("TBE_EMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "Y");
        }
	}
	
	/**
	 * 이메일 정보 
	 * @param Params
	 * @return
	 */
	public ValueObject email_info(ValueObject param) {

		Search search = new SearchImpl();
		 ValueObject valueobject = null;
		RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{ 
        	
        	
        	sqlstr.append("		SELECT B.E_MAIL                                                                         \n");
        	sqlstr.append("		     , B.USER_NAME  AS OPEN_EMPNM                                                     	\n");
        	sqlstr.append("		     , A.DEPT_NM                                                                       	\n");
        	sqlstr.append("		     , A.DEMANDED_TITLE                                                                	\n");
        	sqlstr.append("		     , A.DEMANDED_NO                                                                   	\n");
        	sqlstr.append("		     , F_GET_D_CURRENCY(A.ACT_AMT, F_GET_CURRENCY_DIGIT('KRW')) AS ACT_AMT              \n");
        	sqlstr.append("		     , 'KRW' AS CURRENCY_GB                                                            	\n");
        	sqlstr.append("		     ,  DECODE(A.DEMANDED_KIND , 'O', '외주' , '구매') AS DEMANDED_KIND_NM              	\n");
        	sqlstr.append("		     ,  C.USER_NAME                                                                     \n");
        	sqlstr.append("		     ,  C.GRADE_NAME                                                                    \n");
        	sqlstr.append("		     ,  C.TEL_NO                                                                        \n");
        	sqlstr.append("		     ,  E.E_MAIL TBE_EMAIL                                                              \n");
        	sqlstr.append("		     ,  C.E_MAIL PUR_EMAIL                                                              \n");
        	sqlstr.append("		     ,  A.BID_NO                                                                        \n");
        	sqlstr.append("		     ,  A.BID_MASTER_NM                                                                 \n");
        	sqlstr.append("		     ,  A.OPEN_OPINION                                                                  \n");
        	sqlstr.append("		FROM    BID_MASTER A                                                                    \n");
        	sqlstr.append("		   ,    PUB0120T  B                                                                    	\n");
        	sqlstr.append("		   ,    PUB0120T C                                                                     	\n");
        	sqlstr.append("		   ,    TBE_INFO D                                                                     	\n");
        	sqlstr.append("		   ,    PUB0120T E                                                                     	\n");
        	sqlstr.append("		WHERE A.OPEN_EMPID  = B.ACCT_ID                                                         \n");
        	sqlstr.append("		AND   A.PUREMPNO = C.ACCT_ID                                                            \n");
        	sqlstr.append("		AND   A.BID_NO = D.BID_NO(+)                                                            \n");
        	sqlstr.append("		AND   A.BID_DEG = D.BID_DEG(+)                                                          \n");
        	sqlstr.append("		AND   D.EVAL_EMPID = E.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("BID_NO"));
			search.addParameter(param.get("BID_DEG"));
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        return valueobject;
	}
	/**
	 * 견적 대상 업체 를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	public RowSet bid_supp_info(String openSabun) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{ 
        	
        	sqlstr.append("			SELECT CHG_CODE01, E_MAIL AS CHARGE_EMAIL   \n");
        	sqlstr.append("			FROM (                                      \n");
        	sqlstr.append("			SELECT CHG_CODE01                           \n");
        	sqlstr.append("			FROM EBID_COM911T                           \n");
        	sqlstr.append("			WHERE BASECD = 'PUR_OPEN_EMAIL'             \n");
        	sqlstr.append("			AND DETAILCD = ?                    		\n");
        	sqlstr.append("			UNION ALL                                   \n");
        	sqlstr.append("			SELECT  CHG_CODE02 AS CHG_CODE01            \n");
        	sqlstr.append("			FROM EBID_COM911T                           \n");
        	sqlstr.append("			WHERE BASECD = 'PUR_OPEN_EMAIL'             \n");
        	sqlstr.append("			AND DETAILCD = ?                  			\n");
        	sqlstr.append("			) A , PUB0120T B                            \n");
        	sqlstr.append("			WHERE A.CHG_CODE01 = B.USER_SABUN			\n");
        	
        	
			search.setStatement(sqlstr.toString());
			search.addParameter(openSabun);
			search.addParameter(openSabun);
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}

	  /**
     * 예상환율 저장
	 * @param event
	 * @param valueObject
	 * @return
	 */
	public boolean bidOpenSave() {
        try {
            conn.begin();

            String[] status = super.gets("sheet1.STATUS");
            for (int i = 0; i < status.length; i++) {
            	if (IBSheet7Utils.equalStatusUpdate(status[i])) {
					saveData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
            	}
			}

            return true;
        }
        catch(Exception e) {
        	storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
            WAFLogger.error(this.getClass().getName() + " --> bidOpenSave() : " + e.getMessage());
            WAFLogger.error(e);
            conn.setRollback();

            storage.setDetailMessage(e);
            return false;
        }
        finally {
        	
            conn.end();
        }
    }
	
	/**
     * BID_SUPP 예상환율 업데이트
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void saveData(Connection connection, ValueObject valueObject) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("  UPDATE BID_SUPP	         \n");
        	sqlstr.append("  SET EXCHANGE = ?	         \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(valueObject.get("EXCHANGE"));
            persistent.addParameter(storage.getUser().get("ACCT_ID"));
            persistent.addParameter(params.get("BID_NO"));
            persistent.addParameter(params.get("BID_DEG"));
            persistent.addParameter(valueObject.get("VENDOR_CD"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> saveData() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(persistent.getParameters());
            WAFLogger.error(e);
            throw e;
        }
    }
	
	public void Send_Report() throws Exception {
		MessageSend messageSend = new MessageSend(storage);

     	ValueObject email_info = email_info(params);
     	
 		StringBuffer mailtitle = new StringBuffer();
 		StringBuffer mailtitlein = new StringBuffer();
        StringBuffer mailContent = new StringBuffer();
     	
        mailtitle.append("[견적개찰] "+ email_info.get("DEPT_NM") + ", " +email_info.get("DEMANDED_TITLE")+" 개찰알림");
     	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. 프로젝트 : " + email_info.get("DEPT_NM") + "<br>");
     	mailContent.append("2. 입 찰 명 : " + email_info.get("BID_MASTER_NM") + " ( " + email_info.get("BID_NO") +" )<br>");
     	mailContent.append("3. 구매/외주구분 : " +email_info.get("DEMANDED_KIND_NM")+"<br>");
     	mailContent.append("4. 담 당 자 : " +email_info.get("GRADE_NAME")+" "+ email_info.get("USER_NAME") +" (사내번호 : " + email_info.get("TEL_NO") + ")<br>");
     	mailContent.append("</font>");
     	mailContent.append("<br>");
     	mailContent.append("<table width='700px;' cellspacing='0' cellpadding='0' bordercolordark='#FFFFFF' bordercolorlight='#AEAFAF' border='1' style='table-layout: fixed; font-size:12px;word-break:break-all;'>");
     	mailContent.append("<colgroup>");
     	mailContent.append("<col width='100px;'>");
     	mailContent.append("<col width='100px;'>");
     	mailContent.append("<col width='120px;'>");
     	mailContent.append("<col width='50px;'>");
     	mailContent.append("<col width='120px;'>");
     	mailContent.append("<col width='80px;'>");
     	mailContent.append("<col width='*;'>");
     	mailContent.append("</colgroup>");
     	mailContent.append("<tr>");
     	mailContent.append("<td align='center' bgcolor='#3366CC' style='color:#FFFFFF' rowspan='2'>실행예산<br>(KRW)</td>");
     	mailContent.append("<td align='center' bgcolor='#3366CC' style='color:#FFFFFF' rowspan='2'>업체명</td>");
     	mailContent.append("<td align='center' bgcolor='#3366CC' style='color:#FFFFFF' colspan='3'>견적금액</td>");
     	mailContent.append("<td align='center' bgcolor='#3366CC' style='color:#FFFFFF' rowspan='2'>예산대비<br>견적가율(%)</td>");
     	mailContent.append("<td align='center' bgcolor='#3366CC' style='color:#FFFFFF' rowspan='2'>Remark</td>");
     	mailContent.append("</tr>");
     	mailContent.append("<tr>");
     	mailContent.append("<td align='center' bgcolor='#3366CC' style='color:#FFFFFF'>원화환산금액(KRW)</td>");
     	mailContent.append("<td align='center' bgcolor='#3366CC' style='color:#FFFFFF'>통화</td>");
     	mailContent.append("<td align='center' bgcolor='#3366CC' style='color:#FFFFFF'>금액</td>");
     	mailContent.append("</tr>");
     	
     	//견적업체정보
     	RowSet bidReportSuppInfo = bid_report_supp_info(params);
     	for(int i=0;i < bidReportSuppInfo.size();i++){
     		Row row = bidReportSuppInfo.getRow(i);
     		if(i==0){
	     		mailContent.append("<tr>");
	     		mailContent.append("<td align='right' rowspan='" + bidReportSuppInfo.size() + "'>" + email_info.get("ACT_AMT") + "&nbsp;</td>");
	     		mailContent.append("<td align='left' bgcolor='#FFA500'>" + row.get("VENDOR_NAME") + "&nbsp;</td>");
	     		mailContent.append("<td align='right' bgcolor='#FFA500'>" + row.get("WON_ESTI_TOT_AMT") + "&nbsp;</td>");
	     		mailContent.append("<td align='center' bgcolor='#FFA500'>" + row.get("CURRENCY_GB") + "&nbsp;</td>");
	     		mailContent.append("<td align='right' bgcolor='#FFA500'>" + row.get("ESTI_TOT_AMT") + "&nbsp;</td>");
	     		mailContent.append("<td align='center' bgcolor='#FFA500'>" + row.get("ESTI_TOT_RATE") + "&nbsp;</td>");
	     		mailContent.append("<td align='right' bgcolor='#FFA500'>" + row.get("REMARK") + "&nbsp;</td>");
	     		mailContent.append("</tr>");
     		} else {
	     		mailContent.append("<tr>");
	     		mailContent.append("<td align='left'>" + row.get("VENDOR_NAME") + "&nbsp;</td>");
	     		mailContent.append("<td align='right'>" + row.get("WON_ESTI_TOT_AMT") + "&nbsp;</td>");
	     		mailContent.append("<td align='center'>" + row.get("CURRENCY_GB") + "&nbsp;</td>");
	     		mailContent.append("<td align='right'>" + row.get("ESTI_TOT_AMT") + "&nbsp;</td>");
	     		mailContent.append("<td align='center'>" + row.get("ESTI_TOT_RATE") + "&nbsp;</td>");
	     		mailContent.append("<td align='right'>" + row.get("REMARK") + "&nbsp;</td>");
	     		mailContent.append("</tr>");
     		}
     	}
     	
     	mailContent.append("</table>");
     	if(!email_info.get("OPEN_OPINION").isEmpty()){
	     	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	     	mailContent.append("<br>");
	     	mailContent.append("※ 특기사항 : " + email_info.get("OPEN_OPINION") + "<br>");
	     	mailContent.append("</font>");
     	}
     	
     	//보고자에게 메일 발송
     	RowSet bidReportEmpInfo = bid_report_emp_info(params);
     	for(int i=0;i < bidReportEmpInfo.size();i++){
     		Row row = bidReportEmpInfo.getRow(i);
     		
     		if(!row.get("E_MAIL").isEmpty()){
     			messageSend.sendEmail(row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "Y");
     		}
     	}
	}

	/**
	 * 견적 대상 업체 를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	public RowSet bid_report_supp_info(ValueObject param) {
		Search search = new SearchImpl();
		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.VENDOR_CD,									\n");
        	sqlstr.append("        A.VENDOR_NO,									\n");
        	sqlstr.append("        A.VENDOR_NAME,						        \n");
        	sqlstr.append("        DECODE(B.CURRENCY_GB,'KRW',1,A.EXCHANGE) AS EXCHANGE, -- 예상환율 \n");
        	sqlstr.append("        DECODE(B.CURRENCY_GB,'KRW',F_GET_D_CURRENCY(A.ESTI_TOT_AMT,F_GET_CURRENCY_DIGIT(B.CURRENCY_GB)),F_GET_D_CURRENCY((A.EXCHANGE * A.ESTI_TOT_AMT),F_GET_CURRENCY_DIGIT(B.CURRENCY_GB))) AS WON_ESTI_TOT_AMT, -- 원화환산금액 \n");
        	sqlstr.append("        B.CURRENCY_GB, -- 통화 \n");
        	sqlstr.append("        F_GET_D_CURRENCY(A.ESTI_TOT_AMT,F_GET_CURRENCY_DIGIT(B.CURRENCY_GB)) AS ESTI_TOT_AMT, -- 금액        		 \n");
        	sqlstr.append("        ROUND((DECODE(B.CURRENCY_GB,'KRW',A.ESTI_TOT_AMT,(A.EXCHANGE * A.ESTI_TOT_AMT))/(SELECT DECODE(ACT_AMT,0,1,NVL(ACT_AMT,1)) FROM BID_MASTER WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG) * 100),1) AS ESTI_TOT_RATE, -- 예산대비 견적가율 \n");
        	sqlstr.append("        CASE WHEN DECODE((SELECT SITEDESC_FLAG FROM BID_MASTER WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG),'Y',A.SITEPARTI_YN,'Y') = 'N' THEN '견적설명 미참여'	 \n");
        	sqlstr.append("             WHEN ESTI_TOT_AMT IS NULL AND RNNC_REASON IS NULL THEN '입찰 미참여'	\n");
        	sqlstr.append("       		WHEN RNNC_SIGNDATA IS NOT NULL THEN '입찰포기'						\n");
        	sqlstr.append("             ELSE '' \n");
        	sqlstr.append("        END AS REMARK \n");
        	sqlstr.append("   FROM BID_SUPP A, BID_PUR_CONDI 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.VENDOR_CD = B.VENDOR_CD(+)   \n");
        	sqlstr.append(" AND A.BID_NO = ?                   \n");
        	sqlstr.append(" AND A.BID_DEG = ?                  \n");
        	sqlstr.append(" ORDER BY (DECODE(B.CURRENCY_GB,'KRW',1,A.EXCHANGE) * A.ESTI_TOT_AMT) ASC NULLS LAST	 \n");

			search.setStatement(sqlstr.toString());
			search.addParameter(param.get("BID_NO"));
			search.addParameter(param.get("BID_DEG"));
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}
	
	/**
	 * 보고자 검색하여 리턴
	 * @param Params
	 * @return
	 * @throws Exception 
	 * @throws  
	 */
	public RowSet bid_report_emp_info(ValueObject param) throws  Exception {
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
        
    	StrBuffer sqlstr = new StrBuffer();
    	//실행예산 조회
    	long ACT_AMT = Long.parseLong(getActAmt());
    	//최저 견적금액 조회
    	long ESTI_TOT_AMT = Long.parseLong(getEstiTotAmt());
    	
        try{ 
        	sqlstr.append("		SELECT B.E_MAIL                                                                         \n");
        	sqlstr.append("		FROM    BID_OPEN_REPORT A                                                               \n");
        	sqlstr.append("		   ,    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");
        	sqlstr.append("		AND   A.OPEN_NO != '10'    																\n");
        	sqlstr.append("		UNION                                                                                   \n");
        	sqlstr.append("		SELECT B.E_MAIL                                                                         \n");
        	sqlstr.append("		FROM    BID_MASTER A                                                                    \n");
        	sqlstr.append("		   ,    PUB0120T  B                                                                    	\n");
        	sqlstr.append("		WHERE A.PUREMPNO  = B.ACCT_ID                                                           \n");
        	sqlstr.append("		AND   A.BID_NO = ?                                                                      \n");
        	sqlstr.append("		AND   A.BID_DEG = ?    																	\n");
        	if( ACT_AMT < ESTI_TOT_AMT){ //실행예산 초과(실행예산 < 최저견적금액)일 경우 플랜트/건축 본부장 발송
        		sqlstr.append("		UNION                                                                                   \n");
        		sqlstr.append("		SELECT B.E_MAIL                                                                         \n");
        		sqlstr.append("		FROM    BID_OPEN_REPORT A                                                               \n");
        		sqlstr.append("		   ,    PUB0120T  B                                                                    	\n");
        		sqlstr.append("		WHERE A.EMP_ID  = B.ACCT_ID                                                             \n");
        		sqlstr.append("		AND   A.BID_NO = '"+param.get("BID_NO")+"'                                                                      \n");
        		sqlstr.append("		AND   A.BID_DEG = '"+param.get("BID_DEG")+"'      																	\n");
        		sqlstr.append("		AND   A.OPEN_NO = '10'    																\n");
        	}
			search.setStatement(sqlstr.toString());
			search.addParameter(param.get("BID_NO"));
			search.addParameter(param.get("BID_DEG"));
			search.addParameter(param.get("BID_NO"));
			search.addParameter(param.get("BID_DEG"));
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}

	/**
     * 실행예산을 조회한다.
	 * @param rightID
	 * @return
	 */
	public String getActAmt() throws Exception {
        
        Search search = new SearchImpl();
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append(" SELECT ACT_AMT FROM BID_MASTER WHERE BID_NO = ? AND BID_DEG = ? ");
            search.setStatement(sqlstr.toString());
            search.addParameter(params.get("BID_NO"));
			search.addParameter(params.get("BID_DEG"));
            return search.execute().getRow(0).get("ACT_AMT");
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getActAmt() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
    }
	
	/**
	 * 최저 견적금액을 조회한다.
	 * @param rightID
	 * @return
	 */
	public String getEstiTotAmt() throws Exception {
		
		Search search = new SearchImpl();
		StrBuffer sqlstr = new StrBuffer();
		try {
			sqlstr.append(" SELECT MIN(ESTI_TOT_AMT) as ESTI_TOT_AMT FROM BID_SUPP WHERE BID_NO = ? AND BID_DEG = ? ");
			search.setStatement(sqlstr.toString());
			search.addParameter(params.get("BID_NO"));
			search.addParameter(params.get("BID_DEG"));
			return search.execute().getRow(0).get("ESTI_TOT_AMT");
		}
		catch(Exception e) {
			
			WAFLogger.error(this.getClass().getName() + " --> getEstiTotAmt() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return null;
		}
	}
}