/**
 * @(#)file    SuppSSRDetailSaveBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.08.20
 * Copyright (c) www.udapsoft.co.kr, Inc.
 */
package kr.co.udapsoft.ebid.buyer.process.process.biz;

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

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.base.WAFMessages;
import kr.co.hsnc.common.logger.Logger;
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 kr.co.udapsoft.common.sender.MessageSend;
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 SuppSSRDetailSaveBiz extends Biz {
	public static int tempReturnValue = 0;
	
	public SuppSSRDetailSaveBiz(HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
        boolean success = false;
        if ( storage.compareEvent("SAVE_SUPP_PARTIAL_MATERIAL_SHEET") ) {
        	String[] status = super.gets("SHEET1.status");
			
			try {
				super.conn.begin();
				Connection conn = super.conn.getConnection();
				
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusInsert(status[i])) {
						success = savePartialMaterial(conn, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
					if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						success = updatePartialMaterial(conn, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
					if (IBSheet7Utils.equalStatusDelete(status[i])) {
						success = deletePartialMaterial(conn, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
				}
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(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();
			}
        } else if ( storage.compareEvent("DELETE_SUPP_SSR_DETAIL_SHEET") ) {
        	String[] status = super.gets("SHEET1.status");
			
			try {
				super.conn.begin();
				
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusDelete(status[i])) {
						success = deleteSSRDetailSheet(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);
				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();
			}
        } else if ( storage.compareEvent("SUPP_CONFIRM_SSR_DETAIL") ) {
        	// sheet 저장
        	try {
        		super.conn.begin();
        		
        		Connection conn = super.conn.getConnection();
        		
        		success = updateSSRDetail(conn, params);
            	
        		String[] status = super.gets("SHEET1.status");
    			
    			for (int i = 0; i < status.length; i++) {
    				if (IBSheet7Utils.equalStatusInsert(status[i])) {
    					success = saveSSRDetailSheet(conn, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
    				}
    				if (IBSheet7Utils.equalStatusUpdate(status[i])) {
    					success = updateSSRDetailSheet(conn, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
    				}
    				if (IBSheet7Utils.equalStatusDelete(status[i])) {
    					success = deleteSSRDetailSheet(conn, IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
    				}
    			}
    			//2016.8.31 환율재적용
    			confirmExchange(conn, params);
    			success = confirmData2(conn, params);
            	
    			super.storage.setSuccess(success);
    			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
        	} catch (Exception e) {
        		WAFLogger.error(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();
        		
        		// SMS 이메일 발송
        		if( storage.getSuccess() == true ) {
        			sendSMS(params);
        		}
        	}
        } else {
        	try {
        		super.conn.begin();
        		Connection connection = super.conn.getConnection();
        		
	        	if( storage.compareEvent("SAVE_SUPP_SSR_DETAIL") ) {
	            	success = saveSSRDetail(connection, params);
	                storage.setSuccess(success);
	            } else if( storage.compareEvent("UPDATE_SUPP_SSR_DETAIL") ) {
	                success = updateSSRDetail(connection, params);
	                storage.setSuccess(success);
	            } else if( storage.compareEvent("DELETE_SUPP_SSR_DETAIL") ) {
	                success = deleteSSRDetail(connection, params);
	                storage.setSuccess(success);
	            }
	        	
	        	if(storage.compareEvent("SAVE_SUPP_SSR_DETAIL_SHEET") ) {
	        		String[] status = super.gets("SHEET1.status");
	    			
    				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));
						}
    				}
    				super.storage.setSuccess(success);
    				if(success)
    					storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
  	        	}
	        } catch (Exception e) {
				WAFLogger.error(e);
				super.doException(e);
				super.storage.setDetailMessage(e);
				super.storage.setMessage(e.getMessage());
				super.storage.setSuccess(false);
				super.conn.setRollback();
			} finally {
				super.conn.end();
			} 
        }
	}
	
	//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 conn, 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("		, SHIP_NO             		\n");
        sql.append("		, SHIP_NAME           		\n");
        sql.append("		, BL_NO               		\n");
        sql.append("		, INVOICE_NO          		\n");
        sql.append("		, INVOICE_AMT         		\n");
        sql.append("		, ORIGIN               		\n");
        sql.append("		, CLEAR_CUSTOM        		\n");
        sql.append("		, PACKAGE_QTY         		\n");
        sql.append("		, PKG_CBM             		\n");
        sql.append("		, VENDOR_CD           		\n");
        sql.append("		, PKG_WGT             		\n");
        sql.append("		, SHIP_MODE           		\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("		, 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("		, VESSEL_NAME         		\n"); 
        sql.append("		, ETD_ONSITE          		\n");
        sql.append("		, ONSITE              		\n");
        sql.append("		, REMARKS             		\n");
        sql.append("		, DEPT_CD             		\n");
        sql.append("		, CRE_BY             		\n");
        sql.append("		, CRE_DATE             		\n");
        sql.append("		, SHIP_DATE             		\n");
        sql.append("		, ORDER_FLAG             		\n");
        sql.append("		, STATUS             		\n");
        sql.append("		, DEPTEMPNO             		\n");
		sql.append("		, DEPTEMP_EMAIL             		\n");
		sql.append("		, DEPTEMP_HP             		\n");
		sql.append("		, PUREMP_EMAIL             		\n");
		sql.append("		, PUREMP_HP             		\n");
		sql.append("		, PUR_EMP_NO             		\n");
		sql.append("		, SHORE_YN             		\n");
        sql.append("	) VALUES (              		\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("	,?                      		\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("	,?                      		\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");
        sql.append("	,'1'                      		\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");
		
		try {
            persistent = new PersistentImpl(conn);
            persistent.setStatement(sql.toString());
            
            persistent.addParameter(params.get("shipping_id"));
            persistent.addParameter(getShipNo(params));
            persistent.addParameter(params.get("item_description"));
            persistent.addParameter(params.get("bl_no"));
            persistent.addParameter(params.get("invoice_no"));
            //persistent.addParameter(params.get("payment"));
            persistent.addParameter(params.get("invoice_value"));
            //persistent.addParameter(params.get("condition"));
            persistent.addParameter(params.get("origin"));
            persistent.addParameter(params.get("cc").replaceAll("-", ""));
            persistent.addParameter(params.get("pkg"));
            persistent.addParameter(params.get("cbm"));
            persistent.addParameter(params.get("corp_id"));
            persistent.addParameter(params.get("kgs"));
            persistent.addParameter(params.get("shipping_mode"));
            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("polEtd").replaceAll("-", ""));
            persistent.addParameter(params.get("polAtd").replaceAll("-", ""));
            persistent.addParameter(params.get("podEtd").replaceAll("-", ""));
            persistent.addParameter(params.get("podAtd").replaceAll("-", ""));
            persistent.addParameter(params.get("pol"));
            persistent.addParameter(params.get("pod"));
            persistent.addParameter(params.get("vessel_name"));
            persistent.addParameter(params.get("onsitePlanDate").replaceAll("-", ""));
            persistent.addParameter(params.get("onsiteArrivalDate").replaceAll("-", ""));
            persistent.addParameter(params.get("remarks"));
            persistent.addParameter(params.get("dept_cd"));
            persistent.addParameter(storage.getUser().get("CORP_ID"));
            persistent.addParameter(params.get("polAtd").replaceAll("-", ""));
            persistent.addParameter(params.get("oversea_gb"));
            persistent.addParameter(params.get("dept_emp_no"));
            persistent.addParameter(params.get("dept_e_mail"));
			persistent.addParameter(params.get("dept_hand_tel_no"));
			persistent.addParameter(params.get("pur_e_mail"));
			persistent.addParameter(params.get("pur_hand_tel_no"));
			persistent.addParameter(params.get("pur_emp_no"));
			persistent.addParameter(params.get("SHORE_YN"));
            
            WAFLogger.debug(sql.toString());
            persistent.execute();
            persistent.clearParameters();
              
            return true;
		}
        catch(Exception e) {
        	throw new Exception(e);
        }
    }
		
	public boolean updateSSRDetail(Connection conn, ValueObject params) throws Exception {
		Persistent persistent = null;
		StringBuffer sql   = new StringBuffer();
		
		sql.append("	UPDATE PSR_SHIPPING SET 		\n");
		sql.append("		SHIP_NAME = ?         		\n");
		sql.append("		, BL_NO = ?           		\n");
		sql.append("		, INVOICE_NO = ?      		\n");
		sql.append("		, INVOICE_AMT = ?     		\n");
		sql.append("		, ORIGIN = ?           		\n");
		sql.append("		, CLEAR_CUSTOM = ?    		\n");
		sql.append("		, PACKAGE_QTY = ?     		\n");
		sql.append("		, PKG_CBM = ?         		\n");
		sql.append("		, VENDOR_CD = ?       		\n");
		sql.append("		, PKG_WGT = ?         		\n");
		sql.append("		, SHIP_MODE = ?       		\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("		, 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("		, VESSEL_NAME = ?     		\n");
		sql.append("		, ETD_ONSITE = ?      		\n");
		sql.append("		, ONSITE = ?          		\n");
		sql.append("		, REMARKS = ?         		\n");
		sql.append("		, UPD_BY = ?         		\n");
		sql.append("		, UPD_DATE = SYSDATE         		\n");
		sql.append("		, SHIP_DATE = ?         		\n");
		sql.append("		, DEPTEMPNO = ?         		\n");
		sql.append("		, DEPTEMP_EMAIL = ?           		\n");
		sql.append("		, DEPTEMP_HP = ?             		\n");
		sql.append("		, PUREMP_EMAIL = ?             		\n");
		sql.append("		, PUREMP_HP = ?             		\n");
		sql.append("		, PUR_EMP_NO = ?             		\n");
		sql.append("		, SHORE_YN = ?             		\n");
		sql.append("	WHERE SHIPPING_ID = ?   		\n");
		
		try {
			persistent = new PersistentImpl(conn);
			persistent.setStatement(sql.toString());
			
			persistent.addParameter(params.get("item_description"));
			persistent.addParameter(params.get("bl_no"));
			persistent.addParameter(params.get("invoice_no"));
			persistent.addParameter(params.get("invoice_value").replaceAll(",",""));
			persistent.addParameter(params.get("origin"));
			persistent.addParameter(params.get("cc").replaceAll("-", ""));
			persistent.addParameter(params.get("pkg").replaceAll(",",""));
			persistent.addParameter(params.get("cbm"));
			persistent.addParameter(params.get("corp_id"));
			persistent.addParameter(params.get("kgs").replaceAll(",",""));
			persistent.addParameter(params.get("shipping_mode"));
			persistent.addParameter(params.get("twenty_dc").replaceAll(",",""));
			persistent.addParameter(params.get("twenty_dt").replaceAll(",",""));
			persistent.addParameter(params.get("twenty_fr").replaceAll(",",""));
			persistent.addParameter(params.get("fourty_dc").replaceAll(",",""));
			persistent.addParameter(params.get("fourty_dt").replaceAll(",",""));
			persistent.addParameter(params.get("fourty_fr").replaceAll(",",""));
			persistent.addParameter(params.get("fourty_fft").replaceAll(",",""));
			persistent.addParameter(params.get("polEtd").replaceAll("-", ""));
			persistent.addParameter(params.get("polAtd").replaceAll("-", ""));
			persistent.addParameter(params.get("podEtd").replaceAll("-", ""));
			persistent.addParameter(params.get("podAtd").replaceAll("-", ""));
			persistent.addParameter(params.get("pol"));
			persistent.addParameter(params.get("pod"));
			persistent.addParameter(params.get("vessel_name"));
			persistent.addParameter(params.get("onsitePlanDate").replaceAll("-", ""));
			persistent.addParameter(params.get("onsiteArrivalDate").replaceAll("-", ""));
			persistent.addParameter(params.get("remarks"));
			persistent.addParameter(storage.getUser().get("CORP_ID"));
			persistent.addParameter(params.get("polAtd").replaceAll("-", ""));
			persistent.addParameter(params.get("dept_emp_no"));
			persistent.addParameter(params.get("dept_e_mail"));
			persistent.addParameter(params.get("dept_hand_tel_no"));
			persistent.addParameter(params.get("pur_e_mail"));
			persistent.addParameter(params.get("pur_hand_tel_no"));
			persistent.addParameter(params.get("pur_emp_no"));
			persistent.addParameter(params.get("SHORE_YN"));
			persistent.addParameter(params.get("shipping_id"));
			
			WAFLogger.debug(sql.toString());
			persistent.execute();
			persistent.clearParameters();
			
			return true;
		}
		catch(Exception e) {
			throw new Exception(e);
		}
	}
	
	public boolean deleteSSRDetail(Connection conn, ValueObject params) throws Exception {
		Persistent persistent = null;
		StringBuffer sql   = new StringBuffer();
		StringBuffer sql2   = new StringBuffer();
		
		sql.append("	DELETE FROM PSR_SHIPPING_DETAIL WHERE SHIPPING_ID = ?	\n");
		
		sql2.append("	DELETE FROM PSR_SHIPPING WHERE SHIPPING_ID = ?	\n");
		try {
			persistent = new PersistentImpl(conn);
			persistent.setStatement(sql.toString());
			persistent.addParameter(params.get("shipping_id"));
			WAFLogger.debug(sql.toString());
			persistent.execute();
			persistent.clearParameters();
			
			persistent.setStatement(sql2.toString());
			persistent.addParameter(params.get("shipping_id"));
			WAFLogger.debug(sql2.toString());
			persistent.execute();
			persistent.clearParameters();
			
			return true;
		}
		catch(Exception e) {
			throw new Exception(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("	) VALUES (                     		\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("	,?                             		\n");
		   sql.append("	,SYSDATE                       		\n");
		   sql.append("	)                              		\n");
		   
	        pstmt = connection.prepareStatement(sql.toString());
	        int paramIdx = 1;
			pstmt.setString(paramIdx++, getShippingDetailId(params));
			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"));
			
			//2016.6.30 
			if(!sqlParams.get("currency_gb").equals("KRW")) {
				pstmt.setDouble(paramIdx++, PCMSUtil.doRound( PCMSUtil.multiply(Double.parseDouble(returnValue), sqlParams.getDouble("item_price")), 0));
				double amt  = PCMSUtil.multiply(sqlParams.getDouble("item_price"), sqlParams.getDouble("ship_qty"));
				pstmt.setDouble(paramIdx++, PCMSUtil.doRound( PCMSUtil.multiply(Double.parseDouble(returnValue), amt), 0));
			} else {
				pstmt.setString(paramIdx++, sqlParams.get("item_price"));
				pstmt.setDouble(paramIdx++, PCMSUtil.doRound(PCMSUtil.multiply(sqlParams.getDouble("item_price"), sqlParams.getDouble("ship_qty")),0));
			}
			pstmt.setString(paramIdx++, sqlParams.get("item_price"));
			pstmt.setDouble(paramIdx++, sqlParams.getDouble("item_price") * sqlParams.getDouble("ship_qty"));
			
			
			pstmt.setString(paramIdx++, storage.getUser().get("CORP_ID"));
					
			pstmt.executeUpdate();

			String[] temp1 = null;

			temp1 = new String[] { params.get("polAtd"),
					params.get("podEtd"), params.get("podAtd"),
					params.get("polEtd"), params.get("polAtd"),
					params.get("onsitePlanDate"),
					params.get("onsiteArrivalDate") };

			String[] temp2 = { "51", "53", "53", "55", "55", "59", "59" };
	
			String[] temp3 = { "3", "1", "3", "1", "3", "1", "3" };
	
			for (int i = 0; i < 7; i++) {
				params.set("psr_activity_cd2", temp2[i]);
				params.set("date2", temp1[i]);
				params.set("activity_kind2", temp3[i]);
	
				psrUpdate(super.conn.getConnection(),sqlParams.getInt("pur_cont_id"), "I");
			}
			
			
			callRegContQtyShip(super.conn.getConnection(), sqlParams);
			
			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("		,UPD_BY = ?                 		\n");
		   sql.append("		,UPD_DATE = SYSDATE                 \n");
		   sql.append("		,SHP_UNIT_COST = ?              	\n"); //KRW 2016.7.1
		   sql.append("		,SHP_AMT =?                    		\n");
		   sql.append("		,EXC_PRI = ?                 		\n"); //외화
		   sql.append("		,EXC_AMT = ?                 		\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"));
			pstmt.setString(paramIdx++, storage.getUser().get("CORP_ID"));
			
			if(!sqlParams.get("currency_gb").equals("KRW")) {
				pstmt.setDouble(paramIdx++, PCMSUtil.doRound( PCMSUtil.multiply(Double.parseDouble(returnValue), sqlParams.getDouble("item_price")), 0));
				double amt  = PCMSUtil.multiply(sqlParams.getDouble("item_price"), sqlParams.getDouble("ship_qty"));
				pstmt.setDouble(paramIdx++, PCMSUtil.doRound( PCMSUtil.multiply(Double.parseDouble(returnValue), amt), 0));
			} else {
				pstmt.setString(paramIdx++, sqlParams.get("item_price"));
				pstmt.setDouble(paramIdx++, PCMSUtil.doRound(PCMSUtil.multiply(sqlParams.getDouble("item_price"), sqlParams.getDouble("ship_qty")),0));
			}
			pstmt.setString(paramIdx++, sqlParams.get("item_price"));
			pstmt.setDouble(paramIdx++, PCMSUtil.doRound(PCMSUtil.multiply(sqlParams.getDouble("item_price"), sqlParams.getDouble("ship_qty")),0));
	
			pstmt.setString(paramIdx++, sqlParams.get("shipping_id"));
			pstmt.setString(paramIdx++, sqlParams.get("shipping_detail_id"));
			pstmt.setString(paramIdx++, sqlParams.get("material_cd"));
			pstmt.executeUpdate();
			
			String[] temp1 = null;

			temp1 = new String[] { params.get("polAtd"),
					params.get("podEtd"), params.get("podAtd"),
					params.get("polEtd"), params.get("polAtd"),
					params.get("onsitePlanDate"),
					params.get("onsiteArrivalDate") };

			String[] temp2 = { "51", "53", "53", "55", "55", "59", "59" };
	
			String[] temp3 = { "3", "1", "3", "1", "3", "1", "3" };
	
			for (int i = 0; i < 7; i++) {
				params.set("psr_activity_cd2", temp2[i]);
				params.set("date2", temp1[i]);
				params.set("activity_kind2", temp3[i]);
	
				psrUpdate(super.conn.getConnection(),sqlParams.getInt("pur_cont_id"), "U");
			}
			
			callRegContQtyShip(super.conn.getConnection(), sqlParams);
			
			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 {
			getPsrShippingNotiCnt(connection, sqlParams);
			
			sql.append("	DELETE FROM PSR_SHIPPING_DETAIL WHERE SHIPPING_DETAIL_ID = ?	\n");
			sql.append("		AND SHIPPING_ID = ?                 		\n");
			sql.append("		AND MATERIAL_CD = ?			\n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("shipping_detail_id"));
			pstmt.setString(paramIdx++, sqlParams.get("shipping_id"));
			pstmt.setString(paramIdx++, sqlParams.get("material_cd"));
			pstmt.executeUpdate();
			

			String[] temp1 = null;

				temp1 = new String[] { params.get("polAtd"),
						params.get("podEtd"), params.get("podAtd"),
						params.get("polEtd"), params.get("polAtd"),
						params.get("onsitePlanDate"),
						params.get("onsiteArrivalDate") };

			String[] temp2 = { "51", "53", "53", "55", "55", "59", "59" };

			String[] temp3 = { "3", "1", "3", "1", "3", "1", "3" };

			for (int i = 0; i < 7; i++) {
				params.set("psr_activity_cd2", temp2[i]);
				params.set("date2", temp1[i]);
				params.set("activity_kind2", temp3[i]);

				psrUpdate(super.conn.getConnection(),sqlParams.getInt("pur_cont_id"), "D");
			}
			
			
			//callRegContQtyShip(super.conn.getConnection(), sqlParams);
			
			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 getShipNo(ValueObject params) throws Exception {
	   Search search 	  = new SearchImpl();
       RowSet rowSet 	  = new RowSetImpl();
       StringBuffer sql  = new StringBuffer();
       String returnValue = "";
       
       try {
    	   sql.append("	SELECT 'SRN'||'"+params.get("dept_cd")+"'||LPAD(NVL(SUBSTR(MAX(SHIP_NO),8),0)+1,3,'0') SRN_NO	\n");
    	   sql.append("	FROM PSR_SHIPPING		\n");
		   sql.append("	WHERE DEPT_CD = '"+params.get("dept_cd")+"'            		\n");
       	
		   WAFLogger.debug(sql.toString());
           search.setStatement(sql.toString());
           rowSet = search.execute();
           if(rowSet.size()>0){
        	   returnValue = rowSet.getRow(0).get("SRN_NO");
           }
           return returnValue;
       } catch (WAFSQLException se) {
       	WAFLogger.error(this.getClass().getName() + " --> getShipNo() : \n" + se.getMessage());
           WAFLogger.error("SQL : " + search.getStatement());
           WAFLogger.error(se);
           storage.setDetailMessage(se);
       }
       finally {    
           return returnValue;
       }
    }
	
	public boolean savePartialMaterial(Connection connection, ValueObject sqlParams) throws Exception {
		   PreparedStatement pstmt = null;
		   StringBuffer sql   = new StringBuffer();
		   
		   try {
			   sql.append("	INSERT INTO PSR_SHIPPING_NOTI		\n");
			   sql.append("	(                              		\n");
			   sql.append("		PARTIAL_LINE           		\n");
			   sql.append("		, SHIPPING_DETAIL_ID                		\n");
			   sql.append("		, SHIPPING_ID                		\n");
			   sql.append("		, MATERIAL_CD                		\n");
			   sql.append("		, PARTIAL_NAME                   		\n");
			   sql.append("		, PARTIAL_QTY                    		\n");
			   sql.append("		, PARTIAL_TEXT                     		\n");
			   sql.append("		, CRE_BY                		\n");
			   sql.append("		, CRE_DATE              		\n");
			   sql.append("		, UPD_BY              		\n");
			   sql.append("		, UPD_DATE              		\n");
			   sql.append("		, PUR_CONT_ID              		\n");
			   sql.append("	) VALUES (                     		\n");
			   sql.append("	PSR_SHIPPING_NOTI_S.NEXTVAL                              		\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("	,SYSDATE                             		\n");
			   sql.append("	,?                             		\n");
			   sql.append("	)                              		\n");
			   
		        pstmt = connection.prepareStatement(sql.toString());
		        int paramIdx = 1;
				pstmt.setString(paramIdx++, sqlParams.get("shipping_detail_id"));
				pstmt.setString(paramIdx++, sqlParams.get("shipping_id"));
				pstmt.setString(paramIdx++, sqlParams.get("material_cd"));
				pstmt.setString(paramIdx++, sqlParams.get("partial_name"));
				pstmt.setString(paramIdx++, sqlParams.get("partial_qty"));
				pstmt.setString(paramIdx++, sqlParams.get("partial_text"));
				pstmt.setString(paramIdx++, storage.getUser().get("CORP_ID"));
				pstmt.setString(paramIdx++, storage.getUser().get("CORP_ID"));
				pstmt.setString(paramIdx++, params.get("pur_cont_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 updatePartialMaterial(Connection connection, ValueObject sqlParams) throws Exception {
		   PreparedStatement pstmt = null;
		   StringBuffer sql   = new StringBuffer();
		   
		   try {
			   sql.append("	UPDATE PSR_SHIPPING_NOTI SET 		\n");
			   sql.append("		PARTIAL_NAME = ?                 		\n");
			   sql.append("		, PARTIAL_QTY = ?                 		\n");
			   sql.append("		,PARTIAL_TEXT = ?                 		\n");
			   sql.append("		,UPD_BY = ?                 		\n");
			   sql.append("		,UPD_DATE = SYSDATE                 		\n");
			   sql.append("	WHERE PARTIAL_LINE = ?          		\n");
			   sql.append("	AND SHIPPING_DETAIL_ID = ?     		\n");
			   sql.append("	AND SHIPPING_ID = ?            		\n");
			   sql.append("	AND MATERIAL_CD = ?            		\n");
			   
		        pstmt = connection.prepareStatement(sql.toString());
		        int paramIdx = 1;
				pstmt.setString(paramIdx++, sqlParams.get("partial_name"));
				pstmt.setString(paramIdx++, sqlParams.get("partial_qty"));
				pstmt.setString(paramIdx++, sqlParams.get("partial_text"));
				pstmt.setString(paramIdx++, storage.getUser().get("CORP_ID"));
				pstmt.setString(paramIdx++, sqlParams.get("partial_line"));
				pstmt.setString(paramIdx++, sqlParams.get("shipping_detail_id"));
				pstmt.setString(paramIdx++, sqlParams.get("shipping_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 String getShippingDetailId(ValueObject params) throws Exception {
		   Search search 	  = new SearchImpl();
	       RowSet rowSet 	  = new RowSetImpl();
	       StringBuffer sql  = new StringBuffer();
	       String returnValue = "";
	       
	       try {
	    	   sql.append("	SELECT NVL(MAX(SHIPPING_DETAIL_ID),0) + 1 SHIPPING_DETAIL_ID 	\n");
	    	   sql.append("	FROM PSR_SHIPPING_DETAIL		\n");
			   sql.append("	WHERE SHIPPING_ID = '"+params.get("shipping_id")+"'            		\n");
	       	
			   WAFLogger.debug(sql.toString());
	           search.setStatement(sql.toString());
	           rowSet = search.execute();
	           if(rowSet.size()>0){
	        	   returnValue = rowSet.getRow(0).get("SHIPPING_DETAIL_ID");
	           }
	           
	           return returnValue;
	       } catch (WAFSQLException se) {
	       	WAFLogger.error(this.getClass().getName() + " --> getShippingDetailId() : \n" + se.getMessage());
	           WAFLogger.error("SQL : " + search.getStatement());
	           WAFLogger.error(se);
	           storage.setDetailMessage(se);
	       }
	       finally {    
	           return returnValue;
	       }
	    }
		
		
		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");
	    	   
	    	   WAFLogger.debug(sql.toString());
	           search.setStatement(sql.toString());
	           search.addParameter(params.get("polAtd"));
	           search.addParameter(sqlParams.get("currency_gb"));
	           search.addParameter(params.get("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 psrUpdate(Connection conn, int purContId, String Gubun) throws Exception {
			CallableStatement cs = null;
			ValueObject rtnVo = new ValueObject();
			String pssql = "";

			try {
				pssql = "{call SP_REG_SSR_PSR_STATUS(?,?,?,?,?,?,?,?,?,?,?,?,?)}";
				/*
				 * VI_DEPT_CD IN VARCHAR2, --PROJECT_CD VI_ACTIVITY_CD IN VARCHAR2,
				 * --PSR_ACTIVITY_CD VI_ACTIVITY_KIND IN VARCHAR2, --1=PLAN,
				 * 2=FORECASTING,3=ACTUAL VI_MR_NO IN VARCHAR2, --MR_NO
				 * VI_DEMANDED_ID IN NUMBER, --DEMANDED_ID VI_PUR_CONT_ID IN NUMBER,
				 * --PUR_CONT_ID VI_IN_USER_CD IN NUMBER, --작성자 사번 VI_ACTIVITY_DATE
				 * IN VARCHAR2, --일자 VO_SUCCESS_CD OUT VARCHAR2, --성공여부 S:성공 , N:실폐
				 * VO_MESSAGE OUT VARCHAR2
				 */

				cs = conn.prepareCall(pssql);

				cs.setString(1, params.get("dept_cd2"));
				cs.setString(2, params.get("psr_activity_cd2"));
				cs.setString(3, params.get("activity_kind2"));
				cs.setString(4, "");
				// cs.setInt(5, 0);
				// cs.setInt(6, 0);
				cs.setObject(5, null);
				cs.setInt(6, purContId);

				cs.setString(7, "");
				cs.setInt(8, storage.getUser().getInt("ACCT_ID"));
				cs.setString(9, params.get("date2").replaceAll("-", ""));
				cs.setString(10, params.get("remarks"));
				cs.setString(11, Gubun);

				cs.registerOutParameter(12, java.sql.Types.VARCHAR);
				cs.registerOutParameter(13, java.sql.Types.VARCHAR);

				/*
				 * System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~");
				 * System.out.println("1 ==> " + params.get("dept_cd2"));
				 * System.out.println("2 ==> " + params.get("psr_activity_cd2"));
				 * System.out.println("3 ==> " + params.get("activity_kind2"));
				 * System.out.println("4 ==> " + ""); // System.out.println("5 ==> "
				 * + 0); // System.out.println("6 ==> " + 0);
				 * System.out.println("5 ==> " + 0); System.out.println("6 ==> " +
				 * params.getInt("pur_cont_id")); System.out.println("7 ==> " + "");
				 * System.out.println("8 ==> " +
				 * storage.getUser().getInt("ACCT_ID")); System.out.println("9 ==> "
				 * + params.get("date2").replaceAll("-", ""));
				 * System.out.println("10 ==> " + params.get("remarks"));
				 */

				cs.execute();
				cs.clearParameters();
				cs.close();

				/*
				 * System.out.println("11 ==> " + cs.getString(11));
				 * System.out.println("12 ==> " + cs.getString(12));
				 * System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~");
				 */

				// rtnVo.set("VO_SUCCESS_CD",cs.getString(11));
				// rtnVo.set("VO_MESSAGE",cs.getString(12));

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

			}
		}
		
		public boolean callRegContQtyShip(Connection conn, ValueObject sqlParams) throws Exception {
			CallableStatement cs 	= null;
			ValueObject rtnVo = new ValueObject();
			String pssql			= "";
			
			try {
	        	pssql = "{call SP_REG_CONT_QTY_SHIP(?,?,?,?,?)}";
	        	/*
	        	 *    
	        	 *       VI_MATERIAL_CD   IN VARCHAR2,         --PROJECT_CD
						   VI_PUR_CONT_ID   IN NUMBER,           --PUR_CONT_ID
						   VI_IN_USER_CD    IN NUMBER,           --작성자 사번  
						   VO_SUCCESS_CD    OUT VARCHAR2,        --성공여부 S:성공 , N:실폐
						   VO_MESSAGE       OUT VARCHAR2
	        	 *    
	        	 */
	        	
	        	cs = conn.prepareCall(pssql);
	        	
	        		cs.setString(1, sqlParams.get("material_cd"));
	            	cs.setInt(2, sqlParams.getInt("pur_cont_id"));
	            	cs.setInt(3, storage.getUser().getInt("CORP_ID"));
	            	cs.registerOutParameter(4, java.sql.Types.VARCHAR);
	                cs.registerOutParameter(5, java.sql.Types.VARCHAR);
	          /*  	
	                System.out.println("!!!!!!!!!!!!!!!!!!!!!!!!!!!");
	                System.out.println("1 => " + sqlParams.get("material_cd"));
	                System.out.println("2 => " + sqlParams.getInt("pur_cont_id"));
	                System.out.println("3 => " + storage.getUser().getInt("CORP_ID"));
	                System.out.println("!!!!!!!!!!!!!!!!!!!!!!!!!!!");
	           */     
	                
	            	cs.execute();
	            	
//	            	rtnVo.set("VO_SUCCESS_CD",cs.getString(4));
//	                rtnVo.set("VO_MESSAGE",cs.getString(5));
	        	
	            return true;
	        }
	        catch(Exception e) {
	        	throw e;
	        	
	        }
		}
		
		public boolean deletePartialMaterial(Connection connection, ValueObject sqlParams) throws Exception {
			PreparedStatement pstmt = null;
			StringBuffer sql   = new StringBuffer();
			
			try {
				sql.append("	DELETE FROM PSR_SHIPPING_NOTI WHERE PARTIAL_LINE = ? 		\n");
				sql.append("		AND SHIPPING_DETAIL_ID = ?                 		\n");
				sql.append("		AND SHIPPING_ID = ?                 		\n");
				sql.append("		AND MATERIAL_CD = ?                 		\n");
				
				pstmt = connection.prepareStatement(sql.toString());
				int paramIdx = 1;
				pstmt.setString(paramIdx++, sqlParams.get("partial_line"));
				pstmt.setString(paramIdx++, sqlParams.get("shipping_detail_id"));
				pstmt.setString(paramIdx++, sqlParams.get("shipping_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 void getPsrShippingNotiCnt(Connection connection, ValueObject params) throws Exception {
			Search search 	  = new SearchImpl();
			RowSet rowSet 	  = new RowSetImpl();
			StringBuffer sql  = new StringBuffer();
			int returnCnt = 0;
			
			try {
				sql.append("	SELECT                                                                       		\n");
				sql.append("		COUNT(*) CNT 	\n");
				sql.append("	FROM PSR_SHIPPING_NOTI	\n");
				sql.append("	WHERE SHIPPING_DETAIL_ID = ?	\n");
				sql.append("	AND SHIPPING_ID = ?		\n");
				sql.append("	AND MATERIAL_CD = ?	\n");
				search.addParameter(params.get("shipping_detail_id"));
				search.addParameter(params.get("shipping_id"));
				search.addParameter(params.get("material_cd"));
				
				WAFLogger.debug(sql.toString());
				search.setStatement(sql.toString());
				rowSet = search.execute();
				returnCnt = rowSet.getRow(0).getInt("CNT");
				if(returnCnt > 0) {
					String message = "부분선적정보가 존재합니다.";
					Exception e = new Exception(message);
					storage.setMessage(message);
	                super.storage.setSuccess(false);
	                throw e;
				}
			} catch (WAFSQLException se) {
				WAFLogger.error(this.getClass().getName() + " --> getShippingTarget1() : \n" + se.getMessage());
				WAFLogger.error("SQL : " + search.getStatement());
				WAFLogger.error(se);
				storage.setDetailMessage(se);
			}
		}
		
		public boolean confirmData(Connection conn, ValueObject params) throws Exception {
			try {
				CallableStatement cs 	= null;
        		String pssql			= "";
        		
            	pssql = "{call SP_REG_EPMIS_SHIPPING(?,?,?,?)}";
            	
				// 자재구분이 B인것만 플랜트 쪽으로 넘깁니다.
				{
					if(params.get("material_gb").equals("B")) {
						cs = conn.prepareCall(pssql);
		            	cs.setString(1, params.get("shipping_id"));
		            	cs.setString(2, "D");
		            	cs.setString(3, "");
		            	cs.setString(4, "");
		            	
		            	/*
		            	System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~");
		            	System.out.println("1 ==> " + params.get("shipping_id"));
		            	System.out.println("2 ==> " + "D");
		            	System.out.println("3 ==> " + "");
		            	System.out.println("4 ==> " + "");
		            	System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~");
		            	*/
		            	
		            	cs.execute();
		            	cs.clearParameters();

		            	cs.setString(1, params.get("shipping_id"));
		            	cs.setString(2, "C");
		            	cs.setString(3, "");
		            	cs.setString(4, "");
		            	
		            	/*
		            	
		            	System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~");
		            	System.out.println("1 ==> " + params.get("shipping_id"));
		            	System.out.println("2 ==> " + "C");
		            	System.out.println("3 ==> " + "");
		            	System.out.println("4 ==> " + "");
		            	System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~");
		            	
		            	*/
		            	cs.execute();
		            	cs.clearParameters();
					}
				}
				
            	confirmData2(conn, params);
            	
	            return true;
	        }
	        catch(Exception e) {
	        	super.doException(e);
				super.storage.setMessage(e.getMessage());
				super.storage.setDetailMessage(e);
				super.conn.setRollback();
				return false;
	        }
		}
		
		public boolean confirmData2(Connection connection, ValueObject params) throws Exception {
			   PreparedStatement pstmt = null;
			   StringBuffer sql   = new StringBuffer();
			   
			   try {
				   sql.append("	UPDATE PSR_SHIPPING SET	\n");
				   sql.append("		STATUS = '5'                 		\n");
				   sql.append("	WHERE SHIPPING_ID = ?            		\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);
					}
		        }
		    }
		
		private void sendSMS(ValueObject params) throws Exception {
			MessageSend messageSend = new MessageSend(storage);
			
			String vendor_name = params.get("vendor_name");
			String ship_name = params.get("item_description");
			
	     	RowSet rsUserInfo = getUserInfo(params.get("pur_emp_no"));
			
			String subject = WAFMessages.getMessage("SuppSsr.Complete.Mail.Title");
            String subject_in = WAFMessages.getMessage("SuppSsr.Complete.Mail.Title.in");
            
            StringBuffer mailContent = new StringBuffer();
            mailContent.append("<font style='width:auto; font-size:12px; line-height:18px; text-align:left;'>");
         	mailContent.append("Shipping 정보가 등록 되었습니다. <br>");
         	mailContent.append("업체명: "+ vendor_name + "<br>");
         	mailContent.append("선적명: "+ ship_name + "<br>");
         	mailContent.append("전자조달시스템의 SSR 확인 바랍니다.  <br>");
         	mailContent.append("<br>");
         	mailContent.append("</font>");
         	
        	if( rsUserInfo.size() > 0 && !rsUserInfo.getRow(0).get("e_mail").isEmpty() ){
        		messageSend.sendEmail(rsUserInfo.getRow(0).get("e_mail"), "test", subject, subject_in, mailContent, "Y");
        	}
        	//SMS
        	String smsContent = WAFMessages.getMessage("SuppSsr.Complete"); 
        	String re_phon_no = "024899000"; //이앤씨 번호
        	
        	
        	if( rsUserInfo.size() > 0 && !rsUserInfo.getRow(0).get("hand_tel_no").isEmpty()){
        		messageSend.sendSMS(rsUserInfo.getRow(0).get("hand_tel_no"), re_phon_no, params.get("pur_emp_no"), smsContent); //2015.9.21 SSR대표번호
        	}
		}
		 
		private RowSet getUserInfo(String acct_id) throws Exception{
	    	
	    	Search search = new SearchImpl();
	    	RowSet rowSet = new RowSetImpl();
	    	
	    	StringBuffer sqlstr = new StringBuffer();       
	    	
	    	try {
	    		sqlstr.append(" SELECT  ACCT_ID       \n");
	    		sqlstr.append("         ,USER_SABUN              \n");
	    		sqlstr.append("         ,USER_NAME             \n");
	    		sqlstr.append("         ,TEL_NO              \n");
	    		sqlstr.append("         ,HAND_TEL_NO              \n");
	    		sqlstr.append("         ,E_MAIL              \n");
	    		sqlstr.append(" FROM    PUB0120T             \n");
	    		sqlstr.append(" WHERE   ACCT_ID = "+acct_id+" \n");
	    		
	    		search.setStatement(sqlstr.toString());
	    		rowSet = search.execute();            
	    	}
	    	catch(WAFSQLException se) {
	    		Logger.err.println("SQL : \n" + se.getStatement());
	    		Logger.err.println("PARAM : \n" + se.getParameter());
	    		throw se;
	    	}
	    	finally {
	    		if( rowSet == null ) rowSet = new RowSetImpl();
	    		return rowSet;
	    	}
	    }
	
}

