/**
 * @(#)file    ExceptionalUserSaveBiz.java
 * @(#)author  gyeongmo an
 * @(#)date    2014.6.22
 *
 * 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.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 ExceptionalUserSaveBiz extends Biz {

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

	public void doBiz() throws Exception {
		if (storage.compareEvent("SAVE_USER_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])) {
						int result = selectSabun(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
						if(result >= 1) {
							throw new Exception();
						} else {
							success = saveData(super.conn.getConnection(), IBSheet7Utils.getIBSheet7SqlParams("SHEET1", params, i));
						}
					}else if (IBSheet7Utils.equalStatusUpdate(status[i])) {
						success = updateData(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();
				storage.addObject("ibsheet7Data", IBSheet7Utils.exceptionHandle(storage.request, e));
				throw new Exception(e);
				
			} finally {
				super.conn.close();
			}
		}
	}

	private boolean saveData(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try{
			StringBuffer sql = new StringBuffer();
			
			sql.append("	INSERT INTO PUB0130T	\n");
			sql.append("	(                   	\n");
			sql.append("		USER_SABUN        	\n");
			sql.append("		, USER_NAME       	\n");
			sql.append("		, JUMIN_NO        	\n");
			sql.append("		, DUTYC           	\n");
			sql.append("		, BUSEO           	\n");
			sql.append("		, TEL_NO          	\n");
			sql.append("		, HAND_TEL_NO     	\n");
			sql.append("		, PWD_VALID_TO    	\n");
			sql.append("		, PWD_VALID_FROM  	\n");
			sql.append("		, POSITIONS_CODE  	\n");
			sql.append("		, POSITIONS_NAME  	\n");
			sql.append("		, DEPT_CODE       	\n");
			sql.append("		, SEQ       	\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("		,?                	\n");
			sql.append("		,?                	\n");
			sql.append("		,?                	\n");
			sql.append("		,PUB0130T_S.NEXTVAL                	\n");
			sql.append("	)                  	\n");
         	
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("SABUN"));
			pstmt.setString(paramIdx++,	sqlParams.get("NAME"));
			pstmt.setString(paramIdx++,	sqlParams.get("JUMIN"));
			pstmt.setString(paramIdx++,	sqlParams.get("DUTYC"));
			pstmt.setString(paramIdx++,	sqlParams.get("BUSEO"));
			pstmt.setString(paramIdx++,	sqlParams.get("TEL"));
			pstmt.setString(paramIdx++,	sqlParams.get("HPTEL"));
			pstmt.setString(paramIdx++,	sqlParams.get("ENABLEFROM"));
			pstmt.setString(paramIdx++,	sqlParams.get("ENABLETO"));
			pstmt.setString(paramIdx++,	sqlParams.get("P_CODE"));
			pstmt.setString(paramIdx++,	sqlParams.get("P_NAME1"));
			pstmt.setString(paramIdx++,	sqlParams.get("D_CODE"));
			
			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 PUB0130T SET              	\n");
			sql.append("		USER_SABUN = NVL(?, USER_SABUN)	\n");
			sql.append("		, USER_NAME = NVL(?, USER_NAME)	\n");
			sql.append("		, JUMIN_NO = NVL(?, JUMIN_NO)  	\n");
			sql.append("		, DUTYC = ?                    	\n");
			sql.append("		, BUSEO = ?                    	\n");
			sql.append("		, TEL_NO = ?                   	\n");
			sql.append("		, HAND_TEL_NO =?               	\n");
			sql.append("		, PWD_VALID_FROM = ?             	\n");
			sql.append("		, PWD_VALID_TO = ?           	\n");
			sql.append("		, POSITIONS_CODE = ?           	\n");
			sql.append("		, POSITIONS_NAME = ?           	\n");
			sql.append("		, DEPT_CODE = ?                	\n");
			sql.append("	WHERE SEQ = ?             	\n");
			
         	pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			pstmt.setString(paramIdx++, sqlParams.get("SABUN"));
			pstmt.setString(paramIdx++, sqlParams.get("NAME"));
			pstmt.setString(paramIdx++, sqlParams.get("JUMIN"));
			pstmt.setString(paramIdx++, sqlParams.get("DUTYC"));
			pstmt.setString(paramIdx++, sqlParams.get("BUSEO"));
			pstmt.setString(paramIdx++, sqlParams.get("TEL"));
			pstmt.setString(paramIdx++, sqlParams.get("HPTEL"));
			pstmt.setString(paramIdx++, sqlParams.get("ENABLEFROM"));
			pstmt.setString(paramIdx++, sqlParams.get("ENABLETO"));
			pstmt.setString(paramIdx++, sqlParams.get("P_CODE"));
			pstmt.setString(paramIdx++, sqlParams.get("P_NAME1"));
			pstmt.setString(paramIdx++, sqlParams.get("D_CODE"));
			pstmt.setString(paramIdx++, sqlParams.get("SEQ"));
			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 int selectSabun(Connection connection, ValueObject sqlParams) throws Exception {
		PreparedStatement pstmt = null;
		
		try{
			StringBuffer sql = new StringBuffer();
			
			sql.append("	SELECT USER_SABUN FROM PUB0130T	WHERE USER_SABUN = ? \n");
         	
			pstmt = connection.prepareStatement(sql.toString());
			int paramIdx = 1;
			
			pstmt.setString(paramIdx++, sqlParams.get("SABUN").trim());
			int result = pstmt.executeUpdate();
			
			return result;
		} catch (Exception e) {
			WAFLogger.error(e);
			throw new Exception(e);
		} finally {
			try {
				if(pstmt != null) pstmt.close();
			} catch(SQLException se) {
				WAFLogger.error(se);
			}
		}
	}


}
