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

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

	public void doBiz() throws Exception {

		WAFLogger.debug("전자조달.대금관리.구매.대금신청현황상세 프로세스처리.[PayRequestInitBiz]");
		
		if(storage.compareEvent("")){
			
			WAFLogger.debug("[PayRequestInitBiz] INIT");
			//신청구분ComboList
			//RowSet rowSet = getPerpayReqDiv();
			//storage.addRowSet("perpayReqDivComboList", rowSet);
			
			//기본 정보, 세금계산서 정보,선급금  
			//ValueObject prePayment = getPrePayment(params);
			
			//첨부파일
	        //storage.addValueObject("PREPAYMENTS_GIVEUP", getfilelist("PREPAYMENTS_GIVEUP",prePayment.get("PREPAY_ID"),"PREPAYMENTS_GIVEUP"));
	        //storage.addValueObject("TAX_BILL", getfilelist("TAX_BILL",prePayment.get("PREPAY_ID"),"TAX_BILL"));

			//보증정보
			//ValueObject contOutWarr = getContOutWarr(params);
			//storage.addValueObject("contOutWarr", contOutWarr);
			
	 	    // 각국 통화코드에 따른 소숫점 이하 자릿수 구하기
	        //RowSet rowSetGb = getUseCurrencyGBRowSet();
	        //storage.addRowSet("digitRowSet", rowSetGb);  
	        
	        // 계약통화 자릿수 구하기
	        //String digit = getDigit(rowSetGb, (String)prePayment.get("CON_CUR_CD"));
	        //prePayment.set("digit", digit);
	        
			//storage.addValueObject("prePayment", prePayment);
			
			//기타서류 첨부파일 조회
		    RowSet purchaseFileRow = getfileListRowSet("PURCHASE_"+params.get("payreqId"), "");
		    storage.addRowSet("purchaseFileRow", purchaseFileRow);
		}
		
	}

    /**
     * 첨부파일 조회.
	 * @param 
	 * @param 
	 * @return ValueObject
	 */
	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 
	 * @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 ValueObject
     *@return ValueObject
     *기   능 : 선급금 신청 상세 데이터 조회
     *@author OK
     *@version 2014. 6. 23.
     *@since JDK1.7
     */
    public ValueObject getPrePayment(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        ValueObject rowObject = null;
        
        try {

        	sqlstr += " SELECT 			  								  																						\n"
        			+ "     A1.SUB_CONT_MOD_ID,                			  																						\n"
        			+ "     A1.CON_DOC_NO,	                			  																						\n"
        			+ "     A1.MOD_NO,		                			  																						\n"
        			+ "     A1.SUB_CONT_ID,	                			  																						\n"
        			+ "     A1.MOD_GB,		                			  																						\n"
        			+ "     A1.DEPT_CD,		                			  																						\n"
        			+ "     A1.DEPT_NAME,              --현장명			  																						\n"
        			+ "     A1.BUILD_NAME,             --계약명			  																						\n"
        			+ "     A1.CON_CUR_CD,             --계약통화			  																						\n"
        			+ "     C1.CORP_REG_NO,            --사업자번호		  																						\n"
        			+ "     C1.CORP_KOR,               --계약업체명	  																							\n"
        			+ "     C1.BOSS_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_AMT,                --계약금									                			  									\n"
        			+ "     A1.CON_SUPPLY_AMT,         --계약공급가  									              			  									\n"
        			+ "     NVL(A1.CON_SUPPLY_TAMT, 0) AS CON_SUPPLY_TAMT,    	   --계약금 과세								         							\n"
        			+ "     NVL(A1.CON_SUPPLY_NAMT, 0) AS CON_SUPPLY_NAMT,   	   --계약금 면세 																	\n"
        			+ "     A1.CON_SURTAX_AMT,         --계약 부가세								                			  									\n"
        			+ "     NVL(B1.PREPAY_AMT, A1.PREPAY_AMT) AS PREPAY_AMT,    --선급금액		                			  									\n"
        			+ "     NVL(B1.PREPAY_SUPPLY_TAMT, NVL(A1.PREPAY_SUPPLY_TAMT, 0)) AS SUB_PREPAY_SUPPLY_TAMT,  --선금금액 과세		  							\n"
        			+ "     NVL(B1.PREPAY_SUPPLY_NAMT, NVL(A1.PREPAY_SUPPLY_NAMT, 0)) AS SUB_PREPAY_SUPPLY_NAMT,  --선금금액 면세  								\n"
        			+ "     NVL(B1.PREPAY_SURTAX_AMT, NVL(A1.PREPAY_SURTAX_AMT, 0)) AS SUB_PREPAY_SURTAX_AMT,     --선급금액 부가세    							\n"
        			+ "     TO_CHAR(TRUNC((A1.PREPAY_AMT / A1.CON_AMT) * 100, 2), 'FM990.0') AS  PREPAY_AMT_PERCENT,    --선급금 요율								\n"
        			+ "     NVL(B1.REQ_PREPAY_AMT, 0) AS REQ_PREPAY_AMT, --선급 신청금액					           			  									\n"
        			+ "     NVL(B1.REQ_PREPAY_SUPPLY_TAMT, 0) AS REQ_PREPAY_SUPPLY_TAMT,    --신청금액_과세	       			  									\n"
        			+ "     NVL(B1.REQ_PREPAY_SUPPLY_NAMT, 0) AS REQ_PREPAY_SUPPLY_NAMT,    --신청금액_면세	      			  									\n"
        			+ "     NVL(B1.REQ_PREPAY_SURTAX_AMT, 0) AS REQ_PREPAY_SURTAX_AMT,      --신청금액_부가세          			  									\n"
        			+ "     TO_CHAR(TRUNC((NVL(B1.PREPAY_AMT, A1.PREPAY_AMT) / A1.CON_AMT) * 100, 2), 'FM999.0') AS REQ_PREPAY_PERCENT,							\n"
        			+ "     NVL(A1.PREPAY_AMT, 0) - NVL(B1.REQ_PREPAY_AMT, 0) AS GIVE_AMT,   --선급 포기금액 합계     			  									\n"
        			+ "     NVL(A1.PREPAY_SUPPLY_TAMT, 0) - NVL(B1.REQ_PREPAY_SUPPLY_TAMT, 0) AS GIVE_SUPPLY_TAMT,  --선급 포기금액_과세							\n"
        			+ "     NVL(A1.PREPAY_SUPPLY_NAMT, 0) - NVL(B1.REQ_PREPAY_SUPPLY_NAMT, 0) AS GIVE_SUPPLY_NAMT,  --선급 포기금액_면세							\n"
        			+ "     NVL(A1.PREPAY_SURTAX_AMT, 0) - NVL(B1.REQ_PREPAY_SURTAX_AMT, 0) AS GIVE_SURTAX_AMT,     --선급 포기금액_부가세							\n"
        			+ "     TO_CHAR(TRUNC(((NVL(A1.PREPAY_AMT, 0) - NVL(B1.REQ_PREPAY_AMT, 0)) / A1.CON_AMT) * 100, 2), 'FM990.0') AS GIVE_PERCENT,				\n"
        			+ "     B1.PREPAY_ID,       --선급ID											                			  									\n"
        			+ "     B1.STATUS_CD,       --선급상태										                			  									\n"
        			+ "     F_GET_CODE_NAME('PREPAY_STATUS_CD', B1.STATUS_CD) AS STATUS_NM,          --선급상태명           			  							\n"
        			+ "     C1.CON_CORP_ID,  --계약업체ID											                			  									\n"
        			+ "     NVL(B1.TAX_METHOD_CD, '1') AS TAX_METHOD_CD,                --발행방법		    	  			  									\n"
        			+ "     NVL(B1.TAX_DY, TO_CHAR(SYSDATE, 'YYYY-MM-DD')) AS TAX_DY,   --작성일자	              			  									\n"
        			+ "     B1.TAX_CHARG_NM AS USER_NAME,   --담당자명										      			  									\n"
        			+ "     B1.TAX_CHARG_HP AS HP,    		--담당자 핸드폰번호								       			  									\n"
        			+ "     B1.TAX_CHARG_EMAIL AS EMAIL, 	--담당자 이메일  										   			  									\n"
        			+ "     NVL(B1.REQ_DIV_CD, '1') AS REQ_DIV_CD,   --신청구분					                			  									\n"
        			+ "     DECODE(B1.SIGN_DT, null, '미서명', to_char(B1.SIGN_DT, 'yyyy-mm-dd HH24:MI:SS') ) AS  CUST_SIGN_DATE,     --서명일시		\n"
        			+ "     B1.RTN_REASON,		--반려사유																										\n"
        			+ "     to_char(to_date(B1.RTN_DATE, 'yyyymmdd'), 'yyyy-mm-dd') AS RTN_DATE     --반려일자													\n"
        			+ " FROM (SELECT A.*																														\n"
        			+ "       FROM SUB2000T A,																													\n"
        			+ "       (SELECT SUB_CONT_ID, MAX(MOD_NO) AS MAX_MOD_NO FROM SUB2000T GROUP BY SUB_CONT_ID) B												\n"
        			+ "       WHERE 1=1																															\n"
        			+ "       AND A.SUB_CONT_ID = B.SUB_CONT_ID																									\n"
        			+ "       AND A.MOD_NO = B.MAX_MOD_NO																										\n"
        			+ "       ) A1,																																\n"
        			+ "       SUB1030T B1,																														\n"
        			+ "       SUB1020T C1																														\n"
        			+ " WHERE A1.SUB_CONT_ID = B1.SUB_CONT_ID(+)																								\n"
        			+ " AND A1.SUB_CONT_ID =  C1.SUB_CONT_ID																									\n"
        			+ " AND A1.CON_CLS_YN = '9'																													\n";

        	sqlstr += " AND A1.SUB_CONT_MOD_ID = '"+ params.get("selectSubContModId") +"'																		\n";
        	
            search.setStatement(sqlstr);
            WAFLogger.debug(sqlstr);
            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;
        }
    }
    
    /**
     *@param ValueObject
     *@return ValueObject
     *기   능 : 외주계약 보증정보 데이터 조회
     *@author OK
     *@version 2014. 6. 23.
     *@since JDK1.7
     */
    public ValueObject getContOutWarr(ValueObject Params) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        ValueObject rowObject = null;
        
        try {
        	
        	sqlstr += " SELECT                              \n"
    				+ "    SUB_CONT_ID,                     \n"
    				+ "    WARR_GUBUN,                      \n"
    				+ "    WARR_SEQ,                        \n"
    				+ "    WARR_WAMT,                       \n"
    				+ "    WARR_WRATE,                      \n"
    				+ "    WARR_KAMT1,                      \n"
    				+ "    WARR_KAMT2,                      \n"
    				+ "    WARR_COMMSN,                     \n"
    				+ "    WARR_KCLS,                       \n"
    				+ "    WARR_SDATE,                      \n"
    				+ "    WARR_EDATE,                      \n"
    				+ "    TO_CHAR(TO_DATE(WARR_SDATE,'YYYYMMDD'),'YYYY-MM-DD') ||' ~ '|| TO_CHAR(TO_DATE(WARR_EDATE,'YYYYMMDD'),'YYYY-MM-DD') AS WARR_SDATE_EDATE,                \n"
    				+ "    WARR_WGNO,                       \n"
    				+ "    WARR_GOFFICE,                    \n"
    				+ "    WARR_GJIJUM,                     \n"
    				+ "    WARR_GTEL,                       \n"
    				+ "    WARR_GMAN,                       \n"
    				+ "    WARR_HEADCONF,                   \n"
    				+ "    WARR_GREM,                       \n"
    				+ "    WARR_INDAY,                      \n"
    				+ "    WARR_INUSR,                      \n"
    				+ "    WARR_UPDAY,                      \n"
    				+ "    WARR_UPUSR,                      \n"
    				+ "    GUAR_ONOFF_YN,                   \n"
    				+ "    GUAR_ORGAN_CODE,                 \n"
    				+ "    CAUSECODE,                       \n"
    				+ "    TO_CHAR(TO_DATE(PAPERDATE,'YYYYMMDD'),'YYYY-MM-DD') AS PAPERDATE,                       \n"
    				+ "    REJECTMENT,                      \n"
    				+ "    EXEMPTMENT,                      \n"
    				+ "    ACCDOCSTATUS,                    \n"
    				+ "    ACCDOC_SEQ,                      \n"
    				+ "    GRTREQNO,                        \n"
    				+ "    YEARINSUREYN,                    \n"
    				+ "    REQDATE,                         \n"
    				+ "    GUAR_STATUS,                     \n"
    				+ "    BOND_SEQ,                        \n"
    				+ "    CONTSEQ,                         \n"
    				+ "    GUARRATEGBNCODE,                 \n"
    				+ "    LONGGBN,                         \n"
    				+ "    CONTTYPE,                        \n"
    				+ "    ORDEROWNER,                      \n"
    				+ "    CONTAMT,                         \n"
    				+ "    CONTDATE,                        \n"
    				+ "    CONTSTARTDATE,                   \n"
    				+ "    CONTCLOSEDATE,                   \n"
    				+ "    PREPAYREFYN,                     \n"
    				+ "    EXIISTREFYN,                     \n"
    				+ "    TRADESIGNYN,                     \n"
    				+ "    TRADESIGNDATE,                   \n"
    				+ "    CONTCHECKDATE,                   \n"
    				+ "    CONTEXISTENCEAMT,                \n"
    				+ "    PJTID,                           \n"
    				+ "    PAYMETHOD,                       \n"
    				+ "    CONTORGBNCODE,                   \n"
    				+ "    GUAR_CONTAMT,                    \n"
    				+ "    FILETITLE,                       \n"
    				+ "    FILE_NAME,                       \n"
    				+ "    FILEEXT,                         \n"
    				+ "    FILESIZE,                        \n"
    				+ "    EXFILETITLE,                     \n"
    				+ "    EXFILENAME,                      \n"
    				+ "    DEPNM,                           \n"
    				+ "    POSITION,                        \n"
    				+ "    EMPNM,                           \n"
    				+ "    HPNO,                            \n"
    				+ "    TELNO,                           \n"
    				+ "    EMAIL,                           \n"
    				+ "    ITEMDOC,                         \n"
    				+ "    ITEMEXT,                         \n"
    				+ "    ITEMPATH,                        \n"
    				+ "    CONTORDERNO                      \n"
    				+ " FROM CONT_OUT_WARR                  \n"
    				+ " WHERE 1=1			                \n";
        	
        	sqlstr += " AND SUB_CONT_ID = '"+ params.get("selectSubContModId") +"'	\n";
        	sqlstr += " AND WARR_GUBUN = '1'										\n";	//선급금보증
        	sqlstr += " AND WARR_SEQ = '1'											\n";	//일련번호
            search.setStatement(sqlstr);
            WAFLogger.debug(sqlstr);
            listRowSet = search.execute();
            if(listRowSet.size() != 0){
            	rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
            }
            
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getContOutWarr() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
        finally {

            if( rowObject == null ) rowObject = new ValueObject();
            return rowObject;
        }
    }  

    private String getDigit(RowSet listRowSet, String currencyGB) {
    	ValueObject row = null;
        int size = (listRowSet != null ? listRowSet.size() : 0);

	    if( listRowSet != null && listRowSet.size() > 0) {

            for( int i = 0 ; i < size ; i++ ) {

                row = RowSetUtility.getValueObject(listRowSet.getRow(i));
                
                if (currencyGB.equals((String)row.get("DETAILCD"))) {
                	return (String)row.get("CHG_CODE01");
                }
            }
	    }
	    
	    return "6";
    }
    
	public RowSet getUseCurrencyGBRowSet() {

        Search search = new SearchImpl();
        RowSet rowSet = null;
        String sqlstr = "";
        try {
        	
        	sqlstr += " SELECT DETAILCD, DETAILNM, F_GET_CURRENCY_DIGIT(DETAILCD) CHG_CODE01 FROM EBID_COM911T      	\n"
            		+ " WHERE BASECD = '911'                    														\n"
            		+ " ORDER BY CHG_CODE20                       														\n" ; 
    
            search.setStatement(sqlstr);
            rowSet = search.execute();
        }
        catch(Exception e) {
    
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
        }
        finally {
            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }    
    
    /** 첨부서류조회 
     * @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;
    }
    
}
