package kakibeans;

import java.io.*;
import java.sql.*;
import java.util.*;
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 = false;
  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 int STATUS_EXAMPLE = 4;
  public static final String STATUS_STRING[] = {
      "", "suunnitteilla",
      "aktiivinen", "vanhentunut", "malli"};

  public static final String ENABLE_INFO = "enableInfo";
  public static final String ENABLE_TIMECOMBOS = "enableTimeCombos";

// Attributes

  //fields in study_plan -database table
  private int planID = 0;
  private int studyGroupID = 0;
  private int planStatusID = 0;
  private String name = "";
  private Timestamp createdAt = null;
  private int ownerID = 0;
  private HttpServletRequest request = null;

  private User user = null;
  private String organisationName = "";

  //References to other objects
  private StructureElement rootStructureElement = null;

  private Hashtable acceptances = new Hashtable(); //Move to StructureElement...
  private Hashtable planElements = new Hashtable();

//CheckBox groups for different choices
  private CheckBoxGroup columns = null;
  private CheckBoxGroup commentType = null;
  private CheckBoxGroup commentFields = null;
  private CheckBoxGroup scheduleColumns = null;

  public void setColumns( CheckBoxGroup cbg ) {
    this.columns = cbg;
  }
  public void setScheduleColumns( CheckBoxGroup cbg ) {
    this.scheduleColumns = cbg;
  }
  protected void setCommentTypes(CheckBoxGroup cbg) {
    this.commentType = cbg;
  }
  protected void setCommentFields(CheckBoxGroup cbg) {
    this.commentFields = cbg;
  }
  public CheckBoxGroup getColumns() {
    return this.columns;
  }
  public CheckBoxGroup getScheduleColumns() {
    return this.scheduleColumns;
  }
  public CheckBoxGroup getCommentTypes() {
    return this.commentType;
  }
  public CheckBoxGroup getCommentFields() {
    return this.commentFields;
  }

// Constructors

  public StudyPlan() {};

/* BEGIN OLD */
  /**
   * This is used when creating new studyPlan.
   * (StudyPlan is not in database)
   * @param studyGroupID
   * @param user
   * @param request
   * @param planStatusID
   * @param name
   * @throws java.lang.Exception
   */
  protected StudyPlan(int studyGroupID, User user, HttpServletRequest request,
                      int planStatusID, String name) throws Exception {
    setStudyGroupID(studyGroupID);
    setPlanStatusID(planStatusID);
    setName(name);
    if (user == null)
      throw new Exception("StudyPlan: user = null ");
    setUser(user);
    setOwnerID(user.getPersonID());
    if (request == null)
      throw new Exception("StudyPlan: request = null ");
    setRequest(request);
    setCreatedAt();
  }

  /**
   * Creates instance of StudyPlan
   * @param planID the id of students study plan
   * @param user current user
   * @throws Exception
   */
  protected StudyPlan(int planID, User user, HttpServletRequest request) throws
      Exception {
    if (planID > 0) {
      if (user == null)
        throw new Exception("StudyPlan: user = null ");
      setUser(user);
      loadFromDB(planID);
      //setPlanID(planID);
      if (request == null)
        throw new Exception("StudyPlan: request = null ");
      setRequest(request);
    }
  }

  /**
   * 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, request);
      }
      catch (Exception ex) {
        Log.log("Exception in StudyPlan.getInstance: ", ex);
        throw new Exception("StudyPlan.getInstance: " + ex.getMessage());
      }
    }

    //If we are creating new studyPlan
    else {
      try {

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

        //Remove html-tags
        planName = tools.StringTools.removeHtmlTags(planName);

        //Remove illegal characters
        planName = tools.StringTools.stripChars(planName, "\"'&?=");

        //Encode possible sql-strings
        planName = tools.StringTools.SQLEncode(planName);

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

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

        //Initialize studyPlanElements

        //Load studyPlanElements from database
        plan.loadElementsFromDB();

        //Make rootStudyPlanElement as planned
        plan.getRootStructureElement().getStudyPlanElement().setPlanned();

        plan.getRootStructureElement().makeCompulsoryPlanned();

        //parseJOREFile for possible already completed courses
        //and make them planned and completed
        plan.parseJOREFile();

//Implement saving
        //Save changes (elements added in plan automatically)
        //TODO: Fix this that only changed elements are saved in database
        //Note: maybe done already(?)
        plan.saveToDB();

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

    //If user doesn't have right to see this studyplan
    //Now teachers can see all studyplans and students only their own.
    if (plan != null)
      if (user.getPersonID() != plan.getOwnerID()
          && !user.hasRight(Right.ACCESS_RIGHT, Right.TEACHER)
          && plan.getPlanStatusID() != STATUS_EXAMPLE
         ) {
        Log.log("User doesn't have right to access studyplan.");
        throw new Exception(
            "Sorry, you don't have right to access this studyplan.");
      }

    plan.initCommentSelections();

    //Add studyPlan in session (if USE_SESSION = true)
    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;
  }

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

  /**
   * Sets owners ID.
   * @param ownerID reference to personid in person-table.
   */
  public void setOwnerID(int ownerID) {
    if (ownerID < 0) return;
    this.ownerID = ownerID;
  }

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

  public void setName(String name) {
    if (name == null) return;
    String encodedName = tools.StringTools.removeHtmlTags(name);
    encodedName = tools.StringTools.stripChars(encodedName, "\"'&?=");
    encodedName = tools.StringTools.SQLEncode(encodedName);
    if ("".equals(encodedName)) return;
    this.name = encodedName;
  }

  public void setOrganisationName(String organisationName) {
    if (organisationName == null) return;
    this.organisationName = organisationName;
  }

  protected void setCreatedAt(Timestamp createdAt) {
    if (createdAt == null) return;
    this.createdAt = createdAt;
  }

  protected void setCreatedAt() {
    setCreatedAt(new Timestamp(System.currentTimeMillis()));
  }

  protected void setRootStructureElement(StructureElement rootStructureElement) {
    this.rootStructureElement = rootStructureElement;
  }

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

  protected void setRequest(HttpServletRequest request) {
    this.request = request;
  }

//  public void addAcceptance(Acceptance acceptance) {
//    if (acceptance == null) return;
//    if (acceptances == null) {
//      acceptances = new Hashtable();
//    }
//    acceptances.put(acceptance);
//  }

  public void removeAcceptance(Acceptance acceptance) {
    if (acceptance == null || acceptances == null) return;
    acceptances.remove(acceptance);
  }

// Get-methods

  public int getPlanID()            { return planID; }
  public int getStudyGroupID()      { return studyGroupID; }

  /**
   * Returns personID of the person who is logged in in Korppi.
   * If you need personID of the studyplan's owner,
   * then use getOwnerID()-method instead.
   * @return personID of current user in Korppi
   */
  public int getPersonID() {
    if (getUser() != null)
      return getUser().getPersonID();
    return 0;
  }

  public int getPlanStatusID() {
    return planStatusID;
  }

  public String getName() {
    return name;
  }

  public String getOrganisationName() {
    return organisationName;
  }

  public Timestamp getCreatedAt() {
    return createdAt;
  }

  public static String getPlanStatus(int status) {
    return STATUS_STRING[status];
  }

  public User getUser() {
    return user;
  }

  /**
   *
   * @return
   * @throws java.lang.Exception
   */

  public String getOwnerName() throws Exception {

    String ownerNameQuery =
        "SELECT p.firstnames, p.lastname " +
        "FROM study_plan as s, person as p " +
        "WHERE s.deleted=false AND p.deleted=false " +
        "AND s.personid = p.personid AND s.planid = " + planID + ";";

    DB db = new DB("StudyPlan.getOwnerName db");
    String temp = "";

    try {
      db.connect();
      RS2s rs2 = new RS2s(db.executeQuery(ownerNameQuery));
      if (rs2 != null && rs2.next())
        temp = rs2.getString("lastname") + " " + rs2.getString("firstnames");
    }
    catch (Exception e) {
      Log.log("Exception in StudyPlan.getOwnerName :", e);
      throw e;
    }
    finally {
      db.disconnect();
    }
    return temp;
  }

  public int getOwnerID() {
    return ownerID;
  }

  public HttpServletRequest getRequest() {
    return this.request;
  }

  public StructureElement getRootStructureElement() {
    return rootStructureElement;
  }

  /**
   * @return header string: name and name of organisation
   */
  public String getHeader() {
    return getName() + " - " + getOrganisationName();
  }

  /**
   * @return acceptances
   */
  protected Hashtable getAcceptances() {
    return acceptances;
  }

  public Enumeration acceptanceElements() {
    if (getAcceptances() == null) return null;
    return getAcceptances().elements();
  }

  public Enumeration acceptanceKeys() {
    if (getAcceptances() == null) return null;
    return getAcceptances().keys();
  }

  public void putAcceptance(IntGroup key, Acceptance acceptance) {
    if (getAcceptances() == null) return;
    getAcceptances().put(key, acceptance);
  }

  public Enumeration studyPlanElements() {
    if (planElements == null) return null;
    return planElements.elements();
  }

  public Enumeration studyPlanElementKeys() {
    if (planElements == null) return null;
    return planElements.keys();
  }

  /**
   * Puts StudyPlanElement in Hashtable
   * @param element
   */
  public StudyPlanElement putStudyPlanElement(int studyGroupID, StudyPlanElement element) {
    return (StudyPlanElement)planElements.put(new Integer(studyGroupID), element);
  }

  public StudyPlanElement putStudyPlanElement(Integer studyGroupID, StudyPlanElement element) {
    return (StudyPlanElement)planElements.put(studyGroupID, element);
  }

  /**
   * Returns studyplanElement matching code
   * @param code
   * @return StudyPlanElement
   */
  public StudyPlanElement getStudyPlanElementByCode(String code) {
    Enumeration e = studyPlanElements();
    while(e.hasMoreElements()) {
      StudyPlanElement ele = (StudyPlanElement)e.nextElement();
      if(code.equalsIgnoreCase(ele.getCode()))
        return ele;
    }
    return null;
  }

  /**
   * Handles one row from jorefile and parses grade, completiontime and code
   * @param row
   * @return
   */
  protected void parseJORERow(String row, Vector notInPlanCodes) {
    //identify rows that need to be parsed
    if (row.matches("\\s{2}.*\\s{2}\\d{2}\\.\\d{2}\\.\\d{4}.*")) {
      String grade = "";
      String date = "";
      String code = "";
      String ov = "";

      row = (row.substring(39)).trim();

      //CODE
      code = (row.substring(0, 6)).trim();
      //GRADE
      grade = (row.substring(19, 22)).trim();
      //DATE
      date = (row.substring(7, 18)).trim();
      //OV
      ov = (row.substring(25,31).trim());
      //System.out.println(ov);
//      row = "";
//      row += "CODE=" + code + ",GRADE=" + grade + ", DATE=" + date + "<->";

      if (code.trim().length() > 0) {
        StructureElement sele = getStructureElementByCode(code);
        if (sele == null || sele.getPlanned() == null) {
          notInPlanCodes.add(code);
          return;
        }
        StudyPlanElement ele = sele.getStudyPlanElement();
        ele.setGrade(grade);
        ele.setCompletedAt(date);

        Timestamp timestamp = Timestamp.valueOf(time.monthToDBFormat(date) + " 00:00:00.000000000");
        timestamp.setMonth(timestamp.getMonth() - 1);
        ele.setBeginTime(timestamp);
        ele.setEndTime(ele.getBeginTime());

        //using minCredits attribute so that counting credits is made correct
        ele.setMinCredits(Float.valueOf(ov).floatValue());
        ele.setCompletedOV(ov); //not using this
        if (ele.getPlanned() == null) {
          ele.setPlanned();
          DB db = new DB("StudyPlan.parseJORERow() db");
          try {
            db.connect();
            ele.saveToDB(db);
          } catch (Exception e) {
            Log.log("Exception in StudyPlan.parseJORERow(): ", e);
          } finally {
            db.disconnect();
          }
        }
      }
    }
  }

  /**
   * Parses JOREfile for grades and dates
   * @throws java.lang.Exception
   */
  public Vector parseJOREFile() throws Exception {
    String querySocSec = "SELECT socSecBirth, socSecEnd FROM person WHERE"
        + " personid=" + getOwnerID() + " AND deleted=false;";

    RS2s rs2 = Tools.getDBResultS("StudyPlan.parseJOREFile db", querySocSec);
    if (rs2 == null || !rs2.next()) return null;

    Vector notInPlanCodes = new Vector();

    String socSecBirth = rs2.getString("socSecBirth");
    String socSecEnd = rs2.getString("socSecEnd");
    String fileName = kotkabeans.Common.studyRegisterDir
        + socSecBirth.substring(0, 2)
        //+ java.io.File.separator // not working on windows
        + "/"
        + socSecBirth + socSecEnd + ".txt";

    if (DEBUG)
      System.out.println("FILENAME=" + fileName);

    String fileContents = null;
    java.io.File fileHandle = new java.io.File(fileName);
    if ( fileHandle.exists() )
      fileContents = kotkabeans.Tools.getFile(fileName);
    if ( fileContents == null || fileContents.length() == 0 ) {
      Log.log("StudyPlan.parseJOREFile(): JOREFile not found!");
      return notInPlanCodes;
    }
    BufferedReader reader = null;
    String line = "";
    try {
      reader = new BufferedReader(new FileReader(fileHandle));
      while( (line=reader.readLine()) != null ) {
        parseJORERow(line,notInPlanCodes);
      }
    } catch(Exception e) {
      Log.log("Exception in StudyPlan.parseJOREFile(): " ,e);
    } finally {
      try{
        if (reader != null)
          reader.close();
      }catch(IOException e) {
        Log.log("Exception in StudyPlan.parseJOREFile(): ",e);
      }
    }
    return notInPlanCodes;
  }

  /**
   * Returns StudyPlanElement from Hashtable
   * @param studyGroupID
   * @return
   */
  public StudyPlanElement getStudyPlanElement(int studyGroupID) {
    return (StudyPlanElement)planElements.get(new Integer(studyGroupID));
  }

  public StudyPlanElement getStudyPlanElement(Integer studyGroupID) {
    return (StudyPlanElement)planElements.get(studyGroupID);
  }

  public StudyPlanElement removeStudyPlanElement(int studyGroupID) {
    return (StudyPlanElement)planElements.remove(new Integer(studyGroupID));
  }

  public StudyPlanElement removeStudyPlanElement(Integer studyGroupID) {
    return (StudyPlanElement)planElements.remove(studyGroupID);
  }

  public void openAllNodes(BitSet openNodes) {
    getRootStructureElement().openAllNodes(openNodes);
  }

  public void openAllPlannedNodes(BitSet openNodes) {
    getRootStructureElement().openAllNodes(openNodes, true);
  }

  public void closeAllNodes() {
    getRootStructureElement().closeNodes();
  }

  public void openNodes(BitSet openNodes) {
    try {
      getRootStructureElement().openNodes(openNodes);
    }
    catch (Exception ex) {
    }
  }

// Methods for getting information from database

  /**
   * Initializes studyPlan
   * @param rs2
   * @throws java.lang.Exception
   */
  protected void initStudyPlan(RS2s rs2, int planID) throws Exception {
    if (rs2 != null && rs2.next()) {
      setPlanID(planID);
      setStudyGroupID(rs2.getInt("studygroupid"));
      setOwnerID(rs2.getInt("personid"));
      setName(rs2.getString("name"));
      setPlanStatusID(rs2.getInt("planstatusid"));
      setCreatedAt(rs2.getTimestamp("modifiedon"));
    }
    else {
      throw new Exception("No any studyPlan matching planID=" + planID + ".");
    }

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

  protected void loadPlanFromDB(int planID) throws Exception {
    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
    DB db = new DB("StudyPlan.loadPlanFromDB 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
    initStudyPlan(rs2, planID);

  }

  protected StructureElement loadElementsFromDB() throws Exception {
    StructureElement root = StructureElement.loadElementsFromDB(this);
    loadAcceptancesFromDB();
    return root;
  }

  /**
   * 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 {

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

    loadPlanFromDB(planID);
    loadElementsFromDB();

    //Set organisation name for studyPlan
    String queryOrganisationName = "SELECT name FROM organisationtranslation"
        + " WHERE languageid=" + getUser().getLanguageID()
        + " AND organisationid="
        + getRootStructureElement().getStudyPlanElement().getOrganisationID() + ";";

    RS2s rs2 = null;
    DB db = new DB("StudyPlan.loadFromDB db");
    try {
      db.connect();
      rs2 = new RS2s(db.executeQuery(queryOrganisationName));
    }
    catch (Exception e) {
      Log.log("Exception in StudyPlan.loadFromDB :", e);
      throw new Exception("StudyPlan.loadFromDB: " + e.getMessage());
    }
    finally {
      db.disconnect();
    }

    try {
      while (rs2 != null && rs2.next()) {
        setOrganisationName(rs2.getString("name"));
      }
    } catch (Exception ex) {

    }
  }

  public void loadAcceptancesFromDB() throws Exception {
    acceptances = new Hashtable(); //This will clear old acceptances

    String queryAcceptances = "SELECT a.studyacceptanceid, a.acceptedby,"
        + " a.acceptedon, a.parentstudygroupid, a.studygroupid, a.planid,"
        + " a.studyacceptancelevelid, p.callname, p.lastname"
        + " FROM study_group_acceptance AS a, person AS p"
        + " WHERE a.deleted=false AND p.deleted=false"
        + " AND a.planid=" + getPlanID() + " AND a.acceptedby=p.personid;";
    RS2s rs2 = Tools.getDBResultS("StudyPlan.loadAcceptancesFromDB", queryAcceptances);
    if (rs2 == null) return;
    try {
      while (rs2.next()) {
        Acceptance acceptance = new Acceptance();
        acceptance.setStudyAcceptanceID(rs2.getInt("studyacceptanceid"));
        acceptance.setAcceptedBy(rs2.getInt("acceptedby"));
        acceptance.setAcceptedOn(rs2.getTimestamp("acceptedOn"));
        acceptance.setAcceptorName(rs2.getString("callname") + " " +
                                   rs2.getString("lastname"));
        acceptance.setChanged(false);

        StructureElement structure = getStructureElementByStudyGroupID(
            rs2.getInt("parentstudygroupid"), rs2.getInt("studygroupid"));

        putAcceptance(new IntGroup(new int[] {structure.getGroupRelationID(),
                                     rs2.getInt("studyacceptancelevelid")}),
                        acceptance);
      }
    }
    catch (Exception ex) {
      Log.log("StudyPlan.loadAcceptancesFromDB: ", ex);
      if (DEBUG)
        System.out.println("Exception in StudyPlan.loadAcceptancesFromDB:");
    }
  }

// 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() throws Exception {
    if (getPlanID() == 0) {
      try {
        insertToDB();
      }
      catch (Exception e) {
        throw new Exception("Saving to database failed! " + e.getMessage());
      }
    }
    else {
      try {
        updateToDB();
      }
      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() throws Exception {

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

    //Query to add study_plan to database
    //Here we use personID, because we are creating new plan. Then creator
    //must be also owner of this plan.
    String insertQuery =
        "INSERT INTO study_plan (deleted, planid, studygroupid,"
        + " personid, planstatusid, name, modifiedon) "
        + " VALUES (false, " + getPlanID() + ", " + getStudyGroupID()
        + ", " + getPersonID() + ", " + getPlanStatusID() + ", '"
        + getName() + "', '" + getCreatedAt() + "' )";

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

      //Add studyPlanElements in database
      if (getRootStructureElement() != null)
        getRootStructureElement().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() throws Exception {

    //Here we use ownerID, if person who is changing the plan, is different
    //from the original owner of this studyPlan.
    String sql = "UPDATE study_plan SET studygroupid=" + getStudyGroupID()
        + ", personid=" + getOwnerID() + "," + " planstatusid="
        + getPlanStatusID() + ", name='" + getName() + "'"
        + " WHERE deleted=false" + " AND planid=" + getPlanID();
    DB db = new DB("StudyPlan.updateToDB db");
    try {
      db.connect();
      db.executeUpdate(sql);

      //Save studyPlanElements in database
      if (getRootStructureElement() != null)
        getRootStructureElement().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 rows matching planid deleted in tables:
      //- study_unit
      //- study_group_comment_relation
      //- study_group_relation
      //- study_group_acceptance

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

  public void saveAcceptancesToDB() throws Exception {
    Enumeration e = acceptances.keys();
    while (e != null && e.hasMoreElements()) {
      IntGroup key = (IntGroup) e.nextElement();
      Acceptance acceptance = (Acceptance) acceptances.get(key);
      StructureElement structure = getStructureElement(key.getInt(Acceptance.
          KEY_GROUPRELATIONID));

      if (structure == null) {
        String msg = "StudyPlan.saveAcceptancesToDB: "
            + "Couldn't find StructureElement with groupRelationID=" +
            key.getInt(Acceptance.KEY_GROUPRELATIONID) +
            ". Acceptance stamp for this element is not saved.";
        Log.log(msg);
        if (DEBUG) {
          System.out.println(msg);
        }
        acceptances.remove(key);
        continue;
      }

      //If acceptance is marked as deleted, then it will be marked as
      //deleted in database also.
      acceptance.saveToDB(structure.getParentStudyGroupID(),
                          structure.getStudyGroupID(),
                          key.getInt(Acceptance.KEY_ACCEPTANCELEVELID),
                          getPlanID());

      if (acceptance.getDelete())
        acceptances.remove(key);
    }
  }

// Methods for printing to StringBuffer

  /**
   * Prints list of study plans of user
   * @param buff buffer where to print
   * @return true, if some example studyplans found, else return false
   * @throws Exception
   */
  public static boolean printStudyPlans(StringBuffer buff, int personid, String status,
                                     String srcName, String href)
      throws Exception {
    return printStudyPlans(buff, personid, status, srcName, href, false, 0);
  }

  /**
   * Prints list of study plans of user
   * @param buff buffer where to print
   * @return true, if some example studyplans found, else return false
   * @throws Exception
   */
  public static boolean printStudyPlans(StringBuffer buff, int personid, String status,
                                     String srcName, String href, boolean combobox)
      throws Exception {
    return printStudyPlans(buff, personid, status, srcName, href, combobox, 0);
  }

  /**
   * Prints list of study plans of user
   * @param buff buffer where to print
   * @param user current user
   * @return true, if some example studyplans found, else return false
   * @throws Exception
   */
  public static boolean printStudyPlans(StringBuffer buff, int personid,
                                     String status,
                                     String srcName, String href,
                                     boolean combobox, int studyGroupID) throws
      Exception {

    User user = new User();

    //if( status == 1 || status == 2 ) {

    srcName = kotkabeans.Encoder.SQLEncode(srcName.trim());

    if (personid == 0 && srcName.equals("")
        && (status.equals("" + StudyPlan.STATUS_EXAMPLE) && studyGroupID <= 0))
      return false;

    String studyPlanQuery =
        "SELECT s.personid, s.name, s.modifiedon, s.planid, s.planstatusid,"
        + " p.firstnames, p.lastname, g.organisationid, g.valid_from_year, o.name as oname "
        //+ " ,ga.studyacceptancelevelid, g.studygroupid "
        + " , g.studygroupid "
        + "FROM study_plan as s, person as p, organisationtranslation as o"
        + ", study_group as g "
        //+ ", study_group as g LEFT JOIN study_group_acceptance as ga "
        //+ "ON (g.studygroupid = ga.studygroupid AND ga.parentstudygroupid is null "
        //+ " AND ga.deleted = false) "
        + " WHERE s.deleted = false AND p.deleted=false AND g.deleted = false AND o.deleted = false"
        + (personid != 0 ? " AND s.personid = " + personid : "")
        + (studyGroupID != 0 ? " AND s.studygroupid = " + studyGroupID : "")
        + " AND s.personid = p.personid"
        + " AND s.studygroupid = g.studygroupid"
        + " AND o.organisationid = g.organisationid"
        + " AND o.languageid = 2"
        //+ " AND g.studygroupid = u.studygroupid"
        //+ " AND (u.unitid = ua.unitid OR ua.unitid is NULL)"
        +
        (!srcName.equals("") ? " AND (lower(s.name) LIKE lower('%" + srcName + "%'))" :
         "");
    /*if(status == 1)*/studyPlanQuery += " AND s.planstatusid IN (" + status +
        ") ";
    //else studyPlanQuery += " AND s.planstatusid = '"+STATUS_OUTDATED+"'";

    studyPlanQuery += " ORDER BY s.planstatusid DESC, s.modifiedon DESC";

    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);

      int tsek = 0;

      if (rs2.count() <= 0) return false;

      while (rs2 != null && rs2.next()) {
        int planstatus = rs2.getInt("planstatusid");
        if (planstatus != tsek && status != "" + STATUS_OUTDATED && !combobox) {
          buff.append("\n<tr><th colspan=\"" + (!srcName.equals("") ? 4 : 3) +
                      "\"><h1>"
                      + getPlanStatus(planstatus).replaceAll(getPlanStatus(
              planstatus).substring(0, 1),
              getPlanStatus(planstatus).substring(0, 1).toUpperCase())
                      + "</h1></th>\n</tr>\n");

          buff.append("\n<tr><th>" + user.T("Nimi") + "</th><th>" +
                      user.T("Luotu") + "</th>" +
                      (!srcName.equals("") ?"<th>"+user.T("Tekijä")+"</th>" : "" )
                      + "<th>"+ user.T("Laitos") +
                      "</th><th>" + user.T("Vuosi") + "</th>" + "<th>" +
                      user.T("Hyväksytty") + "</th></tr>\n");
          tsek = planstatus;
        }

        if (combobox) {
          buff.append("<option value=\"" + rs2.getString("planid") + "\">" +
                      rs2.getString("name") + "</option>\n");
        }
        else {
          String acceptanceQuery =
              "SELECT sa.studyacceptancelevelid, p.lastname, p.firstnames,"
              +" sa.acceptedon from study_group_acceptance as sa, person as p"
              +" WHERE sa.deleted = false AND p.deleted = false"
              +" AND sa.planid = "+rs2.getString("planid")
              +" AND sa.studygroupid = "+rs2.getString("studygroupid")
              +" AND sa.acceptedby = p.personid"
              + ";" ;

          String acceStr = "";
          RS2s rs2a = new RS2s(db.executeQuery(acceptanceQuery));
          while(rs2a != null && rs2a.next()) {
            int acce = rs2a.getInt("studyacceptancelevelid");
            if(acce == Acceptance.STRUCTURE) {
              if(acceStr.length()>0) {
                acceStr += ", ";
              }
              acceStr += user.T("Rakenne hyväksytty");
              acceStr += ", " + rs2a.getString("firstnames")
                       + " " + rs2a.getString("lastname");
              acceStr += ", " + time.dbTimestapmToOutFormat(
                                 rs2a.getString("acceptedon"),"d.M.yyyy"
                                );
            }
            if(acce == Acceptance.TIME) {
              if(acceStr.length()>0) {
                acceStr += ", ";
              }
              acceStr += user.T("Aikataulu hyväksytty");
              acceStr += ", " + rs2a.getString("firstnames")
                       + " " + rs2a.getString("lastname");
              acceStr += ", " + time.dbTimestapmToOutFormat(
                                 rs2a.getString("acceptedon"),"d.M.yyyy"
                                );
            }
          }

          buff.append("\n<tr><td><a href=" + href + "?" + "planid="
                      + rs2.getString("planid") + ">"
                      + rs2.getString("name") + "</a></td>"
                      + "<td>"
                      + time.monthToOutFormat( (rs2.getString("modifiedon")).
                                            substring(0, 10))
                      + "</td>"
                      +
                      (!srcName.equals("") ?
                       "<td>" + rs2.getString("firstnames") + " " +
                       rs2.getString("lastname") + "</td>" : "")
                      + "<td>" + rs2.getString("oname") + "</td>"
                      + "<td>" + rs2.getString("valid_from_year") + "</td>"
                      + "<td>" +acceStr+ "</td>"
                      //+ "<td></td><td></td>"
                      + "</tr>\n");
        }
      }

    }

    catch (Exception e) {
      Log.log("Exception in StudyPlan.printStudyPlans", e);
      throw e;
    }
    finally {
      db.disconnect();
    }
    //} else buff.append("Wrong status id!");
    return true;

  }

  /**
   * Prints path of current element in structure.
   * @param structure
   * @return
   */
  public String printStructurePath(StructureElement structure) {
    if (structure == null) return "";
    StudyPlanElement studyPlanElement = structure.getStudyPlanElement();
    String name = studyPlanElement.getName(), pname = "", path = "", code = studyPlanElement.getCode();
    StructureElement parent = structure.getParent();
    StringBuffer buff = new StringBuffer();
    StudyPlanElement parentele = null;
    Vector all = new Vector();

    while (parent != null) {
      parentele = parent.getStudyPlanElement();
      pname = parentele.getName();
      all.add(pname);
      parent = parent.getParent();
    }
    buff.append("<p><strong>");
    int i = all.size();
    while (i > 0) {
      buff.append(all.get(i - 1) + " > ");
      i--;
    }
    buff.append(code + " " + name + "</strong></p>");
    return buff.toString();
  }

// Methods for printing

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

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

  public String printRecursiveAcceptor() {
    if (getRootStructureElement() == null)
      return "";
    return getRootStructureElement().printRecursiveAcceptor();
  }

  public static String printStudyPlanStatesCombo(User user, int selected) {
    StringBuffer buff = new StringBuffer();
    buff.append("<select name=\"planstatus\">");
    for(int i=1;i<STATUS_STRING.length;i++) {

      //If user is secretary, then show example status -option
      if (i == STATUS_EXAMPLE &&
          !user.hasRight(Right.ACCESS_RIGHT, Right.SECRETARY))
        continue;

      buff.append("<option value=\""+i+"\"");
      if(selected==i) buff.append(" selected ");
      buff.append(">");
      buff.append(user.T(STATUS_STRING[i]));
      buff.append("</option>");
    }
    buff.append("</select>");
    return buff.toString();
  }

  public static boolean checkUserRights(HttpServletRequest request,
                                        StudyPlan plan) {
    User user = (User)request.getSession().getAttribute("user");

    //Check if userid differs from plan ownerid
    if(plan.getPersonID() != plan.getOwnerID())
      return false;

    return true;
  }

// Methods for searching StudyPlanElement

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

  public StructureElement getStructureElement(int groupRelationID) {
    return getRootStructureElement().getStructureElement(groupRelationID);
  }

  public StructureElement getStructureElementByStudyGroupID(int studyGroupID) {
    return getRootStructureElement().getElementByStudyGroupID(
        studyGroupID);
  }

  public StructureElement getStructureElementByStudyGroupID(int parentStudyGroupID,
      int studyGroupID) {
    return getRootStructureElement().getElementByStudyGroupID(
        parentStudyGroupID, studyGroupID);
  }

  public StructureElement getStructureElementByCode(String code) {
    return getRootStructureElement().getStructureElementByCode(code);
  }


//  /**
//   * Get element matching unitID.
//   * @param unitID the unitID of searched StudyPlanElement
//   * @return found element, or null if not found
//   */
//  public StudyPlanElement getElementByUnitID(int unitID) {
//    if (getRootStructureElement() == null)
//      return null;
//    return getRootStructureElement().getElementByUnitID(unitID);
//  }

//  /**
//   * Get all elements in structure.
//   * @return elements in Vector
//   */
//  public Vector getElements() {
//    if (getRootStudyPlanElement() == null)
//      return null;
//
//    return getRootStudyPlanElement().getElements();
//  }

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

  /**
   * Get all planned elements
   * @return planned studyPlanElements in vector
   */
  public Vector getPlannedElements() {
    Vector planned = new Vector();
    Enumeration e = studyPlanElements();
    if (e == null) return null;
    while (e.hasMoreElements()) {
      StudyPlanElement element = (StudyPlanElement)e.nextElement();
      if (element.getPlanned() != null)
        planned.add(element);
    }
    return planned;
  }

// Methods for getting ids

  /**
   * Returns studyGroupIDs of elements in structure.
   * @return studyGroupIDs
   */
  public int[] getStudyGroupIDs() {
    Enumeration e = planElements.keys();
    int[] ids = new int[planElements.size()];
    if (e == null) return null;
    int i = 0;
    while (e.hasMoreElements()) {
      ids[i++] = ((StudyPlanElement)e.nextElement()).getStudyGroupID();
    }
    return ids;
  }

  /**
   * Returns unitIDs of elements in structure.
   * @return unitIDs
   */
  public int[] getUnitIDs() {
    int ids[] = new int[planElements.size()];
    Enumeration i = planElements.elements();
    int index = 0;
    while (i.hasMoreElements()) {
      ids[index++] = ( (StudyPlanElement) i.nextElement()).getUnitID();
    }
    return ids;
  }

  /**
   * Set comments for all studygroups
   *
   * @throws java.lang.Exception
   */
  public void initComments() throws Exception {
    Vector comments = StudyGroupComments.getPlanComments(getPlanID());

    Enumeration p = studyPlanElements();
    if (p == null) return;
    while (p.hasMoreElements()) {
      StudyPlanElement ele = (StudyPlanElement)p.nextElement();
      ele.setStudyGroupComments(
          new StudyGroupComments(comments,ele.getStudyGroupID() ));
    }
  }

  /**
   * Sets default values for comment Checkboxes in different pages
   * @throws java.lang.Exception
   */
  public void initCommentSelections() throws Exception {
    CheckBoxGroup columns = new CheckBoxGroup("structure_columns",user);
    CheckBoxGroup scheduleColumns = new CheckBoxGroup("schedule_columns",user);
    CheckBoxGroup commentFields = new CheckBoxGroup("comment_fields",user);
    CheckBoxGroup commentTypes = StudyGroupComments.getCommentTypesAsCBGroup(user);

    columns.addCheckBox(user.T("Opintoviikot"),PlannedElement.COLS_OV,true);
    columns.addCheckBox(user.T("Aloitusaika"),PlannedElement.COLS_STARTTIME, true);
    columns.addCheckBox(user.T("Lopetusaika"),PlannedElement.COLS_ENDTIME, true);
    columns.addCheckBox(user.T("Suoritusaika"),PlannedElement.COLS_COMPLAT,true);
    columns.addCheckBox(user.T("Arvolause"),PlannedElement.COLS_GRADE, true);
    columns.addCheckBox(user.T("Perustelut"),PlannedElement.COLS_COMMENT, false);
    columns.updateChecked(request);
    setColumns(columns);

    scheduleColumns.addCheckBox(user.T("Opintoviikot"),PlannedElement.COLS_OV,true);
    scheduleColumns.addCheckBox(user.T("Aloitusaika"),PlannedElement.COLS_STARTTIME, true);
    scheduleColumns.addCheckBox(user.T("Lopetusaika"),PlannedElement.COLS_ENDTIME, true);
    scheduleColumns.addCheckBox(user.T("Suoritusaika"),PlannedElement.COLS_COMPLAT,true);
    scheduleColumns.addCheckBox(user.T("Arvolause"),PlannedElement.COLS_GRADE, true);
    scheduleColumns.updateChecked(request);
    setScheduleColumns(scheduleColumns);

    commentFields.addCheckBox(user.T("Otsikko"),StudyGroupComment.TITLE,true);
    commentFields.addCheckBox(user.T("Tyyppi"),StudyGroupComment.TYPE,true);
    commentFields.addCheckBox(user.T("Kommentti"),StudyGroupComment.TEXT,true);
    commentFields.addCheckBox(user.T("Käyttäjä"),StudyGroupComment.USER,true);
    commentFields.addCheckBox(user.T("Pvm"),StudyGroupComment.DATE,true);
    commentFields.updateChecked(request);
    setCommentFields(commentFields);

    commentTypes.updateChecked(request);
    setCommentTypes(commentTypes);
  }

//Methods for counting elements

  /**
   * Counts elements under root element.
   * In count root element is also included.
   * @return number of elements in structure
   */
  public int countElements() {
    if (getRootStructureElement() == null)
      return 0;

    return getRootStructureElement().countElements();
  }

  public int countElement(int studyGroupID) {
    return getRootStructureElement().countElement(studyGroupID);
  }

// Methods for accessing acceptions

  public void deleteAcceptances(int acceptanceLevel) {
    Enumeration e = acceptanceKeys();
    while (e != null && e.hasMoreElements()) {
      IntGroup key = (IntGroup)e.nextElement();
      if (acceptanceLevel == key.getInt(Acceptance.KEY_ACCEPTANCELEVELID)) {
        Acceptance acceptance = getAcceptance(key);
        acceptance.setDelete(true);
      }
    }
  }

  public Acceptance getAcceptance(int groupRelationID,
                                  int acceptanceLevelID) {
    Acceptance found = (Acceptance) acceptances.get(new IntGroup(new int[] {
        groupRelationID, acceptanceLevelID}));
//    if (DEBUG) {
//      System.out.print("Search acceptance:\nstudyGroupID=" + studyGroupID);
//      System.out.print(", subGroupID=" + subGroupID);
//      System.out.print(", acceptanceLevelID=" + acceptanceLevelID + "\n");
//      if (found != null){
//        System.out.println("Found Acceptance: " + found);
//        System.out.println("AcceptanceID=" + found.getStudyAcceptanceID());
//        System.out.println("AcceptedBy=" + found.getAcceptedBy());
//        System.out.println("AcceptedOn=" + found.getAcceptedOn());
//      }
//      else {
//        System.out.println(" - not found.");
//      }
//    }
    return found;
  }

  public Acceptance getAcceptance(IntGroup key) {
    return (Acceptance) acceptances.get(key);
  }


  public int copyAcceptedStudyPlan() throws Exception {
    return copyStudyPlan(getName(), 0, true);
  }

  public int copyStudyPlan(String newName) throws Exception {
    return copyStudyPlan(newName, 0, false);
  }

  public int copyStudyPlan(String newName, int yearsToAddToCurrent) throws Exception {
    return copyStudyPlan(newName, yearsToAddToCurrent, false);
  }

  /**
   * Copy studyplan. Copies whole studyplan to new planid
   * @param newName name for new study plan
   * @return new planid
   * @throws java.lang.Exception
   */
  public int copyStudyPlan(String newName, int yearsToAddToCurrent, boolean accepted) throws Exception {

    //Remove html-tags and sql-strings from name
    String encodedName = tools.StringTools.removeHtmlTags(newName);
    encodedName = tools.StringTools.stripChars(encodedName, "\"'&?=");
    encodedName = tools.StringTools.SQLEncode(encodedName);

    StringBuffer relaInsertSQL = new StringBuffer();
    StringBuffer relaSelectSQL = new StringBuffer();
    StringBuffer studyPlanSQL = new StringBuffer();
    StringBuffer unitSelectSQL = new StringBuffer();
    StringBuffer unitInsertSQL = new StringBuffer();
    StringBuffer acceSelectSQL = new StringBuffer();
    StringBuffer acceInsertSQL = new StringBuffer();

    //id for new plan
    int newPlanID = AutoNumber.getNumber("study_plan", "planid");
    String currentTime = time.calendarToDBString(time.getCalendarInstance());


    //make copy of study_plan table
    studyPlanSQL.append("INSERT INTO study_plan (deleted, planid, ");
    studyPlanSQL.append("studygroupid, personid, planstatusid, name, ");
    studyPlanSQL.append("modifiedon, parent");
    if (accepted) {
      studyPlanSQL.append(", acceptedon");
    }
    studyPlanSQL.append(") VALUES (false, "+newPlanID+", ");
    studyPlanSQL.append(getStudyGroupID()+", "+getPersonID()+", ");
    studyPlanSQL.append(StudyPlan.STATUS_UNREADY+", '");
    studyPlanSQL.append((encodedName.trim().equals("") ? getName() : encodedName ));
    studyPlanSQL.append("', '");
    studyPlanSQL.append(currentTime);
    studyPlanSQL.append("', ");
    studyPlanSQL.append(getPlanID());
    if (accepted) {
      studyPlanSQL.append(", '");
      studyPlanSQL.append(currentTime);
      studyPlanSQL.append("'");
    }
    studyPlanSQL.append(")");

    //get all elements that need to be copied in study_plan_relation table
    relaSelectSQL.append("SELECT studygroupid, subgroupid, courseid, ordernum");
    relaSelectSQL.append(
        ", compulsory, externalstudyid FROM study_group_relation ");
    relaSelectSQL.append("WHERE deleted=false AND planid=" + getPlanID());

    //get all studyUnits from old plan
    unitSelectSQL.append("SELECT planid, begintime, endtime, ");
    unitSelectSQL.append("studygroupid, courseinstanceid FROM study_unit ");
    unitSelectSQL.append("WHERE deleted=false AND planid="+getPlanID());

    //get all acceptance stamps to copy
    acceSelectSQL.append("SELECT acceptedby, acceptedon, studycommentid, ");
    acceSelectSQL.append("parentstudygroupid, studygroupid, ");
    acceSelectSQL.append("studyacceptancelevelid, statusid FROM study_group_acceptance ");
    acceSelectSQL.append("WHERE deleted=false AND planid="+getPlanID());

    DB db = new DB("StudyPlan.copyStudyPlan db");
    try {
      db.connect();

      //copy study_plan table
      db.executeUpdate(studyPlanSQL.toString());

      //copy study_plan_relation table
      RS2s rs2rela = new RS2s(db.executeQuery(relaSelectSQL.toString()));
      while (rs2rela != null && rs2rela.next()) {
        relaInsertSQL.append("INSERT INTO study_group_relation ");
        relaInsertSQL.append(
            "(deleted, grouprelationid, studygroupid, subgroupid,");
        relaInsertSQL.append(
            "courseid, ordernum, planid, compulsory, externalstudyid) ");
        relaInsertSQL.append("VALUES (false, " +
                             AutoNumber.getNumber("study_group_relation",
                                                  "grouprelationid"));
        relaInsertSQL.append(", " + rs2rela.getInt("studygroupid") + ", " +
                             (rs2rela.getInt("subgroupid") == 0 ? "null" :
                              rs2rela.getString("subgroupid")));
        relaInsertSQL.append(", " +
                             (rs2rela.getInt("courseid") == 0 ? "null" :
                              rs2rela.getString("courseid")) + ", " +
                             (rs2rela.getInt("ordernum") == 0 ? "null" :
                              rs2rela.getString("ordernum")));
        relaInsertSQL.append(", " + newPlanID + ", " +
                             (rs2rela.getInt("compulsory") == 0 ? "null" :
                              rs2rela.getString("compulsory")));
        relaInsertSQL.append(", " +
                             (rs2rela.getInt("externalstudyid") == 0 ? "null" :
                              rs2rela.getString("externalstudyid")));
        relaInsertSQL.append("); ");
      }
      db.executeUpdate(relaInsertSQL.toString());

      //copy study_unit table
      RS2s rs2unit = new RS2s(db.executeQuery(unitSelectSQL.toString()));

      int diff = 0;
      int once = 1;

      while(rs2unit != null && rs2unit.next()) {

        if (once == 1 && yearsToAddToCurrent != 0) {
          diff = yearsToAddToCurrent - rs2unit.getTimestamp("begintime").getYear()-1900;
          once = 0;
        }

         unitInsertSQL.append("INSERT INTO study_unit (deleted, unitid, ");
         unitInsertSQL.append("planid, begintime, endtime, studygroupid, ");
         unitInsertSQL.append("courseinstanceid) VALUES (false, ");
         unitInsertSQL.append(AutoNumber.getNumber("study_unit","unitid"));
         unitInsertSQL.append(", " + newPlanID + ", ");

         Timestamp btime = rs2unit.getTimestamp("begintime");
         if (yearsToAddToCurrent != 0 && ComboYear.isValidPlanned(btime)) {
           btime.setYear(btime.getYear() + diff);
         }

         unitInsertSQL.append(
            (btime == null ?
             "" + btime
             : "'" + btime + "'") + ", ");

         Timestamp etime = rs2unit.getTimestamp("endtime");
         if (yearsToAddToCurrent != 0 && ComboYear.isValidPlanned(etime)) {
           etime.setYear(etime.getYear() + diff);
         }

         unitInsertSQL.append(
            (etime == null ?
             "" + etime
             : "'" + etime + "'") + ", ");

         unitInsertSQL.append(rs2unit.getInt("studygroupid") + ", ");
         unitInsertSQL.append(rs2unit.getInt("courseinstanceid") + "); ");
      }
      db.executeUpdate(unitInsertSQL.toString());

      //If studyPlan is example studyplan, don't copy acceptance stamps
      if (getPlanStatusID() != STATUS_EXAMPLE) {

        //copy study_group_acceptance table
        RS2s rs2acce = new RS2s(db.executeQuery(acceSelectSQL.toString()));
        while (rs2acce != null && rs2acce.next()) {
          acceInsertSQL.append("INSERT INTO study_group_acceptance (deleted, ");
          acceInsertSQL.append("studyacceptanceid, acceptedby, acceptedon, ");
          acceInsertSQL.append(
              "studycommentid, parentstudygroupid, studygroupid, ");
          acceInsertSQL.append(
              "planid, studyacceptancelevelid, statusid ) VALUES ");
          acceInsertSQL.append("(false, " +
                               AutoNumber.getNumber("study_group_acceptance",
              "studyacceptanceid") + ", ");
          acceInsertSQL.append(rs2acce.getInt("acceptedby") + ", ");
          acceInsertSQL.append("'" + rs2acce.getTimestamp("acceptedon") + "', ");
          acceInsertSQL.append(
              (rs2acce.getInt("studycommentid") == 0 ? "null" :
               rs2acce.getString("studycommentid")
               ) + ", ");
          acceInsertSQL.append(
              (rs2acce.getInt("parentstudygroupid") == 0 ? "null" :
               rs2acce.getString("parentstudygroupid")
               ) + ", ");
          acceInsertSQL.append(rs2acce.getInt("studygroupid") + ", ");
          acceInsertSQL.append(newPlanID + ", " +
                               rs2acce.getInt("studyacceptancelevelid") + ",");
          acceInsertSQL.append(
              (rs2acce.getInt("statusid") == 0 ? "null" :
               rs2acce.getString("statusid"))
              + "); ");
        }
        db.executeUpdate(acceInsertSQL.toString());

      }

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


    //If studyPlan is example studyplan, don't copy comments
    if (getPlanStatusID() != STATUS_EXAMPLE) {

      //copy study_group_comment_relation table
      StudyGroupComments.copyPlanComments(getPlanID(), newPlanID);

    }

    return newPlanID;
  }

  public boolean userIsOwner() {
    return getOwnerID() == getPersonID();
  }

}