package kakibeans;

import java.util.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import kotkabeans.*;

/**
 * <p>Title: StudyPlan</p>
 * <p>
 * Description: Class for study plan. Takes care of basic information about
 * study plan and has reference to the root studyPlanElement.
 * </p>
 * <p>
 * Use getInstance-method for getting instance of studyPlan. This takes care
 * of creating new studyPlan, loading studyPlan from database,
 * getting it from session, and placing it in session.
 * </p>
 * <p>Copyright: Copyright (c) Kuikka-team GPL 2004</p>
 * <p>Company: University of Jyväskylä</p>
 * <p>Created: 31.3.2004<p>
 * @author Sami Kosonen
 * @version 1.0
 *
 * UPDATES:
 *
 * 6.4.2004, Lauri Pohjola
 *   Tietokannasta haku ja lisäys sekä päivitys ovat nyt toiminnassa
 *
 */

public class StudyPlan {

// Constants

  protected static final boolean DEBUG = true;
  protected static final boolean USE_SESSION = false;

  //These constant values are also in database table "study_plan_status"
  public static final int STATUS_UNREADY = 1;
  public static final int STATUS_READY = 2;
  public static final int STATUS_OUTDATED = 3;
  public static final String STATUS_STRING[] = {"", "suunnitteilla",
      "aktiivinen", "vanhentunut"};

// Attributes

  //fields in study_plan -database table
  private int planID = 0;
  private int studyGroupID = 0;
  private int personID = 0;
  private int planStatusID = 0;
  private String name = "";
  private String modifiedOn = ""; //Timestamp
  private User user = null;

  //References to other objects
  private StudyPlanElement rootStudyPlanElement = null;

// Constructors

  public StudyPlan() {};

  protected StudyPlan(int studyGroupID, User user, int planStatusID, String name)
      throws Exception {
    setStudyGroupID(studyGroupID);
    //setPersonID(personID);
    setPlanStatusID(planStatusID);
    setName(name);
    if (user == null) throw new Exception("StudyPlan: user = null ");
    setUser(user);
    setPersonID(getUser().getPersonID());
  }

  /**
   * Creates instance of StudyPlan
   * @param planID the id of students study plan
   * @param user current user
   * @throws Exception
   */
  /*protected*/ public StudyPlan(int planID, User user) throws Exception {
    if (planID > 0)
      setUser(user);
      loadFromDB(planID);
    }

  /**
   * Returns instance of StudyPlan.
   * If right studyPlan is already in session, doesn't create new.
   * Else, creates new studyPlan according to the planID in request.
   * @param request from JSP-page
   * @param user current user
   * @return instance of studyPlan
   * @throws java.lang.Exception
   */
  public static StudyPlan getInstance(HttpServletRequest request, User user)
      throws Exception{

    HttpSession session = null;
    StudyPlan plan = null;

    //Try to get studyPlan from session
    try {
      session = request.getSession();
      plan = (StudyPlan)session.getAttribute("studyPlan");
    } catch (Exception ex) {
      //Couldn't get session
    }

    //Try to get planID from request
    int planID = RequestHandler.getInteger(request, "planid", 0);

    //Check if there is already instance of StudyPlan
    if (plan != null) {

      //Check if studyPlan has right id
      if (plan.getPlanID() == planID && planID > 0)
        return plan; //No need to create new studyPlan

    }

    //Make instance
    //If there should already be a studyPlan in database and we know it's id
    if (planID > 0) {
      try {
        plan = new StudyPlan(planID, user);
      }
      catch (Exception ex) {
        Log.log("Exception in StudyPlan.getInstance: ", ex);
        throw new Exception("StudyPlan.getInstance: " + ex.getMessage());
      }
    }

    //If we have to create new studyPlan
    else {
      try {

        //Get parameters from request
        int studyGroupID = RequestHandler.getInteger(request, "studyGroupID", 0);
        String planName = RequestHandler.getString(request, "planName", "");

        //If parameters are not correct, throw Exception
        if (studyGroupID <= 0) throw new Exception("The studyGroupID="
              + studyGroupID + " is invalid.");
        if (planName.equals("")) throw new Exception(
              "The name of the studyPlan is empty.");

        //Make new studyPlan
        plan = new StudyPlan(studyGroupID, user, StudyPlan.STATUS_UNREADY,
                             planName);

        //TODO: Make this better way later...
        //
        plan.saveToDB(user.getLanguageID());
        plan.loadFromDB(plan.getPlanID());
        plan.getRootStudyPlanElement().setPlanned();

        //Initialize default times for root studyPlanElement
        plan.getRootStudyPlanElement().setBeginTime(
            new Timestamp((Calendar.getInstance()).getTime().getYear(), 0, 0,
                          0, 0, 0, 0));
        plan.getRootStudyPlanElement().setEndTime(
            new Timestamp((Calendar.getInstance()).getTime().getYear() + 5, 0, 0,
                          0, 0, 0, 0));

        plan.saveToDB(user.getLanguageID());

      } catch (Exception ex) {
        Log.log("Exception in StudyPlan.getInstance. ", ex);
        throw new Exception(ex.getMessage());
      }
    }

    //TODO:
    //Check that user has right to see this studyplan
    //if (user.getPersonID() != plan.getPersonID()
    //    && user.getAccessRightLevelID() == user.

    //Add studyPlan in session
    if (USE_SESSION)
      if (session != null) session.setAttribute("studyPlan", plan);

    return plan;
  }

// Set-methods

  protected void setPlanID( int planID ) {
    if (planID < 0) return;
    this.planID = planID;
    setModifiedOn();
  }

  protected void setStudyGroupID( int studyGroupID ) {
    if (studyGroupID < 0) return;
    this.studyGroupID = studyGroupID;
    setModifiedOn();
  }

  protected void setPersonID( int personID ) {
    if (personID < 0) return;
    this.personID = personID;
    setModifiedOn();
  }

  public void setPlanStatusID( int planStatusID ) {
    if (planStatusID < 0) return;
    this.planStatusID = planStatusID;
    setModifiedOn();
  }

  public void setName( String name ) {
    this.name = name;
    setModifiedOn();
  }

  protected void setModifiedOn( String modifiedOn ) {
    this.modifiedOn = modifiedOn;
  }

  protected void setModifiedOn() {
    setModifiedOn(time.getCurrentTime());
  }

  protected void setRootStudyPlanElement(StudyPlanElement rootStudyPlanElement) {
    this.rootStudyPlanElement = rootStudyPlanElement;
  }

  protected void setUser(User user) {
    this.user = user;
  }

// Get-methods

  public int getPlanID()        { return planID; }
  public int getStudyGroupID()  { return studyGroupID; }
  public int getPersonID()      { return personID; }
  public int getPlanStatusID()  { return planStatusID; }
  public String getName()       { return name; }
  public String getModifiedOn() { return modifiedOn; }
  public static String getPlanStatus(int status) {
    return STATUS_STRING[status];
  }
  public User getUser()         { return user; }

  public StudyPlanElement getRootStudyPlanElement() {
    return rootStudyPlanElement;
  }

// Methods for getting structure from database and intializing studyPlanElements

  /**
   * Inserts studyplangroup- and subgroupids in vector.
   * @param studygroupid parent
   * @param subgroupid child
   * @param courseid id of course
   * @param db database connection
   * @param relation vector, where to place IntGroup-objects
   * @param courseids ids of courses
   * @throws java.lang.Exception
   */
  public void getPlanStudyGroupIDs(int studygroupid, int subgroupid,
                                   int courseid, DB db, Vector relation,
                                   Vector courseids)
      throws Exception {
/*
    String sql = "SELECT subgroupid, courseid FROM study_group_relation"
        + " WHERE studygroupid = " + studygroupid
        + " AND deleted = false AND (planid = " + getPlanID()
        + " OR planid is null)";
*/
    String sql = "SELECT subgroupid, courseid FROM study_group_relation"
        + " WHERE (studygroupid = " + studygroupid
        + " OR (studygroupid = " + subgroupid + " AND subgroupid isnull))"
        + " AND deleted = false AND (planid = " + getPlanID()
        + " OR planid is null)";

    //Add studygroupids and groupsubgroupids in vector
    if (studygroupid > 0) {
      //relation.add(new IntGroup(new int[]{studygroupid, subgroupid, courseid}));
      relation.add(new IntGroup(new int[]{subgroupid, studygroupid}));
      RS2s rs2 = new RS2s(db.executeQuery(sql));
      while (rs2 != null && rs2.next()) {
        getPlanStudyGroupIDs(rs2.getInt("subgroupid"), studygroupid,
                             rs2.getInt("courseid"), db, relation, courseids);
      }
    }

    //Add
    else if (courseid > 0) {
      courseids.add(new IntGroup(new int[]{subgroupid, courseid}));
    }
  }

// Methods for getting information from database

  /**
   * Gets information from database and saves it to attributes.
   * @param planID the id of the study_plan
   * @throws java.lang.Exception
   */
  public void loadFromDB(int planID) throws Exception {

    if (DEBUG) {
      Log.log("In StudyPlan.loadFromDB -method.");
      System.out.println("In StudyPlan.loadFromDB -method.");
    }

    String loadPlanQuery = "SELECT s.studygroupid, s.personid, s.planstatusid,"
        + " s.name, s.modifiedon FROM study_plan as s"
        + " WHERE s.planid = " + planID + " AND s.deleted = false";

    RS2s rs2 = null; //For information about studyPlan
    RS2s rs2Group = null;
    RS2s rs2Course = null;
    RS2s rs2PlannedElement = null;
    DB db = new DB("StudyPlan.loadFromDB db");

    //Try to get information for studyPlan from database
    try {
      db.connect();
      rs2 = new RS2s(db.executeQuery(loadPlanQuery));
    } catch (Exception e) {
      Log.log("Exception in StudyPlan.loadFromDB :", e);
      throw new Exception("StudyPlan.loadFromDB: " + e.getMessage());
    } finally {
      db.disconnect();
    }

    //Initialize studyPlan
    if (rs2 != null && rs2.next()) {
      setPlanID(planID);
      setStudyGroupID(rs2.getInt("studygroupid"));
      setPersonID(rs2.getInt("personid"));
      setName(rs2.getString("name"));
      setPlanStatusID(rs2.getInt("planstatusid"));
      setModifiedOn(rs2.getString("modifiedon"));
    }
    else {
      throw new Exception("No any studyPlan matching planID=" + planID + ".");
    }

    if (DEBUG) {
      Log.log("StudyPlan found from DB. planID = " + planID);
    }

    //Query for getting all studyPlanGroups
    StringBuffer queryGroup = new StringBuffer();
    queryGroup.append("SELECT t.value, s.studygroupid, s.grouptypeid,");
    queryGroup.append(" s.groupstatusid, s.min_credits, s.min_count,");
    queryGroup.append(" s.coursemask, s.valid_from_year, s.modifiedon");
    queryGroup.append(" FROM study_group as s,study_group_parameter as p,");
    queryGroup.append(" study_group_parameter_translation as t");
    queryGroup.append(" WHERE s.deleted = false AND p.deleted = false");
    queryGroup.append(" AND t.deleted = false AND s.studygroupid IN (");

    //Query for getting information about courses
    StringBuffer queryCourse = new StringBuffer();
    queryCourse.append(
        "SELECT c.courseid, c.code, c.maxcredits, c.mincredits, ct.name");
    queryCourse.append(" FROM course as c, coursetranslation as ct");
    queryCourse.append(" WHERE c.deleted = false AND ct.deleted = false");
    //Rest of this query is completed while database connection is on

    //Query for getting information for planned elements
    StringBuffer queryPlannedElement = new StringBuffer();
    queryPlannedElement.append(
        "SELECT unitid, begintime, endtime, studygroupid, courseinstanceid");
    queryPlannedElement.append(
        " FROM study_unit WHERE deleted = false AND planid = " + planID);
    queryPlannedElement.append(" AND studygroupid IN (");
    //Rest of this query is completed while database connection is on

    Vector relation = new Vector(); //structure is stored here
    Vector courseids = new Vector();
    //Try to get information for elements in structure and plan.
    try {
      db.connect();
      //Get ids for all elements in structure
      getPlanStudyGroupIDs(getStudyGroupID(), 0, 0, db, relation, courseids);

      //Add to query for getting all studyPlanGroups
      Iterator i = relation.iterator();
      StringBuffer studyGroupIDs = new StringBuffer(); //We need this later
      while (i.hasNext()) {
        IntGroup groupid = (IntGroup) i.next();
        //queryGroup.append(groupid.getInt(1));
        studyGroupIDs.append(groupid.getInt(1));
        if (i.hasNext()) {
          studyGroupIDs.append(", ");
        }
      }
      queryGroup.append(studyGroupIDs);

      queryGroup.append(") AND s.studygroupid = p.studygroupid");
      queryGroup.append(" AND p.groupparametertypeid = " + GroupElement.NAME);
      queryGroup.append(" AND p.groupparameterid = t.groupparameterid");
      queryGroup.append(" AND t.languageid = " + user.getLanguageID());

      //Add to query for getting information about courses
      i = courseids.iterator();
      if (i.hasNext()) {
        queryCourse.append(" AND c.courseid IN (");
        while (i.hasNext()) {
          IntGroup courseid = (IntGroup) i.next();
          queryCourse.append(courseid.getInt(1));
          if (i.hasNext()) {
            queryCourse.append(", ");
          }
        }
        queryCourse.append(")");

        queryCourse.append(" AND c.courseid = ct.courseid");
        queryCourse.append(" AND ct.languageid = " + user.getLanguageID());

        if (DEBUG) System.out.println("queryCourse: " + queryCourse.toString());

        //ResultSet for Courses
        rs2Course
            = new RS2s(db.executeQuery(queryCourse.toString()));

      }

      //Add to query for getting information for planned elements
      queryPlannedElement.append(studyGroupIDs);
      queryPlannedElement.append(")");

      //ResultSet for Groups
      rs2Group
          = new RS2s(db.executeQuery(queryGroup.toString()));

      //ResultSet for PlannedElements
      rs2PlannedElement
          = new RS2s(db.executeQuery(queryPlannedElement.toString()));

    } catch (Exception e) {
      Log.log("Exception in StudyPlan.loadFromDB :", e);
      throw new Exception("StudyPlan.loadFromDB: " + e.getMessage());
    } finally {
      db.disconnect();
    }

    if (DEBUG) {
      Log.log("Stuff for studyPlanElements from DB.");

      //Groups
      System.out.println("Results of queryGroup");
      while (rs2Group != null && rs2Group.next()) {
        System.out.println(rs2Group.getString("value"));
      }
      System.out.println();
      if (rs2Group != null)
        rs2Group.beforeFirst();

      //Courses
      //courseid, code, maxcredits, mincredits, name
      System.out.println("Results of queryCourse");
      while (rs2Course != null && rs2Course.next()) {
        System.out.println(rs2Course.getString("code") + ", "
                           + rs2Course.getString("name") + ", "
                           + rs2Course.getInt("mincredits") + ", "
                           + rs2Course.getInt("maxcredits") + ", "
                           + rs2Course.getInt("courseid"));
      }
      System.out.println();
      if (rs2Course != null)
        rs2Course.beforeFirst();

      //Planned elements
      //unitid, begintime, endtime, studygroupid, courseinstanceid
      System.out.println("Results of queryPlannedElement");
      if (rs2PlannedElement != null)
        while (rs2PlannedElement.next()) {
          System.out.print(rs2PlannedElement.getInt("unitid") + ", ");
          //System.out.print(rs2PlannedElement.getTimestamp("begintime") + ", ");
          //System.out.print(rs2PlannedElement.getTimestamp("endtime") + ", ");
          System.out.print(rs2PlannedElement.getInt("studygroupid") + ", ");
          System.out.print(rs2PlannedElement.getInt("courseinstanceid"));
          System.out.println();
        }
      System.out.println();
      if (rs2PlannedElement != null)
        rs2PlannedElement.beforeFirst();
    }

    //Initialization of objects
    try {

      Vector elements = new Vector();
      Vector plannedElements = new Vector();
      Vector unitIDs = new Vector();
      BitSet plannedGroupIDs = new BitSet();

      //TODO: Place these things in own methods...

      //PLANNED ELEMENTS
      //Go through the ResultSet containing information about planned elments
      //and initialize PlannedElement objects according to that information.
      if (rs2PlannedElement != null)
        while (rs2PlannedElement.next()) {
          PlannedElement planned = null;
          planned  = new PlannedElement();
          planned.setUnitID(rs2PlannedElement.getInt("unitid"));
          planned.setBeginTime(rs2PlannedElement.getTimestamp("begintime"));
          planned.setEndTime(rs2PlannedElement.getTimestamp("endtime"));

          //Now if plannedElement doesn't change in the future, no need to
          //update it to database
          planned.setChanged(false);

          //Place group of studyGroupID and unitID in vector
          int studyGroupID = rs2PlannedElement.getInt("studygroupid");
          IntGroup unitID = new IntGroup(new int[] {studyGroupID,
                                         planned.getUnitID()});
          unitIDs.add(unitID);

          //Set studyGroupID as planned in BitSet
          plannedGroupIDs.set(studyGroupID);

          //Place plannedElement in vector
          plannedElements.add(planned);

        }
      //GROUPS
      //Go through the ResultSet containing information about groups
      //in study plan and initialize objects according to that information
      if (rs2Group != null)
        while (rs2Group.next()) {

          StudyPlanGroup element = null;

          //Create StudyPlanGroup
          element = new StudyPlanGroup();

          //Set attributes
          element.setMinCount(rs2Group.getInt("min_count"));
          element.setMinCredits(rs2Group.getInt("min_credits"));
          element.setCourseMask(rs2Group.getString("coursemask"));

          element.setName(rs2Group.getString("value"));
          element.setStudyGroupID(rs2Group.getInt("studygroupid"));
          element.setPlanID(planID);

          //If element is included in plan, initialize PlannedElement

          //...

          //Place element in vector
          elements.add(element);

          if (DEBUG) {
            System.out.println("Added group in vector: " + element.getName() +
                               " - "
                               + element.getStudyGroupID());
          }

          //For testing
          element.setOpen(true);
        }

      //COURSES
      //Go through the ResultSet containing information about courses
      //in study plan and initialize objects according to that information

      if (rs2Course != null)
        while (rs2Course.next()) {

          StudyPlanCourse element = null;

          //Create StudyPlanCourse
          element = new StudyPlanCourse();

          //Set attributes specific to StudyPlanCourse
          //courseid, code, maxcredits, mincredits, name
          element.setCourseID(rs2Course.getInt("courseid"));
          element.setMinCredits(rs2Course.getInt("mincredits"));
          element.setMaxCredits(rs2Course.getInt("maxcredits"));
          element.setCode(rs2Course.getString("code"));
          element.setName(rs2Course.getString("name"));
          element.setPlanID(planID);

          //Get studygroupid from courseids -vector
          int studyGroupID = 0;
          Iterator i = courseids.iterator();
          while (i.hasNext()) {
            IntGroup ig = (IntGroup) i.next();
            if (ig.getInt(1) == element.getCourseID()) {
              element.setStudyGroupID(ig.getInt(0));
              break;
            }
          }

          //For testing
          element.setAbility(StudyPlanElement.NOT_PLANNED_COURSE);

          //If element is included in plan, initialize PlannedElement

          //...

          //Place element in vector
          elements.add(element);

          if (DEBUG) {
            System.out.println("Added in vector: " + element.getName() + " - "
                               + element.getStudyGroupID());
          }
        }

      if (DEBUG) {
        Iterator d = elements.iterator();
        System.out.println("relation-vector");
        d = relation.iterator();
        while (d.hasNext()) {
          IntGroup ig = (IntGroup) d.next();
          System.out.println(ig.getInt(0) + ", " + ig.getInt(1));
        }
        System.out.println("courseids-vector");
        d = courseids.iterator();
        while (d.hasNext()) {
          IntGroup ig = (IntGroup) d.next();
          System.out.println(ig.getInt(0) + ", " + ig.getInt(1));
        }
      }

      //Place elements in right structure according to rs2, and
      //initialize plannedElement for elements that are included in plan


      Iterator i = elements.iterator();

      //Go through studyPlanElements in vector
      while (i.hasNext()) {

        StudyPlanElement element = (StudyPlanElement) i.next();

        //Check if planned
        if (plannedGroupIDs.get(element.getStudyGroupID()) == true) {

          //Make element planned

          //Find right planned element from vector
          int unitID = 0;
          Iterator ui = unitIDs.iterator();
          while (ui.hasNext()) {
            IntGroup ids = (IntGroup)ui.next();
            if (ids.getInt(0) == element.getStudyGroupID())
              unitID = ids.getInt(1);
          }
          ui = plannedElements.iterator();
          while (ui.hasNext()) {
            PlannedElement planned = (PlannedElement)ui.next();
            if (planned.getUnitID() == unitID)
              element.setPlanned(planned);
          }
        }


        if (DEBUG) {
          Log.log(
              "Find all subgroupids from relation vector for element and set them in BitSet.");
        }

        //Find all subgroupids from relation vector for element and set them in
        //BitSet.
        BitSet subGroupIDs = new BitSet();
        Iterator r = relation.iterator();
        while (r.hasNext()) {
          IntGroup ids = (IntGroup)r.next();
          if (ids.getInt(0) == element.getStudyGroupID())
            subGroupIDs.set( ids.getInt(1)); //Set subgroupid in BitSet
        }

        if (DEBUG) {
          System.out.println(subGroupIDs.toString());
        }

        if (DEBUG) {
          Log.log("Find all elements that has these ids from elements vector and add them to this elements children.");
        }
        //Find all elements that has these ids from elements vector and add them
        //to this elements children.
        Iterator j = elements.iterator();
        while (j.hasNext()) {
          StudyPlanElement candidate = (StudyPlanElement) j.next();

          //If candidate.studyGroupID is set in BitSet
          if (subGroupIDs.get(candidate.getStudyGroupID())) {

            //Add candidate as element's child.
            element.add(candidate);

            if (DEBUG) {
              System.out.println("Added '" + candidate.getName() + "' under '" +
                                 element.getName() + "'");
            }

          }
        }

        //If element is root element, add it in StudyPlan's attribute
        if (getStudyGroupID() == element.getStudyGroupID()) {
          setRootStudyPlanElement(element);

          if (DEBUG) {
            System.out.println("****Added '" + element.getName() + "' as root.");
          }

        }

      }

    } catch (Exception ex) {
      Log.log("Exception in StudyPlan.loadFromDB - initializing: ", ex);
      throw new Exception("StudyPlan.loadFromDB - initializing: " + ex.getMessage());
    }
    if (DEBUG) {
      System.out.println("Finished StudyPlan.loadFromDB");
    }

  }

// Methods for saving information to database

  /**
   * Determines whether to use updateToDB or insertToDB.
   * @param lang The language id from jsp page
   * @throws java.lang.Exception
   */
  public void saveToDB(int lang) throws Exception {
    if (getPlanID() == 0) {
      try {
        insertToDB(lang);
      }
      catch (Exception e) {
        throw new Exception("Saving to database failed! "+ e.getMessage());
      }
    }
    else {
      try {
        updateToDB(lang);
      }
      catch (Exception e) {
        throw new Exception("Updating to database failed! "+ e.getMessage());
      }
    }
  }

  /**
   * Adds new studyPlan in database.
   * @param lang the language id
   * @throws java.lang.Exception
   */
  public void insertToDB(int lang) throws Exception {

    //Create / get next number for index
    setPlanID(AutoNumber.getNumber("study_plan", "planid"));

    //Query to add study_plan to database
    String insertQuery = "INSERT INTO study_plan (deleted, planid, studygroupid,"
        + " personid, planstatusid, name, modifiedon) "
        + " VALUES (false, " + getPlanID() + ", " + getStudyGroupID()
        + ", " + getPersonID() + ", " + getPlanStatusID() + ", '"
        + getName() + "', '" + getModifiedOn() + "' )";

    DB db = new DB("StudyPlan.insertToDB db");
    try {
      db.connect();
      db.executeUpdate(insertQuery);

      //Add studyPlanElements in database
      if (getRootStudyPlanElement() != null)
        getRootStudyPlanElement().saveToDBRecursive(db);

    } catch (Exception e) {
      Log.log("Exception in StudyPlan.insertToDB", e);
      throw e; // disconnect will always be performed
    } finally {
      db.disconnect();
    }
  }

  /**
   * Updates information of studyPlan in database.
   * @param lang the language id
   * @throws java.lang.Exception
   */
  public void updateToDB(int lang) throws Exception {
    String sql = "UPDATE study_plan SET studygroupid=" + getStudyGroupID()
        + ", personid=" + getPersonID() + "," + " planstatusid="
        + getPlanStatusID() + ", name='" + getName() + "', modifiedon='"
        + getModifiedOn() + "'"
        + " WHERE deleted=false" + " AND planid=" + getPlanID();
    DB db = new DB("StudyPlan.updateToDB db");
    try {
      db.connect();
      db.executeUpdate(sql);

      //Save studyPlanElements in database
      if (getRootStudyPlanElement() != null)
        getRootStudyPlanElement().saveToDBRecursive(db);

    } catch (Exception e) {
      Log.log("Exception in StudyPlan.updateToDB", e);
      throw e; // disconnect will always be performed
    } finally {
      db.disconnect();
    }
  }

  /**
   * Sets this study_plan as deleted in database.
   * TODO: mark rows belonging to this study_plan deleted in study_unit table.
   * @throws java.lang.Exception
   */
  public void removeFromDB() throws Exception {
    if (getPlanID() == 0) return; //If not in database

    //SQL query.
    String queryRemove = "UPDATE study_plan SET deleted = true WHERE planid = "
        + getPlanID();

    DB db = new DB("StudyPlan.removeFromDB db");
    try {
      db.connect();
      db.executeUpdate(queryRemove); //Mark as deleted

      //TODO: Mark elements in studyunit-table as deleted also

    }
    catch (Exception e) {
      Log.log("Exception in StudyPlan.removeFromDB", e);
      throw e;
    }
    finally {
      db.disconnect();
    }
  }

// Methods for printing to StringBuffer

  /**
   * Prints list of study plans of user
   * @param buff buffer where to print
   * @param user current user
   * @throws Exception
   */
  public static void printStudyPlans(StringBuffer buff, User user)
      throws Exception {
    String studyPlanQuery = "SELECT personid, planstatusid, name, modifiedon, planid"
        + " FROM study_plan WHERE deleted = false AND personid = '" + user.getPersonID() + "'";

    DB db = new DB("StudyPlan.printStudyPlans db");

    try {
      db.connect();
      RS2s rs2 = new RS2s(db.executeQuery(studyPlanQuery));
      //String str = rs2.getString("modifiedon");
      //String date = str.substring(1,10);
      while(rs2 != null && rs2.next()) {
        buff.append("<tr><td><a href=" + "viewStudyProgram.jsp?" + "planid="
                    + rs2.getString("planid") +  ">"
                 + rs2.getString("name") + "</a></td>"
                    + "<td>" + time.monthToOutFormat((rs2.getString("modifiedon")).substring(0,10)) + "</td>"
                    + "<td>" + user.getFirstNames() + " " + user.getLastName() + "</td>"
                    + "<td>" + getPlanStatus(rs2.getInt("planstatusid")) + "</td></tr>");
      }
    }
    catch (Exception e) {
      Log.log("Exception in StudyPlan.printStudyPlans", e);
      throw e;
    }
    finally {
      db.disconnect();
    }

  }

// Methods for printing

  public String printRecursive() {
    if (getRootStudyPlanElement() == null) return "";
    return getRootStudyPlanElement().printRecursive();
  }

  public String printRecursiveModify() {
    if (getRootStudyPlanElement() == null) return "";
    return getRootStudyPlanElement().printRecursiveModify();
  }

// Methods for searching StudyPlanElement

  /**
   * Find StudyPlanElement recursively.
   * @param studyGroupID the studyGroupID of searched StudyPlanElement
   * @param s current element
   * @return found element, or null if not found
   */
  protected StudyPlanElement getElementRecursive(int studyGroupID,
                                                 StudyPlanElement s) {
    if (s == null) return null;

    //If found element maching studyGroupID
    if (s.getStudyGroupID() == studyGroupID) return s;

    //Go through the children recursively
    Iterator i = s.iterator();
    if (i == null) return null;

    while (i.hasNext()) {
      StudyPlanElement candidate =
          getElementRecursive(studyGroupID, (StudyPlanElement)i.next());
      if (candidate != null) return candidate;
    }

    //Not found element maching studyGroupID
    return null;
  }

  /**
   * Get element matching studyGroupID.
   * @param studyGroupID the studyGroupID of searched StudyPlanElement
   * @return found element, or null if not found
   */
  public StudyPlanElement getElement(int studyGroupID) {
    if (getRootStudyPlanElement() == null) return null;
    return getElementRecursive(studyGroupID, getRootStudyPlanElement());
  }

  /**
   * Find StudyPlanElements recursively
   * @param studyGroupIDs the studyGroupIDs of searched StudyPlanElements
   * @param s current element
   * @param elements vector containing found elements
   */
  protected void getElementsRecursive(BitSet studyGroupIDs,
                                      StudyPlanElement s,
                                      Vector elements) {
    if (s == null) return;
    if (studyGroupIDs.isEmpty()) return;

    //If found element maching studyGroupID
    if (studyGroupIDs.get(s.getStudyGroupID()) == true) {
      elements.add(s);
      studyGroupIDs.clear(s.getStudyGroupID());
    }

    //Go through the children recursively
    Iterator i = s.iterator();
    if (i == null) return;

    while (i.hasNext()) {
      getElementsRecursive(studyGroupIDs, (StudyPlanElement)i.next(),
                           elements);
    }
  }

  /**
   * Get element matching studyGroupID
   * @param studyGroupIDs the studyGroupID of searched StudyPlanElements
   * @return elments in Vector
   */
  public Vector getElements(String[] studyGroupIDs) {
    if (getRootStudyPlanElement() == null) return null;

    //Make BitSet containing studyGroupIDs
    BitSet studyGroupIDsBitSet = new BitSet();
    for (int i = 0; i < studyGroupIDs.length; i++) {
      int id = 0;
      try {
        id = Integer.parseInt(studyGroupIDs[i]);
        studyGroupIDsBitSet.set(id);
      } catch (Exception ex) {
        Log.log("StudyPlan.getElements: Could not parse int from String. ");
      }
    }
    Vector elements = new Vector();
    getElementsRecursive(studyGroupIDsBitSet, getRootStudyPlanElement(),
                         elements);
    return elements;
  }

  /**
   * Find planned StudyPlanElements recursively
   * @param s current element
   * @param elements vector containing found elements
   */
  protected void getPlannedElementsRecursive(StudyPlanElement s,
                                             Vector elements) {
    if (s == null) return;

    //If found element included in plan
    if (s.getPlanned() != null) {
      elements.add(s);
    }

    //Go through the children recursively
    Iterator i = s.iterator();
    if (i == null) return;
    while (i.hasNext()) {
      getPlannedElementsRecursive((StudyPlanElement)i.next(),
                                  elements);
    }
  }

  /**
   * Get all planned elements
   * @return planned studyPlanElements in vector
   */
  public Vector getPlannedElements() {
    Vector elements = new Vector();
    getPlannedElementsRecursive(getRootStudyPlanElement(), elements);
    return elements;
  }
}