/**
 * @(#)file    BidIngBiz.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 java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
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.code.CommCodeManager;
import kr.co.udapsoft.ebid.buyer.bid.outside.biz.ScrchInfoBiz;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import kr.co.udapsoft.ebid.buyer.sycs.user.biz.UserCommon;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.modular.web.ibsheet7.util.IBSheet7Utils;
import com.udapsoft.waf.common.util.StringUtils;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class BidModifyBiz extends Biz {

	public BidModifyBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
		
		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")));
		}else if(storage.compareEvent("GET_CORP_LIST")){
			 params.set("sheet1", getCorpList(params.get("DEMANDED_ID")));
	         storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("UPDATE")){
			boolean success = false;
			params.set("login_user", storage.getUser().get("ACCT_ID"));
	        success = saveProc(storage.getEvent(), params);
	        
	        if(success){
	        	ScrchInfoBiz ScrchInfo = new ScrchInfoBiz(storage);
	        	ScrchInfo.Send(params, "MOD_UPDATE");
	        }
	        
	        super.storage.setSuccess(success);
	        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 ('04','05','06')																							\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 ValueObject getOrderReqeustValueObject() {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
     //   String sqlstr = "";
        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("       F_GET_CODE_NAME('911',A.CURRENCY_GB) AS CURRENCY_GB_NM,                                        \n");
		       sqlstr.append("       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("       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') AS BID_FROM_DT,                                        \n");
		       sqlstr.append("       TO_CHAR(A.BID_FROM_DT,'HH24') AS BID_FROM_HH,                                        \n");
		       sqlstr.append("       TO_CHAR(A.BID_FROM_DT,'MI') AS BID_FROM_MI,                                        \n");
		       sqlstr.append("       TO_CHAR(A.BID_TO_DT,'YYYY-MM-DD') AS BID_TO_DT,                                        \n");
		       sqlstr.append("       TO_CHAR(A.BID_TO_DT,'HH24') AS BID_TO_HH,                                        \n");
		       sqlstr.append("       TO_CHAR(A.BID_TO_DT,'MI') AS BID_TO_MI,                                        \n");
		       sqlstr.append("       A.PO_EMPNM,                                        \n");
		       sqlstr.append("       A.OPEN_EMPNM,                                        \n");
		       sqlstr.append("       A.OPEN_EMPID,                                        \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.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.MODIFY_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("       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.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("       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("       A.PAY_DIV						\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");
			   sqlstr.append("  	  AND A.DEMANDED_KIND IN ('C','P','B')	       																									\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 ValueObject getOrderReqeustValueObject2(String DEMANDED_ID) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
     //   String sqlstr = "";
        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("       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("       A.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("       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') AS BID_FROM_DT,                                        \n");
		       sqlstr.append("       TO_CHAR(A.BID_FROM_DT,'HH24') AS BID_FROM_HH,                                        \n");
		       sqlstr.append("       TO_CHAR(A.BID_FROM_DT,'MI') AS BID_FROM_MI,                                        \n");
		       sqlstr.append("       TO_CHAR(A.BID_TO_DT,'YYYY-MM-DD') AS BID_TO_DT,                                        \n");
		       sqlstr.append("       TO_CHAR(A.BID_TO_DT,'HH24') AS BID_TO_HH,                                        \n");
		       sqlstr.append("       TO_CHAR(A.BID_TO_DT,'MI') AS BID_TO_MI,                                        \n");
		       sqlstr.append("       A.PO_EMPNM,                                        \n");
		       sqlstr.append("       A.OPEN_EMPNM,                                        \n");
		       sqlstr.append("       A.OPEN_EMPID,                                        \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("       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.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("       GET_OUT_ITEM_NM(A.OUT_ITEM_CD) AS OUT_ITEM_NM						\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");
			   sqlstr.append("  	  AND A.DEMANDED_KIND IN ('C','P','B')	       																									\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 RowSet getCorpList(String DEMANDED_ID) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        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("        A.DELIVERY_CONDITION,						\n");
        	sqlstr.append("        A.SITEPARTI_YN,									\n");
        	sqlstr.append("        NVL(A.SITE_ATTEND_YN,'NULL') AS SITE_ATTEND_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("        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("        A.ESTI_TOT_AMT,								\n");
        	sqlstr.append("        ROUND((A.ESTI_TOT_AMT/(SELECT ACT_AMT FROM  BID_MASTER WHERE BID_NO = A.BID_NO AND BID_DEG = A.BID_DEG) * 100),1) AS ESTI_TOT_RATE,								\n");
        	sqlstr.append("        A.WON_ESTI_TOT_AMT,						\n");
        	sqlstr.append("        A.SUCC_YN,										\n");
        	sqlstr.append("        A.FIB_REASON,									\n");
        	sqlstr.append("        A.DISQ_REASON,									\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("   FROM BID_SUPP A										\n");
        	sqlstr.append(" WHERE BID_NO = '"+params.get("BID_NO")+"'	\n");
        	sqlstr.append(" AND BID_DEG = '"+params.get("BID_DEG")+"'	\n");
        	sqlstr.append(" ORDER BY A.ESTI_TOT_AMT ASC					\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 event
	 * @param valueObject
	 * @return
	 */
	public boolean saveProc(String event, ValueObject valueObject) {
        try {
            conn.begin();

            if( event.equals("UPDATE") ) {
            	//데이터 먼저 저장
        		params.get("BID_NO");
        		bid_masterupdate(conn.getConnection(), params);
                if("Y".equals(params.get("TBE_FLAG"))){
                	tbeinfosave(conn.getConnection(), params);
                }
        	}
            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();
        }
    }
	
	/**
     * 사용자 수정
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void tbeinfosave(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("MERGE INTO TBE_INFO TI USING (									\n");
        	sqlstr.append("		        SELECT ? AS BID_NO										\n");
        	sqlstr.append("                ,? AS EVAL_EMPNM										\n");
        	sqlstr.append("                ,? AS BID_DEG												\n");
        	sqlstr.append("            	,TO_DATE(?,'YYYYMMDDHH24MISS') AS SUPP_REQ_DY										\n");
        	sqlstr.append("            	,? AS STATUS_CD											\n");
        	sqlstr.append("            	,? AS EVAL_EMPID										\n");
        	sqlstr.append("             	,? AS EVAL_TEL											\n");
        	sqlstr.append("            	,? AS EVAL_EMAIL										\n");
        	sqlstr.append("		        FROM DUAL													\n");
        	sqlstr.append("		    ) T ON (T.BID_NO = TI.BID_NO AND T.BID_DEG = TI.BID_DEG)	\n");
        	sqlstr.append("		WHEN MATCHED THEN													\n");
        	sqlstr.append("		    UPDATE SET TI.EVAL_EMPNM = T.EVAL_EMPNM								\n");
        	sqlstr.append("            ,TI.SUPP_REQ_DY = T.SUPP_REQ_DY							\n");
        	sqlstr.append("            ,TI.EVAL_EMPID = T.EVAL_EMPID								\n");
        	sqlstr.append("            ,TI.EVAL_TEL = T.EVAL_TEL										\n");
        	sqlstr.append("            ,TI.EVAL_EMAIL = T.EVAL_EMAIL									\n");
        	sqlstr.append("		WHEN NOT MATCHED THEN											\n");
        	sqlstr.append("			INSERT (TI.BID_NO,TI.BID_DEG,TI.EVAL_EMPNM,TI.SUPP_REQ_DY,TI.STATUS_CD,TI.EVAL_EMPID,TI.EVAL_TEL,TI.EVAL_EMAIL)									\n");
        	sqlstr.append("			VALUES (T.BID_NO,T.BID_DEG,T.EVAL_EMPNM,T.SUPP_REQ_DY,T.STATUS_CD,T.EVAL_EMPID,T.EVAL_TEL,T.EVAL_EMAIL)											\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("BID_NO"));
            persistent.addParameter(valueObject.get("EVAL_EMPNM"));
            persistent.addParameter(valueObject.get("BID_DEG"));
            persistent.addParameter(StringUtils.replace(valueObject.get("SUPP_REQ_DY"),"-",""));
            persistent.addParameter("01");
            persistent.addParameter(valueObject.get("EVAL_EMPID"));
            persistent.addParameter(valueObject.get("EVAL_TEL"));
            persistent.addParameter(valueObject.get("EVAL_EMAIL"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
	
	/**
     * bid_master 업데이트
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void bid_masterupdate(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
			sqlstr.append(" UPDATE BID_MASTER 											\n");
			sqlstr.append("      SET BID_FROM_DT=TO_DATE(?||?||?,'YYYYMMDDHH24MI')		--									\n");
			sqlstr.append("      , BID_TO_DT=TO_DATE(?||?||?,'YYYYMMDDHH24MI')											\n");
			sqlstr.append("      , PUREMPNO=?											\n");
			sqlstr.append("      , PO_EMPNM=?											\n");
			sqlstr.append("      , OPEN_EMPNM=?											\n");
			sqlstr.append("      , OPEN_EMPID=?											\n");
			sqlstr.append("      , TBE_FLAG=?											\n");
			sqlstr.append("      , CURRENCY_GB=?											\n");
			sqlstr.append("      , CURRENCY_GB_YN=?											\n");
			sqlstr.append("      , DELI_FROM_DY=?											\n");
			sqlstr.append("      , DELI_TO_DY=?											\n");
			sqlstr.append("      , CONTFUL_RATE=?											\n");
			sqlstr.append("      , CONTFUL_CONDI=?											\n");
			sqlstr.append("      , WARR_RATE=?											\n");
			sqlstr.append("      , WARR_CONDI=?											\n");
			sqlstr.append("      , INNER_REMARKS=?											\n");
			sqlstr.append("      , OUTER_REMARKS=?											\n");
			sqlstr.append("      , BID_APP_DATE=?											\n");
			sqlstr.append("      , BID_APP_NO=?											\n");
			sqlstr.append("      , STATUS_CD=?											\n");
			sqlstr.append("      , ACT_RATE=?											\n");
	        sqlstr.append("       , PBC_DEFECT_CONDI=?							\n");
	        sqlstr.append("       , CBC_DERECT_TERM_CD=?						\n");
	        sqlstr.append("       , CBC_DEFECT_DAY=?								\n");
	        sqlstr.append("       , PBC_FRSTGUAR_RATE=?							\n");
	        sqlstr.append("       , PBC_FRSTGUAR_CONDI=?						\n");
	        sqlstr.append("       , PBC_REST_RATE=?								\n");
	        sqlstr.append("       , PBC_REST_CONDI=?								\n");
	        sqlstr.append("       , CPC_MID_RATE=?									\n");
	        sqlstr.append("       , PBC_MID_CONDI=?								\n");
	        sqlstr.append("       , CBC_FIRST_RATE=?								\n");
	        sqlstr.append("       , PBC_FIRST_CONDI=?								\n");
	        sqlstr.append("       , CBC_DAMAGE_RATE=?							\n");
	        sqlstr.append("       , CBC_DAMAGE_MAXRATE=?						\n");
	        sqlstr.append("       , PBC_OPTION_COMMENT=?						\n");
	        sqlstr.append("       , PBC_OPTION_YN=?								\n");
	        sqlstr.append("       , PBC_FEE_YN=?										\n");
	        sqlstr.append("       , DELIVERY_CONDITION=?						\n");
	        sqlstr.append("       , DELIVERY_CONDITION_YN=?					\n");
	        sqlstr.append("       , PBC_LANDTRAN_YN=?							\n");
	        sqlstr.append("       , PBC_PACK_YN=?									\n");
	        sqlstr.append("       , PBC_DOC_YN=?									\n");
	        sqlstr.append("       , PBC_TEST_YN=?									\n");
	        sqlstr.append("       , PBC_SPETOOL_YN=?								\n");
	        sqlstr.append("       , PBC_COMMISS_YN=?								\n");
	        sqlstr.append("       , PBC_INSTALL_YN=?								\n");
	        sqlstr.append("       , PBC_CBE_TYPE_CD=?							\n");
	        sqlstr.append("       , DIV_PO_YN=?										\n");
	        sqlstr.append("       , MODIFY_REASON=?								\n");
			sqlstr.append("      , UPD_BY=?											\n");
			sqlstr.append("      , UPD_DATE=SYSDATE 											\n");
			sqlstr.append("  WHERE BID_NO = ?											\n");
			sqlstr.append("  AND BID_DEG = ?											\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(StringUtils.replace(valueObject.get("BID_FROM_DT"),"-",""));
            persistent.addParameter(valueObject.get("BID_FROM_HH"));
            persistent.addParameter(valueObject.get("BID_FROM_MI"));
            persistent.addParameter(StringUtils.replace(valueObject.get("BID_TO_DT"),"-",""));
            persistent.addParameter(valueObject.get("BID_TO_HH"));
            persistent.addParameter(valueObject.get("BID_TO_MI"));
            persistent.addParameter(valueObject.get("PUREMPNO"));
            persistent.addParameter(valueObject.get("PO_EMPNM"));
            persistent.addParameter(valueObject.get("OPEN_EMPNM"));
            persistent.addParameter(valueObject.get("OPEN_EMPID"));
            persistent.addParameter(valueObject.get("TBE_FLAG"));
            persistent.addParameter(valueObject.get("CURRENCY_GB"));
            persistent.addParameter(valueObject.get("CURRENCY_GB_YN"));
            persistent.addParameter(StringUtils.replace(valueObject.get("DELI_FROM_DY"),"-",""));
            persistent.addParameter(StringUtils.replace(valueObject.get("DELI_TO_DY"),"-",""));
            persistent.addParameter(valueObject.get("CONTFUL_RATE"));
            persistent.addParameter(valueObject.get("CONTFUL_CONDI"));
            persistent.addParameter(valueObject.get("WARR_RATE"));
            persistent.addParameter(valueObject.get("WARR_CONDI"));
            persistent.addParameter(valueObject.get("INNER_REMARKS"));
            persistent.addParameter(valueObject.get("OUTER_REMARKS"));
            persistent.addParameter(valueObject.get("BID_APP_DATE"));
            persistent.addParameter(valueObject.get("BID_APP_NO"));
            persistent.addParameter(valueObject.get("STATUS_CD"));
            persistent.addParameter(valueObject.get("ACT_RATE"));
	        persistent.addParameter(valueObject.get("PBC_DEFECT_CONDI"));
	        persistent.addParameter(valueObject.get("CBC_DERECT_TERM_CD"));
	        persistent.addParameter(valueObject.get("CBC_DEFECT_DAY"));
	        persistent.addParameter(valueObject.get("PBC_FRSTGUAR_RATE"));
	        persistent.addParameter(valueObject.get("PBC_FRSTGUAR_CONDI"));
	        persistent.addParameter(valueObject.get("PBC_REST_RATE"));
	        persistent.addParameter(valueObject.get("PBC_REST_CONDI"));
	        persistent.addParameter(valueObject.get("CPC_MID_RATE"));
	        persistent.addParameter(valueObject.get("PBC_MID_CONDI"));
	        persistent.addParameter(valueObject.get("CBC_FIRST_RATE"));
	        persistent.addParameter(valueObject.get("PBC_FIRST_CONDI"));
	        persistent.addParameter(valueObject.get("CBC_DAMAGE_RATE"));
	        persistent.addParameter(valueObject.get("CBC_DAMAGE_MAXRATE"));
	        persistent.addParameter(valueObject.get("PBC_OPTION_COMMENT"));
	        persistent.addParameter(valueObject.get("PBC_OPTION_YN"));
	        persistent.addParameter(valueObject.get("PBC_FEE_YN"));
	        persistent.addParameter(valueObject.get("DELIVERY_CONDITION"));
	        persistent.addParameter(valueObject.get("DELIVERY_CONDITION_YN"));
	        persistent.addParameter(valueObject.get("PBC_LANDTRAN_YN"));
	        persistent.addParameter(valueObject.get("PBC_PACK_YN"));
	        persistent.addParameter(valueObject.get("PBC_DOC_YN"));
	        persistent.addParameter(valueObject.get("PBC_TEST_YN"));
	        persistent.addParameter(valueObject.get("PBC_SPETOOL_YN"));
	        persistent.addParameter(valueObject.get("PBC_COMMISS_YN"));
	        persistent.addParameter(valueObject.get("PBC_INSTALL_YN"));
	        persistent.addParameter(valueObject.get("PBC_CBE_TYPE_CD"));
	        persistent.addParameter(valueObject.get("DIV_PO_YN"));
	        persistent.addParameter(valueObject.get("MODIFY_REASON"));
            persistent.addParameter(valueObject.get("login_user"));
            persistent.addParameter(valueObject.get("BID_NO"));
            persistent.addParameter(valueObject.get("BID_DEG"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> bid_masterupdate() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
}