/**
 *
 * @(#)author  [ok]
 * @(#)version 1.0
 * @(#)date    2014. 06. 18
 * @(#)since   JDK 1.7.2
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of udapsoft, Inc.
 */
package kr.co.udapsoft.ebid.buyer.payment.outside.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.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.util.ValueObject;

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 AdvencePaymentResultListBiz extends Biz {
	
	public AdvencePaymentResultListBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {

		if(storage.compareEvent("")){
			WAFLogger.debug("대금관리.외주대금.선급금관리 리스트 페이지 INIT");
			
			//검색 조건에 ComboBox데이터 설정 [상태]
			RowSet rowSet = getPerpayStatus_searchCombo();
			storage.addRowSet("statusComboList", rowSet);
			
			//시트의 ComboBox데이터 설정 [상태]
			ValueObject vo = getPerpayStatus_sheetCombo();
			storage.addObject("selectText", vo.getObject("DETAILCD"));
			storage.addObject("selectValue", vo.getObject("DETAILNM"));
			
			//시트의 ComboBox데이터 설정 [신청구분]
			vo = getPerpayReqDiv_sheetCombo();
			storage.addObject("selectText1", vo.getObject("DETAILCD"));
			storage.addObject("selectValue1", vo.getObject("DETAILNM"));
	
			storage.setSuccess(true);
		}else if(storage.compareEvent("GET_DATA")){
			params.set("sheet1", getPrePaymentList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
	}

    /**
     * 선급금 신청 상태Combox 데이터를 조회 한다.
	 * @param 
	 * @param 
	 * @return ValueObject
	 */
	public RowSet getPerpayStatus_searchCombo(){

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

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}
	
    /**
     * 선급금 신청 상태를 조회 한다.
	 * @param 
	 * @param 
	 * @return ValueObject
	 */
	public ValueObject getPerpayStatus_sheetCombo(){

		ValueObject vo = new ValueObject();
		try{
			RowSet rowSet = getPerpayStatus_searchCombo();
			
			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 getPerpayReqDiv(){

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

			if( listRowSet == null ){
            	listRowSet = new RowSetImpl();
            }
            
			return listRowSet;
		}
	}
	
    /**
     * 선급금 신청구분를 조회 한다.
	 * @param 
	 * @param 
	 * @return ValueObject
	 */
	public ValueObject getPerpayReqDiv_sheetCombo(){

		ValueObject vo = new ValueObject();
		//String RIGHT_ID = "";
		//String RIGHT_NAME = "";
		try{
			RowSet rowSet = getPerpayReqDiv();
			
			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 ValueObject
     *@return RowSet
     *기   능 : 선급금 신청 데이터 조회
     *@author OK
     *@version 2014. 6. 23.
     *@since JDK1.7
     */
    public RowSet getPrePaymentList(ValueObject Params) {

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
        	sqlstr += " SELECT 																													  	\n"
        			+ "     D.MEMBER_NAME,                                                                                     								\n"
        			+ "     A1.SUB_CONT_MOD_ID, --변경계약ID               			  																\n"
        			+ "     A1.CON_DOC_NO,	    --외주계약번호    			  																			\n"
        			+ "     A1.MOD_NO,		    --변경차수        			  																			\n"
        			+ "     A1.SUB_CONT_ID,	    --계약서ID      			  																			\n"
        			+ "     A1.MOD_GB,		    --변경구분       			  																			\n"
        			+ "     A1.DEPT_CD,	        --현장코드   			  																				\n"
        			+ "     A1.DEPT_NAME,	    --현장명   			  																				\n"
        			+ "     A1.BUILD_NAME,	    --계약명         			  																			\n"
        			+ "     TO_CHAR(A1.START_WORK_DATE, 'YYYY-MM-DD') || ' ~ ' || TO_CHAR(A1.END_WORK_DATE, 'YYYY-MM-DD') AS START_END_WORK_DATE,	\n"
        			+ "     A1.CON_SUPPLY_AMT  AS CON_AMT,            --계약금액																		\n"
        			+ "     A1.PREPAY_SUPPLY_AMT AS PREPAY_AMT,       --선급금액																		\n"
        			+ "     NVL(B1.REQ_PREPAY_SUPPLY_AMT, 0) AS REQ_PREPAY_AMT,    --신청금액															\n"
        			+ "     B1.TAX_DY  AS SIGN_DT,	--신청일																	\n"
        			+ "     NVL(B1.STATUS_CD, '00') AS STATUS_CD,   		--신청구분																\n"
        			+ "     NVL(B1.REQ_DIV_CD, '1') AS REQ_DIV_CD   		--상태 																	\n"
        			+ " FROM 				                			  																			\n"
        			+ "      SUB2000T A1,				       			  																			\n"
        			+ "      SUB1030T B1	--외주)선급내역       			  																			\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 A1.SUB_CONT_MOD_ID = B1.SUB_CONT_MOD_ID																				        \n"
        			+ " AND A1.SUB_CONT_ID = D.SUB_CONT_ID                                                       												\n"
        			+ " AND A1.CON_CLS_YN = '9'																								        \n"
        			+ " AND A1.PREPAY_AMT > 0																								        \n"
        			+ " AND B1.STATUS_CD IN('10', '11')																						        \n"
        			;
        	
        	if(!"".equals(params.get("dept_cd"))){
        		sqlstr	+= " AND A1.DEPT_CD = '"+Params.get("dept_cd")+"' \n"		;
        	}

        	if(!"".equals(params.get("searchBuildName"))){
        		sqlstr += " AND UPPER(A1.BUILD_NAME) LIKE UPPER('%" + params.get("searchBuildName") + "%')												\n";
        	}
        	if(!"".equals(params.get("searchStatusCd"))){
        		sqlstr += " AND B1.STATUS_CD = '"+Params.get("searchStatusCd")+"'																			\n";
        	}

        	if(!"".equals(params.get("signDt_from"))){
        		sqlstr += " AND B1.TAX_DY >= '"+Params.get("signDt_from").replaceAll("-", "")+"'																			\n";
        	}

        	if(!"".equals(params.get("signDt_to"))){
        		sqlstr += " AND B1.TAX_DY <= '"+Params.get("signDt_to").replaceAll("-", "")+"'																			\n";
        	}
        	if(!"".equals(params.get("searchCorp"))){
        		sqlstr += " AND UPPER(D.MEMBER_NAME) LIKE UPPER('%" + params.get("searchCorp") + "%')														\n";
        	}
            sqlstr += " ORDER BY B1.STATUS_CD ASC																									\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;
        }
    }
}
