/**
 * @(#)file    ChgItemBiz.java
 * @(#)author  Kim Myung Ho
 * @(#)version 1.0
 * @(#)date    2014-08-11
 * @(#)since   JDK 1.4.2
 */
package kr.co.udapsoft.ebid.buyer.cont.purchase.biz;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

import oracle.toplink.internal.ox.StrBuffer;
import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.config.WAFConfig;
import kr.co.hsnc.common.sql.Row;
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.persistent.Persistent;
import kr.co.hsnc.common.sql.persistent.PersistentImpl;
import kr.co.hsnc.common.sql.search.Search;
import kr.co.hsnc.common.sql.search.SearchImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
import kr.co.hsnc.common.util.ValueObject;
import kr.co.udapsoft.common.code.CommCodeManager;
import kr.co.udapsoft.common.util.Crosscert;
import kr.co.udapsoft.common.util.PDFMaker;
import kr.co.udapsoft.ebid.buyer.common.file.FileManager;
import kr.co.udapsoft.ebid.buyer.sycs.user.biz.UserCommon;

import com.udapsoft.modular.web.ibsheet7.IBSheet7Factory;
import com.udapsoft.modular.web.ibsheet7.util.IBSheet7Utils;
import com.udapsoft.waf.common.ui.InitCombo;
import com.udapsoft.waf.common.util.StringUtils;
import com.udapsoft.waf.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

   
public class ChgItemBiz extends Biz {

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

	public void doBiz() throws Exception {
        ValueObject user = new ValueObject();
		//WAFLogger.debug("HHHHHHHHHHHHHHHHHHHHHHHHHH : " + params.get("PUR_CONT_MOD_ID"));
        //WAFLogger.debug("ChgItemBiz :  ~" + storage.getUser());

		boolean success = false;
		
		//변경 내역
		if(storage.compareEvent("")){
			//변경상품이 아닌 계약 상품팝업일경우 상품 목록을 기본 가져온다. 2014-12-16 김명호 추가
			WAFLogger.debug("HHHHHHHHHHHHHHHHHHHHHHHHHH : " + params.get("pop_win_id"));
			if(params.get("pop_win_id").equals("ItemPop")){
	            RowSet itemList = getContItemList(params);
	            storage.addRowSet("itemList", itemList);
			}
			
		}else if(storage.compareEvent("SHEET_SELECT")){
			params.set("sheet1", getChgItemList(params));
			
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("SHEET_SAVE")){
			
			success = saveItemProc(super.conn.getConnection(), params);
			
			super.storage.setSuccess(success);
			
            //WAFLogger.debug("SUCCESS : ++++++++++++++++++++++++++++++++++++++++++ " + success);
            if(success)
            	storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));		
         //당초계약 내역   
		}else if(storage.compareEvent("SHEET_ITEM_SELECT")){
			params.set("sheet1", getItemList(params));
			
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		}else if(storage.compareEvent("SHEET_ITEM_SAVE")){
			
			success = saveItemProc(super.conn.getConnection(), params);
			
			super.storage.setSuccess(success);
			
            //WAFLogger.debug("SUCCESS : ++++++++++++++++++++++++++++++++++++++++++ " + success);
            if(success)
            	storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));			
		}	
	}

	/**
	 * 계약 변경 내역 목록
	 * @param Params
	 * @return
	 */
	private RowSet getChgItemList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sql = new StrBuffer();
        try{
        	sql.append(" 	SELECT MATERIAL_CD -- 자재코드 		\n");
        	sql.append(" 	     , ITEM_NAME -- 품명 		\n");
        	sql.append(" 	     , ITEM_SPEC -- 규격 		\n");
        	sql.append(" 	     , ITEM_UNIT -- 단위 		\n");
        	sql.append(" 	     , ITEM_QTY -- 수량(변경전 계약) 		\n");
        	sql.append(" 	     , ITEM_PRICE -- 단가(변경전 계약) 		\n");
        	sql.append(" 	     , ITEM_AMT -- 금액(변경전 계약) 		\n");
        	sql.append(" 	     , CONT_QTY -- 수량(변경후 계약)  		\n");
        	sql.append(" 	     , CONT_PRICE -- 단가(변경후 계약)  		\n");
        	sql.append(" 	     , DECODE(CONT_AMT,0,CONT_QTY*CONT_PRICE,CONT_AMT) CONT_AMT -- 금액(변경후 계약)  		\n");
        	sql.append(" 	     , DEMANDED_QTY --  발주의뢰 수량 		\n");
        	sql.append(" 	     , DEMANDED_PRI -- 발주의뢰 단가 		\n");
        	sql.append(" 	     , DECODE(DEMANDED_AMT,0,DEMANDED_QTY*DEMANDED_PRI,DEMANDED_AMT) DEMANDED_AMT -- 발주의뢰 금액 		\n");
        	sql.append(" 	     , STOCK_QTY		\n");
        	sql.append(" 	     , CHG_MOD_CD 		\n");
        	sql.append(" 	  FROM 		\n");
        	sql.append(" 	       (SELECT NVL (B1.MATERIAL_CD, A1.MATERIAL_CD) MATERIAL_CD -- 자재코드 		\n");
        	sql.append(" 	            , NVL (B1.ITEM_NAME, (SELECT MATERIAL_NAME FROM PMM_COMM_MATERIAL_MASTER WHERE MATERIAL_CD=A1.MATERIAL_CD)) ITEM_NAME -- 품명 		\n");
        	sql.append(" 	            , NVL (B1.ITEM_SPEC, (SELECT MATERIAL_SPEC FROM PMM_COMM_MATERIAL_MASTER WHERE MATERIAL_CD=A1.MATERIAL_CD)) ITEM_SPEC -- 규격 		\n");
        	sql.append(" 	            , NVL (F_GET_CODE_NAME('UNIT_KIND',B1.ITEM_UNIT), (SELECT UNIT_KIND FROM PMM_COMM_MATERIAL_MASTER WHERE MATERIAL_CD=A1.MATERIAL_CD)) ITEM_UNIT -- 단위 		\n");
        	sql.append(" 	            , NVL (B1.ITEM_QTY, 0) ITEM_QTY -- 수량(변경전 계약) 		\n");
        	sql.append(" 	            , B1.ITEM_PRICE -- 단가(변경전 계약) 		\n");
        	sql.append(" 	            , NVL (B1.ITEM_QTY, 0)*B1.ITEM_PRICE ITEM_AMT -- 금액(변경전 계약) 		\n");
        	
        	sql.append(" 	            , NVL (D1.ITEM_QTY, 0) CONT_QTY -- 수량(변경후 계약) 		\n");
        	sql.append(" 	            , CASE 		\n");
        	sql.append(" 	                  WHEN D1.ITEM_PRICE = NULL 		\n");
        	sql.append(" 	                  THEN B1.ITEM_PRICE 		\n");
        	sql.append(" 	                  ELSE D1.ITEM_PRICE 		\n");
        	sql.append(" 	             END CONT_PRICE -- 단가(변경후 계약) 		\n");
        	sql.append(" 	            , NVL(D1.ITEM_AMT,0) CONT_AMT -- 발주의뢰 금액 		\n");
     	
        	sql.append(" 	            , NVL (A1.DEMANDED_QTY, 0) DEMANDED_QTY --  발주의뢰 수량 		\n");
        	sql.append(" 	            , CASE 		\n");
        	sql.append(" 	                  WHEN NVL (A1.DEMANDED_PRI, 0)=0 		\n");
        	sql.append(" 	                  THEN B1.ITEM_PRICE 		\n");
        	sql.append(" 	                  ELSE A1.DEMANDED_PRI 		\n");
        	sql.append(" 	              END DEMANDED_PRI -- 발주의뢰 단가 		\n");
        	sql.append(" 	            , NVL(A1.DEMANDED_AMT,0) DEMANDED_AMT -- 발주의뢰 금액 		\n");
        	sql.append(" 	            , NVL (C1.STOCK_QTY, 0) STOCK_QTY		\n");
        	sql.append(" 	            , CASE 		\n");
        	sql.append(" 	                  WHEN B1.MATERIAL_CD IS NOT NULL 		\n");
        	sql.append(" 	                      AND A1.MATERIAL_CD IS NOT NULL 		\n");
        	sql.append(" 	                  THEN 'MODIFY' 		\n");
        	sql.append(" 	                  WHEN B1.MATERIAL_CD IS NULL 		\n");
        	sql.append(" 	                      AND A1.MATERIAL_CD IS NOT NULL 		\n");
        	sql.append(" 	                  THEN 'NEW' 		\n");
        	sql.append(" 	                  WHEN B1.MATERIAL_CD IS NOT NULL 		\n");
        	sql.append(" 	                      AND A1.MATERIAL_CD IS NULL 		\n");
        	sql.append(" 	                  THEN 'DELETE' 		\n");
        	sql.append(" 	              END CHG_MOD_CD 		\n");
        	sql.append(" 	         FROM 		\n");
        	sql.append(" 	              (SELECT* 		\n");
        	sql.append(" 	                FROM PMM_DEMANDED_ITEM 		\n");
        	sql.append(" 	               WHERE DEMANDED_ID= "+ params.get("pDemID") +" 		\n");
        	sql.append(" 	                     AND DEMANDED_QTY>0		\n");
        	sql.append(" 	              ) A1 		\n");
        	sql.append(" 	          FULL OUTER JOIN 		\n");
        	sql.append(" 	              (SELECT *  FROM CONT_PUR_ITEM WHERE PUR_CONT_MOD_ID=(SELECT MAX(PUR_CONT_MOD_ID) FROM PUR2000T WHERE PUR_CONT_ID="+ params.get("PUR_CONT_ID") +" AND PUR_CONT_MOD_ID !="+ params.get("PUR_CONT_MOD_ID") +")		\n");
        	sql.append(" 	              ) B1 		\n");
        	sql.append(" 	              ON A1.MATERIAL_CD=B1.MATERIAL_CD 		\n");
        	sql.append(" 	          FULL OUTER JOIN 		\n");
        	sql.append(" 	              (SELECT *  FROM CONT_PUR_ITEM WHERE PUR_CONT_MOD_ID="+ params.get("PUR_CONT_MOD_ID") +"		\n");
        	sql.append(" 	              ) D1 		\n");
        	sql.append(" 	              ON NVL(A1.MATERIAL_CD, B1.MATERIAL_CD)=D1.MATERIAL_CD 		\n");
        	sql.append(" 	          LEFT OUTER JOIN 		\n");
        	sql.append(" 	              (SELECT PUR_CONT_MOD_ID, MATERIAL_CD, CEIL (SUM (STOCK_QTY)) STOCK_QTY 		\n");
        	sql.append(" 	                FROM 		\n");
        	sql.append(" 	                     (SELECT T3.PUR_CONT_MOD_ID, T2.ITEM_NO MATERIAL_CD , T2.SHIP_QTY STOCK_QTY 		\n");
        	sql.append(" 	                          FROM ETECEPC.FM_SHIPPING_LIST T1		\n");
        	sql.append(" 	                             , ETECEPC.FM_SHIPPING_DETAIL T2		\n");
        	sql.append(" 	                             , ETEC_EBID.PUR2000T T3 		\n");
        	sql.append(" 	                          WHERE T1.SHIPPING_LIST_ID= T2.SHIPPING_LIST_ID 		\n");
        	sql.append(" 	                            AND T1.ORDER_NO= T3.ORD_DOC_NO 		\n");
        	sql.append(" 	                     UNION ALL 		\n");
        	sql.append(" 	                     SELECT T3.PUR_CONT_MOD_ID, T1.MATERIAL_CD, T1.ITEM_QTY  		\n"); 
        	sql.append(" 	                          FROM PMM_PAYREQ_ITEM T1		\n");
        	sql.append(" 	                             , PUR1100T T2		\n");
        	sql.append(" 	                             , PUR2000T T3 		\n");
        	sql.append(" 	                          WHERE T1.PAYREQ_ID=T2.PAYREQ_ID 		\n");
        	sql.append(" 	                            AND T2.PUR_CONT_mod_ID= T3.PUR_CONT_mod_ID		\n");
        	sql.append(" 	                     ) 		\n");
        	sql.append(" 	               WHERE PUR_CONT_MOD_ID = "+ params.get("PUR_CONT_MOD_ID") +" 		\n");
        	sql.append(" 	               GROUP BY PUR_CONT_MOD_ID, MATERIAL_CD		\n");
        	sql.append(" 	              ) C1 		\n");
        	sql.append(" 	              ON A1.MATERIAL_CD=C1.MATERIAL_CD		\n");
        	sql.append(" 	       )		\n");

            
            search.setStatement(sql.toString());
            WAFLogger.debug(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();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        return rowSet;
	}
	
	/**
	 * 계약 변경 내역 목록
	 * @param Params
	 * @return
	 */
	private RowSet getItemList(ValueObject param) {

		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        
        StrBuffer sql = new StrBuffer();
        try{
        	sql.append(" 	SELECT PUR_CONT_MOD_ID	\n");
        	sql.append(" 	     , MATERIAL_CD		\n");
        	sql.append(" 	     , PUR_CONT_ID		\n");
        	sql.append(" 	     , ITEM_NAME		\n");
        	sql.append(" 	     , ITEM_SPEC		\n");
        	sql.append(" 	     , ITEM_UNIT		\n");
        	sql.append(" 	     , ITEM_QTY		AS CONT_QTY		\n");
        	sql.append(" 	     , ITEM_PRICE	AS CONT_PRICE	\n");
        	sql.append(" 	     , ITEM_AMT     AS CONT_AMT		\n");	
        	sql.append(" 	  FROM 	CONT_PUR_ITEM        		\n");	
        	sql.append(" 	  WHERE PUR_CONT_MOD_ID = "+ params.get("PUR_CONT_MOD_ID") +" 		\n");
            search.setStatement(sql.toString());
            WAFLogger.debug(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();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        return rowSet;
	}
	
	
	/**
     * 변경 내역 정보 저장
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public boolean saveItemProc(Connection connection, ValueObject valueObject) throws Exception {
		//WAFLogger.debug("valueObject : " + valueObject);

        try {
        	conn.begin();

			String[] status = super.gets("sheet1.STATUS");
//			delContItemData(conn.getConnection(), params);	//기존 상품내역  삭제
			
			for (int i = 0; i < status.length; i++) {
				//WAFLogger.debug(IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
				saveChgItem(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
			}				


			return true;
        }
        catch(Exception e) {
        	//WAFLogger.debug("Exception saveContProc : " + e.getMessage());
            WAFLogger.error(this.getClass().getName() + " --> addChangeContProc() : " + e.getMessage());
            WAFLogger.error(e);

            conn.setRollback();
            
            this.storage.setSuccess(false);  
            this.storage.setMessage(e.getMessage());            
            storage.setDetailMessage(e);
            return false;
        }
        finally {
        	super.conn.end();
        }        
    }	
	/**
     * 대금계획 삭제
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */
	public void delContItemData(Connection connection, ValueObject valueObject) throws Exception {

        Persistent persistent = new PersistentImpl(connection);
        StrBuffer sqlstr = new StrBuffer();
        try {
        	sqlstr.append("DELETE FROM  CONT_PUR_ITEM						\n");
        	sqlstr.append("WHERE PUR_CONT_MOD_ID = ?						\n");
            persistent.setStatement(sqlstr.toString());
            persistent.addParameter(valueObject.get("PUR_CONT_MOD_ID"));
            persistent.execute();
        }
        catch(Exception e) {
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
    }	
	/**
     * 대금계획 추가
	 * @param connection
	 * @param valueObject
	 * @throws Exception
	 */	
	public boolean saveChgItem(Connection connection, ValueObject sqlParams) throws Exception {
		Persistent persistent = new PersistentImpl(connection);
		StringBuffer sqlstr = new StringBuffer();
		
		try {
//			sqlstr.append("INSERT INTO CONT_PUR_ITEM 	\n");
//			sqlstr.append("       ( 	PUR_CONT_MOD_ID	\n");
//			sqlstr.append("         ,	MATERIAL_CD		\n");
//			sqlstr.append("         ,	PUR_CONT_ID		\n");
//			sqlstr.append("         ,	ITEM_NAME		\n");
//			sqlstr.append("         ,	ITEM_SPEC		\n");
//			sqlstr.append("         ,	ITEM_UNIT		\n");
//			sqlstr.append("         ,	ITEM_QTY		\n");
//			sqlstr.append("         ,	ITEM_PRICE		\n");
//			sqlstr.append("         ,	ITEM_AMT		\n");
//			sqlstr.append("         ,	CRE_BY			\n");
//			sqlstr.append("         ,	CRE_DATE		\n");
//			sqlstr.append("		)VALUES(	?			\n");
//			sqlstr.append("         ,	?				\n");
//			sqlstr.append("         ,	?				\n");
//			sqlstr.append("         ,	?				\n");
//			sqlstr.append("         ,	?				\n");
//			sqlstr.append("         ,	?				\n");
//			sqlstr.append("         ,	?				\n");
//			sqlstr.append("         ,	?				\n");
//			sqlstr.append("         ,	?				\n");
//			sqlstr.append("         ,	?				\n");
//			sqlstr.append("         ,	SYSDATE			\n");
//			sqlstr.append("  )	\n");
			
			sqlstr.append("UPDATE CONT_PUR_ITEM     	\n");
			sqlstr.append("   SET ITEM_QTY = ?      	\n");
			sqlstr.append("     , ITEM_PRICE = ?    	\n");
			sqlstr.append("     , ITEM_AMT = ?      	\n");
			sqlstr.append("     , UPD_BY = ?        	\n");
			sqlstr.append("     , UPD_DATE = SYSDATE	\n");
			sqlstr.append(" WHERE PUR_CONT_MOD_ID = ?	\n");
			sqlstr.append("   AND MATERIAL_CD = ?		\n");
			//WAFLogger.debug("DDDDDDDDDDDDDDDD" + sqlParams.get("PUR_CONT_MOD_ID"));
			persistent.setStatement(sqlstr.toString());
			
			int paramIdx = 1;

//			persistent.addParameter(params.get("PUR_CONT_MOD_ID"));
//			persistent.addParameter(sqlParams.get("MATERIAL_CD"));
//			persistent.addParameter(params.get("PUR_CONT_ID"));
//			persistent.addParameter(sqlParams.get("ITEM_NAME"));
//			persistent.addParameter(sqlParams.get("ITEM_SPEC"));
//			persistent.addParameter(sqlParams.get("ITEM_UNIT"));
//			persistent.addParameter(sqlParams.get("CONT_QTY"));
//			persistent.addParameter(sqlParams.get("CONT_PRICE"));
//			persistent.addParameter(sqlParams.get("CONT_AMT"));
//			persistent.addParameter(storage.getUser().getInt("ACCT_ID"));
			persistent.addParameter(sqlParams.get("CONT_QTY"));
			persistent.addParameter(sqlParams.get("CONT_PRICE"));
			persistent.addParameter(sqlParams.get("CONT_AMT"));
			persistent.addParameter(storage.getUser().getInt("ACCT_ID"));
			persistent.addParameter(params.get("PUR_CONT_MOD_ID"));
			persistent.addParameter(sqlParams.get("MATERIAL_CD"));
			
			persistent.execute();
			
			return true;
		}         
			catch(Exception e) {
			storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
            WAFLogger.error(this.getClass().getName() + " --> updUser() : " + e.getMessage());
            WAFLogger.error("SQL : " + sqlstr);
            WAFLogger.error(e);
            throw e;
        }
	}	
	
	
	/**
	 * 품목 리스트
	 * @param Params
	 * @return
	 * 
	 */
	private RowSet getContItemList(ValueObject ValueObject) {

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

        StrBuffer sqlstr = new StrBuffer();
        try{
        	sqlstr.append(" SELECT MATERIAL_CD, ITEM_NAME, ITEM_SPEC, NVL(ITEM_QTY, 0) as ITEM_QTY, ITEM_UNIT, NVL(F_GET_CODE_NAME('UNIT_KIND_GB',ITEM_UNIT),ITEM_UNIT) AS ITEM_UNIT_NM, nvl(ITEM_PRICE, 0) AS ITEM_PRICE, nvl(ITEM_AMT, 0) AS ITEM_AMT, (nvl(ITEM_QTY,0) * nvl(ITEM_PRICE,0)) AS SUM_AMT	\n");
        	sqlstr.append("   FROM CONT_PUR_ITEM										\n");
        	sqlstr.append(" WHERE PUR_CONT_MOD_ID = '"+params.get("PUR_CONT_MOD_ID")+"'	\n");
			
			search.setStatement(sqlstr.toString());
            rowSet = search.execute();
        }
        catch (WAFSQLException se) {
            super.doException(se);
            this.storage.setSuccess(false);
            this.storage.setMessage(se.getMessage());
            storage.setDetailMessage(se);
            WAFLogger.error("SQL : " + sqlstr);
        }
        finally {    
            if(rowSet == null){
            	rowSet = new RowSetImpl();
            }
        }
        //WAFLogger.debug("### rowSet size : " + rowSet.size());
        
        return rowSet;
	}
	
}


