/**
 * @(#)file    SuppSSRListBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.08.20
 * @(#)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 kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.util.ValueObject;

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

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

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

			try {
				super.conn.begin();
				
				for (int i = 0; i < status.length; i++) {
				    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("GET_SUPP_SSR_LIST") ) { 
			 params.set("sheet1", getSsrList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
    	
    }
    
    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("    , 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(" 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("                     ,ACZ10100V C                                                                                        \n");
            sqlstr.append("                WHERE A.SHIPPING_ID = B.SHIPPING_ID                                                                      \n");
            sqlstr.append("                  AND B.DEPT_CD = C.DEPT_CD                                                                              \n");
            sqlstr.append("                  AND UPPER(C.DEPT_NM) LIKE '%' || UPPER(?) || '%'                                                       \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("                     ,ACZ10100V C                                                                                        \n");
            sqlstr.append("                WHERE A.SHIPPING_ID = B.SHIPPING_ID                                                                      \n");
            sqlstr.append("                  AND B.DEPT_CD = C.DEPT_CD                                                                              \n");
            sqlstr.append("                  AND UPPER(C.DEPT_NM) LIKE '%' || UPPER(?) || '%'  ) 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("                     ,ACZ10100V C                                                                                        \n");
            sqlstr.append("                WHERE A.SHIPPING_ID = B.SHIPPING_ID                                                                      \n");
            sqlstr.append("                  AND B.DEPT_CD = C.DEPT_CD                                                                              \n");
            sqlstr.append("                  AND UPPER(C.DEPT_NM) LIKE '%' || UPPER(?) || '%'  ) 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("                     ,ACZ10100V C                                                                                        \n");
            sqlstr.append("                WHERE A.SHIPPING_ID = B.SHIPPING_ID                                                                      \n");
            sqlstr.append("                  AND B.DEPT_CD = C.DEPT_CD                                                                              \n");
            sqlstr.append("                  AND UPPER(C.DEPT_NM) LIKE '%' || UPPER(?) || '%'                                                       \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("    ,ACZ10100V D                                                                                                         \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 A.DEPT_CD = D.DEPT_CD                                                                                              \n");
            sqlstr.append("  AND UPPER(D.DEPT_NM) LIKE '%' || UPPER(?) || '%'                                                                       \n");
            sqlstr.append("  AND A.VENDOR_CD = ?                                                                                                    \n");
            
            search.addParameter(params.get("dept_nm"));
    		search.addParameter(params.get("dept_nm"));
    		search.addParameter(params.get("dept_nm"));
    		search.addParameter(params.get("dept_nm"));
    		search.addParameter(params.get("dept_nm"));
    		search.addParameter(storage.getUser().get("CORP_ID"));
    		
    		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(A.ORIGIN) 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"));
        	}        	
        	sqlstr.append("	     ORDER BY  D.DEPT_NM, 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 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"));        //ESTI_DEPARTURE
			pstmt.setString(paramIdx++, sqlParams.get("ARRIVAL"));          //ESTI_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);
			}
		}
    }
 }



