/** * This class implements methods to access contacts data in domino server data * @author Diorgenes Felipe Grzesiuk * @copyright Copyright 2007-2008 Prognus * * This program is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License version 2 as published by * the Free Software Foundation. * * 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 General Public License * along with Foobar; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ package br.com.prognus.psync.items.dao; 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.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.LinkedList; import java.util.List; import java.util.StringTokenizer; import java.util.Vector; import br.com.prognus.psync.exception.PIMDBAccessException; import br.com.prognus.psync.items.model.CalendarWrapper; import br.com.prognus.psync.util.Def; 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.utility.TimeUtils; import com.funambol.framework.security.Sync4jPrincipal; import com.funambol.framework.server.store.NotFoundException; import com.funambol.framework.tools.DBTools; public class PIMCalendarDAO extends PIMEntityDAO { // --------------------------------------------------------------- Constants private static final String SQL_AND_NO_SUBJECT_IS_SET = "AND ((C.title IS null) OR (C.title = '')) "; private static final String SQL_AND_SUBJECT_EQUALS_QUESTIONMARK = "AND C.title = ? "; private static final String SQL_AND_NO_dtstart_IS_SET = "AND C.datetime IS null "; private static final String SQL_AND_dtstart_EQUALS_QUESTIONMARK = "AND C.datetime = ? "; private static final String SQL_AND_NO_DEND_IS_SET = "AND C.edatetime IS null "; private static final String SQL_AND_DEND_EQUALS_QUESTIONMARK = "AND C.edatetime = ? "; private static final String SQL_EQUALS_QUESTIONMARK = " = ?"; private static final String SQL_EQUALS_QUESTIONMARK_COMMA = " = ?, "; protected static final int SQL_BODY_DIM = 4096; protected static final int SQL_LOCATION_DIM = 255; protected static final int SQL_SUBJECT_DIM = 255; protected static final String SQL_FIELD_ID = "cal_id"; protected static final String SQL_FIELD_USERID = "owner"; protected static final String SQL_FIELD_LAST_UPDATE = "last_update"; protected static final String SQL_FIELD_STATUS = "last_status"; protected static final String SQL_FIELD_CAL_TYPE = "cal_type"; protected static final String SQL_FIELD_SENSITIVITY = "is_public"; protected static final String SQL_FIELD_SUBJECT = "title"; protected static final String SQL_FIELD_BODY = "description"; protected static final String SQL_FIELD_LOCATION = "location"; protected static final String SQL_FIELD_DATE_START = "datetime"; protected static final String SQL_FIELD_CATEGORY = "category"; protected static final String SQL_FIELD_DATE_END = "edatetime"; protected static final String SQL_FIELD_DATE_NOW = "mdatetime"; private static final String SQL_ORDER_BY_ID = "ORDER BY cal_id ASC"; private static final String SQL_INSERT_INTO_EXP_PIM_CALENDAR = "INSERT INTO phpgw_cal " + "(uid, owner, category, datetime, mdatetime, edatetime, cal_type, is_public, title, description, location, ex_participants, last_update, last_status) " + "VALUES " + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; private static final String SQL_INSERT_INTO_EXP_PIM_CALENDAR_USER = "INSERT INTO phpgw_cal_user " + "(cal_id, cal_login) " + "VALUES " + "(?, ?)"; private static final String SQL_INSERT_INTO_EXP_PIM_CALENDAR_REPEATS = "INSERT INTO phpgw_cal_repeats " + "(cal_id, recur_type, recur_use_end, recur_enddate, recur_interval, recur_data, recur_exception) " + "VALUES " + "(?, ?, ?, ?, ?, ?, ?)"; private static final String SQL_INSERT_INTO_EXP_PIM_ALARM = "INSERT INTO phpgw_async " + "(id, next, times, method, data, account_id) " + "VALUES " + "(?, ?, ?, ?, ?, ?)"; private static final String SQL_GET_FNBL_PIM_CALENDAR_EXCEPTION_BY_CALENDAR = "SELECT recur_type, recur_use_end, recur_enddate, recur_interval, recur_data, recur_exception, datetime FROM phpgw_cal_repeats AS R LEFT JOIN phpgw_cal AS C ON R.cal_id = C.cal_id WHERE C.cal_id = ?"; private static final String SQL_GET_EXP_PIM_CALENDAR_ID_LIST_BY_USER_TIME_STATUS = "SELECT C.cal_id FROM phpgw_cal_user as U, phpgw_cal as C WHERE C.cal_id = U.cal_id AND U.cal_login = ? AND C.last_update > ? AND C.last_update < ? AND C.last_status = ? AND U.cal_status != 'R' "; private static final String SQL_GET_EXP_PIM_CALENDAR_ID_LIST_BY_USER = "SELECT U.cal_id FROM phpgw_cal_user as U, phpgw_cal as C WHERE C.cal_id = U.cal_id AND U.cal_login = ? AND U.cal_status != 'R' "; private static final String SQL_GET_STATUS_BY_ID_USER_TIME = "SELECT last_status FROM phpgw_cal WHERE cal_id = ? AND owner = ? AND last_update > ? LIMIT 1"; private static final String SQL_GET_EXP_PIM_CALENDAR_BY_ID_USERID = "SELECT cal_id, last_update, owner, last_status, location, title, description, datetime, edatetime, is_public, category FROM phpgw_cal WHERE cal_id = ?"; private static final String SQL_UPDATE_EXP_PIM_CALENDAR_BEGIN = "UPDATE phpgw_cal SET "; private static final String SQL_UPDATE_EXP_PIM_CALENDAR_END = " WHERE cal_id = ? AND owner = ? "; private Sync4jPrincipal principal = null; /* * @see PIMEntityDAO#PIMEntityDAO(String, String) */ public PIMCalendarDAO(String jndiDataSourceName, Sync4jPrincipal principal) { super(jndiDataSourceName, principal.getUsername()); log.info("\n\n=> Created new PIMCalendarDAO for data source " + jndiDataSourceName + " and user ID " + userId); this.principal = principal; } // ----------------------------------------------------------- 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 PIMDBAccessException * * @see CalendarWrapper */ public void addItem(CalendarWrapper cw) throws PIMDBAccessException { log.info("\n\n=> PIMCalendarDAO addItem begin"); Connection con = null; PreparedStatement ps = null; RecurrencePattern rp = null; ResultSet rs = null; Reminder reminder = null; String subject = null; String body = null; String location = null; String is_public = null; String category = null; Date dtstart = null; Date dend = null; long id_cal = 0; long dt1 = 0; try { con = getDataSource().getConnection(); con.setAutoCommit(false); CalendarContent c = cw.getCalendar().getCalendarContent(); Timestamp lastUpdate = cw.getLastUpdate(); lastUpdate = (lastUpdate == null) ? new Timestamp(System.currentTimeMillis()) : lastUpdate; // Recebe a data inicial do evento String sd = null; if (c.getDtStart() != null) { sd = c.getDtStart().getPropertyValueAsString(); // data inicial no formato YYYYMMddTHHMMSSZ dtstart = getDateFromString(sd); // data inicial no formato YYYY-MM-dd dt1 = new Long(Long.toString(dtstart.getTime()).substring(0, 10)); } // Recebe a data final do evento String ed = null; if (c.getDtEnd() != null) { ed = c.getDtEnd().getPropertyValueAsString(); dend = getDateFromString(ed); } rp = c.getRecurrencePattern(); // Recebe a repetição do alarme reminder = c.getReminder(); // Recebe o alarme do evento body = stringFrom(c.getDescription()); // Descricao completa do evento location = stringFrom(c.getLocation()); // Localizacao do evento is_public = (stringFrom(c.getAccessClass())).equals("2") ? "0" : "1"; // Evento particular ou publico subject = stringFrom(c.getSummary()); // Titulo do evento category = stringFrom(c.getCategories()); // Verifica o parametro uid necessario para o expresso ps = con.prepareStatement("select config_value from phpgw_config WHERE config_name = 'hostname' AND config_app = 'phpgwapi'"); rs = ps.executeQuery(); String uid = rs.next() ? ("-@" + rs.getString(1)) : ""; // Verifica a categoria do evento if(category != null && (!category.equals(""))){ category = addCategory(category); } // Prepara os dados para inserir o evento no banco de dados ps = con.prepareStatement(SQL_INSERT_INTO_EXP_PIM_CALENDAR); ps.setString(1, uid); // -@hostname ps.setLong(2, Long.parseLong(userId)); // owner ps.setString(3, category); // category ps.setLong(4, new Long(Long.toString(dtstart.getTime()).substring(0, 10))); // datetime ps.setLong(5, new Long(Long.toString(lastUpdate.getTime()).substring(0, 10))); // mdatetime ps.setLong(6, new Long(Long.toString(dend.getTime()).substring(0, 10))); // edatetime ps.setString(7, (rp == null) ? "E" : "M"); // cal_type ps.setLong(8, Long.parseLong(is_public)); // is_plublic ps.setString(9, truncate(subject, SQL_SUBJECT_DIM)); // title ps.setString(10, truncate(body, SQL_BODY_DIM)); // body of event, a little description of it ps.setString(11, truncate(location, SQL_LOCATION_DIM)); // location of the event ps.setString(12, ""); // Participants outside ps.setLong(13, lastUpdate.getTime()); // last update ps.setString(14, String.valueOf(Def.PIM_STATE_NEW)); // status ps.executeUpdate(); // Verifica o id maximo do evento inserido ps = con.prepareStatement("select max(cal_id) from phpgw_cal limit 1"); rs = ps.executeQuery(); if (rs.next()) { long id = rs.getInt(1); id_cal = (id == 0) ? 1 : id; } cw.setId(Long.toString(id_cal)); ps = con.prepareStatement(SQL_INSERT_INTO_EXP_PIM_CALENDAR_USER); ps.setLong(1, id_cal); ps.setLong(2, Long.parseLong(userId)); // owner ps.executeUpdate(); // Evento com repetição if (rp != null) { addRecurrencePattern(rp, id_cal, sd); } log.trace("SERPRO - pimcalendardao - ANTES if (reminder != null && reminder.isActive())"); //Correcao - Se o valor do atributo alarm do calendario eh null, atribui. emerson-faria.nobre@serpro.gov.br if (reminder != null && reminder.isActive() && reminder.getTime() == null) { String strDate = String.valueOf(dt1 * 1000 - reminder.getMinutes() * 60000); String dtUTC = com.funambol.common.pim.utility.TimeUtils.convertDateTo(new Date(Long.valueOf(strDate)), com.funambol.common.pim.utility.TimeUtils.TIMEZONE_UTC, com.funambol.common.pim.utility.TimeUtils.PATTERN_UTC); reminder.setTime(dtUTC); } // Evento com Alarme if (reminder != null && reminder.isActive()) { addReminder(reminder, id_cal); } log.trace("SERPRO - pimcalendardao - APOS if (reminder != null && reminder.isActive())"); con.commit(); con.setAutoCommit(true); } catch (Exception e) { throw new PIMDBAccessException("Error adding a calendar item: " + e.getMessage()); } finally { DBTools.close(con, ps, rs); } log.info("\n\n=>Added item with ID " + id_cal); log.info("\n\n=>PIMCalendarDAO addItem end"); } /** * Updates a calendar. * * @param c * 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 PIMDBAccessException * * @see CalendarWrapper */ public String updateItem(CalendarWrapper cw) throws PIMDBAccessException, Exception { log.info("\n\n=> PIMCalendarDAO updateItem begin"); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; CalendarContent c = null; RecurrencePattern rp = null; Reminder reminder = null; long id_cal = 0; long dt1 = 0; long dt2 = 0; Date dend = null; Date dtstart = null; String body = null; String location = null; String subject = null; String is_public = null; String category = null; StringBuffer queryUpdateFunPimCalendar = null; try { con = getDataSource().getConnection(); Timestamp lastUpdate = (cw.getLastUpdate() == null) ? new Timestamp(System.currentTimeMillis()) : cw.getLastUpdate(); c = cw.getCalendar().getCalendarContent(); id_cal = Long.parseLong(cw.getId()); // Verifica se o evento pertence ao dono para ser atualizado ps = con.prepareStatement("SELECT cal_id FROM phpgw_cal WHERE cal_id = ? AND owner = ?"); ps.setLong(1, id_cal); ps.setLong(2, Long.parseLong(userId)); rs = ps.executeQuery(); if (rs.next()) { rp = c.getRecurrencePattern(); // Recebe a repetição do alarme reminder = c.getReminder(); // Recebe o alarme do evento body = stringFrom(c.getDescription()); // Descrição completa do evento location = stringFrom(c.getLocation()); // Localizacao do evento is_public = (stringFrom(c.getAccessClass())).equals("2") ? "0" : "1"; // Evento particular ou publico subject = stringFrom(c.getSummary()); // Titulo do evento category = stringFrom(c.getCategories()); // Verifica a categoria do evento if(category != null && (!category.equals(""))){ category = addCategory(category); } else { category = null; } // Recebe a data inicial do evento String sd = null; if (c.getDtStart() != null) { sd = c.getDtStart().getPropertyValueAsString(); dtstart = getDateFromString(sd); dt1 = new Long(Long.toString(dtstart.getTime()).substring(0, 10)); } // Recebe a data final do evento String ed = null; if (c.getDtEnd() != null) { ed = c.getDtEnd().getPropertyValueAsString(); dend = getDateFromString(ed); dt2 = new Long(Long.toString(dend.getTime()).substring(0, 10)); } // Prepara os dados para inserir o evento no banco de dados queryUpdateFunPimCalendar = new StringBuffer(); queryUpdateFunPimCalendar.append(SQL_UPDATE_EXP_PIM_CALENDAR_BEGIN); queryUpdateFunPimCalendar.append(SQL_FIELD_CATEGORY).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_START).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_NOW).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_END).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_CAL_TYPE).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_SENSITIVITY).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_SUBJECT).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_BODY).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_LOCATION).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_LAST_UPDATE).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_STATUS).append(SQL_EQUALS_QUESTIONMARK); queryUpdateFunPimCalendar.append(SQL_UPDATE_EXP_PIM_CALENDAR_END); ps = con.prepareStatement(queryUpdateFunPimCalendar.toString()); ps.setString(1, category); //category ps.setLong(2, dt1); // datetime ps.setLong(3, new Long(Long.toString(lastUpdate.getTime()).substring(0, 10))); // mdatetime ps.setLong(4, dt2); // datetime ps.setString(5, (rp == null) ? "E" : "M"); // cal_type ps.setLong(6, Long.parseLong(is_public)); // is_plublic ps.setString(7, truncate(subject, SQL_SUBJECT_DIM)); ps.setString(8, truncate(body, SQL_BODY_DIM)); ps.setString(9, truncate(location, SQL_LOCATION_DIM)); // location ps.setLong(10, lastUpdate.getTime()); // last update ps.setString(11, String.valueOf(Def.PIM_STATE_UPDATED)); // status ps.setLong(12, id_cal); // cal_id // Correcao - Incluido typecast 'userId' para compatibilizar com o postgresql 8.3 - emerson-faria.nobre@serpro.gov.br - 28/jan/2010 // ps.setString(13, userId); // owner ps.setLong(13, Long.parseLong(userId)); ps.executeUpdate(); // Evento com repetição if (rp != null) { addRecurrencePattern(rp, id_cal, sd); // adiciona a repeticao } else { deleteRecurrencePattern(id_cal); // delete a repeticao } // Evento com Alarme //Correcao - Se o valor do atributo alarm do calendario eh null, atribui. emerson-faria.nobre@serpro.gov.br if (reminder != null && reminder.isActive() && reminder.getTime() == null) { String strDate = String.valueOf(dt1 * 1000 - reminder.getMinutes() * 60000); String dtUTC = com.funambol.common.pim.utility.TimeUtils.convertDateTo(new Date(Long.valueOf(strDate)), com.funambol.common.pim.utility.TimeUtils.TIMEZONE_UTC, com.funambol.common.pim.utility.TimeUtils.PATTERN_UTC); reminder.setTime(dtUTC); } if (reminder != null && reminder.isActive() && reminder.getTime() != null) { addReminder(reminder, id_cal); // adiciona o alarme } else { deleteReminder(id_cal); // deleta o alarme } } con.commit(); con.setAutoCommit(true); } catch (Exception e) { throw new PIMDBAccessException( "\n=> Error updating a calendar item: " + e.getMessage()); } finally { DBTools.close(con, ps, rs); } log.info("\n\n=>Update item with ID " + id_cal); log.info("\n\n=>PIMCalendarDAO updateItem end"); return Long.toString(id_cal); } /** * Retrieves the UID list of all calendars belonging to the user of the type * prescribed by the DAO set-up. * * @throws PIMDBAccessException * @return a List of UIDs (as String objects) */ public List getAllItems() throws PIMDBAccessException { log.info("\n\n=> PIMCalendarDAO getAllItems begin"); Connection con = null; PreparedStatement ps = null; List calendars = new ArrayList(); ResultSet rs = null; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); ps = con.prepareStatement(SQL_GET_EXP_PIM_CALENDAR_ID_LIST_BY_USER + SQL_ORDER_BY_ID); //Correcao - Incluido typecast 'userId' para compatibilizar com o postgresql 8.3 - emerson-faria.nobre@serpro.gov.br - 26/feb/2010 // ps.setString(1, userId); ps.setLong(1, Long.parseLong(userId)); rs = ps.executeQuery(); while (rs.next()) { calendars.add(Long.toString(rs.getLong(1))); // It's the first } } catch (Exception e) { throw new PIMDBAccessException("\n=> Error listing contacts. " + e.getMessage()); } finally { DBTools.close(con, ps, rs); } log.info("\n\n=> PIMCalendarDAO getAllItems end"); return calendars; } public CalendarWrapper getItem(String uid) throws PIMDBAccessException { log.info("\n\n=> DAO start getItem " + uid); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; ResultSet rs1 = null; CalendarWrapper cw = null; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); try { ps = con.prepareStatement(SQL_GET_EXP_PIM_CALENDAR_BY_ID_USERID); ps.setLong(1, Long.parseLong(uid)); rs = ps.executeQuery(); //Correcao - Incluido typecast cat_ID::text para compatibilizar com o postgreSQL 8.3 - emerson.faria.nobre@serpro.gov.br - 29/jan/2010 // ps = con.prepareStatement("SELECT CT.cat_name FROM phpgw_cal as C, phpgw_categories as CT WHERE C.category = CT.cat_id AND C.cal_id = ?"); ps = con.prepareStatement("SELECT CT.cat_name FROM phpgw_cal as C, phpgw_categories as CT WHERE C.category = CT.cat_id::text AND C.cal_id = ?"); ps.setLong(1, Long.parseLong(uid)); rs1 = ps.executeQuery(); cw = createCalendar(uid, userId, rs, (rs1.next() ? rs1.getString(1) : "")); } catch (SQLException sqle) { throw new SQLException("\n=> Error while adding createCalendar. " + sqle, sqle.getSQLState()); } try { ps = con.prepareStatement(SQL_GET_FNBL_PIM_CALENDAR_EXCEPTION_BY_CALENDAR); ps.setLong(1, Long.parseLong(uid)); rs = ps.executeQuery(); cw = addPIMCalendarExceptions(cw, rs); } catch (SQLException sqle) { throw new SQLException("\n=> Error while adding addPIMCalendarExceptions. " + sqle, sqle.getSQLState()); } try { ps = con.prepareStatement("SELECT next, times, data from phpgw_async WHERE id = ? AND account_id = ?"); ps.setString(1, "cal:" + uid + ":0"); ps.setLong(2, Long.parseLong(userId)); rs = ps.executeQuery(); cw = addPIMCalendarAlarm(cw, rs); } catch (SQLException sqle) { throw new SQLException("\n=> Error while adding addPIMCalendarAlarm. " + sqle, sqle.getSQLState()); } } catch (Exception e) { throw new PIMDBAccessException("\n=> Error retrieving a calendar item: " + e, e); } finally { DBTools.close(con, ps, rs); DBTools.close(con, ps, rs1); } return cw; } /** * Removes the calendar with given UID and sets its last_update field, * provided it has the same userId as this DAO. The deletion is soft * (reversible). * * @param uid * corresponds to the id field in the phpgw_cal table * @throws PIMDBAccessException */ public void removeItem(String uid) throws PIMDBAccessException { log.info("\n\n=> DAO start removeItem " + uid); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); con.setAutoCommit(false); ps = con.prepareStatement("SELECT cal_id FROM phpgw_cal WHERE cal_id = ? AND owner = ?"); ps.setLong(1, Long.parseLong(uid)); ps.setLong(2, Long.parseLong(userId)); rs = ps.executeQuery(); if (rs.next()) { ps = con.prepareStatement("DELETE FROM phpgw_async WHERE id = ?"); ps.setString(1, "cal:" + uid + ":0"); ps.executeUpdate(); ps = con.prepareStatement("DELETE FROM phpgw_cal_repeats WHERE cal_id = ?"); ps.setLong(1, Long.parseLong(uid)); ps.executeUpdate(); ps = con.prepareStatement("DELETE FROM phpgw_cal_user WHERE cal_id = ?"); ps.setLong(1, Long.parseLong(uid)); ps.executeUpdate(); ps = con.prepareStatement("DELETE FROM phpgw_cal WHERE cal_id = ?"); ps.setLong(1, Long.parseLong(uid)); ps.executeUpdate(); } else { ps = con.prepareStatement("UPDATE phpgw_cal_user SET cal_status = 'R' WHERE cal_id = ? AND cal_login = ?"); ps.setLong(1, Long.parseLong(uid)); ps.setLong(2, Long.parseLong(userId)); ps.executeUpdate(); } con.commit(); con.setAutoCommit(true); } catch (Exception e) { throw new PIMDBAccessException("\n=> Error deleting calendar.", e); } finally { DBTools.close(con, ps, rs); } } /** * Removes a calendar, provided it has the same userId as this DAO. The * deletion is soft (reversible). * * @param calendar * whence the UID and the last update Date are extracted * @throws PIMDBAccessException */ public void removeItem(CalendarWrapper calendar) throws PIMDBAccessException { removeItem(calendar.getId()); } /** * Deletes (reversibly) all calendars belonging to the user of the type * prescribed by the DAO set-up. The last_update field of the (soft-)deleted * items will be set at a given timestamp. * * @throws PIMDBAccessException */ public void removeAllItems() throws PIMDBAccessException { log.info("\n\n=> DAO start removeAllItems"); Connection con = null; PreparedStatement ps = null; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); con.setAutoCommit(false); ps = con.prepareStatement("DELETE FROM phpgw_async WHERE account_id = ?"); ps.setLong(1, Long.parseLong(userId)); ps.executeUpdate(); ps = con.prepareStatement("DELETE FROM phpgw_cal_repeats WHERE cal_id IN(SELECT cal_id FROM phpgw_cal_user WHERE cal_login = ?)"); ps.setLong(1, Long.parseLong(userId)); ps.executeUpdate(); ps = con.prepareStatement("DELETE FROM phpgw_cal_user WHERE cal_id IN(SELECT cal_id FROM phpgw_cal WHERE owner = ?)"); ps.setLong(1, Long.parseLong(userId)); ps.executeUpdate(); ps = con.prepareStatement("DELETE FROM phpgw_cal WHERE owner = ?"); ps.setLong(1, Long.parseLong(userId)); ps.executeUpdate(); con.commit(); con.setAutoCommit(true); } catch (Exception e) { throw new PIMDBAccessException("\n=> Error deleting calendars.", e); } finally { DBTools.close(con, ps, null); } } /** * Retrieves the UID list of the calendars belonging to the user filtered * according to the given time interval and status. Only the calendars * corresponding to the type set-up of the DAO are retrieved. * * @param since * the earliest allowed last-update Timestamp * @param to * the latest allowed last-update Timestamp * @param status * 'D' for deleted items, 'N' for new items, 'U' for updated * items * @throws PIMDBAccessException * @return a List of UIDs (as String objects) */ // Alteracao - Incluido parametro sourceURI - emerson-faria.nobre@serpro.gov.br - 17/set/2009 protected List getItemsHavingStatus(Timestamp since, Timestamp to, char status, String sourceURI) throws PIMDBAccessException { log.info("\n\n=> Seeking '" + status + "' items of sourceURI:'" + sourceURI + "' in time interval ]" + since + "; " + to + "["); Connection con = null; PreparedStatement ps = null; List calendars = new ArrayList(); ResultSet rs = null; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); if (status == 'D') { //Correcao - Incluido typecast 'cal_id::text' para compatibilizar com o postgresql 8.3 - emerson-faria.nobre@serpro.gov.br - 28/feb/2010 // ps = con.prepareStatement("SELECT guid FROM fnbl_client_mapping WHERE sync_source = '" + sourceURI + "' AND guid NOT IN (SELECT C.cal_id FROM phpgw_cal as C, phpgw_cal_user as U WHERE C.cal_id = U.cal_id AND U.cal_login = ?) AND principal = ? ORDER BY guid"); ps = con.prepareStatement("SELECT guid FROM fnbl_client_mapping WHERE sync_source = '" + sourceURI + "' AND guid NOT IN (SELECT C.cal_id::text FROM phpgw_cal as C, phpgw_cal_user as U WHERE C.cal_id = U.cal_id AND U.cal_login = ?) AND principal = ? ORDER BY guid"); ps.setLong(1, Long.parseLong(userId)); ps.setLong(2, this.principal.getId()); rs = ps.executeQuery(); } else { ps = con.prepareStatement(SQL_GET_EXP_PIM_CALENDAR_ID_LIST_BY_USER_TIME_STATUS + SQL_ORDER_BY_ID); //Correcao - Incluido typecast 'userId' para compatibilizar com o postgresql 8.3 - emerson-faria.nobre@serpro.gov.br - 28/feb/2010 // ps.setString(1, userId); ps.setLong(1, Long.parseLong(userId)); ps.setLong(2, since.getTime()); ps.setLong(3, to.getTime()); ps.setString(4, String.valueOf(status)); rs = ps.executeQuery(); } while (rs.next()) { calendars.add(Long.toString(rs.getLong(1))); // It's the first and only column log.info("\n=> Item found " + rs.getLong(1)); } } catch (Exception e) { e.printStackTrace(); throw new PIMDBAccessException("\n=> Error listing contacts. " + e.getMessage()); } finally { DBTools.close(con, ps, rs); } return calendars; } /** * Retrieves the state of the given item, provided it's been modified after * a certain moment. * * @param uid * the UID of the item to be checked (as a String object) * @param since * the Timestamp that the item's lastUpdate field is checked * against: if the item has been modified before that moment, an * "unchanged" state marker is returned * @throws PIMDBAccessException * @return a char identifying either one of the 3 standard states ("new", * "deleted", "updated") or the special "unchanged" status, all of * them as defined in br.com.br.psync.pim.util.Def */ public char getItemState(String uid, Timestamp since) throws PIMDBAccessException { log.info("\n\n=> DAO start getItemState"); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; char status; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); ps = con.prepareStatement(SQL_GET_STATUS_BY_ID_USER_TIME); ps.setLong(1, Long.parseLong(uid)); ps.setString(2, userId); ps.setLong(3, since.getTime()); rs = ps.executeQuery(); if (!rs.next()) { status = Def.PIM_STATE_UNCHANGED; log.info("\n=> Item " + uid + "'s status wasn't retrieved " + "because the item hasn't been modified since " + since + " or the item doesn't exist"); } else { status = rs.getString(1).charAt(0); log.info("\n=> Item " + uid + " has status \'" + status + "\'"); } } catch (Exception e) { throw new PIMDBAccessException("\n=> Error retrieving item state. " + e.getMessage()); } finally { DBTools.close(con, ps, rs); } return status; } /** * Retrieves the UID list of the calendars considered to be "twins" of a * given contact. * * @param c * the Calendar object representing the calendar whose twins need * be found. In the present implementation, only the following * data matter: * * @throws PIMDBAccessException * @return a List of UIDs (as String objects) that may be empty but not null */ public List getTwinItems(Calendar c) throws PIMDBAccessException { log.info("\n\n\n=> PIMCalendarDAO getTwinItems begin"); LinkedList twins = new LinkedList(); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); Date dtStart = null; Date dtEnd = null; dtStart = getDateFromString(stringFrom(c.getCalendarContent().getDtStart())); dtEnd = getDateFromString(stringFrom(c.getCalendarContent().getDtEnd())); StringBuffer sqlGetCalendarTwinList = new StringBuffer(SQL_GET_EXP_PIM_CALENDAR_ID_LIST_BY_USER); String subject = stringFrom(c.getCalendarContent().getSummary(), true); // Empty implies null; if ("null".equals(subject)) { subject = null; } if (subject == null) { sqlGetCalendarTwinList.append(SQL_AND_NO_SUBJECT_IS_SET); } else { sqlGetCalendarTwinList.append(SQL_AND_SUBJECT_EQUALS_QUESTIONMARK); } if (dtStart == null) { sqlGetCalendarTwinList.append(SQL_AND_NO_dtstart_IS_SET); } else { sqlGetCalendarTwinList.append(SQL_AND_dtstart_EQUALS_QUESTIONMARK); } if (dtEnd == null) { sqlGetCalendarTwinList.append(SQL_AND_NO_DEND_IS_SET); } else { sqlGetCalendarTwinList.append(SQL_AND_DEND_EQUALS_QUESTIONMARK); } sqlGetCalendarTwinList.append(SQL_ORDER_BY_ID); StringBuilder sb = new StringBuilder(100); sb.append("\nLooking for items having: "); if (subject == null || subject.length() == 0) { sb.append("\n> subject: "); } else { sb.append("\n> subject: '").append(subject).append('\''); } if (dtStart == null) { sb.append("\n> start date: "); } else { sb.append("\n> start date: ").append(dtStart); } if (dtEnd == null) { sb.append("\n> end date: "); } else { sb.append("\n> end date: ").append(dtEnd); } log.info(sb.toString()); ps = con.prepareStatement(sqlGetCalendarTwinList.toString()); int k = 1; // Correcao - Incluido typecast 'userId' para compatibilizar com o postgresql 8.3 - emerson-faria.nobre@serpro.gov.br - 01/fev/2010 // ps.setString(k++, userId); ps.setLong(k++, Long.parseLong(userId)); if (subject != null) { ps.setString(k++, subject); } if (dtStart != null) { ps.setLong(k++, new Long(Long.toString(dtStart.getTime()).substring(0, 10))); } if (dtEnd != null) { ps.setLong(k++, new Long(Long.toString(dtEnd.getTime()).substring(0, 10))); } rs = ps.executeQuery(); long twinId; while (rs.next()) { twinId = rs.getLong(1); // dend is not relevant in this case log.info("\n\n=> Twin event found: " + twinId); twins.add(Long.toString(twinId)); } } catch (Exception e) { throw new PIMDBAccessException("\n=> Error retrieving twin. " + e); } finally { DBTools.close(con, ps, rs); } log.info("\n\n\n=> PIMCalendarDAO getTwinItems end"); return twins; } // ---------------------------------------------------------- Private // methods /** * Creates a ContactWrapper object of Event type from a ResultSet. * * @param wrapperId * the UID of the wrapper object to be returned * @param rs * the result of the execution of a proper SQL SELECT statement * on the phpgw_cal table, with the cursor before its first row * @return a newly created ContactWrapper initialized with the fields in the * result set * @throws java.sql.SQLException * @throws NotFoundException */ protected static CalendarWrapper createCalendar(String wrapperId, String user_session ,ResultSet rs, String category) throws NotFoundException, Exception { if (!rs.next()) { throw new NotFoundException("\n=> No calendar found."); } ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); String column = null; String uid = null; String user = null; Date dstart = null; Date dend = null; uid = String.valueOf(rs.getLong(SQL_FIELD_ID)); user = rs.getString(SQL_FIELD_USERID); Calendar cal = new Calendar(); Reminder r = new Reminder(); CalendarContent c = new Event(); c.setReminder(r); cal.setEvent((Event) c); CalendarWrapper cw = new CalendarWrapper(wrapperId, user, cal); for (int i = 1; i <= columnCount; i++) { column = rsmd.getColumnName(i); if (SQL_FIELD_ID.equalsIgnoreCase(column)) { // Does nothing: field already set at construction time } else if (SQL_FIELD_LAST_UPDATE.equalsIgnoreCase(column)) { cw.setLastUpdate(new Timestamp(rs.getLong(i))); } else if (SQL_FIELD_USERID.equalsIgnoreCase(column)) { // Does nothing: field already set at construction time } else if (SQL_FIELD_CATEGORY.equalsIgnoreCase(column)) { c.getCategories().setPropertyValue(category); } else if (SQL_FIELD_STATUS.equalsIgnoreCase(column)) { cw.setStatus(rs.getString(i).charAt(0)); } else if (SQL_FIELD_LOCATION.equalsIgnoreCase(column)) { c.getLocation().setPropertyValue(rs.getString(i)); } else if (SQL_FIELD_SUBJECT.equalsIgnoreCase(column)) { c.getSummary().setPropertyValue(rs.getString(i)); } else if (SQL_FIELD_BODY.equalsIgnoreCase(column)) { c.getDescription().setPropertyValue(rs.getString(i)); } else if (SQL_FIELD_DATE_START.equalsIgnoreCase(column)) { if (rs.getLong(i) != 0) { dstart = new Date(new Timestamp(rs.getLong(i) * 1000).getTime()); } } else if (SQL_FIELD_DATE_END.equalsIgnoreCase(column)) { if (rs.getLong(i) != 0) { dend = new Date(new Timestamp(rs.getLong(i) * 1000).getTime()); } } else if (SQL_FIELD_SENSITIVITY.equalsIgnoreCase(column)) { Short sensitivity = rs.getShort(i); if (sensitivity == 0) { c.getAccessClass().setPropertyValue(new Short((short) 2)); } else { c.getAccessClass().setPropertyValue(new Short((short) 0)); } } // Unhandled columns are just ignored } c.setAllDay(new Boolean(false)); if (dstart != null) { c.getDtStart().setPropertyValue(getStringFromDateUTC(dstart)); } if (dend != null) { c.getDtEnd().setPropertyValue(getStringFromDateUTC(dend)); } if(!user_session.equals(user)){ c.getCategories().setPropertyValue("Compartilhado"); } return cw; } /** * Converts date in the DB to a date format palm. * * @param date * it should be in the "yyyyMMdd'T'HHmmss'Z'" format for * yyyy-MM-dd HH:MM:SS * @return a Date object */ private static String getStringFromDateUTC(Date date) throws Exception { SimpleDateFormat utcDateFormatter = new SimpleDateFormat(); utcDateFormatter.applyPattern(TimeUtils.PATTERN_UTC); utcDateFormatter.setTimeZone(TimeUtils.TIMEZONE_UTC); return utcDateFormatter.format(date); } /** * Converts a String object representing a date into a corresponding Date * object apt to represent a date in the DB. * * @param date * it should be in the "yyyyMMdd'T'HHmmss'Z'" * format, but will be forced into the right format also if it's * in the "yyyyMMdd'T'HHmmss" format (a 'Z' will be appended) or * in the "yyyy-MM-dd" format (in this case, the time will be considered 00:00:00) * @return a Date object */ private static Date getDateFromString(String date) throws ParseException { if (date == null || date.length() == 0) { return null; } String dateOK = null; SimpleDateFormat dateFormatter = new SimpleDateFormat(); dateFormatter.applyPattern(TimeUtils.PATTERN_UTC); String format = TimeUtils.getDateFormat(date); if (format.equals(TimeUtils.PATTERN_YYYY_MM_DD)) { dateOK = TimeUtils.convertDateFromInDayFormat(date, "000000", true); } else if (format.equals(TimeUtils.PATTERN_UTC_WOZ)) { dateOK = date + 'Z'; // the non-all-day formatter wants a 'Z' dateFormatter.setTimeZone(TimeUtils.TIMEZONE_UTC); } else { dateOK = date; // then format should be = TimeUtils.PATTERN_UTC dateFormatter.setTimeZone(TimeUtils.TIMEZONE_UTC); } return dateFormatter.parse(dateOK); } /** * Attaches the repetion to the recurrence rule of a calendar on the basis * of a ResultSet. * * @param cw * the calendar (as a CalendarWrapper) still lacking information * on the exceptions * @param rs * the result of the execution of a proper SQL SELECT statement * on the phpgw_cal_repeats table, with the cursor before its * first row * @return the CalendarWrapper object with address information attached * @throws Exception */ private CalendarWrapper addPIMCalendarExceptions(CalendarWrapper cw, ResultSet rs) throws Exception { RecurrencePattern rp = null; short recurrenceType = -1; int interval = 0; short monthOfYear = 0; short dayOfMonth = 0; short dayOfWeekMask = 0; short instance = 0; String startDatePattern = null; String endDatePattern = null; long edate = 0; boolean noend = true; String exceptions_date = null; List exceptions = new Vector(); if (rs.next()) { recurrenceType = Short.parseShort(Long.toString(rs.getLong("recur_type"))); interval = Integer.parseInt(Long.toString(rs.getLong("recur_interval"))); dayOfWeekMask = Short.parseShort(Long.toString(rs.getLong("recur_data"))); startDatePattern = getStringFromDateUTC(new Date(new Timestamp(rs .getLong("datetime") * 1000).getTime())); edate = rs.getLong("recur_enddate"); exceptions_date = rs.getString("recur_exception"); if (edate != 0) { endDatePattern = getStringFromDateUTC(new Date(new Timestamp(edate * 1000).getTime())); noend = false; } StringTokenizer exception_date = new StringTokenizer(exceptions_date, ","); while (exception_date.hasMoreTokens()) { String date = exception_date.nextToken(); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd'T'HHmmss"); ExceptionToRecurrenceRule etrr = new ExceptionToRecurrenceRule(false, sdf.format(new Date(new Long(date) * 1000)).concat("Z")); exceptions.add(etrr); } switch (recurrenceType) { case 1: { recurrenceType = 0; break; } case 2: { recurrenceType = 1; break; } case 3: { recurrenceType = 2; break; } case 4: { recurrenceType = 3; break; } case 5: { recurrenceType = 5; break; } default: break; } // Repeticao do evento rp = new RecurrencePattern(recurrenceType, (interval == 0 ? 1 : interval), monthOfYear, dayOfMonth, dayOfWeekMask, instance, startDatePattern, endDatePattern, noend); rp.setExceptions(exceptions); cw.getCalendar().getCalendarContent().setRecurrencePattern(rp); } return cw; } /** * Attaches the alarm to the recurrence rule of a calendar on the basis of a * ResultSet. * * @param cw * the calendar (as a CalendarWrapper) still lacking information * on the exceptions * @param rs * the result of the execution of a proper SQL SELECT statement * on the phpgw_async table, with the cursor before its first row * @return the CalendarWrapper object with address information attached * @throws Exception */ private CalendarWrapper addPIMCalendarAlarm(CalendarWrapper cw, ResultSet rs) throws Exception { if (rs.next()) { Reminder rt = null; Connection con = null; PreparedStatement ps = null; ResultSet rs1 = null; Date datea = null; Date dateb = null; long difDatas = 0; con = getDataSource().getConnection(); ps = con.prepareStatement("SELECT datetime from phpgw_cal WHERE cal_id = ?"); ps.setLong(1, Long.parseLong(cw.getId())); rs1 = ps.executeQuery(); if (rs1.next()) { datea = new Date(new Long(rs.getLong("next") * 1000)); dateb = new Date(new Long(rs1.getLong("datetime") * 1000)); difDatas = dateb.getTime() - datea.getTime(); rt = new Reminder(); rt.setActive(true); rt.setMinutes(new Integer(Integer.parseInt(new Long(Math.abs(difDatas / (60 * 1000))).toString()))); // Correcao: Para parar de desaparecer o alarme apos algumas sincronizacoes. // Atribui data-hora para o campo Calendar.Reminder.Time - emerson-faria.nobre@serpro.gov.br - 03/08/2009 // Comentada a linha abaixo ate que a alteracao do codigo PHP do Expresso seja colocada em producao - 29/09/2009 // rt.setTime(com.funambol.common.pim.utility.TimeUtils.convertDateTo(datea, com.funambol.common.pim.utility.TimeUtils.TIMEZONE_UTC, com.funambol.common.pim.utility.TimeUtils.PATTERN_UTC)); cw.getCalendar().getCalendarContent().setReminder(rt); } DBTools.close(con, ps, rs1); } return cw; } private String addCategory(String category) throws Exception { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; String id_category = null; try { con = getDataSource().getConnection(); ps = con.prepareStatement("SELECT cat_id from phpgw_categories WHERE cat_owner = -1 AND cat_appname = 'calendar' AND cat_name = ?"); ps.setString(1, category); rs = ps.executeQuery(); if(rs.next()){ id_category = rs.getString(1); } else { ps = con.prepareStatement("SELECT cat_id from phpgw_categories WHERE cat_owner = ? AND cat_appname = 'calendar' AND cat_name = ?"); ps.setLong(1, Long.parseLong(userId)); ps.setString(2, category); rs = ps.executeQuery(); if(rs.next()){ id_category = rs.getString(1); } } if(id_category == null){ ps = con.prepareStatement("INSERT INTO phpgw_categories (cat_owner, cat_access, cat_appname, cat_name, cat_description, cat_data, last_mod) VALUES (?, 'public', 'calendar', ?, '', 'N;', ?)"); ps.setLong(1, Long.parseLong(userId)); ps.setString(2, category); ps.setLong(3, new Long(Long.toString(new Timestamp(System.currentTimeMillis()).getTime()).substring(0, 10))); ps.executeUpdate(); ps = con.prepareStatement("SELECT cat_id from phpgw_categories WHERE cat_owner = ? AND cat_appname = 'calendar' AND cat_name = ?"); ps.setLong(1, Long.parseLong(userId)); ps.setString(2, category); rs = ps.executeQuery(); rs.next(); id_category = rs.getString(1); } } catch (Exception e) { throw new PIMDBAccessException("Error search category <" + category + "> " + e.getMessage()); } finally { DBTools.close(con, ps, rs); } return id_category; } private void addRecurrencePattern(RecurrencePattern rp, long id_cal, String sd) throws Exception { Connection con = null; PreparedStatement ps = null; String dayOfWeekMask = null; String endDatePattern = null; String exception_date = ""; String exception_time = ""; int interval = 0; short recurrenceType = -1; short recur_type = 0; try { con = getDataSource().getConnection(); interval = rp.getInterval(); recurrenceType = rp.getTypeId(); dayOfWeekMask = String.valueOf(rp.getDayOfWeekMask()); endDatePattern = rp.getEndDatePattern(); if (rp.isNoEndDate()) { endDatePattern = "0"; } else { endDatePattern = (Long.toString(getDateFromString(endDatePattern).getTime()).substring(0, 10)); } List exceptions = rp.getExceptions(); if (!exceptions.isEmpty()) { ExceptionToRecurrenceRule etrr = null; exception_time = sd.substring(9, 16); // hora inicial do evento int i = 0; int size = exceptions.size(); for (; i < size - 1; i++) { etrr = (ExceptionToRecurrenceRule) exceptions.get(i); exception_date = exception_date + (Long.toString(getDateFromString(etrr.getDate().substring(0, 9).concat(exception_time)).getTime()).substring(0, 10)) + ","; } etrr = (ExceptionToRecurrenceRule) exceptions.get(i); exception_date = exception_date + (Long.toString(getDateFromString(etrr.getDate().substring(0, 9).concat(exception_time)).getTime()).substring(0, 10)); } // Seta o tipo de repetição if (recurrenceType != -1) { switch (recurrenceType) { case 0: { recur_type = 1; break; } case 1: { recur_type = 2; break; } case 2: { recur_type = 3; break; } case 3: { recur_type = 4; break; } case 5: { recur_type = 5; break; } default: break; } } // Apaga a repeticao do evento se existir deleteRecurrencePattern(id_cal); // Insere no banco de dados a repeticao do evento ps = con.prepareStatement(SQL_INSERT_INTO_EXP_PIM_CALENDAR_REPEATS); ps.setLong(1, id_cal); ps.setLong(2, recur_type); ps.setLong(3, 0); ps.setLong(4, new Long(endDatePattern)); ps.setLong(5, interval); ps.setLong(6, new Long(dayOfWeekMask)); ps.setString(7, exception_date); ps.executeUpdate(); } catch (Exception e) { throw new PIMDBAccessException("Error event repetitive " + e.getMessage()); } finally { DBTools.close(con, ps, null); } } private void deleteRecurrencePattern(long id_cal) throws Exception { Connection con = null; PreparedStatement ps = null; try { con = getDataSource().getConnection(); ps = con.prepareStatement("DELETE FROM phpgw_cal_repeats WHERE cal_id = ?"); ps.setLong(1, id_cal); ps.executeUpdate(); } catch (Exception e) { throw new PIMDBAccessException("Error delete event repetitive " + e.getMessage()); } finally { DBTools.close(con, ps, null); } } private void addReminder(Reminder reminder, long id_cal) throws Exception { Connection con = null; PreparedStatement ps = null; try { con = getDataSource().getConnection(); int minutes = 0; long date = 0; minutes = (reminder.getMinutes() * 60); date = new Long(Long.toString(getDateFromString(reminder.getTime()).getTime()).substring(0, 10)); // Apaga o alarme do evento deleteReminder(id_cal); // Insere no banco de dados o alarme do evento ps = con.prepareStatement(SQL_INSERT_INTO_EXP_PIM_ALARM); ps.setString(1, "cal:" + id_cal + ":0"); // id ps.setLong(2, date); // next ps.setString(3, "i:" + date + ";"); // times ps.setString(4, "calendar.bocalendar.send_alarm"); // method ps.setString(5, "a:5:{s:4:\"time\";i:" + date + ";s:6:\"offset\";i:" + minutes + ";s:5:\"owner\";i:" + userId + ";s:7:\"enabled\";i:1;s:6:\"cal_id\";s:" + new Long(id_cal).toString().length() + ":\"" + id_cal + "\";}"); // data ps.setLong(6, Long.parseLong(userId)); // account_id ps.executeUpdate(); } catch (Exception e) { throw new PIMDBAccessException("Error event alarm " + e.getMessage()); } finally { DBTools.close(con, ps, null); } } private void deleteReminder(long id_cal) throws Exception { Connection con = null; PreparedStatement ps = null; try { con = getDataSource().getConnection(); ps = con.prepareStatement("DELETE FROM phpgw_async WHERE id = ? AND account_id = ?"); ps.setString(1, "cal:" + id_cal + ":0"); ps.setLong(2, Long.parseLong(userId)); ps.executeUpdate(); } catch (Exception e) { throw new PIMDBAccessException("Error delete event alarm " + e.getMessage()); } finally { DBTools.close(con, ps, null); } } }