// Programm 7-8a: BSP_ROOT/meinewebapp/WEB-INF/classes/meinebeans/WarenkorbBean.java
package meinebeans;
import javax.servlet.http.*;
import java.sql.*;
public class WarenkorbBean {
  HttpSession session;            // aktuelle HTTP-Session
  String treiber = "org.gjt.mm.mysql.Driver";
  String jdbcurl = "jdbc:mysql://localhost:3306/jdbcbuch";
  Connection c;  Statement s;  ResultSet r;
  public WarenkorbBean() {              // Standardkonstruktor
    super();
    try {
      Class.forName(treiber);
      c = DriverManager.getConnection(jdbcurl);
      s = c.createStatement();
    }
    catch (Exception ex) {};
  }
  public void setGruppen(String spaltenname) {
    String sql = "SELECT DISTINCT " + spaltenname + 
                 " FROM Katalog ORDER BY " + spaltenname;
    try { r = s.executeQuery(sql); }
    catch (Exception ex) {};
  }
  public String getGruppe() {
    try {
      if (r.next()) return r.getString(1);
      else { r.close(); return ""; }
    }
    catch (Exception ex) { return ""; };
  }
  public void setArtikels(String gruppe) {
    String sql = "SELECT * FROM Katalog " +
                 "WHERE artikelgrp LIKE '" + gruppe + "'ORDER BY bezeichnung";
    try { r = s.executeQuery(sql); }
    catch (Exception ex) {};
  }
  public String getArtikel(String spaltenname, boolean weiter) {
    try {
      if (weiter)
        if (r.next())
          return r.getString(spaltenname);
        else { r.close(); return ""; }
      else return r.getString(spaltenname);
    }
    catch (Exception ex) { return ""; };
  }
  public void setWarenkorb(String warenkorbid) {
    String sql = "SELECT * FROM Warenkorb,Katalog " +
                 "WHERE kundennr LIKE '" + warenkorbid + 
                 "' AND Warenkorb.artikelnr=Katalog.artikelnr ORDER BY datum";
    try { r = s.executeQuery(sql); }
    catch (Exception ex) {};
  }
  public String getItem(String spaltenname, boolean weiter) {
    try {
      if (weiter)
        if (r.next())
          return r.getString(spaltenname);
        else { r.close(); return ""; }
      else return r.getString(spaltenname);
    }
    catch (Exception ex) { return ""; };
  }
  public void setItem(int artikelnr, double menge) {
    String sql = "INSERT INTO Warenkorb(kundennr, artikelnr, datum, menge) " +
                 "VALUES('" + (String) getWarenkorbId() + "'," + artikelnr +
                 ",'" + (new java.util.Date()) + "'," + menge + ")";
    try { s.execute(sql); }
    catch (Exception ex) {
      sql = "UPDATE Warenkorb SET datum='" + (new java.util.Date()) + "', " +
            "menge=" + menge + " " +
            "WHERE kundennr LIKE '" +
            (String) getWarenkorbId() + 
            "' AND artikelnr=" + artikelnr + "";
      try { s.execute(sql); }
      catch (Exception exx) {}
    }
  }
  public void setItemDelete(int artikelnr) { 
    String sql = "DELETE FROM Warenkorb WHERE kundennr LIKE '" +
                 getWarenkorbId() + "' AND artikelnr=" + artikelnr;
    try { s.execute(sql); }
    catch (Exception ex) {}
  }
  public boolean setLogin(String kennung, String passwort) {
    String sql = "SELECT kundennr FROM Kunden WHERE kennung LIKE '" +
                 kennung + "' AND passwort LIKE '" + passwort + "'";
    try {
      ResultSet r = s.executeQuery(sql);
      if (r.next()) {
        String kunr = r.getString("kundennr");
        sql = "SELECT artikelnr FROM Warenkorb " +
              "WHERE kundennr LIKE '" + kunr +"'";
        String notin = "";
        try {
          r = s.executeQuery(sql);
          while (r.next()) 
            notin += r.getString("artikelnr") + ",";
          if (notin.length() != 0) {
            notin = " AND artikelnr NOT IN (" + notin +  "12345678) ";
          }
        }
        catch (Exception ex) { return false; }
        sql = "UPDATE Warenkorb SET kundennr='" + kunr +
              "' WHERE kundennr LIKE '" + getWarenkorbId() + 
              "' " + notin;
        try { s.execute(sql); }
        catch (Exception exx) {}
        setWarenkorbId(kunr);
        return true;
      }
      else return false;
    }
    catch (Exception ex) { return false; }
  }
  public void setSession(Object session) {
    this.session = (HttpSession) session;
    if (this.session.isNew()) {
      String warenkorbid = "warenkorb_";
      for (int i = 0; i < 30; i++)
        warenkorbid += (char)(int) (Math.random() * 26.0 + 'A');
      setWarenkorbId(warenkorbid);
    }
  }
  public void setSessionUncond(Object session) {
    this.session = (HttpSession) session;
    String warenkorbid = "warenkorb_";
    for (int i = 0; i < 30; i++)
      warenkorbid += (char)(int) (Math.random() * 26.0 + 'A');
    setWarenkorbId(warenkorbid);
  }
  public void setWarenkorbId(String warenkorbid) {
    if (session != null) session.setAttribute("warenkorbid", warenkorbid);
  }
  public Object getWarenkorbId() {  // vorwiegend zu Testzwecken
    return (session != null) ? session.getAttribute("warenkorbid") : null;
  }
  public void setSessionTimeout(int sekunden) {
    if (session != null) session.setMaxInactiveInterval(sekunden);
  }
}