/**
 *
 * @(#)author  [Youngmi Im]
 * @(#)version 1.0
 * @(#)date    May 10, 2007
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.hwenc.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of Hanwha E&C, Inc.
 */
 package kr.co.udapsoft.ebid.buyer.payment.purchase.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 ComparePayplanActualBiz extends Biz {
	
	public ComparePayplanActualBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {

		WAFLogger.debug("전자조달.대금관리.구매.대금지급현황 프로세스처리.[PayResultListBiz]");

		if(storage.compareEvent("")){
			
			storage.setSuccess(true);
			
		}else if(storage.compareEvent("GET_DATA")){
			
			WAFLogger.debug("전자조달.대금관리.구매.대금지급현황 GET_DATA");
			params.set("sheet1", getList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
	}
	
    /**
     *@param ValueObject
     *@return RowSet
     *기   능 : 대금 리스트 조회
     *@author OK
     *@version 2014. 7. 23.
     *@since JDK1.7
     */
    public RowSet getList(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
        	sqlstr  +=  "  SELECT A.DEPT_CD, A.DEPT_NAME, A.ORD_DOC_NO, A.MOD_NO, A.CST_DOC_NAME                                                          	                     \n"
                      + "       , A.GB, A.A_REQ_DATE, A.A_AMT, A.A_CNT, A.P_AMT, A.P_PAY_YN, A.P_CNT, A.DIFF                                                                     \n"     
                      + "       , A.PUR_CONT_MOD_ID, A.PUR_CONT_ID, A.PAYREQ_ID                                                                                                  \n"
                      + "    FROM (                                                                                                                                              \n"
                      + "          SELECT '선급' GB, C.PUR_CONT_MOD_ID , C.PUR_CONT_ID, C.MOD_NO, C.DEPT_CD, C.DEPT_NAME, C.ORD_DOC_NO, C.CST_DOC_NAME                            \n"	
                      + "                 ,0 PAYREQ_ID, TO_CHAR(TO_DATE(A.TAX_DY),'YYYY-MM-DD') A_REQ_DATE, A.AMT A_AMT, A.CNT A_CNT                                              \n"
                      + "                 , D.AMT P_AMT, D.PAY_YN P_PAY_YN, D.CNT P_CNT                                                                                           \n"
                      + "                 , NVL(A.AMT,0) - NVL(D.AMT,0) DIFF                                                                                                      \n"
                      + "            FROM  ( SELECT PUR_CONT_ID , MAX(REG_NO) REG_NO, MAX(CORP_KOR) CORP_KOR, SUM(REQ_PREPAY_SUPPLY_AMT) AMT, MAX(TAX_DY) TAX_DY, COUNT(*) CNT    \n"
                      + "                      FROM PUR1030T                                                                                                                      \n" 
                      + "                     WHERE PREPAY_PROC_GB IN (10,11)                                                                          	                          \n";
                      if(!"".equals(params.get("searchDateStart"))){
                  		sqlstr += "             AND TAX_DY BETWEEN '"+Params.get("searchDateStart").replace("-", "")+"'	AND '"+Params.get("searchDateEnd").replace("-", "")+"'	  \n";
                  	  }
                      sqlstr += "             GROUP BY PUR_CONT_ID  ) A  --계약별 선급금 지급현황                                                                                                                                                                           \n"
                      + "                 ,( SELECT MAX(PUR_CONT_MOD_ID) PUR_CONT_MOD_ID , PUR_CONT_ID                                                                            \n"
                      + "        		       FROM PUR2000T                                                                                                                      \n"
                      + "        		      WHERE PAY_DIV = 2 AND STATUS_CD = '50'                                                                                              \n"
                      + "        			  GROUP BY PUR_CONT_ID ) B --최종계약                                                                                                                                                                                                     \n"                                                                                                                                                                                  
                      + "                 ,PUR2000T C                                                                                                                             \n"                                                                                                           
                      + "                 ,( SELECT PUR_CONT_MOD_ID, SUM(PAY_SUPPLY_AMT) AMT ,MIN( PAY_YN) PAY_YN , COUNT(*) CNT                                                  \n"                                                                                                 
                      + "                      FROM CONT_PUR_PAYPLAN                                                                                                              \n"                                                                                                 
                      + "                     WHERE PAY_DIV_CD = '01'                                                                                                             \n"                                                                         
                      + "                     GROUP BY PUR_CONT_MOD_ID ) D                                                                                                        \n"                                                                                                 
                      + "           WHERE A.PUR_CONT_ID = B.PUR_CONT_ID                                                                                                           \n"                                                                                                 
                      + "             AND B.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                                                                                                   \n"                                                                                                 
                      + "             AND B.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID(+) --최종 완료계약의 대금계획                                                                                                                                                    \n"; 
                      if("N".equals(params.get("chk_all"))){
                    	  sqlstr  +=  "             AND NVL(A.AMT,0) - NVL(D.AMT,0) <> 0                                                                                          \n";                                                                                                 
                      }
                      if(!"".equals(params.get("searchDeptCd"))){
                  		sqlstr	+= " AND C.DEPT_CD  = '"+Params.get("searchDeptCd")+"'	                                                                                          \n";
              		  }
                  	  if(!"".equals(params.get("searchBuildName"))){
                  		sqlstr += " AND UPPER(C.CST_DOC_NAME) LIKE UPPER('%" + params.get("searchBuildName") + "%')							                                      \n";
                  	  }
                      sqlstr  += "  UNION ALL    --중도금,잔금                                                                                                                                                                                                                                           \n"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                      + "          SELECT '중도금', D.PUR_CONT_MOD_ID , D.PUR_CONT_ID, D.MOD_NO, D.DEPT_CD, D.DEPT_NAME, D.ORD_DOC_NO, D.CST_DOC_NAME                              \n"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                      + "                 ,A.PAYREQ_ID,  TO_CHAR(TO_DATE(A.TAX_DY),'YYYY-MM-DD') ,  A.CUR_REQ_AMT, 1                                                              \n"                                                                                   
                      + "                 ,b.PAY_SUPPLY_AMT, b.PAY_YN, 1                                                                                                          \n"                                                                                   
                      + "                 ,NVL(A.CUR_REQ_AMT,0) - NVL(b.PAY_SUPPLY_AMT,0)                                                                                         \n"                                                                                                                                                                     
                      + "            FROM PUR1100T A                                                                                                                              \n"                                                                                   
                      + "                ,( SELECT A.PUR_CONT_ID,  A.PUR_CONT_MOD_ID, B.PAYREQ_ID, B.PAY_DIV_CD, B.PAY_SUPPLY_AMT, B.PAY_YN                                       \n"                                                                                   
                      + "                     FROM ( SELECT MAX(PUR_CONT_MOD_ID) PUR_CONT_MOD_ID , PUR_CONT_ID                                                                    \n"                                                                                                                                                                      
                      + "    		                   FROM PUR2000T                                                                                                              \n"                                                                                   
                      + "    		                  WHERE PAY_DIV = 2 AND STATUS_CD = '50'                                                                                      \n"                                                                                   
                      + "    			              GROUP BY PUR_CONT_ID ) A                                                                                                    \n"                                                                                                                                                                       
                      + "                         , CONT_PUR_PAYPLAN B                                                                                                            \n"                                                                                   
                      + "                    WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID(+)                                                                                       \n"                                                                                   
                      + "                      AND B.PAY_DIV_CD(+) <> '01') B                                                                                                     \n"                                                                                                                                                                    
                      + "                , PUR2000T D                                                                                                                             \n"                                                                                   
                      + "           WHERE A.PUR_CONT_ID = B.PUR_CONT_ID(+)                                                                                                        \n"                                                                                   
                      + "             AND A.PAYREQ_ID = b.PAYREQ_ID(+)                                                                                                            \n"                                                                                                                                                                      
                      + "             AND B.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID                                                                                                   \n"                                                                                   
                      + "             AND A.PAY_DIV = 2                                                                                                                           \n";                                                                                   
                      if("N".equals(params.get("chk_all"))){
                    	  sqlstr  +=  "             AND NVL(A.CUR_REQ_AMT,0) - NVL(b.PAY_SUPPLY_AMT,0) <> 0                                                                       \n";                                                                                                 
                      }
                      if(!"".equals(params.get("searchDeptCd"))){
                    	  sqlstr	+= " AND D.DEPT_CD  = '"+Params.get("searchDeptCd")+"'	                                                                                      \n";
                	  }
                      if(!"".equals(params.get("searchBuildName"))){
                    	  sqlstr += " AND UPPER(D.CST_DOC_NAME) LIKE UPPER('%" + params.get("searchBuildName") + "%')							                                  \n";
                      }
                      if(!"".equals(params.get("searchDateStart"))){
                  		  sqlstr += " AND A.TAX_DY BETWEEN '"+Params.get("searchDateStart").replace("-", "")+"'	AND '"+Params.get("searchDateEnd").replace("-", "")+"'		      \n";
                  	  }  
                      sqlstr  += "    AND A.REQ_STATUS = '10'  ) A                                                                                                                \n"                                                                                                                                                                
        	          + "    WHERE 1 = 1 			                                                                                                                  			  \n";	
        	sqlstr += " ORDER BY A.DEPT_NAME, A.PUR_CONT_ID, A.A_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;
        }
    }
}
