/**
 * @(#)file    SSRDetailSaveBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.08.13
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package kr.co.udapsoft.ebid.buyer.process.process.biz;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

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.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.common.util.PCMSUtil;

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 SSRDetailSaveBiz extends Biz { 

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

	public void doBiz() throws Exception {
		boolean success = false;
		
		try {
			super.conn.begin();

			Connection connection = super.conn.getConnection();

			if (storage.compareEvent("SAVE_SSR_DETAIL")) {  //insert
				ValueObject voShippingId = getShippingId(super.conn.getConnection());
				params.set("shipping_id", voShippingId.get("shipping_id"));
				params.set("shipping_seq", voShippingId.get("shipping_id")); 
				success = saveSSRDetail(connection, params);
				success = detailSave(connection, params); 
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request)); 
			} else if (storage.compareEvent("UPDATE_SSR_DETAIL")) {  //update
				success = updateSSRDetail(connection, params);
				success = detailSave(connection, params);
				
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} else if (storage.compareEvent("UPDATE_SSR_INV")) {  //update
				success = invSave(connection, params);
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			}else if (storage.compareEvent("CONFIRM_SSR_DETAIL")) {
				success = confirmData(connection, params);
				if (params.get("will_status").equals("9")){				
					// 환율재적용
					success = confirmExchange(connection, params);
				}				
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			}else if (storage.compareEvent("UPDATE_ATDATA_DETAIL")) {
				success = updateATDATADetail(connection, params);
				storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			}
		} catch (Exception e) {
		    	System.out.println("doBiz" + e );
				WAFLogger.error(e);
				super.doException(e);
				storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
				super.storage.setMessage(e.getMessage());
				super.storage.setDetailMessage(e);

				super.conn.setRollback();
				super.storage.setSuccess(false);
		} finally {
			super.conn.end();
		}
	}
	
	public boolean detailSave(Connection connection, ValueObject params) throws Exception {
		try{
			String[] status = super.gets("SHEET1.status");
			boolean success = true;
			for (int i = 0; i < status.length; i++) {
				if (IBSheet7Utils.equalStatusInsert(status[i])) {
					success = saveSSRDetailSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
				}
				if (IBSheet7Utils.equalStatusUpdate(status[i])) {
					success = updateSSRDetailSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
				}
				if (IBSheet7Utils.equalStatusDelete(status[i])) {
					success = deleteSSRDetailSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
				}
			}		 
			return success;
		} catch (Exception e) {
			System.out.println("detailSave" + e );
			throw e;
		}
	}
	 
	public boolean invSave(Connection connection, ValueObject params) throws Exception {
		try{
			boolean success = true;
			String[] status2 = super.gets("SHEET2.status");
			for (int i = 0; i < status2.length; i++) {
				if (IBSheet7Utils.equalStatusInsert(status2[i])) {
					success = saveSSRInvSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
				}
				if (IBSheet7Utils.equalStatusUpdate(status2[i])) {
					success = updateSSRInvSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
				}
				if (IBSheet7Utils.equalStatusDelete(status2[i])) {
					success = deleteSSRInvSheet(connection, IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
				}
			}
			return success;
		} catch (Exception e) {
			System.out.println("invSave" + e );
			throw e;
		}
	}

	//2016-08-31
	public boolean confirmExchange(Connection connection, ValueObject params) throws Exception {
		   PreparedStatement pstmt = null;
		   StringBuffer sql   = new StringBuffer();
		   
		   try {     
			   sql.append("	MERGE INTO PSR_SHIPPING_DETAIL  A                                                                            \n");
			   sql.append("	USING (SELECT B.SHIPPING_DETAIL_ID, B.SHIPPING_ID, A.DEPARTURE DT, B.EXC_PRI, B.EXC_AMT, C.CURRENCY_GB CUR   \n");
			   sql.append("          FROM PSR_SHIPPING A                                                                                 \n");
			   sql.append("              ,PSR_SHIPPING_DETAIL B                                                                          \n");
			   sql.append("              ,PUR1000T C                                                                                     \n");
			   sql.append("         WHERE A.SHIPPING_ID = B.SHIPPING_ID                                                                  \n");
			   sql.append("           AND B.PUR_CONT_ID = C.PUR_CONT_ID                                                                  \n");
			   sql.append("           AND A.SHIPPING_ID = ? )  B                                                                         \n");
			   sql.append("    ON (A.SHIPPING_DETAIL_ID = B.SHIPPING_DETAIL_ID                                                           \n");
			   sql.append("    AND A.SHIPPING_ID = B.SHIPPING_ID)                                                                        \n");
			   sql.append("  WHEN MATCHED THEN                                                                                           \n");
			   sql.append("       UPDATE                                                                                                 \n");
			   sql.append("          SET A.SHP_UNIT_COST = B.EXC_PRI * F_GET_EXCHANGE_RATE(B.DT, B.CUR, 'KRW')                     \n");
			   sql.append("             ,A.SHP_AMT= B.EXC_AMT  * F_GET_EXCHANGE_RATE(B.DT, B.CUR, 'KRW')                           \n");
			   sql.append("             ,A.UPD_DATE = SYSDATE                                                                            \n");
			   
		        pstmt = connection.prepareStatement(sql.toString());
		        int paramIdx = 1;
				pstmt.setString(paramIdx++, params.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);
				}
	        }
	    }
	
	public boolean saveSSRDetail(Connection connection, ValueObject params)
			throws Exception {
		Persistent persistent = null;
		StringBuffer sql = new StringBuffer();

		sql.append("	INSERT INTO PSR_SHIPPING		\n");
		sql.append("	(                       		\n");
		sql.append("		SHIPPING_ID           		\n");
		sql.append("		, DEPT_CD             		\n");
		sql.append("		, SHIP_NO             		\n");
		sql.append("		, SHIP_MODE           		\n");
		sql.append("		, ESTI_DEPARTURE      		\n");
		sql.append("		, DEPARTURE           		\n");
		sql.append("		, ESTI_ARRIVAL        		\n");
		sql.append("		, ARRIVAL             		\n");
		sql.append("		, STATUS             		\n");
		sql.append("		, POL         		        \n");
		sql.append("		, POD         		        \n");
		sql.append("		, CNTR_20DC           		\n");
		sql.append("		, CNTR_20DT           		\n");
		sql.append("		, CNTR_20FR           		\n");
		sql.append("		, CNTR_40DC           		\n");
		sql.append("		, CNTR_40DT           		\n");
		sql.append("		, CNTR_40FR           		\n");
		sql.append("		, CNTR_45FT           		\n");
		sql.append("		, PACKAGE_QTY         		\n");
		sql.append("		, PKG_WGT             		\n");
		sql.append("		, VESSEL_NAME         		\n");
		sql.append("		, BL_NO               		\n");
		sql.append("		, CLEAR_CUSTOM        		\n");
		sql.append("		, ONSITE              		\n");
		sql.append("		, REMARKS             		\n");
		sql.append("		, ORIGIN               		\n");
		sql.append("		, CRE_BY             		\n");
		sql.append("		, CRE_DATE             		\n");
		sql.append("		, PKG_CBM             		\n");
		sql.append("		, VENDOR_CD           		\n");
		sql.append("		, DEPTEMPNO             	\n");
		sql.append("		, PUR_EMP_NO            	\n");
		sql.append("		, PUREMP_HP             	\n");
		sql.append("		, PUREMP_EMAIL             	\n");
		sql.append("		, DEPTEMP_HP             	\n");
		sql.append("		, DEPTEMP_EMAIL             \n");
		sql.append("		, DATA1             		\n");
		sql.append("		, DATA2             		\n");
		sql.append("		, DATA3             		\n");
		sql.append("		, DATA4             		\n");
		sql.append("		, DATA5             		\n");
		sql.append("		, SHIPPING_SEQ             	\n");
		sql.append("	) VALUES (              		\n");
		sql.append("	?  		                        \n");  //SHIPPING_ID
		sql.append("	,?                      		\n");  //DEPT_CD       
		sql.append("	,?                      		\n");  //SHIP_NO       
		sql.append("	,?                      		\n");  //SHIP_MODE     
		sql.append("	,?                      		\n");  //ESTI_DEPARTURE
		sql.append("	,?                      		\n");  //DEPARTURE     
		sql.append("	,?                      		\n");  //ESTI_ARRIVAL  
		sql.append("	,?                      		\n");  //ARRIVAL       
		sql.append("	,'1'                      		\n");  //STATUS        
		sql.append("	,?                      		\n");  //POL         	
		sql.append("	,?                      		\n");  //POD         	
		sql.append("	,?                      		\n");  //CNTR_20DC     
		sql.append("	,?                      		\n");  //CNTR_20DT     
		sql.append("	,?                      		\n");  //CNTR_20FR     
		sql.append("	,?                      		\n");  //CNTR_40DC     
		sql.append("	,?                      		\n");  //CNTR_40DT     
		sql.append("	,?                      		\n");  //CNTR_40FR     
		sql.append("	,?                      		\n");  //CNTR_45FT     
		sql.append("	,?                      		\n");  //PACKAGE_QTY   
		sql.append("	,?                      		\n");  //PKG_WGT       
		sql.append("	,?                      		\n");  //VESSEL_NAME   
		sql.append("	,?                      		\n");  //BL_NO         
		sql.append("	,?                      		\n");  //CLEAR_CUSTOM  
		sql.append("	,?                      		\n");  //ONSITE        
		sql.append("	,?                      		\n");  //REMARKS       
		sql.append("	,?                      		\n");  //ORIGIN        
		sql.append("	,?                      		\n");  //CRE_BY        
		sql.append("	,SYSDATE                  		\n");  //CRE_DATE      
		sql.append("	,?                      		\n");  //PKG_CBM       
		sql.append("	,?                      		\n");  //VENDOR_CD     
		sql.append("	,?                      		\n");  //DEPTEMPNO     
		sql.append("	,?                      		\n");  //PUR_EMP_NO    
		sql.append("	,?                              \n");  //PUREMP_HP    
		sql.append("	,?                      		\n");  //PUREMP_EMAIL  
		sql.append("	,?                      		\n");  //DEPTEMP_HP    
		sql.append("	,?                      		\n");  //DEPTEMP_EMAIL 
		sql.append("	,?                      		\n");  //DATA1         
		sql.append("	,?                      		\n");  //DATA2         
		sql.append("	,?                      		\n");  //DATA3         
		sql.append("	,?                      		\n");  //DATA4         
		sql.append("	,?                      		\n");  //DATA5         
		sql.append("	,?                      		\n");  //SHIPPING_SEQ  
		sql.append("	)                       		\n");

		try {
			persistent = new PersistentImpl(connection);
			persistent.setStatement(sql.toString());
			 
			persistent.addParameter(params.get("shipping_id"));
			persistent.addParameter(params.get("dept_cd"));
			persistent.addParameter(params.get("ship_no"));
			persistent.addParameter(params.get("ship_mode"));
			persistent.addParameter(params.get("esti_departure").replaceAll("-", ""));
			persistent.addParameter(params.get("departure").replaceAll("-", ""));
			persistent.addParameter(params.get("esti_arrival").replaceAll("-", ""));
			persistent.addParameter(params.get("arrival").replaceAll("-", ""));
			persistent.addParameter(params.get("pol"));
			persistent.addParameter(params.get("pod"));
			persistent.addParameter(params.get("twenty_dc"));
			persistent.addParameter(params.get("twenty_dt"));
			persistent.addParameter(params.get("twenty_fr"));
			persistent.addParameter(params.get("fourty_dc"));
			persistent.addParameter(params.get("fourty_dt"));
			persistent.addParameter(params.get("fourty_fr"));
			persistent.addParameter(params.get("fourty_fft"));
			persistent.addParameter(params.get("package_qty"));
			persistent.addParameter(params.get("pkg_wgt"));
			persistent.addParameter(params.get("vessel_name"));
			persistent.addParameter(params.get("bl_no"));
			persistent.addParameter(params.get("clear_custom").replaceAll("-", ""));
			persistent.addParameter(params.get("onsite").replaceAll("-", ""));
			persistent.addParameter(params.get("remarks"));
			persistent.addParameter(params.get("origin"));
			persistent.addParameter(storage.getUser().get("ACCT_ID"));
			persistent.addParameter(params.get("pkg_cbm"));
			persistent.addParameter(params.get("vendor_cd"));
			persistent.addParameter(params.get("deptempno"));
			persistent.addParameter(params.get("pur_emp_no"));
			persistent.addParameter(params.get("puremp_hp"));
			persistent.addParameter(params.get("puremp_email"));
			persistent.addParameter(params.get("deptemp_hp"));
			persistent.addParameter(params.get("deptemp_email"));
			persistent.addParameter(params.get("DATA1"));
			persistent.addParameter(params.get("DATA2"));
			persistent.addParameter(params.get("DATA3"));
			persistent.addParameter(params.get("DATA4"));
			persistent.addParameter(params.get("DATA5"));
			persistent.addParameter(params.get("shipping_id"));
			
			WAFLogger.debug(sql.toString());
			persistent.execute();
			persistent.clearParameters();

			return true;
		} catch (Exception e) {
			throw e;
		}
	}

	public boolean updateSSRDetail(Connection connection, ValueObject params) throws Exception {
		Persistent persistent = null;
		StringBuffer sql = new StringBuffer();

		sql.append("	UPDATE PSR_SHIPPING SET 		\n");
		sql.append("		  SHIP_NO = ?           		\n");
		sql.append("		, SHIP_MODE = ?       		\n");
		sql.append("		, ESTI_DEPARTURE = ?  		\n");
		sql.append("		, DEPARTURE = ?       		\n");
		sql.append("		, ESTI_ARRIVAL = ?    		\n");
		sql.append("		, ARRIVAL = ?         		\n");
		sql.append("		, POL = ?         		\n");
		sql.append("		, POD = ?         		\n");
		sql.append("		, CNTR_20DC = ?       		\n");
		sql.append("		, CNTR_20DT = ?       		\n");
		sql.append("		, CNTR_20FR = ?       		\n");
		sql.append("		, CNTR_40DC = ?       		\n");
		sql.append("		, CNTR_40DT = ?       		\n");
		sql.append("		, CNTR_40FR = ?       		\n");
		sql.append("		, CNTR_45FT = ?       		\n");
		sql.append("		, PACKAGE_QTY = ?     		\n");
		sql.append("		, PKG_WGT = ?         		\n");
		sql.append("		, VESSEL_NAME = ?     		\n");
		sql.append("		, BL_NO = ?           		\n");
		sql.append("		, CLEAR_CUSTOM = ?    		\n");
		sql.append("		, ONSITE = ?          		\n");
		sql.append("		, REMARKS = ?         		\n");
		sql.append("		, ORIGIN = ?           		\n");
		sql.append("		, UPD_BY = ?         		\n");
		sql.append("		, UPD_DATE = SYSDATE         		\n");
		sql.append("		, PKG_CBM = ?         		\n");
		sql.append("		, VENDOR_CD = ?       		\n");
		sql.append("		, DEPTEMPNO = ?         		\n");
		sql.append("		, PUR_EMP_NO = ?             		\n");
		sql.append("		, PUREMP_HP = ?             		\n");
		sql.append("		, PUREMP_EMAIL = ?             		\n");
		sql.append("		, DEPTEMP_HP = ?             		\n");
		sql.append("		, DEPTEMP_EMAIL = ?           		\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");

		try {
			persistent = new PersistentImpl(connection);
			persistent.setStatement(sql.toString());

			persistent.addParameter(params.get("ship_no"));
			persistent.addParameter(params.get("ship_mode"));
			persistent.addParameter(params.get("esti_departure").replaceAll("-", ""));
			persistent.addParameter(params.get("departure").replaceAll("-", ""));
			persistent.addParameter(params.get("esti_arrival").replaceAll("-", ""));
			persistent.addParameter(params.get("arrival").replaceAll("-", ""));
			persistent.addParameter(params.get("pol"));
			persistent.addParameter(params.get("pod"));
			persistent.addParameter(params.get("twenty_dc"));
			persistent.addParameter(params.get("twenty_dt"));
			persistent.addParameter(params.get("twenty_fr"));
			persistent.addParameter(params.get("fourty_dc"));
			persistent.addParameter(params.get("fourty_dt"));
			persistent.addParameter(params.get("fourty_fr"));
			persistent.addParameter(params.get("fourty_fft"));
			persistent.addParameter(params.get("package_qty"));
			persistent.addParameter(params.get("pkg_wgt"));
			persistent.addParameter(params.get("vessel_name"));
			persistent.addParameter(params.get("bl_no"));
			persistent.addParameter(params.get("clear_custom").replaceAll("-", ""));
			persistent.addParameter(params.get("onsite").replaceAll("-", ""));
			persistent.addParameter(params.get("remarks"));
			persistent.addParameter(params.get("origin"));
			persistent.addParameter(storage.getUser().get("ACCT_ID"));
			persistent.addParameter(params.get("pkg_cbm"));
			persistent.addParameter(params.get("vendor_cd"));
			persistent.addParameter(params.get("deptempno"));
			persistent.addParameter(params.get("pur_emp_no"));
			persistent.addParameter(params.get("puremp_hp"));
			persistent.addParameter(params.get("puremp_email"));
			persistent.addParameter(params.get("deptemp_hp"));
			persistent.addParameter(params.get("deptemp_email"));
			persistent.addParameter(params.get("DATA1"));
			persistent.addParameter(params.get("DATA2"));
			persistent.addParameter(params.get("DATA3"));
			persistent.addParameter(params.get("DATA4"));
			persistent.addParameter(params.get("DATA5"));
			persistent.addParameter(params.get("shipping_id"));

			WAFLogger.debug(sql.toString());
			persistent.execute();
			persistent.clearParameters();

			return true;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		}
	}
    
	
	public boolean saveSSRDetailSheet(Connection connection,
			ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		String returnValue = "";

		try {	

			// 환율 가져오기
			returnValue = getExchangeRate(sqlParams, params);

			sql.append("	INSERT INTO PSR_SHIPPING_DETAIL		\n");
			sql.append("	(                              		\n");
			sql.append("		SHIPPING_DETAIL_ID           		\n");
			sql.append("		, SHIPPING_ID                		\n");
			sql.append("		, MATERIAL_CD                		\n");
			sql.append("		, SHIP_QTY                   		\n");
			sql.append("		, REMARK                    		\n");
			sql.append("		, TAG_NO                     		\n");
			sql.append("		, PUR_CONT_ID                		\n");
			sql.append("		, SHP_UNIT_COST              		\n");
			sql.append("		, SHP_AMT                    		\n");
			sql.append("		, EXC_PRI                    		\n");
			sql.append("		, EXC_AMT                    		\n");
			sql.append("		, CRE_BY                     		\n");
			sql.append("		, CRE_DATE                   		\n");
			sql.append("		, PARTIAL_YN                   		\n");
			sql.append("	) VALUES (                     		\n");
			sql.append("	(SELECT NVL(MAX(SHIPPING_DETAIL_ID),0)+1 FROM PSR_SHIPPING_DETAIL WHERE SHIPPING_ID = ? )       		\n");
			sql.append("	,?                             		\n");
			sql.append("	,?                             		\n");
			sql.append("	,?                             		\n");
			sql.append("	,?                             		\n");
			sql.append("	,?                             		\n");
			sql.append("	,?                             		\n");
			sql.append("	,?                             		\n");
			sql.append("	,?                             		\n");
			sql.append("	,?                             		\n");
			sql.append("	,?                             		\n");
			sql.append("	,?                             		\n");
			sql.append("	,SYSDATE                       		\n");
			sql.append("	,?                             		\n");
			sql.append("	)                              		\n");

			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, params.get("shipping_id"));
			pstmt.setString(paramIdx++, params.get("shipping_id"));
			pstmt.setString(paramIdx++, sqlParams.get("material_cd"));
			pstmt.setString(paramIdx++, sqlParams.get("ship_qty"));
			pstmt.setString(paramIdx++, sqlParams.get("remark"));
			pstmt.setString(paramIdx++, sqlParams.get("tag_no"));
			pstmt.setString(paramIdx++, sqlParams.get("pur_cont_id"));
			
			if (!sqlParams.get("currency_gb").equals("KRW")) {
				pstmt.setDouble(paramIdx++, PCMSUtil.doRound( PCMSUtil.multiply(Double.parseDouble(returnValue), sqlParams.getDouble("shp_unit_cost")), 0));
				double amt  = PCMSUtil.multiply(sqlParams.getDouble("shp_unit_cost"), sqlParams.getDouble("ship_qty"));
				pstmt.setDouble(paramIdx++, PCMSUtil.doRound( PCMSUtil.multiply(Double.parseDouble(returnValue), amt), 0));
			} else {
				pstmt.setString(paramIdx++, sqlParams.get("shp_unit_cost"));
				pstmt.setDouble(paramIdx++, PCMSUtil.doRound(PCMSUtil.multiply(sqlParams.getDouble("shp_unit_cost"), sqlParams.getDouble("ship_qty")),0));
			}
		 
			pstmt.setString(paramIdx++, sqlParams.get("shp_unit_cost"));
			pstmt.setDouble(paramIdx++, sqlParams.getDouble("shp_unit_cost") * sqlParams.getDouble("ship_qty"));

			pstmt.setString(paramIdx++, storage.getUser().get("ACCT_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("PARTIAL_YN"));
			pstmt.executeUpdate(); 
	
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
			} catch (SQLException se) {
				WAFLogger.error(se);
			}
		}
	}

	public boolean updateSSRDetailSheet(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		String returnValue = "";

		try {
			// 환율 가져오기
			returnValue = getExchangeRate(sqlParams, params);

			sql.append("	UPDATE PSR_SHIPPING_DETAIL SET 		\n");
			sql.append("		 SHIP_QTY = ?                 		\n");
			sql.append("		,SHP_UNIT_COST = ?                 		\n"); //원화
			sql.append("		,SHP_AMT = ?                 		\n");
			sql.append("		,UPD_BY = ?                 		\n");
			sql.append("		,UPD_DATE = SYSDATE                 		\n");
			sql.append("		,EXC_PRI = ?                 		\n"); //외화 
			sql.append("		,EXC_AMT = ?                 		\n");
			sql.append("		,PARTIAL_YN = ?                 		\n");
			sql.append("	WHERE SHIPPING_ID = ?          		\n");
			sql.append("	AND SHIPPING_DETAIL_ID = ?     		\n");
			sql.append("	AND MATERIAL_CD = ?            		\n");

			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("ship_qty"));
			if (!sqlParams.get("currency_gb").equals("KRW")) {
				pstmt.setDouble(paramIdx++, PCMSUtil.doRound( PCMSUtil.multiply(Double.parseDouble(returnValue), sqlParams.getDouble("shp_unit_cost")), 0));
				double amt  = PCMSUtil.multiply(sqlParams.getDouble("shp_unit_cost"), sqlParams.getDouble("ship_qty"));
				pstmt.setDouble(paramIdx++, PCMSUtil.doRound( PCMSUtil.multiply(Double.parseDouble(returnValue), amt), 0));
			} else {
				pstmt.setString(paramIdx++, sqlParams.get("shp_unit_cost"));
				pstmt.setDouble(paramIdx++, PCMSUtil.multiply(sqlParams.getDouble("shp_unit_cost"), sqlParams.getDouble("ship_qty")));
			}
			pstmt.setString(paramIdx++, storage.getUser().get("ACCT_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("shp_unit_cost"));
			pstmt.setDouble(paramIdx++, PCMSUtil.multiply(sqlParams.getDouble("shp_unit_cost"), sqlParams.getDouble("ship_qty")));
			
			pstmt.setString(paramIdx++, sqlParams.get("PARTIAL_YN"));
			pstmt.setString(paramIdx++, sqlParams.get("shipping_id"));
			pstmt.setString(paramIdx++, sqlParams.get("shipping_detail_id"));
			pstmt.setString(paramIdx++, sqlParams.get("material_cd"));

			pstmt.executeUpdate();
			//System.out.println(sql.toString());
		
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
			} catch (SQLException se) {
				WAFLogger.error(se);
			}
		}
	}

 
	public boolean deleteSSRDetailSheet(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		try {
			sql.append("   DELETE                      		    \n");
			sql.append("	 FROM PSR_SHIPPING_DETAIL   		\n");
			sql.append("	WHERE SHIPPING_ID = ?          		\n");
			sql.append("	  AND SHIPPING_DETAIL_ID = ?     		\n");
			sql.append("	  AND MATERIAL_CD = ?            		\n");

			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("shipping_id"));
			pstmt.setString(paramIdx++, sqlParams.get("shipping_detail_id"));
			pstmt.setString(paramIdx++, sqlParams.get("material_cd"));

			pstmt.executeUpdate();
				
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
			} catch (SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	public boolean saveSSRInvSheet(Connection connection,
		ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer(); 

		try {
			sql.append("	INSERT INTO PSR_SHIPPING_INV		\n"); 
			sql.append("	(                              		\n");
			sql.append("		  SHIPPING_ID                		\n");
			sql.append("		, SHIPPING_DETAIL_ID           		\n");
			sql.append("		, INV_NO                		\n");
			sql.append("		, INV_VALUE                   		\n");
			sql.append("		, INV_CUR                    		\n");
			sql.append("		, ORD                    		\n");
			sql.append("		, INV_DESC                    		\n");
			sql.append("		, CRE_BY                     		\n");
			sql.append("		, CRE_DATE                   		\n");
			sql.append("		, UPD_BY                   		\n");
			sql.append("		, UPD_DATE                    		\n");
			sql.append("	) VALUES (                     		\n");
			sql.append("	 ?                              		\n");   //SHIPPING_ID       
			sql.append("	,(SELECT NVL(MAX(SHIPPING_DETAIL_ID)+1,1) FROM PSR_SHIPPING_INV WHERE SHIPPING_ID = ?)   	\n");       //SHIPPING_DETAIL_ID
			sql.append("	,?                             		\n");       //INV_NO            
			sql.append("	,?                             		\n");       //INV_VALUE         
			sql.append("	,?                             		\n");       //INV_CUR 
			sql.append("	,?                             		\n");       //ORD 
			sql.append("	,?                             		\n");       //INV_DESC 
			sql.append("	,?                             		\n");       //CRE_BY            
			sql.append("	,SYSDATE                       		\n");       //CRE_DATE          
			sql.append("	,?                             		\n");       //UPD_BY            
			sql.append("	,SYSDATE                       		\n");       //UPD_DATE          
			sql.append("	)                              		\n");

			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, params.get("shipping_id"));
			pstmt.setString(paramIdx++, params.get("shipping_id")); 
			pstmt.setString(paramIdx++, sqlParams.get("INV_NO"));
			pstmt.setDouble(paramIdx++, sqlParams.getDouble("INV_VALUE"));
			pstmt.setString(paramIdx++, sqlParams.get("INV_CUR"));
			pstmt.setString(paramIdx++, sqlParams.get("ORD"));
			pstmt.setString(paramIdx++, sqlParams.get("INV_DESC"));
			pstmt.setString(paramIdx++, storage.getUser().get("ACCT_ID"));
			pstmt.setString(paramIdx++, storage.getUser().get("ACCT_ID"));
			pstmt.executeUpdate();
			
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
			} catch (SQLException se) {
				WAFLogger.error(se);
			}
		}
	}

	public boolean updateSSRInvSheet(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer(); 

		try {
			sql.append("   UPDATE PSR_SHIPPING_INV  		        \n");
			sql.append("	  SET INV_NO = ?                 		\n");
			sql.append("		 ,INV_VALUE = ?                 	\n"); 
			sql.append("		 ,INV_CUR = ?                 		\n");
			sql.append("		 ,ORD = ?                    		\n");
			sql.append("		 ,INV_DESC = ?                      \n");
			sql.append("		 ,UPD_BY = ?                 		\n");
			sql.append("		 ,UPD_DATE = SYSDATE                \n");
			sql.append("	WHERE SHIPPING_ID = ?          		    \n");
			sql.append("	  AND SHIPPING_DETAIL_ID = ?     		\n"); 

			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("INV_NO"));
			pstmt.setDouble(paramIdx++, sqlParams.getDouble("INV_VALUE"));
			pstmt.setString(paramIdx++, sqlParams.get("INV_CUR"));
			pstmt.setString(paramIdx++, sqlParams.get("ORD"));
			pstmt.setString(paramIdx++, sqlParams.get("INV_DESC"));
			pstmt.setString(paramIdx++, storage.getUser().get("ACCT_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("shipping_id"));
			pstmt.setString(paramIdx++, sqlParams.get("shipping_detail_id")); 

			pstmt.executeUpdate();  
			
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
			} catch (SQLException se) {
				WAFLogger.error(se);
			}
		}
	}

 
	public boolean deleteSSRInvSheet(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		try {
			sql.append("   DELETE                      		    \n");
			sql.append("	 FROM PSR_SHIPPING_INV   		    \n");
			sql.append("	WHERE SHIPPING_ID = ?          		\n");
			sql.append("	  AND SHIPPING_DETAIL_ID = ?     	\n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("shipping_id"));
			pstmt.setString(paramIdx++, sqlParams.get("shipping_detail_id")); 

			pstmt.executeUpdate();
				
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
			} catch (SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	
	public String getExchangeRate(ValueObject sqlParams, ValueObject params) throws Exception {
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		StringBuffer sql = new StringBuffer();
		String returnValue = "";

		try {

			sql.append("	SELECT DECODE(F_GET_EXCHANGE_RATE(?,  ? , 'KRW'), 0, '1', F_GET_EXCHANGE_RATE(?,  ? , 'KRW')) EXCHANGE_RATE  	\n");
			sql.append("	FROM DUAL  	\n");

			
			search.setStatement(sql.toString());
			search.addParameter(params.get("departure"));  //2016.5.4 shipDate->polAtd
			search.addParameter(sqlParams.get("currency_gb"));
			search.addParameter(params.get("departure"));//2016.5.4 shipDate->polAtd
			search.addParameter(sqlParams.get("currency_gb"));
			rowSet = search.execute();

			if (rowSet.size() > 0) {
				returnValue = rowSet.getRow(0).get("EXCHANGE_RATE");
			}

			return returnValue;
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName()
					+ " --> getExchangeRate() : \n" + se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		} finally {
			return returnValue;
		}
	}

	public boolean confirmData(Connection connection, ValueObject params) throws Exception {
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();

		try {
			sql.append("	UPDATE PSR_SHIPPING 	                               \n");
			sql.append("	   SET STATUS = ?                		               \n");
			sql.append("	      ,UPD_BY = ?                		               \n");
			sql.append("	      ,UPD_DATE = SYSDATE                              \n");
			sql.append("	      ,CONFIRM_DATE = TO_CHAR(SYSDATE, 'YYYYMMDD')     \n");
			sql.append("		  ,CONFIRM_EMP_NO = ?                 		       \n");
			sql.append("	 WHERE SHIPPING_ID = ?            		               \n");

			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, params.get("WILL_STATUS"));
			pstmt.setString(paramIdx++, storage.getUser().get("ACCT_ID"));
			pstmt.setString(paramIdx++, storage.getUser().get("ACCT_ID"));
			pstmt.setString(paramIdx++, params.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);
			}
		}
	}
    private ValueObject getShippingId(Connection conn) throws Exception {
		
		Persistent persistent_q = null;
		
		ValueObject vo = new ValueObject();
		ValueObject row = null;
		String sqlstr = "";
		
		try{
			persistent_q = new PersistentImpl(conn);
			sqlstr = "SELECT PSR_SHIPPING_S.NEXTVAL SHIPPING_ID FROM DUAL";
			persistent_q.setStatement(sqlstr); 
			row = RowSetUtility.getValueObject(persistent_q.query().getRow(0));
			vo.set("shipping_id", row.get("shipping_id"));
		}catch(Exception e){
			WAFLogger.error(this.getClass().getName() + " --> getSeq() : " + e.getMessage());
			WAFLogger.error(e);
			throw e;
            
		}finally{
			if(vo == null) vo = new ValueObject();
		}
		
		return vo;
	}
    
    public boolean updateATDATADetail(Connection connection, ValueObject params) throws Exception {
		Persistent persistent = null;
		StringBuffer sql = new StringBuffer();

		sql.append("	UPDATE PSR_SHIPPING SET 		\n");
		sql.append("		DEPARTURE  = ?         		\n");
		sql.append("		, ARRIVAL = ?           		\n");
		sql.append("		, ONSITE = ?           		\n");
		sql.append("	WHERE SHIPPING_ID = ?   		\n");

		try {
			persistent = new PersistentImpl(connection);
			persistent.setStatement(sql.toString());

			persistent.addParameter(params.get("DEPARTURE").replaceAll("-", ""));
			persistent.addParameter(params.get("ARRIVAL").replaceAll("-", ""));
			persistent.addParameter(params.get("ONSITE").replaceAll("-", ""));
			persistent.addParameter(params.get("shipping_id"));

			WAFLogger.debug(sql.toString());
			persistent.execute();
			persistent.clearParameters();		
		
			return true;
		} catch (Exception e) {
			super.storage.setMessage(e.getMessage());
			super.storage.setDetailMessage(e);
			throw e;
		}
	}
}
