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


import 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 oracle.toplink.internal.ox.StrBuffer;

import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

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

	public void doBiz() throws Exception {

		WAFLogger.debug("전자조달.대금관리.구매.선급금현황 상세 프로세스처리.[PrepaymentsBiz]");
		
		if(storage.compareEvent("")){
			
			WAFLogger.debug("전자조달 구매_선급금현황상세 INIT");
			WAFLogger.debug(params.get("selectSubContModId"));
			
			//ValueObject test = storage.getUser();
			//String test1 = test.get("REP_NAME");
			
			//신청구분ComboList
			RowSet rowSet = getPerpayReqDiv();
			storage.addRowSet("perpayReqDivComboList", rowSet);
			
			//기본 정보, 세금계산서 정보,선급금  
			ValueObject prePayment = getPrePayment(params);
			//환률기준일자
			String rateDy = prePayment.get("TAX_DY");
			String reqPrepaySupplyAmt = prePayment.get("REQ_PREPAY_SUPPLY_AMT");
			String inputVat = prePayment.get("CHG_CODE10");
			
			//분계유형
			ValueObject contInfo = getContInfo(params);
			String guaGb = contInfo.get("GUA_GB");
            storage.addValueObject("ContInfo", contInfo);
			
            //계약통화
			String curGb =  prePayment.get("CON_CUR_CD");
			String rateEx = "0";
			String rateAmt = "0";
			String rateVamt = "0";
			//if(!"KRW".equals(curGb) && !"Z99".equals(guaGb)){
			if(!"KRW".equals(curGb)){
				ValueObject rowObject = getRageEx(rateDy, curGb, reqPrepaySupplyAmt, inputVat);
				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);
			
			//첨부파일
	        storage.addValueObject("PREPAYMENTS_GIVEUP", getfilelist("PREPAYMENTS_GIVEUP",prePayment.get("PREPAY_ID"),"PREPAYMENTS_GIVEUP"));
	        storage.addValueObject("TAX_BILL", getfilelist("TAX_BILL",prePayment.get("PREPAY_ID"),"TAX_BILL"));

			//보증정보
			RowSet contOutWarr = getContOutWarr(params);
			storage.addRowSet("contOutWarr", contOutWarr);
			
	 	    // 각국 통화코드에 따른 소숫점 이하 자릿수 구하기
	        RowSet rowSetGb = getUseCurrencyGBRowSet();
	        storage.addRowSet("digitRowSet", rowSetGb);  
	        
	        // 계약통화 자릿수 구하기
	        String digit = getDigit(rowSetGb, (String)prePayment.get("CON_CUR_CD"));
	        //String digit = getDigit(rowSetGb, "USD");
	        prePayment.set("digit", digit);
	        
			storage.addValueObject("prePayment", prePayment);
		
		}else if(storage.compareEvent("GET_DATA")){
			//보증정보
			RowSet contOutWarr = getContOutWarr(params);
			storage.addRowSet("VALUEOBJECT", contOutWarr);
		
		}else if(storage.compareEvent("SAVE_DATA")){	//데이터 저장
			boolean success = false;
			success = saveData(params);
			storage.setSuccess(success);
		}else if(storage.compareEvent("RNNC_DATA")){
			boolean success = false;
			success = rnncData(params);
			storage.setSuccess(success);
			
		}else if(storage.compareEvent("DELETE_DATA")){	//데이터 삭제
			boolean success = false;
			success = deleteData(params);
			storage.setSuccess(success);
		}else if(storage.compareEvent("REQ_DATA")){		//기성신청 요청
			
			boolean success = false;
			success = reqData(params);

			String resUserName = params.get("resUserName");			//계약 담당자명
			String resHp = params.get("resHp").replaceAll("-", "");	//계약 담당자 핸드폰번호
			String resEmail = params.get("resEmail");				//계약 담당자 이메일
			String resDivision = params.get("resDivision");			//계약 담당자 부서

			String hp = params.get("hp").replaceAll("-", "");	//업체 담당자 핸드폰번호
			String eMail = params.get("eMail");					//업체 담당자 이메일
        	String memberName = params.get("memberName");		//업체명
        	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("Prepayments.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.계약담당자: "+resDivision+"/" +resUserName+"(연락처 :"+resHp+")</BR>");
        	mailContent.append("</font>");
        	//받는주소, 보내는주소
        	messageSend.sendEmail(eMail, resEmail, "선급금 신청 요청 메일입니다.", "선급금 신청 요청 메일입니다.", mailContent);

			storage.setSuccess(success);
		
		} 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("SAVE_SEGUM")){
			boolean success = false;
			success = segumData(params);
			storage.setSuccess(success);

		}else if(storage.compareEvent("SAVE_GUA")){
			boolean success = false;
			success = guaData(params);
			storage.setSuccess(success);
			
		} else if(storage.compareEvent("CHK_APPVAR")){
			
			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_RATE")){
			
			String rateEx = "0";
			String rateAmt = "0";
			String rateVamt = "0";
			String rateDy = params.get("date1").replace("-", "");
			String curGb = params.get("conCurCd");
			String reqPrepaySupplyAmt = params.get("reqPrepaySupplyAmt").replace(",", "");
			String inputVat = params.get("bugase");
			
			ValueObject vo = new ValueObject();
			
			if(!"KRW".equals(curGb)){
				
				ValueObject RageExValue = getRageEx(rateDy, curGb, reqPrepaySupplyAmt, inputVat);
				//rateEx = RageExValue.get("RATE");
				//rateAmt = rowObject.get("RATE_AMT");
				//rateVamt = rowObject.get("RATE_VAMT");
				
				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);

		}
		
	}

    /**
     * 첨부파일 조회.
	 * @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. 6. 23.
     *@since JDK1.7
     */
    public ValueObject getPrePayment(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        ValueObject rowObject = null;
        
        try {

        	sqlstr += " SELECT 			  								  																						\n"
        			+ "     A1.PUR_CONT_MOD_ID AS SUB_CONT_MOD_ID,		  																						\n"
        			+ "     A1.ORD_DOC_NO AS CON_DOC_NO,       			  																						\n"
        			+ "     A1.MOD_NO,		                			  																						\n"
        			+ "     A1.PUR_CONT_ID AS SUB_CONT_ID,     			  																						\n"
        			+ "     A1.MOD_GB,		                			  																						\n"
        			+ "     A1.DEPT_CD,		                			  																						\n"
        			+ "     A1.DEPT_NAME,              --현장명			  																						\n"
        			+ "     A1.CST_DOC_NAME AS BUILD_NAME,             --계약명																					\n"
        			+ "     A1.CURRENCY_GB AS CON_CUR_CD,             --계약통화																					\n"
        			//+ "     B1.REG_NO AS CORP_REG_NO,            --사업자번호																						\n"
        			+ "     substr(B1.REG_NO,0,3)||'-'||substr(B1.REG_NO,4,2)||'-'||substr(B1.REG_NO,6,5) AS CORP_REG_NO,       --사업자번호						\n"
        			+ "     B1.CORP_KOR,               --계약업체명	  																							\n"
        			+ "     B1.BOSS_NAME,               --대표이사 		  																						\n"
        			+ "     TO_CHAR(A1.ORD_START_DATE, 'YYYY-MM-DD') || ' ~ ' || TO_CHAR(A1.ORD_END_DATE, 'YYYY-MM-DD') AS START_END_WORK_DATE,					\n"
        			+ "     A1.ORD_AMT AS CON_AMT,                --계약금						                			  									\n"
        			+ "     A1.ORD_SUPPLY_AMT AS CON_SUPPLY_AMT,         --계약공급가					              			  									\n"
        			+ "     A1.ORD_SURTAX_AMT AS CON_SURTAX_AMT,         --계약 부가세			                			  									\n"
        			+ "     NVL(B1.PREPAY_AMT, 0) AS PREPAY_AMT,    	 --선급금액				                			  									\n"
        			+ "     NVL(B1.PREPAY_SUPPLY_AMT, 0) AS PREPAY_SUPPLY_AMT,  --선급금_공급가  											  						\n"
        			+ "     NVL(B1.PREPAY_SURTAX_AMT, 0) AS PREPAY_SURTAX_AMT,     --선급금액 부가세								    							\n"
        			+ "     TO_CHAR(TRUNC((B1.PREPAY_SUPPLY_AMT / A1.ORD_SUPPLY_AMT) * 100, 2), 'FM990.0') AS  PREPAY_AMT_PERCENT,    --선급금 요율								\n"
        			+ "     NVL(B1.REQ_PREPAY_AMT, 0) AS REQ_PREPAY_AMT, --선급 신청금액					           			  									\n"
        			+ "     NVL(B1.REQ_PREPAY_SUPPLY_AMT, 0) AS REQ_PREPAY_SUPPLY_AMT, --선급 신청공급가액		       			  									\n"
        			+ "     NVL(B1.REQ_PREPAY_SURTAX_AMT, 0) AS REQ_PREPAY_SURTAX_AMT,      --신청금액_부가세          			  									\n"
        			+ "     TO_CHAR(TRUNC((NVL(B1.REQ_PREPAY_SUPPLY_AMT, 0) / A1.ORD_SUPPLY_AMT) * 100, 2), 'FM990.0') AS REQ_PREPAY_PERCENT,									\n"
        			+ "     NVL(B1.PREPAY_AMT, 0) - NVL(B1.REQ_PREPAY_AMT, 0) AS GIVE_AMT,   --선급 포기금액 합계     			  									\n"
        			+ "     NVL(B1.PREPAY_SUPPLY_AMT, 0) - NVL(B1.REQ_PREPAY_SUPPLY_AMT, 0) AS GIVE_SUPPLY_AMT,   --선급 포기금액 공급가액							\n"
        			+ "     NVL(B1.PREPAY_SURTAX_AMT, 0) - NVL(B1.REQ_PREPAY_SURTAX_AMT, 0) AS GIVE_SURTAX_AMT,   --선급 포기금액_부과세							\n"
        			+ "     TO_CHAR(TRUNC(((NVL(B1.PREPAY_SUPPLY_AMT, 0) - NVL(B1.REQ_PREPAY_SUPPLY_AMT, 0)) / A1.ORD_SUPPLY_AMT) * 100, 2), 'FM990.0') AS GIVE_PERCENT,				\n"
        			+ "     B1.PREPAY_ID,       --선급ID											                			  									\n"
        			+ "     B1.PREPAY_PROC_GB AS STATUS_CD,       --선급상태						                			  									\n"
        			+ "     F_GET_CODE_NAME('PREPAY_STATUS_CD', NVL(B1.PREPAY_PROC_GB, '00')) AS STATUS_NM,          --선급상태명        			  							\n"
        			+ "     B1.CORP_ID AS CON_CORP_ID,  --계약업체ID								                			  									\n"
        			+ "     NVL(B1.TAX_METHOD_CD, '1') AS TAX_METHOD_CD,                --발행방법		    	  			  									\n"
        			+ "     NVL(B1.TAX_DY, TO_CHAR(SYSDATE, 'YYYY-MM-DD')) AS TAX_DY,   --작성일자	              			  									\n"
        			+ "     B1.TAX_CHARG_NM AS USER_NAME,   --담당자명										      			  									\n"
        			+ "     B1.TAX_CHARG_HP AS HP,            --담당자 핸드폰번호								       			  									\n"
        			+ "     B1.TAX_CHARG_EMAIL AS EMAIL,     --담당자 이메일  										   			  									\n"
        			+ "     NVL(B1.REQ_DIV_CD, '1') AS REQ_DIV_CD,   --신청구분					                			  									\n"
        			+ "     DECODE(B1.SUPP_SIGN_DT, null, '미서명', to_char(B1.SUPP_SIGN_DT, 'yyyy-mm-dd HH24:MI:SS') ) AS  CUST_SIGN_DATE,     --서명일시		\n"
        			+ "     B1.RTN_REASON,        --반려사유																										\n"
        			+ "     to_char(to_date(B1.RTN_DATE, 'yyyymmdd'), 'yyyy-mm-dd') AS RTN_DATE,     --반려일자													\n"
        			+ "     C1.USER_NAME AS RES_USER_NAME,													\n"
        			+ "     C1.HP AS RES_HP,													\n"
        			+ "     C1.EMAIL AS RES_EMAIL,													\n"
        			+ "     C1.DIVISION AS RES_DIVISION													\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(A1.GUA_GB, 'Z99')) CHG_CODE10																							\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"
					+ "     ,B1.CRE_BY							\n"
					+ "     ,B1.UPD_BY							\n"
					+ "     ,NVL(B1.SUPP_DIRECT_YN , 'N') AS SUPP_DIRECT_YN						\n"  //2016.12.23 하도급직불 
					+ "     ,NVL(G.CHK6,'A') FOREIGN_CHK					                               \n" 
					+ "     ,DECODE(NVL(G.CHK6,'N'), 'Y', '예', 'N', '아니요') FOREIGN_NM	  				   \n"  //2017.02.27 외환
					+ "     ,NVL(B1.ORG_AMT, 0) AS ORG_AMT    	 --원본_선급금액				               \n"  //2018.07.11원본금액 표기 추가 
        			+ "     ,NVL(B1.ORG_SUPPLY_AMT, 0) AS ORG_SUPPLY_AMT  --원본_선급금_공급가  			   \n"
        			+ "     ,NVL(B1.ORG_SURTAX_AMT, 0) AS ORG_SURTAX_AMT     --원본_선급금액 부가세			   \n"
        			+ "     ,B1.CHG_REASON          							--선급금 변경사유      			\n"
        			+ "     ,(SELECT COUNT(*) FROM PUR1030T WHERE PUR_CONT_ID = A1.PUR_CONT_ID) CNT         \n" //2018.07.27 삭제가능여부 check
        			+ " FROM 																																	\n"
        			+ "       PUR2000T A1,																														\n"
        			+ "       PUR1030T B1,																														\n"
        			+ "       CONT_PUR_SUPP C1,																														\n"
        			+ "       BID_FOREIGN_CHK G,          				 \n"  //2017.02.27 외국환거래 신고대상 
        			+ "      (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"
					+ " WHERE A1.PUR_CONT_MOD_ID = B1.PUR_CONT_MOD_ID(+)																						\n"
        			+ " AND A1.PUR_CONT_MOD_ID = C1.PUR_CONT_MOD_ID																						\n"
        			+ " AND C1.sign_seq = '1'																						\n"
        			+ " AND A1.CON_CLS_YN = '9'																													\n"
        			+ " AND B1.PREPAY_ID  = G.BID_DEG(+)	AND G.CHK5(+) = 'R'																											\n"
        			+ " AND Z1.PAY_KEY(+) = B1.PREPAY_ID																												\n"
        			;
        	sqlstr += " AND A1.PUR_CONT_MOD_ID = '"+ params.get("selectSubContModId") +"'																		\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. 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 ('3')	\n");
        	sqlstr.append("	                     AND CAUSECODE IN ('11')	\n");
        	sqlstr.append("		\n");
        	sqlstr.append("	              UNION ALL	\n");
        	sqlstr.append("		\n");
        	sqlstr.append("	              SELECT '3' AS WARR_GUBUN	\n");
        	sqlstr.append("	                   , '선급금보증' AS WARR_GUBUN_NM	\n");
        	sqlstr.append("	                   , B.MOD_NO	\n");
        	sqlstr.append("	                   , NULL AS WARR_SEQ	\n");
        	sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        	sqlstr.append("	                   , 'PREPAY' AS VIEW_DIV	\n");
        	sqlstr.append("	                   , '11' AS CAUSECODE	\n");
        	sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE','11') 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, PUR1030T C  	\n");
        	sqlstr.append("	               WHERE B.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID  	\n");
        	sqlstr.append("	                     AND C.PUR_CONT_MOD_ID = '"+ params.get("selectSubContModId") +"'	\n");
        	sqlstr.append("	                     AND C.REQ_PREPAY_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 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");
    		
    		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;
    }
	/**
	 * Persistent 를 이용한 Save 처리
	 * @param params
	 * @return
	 * * 입력받은 선급금 상세 정보를 등록합니다.
     * @param connection
     * @param pgmName
     * @param screenName
     * @param systemCode
     * @throws Exception
	 */
	private boolean saveData(ValueObject params) {
		
		ValueObject user = storage.getUser();
		
		Persistent persistent_u = null;
		String sqlstr_u = "";

		Persistent persistent_u1 = null;
		String sqlstr_u1 = "";

		Persistent persistent_u2 = null;
		String sqlstr_u2 = "";

		try {
		
			//선급금 신청 상제 정보
			String prepayId					= params.get("prepayId");				//선급ID
			String subContId				= params.get("subContId");				//외주계약ID
			String prepaySeq				= "1";									//선급금지급차수
			String corpId					= params.get("corpId");					//협력업체ID
			String prepayDate				= "";									//선급일자
			String perpayAmt				= params.get("perpayAmt");				//계약_선급금
			String perpaySupplyAmt			= params.get("subPrepaySupplyAmt");		//계약_선급금공급가	2018.07.11			
			//String subPrepaySupplyTamt		= params.get("subPrepaySupplyTamt");	//계약_선급금공급가_과세
			//String subPrepaySupplyNamt		= params.get("subPrepaySupplyNamt");	//계약_선급금공급가_면세
			String subPrepaySurtaxAmt		= params.get("subPrepaySurtaxAmt");		//계약_선급금부가세
			String reqPrepayAmt				= params.get("reqPrepayAmt");			//신청_선급금합계
			String reqPrepaySupplyAmt		= params.get("reqPrepaySupplyAmt");		//신청_선급금공급가
			String reqPrepaySurtaxAmt		= params.get("reqPrepaySurtaxAmt");		//신청_선급금부가세
			String prepayDesc				= "";									//적요
			String conCurCd					= params.get("conCurCd");				//계약통화
			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 perpayReqDiv				= params.get("perpayReqDiv");			//신청부분
			String statusCd					= "01";									//상태(01:작성중, 02:선급신청요청, 21:선급포기요청, 03:선금신청, 04:결재중, 10:선금완료, 11:포기완료, 00:신청대상, 91:반려)
			//if(params.get("statusCd").equals("91")){
				//statusCd = "91";
			//}
			
			String userId					= user.get("ACCT_ID");		//로그인사용자

			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 subContModId				= params.get("subContModId");
			
			String rateAmt					= params.get("rateAmt");				
			String rateVamt					= params.get("rateVamt");	
			
			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 PUR1030T      														\n"
					+ " SET    TAX_METHOD_CD    			= ?       , 					        \n"
					+ "        TAX_DY 						= ?       ,					            \n"
					+ "        PREPAY_DATE 					= TO_DATE(?,'YYYY-MM-DD'),	            \n"
					+ "        TAX_CHARG_NM					= ?       ,					            \n"
					+ "        TAX_CHARG_HP					= ?       ,					            \n"
					+ "        TAX_CHARG_EMAIL				= ?       ,					            \n"
					+ "        PREPAY_SUPPLY_AMT_KRW		= ?       ,					            \n"
					+ "        PREPAY_SURTAX_AMT_KRW		= ?       ,					            \n"
					+ "        REQ_DIV_CD					= ?       , 					        \n"
					+ "        REQ_PREPAY_AMT				= ?       , 		--신청선급금합계       \n"
					+ "        REQ_PREPAY_SUPPLY_AMT		= ?       , 		--신청선급금공급가     \n"
					+ "        REQ_PREPAY_SURTAX_AMT		= ?       , 		--신청선급금부가세     \n"
					+ "        PREPAY_PROC_GB				= ?		  , 					        \n"
					//+ "        PREPAY_DATE					= ?		  , 					        \n"
					+ "        CRE_BY      					= ?       , 				            \n"
					+ "        CRE_DATE    					= sysdate ,								\n"
					+ "        UPD_BY      					= ?       , 				            \n"
					+ "        UPD_DATE    					= sysdate ,								\n"
					+ "        SUPP_DIRECT_YN    		    = ? ,								    \n"  //2016.12.23 하도급직불 
					+ "        RTN_REASON    				= ''	  , 							\n"
					+ "        PREPAY_AMT				    = ?       , 		--선급금액 수정                \n"  //2018.07.11
					+ "        PREPAY_SUPPLY_AMT		    = ?       , 		--선급금공급가                 \n"
					+ "        PREPAY_SURTAX_AMT		    = ?       , 		--선급금부가세                 \n"
					+ "        CHG_REASON		            = ?         		--변경사유                       \n"
					+ " WHERE  PREPAY_ID      				= ?        								\n"
					;			  

			persistent_u.setStatement(sqlstr_u);
			persistent_u.addParameter(taxMethodCd);
			persistent_u.addParameter(taxDy.replaceAll("-", ""));
			persistent_u.addParameter(taxDy.replaceAll("-", ""));
			persistent_u.addParameter(taxChargNm);
			persistent_u.addParameter(taxChargHp);
			persistent_u.addParameter(taxChargEmail);
			
			persistent_u.addParameter(rateAmt.replaceAll(",", ""));
			persistent_u.addParameter(rateVamt.replaceAll(",", ""));
			
			persistent_u.addParameter(perpayReqDiv);
			persistent_u.addParameter(reqPrepayAmt.replaceAll(",", ""));
			persistent_u.addParameter(reqPrepaySupplyAmt.replaceAll(",", ""));
			persistent_u.addParameter(reqPrepaySurtaxAmt.replaceAll(",", ""));

			persistent_u.addParameter(statusCd);
			//persistent_u.addParameter(taxDy);
			persistent_u.addParameter(by);
			persistent_u.addParameter(userId);
			persistent_u.addParameter(params.get("SUPP_DIRECT_YN"));  //2016.12.23 하도급직불 
			
			persistent_u.addParameter(perpayAmt.replaceAll(",", ""));   //2018.07.11 선급금액 구매담당자 변경가능 
			persistent_u.addParameter(perpaySupplyAmt.replaceAll(",", ""));
			persistent_u.addParameter(subPrepaySurtaxAmt.replaceAll(",", ""));
			persistent_u.addParameter(params.get("chgReason"));
			
			persistent_u.addParameter(prepayId);
				
			WAFLogger.debug(sqlstr_u);
			WAFLogger.debug(persistent_u.getParameters());
				
			persistent_u.execute();
			persistent_u.clearParameters();
			
			persistent_u1 = new PersistentImpl(super.conn.getConnection());
			sqlstr_u1 = " UPDATE PUR2000T      												\n"
					+ " SET    ACCT_GB    			= ?       , 					        \n"
					+ "        GUA_GB 				= ?       ,					            \n"
					+ "        PAY_GROUP			= ?       ,					            \n"
					+ "        PAYMENT_TERMS		= ?       					            \n"
					+ " WHERE  PUR_CONT_MOD_ID      = ?        								\n"
					;			  
			persistent_u1.setStatement(sqlstr_u1);
			persistent_u1.addParameter(acctGb);
			persistent_u1.addParameter(guaGb);
			persistent_u1.addParameter(payGroup);
			persistent_u1.addParameter(paymentTerms);
			persistent_u1.addParameter(subContModId);
				
			WAFLogger.debug(sqlstr_u1);
			WAFLogger.debug(persistent_u1.getParameters());
				
			persistent_u1.execute();
			persistent_u1.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(prepayId);
				
			WAFLogger.debug(sqlstr_u2);
			WAFLogger.debug(persistent_u2.getParameters());
				
			persistent_u2.execute();
			persistent_u2.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 를 이용한 Save 처리
	 * @param params
	 * @return
	 * * 입력받은 선급금 상세 정보를 등록합니다.
	 * @param connection
	 * @param pgmName
	 * @param screenName
	 * @param systemCode
	 * @throws Exception
	 */
	private boolean rnncData(ValueObject params) {
		
		ValueObject user = storage.getUser();
		
		Persistent persistent_u = null;
		String sqlstr_u = "";
		
		Persistent persistent_u1 = null;
		String sqlstr_u1 = "";
		
		Persistent persistent_u2 = null;
		String sqlstr_u2 = "";

		Persistent persistent_u3 = null;
		StringBuffer sqlstr_u3 = new StringBuffer();
		
		try {
			
			//선급금 신청 상제 정보
			String prepayId					= params.get("prepayId");				//선급ID
			String subContId				= params.get("subContId");				//외주계약ID
			String prepaySeq				= "1";									//선급금지급차수
			String corpId					= params.get("corpId");					//협력업체ID
			String prepayDate				= "";									//선급일자
			String perpayAmt				= params.get("perpayAmt");				//계약_선급금
			String perpaySupplyAmt			= "";									//계약_선급금공급가
			String subPrepaySupplyTamt		= params.get("subPrepaySupplyTamt");	//계약_선급금공급가_과세
			String subPrepaySupplyNamt		= params.get("subPrepaySupplyNamt");	//계약_선급금공급가_면세
			String subPrepaySurtaxAmt		= params.get("subPrepaySurtaxAmt");		//계약_선급금부가세
			String reqPrepayAmt				= params.get("reqPrepayAmt");			//신청_선급금합계
			String reqPrepaySupplyAmt		= params.get("reqPrepaySupplyAmt");		//신청_선급금공급가
			String reqPrepaySurtaxAmt		= params.get("reqPrepaySurtaxAmt");		//신청_선급금부가세
			String prepayDesc				= "";									//적요
			String conCurCd					= params.get("conCurCd");				//계약통화
			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 perpayReqDiv				= params.get("perpayReqDiv");			//신청부분
			String statusCd					= "01";									//상태(01:작성중, 02:선급신청요청, 21:선급포기요청, 03:선금신청, 04:결재중, 10:선금완료, 11:포기완료, 00:신청대상, 91:반려)
			//if(params.get("statusCd").equals("91")){
			//statusCd = "91";
			//}
			
			String userId					= user.get("ACCT_ID");		//로그인사용자
			
			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 subContModId				= params.get("subContModId");
			
			String rateAmt					= params.get("rateAmt");				
			String rateVamt					= params.get("rateVamt");	
			
			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 PUR1030T      														\n"
					+ " SET    TAX_METHOD_CD    			= ?       , 					        \n"
					+ "        TAX_DY 						= ?       ,					            \n"
					+ "        PREPAY_DATE 					= TO_DATE(?,'YYYY-MM-DD'),	            \n"
					+ "        TAX_CHARG_NM					= ?       ,					            \n"
					+ "        TAX_CHARG_HP					= ?       ,					            \n"
					+ "        TAX_CHARG_EMAIL				= ?       ,					            \n"
					+ "        PREPAY_SUPPLY_AMT_KRW		= ?       ,					            \n"
					+ "        PREPAY_SURTAX_AMT_KRW		= ?       ,					            \n"
					+ "        REQ_DIV_CD					= ?       , 					        \n"
					+ "        REQ_PREPAY_AMT				= ?       , 		--신청선급금합계       \n"
					+ "        REQ_PREPAY_SUPPLY_AMT		= ?       , 		--신청선급금공급가     \n"
					+ "        REQ_PREPAY_SURTAX_AMT		= ?       , 		--신청선급금부가세     \n"
					+ "        PREPAY_PROC_GB				= '11'		  , 					        \n"
					//+ "        PREPAY_DATE					= ?		  , 					        \n"
					+ "        CRE_BY      					= ?       , 				            \n"
					+ "        CRE_DATE    					= sysdate ,								\n"
					+ "        UPD_BY      					= ?       , 				            \n"
					+ "        UPD_DATE    					= sysdate ,								\n"
					+ "        SUPP_DIRECT_YN    		    = ? ,								    \n"  //2016.12.23 하도급직불 
					+ "        RTN_REASON    				= ''									\n"
					+ " WHERE  PREPAY_ID      				= ?        								\n"
					;			  
			
			persistent_u.setStatement(sqlstr_u);
			persistent_u.addParameter(taxMethodCd);
			persistent_u.addParameter(taxDy.replaceAll("-", ""));
			persistent_u.addParameter(taxDy.replaceAll("-", ""));
			persistent_u.addParameter(taxChargNm);
			persistent_u.addParameter(taxChargHp);
			persistent_u.addParameter(taxChargEmail);
			
			persistent_u.addParameter(rateAmt.replaceAll(",", ""));
			persistent_u.addParameter(rateVamt.replaceAll(",", ""));
			
			persistent_u.addParameter(perpayReqDiv);
			persistent_u.addParameter(reqPrepayAmt.replaceAll(",", ""));
			persistent_u.addParameter(reqPrepaySupplyAmt.replaceAll(",", ""));
			persistent_u.addParameter(reqPrepaySurtaxAmt.replaceAll(",", ""));
			
			//persistent_u.addParameter(taxDy);
			persistent_u.addParameter(by);
			persistent_u.addParameter(userId);
			persistent_u.addParameter(params.get("SUPP_DIRECT_YN"));  //2016.12.23 하도급직불 
			
			persistent_u.addParameter(prepayId);
			
			WAFLogger.debug(sqlstr_u);
			WAFLogger.debug(persistent_u.getParameters());
			
			persistent_u.execute();
			persistent_u.clearParameters();
			
			persistent_u1 = new PersistentImpl(super.conn.getConnection());
			sqlstr_u1 = " UPDATE PUR2000T      												\n"
					+ " SET    ACCT_GB    			= ?       , 					        \n"
					+ "        GUA_GB 				= ?       ,					            \n"
					+ "        PAY_GROUP			= ?       ,					            \n"
					+ "        PAYMENT_TERMS		= ?       					            \n"
					+ " WHERE  PUR_CONT_MOD_ID      = ?        								\n"
					;			  
			persistent_u1.setStatement(sqlstr_u1);
			persistent_u1.addParameter(acctGb);
			persistent_u1.addParameter(guaGb);
			persistent_u1.addParameter(payGroup);
			persistent_u1.addParameter(paymentTerms);
			persistent_u1.addParameter(subContModId);
			
			WAFLogger.debug(sqlstr_u1);
			WAFLogger.debug(persistent_u1.getParameters());
			
			persistent_u1.execute();
			persistent_u1.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(prepayId);
			
			WAFLogger.debug(sqlstr_u2);
			WAFLogger.debug(persistent_u2.getParameters());
			
			persistent_u2.execute();
			persistent_u2.clearParameters();
			
			persistent_u3 = new PersistentImpl(super.conn.getConnection());
			sqlstr_u3.append(" UPDATE  CONT_PUR_PAYPLAN SET   				\n");
			sqlstr_u3.append("         PAY_YN  = 'N', 					\n");
			sqlstr_u3.append("         PAY_SUPPLY_AMT  = '0' 					\n");
			sqlstr_u3.append(" WHERE PUR_CONT_ID =   ?  AND PAY_DIV_CD = '01'   					\n");
			
			persistent_u3.setStatement(sqlstr_u3.toString());
			persistent_u3.addParameter(subContId);
			persistent_u3.execute();
			
			
			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
	 * * 입력받은 선급금 상세 정보를 삭제합니다.
     * @param connection
     * @param pgmName
     * @param screenName
     * @param systemCode
     * @throws Exception
	 */
	private boolean deleteData(ValueObject params) {
		
		
		Persistent persistent_d = null;
		String sqlstr_d = "";

		try {
		
			//선급금 신청 상제 정보
			String prepayId					= params.get("prepayId");				//선급ID
	
			conn.begin();
			
			persistent_d = new PersistentImpl(super.conn.getConnection());
			
			sqlstr_d = 	  " DELETE                   		\n"
		                  + " FROM PUR1030T 	  		    \n"
		                  + "WHERE PREPAY_ID = ?        	\n";
		        
			persistent_d.setStatement(sqlstr_d);
			persistent_d.addParameter(prepayId);
			
			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 를 이용한 Save 처리
	 * @param params
	 * @return
	 * * 기성신청을 처리한다.
     * @param connection
     * @param pgmName
     * @param screenName
     * @param systemCode
     * @throws Exception
	 */
	private boolean reqData(ValueObject params) {
		
		Persistent persistent_u = null;
		String sqlstr_u = "";

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

			sqlstr_u = " UPDATE PUR1030T      														\n"
			          + " SET    PREPAY_PROC_GB				= ?       ,								\n"
			          + "        UPD_BY      				= ?       , 					        \n"
			          + "        UPD_DATE    				= sysdate ,								\n"
			          + "        RTN_REASON    				= ''									\n"
			          + " WHERE  PREPAY_ID      			= ?        								\n"
			          ;
			  
			persistent_u.setStatement(sqlstr_u);
			
			persistent_u.addParameter(statusCd);
			persistent_u.addParameter(userId);
			persistent_u.addParameter(prepayId);
			
			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.PREPAY_PROC_GB,'00') AS STATUS_CD											\n"
        			+ " FROM PUR1030T B																	\n"
        			+ " WHERE 1=1																		\n"
        			;

        	sqlstr += " AND B.PREPAY_ID = '"+ params.get("prepayId") +"'								\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;
        }
    }	

	private boolean segumData(ValueObject params) {
		
		Persistent persistent_u = null;
		String sqlstr_u = "";

		try {
		
			
			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 acctGb					= params.get("acct_gb");				//분계유형
			//String guaGb					= params.get("proof_cd");				//
			//String payGroup					= params.get("pay_group");				//
			//String paymentTerms				= params.get("payment_terms");			//
			String userId					= storage.getUser().get("ACCT_ID");		//로그인사용자
			
			String pKey				= params.get("pKey");			//
			
			conn.begin();
			
			persistent_u = new PersistentImpl(super.conn.getConnection());
			sqlstr_u = " UPDATE PUR1030T	      														\n"
		              + " SET    TAX_METHOD_CD    			= ?       , 					            \n"
		              + "        TAX_DY 					= ?       , 					            \n"
		              + "        TAX_CHARG_NM				= ?       , 					            \n"
		              + "        TAX_CHARG_HP				= ?       , 					            \n"
		              + "        TAX_CHARG_EMAIL			= ?       , 					            \n"
					  + "	     --ACCT_GB      			= ?       , 					            \n"
					  + "        --PAY_GROUP      			= ?       , 					            \n"
					  + "        --GUA_GB      				= ?       , 					            \n"
					  + "        --PAYMENT_TERMS      		= ?       , 					            \n"
					  + "        PREPAY_DATE	      		= ?       , 					            \n"
					  + "        UPD_BY      				= ?       , 					            \n"
		              + "        UPD_DATE    				= sysdate									\n"
		              + " WHERE  PREPAY_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(acctGb);
			//persistent_u.addParameter(payGroup);
			//persistent_u.addParameter(guaGb);
			//persistent_u.addParameter(paymentTerms);
			persistent_u.addParameter(taxDy);
			persistent_u.addParameter(userId);
			
			persistent_u.addParameter(pKey);
				
			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();
        }	
	} 
	
	private boolean guaData(ValueObject params) {
		
		Persistent persistent_u = null;
		String sqlstr_u = "";
		
		try {
			
			//분계유형
			String acctGb					= params.get("acct_gb");				//분계유형
			String guaGb					= params.get("proof_cd");				//
			String payGroup					= params.get("pay_group");				//
			String paymentTerms				= params.get("payment_terms");			//
			String userId					= storage.getUser().get("ACCT_ID");		//로그인사용자			
			String pKey				= params.get("pKey");			//
			
			conn.begin();
			
			persistent_u = new PersistentImpl(super.conn.getConnection());
			sqlstr_u = " UPDATE PUR2000T	      													\n"
					+ " SET    ACCT_GB      			= ?       , 					            \n"
					+ "        PAY_GROUP      			= ?       , 					            \n"
					+ "        GUA_GB      				= ?       , 					            \n"
					+ "        PAYMENT_TERMS      		= ?       	 					            \n"
					+ " WHERE  PUR_CONT_MOD_ID 			= ?        									\n"
					;
			
			persistent_u.setStatement(sqlstr_u);
			
			persistent_u.addParameter(acctGb);
			persistent_u.addParameter(payGroup);
			persistent_u.addParameter(guaGb);
			persistent_u.addParameter(paymentTerms);
			persistent_u.addParameter(pKey);
			
			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 ValueObject getContInfo(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        ValueObject rowObject = null;
        
        try {

        	sqlstr += " SELECT 			  								  																						\n"
        			+ "     A1.PUR_CONT_ID	                			  																						\n"
					+ "     ,A1.ACCT_GB       																													\n"
					+ "     ,A1.GUA_GB      																													\n"
					+ "     ,A1.PAY_GROUP       																													\n"
					+ "     ,A1.PAYMENT_TERMS       																												\n"
					+ "     ,A1.PAY_ITEM ||'-'|| A1.COST_CODE ||'-'|| A1.COST_TYPE AS PAY_ITEM      																	\n"
					+ "     ,A1.PAY_ITEM ||'-'|| A1.COST_CODE ||'-'|| A1.COST_TYPE ||'(' || F_GET_PAY_ITEM_EST_DESC(A1.PAY_ITEM,A1.COST_CODE,A1.COST_TYPE,A1.DEPT_CD)||')' AS PAY_ITEM_NM	      										\n"
					+ "     ,(SELECT AC_UNIT_CD FROM ACZ10100V Z WHERE  Z.DEPT_CD = A1.DEPT_CD) AS  AC_UNIT_CD		      										\n"
        			+ " FROM 																														\n"
        			+ "      PUR2000T A1,																													\n"
        			+ "      PUR1030T B1																													\n"
        			+ " WHERE A1.PUR_CONT_MOD_ID = B1.PUR_CONT_MOD_ID																						\n"
        			+ " AND A1.CON_CLS_YN = '9'																														\n"
        			;
        	
        	sqlstr += " AND A1.PUR_CONT_MOD_ID = '"+ params.get("selectSubContModId") +"'																						\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;
        }
    }
    
    public ValueObject getRageEx(String rateDy, String curGb, String reqPrepaySupplyAmt, String inputVat) {
        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 {
            //2017.1.31 환율연산 소수점 제외 
        	sqlstr += " SELECT A.RATE																									\n"
        		    + " 	   , ROUND(A.RATE * TO_NUMBER("+reqPrepaySupplyAmt+"),0) AS RATE_AMT													\n" 
        		    + " 	   , DECODE('"+segumRate+"',0,0,ROUND(A.RATE * TO_NUMBER("+reqPrepaySupplyAmt+") / "+segumRate+",0)) AS RATE_VAMT								\n" 
					+ " FROM (SELECT F_GET_EXCHANGE_RATE(TO_DATE("+rateDy+"), '"+curGb+"', 'KRW') AS RATE FROM DUAL) 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;
        }
    }
    
}
