/**
 * @(#)file    PreRequestSignBiz.java
 * @(#)author  OK
 * @(#)version 1.0
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package kr.co.udapsoft.ebid.buyer.cont.outside.biz;

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.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 com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

/**
 * 
 * @version 1.0
 */
public class ContChgRcvSignBiz extends Biz {
	FileManager FileManager = new FileManager();
	public ContChgRcvSignBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
		
		ValueObject vo = new ValueObject();

		vo = getContChgRcvInfo(params.get("sign_cont_mod_id"), params.get("acct_id"));
		
    	//WAFLogger.debug("### vo.size() : " + vo.get("CON_DOC_NO"));
        //String doctitle = "-"+vo.get("CST_DOC_NAME")+ "_" +vo.get("DEPT_NM");
        //2015.8.10 조재윤과장 요청 변경.
        String doctitle = "["+vo.get("DEPT_NM") + "]" + vo.get("CST_DOC_NAME");
        vo.set("doctitle", doctitle);   	
    	
		storage.addValueObject("ContInfo", vo);
		
		RowSet getFileListRowSet = getFileListRowSet("SUB_REQFILE_"+vo.get("SUB_CONT_REQ_ID"), "");
		storage.addRowSet("getFileListRowSet", getFileListRowSet);
		
		ValueObject AttachFile = getfilelist("SUB_REQFILE_"+vo.get("SUB_CONT_REQ_ID"),"","AttachFile");
        storage.addValueObject("AttachFile", AttachFile);
        
        storage.addValueObject("SIGN_INFO", getSignInfo(vo.get("SUB_CONT_REQ_ID")));

		//자동 저장 파일
		//RowSet rowSet = getTemplateFile(params.get("sign_cont_mod_id"));
		//storage.addRowSet("AUTOFILE_LIST", rowSet);          
   }
	
	 /**
     * 계약서식 조회 combo 조회
	 * @param 
	 * @param 
	 * @return RowSet 
	 */
	public RowSet getTemplateFile(String sub_cont_cd){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT FILE_ID, UNIQUE_FILE_NAME, FILE_PATH, FILE_EXT,          \n"   
            		+ "		TRIM(          \n"              
            		+ "        CASE                         \n"
				    + "       WHEN FILE_SIZE < 1024 THEN TO_CHAR(FILE_SIZE, '999,999') || ' Byte'          \n"
				    + "       ELSE          \n"
				    + "            CASE                WHEN FILE_SIZE < (1024*1024) THEN TO_CHAR(FLOOR(FILE_SIZE / 1024), '999,999') || ' KByte'          \n"
				    + "             ELSE  TO_CHAR(FLOOR(FILE_SIZE / (1024*1024)), '999,999,999') || ' MByte'          \n"
				    + "            END                           \n"                              
				    + "      END ) AS FILE_SIZE, FILE_NAME         \n"
                    + " FROM CONT_OUT_FILE      					\n"
                    + " WHERE SUB_CONT_MOD_ID =  '"+sub_cont_cd+"'		\n"
		            + " ORDER BY FILE_ID ASC      				\n";
             
            search.setStatement(sqlstr);
            listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}
	public ValueObject getContChgRcvInfo(String sign_cont_mod_id, String acct_id) {
		//System.out.println("acct_id: "+ acct_id);
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		StrBuffer sqlstr = new StrBuffer();
		ValueObject rowObject = null;
		
		try {
			
			sqlstr.append("   SELECT                                                                                                                                                                                  \n");
			sqlstr.append("        DEPART_DEPT_NM -- 요청부서                                                                                                                                                         \n");
			sqlstr.append("       ,CRE_DATE -- 작성일                                                                                                                                                                 \n");
			sqlstr.append("       ,F_GET_USER_NM('"+acct_id+"') AS USER_NAME  -- 작성자                                                                                                                                       \n");
			sqlstr.append("       ,DEALER_NM -- 사업주명                                                                                                                                                          \n");
			sqlstr.append("       ,ORD_DOC_NO    --계약번호                                                                                                                                                           \n");
			sqlstr.append("       ,GET_DEPT_NM(DEPT_CD) AS DEPT_NM  --현장명                                                                                                                                          \n");
			sqlstr.append("       ,CST_DOC_NAME -- 계약명                                                                                                                                                             \n");
			sqlstr.append("       ,S_MEMBER_NAME -- 공급자                                                                                                                                                            \n");
			sqlstr.append("       ,CASE WHEN CURRENCY_GB = 'KRW' THEN '일금'                                                                                                                                           \n");
			sqlstr.append("                  ELSE CHG_CODE09                                                                                                                                                          \n");
			sqlstr.append("        END BEF_HAN                                                                                                                                                                        \n");
			sqlstr.append("       ,CASE WHEN CURRENCY_GB = 'KRW' THEN '원정'                                                                                                                                           \n");
			sqlstr.append("                  ELSE CHG_CODE06                                                                                                                                                          \n");
			sqlstr.append("        END ATF_HAN                                                                                                                                                                        \n");
			sqlstr.append("       , F_GET_HANGUL2(AFT_SUPP_AMT) AFT_SUPP_AMT_HAN                                                                                                                                      \n");
			sqlstr.append("       ,NVL(BEF_CONT_AMT,0) AS BEF_CONT_AMT                                                                                                                                                \n");
			sqlstr.append("       ,NVL(AFT_CONT_AMT,0) AS AFT_CONT_AMT                                                                                                                                                \n");
			sqlstr.append("       ,CASE WHEN NVL(CHG_CONT_AMT,0) = 0 THEN '변동 없음'                                                                                                                                   \n");
			sqlstr.append("             ELSE F_GET_D_CURRENCY(NVL(CHG_CONT_AMT,0),F_GET_CURRENCY_DIGIT(CURRENCY_GB))||                                                                                                \n");
			sqlstr.append("             (                                                                                                                                                                             \n");
			sqlstr.append("                  CASE WHEN NVL(BEF_CONT_AMT,0) > NVL(AFT_CONT_AMT,0) THEN ' (▼)'                                                                                                          \n");
			sqlstr.append("                       WHEN NVL(BEF_CONT_AMT,0) < NVL(AFT_CONT_AMT,0) THEN ' (▲)'                                                                                                          \n");
			sqlstr.append("                       ELSE ''                                                                                                                                                             \n");
			sqlstr.append("                  END                                                                                                                                                                      \n");
			sqlstr.append("             )                                                                                                                                                                             \n");
			sqlstr.append("        END AS CONT_AMT_NM                                                                                                                                                                 \n");
//			sqlstr.append("       ,CASE WHEN NVL(BEF_CONT_AMT,0) > 0 AND NVL(AFT_CONT_AMT,0) > 0THEN                                                                                                                  \n");
//			sqlstr.append("       CASE WHEN BEF_CONT_AMT > AFT_CONT_AMT THEN F_GET_D_CURRENCY((BEF_CONT_AMT - AFT_CONT_AMT),0)||' (▼)'                                                                                \n");
//			sqlstr.append("       WHEN BEF_CONT_AMT < AFT_CONT_AMT THEN F_GET_D_CURRENCY((AFT_CONT_AMT - BEF_CONT_AMT),0)||' (▲)'                                                                                     \n");
//			sqlstr.append("       ELSE '0'                                                                                                                                                                            \n");
//			sqlstr.append("       END                                                                                                                                                                                 \n");
//			sqlstr.append("       END CON_AMT_NM                                                                                                                                                                      \n");
			sqlstr.append("       ,NVL(BEF_BUG_AMT,0) AS BEF_BUG_AMT                                                                                                                                                  \n");
			sqlstr.append("       ,NVL(AFT_BUG_AMT,0) AS AFT_BUG_AMT                                                                                                                                                  \n");
			sqlstr.append("       ,CASE WHEN NVL(CHG_BUG_AMT,0) = 0 THEN '변동 없음'                                                                                                                                    \n");
			sqlstr.append("             ELSE F_GET_D_CURRENCY(NVL(CHG_BUG_AMT,0),F_GET_CURRENCY_DIGIT(CURRENCY_GB))||                                                                                                 \n");
			sqlstr.append("             (                                                                                                                                                                             \n");
			sqlstr.append("                  CASE WHEN NVL(BEF_BUG_AMT,0) > NVL(AFT_BUG_AMT,0) THEN ' (▼)'                                                                                                            \n");
			sqlstr.append("                       WHEN NVL(BEF_BUG_AMT,0) < NVL(AFT_BUG_AMT,0) THEN ' (▲)'                                                                                                            \n");
			sqlstr.append("                       ELSE ''                                                                                                                                                             \n");
			sqlstr.append("                  END                                                                                                                                                                      \n");
			sqlstr.append("             )                                                                                                                                                                             \n");
			sqlstr.append("        END AS BUG_AMT_NM                                                                                                                                                                  \n");
			sqlstr.append("       ,CASE WHEN NVL(AFT_CONT_AMT,0) > 0THEN '도급대비: '||F_GET_D_CURRENCY(ROUND(AFT_BUG_AMT / AFT_CONT_AMT * 100,1),1)||'%'                                                              \n");
			sqlstr.append("        END BUG_AMT_RATE                                                                                                                                                                   \n");
			sqlstr.append("       ,NVL(BEF_SUPP_AMT,0) AS BEF_SUPP_AMT                                                                                                                                                \n");
			sqlstr.append("       ,NVL(AFT_SUPP_AMT,0) AS AFT_SUPP_AMT                                                                                                                                                \n");
			sqlstr.append("       ,NVL(BEF_SUPP_AMT,0) * EXCHANGE BEF_SUPP_AMT_KRW  -- 원 계약금액_원화                                                                                                                                                                                                                                    \n");
			sqlstr.append("       ,NVL(AFT_SUPP_AMT,0) * EXCHANGE AFT_SUPP_AMT_KRW  -- 현 계약금액_원화                                                                                                                                                                                                                                    \n");
			sqlstr.append("       ,CASE WHEN NVL(CHG_SUPP_AMT,0) = 0 THEN '변동 없음'                                                                                                                                   \n");
			sqlstr.append("             ELSE F_GET_D_CURRENCY(NVL(CHG_SUPP_AMT,0),F_GET_CURRENCY_DIGIT(CURRENCY_GB))||                                                                                                \n");
			sqlstr.append("             (                                                                                                                                                                             \n");
			sqlstr.append("                  CASE WHEN NVL(BEF_SUPP_AMT,0) > NVL(AFT_SUPP_AMT,0) THEN ' (▼)'                                                                                                          \n");
			sqlstr.append("                       WHEN NVL(BEF_SUPP_AMT,0) < NVL(AFT_SUPP_AMT,0) THEN ' (▲)'                                                                                                          \n");
			sqlstr.append("                       ELSE ''                                                                                                                                                             \n");
			sqlstr.append("                  END                                                                                                                                                                      \n");
			sqlstr.append("             )                                                                                                                                                                             \n");
			sqlstr.append("        END AS SUPP_AMT_NM                                                                                                                                                                 \n");
			sqlstr.append("       ,CASE WHEN NVL(CHG_SUPP_AMT,0) = 0 THEN '변동 없음'                                                                                                                                   \n");
			sqlstr.append("             ELSE F_GET_D_CURRENCY(NVL(CHG_SUPP_AMT,0) * EXCHANGE,0)||                                                                                                                     \n");
			sqlstr.append("             (                                                                                                                                                                             \n");
			sqlstr.append("                  CASE WHEN NVL(BEF_SUPP_AMT,0) > NVL(AFT_SUPP_AMT,0) THEN ' (▼)'                                                                                                          \n");
			sqlstr.append("                       WHEN NVL(BEF_SUPP_AMT,0) < NVL(AFT_SUPP_AMT,0) THEN ' (▲)'                                                                                                          \n");
			sqlstr.append("                       ELSE ''                                                                                                                                                             \n");
			sqlstr.append("                  END                                                                                                                                                                      \n");
			sqlstr.append("             )                                                                                                                                                                             \n");
			sqlstr.append("        END AS SUPP_AMT_NM_KRW                                                                                                                                                             \n");
//			sqlstr.append("        ,CASE WHEN NVL(BEF_SUPP_AMT,0) > 0 AND NVL(AFT_SUPP_AMT,0) > 0THEN                                                                                                                 \n");
//			sqlstr.append("       CASE WHEN BEF_SUPP_AMT > AFT_SUPP_AMT THEN F_GET_D_CURRENCY((BEF_SUPP_AMT - AFT_SUPP_AMT),0)||' (▼)'                                                                                \n");
//			sqlstr.append("       WHEN BEF_SUPP_AMT < AFT_SUPP_AMT THEN F_GET_D_CURRENCY((AFT_SUPP_AMT - BEF_SUPP_AMT),0)||' (▲)'                                                                                     \n");
//			sqlstr.append("       ELSE '0'                                                                                                                                                                    \n");
//			sqlstr.append("       END                                                                                                                                                                                 \n");
//			sqlstr.append("       END SUPP_AMT_NM                                                                                                                                                                     \n");
			sqlstr.append("       ,CASE WHEN NVL(AFT_BUG_AMT,0) > 0THEN '예산대비: '||F_GET_D_CURRENCY(ROUND(AFT_SUPP_AMT_E / AFT_BUG_AMT * 100,1),1)||'%'                                                                                                                 \n");
			sqlstr.append("       END SUPP_AMT_RATE                                                                                                                                                                   \n");
			sqlstr.append("       ,BEF_ETC                                                                                                                                                                            \n");
			sqlstr.append("       ,AFT_ETC                                                                                                                                                                            \n");
			sqlstr.append("       ,CHG_ETC                                                                                                                                                                            \n");
			sqlstr.append("       ,CON_DATE, START_WORK_DATE                                                                                                                                       			\n");
			sqlstr.append("       ,AFT_CONT_DATE                                                                                                                                       		\n");
			sqlstr.append("       ,CONT_DATE_NM                                                                                                                                                                   	\n");
			sqlstr.append("       ,REMARK                                                                                                                                                                         	\n");
			sqlstr.append("       ,SP_REMARK                                                                                                                                                                      	\n");
			sqlstr.append("       ,SUB_CONT_ID                                                                                                                                                                      	\n");
			sqlstr.append("       ,SUB_CONT_REQ_ID                                                                                                                                                                       	\n");
			sqlstr.append("       ,SUB_CONT_MOD_ID                                                                                                                                                                       	\n");
			sqlstr.append("       ,DIGIT                                                                                                                                                                       	\n");
			sqlstr.append("       ,MOD_NO                                                                                                                                                                       	\n");
			sqlstr.append("       ,CURRENCY_GB                                                                                                                                                                        \n");
			sqlstr.append("       ,WORK_PLACE                                                                                                                                                                         \n");
			sqlstr.append("       ,BEF_CONT_DATE                                                                                                                                                                      \n");
			sqlstr.append("       ,PO_PRC                                                                                                                                                                       \n");
			sqlstr.append("       ,RES_PRC                                                                                                                                                                       \n");
			sqlstr.append("   FROM(                                                                                                                                                                                   \n");
			sqlstr.append("           SELECT (SELECT DEPART_DEPT_NM FROM PUB0120T WHERE ACCT_ID = '"+acct_id+"') DEPART_DEPT_NM -- 요청부서                                                                                   \n");
			sqlstr.append("       ,A.DEALER_NM -- 사업주명                                                                                                                                            \n");
			sqlstr.append("       ,C.MEMBER_NAME S_MEMBER_NAME -- 공급자                                                                                                                                              \n");
			sqlstr.append("       ,A.BUILD_NAME  AS CST_DOC_NAME-- 계약명                                                                                                                                             \n");
			sqlstr.append("       ,TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS CRE_DATE -- 작성일                                                                                                                               \n");
			sqlstr.append("       ,A.CON_DOC_NO               AS ORD_DOC_NO                                                                                                                                           \n");
			sqlstr.append("       ,A.DEPT_CD                                                                                                                                                                          \n");
			sqlstr.append("       ,A.CON_CUR_CD          AS CURRENCY_GB                                                                                                                                               \n");
			sqlstr.append("       ,D.CHG_CODE06                                                                                                                                                                       \n");
			sqlstr.append("       ,D.CHG_CODE09                                                                                                                                                                       \n");
			sqlstr.append("       ,D.CHG_CODE01     AS DIGIT                                                                                                                                                          \n");
			sqlstr.append("       ,A.SUB_CONT_MOD_ID                                                                                                                                                                  \n");
			sqlstr.append("       ,A.MOD_NO                                                                                                                                                                           \n");
			sqlstr.append("       ,TO_CHAR(A.CON_DATE, 'YYYY-MM-DD') AS CON_DATE  , TO_CHAR(A.START_WORK_DATE, 'YYYY-MM-DD') AS START_WORK_DATE                                                                       \n");
			sqlstr.append("       ,E.SUB_CONT_REQ_ID                                                                                                                                                                 \n");
			sqlstr.append("       ,E.MOD_GB                                                                                                                                                                        \n");
			sqlstr.append("       ,E.BEF_CONT_AMT                                                                                                                                                                  \n");
			sqlstr.append("       ,E.AFT_CONT_AMT                                                                                                                                                                  \n");
			sqlstr.append("       ,E.CHG_CONT_AMT                                                                                                                                                                  \n");
			sqlstr.append("       ,E.BEF_BUG_AMT                                                                                                                                                                   \n");
			sqlstr.append("       ,E.AFT_BUG_AMT                                                                                                                                                                   \n");
			sqlstr.append("       ,E.CHG_BUG_AMT                                                                                                                                                                   \n");
			sqlstr.append("       ,ROUND(E.AFT_BUG_AMT/E.AFT_CONT_AMT*100,1) AS CHG_BUG_RATE                                                                                                                       \n");
			sqlstr.append("       ,E.BEF_SUPP_AMT                                                                                                                                                                  \n");
			sqlstr.append("       ,E.AFT_SUPP_AMT, E.AFT_SUPP_AMT * NVL(F.EXCHANGE,1) AFT_SUPP_AMT_E                                                                                                               \n");
			//sqlstr.append("       ,E.AFT_SUPP_AMT, E.AFT_SUPP_AMT AS AFT_SUPP_AMT_E                                                                                                                                \n");
			sqlstr.append("       ,E.CHG_SUPP_AMT                                                                                                                                                                  \n");
			sqlstr.append("       ,ROUND(E.AFT_SUPP_AMT/E.AFT_CONT_AMT*100,1) AS CHG_SUPP_RATE                                                                                                                     \n");
			sqlstr.append("       ,TO_CHAR(TO_DATE(E.BEF_CONT_DATE), 'YYYY-MM-DD') AS BEF_CONT_DATE                                                                                                               \n");
			sqlstr.append("       ,TO_CHAR(TO_DATE(E.AFT_CONT_DATE), 'YYYY-MM-DD') AS AFT_CONT_DATE                                                                                                               \n");
			sqlstr.append("       ,CASE WHEN TO_DATE(E.BEF_CONT_DATE) IS NOT NULL AND TO_DATE(E.AFT_CONT_DATE) IS NOT NULL THEN                                                                                     \n");
			sqlstr.append("        CASE WHEN TO_DATE(E.BEF_CONT_DATE) - TO_DATE(E.AFT_CONT_DATE) < 0 THEN '연장('||TO_NUMBER(TO_DATE(E.AFT_CONT_DATE) - TO_DATE(E.BEF_CONT_DATE)  )||'일)'                          \n");     
			sqlstr.append("        WHEN TO_DATE(E.BEF_CONT_DATE) - TO_DATE(E.AFT_CONT_DATE) > 0 THEN '단축('||TO_NUMBER(TO_DATE(E.BEF_CONT_DATE) - TO_DATE(E.AFT_CONT_DATE) )||'일)'                                \n");     
			sqlstr.append("        ELSE '(변경없음)'                                                                                                                                                                     \n");
			sqlstr.append("        END                                                                                                                                                                              \n");
			sqlstr.append("        END CONT_DATE_NM                                                                                                                                                                 \n");
			sqlstr.append("       ,E.BEF_ETC                                                                                                                                                                       \n");
			sqlstr.append("       ,E.AFT_ETC                                                                                                                                                                       \n");
			sqlstr.append("       ,E.CHG_ETC                                                                                                                                                                       \n");
			sqlstr.append("       ,E.REMARK                                                                                                                                                                        \n");
			sqlstr.append("       ,E.SP_REMARK                                                                                                                                                                     \n");
			sqlstr.append("       ,A.SUB_CONT_ID                                                                                                                                                                    \n");
			sqlstr.append("       ,NVL(F.EXCHANGE, 1) EXCHANGE                                                                                                                                                        \n");
			sqlstr.append("       ,A.WORK_PLACE                                                                                                                                                                       \n");
			sqlstr.append("       ,E.PO_PRC                                                                                                                                                                       \n");
			sqlstr.append("       ,E.RES_PRC                                                                                                                                                                       \n");
			sqlstr.append("       FROM SUB2000T A                                                                                                                                                                     \n");
			sqlstr.append("       ,CONT_OUT_SUPP B                                                                                                                                                                    \n");
			sqlstr.append("       ,CONT_OUT_SUPP C                                                                                                                                                                    \n");
			sqlstr.append("       ,(SELECT CHG_CODE06, CHG_CODE09 , DETAILCD , CHG_CODE01  FROM EBID_COM911T WHERE BASECD = '911' ) D                                                                                \n");
			sqlstr.append("       ,SUB3000T E  , BID_SUPP F             \n");//2016.9.26 외주 외화계약 환율적용 
			sqlstr.append("       WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                                                                                                                                         \n");
			sqlstr.append("       AND A.SUB_CONT_MOD_ID = C.SUB_CONT_MOD_ID                                                                                                                                           \n");
			sqlstr.append("       AND A.SUB_CONT_MOD_ID = E.SUB_CONT_MOD_ID                                                                                                                                           \n");
			sqlstr.append("       AND A.CON_CUR_CD = D.DETAILCD                                                                                                                                                       \n");
			sqlstr.append("       AND B.SIGN_SEQ = 1                                                                                                                                                                  \n");
			sqlstr.append("       AND C.SIGN_SEQ = 2                                                                                                                                                          \n");
			sqlstr.append("       AND A.BID_NO = F.BID_NO(+) AND A.BID_DEG = F.BID_DEG(+) AND F.SUCC_YN(+) =  'Y'                                                                                                                                                              \n");
			sqlstr.append("       AND A.SUB_CONT_MOD_ID =  '"+sign_cont_mod_id+"'                                                                                                                                     \n");
			sqlstr.append("   )                                                                                                                                                                                       \n");
			
			search.setStatement(sqlstr.toString());                                                                                                                                                 
			listRowSet = search.execute();                                                                                                                                                     
			//WAFLogger.debug("size : " + listRowSet.size());
			rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
			
		} catch (Exception e) {
			WAFLogger.error(this.getClass().getName()
					+ " --> getBidMaster() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return null;
		} finally {
			if (rowObject == null)
				rowObject = new ValueObject();
			return rowObject;
		}
	}
	
	
    /** 첨부서류조회 
     * @return
     * @throws Exception
     */
    public RowSet getFileListRowSet(String id, String code) throws Exception {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sqlstr = new StringBuffer();
        try {

        	sqlstr.append(" SELECT DISTINCT  \n");
        	sqlstr.append("        A.SORT_NO,						 \n");
        	sqlstr.append("        A.DETAILCD,						 \n");
        	sqlstr.append(" 	   A.DETAILNM,								 \n");
        	sqlstr.append(" 	   A.DETAILSORTNM,							 \n");
        	sqlstr.append(" 	   B.ID AS DOC_CD,							 \n");
        	sqlstr.append(" 	   '" + id + "' AS FILE_GB,	 \n");
        	sqlstr.append(" 	   NVL(B.ID, A.DETAILCD) AS ID,	 \n");
        	sqlstr.append(" 	   A.CHG_CODE03 AS EDIT_YN	 \n");
        	sqlstr.append("	FROM EBID_COM911T A, PUB0510T B					 \n");
        	sqlstr.append("	WHERE A.BASECD = 'SUB_REQFILE_GB'	 \n");
        	sqlstr.append("	AND A.CHG_CODE02 = 'Y'			 \n");
        	sqlstr.append("	AND A.DETAILCD = B.ID(+)						 \n");
        	sqlstr.append("	AND B.FILE_GB(+) = '" + id + "'	 \n");
        	sqlstr.append("UNION ALL \n");
        	sqlstr.append("SELECT SORT_NO, \n");
        	sqlstr.append("       DETAILCD, \n");
        	sqlstr.append("       DETAILNM, \n");
        	sqlstr.append("       DETAILSORTNM,  \n");
        	sqlstr.append("       TO_NUMBER(DETAILCD) AS DOC_CD, \n");
        	sqlstr.append(" 	  '' AS FILE_GB,	 \n");
        	sqlstr.append(" 	  TO_NUMBER(DETAILCD) AS ID,	 \n");
        	sqlstr.append(" 	  CHG_CODE03 AS EDIT_YN	 \n");
        	sqlstr.append("  FROM EBID_COM911T      \n");
        	sqlstr.append(" WHERE BASECD = 'SUB_REQFILE_GB' \n");
        	sqlstr.append("   AND DETAILCD = '160'	 \n");
        	sqlstr.append("ORDER BY SORT_NO, DETAILCD \n");
    		
            search.setStatement(sqlstr.toString());
            
            listRowSet = search.execute();
            WAFLogger.debug(sqlstr.toString());
            
           
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getListRowSet() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
            if( listRowSet == null ) listRowSet = new RowSetImpl();
           
        }
        
        return listRowSet;
    }

	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 SUB_CONT_REQ_ID
	 * @return
	 */
	private ValueObject getSignInfo(String SUB_CONT_REQ_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, 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, 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, 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");*/
        	
			sqlstr.append("   SELECT                                                                                            \n");
			sqlstr.append("   	    MAX(B.SIGN_INTERFACE_ID) AS SIGN_INTERFACE_ID                                               \n");
			sqlstr.append("   	   ,B.SIGN_CLASS_CD                                                                           	\n");
			sqlstr.append("   	   ,A.SUB_CONT_REQ_ID                                                                           \n");
			sqlstr.append("   	   ,A.SUB_CONT_MOD_ID                                                                           \n");
			sqlstr.append("   	   ,MAX(B.DOC_ID) AS DOC_ID                                                                     \n");
			sqlstr.append("   	   ,MAX(TO_CHAR(B.CRE_DATE,'YYYYMMDD')) YEAR                                                     \n");
			sqlstr.append("   	   ,B.SIGN_DECI_CLASS                                                                     		\n");
			sqlstr.append("   FROM SUB3000T A,                                                                                  \n");
			sqlstr.append("        SIGN001T B                                                                             \n");
			sqlstr.append("   WHERE A.SUB_CONT_REQ_ID = REGEXP_REPLACE(B.KEY_COL1,'[^0-9]')                                     \n");
			sqlstr.append("   AND   A.SUB_CONT_MOD_ID = REGEXP_REPLACE(B.KEY_COL2,'[^0-9]')                                     \n");
			sqlstr.append("   AND   B.SIGN_CLASS_CD = 'ocreqD'                                                   				\n");
			sqlstr.append("   AND   B.SIGN_DECI_CLASS = '14'                                                   					\n");
			sqlstr.append("   AND   A.SUB_CONT_REQ_ID = '"+SUB_CONT_REQ_ID+"'                                                   \n");
			//sqlstr.append("   AND   B.DOC_ID IS NOT NULL                                                                        \n");
			sqlstr.append("   GROUP BY B.SIGN_CLASS_CD,A.SUB_CONT_REQ_ID,                                                      	\n");
			sqlstr.append("   			A.SUB_CONT_MOD_ID,B.SIGN_DECI_CLASS                                                     \n");
        	
        	search.setStatement(sqlstr.toString());
            System.out.println(sqlstr.toString());
        	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;
	}
	
	
}
