ATG/ATG/src/database/Horse.java

package database;

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

import com.google.common.base.Strings;

import objects.ResultsDTO;

public class Horse extends Database {

	private static Horse instance = new Horse();

	private Horse() {
	}

	public static Horse getInstance() {
		return instance;
	}

	/**
	 *
	 * H�mta ut kuskens id fr�n namn
	 *
	 * @param name - Str�ng med formatet "LastName firstName"
	 * @return
	 */
	public int getHorseIdByName(String name) {
		int returnValue = -1;
		final String sql = "SELECT id FROM Horse WHERE name = ?";

		try {
			final PreparedStatement stat = getConnection().prepareStatement(sql);
			stat.setString(1, name);

			final ResultSet rs = stat.executeQuery();

			while (rs.next()) {
				returnValue = rs.getInt("id");
			}
		} catch (final SQLException e) {
			e.printStackTrace();
		}

		return returnValue;
	}

	public int getHorseTravsportId(int horseId) {
		int returnValue = -1;
		final String sql = "SELECT travsportId FROM Horse WHERE id = ?";

		try {
			final PreparedStatement stat = getConnection().prepareStatement(sql);
			stat.setInt(1, horseId);

			final ResultSet rs = stat.executeQuery();

			while (rs.next()) {
				returnValue = rs.getInt("id");
			}
		} catch (final SQLException e) {
			e.printStackTrace();
		}

		return returnValue;
	}

	public ResultsDTO avarageTime(int horseId, String date) {
		return avarageTimeByDistance(horseId, 0, 0, date);
	}

	public ResultsDTO avarageTimeByDistance(int horseId, int distance, String date) {
		return avarageTimeByDistance(horseId, distance, 0, date);
	}

	public ResultsDTO avarageTimeWithLimit(int horseId, int raceLimit, String date) {
		return avarageTimeByDistance(horseId, 0, raceLimit, date);
	}

	public ResultsDTO avarageTimeByDistance(int horseId, int distance, int raceLimit, String date) {
		final ResultsDTO returnValue = new ResultsDTO();

		final String d = Strings.isNullOrEmpty(date) ? "NOW()" : date;

		final String sql = "SELECT ROUND(AVG(avgRes.Time), 2) as avgTime, count(*) as num FROM "
				+ "(SELECT Time as Time FROM Results WHERE Time > 0 AND Lane > 0 AND RaceDate < ? AND HorseId = ?";
		final String distanceSql = " AND distance BETWEEN ? AND ?";
		final String limitSql = " LIMIT ?";
		final String endingSql = ") avgRes";
		final String orderSql = " ORDER BY RaceDate DESC";

		final StringBuilder sb = new StringBuilder();
		sb.append(sql);

		boolean orderAppended = false;
		if (distance > 0) {
			sb.append(distanceSql);
			sb.append(orderSql);
			orderAppended = true;
		}
		if (raceLimit > 0) {
			if (!orderAppended) {
				sb.append(orderSql);
				orderAppended = true;
			}
			sb.append(limitSql);
		}

		if (!orderAppended) {
			sb.append(orderSql);
		}
		sb.append(endingSql);

		try {
			final PreparedStatement stat = getConnection().prepareStatement(sb.toString());
			stat.setString(1, d);
			stat.setInt(2, horseId);
			if (distance > 0) {
				stat.setInt(3, distance - 50);
				stat.setInt(4, distance + 50);
			}
			if (distance > 0 && raceLimit > 0) {
				stat.setInt(5, raceLimit);
			} else if (distance <= 0 && raceLimit > 0) {
				stat.setInt(3, raceLimit);
			}

			final ResultSet rs = stat.executeQuery();

			while (rs.next()) {
				returnValue.setRes(rs.getFloat("avgTime"));
				returnValue.setCount(rs.getInt("num"));
			}
		} catch (final SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}

		return returnValue;
	}

	public ResultsDTO avaragePlacement(int horseId, String date) {
		return avaragePlacementByDistance(horseId, 0, 0, date);
	}

	public ResultsDTO avaragePlacementByDistance(int horseId, int distance, String date) {
		return avaragePlacementByDistance(horseId, distance, 0, date);
	}

	public ResultsDTO avaragePlacementWithLimit(int horseId, int raceLimit, String date) {
		return avaragePlacementByDistance(horseId, 0, raceLimit, date);
	}

	public ResultsDTO avaragePlacementByDistance(int horseId, int distance, int raceLimit, String date) {
		final ResultsDTO returnValue = new ResultsDTO();

		final String d = Strings.isNullOrEmpty(date) ? "NOW()" : date;

		final StringBuilder sb = new StringBuilder();
		final String sql = "SELECT ROUND(avg(" + "CASE " + "WHEN Result = -1 THEN 9 WHEN Result = -2 THEN 10 "
				+ "WHEN Result = 0 THEN 8 ELSE Result END"
				+ "), 2) as avgResult, count(*) as num FROM (SELECT Result FROM Results WHERE RaceDate < DATE(?) AND HorseId = ?";
		final String distSql = " AND distance BETWEEN ? AND ?";
		final String limitSql = " limit ?";
		final String orderBySql = " ORDER BY RaceDate DESC";

		sb.append(sql);
		boolean orderAppended = false;
		if (distance > 0) {
			sb.append(distSql);
			sb.append(orderBySql);
			orderAppended = true;
		}
		if (raceLimit > 0) {
			if (!orderAppended) {
				sb.append(orderBySql);
				orderAppended = true;
			}
			sb.append(limitSql);
		}

		if (!orderAppended) {
			sb.append(orderBySql);
		}

		sb.append(") avgRes");

		try {
			final PreparedStatement stat = getConnection().prepareStatement(sb.toString());
			stat.setString(1, d);
			stat.setInt(2, horseId);
			if (distance > 0) {
				stat.setInt(3, distance - 50);
				stat.setInt(4, distance + 50);
			}
			if (distance > 0 && raceLimit > 0) {
				stat.setInt(5, raceLimit);
			} else if (distance <= 0 && raceLimit > 0) {
				stat.setInt(3, raceLimit);
			}

			final ResultSet rs = stat.executeQuery();

			while (rs.next()) {
				returnValue.setRes(rs.getFloat("avgResult"));
				returnValue.setCount(rs.getInt("num"));
			}
		} catch (final SQLException e) {
			e.printStackTrace();
			System.out.println(sb.toString());
		}

		return returnValue;
	}

	public String getNameFromId(int horseId) {
		String result = "";
		String sql = "SELECT name FROM Horse WHERE id = ?";

		try (PreparedStatement stat = getConnection().prepareStatement(sql)) {
			stat.setInt(1, horseId);
			ResultSet rs = stat.executeQuery();

			while (rs.next()) {
				result = rs.getString("name");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}