/**
 * @(#)file    SuppStockListBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.7.31
 * @(#)Description: 협력업체 입고 현황
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */
package kr.co.udapsoft.ebid.supplier.portal.warehouse.biz;

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


public class SuppStockListBiz extends Biz {

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

	public void doBiz() throws Exception {
		if (storage.compareEvent("")) {
			RowSet stockGbRowSet = getStockGbRowSet();
			storage.addRowSet("stockGbRowSet", stockGbRowSet);

			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", getStock(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		}

	}

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

		try {
			WAFLogger.debug("getStock[협력업체 입고현황] Start");
				
			
			sql.append("	SELECT                                                                                                                                         			\n");
			sql.append("	                D.DEPT_NM                                                                                                                      			\n");
			sql.append("	               ,DECODE('01', '01', GET_CORP_NM(C.CORP_ID), '02', D.DEPT_NM, '03', D.DEPT_NM) VENDOR_NAME                             			\n");
			sql.append("	               ,DECODE(C.PAY_ITEM, '', '', C.PAY_ITEM || '-' || C.COST_CODE || '-' || C.COST_TYPE) AS PAY_ITEM                                                             			\n");
			sql.append("	               ,F_GET_COST_NAME(C.DEPT_CD, C.PAY_ITEM, C.COST_CODE, C.COST_TYPE) AS PAY_ITEM_DESC                                              			\n");
			sql.append("	               , DECODE('01', '01', C.CST_DOC_NAME, '02', '전입입고', '03', '대내입고') CST_DOC_NAME                                   			\n");
			sql.append("	               ,B.MATERIAL_CD                                                                                                                  			\n");
			sql.append("	               ,E.MATERIAL_NAME                                                                                                                			\n");
			sql.append("	               ,E.MATERIAL_SPEC                                                                                                                			\n");
			sql.append("	               ,F_GET_UNIT_KIND_NAME(B.MATERIAL_CD) UNIT_KIND_NAME                                                                             			\n");
			sql.append("	               ,B.STOCK_DATE                                                                                                                   			\n");
			sql.append("	               ,B.ITEM_QTY STOCK_QTY                                                                                                                    			\n");
			sql.append("	               ,'' REMARKS                                                                                                                      			\n");
			sql.append("	               ,'' STOCK_ID                                                                                                                     			\n");
			sql.append("	               ,C.DEPT_CD                                                                                                                      			\n");
			sql.append("	               ,TO_CHAR(C.CORP_ID) VENDOR_CD                                                                                                                    			\n");
			sql.append("	   FROM     PUR1100T A, PMM_PAYREQ_ITEM B, PUR1000T C, ETEC_ERPIF.ACZ10100 D, PMM_COMM_MATERIAL_MASTER@CCSIF E                          			\n");
			sql.append("	      WHERE A.PAYREQ_ID = B.PAYREQ_ID                                                                                                            			\n");
			sql.append("	      AND A.PUR_CONT_ID = C.PUR_CONT_ID                                                                                                     			\n");
			sql.append("	      AND C.DEPT_CD = D.DEPT_CD                                                                                                                			\n");
			sql.append("	      AND B.MATERIAL_CD = E.MATERIAL_CD                                                                                                        			\n");
			sql.append("	      AND E.PAY_DIV = '1'                                                                                                                     			\n");
			sql.append("	  AND C.CORP_ID = ?                                                                                         		\n");
			search.addParameter(storage.getUser().get("corp_id"));
			
			
			if (!"".equals(params.get("dept_name"))) {
				 sql.append("	  AND GET_DEPT_NM(C.DEPT_CD) LIKE '%' || UPPER(?) || '%'                                                                                           		\n");
				 search.addParameter(params.get("dept_name"));
			 }
			 if (!"".equals(params.get("sYmdSt"))
						&& !"".equals(params.get("sYmdEd"))) {
				 sql.append("	  AND B.STOCK_DATE BETWEEN ? AND ?                                                                            		\n");
				 search.addParameter(params.get("sYmdSt").replace("-", ""));
				 search.addParameter(params.get("sYmdEd").replace("-", ""));
			 }
			 if (!"".equals(params.get("cst_doc_name"))) {
				 sql.append("	  AND C.CST_DOC_NAME LIKE '%' || ? || '%'                                                                     		\n");
				 search.addParameter(params.get("cst_doc_name"));
			 }
			 if (!"".equals(params.get("material_cd").trim())) {
				 sql.append("	  AND B.MATERIAL_CD LIKE ? || '%'                                                                                       		\n");
				 search.addParameter(params.get("material_cd").trim());
			 }
			 if (!"".equals(params.get("material_nm").trim())) {
				 sql.append("	  AND E.MATERIAL_NAME LIKE '%' || ? || '%'	\n");
				 search.addParameter(params.get("material_nm").trim());
			 }
			sql.append("	UNION ALL                                                                                                                                       			                                    	\n");
			sql.append("	SELECT                                                                                                                                       			                                    	\n");
			sql.append("	        D.DEPT_NM DEPT_NM--dept_nm                                                                                                                        			                      	\n");
			sql.append("	        , GET_CORP_NM(A.CUST_CD) VENDOR_NAME --vendor_name                                                                                         			                              	\n");
			sql.append("	        ,DECODE(E.PAY_ITEM, '', '', E.PAY_ITEM || '-' || E.COST_CODE || '-' || E.COST_TYPE) AS PAY_ITEM                                                             			                                        	\n");
			sql.append("	        ,F_GET_COST_NAME(A.PROJECT_CD, E.PAY_ITEM, E.COST_CODE, E.COST_TYPE) AS PAY_ITEM_DESC                                              			                                      	\n");
			sql.append("	        ,E.CST_DOC_NAME CST_DOC_NAME                                   			                                                                                                          	\n");
			sql.append("	        ,A.ITEM_NO MATERIAL_CD                                                                                                                    			                              	\n");
			sql.append("	        ,F.MATERIAL_NAME                                                                                                                			                                        	\n");
			sql.append("	        ,F.MATERIAL_SPEC                                                                                                                			                                        	\n");
			sql.append("	        ,F_GET_UNIT_KIND_NAME(A.ITEM_NO) UNIT_KIND_NAME                                                                             			                                            	\n");
			sql.append("	        , SUBSTR(A.WAREHOUSE_DATE,1,8) STOCK_DATE                                                                                                              			                  	\n");
			sql.append("	        , TO_NUMBER(A.GOOD_QTY + A.DAMAGE_QTY + A.OVER_QTY + A.SHORT_QTY + A.INCORRECT_QTY - A.RETURN_QTY) STOCK_QTY                               			                              	\n");
			sql.append("	        , A.REMARK REMARKS                                                                                                                         			                              	\n");
			sql.append("	        , A.WAREHOUSE_ID STOCK_ID                                                                                                                  			                              	\n");
			sql.append("	        , A.PROJECT_CD DEPT_CD                                                                                                                               			                    	\n");
			sql.append("	        ,  A.CUST_CD VENDOR_CD                                                                                                                     			                              	\n");
			sql.append("	       FROM   ETECEPC.FM_WAREHOUSE A, ETECEPC.FM_SHIPPING_DETAIL B, ACZ10100V D, PUR1000T E, PMM_COMM_MATERIAL_MASTER F                                               				\n");
			sql.append("	       WHERE  A.SHIPPING_DETAIL_ID = B.SHIPPING_DETAIL_ID                                                                                                                             	\n");
			sql.append("	       AND    A.PROJECT_CD = D.DEPT_CD                                                                                                                                                	\n");
			sql.append("	       AND    B.ORDER_NO = E.ORD_DOC_NO                                                                                                                                               	\n");
			sql.append("	       AND    A.ITEM_NO = F.MATERIAL_CD                                                                                                                                               	\n");
			sql.append("	      AND F.PAY_DIV = '1'                                                                                                                     			\n");
			sql.append("	  AND A.CUST_CD = ?                                                                                         		\n");
			search.addParameter(storage.getUser().get("corp_id"));
			
			if (!"".equals(params.get("dept_name"))) {
				 sql.append("	  AND GET_DEPT_NM(A.PROJECT_CD) LIKE '%' || UPPER(?) || '%'                                                                                           		\n");
				 search.addParameter(params.get("dept_name"));
			 }
			 if (!"".equals(params.get("sYmdSt"))
						&& !"".equals(params.get("sYmdEd"))) {
				 sql.append("	  AND SUBSTR(A.WAREHOUSE_DATE, 1, 8) BETWEEN ? AND ?	\n");
				 search.addParameter(params.get("sYmdSt").replace("-", ""));
				 search.addParameter(params.get("sYmdEd").replace("-", ""));
			 }
			 if (!"".equals(params.get("cst_doc_name"))) {
				 sql.append("	  AND E.CST_DOC_NAME LIKE '%' || ? || '%'                                                                     		\n");
				 search.addParameter(params.get("cst_doc_name"));
			 }
			 if (!"".equals(params.get("material_cd").trim())) {
				 sql.append("	  AND A.ITEM_NO LIKE ? || '%'                                                                                       		\n");
				 search.addParameter(params.get("material_cd").trim());
			 }
			 if (!"".equals(params.get("material_nm").trim())) {
				 sql.append("	  AND F.MATERIAL_NAME LIKE '%' || ? || '%'	\n");
				 search.addParameter(params.get("material_nm").trim());
			 }
			
			sql.append("	ORDER BY DEPT_CD,VENDOR_NAME,CST_DOC_NAME, MATERIAL_CD ,STOCK_DATE, STOCK_ID                                                                                                     			\n");

			 
			
			 WAFLogger.debug("getStock[협력업체 입고현황] End");
			search.setStatement(sql.toString());
			rowSet = search.execute();
		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName()
					+ " --> getStock() : \n" + se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		} finally {
			if (rowSet == null)
				rowSet = new RowSetImpl();
			return rowSet;
		}
	}

	/**
	 * 입고유형 가져오기
	 * 
	 * @author 김예진
	 * @since 2014.05.01
	 * @return
	 * @throws Exception
	 */
	public RowSet getStockGbRowSet() {

		Search search = new SearchImpl();
		RowSet rowSet = new RowSetImpl();
		StringBuffer sql = new StringBuffer();

		try {

			sql.append("   SELECT ' ' DETAILCD, '전체' DETAILNM FROM DUAL      		\n");
			sql.append("   UNION ALL      		\n");
			sql.append("   SELECT DETAILCD      		\n");
			sql.append("         ,DETAILNM				\n");
			sql.append("   FROM EBID_COM911T		    		\n");
			sql.append("   WHERE BASECD = 'TRAN_TYPE'   \n");
			sql.append("   AND CHG_CODE01 = '1'     	\n");
			sql.append("   ORDER BY DETAILCD     		\n");

			search.setStatement(sql.toString());
			rowSet = search.execute();

		} catch (WAFSQLException se) {
			WAFLogger.error(this.getClass().getName()
					+ " --> getStockGbRowSet() : \n" + se.getMessage());
			WAFLogger.error("SQL : " + search.getStatement());
			WAFLogger.error(se);
			storage.setDetailMessage(se);
		} finally {
			if (rowSet == null)
				rowSet = new RowSetImpl();
			return rowSet;
		}
	}

}
