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


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

import oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.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.code.CommCodeManager;
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 kr.co.udapsoft.ebid.buyer.sycs.user.biz.UserCommon;

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

   
public class ContResultBiz extends Biz {

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

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

		boolean success = false;
		
		if(storage.compareEvent("")){
			RowSet contKindCode = getcontKindCode();
			storage.addRowSet("contKindCode", contKindCode);
		}else if(storage.compareEvent("SHEET_SELECT")){
			params.set("sheet1", getContList(params));
			
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("FORM_SHEET_SELECT")){
			
			params.set("sheet2", getStampList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("FORM_SELECT")){
            user = getContRequestValueObject(params.get("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);
			
			//보증 정보 가져오기
			RowSet warrList = getWarrList(params);
			storage.addRowSet("warrList", warrList);       
			
		}else if(storage.compareEvent("CHANGE_CONT")){		//변경 계약서 작성
			String maxSubContid;
			maxSubContid = getMaxSubCntId();
			
			params.set("maxSubContid", maxSubContid);
			success = addChangeContProc(super.conn.getConnection(), params);	//변경 계약 축
			
			super.storage.setSuccess(success);
			
		}else if(storage.compareEvent("SAVE_PAYITEM")){		//분개유형 저장

			success = updatePayItem(super.conn.getConnection(), params);	//분개유형 저장
			
			super.storage.setSuccess(success);
		}else if(storage.compareEvent("CHG_SUPP_INFO")){		//2017.05.16 완료된계약 업체담당자 정보변경
			success = updateSuppInfo(super.conn.getConnection(), params);	
			super.storage.setSuccess(success);	
		}			
	}
	
	private RowSet getcontKindCode() throws Exception
    {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlStr = new StringBuffer();
        
        
        try
        {           
        	sqlStr.append(" SELECT  TEMPLATE_CD, TEMPLATE_NAME          \n");
        	sqlStr.append("   FROM CONT_TEMPLATE W                      \n");
        	sqlStr.append("  WHERE JOB_DIV = 'O' AND SEQ = '0'          \n");
        	sqlStr.append("  ORDER BY TEMPLATE_CD                       \n");
            
            search.setStatement(sqlStr.toString());
            rowSet = search.execute();
        }
        catch (Exception se) {
        	WAFLogger.error(se);
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            
            se.printStackTrace();
            throw new Exception(se);
        }
        finally {
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
	 /**
     * 저장된 계약서식 조회 
	 * @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 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(" 	     ,A.TEMPLATE_CD \n");
        	sqlstr.append(" 	     ,NVL(B.TEMPLATE_NAME,'이전 계약') AS TEMPLATE_NAME \n");
        	sqlstr.append(" 	     ,C.CHG_CONT_YN \n");
        	sqlstr.append(" 	     ,A.CON_CUR_CD 	\n");
        	sqlstr.append(" 	     ,'1' AS IMG_GB	\n");
        	sqlstr.append(" 	     ,A.SUB_CONT_ID	\n");
//        	sqlstr.append(" 	     ,DECODE(NVL(G.OPTION_YN,'N'),'Y','포함','미포함') AS INSURANCE_YN	\n");
        	sqlstr.append("          ,CASE WHEN G.OPTION_YN = 'Y' AND G.CHECK_YN = 'Y' AND H.STATUS_CD = '10' THEN '완료'        \n");
        	sqlstr.append("                WHEN G.OPTION_YN = 'Y' AND G.CHECK_YN = 'Y' AND H.STATUS_CD = '01' THEN '작성중'       \n");
        	sqlstr.append("                WHEN G.OPTION_YN = 'Y' AND G.CHECK_YN = 'Y' AND H.STATUS_CD = '09' THEN '반려'       \n");
        	sqlstr.append("                WHEN G.OPTION_YN = 'Y' AND G.CHECK_YN = 'Y' AND H.STATUS_CD = '05' THEN '결재중'       \n");
        	sqlstr.append("                ELSE         \n");
        	sqlstr.append("                		CASE WHEN  G.OPTION_YN = 'Y' AND G.CHECK_YN = 'Y' THEN '미신청'      \n");
        	sqlstr.append("               		     ELSE '비대상'       \n");
        	sqlstr.append("                     END       \n");
        	sqlstr.append("           END INSUR_YN       \n");
        	sqlstr.append("          ,CASE WHEN G.OPTION_YN = 'Y' AND G.CHECK_YN = 'Y' THEN '포함'          \n");
        	sqlstr.append("           ELSE '미포함' END  INSURANCE_YN                                                      \n");
        	//2016.5.11 하수급인신청서 추가 
        	sqlstr.append("          ,CASE WHEN I.GUAR_STATUS = '09'  THEN '완료'        \n");
        	sqlstr.append("                ELSE                                         \n");
        	sqlstr.append("                		CASE WHEN D.TEMPLATE_CD != '2' AND A.CON_AMT >= 10000000  THEN '미신청'                  \n");//AND A.MOD_NO = 02016.11.14
        	sqlstr.append("               		     ELSE '비대상'       \n");
        	sqlstr.append("                     END       \n");
        	sqlstr.append("           END INSUR_YN1       \n");
        	sqlstr.append(" 	  FROM SUB2000T A, CONT_OUT_HTML B, CONT_TEMPLATE C, SUB2000T D, CONT_OUT_SUPP F		\n"); //2016.11.14 D추가 
        	sqlstr.append(" 	  	  ,CONT_OUT_HTML G , CONT_OUT_INSUR H	\n");
        	sqlstr.append(" 	  	  ,CONT_OUT_WARR I 	\n");
        	//2023.04.20 계약서 양식 추가(비밀준수약정서)에 따른 로직 수정 : 23년 3월 28일 비밀준수약정서 추가됨        	
        	sqlstr.append(" 	  	  ,(SELECT SUB_CONT_MOD_ID, MOD_NO, CRE_DATE, CASE WHEN TO_CHAR(CRE_DATE,'YYYYMMDD') >= '20230328' THEN 1 ELSE 0 END CONT_GUBUN FROM SUB2000T) J	\n");	//2023.04.20
        	sqlstr.append("   WHERE 1=1					\n");
        	sqlstr.append("   	AND A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID(+)					\n");
        	sqlstr.append("   	AND A.SUB_CONT_ID = D.SUB_CONT_ID(+)					\n");
        	sqlstr.append("   	AND A.TEMPLATE_CD = C.TEMPLATE_CD(+)					\n");
//        	sqlstr.append("   	AND A.SUB_CONT_MOD_ID = G.SUB_CONT_MOD_ID(+)					\n");	//2023.04.20
        	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 B.SEQ(+)=0					\n");
        	sqlstr.append("   	AND C.SEQ(+)=0					\n");
        	sqlstr.append("   	AND D.MOD_NO=0					\n");
        	//2023.04.20 계약서 양식 추가(비밀준수약정서)에 따른 로직 수정 : 23년 3월 28일 비밀준수약정서 추가됨
//        	sqlstr.append("   	AND G.SEQ(+)=DECODE(A.MOD_NO,0,2,1)					\n");				//2023.04.20
        	sqlstr.append("     AND G.SEQ(+) = DECODE (J.CONT_GUBUN,0,DECODE(J.MOD_NO, 0, 2, 1),1,DECODE(J.MOD_NO, 0, 3, 1))		\n");	//2023.04.20
        	sqlstr.append("     AND A.SUB_CONT_MOD_ID = J.SUB_CONT_MOD_ID															\n");	//2023.04.20
        	sqlstr.append("     AND J.SUB_CONT_MOD_ID = G.SUB_CONT_MOD_ID(+)														\n");	//2023.04.20
        	sqlstr.append("   	AND I.WARR_GUBUN(+)=2					\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"));
        	}

        	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");
        	if(!param.get("cont_Kind_Code").isEmpty())
        		sqlstr.append("   AND A.TEMPLATE_CD = '"+param.get("cont_Kind_Code") + "'		\n");
        	
        	sqlstr.append("   ORDER BY NVL(F.SIGN_DATE, A.CON_DATE) DESC , A.SUB_CONT_MOD_ID DESC	\n");
			search.setStatement(sqlstr.toString());
			System.out.println(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.SUB_CONT_ID	\n"); //2016.8.22 분개유형 저장오류. sub_cont_id추가 
	        	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(" 	     , TO_CHAR(A.CON_DATE, 'YYYY-MM-DD') AS CON_DATE	\n");				//수정 (2014-09-19)
	        	sqlstr.append(" 	     , A.DEALER_NM	\n");	
	        	sqlstr.append(" 	     , A.CON_AMT	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE, 'YYYY-MM-DD') AS START_WORK_DATE	\n");				//수정 (2014-09-19)
	        	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(" 	     , TO_CHAR(A.END_WORK_DATE, 'YYYY-MM-DD') AS END_WORK_DATE	\n");				//수정 (2014-09-19)
	        	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(" 	     , F.CHG_CODE01 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.STAMP_DUTY_GB	\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.CON_GUAR_TERM 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.DEF_EST_GUAR_RATE 	\n");	
	        	sqlstr.append(" 	     , A.DEF_GUAR_AMT 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.DEF_EST_TERM 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.DELAY_RATE 	\n");	
	        	sqlstr.append(" 	     , A.PAY_COND_GB 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , F_GET_CODE_NAME('SUB_ACMP_PAY_TYPE', A.PAY_COND_GB) PAY_COND_GB_NM 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.PAY_CONTENT 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.CON_SPE_TXT 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.LABOR_GUAR_JOIN_AMT_GB 	\n");			//추가 (2014-09-19)
	        	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(" 	     , 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(" 	     , A.CON_CLS_YN 	\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(" 	     , A.REQ_USERID 	\n");	
	        	sqlstr.append(" 	     , A.REQ_USERNM	\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(" 	     , A.DEPT_CD AS R_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.CON_CONFIRM_ID 	\n");	
	        	sqlstr.append(" 	     , A.CON_CONFIRM_NAME 	\n");	
	        	
	        	sqlstr.append(" 	     , (SELECT MAX(MOD_NO) FROM SUB2000T WHERE SUB_CONT_ID=A.SUB_CONT_ID) AS MAX_MOD_NO	\n");		//변경 계약을 위해 차수 Max 값을 조회 한다.        	
	        	
	        	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");	        	
	        	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");
	        	//2019.11.25 추가 시작
	        	sqlstr.append(" 	     , A.IN_SPE_INFO	\n");
	        	sqlstr.append(" 	     , A.IN_CHG_INFO	\n");
	        	sqlstr.append(" 	     , A.FRST_CON_AMT	\n");
	        	sqlstr.append(" 	     , A.FRST_EXE_AMT	\n");
	        	sqlstr.append(" 	     , A.CHG_CON_AMT	\n");
	        	sqlstr.append(" 	     , A.CHG_EXE_AMT	\n");
	        	//2019.11.25 추가 끝
				sqlstr.append("  FROM SUB2000T A, BID_MASTER B, HEAD_OFFICE_INFO C, CONT_OUT_SUPP D, CONT_OUT_SUPP E, EBID_COM911T F	\n");
				sqlstr.append("  WHERE A.BID_NO = B.BID_NO(+) 	\n");
				sqlstr.append("  AND A.BID_DEG = B.BID_DEG(+)	\n");
				sqlstr.append("  AND A.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.CON_CUR_CD = F.DETAILCD(+)	\n");
				sqlstr.append("  AND F.BASECD(+) = '911'	\n");
				sqlstr.append("  AND A.SUB_CONT_MOD_ID = '"+SUB_CONT_MOD_ID+"'							\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 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{
        	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");

			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;
	}	
	
    /**
     * 새로운(Next) 계약 SUB_CONT_MOD_ID의 MAX번호를 리턴한다.
	 * @param rightID
	 * @return
	 */
	private String getMaxSubCntId() {
        
        Search search = new SearchImpl();
        String sqlstr = "";
        try {
            sqlstr 	= 	"SELECT SUB2000T_S.NEXTVAL AS MAX_SUB_CONT_MOD_ID \n"
            		+	"  FROM DUAL\n";

            search.setStatement(sqlstr);
            return search.execute().getRow(0).get("MAX_SUB_CONT_MOD_ID");
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getNextPgmGrSeq() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
    }

	/**
     * 변경 계약 정보 추가
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean addChangeContProc(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);

        try {
        	conn.begin();

        	addChangeContSub(super.conn.getConnection(), params);	//변경 계약 축
        	
			addChangeContSupport(super.conn.getConnection(), params);	//변경 계약 축
			
//			orgContChgStatus(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 addChangeContSub(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
			sqlstr.append("INSERT INTO SUB2000T 							\n");
			sqlstr.append("       ( 	SUB_CONT_MOD_ID		\n");
			sqlstr.append("         ,	CON_DOC_NO		\n");
			sqlstr.append("         ,	MOD_NO		\n");
			sqlstr.append("         ,	SUB_CONT_ID		\n");
			sqlstr.append("         ,	MOD_GB		\n");
			sqlstr.append("         ,	MOD_CON_RSN		\n");
			sqlstr.append("         ,	MOD_REQ_DATE		\n");
			sqlstr.append("         ,	MOD_CORP_RSN		\n");
			sqlstr.append("         ,	DEPT_CD		\n");
			sqlstr.append("         ,	DEPT_NAME		\n");
			sqlstr.append("         ,	CON_GB		\n");
			sqlstr.append("         ,	DEALER_NM		\n");
			sqlstr.append("         ,	BUILD_NAME		\n");
			sqlstr.append("         ,	UND_CON_NAME		\n");
			sqlstr.append("         ,	SUB_UND_CON_NAME		\n");
			sqlstr.append("         ,	ELEC_CON_YN		\n");
			sqlstr.append("         ,	REQ_DEPT_CD		\n");
			sqlstr.append("         ,	REQ_DATE		\n");
			sqlstr.append("         ,	REQ_USERID		\n");
			sqlstr.append("         ,	REQ_USERNM		\n");
			sqlstr.append("         ,	REQ_DOC_NO		\n");
			sqlstr.append("         ,	CST_DATE		\n");
			sqlstr.append("         ,	CON_IN_DATE		\n");
			sqlstr.append("         ,	CON_DATE		\n");
			sqlstr.append("         ,	PAY_COND_GB		\n");
			sqlstr.append("         ,	PAY_CONTENT		\n");
			sqlstr.append("         ,	START_WORK_DATE		\n");
			sqlstr.append("         ,	END_WORK_DATE		\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("         ,	LABOR_GUAR_JOIN_AMT_GB		\n");
			sqlstr.append("         ,	CON_SPE_TXT		\n");
			sqlstr.append("         ,	ADD_FILE_KIND		\n");
			sqlstr.append("         ,	CON_CHARGE_ID		\n");
			sqlstr.append("         ,	CON_CHARGE_NAME		\n");
			sqlstr.append("         ,	CORP_SIGN_DATE		\n");
			sqlstr.append("         ,	COMPANY_SIGN_DATE		\n");
			sqlstr.append("         ,	PAY_GROUP		\n");
			sqlstr.append("         ,	PAYMENT_TERMS		\n");
			sqlstr.append("         ,	ACCT_GB		\n");
			sqlstr.append("         ,	PROOF_CD		\n");
			sqlstr.append("         ,	CON_CUR_CD		\n");
			sqlstr.append("         ,	CON_EXCHANGE_DATE		\n");
			sqlstr.append("         ,	CON_WON_RATE		\n");
			sqlstr.append("         ,	PAY_ITEM		\n");
			sqlstr.append("         ,	COST_CODE		\n");
			sqlstr.append("         ,	COST_TYPE		\n");
			sqlstr.append("         ,	WORK_KIND_CD		\n");
			sqlstr.append("         ,	CONTRACTID		\n");
			sqlstr.append("         ,	G_ID		\n");
			sqlstr.append("         ,	P_ID		\n");
			sqlstr.append("         ,	CON_CLS_YN		\n");
			sqlstr.append("         ,	TEMPLATE_CD		\n");
			sqlstr.append("         ,	CONT_HASH		\n");
			sqlstr.append("         ,	TRUE_RANDOM		\n");
			sqlstr.append("         ,	STATUS_CD		\n");
			sqlstr.append("         ,	BID_NO		\n");
			sqlstr.append("         ,	BID_DEG		\n");
			sqlstr.append("         ,	MONTH_ACMP_CNT		\n");
			sqlstr.append("         ,	OBJ_RCPT_DAY		\n");
			sqlstr.append("         ,	MODI_REQ_REASON		\n");
			sqlstr.append("         ,	MODI_REQ_DT		\n");
			sqlstr.append("         ,	CRE_BY		\n");
			sqlstr.append("         ,	CRE_DATE) 								\n");
			sqlstr.append("SELECT ?	\n");
			sqlstr.append("         ,	CON_DOC_NO		\n");
			sqlstr.append("         ,	MOD_NO + 1		\n");
			sqlstr.append("         ,	SUB_CONT_ID		\n");
			sqlstr.append("         ,	NULL		\n");
			sqlstr.append("         ,	NULL		\n");
			sqlstr.append("         ,	NULL		\n");
			sqlstr.append("         ,	NULL		\n");
			sqlstr.append("         ,	DEPT_CD		\n");
			sqlstr.append("         ,	DEPT_NAME		\n");
			sqlstr.append("         ,	CON_GB		\n");
			sqlstr.append("         ,	DEALER_NM		\n");
			sqlstr.append("         ,	BUILD_NAME		\n");
			sqlstr.append("         ,	UND_CON_NAME		\n");
			sqlstr.append("         ,	SUB_UND_CON_NAME		\n");
			sqlstr.append("         ,	ELEC_CON_YN		\n");
			sqlstr.append("         ,	REQ_DEPT_CD		\n");
			sqlstr.append("         ,	REQ_DATE		\n");
			sqlstr.append("         ,	REQ_USERID		\n");
			sqlstr.append("         ,	REQ_USERNM		\n");
			sqlstr.append("         ,	REQ_DOC_NO		\n");
			sqlstr.append("         ,	CST_DATE		\n");
			sqlstr.append("         ,	CON_IN_DATE		\n");
			sqlstr.append("         ,	CON_DATE		\n");
			sqlstr.append("         ,	PAY_COND_GB		\n");
			sqlstr.append("         ,	PAY_CONTENT		\n");
			sqlstr.append("         ,	START_WORK_DATE		\n");
			sqlstr.append("         ,	END_WORK_DATE		\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("         ,	LABOR_GUAR_JOIN_AMT_GB		\n");
			sqlstr.append("         ,	CON_SPE_TXT		\n");
			sqlstr.append("         ,	ADD_FILE_KIND		\n");
			sqlstr.append("         ,	CON_CHARGE_ID		\n");
			sqlstr.append("         ,	CON_CHARGE_NAME		\n");
			sqlstr.append("         ,	CORP_SIGN_DATE		\n");
			sqlstr.append("         ,	COMPANY_SIGN_DATE		\n");
			sqlstr.append("         ,	PAY_GROUP		\n");
			sqlstr.append("         ,	PAYMENT_TERMS		\n");
			sqlstr.append("         ,	ACCT_GB		\n");
			sqlstr.append("         ,	PROOF_CD		\n");
			sqlstr.append("         ,	CON_CUR_CD		\n");
			sqlstr.append("         ,	CON_EXCHANGE_DATE		\n");
			sqlstr.append("         ,	CON_WON_RATE		\n");
			sqlstr.append("         ,	PAY_ITEM		\n");
			sqlstr.append("         ,	COST_CODE		\n");
			sqlstr.append("         ,	COST_TYPE		\n");
			sqlstr.append("         ,	WORK_KIND_CD		\n");
			sqlstr.append("         ,	CONTRACTID		\n");
			sqlstr.append("         ,	G_ID		\n");
			sqlstr.append("         ,	P_ID		\n");
			sqlstr.append("         ,	1		\n");
			sqlstr.append("         ,	NULL		\n");
			sqlstr.append("         ,	NULL		\n");
			sqlstr.append("         ,	NULL		\n");
			sqlstr.append("         ,	'00'		\n");
			sqlstr.append("         ,	BID_NO		\n");
			sqlstr.append("         ,	BID_DEG		\n");
			sqlstr.append("         ,	MONTH_ACMP_CNT		\n");
			sqlstr.append("         ,	OBJ_RCPT_DAY		\n");
			sqlstr.append("         ,	MODI_REQ_REASON		\n");
			sqlstr.append("         ,	MODI_REQ_DT		\n");
			sqlstr.append("         ,	?		\n");
			sqlstr.append("         ,	SYSDATE				\n");
			sqlstr.append("  FROM SUB2000T 				\n");
			sqlstr.append(" WHERE SUB_CONT_MOD_ID= ?									\n");
					
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(valueObject.get("maxSubContid"));
			persistent.addParameter(storage.getUser().get("acct_id"));
			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 addChangeContSupport(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
			sqlstr.append("INSERT INTO CONT_OUT_SUPP 							\n");
			sqlstr.append("       ( 	SUB_CONT_MOD_ID		\n");
			sqlstr.append("         ,	CORP_ID		\n");
			sqlstr.append("         ,	SIGN_SEQ		\n");
			sqlstr.append("         ,	VENDCD		\n");
			sqlstr.append("         ,	MEMBER_NAME		\n");
			sqlstr.append("         ,	BOSS_NAME		\n");
			sqlstr.append("         ,	POST_CODE		\n");
			sqlstr.append("         ,	ADDRESS		\n");
			sqlstr.append("         ,	TEL_NUM		\n");
			sqlstr.append("         ,	MEMBER_SLNO		\n");
			sqlstr.append("         ,	USER_NAME		\n");
			sqlstr.append("         ,	DIVISION		\n");
			sqlstr.append("         ,	POSITION		\n");
			sqlstr.append("         ,	HP		\n");
			sqlstr.append("         ,	EMAIL		\n");
			sqlstr.append("         ,	SIGN_DATE		\n");
			sqlstr.append("         ,	SIGN_DN		\n");
			sqlstr.append("         ,	SIGN_DATA		\n");
			sqlstr.append("         ,	CRE_BY		\n");
			sqlstr.append("         ,	CRE_DATE	)	\n");

			sqlstr.append("SELECT ?	\n");
			sqlstr.append("         ,	A.CORP_ID		\n");
			sqlstr.append("         ,	A.SIGN_SEQ		\n");
			sqlstr.append("         ,	A.VENDCD		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(B.CORP_KOR, A.MEMBER_NAME), A.MEMBER_NAME) AS MEMBER_NAME		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(B.REP_NAME, A.BOSS_NAME), A.BOSS_NAME) AS BOSS_NAME		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(B.POST_NO, A.POST_CODE), A.POST_CODE) AS POST_CODE		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(B.ADDRESS, A.ADDRESS), A.ADDRESS) AS ADDRESS		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(B.CORP_TEL, A.TEL_NUM), A.TEL_NUM) AS TEL_NUM		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(B.COPR_NO, A.MEMBER_SLNO), A.MEMBER_SLNO) AS MEMBER_SLNO		\n");
			sqlstr.append("         ,	A.USER_NAME		\n");
			sqlstr.append("         ,	A.DIVISION		\n");
			sqlstr.append("         ,	A.POSITION		\n");
			sqlstr.append("         ,	A.HP		\n");
			sqlstr.append("         ,	A.EMAIL		\n");
			sqlstr.append("         ,	NULL		\n");
			sqlstr.append("         ,	NULL		\n");
			sqlstr.append("         ,	NULL		\n");
			sqlstr.append("         ,	?		\n");
			sqlstr.append("         ,	SYSDATE				\n");
			sqlstr.append("  FROM CONT_OUT_SUPP A, SUPP_INFO B   				\n");
			sqlstr.append(" WHERE  A.CORP_ID = B.CORP_ID(+)						\n");
			sqlstr.append(" AND SUB_CONT_MOD_ID = ?								\n");
						
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(valueObject.get("maxSubContid"));
			persistent.addParameter(storage.getUser().get("acct_id"));
			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 orgContChgStatus(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("UPDATE SUB2000T SET  	\n");
        	sqlstr.append(" CON_CLS_YN = 2					\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() + " --> orgContChgStatus() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }		
	/**
     * 분개 유형 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean updatePayItem(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        Persistent persistent2 = new PersistentImpl(connection);
        Persistent persistent3 = new PersistentImpl(connection);
        Persistent persistent4 = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        StrBuffer sqlstr2 = new StrBuffer();
        StrBuffer sqlstr3 = new StrBuffer();
        StrBuffer sqlstr4 = new StrBuffer();
        try {
        	sqlstr.append("UPDATE SUB2000T SET  	\n");
        	sqlstr.append(" 	  ACCT_GB  =  '"+ valueObject.get("ACCT_GB") +"' 	\n");	
        	sqlstr.append(" 	 , PAY_GROUP  =  '"+ valueObject.get("PAY_GROUP") +"' 	\n");	
        	sqlstr.append(" 	 , PROOF_CD  =  '"+ valueObject.get("PROOF_CD") +"' 	\n");	
        	sqlstr.append(" 	 , PAYMENT_TERMS  =  '"+ valueObject.get("PAYMENT_TERMS") +"' 	\n");	
        	sqlstr.append(" 	 , PAY_ITEM  =  '"+ valueObject.get("PAY_ITEM") +"' 	\n");	
        	sqlstr.append(" 	 , COST_CODE  =  '"+ valueObject.get("COST_CODE") +"' 	\n");	
        	sqlstr.append(" 	 , COST_TYPE  =  '"+ valueObject.get("COST_TYPE") +"' 	\n");	
        	sqlstr.append(" 	 , SUPP_DIRECT_YN  =  '"+ valueObject.get("SUPP_DIRECT_YN") +"' 	\n");
        	sqlstr.append(" 	 , LABOR_USE_YN  =  '"+ valueObject.get("LABOR_USE_YN") +"' 	\n");
        	if(!valueObject.get("REQ_USERNM").equals("") && storage.getUser().get("ACCT_TYPE_CODE").equals("ADMIN")){
        		sqlstr.append(" 	 , REQ_USERID  =  '"+ valueObject.get("REQ_USERID") +"' 	\n");	
        		sqlstr.append(" 	 , REQ_USERNM  =  '"+ valueObject.get("REQ_USERNM") +"' 	\n");	
        	}
        	sqlstr.append("WHERE SUB_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());  
            persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
            persistent.execute();

            sqlstr2.append("UPDATE SUB1000T SET  	\n");
            sqlstr2.append(" 	  ACCT_GB  =  '"+ valueObject.get("ACCT_GB") +"' 	\n");	
            sqlstr2.append(" 	 , PAY_GROUP  =  '"+ valueObject.get("PAY_GROUP") +"' 	\n");	
            sqlstr2.append(" 	 , PROOF_CD  =  '"+ valueObject.get("PROOF_CD") +"' 	\n");	
            sqlstr2.append(" 	 , PAYMENT_TERMS  =  '"+ valueObject.get("PAYMENT_TERMS") +"' 	\n");	
            sqlstr2.append(" 	 , PAY_ITEM  =  '"+ valueObject.get("PAY_ITEM") +"' 	\n");	
            sqlstr2.append(" 	 , COST_CODE  =  '"+ valueObject.get("COST_CODE") +"' 	\n");	
            sqlstr2.append(" 	 , COST_TYPE  =  '"+ valueObject.get("COST_TYPE") +"' 	\n");
            sqlstr2.append(" 	 , SUPP_DIRECT_YN  =  '"+ valueObject.get("SUPP_DIRECT_YN") +"' 	\n");
            sqlstr2.append(" 	 , LABOR_USE_YN  =  '"+ valueObject.get("LABOR_USE_YN") +"' 	\n");
        	if(!valueObject.get("REQ_USERNM").equals("") && storage.getUser().get("ACCT_TYPE_CODE").equals("ADMIN")){
        		sqlstr2.append(" 	 , REQ_USERID  =  '"+ valueObject.get("REQ_USERID") +"' 	\n");	
        		sqlstr2.append(" 	 , REQ_USERNM  =  '"+ valueObject.get("REQ_USERNM") +"' 	\n");	
        	}
            sqlstr2.append("WHERE SUB_CONT_ID = ?						\n");
            persistent2.setStatement(sqlstr2.toString());  
            persistent2.addParameter(valueObject.get("SUB_CONT_ID"));
            persistent2.execute();

            
          //2019.05.16 ERP계약자료 업데이트
            WAFConnectionImpl erpConn = new WAFConnectionImpl();
			try {
				erpConn.setDataSource("jdbc/erpiu");
				erpConn.begin();
				InterfaceOutERPiU.updateErpContPayment(erpConn.getConnection(),valueObject);
			}catch(Exception e) {
				super.conn.setRollback();
				erpConn.setRollback();
				throw e;
			}finally {
				erpConn.close();
			}
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updatePayItem() : " + 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, '0', '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 
	 * @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;
		}
	}
	
	/**
     * 완료된계약 업체담당자 정보변경 2017.03.08
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean updateSuppInfo(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        
        try { 
        	sqlstr.append("UPDATE CONT_OUT_SUPP                              	        \n");
        	sqlstr.append("   SET USER_NAME =  '"+ valueObject.get("C_USER_NAME") +"' 	\n");	
        	sqlstr.append("      ,POSITION  =  '"+ valueObject.get("C_POSITION") +"' 	\n");	
        	sqlstr.append("      ,HP        =  '"+ valueObject.get("C_HP") +"'     	    \n");	
        	sqlstr.append(" 	 ,EMAIL     =  '"+ valueObject.get("C_EMAIL") +"' 	    \n");	
        	sqlstr.append(" 	 ,TEL_NUM   =  '"+ valueObject.get("C_TEL_NUM") +"'     \n");
        	sqlstr.append(" WHERE SUB_CONT_MOD_ID = ?		 				            \n");
        	sqlstr.append("   AND SIGN_SEQ = 2   						                \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() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	
}