package member;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class member_DB {
	
	private static member_DB instance = new member_DB();
	
	public static member_DB getInstance(){
		return instance;
	}
	public member_DB(){}
	
	private static Connection getConnection() throws Exception{
		Connection con = null;
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
		con = DriverManager.getConnection("jdbc:sqlserver://168.126.146.35:1433;DatabaseName=JNEDB1","20040342","1056624");
		return con;
	}


	public static int userCheck(String me_id, String me_pass) throws Exception
	{
	Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	String dbpass = "";
	int x = -1;

	    try {
	con = getConnection();
	pstmt = con.prepareStatement("SELECT pass FROM member_342 WHERE id = ?");
	pstmt.setString(1, me_id);

	rs = pstmt.executeQuery();

	if (rs.next()) {
	   dbpass = rs.getString("pass");
	   if (dbpass.equals(me_pass)) {
	      x = 1; //¾ÆÀ̵ð¿Í ºñ¹øÀÌ ÀÏÄ¡ÇÒ ¶§
	   }
	   else {
	      x = 0; //¾ÆÀ̵ð´Â ÀÏÄ¡ÇÏ°í ºñ¹øÀÌ ÀÏÄ¡ÇÏÁö ¾ÊÀ» ¶§
	   }
	} 
	else {
	     x = -1; //¾ÆÀ̵𰡠¾øÀ» ¶§
	}
	    
	} 
	    catch (Exception e){}
	    finally {
	 if (rs != null) {
	    try{ rs.close(); }
	    catch(SQLException se){}
	 }
	 if (pstmt != null) {
	    try{ pstmt.close();}
	    catch(SQLException se){}
	 }
	 if (con != null) {
	    try{ con.close();}
	    catch(SQLException se){}
	 }
	    }
	    return x;

	}
	
	
	public static int confirmId(String id) throws Exception{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		int x = -1;

		try{
			con = getConnection();
			pstmt = con.prepareStatement("select id form member_342 where id=?");
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();

			if(rs.next()){
				x=1;
			}
			else {
			x = -1;
			}

			}catch(Exception ex){
			}finally{
				if(rs != null){
				try{rs.close();}
				catch(SQLException ex){}
			}

			if(pstmt != null){
				try{pstmt.close();}
				catch(SQLException ex){}
			}
			if(con != null){
				try{con.close();}
				catch(SQLException ex){}
				}
			}
			return x;
		}
	

	
	public static int insertMember(member_Data member) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		int x = -1; //µî·Ï ½ÇÆÐ

		try{
		con = getConnection();
		sql = "insert into member_342 (id, pass, name, jumin, zip, addr1, addr2, phone, email) values(?,?,?,?,?,?,?,?,?)";
		pstmt = con.prepareStatement(sql);

		pstmt.setString(1, member.getId());
		pstmt.setString(2, member.getPass());
		pstmt.setString(3, member.getName());
		pstmt.setString(4, member.getJumin());
		pstmt.setString(5, member.getZip());
		pstmt.setString(6, member.getAddr1());
		pstmt.setString(7, member.getAddr2());
		pstmt.setString(8, member.getPhone());
		pstmt.setString(9, member.getEmail());

		pstmt.executeUpdate();
		x = 1; // µî·Ï ¼º°ø
		}  //try 

		catch (Exception e){ e.printStackTrace(); }
		finally {
		if (rs != null) {
		   try{ rs.close(); }
		   catch(SQLException se){}
		}
		if (pstmt != null) {
		   try{ pstmt.close();}
		   catch(SQLException se){}
		}
		if (con != null) {
		   try{ con.close();}
		   catch(SQLException se){}
		}
		}
		return x;
		}

}