/**
 * @(#)file    PrepaymentMiddleSignDriver.java
 * @(#)author  OK
 * @(#)version 1.0
 * @(#)date    2014-09-15
 * @(#)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 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.Row;
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.SACDOCreatePDF6;
import kr.co.udapsoft.ebid.buyer.bid.outside.biz.InterfaceOutERPiU;
import weblogic.jdbc.vendor.oracle.OracleThinBlob;

public class PrepaymentSignDriver implements CommonSignInterface {
	

    /**
     * 
     */
    public PrepaymentSignDriver() {
        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("ACC_SLIP_NO", 	acctSlipNo);
			signUser.set("ACCSLIP_NO", 	"");
				
			signUser.set("SLIP_DT", 		signDoc.get("KEY_COL4"));
			signUser.set("SLIP_NO", 		signDoc.get("KEY_COL5"));
        
        	insertSign002t(connection, signUser);
        }
        
    	updateSUB1030T(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 {
    	updateSUB1030T(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 {
    	//u pdateSUB1040T(connection, signDoc);
    }

    /*
     * (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 {
    }
    
    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());
    		return rowSet;
    		
    	}catch(WAFSQLException se) {
    		Logger.err.println("SQL : \n" + se.getStatement());
    		Logger.err.println("PARAM : \n" + se.getParameter());
    		throw se;
    	}
    }

    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("         ,ACC_SLIP_NO                \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");
			sqlstr.append("         )                          \n");
			
			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.addParameter(vo.get("ACC_SLIP_NO"));			
			persistent.execute();
			
		}
		catch(Exception e) {
			
			WAFLogger.error(this.getClass().getName() + " --> insertSign002t() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			throw e;
		}
    }
	
    public void updateSUB1030T(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 = "";
        	
   			sqlstr1.append(" SELECT REQ_DIV_CD  							\n");
    		sqlstr1.append(" FROM SUB1030T						\n");
    		sqlstr1.append(" WHERE PREPAY_ID = ?	    					\n");
    		persistent1.setStatement(sqlstr1.toString());
            persistent1.addParameter(vo.get("KEY_COL2"));
            String div = RowSetUtility.getValueObject(persistent1.query().getRow(0)).get("REQ_DIV_CD");
        	
        	sqlstr.append(" UPDATE SUB1030T            				\n");
        	sqlstr.append("    SET STATUS_CD	= ?    						\n");        	
        	sqlstr.append("      , UPD_DATE		= SYSDATE    				\n");
        	sqlstr.append("  WHERE PREPAY_ID IN (?) 					\n");
    		
    		persistent.setStatement(sqlstr.toString());
    		
    		int complete = 0;
    		
    		if ( vo.get("SIGN_DECI_CLASS").equals("14")) {	
    			status = "10"; 	// 완료
    			if("3".equals(div)){
    				status = "21"; //구매 포기완료
    			}
    		} else {										
    			//persistent.addParameter("91");	// 반려
    			status = "91";
    			complete = 1;
    		}
    		persistent.addParameter(status);
            persistent.addParameter(vo.get("KEY_COL2"));
            persistent.execute();
            
            if(!"3".equals(div)){
	            //전표집계처리
	            if(complete == 0){
	            	//외주 선급금 전표집계 
	
	                //선급금포기각서 첨부파일 복사처리
	                //doSendCCSFile(connection, "SUPP_PREPAYMENTS_GIVEUP", vo.get("KEY_COL2"));
	
	                //
	                //doSendCCSFile(connection, "SUPP_PREPAYMENTS_BILLING", vo.get("KEY_COL2"));
	
	                //
	                //doSendCCSFile(connection, "SUPP_TAX_BILL", vo.get("KEY_COL2"));
	
	                //
	                //doSendCCSFile(connection, "INVOICE", vo.get("KEY_COL2"));
	                
	                
	            	prepaymentJipge(connection, vo, signUserInfo);
	            	
	            }
            }
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> updateSUB1030T() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
    
    public void prepaymentJipge(Connection connection, ValueObject vo, RowSet signUserInfo) throws Exception {
    	
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
        ValueObject selectResult = new ValueObject();
        
        try {
        	
        	sql.append(" SELECT  A.DEPT_CD                                                                              \n");
		    sql.append("         ,B.SUB_CONT_ID                                                                         \n");
		    sql.append("         ,A.SUB_CONT_MOD_ID                                                                     \n");
		    sql.append("         ,A.ERP_NO_CONT                                                                         \n");
		    sql.append("         ,A.ERP_SQ_CONT                                                                         \n");
		    sql.append("         ,B.PREPAY_ID                                                                           \n");
		    sql.append("         ,TO_CHAR(TO_DATE(B.TAX_DY,'YYYYMMDD'), 'YYYY-MM') AS P_ENTER_DT                                   \n");
		    sql.append("         ,(SELECT A.USER_SABUN FROM PUB0120T A WHERE ACCT_ID = C.CRE_BY) AS USER_SABUN          \n");
		    sql.append("         ,(SELECT A.USER_NAME FROM PUB0120T A WHERE ACCT_ID = C.CRE_BY) AS USER_NAME          	\n");
		    sql.append("         ,C.CRE_BY AS ACCT_ID                                                                   \n");
		    sql.append("         ,C.CRE_DATE AS REQ_DATE                                                                \n");
		    sql.append("         ,TO_CHAR(SYSDATE,'YYYYMMDD') AS APPLY_DATE                                             \n");
		    sql.append("         ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT                                     \n");
		    sql.append("         ,B.TAX_METHOD_CD AS TAX_METHOD_CD                                                      \n");
		    sql.append("         ,'"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY                                                                  \n");
		    sql.append("         ,NVL(D.ERP_SITE_CD,A.DEPT_CD) AS CD_SITE                                               \n");
		    sql.append("         ,'01' AS CD_CM00215                                                                    \n");
		    sql.append("         ,A.ERP_NO_CONT AS NO_CONT                                                              \n");
		    sql.append("         ,A.ERP_SQ_CONT AS SQ_CONT                                                              \n");
		    sql.append("         ,TO_CHAR(B.PREPAY_DATE,'YYYYMMDD') AS DT_PREPAY                                        \n");
		    sql.append("         ,FLOOR(B.REQ_PREPAY_SUPPLY_TAMT * F_GET_EXCHANGE_RATE(B.PREPAY_DATE,B.CON_CUR_CD,'KRW')) AS AMT_PREPAY			\n");
		    sql.append("         ,FLOOR(DECODE(A.PROOF_CD,'Z99',0,B.REQ_PREPAY_SURTAX_AMT) * F_GET_EXCHANGE_RATE(B.PREPAY_DATE,B.CON_CUR_CD,'KRW')) AS VAT_PREPAY	\n");
		    sql.append("         ,FLOOR(B.REQ_PREPAY_SUPPLY_NAMT * F_GET_EXCHANGE_RATE(B.PREPAY_DATE,B.CON_CUR_CD,'KRW')) AS FAMT_PREPAY			\n");
		    sql.append("         ,(SELECT CHG_CODE10 FROM EBID_COM911T WHERE BASECD = '911' AND DETAILCD = B.CON_CUR_CD) AS CURR_SOUR 	\n");
		    sql.append("         ,DECODE(B.CON_CUR_CD,'KRW',NULL,F_GET_EXCHANGE_RATE(B.PREPAY_DATE,B.CON_CUR_CD,'KRW')) AS UNT_CURR		\n");
		    sql.append("         ,DECODE(B.CON_CUR_CD,'KRW',NULL,B.REQ_PREPAY_SUPPLY_TAMT) AS AMT_CURR					\n");
		    sql.append("         ,DECODE(B.CON_CUR_CD,'KRW',NULL,DECODE(A.PROOF_CD,'Z99',0,B.REQ_PREPAY_SURTAX_AMT)) AS VAMT_CURR		\n");
		    sql.append("         ,DECODE(B.CON_CUR_CD,'KRW',NULL,B.REQ_PREPAY_SUPPLY_NAMT) AS FAMT_CURR					\n");
		    sql.append("         ,'Y' AS YN_BAN																			\n");
		    sql.append("         ,NVL(E.CHG_CODE10,B.CON_CUR_CD) AS CD_EXCH												\n");
		    sql.append("         ,DECODE(B.CON_CUR_CD,'KRW',NULL,F_GET_EXCHANGE_RATE(B.PREPAY_DATE,B.CON_CUR_CD,'KRW')) AS RT_EXCH						\n");
		    sql.append("         ,DECODE(B.CON_CUR_CD,'KRW',NULL,F_GET_EXCHANGE_DATE(B.PREPAY_DATE,B.CON_CUR_CD,'KRW')) AS DT_EXCH						\n");
		    sql.append("         ,DECODE(B.TAX_METHOD_CD,'1','0','2') AS YN_ISS											\n");
		    //2019.07.29 ERP 자동분개를 위한 계정코드
		    sql.append("         ,CASE WHEN A.CON_CUR_CD = 'KRW' THEN NVL(F.CHG_CODE10, '')  							\n");
		    sql.append("                   ELSE NVL(F.CHG_CODE11, '')  													\n");
		    sql.append("          END AS CD_COST_TRADE								      								\n");
		    sql.append(" FROM    SUB2000T A                                                                             \n");
		    sql.append("         ,SUB1030T B                                                                            \n");
		    sql.append("         ,(                                                                                     \n");
		    sql.append("         SELECT  MAX(SIGN_INTERFACE_ID)                                                         \n");
		    sql.append("                 ,MAX(KEY_COL2) AS KEY_COL2                                                     \n");
		    sql.append("                 ,MAX(CRE_BY) AS CRE_BY                                                         \n");
		    sql.append("                 ,MAX(TO_CHAR(CRE_DATE,'YYYYMMDD')) AS CRE_DATE                                 \n");
		    sql.append("         FROM    SIGN001T                                                                       \n");
		    sql.append("         WHERE   KEY_COL2 = '"+vo.get("KEY_COL2")+"'                                            \n");
		    sql.append("         AND     SIGN_CLASS_CD = 'ebid_oprpyD'                                                  \n");
		    sql.append("         GROUP BY KEY_COL2                                                                      \n");
		    sql.append("         ) C                                                                                    \n");
		    sql.append("         ,ETEC_ERPIF.SITE_MAPPING D                                                             \n");
		    sql.append(" 		,(SELECT DETAILCD, CHG_CODE10 FROM EBID_COM911T WHERE BASECD = '911') E 				\n");
		    sql.append(",(SELECT DETAILCD, CHG_CODE10, CHG_CODE11, CHG_CODE12, CHG_CODE13 FROM EBID_COM911T WHERE BASECD = 'SUB_ACCT_GB' ) F \n");
		    sql.append(" WHERE   A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID                                                  \n");
		    sql.append(" AND     B.PREPAY_ID = C.KEY_COL2                                                               \n");
		    sql.append(" AND     A.DEPT_CD = D.EBID_SITE_CD(+)                                                          \n");
		    sql.append(" AND     B.CON_CUR_CD = E.DETAILCD(+)                                            				\n");
		    sql.append(" AND     A.ACCT_GB = F.DETAILCD(+) 																\n");
		    sql.append(" AND     B.PREPAY_ID = '"+vo.get("KEY_COL2")+"'                                                 \n");
            
        	search.setStatement(sql.toString());
        	//search.addParameter(vo.get("KEY_COL3"));
        	listRowSet = search.execute();
        	selectResult = RowSetUtility.getValueObject(listRowSet.getRow(0));
        	
        	doJipge(connection, selectResult);

        	String userId = selectResult.get("ACCT_ID");
        	RowSet result = getListRow1(connection, selectResult);

        	for( int i = 0 ; i < result.size() ; i++ ) {
            	Row row = result.getRow(i);

            	//외주선급금 PDF작성
    			preInvoiceDoc020t(connection, row.get("TMPSLIP_ID"), userId);
        	}
        	
        	boolean erpSucc = false;
        	
        	selectResult.set("DOC_ID",vo.get("DOC_ID"));
        	
        	String sqPrepay = getSqPrepay(selectResult);
        	
        	selectResult.set("SQ_PREPAY", sqPrepay);
        	
        	erpSucc = InterfaceOutERPiU.insertPrepayInfo(selectResult, signUserInfo);
        	
        	if(!erpSucc) {
        		connection.rollback();
        		throw new Exception("ERPIU insert Error");
        	}
    	}
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> prepaymentMiddle() : " + e.getMessage());
            //WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
    
    
    public ValueObject getListRow(Connection connection, ValueObject vo) throws Exception {
        
    	Persistent pSelect = null;
        StringBuffer sql = new StringBuffer();
        ValueObject selectResult = new ValueObject();
        
        try {
        	
        	pSelect = new PersistentImpl(connection);
        	
        	sql.append("            SELECT MAIN.TMPSLIP_ID                                        -- 외주기성전표 ID              	\n");
        	sql.append("                 , TO_CHAR(MAIN.PROOFDATE, 'YYYY-MM-DD')    PROOFDATE   -- 거래일자                                               	\n");
        	sql.append("                 , MAIN.DEPT_CD                                         -- 현장코드                                                 	\n");
        	sql.append("                 , CONTRACT.DEPT_NAME                                    -- 현장명                                                   	\n");
        	sql.append("                 , CONTRACT.CON_DOC_NO                                    -- 외주계약번호                                	\n");
        	sql.append("                 , CONTRACT.BUILD_NAME                                    -- 계약명                                               	\n");
        	sql.append("                 , MAIN.DCERTI_NO                                       -- 사업자번호                                          	\n");
        	sql.append("                 , SUBVENDOR.CORP_KOR                                    -- 사업자명                                                	\n");
        	sql.append("                 , SUBVENDOR.CORP_ID                                    -- 협력업체_ID                  	\n");
        	sql.append("                 , MAIN.PREREQTFLG                                      -- 선급/기성구분                                            \n");
        	sql.append("                 , MAIN.ACMP_NO                                         -- 기성차수                                                 	\n");
        	sql.append("                 , MAIN.PROOF_CLASS                                     -- 증빙구분                                                	\n");
        	sql.append("                 , MAIN.ORDACCDESC                                      -- 적요                                                     		\n");
        	sql.append("                 , MAIN.CON_CUR_CD                                      -- 계약통화                                                	\n");
        	sql.append("                 , MAIN.SUPPLY_AMT                                      -- 공급가                                                   		\n");
        	sql.append("                 , MAIN.SURTAX_AMT                                      -- 부가세                                                   		\n");
        	sql.append("                 , MAIN.SUPPLY_AMT + MAIN.SURTAX_AMT        REQ_AMT        -- 청구금액                                             	\n");
        	sql.append("                 , CONTRACT.CON_CHARGE_ID                                -- 담당자 ID                    \n");
        	sql.append("                 , CONTRACT.CON_CHARGE_NAME                                -- 담당자 명                                             	\n");
        	sql.append("                 , MAIN.DEALR_CD                                        -- 거래처코드                                               	\n");
        	sql.append("                 , MAIN.PAYITEM                                         -- PAY ITEM                     \n");
        	sql.append("                 , MAIN.COSTCODE                                        -- COST CODE                    \n");
        	sql.append("                 , MAIN.COSTTYPE                                        -- COST TYPE                    \n");
        	sql.append("                 , MAIN.ACC_CLASS                                       -- 계정구분                                                 	\n");
        	sql.append("                 , MAIN.PAY_GROUP                                       -- PAY_GROUP                    \n");
        	sql.append("                 , MAIN.PAYMENT_TERMS                                   -- PAYMENT_TERMS                \n");
        	sql.append("                 , MAIN.TMPSLIP_DEPT                                    -- 작성부서                                                 	\n");
        	sql.append("                 , MAIN.TMPSLIP_DT                                      -- 작성일자                                                 	\n");
        	sql.append("                 , MAIN.TMPSLIP_NO                                      -- 작성번호                                                 	\n");
        	sql.append("                 , MAIN.TMPSLIP_YN                                      -- 전표생성유무                                              \n");
        	sql.append("                 , MAIN.SLIP_YN                                         -- 결의서작성유무                                          \n");
        	sql.append("                 , MAIN.ACMP_ID                                         -- 기성ID                       \n");
        	sql.append("                 , MAIN.PREPAY_ID                                       -- 선급ID                       \n");
        	sql.append("                 , PREPAY.CON_CORP_ID                                    -- 계약업체 ID                  \n");
        	sql.append("                 , CONTRACT.SUB_CONT_ID                                    -- 외주게약서 ID              \n");
        	sql.append("                  , CASE                                                                                \n");
        	sql.append("                        WHEN (SLIP.STATUS_CD = '1') AND (SLIP.RETURN_CD = 'Y') THEN '-1'                \n");
        	sql.append("                        ELSE SLIP.STATUS_CD                                                             \n");
        	sql.append("                      END                                      STATUS_CD                                \n");
        	sql.append("                                                                                                        \n");
        	sql.append("              FROM SUB1050T            MAIN                                                             \n");
        	sql.append("                 , SUB1030T           PREPAY                                                       		\n");
        	sql.append("                 , SUB1020T          SUBVENDOR                                                     		\n");
        	sql.append("                 , SUB1000T        CONTRACT                                                        		\n");
        	sql.append("                 , SAC01001        SLIP                                                                 \n");
        	sql.append("             WHERE PREREQTFLG     = 'PREPAY'                                                            \n");
        	sql.append("               AND MAIN.PREPAY_ID = PREPAY.PREPAY_ID                                                    \n");
        	sql.append("               AND PREPAY.CON_CORP_ID = SUBVENDOR.CON_CORP_ID                                           \n");
        	sql.append("               AND PREPAY.SUB_CONT_ID = CONTRACT.SUB_CONT_ID                                            \n");
        	sql.append("               AND MAIN.TMPSLIP_DEPT = SLIP.TMPSLIP_DEPT(+)                                             \n");
        	sql.append("               AND MAIN.TMPSLIP_DT     = SLIP.TMPSLIP_DT(+)                                             \n");
        	sql.append("               AND MAIN.TMPSLIP_NO      = SLIP.TMPSLIP_NO(+)                                            \n");
        	sql.append("               AND SUBVENDOR.MAIN_CORP = 'Y'                                                            \n");
        	sql.append("			   AND MAIN.PREPAY_ID = "+vo.get("PREPAY_ID")+"												\n");
        	sql.append("  ORDER BY PROOFDATE desc, DEPT_NAME, CON_DOC_NO, CORP_KOR       										\n");     	
        	
        	System.out.println(sql.toString());
        	pSelect.setStatement(sql.toString());
        	selectResult = RowSetUtility.getValueObject(pSelect.query().getRow(0));
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getListRow() : \n" + e.getMessage());
            //WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
            if( selectResult == null ) selectResult = new ValueObject();
        }
        
        return selectResult;
    }

    public RowSet getListRow1(Connection connection, ValueObject vo) throws Exception {
        
    	Persistent pSelect = null;
        StringBuffer sql = new StringBuffer();
        RowSet resultRowset = null;
        
        try {
        	
        	pSelect = new PersistentImpl(connection);
        	
        	sql.append("            SELECT MAIN.TMPSLIP_ID                                        -- 외주기성전표 ID              	\n");
        	sql.append("                 , TO_CHAR(MAIN.PROOFDATE, 'YYYY-MM-DD')    PROOFDATE   -- 거래일자                                               	\n");
        	sql.append("                 , MAIN.DEPT_CD                                         -- 현장코드                                                 	\n");
        	sql.append("                 , CONTRACT.DEPT_NAME                                    -- 현장명                                                   	\n");
        	sql.append("                 , CONTRACT.CON_DOC_NO                                    -- 외주계약번호                                	\n");
        	sql.append("                 , CONTRACT.BUILD_NAME                                    -- 계약명                                               	\n");
        	sql.append("                 , MAIN.DCERTI_NO                                       -- 사업자번호                                          	\n");
        	sql.append("                 , SUBVENDOR.CORP_KOR                                    -- 사업자명                                                	\n");
        	sql.append("                 , SUBVENDOR.CORP_ID                                    -- 협력업체_ID                  	\n");
        	sql.append("                 , MAIN.PREREQTFLG                                      -- 선급/기성구분                                            \n");
        	sql.append("                 , MAIN.ACMP_NO                                         -- 기성차수                                                 	\n");
        	sql.append("                 , MAIN.PROOF_CLASS                                     -- 증빙구분                                                	\n");
        	sql.append("                 , MAIN.ORDACCDESC                                      -- 적요                                                     		\n");
        	sql.append("                 , MAIN.CON_CUR_CD                                      -- 계약통화                                                	\n");
        	sql.append("                 , MAIN.SUPPLY_AMT                                      -- 공급가                                                   		\n");
        	sql.append("                 , MAIN.SURTAX_AMT                                      -- 부가세                                                   		\n");
        	sql.append("                 , MAIN.SUPPLY_AMT + MAIN.SURTAX_AMT        REQ_AMT        -- 청구금액                                             	\n");
        	sql.append("                 , CONTRACT.CON_CHARGE_ID                                -- 담당자 ID                    \n");
        	sql.append("                 , CONTRACT.CON_CHARGE_NAME                                -- 담당자 명                                             	\n");
        	sql.append("                 , MAIN.DEALR_CD                                        -- 거래처코드                                               	\n");
        	sql.append("                 , MAIN.PAYITEM                                         -- PAY ITEM                     \n");
        	sql.append("                 , MAIN.COSTCODE                                        -- COST CODE                    \n");
        	sql.append("                 , MAIN.COSTTYPE                                        -- COST TYPE                    \n");
        	sql.append("                 , MAIN.ACC_CLASS                                       -- 계정구분                                                 	\n");
        	sql.append("                 , MAIN.PAY_GROUP                                       -- PAY_GROUP                    \n");
        	sql.append("                 , MAIN.PAYMENT_TERMS                                   -- PAYMENT_TERMS                \n");
        	sql.append("                 , MAIN.TMPSLIP_DEPT                                    -- 작성부서                                                 	\n");
        	sql.append("                 , MAIN.TMPSLIP_DT                                      -- 작성일자                                                 	\n");
        	sql.append("                 , MAIN.TMPSLIP_NO                                      -- 작성번호                                                 	\n");
        	sql.append("                 , MAIN.TMPSLIP_YN                                      -- 전표생성유무                                              \n");
        	sql.append("                 , MAIN.SLIP_YN                                         -- 결의서작성유무                                          \n");
        	sql.append("                 , MAIN.ACMP_ID                                         -- 기성ID                       \n");
        	sql.append("                 , MAIN.PREPAY_ID                                       -- 선급ID                       \n");
        	sql.append("                 , PREPAY.CON_CORP_ID                                    -- 계약업체 ID                  \n");
        	sql.append("                 , CONTRACT.SUB_CONT_ID                                    -- 외주게약서 ID              \n");
        	//sql.append("                  , CASE                                                                                \n");
        	//sql.append("                        WHEN (SLIP.STATUS_CD = '1') AND (SLIP.RETURN_CD = 'Y') THEN '-1'                \n");
        	//sql.append("                        ELSE SLIP.STATUS_CD                                                             \n");
        	//sql.append("                      END                                      STATUS_CD                                \n");
        	sql.append("                                                                                                        \n");
        	sql.append("              FROM SUB1050T            MAIN                                                             \n");
        	sql.append("                 , SUB1030T           PREPAY                                                       		\n");
        	sql.append("                 , SUB1020T          SUBVENDOR                                                     		\n");
        	sql.append("                 , SUB1000T        CONTRACT                                                        		\n");
        	//sql.append("                 , SAC01001        SLIP                                                                 \n");
        	sql.append("             WHERE PREREQTFLG     = 'PREPAY'                                                            \n");
        	sql.append("               AND MAIN.PREPAY_ID = PREPAY.PREPAY_ID                                                    \n");
        	sql.append("               AND PREPAY.CON_CORP_ID = SUBVENDOR.CON_CORP_ID                                           \n");
        	sql.append("               AND PREPAY.SUB_CONT_ID = CONTRACT.SUB_CONT_ID                                            \n");
        	//sql.append("               AND MAIN.TMPSLIP_DEPT = SLIP.TMPSLIP_DEPT(+)                                             \n");
        	//sql.append("               AND MAIN.TMPSLIP_DT     = SLIP.TMPSLIP_DT(+)                                             \n");
        	//sql.append("               AND MAIN.TMPSLIP_NO      = SLIP.TMPSLIP_NO(+)                                            \n");
        	sql.append("               AND SUBVENDOR.MAIN_CORP = 'Y'                                                            \n");
        	sql.append("			   AND MAIN.PREPAY_ID = "+vo.get("PREPAY_ID")+"												\n");
        	//sql.append("			   AND SLIP.STATUS_cd IS null																\n");
        	sql.append("			   AND MAIN.TMPSLIP_YN = 'N'																\n");
        	sql.append("  ORDER BY PROOFDATE desc, DEPT_NAME, CON_DOC_NO, CORP_KOR       										\n");     	
        	
        	System.out.println(sql.toString());
        	pSelect.setStatement(sql.toString());
        	//selectResult = RowSetUtility.getValueObject(pSelect.query().getRow(0));
            
        	resultRowset = pSelect.query();
        	
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getListRow() : \n" + e.getMessage());
            //WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            
        } finally {
            if( resultRowset == null ) resultRowset = null;
        }
        
        return resultRowset;
    }
    
    private void doJipge(Connection connection, ValueObject vo) throws Exception {
    	
    	Persistent persistent  = new PersistentImpl(connection);
    	StringBuffer sql   	= new StringBuffer();
    	prepareSql(sql);
        persistent.setStatement(sql.toString());

        try {

        	//ValueObject user = storage.getUser();
        	persistent.addParameter(vo.get("ACCT_ID"));
        	persistent.addParameter(vo.get("dept_cd"));
        	persistent.addParameter(vo.get("PREPAY_ID"));
        	persistent.addParameter(vo.get("ACCT_ID"));
        	persistent.addParameter(vo.get("dept_cd"));
        	persistent.addParameter(vo.get("PREPAY_ID"));
        	
        	
        	System.out.println("1="+vo.get("ACCT_ID"));
        	System.out.println("2="+vo.get("dept_cd"));
        	System.out.println("3="+vo.get("PREPAY_ID"));
        	System.out.println("4="+vo.get("ACCT_ID"));
        	System.out.println("5="+vo.get("dept_cd"));
        	System.out.println("6="+vo.get("PREPAY_ID"));
        	
    		persistent.execute();
			persistent.clearParameters();	
            
        } catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> doJipge() : " + e.getMessage());
			WAFLogger.error(e);
			throw e;
            
        }
        
    }

    //면세
	private void prepareSql(StringBuffer sql) {
		
		sql.append("	   INSERT INTO SUB1050T(TMPSLIP_ID, PROOFDATE, DEPT_CD, PREREQTFLG, ACMP_NO, PROOF_CLASS, ORDACCDESC, SUPPLY_AMT, SURTAX_AMT, DEALR_CD, DCERTI_NO, TAX_DATE, ELECTAX_YN, PAYITEM, COSTCODE, COSTTYPE, ACC_CLASS, PAY_GROUP, PAYMENT_TERMS, TMPSLIP_DEPT, TMPSLIP_DT, TMPSLIP_NO, TMPSLIP_YN, SLIP_YN, CON_CUR_CD, ACMP_ID, PREPAY_ID, CRE_BY, CRE_DATE, UPD_BY, UPD_DATE, CORP_ID, TAX_GB)			\n");
		sql.append("			SELECT SUB1050T_S.NEXTVAL , A.*         						     																			\n");
		sql.append("        			 FROM (                                                                                                                                 \n");
		sql.append("              	      SELECT    TO_DATE(MAIN.TAX_DY,'YYYYMMDD') AS                            	PROOFDATE                                                  \n");                          
		sql.append("                           , F_GET_R_DEPT_CD(CONTRACT.DEPT_CD)	DEPT_CD  --2015.11.5 본코드 대체                           	        	                                \n");                
		sql.append("                           , 'PREPAY'                                       PREREQTFLG                                                                      \n");
		sql.append("                           , 0                                              ACMP_NO                                                                         \n");
		sql.append("                           , CONTRACT.PROOF_CD                          		PROOF_CLASS                                                                 \n");             
		sql.append("                           , TO_CHAR(MAIN.PREPAY_DATE, 'YYMMDD') || ' 외주선급전표(' || CONTRACT.BUILD_NAME     || ')'        ORDACCDESC                      \n");
		sql.append("                           , MAIN.REQ_PREPAY_SUPPLY_TAMT                    SUPPLY_AMT                                                                      \n");  
		sql.append("                           , MAIN.REQ_PREPAY_SURTAX_AMT                     SURTAX_AMT                                                                      \n");       
		sql.append("                           , F_GET_DEALR_CD_EBID(SUBVENDOR.CORP_ID)         DEALR_CD                                                                        \n");
		sql.append("                           , F_GET_DEALR_NO_EBID(SUBVENDOR.CORP_ID)         DCERTI_NO                                                                       \n");
		sql.append("                           ,   TO_DATE(MAIN.TAX_DY,'YYYYMMDD') AS                              TAX_DATE                                                     \n");
		sql.append("                           , 'Y'                           ELECTAX_YN                                                           				           \n");
		sql.append("                           , CONTRACT.PAY_ITEM                              PAYITEM                                                                         \n");
		sql.append("                           , CONTRACT.COST_CODE                             COSTCODE                                                                        \n");
		sql.append("                           , CONTRACT.COST_TYPE                             COSTTYPE                                                                        \n");
		sql.append("                           , CONTRACT.ACCT_GB                               ACC_CLASS                                                                       \n");
		sql.append("                           , CASE SAUP.AC_UNIT_CD                                                                                                           \n");
		sql.append("                                  WHEN '10'    THEN '22'                                                                                                    \n");
		sql.append("                                  ELSE              '21'                                                                                                    \n");
		sql.append("                             END                                            PAY_GROUP                                                                       \n");
		sql.append("                           , CONTRACT.PAYMENT_TERMS                         PAYMENT_TERMS                                                                   \n");
		sql.append("                           , NULL                                           TMPSLIP_DEPT                                                                    \n");
		sql.append("                           , NULL                                           TMPSLIP_DT                                                                      \n");
		sql.append("                           , NULL                                           TMPSLIP_NO                                                                      \n");
		sql.append("                           , 'N'                                            TMPSLIP_YN                                                                      \n");
		sql.append("                           , 'N'                                            SLIP_YN                                                                         \n");
		sql.append("                           , MAIN.CON_CUR_CD                                CON_CUR_CD                                                                      \n");
		sql.append("                           , NULL                                           ACMP_ID                                                                         \n");
		sql.append("                           , MAIN.PREPAY_ID                                 PREPAY_ID                                                                       \n");
		sql.append("                           , ?                                     		    CRE_BY                                                                          \n");
		sql.append("                           , SYSDATE                                        CRE_DATE                                                                        \n");
		sql.append("                           , NULL                                           UPD_BY                                                                          \n");
		sql.append("                           , NULL                                           UPD_DATE                                                                        \n");
		sql.append("                           , SUBVENDOR.CORP_ID                              CORP_ID                                                                         \n");
		sql.append("                           , 'T'                             				TAX_GB                                                                         \n");
		sql.append("                        FROM SUB1030T            MAIN                                                                                                       \n");
		sql.append("                           , SUB1020T            SUBVENDOR                                                                                                  \n");
		sql.append("                           , SUB1000T            CONTRACT                                                                                                   \n");
		sql.append("                           , ETEC_ERPIF.ACZ10100            SAUP                                                                                                       \n");
		sql.append("                       WHERE 1=1                                                                                            \n");
		sql.append("                         AND MAIN.CON_CORP_ID    = SUBVENDOR.CON_CORP_ID                                                                                    \n");
		sql.append("                         AND MAIN.SUB_CONT_ID   = CONTRACT.SUB_CONT_ID                                                                                      \n");
		sql.append("                         AND CONTRACT.DEPT_CD = SAUP.DEPT_CD                                                                                                \n");
		sql.append("                         AND SUBVENDOR.MAIN_CORP = 'Y'                                                                                                      \n");
		sql.append("                         AND CONTRACT.DEPT_CD     = ?                                                                                                  		\n");
		sql.append("                         AND MAIN.STATUS_CD = '10'                                                                                                           \n");                    
		sql.append("                         AND MAIN.PREPAY_ID = ?                                                                                                           \n");                    
		sql.append("                         AND MAIN.PREPAY_SUPPLY_TAMT > 0                                                                                                    \n");
		sql.append("                       UNION ALL                                                                                                                            \n");
		sql.append("                      SELECT   TO_DATE(MAIN.TAX_DY,'YYYYMMDD') AS                           	PROOFDATE                                                                       \n");                          
		sql.append("                           , F_GET_R_DEPT_CD(CONTRACT.DEPT_CD )                	DEPT_CD  --2015.11.5 본코드대체                                                                   \n");                
		sql.append("                           , 'PREPAY'                                       PREREQTFLG                                                                      \n");
		sql.append("                           , 0                                              ACMP_NO                                                                         \n");
		sql.append("                           , DECODE(CONTRACT.PROOF_CD,'21','26', CONTRACT.PROOF_CD)                          		PROOF_CLASS                                                                 \n");             
		sql.append("                           , TO_CHAR(MAIN.PREPAY_DATE, 'YYMMDD') || ' 외주선급전표(' || CONTRACT.BUILD_NAME     || ')'            ORDACCDESC                 \n");
		sql.append("                           , MAIN.REQ_PREPAY_SUPPLY_NAMT                    SUPPLY_AMT                                                                      \n");  
		sql.append("                           , 0						                        SURTAX_AMT                                                                      \n");       
		sql.append("                           , F_GET_DEALR_CD_EBID(SUBVENDOR.CORP_ID)         DEALR_CD                                                                        \n");
		sql.append("                           , F_GET_DEALR_NO_EBID(SUBVENDOR.CORP_ID)         DCERTI_NO                                                                       \n");
		sql.append("                           ,   TO_DATE(MAIN.TAX_DY,'YYYYMMDD') AS                              TAX_DATE                                                     \n");
		sql.append("                           , 'Y'                           ELECTAX_YN                                                                      					\n");
		sql.append("                           , CONTRACT.PAY_ITEM                              PAYITEM                                                                         \n");
		sql.append("                           , CONTRACT.COST_CODE                             COSTCODE                                                                        \n");
		sql.append("                           , CONTRACT.COST_TYPE                             COSTTYPE                                                                        \n");
		sql.append("                           , CONTRACT.ACCT_GB                               ACC_CLASS                                                                       \n");
		sql.append("                           , CASE SAUP.AC_UNIT_CD                                                                                                           \n");
		sql.append("                                  WHEN '10'    THEN '22'                                                                                                    \n");
		sql.append("                                  ELSE              '21'                                                                                                    \n");
		sql.append("                             END                                            PAY_GROUP                                                                       \n");
		sql.append("                           , CONTRACT.PAYMENT_TERMS                         PAYMENT_TERMS                                                                   \n");
		sql.append("                           , NULL                                           TMPSLIP_DEPT                                                                    \n");
		sql.append("                           , NULL                                           TMPSLIP_DT                                                                      \n");
		sql.append("                           , NULL                                           TMPSLIP_NO                                                                      \n");
		sql.append("                           , 'N'                                            TMPSLIP_YN                                                                      \n");
		sql.append("                           , 'N'                                            SLIP_YN                                                                         \n");
		sql.append("                           , MAIN.CON_CUR_CD                                CON_CUR_CD                                                                      \n");
		sql.append("                           , NULL                                           ACMP_ID                                                                         \n");
		sql.append("                           , MAIN.PREPAY_ID                                 PREPAY_ID                                                                       \n");
		sql.append("                           , ?                                          	CRE_BY                                                                          \n");
		sql.append("                           , SYSDATE                                        CRE_DATE                                                                        \n");
		sql.append("                           , NULL                                           UPD_BY                                                                          \n");
		sql.append("                           , NULL                                           UPD_DATE                                                                        \n");
		sql.append("                           , SUBVENDOR.CORP_ID                              CORP_ID                                                                         \n");
		sql.append("                           , 'N'                              				TAX_GB                                                                         \n");
		sql.append("                        FROM SUB1030T            MAIN                                                                                                       \n");
		sql.append("                           , SUB1020T            SUBVENDOR                                                                                                  \n");
		sql.append("                           , SUB1000T            CONTRACT                                                                                                   \n");
		sql.append("                           , ETEC_ERPIF.ACZ10100            SAUP                                                                                                       \n");
		sql.append("                       WHERE 1=1                                                                                            \n");
		sql.append("                         AND MAIN.CON_CORP_ID    = SUBVENDOR.CON_CORP_ID                                                                                    \n");
		sql.append("                         AND MAIN.SUB_CONT_ID   = CONTRACT.SUB_CONT_ID                                                                                      \n");
		sql.append("                         AND CONTRACT.DEPT_CD = SAUP.DEPT_CD                                                                                                \n");
		sql.append("                         AND SUBVENDOR.MAIN_CORP = 'Y'                                                                                                      \n");
		sql.append("                         AND CONTRACT.DEPT_CD     = ?                                                                                                  		\n");
		sql.append("                         AND MAIN.STATUS_CD = '10'                                                                                                           \n");
		sql.append("                         AND MAIN.PREPAY_ID = ?                                                                                                           \n");
		sql.append("                         AND MAIN.PREPAY_SUPPLY_NAMT > 0                                                                                                    \n");
		sql.append("             )  A																																			\n");																	
		sql.append("       																																					\n");																	
		
		System.out.println(sql.toString());
	}
	


    private void preInvoiceDoc020t(Connection con, String tmpslipID, String userID) throws Exception {

        String strResult = null;
        String message = "";
		String SPStateMent = "";
        CallableStatement cs = null;
        String arrDocNm [] = {"외주선급금계산서"};
        String arrProofGu [] = {"18"};
        
        //WAFLogger.debug("\n\t #################### params = [\n"+ params +"\n");
        
        try {
        	
        	// 증빙정보 생성 프로시저 
        	// - 외주 기성고 계산서만 새로 생성해야만 한다.
			SPStateMent = "{call SAC_DOCUMENTATIVE.prc_subinvoicebiz4TypeA(?, ?, ?, ?, ?)}";
			
            cs = con.prepareCall(SPStateMent);

            // 라인당, 기성고계산서 한라인만 생성.
            for(int iCount=0; iCount<arrDocNm.length; iCount++) {
            	
	            int i = 1;
	            cs.setString(i++, tmpslipID);
	            cs.setInt(i++, Integer.parseInt(userID));
	            cs.setString(i++, arrDocNm[iCount]);
	            cs.setString(i++, arrProofGu[iCount]);
	            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 생성 
				SACDOCreatePDF6 sacDOCreatePDF6 = new SACDOCreatePDF6(); 
				sacDOCreatePDF6.doBiz1(con, outputStream, tmpslipID ,  null);
            }
            // 라인당, 기성고계산서 한라인만 생성. - 종료 
        }
        catch(Exception e) {
            //super.doException(e);
            
            message = this.getClass().getName() + "\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 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;
		} 
	}
	
	
	public String getSqPrepay(ValueObject vo) throws Exception {
        
		Search search = new SearchImpl();
    	RowSet rowSet = new RowSetImpl();
    	String sqPrepay = "";
    	StringBuffer sqlstr = new StringBuffer();
        
        try {
        	
        	sqlstr.append("SELECT  TO_CHAR(NVL(MAX(SQ_PREPAY),0) + 1,'FM000') AS SQ_PREPAY	\n");
        	sqlstr.append("FROM    WC_O_PREPAY@ERPIU										\n");
        	sqlstr.append("WHERE   CD_COMPANY = ?											\n");
        	sqlstr.append("AND     CD_SITE = ?												\n");
        	sqlstr.append("AND     CD_CM00215 = ?											\n");
        	sqlstr.append("AND     NO_CONT = ?												\n");

        	search.setStatement(sqlstr.toString());
        	search.addParameter(vo.get("CD_COMPANY"));
        	search.addParameter(vo.get("CD_SITE"));
        	search.addParameter(vo.get("CD_CM00215"));
        	search.addParameter(vo.get("NO_CONT"));
        	rowSet = search.execute();
        	
        	if( rowSet != null && rowSet.size() > 0 ) {
        		sqPrepay = rowSet.getRow(0).get("SQ_PREPAY");
        	}else {
        		throw new Exception("ERPIU SQ_PREPAY 채번 실패");	
        	}
        	
        	return sqPrepay;
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getSqPrepay() : \n" + e.getMessage());
            //WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            throw e;
        }
    }

	
}