/**
 * @(#)file    ContIntBiz.java
 * @(#)author  Kim Myung Ho
 * @(#)version 1.0
 * @(#)date    2014-07-15
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.supplier.portal.cont.biz;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

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.code.CommCodeManager;
import kr.co.udapsoft.common.sender.MessageSend;
import kr.co.udapsoft.common.util.PDFMaker;
import kr.co.udapsoft.common.util.Crosscert;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import kr.co.udapsoft.ebid.buyer.sycs.user.biz.UserCommon;

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


public class PurContBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
        ValueObject pricesysInfo = 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("")){
			
		}else if(storage.compareEvent("SHEET_SELECT")){
			params.set("sheet1", getContList(params));
			
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("FORM_SHEET_SELECT")){
			
			params.set("sheet2", getStampList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("FORM_SELECT")){
            user = getContRequestValueObject(params.get("PUR_CONT_MOD_ID"));
            storage.addValueObject("ContInfo", user);

			//저장된 HTML 정보
            RowSet template = getTemplateInfo(params.get("PUR_CONT_MOD_ID"));
            storage.addRowSet("template", template);
            
			//자동 저장 파일
			RowSet rowSet = getTemplateFile(params.get("PUR_CONT_MOD_ID"));
			storage.addRowSet("AUTOFILE_LIST", rowSet);            
			//표준 미연동 계약 정보
			pricesysInfo = getPricesysInfo(params.get("purContModId"));
			storage.addValueObject("pricesysInfo", pricesysInfo);            

			//보증 정보 가져오기
			params.set("MOD_NO", user.get("MOD_NO"));
			
			String wonCont = "K";
		        
		    if(!params.get("MOD_NO").equals("0")){
		        	wonCont = getWarrGubun(params.get("PUR_CONT_MOD_ID") );
		    }
		        
		    params.set("WON_WARR", wonCont);
		        
			
			
			params.set("PUR_CONT_ID", user.get("PUR_CONT_ID"));
			RowSet warrList = getWarrList(params);
			storage.addRowSet("warrList", warrList);            

			//보증완료된 정보
			RowSet warrCount = getWarrCount(params);
			storage.addRowSet("warrCount", warrCount);            

		}else if(storage.compareEvent("SIGN_OK")){
			
			String sMsg="";
			Crosscert crossErt = new Crosscert();
			sMsg = crossErt.chkSignVerify(params.get("CONT_SIGNDATA"));
			//System.out.println("sMsg ======= : " + sMsg);
			
			if(sMsg.equals("SIGN_SUCCESS")){
				/*if( params.get("p_issue_gb").equals("20") ) {
					updateStampDuty(super.conn.getConnection(), params);
				}*/
				CONT_SignOk(super.conn.getConnection(), params);	//서명저장
				params.set("pStatusCD", "30");
				ContStatusUp(super.conn.getConnection(), params);	//서명 완료

				Send(params, storage.getEvent());		//서명요청 메일과 SMS 전송

				super.storage.setSuccess(true);
			}else{
				super.storage.setSuccess(false);	
				super.storage.setMessage("인증서 검증에 실패 하였습니다.");
			}
		}else if(storage.compareEvent("EDIT_CALL")){	//수정요청
		
			/*if( params.get("p_issue_gb").equals("20") ) {
				updateStampDuty(super.conn.getConnection(), params);
			}*/
			
			params.set("pStatusCD", "40");
			ContStatusUp(super.conn.getConnection(), params);	//계약 수정 요청 상태 변경

			Send(params, storage.getEvent());		//서명요청 메일과 SMS 전송
			
			super.storage.setSuccess(true);
			
		}
	}
	

	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 RowSet 
	 */
	public RowSet getTemplateInfo(String sub_cont_cd){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT            							\n"
					+ "	  SEQ,                                      \n"
					+ "	  TEMPLATE_NAME,                            \n"
					+ "	  TEMPLATE_HTML,                            \n"
					+ "	  TEMPLATE_STYLE,                           \n"
					+ "	  CHECK_YN,                              \n"
					+ "	  OPTION_YN	                                \n"
                    + " FROM CONT_PUR_HTML      					\n"
                    + " WHERE PUR_CONT_MOD_ID =  '"+sub_cont_cd+"'		\n"
		            + " 	AND CHECK_YN IN ('Y','A')      				\n"
            		+ " ORDER BY SEQ ASC      				\n";
             
            search.setStatement(sqlstr);
            listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}
	
	 /**
     * 계약서식 조회 combo 조회
	 * @param 
	 * @param 
	 * @return RowSet 
	 */
	public RowSet getTemplateFile(String sub_cont_cd){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT FILE_ID, UNIQUE_FILE_NAME, FILE_PATH, FILE_EXT,          \n"   
            		+ "		TRIM(          \n"              
            		+ "        CASE                         \n"
				    + "       WHEN FILE_SIZE < 1024 THEN TO_CHAR(FILE_SIZE, '999,999') || ' Byte'          \n"
				    + "       ELSE          \n"
				    + "            CASE                WHEN FILE_SIZE < (1024*1024) THEN TO_CHAR(FLOOR(FILE_SIZE / 1024), '999,999') || ' KByte'          \n"
				    + "             ELSE  TO_CHAR(FLOOR(FILE_SIZE / (1024*1024)), '999,999,999') || ' MByte'          \n"
				    + "            END                           \n"                              
				    + "      END ) AS FILE_SIZE, FILE_NAME         \n"
                    + " FROM CONT_PUR_FILE      					\n"
                    + " WHERE PUR_CONT_MOD_ID =  '"+sub_cont_cd+"'		\n"
		            + " ORDER BY FILE_ID ASC      				\n";
             
            search.setStatement(sqlstr);
            listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}

	/**
	 * 계약 대상리스트 조회
	 * @param Params
	 * @return
	 */
	private RowSet getContList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject user = storage.getUser();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" 	SELECT A.PUR_CONT_MOD_ID	\n");
        	sqlstr.append(" 	     ,A.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(" 	     ,A.ORD_AMT	\n");
        	sqlstr.append(" 	     ,A.ORD_SUPPLY_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('CONT_STATUS_CD', A.STATUS_CD) AS STATUS_CD_NM	\n");
        	sqlstr.append(" 	     ,A.TEMPLATE_CD \n");
        	sqlstr.append(" 	     ,B.TEMPLATE_NAME \n");
        	sqlstr.append(" 	     ,C.CHG_CONT_YN \n");
        	sqlstr.append(" 	     ,A.CURRENCY_GB 	\n");
        	sqlstr.append(" 	  FROM PUR2000T A, CONT_PUR_HTML B, CONT_TEMPLATE C, CONT_PUR_SUPP F	\n");
        	sqlstr.append("   WHERE 1=1					\n");
        	sqlstr.append("   	AND A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID					\n");
        	sqlstr.append("   	AND A.TEMPLATE_CD = C.TEMPLATE_CD					\n");
        	sqlstr.append("   	AND B.SEQ=0					\n");
        	sqlstr.append("   	AND C.SEQ=0					\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.ELEC_CON_YN = 'Y'				\n");		
    		sqlstr.append("   	AND F.CORP_ID = "+ user.get("CORP_ID") +"			\n");
        	if(!param.get("P_DEPT_CD").isEmpty())
        		sqlstr.append("   AND A.DEPT_CD = '"+param.get("P_DEPT_CD")+"'		\n");
        	if(!param.get("P_DEPT_NM").isEmpty())	//현장명을 텍스트 검색으로 함
        		sqlstr.append("   AND A.DEPT_NAME LIKE '%'|| '"+param.get("P_DEPT_NM")+"' ||'%'			\n");
        	if(!param.get("P_BUILD_NM").isEmpty())
        		sqlstr.append("   AND A.CST_DOC_NAME LIKE '%"+param.get("P_BUILD_NM")+"%'				\n");        	
        	if(!param.get("ContFromDt").isEmpty())		//계약기간
        		sqlstr.append("   AND TO_CHAR(A.ORD_CON_DATE, 'YYYY-MM-DD') >= '"+param.get("ContFromDt")+"'				\n");
        	if(!param.get("ContToDt").isEmpty())
        		sqlstr.append("   AND TO_CHAR(A.ORD_CON_DATE, 'YYYY-MM-DD') <= '"+param.get("ContToDt")+"'				\n");
        	if(!param.get("P_CONT_STATUS").isEmpty())
        		sqlstr.append("   AND A.STATUS_CD IN ("+param.get("P_CONT_STATUS")+") 		\n");

        	sqlstr.append("   ORDER BY A.ORD_CON_DATE DESC, A.PUR_CONT_ID DESC, A.MOD_NO DESC	\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;
	}
	

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

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" 	SELECT A.PUR_CONT_MOD_ID	\n");
	    	sqlstr.append(" 	     , A.ORD_DOC_NO	\n");
	    	sqlstr.append(" 	     , A.PUR_CONT_ID	\n");
        	sqlstr.append(" 	     , A.MOD_NO	\n");	
        	sqlstr.append(" 	     , DECODE(A.MOD_GB, NULL, '00', A.MOD_GB) AS MOD_GB	\n");	
        	sqlstr.append(" 	     , A.DEPT_NAME	\n");	
        	sqlstr.append(" 	     , A.CST_DOC_NAME	\n");	
        	sqlstr.append(" 	     , A.ORD_CON_DATE	\n");	
//        	sqlstr.append(" 	     , A.DEALER_NM	\n");	
        	sqlstr.append(" 	     , A.ORD_AMT	\n");	
        	sqlstr.append(" 	     , A.ORD_START_DATE	\n");	
        	sqlstr.append(" 	     , A.ELEC_CON_YN	\n");	
        	sqlstr.append(" 	     , TO_CHAR(A.ORD_START_DATE,'YYYY') AS ORD_START_DATE_YEAR 	\n");	
        	sqlstr.append(" 	     , TO_CHAR(A.ORD_START_DATE,'MM') AS ORD_START_DATE_MONTH 	\n");	
        	sqlstr.append(" 	     , TO_CHAR(A.ORD_START_DATE,'DD') AS ORD_START_DATE_DAY 	\n");	
        	sqlstr.append(" 	     , A.ORD_END_DATE	\n");	
        	sqlstr.append(" 	     , TO_CHAR(A.ORD_END_DATE,'YYYY') AS ORD_END_DATE_YEAR 	\n");	
        	sqlstr.append(" 	     , TO_CHAR(A.ORD_END_DATE,'MM') AS ORD_END_DATE_MONTH 	\n");	
        	sqlstr.append(" 	     , TO_CHAR(A.ORD_END_DATE,'DD') AS ORD_END_DATE_DAY 	\n");	
        	sqlstr.append(" 	     , A.CON_GB 	\n");	
        	sqlstr.append(" 	     , A.LEAD_PLACE 	\n");	
        	sqlstr.append("       	 , A.LEAD_COND_GB		 	\n");
        	sqlstr.append("       	 , F_GET_CODE_NAME('PUR_LEAD_GB', A.LEAD_COND_GB) LEAD_COND_GB_NM	 	\n");	 //추가 (2014-09-19)
        	sqlstr.append("       	 , A.ORD_SUPPLY_AMT			\n");
        	sqlstr.append("       	 , A.ORD_SURTAX_AMT			\n");
        	sqlstr.append("      	 , A.PREPAY_AMT_RATE			\n");
        	sqlstr.append("       	 , A.PREPAY_AMT			\n");
        	sqlstr.append("       	 , A.MID_AMT_RATE			\n");
        	sqlstr.append("       	 , A.MID_AMT			\n");
        	sqlstr.append("       	 , A.RMDR_AMT_RATE			\n");
        	sqlstr.append("       	 , A.RMDR_AMT			\n");
        	sqlstr.append("       	 , A.CON_GUAR_AMT_RATE			\n");
        	sqlstr.append("       	 , A.CON_GUAR_TERM			\n");
        	sqlstr.append("       	 , A.CON_GUAR_AMT			\n");
        	sqlstr.append("       	 , A.DEF_GUAR_AMT_RATE			\n");
        	sqlstr.append("       	 , A.DEF_GUAR_AMT			\n");
        	sqlstr.append("       	 , A.DEF_GUAR_TERMS			\n");
        	sqlstr.append("       	 , A.DEF_GUAR_TERMS_TXT			\n");
        	sqlstr.append("       	 , A.DEF_GUAR_TERMS2			\n");
        	sqlstr.append("       	 , A.DEF_GUAR_TERMS_TXT2			\n");
        	sqlstr.append("       	 , A.DEL_RATE_GB			\n");
        	
        	sqlstr.append(" 	     , TO_CHAR(A.ORD_CON_DATE,'YYYY') AS ORD_CON_DATE_YEAR 	\n");	
        	sqlstr.append(" 	     , TO_CHAR(A.ORD_CON_DATE,'MM') AS ORD_CON_DATE_MONTH 	\n");	
        	sqlstr.append(" 	     , TO_CHAR(A.ORD_CON_DATE,'DD') AS ORD_CON_DATE_DAY 	\n");	
        	sqlstr.append(" 	     , A.STATUS_CD 	\n");	
        	sqlstr.append(" 	     , DECODE(A.STATUS_CD,'10','작성중','20','을(乙) 서명요청','30','을(乙) 서명완료','40','수정요청') STATUS_NM 	\n");
        	sqlstr.append(" 	     , A.TEMPLATE_CD 	\n");	
        	sqlstr.append(" 	     , A.TRUE_RANDOM 	\n");
        	sqlstr.append(" 	     , A.CONT_HASH 	\n");	
        	sqlstr.append(" 	     , A.MODI_REQ_REASON 	\n");	
        	sqlstr.append(" 	     , A.MODI_REQ_DT 	\n");	
        	
        	sqlstr.append(" 	     , A.STAMP_DUTY_GB	\n");

        	sqlstr.append(" 	     , B.DEMANDED_NO	\n");	
        	sqlstr.append(" 	     , B.DEMANDED_ID	\n");	
        	sqlstr.append(" 	     , B.DEMANDED_TITLE	\n");	
        	sqlstr.append(" 	     , TO_CHAR(B.REQUEST_DATE,'YYYY-MM-DD') AS REQUEST_DATE	\n");	
        	sqlstr.append(" 	     , B.REQ_DEPT_NM	\n");	
        	sqlstr.append(" 	     , B.EMP_NO	\n");	
        	sqlstr.append(" 	     , B.EMP_NM	\n");	
        	/*분개 유형*/
        	sqlstr.append(" 	     , A.ACCT_GB 	\n");	
        	sqlstr.append(" 	     , A.PAY_GROUP 	\n");	
        	sqlstr.append(" 	     , A.GUA_GB 	\n");	
        	sqlstr.append(" 	     , A.PAYMENT_TERMS 	\n");	
        	sqlstr.append(" 	     , A.PAY_ITEM 	\n");	
        	sqlstr.append(" 	     , A.COST_CODE 	\n");	
        	sqlstr.append(" 	     , A.COST_TYPE 	\n");	
        	sqlstr.append(" 	     , A.DEPT_CD 	\n");		        	
        	sqlstr.append(" 	     , F_GET_PAY_ITEM_EST_DESC(A.PAY_ITEM,A.COST_CODE,A.COST_TYPE,A.DEPT_CD) AS PAY_ITEM_NM	\n");
        	sqlstr.append(" 	     , (SELECT AC_UNIT_CD FROM ACZ10100V Z WHERE  Z.DEPT_CD = A.DEPT_CD) AS  AC_UNIT_CD	\n");		        	

        	sqlstr.append(" 	     , DECODE(D.CORP_ID, '', 0, D.CORP_ID)  AS O_CORP_ID	\n");	
        	sqlstr.append(" 	     , DECODE(D.MEMBER_NAME, '', C.CORP_KOR, D.MEMBER_NAME)  AS O_MEMBER_NAME	\n");	
        	sqlstr.append(" 	     , DECODE(D.ADDRESS, '', C.ADDRESS, D.ADDRESS) AS O_ADDRESS	\n");	
        	sqlstr.append(" 	     , DECODE(D.BOSS_NAME, '', C.REP_NAME, D.BOSS_NAME) AS O_BOSS_NAME	\n");	
        	sqlstr.append(" 	     , D.USER_NAME AS O_USER_NAME	\n");	
        	sqlstr.append(" 	     , D.TEL_NUM AS O_TEL_NUM	\n");	
        	sqlstr.append(" 	     , D.DIVISION AS O_DIVISION	\n");	
        	sqlstr.append(" 	     , D.POSITION AS O_POSITION	\n");	
        	sqlstr.append(" 	     , D.HP AS O_HP	\n");	
        	sqlstr.append(" 	     , D.EMAIL AS O_EMAIL	\n");	
        	sqlstr.append(" 	     , TO_CHAR(D.SIGN_DATE,'YYYY-MM-DD HH24:MI:SS') AS O_SIGN_DATE	\n");	
        	sqlstr.append(" 	     , D.VENDCD AS O_VENDCD	\n");		        	
        	sqlstr.append(" 	     , E.CORP_ID  AS C_CORP_ID	\n");	
        	sqlstr.append(" 	     , E.MEMBER_NAME AS C_MEMBER_NAME	\n");	
        	sqlstr.append(" 	     , E.ADDRESS AS C_ADDRESS	\n");	
        	sqlstr.append(" 	     , E.BOSS_NAME AS C_BOSS_NAME	\n");	
        	sqlstr.append(" 	     , E.USER_NAME AS C_USER_NAME	\n");	
        	sqlstr.append(" 	     , E.TEL_NUM AS C_TEL_NUM	\n");	
        	sqlstr.append(" 	     , E.DIVISION AS C_DIVISION	\n");	
        	sqlstr.append(" 	     , E.POSITION AS C_POSITION	\n");	
        	sqlstr.append(" 	     , E.HP AS C_HP	\n");		        	
        	sqlstr.append(" 	     , E.VENDCD AS C_VENDCD	\n");		        	
        	sqlstr.append(" 	     , E.EMAIL AS C_EMAIL	\n");	
        	sqlstr.append(" 	     , TO_CHAR(E.SIGN_DATE,'YYYY-MM-DD HH24:MI:SS') AS C_SIGN_DATE	\n");	

//        	sqlstr.append(" 	     , NVL(F.STAMP_DUTY_ISSUE_GB,'00') AS STAMP_DUTY_ISSUE_GB	\n");
//        	sqlstr.append(" 	     , F.STAMP_DUTY_NO											\n");
//        	sqlstr.append(" 	     , F.STAMP_DUTY_TAX											\n");
//        	sqlstr.append(" 	     , F.STAMP_DUTY_DATE										\n");
//        	sqlstr.append(" 	     , F.REMARK													\n");
//        	sqlstr.append(" 	     , F.STAMP_DUTY_ID											\n");
        	
			sqlstr.append("  FROM PUR2000T A, BID_MASTER B, HEAD_OFFICE_INFO C, CONT_PUR_SUPP D, CONT_PUR_SUPP E, STAMP_DUTY F	\n");
			sqlstr.append("  WHERE A.BID_NO = B.BID_NO(+) 	\n");
			sqlstr.append("  AND A.BID_DEG = B.BID_DEG(+)	\n");
			sqlstr.append("  AND A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID(+)	\n");
			sqlstr.append("  AND D.SIGN_SEQ(+) = 1 --원사업자	\n");
			sqlstr.append("  AND A.PUR_CONT_MOD_ID = E.PUR_CONT_MOD_ID(+)	\n");
			sqlstr.append("  AND E.SIGN_SEQ(+) = 2 --계약업체정보  	\n");
			sqlstr.append("  AND A.PUR_CONT_MOD_ID = '"+PUR_CONT_MOD_ID+"'							\n");
//			sqlstr.append("  AND A.PUR_CONT_MOD_ID = F.CONT_MOD_ID(+)	\n");
			sqlstr.append("  AND F.STAMP_DUTY_TYPE(+) = '10'			\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 RowSet getWarrList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        /*
		보증서 종류
		  002 : 계약이행
		  003 : 하자
		  004 : 선금
		  005 : 근로자재해
		  39 : 하도급대금지급         * 
         * */
        StrBuffer sqlstr = new StrBuffer();
        try{
        	if(param.get("MOD_NO").equals("0")){
	        	sqlstr.append("	SELECT  WARR_GUBUN	\n");
	        	sqlstr.append("	    , WARR_GUBUN_NM	\n");
	        	sqlstr.append("	    , MAX(MOD_NO) AS MOD_NO	\n");
	        	sqlstr.append("	    , MAX(WARR_SEQ) 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 = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
	        	sqlstr.append("	                     AND WARR_GUBUN IN ('1','4')	\n");
	        	sqlstr.append("		\n");
	        	sqlstr.append("	              UNION ALL	\n");
	        	sqlstr.append("		\n");
	        	sqlstr.append("	              SELECT '1' AS WARR_GUBUN	\n");
	        	sqlstr.append("	                   , '계약이행보증' AS WARR_GUBUN_NM	\n");
	        	sqlstr.append("	                   , MOD_NO	\n");
	        	sqlstr.append("	                   , 1 AS WARR_SEQ	\n");
	        	sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
	        	sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
	        	sqlstr.append("	                   , '01' AS CAUSECODE	\n");
	        	sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE','01') AS CAUSECODE_NM	\n");
	        	sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
	        	sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
	        	sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
	        	sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
	        	sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
	        	sqlstr.append("	                   , '' AS PAPERDATE	\n");
	        	sqlstr.append("	                   , '' AS WARR_WGNO	\n");
	        	sqlstr.append("	                   , '' AS WARR_SDATE	\n");
	        	sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
	        	sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
	        	sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
	        	sqlstr.append("	                   , '' AS GRTREQNO 	\n");
	        	sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
	        	sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
	        	sqlstr.append("	                     AND B.CON_GUAR_AMT > 0	\n");
	        	sqlstr.append("	                	\n");
	        	sqlstr.append("		\n");
	        	sqlstr.append("	              UNION ALL	\n");
	        	sqlstr.append("		\n");
	        	sqlstr.append("	              SELECT '4' AS WARR_GUBUN	\n");
	        	sqlstr.append("	                   , '근로자재해보증' AS WARR_GUBUN_NM	\n");
	        	sqlstr.append("	                   , MOD_NO	\n");
	        	sqlstr.append("	                   , 2 AS WARR_SEQ	\n");
	        	sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
	        	sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
	        	sqlstr.append("	                   , '01' AS CAUSECODE	\n");
	        	sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE','01') AS CAUSECODE_NM	\n");
	        	sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
	        	sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
	        	sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
	        	sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
	        	sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
	        	sqlstr.append("	                   , '' AS PAPERDATE	\n");
	        	sqlstr.append("	                   , '' AS WARR_WGNO	\n");
	        	sqlstr.append("	                   , '' AS WARR_SDATE	\n");
	        	sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
	        	sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
	        	sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
	        	sqlstr.append("	                   , '' AS GRTREQNO 	\n");
	        	sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
	        	sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
	        	sqlstr.append("	                     AND B.CON_GB = '15'	\n");
	        	sqlstr.append("	                	\n");	        	
	        	sqlstr.append("	     )	\n");
	        	sqlstr.append("	GROUP BY  WARR_GUBUN, WARR_GUBUN_NM	\n");
	        	sqlstr.append("	ORDER BY  WARR_GUBUN	\n");
	        	sqlstr.append("	--, WARR_SEQ, WARR_WAMT, VIEW_DIV, CAUSECODE, WARR_GOFFICE, GUAR_STATUS, PAPERDATE, WARR_WGNO, WARR_SDATE, WARR_EDATE 	\n");
        	}else{	//변경 계약일경우 
        		
        		if(!param.get("WON_WARR").equals("S")){
        			
        			sqlstr.append("	SELECT  WARR_GUBUN	\n");
        			sqlstr.append("	    , WARR_GUBUN_NM	\n");
        			sqlstr.append("	    , MAX(MOD_NO) AS MOD_NO	\n");
        			sqlstr.append("	    , WARR_SEQ	\n");
        			sqlstr.append("	    , MAX(WARR_WAMT) AS WARR_WAMT	\n");
        			sqlstr.append("	    , MAX(VIEW_DIV) AS VIEW_DIV	\n");
        			sqlstr.append("	    , CAUSECODE AS CAUSECODE	\n");
        			sqlstr.append("	    , 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 = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND WARR_GUBUN IN ('1','4')	\n");
//	        	sqlstr.append("	                     AND WARR_GUBUN IN ('1','3','4')	\n");		//선금급 연장일경우 주석 해제
        			sqlstr.append("		\n");
        			sqlstr.append("	              UNION ALL	\n");
        			sqlstr.append("		\n");
        			sqlstr.append("	              SELECT '1' AS WARR_GUBUN	\n");
        			sqlstr.append("	                   , '계약이행보증' AS WARR_GUBUN_NM	\n");
        			sqlstr.append("	                   , MOD_NO	\n");
        			sqlstr.append("	                   , 1 AS WARR_SEQ	\n");
        			sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        			sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
        			sqlstr.append("	                   , DECODE(SUBSTR(MOD_GB,1,1),1,'02',2,'08') AS CAUSECODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',DECODE(SUBSTR(MOD_GB,1,1),1,'02',2,'08')) AS CAUSECODE_NM	\n");
        			sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	                   , '' AS PAPERDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_WGNO	\n");
        			sqlstr.append("	                   , '' AS WARR_SDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
        			sqlstr.append("	                   , '' AS GRTREQNO 	\n");
        			sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
        			sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.CON_GUAR_AMT > 0	\n");
        			sqlstr.append("	                     AND B.MOD_GB NOT IN ('99', '98')	\n"); //2019.10.18 타절계약 추가
        			sqlstr.append("	                     AND SUBSTR(MOD_GB,1,1) !=0	\n");
        			sqlstr.append("	    \n");
        			sqlstr.append("	              UNION ALL	\n");
        			sqlstr.append("		\n");
        			sqlstr.append("	              SELECT '1' AS WARR_GUBUN	\n");
        			sqlstr.append("	                   , '계약이행보증' AS WARR_GUBUN_NM	\n");
        			sqlstr.append("	                   , MOD_NO	\n");
        			sqlstr.append("	                   , 2 AS WARR_SEQ	\n");
        			sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        			sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
        			sqlstr.append("	                   , DECODE(SUBSTR(MOD_GB,2,1),1,'03',2,'09') AS CAUSECODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',DECODE(SUBSTR(MOD_GB,2,1),1,'03',2,'09')) AS CAUSECODE_NM	\n");
        			sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	                   , '' AS PAPERDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_WGNO	\n");
        			sqlstr.append("	                   , '' AS WARR_SDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
        			sqlstr.append("	                   , '' AS GRTREQNO 	\n");
        			sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
        			sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.CON_GUAR_AMT > 0	\n");
        			sqlstr.append("	                     AND B.MOD_GB NOT IN ('99', '98')	\n"); //2019.10.18 타절계약 추가
        			sqlstr.append("	                     AND SUBSTR(MOD_GB,2,1) !=0	\n");
        			sqlstr.append("	                	\n");
        			sqlstr.append("	    \n");
        			sqlstr.append("	              UNION ALL	\n");
        			sqlstr.append("		\n");
        			sqlstr.append("	              SELECT '4' AS WARR_GUBUN	\n");
        			sqlstr.append("	                   , '근로자재해보증' AS WARR_GUBUN_NM	\n");
        			sqlstr.append("	                   , MOD_NO	\n");
        			sqlstr.append("	                   , 3 AS WARR_SEQ	\n");
        			sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        			sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
        			sqlstr.append("	                   , DECODE(MOD_GB,'01','03','11','04','12','06','10','02','21','05','22','07','20','08','02','09') AS CAUSECODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',DECODE(MOD_GB,'01','03','11','04','12','06','10','02','21','05','22','07','20','08','02','09')) AS CAUSECODE_NM	\n");
        			sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	                   , '' AS PAPERDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_WGNO	\n");
        			sqlstr.append("	                   , '' AS WARR_SDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
        			sqlstr.append("	                   , '' AS GRTREQNO 	\n");
        			sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
        			sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.MOD_GB NOT IN ('99', '98')	\n"); //2019.10.18 타절계약 추가
        			sqlstr.append("	                     AND B.CON_GB = '15'	\n");
        			sqlstr.append("	                	\n");        			
        			sqlstr.append("	     )	\n");
    	        	sqlstr.append("	GROUP BY  WARR_GUBUN, WARR_GUBUN_NM, WARR_SEQ, CAUSECODE, CAUSECODE_NM	\n");
    	        	sqlstr.append("	ORDER BY  WARR_GUBUN	\n");
    	        	sqlstr.append("	--, WARR_SEQ, WARR_WAMT, VIEW_DIV, CAUSECODE, WARR_GOFFICE, GUAR_STATUS, PAPERDATE, WARR_WGNO, WARR_SDATE, WARR_EDATE 	\n");
        		}else{
        			
        			sqlstr.append("	SELECT  WARR_GUBUN	\n");
        			sqlstr.append("	    , WARR_GUBUN_NM	\n");
        			sqlstr.append("	    , MAX(MOD_NO) AS MOD_NO	\n");
        			sqlstr.append("	    , WARR_SEQ	\n");
        			sqlstr.append("	    , MAX(WARR_WAMT) AS WARR_WAMT	\n");
        			sqlstr.append("	    , MAX(VIEW_DIV) AS VIEW_DIV	\n");
        			sqlstr.append("	    , CAUSECODE AS CAUSECODE	\n");
        			sqlstr.append("	    , 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 = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND WARR_GUBUN IN ('1','4')	\n");
//	        	sqlstr.append("	                     AND WARR_GUBUN IN ('1','3','4')	\n");		//선금급 연장일경우 주석 해제
        			sqlstr.append("		\n");
        			sqlstr.append("	              UNION ALL	\n");
        			sqlstr.append("		\n");
        			sqlstr.append("	              SELECT '1' AS WARR_GUBUN	\n");
        			sqlstr.append("	                   , '계약이행보증' AS WARR_GUBUN_NM	\n");
        			sqlstr.append("	                   , MOD_NO	\n");
        			sqlstr.append("	                   , 1 AS WARR_SEQ	\n");
        			sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        			sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
        			sqlstr.append("	                   , F_GET_CODE_CHGCODE1('MOD_GB',MOD_GB) AS CAUSECODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',F_GET_CODE_CHGCODE1('MOD_GB',MOD_GB)) AS CAUSECODE_NM	\n");
        			sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	                   , '' AS PAPERDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_WGNO	\n");
        			sqlstr.append("	                   , '' AS WARR_SDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
        			sqlstr.append("	                   , '' AS GRTREQNO 	\n");
        			sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
        			sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.CON_GUAR_AMT > 0	\n");
        			sqlstr.append("	                     AND B.MOD_GB NOT IN ('99', '00', '98')	\n"); //2019.10.18 타절계약 추가
        			sqlstr.append("		\n");
        			sqlstr.append("	              UNION ALL	\n");
        			sqlstr.append("		\n");
        			sqlstr.append("	              SELECT '4' AS WARR_GUBUN	\n");
        			sqlstr.append("	                   , '근로자재해보증' AS WARR_GUBUN_NM	\n");
        			sqlstr.append("	                   , MOD_NO	\n");
        			sqlstr.append("	                   , 3 AS WARR_SEQ	\n");
        			sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        			sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
        			sqlstr.append("	                   , DECODE(MOD_GB,'01','03','11','04','12','06','10','02','21','05','22','07','20','08','02','09') AS CAUSECODE	\n");
        			sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE',DECODE(MOD_GB,'01','03','11','04','12','06','10','02','21','05','22','07','20','08','02','09')) AS CAUSECODE_NM	\n");
        			sqlstr.append("	                   , '' AS GUAR_ORGAN_CODE	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE_ID	\n");
        			sqlstr.append("	                   , '' AS WARR_GOFFICE	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
        			sqlstr.append("	                   , '' AS GUAR_STATUS_NM	\n");
        			sqlstr.append("	                   , '' AS PAPERDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_WGNO	\n");
        			sqlstr.append("	                   , '' AS WARR_SDATE	\n");
        			sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_NM 	\n");
        			sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
        			sqlstr.append("	                   , '' AS GRTREQNO 	\n");
        			sqlstr.append("	                FROM DUAL A, PUR2000T B  	\n");
        			sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.MOD_GB NOT IN ('99', '98') 	\n"); //2019.10.18 타절계약 추가
        			sqlstr.append("	                     AND B.CON_GB = '15'	\n");        			
        			sqlstr.append("	     )	\n");
    	        	sqlstr.append("	GROUP BY  WARR_GUBUN, WARR_GUBUN_NM, WARR_SEQ, CAUSECODE, CAUSECODE_NM	\n");
    	        	sqlstr.append("	ORDER BY  WARR_GUBUN	\n");
    	        	sqlstr.append("	--, WARR_SEQ, WARR_WAMT, VIEW_DIV, CAUSECODE, WARR_GOFFICE, GUAR_STATUS, PAPERDATE, WARR_WGNO, WARR_SDATE, WARR_EDATE 	\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("	                   , 3 AS WARR_SEQ	\n");
	        	sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
	        	sqlstr.append("	                   , 'CONT' AS VIEW_DIV	\n");
	        	sqlstr.append("	                   , '12' AS CAUSECODE	\n");
	        	sqlstr.append("	                   , F_GET_CODE_NAME('CAUSECODE','12') 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, (SELECT X.ORD_DOC_NO, X.PUR_CONT_ID, X.MOD_NO, Y.WARR_SDATE, Y.WARR_WGNO, Y.WARR_WAMT  	\n");
	        	sqlstr.append("	                        FROM PUR2000T X, CONT_PUR_WARR Y  	\n");
	        	sqlstr.append("	                       WHERE X.PUR_CONT_ID = '"+ param.get("PUR_CONT_ID") +"'  	\n");
	        	sqlstr.append("	                         AND X.MOD_NO = (SELECT MAX(MOD_NO)  	\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 = 3  	\n");
	        	sqlstr.append("	                                            AND WARR_WGNO IS NOT NULL)  	\n");
	        	sqlstr.append("	                         AND Y.PUR_CONT_ID = X.PUR_CONT_ID  	\n");
	        	sqlstr.append("	                         AND Y.MOD_NO = X.MOD_NO  	\n");
	        	sqlstr.append("	                         AND Y.WARR_GUBUN = 3  	\n");
	        	sqlstr.append("	                         AND Y.WARR_WGNO IS NOT NULL  	\n");
	        	sqlstr.append("	                     ) C                                       -- 계약서 (보증서가 있는 최초차수)  	\n");
	        	sqlstr.append("	               WHERE B.PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
	        	sqlstr.append("	                     AND  C.WARR_WAMT > 0		\n");
	        	sqlstr.append("	                     AND SUBSTR(MOD_GB,1,1) = 1	\n");
	        	sqlstr.append("	                	\n");
	        */
        	}
        	//System.out.println("### MOD_NO : " + sqlstr.toString());

			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;
	}
	
	
	/**
	 * 보증완료 목록 조회
	 * @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("PUR_CONT_MOD_ID") +"' 	\n");
        	sqlstr.append("	                     AND WARR_GUBUN IN ('1','4')	\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;
	}
		
	
	/**
     * 계약 상태 변경
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean ContStatusUp(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("UPDATE PUR2000T SET  	\n");
        	sqlstr.append(" STATUS_CD = ?					\n");
        	if (!valueObject.get("MODI_REQ_REASON").equals(null)){
	        	sqlstr.append(" , MODI_REQ_REASON = '"+ valueObject.get("MODI_REQ_REASON") +"' 	\n");
	        	sqlstr.append(" , MODI_REQ_DT = SYSDATE			\n");
        	}        	
        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("pStatusCD"));
            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
            persistent.execute();

            
            //권준호 부장님 프로시저 콜
      		CallableStatement cs 	= null;
    		String pssql			= "";
    		
        	pssql = "{call SP_REG_PSR_STATUS('','23','3','',?,?,'',?,SYSDATE,'',?,?)}";
        	cs = super.conn.getConnection().prepareCall(pssql);
        	cs.setString(1, params.get("DEMANDED_ID"));
        	cs.setString(2, params.get("PUR_CONT_ID"));
        	cs.setString(3, storage.getUser().get("acct_id"));
            cs.registerOutParameter(4, java.sql.Types.VARCHAR);
            cs.registerOutParameter(5, java.sql.Types.VARCHAR);
            cs.execute();
            
            //System.out.println("VO_SUCCESS_CD : " + cs.getString(4));
            
            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 CONT_SignOk(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("UPDATE CONT_PUR_SUPP SET  	\n");
        	sqlstr.append(" SIGN_DATE = SYSDATE					\n");
        	sqlstr.append(" ,SIGN_DN = ?					\n");
        	sqlstr.append(" ,SIGN_DATA = ?					\n");
        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
        	sqlstr.append(" 	AND SIGN_SEQ=2						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("CONT_SIGNDN"));
            persistent.addParameter(valueObject.get("CONT_SIGNDATA"));
            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
            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 void Send(ValueObject params, String event) throws Exception {
		MessageSend messageSend = new MessageSend(storage);
	
		ValueObject contSendInfo = new ValueObject();
		contSendInfo = getContRequestValueObject(params.get("PUR_CONT_MOD_ID"));
		
		if(!contSendInfo.get("O_EMAIL").isEmpty()){
			boolean email = false;
	 		StringBuffer mailtitle = new StringBuffer();
	 		StringBuffer mailtitlein = new StringBuffer();
	        StringBuffer mailContent = new StringBuffer();
	        if("EDIT_CALL".equals(event)){//수정요청
	        	mailtitle.append("* 계약내용 수정요청 메일입니다.");
	            mailtitlein.append("* 계약내용 수정요청 메일입니다.");
	         	mailContent.append(contSendInfo.get("C_MEMBER_NAME")+"님이 계약내용 수정을 요청하였습니다.<br>전자조달 시스템에서 계약내용 수정사유를 확인하시고 재 수정 바랍니다.<br>");
	         	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	         	mailContent.append("<br>");
	         	mailContent.append("1.프로젝트 : " + contSendInfo.get("DEPT_NAME") + "<br>");
	         	mailContent.append("2.계약번호 : " + contSendInfo.get("ORD_DOC_NO") + "<br>");
	         	mailContent.append("3.계약명 : " + contSendInfo.get("CST_DOC_NAME") + "<br>");
	         	mailContent.append("4.수정요청사유 : " + params.get("MODI_REQ_REASON") + "<br>");
	         	mailContent.append("5.협력사 담당자 : " + contSendInfo.get("C_USER_NAME") +" (연락처 : " + contSendInfo.get("C_TEL_NUM") + ")<br>");
	         	mailContent.append("</font>");
	        	email = true;	 
	        }else if("SIGN_OK".equals(event)){//서명완료 갑 서명 요청
	        	mailtitle.append("* 계약서 서명요청 메일입니다.");
	            mailtitlein.append("* 계약서 서명요청 메일입니다.");
	         	mailContent.append(contSendInfo.get("C_MEMBER_NAME")+"님이 계약서에 서명하였습니다.<br>업체 서명 정보 확인후 계약서에 서명하시기 바랍니다.<br>");
	         	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	         	mailContent.append("<br>");
	         	mailContent.append("1.프로젝트 : " + contSendInfo.get("DEPT_NAME") + "<br>");
	         	mailContent.append("2.계약번호 : " + contSendInfo.get("ORD_DOC_NO") + "<br>");
	         	mailContent.append("3.계약명 : " + contSendInfo.get("CST_DOC_NAME") + "<br>");
	         	mailContent.append("4.협력사 담당자 : " + contSendInfo.get("C_USER_NAME") +" (연락처 : " + contSendInfo.get("C_TEL_NUM") + ")<br>");
	         	mailContent.append("</font>");
	        	email = true;	 
	 		}
	        if(email){
	        	messageSend.sendEmail(contSendInfo.get("O_EMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent,"Y");
	        }
		}
 		//System.out.println("담당자 HP : " + contSendInfo.get("O_HP").replace("-", ""));
		if(!contSendInfo.get("O_HP").isEmpty()){
	 		String smsContent = "";
	 		if("EDIT_CALL".equals(event)){//수정요청
	 			smsContent = WAFMessages.getMessage("Contract.Information.Request");
	 		}else if("SIGN_OK".equals(event)){//서명완료 갑 서명 요청
	 			smsContent = WAFMessages.getMessage("Contract.Signing.Request");
	 		}
	 		 
	        String se_phon_no = "024899000"; //보내는사람 2015.9.21 협력업체->담당자 전송. 대표번호  contSendInfo.get("C_HP").replace("-", "")
	        String re_phon_no = contSendInfo.get("O_HP").replace("-", ""); //받는사람
	        if(!smsContent.isEmpty()){
	        	messageSend.sendSMS(re_phon_no, se_phon_no, storage.getUser().get("ACCT_ID"), smsContent); //2015.9.21 협력업체 서명요청 
	        }
		}
	}
	
	/**
     * 인지세 List
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	private RowSet getStampList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        
        try{

        	sqlstr.append("    SELECT                                                                                                      \n");  
			sqlstr.append("            STAMP_DUTY_ID                                                                                       \n");
			sqlstr.append("           ,STAMP_DUTY_ISSUE_GB                                                                                 \n");    
			sqlstr.append("           ,STAMP_DUTY_NO                                                                                       \n");    
			sqlstr.append("           ,STAMP_DUTY_TAX                                                                                      \n");    
			sqlstr.append("           ,STAMP_DUTY_DATE                                                                                     \n");    
			sqlstr.append("           ,REMARK                                                                                              \n");
			sqlstr.append("           ,(SELECT DECODE(MAX(AA.FILE_ID), NULL, '파일첨부', '1') FROM PUB0510T AA, PUB0500T BB                \n");
			sqlstr.append("        		   WHERE AA.FILE_ID = BB.FILE_ID AND FILE_GB = 'PUR_STAMP_DUTY'                                    \n");
			sqlstr.append("        		   AND ID = B.STAMP_DUTY_ID) AS FILE_YN                                                            \n");
//			sqlstr.append("        	  ,'1' AS FILE_YN                                                            						   \n");
			sqlstr.append("           ,(SELECT MAX(AA.FILE_ID) FROM PUB0510T AA, PUB0500T BB                                               \n");
			sqlstr.append("        		   WHERE AA.FILE_ID = BB.FILE_ID AND FILE_GB = 'PUR_STAMP_DUTY'                                    \n");
			sqlstr.append("        		   AND ID = B.STAMP_DUTY_ID) AS FILE_ID                                                            \n");                                  
			sqlstr.append("    FROM PUR2000T A,                                                                                            \n"); 
			sqlstr.append("         STAMP_DUTY B                                                                                           \n"); 
			sqlstr.append("    WHERE A.PUR_CONT_MOD_ID = B.CONT_MOD_ID                                                                     \n");
			sqlstr.append("    AND B.STAMP_DUTY_TYPE(+) = '10'                                                                             \n");
			sqlstr.append("    AND A.PUR_CONT_MOD_ID = '"+params.get("PUR_CONT_MOD_ID")+"'                                                 \n");
			sqlstr.append("    ORDER BY STAMP_DUTY_ID DESC								                                 				   \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);
            WAFLogger.error("### WAFSQLException : " + se.getMessage());
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}
	
	
	/**
	 * 을) 인지세 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	/*public boolean updateStampDuty(Connection connection, ValueObject valueObject) throws Exception {
		
		Persistent persistent = new PersistentImpl(connection);
		StrBuffer sqlstr = new StrBuffer();
		try {
			
			sqlstr.append("UPDATE	STAMP_DUTY SET			\n");
			sqlstr.append("			STAMP_DUTY_NO = ?		\n");
			sqlstr.append("        ,STAMP_DUTY_TAX = ?		\n");
			sqlstr.append("        ,STAMP_DUTY_DATE = ?		\n");
			sqlstr.append("        ,REMARK = ?				\n");
			sqlstr.append("WHERE	CONT_MOD_ID = ?			\n");
			sqlstr.append("AND		STAMP_DUTY_TYPE = '10'	\n");
	
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(valueObject.get("stamp_duty_no"));
			persistent.addParameter(valueObject.get("stamp_duty_tax").replaceAll(",", ""));
			persistent.addParameter(valueObject.get("stamp_duty_date").replaceAll("-", ""));
			persistent.addParameter(valueObject.get("remark"));
			persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
			persistent.execute();
			
			return true;
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> updateStampDuty() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			//System.out.println("Exception : " + e.getMessage());
			throw e;
		}
	}*/
	
	
	/*
	 * 원계약건이 전문건설인지 서울보증인지 확인 
	 * */
	public String getWarrGubun(String purContModId) {
	 	
	 	Search search = new SearchImpl(); 
	 	RowSet rowSet = new RowSetImpl();
	 	StringBuffer sqlstr = new StringBuffer();
	 	String rtnStr = "K";
	 	try {
	 		
	 		sqlstr.append("				SELECT DECODE(GUAR_ORGAN_CODE , '2', 'S','K') AS WARRGUBUN																			\n");
	 		sqlstr.append("				     , WARR_GOFFICE                 																								\n");
	 		sqlstr.append("				FROM CONT_PUR_WARR                        																							\n");
	 		sqlstr.append("				WHERE PUR_CONT_MOD_ID = (                 																							\n");
	 		sqlstr.append("											SELECT PUR_CONT_MOD_ID                    																\n");
	 		sqlstr.append("											FROM PUR2000T                             																\n");
	 		sqlstr.append("											WHERE MOD_NO = 0                          																\n");
	 		sqlstr.append("											AND PUR_CONT_ID = (SELECT MIN(PUR_CONT_ID) FROM SUB2000T WHERE SUB_CONT_MOD_ID = '"+purContModId+"')    \n");
	 		sqlstr.append("										)																											\n");
	 		sqlstr.append("				AND WARR_GUBUN = '1'                      																							\n");
	 		sqlstr.append("				AND VIEW_DIV = 'CONT'                     																							\n");
	 		
	 		search.setStatement(sqlstr);
	 		rowSet = search.execute();
	 		if(rowSet != null && rowSet.size() > 0 ) {
	 			rtnStr = rowSet.getRow(0).get("WARRGUBUN");
	 		}
	 	}
	 	catch (Exception e) {
	         super.doException(e);
	         this.storage.setSuccess(false);
	         this.storage.setMessage(e.getMessage());
	         storage.setDetailMessage(e);
	 	}
	 	return rtnStr;
	 }

	/**
	 * 표준 미연동 계약 정보 조회
	 * 
	 * @param
	 * @param
	 * @return RowSet
	 */
	public ValueObject getPricesysInfo(String purContModId) {
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject valueobject = null;
		// String sqlstr = "";
		StrBuffer sqlstr = new StrBuffer();
		
		try {
			sqlstr.append(" SELECT A.DEPT_NAME,          																			\n");
			sqlstr.append(" E.PUR_CONT_MOD_ID AS CONT_MOD_ID,          																\n");
			sqlstr.append(" A.MOD_NO AS MOD_NO,          																			\n");
			sqlstr.append(" A.ORD_DOC_NO AS CONT_NO,          																		\n");
			sqlstr.append(" TO_CHAR(A.ORD_CON_DATE, 'YYYY.MM.DD') AS ORD_CON_DATE,          										\n");
			sqlstr.append(" A.CST_DOC_NAME,                        																	\n");
			sqlstr.append(" B.OBJECT_NM,          																					\n");
			sqlstr.append(" B.ITEM_NM,          																					\n");
			sqlstr.append(" B.SYS_REASON,         																					\n");
			sqlstr.append(" B.REMARK,         																						\n");
			sqlstr.append(" D.USER_NAME AS O_USER_NAME,          																	\n");
			sqlstr.append(" D.POSITION AS O_POSITION,                           													\n");
			sqlstr.append(" DECODE(D.ADDRESS, '', C.ADDRESS, D.ADDRESS) AS O_ADDRESS,         										\n");
			sqlstr.append(" DECODE(D.BOSS_NAME, '', C.REP_NAME, D.BOSS_NAME) AS O_BOSS_NAME,      									\n");
			sqlstr.append(" DECODE(D.MEMBER_NAME, '', C.CORP_KOR, D.MEMBER_NAME)  AS O_MEMBER_NAME,									\n");
			sqlstr.append(" E.USER_NAME AS C_USER_NAME,																				\n");
			sqlstr.append(" E.POSITION AS C_POSITION,																				\n");
			sqlstr.append(" E.ADDRESS AS C_ADDRESS,																					\n");
			sqlstr.append(" E.BOSS_NAME AS C_BOSS_NAME,																				\n");
			sqlstr.append(" E.MEMBER_NAME AS C_MEMBER_NAME,																			\n");
			sqlstr.append(" E.VENDCD AS C_VENDCD,																					\n");
			sqlstr.append(" TO_DATE(F.CONT_APP_DATE-1) AS CONT_APP_DATE,															\n");
			sqlstr.append(" TO_CHAR(SYSDATE, 'YYYY')||'년 '||TO_CHAR(SYSDATE, 'MM')||'월 '||TO_CHAR(SYSDATE, 'DD')||'일' AS SIGN_DATE	\n");
			sqlstr.append(" FROM PUR2000T A, PRICE_SYS B, HEAD_OFFICE_INFO C, CONT_PUR_SUPP D, CONT_PUR_SUPP E, BID_MASTER F		\n");
			sqlstr.append(" WHERE A.PUR_CONT_MOD_ID = B.CONT_MOD_ID (+)																\n");
			sqlstr.append(" AND   A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID(+)															\n");
			sqlstr.append(" AND   D.SIGN_SEQ(+) = 1	  																				\n");
			sqlstr.append(" AND   A.PUR_CONT_MOD_ID = E.PUR_CONT_MOD_ID(+)															\n");
			sqlstr.append(" AND   E.SIGN_SEQ(+) = 2  																				\n");
			sqlstr.append(" AND   A.BID_NO = F.BID_NO																				\n");
			sqlstr.append(" AND   A.BID_DEG = F.BID_DEG																				\n");
			sqlstr.append(" AND   A.PUR_CONT_MOD_ID = '" + purContModId + "'  														\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;
		}
	}
	
}