/**
 * @(#)file    StockSchedulePurchaseListBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.7.16
 * @(#)Description: 현장입고계획 관리
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */
package kr.co.udapsoft.ebid.buyer.warehouse.purchase.biz;

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 com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.waf.common.util.Formatter2;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

public class StockSchedulePurchaseListBiz extends Biz {

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

	public void doBiz() throws Exception {
		
		if(storage.getEvent().equals("")){
			String yyyymm =  storage.getUser().get("YYYYMM");
			yyyymm = Formatter2.formatDate(yyyymm , "yyyymm", "yyyy-mm");

        	if( params.get("p_schedule_date").equals("") ){
        		params.set("p_schedule_date", yyyymm);
        	} else {
        		params.set("p_schedule_date",params.get("p_schedule_date"));			// 조회월                        
        	}
		}else if(storage.getEvent().equals("GET_SCHEDULE_LIST")){
			params.set("sheet1", getPURScheduleList());
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
	}

	/**
	 * 대금지급 신청목록을 검색하여 리턴
	 * @param Params
	 * @return
	 */
	private RowSet getPURScheduleList() throws Exception {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sql = new StringBuffer();
        //2018.11.27 입고계획관리 미사용 
       
        try {
        	sql.append("			SELECT                                   						\n");
        	sql.append("			       A.SCHEDULENO SCHEDULENO2,                                  						\n");
        	sql.append("			       A.SCHEDULENO,                                  						\n");
        	sql.append("			       A.DEPT_CD,                               							\n");
        	sql.append("			       GET_DEPT_NM(A.DEPT_CD) AS DEPT_NM,          							\n");
        	sql.append("			       C.ORD_DOC_NO,                             							\n");
        	sql.append("			       C.CST_DOC_NAME,                             							\n");
        	sql.append("			       GET_CORP_NM(C.CORP_ID) AS CORP_NM,       							\n");
        	sql.append("			       B.MATERIAL_CD,                              							\n");
        	sql.append("			       E.MATERIAL_NAME,                            							\n");
        	sql.append("				   F_GET_MATL_SPEC_NAME(B.MATERIAL_CD) AS  MATL_SPEC,                   \n");
        	sql.append("				   F_GET_CODE_NAME('UNIT_KIND_GB', E.UNIT_KIND) AS  UNIT_KIND,                   \n");
        	sql.append("			       B.SCHEDULEDATE,                             							\n");
        	sql.append("			       B.SCHEDULE_QTY,                             							\n");
        	sql.append("			       D.ITEM_QTY,                                 							\n");
        	sql.append("			       NVL(F.STOCK_QTY ,0) AS TEMP_QTY,       			        			\n");
        	sql.append("			       NVL(D.ITEM_QTY ,0) - NVL(F.STOCK_QTY, 0) AS RMNR_QTY,	            \n");
        	sql.append("			       DECODE(A.CONFIRM_YN,'Y','완료','미완료') AS CONFIRM_YN		        \n");
        	sql.append("			FROM   PMM_STOCKSCHEDULE@CCSIF  A, --2018.11.27 입고계획관리 미사용                       							\n");
        	sql.append("			       PMM_STOCKSCHEDULEITEM@CCSIF B,   --2018.11.27 입고계획관리 미사용                  							\n");
        	sql.append("			       PUR1000T C,                                 							\n");
        	sql.append("			       (                                 							\n");
        	sql.append("			       	SELECT                                  							\n");
        	sql.append("			       		PUR_CONT_ID, MATERIAL_CD, ITEM_QTY                                 							\n");
        	sql.append("			       	FROM CONT_PUR_ITEM                                 							\n");
        	sql.append("			       	WHERE PUR_CONT_MOD_ID IN (                                 							\n");
        	sql.append("			       								SELECT MAX(PUR_CONT_MOD_ID) FROM PUR2000T WHERE CON_CLS_YN='9' GROUP BY PUR_CONT_ID                                 							\n");
        	sql.append("			       	)                                 							\n");
        	sql.append("			       	ORDER BY PUR_CONT_ID, MATERIAL_CD                                 							\n");
        	sql.append("			       )D,                                 							\n");
        	sql.append("			       PMM_COMM_MATERIAL_MASTER@CCSIF E,                  						\n");
        	sql.append("			       ( SELECT SUM(STOCK_QTY) AS STOCK_QTY,                  				\n");
        	sql.append("			       			PUR_CONT_ID, DEMANDED_ID, MATERIAL_CD                  		\n");
        	sql.append("			       	 FROM    ETEC_ERPIF.PMM_STOCK_ITEM \n"); //2018.11.27 입고계획관리 미사용                 							
        	sql.append("			        GROUP BY PUR_CONT_ID, DEMANDED_ID, MATERIAL_CD) F                  	\n");
        	sql.append("			WHERE  A.SCHEDULENO = B.SCHEDULENO               							\n");
        	sql.append("      		AND    B.PUR_CONT_ID = C.PUR_CONT_ID               							\n");
        	sql.append("      		AND    C.PUR_CONT_ID = D.PUR_CONT_ID         			 					\n");
        	sql.append("      		AND    B.MATERIAL_CD = D.MATERIAL_CD               							\n");
        	sql.append("      		AND    B.MATERIAL_CD  = E.MATERIAL_CD              							\n");
        	sql.append("      		AND    B.PUR_CONT_ID  = F.PUR_CONT_ID(+)              						\n");
        	sql.append("      		AND    B.DEMANDED_ID  = F.DEMANDED_ID(+)              						\n");
        	sql.append("      		AND    B.MATERIAL_CD  = F.MATERIAL_CD(+)              						\n");
        	sql.append("      		AND    B.MATERIAL_CD  = F.MATERIAL_CD(+)              						\n");
        	sql.append("      		AND    A.OVERSEA_GB= 'D'			            							\n");
        	
        	if(!params.get("p_schedule_date").equals("")){
        		sql.append("	  		AND    A.YYYYMM = '"+params.get("p_schedule_date").replaceAll("-", "")+"'	\n");
        	}
        	
        	if(!params.get("dept_cd").equals("")){
        		sql.append("		AND     A.DEPT_CD = ?                                                              						\n");
    			search.addParameter(params.get("dept_cd"));
        	}
        	if(!params.get("p_confirm_yn").equals("")){
        		sql.append("	  		AND    A.CONFIRM_YN = '"+params.get("p_confirm_yn")+"'						\n");
        	}
        	sql.append("	  		ORDER BY A.SCHEDULENO , A.DEPT_CD, C.ORD_DOC_NO, B.MATERIAL_CD, B.SCHEDULEDATE	\n");

        	WAFLogger.debug(sql.toString());
        	search.setStatement(sql.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
        }
        finally {    
        	 if(rowSet == null){
             	rowSet = new RowSetImpl();
             }
        }
        
        
        return rowSet;
	}

	
}
