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

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;

import kr.co.hsnc.common.base.WAFLogger;
import kr.co.hsnc.common.logger.Logger;
import kr.co.hsnc.common.sql.WAFSQLException;
import kr.co.hsnc.common.util.ValueObject;

/**
 * 품질인증서 목록, 입력, 수정, 삭제 처리 Biz
 * 
 * @author PyoungJae Lee
 *
 */
public class CommCodeDescSaveBiz extends Biz {

    public CommCodeDescSaveBiz(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 = doSave(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
				}
				
				if (IBSheet7Utils.equalStatusUpdate(status[i])) {
					// CODE_ID가 없으면 INSERT 한다.
					if("".equals(IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i).get("CODE_ID"))) {
						success = doSave(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					} else {
						success = doUpdate(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
				}
				
				if (IBSheet7Utils.equalStatusDelete(status[i])) {
					success = doDelete(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 doSave(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		String code_id = "";
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	INSERT INTO TEST_COM912T			\n ");
			sql.append("	(										\n ");
			sql.append("		CODE_ID					\n ");
			sql.append("		, TABLE_NM			\n ");
			sql.append("		, BASE_CD						\n ");
			sql.append("		, COL_ENG		\n ");
			sql.append("		, COL_KOR		\n ");
			sql.append("		, COL_DESC		\n ");
			sql.append("		, CRE_DATE		\n ");
			sql.append("		, CRE_BY		\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("		,SYSDATE											\n ");
			sql.append("		,?											\n ");
			sql.append("		)												\n ");
			
			code_id = getPK(connection);
			
        	pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, code_id);
			pstmt.setString(paramIdx++, sqlParams.get("TABLE_NM"));
			pstmt.setString(paramIdx++, sqlParams.get("BASE_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("COL_ENG"));
			pstmt.setString(paramIdx++, sqlParams.get("COL_KOR"));
			pstmt.setString(paramIdx++, sqlParams.get("COL_DESC"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			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 doUpdate(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	UPDATE TEST_COM912T SET   	\n");
			sql.append("		 TABLE_NM = NVL(?, TABLE_NM)   	\n");
			sql.append("		, BASE_CD = NVL(?, BASE_CD)   	\n");
			sql.append("		, COL_ENG = NVL(?, COL_ENG)   	\n");
			sql.append("		, COL_KOR = NVL(?, COL_KOR)   	\n");
			sql.append("		, COL_DESC = NVL(?, COL_DESC)   	\n");
			sql.append("		, UPD_DATE = SYSDATE   	\n");
			sql.append("		, UPD_BY = ?   	\n");
			sql.append("	WHERE CODE_ID = ?	\n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("TABLE_NM"));
			pstmt.setString(paramIdx++, sqlParams.get("BASE_CD"));
			pstmt.setString(paramIdx++, sqlParams.get("COL_ENG"));
			pstmt.setString(paramIdx++, sqlParams.get("COL_KOR"));
			pstmt.setString(paramIdx++, sqlParams.get("COL_DESC"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setString(paramIdx++, sqlParams.get("CODE_ID"));

			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 doDelete(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	DELETE FROM TEST_COM912T WHERE CODE_ID = ?		\n ");
            
        	pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("CODE_ID"));
			
			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);
			}
		}
	}
	
	public String getPK(Connection conn) throws WAFSQLException {
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		String sql = "SELECT TEST_COM912T_S.nextval seq FROM dual";
		String pk = "";
		try {
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if(rset.next()) {
			  pk  = rset.getString("seq");
			}
			rset.close();
			pstmt.close();
			return pk;
		}
		catch(SQLException se) {
			WAFLogger.error(se);
			throw new WAFSQLException(se);
		}
		finally {
			try {
				if(rset != null) rset.close();
				if(pstmt != null) pstmt.close();
			}
			catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}
	
}
