/*
 * Toegangssysteem database functies.
 *
 */

package outfit;

import java.sql.Timestamp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

/**
 *
 * @author itopia
 */
public class ToegangsLog {

    private Connection conn = null;
    private static final String dbUser = "yftc_user";
    private static final String dbPassword = "YFTCnov09";
    private String url;

    /**
     * Connectie met de database.
     * Maak zelf methoden om gegevens in de database te stoppen en eruit te halen.
     * @return
     */
    public Connection getConnection() {
        // Loading driver
        try {
            url = "jdbc:mysql://oege.ie.hva.nl:3306/yourfit_tc";
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, dbUser, dbPassword);
        } catch (ClassNotFoundException cnfex) {
            cnfex.printStackTrace();
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
        } catch (Exception excp) {
            excp.printStackTrace();
        }
        return conn;
    }

    /**
     * test database connectie en enkele SQL queries
     */
    private void testDB() {
        Statement stmt;
        ResultSet rs;
        conn = getConnection();
        //Display URL and connection information
        System.out.println("URL: " + url);
        System.out.println("Connection: " + conn);
        try {
            //Get a Statement object
            stmt = conn.createStatement();
            // Insert an extra row
            java.sql.Timestamp now = new Timestamp((new Date()).getTime());
            PreparedStatement ps = conn.prepareStatement(
                "INSERT INTO kluis_logboek (datum_in, naam, datum_uit, pascode)"+
                 " VALUES(?,?,?,null)" );
            ps.setTimestamp(1, now);
            ps.setString(2, "JavaTest");
            Timestamp later = new Timestamp(now.getTime()+ 3600*1000);
            ps.setTimestamp(3, later);  // een uur later
            // execute the insert query
            ps.executeUpdate();

            // Get all the records from the table
            rs = stmt.executeQuery("SELECT * " +
                    "FROM kluis_logboek ORDER BY datum_in");
            while (rs.next()) {
                java.sql.Timestamp din = rs.getTimestamp("datum_in");
                java.sql.Timestamp duit = rs.getTimestamp("datum_uit");
                System.out.println("Datum in: "+din + "\tUit: "+duit);
                int pas = rs.getInt("pascode");
                String s = rs.getString("naam");
                System.out.println("pas= " + pas + " naam=" + s);
            }//end while loop
        } catch (SQLException ex) {
            ex.printStackTrace();
            return;
        }
    }

    /**
     * Testmethode om database toegang standalone te testen (run deze file)
     * @param args
     */
    public static void main(String[] args) {
        new ToegangsLog().testDB();
    }
}