Context Navigation

← Previous Revision
Latest Revision
Next Revision →
Annotate
Revision Log

PIMContactDAO.java.original @ 2082

Revision 2082, 41.7 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 PIM contact data in a data store.
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.Date;
23	import java.sql.PreparedStatement;
24	import java.sql.ResultSet;
25	import java.sql.ResultSetMetaData;
26	import java.sql.SQLException;
27	import java.sql.Timestamp;
28	import java.text.SimpleDateFormat;
29	import java.util.ArrayList;
30	import java.util.List;
31
32	import br.com.prognus.psync.exception.PIMDBAccessException;
33	import br.com.prognus.psync.items.model.ContactWrapper;
34	import br.com.prognus.psync.util.Def;
35
36	import com.funambol.common.pim.contact.BusinessDetail;
37	import com.funambol.common.pim.contact.Contact;
38	import com.funambol.common.pim.contact.Email;
39	import com.funambol.common.pim.contact.Name;
40	import com.funambol.common.pim.contact.PersonalDetail;
41	import com.funambol.common.pim.contact.Phone;
42	import com.funambol.framework.security.Sync4jPrincipal;
43	import com.funambol.framework.server.store.NotFoundException;
44	import com.funambol.framework.tools.DBTools;
45
46	public class PIMContactDAO extends PIMEntityDAO {
47
48	// --------------------------------------------------------------- Constants
49
50	private static final String SQL_ORDER_BY_ID = "ORDER BY id_contact";
51
52	private static final String SQL_GET_CONTACT_ID_LIST = "SELECT id_contact FROM phpgw_cc_contact ";
53
54	private static final String SQL_GET_CONTACT_ID_LIST_BY_USER = SQL_GET_CONTACT_ID_LIST
55	+ "WHERE id_owner = ?";
56
57	private static final String SQL_GET_CONTACT_BY_ID_USER = "SELECT id_contact, id_owner, last_update, last_status, given_names, family_names, birthdate, category FROM phpgw_cc_contact WHERE id_contact = ? AND id_owner = ? LIMIT 1";
58
59	private static final String SQL_GET_CONTACT_ITEM_BY_ID = "SELECT connection_name, connection_value, id_typeof_contact_connection FROM phpgw_cc_connections AS CO LEFT JOIN phpgw_cc_contact_conns AS CC ON CO.id_connection = CC.id_connection WHERE id_contact = ?";
60
61	private static final String SQL_GET_STATUS_BY_ID_USER_TIME = "SELECT last_status FROM phpgw_cc_contact WHERE id_contact = ? AND id_owner = ? AND last_update > ? LIMIT 1 ";
62
63	private static final String SQL_GET_FNBL_PIM_CONTACT_ID_LIST_BY_USER_TIME_STATUS = SQL_GET_CONTACT_ID_LIST
64	+ "WHERE id_owner = ? AND last_update > ? AND last_update < ? AND last_status = ? ";
65
66	private static final String SQL_GET_CONTACT_TWIN_ID_LIST = "SELECT id_contact "
67	+ "FROM phpgw_cc_contact "
68	+ "WHERE (id_owner = ?) "
69	+ "AND ((given_names is null AND (?) = '') "
70	+ "OR (lower(given_names) = ?)) "
71	+ "AND ((family_names is null AND (?) = '') "
72	+ "OR (lower(family_names) = ?)) ";
73
74	private static final String SQL_INSERT_INTO_FNBL_PIM_CONTACT = "INSERT INTO phpgw_cc_contact "
75	+ "(id_contact, id_owner, id_status, given_names, family_names, names_ordered, birthdate, category, last_update, last_status ) "
76	+ "VALUES " + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
77
78	private static final String SQL_INSERT_INTO_CONTACT_ITEM = "INSERT INTO phpgw_cc_connections "
79	+ "(id_connection, connection_name, connection_value, connection_is_default) "
80	+ "VALUES (?, ?, ?, ?) ";
81
82	private static final String SQL_ID_MAX_CONNECTIONS = "select max(id_connection) from phpgw_cc_connections";
83
84	private static final String SQL_INSERT_INTO_FNBL_CONNECTIONS = "INSERT INTO phpgw_cc_contact_conns (id_contact, id_connection, id_typeof_contact_connection) VALUES (?, ?, ?)";
85
86	private static final String SQL_UPDATE_FNBL_PIM_CONTACT_BEGIN = "UPDATE phpgw_cc_contact SET ";
87
88	private static final String SQL_UPDATE_FNBL_PIM_CONTACT_END = " WHERE id_contact = ? AND id_owner = ? ";
89
90	private static final String SQL_EQUALS_QUESTIONMARK = " = ?";
91
92	private static final String SQL_EQUALS_QUESTIONMARK_COMMA = " = ?, ";
93
94	protected static final String SQL_FIELD_ID = "id_contact";
95
96	protected static final String SQL_FIELD_USERID = "id_owner";
97
98	protected static final String SQL_FIELD_LAST_UPDATE = "last_update";
99
100	protected static final String SQL_FIELD_STATUS = "last_status";
101
102	protected static final String SQL_FIELD_FIRST_NAME = "given_names";
103
104	protected static final String SQL_FIELD_LAST_NAME = "family_names";
105
106	protected static final String SQL_FIELD_NAME = "names_ordered";
107
108	protected static final String SQL_FIELD_NICK = "alias";
109
110	protected static final String SQL_FIELD_BIRTHDAY = "birthdate";
111
112	protected static final String SQL_FIELD_CATEGORY = "category";
113
114	protected static final int SQL_FIRSTNAME_DIM = 49;
115
116	protected static final int SQL_LASTNAME_DIM = 50;
117
118	protected static final int SQL_EMAIL_DIM = 50;
119
120	protected static final int SQL_PHONE_DIM = 50;
121
122	protected static final int SQL_CATEGORY_DIM = 20;
123
124	protected static final String TYPE_EMAIL_1_ADDRESS = "Principal";
125
126	protected static final String FIELD_EMAIL_1_ADDRESS = "Email1Address";
127
128	protected static final String TYPE_EMAIL_2_ADDRESS = "Alternativo";
129
130	protected static final String FIELD_EMAIL_2_ADDRESS = "OtherEmail2Address";
131
132	protected static final String TYPE_PRIMARY_TELEPHONE_NUMBER = "Principal";
133
134	protected static final String FIELD_PRIMARY_TELEPHONE_NUMBER = "PrimaryTelephoneNumber";
135
136	protected static final String TYPE_HOME_TELEPHONE_NUMBER = "Residencial";
137
138	protected static final String FIELD_HOME_TELEPHONE_NUMBER = "HomeTelephoneNumber";
139
140	protected static final String TYPE_MOBILE_TELEPHONE_NUMBER = "Celular";
141
142	protected static final String FIELD_MOBILE_TELEPHONE_NUMBER = "MobileTelephoneNumber";
143
144	protected static final String TYPE_BUSINESS_TELEPHONE_NUMBER = "Comercial";
145
146	protected static final String FIELD_BUSINESS_TELEPHONE_NUMBER = "BusinessTelephoneNumber";
147
148	protected static final String TYPE_BUSINESS_FAX_NUMBER = "Fax";
149
150	protected static final String FIELD_BUSINESS_FAX_NUMBER = "BusinessFaxNumber";
151
152	protected static final String TYPE_PAGER_NUMBER = "Pager";
153
154	protected static final String FIELD_PAGER_NUMBER = "PagerNumber";
155
156	protected static final String SQL_FIELD_ITEM_NAME = "connection_name";
157
158	protected static final String SQL_FIELD_ITEM_VALUE = "connection_value";
159
160	protected static final String SQL_FIELD_ITEM_TYPE = "id_typeof_contact_connection";
161
162	private Sync4jPrincipal principal = null;
163
164	// -------------------------------------------------------------
165	// Constructors
166
167	/**
168	* @see PIMEntityDAO#PIMEntityDAO(String, String)
169	*/
170	public PIMContactDAO(String jndiDataSourceName, Sync4jPrincipal principal) {
171
172	super(jndiDataSourceName, principal.getUsername());
173
174	log.info("\n\n=> Created new PIMContactDAO for data source "
175	+ jndiDataSourceName + " and user ID LDAP " + userId
176	+ " user login " + principal.getUsername() + "\n");
177
178	this.principal = principal;
179	}
180
181	// ----------------------------------------------------------- Public
182	// methods
183
184	/**
185	* Adds a contact. If necessary, a new ID is generated and set in the
186	* ContactWrapper.
187	*
188	* @param c
189	* as a ContactWrapper object, usually without an ID set.
190	* @throws PIMDBAccessException
191	* @throws SQLException
192	*
193	* @see ContactWrapper
194	*/
195	public void addItem(ContactWrapper cw) throws PIMDBAccessException {
196
197	log.info("\n\n=> PIMContactDAO addItem begin\n");
198
199	Connection con = null;
200	PreparedStatement ps = null;
201	ResultSet rs = null;
202
203	long id_contact = 0;
204	long id_connection = 0;
205
206	PersonalDetail personalDetail = null;
207	BusinessDetail businessDetail = null;
208
209	Name name = null;
210	Phone phone = null;
211	Email email = null;
212
213	List<Email> emails = new ArrayList();
214	List<Phone> phones = new ArrayList();
215
216	String phoneType = null;
217	String firstName = null;
218	String lastName = null;
219	String birthday = null;
220	String type = null;
221	String category = null;
222
223	StringBuffer fullName = null;
224	Date anniversary = null;
225
226	try {
227
228	con = getDataSource().getConnection();
229	con.setAutoCommit(false);
230
231	Contact c = cw.getContact();
232	Timestamp lastUpdate = cw.getLastUpdate();
233
234	personalDetail = c.getPersonalDetail();
235	businessDetail = c.getBusinessDetail();
236	name = c.getName();
237
238	lastUpdate = (lastUpdate == null) ? new Timestamp(System
239	.currentTimeMillis()) : lastUpdate;
240
241	if (personalDetail != null) {
242	emails.addAll(personalDetail.getEmails());
243	phones.addAll(personalDetail.getPhones());
244	birthday = personalDetail.getBirthday();
245	category = stringFrom(c.getCategories());
246	}
247
248	if (businessDetail != null) {
249	emails.addAll(businessDetail.getEmails());
250	phones.addAll(businessDetail.getPhones());
251	}
252
253	if (name != null) {
254	firstName = stringFrom(name.getFirstName());
255	lastName = stringFrom(name.getLastName());
256	}
257
258	// consulta o id maximo da tabela contatos
259	ps = con
260	.prepareStatement("select max(id_contact) from phpgw_cc_contact limit 1");
261	rs = ps.executeQuery();
262
263	if (rs.next()) {
264	long id = rs.getInt(1);
265	id_contact = (id == 0) ? 1 : ++id;
266	}
267
268	cw.setId(Long.toString(id_contact));
269
270	// Formata o nome completo do contato
271	fullName = new StringBuffer();
272
273	if (firstName != null && firstName.length() > 0)
274	fullName.append(truncate(firstName, SQL_FIRSTNAME_DIM));
275	if (lastName != null && lastName.length() > 0) {
276	if (firstName != null && firstName.length() > 0
277	&& lastName != null && lastName.length() > 0)
278	fullName.append(" " + truncate(lastName, SQL_LASTNAME_DIM));
279	else
280	fullName.append(truncate(lastName, SQL_LASTNAME_DIM));
281	}
282
283	// Formata a data de aniversario do contato
284	if ((birthday != null) && (!birthday.equals(""))) {
285	anniversary = new java.sql.Date((new SimpleDateFormat(
286	"yyyy-MM-dd").parse(birthday)).getTime());
287	}
288
289	// Prepara os dados para inserir o contato no banco de dados
290	ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT);
291	ps.setLong(1, id_contact); // id_contact
292	ps.setLong(2, Long.parseLong(userId)); // id_owner
293	ps.setLong(3, 1); // id_status
294	ps.setString(4, truncate(firstName, SQL_FIRSTNAME_DIM)); // given_names
295	ps.setString(5, truncate(lastName, SQL_LASTNAME_DIM)); // family_names
296	ps.setString(6, fullName.toString()); // names_ordered
297	ps.setDate(7, anniversary); // birthdate
298	ps.setString(8, truncate(category, SQL_CATEGORY_DIM)); // category
299	ps.setLong(9, lastUpdate.getTime()); // last_update
300	ps.setString(10, String.valueOf(Def.PIM_STATE_NEW)); // last_status
301	ps.executeUpdate();
302
303	// emails
304	if (!emails.isEmpty()) {
305
306	for (int i = 0, l = emails.size(); i < l; i++) {
307
308	email = emails.get(i);
309	Boolean type_email;
310
311	if ((FIELD_EMAIL_1_ADDRESS).equals(email.getEmailType())) {
312	type = TYPE_EMAIL_1_ADDRESS;
313	type_email = true;
314	} else if ((FIELD_EMAIL_2_ADDRESS).equals(email
315	.getEmailType())) {
316	type = TYPE_EMAIL_2_ADDRESS;
317	type_email = false;
318	} else {
319	continue; // Unknown property: saves nothing
320	}
321
322	String emailValue = email.getPropertyValueAsString();
323
324	if (emailValue != null && emailValue.length() != 0) {
325
326	if (emailValue.length() > SQL_EMAIL_DIM) {
327	emailValue = emailValue.substring(0, SQL_EMAIL_DIM);
328	}
329
330	// Verifica o id maximo da tabela phpgw_cc_connections
331	ps = con.prepareStatement(SQL_ID_MAX_CONNECTIONS);
332	rs = ps.executeQuery();
333
334	if (rs.next()) {
335	long id = rs.getInt(1);
336	id_connection = (id == 0) ? 1 : ++id;
337	}
338
339	// Insere os emails do contato na tabela
340	// phpgw_cc_connections
341	ps = con.prepareStatement(SQL_INSERT_INTO_CONTACT_ITEM);
342	ps.setLong(1, id_connection);
343	ps.setString(2, type);
344	ps.setString(3, emailValue);
345	ps.setBoolean(4, type_email);
346	ps.executeUpdate();
347
348	// Insere as referencias de emails do contato na tabela
349	// phpgw_cc_connections
350	ps = con
351	.prepareStatement(SQL_INSERT_INTO_FNBL_CONNECTIONS);
352	ps.setLong(1, id_contact);
353	ps.setLong(2, id_connection);
354	ps.setLong(3, 1);
355	ps.executeUpdate();
356	}
357
358	}
359	}
360
361	// phones
362	if (!phones.isEmpty()) {
363
364	Boolean type_phone = true;
365
366	for (int i = 0, l = phones.size(); i < l; i++) {
367
368	phone = phones.get(i);
369	phoneType = phone.getPhoneType();
370
371	if ((FIELD_PRIMARY_TELEPHONE_NUMBER).equals(phoneType)) {
372	type = TYPE_PRIMARY_TELEPHONE_NUMBER;
373	} else if ((FIELD_HOME_TELEPHONE_NUMBER).equals(phoneType)) {
374	type = TYPE_HOME_TELEPHONE_NUMBER;
375	} else if ((FIELD_MOBILE_TELEPHONE_NUMBER)
376	.equals(phoneType)) {
377	type = TYPE_MOBILE_TELEPHONE_NUMBER;
378	} else if ((FIELD_BUSINESS_TELEPHONE_NUMBER)
379	.equals(phoneType)) {
380	type = TYPE_BUSINESS_TELEPHONE_NUMBER;
381	} else if ((FIELD_BUSINESS_FAX_NUMBER).equals(phoneType)) {
382	type = TYPE_BUSINESS_FAX_NUMBER;
383	} else if ((FIELD_PAGER_NUMBER).equals(phoneType)) {
384	type = TYPE_PAGER_NUMBER;
385	} else {
386	continue; // Unknown property: saves nothing
387	}
388
389	String phoneValue = phone.getPropertyValueAsString();
390
391	if (phoneValue != null && phoneValue.length() != 0) {
392
393	if (phoneValue.length() > SQL_PHONE_DIM) {
394	phoneValue = phoneValue.substring(0, SQL_PHONE_DIM);
395	}
396
397	ps = con.prepareStatement(SQL_ID_MAX_CONNECTIONS);
398	rs = ps.executeQuery();
399
400	if (rs.next()) {
401	long id = rs.getInt(1);
402	id_connection = (id == 0) ? 1 : ++id;
403	}
404
405	// Insere os telefones do contato na tabela
406	// phpgw_cc_connections
407	ps = con.prepareStatement(SQL_INSERT_INTO_CONTACT_ITEM);
408	ps.setLong(1, id_connection);
409	ps.setString(2, type);
410	ps.setString(3, phoneValue);
411	ps.setBoolean(4, type_phone);
412	ps.executeUpdate();
413
414	type_phone = false; // os demais telefones serao setados
415	// como nao padrao
416
417	// Insere as referencias de telefones do contato na
418	// tabela phpgw_cc_connections
419	ps = con
420	.prepareStatement(SQL_INSERT_INTO_FNBL_CONNECTIONS);
421	ps.setLong(1, id_contact);
422	ps.setLong(2, id_connection);
423	ps.setLong(3, 2);
424	ps.executeUpdate();
425	}
426
427	}
428	}
429
430	con.commit();
431	con.setAutoCommit(true);
432
433	} catch (Exception e) {
434	throw new PIMDBAccessException("\n=> Error adding contact.\n", e);
435	} finally {
436	DBTools.close(con, ps, rs);
437	}
438
439	log.info("\n\n=> Added item with ID " + id_contact + "\n");
440	log.info("\n\n=> PIMContactDAO addItem end\n");
441	}
442
443	/**
444	* Updates a contact.
445	*
446	* @param c
447	* as a ContactWrapper object. If its last update time is null,
448	* then it's set to the current time.
449	* @return the UID of the contact
450	* @throws PIMDBAccessException
451	*
452	* @see ContactWrapper
453	*/
454	public String updateItem(ContactWrapper cw) throws PIMDBAccessException {
455
456	log.info("\n\n=> PIMContactDAO updateItem begin\n");
457
458	Connection con = null;
459	PreparedStatement ps = null;
460	ResultSet rs = null;
461
462	long id_connection = 0;
463	long id_contact = 0;
464
465	PersonalDetail personalDetail = null;
466	BusinessDetail businessDetail = null;
467
468	Name name = null;
469	Phone phone = null;
470	Email email = null;
471	String type = null;
472
473	List<Email> emails = new ArrayList();
474	List<Phone> phones = new ArrayList();
475
476	String phoneType = null;
477	StringBuffer queryUpdateFunPimContact = null;
478
479	String firstName = null;
480	String lastName = null;
481	StringBuffer fullName = null;
482	String birthday = null;
483	String category = null;
484	Date anniversary = null;
485
486	try {
487
488	Timestamp lastUpdate = (cw.getLastUpdate() == null) ? new Timestamp(
489	System.currentTimeMillis())
490	: cw.getLastUpdate();
491
492	con = getDataSource().getConnection();
493	con.setAutoCommit(false);
494
495	Contact c = cw.getContact();
496
497	personalDetail = c.getPersonalDetail();
498	businessDetail = c.getBusinessDetail();
499	name = c.getName();
500	id_contact = Long.parseLong(cw.getId());
501
502	if (personalDetail != null) {
503	emails.addAll(personalDetail.getEmails());
504	phones.addAll(personalDetail.getPhones());
505	birthday = personalDetail.getBirthday();
506	category = stringFrom(c.getCategories());
507	}
508
509	if (businessDetail != null) {
510	emails.addAll(businessDetail.getEmails());
511	phones.addAll(businessDetail.getPhones());
512	}
513
514	if (name != null) {
515	firstName = stringFrom(name.getFirstName());
516	lastName = stringFrom(name.getLastName());
517	}
518
519	if (firstName != null && firstName.length() > SQL_FIRSTNAME_DIM) {
520	firstName = firstName.substring(0, SQL_FIRSTNAME_DIM);
521	}
522	if (lastName != null && lastName.length() > SQL_LASTNAME_DIM) {
523	lastName = lastName.substring(0, SQL_LASTNAME_DIM);
524	}
525	if (category != null && category.length() > SQL_CATEGORY_DIM) {
526	category = category.substring(0, SQL_CATEGORY_DIM);
527	}
528
529	// Formata o nome completo do contato
530	fullName = new StringBuffer();
531
532	if (firstName != null && firstName.length() > 0)
533	fullName.append(truncate(firstName, SQL_FIRSTNAME_DIM));
534	if (lastName != null && lastName.length() > 0) {
535	if (firstName != null && firstName.length() > 0
536	&& lastName != null && lastName.length() > 0)
537	fullName.append(" " + truncate(lastName, SQL_LASTNAME_DIM));
538	else
539	fullName.append(truncate(lastName, SQL_LASTNAME_DIM));
540	}
541
542	// Formata a data de aniversario do contato
543	if ((birthday != null) && (!birthday.equals(""))) {
544	anniversary = new java.sql.Date((new SimpleDateFormat(
545	"yyyy-MM-dd").parse(birthday)).getTime());
546	} else {
547	anniversary = null;
548	}
549
550	// Prepara os dados do contato para serem atualizados no banco de
551	// dados
552	queryUpdateFunPimContact = new StringBuffer();
553	queryUpdateFunPimContact.append(SQL_UPDATE_FNBL_PIM_CONTACT_BEGIN
554	+ SQL_FIELD_LAST_UPDATE + SQL_EQUALS_QUESTIONMARK_COMMA);
555	queryUpdateFunPimContact.append(SQL_FIELD_FIRST_NAME
556	+ SQL_EQUALS_QUESTIONMARK_COMMA);
557	queryUpdateFunPimContact.append(SQL_FIELD_LAST_NAME
558	+ SQL_EQUALS_QUESTIONMARK_COMMA);
559	queryUpdateFunPimContact.append(SQL_FIELD_NAME
560	+ SQL_EQUALS_QUESTIONMARK_COMMA);
561	queryUpdateFunPimContact.append(SQL_FIELD_BIRTHDAY
562	+ SQL_EQUALS_QUESTIONMARK_COMMA);
563	queryUpdateFunPimContact.append(SQL_FIELD_CATEGORY
564	+ SQL_EQUALS_QUESTIONMARK_COMMA);
565	queryUpdateFunPimContact
566	.append(SQL_FIELD_STATUS + SQL_EQUALS_QUESTIONMARK
567	+ SQL_UPDATE_FNBL_PIM_CONTACT_END);
568
569	ps = con.prepareStatement(queryUpdateFunPimContact.toString());
570
571	ps.setLong(1, lastUpdate.getTime()); // last_update
572	ps.setString(2, firstName); // given_names
573	ps.setString(3, lastName); // family_names
574	ps.setString(4, fullName.toString()); // names_ordered
575	ps.setDate(5, anniversary); // birthdate
576	ps.setString(6, category); // category
577	ps.setString(7, String.valueOf(Def.PIM_STATE_UPDATED)); // last_status
578	ps.setLong(8, id_contact); // id_contact
579	ps.setLong(9, Long.parseLong(userId)); // id_owner
580	ps.executeUpdate();
581
582	// emails
583
584	// Apaga os emails do contato na tabela phpgw_cc_connections
585	ps = con
586	.prepareStatement("DELETE FROM phpgw_cc_connections WHERE id_connection IN (SELECT id_connection FROM phpgw_cc_contact_conns WHERE id_contact = ? AND id_typeof_contact_connection = 1)");
587	ps.setLong(1, id_contact);
588	ps.executeUpdate();
589
590	// Apaga as referencias de emails do contato na tabela
591	// phpgw_cc_contact_conns
592	ps = con
593	.prepareStatement("DELETE FROM phpgw_cc_contact_conns WHERE id_contact = ? AND id_typeof_contact_connection = 1");
594	ps.setLong(1, id_contact);
595	ps.executeUpdate();
596
597	if (!emails.isEmpty()) {
598
599	boolean type_mail = true;
600
601	for (int i = 0, l = emails.size(); i < l; i++) {
602
603	email = emails.get(i);
604
605	if ((FIELD_EMAIL_1_ADDRESS).equals(email.getEmailType())) {
606	type = TYPE_EMAIL_1_ADDRESS;
607	type_mail = true;
608	} else if ((FIELD_EMAIL_2_ADDRESS).equals(email
609	.getEmailType())) {
610	type = TYPE_EMAIL_2_ADDRESS;
611	type_mail = false;
612	} else {
613	continue; // no save unknown property
614	}
615
616	String emailValue = email.getPropertyValueAsString();
617	emailValue = truncate(emailValue, SQL_EMAIL_DIM);
618
619	if (emailValue != null && emailValue.length() != 0) {
620
621	// Verifica o id maximo da tabela phpgw_cc_connections
622	ps = con.prepareStatement(SQL_ID_MAX_CONNECTIONS);
623	rs = ps.executeQuery();
624
625	if (rs.next()) {
626	long id = rs.getInt(1);
627	id_connection = (id == 0) ? 1 : ++id;
628	}
629
630	// Insere os emails do contato na tabela
631	// phpgw_cc_connections
632	ps = con.prepareStatement(SQL_INSERT_INTO_CONTACT_ITEM);
633	ps.setLong(1, id_connection);
634	ps.setString(2, type);
635	ps.setString(3, emailValue);
636	ps.setBoolean(4, type_mail);
637	ps.executeUpdate();
638
639	// Insere as referencias de emails do contato na tabela
640	// phpgw_cc_connections
641	ps = con
642	.prepareStatement(SQL_INSERT_INTO_FNBL_CONNECTIONS);
643	ps.setLong(1, id_contact);
644	ps.setLong(2, id_connection);
645	ps.setLong(3, 1);
646	ps.executeUpdate();
647	}
648	}
649	}
650
651	// phones
652
653	// Apaga os telefones do contato na tabela phpgw_cc_connections
654	ps = con
655	.prepareStatement("DELETE FROM phpgw_cc_connections WHERE id_connection IN (SELECT id_connection FROM phpgw_cc_contact_conns WHERE id_contact = ? AND id_typeof_contact_connection = 2)");
656	ps.setLong(1, id_contact);
657	ps.executeUpdate();
658
659	// Apaga as referencias de telefones do contato na tabela
660	// phpgw_cc_contact_conns
661	ps = con
662	.prepareStatement("DELETE FROM phpgw_cc_contact_conns WHERE id_contact = ? AND id_typeof_contact_connection = 2");
663	ps.setLong(1, id_contact);
664	ps.executeUpdate();
665
666	if (!phones.isEmpty()) {
667
668	boolean type_fone = true; // Seta o primeiro telefone como
669	// principal no expresso
670
671	for (int i = 0, l = phones.size(); i < l; i++) {
672
673	phone = phones.get(i);
674	phoneType = phone.getPhoneType();
675
676	if ((FIELD_PRIMARY_TELEPHONE_NUMBER).equals(phoneType)) {
677	type = TYPE_PRIMARY_TELEPHONE_NUMBER;
678	} else if ((FIELD_HOME_TELEPHONE_NUMBER).equals(phoneType)) {
679	type = TYPE_HOME_TELEPHONE_NUMBER;
680	} else if ((FIELD_MOBILE_TELEPHONE_NUMBER)
681	.equals(phoneType)) {
682	type = TYPE_MOBILE_TELEPHONE_NUMBER;
683	} else if ((FIELD_BUSINESS_TELEPHONE_NUMBER)
684	.equals(phoneType)) {
685	type = TYPE_BUSINESS_TELEPHONE_NUMBER;
686	} else if ((FIELD_BUSINESS_FAX_NUMBER).equals(phoneType)) {
687	type = TYPE_BUSINESS_FAX_NUMBER;
688	} else if ((FIELD_PAGER_NUMBER).equals(phoneType)) {
689	type = TYPE_PAGER_NUMBER;
690	} else {
691	continue; // Unknown property: saves nothing
692	}
693
694	String phoneValue = phone.getPropertyValueAsString();
695	phoneValue = truncate(phoneValue, SQL_EMAIL_DIM);
696
697	if (phoneValue != null && phoneValue.length() != 0) {
698
699	// Verifica o id maximo da tabela phpgw_cc_connections
700	ps = con.prepareStatement(SQL_ID_MAX_CONNECTIONS);
701	rs = ps.executeQuery();
702
703	if (rs.next()) {
704	long id = rs.getInt(1);
705	id_connection = (id == 0) ? 1 : ++id;
706	}
707
708	// Insere os telefones do contato na tabela
709	// phpgw_cc_connections
710	ps = con.prepareStatement(SQL_INSERT_INTO_CONTACT_ITEM);
711	ps.setLong(1, id_connection);
712	ps.setString(2, type);
713	ps.setString(3, phoneValue);
714	ps.setBoolean(4, type_fone);
715	ps.executeUpdate();
716
717	type_fone = false; // os demais telefones serao setados
718	// como nao padrao
719
720	// Insere as referencias de telefones do contato na
721	// tabela phpgw_cc_connections
722	ps = con
723	.prepareStatement(SQL_INSERT_INTO_FNBL_CONNECTIONS);
724	ps.setLong(1, id_contact);
725	ps.setLong(2, id_connection);
726	ps.setLong(3, 2);
727	ps.executeUpdate();
728	}
729	}
730	}
731
732	con.commit();
733	con.setAutoCommit(true);
734
735	} catch (Exception e) {
736	throw new PIMDBAccessException("\n=> Error updating contact.\n", e);
737	} finally {
738	DBTools.close(con, ps, rs);
739	}
740
741	log.info("\n\n=> Update item with ID " + id_contact + "\n");
742	log.info("\n\n=> PIMContactDAO updateItem end\n");
743
744	return Long.toString(id_contact);
745	}
746
747	/**
748	* Removes the contact with given UID and sets its last_update field,
749	* provided it has the same userId as this DAO. The deletion is soft
750	* (reversible).
751	*
752	* @param uid
753	* corresponds to the id field in the phpgw_cc_contact table
754	* @throws PIMDBAccessException
755	*/
756	public void removeItem(String uid) throws PIMDBAccessException {
757
758	log.info("\n\n=> DAO start removeItem " + uid + "\n");
759
760	Connection con = null;
761	PreparedStatement ps = null;
762
763	try {
764	// Looks up the data source when the first connection is created
765	con = getDataSource().getConnection();
766	con.setAutoCommit(false);
767
768	ps = con
769	.prepareStatement("DELETE FROM phpgw_cc_addresses WHERE id_address IN (SELECT id_address FROM phpgw_cc_contact_addrs WHERE id_contact = ?)");
770	ps.setLong(1, Long.parseLong(uid));
771	ps.executeUpdate();
772
773	ps = con
774	.prepareStatement("DELETE FROM phpgw_cc_contact_addrs WHERE id_contact = ?");
775	ps.setLong(1, Long.parseLong(uid));
776	ps.executeUpdate();
777
778	ps = con
779	.prepareStatement("DELETE FROM phpgw_cc_connections WHERE id_connection IN (SELECT id_connection FROM phpgw_cc_contact_conns WHERE id_contact = ?)");
780	ps.setLong(1, Long.parseLong(uid));
781	ps.executeUpdate();
782
783	ps = con
784	.prepareStatement("DELETE FROM phpgw_cc_contact_conns WHERE id_contact = ?");
785	ps.setLong(1, Long.parseLong(uid));
786	ps.executeUpdate();
787
788	ps = con
789	.prepareStatement("DELETE FROM phpgw_cc_contact WHERE id_contact = ?");
790	ps.setLong(1, Long.parseLong(uid));
791	ps.executeUpdate();
792
793	con.commit();
794	con.setAutoCommit(true);
795
796	} catch (Exception e) {
797
798	e.printStackTrace();
799	throw new PIMDBAccessException("\nError deleting contact.\n", e);
800
801	} finally {
802	DBTools.close(con, ps, null);
803	}
804	}
805
806	/**
807	* Removes a contact, provided it has the same userId as this DAO. The
808	* deletion is soft (reversible).
809	*
810	* @param contact
811	* whence the UID and the last update Date are extracted
812	* @throws PIMDBAccessException
813	*/
814	public void removeItem(ContactWrapper contact) throws PIMDBAccessException {
815
816	removeItem(contact.getId());
817	}
818
819	/**
820	* Removes all contacts, provided it has the same userId as this DAO. The
821	* deletion is soft (reversible).
822	*
823	* @throws PIMDBAccessException
824	*/
825	public void removeAllItems() throws PIMDBAccessException {
826
827	log.info("\n\n =>DAO start removeAllItems\n");
828
829	Connection con = null;
830	PreparedStatement ps = null;
831
832	try {
833	// Looks up the data source when the first connection is created
834	con = getDataSource().getConnection();
835	con.setAutoCommit(false);
836
837	ps = con
838	.prepareStatement("DELETE FROM phpgw_cc_addresses WHERE id_address IN (SELECT id_address FROM phpgw_cc_contact_addrs WHERE id_contact IN(SELECT id_contact FROM phpgw_cc_contact WHERE id_owner = ?))");
839	ps.setLong(1, Long.parseLong(userId));
840	ps.executeUpdate();
841
842	ps = con
843	.prepareStatement("DELETE FROM phpgw_cc_contact_addrs WHERE id_contact IN(SELECT id_contact FROM phpgw_cc_contact WHERE id_owner = ?)");
844	ps.setLong(1, Long.parseLong(userId));
845	ps.executeUpdate();
846
847	ps = con
848	.prepareStatement("DELETE FROM phpgw_cc_connections WHERE id_connection IN (SELECT id_connection FROM phpgw_cc_contact_conns WHERE id_contact IN(SELECT id_contact FROM phpgw_cc_contact WHERE id_owner = ?))");
849	ps.setLong(1, Long.parseLong(userId));
850	ps.executeUpdate();
851
852	ps = con
853	.prepareStatement("DELETE FROM phpgw_cc_contact_conns WHERE id_contact IN(SELECT id_contact FROM phpgw_cc_contact WHERE id_owner = ?)");
854	ps.setLong(1, Long.parseLong(userId));
855	ps.executeUpdate();
856
857	ps = con
858	.prepareStatement("DELETE FROM phpgw_cc_contact WHERE id_owner = ?");
859	ps.setLong(1, Long.parseLong(userId));
860	ps.executeUpdate();
861
862	con.commit();
863	con.setAutoCommit(true);
864
865	} catch (Exception e) {
866
867	throw new PIMDBAccessException("\n=> Error deleting contacts.\n", e);
868
869	} finally {
870	DBTools.close(con, ps, null);
871	}
872	}
873
874	/**
875	* Retrieves the UID list of all contacts belonging to the user.
876	*
877	* @throws PIMDBAccessException
878	* @return a List of UIDs (as String objects)
879	*/
880	public List getAllItems() throws PIMDBAccessException {
881
882	log.info("\n\n=> PIMContactDAO getAllItems begin\n");
883
884	Connection con = null;
885	PreparedStatement ps = null;
886	List contacts = new ArrayList();
887	ResultSet rs = null;
888
889	try {
890	// Looks up the data source when the first connection is created
891	con = getDataSource().getConnection();
892
893	ps = con.prepareStatement(SQL_GET_CONTACT_ID_LIST_BY_USER
894	+ SQL_ORDER_BY_ID);
895	ps.setLong(1, Long.parseLong(userId));
896
897	rs = ps.executeQuery();
898
899	while (rs.next()) {
900	contacts.add(Long.toString(rs.getLong(1))); // It's the first
901	// and only column
902	}
903
904	} catch (Exception e) {
905	e.printStackTrace();
906	throw new PIMDBAccessException("\n=> Error listing contacts.\n", e);
907	} finally {
908	DBTools.close(con, ps, rs);
909	}
910
911	log.info("\n\n=> PIMContactDAO getAllItems end\n");
912
913	return contacts;
914	}
915
916	/**
917	* Gets the contact with given UID, provided it has the same userId as this
918	* DAO.
919	*
920	* @param uid
921	* corresponds to the id field in the fnbl_pim_contact table
922	* @throws PIMDBAccessException
923	* @return the contact as a ContactWrapper object.
924	*/
925	public ContactWrapper getItem(String uid) throws PIMDBAccessException {
926
927	log.info("\n\n=> DAO start getItem " + uid + "\n");
928
929	Connection con = null;
930	PreparedStatement ps = null;
931	ResultSet rs = null;
932	ContactWrapper c;
933
934	try {
935	// Looks up the data source when the first connection is created
936	con = getDataSource().getConnection();
937
938	ps = con.prepareStatement(SQL_GET_CONTACT_BY_ID_USER);
939	ps.setLong(1, Long.parseLong(uid));
940	ps.setLong(2, Long.parseLong(userId));
941	rs = ps.executeQuery();
942	c = createContact(uid, rs);
943
944	ps = con.prepareStatement(SQL_GET_CONTACT_ITEM_BY_ID);
945	ps.setLong(1, Long.parseLong(uid));
946	rs = ps.executeQuery();
947
948	try {
949	addPIMContactItems(c, rs);
950	} catch (SQLException sqle) {
951	throw new SQLException(
952	"\nError while adding extra PIM contact "
953	+ "information.\n" + sqle.getMessage(), sqle
954	.getSQLState());
955	}
956
957	} catch (Exception e) {
958	e.printStackTrace();
959	throw new PIMDBAccessException("\n=> Error seeking contact.\n", e);
960	} finally {
961	DBTools.close(con, ps, rs);
962	}
963
964	return c;
965	}
966
967	/**
968	* Retrieves the UID list of the contacts considered to be "twins" of a
969	* given contact.
970	*
971	* @param c
972	* the Contact object representing the contact whose twins need
973	* be found. In the present implementation, only the following
974	* data matter:
975	* <UL>
976	* <LI>first name
977	* <LI>last name
978	* </UL>
979	* @throws PIMDBAccessException
980	* @return a List of UIDs (as String objects) that may be empty but not null
981	*/
982	public List getTwinItems(Contact c) throws PIMDBAccessException {
983
984	log.info("\n\n=> PIMContactDAO getTwinItems begin\n");
985
986	List twins = new ArrayList();
987	Connection con = null;
988	PreparedStatement ps = null;
989	ResultSet rs = null;
990
991	try {
992
993	String firstName = c.getName().getFirstName()
994	.getPropertyValueAsString();
995	String lastName = c.getName().getLastName()
996	.getPropertyValueAsString();
997
998	// Looks up the data source when the first connection is created
999	con = getDataSource().getConnection();
1000
1001	if (firstName == null \|\| ("null".equals(firstName))) {
1002	firstName = "";
1003	}
1004	if (lastName == null \|\| ("null".equals(lastName))) {
1005	lastName = "";
1006	}
1007
1008	String fnSearch = (firstName.length() == 0 ? "<N/A>" : firstName
1009	.toLowerCase());
1010	String lnSearch = (lastName.length() == 0 ? "<N/A>" : lastName
1011	.toLowerCase());
1012	StringBuilder sb = new StringBuilder(100);
1013
1014	sb.append("\n\nLooking for items having: ").append(
1015	"\n> first name : '").append(fnSearch).append('\'')
1016	.append("\n> last name : '").append(lnSearch).append(
1017	'\'').append("\n");
1018
1019	log.info(sb.toString());
1020
1021	ps = con.prepareStatement(SQL_GET_CONTACT_TWIN_ID_LIST
1022	+ SQL_ORDER_BY_ID);
1023	ps.setLong(1, Long.parseLong(userId));
1024	ps.setString(2, firstName.toLowerCase());
1025	ps.setString(3, firstName.toLowerCase());
1026	ps.setString(4, lastName.toLowerCase());
1027	ps.setString(5, lastName.toLowerCase());
1028	rs = ps.executeQuery();
1029
1030	long twinId;
1031
1032	while (rs.next()) {
1033
1034	twinId = rs.getLong(1); // The id is the first and only column
1035	log.info("\n\n=> Twin found: " + twinId + "\n");
1036	twins.add(Long.toString(twinId));
1037	}
1038
1039	} catch (Exception e) {
1040	throw new PIMDBAccessException("\n=> Error retrieving twin.\n", e);
1041	} finally {
1042	DBTools.close(con, ps, rs);
1043	}
1044
1045	log.info("\n\n=> PIMContactDAO getTwinItems end\n");
1046
1047	return twins;
1048	}
1049
1050	/**
1051	* Retrieves the state of the given item, provided it's been modified after
1052	* a certain moment.
1053	*
1054	* @param uid
1055	* the UID of the item to be checked (as a String object)
1056	* @param since
1057	* the Timestamp that the item's lastUpdate field is checked
1058	* against: if the item has been modified before that moment, an
1059	* "unchanged" state marker is returned
1060	* @throws PIMDBAccessException
1061	* @return a char identifying either one of the 3 standard states ("new",
1062	* "deleted", "updated") or the special "unchanged" status, all of
1063	* them as defined in com.funambol.pim.util.Def
1064	*/
1065	public char getItemState(String uid, Timestamp since)
1066	throws PIMDBAccessException {
1067
1068	log.info("\n\n=> DAO start getItemState\n");
1069
1070	Connection con = null;
1071	PreparedStatement ps = null;
1072	ResultSet rs = null;
1073	char status;
1074
1075	try {
1076	// Looks up the data source when the first connection is created
1077	con = getDataSource().getConnection();
1078
1079	ps = con.prepareStatement(SQL_GET_STATUS_BY_ID_USER_TIME);
1080	ps.setLong(1, Long.parseLong(uid));
1081	ps.setLong(2, Long.parseLong(userId));
1082	ps.setLong(3, since.getTime());
1083	rs = ps.executeQuery();
1084
1085	if (!rs.next()) {
1086
1087	status = Def.PIM_STATE_UNCHANGED;
1088
1089	log.info("\n\n=> Item " + uid + "'s status wasn't retrieved "
1090	+ "because the item hasn't been modified since "
1091	+ since + "\n");
1092
1093	} else {
1094
1095	status = rs.getString(1).charAt(0);
1096
1097	log.info("\n\n=> Item " + uid + " has status " + status + "\n");
1098
1099	}
1100
1101	} catch (Exception e) {
1102	throw new PIMDBAccessException(
1103	"\n=> Error retrieving item state.\n", e);
1104	} finally {
1105	DBTools.close(con, ps, rs);
1106	}
1107
1108	return status;
1109	}
1110
1111	// ---------------------------------------------------------- Private
1112	// methods
1113
1114	/**
1115	* Creates a ContactWrapper object from a ResultSet. Only the basic data are
1116	* set.
1117	*
1118	* @param wrapperId
1119	* the UID of the wrapper object to be returned
1120	* @param rs
1121	* the result of the execution of a proper SQL SELECT statement
1122	* on the phpgw_cc_contact table, with the cursor before its
1123	* first row
1124	* @return a newly created ContactWrapper initialized with the fields in the
1125	* result set
1126	* @throws java.sql.SQLException
1127	* @throws NotFoundException
1128	*/
1129	private static ContactWrapper createContact(String wrapperId, ResultSet rs)
1130	throws SQLException, NotFoundException {
1131
1132	if (!rs.next()) {
1133	throw new NotFoundException("\n\n=> No contact found.\n");
1134	}
1135
1136	ResultSetMetaData rsmd = rs.getMetaData();
1137	ContactWrapper cw = null;
1138
1139	String column = null;
1140	String uid = null;
1141	String userId = null;
1142
1143	uid = String.valueOf(rs.getLong(SQL_FIELD_ID));
1144	userId = rs.getString(SQL_FIELD_USERID);
1145
1146	Contact c = new Contact();
1147	cw = new ContactWrapper(wrapperId, userId, c);
1148
1149	int columnCount = rsmd.getColumnCount();
1150
1151	for (int i = 1; i <= columnCount; ++i) {
1152
1153	column = rsmd.getColumnName(i);
1154
1155	// General
1156	if (SQL_FIELD_ID.equalsIgnoreCase(column)) {
1157	// Does nothing: field already set at construction time
1158	} else if (SQL_FIELD_LAST_UPDATE.equalsIgnoreCase(column)) {
1159	cw.setLastUpdate(new Timestamp(rs.getLong(i)));
1160	} else if (SQL_FIELD_USERID.equalsIgnoreCase(column)) {
1161	// Does nothing: field already set at construction time
1162	} else if (SQL_FIELD_STATUS.equalsIgnoreCase(column)) {
1163	cw.setStatus(rs.getString(i).charAt(0));
1164	} else if (SQL_FIELD_FIRST_NAME.equalsIgnoreCase(column)) {
1165	c.getName().getFirstName().setPropertyValue(rs.getString(i));
1166	} else if (SQL_FIELD_LAST_NAME.equalsIgnoreCase(column)) {
1167	c.getName().getLastName().setPropertyValue(rs.getString(i));
1168	} else if (SQL_FIELD_BIRTHDAY.equalsIgnoreCase(column)) {
1169	c.getPersonalDetail().setBirthday(rs.getString(i));
1170	} else if (SQL_FIELD_CATEGORY.equalsIgnoreCase(column)) {
1171	c.getCategories().setPropertyValue(rs.getString(i));
1172	} else {
1173	throw new SQLException("\n=> Unhandled column: " + column);
1174	}
1175	}
1176
1177	return cw;
1178	}
1179
1180	/**
1181	* Attaches extra information to a contact on the basis of a ResultSet.
1182	*
1183	* @param c
1184	* the contact still lacking extra information
1185	* @param rs
1186	* the result of the execution of a proper SQL SELECT statement
1187	* on the phpgw_cc_connections and phpgw_cc_contact_conns table,
1188	* with the cursor before its first row
1189	* @return the ContactWrapper object with extra information attached
1190	* @throws java.sql.SQLException
1191	*/
1192	private static ContactWrapper addPIMContactItems(ContactWrapper cw,
1193	ResultSet rs) throws SQLException {
1194
1195	ResultSetMetaData rsmd = rs.getMetaData();
1196	Contact c = cw.getContact();
1197
1198	String type = null;
1199	String value = null;
1200	String name = null;
1201	String column = null;
1202
1203	int columnCount = 0;
1204
1205	Phone phone;
1206	Email email;
1207
1208	while (rs.next()) {
1209
1210	columnCount = rsmd.getColumnCount();
1211
1212	for (int i = 1; i <= columnCount; ++i) {
1213
1214	column = rsmd.getColumnName(i);
1215
1216	if (SQL_FIELD_ITEM_NAME.equalsIgnoreCase(column)) {
1217	name = rs.getString(i);
1218	} else if (SQL_FIELD_ITEM_VALUE.equalsIgnoreCase(column)) {
1219	value = rs.getString(i);
1220	} else if (SQL_FIELD_ITEM_TYPE.equalsIgnoreCase(column)) {
1221	type = rs.getString(i);
1222	} else {
1223	throw new SQLException("\n=> Unhandled column: " + column);
1224	}
1225	}
1226
1227	if (type.equals("1")) { // email
1228
1229	if (TYPE_EMAIL_1_ADDRESS.equalsIgnoreCase(name)) {
1230	email = new Email();
1231	email.setEmailType(FIELD_EMAIL_1_ADDRESS);
1232	email.setPropertyValue(value);
1233	c.getPersonalDetail().addEmail(email);
1234	} else if (TYPE_EMAIL_2_ADDRESS.equalsIgnoreCase(name)) {
1235	email = new Email();
1236	email.setEmailType(FIELD_EMAIL_2_ADDRESS);
1237	email.setPropertyValue(value);
1238	c.getPersonalDetail().addEmail(email);
1239	}
1240	}
1241
1242	if (type.equals("2")) { // telefone
1243
1244	if (TYPE_PRIMARY_TELEPHONE_NUMBER.equalsIgnoreCase(name)) {
1245	phone = new Phone();
1246	phone.setPhoneType(FIELD_PRIMARY_TELEPHONE_NUMBER);
1247	phone.setPropertyValue(value);
1248	c.getBusinessDetail().addPhone(phone);
1249	} else if (TYPE_HOME_TELEPHONE_NUMBER.equalsIgnoreCase(name)) {
1250	phone = new Phone();
1251	phone.setPhoneType(FIELD_HOME_TELEPHONE_NUMBER);
1252	phone.setPropertyValue(value);
1253	c.getPersonalDetail().addPhone(phone);
1254	} else if (TYPE_MOBILE_TELEPHONE_NUMBER.equalsIgnoreCase(name)) {
1255	phone = new Phone();
1256	phone.setPhoneType(FIELD_MOBILE_TELEPHONE_NUMBER);
1257	phone.setPropertyValue(value);
1258	c.getPersonalDetail().addPhone(phone);
1259	} else if (TYPE_BUSINESS_TELEPHONE_NUMBER
1260	.equalsIgnoreCase(name)) {
1261	phone = new Phone();
1262	phone.setPhoneType(FIELD_BUSINESS_TELEPHONE_NUMBER);
1263	phone.setPropertyValue(value);
1264	c.getBusinessDetail().addPhone(phone);
1265	} else if (TYPE_BUSINESS_FAX_NUMBER.equalsIgnoreCase(name)) {
1266	phone = new Phone();
1267	phone.setPhoneType(FIELD_BUSINESS_FAX_NUMBER);
1268	phone.setPropertyValue(value);
1269	c.getBusinessDetail().addPhone(phone);
1270	} else if (TYPE_PAGER_NUMBER.equalsIgnoreCase(name)) {
1271	phone = new Phone();
1272	phone.setPhoneType(FIELD_PAGER_NUMBER);
1273	phone.setPropertyValue(value);
1274	c.getBusinessDetail().addPhone(phone);
1275	}
1276	}
1277	}
1278
1279	return cw;
1280	}
1281
1282	/**
1283	* Retrieves the UID list of the contacts belonging to the user filtered
1284	* according to the given time interval and status.
1285	*
1286	* @param since
1287	* the earliest allowed last-update Timestamp
1288	* @param to
1289	* the latest allowed last-update Timestamp
1290	* @param status
1291	* 'D' for deleted items, 'N' for new items, 'U' for updated
1292	* items
1293	* @throws PIMDBAccessException
1294	* @return a List of UIDs (as String objects)
1295	*/
1296	protected List getItemsHavingStatus(Timestamp since, Timestamp to,
1297	char status) throws PIMDBAccessException {
1298
1299	log.info("\n\n=> Seeking '" + status + "' items "
1300	+ "in time interval (" + since + "; " + to + ")\n");
1301
1302	Connection con = null;
1303	PreparedStatement ps = null;
1304	List contacts = new ArrayList();
1305	ResultSet rs = null;
1306
1307	try {
1308	// Looks up the data source when the first connection is created
1309	con = getDataSource().getConnection();
1310
1311	if (status == 'D') {
1312
1313	ps = con
1314	.prepareStatement("SELECT guid FROM fnbl_client_mapping WHERE sync_source = 'catalogo' AND guid NOT IN (SELECT id_contact FROM phpgw_cc_contact WHERE id_owner = ?) AND principal = ? ORDER BY guid");
1315	ps.setLong(1, Long.parseLong(userId));
1316	ps.setLong(2, this.principal.getId());
1317	rs = ps.executeQuery();
1318
1319	} else {
1320
1321	ps = con
1322	.prepareStatement(SQL_GET_FNBL_PIM_CONTACT_ID_LIST_BY_USER_TIME_STATUS
1323	+ SQL_ORDER_BY_ID);
1324	ps.setLong(1, Long.parseLong(userId));
1325	ps.setLong(2, since.getTime());
1326	ps.setLong(3, to.getTime());
1327	ps.setString(4, String.valueOf(status));
1328	rs = ps.executeQuery();
1329	}
1330
1331	while (rs.next()) {
1332	contacts.add(Long.toString(rs.getLong(1))); // It's the first
1333	// and only column
1334	log.info("\n\n=> Item found " + rs.getLong(1) + "\n");
1335	}
1336
1337	} catch (Exception e) {
1338	e.printStackTrace();
1339	throw new PIMDBAccessException("\n=> Error listing contacts.\n", e);
1340	} finally {
1341	DBTools.close(con, ps, rs);
1342	}
1343
1344	return contacts;
1345	}
1346	}

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

Download in other formats:

Original Format