/**
 *
 * @(#)author  [OK]
 * @(#)version 1.0
 * @(#)date    2014-08-07
 * @(#)since   JDK 1.6.2
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */
package kr.co.udapsoft.ebid.buyer.payment.purchase.biz;


import java.util.ArrayList;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.base.WAFMessages;
import kr.co.hsnc.common.logger.Logger;
import kr.co.hsnc.common.sql.Row;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.common.sender.MessageSend;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import oracle.toplink.internal.ox.StrBuffer;

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

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

	public void doBiz() throws Exception {

		if(storage.compareEvent("")){
			
			//계약정보, 세금계산서정보, 대금정보  
			ValueObject prePayment = getPayItem(params);
			String PayItemListId = prePayment.get("PAYREQ_ID");
			
			//환률기준일자
			String rateDy = prePayment.get("TAX_DY");
			String curAcmpApplyamt = prePayment.get("CUR_ACMP_APPLYAMT");
			String curReaAmt = prePayment.get("CUR_REQ_AMT");
			String inputVat = prePayment.get("CHG_CODE10");
			
			//분계유형
			ValueObject contInfo = getContInfo(params);    
            storage.addValueObject("ContInfo", contInfo);
            
            String curGb =  prePayment.get("CURRENCY_GB");
			String rateEx = "0";
			String rateAmt = "0";
			String rateVamt = "0";
			if(!"KRW".equals(curGb)){
				ValueObject rowObject = getRageEx(rateDy, curGb, curReaAmt ,curAcmpApplyamt, inputVat, PayItemListId);
				rateEx = rowObject.get("RATE");
				rateAmt = rowObject.get("RATE_AMT");
				rateVamt = rowObject.get("RATE_VAMT");
			}
			prePayment.set("rateEx", rateEx);
			prePayment.set("rateAmt", rateAmt);
			prePayment.set("rateVamt", rateVamt);
			
			//보증정보
			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")){
			
			try {
			
				//C:토건, B:플랜트 구분조회
				String gubun = getGubun();
				
				//구매_대금지급신청 삭제
				delPur1100t();
				
				//구분에 따른 입고 테이블 변경
				if(gubun.equals("C")){
					//updatePmmStock(); 2018.11.27 업데이트 대상 없음. 
				}else{
					updateShippingList();
					updateWarehouse();
				}
				
				//구매_대금지급 내역 삭제
				delPmmPayreqItem();
				
				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_u = null;
			String sqlstr_u = "";

			Persistent persistent_u2 = null;
			String sqlstr_u2 = "";
			
			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 curReqAmt				= params.get("curReqAmt");				//금회 실지금	공급가액
				String curReqVatamt				= params.get("curReqVatamt");			//금회 실지금	부가세
				String curReqApplyamt			= params.get("curReqApplyamt");			//금회 실지금	합계
				
				String curPayedSupplyamt		= params.get("curPayedSupplyamt");		//누계 실지금	공급가액
				String curPayedVat				= params.get("curPayedVat");			//누계 실지금	부가세
				String curPayedAmt				= params.get("curPayedAmt");			//누계 실지금	합계
				
				String reMarks					= params.get("reMarks");				//특기사항
				
				String statusCd = params.get("statusCd");
				if(!"03".equals(statusCd)){
					statusCd					= "01";									//상태(작성중)
				}
				String userId					= storage.getUser().get("ACCT_ID");		//로그인사용자				
				String jigubsayu				= params.get("jigubsayu");
				
				//분계유형
				String acctGb					= params.get("acct_gb");				//분계유형
				String guaGb					= params.get("proof_cd");				//
				String payGroup					= params.get("pay_group");				//
				String paymentTerms				= params.get("payment_terms_id");			//
				
				//환률정보
				String rateAmt					= params.get("rateAmt").replace(",", "");				
				String rateVamt					= params.get("rateVamt").replace(",", "");			
				
				String creBy					= params.get("creBy");
				String updBy					= params.get("updBy");
				String by						= "";
				if("".equals(creBy)){
					by = updBy;
				}else{
					by = creBy;
				}
				
				conn.begin();
				
				persistent_u = new PersistentImpl(super.conn.getConnection());
				sqlstr_u = " UPDATE PUR1100T	      														\n"
			              + " SET    TAX_METHOD_CD    			= ?       , 					            \n"
			              + "        REQ_DATE 					= to_date(?,'YYYY-MM-DD')  ,	            \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"
			              + "        CUR_REQ_AMT				= ?       , 					            \n"
			              + "        CUR_REQ_VATAMT				= ?       , 					            \n"
			              + "        CUR_REQ_APPLYAMT			= ?       , 					            \n"
			              + "        CUR_PAYED_SUPPLYAMT		= ?       , 					            \n"
			              + "        CUR_PAYED_VAT				= ?       , 					            \n"
			              + "        CUR_PAYED_AMT				= ?       , 					            \n"
			              
			              + "        REQ_STATUS					= ?       , 					            \n"
			              + "        RE_MARKS					= ?       , 					            \n"
			              + "        CRE_BY      				= ?       , 					            \n"
			              + "        UPD_BY      				= ?       , 					            \n"
						  + "        ACCT_GB      				= ?       , 					            \n"
						  + "        PAY_GROUP      			= ?       , 					            \n"
						  + "        GUA_GB      				= ?       , 					            \n"
						  + "        PAYMENT_TERMS      		= ?       , 					            \n"
						  
						  + "        CUR_REQ_APPLYAMT_KRW      	= ?       , 					            \n"
						  + "        CUR_REQ_VATAMT_KRW      	= ?       , 					            \n"
						  
						  + "        DEPOSIT_RESN		      	= ?       , 					            \n"
						  + "        RTN_REASON    				= ''      , 					            \n"
						  + "        TRANS_CD    				= 'N'      , 					            \n"
						  
						  + "        CRE_DATE    				= sysdate , 					            \n"
			              + "        UPD_DATE    				= sysdate ,									\n"
			              + "        SUPP_DIRECT_YN    			= ?			             					\n"  //2016.6.15 하도급직불 
			              + " WHERE  PAYREQ_ID      			= ?        									\n" ;
				  
				persistent_u.setStatement(sqlstr_u);
					
				persistent_u.addParameter(taxMethodCd);
				persistent_u.addParameter(taxDy);
				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(curReqAmt.replaceAll(",", ""));
				persistent_u.addParameter(curReqVatamt.replaceAll(",", ""));
				persistent_u.addParameter(curReqApplyamt.replaceAll(",", ""));
				persistent_u.addParameter(curPayedSupplyamt.replaceAll(",", ""));
				persistent_u.addParameter(curPayedVat.replaceAll(",", ""));
				persistent_u.addParameter(curPayedAmt.replaceAll(",", ""));
				
				persistent_u.addParameter(statusCd);
				persistent_u.addParameter(reMarks);
				persistent_u.addParameter(by);
				persistent_u.addParameter(userId);
				//분계유형
				persistent_u.addParameter(acctGb);
				persistent_u.addParameter(payGroup);
				persistent_u.addParameter(guaGb);
				persistent_u.addParameter(paymentTerms);
				
				persistent_u.addParameter(rateAmt);
				persistent_u.addParameter(rateVamt);
				
				persistent_u.addParameter(jigubsayu);
				persistent_u.addParameter(params.get("SUPP_DIRECT_YN"));
				
				persistent_u.addParameter(payreqId);	//지급신청ID
					
				WAFLogger.debug(sqlstr_u);
				WAFLogger.debug(persistent_u.getParameters());
					
				persistent_u.execute();
				persistent_u.clearParameters();

				/*
				persistent_u2 = new PersistentImpl(super.conn.getConnection());
				sqlstr_u2 = " UPDATE ETEC_ERPIF.TAX_INT_INFO											\n"
						+ " SET    RTN_REASON    		= ''									\n"
						+ "        , RTN_TAX_CD    		= ''									\n"
						+ " WHERE  PAY_KEY      		= ?        								\n"
						;			  
				persistent_u2.setStatement(sqlstr_u2);
				persistent_u2.addParameter(payreqId);
					
				WAFLogger.debug(sqlstr_u2);
				WAFLogger.debug(persistent_u2.getParameters());
					
				persistent_u2.execute();
				persistent_u2.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("REQ_DATA")){
			
			Persistent persistent_u = null;
			String sqlstr_u = "";

			try {
			
				//대금 신청 상제 정보
				String payreqId					= params.get("payreqId");				//지급신청ID				
				String statusCd					= "02";						//상태(01:작성중, 02:대금요청, 03:대금신청, 04:결재중, 10:결재완료, 99:반려)
				String userId					= storage.getUser().get("ACCT_ID");		//로그인사용자
				
				conn.begin();
				
				persistent_u = new PersistentImpl(super.conn.getConnection());

				sqlstr_u = " UPDATE PUR1100T      														\n"
				          + " SET    REQ_STATUS    				= ?       ,									\n"
				          + "        UPD_BY      				= ?       , 					            \n"
				          + "        UPD_DATE    				= sysdate									\n"
				          + " WHERE  PAYREQ_ID      			= ?        									\n" ;
				  
				persistent_u.setStatement(sqlstr_u);
				
				persistent_u.addParameter(statusCd);
				persistent_u.addParameter(userId);
				persistent_u.addParameter(payreqId);
				
				WAFLogger.debug(sqlstr_u);
				WAFLogger.debug(persistent_u.getParameters());
				
				persistent_u.execute();
				persistent_u.clearParameters();
				
				String resUserName = params.get("resUserName");			//계약 담당자명
				String resHp = params.get("resHp").replaceAll("-", "");	//계약 담당자 핸드폰번호
				String resEmail = params.get("resEmail");				//계약 담당자 이메일
				String resDivsion = params.get("resDivsion");			//계약 담당자 부서

				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.Application.Request");
	        	WAFLogger.debug(smsContent);
	        	messageSend.sendSMS(recvAddress, storage.getUser().get("USER_PHONE"), storage.getUser().get("ACCT_ID"), smsContent); //2015.9.21 구매대금신청 납품 resHp
				
	        	//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.계약담당자:"+resDivsion+"/"+resUserName+"(연락처 :"+resHp+")</BR>");
	        	mailContent.append("</font>");
	        	messageSend.sendEmail(eMail, "partner@sgc.co.kr", "대금 신청 요청 메일입니다.", "대금 신청 요청 메일입니다.", mailContent);	//(받는주소, 보낸주소, 제목, 소제목, 내용)
	        	
				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("BUDGET_CHECK")){
			
			WAFLogger.debug("BUDGET_CHECK");
    		
			String dept_cd   = params.get("dept_cd");
    		String checkFlag = budgetCheck(dept_cd); 
    		ValueObject vo = new ValueObject();
    		vo.set("checkFlag", checkFlag);
    		storage.addValueObject("VALUEOBJECT", vo);
    		storage.setSuccess(true);
    		
		}else if(storage.compareEvent("GET_BUDGET_AMT")){
			//2018.11.26 예산체크 호출 없음.
			WAFLogger.debug("GET_BUDGET_AMT");
			
    		ValueObject budgetAMTVO = new ValueObject();
    		ValueObject checkVO = new ValueObject();
    		String payreq_id = params.get("payreqId");
    		
    		// 선급공제 시 같은 PAYREQ_ID 이므로 중복제거한다.
    		String check_id[] = payreq_id.split(";");
    		ArrayList<String> list = new ArrayList<String>();
    		for(int i=0; i<check_id.length ; i++){
    			if(!list.contains(check_id[i])) list.add(check_id[i]);
    		}
    		String d_check_id[] = new String[list.size()];
    		list.toArray(d_check_id);
    		
    		for(int i=0; i<d_check_id.length ; i++){
    			String enterDt = params.get("date1").replaceFirst("-", "").substring(0, 6);
    			String dept_cd = params.get("dept_cd");
    			String pItem = params.get("pItem");
    			//budgetAMTVO = getBudgetAMT(enterDt, d_check_id[i], dept_cd, pItem);//2018.11.26 예산체크 호출 없음.
    			if(budgetAMTVO.get("CNT").equals("999")){
    				
	    		}else{
	    			double calFlag = Double.parseDouble(budgetAMTVO.get("CAL_AMT"));
	    			// -금액이 여러건일 경우 마지막 budgetAMTVO 를 저장하여 반환하여 Alert 표기
	    			if(0 > calFlag){
	    				checkVO = budgetAMTVO;
	    			}
	    		}
    		}
    		if(checkVO.size() > 0){
    			budgetAMTVO = checkVO;
    		}
    		
    		storage.addValueObject("VALUEOBJECT", budgetAMTVO);
    		storage.setSuccess(true);

		}else if(storage.compareEvent("SAVE_ATTC")){
			
			//기타서류 첨부파일 조회
			String payreqId					= params.get("payreqId");				//지급신청ID
		    RowSet purchaseFileRow = getfileListRowSet("PURCHASE_"+payreqId, "");
		    
		    String docPayReq 			= "N";	//대금청구서
		    String docMrr				= "N";	//MRR
		    String docIteminputconfirm 	= "N";	//원자재입고확인서
		    String docInspectionReport 	= "N";	//Inspectin Reqport
		    String docConfirm		 	= "N";	//납품확인서
		    String docFinalDocuments	= "N";	//Final Documents 제출확인서
		    String docGuarConst			= "N";	//계약이행보증보험증권
		    String docGuarAs			= "N";	//하자이행보증보험증권
		    String docWorkqa			= "N";	//기성검사보고서
		    String docShip				= "N";	//선적서류
		    String docComplete			= "N";	//준공검사보고서
		    String docConstCopy			= "N";	//계약서사본
		    String docEtc				= "N";	//기타
		    String docTestrun			= "N";	//시운전안전보고서
		    String docMc				= "N";	//M/C
		    String docTaxOrInvoice		= "N";	//TaxOrInvoice

		    for(int i=0; i<purchaseFileRow.size() ; i++){
		    	
		    	Row row = purchaseFileRow.getRow(i);
		    	if(row.get("DOC_CD").equals("110")){	//대금청구서
		    		docPayReq = "Y";
		    	}else if(row.get("DOC_CD").equals("120")){	//계약이행 보증보험 증권
		    		docGuarConst = "Y";
		    	}else if(row.get("DOC_CD").equals("130")){	//지급이행 보증보험 증권
		    		docMrr = "Y";
		    	}else if(row.get("DOC_CD").equals("140")){	//하자이행 보증보험 증권
		    		docGuarAs = "Y";
		    	}else if(row.get("DOC_CD").equals("150")){	//원자재입고확인서
		    		docIteminputconfirm = "Y";
		    	}else if(row.get("DOC_CD").equals("160")){	//MRR
		    		docConfirm = "Y";
		    	}else if(row.get("DOC_CD").equals("170")){	//거래명세표
		    		docWorkqa = "Y";
		    	}else if(row.get("DOC_CD").equals("180")){	//설치완료확인서
		    		docComplete = "Y";
		    	}else if(row.get("DOC_CD").equals("190")){	//시운전안전보고서
		    		docTestrun = "Y";
		    	}else if(row.get("DOC_CD").equals("200")){	//Inspection Report
		    		docInspectionReport = "Y";
		    	}else if(row.get("DOC_CD").equals("210")){	//선적서류
		    		docShip = "Y";
		    	}else if(row.get("DOC_CD").equals("220")){	//Final Documents
		    		docFinalDocuments = "Y";
		    	}else if(row.get("DOC_CD").equals("230")){	//계약서 사본
		    		docConstCopy = "Y";
		    	}else if(row.get("DOC_CD").equals("240")){	//기타
		    		docEtc = "Y";
		    	}else if(row.get("DOC_CD").equals("250")){	//M/C
		    		docMc = "Y";
		    	}else if(row.get("DOC_CD").equals("260")){	//docTax or Invoice
		    		docMc = "Y";
		    	}
		    }
		    
			Persistent persistent_u = null;
			String sqlstr_u = "";

			try {
			
				conn.begin();
				persistent_u = new PersistentImpl(super.conn.getConnection());
				sqlstr_u = " UPDATE PUR1100T      															\n"
				          + " SET    DOC_PAYREQ    				= ?       ,									\n"
				          + "        DOC_GUAR_CONST      		= ?       , 					            \n"
				          + "        DOC_MRR      				= ?       , 					            \n"
				          + "        DOC_GUAR_AS      			= ?       , 					            \n"
				          + "        DOC_ITEMINPUTCONFIRM      	= ?       , 					            \n"
				          + "        DOC_CONFIRM      			= ?       , 					            \n"
				          + "        DOC_WORKQA      			= ?       , 					            \n"
				          + "        DOC_COMPLETE      			= ?       , 					            \n"
				          + "        DOC_TESTRUN      			= ?       , 					            \n"
				          + "        DOC_INSPECTION_REPORT      = ?       , 					            \n"
				          + "        DOC_SHIP      				= ?       , 					            \n"
				          + "        DOC_FINAL_DOCUMENTS      	= ?       , 					            \n"
				          + "        DOC_CONST_COPY      		= ?       , 					            \n"
				          + "        DOC_ETC      				= ?       , 					            \n"
				          + "        DOC_MC      				= ? 	  ,     					        \n"
				          + "        DOC_TAX_INVOICE			= ? 	       					            \n"
				          + " WHERE  PAYREQ_ID      			= ?        									\n"
				          ;
				  
				persistent_u.setStatement(sqlstr_u);
				
				persistent_u.addParameter(docPayReq);
				persistent_u.addParameter(docGuarConst);
				persistent_u.addParameter(docMrr);
				persistent_u.addParameter(docGuarAs);
				persistent_u.addParameter(docIteminputconfirm);
				persistent_u.addParameter(docConfirm);
				persistent_u.addParameter(docWorkqa);
				persistent_u.addParameter(docComplete);
				persistent_u.addParameter(docTestrun);
				persistent_u.addParameter(docInspectionReport);
				persistent_u.addParameter(docShip);
				persistent_u.addParameter(docFinalDocuments);
				persistent_u.addParameter(docConstCopy);
				persistent_u.addParameter(docEtc);
				persistent_u.addParameter(docMc);
				persistent_u.addParameter(docTaxOrInvoice);
				persistent_u.addParameter(payreqId);
				
				WAFLogger.debug(sqlstr_u);
				WAFLogger.debug(persistent_u.getParameters());
				
				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("SAVE_DRAFT")){
			
			String  signClassCd = "paymentapplication";
			//결재 가능여부 확인
			String validation = "";
			validation = iNisDraftable(signClassCd, "PUR1100T", "PAYREQ_ID", params.get("payreqId"));
			super.storage.setSuccess(true);
			super.storage.setMessage(validation);
		
		} else if(storage.compareEvent("GET_ATTACHFILE")){
			
			//첨부파일
			ValueObject AttachFile = getfilelist("PURCHASE_"+params.get("payreqId"),"","AttachFile");
	        storage.addValueObject("VALUEOBJECT", AttachFile);
	        
		} else if(storage.compareEvent("CHK_REJECT")){
			
			String resultChk = "1";	//불가
			String status = "";
			
			status = getRejectionCheck();
			
			if(!status.equals("04")){	//결재중
				resultChk = "0";	//가능
			} 
    		ValueObject vo = new ValueObject();
    		vo.set("checkFlag", resultChk);
    		storage.addValueObject("VALUEOBJECT", vo);
    		storage.setSuccess(true);

		}else if(storage.compareEvent("GET_LIST")){
			
			params.set("sheet1", getList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		} else if(storage.compareEvent("CHK_APPVAR")){
			
			String resultChk = "1";	//불가
			String status = "";
			
			status = getRejectionCheck();
			
			if(!status.equals("04")){	//결재중
				resultChk = "0";	//가능
				
				if("2".equals(params.get("btnAppReqSelect"))){
					status = getTaxOrInvoiceCnt();
					if(!status.equals("99")){
						resultChk = "0";	//
					}else{
						resultChk = "99";	//
					}
				}
			} 
    		ValueObject vo = new ValueObject();
    		vo.set("checkFlag", resultChk);
    		storage.addValueObject("VALUEOBJECT", vo);
    		storage.setSuccess(true);

		}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";
		    	}else if(rowObject.get("DETAILCD").equals("170") && rowObject.get("DOC_CD").equals("")){	//거래명세
		    		resultChk = "2";
		    	}
		    }
    		ValueObject vo = new ValueObject();
    		vo.set("checkFlag", resultChk);
    		storage.addValueObject("VALUEOBJECT", vo);
    		storage.setSuccess(true);
    		
		} else if(storage.compareEvent("GET_RATE")){
			
			String rateEx = "0";
			String rateAmt = "0";
			String rateVamt = "0";
			String rateDy = params.get("date1").replace("-", "");
			String curGb = params.get("conCurCd");
			String payreqId = params.get("payreqId");
			String curAcmpApplyamt = params.get("curAcmpApplyamt").replace(",", "");
			String curReaAmt = params.get("curReqAmt").replace(",", "");
			String inputVat = params.get("bugase");
			
			ValueObject vo = new ValueObject();
			
			if(!"KRW".equals(curGb)){
				
				ValueObject RageExValue = getRageEx(rateDy, curGb, curReaAmt ,curAcmpApplyamt, inputVat, payreqId);
				
				vo.set("rate", RageExValue.get("RATE"));
				vo.set("rateAmt", RageExValue.get("RATE_AMT"));
				vo.set("rateVamt", RageExValue.get("RATE_VAMT"));
				
			}else{
	
				vo.set("rate", rateEx);
				vo.set("rateAmt", rateAmt);
				vo.set("rateVamt", rateVamt);
			}
			
    		storage.addValueObject("VALUEOBJECT", vo);
    		storage.setSuccess(true);

		}
		
	}

    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"
    				+ "     (SELECT DETAILNM FROM EBID_COM911T WHERE BASECD = 'UNIT_KIND_GB' AND DETAILCD = 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);
    		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 signClassCd
	 * @param linkTable
	 * @param linkColum
	 * @param linkId
	 * @return 
	 * @throws Exception
	 */
    public static String iNisDraftable(String signClassCd, String linkTable, String linkColum, String linkId) throws Exception{
        Search search = new SearchImpl();        
        RowSet rowSet = null;
        String sqlstr = "";     
        String isDraftable = "0";
        try {

        	sqlstr += " SELECT DECODE(A.ATTR_6, NULL, '1', '2') AS APPR_STATUS 		\n"
        			+ " FROM SIGN001V A,  "+linkTable+" B          					\n"
        			+ " WHERE A.SIGN_INTERFACE_ID = B.APPR_ID       				\n"
        			+ " AND A.SIGN_CLASS_CD = '"+signClassCd+"'   					\n"
        			+ " AND B."+linkColum+" = '"+linkId+"'        					\n"
        			; 
            
        	search.setStatement(sqlstr);

            rowSet = search.execute();
            if( rowSet.size() > 0 ) {
            	isDraftable = rowSet.getRow(0).get("APPR_STATUS");
            }
            
        }
        catch(WAFSQLException se) {
            se.printStackTrace(System.out);
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
        }
        catch(Exception e) {
            Logger.err.printStackTrace(e);
        }
        finally {
            return isDraftable;
        }
    }
    
 
    // 2018.11.26 예산통제 확인 제외 
    //public ValueObject getBudgetAMT(String yymm, String payreq_id, String dept_cd, String pItem) 
   
    // 예산통제 현장인지 체크
    public String budgetCheck(String dept_cd) throws Exception {
    	ValueObject vo = new ValueObject();
    	String sqlstr = "";
    	String count = "";
    	Search search = new SearchImpl();
    	RowSet rowSet = null;
    	
    	try{
    		sqlstr = "		SELECT COUNT(*) AS CNT FROM ETEC_ERPIF.ACZ10100		\n"
    				+"		WHERE BUDGET_YN = 'Y'						\n"
    				+"		AND DEPT_CD = '"+ dept_cd +"'					\n";
    		
    		search.setStatement(sqlstr.toString());
    		WAFLogger.debug("budgetCheck sqlstr :"+sqlstr.toString());
	    	rowSet = search.execute();
	    	if (rowSet.size()>0){
    			vo.set("CHECK_YN", rowSet.getRow(0).get("CNT"));
	    	}
    		count = vo.get("CHECK_YN");
    		
    	}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> budgetCheck() : " + e.getMessage());
			WAFLogger.error(e);
			throw e;
		}
    	return count;
    }
	
    /**
     * 첨부파일 조회.
	 * @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"
        			+ "     ,F_GET_R_DEPT_CD(A.DEPT_CD) P_DEPT_CD          --현장코드 P158->1631로 변환한 DEPT 2015.10.7                     										\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"
        			+ "     ,NVL((SELECT ORD_SURTAX_AMT FROM PUR1000T WHERE PUR_CONT_ID = A.PUR_CONT_ID),0) AS  ORD_VAMT                       --발주부가세금액 		  																				\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"
        			+ "     ,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"
					+ "     ,CHK.PUR_CONT_MOD_ID AS MAX_PUR_CONT_MOD_ID																							\n"
					+ "     ,CHK.STATUS_CD AS MAX_STATUS_CD																										\n"
					+ "     ,D.CORP_ID                                                                													        \n"
					+ "     ,D.CORP_REG_NO                                                                  											        \n"
					+ "     ,DECODE(D.MFTR_DIV_CD, '', '03',   '1', '03', '2', '04') DOC_CD                                                                     \n"
					+ "     ,(SELECT NVL(CHG_CODE10, 'INPUT_VAT') -- 공급가액의 input_vat : 10% other 0%															\n"
					+ "     FROM EBID_COM911T     																												\n"
					+ "     WHERE BASECD = 'PROOF_CD'   																												\n"
					+ "     AND DETAILCD = NVL(A.GUA_GB, 'Z99')) CHG_CODE10																						\n"
					+ "     ,E.USER_NAME AS RES_USER_NAME																								\n"
					+ "     ,E.DIVISION    AS RES_DIVISION																								\n"
					+ "     ,E.HP              AS RES_HP																								\n"
					+ "     ,E.EMAIL         AS RES_EMAIL																								\n"
					+ "     ,E2.USER_NAME AS SUPP_USER_NAME																								\n"
					+ "     ,E2.DIVISION    AS SUPP_DIVISION																								\n"
					+ "     ,E2.HP              AS SUPP_HP																								\n"
					+ "     ,E2.EMAIL         AS SUPP_EMAIL																								\n"
					+ "     ,A.DEF_GUAR_AMT_RATE																										\n"
					+ "     , F.ARR_ID AS APPR_ARR_ID																										\n"
					+ "     , F.DOC_CD AS APPR_DOC_CD																										\n"
					+ "     , F.APPR_DY AS APPR_DY																										\n"
					+ "     , LENGTH(NVL(F.APPR_NOTE,'')) AS APPR_NOTE, F.ADM_YEAR																										\n"
					+ "    ,DECODE(Z1.RTN_REASON, '', '', '기재사항 정정 오류 - ')||Z1.RTN_REASON AS TAX_RTN_REASON																								\n"
					+ "    ,to_char(Z1.RTN_DATE, 'yyyy-mm-dd') AS TAX_RTN_DATE     --반려일자																								\n"
					
					+ "    ,B.F_TAX_DY AS F_TAX_DY   																							\n"
					+ "    ,B.F_CON_SUPPLY_AMT AS F_CON_SUPPLY_AMT   																						\n"

					+ "     ,B.CRE_BY							\n"
					+ "     ,B.UPD_BY							\n"
					+ "     ,B.SUPP_DIRECT_YN						\n"  //2016.6.15 하도급직불 
					+ "     ,NVL(G.CHK6,'A') FOREIGN_CHK					                               \n" 
					+ "     ,DECODE(NVL(G.CHK6,'N'), 'Y', '예', 'N', '아니요') FOREIGN_NM	  				   \n"  //2016.11.10 외환
					+ " 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"
        			+ "      ,cont_pur_supp E2          																												\n"
        			+ "      ,BID_FOREIGN_CHK G          				 \n"  //2016.11.10 외국환거래 신고대상 
        			+ "      , (SELECT A.CORP_ID, A.ORD_DOC_NO, A.APPR_TOT_RATE, MAX(A.APPR_DY) APPR_DY, A.ARR_ID, A.DOC_CD, MAX(A.APPR_NOTE) APPR_NOTE  \n"
        			+ "              ,MAX(B.ADM_YEAR) ADM_YEAR 															\n"
        			+ "          FROM SUPP_APPR A, SUPP_APPR_ITEM B , PUR1100T C						\n"
        			+ "         WHERE A.CORP_ID = B.CORP_ID AND A.ARR_ID = B.ARR_ID AND A.CONT_ID = C.PUR_CONT_ID AND A.DOC_CD IN ('03', '04') AND C.PAYREQ_ID  = '"+ params.get("payreqId") +"'     \n"
        			+ "         GROUP BY A.CORP_ID, A.ORD_DOC_NO, A.APPR_TOT_RATE, A.DOC_CD, A.ARR_ID)  F        																												\n"
					+ "      ,(SELECT B.PAY_KEY, B.RTN_REASON, B.RTN_DATE																		\n"
					+ "      FROM (SELECT PAY_KEY, MIN(TMPSLIP_NO) AS TMPSLIP_NO FROM ETEC_ERPIF.TAX_INT_INFO GROUP BY PAY_KEY) A																		\n"
					+ "                       ,ETEC_ERPIF.TAX_INT_INFO B     																												\n"
					+ "              WHERE 1=1        																												\n"
					+ "     AND A.PAY_KEY = B.PAY_KEY        																												\n"
					+ "     AND A.TMPSLIP_NO = B.TMPSLIP_NO       																												\n"
					+ "    ) Z1, 																												\n"
					+ "    (SELECT PUR_CONT_MOD_ID,STATUS_CD,PUR_CONT_ID FROM PUR2000T WHERE PUR_CONT_MOD_ID IN(SELECT MAX(PUR_CONT_MOD_ID) FROM PUR2000T GROUP BY PUR_CONT_ID)) CHK					\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 A.pur_cont_mod_id = E2.pur_cont_mod_id																												\n"
        			+ " AND A.ORD_DOC_NO = F.ORD_DOC_NO(+)																												\n"
        			+ " AND A.CORP_ID = F.CORP_ID(+)																												\n"
        			+ " AND B.PAYREQ_ID  = G.BID_DEG(+)			AND G.CHK5(+) = 'P'		   												\n"
        			+ " AND E.sign_seq = '1'																												\n"
        			+ " AND E2.sign_seq = '2'																												\n"
        			+ " AND Z1.PAY_KEY(+) = B.PAYREQ_ID																												\n"
        			+ " AND B.PUR_CONT_ID = CHK.PUR_CONT_ID(+)																												\n"
        			;

        	sqlstr += " AND B.PAYREQ_ID = '"+ params.get("payreqId") +"'																						\n"
        			;
        	
            search.setStatement(sqlstr);
            WAFLogger.debug(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 ValueObject getContInfo(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        ValueObject rowObject = null;
        
        try {

        	sqlstr += " SELECT 			  								  																						\n"
        			+ "     A.PUR_CONT_ID	                			  																						\n"
					+ "     ,B.ACCT_GB       																													\n"
					+ "     ,B.GUA_GB       																													\n"
					+ "     ,B.PAY_GROUP       																		 										\n"
					+ "     ,B.PAYMENT_TERMS       																												\n"
					+ "     ,B.PAY_ITEM ||'-'|| B.COST_CODE ||'-'|| B.COST_TYPE AS PAY_ITEM      																	\n"
					+ "     ,B.PAY_ITEM ||'-'|| B.COST_CODE ||'-'|| B.COST_TYPE ||'(' || F_GET_PAY_ITEM_EST_DESC(B.PAY_ITEM,B.COST_CODE,B.COST_TYPE,A.DEPT_CD)||')' AS PAY_ITEM_NM	      										\n"
					+ "     ,(SELECT AC_UNIT_CD FROM ACZ10100V Z WHERE  Z.DEPT_CD = A.DEPT_CD) AS  AC_UNIT_CD		      										\n"
					+ "     ,A.DEPT_CD		      										\n"
					+ "     ,B.PAY_DIV		      										\n"
					+ "     ,B.PAY_ITEM AS P_ITEM  										\n"
        			+ " FROM PUR1000T A, 																														\n"
					+ "      PUR1100T B,																															\n"
        			+ "      PUR1030T C		--2015.12.1 선급문제. 두건발생																									\n"
        			+ " WHERE 1=1																																\n"
        			+ " AND A.PUR_CONT_ID = B.PUR_CONT_ID																										\n"
        			+ " AND B.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID(+)	--2015.12.1 선급문제해결  																								\n";

        	sqlstr += " AND B.PAYREQ_ID = '"+ params.get("PAYREQID") +"'																						\n";
        	
            search.setStatement(sqlstr);
            WAFLogger.debug(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"
    				+ "     (SELECT DETAILNM FROM EBID_COM911T WHERE BASECD = 'UNIT_KIND_GB' AND DETAILCD = 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 = '"+PayItemListId+"'																				\n";
    		
    		search.setStatement(sqlstr);
    		WAFLogger.debug(sqlstr);
    		listRowSet = search.execute();
    		
    	}
    	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");
       	
        	WAFLogger.debug(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 B.FILE_GB(+) = '"+payreqId+"'				\n");
    		sql.append("	ORDER BY A.DETAILCD								\n");
    		
    		WAFLogger.debug(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);
			
			WAFLogger.debug(sqlstr_d);
			WAFLogger.debug(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);
			
			WAFLogger.debug(sqlstr_d);
			WAFLogger.debug(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);
			WAFLogger.debug(searchSql);
			WAFLogger.debug(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);
					WAFLogger.debug(sqlstr_u);
					WAFLogger.debug(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);
			WAFLogger.debug(searchSql);
			WAFLogger.debug(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);
					WAFLogger.debug(sqlstr_u);
					WAFLogger.debug(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();
		}
	}

    // 반려가능상태인지 확인한다.
    public String getRejectionCheck() throws Exception {
    	
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        String resultStatus = "";
        ValueObject rowObject = null;
        
        try {

        	sqlstr += " SELECT 			  								  																						\n"
					+ "     NVL(B.REQ_STATUS,'00') AS STATUS_CD											       															\n"
        			+ " FROM PUR1100T B																														\n"
        			+ " WHERE 1=1																																\n"
        			;

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

    public String getTaxOrInvoiceCnt() throws Exception {
    	
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        String resultStatus = "";
        ValueObject rowObject = null;
        
        try {

        	sqlstr += " SELECT 			  								  									\n"
					+ "     DECODE(COUNT(*), 0 , 99, 10) AS  STATUS_CD										\n"
        			+ " FROM EBID_COM911T A, PUB0510T B													\n"
        			+ " WHERE A.BASECD = 'PUR_PAY_FILE'														\n"
        			+ " AND A.DETAILCD = B.ID																\n"
        			+ " AND DETAILCD = '260'																\n"
        			;

        	sqlstr += " AND B.FILE_GB = 'PURCHASE_"+ params.get("payreqId") +"'								\n"
        			;
        	
            search.setStatement(sqlstr);
            WAFLogger.debug(sqlstr);
            listRowSet = search.execute();
            rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
            resultStatus = rowObject.get("STATUS_CD");
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getRejectionCheck() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {
            
            return resultStatus;
        }
    }
    public ValueObject getRageEx(String rateDy, String curGb, String curReaAmt ,String curAcmpApplyamt, String inputVat, String payReqId) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        ValueObject rowObject = null;
        String resultRateEx = "0";
        
        int segumRate = 0;
        if("INPUT_VAT".equals(inputVat)){
        	segumRate = 100 / 10;
        }
        
        try {

        	sqlstr += " SELECT A.RATE																									\n"
        		    + " 	   , A.RATE * TO_NUMBER("+curReaAmt+") AS RATE_AMT															\n" 
        		    + " 	   , DECODE('"+segumRate+"',0,0,A.RATE * TO_NUMBER("+curReaAmt+") / "+segumRate+") AS RATE_VAMT				\n" 
					+ " FROM (																											\n"
        			+ " 		SELECT ROUND(sum_item_amt / '"+curAcmpApplyamt+"' ,2) AS RATE											\n"
        			+ " 		FROM (																									\n"
        			+ " 				SELECT SUM(ITEM_AMT) AS sum_item_amt															\n"
        			+ " 				FROM PMM_PAYREQ_ITEM A																			\n"
        			+ " 				WHERE 1=1																						\n"
        			+ " 				AND A.PAYREQ_ID = '"+payReqId+"'																\n"
        			+ " 	 		 )																									\n"
        			+ " 	 ) A																										\n"
        			;
        			
        			
            search.setStatement(sqlstr);
            WAFLogger.debug(sqlstr);
            listRowSet = search.execute();
            rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
            //resultRateEx = rowObject.get("RATE");
            
        }
        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;
        }
    }
}
