/**
 * @(#)file    PlanStockListBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.08.05
 * @(#)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 kr.co.hsnc.common.util.ValueObject;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
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 PlanStockListBiz extends Biz {

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

	public void doBiz() throws Exception {
		if (storage.getEvent().equals("")) {
			String stDate = "";
			
			stDate = DateUtil.formatDateTime(DateUtil.getToday(), "yyyyMMdd", "yyyyMM") + "01";
			stDate = Formatter2.formatDate(stDate, "yyyymmdd", "yyyy-mm-dd");

			String endDate = DateUtil.getLastDate(stDate.replaceAll("-", ""),
					"yyyy-mm-dd");

			if (params.get("sYmdSt").equals("")) {
				params.set("sYmdSt", stDate);
			} else {
				params.set("sYmdSt", params.get("sYmdSt")); // 시작일
			}

			if (params.get("sYmdEd").equals("")) {
				params.set("sYmdEd", endDate);
			} else {
				params.set("sYmdEd", params.get("sYmdEd")); // 종료일
			}
		} else {
			params.set("sheet1", getPlanStockList(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}
	}


	/**
	 * PlanStockList 가져오기
	 * 
	 * @author 김예진
	 * @since 2014.05.19
	 * @return
	 * @throws Exception
	 */
	public RowSet getPlanStockList(ValueObject params) {
		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		StringBuffer sql = new StringBuffer();

		try {
			WAFLogger.debug("getPlanStockList[계획대비 입고조회] Start");
			//2018.11.27 계회입고 미사용 
			sql.append("    SELECT                                                                                                                                 \n");
			sql.append("    	        GET_DEPT_NM(A.DEPT_CD) AS DEPT_NM   --현장                                                                                 \n");
			sql.append("    	      , A.MATERIAL_CD  --자재코드                                                                                                  \n");
			sql.append("    	      , B.MATERIAL_NAME  --자재명                                                                \n");
			sql.append("    	      , F_GET_MATL_SPEC_NAME(A.MATERIAL_CD) AS MATERIAL_SPEC  --규격                                                               \n");
			sql.append("    	      , F_GET_UNIT_KIND_NAME(A.MATERIAL_CD) AS UNIT_NM  --단위                                                                     \n");
			sql.append("    	      , A.WKDATE   --일자                                                                                                          \n");
			sql.append("    	      , SUM(A.PLAN_QTY) AS PLAN_QTY    --계획수량                                                                                  \n");
			sql.append("    	      , SUM(A.STOCK_QTY) AS STOCK_QTY  --입고수량                                                                                  \n");
			sql.append("    	      , SUM(A.PLAN_QTY) - SUM(A.STOCK_QTY) AS CONTRAST_PLAN_STOC                                                                   \n");
			sql.append("    	      ,A.DEPT_CD --현장코드                                                                                                        \n");
			sql.append("    FROM                                                                                                                                   \n");
			sql.append("           (                                                                                                                               \n");
			sql.append("             SELECT  BB.DEPT_CD DEPT_CD,AA.MATERIAL_CD MATERIAL_CD,AA.SCHEDULEDATE WKDATE, SUM(AA.SCHEDULE_QTY) PLAN_QTY, SUM(0) STOCK_QTY \n");
			sql.append("             FROM    PMM_STOCKSCHEDULEITEM@CCSIF AA, PMM_STOCKSCHEDULE@CCSIF BB     --2018.11.27 계회입고 미사용                                                              \n");
			sql.append("             WHERE   AA.SCHEDULENO = BB.SCHEDULENO                                                                                         \n");
			sql.append("             GROUP BY BB.DEPT_CD, AA.MATERIAL_CD, AA.SCHEDULEDATE                                                                          \n");
			sql.append("             UNION ALL                                                                                                                     \n");
			sql.append("             SELECT  BB.DEPT_CD DEPT_CD,AA.MATERIAL_CD MATERIAL_CD,BB.STOCK_DATE WKDATE, SUM(0) PLAN_QTY, SUM(AA.STOCK_QTY) STOCK_QTY      \n");
			sql.append("             FROM    ETEC_ERPIF.PMM_STOCK_ITEM AA, ETEC_ERPIF.PMM_STOCK BB     \n");//2018.11.27 계회입고 미사용 
			sql.append("             WHERE   AA.STOCK_ID = BB.STOCK_ID                                                                                             \n");
			sql.append("             GROUP BY BB.DEPT_CD, AA.MATERIAL_CD, BB.STOCK_DATE                                                                            \n");
			sql.append("           ) A ,                                                                                                                            \n");
			sql.append("           ( SELECT MATERIAL_CD, MATERIAL_NAME FROM PMM_COMM_MATERIAL_MASTER@CCSIF A, PMM_MATERIALGROUP@CCSIF B                                        \n");
			sql.append("           		WHERE  A.KIND_SMALL = B.MATERIALGROUP AND MATERIAL_GB = 'C'  ) B                                                           \n");
			sql.append("    WHERE A.MATERIAL_CD = B.MATERIAL_CD                                                                                                    \n");
			if (!"".equals(params.get("dept_cd"))) {
				sql.append("		AND     A.DEPT_CD = ?                                                              						\n");
    			search.addParameter(params.get("dept_cd"));
			}
			if (!"".equals(params.get("sYmdSt")) && !"".equals(params.get("sYmdEd"))) {
				sql.append("    AND A.WKDATE BETWEEN '"+ params.get("sYmdSt").replace("-", "")+ "' "
									+ "AND '"+ params.get("sYmdEd").replace("-", "")+ "' \n");
			}
			if (!"".equals(params.get("material_cd").trim())) {
				sql.append("  AND A.MATERIAL_CD LIKE '"+ params.get("material_cd").trim()+ "%'	\n");
			}
			if (!"".equals(params.get("material_nm").trim())) {
				sql.append("  AND F_GET_MATERIAL_NAME(A.MATERIAL_CD) LIKE '%"+ params.get("material_nm").trim()+ "%'	\n");
			}
			sql.append(" GROUP BY A.DEPT_CD, A.MATERIAL_CD, A.WKDATE, B.MATERIAL_NAME       \n");
			sql.append(" ORDER BY A.DEPT_CD, A.MATERIAL_CD, A.WKDATE       \n");
			
			search.setStatement(sql.toString());

			rowSet = search.execute();

			WAFLogger.debug("getPlanStockList[계획대비 입고조회] End");
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName()
					+ " --> getUserName() : \n" + se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		} finally {
			if (rowSet == null)
				rowSet = new RowSetImpl();
			return rowSet;
		}
	}
}

