/**
 * @(#)file    ContPriceSearch.java
 * @(#)author  Seokjin
 * @(#)version 1.0
 * @(#)date    2015-01-09
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.cont.purchase.biz;


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

import org.jdom.Document;
import org.jdom.Element;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;

import oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.util.ValueObject;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.modular.web.ibsheet7.util.IBSheet7Utils;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class ContBudgetMRBiz extends Biz {

	public ContBudgetMRBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
		if(storage.compareEvent("GET_ITEM")) {
			RowSet rowSet = getRowSet();
			storage.addRowSet("ROWSET", rowSet);
	        storage.setSuccess(true);
		}else if(storage.compareEvent("BUDGET_CONTROL")){
			RowSet rowset = null;
            rowset = getBudgetList(params);
            Document xmlData = getXml(rowset);
            XMLOutputter xmlOutputter = new XMLOutputter();
            xmlOutputter.setFormat(Format.getRawFormat());
            storage.addObject("ibsheet7Data", xmlData);
            
        }else if(storage.compareEvent("BUDGET_SAVE")){
        	boolean success = false;
        	String[] status = super.gets("sheet1.STATUS");
        	try {
        		super.conn.begin();
    			for (int i = 0; i < status.length; i++) {
    				if (IBSheet7Utils.equalStatusInsert(status[i])) {
    					success = InsertData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
    				}
    				if (IBSheet7Utils.equalStatusUpdate(status[i])) {
    					success = updateData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
    				}
    				if (IBSheet7Utils.equalStatusDelete(status[i])) {
    					success = deleteData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
    				}
    			}	
    			super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        	} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
        }else if(storage.compareEvent("BUDGET_CONTROL_CO")){
        	params.set("sheet2", getBudgetListCo(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));			
        }else if(storage.compareEvent("BUDGET_SAVE_CO")){
        	boolean success = false;
        	String[] status = super.gets("sheet2.STATUS");
        	try {
        		super.conn.begin();
    			for (int i = 0; i < status.length; i++) {
    				if (IBSheet7Utils.equalStatusInsert(status[i])) {
    					success = InsertDataCo(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
    				}
    				if (IBSheet7Utils.equalStatusUpdate(status[i])) {
    			 		success = updateDataCo(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
    				}
    				if (IBSheet7Utils.equalStatusDelete(status[i])) {
    					success = deleteDataCo(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
    				}
    			}	
    			super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        	} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
        }
	}
	
	private RowSet getRowSet() {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlstr = new StringBuffer();
        try {
        	sqlstr.append("SELECT A.PAY_ITEM_SEQ, A.ITEM_NAME , NVL(B.PAY_ITEM_SEQ,0)              \n");
        	sqlstr.append("  FROM BCS_MASTER A                                                     \n");
        	sqlstr.append("      ,(SELECT A.*                                                      \n");
        	sqlstr.append("              ,RANK() OVER ( PARTITION BY PAY_ITEM_SEQ ORDER BY MR_ID DESC) RNK  \n");
        	sqlstr.append("          FROM BCS_MR A                                                 \n");
        	sqlstr.append("         WHERE DEPT_CD = '" + params.get("P_DEPT_CD") + "' ) B          \n");
        	sqlstr.append(" WHERE A.DEPT_CD = B.DEPT_CD(+)                                         \n");
        	sqlstr.append("   AND A.PAY_ITEM_SEQ = B.PAY_ITEM_SEQ(+)                               \n");
        	sqlstr.append("   AND B.RNK(+) = 1                                                     \n");
        	sqlstr.append("   AND A.DEPT_CD = '" + params.get("P_DEPT_CD") + "'                    \n");
        	sqlstr.append("   AND A.KIND_GB LIKE '" + params.get("P_KIND_GB") + "%'                \n");
        	sqlstr.append("	  ORDER BY A.ITEM_NAME                                                 \n");
        	
        	System.out.println(sqlstr.toString());
        	search.setStatement(sqlstr);
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
	
	private RowSet getBudgetListCo(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT B.MR_ID																   \n");
        	sqlstr.append("      , B.MR_NO																   \n");
        	sqlstr.append("      , B.TITLE																   \n");
        	sqlstr.append("      , A.PAY_ITEM_SEQ 													       \n");
        	sqlstr.append("      , C.EXEAMT	   														       \n");
        	sqlstr.append("      , B.CONAMT																   \n");
        	sqlstr.append("      , A.COAMT																   \n");
        	sqlstr.append("      , A.STATUS_CD															   \n");
        	sqlstr.append("      , A.REMARK1															   \n");
        	sqlstr.append("      , A.UPD_BY  															   \n");
        	sqlstr.append("      , A.UPD_DATE															   \n");
            sqlstr.append("        , A.DEPT_CD															   \n");
            sqlstr.append("        , A.SEQ																   \n");
            sqlstr.append("  FROM BCS_CO A																   \n");
            sqlstr.append("     , (SELECT A.*, RANK() OVER (PARTITION BY PAY_ITEM_SEQ ORDER BY MR_ID DESC ) RNK      \n");
            sqlstr.append("          FROM BCS_MR  A 													   \n");
            sqlstr.append("         WHERE A.DEPT_CD = '" + params.get("P_DEPT_CD") + "') B			       \n");
            sqlstr.append("     , BCS_MASTER C															   \n");
            sqlstr.append(" WHERE A.DEPT_CD = B.DEPT_CD													   \n");
            sqlstr.append("   AND A.PAY_ITEM_SEQ = B.PAY_ITEM_SEQ										   \n");
            sqlstr.append("   AND B.DEPT_CD = C.DEPT_CD													   \n");
            sqlstr.append("   AND B.PAY_ITEM_SEQ=  C.PAY_ITEM_SEQ 										   \n");
            sqlstr.append("   AND B.RNK = 1																   \n");
            sqlstr.append("   AND A.DEPT_CD = '" + params.get("P_DEPT_CD") + "'    						   \n");
            sqlstr.append("   AND B.KIND_GB LIKE '" + params.get("P_KIND_GB") + "%' 					   \n");
            
            System.out.println(sqlstr.toString());
            search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	} 
	
	
	private RowSet getBudgetList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" WITH BCS_MR_LAST AS (                                                                                \n");
        	sqlstr.append(" 	SELECT A.*,RANK() OVER (PARTITION BY A.PAY_ITEM_SEQ ORDER BY A.MR_ID DESC )  RNK                 \n");                
        	sqlstr.append(" 	  FROM BCS_MR A                                                                                  \n");                                                      
        	sqlstr.append(" 	 WHERE DEPT_CD = '" + params.get("P_DEPT_CD") + "'                                               \n");
        	sqlstr.append(" 	 )                                                                                               \n");
        	sqlstr.append(" SELECT ORD, DEPT_CD, PAY_ITEM, PAY_ITEM_SEQ, MR_ID, MR_NO, ITEM_NAME, EXEAMT, CONAMT                 \n");
        	sqlstr.append("       ,CONDATE, COAMT, RATE, SAVEAMT, CORATE, COSAVEAMT, CURRENCY_GB, VENDOR_NM                      \n");
        	sqlstr.append("       ,REMARK1, TITLE, UPD_DATE, GROUP_SEQ, KIND_GB ,ORDER_SEQ, DECODE(GROUP_SEQ, 1,1,2) GROUP_SEQ1  \n");
        	sqlstr.append("   FROM (                                                                                             \n");
        	sqlstr.append(" SELECT 2 ORD, A.DEPT_CD                                                                              \n");//예산기준 
        	sqlstr.append("       ,A.PAY_ITEM                                                                                    \n");
        	sqlstr.append("       ,A.PAY_ITEM_SEQ                                                                                \n");
        	sqlstr.append("       ,NVL(B.MR_ID, 0) MR_ID                                                                         \n");
        	sqlstr.append("       ,B.MR_NO                                                                                       \n");
        	sqlstr.append("       ,A.ITEM_NAME                                                                                   \n");
        	sqlstr.append("       ,A.EXEAMT                                                                                      \n");
        	sqlstr.append("       ,B.CONAMT                                                                                      \n");
        	sqlstr.append("       ,TO_CHAR(B.CONDATE, 'YYYY-MM-DD') CONDATE                                                      \n");
        	sqlstr.append("       ,C.COAMT                                                                                       \n");
        	sqlstr.append("       ,DECODE(A.EXEAMT,0, 0, ROUND(B.CONAMT/ A.EXEAMT * 100,2))  RATE                                                       \n");	   
        	sqlstr.append("       ,A.EXEAMT - B.CONAMT                SAVEAMT                                                    \n");
        	sqlstr.append("       ,DECODE(A.EXEAMT+NVL(C.COAMT,0),0, 0,ROUND(B.CONAMT/ (A.EXEAMT+NVL(C.COAMT,0)) * 100,2)) CORATE                                     \n");
        	sqlstr.append("       ,A.EXEAMT+NVL(C.COAMT,0) - B.CONAMT        COSAVEAMT                                           \n");
        	sqlstr.append("       ,B.CURRENCY_GB                                                                                 \n");
        	sqlstr.append("       ,B.VENDOR_NM                                                                                   \n");
            sqlstr.append("       ,B.REMARK1                                                                                     \n");
            sqlstr.append("       ,B.TITLE                                                                                       \n");
            sqlstr.append("       ,TO_CHAR(B.UPD_DATE, 'YYYY-MM-DD') UPD_DATE                                                    \n");
            sqlstr.append("       ,DECODE(NVL(B.RNK, 1), 1, 1, B.RNK) GROUP_SEQ                                                  \n");
            sqlstr.append("       ,A.KIND_GB ,A.ORDER_SEQ                                                                        \n");
            sqlstr.append("   FROM BCS_MASTER A                                                                                  \n");
            sqlstr.append("       ,BCS_MR_LAST B                                     			                                 \n");
    		sqlstr.append("       ,(SELECT DEPT_CD, PAY_ITEM_SEQ, SUM(COAMT) COAMT                                               \n");
            sqlstr.append("           FROM BCS_CO                                                                                \n");
            sqlstr.append("          WHERE DEPT_CD = '" + params.get("P_DEPT_CD") + "' AND STATUS_CD = '1'                       \n");
            sqlstr.append("          GROUP BY  DEPT_CD ,PAY_ITEM_SEQ) C                                                          \n");
            sqlstr.append("  WHERE A.DEPT_CD = B.DEPT_CD(+)                                                                      \n");
            sqlstr.append("    AND A.PAY_ITEM_SEQ = B.PAY_ITEM_SEQ(+)                                                            \n");
            sqlstr.append("    AND A.DEPT_CD = C.DEPT_CD(+)                                                                      \n");
            sqlstr.append("    AND A.PAY_ITEM_SEQ = C.PAY_ITEM_SEQ(+)                                                            \n");
            sqlstr.append("    AND A.KIND_GB LIKE  '" + params.get("P_KIND_GB") + "%'                                            \n");
            sqlstr.append("    AND A.DEPT_CD = '" + params.get("P_DEPT_CD") + "'						                         \n");
        	sqlstr.append(" UNION ALL																				             \n");
        	sqlstr.append(" SELECT 6, B.DEPT_CD                                                                                  \n"); //예산에 MAPPING되지 않은 계약자료
        	sqlstr.append("       ,A.PAY_ITEM                                                                                    \n");
        	sqlstr.append("       ,B.PAY_ITEM_SEQ                                                                                \n");
        	sqlstr.append("       ,NVL(B.MR_ID, 0) MR_ID                                                                         \n");
        	sqlstr.append("       ,B.MR_NO                                                                                       \n");
        	sqlstr.append("       ,A.ITEM_NAME                                                                                   \n");
        	sqlstr.append("       ,A.EXEAMT                                                                                      \n");
        	sqlstr.append("       ,B.CONAMT                                                                                      \n");
        	sqlstr.append("       ,TO_CHAR(B.CONDATE, 'YYYY-MM-DD') CONDATE                                                      \n");
        	sqlstr.append("       ,0 COAMT                                                                                       \n");
        	sqlstr.append("       ,0 RATE                                                                                        \n");	   
        	sqlstr.append("       ,0 SAVEAMT                                                                                     \n");
        	sqlstr.append("       ,0 CORATE                                                                                      \n");
        	sqlstr.append("       ,0 COSAVEAMT                                                                                   \n");
        	sqlstr.append("       ,B.CURRENCY_GB                                                                                 \n");
        	sqlstr.append("       ,B.VENDOR_NM                                                                                   \n");
            sqlstr.append("       ,B.REMARK1                                                                                     \n");
            sqlstr.append("       ,B.TITLE                                                                                       \n");
            sqlstr.append("       ,TO_CHAR(B.UPD_DATE, 'YYYY-MM-DD') UPD_DATE                                                    \n");
            sqlstr.append("       ,1 GROUP_SEQ                                                                                   \n");
            sqlstr.append("       ,B.KIND_GB  ,A.ORDER_SEQ                                                                       \n");
            sqlstr.append("   FROM BCS_MR B   		                                          			                         \n");
            sqlstr.append("       ,BCS_MASTER A                                                                                  \n");
    		sqlstr.append("  WHERE B.DEPT_CD = A.DEPT_CD(+)                                                                      \n");
            sqlstr.append("    AND B.PAY_ITEM_SEQ = A.PAY_ITEM_SEQ(+)                                                            \n");
            sqlstr.append("    AND B.DEPT_CD = '" + params.get("P_DEPT_CD") + "'								                 \n");
        	sqlstr.append("    AND A.PAY_ITEM_SEQ IS NULL                                                                        \n");
            sqlstr.append("    AND B.KIND_GB LIKE  '" + params.get("P_KIND_GB") + "%'                                            \n");
            sqlstr.append("UNION ALL                                                                                             \n");
            sqlstr.append("SELECT 1, A.DEPT_CD, A.PAY_ITEM, 0,  0, MAX(A.PAY_ITEM_NAME) , ''                                     \n");//상단 소계                                         
            sqlstr.append(", SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT))                                                        \n");                                           
            sqlstr.append(", SUM(B.CONAMT), '', SUM(DECODE(NVL(B.CONAMT,0), 0, 0, C.COAMT))                                      \n");                                                   
            sqlstr.append(", DECODE(MAX(A.EXEAMT),0, 0,ROUND(SUM(B.CONAMT)/SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT))*100 ,2))                            \n");                                                   
            sqlstr.append(", SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT))-SUM(B.CONAMT)                                          \n");                                                
            sqlstr.append(", DECODE(MAX(A.EXEAMT)+SUM(NVL(C.COAMT,0)),0, 0,ROUND(SUM(B.CONAMT)/(SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT))+SUM(NVL(C.COAMT,0)))*100 ,2))      \n");                                                   
            sqlstr.append(", SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT)) + SUM(NVL(C.COAMT,0))- SUM(B.CONAMT)                   \n"); 
            sqlstr.append(", '', '', '', '', '', 1                                                                               \n"); 
            sqlstr.append(", A.KIND_GB, MIN(A.ORDER_SEQ)                                                                         \n");             
            sqlstr.append(" FROM BCS_MASTER A                                                                                    \n");                             
            sqlstr.append("         ,BCS_MR_LAST B                                                                               \n");                                 
            sqlstr.append("         ,(SELECT DEPT_CD, PAY_ITEM_SEQ, SUM(COAMT) COAMT                                             \n");
            sqlstr.append("             FROM BCS_CO                                                                              \n");
            sqlstr.append("            WHERE DEPT_CD = '" + params.get("P_DEPT_CD") + "' AND STATUS_CD = '1'                     \n");
            sqlstr.append("            GROUP BY  DEPT_CD ,PAY_ITEM_SEQ) C                                                        \n");
            sqlstr.append(" WHERE A.DEPT_CD = B.DEPT_CD(+)                                                                       \n");                                 
            sqlstr.append("   AND A.PAY_ITEM_SEQ = B.PAY_ITEM_SEQ(+)                                                             \n");                                 
            sqlstr.append("   AND A.DEPT_CD = C.DEPT_CD(+)                                                                       \n");                                       
            sqlstr.append("   AND A.PAY_ITEM_SEQ = C.PAY_ITEM_SEQ(+)                                                             \n");                                           
            sqlstr.append("   AND B.RNK(+) = 1                                                                                   \n");                                                    
            sqlstr.append("   AND A.DEPT_CD = '" + params.get("P_DEPT_CD") + "'                                                  \n"); 
            sqlstr.append("   AND A.KIND_GB LIKE  '" + params.get("P_KIND_GB") + "%'                                             \n");
            sqlstr.append(" GROUP BY A.DEPT_CD, A.KIND_GB  , A.PAY_ITEM                                                          \n"); 
        	sqlstr.append("UNION ALL                                                                                             \n");
            sqlstr.append("SELECT 3, A.DEPT_CD, A.PAY_ITEM, 100, 0,  'Sub-Total', ''                                             \n");//하단소계                                         
            sqlstr.append(", SUM(A.EXEAMT)                                                                                       \n");                                           
            sqlstr.append(", 0, '', 0, 0                                                                                         \n");                                                   
            sqlstr.append(", SUM(A.EXEAMT) - SUM(B.CONAMT),0                                                                     \n");                                                
            sqlstr.append(", SUM(A.EXEAMT) + SUM(NVL(C.COAMT,0))- SUM(B.CONAMT), '', '', '', '', '', 1                           \n"); 
            sqlstr.append(", A.KIND_GB , MAX(A.ORDER_SEQ)                                                                        \n");             
            sqlstr.append(" FROM BCS_MASTER A                                                                                    \n");                             
            sqlstr.append("         ,BCS_MR_LAST B                                                                               \n");                                 
            sqlstr.append("         ,(SELECT DEPT_CD, PAY_ITEM_SEQ, SUM(COAMT) COAMT                                             \n");
            sqlstr.append("             FROM BCS_CO                                                                              \n");
            sqlstr.append("            WHERE DEPT_CD = '" + params.get("P_DEPT_CD") + "' AND STATUS_CD = '1'                     \n");
            sqlstr.append("            GROUP BY  DEPT_CD ,PAY_ITEM_SEQ) C                                                        \n");
            sqlstr.append(" WHERE A.DEPT_CD = B.DEPT_CD(+)                                                                       \n");                                 
            sqlstr.append("   AND A.PAY_ITEM_SEQ = B.PAY_ITEM_SEQ(+)                                                             \n");                                 
            sqlstr.append("   AND A.DEPT_CD = C.DEPT_CD(+)                                                                       \n");                                       
            sqlstr.append("   AND A.PAY_ITEM_SEQ = C.PAY_ITEM_SEQ(+)                                                             \n");                                           
            sqlstr.append("   AND B.RNK(+) = 1                                                                                   \n");                                                    
            sqlstr.append("   AND A.DEPT_CD = '" + params.get("P_DEPT_CD") + "'                                                  \n"); 
            sqlstr.append("   AND A.KIND_GB LIKE  '" + params.get("P_KIND_GB") + "%'                                             \n");
            sqlstr.append(" GROUP BY A.DEPT_CD, A.KIND_GB  , A.PAY_ITEM                                                          \n");  
            sqlstr.append("  UNION ALL                                                                                           \n");
            sqlstr.append("SELECT 4, A.DEPT_CD, max(A.PAY_ITEM), 100, 0, DECODE(A.KIND_GB,'P', 'Purchased', 'Contracted') || ' Total'     \n"); //합계     
            sqlstr.append(", ''                                                                                                  \n");            
            sqlstr.append(", SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT))                                                        \n");                                                          
            sqlstr.append(", SUM(B.CONAMT), '', SUM(DECODE(NVL(B.CONAMT,0), 0, 0, C.COAMT))                                      \n");                                                             
            sqlstr.append(", DECODE(MAX(A.EXEAMT),0, 0,ROUND(SUM(B.CONAMT)/SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT))*100,2))                             \n");                                                             
            sqlstr.append(", SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT)) - SUM(B.CONAMT)                                        \n");                                                       
            sqlstr.append(", DECODE(MAX(A.EXEAMT),0, 0,ROUND(SUM(B.CONAMT)/(SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT))+ SUM(DECODE(NVL(B.CONAMT,0), 0, 0, C.COAMT)))*100,2))      \n");                                                       
            sqlstr.append(", SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT)) + SUM(DECODE(NVL(B.CONAMT,0), 0, 0, C.COAMT))- SUM(B.CONAMT)                   \n");    
            sqlstr.append(", '', TO_CHAR(SUM(A.EXEAMT), '999,999,999,999')                                                       \n");   
            sqlstr.append(", '', DECODE(MAX(A.EXEAMT),0, 0,TO_CHAR(SUM(B.CONAMT)/(SUM(B.CONAMT) + SUM(A.EXEAMT)-SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT)))*100,'999.00'))||'%', '(진행율)', 1                                   \n");   
            sqlstr.append(", A.KIND_GB, MAX(A.ORDER_SEQ)                                                                         \n");           
            sqlstr.append(" FROM BCS_MASTER A                                                                                    \n");                                                            
            sqlstr.append("         ,BCS_MR_LAST B                                                                               \n");                                 
            sqlstr.append("         ,(SELECT DEPT_CD, PAY_ITEM_SEQ, SUM(COAMT) COAMT                                             \n");
            sqlstr.append("             FROM BCS_CO                                                                              \n");
            sqlstr.append("            WHERE DEPT_CD = '" + params.get("P_DEPT_CD") + "' AND STATUS_CD = '1'                     \n");
            sqlstr.append("            GROUP BY  DEPT_CD ,PAY_ITEM_SEQ) C                                                        \n");
            sqlstr.append(" WHERE A.DEPT_CD = B.DEPT_CD(+)                                                                       \n");                                             
            sqlstr.append("   AND A.PAY_ITEM_SEQ = B.PAY_ITEM_SEQ(+)                                                             \n");                                 
            sqlstr.append("   AND A.DEPT_CD = C.DEPT_CD(+)                                                                       \n");                                       
            sqlstr.append("   AND A.PAY_ITEM_SEQ = C.PAY_ITEM_SEQ(+)                                                             \n");                                             
            sqlstr.append("   AND B.RNK(+) = 1                                                                                   \n");                                                  
            sqlstr.append("   AND A.DEPT_CD = '" + params.get("P_DEPT_CD") + "'                                                  \n");  
            sqlstr.append("   AND A.KIND_GB LIKE  '" + params.get("P_KIND_GB") + "%'                                             \n");                                               
            sqlstr.append(" GROUP BY A.DEPT_CD, A.KIND_GB                                                                        \n");
            sqlstr.append("  UNION ALL                                                                                           \n");
            sqlstr.append("SELECT 5, A.DEPT_CD, 'Z99', 100, 0,  'Total'                                                          \n"); //총계     
            sqlstr.append(", ''                                                                                                  \n");            
            sqlstr.append(", SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT))                                                        \n");                                                          
            sqlstr.append(", SUM(B.CONAMT), '', SUM(DECODE(NVL(B.CONAMT,0), 0, 0, C.COAMT))                                      \n");                                                             
            sqlstr.append(", DECODE(MAX(A.EXEAMT),0, 0,ROUND(SUM(B.CONAMT)/SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT))*100,2))                             \n");                                                             
            sqlstr.append(", SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT)) - SUM(B.CONAMT)                                        \n");                                                       
            sqlstr.append(", DECODE(MAX(A.EXEAMT),0, 0,ROUND(SUM(B.CONAMT)/(SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT))+ SUM(DECODE(NVL(B.CONAMT,0), 0, 0, C.COAMT)))*100,2))      \n");                                                       
            sqlstr.append(", SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT)) + SUM(DECODE(NVL(B.CONAMT,0), 0, 0, C.COAMT))- SUM(B.CONAMT)                   \n");    
            sqlstr.append(", '', TO_CHAR(SUM(A.EXEAMT), '999,999,999,999')                                                           \n");   
            sqlstr.append(", '', DECODE(MAX(A.EXEAMT),0, 0,TO_CHAR(SUM(B.CONAMT)/(SUM(B.CONAMT)+SUM(A.EXEAMT)-SUM(DECODE(NVL(B.CONAMT,0), 0, 0, A.EXEAMT)))*100,'999.00'))||'%', '', 1                                   \n");   
            sqlstr.append(", 'A'  , MAX(A.ORDER_SEQ)                                                                                                  \n");           
            sqlstr.append(" FROM BCS_MASTER A                                                                                    \n");                                                            
            sqlstr.append("         ,BCS_MR_LAST B                                                                               \n");                                 
            sqlstr.append("         ,(SELECT DEPT_CD, PAY_ITEM_SEQ, SUM(COAMT) COAMT                                                    \n");
            sqlstr.append("             FROM BCS_CO                                                                              \n");
            sqlstr.append("            WHERE DEPT_CD = '" + params.get("P_DEPT_CD") + "' AND STATUS_CD = '1'                     \n");
            sqlstr.append("            GROUP BY  DEPT_CD ,PAY_ITEM_SEQ) C                                                               \n");
            sqlstr.append(" WHERE A.DEPT_CD = B.DEPT_CD(+)                                                                       \n");                                             
            sqlstr.append("   AND A.PAY_ITEM_SEQ = B.PAY_ITEM_SEQ(+)                                                             \n");                                 
            sqlstr.append("   AND A.DEPT_CD = C.DEPT_CD(+)                                                                       \n");                                       
            sqlstr.append("   AND A.PAY_ITEM_SEQ = C.PAY_ITEM_SEQ(+)                                                                           \n");                                             
            sqlstr.append("   AND B.RNK(+) = 1                                                                                   \n");                                                  
            sqlstr.append("   AND A.DEPT_CD = '" + params.get("P_DEPT_CD") + "'                                                  \n");  
            sqlstr.append("   AND A.KIND_GB LIKE  '" + params.get("P_KIND_GB") + "%'                                             \n");                                               
            sqlstr.append(" GROUP BY A.DEPT_CD                                                                                   \n");
            sqlstr.append(") ORDER BY  ORDER_SEQ , ORD, GROUP_SEQ ,  KIND_GB DESC                                                \n");
            
            System.out.println(sqlstr.toString());
        	search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	} 
	
	public Document getXml(RowSet rowset)
    {
        
          Document doc = new Document();  
            
          Element root = new Element("Grid");
          
          Element pack = new Element("DATA");
          
          Element pack_tr = new Element("TR");
          
          Element pack_td = new Element("TD");
          
        try {

              root.addContent(pack);//root element 의 하위 element 를 만들기
              pack.addContent(pack_tr);
              pack_tr.addContent(pack_td);
              
              for(int i=0; i < rowset.size(); i++)
              {
                  if( i != 0)
                  {
                      pack_tr = new Element("TR");
                      pack.addContent(pack_tr);
                  }
                  
                  pack_tr.setAttribute("LEVEL", rowset.getRow(i).get("GROUP_SEQ1"));
                  
                  if( i != 0)
                  {
                      pack_td = new Element("TD");
                      pack_tr.addContent(pack_td);
                  }
                  pack_td.addContent("<![CDATA[]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("DEL_CHECK") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("STATUS") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("ORD") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("DEPT_CD") + "]]>");
                 
                  
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("PAY_ITEM") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("KIND_GB") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("MR_NO") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("PAY_ITEM_SEQ") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("MR_ID") + "]]>");
                  
                          
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("ITEM_NAME") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("EXEAMT") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("CONAMT") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("CONDATE") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("COAMT") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("RATE") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("SAVEAMT") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("CORATE") + "]]>");
                
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("COSAVEAMT") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("CURRENCY_GB") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("VENDOR_NM") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("TITLE") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("REMARK1") + "]]>");
                  
                  pack_td = new Element("TD");
                  pack_tr.addContent(pack_td);
                  pack_td.addContent("<![CDATA[" + rowset.getRow(i).get("UPD_DATE") + "]]>");
                  
//                WAFLogger.debug(rowset.getRow(i).get("GUBUN_1") + "/" + rowset.getRow(i).get("GUBUN_2") + "/" + rowset.getRow(i).get("GUBUN_3"));
              }
              
              doc.setRootElement(root);              
              
                  
              } catch (Exception e) {                                         
                  WAFLogger.debug("getXml===========> " +e.getMessage());
            	  System.err.println(e);                                        
              }
        return doc;
        
    }
	
	private boolean InsertData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer(); 
			
			sql.append("   INSERT INTO BCS_MR(                           \n");
			sql.append("          DEPT_CD                                \n");
			sql.append("         ,MR_ID 	                             \n");
			sql.append("         ,KIND_GB                                \n");
			sql.append("         ,PAY_ITEM_SEQ                           \n");
			sql.append("         ,MR_NO                                  \n");
			sql.append("         ,TITLE                                  \n");
			sql.append("         ,CONAMT                                 \n");
			sql.append("         ,CONDATE                                \n");
			sql.append("         ,CONT_ID                                \n");
			sql.append("         ,CONT_MOD_ID                            \n");
			sql.append("         ,VENDOR_NM                              \n");
			sql.append("         ,REMARK1                                \n");
			sql.append("         ,CRE_DATE                               \n");
			sql.append("         ,CRE_BY                                 \n");
			sql.append("         ,UPD_DATE                               \n");
			sql.append("         ,UPD_BY                                 \n");
			sql.append("         ,CURRENCY_GB                            \n");
			sql.append("   )VALUES(                                      \n");
			sql.append("        ?                                                                                         \n");  //DEPT_CD        
			sql.append("       ,(SELECT DECODE(count(*), 0,1, MAX(MR_ID)+1) FROM BCS_MR WHERE DEPT_CD = ?)                \n");  //MR_ID 	     
			sql.append("       ,?                                                                                         \n");  //DEMANDED_KIND  
			sql.append("       ,?                                                                                         \n");  //PAY_ITEM_SEQ   
			sql.append("       ,?                                                                                         \n");  //MR_NO          
			sql.append("       ,?                                                    \n");  //TITLE          
			sql.append("       ,?                                                    \n");  //CONAMT         
			sql.append("       ,?                                                    \n");  //CONDATE        
			sql.append("       ,?                                                    \n");  //CONT_ID        
			sql.append("       ,?                                                    \n");  //CONT_MOD_ID    
			sql.append("       ,?                                                    \n");  //VENDOR_NM      
			sql.append("       ,?                                                    \n");  //REMARK1        
			sql.append("       ,SYSDATE                                              \n");  //CRE_DATE       
			sql.append("       ,?                                                    \n");  //CRE_BY         
			sql.append("       ,SYSDATE                                              \n");  //UPD_DATE       
			sql.append("       ,?                                                    \n");  //UPD_BY         
			sql.append("       ,?  )                                                  \n");  //CURRENCY_GB    
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
			pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));			
			pstmt.setString(paramIdx++, sqlParams.get("KIND_GB"));
			pstmt.setString(paramIdx++, sqlParams.get("PAY_ITEM_SEQ"));
			pstmt.setString(paramIdx++, sqlParams.get("MR_NO"));
			pstmt.setString(paramIdx++, sqlParams.get("TITLE"));
			pstmt.setDouble(paramIdx++, sqlParams.getDouble("CONAMT"));
			pstmt.setString(paramIdx++, sqlParams.get("CONDATE"));
			pstmt.setString(paramIdx++, sqlParams.get("CONT_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("CONT_MOD_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("VENDOR_NM"));
			pstmt.setString(paramIdx++, sqlParams.get("REMARK1"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("CURRENCY_GB")); 
			
			pstmt.executeUpdate();                          
			return true;                                      
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	private boolean updateData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("  UPDATE BCS_MR                        \n");
			sql.append("     SET  KIND_GB = ?                  \n");
			sql.append("         ,PAY_ITEM_SEQ  = ?            \n");
			sql.append("         ,MR_NO         = ?            \n");
			sql.append("         ,TITLE         = ?            \n");
			sql.append("         ,CONAMT        = ?            \n");
			sql.append("         ,CONDATE       = ?            \n");
			sql.append("         ,VENDOR_NM     = ?            \n");
			sql.append("         ,REMARK1       = ?            \n");
			sql.append("         ,UPD_DATE      = SYSDATE      \n");
			sql.append("         ,UPD_BY        = ?            \n");
			sql.append("         ,CURRENCY_GB   = ?            \n");
			sql.append("   WHERE DEPT_CD = ?                   \n"); 
			sql.append("  	 AND MR_ID = ?	                   \n");
			  
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("KIND_GB")); 
			pstmt.setString(paramIdx++, sqlParams.get("PAY_ITEM_SEQ")); 
			pstmt.setString(paramIdx++, sqlParams.get("MR_NO")); 
			pstmt.setString(paramIdx++, sqlParams.get("TITLE")); 
			pstmt.setDouble(paramIdx++, sqlParams.getDouble("CONAMT")); 
			pstmt.setString(paramIdx++, sqlParams.get("CONDATE")); 
			pstmt.setString(paramIdx++, sqlParams.get("VENDOR_NM")); 
			pstmt.setString(paramIdx++, sqlParams.get("REMARK1")); 
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID")); //UPD_BY
			pstmt.setString(paramIdx++, sqlParams.get("CURRENCY_GB"));
			pstmt.setString(paramIdx++, sqlParams.get("DEPT_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("MR_ID"));
			
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}

	
	private boolean deleteData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	DELETE FROM BCS_MR WHERE DEPT_CD = ? AND MR_ID = ? 		\n ");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("DEPT_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("MR_ID")); 
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	

	private boolean InsertDataCo(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer(); 
			
			sql.append("   INSERT INTO BCS_CO(                           \n");
			sql.append("          DEPT_CD                                \n");
			sql.append("         ,PAY_ITEM_SEQ 	                         \n");
			sql.append("         ,SEQ                                    \n");
			sql.append("         ,COAMT                                  \n");
			sql.append("         ,STATUS_CD                              \n");
			sql.append("         ,REMARK1                                 \n");
			sql.append("         ,CRE_DATE                               \n");
			sql.append("         ,CRE_BY                                 \n");
			sql.append("         ,UPD_DATE                               \n");
			sql.append("         ,UPD_BY                                 \n");
			sql.append("   )VALUES(                                     \n");
			sql.append("        ?                                                                                         \n");  //DEPT_CD        
			sql.append("       ,?                                                                                         \n");  //MR_ID 	     
			sql.append("       ,(SELECT DECODE(COUNT(*), 0, 1, MAX(SEQ)+1) FROM BCS_CO WHERE DEPT_CD = ? )                \n");  //SEQ            
			sql.append("       ,?                                                                                         \n");  //COAMT  
			sql.append("       ,?                                                                                         \n");  //STATUS_CD   
			sql.append("       ,?                                                                                         \n");  //REMARK          
			sql.append("       ,SYSDATE                                              \n");  //CRE_DATE       
			sql.append("       ,?                                                    \n");  //CRE_BY         
			sql.append("       ,SYSDATE                                              \n");  //UPD_DATE       
			sql.append("       ,?          )                                         \n");  //UPD_BY         
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, params.get("P_DEPT_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("PAY_ITEM_SEQ"));
			pstmt.setString(paramIdx++, params.get("P_DEPT_CD")); 
			pstmt.setString(paramIdx++, sqlParams.get("COAMT"));
			pstmt.setString(paramIdx++, sqlParams.get("STATUS_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("REMARK1"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			
			pstmt.executeUpdate();                          
			return true;                                      
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	private boolean updateDataCo(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("  UPDATE BCS_CO                     \n");
			sql.append("     SET  COAMT      = ?            \n");
			sql.append("         ,STATUS_CD  = ?            \n");
			sql.append("         ,REMARK1     = ?           \n");
			sql.append("         ,UPD_DATE   = SYSDATE      \n");
			sql.append("         ,UPD_BY     = ?            \n");
			sql.append("  	     ,PAY_ITEM_SEQ  = ?	        \n");
			sql.append("   WHERE DEPT_CD     = ?            \n"); 
			sql.append("  	 AND SEQ         = ?	        \n");
			  
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setDouble(paramIdx++, sqlParams.getDouble("COAMT")); 
			pstmt.setString(paramIdx++, sqlParams.get("STATUS_CD")); 
			pstmt.setString(paramIdx++, sqlParams.get("REMARK1")); 
			//pstmt.setString(paramIdx++, sqlParams.get("TITLE")); 
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID")); //UPD_BY
			pstmt.setString(paramIdx++, sqlParams.get("PAY_ITEM_SEQ"));
			pstmt.setString(paramIdx++, sqlParams.get("DEPT_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("SEQ"));
			
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}

	
	private boolean deleteDataCo(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	DELETE FROM BCS_CO WHERE DEPT_CD = ? AND PAY_ITEM_SEQ = ? AND SEQ = ?			\n ");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("DEPT_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("PAY_ITEM_SEQ"));
			pstmt.setString(paramIdx++, sqlParams.get("SEQ"));
			pstmt.executeUpdate();
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
}