/**
 * @(#)file    ContractCloseBiz.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 java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

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.modular.web.ibsheet7.util.IBSheet7Utils;
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 ContractCloseBiz extends Biz {
	
    public ContractCloseBiz(HandlerStorage storage) {
        super(storage);        
    }

    public void doBiz() throws Exception {
    	boolean success =  false;
    	
    	if(storage.compareEvent("UPDATE_DATA")){		
			String[] status = super.gets("sheet1.status");

			try {
				super.conn.begin();
				
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						success = statusUpdate(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
				}
				
				super.storage.setSuccess(success);
				storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			} catch (Exception e) {
				WAFLogger.error(e);
				super.conn.setRollback();
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		}else{
			getContractCloseList();
		}
    	
    }
    
    private void getContractCloseList() throws Exception {
    	if(storage.getEvent()==""){
    		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", getContractClose(params));
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
    	}	
    }
    
    
    /**
     * 계획물량관리 List 가져오기
     * @author 김예진
     * @since 2014.04.09
     * @return
     * @throws Exception
     */
    public RowSet getContractClose(ValueObject params) {
        Search search 	  = new SearchImpl();
        RowSet rowSet 	  = new RowSetImpl();
        StringBuffer sql  = new StringBuffer();
        
        try {
        	WAFLogger.debug("getContractClose[계획물량관리] Start");
        	//2018.11.27 계약물량관리 미사용 
			sql.append(" SELECT B.DEPT_NAME, 																	--계약번호       \n");
			sql.append("		B.ORD_DOC_NO, 																	--계약번호       \n");
			sql.append("        B.CST_DOC_NAME, 																-- 계약명        \n");
			sql.append("        TO_CHAR(B.ORD_CON_DATE, 'yyyy-mm-dd')  AS ORD_CON_DATE,  --계약일자       \n");
			sql.append("        SUM(A.ITEM_QTY) ITEM_QTY, 														-- 계약량        \n");
			sql.append("        AVG(A.ITEM_PRICE) ITEM_PRICE, 													--계약단가       \n");
			sql.append("        SUM(A.ITEM_AMT) ITEM_AMT,  														-- 계약금액      \n");
			sql.append("        SUM(C.STOCK_QTY) STOCK_QTY, 													-- 납품량        \n");
			sql.append("        AVG(C.STOCK_PRI) STOCK_PRI, 													-- 납품단가      \n");
			sql.append("        SUM(C.STOCK_AMT) STOCK_AMT,  													-- 납품금액      \n");
			sql.append("        B.ENT_CLS_STATUS, 																--입고상태       \n");
			sql.append("        B.PUR_CONT_CLOSE_REMARK, 														--강제종결사유   \n");
			sql.append("        B.PUR_CONT_ID 																	--발주계약아이디 \n");
			sql.append(" FROM   PUR1010T@CCSIF A, PUR1000T@CCSIF B,                                                          \n");
			sql.append("	(                                  		\n");
			sql.append("		SELECT                           		\n");
			sql.append("			PUR_CONT_ID                    		\n");
			sql.append("			, MATERIAL_CD                  		\n");
			sql.append("			, SUM(STOCK_QTY) STOCK_QTY     		\n");
			sql.append("			, AVG(STOCK_PRI) STOCK_PRI     		\n");
			sql.append("			, SUM(STOCK_AMT) STOCK_AMT     		\n");
			sql.append("		FROM ETEC_ERPIF.PMM_STOCK_ITEM          \n");//2018.11.27 계약물량관리 미사용 
			sql.append("		GROUP BY PUR_CONT_ID, MATERIAL_CD		\n");
			sql.append("	)C                                 		\n");
			sql.append(" WHERE  A.PUR_CONT_ID = B.PUR_CONT_ID                                                                    \n");
			sql.append(" AND    A.PUR_CONT_ID = C.PUR_CONT_ID(+)                                                                 \n");
			sql.append(" AND    A.MATERIAL_CD = C.MATERIAL_CD(+)                                                                 \n");
			sql.append(" AND    B.ENT_CLS_YN <> 'Y'                                                      		                 \n");
			sql.append(" AND    B.CON_RUN_STS = '9'                                                                              \n");
			if(!"".equals(params.get("dept_cd"))) {
				sql.append("		AND     B.DEPT_CD = ?                                                              						\n");
    			search.addParameter(params.get("dept_cd"));
			}
			if(!"".equals(params.get("sYmdSt")) && !"".equals(params.get("sYmdEd"))) {
				sql.append(" AND    B.ORD_CON_DATE BETWEEN '"+params.get("sYmdSt")+"' AND '"+params.get("sYmdEd")+"'   		 \n");
			}
			if(!"".equals(params.get("s_pur_cont_status"))) {
				sql.append(" AND    B.ENT_CLS_STATUS = '"+params.get("s_pur_cont_status")+"'                                     \n");
			}
			sql.append(" GROUP BY B.DEPT_NAME, B.ORD_DOC_NO, B.CST_DOC_NAME, B.ORD_CON_DATE, B.ENT_CLS_STATUS, B.PUR_CONT_CLOSE_REMARK, B.PUR_CONT_ID       \n");
			sql.append(" ORDER BY B.PUR_CONT_ID       \n");
			
            search.setStatement(sql.toString());
            
            rowSet = search.execute();
            
            WAFLogger.debug("getContractClose[계획물량관리] 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;
        }
    }
    
    
    /**
     * 업데이트
     * @author 김예진
     * @since 2014.04.09
     * @return
     * @throws Exception
     */
    public boolean statusUpdate(Connection connection, ValueObject sqlParams) throws Exception  {
        PreparedStatement pstmt = null;
        PreparedStatement pstmt2 = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("  UPDATE PUR1000T@CCSIF SET    					\n");
	        sql.append("         ENT_CLS_STATUS = ?,     			\n");
	        sql.append("         PUR_CONT_CLOSE_REMARK = ?      	\n");
	        sql.append("  WHERE PUR_CONT_ID = ?         			\n");
			
	        pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("ENT_CLS_STATUS"));
			pstmt.setString(paramIdx++, sqlParams.get("PUR_CONT_CLOSE_REMARK"));
			pstmt.setString(paramIdx++, sqlParams.get("PUR_CONT_ID"));
			
			pstmt.executeUpdate();
			
			StringBuffer sql2 = new StringBuffer();
			sql2.append("  UPDATE PUR1000T SET    					\n");
			sql2.append("         ENT_CLS_STATUS = ?,     			\n");
			sql2.append("         PUR_CONT_CLOSE_REMARK = ?      	\n");
			sql2.append("  WHERE PUR_CONT_ID = ?         			\n");
			
			pstmt2 = connection.prepareStatement(sql2.toString());
			int paramIdx2 = 1;
			
			pstmt2.setString(paramIdx2++, sqlParams.get("ENT_CLS_STATUS"));
			pstmt2.setString(paramIdx2++, sqlParams.get("PUR_CONT_CLOSE_REMARK"));
			pstmt2.setString(paramIdx2++, sqlParams.get("PUR_CONT_ID"));
			
			pstmt2.executeUpdate();
			
			return true;
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
    }
    
    
    
    
 }


