/**
 * @(#)file    ModStatusBiz.java
 * @(#)author  hoonseok choi
 * @(#)date    2021.3.3
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package kr.co.udapsoft.ebid.buyer.test.biz;

import kr.co.hsnc.common.base.WAFLogger;
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.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 oracle.toplink.internal.ox.StrBuffer;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.waf.common.ui.InitCombo;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;


public class ModStatusBiz extends Biz {

	public ModStatusBiz(HandlerStorage storage) {
		super(storage);
	}
	
	public void doBiz() throws Exception {
		if(storage.compareEvent("")){
			ValueObject vo = getModStatusList();			
			ValueObject vo2 = get_sheetCombo("ACMP_STATUS_CD");
			ValueObject vo3 = get_sheetCombo("PURCHASE_STATUS_CD");
	        storage.addValueObject("modStatusListRowSet", vo);
			storage.addObject("selectText", vo2.getObject("DETAILCD"));
			storage.addObject("selectValue", vo2.getObject("DETAILNM"));
			storage.addObject("selectText2", vo3.getObject("DETAILCD"));
			storage.addObject("selectValue2", vo3.getObject("DETAILNM"));
		}else if(storage.compareEvent("SAVE_SEL_CNT")){
			ValueObject vo = selSaveCnt();
	        storage.addValueObject("modSaveCnt", vo);
		}else if(storage.compareEvent("DEL_SEL_CNT")){
			ValueObject vo = selDelCnt();
	        storage.addValueObject("modDelCnt", vo);
		}else if(storage.compareEvent("MOD_BID_STATUS")){
			params.set("sheet1", getBoardList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("MOD_ERP_STS")){
			params.set("sheet2", getBoardList2(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("MOD_TEMPLATE")){
			params.set("sheet3", getTemplateList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("PUR_CONT_STATUS")){
			params.set("sheet4", getPurContStatus(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("SUB_CONT_STATUS")){
			params.set("sheet5", getSubContStatus(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("SUB_PAY_STATUS")){
			params.set("sheet6", getSubPayStatus(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("PUR_PAY_STATUS")){
			params.set("sheet7", getPurPayStatus(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
	}

	
	private ValueObject getModStatusList() {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();
        ValueObject vo = new ValueObject();
        try {
        	sql.append(" SELECT DETAILCD AS ADMIN_AUTH_ID         	\n");
        	sql.append("   FROM EBID_COM911T       	\n");
        	sql.append("  WHERE BASECD = 'ADMIN_MOD_ID'       	\n");
        	
        	search.setStatement(sql);
        	WAFLogger.debug(sql.toString());
            rowSet = search.execute();
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            return vo;
        }
        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;
        }
	}
	
	private ValueObject selSaveCnt() {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();
        ValueObject vo = new ValueObject();
        try {
        	sql.append("SELECT COUNT(*) AS CNT	\n");
        	sql.append("FROM "+params.get("TABLE_NM")+"	\n");
        	sql.append("WHERE "+params.get("WHERE")+" = '"+params.get("WHERE_VAL")+"'     	\n");        	
    		if(!params.get("AND_1").isEmpty())		sql.append("AND "+params.get("AND_1")+" = '"+params.get("AND_1_VAL")+"'     	\n");
    		if(!params.get("AND_2").isEmpty())		sql.append("AND "+params.get("AND_2")+" = '"+params.get("AND_2_VAL")+"'     	\n");
    		if(!params.get("AND_3").isEmpty())		sql.append("AND "+params.get("AND_3")+" = '"+params.get("AND_3_VAL")+"'     	\n");
        	
        	search.setStatement(sql);
        	WAFLogger.debug(sql.toString());
            rowSet = search.execute();
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            return vo;
        }
        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;
        }
	}
	
	private ValueObject selDelCnt() {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();
        ValueObject vo = new ValueObject();
        try {
        	sql.append("SELECT COUNT(*) AS CNT	\n");
        	sql.append("FROM "+params.get("DEL_TABLE_NM")+"	\n");
        	sql.append("WHERE "+params.get("DEL_WHERE")+" = '"+params.get("DEL_WHERE_VAL")+"'     	\n");        	
    		if(!params.get("DEL_AND_1").isEmpty())		sql.append("AND "+params.get("DEL_AND_1")+" = '"+params.get("DEL_AND_1_VAL")+"'     	\n");
    		if(!params.get("DEL_AND_2").isEmpty())		sql.append("AND "+params.get("DEL_AND_2")+" = '"+params.get("DEL_AND_2_VAL")+"'     	\n");
    		if(!params.get("DEL_AND_3").isEmpty())		sql.append("AND "+params.get("DEL_AND_3")+" = '"+params.get("DEL_AND_3_VAL")+"'     	\n");
        	
        	search.setStatement(sql);
        	WAFLogger.debug(sql.toString());
            rowSet = search.execute();
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            return vo;
        }
        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;
        }
	}
	
	/**
	 * 입찰정보를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private RowSet getBoardList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{ 
        	sqlstr.append(" SELECT DISTINCT A.BID_DEG																															\n");
        	sqlstr.append("      , B.DEPT_CD																																\n");
        	sqlstr.append("      , B.DEPT_NM																																\n");
        	sqlstr.append("      , A.BID_NO																																\n");
        	sqlstr.append("      , A.DEMANDED_TITLE																													\n");
        	sqlstr.append("      , A.BID_MASTER_NM																													\n");
        	sqlstr.append("      , A.ONBID_YN																															\n");
        	sqlstr.append("      , A.STATUS_CD 																															\n");
        	sqlstr.append("      , F_GET_CODE_NAME('BID_STATUS_CD',A.STATUS_CD) AS STATUS_NM													\n");	
        	sqlstr.append("      , A.DEMANDED_ID		 																												\n");
        	sqlstr.append("      , TO_CHAR(A.OPEN_DT,'YYYY-MM-DD HH24:MI') AS OPEN_DT															\n");
        	sqlstr.append("      , A.OPEN_EMPNM		 																												\n");
        	sqlstr.append("      , A.NOTI_DY				 																												\n");
        	sqlstr.append("      , C.VENDOR_NAME				 																												\n");
        	sqlstr.append("      , C.VENDOR_CD				 																												\n");
        	sqlstr.append("      , C.BID_STATUS_CD				 																												\n");
        	sqlstr.append("      , F_GET_CODE_NAME('BID_RESOL_CD',C.BID_STATUS_CD) AS BID_STATUS_NM																\n");
        	sqlstr.append("      , A.CURRENCY_GB				 																												\n");
        	sqlstr.append("      , C.EXCHANGE				 																												\n");
        	sqlstr.append("      , A.DEMANDED_KIND				 																												\n");
        	sqlstr.append("      , DECODE(A.DEMANDED_KIND,'O','외주','구매') DEMANDED_KIND_NM				 																												\n");
        	sqlstr.append("      , D.EVAL_EMPNM				 																												\n");
        	sqlstr.append("      , D.STATUS_CD AS TBE_STATUS_CD				 																												\n");
        	sqlstr.append("      , F_GET_CODE_NAME('TBE_LIST_STATUS',D.STATUS_CD) AS TBE_STATUS_NM																	\n");
        	sqlstr.append("   FROM BID_MASTER A	, ACZ10100V B , BID_SUPP C, TBE_INFO D   																			\n");
        	sqlstr.append("   WHERE A.DEPT_CD = B.DEPT_CD																										\n");
        	sqlstr.append("     AND A.BID_NO = C.BID_NO(+)																										\n");
        	sqlstr.append("     AND A.BID_DEG = C.BID_DEG(+) 																										\n");
        	if(!param.get("P_DEPT_CD").isEmpty())
        		sqlstr.append("   AND A.DEPT_CD = '"+params.get("P_DEPT_CD")+"'																	\n");
        	if(!param.get("P_BID_MASTER_NM").isEmpty())
        		sqlstr.append("   AND UPPER(A.BID_MASTER_NM) LIKE '%'||'"+param.get("P_BID_MASTER_NM").toUpperCase()+"' ||'%'																	\n");
        	if(!param.get("P_DEMANDED_STATUS").isEmpty())
        		sqlstr.append("   AND A.STATUS_CD = '"+param.get("P_DEMANDED_STATUS")+"'																						\n");
        	if(!param.get("P_BID_NO").isEmpty())
        		sqlstr.append("   AND UPPER(A.BID_NO) LIKE UPPER('%'|| '"+param.get("P_BID_NO")+"' ||'%')							    \n");
        	sqlstr.append("     AND A.BID_NO = D.BID_NO(+)																										\n");
        	sqlstr.append("     AND A.BID_DEG = D.BID_DEG(+) 																										\n");
        	sqlstr.append("   ORDER BY B.DEPT_CD,A.BID_NO,A.BID_DEG DESC																												\n");
			WAFLogger.debug(sqlstr.toString());
        	search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}
	
	/**
	 * ERP결재정보를 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private RowSet getBoardList2(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
     //   String sqlstr = "";
        StrBuffer sqlstr = new StrBuffer();
        try{ 
        	sqlstr.append(" SELECT DECODE(SIGN_CLASS_CD,'ERPIU_prD','구매발주의뢰','ERPIU_pcreqD','구매 변경 계약','ERPIU_pspyD','입고등록(계획분할)','ERPIU_stdD','입고등록(납품)','ERPIU_ocreqD','외주발주의뢰','ERPIU_opycD','외주 기성 등록',SIGN_CLASS_CD) AS SIGN_CLASS_NM				\n");
        	sqlstr.append("      , DECODE(SIGN_DECI_CLASS,'11','기안','12','반려','13','회수','14','완료','21','기안(수신부서)','22','반려(수신부서)','23','회수(수신부서)','24','완료(수신부서)','25','기안부서로 회송') AS SIGN_DECI_CLASS_NM												\n");
        	sqlstr.append("      , SIGN_CLASS_CD, SIGN_INTERFACE_ID, DOC_ID, SIGN_DECI_CLASS, RK																															\n");
        	sqlstr.append("   FROM (																																	\n");
        	sqlstr.append("      select A.*, ROW_NUMBER() OVER(PARTITION BY A.KEY_COL1,A.KEY_COL2,A.KEY_COL3,A.KEY_COL4,A.KEY_COL5,A.KEY_COL6 ORDER BY A.CRE_DATE DESC) AS RK					\n");
        	sqlstr.append("        from SIGN001T@ERPIU A																																\n");
        	sqlstr.append("      WHERE KEY_COL1 = '"+param.get("KEY_COL1")+"'																													\n");
        	sqlstr.append("      AND KEY_COL2 = '"+param.get("KEY_COL2")+"'																													\n");
        	sqlstr.append("      AND KEY_COL3 = '"+param.get("KEY_COL3")+"'																															\n");
        	sqlstr.append("      AND KEY_COL4 = '"+param.get("KEY_COL4")+"'																															\n");
        	sqlstr.append("      AND KEY_COL5 = '"+param.get("KEY_COL5")+"'													\n");
        	if(!param.get("KEY_COL6").isEmpty()){
        		sqlstr.append("      AND KEY_COL6 = '"+param.get("KEY_COL6")+"'		 																												\n");
        	}        	
        	sqlstr.append("      AND SIGN_DECI_CLASS IS NOT NULL)															\n");
        	sqlstr.append("  WHERE RK = '1' 																												\n");

			WAFLogger.debug(sqlstr.toString());
        	search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}
	
	private RowSet getTemplateList(ValueObject param){
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();
        ValueObject vo = new ValueObject();
        try {
        	sql.append(" SELECT TEMPLATE_CD,SEQ,JOB_DIV,DECODE(JOB_DIV,'P','구매','O','외주','G','보증',JOB_DIV) JOB_DIV_NM, TEMPLATE_NAME, TEMPLATE_STYLE, CHG_CONT_YN, OPTION_YN         	\n");
        	sql.append("   FROM CONT_TEMPLATE       	\n");
        	sql.append("  ORDER BY JOB_DIV DESC,TEMPLATE_CD,SEQ        	\n");
        	
        	WAFLogger.debug(sql.toString());
        	search.setStatement(sql.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;
	}
	
	private RowSet getPurContStatus(ValueObject param){
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();
        ValueObject vo = new ValueObject();
        try {
        	sql.append(" SELECT A.PUR_CONT_ID,A.PUR_CONT_MOD_ID, A.DEPT_CD, A.DEPT_NAME, A.ORD_DOC_NO, A.CST_DOC_NAME, PAY_DIV, DECODE(A.PAY_DIV, '1', '납품정산', '계획분할') PAY_DIV_NM, A.MOD_NO, A.STATUS_CD, F_GET_CODE_NAME('CONT_STATUS_CD', A.STATUS_CD) AS STATUS_CD_NM         	\n");
        	sql.append("      , B.SEQ, B.TEMPLATE_NAME                                                                                              	\n");
        	sql.append("      , C.WARR_GUBUN, C.WARR_SEQ, C.GUAR_ONOFF_YN , C.GUAR_STATUS, F_GET_CODE_NAME('GUAR_STATUS', C.GUAR_STATUS) AS GUAR_STATUS_NM , C.GUAR_STATUS, F_GET_CODE_NAME('GAUR_KIND', C.WARR_GUBUN) AS GAUR_KIND_NM                                                           	\n");
        	sql.append("   FROM PUR2000T A, CONT_PUR_HTML B, CONT_PUR_WARR C           		\n");
        	sql.append("  WHERE A.DEPT_CD = '"+params.get("P_DEPT_CD2")+"'        			\n");
        	sql.append("    AND A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID       			  	\n");
        	sql.append("    AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID(+)					\n");
			if(!param.get("P_ORD_DOC_NO").isEmpty()){
				sql.append("     AND A.ORD_DOC_NO = '"+params.get("P_ORD_DOC_NO")+"'        \n");
			}
			if(!param.get("P_CST_DOC_NAME").isEmpty()){
				sql.append("     AND A.CST_DOC_NAME = '"+params.get("P_CST_DOC_NAME")+"'    \n");
			}
			if(!param.get("P_MOD_NO").isEmpty()){
				sql.append("     AND A.MOD_NO = '"+params.get("P_MOD_NO")+"'      \n");
			}
        	sql.append("  ORDER BY A.PUR_CONT_ID, A.PUR_CONT_MOD_ID, A.MOD_NO, B.SEQ, C.WARR_SEQ         	\n");
        	
        	WAFLogger.debug(sql.toString());
        	search.setStatement(sql.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;
	}
	
	private RowSet getSubContStatus(ValueObject param){
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();
        ValueObject vo = new ValueObject();
        try {
        	sql.append(" SELECT A.SUB_CONT_ID,A.SUB_CONT_MOD_ID, A.DEPT_CD, A.DEPT_NAME, A.CON_DOC_NO, A.BUILD_NAME, A.MOD_NO, A.STATUS_CD, F_GET_CODE_NAME('CONT_STATUS_CD', A.STATUS_CD) AS STATUS_CD_NM         	\n");
        	sql.append("      , B.SEQ, B.TEMPLATE_NAME                                                                                              	\n");
        	sql.append("      , C.WARR_GUBUN, C.WARR_SEQ, C.GUAR_ONOFF_YN , C.GUAR_STATUS, F_GET_CODE_NAME('GUAR_STATUS', C.GUAR_STATUS) AS GUAR_STATUS_NM , C.GUAR_STATUS, F_GET_CODE_NAME('GAUR_KIND', C.WARR_GUBUN) AS GAUR_KIND_NM                                                           	\n");
        	sql.append("   FROM SUB2000T A, CONT_OUT_HTML B, CONT_OUT_WARR C           		\n");
        	sql.append("  WHERE A.DEPT_CD = '"+params.get("P_DEPT_CD3")+"'        			\n");
        	sql.append("    AND A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID       			  	\n");
        	sql.append("    AND A.SUB_CONT_MOD_ID = C.SUB_CONT_MOD_ID(+)					\n");
			if(!param.get("P_CON_DOC_NO").isEmpty()){
				sql.append("     AND A.CON_DOC_NO = '"+params.get("P_CON_DOC_NO")+"'        \n");
			}
			if(!param.get("P_BUILD_NAME").isEmpty()){
				sql.append("     AND A.BUILD_NAME = '"+params.get("P_BUILD_NAME")+"'    \n");
			}
			if(!param.get("P_MOD_NO").isEmpty()){
				sql.append("     AND A.MOD_NO = '"+params.get("P_MOD_NO")+"'      \n");
			}
        	sql.append("  ORDER BY A.SUB_CONT_ID, A.SUB_CONT_MOD_ID, A.MOD_NO, B.SEQ, C.WARR_SEQ         	\n");
        	
        	WAFLogger.debug(sql.toString());
        	search.setStatement(sql.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;
	}
	
	private RowSet getSubPayStatus(ValueObject Params){
		Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
        	sqlstr += " SELECT A.DEPT_NAME,                                                                                 --현장명								\n"
        			+ "		  A.BUILD_NAME,                                                                                     --업체명							\n"
        			+ "       A.CON_DOC_NO,                                                                                     								\n"
        			+ "       A.CON_CUR_CD AS CURRENCY_GB,                                                                                     								\n"
        			+ "       F_GET_CURRENCY_DIGIT(A.CON_CUR_CD) AS CURRENCY_DIGIT,                                                                                     								\n"
        			+ "       A.SUB_CONT_ID,                                                                                     								\n"
        			+ "       D.MEMBER_NAME,                                                                                     								\n"
        			+ "       TO_CHAR(A.START_WORK_DATE ,'YYYY-MM-DD')||' ~ ' || TO_CHAR(A.END_WORK_DATE ,'YYYY-MM-DD') AS START_END_WORK_DATE, --공사기간  		\n"                     
        			+ "       A.CON_SUPPLY_AMT,   --계약공급가                                                             															\n"
        			+ "       A.CON_SURTAX_AMT,   --계약부가세                                                      																\n"
        			+ "       A.CON_AMT,          --계약합계 																										\n"
        			+ "       A.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(TO_DATE(B.ACMP_YYYYMM, 'YYYYMM'), 'YYYY-MM') AS ACMP_YYYYMM,                     --기성년월   						 		\n"
        			+ "       B.ACMP_NO,  --지급차수                                                                        															\n"
        			+ "       B.FUND_AMT, --금회기성    																											\n"
        			+ "       B.PAY_DEDUCT_AMT,   --선급금공제																										\n"
        			+ "       B.REQU_SUPPLY_AMT,   -- 실지불액                                                             															\n"
        			+ "          NVL(A.CON_SUPPLY_AMT - B.TOT_FUND_AMT,0) AS MOD_FUND_AMT,   --잔여기성                                						    			\n"
        			+ "       TO_CHAR(ROUND((NVL(B.FUND_AMT, 0) / A.CON_SUPPLY_AMT)*100,2), 'FM999.00') FUND_RATE,--기성율       									\n"
        			+ "       B.STATUS_CD,																									\n"
        			+ "       DECODE(B.STATUS_CD,'3 ','10',B.STATUS_CD) AS STATUS_CD_NM    ,        --상태																										\n"
        			+ "       B.ACMP_ID                                                            												       			\n"
        			+ " FROM SUB1000T A,  SUB1040T B                                                          						      		\n"
        			+ "		 , ( SELECT SUM(NVL(REQ_PREPAY_SUPPLY_AMT,0)) AS REQ_PREPAY_SUPPLY_AMT                                                      						      											\n"
        			+ "		           , SUB_CONT_ID, MIN(TAX_DY) AS TAX_DY                                                    						      											\n"
        			+ "		      FROM SUB1030T                                                        						      											\n"
        			+ "		      WHERE STATUS_CD = '10'                                                   						      											\n"
        			+ "		      GROUP BY SUB_CONT_ID                                                       						      											\n"
        			+ "		    )    C                                                         						      											\n"
        			+ "		, (                                                          						      											\n"
        			+ " 			SELECT X.SUB_CONT_ID , MAX(Y.MEMBER_NAME) AS MEMBER_NAME                                                          			\n"
        			+ " 			FROM SUB2000T X, CONT_OUT_SUPP Y                                                          						      		\n"
        			+ " 			WHERE X.SUB_CONT_MOD_ID = Y.SUB_CONT_MOD_ID                                                          						\n"
        			+ " 			AND SIGN_SEQ = 2                                                          										      		\n"
        			+ "  			GROUP BY SUB_CONT_ID                                                          									      		\n"
        			+ "  	) D                                                           						      											\n"        			
        			+ " WHERE 1=1                                                                                        										\n"
        			+ " AND A.SUB_CONT_ID = B.SUB_CONT_ID         																								\n"
        			+ " AND A.SUB_CONT_ID = C.SUB_CONT_ID(+)                                                       												\n"
        			+ " AND A.SUB_CONT_ID = D.SUB_CONT_ID                                                       												\n"
        			+ " AND CON_CLS_YN = '9'  																													\n";

        	if(!"".equals(params.get("searchDeptCd")))
        	{
        		sqlstr	+= 	"  AND A.DEPT_CD = '"+params.get("searchDeptCd")+"'													\n";			
        	}
        	if(!"".equals(params.get("searchFromDate"))){
        		sqlstr += " AND B.ACMP_YYYYMM >= '"+Params.get("searchFromDate").replaceAll("-", "")+"'					\n";
        	}
        	if(!"".equals(params.get("searchToDate"))){
        		sqlstr += " AND B.ACMP_YYYYMM <= '"+Params.get("searchToDate").replaceAll("-", "")+"'					\n";
        	}
        	
        	if(!"".equals(params.get("searchBuldNm"))){
        		sqlstr += " AND UPPER(A.BUILD_NAME) LIKE UPPER('%" + params.get("searchBuldNm") + "%')					\n";
        	}
        	if(!"".equals(params.get("searchCorp"))){
        		sqlstr += " AND UPPER(D.MEMBER_NAME) LIKE UPPER('%" + params.get("searchCorp") + "%')														\n";
        	}
        	
        	sqlstr += "  ORDER BY DEPT_NAME, BUILD_NAME, ACMP_YYYYMM    												\n";
            search.setStatement(sqlstr);
            WAFLogger.debug(sqlstr);
            listRowSet = search.execute();
           
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getPrePaymentList() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            if( listRowSet == null ) listRowSet = new RowSetImpl();
            return listRowSet;
        }
	}	

	private RowSet getPurPayStatus(ValueObject Params){
		Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
        	sqlstr += " SELECT																												  	\n"
        			+ "     	A.DEPT_NAME            AS DEPT_NAME,          --현장명                											  	\n"
        			+ "     	A.CST_DOC_NAME     	   AS CST_DOC_NAME,   	  --계약명	           			  									\n"
        			+ "     	TO_CHAR(A.ORD_SUPPLY_AMT,'999,999,999,990')       ORD_AMT,				                			  									\n"
        			+ "     	B.PAY_DIV              AS PAY_DIV,				                			  									\n"
        			+ "     	DECODE(B.PAY_DIV,'1','납품정산','계획분할') PAY_DIV_NM, -- 납품설치구분명				  									\n"
        			+ "     	B.DEPOSIT_CD           AS DEPOSIT_CD,      --지급구분	               			  									\n"
        			+ "     	F_GET_CODE_NAME('DEPOSIT_CD', B.DEPOSIT_CD) AS DEPOSIT_NM,         			  									\n"
        			+ "     	TO_CHAR(TO_DATE(B.TAX_DY, 'yyyy-mm-dd'), 'yyyy-mm-dd') AS REQ_DATE,         --신청서작성일자        	  									\n"
        			+ "     	B.DEPOSIT_SEQ          AS DEPOSIT_SEQ,      --지급차수                			  									\n"
        			+ "     	A.CURRENCY_GB          AS CURRENCY_GB,                						  									\n"
        			+ "     	F_GET_D_CURRENCY( NVL( B.CUR_PREPAY_APPLYAMT, 0 ) , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ) ) ) CUR_PREPAY_APPLYAMT,    --금회선급금공제액		  									\n"
        			+ "     	nvl(B.CUR_REQ_AMT, 0) as CUR_REQ_AMT,    --금회청구_공급가액		  									\n"
        			+ "     	nvl(B.REMAINDER_ACMP_SUPPLYAMT, 0) as REMAINDER_ACMP_SUPPLYAMT, --잔액청구_공급가액								\n"
        			+ "         A.ORD_SUPPLY_AMT - (NVL(B.CUR_PAYED_SUPPLYAMT, 0) + NVL((SELECT SUM(REQ_PREPAY_SUPPLY_AMT) FROM PUR1030T WHERE PUR_CONT_ID = A.PUR_CONT_ID AND PREPAY_PROC_GB = 10), 0)) as REMAINDER_SUPPLYAMT, --잔액청구_공급가액 \n"
        			+ "     	remainder_acmp_vat,								\n"
        			+ "     	remainder_acmp_amt,								\n"        			
        			+ "     	nvl(B.CUR_ACMP_PERCENT, 0) as  CUR_ACMP_PERCENT,      --금회청구_지급율[금회기성고비율]								\n" 
        			+ "     	B.REQ_STATUS           AS  REQ_STATUS,                			  												\n"
        			+ "     	B.REQ_STATUS           AS  REQ_STATUS_NM,                			  												\n"
        			+ "     	A.PUR_CONT_ID,                			  												\n"
        			+ "     	B.PAYREQ_ID          AS  PAYREQ_ID,                			  												\n"
        			+ "     	TO_CHAR(A.ORD_START_DATE, 'YYYY-MM-DD') || ' ~ ' || TO_CHAR(A.ORD_END_DATE, 'YYYY-MM-DD') AS ORD_DATE,               				\n"
        			+ "     	A.ORD_DOC_NO AS ORD_DOC_NO,               				\n"
        			+ "     	(SELECT CORP_KOR FROM SUPP_INFO WHERE CORP_ID = A.CORP_ID) CORP_KOR                			  												\n"
        			+ " FROM PUR1000T A, PUR1100T B			                			  														\n"
        			+ " WHERE A.PUR_CONT_ID = B.PUR_CONT_ID                       																\n";

        	if(!"".equals(params.get("searchDeptCd2"))){
        		sqlstr	+= " AND A.DEPT_CD = '"+Params.get("searchDeptCd2")+"'	\n";
    		}
        	if(!"".equals(params.get("searchBuildName"))){
        		sqlstr += " AND UPPER(A.CST_DOC_NAME) LIKE UPPER('%" + params.get("searchBuildName") + "%')							\n";
        	}
        	if(!"".equals(params.get("searchDateStart"))){
        		sqlstr += " AND B.TAX_DY BETWEEN '"+Params.get("searchDateStart").replaceAll("-", "")+"'	AND '"+Params.get("searchDateEnd").replaceAll("-", "")+"'		\n";
        	}
        	if(!"".equals(params.get("searchStatusCd"))){
        		sqlstr += " AND B.REQ_STATUS = '"+Params.get("searchStatusCd")+"'													\n";
    		}      
        	if(!"".equals(params.get("searchCorp2"))){
        		sqlstr += " AND UPPER((SELECT CORP_KOR FROM SUPP_INFO WHERE CORP_ID = A.CORP_ID)) LIKE UPPER('%" + params.get("searchCorp2") + "%')	\n";
        	}
            sqlstr += "ORDER BY ORD_DOC_NO, REQ_DATE											      \n" ;  
            search.setStatement(sqlstr);
            WAFLogger.debug(sqlstr);
            listRowSet = search.execute();
           
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getPrePaymentList() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            if( listRowSet == null ) listRowSet = new RowSetImpl();
            return listRowSet;
        }
	}	
	
    /**
     * 기성 신청 상태를 조회 한다.
	 * @param 
	 * @param 
	 * @return ValueObject
	 */
	public ValueObject get_sheetCombo(String str){

		ValueObject vo = new ValueObject();
		
		try{
			
			RowSet rowSet = getStatusList(str);
			
			InitCombo initCombo  = new InitCombo();
			initCombo.makeComboData(rowSet,"DETAILCD", "DETAILNM");
		
			vo.set("DETAILCD", initCombo.getCode());
			vo.set("DETAILNM", initCombo.getCodeName());
		    
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if( vo == null ){
				vo = new ValueObject();
            }
			return vo;
		}
		
	}
	
    /**
     * 기성상태를 조회한다.
	 * @param 
	 * @param 
	 * @return ValueObject
	 */
	public RowSet getStatusList(String str){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT DETAILCD, DETAILNM             		\n"
                    + " FROM EBID_COM911T       					\n"
                    + " WHERE BASECD = '"+str+"'				\n";
             
            search.setStatement(sqlstr);
            listRowSet = search.execute();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}	
}