package com.etecenc.ebid.test.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 TestBiz extends Biz {
	public TestBiz (HandlerStorage storage) {
		super(storage);
	}

	public void doBiz() throws Exception {
		
		if (storage.compareEvent("masterDataList")) {
			params.set("masterSheet", getMasterData());
			storage.addObject("ibsheet7Data", IBSheet7Factory.createIBSheet7(params, storage.request));
			
		} else if (storage.compareEvent("masterDataCUD")) {
			System.out.println(params.get("MASTERSHEET"));
			
			String[] status = super.gets("MASTERSHEET.SSTATUS");
			
			try {
				super.conn.begin();
				
				for (int i = 0; i < status.length; i++) {
					if (IBSheet7Utils.equalStatusInsert(status[i])) {
						createMasterData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("MASTERSHEET", params, i));
					}
					
					if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						updateMasterData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("MASTERSHEET", params, i));
					}
					
					if (IBSheet7Utils.equalStatusDelete(status[i])) {
						deleteMasterData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("MASTERSHEET", params, i));
					}
				}
				
				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();
			}
			
		} else {
			//IBSheet7 시스템 코드 Combo 셋업
			IBSheet7Utils.setIBSheet7Combo("MASTERSHEET", "GENDER", storage);
		}
			
	}
	
	private RowSet getMasterData() {
		Search search = new SearchImpl();
        RowSet rowSet = new RowSetImpl();
        try {
        	search.setStatement("select pid, first_name, last_name, birth, gender, salary from master_tbl");
            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 createMasterData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append(" insert into master_tbl (															\n ");
			sql.append(" 	pid, first_name, last_name, birth, gender, salary, insert_date, update_date		\n ");
			sql.append(" ) values (																			\n ");
			sql.append(" 	?, ?, ?, to_date(?, 'yyyy-mm-dd'), ?, ?, sysdate, sysdate						\n ");
			sql.append(" )																					\n ");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setInt(paramIdx++, sqlParams.getInt("pid"));
			pstmt.setString(paramIdx++, sqlParams.get("first_name"));
			pstmt.setString(paramIdx++, sqlParams.get("last_name"));
			pstmt.setString(paramIdx++, sqlParams.get("birth"));
			pstmt.setString(paramIdx++, sqlParams.get("gender"));
			pstmt.setBigDecimal(paramIdx++, sqlParams.getBigDecimal("salary"));
			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 updateMasterData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append(" update master_tbl 								\n ");
			sql.append(" 	set first_name = ?							\n ");
			sql.append(" 	   ,last_name = ?							\n ");
			sql.append(" 	   ,birth = to_date(?, 'yyyy-mm-dd')		\n ");
			sql.append(" 	   ,gender = ?								\n ");
			sql.append(" 	   ,salary = ?								\n ");
			sql.append(" 	   ,update_date = sysdate					\n ");
			sql.append("  where pid = ?				\n ");
			
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("first_name"));
			pstmt.setString(paramIdx++, sqlParams.get("last_name"));
			pstmt.setString(paramIdx++, sqlParams.get("birth"));
			pstmt.setString(paramIdx++, sqlParams.get("gender"));
			pstmt.setBigDecimal(paramIdx++, sqlParams.getBigDecimal("salary"));
			pstmt.setInt(paramIdx++, sqlParams.getInt("pid"));
			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 deleteMasterData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append(" delete from master_tbl where pid = ?						\n ");
			pstmt = connection.prepareStatement(sql.toString());
			
			pstmt.setInt(1, sqlParams.getInt("pid"));
			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);
			}
		}
	}
}