/**
 * @(#)file    PurExcutionListBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.7.24
 * @(#)Description: 구매실적조회
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */
package kr.co.udapsoft.ebid.buyer.warehouse.purchase.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 PurExcutionListBiz extends Biz {

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


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

		try {
			WAFLogger.debug("getPurExcutionList[구매실적조회] Start");

			sql.append("    SELECT                                                                                                 \n");
			sql.append("    	 F_GET_CODE_NAME('101',C.AC_UNIT_CD) AS AC_UNIT_NM 								--본사명           \n");
			sql.append("    	,GET_DEPT_NM(A.DEPT_CD) AS DEPT_NM 												--현장명           \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) AS MATERIAL_SPEC 							--규격             \n");
			sql.append("    	,F_GET_UNIT_KIND_NAME(A.MATERIAL_CD) AS UNIT 									--단위             \n");
			sql.append("    	,A.STOCK_QTY 																	--입고수량         \n");
			sql.append("    	,A.STOCK_AMT 																	--입고금액         \n");
			sql.append("    	,B.CONT_PRI * A.STOCK_QTY AS TOTAL_CONTRACT_AMT 							--도급금액         \n");
			sql.append("    	,B.DEMANDED_PRI * A.STOCK_QTY AS TOTAL_BUDGET_AMT 								--실행금액         \n");
			sql.append("    	,DECODE((B.CONT_PRI * A.STOCK_QTY),0,'-'                                                       \n");
			sql.append("    			,(A.STOCK_AMT/(B.CONT_PRI * A.STOCK_QTY)*100)) AS CONTRAST_CONTRACT --도급대비         \n");
			sql.append("    	,DECODE((B.DEMANDED_PRI * A.STOCK_QTY),0,'-'                                                         \n");
			sql.append("    			,(A.STOCK_AMT/(B.DEMANDED_PRI * A.STOCK_QTY)*100)) AS CONTRAST_BUDGET 	--실행대비         \n");
			sql.append("    	,C.AC_UNIT_CD 																	--본사코드         \n");
			sql.append("    	,A.DEPT_CD 																		--현장코드         \n");
			sql.append("    FROM                                                                                                   \n");
			sql.append("    (                                                                                                      \n");
			sql.append("    	SELECT CC.DEPT_CD,AA.MATERIAL_CD,SUM(AA.ITEM_QTY) STOCK_QTY, SUM(AA.ITEM_AMT) STOCK_AMT          \n");
			sql.append("    	FROM PMM_PAYREQ_ITEM AA, PUR1100T BB , PUR1000T CC                                                              \n");
			sql.append("    	WHERE AA.PAYREQ_ID = BB.PAYREQ_ID                                                                    \n");
			sql.append("    	  AND BB.PUR_CONT_ID = CC.PUR_CONT_ID                                                                    \n");
			if (!"".equals(params.get("sYmdSt")) && !"".equals(params.get("sYmdEd"))) {
				sql.append("    AND AA.STOCK_DATE BETWEEN '"+ params.get("sYmdSt").replace("-", "")+ "' "
									+ "AND '"+ params.get("sYmdEd").replace("-", "")+ "' \n");
			}
			sql.append("    	GROUP BY CC.DEPT_CD, AA.MATERIAL_CD                                                                \n");
			sql.append("    	UNION ALL                                                                 \n");
			sql.append("    		SELECT PROJECT_CD DEPT_CD,ITEM_NO MATERIAL_CD ,SUM(GOOD_QTY + OVER_QTY) STOCK_QTY, SUM(INPUT_AMT) STOCK_AMT		\n");
			sql.append("    		FROM ETECEPC.FM_WAREHOUSE                                                                \n");
			if (!"".equals(params.get("sYmdSt")) && !"".equals(params.get("sYmdEd"))) {
				sql.append("    WHERE SUBSTR(WAREHOUSE_DATE, 1,8) BETWEEN '"+ params.get("sYmdSt").replace("-", "")+ "' "
									+ "AND '"+ params.get("sYmdEd").replace("-", "")+ "' \n");
			}
			sql.append("    		GROUP BY PROJECT_CD, ITEM_NO	\n");
			sql.append("    ) A,                                                                                                   \n");
			sql.append("    (                                                                                                      \n");
			sql.append("    	SELECT A.DEPT_CD, B.MATERIAL_CD, SUM(DEMANDED_QTY * NVL(CONT_PRI,0)) CONTRACT_AMT, SUM(DEMANDED_QTY * DEMANDED_PRI) BUDGET_AMT       \n");
			sql.append("    	, B.CONT_PRI, B.DEMANDED_PRI		\n");
			sql.append("    	FROM PMM_DEMANDED_REQUEST A, PMM_DEMANDED_ITEM B                                                                                     \n");
			sql.append("    	WHERE A.DEMANDED_ID = B.DEMANDED_ID		\n");
			sql.append("    	GROUP BY A.DEPT_CD, B.MATERIAL_CD, B.CONT_PRI, B.DEMANDED_PRI                                          \n");
			sql.append("    ) B,                                                                                                   \n");
			sql.append("    ETEC_ERPIF.ACZ10100 C                                                                                             \n");
			sql.append("    WHERE A.MATERIAL_CD = B.MATERIAL_CD                                                                    \n");
			sql.append("    AND A.DEPT_CD = B.DEPT_CD                                                                              \n");
			sql.append("    AND A.DEPT_CD = C.DEPT_CD                                                                              \n");
			
			if (!"".equals(params.get("ac_unit_cd"))) {
				sql.append("  AND C.AC_UNIT_CD = '"+ params.get("ac_unit_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("material_cd"))) {
				sql.append("  AND A.MATERIAL_CD LIKE '%"+ params.get("material_cd")+ "%'	\n");
			}
			if (!"".equals(params.get("material_nm"))) {
				sql.append("  AND F_GET_MATERIAL_NAME(A.MATERIAL_CD) LIKE '%"+ params.get("material_nm")+ "%'	\n");
			}
			sql.append(" ORDER BY A.DEPT_CD,A.MATERIAL_CD       \n");

			search.setStatement(sql.toString());

			rowSet = search.execute();
			WAFLogger.debug(sql.toString());
			WAFLogger.debug("getPurExcutionList[구매실적조회] 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;
		}
	}
}

