package com.score.dao;

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

import org.springframework.stereotype.Repository;

import com.score.dao.JDBCUtil;
import com.score.bean.ScoreVO;

// @Repository
// - DAO Class에 사용
// - database 에 접근하는 메서드를 가지고 있는 Class에 사용.

@Repository
public class ScoreDAO {
	
	private Connection con;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	private final String SCORE_INSERT = "INSERT INTO SCORE VALUES (?,?,?,?,?,?,?,sysdate)";
	private final String SCORE_LIST = "SELECT * FROM SCORE ORDER BY NO ASC";

	// 성적 입력 
	public int insertScore(ScoreVO vo) {
		int su = 0;
		int tot = 0;
		double avg = 0;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(SCORE_INSERT);
			tot = vo.getKor()+vo.getEng()+vo.getMat();
			avg = tot/3.0;
			pstmt.setString(1, vo.getNo());
			pstmt.setString(2, vo.getName());
			pstmt.setInt(3, vo.getKor());
			pstmt.setInt(4, vo.getEng());
			pstmt.setInt(5, vo.getMat());
			pstmt.setInt(6, tot);
			pstmt.setDouble(7, avg);
			su = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(rs, pstmt, con);
		}
		return su;
	}
	
	// 도서 목록
		public List<ScoreVO> getScoreList(ScoreVO vo) {
			List<ScoreVO> list = null;
			
			try {
				list = new ArrayList<ScoreVO>();
				con = JDBCUtil.getConnection();
				pstmt = con.prepareStatement(SCORE_LIST);
				rs = pstmt.executeQuery();
				while(rs.next()) {
					vo = new ScoreVO();
					vo.setNo(rs.getString("NO"));
					vo.setName(rs.getString("NAME"));
					vo.setKor(rs.getInt("KOR"));
					vo.setEng(rs.getInt("ENG"));
					vo.setMat(rs.getInt("MAT"));
					vo.setTot(rs.getInt("TOT"));
					vo.setAvg(rs.getInt("AVG"));
					vo.setLogtime(rs.getString("LOGTIME"));
					list.add(vo);
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JDBCUtil.close(rs, pstmt, con);
			}
			
			return list; 
		}
}