/**
 * @(#)file    ContChgRcvBiz.java
 * @(#)author  kyj
 * @(#)version 1.0
 * @(#)date    2015-01-22
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.cont.outside.biz;


import java.sql.Connection;

import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.sql.Row;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFConnectionImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.ebid.buyer.bid.outside.biz.InterfaceOutERPiU;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import oracle.toplink.internal.ox.StrBuffer;


   
public class ContChgRcvBiz extends Biz {

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

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

		boolean success = false;
		
		if(storage.compareEvent("")){
			
		}else if(storage.compareEvent("FORM_SELECT")){
            user = getContChgRcvValueObject(params.get("SUB_CONT_MOD_ID"));
            storage.addValueObject("ContInfo", user);
            
            RowSet getFileListRowSet = getFileListRowSet("SUB_REQFILE_"+user.get("SUB_CONT_REQ_ID"), "");
            storage.addRowSet("getFileListRowSet", getFileListRowSet);
			
            storage.addValueObject("SIGN_INFO", getSignInfo(user.get("SUB_CONT_REQ_ID")));
		}else if(storage.compareEvent("FORM_SAVE")){
			success = saveContChgRcvProc(super.conn.getConnection(), params);
			super.storage.setSuccess(success);
			
		}else if(storage.compareEvent("CONT_REJECT")){		//반려
			
			success = deleteSUB3000T(super.conn.getConnection(), params);//2019.05.09 인터페이스테이블 삭제  
			
			if(success) {
				WAFConnectionImpl erpConn = new WAFConnectionImpl();
				try {
					erpConn.setDataSource("jdbc/erpiu");
					erpConn.begin();
					InterfaceOutERPiU.updateChangeContReject(erpConn.getConnection(), params.get("SUB_CONT_REQ_ID"), params.get("RJCTREASON"), storage.getUser().get("USER_SABUN"));
				}catch(Exception e) {
					super.conn.setRollback();
					erpConn.setRollback();
					throw e;
				}finally {
					erpConn.close();
				}
			}
			
			super.storage.setSuccess(true);
			
		}else if(storage.compareEvent("GET_ATTACHFILE")){
			//첨부파일
			ValueObject AttachFile = getfilelist("SUB_REQFILE_"+params.get("SUB_CONT_REQ_ID"),"","AttachFile");
	        storage.addValueObject("VALUEOBJECT", AttachFile);
        }else if(storage.compareEvent("DUP_CHK")){
			//첨부파일
        	ValueObject dupCnt = getDupCnt(params.get("SUB_CONT_REQ_ID"), params.get("SUB_CONT_MOD_ID"));
	        storage.addValueObject("VALUEOBJECT", dupCnt);
        }		
	}
	
	
	/**
	 * 계약 상세 정보
	 * @param Params
	 * @return
	 */
	private ValueObject getContChgRcvValueObject(String SUB_CONT_MOD_ID) {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject valueobject = null;
		//   String sqlstr = "";
		StrBuffer sqlstr = new StrBuffer();
		try{
			sqlstr.append(" 	SELECT A.SUB_CONT_MOD_ID	\n");
			sqlstr.append(" 	     , A.CON_DOC_NO	\n");
			sqlstr.append(" 	     , A.MOD_NO	\n");	
			sqlstr.append(" 	     , DECODE(A.MOD_GB, NULL, '00', A.MOD_GB) AS MOD_GB	\n");	
			sqlstr.append(" 	     , A.DEPT_NAME	\n");	
			sqlstr.append(" 	     , A.BUILD_NAME	\n");	
			sqlstr.append(" 	     , A.CON_DATE	\n");	
			sqlstr.append(" 	     , A.DEALER_NM	\n");	
			sqlstr.append(" 	     , A.CON_AMT	\n");	
			sqlstr.append(" 	     , A.START_WORK_DATE	\n");	
			sqlstr.append(" 	     , A.ELEC_CON_YN	\n");	
			sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE,'YYYY') AS START_WORK_DATE_YEAR 	\n");	
			sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE,'MM') AS START_WORK_DATE_MONTH 	\n");	
			sqlstr.append(" 	     , TO_CHAR(A.START_WORK_DATE,'DD') AS START_WORK_DATE_DAY 	\n");	
			sqlstr.append(" 	     , A.END_WORK_DATE	\n");	
			sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE,'YYYY') AS END_WORK_DATE_YEAR 	\n");	
			sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE,'MM') AS END_WORK_DATE_MONTH 	\n");	
			sqlstr.append(" 	     , TO_CHAR(A.END_WORK_DATE,'DD') AS END_WORK_DATE_DAY 	\n");	
			sqlstr.append(" 	     , A.CON_GB 	\n");	
			sqlstr.append(" 	     , A.CON_CUR_CD 	\n");														//추가 (2014-09-19)
			sqlstr.append(" 	     , (SELECT CHG_CODE01 FROM EBID_COM911T WHERE BASECD='911' AND DETAILCD=A.CON_CUR_CD) AS SOSUNUM 	\n");
			sqlstr.append(" 	     , A.UND_CON_NAME 	\n");	
			sqlstr.append(" 	     , A.SUB_UND_CON_NAME 	\n");	
			sqlstr.append(" 	     , A.WORK_PLACE 	\n");	
			sqlstr.append(" 	     , A.CON_SUPPLY_AMT 	\n");	
			sqlstr.append(" 	     , A.LABOR_AMT 	\n");	     	
			sqlstr.append(" 	     , A.CON_SUPPLY_TAMT 	\n");	
			sqlstr.append(" 	     , A.CON_SUPPLY_NAMT 	\n");	
			sqlstr.append(" 	     , A.CON_SURTAX_AMT 	\n");	
			sqlstr.append(" 	     , A.PREPAY_AMT 	\n");	
			sqlstr.append(" 	     , A.PREPAY_RATE 	\n");	
			sqlstr.append(" 	     , A.PAY_WAY_BILL 	\n");	
			sqlstr.append(" 	     , A.PAY_WAY_CASH 	\n");	
			
			sqlstr.append(" 	     , A.PREPAY_SUPPLY_AMT 	\n");	
			sqlstr.append(" 	     , A.PREPAY_SUPPLY_TAMT 	\n");	
			sqlstr.append(" 	     , A.PREPAY_SUPPLY_NAMT 	\n");	
			sqlstr.append(" 	     , A.PREPAY_SURTAX_AMT 	\n");	
			sqlstr.append(" 	     , A.CON_EST_GUAR_AMT 	\n");	
			sqlstr.append(" 	     , A.CON_EST_GUAR_RATE 	\n");	
			sqlstr.append(" 	     , A.DEF_EST_GUAR_RATE 	\n");	
			sqlstr.append(" 	     , A.DELAY_RATE 	\n");	
			sqlstr.append(" 	     , TO_CHAR(A.CON_DATE,'YYYY') AS CON_DATE_YEAR 	\n");	
			sqlstr.append(" 	     , TO_CHAR(A.CON_DATE,'MM') AS CON_DATE_MONTH 	\n");	
			sqlstr.append(" 	     , TO_CHAR(A.CON_DATE,'DD') AS CON_DATE_DAY 	\n");	
			sqlstr.append(" 	     , A.STATUS_CD 	\n");	
			sqlstr.append(" 	     , F_GET_CODE_NAME('CONT_STATUS_CD',A.STATUS_CD) AS STATUS_NM 	\n");	
			sqlstr.append(" 	     , A.MONTH_ACMP_CNT 	\n");	
			sqlstr.append(" 	     , A.OBJ_RCPT_DAY 	\n");	
			sqlstr.append(" 	     , A.TEMPLATE_CD 	\n");	
			sqlstr.append(" 	     , A.TRUE_RANDOM 	\n");
			sqlstr.append(" 	     , A.CONT_HASH 	\n");	
			sqlstr.append(" 	     , A.MODI_REQ_REASON 	\n");	
			sqlstr.append(" 	     , A.MODI_REQ_DT 	\n");	
			
			sqlstr.append(" 	     , B.DEMANDED_NO	\n");	
			sqlstr.append(" 	     , B.DEMANDED_TITLE	\n");	
			sqlstr.append(" 	     , TO_CHAR(TO_DATE(B.DEMANDED_DATE),'YYYY-MM-DD') AS DEMANDED_DATE	\n");	
			sqlstr.append(" 	     , B.REQ_DEPT_NM	\n");	
			sqlstr.append(" 	     , B.EMP_NO	\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.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(" 	     , DECODE(D.VENDCD, '', C.CORP_REG_NO, D.VENDCD) AS O_REG_NO	\n");
			sqlstr.append(" 	     , D.USER_NAME AS O_USER_NAME	\n");	
			sqlstr.append(" 	     , D.TEL_NUM AS O_TEL_NUM	\n");	
			sqlstr.append(" 	     , D.DIVISION AS O_DIVISION	\n");	
			sqlstr.append(" 	     , D.POSITION AS O_POSITION	\n");	
			sqlstr.append(" 	     , D.HP AS O_HP	\n");	
			sqlstr.append(" 	     , D.EMAIL AS O_EMAIL	\n");	
			sqlstr.append(" 	     , TO_CHAR(D.SIGN_DATE,'YYYY-MM-DD HH24:MI:SS') AS O_SIGN_DATE	\n");	
			sqlstr.append(" 	     , D.VENDCD AS O_VENDCD	\n");		        	
			sqlstr.append(" 	     , NVL(F.AFT_CORP_ID, E.CORP_ID) AS C_CORP_ID	\n");	
			sqlstr.append(" 	     , NVL(I.CORP_KOR, E.MEMBER_NAME) AS C_MEMBER_NAME	\n");	
			sqlstr.append(" 	     , NVL(I.ADDRESS, E.ADDRESS) AS C_ADDRESS	\n");	
			sqlstr.append(" 	     , NVL(I.REP_NAME, 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(" 	     , NVL(I.CORP_REG_NO, E.VENDCD) AS C_VENDCD	\n");		        	
			sqlstr.append(" 	     , E.EMAIL AS C_EMAIL	\n");	
			sqlstr.append(" 	     , TO_CHAR(E.SIGN_DATE,'YYYY-MM-DD HH24:MI:SS') AS C_SIGN_DATE	\n");			//TO_DATE(E.SIGN_DATE, 'YYYYMMDDHH24MISS')
			
			sqlstr.append("          , F.SUB_CONT_REQ_ID                                                                                   \n");
			sqlstr.append("          , F.MOD_GB                                                                                            \n");
			sqlstr.append("          , F_GET_D_CURRENCY(NVL(F.BEF_CONT_AMT,0), F_GET_CURRENCY_DIGIT(A.CON_CUR_CD)) AS BEF_CONT_AMT                \n");
			sqlstr.append("          , F_GET_D_CURRENCY(NVL(F.AFT_CONT_AMT,0), F_GET_CURRENCY_DIGIT(A.CON_CUR_CD)) AS AFT_CONT_AMT                \n");
			sqlstr.append("          , F_GET_D_CURRENCY(NVL(F.CHG_CONT_AMT,0), F_GET_CURRENCY_DIGIT(A.CON_CUR_CD)) AS CHG_CONT_AMT                \n");
//			sqlstr.append("          ,CASE WHEN NVL(BEF_BUG_AMT,0) > NVL(AFT_BUG_AMT,0) THEN F_GET_D_CURRENCY((BEF_CONT_AMT - AFT_CONT_AMT),G.DIGIT)   \n");                                                                                 
//	        sqlstr.append("            WHEN NVL(BEF_BUG_AMT,0) < NVL(AFT_BUG_AMT,0) THEN F_GET_D_CURRENCY((AFT_CONT_AMT - BEF_CONT_AMT),G.DIGIT)       \n");                                                                                  
//	        sqlstr.append("            ELSE '0'                                                                                                        \n");                                                            
//	        sqlstr.append("            END CHG_CONT_AMT                                                                                                \n");
			sqlstr.append("          , F_GET_D_CURRENCY(NVL(F.BEF_BUG_AMT,0), F_GET_CURRENCY_DIGIT(A.CON_CUR_CD)) AS BEF_BUG_AMT                  \n");
			sqlstr.append("          , F_GET_D_CURRENCY(NVL(F.AFT_BUG_AMT,0), F_GET_CURRENCY_DIGIT(A.CON_CUR_CD)) AS AFT_BUG_AMT                  \n");
			sqlstr.append("          , F_GET_D_CURRENCY(NVL(F.CHG_BUG_AMT,0), F_GET_CURRENCY_DIGIT(A.CON_CUR_CD)) AS CHG_BUG_AMT                  \n");
			sqlstr.append("       	 , CASE WHEN NVL(AFT_CONT_AMT,0) > 0THEN '도급대비: '||F_GET_D_CURRENCY(ROUND(AFT_BUG_AMT / AFT_CONT_AMT * 100,1),1)||'%'       \n");
			sqlstr.append("       	   END CHG_BUG_RATE 																											\n");
			//sqlstr.append("          , '도급대비: '||DECODE(F.AFT_CONT_AMT, 0 , 0 , ROUND(F.AFT_BUG_AMT/F.AFT_CONT_AMT*100,1))||'%' AS CHG_BUG_RATE      \n");
			sqlstr.append("          , F_GET_D_CURRENCY(NVL(F.BEF_SUPP_AMT,0), F_GET_CURRENCY_DIGIT(A.CON_CUR_CD)) AS BEF_SUPP_AMT                \n");
			sqlstr.append("          , F_GET_D_CURRENCY(NVL(F.AFT_SUPP_AMT,0), F_GET_CURRENCY_DIGIT(A.CON_CUR_CD)) AS AFT_SUPP_AMT                \n");
			sqlstr.append("          , F_GET_D_CURRENCY(NVL(F.CHG_SUPP_AMT,0), F_GET_CURRENCY_DIGIT(A.CON_CUR_CD)) AS CHG_SUPP_AMT                \n");
			//sqlstr.append("       	 , CASE WHEN NVL(AFT_BUG_AMT,0) > 0THEN '예산대비: '||F_GET_D_CURRENCY(ROUND(AFT_SUPP_AMT * NVL(H.EXCHANGE,1) / AFT_BUG_AMT * 100,1),1)||'%'  \n");			
			sqlstr.append("       	 , CASE WHEN NVL(AFT_BUG_AMT,0) > 0THEN '예산대비: '||F_GET_D_CURRENCY(ROUND(AFT_SUPP_AMT / AFT_BUG_AMT * 100,1),1)||'%' \n");
			sqlstr.append("       	   END CHG_SUPP_RATE                                                                                                    \n");
			//sqlstr.append("          , '예산대비: '||DECODE(F.AFT_CONT_AMT,0, 0, ROUND(F.AFT_SUPP_AMT/F.AFT_BUG_AMT*100,1))||'%' AS CHG_SUPP_RATE        \n");
			sqlstr.append("          , TO_CHAR(TO_DATE(F.BEF_CONT_DATE), 'YYYY-MM-DD') AS BEF_CONT_DATE                                    \n");
			sqlstr.append("          , TO_CHAR(TO_DATE(F.AFT_CONT_DATE), 'YYYY-MM-DD') AS AFT_CONT_DATE                                    \n");
			sqlstr.append("          ,CASE WHEN TO_DATE(F.BEF_CONT_DATE) IS NOT NULL AND TO_DATE(F.AFT_CONT_DATE) IS NOT NULL THEN                                                           \n");
			sqlstr.append("           CASE WHEN TO_DATE(F.BEF_CONT_DATE) - TO_DATE(F.AFT_CONT_DATE) < 0 THEN TO_NUMBER(TO_DATE(F.AFT_CONT_DATE) - TO_DATE(F.BEF_CONT_DATE)  )||'일'\n");  
			sqlstr.append("           WHEN TO_DATE(F.BEF_CONT_DATE) - TO_DATE(F.AFT_CONT_DATE) > 0 THEN TO_NUMBER(TO_DATE(F.BEF_CONT_DATE) - TO_DATE(F.AFT_CONT_DATE) ) ||'일'      \n");  
			sqlstr.append("           ELSE '0일'                                                                                                                                           \n");
			sqlstr.append("           END                                                                                                                                                    \n");
			sqlstr.append("           END CONT_DATE_NM                                                                                                                                       \n");
			//sqlstr.append("          , F.CHG_CONT_DATE||'일'    AS CHG_CONT_DATE                                                           \n");
			sqlstr.append("          , F.BEF_ETC                                                                                           \n");
			sqlstr.append("          , F.AFT_ETC                                                                                           \n");
			sqlstr.append("          , F.CHG_ETC                                                                                           \n");
			sqlstr.append("          , F.REMARK                                                                                            \n");
			sqlstr.append("          , F.SP_REMARK			                                                                               \n");
			sqlstr.append("          , F.STATUS_CD AS STATUS_CD_3000                                                                       \n");
			sqlstr.append("          , G.DIGIT                                                                                             \n");
			//2019.10.17 타절계약 추가 S
			sqlstr.append("	         , B.OUT_ITEM_CD                                                                                                            \n");
			sqlstr.append("	         , A.SUB_CONT_ID                                                                                                            \n");
			sqlstr.append("	         , (SELECT CORP_ID FROM CONT_OUT_SUPP WHERE SUB_CONT_MOD_ID = F.SUB_CONT_MOD_ID AND SIGN_SEQ = 2) AS BEF_C_CORP_ID          \n");
			sqlstr.append("	         , (SELECT VENDCD FROM CONT_OUT_SUPP WHERE SUB_CONT_MOD_ID = F.SUB_CONT_MOD_ID AND SIGN_SEQ = 2) AS BEF_C_VEND_CD           \n");			
			//2019.10.17 타절계약 추가 E	
			//2022.07.20 발주진행율 ,누적율 누계 추가
			sqlstr.append("	          , F.PO_PRC                                                                                                                                                      \n");
			sqlstr.append("	          , F.RES_PRC                                                                                                                                                     \n");			
			sqlstr.append("  FROM SUB2000T A, BID_MASTER B, HEAD_OFFICE_INFO C, CONT_OUT_SUPP D, CONT_OUT_SUPP E, SUB3000T F, BID_SUPP H, SUPP_INFO I, \n"); //2016.9.26 외주 외화계약 환율적용 
			sqlstr.append("  		(SELECT DETAILCD,DETAILNM,CHG_CODE01 AS DIGIT FROM EBID_COM911T WHERE BASECD = '911') G	\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");
			sqlstr.append("  AND A.SUB_CONT_MOD_ID = F.SUB_CONT_MOD_ID(+)	\n");
			sqlstr.append("  AND A.CON_CUR_CD = G.DETAILCD	\n");
			sqlstr.append("  AND A.BID_NO = H.BID_NO(+) AND A.BID_DEG = H.BID_DEG(+) AND H.SUCC_YN(+) =  'Y'  	\n");
			sqlstr.append("  AND F.AFT_CORP_ID = I.CORP_ID(+)  	\n");
			search.setStatement(sqlstr.toString());
			rowSet = search.execute();
			
			
			if( rowSet != null && rowSet.size() > 0 ){
				valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
			}
			
		}
		catch(WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
			//WAFLogger.debug(sqlstr.toString());
			//WAFLogger.debug("WAFSQLException : " + se.getMessage());
		}
		finally {
			
			if( valueobject == null ) valueobject = new ValueObject();
			//WAFLogger.debug("### valueobject size : " + valueobject.size());
			return valueobject;
		}
	}
	
    /** 첨부서류조회 
     * @return
     * @throws Exception
     */
    public RowSet getFileListRowSet(String id, String code) throws Exception {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sqlstr = new StringBuffer();
        try {

        	sqlstr.append(" SELECT DISTINCT  \n");
        	sqlstr.append("        A.SORT_NO,						 \n");
        	sqlstr.append("        A.DETAILCD,						 \n");
        	sqlstr.append(" 	   A.DETAILNM,								 \n");
        	sqlstr.append(" 	   A.DETAILSORTNM,							 \n");
        	sqlstr.append(" 	   B.ID AS DOC_CD,							 \n");
        	sqlstr.append(" 	   '" + id + "' AS FILE_GB,	 \n");
        	sqlstr.append(" 	   NVL(B.ID, A.DETAILCD) AS ID,	 \n");
        	sqlstr.append(" 	   A.CHG_CODE03 AS EDIT_YN	 \n");
        	sqlstr.append("	FROM EBID_COM911T A, PUB0510T B					 \n");
        	sqlstr.append("	WHERE A.BASECD = 'SUB_REQFILE_GB'	 \n");
        	sqlstr.append("	AND A.CHG_CODE02 = 'Y'			 \n");
        	sqlstr.append("	AND A.DETAILCD = B.ID(+)						 \n");
        	sqlstr.append("	AND B.FILE_GB(+) = '" + id + "'	 \n");
        	sqlstr.append("UNION ALL \n");
        	sqlstr.append("SELECT SORT_NO, \n");
        	sqlstr.append("       DETAILCD, \n");
        	sqlstr.append("       DETAILNM, \n");
        	sqlstr.append("       DETAILSORTNM,  \n");
        	sqlstr.append("       TO_NUMBER(DETAILCD) AS DOC_CD, \n");
        	sqlstr.append(" 	  '' AS FILE_GB,	 \n");
        	sqlstr.append(" 	  TO_NUMBER(DETAILCD) AS ID,	 \n");
        	sqlstr.append(" 	  CHG_CODE03 AS EDIT_YN	 \n");
        	sqlstr.append("  FROM EBID_COM911T      \n");
        	sqlstr.append(" WHERE BASECD = 'SUB_REQFILE_GB' \n");
        	sqlstr.append("   AND DETAILCD = '160'	 \n");
        	sqlstr.append("ORDER BY SORT_NO, DETAILCD \n");
    		
            search.setStatement(sqlstr.toString());
            
            listRowSet = search.execute();
            WAFLogger.debug(sqlstr.toString());
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getListRowSet() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
            if( listRowSet == null ) listRowSet = new RowSetImpl();
           
        }
        
        return listRowSet;
    }
    
	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;
	}


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

        try {
        	conn.begin();
        	saveContChgRcv(super.conn.getConnection(), params);		//계약 정보 저장

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

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



	/**
     * 변경계약접수 업데이트
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean saveContChgRcv(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        
        try {
        	
        	sqlstr.append("		UPDATE SUB3000T SET 			\n");
        	sqlstr.append("        REMARK		 	= ? 		\n");	        	        
        	sqlstr.append("      , SP_REMARK		= ? 		\n");	        	   
        	if(params.get("STATUS_CD_3000").equals("90")){
        		sqlstr.append("      , STATUS_CD	= '30' 		\n");	        	        
        	}
			if(!(params.get("p_mode_gb").equals("10") || params.get("p_mode_gb").equals("20") || params.get("p_mode_gb").equals("98"))){
			sqlstr.append("      , BEF_CONT_AMT		= ? 		\n");	        	        
			sqlstr.append("      , AFT_CONT_AMT		= ? 		\n");	        	        
			sqlstr.append("      , CHG_CONT_AMT		= ? 		\n");	        	        
			sqlstr.append("      , BEF_BUG_AMT		= ? 		\n");	        	        
			sqlstr.append("      , AFT_BUG_AMT		= ? 		\n");	        	        
			sqlstr.append("      , CHG_BUG_AMT		= ? 		\n");	        	        
			sqlstr.append("      , BEF_SUPP_AMT		= ? 		\n");	        	        
			sqlstr.append("      , AFT_SUPP_AMT		= ? 		\n");	        	        
			sqlstr.append("      , CHG_SUPP_AMT		= ? 		\n");	        	        
			}
			if(!(params.get("p_mode_gb").equals("01") || params.get("p_mode_gb").equals("02") || params.get("p_mode_gb").equals("98"))){
			sqlstr.append("      , AFT_CONT_DATE	= ? 		\n");	        	        
			sqlstr.append("      , CHG_CONT_DATE	= ? 		\n");	     
			}
			
			sqlstr.append("      , BEF_ETC			= ? 		\n");	        	   
			sqlstr.append("      , AFT_ETC			= ? 		\n");	        	   
			sqlstr.append("      , BEF_CORP_ID		= ? 		\n");	        	   
			sqlstr.append("      , AFT_CORP_ID		= ? 		\n");
			sqlstr.append("      , PO_PRC			= ? 		\n");	        	        
			sqlstr.append("      , RES_PRC			= ? 		\n");
        	sqlstr.append(" 	 , UPD_BY  			= ?  		\n");	
        	sqlstr.append(" 	 , UPD_DATE  		= SYSDATE  	\n");	
        	sqlstr.append("		WHERE  SUB_CONT_REQ_ID = "+ valueObject.get("SUB_CONT_REQ_ID") +"	\n");
        	sqlstr.append("		AND    SUB_CONT_MOD_ID = "+ valueObject.get("SUB_CONT_MOD_ID") +"	\n");
        	
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("p_remark"));										        	
            persistent.addParameter(valueObject.get("p_sp_remark"));	
            if(!(params.get("p_mode_gb").equals("10") || params.get("p_mode_gb").equals("20") || params.get("p_mode_gb").equals("98"))){
        	persistent.addParameter(valueObject.get("p_bef_cont_amt").replaceAll(",", ""));										        	
        	persistent.addParameter(valueObject.get("p_aft_cont_amt").replaceAll(",", ""));										        	
        	persistent.addParameter(valueObject.get("p_chg_cont_amt").replaceAll(",", ""));										        	
        	persistent.addParameter(valueObject.get("p_bef_bug_amt").replaceAll(",", ""));										        	
        	persistent.addParameter(valueObject.get("p_aft_bug_amt").replaceAll(",", ""));										        	
        	persistent.addParameter(valueObject.get("p_chg_bug_amt").replaceAll(",", ""));										        	
        	persistent.addParameter(valueObject.get("p_bef_supp_amt").replaceAll(",", ""));										        	
        	persistent.addParameter(valueObject.get("p_aft_supp_amt").replaceAll(",", ""));										        	
        	persistent.addParameter(valueObject.get("p_chg_supp_amt").replaceAll(",", ""));	
            }
            if(!(params.get("p_mode_gb").equals("01") || params.get("p_mode_gb").equals("02") || params.get("p_mode_gb").equals("98"))){
        	persistent.addParameter(valueObject.get("p_aft_cont_date").replaceAll("-", ""));										        	
        	persistent.addParameter(valueObject.get("p_chg_cont_date").replaceAll("일", ""));	
            }
        	persistent.addParameter(valueObject.get("p_bef_etc"));									
        	persistent.addParameter(valueObject.get("p_aft_etc"));									
        	persistent.addParameter(valueObject.get("p_bef_corp_id"));									
        	persistent.addParameter(valueObject.get("p_aft_corp_id"));
        	persistent.addParameter(valueObject.get("p_po_prc"));									
        	persistent.addParameter(valueObject.get("p_res_prc"));
        	persistent.addParameter(storage.getUser().get("acct_id"));	
        	
            persistent.execute();
            
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }	

	/**
     * 반려삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean deleteSUB3000T(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent1 = new PersistentImpl(connection);
        Persistent persistent2 = new PersistentImpl(connection);
        Persistent persistent3 = new PersistentImpl(connection);
        Persistent persistent4 = new PersistentImpl(connection);
        StrBuffer sqlstr1 = new StrBuffer();
        StrBuffer sqlstr2 = new StrBuffer();
        StrBuffer sqlstr3 = new StrBuffer();
        StrBuffer sqlstr4 = new StrBuffer();
        
        try {
        	     //2019.05.09 인터페이스테이블 삭제 
	        	sqlstr1.append("DELETE	FROM SUB3000T		            WHERE	SUB_CONT_REQ_ID = ?  \n"); 
	        	sqlstr2.append("DELETE	FROM SUB3000T_DETAIL		    WHERE	SUB_CONT_REQ_ID = ?  \n"); 
	        	sqlstr3.append("DELETE	FROM ETEC_ERPIF.SUB3000T		WHERE	SUB_CONT_REQ_ID = ?  \n"); 
	        	sqlstr4.append("DELETE	FROM ETEC_ERPIF.SUB3000T_DETAIL	WHERE	SUB_CONT_REQ_ID = ?  \n"); 
	        	
	            persistent1.setStatement(sqlstr1.toString());
	            persistent1.addParameter(valueObject.get("SUB_CONT_REQ_ID"));
	            persistent1.execute();
	            
	            persistent2.setStatement(sqlstr2.toString());
	            persistent2.addParameter(valueObject.get("SUB_CONT_REQ_ID"));
	            persistent2.execute();
	            
	            persistent3.setStatement(sqlstr3.toString());
	            persistent3.addParameter(valueObject.get("SUB_CONT_REQ_ID"));
	            persistent3.execute();
	            
	            persistent4.setStatement(sqlstr4.toString());
	            persistent4.addParameter(valueObject.get("SUB_CONT_REQ_ID"));
	            persistent4.execute(); 
	            
            return true;
        
        }catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> deleteSUB3000T() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr1);
            WAFLogger.error(e);
            //WAFLogger.debug("Exception : " + e.getMessage());
            throw e;
        }
    }
	 
	
    
	private ValueObject getDupCnt(String sign_cont_req_id, String sign_cont_mod_id) {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		ValueObject valueobject = null;
		ValueObject user = storage.getUser();
		//   String sqlstr = "";
		StrBuffer sqlstr = new StrBuffer();
		try{
			
			sqlstr.append(" SELECT  COUNT(*) AS CNT                                                                   	\n");
    		sqlstr.append(" FROM    SIGN001T                                                                     	\n");
    		sqlstr.append("	WHERE SIGN_CLASS_CD = 'ebid_ocpoD'												\n");
    		sqlstr.append("	AND SIGN_DECI_CLASS IN ('11','14')												\n");
    		sqlstr.append("	AND KEY_COL1 = '"+sign_cont_mod_id+"'	 														\n");
    		sqlstr.append("	AND KEY_COL2 = '"+sign_cont_req_id+"'	 														\n");
    		sqlstr.append("	AND ATTR_6  = '0'  																				\n");
    		//sqlstr.append("	AND CRE_BY = '"+user.get("acct_id")+"'													\n");
			
			search.setStatement(sqlstr.toString());
			rowSet = search.execute();
			
			if( rowSet != null && rowSet.size() > 0 ){
				valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
			}
			
		}
		catch(WAFSQLException se) {
			super.doException(se);
			this.storage.setSuccess(false);
			this.storage.setMessage(se.getMessage());
			storage.setDetailMessage(se);
			//WAFLogger.debug(sqlstr.toString());
			//WAFLogger.debug("WAFSQLException : " + se.getMessage());
		}
		finally {
			
			if( valueobject == null ) valueobject = new ValueObject();
			//WAFLogger.debug("### valueobject size : " + valueobject.size());
			return valueobject;
		}
	}
	
	/**
	 * 
	 * @param SUB_CONT_REQ_ID
	 * @return
	 */
	private ValueObject getSignInfo(String SUB_CONT_REQ_ID) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject vo = new ValueObject();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{
			sqlstr.append("   SELECT                                                                                            \n");
			sqlstr.append("   	    MAX(B.SIGN_INTERFACE_ID) AS SIGN_INTERFACE_ID                                               \n");
			sqlstr.append("   	   ,B.SIGN_CLASS_CD                                                                           	\n");
			sqlstr.append("   	   ,A.SUB_CONT_REQ_ID                                                                           \n");
			sqlstr.append("   	   ,A.SUB_CONT_MOD_ID                                                                           \n");
			sqlstr.append("   	   ,MAX(B.DOC_ID) AS DOC_ID                                                                     \n");
			sqlstr.append("   	   ,MAX(TO_CHAR(B.CRE_DATE,'YYYYMMDD')) YEAR                                                     \n");
			sqlstr.append("   	   ,B.SIGN_DECI_CLASS                                                                     		\n");
			sqlstr.append("   FROM SUB3000T A,                                                                                  \n");
			sqlstr.append("        SIGN001T B                                                                             \n");
			sqlstr.append("   WHERE A.SUB_CONT_REQ_ID = REGEXP_REPLACE(B.KEY_COL1,'[^0-9]')                                     \n");
			sqlstr.append("   AND   A.SUB_CONT_MOD_ID = REGEXP_REPLACE(B.KEY_COL2,'[^0-9]')                                     \n");
			sqlstr.append("   AND   B.SIGN_CLASS_CD = 'ocreqD'                                                   				\n");
			sqlstr.append("   AND   B.SIGN_DECI_CLASS = '14'                                                   					\n");
			sqlstr.append("   AND   A.SUB_CONT_REQ_ID = '"+SUB_CONT_REQ_ID+"'                                                   \n");
			//sqlstr.append("   AND   B.DOC_ID IS NOT NULL                                                                        \n");
			sqlstr.append("   GROUP BY B.SIGN_CLASS_CD,A.SUB_CONT_REQ_ID,                                                      	\n");
			sqlstr.append("   			A.SUB_CONT_MOD_ID,B.SIGN_DECI_CLASS                                                     \n");
        	
        	search.setStatement(sqlstr.toString());
            System.out.println(sqlstr.toString());
        	rowSet = search.execute();
            if(rowSet.size()>0){
            	vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
        	if( vo == null ) vo = new ValueObject();
			return vo;
        }
	}	
}
