/**
 * @(#)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.outside.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.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.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class TBECheckBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
        RowSet tbe_info = null;
        RowSet tbe_info_file = new RowSetImpl();
		
		if(storage.compareEvent("SHEET_SELECT")){
			params.set("sheet1", getBoardList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("FORM_SELECT")){//견적의뢰
	            user = getOrderReqeustValueObject();
	            tbe_info = getCorpList();
	            for(int i=0; i < tbe_info.size();i++){
	            	tbe_info.getRow(i);
	            	Row row = tbe_info.getRow(i);
	            	row.addColumn("FILE_LIST", getfilelist(row.get("FIN_FILE_GB"),row.get("FIN_FILE_ID")));
	            	tbe_info_file.addRow(row);
	            }
	            if(user.get("TOT_FILE_GB").isEmpty()){
            		String file_gb = "TBE_TOT" + user.get("BID_NO") + user.get("BID_DEG");
            		user.set("TOT_FILE_GB", file_gb);
            	}
	            storage.addValueObject("Order", user);
            	storage.addRowSet("TBE_INFO", tbe_info_file);

            	storage.addValueObject("SIGN_INFO", getSignInfo(params.get("DEMANDED_ID")));
		        
	            /**		파일 목록 조회			 **/
		        storage.addValueObject(user.get("DOC_ETC_IN"), getfilelist(user.get("DOC_ETC_IN"),user.get("DEMANDED_ID"),user.get("DOC_ETC_IN")));
		        storage.addValueObject(user.get("DOC_ETC_OUT"), getfilelist(user.get("DOC_ETC_OUT"),user.get("DEMANDED_ID"),user.get("DOC_ETC_OUT")));
		        storage.addValueObject(user.get("TOT_FILE_GB"), getfilelist(user.get("TOT_FILE_GB"),user.get("DEMANDED_ID"),user.get("TOT_FILE_GB")));
		}else if(storage.compareEvent("SAVE")){
			boolean success = false;
			//success = save();
			super.storage.setSuccess(success);
			if(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;
	}
	private String getfilelist(String filegb,String id){
		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";
	        	}
	        }
		return str;
	}

	/**
	 * 발주의뢰(외주) 접수를 검색하여 리턴
	 * @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.BID_MASTER_NM																													\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("      , TO_CHAR(A.OPEN_DT,'YYYY-MM-DD HH24:MI') AS OPEN_DT															\n");
        	sqlstr.append("      , A.OPEN_EMPNM		 																												\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");
        	sqlstr.append("   AND A.DEMANDED_KIND = 'O'						\n");
        	sqlstr.append("   AND A.OPEN_DT IS NOT NULL																									\n");
        	if(!param.get("P_DEPT_CD").isEmpty())
        		sqlstr.append("   AND A.DEPT_CD = '"+param.get("P_DEPT_CD")+"'																											\n");
        	if(!param.get("P_BID_MASTER_NM").isEmpty())
        		sqlstr.append("   AND A.BID_MASTER_NM LIKE '%'|| '"+param.get("P_BID_MASTER_NM")+"' ||'%'																	\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("       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("       ONBID_YN AS ONBID_YN_CD,                                       \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("       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("       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("       B.TOT_OPIN,                                        \n");
		       sqlstr.append("       NVL(B.EVAL_TEL,(SELECT TEL_NO FROM PUB0120T WHERE ACCT_ID = B.EVAL_EMPID)) AS EVAL_TEL,                                        \n");
		       sqlstr.append("       NVL(B.EVAL_EMAIL,(SELECT E_MAIL FROM PUB0120T WHERE ACCT_ID = B.EVAL_EMPID)) AS EVAL_EMAIL,                                 	  	     \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("       F_GET_CODE_NAME('CONT_TYPE',A.OBC_CONT_TYPE_CD) AS OBC_CONT_TYPE_NM,				\n");
		       sqlstr.append("       A.OBC_FRST_CASH_RATE,				\n");
		       sqlstr.append("       A.OBC_FRST_CONDI,				\n");
		       sqlstr.append("       A.OBC_FRST_CNT_CONDI,				\n");
		       sqlstr.append("       A.OBC_MID_CASH_RATE,				\n");
		       sqlstr.append("       A.OBC_MID_BILL_RATE,				\n");
		       sqlstr.append("       A.OBC_MID_PAYDAY,				\n");
		       sqlstr.append("       A.OBC_MID_REQ_MONTH,				\n");
		       sqlstr.append("       A.OBC_MID_CNT,				\n");
		       sqlstr.append("       A.OBC_FIN_CASH_RATE,				\n");
		       sqlstr.append("       A.OBC_FIN_BILL_RATE,				\n");
		       sqlstr.append("       A.OBC_FIN_PAYDAY,				\n");
		       sqlstr.append("       A.OBC_FIN_CONDI,				\n");
		       sqlstr.append("       A.CBC_FIRST_RATE,				\n");
		       sqlstr.append("       A.CONTFUL_RATE,				\n");
		       sqlstr.append("       A.WARR_RATE,				\n");
		       sqlstr.append("       A.CBC_DEFECT_DAY,				\n");
		       sqlstr.append("       A.PAY_CON_RATE,				\n");
		       sqlstr.append("       A.CBC_DAMAGE_RATE,				\n");
		       sqlstr.append("       A.CBC_DAMAGE_MAXRATE,				\n");
		       sqlstr.append("       A.OBC_EMP_RATE,				\n");
		       sqlstr.append("       A.OBC_EMP_YN,				\n");
		       sqlstr.append("       A.OBC_PENSION_RATE,				\n");
		       sqlstr.append("       A.OBC_PENSION_YN,				\n");
		       sqlstr.append("       A.OBC_HEALTH_RATE,				\n");
		       sqlstr.append("       A.OBC_HEALTH_YN,				\n");
		       sqlstr.append("       A.OBC_ELDER_RATE,				\n");
		       sqlstr.append("       A.OBC_ELDER_YN,				\n");
		       sqlstr.append("       A.OBC_WORK_RATE,				\n");
		       sqlstr.append("       A.OBC_WORK_YN,				\n");
		       sqlstr.append("       A.OBC_RESIGN_RATE,				\n");
		       sqlstr.append("       A.OBC_RESIGN_YN,				\n");
		       sqlstr.append("       A.OBC_SAFE_RATE,				\n");
		       sqlstr.append("       A.OBC_SAFE_YN,				\n");
		       sqlstr.append("       A.OBC_ENV_RATE,				\n");
		       sqlstr.append("       A.OBC_ENV_YN,				\n");
		       sqlstr.append("       A.OBC_SITE_RATE,				\n");
		       sqlstr.append("       A.OBC_SITE_YN,				\n");
		       sqlstr.append("       A.OBC_DUTY_RATE,				\n");
		       sqlstr.append("       A.OBC_DUTY_YN,				\n");
		       sqlstr.append("       A.OBC_WARR_YN,				\n");
		       sqlstr.append("       A.OBC_FRST_CONT_RATE,				\n");
		       sqlstr.append("       A.CPC_MID_RATE,				\n");
		       sqlstr.append("       A.PBC_REST_RATE,				\n");
		       sqlstr.append("       A.OBC_IND_YN,				\n");
		       sqlstr.append("       B.RTN_REASON,						\n");
		       sqlstr.append("       B.RTN_DATE,						\n");
		       sqlstr.append("       B.RTN_EMPNO,						\n");
		       sqlstr.append("       F_GET_USER_NM(B.RTN_EMPNO) AS RTN_EMPNM,						\n");
		       sqlstr.append("       A.REBID_REASON,						\n");
		       sqlstr.append("       A.REBID_OUTREASON,						\n");
		       sqlstr.append("       A.OBC_MID_BILL_DATE,						\n");
		       sqlstr.append("       B.STATUS_CD AS TBE_STATUS_CD,						\n");
		       sqlstr.append("      (SELECT E_MAIL FROM PUB0120T WHERE ACCT_ID = A.PUREMPNO) AS BID_EMAIL,     \n");
		       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");
			   
		     //  sqlstr.append("       AND A.DEMANDED_DIV = 'O'							\n");   * 청구구분 O 인거 
        	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() {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("SELECT A.VENDOR_NO																																	\n");
        	sqlstr.append("     ,A.VENDOR_NAME																																	\n");
        	sqlstr.append("     ,A.VENDOR_CD																																		\n");
        	sqlstr.append("     ,(SELECT COUNT(*) FROM TBE_SUPP_ATTFILE C WHERE B.BID_NO = C.BID_NO AND B.BID_DEG = C.BID_DEG AND B.VENDOR_CD = C.VENDOR_CD AND C.STATUS_CD > '00') AS FILE_CNT									\n");
        	sqlstr.append("     ,(SELECT COUNT(*) FROM TBE_SUPP_ATTFILE C WHERE B.BID_NO = C.BID_NO AND B.BID_DEG = C.BID_DEG AND B.VENDOR_CD = C.VENDOR_CD AND C.STATUS_CD = '10') AS CL_CNT	\n");
        	sqlstr.append("     ,TO_CHAR(B.REQ_DY,'YYYY-MM-DD') AS REQ_DY																							\n");
        	sqlstr.append("     ,B.STATUS_CD																																		\n");
        	sqlstr.append("     ,CASE WHEN A.RNNC_SIGNDATA IS NOT NULL THEN '입찰포기'																			\n");
        	//sqlstr.append("           WHEN B.VENDOR_CD IS NULL AND A.RNNC_SIGNDATA IS NULL AND A.SIGN_DT IS NOT NULL THEN '문서 미제출'		\n");
        	//sqlstr.append("           WHEN B.VENDOR_CD IS NULL AND A.RNNC_SIGNDATA IS NULL AND A.SIGN_DT IS NULL THEN '견적 미제출'				\n");
        	sqlstr.append("           	WHEN B.STATUS_CD IS NULL THEN '문서요청'																							\n");
        	sqlstr.append("           ELSE F_GET_CODE_NAME('TBE_SUPP_STATUS_CD',B.STATUS_CD) END AS STATUS_NM									\n");
        	sqlstr.append("     ,A.SIGN_DT																																			\n");
        	sqlstr.append("     ,A.BID_RNNC_DT																																		\n");
        	sqlstr.append("     ,B.FIN_FILE_GB																																		\n");
        	sqlstr.append("     ,B.FIN_FILE_ID																																		\n");
        	sqlstr.append("     ,B.EVAL_OPIN																																			\n");
        	sqlstr.append("     ,B.EVAL_RESULT																																		\n");
        	sqlstr.append("     ,TO_CHAR(TSC.DOC_DT,'YYYY-MM-DD') AS DOC_DT 																						\n");
        	sqlstr.append("     ,TO_CHAR(TSC.REG_DT,'YYYY-MM-DD') AS REG_DT 																						\n");
        	sqlstr.append("     ,NVL(TSC.REQ_DEG,0) AS REQ_DEG					 																						\n");
        	sqlstr.append("     ,B.REQ_YN														 																						\n");
        	sqlstr.append("  FROM (SELECT A.* FROM BID_SUPP A, (SELECT BID_NO,MAX(BID_DEG) AS BID_DEG,VENDOR_CD FROM BID_SUPP WHERE BID_NO= ? GROUP BY BID_NO,VENDOR_CD) B		\n");
   			sqlstr.append("                      WHERE A.BID_NO = B.BID_NO AND A.BID_DEG = B.BID_DEG AND A.VENDOR_CD = B.VENDOR_CD) A							\n");
   			sqlstr.append("	     , (SELECT NVL(Y.BID_NO, X.BID_NO) AS BID_NO                                              \n");
   			sqlstr.append("	                  ,  NVL(Y.BID_DEG, X.BID_DEG) AS BID_DEG                                     \n");
   			sqlstr.append("	                  ,  NVL(Y.VENDOR_CD , X.VENDOR_CD) AS VENDOR_CD                              \n");
   			sqlstr.append("	                 , Y.FIN_FILE_GB                                                              \n");                                                                          
   			sqlstr.append("	                 ,Y.FIN_FILE_ID                                                               \n");                                                                         
   			sqlstr.append("	                 ,Y.EVAL_OPIN                                                                 \n");                                                                           
   			sqlstr.append("	                 ,Y.EVAL_RESULT                                                               \n");
   			sqlstr.append("	                 ,Y.REQ_YN                                                                    \n");
   			sqlstr.append("	                 , Y.STATUS_CD                                                                \n");
   			sqlstr.append("	                 , Y.REQ_DY                                                                   \n");
   			sqlstr.append("	         FROM BID_SUPP X,                                                                     \n");
   			sqlstr.append("	                   TBE_SUPP Y                                                                 \n");
   			sqlstr.append("	         WHERE  X.BID_NO = Y.BID_NO(+)                                                        \n");
   			sqlstr.append("	         AND       X.BID_DEG = Y.BID_DEG(+)                                                   \n");
   			sqlstr.append("	         AND X.VENDOR_CD = Y.VENDOR_CD(+)                                           \n");
   			sqlstr.append("	         AND  X.BID_NO= ?                                           \n");
   			sqlstr.append("	         AND  X.BID_DEG = ?                                                                 \n");
   			sqlstr.append("	         ) B          																																				\n");
        	//sqlstr.append("     , TBE_SUPP B 																																		\n");
        	sqlstr.append("     , (SELECT MAX(REQ_DEG) AS REQ_DEG,BID_DEG,BID_NO,VENDOR_CD FROM TBE_SUPP_CHASU WHERE 1=1 GROUP BY BID_NO,BID_DEG,VENDOR_CD) SC 																																		\n");
        	sqlstr.append("     , TBE_SUPP_CHASU TSC 																															\n");				
        	sqlstr.append(" WHERE A.BID_NO= ? 																																	\n");
        	//sqlstr.append("       AND A.BID_DEG= ? 																																\n");
        	//sqlstr.append("       AND ((A.ESTI_SIGNDATA IS NOT NULL AND A.SIGN_DT IS NOT NULL) OR A.RNNC_SIGNDATA IS NOT NULL) 			\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.VENDOR_CD=B.VENDOR_CD 																										\n");
        	sqlstr.append("       AND SC.BID_NO=TSC.BID_NO(+) 																												\n");
        	sqlstr.append("       AND SC.BID_DEG=TSC.BID_DEG(+) 																											\n");
        	sqlstr.append("       AND SC.REQ_DEG=TSC.REQ_DEG(+) 																											\n");
        	sqlstr.append("       AND SC.VENDOR_CD=TSC.VENDOR_CD(+) 																									\n");
        	sqlstr.append("       AND B.BID_NO=SC.BID_NO(+) 																													\n");
        	sqlstr.append("       AND B.BID_DEG=SC.BID_DEG(+) 																												\n");
        	sqlstr.append("       AND B.VENDOR_CD=SC.VENDOR_CD(+) 																										\n");
        	sqlstr.append("       ORDER BY A.SIGN_DT ASC				 																										\n");
			search.setStatement(sqlstr.toString());
			search.addParameter(params.get("BID_NO"));
			search.addParameter(params.get("BID_NO"));
			search.addParameter(params.get("BID_DEG"));
			search.addParameter(params.get("BID_NO"));
 			//search.addParameter(params.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 Params
	 * @return
	 */
	private RowSet geitemList(ValueObject param) {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT F_GET_OUT_ITEM_NAME(SUBSTR(B.MATERIAL_CD,0,2)) AS  DEPTH1						\n");
        	sqlstr.append("      , F_GET_OUT_ITEM_NAME(SUBSTR(B.MATERIAL_CD,0,4)) AS DEPTH2							\n");
        	sqlstr.append("      , F_GET_OUT_ITEM_NAME(SUBSTR(B.MATERIAL_CD,0,6)) AS DEPTH3							\n");
        	sqlstr.append("      , B.MATERIAL_CD																								\n");
        	sqlstr.append("      , B.ITEM_NAME																									\n");
        	sqlstr.append("      , DECODE(B.PARENT_LEVEL,'R',B.MATERIAL_CD,B.PARENT_LEVEL) AS PARENT_LEVEL		\n");
        	sqlstr.append("      , DECODE(B.PARENT_LEVEL,'R','',A.DEMANDED_QTY || '/'||A.STD) AS ITEMS					\n");
        	sqlstr.append("      , C.UNIT_COST 																									\n");
        	sqlstr.append("      , C.BASIS_CALCU 																								\n");
        	sqlstr.append("      , C.NOTI 																											\n");
        	sqlstr.append("      , A.DEMANDED_QTY AS QTY 																				\n");
        	sqlstr.append("   FROM BID_ITEM A 																									\n");
        	sqlstr.append("      , BID_OUT_ITEM_MASTER B 																					\n");
        	sqlstr.append("      , BID_SUPP_ESTI C 																							\n");
        	sqlstr.append("  WHERE B.MATERIAL_CD=A.MATERIAL_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.MATERIAL_CD = C.MATERIAL_CD 																\n");
        	sqlstr.append("        AND VENDOR_CD = ?																							\n");
        	sqlstr.append("        AND A.BID_NO = ? 																							\n");
        	sqlstr.append("        AND A.BID_DEG = ?																							\n");
        	sqlstr.append(" ORDER BY MATERIAL_CD 																							\n");
			search.setStatement(sqlstr.toString());
			search.addParameter(param.get("VENDOR_CD"));
			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 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;
	}
	
	
}