/**
 * @(#)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.purchase.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 SubconPayGuarDetailBiz extends Biz {

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

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

		boolean success = false;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
		
		if(storage.compareEvent("")){
			
		}else if(storage.compareEvent("INSERT_DATA")){
			//1.계약정보
			user = getContValueObject();
            storage.addValueObject("Cont", user);
			//3.보증금계산
			user = getGuarValueObject();
            storage.addValueObject("guar", user);
		}else if(storage.compareEvent("UPDATE_DATA")){ 
			//1.계약정보
			user = getContValueObject();
            storage.addValueObject("Cont", user);
			//3.보증금계산
			user = getGuarGetValueObject();
            storage.addValueObject("guar", user);
		}else if(storage.compareEvent("GUAR_SAVE")){
			Persistent persistent = null;
			StringBuffer sql = new StringBuffer();
					
			try {
				conn.begin();
				persistent = new PersistentImpl(super.conn.getConnection());
				sql.append("MERGE INTO CONT_PUR_WARR T1 USING (			                \n");
	        	sql.append("          SELECT   ?	PUR_CONT_MOD_ID		                \n");
				sql.append("		         , '2'	WARR_GUBUN				            \n");
				sql.append("		         , ?	WARR_SEQ			                \n");
				sql.append("		         , ?	PUR_CONT_ID		                    \n");
				sql.append("		         , ?	MOD_NO				                \n");
				sql.append("		         , ?	CAUSECODE				            \n");				
				sql.append("		         , ?	CONTAMT		                        \n");
				sql.append("		         , ?	CONTSTARTDATE				        \n");
				sql.append("		         , ?	CONTCLOSEDATE				        \n");
				sql.append("		         , ?	FILESIZE				            \n");
				sql.append("		         , ?	ADDPREPAYAMT				        \n");
				sql.append("		         , ?	WARR_WAMT				            \n");
				sql.append("                 , (SELECT NVL(MAX(WARR_SEQ),0) + 1 FROM CONT_PUR_WARR WHERE PUR_CONT_MOD_ID = ? ) NEW_SEQ  \n");
	        	sql.append("		        FROM DUAL									\n");
	        	sql.append("		    ) T ON (T.PUR_CONT_MOD_ID = T1.PUR_CONT_MOD_ID AND T.WARR_GUBUN = T1.WARR_GUBUN AND T.WARR_SEQ = T1.WARR_SEQ  )	\n");
	        	sql.append("		WHEN MATCHED THEN									\n");
	        	sql.append("		    UPDATE SET 				                        \n");
	        	sql.append("  				   T1.CONTAMT	    =	 T.CONTAMT	        \n");
	        	sql.append("  				 , T1.CONTSTARTDATE =	 T.CONTSTARTDATE	\n");
	        	sql.append("  				 , T1.CONTCLOSEDATE =	 T.CONTCLOSEDATE	\n");
	        	sql.append("  				 , T1.FILESIZE	    =	 T.FILESIZE	        \n");
	        	sql.append("  				 , T1.ADDPREPAYAMT  =	 T.ADDPREPAYAMT	    \n");
	        	sql.append("  				 , T1.WARR_WAMT	    =	 T.WARR_WAMT	    \n");
	        	sql.append("  				 , T1.GUAR_STATUS	=	 '01'       	    \n");
	        	sql.append("		         , T1.WARR_INDAY	=	 SYSDATE	        \n");
				sql.append("		         , T1.WARR_UPDAY	=	 SYSDATE	        \n");
	        	sql.append("		WHEN NOT MATCHED THEN								\n");
	        	sql.append("			INSERT (								        \n");
				sql.append("		           T1.PUR_CONT_MOD_ID		                \n");
				sql.append("		         , T1.WARR_GUBUN				            \n");
				sql.append("		         , T1.WARR_SEQ			                    \n");
				sql.append("		         , T1.PUR_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.CONTAMT		                        \n");
				sql.append("		         , T1.CONTSTARTDATE				            \n");
				sql.append("		         , T1.CONTCLOSEDATE				            \n");
				sql.append("		         , T1.FILESIZE		                        \n");
				sql.append("		         , T1.ADDPREPAYAMT		                    \n");
				sql.append("		         , T1.WARR_WAMT		                        \n");
				sql.append("		         , T1.GUAR_ORGAN_CODE	                    \n");
				sql.append("		       ) 		                                    \n");
				sql.append("		       VALUES 		                                \n");
				sql.append("		       ( 					                        \n");
				sql.append("		           T.PUR_CONT_MOD_ID	                    \n");
				sql.append("		         , T.WARR_GUBUN			                    \n");
				sql.append("		         , T.NEW_SEQ      		                    \n");
				sql.append("		         , T.PUR_CONT_ID		                    \n");
				sql.append("		         , T.MOD_NO				                    \n");
				sql.append("		         , ''		                                \n");
				sql.append("  				 , 'N'	 	                                \n");
				sql.append("		         , '01'      		                        \n");//T.CAUSECODE
				sql.append("  				 , '01'	 	                                \n");
				sql.append("		         , SYSDATE		                            \n");
				sql.append("		         , SYSDATE		                            \n");
				sql.append("		         , T.CONTAMT		                        \n");
				sql.append("		         , T.CONTSTARTDATE				            \n");
				sql.append("		         , T.CONTCLOSEDATE				            \n");
				sql.append("		         , T.FILESIZE		                        \n");
				sql.append("		         , T.ADDPREPAYAMT		                    \n");
				sql.append("		         , T.WARR_WAMT		                        \n");
				sql.append("  				 , '4'	 	                                \n");
				sql.append("		       ) 		                                    \n");			

				persistent.setStatement(sql.toString());
				
				persistent.addParameter(params.get("PUR_CONT_MOD_ID"));
				persistent.addParameter(params.get("WARR_SEQ"));
				persistent.addParameter(params.get("PUR_CONT_ID"));
				persistent.addParameter(params.get("MOD_NO"));
				persistent.addParameter(params.get("CAUSECODE"));
				persistent.addParameter(params.get("CON_AMT").replaceAll(",", ""));  
				persistent.addParameter(params.get("START_WORK_DATE").replaceAll("-", ""));
				persistent.addParameter(params.get("END_WORK_DATE").replaceAll("-", ""));
				persistent.addParameter(params.get("FILESIZE"));
				persistent.addParameter(params.get("ADDPREPAYAMT").replaceAll(",", ""));
				persistent.addParameter(params.get("WARR_WAMT").replaceAll(",", ""));
				persistent.addParameter(params.get("PUR_CONT_MOD_ID"));
				persistent.execute();
				persistent.clearParameters();
				super.storage.setSuccess(true);
			}catch(Exception e) {
		        super.doException(e);
		        super.storage.setMessage(e.getMessage());
		        super.storage.setDetailMessage(e);
		        super.conn.setRollback();
		      	super.storage.setSuccess(false);
		    }
		    finally {
		       	super.conn.end();
		    }
		}else if(storage.compareEvent("PAYMENT_SHEET")){
			//2.잔여기성검색
			params.set("sheet2", getPaymentList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("GUAR_SHEET")){
			//4.보증서목록
			params.set("sheet1", getWarrList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}	
	}
     
	//계약정보
	private ValueObject getContValueObject() {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        StrBuffer sqlstr = new StrBuffer();
        try{
            sqlstr.append("    SELECT A.PUR_CONT_ID                                                   \n");
		    sqlstr.append("          ,A.PUR_CONT_MOD_ID                                               \n");
		    sqlstr.append("          ,A.MOD_NO                                                        \n");
		    sqlstr.append("          ,ORD_DOC_NO CON_DOC_NO                                                      \n");
		    sqlstr.append("          ,CST_DOC_NAME BUILD_NAME                                                      \n");
		    sqlstr.append("          ,TO_CHAR(ORD_CON_DATE,'yyyy-mm-dd') CON_DATE                         \n");
		    sqlstr.append("          ,TO_CHAR(ORD_START_DATE ,'yyyy-mm-dd') START_WORK_DATE          \n");
		    sqlstr.append("          ,TO_CHAR(ORD_END_DATE ,'yyyy-mm-dd')  END_WORK_DATE             \n");
		    sqlstr.append("          ,TO_CHAR(ORD_AMT,'999,999,999,999')   CON_AMT                    \n");
		    sqlstr.append("          ,B.MEMBER_NAME                                                   \n");
		    sqlstr.append("          ,DEPT_NAME                                                       \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("          ,NVL(A.MOD_GB,'01') AS CAUSECODE                                 \n");
		    sqlstr.append("     FROM PUR2000T A                                                       \n");
		    sqlstr.append("         ,CONT_PUR_SUPP B                                                  \n");
		    sqlstr.append("    WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID                            \n");
		    sqlstr.append("      AND B.SIGN_SEQ = 2                                                   \n");
		    sqlstr.append("      AND A.PUR_CONT_MOD_ID = '" +params.get("PUR_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);
        }
        finally {
        	if( valueobject == null ) valueobject = new ValueObject();
            return valueobject;
        }
	}
	
	/**
	 * 잔여기성조회
	 * @param Params
	 * @return
	 */
	private RowSet getPaymentList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = null;
        String sqlstr = "";
        try{  
        	sqlstr += " SELECT A.DEPT_NAME,                                                                                 --현장명								\n"
    			+ "		  A.CST_DOC_NAME BUILD_NAME,                                                                                     --업체명							\n"
    			+ "       A.ORD_DOC_NO CON_DOC_NO,                                                                                     								\n"
    			+ "       A.CURRENCY_GB AS CURRENCY_GB,                                                                                     								\n"
    			+ "       F_GET_CURRENCY_DIGIT(A.CURRENCY_GB) AS CURRENCY_DIGIT,                                                                                     								\n"
    			+ "       A.PUR_CONT_ID,                                                                                     								\n"
    			+ "       D.MEMBER_NAME,                                                                                     								\n"
    			+ "       TO_CHAR(A.ORD_START_DATE ,'YYYY-MM-DD')||' ~ ' || TO_CHAR(A.ORD_END_DATE ,'YYYY-MM-DD') AS START_END_WORK_DATE, --공사기간  		\n"                     
    			+ "       A.ORD_SUPPLY_AMT CON_SUPPLY_AMT,   --계약공급가                                                             															\n"
    			+ "       A.ORD_SURTAX_AMT CON_SURTAX_AMT,   --계약부가세                                                      																\n"
    			+ "       A.ORD_AMT CON_AMT,          --계약합계 																										\n"
    			+ "       A.PREPAY_AMT PREPAY_SUPPLY_AMT  ,       -- 계약상선급금																							\n"
    			+ "       NVL(C.REQ_PREPAY_SUPPLY_AMT,0) AS REQ_PREPAY_SUPPLY_AMT, --     선급금 발생																							\n"
    			+ "       TO_CHAR(TO_DATE(C.TAX_DY, 'YYYYMMDD') , 'YYYY-MM-DD') AS PREPAY_DATE , --선급금 신청날짜                						            \n"              
    			+ "       TO_CHAR(B.REQ_DATE, 'YYYY-MM') AS ACMP_YYYYMM,                     --기성년월   						 		\n"
    			+ "       B.DEPOSIT_SEQ ACMP_NO,  --지급차수                                                                        															\n"
    			+ "       B.CUR_ACMP_APPLYAMT FUND_AMT, --금회기성    																											\n"
    			+ "       B.CUR_PREPAY_APPLYAMT PAY_DEDUCT_AMT,   --선급금공제																										\n"
    			+ "       B.CUR_REQ_AMT REQU_SUPPLY_AMT,   -- 실지불액                                                             															\n"
    			+ "       B.REMAINDER_ACMP_SUPPLYAMT AS MOD_FUND_AMT,   --잔여기성                                						    			\n"
    			+ "       TO_CHAR(ROUND(B.CUR_ACMP_PERCENT,2), 'FM999.00') FUND_RATE,--기성율       									\n"
    			+ "       B.REQ_STATUS AS STATUS_CD    ,        --상태																										\n"
    			+ "       B.PAYREQ_ID ACMP_ID                                                            												       			\n"
    			+ " FROM PUR1000T A,  PUR1100T B                                                          						      		\n"
    			+ "		 , ( SELECT SUM(NVL(REQ_PREPAY_SUPPLY_AMT,0)) AS REQ_PREPAY_SUPPLY_AMT                                                      						      											\n"
    			+ "		           , PUR_CONT_ID, MIN(TAX_DY) AS TAX_DY                                                    						      											\n"
    			+ "		      FROM PUR1030T                                                        						      											\n"
    			+ "		      WHERE PREPAY_PROC_GB = '10'                                                   						      											\n"
    			+ "		      GROUP BY PUR_CONT_ID                                                       						      											\n"
    			+ "		    )    C                                                         						      											\n"
    			+ "		, (                                                          						      											\n"
    			+ " 			SELECT X.PUR_CONT_ID , MAX(Y.MEMBER_NAME) AS MEMBER_NAME                                                          			\n"
    			+ " 			FROM PUR2000T X, CONT_PUR_SUPP Y                                                          						      		\n"
    			+ " 			WHERE X.PUR_CONT_MOD_ID = Y.PUR_CONT_MOD_ID                                                          						\n"
    			+ " 			AND SIGN_SEQ = 2                                                          										      		\n"
    			+ "  			GROUP BY PUR_CONT_ID                                                          									      		\n"
    			+ "  	) D                                                           						      											\n"        			
    			+ " WHERE 1=1                                                                                        										\n"
    			+ " AND A.PUR_CONT_ID = B.PUR_CONT_ID       																								\n"
    			+ " AND A.PUR_CONT_ID = C.PUR_CONT_ID(+)                                                       												\n"
    			+ " AND A.PUR_CONT_ID = D.PUR_CONT_ID                                                       												\n"
    			+ " AND A.CON_RUN_STS = '9'  																													\n"
                + " AND A.PUR_CONT_ID = '" + params.get("PUR_CONT_ID") + "'														\n"
    	        + "  ORDER BY A.DEPT_NAME, A.CST_DOC_NAME, B.REQ_DATE    												\n";
        search.setStatement(sqlstr); 
        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;
	}
	
	private ValueObject getGuarValueObject() {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        StrBuffer sqlstr = new StrBuffer();
        try{
        	if (params.get("MOD_NO").equals("0")){
        		sqlstr.append("    SELECT ORD_AMT CON_AMT                                                           \n");
    		    sqlstr.append("          ,TO_CHAR(ORD_CON_DATE, 'YYYY-MM-DD') CON_DATE                          \n");
    		    sqlstr.append("          ,TO_CHAR(ORD_START_DATE, 'YYYY-MM-DD') START_WORK_DATE            \n");
    		    sqlstr.append("          ,TO_CHAR(ORD_END_DATE, 'YYYY-MM-DD') END_WORK_DATE                \n");
    		    sqlstr.append("          ,'' FILESIZE                                        \n");
    		    sqlstr.append("          ,PREPAY_AMT ADDPREPAYAMT                                           \n");
    		    sqlstr.append("          ,'미발급'  WARR_STATUS                                              \n");   
    		    sqlstr.append("          ,0 WARR_SEQ                                                        \n");   
    		    sqlstr.append("     FROM PUR2000T A                                                         \n");
    		    sqlstr.append("    WHERE A.PUR_CONT_MOD_ID = '" +params.get("PUR_CONT_MOD_ID") + "'         \n");
        	}else{
        		sqlstr.append("  SELECT  MAX(A.ORD_AMT)-SUM( NVL(B.CUR_ACMP_APPLYAMT,0)) CON_AMT    \n");                                        
        		sqlstr.append("        ,TO_CHAR(MAX(A.ORD_CON_DATE), 'YYYY-MM-DD') CON_DATE                     \n");     
        		sqlstr.append("        ,TO_CHAR(MAX(A.ORD_START_DATE), 'YYYY-MM-DD') START_WORK_DATE       \n");
    		    sqlstr.append("        ,TO_CHAR(MAX(A.ORD_END_DATE), 'YYYY-MM-DD') END_WORK_DATE           \n");                                                 
        		sqlstr.append("        ,'' FILESIZE                                   \n");                                              
        		sqlstr.append("        ,MAX(NVL(C.PREPAY_AMT,0)) - SUM(NVL(B.PAY_DEDUCT_TAMT,0)*1.1)- SUM(NVL(B.PAY_DEDUCT_NAMT,0)) ADDPREPAYAMT     \n");      
        		sqlstr.append("        ,'미발급'  WARR_STATUS                                                \n");                                              
        		sqlstr.append("        ,0 WARR_SEQ                                                          \n");   
    		    sqlstr.append("    FROM PUR2000T A                                                          \n");                                                      
        		sqlstr.append("        ,PUR1100T B                                                          \n");       
        		sqlstr.append("        ,(SELECT SUM(PREPAY_AMT) PREPAY_AMT                                  \n");   
        		sqlstr.append("  	       FROM PUR1030T                                                    \n");   
        		sqlstr.append("  	      WHERE PUR_CONT_ID = " +params.get("PUR_CONT_ID") + "              \n");   
        		sqlstr.append("   	        AND PREPAY_PROC_GB = '10' ) C                                        \n");                                                
        		sqlstr.append("    WHERE A.PUR_CONT_ID = B.PUR_CONT_ID(+)                                      \n");   
        		sqlstr.append("      AND A.PUR_CONT_MOD_ID = " +params.get("PUR_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);
        }
        finally {
        	if( valueobject == null ) valueobject = new ValueObject();
            return valueobject;
        }
	}
	
	private ValueObject getGuarGetValueObject() {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        StrBuffer sqlstr = new StrBuffer();
        try{
        	
        	sqlstr.append("  SELECT PUR_CONT_MOD_ID                          \n");
        	sqlstr.append("        ,WARR_GUBUN                               \n");
        	sqlstr.append("        ,WARR_SEQ                                 \n");         
        	sqlstr.append("        ,CONTAMT       CON_AMT                    \n");           
        	sqlstr.append("        ,CONTDATE      CON_DATE                   \n");           
        	sqlstr.append("        ,TO_CHAR(TO_DATE(CONTSTARTDATE, 'yyyymmdd'),'YYYY-MM-DD') START_WORK_DATE            \n");            
        	sqlstr.append("        ,TO_CHAR(TO_DATE(CONTCLOSEDATE, 'yyyymmdd'),'YYYY-MM-DD')  END_WORK_DATE             \n");             
        	sqlstr.append("        ,FILESIZE                                 \n");          
        	sqlstr.append("        ,ADDPREPAYAMT                             \n");            
        	sqlstr.append("        ,WARR_WAMT                                \n"); 
        	sqlstr.append("        ,DECODE(NVL(GUAR_STATUS, '00'), '09', '완료', '01','작성중', '미신청') WARR_STATUS	                                                     \n");      
        	sqlstr.append("    FROM CONT_PUR_WARR                            \n");           
            sqlstr.append("   WHERE PUR_CONT_MOD_ID = '"+params.get("PUR_CONT_MOD_ID")+"'	\n");	
            sqlstr.append("     AND WARR_GUBUN = '2'                      	 \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);
        }
        finally {
        	if( valueobject == null ) valueobject = new ValueObject();
            return valueobject;
        }
	}
	/**
	 * 보증 대상리스트 조회
	 * @param Params
	 * @return
	 */
	private RowSet getWarrList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        /*
		보증서 종류
		  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_PUR_WARR WHERE PUR_CONT_MOD_ID = '"+ params.get("PUR_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_PUR_WARR 	\n");
        	sqlstr.append("	               WHERE PUR_CONT_MOD_ID = '"+ param.get("PUR_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,  PUR2000T B 	\n");
        	sqlstr.append("	               WHERE B.PUR_CONT_MOD_ID = '"+ param.get("PUR_CONT_MOD_ID") +"' 	\n");
        	sqlstr.append("	                     AND  (SELECT COUNT(*) FROM CONT_PUR_WARR W WHERE  W.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID AND W.WARR_GUBUN = 2)  = 0 	\n");
        	sqlstr.append("	                     AND B.ORD_AMT > 10000000	\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;
	}	
}