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


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

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

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.base.WAFMessages;
import kr.co.hsnc.common.config.WAFConfig;
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.WAFConnectionImpl;
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.common.util.Crosscert;
import kr.co.udapsoft.common.util.PDFMaker;
import kr.co.udapsoft.ebid.buyer.bid.outside.biz.InterfaceOutERPiU;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import oracle.toplink.internal.ox.StrBuffer;


   
public class ContIngBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
        //WAFLogger.debug("BidIngListBiz :  ~" + storage.getUser());

		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_PROMISE_SELECT")){
			
			params.set("sheet3", getPromiseList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("FORM_SELECT")){
            user = getContRequestValueObject(params.get("SUB_CONT_MOD_ID"));
            storage.addValueObject("ContInfo", user);
            
			//저장된 HTML 정보
            RowSet template = getTemplateInfo(params.get("SUB_CONT_MOD_ID"));
            storage.addRowSet("template", template);
            
			//자동 저장 파일
			RowSet rowSet = getTemplateFile(params.get("SUB_CONT_MOD_ID"));
			storage.addRowSet("AUTOFILE_LIST", rowSet);
			
			//인지세구분
			RowSet stampRowSet = getStampIssue();
			storage.addRowSet("stampRowSet", stampRowSet);            

			//보증 정보 가져오기
			params.set("MOD_NO", user.get("MOD_NO"));
			
	        String wonCont = "K";
	        
	        if(!params.get("MOD_NO").equals("0")){
	        	wonCont = getWarrGubun(params.get("SUB_CONT_MOD_ID") );
	        }
	        
	        params.set("WON_WARR", wonCont);
			
			RowSet warrList = getWarrList(params);
			storage.addRowSet("warrList", warrList);       
		}else if(storage.compareEvent("PARTNER_EMP_CHK")){
        	ValueObject empChk = getEmpChk(params.get("SUB_CONT_MOD_ID"), params.get("C_HP"));
	        storage.addValueObject("VALUEOBJECT", empChk);
        }else if(storage.compareEvent("FORM_SAVE")){
			//계약 정보 저장
			success = saveContProc(super.conn.getConnection(), params);
			
			super.storage.setSuccess(success);
			
		}else if(storage.compareEvent("STAMP_DUTY_GB_SAVE")){ //인지세 발급구분 업데이트 
	           
				success = updateStampDutyGb(super.conn.getConnection(), params);
				super.storage.setSuccess(success);

		}else if(storage.compareEvent("SIGN_CALL")){

			params.set("pStatusCD", "20");
			params.set("pConClsYn", "1");
			ContStatusUp(super.conn.getConnection(), params);	//서명 요청
			
			Send(params, storage.getEvent());		//서명요청 메일과 SMS 전송

			super.storage.setSuccess(true);
		}else if(storage.compareEvent("SIGN_OK")){
			//ERP 등록 업체 확인
			Integer sCount = getErpSuppCount(params.get("C_CORP_ID"));
			
			String orgCdCoop = getOrgCdCoop(params.get("SUB_CONT_MOD_ID"));
			if( !orgCdCoop.equals("") ) {
				sCount = 1;
			}
			
			if(sCount>0) {
				
				String sMsg="";
				String signedData="";
				Crosscert crossErt = new Crosscert();
				signedData = crossErt.signCrosscert(params.get("CONT_HASH"));
				sMsg = crossErt.chkSignVerify(signedData);
				//WAFLogger.debug("sMsg ======= : " + crossErt.getDn());
			
				if(sMsg.equals("SIGN_SUCCESS")){
					
					params.set("CONT_SIGNDN", crossErt.getDn());
					params.set("CONT_SIGNDATA", signedData);

					success = endContProc(super.conn.getConnection(), params, true);
					
					//WAFLogger.debug("SUCCESS STATUS ======= : " + success);	
					super.storage.setSuccess(success);
				}else{
					super.storage.setSuccess(false);	
					super.storage.setMessage("인증서 검증에 실패 하였습니다.");
				}
				
			}else{
				
				int aCount = 0;
				ValueObject suppVo = getSuppInfo(params.get("SUB_CONT_MOD_ID"));
				aCount = getErpSuppCountAll(suppVo.get("CORP_REG_NO"));
				
				if(aCount == 0) {
					
					/*
					 * 2019.02.15
					 * 이미경차장님요청
					 * 해외업체일경우 ERP 거래처등록 안함 
					 */
					if( suppVo.get("IN_OUT_DIV").equals("O") ){
						throw new Exception("ERP에 등록되지 않은 해외 업체입니다.");
					}
					
					suppVo.set("CD_PARTNER", InterfaceOutERPiU.getCdPartner());
					
					//국외업체일경우 SUPP_INFO.CORP_REG_NO 에 CD_PARTNER 값을 넣어준다.
					if( suppVo.get("IN_OUT_DIV").equals("O") ){
						updateCdPartner(suppVo.get("CORP_ID"), suppVo.get("CD_PARTNER"));
					}
					
					WAFConnectionImpl erpConn = new WAFConnectionImpl();
					try {
						erpConn.setDataSource("jdbc/erpiu");
						erpConn.begin();

						InterfaceOutERPiU.insertErpSupp(erpConn.getConnection(), suppVo);
						
						//외주담당자 ERP거래청 등록요청 메일발송
						sendSuppInfoMail(suppVo.get("TO_MAIL"), suppVo.get("CORP_REG_NO"), suppVo.get("LN_PARTNER"), suppVo.get("CORP_ID"));
						
					}catch(Exception e) {
						super.storage.setSuccess(false);
						super.storage.setMessage(e.getMessage());	
						erpConn.setRollback();
						throw e;
					}finally {
						erpConn.close();
					}
					
				}
				
				super.storage.setSuccess(false);	
				super.storage.setMessage("ERP 업체 등록이 안되어 있습니다.");
				
			}
		}else if(storage.compareEvent("END_CONT")){	
			
			int sCount = 0;
			
			if(params.get("ERP_CHECK_YN").equals("Y")){
				sCount = getErpSuppCount(params.get("C_CORP_ID"));
			}else{
				sCount = 1;
			}
			
			String orgCdCoop = getOrgCdCoop(params.get("SUB_CONT_MOD_ID"));
			if( !orgCdCoop.equals("") ) {
				sCount = 1;
			}
			
			
			if(sCount>0) {			
				success = endContProc(super.conn.getConnection(), params, false);
				//WAFLogger.debug("SUCCESS STATUS ======= : " + success);	
				super.storage.setSuccess(success);
				
			}else{
				
				int aCount = 0;
				ValueObject suppVo = getSuppInfo(params.get("SUB_CONT_MOD_ID"));
				aCount = getErpSuppCountAll(suppVo.get("CORP_REG_NO"));
				
				if(aCount == 0) {
					
					/*
					 * 2019.02.15
					 * 이미경차장님요청
					 * 해외업체일경우 ERP 거래처등록 안함 
					 */
					if( suppVo.get("IN_OUT_DIV").equals("O") ){
						super.storage.setSuccess(false);	
						super.storage.setMessage("ERP 업체 등록이 안되어 있습니다.");
						throw new Exception("ERP에 등록되지 않은 해외 업체입니다.");
					}
					
					suppVo.set("CD_PARTNER", InterfaceOutERPiU.getCdPartner());
					
					//국외업체일경우 SUPP_INFO.CORP_REG_NO 에 CD_PARTNER 값을 넣어준다.
					if( suppVo.get("IN_OUT_DIV").equals("O") ){
						updateCdPartner(suppVo.get("CORP_ID"), suppVo.get("CD_PARTNER"));
					}
					
					WAFConnectionImpl erpConn = new WAFConnectionImpl();
					try {
						erpConn.setDataSource("jdbc/erpiu");
						erpConn.begin();
						
						InterfaceOutERPiU.insertErpSupp(erpConn.getConnection(), suppVo);
						
						//외주담당자 ERP거래처 등록요청 메일발송
						sendSuppInfoMail(suppVo.get("TO_MAIL"), suppVo.get("CORP_REG_NO"), suppVo.get("LN_PARTNER"), suppVo.get("CORP_ID"));
						
					}catch(Exception e) {
						super.storage.setSuccess(false);
						super.storage.setMessage(e.getMessage());	
						erpConn.setRollback();
						throw e;
					}finally {
						erpConn.close();
					}
					
				}
				
				super.storage.setSuccess(false);	
				super.storage.setMessage("ERP 업체 등록이 안되어 있습니다.");
			}				
		}else if(storage.compareEvent("FORM_DEL")){
			//계약 정보 삭제
			success = deleteContProc(super.conn.getConnection(), params);
			
			super.storage.setSuccess(success);
			
		}else if(storage.compareEvent("WRITE_END")){		//작성 완료 상태로 놓음
			//2018.11.2 계약내역과, 갑지 금액비교. 일치하지 않으면 반려.
			if (!AmtCompare(params.get("SUB_CONT_MOD_ID"))) {
				super.storage.setSuccess(false);
				super.storage.setMessage("계약금액과 계약내역의 과세,면세 금액이 일치하지 않습니다.");
				return;
			}
			
			params.set("pStatusCD", "16");
		//	params.set("pConClsYn", "1");
			
			ContStatusUp(super.conn.getConnection(), params);	//서명 요청
			super.storage.setSuccess(true);
			
		}else if(storage.compareEvent("CONT_REJECT")){		//반려 상태로 놓음
			params.set("pStatusCD", "41");
		//	params.set("pConClsYn", "1");
			ContStatusUp(super.conn.getConnection(), params);	//서명 요청
			super.storage.setSuccess(true);
			
		}/*else if(storage.compareEvent("SAVE_STAMP")){
			//인지세 저장
			success = saveStampDuty(super.conn.getConnection(), params);
			
			super.storage.setSuccess(success);
			
		}*/else if(storage.compareEvent("FORM_MOVE")){
			success = updateMoveStatus(super.conn.getConnection(), params);
			
			super.storage.setSuccess(success);
		}else if(storage.compareEvent("CONT_PAY_GUAR")){
			success = updateGuarStatus(super.conn.getConnection(), params);
			
			super.storage.setSuccess(success);
		}	
	}

	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;
	}
	
	private String getfileHashlist(String filegb,String id){
		String fileHash="";
		
		if( ("localhost").equals(storage.getRequest().getServerName()) ) {
			return fileHash;
		}
		
		RowSet rowSet = FileManager.getFileList(filegb, id);
		String str="";
		
		if (rowSet.size()>0){
			PDFMaker pdfMaker = new PDFMaker();

			for(int i=0;i<rowSet.size();i++){
	        	Row row=rowSet.getRow(i);

	        	str = pdfMaker.getHash(WAFConfig.get("waf.repository.path") + row.get("FILE_PATH") +"/"+ row.get("REAL_FILE_NAME"));
	        	fileHash = fileHash + str +"|" ;
	        }
		}
		return fileHash;
	}
	
	
	
	
	 /**
     * 저장된 계약서식 조회 
	 * @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_OUT_HTML      					\n"
                    + " WHERE SUB_CONT_MOD_ID =  '"+sub_cont_cd+"'		\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_OUT_FILE      					\n"
                    + " WHERE SUB_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 
	 * @param 
	 * @return RowSet 
	 */
	public RowSet getStampIssue(){
		
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		String sqlstr = "";
		
		try {
			sqlstr += " SELECT	DETAILCD, DETAILNM				\n"
					+ "	FROM	EBID_COM911T					\n"
					+ "	WHERE	BASECD = 'STAMP_DUTY_ISSUE_GB'	\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();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" 	SELECT A.SUB_CONT_MOD_ID	\n");
        	sqlstr.append(" 	     ,A.CON_DOC_NO	\n");
        	sqlstr.append(" 	     ,A.MOD_NO	\n");
        	sqlstr.append(" 	     ,A.DEPT_NAME	\n");
        	sqlstr.append(" 	     ,A.BUILD_NAME	\n");
        	sqlstr.append(" 	     ,TO_CHAR(A.CON_DATE, 'YYYY-MM-DD') AS CON_DATE	\n");
        	sqlstr.append(" 	     ,F.MEMBER_NAME	\n");
        	sqlstr.append(" 	     ,A.CON_AMT	\n");
        	sqlstr.append(" 	     ,F_GET_D_CURRENCY(A.CON_SUPPLY_AMT, F_GET_CURRENCY_DIGIT(A.CON_CUR_CD)) AS CON_SUPPLY_AMT	\n");
//        	sqlstr.append(" 	     ,TO_CHAR(A.START_WORK_DATE, 'YYYY-MM-DD') || '~' || TO_CHAR(A.END_WORK_DATE, 'YYYY-MM-DD') AS WORK_START_END_DATE	\n");
        	sqlstr.append(" 	     , '~' || TO_CHAR(A.END_WORK_DATE, 'YYYY-MM-DD') AS WORK_START_END_DATE	\n");
        	sqlstr.append(" 	     ,A.START_WORK_DATE	\n");
        	sqlstr.append(" 	     ,A.END_WORK_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.CON_CUR_CD 	\n");
        	sqlstr.append(" 	     ,NVL(A.ELEC_CON_YN, 'N') 	 AS ELEC_CON_YN  \n");
        	sqlstr.append(" 	  FROM SUB2000T A, CONT_OUT_HTML B, CONT_TEMPLATE C, CONT_OUT_SUPP F	\n");
        	sqlstr.append("   WHERE 1=1					\n");
        	sqlstr.append("   	AND A.SUB_CONT_MOD_ID = B.SUB_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.SUB_CONT_MOD_ID = F.SUB_CONT_MOD_ID				\n");
    		sqlstr.append("   	AND F.SIGN_SEQ = 2				\n");
        	// 현장
        	if(!param.get("P_DEPT_CD").isEmpty()) {
        		sqlstr.append(" AND A.DEPT_CD = ? 																												\n");
    			search.addParameter(params.get("P_DEPT_CD"));
        	}
//        		sqlstr.append("   AND A.DEPT_CD = '"+param.get("P_DEPT_CD")+"'				\n");
        	if(!param.get("P_BUILD_NM").isEmpty())
        		sqlstr.append("   AND UPPER(A.BUILD_NAME) LIKE '%"+param.get("P_BUILD_NM").toUpperCase()+"%'				\n");
        	if(!param.get("ContFromDt").isEmpty())
        		sqlstr.append("   AND TO_CHAR(A.CON_DATE, 'YYYY-MM-DD') >= '"+param.get("ContFromDt")+"'				\n");
        	if(!param.get("ContToDt").isEmpty())
        		sqlstr.append("   AND TO_CHAR(A.CON_DATE, 'YYYY-MM-DD') <= '"+param.get("ContToDt")+"'				\n");
        	
        	if(!param.get("p_dealer_nm").isEmpty())
        		sqlstr.append("   AND UPPER(F.MEMBER_NAME) LIKE '%'|| '"+param.get("p_dealer_nm").toUpperCase()+"' ||'%'	\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.CON_DATE 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.debug("### WAFSQLException : " + se.getMessage());
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        return rowSet;
	}
	

	
	/**
	 * 계약 상세 정보
	 * @param Params
	 * @return
	 */
	private ValueObject getContRequestValueObject(String SUB_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.SUB_CONT_MOD_ID	\n");
	        	sqlstr.append(" 	     , A.CON_DOC_NO	\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.BUILD_NAME	\n");	
	        	sqlstr.append(" 	     , A.CON_DATE	\n");	
	        	sqlstr.append(" 	     , A.DEALER_NM	\n");	
	        	sqlstr.append(" 	     , A.CON_AMT	\n");	
	        	sqlstr.append(" 	     , A.START_WORK_DATE	\n");	
	        	sqlstr.append(" 	     , A.ELEC_CON_YN	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE,'YYYY') AS START_WORK_DATE_YEAR 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE,'MM') AS START_WORK_DATE_MONTH 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE,'DD') AS START_WORK_DATE_DAY 	\n");	
	        	sqlstr.append(" 	     , A.END_WORK_DATE	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE,'YYYY') AS END_WORK_DATE_YEAR 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE,'MM') AS END_WORK_DATE_MONTH 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE,'DD') AS END_WORK_DATE_DAY 	\n");	
	        	sqlstr.append(" 	     , A.CON_GB 	\n");	
	        	sqlstr.append(" 	     , A.CON_CUR_CD 	\n");														//추가 (2014-09-19)
	        	sqlstr.append(" 	     , (SELECT CHG_CODE01 FROM EBID_COM911T WHERE BASECD='911' AND DETAILCD=A.CON_CUR_CD) AS SOSUNUM 	\n");
	        	sqlstr.append(" 	     , A.UND_CON_NAME 	\n");	
	        	sqlstr.append(" 	     , A.SUB_UND_CON_NAME 	\n");	
	        	sqlstr.append(" 	     , A.WORK_PLACE 	\n");	
	        	sqlstr.append(" 	     , A.CON_SUPPLY_AMT 	\n");	
	        	sqlstr.append(" 	     , A.LABOR_AMT 	\n");	     	
	        	sqlstr.append(" 	     , A.CON_SUPPLY_TAMT 	\n");	
	        	sqlstr.append(" 	     , A.CON_SUPPLY_NAMT 	\n");	
	        	sqlstr.append(" 	     , A.CON_SURTAX_AMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_AMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_RATE 	\n");	
	        	sqlstr.append(" 	     , A.PAY_WAY_BILL 	\n");	
	        	sqlstr.append(" 	     , A.PAY_WAY_CASH 	\n");	

	        	sqlstr.append(" 	     , A.PREPAY_SUPPLY_AMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_SUPPLY_TAMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_SUPPLY_NAMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_SURTAX_AMT 	\n");	
	        	sqlstr.append(" 	     , A.CON_EST_GUAR_AMT 	\n");	
	        	sqlstr.append(" 	     , A.CON_EST_GUAR_RATE 	\n");	
	        	sqlstr.append(" 	     , A.DEF_EST_GUAR_RATE 	\n");	
	        	sqlstr.append(" 	     , A.DELAY_RATE 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.CON_DATE,'YYYY') AS CON_DATE_YEAR 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.CON_DATE,'MM') AS CON_DATE_MONTH 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.CON_DATE,'DD') AS CON_DATE_DAY 	\n");	
	        	sqlstr.append(" 	     , A.STATUS_CD 	\n");	
	        	sqlstr.append(" 	     , F_GET_CODE_NAME('CONT_STATUS_CD',A.STATUS_CD) AS STATUS_NM 	\n");	
	        	sqlstr.append(" 	     , A.MONTH_ACMP_CNT 	\n");	
	        	sqlstr.append(" 	     , A.OBJ_RCPT_DAY 	\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(" 	     , B.DEMANDED_NO	\n");	
	        	sqlstr.append(" 	     , B.DEMANDED_TITLE	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(TO_DATE(B.DEMANDED_DATE),'YYYY-MM-DD') AS DEMANDED_DATE	\n");	
	        	sqlstr.append(" 	     , B.REQ_DEPT_NM	\n");	
	        	sqlstr.append(" 	     , B.EMP_NO	, G.E_MAIL EMP_MAIL , H.E_MAIL CHG_MAIL  \n");	
	        	sqlstr.append(" 	     , B.EMP_NM	\n");	
	        	sqlstr.append("       	 , GET_OUT_ITEM_NM(B.OUT_ITEM_CD) AS OUT_ITEM_NM						\n");			//2014*-10-02 공종 추가
	        	/*분개 유형*/
	        	sqlstr.append(" 	     , A.ACCT_GB 	\n");	
	        	sqlstr.append(" 	     , A.PAY_GROUP 	\n");	
	        	sqlstr.append(" 	     , A.PROOF_CD 	\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(" 	     , NVL(A.SUPP_DIRECT_YN,'N') AS SUPP_DIRECT_YN       	\n");	//2021.04.01 최훈석(하도급직불여부 추가)
	        	sqlstr.append(" 	     , NVL(A.LABOR_USE_YN,'N') AS LABOR_USE_YN         	\n");	//2022.03.21 최훈석(노무비닷컴 사용여부 추가)
	        	
	        	sqlstr.append(" 	     , A.STAMP_DUTY_GB	\n");
	        	
	        	//승인자 추가
	        	sqlstr.append(" 	     , DECODE(A.CON_CONFIRM_ID  , '', (SELECT ACCT_ID   FROM PUB0120T WHERE ACCT_ID IN(SELECT DETAILCD FROM EBID_COM911T WHERE BASECD = 'REPORTER_LIST' AND CHG_CODE02 = 'OUT' AND DETAILSORTNM = 1)), A.CON_CONFIRM_ID)   AS CON_CONFIRM_ID	\n");	//2024.01.02 외주팀장ID 고정
	        	sqlstr.append(" 	     , DECODE(A.CON_CONFIRM_NAME, '', (SELECT USER_NAME FROM PUB0120T WHERE ACCT_ID IN(SELECT DETAILCD FROM EBID_COM911T WHERE BASECD = 'REPORTER_LIST' AND CHG_CODE02 = 'OUT' AND DETAILSORTNM = 1)), A.CON_CONFIRM_NAME) AS CON_CONFIRM_NAME	\n");	//2024.01.02 외주팀장명 고정
	        	//sqlstr.append(" 	     , A.CON_CONFIRM_ID 	\n");	
	        	//sqlstr.append(" 	     , A.CON_CONFIRM_NAME 	\n");	
	        	
	        	sqlstr.append(" 	     , A.CON_CHARGE_ID 	\n");	
	        	sqlstr.append(" 	     , A.CON_CHARGE_NAME 	\n");	

	        	
	        	sqlstr.append(" 	     , A.IN_SPE_INFO 	\n");				//내부특기사항 : 2014-11-26 천호준 추가
	        	sqlstr.append(" 	     , A.IN_CHG_INFO 	\n");				//내부변경사유 : 2014-11-26 천호준 추가
	        	sqlstr.append(" 	     , A.FRST_CON_AMT	\n");				//원_도급금액 : 2014-11-26 천호준 추가
	        	sqlstr.append(" 	     , A.FRST_EXE_AMT	\n");				//원_실행금액 : 2014-11-26 천호준 추가
	        	sqlstr.append(" 	     , A.CHG_CON_AMT	\n");				//현_도급금액 : 2014-11-26 천호준 추가
	        	sqlstr.append(" 	     , A.CHG_EXE_AMT	\n");				//현_실행금액 : 2014-11-26 천호준 추가
	        	
	        	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(" 	     , DECODE(D.VENDCD, '', C.CORP_REG_NO, D.VENDCD) AS O_REG_NO	\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");			//TO_DATE(E.SIGN_DATE, 'YYYYMMDDHH24MISS')
	        	
	        	sqlstr.append(" 	     , A.ERP_CHECK_YN AS ERP_CHECK_YN	\n");	
	        	
	        	sqlstr.append(" 	     , (SELECT IN_OUT_DIV FROM SUPP_INFO WHERE CORP_ID = E.CORP_ID) AS IN_OUT_DIV	\n");	
	        	sqlstr.append("          ,F.MIN_CONT_SUPPLY_TAMT                                                        \n");	
	        	sqlstr.append("          ,F.MIN_CONT_SUPPLY_NAMT                                                        \n");
	        	sqlstr.append("          ,NVL (I.GUAR_STATUS, '00') AS WARR_STATUS                                      \n");	//하도급대금지급보증서 발급상태
				sqlstr.append("  FROM SUB2000T A, BID_MASTER B, HEAD_OFFICE_INFO C, CONT_OUT_SUPP D, CONT_OUT_SUPP E, PUB0120T G, CONT_OUT_WARR I	\n");
				sqlstr.append("   , ( 	\n");
				sqlstr.append("   		SELECT A.SUB_CONT_ID 	\n");
				//sqlstr.append("  		 	, NVL(REQU_SUPPLY_TAMT,0)  + (NVL(REQ_PREPAY_SUPPLY_TAMT,0) - NVL(B.PAY_DEDUCT_TAMT,0)) AS MIN_CONT_SUPPLY_TAMT	\n");2016.04.29 변경계약 최소금액 수정
				//sqlstr.append("    		, NVL(REQU_SUPPLY_NAMT,0)  + (NVL(REQ_PREPAY_SUPPLY_NAMT,0) - NVL(B.PAY_DEDUCT_NAMT,0)) AS MIN_CONT_SUPPLY_NAMT	\n");
				sqlstr.append("  		 	, NVL(REQU_SUPPLY_TAMT,0)  + NVL(REQ_PREPAY_SUPPLY_TAMT,0) AS MIN_CONT_SUPPLY_TAMT	\n");
				sqlstr.append("    			, NVL(REQU_SUPPLY_NAMT,0)  + NVL(REQ_PREPAY_SUPPLY_NAMT,0) AS MIN_CONT_SUPPLY_NAMT 	\n");
				sqlstr.append("   		FROM  	\n");
				sqlstr.append("   			( 	\n");
				sqlstr.append("    				SELECT SUB_CONT_ID  	\n");
				sqlstr.append("     				, SUM(REQ_PREPAY_SUPPLY_TAMT) AS REQ_PREPAY_SUPPLY_TAMT	\n");
				sqlstr.append("    					, SUM(REQ_PREPAY_SUPPLY_NAMT) AS REQ_PREPAY_SUPPLY_NAMT 	\n");
				sqlstr.append("   	 			FROM SUB1030T	\n");
				sqlstr.append(" 				GROUP BY SUB_CONT_ID	\n");
				sqlstr.append("    			) A , 	\n");
				sqlstr.append("     		(	\n");
				sqlstr.append("     			SELECT SUB_CONT_ID	\n");
				sqlstr.append("       				, SUM(REQU_SUPPLY_TAMT) AS REQU_SUPPLY_TAMT 	\n");
				sqlstr.append("     				, SUM(REQU_SUPPLY_NAMT) AS REQU_SUPPLY_NAMT 	\n");
				sqlstr.append("       				, SUM(PAY_DEDUCT_TAMT) AS PAY_DEDUCT_TAMT	\n");
				sqlstr.append("          			, SUM(PAY_DEDUCT_NAMT) AS PAY_DEDUCT_NAMT 	\n");
				sqlstr.append("     			FROM SUB1040T	\n");
				sqlstr.append("    				GROUP BY SUB_CONT_ID 	\n");
				sqlstr.append("      		)  B	\n");
				sqlstr.append("      	WHERE A.SUB_CONT_ID = B.SUB_CONT_ID 	\n");
				sqlstr.append("   	) F    	\n");
				sqlstr.append("	,( SELECT A.CRE_BY, D.E_MAIL, C.SUB_CONT_MOD_ID  \n");
				sqlstr.append("		 FROM SUB3000T A,                            \n");
				sqlstr.append("		      SUB2000T B,                            \n");
				sqlstr.append("		      SUB2000T C,                            \n");
				sqlstr.append("		      PUB0120T D                             \n");
				sqlstr.append("		 WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID \n");
				sqlstr.append("		 AND B.SUB_CONT_ID = C.SUB_CONT_ID           \n");
				sqlstr.append("		 AND B.MOD_NO = C.MOD_NO -1                  \n");
				sqlstr.append("		 AND A.CRE_BY = D.ACCT_ID                    \n");
				sqlstr.append("		 AND C.SUB_CONT_MOD_ID = "+SUB_CONT_MOD_ID+" ) H  \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.SUB_CONT_MOD_ID = D.SUB_CONT_MOD_ID(+)	 \n");
				sqlstr.append("  AND D.SIGN_SEQ(+) = 1 --원사업자	                 \n");
				sqlstr.append("  AND A.SUB_CONT_MOD_ID = E.SUB_CONT_MOD_ID(+)	 \n");
				sqlstr.append("  AND E.SIGN_SEQ(+) = 2 --계약업체정보  	             \n");
				sqlstr.append("  AND A.SUB_CONT_ID = F.SUB_CONT_ID(+)        	 \n");
				sqlstr.append("  AND B.EMP_NO = G.ACCT_ID(+) 	                 \n");
				sqlstr.append("  AND A.SUB_CONT_MOD_ID = H.SUB_CONT_MOD_ID(+)    \n");
				sqlstr.append("  AND A.SUB_CONT_MOD_ID = I.SUB_CONT_MOD_ID(+)    \n");
				sqlstr.append("  AND I.WARR_GUBUN(+) = 2 --하도급대급지급보증서           \n");
				sqlstr.append("  AND A.SUB_CONT_MOD_ID = '"+SUB_CONT_MOD_ID+"'	 \n");

	            search.setStatement(sqlstr.toString());
	            rowSet = search.execute();
                System.out.println(sqlstr.toString());
            
            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);
            //WAFLogger.debug(sqlstr.toString());
            //WAFLogger.debug("WAFSQLException : " + se.getMessage());
            System.out.println(sqlstr.toString());
            System.out.println(se.getMessage());
        }
        finally {

        	if( valueobject == null ) valueobject = new ValueObject();
        	//WAFLogger.debug("### valueobject size : " + valueobject.size());
            return valueobject;
        }
	}

	/**
     * 계약 정보 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean saveContProc(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);

        try {
        	conn.begin();
			ArrayList pdfIfno = new ArrayList();
			if( !("localhost").equals(storage.getRequest().getServerName()) ) {
				pdfIfno = makeContPdf(params);		// pdf파일 생성
				if (pdfIfno.isEmpty()){
					storage.setMessage("PDF 생성에 실패 하였습니다.");    
					storage.setDetailMessage("PDF 생성에 실패 하였습니다.");    
					return false;
				}			
			}
			margeOrigCorpData(super.conn.getConnection(), params);		//원사업자 업체정보 저장
			margeContCorpData(super.conn.getConnection(), params);		//계약업체정보 저장

			CONT_OUT_HTML_del(conn.getConnection(), params);	//ContHTML 삭제
			CONT_OUT_HTML_Save(super.conn.getConnection(), params);	//ContHTML 등록
			
			String file_hash="";
			String CONT_HASH="";
			if (!((params.get("TEMPLATE_CD").equals("7")) || (params.get("TEMPLATE_CD").equals("8")))) {
				CONT_OUT_FILE_del(conn.getConnection(), params);	//자동생성파일 삭제
				for(int i=0; i<pdfIfno.size(); i++){
					file_hash = CONT_OUT_FILE_Save(super.conn.getConnection(), (ValueObject) pdfIfno.get(i));	//자동생성파일 등록
					CONT_HASH = CONT_HASH + file_hash +"|" ;
				}			
			}else{	//자유서식인 경우 '비밀준수계약서'만 자동생성
				CONT_OUT_FILE_del(conn.getConnection(), params);	//자동생성파일 삭제
				for(int i=1; i<pdfIfno.size(); i++){
					file_hash = CONT_OUT_FILE_Save(super.conn.getConnection(), (ValueObject) pdfIfno.get(i));	//자동생성파일 등록
					CONT_HASH = CONT_HASH + file_hash +"|" ;
				}	
			}
			//첨부파일 hash값 가져오기
			String addFileHash = getfileHashlist("SUB_CONT_MOD_ID", params.get("SUB_CONT_MOD_ID"));
			CONT_HASH = CONT_HASH + addFileHash;
			
			if (CONT_HASH.length()>0){
				params.set("CONT_HASH", CONT_HASH.subSequence(0, CONT_HASH.length()-1));
			}else{
				params.set("CONT_HASH", CONT_HASH.subSequence(0, CONT_HASH.length()));				
			}
			saveContInfo(super.conn.getConnection(), params);		//계약 정보 저장
			
			//2015.01.08 이인오 인지세 추가
			/*if( !params.get("stamp_duty_issue_gb").equals("00") ){
				mergeStampDuty(super.conn.getConnection(), params);
				
			}else {
				deleteStampDuty(super.conn.getConnection(), params);
				
			}*/


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

            conn.setRollback();
            
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }
        finally {
        	super.conn.end();
        }        
    }		

	/**
     * 원사업자 업체 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean margeOrigCorpData(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("MERGE INTO CONT_OUT_SUPP T1 USING (									\n");
        	sqlstr.append("          SELECT ? AS	SUB_CONT_MOD_ID	\n");
        	sqlstr.append("                ,? AS	CORP_ID	\n");
        	sqlstr.append("                ,'' AS	VENDCD	\n");
        	sqlstr.append("                ,? AS	MEMBER_NAME	\n");
        	sqlstr.append("                ,? AS	BOSS_NAME	\n");
        	sqlstr.append("                ,'' AS	POST_CODE	\n");
        	sqlstr.append("                ,? AS	ADDRESS	\n");
        	sqlstr.append("                ,? AS	TEL_NUM	\n");
        	sqlstr.append("                ,'' AS	MEMBER_SLNO	\n");
        	sqlstr.append("                ,? AS	USER_NAME	\n");
        	sqlstr.append("                ,? AS	DIVISION	\n");
        	sqlstr.append("                ,? AS	POSITION	\n");
        	sqlstr.append("                ,? AS	HP	\n");
        	sqlstr.append("                ,? AS	EMAIL	\n");
        	sqlstr.append("		        FROM DUAL													\n");
        	sqlstr.append("		    ) T ON (T.SUB_CONT_MOD_ID = T1.SUB_CONT_MOD_ID AND T.CORP_ID = T1.CORP_ID)	\n");
        	sqlstr.append("		WHEN MATCHED THEN													\n");
        	sqlstr.append("		    UPDATE SET 				\n");
//        	sqlstr.append("  				, T1.VENDCD	 =	, T.VENDCD	\n");
        	sqlstr.append("  				 T1.MEMBER_NAME	 =	 T.MEMBER_NAME	\n");
        	sqlstr.append("  				, T1.BOSS_NAME	 =	 T.BOSS_NAME	\n");
//        	sqlstr.append("  				, T1.POST_CODE	 =	 T.POST_CODE	\n");
        	sqlstr.append("  				, T1.ADDRESS	 =	 T.ADDRESS	\n");
        	sqlstr.append("  				, T1.TEL_NUM	 =	 T.TEL_NUM	\n");
//        	sqlstr.append("  				, T1.MEMBER_SLNO	 =	 T.MEMBER_SLNO	\n");
        	sqlstr.append("  				, T1.USER_NAME	 =	 T.USER_NAME	\n");
        	sqlstr.append("  				, T1.DIVISION	 =	 T.DIVISION	\n");
        	sqlstr.append("  				, T1.POSITION	 =	 T.POSITION	\n");
        	sqlstr.append("  				, T1.HP	 =	 T.HP	\n");
        	sqlstr.append("  				, T1.EMAIL	 =	 T.EMAIL	\n");
        	sqlstr.append("		WHEN NOT MATCHED THEN											\n");
        	sqlstr.append("			INSERT (T1.SUB_CONT_MOD_ID, T1.CORP_ID, T1.SIGN_SEQ, T1.VENDCD, T1.MEMBER_NAME, T1.BOSS_NAME, T1.POST_CODE, T1.ADDRESS, T1.TEL_NUM, T1.MEMBER_SLNO, T1.USER_NAME, T1.DIVISION, T1.POSITION, T1.HP, T1.EMAIL)									\n");
        	sqlstr.append("			VALUES (T.SUB_CONT_MOD_ID, T.CORP_ID, 1, T.VENDCD, T.MEMBER_NAME, T.BOSS_NAME, T.POST_CODE, T.ADDRESS, T.TEL_NUM, T.MEMBER_SLNO, T.USER_NAME, T.DIVISION, T.POSITION, T.HP, T.EMAIL)											\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
            persistent.addParameter(valueObject.get("O_CORP_ID"));
            persistent.addParameter(valueObject.get("O_MEMBER_NAME"));
            persistent.addParameter(valueObject.get("O_BOSS_NAME"));
            persistent.addParameter(valueObject.get("O_ADDRESS"));
            persistent.addParameter(valueObject.get("O_TEL_NUM"));
            persistent.addParameter(valueObject.get("O_USER_NAME"));
            persistent.addParameter(valueObject.get("O_DIVISION"));
            persistent.addParameter(valueObject.get("O_POSITION"));
            persistent.addParameter(valueObject.get("O_HP"));
            persistent.addParameter(valueObject.get("O_EMAIL"));
            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }	
	
	/**
     * 협력사 담당자 정보 조회
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	private ValueObject getEmpChk(String sub_cont_mod_id, String c_hp) {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject valueobject = null;
		ValueObject user = storage.getUser();
		StrBuffer sqlstr = new StrBuffer();
		try{
			
			sqlstr.append(" select NVL(COUNT(*),0) EMP_CHK_CNT                  \n");
			sqlstr.append("   from CONT_OUT_SUPP A, SUPP_CHARGE B               \n");
			sqlstr.append("  where A.sub_cont_mod_id = '"+sub_cont_mod_id+"'    \n");
			sqlstr.append("    and A.sign_seq = '2'                             \n");
			sqlstr.append("    AND A.CORP_ID = B.CORP_ID                        \n");
			sqlstr.append("    AND b.hp1||b.hp2||b.hp3 = replace('"+c_hp+"','-','')      \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);
			//WAFLogger.debug(sqlstr.toString());
			//WAFLogger.debug("WAFSQLException : " + se.getMessage());
		}
		finally {
			
			if( valueobject == null ) valueobject = new ValueObject();
			//WAFLogger.debug("### valueobject size : " + valueobject.size());
			return valueobject;
		}
	}
	/**
     * 원사업자 업체 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean margeContCorpData(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("MERGE INTO CONT_OUT_SUPP T1 USING (									\n");
        	sqlstr.append("          SELECT ? AS	SUB_CONT_MOD_ID	\n");
        	sqlstr.append("                ,? AS	CORP_ID	\n");
        	sqlstr.append("                ,'' AS	VENDCD	\n");
        	sqlstr.append("                ,? AS	MEMBER_NAME	\n");
        	sqlstr.append("                ,? AS	BOSS_NAME	\n");
        	sqlstr.append("                ,'' AS	POST_CODE	\n");
        	sqlstr.append("                ,? AS	ADDRESS	\n");
        	sqlstr.append("                ,? AS	TEL_NUM	\n");
        	sqlstr.append("                ,'' AS	MEMBER_SLNO	\n");
        	sqlstr.append("                ,? AS	USER_NAME	\n");
        	sqlstr.append("                ,? AS	DIVISION	\n");
        	sqlstr.append("                ,? AS	POSITION	\n");
        	sqlstr.append("                ,? AS	HP	\n");
        	sqlstr.append("                ,? AS	EMAIL	\n");
        	sqlstr.append("		        FROM DUAL													\n");
        	sqlstr.append("		    ) T ON (T.SUB_CONT_MOD_ID = T1.SUB_CONT_MOD_ID AND T.CORP_ID = T1.CORP_ID)	\n");
        	sqlstr.append("		WHEN MATCHED THEN													\n");
        	sqlstr.append("		    UPDATE SET 				\n");
//        	sqlstr.append("  				, T1.VENDCD	 =	, T.VENDCD	\n");
        	sqlstr.append("  				 T1.MEMBER_NAME	 =	 T.MEMBER_NAME	\n");
        	sqlstr.append("  				, T1.BOSS_NAME	 =	 T.BOSS_NAME	\n");
//        	sqlstr.append("  				, T1.POST_CODE	 =	 T.POST_CODE	\n");
        	sqlstr.append("  				, T1.ADDRESS	 =	 T.ADDRESS	\n");
        	sqlstr.append("  				, T1.TEL_NUM	 =	 T.TEL_NUM	\n");
//        	sqlstr.append("  				, T1.MEMBER_SLNO	 =	 T.MEMBER_SLNO	\n");
        	sqlstr.append("  				, T1.USER_NAME	 =	 T.USER_NAME	\n");
        	sqlstr.append("  				, T1.DIVISION	 =	 T.DIVISION	\n");
        	sqlstr.append("  				, T1.POSITION	 =	 T.POSITION	\n");
        	sqlstr.append("  				, T1.HP	 =	 T.HP	\n");
        	sqlstr.append("  				, T1.EMAIL	 =	 T.EMAIL	\n");
        	sqlstr.append("		WHEN NOT MATCHED THEN											\n");
        	sqlstr.append("			INSERT (T1.SUB_CONT_MOD_ID, T1.CORP_ID, T1.SIGN_SEQ, T1.VENDCD, T1.MEMBER_NAME, T1.BOSS_NAME, T1.POST_CODE, T1.ADDRESS, T1.TEL_NUM, T1.MEMBER_SLNO, T1.USER_NAME, T1.DIVISION, T1.POSITION, T1.HP, T1.EMAIL)									\n");
        	sqlstr.append("			VALUES (T.SUB_CONT_MOD_ID, T.CORP_ID, 1, T.VENDCD, T.MEMBER_NAME, T.BOSS_NAME, T.POST_CODE, T.ADDRESS, T.TEL_NUM, T.MEMBER_SLNO, T.USER_NAME, T.DIVISION, T.POSITION, T.HP, T.EMAIL)											\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
            persistent.addParameter(valueObject.get("C_CORP_ID"));
            persistent.addParameter(valueObject.get("C_MEMBER_NAME"));
            persistent.addParameter(valueObject.get("C_BOSS_NAME"));
            persistent.addParameter(valueObject.get("C_ADDRESS"));
            persistent.addParameter(valueObject.get("C_TEL_NUM"));
            persistent.addParameter(valueObject.get("C_USER_NAME"));
            persistent.addParameter(valueObject.get("C_DIVISION"));
            persistent.addParameter(valueObject.get("C_POSITION"));
            persistent.addParameter(valueObject.get("C_HP"));
            persistent.addParameter(valueObject.get("C_EMAIL"));
            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }	

	/**
     * 계약정보 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean saveContInfo(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {

        	
        	
        	sqlstr.append("		UPDATE SUB2000T SET 				\n");
        //	sqlstr.append("       CON_DOC_NO  =  '"+ valueObject.get("con_doc_no") +"' \n");		// 계약번호
        //   	sqlstr.append("      , DEPT_NAME  =  '"+ valueObject.get("dept_name") +"' \n");		//현장명
           	sqlstr.append("       DEALER_NM  =  ? \n");		//발주처
        	sqlstr.append("      , CON_DATE  =  ? \n");		//계약일
        	sqlstr.append("      , START_WORK_DATE  =  ? \n");		//공사기간 시작일
        	sqlstr.append("      , END_WORK_DATE  =  ? \n");		//공사기간 종료일
        	sqlstr.append("      , BUILD_NAME  =  ? \n");		//계약명
        	sqlstr.append("      , MOD_GB  =  ? \n");		//계약명
        	sqlstr.append("      , UND_CON_NAME  =  ? \n");		//원도급공사명
        	sqlstr.append("      , SUB_UND_CON_NAME  =  ? \n");		//하도급공사명
        	sqlstr.append("      , WORK_PLACE  =  ? \n");		//공사장소
        	sqlstr.append("      , PAY_WAY_CASH  =  ? \n");		//지급방법 현금
        	sqlstr.append("      , PAY_WAY_BILL  =  ? \n");		// 지금방법 어음
        	sqlstr.append("      , CON_AMT  =  ? \n");		// 계약금액
        	sqlstr.append("      , CON_SUPPLY_AMT  =  ? \n");		// 공급가액
        	sqlstr.append("      , CON_SUPPLY_TAMT  =  ? \n");		// 공급가액 과세
        	sqlstr.append("      , CON_SUPPLY_NAMT  =  ? \n");		// 공급가액 비과세
        	sqlstr.append("      , CON_SURTAX_AMT  =  ? \n");		// 계약부과세
        	sqlstr.append("      , LABOR_AMT  =  ? \n");		// 노무비
        	sqlstr.append("      , PREPAY_RATE  =  ? \n");		// 선급금율
        	sqlstr.append("      , PREPAY_AMT  =  ? \n");		// 선급금액
        	sqlstr.append("      , PREPAY_GUAR_AMT_RATE  =  ? \n");		// 선급금율
        	sqlstr.append("      , PREPAY_GUAR_AMT  =  ? \n");		// 선급금액        	
        	sqlstr.append("      , PREPAY_SUPPLY_AMT  =  ? \n");		// 선급금 공급가액
        	sqlstr.append("      , PREPAY_SUPPLY_TAMT  =  ? \n");		// 선급금 과세
        	sqlstr.append("      , PREPAY_SUPPLY_NAMT  =  ? \n");		// 선급금 면세
        	sqlstr.append("      , PREPAY_SURTAX_AMT  =  ? \n");		// 선급금 부과세
        	sqlstr.append("      , CON_EST_GUAR_RATE  =  ? \n");		// 계약이행 보증금율
        	sqlstr.append("      , CON_EST_GUAR_AMT  =  ? \n");		// 계약이행 보증금
        	sqlstr.append("      , CON_GUAR_TERM  =  ? \n");		// 계약이행보증기한 -->계약서항목 없음. 
        	sqlstr.append("      , DEF_EST_GUAR_RATE  =  ? \n");		// 하자보증율
        	sqlstr.append("      , DEF_GUAR_AMT  =  ? \n");		//하자보증금액
        	sqlstr.append("      , DEF_EST_TERM  =  ? \n");		// 하자보증기간
        	sqlstr.append("      , DELAY_RATE  =  ? \n");		// 지체상급율
        	sqlstr.append("      , TEMPLATE_CD  =  ? \n");		// 계약서류코드
        	sqlstr.append("      , MONTH_ACMP_CNT  =  ? \n");		// 월기성지불횟수
        	sqlstr.append("      , OBJ_RCPT_DAY  =  ? \n");		// 목적물 수령일      	
        	sqlstr.append("      , CONT_HASH  =  ? \n");		// 지체상급율
        	
        	sqlstr.append("      , CON_CUR_CD  =  ? \n");		// 통화구분 저장
        	
        	sqlstr.append("      , ELEC_CON_YN  =  ? \n");		// 전자계약여부        	
        	sqlstr.append("      , STATUS_CD  =  '10' \n");		// 계약진행상태
        	/*분개 유형*/
        	sqlstr.append(" 	 , ACCT_GB  =  ? 	\n");	
        	sqlstr.append(" 	 , PAY_GROUP  =  ? 	\n");	
        	sqlstr.append(" 	 , PROOF_CD  =  ? 	\n");	
        	sqlstr.append(" 	 , PAYMENT_TERMS  =  ? 	\n");	
        	sqlstr.append(" 	 , PAY_ITEM  = ?	\n");	
        	sqlstr.append(" 	 , COST_CODE  = ? 	\n");	
        	sqlstr.append(" 	 , COST_TYPE  = ? 	\n");	
        	sqlstr.append(" 	 , SUPP_DIRECT_YN  =  ? 	\n");	//하도급직불여부 추가(2021.04.01 최훈석)
        	sqlstr.append(" 	 , LABOR_USE_YN  =  ? 	\n");		//노무비닷컴 사용여부 추가(2021.04.01 최훈석)

        	sqlstr.append(" 	 , CON_SPE_TXT  = ? 	\n");	

        	sqlstr.append("      , STAMP_DUTY_GB	 =  ?	\n");
        	//자유서식 하면서 추가
        	sqlstr.append(" 	 , PAY_COND_GB  = ? 	\n");	
        	sqlstr.append(" 	 , PAY_CONTENT  = ? 	\n");	
        	sqlstr.append(" 	 , LABOR_GUAR_JOIN_AMT_GB  =  ?	\n");	
        	
        	sqlstr.append(" 	 , CON_IN_DATE  =  SYSDATE  	\n");	
        	
        	//승인자 추가
        	sqlstr.append("      , CON_CONFIRM_ID	 = ?	\n");									//추가 (2014-11-05) 	        	
        	sqlstr.append("      , CON_CONFIRM_NAME	 = ?	\n");									//추가 (2014-11-05) 	    
        	
			sqlstr.append("      , IN_SPE_INFO 		  =  ? 		\n");						//추가(2014-11-26)
			sqlstr.append("      , IN_CHG_INFO		  =  ? 	\n");							//추가(2014-11-26)
			sqlstr.append("      , FRST_CON_AMT		  =  ? 		\n");	        	        //추가(2014-11-26)
			sqlstr.append("      , FRST_EXE_AMT		  =  ? 		\n");	        	        //추가(2014-11-26)
			sqlstr.append("      , CHG_CON_AMT		  =  ? 		\n");	        	        //추가(2014-11-26)
			sqlstr.append("      , CHG_EXE_AMT		  =  ? 		\n");	        	        //추가(2014-11-26)
			
			sqlstr.append("      , CON_CHARGE_ID  =  ? 		\n");
			sqlstr.append("      , CON_CHARGE_NAME  =  ? 		\n");
			
			sqlstr.append("      , ERP_CHECK_YN  =  ? 		\n");
			
        	sqlstr.append(" 	 , UPD_BY  = ?  	\n");	
        	sqlstr.append(" 	 , UPD_DATE  =  SYSDATE  	\n");	

        	sqlstr.append("		WHERE  SUB_CONT_MOD_ID = "+ valueObject.get("SUB_CONT_MOD_ID") +"	\n");
        	
            persistent.setStatement(sqlstr.toString());
           	persistent.addParameter(valueObject.get("dealer_nm"));		//발주처
        	persistent.addParameter(valueObject.get("con_date_year") +"-"+ valueObject.get("con_date_month") +"-"+ valueObject.get("con_date_day"));		//계약일
        	persistent.addParameter(valueObject.get("start_work_date_year") +"-"+ valueObject.get("start_work_date_month") +"-"+ valueObject.get("start_work_date_day"));		//공사기간 시작일
        	persistent.addParameter(valueObject.get("end_work_date_year") +"-"+ valueObject.get("end_work_date_month") +"-"+ valueObject.get("end_work_date_day"));		//공사기간 종료일
        	persistent.addParameter(valueObject.get("build_name"));		//계약명
        	persistent.addParameter(valueObject.get("mod_gb"));		//계약명
        	persistent.addParameter(valueObject.get("und_con_name"));		//원도급공사명
        	persistent.addParameter(valueObject.get("sub_und_con_name"));		//하도급공사명
        	persistent.addParameter(valueObject.get("work_place"));		//공사장소
        	persistent.addParameter(valueObject.get("pay_way_cash"));		//지급방법 현금
        	persistent.addParameter(valueObject.get("pay_way_bill"));		// 지금방법 어음
        	persistent.addParameter(valueObject.get("con_amt").replaceAll(",",""));		// 계약금액
        	persistent.addParameter(valueObject.get("con_supply_amt").replaceAll(",",""));		// 공급가액
        	persistent.addParameter(valueObject.get("con_supply_tamt").replaceAll(",",""));		// 공급가액 과세
        	persistent.addParameter(valueObject.get("con_supply_namt").replaceAll(",",""));		// 공급가액 비과세
        	persistent.addParameter(valueObject.get("con_surtax_amt").replaceAll(",",""));		// 계약부과세
        	persistent.addParameter(valueObject.get("labor_amt").replaceAll(",",""));		// 노무비
        	persistent.addParameter(valueObject.get("prepay_rate").replaceAll(",",""));		// 선급금율
        	persistent.addParameter(valueObject.get("prepay_amt").replaceAll(",",""));		// 선급금액
        	persistent.addParameter(valueObject.get("prepay_guar_amt_rate").replaceAll(",",""));		// 선급금율
        	persistent.addParameter(valueObject.get("prepay_guar_amt").replaceAll(",",""));		// 선급금액        	
        	persistent.addParameter(valueObject.get("prepay_supply_amt").replaceAll(",",""));		// 선급금 공급가액
        	persistent.addParameter(valueObject.get("prepay_supply_tamt").replaceAll(",",""));		// 선급금 과세
        	persistent.addParameter(valueObject.get("prepay_supply_namt").replaceAll(",",""));		// 선급금 면세
        	persistent.addParameter(valueObject.get("prepay_surtax_amt").replaceAll(",",""));		// 선급금 부과세
        	persistent.addParameter(valueObject.get("con_est_guar_rate"));		// 계약이행 보증금율
        	persistent.addParameter(valueObject.get("con_est_guar_amt").replaceAll(",",""));		// 계약이행 보증금
        	persistent.addParameter(valueObject.get("con_guar_term"));		// 계약이행보증기한 -->계약서항목 없음. 
        	persistent.addParameter(valueObject.get("def_est_guar_rate"));		// 하자보증율
        	persistent.addParameter(valueObject.get("def_guar_amt").replaceAll(",",""));		//하자보증금액
        	persistent.addParameter(valueObject.get("def_est_term"));		// 하자보증기간
        	persistent.addParameter(valueObject.get("delay_rate"));		// 지체상급율
        	persistent.addParameter(valueObject.get("TEMPLATE_CD"));		// 계약서류코드
        	persistent.addParameter(valueObject.get("month_acmp_cnt"));		// 월기성지불횟수
        	persistent.addParameter(valueObject.get("obj_rcpt_day"));		// 목적물 수령일      	
        	persistent.addParameter(valueObject.get("CONT_HASH"));		// 지체상급율
        	persistent.addParameter(valueObject.get("CON_CUR_CD"));		// 통화구분 저장
        	persistent.addParameter(valueObject.get("ELEC_CON_YN"));		// 전자계약여부        	

        	/*분개 유형*/
        	persistent.addParameter(valueObject.get("ACCT_GB"));	
        	persistent.addParameter(valueObject.get("PAY_GROUP"));	
        	persistent.addParameter(valueObject.get("PROOF_CD"));	
        	persistent.addParameter(valueObject.get("PAYMENT_TERMS"));	
        	persistent.addParameter(valueObject.get("PAY_ITEM"));	
        	persistent.addParameter(valueObject.get("COST_CODE"));	
        	persistent.addParameter(valueObject.get("COST_TYPE"));	
        	persistent.addParameter(valueObject.get("SUPP_DIRECT_YN"));
        	persistent.addParameter(valueObject.get("LABOR_USE_YN"));

        	persistent.addParameter(valueObject.get("CON_SPE_TXT"));	
        	
        	persistent.addParameter(valueObject.get("p_issue_gb"));
        	
        	//자유서식 하면서 추가
        	persistent.addParameter(valueObject.get("PAY_COND_GB"));	
        	persistent.addParameter(valueObject.get("PAY_CONTENT"));	
        	persistent.addParameter(valueObject.get("LABOR_GUAR_JOIN_AMT_GB"));	
        	
        	//승인자 추가
        	persistent.addParameter(valueObject.get("CON_CONFIRM_ID"));	//추가 (2014-11-05) 	        	
        	persistent.addParameter(valueObject.get("CON_CONFIRM_NAME"));	//추가 (2014-11-05) 	        	
        	
        	
        	persistent.addParameter(valueObject.get("IN_SPE_INFO"));													//추가 (2014-11-26) 	        	
        	persistent.addParameter(valueObject.get("IN_CHG_INFO"));													//추가 (2014-11-26)  	        	
        	persistent.addParameter(valueObject.get("FRST_CON_AMT").replaceAll(",",""));								//추가 (2014-11-26) 	        	
        	persistent.addParameter(valueObject.get("FRST_EXE_AMT").replaceAll(",",""));								//추가 (2014-11-26)  	        	
        	persistent.addParameter(valueObject.get("CHG_CON_AMT").replaceAll(",",""));									//추가 (2014-11-26) 	        	
        	persistent.addParameter(valueObject.get("CHG_EXE_AMT").replaceAll(",",""));									//추가 (2014-11-26)
        	
        	persistent.addParameter(valueObject.get("O_ACCT_ID"));
        	persistent.addParameter(valueObject.get("O_USER_NAME"));
        	
        	persistent.addParameter(valueObject.get("ERP_CHECK_YN"));	
        	
        	persistent.addParameter(storage.getUser().get("acct_id"));	
        	
            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }	

	private ArrayList makeContPdf(ValueObject parms) {
		ArrayList<ValueObject> pdfList = new ArrayList();
		if(params.get("PDF_FILE_CNT").equals("")){
			return null;
		}
		String[] template_seq = null;
		String[] template_name = null;
		String[] contHtml = null;
		String[] contHtmlRm = null;
		if(params.getInt("PDF_FILE_CNT")>1){
			template_seq = (String[])params.getObject("template_seq");
			template_name = (String[])params.getObject("template_name");
			contHtml = (String[])params.getObject("cont_html");
			contHtmlRm = (String[])params.getObject("cont_html_rm");
		}else{
			template_seq = new String[]{params.get("template_seq")};
			template_name = new String[]{params.get("template_name")};
			contHtml = new String[]{params.get("cont_html")};
			contHtmlRm = new String[] {params.get("cont_html_rm")};
		}
		for(int i = 0 ; i < params.getInt("PDF_FILE_CNT"); i++){
			String check_yn = params.get("check_yn_"+template_seq[i]);
			if(check_yn.equals("A")||check_yn.equals("Y")){
				ValueObject vo = new ValueObject();
				vo.set("cont_no", params.get("SUB_CONT_MOD_ID"));
				vo.set("cont_chasu", params.get("MOD_NO"));
				vo.set("true_random", params.get("TRUE_RANDOM"));
				vo.set("file_seq", i);
				vo.set("folder", "outside");
				vo.set("cont_userno", params.get("CON_DOC_NO"));
				vo.set("html", contHtmlRm[i]);
				PDFMaker pdfMaker = new PDFMaker();
				ValueObject pdfInfo = pdfMaker.makeContPdf(vo);
				if (!pdfInfo.isNull(0)){
					pdfInfo.set("cont_no", params.get("SUB_CONT_MOD_ID"));
					pdfInfo.set("template_name", template_name[i]);
					pdfInfo.set("file_seq", i);
					pdfList.add(pdfInfo);
				}else{
					return null;
				}
			}
		}

		return pdfList;
	}
	
	/**
     * 계약 htm 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void CONT_OUT_HTML_del(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  CONT_OUT_HTML						\n");
        	sqlstr.append("WHERE SUB_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }	
	/**
     * 계약 htm 등록
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean CONT_OUT_HTML_Save(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
    		String[] template_seq = null;
    		String[] contHtml = null;
    		String[] template_name = null;
    		String[] template_style = null;
    		String[] option_yn = null;
    		if(valueObject.getInt("PDF_FILE_CNT")>1){
    			template_seq = (String[])valueObject.getObject("template_seq");
       			template_name = (String[])valueObject.getObject("template_name");
       			template_style = (String[])valueObject.getObject("template_style");
       			option_yn = (String[])valueObject.getObject("option_yn");
    			contHtml = (String[])valueObject.getObject("cont_html");
    		}else{
    			template_seq = new String[]{valueObject.get("template_seq")};
    			template_name = new String[]{valueObject.get("template_name")};
    			template_style = new String[]{valueObject.get("template_style")};
    			option_yn = new String[]{valueObject.get("option_yn")};
    			contHtml = new String[]{valueObject.get("cont_html")};
    		}        	
			for(int i = 0 ; i < valueObject.getInt("PDF_FILE_CNT"); i++){
				sqlstr.append("INSERT INTO CONT_OUT_HTML (SUB_CONT_MOD_ID, SEQ, TEMPLATE_NAME, TEMPLATE_HTML, TEMPLATE_STYLE, CHECK_YN, OPTION_YN)						\n");
				sqlstr.append("VALUES(?, ?, ?, ?, ?, ?, ?)					\n");
	            persistent.setStatement(sqlstr.toString());
	            persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
	            persistent.addParameter(template_seq[i]);
	            persistent.addParameter(template_name[i]);
	            persistent.addParameter(contHtml[i]);
	            persistent.addParameter(template_style[i]);
	            persistent.addParameter(valueObject.get("check_yn_"+template_seq[i]));
	            persistent.addParameter(option_yn[i]);
	            persistent.execute();  
	            sqlstr.reset();
	            persistent.clearParameters();
			}
      	
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }	
	/**
     * 계약 파일 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void CONT_OUT_FILE_del(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  CONT_OUT_FILE						\n");
        	sqlstr.append("WHERE SUB_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }	
	/**
     * 계약 파일 등록
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public String CONT_OUT_FILE_Save(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {

        	sqlstr.append("INSERT INTO CONT_OUT_FILE (SUB_CONT_MOD_ID, FILE_ID, UNIQUE_FILE_NAME, FILE_PATH, FILE_EXT, FILE_SIZE, FILE_NAME, AUTO_YN, CRE_BY, CRE_DATE)						\n");
        	sqlstr.append("VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, SYSDATE)						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("cont_no"));
            persistent.addParameter(valueObject.get("file_seq"));
            persistent.addParameter(valueObject.get("file_name"));
            persistent.addParameter(valueObject.get("file_path"));
            persistent.addParameter(valueObject.get("file_ext"));
            persistent.addParameter(valueObject.get("file_size"));
            persistent.addParameter(valueObject.get("template_name"));
            persistent.addParameter("Y");
            persistent.addParameter("");
            persistent.execute();
            persistent.clearParameters();
            
            return valueObject.get("file_hash");
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }		
	/**
     * 을사에 싸인 요청
	 * @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 {
        	//WAFLogger.debug("Exception : " + valueObject.get("pConClsYn"));
	        	sqlstr.append("UPDATE SUB2000T SET  	\n");
	        	sqlstr.append(" STATUS_CD = ?					\n");
	        	if (!valueObject.get("pConClsYn").equals(null)){
	        		sqlstr.append(", CON_CLS_YN = '"+ valueObject.get("pConClsYn") +"'					\n");
	        	}    	        	
	        	sqlstr.append("WHERE SUB_CONT_MOD_ID = ?						\n");
	            persistent.setStatement(sqlstr.toString());
	            persistent.addParameter(valueObject.get("pStatusCD"));                  
	            persistent.addParameter(valueObject.get("SUB_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);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }	

	/**
	 * 보증 대상리스트 조회
	 * @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_OUT_WARR 	\n");
	        	sqlstr.append("	               WHERE SUB_CONT_MOD_ID = '"+ param.get("SUB_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("	                   , B.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, SUB2000T B  	\n");
	        	sqlstr.append("	               WHERE SUB_CONT_MOD_ID = '"+ param.get("SUB_CONT_MOD_ID") +"' 	\n");
	        	sqlstr.append("	                     AND B.CON_EST_GUAR_AMT > 0	\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("	                   , B.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, SUB2000T B  	\n");
	        	sqlstr.append("	               WHERE SUB_CONT_MOD_ID = '"+ param.get("SUB_CONT_MOD_ID") +"' 	\n");
	        	sqlstr.append("	                     AND B.LABOR_AMT > 0	\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_OUT_WARR 	\n");
        			sqlstr.append("	               WHERE SUB_CONT_MOD_ID = '"+ param.get("SUB_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("	                   , B.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, SUB2000T B  	\n");
        			sqlstr.append("	               WHERE SUB_CONT_MOD_ID = '"+ param.get("SUB_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.CON_EST_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("	                   , B.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, SUB2000T B  	\n");
        			sqlstr.append("	               WHERE SUB_CONT_MOD_ID = '"+ param.get("SUB_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.CON_EST_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("	              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, SUB2000T B  	\n");
        			sqlstr.append("	               WHERE SUB_CONT_MOD_ID = '"+ param.get("SUB_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.LABOR_AMT > 0 AND MOD_GB NOT IN ('99', '98')	\n"); //2019.10.18 타절계약 추가
        			
        			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_OUT_WARR 	\n");
        			sqlstr.append("	               WHERE SUB_CONT_MOD_ID = '"+ param.get("SUB_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("	                   , B.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, SUB2000T B  	\n");
        			sqlstr.append("	               WHERE SUB_CONT_MOD_ID = '"+ param.get("SUB_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.CON_EST_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, SUB2000T B  	\n");
        			sqlstr.append("	               WHERE SUB_CONT_MOD_ID = '"+ param.get("SUB_CONT_MOD_ID") +"' 	\n");
        			sqlstr.append("	                     AND B.LABOR_AMT > 0 AND MOD_GB NOT IN ('99', '98')	\n"); //2019.10.18 타절계약 추가
        			
        			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");        		
        			
        		}
        	}
        	//WAFLogger.debug("### MOD_NO : " + param.get("MOD_NO"));
			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.debug("### WAFSQLException : " + se.getMessage());
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        return rowSet;
	}
	/**
     * 계약 정보 완료 프로세스
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean endContProc(Connection connection, ValueObject valueObject, boolean signChk) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);

        try {
        	conn.begin();
        	boolean bSuccess = false;
        	
        	//오프라인 계약 완료 일경우 서명 저장을 안함
        	if(signChk){
        		CONT_SignOk(super.conn.getConnection(), params);	//서명저장
        	}

			params.set("pStatusCD", "50");
			params.set("pConClsYn", "9");
			
			ContStatusUp(super.conn.getConnection(), params);	//서명 완료
			
			//2015.01.08 이인오 인지세 추가
			/*if( params.get("stamp_duty_issue_gb").equals("10") || params.get("stamp_duty_issue_gb").equals("20") ){
				mergeStampDuty(super.conn.getConnection(), params);
				
			}else if( params.get("stamp_duty_issue_gb").equals("00") ) {
				deleteStampDuty(super.conn.getConnection(), params);
				
			}*/
			
			//계약 완료 데이터 이동
			bSuccess = endMoveData(super.conn.getConnection(), params);	//보증 데이터 생성
			
			if( bSuccess ){
				ValueObject contVo = new ValueObject();
				
				//2019.10.18 변경구분 및 사업자번호 변경여부 조회
				ValueObject contChgVo = getContChgInfo(params.get("SUB_CONT_MOD_ID"));

				//계약정보 조회
				//2019.10.18 타절계약 사업자번호 변경된 경우 BID_SUPP에서 EXCHANGE 값을 가져올수 없기 때문에 최초 계약(MOD_NO=0) 기준으로 가져오도록 수정
				if(contChgVo.get("MOD_GB").equals("98") && contChgVo.get("CORP_CHG_YN").equals("Y")){
					contVo = getConfinfoChg(params.get("SUB_CONT_MOD_ID"));
					
					if(contVo.get("ORG_ERP_CD_PARTNER").equals("")){
						super.storage.setSuccess(false);	
						throw new Exception("변경전 거래처코드(ORG_ERP_CD_PARTNER) 등록이 안되어 있습니다.");
					} else if(contVo.get("ERP_CD_PARTNER").equals("")){
						super.storage.setSuccess(false);	
						throw new Exception("변경후 거래처코드(ERP_CD_PARTNER) 등록이 안되어 있습니다.");
					}				
				} else {					
					contVo = getConfinfo(params.get("SUB_CONT_MOD_ID"));					
				}				
				
				if( !contVo.get("ORG_CD_COOP").equals("") && !(contChgVo.get("MOD_GB").equals("98") && contChgVo.get("CORP_CHG_YN").equals("Y"))) { //2019.10.18 타절계약 사업자번호 변경 안된 경우 추가
					contVo.set("CD_COOP",contVo.get("ORG_CD_COOP"));
				}else {
					updateSub1000tCoop(super.conn.getConnection(), contVo);
				}
				
				if( contVo.get("CD_COOP").equals("") ) {
					super.storage.setSuccess(false);	
					throw new Exception("ERP 공사거래처(CD_COOP) 등록이 안되어 있습니다.");
				}
				
				//ERP 전송여부 Y 일때만 전송 NVL(SUB2000T.ERP_CHECK_YN,'Y')
				if( contVo.get("ERP_CHECK_YN").equals("Y") ) {
					//ERP연계 키값 없을때 조회하여 UPDATE (NO_CONT)
					if( "".equals(contVo.get("ERP_NO_CONT")) ) {
						String erp_no_cont = getErpNoCont(contVo.get("DEPT_CD"));
						contVo.set("ERP_NO_CONT", erp_no_cont);
						updateSub1000t(super.conn.getConnection(), contVo);
					}
					
					//ERP연계 키값 조회하여 UPDATE (NO_CONT, SQ_CONT)
					String erp_sq_cont = getErpSqCont(contVo.get("DEPT_CD"), contVo.get("ERP_NO_CONT"));
					contVo.set("ERP_SQ_CONT", erp_sq_cont);
					
					if( "01".equals(erp_sq_cont) ) {
						contVo.set("CD_CM00202", "01");
					}else {
						contVo.set("CD_CM00202", "02");
						
					}
					updateSub2000t(super.conn.getConnection(), contVo);
					
					RowSet conDetail = getConfDetail(contVo);
					
					/*
					 * 분산트렌젝션문제로 새로운 커넥션으로 ERP insert
					 */
					WAFConnectionImpl erpConn = new WAFConnectionImpl();
					try {
						erpConn.setDataSource("jdbc/erpiu");
						erpConn.begin();
						
						InterfaceOutERPiU.insertErpContMst(erpConn.getConnection(), contVo);
						
						long mstAmt = contVo.getLong("SAMT_CONT",0) + contVo.getLong("FAMT_CONT",0);
						
						if( conDetail != null && conDetail.size() > 0) {
							for( int i = 0; i < conDetail.size(); i++ ) {
								ValueObject detailVo = RowSetUtility.getValueObject(conDetail.getRow(i)); 
								
								long amt = detailVo.getLong("AMT_CONT",0);
								
								/*
								 * 헤더금액 = SUM(상세금액)
								 * 헤더의 원단위 맞추기 위해 마지막내역 금액조정
								 */
								if( i == conDetail.size() - 1 ) {
									detailVo.set("UNT_CONT", mstAmt);
									detailVo.set("AMT_CONT", mstAmt);
								}
								
								InterfaceOutERPiU.insertErpContDet(erpConn.getConnection(), detailVo);
								
								mstAmt -= amt;
							}
						}
						
						//2019.10.18 타절계약 사업자번호 변경시 추가
						if(contChgVo.get("MOD_GB").equals("98") && contChgVo.get("CORP_CHG_YN").equals("Y")){
							InterfaceOutERPiU.execCoopChg(erpConn.getConnection(), contVo);
						}
					}catch(Exception e) {
						conn.setRollback();
						erpConn.setRollback();
						throw e;
					}finally {
						erpConn.close();
					}
					
				}
				
			}

			if(signChk){
				
				ValueObject vo = getContRequestValueObject(params.get("SUB_CONT_MOD_ID"));
				
				MessageSend messageSend = new MessageSend(storage);
				String resHp = vo.get("C_HP");
				String recvAddress = vo.get("O_TEL_NUM");  //O_HP 2015.9.21 담당자번호로 변경. 
				String conChargeId = vo.get("CON_CHARGE_ID");
				String memberName = vo.get("C_MEMBER_NAME");
				String deptName = vo.get("DEPT_NAME");
				String conDocNo = vo.get("CON_DOC_NO");
				String buildName = vo.get("BUILD_NAME");
				String userName = vo.get("CON_CHARGE_NAME");
				String hp = vo.get("O_HP");
				String resEmail = vo.get("C_EMAIL");
				String eMail = vo.get("O_EMAIL");
				
				String smsContent = "하도급계약이 완료 되었습니다.'||CHR(13)||CHR(10)||'협력업체 포탈시스템에 접속/확인 바랍니다.'||CHR(13)||CHR(10)||'SGC이앤씨(주)";
				messageSend.sendSMS(resHp, recvAddress, conChargeId, smsContent);  //2015.9.21 계약담당자 발송 
				
				MessageSend mailSend = new MessageSend(storage);
	      		StringBuffer mailContent = new StringBuffer();
	      		mailContent.append("SGC이앤씨(주)과 "+memberName+ "간의 하도급계약이 완료되었습니다. </BR>");
	      		mailContent.append("협력업체 포탈시스템에서 하도급계약서 확인하시기 바랍니다.</BR>");
	      		mailContent.append("<font color='red'>※선급금이 있는 경우 SGC이앤씨 외주팀 계약담당자에게 필히 연락하여 신청이 가능한지</BR>");
	      		mailContent.append("&nbsp;&nbsp;체크 한 후 협력업체 포탈시스템에서 신청, 일부신청, 포기를 계약일로부터 5일 내로</BR>");
	      		mailContent.append("&nbsp;&nbsp;반드시 하시기 바랍니다.</BR>");
	      		mailContent.append("</font>");
	      		mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	      		mailContent.append("</BR>");
	      		mailContent.append("1.프로젝트: "+deptName+"</BR>");
	      		mailContent.append("2.계약번호: "+conDocNo+"</BR>");
	      		mailContent.append("3.계약명: " +buildName+"</BR>");
	      		mailContent.append("4.SGC이앤씨 외주팀 담당자:"+userName+"(연락처 :"+hp+")</BR>");
	      		mailContent.append("</font>");
	      		messageSend.sendEmail(resEmail, eMail, "하도급 계약 완료 통보 메일입니다.", "하도급 계약 완료 통보 메일입니다.", mailContent);
			}
			send_toETEC();  //발주의뢰자, 변경계약 요청자 메일발송  2016.6.16 
			return true;
        }
        catch(Exception e) {
        	//WAFLogger.debug("Exception deleteContProc : " + e.getMessage());
            WAFLogger.error(this.getClass().getName() + " --> deleteContProc() : " + e.getMessage());
            WAFLogger.error(e);

            conn.setRollback();
            
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }
        finally {
        	super.conn.end();
        }        
    }		
	
	//2016.6.16 계약완료후 발주의뢰자(변경계약 요청자) 메일발송 
	public boolean send_toETEC() throws Exception {
        try {
				ValueObject vo = getContRequestValueObject(params.get("SUB_CONT_MOD_ID"));
				
				MessageSend mailSend = new MessageSend(storage);
	      		StringBuffer mailContent = new StringBuffer();
	      		String title = "[알림]계약완료";
	      		String gb = "계약";
	      		String toMail = vo.get("EMP_MAIL");//발주의뢰자 
	      		if (!vo.get("mod_no").equals("0")) {
	      			title = "[알림]변경계약완료";
	      			gb = "변경계약";
	      			toMail = vo.get("CHG_MAIL");;  //변경계약 요청자 
	      		}
	      		
	      		mailContent.append("SGC이앤씨(주)과 "+vo.get("C_MEMBER_NAME")+ "간의 하도급계약이 완료되었습니다. </BR>");
	      		mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
	      		mailContent.append("</BR>");
	      		mailContent.append("1.계약구분: "+ gb +"</BR>");
	      		mailContent.append("2.프로젝트: "+ vo.get("DEPT_NAME")+"</BR>");
	      		mailContent.append("3.계약번호: "+ vo.get("CON_DOC_NO")+"</BR>");
	      		mailContent.append("4.계약명: " + vo.get("BUILD_NAME")+"</BR>");
	      		mailContent.append("5.외주담당자:"+ vo.get("CON_CHARGE_NAME")+"</BR>");
	      		mailContent.append("6.계약업체:"+ vo.get("C_MEMBER_NAME")+"</BR>");
	      		mailContent.append("7.비고</BR>");
	      		mailContent.append("&nbsp;1)계약서 및 관련 서류는 전자조달시스템의 외주-계약결과에서 조회 가능합니다.</BR>");
	      		mailContent.append("&nbsp;2)기성금 정보 및 관련 보증증권은 전자조달시스템의 외주-계약결과의 '자료' 메뉴에서 조회 가능합니다.</BR>");
	      		mailContent.append("</font>");
	      		
	      		mailSend.sendEmail(vo.get("O_EMAIL"), "partner@sgc.co.kr", title, title, mailContent, "1");
	      		if (!toMail.equals("")) 
	      			mailSend.sendEmail(toMail, "partner@sgc.co.kr", title, title, mailContent, "1");
	      		
			    return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> send_toETEC() : " + e.getMessage());
            WAFLogger.error(e);
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }      
    }			
	/**
     * 갑) 서명 저장
	 * @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_OUT_SUPP SET  	\n");
        	sqlstr.append(" SIGN_DATE = SYSDATE				\n");
        	sqlstr.append(" ,SIGN_DN = ?					\n");
        	sqlstr.append(" ,SIGN_DATA = ?					\n");
        	sqlstr.append("WHERE SUB_CONT_MOD_ID = ?						\n");
        	sqlstr.append(" 	AND SIGN_SEQ=1						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("CONT_SIGNDN"));
            persistent.addParameter(valueObject.get("CONT_SIGNDATA"));
            persistent.addParameter(valueObject.get("SUB_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);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }		

	/**
     * 계약 완료 데이터 이동 처리
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean endMoveData(Connection connection, ValueObject valueObject) throws Exception {

        CallableStatement cs = null;
        ValueObject rtnVo = new ValueObject();
        String sqlStr = "";
        try {
        	//conn.begin();
        	sqlStr = "{call SP_SUB1000T_OTEHR_SET_ERP(?, ?, ?)}";
        	
        	//WAFLogger.debug("SQL : " + sqlStr); 
        	
            cs = super.conn.getConnection().prepareCall(sqlStr);
            cs.setString(1, valueObject.get("SUB_CONT_MOD_ID"));
        	//WAFLogger.debug("1 : " + valueObject.get("SUB_CONT_MOD_ID")); 

            cs.registerOutParameter(2, java.sql.Types.VARCHAR);
            cs.registerOutParameter(3, java.sql.Types.VARCHAR);
            cs.execute();
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(2));
            rtnVo.set("OUT_MESSAGE",cs.getString(3));            
            
            return true;
        }
        catch(Exception e) {
            super.doException(e);
            rtnVo.set("OUT_SUCCESS_CD",cs.getString(2));
            rtnVo.set("errMsg",cs.getString(3));
            //super.conn.setRollback();
            this.storage.setSuccess(false);
            this.storage.setMessage(e.getMessage());
            return false;
        }
        finally {
            //super.conn.end();
        }
    }	
	public void Send(ValueObject params, String event) throws Exception {
		MessageSend messageSend = new MessageSend(storage);

		ValueObject contSendInfo = new ValueObject();
		contSendInfo = getContRequestValueObject(params.get("SUB_CONT_MOD_ID"));

 		//WAFLogger.debug("담당자 C_EMAIL : " + contSendInfo.get("C_EMAIL"));
		if(!contSendInfo.get("C_EMAIL").isEmpty()){
			boolean email = false;
	 		StringBuffer mailtitle = new StringBuffer();
	 		StringBuffer mailtitlein = new StringBuffer();
	        StringBuffer mailContent = new StringBuffer();
	        if("SIGN_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("CON_DOC_NO") + "<br>");
	         	mailContent.append("3.계약명 : " + contSendInfo.get("BUILD_NAME") + "<br>");
	         	mailContent.append("4.계약담당자 : " +contSendInfo.get("O_DIVISION")+" / "+ contSendInfo.get("O_USER_NAME") +" (연락처 : " + contSendInfo.get("O_TEL_NUM") + ")<br>");
	         	mailContent.append("</font>");
	        	email = true;	    
	 		}
	        if(email){	//params.get("C_EMAIL")
	        	messageSend.sendEmail(contSendInfo.get("C_EMAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent);
	        }
		}
 		//WAFLogger.debug("담당자 C_HP : " + contSendInfo.get("C_HP"));
		if(!contSendInfo.get("C_HP").isEmpty()){
	 		//WAFLogger.debug("C_HP : "+contSendInfo.get("C_HP"));
	 		String smsContent = "";
	 		if("SIGN_CALL".equals(event)){//서명요청
	 			smsContent = WAFMessages.getMessage("Contract.Signing.Portalrequest");
	 		}

	 		//WAFLogger.debug("담당자 HP : " + contSendInfo.get("O_HP").replace("-", ""));
	        
	        //WAFLogger.debug("받는사람 HP : " + contSendInfo.get("C_HP").replace("-", ""));
	        
	        String se_phon_no = contSendInfo.get("O_TEL_NUM").replace("-", ""); //보내는사람 2015.9.21변경 O_HP
	        String re_phon_no = contSendInfo.get("C_HP").replace("-", ""); //받는사람
	        if(!smsContent.isEmpty()){
	        	messageSend.sendSMS(re_phon_no, se_phon_no, storage.getUser().get("ACCT_ID"), smsContent); //2015.9.21 계약담당자로 변경 .
	        }
		}
	}	
	/**
	 * ERP 업체 등록 확인
	 * @param Params
	 * @return
	 */
	private Integer getErpSuppCount(String corpid) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        StrBuffer sqlstr = new StrBuffer();
        Integer wCount = 0;
        try{
        	
        	sqlstr.append("SELECT	COUNT(*) AS CNT												\n");
        	sqlstr.append("FROM		SUPP_INFO A													\n");
        	sqlstr.append("			,ETEC_ERPIF.ACZ10800 B										\n");
        	sqlstr.append("WHERE	REPLACE(A.CORP_REG_NO,'-','') = REPLACE(B.DCERTI_NO,'-','')	\n");
        	sqlstr.append("AND		B.ADDITEM01 = 'A'											\n");
        	sqlstr.append("AND      B.ADDITEM20 = 'O'               											\n"); //2019.02.26
        	sqlstr.append("AND		A.CORP_ID = ?												\n");
        	
			search.setStatement(sqlstr.toString());
			search.addParameter(corpid);
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
                wCount = valueobject.getInt(0);
            }
            //WAFLogger.debug("wCount : " + wCount);
            	
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //WAFLogger.debug(sqlstr.toString());
            //WAFLogger.debug("WAFSQLException : " + se.getMessage());
        }
        finally {

        	if( valueobject == null ) valueobject = new ValueObject();
        	//WAFLogger.debug("### valueobject size : " + valueobject.size());
        }
        return wCount;
	}
	
	private Integer getErpSuppCountAll(String corpRegNo) throws Exception {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject valueobject = null;
		StrBuffer sqlstr = new StrBuffer();
		Integer wCount = 0;
		
		try{
			sqlstr.append("	SELECT  COUNT(*) AS CNT									\n");
			sqlstr.append("	FROM    MA_PARTNER@ERPIU								\n");
			sqlstr.append("	WHERE   REPLACE(NO_COMPANY,'-','') = REPLACE(?,'-','')	\n");
			sqlstr.append("	OR   	CD_PARTNER = ?									\n");
			search.setStatement(sqlstr.toString());
			search.addParameter(corpRegNo);
			search.addParameter(corpRegNo);
			rowSet = search.execute();
			if( rowSet != null && rowSet.size() > 0 ){
				valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
				wCount = valueobject.getInt(0);
			}
			
		}catch(WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
			throw se;
		}
		
		return wCount;
	}
	
	/**
     * 계약 정보 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean deleteContProc(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);

        try {
        	conn.begin();

        	updateCHPCont(super.conn.getConnection(), params);   //변경계약 요청 상태 결재 완료로 변경.
        	
			CONT_OUT_HTML_del(super.conn.getConnection(), params);	//ContHTML 삭제
			//WAFLogger.debug("ContHTML 삭제");

			CONT_OUT_FILE_del(super.conn.getConnection(), params);	//자동생성파일 삭제
			//WAFLogger.debug("자동생성파일 삭제");

			delContCorpData(super.conn.getConnection(), params);		//계약업체정보 저장
			//WAFLogger.debug("계약업체정보 삭제");

			delContItemData(super.conn.getConnection(), params);		//계약내역 2018.11.1
			
			delContInfo(super.conn.getConnection(), params);		//계약 정보 삭제
			//WAFLogger.debug("계약정보 삭제");
			
			

			return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> deleteContProc() : " + e.getMessage());
            WAFLogger.error(e);

            conn.setRollback();
            
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }
        finally {
        	super.conn.end();
        }        
    }	
	/**
     * 계약 업체 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void delContCorpData(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  CONT_OUT_SUPP						\n");
        	sqlstr.append("WHERE SUB_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	
	/**
     * 계약 정보 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void delContInfo(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  SUB2000T						\n");
        	sqlstr.append("WHERE SUB_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
	
	/**
	 * 계약 정보 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void updateCHPCont(Connection connection, ValueObject valueObject) throws Exception {
		
		Persistent persistent = new PersistentImpl(connection);
		StrBuffer sqlstr = new StrBuffer();
		try {
			sqlstr.append("UPDATE SUB3000T						\n");
			sqlstr.append("SET  STATUS_CD = '30'						\n");
			sqlstr.append("WHERE SUB_CONT_MOD_ID = ( SELECT SUB_CONT_MOD_ID FROM  SUB2000T WHERE (SUB_CONT_ID, MOD_NO) = ( SELECT SUB_CONT_ID , MOD_NO -1 FROM SUB2000T WHERE SUB_CONT_MOD_ID = ? ))					\n");
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
			persistent.execute();
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			throw e;
		}
	}
	
	/**
     * 인지세 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 = 'SUB_STAMP_DUTY'                   \n");
			sqlstr.append("   			   AND ID = B.STAMP_DUTY_ID) 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 = 'SUB_STAMP_DUTY'                   \n");
			sqlstr.append("   			   AND ID = B.STAMP_DUTY_ID) AS FILE_ID                                           \n");                                                   
			sqlstr.append("   FROM SUB2000T  A,                                                                           \n");                  
			sqlstr.append("        STAMP_DUTY B                                                                           \n");                 
			sqlstr.append("   WHERE A.SUB_CONT_MOD_ID = B.CONT_MOD_ID                                                     \n");                  
			sqlstr.append("   AND   B.STAMP_DUTY_TYPE(+) = '20'                                                           \n");                 
			sqlstr.append("   AND   A.SUB_CONT_MOD_ID = '"+params.get("SUB_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 updateStampDutyGb(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
	        	sqlstr.append("UPDATE PUR2000T SET  	\n");
	        	sqlstr.append(" STAMP_DUTY_GB = ?					\n");
	        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
	           
	        	persistent.setStatement(sqlstr.toString());
	            
	        	persistent.addParameter(valueObject.get("p_issue_gb"));                  
	            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));

            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updateStampDutyGb() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	

	/**
     * 서약서 List
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	private RowSet getPromiseList(ValueObject param) {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        
        try{
			
        	sqlstr.append("  SELECT PROMISE_DOC_ID,                                                 \n");
        	sqlstr.append("         PROMISE_DOC_TYPE_GB,                                            \n");
        	sqlstr.append("         REMARK,                                                         \n");
        	sqlstr.append("         (SELECT DECODE (MAX (AA.FILE_ID), NULL, '파일첨부', '1')        \n");
        	sqlstr.append("            FROM PUB0510T AA, PUB0500T BB                                \n");
        	sqlstr.append("           WHERE     AA.FILE_ID = BB.FILE_ID                             \n");
        	sqlstr.append("                 AND FILE_GB = 'OUT_PROMISE_DOC'                         \n");
        	sqlstr.append("                 AND ID = B.PROMISE_DOC_ID)    AS FILE_YN,               \n");
        	sqlstr.append("         (SELECT MAX (AA.FILE_ID)                                        \n");
        	sqlstr.append("            FROM PUB0510T AA, PUB0500T BB                                \n");
        	sqlstr.append("           WHERE     AA.FILE_ID = BB.FILE_ID                             \n");
        	sqlstr.append("                 AND FILE_GB = 'OUT_PROMISE_DOC'                         \n");
        	sqlstr.append("                 AND ID = B.PROMISE_DOC_ID)    AS FILE_ID                \n");
        	sqlstr.append("    FROM SUB2000T A, PROMISE_DOC_TYPE B                                  \n");
        	sqlstr.append("   WHERE     A.SUB_CONT_MOD_ID = B.CONT_MOD_ID                           \n");
        	sqlstr.append("         AND B.PROMISE_DOC_TYPE(+) = '10'                                \n");
        	sqlstr.append("         AND A.SUB_CONT_MOD_ID = '"+params.get("SUB_CONT_MOD_ID")+"'     \n");
        	sqlstr.append("ORDER BY PROMISE_DOC_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 mergeStampDuty(Connection connection, ValueObject valueObject) throws Exception {
		Persistent persistent = new PersistentImpl(connection);
		StrBuffer sqlstr = new StrBuffer();
		try {

			sqlstr.append(" MERGE	INTO STAMP_DUTY A													\n");
			sqlstr.append("     USING 	(																\n");
			sqlstr.append("             SELECT	SUB_CONT_MOD_ID	AS CONT_MOD_ID							\n");
			sqlstr.append("             		,CON_DOC_NO	AS CONT_NO									\n");
			sqlstr.append("                     ,MOD_NO	AS MOD_NO										\n");
			sqlstr.append("                     ,BUILD_NAME AS CONT_NAME								\n");
			sqlstr.append("                     ,CON_AMT AS CONT_AMT									\n");
			sqlstr.append("             FROM	SUB2000T												\n");
			sqlstr.append("             WHERE	SUB_CONT_MOD_ID = ?										\n");
			sqlstr.append("             ) B																\n");
			sqlstr.append("     ON		(A.CONT_MOD_ID = B.CONT_MOD_ID AND A.STAMP_DUTY_TYPE = '20')	\n");
			sqlstr.append(" WHEN MATCHED THEN															\n");
			sqlstr.append(" 	UPDATE	SET																\n");
			sqlstr.append("             STAMP_DUTY_ISSUE_GB = ?											\n");
			sqlstr.append("             ,CORP_REG_NO = ?												\n");
			sqlstr.append("             ,CORP_NAME = ?													\n");
			sqlstr.append("             ,CONT_NO = B.CONT_NO											\n");
			sqlstr.append("             ,MOD_NO = B.MOD_NO												\n");
			sqlstr.append("             ,STAMP_DUTY_NO = ?												\n");
			sqlstr.append("             ,CONT_NAME = B.CONT_NAME										\n");
			sqlstr.append("             ,CONT_AMT = B.CONT_AMT											\n");
			sqlstr.append("             ,STAMP_DUTY_TAX = ?												\n");
			sqlstr.append("             ,STAMP_DUTY_DATE = ?											\n");
			sqlstr.append("             ,REMARK = ?														\n");
			sqlstr.append(" WHEN NOT MATCHED THEN														\n");
			sqlstr.append(" 	INSERT	(																\n");
			sqlstr.append("             STAMP_DUTY_ID													\n");
			sqlstr.append("             ,STAMP_DUTY_TYPE												\n");
			sqlstr.append("             ,STAMP_DUTY_ISSUE_GB											\n");
			sqlstr.append("             ,CONT_MOD_ID													\n");
			sqlstr.append("             ,CORP_REG_NO													\n");
			sqlstr.append("             ,CORP_NAME														\n");
			sqlstr.append("             ,CONT_NO														\n");
			sqlstr.append("             ,MOD_NO															\n");
			sqlstr.append("             ,STAMP_DUTY_NO													\n");
			sqlstr.append("             ,CONT_NAME														\n");
			sqlstr.append("             ,CONT_AMT														\n");
			sqlstr.append("             ,STAMP_DUTY_TAX													\n");
			sqlstr.append("             ,STAMP_DUTY_DATE												\n");
			sqlstr.append("             ,REMARK															\n");
			sqlstr.append(" 			)																\n");
			sqlstr.append("     VALUES	(																\n");
			sqlstr.append("     		STAMP_DUTY_S.NEXTVAL											\n");
			sqlstr.append("             ,'20'															\n");
			sqlstr.append("             ,? --STAMP_DUTY_ISSUE_GB										\n");
			sqlstr.append("             ,? --CONT_MOD_ID												\n");
			sqlstr.append("             ,? --CORP_REG_NO												\n");
			sqlstr.append("             ,? --CORP_NAME													\n");
			sqlstr.append("             ,B.CONT_NO --CONT_NO											\n");
			sqlstr.append("             ,B.MOD_NO --MOD_NO												\n");
			sqlstr.append("             ,? --STAMP_DUTY_NO												\n");
			sqlstr.append("             ,B.CONT_NAME --CONT_NAME										\n");
			sqlstr.append("             ,B.CONT_AMT --CONT_AMT											\n");
			sqlstr.append("             ,? --STAMP_DUTY_TAX												\n");
			sqlstr.append("             ,? --STAMP_DUTY_DATE											\n");
			sqlstr.append("             ,? --REMARK														\n");
			sqlstr.append("     		)																\n");
			    		
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
			persistent.addParameter(valueObject.get("stamp_duty_issue_gb"));
			persistent.addParameter(valueObject.get("O_REG_NO"));
			persistent.addParameter(valueObject.get("O_MEMBER_NAME"));
			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("stamp_duty_issue_gb"));
			persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
			persistent.addParameter(valueObject.get("O_REG_NO"));
			persistent.addParameter(valueObject.get("O_MEMBER_NAME"));
			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.execute();
			
			return true;
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> mergeStampDuty() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			throw e;
		}
	}*/
	
	/**
	 * 인지세 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
/*	public boolean deleteStampDuty(Connection connection, ValueObject valueObject) throws Exception {
		Persistent persistent = new PersistentImpl(connection);
		StrBuffer sqlstr = new StrBuffer();
		try {
			
			sqlstr.append(" BEGIN														\n");
			sqlstr.append(" 	DELETE	FROM PUB0500T									\n");
			sqlstr.append(" 	WHERE	FILE_ID IN 	(									\n");
			sqlstr.append(" 	                    SELECT	FILE_ID						\n");
			sqlstr.append(" 	                    FROM	PUB0510T					\n");
			sqlstr.append(" 	                    WHERE	FILE_GB = 'SUB_STAMP_DUTY'	\n");
			sqlstr.append(" 	                    AND		ID = ?						\n");
			sqlstr.append(" 	                    );									\n");
			sqlstr.append(" 	DELETE	FROM PUB0510T									\n");
			sqlstr.append(" 	WHERE	FILE_GB = 'SUB_STAMP_DUTY'						\n");
			sqlstr.append(" 	AND		ID = ?;											\n");
			sqlstr.append(" 	DELETE	FROM STAMP_DUTY									\n");
			sqlstr.append(" 	WHERE	CONT_MOD_ID	= ?									\n");
			sqlstr.append(" 	AND		STAMP_DUTY_TYPE = '20';							\n");
			sqlstr.append(" END;														\n");
			
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
			persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
			persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
			persistent.execute();
			
			return true;
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> deleteStampDuty() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			throw e;
		}
	}*/
	
	/**
     * 인지세 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
/*	public boolean saveStampDuty(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);

        try {
        	conn.begin();

			//2015.01.08 이인오 인지세 추가
			if( !params.get("stamp_duty_issue_gb").equals("00") ){
				mergeStampDuty(super.conn.getConnection(), params);
				
			}else {
				deleteStampDuty(super.conn.getConnection(), params);
				
			}

			return true;
        }
        catch(Exception e) {
        	//WAFLogger.debug("Exception : " + e.getMessage());
            WAFLogger.error(this.getClass().getName() + " --> saveStampDuty() : " + e.getMessage());
            WAFLogger.error(e);

            conn.setRollback();
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }
        finally {
        	super.conn.end();
        }        
    }*/
	

	/**
	 * 변경발주의뢰요청 상태 변경
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean udpReqData(Connection connection, ValueObject valueObject) throws Exception {
		Persistent persistent = new PersistentImpl(connection);
		StrBuffer sqlstr = new StrBuffer();
		try {
			
			sqlstr.append(" 	UPDATE  SUB3000T SET									\n");
			sqlstr.append(" 	 STATUS_CD = '60'									\n");
			sqlstr.append(" 	WHERE SUB_CONT_MOD_ID = ?							\n");
			
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
			persistent.execute();
			
			return true;
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> udpReqData() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			throw e;
		}
	}
	
	
	public boolean updateMoveStatus(Connection connection, ValueObject valueObject) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("		UPDATE SUB2000T SET 				\n");
        	sqlstr.append("       STATUS_CD  =  '00' 				\n");		// 계약대상
        	sqlstr.append("       WHERE  SUB_CONT_MOD_ID =  ?				\n");		// 계약대상
        	
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(params.get("SUB_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);
            throw e;
        }
    }
	
	public boolean updateGuarStatus(Connection connection, ValueObject valueObject) throws Exception {
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("		UPDATE SUB2000T SET 				\n");
        	sqlstr.append("       STATUS_CD  =  '19' 				\n");		// 보증완료
        	sqlstr.append("       WHERE  SUB_CONT_MOD_ID =  ?				\n");		// 계약대상
        	
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(params.get("SUB_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);
            throw e;
        }
    }
	
	/*
	 * 원계약건이 전문건설인지 서울보증인지 확인 
	 * */
	public String getWarrGubun(String subContModId) {
	 	
	 	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_OUT_WARR                        																							\n");
	 		sqlstr.append("				WHERE SUB_CONT_MOD_ID = (                 																							\n");
	 		sqlstr.append("											SELECT SUB_CONT_MOD_ID                    																\n");
	 		sqlstr.append("											FROM SUB2000T                             																\n");
	 		sqlstr.append("											WHERE MOD_NO = 0                          																\n");
	 		sqlstr.append("											AND SUB_CONT_ID = (SELECT MIN(SUB_CONT_ID) FROM SUB2000T WHERE SUB_CONT_MOD_ID = '"+subContModId+"')    \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;
	 }
	
	private ValueObject getConfinfo(String sub_cont_mod_id) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject vo = new ValueObject();
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT	A.SUB_CONT_MOD_ID												\n");
        	sqlstr.append(" 		,A.SUB_CONT_ID													\n");
        	sqlstr.append(" 		,F.ERP_NO_CONT													\n");
        	sqlstr.append(" 		,NVL(B.ERP_SITE_CD, A.DEPT_CD) AS DEPT_CD						\n");
        	sqlstr.append(" 		,'"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY											\n");
            sqlstr.append(" 		,A.BUILD_NAME AS NM_CONT											\n");
            sqlstr.append(" 		,TO_CHAR(A.CON_DATE,'YYYYMMDD') AS DT_CONT						\n");
            sqlstr.append(" 		,'01' AS CD_CM00201												\n");
            sqlstr.append(" 		,'01' AS CD_CM00212												\n");
            sqlstr.append(" 		,'03' AS CD_CM00213												\n");
            sqlstr.append(" 		,'01' AS CD_CM00208												\n");
            sqlstr.append(" 		,TO_CHAR(A.START_WORK_DATE,'YYYYMMDD') AS DT_FM_CONT				\n");
            sqlstr.append(" 		,TO_CHAR(A.END_WORK_DATE,'YYYYMMDD') AS DT_TO_CONT				\n");
            sqlstr.append(" 		,D.ERP_CD_COOP AS CD_COOP /*거래처코드*/							\n");
            sqlstr.append(" 		,D.DEALR_NM AS NM_COOP /*거래처명*/								\n");
            sqlstr.append(" 		,'' AS TP_COMPANY /*사업자구분*/									\n");
            sqlstr.append(" 		,REPLACE(C.VENDCD,'-') AS NO_COMPANY /*사업자번호*/           		\n");
            //2019.10.01 타절로 계약금액이 0인 경우 0으로 나누기 오류 발생 
            //sqlstr.append(" 		,ROUND(A.CON_SUPPLY_TAMT/A.CON_SUPPLY_AMT*100,2) AS DRAT_TAX		\n");            
            //sqlstr.append(" 		,100 - ROUND(A.CON_SUPPLY_TAMT/A.CON_SUPPLY_AMT*100,2) AS FRAT_TAX	\n");
            sqlstr.append(" 		,ROUND(NVL(A.CON_SUPPLY_TAMT / DECODE(A.CON_SUPPLY_AMT, 0, NULL, A.CON_SUPPLY_AMT), 0) * 100, 2) AS DRAT_TAX \n");
            sqlstr.append(" 		,100 - ROUND(NVL(A.CON_SUPPLY_TAMT / DECODE(A.CON_SUPPLY_AMT, 0, NULL, A.CON_SUPPLY_AMT), 0) * 100, 2) AS FRAT_TAX \n");
            sqlstr.append(" 		,ROUND(A.CHG_CON_AMT * NVL(E.EXCHANGE,1)) AS AMT_ORD						\n");
            sqlstr.append(" 		,ROUND(A.CHG_EXE_AMT * NVL(E.EXCHANGE,1)) AS AMT_ACT						\n");
            sqlstr.append(" 		,ROUND(A.CON_SUPPLY_AMT * NVL(E.EXCHANGE,1)) AS AMT_CONT				\n");
            sqlstr.append(" 		,ROUND(A.CON_SUPPLY_TAMT * NVL(E.EXCHANGE,1)) AS SAMT_CONT				\n");
            sqlstr.append(" 		,ROUND(DECODE(A.PROOF_CD,'Z99',0,A.CON_SURTAX_AMT) * NVL(E.EXCHANGE,1)) AS VAMT_CONT	\n");
            sqlstr.append(" 		,ROUND(A.CON_SUPPLY_NAMT * NVL(E.EXCHANGE,1)) AS FAMT_CONT				\n");
            //2019.07.26 변경발주일경우 PMM_DEMANDED_REQUEST가 아닌 SUB3000T로 들어오기 때문에 발주번호가 제대로 넘어가지 못함
            //sqlstr.append(" 		,(SELECT  MAX(PMM.ERP_NO_CORD)									\n");
            //sqlstr.append(" 		FROM    BID_MASTER BID											\n");
            //sqlstr.append("    		     	,PMM_DEMANDED_REQUEST PMM								\n");
            //sqlstr.append(" 		WHERE   BID.DEMANDED_ID = PMM.DEMANDED_ID						\n");
            //sqlstr.append(" 		AND     BID.BID_NO = A.BID_NO) AS NO_CORD						\n");
            sqlstr.append("         ,(SELECT MAX(NO_CORD)                                           \n");
            sqlstr.append("             FROM CONT_OUT_ITEM ITEM                                     \n");
            sqlstr.append("            WHERE ITEM.SUB_CONT_MOD_ID = A.SUB_CONT_MOD_ID) AS NO_CORD   \n");
            sqlstr.append(" 		,'' AS SQ_ESTM /*견적업체순번*/									\n");
            sqlstr.append(" 		,'' AS NM_CONT_ML /*계약명(외국어)*/								\n");
            sqlstr.append(" 		,'' AS DT_DELV /*납기일자*/										\n");
            sqlstr.append(" 		,'' AS LOC_DELV /*납기장소*/										\n");
            sqlstr.append(" 		,(SELECT CHG_CODE10 FROM EBID_COM911T WHERE BASECD = '911' AND DETAILCD = A.CON_CUR_CD) AS CURR_SOUR	\n");
            sqlstr.append(" 		,DECODE(NVL(A.CON_CUR_CD,'KRW'),'KRW',NULL,E.EXCHANGE) AS UNT_CURR			\n");
            sqlstr.append(" 		,DECODE(NVL(A.CON_CUR_CD,'KRW'),'KRW',NULL,A.CON_SUPPLY_TAMT) AS AMT_CURR	\n");
            sqlstr.append(" 		,DECODE(NVL(A.CON_CUR_CD,'KRW'),'KRW',NULL,DECODE(A.PROOF_CD,'Z99',0,A.CON_SURTAX_AMT)) AS VAMT_CURR	\n");
            sqlstr.append(" 		,DECODE(NVL(A.CON_CUR_CD,'KRW'),'KRW',NULL,A.CON_SUPPLY_NAMT) AS FAMT_CURR	\n");
            sqlstr.append(" 		,REPLACE(A.PROOF_CD,'Z99','') AS CD_PROOF									\n");
            sqlstr.append(" 		,A.PAYMENT_TERMS AS CD_CM00206												\n");
            sqlstr.append(" 		,(SELECT USER_SABUN FROM PUB0120T WHERE ACCT_ID = A.CRE_BY)  AS ID_INSERT	\n");
            sqlstr.append(" 		,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT							\n");
            sqlstr.append(" 		,NVL(A.ERP_CHECK_YN,'Y') AS ERP_CHECK_YN									\n");
            sqlstr.append(" 		,F.ERP_CD_COOP AS ORG_CD_COOP									\n");
            sqlstr.append(" 		,A.ACCT_GB  AS CD_COST_TRADE									\n");  //2019.04.13 분개유형추가
            sqlstr.append(" FROM	SUB2000T A														\n");
        	sqlstr.append(" 		,ETEC_ERPIF.SITE_MAPPING B										\n");
        	sqlstr.append(" 		,CONT_OUT_SUPP C												\n");
        	sqlstr.append(" 		,ETEC_ERPIF.ACZ10800 D											\n");
        	sqlstr.append(" 		,BID_SUPP E														\n");
        	sqlstr.append(" 		,SUB1000T F														\n");
        	sqlstr.append(" 		,SUPP_INFO G													\n");
        	sqlstr.append(" WHERE	A.DEPT_CD = B.EBID_SITE_CD(+)									\n");
        	sqlstr.append(" AND		A.SUB_CONT_MOD_ID  = C.SUB_CONT_MOD_ID							\n");
        	sqlstr.append(" AND		A.SUB_CONT_ID  = F.SUB_CONT_ID									\n");
        	sqlstr.append(" AND		C.CORP_ID != 0													\n");
        	sqlstr.append(" AND		C.CORP_ID = G.CORP_ID											\n");
        	sqlstr.append(" AND     REPLACE(G.CORP_REG_NO,'-','') = REPLACE(D.DCERTI_NO,'-','')		\n");
        	sqlstr.append("AND      D.ADDITEM20 = 'O'               								\n"); //2019.02.26
        	sqlstr.append(" AND     A.BID_NO = E.BID_NO												\n");
        	sqlstr.append(" AND     A.BID_DEG = E.BID_DEG											\n");
        	//2023.01.20 계약이관 변경계약인 경우 입찰업체와 계약업체가 다름(입찰업체의 환율정보를 가져옴)        	
        	//sqlstr.append(" AND     TO_CHAR(C.CORP_ID) = E.VENDOR_CD								\n");
        	sqlstr.append(" AND     E.SUCC_YN = 'Y'													\n");
        	sqlstr.append(" AND		A.SUB_CONT_MOD_ID	= ?											\n");
			search.setStatement(sqlstr.toString());
			search.addParameter(sub_cont_mod_id);
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //WAFLogger.debug(sqlstr.toString());
            //WAFLogger.debug("WAFSQLException : " + se.getMessage());
            System.out.println(sqlstr.toString());
            System.out.println(se.getMessage());
        }finally {
        	if( vo == null ) vo = new ValueObject();
            return vo;
        }
	}

	public RowSet getConfDetail(ValueObject vo){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StrBuffer sqlstr = new StrBuffer();
        
        try {
        	sqlstr.append(" SELECT  '"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY                                                                               \n");
		    sqlstr.append("         ,NVL(E.ERP_SITE_CD, B.DEPT_CD) AS CD_SITE                                                          \n");
		    sqlstr.append("         ,? NO_CONT                                                                                         \n");
		    sqlstr.append("         ,? SQ_CONT                                                                                         \n");
		    sqlstr.append("         ,TO_CHAR(ROW_NUMBER() OVER(ORDER BY A.MATERIAL_CD),'FM00000') AS SEQ_CONT     					\n");
		    sqlstr.append("         ,'01' AS CD_CM00208                                                                                \n");
		    sqlstr.append("         ,'01' AS CD_CM00201                                                                                \n");
		    sqlstr.append("         ,DECODE(?,'01','01','02') AS CD_CM00202                                                           \n");
		    sqlstr.append("         ,'01' AS CD_CM00212                                                                                \n");
		    sqlstr.append("         ,A.SQ_BUDGET                                                                                       \n");
		    sqlstr.append("         ,A.CD_CSTR                                                                                         \n");
		    sqlstr.append("         ,A.DL_CSTR                                                                                         \n");
		    sqlstr.append("         ,A.NM_CSTR                                                                                         \n");
		    sqlstr.append("         ,A.CD_SIZE                                                                                         \n");
		    sqlstr.append("         ,A.SIZE_CSTR                                                                                       \n");
		    sqlstr.append("         ,A.UNIT_CSTR                                                                                       \n");
		    sqlstr.append("         ,A.QTY_ORD                                                                                         \n");
		    sqlstr.append("         ,A.UNT_ORD                                                                                         \n");
		    sqlstr.append("         ,A.AMT_ORD                                                                                         \n");
		    sqlstr.append("         ,A.QTY_ACT                                                                                         \n");
		    sqlstr.append("         ,A.UNT_ACT                                                                                         \n");
		    sqlstr.append("         ,A.AMT_ACT                                                                                         \n");
		    sqlstr.append("         ,A.QTY_TRNS_ACT                                                                                    \n");
		    sqlstr.append("         ,A.UNT_TRNS_ACT                                                                                    \n");
		    sqlstr.append("         ,A.AMT_TRNS_ACT                                                                                    \n");
		    sqlstr.append("         ,1 AS QTY_CONT                                                                        				\n");
		    sqlstr.append("         ,ROUND(A.SUPPLY_AMT * NVL(?,1)) AS UNT_CONT                                                       		\n");
		    sqlstr.append("         ,ROUND(A.SUPPLY_AMT * NVL(?,1)) AS AMT_CONT                                                       		\n");
		    sqlstr.append("         ,DECODE(?,'000',NULL,A.SUPPLY_AMT) AS UNT_CURR                                                     \n");
		    sqlstr.append("         ,DECODE(?,'000',NULL,A.SUPPLY_AMT) AS AMT_CURR                                                     \n");
		    sqlstr.append("         ,(SELECT USER_SABUN FROM PUB0120T WHERE ACCT_ID = B.CRE_BY)  AS ID_INSERT                          \n");
		    sqlstr.append("         ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT                                                 \n");
		    sqlstr.append(" FROM    CONT_OUT_ITEM A                                                                                    \n");
		    sqlstr.append("         ,SUB2000T B                                                                                        \n");
		    sqlstr.append("         ,ETEC_ERPIF.SITE_MAPPING E                                                                         \n");
		    sqlstr.append(" WHERE   A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                                                              \n");
		    sqlstr.append(" AND     B.DEPT_CD = E.EBID_SITE_CD(+)                                                                      \n");
		    sqlstr.append(" AND     A.SUB_CONT_MOD_ID = ?                                                                              \n");
            search.setStatement(sqlstr.toString());
            search.addParameter(vo.get("ERP_NO_CONT"));
            search.addParameter(vo.get("ERP_SQ_CONT"));
            search.addParameter(vo.get("ERP_SQ_CONT"));
            search.addParameter(vo.get("UNT_CURR"));
            search.addParameter(vo.get("UNT_CURR"));
            search.addParameter(vo.get("CURR_SOUR"));
            search.addParameter(vo.get("CURR_SOUR"));
            search.addParameter(vo.get("SUB_CONT_MOD_ID"));
            listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}
	
	private String getErpNoCont(String dept_cd) {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject vo = new ValueObject();
		StrBuffer sqlstr = new StrBuffer();
		String rtnStr = "";
		try{
			sqlstr.append(" SELECT  TO_CHAR(TO_NUMBER(NVL(MAX(NO_CONT),'0')) + 1,'FM000000') AS ERP_NO_CONT	\n");
			sqlstr.append(" FROM    WC_O_CONTRACT_H@ERPIU													\n");
			sqlstr.append(" WHERE   CD_COMPANY = '"+WAFConfig.get("waf.erp.cdCompany")+"'														\n");
			sqlstr.append(" AND     CD_SITE = '"+dept_cd+"'													\n");
			search.setStatement(sqlstr.toString());
			rowSet = search.execute();
			if( rowSet != null && rowSet.size() > 0 ){
				vo = RowSetUtility.getValueObject(rowSet.getRow(0));
				rtnStr = vo.get("ERP_NO_CONT");
			}
		}catch(WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
		}finally {
			return rtnStr;
		}
	}
	
	private String getErpSqCont(String dept_cd, String erp_no_cont) {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject vo = new ValueObject();
		StrBuffer sqlstr = new StrBuffer();
		String rtnStr = "";
		try{
			sqlstr.append(" SELECT  TO_CHAR(TO_NUMBER(NVL(MAX(SQ_CONT),'0')) + 1,'FM00') AS ERP_SQ_CONT	\n");
			sqlstr.append(" FROM    WC_O_CONTRACT_H@ERPIU												\n");
			sqlstr.append(" WHERE   CD_COMPANY = '"+WAFConfig.get("waf.erp.cdCompany")+"'													\n");
			sqlstr.append(" AND     CD_SITE = '"+dept_cd+"'												\n");
			sqlstr.append(" AND     NO_CONT = '"+erp_no_cont+"'											\n");
			search.setStatement(sqlstr.toString());
			rowSet = search.execute();
			if( rowSet != null && rowSet.size() > 0 ){
				vo = RowSetUtility.getValueObject(rowSet.getRow(0));
				rtnStr = vo.get("ERP_SQ_CONT");
			}
		}catch(WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
		}finally {
			return rtnStr;
		}
	}

	public boolean updateSub1000t(Connection connection, ValueObject vo) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append(" UPDATE  SUB1000T																\n");
        	sqlstr.append(" SET     ERP_NO_CONT = ?															\n");
        	sqlstr.append(" 		,ERP_CD_COOP = ?															\n");
        	sqlstr.append(" WHERE   SUB_CONT_ID = (SELECT SUB_CONT_ID FROM SUB2000T WHERE SUB_CONT_MOD_ID = ?)	\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(vo.get("ERP_NO_CONT"));
            persistent.addParameter(vo.get("CD_COOP"));
            persistent.addParameter(vo.get("SUB_CONT_MOD_ID"));
            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updateSub1000t() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }
	
	public boolean updateSub2000t(Connection connection, ValueObject vo) throws Exception {
		
		Persistent persistent = new PersistentImpl(connection);
		StrBuffer sqlstr = new StrBuffer();
		try {
			sqlstr.append(" UPDATE  SUB2000T			\n");
			sqlstr.append(" SET     ERP_NO_CONT = ?		\n");
			sqlstr.append(" 		,ERP_SQ_CONT = ?	\n");
			sqlstr.append(" WHERE   SUB_CONT_MOD_ID = ?	\n");
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(vo.get("ERP_NO_CONT"));
			persistent.addParameter(vo.get("ERP_SQ_CONT"));
			persistent.addParameter(vo.get("SUB_CONT_MOD_ID"));
			persistent.execute();
			
			return true;
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> updateSub2000t() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			//WAFLogger.debug("Exception : " + e.getMessage());
			throw e;
		}
	}

	public void delContItemData(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  CONT_OUT_ITEM						\n");
        	sqlstr.append("WHERE SUB_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	

	private boolean AmtCompare(String subContModId) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        StrBuffer sqlstr = new StrBuffer();
        double NAmt = 0;
        double TAmt = 0;
        String erp_yn = "Y";
        try{
        	sqlstr.append("SELECT ROUND((A.CON_SUPPLY_NAMT - B.SUPPLY_NAMT)*100000,0) NAMT                          \n");
        	sqlstr.append("      ,ROUND((A.CON_SUPPLY_TAMT - B.SUPPLY_TAMT)*100000,0) TAMT	                        \n");
        	sqlstr.append("      ,ERP_CHECK_YN                                                                      \n");
        	sqlstr.append("	FROM SUB2000T A	                                                                        \n");
        	sqlstr.append("	   , (SELECT SUB_CONT_MOD_ID	                                                        \n");
        	sqlstr.append(" 			    ,SUM(NVL(SUPPLY_NAMT,0)) SUPPLY_NAMT 	                                \n");
        	sqlstr.append(" 			    ,SUM(NVL(SUPPLY_TAMT,0)) SUPPLY_TAMT	                                \n");
        	sqlstr.append("			FROM CONT_OUT_ITEM B	                                                        \n");
        	sqlstr.append("	 	   WHERE SUB_CONT_MOD_ID = '" + subContModId + "'	                                \n");
        	sqlstr.append("		   GROUP BY SUB_CONT_MOD_ID ) B	                                                    \n");
        	sqlstr.append("	WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID(+)	                                            \n");
        	sqlstr.append("	  AND A.SUB_CONT_MOD_ID = '" + subContModId + "'                                        \n");
	        
        	System.out.println(sqlstr.toString());
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            	NAmt = valueobject.getDouble(0);
            	TAmt = valueobject.getDouble(1);
            	erp_yn = valueobject.getString(2);
            }
            if (erp_yn.equals("Y")) {
	            if (NAmt == 0 && TAmt == 0  ) return true;
	            else return false;
            }
	        else {
	        	return true;  //관리목적은 내역체크 안함. 계약전송. 
	        }
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se); 
        }
        finally {
        	if( valueobject == null ) return false;
        } 
        return false;
	}
	

	private ValueObject getSuppInfo(String sub_cont_mod_id) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject vo = new ValueObject();
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT  '"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY    /*회사코드*/														\n");
        	sqlstr.append("         ,A.CORP_KOR AS LN_PARTNER    /*거래처명(한)*/												\n");
        	sqlstr.append("         ,A.CORP_KOR AS SN_PARTNER    /*거래처명(약어)*/												\n");
        	sqlstr.append("         ,A.CORP_ENG AS EN_PARTNER    /*거래처명(영)*/												\n");
        	sqlstr.append("         ,'EXT' AS TP_PARTNER    /*거래처타입*/														\n");
        	sqlstr.append("         ,'001' AS FG_PARTNER    /*거래처구분*/														\n");
        	sqlstr.append("         ,B.BOSS_NAME AS NM_CEO    /*대표자명*/														\n");
        	sqlstr.append("         ,DECODE(A.IN_OUT_DIV,'O','9999999999',REPLACE(B.VENDCD,'-','')) AS NO_COMPANY    /*사업자등록번호*/	\n");
        	sqlstr.append("         ,A.CORP_STS AS TP_JOB    /*업태*/															\n");
        	sqlstr.append("         ,A.BIZ_FORM AS CLS_JOB    /*종목*/														\n");
        	sqlstr.append("         ,'102' AS CLS_PARTNER    /*거래처분류*/													\n");
        	sqlstr.append("         ,NVL(D.CHG_CODE10,'000') AS CD_NATION    /*국가코드*/										\n");
        	sqlstr.append("         ,B.EMAIL AS E_MAIL    /*담당자 E-MAIL*/													\n");
        	sqlstr.append("         ,B.TEL_NUM AS NO_TEL    /*담당자전화번호*/													\n");
        	sqlstr.append("         ,A.CORP_TEL AS NO_TEL1    /*본사전화번호*/													\n");
        	sqlstr.append("         ,A.CORP_FAX AS NO_FAX1    /*본사FAX*/														\n");
        	sqlstr.append("         ,B.USER_NAME AS CD_EMP_PARTNER    /*거래처담당자*/											\n");
        	sqlstr.append("         ,B.HP AS NO_HPEMP_PARTNER    /*핸드폰번호*/												\n");
        	sqlstr.append("         ,'N' AS USE_YN    /*사용유무*/																\n");
        	sqlstr.append("         ,(SELECT USER_SABUN FROM PUB0120T WHERE ACCT_ID = B.CRE_BY) AS ID_INSERT    /*추가자*/	\n");
        	sqlstr.append("         ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT    /*등록일*/							\n");
        	sqlstr.append("         ,A.CORP_ID																								\n");
        	sqlstr.append("         ,E.CHG_CODE02 AS TO_MAIL																				\n");
        	sqlstr.append("         ,CASE																									\n");
        	sqlstr.append("         	WHEN 	LENGTH(A.CORP_REG_NO) = 10 																	\n");
        	sqlstr.append("         	THEN	SUBSTR(A.CORP_REG_NO,1,3)||'-'||SUBSTR(A.CORP_REG_NO,4,2)||'-'||SUBSTR(A.CORP_REG_NO,6) 	\n");
        	sqlstr.append("         	ELSE	A.CORP_REG_NO																				\n");
        	sqlstr.append("         END AS CORP_REG_NO																						\n");
        	sqlstr.append("         ,A.IN_OUT_DIV																							\n");
        	sqlstr.append(" FROM    SUPP_INFO A																				\n");
        	sqlstr.append("         ,CONT_OUT_SUPP B																		\n");
        	sqlstr.append("         ,SUB2000T C																				\n");
        	sqlstr.append("         ,(SELECT    DETAILCD, CHG_CODE10														\n");
        	sqlstr.append("         FROM    EBID_COM911T																	\n");
        	sqlstr.append("         WHERE   BASECD = '911') D																\n");
        	sqlstr.append("         ,(SELECT    DETAILCD, CHG_CODE02														\n");
        	sqlstr.append("         FROM    EBID_COM911T																	\n");
        	sqlstr.append("         WHERE   BASECD = 'JOB_DIV') E															\n");
        	sqlstr.append(" WHERE   A.CORP_ID = B.CORP_ID																	\n");
        	sqlstr.append(" AND     B.SUB_CONT_MOD_ID = C.SUB_CONT_MOD_ID													\n");
        	sqlstr.append(" AND     B.SIGN_SEQ = 2																			\n");
        	sqlstr.append(" AND     A.NATION_CD = D.DETAILCD(+)																\n");
        	sqlstr.append(" AND     A.JOB_DIV = E.DETAILCD																	\n");
        	sqlstr.append(" AND     B.SUB_CONT_MOD_ID = ?																	\n");
        	
			search.setStatement(sqlstr.toString());
			search.addParameter(sub_cont_mod_id);
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //WAFLogger.debug(sqlstr.toString());
            //WAFLogger.debug("WAFSQLException : " + se.getMessage());
            System.out.println(sqlstr.toString());
            System.out.println(se.getMessage());
        }finally {
        	if( vo == null ) vo = new ValueObject();
            return vo;
        }
	}
	
	public boolean sendSuppInfoMail(String toMail, String corpRegNo, String corpName, String corpId) throws Exception {
		try {
			MessageSend mailSend = new MessageSend(storage);
			StringBuffer mailContent = new StringBuffer();
			String title = "ERP 협력업체 등록요청";
			
			String fileLink = "http://ebid.sgcenc.co.kr/common/file/FileListNew.screen?file_gb=TERMS_AGREE_INFO_"+corpId+"&is_modify=N";
	      		
      		mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; padding:5px; text-align:left;'>");
      		mailContent.append("</BR>");
      		mailContent.append("전자조달 계약을 위한 ERP 협력업체 등록을 진행해 주십시요.</BR>");
      		mailContent.append("</BR>");
      		mailContent.append("사업자번호: "+corpRegNo+"</BR>");
      		mailContent.append("업체명 : "+corpName+"</BR>");
      		mailContent.append("첨부파일 : <a href='"+fileLink+"' target='_blank'>파일확인</a></BR>");
      		mailContent.append("</font>");
      		
      		mailSend.sendEmail(toMail, "partner@sgc.co.kr", title, title, mailContent, "1");
      		
		    return true;
        }catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> sendSuppInfoMail() : " + e.getMessage());
            WAFLogger.error(e);
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }      
    }
	
	public void updateCdPartner(String corpId, String cdPartner) throws Exception {
        try {
        	conn.begin();
			Persistent pstmt = new PersistentImpl(conn.getConnection());
        	StrBuffer sqlstr = new StrBuffer();
        	sqlstr.append("UPDATE	SUPP_INFO			\n");
        	sqlstr.append("SET		CORP_REG_NO = ?		\n");
        	sqlstr.append("			,UPD_BY = ?			\n");
        	sqlstr.append("			,UPD_DATE = SYSDATE	\n");
        	sqlstr.append("WHERE	CORP_ID = ?			\n");
        			
	       	pstmt.setStatement(sqlstr.toString());
	       	pstmt.addParameter(cdPartner);
	       	pstmt.addParameter(storage.getUser().get("ACCT_ID"));
			pstmt.addParameter(corpId);
			pstmt.execute();
        }catch (Exception e) {
        	WAFLogger.error(this.getClass().getName() + " --> updateCdPartner() : " + e.getMessage());
			WAFLogger.error(e);
			storage.setSuccess(false);
			storage.setDetailMessage(e);
			conn.setRollback();
			throw e;
        }finally {
        	conn.end();
        }
	}

	
	private String getOrgCdCoop(String subContModId) {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject valueobject = null;
		StrBuffer sqlstr = new StrBuffer();
		String cdCoop = "";
		try{
			sqlstr.append("SELECT  ERP_CD_COOP						\n");
			sqlstr.append("FROM    SUB1000T A						\n");
			sqlstr.append("        ,SUB2000T B						\n");
			sqlstr.append("WHERE   A.SUB_CONT_ID = B.SUB_CONT_ID	\n");
			sqlstr.append("AND     B.SUB_CONT_MOD_ID = ?			\n");
			
			search.setStatement(sqlstr.toString());
			search.addParameter(subContModId);
			rowSet = search.execute();
			if( rowSet != null && rowSet.size() > 0 ){
				valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
				cdCoop = valueobject.get("ERP_CD_COOP");
			}
			
		}
		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 cdCoop;
	}

	/**
	 * 변경구분 및 사업자번호 변경여부 조회
	 * @param Params
	 * @return
	 */
	private ValueObject getContChgInfo(String sub_cont_mod_id) {

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

        	sqlstr.append("SELECT A.MOD_GB                                                              \n");
        	sqlstr.append("      ,DECODE(B.CORP_ID, NVL(C.BEF_CORP_ID, B.CORP_ID), 'N', 'Y') AS CORP_CHG_YN \n");
        	sqlstr.append("  FROM SUB2000T A                                                            \n");
        	sqlstr.append("      ,CONT_OUT_SUPP B                                                       \n");
        	sqlstr.append("      ,(                                                                     \n");
        	sqlstr.append("        SELECT  B.SUB_CONT_ID                                                \n");
        	sqlstr.append("               ,C.CORP_ID AS BEF_CORP_ID                                     \n");
        	sqlstr.append("          FROM  SUB2000T A                                                   \n");
        	sqlstr.append("               ,SUB2000T B                                                   \n");
        	sqlstr.append("               ,CONT_OUT_SUPP C                                              \n");
        	sqlstr.append("         WHERE A.SUB_CONT_MOD_ID = ?                                         \n");
        	sqlstr.append("           AND A.SUB_CONT_ID = B.SUB_CONT_ID                                 \n");
        	sqlstr.append("           AND A.MOD_NO - 1 = B.MOD_NO                                       \n");
        	sqlstr.append("           AND B.SUB_CONT_MOD_ID = C.SUB_CONT_MOD_ID                         \n");
        	sqlstr.append("           AND C.CORP_ID != 0                                                \n");
        	sqlstr.append("        ) C                                                                  \n");
        	sqlstr.append(" WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                                 \n");
        	sqlstr.append("   AND B.SIGN_SEQ = 2                                                        \n");
        	sqlstr.append("   AND A.SUB_CONT_ID = C.SUB_CONT_ID(+)                                      \n");
        	sqlstr.append("   AND A.SUB_CONT_MOD_ID = ?                                                 \n");

			search.setStatement(sqlstr.toString());
			search.addParameter(sub_cont_mod_id);
			search.addParameter(sub_cont_mod_id);

            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            
        }catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            throw new Exception(se.getMessage());
        }finally {
        	if( vo == null ) vo = new ValueObject();
            return vo;
        }
	}
	
	/**
	 * 계약 정보 (사업자번호 변경시)
	 * @param Params
	 * @return
	 */
	private ValueObject getConfinfoChg(String sub_cont_mod_id) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject vo = new ValueObject();
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT	A.SUB_CONT_MOD_ID												\n");
        	sqlstr.append(" 		,A.SUB_CONT_ID													\n");
        	sqlstr.append(" 		,F.ERP_NO_CONT													\n");
        	sqlstr.append(" 		,NVL(B.ERP_SITE_CD, A.DEPT_CD) AS DEPT_CD						\n");
        	sqlstr.append(" 		,'"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY											\n");
            sqlstr.append(" 		,A.BUILD_NAME AS NM_CONT											\n");
            sqlstr.append(" 		,TO_CHAR(A.CON_DATE,'YYYYMMDD') AS DT_CONT						\n");
            sqlstr.append(" 		,'01' AS CD_CM00201												\n");
            sqlstr.append(" 		,'01' AS CD_CM00212												\n");
            sqlstr.append(" 		,'03' AS CD_CM00213												\n");
            sqlstr.append(" 		,'01' AS CD_CM00208												\n");
            sqlstr.append(" 		,TO_CHAR(A.START_WORK_DATE,'YYYYMMDD') AS DT_FM_CONT				\n");
            sqlstr.append(" 		,TO_CHAR(A.END_WORK_DATE,'YYYYMMDD') AS DT_TO_CONT				\n");
            sqlstr.append(" 		,D.ERP_CD_COOP AS CD_COOP /*거래처코드*/							\n");
            sqlstr.append(" 		,D.DEALR_NM AS NM_COOP /*거래처명*/								\n");
            sqlstr.append(" 		,'' AS TP_COMPANY /*사업자구분*/									\n");
            sqlstr.append(" 		,REPLACE(C.VENDCD,'-') AS NO_COMPANY /*사업자번호*/           		\n");
            sqlstr.append(" 		,ROUND(NVL(A.CON_SUPPLY_TAMT / DECODE(A.CON_SUPPLY_AMT, 0, NULL, A.CON_SUPPLY_AMT), 0) * 100, 2) AS DRAT_TAX \n");
            sqlstr.append(" 		,100 - ROUND(NVL(A.CON_SUPPLY_TAMT / DECODE(A.CON_SUPPLY_AMT, 0, NULL, A.CON_SUPPLY_AMT), 0) * 100, 2) AS FRAT_TAX \n");
            sqlstr.append(" 		,ROUND(A.CHG_CON_AMT * NVL(I.EXCHANGE_ORD,1)) AS AMT_ORD						\n");
            sqlstr.append(" 		,ROUND(A.CHG_EXE_AMT * NVL(I.EXCHANGE_ORD,1)) AS AMT_ACT						\n");
            sqlstr.append(" 		,ROUND(A.CON_SUPPLY_AMT * NVL(I.EXCHANGE_ORD,1)) AS AMT_CONT				\n");
            sqlstr.append(" 		,ROUND(A.CON_SUPPLY_TAMT * NVL(I.EXCHANGE_ORD,1)) AS SAMT_CONT				\n");
            sqlstr.append(" 		,ROUND(DECODE(A.PROOF_CD,'Z99',0,A.CON_SURTAX_AMT) * NVL(I.EXCHANGE_ORD,1)) AS VAMT_CONT	\n");
            sqlstr.append(" 		,ROUND(A.CON_SUPPLY_NAMT * NVL(I.EXCHANGE_ORD,1)) AS FAMT_CONT				\n");
            sqlstr.append("         ,(SELECT MAX(NO_CORD)                                           \n");
            sqlstr.append("             FROM CONT_OUT_ITEM ITEM                                     \n");
            sqlstr.append("            WHERE ITEM.SUB_CONT_MOD_ID = A.SUB_CONT_MOD_ID) AS NO_CORD   \n");
            sqlstr.append(" 		,'' AS SQ_ESTM /*견적업체순번*/									\n");
            sqlstr.append(" 		,'' AS NM_CONT_ML /*계약명(외국어)*/								\n");
            sqlstr.append(" 		,'' AS DT_DELV /*납기일자*/										\n");
            sqlstr.append(" 		,'' AS LOC_DELV /*납기장소*/										\n");
            sqlstr.append(" 		,(SELECT CHG_CODE10 FROM EBID_COM911T WHERE BASECD = '911' AND DETAILCD = A.CON_CUR_CD) AS CURR_SOUR	\n");
            sqlstr.append(" 		,DECODE(NVL(A.CON_CUR_CD,'KRW'),'KRW',NULL,I.EXCHANGE_ORD) AS UNT_CURR			\n");
            sqlstr.append(" 		,DECODE(NVL(A.CON_CUR_CD,'KRW'),'KRW',NULL,A.CON_SUPPLY_TAMT) AS AMT_CURR	\n");
            sqlstr.append(" 		,DECODE(NVL(A.CON_CUR_CD,'KRW'),'KRW',NULL,DECODE(A.PROOF_CD,'Z99',0,A.CON_SURTAX_AMT)) AS VAMT_CURR	\n");
            sqlstr.append(" 		,DECODE(NVL(A.CON_CUR_CD,'KRW'),'KRW',NULL,A.CON_SUPPLY_NAMT) AS FAMT_CURR	\n");
            sqlstr.append(" 		,REPLACE(A.PROOF_CD,'Z99','') AS CD_PROOF									\n");
            sqlstr.append(" 		,A.PAYMENT_TERMS AS CD_CM00206												\n");
            sqlstr.append(" 		,(SELECT USER_SABUN FROM PUB0120T WHERE ACCT_ID = A.CRE_BY)  AS ID_INSERT	\n");
            sqlstr.append(" 		,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT							\n");
            sqlstr.append(" 		,NVL(A.ERP_CHECK_YN,'Y') AS ERP_CHECK_YN									\n");
            sqlstr.append(" 		,F.ERP_CD_COOP AS ORG_CD_COOP									\n");
            sqlstr.append(" 		,A.ACCT_GB  AS CD_COST_TRADE									\n");  //2019.04.13 분개유형추가
            sqlstr.append(" 		,D.ERP_CD_PARTNER AS ERP_CD_PARTNER								\n");
            sqlstr.append(" 		,J.BEF_ERP_CD_PARTNER AS ORG_ERP_CD_PARTNER						\n");
            sqlstr.append(" FROM	SUB2000T A														\n");
        	sqlstr.append(" 		,ETEC_ERPIF.SITE_MAPPING B										\n");
        	sqlstr.append(" 		,CONT_OUT_SUPP C												\n");
        	sqlstr.append(" 		,ETEC_ERPIF.ACZ10800 D											\n");
        	sqlstr.append(" 		,SUB1000T F														\n");
        	sqlstr.append(" 		,SUPP_INFO G													\n");
        	sqlstr.append("         ,(                                                              \n");
        	sqlstr.append("           SELECT  SUB_CONT_ID                                           \n");
        	sqlstr.append("                  ,B.VENDOR_CD AS VENDOR_CD_ORD                          \n");
        	sqlstr.append("                  ,B.EXCHANGE AS EXCHANGE_ORD                            \n");
        	sqlstr.append("             FROM SUB2000T A, BID_SUPP B, CONT_OUT_SUPP C                \n");
        	sqlstr.append("            WHERE A.MOD_NO = 0                                           \n");
        	sqlstr.append("              AND A.BID_NO = B.BID_NO                                    \n");
        	sqlstr.append("              AND A.BID_DEG = B.BID_DEG                                  \n");
        	sqlstr.append("              AND A.SUB_CONT_MOD_ID  = C.SUB_CONT_MOD_ID                 \n");
        	sqlstr.append("              AND B.VENDOR_CD = TO_CHAR(C.CORP_ID)                       \n");
        	sqlstr.append("         ) I                                                             \n");
        	sqlstr.append("        ,(                                                               \n");
        	sqlstr.append("          SELECT  B.SUB_CONT_ID                                          \n");
        	sqlstr.append("                 ,E.ERP_CD_PARTNER AS BEF_ERP_CD_PARTNER                 \n");
        	sqlstr.append("            FROM  SUB2000T A                                             \n");
        	sqlstr.append("                 ,SUB2000T B                                             \n");
        	sqlstr.append("                 ,CONT_OUT_SUPP C                                        \n");
        	sqlstr.append("                 ,SUPP_INFO D                                            \n");
        	sqlstr.append("                 ,ETEC_ERPIF.ACZ10800 E                                  \n");
        	sqlstr.append("           WHERE A.SUB_CONT_MOD_ID = ?                                   \n");
        	sqlstr.append("             AND A.SUB_CONT_ID = B.SUB_CONT_ID                           \n");
        	sqlstr.append("             AND A.MOD_NO - 1 = B.MOD_NO                                 \n");
        	sqlstr.append("             AND B.SUB_CONT_MOD_ID  = C.SUB_CONT_MOD_ID                  \n");
        	sqlstr.append("             AND C.CORP_ID != 0                                          \n");
        	sqlstr.append("             AND C.CORP_ID = D.CORP_ID                                   \n");
        	sqlstr.append("             AND REPLACE(D.CORP_REG_NO,'-','') = REPLACE(E.DCERTI_NO,'-','') \n");
        	sqlstr.append("             AND E.ADDITEM20 = 'O'                                       \n");
        	sqlstr.append("          ) J                                                            \n");
        	sqlstr.append(" WHERE	A.DEPT_CD = B.EBID_SITE_CD(+)									\n");
        	sqlstr.append(" AND		A.SUB_CONT_MOD_ID  = C.SUB_CONT_MOD_ID							\n");
        	sqlstr.append(" AND		A.SUB_CONT_ID  = F.SUB_CONT_ID									\n");
        	sqlstr.append(" AND		C.CORP_ID != 0													\n");
        	sqlstr.append(" AND		C.CORP_ID = G.CORP_ID											\n");
        	sqlstr.append(" AND     REPLACE(G.CORP_REG_NO,'-','') = REPLACE(D.DCERTI_NO,'-','')		\n");
        	sqlstr.append(" AND     D.ADDITEM20 = 'O'               								\n"); //2019.02.26
        	sqlstr.append(" AND		A.SUB_CONT_ID = I.SUB_CONT_ID(+)								\n");
        	sqlstr.append(" AND		A.SUB_CONT_ID = J.SUB_CONT_ID(+)								\n");
        	sqlstr.append(" AND		A.SUB_CONT_MOD_ID	= ?											\n");
			search.setStatement(sqlstr.toString());
			search.addParameter(sub_cont_mod_id);
			search.addParameter(sub_cont_mod_id);
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //WAFLogger.debug(sqlstr.toString());
            //WAFLogger.debug("WAFSQLException : " + se.getMessage());
            System.out.println(sqlstr.toString());
            System.out.println(se.getMessage());
        }finally {
        	if( vo == null ) vo = new ValueObject();
            return vo;
        }
	}

	public boolean updateSub1000tCoop(Connection connection, ValueObject vo) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append(" UPDATE  SUB1000T																\n");
        	sqlstr.append(" SET     ERP_CD_COOP = ?															\n");
        	sqlstr.append(" WHERE   SUB_CONT_ID = (SELECT SUB_CONT_ID FROM SUB2000T WHERE SUB_CONT_MOD_ID = ?)	\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(vo.get("CD_COOP"));
            persistent.addParameter(vo.get("SUB_CONT_MOD_ID"));
            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updateSub1000t() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }
}
