/**
 * @(#)file    ContIntBiz.java
 * @(#)author  Kim Myung Ho
 * @(#)version 1.0
 * @(#)date    2014-09-04
 * @(#)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.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.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 SubconPayGuarBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();

		boolean success = false;
		
		if(storage.compareEvent("")){
			
		}else if(storage.compareEvent("SHEET_SELECT")){
			params.set("sheet1", getContList(params));
			
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("SHEET_EXCEL")){  //2015.3.18 excel양식 다운 mklee
			params.set("sheet3", getContExcelList(params));
			
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("SHEET_WARR")){
			params.set("sheet2", getWarrList(params));
			
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));

			
		}else if(storage.compareEvent("NOT_GUAR_SAVE")){		//보증 비대상 작성
			String[] status = super.gets("sheet2.STATUS");
			
			for (int i = 0; i < status.length; i++) {
				success = insertWarrData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
			}				
			super.storage.setSuccess(success);
            if(success)
            	storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));		
		}			
	}

	/**
	 * 하도급 대금 지급 대상 계약 리스트 조회
	 * @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 || '-' || A.MOD_NO) AS CON_DOC_NO	\n");
        	sqlstr.append(" 	     ,A.MOD_NO						\n");
        	sqlstr.append(" 	     ,A.SUB_CONT_ID					\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(" 	     ,TO_CHAR(A.CON_AMT, '999,999,999,999,999') AS CON_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(" 	     ,A.START_WORK_DATE				\n");
        	sqlstr.append(" 	     ,A.END_WORK_DATE				\n");
        	sqlstr.append(" 	     ,A.CON_GB 						\n");
        	sqlstr.append(" 	     ,A.STATUS_CD 					\n");
        	sqlstr.append(" 	     ,F_GET_CODE_NAME('MOD_GB',NVL(DECODE(A.MOD_GB,'1','01','2','10','3','11','9','99', A.MOD_GB),'00')) AS CAUSECODE_NM 				\n");
        	sqlstr.append(" 	     ,NVL2(B.GUAR_STATUS, '00', B.GUAR_STATUS) as WARR_STATUS_CD				\n");
        	sqlstr.append(" 	     ,DECODE(NVL(B.GUAR_STATUS, '00'), '09', '완료', '01','작성중', '작성대기') WARR_STATUS				\n");
        	sqlstr.append(" 	     ,SUBSTR(B.PAPERDATE, 1, 4) || '-' || SUBSTR(B.PAPERDATE, 5,2) || '-' ||  SUBSTR(B.PAPERDATE, 7,2) WARR_DATE  \n");
        	sqlstr.append(" 	     ,B.WARR_PAY_GB					\n");
        	sqlstr.append(" 	     ,F_GET_CODE_NAME('WARR_PAY_GB', B.WARR_PAY_GB) AS WARR_PAY_NM					\n");
        	sqlstr.append(" 	  FROM SUB2000T A, CONT_OUT_WARR B, SUB2000T C, CONT_OUT_SUPP F	\n");  //2016.11.14 변경계약도 포함
        	sqlstr.append("   WHERE 1=1								\n");
    		sqlstr.append("   	AND A.SUB_CONT_MOD_ID = F.SUB_CONT_MOD_ID			\n");
    		sqlstr.append("   	AND A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID(+)			\n");
    		sqlstr.append("   	AND A.SUB_CONT_ID = C.SUB_CONT_ID			\n");
//    		sqlstr.append("   	AND A.CON_CLS_YN = 9				\n");
    		sqlstr.append("   	AND A.STATUS_CD NOT IN('00','10')	\n");
    		sqlstr.append("   	AND F.SIGN_SEQ = 2					\n");
    		sqlstr.append("   	AND B.WARR_GUBUN(+) = '2'					\n");
    		sqlstr.append("   	AND C.MOD_NO = 0					\n");
//    		sqlstr.append("   	AND C.CON_CLS_YN = 9				\n");
    		sqlstr.append("   	AND C.STATUS_CD NOT IN('00','10')	\n");
    		sqlstr.append("   	AND A.CON_AMT >= 10000000			\n"); 
    		sqlstr.append("   	AND C.TEMPLATE_CD != '2'	        \n"); 
    		sqlstr.append("   	AND A.MOD_GB NOT IN ('02','20','22') 	   \n");  //2017.07.31 감액,단축,감액단축 제외 
        	// 현장
        	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_WARR_PAY_GB").isEmpty())
        		sqlstr.append("  AND B.WARR_PAY_GB = '"+param.get("P_WARR_PAY_GB")+"' 	\n");
        	if(!param.get("P_WARR_STATUS").isEmpty())
        		sqlstr.append("  AND NVL(B.GUAR_STATUS, '00') = '"+param.get("P_WARR_STATUS")+"' 	\n");
        	
//        	if(!param.get("P_CONT_STATUS").isEmpty())
//        		sqlstr.append("   AND A.STATUS_CD IN ("+param.get("P_CONT_STATUS")+")		\n");
        	
        	//sqlstr.append("   ORDER BY A.DEPT_NAME, A.BUILD_NAME DESC, F.MEMBER_NAME, A.MOD_NO DESC	\n"); 2017.07.31 정렬기준변경
        	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();
            }
        }
        return rowSet;
	}
	
	//2015.3.18
	private RowSet getContExcelList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" 	SELECT A.DEPT_NAME                DEPT_NAME                                                       \n");
        	sqlstr.append(" 	, GET_OUT_ITEM_NM(A.WORK_KIND_CD)  OUT_ITEM                                                        \n");
        	sqlstr.append(" 	, A.BUILD_NAME                    BUILD_NAME                                                       \n");
        	sqlstr.append(" 	, C.MEMBER_NAME                   CONT_NAME                                                       \n");
        	sqlstr.append(" 	, TO_CHAR(A.CON_AMT, '999,999,999,999,999') CON_AMT                                               \n");
        	sqlstr.append(" 	, TO_CHAR(B.WARR_COMMSN, '999,999,999,999,999')  WARR_AMT                                           \n");
        	sqlstr.append(" 	, TO_CHAR(A.CON_DATE, 'YYYY-MM-DD') || '~' ||  TO_CHAR(A.END_WORK_DATE, 'YYYY-MM-DD')  DUR_DATE   \n");
        	sqlstr.append(" 	, SUBSTR(B.WARR_SDATE, 1, 4) || '-' || SUBSTR(B.WARR_SDATE, 5,2) || '-' ||  SUBSTR(B.WARR_SDATE, 7,2) WARR_DATE  \n");
        	sqlstr.append(" 	, TO_CHAR(A.CON_DATE, 'YYYY-MM-DD') AS CON_DATE	\n");
        	sqlstr.append(" 	, F_GET_CODE_NAME('MOD_GB',NVL(DECODE(A.MOD_GB,'1','01','2','10','3','11','9','99', A.MOD_GB),'00')) AS CAUSECODE_NM 				\n");
        	sqlstr.append(" 	, DECODE(NVL(B.GUAR_STATUS, '00'), '09', '완료', '01','작성중', '작성대기') WARR_STATUS				\n");        	
        	sqlstr.append(" 	FROM SUB2000T A                                                                                   \n");
        	sqlstr.append(" 	, CONT_OUT_WARR B                                                                                 \n");
        	sqlstr.append(" 	, CONT_OUT_SUPP C                                                                                 \n");
        	sqlstr.append(" 	WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID(+)                                                    \n");
        	sqlstr.append(" 	AND A.SUB_CONT_MOD_ID = C.SUB_CONT_MOD_ID                                                         \n");
        	sqlstr.append(" 	AND C.SIGN_SEQ = 2                                                                                \n");
        	sqlstr.append(" 	AND A.CON_CLS_YN = 9                                                                              \n");
        	sqlstr.append(" 	AND B.WARR_GUBUN(+) = '2'                                                                         \n");
        	sqlstr.append(" 	AND A.MOD_NO = 0 AND A.CON_AMT >= 10000000                                                          \n");
    		sqlstr.append("   	AND A.TEMPLATE_CD != '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 A.BUILD_NAME LIKE '%"+param.get("P_BUILD_NM")+"%'				\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 C.MEMBER_NAME LIKE '%'|| '"+param.get("p_dealer_nm")+"' ||'%'	\n");
        	
        	sqlstr.append("   ORDER BY A.DEPT_NAME, A.BUILD_NAME DESC, C.MEMBER_NAME 	\n");
        	
        	WAFLogger.debug(sqlstr.toString());
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}
	

	/**
	 * 보증 대상리스트 조회
	 * @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  NVL(MAX(WARR_GUBUN),'2') AS WARR_GUBUN	\n");
        	sqlstr.append("	    , NVL(MAX(WARR_GUBUN_NM),'하도급대금지급보증') AS WARR_GUBUN_NM	\n");
        	sqlstr.append("	    , NVL(MAX(WARR_SEQ), (SELECT NVL(MAX(WARR_SEQ),0) + 1 FROM CONT_OUT_WARR WHERE SUB_CONT_MOD_ID = '"+ params.get("SUB_CONT_MOD_ID") +"')) AS WARR_SEQ	\n");
        	sqlstr.append("	    , TO_CHAR(MAX(WARR_WAMT), '999,999,999,999,999') AS WARR_WAMT	\n");
        	sqlstr.append("	    , TO_CHAR(MAX(WARR_COMMSN), '999,999,999,999,999') AS WARR_COMMSN 	\n");
        	sqlstr.append("	    , NVL(MAX(VIEW_DIV),'SUBCONPAY') AS VIEW_DIV	\n");
        	sqlstr.append("	    , NVL(CAUSECODE,'01') AS CAUSECODE	\n");
        	sqlstr.append("	    , F_GET_CODE_NAME('CAUSECODE',NVL(CAUSECODE,'01')) AS CAUSECODE_NM	\n");
        	sqlstr.append("	    , NVL(MAX(WARR_GOFFICE),'엔지니어링공제조합') AS WARR_GOFFICE	\n");  //건설공제조합 2016.5.11수정 
        	sqlstr.append("	    , MAX(GUAR_STATUS) AS GUAR_STATUS	\n");
        	sqlstr.append("	    , F_GET_CODE_NAME('GUAR_STATUS',NVL(MAX(GUAR_STATUS),'01')) AS GUAR_STATUS_NM	\n");
        	sqlstr.append("	    , MAX(PAPERDATE) AS PAPERDATE	\n");
        	sqlstr.append("	    , MAX(WARR_WGNO) AS WARR_WGNO	\n");
        	sqlstr.append("	    , MAX(WARR_SDATE) AS WARR_SDATE	\n");
        	sqlstr.append("	    , MAX(WARR_EDATE) AS WARR_EDATE	\n");
        	sqlstr.append("	    , MAX(WARR_START_END_DATE) AS WARR_START_END_DATE	\n");
        	sqlstr.append("	    , MAX(GRTREQNO) AS GRTREQNO 	\n");
        	sqlstr.append("	    , MAX(GUAR_ONOFF_YN) AS GUAR_ONOFF_YN 	\n");
        	sqlstr.append("	    , MAX(WARR_GREM) AS WARR_GREM 	\n");
        	sqlstr.append(" 	, DECODE(MAX(GUAR_ONOFF_YN), NULL, 0, DECODE(MAX(GUAR_STATUS), '08', 2, '09', 2, 1)) AS BTN 	\n");
        	sqlstr.append("	    , MAX(WARR_GOFFICE_ID) AS WARR_GOFFICE_ID	\n");
        	sqlstr.append("	     FROM (	\n");

        	sqlstr.append("	              SELECT WARR_GUBUN	\n");
        	sqlstr.append("	                   , F_GET_CODE_NAME('GAUR_KIND',WARR_GUBUN) AS WARR_GUBUN_NM	\n");
        	sqlstr.append("	                   , WARR_SEQ	\n");
        	sqlstr.append("	                   , NVL(WARR_WAMT, 0) AS WARR_WAMT	\n");
        	sqlstr.append("	                   , NVL(WARR_COMMSN, 0) AS WARR_COMMSN	\n");
        	sqlstr.append("	                   , VIEW_DIV	\n");
        	sqlstr.append("	                   , CAUSECODE	\n");
        	sqlstr.append("	                   , WARR_GOFFICE	\n");
        	sqlstr.append("	                   , GUAR_STATUS	\n");
        	sqlstr.append("	                   , TO_CHAR(TO_DATE(PAPERDATE),'YYYY-MM-DD') AS PAPERDATE	\n");
        	sqlstr.append("	                   , WARR_WGNO	\n");
        	sqlstr.append("	                   , WARR_SDATE	\n");
        	sqlstr.append("	                   , WARR_EDATE 	\n");
        	sqlstr.append("	                   , (TO_CHAR(TO_DATE(WARR_SDATE),'YYYY-MM-DD') || '~' || TO_CHAR(TO_DATE(WARR_EDATE),'YYYY-MM-DD')) AS WARR_START_END_DATE 	\n");
        	sqlstr.append("	                   , GRTREQNO 	\n");
        	sqlstr.append("	                   , GUAR_ONOFF_YN	\n");
        	sqlstr.append("	                   , WARR_GREM 	\n");
        	sqlstr.append("	                   , F_GET_CODE_NAME('GUAROFFICEID',GUAR_ORGAN_CODE) AS WARR_GOFFICE_ID	\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 ('2')	\n");
        	sqlstr.append("		\n");
        	sqlstr.append("	              UNION ALL	\n");
        	sqlstr.append("		\n");
        	sqlstr.append("	              SELECT null AS WARR_GUBUN	\n");
        	sqlstr.append("	                   , null AS WARR_GUBUN_NM	\n");
        	sqlstr.append("	                   , NULL AS WARR_SEQ	\n");
        	sqlstr.append("	                   , 0 AS WARR_WAMT	\n");
        	sqlstr.append("	                   , 0 AS WARR_COMMSN	\n");
        	sqlstr.append("	                   , null AS VIEW_DIV	\n");
        	sqlstr.append("	                   , NVL(DECODE(MOD_GB,'1','03','2','02','3','04'),'01') AS CAUSECODE	\n");
        	sqlstr.append("	                   , null AS WARR_GOFFICE	\n");
        	sqlstr.append("	                   , '' AS GUAR_STATUS	\n");
        	sqlstr.append("	                   , '' AS PAPERDATE	\n");
        	sqlstr.append("	                   , '' AS WARR_WGNO	\n");
        	sqlstr.append("	                   , '' AS WARR_SDATE	\n");
        	sqlstr.append("	                   , '' AS WARR_EDATE 	\n");
        	sqlstr.append("	                   , '' AS WARR_START_END_DATE 	\n");
        	sqlstr.append("	                   , '' AS GRTREQNO 	\n");
        	sqlstr.append("	                   , '' AS GUAR_ONOFF_YN 	\n");
        	sqlstr.append("	                   , '' AS WARR_GREM 	\n");
        	sqlstr.append("	                   , '' AS WARR_GOFFICE_ID 	\n");
        	sqlstr.append("	                FROM DUAL A, SUB2000T B 	\n");
        	sqlstr.append("	               WHERE B.SUB_CONT_MOD_ID = '"+ param.get("SUB_CONT_MOD_ID") +"' 	\n");
        	sqlstr.append("	                     AND  (SELECT COUNT(*) FROM CONT_OUT_WARR W WHERE  W.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID AND W.WARR_GUBUN = 2)  = 0 	\n");
        	sqlstr.append("	                     AND B.CON_AMT > 10000000	\n");
//        	sqlstr.append("	                     AND SUBSTR(MOD_GB,1,1) !=0	\n");
        	sqlstr.append("	                	\n");
        	sqlstr.append("	     )	\n");
        	sqlstr.append("	GROUP BY  WARR_SEQ, CAUSECODE	\n");
        	sqlstr.append("	ORDER BY  WARR_GUBUN	\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();
            }
        }
        return rowSet;
	}	
	
	/**
     * 비대상 보증정보 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */	
	public boolean insertWarrData(Connection connection, ValueObject sqlParams) throws Exception {
		Persistent persistent = new PersistentImpl(connection);
		StringBuffer sql = new StringBuffer();
		
		try {
        	sql.append("MERGE INTO CONT_OUT_WARR T1 USING (									\n");
        	sql.append("          SELECT ?	SUB_CONT_MOD_ID		\n ");
			sql.append("		         , ?	WARR_GUBUN				\n ");
			sql.append("		         , ?	WARR_SEQ			\n ");
			sql.append("		         , ?	SUB_CONT_ID		\n ");
			sql.append("		         , ?	MOD_NO				\n ");
			sql.append("		         , ?	WARR_GREM		\n ");
			sql.append("		         , ?	GUAR_ONOFF_YN				\n ");
			sql.append("		         , ?	CAUSECODE				\n ");
        	sql.append("		        FROM DUAL													\n");
        	sql.append("		    ) T ON (T.SUB_CONT_MOD_ID = T1.SUB_CONT_MOD_ID AND T.WARR_GUBUN = T1.WARR_GUBUN AND T.WARR_SEQ = T1.WARR_SEQ AND T.MOD_NO = T1.MOD_NO)	\n");
        	sql.append("		WHEN MATCHED THEN													\n");
        	sql.append("		    UPDATE SET 				\n");
        	sql.append("  				 T1.WARR_GREM	 =	 T.WARR_GREM	\n");
        	sql.append("  				, T1.GUAR_STATUS	 =	 '09'	\n");
        	sql.append("  				, T1.WARR_UPDAY	 =	 SYSDATE	\n");
        	sql.append("		WHEN NOT MATCHED THEN											\n");
        	sql.append("			INSERT (								\n ");
			sql.append("		         T1.SUB_CONT_MOD_ID		\n ");
			sql.append("		         , T1.WARR_GUBUN				\n ");
			sql.append("		         , T1.WARR_SEQ			\n ");
			sql.append("		         , T1.SUB_CONT_ID		\n ");
			sql.append("		         , T1.MOD_NO				\n ");
			sql.append("		         , T1.WARR_GREM		\n ");
			sql.append("		         , T1.GUAR_ONOFF_YN				\n ");
			sql.append("		         , T1.CAUSECODE				\n ");
			sql.append("		         , T1.GUAR_STATUS				\n ");
			sql.append("		         , T1.WARR_INDAY		\n ");
			sql.append("		         , T1.WARR_UPDAY		\n ");
			sql.append("		         , T1.TRADESIGNDATE		\n ");
			sql.append("		       ) 		\n ");
			sql.append("		       VALUES 		\n ");
			sql.append("		       ( 					\n ");
			sql.append("		          T.SUB_CONT_MOD_ID		\n ");
			sql.append("		         , T.WARR_GUBUN				\n ");
			sql.append("		         , T.WARR_SEQ			\n ");
			sql.append("		         , T.SUB_CONT_ID		\n ");
			sql.append("		         , T.MOD_NO				\n ");
			sql.append("		         , T.WARR_GREM		\n ");
			sql.append("		         , T.GUAR_ONOFF_YN				\n ");
			sql.append("		         , T.CAUSECODE				\n ");
			sql.append("		         , '09'				\n ");
			sql.append("		         , SYSDATE		\n ");
			sql.append("		         , SYSDATE		\n ");
			sql.append("		         , SYSDATE		\n ");
			sql.append("		       ) 		\n ");			

			persistent.setStatement(sql.toString());
			
			int paramIdx = 1;

			persistent.addParameter(params.get("SUB_CONT_MOD_ID"));
			persistent.addParameter(sqlParams.get("WARR_GUBUN"));
			persistent.addParameter(sqlParams.get("WARR_SEQ"));
			persistent.addParameter(params.get("SUB_CONT_ID"));
			persistent.addParameter(params.get("MOD_NO"));
			persistent.addParameter(sqlParams.get("WARR_GREM"));
			persistent.addParameter(sqlParams.get("GUAR_ONOFF_YN"));
			persistent.addParameter(sqlParams.get("CAUSECODE"));
//			persistent.addParameter(storage.getUser().getInt("ACCT_ID"));
//			persistent.addParameter(storage.getUser().getInt("ACCT_ID"));
			
			persistent.execute();
			return true;
		}         
			catch(Exception e) {
			storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sql);
            WAFLogger.error(e);
            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() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
	
}