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

/**
 * @(#)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.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.SACDOCreatePDF7;
import kr.co.udapsoft.common.commonSign.util.SACDOCreatePDF8;
import weblogic.jdbc.vendor.oracle.OracleThinBlob;

public class PrepaymentMiddleSignDriver implements CommonSignInterface {

    /**
     * 
     */
    public PrepaymentMiddleSignDriver() {
        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"));
        
        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_COL2"));
			signUser.set("ACCSLIP_NO", 	"");
				
			signUser.set("SLIP_DT", 		signDoc.get("KEY_COL4"));
			signUser.set("SLIP_NO", 		signDoc.get("KEY_COL5"));
			
        
        	insertSign002t(connection, signUser);
        }
    	updateSUB1040T(connection, signDoc);
    	
    	
    }
    
    /* 
     * (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");
    	
        updateSUB1040T(connection, signDoc);
    }

    /* 
     * (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 {
    	System.out.println("5");
    	updateSUB1040T(connection, signDoc);
    }
    /* 
     * (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 {
    	System.out.println("6");
    	updateSUB1040T(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("         ) 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");
			
			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() + " --> insertSign002t() : " + e.getMessage());
			WAFLogger.error("SQL : " + sqlstr);
			WAFLogger.error(e);
			throw e;
		}
    }
	
    public void updateSUB1040T(Connection connection, ValueObject vo) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StringBuffer sqlstr = null;
        try {
        	sqlstr = new StringBuffer();
        	
        	
        	sqlstr.append(" UPDATE SUB1040T            		\n");
        	sqlstr.append("    SET STATUS_CD	= ?    		\n");        	
        	sqlstr.append("      , UPD_DATE		= SYSDATE   \n");
        	sqlstr.append("  WHERE ACMP_ID = ?				\n");
    		
    		persistent.setStatement(sqlstr.toString());
    		persistent.addParameter("10");	// 완료
            persistent.addParameter(vo.get("KEY_COL1"));
            persistent.execute();
            
            //대급지급 신청서(중도) 전표집계
            prepaymentMiddle(connection, vo);
	            
        }catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> updateSUB1040T() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
    
    public void prepaymentMiddle(Connection connection, ValueObject vo) throws Exception {
    	
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
        ValueObject selectResult = new ValueObject();
        
        try {
        	sql.append("	SELECT B.DEPT_CD ,(SELECT ACCT_ID FROM PUB0120T WHERE USER_SABUN = '"+vo.get("APPROVAL_SABUN")+"') as ACCT_ID, B.ACMP_ID	\n");  //2015.9.1 a.dept_cd->b.dept_cd로 변경. P158->1631
        	sql.append("	FROM SUB1000T A,									\n");
        	sql.append("		 SUB1040T B										\n");
        	sql.append("    WHERE	A.SUB_CONT_ID = B.SUB_CONT_ID				\n");
        	sql.append("	AND		B.ACMP_ID = "+vo.get("KEY_COL1")+"			\n");
            
        	search.setStatement(sql.toString());
        	listRowSet = search.execute();
        	selectResult = RowSetUtility.getValueObject(listRowSet.getRow(0));
        	
        	doJipge(connection, selectResult);
        	
        	RowSet result = getListRow(connection, selectResult);
        	//result.set("ACCT_ID", selectResult.getObject("ACCT_ID"));
        	String acctId = selectResult.get("ACCT_ID");

        	for( int i = 0 ; i < result.size() ; i++ ) {
        		Row row = result.getRow(i);
        		//외주기성고 PDF작성
				prcDoc020t(connection, row.get("TMPSLIP_ID"), acctId, selectResult.get("ACMP_ID"), row.get("END_YN"));
        	}
        	
    	}catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> prepaymentMiddle() : " + e.getMessage());
            //WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }
    
    public RowSet getListRow(Connection connection, ValueObject vo) throws Exception {
        
    	Persistent persistent_c = null;
    	
    	Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
        
        try {
        	
        	persistent_c = new PersistentImpl(connection);
        	
        	sql.append("			SELECT MAIN.ACMP_ID                                         -- 기성ID               			        \n");
        	sql.append("				 , '' 										CHK			-- 										\n");
			sql.append("				 , 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.PREPAY_ID                                       -- 선급ID            		   	        \n");
        	sql.append("				 , ACMP.CON_CORP_ID										-- 계약업체 ID  		        	        \n");
        	sql.append("				 , ACMP.ACMP_YYYYMM										-- 기성년월        			   		    \n");
        	sql.append("				 , ACMP.ACMP_NO								CHASU		-- 차수          				   		 	\n");
        	sql.append("				 , ACMP.TAX_METHOD_CD 										          				   		 	\n");
        	sql.append("				 , CONTRACT.SUB_CONT_ID									-- 외주게약서 ID              	    	\n");
            sql.append("     			 , CASE 																						\n");
            sql.append("            			WHEN ACMP.TOT_FUND_AMT >= ACMP.CON_SUPPLY_AMT  THEN 'J'    								\n");                              
            sql.append("            			ELSE 'K'                                                 								\n");
            sql.append("       			   END                              		END_YN	                     						\n");
        	sql.append("			  FROM SUB1050T			MAIN								-- << 집계 >>         		  	       	\n");
        	sql.append("   LEFT OUTER JOIN SUB1040T			ACMP								-- << 기성내역 >>      		  	\n");
        	sql.append("				ON MAIN.ACMP_ID = ACMP.ACMP_ID				                            				       	\n");
        	sql.append("   LEFT OUTER JOIN SUB1020T		SUBVENDOR							-- << 계약업체 >>       			   	\n");
        	sql.append("				ON ACMP.CON_CORP_ID = SUBVENDOR.CON_CORP_ID					                			      	\n");
        	sql.append("   LEFT OUTER JOIN SUB2000T			CONTRACT							-- << 계약서 >>     		       	\n");
        	sql.append("				ON ACMP.SUB_CONT_ID = CONTRACT.SUB_CONT_ID			                         		          	\n");
			sql.append("   			   AND ACMP.MOD_NO	 = CONTRACT.MOD_NO                                            			     	\n");
        	sql.append("			 WHERE PREREQTFLG IN ('ACMP', 'PREPAY_DED') -- PREPAY:선급, ACMP:기성, PREPAY_DED: 선급금공제   		\n");
        	sql.append("			 AND MAIN.ACMP_ID = "+vo.get("ACMP_ID")+"													   		\n");
        	sql.append("  ORDER BY PROOFDATE desc, MAIN.ACMP_ID, DEPT_NAME, CON_DOC_NO, CORP_KOR, MAIN.PREREQTFLG        				\n");     	
        	
            System.out.println(sql.toString());
            //search.setStatement(sql.toString());
            //listRowSet = search.execute();
            //selectResult = RowSetUtility.getValueObject(search.execute().getRow(0));
            persistent_c.setStatement(sql.toString());
            listRowSet = persistent_c.query();
            //selectResult = RowSetUtility.getValueObject(listRowSet.getRow(0));
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getListRowSet() : \n" + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            throw e;
            
        } finally {
            if( listRowSet == null ) listRowSet = new RowSetImpl();
        }
        
        return listRowSet;
    }

    private void doJipge(Connection connection, ValueObject vo) throws Exception {
    	
    	Persistent persistent  = new PersistentImpl(connection);
    	Persistent persistent2  = new PersistentImpl(connection);
    	Persistent persistent3  = new PersistentImpl(connection);
    	Persistent persistent4  = new PersistentImpl(connection);
    	
    	Persistent persistent_u  = new PersistentImpl(connection);
    	
    	StringBuffer sql   	= new StringBuffer();
    	StringBuffer sql2  	= new StringBuffer();
    	StringBuffer sql3  	= new StringBuffer();
    	StringBuffer sql4  	= new StringBuffer();
    	
    	StringBuffer sql_u  	= new StringBuffer();
    	
    	/*면세 기성*/
    	prepareSql(sql);
    	/*면세 공제*/
    	prepareSql2(sql2);
    	/*과세 기성*/
    	prepareSql3(sql3);
    	/*과세 공제*/
    	prepareSql4(sql4);
    	
    	updateSql(sql_u);
    	
        persistent.setStatement(sql.toString());
        persistent2.setStatement(sql2.toString());
        persistent3.setStatement(sql3.toString());
        persistent4.setStatement(sql4.toString());
        persistent_u.setStatement(sql_u.toString());

        try {
        	//ValueObject user = storage.getUser();
        	
        	RowSet rs = getListRowSet(connection, vo);
        	
        	if(rs.size() > 0){
        		
        	
	        	for(int i = 0; rs.size() > i; i++){
	        	
		        	// 기성집계 과세
		        	persistent.addParameter(vo.get("ACCT_ID"));
		        	persistent.addParameter(rs.getRow(i).get("ACMP_ID"));
		    		persistent.execute();
					persistent.clearParameters();	
					
					// 선급금 공제 집계 과세 (선급금공제금액이 있는 놈만 집계)
					persistent2.addParameter(vo.get("ACCT_ID"));
		        	persistent2.addParameter(rs.getRow(i).get("ACMP_ID"));
		    		persistent2.execute();
					persistent2.clearParameters();
					
					// 기성집계 면세
					persistent3.addParameter(vo.get("ACCT_ID"));
					persistent3.addParameter(rs.getRow(i).get("ACMP_ID"));
					persistent3.execute();
					persistent3.clearParameters();	
					
					// 선급금 공제 집계 면세 (선급금공제금액이 있는 놈만 집계)
					persistent4.addParameter(vo.get("ACCT_ID"));
					persistent4.addParameter(rs.getRow(i).get("ACMP_ID"));
					persistent4.execute();
					persistent4.clearParameters();
					
					
					// 전표집계유무 상태값 변경
					persistent_u.addParameter(rs.getRow(i).get("ACMP_ID"));
					persistent_u.execute();
					persistent_u.clearParameters();
		            
	        	}
	        	
        	}
			
        } catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> doJipge() : " + e.getMessage());
			WAFLogger.error(e);
			throw e;
            
        }
    }

    /**
     * 면세금
     * @param sql
     */
	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						-- ID          						     																\n");
		sql.append("				 , A.TAX_DY									-- 기성일자                                 																	\n");
		sql.append("				 , A.DEPT_CD								-- 현장코드                                 																	\n");
		sql.append("				 , 'ACMP'									-- 선급기성구분   (ACMP= 기성)      																		\n");
		sql.append("				 , A.ACMP_NO								-- 기성차수                                 																	\n");
		sql.append("				 , DECODE(B.PROOF_CD,'21','26', B.PROOF_CD)								-- 증빙구분                                 									\n");
		sql.append("				 , A.TAX_DY || ' 외주기성전표 - '  || A.ACMP_NO																										\n");
		sql.append("        			|| ' (' || BUILD_NAME || ') '   		-- 적요                                  																		\n");
		sql.append("				 , A.REQU_SUPPLY_NAMT	 					-- 면세	                       																			\n");
		sql.append("				 , 0				 						-- 면세보증금                          																		\n");
		sql.append("				 , F_GET_DEALR_CD_EBID(C.CORP_ID)  AS DEALR_CD 			-- 거래처코드                              	 																\n");
		sql.append("				 , F_GET_DEALR_NO_EBID(C.CORP_ID)  AS DCERTI_NO						-- 사업자번호                               																	\n");
		sql.append("				 , TO_DATE(A.TAX_DY, 'YYYY-MM-DD')                        -- 세금계산서일자                           																	\n");
		sql.append("				 , 'Y'							-- 전자계약여부                             																				\n");
		sql.append("				 , B.PAY_ITEM							-- PAY_ITEM                                 																\n");
		sql.append("				 , B.COST_CODE							-- COST_CODE                               																	\n");
		sql.append("				 , B.COST_TYPE							-- COST_TYPE                                																\n");
		sql.append("				 , B.ACCT_GB								-- 계정구분  (=분개유형 )           																		\n");
		sql.append("				 , B.PAY_GROUP							-- PAY_GROUP                                																\n");
		sql.append("				 , B.PAYMENT_TERMS						-- PAYMENT_TERMS                            																\n");
		sql.append("				 , NULL										-- 작성부서                                 																	\n");
		sql.append("				 , NULL										-- 작성일자                                 																	\n");
		sql.append("				 , NULL										-- 작성번호                                 																	\n");
		sql.append("				 , 'N'										-- 전표생성유무                             																	\n");
		sql.append("				 , 'N'										-- 결의서작성유무                           																	\n");
		sql.append("				 , B.CON_CUR_CD								-- 계약통화                                 																	\n");
		sql.append("				 , A.ACMP_ID								-- 기성ID                                   																\n");
		sql.append("				 , NULL										-- 선급ID                                   																\n");
		sql.append("				 , ?										-- 등록자                                   																	\n");
		sql.append("				 , SYSDATE									-- 등록일시                                 																	\n");
		sql.append("				 , NULL										-- 수정자                                  																	\n");
		sql.append("				 , NULL										-- 수정일시                                 																	\n");
		sql.append("				 , C.CORP_ID								-- 협력업체ID                               																\n");
		sql.append("				 , 'N'								-- 면과세 구분                               																			\n");
		sql.append("			  FROM SUB1040T			A				-- << 기성내역 >>                           																\n");
		sql.append("			       ,SUB2000T 		B				-- << 계약서  >>	                                     													\n");
		sql.append("			       ,SUB1020T 		C				-- << 업체  >>	                                     													\n");
		sql.append("			 WHERE A.SUB_CONT_ID = B.SUB_CONT_ID                                                             														\n");
		sql.append("			   AND A.MOD_NO = B.MOD_NO                                                             																	\n");
		sql.append("			   AND A.SUB_CONT_ID = C.SUB_CONT_ID                                                             														\n");
		sql.append("			   AND A.ACMP_ID 	= ?                                                             																	\n");
		sql.append("			   AND A.REQU_SUPPLY_NAMT > 0 		-- 면세금액이 있음								        																\n");
		sql.append("			   AND A.STATUS_CD = '10'			-- 결제완료된 내역																										\n");
		
		System.out.println(sql.toString());
	}
	
	/**
	 * 선급공제액_면세금액
	 * @param sql
	 */
	private void prepareSql2(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						-- ID          						     	\n");
		sql.append("				 , A.TAX_DY								-- 기성일자                                 					\n");
		sql.append("				 , A.DEPT_CD								-- 현장코드                                 					\n");
		sql.append("				 , 'PREPAY_DED'									-- 선급기성구분   (ACMP= 기성)      				\n");
		sql.append("				 , A.ACMP_NO								-- 기성차수                                 					\n");
		/*제경팀 요청(정승우) 선급금 공제인 경우 증빙구분이 기타로 요청*/
		sql.append("				 , 'Z99' 								-- 증빙구분                                 					\n");
		sql.append("				 , A.TAX_DY || ' 외주선급공제전표 - '  || A.ACMP_NO				\n");
		sql.append("        			|| ' (' || BUILD_NAME || ') '   		-- 적요                                  							\n");
		sql.append("				 , A.PAY_DEDUCT_NAMT	 					-- 선급공제금_면세                      					\n");
		sql.append("				 , 0 						-- 부가세                        				\n");
		sql.append("				 , F_GET_DEALR_CD_EBID(C.CORP_ID)  AS DEALR_CD 			-- 거래처코드                              	 				\n");
		sql.append("				 , F_GET_DEALR_NO_EBID(C.CORP_ID)  AS DCERTI_NO								-- 사업자번호                               					\n");
		sql.append("				 , TO_DATE(A.TAX_DY, 'YYYY-MM-DD')                        -- 세금계산서일자                          					\n");
		sql.append("				 , 'Y'							-- 전자계약여부                             					\n");
		sql.append("				 , B.PAY_ITEM							-- PAY_ITEM                                 \n");
		sql.append("				 , B.COST_CODE							-- COST_CODE                                \n");
		sql.append("				 , B.COST_TYPE							-- COST_TYPE                                \n");
		sql.append("				 , B.ACCT_GB								-- 계정구분  (=분개유형 )           				\n");
		sql.append("				 , CASE E.AC_UNIT_CD WHEN '10' THEN '22'											\n");
		sql.append("            		ELSE '21'																	\n");
		sql.append("     				END 							-- PAY_GROUP                                \n");
		sql.append("				 , B.PAYMENT_TERMS						-- PAYMENT_TERMS                            \n");
		sql.append("				 , NULL										-- 작성부서                                 					\n");
		sql.append("				 , NULL										-- 작성일자                                 					\n");
		sql.append("				 , NULL										-- 작성번호                                 					\n");
		sql.append("				 , 'N'										-- 전표생성유무                             					\n");
		sql.append("				 , 'N'										-- 결의서작성유무                           					\n");
		sql.append("				 , B.CON_CUR_CD								-- 계약통화                                 					\n");
		sql.append("				 , A.ACMP_ID								-- 기성ID                                   	\n");
		sql.append("				 , NULL										-- 선급ID                                   	\n");
		sql.append("				 , ?										-- 등록자                                   					\n");
		sql.append("				 , SYSDATE									-- 등록일시                                 					\n");
		sql.append("				 , NULL										-- 수정자                                  					\n");
		sql.append("				 , NULL										-- 수정일시                                 					\n");
		sql.append("				 , C.CORP_ID								-- 협력업체ID                               	\n");
		sql.append("				 , 'N'								-- 면과세구분                               	\n");
		sql.append("			  FROM SUB1040T			A				-- << 기성내역 >>                           	\n");
		sql.append("			       ,SUB2000T 		B				-- << 계약서  >>	                                     \n");
		sql.append("			       ,SUB1020T 		C				-- << 업체  >>	                                     \n");
		sql.append("			       ,ETEC_ERPIF.ACZ10100 		E				-- << 사업부  >>	                                     \n");
		sql.append("			 WHERE A.SUB_CONT_ID = B.SUB_CONT_ID                                                             	\n");
		sql.append("			   AND A.MOD_NO = B.MOD_NO                                                             	\n");
		sql.append("			   AND A.SUB_CONT_ID = C.SUB_CONT_ID                                                             	\n");
		sql.append("			   AND A.ACMP_ID 	= ?                                                             	\n");
		sql.append("			   AND A.PAY_DEDUCT_NAMT > 0 		-- 선급공제액_면세금액이 있음								        \n");
		sql.append("			   AND A.DEPT_CD = E.DEPT_CD				 										        \n");
		sql.append("			   AND A.STATUS_CD = '10'			-- 결제완료된 내역										\n");
		
		System.out.println(sql.toString());
	}
	
	/**
	 * 과세금액
	 * @param sql
	 */
	private void prepareSql3(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						-- ID          						     	\n");
		sql.append("				 , A.TAX_DY								-- 기성일자                                 					\n");
		sql.append("				 , A.DEPT_CD								-- 현장코드                                 					\n");
		sql.append("				 , 'ACMP'									-- 선급기성구분   (ACMP= 기성)      				\n");
		sql.append("				 , A.ACMP_NO								-- 기성차수                                 					\n");
		sql.append("				 , B.PROOF_CD						-- 증빙구분                                 					\n");
		sql.append("				 , A.TAX_DY || ' 외주기성전표 - '  || A.ACMP_NO			\n");
		sql.append("        			|| ' (' || BUILD_NAME || ') '   		-- 적요                                  							\n");
		sql.append("				 , A.REQU_SUPPLY_TAMT	 					-- 과세	                       					\n");
		sql.append("				 , A.REQU_SURTAX_AMT 						-- 과세보증금                          				\n");
		sql.append("				 , F_GET_DEALR_CD_EBID(C.CORP_ID)  AS DEALR_CD			-- 거래처코드                              	 				\n");
		sql.append("				 , F_GET_DEALR_NO_EBID(C.CORP_ID)  AS DCERTI_NO								-- 사업자번호                               					\n");
		sql.append("				 , TO_DATE(A.TAX_DY, 'YYYY-MM-DD')                        -- 세금계산서일자                           					\n");
		sql.append("				 , 'Y'							-- 전자계약여부                             					\n");
		sql.append("				 , B.PAY_ITEM							-- PAY_ITEM                                 \n");
		sql.append("				 , B.COST_CODE							-- COST_CODE                                \n");
		sql.append("				 , B.COST_TYPE							-- COST_TYPE                                \n");
		sql.append("				 , B.ACCT_GB								-- 계정구분  (=분개유형 )           				\n");
		sql.append("				 , B.PAY_GROUP							-- PAY_GROUP                                \n");
		sql.append("				 , B.PAYMENT_TERMS						-- PAYMENT_TERMS                            \n");
		sql.append("				 , NULL										-- 작성부서                                 					\n");
		sql.append("				 , NULL										-- 작성일자                                 					\n");
		sql.append("				 , NULL										-- 작성번호                                 					\n");
		sql.append("				 , 'N'										-- 전표생성유무                             					\n");
		sql.append("				 , 'N'										-- 결의서작성유무                           					\n");
		sql.append("				 , B.CON_CUR_CD								-- 계약통화                                 					\n");
		sql.append("				 , A.ACMP_ID								-- 기성ID                                   	\n");
		sql.append("				 , NULL										-- 선급ID                                   	\n");
		sql.append("				 , ?										-- 등록자                                   					\n");
		sql.append("				 , SYSDATE									-- 등록일시                                 					\n");
		sql.append("				 , NULL										-- 수정자                                  					\n");
		sql.append("				 , NULL										-- 수정일시                                 					\n");
		sql.append("				 , C.CORP_ID								-- 협력업체ID                               	\n");
		sql.append("				 , 'T'								-- 면과세구분                               	\n");
		sql.append("			  FROM SUB1040T			A				-- << 기성내역 >>                           	\n");
		sql.append("			       ,SUB2000T 		B				-- << 계약서  >>	                                     \n");
		sql.append("			       ,SUB1020T 		C				-- << 업체  >>	                                     \n");
		sql.append("			 WHERE A.SUB_CONT_ID = B.SUB_CONT_ID                                                             	\n");
		sql.append("			   AND A.MOD_NO = B.MOD_NO                                                             	\n");
		sql.append("			   AND A.SUB_CONT_ID = C.SUB_CONT_ID                                                             	\n");
		sql.append("			   AND A.ACMP_ID 	= ?                                                             	\n");
		sql.append("			   AND A.REQU_SUPPLY_TAMT > 0 		-- 과세금액이 있음								        \n");
		sql.append("			   AND A.STATUS_CD = '10'			-- 결제완료된 내역										\n");
	
		System.out.println(sql.toString());
	}
	
	/**
	 * 선급공제액_과세금액
	 * @param sql
	 */
	private void prepareSql4(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						-- ID          						     	\n");
		sql.append("				 , A.TAX_DY                                -- 기성일자                                 					\n");
		sql.append("				 , A.DEPT_CD								-- 현장코드                                 					\n");
		sql.append("				 , 'PREPAY_DED'									-- 선급기성구분   (ACMP= 기성)      				\n");
		sql.append("				 , A.ACMP_NO								-- 기성차수                                 					\n");
		sql.append("				 , 'Z99' 							-- 증빙구분                                 					\n");
		sql.append("				 , A.TAX_DY || '  외주선급공제전표 - '  || A.ACMP_NO			\n");
		sql.append("        			|| ' (' || BUILD_NAME || ') '   		-- 적요                                  							\n");
		sql.append("				 , A.PAY_DEDUCT_TAMT	 					-- 선급공제금_과세                      					\n");
		sql.append("				 , 0 						-- 부가세                        				\n");
		sql.append("				 , F_GET_DEALR_CD_EBID(C.CORP_ID)  AS DEALR_CD		-- 거래처코드                              	 				\n");
		sql.append("				 , F_GET_DEALR_NO_EBID(C.CORP_ID)  AS DCERTI_NO							-- 사업자번호                               					\n");
		sql.append("				 , TO_DATE(A.TAX_DY, 'YYYY-MM-DD')                        -- 세금계산서일자                          					\n");
		sql.append("				 , 'Y'							-- 전자계약여부                             					\n");
		sql.append("				 , B.PAY_ITEM							-- PAY_ITEM                                 \n");
		sql.append("				 , B.COST_CODE							-- COST_CODE                                \n");
		sql.append("				 , B.COST_TYPE							-- COST_TYPE                                \n");
		sql.append("				 , B.ACCT_GB								-- 계정구분  (=분개유형 )           				\n");
		sql.append("				 , CASE E.AC_UNIT_CD WHEN '10' THEN '22'											\n");
		sql.append("            		ELSE '21'																	\n");
		sql.append("     				END 							-- PAY_GROUP                                \n");
		sql.append("				 , B.PAYMENT_TERMS						-- PAYMENT_TERMS                            \n");
		sql.append("				 , NULL										-- 작성부서                                 					\n");
		sql.append("				 , NULL										-- 작성일자                                 					\n");
		sql.append("				 , NULL										-- 작성번호                                 					\n");
		sql.append("				 , 'N'										-- 전표생성유무                             					\n");
		sql.append("				 , 'N'										-- 결의서작성유무                           					\n");
		sql.append("				 , B.CON_CUR_CD								-- 계약통화                                 					\n");
		sql.append("				 , A.ACMP_ID								-- 기성ID                                   	\n");
		sql.append("				 , NULL										-- 선급ID                                   	\n");
		sql.append("				 , ?										-- 등록자                                   					\n");
		sql.append("				 , SYSDATE									-- 등록일시                                 					\n");
		sql.append("				 , NULL										-- 수정자                                  					\n");
		sql.append("				 , NULL										-- 수정일시                                 					\n");
		sql.append("				 , C.CORP_ID								-- 협력업체ID                               	\n");
		sql.append("				 , 'T'								-- 면과세구분                               	\n");
		sql.append("			  FROM SUB1040T			A				-- << 기성내역 >>                           	\n");
		sql.append("			       ,SUB2000T 		B				-- << 계약서  >>	                                     \n");
		sql.append("			       ,SUB1020T 		C				-- << 업체  >>	                                     \n");
		sql.append("			       ,ETEC_ERPIF.ACZ10100 		E				-- << 사업부  >>	                                     \n");
		sql.append("			 WHERE A.SUB_CONT_ID = B.SUB_CONT_ID                                                             	\n");
		sql.append("			   AND A.MOD_NO = B.MOD_NO                                                             	\n");
		sql.append("			   AND A.SUB_CONT_ID = C.SUB_CONT_ID                                                             	\n");
		sql.append("			   AND A.ACMP_ID 	= ?                                                             	\n");
		sql.append("			   AND A.PAY_DEDUCT_TAMT > 0 		-- 선급공제액_과세금액이 있음								        \n");
		sql.append("			   AND A.DEPT_CD = E.DEPT_CD				 										        \n");
		sql.append("			   AND A.STATUS_CD = '10'			-- 결제완료된 내역										\n");
		
		System.out.println(sql.toString());
	}
	
	private void updateSql(StringBuffer sql){
		
		sql.append("	UPDATE SUB1040T SET TRANS_CD = 'Y'						\n");
		sql.append("	WHERE ACMP_ID = ?										\n");
		sql.append("	AND STATUS_CD = '10'									\n");
		
	}

	private RowSet getListRowSet(Connection connection, ValueObject vo) throws Exception {
        RowSet listRowSet = null;
        StringBuffer sql = new StringBuffer();
        
        Persistent persistent = new PersistentImpl(connection);
        
        try {
    		sql.append("				SELECT MAIN.ACMP_ID					-- 기성아이디										\n");
            sql.append("				  FROM SUB1040T		MAIN			-- << 기성내역 >>        		                           	\n");  
            sql.append("				 WHERE MAIN.DEPT_CD 	= ?                                                             	\n");
        	sql.append("				   AND MAIN.TRANS_CD = 'N'									 	                        	\n");
        	sql.append("				   AND MAIN.STATUS_CD = '10'																	\n");			
        	sql.append("				   AND MAIN.ACMP_ID = ?																	\n");			
            
        	persistent.setStatement(sql.toString());
        	
        	persistent.addParameter(vo.get("dept_cd"));
        	persistent.addParameter(vo.get("ACMP_ID"));
        	
            listRowSet = persistent.query();
            
        } catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getListRowSet() : \n" + e.getMessage());
            WAFLogger.error(e);
            throw e;
        } finally {
            if( listRowSet == null ) listRowSet = new RowSetImpl();
        }
        
        return listRowSet;
    }	

    private void prcDoc020t(Connection con, String tmpslipID, String userID, String acmp_id, String endYn) throws Exception {

        String strResult = null;
	
        String message = "";
		String SPStateMent = "";
        CallableStatement cs = null;
        String arrDocNm [] = {"외주기성고 계산서"};
        String arrProofGu [] = {"12"};
        
        
        try {
        	
        	// 증빙정보 생성 프로시저 
        	// - 외주 기성고 계산서만 새로 생성해야만 한다.
			SPStateMent = "{call SAC_DOCUMENTATIVE.prc_subinvoicebiz2(?, ?, ?, ?, ?)}";
			
            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);

	            if(endYn.equals("J")){ //준공
	                /*// 입력된 증빙데이터 라인을 기준으로 실제 증빙을 */
					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 생성 
					SACDOCreatePDF7 sacDOCreatePDF7 = new SACDOCreatePDF7(); 
					sacDOCreatePDF7.doBiz1(con, outputStream, acmp_id, null);
	            }else{	//중도
	                /*// 입력된 증빙데이터 라인을 기준으로 실제 증빙을 */
					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 생성 
					SACDOCreatePDF8 sacDOCreatePDF8 = new SACDOCreatePDF8(); 
					sacDOCreatePDF8.doBiz1(con, outputStream, acmp_id, 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;
		} 
	}
	
}