/**
 * @(#)file    SuppSSRDetailBiz.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.Connection;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.sql.Row;
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.ebid.buyer.common.file.FileManager;

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

public class SuppSSRDetailBiz extends Biz {

	public SuppSSRDetailBiz(HandlerStorage storage) {
		super(storage);
	}
	
	public void doBiz() throws Exception {
		ValueObject vo_file = new ValueObject();
		
		if( storage.compareEvent("") ){
			ValueObject vo = new ValueObject();
	        storage.addValueObject("ssrDetail", vo);
		} 
		else if ( storage.compareEvent("GET_SUPP_SSR_DETAIL") ) {
			ValueObject vo = getSSRDetail(params);
	        storage.addValueObject("ssrDetail", vo);
	        
	        RowSet rowSet = FileManager.getFileList("SSR_DETAIL", params.get("shipping_id"));
	    	
	        String str="";
	        for(int i=0;i<rowSet.size();i++){
	        	Row row=rowSet.getRow(i);
	        	str += (i+1)+" : "+row.get("FILE_NAME")+" ("+ row.get("FILE_SIZE")+")";
	        	if(i+1!=rowSet.size()){
	        		str +="\r\n";
	        	}
	        }
	        
	        vo_file.set("SUPP_SSR_DETAIL", str);
	        
	        
		} 
		else if( storage.compareEvent("GET_SUPP_SSR_DETAIL_SHEET") ) {
			params.set("sheet1", getSsrDetailSheet(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		} 
		else if ( storage.compareEvent("REG_SUPP_SSR_DETAIL") ) {
			ValueObject voShippingId = getShippingId(super.conn.getConnection());
			params.set("shipping_id", voShippingId.get("shipping_id"));
			ValueObject vo = new ValueObject();
			storage.addValueObject("ssrDetail", vo);

		} 
		else if ( storage.compareEvent("GET_SUPP_SHIPPING_TARGET1") ) {
			params.set("sheet1", getShippingTarget1(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		} 
		else if ( storage.compareEvent("GET_SUPP_SHIPPING_TARGET2") ) {
			params.set("sheet2", getShippingTarget2(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		} 
		else if ( storage.compareEvent("GET_SUPP_PARTIAL_MATERIAL") ) {
			params.set("sheet1", getPartialMaterial(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
		
		storage.addValueObject("SUPP_SSR_DETAIL", vo_file);
	}

	
	private ValueObject getSSRDetail(ValueObject params) {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();
        ValueObject vo = new ValueObject();
        try {
        	sql.append(" SELECT               	\n");
        	sql.append("      SUBSTR(A.SHIP_NO,0,7)||'-'||SUBSTR(A.SHIP_NO,8,3) srn_no       	\n");
        	sql.append("     , a.ship_name item_description       	\n");
        	sql.append("     , a.bl_no bl_no       	\n");
        	sql.append("     , a.invoice_no invoice_no       	\n");
        	sql.append("     , CASE WHEN C.PAY_GROUP = '73' THEN 'T/T'	\n");
        	sql.append("     		WHEN C.PAY_GROUP LIKE '4%' THEN '기성'	\n");
        	sql.append("     		WHEN C.PAY_GROUP LIKE '7%' THEN 'L/C'		\n");
        	sql.append("     		ELSE C.PAY_GROUP	\n");
        	sql.append("     END PAYMENT	\n");
//        	sql.append("     , c.payment_terms payment       	\n");
        	sql.append("     , nvl(a.invoice_amt,0) invoice_value       	\n");
        	sql.append("     , c.currency_gb       	\n");
        	sql.append("     , f_get_code_name('PUR_LEAD_GB', c.lead_cond_gb) condition       	\n");
        	sql.append("     , a.ship_date fob_date       	\n");
        	sql.append("     , a.origin       	\n");
        	sql.append("     , a.clear_custom cc       	\n");
        	sql.append("     , a.package_qty pkg       	\n");
        	sql.append("     , a.pkg_cbm cbm       	\n");
        	sql.append("     , GET_CORP_NM_EBID(C.CORP_ID) vendor_name       	\n");
        	sql.append("     , a.pkg_wgt kgs       	\n");
        	sql.append("     , a.ship_mode shipping_mode       	\n");
        	sql.append("     , nvl(a.cntr_20dc,0) twenty_dc       	\n");
        	sql.append("     , nvl(a.cntr_20dt,0) twenty_dt       	\n");
        	sql.append("     , nvl(a.cntr_20fr,0) twenty_fr       	\n");
        	sql.append("     , nvl(a.cntr_40dc,0) fourty_dc       	\n");
        	sql.append("     , nvl(a.cntr_40dt,0) fourty_dt			\n");
        	sql.append("     , nvl(a.cntr_40fr,0) fourty_fr        	\n");
        	sql.append("     , nvl(a.cntr_45ft,0) fourty_fft        	\n");
        	sql.append("     , nvl(a.cntr_20dc,0) + nvl(a.cntr_20dt,0) + nvl(a.cntr_20fr,0) + nvl(a.cntr_40dc,0) + nvl(a.cntr_40dt,0) + nvl(a.cntr_40fr,0) + nvl(a.cntr_45ft,0)  total       	\n");
        	sql.append("     , a.pol pol       	\n");
        	sql.append("     , a.SHORE_YN AS SHORE_YN      	\n");
        	sql.append("     , a.esti_departure pol_etd       	\n");
        	sql.append("     , a.departure pol_atd       	\n");
        	sql.append("     , a.pod pod         	\n");
        	sql.append("     , a.esti_arrival pod_etd       	\n");
        	sql.append("     , a.arrival pod_atd         	\n");
        	sql.append("     , a.vessel_name vessel_name       	\n");
        	sql.append("     , a.etd_onsite onsite_plan_date       	\n");
        	sql.append("     , a.onsite onsite_arrival_date       	\n");
        	sql.append("     , a.remarks       	\n");
        	sql.append("     , a.ship_date       	\n");
        	sql.append("     , a.shipping_id       	\n");
        	sql.append("     , c.corp_id       	\n");
        	sql.append("     , a.dept_cd       	\n");
        	sql.append("     , GET_DEPT_NM(a.dept_cd) dept_nm       	\n");
        	sql.append("     , A.CONFIRM_DATE	\n");
        	sql.append("     , A.STATUS CONFIRM_YN	\n");
        	sql.append("     , A.DEPTEMPNO DEPT_EMP_NO	\n");
        	sql.append("     , GET_USER_NM(A.DEPTEMPNO) DEPT_EMP_NAME	\n");
        	sql.append("     , A.DEPTEMP_EMAIL AS DEPT_E_MAIL			\n");
        	sql.append("     , A.DEPTEMP_HP	AS DEPT_HAND_TEL_NO	\n");
        	sql.append("     , A.PUR_EMP_NO		\n");
        	sql.append("     , GET_USER_NM(A.PUR_EMP_NO) PUR_EMP_NAME	\n");
        	sql.append("     , A.PUREMP_EMAIL AS PUR_E_MAIL		\n");
        	sql.append("     , A.PUREMP_HP AS PUR_HAND_TEL_NO		\n");
        	sql.append(" FROM PSR_SHIPPING A						\n");
        	sql.append("    , (  SELECT SHIPPING_ID , PUR_CONT_ID	\n");
        	sql.append("         FROM  PSR_SHIPPING_DETAIL			\n");
        	sql.append("         GROUP BY SHIPPING_ID , PUR_CONT_ID	\n");
        	sql.append("      ) 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 = '"+params.get("shipping_id")+"'	\n");
        	sql.append(" AND B.PUR_CONT_ID = '"+params.get("pur_cont_id")+"'	\n");

        	search.setStatement(sql);
        	WAFLogger.debug(sql.toString());
            rowSet = search.execute();
            if(rowSet.size()>0){
                vo = RowSetUtility.getValueObject(rowSet.getRow(0));
            }
            return vo;
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally{
            if( vo == null) vo = new ValueObject();
            return vo;
        }
	}
	
	public RowSet getSsrDetailSheet(ValueObject params) {
		Search search 	  = new SearchImpl();
		RowSet rowSet 	  = new RowSetImpl();
		StringBuffer sql  = new StringBuffer();
		
		try {
			sql.append("	SELECT                                                             	\n");
			sql.append("		A.MATERIAL_CD                                                    	\n");
			sql.append("		, F_GET_MATERIAL_NAME(A.MATERIAL_CD) MATERIAL_NAME               	\n");
			sql.append("		, F_GET_MATL_SPEC_NAME(A.MATERIAL_CD) MATERIAL_SPEC              	\n");
			sql.append("		, F_GET_UNIT_KIND_NAME(A.MATERIAL_CD) ITEM_UNIT                		\n");
			sql.append("		, C.ORD_DOC_NO                                                   	\n");
			sql.append("		, C.CST_DOC_NAME                                                 	\n");
			sql.append("		, F_GET_PMM_SHIP_REMAIN_QTY(A.MATERIAL_CD, A.PUR_CONT_ID) JAN_QTY	\n");
			sql.append("		, NVL(B.ITEM_PRICE,0) ITEM_PRICE                                                   	\n");
			sql.append("		, NVL(B.ITEM_AMT,0) ITEM_AMT                                     	\n");
			sql.append("		, NVL(A.SHIP_QTY,0) SHIP_QTY                                     	\n");
			sql.append("		, A.SHIPPING_DETAIL_ID                                           	\n");
			sql.append("		, A.PUR_CONT_ID                                                  	\n");
			sql.append("		, A.SHIPPING_ID                                                  	\n");
			sql.append("		, C.CURRENCY_GB                                                  	\n");
			sql.append("		, NVL(E.SHIP_QTY2,0) + NVL(F_GET_PMM_SHIP_REMAIN_QTY(A.MATERIAL_CD, A.PUR_CONT_ID),0) SHIP_QTY2                                                 	\n");
			sql.append("		, NVL(B.ITEM_QTY,0) ITEM_QTY		                                                 	\n");
			sql.append("		, C.DEPT_NAME		                                                 	\n");
			sql.append("		, C.DEPT_CD		                                                 	\n");
			sql.append("		, F.MATERIAL_GB		                                                 	\n");
			sql.append("		, GET_CORP_NM_EBID(C.CORP_ID) AS VENDOR_NM		                                                 	\n");
			sql.append("		, CASE WHEN C.PAY_GROUP = '73' THEN 'T/T' 	                                                 	\n");
			sql.append("		 	   WHEN C.PAY_GROUP LIKE '4%' THEN '기성'     	                                                 	\n");
			sql.append("			   WHEN C.PAY_GROUP LIKE '7%' THEN 'L/C'	                                                 	\n");
			sql.append("		       ELSE C.PAY_GROUP 		                                                 	\n");
			sql.append("		  END PAYMENT_NM		                                                 	\n");
			sql.append("		, F_GET_CODE_NAME('PUR_LEAD_GB', C.LEAD_COND_GB)       	AS CONDITION_NM	  		                                                 	\n");
			sql.append("		, NVL(A.SHIP_QTY, 0) * NVL(B.ITEM_PRICE, 0) SHIP_AMT	\n");
			sql.append("	FROM PSR_SHIPPING_DETAIL A, CONT_PUR_ITEM B, PUR2000T C,           	\n");
			sql.append("	(                                                                  	\n");
			sql.append("		SELECT                                                           	\n");
			sql.append("			PUR_CONT_ID                                                    	\n");
			sql.append("			,MAX(PUR_CONT_MOD_ID) PUR_CONT_MOD_ID                          	\n");
			sql.append("		FROM PUR2000T                                                    	\n");
			sql.append("		WHERE CON_CLS_YN='9'                                                    	\n");
			sql.append("		GROUP BY PUR_CONT_ID                                             	\n");
			sql.append("	) D,                                                               	\n");
			sql.append("	(                                                                  	\n");
			sql.append("		SELECT                                                           	\n");
			sql.append("			MAX(A.SHIP_QTY) SHIP_QTY2                                  	\n");
			sql.append("			, A.PUR_CONT_ID                                                	\n");
			sql.append("			, A.MATERIAL_CD                                                	\n");
			sql.append("		FROM PSR_SHIPPING_DETAIL A, PSR_SHIPPING B                       	\n");
			sql.append("		WHERE A.SHIPPING_ID = B.SHIPPING_ID                              	\n");
			sql.append("		GROUP BY A.PUR_CONT_ID, A.MATERIAL_CD                            	\n");
			sql.append("	)E,                                                                 	\n");
//			sql.append("	(                                                               		\n");
//			sql.append("		SELECT A.MATERIAL_GB, B.MATERIAL_CD									\n");
//			sql.append("		FROM PMM_MATERIALGROUP A, PMM_COMM_MATERIAL_MASTER B				\n");
//			sql.append("		WHERE A.MATERIALGROUP = B.KIND_BIG									\n");
//			sql.append("	)F                                                               		\n");

			sql.append("	(	SELECT A.MATERIAL_GB, B.MATERIAL_CD									\n");
			sql.append("		FROM PMM_MATERIALGROUP A, PMM_COMM_MATERIAL_MASTER B				\n");
			sql.append("		WHERE A.MATERIALGROUP = B.KIND_BIG									\n");
			sql.append("		 AND decode(substr(B.material_cd, 0, 1), 'S', A.MATERIALLEVEL, '1') = decode(substr(B.material_cd, 0, 1), 'S', CASE WHEN LENGTH(MATERIAL_CD) <= 13 THEN 1 ELSE 2 END   , '1')						\n");
			sql.append("	)F                                                               		\n");

			sql.append("	WHERE A.PUR_CONT_ID = B.PUR_CONT_ID                                	\n");
			sql.append("	AND A.MATERIAL_CD = B.MATERIAL_CD                                  	\n");
			sql.append("	AND A.PUR_CONT_ID = C.PUR_CONT_ID                                  	\n");
			sql.append("	AND B.PUR_CONT_ID = C.PUR_CONT_ID                                  	\n");
			sql.append("	AND B.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                          	\n");
			sql.append("	AND C.PUR_CONT_ID = D.PUR_CONT_ID                          	\n");
			sql.append("	AND C.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID                          	\n");
			sql.append("	AND A.PUR_CONT_ID = E.PUR_CONT_ID(+)                               	\n");
			sql.append("	AND A.MATERIAL_CD = E.MATERIAL_CD(+)                               	\n");
			sql.append("	AND A.MATERIAL_CD = F.MATERIAL_CD                               	\n");
			sql.append("	AND A.SHIPPING_ID = ?                                              	\n");
			search.addParameter(params.get("shipping_id"));
			sql.append("	AND C.CORP_ID = ?                                              	\n");
			search.addParameter(params.get("vendor_cd"));
			sql.append("	ORDER BY A.MATERIAL_CD                                             	\n");
			
			
			WAFLogger.debug(sql.toString());
			search.setStatement(sql.toString());
			rowSet = search.execute();
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName() + " --> getSsrDetailSheet() : \n" + se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		}
		finally {    
			if(rowSet == null) rowSet = new RowSetImpl();
			return rowSet;
		}
	}
	
	public RowSet getShippingTarget1(ValueObject params) {
		Search search 	  = new SearchImpl();
		RowSet rowSet 	  = new RowSetImpl();
		StringBuffer sql  = new StringBuffer();
		
		try {
			
			
			sql.append("	        SELECT A.PUR_CONT_ID                                                                                    \n");
			sql.append("	             , A.ORD_DOC_NO                                                                                     \n");
			sql.append("	             , A.CST_DOC_NAME                                                                                   \n");
			sql.append("	             , A.OVERSEA_GB                                                                                     \n");
			sql.append("	             , GET_CORP_NM_EBID(A.CORP_ID) VENDOR_NAME                                                               \n");
			sql.append("	             , A.CORP_ID                                                                                        \n");
			sql.append("	             , A.DEPT_NAME                                                                                      \n");
			sql.append("	             , A.DEPT_CD                                                                                        \n");
			sql.append("	        FROM PUR1000T A                                                                                         \n");
			sql.append("	           , ( SELECT  X.PUR_CONT_ID                                                                            \n");
			sql.append("	               FROM  (                                                                                          \n");
			sql.append("	                        SELECT MATERIAL_CD                                                                      \n");
			sql.append("	                             , PUR_CONT_ID                                                                      \n");
			sql.append("	                             , PUR_CONT_MOD_ID                                                                  \n");
			sql.append("	                             , F_GET_PMM_SHIP_REMAIN_QTY(MATERIAL_CD, PUR_CONT_ID) AS REMAIN_QTY                \n");
			sql.append("	                         FROM CONT_PUR_ITEM                                                                     \n");
			sql.append("	                      ) X                                                                                       \n");
			sql.append("	                WHERE  X.REMAIN_QTY > 0                                                                         \n");
			sql.append("	                GROUP BY X.PUR_CONT_ID                                                                          \n");
			sql.append("	              ) B                                                                                               \n");
			sql.append("	         WHERE A.PUR_CONT_ID = B.PUR_CONT_ID                                                                    \n");
			sql.append("	         AND A.CORP_ID = ?                                                                                		\n");
			search.addParameter(params.get("corp_id"));
			sql.append("	         ORDER BY PUR_CONT_ID    																				\n");
			
			
			/*
			sql.append("	SELECT                                                                       		\n");
			sql.append("		A.PUR_CONT_ID                                                              		\n");
			sql.append("		, B.ORD_DOC_NO                                                             		\n");
			sql.append("		, B.CST_DOC_NAME                                                           		\n");
			sql.append("		, B.OVERSEA_GB                                                             		\n");
			sql.append("		, GET_CORP_NM(B.CORP_ID) VENDOR_NAME                                       		\n");
			sql.append("		, B.CORP_ID                                                                		\n");
			sql.append("		, B.DEPT_NAME                                                                		\n");
			sql.append("		, B.DEPT_CD                                                                		\n");
			sql.append("	FROM CONT_PUR_ITEM A, PUR2000T B,                                            		\n");
			sql.append("	(                                                                            		\n");
			sql.append("		SELECT                                                                     		\n");
			sql.append("			PUR_CONT_ID                                                              		\n");
			sql.append("			,MAX(PUR_CONT_MOD_ID) PUR_CONT_MOD_ID                                    		\n");
			sql.append("		FROM PUR2000T                                                              		\n");
			sql.append("		WHERE CON_CLS_YN='9'                                                              		\n");
			sql.append("		GROUP BY PUR_CONT_ID                                                       		\n");
			sql.append("	) C                                                                          		\n");
			sql.append("	, (                                                                          		\n");
			sql.append("		SELECT                                                                     		\n");
			sql.append("			MATERIAL_CD, PUR_CONT_ID                                                 		\n");
			sql.append("			, F_GET_PMM_SHIP_REMAIN_QTY(MATERIAL_CD, PUR_CONT_ID) REMAIN_QTY         		\n");
			sql.append("		FROM CONT_PUR_ITEM                                                         		\n");
			sql.append("	) D                                                                          		\n");
			sql.append("	WHERE A.PUR_CONT_ID = B.PUR_CONT_ID                                          		\n");
			sql.append("	AND B.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                                    		\n");
			sql.append("	AND A.PUR_CONT_ID = D.PUR_CONT_ID                                            		\n");
			sql.append("	AND D.REMAIN_QTY > 0                                                         		\n");
//			sql.append("	AND B.CORP_ID = '15716'	\n");
			sql.append("	AND B.CORP_ID = ?	\n");
			search.addParameter(params.get("corp_id"));

			sql.append("		GROUP BY A.PUR_CONT_ID, B.ORD_DOC_NO, B.CST_DOC_NAME, B.OVERSEA_GB, B.CORP_ID, B.DEPT_NAME, B.DEPT_CD       	\n");
			sql.append("		ORDER BY A.PUR_CONT_ID       	\n");
			*/
			WAFLogger.debug(sql.toString());
			search.setStatement(sql.toString());
			rowSet = search.execute();
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName() + " --> getShippingTarget1() : \n" + se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		}
		finally {    
			if(rowSet == null) rowSet = new RowSetImpl();
			return rowSet;
		}
	}
	
	public RowSet getShippingTarget2(ValueObject params) {
		Search search 	  = new SearchImpl();
		RowSet rowSet 	  = new RowSetImpl();
		StringBuffer sql  = new StringBuffer();
		
		try {
			sql.append("	SELECT                                                           		\n");
			sql.append("		A.MATERIAL_CD                                                  		\n");
			sql.append("		, F_GET_MATERIAL_NAME(A.MATERIAL_CD) MATERIAL_NAME             		\n");
			sql.append("		, F_GET_MATL_SPEC_NAME(A.MATERIAL_CD) MATERIAL_SPEC            		\n");
			sql.append("		, F_GET_UNIT_KIND_NAME(A.MATERIAL_CD) ITEM_UNIT                		\n");
			sql.append("		,  D.JAN_QTY                                                   		\n");
			sql.append("		, NVL(A.ITEM_QTY,0) ITEM_QTY                                   		\n");
			sql.append("		, A.ITEM_PRICE                                                 		\n");
			sql.append("		, NVL(D.JAN_QTY,0) * NVL(A.ITEM_PRICE,0) ITEM_AMT                                                   		\n");
			sql.append("		, B.PUR_CONT_ID                                                		\n");
			sql.append("		, B.ORD_DOC_NO                                                 		\n");
			sql.append("		, B.CST_DOC_NAME                                               		\n");
			sql.append("		, B.CURRENCY_GB                                                		\n");
			sql.append("		, B.DEPT_NAME                                                		\n");
			sql.append("		, B.DEPT_CD                                                		\n");
			sql.append("		, E.MATERIAL_GB                                                		\n");
			sql.append("	FROM CONT_PUR_ITEM A, PUR2000T B,                                		\n");
			sql.append("	(                                                                		\n");
			sql.append("		SELECT                                                         		\n");
			sql.append("			PUR_CONT_ID                                                  		\n");
			sql.append("			,MAX(PUR_CONT_MOD_ID) PUR_CONT_MOD_ID                        		\n");
			sql.append("		FROM PUR2000T                                                  		\n");
			sql.append("		WHERE DEPT_CD = ?		\n");
			search.addParameter(params.get("dept_cd"));
			sql.append("		AND PUR_CONT_ID = ?		\n");
			search.addParameter(params.get("pur_cont_id"));
			sql.append("		AND CON_CLS_YN='9'                                                  		\n");
			sql.append("		GROUP BY PUR_CONT_ID                                           		\n");
			sql.append("	) C,                                                             		\n");
			sql.append("	(                                                                		\n");
			sql.append("		SELECT                                                         		\n");
			sql.append("			MATERIAL_CD, PUR_CONT_ID, PUR_CONT_MOD_ID                                     		\n");
			sql.append("			, F_GET_PMM_SHIP_REMAIN_QTY(MATERIAL_CD, PUR_CONT_ID) JAN_QTY		\n");
			sql.append("		FROM CONT_PUR_ITEM                                             		\n");
			sql.append("		WHERE PUR_CONT_ID = ?                                             		\n");
			search.addParameter(params.get("pur_cont_id"));
			sql.append("	)D,                                                               		\n");
//			sql.append("	(                                                               		\n");
//			sql.append("		SELECT A.MATERIAL_GB, B.MATERIAL_CD									\n");
//			sql.append("		FROM PMM_MATERIALGROUP A, PMM_COMM_MATERIAL_MASTER B				\n");
//			sql.append("		WHERE A.MATERIALGROUP = B.KIND_BIG									\n");
//			sql.append("	)E                                                               		\n");
			
			sql.append("	(                                                               		\n");
			sql.append("		SELECT A.MATERIAL_GB, B.MATERIAL_CD									\n");
			sql.append("		FROM PMM_MATERIALGROUP A, PMM_COMM_MATERIAL_MASTER B				\n");
			sql.append("		WHERE A.MATERIALGROUP = B.KIND_BIG									\n");
			sql.append("		 AND decode(substr(B.material_cd, 0, 1), 'S', A.MATERIALLEVEL, '1') = decode(substr(b.material_cd, 0, 1), 'S', CASE WHEN LENGTH(b.MATERIAL_CD) <= 13 THEN 1 ELSE 2 END   , '1')       									\n");
			sql.append("	)E                                                               		\n");
			
			sql.append("	WHERE A.PUR_CONT_ID = B.PUR_CONT_ID                              		\n");
			sql.append("	AND A.PUR_CONT_MOD_ID = B.PUR_CONT_MOD_ID		\n");
			sql.append("	AND A.PUR_CONT_ID = C.PUR_CONT_ID		\n");
			sql.append("	AND A.PUR_CONT_MOD_ID = C.PUR_CONT_MOD_ID                        		\n");
			sql.append("	AND A.PUR_CONT_ID = D.PUR_CONT_ID                                		\n");
			sql.append("	AND A.PUR_CONT_MOD_ID = D.PUR_CONT_MOD_ID                                		\n");
			sql.append("	AND A.MATERIAL_CD = D.MATERIAL_CD                                		\n");
			sql.append("	AND A.MATERIAL_CD = E.MATERIAL_CD                                		\n");
			sql.append("	AND D.JAN_QTY > 0                                                		\n");
			sql.append("	AND DEPT_CD = ?                                                  		\n");
			search.addParameter(params.get("dept_cd"));
			sql.append("	AND A.PUR_CONT_ID = ?                                            		\n");
			search.addParameter(params.get("pur_cont_id"));
			sql.append("	ORDER BY MATERIAL_CD                                             		\n");
			
			WAFLogger.debug(sql.toString());
			search.setStatement(sql.toString());
			rowSet = search.execute();
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName() + " --> getShippingTarget2() : \n" + se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		}
		finally {    
			if(rowSet == null) rowSet = new RowSetImpl();
			return rowSet;
		}
	}
	
	public RowSet getPartialMaterial(ValueObject params) {
		Search search 	  = new SearchImpl();
		RowSet rowSet 	  = new RowSetImpl();
		StringBuffer sql  = new StringBuffer();
		
		try {
			sql.append("		SELECT                                             		\n");
			sql.append("			A.UPD_BY                                         		\n");
			sql.append("			, GET_CORP_NM(A.UPD_BY) UPD_BY2                                         		\n");
			sql.append("			, A.UPD_DATE                                       		\n");
			sql.append("			, B.SHIP_NO                             		\n");
			sql.append("			, A.PARTIAL_NAME                                   		\n");
			sql.append("			, NVL(A.PARTIAL_QTY, 0) PARTIAL_QTY                                    		\n");
			sql.append("			, A.PARTIAL_TEXT                                   		\n");
			sql.append("			, A.PARTIAL_LINE                                   		\n");
			sql.append("			, A.SHIPPING_ID                                   		\n");
			sql.append("			, A.MATERIAL_CD                                   		\n");
			sql.append("			, A.SHIPPING_DETAIL_ID                                   		\n");
			sql.append("			, B.STATUS S_STATUS                                   		\n");
			sql.append("		FROM PSR_SHIPPING_NOTI A, PSR_SHIPPING B                             		\n");
			sql.append("		WHERE A.SHIPPING_ID = B.SHIPPING_ID	\n");
			sql.append("		AND A.PUR_CONT_ID = '"+params.get("pur_cont_id")+"'		\n");
			sql.append("		AND A.MATERIAL_CD = '"+params.get("material_cd")+"'  		\n");
			sql.append("		ORDER BY B.SHIP_NO DESC	\n");
			
			WAFLogger.debug(sql.toString());
			search.setStatement(sql.toString());
			rowSet = search.execute();
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName() + " --> getPartialMaterial : \n" + se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		}
		finally {    
			if(rowSet == null) rowSet = new RowSetImpl();
			return rowSet;
		}
	}
	
	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;
	}
	
}
