/**
 *
 * @(#)author  [ok]
 * @(#)version 1.0
 * @(#)date    2014. 07. 15
 * @(#)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.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

public class ResolutionFinishListViewBiz extends Biz {
	
	public ResolutionFinishListViewBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {

		WAFLogger.debug("외주 기성완료현황 데이터 조회 처리");
		
		if(storage.compareEvent("GET_DATA")){	//데이터 조회
			params.set("sheet1", getPrePaymentList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		} else if (storage.compareEvent("GET_DATA2")){	//데이터 조회(부가세 포함)
			params.set("sheet2", getPrePaymentList2(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
	}

    /**
     *@param ValueObject
     *@return RowSet
     *기   능 : 기성신청현황 데이터 조회
     *@author OK
     *@version 2014. 6. 30.
     *@since JDK1.7
     */
    public RowSet getPrePaymentList(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"
        			+ "       DECODE(B.STATUS_CD,'3 ','10',B.STATUS_CD) AS STATUS_CD    ,        --상태																										\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;
        }
    }
    
    /**
     *@param ValueObject
     *@return RowSet
     *기   능 : 기성신청현황 데이터 조회(부가세 포함)
     *@author OK
     *@version 2019. 12. 11.
     *@since JDK1.7
     */
    public RowSet getPrePaymentList2(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"
        			+ "       A.PREPAY_SURTAX_AMT  ,       -- 계약상선급금부가세																					\n"
        			+ "       NVL(C.REQ_PREPAY_SUPPLY_AMT,0) AS REQ_PREPAY_SUPPLY_AMT, --     선급금 발생															\n"
        			+ "       NVL(C.REQ_PREPAY_SURTAX_AMT,0) AS REQ_PREPAY_SURTAX_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"
        			+ "       B.REQU_SURTAX_AMT,   -- 실지불액 부가세                                                   															\n"
        			+ "       B.REQU_AMT, -- 실지불액 합계                                                   															\n"
        			+ "       NVL(A.CON_SUPPLY_AMT - B.TOT_FUND_AMT,0) AS MOD_FUND_AMT,   --잔여기성                                						    			\n"
        			+ "       NVL(A.CON_SURTAX_AMT - B.TOT_REQU_SURTAX_AMT - ROUND(B.TOT_PAY_DEDUCT_AMT * 10 / 100),0) AS MOD_SURTAX_AMT,   --잔여기성 부가세 		\n"
        			+ "       NVL(A.CON_SUPPLY_AMT - B.TOT_FUND_AMT,0) + NVL(A.CON_SURTAX_AMT - B.TOT_REQU_SURTAX_AMT - ROUND(B.TOT_PAY_DEDUCT_AMT * 10 / 100),0) AS MOD_AMT, -- 잔여기성 합계	\n"
        			+ "       TO_CHAR(ROUND((NVL(B.FUND_AMT, 0) / A.CON_SUPPLY_AMT)*100,2), 'FM999.00') FUND_RATE,--기성율       									\n"
        			+ "       DECODE(B.STATUS_CD,'3 ','10',B.STATUS_CD) AS STATUS_CD    ,        --상태																										\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"
        			+ "		           , SUM(NVL(REQ_PREPAY_SURTAX_AMT,0)) AS REQ_PREPAY_SURTAX_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;
        }
    }    
}
