ebid/java/kr/co/udapsoft/common/commonSign/CommonMailThreadBiz.java

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

import java.sql.Connection;

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.RowImpl;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFConnection;
import kr.co.hsnc.common.sql.WAFConnectionImpl;
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.util.ValueObject;
import java.text.SimpleDateFormat;
import java.util.Date;
import kr.co.udapsoft.common.sender.MessageSend;
import com.udapsoft.waf.system.HandlerStorage;
import oracle.toplink.internal.ox.StrBuffer;
/**
 * 
 * @version 1.0
 * @author DaeJin Lee
 */
public class CommonMailThreadBiz {

    private WAFConnection conn = null;
    
    public CommonMailThreadBiz() {

        this.conn = new WAFConnectionImpl();        
        
    }

    /**
     * 전자결재 처리결과 수신 Biz
     */
    public void doBiz() throws Exception {

    	try {
    		long time = System.currentTimeMillis(); 
    		SimpleDateFormat dayTime = new SimpleDateFormat("HHmm");
    		String str = dayTime.format(new Date(time)); 
    	    if (str.equals("0800") || str.equals("0801")){   //오전08시 실행. 
    			try{
    				RowSet rowTread = getBidThread();
	        		sendBidMail(rowTread);  //당일 마감입찰
	        		
	        		RowSet rowCont = getContThread();
	        		sendContMail(rowCont);  //계약 20일 초과 하도급미발행 

	        		//2019.11.29 추가 Start
	        		//RowSet rowPurContEnd = getPurContEndThread();
	        		//sendContEndMail(rowPurContEnd);  //구매계약 만료 30일전 알림
	        		
	        		RowSet rowOutContEnd = getOutContEndThread();
	        		sendContEndMail(rowOutContEnd);  //외주계약 만료 30일전 알림
	        		//2019.11.29 추가 End
    			}
    			catch(Exception se){
    				WAFLogger.error(this.getClass().getName() + " --> 입찰마감오류() : " + se.getMessage());
    	    		WAFLogger.error(se);
    				throw se;
    			} 
    		}   
    	    try{
    	    	conn.begin();				
        		
        		//구매변경계약 업데이트
        		Row row = new RowImpl();
        		String maxPurContid = new String();
        		RowSet rowChgTread = getChgContThread();
        		for ( int i = 0 ; i < rowChgTread.size() ; i ++ ) {
    	        	row = rowChgTread.getRow(i); //구매 변경계약 PUR2000T로 INSERT 
    	    	        
        		    maxPurContid = getMaxPurCntId();
        		    addChangeContItem(conn.getConnection(), row.get("DEMANDED_ID"), maxPurContid, row.get("PUR_CONT_MOD_ID"));	//변경계약 아이템 저장
        		    addChangeContPur(conn.getConnection(), row.get("DEMANDED_ID"), maxPurContid, row.get("PUR_CONT_MOD_ID") );	//변경 계약 추가
        		    addChangeContSupport(conn.getConnection(), row.get("PUR_CONT_MOD_ID"), maxPurContid, row.get("PUR_CONT_MOD_ID"));	//변경 계약 업체추가
        		}
        		
        		RowSet rowMail = getMailThread();  //발주의뢰, 변경계약 메일알림 
        		sendMail(rowMail);
        		updateMail(conn.getConnection(), rowMail);
			}
			catch(Exception se){
				WAFLogger.error(this.getClass().getName() + " --> doBiz() : " + se.getMessage());
	    		WAFLogger.error(se);
	    		conn.setRollback();
				throw se;
			} finally {
                conn.end();
            }
    	} catch(Exception se) {
    		WAFLogger.error(this.getClass().getName() + " --> setInsert() : " + se.getMessage());
    		WAFLogger.error(se);    		
            throw se;
        }    	
	}
    
    
    private RowSet getBidThread() throws Exception{
    	Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();       
        
        try {
        	//개찰알림메일
            sqlstr.append(" SELECT A.DEPT_CD                                                             \n");
            sqlstr.append("       ,A.DEPT_NM                                                             \n");
            sqlstr.append("       ,A.BID_MASTER_NM                                                       \n");
            sqlstr.append("       ,A.BID_NO                                                              \n");
            sqlstr.append("       ,A.BID_DEG                                                             \n");
            sqlstr.append("       ,TO_CHAR(A.BID_TO_DT , 'YYYY-MM-DD HH24:MI') BID_TO_DT                \n");
            sqlstr.append("       ,A.PUREMPNO                                                            \n");
            sqlstr.append("       ,B.USER_SABUN                                                          \n");
            sqlstr.append("       ,B.USER_NAME                                                           \n");
            sqlstr.append("       ,B.HAND_TEL_NO                                                         \n");
            sqlstr.append("       ,B.E_MAIL                                                              \n");
            sqlstr.append("       ,A.OPEN_EMPNM                                                          \n");
            sqlstr.append("   FROM BID_MASTER A                                                          \n");
            sqlstr.append("       ,PUB0120T B                                                            \n");
            sqlstr.append("  WHERE A.PUREMPNO = B.ACCT_ID                                                \n");
            sqlstr.append("    AND A.ONBID_YN = 'Y'                                                      \n");
            sqlstr.append("    AND A.STATUS_CD = '04'                                                    \n");
            sqlstr.append("    AND DEMANDED_KIND != 'O'                                                  \n");
            sqlstr.append("    AND TO_CHAR(BID_TO_DT, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD')         \n");
          
            search.setStatement(sqlstr.toString());
            rowSet = search.execute();            
        }
        catch(WAFSQLException se) {
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
            throw se;
       }
        finally {
            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    private RowSet getContThread() throws Exception{
    	Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();       
        
        try {
        	//하도급 지급보증 발급. 계약일로 부터 20일 . 
        	sqlstr.append("SELECT A.DEPT_CD                                                              \n");
            sqlstr.append("      ,A.DEPT_NAME                                                            \n");
            sqlstr.append("      ,A.ORD_DOC_NO                                                           \n");
            sqlstr.append("      ,A.CST_DOC_NAME                                                         \n");
            sqlstr.append("      ,TO_CHAR(A.ORD_CON_DATE, 'YYYY-MM-DD') ORD_CON_DATE                     \n");
            sqlstr.append("      ,A.CON_CHARGE_ID                                                        \n");
            sqlstr.append("      ,B.USER_NAME                                                            \n");
            sqlstr.append("      ,B.HAND_TEL_NO                                                          \n");
            sqlstr.append("      ,B.E_MAIL                                                               \n");
            sqlstr.append("      ,C.VENDCD                                                               \n");
            sqlstr.append("      ,C.MEMBER_NAME                                                          \n");
            sqlstr.append("      ,D.PUR_CONT_MOD_ID                                                      \n");
            sqlstr.append("  FROM PUR2000T A                                                             \n");
            sqlstr.append("      ,PUB0120T B                                                             \n");
            sqlstr.append("      ,CONT_PUR_SUPP C                                                        \n");
            sqlstr.append("      ,CONT_PUR_WARR D                                                        \n");
            sqlstr.append(" WHERE A.CON_CHARGE_ID = B.ACCT_ID                                            \n");
            sqlstr.append("   AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                                  \n");
            sqlstr.append("   AND A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID(+)                               \n");
            sqlstr.append("   AND A.STATUS_CD = '50'                                                     \n");
            sqlstr.append("   AND A.TEMPLATE_CD = '15'                                                   \n");
            sqlstr.append("   AND C.SIGN_SEQ = 2                                                         \n");
            sqlstr.append("   AND D.WARR_GUBUN(+) = '2'                                                  \n");
            sqlstr.append("   AND D.PUR_CONT_MOD_ID IS NULL                                              \n");
            sqlstr.append("   AND TO_DATE(A.ORD_CON_DATE) + 19 > SYSDATE                                 \n");//계약일 20일이후 부터 . 금액제한 확인 
        	
            search.setStatement(sqlstr.toString());
            rowSet = search.execute();            
        }
        catch(WAFSQLException se) {
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
            throw se;
       }
        finally {
            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    private RowSet getMailThread() throws Exception{
    	Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();       
        
        try {
        	//발주의뢰, 변경계약 메일발송 
        	sqlstr.append("SELECT  A.MAIL_ID   --발주의뢰                                           \n");  
        	sqlstr.append("       ,A.SEND_GB                                 \n");
        	sqlstr.append("       ,GET_DEPT_NM(B.DEPT_CD) DEPT_NM            \n");   
        	sqlstr.append("       ,B.DEMANDED_NO DEM_NO                      \n");  
        	sqlstr.append("       ,B.DEMANDED_TITLE DEM_TITLE                \n");  
        	sqlstr.append("       ,A.ACCT_ID                                 \n");
        	sqlstr.append("       ,A.ADDR E_MAIL                             \n");
        	sqlstr.append("  FROM T_MAIL A                                   \n");
            sqlstr.append("      ,PMM_DEMANDED_REQUEST B                     \n");
            sqlstr.append(" WHERE A.DEM_ID = B.DEMANDED_ID                   \n");
            sqlstr.append("   AND A.ATTR_1 = 0                               \n");
            sqlstr.append("   AND A.SEND_GB = 'ORD'                          \n");
            sqlstr.append(" UNION ALL --외주변경                                                             \n");
            sqlstr.append("SELECT A.MAIL_ID                                  \n");
            sqlstr.append("      ,A.SEND_GB                                  \n");
            sqlstr.append("      ,B.DEPT_NAME DEPT_NM                        \n");
            sqlstr.append("      ,B.CON_DOC_NO DEM_NO                        \n");
            sqlstr.append("      ,B.BUILD_NAME DEM_TITLE                     \n");
            sqlstr.append("      ,A.ACCT_ID                                  \n");           
            sqlstr.append("      ,A.ADDR E_MAIL                              \n");           
            sqlstr.append("  FROM T_MAIL A                                   \n");           
            sqlstr.append("      ,SUB2000T B                                 \n");
            sqlstr.append(" WHERE A.DEM_ID = B.SUB_CONT_MOD_ID               \n");        
            sqlstr.append("   AND A.ATTR_1 = 0                               \n");
            sqlstr.append("   AND A.SEND_GB = 'OCHG'                         \n");
            sqlstr.append(" UNION ALL  --구매변경                                                           \n");
            sqlstr.append("SELECT  A.MAIL_ID                                 \n");               
            sqlstr.append("      ,A.SEND_GB                                  \n");                 
            sqlstr.append("      ,C.DEPT_NAME DEPT_NM                        \n");
            sqlstr.append("      ,C.ORD_DOC_NO DEM_NO                        \n");
            sqlstr.append("      ,C.CST_DOC_NAME DEM_TITLE                   \n");
            sqlstr.append("      ,D.ACCT_ID                                  \n");          
            sqlstr.append("      ,D.E_MAIL E_MAIL                            \n");            
            sqlstr.append("  FROM T_MAIL A                                   \n");          
            sqlstr.append("      ,PMM_DEMANDED_REQUEST B                     \n");
            sqlstr.append("      ,PUR1000T C                                 \n");
            sqlstr.append("      ,PUB0120T D                                 \n");
            sqlstr.append(" WHERE A.DEM_ID = B.DEMANDED_ID                   \n");
            sqlstr.append("   AND B.DEMANDED_NO = C.REQ_DOC_NO               \n");
            sqlstr.append("   AND C.CON_CHARGE_ID = D.ACCT_ID                \n");
            sqlstr.append("   AND A.ATTR_1 = 0                               \n");
            sqlstr.append("   AND A.SEND_GB = 'PCHG'                         \n");
            		 		
            search.setStatement(sqlstr.toString());
            rowSet = search.execute();            
        }
        catch(WAFSQLException se) {
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
            throw se;
       }
        finally {
            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    private void updateMail(Connection connection,RowSet rowTread) throws Exception{
    	Persistent persistent = new PersistentImpl(connection);
        StringBuffer sqlstr = new StringBuffer();
        
        Row row = new RowImpl();
        
        try {
        	sqlstr.append("UPDATE T_MAIL             \n"); 
	        sqlstr.append("   SET ATTR_1  = 1        \n");
	        sqlstr.append("      ,UPD_DATE = SYSDATE \n");
	        sqlstr.append(" WHERE MAIL_ID = ?        \n");
	           
	        persistent.setStatement(sqlstr.toString()); 
	            
        	 for ( int i = 0 ; i < rowTread.size() ; i ++ ) {
 	        	row = rowTread.getRow(i);
 	        	persistent.addParameter(row.get("MAIL_ID"));
 	            persistent.execute();
 	            persistent.clearParameters();
        	 }   
        }
        catch(WAFSQLException se) {
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
            throw se;
       }
    }
    
    
    private void sendBidMail(RowSet rowTread) throws Exception{
    	try {
	    	HandlerStorage Stroage = new HandlerStorage();
			MessageSend messageSend = new MessageSend(Stroage);  
			StringBuffer mailtitle = new StringBuffer();
	 		StringBuffer mailtitlein = new StringBuffer();
	        StringBuffer mailContent = new StringBuffer();           	
	        Row row = new RowImpl();
	        
	        mailtitle.append("*[알림]금일 입찰마감 안내입니다. ");
	        mailtitlein.append("*[알림]금일 입찰마감 안내입니다. ");
	     
	        for ( int i = 0 ; i < rowTread.size() ; i ++ ) {
	        	row = rowTread.getRow(i);
	        	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; text-align:left;'>");
	        	mailContent.append("금일 입찰이 마감되니 확인 후 조치하여 주시기 바랍니다.<br>");  
	        	mailContent.append("<br>");  
	         	mailContent.append("프로젝트: " + row.get("DEPT_NM") + "  <br>");  
	         	mailContent.append("입찰번호: " + row.get("BID_NO") + "-" + row.get("BID_DEG") + "  <br>");  
	         	mailContent.append("입찰명: " + row.get("BID_MASTER_NM") + "  <br>");  
	         	mailContent.append("마감시간: " + row.get("BID_TO_DT") + "  <br>");  
	         	mailContent.append("<br>");
	         	mailContent.append("</font>"); 
	         	   
	         	if (row.get("E_MAIL").length() > 4 ){
	         		messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
	         	} 
	         	mailContent = new StringBuffer();     
	        }	        	
    	}
    	catch(Exception e) {
    		Logger.err.println("CommonMailThreadBiz.sendBidMail() 실행중 오류가 발생 했습니다.\\n" + e.getMessage());
    		throw e;
    	}
    }    
    
    private void sendContMail(RowSet rowTread){
    	try {
	    	HandlerStorage Stroage = new HandlerStorage();
			MessageSend messageSend = new MessageSend(Stroage);  
			StringBuffer mailtitle = new StringBuffer();
	 		StringBuffer mailtitlein = new StringBuffer();
	        StringBuffer mailContent = new StringBuffer();           	
	        Row row = new RowImpl();
	        
	        mailtitle.append("*[알림]하도급 지급보증 발급을 처리해 주십시요.");
	        mailtitlein.append("*[알림]하도급 지급보증 발급을 처리해 주십시요.");
	     
	        for ( int i = 0 ; i < rowTread.size() ; i ++ ) {
	        	row = rowTread.getRow(i);
	        	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; text-align:left;'>");
	        	mailContent.append("건설공사 표준하도급 계약이후 하도급지급보증 발급이 처리되지 않았습니다. 확인 후 조치하여 주시기 바랍니다.<br>");  
	        	mailContent.append("<br>");  
	         	mailContent.append("프로젝트: " + row.get("DEPT_NAME") + "  <br>");  
	         	mailContent.append("계약번호: " + row.get("ORD_DOC_NO") + "  <br>");  
	         	mailContent.append("계약명: " + row.get("CST_DOC_NAME") + "  <br>");  
	         	mailContent.append("거래처: " + row.get("MEMBER_NAME") + "  <br>");  
	         	mailContent.append("계약일자: " + row.get("ORD_CON_DATE") + "  <br>");  
	         	mailContent.append(row.get("E_MAIL"));   
	         	mailContent.append("<br>");
	         	mailContent.append("</font>");
	         	if (row.get("E_MAIL").length() > 4 ){
	         		messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
	         	}
	         	mailContent = new StringBuffer();     
	        }	        	
    	}
    	catch(Exception e) {
    		Logger.err.println("doBiz() 실행중 오류가 발생 했습니다.\\n" + e.getMessage()); 
        }
    }    
    
    private void sendMail(RowSet rowTread){
    	try {
	    	HandlerStorage Stroage = new HandlerStorage();
			MessageSend messageSend = new MessageSend(Stroage);  
			StringBuffer mailtitle = new StringBuffer();
	 		StringBuffer mailtitlein = new StringBuffer();
	        StringBuffer mailContent = new StringBuffer();           	
	        Row row = new RowImpl();
	        
	        
	     
	        for ( int i = 0 ; i < rowTread.size() ; i ++ ) {
	        	row = new RowImpl();
	        	row = rowTread.getRow(i);
	        	if  (row.get("SEND_GB").equals("ORD")){ 
	        		mailtitle.append("*[알림]발주의뢰가 등록 되었습니다. ");
	    	        mailtitlein.append("*[알림]발주의뢰가 등록 되었습니다. ");
	    	        
	    	        row = rowTread.getRow(i);
		        	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; text-align:left;'>");
		        	mailContent.append("발주의뢰가 등록되었습니다. <br>");  
		        	mailContent.append("<br>");  
		         	mailContent.append("프로젝트: " + row.get("DEPT_NM") + "  <br>");   
		         	mailContent.append("발주의뢰번호: " + row.get("DEM_NO") + "  <br>");  
		         	mailContent.append("발주의뢰명: " + row.get("DEM_TITLE") + "  <br>");  
		         	mailContent.append("전자조달시스템의 발주의뢰 접수 확인 바랍니다.  <br>");  
		         	mailContent.append("<br>");
		         	mailContent.append("</font>");
		         	if (row.get("E_MAIL").length() > 4 ){
		         		messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
		         	}
		         	
		         	mailContent = new StringBuffer();
		         	mailtitle = new StringBuffer();
		         	mailtitlein = new StringBuffer();    
	        	}else if(row.get("SEND_GB").equals("PCHG")){ 
	        		mailtitle.append("*[알림]변경계약이 등록 되었습니다. ");
	    	        mailtitlein.append("*[알림]변경계약이 등록 되었습니다. ");
	    	        
	    	        row = rowTread.getRow(i);
	    	        mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; text-align:left;'>");
		        	mailContent.append("변경계약 요청건이 있습니다. <br>");  
		        	mailContent.append("<br>");  
		         	mailContent.append("프로젝트: " + row.get("DEPT_NM") + "  <br>");   
		         	mailContent.append("계약번호: " + row.get("DEM_NO") + "  <br>");  
		         	mailContent.append("계약명: " + row.get("DEM_TITLE") + "  <br>");  
		         	mailContent.append("전자조달시스템의 변경계약접수 확인 바랍니다.  <br>");  
		         	mailContent.append("<br>");
		         	mailContent.append("</font>");
		         	if (row.get("E_MAIL").length() > 4 ){
		         		messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
		         	}
		         	
		         	mailContent = new StringBuffer();		
		         	mailtitle = new StringBuffer();
		         	mailtitlein = new StringBuffer(); 
	        	}else if(row.get("SEND_GB").equals("OCHG")){ 
	        		mailtitle.append("*[알림]변경계약이 등록 되었습니다. ");
	        		mailtitlein.append("*[알림]변경계약이 등록 되었습니다. ");
	        		
	        		row = rowTread.getRow(i);
	        		mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; text-align:left;'>");
	        		mailContent.append("변경계약 요청건이 있습니다. <br>");  
	        		mailContent.append("<br>");  
	        		mailContent.append("프로젝트: " + row.get("DEPT_NM") + "  <br>");   
	        		mailContent.append("계약번호: " + row.get("DEM_NO") + "  <br>");  
	        		mailContent.append("계약명: " + row.get("DEM_TITLE") + "  <br>");  
	        		mailContent.append("전자조달시스템의 변경계약접수 확인 바랍니다.  <br>");  
	        		mailContent.append("<br>");
	        		mailContent.append("</font>");
	        		if (row.get("E_MAIL").length() > 4 ){
	        			messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "N");
	        		}
	        		
	        		mailContent = new StringBuffer();	
		         	mailtitle = new StringBuffer();
		         	mailtitlein = new StringBuffer();   	         	
	        	}
	        }	        	
    	}
    	catch(Exception e) {
    		Logger.err.println("doBiz() 실행중 오류가 발생 했습니다.\\n" + e.getMessage()); 
        }
    } 
    
    private RowSet getChgContThread() throws Exception{
    	Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();       
        
        try {
        	//구매변경계약 
            sqlstr.append(" SELECT A.DEM_ID DEMANDED_ID                                                  \n");
            sqlstr.append("       ,MAX(C.PUR_CONT_MOD_ID) PUR_CONT_MOD_ID                                \n");
            sqlstr.append("   FROM T_MAIL A                                                              \n");
            sqlstr.append("       ,PMM_DEMANDED_REQUEST B                                                \n");
            sqlstr.append("       ,PUR2000T C                                                            \n");
            sqlstr.append("  WHERE A.DEM_ID = B.DEMANDED_ID                                              \n");
            sqlstr.append("    AND B.DEMANDED_NO = C.REQ_DOC_NO                                          \n");
            sqlstr.append("    AND A.SEND_GB = 'PCHG'                                                    \n");
            sqlstr.append("    AND A.ATTR_1 = 0                                                          \n");
            sqlstr.append("    AND C.STATUS_CD = '50'                                                    \n");
            sqlstr.append("    AND NVL(DIV_PO_YN,'N') != 'Y'                                             \n"); //단가계약 아닌자료 
            sqlstr.append("  GROUP BY  A.DEM_ID                                                          \n");
            //단가계약 추가 2019.04.15
            sqlstr.append("   UNION ALL                                                                  \n");
            sqlstr.append(" SELECT MAX(A.DEM_ID) DEMANDED_ID                                             \n");
            sqlstr.append("       ,MAX(C.PUR_CONT_MOD_ID) PUR_CONT_MOD_ID                                \n");
            sqlstr.append("   FROM T_MAIL A                                                              \n");
            sqlstr.append("       ,PMM_DEMANDED_REQUEST B                                                \n");
            sqlstr.append("       ,PUR2000T C                                                            \n");
            sqlstr.append("  WHERE A.DEM_ID = B.DEMANDED_ID                                              \n");
            sqlstr.append("    AND B.DEMANDED_NO = C.REQ_DOC_NO                                          \n");
            sqlstr.append("    AND A.SEND_GB = 'PCHG'                                                    \n");
            sqlstr.append("    AND A.ATTR_1 = 0                                                          \n");
            sqlstr.append("    AND C.STATUS_CD = '50'                                                    \n");
            sqlstr.append("    AND DIV_PO_YN = 'Y'                                                       \n"); //단가계약 자료 
            sqlstr.append("  GROUP BY  C.PUR_CONT_ID                                                     \n");
                      
            search.setStatement(sqlstr.toString());
            rowSet = search.execute();            
        }
        catch(WAFSQLException se) {
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
            throw se;
       }
        finally {
            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    private String getMaxPurCntId() {
        
        Search search = new SearchImpl();
        String sqlstr = "";
        try {
            sqlstr 	= 	"SELECT PUR2000T_S.NEXTVAL AS MAX_PUR_CONT_MOD_ID \n"
            		+	"  FROM DUAL\n";

            search.setStatement(sqlstr);
            return search.execute().getRow(0).get("MAX_PUR_CONT_MOD_ID");
        }
        catch(Exception e) {

            WAFLogger.error(this.getClass().getName() + " --> getMaxPurCntId() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return null;
        }
    }

    public boolean addChangeContPur(Connection connection, String DEMANDED_ID, String maxPurContid, String prePurContid) throws Exception {
		Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("			INSERT INTO PUR2000T                                                          \n");
        	sqlstr.append("			       (         PUR_CONT_MOD_ID                                              \n");
        	sqlstr.append("			         ,        PUR_CONT_ID                                                 \n");
        	sqlstr.append("			         ,        MOD_NO                                                      \n");
        	sqlstr.append("			         ,        MOD_GB                                                      \n");
        	sqlstr.append("			         ,        MOD_RSN                                                     \n");
        	sqlstr.append("			         ,        MOD_REQ_DATE                                                \n");
        	sqlstr.append("			         ,        MOD_CORP_RSN                                                \n");
        	sqlstr.append("			         ,        DEPT_CD                                                     \n");
        	sqlstr.append("			         ,        DEPT_NAME                                                   \n");
        	sqlstr.append("			         ,        CON_GB                                                      \n");
        	sqlstr.append("			         ,        ORD_DOC_NO                                                  \n");
        	sqlstr.append("			         ,        CST_DOC_NAME                                                \n");
        	sqlstr.append("			         ,        REQ_DEPT_CD                                                 \n");
        	sqlstr.append("			         ,        REQ_DATE                                                    \n");
        	sqlstr.append("			         ,        REQ_USERID                                                  \n");
        	sqlstr.append("			         ,        REQ_USERNM                                                  \n");
        	sqlstr.append("			         ,        REQ_DOC_NO                                                  \n");
        	sqlstr.append("			         ,        CST_DATE                                                    \n");
        	sqlstr.append("			         ,        CON_IN_DATE                                                 \n");
        	sqlstr.append("			         ,        ELEC_CON_YN                                                 \n");
        	sqlstr.append("			         ,        ORD_CON_DATE                                                \n");
        	sqlstr.append("			         ,        PAY_COND_GB                                                 \n");
        	sqlstr.append("			         ,        PAY_CONTENT                                                 \n");
        	sqlstr.append("			         ,        LEAD_COND_GB                                                \n");
        	sqlstr.append("			         ,        LEAD_PLACE                                                  \n");
        	sqlstr.append("			         ,        CORP_ID                                                     \n");
        	sqlstr.append("			         ,        ORD_START_DATE                                              \n");
        	sqlstr.append("			         ,        ORD_END_DATE                                                \n");
        	sqlstr.append("			         ,        ORD_SEND_DATE                                               \n");
        	sqlstr.append("			         ,        ORD_AMT                                                     \n");
        	sqlstr.append("			         ,        ORD_SUPPLY_AMT                                              \n");
        	sqlstr.append("			         ,        ORD_SURTAX_AMT                                              \n");
        	sqlstr.append("			         ,        PREPAY_AMT                                                  \n");
        	sqlstr.append("			         ,        MID_AMT                                                     \n");
        	sqlstr.append("			         ,        RMDR_AMT                                                    \n");
        	sqlstr.append("			         ,        PREPAY_EXE_YN                                               \n");
        	sqlstr.append("			         ,        PREPAY_GUAR_AMT                                             \n");
        	sqlstr.append("			         ,        CON_GUAR_AMT_RATE                                           \n");
        	sqlstr.append("			         ,        CON_GUAR_AMT                                                \n");
        	sqlstr.append("			         ,        CON_GUAR_TERM                                               \n");
        	sqlstr.append("			         ,        DEF_GUAR_AMT_RATE                                           \n");
        	sqlstr.append("			         ,        DEF_GUAR_AMT                                                \n");
        	sqlstr.append("			         ,        DEF_GUAR_TERMS                                              \n");
        	sqlstr.append("			         ,        DEF_GUAR_TERMS_TXT                                          \n");
        	sqlstr.append("			         ,        DEF_GUAR_TERMS2                                             \n");
        	sqlstr.append("			         ,        DEF_GUAR_TERMS_TXT2                                         \n");
        	sqlstr.append("			         ,        DEL_RATE_GB                                                 \n");
        	sqlstr.append("			         ,        CON_CHARGE_ID                                               \n");
        	sqlstr.append("			         ,        CON_CHARGE_NAME                                             \n");
        	sqlstr.append("			         ,        CON_DISPLAY_TXT                                             \n");
        	sqlstr.append("			         ,        ADD_FILE_KIND                                               \n");
        	sqlstr.append("			         ,        CORP_SIGN_DATE                                              \n");
        	sqlstr.append("			         ,        COMPANY_SIGN_DATE                                           \n");
        	sqlstr.append("			         ,        ENT_CLS_YN                                                  \n");
        	sqlstr.append("			         ,        PAY_GROUP                                                   \n");
        	sqlstr.append("			         ,        PAYMENT_TERMS                                               \n");
        	sqlstr.append("			         ,        ACCT_GB                                                     \n");
        	sqlstr.append("			         ,        GUA_GB                                                      \n");
        	sqlstr.append("			         ,        CURRENCY_GB                                                 \n");
        	sqlstr.append("			         ,        PAY_ITEM                                                    \n");
        	sqlstr.append("			         ,        COST_CODE                                                   \n");
        	sqlstr.append("			         ,        COST_TYPE                                                   \n");
        	sqlstr.append("			         ,        ONSHORE_CD                                                  \n");
        	sqlstr.append("			         ,        CONTRACTID                                                  \n");
        	sqlstr.append("			         ,        G_ID                                                        \n");
        	sqlstr.append("			         ,        P_ID                                                        \n");
        	sqlstr.append("			         ,        PREPAY_AMT_RATE                                             \n");
        	sqlstr.append("			         ,        PREPAY_PAYMENT_NM                                           \n");
        	sqlstr.append("			         ,        MID_AMT_RATE                                                \n");
        	sqlstr.append("			         ,        MID_PAYMENT_NM                                              \n");
        	sqlstr.append("			         ,        MID_PAYMENT_TXT                                             \n");
        	sqlstr.append("			         ,        RMDR_AMT_RATE                                               \n");
        	sqlstr.append("			         ,        RMDR_PAYMENT_NM                                             \n");
        	sqlstr.append("			         ,        RMDR_PAYMENT_TXT                                            \n");
        	sqlstr.append("			         ,        PREPAY_GUAR_AMT_RATE                                        \n");
        	sqlstr.append("			         ,        OVERSEA_GB                                                  \n");
        	sqlstr.append("			         ,        PUR_LIST_GB                                                 \n");
        	sqlstr.append("			         ,        CON_RUN_STS                                                 \n");
        	sqlstr.append("			         ,        BID_NO                                                      \n");
        	sqlstr.append("			         ,        BID_DEG                                                     \n");
        	sqlstr.append("			         ,        STATUS_CD                                                   \n");
        	sqlstr.append("			         ,        TEMPLATE_CD                                                 \n");
        	sqlstr.append("			         ,        MODI_REQ_REASON                                             \n");
        	sqlstr.append("			         ,        MODI_REQ_DT                                                 \n");
        	sqlstr.append("			         ,        CON_CLS_YN                                                  \n");
        	sqlstr.append("			         ,        PAY_DIV                                                     \n");
        	sqlstr.append("			         ,        DEM_ID                                                      \n");
        	sqlstr.append("			         ,        IN_SPE_INFO                                                 \n");
        	sqlstr.append("			         ,        IN_CHG_INFO                                                 \n");
        	sqlstr.append("			         ,        FRST_CON_AMT                                                \n");
        	sqlstr.append("			         ,        FRST_EXE_AMT                                                \n");
        	sqlstr.append("			         ,        CHG_CON_AMT                                                 \n");
        	sqlstr.append("			         ,        CHG_EXE_AMT                                                 \n");
        	sqlstr.append("			         ,        CRE_BY                                                      \n");
        	sqlstr.append("			         ,        CRE_DATE                                                    \n");
        	sqlstr.append("			         ,        DIV_PO_YN ,ERP_NO_ECUT   )                                  \n");  //2019.04.26 신용길부장님 요청. 같은계약은 ERP_NO_ECUT동일하게.
        	sqlstr.append("			SELECT ?                                                                      \n");
        	sqlstr.append("			         ,        A.PUR_CONT_ID                                               \n");
        	sqlstr.append("			         ,        MOD_NO + 1                                                  \n");
        	sqlstr.append("			         ,        B.MOD_GB                                                        \n");
        	sqlstr.append("			         ,        NULL                                                        \n");
        	sqlstr.append("			         ,        NULL                                                        \n");
        	sqlstr.append("			         ,        NULL                                                        \n");
        	sqlstr.append("			         ,        A.DEPT_CD                                                   \n");
        	sqlstr.append("			         ,        A.DEPT_NAME                                                 \n");
        	sqlstr.append("			         ,        A.CON_GB                                                    \n");
        	sqlstr.append("			         ,        A.ORD_DOC_NO                                                \n");
        	sqlstr.append("			         ,        A.CST_DOC_NAME                                              \n");
        	sqlstr.append("			         ,        A.REQ_DEPT_CD                                               \n");
        	sqlstr.append("			         ,        A.REQ_DATE                                                  \n");
        	sqlstr.append("			         ,        A.REQ_USERID                                                \n");
        	sqlstr.append("			         ,        A.REQ_USERNM                                                \n");
        	sqlstr.append("			         ,        A.REQ_DOC_NO                                                \n");
        	sqlstr.append("			         ,        A.CST_DATE                                                  \n");
        	sqlstr.append("			         ,        A.CON_IN_DATE                                               \n");
        	sqlstr.append("			         ,        A.ELEC_CON_YN                                               \n");
        	sqlstr.append("			         ,        A.ORD_CON_DATE                                              \n");
        	sqlstr.append("			         ,        A.PAY_COND_GB                                               \n");
        	sqlstr.append("			         ,        A.PAY_CONTENT                                               \n");
        	sqlstr.append("			         ,        A.LEAD_COND_GB                                              \n");
        	sqlstr.append("			         ,        A.LEAD_PLACE                                                \n");
        	sqlstr.append("			         ,        A.CORP_ID                                                   \n");
        	sqlstr.append("			         ,        A.ORD_START_DATE                                            \n");
        	sqlstr.append("			         ,        TO_DATE(B.AFT_CONT_DATE,'YYYYMMDD') AS ORD_END_DATE       \n");
        	sqlstr.append("			         ,        A.ORD_SEND_DATE                                             \n");
        	sqlstr.append("			         ,        CASE WHEN A.ORD_SURTAX_AMT > 0 THEN SUM_ITEM_AMT + TRUNC(SUM_ITEM_AMT / 10, F_GET_CURRENCY_DIGIT(A.CURRENCY_GB))  -- 발주계약금액                                                    \n");
        	sqlstr.append("			                  ELSE SUM_ITEM_AMT END ORD_AMT -- 발주계약금액                                                             \n");
        	sqlstr.append("			         ,		  SUM_ITEM_AMT                                          \n");
        	sqlstr.append("			         ,        CASE WHEN A.ORD_SURTAX_AMT > 0  THEN  TRUNC(SUM_ITEM_AMT / 10,F_GET_CURRENCY_DIGIT(A.CURRENCY_GB))  -- 부가세                                            \n");
        	sqlstr.append("			                  ELSE 0 END ORD_SURTAX_AMT -- 부가세                                            \n");
        	sqlstr.append("			         ,TRUNC(SUM_ITEM_AMT * (PREPAY_AMT_RATE / 100), F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) PREPAY_AMT -- 선급금                                           \n");
        	sqlstr.append("			          ,TRUNC(SUM_ITEM_AMT * (MID_AMT_RATE / 100),F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) MID_AMT -- 중도금                                                                \n");
        	sqlstr.append("			          ,TRUNC(SUM_ITEM_AMT * (RMDR_AMT_RATE / 100),F_GET_CURRENCY_DIGIT(A.CURRENCY_GB)) RMDR_AMT -- 잔금                                              \n");
        	sqlstr.append("			         ,        A.PREPAY_EXE_YN                                             \n");
        	sqlstr.append("			         ,        A.PREPAY_GUAR_AMT                                           \n");
        	sqlstr.append("			         ,        A.CON_GUAR_AMT_RATE                                         \n");
        	sqlstr.append("			         ,        A.CON_GUAR_AMT                                              \n");
        	sqlstr.append("			         ,        A.CON_GUAR_TERM                                             \n");
        	sqlstr.append("			         ,        A.DEF_GUAR_AMT_RATE                                         \n");
        	sqlstr.append("			         ,        A.DEF_GUAR_AMT                                              \n");
        	sqlstr.append("			         ,        A.DEF_GUAR_TERMS                                            \n");
        	sqlstr.append("			         ,        A.DEF_GUAR_TERMS_TXT                                        \n");
        	sqlstr.append("			         ,        A.DEF_GUAR_TERMS2                                           \n");
        	sqlstr.append("			         ,        A.DEF_GUAR_TERMS_TXT2                                       \n");
        	sqlstr.append("			         ,        A.DEL_RATE_GB                                               \n");
        	sqlstr.append("			         ,        A.CON_CHARGE_ID                                             \n");
        	sqlstr.append("			         ,        A.CON_CHARGE_NAME                                           \n");
        	sqlstr.append("			         ,        A.CON_DISPLAY_TXT                                           \n");
        	sqlstr.append("			         ,        A.ADD_FILE_KIND                                             \n");
        	sqlstr.append("			         ,        A.CORP_SIGN_DATE                                            \n");
        	sqlstr.append("			         ,        A.COMPANY_SIGN_DATE                                         \n");
        	sqlstr.append("			         ,        A.ENT_CLS_YN                                                \n");
        	sqlstr.append("			         ,        A.PAY_GROUP                                                 \n");
        	sqlstr.append("			         ,        A.PAYMENT_TERMS                                             \n");
        	sqlstr.append("			         ,        A.ACCT_GB                                                   \n");
        	sqlstr.append("			         ,        A.GUA_GB                                                    \n");
        	sqlstr.append("			         ,        A.CURRENCY_GB                                               \n");
        	sqlstr.append("			         ,        A.PAY_ITEM                                                  \n");
        	sqlstr.append("			         ,        A.COST_CODE                                                 \n");
        	sqlstr.append("			         ,        A.COST_TYPE                                                 \n");
        	sqlstr.append("			         ,        A.ONSHORE_CD                                                \n");
        	sqlstr.append("			         ,        A.CONTRACTID                                                \n");
        	sqlstr.append("			         ,        A.G_ID                                                      \n");
        	sqlstr.append("			         ,        A.P_ID                                                      \n");
        	sqlstr.append("			         ,        A.PREPAY_AMT_RATE                                           \n");
        	sqlstr.append("			         ,        A.PREPAY_PAYMENT_NM                                         \n");
        	sqlstr.append("			         ,        A.MID_AMT_RATE                                              \n");
        	sqlstr.append("			         ,        A.MID_PAYMENT_NM                                            \n");
        	sqlstr.append("			         ,        A.MID_PAYMENT_TXT                                           \n");
        	sqlstr.append("			         ,        A.RMDR_AMT_RATE                                             \n");
        	sqlstr.append("			         ,        A.RMDR_PAYMENT_NM                                           \n");
        	sqlstr.append("			         ,        A.RMDR_PAYMENT_TXT                                          \n");
        	sqlstr.append("			         ,        A.PREPAY_GUAR_AMT_RATE                                      \n");
        	sqlstr.append("			         ,        A.OVERSEA_GB                                                \n");
        	sqlstr.append("			         ,        A.PUR_LIST_GB                                               \n");
        	sqlstr.append("			         ,        A.CON_RUN_STS                                               \n");
        	sqlstr.append("			         ,        A.BID_NO                                                    \n");
        	sqlstr.append("			         ,        A.BID_DEG                                                   \n");
        	sqlstr.append("			         ,        '00'                                                        \n");
        	sqlstr.append("			         ,        NULL                                                        \n");
        	sqlstr.append("			         ,        A.MODI_REQ_REASON                                           \n");
        	sqlstr.append("			         ,        A.MODI_REQ_DT                                               \n");
        	sqlstr.append("			         ,        1                                                           \n");
        	sqlstr.append("			         ,        A.PAY_DIV                                                   \n");
        	sqlstr.append("			         ,        B.DEMANDED_ID                                               \n");
        	sqlstr.append("			         ,        B.P_REMARK                                                  \n");
        	sqlstr.append("			         ,        B.S_REMARK                                                  \n");
        	sqlstr.append("			         ,        A.CHG_CON_AMT                                               \n");   
        	sqlstr.append("			         ,        B.BEF_BUG_AMT                                               \n");
            sqlstr.append("			         ,        A.CHG_CON_AMT                                               \n");
        	sqlstr.append("			         ,        B.AFT_BUG_AMT                                               \n");
        	sqlstr.append("			         ,    '11111'                                                         \n");
        	sqlstr.append("			         ,    SYSDATE                                                         \n");
        	sqlstr.append("			         ,    A.DIV_PO_YN,A.ERP_NO_ECUT                                       \n");
        	sqlstr.append("			  FROM PUR2000T  A                                                            \n");
        	sqlstr.append("			          , PMM_DEMANDED_REQUEST  B                                           \n");
        	sqlstr.append("			          , ( SELECT PUR_CONT_MOD_ID                                          \n");
        	sqlstr.append("			                   ,  SUM(ITEM_AMT) AS SUM_ITEM_AMT                           \n");
        	sqlstr.append("			              FROM CONT_PUR_ITEM                                              \n");
        	sqlstr.append("			              WHERE PUR_CONT_MOD_ID = ?                                       \n");
        	sqlstr.append("			             GROUP BY PUR_CONT_MOD_ID ) C                                     \n");
        	sqlstr.append("			 WHERE   A.REQ_DOC_NO = B.DEMANDED_NO                                         \n");
        	sqlstr.append("			   AND   A.PUR_CONT_MOD_ID = ?                                                \n");
        	sqlstr.append("			 AND     B.DEMANDED_ID = ?                                  				  \n");
        	
					
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(maxPurContid);
			persistent.addParameter(prePurContid);
			persistent.addParameter(prePurContid);
			persistent.addParameter(DEMANDED_ID);
			//System.out.println(sqlstr.toString());
			persistent.execute();
			
            return true;
        }
        catch(Exception e) {
        	WAFLogger.error(this.getClass().getName() + " --> addChangeContPur() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }        
    }	
    
    public boolean addChangeContSupport(Connection connection, String PUR_CONT_MOD_ID, String maxPurContid, String prePurContid ) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
			sqlstr.append("INSERT INTO CONT_PUR_SUPP							\n");
			sqlstr.append("       ( 	PUR_CONT_MOD_ID		\n");
			sqlstr.append("         ,	CORP_ID		\n");
			sqlstr.append("         ,	SIGN_SEQ		\n");
			sqlstr.append("         ,	VENDCD		\n");
			sqlstr.append("         ,	MEMBER_NAME		\n");
			sqlstr.append("         ,	BOSS_NAME		\n");
			sqlstr.append("         ,	POST_CODE		\n");
			sqlstr.append("         ,	ADDRESS		\n");
			sqlstr.append("         ,	TEL_NUM		\n");
			sqlstr.append("         ,	MEMBER_SLNO		\n");
			sqlstr.append("         ,	USER_NAME		\n");
			sqlstr.append("         ,	DIVISION		\n");
			sqlstr.append("         ,	POSITION		\n");
			sqlstr.append("         ,	HP		\n");
			sqlstr.append("         ,	EMAIL		\n");
			sqlstr.append("         ,	CRE_BY		\n");
			sqlstr.append("         ,	CRE_DATE	)	\n");

			sqlstr.append("SELECT ?	\n");
			sqlstr.append("         ,	A.CORP_ID		\n");
			sqlstr.append("         ,	A.SIGN_SEQ		\n");
			sqlstr.append("         ,	A.VENDCD		\n"); 
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(B.CORP_KOR, A.MEMBER_NAME), C.CORP_KOR) AS MEMBER_NAME		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(B.REP_NAME, A.BOSS_NAME), C.REP_NAME) AS BOSS_NAME		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(B.POST_NO, A.POST_CODE), C.POST_NO) AS POST_CODE		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(B.ADDRESS, A.ADDRESS), C.ADDRESS) AS ADDRESS		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(B.CORP_TEL, A.TEL_NUM), A.TEL_NUM) AS TEL_NUM		\n");
			sqlstr.append("         ,	DECODE(SIGN_SEQ, 2, NVL(B.COPR_NO, A.MEMBER_SLNO), C.COPR_NO) AS MEMBER_SLNO		\n");
			sqlstr.append("         ,	A.USER_NAME		\n");
			sqlstr.append("         ,	A.DIVISION		\n");
			sqlstr.append("         ,	A.POSITION		\n");
			sqlstr.append("         ,	A.HP		\n");
			sqlstr.append("         ,	A.EMAIL		\n");
			sqlstr.append("         ,	'11111'		\n");
			sqlstr.append("         ,	SYSDATE				\n");
			sqlstr.append("  FROM CONT_PUR_SUPP A, SUPP_INFO B , HEAD_OFFICE_INFO C				\n");
			sqlstr.append(" WHERE A.CORP_ID = B.CORP_ID (+) 					\n");
			sqlstr.append(" AND A.PUR_CONT_MOD_ID= ?								\n");
						
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(maxPurContid);
			persistent.addParameter(PUR_CONT_MOD_ID);
			persistent.execute();
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> addChangeContSupport() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }        
    }
	
	/**
     * 변경 계약 업체 정보 추가
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean addChangeContItem(Connection connection, String demanded_id, String maxPurContid, String prePurContid) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);
        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	
        	
			sqlstr.append("INSERT INTO CONT_PUR_ITEM 	                                     \n");
			sqlstr.append("       ( 	PUR_CONT_MOD_ID	                                     \n");
			sqlstr.append("         ,	MATERIAL_CD		                                     \n");
			sqlstr.append("         ,	PUR_CONT_ID		                                     \n");
			sqlstr.append("         ,	ITEM_NAME		                                     \n");
			sqlstr.append("         ,	ITEM_SPEC		                                     \n");
			sqlstr.append("         ,	ITEM_UNIT		                                     \n");
			sqlstr.append("         ,	ITEM_QTY		                                     \n");
			sqlstr.append("         ,	ITEM_PRICE		                                     \n");
			sqlstr.append("         ,	ITEM_AMT		                                     \n");
			sqlstr.append("         ,	CRE_BY			                                     \n");
			sqlstr.append("         ,	CRE_DATE		                                     \n");
			sqlstr.append("		)						                                     \n");
			sqlstr.append("SELECT ?						                                     \n");
			sqlstr.append("         ,   B.MATERIAL_CD 	                                     \n");
			sqlstr.append("         ,	C.PUR_CONT_ID		                                 \n");
			sqlstr.append("         ,   D.MATERIAL_NAME  AS ITEM_NAME        		         \n");
			sqlstr.append("         ,   D.MATERIAL_SPEC     AS ITEM_SPEC   	                 \n");
			sqlstr.append("         ,   D.UNIT_KIND      AS ITEM_UNIT	                     \n");
			sqlstr.append("         ,	DEMANDED_QTY		                                 \n");
			sqlstr.append("         ,	DEMANDED_PRI		                                 \n");
			sqlstr.append("         ,	DEMANDED_AMT		                                 \n");
			sqlstr.append("         ,	'11111'				                                 \n");
			sqlstr.append("         ,	SYSDATE			                                     \n");
			sqlstr.append("  FROM PMM_DEMANDED_REQUEST A 		                             \n");
			sqlstr.append("     , PMM_DEMANDED_ITEM B		                                 \n");
			sqlstr.append("     , PUR2000T   C		                                         \n");
			sqlstr.append("     , PMM_COMM_MATERIAL_MASTER  D		                         \n");
			sqlstr.append("     ,(SELECT MAX(A1.PUR_CONT_MOD_ID) PUR_CONT_MOD_ID              \n");
            sqlstr.append("         FROM PUR2000T A1, PMM_DEMANDED_REQUEST B1                 \n");
            sqlstr.append("        WHERE A1.REQ_DOC_NO = B1.DEMANDED_NO                       \n");
            sqlstr.append("          AND A1.STATUS_CD = '50'                                  \n");
            sqlstr.append("          AND B1.DEMANDED_ID = ? ) E                               \n");
        	sqlstr.append(" WHERE A.DEMANDED_ID = B.DEMANDED_ID  		                      \n");
			sqlstr.append(" AND A.DEMANDED_NO = C.REQ_DOC_NO    		                      \n");
			sqlstr.append(" AND C.PUR_CONT_MOD_ID = E.PUR_CONT_MOD_ID   	                  \n");
			sqlstr.append(" AND B.MATERIAL_CD = D.MATERIAL_CD         		                  \n");
			sqlstr.append(" AND A.DEMANDED_ID= ?	                                          \n");
			persistent.setStatement(sqlstr.toString());
			persistent.addParameter(maxPurContid);
			persistent.addParameter(demanded_id);
			persistent.addParameter(demanded_id);
				
        	
			persistent.execute();
            return true;
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> addChangeContItem() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }        
    }	
	
    private RowSet getPurContEndThread() throws Exception{
    	Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();       
        
        try {
        	sqlstr.append(" 	-- 프로젝트명, 계약번호, 계약명, 거래처, 계약기간 \n");
        	sqlstr.append(" 	SELECT A.CON_DOC_NO                       \n");
        	sqlstr.append(" 	     , A.DEPT_NAME                        \n");
        	sqlstr.append(" 	     , A.BUILD_NAME                       \n");
        	sqlstr.append(" 	     , A.END_WORK_DATE                    \n");
        	sqlstr.append(" 	     , B.MEMBER_NAME                      \n");
        	sqlstr.append(" 	     , C.E_MAIL                           \n");
        	sqlstr.append(" 	  FROM (                                  \n");
        	sqlstr.append("            SELECT A.PUR_CONT_MOD_ID           \n");
        	sqlstr.append("                 ,A.REQ_USERID	              \n");
        	sqlstr.append("                 ,A.ORD_DOC_NO AS CON_DOC_NO	  \n");
        	sqlstr.append("                 ,A.DEPT_NAME                  \n");
        	sqlstr.append("                 ,A.CST_DOC_NAME AS BUILD_NAME \n");
        	sqlstr.append("                 ,'~ ' || TO_CHAR(A.ORD_END_DATE, 'YYYY-MM-DD') AS END_WORK_DATE \n");
        	sqlstr.append("              FROM PUR2000T A                  \n");
        	sqlstr.append("                   , (SELECT PUR_CONT_ID, MAX(MOD_NO) AS MOD_NO  \n");
        	sqlstr.append("                        FROM PUR2000T          \n");
        	sqlstr.append("                       WHERE STATUS_CD = '50'  \n");
        	sqlstr.append("                         AND PUR_CONT_ID IN (SELECT PUR_CONT_ID FROM PUR2000T WHERE TEMPLATE_CD = '15') -- 건설공사 표준하도급계약서  \n");
        	sqlstr.append("                       GROUP BY PUR_CONT_ID) B \n");
        	sqlstr.append("           WHERE A.PUR_CONT_ID = B.PUR_CONT_ID \n");
        	sqlstr.append("            AND A.MOD_NO = B.MOD_NO            \n");
        	sqlstr.append("            AND A.STATUS_CD = '50' -- 계약완료     \n");
        	sqlstr.append("            AND TO_CHAR(A.ORD_END_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE + 30, 'YYYY-MM-DD')-- 30일 이후 종료 계약 \n");        	
        	sqlstr.append("           ) A, CONT_PUR_SUPP B, PUB0120T C    \n");
        	sqlstr.append("     WHERE A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID \n");
        	sqlstr.append("       AND B.SIGN_SEQ = 2                        \n");
        	sqlstr.append("       AND A.REQ_USERID = C.ACCT_ID              \n");

            search.setStatement(sqlstr.toString());
            rowSet = search.execute();            
        }
        catch(WAFSQLException se) {
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
            throw se;
       }
        finally {
            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }	
    
    private RowSet getOutContEndThread() throws Exception{
    	Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StringBuffer sqlstr = new StringBuffer();       
        
        try {
        	sqlstr.append(" 	-- 프로젝트명, 계약번호, 계약명, 거래처, 계약기간 \n");
        	sqlstr.append(" 	SELECT A.CON_DOC_NO                       \n");
        	sqlstr.append(" 	     , A.DEPT_NAME                        \n");
        	sqlstr.append(" 	     , A.BUILD_NAME                       \n");
        	sqlstr.append(" 	     , A.END_WORK_DATE                    \n");
        	sqlstr.append(" 	     , B.MEMBER_NAME                      \n");
        	sqlstr.append(" 	     , C.E_MAIL 	                      \n");
        	sqlstr.append(" 	  FROM (                                  \n");
        	sqlstr.append("            SELECT A.SUB_CONT_MOD_ID           \n");
        	sqlstr.append("                 ,A.REQ_USERID	              \n");
        	sqlstr.append("                 ,A.CON_DOC_NO		          \n");
        	sqlstr.append("                 ,A.DEPT_NAME	              \n");
        	sqlstr.append("                 ,A.BUILD_NAME		          \n");
        	sqlstr.append("                 ,'~ ' || TO_CHAR(A.END_WORK_DATE, 'YYYY-MM-DD') AS END_WORK_DATE \n");
        	sqlstr.append("              FROM SUB2000T A                  \n");
        	sqlstr.append("                   , (SELECT SUB_CONT_ID, MAX(MOD_NO) AS MOD_NO \n");
        	sqlstr.append("                        FROM SUB2000T          \n");
        	sqlstr.append("                       WHERE STATUS_CD = '50'  \n");
        	sqlstr.append("                       GROUP BY SUB_CONT_ID) B \n");
        	sqlstr.append("           WHERE A.SUB_CONT_ID = B.SUB_CONT_ID \n");
        	sqlstr.append("            AND A.MOD_NO = B.MOD_NO            \n");
        	sqlstr.append("            AND A.STATUS_CD = '50' -- 계약완료     \n");
        	sqlstr.append("            AND TO_CHAR(A.END_WORK_DATE, 'YYYY-MM-DD') = TO_CHAR(SYSDATE + 30, 'YYYY-MM-DD')-- 30일 이후 종료 계약 \n");        	
        	sqlstr.append("           ) A, CONT_OUT_SUPP B, PUB0120T C      \n");
        	sqlstr.append("     WHERE A.SUB_CONT_MOD_ID = B.SUB_CONT_MOD_ID \n");
        	sqlstr.append("       AND B.SIGN_SEQ = 2                        \n");
        	sqlstr.append("       AND A.REQ_USERID = C.ACCT_ID              \n");

            search.setStatement(sqlstr.toString());
            rowSet = search.execute();            
        }
        catch(WAFSQLException se) {
            Logger.err.println("SQL : \n" + se.getStatement());
            Logger.err.println("PARAM : \n" + se.getParameter());
            throw se;
       }
        finally {
            if( rowSet == null ) rowSet = new RowSetImpl();
            return rowSet;
        }
    }    
    
    private void sendContEndMail(RowSet rowTread){
    	try {
	    	HandlerStorage Stroage = new HandlerStorage();
			MessageSend messageSend = new MessageSend(Stroage);  
			StringBuffer mailtitle = new StringBuffer();
	 		StringBuffer mailtitlein = new StringBuffer();
	        StringBuffer mailContent = new StringBuffer();           	
	        Row row = new RowImpl();
	        
	        mailtitle.append("*[알림]계약기간 만료 예정");
	        mailtitlein.append("*[알림]계약기간 만료 예정");
	     
	        for ( int i = 0 ; i < rowTread.size() ; i ++ ) {
	        	row = rowTread.getRow(i);
	        	mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; text-align:left;'>");
	        	mailContent.append("다음 하도급 계약의 계약기간이 30일 후 만료 예정이오니 ERP에서 변경계약을 요청하시기 바랍니다.<br>");  
	        	mailContent.append("<br>");  
	         	mailContent.append("1.프로젝트: " + row.get("DEPT_NAME") + "  <br>");  
	         	mailContent.append("2.계약번호: " + row.get("CON_DOC_NO") + "  <br>");
	         	mailContent.append("3.계약명: " + row.get("BUILD_NAME") + "  <br>");
	         	mailContent.append("4.계약기간: " + row.get("END_WORK_DATE") + "  <br>");
	         	mailContent.append("5.계약업체: " + row.get("MEMBER_NAME") + "  <br>");  
	         	mailContent.append("6.비고: <br>");  
		        mailContent.append("&nbsp;1)계약기간 만료일에 준공 예정일 경우 해당 없음.   <br>");
		        mailContent.append("&nbsp;2)계약기간이 유효한 계약만 기성 신청가능하므로 실제 준공 예상 시점까지 계약기간 연장 필요.   <br>");
		        mailContent.append("&nbsp;3)eTEC Standard에 따라 변경 계약에 필요한 자료를 ERP에 첨부 후 변경계약 요청.          <br>");
	         	mailContent.append("<br>");
	         	mailContent.append("</font>");
	         	if (row.get("E_MAIL").length() > 4 ){
	         		messageSend.sendEmail( row.get("E_MAIL"), "partner@sgc.co.kr", mailtitle.toString(), mailtitlein.toString(), mailContent, "NO_LINK");	         		
	         	}
	         	mailContent = new StringBuffer();     
	        }	        	
    	}
    	catch(Exception e) {
    		Logger.err.println("doBiz() 실행중 오류가 발생 했습니다.\\n" + e.getMessage()); 
        }
    }        
}