package com.board;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class BoardDAO {

	
	private Connection conn;
	
	public BoardDAO(Connection conn){
		this.conn = conn;
	}
	
	//1. num의 최대값
	
	public int getMaxNum(){
		
		
		int maxNum = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			
			sql = "select nvl(max(num),0) from board";
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				maxNum = rs.getInt(1);
			}
			rs.close();
			pstmt.close();
			
		} catch (Exception e) {
			System.out.println(e.toString());
		}
		
		return maxNum;
		
	}
	
	// 입력(created.jsp -> created_ok.jsp)
	public int insertData(BoardForm dto){
		int result = 0;

/*		
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		*/
		
		PreparedStatement pstmt = null;
		String sql;
		
		try {
/*	
			sql.append("insert into board");
			sql.append("(num, name, pwd, email, subject, content,");
	*/

			sql = 	"insert into board" +
					"(num, name, pwd, email, subject, content," +
					"ipAddr, hitCount, created) " +
					"values(?, ?, ?, ?, ?, ?, ?, 0, sysdate)";

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getNum());
			pstmt.setString(2, dto.getName());
			pstmt.setString(3, dto.getPwd());
			pstmt.setString(4, dto.getEmail());
			pstmt.setString(5, dto.getSubject());
			pstmt.setString(6, dto.getContent());
			pstmt.setString(7, dto.getIpAddr());
			
			result = pstmt.executeUpdate();
			pstmt.close();
			
		} catch (Exception e) {
			System.out.println("# insertData");
			System.out.println(e.toString());
		}
		return result;
		
	}
	
	// 전체데이터 가지고 올거야
	public List<BoardForm> getList(int start, int end){
		
		List<BoardForm> lists = new ArrayList<BoardForm>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			
			sql  = 	"select * from (";
			
			sql +=	"select rownum rnum,data.* " +
					" from (select num,name,subject,hitCount," +
					" to_char(created, 'YYYY-MM-DD') created" +
					" from board order by num desc) data )" +
					" where rnum >= ? and rnum <= ? ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, start);
			pstmt.setInt(2, end);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				
				BoardForm dto = new BoardForm();
				
				dto.setNum(rs.getInt(2));
				dto.setName(rs.getString(3));
				dto.setSubject(rs.getString(4));
				dto.setHitCount(rs.getInt(5));
				dto.setCreated(rs.getString(6));
				
				lists.add(dto);
				
			}
			
			rs.close();
			pstmt.close();
			
		} catch (Exception e) {
			System.out.println(e.toString());
		}
		
		return lists;
		
	}
	
	//전체 데이터수 구하기
	public int getDataCount(){
		
		int result= 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			
			sql = "select nvl(count(*),0) from board";
			pstmt = conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				result = rs.getInt(1);
			}
			
			rs.close();
			pstmt.close();
			
			
			
		} catch (Exception e) {
			System.out.println(e.toString());
		}
		
		return result;
		
		
	}
	
	//조회수증가
	public int updateHitCount(int num){
	
		int result = 0;
		PreparedStatement pstmt = null;
		String sql;
		
		try {
			sql = "update board set hitCount=hitCount+1 where num=?" ;
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			
			result = pstmt.executeUpdate();
		
			pstmt.close();
			
			
		} catch (Exception e) {
			System.out.println(e.toString());
		}
		
		return result;
		
	}
	
	// 한명의 데이터 출력
	
	public BoardForm getReadData(int num){
		
		BoardForm dto = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			
			// 제목, 작성자, 줄수, 등록일, 조회수, 내용, ip주소
			sql = 	"select num, name, pwd, email, subject, content, ipaddr, created, hitCount " +
					"from board where num=?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				
				dto = new BoardForm();
				
				dto.setNum(rs.getInt("num"));
				dto.setName(rs.getString("name"));
				dto.setPwd(rs.getString("pwd"));
				dto.setEmail(rs.getString("email"));
				dto.setSubject(rs.getString("subject"));
				dto.setContent(rs.getString("content"));
				dto.setIpAddr(rs.getString("ipAddr"));
				dto.setHitCount(rs.getInt("hitCount"));
				dto.setCreated(rs.getString("created"));
				
			}
			
			
			rs.close();
			pstmt.close();
			
			
		} catch (Exception e) {
			System.out.println(e.toString());
		}
		
		return dto;
		
	}
	
	// 삭제
	public int deleteData(int num){
		
		int result = 0;
		PreparedStatement pstmt = null;
		String sql;
		
		try {
			
			sql = "delete board where num=?";
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, num);
			result = pstmt.executeUpdate();
			pstmt.close();
			
			
		} catch (Exception e) {
			System.out.println(e.toString());
		}
		
		return result;
		
	}
	
	// 수정
	public int updateData(BoardForm dto){
		
		int result = 0;
		PreparedStatement pstmt = null;
		String sql;
		
		try {
			sql = "update board set name=?, pwd=?, subject=?, content=?, email=? where num=? ";
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, dto.getName());
			pstmt.setString(2, dto.getPwd());
			pstmt.setString(3, dto.getSubject());
			pstmt.setString(4, dto.getContent());
			pstmt.setString(5, dto.getEmail());
			pstmt.setInt(6, dto.getNum());

			result = pstmt.executeUpdate();
			pstmt.close();
			
		} catch (Exception e) {
			System.out.println(e.toString());
		}
		
		
		return result;
		
		
		
	}
	

	//검색된 데이터수 구하기
	public int getDataCount(String searchKey, String searchValue){
		
		int result= 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			
			searchValue = "%" + searchValue + "%";
			
			sql = "select nvl(count(*),0) from board where "+searchKey + " like ?";
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, searchValue);
			
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				result = rs.getInt(1);
			}
			
			rs.close();
			pstmt.close();
			
			
			
		} catch (Exception e) {
			System.out.println(e.toString());
		}
		
		return result;
		
		
	}
	
	// 검색데이터 가지고 올거야
	public List<BoardForm> getList(int start, int end, String searchKey, String searchValue){
		
		List<BoardForm> lists = new ArrayList<BoardForm>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql;
		
		try {
			searchValue = "%" + searchValue + "%";
			
			sql  = 	"select * from (";
			
			sql +=	"select rownum rnum,data.* " +
					" from (select num,name,subject,hitCount," +
					" to_char(created, 'YYYY-MM-DD') created" +
					" from board where "+searchKey + " like ? order by num desc) data )" +
					" where rnum >= ? and rnum <= ? ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, searchValue);
			pstmt.setInt(2, start);
			pstmt.setInt(3, end);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				
				BoardForm dto = new BoardForm();
				
				dto.setNum(rs.getInt(2));
				dto.setName(rs.getString(3));
				dto.setSubject(rs.getString(4));
				dto.setHitCount(rs.getInt(5));
				dto.setCreated(rs.getString(6));
				
				lists.add(dto);
				
			}
			
			rs.close();
			pstmt.close();
			
		} catch (Exception e) {
			System.out.println(e.toString());
		}
		
		return lists;
		
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}



/////////////////









































