/**
 * @(#)file    SYSRightRegPgmListSaveBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.6.22
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */

 package kr.co.udapsoft.ebid.buyer.quot.outside.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 QuotDeptCodeSaveBiz extends Biz {
	
	public QuotDeptCodeSaveBiz(HandlerStorage storage) {
		super(storage);
	}

	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();
			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("		, CHG_CODE01			\n ");
			sql.append("		, CRE_BY				\n ");
			sql.append("		, CRE_DATE				\n ");
			sql.append("		, SORT_NO				\n ");
			sql.append("		) VALUES (				\n ");
			sql.append("		 '"+sqlParams.get("BASECD")+"'				\n ");
			sql.append("		,'"+sqlParams.get("DETAILCD")+"'			\n ");
			sql.append("		,'"+sqlParams.get("DETAILNM")+"'			\n ");
			sql.append("		,'"+sqlParams.get("CHG_CODE01")+"'			\n ");
			sql.append("		,'"+storage.getUser().getInt("ACCT_ID")+"'	\n ");
			sql.append("		,SYSDATE									\n ");
			sql.append("		,'"+sqlParams.get("SORT_NO")+"'				\n ");
			sql.append("		)											\n ");
			
			//데이터 존재유무를 확인하는 코드 적용 2008-11-07
			ValueObject CntDetail = getDetailCnt(sqlParams.get("BASECD"), sqlParams.get("DETAILCD"));  
			String resultMsg = "중복된 자료가 존재합니다.";
            if(CntDetail.get("cnt").equals("0")) {
            	pstmt = connection.prepareStatement(sql.toString());
    			int paramIdx = 1;
    			pstmt.executeUpdate();
    			return true;
			} else {
				super.storage.setMessage(resultMsg);
				throw new Exception(resultMsg);
			}
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		}finally {
			try {
				if(pstmt != null) pstmt.close();
				else return false;
			} 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 = '"+sqlParams.get("DETAILNM")+"'   		\n");
			sql.append("		, SORT_NO = '"+sqlParams.get("SORT_NO")+"'   		\n");
			sql.append("		, CHG_CODE01 = '"+sqlParams.get("CHG_CODE01")+"'	\n");
			sql.append("		, UPD_BY = '"+storage.getUser().getInt("ACCT_ID")+"'\n");
			sql.append("		, UPD_DATE = SYSDATE   								\n");
			sql.append("	WHERE BASECD = '"+sqlParams.get("BASECD")+"'			\n");
			sql.append("	AND DETAILCD = '"+sqlParams.get("DETAILCD")+"'			\n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;

			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 = '"+sqlParams.get("BASECD")+"' AND DETAILCD = '"+sqlParams.get("DETAILCD")+"'		\n ");
			
        	pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			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;
        }
    }	
}