package com.chapter04;

import java.sql.*;
import java.util.*;

import com.servlet.*;


public class MemberCtrl {
	 private DBConnectionMgr pool = null;
	 private Connection con = null;
	 private PreparedStatement pstmt = null;
	 private StringBuffer query = null;
	 
	 private Statement stmt = null;
	 private ResultSet rs = null;
	 /*
	  * ȸ¿øÁ¤º¸ ÀÔ·Â ±¸Çö
	  * @param : MemberVO vo
	  * @return : count
	  */ 
	 public int insertDB(MemberVO uiVO){
		 int count = 0;
		 int i = 0;
		 query = new StringBuffer();
		 query.append("INSERT INTO member values(?,?,?,?,?)");
		 pool = DBConnectionMgr.getInstance();
		 try {
			 con = pool.getConnection();
			 pstmt = con.prepareStatement(query.toString());
			 pstmt.setString(++i,uiVO.getMem_id());
			 pstmt.setString(++i,uiVO.getMem_pw());
			 pstmt.setString(++i,HangulConversion.toKor(uiVO.getMem_name()));
			 pstmt.setString(++i,uiVO.getMem_email());
			 pstmt.setString(++i,uiVO.getMem_tel());
			 count = pstmt.executeUpdate();
		 } catch(SQLException s) {
		   System.out.println("=====================================================");
		   System.out.println(query.toString());
		   System.out.println("=====================================================");
		   System.out.println(s.toString());
		 } catch(Exception e) {
			System.out.println(e.toString());
		 } finally {
			 pool.freeConnection(con,pstmt);
		 }
		   return count;
	 }
	 
	 /*
	  * ȸ¿øÁ¤º¸¸ñ·Ï
	  * @return ArryList //Àüü Á¶È¸À̹ǷΠÄ÷º¼Ç ÇÁ·¹ÀÓÀ» »ç¿ëÇß´Ù 
	  */
	public ArrayList memberList() {
		ArrayList memAList = null;
		pool = DBConnectionMgr.getInstance();
		query = new StringBuffer();
		query.append("SELECT mem_name, mem_id, mem_email, mem_tel");
		query.append(" FROM member");
		memAList = new ArrayList();
		int count = 0;
		try {
			con = pool.getConnection();
			pstmt = con.prepareStatement(query.toString());
			rs = pstmt.executeQuery();
			while(rs.next()) {
				memAList.add(rs.getString(1));
				memAList.add(rs.getString(2));
				memAList.add(rs.getString(3));
				memAList.add(rs.getString(4));
			}
		} catch (SQLException s) {
			System.out.println("====================================");
			System.out.println("query : " + query.toString());
			System.out.println("====================================");
			System.out.println(s.toString());
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			 pool.freeConnection(con,pstmt,rs);
		}
		return memAList; 
	 }
	/*
	 * ȸ¿øÁ¤º¸º¸±â
	 * @param : sel_id(»ç¿ëÀÚ°¡ ¼±ÅÃÇÑ ¾ÆÀ̵ð)
	 * @return : vo(MemberVO´ã¾Æ¼­ ó¸®)
	 */
	public MemberVO memberDetail(String sel_id) {
		MemberVO mVO = new MemberVO();
		query = new StringBuffer();
		query.append("SELECT * FROM member WHERE mem_id = ?");
		//Statement¸¦ ÀÌ¿ëÇÏ´Â °æ¿ì
		//query.append("SELECT * FROM member WHERE mem_id = sel_id");
		
		pool = DBConnectionMgr.getInstance();
		try {
			con = pool.getConnection();
			//Statement¸¦ ÀÌ¿ëÇÏ´Â °æ¿ì
			//stmt = con.createStatement();
			//rs = stmt.executeQuery(query.toString());
			pstmt = con.prepareStatement(query.toString());
			pstmt.setString(1, sel_id);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				/*
				 * µ¥ÀÌÅͺ£À̽º¿¡¼­ Àоî¿Â µ¥ÀÌÅ͸¦ MemberVO¿¡ ´ã´Â´Ù.
				 */
				mVO.setMem_id(rs.getString("mem_id"));
				mVO.setMem_name(rs.getString("mem_name"));
				mVO.setMem_pw(rs.getString("mem_pw"));
				mVO.setMem_email(rs.getString("mem_email"));
				mVO.setMem_tel(rs.getString("mem_tel"));
			}
		} catch (SQLException s) {
			System.out.println("====================================");
			System.out.println("query : " + query.toString());
			System.out.println("====================================");
			System.out.println(s.toString());
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			pool.freeConnection(con,pstmt,rs);
		}
		return mVO;
	}
	
	/*
	 * ȸ¿øÁ¤º¸º¸±â
	 * @param : del_id
	 * @return : count
	 */	
	public int memberDelete(String del_id){
		int count = 0;
		pool = DBConnectionMgr.getInstance();
		query = new StringBuffer();
		query.append("DELETE FROM member WHERE mem_id = ?");
		try {
			con = pool.getConnection();
			pstmt = con.prepareStatement(query.toString());
			pstmt.setString(1,del_id);
			count = pstmt.executeUpdate(); 
		}catch(SQLException s){
			System.out.println("=============================================");
			System.out.println("query:"+query.toString());
			System.out.println("=============================================");
			System.out.println(s.toString());
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally{
			pool.freeConnection(con,pstmt,rs);
		}
		return count;
	}
}