/**
 * @(#)file    PaymentBuyPrepaySignDriver.java
 * @(#)author  Ok seung hyun
 * @(#)version 1.0
 * @(#)date    2014-09-13
 * @(#)since   JDK 1.6.21
 *
 * Copyright (c) www.udapsoft.co.kr, 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 com.udapsoft.waf.system.HandlerStorage;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.config.WAFConfig;
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.common.sender.MessageSend;
import kr.co.udapsoft.ebid.buyer.bid.purchase.biz.InterfacePurERPiU;
import weblogic.jdbc.vendor.oracle.OracleThinBlob;


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

    /**
     * 테슽 파일
     * 
     */
    public PaymentBuyPrepaySignDriver() {
        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 {
    }

    /* 
     * (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 {
    }

    /*
     * (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"));
        	if( signDoc.get("SIGN_TYPE").equals("2") ){
				signUser.set("SLIP_DEPT", 	"");
				signUser.set("ACCSLIP_NO", 	signDoc.get("KEY_COL1"));
				
			}else {
				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);
        }
        
    	updatePur1030t(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 {
    	updatePur1030t(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 {
    	updatePur1030t(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 updatePur1030t(Connection connection, ValueObject vo, RowSet signUserInfo) throws Exception {

    	Persistent persistent = new PersistentImpl(connection);
        Persistent persistent1 = new PersistentImpl(connection);
        StringBuffer sqlstr = null;
        StringBuffer sqlstr1 = null;
        try {
        	sqlstr = new StringBuffer();
        	sqlstr1 = new StringBuffer();
        	String status = "";
        	
        	int complete = 0;

   			sqlstr.append(" SELECT REQ_DIV_CD  							\n"); //신청구분코드(1:신청, 2:부분신청, 3:포기) 	
    		sqlstr.append(" FROM PUR1030T								\n");
    		sqlstr.append(" WHERE PREPAY_ID = ?	    					\n");
    		persistent.setStatement(sqlstr.toString());
            persistent.addParameter(vo.get("KEY_COL2"));
            String div = RowSetUtility.getValueObject(persistent.query().getRow(0)).get("REQ_DIV_CD");
    		
    		if(vo.get("SIGN_DECI_CLASS").equals("14")){
    			status = "10"; //구매 선급완료
    			if("3".equals(div)){
    				status = "11"; //구매 포기완료
    			} 
    		}else {
    			status = "91"; //구배 선급반려
    			complete = 1;
    		}
    			    		
    		//구매)선급내역에 상태값 변경
   			sqlstr1.append(" UPDATE  PUR1030T SET   					\n");
    		sqlstr1.append("         PREPAY_PROC_GB = ?					\n");
    		sqlstr1.append(" WHERE   PREPAY_ID = ?    					\n");
    		persistent1.setStatement(sqlstr1.toString());
            persistent1.addParameter(status);
            persistent1.addParameter(vo.get("KEY_COL2"));
            persistent1.execute();
            
            ValueObject getPur1030tData = getPur1030t(vo.get("KEY_COL1"), vo.get("KEY_COL2"), vo.get("SIGN_INTERFACE_ID"));
            if(getPur1030tData.get("CHG_DIV_CD").equals("")){ //2019.05.10 신규=NULL, 추가='Y'
            	if(!"3".equals(div)){ //신청, 부분신청
            		//전표집계처리
            		if(complete == 0){
            			//ValueObject pur1030tData = getPur1030t(vo.get("KEY_COL1"), vo.get("KEY_COL2"), vo.get("SIGN_INTERFACE_ID"));
            			getPur1030tData.set("slipNo", vo.get("KEY_COL5"));
            			getPur1030tData.set("DOC_ID", vo.get("DOC_ID"));
            			getPayJipge(connection, getPur1030tData, signUserInfo);
            			
            		}
            	}else{//포기 
            		//ValueObject pur1030tData = getPur1030t(vo.get("KEY_COL1"), vo.get("KEY_COL2"), vo.get("SIGN_INTERFACE_ID"));
            		updatePayPlan(connection, getPur1030tData);
            	}
            }else{  //2018.07.27 추가 선급금 
            	if(!"3".equals(div)){ //신청, 부분신청 
            		//전표집계처리
            		if(complete == 0){
            			//ValueObject pur1030tData = getPur1030t(vo.get("KEY_COL1"), vo.get("KEY_COL2"), vo.get("SIGN_INTERFACE_ID"));
            			getPur1030tData.set("slipNo", vo.get("KEY_COL5"));
            			getPur1030tData.set("DOC_ID", vo.get("DOC_ID"));
            			getPayJipgeTybeB(connection, getPur1030tData, signUserInfo); //추가된 선급금 cont_pur_payplan insert 
            		}
            	}else{ //포기 
            		//2019.05.10 추가선급금 포기는 아무일 없음. 
            		//ValueObject pur1030tData = getPur1030t(vo.get("KEY_COL2"), vo.get("SIGN_INTERFACE_ID")); 
            	}
            }
            //2018.08.02 메일발송 전표발행 완료후 
            if(vo.get("SIGN_DECI_CLASS").equals("14")){
    			Send(vo.get("KEY_COL1")); 
    		}
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> updatePur1030t() : " + 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_CREATE1(?,?,?)}";
           
            cs = con.prepareCall(SPStateMent);
           
            cs.setString(1, prp_req_id);     
            cs.setString(2, userID);        
            cs.setString(3, slipNo);        

            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_CREATE1(?,?,?,?,?)}";
           
            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;
    	Persistent persistent_u01 = null;
		
		String sqlstr_i = "";
		String sqlstr_i_p = "";
		String sqlstr_u = "";
		String sqlstr_u01 = "";
	
		try{
			
			persistent_i = new PersistentImpl(connection);
			persistent_i_p = new PersistentImpl(connection);
			persistent_u = new PersistentImpl(connection);
			persistent_u01 = 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" +
							"		,PREPAY_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" +
							"		,PREPAY_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_u = 		" 	UPDATE CONT_PUR_PAYPLAN SET PAY_SUPPLY_AMT = ? WHERE PUR_CONT_MOD_ID = ?	AND PAY_SEQ = ?	";
			sqlstr_u01 = 	" 	UPDATE CONT_PUR_PAYPLAN SET PAY_SUPPLY_AMT = ? WHERE PUR_CONT_MOD_ID = ?	AND PAY_SEQ = ?	AND PAY_DIV_CD = '01'";
			
			RowSet rowSet = getPayReqList(connection, vo);
			
			persistent_i.setStatement(sqlstr_i);
			persistent_i_p.setStatement(sqlstr_i_p);
			persistent_u.setStatement(sqlstr_u);
			persistent_u01.setStatement(sqlstr_u01);
			
			String pay_group = "";
			String payment_term = "";
			String pay_item = "";
			String cost_code = "";
			String cost_type = "";
        	String billDiv = "";

        	if(jip_count > 0){
            	ValueObject contPurPnalData01 = getContPurPayplan01(vo.get("PUR_CONT_MOD_ID"));
            	ValueObject contPurPnalDataMax = getContPurPayplanMax(vo.get("PUR_CONT_MOD_ID"));
				
				if(rowSet.size() > 0){
					for(int i = 0; rowSet.size() > i; i++){
		            	if(contPurPnalData01.size() > 0){
		                    double paySuppluAmt01 = contPurPnalData01.getDouble("PAY_SUPPLY_AMT");
		                    double curReqAmt = rowSet.getRow(i).getDouble("R_P_S_A");
		                    double paySuppluAmtMax = contPurPnalDataMax.getDouble("PAY_SUPPLY_AMT");
		                    double culSuppluAmt = paySuppluAmt01 - curReqAmt;

		                    if(culSuppluAmt != 0){
								paySuppluAmtMax = paySuppluAmtMax + culSuppluAmt;
								persistent_u.addParameter(paySuppluAmtMax);
								persistent_u.addParameter(vo.get("PUR_CONT_MOD_ID"));
								persistent_u.addParameter(contPurPnalDataMax.get("PAY_SEQ"));
								System.out.println(sqlstr_u);
								persistent_u.execute();
								persistent_u.clearParameters();

								persistent_u01.addParameter(curReqAmt);
								persistent_u01.addParameter(vo.get("PUR_CONT_MOD_ID"));
								persistent_u01.addParameter(contPurPnalDataMax.get("MINPAY_SEQ"));
								System.out.println(sqlstr_u01);
								persistent_u01.execute();
								persistent_u01.clearParameters();
							}
		            	}

						//자재대금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("REQ_PREPAY_AMT")); //구매금액
						persistent_i.addParameter(rowSet.getRow(i).get("REQ_PREPAY_SUPPLY_AMT")); //공급가액
						persistent_i.addParameter(rowSet.getRow(i).get("REQ_PREPAY_SURTAX_AMT")); //부가세
						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();
						
						//증빙생성
						prcDoc020t(connection, tmpslipId_i, vo.getInt("USERID"), tmpslipId_i, "0");
						
			        	//전표생성프로시저호출
			        	//callPurInvoiceCreate(connection, tmpslipId_i, vo.get("USERID"), vo.get("slipNo"));
			        
			        	if(rowSet.getRow(i).get("TAX_METHOD_CD").equals("1")){	//역발행(SGC이앤씨 발행)일때 발행
			        		billDiv = "1";
			        		//세금계산서 발행
			        		//createSMbill(connection, tmpslipId_i, vo.get("USERID"), rowSet.getRow(i).get("PAYREQ_ID"), rowSet.getRow(i).get("ORD_DESC"), batchId);
			        	}
			        	//세금계산서 번호업테이트
			        	//update1040tConId(connection, tmpslipId_i, rowSet.getRow(i).get("PAYREQ_ID"));
			        	
					}
					
				}
				
				String paySeq = getErpPaySeq(vo.get("YY_PREPAY"));
				vo.set("NO_PREPAY", paySeq);

				try {
					InterfacePurERPiU.insertPrepayInfo(vo, signUserInfo);
				}catch(Exception e) {
					connection.rollback();
	        		throw new Exception("ERPIU insert Error"+e.getMessage());
				}
				
				if(billDiv.equals("1")){
					connection.commit();
		        	//세금계산서 역발행 요청
		        	//apProcess(connection, batchId);
				}
	        	
			}else{
				//storage.setMessage("집계할수 있는 내용이 없습니다.");
				return false;
			}
			
			return true;
			
		} catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updatePur1030t() : " + e.getMessage());
            WAFLogger.error(e);
            throw e;
            //return false;
        }
		
    }

    //집계처리
    public boolean getPayJipgeTybeB(Connection connection, ValueObject vo, RowSet signUserInfo) throws Exception {
    	
    	Persistent persistent_i = null;
    	Persistent persistent_i_p = null; 
    	
    	String sqlstr_i = "";
    	String sqlstr_i_p = ""; 
    	
    	try{
    		
    		persistent_i = new PersistentImpl(connection);
    		persistent_i_p = 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" +
    				"		,PREPAY_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" +
    				"		,PREPAY_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" ;
    		
    		
    		RowSet rowSet = getPayReqList(connection, vo);
    		
    		persistent_i.setStatement(sqlstr_i);
    		persistent_i_p.setStatement(sqlstr_i_p);
    		
    		String pay_group = "";
    		String payment_term = "";
    		String pay_item = "";
    		String cost_code = "";
    		String cost_type = "";

    		if(jip_count > 0){
    			if(rowSet.size() > 0){
    				for(int i = 0; rowSet.size() > i; i++){
    					if(vo.get("PAY_DIV").equals("2")){
    						savePlanData(connection,vo, rowSet.getRow(i).get("REQ_PREPAY_SUPPLY_AMT"));//insert cont_pur_payplan
    					}
    					//자재대금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("REQ_PREPAY_AMT")); //구매금액
    					persistent_i.addParameter(rowSet.getRow(i).get("REQ_PREPAY_SUPPLY_AMT")); //공급가액
    					persistent_i.addParameter(rowSet.getRow(i).get("REQ_PREPAY_SURTAX_AMT")); //부가세
    					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();
    					
    					 
    					
    					//증빙생성
    					prcDoc020t(connection, tmpslipId_i, vo.getInt("USERID"), tmpslipId_i, "0");
    					
    					//전표생성프로시저호출
    					//callPurInvoiceCreate(connection, tmpslipId_i, vo.get("USERID"), vo.get("slipNo"));
    					
    					//세금계산서 번호업테이트
    					//update1040tConId(connection, tmpslipId_i, rowSet.getRow(i).get("PAYREQ_ID"));
    					
    				}
    				
    			}
    			
	        	String paySeq = getErpPaySeq(vo.get("YY_PREPAY"));
				vo.set("NO_PREPAY", paySeq);
				
				try {
					InterfacePurERPiU.insertPrepayInfo(vo, signUserInfo);
				}catch(Exception e) {
					connection.rollback();
	        		throw new Exception("ERPIU insert Error"+e.getMessage());
				}
				
	        	
    			
    		}else{
    			//storage.setMessage("집계할수 있는 내용이 없습니다.");
    			return false;
    		}
    		
    		return true;
    		
    	} catch(Exception e) {
    		WAFLogger.error(this.getClass().getName() + " --> updatePur1030t() : " + e.getMessage());
    		WAFLogger.error(e);
    		throw e;
    		//return false;
    	}
    	
    }

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

        String strResult = null;
		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.doBizBuyPre(con, outputStream, tmpslipID, null);
				//sacDOCreatePDF4.doBizBuyPre1(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 PUR1030T A ,PUR2000T B								\n" +
						"	WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID								\n" +
						"	AND A.PREPAY_PROC_GB = '10' 											\n" +
						"	AND A.PREPAY_ID = '"+vo.get("PREPAY_ID")+"' 							\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 A.PREPAY_ID  																													\n" +
						"		,B.DEPT_CD	DEPT_CD	--   현장코드		\n" + // 2015.9.10 Mapping Dept변경 (원B.DEPT_CD)
			            "		,'1' AS PREPAY_GB    --선급입고구분																									\n" +
						"		,TO_CHAR(A.PREPAY_DATE, 'YYYYMMDD')        ORD_CON_DATE    --거래일자																	\n" +
						"		,B.GUA_GB        GUA_GB    --증빙코드																									\n" +
						"		,CASE WHEN  (SELECT AC_UNIT_CD  FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = B.DEPT_CD ) = '10' THEN '21'											\n" +
						"			  WHEN  (SELECT AC_UNIT_CD FROM ETEC_ERPIF.ACZ10100 WHERE DEPT_CD = B.DEPT_CD ) <> '10' THEN '22'											\n" +
						"		ELSE B.PAY_GROUP																													\n" +
                        "		END PAY_GROUP    --선급금PAY_GROUP																									\n" +
                        "		,B.PAYMENT_TERMS    PAYMENT_TERMS    --PAYMENT_TERMS																				\n" +
                        "       ,F_GET_REMARK_DATE(A.PREPAY_DATE) || ' 선급금(' || TO_NUMBER(A.PREPAY_NO)     || '차) ' || B.CST_DOC_NAME  AS ORD_DESC				\n" +
                        "		,B.ORD_AMT    ORD_AMT    --구매금액																									\n" +
                        "		,A.PREPAY_SUPPLY_AMT    ORD_SUPPLY_AMT        --부가세																				\n" +
                        "		,A.PREPAY_SURTAX_AMT    CUR_REQ_VATAMT        --부가세																				\n" +
						"		,F_GET_DEALR_CD_EBID(C.CORP_ID)    CORP_ID    --거래처코드																									\n" +
						"		,F_GET_DEALR_NO_EBID(C.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.PREPAY_ID    PAYREQ_ID    --선급신청ID																								\n" +
						"		,A.TAX_DY as  REQ_DATE                    -- 작성일자 																				\n" +
						"		,B.CON_GB                    -- 계약구분																								\n" +
						"		,B.ACCT_GB                    -- 분개구분																								\n" +
						"		,B.ELEC_CON_YN                    -- 전자계약여부																						\n" +
						"		,A.REQ_PREPAY_AMT AS REQ_PREPAY_AMT																									\n" +
						"		,A.REQ_PREPAY_SUPPLY_AMT AS REQ_PREPAY_SUPPLY_AMT																					\n" +
						"		,A.REQ_PREPAY_SURTAX_AMT AS REQ_PREPAY_SURTAX_AMT																					\n" +
						"		,1182  AS CUR_PAYING_AMT          -- 금회지급예정액																					\n" +
						"		,A.PREPAY_SUPPLY_AMT  AS CUR_REQ_AMT          -- 금회청구액																			\n" +
						"		,A.REQ_PREPAY_SUPPLY_AMT AS R_P_S_A																									\n" +
						"		,A.TAX_METHOD_CD																													\n" +
						"	FROM PUR1030T A, PUR2000T B, SUPP_INFO C																					\n" +
						"	WHERE 1=1																																\n" +
						"	AND A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID																								\n" +
						"	AND B.CORP_ID = C.CORP_ID																											\n" +
						"	AND	A.PREPAY_PROC_GB	= '10'																											\n" +
						"	AND A.PREPAY_ID = '"+params.get("PREPAY_ID")+"'																							\n"
						;
	        
			System.out.println(sqlstr);
			persistent_c.setStatement(sqlstr);
			rowSet = persistent_c.query();
			
        }catch (WAFSQLException se) {
            WAFLogger.error(this.getClass().getName() + " --> updatePur1030t() : " + se.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(se);
            throw se;
        }
        finally {    
        	 if(rowSet == null){
             	rowSet = new RowSetImpl();
             }
        }
        
        return rowSet;
			
    }
    
	public ValueObject getPur1030t(String purContModId, String prepayId, String signInterfaceId) throws Exception {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlstr = new StringBuffer();
        ValueObject row = null;
        
        try{
        	
        	sqlstr.append("SELECT  B.PUR_CONT_ID															\n");
        	sqlstr.append("        ,B.PUR_CONT_MOD_ID														\n");
        	sqlstr.append("        ,(																		\n");
        	sqlstr.append("        SELECT	MAX(PAY_SEQ)													\n");
        	sqlstr.append("        FROM 	CONT_PUR_PAYPLAN												\n");
        	sqlstr.append("        WHERE	PAY_DIV_CD = '01'												\n");
        	sqlstr.append("        AND PUR_CONT_ID = B.PUR_CONT_ID											\n");
        	sqlstr.append("        AND PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID									\n");
        	sqlstr.append("        ) AS PAY_SEQ																\n");
        	sqlstr.append("        ,B.PREPAY_ID																\n");
        	sqlstr.append("        ,A.DEPT_CD																\n");
        	sqlstr.append("        ,B.CHG_DIV_CD															\n");
        	sqlstr.append("        ,A.PAY_DIV																\n");
        	sqlstr.append("        ,TO_CHAR(TO_DATE(B.TAX_DY, 'YYYYMMDD'), 'YYYY-MM') AS P_ENTER_DT      	\n");
        	sqlstr.append("        ,F.CRE_BY AS USERID														\n");
        	sqlstr.append("        ,TO_CHAR(PREPAY_DATE,'YYYY') AS YY_PREPAY								\n");
        	sqlstr.append("        ,'' AS NO_PREPAY															\n");
        	sqlstr.append("        ,'001' AS SQ_PREPAY														\n");
        	sqlstr.append("        ,'"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY					\n");
        	sqlstr.append("        ,NVL(C.ERP_SITE_CD,A.DEPT_CD) AS CD_SITE									\n");
        	sqlstr.append("        ,'01' AS WM_CM00308														\n");
        	sqlstr.append("        ,TO_CHAR(PREPAY_DATE,'YYYYMMDD') AS DT_PREPAY							\n");
        	sqlstr.append("        ,'01' AS WM_CM00301														\n");
        	sqlstr.append("        ,A.ERP_NO_ECUT AS NO_ECUT												\n");
        	sqlstr.append("        ,TO_CHAR(A.CST_DATE,'YYYYMM') AS YM_ECUT									\n");
        	sqlstr.append("        ,E.ERP_CD_COOP AS CD_COOP												\n");
        	sqlstr.append("        ,REPLACE(E.DCERTI_NO,'-','') AS NO_COMPANY								\n");
        	sqlstr.append("        ,NVL(B.REQ_PREPAY_SUPPLY_AMT,0)*F_GET_EXCHANGE_RATE(B.PREPAY_DATE,A.CURRENCY_GB,'KRW') AS AMT_PREPAY							\n");//2019.03.07
        	sqlstr.append("        ,NVL(B.REQ_PREPAY_SURTAX_AMT,0)*F_GET_EXCHANGE_RATE(B.PREPAY_DATE,A.CURRENCY_GB,'KRW') AS VAT_PREPAY							\n");//2019.03.07
        	sqlstr.append("        ,TO_CHAR(B.CRE_DATE,'YYYYMMDD') AS DT_REQU								\n");
        	sqlstr.append("        ,GET_USER_NM(B.CRE_BY) AS NM_REQU										\n");
        	sqlstr.append("        ,TO_CHAR(F.CRE_DATE,'YYYYMMDD') AS DT_CONFIRM							\n");
        	sqlstr.append("        ,GET_USER_NM(F.CRE_BY) AS NM_CONFIRM										\n");
        	sqlstr.append("        ,PREPAY_DESC AS DESC_PREPAY												\n");
        	sqlstr.append("        ,(SELECT USER_SABUN FROM PUB0120T WHERE ACCT_ID = B.CRE_BY) AS ID_INSERT	\n");
        	sqlstr.append("        ,TO_CHAR(B.CRE_DATE,'YYYYMMDD') AS DTS_INSERT							\n");
        	sqlstr.append("        ,NVL(G.CHG_CODE10,A.CURRENCY_GB) AS CURR_SOUR							\n");
        	sqlstr.append("        ,F_GET_EXCHANGE_RATE(B.PREPAY_DATE,A.CURRENCY_GB,'KRW') AS UNT_CURR	 \n"); //EXCHANGE AS 2019.03.07
        	sqlstr.append("        ,NVL(B.REQ_PREPAY_SUPPLY_AMT,0) AS SAMT_CURR_PREPAY						\n");
        	sqlstr.append("        ,NVL(B.REQ_PREPAY_SURTAX_AMT,0) AS VAMT_CURR_PREPAY						\n");
        	sqlstr.append("        ,'Y' AS YN_BAN															\n");
        	sqlstr.append("        ,DECODE(B.TAX_METHOD_CD,'1','0','2') AS YN_ISS							\n");
        	//2019.07.29 ERP 자동분개를 위한 계정코드
        	sqlstr.append("        ,CASE WHEN A.CURRENCY_GB = 'KRW' THEN                                    \n");
        	sqlstr.append("                   CASE WHEN NVL(B.SUPP_DIRECT_YN,'') = 'Y' THEN NVL(J.CHG_CODE12, '')  \n");
        	sqlstr.append("                        ELSE NVL(J.CHG_CODE10, '')                   			\n");
        	sqlstr.append("                   END                                                   		\n");
        	sqlstr.append("              ELSE CASE WHEN NVL(B.SUPP_DIRECT_YN,'') = 'Y' THEN NVL(J.CHG_CODE13, '')  \n");
        	sqlstr.append("                        ELSE NVL(J.CHG_CODE11, '')                   			\n");
        	sqlstr.append("              END                                                   				\n");
        	sqlstr.append("         END AS CD_COST_TRADE 													\n");
        	sqlstr.append("FROM    PUR2000T A																\n");
        	sqlstr.append("        ,PUR1030T B																\n");
        	sqlstr.append("        ,ETEC_ERPIF.SITE_MAPPING C												\n");
        	sqlstr.append("        ,CONT_PUR_SUPP D															\n");
        	sqlstr.append("        ,ETEC_ERPIF.ACZ10800 E													\n");
        	sqlstr.append("        ,(																		\n");
        	sqlstr.append("        SELECT  CRE_BY, CRE_DATE													\n");
        	sqlstr.append("        FROM    SIGN001T															\n");
        	sqlstr.append("        WHERE   SIGN_INTERFACE_ID = "+signInterfaceId+"							\n");
        	sqlstr.append("        ) F																		\n");
        	sqlstr.append("        ,(																		\n");
        	sqlstr.append("        SELECT  DETAILCD, CHG_CODE10												\n");
        	sqlstr.append("        FROM    EBID_COM911T														\n");
        	sqlstr.append("        WHERE   BASECD = '911'													\n");
        	sqlstr.append("        ) G																		\n");
        	//sqlstr.append("        ,BID_SUPP H																\n");2019.03.07
        	sqlstr.append("        ,SUPP_INFO I																\n");
        	sqlstr.append("        ,(SELECT DETAILCD, CHG_CODE10, CHG_CODE11, CHG_CODE12, CHG_CODE13 FROM EBID_COM911T WHERE BASECD = 'PUR-ACCT_GB' ) J   \n");
        	sqlstr.append("WHERE   A.PUR_CONT_ID = B.PUR_CONT_ID											\n");
        	sqlstr.append("AND     A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID									\n");
        	sqlstr.append("AND     A.DEPT_CD = C.EBID_SITE_CD(+)											\n");
        	sqlstr.append("AND     A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID									\n");
        	sqlstr.append("AND     D.CORP_ID != 0															\n");
        	sqlstr.append("AND     D.CORP_ID = I.CORP_ID													\n");
        	sqlstr.append("AND     REPLACE(I.CORP_REG_NO,'-','') = REPLACE(E.DCERTI_NO,'-','')				\n");
        	sqlstr.append("AND     E.ADDITEM20 = 'P'               											\n"); //2019.02.26
        	sqlstr.append("AND     A.CURRENCY_GB = G.DETAILCD(+)											\n");
        	//sqlstr.append("AND     A.BID_NO = H.BID_NO														\n");//2019.02.26
        	//sqlstr.append("AND     A.BID_DEG = H.BID_DEG													\n");//2019.02.26
        	//sqlstr.append("AND     D.CORP_ID = TO_NUMBER(H.VENDOR_CD)										\n");//2019.02.26
        	sqlstr.append("AND     A.ACCT_GB = J.DETAILCD(+) 												\n");
        	sqlstr.append("AND     B.PREPAY_ID = "+prepayId+"												\n");
        	sqlstr.append("AND     B.PUR_CONT_MOD_ID = "+purContModId+"										\n");
        	
			System.out.println(sqlstr.toString());
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
			row = RowSetUtility.getValueObject(rowSet.getRow(0));

        }catch(Exception e){
            WAFLogger.error(this.getClass().getName() + " --> getPur1030t() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
            
		}finally{
			if(row == null) row = new ValueObject();
		}
		
		return row;
	}

	public ValueObject getContPurPayplanMax(String purContModId) throws Exception {
		
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        String sqlstr = "";
        ValueObject row = null;
        
        try{
	        sqlstr =	"	SELECT B.PAY_SUPPLY_AMT    , A.PAY_SEQ,  A.MINPAY_SEQ																					\n" +
						"	FROM (																																	\n" +
						"			SELECT PUR_CONT_MOD_ID, MIN(PAY_SEQ) AS MINPAY_SEQ, MAX(PAY_SEQ) AS PAY_SEQ, MAX(PAY_SUPPLY_AMT) AS  PAY_SUPPLY_AMT				\n" +
						"			FROM CONT_PUR_PAYPLAN																									\n" +
						"			WHERE 1=1																														\n" +
						"			AND PUR_CONT_MOD_ID = '"+purContModId+"'																						\n" +
						"			GROUP BY  PUR_CONT_MOD_ID																										\n" +
						"		) A,																																\n" +
	        			"		CONT_PUR_PAYPLAN B   																										\n" +
	        			"	WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID																								\n" +
	        			"	AND A.PAY_SEQ = B.PAY_SEQ																												\n"
	        			;
	        
			System.out.println(sqlstr);
			
			search.setStatement(sqlstr);
            rowSet = search.execute();
			
            if(rowSet.size() > 0){
            	row = RowSetUtility.getValueObject(rowSet.getRow(0));
            }

        }catch(Exception e){
            WAFLogger.error(this.getClass().getName() + " --> getContPurPayplanMax() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
            
		}finally{
			if(row == null) row = new ValueObject();
		}
		
		return row;
	}
	
	public ValueObject getContPurPayplan01(String purContModId) throws Exception {
		
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		String sqlstr = "";
		ValueObject row = null;
		
		try{
			sqlstr ="	SELECT PUR_CONT_MOD_ID, PAY_SEQ, PUR_CONT_ID, PAY_SUPPLY_AMT, GUAR_YN, WARR_GUBUN, VP_YN, PAY_DIV_CD, PAYREQ_ID, PAY_YN							\n" +
					"	FROM CONT_PUR_PAYPLAN																													\n" +
					"	WHERE PAY_DIV_CD = '01'																															\n" +
					"	AND PUR_CONT_MOD_ID = '"+purContModId+"'																										\n"
					;
			
			System.out.println(sqlstr);
			
			search.setStatement(sqlstr);
			rowSet = search.execute();
			if(rowSet.size() > 0){
				row = RowSetUtility.getValueObject(rowSet.getRow(0));
			}
			
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getContPurPayplan() : " + 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() + " --> updatePur1030t() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
            
		}finally{
			if(row == null) row = new ValueObject();
		}
		
		return resultId;
	}

	public String getBatchId() throws Exception {
		
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        String sqlstr = "";
        ValueObject row = null;
        String resultId = "";
        
        try{
	        sqlstr =	"	SELECT TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS') AS BATCH_ID FROM DUAL	\n";
	        
			System.out.println(sqlstr);
			
			search.setStatement(sqlstr);
            rowSet = search.execute();
			
			row = RowSetUtility.getValueObject(rowSet.getRow(0));
			resultId = row.get("BATCH_ID");
			
        }catch(Exception e){
            WAFLogger.error(this.getClass().getName() + " --> getBatchId() : " + 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;
	}
	
	public void updatePayPlan(Connection con, ValueObject vo) throws Exception {
		Persistent persistent = new PersistentImpl(con);
		StringBuffer sqlstr = null;
		try {
			
			
			
			sqlstr = new StringBuffer();
			
			//구매)선급내역에 상태값 변경
			sqlstr.append(" UPDATE  CONT_PUR_PAYPLAN SET   				\n");
			sqlstr.append("         PAY_YN  = 'N', 					\n");
			sqlstr.append("         PAY_SUPPLY_AMT  = '0' 					\n");
			sqlstr.append(" WHERE PUR_CONT_ID =   ?  AND PAY_DIV_CD = '01'   					\n");
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(vo.get("PUR_CONT_ID"));
			persistent.execute();
			
		}catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> updatePayPlan() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			throw e;
		}
	}
	
	/**
     * 대금계획 추가
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */	
	public boolean savePlanData(Connection connection, ValueObject vo, String amt) throws Exception {
		Persistent persistent = new PersistentImpl(connection);
		StringBuffer sql = new StringBuffer();
		
		try {
			sql.append("		INSERT 							\n ");
			sql.append("		  INTO CONT_PUR_PAYPLAN 		\n ");
			sql.append("		       ( 						\n ");
			sql.append("		           PUR_CONT_MOD_ID		\n ");
			sql.append("		         ,PAY_SEQ				\n ");
			sql.append("		         ,PUR_CONT_ID			\n ");
			sql.append("		         ,PAY_SUPPLY_AMT		\n ");
			sql.append("		         ,PAY_DIV_CD		\n ");
			sql.append("		         ,PAY_YN		\n ");
			sql.append("		         ,CRE_BY		\n ");
			sql.append("		         ,CRE_DATE		\n ");
			sql.append("		       ) 		\n ");
			sql.append("		       VALUES 		\n ");
			sql.append("		       ( ?		\n ");			//pur_cont_mod_id
			sql.append("		         , (select nvl(max(pay_seq), 0) + 1 from CONT_PUR_PAYPLAN where PUR_CONT_MOD_ID = ?) 		\n ");	//pay_seq
			sql.append("		         , ?		\n ");		//pur_cont_id
			sql.append("		         , ?		\n ");		//pay_supply_amt
			sql.append("		         , '01'		\n ");		//pay_div_cd
			sql.append("		         , 'Y'		\n ");		//pay_yn
			sql.append("		         , '99996'		\n ");
			sql.append("		         , SYSDATE		\n ");
			sql.append("		       ) 		\n ");

			//WAFLogger.debug("DDDDDDDDDDDDDDDD" + sqlParams.get("PUR_CONT_MOD_ID"));
			persistent.setStatement(sql.toString());
			
			int paramIdx = 1;

			persistent.addParameter(vo.get("PUR_CONT_MOD_ID"));
			persistent.addParameter(vo.get("PUR_CONT_MOD_ID"));
			persistent.addParameter(vo.get("PUR_CONT_ID"));
			persistent.addParameter(amt);
			
			persistent.execute();
			return true;
		}         
			catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> savePlanData() : " + e.getMessage());
            WAFLogger.error("SQL : " + sql);
            WAFLogger.error(e);
            throw e;
        }
	}
	
	//20115.10.6 선급완료후 email발송  
	public void Send(String PUR_CONT_MOD_ID) throws Exception {
		HandlerStorage Stroage = new HandlerStorage();
		MessageSend messageSend = new MessageSend(Stroage);
     	RowSet rsUserInfo = getUserInfo(PUR_CONT_MOD_ID);
     	
     	boolean email = false;
 		StringBuffer mailtitle = new StringBuffer();
 		StringBuffer mailtitlein = new StringBuffer();
        StringBuffer mailContent = new StringBuffer();
        
 		if( rsUserInfo.size() > 0 && !rsUserInfo.getRow(0).get("MAT_MAIL").isEmpty()){
 			
 			mailtitle.append("* 선급금 신청 되었습니다.");
	        mailtitlein.append("* 선급금 신청 되었습니다.");
	        mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; text-align:left;'>");
	        mailContent.append("선급금 신청 되었습니다. ");
	        mailContent.append("경영정보의 전표마감 현황 확인 바랍니다.  <br>");
	        mailContent.append("1. 현      장: (" + rsUserInfo.getRow(0).get("DEPT_CD") + ")" + rsUserInfo.getRow(0).get("DEPT_NAME") + "  <br>");
	        mailContent.append("2. 계약번호: " + rsUserInfo.getRow(0).get("CONT_NO") + "  <br>");
	        mailContent.append("3. 계  약 명: " + rsUserInfo.getRow(0).get("CONT_NAME") + "  <br>");
	        mailContent.append("<br>");
	        mailContent.append("</font>");
	        email = true;
	       
	        if(email){   //구매팀, 회계팀  
            	messageSend.sendEmail(rsUserInfo.getRow(0).get("MAT_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
            	messageSend.sendEmail(rsUserInfo.getRow(0).get("ACT_MAIL1"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
            	if (!rsUserInfo.getRow(0).get("ACT_MAIL2").equals("NA") ){
            		messageSend.sendEmail(rsUserInfo.getRow(0).get("ACT_MAIL2"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
                }
            }
            
 		}
	}
	
	//2015.10.6 구매, 회계 담당자 정보 
	private RowSet getUserInfo(String PUR_CONT_MOD_ID) throws Exception{
    	
    	Search search = new SearchImpl();
    	RowSet rowSet = new RowSetImpl();
    	
    	StringBuffer sqlstr = new StringBuffer();       
    	
    	try {
			sqlstr.append(" SELECT B.EMAIL                 MAT_MAIL                                    \n");
			sqlstr.append("       ,NVL(D.CHG_CODE08, 'NA') ACT_MAIL1                                   \n");
			sqlstr.append("       ,NVL(D.CHG_CODE09, 'NA') ACT_MAIL2                                   \n");
			sqlstr.append("       ,A.DEPT_CD               DEPT_CD                                     \n");
			sqlstr.append("       ,A.DEPT_NAME             DEPT_NAME                                   \n");
			sqlstr.append("       ,A.ORD_DOC_NO            CONT_NO                                     \n");
			sqlstr.append("       ,A.CST_DOC_NAME          CONT_NAME                                   \n");
			sqlstr.append("   FROM PUR2000T A                                                   		\n");
    		sqlstr.append("       ,CONT_PUR_SUPP B                                              		\n");
    		sqlstr.append("       ,ETEC_ERPIF.ACZ10100 C                                                          \n");          
    		sqlstr.append("       ,EBID_COM911T D                                                           \n");
    		sqlstr.append("  WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID                               \n");
    		sqlstr.append("    AND A.DEPT_CD = C.DEPT_CD                                               \n");
    		sqlstr.append("    AND C.AC_UNIT_CD = D.DETAILCD                                           \n");
    		sqlstr.append("    AND D.BASECD = '101'                                                    \n");
    		sqlstr.append("    AND A.PUR_CONT_MOD_ID =  '" + PUR_CONT_MOD_ID + "'                        \n");
    		sqlstr.append("    AND B.SIGN_SEQ = 1                                                      \n");
    			   
    		search.setStatement(sqlstr.toString());
    		rowSet = search.execute();            
    	}
    	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 String getErpPaySeq(String YY_PREPAY) throws Exception {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlstr = new StringBuffer();
        String rtnStr = "";
        
        try{
        	sqlstr.append("SELECT  TO_CHAR(NVL(MAX(TO_NUMBER(NO_PREPAY)),0)+1,'FM00000') AS NO_PREPAY	\n");
        	sqlstr.append("FROM    WM_E_EXECUTE_PREPAY@ERPIU											\n");
        	sqlstr.append("WHERE   YY_PREPAY = '"+YY_PREPAY+"'											\n");

			System.out.println(sqlstr.toString());
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            if( rowSet != null ) {
            	rtnStr = rowSet.getRow(0).get("NO_PREPAY");
            }

        }catch(Exception e){
            WAFLogger.error(this.getClass().getName() + " --> getErpPaySeq() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
		}
		
		return rtnStr;
	}
	
}