/**
 *
 * @(#)author  [ok]
 * @(#)version 1.0
 * @(#)date    2014. 06. 18
 * @(#)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.portal.payment.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 AdvencePayRequestInitBiz extends Biz {
	
	public AdvencePayRequestInitBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {

		WAFLogger.debug("협력업체포탈 선급금 신청_외주 상세 INIT");
		WAFLogger.debug(params.get("selectSubContModId"));
		
		//신청구분ComboList
		RowSet rowSet = getPerpayReqDiv();
		storage.addRowSet("perpayReqDivComboList", rowSet);
		
		//기본 정보, 세금계산서 정보,선급금  
		ValueObject prePayment = getPrePayment(params);
		storage.addValueObject("prePayment", prePayment);
		
		//첨부파일
        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);
		
		
	}
	
    /**
     * 첨부파일 조회.
	 * @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"
        			+ "     A.SUB_CONT_MOD_ID,                			  	\n"
        			+ "     A.SUB_CONT_ID,               		            \n"
        			+ "     A.CON_DOC_NO,        	                        \n"
        			+ "     A.MOD_NO,             	                        \n"
        			+ "     A.MOD_GB,                 	                    \n"
        			+ "     A.MOD_CON_RSN,                                  \n"
        			+ "     A.MOD_REQ_DATE,                                 \n"
        			+ "     A.MOD_CORP_RSN,                                 \n"
        			+ "     A.DEPT_CD,                                      \n"
        			+ "     A.DEPT_NAME,                                    \n"
        			+ "     A.CON_GB,                                       \n"
        			+ "     A.DEALER_NM,                                    \n"
        			+ "     A.BUILD_NAME,                                   \n"
        			+ "     A.UND_CON_NAME,                                 \n"
        			+ "     A.SUB_UND_CON_NAME,                             \n"
        			+ "     A.WORK_KIND_CD,                                 \n"
        			+ "     A.ELEC_CON_YN,                                  \n"
        			+ "     A.REQ_DEPT_CD,                                  \n"
        			+ "     A.REQ_DATE,                                     \n"
        			+ "     A.REQ_USERID,                                   \n"
        			+ "     A.REQ_USERNM,                                   \n"
        			+ "     A.REQ_DOC_NO,                                   \n"
        			+ "     A.CST_DATE,                                     \n"
        			+ "     A.CON_IN_DATE,                                  \n"
        			+ "     A.CON_DATE,                                     \n"
        			+ "     A.PAY_COND_GB,                                  \n"
        			+ "     A.PAY_WAY_CASH,                                 \n"
        			+ "     A.PAY_CONTENT,                                  \n"
        			+ "     A.START_WORK_DATE,                              \n"
        			+ "     A.END_WORK_DATE,                                \n"
        			+ "     A.WORK_PLACE,                                   \n"
        			+ "     A.PAY_WAY_BILL,                                 \n"
        			+ "     A.CON_AMT,                                      \n"
        			+ "     A.CON_SUPPLY_AMT,                               \n"
        			+ "     A.CON_SUPPLY_TAMT,                              \n"
        			+ "     A.CON_SUPPLY_NAMT,                              \n"
        			+ "     A.CON_SURTAX_AMT,                               \n"
        			+ "     A.LABOR_AMT,                                    \n"
        			+ "     NVL(B.PREPAY_AMT,A.PREPAY_AMT) AS PREPAY_AMT,															                                     \n"
        			+ "     A.PREPAY_SUPPLY_AMT,                            \n"
        			+ "     NVL(B.PREPAY_SUPPLY_TAMT, A.PREPAY_SUPPLY_TAMT) AS SUB_PREPAY_SUPPLY_TAMT,											                         \n"
        			+ "     NVL(B.PREPAY_SUPPLY_NAMT, A.PREPAY_SUPPLY_NAMT) AS SUB_PREPAY_SUPPLY_NAMT, 																	 \n"
        			+ "     NVL(B.PREPAY_SURTAX_AMT, A.PREPAY_SURTAX_AMT) AS SUB_PREPAY_SURTAX_AMT,    																	 \n"
        			//+ "     TO_CHAR(TRUNC(((A.PREPAY_SUPPLY_AMT + A.PREPAY_SURTAX_AMT) / A.PREPAY_AMT)  * 100, 2), 'FM999.0') AS  PREPAY_AMT_PERCENT,                    \n"
        			+ "     TO_CHAR(TRUNC((A.PREPAY_AMT / A.CON_AMT) * 100, 2), 'FM999.0') AS  PREPAY_AMT_PERCENT,                    \n"
        			+ "     A.CON_EST_GUAR_RATE,                            \n"
        			+ "     A.CON_EST_GUAR_AMT,                             \n"
        			+ "     A.CON_GUAR_TERM,                                \n"
        			+ "     A.DEF_EST_GUAR_RATE,                            \n"
        			+ "     A.DEF_GUAR_AMT,                                 \n"
        			+ "     A.DEF_EST_TERM,                                 \n"
        			+ "     A.DELAY_RATE,                                   \n"
        			+ "     A.LABOR_GUAR_JOIN_AMT_GB,                       \n"
        			+ "     A.CON_SPE_TXT,                                  \n"
        			+ "     A.ADD_FILE_KIND,                                \n"
        			+ "     A.CON_CHARGE_ID,                                \n"
        			+ "     A.CON_CHARGE_NAME,                              \n"
        			+ "     A.CORP_SIGN_DATE,                               \n"
        			+ "     A.COMPANY_SIGN_DATE,                            \n"
        			+ "     A.PAY_GROUP,                                    \n"
        			+ "     A.PAYMENT_TERMS,                                \n"
        			+ "     A.ACCT_GB,                                      \n"
        			+ "     A.PROOF_CD,                                     \n"
        			+ "     A.CON_CLS_YN,                                   \n"
        			+ "     A.CON_CUR_CD,                                   \n"
        			+ "     A.CON_EXCHANGE_DATE,                            \n"
        			+ "     A.CON_WON_RATE,                                 \n"
        			+ "     A.PAY_ITEM,                                     \n"
        			+ "     A.COST_CODE,                                    \n"
        			+ "     A.COST_TYPE,                                    \n"
        			+ "     A.CONTRACTID,                                   \n"
        			+ "     A.G_ID,                                         \n"
        			+ "     A.P_ID,                                         \n"
        			+ "     A.TEMPLATE_CD,                                  \n"
        			+ "     A.CONT_HASH,                                    \n"
        			+ "     A.CONT_HTML,                                    \n"
        			+ "     A.TRUE_RANDOM,                                  \n"
        			+ "     A.CRE_BY,                                       \n"
        			+ "     A.CRE_DATE,                                     \n"
        			+ "     A.UPD_BY,                                       \n"
        			+ "     A.UPD_DATE,                                      \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"
					+ "     B.PREPAY_ID,									\n"
					+ "     B.SUB_CONT_MOD_ID,                          	\n"
					+ "     B.PREPAY_SEQ,                                  	\n"
					+ "     B.CORP_ID,                                     	\n"
					+ "     B.STATUS_CD,            	                    \n"
					+ "     B.PREPAY_DATE,              	                \n"
					+ "     B.PREPAY_AMT AS PRE_PREPAY_AMT,    	            \n"
					+ "     B.PREPAY_SUPPLY_AMT,                           	\n"
					+ "     B.PREPAY_SUPPLY_TAMT,                          	\n"
					+ "     B.PREPAY_SUPPLY_NAMT,                          	\n"
					+ "     B.PREPAY_SURTAX_AMT,                           	\n"
					+ "     NVL(B.REQ_PREPAY_AMT, A.PREPAY_AMT) AS REQ_PREPAY_AMT,																	\n"
					+ "     NVL(B.REQ_PREPAY_SUPPLY_TMAT, A.PREPAY_SUPPLY_TAMT) AS REQ_PREPAY_SUPPLY_TMAT,					                     	\n"
					+ "     NVL(B.REQ_PREPAY_SUPPLY_NAMT, A.PREPAY_SUPPLY_NAMT) AS REQ_PREPAY_SUPPLY_NAMT,					                     	\n"
					+ "     NVL(B.REQ_PREPAY_SURTAX_AMT, A.PREPAY_SURTAX_AMT) AS REQ_PREPAY_SURTAX_AMT,						                      	\n"
					+ "     TO_CHAR(TRUNC((NVL(B.REQ_PREPAY_AMT, A.PREPAY_AMT) / A.CON_AMT) * 100, 2), 'FM999.0') AS REQ_PREPAY_PERCENT,           	\n"
					+ "     NVL(B.PREPAY_AMT,A.PREPAY_AMT) - NVL(B.REQ_PREPAY_AMT, A.PREPAY_AMT) AS GIVE_AMT,          								\n"
					+ "     NVL(B.REQ_PREPAY_SUPPLY_TMAT, A.PREPAY_SUPPLY_TAMT) - NVL(B.PREPAY_SUPPLY_TAMT, A.PREPAY_SUPPLY_TAMT) AS GIVE_SUPPLY_TMAT, 	\n"
					+ "     NVL(B.REQ_PREPAY_SUPPLY_NAMT, A.PREPAY_SUPPLY_NAMT) - NVL(B.PREPAY_SUPPLY_NAMT, A.PREPAY_SUPPLY_NAMT) AS GIVE_SUPPLY_NAMT, 	\n"
					+ "     NVL(B.REQ_PREPAY_SURTAX_AMT, A.PREPAY_SURTAX_AMT) - NVL(B.PREPAY_SURTAX_AMT, A.PREPAY_SURTAX_AMT) AS GIVE_SURTAX_AMT, 	\n"
					+ "     TO_CHAR(TRUNC(((NVL(B.REQ_PREPAY_AMT, A.PREPAY_AMT) - NVL(B.PREPAY_AMT,A.PREPAY_AMT)) / A.CON_AMT) * 100, 2), 'FM999.0') AS GIVE_PERCENT,           	\n"
					+ "     B.PREPAY_DESC,                                 	\n"
					+ "     B.CON_CUR_CD AS PRE_CON_CUR_CD,                	\n"
					+ "     NVL(B.TAX_METHOD_CD, '1') AS TAX_METHOD_CD,    	\n"
					+ "     NVL(B.TAX_DY, TO_CHAR(SYSDATE, 'YYYY-MM-DD')) AS TAX_DY,						                                    	\n"
					+ "     B.TAX_CHARG_NM,                                	\n"
					+ "     B.TAX_CHARG_HP,                                	\n"
					+ "     B.TAX_CHARG_EMAIL,                             	\n"
					+ "     NVL(B.REQ_DIV_CD, '1') AS REQ_DIV_CD,          	\n"
					+ "     B.SIGN_DATA,                                   	\n"
					+ "     B.SIGN_DT,                                     	\n"
					+ "     B.SIGN_DN,                                     	\n"
					+ "     B.CRE_BY,                                      	\n"
					+ "     B.CRE_DATE,                                    	\n"
					+ "     B.UPD_BY,                                      	\n"
					+ "     B.UPD_DATE,                                    	\n"
					+ "		C.CORP_ID,										\n"
					+ "		C.SUB_CONT_MOD_ID,        						\n"
					+ "		C.SIGN_SEQ,               						\n"
					+ "		SUBSTR(VENDCD, 0,3) || '-' || SUBSTR(VENDCD, 3,2) || '-' || SUBSTR(VENDCD, 5,5)AS VENDCD,        						\n"
					+ "		C.MEMBER_NAME,            						\n"
					+ "		C.BOSS_NAME,              						\n"
					+ "		C.POST_CODE,              						\n"
					+ "		C.ADDRESS,                						\n"
					+ "		C.TEL_NUM,                						\n"
					+ "		C.MEMBER_SLNO,            						\n"
					+ "		NVL(B.TAX_CHARG_NM, C.USER_NAME) AS  USER_NAME,										              						\n"
					+ "		C.HP1,                    						\n"
					+ "		C.HP2,                    						\n"
					+ "		C.HP3,                    						\n"
					+ "		NVL(B.TAX_CHARG_HP, C.HP1 || '-' || C.HP2 || '-' || C.HP3) AS HP,														\n"
					+ "		NVL(B.TAX_CHARG_EMAIL, C.EMAIL) AS EMAIL,										                  						\n"
					+ "		DECODE(C.SIGN_DATE, null, '미서명', '서명완료' ) AS  CUST_SIGN_DATE,					              						\n"
					+ "		C.SIGN_DN,                						\n"
					+ "		C.SIGN_DATA					      				\n"
        			+ " FROM SUB2000T A, (							        \n"
        			+ " 	 SELECT *									    \n"
        			+ " 	 FROM PREPAY_OUT						        \n"
        			+ " 	 WHERE PREPAY_SEQ = (					        \n"
        			+ " 	 	SELECT MAX(PREPAY_SEQ) AS PREPAY_SEQ		\n"
        			+ " 		FROM PREPAY_OUT							    \n"
        			+ " 		WHERE SUB_CONT_MOD_ID = '"+ params.get("selectSubContModId") +"'												    \n"
        			+ " 	 	)							    			\n"
        			+ " 	 ) B							    			\n"
        			+ " 	, CONT_OUT_CUST C      							\n"
        			+ " WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID(+)      \n"
        			+ " AND A.SUB_CONT_MOD_ID = C.SUB_CONT_MOD_ID		    \n";
        	
        	sqlstr += " AND A.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;
        }
    }  

    
    
}
