/**
 *
 * @(#)author  [ok]
 * @(#)version 1.0
 * @(#)date    2014. 06. 18
 * @(#)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.supplier.portal.payment.biz;


import oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.base.WAFMessages;
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.ValueObject;
import kr.co.udapsoft.common.sender.MessageSend;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.waf.common.ui.InitCombo;
import com.udapsoft.waf.common.util.ClobUtils;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

public class SuppPayItemRequestBiz extends Biz {
	
	public SuppPayItemRequestBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {

		if(storage.compareEvent("")){
			
			//계약정보, 세금계산서정보, 대금정보  
			ValueObject prePayment = getPayItem(params);
			
			String PayItemListId = prePayment.get("PAYREQ_ID");
			
			//보증정보
			params.set("selectSubContModId", prePayment.get("PUR_CONT_MOD_ID"));
			RowSet contOutWarr = getContOutWarr(params);
			storage.addRowSet("contOutWarr", contOutWarr);
			
	 	    // 각국 통화코드에 따른 소숫점 이하 자릿수 구하기
	        RowSet rowSetGb = getUseCurrencyGBRowSet();
	        storage.addRowSet("digitRowSet", rowSetGb);  
	        
	        // 계약통화 자릿수 구하기
	        String digit = getDigit(rowSetGb, (String)prePayment.get("CURRENCY_GB"));
	        prePayment.set("digit", digit);
	        
			storage.addValueObject("prePayment", prePayment);
			
			//기타서류 첨부파일 조회
		    RowSet purchaseFileRow = getfileListRowSet("PURCHASE_"+PayItemListId, "");
		    storage.addRowSet("purchaseFileRow", purchaseFileRow);
		    
		}else if(storage.compareEvent("DELETE_DATA")){
			
			Persistent persistent_d = null;
			String sqlstr_d = "";

			try {
			
				//C:토건, B:플랜트 구분조회
				String gubun = getGubun();
				
				//구매_대금지급신청 삭제
				delPur1100t();
				
				//구매_대금지급 내역 삭제
				delPmmPayreqItem();
				
				//구분에 따른 입고 테이블 변경
				if(gubun.equals("C")){
					//updatePmmStock(); 2018.11.27 미사용 
				}else{
					updateShippingList();
					updateWarehouse();
				}

				super.storage.setSuccess(true);
	        }
	        catch(Exception e) {
	            super.doException(e);
	            super.storage.setMessage(e.getMessage());
	            super.storage.setDetailMessage(e);
	            super.conn.setRollback();
	        	super.storage.setSuccess(false);
	        }
	        finally {
	        	super.conn.end();
	        }
			
		}else if(storage.compareEvent("SAVE_DATA")){
			
			Persistent persistent_i = null;
			Persistent persistent_u = null;
			String sqlstr_i = "";
			String sqlstr_u = "";

			try {
			
				//대금 신청 상제 정보
				String payreqId					= params.get("payreqId");				//지급신청ID
				String taxMethodCd				= params.get("chk_info");				//세금계산서발행방법
				String taxDy					= params.get("date1");					//작성일자(증빙일자)
				String taxChargNm				= params.get("userName");				//담당자명
				String taxChargHp				= params.get("hp");						//담당자 핸드폰번호
				String taxChargEmail			= params.get("eMail");					//담당자 이메일
				String selectDepositCd			= params.get("selectDepositCd");		//지급구분
				
				String befAcmpApplyAmt			= params.get("befAcmpApplyAmt");		//전회 청구 공급가액
				String befAcmpVat				= params.get("befAcmpVat");				//전회 청구 부가세
				String befAcmpAmt				= params.get("befAcmpAmt");				//전회 청구 합계
				String befAcmpPercent			= params.get("befAcmpPercent");			//전회 청구 지급율
				
				String curAcmpApplyamt			= params.get("curAcmpApplyamt");		//금회 청구 공급가액
				String curAcmpVat				= params.get("curAcmpVat");				//금회 청구 부가세
				String curAcmpAmt				= params.get("curAcmpAmt");				//금회 청구 합계
				String curAcmpPercent			= params.get("curAcmpPercent");			//금회 청구 지급율
				
				String curBefAcmpSupplyamt		= params.get("curBefAcmpSupplyamt");	//누계 청구 공급가액
				String curBefAcmpVat			= params.get("curBefAcmpVat");			//누계 청구 부가세
				String curBefAcmpAmt			= params.get("curBefAcmpAmt");			//누계 청구 합계
				String curBefAcmpPercent		= params.get("curBefAcmpPercent");		//누계 청구 지급율
				
				String remainderAcmpSupplyamt	= params.get("remainderAcmpSupplyamt");	//잔여 청구 공급가액
				String remainderAcmpVat			= params.get("remainderAcmpVat");		//잔여 청구 부가세
				String remainderAcmpAmt			= params.get("remainderAcmpAmt");		//잔여 청구 합계
				String remainderAcmpPercent		= params.get("remainderAcmpPercent");	//잔여 청구 지급율
				
				String befPrepayApplyAmt		= params.get("befPrepayApplyAmt");		//전회 선급반제 공급가액
				String befPrepayVat				= params.get("befPrepayVat");			//전회 선급반제 부가세
				String befPrepayAmt				= params.get("befPrepayAmt");			//전회 선급반제 합계
				String befPrepayPercent			= params.get("befPrepayPercent");		//전회 선급반제 지급율
				
				String curPrepayApplyamt		= params.get("curPrepayApplyamt");		//금회 선급반제 공급가액
				String curPrepayVat				= params.get("curPrepayVat");			//금회 선급반제 부가세
				String curPrepayDeductamt		= params.get("curPrepayDeductamt");		//금회 선급반제 합계
				String curPrepayPercent			= params.get("curPrepayPercent");		//금회 선급반제 지급율
				
				String curBefPrepaySupplyamt	= params.get("curBefPrepaySupplyamt");	//누계 선급반제 공급가액
				String curBefPrepayVat			= params.get("curBefPrepayVat");		//누계 선급반제 부가세
				String curBefPrepayAmt			= params.get("curBefPrepayAmt");		//누계 선급반제 합계
				String curBefPrepayPercent		= params.get("curBefPrepayPercent");	//누계 선급반제 지급율
				
				String remainderPrepaySupplyamt	= params.get("remainderPrepaySupplyamt");	//잔여 선급반제 공급가액
				String remainderPrepayVat		= params.get("remainderPrepayVat");			//잔여 선급반제 부가세
				String remainderPrepayAmt		= params.get("remainderPrepayAmt");			//잔여 선급반제 합계
				String remainderPrepayPercent	= params.get("remainderPrepayPercent");		//잔여 선급반제 지급율
				
				String payedApplyamt			= params.get("payedApplyamt");			//전회 실지금	공급가액
				String payedVat					= params.get("payedVat");				//전회 실지금	부가세
				String payedAmt					= params.get("payedAmt");				//전회 실지금	합계
				String payedPercent				= params.get("payedPercent");			//전회 실지금	지급율
				
				String curReqAmt				= params.get("curReqAmt");				//금회 실지금	공급가액
				String curReqVatamt				= params.get("curReqVatamt");			//금회 실지금	부가세
				String curReqApplyamt			= params.get("curReqApplyamt");			//금회 실지금	합계
				String curReqPercent			= params.get("curReqPercent");			//금회 실지금	지급율
				
				String curPayedSupplyamt		= params.get("curPayedSupplyamt");		//누계 실지금	공급가액
				String curPayedVat				= params.get("curPayedVat");			//누계 실지금	부가세
				String curPayedAmt				= params.get("curPayedAmt");			//누계 실지금	합계
				String curPayedPercent			= params.get("curPayedPercent");		//누계 실지금	지급율
				
				String remainderSupplyamt		= params.get("remainderSupplyamt");		//잔여 실지금 공급가액
				String remainderVat				= params.get("remainderVat");			//잔여 실지금 부가세
				String remainderAmt				= params.get("remainderAmt");			//잔여 실지금 합계
				String remainderPercent			= params.get("remainderPercent");		//잔여 실지금 지급율
				
				String reMarks					= params.get("reMarks");				//특기사항
				String jigubsayu				= params.get("howToPay");
				String statusCd					= "01";									//상태(작성중)

				conn.begin();
				
				persistent_u = new PersistentImpl(super.conn.getConnection());
				sqlstr_u = " UPDATE PUR1100T	      														\n"
			              + " SET    TAX_METHOD_CD    			= ?       , 					            \n"
			              + "        TAX_DY 					= ?       , 					            \n"
			              + "        TAX_CHARG_NM				= ?       , 					            \n"
			              + "        TAX_CHARG_HP				= ?       , 					            \n"
			              + "        TAX_CHARG_EMAIL			= ?       , 					            \n"
			              + "        DEPOSIT_CD					= ?       , 					            \n"

			              + "        BEF_ACMP_APPLYAMT			= ?       , 					            \n"
			              + "        BEF_ACMP_VAT				= ?       , 					            \n"
			              + "        BEF_ACMP_AMT				= ?       , 					            \n"
			              + "        BEF_ACMP_PERCENT			= ?       , 					            \n"
			              + "        CUR_ACMP_APPLYAMT			= ?       , 					            \n"
			              + "        CUR_ACMP_VAT				= ?       , 					            \n"
			              + "        CUR_ACMP_AMT				= ?       , 					            \n"
			              + "        CUR_ACMP_PERCENT			= ?       , 					            \n"
			              + "        CUR_BEF_ACMP_SUPPLYAMT		= ?       , 					            \n"
			              + "        CUR_BEF_ACMP_VAT			= ?       , 					            \n"
			              + "        CUR_BEF_ACMP_AMT			= ?       , 					            \n"
			              + "        CUR_BEF_ACMP_PERCENT		= ?       , 					            \n"
			              + "        REMAINDER_ACMP_SUPPLYAMT	= ?       , 					            \n"
			              + "        REMAINDER_ACMP_VAT			= ?       , 					            \n"
			              + "        REMAINDER_ACMP_AMT			= ?       , 					            \n"
			              + "        REMAINDER_ACMP_PERCENT		= ?       , 					            \n"
			              
			              + "        BEF_PREPAY_APPLYAMT		= ?       , 					            \n"
			              + "        BEF_PREPAY_VAT				= ?       , 					            \n"
			              + "        BEF_PREPAY_AMT				= ?       , 					            \n"
			              + "        BEF_PREPAY_PERCENT			= ?       , 					            \n"
			              + "        CUR_PREPAY_APPLYAMT		= ?       , 					            \n"
			              + "        CUR_PREPAY_VAT				= ?       , 					            \n"
			              + "        CUR_PREPAY_DEDUCTAMT		= ?       , 					            \n"
			              + "        CUR_PREPAY_PERCENT			= ?       , 					            \n"
			              + "        CUR_BEF_PREPAY_SUPPLYAMT	= ?       , 					            \n"
			              + "        CUR_BEF_PREPAY_VAT			= ?       , 					            \n"
			              + "        CUR_BEF_PREPAY_AMT			= ?       , 					            \n"
			              + "        CUR_BEF_PREPAY_PERCENT		= ?       , 					            \n"
			              + "        REMAINDER_PREPAY_SUPPLYAMT	= ?       , 					            \n"
			              + "        REMAINDER_PREPAY_VAT		= ?       , 					            \n"
			              + "        REMAINDER_PREPAY_AMT		= ?       , 					            \n"
			              + "        REMAINDER_PREPAY_PERCENT	= ?       , 					            \n"
			              
			              + "        PAYED_APPLYAMT				= ?       , 					            \n"
			              + "        PAYED_VAT					= ?       , 					            \n"
			              + "        PAYED_AMT					= ?       , 					            \n"
			              + "        PAYED_PERCENT				= ?       , 					            \n"
			              + "        CUR_REQ_AMT				= ?       , 					            \n"
			              + "        CUR_REQ_VATAMT				= ?       , 					            \n"
			              + "        CUR_REQ_APPLYAMT			= ?       , 					            \n"
			              + "        CUR_REQ_PERCENT			= ?       , 					            \n"
			              + "        CUR_PAYED_SUPPLYAMT		= ?       , 					            \n"
			              + "        CUR_PAYED_VAT				= ?       , 					            \n"
			              + "        CUR_PAYED_AMT				= ?       , 					            \n"
			              + "        CUR_PAYED_PERCENT			= ?       , 					            \n"
			              + "        REMAINDER_SUPPLYAMT		= ?       , 					            \n"
			              + "        REMAINDER_VAT				= ?       , 					            \n"
			              + "        REMAINDER_AMT				= ?       , 					            \n"
			              + "        REMAINDER_PERCENT			= ?       , 					            \n"
			              
			              + "        REQ_STATUS					= ?       , 					            \n"
			              + "        RE_MARKS					= ?       ,	 					            \n"
			              + "        DEPOSIT_RESN				= ?       	 					            \n"
			              
			              + " WHERE  PAYREQ_ID      			= ?        									\n" ;
				  
				persistent_u.setStatement(sqlstr_u);
					
				persistent_u.addParameter(taxMethodCd);
				persistent_u.addParameter(taxDy.replaceAll("-", ""));
				persistent_u.addParameter(taxChargNm);
				persistent_u.addParameter(taxChargHp);
				persistent_u.addParameter(taxChargEmail);
				persistent_u.addParameter(selectDepositCd);
				
				persistent_u.addParameter(befAcmpApplyAmt.replaceAll(",", ""));
				persistent_u.addParameter(befAcmpVat.replaceAll(",", ""));
				persistent_u.addParameter(befAcmpAmt.replaceAll(",", ""));
				persistent_u.addParameter(befAcmpPercent.replaceAll(",", ""));
				persistent_u.addParameter(curAcmpApplyamt.replaceAll(",", ""));
				persistent_u.addParameter(curAcmpVat.replaceAll(",", ""));
				persistent_u.addParameter(curAcmpAmt.replaceAll(",", ""));
				persistent_u.addParameter(curAcmpPercent.replaceAll(",", ""));
				persistent_u.addParameter(curBefAcmpSupplyamt.replaceAll(",", ""));
				persistent_u.addParameter(curBefAcmpVat.replaceAll(",", ""));
				persistent_u.addParameter(curBefAcmpAmt.replaceAll(",", ""));
				persistent_u.addParameter(curBefAcmpPercent.replaceAll(",", ""));
				persistent_u.addParameter(remainderAcmpSupplyamt.replaceAll(",", ""));
				persistent_u.addParameter(remainderAcmpVat.replaceAll(",", ""));
				persistent_u.addParameter(remainderAcmpAmt.replaceAll(",", ""));
				persistent_u.addParameter(remainderAcmpPercent.replaceAll(",", ""));
				
				persistent_u.addParameter(befPrepayApplyAmt.replaceAll(",", ""));
				persistent_u.addParameter(befPrepayVat.replaceAll(",", ""));
				persistent_u.addParameter(befPrepayAmt.replaceAll(",", ""));
				persistent_u.addParameter(befPrepayPercent.replaceAll(",", ""));
				persistent_u.addParameter(curPrepayApplyamt.replaceAll(",", ""));
				persistent_u.addParameter(curPrepayVat.replaceAll(",", ""));
				persistent_u.addParameter(curPrepayDeductamt.replaceAll(",", ""));
				persistent_u.addParameter(curPrepayPercent.replaceAll(",", ""));
				persistent_u.addParameter(curBefPrepaySupplyamt.replaceAll(",", ""));
				persistent_u.addParameter(curBefPrepayVat.replaceAll(",", ""));
				persistent_u.addParameter(curBefPrepayAmt.replaceAll(",", ""));
				persistent_u.addParameter(curBefPrepayPercent.replaceAll(",", ""));
				persistent_u.addParameter(remainderPrepaySupplyamt.replaceAll(",", ""));
				persistent_u.addParameter(remainderPrepayVat.replaceAll(",", ""));
				persistent_u.addParameter(remainderPrepayAmt.replaceAll(",", ""));
				persistent_u.addParameter(remainderPrepayPercent.replaceAll(",", ""));
				
				persistent_u.addParameter(payedApplyamt.replaceAll(",", ""));
				persistent_u.addParameter(payedVat.replaceAll(",", ""));
				persistent_u.addParameter(payedAmt.replaceAll(",", ""));
				persistent_u.addParameter(payedPercent.replaceAll(",", ""));
				persistent_u.addParameter(curReqAmt.replaceAll(",", ""));
				persistent_u.addParameter(curReqVatamt.replaceAll(",", ""));
				persistent_u.addParameter(curReqApplyamt.replaceAll(",", ""));
				persistent_u.addParameter(curReqPercent.replaceAll(",", ""));
				persistent_u.addParameter(curPayedSupplyamt.replaceAll(",", ""));
				persistent_u.addParameter(curPayedVat.replaceAll(",", ""));
				persistent_u.addParameter(curPayedAmt.replaceAll(",", ""));
				persistent_u.addParameter(curPayedPercent.replaceAll(",", ""));
				persistent_u.addParameter(remainderSupplyamt.replaceAll(",", ""));
				persistent_u.addParameter(remainderVat.replaceAll(",", ""));
				persistent_u.addParameter(remainderAmt.replaceAll(",", ""));
				persistent_u.addParameter(remainderPercent.replaceAll(",", ""));
				
				persistent_u.addParameter(statusCd);
				persistent_u.addParameter(reMarks);
				persistent_u.addParameter(jigubsayu);
				
				persistent_u.addParameter(payreqId);	//지급신청ID
					
				persistent_u.execute();
				persistent_u.clearParameters();

				super.storage.setSuccess(true);
			}			
	        catch(Exception e) {
	            super.doException(e);
	            super.storage.setMessage(e.getMessage());
	            super.storage.setDetailMessage(e);
	            super.conn.setRollback();
	        	super.storage.setSuccess(false);
	        }
	        finally {
	        	super.conn.end();
	        }
		
		}else if(storage.compareEvent("CHK_ATTCH")){
			
			String resultChk = "0";	//첨부완료
			String status = "";

			//기타서류 첨부파일 조회
		    RowSet purchaseFileRow = getfileListRowSet("PURCHASE_"+params.get("payreqId"), "");
		    for(int i=0; i<purchaseFileRow.size(); i++){
		    	ValueObject rowObject = RowSetUtility.getValueObject(purchaseFileRow.getRow(i));
		    	if(rowObject.get("DETAILCD").equals("110") && rowObject.get("DOC_CD").equals("")){	//대금청구서
		    		resultChk = "1";
		    	}
		    }
    		ValueObject vo = new ValueObject();
    		vo.set("checkFlag", resultChk);
    		storage.addValueObject("VALUEOBJECT", vo);
    		storage.setSuccess(true);
    		
		}else if(storage.compareEvent("REQ_DATA")){
			
			Persistent persistent_u = null;
			String sqlstr_u = "";

			try {
			
				//대금 신청 상제 정보
				String payreqId					= params.get("payreqId");			//지급신청ID				
				String statusCd					= "03";								//상태(01:작성중, 02:대금요청, 03:대금신청, 04:결재중, 10:결재완료, 99:반려)
				String signeddata				= params.get("signeddata");			//서명데이터
				
				conn.begin();
				
				persistent_u = new PersistentImpl(super.conn.getConnection());

				sqlstr_u = " UPDATE PUR1100T      														\n"
				          + " SET    REQ_STATUS    				= ?       ,									\n"
				          + "        SUPP_SIGN_DT  				= sysdate									\n"
				          + " WHERE  PAYREQ_ID      			= ?        									\n" ;
				  
				persistent_u.setStatement(sqlstr_u);
				
				persistent_u.addParameter(statusCd);
				persistent_u.addParameter(payreqId);
				
				persistent_u.execute();
				persistent_u.clearParameters();

				ClobUtils.setClob( conn.getConnection(), "PUR1100T", "SIGN_DATA", " WHERE PAYREQ_ID = '"+payreqId+"'", signeddata);
				
				String resUserName = params.get("resUserName");			//계약 담당자명
				String resHp = params.get("resHp").replaceAll("-", "");	//계약 담당자 핸드폰번호
				String resEmail = params.get("resEmail");				//계약 담당자 이메일

				String hp = params.get("hp").replaceAll("-", "");	//업체 담당자 핸드폰번호
				String eMail = params.get("eMail");					//업체 담당자 이메일
	        	String memberName = params.get("memberName");		//업체명
	        	String userName = params.get("userName");			//업체 담당자명
	        	String deptName = params.get("deptName");			//현장명
	        	String conDocNo = params.get("conDocNo");			//계약번호
	        	String buildName = params.get("buildName");			//계약명

				//SMS
		      	MessageSend messageSend = new MessageSend(storage);
		      	String recvAddress = hp;
	        	String smsContent = WAFMessages.getMessage("Billing.Approved.Request");
	        	//System.out.println(smsContent);
	        	messageSend.sendSMS(resHp, "024899000", "", smsContent);  //2015.9.21 업체->당사담당자 sms 대표번호 
				
	        	//E-Mail
	        	MessageSend mailSend = new MessageSend(storage);
	        	StringBuffer mailContent = new StringBuffer();
	        	mailContent.append(memberName+ "님이 대금을 신청하였습니다. </BR>");
	        	mailContent.append("신청한 대금정보 확인후 그룹웨어 결재 하시기 바랍니다.</BR>");
	        	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	        	mailContent.append("</BR>");
	        	mailContent.append("1.프로젝트: "+deptName+"</BR>");
	        	mailContent.append("2.계약번호: "+conDocNo+"</BR>");
	        	mailContent.append("3.계약명: " +buildName+"</BR>");
	        	mailContent.append("4.협력사 담당자:"+resUserName+"(연락처 :"+resUserName+")</BR>");
	        	mailContent.append("</font>");
	        	messageSend.sendEmail(resEmail, eMail, "대금 결재요청 메일입니다.", "대금 결재요청 메일입니다.", mailContent,"Y");
				
				super.storage.setSuccess(true);
			}			
	        catch(Exception e) {
	            super.doException(e);
	            super.storage.setMessage(e.getMessage());
	            super.storage.setDetailMessage(e);
	            super.conn.setRollback();
	        	super.storage.setSuccess(false);
	        }
	        finally {
	        	super.conn.end();
	        }
		} else if(storage.compareEvent("GET_ATTACHFILE")){
			//첨부파일
			ValueObject AttachFile = getfilelist("PURCHASE_"+params.get("payreqId"),"","AttachFile");
	        storage.addValueObject("VALUEOBJECT", AttachFile);
		}else if(storage.compareEvent("GET_LIST")){
			
			params.set("sheet1", getList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
		
	}
	
    public RowSet getList(ValueObject Params) {
    	Search search = new SearchImpl();
    	RowSet listRowSet = null;
    	String sqlstr = "";
    	
    	try {
    		
    		sqlstr += " SELECT 																											\n"
    				+ "     PAYREQ_ITEM_ID, PAYREQ_ID, STOCK_NO, A.MATERIAL_CD, ITEM_QTY, ITEM_PRI, ITEM_AMT,  GUBUN, EXC_PRI, EXC_AMT,	\n"
    				+ "     B.MATERIAL_NAME AS MATERIAL_NAME,																			\n"
    				+ "     B.MATERIAL_SPEC AS MATERIAL_SPEC,																			\n"
    				+ "     nvl((SELECT DETAILNM FROM EBID_COM911T WHERE BASECD = 'UNIT_KIND_GB' AND DETAILCD = B.UNIT_KIND), B.UNIT_KIND) AS UNIT_KIND	\n"
    				+ " FROM PMM_PAYREQ_ITEM A, PMM_COMM_MATERIAL_MASTER B								 								\n"
    				+ " WHERE 1=1																										\n"
    				+ " AND A.MATERIAL_CD = B.MATERIAL_CD																				\n"
    				+ " AND PAYREQ_ID = '"+Params.get("payreqId")+"'																				\n";
    		
    		search.setStatement(sqlstr);
    		//System.out.println(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 
	 * @param 
	 * @return ValueObject
	 */
	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 
	 * @param 
	 * @return ValueObject
	 */
	public RowSet getPerpayReqDiv(){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT DETAILCD, DETAILNM             	\n"
                    + " FROM EBID_COM911T      					\n"
                    + " WHERE BASECD = 'REG_DIV_CD'     		\n";
             
            search.setStatement(sqlstr);
            listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}
	
    /**
     *@param ValueObject
     *@return ValueObject
     *기   능 : 대금신청 상세(구매 중도금,잔금)
     *@author OK
     *@version 2014. 8. 07.
     *@since JDK1.7
     */
    public ValueObject getPayItem(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        ValueObject rowObject = null;
        
        try {

        	sqlstr += " SELECT 			  								  																						\n"
        			+ "     A.PUR_CONT_ID	                			  																						\n"
        			+ "     ,A.DEPT_CD                      --현장코드	 																						\n"
        			+ "     ,A.DEPT_NAME                    --현장명		  																						\n"
        			+ "     ,A.CON_GB                       --계약구문  																							\n"
        			+ "     ,A.ORD_DOC_NO                 	--발주계약번호																						\n"
        			+ "     ,A.CST_DOC_NAME             	--계약명		  																						\n"
        			+ "     ,A.CURRENCY_GB                	--통화		  																						\n"
        			+ "     ,substr(D.CORP_REG_NO,0,3)||'-'||substr(D.CORP_REG_NO,4,2)||'-'||substr(D.CORP_REG_NO,6,5) AS REG_NO       --사업자번호				\n"
        			+ "     ,D.CORP_KOR AS CORP_KOR                     --계약업체명     																			\n"
        			+ "     ,D.REP_NAME AS BOSS_NAME					  																						\n"
        			+ "     ,TO_CHAR(A.ORD_START_DATE, 'YYYY-MM-DD') || ' ~ '|| TO_CHAR(ORD_END_DATE, 'YYYY-MM-DD') AS ORD_START_END_DATE --계약기간				\n"
        			+ "     ,B.ORD_AMT                       --발주계약금액 		  																				\n"
        			+ "     ,B.DEPOSIT_RESN AS DDD     	--대금지불방법														         							\n"
        			+ "     ,NVL(B.TAX_METHOD_CD, '1') AS TAX_METHOD_CD	    --세금계산서 발행방법																	\n"
        			//+ "     ,NVL(B.TAX_DY, TO_CHAR(SYSDATE, 'YYYY-MM-DD')) AS TAX_DY   --작성일자				       			  									\n"
        			+ "     ,NVL(B.TAX_DY, '') AS TAX_DY   --작성일자				       			  																\n"
        			+ "     ,B.TAX_CHARG_NM			   	--담당자명											      			  									\n"
        			+ "     ,B.TAX_CHARG_HP	    		--담당자 핸드폰번호									       			  									\n"
        			+ "     ,B.TAX_CHARG_EMAIL		 	--담당자 이메일  											   			  									\n"
        			+ "     ,B.PAY_DIV                  --대금구분								                			  									\n"
        			+ "     ,B.DEPOSIT_CD               --지급구분								                			  									\n"
        			+ "     ,B.DEPOSIT_SEQ              --지급차수								                			  									\n"
        			+ "     ,NVL(C.REQ_PREPAY_AMT       ,0) AS PREPAY_AMT    		--선급금 청구금액  합계					                			  			\n"
        			+ "     ,NVL(C.REQ_PREPAY_SUPPLY_AMT,0) AS PREPAY_SUPPLY_AMT   	--선급금 청구금액 청구금액					                			  			\n"
        			+ "     ,NVL(C.REQ_PREPAY_SURTAX_AMT,0) AS PREPAY_SURTAX_AMT    --선급금 청구금액 부가세					                			  			\n"
        			+ "     ,DECODE(B.SUPP_SIGN_DT, null, '미서명', to_char(B.SUPP_SIGN_DT, 'yyyy-mm-dd HH24:MI:SS') ) AS  CUST_SIGN_DATE --서명일시	\n"
					+ "     ,NVL(B.BEF_ACMP_APPLYAMT, 0) AS BEF_ACMP_APPLYAMT          --전회까지 청구금액 공급가액													\n"
					+ "     ,NVL(B.BEF_ACMP_VAT, 0) AS BEF_ACMP_VAT                    --전회까지 청구금액 부가세													\n"
					+ "     ,NVL(B.BEF_ACMP_AMT, 0) AS BEF_ACMP_AMT                    --전회까지 청구금액 합계														\n"
					+ "     ,NVL(B.BEF_ACMP_PERCENT, 0) AS BEF_ACMP_PERCENT            --전회까지 청구금액 지급율													\n"
					+ "     ,NVL(B.CUR_ACMP_APPLYAMT, 0) AS CUR_ACMP_APPLYAMT          --금회 청구금액 공급가액														\n"
					+ "     ,NVL(B.CUR_ACMP_VAT, 0) AS CUR_ACMP_VAT                    --금회 청구금액 부가세														\n"
					+ "     ,NVL(B.CUR_ACMP_AMT, 0) AS  CUR_ACMP_AMT                   --금회 청구금액 합계														\n"
					+ "     ,NVL(B.CUR_ACMP_PERCENT, 0) AS CUR_ACMP_PERCENT            --금회 청구금액 지급율														\n"
					+ "     ,NVL(B.CUR_BEF_ACMP_SUPPLYAMT, 0) AS CUR_BEF_ACMP_SUPPLYAMT	   --누계 청구금액 공급가액													\n"
					+ "     ,NVL(B.CUR_BEF_ACMP_VAT, 0) AS CUR_BEF_ACMP_VAT                --누계 청구금액 부가세													\n"
					+ "     ,NVL(B.CUR_BEF_ACMP_AMT, 0) AS CUR_BEF_ACMP_AMT                --누계 청구금액 합계													\n"
					+ "     ,NVL(B.CUR_BEF_ACMP_PERCENT, 0) AS CUR_BEF_ACMP_PERCENT   	   --누계 청구금액 지급율													\n"
					+ "     ,NVL(B.REMAINDER_ACMP_SUPPLYAMT, 0) AS REMAINDER_ACMP_SUPPLYAMT   --잔여 청구금액 공급가액												\n"
					+ "     ,NVL(B.REMAINDER_ACMP_VAT, 0) AS REMAINDER_ACMP_VAT               --잔여 청구금액 부가세												\n"
					+ "     ,NVL(B.REMAINDER_ACMP_AMT, 0) AS REMAINDER_ACMP_AMT               --잔여 청구금액 합계													\n"
					+ "     ,NVL(B.REMAINDER_ACMP_PERCENT, 0) AS REMAINDER_ACMP_PERCENT       --잔여 청구금액 지급율												\n"
					+ "     ,NVL(B.BEF_PREPAY_APPLYAMT, 0) AS BEF_PREPAY_APPLYAMT             --전회까지 선급금반제 공급가액										\n"
					+ "     ,NVL(B.BEF_PREPAY_VAT, 0) AS BEF_PREPAY_VAT		                  --전회까지 선급금반제 부가세											\n"
					+ "     ,NVL(B.BEF_PREPAY_AMT, 0) AS BEF_PREPAY_AMT                       --전회까지 선급금반제 합계											\n"
					+ "     ,NVL(B.BEF_PREPAY_PERCENT, 0) AS BEF_PREPAY_PERCENT               --전회까지 전급금반제 지급율											\n"
					+ "     ,NVL(B.CUR_PREPAY_APPLYAMT, 0) AS CUR_PREPAY_APPLYAMT             --금회 선급금반제 공급가액											\n"
					+ "     ,NVL(B.CUR_PREPAY_VAT, 0) AS CUR_PREPAY_VAT	                      --금회 선급금반제 부가세												\n"
					+ "     ,NVL(B.CUR_PREPAY_DEDUCTAMT, 0) AS CUR_PREPAY_DEDUCTAMT           --금회 선급금반제 합계												\n"
					+ "     ,NVL(B.CUR_PREPAY_PERCENT, 0) AS CUR_PREPAY_PERCENT               --금회 선급금반제 지급율												\n"
					+ "     ,NVL(B.CUR_BEF_PREPAY_SUPPLYAMT, 0) AS CUR_BEF_PREPAY_SUPPLYAMT   --누계 선급금반제 공급가액											\n"
					+ "     ,NVL(B.CUR_BEF_PREPAY_VAT, 0) AS CUR_BEF_PREPAY_VAT               --누계 선급금반제 부가세												\n"
					+ "     ,NVL(B.CUR_BEF_PREPAY_AMT, 0) AS CUR_BEF_PREPAY_AMT               --누계 선급금반제 합계												\n"
					+ "     ,NVL(B.CUR_BEF_PREPAY_PERCENT, 0) AS CUR_BEF_PREPAY_PERCENT       --누계 선급금반제 지급율												\n"
					+ "     ,NVL(B.REMAINDER_PREPAY_SUPPLYAMT, 0) AS REMAINDER_PREPAY_SUPPLYAMT      --잔여 선급금반제 공급가액										\n"
					+ "     ,NVL(B.REMAINDER_PREPAY_VAT, 0) AS REMAINDER_PREPAY_VAT                  --잔여 선급금반제 부가세										\n"
					+ "     ,NVL(B.REMAINDER_PREPAY_AMT, 0) AS REMAINDER_PREPAY_AMT                  --잔여 선급금반제 합계										\n"
					+ "     ,NVL(B.REMAINDER_PREPAY_PERCENT, 0) AS REMAINDER_PREPAY_PERCENT          --잔여 선급금반제 지급율										\n"
					+ "     ,NVL(B.PAYED_APPLYAMT, 0) AS PAYED_APPLYAMT			                     --전회까지 실지급액 공급가액									\n"
					+ "     ,NVL(B.PAYED_VAT, 0) AS PAYED_VAT					                     --전회까지 실지급액 부가세										\n"
					+ "     ,NVL(B.PAYED_AMT, 0) AS PAYED_AMT				                         --전회까지 실지급액 합계										\n"
					+ "     ,NVL(B.CUR_REQ_AMT, 0) AS CUR_REQ_AMT		                      --금회 실지급액 공급가액												\n"
					+ "     ,NVL(B.CUR_REQ_VATAMT, 0) AS CUR_REQ_VATAMT		                  --금회 실지급액 부가세												\n"
					+ "     ,NVL(B.CUR_REQ_APPLYAMT, 0) AS CUR_REQ_APPLYAMT                   --금회 실지급액 합계													\n"
					+ "     ,NVL(B.CUR_PAYED_SUPPLYAMT, 0) AS CUR_PAYED_SUPPLYAMT             --누계 실지급액 공급가액												\n"
					+ "     ,NVL(B.CUR_PAYED_VAT, 0) AS CUR_PAYED_VAT		                  --누계 실지급액 부가세												\n"
					+ "     ,NVL(B.CUR_PAYED_AMT, 0) AS CUR_PAYED_AMT		                  --누계 실지급액 합계													\n"
					+ "     ,B.PAYREQ_ID               																											\n"
					+ "     ,B.RE_MARKS               																											\n"
					+ "     ,B.REQ_STATUS              																											\n"
					+ "     ,F_GET_CODE_NAME('PURCHASE_STATUS_CD', B.REQ_STATUS) AS STATUS_NM       															\n"
					+ "     ,B.RTN_DATE       																													\n"
					+ "     ,B.RTN_REASON       																												\n"
					+ "     ,B.PUR_CONT_MOD_ID     																												\n"
					+ "     ,(SELECT NVL(CHG_CODE10, 'INPUT_VAT') -- 공급가액의 input_vat : 10% other 0%															\n"
					+ "     FROM EBID_COM911T     																												\n"
					+ "     WHERE BASECD = 'PROOF_CD'   																												\n"
					+ "     AND DETAILCD = (SELECT NVL(GUA_GB, 'Z99') FROM PUR1000T WHERE PUR_CONT_ID = A.PUR_CONT_ID)) CHG_CODE10											\n"
					+ "     ,E.USER_NAME AS RES_USER_NAME,											\n"
					+ "    E.HP AS RES_HP,     											\n"
					+ "    E.EMAIL AS RES_EMAIL,									\n"
					+ "       E.DIVISION AS RES_DIVISION,       											\n"
					+ "       A.DEF_GUAR_AMT_RATE       											\n"
					+ " FROM PUR2000T A, 																														\n"
        			+ "      PUR1100T B,																														\n"
        			+ "      (select sum(REQ_PREPAY_AMT) REQ_PREPAY_AMT -- 신청 선급금 합계																		\n"
        			+ "      		,sum(REQ_PREPAY_SUPPLY_AMT) REQ_PREPAY_SUPPLY_AMT -- 신청 선급금 공급가액														\n"
        			+ "     		,sum(REQ_PREPAY_SURTAX_AMT) REQ_PREPAY_SURTAX_AMT -- 신청 선급금 부가세														\n"
        			+ "      		,pur_cont_id      																												\n"
        			+ "      from pur1030t      																												\n"
        			+ "      where PREPAY_PROC_GB = '10'     																												\n"
        			+ "      group by pur_cont_id   																												\n"
        			+ "      ) C,   																												\n"
        			+ "      SUPP_INFO D          																												\n"
        			+ "      ,cont_pur_supp E          																												\n"
        			+ " WHERE 1=1																																\n"
        			+ " AND A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID																										\n"
        			+ " AND B.PUR_CONT_ID = C.PUR_cont_ID(+)																								\n"
        			+ " AND A.CORP_ID = D.CORP_ID																												\n"
        			+ " AND A.pur_cont_mod_id = E.pur_cont_mod_id																											\n"
        			+ " AND E.sign_seq = '1'																												\n"
        			;

        	sqlstr += " AND B.PAYREQ_ID = '"+ params.get("payreqId") +"'																					\n"
        			;
        	
            search.setStatement(sqlstr);
            //System.out.println(sqlstr);
            listRowSet = search.execute();
            rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
            
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getPrePayment() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            if( rowObject == null ) rowObject = new ValueObject();
            return rowObject;
        }
    }
    
    /**
     *@param ValueObject
     *@return ValueObject
     *기   능 : 대금내역
     *@author OK
     *@version 2014. 8. 07.
     *@since JDK1.7
     */
    public RowSet getPayItemList(String PayItemListId) {
    	Search search = new SearchImpl();
    	RowSet listRowSet = null;
    	String sqlstr = "";
    	
    	try {
    		
    		sqlstr += " SELECT 																											\n"
    				+ "     PAYREQ_ITEM_ID, PAYREQ_ID, STOCK_NO, A.MATERIAL_CD, ITEM_QTY, ITEM_PRI, ITEM_AMT,  GUBUN, EXC_PRI, EXC_AMT,	\n"
    				+ "     B.MATERIAL_NAME AS MATERIAL_NAME,																			\n"
    				+ "     B.MATERIAL_SPEC AS MATERIAL_SPEC																			\n"
    				+ " FROM PMM_PAYREQ_ITEM A, PMM_COMM_MATERIAL_MASTER B								 								\n"
    				+ " WHERE 1=1																										\n"
    				+ " AND A.MATERIAL_CD = B.MATERIAL_CD																				\n"
    				+ " AND PAYREQ_ID = '"+PayItemListId+"'																				\n";
    		
    		search.setStatement(sqlstr);
    		//System.out.println(sqlstr);
    		listRowSet = search.execute();
    		//rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
    		
    	}
    	catch(Exception e) {
    		WAFLogger.error(this.getClass().getName() + " --> getPayItemList() : " + e.getMessage());
    		WAFLogger.error("SQL : " + search.getStatement());
    		WAFLogger.error(e);
    		return null;
    	}
    	finally {
    		
    		if( listRowSet == null ) listRowSet = null;
    		return listRowSet;
    	}
    }
    
    /**
     *@param ValueObject
     *@return ValueObject
     *기   능 : 외주계약 보증정보 데이터 조회
     *@author OK
     *@version 2014. 6. 23.
     *@since JDK1.7
     */
    public RowSet getContOutWarr(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        ValueObject rowObject = null;
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("	SELECT  WARR_GUBUN	\n");
        	sqlstr.append("	    , WARR_GUBUN_NM	\n");
        	sqlstr.append("	    , MAX(MOD_NO) AS MOD_NO	\n");
        	sqlstr.append("	    , NVL(MAX(WARR_SEQ),(SELECT NVL(MAX(WARR_SEQ),0) + 1 FROM CONT_PUR_WARR WHERE PUR_CONT_MOD_ID = '"+ params.get("selectSubContModId") +"')) AS WARR_SEQ	\n");
        	sqlstr.append("	    , MAX(WARR_WAMT) AS WARR_WAMT	\n");
        	sqlstr.append("	    , MAX(VIEW_DIV) AS VIEW_DIV	\n");
        	sqlstr.append("	    , MAX(CAUSECODE) AS CAUSECODE	\n");
        	sqlstr.append("	    , MAX(CAUSECODE_NM) AS CAUSECODE_NM	\n");
        	sqlstr.append("	    , MAX(GUAR_ORGAN_CODE) AS GUAR_ORGAN_CODE	\n");
        	sqlstr.append("	    , MAX(WARR_GOFFICE_ID) AS WARR_GOFFICE_ID	\n");
        	sqlstr.append("	    , MAX(WARR_GOFFICE) AS WARR_GOFFICE	\n");
        	sqlstr.append("	    , MAX(GUAR_STATUS) AS GUAR_STATUS	\n");
        	sqlstr.append("	    , MAX(GUAR_STATUS_NM) AS GUAR_STATUS_NM	\n");
        	sqlstr.append("	    , MAX(PAPERDATE) AS PAPERDATE	\n");
        	sqlstr.append("	    , MAX(WARR_WGNO) AS WARR_WGNO	\n");
        	sqlstr.append("	    , MAX(WARR_SDATE) AS WARR_SDATE	\n");
        	sqlstr.append("	    , MAX(WARR_EDATE) AS WARR_EDATE	\n");
        	sqlstr.append("	    , MAX(GUAR_ONOFF_NM) AS GUAR_ONOFF_NM	\n");
        	sqlstr.append("	    , MAX(GUAR_ONOFF_YN) AS GUAR_ONOFF_YN	\n");
        	sqlstr.append("	    , MAX(GRTREQNO) AS GRTREQNO	\n");        	
        	sqlstr.append("	     FROM (	\n");
        	sqlstr.append("	              SELECT WARR_GUBUN	\n");
        	sqlstr.append("	                   , F_GET_CODE_NAME('GAUR_KIND',WARR_GUBUN) AS WARR_GUBUN_NM	\n");
        	sqlstr.append("	                   , MOD_NO	\n");
        	sqlstr.append("	                   , WARR_SEQ	\n");
        	sqlstr.append("	                   , WARR_WAMT	\n");
        	sqlstr.append("	                   , VIEW_DIV	\n");
        	sqlstr.append("	                   , CAUSECODE	\n");
        	sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',CAUSECODE) AS CAUSECODE_NM	\n");
        	sqlstr.append("	                   , GUAR_ORGAN_CODE	\n");
        	sqlstr.append("	                   , F_GET_CODE_NAME('GUAROFFICEID',GUAR_ORGAN_CODE) AS WARR_GOFFICE_ID	\n");
        	sqlstr.append("	                   , F_GET_CODE_NAME('GUARORGAN',GUAR_ORGAN_CODE) AS WARR_GOFFICE	\n");
        	sqlstr.append("	                   , GUAR_STATUS	\n");
        	sqlstr.append("	                   , F_GET_CODE_NAME('GUAR_STATUS',GUAR_STATUS) AS GUAR_STATUS_NM	\n");
        	sqlstr.append("	                   , PAPERDATE	\n");
        	sqlstr.append("	                   , WARR_WGNO	\n");
        	sqlstr.append("	                   , WARR_SDATE	\n");
        	sqlstr.append("	                   , WARR_EDATE 	\n");
        	sqlstr.append("	                   , DECODE(GUAR_ONOFF_YN,'Y','온라인','N','오프라인') AS GUAR_ONOFF_NM	\n");
        	sqlstr.append("	                   , GUAR_ONOFF_YN 	\n");
        	sqlstr.append("	                   , GRTREQNO 	\n");        	
        	sqlstr.append("	                FROM CONT_PUR_WARR 	\n");
        	sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ params.get("selectSubContModId") +"' 	\n");	//
        	sqlstr.append("	                     AND WARR_GUBUN IN ('5')	\n");
        	sqlstr.append("		\n");
        	sqlstr.append("	              UNION ALL	\n");
        	sqlstr.append("		\n");
        	sqlstr.append("	              SELECT '5' AS WARR_GUBUN	\n");
        	sqlstr.append("	                   , '하자이행보증' AS WARR_GUBUN_NM	\n");
        	sqlstr.append("	                   , MOD_NO	\n");
        	sqlstr.append("	                   , NULL AS WARR_SEQ	\n");
        	sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        	sqlstr.append("	                   , 'PAYREQ' AS VIEW_DIV	\n");
        	sqlstr.append("	                   , '10' AS CAUSECODE	\n");
        	sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE','10') AS CAUSECODE_NM	\n");
        	sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
        	sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");        	
        	sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
        	sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
        	sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
        	sqlstr.append("	                   , '' AS PAPERDATE	\n");
        	sqlstr.append("	                   , '' AS WARR_WGNO	\n");
        	sqlstr.append("	                   , '' AS WARR_SDATE	\n");
        	sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
        	sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
        	sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
        	sqlstr.append("	                   , '' AS GRTREQNO 	\n");        	
        	sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
        	sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ params.get("selectSubContModId") +"'	\n");		//"+ params.get("selectSubContModId") +"
        	sqlstr.append("	                     AND B.DEF_GUAR_AMT > 0	\n");
        	sqlstr.append("	        )        	\n");
        	sqlstr.append("	GROUP BY  WARR_GUBUN, WARR_GUBUN_NM, CAUSECODE, CAUSECODE_NM	\n");
       	
            //System.out.println(sqlstr);
            search.setStatement(sqlstr.toString());
            listRowSet = search.execute();           
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getContOutWarr() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            if( rowObject == null ) rowObject = new ValueObject();
        }
        return listRowSet;
    }  

    private String getDigit(RowSet listRowSet, String currencyGB) {
    	ValueObject row = null;
        int size = (listRowSet != null ? listRowSet.size() : 0);

	    if( listRowSet != null && listRowSet.size() > 0) {

            for( int i = 0 ; i < size ; i++ ) {

                row = RowSetUtility.getValueObject(listRowSet.getRow(i));
                
                if (currencyGB.equals((String)row.get("DETAILCD"))) {
                	return (String)row.get("CHG_CODE01");
                }
            }
	    }
	    
	    return "6";
    }
    
	public RowSet getUseCurrencyGBRowSet() {

        Search search = new SearchImpl();
        RowSet rowSet = null;
        String sqlstr = "";
        try {
        	
        	sqlstr += " SELECT DETAILCD, DETAILNM, F_GET_CURRENCY_DIGIT(DETAILCD) CHG_CODE01 FROM EBID_COM911T      	\n"
            		+ " WHERE BASECD = '911'                    														\n"
            		+ " ORDER BY CHG_CODE20                       														\n" ; 
    
            search.setStatement(sqlstr);
            rowSet = search.execute();
        }
        catch(Exception e) {
    
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
        }
        finally {
            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }    
    
    /** 첨부서류조회 
     * @return
     * @throws Exception
     */
    public RowSet getfileListRowSet(String payreqId, String code) throws Exception {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
        try {

    		sql.append("	SELECT distinct A.DETAILCD,								\n");
    		sql.append(" 		   A.DETAILNM,								\n");
    		sql.append(" 		   B.ID AS DOC_CD							\n");
    		sql.append("	FROM EBID_COM911T A, PUB0510T B				\n");
    		sql.append("	WHERE A.BASECD = 'PUR_PAY_FILE'					\n");
    		sql.append("	AND A.DETAILCD = B.ID(+)						\n");
    		//sql.append("	AND A.CHG_CODE01 = '"+code+"'					\n");
    		sql.append("	AND B.FILE_GB(+) = '"+payreqId+"'				\n");
    		//sql.append("	AND DETAILCD NOT IN ('560', '570')				\n");
    		sql.append("	ORDER BY A.DETAILCD								\n");
    		
    		//System.out.println(sql);
        	
            search.setStatement(sql.toString());
            
            listRowSet = search.execute();
            WAFLogger.debug(sql.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;
    }

    /**
	 * @param 
	 * @param 
	 * @return ValueObject
	 */
	public String  getGubun(){

        Search search = new SearchImpl();
        String resultGubun = "";
        String sqlstr = "";
        String payreqId					= params.get("payreqId");				//지급신청ID	
        
        try {
            sqlstr += " SELECT distinct GUBUN             	\n"
                    + " FROM PMM_PAYREQ_ITEM    			\n"
                    + " WHERE PAYREQ_ID = ? 	     		\n";
             
            search.setStatement(sqlstr);
            search.addParameter(payreqId);
            RowSet listRowSet = search.execute();
            if(listRowSet.size() != 0){
            	ValueObject rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
            	resultGubun = rowObject.get("GUBUN");
            }
            
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			return resultGubun;
		}
	}
	
	/**
	 * Persistent 를 이용한 Delete 처리
	 * @param params
	 * @return
     * @throws Exception
	 */
	private boolean delPur1100t() {
		
		
		Persistent persistent_d = null;
		String sqlstr_d = "";

		try {
		
			//선급금 신청 상제 정보
			String payreqId					= params.get("payreqId");				//지급신청ID			
			conn.begin();
			
			persistent_d = new PersistentImpl(super.conn.getConnection());
			
			sqlstr_d = 	" DELETE                   		\n"
		              + " FROM   PUR1100T 				\n"
		              + " WHERE  PAYREQ_ID = ?        	\n";
		        
			persistent_d.setStatement(sqlstr_d);
			persistent_d.addParameter(payreqId);
			
			//System.out.println(sqlstr_d);
			//System.out.println(persistent_d.getParameters());
			
			persistent_d.execute();
			persistent_d.clearParameters();

			return true;
        }
        catch(Exception e) {
            super.doException(e);
            super.storage.setMessage(e.getMessage());
            super.storage.setDetailMessage(e);
            super.conn.setRollback();
            return false;
        }
        finally {
        	super.conn.end();
        }
	}

	/**
	 * Persistent 를 이용한 Delete 처리
	 * @param params
	 * @return
	 * @throws Exception
	 */
	private boolean delPmmPayreqItem() {
		
		
		Persistent persistent_d = null;
		String sqlstr_d = "";
		
		try {
			
			//선급금 신청 상제 정보
			String payreqId					= params.get("payreqId");				//지급신청ID			
			conn.begin();
			
			persistent_d = new PersistentImpl(super.conn.getConnection());
			
			sqlstr_d =" DELETE                   		\n"
					+ " FROM   PMM_PAYREQ_ITEM			\n"
					+ " WHERE  PAYREQ_ID = ?        	\n";
			
			persistent_d.setStatement(sqlstr_d);
			persistent_d.addParameter(payreqId);
			
			//System.out.println(sqlstr_d);
			//System.out.println(persistent_d.getParameters());
			
			persistent_d.execute();
			persistent_d.clearParameters();
			
			return true;
		}
		catch(Exception e) {
			super.doException(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			super.conn.setRollback();
			return false;
		}
		finally {
			super.conn.end();
		}
	}
	
	
	
	/**
	 * @param params
	 * @return
	 * @throws Exception
	 */
	private boolean updateShippingList() {
		
		String payreqId					= params.get("payreqId");				//지급신청ID
		
		Search search = new SearchImpl();
		String searchSql = "";
		String resultShippingListId = "";
		
		Persistent persistent_u = null;
		String sqlstr_u = "";
		
		try {
			
			conn.begin();
			
			persistent_u = new PersistentImpl(super.conn.getConnection());
			sqlstr_u 	= " UPDATE ETECEPC.FM_SHIPPING_LIST 				\n"
						+ "    SET RECEIPT_NO = NULL		        			\n"
						+ "  WHERE SHIPPING_LIST_ID = ?        					\n";
			persistent_u.setStatement(sqlstr_u);
			
			searchSql	+= " SELECT SHIPPING_LIST_ID             	\n"
						+ "  FROM ETECEPC.FM_SHIPPING_LIST    				\n"
						+ "  WHERE RECEIPT_NO = ?     		\n";
			
			search.setStatement(searchSql);
			search.addParameter(payreqId);
			//System.out.println(searchSql);
			//System.out.println(search.getParameters());
			RowSet listRowSet = search.execute();
			if(listRowSet.size() != 0){
				for(int i=0; i<listRowSet.size(); i++){
					ValueObject rowObject = RowSetUtility.getValueObject(listRowSet.getRow(i));
					resultShippingListId = rowObject.get("SHIPPING_LIST_ID");
					persistent_u.addParameter(resultShippingListId);
					//System.out.println(sqlstr_u);
					//System.out.println(persistent_u.getParameters());
					persistent_u.execute();
					persistent_u.clearParameters();
				}
			}
			return true;
		}
		catch(Exception e) {
			super.doException(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			super.conn.setRollback();
			return false;
		}
		finally {
			super.conn.end();
		}
	}
	
	/**
	 * @param params
	 * @return
	 * @throws Exception
	 */
	private boolean updateWarehouse() {
		
		String payreqId					= params.get("payreqId");				//지급신청ID
		
		Search search = new SearchImpl();
		String searchSql = "";
		String resultWarehouseId = "";
		
		Persistent persistent_u = null;
		String sqlstr_u = "";
		
		try {
			
			conn.begin();
			
			persistent_u = new PersistentImpl(super.conn.getConnection());
			sqlstr_u 	= " UPDATE ETECEPC.FM_WAREHOUSE 					\n"
						+ "    SET RECEIPT_NO = NULL		        		\n"
						+ "  WHERE WAREHOUSE_ID = ?        					\n";
			persistent_u.setStatement(sqlstr_u);
			
			searchSql	+= " SELECT WAREHOUSE_ID			            \n"
						+ "  FROM ETECEPC.FM_WAREHOUSE    				\n"
						+ "  WHERE RECEIPT_NO = ?     					\n";
			
			search.setStatement(searchSql);
			search.addParameter(payreqId);
			//System.out.println(searchSql);
			//System.out.println(search.getParameters());
			RowSet listRowSet = search.execute();
			if(listRowSet.size() != 0){
				for(int i=0; i<listRowSet.size(); i++){
					ValueObject rowObject = RowSetUtility.getValueObject(listRowSet.getRow(i));
					resultWarehouseId = rowObject.get("WAREHOUSE_ID");
					persistent_u.addParameter(resultWarehouseId);
					//System.out.println(sqlstr_u);
					//System.out.println(persistent_u.getParameters());
					persistent_u.execute();
					persistent_u.clearParameters();
				}
			}
			return true;
		}
		catch(Exception e) {
			super.doException(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			super.conn.setRollback();
			return false;
		}
		finally {
			super.conn.end();
		}
	}   
}
