/**
 * @(#)file    SYSInputStandardCodeListSaveBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.08.01
 * @(#)Description: 기준코드 등록
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */
package kr.co.udapsoft.ebid.buyer.sycs.code.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.sql.util.RowSetUtility;
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.system.Biz;
import com.udapsoft.waf.system.HandlerStorage;

public class SYSInputStandardCodeListSaveBiz extends Biz {

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

	/* (non-Javadoc)
	 * @see kr.co.hwenc.system.Biz#doBiz()
	 */
	public void doBiz() throws Exception {
		boolean success = false;
		String[] status = super.gets("sheet1.STATUS");

		try {
			super.conn.begin();
			
			for (int i = 0; i < status.length; i++) {
				if (IBSheet7Utils.equalStatusInsert(status[i])) {
					success = saveListData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
				}
				
				if (IBSheet7Utils.equalStatusUpdate(status[i])) {
					success = updateListData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
				}
				
				if (IBSheet7Utils.equalStatusDelete(status[i])) {
					success = deleteListData(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();
			storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
			throw new Exception(e);
			
		} finally {
			super.conn.close();
		}
	}

	private boolean saveListData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	INSERT INTO EBID_COM910T			\n ");
			sql.append("	(										\n ");
			sql.append("		BASECD					\n ");
			sql.append("		, BASENM			\n ");
			sql.append("		, BASELENGTH						\n ");
			sql.append("		, REMARK		\n ");
			sql.append("		, CRE_BY							\n ");
			sql.append("		, CRE_DATE					\n ");
			sql.append("		, SORT_METHOD				\n ");
			sql.append("		) VALUES (				\n ");
			sql.append("		?			\n ");
			sql.append("		,?											\n ");
			sql.append("		,?											\n ");
			sql.append("		,?											\n ");
			sql.append("		,?									\n ");
			sql.append("		,SYSDATE											\n ");
			sql.append("		,'1'	\n ");
			sql.append("		)												\n ");
			
			
			//데이터 존재유무를 확인하는 코드 적용 2008-11-07
			ValueObject CntMaster = getMasterCnt(sqlParams.get("BASECD"));  
			String resultMsg = "중복된 자료가 존재합니다.";
            super.storage.setMessage(resultMsg);
            
            if(CntMaster.get("cnt").equals("0")) {
            	pstmt = connection.prepareStatement(sql.toString());
    			int paramIdx = 1;
    			
    			pstmt.setString(paramIdx++, sqlParams.get("BASECD"));
    			pstmt.setString(paramIdx++, sqlParams.get("BASENM"));
    			pstmt.setString(paramIdx++, sqlParams.get("BASELENGTH"));
    			pstmt.setString(paramIdx++, sqlParams.get("REMARK"));
    			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
    			//pstmt.setString(paramIdx++, sqlParams.get("SORT_METHOD"));
    			
    			pstmt.executeUpdate();
    			return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
	private boolean updateListData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	UPDATE EBID_COM910T SET   	\n");
			sql.append("		 BASENM = ?   	\n");
			sql.append("		, BASELENGTH = ?   	\n");
			sql.append("		, REMARK = ?   	\n");
//			sql.append("		, SORT_METHOD = ?          	\n");
			sql.append("		, UPD_BY = ?	\n");
			sql.append("		, UPD_DATE = SYSDATE	\n");
			sql.append("	WHERE BASECD = ?	\n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("BASENM"));
			pstmt.setString(paramIdx++, sqlParams.get("BASELENGTH"));
			pstmt.setString(paramIdx++, sqlParams.get("REMARK"));
//			pstmt.setString(paramIdx++, sqlParams.get("SORT_METHOD"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("BASECD"));
			
			pstmt.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);
			}
		}
	}
	
	private boolean deleteListData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		String resultMsg = "";
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	DELETE FROM EBID_COM910T WHERE BASECD = ?			\n ");
			
			
			ValueObject Cnt = getCnt(sqlParams.get("BASECD"));  
			resultMsg = "상세목록이 존재합니다. 상세목록을 먼저 삭제하세요.";
            super.storage.setMessage(resultMsg);
            
            if(Cnt.get("CNT").equals("0")) {
            	pstmt = connection.prepareStatement(sql.toString());
    			int paramIdx = 1;
    			pstmt.setString(paramIdx++, sqlParams.get("BASECD"));
    			
    			pstmt.executeUpdate();
    			return true;
			} else {
				//return false;
				throw new Exception();
			}
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(resultMsg);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}

	// COM910T 테이타 유무체크
	// 마스터내역에 데이터가 있는지를 체크한다.
	public  ValueObject getMasterCnt(String basecd) {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        String sqlstr = "";
        
		try {            									
			sqlstr  = " SELECT count(*) cnt            	  \n"
				    + "   FROM EBID_COM910T            		  \n"
				    + "  WHERE basecd = '" + basecd + "'  \n";
			
			search.setStatement(sqlstr);
            rowSet = search.execute();
            
			if( rowSet != null && rowSet.size() > 0 )
                valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
        }
		catch (WAFSQLException se) {
        	  super.doException(se);
              this.storage.setSuccess(false);
              this.storage.setMessage(se.getMessage());
              storage.setDetailMessage(se);
        }
        finally {
            if( valueobject == null ) valueobject = new ValueObject();
            return valueobject;
        }
    }
	
	
	
	// COM911T 테이타 유무체크
	// 상세내역에 데이터가 있는지를 체크한다.
	public  ValueObject getCnt(String basecd) {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        ValueObject valueobject = null;
        String sqlstr = "";
        
		try {            									
			sqlstr  = " SELECT count(*) cnt            	  \n"
				    + "   FROM EBID_COM911T            		  \n"
				    + "  WHERE basecd = '" + basecd + "'  \n";
			
			search.setStatement(sqlstr);
            rowSet = search.execute();
            
			if( rowSet != null && rowSet.size() > 0 )
                valueobject = RowSetUtility.getValueObject(rowSet.getRow(0));
        }
		catch (WAFSQLException se) {
        	  super.doException(se);
              this.storage.setSuccess(false);
              this.storage.setMessage(se.getMessage());
              storage.setDetailMessage(se);
        }
        finally {

            if( valueobject == null ) valueobject = new ValueObject();
            return valueobject;
        }
    }
}
