/**
 *
 * @(#)author  [ok]
 * @(#)version 1.0
 * @(#)date    2014. 07. 24
 * @(#)since   JDK 1.7.2
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of udapsoft, Inc.
 */
 package kr.co.udapsoft.ebid.buyer.payment.purchase.biz;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.config.WAFConfig;
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.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.StringUtil;
import kr.co.hsnc.common.util.ValueObject;

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 PurchaseRequestListBiz extends Biz {
	public PurchaseRequestListBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
		
		
		WAFLogger.debug("전자조달.대금관리.구매.대금신청 신규등록 프로세스처리.[PurchaseRequestListBiz]");

		if(storage.compareEvent("")){
			
		}else if(storage.compareEvent("GET_LIST")){
			
			WAFLogger.debug("[PurchaseRequestListBiz] GET_LIST");
			WAFLogger.debug(params.get("searchDeptCd"));
			
			params.set("sheet1", getPrePaymentList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));

		}else if(storage.compareEvent("SAVE_DATA")){
			
			boolean success = false;
			String[] status = super.gets("sheet1.STATUS");

			try {
				super.conn.begin();
				
				
				for (int i = 0; i < status.length; i++) {
					
					//구매_대금지금신청 SEQ채번
					params.set("seq", getSeq(super.conn.getConnection()));
					saveData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));	
				}
				
				super.storage.setSuccess(true);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		} 
	}
	
    /**
     *@param ValueObject
     *@return RowSet
     *기   능 : 대금신청 대상 리스트 조회
     *@author OK
     *@version 2014. 7. 28.
     *@since JDK1.7
     */
    public RowSet getPrePaymentList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        StringBuffer sql = new StringBuffer();
        
        try {

        	if(params.get("pageDiv").equals("1")){	//1:조달
            	sqlstr += " SELECT DEPT_CD, DEPT_NAME, CST_DOC_NAME, CORP_KOR, CURRENCY_GB, DEPOSIT_SEQ, ORD_SUPPLY_AMT, CUR_ACMP_APPLYAMT, CUR_BEF_ACMP_SUPPLYAMT, REMAINDER_ACMP_SUPPLYAMT, CUR_ACMP_PERCENT, PUR_CONT_ID, PUR_CONT_MOD_ID, DIV, DEPOSIT_DIV	\n"
            			+ " FROM PUR1100T_NEW_V																																																					\n"        			
            			+ " WHERE 1=1																																																							\n"        			
            			+ " AND DIV NOT IN ('3')																																																				\n"        			
            			;
            	if(!"".equals(params.get("searchDeptCd"))){
            		sqlstr += " AND DEPT_CD = '"+Params.get("searchDeptCd")+"'																						\n";
        		}
        	}else{	//2:포탈
            	sqlstr += " SELECT A.DEPT_CD, A.DEPT_NAME, A.CST_DOC_NAME, A.CORP_KOR, A.CURRENCY_GB, A.DEPOSIT_SEQ, A.ORD_SUPPLY_AMT, A.CUR_ACMP_APPLYAMT, A.CUR_BEF_ACMP_SUPPLYAMT, A.REMAINDER_ACMP_SUPPLYAMT, A.CUR_ACMP_PERCENT, A.PUR_CONT_ID, A.PUR_CONT_MOD_ID, A.DIV, A.DEPOSIT_DIV	\n"
            			+ " FROM PUR1100T_NEW_V A, CONT_PUR_SUPP C1																																																								\n"        			
            			+ " WHERE C1.SIGN_SEQ = 2																																																												\n"        			
            			+ " AND A.PUR_CONT_MOD_ID = C1.PUR_CONT_MOD_ID																																																							\n"        			
            			+ " AND A.DIV NOT IN ('3')																																																												\n"
            			;
            	if(!"".equals(params.get("userCorpId"))){
            		sqlstr += " AND C1.CORP_ID  = '"+Params.get("userCorpId")+"'																\n";
            	}        		
            	if(!"".equals(params.get("searchCstNm"))){
            		sqlstr += " AND upper(A.CST_DOC_NAME) LIKE upper('"+Params.get("searchCstNm")+"')||'%'										\n";
            	}
        	}
            search.setStatement(sqlstr);
            WAFLogger.debug(sqlstr);
            listRowSet = search.execute();
            
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getPrePaymentList() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            if( listRowSet == null ) listRowSet = new RowSetImpl();
            return listRowSet;
        }
    }

    public RowSet getDept() {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        StringBuffer sql = new StringBuffer();
        
        try {
           	sqlstr += " SELECT  DEPT_CD ,A.DEPT_NM ,A.AC_UNIT_CD ,F_GET_CODE_NAME('101', A.AC_UNIT_CD) AS AC_UNIT_NM			\n"
        			+ " 		,A.STD_CURRENCY_CD ,F_GET_CURRENCY_DIGIT(A.STD_CURRENCY_CD) AS STD_DIGIT						\n"        			
           			+ " FROM    ACZ10100V A																						\n"        			
           			+ " WHERE	SUBSTR(DEPT_CD,1,4) = '"+params.get("searchDeptCd")+"'											\n"     			
           			+ " AND A.AC_UNIT_CD != '50' 																				\n"        			
           			+ "  AND	DEPT_CD != '0000'																				\n"        			
           			;

            search.setStatement(sqlstr);
            WAFLogger.debug(sqlstr);
            listRowSet = search.execute();
            
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getPrePaymentList() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            if( listRowSet == null ) listRowSet = new RowSetImpl();
            return listRowSet;
        }
    }
    
    /**
     *@param ValueObject
     *@return RowSet
     *기   능 : 구매)대금지급 
     *@author OK
     *@version 2014. 7. 28.
     *@since JDK1.7
     */
	private void saveData(Connection connection, ValueObject sqlParams) throws Exception {
		
		PreparedStatement pstmt = null;
		Persistent persistent_i	= new PersistentImpl(connection);
		
		try {
			
			StringBuffer sql = new StringBuffer();

			sql.append("	INSERT INTO PUR1100T											\n ");
			sql.append("	(																\n ");
			sql.append("		PAYREQ_ID               --지급신청ID							\n ");
			sql.append("		,REQ_DATE               --신청서작성일자						\n ");
			sql.append("		,PAY_DIV                --대금구분							\n ");
			//sql.append("		,REQ_CHARGE_ID          --신청서작성자ID						\n ");
			//sql.append("		,COOP_DEPT_CD           --협조부서코드						\n ");
			sql.append("		,ORD_AMT                --총계약금							\n ");
			sql.append("		,DEPOSIT_CD             --지급구분							\n ");
			sql.append("		,DEPOSIT_SEQ            --지급차수							\n ");
			//sql.append("		,DEPOSIT_RESN           --지급사유							\n ");
			//sql.append("		,ACMP_APPLYRATE         --기성고적용비율						\n ");
			sql.append("		,BEF_ACMP_AMT           --전회청구_합계						\n ");
			sql.append("		,BEF_ACMP_APPLYAMT   	--전회청구_공급가액					\n ");
			sql.append("		,BEF_ACMP_VAT           --전회청구_부가세						\n ");
			sql.append("		,BEF_ACMP_PERCENT     	--전회청구_지급율						\n ");
			sql.append("		,BEF_PREPAY_AMT         --전회선급금반제_합계					\n ");
			sql.append("		,BEF_PREPAY_APPLYAMT    --전회선급금반제_공급가액				\n ");
			sql.append("		,BEF_PREPAY_VAT         --전회선급금반제_부가세				\n ");
			sql.append("		,BEF_PREPAY_PERCENT     --전회선급금반제_지급율				\n ");
			sql.append("		,PAYED_AMT              --전회실지급액_합계					\n ");
			sql.append("		,PAYED_APPLYAMT         --전회실지급액_공급가액				\n ");
			sql.append("		,PAYED_VAT              --전회실지급액_부가세					\n ");
			//sql.append("		,PAYED_PERCENT          --전회실지급액_지급율					\n ");
			sql.append("		,CUR_ACMP_APPLYAMT      --금회청구_공급가액					\n ");
			sql.append("		,CUR_ACMP_VAT           --금회청구_부가세						\n ");
			sql.append("		,CUR_ACMP_AMT           --금회청구_합계						\n ");
			sql.append("		,CUR_ACMP_PERCENT       --금회청구_지급율						\n ");
			sql.append("		,CUR_PREPAY_APPLYAMT    --금회선급금반제_공급가액				\n ");
			sql.append("		,CUR_PREPAY_VAT         --금회선급금반제_부가세				\n ");
			sql.append("		,CUR_PREPAY_DEDUCTAMT  	--금회선급금반제_합계					\n ");
			sql.append("		,CUR_PREPAY_PERCENT     --금회선급금반제_지급율				\n ");
			sql.append("		,CUR_REQ_AMT            --금회실지급액_공급가액				\n ");
			sql.append("		,CUR_REQ_VATAMT         --금회실지급액_부가세					\n ");
			sql.append("		,CUR_REQ_APPLYAMT       --금회실지급액_합계					\n ");
			//sql.append("		,CUR_REQ_PERCENT        --금회실지급액_비율					\n ");
			sql.append("		,REQ_STATUS													\n ");
			sql.append("		,TRANS_CD													\n ");
			sql.append("		,PUR_CONT_ID												\n ");
			sql.append("		,CRE_BY														\n ");
			sql.append("		,CRE_DATE													\n ");
			sql.append("		,CUR_BEF_ACMP_SUPPLYAMT										\n ");
			sql.append("		,CUR_BEF_ACMP_VAT											\n ");
			sql.append("		,CUR_BEF_ACMP_AMT											\n ");
			sql.append("		,CUR_BEF_ACMP_PERCENT										\n ");
			sql.append("		,CUR_BEF_PREPAY_SUPPLYAMT									\n ");
			sql.append("		,CUR_BEF_PREPAY_VAT											\n ");
			sql.append("		,CUR_BEF_PREPAY_AMT											\n ");
			sql.append("		,CUR_BEF_PREPAY_PERCENT										\n ");
			sql.append("		,CUR_PAYED_SUPPLYAMT										\n ");
			sql.append("		,CUR_PAYED_VAT												\n ");
			sql.append("		,CUR_PAYED_AMT												\n ");
			//sql.append("		,CUR_PAYED_PERCENT											\n ");
			sql.append("		,REMAINDER_ACMP_SUPPLYAMT									\n ");
			sql.append("		,REMAINDER_ACMP_VAT											\n ");
			sql.append("		,REMAINDER_ACMP_AMT											\n ");
			sql.append("		,REMAINDER_ACMP_PERCENT										\n ");
			sql.append("		,REMAINDER_PREPAY_SUPPLYAMT									\n ");
			sql.append("		,REMAINDER_PREPAY_VAT										\n ");
			sql.append("		,REMAINDER_PREPAY_AMT										\n ");
			sql.append("		,REMAINDER_PREPAY_PERCENT									\n ");
			//sql.append("		,REMAINDER_SUPPLYAMT										\n ");
			//sql.append("		,REMAINDER_VAT												\n ");
			//sql.append("		,REMAINDER_AMT												\n ");
			//sql.append("		,REMAINDER_PERCENT											\n ");
			sql.append("		,CUR_ACMP_APPLYAMT_KRW											\n ");
			//sql.append("		,CUR_ACMP_EXCRATE											\n ");
			sql.append("		,ACCT_GB													\n ");
			sql.append("		,PAY_GROUP													\n ");
			sql.append("		,GUA_GB														\n ");
			sql.append("		,PAYMENT_TERMS												\n ");
			sql.append("		,PAY_ITEM													\n ");
			sql.append("		,COST_CODE													\n ");
			sql.append("		,COST_TYPE													\n ");
			sql.append("		,PUR_CONT_MOD_ID											\n ");
			sql.append("		,TAX_METHOD_CD												\n ");
			sql.append("		,TAX_DY														\n ");
			sql.append("		,TAX_CHARG_NM												\n ");
			sql.append("		,TAX_CHARG_HP												\n ");
			sql.append("		,TAX_CHARG_EMAIL											\n ");
			sql.append("		,F_TAX_DY													\n ");
			sql.append("		,F_CON_SUPPLY_AMT											\n ");
			sql.append("		,SUPP_DIRECT_YN  											\n "); //2016.6.15 하도급직불 
			sql.append("		) (															\n ");
			sql.append("		SELECT														\n ");
			sql.append("				? AS PAYREQ_ID										\n ");
			sql.append("				,TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS REQ_DATE			\n ");
			sql.append("				,? AS PAY_DIV										\n ");
			sql.append("				,ORD_AMT											\n ");
			sql.append("				,DEPOSIT_CD AS DEPOSIT_CD							\n ");
			sql.append("				,? AS DEPOSIT_SEQ									\n ");
			sql.append("				,BEF_ACMP_AMT										\n ");
			sql.append("				,BEF_ACMP_APPLYAMT									\n ");
			sql.append("				,BEF_ACMP_VAT										\n ");
			sql.append("				,BEF_ACMP_PERCENT									\n ");
			sql.append("				,BEF_PREPAY_AMT										\n ");
			sql.append("				,BEF_PREPAY_APPLYAMT								\n ");
			sql.append("				,BEF_PREPAY_VAT										\n ");
			sql.append("				,BEF_PREPAY_PERCENT									\n ");
			sql.append("				,PAYED_AMT											\n ");
			sql.append("				,PAYED_APPLYAMT										\n ");
			sql.append("				,PAYED_VAT											\n ");
			sql.append("				,CUR_ACMP_APPLYAMT									\n ");
			sql.append("				,CUR_ACMP_VAT										\n ");
			sql.append("				,CUR_ACMP_AMT										\n ");
			sql.append("				,CUR_ACMP_PERCENT									\n ");
			sql.append("				,CUR_PREPAY_APPLYAMT								\n ");
			sql.append("				,CUR_PREPAY_VAT										\n ");
			sql.append("				,CUR_PREPAY_DEDUCTAMT								\n ");
			sql.append("				,CUR_PREPAY_PERCENT									\n ");
			sql.append("				,CUR_REQ_AMT										\n ");
			sql.append("				,CUR_REQ_VATAMT										\n ");
			sql.append("				,CUR_REQ_APPLYAMT									\n ");
			sql.append("				,'00' AS REQ_STATUS									\n ");
			sql.append("				,'N' AS TRANS_CD									\n ");
			sql.append("				,? AS PUR_CONT_ID									\n ");
			sql.append("				,? AS CRE_BY										\n ");
			sql.append("				,SYSDATE											\n ");
			sql.append("				,CUR_BEF_ACMP_SUPPLYAMT								\n ");
			sql.append("				,CUR_BEF_ACMP_VAT									\n ");
			sql.append("				,CUR_BEF_ACMP_AMT									\n ");
			sql.append("				,CUR_BEF_ACMP_PERCENT								\n ");
			sql.append("				,CUR_BEF_PREPAY_SUPPLYAMT							\n ");
			sql.append("				,CUR_BEF_PREPAY_VAT									\n ");
			sql.append("				,CUR_BEF_PREPAY_AMT									\n ");
			sql.append("				,CUR_BEF_PREPAY_PERCENT								\n ");
			sql.append("				,CUR_PAYED_SUPPLYAMT								\n ");
			sql.append("				,CUR_PAYED_VAT										\n ");
			sql.append("				,CUR_PAYED_AMT										\n ");
			sql.append("				,REMAINDER_ACMP_SUPPLYAMT							\n ");
			sql.append("				,REMAINDER_ACMP_VAT									\n ");
			sql.append("				,REMAINDER_ACMP_AMT									\n ");
			sql.append("				,REMAINDER_ACMP_PERCENT								\n ");
			sql.append("				,REMAINDER_PREPAY_SUPPLYAMT							\n ");
			sql.append("				,REMAINDER_PREPAY_VAT								\n ");
			sql.append("				,REMAINDER_PREPAY_AMT								\n ");
			sql.append("				,REMAINDER_PREPAY_PERCENT							\n ");
			sql.append("				,CUR_ACMP_APPLYAMT_KRW								\n ");
			sql.append("				,ACCT_GB											\n ");
			sql.append("				,PAY_GROUP											\n ");
			sql.append("				,GUA_GB												\n ");
			sql.append("				,PAYMENT_TERMS										\n ");
			sql.append("				,PAY_ITEM											\n ");
			sql.append("				,COST_CODE											\n ");
			sql.append("				,COST_TYPE											\n ");
			sql.append("				,PUR_CONT_MOD_ID									\n ");
			sql.append("				,TAX_METHOD_CD										\n ");
			sql.append("				,TAX_DY												\n ");
			sql.append("				,TAX_CHARG_NM										\n ");
			sql.append("				,TAX_CHARG_HP										\n ");
			sql.append("				,TAX_CHARG_EMAIL									\n ");
			sql.append("				,F_TAX_DY											\n ");
			sql.append("				,F_CON_SUPPLY_AMT									\n ");
			sql.append("				,'N'							             		\n ");
			sql.append("		FROM PUR1100T_INSERT_V										\n ");				
			sql.append("		WHERE PUR_CONT_ID  = ?										\n ");
			sql.append("		)															\n ");			
			
			
			if(sqlParams.get("DIV").equals("1")){	//1:토건, 2:플랜트, 3:설치
				//아이템 내역 조회
				RowSet itemListResult = getitemList(sqlParams.get("PUR_CONT_ID"));
				
				persistent_i.setStatement(sql.toString());
				WAFLogger.debug(sql.toString());
				
				persistent_i.addParameter(params.get("seq"));
				persistent_i.addParameter("1");
				persistent_i.addParameter(sqlParams.get("DEPOSIT_SEQ"));
				persistent_i.addParameter(sqlParams.get("PUR_CONT_ID"));
				persistent_i.addParameter(params.getInt("USER_ID"));
				persistent_i.addParameter(sqlParams.get("PUR_CONT_ID"));
				WAFLogger.debug(persistent_i.getParameters());
	    		
				persistent_i.execute();
				persistent_i.clearParameters();	
			
				
				for(int i=0; i<itemListResult.size(); i++){
					
					Row itemRow = itemListResult.getRow(i);
					//조달 내역테이블에 인서트
					//updatePayReqId(connection, itemRow);
					insPayItemList(connection, sqlParams, itemRow);
				}
			
				createPDF(sqlParams.get("PUR_CONT_ID"));
				
			}else if(sqlParams.get("DIV").equals("2")){	//1:토건, 2:플랜트, 3:설치
				//아이템 내역 조회
				//RowSet plantItemListResult = getPlantitemList(sqlParams.get("PUR_CONT_ID"));
				RowSet itemListResult = getitemList(sqlParams.get("PUR_CONT_ID"));

				
				persistent_i.setStatement(sql.toString());
				WAFLogger.debug(sql.toString());
				
				persistent_i.addParameter(params.get("seq"));
				persistent_i.addParameter("1");
				persistent_i.addParameter(sqlParams.get("DEPOSIT_SEQ"));
				persistent_i.addParameter(sqlParams.get("PUR_CONT_ID"));
				persistent_i.addParameter(params.getInt("USER_ID"));
				persistent_i.addParameter(sqlParams.get("PUR_CONT_ID"));
				WAFLogger.debug(persistent_i.getParameters());
	    		
				persistent_i.execute();
				persistent_i.clearParameters();				
				
				//입고테이블
				//SHIPPING_LIST_ID 및 WAREHOUSE_ID 의 RECEIPT_NO update
				getShippId(connection, sqlParams);
			
				for(int i=0; i<itemListResult.size(); i++){
					
					Row itemRow = itemListResult.getRow(i);
					//조달 내역테이블에 인서트
					insPayItemList(connection, sqlParams, itemRow);
				}
				
				createPDF(sqlParams.get("PUR_CONT_ID"));
				
			}else if(sqlParams.get("DIV").equals("3")){	//1:토건, 2:플랜트, 3:설치

				persistent_i.setStatement(sql.toString());
				WAFLogger.debug(sql.toString());
				
				persistent_i.addParameter(params.get("seq"));
				persistent_i.addParameter("2");
				persistent_i.addParameter(sqlParams.get("DEPOSIT_SEQ"));
				persistent_i.addParameter(sqlParams.get("PUR_CONT_ID"));
				persistent_i.addParameter(params.getInt("USER_ID"));
				persistent_i.addParameter(sqlParams.get("PUR_CONT_ID"));
				WAFLogger.debug(persistent_i.getParameters());
	    		
				persistent_i.execute();
				persistent_i.clearParameters();				
			
				//대금지불계획에 대금플래그 Y업데이트
				updatePayplan(connection, sqlParams);
				
			}
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} 
	} 

    /**
	 * Seq 채번하기 (Persisitent 이용)
	 * @param 	
	 * @return	ValueObject
	 */
	private String getSeq(Connection conn) throws Exception {
	
		Persistent persistent_q = null;
		
		String resultSeq = null;
		ValueObject row = null;
		String sqlstr = "";
		
		try{
			persistent_q = new PersistentImpl(conn);
			sqlstr = "SELECT PUR1100T_S.NEXTVAL SEQ FROM DUAL";
			persistent_q.setStatement(sqlstr); 
			row = RowSetUtility.getValueObject(persistent_q.query().getRow(0));
			resultSeq = row.get("seq");
			
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getSeq() : " + e.getMessage());
//			WAFLogger.debug(e.getMessage());
			WAFLogger.error(e);
			throw e;
            
		}finally{
			if(resultSeq == null) resultSeq = "";
		}
		
		return resultSeq;
	} 
	
	/**
	 * 
	 * @param 	
	 * @return	ValueObject
	 */
	private void getShippId(Connection connection, ValueObject vo) throws Exception {
		
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		String sqlstr = "";
		String resultShippingListId = "";
		String resultWarehouseId = "";
		
		Persistent persistent1 = new PersistentImpl(connection);
		StringBuffer updateSqlstr1 = null;
		Persistent persistent2 = new PersistentImpl(connection);
		StringBuffer updateSqlstr2 = null;
		
		try {
			
			updateSqlstr1 = new StringBuffer();
			updateSqlstr1.append(" UPDATE  ETECEPC.FM_SHIPPING_LIST SET					\n");
			updateSqlstr1.append("         RECEIPT_NO			= ?						\n");
			updateSqlstr1.append(" WHERE   SHIPPING_LIST_ID	    = ?						\n");
			updateSqlstr1.append("   and   RECEIPT_NO is null   						\n");//2016.08.31추가. 

			updateSqlstr2 = new StringBuffer();
			updateSqlstr2.append(" UPDATE  ETECEPC.FM_WAREHOUSE SET						\n");
			updateSqlstr2.append("         RECEIPT_NO			= ?						\n");
			updateSqlstr2.append(" WHERE   WAREHOUSE_ID		    = ?						\n");
			
			sqlstr += " 																												\n"
					+ " SELECT DISTINCT																									\n"
					+ " 		T1.SHIPPING_LIST_ID																						\n"
					+ " 		,T3.WAREHOUSE_ID																						\n"
					+ " FROM PUR1000T A, ETECEPC.FM_SHIPPING_LIST T1, ETECEPC.FM_SHIPPING_DETAIL T2, ETECEPC.FM_WAREHOUSE T3			\n"
					+ " WHERE  A.ORD_DOC_NO = T1.ORDER_NO																				\n"
					+ " AND T1.ORDER_NO = T2.ORDER_NO																					\n"
					+ " AND T2.SHIPPING_DETAIL_ID = T3.SHIPPING_DETAIL_ID(+)															\n"
					+ " AND T1.RECEIPT_NO IS NULL                            \n" //2016.08.31추가. 분할입고되는 경우 전체 업데이트되는 오류.
					+ " AND A.PUR_CONT_ID = '"+vo.get("PUR_CONT_ID")+"'																	\n"
					;
			
			search.setStatement(sqlstr);
			WAFLogger.debug(sqlstr);
			listRowSet = search.execute();
			for(int i=0; i<listRowSet.size(); i++){

				ValueObject row = null;
				row = RowSetUtility.getValueObject(listRowSet.getRow(i));
				resultShippingListId = row.get("SHIPPING_LIST_ID");
				resultWarehouseId = row.get("WAREHOUSE_ID");
				
	            persistent1.setStatement(updateSqlstr1.toString());
	            persistent1.addParameter(params.get("seq"));
	            persistent1.addParameter(resultShippingListId);
	            persistent1.execute();
	            
	            persistent2.setStatement(updateSqlstr2.toString());
	            persistent2.addParameter(params.get("seq"));
	            persistent2.addParameter(resultWarehouseId);
		        persistent2.execute();
		        
		        persistent1.clearParameters();
		        persistent2.clearParameters();
			}
			
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getStockId() : " + e.getMessage());
//			WAFLogger.debug(e.getMessage());
			WAFLogger.error(e);
			throw e;
			
		}finally{
			//if(resultStockId == null) resultStockId = "";
		}
		
		//return resultStockId;
	}
	
	/**
	 * 
	 * @param 	
	 * @return	ValueObject
	 */
	private void updatePayplan(Connection connection, ValueObject vo) throws Exception {
		
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		String sqlstr = "";
		String resultPurContModId = "";
		String resultPaySeq = "";
		
		Persistent persistent1 = new PersistentImpl(connection);
		StringBuffer updateSqlstr1 = null;
		
		try {
			
			updateSqlstr1 = new StringBuffer();
			updateSqlstr1.append(" UPDATE  CONT_PUR_PAYPLAN		 SET					\n");
			updateSqlstr1.append("         PAY_YN				= 'Y' ,					\n");
			updateSqlstr1.append("         PAYREQ_ID			= ?						\n");
			updateSqlstr1.append(" WHERE   PUR_CONT_MOD_ID	    = ?						\n");
			updateSqlstr1.append(" AND     PAY_SEQ			    = ?						\n");
			
			sqlstr += " 																\n"
					+ " SELECT   A1.PUR_CONT_ID											\n"
					+ " 		,A1.PUR_CONT_MOD_ID										\n"
					+ " 		,A1.PAY_SEQ												\n"
					+ " FROM (SELECT A.*												\n"
					+ " 	  FROM CONT_PUR_PAYPLAN A									\n"
					+ " 	  	   ,(SELECT PUR_CONT_MOD_ID, MIN(PAY_SEQ) PAY_SEQ		\n"
					+ " 	    	 FROM CONT_PUR_PAYPLAN								\n"
					+ " 	         WHERE NVL(PAY_YN,'N') = 'N'						\n"
					+ " 	         AND PAY_DIV_CD != '01'								\n"
					+ " 	         GROUP BY PUR_CONT_MOD_ID) B						\n"
					+ " 	  WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID				\n"
					+ "       AND A.PAY_SEQ = B.PAY_SEQ) A1								\n"
					+ " WHERE A1.PUR_CONT_ID = '"+vo.get("PUR_CONT_ID")+"'				\n"
					;
			
			search.setStatement(sqlstr);
			WAFLogger.debug(sqlstr);
			listRowSet = search.execute();
			for(int i=0; i<listRowSet.size(); i++){
				
				ValueObject row = null;
				row = RowSetUtility.getValueObject(listRowSet.getRow(i));
				resultPurContModId = row.get("PUR_CONT_MOD_ID");
				resultPaySeq = row.get("PAY_SEQ");
				
				persistent1.setStatement(updateSqlstr1.toString());
				persistent1.addParameter(params.get("seq"));
				persistent1.addParameter(resultPurContModId);
				persistent1.addParameter(resultPaySeq);
				persistent1.execute();
				persistent1.clearParameters();
			
				//아이템 내역 조회
				//RowSet contPutItemResult = getContPutItem(resultPurContModId, resultPaySeq);
				RowSet itemListResult = getitemList(resultPurContModId);
				for(int j=0; i<itemListResult.size(); j++){
					
					Row itemRow = itemListResult.getRow(j);
					//조달 내역테이블에 인서트
					insPayItemList(connection, vo, itemRow);
				}
			}
			
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getStockId() : " + e.getMessage());
			WAFLogger.error(e);
			throw e;
			
		}finally{
			//if(resultStockId == null) resultStockId = "";
		}
		
		//return resultStockId;
	}
	
    /**
     *@param ValueObject
     *@return RowSet
     *기   능 : 대금신청 아이템 내역을 조회한다.
     *@author OK
     *@version 2014. 7. 28.
     *@since JDK1.7
     */
    public RowSet getitemList(String purContId) {
    	
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
        	sqlstr += " 																			\n"
        			+ " SELECT																		\n"
        			+ " 		A.MATERIAL_CD,														\n"
        			+ " 		A.STOCK_NO,															\n"
        			+ " 		A.PUR_CONT_ID,														\n"
        			+ " 		A.ITEM_QTY AS STOCK_QTY,											\n"
        			+ " 		A.ITEM_PRI AS STOCK_PRI,  -- pmm_payreq_item.item_pri				\n"
        			+ " 		A.ITEM_AMT AS STOCK_AMT,  -- pmm_payreq_item.item_amt				\n"
        			+ " 		A.EXC_PRI,    -- pmm_payreq_item.exc_pri							\n"
        			+ " 		A.EXC_AMT,    -- pmm_payreq_item.exc_amt							\n"
        			+ " 		A.GUBUN																\n"
        			+ " 		A.STOCK_DATE																\n"
        			+ " FROM  PMM_PAYREQ_ITEM_NEW_V A												\n"
        			+ " WHERE A.PUR_CONT_ID = '"+purContId+"'										\n"
        			;
        	search.setStatement(sqlstr);
        	WAFLogger.debug(sqlstr);
            listRowSet = search.execute();
            
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getitemList() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            if( listRowSet == null ) listRowSet = new RowSetImpl();
            return listRowSet;
        }
    }
    
    /**
     *@param ValueObject
     *@return RowSet
     *기   능 : 대금신청 아이템 내역을 조회한다.
     *@author OK
     *@version 2014. 7. 28.
     *@since JDK1.7
     */
    public RowSet getPlantitemList(String purContId) {
    	
    	Search search = new SearchImpl();
    	RowSet listRowSet = null;
    	String sqlstr = "";
    	
    	try {
    		sqlstr += " 																												\n"
    				+ " SELECT																											\n"
    				+ " 		STOCK_NO																								\n"
    				+ " 		,ITEM_NO AS MATERIAL_CD																					\n"
    				+ " 		,STOCK_QTY																								\n"
    				+ " 		,STOCK_PRI	AS STOCK_PRI	 																			\n"
    				+ " 		,STOCK_QTY * STOCK_PRI AS STOCK_AMT																		\n"
    				+ " 		,ITEM_PRI AS EXC_PRI																					\n"
    				+ " 		,ITEM_AMT AS EXC_AMT																					\n"
    				+ " 		,'B' AS GUBUN																							\n"
    				+ " FROM (																											\n"
    				+ " 		SELECT																									\n"
    				+ " 				DECODE(T1.FOREIGN_YN , 'D' , T2.MRR_NO, T2.SHIP_NO) AS STOCK_NO									\n"
    				+ " 				, T1.ITEM_NO       --ITEM_NO																	\n"
    				+ " 				, DECODE(T1.FOREIGN_YN , 'D' , T2.INPUT_QTY, T2.SHIP_QTY) AS STOCK_QTY      --입고수량			\n"
    				+ " 				, T1.EST_PRICE AS STOCK_PRI																		\n"
    				+ " 				, T1.PROJECT_CD    AS DEPT_CD         --PROJECT CD												\n"
    				+ " 				, (SELECT DISTINCT PUR_CONT_ID FROM pur1000T WHERE ORD_DOC_NO = T1.MNG_PO_NO)     PUR_CONT_ID	\n"
    				+ " 				,T1.MNG_PO_NO AS ORD_DOC_NO   --계약번호															\n"
    				+ " 				, T1.SPEC_DESC    --ITEM DESCRIPTION															\n"
    				+ " 				, DECODE(T1.FOREIGN_YN , 'D' , T2.SHIP_EXC_PRI, T2.INPUT_EXC_PRI) AS ITEM_PRI					\n"
    				+ " 				, DECODE(T1.FOREIGN_YN , 'D' , T2.SHIP_EXC_AMT, T2.INPUT_EXC_AMT) AS ITEM_AMT					\n"
    				+ " 		FROM ( --계약정보																							\n"
    				+ " 				SELECT  T1.CST_INFO_ID																			\n"
    				+ " 						,T1.MR_REQ_PO_NM																		\n"
    				+ " 						,T1.CST_DOC_NO																			\n"
    				+ " 						,T1.CORP_ID																				\n"
    				+ " 						,T1.MNG_PO_NO																			\n"
    				+ " 						,T1.REQ_PO_ID																			\n"
    				+ " 						,T1.PROJECT_CD																			\n"
    				+ " 						,T2.ITEM_NO																				\n"
    				+ " 						,T2.SPEC_DESC																			\n"
    				+ " 						,T1.FOREIGN_YN																			\n"
    				+ " 						,T2.EST_PRICE																			\n"
    				+ " 				FROM ETECEPC.FM_CONSULTATION T1, ETECEPC.FM_CONSULTATION_INFO T2								\n"
    				+ " 				WHERE T1.CST_DOC_NO = T2.CST_DOC_NO																\n"
    				+ " 				AND T1.PROJECT_CD = T2.PROJECT_CD																\n"
    				+ " 			  ) T1,																								\n"
    				+ " 			  (																									\n"
    				+ " 				SELECT T1.PROJECT_CD																			\n"
    				+ " 						, T1.ORDER_NO																			\n"
    				+ " 						, T2.ITEM_NO																			\n"
    				+ " 						, T3.WAREHOUSE_DATE																		\n"
    				+ " 						, SUM(T2.ORDER_QTY) AS ORDER_QTY														\n"
    				+ " 						, SUM(T2.SHIP_QTY) AS SHIP_QTY															\n"
    				+ " 						, NVL(SUM(T3.GOOD_QTY), 0) + NVL(SUM(T3.OVER_QTY), 0) AS INPUT_QTY												\n"
    				+ " 						, NVL(SUM(T3.GOOD_QTY), 0) AS GOOD_QTY													\n"
    				+ " 						, NVL(SUM(T3.DAMAGE_QTY), 0) AS DAMAGE_QTY												\n"
    				+ " 						, NVL(SUM(T3.INCORRECT_QTY), 0) AS INCORRECT_QTY										\n"
    				+ " 						, NVL(SUM(T3.SHORT_QTY), 0) AS SHORT_QTY												\n"
    				+ " 						, NVL(SUM(T3.OVER_QTY), 0) AS OVER_QTY													\n"
    				+ " 						, NVL(SUM(T3.RETURN_QTY), 0) AS RETURN_QTY												\n"
    				+ " 						,t4.DEPARTURE																			\n"
    				+ " 						, T3.MRR_NO																				\n"
    				+ " 						, T1.SHIP_NO																			\n"
    				+ " 						, NVL(SUM(T2.SHIP_EXC_PRI),0) as  SHIP_EXC_PRI																		\n"
    				+ " 						,  NVL(SUM(T2.SHIP_EXC_AMT),0) as SHIP_EXC_AMT																		\n"
    				+ " 						, NVL(SUM(T3.INPUT_EXC_PRI),0) as INPUT_EXC_PRI																		\n"
    				+ " 						, NVL(SUM(T3.INPUT_EXC_AMT),0)  as  INPUT_EXC_AMT																		\n"
    				+ " 				FROM ETECEPC.FM_SHIPPING_LIST T1, ETECEPC.FM_SHIPPING_DETAIL T2, ETECEPC.FM_WAREHOUSE T3 , ETECEPC.FM_SHIPPING T4			\n"
    				+ " 				WHERE T1.SHIPPING_LIST_ID = T2.SHIPPING_LIST_ID																				\n"
    				+ " 				AND T1.ORDER_NO = T2.ORDER_NO																								\n"
    				+ " 				AND T2.SHIPPING_DETAIL_ID = T3.SHIPPING_DETAIL_ID (+)																		\n"
    				+ " 				AND '5' = T3.STATUS (+)																										\n"
    				+ " 				AND T1.SHIPPING_ID = T4.SHIPPING_ID																							\n"
    				+ " 				GROUP BY T1.PROJECT_CD, T1.ORDER_NO, T2.ITEM_NO, t4.DEPARTURE , T3.MRR_NO, T1.SHIP_NO     , T3.WAREHOUSE_DATE				\n"
    				+ " 			  ) T2																															\n"
    				+ " 		WHERE T1.PROJECT_CD = T2.PROJECT_CD																									\n"
    				+ " 		AND T1.MNG_PO_NO = T2.ORDER_NO																										\n"
    				+ " 		AND T1.ITEM_NO = T2.ITEM_NO																											\n"
    				+ " 		and DECODE(T1.FOREIGN_YN , 'D' , T2.INPUT_QTY, T2.SHIP_QTY) > 0																		\n"
    				+ " 	) A																																		\n"
    				+ " WHERE A.PUR_CONT_ID = "+purContId+"																											\n"
    				;
    		
    		search.setStatement(sqlstr);
    		WAFLogger.debug(sqlstr);
    		listRowSet = search.execute();
    		
    	}
    	catch(Exception e) {
    		WAFLogger.error(this.getClass().getName() + " --> getitemList() : " + e.getMessage());
    		WAFLogger.error("SQL : " + search.getStatement());
    		WAFLogger.error(e);
    		return null;
    	}
    	finally {
    		
    		if( listRowSet == null ) listRowSet = new RowSetImpl();
    		return listRowSet;
    	}
    }
    
    /**
     *@param ValueObject
     *@return RowSet
     *기   능 : 대금신청 아이템 내역을 조회한다.
     *@author OK
     *@version 2014. 7. 28.
     *@since JDK1.7
     */
    public RowSet getContPutItem(String purContId, String paySeq) {
    	
    	Search search = new SearchImpl();
    	RowSet listRowSet = null;
    	String sqlstr = "";
    	
    	try {
    		sqlstr += " 																																\n"
    				+ " SELECT																															\n"
    				+ " 		NULL AS STOCK_NO -- 입고 번호																								\n"
    				+ " 		,B.MATERIAL_CD AS MATERIAL_CD-- 자재 코드																					\n"
    				+ " 		,B.ITEM_QTY AS STOCK_QTY    -- 대금지급 수량 --> (계약수량)																	\n"
    				+ " 		,CASE WHEN (SELECT CURRENCY_GB FROM PUR2000T WHERE PUR_CONT_MOD_ID = A.PUR_CONT_MOD_ID) = 'KRW' THEN B.ITEM_PRICE		\n"
    				+ " 		ELSE NULL END AS STOCK_PRI -- 계약단가(KRW)																				\n"
    				+ " 		,CASE WHEN (SELECT CURRENCY_GB FROM PUR2000T WHERE PUR_CONT_MOD_ID = A.PUR_CONT_MOD_ID) = 'KRW' THEN B.ITEM_AMT			\n"
    				+ " 		ELSE NULL END as STOCK_AMT -- 계약금액(KRW)																				\n"
    				+ " 		,B.ITEM_PRICE AS EXC_PRI -- 계약단가(외화)																				\n"
    				+ " 		,B.ITEM_AMT AS EXC_AMT -- 계약금액(외화)																					\n"
    				+ " 		,'C' AS GUBUN -- 토건_배관구분																							\n"
    				+ " FROM CONT_PUR_PAYPLAN A ,CONT_PUR_ITEM B																						\n"
    				+ " WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID																						\n"
    				+ " AND A.PUR_CONT_MOD_ID = "+purContId+"																							\n"
    				+ " AND A.PAY_SEQ = "+paySeq+"																										\n"
    				;
    		
    		search.setStatement(sqlstr);
    		WAFLogger.debug(sqlstr);
    		listRowSet = search.execute();
    		
    	}
    	catch(Exception e) {
    		WAFLogger.error(this.getClass().getName() + " --> getitemList() : " + e.getMessage());
    		WAFLogger.error("SQL : " + search.getStatement());
    		WAFLogger.error(e);
    		return null;
    	}
    	finally {
    		
    		if( listRowSet == null ) listRowSet = new RowSetImpl();
    		return listRowSet;
    	}
    }

    /**
     *@param ValueObject
     *@return RowSet
     *기   능 : 대금 내역을 저장
     *@author OK
     *@version 2014. 7. 28.
     *@since JDK1.7
     */
	private void insPayItemList(Connection connection, ValueObject sqlParams, Row itemResult) throws Exception {
		
		PreparedStatement pstmt = null;
		Persistent persistent_i	= new PersistentImpl(connection);
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	INSERT INTO PMM_PAYREQ_ITEM							\n ");
			sql.append("	(													\n ");
			sql.append("		PAYREQ_ITEM_ID         							\n ");
			sql.append("		,PAYREQ_ID              						\n ");
			sql.append("		,STOCK_NO 										\n ");
			sql.append("		,MATERIAL_CD        							\n ");
			sql.append("		,ITEM_QTY       								\n ");
			sql.append("		,ITEM_PRI             							\n ");
			sql.append("		,ITEM_AMT            							\n ");
			sql.append("		,GUBUN	            							\n ");
			sql.append("		,CRE_BY       									\n ");
			sql.append("		,CRE_DATE  										\n ");
			sql.append("		,UPD_BY     									\n ");
			sql.append("		,UPD_DATE    									\n ");
			sql.append("		,EXC_PRI    									\n ");
			sql.append("		,EXC_AMT    									\n ");
			sql.append("		,STOCK_DATE    									\n ");
			sql.append("		)VALUES (										\n ");
			sql.append("				PMM_PAYREQ_ITEM_S.NEXTVAL				\n ");
			sql.append("				,?										\n ");
			sql.append("				,?										\n ");
			sql.append("				,?										\n ");
			sql.append("				,?										\n ");
			sql.append("				,?										\n ");
			sql.append("				,?										\n ");
			sql.append("				,?										\n ");
			sql.append("				,?										\n ");
			sql.append("				,SYSDATE								\n ");
			sql.append("				,?										\n ");
			sql.append("				,SYSDATE								\n ");
			sql.append("				,?										\n ");
			sql.append("				,?										\n ");
			sql.append("		)												\n ");
			
			persistent_i.setStatement(sql.toString());
			WAFLogger.debug(sql.toString());
			
			persistent_i.addParameter(params.getInt("seq"));
			persistent_i.addParameter(itemResult.get("STOCK_NO"));
			persistent_i.addParameter(itemResult.get("MATERIAL_CD"));
			persistent_i.addParameter(itemResult.get("STOCK_QTY"));
			persistent_i.addParameter(itemResult.get("STOCK_PRI"));
			persistent_i.addParameter(itemResult.get("STOCK_AMT"));
			persistent_i.addParameter(itemResult.get("GUBUN"));
			persistent_i.addParameter(params.getInt("USER_ID"));
			persistent_i.addParameter(params.getInt("USER_ID"));
			persistent_i.addParameter(itemResult.get("EXC_PRI"));
			persistent_i.addParameter(itemResult.get("EXC_AMT"));
			WAFLogger.debug(persistent_i.getParameters());
    		
			persistent_i.execute();
			persistent_i.clearParameters();	

		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		}
	}
	
	public void createPDF(String purContId) throws Exception {
		HandlerStorage storage = new HandlerStorage();
    	params.set("p_payreq_id", params.get("seq"));
    	
		ValueObject returnVO = getPayReqDetail(purContId);
		//RowSet returnRowSet = getListRowSet(params.get("seq"));
		RowSet returnRowSet = getitemList(purContId);
		
		if(returnRowSet.size() > 0) {
			
			if(returnVO.get("CURRENCY_GB").equals("KRW")){
				
				PURCreatePDF1 purCreatePDF1 = new PURCreatePDF1();
				purCreatePDF1.doBiz(returnVO.get("dept_cd"), returnVO.get("dept_nm"), returnVO.get("corp_nm"), returnVO.get("ord_doc_no"), params.get("seq"), purContId, returnVO.get("TAX_DY"), storage.request);
			}else{
				PURCreatePDF3 purCreatePDF3 = new PURCreatePDF3();
				purCreatePDF3.doBiz(returnVO.get("dept_cd"), returnVO.get("dept_nm"), returnVO.get("corp_nm"), returnVO.get("ord_doc_no"), params.get("seq"), purContId, returnVO.get("TAX_DY"),returnVO.get("CURRENCY_GB"),returnVO.getInt("digit"), storage.request);
			}

			saveFileData(params.get("seq"), returnVO.get("TAX_DY"), returnVO.get("ord_doc_no"));
		}
	}
	
	
	
	public ValueObject getPayReqDetail(String purContId) throws Exception {               
        
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
		ValueObject vo = null;
        
        try {
        	sql.append("	SELECT                              	                                                    \n");
        	sql.append("		A.DEPT_CD DEPT_CD                 	                                                    \n");
        	sql.append("		, A.DEPT_NAME DEPT_NM             	                                                    \n");
        	sql.append("		, (SELECT CORP_KOR FROM SUPP_INFO WHERE CORP_ID = A.CORP_ID) AS CORP_NM                 \n");
        	sql.append("		, A.ORD_DOC_NO                    	                                                    \n");
        	sql.append("		, TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS REQ_DATE                                            \n");
        	sql.append("		, A.PUR_CONT_ID                      	                                                \n");
        	sql.append("		, A.CURRENCY_GB                      	                                                \n");
        	sql.append("		, F_GET_CURRENCY_DIGIT(A.CURRENCY_GB) AS DIGIT                      	                \n");
        	sql.append("		,(																		                \n");
        	sql.append("			SELECT	NVL(MAX(X.STOCK_DATE),TO_CHAR(LAST_DAY(SYSDATE),'YYYYMMDD'))	            \n");
        	sql.append("			FROM	PMM_PAYREQ_ITEM X, PUR1100T Y						                        \n");
        	sql.append("			WHERE	X.PAYREQ_ID = Y.PAYREQ_ID											        \n");
        	sql.append("			AND		Y.PUR_CONT_ID = A.PUR_CONT_ID												\n");
        	sql.append("		) AS TAX_DY																                \n");
        	sql.append("	FROM PUR1000T A       	                                                                    \n");
        	sql.append("	WHERE A.PUR_CONT_ID = ?	                                                                    \n");
        	
        	
            search.setStatement(sql.toString());
            search.addParameter(purContId);
            WAFLogger.debug(sql.toString());
            listRowSet = search.execute();
            
			if(listRowSet.size()>0){
				vo = RowSetUtility.getValueObject(listRowSet.getRow(0));
			}
			
		}catch(Exception e){
			WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
		}finally{
			if( vo == null) vo = new ValueObject();
		}
		return vo;
    }
	
	public RowSet getListRowSet(String payreq_id){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer(5000);		// 초기메모리 확보함
        
        try {
        	sql.append("	SELECT                                              					\n");
        	sql.append("		MATERIAL_CD                                       					\n");
        	sql.append("		, F_GET_MATERIAL_NAME(MATERIAL_CD) MATERIAL_NAME  					\n");
        	sql.append("		, F_GET_MATL_SPEC_NAME(MATERIAL_CD) MATERIAL_SPEC 					\n");
        	sql.append("		, F_GET_UNIT_KIND_NAME(MATERIAL_CD) UNIT_KIND_NAME					\n");
        	sql.append("		, SUM(NVL(ITEM_QTY,0)) ITEM_QTY                   					\n");
        	sql.append("		, ROUND(SUM(NVL(ITEM_AMT,0)) / SUM(NVL(ITEM_QTY,0)),0) ITEM_PRI		\n");
        	sql.append("		, SUM(NVL(ITEM_AMT,0)) ITEM_AMT                   					\n");
        	sql.append("	FROM PMM_PAYREQ_ITEM                                					\n");
        	sql.append("	WHERE PAYREQ_ID = ?                                 					\n");
        	sql.append("	GROUP BY MATERIAL_CD                                					\n");
        	sql.append("	ORDER BY MATERIAL_CD                                					\n");
			 
            search.setStatement(sql.toString());
            search.addParameter(payreq_id);
            
            listRowSet = search.execute();
        }
        catch(Exception e) {
        	WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
         	return null;
             
         } finally {
             if( listRowSet == null ) listRowSet = new RowSetImpl();
         }
         return listRowSet;
	}
	
	private boolean saveFileData(String payreq_id, String req_date, String ord_doc_no)throws Exception{
		try {
			File fileObj = null;
			String fileName = "";
			String baseCd = "PUR_PAY_FILE";
			
			ValueObject voFileNm = new ValueObject();
			
			String req_date2 = req_date.replaceAll("-", "");
			req_date2 = req_date2.substring(0, 6);
			
			Connection conn = super.conn.getConnection();
			String yyyyMM = (new java.text.SimpleDateFormat ("yyyyMM")).format (new java.util.Date());
			
			
			String fileConnGB = "PURCHASE_"+payreq_id;
			
			// ID = 160 = MRR
			String fileId = getFileID(conn);
			
			addFileMap(conn, fileId, fileConnGB, "160", "F", "");
			
//			String path = StringUtil.replace(WAFConfig.get("waf.repository.path") + "/"+yyyyMM, "/", File.separator);
			String path = StringUtil.replace(WAFConfig.get("waf.repository.path") + "/payment/purchase/"+payreq_id, "/", File.separator);
        	
    		int tempFileCnt = 0;
    		File fileExist = new File(path);
    		File fileList[] = fileExist.listFiles();
    		for(File tempFile : fileList) {
    			if(tempFile.isFile()) {
    				if(tempFile.getName().indexOf(ord_doc_no+"_"+req_date2) > -1) {
    					tempFileCnt++;
    				}
    			}
    		}
    		
    		
    		voFileNm = getNewFileName(conn, "160", baseCd);	// MRR
    		
    		
    		if(tempFileCnt == 1 ) {
    			fileObj = new File(path+"/"+ord_doc_no+"_"+req_date2+".pdf");
    			fileName	=	ord_doc_no+"_"+req_date2+".pdf";
    		} else if (tempFileCnt > 1) {
    			--tempFileCnt;
    			fileObj = new File(path+"/"+ord_doc_no+"_"+req_date2+"_"+tempFileCnt+".pdf");
    			fileName	=	ord_doc_no+"_"+req_date2+".pdf";
    		}
    		
    		fileName = voFileNm.get("FILE_NUM") +". "+ voFileNm.get("DETAILNM") +"_" + fileName;
    		
             //첨부파일 연결하기
    		addFile(conn, fileObj, fileName, fileId);
    		return true;
		} catch (Exception e) {
			super.doException(e);
            conn.setRollback();
            storage.setMessage(e.getMessage());
            storage.setDetailMessage(e);
            return false;
            
		}
	}

	/**
     * 파일맵을 추가한다.
	 * @param connection
	 * @return
	 * @throws Exception
	 */
    private String getFileID(Connection connection) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        String sqlstr = "";
        try {

            sqlstr = " SELECT PUB0500T_S.NEXTVAL AS FILE_ID \n"
                   + " FROM   DUAL                          \n" ;

            persistent.setStatement(sqlstr);

            return persistent.query().getRow(0).get("FILE_ID");
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getFileID() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            storage.setDetailMessage(e);
            throw e;
        }
    }
    
    //변경차수 추가
    private void addFileMap(Connection connection, String fileID, String fileGB, String ID, String addGB, String modNo) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        String sqlstr = "";
        int ACCT_ID = 0;
        if(storage.getUser() != null) ACCT_ID = storage.getUser().getInt("ACCT_ID");
        try {

            sqlstr = " INSERT INTO PUB0510T "
                   + " ( FILE_MAP_ID, FILE_ID, O_FILE_GB, O_ID, FILE_GB, ID, OPEN_CORP_YN, USE_CON_YN, ADD_GB, MOD_NO, " //변경차수 추가
                   + "   CRE_BY, CRE_DATE ) "
                   + " VALUES "
                   + " ( PUB0510T_S.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
                   + "   ?, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') ) " ;

            persistent.setStatement(sqlstr);

            persistent.addParameter(fileID);
            persistent.addParameter(fileGB);
            persistent.addParameter(ID);
            persistent.addParameter(fileGB);
            persistent.addParameter(ID);
            persistent.addParameter("N");
            persistent.addParameter("Y");
            persistent.addParameter(addGB);
            persistent.addParameter(modNo); //변경차수 추가
            persistent.addParameter(ACCT_ID);

            persistent.execute();
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> addFile() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            storage.setDetailMessage(e);
            throw e;
        }
    }
    
    private void addFile(Connection connection, File file, String fileName, String FileId) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        String sqlstr = "";
        int ACCT_ID = 0;
        if(storage.getUser() != null) ACCT_ID = storage.getUser().getInt("ACCT_ID");

        try {

            sqlstr = " INSERT INTO PUB0500T "
                   + " ( FILE_ID, REAL_FILE_NAME, FILE_NAME, FILE_PATH, FILE_EXT,  "
                   + "   FILE_SIZE, MINE_TYPE, CONTENT_TYPE, CONTENT_DISP, SUB_MINE_TYPE,  "
                   + "   CRE_BY, CRE_DATE ) "
                   + " VALUES "
                   + " ( ?, ?, ?, ?, ?, "
                   + "   ?, ?, ?, ?, ?, "
                   + "   ?, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') ) " ;

            persistent.setStatement(sqlstr);

            String defaultDir = WAFConfig.get("waf.repository.path");
            String path = StringUtil.replace( StringUtil.replace(file.getPath(), "\\", "/"), "/" + file.getName(), "");
            path = path.substring(defaultDir.length(), path.length());
            
            ///payment/purchase/30659

            String ext = file.getName().substring(file.getName().lastIndexOf(".") + 1, file.getName().length());

            fileName = StringUtil.replace( fileName, "\\", "/");
            fileName = fileName.substring( fileName.lastIndexOf("/") + 1, fileName.length());

            persistent.addParameter(FileId);
            persistent.addParameter(file.getName());
            persistent.addParameter(fileName);
            persistent.addParameter(path);
            persistent.addParameter(ext);
            
            persistent.addParameter((int)file.length());
            persistent.addParameter("");
            persistent.addParameter(file.getName());
            persistent.addParameter("");
            persistent.addParameter("");

            persistent.addParameter(ACCT_ID);

            persistent.execute();
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> addFile() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            storage.setDetailMessage(e);
            throw e;
        }
    }
	
    private ValueObject getNewFileName( Connection connection, String file_cd, String baseCd) {

		 Persistent persistent = new PersistentImpl(connection);
		 String sqlstr = "";
		 ValueObject vo = new ValueObject();
		 
      try {
      	
          sqlstr = 	" SELECT DETAILNM						" +
          				" 		 ,CHG_CODE02	FILE_NUM				" +
       	   			" FROM EBID_COM911T							"+
       	   			" WHERE BASECD = ?				 		"+
       	   			" AND DETAILCD = ?						" ;

          persistent.setStatement(sqlstr);
          persistent.addParameter(baseCd);
          persistent.addParameter(file_cd);
          RowSet rowset = persistent.query();
          
          if( rowset != null && rowset.size() > 0 ){
          	vo = RowSetUtility.getValueObject(rowset.getRow(0));
          }
      }
      catch(Exception e) {

          WAFLogger.error(this.getClass().getName() + " --> getFileMapID() : " + e.getMessage());
          WAFLogger.error("SQL : " + persistent.getStatement());
          WAFLogger.error(e);
      }
      finally {

          if( vo == null ) vo = new ValueObject();
      }
      
      return vo;
  }

	
}
