gt:             if (c.getPriority() != null) {
context: /*
   * Funambol is a mobile platform developed by Funambol, Inc.
   * Copyright (C) 2006 - 2007 Funambol, Inc.
   *
   * This program is free software; you can redistribute it and/or modify it under
   * the terms of the GNU Affero General Public License version 3 as published by
   * the Free Software Foundation with the addition of the following permission
   * added to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED
   * WORK IN WHICH THE COPYRIGHT IS OWNED BY FUNAMBOL, FUNAMBOL DISCLAIMS THE
   * WARRANTY OF NON INFRINGEMENT  OF THIRD PARTY RIGHTS.
   *
   * This program is distributed in the hope that it will be useful, but WITHOUT
   * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
   * FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
   * details.
   *
   * You should have received a copy of the GNU Affero General Public License
   * along with this program; if not, see http://www.gnu.org/licenses or write to
   * the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
   * MA 02110-1301 USA.
   *
   * You can contact Funambol, Inc. headquarters at 643 Bair Island Road, Suite
   * 305, Redwood City, CA 94063, USA, or at email address info@funambol.com.
   *
   * The interactive user interfaces in modified source and object code versions
   * of this program must display Appropriate Legal Notices, as required under
   * Section 5 of the GNU Affero General Public License version 3.
   *
   * In accordance with Section 7(b) of the GNU Affero General Public License
   * version 3, these Appropriate Legal Notices must retain the display of the
   * "Powered by Funambol" logo. If the display of the logo is not reasonably
   * feasible for technical reasons, the Appropriate Legal Notices must display
   * the words "Powered by Funambol".
   */
  package com.funambol.foundation.items.dao;
  
  import java.text.ParseException;
  import java.text.SimpleDateFormat;
  
  import java.util.ArrayList;
  import java.util.Date;
  import java.util.GregorianCalendar;
  import java.util.LinkedList;
  import java.util.List;
  import java.util.Locale;
  import java.util.TimeZone;
  
  import org.apache.commons.lang.StringUtils;
  
  import com.funambol.framework.server.store.NotFoundException;
  import com.funambol.framework.tools.DBTools;
  
  import com.funambol.server.config.Configuration;
  
  import com.funambol.common.pim.common.PropertyWithTimeZone;
  import com.funambol.common.pim.utility.TimeUtils;
  import com.funambol.common.pim.calendar.Calendar;
  import com.funambol.common.pim.calendar.CalendarContent;
  import com.funambol.common.pim.calendar.Event;
  import com.funambol.common.pim.calendar.ExceptionToRecurrenceRule;
  import com.funambol.common.pim.calendar.RecurrencePattern;
  import com.funambol.common.pim.calendar.Reminder;
  import com.funambol.common.pim.calendar.Task;
  
  import com.funambol.common.pim.common.Property;
  import com.funambol.foundation.exception.DAOException;
  import com.funambol.foundation.items.model.CalendarWrapper;
  import com.funambol.foundation.util.Def;
  
  /*
   * This class implements methods to access contacts data
   * in domino server datastore
   *
   * @version $Id: PIMCalendarDAO.java,v 1.1.1.1 2008-03-20 21:38:36 stefano_fornari Exp $
   **/
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.ResultSetMetaData;
  import java.sql.SQLException;
  import java.sql.Timestamp;
  import java.sql.Types;
  import java.util.SortedSet;
  import java.util.TreeSet;
  
  public class PIMCalendarDAO extends EntityDAO {
  
      // --------------------------------------------------------------- Constants
  
      private static final int CALENDAR_BOTH_TYPES = 0;
      private static final int CALENDAR_EVENT_TYPE = 1;
      private static final int CALENDAR_TASK_TYPE = 2;
      private static final String[] SQL_FILTER_BY_TYPE = {
          "",                                          // any CalendarContent
          "AND type = '" + CALENDAR_EVENT_TYPE + "' ", // Event
          "AND type = '" + CALENDAR_TASK_TYPE + "' "   // Task
      };
  
      private static final String SQL_PIM_CALENDAR_TABLE = "fnbl_pim_calendar";
      private static final String SQL_PIM_CALENDAR_EXCEPTION_TABLE =
              "fnbl_pim_calendar_exception";
      private static final String SQL_ORDER_BY_ID = "ORDER BY id";
      private static final String SQL_GET_FNBL_PIM_CALENDAR_ID_LIST =
              "SELECT id FROM " + SQL_PIM_CALENDAR_TABLE + " ";
      private static final String SQL_GET_FNBL_PIM_CALENDAR_ID_LIST_BY_USER =
              "SELECT id, dend FROM " + SQL_PIM_CALENDAR_TABLE
              + " WHERE userid = ? "
              + "AND status <> 'D' ";
      private static final String
              SQL_GET_FNBL_PIM_CALENDAR_ID_LIST_BY_USER_TIME_STATUS =
              SQL_GET_FNBL_PIM_CALENDAR_ID_LIST
              + "WHERE "
              + "userid = ? "
              + "AND last_update > ? "
              + "AND last_update < ? "
              + "AND status = ? ";
      private static final String SQL_AND_NO_SUBJECT_IS_SET =
              "AND ((subject IS null) OR (subject = '')) ";
      private static final String SQL_AND_SUBJECT_EQUALS_QUESTIONMARK =
              "AND LOWER(subject) = ? ";
      private static final String SQL_AND_NO_DSTART_IS_SET =
              "AND dstart IS null ";
      private static final String SQL_AND_DSTART_EQUALS_QUESTIONMARK =
              "AND dstart = ? ";
      private static final String SQL_AND_NO_DEND_IS_SET =
              "AND dend IS null ";
      private static final String SQL_AND_DEND_EQUALS_QUESTIONMARK =
              "AND dend = ? ";
      private static final String SQL_AND_DEND_IN_INTERVAL =
              "AND dend >= ? AND dend <= ? ";
      private static final String SQL_AND_DSTART_IN_INTERVAL =
              "AND dstart >= ? AND dstart <= ? ";
      private static final String SQL_GET_STATUS_BY_ID_USER_TIME =
              "SELECT status FROM " + SQL_PIM_CALENDAR_TABLE
              + " WHERE id = ? AND userid = ? AND last_update > ? ";
      private static final String SQL_GET_FNBL_PIM_CALENDAR =
              "SELECT id, userid, last_update, status, all_day, body, busy_status, " +
              "categories, companies, duration, dend, importance, " +
              "location, meeting_status, mileage, reminder_time," +
              "reminder_repeat_count, reminder, " +
              "reminder_sound_file, reminder_options, reply_time, sensitivity, " +
              "dstart, subject, rec_type, rec_interval, rec_month_of_year, " +
              "rec_day_of_month, rec_day_of_week_mask, rec_instance, rec_start_date_pattern, " +
              "rec_no_end_date, rec_end_date_pattern, rec_occurrences, type, " +
              "completed, percent_complete, folder, " +
              "dstart_tz, dend_tz, reminder_tz " +
              "FROM " + SQL_PIM_CALENDAR_TABLE + " ";
      private static final String SQL_CLAUSE_GET_CALENDARS_BY_ID_USERID =
              "WHERE id = ? AND userid = ? ";
      private static final String
              SQL_GET_FNBL_PIM_CALENDAR_BY_ID_USERID =
              SQL_GET_FNBL_PIM_CALENDAR +
              SQL_CLAUSE_GET_CALENDARS_BY_ID_USERID;
      private static final String
              SQL_GET_FNBL_PIM_CALENDAR_EXCEPTION_BY_CALENDAR =
              "SELECT calendar, addition, occurrence_date FROM "
              + SQL_PIM_CALENDAR_EXCEPTION_TABLE + " WHERE calendar = ? ";
      private static final String SQL_INSERT_INTO_FNBL_PIM_CALENDAR =
              "INSERT INTO " + SQL_PIM_CALENDAR_TABLE + " " +
              "(id, userid, last_update, status, all_day, body, busy_status, " +
              "categories, companies, duration, dend, importance, location, " +
              "meeting_status, mileage, reminder_time, reminder_repeat_count, " +
              "reminder, reminder_sound_file, reminder_options, reply_time, " +
              "sensitivity, dstart, subject, rec_type, rec_interval, " +
              "rec_month_of_year, rec_day_of_month, rec_day_of_week_mask, " +
              "rec_instance, rec_start_date_pattern, rec_no_end_date, " +
              "rec_end_date_pattern, rec_occurrences, type, completed, " +
              "percent_complete, folder, " +
              "dstart_tz, dend_tz, reminder_tz) " +
              "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
              "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
      private static final String SQL_INSERT_INTO_FNBL_PIM_CALENDAR_EXCEPTION =
              "INSERT INTO " + SQL_PIM_CALENDAR_EXCEPTION_TABLE + " (calendar, " +
              "addition, occurrence_date) " +
              "VALUES(?, ?, ?)";
      private static final String SQL_DELETE_CALENDAR_BY_ID_USERID =
              "UPDATE " + SQL_PIM_CALENDAR_TABLE + " SET status = 'D', " +
              "last_update = ? WHERE id = ? AND userid = ? ";
      private static final String SQL_DELETE_CALENDARS_BY_USERID =
               "UPDATE " + SQL_PIM_CALENDAR_TABLE + " SET status = 'D', " +
               "last_update = ? WHERE status <> 'D' AND userid = ? ";
       private static final String SQL_DELETE_CALENDAR_EXCEPTIONS_BY_CALENDAR =
               "DELETE FROM " + SQL_PIM_CALENDAR_EXCEPTION_TABLE
               + " WHERE calendar = ? ";
      private static final String SQL_UPDATE_FNBL_PIM_CALENDAR_BEGIN =
              "UPDATE " + SQL_PIM_CALENDAR_TABLE + " SET ";
      private static final String SQL_UPDATE_FNBL_PIM_CALENDAR_END =
              " WHERE id = ? AND userid = ? ";
      private static final String SQL_GET_CHANGED_CALENDARS_BY_USER_AND_LAST_UPDATE =
              "select id,status from fnbl_pim_calendar where userid=? and last_update>? and last_update<? ";
  
      private static final String SQL_EQUALS_QUESTIONMARK       = " = ?"     ;
      private static final String SQL_EQUALS_QUESTIONMARK_COMMA = " = ?, "   ;
      private static final String SQL_EQUALS_NULL_COMMA         = " = null, ";
      private static final String SQL_EQUALS_ONE_COMMA          = " = '1', " ;
      private static final String SQL_EQUALS_ZERO_COMMA         = " = '0', " ;
      private static final String SQL_EQUALS_EMPTY_COMMA        = " = '', "  ;
      private static final String SQL_PERCENT_COMPLETE_FORMULA  =
              " = MOD(percent_complete, 100), ";
  
      protected static final String SQL_FIELD_ID = "id";
      protected static final String SQL_FIELD_USERID = "userid";
      protected static final String SQL_FIELD_LAST_UPDATE = "last_update";
      protected static final String SQL_FIELD_STATUS = "status";
      protected static final String SQL_FIELD_ALL_DAY = "all_day";
      protected static final String SQL_FIELD_BODY = "body";
      protected static final String SQL_FIELD_BUSY_STATUS = "busy_status";
      protected static final String SQL_FIELD_CATEGORIES = "categories";
      protected static final String SQL_FIELD_COMPANIES = "companies";
      protected static final String SQL_FIELD_DURATION = "duration";
      protected static final String SQL_FIELD_DATE_END = "dend";
      protected static final String SQL_FIELD_LOCATION = "location";
      protected static final String SQL_FIELD_MEETING_STATUS = "meeting_status";
      protected static final String SQL_FIELD_MILEAGE = "mileage";
      protected static final String SQL_FIELD_REMINDER_TIME = "reminder_time";
      protected static final String SQL_FIELD_REMINDER = "reminder";
      protected static final String SQL_FIELD_REMINDER_REPEAT_COUNT
              = "reminder_repeat_count";
      protected static final String SQL_FIELD_REMINDER_SOUND_FILE
              = "reminder_sound_file";
      protected static final String SQL_FIELD_REMINDER_OPTIONS =
              "reminder_options";
      protected static final String SQL_FIELD_REPLY_TIME = "reply_time";
      protected static final String SQL_FIELD_SENSITIVITY = "sensitivity";
      protected static final String SQL_FIELD_DATE_START = "dstart";
      protected static final String SQL_FIELD_SUBJECT = "subject";
      protected static final String SQL_FIELD_RECURRENCE_TYPE = "rec_type";
      protected static final String SQL_FIELD_INTERVAL = "rec_interval";
      protected static final String SQL_FIELD_MONTH_OF_YEAR = "rec_month_of_year";
      protected static final String SQL_FIELD_DAY_OF_MONTH = "rec_day_of_month";
      protected static final String SQL_FIELD_DAY_OF_WEEK_MASK =
              "rec_day_of_week_mask";
      protected static final String SQL_FIELD_INSTANCE = "rec_instance";
      protected static final String SQL_FIELD_IMPORTANCE = "importance";
      protected static final String SQL_FIELD_START_DATE_PATTERN
              = "rec_start_date_pattern";
      protected static final String SQL_FIELD_NO_END_DATE = "rec_no_end_date";
      protected static final String SQL_FIELD_END_DATE_PATTERN =
              "rec_end_date_pattern";
      protected static final String SQL_FIELD_OCCURRENCES = "rec_occurrences";
      protected static final String SQL_FIELD_TYPE = "type";
      protected static final String SQL_FIELD_COMPLETED = "completed";
      protected static final String SQL_FIELD_PERCENT_COMPLETE = "percent_complete";
      protected static final String SQL_FIELD_ADDITION = "addition";
      protected static final String SQL_FIELD_OCCURRENCE_DATE = "occurrence_date";
      protected static final String SQL_FIELD_FOLDER = "folder";
      protected static final String SQL_FIELD_START_DATE_TIME_ZONE = "dstart_tz";
      protected static final String SQL_FIELD_END_DATE_TIME_ZONE = "dend_tz";
      protected static final String SQL_FIELD_REMINDER_TIME_ZONE = "reminder_tz";
  
      protected static final String NAMESPACE_KEY = "calendar";
      protected static final String COMPANY_ID = "funambol.com";
  
      protected static final int SQL_BODY_DIM = 4096;
      protected static final int SQL_CATEGORIES_DIM = 255;
      protected static final int SQL_COMPANIES_DIM = 255;
      protected static final int SQL_DAYOFWEEKMASK_DIM = 16;
      protected static final int SQL_ENDDATEPATTERN_DIM = 32;
      protected static final int SQL_LOCATION_DIM = 255;
      protected static final int SQL_SOUNDFILE_DIM = 255;
      protected static final int SQL_STARTDATEPATTERN_DIM = 32;
      protected static final int SQL_SUBJECT_DIM = 1000;
      protected static final int SQL_FOLDER_DIM = 255;
      protected static final int SQL_TIME_ZONE_DIM = 255;
  
      //------------------------------------------------------------- Private data
      private int type;
  
      //------------------------------------------------------------- Constructors
  
      /*
       * @see PIMEntityDAO#PIMEntityDAO(String, String)
       */
      public PIMCalendarDAO(String userId, Class allowedContent) {
          super(userId, Def.ID_COUNTER);
  
          if (allowedContent == Event.class) {
              type = CALENDAR_EVENT_TYPE;
          } else if (allowedContent == Task.class) {
              type = CALENDAR_TASK_TYPE;
          } else {
              type = CALENDAR_BOTH_TYPES;
          }
  
          if (log.isTraceEnabled()) {
              log.trace("Created new PIMCalendarDAO for user ID " + userId + ". "
                      + "It will retrieve content of " + allowedContent);
          }
      }
  
      //----------------------------------------------------------- Public methods
  
      /**
       * Adds a calendar. If necessary, a new ID is generated and set in the
       * CalendarWrapper.
       *
       * @param c as a CalendarWrapper object, usually without an ID set.
       * @throws DAOException
       *
       * @see CalendarWrapper
       */
      public void addItem(CalendarWrapper cw) throws DAOException {
  
          if (log.isTraceEnabled()) {
              log.trace("PIMCalendarDAO addItem begin");
          }
  
          Connection        con = null;
          PreparedStatement ps  = null;
  
          long              id               = 0   ;
          String            allDay           = null;
          String            body             = null;
          Short             busyStatus       = null;
          String            categories       = null;
          String            companies        = null;
          int               duration         = 0   ;
          Date              dend             = null;
          short             importance       = 0   ;
          String            location         = null;
          Short             meetingStatus    = null;
          String            mileage          = null;
          Date              replyTime        = null;
          short             sensitivity      = 0   ;
          Date              dstart           = null;
          String            subject          = null;
          int               interval         = 0   ;
          short             monthOfYear      = 0   ;
          short             dayOfMonth       = 0   ;
          String            dayOfWeekMask    = null;
          short             instance         = 0   ;
          String            startDatePattern = null;
          String            endDatePattern   = null;
          Reminder          reminder         = null;
          RecurrencePattern rp               = null;
          short             recurrenceType   = -1  ;
          String            sId              = null;
          int               occurrences      = -1  ;
          String            folder           = null;
          String            dstartTimeZone   = null;
          String            dendTimeZone     = null;
          String            reminderTimeZone = null;
          Date              completed        = null;
          short             percentComplete  = -1  ;
  
          Timestamp lastUpdate = cw.getLastUpdate();
          if (lastUpdate == null) {
              lastUpdate = new Timestamp(System.currentTimeMillis());
          }
  
          try {
  
              sId = cw.getId();
              if (sId == null || sId.length() == 0) { // ...as it should be
                  sId = getNextID();
                  cw.setId(sId);
              }
              id = Long.parseLong(sId);
  
              CalendarContent c = cw.getCalendar().getCalendarContent();
  
              rp = c.getRecurrencePattern();
  
              boolean allDayB;
              if (c.getAllDay() != null && c.getAllDay().booleanValue()) {
                  allDayB = true;
                  allDay = "1";
              } else {
                  allDayB = false;
                  allDay = "0";
              }
  
              String sd = null;
              if (c.getDtStart() != null) {
                  sd = c.getDtStart().getPropertyValueAsString();
                  dstart = getDateFromString(allDayB, sd, "000000");
              }
  
              String ed = null;
              if ((sd != null && sd.length() > 0) || c.getDtEnd() != null) {
                  ed = c.getDtEnd().getPropertyValueAsString();
  
                  //
                  // Fix for Siemens S56 end date issue only for event
                  // @todo: verify if is really need to do this
                  //
                  if (c instanceof Event) {
                      if (ed == null || ed.length()==0) {
                          ed = sd;
                      }
                  }
  
                  dend = getDateFromString(allDayB, ed, "235900");
              }
  
              body = Property.stringFrom(c.getDescription());
  
              if (c.getBusyStatus() != null) {
                  busyStatus = new Short(c.getBusyStatus().shortValue());
              }
  
              categories       = Property.stringFrom  (c.getCategories());
              companies        = Property.stringFrom  (c.getOrganizer() );
              location         = Property.stringFrom  (c.getLocation()  );
              folder           = Property.stringFrom  (c.getFolder()    );
              dstartTimeZone   = timeZoneFrom(c.getDtStart()   );
              dendTimeZone     = timeZoneFrom(c.getDtEnd()     );
              reminderTimeZone = timeZoneFrom(c.getReminder()  );
              meetingStatus    = c.getMeetingStatus();
  
              Integer mileageInteger = c.getMileage(); // NB: not an int...
              if (mileageInteger != null) { // ...therefore it may be null
                  mileage = String.valueOf(mileageInteger);
              }
  
              if (c instanceof Event) {
                  replyTime =
                      getDateFromString(allDayB, // @todo or false?
                                        Property.stringFrom(((Event) c).getReplyTime()),
                                        "000000");
              }
  
              try {
                  sensitivity = Short.parseShort(Property.stringFrom(c.getAccessClass()));
              } catch (NumberFormatException nfe) {
                  sensitivity = 0;
                  // The short must go on
              }
  
              if ((subject = Property.stringFrom(c.getSummary())) == null && body != null) {
                  int endOfSentence = body.indexOf('.');
                  if (endOfSentence == -1) {
                      endOfSentence = body.length();
                  }
                  if (endOfSentence > SQL_SUBJECT_DIM) {
                      endOfSentence = SQL_SUBJECT_DIM;
                  }
                  subject = body.substring(0, endOfSentence);
              }
  
              String isInfinite = "0";
              if (rp != null) {
                  interval         = rp.getInterval()                     ;
                  recurrenceType   = rp.getTypeId()                       ;
                  monthOfYear      = rp.getMonthOfYear()                  ;
                  dayOfMonth       = rp.getDayOfMonth()                   ;
                  dayOfWeekMask    = String.valueOf(rp.getDayOfWeekMask());
                  instance         = rp.getInstance()                     ;
                  startDatePattern = rp.getStartDatePattern()             ;
                  endDatePattern   = rp.getEndDatePattern()               ;
                  if (rp.isNoEndDate()) {
                      isInfinite = "1";
                  }
                  occurrences      = rp.getOccurrences()                  ;
              }
  
              if (c instanceof Task) {
                  Task t = (Task) c;
                  if (t.getDateCompleted() != null) {
                       completed = getDateFromString(
                           allDayB,
                           ((Task) c).getDateCompleted().getPropertyValueAsString(),
                           "000000"
                       );
                  }
  
                  try {
                      String complete = Property.stringFrom(t.getComplete());
                      if (complete != null && complete.equals("1")) {
                          percentComplete = 100;
                      } else {
                          percentComplete = Short.parseShort(
                                  Property.stringFrom(t.getPercentComplete())
                              );
                      }
                      if (percentComplete < 0 || percentComplete > 100) {
                          throw new NumberFormatException("A percentage can't be "
                                  + percentComplete);
                      }
                  } catch (NumberFormatException nfe) {
                      percentComplete = -1; // the short must go on
                  }
  
                  
                  meetingStatus = getTaskStatus(t);
              }
  
              con = getUserDataSource().getRoutedConnection(userId);
  
              ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CALENDAR);
  
              ps.setLong  (1, id                               );
              ps.setString(2, userId                           );
              ps.setLong  (3, lastUpdate.getTime()             );
              ps.setString(4, String.valueOf(Def.PIM_STATE_NEW));
              ps.setString(5, allDay                           );
              ps.setString(6, StringUtils.left(body, SQL_BODY_DIM)     );
              if (busyStatus != null) {
                  ps.setShort(7, busyStatus.shortValue());
              } else {
                  ps.setNull (7, Types.SMALLINT);
              }
              ps.setString(8 , StringUtils.left(categories, SQL_CATEGORIES_DIM));
              ps.setString(9 , StringUtils.left(companies , SQL_COMPANIES_DIM ));
              ps.setInt   (10, duration);
              if (dend != null) {
                  ps.setTimestamp(11, new Timestamp(dend.getTime()));
              } else {
                  ps.setNull     (11, Types.TIMESTAMP);
              }
  
              if (c.getPriority() != null) {
  
                  String priority = c.getPriority().getPropertyValueAsString();
  
                  if (priority != null && priority.length() > 0) {
                      importance = Short.parseShort(priority);
                      ps.setShort(12, importance);
                  } else {
                      ps.setNull(12, Types.SMALLINT);
                  }
  
              } else {
                  ps.setNull(12, Types.SMALLINT);
              }
  
              ps.setString(13, StringUtils.left(location, SQL_LOCATION_DIM));
  
              if (meetingStatus != null) {
                  ps.setShort(14, meetingStatus.shortValue());
              } else {
                  ps.setNull (14, Types.SMALLINT);
              }
  
              ps.setString(15, mileage);
  
              reminder = c.getReminder();
              if (reminder != null && reminder.isActive()) {
                  Timestamp reminderTime = getReminderTime(dstart, reminder);
                  if (reminderTime == null) {
                      ps.setNull     (16, Types.TIMESTAMP);
                  } else {
                      ps.setTimestamp(16, reminderTime   );
                  }
                  ps.setInt   (17, reminder.getRepeatCount()        );
                  ps.setString(18, (reminder.isActive()) ? "1" : "0");
  
                  String soundFileValue = reminder.getSoundFile();
                  ps.setString(19, StringUtils.left(soundFileValue, SQL_SOUNDFILE_DIM));
                  ps.setInt   (20, reminder.getOptions()                      );
              } else {
                  ps.setNull  (16, Types.TIMESTAMP);
                  ps.setInt   (17, 0              );
                  ps.setString(18, "0"            );
                  ps.setString(19, null           );
                  ps.setInt   (20, 0              );
              }
  
              if (replyTime != null) {
                  ps.setTimestamp(21, new Timestamp(replyTime.getTime()));
              } else {
                  ps.setNull     (21, Types.TIMESTAMP);
              }
  
              ps.setShort(22, sensitivity);
  
              if (dstart != null) {
                  ps.setTimestamp(23, new Timestamp(dstart.getTime()));
              } else {
                  ps.setNull     (23, Types.TIMESTAMP);
              }
              ps.setString(24, StringUtils.left(subject, SQL_SUBJECT_DIM));
              ps.setShort (25, recurrenceType);
              ps.setInt   (26, interval      );
              ps.setShort (27, monthOfYear   );
              ps.setShort (28, dayOfMonth    );
              ps.setString(29, StringUtils.left(dayOfWeekMask, SQL_DAYOFWEEKMASK_DIM));
              ps.setShort (30, instance      );
              ps.setString(31,
                           StringUtils.left(startDatePattern, SQL_STARTDATEPATTERN_DIM));
              ps.setString(32, isInfinite    );
              ps.setString(33, StringUtils.left(endDatePattern, SQL_ENDDATEPATTERN_DIM));
              ps.setInt   (34, occurrences   );
  
              if (c instanceof Event) {
                  ps.setInt (35, CALENDAR_EVENT_TYPE);
                  ps.setNull(36, Types.TIMESTAMP    ); // completed
                  ps.setNull(37, Types.SMALLINT     ); // percent_complete
              } else {
                  ps.setInt (35, CALENDAR_TASK_TYPE);
  
                  if (completed != null) {
                      ps.setTimestamp(36, new Timestamp(completed.getTime()));
                  } else {
                      ps.setNull     (36, Types.TIMESTAMP);
                  }
  
                  if (percentComplete != -1) {
                      ps.setShort(37, percentComplete);
                  } else {
                      ps.setNull (37, Types.SMALLINT);
                  }
              }
  
              ps.setString(38, StringUtils.left(folder,           SQL_FOLDER_DIM   ));
              ps.setString(39, StringUtils.left(dstartTimeZone,   SQL_TIME_ZONE_DIM));
              ps.setString(40, StringUtils.left(dendTimeZone,     SQL_TIME_ZONE_DIM));
              ps.setString(41, StringUtils.left(reminderTimeZone, SQL_TIME_ZONE_DIM));
              
              ps.executeUpdate();
              DBTools.close(null, ps, null);
  
              if (recurrenceType != -1) {
                  List<ExceptionToRecurrenceRule> exceptions = 
                          rp.getExceptions();
                  for (ExceptionToRecurrenceRule etrr : exceptions) {
                      ps = con.prepareStatement(
                              SQL_INSERT_INTO_FNBL_PIM_CALENDAR_EXCEPTION
                           );
  
                      ps.setLong     (1, id);
                      ps.setString   (2, (etrr.isAddition() ? "1" : "0"));
                      ps.setTimestamp(3, new Timestamp(
                                          getDateFromString(allDayB,
                                                            etrr.getDate(),
                                                            "000000").getTime())
                                      );
  
                      ps.executeUpdate();
                      DBTools.close(null, ps, null);
                  }
              }
  
          } catch (Exception e) {
              throw new DAOException(
                      "Error adding a calendar item: " + e.getMessage(), e);
          } finally {
              DBTools.close(con, ps, null);
          }
      }
  
      /**
       * Updates a calendar.
       *
       * @param cw as a CalendarWrapper object. If its last update time is null,
       *          then it's set to the current time.
       * @return the UID of the contact
       * @throws DAOException
       * @throws java.lang.Exception 
       * @see CalendarWrapper
       */
      public String updateItem(CalendarWrapper cw)
      throws DAOException, Exception {
  
          Connection        con = null;
          PreparedStatement ps  = null;
          ResultSet         rs  = null;
  
          RecurrencePattern rp               = null;
          String            id               = null;
          String            allDay           = null;
          String            body             = null;
          Boolean           allDayBoolean    = null;
          Short             busyStatus       = null;
          String            categories       = null;
          String            companies        = null;
          int               duration         = 0   ;
          Date              dend             = null;
          short             importance       = 0   ;
          String            location         = null;
          Short             meetingStatus    = null;
          String            mileage          = null;
          Date              replyTime        = null;
          short             sensitivity      = 0   ;
          Date              dstart           = null;
          String            subject          = null;
          short             recurrenceType   = -1  ;
          int               interval         = 0   ;
          short             monthOfYear      = 0   ;
          short             dayOfMonth       = 0   ;
          String            dayOfWeekMask    = null;
          String            priority         = null;
          short             instance         = 0   ;
          String            startDatePattern = null;
          String            noEndDate        = null;
          String            endDatePattern   = null;
          int               occurrences      = -1  ;
          Reminder          reminder         = null;
          CalendarContent   c                = null;
          Date              completed        = null;
          String            complete         = null;
          short             percentComplete  = -1  ;
          String            folder           = null;
          String            dstartTimeZone   = null;
          String            dendTimeZone     = null;
          String            reminderTimeZone = null;
  
          StringBuffer queryUpdateFunPimCalendar = null;
  
          try {
  
              Timestamp lastUpdate = (cw.getLastUpdate() == null)
                                   ? new Timestamp(System.currentTimeMillis())
                                   : cw.getLastUpdate()
                                   ;
  
              c = cw.getCalendar().getCalendarContent();
  
              rp = c.getRecurrencePattern();
  
              id = cw.getId();
  
              boolean allDayB;
              allDayBoolean = c.getAllDay();
              if (allDayBoolean != null && allDayBoolean.booleanValue()) {
                  allDayB = true;
                  allDay = "1";
              } else {
                  allDayB = false;
                  allDay = "0";
              }
  
              body = Property.stringFrom(c.getDescription());
  
              if (c.getBusyStatus() != null) {
                  busyStatus = new Short(c.getBusyStatus().shortValue());
              }
              categories = Property.stringFrom(c.getCategories());
              companies  = Property.stringFrom(c.getOrganizer() );
  