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



public class Spoer {

	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;


	public Spoer() {
		connect();
	}


	public void connect(){
		try{
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/treningsdagbok?user=admin&password=123");

		}
		catch (SQLException e){
			System.out.println("SQLException: " + e.getMessage());
		}
	}

	public void hentResultatForTrening(int nr){
		try{
			stmt = conn.createStatement();

			String query1 = "SELECT * FROM resultater where idOevelse = " + String.valueOf(nr);

			if(stmt.execute(query1)){
				rs = stmt.getResultSet();
			}
			while(rs.next()){
				String kolonne1 = rs.getString(1);
				String kolonne2 = rs.getString(2);
				String kolonne3 = rs.getString(3);
				String kolonne4 = rs.getString(4);
				String kolonne5 = rs.getString(5);
				String kolonne6 = rs.getString(6);
				String kolonne7 = rs.getString(7);
				String kolonne8 = rs.getString(8);
				String kolonne9 = rs.getString(9);
				String kolonne10 = rs.getString(10);

				System.out.println(kolonne1 + " - " +
						kolonne2 + " - " +
						kolonne3 + " - " +
						kolonne4 + " - " +
						kolonne5 + " - " +
						kolonne6 + " - " +
						kolonne7 + " - " +
						kolonne8 + " - " +
						kolonne9 + " - " +
						kolonne10);
			}
		}
		catch(SQLException e){
			System.out.println("SQLException: " + e.getMessage());
		}
	}

	public void hentTreningForSistePeriodeMedBestPrestasjon(int antalldager, String type){
		try{
			stmt = conn.createStatement();

			String q = "Select t.idTreningsoekt, t.Tidspunkt, t.Varighet, t.Luft, t.Antall_tilskuere, t.Temperatur,t.Vaer, t.Sted, r.Prestasjon from treningsoekt as t, oevelse as o, resultater as r "
					+ "where (t.tidspunkt > date_sub(now(), interval " + antalldager + " day) "
					+ "and (o.Type = '"+ type +"') "
					+ "and (r.idTreningsoekt = t.idTreningsoekt) "
					+ "and (r.idOevelse = o.idOevelse)) "
					+ "order by r.prestasjon desc ;";

			if(stmt.execute(q)){
				rs = stmt.getResultSet();
			}

			while(rs.next()){
				String kolonne1 = rs.getString(1);
				String kolonne2 = rs.getString(2);
				String kolonne3 = rs.getString(3);
				String kolonne4 = rs.getString(4);
				String kolonne5 = rs.getString(5);
				String kolonne6 = rs.getString(6);
				String kolonne7 = rs.getString(7);
				String kolonne8 = rs.getString(8);
				String kolonne9 = rs.getString(9);

				System.out.println(kolonne1 + " - " +
						kolonne2 + " - " +
						kolonne3 + " - " +
						kolonne4 + " - " +
						kolonne5 + " - " +
						kolonne6 + " - " +
						kolonne7 + " - " +
						kolonne8 + " - " +
						kolonne9 + " - ");
			}
		}

		catch(SQLException e){
			System.out.println("SQLException: " + e.getMessage());
		}
	}

	public void hentStatistikkForSisteMaaned(){
		try{
			stmt = conn.createStatement();

			String q = "select sum(t.Varighet) as antallTimer, count(t.idTreningsoekt) as antallOekter, sum(t.Antall_tilskuere) as tilskuere "
					+ "from treningsoekt as t "
					+ "where(t.tidspunkt > date_sub(now(), interval 31 day));";

			if(stmt.execute(q)){
				rs = stmt.getResultSet();
			}

			while(rs.next()){
				String kolonne1 = rs.getString(1);
				String kolonne2 = rs.getString(2);
				String kolonne3 = rs.getString(3);


				System.out.println(rs.getMetaData().getColumnName(1)+ ": " + kolonne1 + " - " +
						rs.getMetaData().getColumnName(2) + ": " + kolonne2 + " - " +
						rs.getMetaData().getColumnName(3) + ": " +kolonne3);
			}

		}

		catch(SQLException e){
			System.out.println("SQLException: " + e.getMessage());
		}
	}



}