[2082] | 1 | /** |
---|
| 2 | * This class implements methods to access contacts data in domino server data |
---|
| 3 | * @author Diorgenes Felipe Grzesiuk <diorgenes@prognus.com.br> |
---|
| 4 | * @copyright Copyright 2007-2008 Prognus |
---|
| 5 | * |
---|
| 6 | * This program is free software; you can redistribute it and/or modify |
---|
| 7 | * it under the terms of the GNU General Public License version 2 as published by |
---|
| 8 | * the Free Software Foundation. |
---|
| 9 | * |
---|
| 10 | * This program is distributed in the hope that it will be useful, |
---|
| 11 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
---|
| 12 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
---|
| 13 | * GNU General Public License for more details. |
---|
| 14 | * |
---|
| 15 | * You should have received a copy of the GNU General Public License |
---|
| 16 | * along with Foobar; if not, write to the Free Software |
---|
| 17 | * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA |
---|
| 18 | */ |
---|
| 19 | package br.com.prognus.psync.items.dao; |
---|
| 20 | |
---|
| 21 | import java.sql.Connection; |
---|
| 22 | import java.sql.PreparedStatement; |
---|
| 23 | import java.sql.ResultSet; |
---|
| 24 | import java.sql.ResultSetMetaData; |
---|
| 25 | import java.sql.SQLException; |
---|
| 26 | import java.sql.Timestamp; |
---|
| 27 | import java.text.ParseException; |
---|
| 28 | import java.text.SimpleDateFormat; |
---|
| 29 | import java.util.ArrayList; |
---|
| 30 | import java.util.Date; |
---|
| 31 | import java.util.LinkedList; |
---|
| 32 | import java.util.List; |
---|
| 33 | import java.util.StringTokenizer; |
---|
| 34 | import java.util.Vector; |
---|
| 35 | |
---|
| 36 | import br.com.prognus.psync.exception.PIMDBAccessException; |
---|
| 37 | import br.com.prognus.psync.items.model.CalendarWrapper; |
---|
| 38 | import br.com.prognus.psync.util.Def; |
---|
| 39 | |
---|
| 40 | import com.funambol.common.pim.calendar.Calendar; |
---|
| 41 | import com.funambol.common.pim.calendar.CalendarContent; |
---|
| 42 | import com.funambol.common.pim.calendar.Event; |
---|
| 43 | import com.funambol.common.pim.calendar.ExceptionToRecurrenceRule; |
---|
| 44 | import com.funambol.common.pim.calendar.RecurrencePattern; |
---|
| 45 | import com.funambol.common.pim.calendar.Reminder; |
---|
| 46 | import com.funambol.common.pim.utility.TimeUtils; |
---|
| 47 | import com.funambol.framework.security.Sync4jPrincipal; |
---|
| 48 | import com.funambol.framework.server.store.NotFoundException; |
---|
| 49 | import com.funambol.framework.tools.DBTools; |
---|
| 50 | |
---|
| 51 | public class PIMCalendarDAO extends PIMEntityDAO { |
---|
| 52 | |
---|
| 53 | // --------------------------------------------------------------- Constants |
---|
| 54 | |
---|
| 55 | private static final String SQL_AND_NO_SUBJECT_IS_SET = "AND ((C.title IS null) OR (C.title = '')) "; |
---|
| 56 | |
---|
| 57 | private static final String SQL_AND_SUBJECT_EQUALS_QUESTIONMARK = "AND C.title = ? "; |
---|
| 58 | |
---|
| 59 | private static final String SQL_AND_NO_dtstart_IS_SET = "AND C.datetime IS null "; |
---|
| 60 | |
---|
| 61 | private static final String SQL_AND_dtstart_EQUALS_QUESTIONMARK = "AND C.datetime = ? "; |
---|
| 62 | |
---|
| 63 | private static final String SQL_AND_NO_DEND_IS_SET = "AND C.edatetime IS null "; |
---|
| 64 | |
---|
| 65 | private static final String SQL_AND_DEND_EQUALS_QUESTIONMARK = "AND C.edatetime = ? "; |
---|
| 66 | |
---|
| 67 | private static final String SQL_EQUALS_QUESTIONMARK = " = ?"; |
---|
| 68 | |
---|
| 69 | private static final String SQL_EQUALS_QUESTIONMARK_COMMA = " = ?, "; |
---|
| 70 | |
---|
| 71 | protected static final int SQL_BODY_DIM = 4096; |
---|
| 72 | |
---|
| 73 | protected static final int SQL_LOCATION_DIM = 255; |
---|
| 74 | |
---|
| 75 | protected static final int SQL_SUBJECT_DIM = 255; |
---|
| 76 | |
---|
| 77 | protected static final String SQL_FIELD_ID = "cal_id"; |
---|
| 78 | |
---|
| 79 | protected static final String SQL_FIELD_USERID = "owner"; |
---|
| 80 | |
---|
| 81 | protected static final String SQL_FIELD_LAST_UPDATE = "last_update"; |
---|
| 82 | |
---|
| 83 | protected static final String SQL_FIELD_STATUS = "last_status"; |
---|
| 84 | |
---|
| 85 | protected static final String SQL_FIELD_CAL_TYPE = "cal_type"; |
---|
| 86 | |
---|
| 87 | protected static final String SQL_FIELD_SENSITIVITY = "is_public"; |
---|
| 88 | |
---|
| 89 | protected static final String SQL_FIELD_SUBJECT = "title"; |
---|
| 90 | |
---|
| 91 | protected static final String SQL_FIELD_BODY = "description"; |
---|
| 92 | |
---|
| 93 | protected static final String SQL_FIELD_LOCATION = "location"; |
---|
| 94 | |
---|
| 95 | protected static final String SQL_FIELD_DATE_START = "datetime"; |
---|
| 96 | |
---|
| 97 | protected static final String SQL_FIELD_CATEGORY = "category"; |
---|
| 98 | |
---|
| 99 | protected static final String SQL_FIELD_DATE_END = "edatetime"; |
---|
| 100 | |
---|
| 101 | protected static final String SQL_FIELD_DATE_NOW = "mdatetime"; |
---|
| 102 | |
---|
| 103 | private static final String SQL_ORDER_BY_ID = "ORDER BY cal_id ASC"; |
---|
| 104 | |
---|
| 105 | private static final String SQL_INSERT_INTO_EXP_PIM_CALENDAR = "INSERT INTO phpgw_cal " |
---|
| 106 | + "(uid, owner, category, datetime, mdatetime, edatetime, cal_type, is_public, title, description, location, ex_participants, last_update, last_status) " |
---|
| 107 | + "VALUES " + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; |
---|
| 108 | |
---|
| 109 | private static final String SQL_INSERT_INTO_EXP_PIM_CALENDAR_USER = "INSERT INTO phpgw_cal_user " |
---|
| 110 | + "(cal_id, cal_login) " + "VALUES " + "(?, ?)"; |
---|
| 111 | |
---|
| 112 | private static final String SQL_INSERT_INTO_EXP_PIM_CALENDAR_REPEATS = "INSERT INTO phpgw_cal_repeats " |
---|
| 113 | + "(cal_id, recur_type, recur_use_end, recur_enddate, recur_interval, recur_data, recur_exception) " |
---|
| 114 | + "VALUES " + "(?, ?, ?, ?, ?, ?, ?)"; |
---|
| 115 | |
---|
| 116 | private static final String SQL_INSERT_INTO_EXP_PIM_ALARM = "INSERT INTO phpgw_async " |
---|
| 117 | + "(id, next, times, method, data, account_id) " |
---|
| 118 | + "VALUES " |
---|
| 119 | + "(?, ?, ?, ?, ?, ?)"; |
---|
| 120 | |
---|
| 121 | 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 = ?"; |
---|
| 122 | |
---|
| 123 | 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' "; |
---|
| 124 | |
---|
| 125 | 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' "; |
---|
| 126 | |
---|
| 127 | 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"; |
---|
| 128 | |
---|
| 129 | 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 = ?"; |
---|
| 130 | |
---|
| 131 | private static final String SQL_UPDATE_EXP_PIM_CALENDAR_BEGIN = "UPDATE phpgw_cal SET "; |
---|
| 132 | |
---|
| 133 | private static final String SQL_UPDATE_EXP_PIM_CALENDAR_END = " WHERE cal_id = ? AND owner = ? "; |
---|
| 134 | |
---|
| 135 | private Sync4jPrincipal principal = null; |
---|
| 136 | |
---|
| 137 | /* |
---|
| 138 | * @see PIMEntityDAO#PIMEntityDAO(String, String) |
---|
| 139 | */ |
---|
| 140 | public PIMCalendarDAO(String jndiDataSourceName, Sync4jPrincipal principal) { |
---|
| 141 | |
---|
| 142 | super(jndiDataSourceName, principal.getUsername()); |
---|
| 143 | log.info("\n\n=> Created new PIMCalendarDAO for data source " + jndiDataSourceName + " and user ID " + userId); |
---|
| 144 | this.principal = principal; |
---|
| 145 | } |
---|
| 146 | |
---|
| 147 | // ----------------------------------------------------------- Public |
---|
| 148 | // methods |
---|
| 149 | |
---|
| 150 | /** |
---|
| 151 | * Adds a calendar. If necessary, a new ID is generated and set in the |
---|
| 152 | * CalendarWrapper. |
---|
| 153 | * |
---|
| 154 | * @param c |
---|
| 155 | * as a CalendarWrapper object, usually without an ID set. |
---|
| 156 | * @throws PIMDBAccessException |
---|
| 157 | * |
---|
| 158 | * @see CalendarWrapper |
---|
| 159 | */ |
---|
| 160 | public void addItem(CalendarWrapper cw) throws PIMDBAccessException { |
---|
| 161 | |
---|
| 162 | log.info("\n\n=> PIMCalendarDAO addItem begin"); |
---|
| 163 | |
---|
| 164 | Connection con = null; |
---|
| 165 | PreparedStatement ps = null; |
---|
| 166 | RecurrencePattern rp = null; |
---|
| 167 | ResultSet rs = null; |
---|
| 168 | Reminder reminder = null; |
---|
| 169 | |
---|
| 170 | String subject = null; |
---|
| 171 | String body = null; |
---|
| 172 | String location = null; |
---|
| 173 | String is_public = null; |
---|
| 174 | String category = null; |
---|
| 175 | |
---|
| 176 | Date dtstart = null; |
---|
| 177 | Date dend = null; |
---|
| 178 | long id_cal = 0; |
---|
| 179 | long dt1 = 0; |
---|
| 180 | |
---|
| 181 | try { |
---|
| 182 | |
---|
| 183 | con = getDataSource().getConnection(); |
---|
| 184 | con.setAutoCommit(false); |
---|
| 185 | |
---|
| 186 | CalendarContent c = cw.getCalendar().getCalendarContent(); |
---|
| 187 | Timestamp lastUpdate = cw.getLastUpdate(); |
---|
| 188 | |
---|
| 189 | lastUpdate = (lastUpdate == null) ? new Timestamp(System.currentTimeMillis()) : lastUpdate; |
---|
| 190 | |
---|
| 191 | // Recebe a data inicial do evento |
---|
| 192 | String sd = null; |
---|
| 193 | if (c.getDtStart() != null) { |
---|
| 194 | sd = c.getDtStart().getPropertyValueAsString(); // data inicial no formato YYYYMMddTHHMMSSZ |
---|
| 195 | dtstart = getDateFromString(sd); // data inicial no formato YYYY-MM-dd |
---|
| 196 | dt1 = new Long(Long.toString(dtstart.getTime()).substring(0, 10)); |
---|
| 197 | } |
---|
| 198 | |
---|
| 199 | // Recebe a data final do evento |
---|
| 200 | String ed = null; |
---|
| 201 | if (c.getDtEnd() != null) { |
---|
| 202 | ed = c.getDtEnd().getPropertyValueAsString(); |
---|
| 203 | dend = getDateFromString(ed); |
---|
| 204 | } |
---|
| 205 | |
---|
| 206 | rp = c.getRecurrencePattern(); // Recebe a repetição do alarme |
---|
| 207 | reminder = c.getReminder(); // Recebe o alarme do evento |
---|
| 208 | body = stringFrom(c.getDescription()); // Descricao completa do evento |
---|
| 209 | location = stringFrom(c.getLocation()); // Localizacao do evento |
---|
| 210 | is_public = (stringFrom(c.getAccessClass())).equals("2") ? "0" : "1"; // Evento particular ou publico |
---|
| 211 | subject = stringFrom(c.getSummary()); // Titulo do evento |
---|
| 212 | category = stringFrom(c.getCategories()); |
---|
| 213 | |
---|
| 214 | // Verifica o parametro uid necessario para o expresso |
---|
| 215 | ps = con.prepareStatement("select config_value from phpgw_config WHERE config_name = 'hostname' AND config_app = 'phpgwapi'"); |
---|
| 216 | rs = ps.executeQuery(); |
---|
| 217 | String uid = rs.next() ? ("-@" + rs.getString(1)) : ""; |
---|
| 218 | |
---|
| 219 | // Verifica a categoria do evento |
---|
| 220 | if(category != null && (!category.equals(""))){ |
---|
| 221 | category = addCategory(category); |
---|
| 222 | } |
---|
| 223 | |
---|
| 224 | // Prepara os dados para inserir o evento no banco de dados |
---|
| 225 | ps = con.prepareStatement(SQL_INSERT_INTO_EXP_PIM_CALENDAR); |
---|
| 226 | ps.setString(1, uid); // -@hostname |
---|
| 227 | ps.setLong(2, Long.parseLong(userId)); // owner |
---|
| 228 | ps.setString(3, category); // category |
---|
| 229 | ps.setLong(4, new Long(Long.toString(dtstart.getTime()).substring(0, 10))); // datetime |
---|
| 230 | ps.setLong(5, new Long(Long.toString(lastUpdate.getTime()).substring(0, 10))); // mdatetime |
---|
| 231 | ps.setLong(6, new Long(Long.toString(dend.getTime()).substring(0, 10))); // edatetime |
---|
| 232 | ps.setString(7, (rp == null) ? "E" : "M"); // cal_type |
---|
| 233 | ps.setLong(8, Long.parseLong(is_public)); // is_plublic |
---|
| 234 | ps.setString(9, truncate(subject, SQL_SUBJECT_DIM)); // title |
---|
| 235 | ps.setString(10, truncate(body, SQL_BODY_DIM)); // body of event, a little description of it |
---|
| 236 | ps.setString(11, truncate(location, SQL_LOCATION_DIM)); // location of the event |
---|
| 237 | ps.setString(12, ""); // Participants outside |
---|
| 238 | ps.setLong(13, lastUpdate.getTime()); // last update |
---|
| 239 | ps.setString(14, String.valueOf(Def.PIM_STATE_NEW)); // status |
---|
| 240 | ps.executeUpdate(); |
---|
| 241 | |
---|
| 242 | // Verifica o id maximo do evento inserido |
---|
| 243 | ps = con.prepareStatement("select max(cal_id) from phpgw_cal limit 1"); |
---|
| 244 | rs = ps.executeQuery(); |
---|
| 245 | |
---|
| 246 | if (rs.next()) { |
---|
| 247 | long id = rs.getInt(1); |
---|
| 248 | id_cal = (id == 0) ? 1 : id; |
---|
| 249 | } |
---|
| 250 | |
---|
| 251 | cw.setId(Long.toString(id_cal)); |
---|
| 252 | |
---|
| 253 | ps = con.prepareStatement(SQL_INSERT_INTO_EXP_PIM_CALENDAR_USER); |
---|
| 254 | ps.setLong(1, id_cal); |
---|
| 255 | ps.setLong(2, Long.parseLong(userId)); // owner |
---|
| 256 | ps.executeUpdate(); |
---|
| 257 | |
---|
| 258 | // Evento com repetição |
---|
| 259 | if (rp != null) { |
---|
| 260 | addRecurrencePattern(rp, id_cal, sd); |
---|
| 261 | } |
---|
| 262 | |
---|
| 263 | log.trace("SERPRO - pimcalendardao - ANTES if (reminder != null && reminder.isActive())"); |
---|
| 264 | |
---|
| 265 | //Correcao - Se o valor do atributo alarm do calendario eh null, atribui. emerson-faria.nobre@serpro.gov.br |
---|
| 266 | if (reminder != null && reminder.isActive() && reminder.getTime() == null) { |
---|
| 267 | String strDate = String.valueOf(dt1 * 1000 - reminder.getMinutes() * 60000); |
---|
| 268 | 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); |
---|
| 269 | reminder.setTime(dtUTC); |
---|
| 270 | } |
---|
| 271 | |
---|
| 272 | // Evento com Alarme |
---|
| 273 | if (reminder != null && reminder.isActive()) { |
---|
| 274 | addReminder(reminder, id_cal); |
---|
| 275 | } |
---|
| 276 | |
---|
| 277 | log.trace("SERPRO - pimcalendardao - APOS if (reminder != null && reminder.isActive())"); |
---|
| 278 | |
---|
| 279 | con.commit(); |
---|
| 280 | con.setAutoCommit(true); |
---|
| 281 | |
---|
| 282 | } catch (Exception e) { |
---|
| 283 | throw new PIMDBAccessException("Error adding a calendar item: " |
---|
| 284 | + e.getMessage()); |
---|
| 285 | } finally { |
---|
| 286 | DBTools.close(con, ps, rs); |
---|
| 287 | } |
---|
| 288 | |
---|
| 289 | log.info("\n\n=>Added item with ID " + id_cal); |
---|
| 290 | log.info("\n\n=>PIMCalendarDAO addItem end"); |
---|
| 291 | } |
---|
| 292 | |
---|
| 293 | /** |
---|
| 294 | * Updates a calendar. |
---|
| 295 | * |
---|
| 296 | * @param c |
---|
| 297 | * as a CalendarWrapper object. If its last update time is null, |
---|
| 298 | * then it's set to the current time. |
---|
| 299 | * @return the UID of the contact |
---|
| 300 | * @throws PIMDBAccessException |
---|
| 301 | * |
---|
| 302 | * @see CalendarWrapper |
---|
| 303 | */ |
---|
| 304 | public String updateItem(CalendarWrapper cw) throws PIMDBAccessException, Exception { |
---|
| 305 | |
---|
| 306 | log.info("\n\n=> PIMCalendarDAO updateItem begin"); |
---|
| 307 | |
---|
| 308 | Connection con = null; |
---|
| 309 | PreparedStatement ps = null; |
---|
| 310 | ResultSet rs = null; |
---|
| 311 | CalendarContent c = null; |
---|
| 312 | RecurrencePattern rp = null; |
---|
| 313 | Reminder reminder = null; |
---|
| 314 | |
---|
| 315 | long id_cal = 0; |
---|
| 316 | long dt1 = 0; |
---|
| 317 | long dt2 = 0; |
---|
| 318 | |
---|
| 319 | Date dend = null; |
---|
| 320 | Date dtstart = null; |
---|
| 321 | |
---|
| 322 | String body = null; |
---|
| 323 | String location = null; |
---|
| 324 | String subject = null; |
---|
| 325 | String is_public = null; |
---|
| 326 | String category = null; |
---|
| 327 | |
---|
| 328 | StringBuffer queryUpdateFunPimCalendar = null; |
---|
| 329 | |
---|
| 330 | try { |
---|
| 331 | |
---|
| 332 | con = getDataSource().getConnection(); |
---|
| 333 | |
---|
| 334 | |
---|
| 335 | |
---|
| 336 | Timestamp lastUpdate = (cw.getLastUpdate() == null) ? new Timestamp(System.currentTimeMillis()) : cw.getLastUpdate(); |
---|
| 337 | |
---|
| 338 | c = cw.getCalendar().getCalendarContent(); |
---|
| 339 | |
---|
| 340 | id_cal = Long.parseLong(cw.getId()); |
---|
| 341 | |
---|
| 342 | // Verifica se o evento pertence ao dono para ser atualizado |
---|
| 343 | ps = con.prepareStatement("SELECT cal_id FROM phpgw_cal WHERE cal_id = ? AND owner = ?"); |
---|
| 344 | ps.setLong(1, id_cal); |
---|
| 345 | ps.setLong(2, Long.parseLong(userId)); |
---|
| 346 | rs = ps.executeQuery(); |
---|
| 347 | |
---|
| 348 | if (rs.next()) { |
---|
| 349 | |
---|
| 350 | rp = c.getRecurrencePattern(); // Recebe a repetição do alarme |
---|
| 351 | reminder = c.getReminder(); // Recebe o alarme do evento |
---|
| 352 | body = stringFrom(c.getDescription()); // Descrição completa do evento |
---|
| 353 | location = stringFrom(c.getLocation()); // Localizacao do evento |
---|
| 354 | is_public = (stringFrom(c.getAccessClass())).equals("2") ? "0" : "1"; // Evento particular ou publico |
---|
| 355 | subject = stringFrom(c.getSummary()); // Titulo do evento |
---|
| 356 | category = stringFrom(c.getCategories()); |
---|
| 357 | |
---|
| 358 | // Verifica a categoria do evento |
---|
| 359 | if(category != null && (!category.equals(""))){ |
---|
| 360 | category = addCategory(category); |
---|
| 361 | } else { |
---|
| 362 | category = null; |
---|
| 363 | } |
---|
| 364 | |
---|
| 365 | // Recebe a data inicial do evento |
---|
| 366 | String sd = null; |
---|
| 367 | if (c.getDtStart() != null) { |
---|
| 368 | sd = c.getDtStart().getPropertyValueAsString(); |
---|
| 369 | dtstart = getDateFromString(sd); |
---|
| 370 | dt1 = new Long(Long.toString(dtstart.getTime()).substring(0, 10)); |
---|
| 371 | } |
---|
| 372 | |
---|
| 373 | // Recebe a data final do evento |
---|
| 374 | String ed = null; |
---|
| 375 | if (c.getDtEnd() != null) { |
---|
| 376 | ed = c.getDtEnd().getPropertyValueAsString(); |
---|
| 377 | dend = getDateFromString(ed); |
---|
| 378 | dt2 = new Long(Long.toString(dend.getTime()).substring(0, 10)); |
---|
| 379 | } |
---|
| 380 | |
---|
| 381 | // Prepara os dados para inserir o evento no banco de dados |
---|
| 382 | queryUpdateFunPimCalendar = new StringBuffer(); |
---|
| 383 | queryUpdateFunPimCalendar.append(SQL_UPDATE_EXP_PIM_CALENDAR_BEGIN); |
---|
| 384 | queryUpdateFunPimCalendar.append(SQL_FIELD_CATEGORY).append(SQL_EQUALS_QUESTIONMARK_COMMA); |
---|
| 385 | queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_START).append(SQL_EQUALS_QUESTIONMARK_COMMA); |
---|
| 386 | queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_NOW).append(SQL_EQUALS_QUESTIONMARK_COMMA); |
---|
| 387 | queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_END).append(SQL_EQUALS_QUESTIONMARK_COMMA); |
---|
| 388 | queryUpdateFunPimCalendar.append(SQL_FIELD_CAL_TYPE).append(SQL_EQUALS_QUESTIONMARK_COMMA); |
---|
| 389 | queryUpdateFunPimCalendar.append(SQL_FIELD_SENSITIVITY).append(SQL_EQUALS_QUESTIONMARK_COMMA); |
---|
| 390 | queryUpdateFunPimCalendar.append(SQL_FIELD_SUBJECT).append(SQL_EQUALS_QUESTIONMARK_COMMA); |
---|
| 391 | queryUpdateFunPimCalendar.append(SQL_FIELD_BODY).append(SQL_EQUALS_QUESTIONMARK_COMMA); |
---|
| 392 | queryUpdateFunPimCalendar.append(SQL_FIELD_LOCATION).append(SQL_EQUALS_QUESTIONMARK_COMMA); |
---|
| 393 | queryUpdateFunPimCalendar.append(SQL_FIELD_LAST_UPDATE).append(SQL_EQUALS_QUESTIONMARK_COMMA); |
---|
| 394 | queryUpdateFunPimCalendar.append(SQL_FIELD_STATUS).append(SQL_EQUALS_QUESTIONMARK); |
---|
| 395 | queryUpdateFunPimCalendar.append(SQL_UPDATE_EXP_PIM_CALENDAR_END); |
---|
| 396 | |
---|
| 397 | ps = con.prepareStatement(queryUpdateFunPimCalendar.toString()); |
---|
| 398 | ps.setString(1, category); //category |
---|
| 399 | ps.setLong(2, dt1); // datetime |
---|
| 400 | ps.setLong(3, new Long(Long.toString(lastUpdate.getTime()).substring(0, 10))); // mdatetime |
---|
| 401 | ps.setLong(4, dt2); // datetime |
---|
| 402 | ps.setString(5, (rp == null) ? "E" : "M"); // cal_type |
---|
| 403 | ps.setLong(6, Long.parseLong(is_public)); // is_plublic |
---|
| 404 | ps.setString(7, truncate(subject, SQL_SUBJECT_DIM)); |
---|
| 405 | ps.setString(8, truncate(body, SQL_BODY_DIM)); |
---|
| 406 | ps.setString(9, truncate(location, SQL_LOCATION_DIM)); // location |
---|
| 407 | ps.setLong(10, lastUpdate.getTime()); // last update |
---|
| 408 | ps.setString(11, String.valueOf(Def.PIM_STATE_UPDATED)); // status |
---|
| 409 | ps.setLong(12, id_cal); // cal_id |
---|
| 410 | // Correcao - Incluido typecast 'userId' para compatibilizar com o postgresql 8.3 - emerson-faria.nobre@serpro.gov.br - 28/jan/2010 |
---|
| 411 | // ps.setString(13, userId); // owner |
---|
| 412 | ps.setLong(13, Long.parseLong(userId)); |
---|
| 413 | ps.executeUpdate(); |
---|
| 414 | // Evento com repetição |
---|
| 415 | if (rp != null) { |
---|
| 416 | addRecurrencePattern(rp, id_cal, sd); // adiciona a repeticao |
---|
| 417 | } else { |
---|
| 418 | deleteRecurrencePattern(id_cal); // delete a repeticao |
---|
| 419 | } |
---|
| 420 | // Evento com Alarme |
---|
| 421 | |
---|
| 422 | //Correcao - Se o valor do atributo alarm do calendario eh null, atribui. emerson-faria.nobre@serpro.gov.br |
---|
| 423 | if (reminder != null && reminder.isActive() && reminder.getTime() == null) { |
---|
| 424 | String strDate = String.valueOf(dt1 * 1000 - reminder.getMinutes() * 60000); |
---|
| 425 | 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); |
---|
| 426 | reminder.setTime(dtUTC); |
---|
| 427 | } |
---|
| 428 | |
---|
| 429 | if (reminder != null && reminder.isActive() && reminder.getTime() != null) { |
---|
| 430 | addReminder(reminder, id_cal); // adiciona o alarme |
---|
| 431 | } else { |
---|
| 432 | deleteReminder(id_cal); // deleta o alarme |
---|
| 433 | } |
---|
| 434 | } |
---|
| 435 | |
---|
| 436 | con.commit(); |
---|
| 437 | con.setAutoCommit(true); |
---|
| 438 | |
---|
| 439 | } catch (Exception e) { |
---|
| 440 | |
---|
| 441 | throw new PIMDBAccessException( |
---|
| 442 | "\n=> Error updating a calendar item: " + e.getMessage()); |
---|
| 443 | |
---|
| 444 | } finally { |
---|
| 445 | |
---|
| 446 | DBTools.close(con, ps, rs); |
---|
| 447 | } |
---|
| 448 | |
---|
| 449 | log.info("\n\n=>Update item with ID " + id_cal); |
---|
| 450 | log.info("\n\n=>PIMCalendarDAO updateItem end"); |
---|
| 451 | |
---|
| 452 | return Long.toString(id_cal); |
---|
| 453 | } |
---|
| 454 | |
---|
| 455 | /** |
---|
| 456 | * Retrieves the UID list of all calendars belonging to the user of the type |
---|
| 457 | * prescribed by the DAO set-up. |
---|
| 458 | * |
---|
| 459 | * @throws PIMDBAccessException |
---|
| 460 | * @return a List of UIDs (as String objects) |
---|
| 461 | */ |
---|
| 462 | public List getAllItems() throws PIMDBAccessException { |
---|
| 463 | |
---|
| 464 | log.info("\n\n=> PIMCalendarDAO getAllItems begin"); |
---|
| 465 | |
---|
| 466 | Connection con = null; |
---|
| 467 | PreparedStatement ps = null; |
---|
| 468 | List calendars = new ArrayList(); |
---|
| 469 | ResultSet rs = null; |
---|
| 470 | |
---|
| 471 | try { |
---|
| 472 | // Looks up the data source when the first connection is created |
---|
| 473 | con = getDataSource().getConnection(); |
---|
| 474 | |
---|
| 475 | ps = con.prepareStatement(SQL_GET_EXP_PIM_CALENDAR_ID_LIST_BY_USER + SQL_ORDER_BY_ID); |
---|
| 476 | |
---|
| 477 | //Correcao - Incluido typecast 'userId' para compatibilizar com o postgresql 8.3 - emerson-faria.nobre@serpro.gov.br - 26/feb/2010 |
---|
| 478 | // ps.setString(1, userId); |
---|
| 479 | ps.setLong(1, Long.parseLong(userId)); |
---|
| 480 | |
---|
| 481 | rs = ps.executeQuery(); |
---|
| 482 | |
---|
| 483 | while (rs.next()) { |
---|
| 484 | calendars.add(Long.toString(rs.getLong(1))); // It's the first |
---|
| 485 | } |
---|
| 486 | |
---|
| 487 | } catch (Exception e) { |
---|
| 488 | throw new PIMDBAccessException("\n=> Error listing contacts. " + e.getMessage()); |
---|
| 489 | } finally { |
---|
| 490 | DBTools.close(con, ps, rs); |
---|
| 491 | } |
---|
| 492 | |
---|
| 493 | log.info("\n\n=> PIMCalendarDAO getAllItems end"); |
---|
| 494 | |
---|
| 495 | return calendars; |
---|
| 496 | } |
---|
| 497 | |
---|
| 498 | public CalendarWrapper getItem(String uid) throws PIMDBAccessException { |
---|
| 499 | |
---|
| 500 | log.info("\n\n=> DAO start getItem " + uid); |
---|
| 501 | |
---|
| 502 | Connection con = null; |
---|
| 503 | PreparedStatement ps = null; |
---|
| 504 | ResultSet rs = null; |
---|
| 505 | ResultSet rs1 = null; |
---|
| 506 | CalendarWrapper cw = null; |
---|
| 507 | |
---|
| 508 | try { |
---|
| 509 | // Looks up the data source when the first connection is created |
---|
| 510 | con = getDataSource().getConnection(); |
---|
| 511 | |
---|
| 512 | try { |
---|
| 513 | |
---|
| 514 | ps = con.prepareStatement(SQL_GET_EXP_PIM_CALENDAR_BY_ID_USERID); |
---|
| 515 | ps.setLong(1, Long.parseLong(uid)); |
---|
| 516 | rs = ps.executeQuery(); |
---|
| 517 | |
---|
| 518 | //Correcao - Incluido typecast cat_ID::text para compatibilizar com o postgreSQL 8.3 - emerson.faria.nobre@serpro.gov.br - 29/jan/2010 |
---|
| 519 | // 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 = ?"); |
---|
| 520 | 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 = ?"); |
---|
| 521 | ps.setLong(1, Long.parseLong(uid)); |
---|
| 522 | rs1 = ps.executeQuery(); |
---|
| 523 | |
---|
| 524 | cw = createCalendar(uid, userId, rs, (rs1.next() ? rs1.getString(1) : "")); |
---|
| 525 | |
---|
| 526 | } catch (SQLException sqle) { |
---|
| 527 | throw new SQLException("\n=> Error while adding createCalendar. " + sqle, sqle.getSQLState()); |
---|
| 528 | } |
---|
| 529 | |
---|
| 530 | try { |
---|
| 531 | |
---|
| 532 | ps = con.prepareStatement(SQL_GET_FNBL_PIM_CALENDAR_EXCEPTION_BY_CALENDAR); |
---|
| 533 | ps.setLong(1, Long.parseLong(uid)); |
---|
| 534 | rs = ps.executeQuery(); |
---|
| 535 | cw = addPIMCalendarExceptions(cw, rs); |
---|
| 536 | |
---|
| 537 | } catch (SQLException sqle) { |
---|
| 538 | throw new SQLException("\n=> Error while adding addPIMCalendarExceptions. " + sqle, sqle.getSQLState()); |
---|
| 539 | } |
---|
| 540 | |
---|
| 541 | try { |
---|
| 542 | |
---|
| 543 | ps = con.prepareStatement("SELECT next, times, data from phpgw_async WHERE id = ? AND account_id = ?"); |
---|
| 544 | ps.setString(1, "cal:" + uid + ":0"); |
---|
| 545 | ps.setLong(2, Long.parseLong(userId)); |
---|
| 546 | rs = ps.executeQuery(); |
---|
| 547 | cw = addPIMCalendarAlarm(cw, rs); |
---|
| 548 | |
---|
| 549 | } catch (SQLException sqle) { |
---|
| 550 | throw new SQLException("\n=> Error while adding addPIMCalendarAlarm. " + sqle, sqle.getSQLState()); |
---|
| 551 | } |
---|
| 552 | |
---|
| 553 | } catch (Exception e) { |
---|
| 554 | throw new PIMDBAccessException("\n=> Error retrieving a calendar item: " + e, e); |
---|
| 555 | } finally { |
---|
| 556 | DBTools.close(con, ps, rs); |
---|
| 557 | DBTools.close(con, ps, rs1); |
---|
| 558 | } |
---|
| 559 | |
---|
| 560 | return cw; |
---|
| 561 | } |
---|
| 562 | |
---|
| 563 | /** |
---|
| 564 | * Removes the calendar with given UID and sets its last_update field, |
---|
| 565 | * provided it has the same userId as this DAO. The deletion is soft |
---|
| 566 | * (reversible). |
---|
| 567 | * |
---|
| 568 | * @param uid |
---|
| 569 | * corresponds to the id field in the phpgw_cal table |
---|
| 570 | * @throws PIMDBAccessException |
---|
| 571 | */ |
---|
| 572 | public void removeItem(String uid) throws PIMDBAccessException { |
---|
| 573 | |
---|
| 574 | log.info("\n\n=> DAO start removeItem " + uid); |
---|
| 575 | |
---|
| 576 | Connection con = null; |
---|
| 577 | PreparedStatement ps = null; |
---|
| 578 | ResultSet rs = null; |
---|
| 579 | |
---|
| 580 | try { |
---|
| 581 | // Looks up the data source when the first connection is created |
---|
| 582 | con = getDataSource().getConnection(); |
---|
| 583 | con.setAutoCommit(false); |
---|
| 584 | |
---|
| 585 | ps = con.prepareStatement("SELECT cal_id FROM phpgw_cal WHERE cal_id = ? AND owner = ?"); |
---|
| 586 | ps.setLong(1, Long.parseLong(uid)); |
---|
| 587 | ps.setLong(2, Long.parseLong(userId)); |
---|
| 588 | rs = ps.executeQuery(); |
---|
| 589 | |
---|
| 590 | if (rs.next()) { |
---|
| 591 | |
---|
| 592 | ps = con.prepareStatement("DELETE FROM phpgw_async WHERE id = ?"); |
---|
| 593 | ps.setString(1, "cal:" + uid + ":0"); |
---|
| 594 | ps.executeUpdate(); |
---|
| 595 | |
---|
| 596 | ps = con.prepareStatement("DELETE FROM phpgw_cal_repeats WHERE cal_id = ?"); |
---|
| 597 | ps.setLong(1, Long.parseLong(uid)); |
---|
| 598 | ps.executeUpdate(); |
---|
| 599 | |
---|
| 600 | ps = con.prepareStatement("DELETE FROM phpgw_cal_user WHERE cal_id = ?"); |
---|
| 601 | ps.setLong(1, Long.parseLong(uid)); |
---|
| 602 | ps.executeUpdate(); |
---|
| 603 | |
---|
| 604 | ps = con.prepareStatement("DELETE FROM phpgw_cal WHERE cal_id = ?"); |
---|
| 605 | ps.setLong(1, Long.parseLong(uid)); |
---|
| 606 | ps.executeUpdate(); |
---|
| 607 | |
---|
| 608 | } else { |
---|
| 609 | |
---|
| 610 | ps = con.prepareStatement("UPDATE phpgw_cal_user SET cal_status = 'R' WHERE cal_id = ? AND cal_login = ?"); |
---|
| 611 | ps.setLong(1, Long.parseLong(uid)); |
---|
| 612 | ps.setLong(2, Long.parseLong(userId)); |
---|
| 613 | ps.executeUpdate(); |
---|
| 614 | |
---|
| 615 | } |
---|
| 616 | |
---|
| 617 | con.commit(); |
---|
| 618 | con.setAutoCommit(true); |
---|
| 619 | |
---|
| 620 | } catch (Exception e) { |
---|
| 621 | throw new PIMDBAccessException("\n=> Error deleting calendar.", e); |
---|
| 622 | } finally { |
---|
| 623 | DBTools.close(con, ps, rs); |
---|
| 624 | } |
---|
| 625 | } |
---|
| 626 | |
---|
| 627 | /** |
---|
| 628 | * Removes a calendar, provided it has the same userId as this DAO. The |
---|
| 629 | * deletion is soft (reversible). |
---|
| 630 | * |
---|
| 631 | * @param calendar |
---|
| 632 | * whence the UID and the last update Date are extracted |
---|
| 633 | * @throws PIMDBAccessException |
---|
| 634 | */ |
---|
| 635 | public void removeItem(CalendarWrapper calendar) throws PIMDBAccessException { |
---|
| 636 | removeItem(calendar.getId()); |
---|
| 637 | } |
---|
| 638 | |
---|
| 639 | /** |
---|
| 640 | * Deletes (reversibly) all calendars belonging to the user of the type |
---|
| 641 | * prescribed by the DAO set-up. The last_update field of the (soft-)deleted |
---|
| 642 | * items will be set at a given timestamp. |
---|
| 643 | * |
---|
| 644 | * @throws PIMDBAccessException |
---|
| 645 | */ |
---|
| 646 | public void removeAllItems() throws PIMDBAccessException { |
---|
| 647 | |
---|
| 648 | log.info("\n\n=> DAO start removeAllItems"); |
---|
| 649 | |
---|
| 650 | Connection con = null; |
---|
| 651 | PreparedStatement ps = null; |
---|
| 652 | |
---|
| 653 | try { |
---|
| 654 | // Looks up the data source when the first connection is created |
---|
| 655 | con = getDataSource().getConnection(); |
---|
| 656 | con.setAutoCommit(false); |
---|
| 657 | |
---|
| 658 | ps = con.prepareStatement("DELETE FROM phpgw_async WHERE account_id = ?"); |
---|
| 659 | ps.setLong(1, Long.parseLong(userId)); |
---|
| 660 | ps.executeUpdate(); |
---|
| 661 | |
---|
| 662 | ps = con.prepareStatement("DELETE FROM phpgw_cal_repeats WHERE cal_id IN(SELECT cal_id FROM phpgw_cal_user WHERE cal_login = ?)"); |
---|
| 663 | ps.setLong(1, Long.parseLong(userId)); |
---|
| 664 | ps.executeUpdate(); |
---|
| 665 | |
---|
| 666 | ps = con.prepareStatement("DELETE FROM phpgw_cal_user WHERE cal_id IN(SELECT cal_id FROM phpgw_cal WHERE owner = ?)"); |
---|
| 667 | ps.setLong(1, Long.parseLong(userId)); |
---|
| 668 | ps.executeUpdate(); |
---|
| 669 | |
---|
| 670 | ps = con.prepareStatement("DELETE FROM phpgw_cal WHERE owner = ?"); |
---|
| 671 | ps.setLong(1, Long.parseLong(userId)); |
---|
| 672 | ps.executeUpdate(); |
---|
| 673 | |
---|
| 674 | con.commit(); |
---|
| 675 | con.setAutoCommit(true); |
---|
| 676 | |
---|
| 677 | } catch (Exception e) { |
---|
| 678 | throw new PIMDBAccessException("\n=> Error deleting calendars.", e); |
---|
| 679 | } finally { |
---|
| 680 | DBTools.close(con, ps, null); |
---|
| 681 | } |
---|
| 682 | } |
---|
| 683 | |
---|
| 684 | /** |
---|
| 685 | * Retrieves the UID list of the calendars belonging to the user filtered |
---|
| 686 | * according to the given time interval and status. Only the calendars |
---|
| 687 | * corresponding to the type set-up of the DAO are retrieved. |
---|
| 688 | * |
---|
| 689 | * @param since |
---|
| 690 | * the earliest allowed last-update Timestamp |
---|
| 691 | * @param to |
---|
| 692 | * the latest allowed last-update Timestamp |
---|
| 693 | * @param status |
---|
| 694 | * 'D' for deleted items, 'N' for new items, 'U' for updated |
---|
| 695 | * items |
---|
| 696 | * @throws PIMDBAccessException |
---|
| 697 | * @return a List of UIDs (as String objects) |
---|
| 698 | */ |
---|
| 699 | // Alteracao - Incluido parametro sourceURI - emerson-faria.nobre@serpro.gov.br - 17/set/2009 |
---|
| 700 | protected List getItemsHavingStatus(Timestamp since, Timestamp to, char status, String sourceURI) throws PIMDBAccessException { |
---|
| 701 | |
---|
| 702 | log.info("\n\n=> Seeking '" + status + "' items of sourceURI:'" + sourceURI + "' in time interval ]" + since + "; " + to + "["); |
---|
| 703 | |
---|
| 704 | Connection con = null; |
---|
| 705 | PreparedStatement ps = null; |
---|
| 706 | List calendars = new ArrayList(); |
---|
| 707 | ResultSet rs = null; |
---|
| 708 | |
---|
| 709 | try { |
---|
| 710 | // Looks up the data source when the first connection is created |
---|
| 711 | con = getDataSource().getConnection(); |
---|
| 712 | |
---|
| 713 | if (status == 'D') { |
---|
| 714 | |
---|
| 715 | //Correcao - Incluido typecast 'cal_id::text' para compatibilizar com o postgresql 8.3 - emerson-faria.nobre@serpro.gov.br - 28/feb/2010 |
---|
| 716 | // 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"); |
---|
| 717 | 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"); |
---|
| 718 | ps.setLong(1, Long.parseLong(userId)); |
---|
| 719 | ps.setLong(2, this.principal.getId()); |
---|
| 720 | rs = ps.executeQuery(); |
---|
| 721 | |
---|
| 722 | } else { |
---|
| 723 | |
---|
| 724 | ps = con.prepareStatement(SQL_GET_EXP_PIM_CALENDAR_ID_LIST_BY_USER_TIME_STATUS + SQL_ORDER_BY_ID); |
---|
| 725 | //Correcao - Incluido typecast 'userId' para compatibilizar com o postgresql 8.3 - emerson-faria.nobre@serpro.gov.br - 28/feb/2010 |
---|
| 726 | // ps.setString(1, userId); |
---|
| 727 | ps.setLong(1, Long.parseLong(userId)); |
---|
| 728 | ps.setLong(2, since.getTime()); |
---|
| 729 | ps.setLong(3, to.getTime()); |
---|
| 730 | ps.setString(4, String.valueOf(status)); |
---|
| 731 | rs = ps.executeQuery(); |
---|
| 732 | |
---|
| 733 | } |
---|
| 734 | |
---|
| 735 | while (rs.next()) { |
---|
| 736 | calendars.add(Long.toString(rs.getLong(1))); // It's the first and only column |
---|
| 737 | log.info("\n=> Item found " + rs.getLong(1)); |
---|
| 738 | } |
---|
| 739 | |
---|
| 740 | } catch (Exception e) { |
---|
| 741 | e.printStackTrace(); |
---|
| 742 | throw new PIMDBAccessException("\n=> Error listing contacts. " + e.getMessage()); |
---|
| 743 | } finally { |
---|
| 744 | DBTools.close(con, ps, rs); |
---|
| 745 | } |
---|
| 746 | |
---|
| 747 | return calendars; |
---|
| 748 | } |
---|
| 749 | |
---|
| 750 | /** |
---|
| 751 | * Retrieves the state of the given item, provided it's been modified after |
---|
| 752 | * a certain moment. |
---|
| 753 | * |
---|
| 754 | * @param uid |
---|
| 755 | * the UID of the item to be checked (as a String object) |
---|
| 756 | * @param since |
---|
| 757 | * the Timestamp that the item's lastUpdate field is checked |
---|
| 758 | * against: if the item has been modified before that moment, an |
---|
| 759 | * "unchanged" state marker is returned |
---|
| 760 | * @throws PIMDBAccessException |
---|
| 761 | * @return a char identifying either one of the 3 standard states ("new", |
---|
| 762 | * "deleted", "updated") or the special "unchanged" status, all of |
---|
| 763 | * them as defined in br.com.br.psync.pim.util.Def |
---|
| 764 | */ |
---|
| 765 | public char getItemState(String uid, Timestamp since) throws PIMDBAccessException { |
---|
| 766 | |
---|
| 767 | log.info("\n\n=> DAO start getItemState"); |
---|
| 768 | |
---|
| 769 | Connection con = null; |
---|
| 770 | PreparedStatement ps = null; |
---|
| 771 | ResultSet rs = null; |
---|
| 772 | char status; |
---|
| 773 | |
---|
| 774 | try { |
---|
| 775 | // Looks up the data source when the first connection is created |
---|
| 776 | con = getDataSource().getConnection(); |
---|
| 777 | |
---|
| 778 | ps = con.prepareStatement(SQL_GET_STATUS_BY_ID_USER_TIME); |
---|
| 779 | |
---|
| 780 | ps.setLong(1, Long.parseLong(uid)); |
---|
| 781 | ps.setString(2, userId); |
---|
| 782 | ps.setLong(3, since.getTime()); |
---|
| 783 | |
---|
| 784 | rs = ps.executeQuery(); |
---|
| 785 | |
---|
| 786 | if (!rs.next()) { |
---|
| 787 | |
---|
| 788 | status = Def.PIM_STATE_UNCHANGED; |
---|
| 789 | log.info("\n=> Item " + uid + "'s status wasn't retrieved " |
---|
| 790 | + "because the item hasn't been modified since " |
---|
| 791 | + since + " or the item doesn't exist"); |
---|
| 792 | |
---|
| 793 | } else { |
---|
| 794 | |
---|
| 795 | status = rs.getString(1).charAt(0); |
---|
| 796 | log.info("\n=> Item " + uid + " has status \'" + status + "\'"); |
---|
| 797 | |
---|
| 798 | } |
---|
| 799 | |
---|
| 800 | } catch (Exception e) { |
---|
| 801 | throw new PIMDBAccessException("\n=> Error retrieving item state. " + e.getMessage()); |
---|
| 802 | } finally { |
---|
| 803 | DBTools.close(con, ps, rs); |
---|
| 804 | } |
---|
| 805 | |
---|
| 806 | return status; |
---|
| 807 | } |
---|
| 808 | |
---|
| 809 | /** |
---|
| 810 | * Retrieves the UID list of the calendars considered to be "twins" of a |
---|
| 811 | * given contact. |
---|
| 812 | * |
---|
| 813 | * @param c |
---|
| 814 | * the Calendar object representing the calendar whose twins need |
---|
| 815 | * be found. In the present implementation, only the following |
---|
| 816 | * data matter: |
---|
| 817 | * <UL> |
---|
| 818 | * <LI>date start |
---|
| 819 | * <LI>date end |
---|
| 820 | * <LI>location |
---|
| 821 | * </UL> |
---|
| 822 | * @throws PIMDBAccessException |
---|
| 823 | * @return a List of UIDs (as String objects) that may be empty but not null |
---|
| 824 | */ |
---|
| 825 | public List getTwinItems(Calendar c) throws PIMDBAccessException { |
---|
| 826 | |
---|
| 827 | log.info("\n\n\n=> PIMCalendarDAO getTwinItems begin"); |
---|
| 828 | |
---|
| 829 | LinkedList twins = new LinkedList(); |
---|
| 830 | Connection con = null; |
---|
| 831 | PreparedStatement ps = null; |
---|
| 832 | ResultSet rs = null; |
---|
| 833 | |
---|
| 834 | try { |
---|
| 835 | |
---|
| 836 | // Looks up the data source when the first connection is created |
---|
| 837 | con = getDataSource().getConnection(); |
---|
| 838 | |
---|
| 839 | Date dtStart = null; |
---|
| 840 | Date dtEnd = null; |
---|
| 841 | |
---|
| 842 | dtStart = getDateFromString(stringFrom(c.getCalendarContent().getDtStart())); |
---|
| 843 | dtEnd = getDateFromString(stringFrom(c.getCalendarContent().getDtEnd())); |
---|
| 844 | |
---|
| 845 | StringBuffer sqlGetCalendarTwinList = new StringBuffer(SQL_GET_EXP_PIM_CALENDAR_ID_LIST_BY_USER); |
---|
| 846 | |
---|
| 847 | String subject = stringFrom(c.getCalendarContent().getSummary(), true); // Empty implies null; |
---|
| 848 | |
---|
| 849 | if ("null".equals(subject)) { |
---|
| 850 | subject = null; |
---|
| 851 | } |
---|
| 852 | |
---|
| 853 | if (subject == null) { |
---|
| 854 | sqlGetCalendarTwinList.append(SQL_AND_NO_SUBJECT_IS_SET); |
---|
| 855 | } else { |
---|
| 856 | sqlGetCalendarTwinList.append(SQL_AND_SUBJECT_EQUALS_QUESTIONMARK); |
---|
| 857 | } |
---|
| 858 | |
---|
| 859 | if (dtStart == null) { |
---|
| 860 | sqlGetCalendarTwinList.append(SQL_AND_NO_dtstart_IS_SET); |
---|
| 861 | } else { |
---|
| 862 | sqlGetCalendarTwinList.append(SQL_AND_dtstart_EQUALS_QUESTIONMARK); |
---|
| 863 | } |
---|
| 864 | |
---|
| 865 | if (dtEnd == null) { |
---|
| 866 | sqlGetCalendarTwinList.append(SQL_AND_NO_DEND_IS_SET); |
---|
| 867 | } else { |
---|
| 868 | sqlGetCalendarTwinList.append(SQL_AND_DEND_EQUALS_QUESTIONMARK); |
---|
| 869 | } |
---|
| 870 | |
---|
| 871 | sqlGetCalendarTwinList.append(SQL_ORDER_BY_ID); |
---|
| 872 | |
---|
| 873 | StringBuilder sb = new StringBuilder(100); |
---|
| 874 | sb.append("\nLooking for items having: "); |
---|
| 875 | |
---|
| 876 | if (subject == null || subject.length() == 0) { |
---|
| 877 | sb.append("\n> subject: <N/A>"); |
---|
| 878 | } else { |
---|
| 879 | sb.append("\n> subject: '").append(subject).append('\''); |
---|
| 880 | } |
---|
| 881 | if (dtStart == null) { |
---|
| 882 | sb.append("\n> start date: <N/A>"); |
---|
| 883 | } else { |
---|
| 884 | sb.append("\n> start date: ").append(dtStart); |
---|
| 885 | } |
---|
| 886 | if (dtEnd == null) { |
---|
| 887 | sb.append("\n> end date: <N/A>"); |
---|
| 888 | } else { |
---|
| 889 | sb.append("\n> end date: ").append(dtEnd); |
---|
| 890 | } |
---|
| 891 | |
---|
| 892 | log.info(sb.toString()); |
---|
| 893 | |
---|
| 894 | ps = con.prepareStatement(sqlGetCalendarTwinList.toString()); |
---|
| 895 | |
---|
| 896 | int k = 1; |
---|
| 897 | |
---|
| 898 | // Correcao - Incluido typecast 'userId' para compatibilizar com o postgresql 8.3 - emerson-faria.nobre@serpro.gov.br - 01/fev/2010 |
---|
| 899 | // ps.setString(k++, userId); |
---|
| 900 | ps.setLong(k++, Long.parseLong(userId)); |
---|
| 901 | |
---|
| 902 | if (subject != null) { |
---|
| 903 | ps.setString(k++, subject); |
---|
| 904 | } |
---|
| 905 | if (dtStart != null) { |
---|
| 906 | ps.setLong(k++, new Long(Long.toString(dtStart.getTime()).substring(0, 10))); |
---|
| 907 | } |
---|
| 908 | if (dtEnd != null) { |
---|
| 909 | ps.setLong(k++, new Long(Long.toString(dtEnd.getTime()).substring(0, 10))); |
---|
| 910 | } |
---|
| 911 | |
---|
| 912 | rs = ps.executeQuery(); |
---|
| 913 | |
---|
| 914 | long twinId; |
---|
| 915 | |
---|
| 916 | while (rs.next()) { |
---|
| 917 | |
---|
| 918 | twinId = rs.getLong(1); // dend is not relevant in this case |
---|
| 919 | log.info("\n\n=> Twin event found: " + twinId); |
---|
| 920 | |
---|
| 921 | twins.add(Long.toString(twinId)); |
---|
| 922 | } |
---|
| 923 | |
---|
| 924 | } catch (Exception e) { |
---|
| 925 | throw new PIMDBAccessException("\n=> Error retrieving twin. " + e); |
---|
| 926 | } finally { |
---|
| 927 | DBTools.close(con, ps, rs); |
---|
| 928 | } |
---|
| 929 | |
---|
| 930 | log.info("\n\n\n=> PIMCalendarDAO getTwinItems end"); |
---|
| 931 | |
---|
| 932 | return twins; |
---|
| 933 | } |
---|
| 934 | |
---|
| 935 | // ---------------------------------------------------------- Private |
---|
| 936 | // methods |
---|
| 937 | |
---|
| 938 | /** |
---|
| 939 | * Creates a ContactWrapper object of Event type from a ResultSet. |
---|
| 940 | * |
---|
| 941 | * @param wrapperId |
---|
| 942 | * the UID of the wrapper object to be returned |
---|
| 943 | * @param rs |
---|
| 944 | * the result of the execution of a proper SQL SELECT statement |
---|
| 945 | * on the phpgw_cal table, with the cursor before its first row |
---|
| 946 | * @return a newly created ContactWrapper initialized with the fields in the |
---|
| 947 | * result set |
---|
| 948 | * @throws java.sql.SQLException |
---|
| 949 | * @throws NotFoundException |
---|
| 950 | */ |
---|
| 951 | protected static CalendarWrapper createCalendar(String wrapperId, String user_session ,ResultSet rs, String category) throws NotFoundException, Exception { |
---|
| 952 | |
---|
| 953 | if (!rs.next()) { |
---|
| 954 | throw new NotFoundException("\n=> No calendar found."); |
---|
| 955 | } |
---|
| 956 | |
---|
| 957 | ResultSetMetaData rsmd = rs.getMetaData(); |
---|
| 958 | int columnCount = rsmd.getColumnCount(); |
---|
| 959 | |
---|
| 960 | String column = null; |
---|
| 961 | |
---|
| 962 | String uid = null; |
---|
| 963 | String user = null; |
---|
| 964 | |
---|
| 965 | Date dstart = null; |
---|
| 966 | Date dend = null; |
---|
| 967 | |
---|
| 968 | uid = String.valueOf(rs.getLong(SQL_FIELD_ID)); |
---|
| 969 | user = rs.getString(SQL_FIELD_USERID); |
---|
| 970 | |
---|
| 971 | Calendar cal = new Calendar(); |
---|
| 972 | Reminder r = new Reminder(); |
---|
| 973 | CalendarContent c = new Event(); |
---|
| 974 | |
---|
| 975 | c.setReminder(r); |
---|
| 976 | cal.setEvent((Event) c); |
---|
| 977 | |
---|
| 978 | CalendarWrapper cw = new CalendarWrapper(wrapperId, user, cal); |
---|
| 979 | |
---|
| 980 | for (int i = 1; i <= columnCount; i++) { |
---|
| 981 | |
---|
| 982 | column = rsmd.getColumnName(i); |
---|
| 983 | |
---|
| 984 | if (SQL_FIELD_ID.equalsIgnoreCase(column)) { |
---|
| 985 | // Does nothing: field already set at construction time |
---|
| 986 | } else if (SQL_FIELD_LAST_UPDATE.equalsIgnoreCase(column)) { |
---|
| 987 | cw.setLastUpdate(new Timestamp(rs.getLong(i))); |
---|
| 988 | } else if (SQL_FIELD_USERID.equalsIgnoreCase(column)) { |
---|
| 989 | // Does nothing: field already set at construction time |
---|
| 990 | } else if (SQL_FIELD_CATEGORY.equalsIgnoreCase(column)) { |
---|
| 991 | c.getCategories().setPropertyValue(category); |
---|
| 992 | } else if (SQL_FIELD_STATUS.equalsIgnoreCase(column)) { |
---|
| 993 | cw.setStatus(rs.getString(i).charAt(0)); |
---|
| 994 | } else if (SQL_FIELD_LOCATION.equalsIgnoreCase(column)) { |
---|
| 995 | c.getLocation().setPropertyValue(rs.getString(i)); |
---|
| 996 | } else if (SQL_FIELD_SUBJECT.equalsIgnoreCase(column)) { |
---|
| 997 | c.getSummary().setPropertyValue(rs.getString(i)); |
---|
| 998 | } else if (SQL_FIELD_BODY.equalsIgnoreCase(column)) { |
---|
| 999 | c.getDescription().setPropertyValue(rs.getString(i)); |
---|
| 1000 | } else if (SQL_FIELD_DATE_START.equalsIgnoreCase(column)) { |
---|
| 1001 | if (rs.getLong(i) != 0) { |
---|
| 1002 | dstart = new Date(new Timestamp(rs.getLong(i) * 1000).getTime()); |
---|
| 1003 | } |
---|
| 1004 | } else if (SQL_FIELD_DATE_END.equalsIgnoreCase(column)) { |
---|
| 1005 | if (rs.getLong(i) != 0) { |
---|
| 1006 | dend = new Date(new Timestamp(rs.getLong(i) * 1000).getTime()); |
---|
| 1007 | } |
---|
| 1008 | } else if (SQL_FIELD_SENSITIVITY.equalsIgnoreCase(column)) { |
---|
| 1009 | Short sensitivity = rs.getShort(i); |
---|
| 1010 | if (sensitivity == 0) { |
---|
| 1011 | c.getAccessClass().setPropertyValue(new Short((short) 2)); |
---|
| 1012 | } else { |
---|
| 1013 | c.getAccessClass().setPropertyValue(new Short((short) 0)); |
---|
| 1014 | } |
---|
| 1015 | } |
---|
| 1016 | // Unhandled columns are just ignored |
---|
| 1017 | } |
---|
| 1018 | |
---|
| 1019 | c.setAllDay(new Boolean(false)); |
---|
| 1020 | |
---|
| 1021 | if (dstart != null) { |
---|
| 1022 | c.getDtStart().setPropertyValue(getStringFromDateUTC(dstart)); |
---|
| 1023 | } |
---|
| 1024 | |
---|
| 1025 | if (dend != null) { |
---|
| 1026 | c.getDtEnd().setPropertyValue(getStringFromDateUTC(dend)); |
---|
| 1027 | } |
---|
| 1028 | |
---|
| 1029 | if(!user_session.equals(user)){ |
---|
| 1030 | c.getCategories().setPropertyValue("Compartilhado"); |
---|
| 1031 | } |
---|
| 1032 | |
---|
| 1033 | return cw; |
---|
| 1034 | } |
---|
| 1035 | |
---|
| 1036 | /** |
---|
| 1037 | * Converts date in the DB to a date format palm. |
---|
| 1038 | * |
---|
| 1039 | * @param date |
---|
| 1040 | * it should be in the "yyyyMMdd'T'HHmmss'Z'" format for |
---|
| 1041 | * yyyy-MM-dd HH:MM:SS |
---|
| 1042 | * @return a Date object |
---|
| 1043 | */ |
---|
| 1044 | |
---|
| 1045 | private static String getStringFromDateUTC(Date date) throws Exception { |
---|
| 1046 | |
---|
| 1047 | SimpleDateFormat utcDateFormatter = new SimpleDateFormat(); |
---|
| 1048 | |
---|
| 1049 | utcDateFormatter.applyPattern(TimeUtils.PATTERN_UTC); |
---|
| 1050 | utcDateFormatter.setTimeZone(TimeUtils.TIMEZONE_UTC); |
---|
| 1051 | |
---|
| 1052 | return utcDateFormatter.format(date); |
---|
| 1053 | } |
---|
| 1054 | |
---|
| 1055 | /** |
---|
| 1056 | * Converts a String object representing a date into a corresponding Date |
---|
| 1057 | * object apt to represent a date in the DB. |
---|
| 1058 | * |
---|
| 1059 | * @param date |
---|
| 1060 | * it should be in the "yyyyMMdd'T'HHmmss'Z'" |
---|
| 1061 | * format, but will be forced into the right format also if it's |
---|
| 1062 | * in the "yyyyMMdd'T'HHmmss" format (a 'Z' will be appended) or |
---|
| 1063 | * in the "yyyy-MM-dd" format (in this case, the time will be considered 00:00:00) |
---|
| 1064 | * @return a Date object |
---|
| 1065 | */ |
---|
| 1066 | private static Date getDateFromString(String date) throws ParseException { |
---|
| 1067 | |
---|
| 1068 | if (date == null || date.length() == 0) { |
---|
| 1069 | return null; |
---|
| 1070 | } |
---|
| 1071 | |
---|
| 1072 | String dateOK = null; |
---|
| 1073 | |
---|
| 1074 | SimpleDateFormat dateFormatter = new SimpleDateFormat(); |
---|
| 1075 | dateFormatter.applyPattern(TimeUtils.PATTERN_UTC); |
---|
| 1076 | |
---|
| 1077 | String format = TimeUtils.getDateFormat(date); |
---|
| 1078 | |
---|
| 1079 | if (format.equals(TimeUtils.PATTERN_YYYY_MM_DD)) { |
---|
| 1080 | |
---|
| 1081 | dateOK = TimeUtils.convertDateFromInDayFormat(date, "000000", true); |
---|
| 1082 | |
---|
| 1083 | } else if (format.equals(TimeUtils.PATTERN_UTC_WOZ)) { |
---|
| 1084 | |
---|
| 1085 | dateOK = date + 'Z'; // the non-all-day formatter wants a 'Z' |
---|
| 1086 | dateFormatter.setTimeZone(TimeUtils.TIMEZONE_UTC); |
---|
| 1087 | |
---|
| 1088 | } else { |
---|
| 1089 | |
---|
| 1090 | dateOK = date; // then format should be = TimeUtils.PATTERN_UTC |
---|
| 1091 | dateFormatter.setTimeZone(TimeUtils.TIMEZONE_UTC); |
---|
| 1092 | } |
---|
| 1093 | |
---|
| 1094 | return dateFormatter.parse(dateOK); |
---|
| 1095 | } |
---|
| 1096 | |
---|
| 1097 | /** |
---|
| 1098 | * Attaches the repetion to the recurrence rule of a calendar on the basis |
---|
| 1099 | * of a ResultSet. |
---|
| 1100 | * |
---|
| 1101 | * @param cw |
---|
| 1102 | * the calendar (as a CalendarWrapper) still lacking information |
---|
| 1103 | * on the exceptions |
---|
| 1104 | * @param rs |
---|
| 1105 | * the result of the execution of a proper SQL SELECT statement |
---|
| 1106 | * on the phpgw_cal_repeats table, with the cursor before its |
---|
| 1107 | * first row |
---|
| 1108 | * @return the CalendarWrapper object with address information attached |
---|
| 1109 | * @throws Exception |
---|
| 1110 | */ |
---|
| 1111 | private CalendarWrapper addPIMCalendarExceptions(CalendarWrapper cw, ResultSet rs) throws Exception { |
---|
| 1112 | |
---|
| 1113 | RecurrencePattern rp = null; |
---|
| 1114 | short recurrenceType = -1; |
---|
| 1115 | int interval = 0; |
---|
| 1116 | short monthOfYear = 0; |
---|
| 1117 | short dayOfMonth = 0; |
---|
| 1118 | short dayOfWeekMask = 0; |
---|
| 1119 | short instance = 0; |
---|
| 1120 | String startDatePattern = null; |
---|
| 1121 | String endDatePattern = null; |
---|
| 1122 | long edate = 0; |
---|
| 1123 | boolean noend = true; |
---|
| 1124 | String exceptions_date = null; |
---|
| 1125 | List exceptions = new Vector(); |
---|
| 1126 | |
---|
| 1127 | if (rs.next()) { |
---|
| 1128 | |
---|
| 1129 | recurrenceType = Short.parseShort(Long.toString(rs.getLong("recur_type"))); |
---|
| 1130 | interval = Integer.parseInt(Long.toString(rs.getLong("recur_interval"))); |
---|
| 1131 | dayOfWeekMask = Short.parseShort(Long.toString(rs.getLong("recur_data"))); |
---|
| 1132 | startDatePattern = getStringFromDateUTC(new Date(new Timestamp(rs .getLong("datetime") * 1000).getTime())); |
---|
| 1133 | edate = rs.getLong("recur_enddate"); |
---|
| 1134 | exceptions_date = rs.getString("recur_exception"); |
---|
| 1135 | |
---|
| 1136 | if (edate != 0) { |
---|
| 1137 | endDatePattern = getStringFromDateUTC(new Date(new Timestamp(edate * 1000).getTime())); |
---|
| 1138 | noend = false; |
---|
| 1139 | } |
---|
| 1140 | |
---|
| 1141 | StringTokenizer exception_date = new StringTokenizer(exceptions_date, ","); |
---|
| 1142 | |
---|
| 1143 | while (exception_date.hasMoreTokens()) { |
---|
| 1144 | String date = exception_date.nextToken(); |
---|
| 1145 | SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd'T'HHmmss"); |
---|
| 1146 | ExceptionToRecurrenceRule etrr = new ExceptionToRecurrenceRule(false, sdf.format(new Date(new Long(date) * 1000)).concat("Z")); |
---|
| 1147 | exceptions.add(etrr); |
---|
| 1148 | } |
---|
| 1149 | |
---|
| 1150 | switch (recurrenceType) { |
---|
| 1151 | case 1: { |
---|
| 1152 | recurrenceType = 0; |
---|
| 1153 | break; |
---|
| 1154 | } |
---|
| 1155 | case 2: { |
---|
| 1156 | recurrenceType = 1; |
---|
| 1157 | break; |
---|
| 1158 | } |
---|
| 1159 | case 3: { |
---|
| 1160 | recurrenceType = 2; |
---|
| 1161 | break; |
---|
| 1162 | } |
---|
| 1163 | case 4: { |
---|
| 1164 | recurrenceType = 3; |
---|
| 1165 | break; |
---|
| 1166 | } |
---|
| 1167 | case 5: { |
---|
| 1168 | recurrenceType = 5; |
---|
| 1169 | break; |
---|
| 1170 | } |
---|
| 1171 | default: |
---|
| 1172 | break; |
---|
| 1173 | } |
---|
| 1174 | |
---|
| 1175 | // Repeticao do evento |
---|
| 1176 | rp = new RecurrencePattern(recurrenceType, (interval == 0 ? 1 : interval), monthOfYear, dayOfMonth, dayOfWeekMask, instance, startDatePattern, endDatePattern, noend); |
---|
| 1177 | rp.setExceptions(exceptions); |
---|
| 1178 | cw.getCalendar().getCalendarContent().setRecurrencePattern(rp); |
---|
| 1179 | } |
---|
| 1180 | |
---|
| 1181 | return cw; |
---|
| 1182 | } |
---|
| 1183 | |
---|
| 1184 | /** |
---|
| 1185 | * Attaches the alarm to the recurrence rule of a calendar on the basis of a |
---|
| 1186 | * ResultSet. |
---|
| 1187 | * |
---|
| 1188 | * @param cw |
---|
| 1189 | * the calendar (as a CalendarWrapper) still lacking information |
---|
| 1190 | * on the exceptions |
---|
| 1191 | * @param rs |
---|
| 1192 | * the result of the execution of a proper SQL SELECT statement |
---|
| 1193 | * on the phpgw_async table, with the cursor before its first row |
---|
| 1194 | * @return the CalendarWrapper object with address information attached |
---|
| 1195 | * @throws Exception |
---|
| 1196 | */ |
---|
| 1197 | private CalendarWrapper addPIMCalendarAlarm(CalendarWrapper cw, ResultSet rs) throws Exception { |
---|
| 1198 | |
---|
| 1199 | if (rs.next()) { |
---|
| 1200 | |
---|
| 1201 | Reminder rt = null; |
---|
| 1202 | |
---|
| 1203 | Connection con = null; |
---|
| 1204 | PreparedStatement ps = null; |
---|
| 1205 | ResultSet rs1 = null; |
---|
| 1206 | |
---|
| 1207 | Date datea = null; |
---|
| 1208 | Date dateb = null; |
---|
| 1209 | long difDatas = 0; |
---|
| 1210 | |
---|
| 1211 | con = getDataSource().getConnection(); |
---|
| 1212 | |
---|
| 1213 | ps = con.prepareStatement("SELECT datetime from phpgw_cal WHERE cal_id = ?"); |
---|
| 1214 | ps.setLong(1, Long.parseLong(cw.getId())); |
---|
| 1215 | rs1 = ps.executeQuery(); |
---|
| 1216 | |
---|
| 1217 | if (rs1.next()) { |
---|
| 1218 | |
---|
| 1219 | datea = new Date(new Long(rs.getLong("next") * 1000)); |
---|
| 1220 | dateb = new Date(new Long(rs1.getLong("datetime") * 1000)); |
---|
| 1221 | difDatas = dateb.getTime() - datea.getTime(); |
---|
| 1222 | |
---|
| 1223 | rt = new Reminder(); |
---|
| 1224 | rt.setActive(true); |
---|
| 1225 | rt.setMinutes(new Integer(Integer.parseInt(new Long(Math.abs(difDatas / (60 * 1000))).toString()))); |
---|
| 1226 | // Correcao: Para parar de desaparecer o alarme apos algumas sincronizacoes. |
---|
| 1227 | // Atribui data-hora para o campo Calendar.Reminder.Time - emerson-faria.nobre@serpro.gov.br - 03/08/2009 |
---|
| 1228 | // Comentada a linha abaixo ate que a alteracao do codigo PHP do Expresso seja colocada em producao - 29/09/2009 |
---|
| 1229 | // 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)); |
---|
| 1230 | |
---|
| 1231 | cw.getCalendar().getCalendarContent().setReminder(rt); |
---|
| 1232 | |
---|
| 1233 | } |
---|
| 1234 | |
---|
| 1235 | DBTools.close(con, ps, rs1); |
---|
| 1236 | } |
---|
| 1237 | |
---|
| 1238 | return cw; |
---|
| 1239 | } |
---|
| 1240 | |
---|
| 1241 | private String addCategory(String category) throws Exception { |
---|
| 1242 | |
---|
| 1243 | Connection con = null; |
---|
| 1244 | PreparedStatement ps = null; |
---|
| 1245 | ResultSet rs = null; |
---|
| 1246 | String id_category = null; |
---|
| 1247 | |
---|
| 1248 | try { |
---|
| 1249 | |
---|
| 1250 | con = getDataSource().getConnection(); |
---|
| 1251 | |
---|
| 1252 | ps = con.prepareStatement("SELECT cat_id from phpgw_categories WHERE cat_owner = -1 AND cat_appname = 'calendar' AND cat_name = ?"); |
---|
| 1253 | ps.setString(1, category); |
---|
| 1254 | rs = ps.executeQuery(); |
---|
| 1255 | |
---|
| 1256 | if(rs.next()){ |
---|
| 1257 | id_category = rs.getString(1); |
---|
| 1258 | } else { |
---|
| 1259 | |
---|
| 1260 | ps = con.prepareStatement("SELECT cat_id from phpgw_categories WHERE cat_owner = ? AND cat_appname = 'calendar' AND cat_name = ?"); |
---|
| 1261 | ps.setLong(1, Long.parseLong(userId)); |
---|
| 1262 | ps.setString(2, category); |
---|
| 1263 | rs = ps.executeQuery(); |
---|
| 1264 | |
---|
| 1265 | if(rs.next()){ |
---|
| 1266 | id_category = rs.getString(1); |
---|
| 1267 | } |
---|
| 1268 | } |
---|
| 1269 | |
---|
| 1270 | |
---|
| 1271 | if(id_category == null){ |
---|
| 1272 | |
---|
| 1273 | 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;', ?)"); |
---|
| 1274 | ps.setLong(1, Long.parseLong(userId)); |
---|
| 1275 | ps.setString(2, category); |
---|
| 1276 | ps.setLong(3, new Long(Long.toString(new Timestamp(System.currentTimeMillis()).getTime()).substring(0, 10))); |
---|
| 1277 | ps.executeUpdate(); |
---|
| 1278 | |
---|
| 1279 | ps = con.prepareStatement("SELECT cat_id from phpgw_categories WHERE cat_owner = ? AND cat_appname = 'calendar' AND cat_name = ?"); |
---|
| 1280 | ps.setLong(1, Long.parseLong(userId)); |
---|
| 1281 | ps.setString(2, category); |
---|
| 1282 | rs = ps.executeQuery(); |
---|
| 1283 | |
---|
| 1284 | rs.next(); |
---|
| 1285 | |
---|
| 1286 | id_category = rs.getString(1); |
---|
| 1287 | |
---|
| 1288 | } |
---|
| 1289 | |
---|
| 1290 | } catch (Exception e) { |
---|
| 1291 | throw new PIMDBAccessException("Error search category <" + category + "> " + e.getMessage()); |
---|
| 1292 | } finally { |
---|
| 1293 | DBTools.close(con, ps, rs); |
---|
| 1294 | } |
---|
| 1295 | |
---|
| 1296 | return id_category; |
---|
| 1297 | } |
---|
| 1298 | |
---|
| 1299 | private void addRecurrencePattern(RecurrencePattern rp, long id_cal, String sd) throws Exception { |
---|
| 1300 | |
---|
| 1301 | Connection con = null; |
---|
| 1302 | PreparedStatement ps = null; |
---|
| 1303 | |
---|
| 1304 | String dayOfWeekMask = null; |
---|
| 1305 | String endDatePattern = null; |
---|
| 1306 | String exception_date = ""; |
---|
| 1307 | String exception_time = ""; |
---|
| 1308 | |
---|
| 1309 | int interval = 0; |
---|
| 1310 | short recurrenceType = -1; |
---|
| 1311 | short recur_type = 0; |
---|
| 1312 | |
---|
| 1313 | try { |
---|
| 1314 | |
---|
| 1315 | con = getDataSource().getConnection(); |
---|
| 1316 | |
---|
| 1317 | interval = rp.getInterval(); |
---|
| 1318 | recurrenceType = rp.getTypeId(); |
---|
| 1319 | dayOfWeekMask = String.valueOf(rp.getDayOfWeekMask()); |
---|
| 1320 | endDatePattern = rp.getEndDatePattern(); |
---|
| 1321 | |
---|
| 1322 | if (rp.isNoEndDate()) { |
---|
| 1323 | endDatePattern = "0"; |
---|
| 1324 | } else { |
---|
| 1325 | endDatePattern = (Long.toString(getDateFromString(endDatePattern).getTime()).substring(0, 10)); |
---|
| 1326 | } |
---|
| 1327 | |
---|
| 1328 | List exceptions = rp.getExceptions(); |
---|
| 1329 | |
---|
| 1330 | if (!exceptions.isEmpty()) { |
---|
| 1331 | |
---|
| 1332 | ExceptionToRecurrenceRule etrr = null; |
---|
| 1333 | exception_time = sd.substring(9, 16); // hora inicial do evento |
---|
| 1334 | |
---|
| 1335 | int i = 0; |
---|
| 1336 | int size = exceptions.size(); |
---|
| 1337 | |
---|
| 1338 | for (; i < size - 1; i++) { |
---|
| 1339 | |
---|
| 1340 | etrr = (ExceptionToRecurrenceRule) exceptions.get(i); |
---|
| 1341 | exception_date = exception_date + (Long.toString(getDateFromString(etrr.getDate().substring(0, 9).concat(exception_time)).getTime()).substring(0, 10)) + ","; |
---|
| 1342 | } |
---|
| 1343 | |
---|
| 1344 | etrr = (ExceptionToRecurrenceRule) exceptions.get(i); |
---|
| 1345 | exception_date = exception_date + (Long.toString(getDateFromString(etrr.getDate().substring(0, 9).concat(exception_time)).getTime()).substring(0, 10)); |
---|
| 1346 | } |
---|
| 1347 | |
---|
| 1348 | // Seta o tipo de repetição |
---|
| 1349 | if (recurrenceType != -1) { |
---|
| 1350 | |
---|
| 1351 | switch (recurrenceType) { |
---|
| 1352 | case 0: { |
---|
| 1353 | recur_type = 1; |
---|
| 1354 | break; |
---|
| 1355 | } |
---|
| 1356 | case 1: { |
---|
| 1357 | recur_type = 2; |
---|
| 1358 | break; |
---|
| 1359 | } |
---|
| 1360 | case 2: { |
---|
| 1361 | recur_type = 3; |
---|
| 1362 | break; |
---|
| 1363 | } |
---|
| 1364 | case 3: { |
---|
| 1365 | recur_type = 4; |
---|
| 1366 | break; |
---|
| 1367 | } |
---|
| 1368 | case 5: { |
---|
| 1369 | recur_type = 5; |
---|
| 1370 | break; |
---|
| 1371 | } |
---|
| 1372 | default: |
---|
| 1373 | break; |
---|
| 1374 | } |
---|
| 1375 | } |
---|
| 1376 | |
---|
| 1377 | // Apaga a repeticao do evento se existir |
---|
| 1378 | deleteRecurrencePattern(id_cal); |
---|
| 1379 | |
---|
| 1380 | // Insere no banco de dados a repeticao do evento |
---|
| 1381 | ps = con.prepareStatement(SQL_INSERT_INTO_EXP_PIM_CALENDAR_REPEATS); |
---|
| 1382 | ps.setLong(1, id_cal); |
---|
| 1383 | ps.setLong(2, recur_type); |
---|
| 1384 | ps.setLong(3, 0); |
---|
| 1385 | ps.setLong(4, new Long(endDatePattern)); |
---|
| 1386 | ps.setLong(5, interval); |
---|
| 1387 | ps.setLong(6, new Long(dayOfWeekMask)); |
---|
| 1388 | ps.setString(7, exception_date); |
---|
| 1389 | ps.executeUpdate(); |
---|
| 1390 | |
---|
| 1391 | } catch (Exception e) { |
---|
| 1392 | throw new PIMDBAccessException("Error event repetitive " + e.getMessage()); |
---|
| 1393 | } finally { |
---|
| 1394 | DBTools.close(con, ps, null); |
---|
| 1395 | } |
---|
| 1396 | } |
---|
| 1397 | |
---|
| 1398 | private void deleteRecurrencePattern(long id_cal) throws Exception { |
---|
| 1399 | |
---|
| 1400 | Connection con = null; |
---|
| 1401 | PreparedStatement ps = null; |
---|
| 1402 | |
---|
| 1403 | try { |
---|
| 1404 | |
---|
| 1405 | con = getDataSource().getConnection(); |
---|
| 1406 | |
---|
| 1407 | ps = con.prepareStatement("DELETE FROM phpgw_cal_repeats WHERE cal_id = ?"); |
---|
| 1408 | ps.setLong(1, id_cal); |
---|
| 1409 | ps.executeUpdate(); |
---|
| 1410 | |
---|
| 1411 | } catch (Exception e) { |
---|
| 1412 | throw new PIMDBAccessException("Error delete event repetitive " + e.getMessage()); |
---|
| 1413 | } finally { |
---|
| 1414 | DBTools.close(con, ps, null); |
---|
| 1415 | } |
---|
| 1416 | } |
---|
| 1417 | |
---|
| 1418 | private void addReminder(Reminder reminder, long id_cal) throws Exception { |
---|
| 1419 | |
---|
| 1420 | Connection con = null; |
---|
| 1421 | PreparedStatement ps = null; |
---|
| 1422 | |
---|
| 1423 | try { |
---|
| 1424 | con = getDataSource().getConnection(); |
---|
| 1425 | |
---|
| 1426 | int minutes = 0; |
---|
| 1427 | long date = 0; |
---|
| 1428 | |
---|
| 1429 | minutes = (reminder.getMinutes() * 60); |
---|
| 1430 | date = new Long(Long.toString(getDateFromString(reminder.getTime()).getTime()).substring(0, 10)); |
---|
| 1431 | |
---|
| 1432 | // Apaga o alarme do evento |
---|
| 1433 | deleteReminder(id_cal); |
---|
| 1434 | |
---|
| 1435 | // Insere no banco de dados o alarme do evento |
---|
| 1436 | ps = con.prepareStatement(SQL_INSERT_INTO_EXP_PIM_ALARM); |
---|
| 1437 | ps.setString(1, "cal:" + id_cal + ":0"); // id |
---|
| 1438 | ps.setLong(2, date); // next |
---|
| 1439 | ps.setString(3, "i:" + date + ";"); // times |
---|
| 1440 | ps.setString(4, "calendar.bocalendar.send_alarm"); // method |
---|
| 1441 | ps.setString(5, "a:5:{s:4:\"time\";i:" + date |
---|
| 1442 | + ";s:6:\"offset\";i:" + minutes + ";s:5:\"owner\";i:" |
---|
| 1443 | + userId + ";s:7:\"enabled\";i:1;s:6:\"cal_id\";s:" |
---|
| 1444 | + new Long(id_cal).toString().length() + ":\"" + id_cal |
---|
| 1445 | + "\";}"); // data |
---|
| 1446 | ps.setLong(6, Long.parseLong(userId)); // account_id |
---|
| 1447 | ps.executeUpdate(); |
---|
| 1448 | |
---|
| 1449 | } catch (Exception e) { |
---|
| 1450 | throw new PIMDBAccessException("Error event alarm " + e.getMessage()); |
---|
| 1451 | } finally { |
---|
| 1452 | DBTools.close(con, ps, null); |
---|
| 1453 | } |
---|
| 1454 | } |
---|
| 1455 | |
---|
| 1456 | private void deleteReminder(long id_cal) throws Exception { |
---|
| 1457 | |
---|
| 1458 | Connection con = null; |
---|
| 1459 | PreparedStatement ps = null; |
---|
| 1460 | |
---|
| 1461 | try { |
---|
| 1462 | con = getDataSource().getConnection(); |
---|
| 1463 | |
---|
| 1464 | ps = con.prepareStatement("DELETE FROM phpgw_async WHERE id = ? AND account_id = ?"); |
---|
| 1465 | ps.setString(1, "cal:" + id_cal + ":0"); |
---|
| 1466 | ps.setLong(2, Long.parseLong(userId)); |
---|
| 1467 | ps.executeUpdate(); |
---|
| 1468 | |
---|
| 1469 | } catch (Exception e) { |
---|
| 1470 | throw new PIMDBAccessException("Error delete event alarm " + e.getMessage()); |
---|
| 1471 | } finally { |
---|
| 1472 | DBTools.close(con, ps, null); |
---|
| 1473 | } |
---|
| 1474 | } |
---|
| 1475 | |
---|
| 1476 | } |
---|