/**
 * @(#)file    PayRequestSignBiz.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 PayRequestSignBiz extends Biz {

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

    public void doBiz() throws Exception {
    	
    	String acctid = params.get("acctid");
        String tmpslip_payreqId = params.get("tmpslip_payreqId");
        String tmpslip_purContId = params.get("tmpslip_purContId");
		
		ValueObject vo = new ValueObject();
        
		vo = getPayItem(tmpslip_payreqId, acctid);	//계약정보조회
        String docTitle = "";
        
	    RowSet purchaseFileRow = getfileListRowSet("PURCHASE_"+tmpslip_payreqId, "");
	    //2015.8.10 조재윤과장 요청. 타이틀 순서변경
		//docTitle = "- "+vo.get("CST_DOC_NAME")+"_"+vo.get("DEPT_NAME");
	    docTitle = "["+vo.get("DEPT_NAME") + "]" + vo.get("CST_DOC_NAME");
	    params.set("docTitle", docTitle);
        
		//대금내역
		RowSet prePaymentList = getPayItemList(tmpslip_payreqId);
		storage.addRowSet("listRowSet", prePaymentList);
        storage.addRowSet("purchaseFileRow" , purchaseFileRow);
        storage.addValueObject("payList", vo);
		
   }

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

        	sqlstr += " SELECT 			  								  																																												\n"
        			+ "     A.PUR_CONT_ID,A.CORP_ID               			  																																												\n"
        			+ "     ,A.DEPT_CD                      --현장코드	 																																												\n"
        			+ "     ,A.DEPT_NAME                    --현장명		  																																												\n"
        			+ "     ,A.CON_GB                       --계약구문  																																													\n"
        			+ "     ,A.ORD_DOC_NO                 	--발주계약번호																																												\n"
        			+ "     ,A.CST_DOC_NAME             	--계약명		  																																												\n"
        			+ "     ,A.CURRENCY_GB                	--통화		  																																												\n"
        			+ "     ,SUBSTR(D.CORP_REG_NO,0,3)||'-'||SUBSTR(D.CORP_REG_NO,4,2)||'-'||SUBSTR(D.CORP_REG_NO,6,5)   as CORP_REG_NO                   --사업자번호																					\n"
        			+ "     ,D.CORP_KOR                     --계약업체명   																																												\n"
        			+ "     ,D.REP_NAME AS BOSS_NAME					  																																												\n"
        			+ "     ,B.DEPOSIT_CD               --지급구분		  																																												\n"
        			+ "     ,B.DEPOSIT_SEQ              --지급차수		  																																												\n"
        			+ "     ,F_GET_D_CURRENCY( NVL( B.ORD_AMT, 0 ) , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) AS ORD_AMT    -- 발주계약금액																									\n"
        			+ "     ,F_GET_D_CURRENCY( NVL( (SELECT SUM(REQ_PREPAY_SUPPLY_AMT) FROM PUR1030T WHERE PUR_CONT_ID = B.PUR_CONT_ID GROUP BY PUR_CONT_ID), 0 ) , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) AS PREPAY_AMT  -- 선급금액		\n"
        			+ "     ,F_GET_D_CURRENCY( NVL( B.BEF_PREPAY_APPLYAMT , 0 ) , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) AS PREPAY_SUPPLY_AMT  -- 기선급공제액																				\n"
        			+ "     ,F_GET_D_CURRENCY( NVL( B.REMAINDER_PREPAY_SUPPLYAMT , 0 )  , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) AS REMAINDER_PREPAY_AMT            --선급잔액																\n"
 					+ "     ,F_GET_D_CURRENCY( NVL( (SELECT SUM(REQ_PREPAY_SUPPLY_AMT) FROM PUR1030T WHERE PUR_CONT_ID = B.PUR_CONT_ID GROUP BY PUR_CONT_ID), 0 )  +  NVL(B.PAYED_APPLYAMT, 0) , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) as PAYED_AMT       --기지불액																								\n"
 					+ "     ,B.PAYED_PERCENT                   --기지불액 지급율																																											\n"
 					+ "     ,F_GET_D_CURRENCY( NVL( B.BEF_ACMP_APPLYAMT, 0 ) , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) as BEF_ACMP_AMT --전기성고																								\n"
 					+ "     ,B.BEF_ACMP_PERCENT                --전기성고	 지급율																																											\n"
 					+ "     ,F_GET_D_CURRENCY( NVL( B.CUR_REQ_AMT, 0 ) , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) as CUR_REQ_AMT   --금회청구액																								\n"
 					+ "     ,B.CUR_REQ_PERCENT                 --금회 청구 지급율																																											\n"
 					+ "     ,F_GET_D_CURRENCY( NVL( B.CUR_ACMP_APPLYAMT, 0 ) , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) as CUR_ACMP_AMT --금회기성고																							\n"
 					+ "     ,B.CUR_ACMP_PERCENT                --금회기성고 지급율																																											\n"
 					+ "     ,F_GET_D_CURRENCY( NVL( (SELECT SUM(REQ_PREPAY_SUPPLY_AMT) FROM PUR1030T WHERE PUR_CONT_ID = B.PUR_CONT_ID GROUP BY PUR_CONT_ID), 0 )  +  NVL(B.PAYED_APPLYAMT, 0)  + NVL( B.CUR_REQ_AMT, 0 )  , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) as CUR_PAYED_AMT --금회포함지불액																					\n"
 					+ "     ,B.CUR_PAYED_PERCENT               --금회포함지불액 지급율																																										\n"
 					+ "     ,F_GET_D_CURRENCY( NVL( B.CUR_BEF_ACMP_SUPPLYAMT, 0 ) , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) as CUR_BEF_ACMP_AMT --금회포함기성고																				\n"
 					+ "     ,B.CUR_BEF_ACMP_PERCENT        	   --금회포함기성고 지급율																																										\n"
 					+ "     ,F_GET_D_CURRENCY(  NVL( B.ORD_AMT, 0 ) - (NVL( (SELECT SUM(REQ_PREPAY_SUPPLY_AMT) FROM PUR1030T WHERE PUR_CONT_ID = B.PUR_CONT_ID GROUP BY PUR_CONT_ID), 0 )  +  NVL(B.PAYED_APPLYAMT, 0)  + NVL( B.CUR_REQ_AMT, 0 ) ) , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) as REMAINDER_AMT  --잔액																						\n"
 					+ "     ,B.REMAINDER_ACMP_PERCENT               --잔액 지급율																																											\n"
 					+ "     ,F_GET_D_CURRENCY( NVL( B.REMAINDER_ACMP_SUPPLYAMT, 0 ) , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) as REMAINDER_ACMP_AMT   --미기성고																				\n"
 					+ "     ,B.REMAINDER_ACMP_PERCENT          --미기성고 지급율																																											\n"
 					//+ "     ,REPLACE(B.RE_MARKS,CHR(13)||CHR(10),'<br/>') as RE_MARKS               																																																	\n"
 					+ "     ,B.RE_MARKS as RE_MARKS               																																																	\n"
 					+ "     ,(SELECT DEPT_NM AS BUSEO_NM FROM ETEC_ERPIF.ACZ10101 WHERE ACC_DEPT_CD = B.COOP_DEPT_CD) AS  BUSEO_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_RESN 																																																			\n"
 					+ "     ,B.PAYREQ_ID 																																																				\n"
 					+ "     ,F_GET_USER_NM("+acctid+") AS REQ_CHARGE_NAME    -- 작성자																																								\n"
 					+ "     ,GET_ACC_DEPT_NM(B.COOP_DEPT_CD) AS COOP_DEPT_NM -- 협조부서명 																																								\n"
 					+ "     ,B.CUR_BEF_PREPAY_AMT 																																								\n"
 					+ "     ,F_GET_D_CURRENCY( NVL( B.CUR_PREPAY_APPLYAMT, 0 ) , F_GET_CURRENCY_DIGIT( NVL( A.CURRENCY_GB, 'KRW' ))) as CUR_BEF_PREPAY_SUPPLYAMT																					\n"
 					//+ "     ,(SELECT USER_NAME   from PUB0120T A ,PUB0130V B where A.USER_SABUN = B.USER_SABUN and A.ACCT_ID = REQ_CHARGE_ID) as REQ_CHARGE_NAME																					\n"
 					+ "     ,(SELECT DEPT_NM FROM ETEC_ERPIF.ACZ10101 WHERE ACC_DEPT_CD = A.REQ_DEPT_CD) AS  SUB_DEPT_NM 																\n"
 					+ "     ,NVL(E.CHK6, 'A') FOREIGN_CHK																															\n"
 					+ "     ,DECODE(NVL(E.CHK6, 'A'), 'N', '아니오', 'Y', '예', '비대상') FOREIGN_NM																				    \n"
 					+ " FROM PUR1000T A, 																																																				\n"
//        			+ "      (SELECT B1.*																																																				\n"
//        			+ "      FROM PUR1100T B1,																																																			\n"
//        			+ "      (SELECT PUR_CONT_ID, MAX(DEPOSIT_SEQ) AS MAX_DEPOSIT_SEQ FROM PUR1100T GROUP BY PUR_CONT_ID) B2																															\n"
//        			+ "      WHERE 1=1																																																					\n"
//        			+ "      AND B1.PUR_CONT_ID = B2.PUR_CONT_ID																																														\n"
//        			+ "      AND B1.DEPOSIT_SEQ = B2.MAX_DEPOSIT_SEQ																																													\n"
//        			+ "      ) B, 																																																						\n"
        			+ "      PUR1100T B,																																								\n"
        			//+ "      (SELECT pur_cont_id FROM PUR1030T CC WHERE PREPAY_PROC_GB = '10' group by pur_cont_id) C,	--2015.12.1 선급중복 																																							\n"
        			+ "      SUPP_INFO D,																																																				\n"
        			+ "      BID_FOREIGN_CHK E																																																				\n"
        			+ " WHERE 1=1																																																						\n"
        			+ " AND A.PUR_CONT_ID = B.PUR_CONT_ID																																																\n"
        			+ " AND B.PAYREQ_ID = E.BID_DEG(+) AND E.CHK5(+) = 'P'																																																	\n"
        			//+ " AND B.PUR_CONT_ID = C.PUR_CONT_ID(+)	--2015.12.1 선급중복 수정 																																														\n"
        			+ " AND A.CORP_ID = D.CORP_ID																																																		\n"
        			;

        	//sqlstr += " AND A.PUR_CONT_ID = '"+tmpslip_purContId +"'																																													\n";
        	sqlstr += " AND B.PAYREQ_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"
    				+ "     (SELECT DETAILNM FROM EBID_COM911T WHERE BASECD = 'UNIT_KIND_GB' AND DETAILCD = B.UNIT_KIND) AS UNIT_KIND	\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	\n"); //, PUB0500T C
    		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;
	}

}
