package jp.yoyoyoyo.webformmanager.dao;

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

import javax.sql.DataSource;

import jp.yoyoyoyo.webformmanager.dao.DaoUtil;
import jp.yoyoyoyo.webformmanager.model.RequestData;

public class RequestDataDAO {
	// データ一覧を検索するSQL
	private static final String SELECT = "select * from REQUEST_DATA";
	
	private static final String SELECT_BY_ID = "select * from REQUEST_DATA where SEQ_ID = ?";
	
	private static final String SELECT_FOR_CSV = "select * from REQUEST_DATA where REQUEST_DATE > ? and REQUEST_DATE < ?";
	
	private DataSource source;
	
	public RequestDataDAO(){
		source = DaoUtil.getSource();
	}
	
	public RequestData getRequestDataByID(int id) throws SQLException{
		Connection conn = source.getConnection();
		ResultSet rs = null;
		
		// ステートメントを作成
	    PreparedStatement stmt = conn.prepareStatement(SELECT_BY_ID); 
	    try{
	    	
	    	stmt.setInt(1, id);
	    	rs = stmt.executeQuery();
	    	
	    	if(rs.next()){
	    		return getRequestData(rs);
	    	}
	    	
	    } catch (SQLException e){
	    	throw e;
	    } finally {
	    
	    	if(rs != null){
	    		rs.close();
	    	}
	    	
	    	if(stmt != null){
	    		stmt.close();
	    	}
	    	
	    	if(conn != null){
	    		conn.close();
	    	}
	    }
		
	    return null;
	}

	public List<RequestData> getRequestData(String where) throws SQLException {
		List<RequestData> list = new ArrayList<RequestData>();
		
		String sql = SELECT + where + " order by REQUEST_DATE desc";
		
		Connection conn = source.getConnection();
		ResultSet rs = null;
		
		// ステートメントを作成
	    Statement stmt = conn.createStatement(); 
	    try{
	    	rs = stmt.executeQuery(sql);
	    	
	    	while(rs.next()){
	    		list.add(getRequestData(rs));
	    	}
	    	
	    } catch (SQLException e){
	    	throw e;
	    } finally {
	    
	    	if(rs != null){
	    		rs.close();
	    	}
	    	
	    	if(stmt != null){
	    		stmt.close();
	    	}
	    	
	    	if(conn != null){
	    		conn.close();
	    	}
	    }
		
	    return list;
	}
	
	public List<RequestData> getRequestData(String fromDate, String toDate) throws SQLException {
		List<RequestData> list = new ArrayList<RequestData>();
		
		Connection conn = source.getConnection();
		ResultSet rs = null;
		
		// ステートメントを作成
	    PreparedStatement stmt = conn.prepareStatement(SELECT_FOR_CSV + " order by REQUEST_DATE desc"); 
		
	    try{
	    	stmt.setString(1, fromDate);
	    	stmt.setString(2, toDate);
	    	
	    	rs = stmt.executeQuery();
	    	
	    	while(rs.next()){
	    		list.add(getRequestData(rs));
	    	}
	    	
	    } catch (SQLException e){
	    	throw e;
	    } finally {
	    
	    	if(rs != null){
	    		rs.close();
	    	}
	    	
	    	if(stmt != null){
	    		stmt.close();
	    	}
	    	
	    	if(conn != null){
	    		conn.close();
	    	}
	    }
		
	    return list;
	}
	
	private RequestData getRequestData(ResultSet rs) throws SQLException {
		RequestData request = new RequestData();
		
		request.setId(rs.getInt("SEQ_ID"));
		request.setSeiKanji(rs.getString("SEI_KANJI"));
		request.setMeiKanji(rs.getString("MEI_KANJI"));
		request.setSeiKana(rs.getString("SEI_KANA"));
		request.setMeiKana(rs.getString("MEI_KANA"));
		request.setBirthDate(rs.getString("BIRTH_DATE"));
		request.setGender(rs.getInt("GENDER"));
		request.setGenderText(rs.getString("GENDER_TEXT"));
		request.setZip1(rs.getString("ZIP1"));
		request.setZip2(rs.getString("ZIP2"));
		request.setAddress1(rs.getString("ADDRESS1"));
		request.setAddress2(rs.getString("ADDRESS2"));
		request.setAddress3(rs.getString("ADDRESS3"));
		request.setAddress4(rs.getString("ADDRESS4"));
		request.setTel1(rs.getString("TEL1"));
		request.setTel2(rs.getString("TEL2"));
		request.setTel3(rs.getString("TEL3"));
		request.setmTel1(rs.getString("M_TEL1"));
		request.setmTel2(rs.getString("M_TEL2"));
		request.setmTel3(rs.getString("M_TEL3"));
		request.setEmail(rs.getString("E_MAIL"));
		request.setOccupation(rs.getInt("OCCUPATION"));
		request.setOccupationText(rs.getString("OCCUPATION_TEXT"));
		request.setRequestDate(rs.getTimestamp("REQUEST_DATE"));
		request.setIpAddress(rs.getString("IP_ADDRESS"));
		request.setSessionId(rs.getString("SESSION_ID"));
		request.setStatus(rs.getInt("STATUS"));
		request.setStatusText(rs.getString("STATUS_TEXT"));
		
		return request;
	}
}
