/**
 * @(#)file    PreRequestSignBiz.java
 * @(#)author  OK
 * @(#)version 1.0
 *
 * Copyright (c) www.udapsoft.co.kr, 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.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;

import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;


/**
 * 
 * @version 1.0
 */
public class PreRequestSignBiz extends Biz {

	public PreRequestSignBiz(HandlerStorage storage) {
		super(storage);
	}

    public void doBiz() throws Exception {
    	
        String tmpslip_subContModId = params.get("tmpslip_subContModId");
		
		ValueObject vo = new ValueObject();
        
		vo = getPayItem(tmpslip_subContModId);	//계약정보조회
        String docTitle = "";
        
        //2015.8.10 조재윤과장 요청. 타이틀 순서변경
        //docTitle = "- "+vo.get("BUILD_NAME")+"_"+vo.get("DEPT_NAME");
	    docTitle = "["+vo.get("DEPT_NAME") + "]" + vo.get("BUILD_NAME");
        
        params.set("docTitle", docTitle);
        
        storage.addValueObject("payList", vo);
		
   }

    public ValueObject getPayItem(String tmpslip_purContId) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        String sqlstr = "";
        ValueObject rowObject = null;
        
        try {

        	sqlstr += " SELECT 			  								  																						\n"
        			+ "     A1.PUR_CONT_MOD_ID AS SUB_CONT_MOD_ID,		  																						\n"
        			+ "     A1.ORD_DOC_NO AS CON_DOC_NO,       			  																						\n"
        			+ "     A1.MOD_NO,		                			  																						\n"
        			+ "     A1.PUR_CONT_ID AS SUB_CONT_ID,     			  																						\n"
        			+ "     A1.MOD_GB,		                			  																						\n"
        			+ "     A1.DEPT_CD,		                			  																						\n"
        			+ "     A1.DEPT_NAME,              --현장명			  																						\n"
        			+ "     A1.CST_DOC_NAME AS BUILD_NAME,             --계약명																					\n"
        			+ "     A1.CURRENCY_GB AS CON_CUR_CD,             --계약통화																					\n"
        			+ "     B1.REG_NO AS CORP_REG_NO,            --사업자번호																						\n"
        			+ "     B1.CORP_KOR,               --계약업체명	  																							\n"
        			+ "     B1.BOSS_NAME,               --대표이사 		  																						\n"
        			+ "     TO_CHAR(A1.ORD_START_DATE, 'YYYY-MM-DD') || ' ~ ' || TO_CHAR(A1.ORD_END_DATE, 'YYYY-MM-DD') AS START_END_WORK_DATE,					\n"
        			+ "     F_GET_D_CURRENCY( NVL( A1.ORD_AMT, 0 ) , F_GET_CURRENCY_DIGIT( NVL( A1.CURRENCY_GB, 'KRW' ) ) ) CON_AMT,      --계약금액합계			\n"
        			+ "     F_GET_D_CURRENCY( NVL( A1.ORD_SUPPLY_AMT, 0 ) , F_GET_CURRENCY_DIGIT( NVL( A1.CURRENCY_GB, 'KRW' ) ) ) AS CON_SUPPLY_AMT,         --계약공급가					              			  									\n"
        			+ "     F_GET_D_CURRENCY( NVL( A1.ORD_SURTAX_AMT, 0 ) , F_GET_CURRENCY_DIGIT( NVL( A1.CURRENCY_GB, 'KRW' ) ) ) AS CON_SURTAX_AMT,         --계약 부가세			                			  									\n"
        			+ "     F_GET_D_CURRENCY( NVL(B1.PREPAY_AMT, 0) , F_GET_CURRENCY_DIGIT( NVL( A1.CURRENCY_GB, 'KRW' ) ) ) AS PREPAY_AMT,    	 --선급금액				                			  									\n"
        			+ "     F_GET_D_CURRENCY( NVL(B1.PREPAY_SUPPLY_AMT, 0), F_GET_CURRENCY_DIGIT( NVL( A1.CURRENCY_GB, 'KRW' ) ) ) AS PREPAY_SUPPLY_AMT,  --선급금_공급가  											  						\n"
        			+ "     F_GET_D_CURRENCY( NVL(B1.PREPAY_SURTAX_AMT, 0), F_GET_CURRENCY_DIGIT( NVL( A1.CURRENCY_GB, 'KRW' ) ) ) AS PREPAY_SURTAX_AMT,     --선급금액 부가세								    							\n"
        			+ "     TO_CHAR(TRUNC((B1.PREPAY_AMT / A1.ORD_AMT) * 100, 2), 'FM990.0') AS  PREPAY_AMT_PERCENT,    --선급금 요율								\n"
        			+ "     F_GET_D_CURRENCY( NVL(B1.REQ_PREPAY_AMT, 0), F_GET_CURRENCY_DIGIT( NVL( A1.CURRENCY_GB, 'KRW' ) ) ) AS REQ_PREPAY_AMT, --선급 신청금액					           			  									\n"
        			+ "     F_GET_D_CURRENCY( NVL(B1.REQ_PREPAY_SUPPLY_AMT, 0), F_GET_CURRENCY_DIGIT( NVL( A1.CURRENCY_GB, 'KRW' ) ) ) AS REQ_PREPAY_SUPPLY_AMT, --선급 신청공급가액		       			  									\n"
        			+ "     F_GET_D_CURRENCY( NVL(B1.REQ_PREPAY_SURTAX_AMT, 0), F_GET_CURRENCY_DIGIT( NVL( A1.CURRENCY_GB, 'KRW' ) ) ) AS REQ_PREPAY_SURTAX_AMT,      --신청금액_부가세          			  									\n"
        			+ "     TO_CHAR(TRUNC((NVL(B1.REQ_PREPAY_AMT, 0) / A1.ORD_AMT) * 100, 2), 'FM990.0') AS REQ_PREPAY_PERCENT,									\n"
        			+ "     F_GET_D_CURRENCY( NVL(B1.PREPAY_AMT, 0) - NVL(B1.REQ_PREPAY_AMT, 0), F_GET_CURRENCY_DIGIT( NVL( A1.CURRENCY_GB, 'KRW' ) ) ) AS GIVE_AMT,   --선급 포기금액 합계     			  									\n"
        			+ "     F_GET_D_CURRENCY( NVL(B1.PREPAY_SUPPLY_AMT, 0) - NVL(B1.REQ_PREPAY_SUPPLY_AMT, 0), F_GET_CURRENCY_DIGIT( NVL( A1.CURRENCY_GB, 'KRW' ) ) ) AS GIVE_SUPPLY_AMT,   --선급 포기금액 공급가액							\n"
        			+ "     F_GET_D_CURRENCY( NVL(B1.PREPAY_SURTAX_AMT, 0) - NVL(B1.REQ_PREPAY_SURTAX_AMT, 0), F_GET_CURRENCY_DIGIT( NVL( A1.CURRENCY_GB, 'KRW' ) ) ) AS GIVE_SURTAX_AMT,   --선급 포기금액_부과세							\n"
        			+ "     TO_CHAR(TRUNC(((NVL(B1.PREPAY_AMT, 0) - NVL(B1.REQ_PREPAY_AMT, 0)) / A1.ORD_AMT) * 100, 2), 'FM990.0') AS GIVE_PERCENT,				\n"
        			+ "     B1.PREPAY_ID,       --선급ID											                			  									\n"
        			+ "     B1.PREPAY_PROC_GB AS STATUS_CD,       --선급상태						                			  									\n"
        			+ "     F_GET_CODE_NAME('PREPAY_STATUS_CD', NVL(B1.PREPAY_PROC_GB, '00')) AS STATUS_NM,          --선급상태명        			  							\n"
        			+ "     B1.CORP_ID AS CON_CORP_ID,  --계약업체ID								                			  									\n"
        			+ "     NVL(B1.TAX_METHOD_CD, '1') AS TAX_METHOD_CD,                --발행방법		    	  			  									\n"
        			+ "     NVL(TO_CHAR(TO_DATE(B1.TAX_DY, 'YYYY-MM-DD'), 'YYYY-MM-DD'), 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"
        			+ "     DECODE(NVL(B1.REQ_DIV_CD, '1'), '1', '선급신청', '2', '부분신청', '3', '포기신청') AS REQ_DIV_CD,   --신청구분							\n"
        			+ "     DECODE(B1.SUPP_SIGN_DT, null, '미서명', to_char(B1.SUPP_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"
        			+ "     ,NVL(E.CHK6, 'A') FOREIGN_CHK																															\n"
 					+ "     ,DECODE(NVL(E.CHK6, 'A'), 'N', '아니오', 'Y', '예', '비대상') FOREIGN_NM																				    \n"
 					+ " FROM PUR2000T A1,																													\n"
        			+ "      PUR1030T B1,																														\n"
        			+ "      BID_FOREIGN_CHK E																																																				\n"
            		+ " WHERE A1.PUR_CONT_MOD_ID = B1.PUR_CONT_MOD_ID																						\n"
            		+ " AND B1.PREPAY_ID = E.BID_DEG(+) AND E.CHK5(+) = 'R'																																																	\n"
        			+ " AND A1.CON_CLS_YN = '9'																													\n";

        	sqlstr += " AND A1.PUR_CONT_MOD_ID = '"+tmpslip_purContId +"'																						\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;
        }
    }
    
    public RowSet getPayItemList(String PayItemListId) {
    	Search search = new SearchImpl();
    	RowSet listRowSet = null;
    	String sqlstr = "";
    	
    	try {
    		
    		sqlstr += " SELECT 																											\n"
    				+ "     PAYREQ_ITEM_ID, PAYREQ_ID, STOCK_NO, A.MATERIAL_CD, ITEM_QTY, ITEM_PRI, ITEM_AMT,  GUBUN, EXC_PRI, EXC_AMT,	\n"
    				+ "     B.MATERIAL_NAME AS MATERIAL_NAME,																			\n"
    				+ "     B.MATERIAL_SPEC AS MATERIAL_SPEC																			\n"
    				+ " FROM PMM_PAYREQ_ITEM A, PMM_COMM_MATERIAL_MASTER B								 								\n"
    				+ " WHERE 1=1																										\n"
    				+ " AND A.MATERIAL_CD = B.MATERIAL_CD																				\n"
    				+ " AND PAYREQ_ID = '"+PayItemListId+"'																				\n";
    		
    		search.setStatement(sqlstr);
    		WAFLogger.debug(sqlstr);
    		listRowSet = search.execute();
    		
    	}
    	catch(Exception e) {
    		WAFLogger.error(this.getClass().getName() + " --> getPayItemList() : " + e.getMessage());
    		WAFLogger.error("SQL : " + search.getStatement());
    		WAFLogger.error(e);
    		return null;
    	}
    	finally {
    		
    		if( listRowSet == null ) listRowSet = null;
    		return listRowSet;
    	}
    }
    
    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(" 		   C.FILE_SIZE								\n");
    		sql.append("	FROM EBID_COM911T A, PUB0510T B, PUB0500T C	\n");
    		sql.append("	WHERE A.BASECD = 'PUR_PAY_FILE'					\n");
    		sql.append("	AND A.DETAILCD = B.ID(+)						\n");
    		sql.append("	AND B.FILE_ID = C.FILE_ID(+)					\n");
    		sql.append("	AND B.FILE_GB(+) = '"+payreqId+"'				\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;
    }
    
   public RowSet getList(String keyParam, String deposit_cd, String deposit_seq) {
	   	
	   	Search search      = new SearchImpl();
	   	RowSet listRowSet = null;
	   	StringBuffer sql = new StringBuffer();
	   	
	   	try {
	   		
	   		
	   		sql.append("            SELECT    B.STOCK_NO,                                                                                         \n");
	   		sql.append("                      TO_CHAR( TO_DATE( B.STOCK_DATE, 'YYYYMMDD'), 'YYYY-MM-DD') AS STOCK_DATE,                           \n");
	   		sql.append("                      B.MATERIAL_CD,                                                                                      \n");
	   		sql.append("                      SUBSTR(C.MATERIAL_NAME, 0 ,20) AS MATERIAL_NAME,                                                    \n");
	   		sql.append("                      F_GET_CODE_NAME2('UNIT_KIND_GB', C.UNIT_KIND) AS UNIT_KIND,                                         \n");
	   		sql.append("                      B.ITEM_QTY,                                                                                         \n");
	   		sql.append("                      B.ITEM_PRI,                                                                                         \n");
	   		sql.append("                      B.ITEM_AMT                                                                                          \n");
	   		sql.append("            FROM      PUR1100T A,                                                                                         \n");
	   		sql.append("                      PMM_PAYREQ_ITEM  B,                                                                                 \n");
	   		sql.append("                      PMM_COMM_MATERIAL_MASTER C                                                                          \n");
	   		sql.append("            WHERE A.PAYREQ_ID = B.PAYREQ_ID                                                                               \n");
	   		sql.append("            AND      B.MATERIAL_CD = C.MATERIAL_CD                                                                        \n");
	   		sql.append("            AND      A.PAYREQ_ID = '"+keyParam+"'                                                                         \n");
	   		sql.append("           ORDER BY B.STOCK_DATE DESC     																																								\n");
	   		            
	   		
	   		search.setStatement(sql.toString());
	   		listRowSet = search.execute();
	   		
	   	} catch (Exception e) {
	   		WAFLogger.debug("[Exception]getList()... "+ e);
	   		storage.setDetailMessage(e);
	   	} finally {
	   		if (listRowSet == null)
	   			listRowSet = new RowSetImpl();
	   	}
	   	return listRowSet;
	}

}
