/* Java libraries */
import java.sql.*;
import java.util.*;
import java.io.*;

public class uke9 {


/************* Utility methods ***************/


    static void warn(String msg, Exception e) {
	System.err.println( msg );
	if ( e != null ) {
	    System.err.println( "Fikk unntak: " + e.toString() );
	    System.err.println( "Melding: " + e.getMessage() );
	    System.err.println( "Traceback: " );
	    e.printStackTrace(System.err);
	}
    }

    static void die(String msg, Exception e) {
	warn(msg, e);
	System.exit(1);
    }


/* **************** Connecting to Postgres ************* */


    public static Connection openConnection() {

	Connection con = null;

	// Load the JDBC driver (if there is one) 
	try {
	    Class.forName("org.postgresql.Driver");
	}
	catch(Exception e) {
	    die( "Exception: JDBC driver not found", e);
	}

	// Create a properties object with username and password

	Properties props = new Properties();
	props.put( "user", "ditt_brukernavn" );
	props.put( "password", "ditt_passord" );

	// Set the JDBC URL
	String url = "jdbc:postgresql://kurspg/ditt_databasenavn";

	// Get the connection to the Postgres DBMS

	try {
	    con = DriverManager.getConnection( url, props );
	}
	catch(Exception e) {
	    die("Wrong DB-URL, username or password", e);
	}

	return con;
    }


/**************************************************************
*                       MAIN PROGRAM                          *
**************************************************************/


/* Dette er et veldig enkelt lite program som ikke gjør annet 
enn å på enklest mulig måte utføre de tre sql-setningene som 
løser uke 9s oppgave 1. */

    public static void main(String argv[]) throws Exception {

	ResultSet res = null;
	Statement stmt = null;
	ResultSetMetaData rsmd = null;
	int colCount = 0;
	int ant = 0;


	String query_a = null;
	String query_b = null;
	String insert_c = null;

/* query/insert-strings */

/* Oppgave 1 a) Finn antall ulike lønnsnivåer i hver avdeling, sortert etter antall nivåer */

	query_a = "select avd.anavn, count (distinct a.lønn) ";
	query_a += "from ansatt a, avdeling avd ";
	query_a += "where a.avdnr = avd.avdnr ";
	query_a += "group by avd.anavn ";
	query_a += "order by count (distinct a.lønn) desc";

/* Oppgave 1 b) For hvert prosjekt, list antall ansatte fra avdelingen med navn Utvikling */

	query_b = "select pl.pnr, p.pnavn, count(pl.anr) ";
	query_b += "from prosjekt p, prosjektplan pl, ansatt a, avdeling avd ";
	query_b += "where p.pnr = pl.pnr ";
	query_b += "and pl.anr = a.anr ";
	query_b += "and avd.avdnr = a.avdnr ";
	query_b += "and avd.anavn like \'Utvikling\' ";
	query_b += "group by pl.pnr, p.pnavn";

/* Oppgave 1 c) Registrer at Kåre Hansen nå jobber på prosjektet Aksjebørs, og hittil har jobbet 15 timer på prosjektet */

	insert_c = "insert into prosjektplan (pnr, anr, timer) ";
	insert_c += "values ( ";
	insert_c += "(select pnr from prosjekt where pnavn like 'Aksjebørs'), ";
	insert_c += "(select anr from ansatt where navn like 'Kåre Hansen'), ";
	insert_c += "15)";


/* Load Postgres driver and open connection to the database */

        Connection con = openConnection();

/* Oppgave 1 a) */

	System.out.println();
	System.out.println("Oppgave 1 a):");
	System.out.println("-------------");
	System.out.println();

	try {
	    stmt = con.createStatement();
	    res = stmt.executeQuery(query_a);
	}
	catch (Exception e) {
	    warn("Exception in SQL-query ", e);
	}

	rsmd = res.getMetaData();
	colCount = rsmd.getColumnCount();

	for (int i = 1; i <= colCount; i++) {
	    System.out.print(rsmd.getColumnLabel(i) + "\t");
	}

	System.out.println();

	while(res.next()) {
	    for(int i = 1; i<=colCount; i++) {
		System.out.print(res.getString(i) + "\t");
	    }
	    System.out.println();
	}
	System.out.println();


/* Oppgave 1 b) */

	System.out.println();
	System.out.println("Oppgave 1 b):");
	System.out.println("-------------");
	System.out.println();

	try {
	    stmt = con.createStatement();
	    res = stmt.executeQuery(query_b);
	}
	catch (Exception e) {
	    warn("Exception in SQL-query ", e);
	}

	rsmd = res.getMetaData();
	colCount = rsmd.getColumnCount();

	for (int i = 1; i <= colCount; i++) {
	    System.out.print(rsmd.getColumnLabel(i) + "\t");
	}

	System.out.println();

	while(res.next()) {
	    for(int i = 1; i<=colCount; i++) {
		System.out.print(res.getString(i) + "\t");
	    }
	    System.out.println();
	}
	System.out.println();


/* Oppgave 1 c) */
	
	System.out.println();
	System.out.println("Oppgave 1 c):");
	System.out.println("-------------");
	System.out.println();

	try {
	    stmt = con.createStatement();
	    ant = stmt.executeUpdate(insert_c);
	}
	catch (Exception e) {
	    warn("Exception in SQL-insert ", e);
	}

	System.out.println("Rader satt inn: " + ant);
	System.out.println();

	con.close();
	res.close();
	stmt.close();
	   
    }
}