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


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.ValueObject;
import kr.co.udapsoft.common.util.Crosscert;
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 PayGuarBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
        //System.out.println("BidIngListBiz :  ~" + storage.getUser());
		//System.out.println("Outside event : #" + storage.getEvent());
		//System.out.println("Outside params : " + params);
		boolean success = false;
		
		if(storage.compareEvent("")){
			RowSet rowSet = getTemplateList_searchCombo();
			//시트의 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_SELECT")){
            user = getContRequestValueObject(params.get("SUB_CONT_MOD_ID"));
            storage.addValueObject("ContInfo", user);

            
            storage.addValueObject("template", getTemplateInfo(params));
            
            storage.addValueObject("WarrContInfo", getWarrContValueObject(params));


		}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("SIGN_CALL")){

			params.set("pStatusCD", "20");
			params.set("CONT_SIGNDN", "");
			pageStatusUp(super.conn.getConnection(), params);	//서명 요청
			super.storage.setSuccess(true);
		}else if(storage.compareEvent("SIGN_OK")){
			//ERP 등록 업체 확인
			String sMsg="";
			String signedData="";
			Crosscert crossErt = new Crosscert();
			signedData = crossErt.signCrosscert(params.get("CONT_HASH"));
			sMsg = crossErt.chkSignVerify(signedData);
			//WAFLogger.debug("sMsg ======= : " + crossErt.getDn());
		
			if(sMsg.equals("SIGN_SUCCESS")){
				
				params.set("pStatusCD", "50");
				params.set("CONT_SIGNDN", crossErt.getDn());
				params.set("CONT_SIGNDATA", signedData);

				success = pageStatusUp(super.conn.getConnection(), params);
				
				//WAFLogger.debug("SUCCESS STATUS ======= : " + success);	
				super.storage.setSuccess(success);
			}else{
				super.storage.setSuccess(false);	
				super.storage.setMessage("인증서 검증에 실패 하였습니다.");
			}		
		}
	}

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

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        ValueObject valueobject = null;
        
        try {
        	if(params.get("PAGE_SEQ").isEmpty()){
                sqlstr += " SELECT            							\n"
                		+ "   T.TEMPLATE_CD                              \n"
    					+ "	  , SEQ                                     \n"
    					+ "	  , T.TEMPLATE_NAME                           \n"
    					+ "	  , T.TEMPLATE_HTML                            \n"
    					+ "	  , T.TEMPLATE_STYLE                           \n"
    					+ "	  , OPTION_YN	                                \n"
    					+ "	  , '' AS UNIQUE_FILE_NAME	                                \n"
    					+ "	  , '' AS FILE_PATH	                                \n"
    					+ "	  , '' AS FILE_EXT	                                \n"
    					+ "	  , '' AS FILE_SIZE	                                \n"    					
    					+ "	  , (SELECT NVL(MAX(PAGE_SEQ),0) + 1 FROM CONT_OUT_PAYGUA_HTML WHERE SUB_CONT_MOD_ID = '"+ param.get("SUB_CONT_MOD_ID") +"'	AND WARR_SEQ =  '"+ param.get("WARR_SEQ") +"') AS PAGE_SEQ		\n"
    					+ "	  , '' AS STATUS_CD                       \n"
    					+ "	  , F_GET_CODE_NAME('PAGE_STATUS_CD', '10') AS STATUS_CD_NM	\n"
                        + " FROM CONT_TEMPLATE T 					\n"
                        + " WHERE T.TEMPLATE_CD =  '"+ param.get("TEMPLATE_CD") +"'		\n"
    		            + " ORDER BY T.TEMPLATE_CD ASC      				\n";        		
        	}else{
                sqlstr += " SELECT            							\n"
                		+ "   TEMPLATE_CD                              \n"
    					+ "	  , 0 AS SEQ                                     \n"
    					+ "	  , H.TEMPLATE_NAME                            \n"
    					+ "	  , H.TEMPLATE_HTML                            \n"
    					+ "	  , H.TEMPLATE_STYLE                           \n"
     					+ "	  , 'A' AS OPTION_YN	                                \n"
    					+ "	  , UNIQUE_FILE_NAME	                                \n"
    					+ "	  , FILE_PATH	                                \n"
    					+ "	  , FILE_EXT	                                \n"
    					+ "	  , TRIM(          \n"              
	            		+ "        CASE                         \n"
					    + "       WHEN FILE_SIZE < 1024 THEN TO_CHAR(FILE_SIZE, '999,999') || ' Byte'          \n"
					    + "       ELSE          \n"
					    + "            CASE                WHEN FILE_SIZE < (1024*1024) THEN TO_CHAR(FLOOR(FILE_SIZE / 1024), '999,999') || ' KByte'          \n"
					    + "             ELSE  TO_CHAR(FLOOR(FILE_SIZE / (1024*1024)), '999,999,999') || ' MByte'          \n"
					    + "            END                           \n"                              
					    + "      END ) AS FILE_SIZE       			\n"
    					+ "	  , CONT_HASH	                         \n"
    					+ "	  , H.PAGE_SEQ                       \n"
    					+ "	  , H.STATUS_CD                       \n"
    					+ "	  , F_GET_CODE_NAME('PAGE_STATUS_CD', H.STATUS_CD) AS STATUS_CD_NM	\n"
    					+ "	  , TO_CHAR(H.C_SIGN_DATE,'YYYY-MM-DD HH24:MI:SS') AS C_SIGN_DATE	\n"
    					+ "	  , TO_CHAR(H.O_SIGN_DATE,'YYYY-MM-DD HH24:MI:SS') AS O_SIGN_DATE	\n"
                        + " FROM CONT_OUT_PAYGUA_HTML H    					\n"
                        + " WHERE H.SUB_CONT_MOD_ID = '"+ param.get("SUB_CONT_MOD_ID") +"'		\n"
                        + "    AND H.WARR_SEQ =  '"+ param.get("WARR_SEQ") +"'		\n"
                        + "    AND H.PAGE_SEQ =  '"+ param.get("PAGE_SEQ") +"'		\n";
        		
        	}
             
            search.setStatement(sqlstr);
            listRowSet = search.execute();
            if( listRowSet != null && listRowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(listRowSet.getRow(0));
            }
            
		}catch(Exception e){
			e.printStackTrace();
            System.out.println(sqlstr.toString());
            System.out.println("WAFSQLException : " + e.getMessage());		
		}finally{

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

        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 ('G')       					\n"
                    + "   AND SEQ = '0'        						\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 || '-' || 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.CON_SUPPLY_AMT, '999,999,999,999,999') 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("	     ,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("	     ,W.WARR_WGNO	\n");
        	sqlstr.append("	     ,TO_CHAR(TO_DATE(W.PAPERDATE),'YYYY-MM-DD') AS PAPERDATE	\n");
        	sqlstr.append("	     ,F_GET_CODE_NAME('CAUSECODE', W.CAUSECODE) AS CAUSECODE_NM	\n");
        	sqlstr.append("	     ,W.WARR_WAMT	\n");
        	sqlstr.append("	     ,W.WARR_SEQ	\n");
        	sqlstr.append("	     ,W.WARR_COMMSN	\n");
        	sqlstr.append("	     ,H.TEMPLATE_CD	\n");
        	sqlstr.append("	     ,H.PAGE_SEQ		\n");
        	sqlstr.append("	     ,DECODE(H.TEMPLATE_CD, NULL, 0, 1) AS BTN	\n");
        	sqlstr.append("	     ,DECODE(H.TEMPLATE_CD, 12, 1, 0) AS BTN1	\n");
        	sqlstr.append("	     ,F_GET_CODE_NAME('PAGE_STATUS_CD', NVL(H.STATUS_CD,'10')) AS STATUS_CD_NM	\n");
        	sqlstr.append("	  FROM SUB2000T A, CONT_OUT_SUPP F, CONT_OUT_WARR W, CONT_OUT_PAYGUA_HTML H	\n");
        	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 = W.SUB_CONT_MOD_ID	\n");
        	sqlstr.append("			AND W.SUB_CONT_MOD_ID = H.SUB_CONT_MOD_ID(+)	\n");
        	sqlstr.append("			AND W.WARR_SEQ = H.WARR_SEQ(+)	\n");
        	sqlstr.append("			AND A.CON_CLS_YN = 9	\n");
        	sqlstr.append("			AND F.SIGN_SEQ = 2	\n");
        	sqlstr.append("			AND W.WARR_GUBUN = 2	\n");
        	sqlstr.append("	    	AND W.GUAR_STATUS = 9	\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");

        	sqlstr.append("	  ORDER BY PAPERDATE DESC, PAGE_SEQ DESC, CON_DOC_NO	\n");
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //System.out.println("### WAFSQLException : " + se.getMessage());
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        //System.out.println("### rowSet size : " + rowSet.size());
        return rowSet;
	}
	

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

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
	        	sqlstr.append(" 	SELECT A.SUB_CONT_MOD_ID	\n");
	        	sqlstr.append(" 	     , A.CON_DOC_NO	\n");
	        	sqlstr.append(" 	     , A.MOD_NO	\n");	
	        	sqlstr.append(" 	     , DECODE(A.MOD_GB, NULL, '00', A.MOD_GB) AS MOD_GB	\n");	
	        	sqlstr.append(" 	     , A.DEPT_NAME	\n");	
	        	sqlstr.append(" 	     , A.BUILD_NAME	\n");	
	        	sqlstr.append(" 	     , A.CON_DATE	\n");	
	        	sqlstr.append(" 	     , A.DEALER_NM	\n");	
	        	sqlstr.append(" 	     , A.CON_AMT	\n");	
	        	sqlstr.append(" 	     , A.START_WORK_DATE	\n");	
	        	sqlstr.append(" 	     , A.ELEC_CON_YN	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE,'YYYY') AS START_WORK_DATE_YEAR 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE,'MM') AS START_WORK_DATE_MONTH 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE,'DD') AS START_WORK_DATE_DAY 	\n");	
	        	sqlstr.append(" 	     , A.END_WORK_DATE	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE,'YYYY') AS END_WORK_DATE_YEAR 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE,'MM') AS END_WORK_DATE_MONTH 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE,'DD') AS END_WORK_DATE_DAY 	\n");	
	        	sqlstr.append(" 	     , A.CON_GB 	\n");
	        	sqlstr.append(" 	     , A.CON_CUR_CD 	\n");														//추가 (2014-09-19)
	        	sqlstr.append(" 	     , (SELECT CHG_CODE01 FROM EBID_COM911T WHERE BASECD='911' AND DETAILCD=A.CON_CUR_CD) AS SOSUNUM 	\n");
	        	sqlstr.append(" 	     , A.UND_CON_NAME 	\n");	
	        	sqlstr.append(" 	     , A.SUB_UND_CON_NAME 	\n");	
	        	sqlstr.append(" 	     , A.WORK_PLACE 	\n");	
	        	sqlstr.append(" 	     , A.CON_SUPPLY_AMT 	\n");	
	        	sqlstr.append(" 	     , A.LABOR_AMT 	\n");	     	
	        	sqlstr.append(" 	     , A.CON_SUPPLY_TAMT 	\n");	
	        	sqlstr.append(" 	     , A.CON_SUPPLY_NAMT 	\n");	
	        	sqlstr.append(" 	     , A.CON_SURTAX_AMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_AMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_RATE 	\n");	
	        	sqlstr.append(" 	     , A.PAY_WAY_BILL 	\n");	
	        	sqlstr.append(" 	     , A.PAY_WAY_CASH 	\n");	

	        	sqlstr.append(" 	     , A.PREPAY_SUPPLY_AMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_SUPPLY_TAMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_SUPPLY_NAMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_SURTAX_AMT 	\n");	
	        	sqlstr.append(" 	     , A.PREPAY_GUAR_AMT 	\n");	//추가 (2014-10-15)
	        	sqlstr.append(" 	     , A.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(" 	     , A.DEF_EST_GUAR_RATE 	\n");	
	        	sqlstr.append(" 	     , A.DEF_GUAR_AMT 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.DEF_EST_TERM 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.DELAY_RATE 	\n");	
	        	sqlstr.append(" 	     , A.PAY_COND_GB 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , F_GET_CODE_NAME('SUB_ACMP_PAY_TYPE', A.PAY_COND_GB) PAY_COND_GB_NM 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.PAY_CONTENT 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.CON_SPE_TXT 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , A.LABOR_GUAR_JOIN_AMT_GB 	\n");			//추가 (2014-09-19)
	        	sqlstr.append(" 	     , TO_CHAR(A.CON_DATE,'YYYY') AS CON_DATE_YEAR 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.CON_DATE,'MM') AS CON_DATE_MONTH 	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(A.CON_DATE,'DD') AS CON_DATE_DAY 	\n");	
	        	sqlstr.append(" 	     , A.STATUS_CD 	\n");	
	        	sqlstr.append(" 	     , A.MONTH_ACMP_CNT 	\n");	
	        	sqlstr.append(" 	     , A.OBJ_RCPT_DAY 	\n");	
	        	/*발주자 정보*/
	        	sqlstr.append(" 	     , 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(" 	     , A.CON_CONFIRM_ID 	\n");	
	        	sqlstr.append(" 	     , A.CON_CONFIRM_NAME 	\n");	

	        	sqlstr.append(" 	     , DECODE(D.CORP_ID, '', 0, D.CORP_ID)  AS O_CORP_ID	\n");	
	        	sqlstr.append(" 	     , DECODE(D.MEMBER_NAME, '', C.CORP_KOR, D.MEMBER_NAME)  AS O_MEMBER_NAME	\n");	
	        	sqlstr.append(" 	     , DECODE(D.ADDRESS, '', C.ADDRESS, D.ADDRESS) AS O_ADDRESS	\n");	
	        	sqlstr.append(" 	     , DECODE(D.BOSS_NAME, '', C.REP_NAME, D.BOSS_NAME) AS O_BOSS_NAME	\n");	
	        	sqlstr.append(" 	     , D.USER_NAME AS O_USER_NAME	\n");	
	        	sqlstr.append(" 	     , D.TEL_NUM AS O_TEL_NUM	\n");	
	        	sqlstr.append(" 	     , D.DIVISION AS O_DIVISION	\n");	
	        	sqlstr.append(" 	     , D.POSITION AS O_POSITION	\n");	
	        	sqlstr.append(" 	     , D.HP AS O_HP	\n");	
	        	sqlstr.append(" 	     , D.EMAIL AS O_EMAIL	\n");	
	        	sqlstr.append(" 	     , TO_CHAR(D.SIGN_DATE,'YYYY-MM-DD HH24:MI:SS') AS O_SIGN_DATE	\n");	
	        	sqlstr.append(" 	     , 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(" 	     , TO_CHAR(E.SIGN_DATE,'YYYY-MM-DD HH24:MI:SS') AS C_SIGN_DATE	\n");	

				sqlstr.append("  FROM SUB2000T A, BID_MASTER B, HEAD_OFFICE_INFO C, CONT_OUT_SUPP D, CONT_OUT_SUPP E	\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_MOD_ID = '"+SUB_CONT_MOD_ID+"'							\n");

	            search.setStatement(sqlstr.toString());
	            rowSet = search.execute();

            
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            	
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //System.out.println(sqlstr.toString());
            //System.out.println("WAFSQLException : " + se.getMessage());
        }
        finally {

        	if( valueobject == null ) valueobject = new ValueObject();
        	//System.out.println("### valueobject size : " + valueobject.size());
            return valueobject;
        }
	}
	
	/**
     * 계약 정보 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean saveContProc(Connection connection, ValueObject valueObject) throws Exception {
		//System.out.println("valueObject : " + valueObject);

        try {
        	conn.begin();
        	
			params.set("TRUE_RANDOM", "");
			
			ArrayList pdfIfno=makeContPdf(params);		// pdf파일 생성
			
//			if (pdfIfno.isEmpty()){
//				super.storage.setMessage("PDF 생성에 실패 하였습니다.");    
//				return false;
//			}
			
			payGuaHtmlDel(conn.getConnection(), params);	//ContHTML 삭제
			payGuaHtmlSave(super.conn.getConnection(), params, (ValueObject) pdfIfno.get(0));	//ContHTML 등록
			
			return true;
        }
        catch(Exception e) {
        	System.out.println("Exception : " + e.getMessage());
            WAFLogger.error(this.getClass().getName() + " --> addChangeContProc() : " + e.getMessage());
            WAFLogger.error(e);

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

	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")};
		}
		//System.out.println("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", "paygua");
				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 payGuaHtmlDel(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  CONT_OUT_PAYGUA_HTML						\n");
        	sqlstr.append("WHERE SUB_CONT_MOD_ID = ?						\n");
        	sqlstr.append("AND WARR_SEQ = ?						\n");
        	sqlstr.append("AND PAGE_SEQ = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
            persistent.addParameter(valueObject.get("WARR_SEQ"));
            persistent.addParameter(valueObject.get("PAGE_SEQ"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //System.out.println("Exception : " + e.getMessage());
            throw e;
        }
    }	
	/**
     * 계약 htm 등록
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean payGuaHtmlSave(Connection connection, ValueObject valueObject, ValueObject pdfInfo) 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;

			sqlstr.append("INSERT INTO CONT_OUT_PAYGUA_HTML (SUB_CONT_MOD_ID, WARR_SEQ, PAGE_SEQ, TEMPLATE_NAME, TEMPLATE_HTML, TEMPLATE_STYLE, CHECK_YN, UNIQUE_FILE_NAME, FILE_PATH, FILE_EXT, FILE_SIZE, CONT_HASH, TEMPLATE_CD, STATUS_CD)						\n");
			sqlstr.append("VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '10')					\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
            persistent.addParameter(valueObject.getObject("warr_seq"));
            persistent.addParameter(valueObject.getObject("PAGE_SEQ"));
            persistent.addParameter(valueObject.getObject("template_name"));
            persistent.addParameter(valueObject.getObject("cont_html"));
            persistent.addParameter(pdfInfo.getObject("template_style"));
            persistent.addParameter("A");
            persistent.addParameter(pdfInfo.get("file_name"));
            persistent.addParameter(pdfInfo.get("file_path"));
            persistent.addParameter(pdfInfo.get("file_ext"));
            persistent.addParameter(pdfInfo.get("file_size"));
            persistent.addParameter(pdfInfo.get("file_hash"));
            persistent.addParameter(valueObject.get("template_cd"));
            persistent.execute();  
            sqlstr.reset();
            persistent.clearParameters();


            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> payGuaHtmlSave() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            System.out.println("Exception : " + e.getMessage());
            throw e;
        }
    }	

	/**
     * 계약 정보 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean deleteContProc(Connection connection, ValueObject valueObject) throws Exception {
		//System.out.println("valueObject : " + valueObject);

        try {
        	conn.begin();

        	payGuaHtmlDel(super.conn.getConnection(), params);	//ContHTML 삭제

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

            conn.setRollback();
            
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }
        finally {
        	super.conn.end();
        }        
    }	
	
	/**
	 * 보증 상세 정보
	 * @param Params
	 * @return
	 */
	private ValueObject getWarrContValueObject(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        //System.out.println("=======================" + param.get("warrGubun"));
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append("  	SELECT 	\n");
        	sqlstr.append("  	  	SUB_CONT_MOD_ID		\n");
        	sqlstr.append("  	  , 	WARR_GUBUN		\n");
        	sqlstr.append("  	  , 	WARR_SEQ		\n");
        	sqlstr.append("  	  , 	SUB_CONT_ID		\n");
        	sqlstr.append("  	  , 	MOD_NO		\n");
        	sqlstr.append("  	  , 	VIEW_DIV		\n");
        	sqlstr.append("  	  , 	PREPAY_ACMP_ID		\n");
        	sqlstr.append("  	  , 	WARR_WAMT		\n");
        	sqlstr.append("  	  , 	WARR_WRATE		\n");
        	sqlstr.append("  	  , 	WARR_KAMT1		\n");
        	sqlstr.append("  	  , 	WARR_KAMT2		\n");
        	sqlstr.append("  	  , 	WARR_COMMSN		\n");
        	sqlstr.append("  	  , 	WARR_KCLS		\n");
        	sqlstr.append("  	  , 	WARR_SDATE		\n");
        	sqlstr.append("  	  , 	WARR_EDATE		\n");
        	sqlstr.append("  	  , 	WARR_WGNO		\n");
        	sqlstr.append("  	  , 	WARR_GOFFICE		\n");
        	sqlstr.append("  	  , 	WARR_GJIJUM		\n");
        	sqlstr.append("  	  , 	WARR_GTEL		\n");
        	sqlstr.append("  	  , 	WARR_GMAN		\n");
        	sqlstr.append("  	  , 	WARR_HEADCONF		\n");
        	sqlstr.append("  	  , 	WARR_GREM		\n");
        	sqlstr.append("  	  , 	WARR_INDAY		\n");
        	sqlstr.append("  	  , 	WARR_INUSR		\n");
        	sqlstr.append("  	  , 	WARR_UPDAY		\n");
        	sqlstr.append("  	  , 	WARR_UPUSR		\n");
        	sqlstr.append("  	  , 	GUAR_ONOFF_YN		\n");
        	sqlstr.append("  	  , 	GUAR_ORGAN_CODE		\n");
        	sqlstr.append("  	  , 	CAUSECODE		\n");
        	sqlstr.append("  	  , 	PAPERDATE		\n");
        	sqlstr.append("  	  , 	REJECTMENT		\n");
        	sqlstr.append("  	  , 	REJECT_DY		\n");
        	sqlstr.append("  	  , 	EXEMPTMENT		\n");
        	sqlstr.append("  	  , 	ACCDOCSTATUS		\n");
        	sqlstr.append("  	  , 	ACCDOC_SEQ		\n");
        	sqlstr.append("  	  , 	GRTREQNO		\n");
        	sqlstr.append("  	  , 	YEARINSUREYN		\n");
        	sqlstr.append("  	  , 	REQDATE		\n");
        	sqlstr.append("  	  , 	GUAR_STATUS		\n");
        	sqlstr.append("  	  , 	BOND_SEQ		\n");
        	sqlstr.append("  	  , 	CONTSEQ		\n");
        	sqlstr.append("  	  , 	GUARRATEGBNCODE		\n");
        	sqlstr.append("  	  , 	LONGGBN		\n");
        	sqlstr.append("  	  , 	CONTORDERNO		\n");
        	sqlstr.append("  	  , 	CONTAMT		\n");
        	sqlstr.append("  	  , 	CONTDATE		\n");
        	sqlstr.append("  	  , 	CONTSTARTDATE		\n");
        	sqlstr.append("  	  , 	CONTCLOSEDATE		\n");
        	sqlstr.append("  	  , 	PREPAYREFYN		\n");
        	sqlstr.append("  	  , 	EXIISTREFYN		\n");
        	sqlstr.append("  	  , 	TRADESIGNYN		\n");
        	sqlstr.append("  	  , 	TRADESIGNDATE		\n");
        	sqlstr.append("  	  , 	CONTCHECKDATE		\n");
        	sqlstr.append("  	  , 	CONTEXISTENCEAMT		\n");
        	sqlstr.append("  	  , 	PJTID		\n");
        	sqlstr.append("  	  , 	PAYMETHOD		\n");
        	sqlstr.append("  	  , 	CONTORGBNCODE		\n");
        	sqlstr.append("  	  , 	GUAR_CONTAMT		\n");
        	sqlstr.append("  	  , 	FILETITLE		\n");
        	sqlstr.append("  	  , 	FILE_NAME		\n");
        	sqlstr.append("  	  , 	FILEEXT		\n");
        	sqlstr.append("  	  , 	FILESIZE		\n");
        	sqlstr.append("  	  , 	EXFILETITLE		\n");
        	sqlstr.append("  	  , 	EXFILENAME		\n");
        	sqlstr.append("  	  , 	DEPNM		\n");
        	sqlstr.append("  	  , 	POSITION		\n");
        	sqlstr.append("  	  , 	EMPNM		\n");
        	sqlstr.append("  	  , 	HPNO		\n");
        	sqlstr.append("  	  , 	TELNO		\n");
        	sqlstr.append("  	  , 	EMAIL		\n");
        	sqlstr.append("  	  , 	ITEMDOC		\n");
        	sqlstr.append("  	  , 	ITEMEXT		\n");
        	sqlstr.append("  	  , 	ITEMPATH		\n");
        	sqlstr.append("  	  , 	ORDEROWNER		\n");
        	sqlstr.append("  	  , 	ADDCONTAMT		\n");
        	sqlstr.append("  	  , 	ADDPREPAYAMT		\n");
        	sqlstr.append("  	  , 	ADDEXISAMT		\n");
        	sqlstr.append("  	  , 	CONTTYPE		\n");
        	sqlstr.append("  	  , 	DEALER_BSNO		\n");
        	sqlstr.append("  	FROM CONT_OUT_WARR B	\n");
        	sqlstr.append("  	WHERE 1=1					\n");
        	sqlstr.append("  	    AND B.SUB_CONT_MOD_ID = '"+param.get("SUB_CONT_MOD_ID")+"'	\n");
        	sqlstr.append("  	    AND B.WARR_GUBUN = 2	\n");
        	sqlstr.append("  	    AND B.WARR_SEQ = '"+param.get("WARR_SEQ")+"'	\n");

			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            	
        }
        catch(WAFSQLException se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            //System.out.println(sqlstr.toString());
            //System.out.println("WAFSQLException : " + se.getMessage());
        }
        finally {

        	if( valueobject == null ) valueobject = new ValueObject();
        	//System.out.println("### valueobject size : " + valueobject.size());
            return valueobject;
        }
	}
	/**
     * 을사에 싸인 요청
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean pageStatusUp(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	//WAFLogger.debug("Exception : " + valueObject.get("pConClsYn"));
	        	sqlstr.append("UPDATE CONT_OUT_PAYGUA_HTML SET  	\n");
	        	sqlstr.append(" STATUS_CD = ?					\n");	        	
	        	if (!valueObject.get("CONT_SIGNDN").isEmpty()){
		        	sqlstr.append(" , O_SIGN_DN = '"+ valueObject.get("CONT_SIGNDN") +"'					\n");	        	
		        	sqlstr.append(" , O_SIGN_DATA = '"+ valueObject.get("CONT_SIGNDATA") +"'					\n");	        	
		        	sqlstr.append(" , O_SIGN_DATE = SYSDATE					\n");	        	
	        	}
	        	sqlstr.append("WHERE SUB_CONT_MOD_ID = ?						\n");
	        	sqlstr.append("AND WARR_SEQ = ?						\n");
	        	sqlstr.append("AND PAGE_SEQ = ?						\n");

				
	            persistent.setStatement(sqlstr.toString());
	            persistent.addParameter(valueObject.get("pStatusCD"));                  
	            persistent.addParameter(valueObject.get("SUB_CONT_MOD_ID"));
	            persistent.addParameter(valueObject.get("WARR_SEQ"));
	            persistent.addParameter(valueObject.get("PAGE_SEQ"));
            persistent.execute();
//
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            return false;
        }
    }	
}