ebid/java/kr/co/udapsoft/common/commonSign/driver/PaymentBuySignDriver.java

/**
 * @(#)file    PoDeciSignDriver.java
 * @(#)author  Lee beomgeun
 * @(#)version 1.0
 * @(#)date    Aug 1, 2005
 * @(#)since   JDK 1.4.2
 *
 * Copyright (c) www.dcchem.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of dcchem, Inc.
 */
package kr.co.udapsoft.common.commonSign.driver;

import java.io.OutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.logger.Logger;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
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.StringUtil;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.common.commonSign.CommonSignInterface;
import kr.co.udapsoft.common.commonSign.util.SACDOCreatePDF4;
import kr.co.udapsoft.ebid.buyer.bid.purchase.biz.InterfacePurERPiU;
import weblogic.jdbc.vendor.oracle.OracleThinBlob;


/**
 * 
 * @version 1.0
 * @author Beomgeun Lee
 */
public class PaymentBuySignDriver implements CommonSignInterface {

    /**
     * 테슽 파일
     * 
     */
    public PaymentBuySignDriver() {
        super();
    }

    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doCallSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doCallSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("1");
    }

    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doStartSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doStartSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("2");
    }

    /*
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doFirstFinishSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doFirstFinishSign(Connection connection, ValueObject signDoc) throws Exception {
    	
    	System.out.println("3");
    	
    	/* 운영에 반영할때는 변경해야함*/
    	RowSet signUserInfo = getIFSignUser(signDoc.get("DOC_ID"));
        
    	// 2020.02.17 결재정보가 없는 경우 예외처리
    	if(signUserInfo.size() == 0){
			throw new Exception("결재정보가 없습니다.");
		}
    	
        for ( int j = 0 ; j < signUserInfo.size() ; j ++ ) {
        	ValueObject signUser = new ValueObject();
        	signUser.set("SIGN_SEQ", 		""+(j+1));
    		signUser.set("DOC_ID", 			signDoc.get("DOC_ID"));
        	signUser.set("APPROVAL_DATE", 	signUserInfo.getRow(j).get("PROCESSDATE"));
        	signUser.set("APPROVAL_SABUN", 	signUserInfo.getRow(j).get("SABUN"));
        	signUser.set("APPROVAL_USER", 	signUserInfo.getRow(j).get("APRMEMBERNAME"));
        	signUser.set("A_POSITION", 		signUserInfo.getRow(j).get("APRMEMBERJOBTITLE"));
        	signUser.set("SIGN_RESULT", 	signUserInfo.getRow(j).get("APRSTATE"));
        
        	signUser.set("SIGN_TYPE", 		signDoc.get("SIGN_TYPE"));
        	signUser.set("ACCT_TYPE", 		signDoc.get("ACCT_TYPE"));
     
			signUser.set("SLIP_DEPT", 	signDoc.get("KEY_COL3"));
			signUser.set("ACCSLIP_NO", 	"");
				
			signUser.set("SLIP_DT", 		signDoc.get("KEY_COL4"));
			signUser.set("SLIP_NO", 		signDoc.get("KEY_COL5"));
			
        
        	insertSign002t(connection, signUser);
        }
        
    	updatePur1100t(connection, signDoc, signUserInfo);
    }
    
    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doFinishSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doFinishSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("4");
    }

    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doRejectSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doFirstRejectSign(Connection connection, ValueObject signDoc) throws Exception {
    	updatePur1100t(connection, signDoc, new RowSetImpl());
    }
    /* 
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doRejectSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doRejectSign(Connection connection, ValueObject signDoc) throws Exception {
    	updatePur1100t(connection, signDoc, new RowSetImpl());
    }

    /*
     * (non-Javadoc)
     * @see kr.co.udap.ehr.common.sign.SignInterface#doCancelSign(java.sql.Connection, com.udapsoft.common.util.ValueObject)
     */
    public void doCancelSign(Connection connection, ValueObject signDoc) throws Exception {
    	System.out.println("5");
    }
    
    private RowSet getIFSignUser(String docId) throws Exception{
    	
    	Search search = new SearchImpl();
    	search.setDSName("jdbc/gwsql");
    	RowSet rowSet = new RowSetImpl();
    	
    	StringBuffer sqlstr = new StringBuffer();       
    	
    	try {
    		sqlstr.append(" SELECT	APRSTATE                                                    			\n");
    		sqlstr.append(" 		,REPLACE(APRMEMBERID,SUBSTRING(APRMEMBERID,1,4),'') AS SABUN    		\n");
    		sqlstr.append(" 		,APRMEMBERNAME                                                  		\n");
    		sqlstr.append(" 		,APRMEMBERJOBTITLE                                              		\n");
    		sqlstr.append(" 		,APRMEMBERDEPTNAME                                              		\n");
    		sqlstr.append(" 		,REPLACE(CONVERT(VARCHAR(10),PROCESSDATE,120),'-','') AS PROCESSDATE	\n");
    		sqlstr.append(" FROM	TBENDAPRLINEINFO                                              			\n");
    		sqlstr.append(" WHERE	APRTYPE = 'A03001'                                               			\n");
    		sqlstr.append(" AND		DOCID = '"+docId+"'                        								\n");
    		sqlstr.append(" ORDER BY APRMEMBERSN                                                			\n");
    		
    		search.setStatement(sqlstr.toString());
    		rowSet = search.execute();         
    		System.out.println(sqlstr.toString());
    	}
    	catch(WAFSQLException se) {
    		Logger.err.println("SQL : \n" + se.getStatement());
    		Logger.err.println("PARAM : \n" + se.getParameter());
    		throw se;
    	}
    	finally {
    		if( rowSet == null ) rowSet = new RowSetImpl();
    	}
    	return rowSet;
    }
    
    public void insertSign002t(Connection connection, ValueObject vo) throws Exception {
		
		Persistent persistent = new PersistentImpl(connection);
		StringBuffer sqlstr = null;
		try {
			sqlstr = new StringBuffer();
			
			sqlstr.append(" INSERT INTO SIGN002T               \n");
			sqlstr.append("         (SIGN_HIS_ID               \n");
			sqlstr.append("         ,SIGN_SEQ                  \n");
			sqlstr.append("         ,DOC_ID  	               \n");
			sqlstr.append("         ,APPROVAL_DATE             \n");
			sqlstr.append("         ,APPROVAL_SABUN            \n");
			sqlstr.append("         ,APPROVAL_USER             \n");
			sqlstr.append("         ,A_POSITION                \n");
			sqlstr.append("         ,SIGN_RESULT               \n");
			sqlstr.append("         ,SLIP_DEPT                 \n");
			sqlstr.append("         ,SLIP_DT                   \n");
			sqlstr.append("         ,SLIP_NO                   \n");
			sqlstr.append("         ,SIGN_TYPE                 \n");
			sqlstr.append("         ,COMON_DCR                 \n");
			sqlstr.append("         ) VALUES                   \n");
			sqlstr.append("         (                          \n");
			sqlstr.append("         SIGN002T_S.NEXTVAL         \n");
			sqlstr.append("         ,?                         \n");
			sqlstr.append("         ,?                         \n");
			sqlstr.append("         ,TO_DATE(?,'YYYYMMDD')     \n");
			sqlstr.append("         ,?                         \n");
			sqlstr.append("         ,?                         \n");
			sqlstr.append("         ,?                         \n");
			sqlstr.append("         ,?                         \n");
			sqlstr.append("         ,?                         \n");
			sqlstr.append("         ,?                         \n");
			sqlstr.append("         ,?                         \n");
			sqlstr.append("         ,1                         \n");
			sqlstr.append("         ,2                         \n");
			sqlstr.append("         )                          \n");
			
			System.out.println(sqlstr.toString());
			System.out.println("=>"+vo.get("SIGN_SEQ"));
			System.out.println("=>"+vo.get("DOC_ID"));
			System.out.println("=>"+vo.get("APPROVAL_DATE"));
			System.out.println("=>"+vo.get("APPROVAL_SABUN"));
			System.out.println("=>"+vo.get("APPROVAL_USER"));
			System.out.println("=>"+vo.get("A_POSITION"));
			System.out.println("=>"+vo.get("SIGN_RESULT"));
			System.out.println("=>"+vo.get("SLIP_DEPT"));
			System.out.println("=>"+vo.get("SLIP_DT"));
			System.out.println("=>"+vo.get("SLIP_NO"));
			
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(vo.get("SIGN_SEQ"));
			persistent.addParameter(vo.get("DOC_ID"));
			persistent.addParameter(vo.get("APPROVAL_DATE"));
			persistent.addParameter(vo.get("APPROVAL_SABUN"));
			persistent.addParameter(vo.get("APPROVAL_USER"));
			persistent.addParameter(vo.get("A_POSITION"));
			persistent.addParameter(vo.get("SIGN_RESULT"));
			persistent.addParameter(vo.get("SLIP_DEPT"));
			persistent.addParameter(vo.get("SLIP_DT"));
			persistent.addParameter(vo.get("SLIP_NO"));
			persistent.execute();
			
		}
		catch(Exception e) {
			
			WAFLogger.error(this.getClass().getName() + " --> updateSac01001() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			throw e;
		}
	}
    
    public void updatePur1100t(Connection connection, ValueObject vo, RowSet signUserInfo) throws Exception {

        Persistent persistent1 = new PersistentImpl(connection);
        StringBuffer sqlstr = null;
        StringBuffer sqlstr1 = null;
        try {
        	sqlstr = new StringBuffer();
        	sqlstr1 = new StringBuffer();
        	String status = "";
        	
        	int complete = 0;
        	
    		if(vo.get("SIGN_DECI_CLASS").equals("14")){
    			status = "10"; //완료
    		}else {
    			status = "99"; //반려
    			complete = 1;
    		}
    		
   			sqlstr1.append(" UPDATE  PUR1100T SET        					\n");
    		sqlstr1.append("         REQ_STATUS = ?	    					\n");
    		sqlstr1.append(" WHERE   PAYREQ_ID = ?    						\n");
    		persistent1.setStatement(sqlstr1.toString());
            persistent1.addParameter(status);
            persistent1.addParameter(vo.get("KEY_COL2"));
            persistent1.execute();
            
            //전표집계처리
            if(complete == 0){
            	
            	ValueObject pur1100tData = getPur1100t(vo.get("KEY_COL2"), vo.get("APPROVAL_SABUN"));
            	pur1100tData.set("slipNo", vo.get("KEY_COL5"));
            	pur1100tData.set("DOC_ID", vo.get("DOC_ID"));
            	
            	//업체평가 상태값 처리
            	updateAppr(connection, pur1100tData);
            	
            	getPayJipge(connection, pur1100tData, signUserInfo);
            	
            	
            }
            
            
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> updatePur1100t() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
    
    /** 전표 생성 Procedure( PUR_INVOICE_CREATE) 수행
     * @throws Exception
     */
    public void callPurInvoiceCreate(Connection con, String prp_req_id, String userID, String slipNo) throws Exception {
        
        String message = "";
        String SPStateMent = "";
		CallableStatement cs = null;
        
        try {
        	SPStateMent = "{call PUR_INVOICE_CREATE2(?,?,?)}";
           
            cs = con.prepareCall(SPStateMent);
           
            cs.setString(1, prp_req_id);     // 가불금 ID
            cs.setString(2, userID);        // USER ID
            cs.setString(3, slipNo);        // USER ID
            
            cs.execute();
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() +" -->" + SPStateMent+ " : \n" + e.getMessage());
            WAFLogger.error(e);
            
            message = this.getClass().getName() + "." + SPStateMent + " : \n" + e.getMessage();
            message = StringUtil.replace(message, "\"", "");
            //storage.setDetailMessage(message);
            
            throw e;
            
        } finally{
			try { 
				if (cs != null) cs.close(); 
			} catch (Exception ex_close) {
				WAFLogger.error(ex_close);
			} 
        }
    }

    public void createSMbill(Connection con, String prp_req_id, String userID, String payreqId, String ordDesc, String batchId) throws Exception {
        
        String message = "";
        String SPStateMent = "";
		CallableStatement cs = null;
        
        try {
        	SPStateMent = "{call PUR_BILL_CREATE2(?,?,?,?,?)}";
           
            cs = con.prepareCall(SPStateMent);
           
            cs.setString(1, prp_req_id);     // 가불금 ID
            cs.setString(2, userID);         // USER ID
            cs.setString(3, payreqId);      
            cs.setString(4, ordDesc.substring(10));    	  
            cs.setString(5, batchId);
            
            cs.execute();
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() +" -->" + SPStateMent+ " : \n" + e.getMessage());
            WAFLogger.error(e);
            
            message = this.getClass().getName() + "." + SPStateMent + " : \n" + e.getMessage();
            message = StringUtil.replace(message, "\"", "");
            //storage.setDetailMessage(message);
            
            throw e;
            
        } finally{
			try { 
				if (cs != null) cs.close(); 
			} catch (Exception ex_close) {
				WAFLogger.error(ex_close);
			} 
        }
    }
    
    //집계처리
    public boolean getPayJipge(Connection connection, ValueObject vo, RowSet signUserInfo) throws Exception {

    	Persistent persistent_i = null;
    	Persistent persistent_i_p = null;
    	Persistent persistent_u = null;
		
		String sqlstr_i = "";
		String sqlstr_i_p = "";
		String sqlstr_u = "";
	
		try{
			
			persistent_i = new PersistentImpl(connection);
			persistent_i_p = new PersistentImpl(connection);
			persistent_u = new PersistentImpl(connection);
			
			int jip_count = getPayJipgeCount(connection, vo).getInt("jip_count");
					
			sqlstr_i = 		"	INSERT INTO PUR1040T ( 								\n" +
							"		TMPSLIP_ID		-- 자재대금ID					\n" +
							"		,DEPT_CD			-- 현장코드					\n" +
							"		,PREPAY_GB		-- 선급입고구분					\n" +
							"		,ENTER_DT		-- 거래일자						\n" +
							"		,PROOF_CD		-- 증빙코드						\n" +
							"		,ORD_DESC		-- 적요							\n" +
							"		,MAT_AMT			-- 구매금액					\n" +
							"		,MAT_SUPPLY_AMT	-- 공급가액						\n" +
							"		,MAT_SURTAX_AMT	-- 부가세						\n" +
							"		,PREPAY_SUB_AMT	-- 선급금공제액					\n" +
							"		,DEALR_CD		-- 거래처코드					\n" +
							"		,DCERTI_NO		-- 사업자번호					\n" +
							"		,TAX_DATE		-- 세금계산서일자				\n" +
							"		,ELECTAX_YN		-- 전자세금계산서여부				\n" +
							"		,PAYITEM			-- PAY ITEM					\n" +
							"		,COSTCODE		-- COST CODE					\n" +
							"		,COSTTYPE		-- COST TYPE					\n" +
							"		,PAY_GROUP		-- PAY_GROUP					\n" +
							"		,PAYMENT_TERMS	-- PAYMENT_TERMS				\n" +
							"		,TMPNATFLG		-- 계정구분						\n" +
							"		,TMPSLIP_YN		-- 전표생성유무					\n" +
							"		,SLIP_YN			-- 결의서작성유무			\n" +
							"		,CURRENCY_GB		-- 통화구분					\n" +
							"		,PUR_CONT_ID		-- 발주계약ID				\n" +
							"		,PAYREQ_ID		-- 지급신청ID					\n" +
							"		,CRE_BY			-- 등록자						\n" +
							"		,CRE_DATE		-- 등록일시						\n" +
							"		)												\n" +
							"		VALUES		(									\n" +
							"		?								\n" +
							"		,?					-- 현장코드					\n" +
							"		,?					-- 선급입고구분				\n" +
							"		,TO_DATE(?, 'YYYYMMDD')	-- 거래일자				\n" +
							"		,?						-- 증빙코드				\n" +
							"		,?						-- 적요					\n" +
							"		,?						-- 구매금액				\n" +
							"		,?						-- 공급가액				\n" +
							"		,?						-- 부가세				\n" +
							"		,?						-- 선급금공제액			\n" +
							"		,?						-- 거래처코드			\n" +
							"		,?						-- 사업자번호			\n" +
							"		,TO_DATE(?, 'YYYYMMDD')	-- 세금계산서일자			\n" +
							"		,'Y'						-- 전자세금계산서여부		\n" +
							"		,?						-- PAY ITEM				\n" +
							"		,?						-- COST CODE			\n" +
							"		,?						-- COST TYPE			\n" +
							"		,?						-- PAY_GROUP			\n" +
							"		,?						-- PAYMENT_TERMS		\n" +
							"		,?						-- 계정구분				\n" +
							"		,?						-- 전표생성유무			\n" +
							"		,?						-- 결의서작성유무		\n" +
							"		,?						-- 통화구분				\n" +
							"		,?						-- 발주계약ID			\n" +
							"		,?						-- 지급신청ID			\n" +
							"		,?						-- 등록자				\n" +
							"		,SYSDATE	)									\n" ;
			
			
			
			sqlstr_i_p = 	"	INSERT INTO PUR1040T ( 								\n" +
							"		TMPSLIP_ID		-- 자재대금ID					\n" +
							"		,DEPT_CD			-- 현장코드					\n" +
							"		,PREPAY_GB		-- 선급입고구분					\n" +
							"		,ENTER_DT		-- 거래일자						\n" +
							"		,PROOF_CD		-- 증빙코드						\n" +
							"		,ORD_DESC		-- 적요							\n" +
							"		,MAT_AMT			-- 구매금액					\n" +
							"		,MAT_SUPPLY_AMT	-- 공급가액						\n" +
							"		,MAT_SURTAX_AMT	-- 부가세						\n" +
							"		,PREPAY_SUB_AMT	-- 선급금공제액					\n" +
							"		,DEALR_CD		-- 거래처코드					\n" +
							"		,DCERTI_NO		-- 사업자번호					\n" +
							"		,TAX_DATE		-- 세금계산서일자				\n" +
							"		,ELECTAX_YN		-- 전자세금계산서여부				\n" +
							"		,PAYITEM			-- PAY ITEM					\n" +
							"		,COSTCODE		-- COST CODE					\n" +
							"		,COSTTYPE		-- COST TYPE					\n" +
							"		,PAY_GROUP		-- PAY_GROUP					\n" +
							"		,PAYMENT_TERMS	-- PAYMENT_TERMS				\n" +
							"		,TMPNATFLG		-- 계정구분						\n" +
							"		,TMPSLIP_YN		-- 전표생성유무					\n" +
							"		,SLIP_YN			-- 결의서작성유무			\n" +
							"		,CURRENCY_GB		-- 통화구분					\n" +
							"		,PUR_CONT_ID		-- 발주계약ID				\n" +
							"		,PAYREQ_ID		-- 지급신청ID					\n" +
							"		,CRE_BY			-- 등록자						\n" +
							"		,CRE_DATE		-- 등록일시						\n" +
							"		)												\n" +
							"		VALUES		(									\n" +
							"		?								\n" +
							"		,?					-- 현장코드					\n" +
							"		,?					-- 선급입고구분				\n" +
							"		,TO_DATE(?, 'YYYYMMDD')	-- 거래일자				\n" +
							"		,?						-- 증빙코드				\n" +
							"		,?						-- 적요					\n" +
							"		,?						-- 구매금액				\n" +
							"		,?						-- 공급가액				\n" +
							"		,?						-- 부가세				\n" +  //2015.9.11 불공제일때 선급공제 부가세 입력. 
							"		,?						-- 선급금공제액			\n" +
							"		,?						-- 거래처코드			\n" +
							"		,?						-- 사업자번호			\n" +
							"		,TO_DATE(?, 'YYYYMMDD')	-- 세금계산서일자			\n" +
							"		,'Y'						-- 전자세금계산서여부		\n" +
							"		,?						-- PAY ITEM				\n" +
							"		,?						-- COST CODE			\n" +
							"		,?						-- COST TYPE			\n" +
							"		,?						-- PAY_GROUP			\n" +
							"		,?						-- PAYMENT_TERMS		\n" +
							"		,?						-- 계정구분				\n" +
							"		,?						-- 전표생성유무			\n" +
							"		,?						-- 결의서작성유무		\n" +
							"		,?						-- 통화구분				\n" +
							"		,?						-- 발주계약ID			\n" +
							"		,?						-- 지급신청ID			\n" +
							"		,?						-- 등록자				\n" +
							"		,SYSDATE	)									\n" ;
			
			
			
			sqlstr_u = 		" 	UPDATE PUR1100T SET TRANS_CD = 'Y' WHERE PAYREQ_ID = ?		";
			
			
			RowSet rowSet = getPayReqList(connection, vo);
			RowSet preRowSet = getPayPreList(connection, vo);
			
			persistent_i.setStatement(sqlstr_i);
			persistent_i_p.setStatement(sqlstr_i_p);
			persistent_u.setStatement(sqlstr_u);
			
			String pay_group = "";
			String payment_term = "";
			String pay_item = "";
			String cost_code = "";
			String cost_type = "";
        	String billDiv = "";
        	String PAYREQ_ID = "";  //2018.08.02
        	String PAYREQ_YN = "N";
        	
			if(jip_count > 0){
				
				//선급공제액 전표집계
				if(preRowSet.size() > 0){
					
					for(int j = 0; j < preRowSet.size(); j++){
						
						//자재대금ID
						String tmpslipId_i_p = getTmpslipId();
						persistent_i_p.addParameter(tmpslipId_i_p);
						persistent_i_p.addParameter(preRowSet.getRow(j).get("DEPT_CD")); 
						persistent_i_p.addParameter(preRowSet.getRow(j).get("PREPAY_GB")); 
						persistent_i_p.addParameter(preRowSet.getRow(j).get("REQ_DATE")); //거래일자
						persistent_i_p.addParameter(preRowSet.getRow(j).get("GUA_GB")); //증빙
						persistent_i_p.addParameter(preRowSet.getRow(j).get("ORD_DESC")); //적요
						persistent_i_p.addParameter(0); //구매금액
						persistent_i_p.addParameter(0); //공급가액
						persistent_i_p.addParameter(0); //부가세   2015.9.11 불공제일때 선급 부가세 입력
						persistent_i_p.addParameter(preRowSet.getRow(j).get("CUR_PREPAY_DEDUCTAMT")); //선급공제액
						persistent_i_p.addParameter(preRowSet.getRow(j).get("CORP_ID"));
						persistent_i_p.addParameter(preRowSet.getRow(j).get("CORP_REG_NO")); //사업자번호
						persistent_i_p.addParameter(preRowSet.getRow(j).get("REQ_DATE")); //세금계산서일자
						persistent_i_p.addParameter(preRowSet.getRow(j).get("PAY_ITEM")); 
						persistent_i_p.addParameter(preRowSet.getRow(j).get("COST_CODE")); 
						persistent_i_p.addParameter(preRowSet.getRow(j).get("COST_TYPE")); 
						persistent_i_p.addParameter(preRowSet.getRow(j).get("PAY_GROUP"));
						persistent_i_p.addParameter(preRowSet.getRow(j).get("PAYMENT_TERMS"));
						persistent_i_p.addParameter(preRowSet.getRow(j).get("ACCT_GB")); //계정구분
						persistent_i_p.addParameter("N"); //전표생성유무
						persistent_i_p.addParameter("N"); //결의서작성유무
						persistent_i_p.addParameter(preRowSet.getRow(j).get("CURRENCY_GB")); 
						persistent_i_p.addParameter(preRowSet.getRow(j).get("PUR_CONT_ID"));
						persistent_i_p.addParameter(preRowSet.getRow(j).get("PAYREQ_ID")); 
						persistent_i_p.addParameter(vo.getInt("USERID"));
						persistent_i_p.execute();
						persistent_i_p.clearParameters();

						//2018.8.2 청구금액없이 선급반제만 처리하는 경우 오류발생
						PAYREQ_ID = preRowSet.getRow(j).get("PAYREQ_ID");						
						
						//증빙생성
						prcDoc020t(connection, tmpslipId_i_p, vo.getInt("USERID"), tmpslipId_i_p, preRowSet.getRow(j).get("CUR_PREPAY_DEDUCTAMT"));

					}
				}

				if(rowSet.size() > 0){
				
					for(int i = 0; rowSet.size() > i; i++){
						
						//자재대금ID
						String tmpslipId_i = getTmpslipId();	
						pay_group = rowSet.getRow(i).get("PAY_GROUP");
						payment_term = rowSet.getRow(i).get("PAYMENT_TERMS");
						pay_item = rowSet.getRow(i).get("PAY_ITEM");
						cost_code = rowSet.getRow(i).get("COST_CODE");
						cost_type = rowSet.getRow(i).get("COST_TYPE");
						persistent_i.addParameter(tmpslipId_i);
						persistent_i.addParameter(rowSet.getRow(i).get("DEPT_CD")); 
						persistent_i.addParameter(rowSet.getRow(i).get("PREPAY_GB")); 
						persistent_i.addParameter(rowSet.getRow(i).get("REQ_DATE")); //거래일자
						persistent_i.addParameter(rowSet.getRow(i).get("GUA_GB")); //증빙
						persistent_i.addParameter(rowSet.getRow(i).get("ORD_DESC")); //적요
						persistent_i.addParameter(rowSet.getRow(i).get("CUR_REQ_APPLYAMT")); //구매금액
						persistent_i.addParameter(rowSet.getRow(i).get("CUR_REQ_AMT")); //공급가액
						persistent_i.addParameter(rowSet.getRow(i).get("CUR_REQ_VATAMT")); //부가세
						persistent_i.addParameter(0); //선급공제액
						persistent_i.addParameter(rowSet.getRow(i).get("CORP_ID"));
						persistent_i.addParameter(rowSet.getRow(i).get("CORP_REG_NO")); //사업자번호
						persistent_i.addParameter(rowSet.getRow(i).get("REQ_DATE")); //세금계산서일자
						persistent_i.addParameter(pay_item); 
						persistent_i.addParameter(cost_code); 
						persistent_i.addParameter(cost_type); 
						persistent_i.addParameter(pay_group);
						persistent_i.addParameter(payment_term);
						persistent_i.addParameter(rowSet.getRow(i).get("ACCT_GB")); //계정구분
						persistent_i.addParameter("N"); //전표생성유무
						persistent_i.addParameter("N"); //결의서작성유무
						persistent_i.addParameter(rowSet.getRow(i).get("CURRENCY_GB")); 
						persistent_i.addParameter(rowSet.getRow(i).get("PUR_CONT_ID"));
						persistent_i.addParameter(rowSet.getRow(i).get("PAYREQ_ID")); 
						persistent_i.addParameter(vo.getInt("USERID")); 
						persistent_i.execute();
						persistent_i.clearParameters();
						
						//2018.8.2 청구금액없이 선급반제만 처리하는 경우 오류발생
						PAYREQ_YN = "Y";
						persistent_u.addParameter(rowSet.getRow(i).get("PAYREQ_ID"));//2018.08.02
						persistent_u.execute();
						persistent_u.clearParameters();
						
						//증빙생성
						prcDoc020t(connection, tmpslipId_i, vo.getInt("USERID"), tmpslipId_i, "0");
					}
					
				}
				
				//2018.8.2 청구금액없이 선급반제만 처리하는 경우 오류발생
				if(PAYREQ_YN.equals("N")){ 
					persistent_u.addParameter(PAYREQ_ID);//2018.08.02
					persistent_u.execute();
					persistent_u.clearParameters();
				}
				
				
				try {
					
					if(vo.get("PAY_DIV").equals("1")) {
						InterfacePurERPiU.updateErpIuItem(vo, signUserInfo);	
					}else {
						InterfacePurERPiU.updateErpIuInstall(vo, signUserInfo);
					}
					
				}catch(Exception e) {
					connection.rollback();
	        		throw new Exception("ERPIU insert Error : "+e.getMessage());
				}
	        	
			}else{
				return false;
			}
			
			return true;
			
		} catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updatePur1100t() : " + e.getMessage());
            WAFLogger.error(e);
            throw e;
        }
		
    }

    public void prcDoc020t(Connection con, String tmpslipID, int userID, String tempslip_id, String prepay_sub_amt) throws Exception {

        String strResult = null;
		//String strFileName = null; 
	
        //String message = "";
		String SPStateMent = "";
        CallableStatement cs = null;
        String arrDocNm [] = {"자재 계산서"};
        String arrProofGu [] = {"30"};
        
        //WAFLogger.debug("\n\t #################### params = [\n"+ params +"\n");
        
        try {
        	
        	// 증빙정보 생성 프로시저 
        	// - 자재 계산서만 새로 생성해야만 한다.
			SPStateMent = "{call SAC_DOCUMENTATIVE.prc_purinvoicebiz3(?, ?, ?, ?, ?)}";
			
            cs = con.prepareCall(SPStateMent);

            // 라인당, 기성고계산서 한라인만 생성.
            for(int iCount=0; iCount<arrDocNm.length; iCount++) {
            	
	            int i = 1;
	            cs.setString(i++, tmpslipID);
	            cs.setString(i++, arrProofGu[iCount]);
	            cs.setInt(i++, userID);
	            cs.setString(i++, prepay_sub_amt);
	            cs.registerOutParameter(i++, Types.VARCHAR);
	            
	          
	            cs.execute();
	            strResult = cs.getString(5);

	            // 입력된 증빙데이터 라인을 기준으로 실제 증빙을 
				String arrKeys[] = strResult.split("@"); // proof_dept, proofdate, proof_no, proof_seq
				OutputStream outputStream = getOS(con, arrKeys[0], arrKeys[1], arrKeys[2], Integer.parseInt(arrKeys[3]));

		    	// 기성고 계산서 PDF 생성 
				SACDOCreatePDF4 sacDOCreatePDF4 = new SACDOCreatePDF4(); 
				sacDOCreatePDF4.doBiz1(con, outputStream, tmpslipID, null);
            }
            // 라인당, 기성고계산서 한라인만 생성. - 종료 
        }
        catch(Exception e) {
			WAFLogger.error(e);
			throw e;
        }   
        finally{
			try { 
				if (cs != null) cs.close(); 
			} 
			catch (Exception ex_close) {
				WAFLogger.error(ex_close);
			} 
        }
	}
    
    /* 증빙 스트림 Select !~ */
    
	public static OutputStream getOS(Connection con, String proof_dept, String proof_dt, String proof_no, 
			int proof_seq) throws Exception {
		OutputStream outputStream = null;
       PreparedStatement pstmt = null;
       ResultSet resultSet = null;
       
       Statement stmt = null;
       
		try {

           String strSQL = "SELECT photo "
				+ " FROM DOC021T "
				+ " WHERE proof_dept = '"+ proof_dept +"'"
				+ "   AND TO_CHAR (proofdate, 'yyyymmdd') = '"+ proof_dt +"' "
				+ "   AND proof_no = " + proof_no
				+ "   AND proof_seq = "+ proof_seq
				+ "   AND proc_gu = '30' " 
				+ " FOR UPDATE";
			
			pstmt = con.prepareStatement(strSQL);
			resultSet = pstmt.executeQuery();

			if (resultSet.next()) {
				OracleThinBlob blob = (OracleThinBlob)resultSet.getBlob(1);
				
				outputStream = blob.getBinaryOutputStream();
			} else {
				throw new Exception("증빙화일을 저장할 레코드가 존재하지 않습니다.");
			}

		} catch (Exception e) {
			WAFLogger.error(e);

		} finally {
			if( stmt   != null ) stmt.close();
			
			resultSet.close();
			pstmt.close();
			return outputStream;
		} 
	}
	
	/**
	 * 집계 가능한 내용 갯수
	 * @param 	
	 * @return	ValueObject
	 */
	public ValueObject getPayJipgeCount(Connection connection, ValueObject vo) throws Exception {
	
		Persistent persistent_c = null;
		
		
		//ValueObject resultVo = new ValueObject();
		ValueObject row = null;
		String sqlstr = "";
		
		try{
			
			persistent_c = new PersistentImpl(connection);
			
			sqlstr = 	"	SELECT COUNT(*) JIP_COUNT												\n" +
						"	FROM PUR1100T A ,PUR1000T B								\n" +
						"	WHERE A.PUR_CONT_ID = B.PUR_CONT_ID 									\n" +
						"	AND	B.DEPT_CD = '"+vo.get("dept_cd")+"'									\n"	+
						"	AND A.REQ_STATUS = '10' 												\n" +
						"	AND TO_CHAR(A.REQ_DATE, 'YYYY-MM') = '"+vo.get("p_enter_dt")+"'			\n" +
						"	AND A.TRANS_CD = 'N'													\n";
			
			persistent_c.setStatement(sqlstr); 
			
			System.out.println(sqlstr);
			
			
			row = RowSetUtility.getValueObject(persistent_c.query().getRow(0));
			
			vo.set("jip_count", row.get("JIP_COUNT"));
			
			
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getSeq() : " + e.getMessage());
//			System.out.println(e.getMessage());
			WAFLogger.error(e);
			throw e;
            
		}finally{
			if(vo == null) vo = new ValueObject();
		}
		
		return vo;
	}

    private RowSet getPayReqList(Connection connection, ValueObject params) throws Exception {
        RowSet rowSet = new RowSetImpl();
        String sqlstr = "";
        Persistent persistent_c = null;
		
        try{
        	
        	persistent_c = new PersistentImpl(connection);
        	
	        sqlstr =	"	SELECT  																															\n" +
						"		B.DEPT_CD	DEPT_CD	--현장코드							\n" +
						"		, '2'  AS PREPAY_GB	--선급입고구분																		\n" +
						"		,TO_CHAR(B.ORD_CON_DATE, 'YYYYMMDD')		ORD_CON_DATE	--거래일자															\n" +
						"		,A.GUA_GB		GUA_GB	--증빙코드																								\n" +
						"		,CASE WHEN A.DEPOSIT_CD = '1' AND (SELECT AC_UNIT_CD																			\n" +
						"										   FROM ETEC_ERPIF.ACZ10100	 																			\n" +
						"										   WHERE DEPT_CD = B.DEPT_CD ) = '10'															\n" +
                        "			  THEN '21'																													\n" +
                        "			  WHEN A.DEPOSIT_CD = '1' AND (SELECT AC_UNIT_CD																			\n" +
                        "                                          FROM ETEC_ERPIF.ACZ10100																				\n" + 
                        "                                          WHERE DEPT_CD = B.DEPT_CD ) <> '10'															\n" +
                        "			  THEN '22'																													\n" +
                        "		ELSE A.PAY_GROUP																												\n" +
                        "		END PAY_GROUP	--선급금PAY_GROUP																								\n" +
                        "		,A.PAYMENT_TERMS	PAYMENT_TERMS	--PAYMENT_TERMS																				\n" +
						"		,CASE WHEN A.DEPOSIT_CD = 1 THEN                         																		\n" +
                        "       		  F_GET_REMARK_DATE(TO_DATE(A.TAX_DY , 'YYYYMMDD')) || ' 선급금(' || TO_NUMBER(A.DEPOSIT_SEQ)     || '차) ' || B.CST_DOC_NAME					\n" +
                        "       		  WHEN A.DEPOSIT_CD = 2 THEN																							\n" +
                        "       		  F_GET_REMARK_DATE(TO_DATE(A.TAX_DY , 'YYYYMMDD')) || ' 중도금(' || TO_NUMBER(A.DEPOSIT_SEQ )    || '차) ' || B.CST_DOC_NAME 				\n" +
                        "       		  ELSE  F_GET_REMARK_DATE(TO_DATE(A.TAX_DY , 'YYYYMMDD')) || ' 잔금(' || TO_NUMBER(A.DEPOSIT_SEQ )     || '차) ' || B.CST_DOC_NAME END   	\n" +  
                        "          AS ORD_DESC																													\n" +
						"		,B.ORD_AMT	ORD_AMT	--구매금액																									\n" +
						"		,B.ORD_SUPPLY_AMT	ORD_SUPPLY_AMT	--공급가액																					\n" +
						"		,A.CUR_REQ_VATAMT	CUR_REQ_VATAMT		--부가세																					\n" +
						"		,F_GET_DEALR_CD_EBID(B.CORP_ID)	CORP_ID	--거래처코드																									\n" +
						"		,F_GET_DEALR_NO_EBID(B.CORP_ID) CORP_REG_NO	--사업자번호																							\n" +
						"		,B.PAY_ITEM  AS PAY_ITEM																										\n" +
						"		,B.COST_TYPE AS COST_TYPE																										\n" +
						"		,B.COST_CODE AS COST_CODE																										\n" +
						"		,B.CURRENCY_GB	CURRENCY_GB	--통화구분																							\n" +
						"		,A.PUR_CONT_ID	PUR_CONT_ID	--발주계약ID																							\n" +
						"		,A.PAYREQ_ID	PAYREQ_ID	--지급신청ID																							\n" +
						"		,A.TAX_DY as  REQ_DATE                    -- 작성일자 																			\n" +
						"		,B.CON_GB					-- 계약구분																							\n" +
						"		,B.ELEC_CON_YN					-- 전자계약여부																					\n" +
						"		,A.CUR_PREPAY_APPLYAMT AS CUR_PREPAY_DEDUCTAMT			-- 금회선급공제 공급가액													\n" +
						"		,A.CUR_REQ_AMT + A.CUR_REQ_VATAMT  CUR_REQ_APPLYAMT			-- 금회청구공급가액													\n" +
				        "		,A.CUR_PAYING_AMT			-- 금회지급예정액																						\n" +
						"		,A.CUR_REQ_AMT			-- 금회청구액																								\n" +
						"		,A.ACCT_GB				--분계유형																								\n" +
						"		,A.TAX_METHOD_CD																												\n" +
						"		FROM	PUR1100T A 																										\n" +
						"		LEFT OUTER JOIN PUR1000T B ON A.PUR_CONT_ID = B.PUR_CONT_ID																\n" +
						"		LEFT OUTER JOIN SUPP_INFO C ON B.CORP_ID = C.CORP_ID 																			\n" +
						"		WHERE 	B.DEPT_CD 	= 	'"+params.get("dept_cd")+"'																				\n" +
						"		AND		A.TRANS_CD	= 	'N'																										\n" +
						"		AND		A.REQ_STATUS	= '10'																									\n" +
						"		AND		C.JOB_DIV = 'P'																											\n" +
						"		AND		TO_CHAR(TO_DATE(A.TAX_DY, 'YYYY-MM-DD'), 'YYYY-MM')	= '"+params.get("p_enter_dt")+"'									\n" +
	                    "		AND		A.CUR_REQ_AMT > 0	 	\n" ; //2017.04.28추가. 금회청구액 >0경우만 전표발생. 0원전표 금지.
			             
	       
			System.out.println(sqlstr);
			persistent_c.setStatement(sqlstr);
			rowSet = persistent_c.query();
			
        }catch (WAFSQLException se) {
            WAFLogger.error(this.getClass().getName() + " --> updatePur1100t() : " + se.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(se);
            throw se;
        }
        finally {    
        	 if(rowSet == null){
             	rowSet = new RowSetImpl();
             }
        }
        
        return rowSet;
			
    }
    
    private RowSet getPayPreList(Connection connection, ValueObject params) throws Exception{
		
        RowSet rowSet = new RowSetImpl();
        String sqlstr = "";
        
        Persistent persistent_c = null;
        //ValueObject user = storage.getUser();
        
        try{
        	
        	persistent_c = new PersistentImpl(connection);
        	
	        sqlstr =	"	SELECT  																																\n" +
						"		B.DEPT_CD	DEPT_CD	--현장코드								\n" +
						"		,'2'  AS PREPAY_GB	--선급입고구분																			\n" +
						"		,TO_CHAR(B.ORD_CON_DATE, 'YYYYMMDD')		ORD_CON_DATE	--거래일자																\n" +
						"		,A.GUA_GB		GUA_GB	--증빙코드																									\n" +
						"		,A.PAY_GROUP	PAY_GROUP	--PAY_GROUP																								\n" +
						"		,A.PAYMENT_TERMS	PAYMENT_TERMS	--PAYMENT_TERMS																					\n" +
						"		,CASE WHEN A.DEPOSIT_CD = '1' THEN                         																			\n" +
                        "       		  F_GET_REMARK_DATE(TO_DATE(A.TAX_DY , 'YYYYMMDD')) || ' 선급금(' || TO_NUMBER(A.DEPOSIT_SEQ)     || '차) ' || B.CST_DOC_NAME						\n" +
                        "       		  WHEN A.DEPOSIT_CD = '2' THEN																								\n" +
                        "       		  F_GET_REMARK_DATE(TO_DATE(A.TAX_DY , 'YYYYMMDD')) || ' 중도금선급공제(' || TO_NUMBER(A.DEPOSIT_SEQ )    || '차) ' || B.CST_DOC_NAME 					\n" +
                        "       		  ELSE  F_GET_REMARK_DATE(TO_DATE(A.TAX_DY , 'YYYYMMDD')) || ' 잔금(' || TO_NUMBER(A.DEPOSIT_SEQ )     || '차) ' || B.CST_DOC_NAME END   		\n" +
						"				AS ORD_DESC	--적요																											\n" +
						"		,B.ORD_AMT	ORD_AMT	--구매금액																										\n" +
						"		,B.ORD_SUPPLY_AMT	ORD_SUPPLY_AMT	--공급가액																						\n" +
						"		,A.CUR_REQ_VATAMT	CUR_REQ_VATAMT		--부가세																						\n" +
						"		,F_GET_DEALR_CD_EBID(B.CORP_ID)	CORP_ID	--거래처코드																									\n" +
						"		,F_GET_DEALR_NO_EBID(B.CORP_ID) CORP_REG_NO	--사업자번호																							\n" +
						"		,B.PAY_ITEM  AS PAY_ITEM																											\n" +
						"		,B.COST_TYPE AS COST_TYPE																											\n" +
						"		,B.COST_CODE AS COST_CODE																											\n" +
						"		,B.CURRENCY_GB	CURRENCY_GB	--통화구분																								\n" +
						"		,A.PUR_CONT_ID	PUR_CONT_ID	--발주계약ID																								\n" +
						"		,A.PAYREQ_ID	PAYREQ_ID	--지급신청ID																								\n" +
						"		,A.TAX_DY as  REQ_DATE                    -- 작성일자 																				\n" +
						"		,B.CON_GB					-- 계약구분																								\n" +
						"		,B.ELEC_CON_YN					-- 전자계약여부																						\n" +
						"		,A.CUR_PREPAY_APPLYAMT AS CUR_PREPAY_DEDUCTAMT			-- 금회선급공제 공급가액																					\n" +
						"		,A.CUR_PREPAY_VAT AS CUR_PREPAY_VAT			-- 금회선급공제 vat 2015.9.11 선급공제 부가세 추가																					\n" +
						"		,A.CUR_REQ_APPLYAMT			-- 금회청구공급가액																						\n" +
						"		,A.CUR_PAYING_AMT			-- 금회지급예정액																							\n" +
						"		,A.CUR_REQ_AMT			-- 금회청구액																									\n" +
						"		,A.ACCT_GB				--분계유형																									\n" +
						"		FROM	PUR1100T A 																											\n" +
						"		LEFT OUTER JOIN PUR1000T B ON A.PUR_CONT_ID = B.PUR_CONT_ID																	\n" +
						"		LEFT OUTER JOIN SUPP_INFO C ON B.CORP_ID = C.CORP_ID 																				\n" +
						"		WHERE 	B.DEPT_CD 	= 	'"+params.get("dept_cd")+"'																					\n" +
						"		AND		A.TRANS_CD	= 	'N'																											\n" +
						"		AND		A.REQ_STATUS	= '10'																										\n" +
						"		AND		C.JOB_DIV = 'P'																											\n" +
						"		AND		TO_CHAR(TO_DATE(A.TAX_DY, 'YYYY-MM-DD'), 'YYYY-MM')	= '"+params.get("p_enter_dt")+"'															\n"+
	        			"		AND		A.CUR_PREPAY_DEDUCTAMT	> 0																									\n";
	        
			System.out.println(sqlstr);
			persistent_c.setStatement(sqlstr); 
			rowSet = persistent_c.query();
		
        }catch (WAFSQLException se) {
            WAFLogger.error(this.getClass().getName() + " --> updatePur1100t() : " + se.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(se);
            throw se;
        }
        finally {    
        	 if(rowSet == null){
             	rowSet = new RowSetImpl();
             }
        }
        
        return rowSet;
		
	}

	public ValueObject getPur1100t(String payreqId, String appSabun) throws Exception {
		
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        String sqlstr = "";
        ValueObject row = null;
        
        try{

	        sqlstr = "	    SELECT                                                              \n "						
	                +"	        A.DEPT_CD,    --현장코드                                       					\n "						
	                +"	        B.PUR_CONT_ID,                                                  \n "						
	                +"	        B.PAYREQ_ID,                                                    \n "						
	                +"	        B.PAY_DIV,                                                    \n "						
	                +"	        TO_CHAR(TO_DATE(B.TAX_DY,'yyyymmdd'), 'YYYY-MM') as P_ENTER_DT,            \n "						
	                +"	        (SELECT MAX(ACCT_ID) FROM PUB0120T A                            \n "						
	                +"	        WHERE USER_SABUN = '"+appSabun+"') AS USERID,       			\n "						
	                +"	        D.ARR_ID,                                                       \n "						
	                +"	        A.CORP_ID                                                       \n "
	                +"	        ,TO_CHAR(SYSDATE,'YYYYMMDD') AS DT_CHECK_APP												\n "
	                +"	        ,(SELECT MAX(USER_NAME) FROM PUB0120T WHERE USER_SABUN = '"+appSabun+"') AS NM_CHECK_APP	\n "
	                +"	        ,'"+appSabun+"' AS ID_CHECK_APP																\n "
	                 //2019.04.03 신용길부장님 수정요청
	                +"	        ,ROUND(B.CUR_ACMP_APPLYAMT  * NVL(B.CUR_ACMP_EXCRATE,1),0) AS SAMT_BUILT								\n "
					+"	        ,ROUND(B.CUR_ACMP_VAT * NVL(B.CUR_ACMP_EXCRATE,1),0) AS VAMT_BUILT								\n "
					+"	        ,ROUND((B.CUR_ACMP_APPLYAMT + B.CUR_ACMP_VAT )  * NVL(B.CUR_ACMP_EXCRATE,1),0)  AS AMT_BUILT								\n "
					+"	        ,ROUND(B.CUR_PREPAY_APPLYAMT * NVL(B.CUR_ACMP_EXCRATE,1),0) AS SAMT_DEDUCT														\n "
					+"	        ,ROUND(B.CUR_PREPAY_VAT * NVL(B.CUR_ACMP_EXCRATE,1),0) AS VAMT_DEDUCT															\n "
					+"	        ,ROUND((B.CUR_PREPAY_APPLYAMT + B.CUR_PREPAY_VAT) * NVL(B.CUR_ACMP_EXCRATE,1),0) AS AMT_DEDUCT										\n "
					+"	        ,B.CUR_ACMP_APPLYAMT AS SAMT_BUILT_CURR														\n "
					+"	        ,B.CUR_ACMP_VAT AS VAMT_BUILT_CURR														\n "
					+"	        ,(B.CUR_ACMP_APPLYAMT + B.CUR_ACMP_VAT ) AS AMT_BUILT_CURR														\n "
					+"	        ,B.CUR_PREPAY_APPLYAMT AS SAMT_DEDUCT_CURR													\n "
					+"	        ,B.CUR_PREPAY_VAT AS VAMT_DEDUCT_CURR														\n "
					+"	        ,B.CUR_PREPAY_APPLYAMT + B.CUR_PREPAY_VAT AS AMT_DEDUCT_CURR								\n "					
					+"	        ,DECODE(B.TAX_METHOD_CD,'1','0','2') AS YN_ISS														\n "
					+"	        ,B.TAX_DY AS DT_BUILT														\n "
					+"	        ,B.TAX_DY AS DT_STRG_IN														\n "
					+"	        ,NVL(B.CUR_ACMP_EXCRATE,1) AS UNT_CURR														\n "
					//2019.07.29 ERP 자동분개를 위한 계정코드
					+"          ,CASE WHEN A.CURRENCY_GB = 'KRW' THEN  \n"
					+"                     CASE WHEN NVL(B.SUPP_DIRECT_YN,'') = 'Y' THEN NVL(E.CHG_CODE16, '')  \n"
					+"                               ELSE NVL(E.CHG_CODE14, '') \n"
					+"                     END                     \n"
					+"                ELSE CASE WHEN NVL(B.SUPP_DIRECT_YN,'') = 'Y' THEN NVL(E.CHG_CODE17, '')  \n"
					+"                                ELSE NVL(E.CHG_CODE15, '') \n"
					+"                     END  \n"
					+"           END AS CD_COST_TRADE    \n"					
	                +"	    FROM PUR1000T A, PUR1100T B, SUPP_APPR D, (SELECT DETAILCD, CHG_CODE14, CHG_CODE15, CHG_CODE16, CHG_CODE17 FROM EBID_COM911T WHERE BASECD = 'PUR-ACCT_GB' ) E	\n "	                
	                +"      WHERE A.PUR_CONT_ID = B.PUR_CONT_ID                                 \n "
	                +"      AND A.ORD_DOC_NO = D.ORD_DOC_NO(+)                                  \n "
	                +"	    AND A.CORP_ID = D.CORP_ID(+)                                        \n "
	                +"	    AND A.ACCT_GB = E.DETAILCD(+)                                       \n "	                
	                +"      AND B.PAYREQ_ID = '"+payreqId+"'                                    \n ";

	         //2019.04.03 신용길부장님 수정요청
            //+"	        ,B.CUR_PREPAY_APPLYAMT AS SAMT_DEDUCT														\n "
			//+"	        ,B.CUR_PREPAY_VAT AS VAMT_DEDUCT															\n "
			//+"	        ,(B.CUR_PREPAY_APPLYAMT + B.CUR_PREPAY_VAT) AS AMT_DEDUCT										\n "
			// +"	        ,(B.CUR_REQ_AMT+B.CUR_REQ_VATAMT) * NVL(B.CUR_ACMP_EXCRATE,1) AS AMT_BUILT								\n "
			//+"	        ,B.CUR_REQ_AMT * NVL(B.CUR_ACMP_EXCRATE,1) AS SAMT_BUILT								\n "
			//+"	        ,B.CUR_REQ_VATAMT * NVL(B.CUR_ACMP_EXCRATE,1) AS VAMT_BUILT								\n "
			//+"	        ,(B.CUR_REQ_AMT+B.CUR_REQ_VATAMT) AS AMT_BUILT_CURR														\n "
			//+"	        ,B.CUR_REQ_AMT AS SAMT_BUILT_CURR														\n "
			//+"	        ,B.CUR_REQ_VATAMT AS VAMT_BUILT_CURR														\n "
			
	        
			System.out.println(sqlstr);
			
			search.setStatement(sqlstr);
            rowSet = search.execute();
			
			row = RowSetUtility.getValueObject(rowSet.getRow(0));

        }catch(Exception e){
            WAFLogger.error(this.getClass().getName() + " --> getPur1100t() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
            
		}finally{
			if(row == null) row = new ValueObject();
		}
		
		return row;
	}
	
	public String getTmpslipId() throws Exception {
		
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        String sqlstr = "";
        ValueObject row = null;
        String resultId = "";
        
        try{
	        sqlstr =	"	SELECT PUR1040T_S.NEXTVAL AS TMPSLIP_ID FROM DUAL	\n";
	        
			System.out.println(sqlstr);
			
			search.setStatement(sqlstr);
            rowSet = search.execute();
			
			row = RowSetUtility.getValueObject(rowSet.getRow(0));
			resultId = row.get("TMPSLIP_ID");
			
        }catch(Exception e){
            WAFLogger.error(this.getClass().getName() + " --> updatePur1100t() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
            
		}finally{
			if(row == null) row = new ValueObject();
		}
		
		return resultId;
	}
	
	/**
	 * 대금이 선급금일시 pay_group, payment_terms 변경
	 * @param 	
	 * @return	ValueObject
	 */
	public ValueObject getPayGroup(Connection connection, String dept_cd) throws Exception {
	
		Persistent persistent_c = null;
		
		ValueObject vo = new ValueObject();
		ValueObject row = null;
		String sqlstr = "";
		
		try{
			
			persistent_c = new PersistentImpl(connection);
			
			sqlstr 	+= " SELECT																												\n"
                	+ "      DETAILCD																										\n"
                	+ "      ,DETAILNM																										\n"
                	+ " FROM   COM911T																										\n"
                	+ " WHERE BASECD = '005'                        																		\n"
                	+ " AND CHG_CODE04 = CASE 																								\n" 
                	+ "							WHEN (SELECT AC_UNIT_CD FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = '"+dept_cd+"' ) = '60' THEN '60'		\n"
                	+ "							WHEN (SELECT AC_UNIT_CD FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = '"+dept_cd+"' ) <> '10' THEN '20'		\n" 
                	+ "					 	ELSE '10' END || '_C' 																			\n" ;
			
			persistent_c.setStatement(sqlstr); 
			
			System.out.println(sqlstr);
			
			
			row = RowSetUtility.getValueObject(persistent_c.query().getRow(0));
			
			vo.set("DETAILCD", row.get("DETAILCD"));
			
			
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getSeq() : " + e.getMessage());
//			System.out.println(e.getMessage());
			WAFLogger.error(e);
			throw e;
            
		}finally{
			if(vo == null) vo = new ValueObject();
		}
		
		return vo;
	}
	
	private void updateAppr(Connection connection, ValueObject pur1100tData) throws Exception {
	
        Persistent persistent = new PersistentImpl(connection);
        StringBuffer sqlstr = null;

        try {
        	sqlstr = new StringBuffer();
        	String status = "02";
    		
   			sqlstr.append(" UPDATE  SUPP_APPR SET        					\n");
    		sqlstr.append("         REGU_STATUS_CD = '02'  					\n");
    		sqlstr.append(" WHERE   CORP_ID = ?    						\n");
    		sqlstr.append(" AND		ARR_ID	= ?    						\n");
    		
    		persistent.setStatement(sqlstr.toString());
            persistent.addParameter(pur1100tData.get("CORP_ID"));
            persistent.addParameter(pur1100tData.get("ARR_ID"));
    		
            persistent.execute();
 
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> updateAppr() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
	}
	
}