/**
 *
 * @(#)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 PayResultListBiz extends Biz {
	
	public PayResultListBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {

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

		if(storage.compareEvent("")){
			
			//검색 조건에 ComboBox데이터 설정 [상태]
			RowSet rowSet = getPurchaseStatus_searchCombo();
			storage.addRowSet("statusComboList", rowSet);
			
			//시트의 ComboBox데이터 설정 [상태]
			ValueObject vo = getPurchaseStatus_sheetCombo();
			storage.addObject("selectText", vo.getObject("DETAILCD"));
			storage.addObject("selectValue", vo.getObject("DETAILNM"));
			
			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));
		}
	}
	
	
    /**
     * 대금관리 상태Combox 데이터를 조회 한다.
	 * @param 
	 * @param 
	 * @return ValueObject
	 */
	public RowSet getPurchaseStatus_searchCombo(){

        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        
        try {
            sqlstr += " SELECT DETAILCD, DETAILNM             		\n"
                    + " FROM EBID_COM911T      						\n"
                    + " WHERE BASECD = 'PURCHASE_STATUS_CD'   		\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 getPurchaseStatus_sheetCombo(){

		ValueObject vo = new ValueObject();
		try{
			RowSet rowSet = getPurchaseStatus_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 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 * FROM ( \n" ;
        	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(B.REQ_DATE, 'yyyy-mm-dd') AS REQ_DATE,         --신청서작성일자        	  									\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"
        			+ "     	nvl(B.CUR_ACMP_PERCENT, 0) as  CUR_ACMP_PERCENT,      --금회청구_지급율[금회기성고비율]								\n"
        			//+ "         A.ORD_SUPPLY_AMT -  SUM (B.CUR_REQ_AMT) OVER (PARTITION BY A.PUR_CONT_ID order by B.DEPOSIT_SEQ) REMAINDER_ACMP_SUPPLYAMT, \n" //2016.7.4 잔액계산 수정 
        			//+ "     	ROUND(NVL(B.CUR_REQ_AMT, 0)/A.ORD_SUPPLY_AMT* 100,2)   CUR_ACMP_PERCENT,      --금회청구_지급율[금회기성고비율]			  	   \n" //2016.7.4 비율계산 
        			+ "     	B.REQ_STATUS           AS  REQ_STATUS,                			  												\n"
        			+ "     	A.PUR_CONT_ID          AS  PUR_CONT_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";
        			//+ " AND  A.CST_DOC_NAME LIKE '%%'                       									\n"
        			//+ " AND  A.DEPT_CD = '1557'                       											\n"
        			//+ " AND B.REQ_DATE BETWEEN '2012-02-15' AND '2012-02-15'         							\n";

        	if(!"".equals(params.get("searchDeptCd"))){
        		sqlstr	+= " AND A.DEPT_CD = '"+Params.get("searchDeptCd")+"'	\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("searchCorp"))){
        		sqlstr += " AND UPPER((SELECT CORP_KOR FROM SUPP_INFO WHERE CORP_ID = A.CORP_ID)) LIKE UPPER('%" + params.get("searchCorp") + "%')	\n";
        	}
        	//2015.3.3 대금지급현황 조회 선급금 추가 mklee97
        	sqlstr += " UNION ALL                                                                                                  \n"    
        		   +  " 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')     AS ORD_AMT,                                        \n"                                                                       
        	       +  "        ''                   AS PAY_DIV,                                                                    \n"                                                                      
        	       +  "        ''                   AS PAY_DIV_NM,                                     -- 납품설치구분명                            \n"                                           
        	       +  "        '1'                  AS DEPOSIT_CD,                                     --지급구분                                        \n"                                                                
        	       +  "        '선급금'              AS DEPOSIT_NM,                                                                  \n"                      
        	       +  "        TO_CHAR(TO_DATE(B.TAX_DY, 'yyyy-mm-dd'), 'yyyy-mm-dd') AS REQ_DATE,     --신청서작성일자                               \n"                                             
        	       +  "        ' '                  AS DEPOSIT_SEQ,                                    --지급차수                                         \n"                                                            
        	       +  "        A.CURRENCY_GB        AS CURRENCY_GB,                                                                 \n"                                                                         
        	       +  "        '' CUR_PREPAY_APPLYAMT,                                                  --금회선급금공제액                             \n"                                             
        	       +  "        nvl(B.req_prepay_supply_amt, 0) AS CUR_REQ_AMT,                         --금회청구_공급가액                           \n"                                            
        	       +  "        A.ORD_SUPPLY_AMT - nvl(B.req_prepay_supply_amt,0) as REMAINDER_ACMP_SUPPLYAMT,                       --잔액청구_공급가액                           \n"
        	       +  "        0                    as REMAINDER_SUPPLYAMT,                            --잔액청구_공급가액  \n"
        	       +  "        0                    as CUR_ACMP_PERCENT,                               --금회청구_지급율[금회기성고비율]  \n"                               
        	       +  "        ''                   AS REQ_STATUS,                                                                   \n"                                                           
        	       +  "        A.PUR_CONT_ID        AS  PUR_CONT_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,                                                                                         \n"
        	       +  "        (SELECT CORP_KOR FROM SUPP_INFO WHERE CORP_ID = A.CORP_ID) CORP_KOR                                   \n"                                                                        
        	       +  "   FROM PUR1000T A, pur1030t B     --2015.12.1 선급중복  ->선급모두조회.ok                                                                          \n"                                                                                              
        	       +  "  WHERE A.PUR_CONT_ID = B.PUR_CONT_ID                                                                         \n"  
        	       +  "    and b.req_prepay_supply_amt <> 0                                                                          \n"                 
        	       +  "    and b.prepay_proc_gb = 10                                                    --선급완료                                         \n"                                                           
        	       +  "    AND A.DEPT_CD =  '"+Params.get("searchDeptCd")+"'                                       \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("searchCorp"))){
        		sqlstr += " AND UPPER((SELECT CORP_KOR FROM SUPP_INFO WHERE CORP_ID = A.CORP_ID)) LIKE UPPER('%" + params.get("searchCorp") + "%')	\n";
        	}                     
            //sqlstr += " ORDER BY A.ORD_DOC_NO, B.DEPOSIT_CD, B.DEPOSIT_SEQ, REQ_DATE												\n" ; 
            sqlstr += ") ORDER BY ORD_DOC_NO, REQ_DATE											      \n" ; //2018.06.08 DEPOSIT_CD, DEPOSIT_SEQ, 
            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;
        }
    }
}
