/**
 * @(#)file    GuarPurSubconPayBiz.java
 * @(#)author  Kim Myung Ho
 * @(#)version 1.0
 * @(#)date    2014-09-18
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.supplier.portal.common.guar.biz;

import java.io.*;
//전문건설 전송시 필요
import java.net.*;
import java.sql.CallableStatement;
import java.sql.Connection;

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

import com.udapsoft.waf.common.util.StringUtils;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

import kr.co.hsnc.common.config.WAFConfig;

public class GuarPurSubconPayBiz extends Biz {

	public GuarPurSubconPayBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
        //System.out.println("BidIngListBiz :  ~" + storage.getUser());
		//System.out.println("Outside event : #" + storage.getEvent());
		//System.out.println("Outside params : " + params);
		boolean success = false;
		
		if(storage.compareEvent("WARR_OFF")){
			
            user = getWarrContValueObject(params);
            storage.addValueObject("WarrContInfo", user);

    		//보증기관명 ComboList
    		RowSet rowSet = getWarrGofficeList();
    		storage.addRowSet("WarrGofficeComboList", rowSet);

            //첨부된 저장 파일
    		storage.addValueObject("attFileList", getfilelist("GUARCONT_PUR_"+params.get("warrGubun")+"_"+params.get("warrSeq"), params.get("purContModId"),"fileRow"));
    		storage.addValueObject("attFileList1", getfilelist("GUARCONT_PUR_"+params.get("warrGubun")+"_"+(Integer.parseInt(params.get("warrSeq"))+1), params.get("purContModId"),"fileRow"));
  
		}else if(storage.compareEvent("WARR_ON")){
			
            user = getWarrContOnValueObject(params);
            storage.addValueObject("WarrContInfo", user);
            
    		//기관분류 ComboList
    		RowSet rowSet = getCodeList("CONTORGBNCODE");
    		storage.addRowSet("contorgbncodeComboList", rowSet);
    		//장기계속구분 ComboList
    		RowSet rowSetlonggbn = getCodeList("LONGGBN");
    		storage.addRowSet("longgbnComboList", rowSetlonggbn);

    		//첨부된 저장 파일
    		storage.addValueObject("attFileList", getfilelist("GUARCONT_PUR_"+params.get("warrGubun")+"_"+params.get("warrSeq"), params.get("purContModId"),"fileRow"));
  
		}else if(storage.compareEvent("FORM_SAVE")){			//오프라인 저장
			
			success = offGuarReqProc(super.conn.getConnection(), params);	//보증정보 저장
			super.storage.setSuccess(success);
			
		}else if(storage.compareEvent("FORM_SAVE_ON")){			//온라인 저장
			
			success = onGuarReqProc(super.conn.getConnection(), params);
			if(success){
	        	for(int k=1; k<= Integer.parseInt(params.get("CAUSECOUNT")); k++){
	        		success = makeGuarData(super.conn.getConnection(), params, ""+ k);	//보증 데이터 생성	
	    			if(success){	//전송 실패일 경우 상태값을 신청 대기 상태로 놓는다.
	    				params.set("gStatus", "05");
	    				WarrStatusUp(super.conn.getConnection(), params, ""+ k);
	    			}
	    		}
			}

			super.storage.setSuccess(success);
			
		}else if(storage.compareEvent("FORM_DEL")){
			String sContChasu		= params.get("MOD_NO");
			String sWarrChasu		= params.get("warrSeq1");
			if(sContChasu.length() == 1)	sContChasu	=	"0"	+	sContChasu;
			if(sWarrChasu.length() == 1)	sWarrChasu	=	"0"	+	sWarrChasu;
			
			String repGrtReqNo = params.get("GRTREQNO") +"--"+ sContChasu +"--006--"+ sWarrChasu;
			
			//TRANUSRE쪽 업데이트 필요
			success = guarStatusUp(super.conn.getConnection(), repGrtReqNo);
			
			if(success){	//전송 성공일 경우 삭제처리
				success = sendGuarData(super.conn.getConnection(), repGrtReqNo);	//온라인 보증 취소
				if(success){	//전송 성공일 경우 삭제처리
					params.set("gStatus", "02");
					WarrStatusUp(super.conn.getConnection(), params, "1");
				}	
			}
			super.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 getWarrGofficeList(){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT '4' AS DETAILCD, '엔지니어링공제조합' AS DETAILNM FROM DUAL			\n"  //2023.3.27추가 
        	    + " UNION ALL										      					    \n"
                + " SELECT '3' AS DETAILCD, '건설공제조합' AS DETAILNM FROM DUAL   				\n"
                + " UNION ALL										      					    \n"
                + " SELECT '99' AS DETAILCD, '기타' AS DETAILNM FROM DUAL   					    \n";
            
            search.setStatement(sqlstr);
            listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}    
 
    /**
     * 각 콤보에 사용될 코드 목록을 가져온다
	 * @param 
	 * @param 
	 * @return ValueObject
	 */
	public RowSet getCodeList(String codeValue){

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

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}    
 
    /**
     * 발행구분을 조회한다.
	 * @param 
	 * @param 
	 * @return ValueObject
	 */
	public ValueObject getContInfo(ValueObject param){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StrBuffer sqlstr = new StrBuffer();
        ValueObject rowObject = null;
        
        try {
        	sqlstr.append(" 	SELECT A.PUR_CONT_MOD_ID			\n");
        	sqlstr.append(" 	     ,(A.ORD_DOC_NO || '-' || A.MOD_NO) AS ORD_DOC_NO	\n");
        	sqlstr.append(" 	     ,A.MOD_NO						\n");
        	sqlstr.append(" 	     ,A.DEPT_NAME					\n");
        	sqlstr.append(" 	     ,A.CST_DOC_NAME					\n");
        	sqlstr.append(" 	     ,TO_CHAR(A.ORD_CON_DATE, 'YYYY-MM-DD') AS ORD_CON_DATE	\n");
        	sqlstr.append(" 	     ,F.MEMBER_NAME					\n");
        	sqlstr.append(" 	     ,TO_CHAR(A.ORD_AMT, '999,999,999,999,999') AS ORD_AMT	\n");
        	sqlstr.append(" 	     ,TO_CHAR(A.ORD_START_DATE, 'YYYY-MM-DD') || '~' || TO_CHAR(A.ORD_END_DATE, 'YYYY-MM-DD') AS WORK_START_END_DATE	\n");
        	sqlstr.append(" 	     ,A.ORD_START_DATE				\n");
        	sqlstr.append(" 	     ,A.ORD_END_DATE				\n");
        	sqlstr.append(" 	     ,A.CON_GB 						\n");
        	sqlstr.append(" 	     ,A.STATUS_CD 					\n");
        	sqlstr.append(" 	     ,F_GET_CODE_NAME('CAUSECODE',NVL(DECODE(MOD_GB,'1','03','2','02','3','04'),'01')) AS CAUSECODE_NM 				\n");
        	sqlstr.append(" 	  FROM PUR2000T A, CONT_PUR_SUPP F	\n");
        	sqlstr.append("   WHERE 1=1								\n");
    		sqlstr.append("   	AND A.PUR_CONT_MOD_ID = F.PUR_CONT_MOD_ID			\n");
    		sqlstr.append("   	AND A.CON_CLS_YN = 9				\n");
    		sqlstr.append("   	AND F.SIGN_SEQ = 2					\n");
    		sqlstr.append("  	AND A.PUR_CONT_MOD_ID = '"+param.get("purContModId")+"'	\n");
             
    		search.setStatement(sqlstr.toString());
            listRowSet = search.execute();
            if(listRowSet.size() != 0){
            	rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
            }
            
		}catch(Exception e){
			e.printStackTrace();
		}finally{

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

	/**
	 * 계약 상세 정보
	 * @param Params
	 * @return
	 */
	private ValueObject getWarrContValueObject(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        //System.out.println("=======================" + param.get("warrGubun"));
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("  	SELECT 	\n");
        	sqlstr.append("  	  A.PUR_CONT_MOD_ID	\n");
        	sqlstr.append("  	  , NVL(B.GRTREQNO, 'M_' || A.PUR_CONT_MOD_ID ||'_'|| A.PUR_CONT_ID) AS GRTREQNO	\n");
        	sqlstr.append("  	  , A.PUR_CONT_ID	\n");
        	sqlstr.append("  	  , A.MOD_NO	\n");
        	sqlstr.append("  	  , NVL(B.CAUSECODE, '"+param.get("causeCode")+"') AS CAUSECODE	\n");
           	sqlstr.append("	      , F_GET_CODE_NAME('CAUSECODE',NVL(B.CAUSECODE, '"+param.get("causeCode")+"')) AS CAUSECODE_NM	\n");        	
        	sqlstr.append("  	  , NVL(B.GUAR_STATUS, '02') AS GUAR_STATUS	\n");
        	sqlstr.append("  	  , TO_CHAR(A.ORD_AMT, '999,999,999,999,999')	\n");
        	sqlstr.append("  	  , NVL(B.WARR_WAMT, 0) AS WARR_WAMT	\n");
        	sqlstr.append("  	  , NVL(B.WARR_COMMSN, 0) AS WARR_COMMSN	\n");
        	sqlstr.append("  	  , NVL(B.GUAR_ONOFF_YN, 'N') AS GUAR_ONOFF_YN	\n");
        	sqlstr.append("  	  , B.GUAR_ORGAN_CODE	\n");
        	sqlstr.append("  	  , B.WARR_GOFFICE	\n");
        	sqlstr.append("  	  , B.WARR_WGNO	\n");
        	sqlstr.append("  	  , NVL(B.WARR_SDATE, TO_CHAR(A.ORD_START_DATE, 'YYYY-MM-DD')) AS WARR_SDATE	\n");
        	sqlstr.append("  	  , NVL(B.WARR_EDATE, TO_CHAR(A.ORD_END_DATE, 'YYYY-MM-DD')) AS WARR_EDATE	\n");
        	sqlstr.append("  	  , B.PAPERDATE	\n");
           	sqlstr.append("	      , F_GET_CODE_NAME('GAUR_KIND','"+param.get("warrGubun")+"') AS WARR_GUBUN_NM	\n");
        	sqlstr.append("  	  , A.DEPT_NAME	\n");
        	sqlstr.append("  	  , A.CST_DOC_NAME	\n");
        	sqlstr.append("  	  , F.MEMBER_NAME	\n");
        	sqlstr.append("  	  , A.ORD_DOC_NO	\n");
        	sqlstr.append("  	FROM PUR2000T A, CONT_PUR_WARR B, CONT_PUR_SUPP F	\n");
        	sqlstr.append("  	WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID(+)	\n");
        	sqlstr.append("  		AND A.PUR_CONT_MOD_ID = F.PUR_CONT_MOD_ID	\n");
        	sqlstr.append("   		AND F.SIGN_SEQ = 2					\n");
        	sqlstr.append("  	    AND A.PUR_CONT_MOD_ID = '"+param.get("purContModId")+"'	\n");
        	sqlstr.append("  	    AND B.WARR_GUBUN(+) = '"+param.get("warrGubun")+"'	\n");
        	sqlstr.append("  	    AND B.WARR_SEQ(+) = '"+param.get("warrSeq")+"'	\n");

			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            	
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //System.out.println(sqlstr.toString());
            //System.out.println("WAFSQLException : " + se.getMessage());
        }
        finally {

        	if( valueobject == null ) valueobject = new ValueObject();
        	//System.out.println("### valueobject size : " + valueobject.size());
            return valueobject;
        }
	}

	/**
	 * 계약 상세 정보
	 * @param Params
	 * @return
	 */
	private ValueObject getWarrContOnValueObject(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        //System.out.println("=======================" + param.get("warrGubun"));
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("   	  SELECT S.CORP_ID AS CONT_OFFICE	\n");
        	sqlstr.append("   	       , C.DEPT_CD AS CONT_SITE	\n");
        	sqlstr.append("   	       , C.ORD_DOC_NO AS CONT_GNO	\n");
        	sqlstr.append("   	       , C.MOD_NO AS CONT_CHASU	\n");
        	sqlstr.append("   	  --     , C.CHG_CONT_FORM	\n");
        	sqlstr.append("   	       , C.DEPT_NAME AS SITE_NAME	\n");
        	sqlstr.append("   	       , C.PUR_CONT_ID||'-'||C.MOD_NO SITE_GNO	\n");
        	sqlstr.append("   	       , C.ORD_CON_DATE AS CONT_CDATE	\n");
        	sqlstr.append("   	       , C.ORD_AMT AS CONT_CAMT	\n");
        	sqlstr.append("   	       , TO_CHAR(C.ORD_START_DATE,'YYYY-MM-DD') AS CONT_SDATE	\n");
        	sqlstr.append("   	       , TO_CHAR(C.ORD_END_DATE,'YYYY-MM-DD') AS CONT_EDATE	\n");
        	sqlstr.append("   	       , NVL(C.DEALER_NM, (SELECT REPLACE(ORDER_COMP_NM,'T_','') FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = C.DEPT_CD)) AS DEALER_NM	\n");
        	sqlstr.append("   	       , NVL(W.DEALER_BSNO, (SELECT ORDER_COMP_NO FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = C.DEPT_CD)) AS DEALER_BSNO	\n");
        	sqlstr.append("   	       , C.CST_DOC_NAME AS CONT_NAME	\n");
        	sqlstr.append("   	       , S.MEMBER_NAME AS CUST_NAME	\n");
        	sqlstr.append("   	       , S.VENDCD AS CONT_BSNO	\n");
        	sqlstr.append("   	  --     , C.CONT_PCNT	\n");
        	sqlstr.append("   	       , '2' WARR_GUBUN	\n");
        	sqlstr.append("   	       , W.WARR_SEQ	\n");
        	sqlstr.append("   	       , DECODE(W.GRTREQNO,NULL,'I','U') AS EXIST_FLAG	\n");
        	sqlstr.append("   	       , W.GUAR_ONOFF_YN	\n");
        	sqlstr.append("   	       , W.WARR_GREM	\n");
        	sqlstr.append("   	       , W.CONTSEQ	\n");
        	sqlstr.append("   	  --     , W.CONTTYPE	\n");
        	sqlstr.append("   	       , W.CONTAMT	\n");
        	sqlstr.append("   	       , W.CONTDATE 	\n");
        	sqlstr.append("   	       , W.CONTSTARTDATE	\n");
        	sqlstr.append("   	       , W.CONTCLOSEDATE	\n");
        	sqlstr.append("   	       , W.GUAR_CONTAMT	\n");
        	sqlstr.append("	           , W.ADDCONTAMT	 --계약금 분담금액   		\n");
        	sqlstr.append("	           , W.ADDPREPAYAMT	 --기지급 선급금   		\n");
        	sqlstr.append("	           , W.ADDEXISAMT	 --기지급 기성금  		\n");        	
        	sqlstr.append("   	       , W.CONTORDERNO	\n");
        	sqlstr.append("   	       , W.ORDEROWNER	\n");
        	sqlstr.append("   	       , NVL(W.WARR_GOFFICE,'건설공제조합') AS WARR_GOFFICE	\n");
        	sqlstr.append("   	       , W.GUAR_ORGAN_CODE	\n");
        	sqlstr.append("   	       , W.WARR_WGNO	\n");
        	sqlstr.append("   	       , W.WARR_WAMT	\n");
        	sqlstr.append("   	       , W.WARR_SDATE	\n");
        	sqlstr.append("   	       , W.WARR_EDATE	\n");
        	sqlstr.append("   	       , W.WARR_WRATE	\n");
        	sqlstr.append("   	       , W.WARR_KAMT2	\n");
        	sqlstr.append("   	       , W.WARR_KAMT1	\n");
        	sqlstr.append("   	       , NVL(W.CAUSECODE,'"+param.get("causeCode")+"') AS CAUSECODE	\n");
        	sqlstr.append("   	       , W.PAPERDATE	\n");
        	sqlstr.append("   	       , W.REJECTMENT	\n");
        	sqlstr.append("   	       , W.EXEMPTMENT	\n");
        	sqlstr.append("   	       , W.WARR_COMMSN	\n");
        	sqlstr.append("   	       , W.ACCDOCSTATUS	\n");
        	sqlstr.append("   	       , NVL(W.GRTREQNO, 'M_' || C.PUR_CONT_MOD_ID ||'_'|| C.PUR_CONT_ID) AS GRTREQNO	\n");
        	sqlstr.append("   	       , W.YEARINSUREYN	\n");
        	sqlstr.append("   	       , W.REQDATE	\n");
        	sqlstr.append("   	       , W.GUAR_STATUS	\n");
        	sqlstr.append("   	       , W.GUARRATEGBNCODE	\n");
        	sqlstr.append("   	       , 100 AS CONTRATE	\n");				//계약지분율
        	sqlstr.append("   	       , 100 AS GUARRATE	\n");				//보증지분율
        	sqlstr.append("   	       , W.LONGGBN	\n");
        	sqlstr.append("   	       , W.CONTORGBNCODE	\n");
        	sqlstr.append("   	       , W.DEPNM	\n");
        	sqlstr.append("   	       , W.EMPNM	\n");
        	sqlstr.append("   	       , W.TELNO	\n");
        	sqlstr.append("   	       , W.HPNO	\n");
        	sqlstr.append("   	       , W.EMAIL	\n");
        	sqlstr.append("   	       , DECODE(W.PREPAYREFYN, NULL, DECODE(PY.SUM_SUN, 0, 'N', 'Y'), W.PREPAYREFYN) PREPAYREFYN	\n");
        	sqlstr.append("   	       , DECODE(PY.SUM_SUN, 0, 'N', 'Y') PREPAYREFYN	\n");
        	sqlstr.append("   	--       , DECODE(W.EXIISTREFYN, NULL, DECODE(FU.FUND_TAMT, 0, 'N', 'Y'), W.EXIISTREFYN) EXIISTREFYN	\n");
        	sqlstr.append("   	       , CASE 	\n");
        	sqlstr.append("   	             WHEN W.EXIISTREFYN IS NOT NULL 	\n");
        	sqlstr.append("   	             THEN W.EXIISTREFYN 	\n");
        	sqlstr.append("   	             WHEN W.EXIISTREFYN IS NULL 	\n");
        	sqlstr.append("   	                 AND EWA.WARR_CNT>0 	\n");
        	sqlstr.append("   	             THEN 'N' --WHEN W.EXIISTREFYN IS NULL AND CGS.CNT_GS > 0 THEN 'N' 	\n");
        	sqlstr.append("   	             WHEN W.EXIISTREFYN IS NULL 	\n");
        	sqlstr.append("   	                 AND FU.FUND_TAMT=0 	\n");
        	sqlstr.append("   	             THEN 'N' 	\n");
        	sqlstr.append("   	             ELSE 'N' 	\n");
        	sqlstr.append("   	         END EXIISTREFYN	\n");
        	sqlstr.append("   	       , F_GET_CODE_NAME('CAUSECODE',NVL(W.CAUSECODE,'"+param.get("causeCode")+"')) AS CAUSENAME	\n");
        	sqlstr.append("   	       , F_GET_CODE_NAME('GUAR_STATUS',W.GUAR_STATUS) AS GUAR_STATUS_NM	\n");
        	sqlstr.append("   	       , F_GET_CODE_NAME('GUARRATEGBNCODE',W.GUARRATEGBNCODE) AS GUARRATEGBNNAME	\n");
        	sqlstr.append("   	       , F_GET_CODE_NAME('LONGGBN',W.LONGGBN) AS LONGGBN_NM	\n");
        	sqlstr.append("   	       , F_GET_CODE_NAME('CONTORGBNCODE',W.CONTORGBNCODE) AS CONTORGBNCODE_NM	\n");
        	sqlstr.append("   	       , W.BOND_SEQ	\n");
        	sqlstr.append("   	       , NVL(PY.SUM_SUN,0) AS SUM_SUN	\n");
        	sqlstr.append("   	       , W.GUAR_ORGAN_CODE AS OFFID	\n");
        	sqlstr.append("   	       , 	\n");
        	sqlstr.append("   	         (SELECT X.WARR_WGNO 	\n");
        	sqlstr.append("   	           FROM 	\n");
        	sqlstr.append("   	                (SELECT ROWNUM ROW_NUMBER	\n");
        	sqlstr.append("   	                     , WARR_WGNO 	\n");
        	sqlstr.append("   	                  FROM CONT_PUR_WARR 	\n");
        	sqlstr.append("   	                 WHERE PUR_CONT_ID = '"+param.get("PUR_CONT_ID")+"'	\n");
        	sqlstr.append("   	                       AND WARR_GUBUN='2' 	\n");
        	sqlstr.append("   	              ORDER BY WARR_SEQ	\n");
        	sqlstr.append("   	                     , PAPERDATE	\n");
        	sqlstr.append("   	                ) X 	\n");
        	sqlstr.append("   	          WHERE ROW_NUMBER=1 	\n");
        	sqlstr.append("   	         ) LAST_WGNO	\n");
        	sqlstr.append("   	       , 	\n");
        	sqlstr.append("   	         (SELECT X.WARR_WGNO 	\n");
        	sqlstr.append("   	           FROM 	\n");
        	sqlstr.append("   	                (SELECT ROWNUM ROW_NUMBER	\n");
        	sqlstr.append("   	                     , WARR_WGNO 	\n");
        	sqlstr.append("   	                  FROM CONT_PUR_WARR 	\n");
        	sqlstr.append("   	                 WHERE PUR_CONT_MOD_ID = '"+param.get("purContModId")+"'	\n");
        	sqlstr.append("   	                       AND WARR_GUBUN='2' 	\n");
        	sqlstr.append("   	              ORDER BY WARR_SEQ	\n");
        	sqlstr.append("   	                     , PAPERDATE	\n");
        	sqlstr.append("   	                ) X 	\n");
        	sqlstr.append("   	          WHERE ROW_NUMBER=1 	\n");
        	sqlstr.append("   	         ) ORI_WGNO	\n");
        	sqlstr.append("   	       , 	\n");
        	sqlstr.append("   	         NVL((SELECT ORD_AMT 	\n");
        	sqlstr.append("   	           FROM PUR2000T 	\n");
        	sqlstr.append("   	          WHERE PUR_CONT_ID = '"+param.get("PUR_CONT_ID")+"'	\n");
        	sqlstr.append("   	                AND MOD_NO=TO_CHAR(TO_NUMBER('"+param.get("MOD_NO")+"')-1) 	\n");
        	sqlstr.append("   	         ),0) LAST_CAMT	\n");
        	
//        	sqlstr.append("   	       , 0 AS CONTEXISTENCEAMT 	\n");
        	sqlstr.append("   	       , NVL(DECODE(W.WARR_SEQ, NULL, FU.FUND_TAMT, W.CONTEXISTENCEAMT),0) CONTEXISTENCEAMT	\n");						
        	sqlstr.append("   	       , DECODE(W.WARR_SEQ, NULL, FU.FUND_DATE, W.CONTCHECKDATE) CONTCHECKDATE	\n");							
        	sqlstr.append("   	       , DECODE(EWA.WARR_CNT, 0, 'N', 'Y') GS_REF_FLAG	\n");
        	sqlstr.append("   	       , '' CREATE_FLAG 	\n");
        	sqlstr.append("   	       , TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS TODAY 	\n");
        	sqlstr.append("   	    FROM PUR2000T C 	\n");
        	sqlstr.append("   	       ,CONT_PUR_WARR W 	\n");
        	sqlstr.append("   	       ,CONT_PUR_SUPP S 	\n");
        	sqlstr.append("   	       ,	\n");
        	sqlstr.append("   	         (SELECT PUR_CONT_MOD_ID, NVL(F.CUR_PAYED_AMT,0) FUND_TAMT 	\n");
        	sqlstr.append("   	              ,TO_CHAR(NVL(F.SUPP_SIGN_DT, NVL(F.TAX_DY,F.TAX_DY)), 'YYYYMMDD') FUND_DATE 	\n");
        	sqlstr.append("   	           FROM PUR1100T F,(SELECT MAX(PAYREQ_ID) PAYREQ_ID FROM PUR1100T WHERE REQ_STATUS IN ('3','10') AND DEPOSIT_CD != 1 GROUP BY PUR_CONT_ID) FM 	\n");
        	sqlstr.append("   	          WHERE F.PAYREQ_ID = FM.PAYREQ_ID AND PUR_CONT_ID = '"+param.get("PUR_CONT_ID")+"'	\n");
        	sqlstr.append("   	         ) FU 	\n");
        	sqlstr.append("   	       ,	\n");
        	sqlstr.append("   	         (SELECT NVL(SUM(REQ_PREPAY_AMT),0) SUM_SUN 	\n");
        	sqlstr.append("   	           FROM PUR1030T 	\n");
        	sqlstr.append("   	          WHERE PUR_CONT_ID = '"+param.get("PUR_CONT_ID")+"'	\n");
        	sqlstr.append("   	                AND PREPAY_PROC_GB IN('3','10') 	\n");
        	sqlstr.append("   	         ) PY 	\n");
        	sqlstr.append("   	  --     ,	\n");
        	sqlstr.append("   	  --       (SELECT COUNT(XX.WARR_CHASU) CNT_GS 	\n");
        	sqlstr.append("   	  --         FROM CONT_PUR_WARR XX 	\n");
        	sqlstr.append("   	  --            ,ISCON.TOR_CONT_GUARJV_ATTFILE GA 	\n");
        	sqlstr.append("   	  --        WHERE XX.WARR_OFFICE=GA.CONT_OFFICE 	\n");
        	sqlstr.append("   	  --              AND XX.WARR_SITE=GA.CONT_SITE 	\n");
        	sqlstr.append("   	  --              AND XX.WARR_GNO=GA.CONT_GNO 	\n");
        	sqlstr.append("   	  --              AND XX.WARR_CHASU=GA.CONT_CHASU 	\n");
        	sqlstr.append("   	  --              AND XX.GUAR_STATUS='09' 	\n");
        	sqlstr.append("   	  --              AND XX.WARR_GUBUN='2' 	\n");
        	sqlstr.append("   	  --              AND GA.SEQ=3 	\n");
        	sqlstr.append("   	  --              AND XX.PUR_CONT_MOD_ID = '"+param.get("purContModId")+"'	\n");
        	sqlstr.append("   	  --       ) CGS 	\n");
        	sqlstr.append("   	       ,	\n");
        	sqlstr.append("   	         (SELECT COUNT(EX.WARR_SEQ) WARR_CNT 	\n");
        	sqlstr.append("   	           FROM CONT_PUR_WARR EX 	\n");
        	sqlstr.append("   	          WHERE EX.GUAR_STATUS='09' 	\n");
        	sqlstr.append("   	                AND EX.WARR_GUBUN='2' 	\n");
        	sqlstr.append("   	                AND EX.PUR_CONT_MOD_ID = '"+param.get("purContModId")+"'	\n");
        	sqlstr.append("   	         ) EWA 	\n");
        	sqlstr.append("   	   WHERE C.PUR_CONT_MOD_ID = '"+param.get("purContModId")+"'	\n");
        	sqlstr.append("   	         AND C.MOD_NO='"+param.get("MOD_NO")+"'	\n");
        	sqlstr.append("   	         AND C.PUR_CONT_MOD_ID=S.PUR_CONT_MOD_ID	\n");
        	sqlstr.append("   	         AND S.SIGN_SEQ = 1	\n");
        	sqlstr.append("   	  --       AND C.CONT_OFFICE=S.CONT_OFFICE 	\n");
        	sqlstr.append("   	  --       AND C.CONT_SITE=S.CONT_SITE 	\n");
        	sqlstr.append("   	  --       AND C.CONT_GNO=S.CONT_GNO 	\n");
        	sqlstr.append("   	  --       AND C.CONT_CHASU=S.CONT_CHASU 	\n");
        	sqlstr.append("   	  --       AND C.CONT_BSNO=S.CUST_BSNO 	\n");
        	sqlstr.append("   	         AND C.PUR_CONT_MOD_ID=W.PUR_CONT_MOD_ID(+) 	\n");
        	sqlstr.append("   	         AND W.WARR_GUBUN(+)='2' 	\n");
        	sqlstr.append("   	         AND C.PUR_CONT_MOD_ID = FU.PUR_CONT_MOD_ID(+)	\n");
        	sqlstr.append("   	  --       AND W.BOND_SEQ(+)=#I_BOND_SEQ#  	\n");

//        	sqlstr.append("  	    AND W.WARR_GUBUN(+) = '"+param.get("warrGubun")+"'	\n");
        	sqlstr.append("  	    AND W.WARR_SEQ(+) = '"+param.get("warrSeq")+"'	\n");

			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            	
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //System.out.println(sqlstr.toString());
            //System.out.println("WAFSQLException : " + se.getMessage());
        }
        finally {

        	if( valueobject == null ) valueobject = new ValueObject();
        	//System.out.println("### valueobject size : " + valueobject.size());
            return valueobject;
        }
	}
	/**
	 * 계약 상세 정보
	 * @param Params
	 * @return
	 */
	private Integer getWarrContSaveCheck(ValueObject param, String rowIndex) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        StrBuffer sqlstr = new StrBuffer();
        Integer wCount = 0;
        try{
        	sqlstr.append(" SELECT COUNT(*) FROM CONT_PUR_WARR WHERE PUR_CONT_MOD_ID = "+ param.get("purContModId") +" AND WARR_GUBUN = "+ param.get("warrGubun") +" AND WARR_SEQ = "+ param.get("warrSeq" +rowIndex) +"	\n");
        	
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
                wCount = valueobject.getInt(0);
            }
            //System.out.println("wCount : " + wCount);
            	
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //System.out.println(sqlstr.toString());
            //System.out.println("WAFSQLException : " + se.getMessage());
        }
        finally {

        	if( valueobject == null ) valueobject = new ValueObject();
        	//System.out.println("### valueobject size : " + valueobject.size());
        }
        return wCount;
	}

	/**
     * 온라인 보증 정보 등록 프로세스
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean offGuarReqProc(Connection connection, ValueObject valueObject) throws Exception {
		//System.out.println("valueObject : " + valueObject);
		Integer chkCount=0;
		String smsContent = "";
        try {
        	conn.begin();
        	for(int k=1; k<= Integer.parseInt(params.get("CAUSECOUNT")); k++){
        		margeGuarInfoData(super.conn.getConnection(), valueObject, ""+ k);	//보증정보 저장(super.conn.getConnection(), params, ""+ k);	//보증정보 저장
        		//2019.10.17 외주와 동일하게 적용 - 보증정보 저장 후 SMS발송 (향후 OFF만 등록)
        		MessageSend messageSend = new MessageSend(storage);
        		ValueObject vo = cont_supp_info(params.get("purContModId"));
        		smsContent = "하도급대금지급보증완료'||CHR(13)||CHR(10)||'협력업체포탈시스템에 접속/확인바랍니다.'||CHR(13)||CHR(10)||'SGC이앤씨(주)";
        		messageSend.sendSMS(vo.get("HP").replace("-", ""),vo.get("S_HP").replace("-", ""), storage.getUser().get("ACCT_ID"), smsContent); //2015.9.21 SMS tel_num으로 전송         		
        	}


			return true;
        }
        catch(Exception e) {
        	//System.out.println("Exception : " + e.getMessage());
            WAFLogger.error(this.getClass().getName() + " --> addChangeContProc() : " + e.getMessage());
            WAFLogger.error(e);

            conn.setRollback();
            this.storage.setSuccess(false);  
            storage.setDetailMessage(e);
            this.storage.setMessage(e.getMessage());            
            return false;
        }
        finally {
        	super.conn.end();
        }        
    }	
	/**
     * 오프라인 보증 정보 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean margeGuarInfoData(Connection connection, ValueObject valueObject, String rowIndex) throws Exception {
		//System.out.println("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("MERGE INTO CONT_PUR_WARR T1 USING (									\n");
        	sqlstr.append("          SELECT ? AS	PUR_CONT_MOD_ID	\n");
        	sqlstr.append("                ,? AS	WARR_GUBUN	\n");
        	sqlstr.append("                ,? AS	WARR_SEQ	\n");
        	sqlstr.append("                ,? AS	PUR_CONT_ID	\n");
        	sqlstr.append("                ,? AS	MOD_NO	\n");
        	sqlstr.append("                ,? AS	VIEW_DIV	\n");
        	sqlstr.append("                ,? AS	WARR_WAMT	\n");
        	sqlstr.append("                ,? AS	WARR_COMMSN	\n");
        	sqlstr.append("                ,? AS	WARR_SDATE	\n");
        	sqlstr.append("                ,? AS	WARR_EDATE	\n");
        	sqlstr.append("                ,? AS	WARR_WGNO	\n");
        	sqlstr.append("                ,? AS	WARR_GOFFICE	\n");
        	sqlstr.append("                ,? AS	GUAR_ONOFF_YN	\n");
        	sqlstr.append("                ,? AS	GUAR_ORGAN_CODE	\n");
        	sqlstr.append("                ,? AS	CAUSECODE	\n");
        	sqlstr.append("                ,? AS	PAPERDATE	\n");
        	sqlstr.append("                ,? AS	GRTREQNO	\n");
        	sqlstr.append("                ,? AS	GUAR_STATUS	\n");
        	sqlstr.append("		        FROM DUAL													\n");
        	sqlstr.append("		    ) T ON (T.PUR_CONT_MOD_ID = T1.PUR_CONT_MOD_ID AND T.WARR_GUBUN = T1.WARR_GUBUN AND T.WARR_SEQ = T1.WARR_SEQ)	\n");
        	sqlstr.append("		WHEN MATCHED THEN													\n");
        	sqlstr.append("		    UPDATE SET 				\n");
//        	sqlstr.append("                  T1.WARR_GUBUN	 =	T.WARR_GUBUN	\n");
//        	sqlstr.append("                , T1.WARR_SEQ	 =	T.WARR_SEQ	\n");
        	sqlstr.append("                  T1.VIEW_DIV	 =	T.VIEW_DIV	\n");
        	sqlstr.append("                , T1.WARR_WAMT	 =	T.WARR_WAMT	\n");
        	sqlstr.append("                , T1.WARR_COMMSN	 =	T.WARR_COMMSN	\n");
        	sqlstr.append("                , T1.WARR_SDATE	 =	T.WARR_SDATE	\n");
        	sqlstr.append("                , T1.WARR_EDATE	 =	T.WARR_EDATE	\n");
        	sqlstr.append("                , T1.WARR_WGNO	 =	T.WARR_WGNO	\n");
        	sqlstr.append("                , T1.WARR_GOFFICE	 =	T.WARR_GOFFICE	\n");
        	sqlstr.append("                , T1.GUAR_ONOFF_YN	 =	T.GUAR_ONOFF_YN	\n");
        	sqlstr.append("                , T1.GUAR_ORGAN_CODE	 =	T.GUAR_ORGAN_CODE	\n");
        	sqlstr.append("                , T1.CAUSECODE	 =	T.CAUSECODE	\n");
        	sqlstr.append("                , T1.PAPERDATE	 =	T.PAPERDATE	\n");
        	sqlstr.append("                , T1.GRTREQNO	 =	T.GRTREQNO	\n");
        	sqlstr.append("                , T1.GUAR_STATUS	 =	T.GUAR_STATUS	\n");
        	sqlstr.append("		WHEN NOT MATCHED THEN											\n");
        	sqlstr.append("			INSERT (T1.PUR_CONT_MOD_ID, T1.WARR_GUBUN, T1.WARR_SEQ, T1.PUR_CONT_ID, T1.MOD_NO, T1.VIEW_DIV, T1.WARR_WAMT, T1.WARR_COMMSN, T1.WARR_SDATE, T1.WARR_EDATE, T1.WARR_WGNO, T1.WARR_GOFFICE, T1.GUAR_ONOFF_YN, T1.GUAR_ORGAN_CODE, T1.CAUSECODE, T1.PAPERDATE, T1.GRTREQNO, T1.GUAR_STATUS, T1.WARR_INDAY, T1.WARR_UPDAY)	\n");
        	sqlstr.append("			VALUES (T.PUR_CONT_MOD_ID, T.WARR_GUBUN, T.WARR_SEQ, T.PUR_CONT_ID, T.MOD_NO, T.VIEW_DIV, T.WARR_WAMT, T.WARR_COMMSN, T.WARR_SDATE, T.WARR_EDATE, T.WARR_WGNO, T.WARR_GOFFICE, T.GUAR_ONOFF_YN, T.GUAR_ORGAN_CODE, T.CAUSECODE, T.PAPERDATE, T.GRTREQNO, T.GUAR_STATUS, SYSDATE, SYSDATE)	\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("PURCONTMODID"));
            persistent.addParameter(valueObject.get("WARRGUBUN"));
            persistent.addParameter(valueObject.get("WARRSEQ" + rowIndex));
            persistent.addParameter(valueObject.get("PUR_CONT_ID"));
            persistent.addParameter(valueObject.get("MOD_NO"));
            persistent.addParameter(valueObject.get("VIEWDIV"));
            persistent.addParameter(StringUtils.replace(valueObject.get("WARRWAMT" + rowIndex),",",""));
            persistent.addParameter(StringUtils.replace(valueObject.get("WARRCOMMSN" + rowIndex),",",""));
            persistent.addParameter(StringUtils.replace(valueObject.get("DATESTART" + rowIndex),"-",""));
            persistent.addParameter(StringUtils.replace(valueObject.get("DATEEND" + rowIndex),"-",""));
            persistent.addParameter(valueObject.get("WARRWGNO" + rowIndex));
            persistent.addParameter(valueObject.get("WARRGOFFICEETC"));
            persistent.addParameter(valueObject.get("guarOnOffYn"));
            persistent.addParameter(valueObject.get("guarOrganCode"));
            persistent.addParameter(valueObject.get("CAUSECODE" + rowIndex));
            persistent.addParameter(StringUtils.replace(valueObject.get("PAPERDATE" + rowIndex),"-",""));
            persistent.addParameter("");				//valueObject.get("GRTREQNO"));
            persistent.addParameter(valueObject.get("GUARSTATUS"));

//            persistent.addParameter(StringUtils.replace(valueObject.get("O_HP"),"-",""));
            
            persistent.execute();


            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //System.out.println("Exception : " + e.getMessage());
            throw e;
        }
    }	
	
	/**
     * 온라인 보증 정보 등록 프로세스
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean onGuarReqProc(Connection connection, ValueObject valueObject) throws Exception {
		//System.out.println("valueObject : " + valueObject);
		Integer chkCount=0;
        try {
        	conn.begin();
        	for(int k=1; k<= Integer.parseInt(params.get("CAUSECOUNT")); k++){
    			chkCount = getWarrContSaveCheck(params, ""+ k);
    			//System.out.println("chkCount : " + chkCount);
    			if (chkCount>0){
    				updateOnGuarInfoData(super.conn.getConnection(), params, ""+ k);	//보증정보 저장	
    			}else{
    				insertOnGuarInfoData(super.conn.getConnection(), params, ""+ k);	//보증정보 저장
    			}        		
        	}


			return true;
        }
        catch(Exception e) {
        	//System.out.println("Exception : " + e.getMessage());
            WAFLogger.error(this.getClass().getName() + " --> addChangeContProc() : " + e.getMessage());
            WAFLogger.error(e);

            conn.setRollback();
            this.storage.setSuccess(false);  
            storage.setDetailMessage(e);
            this.storage.setMessage(e.getMessage());            
            return false;
        }
        finally {
        	super.conn.end();
        }        
    }	
	
	/**
     * 온라인 보증 정보 보증 정보 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean insertOnGuarInfoData(Connection connection, ValueObject valueObject, String rowIndex) throws Exception {
		//System.out.println("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("	    INSERT INTO CONT_PUR_WARR( 			\n");
        	sqlstr.append("	            PUR_CONT_MOD_ID	 --외주계약변경ID                           		\n");
        	sqlstr.append("	            ,WARR_GUBUN	 --구분                               		\n");
        	sqlstr.append("	            ,WARR_SEQ	 --일련번호		\n");
        	sqlstr.append("	            ,PUR_CONT_ID	 --외주계약서ID                           		\n");
        	sqlstr.append("	            ,MOD_NO	 --변경차수                           		\n");
        	sqlstr.append("	            ,VIEW_DIV	 --계약번호                           		\n");

        	sqlstr.append("	            ,WARR_GOFFICE	 --보증서발급기관명                        		\n");
        	sqlstr.append("	            ,WARR_GREM	 --비고                            		\n");
        	sqlstr.append("	            ,WARR_INDAY	 --입력날짜                         		\n");
        	sqlstr.append("	            ,WARR_UPDAY	 --수정날짜                         		\n");
//        	sqlstr.append("	            ,TRADESIGNDATE	 --입력날짜                         		\n");
        	sqlstr.append("	            ,WARR_INUSR	 --입력자                         		\n");
        	
        	sqlstr.append("	            ,GUAR_ONOFF_YN	 --온오프여부   		\n");
        	sqlstr.append("	            ,GUAR_ORGAN_CODE	 --보증서발급기관코드              		\n");
        	sqlstr.append("	            ,CAUSECODE	 --변경구분코드                       		\n");
        	sqlstr.append("	            ,PAPERDATE	 --발급일자                           		\n");
        	sqlstr.append("	            ,REJECTMENT	 --반려사유    		\n");
        	sqlstr.append("	                                			\n");
        	sqlstr.append("	            ,EXEMPTMENT	 --면제사유                        		\n");
        	sqlstr.append("	            ,GRTREQNO	 --보증서신청계약번호              		\n");
        	sqlstr.append("	            ,YEARINSUREYN	 --연간근재구분                       		\n");
        	sqlstr.append("	            ,REQDATE	 --발급희망일                         		\n");
        	sqlstr.append("	            ,GUAR_STATUS	 --보증발급상태		\n");
        	sqlstr.append("	            ,BOND_SEQ	 --보증순번   		\n");
        	sqlstr.append("	            ,CONTSEQ	 --도급변경차수_원도급계약         		\n");
        	sqlstr.append("	            ,GUARRATEGBNCODE	 --보증서발급지분코드                 		\n");
        	sqlstr.append("	            ,LONGGBN	 --장기계속구분_원도급계약            		\n");
        	sqlstr.append("	            ,CONTTYPE	 --공동도급형태_원도급계약  		\n");
        	sqlstr.append("	                      			\n");
        	sqlstr.append("	            ,CONTAMT	 --원도급계약금액                     		\n");
        	sqlstr.append("	            ,CONTDATE	 --원도급계약체결일                   		\n");
        	sqlstr.append("	            ,CONTSTARTDATE	 --원도급계약시작일                   		\n");
        	sqlstr.append("	            ,CONTCLOSEDATE	 --원도급계약종료일                   		\n");
        	sqlstr.append("	            ,PREPAYREFYN	 --선급금반영여부                     		\n");
        	sqlstr.append("	            ,EXIISTREFYN	 --기성금반영여부                     		\n");
        	sqlstr.append("	            ,CONTCHECKDATE	 --기성검사일_보증용                  		\n");
        	sqlstr.append("	            ,CONTEXISTENCEAMT	 --기성금액_보증용                    		\n");
        	sqlstr.append("	            ,CONTORGBNCODE	 --발주처기관분류코드                 		\n");
        	sqlstr.append("	            ,DEALER_BSNO	 --발주처사업자코드                 		\n");
        	
        	sqlstr.append("	            ,GUAR_CONTAMT	 --보증용계약금액   		\n");

        	sqlstr.append("	            ,ADDCONTAMT	 --계약금 분담금액   		\n");
        	sqlstr.append("	            ,ADDPREPAYAMT	 --기지급 선급금   		\n");
        	sqlstr.append("	            ,ADDEXISAMT	 --기지급 기성금  		\n");
         	
        	sqlstr.append("	                              			\n");
        	sqlstr.append("	            ,DEPNM	 --부서명                         		\n");
        	sqlstr.append("	            ,EMPNM	 --성명                            		\n");
        	sqlstr.append("	            ,HPNO	 --휴대전화                        		\n");
        	sqlstr.append("	            ,TELNO	 --전화번호                        		\n");
        	sqlstr.append("	            ,EMAIL	 --이메일                          		\n");
        	sqlstr.append("	            ,CONTORDERNO	 --원도급계약번호   		\n");
        	sqlstr.append("	            ,ORDEROWNER	 --발주처대표자명 		\n");
        	sqlstr.append("	)VALUES(			\n");
        	sqlstr.append("	             ?,?,?,?,?,?,?,?,SYSDATE,SYSDATE,?			\n");
        	sqlstr.append("	            ,?,?,?,?,?,?,?,?,?,?			\n");
        	sqlstr.append("	            ,?,?,?,?,?,?,?,?,?,?			\n");
        	sqlstr.append("	            ,?,?,?,?,?,?,?,?,?,?			\n");
        	sqlstr.append("	            ,?,?,?,?,?,?			\n");
        	sqlstr.append("	            			\n");
        	sqlstr.append("	    )   			\n");

            persistent.setStatement(sqlstr.toString());
            //System.out.println("Exception : " + valueObject.get("ADDCONTAMT" + rowIndex));         
            persistent.addParameter(valueObject.get("PURCONTMODID"));
            persistent.addParameter(valueObject.get("WARRGUBUN"));
            persistent.addParameter(valueObject.get("WARRSEQ" + rowIndex));				//연장증액이 같이 올수 있어서 두번 넘김 (1,2)
            persistent.addParameter(valueObject.get("PUR_CONT_ID"));
            persistent.addParameter(valueObject.get("MOD_NO"));
            persistent.addParameter(valueObject.get("VIEWDIV"));
            
            persistent.addParameter(valueObject.get("WARRGOFFICE"));
            persistent.addParameter(valueObject.get("WARR_GREM"));
            
            persistent.addParameter("");			//등록자

            persistent.addParameter(valueObject.get("guarOnOffYn"));
            persistent.addParameter(valueObject.get("guarOrganCode"));

            persistent.addParameter(valueObject.get("CAUSECODE" + rowIndex));						//연장증액이 같이 올수 있어서 두번 넘김 (1,2)
            persistent.addParameter(valueObject.get("PAPERDATE"));
            persistent.addParameter(valueObject.get("REJECTMENT"));

            persistent.addParameter(valueObject.get("EXEMPTMENT"));
            persistent.addParameter(valueObject.get("GRTREQNO"));
            persistent.addParameter(valueObject.get("YEARINSUREYN"));
            persistent.addParameter(StringUtils.replace(valueObject.get("REQDATE"),"-",""));
            persistent.addParameter(valueObject.get("guarStatus"));
            persistent.addParameter(valueObject.get("BOND_SEQ"));
            persistent.addParameter(valueObject.get("CONTSEQ"));
            persistent.addParameter(valueObject.get("GUARRATEGBNCODE"));
            persistent.addParameter(valueObject.get("LONGGBN"));
            persistent.addParameter("01");

            persistent.addParameter(StringUtils.replace(valueObject.get("CONTAMT"),",",""));
            persistent.addParameter(StringUtils.replace(valueObject.get("CONTDATE"),"-",""));
            persistent.addParameter(StringUtils.replace(valueObject.get("CONTSTARTDATE"),"-",""));
            persistent.addParameter(StringUtils.replace(valueObject.get("CONTCLOSEDATE"),"-",""));
            persistent.addParameter(valueObject.get("PREPAYREFYN" + rowIndex));						//연장증액이 같이 올수 있어서 두번 넘김 (1,2)
            persistent.addParameter(valueObject.get("EXIISTREFYN" + rowIndex));						//연장증액이 같이 올수 있어서 두번 넘김 (1,2)
            persistent.addParameter(StringUtils.replace(valueObject.get("CONTCHECKDATE"),"-",""));
            persistent.addParameter(StringUtils.replace(valueObject.get("CONTEXISTENCEAMT"),",",""));
            persistent.addParameter(valueObject.get("CONTORGBNCODE"));
            persistent.addParameter(valueObject.get("DEALER_BSNO"));
            persistent.addParameter(StringUtils.replace(valueObject.get("GUAR_CONTAMT"),",",""));
            
            persistent.addParameter(StringUtils.replace(valueObject.get("ADDCONTAMT" + rowIndex),",",""));							//연장증액이 같이 올수 있어서 두번 넘김 (1,2)	 	
            persistent.addParameter(StringUtils.replace(valueObject.get("ADDPREPAYAMT" + rowIndex),",",""));						//연장증액이 같이 올수 있어서 두번 넘김 (1,2)
            persistent.addParameter(StringUtils.replace(valueObject.get("ADDEXISAMT" + rowIndex),",",""));      					//연장증액이 같이 올수 있어서 두번 넘김 (1,2)      

            persistent.addParameter(valueObject.get("DEPNM"));
            persistent.addParameter(valueObject.get("EMPNM"));
            persistent.addParameter(valueObject.get("HPNO"));
            persistent.addParameter(valueObject.get("TELNO"));
            persistent.addParameter(valueObject.get("EMAIL"));
            persistent.addParameter(valueObject.get("CONTORDERNO"));
            persistent.addParameter(valueObject.get("ORDEROWNER"));
            
            //System.out.println(persistent.getParameters());
            persistent.execute();


            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //System.out.println("Exception : " + e.getMessage());
            throw e;
        }
    }	
	
	/**
     * 온라인 보증 정보 보증 정보 수정
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean updateOnGuarInfoData(Connection connection, ValueObject valueObject, String rowIndex) throws Exception {
		//System.out.println("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("	UPDATE CONT_PUR_WARR SET	\n");
//        	sqlstr.append("	   	 WARR_WAMT        = ?  --보증금액                           	\n");
//        	sqlstr.append("	      ,WARR_WRATE       = ?  --보증율                             	\n");
//        	sqlstr.append("	      ,WARR_COMMSN      = ?  --수수료                             	\n");
//        	sqlstr.append("	      ,WARR_SDATE       = ?  --보증기간(시작)	\n");
//        	sqlstr.append("	      ,WARR_EDATE       = ?  --보증기간(끝)                       	\n");
//        	sqlstr.append("	      ,WARR_WGNO        = ?  --증권번호       	\n");
        	sqlstr.append("	      WARR_GOFFICE     = ?  --보증서발급기관명                 	\n");
        	sqlstr.append("	      ,WARR_GREM        = ?  --비고                             	\n");
        	sqlstr.append("	      ,WARR_UPDAY       = SYSDATE  --수정날짜          	\n");
        	sqlstr.append("	      ,WARR_UPUSR       = ?  --수정자 	\n");
        	sqlstr.append("	      ,GUAR_ONOFF_YN    = ?  --온오프여부     	\n");
        	sqlstr.append("	      ,GUAR_ORGAN_CODE  = ?  --보증서발급기관코드              	\n");
        	sqlstr.append("	      ,CAUSECODE        = ?  --변경구분코드                       	\n");
        	sqlstr.append("	      ,PAPERDATE        = ?  --발급일자                           	\n");
        	sqlstr.append("	      ,REJECTMENT       = ?  --반려사유   	\n");
        	sqlstr.append("	      ,EXEMPTMENT       = ?  --면제사유                        	\n");
        	sqlstr.append("	      ,GRTREQNO         = ?  --보증서신청계약번호              	\n");
        	sqlstr.append("	      ,YEARINSUREYN     = ?  --연간근재구분                       	\n");
        	sqlstr.append("	      ,REQDATE          = ?  --발급희망일                         	\n");
        	sqlstr.append("	      ,GUAR_STATUS      = ?  --보증발급상태  	\n");
        	sqlstr.append("	      ,BOND_SEQ         = ?  --보증순번       	\n");
        	sqlstr.append("	      ,CONTSEQ          = ?  --도급변경차수_원도급계약         	\n");
        	sqlstr.append("	      ,GUARRATEGBNCODE  = ?  --보증서발급지분코드                 	\n");
        	sqlstr.append("	      ,LONGGBN          = ?  --장기계속구분_원도급계약            	\n");
        	sqlstr.append("	      ,CONTTYPE         = ?  --공동도급형태_원도급계약  	\n");
        	sqlstr.append("	      ,CONTAMT          = ?  --원도급계약금액                     	\n");
        	sqlstr.append("	      ,CONTDATE         = ?  --원도급계약체결일                   	\n");
        	sqlstr.append("	      ,CONTSTARTDATE    = ?  --원도급계약시작일                   	\n");
        	sqlstr.append("	      ,CONTCLOSEDATE    = ?  --원도급계약종료일                   	\n");
        	sqlstr.append("	      ,PREPAYREFYN      = ?  --선급금반영여부  	\n");
        	sqlstr.append("	      ,EXIISTREFYN      = ?  --기성금반영여부  	\n");
        	sqlstr.append("	      ,CONTCHECKDATE    = ?  --기성검사일_보증용                  	\n");
        	sqlstr.append("	      ,CONTEXISTENCEAMT = ?  --기성금액_보증용                    	\n");
        	sqlstr.append("	      ,CONTORGBNCODE    = ?  --발주처기관분류코드                 	\n");
        	sqlstr.append("	      ,DEALER_BSNO   	= ?  --발주처사업자코드                 		\n");
        	sqlstr.append("	      ,GUAR_CONTAMT     = ?  --보증용계약금액   	\n");
        	sqlstr.append("	      ,ADDCONTAMT     	= ?  --계약금 분담금액   		\n");
        	sqlstr.append("	      ,ADDPREPAYAMT     = ?  --기지급 선급금   		\n");
        	sqlstr.append("	      ,ADDEXISAMT    	= ?  --기지급 기성금  		\n");
        	
        	sqlstr.append("	      ,DEPNM            = ?  --부서명                         	\n");
        	sqlstr.append("	      ,EMPNM            = ?  --성명                            	\n");
        	sqlstr.append("	      ,HPNO             = ?  --휴대전화                        	\n");
        	sqlstr.append("	      ,TELNO            = ?  --전화번호	\n");
        	sqlstr.append("	      ,EMAIL            = ?  --이메일	\n");
        	sqlstr.append("	      ,CONTORDERNO      = ?   --원도급계약번호	\n");
        	sqlstr.append("	      ,ORDEROWNER      = ?  --발주처대표자명	\n");
        	sqlstr.append("	 WHERE PUR_CONT_MOD_ID      = ?  --외주계약변경ID                           	\n");
        	sqlstr.append("	   AND MOD_NO       = ?  --변경차수                           	\n");
        	sqlstr.append("	   AND PUR_CONT_ID       = ?  --외주계약서ID                           	\n");
        	sqlstr.append("	   AND WARR_GUBUN       = ?  --구분                               	\n");
        	sqlstr.append("	   AND WARR_SEQ         = ?  --일련번호 	\n");


            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("WARRGOFFICE"));
            persistent.addParameter(valueObject.get("WARR_GREM"));
            
            persistent.addParameter("");			//등록자

            persistent.addParameter(valueObject.get("guarOnOffYn"));
            persistent.addParameter(valueObject.get("guarOrganCode"));
            persistent.addParameter(valueObject.get("CAUSECODE" + rowIndex));						//연장증액이 같이 올수 있어서 뒤에 숫자1을 붙힘
            persistent.addParameter(valueObject.get("PAPERDATE"));
            persistent.addParameter(valueObject.get("REJECTMENT"));

            persistent.addParameter(valueObject.get("EXEMPTMENT"));
            persistent.addParameter(valueObject.get("GRTREQNO"));
            persistent.addParameter(valueObject.get("YEARINSUREYN"));
            persistent.addParameter(StringUtils.replace(valueObject.get("REQDATE"),"-",""));
            persistent.addParameter(valueObject.get("guarStatus"));
            persistent.addParameter(valueObject.get("BOND_SEQ"));
            persistent.addParameter(valueObject.get("CONTSEQ"));
            persistent.addParameter(valueObject.get("GUARRATEGBNCODE"));
            persistent.addParameter(valueObject.get("LONGGBN"));
            persistent.addParameter("01");

            persistent.addParameter(StringUtils.replace(valueObject.get("CONTAMT"),",",""));
            persistent.addParameter(StringUtils.replace(valueObject.get("CONTDATE"),"-",""));
            persistent.addParameter(StringUtils.replace(valueObject.get("CONTSTARTDATE"),"-",""));
            persistent.addParameter(StringUtils.replace(valueObject.get("CONTCLOSEDATE"),"-",""));
            persistent.addParameter(valueObject.get("PREPAYREFYN" + rowIndex));					//연장증액이 같이 올수 있어서 뒤에 숫자1을 붙힘	
            persistent.addParameter(valueObject.get("EXIISTREFYN" + rowIndex));					//연장증액이 같이 올수 있어서 뒤에 숫자1을 붙힘	
            persistent.addParameter(StringUtils.replace(valueObject.get("CONTCHECKDATE"),"-",""));
            persistent.addParameter(StringUtils.replace(valueObject.get("CONTEXISTENCEAMT"),",",""));
            persistent.addParameter(valueObject.get("CONTORGBNCODE"));
            persistent.addParameter(valueObject.get("DEALER_BSNO"));
            persistent.addParameter(StringUtils.replace(valueObject.get("GUAR_CONTAMT"),",",""));
            
            persistent.addParameter(StringUtils.replace(valueObject.get("ADDCONTAMT" + rowIndex),",",""));						//연장증액이 같이 올수 있어서 뒤에 숫자1을 붙힘		 	
            persistent.addParameter(StringUtils.replace(valueObject.get("ADDPREPAYAMT" + rowIndex),",",""));						//연장증액이 같이 올수 있어서 뒤에 숫자1을 붙힘
            persistent.addParameter(StringUtils.replace(valueObject.get("ADDEXISAMT" + rowIndex),",",""));     					//연장증액이 같이 올수 있어서 뒤에 숫자1을 붙힘       

            persistent.addParameter(valueObject.get("DEPNM"));
            persistent.addParameter(valueObject.get("EMPNM"));
            persistent.addParameter(valueObject.get("HPNO"));
            persistent.addParameter(valueObject.get("TELNO"));
            persistent.addParameter(valueObject.get("EMAIL"));
            persistent.addParameter(valueObject.get("CONTORDERNO"));
            persistent.addParameter(valueObject.get("ORDEROWNER"));
            //조건값
            persistent.addParameter(valueObject.get("PURCONTMODID"));
            persistent.addParameter(valueObject.get("MOD_NO"));
            persistent.addParameter(valueObject.get("PUR_CONT_ID"));
            persistent.addParameter(valueObject.get("WARRGUBUN"));
            persistent.addParameter(valueObject.get("WARRSEQ" + rowIndex));

            persistent.execute();


            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //System.out.println("Exception : " + e.getMessage());
            throw e;
        }
    }	
	
	
	/**
     * 보증 데이터 생성
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean makeGuarData(Connection connection, ValueObject valueObject, String rowIndex) throws Exception {

        CallableStatement cs = null;
        ValueObject rtnVo = new ValueObject();
        String sqlStr = "";
        try {
        	conn.begin();
        	sqlStr = "{call SP_GRT_DATA_CREATE(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
        	
        	//System.out.println("SQL : " + sqlStr); 
        	
            cs = super.conn.getConnection().prepareCall(sqlStr);
            cs.setString(1, valueObject.get("PURCONTMODID"));
            cs.setString(2, "M");
            cs.setString(3, valueObject.get("GRTREQNO"));
            cs.setString(4, valueObject.get("PUR_CONT_ID"));
            cs.setString(5, valueObject.get("MOD_NO"));
            cs.setString(6, valueObject.get("WARRSEQ" + rowIndex));			
            cs.setString(7, valueObject.get("WARRGUBUN"));
            cs.setString(8, "0"+valueObject.get("guarOrganCode"));
            cs.setString(9, valueObject.get("CAUSECODE" + rowIndex));
            cs.setString(10, valueObject.get("prepayId"));
            cs.setString(11, null);
        	//System.out.println("1 : " + valueObject.get("PURCONTMODID")); 
        	//System.out.println("2 : " + "M"); 
        	//System.out.println("3 : " + valueObject.get("GRTREQNO")); 
        	//System.out.println("4 : " + valueObject.get("PUR_CONT_ID")); 
        	//System.out.println("5 : " + valueObject.get("MOD_NO")); 
        	//System.out.println("6 : " + valueObject.get("WARRSEQ" + rowIndex));
        	//System.out.println("7 : " + valueObject.get("WARRGUBUN")); 
        	//System.out.println("8 : " + "0"+valueObject.get("guarOrganCode")); 
        	//System.out.println("9 : " + valueObject.get("CAUSECODE" + rowIndex)); 
        	//System.out.println("10 : " + valueObject.get("prepayId")); 

            cs.registerOutParameter(12, java.sql.Types.VARCHAR);
            cs.registerOutParameter(13, java.sql.Types.VARCHAR);		//성공일경우 V_REP_CONTRT_REF_ID 값을 전달 받는다.
            cs.execute();
            rtnVo.set("O_ERRCODE",cs.getString(12));
            rtnVo.set("O_ERRMSG",cs.getString(13));            
            
            //온라인 보증 정보 전송
            return this.sendGuarData(connection, cs.getString(13));
          //  return true;

        }
        catch(Exception e) {
            //System.out.println("Exception : " + e.getMessage());
            //System.out.println("OUT_SUCCESS_CD : " + cs.getString(12));
            //System.out.println("errMsg : " + cs.getString(13));
            super.doException(e);
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(12));
            rtnVo.set("errMsg",cs.getString(13));
            super.conn.setRollback();
            this.storage.setSuccess(false);
            this.storage.setMessage(e.getMessage());
            return false;
        }
        finally {
            super.conn.end();
        }
    }	
	
	/**
	 * 보증정보 전송
	 * @param gs				GauceService
	 * @param sGuarOrganCode	보증기관코드
	 * @param sContNo			계약번호
	 * @param sContSubNo		계약순번
	 * @param sContRound		계약차수
	 * @param sBondSeq			
	 * @param sGrtType			보증종류코드
	 * @param sProcessCode
	 * @return
	 * @throws Exception
	 */
	public boolean sendGuarData(Connection connection, String repContrtRefId) throws Exception
	{
		String guarUrl = WAFConfig.get("waf.guar.url");
		
		String SendURL = guarUrl + "/KCFC_Client/SendServlet?pSection=APPLICATION&repContrtRefId="+ repContrtRefId +"&repContrtYn=1";
		//System.out.println("SendURL : " + SendURL);		
		
		URL url = new URL(SendURL);
		URLConnection urlConn = null;
		BufferedReader in = null;
		boolean	bSuccess	=	true;
		String 	errMsg 			= "";
		
		try {
			urlConn = url.openConnection();
			urlConn.setDoOutput(true);
			urlConn.setUseCaches(false);
						
		    InputStream is = urlConn.getInputStream();
		    in = new BufferedReader(new InputStreamReader(is),8*1024);
		    
		    String code = null; //응답코드
		    String message = null; //응답메세지    
		    String temp = null;
		    int i=0;
		    while( (temp=in.readLine())!= null ) {
				if (i==0){
					    code = temp;
					}else{
					    message = temp;
					}
					i++;
				}
				if(code!=null && code.equals("OK")){ //성공
					//System.out.println("성공");
				}else if(code!=null && code.equals("FAIL")){ //실패
					//System.out.println(java.net.URLDecoder.decode(message, "UTF-8" ));
					//System.out.println(code);

					bSuccess	=	false;
					errMsg 	= message;
				}
			
		} catch (MalformedURLException e) {
			bSuccess	=	false;
			//System.out.println("[ERROR 1 "+this.getClass().toString()+".sendGuarData()] : "	+ e.toString());
			throw new MalformedURLException("[ERROR 1 "+this.getClass().toString()+".sendGuarData()] : "	+ e.toString());
		
		}catch (Exception e) {
			bSuccess	=	false;
			//System.out.println("[ERROR 2 "+this.getClass().toString()+".sendGuarData()] : "	+ e.toString());
			WAFLogger.error("[ERROR 1 "+this.getClass().toString()+".sendGuarData()] : "	+ e.toString());
		}
		this.storage.setMessage(errMsg);
		return	bSuccess;
	}
	/**
     * 전송 실패일경우 상태값을 04 신청 대기 상태로 놓는다.
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean WarrStatusUp(Connection connection, ValueObject valueObject, String rowIndex) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("UPDATE CONT_PUR_WARR SET  	\n");
        	sqlstr.append(" GUAR_STATUS = ?					\n");
        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
        	sqlstr.append(" AND WARR_GUBUN = ?						\n");
        	sqlstr.append(" AND WARR_SEQ = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("gStatus"));
            persistent.addParameter(valueObject.get("purContModId"));
            persistent.addParameter(valueObject.get("warrGubun"));
            persistent.addParameter(valueObject.get("warrSeq" + rowIndex));
            persistent.execute();

            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //System.out.println("Exception : " + e.getMessage());
            throw e;
        }
    }	

	/**
     * 취소 신청을 할경우 TRANSURE 쪽 데이터를 업데이트 한다
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean guarStatusUp(Connection connection, String repContrtRefId) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("UPDATE TRANSURE.EGRT_APPLI SET  	\n");
        	sqlstr.append(" DOC_STATUS_CD = 'DEL'					\n");
        	sqlstr.append("WHERE REP_CONTRT_REF_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(repContrtRefId);
            persistent.execute();

            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //System.out.println("Exception : " + e.getMessage());
            throw e;
        }
    }	

	public ValueObject cont_supp_info(String PUR_CONT_MOD_ID) {

		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject valueobject = null;
        
        StrBuffer sqlstr = new StrBuffer();
        try{ 
        	sqlstr.append("	SELECT A.MEMBER_NAME, A.USER_NAME, A.HP, A.EMAIL, C.TEL_NUM S_HP		\n");
			sqlstr.append("	FROM CONT_PUR_SUPP A, PUR2000T B, CONT_PUR_SUPP C   					\n");
			sqlstr.append("	WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID   							\n");
			sqlstr.append("	  AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID   							\n");
			sqlstr.append("	  AND A.SIGN_SEQ = 2							            			\n");
			sqlstr.append("	  AND C.SIGN_SEQ = 1							            			\n");
			sqlstr.append("	  AND A.PUR_CONT_MOD_ID = ?											\n");
			
			
			search.setStatement(sqlstr.toString());
			search.addParameter(PUR_CONT_MOD_ID);
			
			search.setStatement(sqlstr.toString());
			rowSet = search.execute();
			
			if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {
        	if( valueobject == null ) valueobject = new ValueObject();
            return valueobject;
        }
	}
}