/**
 * @(#)file    SYSRightListCodeBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.6.20
 *
 * Copyright (c) www.udapsoft.co.kr, Inc.
 * All rights reserved.
 */
package kr.co.udapsoft.ebid.buyer.sycs.security.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.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 SYSRightListCodeBiz extends Biz{

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

	public void doBiz() throws Exception {
		if (storage.compareEvent("")) {
			RowSet rowSet = getRowSet();
			super.storage.addRowSet("ROWSET", rowSet);
		} else if (storage.compareEvent("GET_SYS_LIST")) {
			params.set("sheet1", getUserAuthorityList());
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
		} else if (storage.compareEvent("SAVE_SYS_LIST")) {
			
			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 = saveData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
					
					if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						success = updateData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
					}
					
					if (IBSheet7Utils.equalStatusDelete(status[i])) {
						success = deleteData(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 RowSet getRowSet() {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlstr = new StringBuffer();
        try {
        	sqlstr.append("	SELECT   ACCT_TYPE_NAME, ACCT_TYPE_ID	\n");
        	sqlstr.append("	FROM     PUB0110T	\n");
        	
        	search.setStatement(sqlstr);
            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();
            return rowSet;
        }
    }
	
	private RowSet getUserAuthorityList() {
        Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        StringBuffer sqlstr = new StringBuffer();
        try {
        	sqlstr.append("	SELECT                               		\n");
        	sqlstr.append("		A.ACCT_TYPE_ID                     		\n");
        	sqlstr.append("		, A.RIGHT_ID                       		\n");
        	sqlstr.append("		, A.RIGHT_NAME                     		\n");
        	sqlstr.append("		, A.DSCR                           		\n");
        	sqlstr.append("		, F_GET_USER_NM(A.CRE_BY) CRE_BY                         		\n");
        	sqlstr.append("		, A.CRE_DATE                       		\n");
        	sqlstr.append("		, F_GET_USER_NM(A.UPD_BY) UPD_BY                         		\n");
        	sqlstr.append("		, A.UPD_DATE                       		\n");
        	sqlstr.append("	FROM PUB0340T A, PUB0110T B          		\n");
        	sqlstr.append("	WHERE A.ACCT_TYPE_ID = B.ACCT_TYPE_ID		\n");
        	sqlstr.append("	ORDER BY RIGHT_ID                    		\n");

        	search.setStatement(sqlstr);
            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();
            return rowSet;
        }
	}
	
	private boolean saveData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	INSERT INTO PUB0340T			\n ");
			sql.append("	(										\n ");
			sql.append("		RIGHT_ID					\n ");
			sql.append("		, RIGHT_NAME			\n ");
			sql.append("		, DSCR						\n ");
			sql.append("		, ACCT_TYPE_ID		\n ");
			sql.append("		, USE							\n ");
			sql.append("		, CRE_BY					\n ");
			sql.append("		, CRE_DATE				\n ");
			sql.append("		) VALUES (				\n ");
			sql.append("		PUB0340T_S.NEXTVAL			\n ");
			sql.append("		,?											\n ");
			sql.append("		,?											\n ");
			sql.append("		,?											\n ");
			sql.append("		,'use'									\n ");
			sql.append("		,?											\n ");
			sql.append("		,TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')	\n ");
			sql.append("		)												\n ");
			
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("RIGHT_NAME"));
			pstmt.setString(paramIdx++, sqlParams.get("DSCR"));
			pstmt.setString(paramIdx++, sqlParams.get("ACCT_TYPE_ID"));
			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 updateData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	UPDATE PUB0340T SET   	\n");
			sql.append("		RIGHT_NAME = ?        	\n");
			sql.append("		, DSCR = ?   	\n");
			sql.append("		, ACCT_TYPE_ID = ?   	\n");
			sql.append("		, USE = 'use'   	\n");
			sql.append("		, UPD_BY = ?          	\n");
			sql.append("		, UPD_DATE = TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')	\n");
			sql.append("	WHERE RIGHT_ID = ?	\n");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("RIGHT_NAME"));
			pstmt.setString(paramIdx++, sqlParams.get("DSCR"));
			pstmt.setString(paramIdx++, sqlParams.get("ACCT_TYPE_ID"));
			pstmt.setInt(paramIdx++, storage.getUser().getInt("ACCT_ID"));
			pstmt.setInt(paramIdx++, sqlParams.getInt("RIGHT_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 deleteData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("	DELETE FROM PUB0340T WHERE RIGHT_ID = ?			\n ");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setInt(paramIdx++, sqlParams.getInt("RIGHT_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);
			}
		}
	}
	
	
}


