package de.tum.in.www_db.UniVerwaltung_wsdl;

import java.math.BigInteger;
import java.sql.*;
import java.util.LinkedList;

import javax.sql.DataSource;
import javax.naming.InitialContext;


public class InquireDB {

  // insert DB-connection information here:
  private static String driver = "...";

  private static String conURL = "...";

  private static String user = "...";

  private static String pwd = "...";

  /**
   * create a connection to the uni-database and inquire the amount of lectures
   * for a specified professor
   * 
   * exception-handling is skipped for keeping the example concise
   * 
   * @param profName
   *          the name of the professor
   * @return the amount of lectures
   */
  public static int getLehrUmfangVonProfessor(String profName) {
    int sumsws = 0;

    try {
      // connect to database:
      Class.forName(driver);
      Connection conn = DriverManager.getConnection(conURL, user, pwd);

      // post query:
      PreparedStatement stmt = conn
          .prepareStatement("SELECT SUM( v.SWS ) AS SUMSWS "
              + "FROM uni.Vorlesungen v, uni.Professoren p "
              + "WHERE v.gelesenVon = p.PersNr " + "AND p.Name = ?");
      stmt.setString(1, profName);
      ResultSet rset = stmt.executeQuery();
      rset.next();
      sumsws = java.lang.Integer.parseInt(rset.getString("SUMSWS"));
      // disconnect
      rset.close();
      stmt.close();
      conn.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return sumsws;
  }

  /**
   * connect to the uni-sample database and insert a new tuple, representing
   * that a student attends a certain lecture
   * 
   * @param sName
   *          the student's name
   * @param vTitel
   *          the title of the lecture
   */
  public static void setHoertVorlesung(String sName, String vTitel) {
    try {
      // connect to database:
      Class.forName(driver);
      Connection conn = DriverManager.getConnection(conURL, user, pwd);

      // post query:
      PreparedStatement stmt = conn.prepareStatement("insert into hören "
          + "select s.MatrNr, v.VorlNr " + "from Vorlesungen v, Studenten s "
          + "where v.Titel =? " + "and s.Name=?");
      stmt.setString(1, vTitel);
      stmt.setString(1, sName);
      stmt.execute();
      // disconnect
      stmt.close();
      conn.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  /**
   * determines the list of lectures held by a certain professor
   * 
   * @param profName
   *          the professor's name
   * @return the lectures list
   */
  public static VorlesungslistenTyp getVorlesungslisteVonProfessor(
      String profName) {
    VorlesungslistenTyp list = new VorlesungslistenTyp();
    LinkedList<VorlesungslistenTypVorlesung> vorlesungen = new LinkedList<VorlesungslistenTypVorlesung>();

    try {
      // connect to database:
      Class.forName(driver);
      Connection conn = DriverManager.getConnection(conURL, user, pwd);

      // post query:
      PreparedStatement stmt = conn
          .prepareStatement("select v.VorlNr, v.Titel, v.SWS "
              + "from Vorlesungen v, Professoren p "
              + "where v.gelesenVon = p.PersNr " + "and p.Name = ?");
      stmt.setString(1, profName);
      ResultSet rset = stmt.executeQuery();
      while (rset.next()) {
        VorlesungslistenTypVorlesung vorl = new VorlesungslistenTypVorlesung();
        vorl.setSWS(new BigInteger(rset.getString("SWS")));
        vorl.setTitel(rset.getString("Titel"));
        vorl.setVorlNr(new BigInteger(rset.getString("VorlNr")));
        vorlesungen.add(vorl);
      }
      VorlesungslistenTypVorlesung[] vorlArr = new VorlesungslistenTypVorlesung[vorlesungen
          .size()];
      int i = 0;
      for (VorlesungslistenTypVorlesung v : vorlesungen) {
        vorlArr[i] = v;
        ++i;
      }
      list.setVorlesung(vorlArr);
      // disconnect
      stmt.close();
      conn.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return list;
  }

}