/**
 * @(#)file    ContTargetBiz.java
 * @(#)author  Kim Myung Ho
 * @(#)version 1.0
 * @(#)date    2014-07-04
 * @(#)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 java.util.Random;

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.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.StringUtil;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.common.util.PDFMaker;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import oracle.toplink.internal.ox.StrBuffer;

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

   
public class ContTargetBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
        //WAFLogger.debug("BidIngListBiz :  ~" + storage.getUser());
		//WAFLogger.debug("Outside event : #" + storage.getEvent());
		//WAFLogger.debug("Outside params : " + params);
		boolean success = false;
		
		if(storage.compareEvent("")){
			RowSet rowSet = getTemplateList_searchCombo(params.get("ChgCont"));
			//시트의 ComboBox데이터 설정 [상태]
			storage.addRowSet("TEMPLATE_LIST", rowSet);
			
		}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);

            RowSet template = getTemplateInfo(params.get("TEMPLATE_CD"));
            storage.addRowSet("template", template);
            
            //변경계약일경우 이전 차수에서 데이터를 가져온다.
            if(params.get("ChgCont").equals("Y")){
            	String oldContModId = getOldContNum(user);
            	if(!oldContModId.isEmpty()){
                    storage.addValueObject("OldContInfo", getContRequestValueObject(oldContModId));            		
            	}
            }            

		}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("FORM_DEL")){
			//계약 정보 삭제
			success = deleteContProc(super.conn.getConnection(), params);
			
			super.storage.setSuccess(success);
		}else if(storage.compareEvent("FORM_DELETE")){
			//계약 정보 삭제
			deleteContFile(params);
		}
	}

	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;
	}	
	
	/*계약서 진위확인문자열을 구한다.*/
	public String getTrueRamdom(){
		Random r = new Random();
		int str =  r.nextInt(99999);
		return StringUtils.rightPad(str+"",5,"0");		
	}
	
	 /**
     * 계약서식 조회 combo 조회
	 * @param 
	 * @param 
	 * @return RowSet 
	 */
	public RowSet getTemplateInfo(String template_cd){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT            							\n"
            		+ "   TEMPLATE_CD,                              \n"
					+ "	  SEQ,                                      \n"
					+ "	  JOB_DIV,                                  \n"
					+ "	  TEMPLATE_NAME,                            \n"
					+ "	  TEMPLATE_HTML,                            \n"
					+ "	  TEMPLATE_STYLE,                           \n"
					+ "	  CHG_CONT_YN,                              \n"
					+ "	  OPTION_YN,                                \n"
					+ "	  DEFAULT_YN                                \n"
                    + " FROM CONT_TEMPLATE      					\n"
                    //+ " WHERE JOB_DIV = 'O'       					\n"
                    //+ "   AND TEMPLATE_CD =  '"+template_cd+"'		\n"
                    + " WHERE TEMPLATE_CD =  '"+template_cd+"'		\n"
		            + " ORDER BY TEMPLATE_CD 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 getTemplateList_searchCombo(String ChgContYN){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT TEMPLATE_CD, TEMPLATE_NAME           \n"
                    + " FROM CONT_TEMPLATE      					\n"
                    + " WHERE JOB_DIV IN ('O')       					\n"
                    + "   AND SEQ = '0'        						\n"
                    + "   AND NVL(CHG_CONT_YN,'N') = '"+ ChgContYN +"'       	\n"
		            + " ORDER BY TEMPLATE_CD 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.CORP_ID	\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(" 	     ,DECODE(A.CON_CUR_CD, 'KRW', DECODE(A.MOD_NO,0,DECODE(A.CON_GB,'20','1','30','2'),5), DECODE(A.MOD_NO,0,7,8)) AS TEMPLATE_CD \n");
        	sqlstr.append(" 	     ,DECODE(DECODE(A.CON_CUR_CD, 'KRW', DECODE(A.MOD_NO,0,DECODE(A.CON_GB,'20','1','30','2'),5), DECODE(A.MOD_NO,0,7,8)), NULL, 0, 1) AS BTN 	\n");
        	sqlstr.append(" 	     ,A.CON_CUR_CD 	\n");
        	sqlstr.append(" 	  FROM SUB2000T A, CONT_OUT_SUPP F	\n");
        	sqlstr.append("   WHERE 1=1					\n");
    		sqlstr.append("   	AND A.SUB_CONT_MOD_ID = F.SUB_CONT_MOD_ID(+)				\n");
    		sqlstr.append("   	AND F.SIGN_SEQ(+) = 2				\n");
        	if(params.get("ChgCont").equals("Y")){	//변경계약 대상
        		sqlstr.append("   	AND MOD_NO>0					\n");
        	}else{
        		sqlstr.append("   	AND MOD_NO=0					\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 = '"+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);
        }
        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");
	        	sqlstr.append(" 	     , A.CON_DOC_NO	\n");
	        	sqlstr.append(" 	     , A.MOD_NO AS 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(" 	     , NVL(A.PREPAY_AMT,0) AS PREPAY_AMT 	\n");	
	        	sqlstr.append(" 	     , NVL(A.PREPAY_RATE,0) AS PREPAY_RATE 	\n");	
	        	sqlstr.append(" 	     , NVL(A.PREPAY_DAY,15) AS PREPAY_DAY	\n");
	        	sqlstr.append(" 	     , NVL(A.PREPAY_DAY2,15) AS PREPAY_DAY2	\n");	
	        	sqlstr.append(" 	     , NVL(A.PAY_WAY_BILL,0) AS PAY_WAY_BILL 	\n");	
	        	sqlstr.append(" 	     , NVL(A.PAY_WAY_CASH,0) AS 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(" 	     , NVL(A.PREPAY_GUAR_AMT,0) AS PREPAY_GUAR_AMT 	\n");	//추가 (2014-10-15)
	        	sqlstr.append(" 	     , NVL(A.PREPAY_GUAR_AMT_RATE,0) AS PREPAY_GUAR_AMT_RATE 	\n");	//추가 (2014-10-15)
	        	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(" 	     , NVL(A.DEF_EST_GUAR_RATE,0) AS DEF_EST_GUAR_RATE 	\n");	
	        	sqlstr.append(" 	     , A.DEF_GUAR_AMT 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , NVL(A.DEF_EST_TERM,0) AS 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(" 	     , NVL(A.MONTH_ACMP_CNT,1) AS MONTH_ACMP_CNT 	\n");	
	        	sqlstr.append(" 	     , NVL(A.PAY_CON_RATE,0) AS PAY_CON_RATE 	\n");	
	        	sqlstr.append(" 	     , A.OBJ_RCPT_DAY 	\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	\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(" 	     , NVL(A.PROOF_CD,'21') AS 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(" 	     , A.CON_CHARGE_ID 	\n");	
	        	sqlstr.append(" 	     , A.CON_CHARGE_NAME 	\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.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(" 	     , D.SIGN_DATE AS O_SIGN_DATE	\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.EMAIL AS C_EMAIL	\n");	
	        	sqlstr.append(" 	     , E.SIGN_DATE AS C_SIGN_DATE	\n");	
	        	sqlstr.append(" 	     , A.OBC_MID_BILL_DATE AS OBC_MID_BILL_DATE	\n");	
	        	sqlstr.append(" 	     , NVL(A.OBC_MID_REQ_MONTH,1) AS OBC_MID_REQ_MONTH	\n");	
	        	sqlstr.append(" 	     , F.MIN_CONT_SUPPLY_TAMT AS MIN_CONT_SUPPLY_TAMT	\n");	
	        	sqlstr.append(" 	     , F.MIN_CONT_SUPPLY_NAMT AS MIN_CONT_SUPPLY_NAMT	\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");	
	        	sqlstr.append(" 	     , G.BEF_ETC, G.AFT_ETC       	\n");	//2016.12.9
				sqlstr.append("  FROM SUB2000T A, BID_MASTER B, HEAD_OFFICE_INFO C, CONT_OUT_SUPP D, CONT_OUT_SUPP E	\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.03.03 변경계약 최소금액 수정
				//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("   	, SUB3000T G    	\n");    //2016.12.9
				sqlstr.append("   	, SUB2000T 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 A.SUB_CONT_ID = H.SUB_CONT_ID(+)        	    \n"); //2016.12.9
				sqlstr.append("  AND A.MOD_NO -1 = H.MOD_NO(+)                	    \n");
				sqlstr.append("  AND H.SUB_CONT_MOD_ID = G.SUB_CONT_MOD_ID(+)        	    \n");
				
				sqlstr.append("  AND A.SUB_CONT_MOD_ID = '"+SUB_CONT_MOD_ID+"'							\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));
            }
            	
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //WAFLogger.error(sqlstr.toString());
        }
        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();
			params.set("TRUE_RANDOM", getTrueRamdom());
			ArrayList pdfIfno = new ArrayList();
			
			if( !("localhost").equals(storage.getRequest().getServerName()) ) {
				pdfIfno=makeContPdf(params);		// pdf파일 생성
				
				if (pdfIfno.isEmpty()){
					super.storage.setMessage("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 +"|" ;
				}
			}
			//첨부파일 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.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);
            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 {
        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);
            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_DAY  = ? \n");		// 선급금일정조건1
        	sqlstr.append("      , PREPAY_DAY2  = ? \n");		// 선급금일정조건2
        	sqlstr.append("      , PAY_CON_RATE  =  ? \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("      , TRUE_RANDOM  =  ? \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");	//노무비닷컴 사용여부 추가(2022.03.21 최훈석)

        	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("      , OBC_MID_REQ_MONTH  =  ? 		\n");
			sqlstr.append("      , OBC_MID_BILL_DATE  =  ? 		\n");
			
			sqlstr.append("      , CON_CHARGE_ID  =  ? 		\n");
			sqlstr.append("      , CON_CHARGE_NAME  =  ? 		\n");
			
			sqlstr.append("      , ERP_CHECK_YN  =  ? 		\n");
			
        	
        	sqlstr.append(" 	 , CRE_BY  =  ?  	\n");	
        	sqlstr.append(" 	 , CRE_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_day"));		// 선급금일정조건1
        	persistent.addParameter(valueObject.get("prepay_day2"));	// 선급금일정조건2
        	persistent.addParameter(valueObject.get("pay_con_rate"));		// 하도급지급기준율        	
        	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("TRUE_RANDOM"));		// 지체상급율
        	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("OBC_MID_REQ_MONTH"));
        	persistent.addParameter(valueObject.get("OBC_MID_BILL_DATE"));
        	
        	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);
            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")};
		}
		//WAFLogger.debug("PDF_FILE_CNT : " + params.getInt("PDF_FILE_CNT"));
		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);
            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);
            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);
            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);
            throw e;
        }
    }		
	
	
	
	/**
     * 계약 정보 삭제
	 * @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("계약업체정보 삭제");

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

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

            super.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;
        }
    }	
	
	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"));
			System.out.println(sqlstr.toString());
			persistent.execute();
			
			
		}
		catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			throw e;
		}
	}
	
    /**
     * 이전 계약번호 조회
	 * @param 
	 * @param 
	 * @return ValueObject
	 */
	public String getOldContNum(ValueObject valueObject){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StrBuffer sqlstr = new StrBuffer();
        ValueObject rowObject = null;
        String OldContModId = "";
        Integer old_mod_no = Integer.parseInt(valueObject.get("MOD_NO"))-1;
        
        try {
        	sqlstr.append(" 	SELECT SUB_CONT_MOD_ID	\n");
        	sqlstr.append(" 	  FROM SUB2000T A	\n");
        	sqlstr.append("   WHERE 1=1								\n");
    		sqlstr.append("   	AND A.SUB_CONT_ID = '"+ valueObject.get("SUB_CONT_ID") +"'			\n");
    		sqlstr.append("   	AND A.MOD_NO = '"+ old_mod_no +"'\n");
             
    		search.setStatement(sqlstr.toString());
            listRowSet = search.execute();
            if(listRowSet.size() != 0){
            	rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
            	OldContModId = rowObject.get("SUB_CONT_MOD_ID");
            }
            
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			return OldContModId;
		}
	}
	
	/**
     * 인지세 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 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;
		}
	}
*/	
	public void deleteContFile(ValueObject params) throws Exception {
		CallableStatement cs 	= null;
		String pssql			= "";
		
		try {
			conn.begin();
			
			pssql = "{call SP_DELETE_SUB_CONT(?,?,?)}";
			
			cs = conn.getConnection().prepareCall(pssql);
			cs.setString(1, params.get("SUB_CONT_MOD_ID"));
    		cs.registerOutParameter(2, java.sql.Types.VARCHAR);
    		cs.registerOutParameter(3, java.sql.Types.VARCHAR);
			cs.execute();
			
			storage.setSuccess(true);
		}catch(Exception e) {
			WAFLogger.error(e);
			storage.setSuccess(false);
			storage.setMessage(e.getMessage());
			throw e;
		}finally{
			try { 
				if (cs != null){ cs.close();}
			}catch (Exception ex_close) {
				WAFLogger.error(ex_close);
			}   
			conn.end();
		}
	}
}