/**
 *
 * @(#)author  [ok]
 * @(#)version 1.0
 * @(#)date    2014. 06. 25
 * @(#)since   JDK 1.7.2
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 * This software is the proprietary information of udapsoft, Inc.
 */
package kr.co.udapsoft.ebid.supplier.portal.payment.biz;


import java.sql.Connection;
import java.sql.PreparedStatement;

import com.udapsoft.waf.common.util.ClobUtils;
import com.udapsoft.waf.common.util.StringUtils;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.config.WAFConfig;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.WAFConnectionImpl;
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.ValueObject;
import kr.co.udapsoft.ebid.buyer.bid.outside.biz.InterfaceOutERPiU;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import oracle.toplink.internal.ox.StrBuffer;

public class ResolutionRequestSaveBiz extends Biz {
	
	private FileManager fileManager = null;
	
	public ResolutionRequestSaveBiz(HandlerStorage storage) {
		super(storage);
		fileManager = new FileManager();       
	}

	public void doBiz() throws Exception {

		boolean success = false;
		if(storage.compareEvent("SAVE_DATA")){	//데이터 저장
			success = saveData(params);
		}else if(storage.compareEvent("DELETE_DATA")){	//데이터 삭제
			success = deleteData(params);
		}else if(storage.compareEvent("REQ_DATA")){		//선급금신청 요청
			if (!PreAmtCompare(params.get("acmpId"))) {  //2023.09.05 금회 선급금액 비교(과세+면세 = 합계)
				super.storage.setSuccess(false);
				super.storage.setMessage("금회 선급금의 과세,면세 금액 합계가 일치하지 않습니다.");
				return;
			}
			
			if (!AmtCompare(params.get("acmpId"))) {  //2018.11.5 기성신청시 내역과 기성금액 비교 
				super.storage.setSuccess(false);
				super.storage.setMessage("기성금액과 기성내역의 과세,면세 금액이 일치하지 않습니다.");
				return;
			}else if (params.get("LABOR_USE_YN").equals("Y")) {  //2022. ERP내 에스크로계좌 등록여부 확인
				Search search = new SearchImpl();
		        RowSet listRowSet = null;
		        StrBuffer sqlstr = new StrBuffer();
		        ValueObject rowObject = null;
		        String LABOR_ACCOUNT_CNT = "";
		        
		        sqlstr.append(" 	SELECT NVL(COUNT(*),0) LABOR_ACCOUNT_CNT	        \n");
	        	sqlstr.append(" 	  FROM MA_PARTNER@ERPIU A                           \n");	//erp 업체정보 테이블
	        	sqlstr.append(" 	     , MA_PARTNER_DEPOSIT@ERPIU B                   \n");	//erp 계좌정보 테이블
	        	sqlstr.append("   WHERE 1=1			                                    \n");
	    		sqlstr.append("   	AND A.CD_COMPANY = 'ETEC'		                    \n");
	    		sqlstr.append("   	AND A.NO_COMPANY = '"+StringUtils.replace(params.get("CORPREGNO"),"-","")+"'		   			    \n");	//사업자번호
	    		sqlstr.append("   	AND A.USE_YN = 'Y'		                            \n");	
	    		sqlstr.append("   	AND A.CD_PARTNER = B.CD_PARTNER		                \n");
	    		sqlstr.append("   	AND B.CD_DEPOSIT = '3'		                        \n");	//CD_DEPOSIT('3' : 에스크로계좌)
	    		
	    		search.setStatement(sqlstr.toString());
	            listRowSet = search.execute();
	            if(listRowSet.size() != 0){
	            	rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
	            	LABOR_ACCOUNT_CNT = rowObject.get("LABOR_ACCOUNT_CNT");
	            }
	            
	            if(LABOR_ACCOUNT_CNT.equals("1")){
	            	Search search2 = new SearchImpl();
			        RowSet listRowSet2 = null;
			        StrBuffer sqlstr2 = new StrBuffer();
			        ValueObject rowObject2 = null;
			        String LABOR_ACCOUNT_CHECK = "";
			        
			        sqlstr2.append(" 	SELECT NVL(COUNT(*),0) LABOR_ACCOUNT_CHECK	        \n");
		        	sqlstr2.append(" 	  FROM MA_PARTNER@ERPIU A                           \n");	//erp 업체정보 테이블
		        	sqlstr2.append(" 	     , MA_PARTNER_DEPOSIT@ERPIU B                   \n");	//erp 계좌정보 테이블
		        	sqlstr2.append("   WHERE 1=1			                                \n");
		    		sqlstr2.append("   	AND A.CD_COMPANY = 'ETEC'		                    \n");
		    		sqlstr2.append("   	AND A.NO_COMPANY = '"+StringUtils.replace(params.get("CORPREGNO"),"-","")+"'		                        \n");	//사업자번호
		    		sqlstr2.append("   	AND A.USE_YN = 'Y'		                            \n");	
		    		sqlstr2.append("   	AND A.CD_PARTNER = B.CD_PARTNER		                \n");
		    		sqlstr2.append("   	AND B.CD_DEPOSIT = '3'		                        \n");	//CD_DEPOSIT('3' : 에스크로계좌)
		    		sqlstr2.append("   	AND B.USE_YN = 'Y'		                            \n");	//주요계좌여부
		    		
		    		search2.setStatement(sqlstr2.toString());
		            listRowSet2 = search2.execute();
		            if(listRowSet2.size() != 0){
		            	rowObject2 = RowSetUtility.getValueObject(listRowSet2.getRow(0));
		            	LABOR_ACCOUNT_CHECK = rowObject2.get("LABOR_ACCOUNT_CHECK");
		            }
		            
		            if(LABOR_ACCOUNT_CHECK.equals("0")){
		            	
		            }else{
		            	super.storage.setSuccess(false);
						super.storage.setMessage("(ERP)주계좌는 에스크로계좌로 등록할 수 없습니다.");
						return;
		            }
	            }
	            else if(LABOR_ACCOUNT_CNT.equals("0")){
					super.storage.setSuccess(false);
					super.storage.setMessage("(ERP)에스크로 계좌를 등록해 주시기 바랍니다.");
					return;
				}else{
					super.storage.setSuccess(false);
					super.storage.setMessage("(ERP)2개 이상의 에스크로계좌가 등록되어 있습니다. 1개만 등록해 주시기 바랍니다.");
					return;
				}
			}
			success = reqData(params);
		
		}
		storage.setSuccess(success);
		
	}

	/**
	 * Persistent 를 이용한 Save 처리
	 * @param params
	 * @return
	 * * 입력받은 기성 상세 정보를 등록합니다.
     * @param connection
     * @param pgmName
     * @param screenName
     * @param systemCode
     * @throws Exception
	 */
	private boolean saveData(ValueObject params) {
		
		Persistent persistent_u = null;
		String sqlstr_u = "";

		try {
		
			//기성 신청 상세 DATA
			String acmpId					= params.get("acmpId");					//기성ID
			
			String chk_info					= params.get("chk_info");				//세금계산서 발행방법
			String date1					= params.get("date1");					//작성일자(증빙일자)
			String taxChargNm				= params.get("taxChargNm");				//담당자명
			String taxChargHp				= params.get("taxChargHp");				//담당자 핸드폰번호
			String taxChargEmail			= params.get("taxChargEmail");			//담당자 이메일
			String suppDirectYn				= params.get("SUPP_DIRECT_YN");			//하도급직불여부(2020.04.06 추가)
			String laborUseYn				= params.get("LABOR_USE_YN");			//노무비닷컴 사용여부(2022.03.21 추가)
		
			String totFundTamt				= params.get("totFundTamt").replaceAll(",", "");			//기성 누계 과세
			String totFundNamt				= params.get("totFundNamt").replaceAll(",", "");			//기성 누계 면세
			String totFundAmt				= params.get("totFundAmt").replaceAll(",", "");			//기성 누계 합계
			
			String payDeductTamt			= params.get("payDeductTamt").replaceAll(",", "");			//선급공제 금회 과세
			String payDeductNamt			= params.get("payDeductNamt").replaceAll(",", "");			//선급공제 금회 면세
			String payDeductAmt				= params.get("payDeductAmt").replaceAll(",", "");				//선급공제 금회 합계
			
			String totPayDeductTamt			= params.get("totPayDeductTamt").replaceAll(",", "");			//선급공제 누계 과세
			String totPayDeductNamt			= params.get("totPayDeductNamt").replaceAll(",", "");			//선급공제 누계 면세
			String totPayDeductAmt			= params.get("totPayDeductAmt").replaceAll(",", "");			//선급공제 누계 합계
			
			String requSupplyTamt			= params.get("requSupplyTamt").replaceAll(",", "");				//청구액 공급액 금회 과세
			String requSupplyNamt			= params.get("requSupplyNamt").replaceAll(",", "");				//청구액 공급액 금회 면세
			String requSupplyAmt			= params.get("requSupplyAmt").replaceAll(",", "");				//청구액 공급액 금회 공급가액(합계)
			
			String totRequSupplyTamt		= params.get("totRequSupplyTamt").replaceAll(",", "");			//청구액 공급액 누계 과세
			String totRequSupplyNamt		= params.get("totRequSupplyNamt").replaceAll(",", "");			//청구액 공급액 면세 과세
			String totRequSupplyAmt			= params.get("totRequSupplyAmt").replaceAll(",", "");			//청구액 공급액 공급가액(합계) 과세
			
			String requSurtaxAmt			= params.get("requSurtaxAmt").replaceAll(",", "");			//청구액 부가세 금회 과세
			String totRequSurtaxAmt			= params.get("totRequSurtaxAmt").replaceAll(",", "");			//청구액 부가세 누계 과세
			
			String requAmt					= params.get("trequAmt").replaceAll(",", "");				//청구액 합계 금회 과세
			String totRequAmt				= params.get("ttotRequAmt").replaceAll(",", "");			//청구액 합계 누계 과세
			
			String acmpRemark				= params.get("acmp_remark");	//특기사항
			
			conn.begin();
			
			persistent_u = new PersistentImpl(super.conn.getConnection());
			  
			sqlstr_u = " UPDATE SUB1040T	      														\n"
		              + " SET    TAX_METHOD_CD    			= ?       , 					            \n"
		              + "        TAX_DY 					= ?       , 					            \n"
		              + "        TAX_CHARG_NM				= ?       , 					            \n"
		              + "        TAX_CHARG_HP				= ?       , 					            \n"
		              + "        TAX_CHARG_EMAIL			= ?       , 					            \n"
		              + "        SUPP_DIRECT_YN				= ?       , 					            \n"
		              + "        LABOR_USE_YN				= ?       , 					            \n"	//2022.03.21

		              + "        FUND_TAMT			= (SELECT SUM(NVL(CUR_TAMT,0)) FROM SUB1041T WHERE ACMP_ID = ?)       , 					        		    \n"
		              + "        FUND_NAMT			= (SELECT SUM(NVL(CUR_NAMT,0)) FROM SUB1041T WHERE ACMP_ID = ?)       , 					    		        \n"
		              + "        FUND_AMT			= (SELECT SUM(NVL(CUR_TAMT,0)+NVL(CUR_NAMT,0)) FROM SUB1041T WHERE ACMP_ID = ?)       , 							            \n"
		              + "        TOT_FUND_TAMT			= ?       , 					        	    \n"
		              + "        TOT_FUND_NAMT			= ?       , 					    	        \n"
		              + "        TOT_FUND_AMT			= ?       , 						            \n"

		              + "        PAY_DEDUCT_TAMT			= ?       , 					            \n"
		              + "        PAY_DEDUCT_NAMT			= ?       , 					            \n"
		              + "        PAY_DEDUCT_AMT			= ?       , 					        	    \n"
		              + "        TOT_PAY_DEDUCT_TAMT			= ?       , 					        \n"
		              + "        TOT_PAY_DEDUCT_NAMT			= ?       , 					        \n"
		              + "        TOT_PAY_DEDUCT_AMT			= ?       , 						        \n"

		              + "        REQU_SUPPLY_TAMT			= ?       , 					            \n"
		              + "        REQU_SUPPLY_NAMT			= ?       , 					            \n"
		              + "        REQU_SUPPLY_AMT			= ?       , 					            \n"
		              + "        TOT_REQU_SUPPLY_TAMT			= ?       , 				            \n"
		              + "        TOT_REQU_SUPPLY_NAMT			= ?       , 				            \n"
		              + "        TOT_REQU_SUPPLY_AMT			= ?       , 				            \n"

		              + "        REQU_SURTAX_AMT			= ?       , 					            \n"
		              + "        TOT_REQU_SURTAX_AMT			= ?       , 				            \n"

		              + "        REQU_AMT			= ?       , 							            \n"
		              + "        TOT_REQU_AMT			= ?       , 						            \n"

		              + "        ACMP_REMARK   				= ?,        						            \n"
		              + "        STATUS_CD   				= '01'        					            \n"
		              + " WHERE  ACMP_ID	      			= ?        									\n" ;
			  
			persistent_u.setStatement(sqlstr_u);
			
			persistent_u.addParameter(chk_info);	
			persistent_u.addParameter(date1.replaceAll("-", ""));	
			persistent_u.addParameter(taxChargNm);	
			persistent_u.addParameter(taxChargHp);	
			persistent_u.addParameter(taxChargEmail);	
			persistent_u.addParameter(suppDirectYn);
			persistent_u.addParameter(laborUseYn);
			//기성
			persistent_u.addParameter(acmpId);	
			persistent_u.addParameter(acmpId);	
			persistent_u.addParameter(acmpId);	
			persistent_u.addParameter(totFundTamt);	
			persistent_u.addParameter(totFundNamt);	
			persistent_u.addParameter(totFundAmt);	
			
			//선급공제
			persistent_u.addParameter(payDeductTamt);	
			persistent_u.addParameter(payDeductNamt);	
			persistent_u.addParameter(payDeductAmt);	
			persistent_u.addParameter(totPayDeductTamt);	
			persistent_u.addParameter(totPayDeductNamt);	
			persistent_u.addParameter(totPayDeductAmt);	
			
			//청구액 공급액
			persistent_u.addParameter(requSupplyTamt);
			persistent_u.addParameter(requSupplyNamt);
			persistent_u.addParameter(requSupplyAmt);
			persistent_u.addParameter(totRequSupplyTamt);
			persistent_u.addParameter(totRequSupplyNamt);
			persistent_u.addParameter(totRequSupplyAmt);
			
			//청구액 부가세
			persistent_u.addParameter(requSurtaxAmt);
			persistent_u.addParameter(totRequSurtaxAmt);
			
			//합계
			persistent_u.addParameter(requAmt);
			persistent_u.addParameter(totRequAmt);
			
			persistent_u.addParameter(acmpRemark);
			
			persistent_u.addParameter(acmpId);

			persistent_u.execute();
			persistent_u.clearParameters();
			return true;
        }
        catch(Exception e) {
            super.doException(e);
            super.storage.setMessage(e.getMessage());
            super.storage.setDetailMessage(e);
            super.conn.setRollback();
            return false;
        }
        finally {
        	super.conn.end();
        }
	}
	
	/**
	 * Persistent 를 이용한 Delete 처리
	 * @param params
	 * @return
	 * * 입력받은 선급금 상세 정보를 삭제합니다.
     * @param connection
     * @param pgmName
     * @param screenName
     * @param systemCode
     * @throws Exception
	 */
	private boolean deleteData(ValueObject params) {
		
		
		Persistent persistent_d = null;
		String sqlstr_d = "";
		String sqlstr_d2 = "";

		try {
		
			//협력업체 포탈 기성 신청 상세
			String acmpId					= params.get("acmpId");				//기성ID

			conn.begin();
			
			persistent_d = new PersistentImpl(super.conn.getConnection());
			
			sqlstr_d = 	" DELETE                   		\n"
		                  + " FROM   SUB1040T 			\n"
		                  + " WHERE  ACMP_ID = ?        	\n";
			sqlstr_d2 = 	" DELETE  FROM  SUB1041T  WHERE  ACMP_ID = ?        	\n";
			
			persistent_d.setStatement(sqlstr_d);
			persistent_d.addParameter(acmpId);
			
			persistent_d.execute();
			persistent_d.clearParameters();
            
			//sub1041t삭제 
			persistent_d.clear();
			persistent_d.setStatement(sqlstr_d2);
			persistent_d.addParameter(acmpId);
			
			persistent_d.execute();
			
			
			/*************************************************************************************/
    		// 기성 첨부파일 삭제
    		/*************************************************************************************/
			fileManager.delFileMapProcNew( super.conn.getConnection(), "RESOLUTION_"+acmpId);
			
			return true;
        }
        catch(Exception e) {
            super.doException(e);
            super.storage.setMessage(e.getMessage());
            super.storage.setDetailMessage(e);
            super.conn.setRollback();
            return false;
        }
        finally {
        	super.conn.end();
        }
	}
	
	/**
	 * Persistent 를 이용한 Save 처리
	 * @param params
	 * @return
	 * * 협력업체포탈의 기성신청 상세에 기성신청을 처리한다.
     * @param connection
     * @param pgmName
     * @param screenName
     * @param systemCode
     * @throws Exception
	 */
	private boolean reqData(ValueObject params) {
		
		Persistent persistent_u = null;
		String sqlstr_u = "";

		try {
		
			//외주_기성정보
			String acmpId					= params.get("acmpId");					//기성ID
			String subContModId				= params.get("subContModId");		
			String statusCd					= "03";									//기성상태값 : 작성상태(01),기성신청요청(02), 기성신청(03), 기성완료(10), 반려(91)
			String signeddata				= params.get("signeddata");			//서명데이터
			
			conn.begin();
			persistent_u = new PersistentImpl(super.conn.getConnection());

			sqlstr_u = " UPDATE  SUB1040T	    														\n"
			          + " SET    STATUS_CD    				= ?,    									\n"
			          + "        SIGN_DN  				    = ?, 								  	    \n" //2016.02.03추가 
			          + "        SUPP_SIGN_DT  				= sysdate									\n"
			          + " WHERE  ACMP_ID      				= ?        									\n" ;
			  
			persistent_u.setStatement(sqlstr_u);
			persistent_u.addParameter(statusCd);
			persistent_u.addParameter(params.get("CONT_SIGNDN"));//2016.02.03추가 
			persistent_u.addParameter(acmpId);
			
			persistent_u.execute();
			persistent_u.clearParameters();
			
			ClobUtils.setClob( conn.getConnection(), "SUB1040T", "SIGN_DATA", " WHERE ACMP_ID = '"+acmpId+"'", signeddata);
			
			ValueObject mstVo = getSUB1040T(acmpId, subContModId);
			
			ValueObject chkCont = checkChgCont(acmpId, subContModId);
			
			if( chkCont.get("CHK_CONT").equals("N") ) {
				super.storage.setMessage("변경계약이 진행중이거나 이전계약건에 대한 기성신청건입니다.\n변경계약 체결 후 신규기성 등록해 주시기 바랍니다.");
	            super.conn.setRollback();
				return false;
			}
			
			//자금청구 마감여부는 자금청구DETAIL테이블의 SEQ_BUILT = '01' 고정하여 체크
			ValueObject chkVo = checkErpClose(mstVo.get("CD_COMPANY"), mstVo.get("CD_SITE"), mstVo.get("TAX_YM"));
			
			if( chkVo.get("TAG_CLOSE").equals("Y") ) {
				super.storage.setMessage(mstVo.get("TAX_YM").substring(0,4)+"년 "+mstVo.get("TAX_YM").substring(4,6)+"월은 마감되었습니다.");
	            super.conn.setRollback();
				return false;
				
			}else if( chkVo.get("TAG_CLOSE").equals("") ) {
				super.storage.setMessage(mstVo.get("TAX_YM").substring(0,4)+"년 "+mstVo.get("TAX_YM").substring(4,6)+"월 자금청구 데이터가 없습니다.");
	            super.conn.setRollback();
				return false;
			}
			
			RowSet detList = getSUB1041T(acmpId, subContModId);
			
			/*
			 * 분산트렌젝션문제로 새로운 커넥션으로 ERP insert
			 */
			WAFConnectionImpl erpConn = new WAFConnectionImpl();
			
			try {
				erpConn.setDataSource("jdbc/erpiu");
				erpConn.begin();
				
				ValueObject erpVo = getErpBuiltKey(mstVo.get("CD_COMPANY"), mstVo.get("CD_SITE"), mstVo.get("TAX_YM"), mstVo.get("NO_CONT"));
				
				//자금청구 데이터가 없을때 INSERT
				if( erpVo.get("NO_BUILT").equals("") ) {
					erpVo = getNewErpBuiltKey(mstVo.get("CD_COMPANY"), mstVo.get("CD_SITE"), mstVo.get("TAX_YM"));
					ValueObject builtVo = getErpBuiltInfo(mstVo.get("CD_COMPANY"), mstVo.get("CD_SITE"), mstVo.get("TAX_YM"));
					builtVo.set("SQ_BUILT", erpVo.get("SQ_BUILT"));
					builtVo.set("NO_BUILT", erpVo.get("NO_BUILT"));
					InterfaceOutERPiU.insertErpBuilt(erpConn.getConnection(), mstVo, builtVo);
				}
				
				mstVo.set("SQ_BUILT",erpVo.get("SQ_BUILT"));
				mstVo.set("NO_BUILT",erpVo.get("NO_BUILT"));
				
				String seqBuilt = getSeqBuilt(mstVo.get("CD_COMPANY"), mstVo.get("CD_SITE"), mstVo.get("NO_CONT"), mstVo.get("SQ_BUILT"));
				mstVo.set("SEQ_BUILT",seqBuilt);
				
				InterfaceOutERPiU.insertErpSUB1040T(erpConn.getConnection(), mstVo); 
				InterfaceOutERPiU.insertErpSUB1040TSub(erpConn.getConnection(), mstVo);
				
				long mstAmtP = mstVo.getLong("SAMT_BUILT_P",0) + mstVo.getLong("FAMT_BUILT_P",0);
				long mstAmtS = mstVo.getLong("SAMT_BUILT_S",0) + mstVo.getLong("FAMT_BUILT_S",0);
				
				if( detList != null && detList.size() > 0 ) {
					for( int i = 0; i < detList.size(); i++ ) {
						
						ValueObject detVo = RowSetUtility.getValueObject(detList.getRow(i));
						
						long amtP = detVo.getLong("AMT_BUILT_P",0);
						long amtS = detVo.getLong("AMT_BUILT_S",0);
						
						/*
						 * 헤더금액 = SUM(상세금액)
						 * 헤더의 원단위 맞추기 위해 마지막내역 금액조정
						 */
						if( i == detList.size() - 1 ) {
							detVo.set("AMT_BUILT_P", mstAmtP);
							detVo.set("AMT_BUILT_S", mstAmtS);
						}
						
						InterfaceOutERPiU.insertErpSUB1041T(erpConn.getConnection(), mstVo, detVo);
						
						mstAmtP -= amtP;
						mstAmtS -= amtS;
						
					}
				}
				
			}catch(Exception e) {
				erpConn.setRollback();
				throw e;
			}finally {
				erpConn.close();
			}
			
			return true;
			
        }catch(Exception e) {
            super.doException(e);
            super.storage.setMessage(e.getMessage());
            super.storage.setDetailMessage(e);
            super.conn.setRollback();
            return false;
        }
        finally {
        	super.conn.end();
        }	
	}
	
	public ValueObject getSUB1040T(String acmpId, String subContModId) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sqlstr = new StringBuffer();
        ValueObject rowObject = new ValueObject();
        
        try {
        	sqlstr.append(" SELECT  '"+WAFConfig.get("waf.erp.cdCompany")+"' AS CD_COMPANY/*회사코드*/				\n");
        	sqlstr.append(" 		,NVL(C.ERP_SITE_CD,B.DEPT_CD) AS CD_SITE/*현장코드*/							\n");
        	sqlstr.append(" 		,B.ERP_NO_CONT AS NO_CONT/*계약번호*/											\n");
        	sqlstr.append(" 		,TO_CHAR(A.ACMP_NO,'FM00') AS SEQ_BUILT/*업체기성차수 :getSeqBuilt()에서 다시 조정됨.*/							\n");
        	sqlstr.append(" 		,TO_CHAR(A.ACMP_NO,'FM00') AS SEQ_BUILT_ETEC  /*업체기성차수 2019.04.02 추가 */							\n");
        	sqlstr.append(" 		,B.ERP_SQ_CONT AS SQ_CONT/*계약(품의)차수*/										\n");
        	sqlstr.append(" 		,A.ACMP_YYYYMM AS YM_BUILT/*기성년월*/											\n");
        	sqlstr.append(" 		,B.BUILD_NAME AS NM_CONT/*공사명(품의명)*/										\n");
        	sqlstr.append(" 		,'01' AS CD_CM00201/*계약(품의)형태*/											\n");
        	sqlstr.append(" 		,'03' AS CD_CM00213/*기성방법*/												\n");
        	sqlstr.append(" 		,E.ERP_CD_COOP AS CD_COOP/*협력업체코드*/										\n");
        	sqlstr.append(" 		,'' AS TP_COMPANY/*사업자구분*/												\n");
        	sqlstr.append(" 		,REPLACE(E.DCERTI_NO,'-') AS NO_COMPANY/*사업자번호*/							\n");
        	sqlstr.append(" 		,E.DEALR_NM AS NM_COOP/*거래처명*/												\n");
        	sqlstr.append(" 		,ROUND(B.CON_SUPPLY_AMT * NVL(H.EXCHANGE,1)) AS AMT_CONT/*계약금액*/					\n");
        	sqlstr.append(" 		,ROUND(B.CON_SUPPLY_TAMT * NVL(H.EXCHANGE,1)) AS SAMT_CONT/*공급가액*/				\n");
        	sqlstr.append(" 		,ROUND(DECODE(B.PROOF_CD,'Z99',0,B.CON_SURTAX_AMT * NVL(H.EXCHANGE,1))) AS VAMT_CONT/*부가세액*/					\n");
        	sqlstr.append(" 		,ROUND(B.CON_SUPPLY_NAMT * NVL(H.EXCHANGE,1)) AS FAMT_CONT/*면세금액*/				\n");
        	sqlstr.append(" 		,ROUND(F_GET_SUB_ACMP_LAST_SUM(A.ACMP_ID,'A')) AS AMT_BUILT_P/*전회기성*/				\n");
        	sqlstr.append(" 		,ROUND(F_GET_SUB_ACMP_LAST_SUM(A.ACMP_ID,'T')) AS SAMT_BUILT_P/*전회공급가*/			\n");
        	sqlstr.append(" 		,ROUND(DECODE(B.PROOF_CD,'Z99',0,F_GET_SUB_ACMP_LAST_SUM(A.ACMP_ID,'T') / 10)) AS VAMT_BUILT_P/*전회부가세*/		\n");
        	sqlstr.append(" 		,ROUND(F_GET_SUB_ACMP_LAST_SUM(A.ACMP_ID,'N')) AS FAMT_BUILT_P/*전회면세*/			\n");
        	sqlstr.append(" 		,ROUND(A.FUND_AMT * F_GET_EXCHANGE_RATE(TO_DATE(A.TAX_DY,'YYYYMMDD'),A.CON_CUR_CD,'KRW')) AS AMT_BUILT_S/*금회기성_현장*/			\n");
        	sqlstr.append(" 		,ROUND(A.FUND_TAMT * F_GET_EXCHANGE_RATE(TO_DATE(A.TAX_DY,'YYYYMMDD'),A.CON_CUR_CD,'KRW')) AS SAMT_BUILT_S/*금회공급가_현장*/		\n");
        	sqlstr.append(" 		,ROUND(DECODE(B.PROOF_CD,'Z99',0,A.FUND_TAMT * F_GET_EXCHANGE_RATE(TO_DATE(A.TAX_DY,'YYYYMMDD'),A.CON_CUR_CD,'KRW') / 10)) AS VAMT_BUILT_S/*금회부가세_현장*/	\n");
        	sqlstr.append(" 		,ROUND(A.FUND_NAMT * F_GET_EXCHANGE_RATE(TO_DATE(A.TAX_DY,'YYYYMMDD'),A.CON_CUR_CD,'KRW')) AS FAMT_BUILT_S/*금회면세_현장*/		\n");
        	sqlstr.append(" 		,ROUND(F_GET_SUB_PREPAY_LAST_SUM(A.ACMP_ID,'T')) AS AMT_D_PREPAY_P/*전회선급금공제*/										\n");
        	sqlstr.append(" 		,ROUND(DECODE(B.PROOF_CD,'Z99',0, DECODE(A.CON_CUR_CD,'VND',0,F_GET_SUB_PREPAY_LAST_SUM(A.ACMP_ID,'T')) / 10)) AS VAMT_D_PREPAY_P/*전회선급금공제 부가세*/	\n");
        	sqlstr.append(" 		,ROUND(F_GET_SUB_PREPAY_LAST_SUM(A.ACMP_ID,'N')) AS FAMT_D_PREPAY_P/*전회선급금공제(면세)*/									\n");
        	sqlstr.append(" 		,ROUND(F_GET_SUB_PREPAY_SUM(A.ACMP_ID,'T')) AS AMT_D_PREPAY_S/*금회선급금공제_현장*/											\n");
        	sqlstr.append(" 		,ROUND(DECODE(B.PROOF_CD,'Z99',0,DECODE(A.CON_CUR_CD,'VND',0,F_GET_SUB_PREPAY_SUM(A.ACMP_ID,'T')) / 10)) AS VAMT_D_PREPAY_S/*금회선급금공제 부가세_현장*/	\n");
        	sqlstr.append(" 		,ROUND(F_GET_SUB_PREPAY_SUM(A.ACMP_ID,'N')) AS FAMT_D_PREPAY_S/*금회선급금공제(면세)현장*/									\n");
        	sqlstr.append(" 		,NVL(TAX_DY,TO_CHAR(LAST_DAY(SYSDATE),'YYYYMMDD')) AS DT_REQUEST_S/*지급요청일*/									\n");
        	sqlstr.append(" 		,(SELECT USER_SABUN FROM PUB0120T WHERE ACCT_ID = A.CRE_BY) AS ID_INSERT/*등록자*/								\n");
        	sqlstr.append(" 		,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT/*등록일*/                          							\n");
        	sqlstr.append(" 		,'N' AS TAG_COPY_S/*현장자료복사여부(본사접수여부)*/                                        							\n");
        	sqlstr.append(" 		,'02' AS CD_CM00252/*외주기성구분*/                                                    								\n");
        	sqlstr.append(" 		,A.ACMP_REMARK AS DESC_BUILT/*비고*/                                                 								\n");
        	sqlstr.append(" 		,NVL(F.CHG_CODE10,A.CON_CUR_CD) AS CURR_SOUR/*환종*/                                 								\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,F_GET_EXCHANGE_RATE(TO_DATE(A.TAX_DY,'YYYYMMDD'),A.CON_CUR_CD,'KRW')) AS UNT_CURR/*환율*/				\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,A.PRE_FUND_TAMT) AS AMT_CURR_P/*전회공급가(외화) - 과세*/													\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,DECODE(B.PROOF_CD,'Z99',0,A.PRE_FUND_TAMT / 10)) AS VAMT_CURR_P/*전회부가세(외화)*/						\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,A.PRE_FUND_NAMT) AS FAMT_CURR_P/*전회공급가(외화) - 면세*/												\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,A.FUND_TAMT) AS AMT_CURR_S/*금회공급가(현장) - 과세*/														\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,DECODE(B.PROOF_CD,'Z99',0,A.FUND_TAMT / 10)) AS VAMT_CURR_S/*금회부가세(외화)_현장*/						\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,A.FUND_NAMT) AS FAMT_CURR_S/*금회공급가(현장) - 면세*/													\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,A.PRE_PAY_DEDUCT_TAMT) AS AMT_D_PREPAY_CURR_P/*전회선급금공제(외화) - 과세*/								\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,'VND',0,DECODE(B.PROOF_CD,'Z99',0,A.PRE_PAY_DEDUCT_TAMT / 10)) AS VAMT_D_PREPAY_CURR_P/*전회선급금공제 부가세*/	\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,A.PRE_PAY_DEDUCT_NAMT) AS FAMT_D_PREPAY_CURR_P/*전회선급금공제(외화) - 면세*/								\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,A.PAY_DEDUCT_TAMT) AS AMT_D_PREPAY_CURR_S/*금회선급금공제(외화)_현장 - 과세*/								\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,'VND',0,DECODE(B.PROOF_CD,'Z99',0,A.PAY_DEDUCT_TAMT / 10)) AS VAMT_D_PREPAY_CURR_S/*금회선급금공제 부가세_현장*/	\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,A.PAY_DEDUCT_NAMT) AS FAMT_D_PREPAY_CURR_S/*금회선급금공제(외화)_현장 - 면세*/								\n");
        	sqlstr.append(" 		,A.ACMP_ID             													\n");
        	sqlstr.append(" 		,SUBSTR(A.TAX_DY,1,6) AS TAX_YM											\n");
        	sqlstr.append(" 		,NVL(F.CHG_CODE10,A.CON_CUR_CD) AS CD_EXCH																			\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,F_GET_EXCHANGE_RATE(TO_DATE(A.TAX_DY,'YYYYMMDD'),A.CON_CUR_CD,'KRW')) AS RT_EXCH	\n");
        	sqlstr.append(" 		,DECODE(A.CON_CUR_CD,'KRW',NULL,F_GET_EXCHANGE_DATE(TO_DATE(A.TAX_DY,'YYYYMMDD'),A.CON_CUR_CD,'KRW')) AS DT_EXCH	\n");
        	//2019.07.29 ERP 자동분개를 위한 계정코드
        	sqlstr.append("         ,CASE WHEN B.CON_CUR_CD = 'KRW' THEN  											\n");
        	sqlstr.append("                    CASE WHEN NVL(A.SUPP_DIRECT_YN,'') = 'Y' THEN NVL(J.CHG_CODE16, '')  \n");
        	sqlstr.append("                         ELSE NVL(J.CHG_CODE14, '') 										\n");
        	sqlstr.append("                    END                     												\n");
        	sqlstr.append("               ELSE CASE WHEN NVL(A.SUPP_DIRECT_YN,'') = 'Y' THEN NVL(J.CHG_CODE17, '')  \n");
        	sqlstr.append("                         ELSE NVL(J.CHG_CODE15, '') 										\n");
        	sqlstr.append("               END  																		\n");
        	sqlstr.append("          END AS CD_COST_TRADE 															\n");
        	sqlstr.append(" 		,NVL(A.LABOR_USE_YN,'N') AS LABOR_USE_YN																			\n");	//2022.03.21
        	sqlstr.append(" FROM    SUB1040T A                                                              \n");
        	sqlstr.append(" 		,SUB2000T B                                                             \n");
        	sqlstr.append(" 		,ETEC_ERPIF.SITE_MAPPING C                                              \n");
        	sqlstr.append(" 		,SUB1020T D                                                             \n");
        	sqlstr.append(" 		,ETEC_ERPIF.ACZ10800 E                                                  \n");
        	sqlstr.append(" 		,(SELECT DETAILCD, CHG_CODE10 FROM EBID_COM911T WHERE BASECD = '911') F \n");
//        	sqlstr.append(" 		,CONT_OUT_SUPP G                                                        \n");	//계약이관 변경계약시 기성신청 문제조치
        	sqlstr.append(" 		,(SELECT MIN(CORP_ID) CORP_ID,MAX(SUB_CONT_MOD_ID) SUB_CONT_MOD_ID		\n");	//CORP_ID : 입찰시 업체ID, SUB_CONT_MOD_ID : 최근 변경계약 ID
        	sqlstr.append(" 		    FROM CONT_OUT_SUPP													\n");
        	sqlstr.append(" 		   WHERE SUB_CONT_MOD_ID IN (SELECT SUB_CONT_MOD_ID FROM SUB2000T WHERE STATUS_CD = '50' AND SUB_CONT_ID IN(SELECT SUB_CONT_ID FROM SUB2000T WHERE SUB_CONT_MOD_ID = ?))	\n");
        	sqlstr.append(" 		     AND corp_id != 0) G												\n");
        	sqlstr.append(" 		,BID_SUPP H                                                             \n");
        	sqlstr.append(" 		,SUPP_INFO I                                                            \n");
        	sqlstr.append("         ,(SELECT DETAILCD, CHG_CODE14, CHG_CODE15, CHG_CODE16, CHG_CODE17 FROM EBID_COM911T WHERE BASECD = 'SUB_ACCT_GB' ) J \n");
        	sqlstr.append(" 		,SUB2000T K                                                             \n");
        	sqlstr.append(" WHERE   A.SUB_CONT_ID = B.SUB_CONT_ID                                           \n");
        	sqlstr.append(" AND     B.DEPT_CD = C.EBID_SITE_CD(+)                                           \n");
        	sqlstr.append(" AND     A.CON_CORP_ID = D.CON_CORP_ID                                           \n");
        	sqlstr.append(" AND     D.CORP_ID = I.CORP_ID                                             		\n");
        	sqlstr.append(" AND     REPLACE(I.CORP_REG_NO,'-','') = REPLACE(E.DCERTI_NO,'-','')				\n");
        	sqlstr.append(" AND     E.ADDITEM20 = 'O'               										\n"); //2019.02.26
        	sqlstr.append(" AND     A.CON_CUR_CD = F.DETAILCD(+)                                            \n");
        	sqlstr.append(" AND     A.SUB_CONT_ID = K.SUB_CONT_ID                                           \n");
//        	sqlstr.append(" AND     K.MOD_NO = 0                                                            \n");
        	sqlstr.append(" AND     B.MOD_NO = K.MOD_NO                                                     \n");
        	sqlstr.append(" AND     K.SUB_CONT_MOD_ID  = G.SUB_CONT_MOD_ID                                  \n");
//        	sqlstr.append(" AND     G.CORP_ID != 0                                                          \n");
        	sqlstr.append(" AND     B.BID_NO = H.BID_NO                                                     \n");
        	sqlstr.append(" AND     B.BID_DEG = H.BID_DEG                                                   \n");
//        	sqlstr.append(" AND     TO_CHAR(G.CORP_ID) = H.VENDOR_CD                                        \n");
        	sqlstr.append(" AND     H.SUCC_YN = 'Y'                                                         \n");	////계약이관 변경계약시 기성신청 문제조치
        	sqlstr.append(" AND     B.ACCT_GB = J.DETAILCD(+) 												\n");
        	sqlstr.append(" AND     A.ACMP_ID = ?                                                           \n");
        	sqlstr.append(" AND     B.SUB_CONT_MOD_ID = ?                                                   \n");
        	
        	System.out.println("subContModId : "+subContModId);
        	System.out.println("acmpId : "+acmpId);
            search.setStatement(sqlstr);
            search.addParameter(subContModId);
            search.addParameter(acmpId);
            search.addParameter(subContModId);
            listRowSet = search.execute();
            if(listRowSet.size() != 0){
            	rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
            }
            
            return rowObject;
            
        }catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getSUB1040T() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return rowObject;
        }
    }
	
	public ValueObject checkErpClose(String cdCompany, String cdSite, String taxYm) {
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		StringBuffer sqlstr = new StringBuffer();
		ValueObject rowObject = new ValueObject();
		
		try {
			sqlstr.append("SELECT  NVL(B.TAG_CLOSE,'N') AS TAG_CLOSE				\n");
			sqlstr.append("FROM    WC_S_BUILT_SQ_H@ERPIU A							\n");
			sqlstr.append("        ,WC_S_BUILT_SQ_D@ERPIU B							\n");
			sqlstr.append("WHERE   A.CD_COMPANY = B.CD_COMPANY						\n");
			sqlstr.append("AND     A.CD_SITE = B.CD_SITE							\n");
			sqlstr.append("AND     A.SQ_BUILT = B.SQ_BUILT							\n");
			sqlstr.append("AND     A.CD_COMPANY = '"+cdCompany+"'					\n");
			sqlstr.append("AND     A.CD_SITE = F_GET_ERP_SITE_CODE('"+cdSite+"')	\n");
			sqlstr.append("AND     A.YM_BUILT = '"+taxYm+"'							\n");
			sqlstr.append("AND     B.CD_CM00008 = '003'								\n");
			sqlstr.append("AND     B.SEQ_BUILT = '01'								\n");
			search.setStatement(sqlstr);
			
			WAFLogger.debug("자금청구 데이터 쿼리 시작");
			WAFLogger.debug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
			WAFLogger.debug(sqlstr.toString());
			WAFLogger.debug("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
			
			listRowSet = search.execute();
			if(listRowSet.size() != 0){
				rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
				WAFLogger.debug(rowObject);
			}
			
			return rowObject;
			
		}catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> checkErpClose() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return rowObject;
		}
	}
	
	public ValueObject getErpBuiltKey(String cdCompany, String cdSite, String taxYm, String noCont) {
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		StringBuffer sqlstr = new StringBuffer();
		ValueObject rowObject = new ValueObject();
		
		try {
			sqlstr.append("SELECT  SQ_BUILT																		\n");
			sqlstr.append("        ,NO_BUILT																	\n");
			sqlstr.append("FROM    (																			\n");
			sqlstr.append("        SELECT  ROW_NUMBER() OVER(ORDER BY A.SQ_BUILT, B.NO_BUILT) AS IDX			\n");
			sqlstr.append("                ,A.SQ_BUILT															\n");
			sqlstr.append("                ,B.NO_BUILT															\n");
			sqlstr.append("        FROM    WC_S_BUILT_SQ_H@ERPIU A												\n");
			sqlstr.append("                ,WC_S_BUILT_SQ_D@ERPIU B												\n");
			sqlstr.append("        WHERE   A.CD_COMPANY = B.CD_COMPANY											\n");
			sqlstr.append("        AND     A.CD_SITE = B.CD_SITE												\n");
			sqlstr.append("        AND     A.SQ_BUILT = B.SQ_BUILT												\n");
			sqlstr.append("        AND     A.CD_COMPANY = '"+cdCompany+"'										\n");
			sqlstr.append("        AND     A.CD_SITE = F_GET_ERP_SITE_CODE('"+cdSite+"')												\n");
			sqlstr.append("        AND     A.YM_BUILT = '"+taxYm+"'												\n");
			sqlstr.append("        AND     B.CD_CM00008 = '003'													\n");
			sqlstr.append("        AND     (B.SQ_BUILT, B.NO_BUILT) NOT IN (									\n");
			sqlstr.append("                                                SELECT  SQ_BUILT, NO_BUILT			\n");
			sqlstr.append("                                                FROM    WC_O_BUILT_H@ERPIU			\n");
			sqlstr.append("                                                WHERE   CD_COMPANY = '"+cdCompany+"'	\n");
			sqlstr.append("                                                AND     CD_SITE = F_GET_ERP_SITE_CODE('"+cdSite+"')		\n");
			sqlstr.append("                                                AND     NO_CONT = '"+noCont+"'		\n");
			sqlstr.append("                                                AND     YM_BUILT = '"+taxYm+"'		\n");
			sqlstr.append("                                                )									\n");
			sqlstr.append("        )																			\n");
			sqlstr.append("WHERE   IDX = 1																		\n");
			
			search.setStatement(sqlstr);
			listRowSet = search.execute();
			if(listRowSet.size() != 0){
				rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
			}
			
			return rowObject;
			
		}catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> getErpBuiltKey() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return rowObject;
		}
	}
	
	public ValueObject getNewErpBuiltKey(String cdCompany, String cdSite, String taxYm) {
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		StringBuffer sqlstr = new StringBuffer();
		ValueObject rowObject = new ValueObject();
		
		try {
			sqlstr.append("SELECT  MAX(A.SQ_BUILT) AS SQ_BUILT											\n");
			sqlstr.append("		   ,TO_CHAR(NVL(MAX(TO_NUMBER(B.NO_BUILT)),0) + 1,'FM00') AS NO_BUILT	\n");
			sqlstr.append("FROM    WC_S_BUILT_SQ_H@ERPIU A												\n");
			sqlstr.append("        ,WC_S_BUILT_SQ_D@ERPIU B												\n");
			sqlstr.append("WHERE   A.CD_COMPANY = B.CD_COMPANY											\n");
			sqlstr.append("AND     A.CD_SITE = B.CD_SITE												\n");
			sqlstr.append("AND     A.SQ_BUILT = B.SQ_BUILT												\n");
			sqlstr.append("AND     A.CD_COMPANY = '"+cdCompany+"'										\n");
			sqlstr.append("AND     A.CD_SITE = F_GET_ERP_SITE_CODE('"+cdSite+"')												\n");
			sqlstr.append("AND     A.YM_BUILT = '"+taxYm+"'												\n");
			
			search.setStatement(sqlstr);
			listRowSet = search.execute();
			if(listRowSet.size() != 0){
				rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
			}
			
			return rowObject;
			
		}catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> getNewErpBuiltKey() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return rowObject;
		}
	}
	
	public String getSeqBuilt(String cdCompany, String cdSite, String noCont, String sqBuilt) {
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		StringBuffer sqlstr = new StringBuffer();
		String seqBuilt = "";
		
		try {
			sqlstr.append("SELECT  TO_CHAR(NVL(MAX(TO_NUMBER(SEQ_BUILT)),0) + 1,'FM00') AS SEQ_BUILT	\n");
			sqlstr.append("FROM    WC_O_BUILT_H@ERPIU													\n");
			sqlstr.append("WHERE   CD_COMPANY = '"+cdCompany+"'											\n");
			sqlstr.append("AND     CD_SITE = '"+cdSite+"'												\n");
			sqlstr.append("AND     NO_CONT = '"+noCont+"'												\n");
			sqlstr.append("AND     SQ_BUILT = '"+sqBuilt+"'												\n");
			
			search.setStatement(sqlstr);
			listRowSet = search.execute();
			if(listRowSet.size() != 0){
				seqBuilt = listRowSet.getRow(0).get("SEQ_BUILT");
			}
			
			return seqBuilt;
			
		}catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> getSeqBuilt() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return seqBuilt;
		}
	}
	
	public ValueObject getErpBuiltInfo(String cdCompany, String cdSite, String taxYm) {
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		StringBuffer sqlstr = new StringBuffer();
		ValueObject rowObject = new ValueObject();
		
		try {
	
			sqlstr.append("SELECT  TO_CHAR(MAX(TO_NUMBER(B.SEQ_BUILT)) + 1,'FM00') AS SEQ_BUILT					\n");
			sqlstr.append("        ,'"+taxYm+"'||'01' AS DT_FM_FUND												\n");
			sqlstr.append("        ,TO_CHAR(LAST_DAY(TO_DATE('"+taxYm+"','YYYYMM')),'YYYYMMDD') AS DT_TO_FUND	\n");
			sqlstr.append("        ,TO_CHAR(ADD_MONTHS(TO_DATE('"+taxYm+"','YYYYMM'),1),'YYYYMM') AS YM_PAY		\n");
			sqlstr.append("        ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT							\n");
			sqlstr.append("FROM    WC_S_BUILT_SQ_H@ERPIU A														\n");
			sqlstr.append("        ,WC_S_BUILT_SQ_D@ERPIU B														\n");
			sqlstr.append("WHERE   A.CD_COMPANY = B.CD_COMPANY													\n");
			sqlstr.append("AND     A.CD_SITE = B.CD_SITE														\n");
			sqlstr.append("AND     A.SQ_BUILT = B.SQ_BUILT														\n");
			sqlstr.append("AND     A.CD_COMPANY = '"+cdCompany+"'												\n");
			sqlstr.append("AND     A.CD_SITE = F_GET_ERP_SITE_CODE('"+cdSite+"')														\n");
			sqlstr.append("AND     A.YM_BUILT = '"+taxYm+"'														\n");
			sqlstr.append("AND     B.CD_CM00008 = '003'															\n");
			
			search.setStatement(sqlstr);
			listRowSet = search.execute();
			if(listRowSet.size() != 0){
				rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
			}
			
			return rowObject;
			
		}catch(Exception e) {
			WAFLogger.error(this.getClass().getName() + " --> getErpBuiltInfo() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			return rowObject;
		}
	}
	
	public RowSet getSUB1041T(String acmpId, String subContModId) throws Exception{
		Search search = new SearchImpl();
		RowSet listRowSet = null;
		StringBuffer sqlstr = new StringBuffer();
		
		try {
			sqlstr.append(" SELECT  C.ERP_SQ_CONT AS SQ_CONT                                                                           \n");
			sqlstr.append("         ,TO_CHAR(ROWNUM,'FM00000') AS NO_BUILT                                                             \n");
			sqlstr.append("         ,'01' AS CD_CM00201                                                                                 \n");
			sqlstr.append("         ,'01' AS CD_CM00208                                                                                 \n");
			sqlstr.append("         ,G.CD_CSTR                                                                                          \n");
			sqlstr.append("         ,G.DL_CSTR                                                                                          \n");
			sqlstr.append("         ,G.NM_CSTR                                                                                          \n");
			sqlstr.append("         ,G.CD_SIZE                                                                                          \n");
			sqlstr.append("         ,G.SIZE_CSTR                                                                                        \n");
			sqlstr.append("         ,G.UNIT_CSTR                                                                                        \n");
			sqlstr.append("         ,1 AS QTY_CONT                                                                         				\n");
			sqlstr.append("         ,G.SUPPLY_AMT AS UNT_CONT                                                                         \n");
			sqlstr.append("         ,1 * G.SUPPLY_AMT AS AMT_CONT                                                        				\n");
			sqlstr.append("         ,DECODE(NVL(B.BEF_TAMT,0),0,0,1) AS QTY_BUILT_P                                                     \n");
			sqlstr.append("         ,ROUND(F_GET_SUB_ACMP_DETAIL_LAST_SUM(B.ACMP_ID,B.CD_CSTR,B.DL_CSTR,B.MATERIAL_CD)) AS AMT_BUILT_P		\n");
			sqlstr.append("         ,1 AS QTY_BUILT_S                                                                 						\n");
			sqlstr.append("         ,ROUND((CUR_TAMT + CUR_NAMT) * F_GET_EXCHANGE_RATE(TO_DATE(A.TAX_DY, 'YYYYMMDD'),A.CON_CUR_CD,'KRW')) AS AMT_BUILT_S			\n");
			sqlstr.append("         ,1 AS QTY_BUILT                                                                         			\n");
			sqlstr.append("         ,(SELECT USER_SABUN FROM PUB0120T WHERE ACCT_ID = B.CRE_BY) AS ID_INSERT                            \n");
			sqlstr.append("         ,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS DTS_INSERT                                                  \n");
			sqlstr.append("         ,'001' AS CD_CM00006                                                                                \n");
			sqlstr.append("         ,CUR_TAMT + CUR_NAMT AS AMT_CURR_S             \n");
			sqlstr.append(" FROM    SUB1040T A                                                                                          \n");
			sqlstr.append("         ,SUB1041T B                                                                                         \n");
			sqlstr.append("         ,SUB2000T C                                                                                         \n");
			sqlstr.append("         ,CONT_OUT_ITEM G                                                                              		\n");
			sqlstr.append(" WHERE   A.ACMP_ID = B.ACMP_ID                                                                               \n");
			sqlstr.append(" AND     A.SUB_CONT_ID = C.SUB_CONT_ID                                                                       \n");
			sqlstr.append(" AND     B.SUB_CONT_ID = G.SUB_CONT_ID                                                               		\n");
			sqlstr.append(" AND     B.MATERIAL_CD = G.MATERIAL_CD                                                               		\n");
			sqlstr.append(" AND     C.SUB_CONT_MOD_ID = G.SUB_CONT_MOD_ID                                                          		\n");
			sqlstr.append(" AND     A.ACMP_ID = ?                                                                                       \n");
			sqlstr.append(" AND     C.SUB_CONT_MOD_ID = ?                                                                               \n");
	
			search.setStatement(sqlstr);
			search.addParameter(acmpId);
			search.addParameter(subContModId);
			listRowSet = search.execute();
			
			return listRowSet;
			
		}catch(Exception e) {
			
			WAFLogger.error(this.getClass().getName() + " --> getSUB1041T() : " + e.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(e);
			throw e;
		}
	}
	
	private boolean AmtCompare(String acmpId) {

		Search search = new SearchImpl();
        RowSet rowSet = null;
        ValueObject valueobject = null;
        StrBuffer sqlstr = new StrBuffer();
        double NAmt = 0;
        double TAmt = 0;
        double AmtZero = 0;
        try{
        	sqlstr.append(" SELECT ROUND((NVL(A.FUND_TAMT,0) - NVL(B.TAMT  ,0))* 100000 ,0)         \n");
        	sqlstr.append("       ,ROUND((NVL(A.FUND_NAMT,0) - NVL(B.NAMT  ,0))* 100000 ,0)         \n");
        	sqlstr.append("       ,NVL(A.FUND_TAMT, 0)                                              \n");
        	sqlstr.append("       ,NVL(A.FUND_NAMT, 0)                                              \n");
        	sqlstr.append("   FROM SUB1040T A                                                          \n");
        	sqlstr.append("      ,(SELECT SUM(NVL(CUR_TAMT,0)) TAMT                                    \n");
        	sqlstr.append("              ,SUM(NVL(CUR_NAMT,0)) NAMT                                    \n");
        	sqlstr.append("          FROM SUB1041T B                                                   \n");
        	sqlstr.append("         WHERE B.ACMP_ID = '" + acmpId + "') B                              \n");
        	sqlstr.append("  WHERE A.ACMP_ID = '" + acmpId + "'                                       \n");
        	
        	search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            if( rowSet != null && rowSet.size() > 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            	TAmt = valueobject.getDouble(0);
            	NAmt = valueobject.getDouble(1);
            	AmtZero = valueobject.getDouble(2) + valueobject.getDouble(3); //0원기성처리 2019.05.13  
            }
            
            if (AmtZero == 0 ){
            	try {
	            	super.conn.begin();
					Connection con = super.conn.getConnection();				
	            	PreparedStatement pstmt = null;
				    StrBuffer sql = new StrBuffer();
				    
	            	//insert update sub1041t
	            	sql.append("  MERGE INTO SUB1041T A                                                                                     \n");
	            	sql.append("  USING ( SELECT MAX(ACMP_ID) ACMP_ID                                                                       \n");
	                sql.append("                ,MAX(CD_CSTR) CD_CSTR                                                                       \n");
	                sql.append("                ,MAX(DL_CSTR) DL_CSTR                                                                       \n");
	                sql.append("                ,MAX(MATERIAL_CD) MATERIAL_CD                                                               \n");
	                sql.append("                ,SUB_CONT_ID  SUB_CONT_ID                                                                   \n");
	                sql.append("                ,SUM(CUR_TAMT) BEF_TAMT                                                                     \n");
	                sql.append("                ,SUM(CUR_NAMT) BEF_NAMT                                                                     \n");
	                sql.append("                ,MAX(CD_SITE) CD_SITE                                                                       \n");
	                sql.append("                FROM SUB1041T                                                                               \n");
	                sql.append("                WHERE SUB_CONT_ID = (SELECT MAX(SUB_CONT_ID) FROM SUB1040T WHERE ACMP_ID =? )               \n");
	                sql.append("                GROUP BY SUB_CONT_ID ) B                                                                    \n");
	            	sql.append("  ON (A.ACMP_ID = B.ACMP_ID                                                                                 \n");
	            	sql.append("   AND A.ACMP_ID = ?)                                                                                       \n");
	            	sql.append("  WHEN NOT MATCHED THEN                                                                                     \n");
	            	sql.append("       INSERT (ACMP_ID, CD_CSTR, DL_CSTR, SUB_CONT_ID, MATERIAL_CD                                          \n");
	            	sql.append("		      ,BEF_TAMT, BEF_NAMT, CUR_TAMT, CUR_NAMT, TOT_TAMT   	                                        \n");
	            	sql.append("		      ,TOT_NAMT, CRE_BY, CRE_DATE, CD_SITE )               	                                        \n");
	            	sql.append("        VALUES(? , B.CD_CSTR, B.DL_CSTR, B.SUB_CONT_ID, B.MATERIAL_CD 		                                \n");
	            	sql.append("               ,B.BEF_TAMT, B.BEF_NAMT, 0, 0, 0                                                             \n");
	            	sql.append("               ,0 , '', SYSDATE, B.CD_SITE  )                                                               \n");
	
	            	pstmt = con.prepareStatement(sql.toString()); 
	            	int paramIdx = 1;
	    		    
	    			pstmt.setString(paramIdx++, acmpId);
	    			pstmt.setString(paramIdx++, acmpId);
	    			pstmt.setString(paramIdx++, acmpId);
	    			pstmt.executeUpdate();
            	}catch (Exception e) {
    				WAFLogger.error(e);
    				super.conn.setRollback();
    				throw new Exception(e);
    			} finally {
    				super.conn.close();
    			}
            }
            	
            if (NAmt == 0 && TAmt == 0) return true;
            else return false;
        }
        catch(Exception se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se); 
        }
        finally {
        	if( valueobject == null ) return false;
        } 
        return false;
	}
	
	public ValueObject checkChgCont(String acmpId, String subContModId) {
        Search search = new SearchImpl();
        RowSet listRowSet = null;
        StringBuffer sqlstr = new StringBuffer();
        ValueObject rowObject = new ValueObject();
        
        try {
        	sqlstr.append(" SELECT DECODE(COUNT(*),'0','N','Y') AS CHK_CONT					\n");
        	sqlstr.append("   FROM (SELECT MAX(B.SUB_CONT_MOD_ID) AS MAX_SUB_CONT_MOD_ID 	\n");
        	sqlstr.append(" 		  FROM SUB1040T A, SUB2000T B							\n");
        	sqlstr.append(" 		 WHERE A.ACMP_ID =	?									\n");
        	sqlstr.append(" 		   AND A.SUB_CONT_ID = B.SUB_CONT_ID) AA				\n");
        	sqlstr.append(" WHERE MAX_SUB_CONT_MOD_ID = ?									\n");
        	
            search.setStatement(sqlstr);
            search.addParameter(acmpId);
            search.addParameter(subContModId);
            listRowSet = search.execute();
            if(listRowSet.size() != 0){
            	rowObject = RowSetUtility.getValueObject(listRowSet.getRow(0));
            }
            
            return rowObject;
            
        }catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> getSUB1040T() : " + e.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(e);
            return rowObject;
        }
    }
	
	private boolean PreAmtCompare(String acmpId) {

		Search search = new SearchImpl();
        RowSet rowSet = null;
        ValueObject valueobject = null;
        StrBuffer sqlstr = new StrBuffer();
        String PRE_AMT_CHK_CNT = "";
        try{
        	sqlstr.append(" SELECT COUNT(*) AS CNT                                     \n");
        	sqlstr.append("   FROM SUB1040T                                            \n");
        	sqlstr.append("  WHERE PAY_DEDUCT_TAMT+PAY_DEDUCT_NAMT <> PAY_DEDUCT_AMT   \n");
        	sqlstr.append("    AND ACMP_ID = '" + acmpId + "'                          \n");
        	
        	search.setStatement(sqlstr.toString());
            rowSet = search.execute();
            if( rowSet.size() != 0 ){
            	valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
            	PRE_AMT_CHK_CNT = valueobject.get("CNT");
            }

            if(PRE_AMT_CHK_CNT.equals("0"))	return true;
            else							return false;	
        }
        catch(Exception se) {
        	super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se); 
        }
        finally {
        	if( valueobject == null ) return false;
        } 
        return false;
	}
}

