Context Navigation

← Previous Revision
Latest Revision
Next Revision →
Annotate
Revision Log

PIMCalendarDAO.java.original @ 2082

Revision 2082, 45.0 KB checked in by emersonfaria, 14 years ago (diff)
Ticket #927 - Reestruturacao dos diretorios do Funambol

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

Note: See TracBrowser for help on using the repository browser.

Download in other formats:

Original Format