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


import oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.base.WAFMessages;
import kr.co.hsnc.common.sql.Row;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.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 com.udapsoft.waf.common.util.ClobUtils;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

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

	public void doBiz() throws Exception {

		
		if(storage.compareEvent("")){
			
			//신청구분ComboList
			RowSet rowSet = getPerpayReqDiv();
			storage.addRowSet("perpayReqDivComboList", rowSet);
			
			//기본 정보, 세금계산서 정보,선급금  
			ValueObject prePayment = getPrePayment(params);

			//첨부파일
	        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"));
	        prePayment.set("digit", digit);
	        
			storage.addValueObject("prePayment", prePayment);
			
			//보증완료된 정보
			RowSet warrCount = getWarrCount(params);
			storage.addRowSet("warrCount", warrCount); 
		
		}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("DELETE_DATA")){	//데이터 삭제
			boolean success = false;
			success = deleteData(params);
			storage.setSuccess(success);
		}else if(storage.compareEvent("REQ_DATA")){		//기성신청 요청
			boolean success = false;
			success = reqData(params);

			if(success){	//대금지급 처리 start
				ValueObject conditionReslut = getPayDiv(params);
 				String payDiv = conditionReslut.get("PAY_DIV");
 				String chgDivCd = conditionReslut.get("CHG_DIV_CD"); //2019.12.18 추가(추가 선급금인 경우 아래 로직 적용안되도록)
				if("2".equals(payDiv) && "".equals(chgDivCd)){	//설치 && 신규 선급금
					ValueObject contPurPnalData01 = getContPurPayplan01(params.get("subContModId"));
					ValueObject contPurPnalDataMax = getContPurPayplanMax(params.get("subContModId"));
					
					if(contPurPnalData01.size() > 0){
						double paySuppluAmt01 = contPurPnalData01.getDouble("PAY_SUPPLY_AMT");
	                    double curReqAmt = conditionReslut.getDouble("R_P_S_A");
	                    double paySuppluAmtMax = contPurPnalDataMax.getDouble("PAY_SUPPLY_AMT");
	                    double culSuppluAmt = paySuppluAmt01 - curReqAmt;
	                    if(culSuppluAmt != 0){
	                    	
	                    	try{
	                    		
		                    	Persistent persistent_u1 = new PersistentImpl(super.conn.getConnection());
		                    	Persistent persistent_u2 = new PersistentImpl(super.conn.getConnection());
		                    	
		                		String sqlstr_u1 = "";
		                		String sqlstr_u2 = "";
		                		
		                		conn.begin(); 
		                		
		                		sqlstr_u1 = 	" 	UPDATE CONT_PUR_PAYPLAN SET PAY_SUPPLY_AMT = ? WHERE PUR_CONT_MOD_ID = ?	AND PAY_SEQ = ?	";
		                		sqlstr_u2 = 	" 	UPDATE CONT_PUR_PAYPLAN SET PAY_SUPPLY_AMT = ? WHERE PUR_CONT_MOD_ID = ?	AND PAY_SEQ = ?	AND PAY_DIV_CD = '01'";

		                		persistent_u1.setStatement(sqlstr_u1);
		                		persistent_u2.setStatement(sqlstr_u2);
		                		
		                		paySuppluAmtMax = paySuppluAmtMax + culSuppluAmt;
		                		persistent_u1.addParameter(paySuppluAmtMax);
		                		persistent_u1.addParameter(params.get("subContModId"));
		                		persistent_u1.addParameter(contPurPnalDataMax.get("PAY_SEQ"));
		                		persistent_u1.execute();
		                		persistent_u1.clearParameters();
								
		                		persistent_u2.addParameter(curReqAmt);
		                		persistent_u2.addParameter(params.get("subContModId"));
		                		persistent_u2.addParameter(contPurPnalDataMax.get("MINPAY_SEQ"));
		                		persistent_u2.execute();
		                		persistent_u2.clearParameters();
		                		
	                    	}catch(Exception e) {
	                            super.doException(e);
	                            super.storage.setMessage(e.getMessage());
	                            super.storage.setDetailMessage(e);
	                            super.conn.setRollback();
	                        }finally {
	                        	super.conn.end();
	                        }
	                    }
					}
				}
			} //대금지급 처리 end
			
			String resHp = params.get("resHp").replaceAll("-", "");	//계약 담당자 핸드폰번호
			String resEmail = params.get("resEmail");				//계약 담당자 이메일

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

			//SMS
	      	MessageSend messageSend = new MessageSend(storage);
	      	
	      	if(params.get("perpayReqDiv").equals("1")){ // 선급금 신청
	        	String smsContent = WAFMessages.getMessage("Prepayments.Approved.Request");
	        	messageSend.sendSMS(resHp, "024899000", "", smsContent);  //2015.9.21  당사담당자 수신SMS 대표번호로 변경. 
	        	
				
	        	//E-Mail
	        	MessageSend mailSend = new MessageSend(storage);
	        	StringBuffer mailContent = new StringBuffer();
	        	mailContent.append(memberName+ "님이 선급금을 신청하였습니다. </BR>");
	        	mailContent.append("신청한 선급금정보 확인후 그룹웨어 결재 하시기 바랍니다.</BR>");
	        	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	        	mailContent.append("</BR>");
	        	mailContent.append("1.프로젝트: "+deptName+"</BR>");
	        	mailContent.append("2.계약번호: "+conDocNo+"</BR>");
	        	mailContent.append("3.계약명: " +buildName+"</BR>");
	        	mailContent.append("4.협력사 담당자:"+userName+"(연락처 :"+hp+")</BR>");
	        	mailContent.append("</font>");
	        	messageSend.sendEmail(resEmail, eMail, "선급금 결재요청 메일입니다.", "선급금 결재요청 메일입니다.", mailContent, "Y");
	      	}else if(params.get("perpayReqDiv").equals("2")){
	      		String smsContent = "선급금(일부) 결재요청이 있습니다.'||CHR(13)||CHR(10)||'전자조달시스템에 접속/확인바랍니다.'||CHR(13)||CHR(10)||'SGC이앤씨(주)";
	      		//System.out.println(smsContent);
	      		messageSend.sendSMS(resHp, "024899000", "", smsContent);  //2015.9.21  당사담당자 수신SMS 대표번호로 변경. 
	      		
	      		//E-Mail
	      		MessageSend mailSend = new MessageSend(storage);
	      		StringBuffer mailContent = new StringBuffer();
	      		mailContent.append(memberName+ "에서 선급금을 일부 신청하였습니다. </BR>");
	      		mailContent.append("전자조달시스템에서 신청한 선급금액 및 선급금 일부포기각서 확인 후 그룹웨어 결재 하시기 바랍니다.</BR>");
	      		mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	      		mailContent.append("</BR>");
	      		mailContent.append("1.프로젝트: "+deptName+"</BR>");
	      		mailContent.append("2.계약번호: "+conDocNo+"</BR>");
	      		mailContent.append("3.계약명: " +buildName+"</BR>");
	      		mailContent.append("4.협력사 담당자:"+userName+"(연락처 :"+hp+")</BR>");
	      		mailContent.append("</font>");
	      		messageSend.sendEmail(resEmail, eMail, "선급금(일부) 결재요청 메일입니다.", "선급금(일부) 결재요청 메일입니다.", mailContent, "Y");
	      		
	      	}else if(params.get("perpayReqDiv").equals("3")){ // 선급금 포기
	      		String smsContent = "선급금 포기 신청이 있습니다.'||CHR(13)||CHR(10)||'전자조달시스템에 접속/확인바랍니다.'||CHR(13)||CHR(10)||'SGC이앤씨(주)";
	      		//System.out.println(smsContent);
	      		messageSend.sendSMS(resHp, "024899000", "", smsContent);  //2015.9.21  당사담당자 수신SMS 대표번호로 변경. 
	      		
	      		//E-Mail
	      		MessageSend mailSend = new MessageSend(storage);
	      		StringBuffer mailContent = new StringBuffer();
	      		mailContent.append(memberName+ "에서 선급금을 포기하였습니다. </BR>");
	      		mailContent.append("전자조달 시스템에서 선급금 포기각서 확인 후 처리바랍니다.</BR>");
	      		mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	      		mailContent.append("</BR>");
	      		mailContent.append("1.프로젝트: "+deptName+"</BR>");
	      		mailContent.append("2.계약번호: "+conDocNo+"</BR>");
	      		mailContent.append("3.계약명: " +buildName+"</BR>");
	      		mailContent.append("4.협력사 담당자:"+userName+"(연락처 :"+hp+")</BR>");
	      		mailContent.append("</font>");
	      		messageSend.sendEmail(resEmail, eMail, "선급금 포기 신청 메일입니다.", "선급금 포기 신청 메일입니다.", mailContent, "Y");
	      		
	      	}
	      	
			storage.setSuccess(success);
		}
		
	}

    /**
     * 첨부파일 조회.
	 * @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"
        			+ "     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"
        			+ " FROM PUR2000T A1,																																\n"
        			+ "       PUR1030T B1,																														\n"
        			+ "       CONT_PUR_SUPP C1																														\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";

        	sqlstr += " AND A1.PUR_CONT_MOD_ID = '"+ params.get("selectSubContModId") +"'																		\n";
        	
            search.setStatement(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");
       	
            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");
    		
            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) {
		
		Persistent persistent_u = null;
		String sqlstr_u = "";
		ValueObject user = storage.getUser();
		
		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					= "02";									//상태(01:작성중, 02:선급신청요청, 21:선급포기요청, 03:선금신청, 04:결재중, 10:선금완료, 11:포기완료, 00:신청대상, 91:반려)
			
			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"
					+ "        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"
					+ " 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(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(prepayId);
			
			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();
        }
	}
	
	/**
	 * 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
			String subContModId				= params.get("subContModId");			//외주계약변경ID

			conn.begin();
			
			persistent_d = new PersistentImpl(super.conn.getConnection());
			
			  sqlstr_d = 	" DELETE                   		\n"
		                  + " FROM   PREPAY_OUT 			\n"
		                  + " WHERE  PREPAY_ID = ?        	\n";
		        
			persistent_d.setStatement(sqlstr_d);
			persistent_d.addParameter(prepayId);
			
			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 signeddata				= params.get("signeddata");			//서명데이터
			int userId						= params.getInt("USER_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"
			          + "        SUPP_SIGN_DT  				= 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);
			
			persistent_u.execute();
			persistent_u.clearParameters();

			ClobUtils.setClob( conn.getConnection(), "PUR1030T", "SIGN_DATA", " WHERE PREPAY_ID = '"+prepayId+"'", signeddata);
			
			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 getPayDiv(ValueObject Params) {
        
    	Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        ValueObject rowObject = null;
        
        try {

        	sqlstr += " SELECT A.REQ_DIV_CD AS REQ_DIV_CD,  B.PAY_DIV AS PAY_DIV  ,A.REQ_PREPAY_SUPPLY_AMT AS R_P_S_A, A.CHG_DIV_CD	\n"
        			+ " FROM PUR1030T A, pur2000t B							\n"
        			+ " WHERE a.pur_cont_mod_id = b.pur_cont_mod_id			\n"
        			+ " AND PREPAY_ID    = '"+ params.get("prepayId") +"'	\n";
            
        	search.setStatement(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 getContPurPayplan01(String purContModId) throws Exception {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		String sqlstr = "";
		ValueObject row = null;
		
		try{
			sqlstr ="	SELECT PUR_CONT_MOD_ID, PAY_SEQ, PUR_CONT_ID, PAY_SUPPLY_AMT, GUAR_YN, WARR_GUBUN, VP_YN, PAY_DIV_CD, PAYREQ_ID, PAY_YN							\n" +
					"	FROM CONT_PUR_PAYPLAN																													\n" +
					"	WHERE PAY_DIV_CD = '01'																															\n" +
					"	AND PUR_CONT_MOD_ID = '"+purContModId+"'																										\n"
					;
			
			System.out.println(sqlstr);
			
			search.setStatement(sqlstr);
			rowSet = search.execute();
			if(rowSet.size() > 0){
				row = RowSetUtility.getValueObject(rowSet.getRow(0));
			}
			
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getContPurPayplan() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			throw e;
			
		}finally{
			if(row == null) row = new ValueObject();
		}
		
		return row;
	}
	
	public ValueObject getContPurPayplanMax(String purContModId) throws Exception {
		
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        String sqlstr = "";
        ValueObject row = null;
        
        try{
	        sqlstr =	"	SELECT B.PAY_SUPPLY_AMT    , A.PAY_SEQ,  A.MINPAY_SEQ																					\n" +
						"	FROM (																																	\n" +
						"			SELECT PUR_CONT_MOD_ID, MIN(PAY_SEQ) AS MINPAY_SEQ, MAX(PAY_SEQ) AS PAY_SEQ, MAX(PAY_SUPPLY_AMT) AS  PAY_SUPPLY_AMT				\n" +
						"			FROM CONT_PUR_PAYPLAN																									\n" +
						"			WHERE 1=1																														\n" +
						"			AND PUR_CONT_MOD_ID = '"+purContModId+"'																						\n" +
						"			GROUP BY  PUR_CONT_MOD_ID																										\n" +
						"		) A,																																\n" +
	        			"		CONT_PUR_PAYPLAN B   																										\n" +
	        			"	WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID																								\n" +
	        			"	AND A.PAY_SEQ = B.PAY_SEQ																												\n"
	        			;
	        
			System.out.println(sqlstr);
			
			search.setStatement(sqlstr);
            rowSet = search.execute();
			
            if(rowSet.size() > 0){
            	row = RowSetUtility.getValueObject(rowSet.getRow(0));
            }

        }catch(Exception e){
            WAFLogger.error(this.getClass().getName() + " --> getContPurPayplanMax() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
            
		}finally{
			if(row == null) row = new ValueObject();
		}
		
		return row;
	}
	
	  /**
		 * 보증완료 목록 조회
		 * @param Params
		 * @return
		 */
		private RowSet getWarrCount(ValueObject param) {

			Search search = new SearchImpl();
	        RowSet rowSet = new RowSetImpl();
	        
	     //   String sqlstr = "";
	        /*
			보증서 종류
			  002 : 계약이행
			  003 : 하자
			  004 : 선금
			  005 : 근로자재해
			  39 : 하도급대금지급         * 
	         * */
	        StrBuffer sqlstr = new StrBuffer();
	        try{
	        	sqlstr.append("	              SELECT WARR_GUBUN	\n");
	        	sqlstr.append("	                   , F_GET_CODE_NAME('GAUR_KIND',WARR_GUBUN) AS WARR_GUBUN_NM	\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('MOD_GB',CAUSECODE) AS CAUSECODE_NM	\n");
	        	sqlstr.append("	                   , 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("	                FROM CONT_PUR_WARR 	\n");
	        	sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("selectSubContModId") +"' 	\n");
	        	sqlstr.append("	                     AND WARR_GUBUN IN ('3')	\n");
	        	sqlstr.append("	                     AND GUAR_STATUS IN ('09')	\n");

				search.setStatement(sqlstr.toString());
	            rowSet = search.execute();
	        }
	        catch (WAFSQLException se) {
	            super.doException(se);
	            this.storage.setSuccess(false);  
	            this.storage.setMessage(se.getMessage());
	            storage.setDetailMessage(se);
	            //System.out.println("### WAFSQLException : " + se.getMessage());
	        }
	        finally {    
	            if(rowSet == null){
	            	rowSet = new RowSetImpl();
	            }
	        }
	        //System.out.println("### rowSet size : " + rowSet.size());
	        return rowSet;
		}
}
