/**
 * @(#)file    SSRListBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.08.12
 * @(#)Description: SSR List
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */
package kr.co.udapsoft.ebid.buyer.process.process.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.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.util.ValueObject;

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

public class SSRListBiz extends Biz {
	
    public SSRListBiz(HandlerStorage storage) {
        super(storage);        
    }

    public void doBiz() throws Exception {
    	boolean success =  false;
    	
    	if( storage.compareEvent("SAVE_SSR_LIST") ){		
			String[] status = super.gets("sheet1.status");

			try {
				super.conn.begin();
				
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusDelete(status[i])) {
						success = delete(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}else if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						success = update(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("") ){
	 
			/*String stDate = "";
			
			stDate = DateUtil.formatDateTime(DateUtil.getToday(), "yyyyMMdd", "yyyyMM");
			stDate = Formatter2.formatDate(stDate, "yyyymm", "yyyy-mm");

			String endDate = DateUtil.getLastDate(stDate.replaceAll("-", "")+"01",
					"yyyy-mm-dd");

			endDate =Formatter2.formatDate(endDate, "yyyy-mm-dd", "yyyy-mm"); 
					
					
			if (params.get("sYmdSt").equals("")) {
				params.set("sYmdSt", stDate);
			} else {
				params.set("sYmdSt", params.get("sYmdSt")); // 시작일
			}
		
			if (params.get("sYmdEd").equals("")) {
				params.set("sYmdEd", endDate);
			} else {
				params.set("sYmdEd", params.get("sYmdEd")); // 종료일
			}*/
		} else if( storage.compareEvent("GET_SSR_LIST") ) {
		    params.set("sheet1", getSsrList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}else if(storage.compareEvent("DEPTPOP")){
			params.set("sheet1", getDeptList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
		else if(storage.compareEvent("DEPT_SAVE")){
			success=getDeptSave(params); 
			super.storage.setSuccess(success);
		}
		else if(storage.compareEvent("GET_SSR_LIST_EXCEL")){
		    params.set("sheet2", getSsrList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}    	
    	
    }
    
    private RowSet getDeptList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sqlstr = new StrBuffer();
        try{                                                                                                                              
        	sqlstr.append("SELECT DETAILCD dept_cd                                       \n");
        	sqlstr.append("      ,DETAILNM dept_nm                                       \n");
        	sqlstr.append("      ,1 img_gb                                               \n");
        	sqlstr.append("      ,2 img_del                                              \n");
        	sqlstr.append("      ,CHG_CODE01                                              \n");
        	sqlstr.append("      ,CHG_CODE02                                              \n");
        	sqlstr.append("      ,CHG_CODE03                                              \n");
        	sqlstr.append("      ,CHG_CODE04                                              \n");
        	sqlstr.append("      ,CHG_CODE05                                              \n");
        	sqlstr.append("  FROM EBID_COM911T                                           \n");
        	sqlstr.append(" WHERE BASECD = 'SSR_DEPT'                                    \n"); 
            sqlstr.append(" ORDER BY DETAILCD                                            \n");
            search.setStatement(sqlstr.toString());
      
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            WAFLogger.debug(sqlstr.toString());
            WAFLogger.debug(se.getMessage());
            this.storage.setSuccess(false);  
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        return rowSet;
	}		
    private boolean getDeptSave(ValueObject param) {
		 Persistent persistent = null;
		 String sqlstr_i = "";
		 String sqlstr_u = "";
		 String sqlstr_d = "";
       
		 try{       
           conn.begin();
           persistent = new PersistentImpl(super.conn.getConnection());
           sqlstr_i += "insert into EBID_COM911T                                                  \n"
       	        + "                (BASECD, DETAILCD,DETAILNM                                     \n"
       	        + "                ,CHG_CODE01,CHG_CODE02,CHG_CODE03,CHG_CODE04,CHG_CODE05        \n"
       	        + "                ,CRE_BY, CRE_DATE, UPD_BY, UPD_DATE)                           \n"
    	        + "    values ('SSR_DEPT', ?, ?                                                   \n"
    	        + "           ,? ,? ,? ,? ,?                                                      \n"
    	        + "           ,? ,SYSDATE ,? ,SYSDATE )                                           \n";
    	       	   
       	      
           sqlstr_u += "UPDATE EBID_COM911T                              \n"
      	        + "   SET DETAILCD = ?                                   \n"
      	        + "      ,DETAILNM = ?                                  \n"
      	        + "      ,CHG_CODE01 = ?                                \n"
      	        + "      ,CHG_CODE02 = ?                                \n"
      	        + "      ,CHG_CODE03 = ?                                \n"
      	        + "      ,CHG_CODE04 = ?                                \n"
      	        + "      ,CHG_CODE05 = ?                                \n"
                + "      ,UPD_BY = ?                                    \n"
      	        + "      ,UPD_DATE = SYSDATE                            \n"
      	        + " WHERE BASECD = 'SSR_DEPT'                           \n"
      	        + "   AND DETAILCD = ?                                  \n";
           
           sqlstr_d += "DELETE FROM EBID_COM911T                        \n"
     	        + " WHERE BASECD = 'SSR_DEPT'                           \n"
     	        + "   AND DETAILCD = ?                                  \n";
           
       	String[] status  		= super.gets("sheet1.STATUS");
   		String[] dept_cd  		= super.gets("sheet1.dept_cd");
   		String[] dept_nm  		= super.gets("sheet1.dept_nm");
   		String[] chg_code01  	= super.gets("sheet1.chg_code01");
   		String[] chg_code02  	= super.gets("sheet1.chg_code02");
   		String[] chg_code03  	= super.gets("sheet1.chg_code03");
   		String[] chg_code04  	= super.gets("sheet1.chg_code04");
   		String[] chg_code05  	= super.gets("sheet1.chg_code05");
   		
   		
       	
   		if( status != null ) {
   			for( int i = 0 ; i < status.length ; i++ ) {
   				if( "I".equals(status[i]) ) {
   					persistent.setStatement(sqlstr_i);
   					persistent.addParameter(dept_cd[i].trim());
   					persistent.addParameter(dept_nm[i].trim());
   					persistent.addParameter(chg_code01[i].trim());
   					persistent.addParameter(chg_code02[i].trim());
   					persistent.addParameter(chg_code03[i].trim());
   					persistent.addParameter(chg_code04[i].trim());
   					persistent.addParameter(chg_code05[i].trim());
   					persistent.addParameter(storage.getUser().get("ACCT_ID"));
   					persistent.addParameter(storage.getUser().get("ACCT_ID"));
   					
   					persistent.execute();
                    persistent.clearParameters();
   				}
   				if( "U".equals(status[i]) ) {
   					persistent.setStatement(sqlstr_u);
   					persistent.addParameter(dept_cd[i].trim());
   					persistent.addParameter(dept_nm[i].trim());
   					persistent.addParameter(chg_code01[i].trim());
   					persistent.addParameter(chg_code02[i].trim());
   					persistent.addParameter(chg_code03[i].trim());
   					persistent.addParameter(chg_code04[i].trim());
   					persistent.addParameter(chg_code05[i].trim());
   					persistent.addParameter(storage.getUser().get("ACCT_ID"));
   					persistent.addParameter(dept_cd[i].trim());
   					 
   					persistent.execute();
                    persistent.clearParameters();
   				}
   				if( "D".equals(status[i]) ) {
   					persistent.setStatement(sqlstr_d);
   					persistent.addParameter(dept_cd[i].trim());
   					 
   					persistent.execute();
                    persistent.clearParameters();
   				}
   			}
   		}	
   		return true;
       }
       catch (Exception e) {
       	System.out.println(this.getClass().getName() + " --> saveData() : " + e.getMessage());
           System.out.println("SQL : " + persistent.getStatement());
           System.out.println(e);
           return false;
       }
       finally {    
       	super.conn.end();
       }
	}	
    public RowSet getSsrList(ValueObject params) {
        Search search 	  = new SearchImpl();
        RowSet rowSet 	  = new RowSetImpl();
        StringBuffer sql  = new StringBuffer();
        StringBuffer sqlstr  = new StringBuffer();
        
        try {
            sqlstr.append("SELECT                                                                                                                   \n");
            sqlstr.append("      A.SHIPPING_ID                                                                                                      \n");
            sqlstr.append("    , A.DEPT_CD                                                                                                          \n");
            sqlstr.append("    , A.SHIP_NO                                                                                                          \n");
            sqlstr.append("    , F_GET_CODE_NAME('SHIP_MODE', A.SHIP_MODE) SHIPPING_MODE                                                            \n");
            sqlstr.append("    , A.SHIP_MODE                                                                                                        \n");
            sqlstr.append("    , A.ESTI_DEPARTURE                                                                                                   \n");
            sqlstr.append("    , A.DEPARTURE                                                                                                        \n");
            sqlstr.append("    , A.ESTI_ARRIVAL                                                                                                     \n");
            sqlstr.append("    , A.ARRIVAL                                                                                                          \n");
            sqlstr.append("    , A.STATUS                                                                                                           \n");
            sqlstr.append("    , A.POL POL                                                                                                          \n");
            sqlstr.append("    , A.POD POD                                                                                                          \n");
            sqlstr.append("    , NVL(A.CNTR_20DC,0) TWENTY_DC                                                                                       \n");
            sqlstr.append("    , NVL(A.CNTR_20DT,0) TWENTY_DT                                                                                       \n");
            sqlstr.append("    , NVL(A.CNTR_20FR,0) TWENTY_FR                                                                                       \n");
            sqlstr.append("    , NVL(A.CNTR_40DC,0) FOURTY_DC                                                                                       \n");
            sqlstr.append("    , NVL(A.CNTR_40DT,0) FOURTY_DT                                                                                       \n");
            sqlstr.append("    , NVL(A.CNTR_40FR,0) FOURTY_FR                                                                                       \n");
            sqlstr.append("    , NVL(A.CNTR_45FT,0) FOURTY_FFT                                                                                      \n");
            sqlstr.append("    , A.PACKAGE_QTY                                                                                                      \n");
            sqlstr.append("    , A.PKG_WGT                                                                                                          \n");
            sqlstr.append("    , A.VESSEL_NAME                                                                                                      \n");
            sqlstr.append("    , A.BL_NO                                                                                                            \n");
            sqlstr.append("    , A.CLEAR_CUSTOM                                                                                                     \n");
            sqlstr.append("    , A.ONSITE                                                                                                           \n");
            sqlstr.append("    , A.REMARKS                                                                                                          \n");
            sqlstr.append("    , A.ORIGIN                                                                                                           \n");
            sqlstr.append("    , A.PKG_CBM                                                                                                          \n");
            sqlstr.append("    , A.VENDOR_CD                                                                                                        \n");
            sqlstr.append("    , A.DEPTEMPNO                                                                                                        \n");
            sqlstr.append("    , A.PUR_EMP_NO                                                                                                       \n");
            sqlstr.append("    , A.PUREMP_HP                                                                                                        \n");
            sqlstr.append("    , A.PUREMP_EMAIL                                                                                                     \n");
            sqlstr.append("    , A.DEPTEMP_HP                                                                                                       \n");
            sqlstr.append("    , A.DEPTEMP_EMAIL                                                                                                    \n");
            sqlstr.append("    , A.DATA1                                                                                                            \n");
            sqlstr.append("    , A.DATA2                                                                                                            \n");
            sqlstr.append("    , A.DATA3                                                                                                            \n");
            sqlstr.append("    , A.DATA4                                                                                                            \n");
            sqlstr.append("    , A.DATA5                                                                                                            \n");
            sqlstr.append("    , GET_DEPT_NM(A.DEPT_CD) DEPT_NM                                                                                     \n");
            sqlstr.append("    , B.INV_NO                                                                                                           \n");
            sqlstr.append("    , B.PARTIAL_YN                                                                                                       \n");
            sqlstr.append("    , C.ORD_DOC_NO                                                                                                       \n");
            sqlstr.append("    , NVL(C.CST_DOC_NAME, B.INV_DESC) DESCRIPTION                                                                                         \n");
            //sqlstr.append("    , GET_CORP_NM_EBID(C.CORP_ID) VENDOR_INFO                                                                            \n");
            sqlstr.append("    , D.CORP_KOR VENDOR_INFO                                                                            \n");
            sqlstr.append("    , F_GET_D_CURRENCY(NVL(C.ORD_SUPPLY_AMT,0),NVL(F_GET_CURRENCY_DIGIT(C.CURRENCY_GB),0))  PO_VALUE                     \n");
            sqlstr.append("    , F_GET_D_CURRENCY(NVL(B.INV_VALUE,0),NVL(F_GET_CURRENCY_DIGIT(B.INV_CUR),0))   INVOICE_VALUE                        \n");
            sqlstr.append("    , F_GET_CODE_CHGCODE2('911', B.INV_CUR)  INV_CUR                                                                     \n");
            sqlstr.append("    , F_GET_CODE_NAME('PUR_LEAD_GB', C.LEAD_COND_GB) CONDITION                                                           \n");
            sqlstr.append("    , C.CORP_ID                                                                                                          \n");
            sqlstr.append("    , F_GET_CODE_CHGCODE2('911', C.CURRENCY_GB) CURRENCY                                                                 \n");
            sqlstr.append("    , A.CONFIRM_DATE                                                                                                     \n");
            sqlstr.append("    , A.CONFIRM_EMP_NO                                                                                                   \n");
            sqlstr.append("    , B.PUR_CONT_ID                                                                                                      \n");
            sqlstr.append("    , A.SHIPPING_SEQ                                                                                                     \n");
            sqlstr.append("    , F_GET_CODE_NAME('SSR_STATUS', A.STATUS) STATUS_NM                                                                  \n");
            //sqlstr.append("    , (SELECT COUNT(FILE_MAP_ID) FROM PUB0510T WHERE O_FILE_GB = 'SSR_DETAIL_' || A.SHIPPING_ID  AND ID = '10') file_cnt \n");
            sqlstr.append("    , NVL(E.FILE_CNT, 0) file_cnt \n");
            sqlstr.append(" FROM PSR_SHIPPING A                                                                                                     \n");
            sqlstr.append("    ,( SELECT A.SHIPPING_ID, A.PUR_CONT_ID, A.PARTIAL_YN, A.RNK, B.INV_NO, B.INV_VALUE, B.INV_CUR , B.INV_DESC           \n");
            sqlstr.append("         FROM (SELECT A.SHIPPING_ID,  A.PUR_CONT_ID, MAX(A.PARTIAL_YN) PARTIAL_YN                                        \n");
            sqlstr.append("                     ,RANK() OVER (PARTITION BY  A.SHIPPING_ID  ORDER BY  A.PUR_CONT_ID ASC)  RNK                        \n");
            sqlstr.append("                 FROM PSR_SHIPPING_DETAIL A                                                                              \n");
            sqlstr.append("                     ,PSR_SHIPPING B                                                                                     \n");
            sqlstr.append("                WHERE A.SHIPPING_ID = B.SHIPPING_ID                                                                      \n");
            sqlstr.append("                  AND B.DEPT_CD = ?                                                                                      \n");
            sqlstr.append("                GROUP BY A.SHIPPING_ID, A.PUR_CONT_ID ) A                                                                \n");
            sqlstr.append("             ,(SELECT A.SHIPPING_ID, A.INV_NO, A.INV_VALUE, A.INV_CUR, A.INV_DESC                                        \n");
            sqlstr.append("        		        ,DENSE_RANK() OVER (PARTITION BY A.SHIPPING_ID  ORDER BY A.ORD  ASC) RNK                            \n");
            sqlstr.append("                 FROM PSR_SHIPPING_INV A                                                                                 \n");
            sqlstr.append("                     ,PSR_SHIPPING B                                                                                     \n");
            sqlstr.append("                WHERE A.SHIPPING_ID = B.SHIPPING_ID                                                                      \n");
            sqlstr.append("                  AND B.DEPT_CD = ?) B                                                                                   \n");
            sqlstr.append("        WHERE A.SHIPPING_ID = B.SHIPPING_ID(+)                                                                           \n");
            sqlstr.append("          AND A.RNK = B.RNK(+)                                                                                           \n");
            sqlstr.append("        UNION                                                                                                            \n");
            sqlstr.append("       SELECT A.SHIPPING_ID, B.PUR_CONT_ID, B.PARTIAL_YN, A.RNK, A.INV_NO, A.INV_VALUE, A.INV_CUR  , A.INV_DESC          \n");
            sqlstr.append("         FROM(SELECT A.SHIPPING_ID, A.INV_NO, A.INV_VALUE, A.INV_CUR ,A.INV_DESC                                         \n");
            sqlstr.append("        		        ,DENSE_RANK() OVER (PARTITION BY A.SHIPPING_ID  ORDER BY A.ORD ASC) RNK                             \n");
            sqlstr.append("                 FROM PSR_SHIPPING_INV A                                                                                 \n");
            sqlstr.append("                     ,PSR_SHIPPING B                                                                                     \n");
            sqlstr.append("                WHERE A.SHIPPING_ID = B.SHIPPING_ID                                                                      \n");
            sqlstr.append("                  AND B.DEPT_CD = ?) A                                                                                   \n");
            sqlstr.append("             ,(SELECT A.SHIPPING_ID,  A.PUR_CONT_ID, MAX(A.PARTIAL_YN) PARTIAL_YN                                        \n");
            sqlstr.append("                     ,RANK() OVER (PARTITION BY  A.SHIPPING_ID  ORDER BY  A.PUR_CONT_ID ASC)  RNK                        \n");
            sqlstr.append("                 FROM PSR_SHIPPING_DETAIL A                                                                              \n");
            sqlstr.append("                     ,PSR_SHIPPING B                                                                                     \n");
            sqlstr.append("                WHERE A.SHIPPING_ID = B.SHIPPING_ID                                                                      \n");
            sqlstr.append("                  AND B.DEPT_CD = ?                                                                                      \n");
            sqlstr.append("                GROUP BY A.SHIPPING_ID, A.PUR_CONT_ID ) B                                                                \n");
            sqlstr.append("        WHERE A.SHIPPING_ID = B.SHIPPING_ID(+)                                                                           \n");
            sqlstr.append("          AND A.RNK = B.RNK(+)                                                                                           \n");
            sqlstr.append("      ) B                                                                                                                \n");
            sqlstr.append("    , PUR1000T C                                                                                                         \n");
            sqlstr.append("    , SUPP_INFO D                                                                                                        \n");
            sqlstr.append("    , (SELECT O_FILE_GB, COUNT(FILE_MAP_ID) FILE_CNT                                                                     \n");
            sqlstr.append("          FROM PUB0510T                                                                                                  \n");
            sqlstr.append("        WHERE ID = '10'                                                                                                  \n");
            sqlstr.append("        GROUP BY O_FILE_GB                                                                                               \n");
            sqlstr.append("      ) E                                                                                                                \n");            
            sqlstr.append("WHERE A.SHIPPING_ID = B.SHIPPING_ID(+)                                                                                   \n");
            sqlstr.append("  AND B.PUR_CONT_ID = C.PUR_CONT_ID(+)                                                                                   \n");
            sqlstr.append("  AND C.CORP_ID = D.CORP_ID(+)                                                                                           \n");
            sqlstr.append("  AND 'SSR_DETAIL_' || A.SHIPPING_ID = E.O_FILE_GB(+)                                                                    \n");
            sqlstr.append("  AND A.DEPT_CD = ?                                                                                                      \n");
            
            search.addParameter(params.get("dept_cd"));
    		search.addParameter(params.get("dept_cd"));
    		search.addParameter(params.get("dept_cd"));
    		search.addParameter(params.get("dept_cd"));
    		search.addParameter(params.get("dept_cd"));
    		
    		if(!"".equals(params.get("sYmdSt")) && !"".equals(params.get("sYmdEd"))) {
        		sqlstr.append(" AND SUBSTR(A.ESTI_DEPARTURE, 1,6) BETWEEN ? AND ?		\n");
        		search.addParameter(params.get("sYmdSt").replaceAll("-", ""));
        		search.addParameter(params.get("sYmdEd").replaceAll("-", ""));
        	}
        	
        	if(!"".equals(params.get("p_origin"))) {
        		sqlstr.append(" 	AND UPPER(C.ORD_DOC_NO) LIKE UPPER('%'||?||'%')	\n");
        		search.addParameter(params.get("p_origin"));
        	}
        	
        	
        	if(!"".equals(params.get("p_shipping_mode"))) {
        		sqlstr.append(" 	AND A.SHIP_MODE LIKE '%' || ? || '%'	\n");
        		search.addParameter(params.get("p_shipping_mode"));
        	}

        	if(!"".equals(params.get("p_status"))) {
        		sqlstr.append(" 	AND A.STATUS = ?	\n");
        		search.addParameter(params.get("p_status"));
        	}
        	if(!"".equals(params.get("p_srn_no"))) {
        		sqlstr.append(" 	AND A.SHIP_NO   like UPPER('%'||?||'%')	\n");
        		search.addParameter(params.get("p_srn_no"));
        	}
        	if(!"".equals(params.get("p_vendor"))) {
        		sqlstr.append(" 	AND UPPER(D.CORP_KOR)   like UPPER('%'||?||'%')	\n");
        		search.addParameter(params.get("p_vendor"));
        	}        	
        	sqlstr.append("	     ORDER BY  A.SHIP_NO DESC,   B.PUR_CONT_ID,  B.INV_NO     	 											\n");
        	
        	
        	System.out.println(sqlstr.toString());
        	System.out.println(search.getParameters());
            search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        } catch (WAFSQLException se) {
        	WAFLogger.error(this.getClass().getName() + " --> getSsrList() : \n" + se.getMessage());
            WAFLogger.error("SQL : " + search.getStatement());
            WAFLogger.error(se);
            storage.setDetailMessage(se);
        }
        finally {    
            if(rowSet == null) rowSet = new RowSetImpl();
            return rowSet;
        }
    }
    
    public boolean delete(Connection connection, ValueObject sqlParams) throws Exception  {
        PreparedStatement pstmt = null;
        PreparedStatement pstmt2 = null;
        PreparedStatement pstmt3 = null;
		try {
			StringBuffer sql = new StringBuffer();
			StringBuffer sql2 = new StringBuffer();
			StringBuffer sql3 = new StringBuffer();
			
			sql.append("   DELETE FROM PSR_SHIPPING_DETAIL WHERE SHIPPING_ID = ?		\n");
			sql2.append("  DELETE FROM PSR_SHIPPING_INV WHERE SHIPPING_ID = ?		    \n");
			sql3.append("  DELETE FROM PSR_SHIPPING WHERE SHIPPING_ID = ?		        \n");
			pstmt = connection.prepareStatement(sql.toString());
			pstmt2 = connection.prepareStatement(sql2.toString());
			pstmt3 = connection.prepareStatement(sql3.toString());
			int paramIdx = 1;
			int paramIdx2 = 1;
			int paramIdx3 = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("SHIPPING_ID"));
			pstmt2.setString(paramIdx2++, sqlParams.get("SHIPPING_ID"));
			pstmt3.setString(paramIdx3++, sqlParams.get("SHIPPING_ID"));
			
			pstmt.executeUpdate();
			pstmt2.executeUpdate();
			pstmt3.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);
			}
		}
    }
    
    public boolean update(Connection connection, ValueObject sqlParams) throws Exception  {
        PreparedStatement pstmt = null; 
		try {
			StringBuffer sql = new StringBuffer(); 
			
			sql.append(" UPDATE PSR_SHIPPING                                                               \n");
			sql.append("    SET origin = ?                                                                 \n");
			sql.append("       ,PACKAGE_QTY = ?                                                             \n");
			sql.append("       ,PKG_WGT         = ?                                                         \n");
			sql.append("       ,PKG_CBM          = ?                                                        \n");
			sql.append("       ,ship_mode = ?                                                               \n");  //shipping_mode
			sql.append("       ,cntr_20dc = ?                                                               \n");  //twenty_dc    
			sql.append("       ,cntr_20dt = ?                                                               \n");  //twenty_dt    
			sql.append("       ,cntr_20fr = ?                                                               \n");  //twenty_fr    
			sql.append("       ,cntr_40dc = ?                                                               \n");  //fourty_dc    
			sql.append("       ,cntr_40dt = ?                                                               \n");  //fourty_dt    
			sql.append("       ,cntr_40fr = ?                                                               \n");  //fourty_fr    
			sql.append("       ,cntr_45ft = ?                                                               \n");  //fourty_fft   
			sql.append("       ,pol  = ?                                                                    \n");
			sql.append("       ,pod  = ?                                                                    \n");
			sql.append("       ,vessel_name  = ?                                                            \n");
			sql.append("       ,bl_no   = ?                                                                 \n");
			sql.append("       ,ESTI_DEPARTURE = ?                                                          \n");
			sql.append("       ,ESTI_ARRIVAL   = ?                                                          \n");
			sql.append("       ,DEPARTURE = ?                                                               \n");
			sql.append("       ,ARRIVAL   = ?                                                               \n");
			sql.append("       ,CLEAR_CUSTOM   = ?                                                          \n");
			sql.append("       ,ONSITE    = ?                                                               \n");
			sql.append("       ,REMARKS    = ?                                                              \n");
			sql.append("       ,data1    = ?                                                                \n");
			sql.append("       ,data2    = ?                                                                \n");
			sql.append("       ,data3    = ?                                                                \n");
			sql.append("       ,data4    = ?                                                                \n");
			sql.append("       ,data5    = ?                                                                \n");
			sql.append(" WHERE SHIPPING_ID = ?                                                              \n");
			pstmt = connection.prepareStatement(sql.toString()); 
			int paramIdx = 1; 
			
			pstmt.setString(paramIdx++, sqlParams.get("origin"));           //origin   
			pstmt.setString(paramIdx++, sqlParams.get("PACKAGE_QTY"));      //PACKAGE_QTY
			pstmt.setString(paramIdx++, sqlParams.get("PKG_WGT"));          //PKG_WGT    
			pstmt.setString(paramIdx++, sqlParams.get("PKG_CBM"));          //PKG_CBM    
			pstmt.setString(paramIdx++, sqlParams.get("shipping_mode"));    //shipping_mode  
			pstmt.setString(paramIdx++, sqlParams.get("twenty_dc"));        //twenty_dc      
			pstmt.setString(paramIdx++, sqlParams.get("twenty_dt"));        //twenty_dt      
			pstmt.setString(paramIdx++, sqlParams.get("twenty_fr"));        //twenty_fr      
			pstmt.setString(paramIdx++, sqlParams.get("fourty_dc"));        //fourty_dc      
			pstmt.setString(paramIdx++, sqlParams.get("fourty_dt"));        //fourty_dt      
			pstmt.setString(paramIdx++, sqlParams.get("fourty_fr"));        //fourty_fr      
			pstmt.setString(paramIdx++, sqlParams.get("fourty_fft"));       //fourty_fft     
			pstmt.setString(paramIdx++, sqlParams.get("pol"));              //pol        
			pstmt.setString(paramIdx++, sqlParams.get("pod"));              //pod        
			pstmt.setString(paramIdx++, sqlParams.get("vessel_name"));      //vessel_name  
			pstmt.setString(paramIdx++, sqlParams.get("bl_no"));            //bl_no      
			pstmt.setString(paramIdx++, sqlParams.get("ESTI_DEPARTURE"));   //ESTI_DEPARTURE
			pstmt.setString(paramIdx++, sqlParams.get("ESTI_ARRIVAL"));     //ESTI_ARRIVAL  
			pstmt.setString(paramIdx++, sqlParams.get("DEPARTURE"));        //DEPARTURE
			pstmt.setString(paramIdx++, sqlParams.get("ARRIVAL"));          //ARRIVAL  
			pstmt.setString(paramIdx++, sqlParams.get("CLEAR_CUSTOM"));     //CLEAR_CUSTOM  
			pstmt.setString(paramIdx++, sqlParams.get("ONSITE"));           //ONSITE   
			pstmt.setString(paramIdx++, sqlParams.get("remarks"));          //remarks  
			pstmt.setString(paramIdx++, sqlParams.get("data1"));            //data1   
			pstmt.setString(paramIdx++, sqlParams.get("data2"));            //data2   
			pstmt.setString(paramIdx++, sqlParams.get("data3"));            //data3   
			pstmt.setString(paramIdx++, sqlParams.get("data4"));            //data4   
			pstmt.setString(paramIdx++, sqlParams.get("data5"));            //data5   
			pstmt.setString(paramIdx++, sqlParams.get("SHIPPING_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);
			}
		}
    }
 }

