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

import java.sql.Connection;

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.ebid.buyer.common.file.FileManager;
import oracle.toplink.internal.ox.StrBuffer;

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

   
public class OrderRequestTypeBBiz extends Biz {
	FileManager fileManager = new FileManager();
	public OrderRequestTypeBBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
		boolean success = false;
		if(storage.compareEvent("SHEET_SELECT")){
			params.set("sheet1", getBoardList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("FORM_SELECT")){
	           String DEMANDED_ID = params.get("DEMANDED_ID");
	            user = getOrderReqeustValueObject(DEMANDED_ID);
	            storage.addValueObject("Order", user);
	            storage.addValueObject("DEMANDED_INNER", getfilelist("DEMANDED_INNER",params.get("DEMANDED_ID"),"DEMANDED_INNER"));
		        storage.addValueObject("DEMANDED_OUTER", getfilelist("DEMANDED_OUTER",params.get("DEMANDED_ID"),"DEMANDED_OUTER"));
		        storage.addValueObject("SIGN_INFO", getSignInfo(DEMANDED_ID));
		        
		}else if(storage.compareEvent("FROM_RECEIPT_UPDATE")){//접수
				success = setReceipt_insert();
				BidCommonBiz BidCommonBiz = new BidCommonBiz(storage);
				BidCommonBiz.SP_REG_PSR_STATUS("11",params.get("DEMANDED_ID"),storage.getUser().get("acct_id"));
				BidCommonBiz.SP_REG_PSR_STATUS("22",params.get("DEMANDED_ID"),storage.getUser().get("acct_id"),params.get("DEMANDED_DATE"));
				storage.setSuccess(success);
				if(success)
					storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("FROM_RETURN_UPDATE")){//반려
			try{
				conn.begin(); 	
				setReceipt(conn.getConnection());
				if("B".equals(params.get("DEMANDED_KIND"))) {
					update_mrreq_po(conn.getConnection(),"90","9");//반려
				}else {
					//update_pmm_demanded_request(conn.getConnection(),"90");//반려
				}
			}catch (Exception e){
				storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
				 super.conn.setRollback();
			}
			conn.end();
		}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("GET_ITEM_LIST")){//발주의뢰 내역
			params.set("sheet2", getItemList(params.get("DEMANDED_ID")));
            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();
        StrBuffer sqlstr = new StrBuffer();
        try{
			sqlstr.append(" SELECT NVL(B.ONBID_YN,'Y') AS ONBID_YN																									\n");
			sqlstr.append(" ,A.DEMANDED_ID	       																																\n");
			sqlstr.append(" ,A.DEMANDED_NO																				                                           		    \n");
			sqlstr.append(" ,A.DEPT_CD       																																		\n");
			sqlstr.append(" ,(SELECT DEPT_NM FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = A.DEPT_CD) AS DEPT_NM	       							\n");
			sqlstr.append(" ,A.DEMANDED_TITLE	       																														\n");
			sqlstr.append(" ,B.REQ_DEPT_NM	       																																\n");
			sqlstr.append(" ,B.REQ_DEPT_CD	       																																\n");
			sqlstr.append(" ,A.EMP_NO	       																																		\n");
			sqlstr.append(" ,C.DEPART_DEPT_NM AS BUSEO_NM						\n");
			sqlstr.append(" ,C.USER_NAME AS EMP_NM		\n");
			sqlstr.append(" ,TO_CHAR(TO_DATE(A.DEMANDED_DATE,'YYYYMMDD'),'YYYY-MM-DD') AS DEMANDED_DATE									\n");
			sqlstr.append(" ,TO_CHAR(A.REQUEST_DATE,'YYYY-MM-DD') AS REQUEST_DATE																		\n");
			sqlstr.append(" ,A.RECEIPT_NO	       																																\n");
			sqlstr.append(" ,GET_USER_NM(A.RECEIPT_NO) AS RECEIPT_NM																							\n");  
			sqlstr.append(" ,A.DEMANDED_STATUS	       																														\n");
			sqlstr.append(" ,A.PR_TYPE	       																														\n");
			sqlstr.append("   FROM PMM_DEMANDED_REQUEST A , (SELECT ONBID_YN,REQ_DEPT_NM,REQ_DEPT_CD,DEMANDED_ID FROM BID_MASTER GROUP BY ONBID_YN,REQ_DEPT_NM,REQ_DEPT_CD,DEMANDED_ID) B  , PUB0120T C																\n");
			sqlstr.append("  WHERE A.DEMANDED_ID = B.DEMANDED_ID(+)	       																						\n");
			sqlstr.append("  AND A.DEMANDED_KIND IN ('C','P','B')	       																									\n");
			sqlstr.append("  AND A.PR_TYPE IS NOT NULL	       																														\n");
			sqlstr.append("  AND A.EMP_NO = C.USER_SABUN(+) 																											\n");
			sqlstr.append("  AND A.DEMANDED_STATUS > '2'	       																							\n");
			if(!param.get("P_DEMANDED_STATUS").isEmpty()){
				sqlstr.append("  AND A.DEMANDED_STATUS = '"+param.get("P_DEMANDED_STATUS")+"'	       																							\n");
			}
			
			if(!param.get("P_DEMANDED_TITLE").isEmpty()){
				sqlstr.append("  AND UPPER(A.DEMANDED_TITLE) LIKE '%'||'"+param.get("P_DEMANDED_TITLE").toUpperCase()+"'||'%'	       																				\n");
			}
			if(!param.get("P_DEMANDED_KIND").isEmpty()){
				//sqlstr.append("  AND A.DEMANDED_KIND = '"+param.get("P_DEMANDED_KIND")+"'	       																							\n");
			}
			if(!param.get("P_PR_TYPE").isEmpty()){
				sqlstr.append("  AND A.PR_TYPE = '"+param.get("P_PR_TYPE")+"'	       																							\n");
			}
        	// 현장
        	if(!"".equals(params.get("P_DEPT_CD"))) {
        		sqlstr.append(" AND A.DEPT_CD = ?																												 \n");
    			search.addParameter(params.get("P_DEPT_CD"));
        	}
			sqlstr.append("  ORDER BY DEMANDED_DATE DESC	, DEMANDED_ID DESC       																				\n");
			search.setStatement(sqlstr.toString());
			WAFLogger.debug(sqlstr.toString());
			WAFLogger.debug(search.getParameters());
            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(String DEMANDED_ID) {

		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_STATUS,                                          \n");
		       sqlstr.append("       (SELECT DECODE(DETAILNM,'결재완료','접수대상',DETAILNM) FROM EBID_COM911T WHERE BASECD = 'DEMANDED_STATUS' AND A.DEMANDED_STATUS = DETAILCD) AS DEMANDED_STATUS_NM,                                          \n");
		       sqlstr.append("       (SELECT DEPART_DEPT_NM FROM PUB0120T WHERE USER_SABUN = A.EMP_NO) AS BUSEO_NM,                                          \n");
		       sqlstr.append("       A.DEMANDED_KIND,                                          \n");
		       sqlstr.append("       A.CURRENCY_GB,                                          \n");
		       sqlstr.append("       A.DEPT_CD,                                          \n");
		       sqlstr.append("        (SELECT DEPT_NM FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = A.DEPT_CD) AS DEPT_NM,                                         \n");
		       sqlstr.append("       A.EMP_NO,                                          \n");
		       sqlstr.append("       (SELECT USER_NAME FROM PUB0120T WHERE USER_SABUN = A.EMP_NO) AS EMP_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.APP_DATE,                                          \n");
		       sqlstr.append("       A.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 HH24:MI') AS RECEIPT_DATE,                                          \n");
		       sqlstr.append("       DECODE(A.SITEDESC_FLAG,'Y','예','N','아니오','') AS SITEDESC_FLAG,                                          \n");
		       sqlstr.append("       A.SITEDESCCHRGPRSNID,                                          \n");
		       sqlstr.append("       GET_USER_SABUN_NM(SITEDESCCHRGPRSNID) AS SITEDESC_CRG_NM,                                          \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("       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("       TO_CHAR(A.TBE_DATE,'YYYY-MM-DD') AS TBE_DATE,                                          \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.DOC_ETC_IN,                                          \n");
		       sqlstr.append("       A.DOC_ETC_OUT,                                          \n");
		       sqlstr.append("       A.DOC_MR_IN,                                          \n");
		       sqlstr.append("       A.DOC_MR_OUT,                                          \n");
		       sqlstr.append("       A.DOC_FIELD_MANUAL_IN,                                          \n");
		       sqlstr.append("       A.DOC_FIELD_MANUAL_OUT,                                          \n");
		       sqlstr.append("       NVL(A.ONBID_YN,'Y') AS ONBID_YN,                                   \n");
		       sqlstr.append("       A.RTN_REASON,                                          \n");
		       sqlstr.append("       TO_CHAR(TO_DATE(A.RTN_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') AS RTN_DATE,                                          \n");
		       sqlstr.append("       A.RTN_EMPNO,                                          \n");
		       sqlstr.append("       A.RTN_EMPNM,                                          \n");
		       sqlstr.append("       A.CONT_AMT,                                          \n");
		       sqlstr.append("       A.ACT_AMT,                                          \n");
		       sqlstr.append("       A.ACT_RATE,                                          \n");
		       sqlstr.append("       TO_CHAR(TO_DATE(A.DELI_START_DT,'YYYYMMDD'),'YYYY-MM-DD') AS DELI_START_DT,                                  \n");             
		       sqlstr.append("       TO_CHAR(TO_DATE(A.DELI_END_DT,'YYYYMMDD'),'YYYY-MM-DD') AS DELI_END_DT,                                         \n");     
		       sqlstr.append("       A.DELI_END_PLACE,                                          \n");
		       sqlstr.append("       A.RECEIPT_NO,											\n");
		       sqlstr.append("       TO_CHAR(TO_DATE(A.REQUEST_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') AS REQUEST_DATE,                                          \n");
		       sqlstr.append("       GET_USER_NM(RECEIPT_NO) AS RECEIPT_NM,											\n");
		       sqlstr.append("       GET_OUT_ITEM_NM(A.OUT_ITEM_CD) AS OUT_ITEM_NM,						\n");
		       sqlstr.append(" 		F_GET_CODE_NAME('PR_TYPE', A.PR_TYPE) PR_TYPE_NAME	       																														\n");
		       sqlstr.append("       FROM PMM_DEMANDED_REQUEST A				\n");
		       sqlstr.append("       WHERE A.DEMANDED_ID = '"+DEMANDED_ID+"'							\n");
		       sqlstr.append(" 	  AND A.DEMANDED_KIND IN ('C','P','B')	       																									\n"); 

		       search.setStatement(sqlstr.toString());
		       WAFLogger.debug(sqlstr.toString());
		       WAFLogger.debug(search.getParameters());
            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 DEMANDED_ID 																																			\n");
			sqlstr.append(" , VENDOR_ID       																																				\n");
			sqlstr.append(" , COPR.VENDOR_NAME																																			\n");
			sqlstr.append(" , NVL(COPR.CHARGE_NAME,B.USER_NAME) AS CHARGE_NAME	       																				\n");
			sqlstr.append(" , NVL(COPR.CHARGE_TEL,DECODE(B.HP1,'','',B.HP1||'-'||B.HP2||'-'||B.HP3)) AS CHARGE_TEL       										\n");
			sqlstr.append(" , NVL(COPR.CHARGE_EMAIL,B.EMAIL) AS CHARGE_EMAIL       																						\n");
			sqlstr.append(" , RECOMMEND_REASON      																																	\n");
			sqlstr.append(" , NVL(COPR.PRE_NM,A.REP_NAME) AS PRE_NM      																									\n");
			sqlstr.append(" ,DECODE(A.CORP_ID,'','N','Y') AS INFO_YN 																												\n");
			sqlstr.append(" FROM PMM_COPR COPR, SUPP_INFO A, SUPP_CHARGE B																								\n");
			sqlstr.append(" WHERE COPR.DEMANDED_ID = '"+DEMANDED_ID+"'																										\n");
			sqlstr.append(" AND REGEXP_REPLACE(COPR.VENDOR_ID,'[^0-9]') = A.CORP_REG_NO(+)																											\n");
			sqlstr.append(" AND A.JOB_DIV(+) = 'P'																																		\n");
			sqlstr.append(" AND A.STATUS(+) IN ('02','03')																																\n");
			sqlstr.append(" AND A.CORP_ID = B.CORP_ID(+)																																\n");
			sqlstr.append(" AND B.DEFAULT_YN(+) = 'Y'																																	\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 getItemList(String DEMANDED_ID) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
			sqlstr.append(" SELECT A.MATERIAL_CD																						\n");
			sqlstr.append(" ,B.MATERIAL_NAME       																						\n");
			sqlstr.append(" ,B.MATERIAL_SPEC      																						\n");
			sqlstr.append(" ,F_GET_UNIT_KIND_NAME(A.MATERIAL_CD) AS UNIT_KIND											\n");
			sqlstr.append(" ,A.DEMANDED_QTY       																						\n");
			sqlstr.append(" ,A.REQUEST_DATE       																						\n");
			sqlstr.append(" ,A.CONT_PRI      																								\n");
			sqlstr.append(" ,A.CONT_PRI * A.DEMANDED_QTY AS CONT_AMT      												\n");
			sqlstr.append(" ,A.DEMANDED_PRI      																							\n");
			sqlstr.append(" ,A.DEMANDED_PRI * A.DEMANDED_QTY AS ACT_AMT     											\n");
			sqlstr.append(" FROM PMM_DEMANDED_ITEM A , PMM_COMM_MATERIAL_MASTER B  							\n");
			sqlstr.append(" WHERE A.MATERIAL_CD = B.MATERIAL_CD																\n");
			sqlstr.append(" AND A.DEMANDED_ID = '"+DEMANDED_ID+"'																\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;
	}
	
	/**
	 * 발주의뢰 상태값 업데이트
	 * @return
	 */
	private boolean setReceipt(Connection connection) throws Exception {
		StrBuffer sqlstr = new StrBuffer();
		Persistent pstmt = new PersistentImpl(connection);
		
        try{
        		sqlstr.append("	UPDATE PMM_DEMANDED_REQUEST					\n");
				sqlstr.append("	SET DEMANDED_STATUS = ?							\n");
				if("5".equals(params.get("DEMANDED_STATUS"))){
					sqlstr.append("	,RTN_REASON = ?										\n");
					sqlstr.append("	,RTN_DATE = TO_CHAR(SYSDATE,'YYYYMMDD')		\n");
					sqlstr.append("	,RTN_EMPNO = ?										\n");
					sqlstr.append("	,RTN_EMPNM = GET_USER_NM(?)				\n");
				}
				sqlstr.append("	,UPD_BY = ?												\n");
				sqlstr.append("	,UPD_DATE = SYSDATE									\n");
				sqlstr.append("	WHERE DEMANDED_ID = ?								\n");
		       	pstmt.setStatement(sqlstr.toString());
				pstmt.addParameter(params.get("DEMANDED_STATUS"));
				if("5".equals(params.get("DEMANDED_STATUS"))){
					pstmt.addParameter(params.get("RTN_REASON"));
					pstmt.addParameter(storage.getUser().get("acct_id"));
					pstmt.addParameter(storage.getUser().get("acct_id"));
				}
				pstmt.addParameter(storage.getUser().get("acct_id"));
				pstmt.addParameter(params.get("DEMANDED_ID"));
				pstmt.execute();
				return true;
	        }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);
	        }
	}
	
	/**
	 * 발주의뢰 상태값 업데이트
	 * 2018.11.20
	 * 이인오
	 * 사용안함
	 * @return
	 */
	private boolean update_pmm_demanded_request(Connection connection,String status_code) throws Exception {
		StrBuffer sqlstr = new StrBuffer();
		Persistent pstmt = new PersistentImpl(connection);
        try{
	    		sqlstr.append("	UPDATE PMM_DEMANDED_REQUEST!CCSIF					\n");
				sqlstr.append("	SET EBID_STATUS_CD = ?											\n");
				if(status_code == "90"){
					sqlstr.append("	,RTN_REASON = ?														\n");
					sqlstr.append("	,RTN_DATE = TO_CHAR(SYSDATE,'YYYYMMDD')				\n");
					sqlstr.append("	,RTN_EMPNO = ?														\n");
					sqlstr.append("	,RTN_EMPNM = ?														\n");
				}else{
					sqlstr.append("	     ,DEMANDED_STATUS = '4'								\n");
				}
				sqlstr.append("	WHERE DEMANDED_ID = ?											\n");
		       	pstmt.setStatement(sqlstr.toString());
				pstmt.addParameter(status_code);
				if(status_code == "90"){
					pstmt.addParameter(params.get("RTN_REASON"));
					pstmt.addParameter(storage.getUser().get("acct_id"));
					pstmt.addParameter(storage.getUser().get("USER_NAME"));
				}
				pstmt.addParameter(params.get("DEMANDED_ID"));
				pstmt.execute();
				return true;
	        }catch (Exception e){
				WAFLogger.error(e);
				throw new Exception(e);
	        }
	}
	
	/**
	 * 발주의뢰 접수
	 * @return
	 */
	private boolean setReceipt_insert() throws Exception{
        try{
        	conn.begin(); 
        	String BID_NO = BIDNO();
        	BID_NO = BidNodeg(BID_NO);
        	params.set("BID_NO", BID_NO);
        	//마스터
        	BID_MASTER_insert(conn.getConnection());
        	//TBE 정보 저장
        	TEB_INFO_insert(conn.getConnection());
        	//고객사
           	BID_SUPP_insert(conn.getConnection());
        	//아이템 외주는 아이템 없음.
        	BID_ITEM_insert(conn.getConnection());
            //접수일 업데이트
            PMM_DEMANDED_REQUEST_update(conn.getConnection());
            //파일 복사
            fileManager.copyFileList(conn.getConnection(), "DEMANDED_INNER", params.get("DEMANDED_ID"), "BID_IN_1", params.get("DEMANDED_ID"));
            fileManager.copyFileList(conn.getConnection(), "DEMANDED_OUTER", params.get("DEMANDED_ID"), "BID_OUT_1", params.get("DEMANDED_ID"));
 
            /**
        	 *  접수 완료 후 이메일 , SMS 발송 
        	 */
            //상태 업데이트
        	setReceipt(conn.getConnection());
        	
        	if("B".equals(params.get("DEMANDED_KIND"))){
        		update_mrreq_po(conn.getConnection(),"02");//접수
        	}else{
        		//update_pmm_demanded_request(conn.getConnection(),"02");//접수
        	}
			return true;
        }catch (Exception e){
            WAFLogger.error(this.getClass().getName() + " --> setReceipt_insert() : " + e.getMessage());
            storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
            WAFLogger.error(e);
             conn.setRollback();
			return false;
        }finally{
        	conn.end();
        }
        
	}
	
	/**
	 * 발주의뢰 접수
	 * @return
	 */
	private void BID_MASTER_insert(Connection connection) throws Exception{
		StrBuffer sqlstr = new StrBuffer();
		Persistent pstmt = new PersistentImpl(connection);
        try{
        	super.conn.begin(); 
        	sqlstr.append("INSERT											\n");				
        	sqlstr.append("  INTO BID_MASTER 							\n");
        	sqlstr.append("       ( 											\n");
        	sqlstr.append("           DEMANDED_ID						\n");
        	sqlstr.append("         , DEMANDED_NO						\n");
        	sqlstr.append("         , DEMANDED_DATE					\n");
        	sqlstr.append("         , DEMANDED_TITLE					\n");
        	sqlstr.append("         , DEMANDED_KIND					\n");
        	sqlstr.append("         , DEPT_CD								\n");
        	sqlstr.append("         , DEPT_NM								\n");
        	sqlstr.append("         , CURRENCY_GB						\n");
        	sqlstr.append("         , EMP_NO								\n");
        	sqlstr.append("         , EMP_NM								\n");
        	sqlstr.append("         , REQ_DEPT_CD						\n");
        	sqlstr.append("         , REQ_DEPT_NM						\n");
        	sqlstr.append("         , DELIVERY_CONDITION			\n");
        	sqlstr.append("         , BID_APP_DATE						\n");
        	sqlstr.append("         , BID_APP_NO							\n");
        	sqlstr.append("         , PAY_ITEM							\n");
        	sqlstr.append("         , COST_CODE							\n");
        	sqlstr.append("         , COST_TYPE							\n");
        	sqlstr.append("         , RECEIPT_DATE						\n");
        	sqlstr.append("         , SITEDESC_FLAG					\n");
        	sqlstr.append("         , SITEDESCCHRGPRSNID			\n");
        	sqlstr.append("         , SITEDESC_CRG_NM				\n");
        	sqlstr.append("         , SITEDESCTELNO					\n");
        	sqlstr.append("         , SITEDESCDATE						\n");
        	sqlstr.append("         , SITEDESCPLACE					\n");
        	sqlstr.append("         , SITEDESCEMAIL					\n");
        	sqlstr.append("         , PUREMPNO							\n");
        	sqlstr.append("         , INNER_REMARKS					\n");
        	sqlstr.append("         , TBE_FLAG							\n");
        	sqlstr.append("         , OUTER_REMARKS					\n");
        	sqlstr.append("         , REQUEST_DATE						\n");
        	sqlstr.append("         , CONT_AMT							\n");
        	sqlstr.append("         , ACT_AMT								\n");
        	sqlstr.append("         , ACT_RATE							\n");
        	sqlstr.append("         , ONBID_YN							\n");
        	sqlstr.append("         , DELI_FROM_DY						\n");
        	sqlstr.append("         , DELI_TO_DY							\n");
        	sqlstr.append("         , DELIVERY_PLACE 					\n");
        	sqlstr.append("         , DELI_START_DT					\n");
        	sqlstr.append("         , DELI_END_DT						\n");
        	sqlstr.append("         , CRE_BY		 						\n");
        	sqlstr.append("         , CRE_DATE 							\n");
        	sqlstr.append("         , BID_NO 								\n");
        	sqlstr.append("         , BID_DEG 								\n");
        	sqlstr.append("         , STATUS_CD 							\n");
        	sqlstr.append("         , DOC_ETC_IN 						\n");
        	sqlstr.append("         , DOC_ETC_OUT 						\n");
        	sqlstr.append("         , OUT_ITEM_CD 						\n");
        	sqlstr.append("         , BID_MASTER_NM					\n");
        	sqlstr.append("         , PAY_DIV								\n");
        	sqlstr.append("         , MATERIAL_GB						\n");
        	sqlstr.append("       ) 											\n");
        	sqlstr.append("SELECT A.DEMANDED_ID					\n");
        	sqlstr.append("     , A.DEMANDED_NO						\n");
        	sqlstr.append("     , A.DEMANDED_DATE					\n");
        	sqlstr.append("     , A.DEMANDED_TITLE					\n");
        	sqlstr.append("     , A.DEMANDED_KIND					\n");
        	sqlstr.append("     , A.DEPT_CD								\n");
        	sqlstr.append("     , (SELECT DEPT_NM FROM ACZ10100V WHERE DEPT_CD=A.DEPT_CD)							\n");
        	sqlstr.append("     , A.CURRENCY_GB						\n");
        	sqlstr.append("     ,  (SELECT ACCT_ID FROM PUB0120T WHERE USER_SABUN = A.EMP_NO)								\n");
        	sqlstr.append("     , GET_USER_SABUN_NM(A.EMP_NO)						\n");
        	sqlstr.append("     , (SELECT B.DEPART_DEPT_CD FROM PUB0120T B WHERE B.USER_SABUN = A.EMP_NO)								\n");
        	sqlstr.append("     , (SELECT B.DEPART_DEPT_NM FROM PUB0120T B WHERE B.USER_SABUN = A.EMP_NO)								\n");
        	sqlstr.append("     , A.DELIVERY_CONDITION				\n");
        	sqlstr.append("     , A.APP_DATE								\n");
        	sqlstr.append("     , A.APP_NO								\n");
        	sqlstr.append("     , A.PAY_ITEM								\n");
        	sqlstr.append("     , A.COST_CODE							\n");
        	sqlstr.append("     , A.COST_TYPE							\n");
        	sqlstr.append("     , SYSDATE									\n");
        	sqlstr.append("     , A.SITEDESC_FLAG						\n");
        	sqlstr.append("     , (SELECT ACCT_ID FROM PUB0120T WHERE USER_SABUN = A.SITEDESCCHRGPRSNID) 				\n");
        	sqlstr.append("     , GET_USER_NM(SITEDESCCHRGPRSNID)			\n");
        	sqlstr.append("     , A.SITEDESCTELNO						\n");
        	sqlstr.append("     , A.SITEDESCDATE						\n");
        	sqlstr.append("     , A.SITEDESCPLACE						\n");
        	sqlstr.append("     , A.SITEDESCEMAIL						\n");
        	sqlstr.append("     , A.PUREMPNO							\n");
        	sqlstr.append("     , A.INNER_REMARKS						\n");
        	sqlstr.append("     , A.TBE_FLAG								\n");
        	sqlstr.append("     , A.OUTER_REMARKS					\n");
        	sqlstr.append("     , A.REQUEST_DATE						\n");
        	sqlstr.append("     , A.CONT_AMT							\n");
        	sqlstr.append("     , A.ACT_AMT								\n");
        	sqlstr.append("     , A.ACT_RATE								\n");
        	sqlstr.append("     , ?											\n");
        	sqlstr.append("     , A.DELI_START_DT						\n");
        	sqlstr.append("     , A.DELI_END_DT							\n");
        	sqlstr.append("     , A.DELI_END_PLACE					\n");
        	sqlstr.append("     , A.DELI_START_DT						\n");
        	sqlstr.append("     , A.DELI_END_DT							\n");
        	sqlstr.append("     , ?	 										\n");
        	sqlstr.append("     , SYSDATE 								\n");
        	sqlstr.append("     , ?											\n");
        	sqlstr.append("     ,1												\n");
        	sqlstr.append("     ,'00'											\n");
        	sqlstr.append("     ,'BID_IN_1'									\n");
        	sqlstr.append("     ,'BID_OUT_1'								\n");
        	sqlstr.append("     ,OUT_ITEM_CD							\n");
        	sqlstr.append("     ,DEMANDED_TITLE						\n");
        	sqlstr.append("     , PAY_DIV									\n");
        	sqlstr.append("     , (SELECT D.MATERIAL_GB FROM PMM_DEMANDED_ITEM PDI,  PMM_COMM_MATERIAL_MASTER C,  PMM_MATERIALGROUP D									\n");
        	sqlstr.append("     WHERE PDI.MATERIAL_CD = C.MATERIAL_CD AND C.KIND_BIG = D.MATERIALGROUP AND PDI.DEMANDED_ID = ? AND ROWNUM = 1)						\n");
        	sqlstr.append("  FROM PMM_DEMANDED_REQUEST A 	\n");
        	sqlstr.append(" WHERE A.DEMANDED_ID= ? 				\n");			
        	pstmt.setStatement(sqlstr.toString());
        	pstmt.addParameter(params.get("ONBID_YN"));
			pstmt.addParameter(storage.getUser().get("acct_id"));
			pstmt.addParameter(params.get("BID_NO"));
			pstmt.addParameter(params.get("DEMANDED_ID"));
			pstmt.addParameter(params.get("DEMANDED_ID"));
			pstmt.execute();
        }catch (Exception e){
			WAFLogger.error(e);
			throw new Exception(e);
        }
	}
	
	/**
	 * 발주의뢰 접수
	 * @return
	 */
	private void BID_SUPP_insert(Connection connection) throws Exception{
		StrBuffer sqlstr = new StrBuffer();
		Persistent pstmt = new PersistentImpl(connection);
		String SITEPARTI_YN = "";
		String SITE_ATTEND_YN = "";
		String FileGB = "";
        try{
        	sqlstr = new StrBuffer();
			sqlstr.append("     INSERT												\n");
			sqlstr.append("       INTO BID_SUPP									\n");				
			sqlstr.append("            ( 												\n");
			sqlstr.append("                BID_NO									\n");
			sqlstr.append("              ,BID_DEG									\n");
			sqlstr.append("              ,VENDOR_CD -- 거래처 코드				\n");
			sqlstr.append("              ,VENDOR_NO -- 사업자 번호				\n");
			sqlstr.append("              ,VENDOR_NAME 							\n");
			sqlstr.append("              ,REPRE_NO								\n");
			sqlstr.append("              ,RECOMMEND_REASON					\n");
			sqlstr.append("              ,CHARGE_NAME							\n");
			sqlstr.append("              ,CHAGE_HP								\n");
			sqlstr.append("              ,CHARGE_EMAIL							\n");
			sqlstr.append("              ,RECM_DEPT_ID							\n");
			sqlstr.append("              ,RECM_DEPT_NM						\n");
			sqlstr.append("              ,FILE_GB									\n");
			sqlstr.append("              ,SITE_ATTEND_YN						\n");
			sqlstr.append("              ,SITEPARTI_YN							\n");
			sqlstr.append("              ,CRE_BY									\n");
			sqlstr.append("              ,CRE_DATE								\n");
			sqlstr.append("            ) 											 	\n");
			sqlstr.append("      SELECT ?														\n");
			sqlstr.append("          ,1															\n");
			sqlstr.append("          , SI.CORP_ID	-- 거래처 코드							\n");
			sqlstr.append("          , REPLACE(NVL(COPR.VENDOR_ID,SI.CORP_REG_NO),'-','')  -- 사업자번호		\n");
			sqlstr.append("          , NVL(COPR.VENDOR_NAME,SI.CORP_KOR)   -- 업체명							\n");
			sqlstr.append("          , NVL(COPR.PRE_NM,SI.REP_NAME)  -- 대표자명									\n");
			sqlstr.append("          , COPR.RECOMMEND_REASON -- 추천사유 				\n");
			sqlstr.append("          , NVL(COPR.CHARGE_NAME,SC.USER_NAME)  --담당자명							\n");
			sqlstr.append("          , NVL(COPR.CHARGE_TEL,SC.HP1||'-'||SC.HP2||'-'||SC.HP3)  -- 담당자 전화번호					\n");
			sqlstr.append("          , NVL(COPR.CHARGE_EMAIL,SC.EMAIL)  -- 이메일							\n");
			sqlstr.append("          , ?  -- 추천부서아이디										\n");
			sqlstr.append("          , ?  -- 추천부서명											\n");
			sqlstr.append("          , DECODE(?,'','','INQ_'||NVL(COPR.VENDOR_ID,SI.CORP_REG_NO)||TO_CHAR(SYSDATE,'YYYYMMDD')||'OFF')	\n");
			sqlstr.append("          , ?															\n");
			sqlstr.append("          , ?															\n");
			sqlstr.append("          , ?															\n");
			sqlstr.append("          , SYSDATE												\n");
			sqlstr.append("       FROM PMM_COPR COPR,SUPP_INFO SI, SUPP_CHARGE SC 									\n");
			sqlstr.append("	   WHERE COPR.DEMANDED_ID = ?					    \n");
			sqlstr.append("       AND SI.CORP_REG_NO= REGEXP_REPLACE(COPR.VENDOR_ID,'[^0-9]') 			\n");
			sqlstr.append("      AND SI.JOB_DIV = 'P' 										\n");
			sqlstr.append("      AND SI.STATUS IN ('02','03')	 							\n");
			sqlstr.append("      AND SI.CORP_ID = SC.CORP_ID(+)						\n");
			sqlstr.append("      AND SC.DEFAULT_YN(+) = 'Y'							\n");
        	pstmt.setStatement(sqlstr.toString());		
        	pstmt.addParameter(params.get("BID_NO"));
        	pstmt.addParameter(params.get("BUSEO"));
        	pstmt.addParameter(params.get("BUSEO_NM"));
        	/*오프라인 일경우 현설 참여로 업데이트*/
        	if("N".equals(params.get("ONBID_YN")) || "오프라인".equals(params.get("ONBID_YN"))){
        		SITEPARTI_YN ="Y";
        		SITE_ATTEND_YN = "Y";
                FileGB = "FILE_GB";
        	}
	        pstmt.addParameter(FileGB);
	        pstmt.addParameter(SITE_ATTEND_YN);
	        pstmt.addParameter(SITEPARTI_YN);
			pstmt.addParameter(storage.getUser().get("acct_id"));
			pstmt.addParameter(params.get("DEMANDED_ID"));
			pstmt.execute();
        }catch (Exception e){
			WAFLogger.error(e);
			throw new Exception(e);
        }
	}
	
	/**
	 * 발주의뢰 접수
	 * @return
	 */
	private void PMM_DEMANDED_REQUEST_update(Connection connection) throws Exception{
		StrBuffer sqlstr = new StrBuffer();
		Persistent pstmt = new PersistentImpl(connection);
        try{
			pstmt.clear();
			sqlstr = new StrBuffer();
			sqlstr.append("     UPDATE PMM_DEMANDED_REQUEST			\n");
			sqlstr.append("       SET RECEIPT_NO = ?								\n");				
			sqlstr.append("            ,ONBID_YN = ?									\n");
			sqlstr.append("            ,RECEIPT_DATE = SYSDATE				\n");
			sqlstr.append("            ,UPD_BY = ?									\n");
			sqlstr.append("            ,UPD_DATE = SYSDATE 						\n");
			sqlstr.append("            WHERE DEMANDED_ID = ?					\n");
        	pstmt.setStatement(sqlstr.toString());
			pstmt.addParameter(storage.getUser().get("acct_id"));
			pstmt.addParameter(params.get("ONBID_YN"));
			pstmt.addParameter(storage.getUser().get("acct_id"));
			pstmt.addParameter(params.get("DEMANDED_ID"));
			pstmt.execute();
        }catch (Exception e){
			WAFLogger.error(e);
			throw new Exception(e);
        }
	}
	
	
	/**
	 * 발주의뢰 접수
	 * @return
	 */
	private void BID_ITEM_insert(Connection connection) throws Exception{
		StrBuffer sqlstr = new StrBuffer();
		Persistent pstmt = new PersistentImpl(connection);
        try{
			pstmt.clear();
			sqlstr.append("     INSERT INTO BID_ITEM (						\n");
			sqlstr.append("                BID_NO									\n");
			sqlstr.append("              ,BID_DEG									\n");
			sqlstr.append("              ,MATERIAL_CD -- 아이템코드			\n");
			sqlstr.append("              ,REQUEST_DATE -- 투입요청일			\n");
			sqlstr.append("              ,DEMANDED_PRI -- 단가				\n");
			sqlstr.append("              ,DEMANDED_QTY  -- 수량				\n");
			sqlstr.append("              ,CONT_UNIT_AMT  -- 도급단가		\n");
			sqlstr.append("              ,ACT_UNIT_AMT  -- 실행단가			\n");
			sqlstr.append("              ,STD  -- 수량								\n");		
			sqlstr.append("            )												\n");
			sqlstr.append("     SELECT ?											\n");
			sqlstr.append("              ,1											\n");
			sqlstr.append("              ,A.MATERIAL_CD						\n");
			sqlstr.append("              ,A.REQUEST_DATE						\n");
			sqlstr.append("              ,DECODE(NVL(A.DEMANDED_AMT,0),0,0,A.DEMANDED_AMT/A.DEMANDED_QTY)	\n");
			sqlstr.append("              ,A.DEMANDED_QTY						\n");
			sqlstr.append("              ,A.CONT_PRI								\n");
			sqlstr.append("              ,A.DEMANDED_PRI						\n");
			sqlstr.append("              ,B.MATERIAL_SPEC  					\n");						
			sqlstr.append("            FROM PMM_DEMANDED_ITEM A , PMM_COMM_MATERIAL_MASTER B , PMM_DEMANDED_REQUEST C						\n");	
			sqlstr.append("            WHERE A.DEMANDED_ID = ?																													\n");
			sqlstr.append("            AND A.MATERIAL_CD = B.MATERIAL_CD																									\n");
			sqlstr.append("            AND A.DEMANDED_ID = C.DEMANDED_ID																									\n");
        	pstmt.setStatement(sqlstr.toString());
        	pstmt.addParameter(params.get("BID_NO"));
        	pstmt.addParameter(params.get("DEMANDED_ID"));
			pstmt.execute();
        }catch (Exception e){
			WAFLogger.error(e);
			throw new Exception(e);
        }
	}
	
	/**
	 * TBE_INFO 데이터 저장
	 * @return
	 */
	private void TEB_INFO_insert(Connection connection) throws Exception{
		StrBuffer sqlstr = new StrBuffer();
		Persistent pstmt = new PersistentImpl(connection);
        try{
			pstmt.clear();
			sqlstr.append("     INSERT INTO TBE_INFO ( BID_NO,BID_DEG,SUPP_REQ_DY,STATUS_CD )					\n");
			sqlstr.append("     SELECT ? 					\n");
			sqlstr.append("     ,1					\n");
			sqlstr.append("     ,TBE_DATE					\n");
			sqlstr.append("     ,'01'					\n");
			sqlstr.append("     FROM PMM_DEMANDED_REQUEST					\n");
			sqlstr.append("     WHERE DEMANDED_ID = ?					\n");
        	pstmt.setStatement(sqlstr.toString());
        	pstmt.addParameter(params.get("BID_NO"));
			pstmt.addParameter(params.get("DEMANDED_ID"));
			pstmt.execute();
        }catch (Exception e){
			WAFLogger.error(e);
			throw new Exception(e);
        }
	}
	
	 /**
     * BID_NO를 생성한다.
	 * @param rightID
	 * @return
	 */
	public String BIDNO() throws Exception {
        
        Search search = new SearchImpl();
        StrBuffer sqlstr = new StrBuffer();
        try {
        	
        	sqlstr.append(" SELECT CASE  ");
			sqlstr.append(" WHEN INSTR(REPLACE(UPPER(DEMANDED_NO),'MR',''),'-')=1 THEN 'INQ-'||REPLACE(UPPER(DEMANDED_NO),'MR-','')");
			sqlstr.append(" WHEN INSTR(REPLACE(UPPER(DEMANDED_NO),'MR',''),'_')=1 THEN 'INQ-'||REPLACE(UPPER(DEMANDED_NO),'MR_','')");
			sqlstr.append(" WHEN INSTR(REPLACE(UPPER(DEMANDED_NO),'WR',''),'-')=1 THEN 'INQ-'||REPLACE(UPPER(DEMANDED_NO),'WR-','')");
			sqlstr.append(" WHEN INSTR(REPLACE(UPPER(DEMANDED_NO),'WR',''),'_')=1 THEN 'INQ-'||REPLACE(UPPER(DEMANDED_NO),'WR_','')");
			sqlstr.append(" ELSE 'INQ-'||DEMANDED_NO END AS BID_NO");
			sqlstr.append(" FROM PMM_DEMANDED_REQUEST ");
			sqlstr.append(" WHERE DEMANDED_ID = ? ");
            search.setStatement(sqlstr.toString());
            search.addParameter(params.get("DEMANDED_ID"));
            return search.execute().getRow(0).get("BID_NO");
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> BIDNO() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
    }
	
	 /**
     * BID_NO에 차수를 더한다.
	 * @param rightID
	 * @return
	 */
	public String BidNodeg(String BID_NO) throws Exception {
        
        Search search = new SearchImpl();
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append(" SELECT NVL(TRIM(TO_CHAR(MAX(SUBSTR(BID_NO,LENGTH(BID_NO)-2,LENGTH(BID_NO)))+1,'000')),'001') AS DEG FROM BID_MASTER  ");
			sqlstr.append(" WHERE SUBSTR(BID_NO,0,LENGTH(BID_NO)-4) = ?									 ");
            search.setStatement(sqlstr.toString());
            search.addParameter(BID_NO);
            return BID_NO+"_"+search.execute().getRow(0).get("DEG");
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> BidNodeg() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
    }
	
	
	public boolean update_mrreq_po(Connection connection,String ebid_staus_code) throws Exception {
		return update_mrreq_po(connection,ebid_staus_code,"");
	}
	
	/**
	 * 발주의뢰 결재 정보 조회
	 * @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;
	}

}
