/**
 *
 * @(#)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.Row;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
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 kr.co.udapsoft.ebid.buyer.common.file.FileManager;



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

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

	public void doBiz() throws Exception {
	    
	    if(storage.getEvent().equals("GET_DETAIL"))
	    {
	    	WAFLogger.debug("==========GET_DETAIL============");
	        
	      //계약정보, 세금계산서정보, 대금정보  
            ValueObject prePayment = getPayItem(params);
	        
	        String PayItemListId = prePayment.get("PAYREQ_ID");
	        
	        //기타서류 첨부파일 조회
            storage.addValueObject("PURCHASE_"+PayItemListId, getfilelist("PURCHASE_"+PayItemListId,"240","PURCHASE_"+PayItemListId));
            storage.addValueObject("prePayment", prePayment);
	        storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
	    }
		
	}
	
	
	/**
     * 첨부파일
     * @param filegb
     * @param id
     * @param returnid
     * @return
     */
    private ValueObject getfilelist(String filegb,String id,String returnid){
        ValueObject vo = new ValueObject();
        RowSet rowSet = FileManager.getFileList(filegb, id);
        String str="";
            for(int i=0;i<rowSet.size();i++){
                Row row=rowSet.getRow(i);
                str += (i+1)+" : "+row.get("FILE_NAME")+" ("+ row.get("FILE_SIZE")+")";
                if(i+1!=rowSet.size()){
                    str +="\r\n";
                }
            }
            vo.set(returnid, str);
        return vo;
    }
	
	
	/**
     *@param ValueObject
     *@return ValueObject
     *기   능 : 대금신청 상세(구매 중도금,잔금)
     *@author OK
     *@version 2014. 8. 07.
     *@since JDK1.7
     */
    public ValueObject getPayItem(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sqlStr = new StringBuffer();
        ValueObject rowObject = null;
        
        try {

            sqlStr.append("             SELECT A.DEPT_CD -- 현장코드                                                                            \n");
            sqlStr.append("                          ,DEPT_NAME -- 현장명                                                                            \n");
            sqlStr.append("                          ,CST_DOC_NAME -- 계약명                                                                            \n");
            sqlStr.append("                          ,MEMBER_NAME -- 업체명                                                                            \n");
            sqlStr.append("                          ,VP_DESC -- VP 검토 항목                                                                            \n");
            sqlStr.append("                          ,VP_END_DY                                                                                                     \n");
            sqlStr.append("                          ,DEPOSIT_CD -- 지금구분코드                                                                            \n");
            sqlStr.append("                          ,DEPOSIT_NM -- 지급구분명                                                                            \n");
            sqlStr.append("                          ,DEPOSIT_SEQ -- 지금차수                                                                            \n");
            sqlStr.append("                          ,CURRENCY_GB -- 통화구분                                                                            \n");
            sqlStr.append("                          ,VP_STATUS -- vp 상태                                                                            \n");
            sqlStr.append("                          ,CASE WHEN VP_STATUS = '03' THEN '검토대상'                                                                            \n");
            sqlStr.append("                          WHEN VP_STATUS = '40' THEN 'VP심사중'                                                                            \n");
            sqlStr.append("                          WHEN VP_STATUS = '50' THEN 'VP반려'                                                                            \n");
            sqlStr.append("                          WHEN VP_STATUS = '60' THEN 'VP승인' END VP_STATUS_NM                                                                            \n");
            sqlStr.append("                          ,PAYREQ_ID                                                                            \n");
            sqlStr.append("                          ,SUBSTR(VENDCD, 0,3) || '-' || SUBSTR(VENDCD, 4,2) || '-' || SUBSTR(VENDCD, 6,5) VENDCD --사업자번호                                                                            \n");
            sqlStr.append("                          ,A.BOSS_NAME                                                                            \n");
            sqlStr.append("                          ,ORD_START_END_DATE                                                                            \n");
            sqlStr.append("                          ,ORD_AMT                                                                            \n");
            sqlStr.append("                          ,A.PUR_CONT_MOD_ID                                                                            \n");
            sqlStr.append("                          ,NVL(B.PREPAY_SUPPLY_AMT,0) AS PREPAY_SUPPLY_AMT                                                                            \n");
            sqlStr.append("                          ,'대금지불방법' AS DDD         --대금지불방법                                                                            \n");
            sqlStr.append("                          ,A.PAY_DIV                                                                            \n");
            sqlStr.append("                          ,CUST_SIGN_DATE                                                                            \n");
            sqlStr.append("                          ,A.BEF_ACMP_APPLYAMT                                                                            \n");
            sqlStr.append("                          ,A.BEF_ACMP_VAT                                                                            \n");
            sqlStr.append("                          ,A.BEF_ACMP_AMT                                                                            \n");
            sqlStr.append("                          ,A.BEF_ACMP_PERCENT                                                                            \n");
            sqlStr.append("                          ,FLOOR(CUR_ACMP_APPLYAMT) CUR_ACMP_APPLYAMT -- 금회기성고                                                                            \n");
            sqlStr.append("                          ,CUR_ACMP_VAT                                                                            \n");
            sqlStr.append("                          ,CUR_ACMP_AMT                                                                            \n");
            sqlStr.append("                          ,CUR_ACMP_PERCENT                                                                            \n");
            sqlStr.append("                          ,CUR_BEF_ACMP_SUPPLYAMT                                                                            \n");
            sqlStr.append("                          ,CUR_BEF_ACMP_VAT                                                                            \n");
            sqlStr.append("                          ,CUR_BEF_ACMP_AMT                                                                            \n");
            sqlStr.append("                          ,CUR_BEF_ACMP_PERCENT                                                                            \n");
            sqlStr.append("                          ,REMAINDER_ACMP_SUPPLYAMT                                                                            \n");
            sqlStr.append("                          ,REMAINDER_ACMP_VAT                                                                            \n");
            sqlStr.append("                          ,REMAINDER_ACMP_AMT                                                                            \n");
            sqlStr.append("                          ,REMAINDER_ACMP_PERCENT                                                                            \n");
            sqlStr.append("                          ,VP_CHK_EMPNO --검토자 사                                                                            \n");
            sqlStr.append("                          ,GET_DEPART_DEPT_NAME(VP_CHK_EMPNO) EPART_DEPT_NAME  --검토자 부서                                                                            \n");
            sqlStr.append("                          ,VP_CHK_EMPNM --검토자                                                                            \n");
            sqlStr.append("                          ,VP_CHK_HP --검토자 핸드폰번호                                                                            \n");
            sqlStr.append("                          ,VP_CHK_EMAIL --검토자 EMAIL                                                                            \n");
            sqlStr.append("                          ,VP_RTN_REASON --반려사유                                                                            \n");
            sqlStr.append("                          ,VP_RTN_DY --반려일시                                                                            \n");
            sqlStr.append("                          ,VP_OPINION --VP 의견                                                                            \n");
            sqlStr.append("                          FROM(                                                                            \n");
            sqlStr.append("                          SELECT C.DEPT_CD -- 현장코드                                                                            \n");
            sqlStr.append("                          ,C.DEPT_NAME -- 현장명                                                                            \n");
            sqlStr.append("                          ,C.CST_DOC_NAME -- 계약명                                                                            \n");
            sqlStr.append("                          ,D.MEMBER_NAME -- 업체명                                                                            \n");
            sqlStr.append("                          ,B.VP_DESC -- VP 검토 항목                                                                            \n");
            sqlStr.append("                          ,TO_CHAR(TO_DATE(A.VP_END_DY, 'YYYY-MM-DD'), 'YYYY-MM-DD') VP_END_DY                                                                            \n");
            sqlStr.append("                          ,DEPOSIT_CD -- 지금구분코드                                                                            \n");
            sqlStr.append("                          ,CASE WHEN DEPOSIT_CD = 2 THEN '중도금'                                                                            \n");
            sqlStr.append("                          ELSE '잔금' END DEPOSIT_NM -- 지급구분명                                                                            \n");
            sqlStr.append("                          ,A.DEPOSIT_SEQ -- 지금차수                                                                            \n");
            sqlStr.append("                          ,C.CURRENCY_GB -- 통화구분                                                                            \n");
            //2016.8.2 수정 
            //sqlStr.append("                          ,A.REQ_STATUS AS  VP_STATUS -- vp 상태+                                                                              \n");
            sqlStr.append("                         ,CASE WHEN A.REQ_STATUS IN ('00','01','02','03','99') THEN '03'                                                  \n");
            sqlStr.append("                               WHEN A.REQ_STATUS in ( '10', '04') THEN '60'                                                  \n");
            sqlStr.append("                               ELSE A.REQ_STATUS END VP_STATUS -- vp 상태                                                  \n");
            sqlStr.append("                          ,A.PAYREQ_ID                                                                            \n");
            sqlStr.append("                          ,D.VENDCD                                                                            \n");
            sqlStr.append("                          ,D.BOSS_NAME                                                                            \n");
            sqlStr.append("                          ,TO_CHAR(C.ORD_START_DATE, 'YYYY-MM-DD') || ' ~ '|| TO_CHAR(C.ORD_END_DATE, 'YYYY-MM-DD') AS ORD_START_END_DATE --계약기간                                                                            \n");
            sqlStr.append("                          ,A.ORD_AMT -- 계약금액                                                                            \n");
            sqlStr.append("                          ,A.PUR_CONT_MOD_ID                                                                            \n");
            sqlStr.append("                          ,DECODE(A.PAY_DIV, '1', '납품정산', '2', '계획분할') PAY_DIV                                                                            \n");
            sqlStr.append("                          ,DECODE(A.SUPP_SIGN_DT, null, '미서명', '서명완료 서명일시 : '||to_char(A.SUPP_SIGN_DT, 'yyyy-mm-dd HH24:MI:SS') ) AS  CUST_SIGN_DATE --서명일시                                                                            \n");
            sqlStr.append("                          ,NVL(A.BEF_ACMP_APPLYAMT, 0) AS BEF_ACMP_APPLYAMT          --전회까지 청구금액 공급가액                                                                            \n");
            sqlStr.append("                          ,NVL(A.BEF_ACMP_VAT, 0) AS BEF_ACMP_VAT                    --전회까지 청구금액 부가세                                                                            \n");
            sqlStr.append("                          ,NVL(A.BEF_ACMP_AMT, 0) AS BEF_ACMP_AMT                    --전회까지 청구금액 합계                                                                            \n");
            sqlStr.append("                          ,NVL(A.BEF_ACMP_PERCENT, 0) AS BEF_ACMP_PERCENT            --전회까지 청구금액 지급율                                                                            \n");
            sqlStr.append("                          ,NVL(A.CUR_ACMP_APPLYAMT, 0) CUR_ACMP_APPLYAMT -- 금회기성고                                                                            \n");
            sqlStr.append("                          ,NVL(A.CUR_ACMP_VAT, 0) CUR_ACMP_VAT -- 금회 청구금액 부가세                                                                            \n");
            sqlStr.append("                          ,NVL(A.CUR_ACMP_AMT, 0) CUR_ACMP_AMT -- 청구금액 합계                                                                            \n");
            sqlStr.append("                          ,NVL(A.CUR_ACMP_PERCENT, 0) CUR_ACMP_PERCENT -- 청구금액 지급율                                                                            \n");
            sqlStr.append("                          ,NVL(A.CUR_BEF_ACMP_SUPPLYAMT, 0) CUR_BEF_ACMP_SUPPLYAMT -- 누계 청구금액 공급가액                                                                            \n");
            sqlStr.append("                          ,NVL(A.CUR_BEF_ACMP_VAT, 0) CUR_BEF_ACMP_VAT -- 누계  청구금액 합계                                                                            \n");
            sqlStr.append("                          ,NVL(A.CUR_BEF_ACMP_AMT, 0) CUR_BEF_ACMP_AMT -- 누계 청구금액 합계                                                                            \n");
            sqlStr.append("                          ,NVL(A.CUR_BEF_ACMP_PERCENT, 0) CUR_BEF_ACMP_PERCENT -- 누계 청구금액 지급율                                                                            \n");
            sqlStr.append("                          ,NVL(A.REMAINDER_ACMP_SUPPLYAMT, 0) AS REMAINDER_ACMP_SUPPLYAMT   --잔여 청구금액 공급가액                                                                            \n");
            sqlStr.append("                          ,NVL(A.REMAINDER_ACMP_VAT, 0) AS REMAINDER_ACMP_VAT   --잔여 청구금액 부가세                                                                            \n");
            sqlStr.append("                          ,NVL(A.REMAINDER_ACMP_AMT, 0) AS REMAINDER_ACMP_AMT   --잔여 청구금액 합계                                                                            \n");
            sqlStr.append("                          ,NVL(A.REMAINDER_ACMP_PERCENT, 0) AS REMAINDER_ACMP_PERCENT   --잔여 청구금액 지급율                                                                            \n");
            sqlStr.append("                          ,NVL(A.VP_CHK_EMPNO, B.VP_CHK_EMPNO) as VP_CHK_EMPNO --검토자 사                                                                            \n");
            sqlStr.append("                          ,NVL(A.VP_CHK_EMPNM ,B.VP_CHK_EMPNM)  as VP_CHK_EMPNM --검토자                                                                         \n");
            sqlStr.append("                          ,A.VP_CHK_HP --검토자 핸드폰번호                                                                            \n");
            sqlStr.append("                          ,A.VP_CHK_EMAIL --검토자 EMAIL                                                                            \n");
            sqlStr.append("                          ,A.VP_RTN_REASON --반려사유                                                                            \n");
            sqlStr.append("                          ,TO_CHAR(VP_RTN_DY, 'YYYY-MM-DD') VP_RTN_DY --반려일시                                                                            \n");
            sqlStr.append("                          ,REPLACE(VP_OPINION,CHR(13)||CHR(10),'<br/>')  VP_OPINION -- VP의견                                                                            \n");
            sqlStr.append("                    FROM PUR1100T A                                                                            \n");
            sqlStr.append("                    ,CONT_PUR_PAYPLAN B                                                                            \n");
            sqlStr.append("                    ,PUR2000T C                                                                            \n");
            sqlStr.append("                    ,CONT_PUR_SUPP D                                                                            \n");
            sqlStr.append("                    WHERE A.PAY_DIV = 2                                                                            \n");
            sqlStr.append("                    AND D.SIGN_SEQ = 2                                                                            \n");
            sqlStr.append("                    AND NVL(B.VP_YN,'N') = 'Y'                                                                            \n");
            sqlStr.append("                    AND A.PAYREQ_ID = B.PAYREQ_ID                                                                            \n");
            sqlStr.append("                    AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                                                                            \n");
            sqlStr.append("                    AND A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID                                                                            \n");
            sqlStr.append("                    ) A,                                                                            \n");
            sqlStr.append("                    (SELECT CC.* FROM PUR1030T CC WHERE PREPAY_PROC_GB = '10') B                                                                            \n");
            sqlStr.append("                    WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID(+)                                                                            \n");
            sqlStr.append("                    AND PAYREQ_ID = ?                                                                           \n");
            
            search.setStatement(sqlStr.toString());
            search.addParameter(params.get("payreqId"));
            
            WAFLogger.debug(sqlStr.toString());
            WAFLogger.debug(search.getParameters());
            
            listRowSet = search.execute();
            rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
            
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getPrePayment() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            if( rowObject == null ) rowObject = new ValueObject();
            return rowObject;
        }
    }
    
    /** 첨부서류조회 
     * @return
     * @throws Exception
     */
    public RowSet getfileListRowSet(String payreqId, String code) throws Exception {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
        try {

            sql.append("    SELECT distinct A.DETAILCD,                             \n");
            sql.append("           A.DETAILNM,                              \n");
            sql.append("           B.ID AS DOC_CD                           \n");
            sql.append("    FROM EBID_COM911T A, PUB0510T B                \n");
            sql.append("    WHERE A.BASECD = 'PUR_PAY_FILE'                 \n");
            sql.append("    AND A.DETAILCD = B.ID(+)                        \n");
            //sql.append("  AND A.CHG_CODE01 = '"+code+"'                   \n");
            sql.append("    AND B.FILE_GB(+) = '"+payreqId+"'               \n");
            //sql.append("  AND DETAILCD NOT IN ('560', '570')              \n");
            sql.append("    ORDER BY A.DETAILCD                             \n");
            
            WAFLogger.debug(sql);
            
            search.setStatement(sql.toString());
            
            listRowSet = search.execute();
            WAFLogger.debug(sql.toString());
            
           
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getListRowSet() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
            if( listRowSet == null ) listRowSet = new RowSetImpl();
           
        }
        
        return listRowSet;
    }
    
}
