/**
 * @(#)file    SYSInputStandardCodeListDetailSaveBiz.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.WAFSQLException;
import kr.co.hsnc.common.sql.RowSet;
import kr.co.hsnc.common.sql.RowSetImpl;
import kr.co.hsnc.common.sql.util.RowSetUtility;
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.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 SYSInputStandardCodeListDetailSaveBiz extends Biz {

	public SYSInputStandardCodeListDetailSaveBiz(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("sheet2.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("SHEET2", params, i));
				}
				
				if (IBSheet7Utils.equalStatusUpdate(status[i])) {
					success = updateListData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", params, i));
				}
				
				if (IBSheet7Utils.equalStatusDelete(status[i])) {
					success = deleteListData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET2", 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();
		}
	}
	
	private boolean saveListData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	INSERT INTO EBID_COM911T			\n ");
			sql.append("	(										\n ");
			sql.append("		BASECD					\n ");
			sql.append("		, DETAILCD			\n ");
			sql.append("		, DETAILNM						\n ");
			sql.append("		, DETAILSORTNM		\n ");
			sql.append("		, CHG_CODE01, CHG_CODE02, CHG_CODE03, CHG_CODE04, CHG_CODE05, CHG_CODE06, CHG_CODE07, CHG_CODE08							\n ");
			sql.append("		, CHG_CODE09, CHG_CODE10, CHG_CODE11, CHG_CODE12, CHG_CODE13, CHG_CODE14, CHG_CODE15, CHG_CODE16					\n ");
			sql.append("		, CHG_CODE17, CHG_CODE18, CHG_CODE19, CHG_CODE20				\n ");
			sql.append("		, REMARK	\n ");
			sql.append("		, CRE_BY	\n ");
			sql.append("		, CRE_DATE	\n ");
			sql.append("		, SORT_NO	\n ");
			sql.append("		) VALUES (				\n ");
			sql.append("		?			\n ");
			sql.append("		,?											\n ");
			sql.append("		,?											\n ");
			sql.append("		,?											\n ");
			sql.append("		,?, ?, ?, ?, ?, ?, ?, ?									\n ");
			sql.append("		,?, ?, ?, ?, ?, ?, ?, ?									\n ");
			sql.append("		,?, ?, ?, ?									\n ");
			sql.append("		,?	\n ");
			sql.append("		,?	\n ");
			sql.append("		,SYSDATE	\n ");
			sql.append("		,?	\n ");
			sql.append("		)												\n ");
			
			//데이터 존재유무를 확인하는 코드 적용 2008-11-07
			ValueObject CntDetail = getDetailCnt(sqlParams.get("BASECD"), sqlParams.get("DETAILCD"));  
			String resultMsg = "중복된 자료가 존재합니다.";
            super.storage.setMessage(resultMsg);
            
            if(CntDetail.get("cnt").equals("0")) {
            	pstmt = connection.prepareStatement(sql.toString());
    			int paramIdx = 1;
    			
    			pstmt.setString(paramIdx++, sqlParams.get("BASECD"));
    			pstmt.setString(paramIdx++, sqlParams.get("DETAILCD"));
    			pstmt.setString(paramIdx++, sqlParams.get("DETAILNM"));
    			pstmt.setString(paramIdx++, sqlParams.get("DETAILSORTNM"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE01"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE02"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE03"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE04"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE05"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE06"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE07"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE08"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE09"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE10"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE11"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE12"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE13"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE14"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE15"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE16"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE17"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE18"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE19"));
    			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE20"));
    			pstmt.setString(paramIdx++, sqlParams.get("REMARK"));
    			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
    			pstmt.setString(paramIdx++, sqlParams.get("SORT_NO"));
    			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_COM911T SET   	\n");
			sql.append("		 DETAILNM = ?   	\n");
			sql.append("		, DETAILSORTNM = ?   	\n");
			sql.append("		, CHG_CODE01 = ?   	\n");
			sql.append("		, CHG_CODE02 = ?   	\n");
			sql.append("		, CHG_CODE03 = ?   	\n");
			sql.append("		, CHG_CODE04 = ?   	\n");
			sql.append("		, CHG_CODE05 = ?   	\n");
			sql.append("		, CHG_CODE06 = ?   	\n");
			sql.append("		, CHG_CODE07 = ?   	\n");
			sql.append("		, CHG_CODE08 = ?   	\n");
			sql.append("		, CHG_CODE09 = ?   	\n");
			sql.append("		, CHG_CODE10 = ?   	\n");
			sql.append("		, CHG_CODE11 = ?   	\n");
			sql.append("		, CHG_CODE12 = ?   	\n");
			sql.append("		, CHG_CODE13 = ?   	\n");
			sql.append("		, CHG_CODE14 = ?   	\n");
			sql.append("		, CHG_CODE15 = ?   	\n");
			sql.append("		, CHG_CODE16 = ?   	\n");
			sql.append("		, CHG_CODE17 = ?   	\n");
			sql.append("		, CHG_CODE18 = ?   	\n");
			sql.append("		, CHG_CODE19 = ?   	\n");
			sql.append("		, CHG_CODE20 = ?   	\n");
			sql.append("		, REMARK = ?   	\n");
			sql.append("		, SORT_NO = ?   	\n");
			sql.append("		, UPD_BY = ?   	\n");
			sql.append("		, UPD_DATE = SYSDATE   	\n");
			sql.append("	WHERE BASECD = ?	\n");
			sql.append("	AND DETAILCD = ?	\n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("DETAILNM"));
			pstmt.setString(paramIdx++, sqlParams.get("DETAILSORTNM"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE01"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE02"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE03"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE04"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE05"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE06"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE07"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE08"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE09"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE10"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE11"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE12"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE13"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE14"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE15"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE16"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE17"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE18"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE19"));
			pstmt.setString(paramIdx++, sqlParams.get("CHG_CODE20"));
			pstmt.setString(paramIdx++, sqlParams.get("REMARK"));
			pstmt.setString(paramIdx++, sqlParams.get("SORT_NO"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("BASECD"));
			pstmt.setString(paramIdx++, sqlParams.get("DETAILCD"));

			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;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	DELETE FROM EBID_COM911T WHERE BASECD = ? AND DETAILCD = ?		\n ");
            
        	pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("BASECD"));
			pstmt.setString(paramIdx++, sqlParams.get("DETAILCD"));
			
			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);
			}
		}
	}
	
	// COM911T 테이타 유무체크
	public  ValueObject getDetailCnt(String basecd,String detailcd) {
		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"
		    		+ "  AND detailcd = '" + detailcd + "'  \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;
        }
    }	
}
