Context Navigation

PIMContactDAO.java @ 1545

Revision 1545, 54.5 KB checked in by wmerlotto, 15 years ago (diff)
Ticket #693 - Incrementado a sincronizacao de contatos, integracao com maven e codigo de autenticacao no LDAP.

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.Country;
35	import br.com.prognus.psync.util.Def;
36	import br.com.prognus.psync.util.TypeAddress;
37
38	import com.funambol.common.pim.common.Property;
39	import com.funambol.common.pim.contact.Address;
40	import com.funambol.common.pim.contact.BusinessDetail;
41	import com.funambol.common.pim.contact.Contact;
42	import com.funambol.common.pim.contact.Email;
43	import com.funambol.common.pim.contact.Name;
44	import com.funambol.common.pim.contact.Note;
45	import com.funambol.common.pim.contact.PersonalDetail;
46	import com.funambol.common.pim.contact.Phone;
47	import com.funambol.common.pim.contact.Photo;
48	import com.funambol.framework.security.Sync4jPrincipal;
49	import com.funambol.framework.server.store.NotFoundException;
50	import com.funambol.framework.tools.DBTools;
51
52	public class PIMContactDAO extends PIMEntityDAO {
53
54	// --------------------------------------------------------------- Constants
55
56	private static final String SQL_ORDER_BY_ID = "ORDER BY id_contact";
57
58	private static final String SQL_GET_CONTACT_ID_LIST = "SELECT id_contact FROM phpgw_cc_contact ";
59
60	private static final String SQL_GET_CONTACT_ID_LIST_BY_USER = SQL_GET_CONTACT_ID_LIST
61	+ "WHERE id_owner = ?";
62
63	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, photo, notes, alias FROM phpgw_cc_contact WHERE id_contact = ? AND id_owner = ? LIMIT 1";
64
65	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 = ?";
66
67	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 ";
68
69	private static final String SQL_GET_FNBL_PIM_CONTACT_ID_LIST_BY_USER_TIME_STATUS = SQL_GET_CONTACT_ID_LIST
70	+ "WHERE id_owner = ? AND last_update > ? AND last_update < ? AND last_status = ? ";
71
72	private static final String SQL_GET_CONTACT_TWIN_ID_LIST = "SELECT id_contact "
73	+ "FROM phpgw_cc_contact "
74	+ "WHERE (id_owner = ?) "
75	+ "AND ((given_names is null AND (?) = '') "
76	+ "OR (given_names = ?)) "
77	+ "AND ((family_names is null AND (?) = '') "
78	+ "OR (family_names = ?)) ";
79
80	private static final String SQL_INSERT_INTO_FNBL_PIM_CONTACT = "INSERT INTO phpgw_cc_contact "
81	+ "(id_contact, id_owner, id_status, given_names, family_names, names_ordered, birthdate, category, photo, notes, alias, last_update, last_status ) "
82	+ "VALUES " + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
83
84	private static final String SQL_INSERT_INTO_CONTACT_ITEM = "INSERT INTO phpgw_cc_connections "
85	+ "(id_connection, connection_name, connection_value, connection_is_default) "
86	+ "VALUES (?, ?, ?, ?) ";
87
88	private static final String SQL_ID_MAX_CONNECTIONS = "select max(id_connection) from phpgw_cc_connections";
89
90	private static final String SQL_INSERT_INTO_FNBL_CONNECTIONS = "INSERT INTO phpgw_cc_contact_conns (id_contact, id_connection, id_typeof_contact_connection) VALUES (?, ?, ?)";
91
92	private static final String SQL_UPDATE_FNBL_PIM_CONTACT_BEGIN = "UPDATE phpgw_cc_contact SET ";
93
94	private static final String SQL_UPDATE_FNBL_PIM_CONTACT_END = " WHERE id_contact = ? AND id_owner = ? ";
95
96
97	private static final String SQL_GET_CONTACT_ADDRESS_ITEM = " select ad.complement, ad.address1, ad.postal_code, ca.id_typeof_contact_address, city_name, state_name, cy.id_country from phpgw_cc_addresses AS ad JOIN phpgw_cc_contact_addrs AS ca ON ad.id_address=ca.id_address, phpgw_cc_city AS cy JOIN phpgw_cc_state AS st ON cy.id_state=st.id_state where cy.id_city=ad.id_city and ad.id_address in (select min(ca.id_address) from phpgw_cc_contact AS cc JOIN phpgw_cc_contact_addrs AS ca ON cc.id_contact=ca.id_contact JOIN phpgw_cc_addresses AS ad ON ca.id_address=ad.id_address where cc.id_contact = ? AND cc.id_owner = ? and ca.id_typeof_contact_address = ? )";
98
99	private static final String SQL_GET_STATE_ITEM = "SELECT id_state, id_country, state_name, state_symbol FROM phpgw_cc_state ";
100
101	private static final String SQL_GET_CITY_ITEM = "SELECT id_city, id_state, id_country, city_timezone, city_geo_location, city_name FROM phpgw_cc_city ";
102
103	private static final String SQL_INSERT_STATE = "INSERT INTO phpgw_cc_state(id_state, id_country, state_name, state_symbol) VALUES (?, ?, ?, ?)";
104
105	private static final String SQL_INSERT_CITY = "INSERT INTO phpgw_cc_city(id_city, id_state, id_country, city_timezone, city_geo_location, city_name) VALUES (?, ?, ?, ?, ?, ?)";
106
107	private static final String SQL_INSERT_ADDRESS = "INSERT INTO phpgw_cc_addresses(id_address, id_city, id_state, id_country, address1, complement, postal_code, address_is_default) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
108
109	private static final String SQL_INSERT_CONTACT_ADDRESS = "INSERT INTO phpgw_cc_contact_addrs(id_contact, id_address, id_typeof_contact_address) VALUES (?, ?, ?)";
110
111	private static final int SQL_ADDRESS_DIM = 60;
112
113	private static final String SQL_ID_MAX_ADDRESS = "select max(id_address) FROM phpgw_cc_addresses";
114
115	private static final String SQL_ID_MAX_STATE = "select max(id_state) FROM phpgw_cc_state";
116
117	private static final String SQL_ID_MAX_CITY = "select max(id_city) FROM phpgw_cc_city";
118
119	private static final String SQL_EQUALS_QUESTIONMARK = " = ?";
120
121	private static final String SQL_EQUALS_QUESTIONMARK_COMMA = " = ?, ";
122
123	protected static final String SQL_FIELD_ID = "id_contact";
124
125	protected static final String SQL_FIELD_USERID = "id_owner";
126
127	protected static final String SQL_FIELD_LAST_UPDATE = "last_update";
128
129	protected static final String SQL_FIELD_STATUS = "last_status";
130
131	protected static final String SQL_FIELD_FIRST_NAME = "given_names";
132
133	protected static final String SQL_FIELD_LAST_NAME = "family_names";
134
135	protected static final String SQL_FIELD_NAME = "names_ordered";
136
137	protected static final String SQL_FIELD_NICK = "alias";
138
139	protected static final String SQL_FIELD_BIRTHDAY = "birthdate";
140
141	protected static final String SQL_FIELD_CATEGORY = "category";
142
143	protected static final String SQL_FIELD_PHOTO = "photo";
144
145	protected static final String SQL_FIELD_NOTES = "notes";
146
147	protected static final int SQL_FIRSTNAME_DIM = 49;
148
149	protected static final int SQL_LASTNAME_DIM = 50;
150
151	protected static final int SQL_EMAIL_DIM = 50;
152
153	protected static final int SQL_PHONE_DIM = 50;
154
155	protected static final int SQL_CATEGORY_DIM = 20;
156
157	protected static final int SQL_NICK_DIM = 30;
158
159	protected static final String TYPE_EMAIL_1_ADDRESS = "Principal";
160
161	protected static final String FIELD_EMAIL_1_ADDRESS = "Email1Address";
162
163	protected static final String TYPE_EMAIL_2_ADDRESS = "Alternativo";
164
165	protected static final String FIELD_EMAIL_2_ADDRESS = "OtherEmail2Address";
166
167	protected static final String TYPE_PRIMARY_TELEPHONE_NUMBER = "Principal";
168
169	protected static final String FIELD_PRIMARY_TELEPHONE_NUMBER = "PrimaryTelephoneNumber";
170
171	protected static final String TYPE_HOME_TELEPHONE_NUMBER = "Residencial";
172
173	protected static final String FIELD_HOME_TELEPHONE_NUMBER = "HomeTelephoneNumber";
174
175	protected static final String TYPE_MOBILE_TELEPHONE_NUMBER = "Celular";
176
177	protected static final String FIELD_MOBILE_TELEPHONE_NUMBER = "MobileTelephoneNumber";
178
179	protected static final String TYPE_BUSINESS_TELEPHONE_NUMBER = "Comercial";
180
181	protected static final String FIELD_BUSINESS_TELEPHONE_NUMBER = "BusinessTelephoneNumber";
182
183	protected static final String TYPE_BUSINESS_FAX_NUMBER = "Fax";
184
185	protected static final String FIELD_BUSINESS_FAX_NUMBER = "BusinessFaxNumber";
186
187	protected static final String TYPE_PAGER_NUMBER = "Pager";
188
189	protected static final String FIELD_PAGER_NUMBER = "PagerNumber";
190
191	protected static final String SQL_FIELD_ITEM_NAME = "connection_name";
192
193	protected static final String SQL_FIELD_ITEM_VALUE = "connection_value";
194
195	protected static final String SQL_FIELD_ITEM_TYPE = "id_typeof_contact_connection";
196
197	protected static final String SQL_FIELD_TYPEOF = "id_typeof_contact_address";
198
199	protected static final String SQL_FIELD_COMPLEMENT = "complement";
200
201	protected static final String SQL_FIELD_ADDRESS1 = "address1";
202
203	protected static final String SQL_FIELD_POSTALCODE = "postal_code";
204
205	protected static final String SQL_FIELD_CITY_NAME = "city_name";
206
207	protected static final String SQL_FIELD_STATE_NAME = "state_name";
208
209	protected static final String SQL_FIELD_COUNTRY_ID = "id_country";
210
211	protected static final String SQL_FIELD_STATE_ID = "id_state";
212
213	protected static final String SQL_FIELD_CITY_ID = "id_city";
214
215	private Sync4jPrincipal principal = null;
216
217	// -------------------------------------------------------------
218	// Constructors
219
220	/**
221	* @see PIMEntityDAO#PIMEntityDAO(String, String)
222	*/
223	public PIMContactDAO(String jndiDataSourceName, Sync4jPrincipal principal) {
224
225	super(jndiDataSourceName, principal.getUsername());
226
227	log.info("\n\n=> Created new PIMContactDAO for data source "
228	+ jndiDataSourceName + " and user ID LDAP " + userId
229	+ " user login " + principal.getUsername() + "\n");
230
231	this.principal = principal;
232	}
233
234	// ----------------------------------------------------------- Public
235	// methods
236
237	/**
238	* Adds a contact. If necessary, a new ID is generated and set in the
239	* ContactWrapper.
240	*
241	* @param c
242	* as a ContactWrapper object, usually without an ID set.
243	* @throws PIMDBAccessException
244	* @throws SQLException
245	*
246	* @see ContactWrapper
247	*/
248	@SuppressWarnings("unchecked")
249	public void addItem(ContactWrapper cw) throws PIMDBAccessException {
250
251	log.info("\n\n=> PIMContactDAO addItem begin\n");
252
253	Connection con = null;
254	PreparedStatement ps = null;
255	ResultSet rs = null;
256
257	long id_contact = 0;
258	long id_connection = 0;
259
260	PersonalDetail personalDetail = null;
261	BusinessDetail businessDetail = null;
262
263	Name name = null;
264	Phone phone = null;
265	Email email = null;
266
267	List<Email> emails = new ArrayList();
268	List<Phone> phones = new ArrayList();
269
270	String phoneType = null;
271	String firstName = null;
272	String lastName = null;
273	String birthday = null;
274	String type = null;
275	String category = null;
276
277	StringBuffer fullName = null;
278	Date anniversary = null;
279	byte[] imagePhoto = null;
280	String note = null;
281	String alias = null;
282
283	try {
284
285	con = getDataSource().getConnection();
286	con.setAutoCommit(false);
287
288	Contact c = cw.getContact();
289	Timestamp lastUpdate = cw.getLastUpdate();
290
291	try {
292	List<Note> notes = c.getNotes();
293	int i = notes.size() - 1;
294	note = notes.get(i).getPropertyValueAsString();
295	} catch (Exception e) {
296	// log.error(e.getMessage(),e);
297	}
298	if(note==null) {
299	note="";
300	}
301
302	personalDetail = c.getPersonalDetail();
303	businessDetail = c.getBusinessDetail();
304	name = c.getName();
305
306	lastUpdate = (lastUpdate == null) ? new Timestamp(System
307	.currentTimeMillis()) : lastUpdate;
308
309	if (personalDetail != null) {
310	emails.addAll(personalDetail.getEmails());
311	phones.addAll(personalDetail.getPhones());
312	birthday = personalDetail.getBirthday();
313	category = stringFrom(c.getCategories());
314	imagePhoto = personalDetail.getPhotoObject().getImage();
315	}
316
317	if (businessDetail != null) {
318	emails.addAll(businessDetail.getEmails());
319	phones.addAll(businessDetail.getPhones());
320	}
321
322	if (name != null) {
323	firstName = stringFrom(name.getFirstName());
324	lastName = stringFrom(name.getLastName());
325	alias = stringFrom(name.getNickname());
326	}
327
328	// consulta o id maximo da tabela contatos
329	ps = con
330	.prepareStatement("select max(id_contact) from phpgw_cc_contact limit 1");
331	rs = ps.executeQuery();
332
333	if (rs.next()) {
334	long id = rs.getInt(1);
335	id_contact = (id == 0) ? 1 : ++id;
336	}
337
338	cw.setId(Long.toString(id_contact));
339
340	// Formata o nome completo do contato
341	fullName = new StringBuffer();
342
343	if (firstName != null && firstName.length() > 0)
344	fullName.append(truncate(firstName, SQL_FIRSTNAME_DIM));
345	if (lastName != null && lastName.length() > 0) {
346	if (firstName != null && firstName.length() > 0
347	&& lastName != null && lastName.length() > 0)
348	fullName.append(" " + truncate(lastName, SQL_LASTNAME_DIM));
349	else
350	fullName.append(truncate(lastName, SQL_LASTNAME_DIM));
351	}
352
353	// Formata a data de aniversario do contato
354	if ((birthday != null) && (!birthday.equals(""))) {
355	anniversary = new java.sql.Date((new SimpleDateFormat(
356	"yyyy-MM-dd").parse(birthday)).getTime());
357	}
358
359	// Prepara os dados para inserir o contato no banco de dados
360	ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT);
361	ps.setLong(1, id_contact); // id_contact
362	ps.setLong(2, Long.parseLong(userId)); // id_owner
363	ps.setLong(3, 1); // id_status
364	ps.setString(4, truncate(firstName, SQL_FIRSTNAME_DIM)); // given_names
365	ps.setString(5, truncate(lastName, SQL_LASTNAME_DIM)); // family_names
366	ps.setString(6, fullName.toString()); // names_ordered
367	ps.setDate(7, anniversary); // birthdate
368	ps.setString(8, truncate(category, SQL_CATEGORY_DIM)); // category
369	ps.setBytes(9, imagePhoto); // photo
370	ps.setString(10, note); // note
371	ps.setString(11, truncate(alias, SQL_NICK_DIM)); // alias
372	ps.setLong(12, lastUpdate.getTime()); // last_update
373	ps.setString(13, String.valueOf(Def.PIM_STATE_NEW)); // last_status
374	ps.executeUpdate();
375
376	// emails
377	if (!emails.isEmpty()) {
378
379	for (int i = 0, l = emails.size(); i < l; i++) {
380
381	email = emails.get(i);
382	Boolean type_email;
383
384	if ((FIELD_EMAIL_1_ADDRESS).equals(email.getEmailType())) {
385	type = TYPE_EMAIL_1_ADDRESS;
386	type_email = true;
387	} else if ((FIELD_EMAIL_2_ADDRESS).equals(email
388	.getEmailType())) {
389	type = TYPE_EMAIL_2_ADDRESS;
390	type_email = false;
391	} else {
392	continue; // Unknown property: saves nothing
393	}
394
395	String emailValue = email.getPropertyValueAsString();
396
397	if (emailValue != null && emailValue.length() != 0) {
398
399	if (emailValue.length() > SQL_EMAIL_DIM) {
400	emailValue = emailValue.substring(0, SQL_EMAIL_DIM);
401	}
402
403	// Verifica o id maximo da tabela phpgw_cc_connections
404	ps = con.prepareStatement(SQL_ID_MAX_CONNECTIONS);
405	rs = ps.executeQuery();
406
407	if (rs.next()) {
408	long id = rs.getInt(1);
409	id_connection = (id == 0) ? 1 : ++id;
410	}
411
412	// Insere os emails do contato na tabela
413	// phpgw_cc_connections
414	ps = con.prepareStatement(SQL_INSERT_INTO_CONTACT_ITEM);
415	ps.setLong(1, id_connection);
416	ps.setString(2, type);
417	ps.setString(3, emailValue);
418	ps.setBoolean(4, type_email);
419	ps.executeUpdate();
420
421	// Insere as referencias de emails do contato na tabela
422	// phpgw_cc_connections
423	ps = con
424	.prepareStatement(SQL_INSERT_INTO_FNBL_CONNECTIONS);
425	ps.setLong(1, id_contact);
426	ps.setLong(2, id_connection);
427	ps.setLong(3, 1);
428	ps.executeUpdate();
429	}
430
431	}
432	}
433
434	// phones
435	if (!phones.isEmpty()) {
436
437	Boolean type_phone = true;
438
439	for (int i = 0, l = phones.size(); i < l; i++) {
440
441	phone = phones.get(i);
442	phoneType = phone.getPhoneType();
443
444	if ((FIELD_PRIMARY_TELEPHONE_NUMBER).equals(phoneType)) {
445	type = TYPE_PRIMARY_TELEPHONE_NUMBER;
446	} else if ((FIELD_HOME_TELEPHONE_NUMBER).equals(phoneType)) {
447	type = TYPE_HOME_TELEPHONE_NUMBER;
448	} else if ((FIELD_MOBILE_TELEPHONE_NUMBER)
449	.equals(phoneType)) {
450	type = TYPE_MOBILE_TELEPHONE_NUMBER;
451	} else if ((FIELD_BUSINESS_TELEPHONE_NUMBER)
452	.equals(phoneType)) {
453	type = TYPE_BUSINESS_TELEPHONE_NUMBER;
454	} else if ((FIELD_BUSINESS_FAX_NUMBER).equals(phoneType)) {
455	type = TYPE_BUSINESS_FAX_NUMBER;
456	} else if ((FIELD_PAGER_NUMBER).equals(phoneType)) {
457	type = TYPE_PAGER_NUMBER;
458	} else {
459	continue; // Unknown property: saves nothing
460	}
461
462	String phoneValue = phone.getPropertyValueAsString();
463
464	if (phoneValue != null && phoneValue.length() != 0) {
465
466	if (phoneValue.length() > SQL_PHONE_DIM) {
467	phoneValue = phoneValue.substring(0, SQL_PHONE_DIM);
468	}
469
470	ps = con.prepareStatement(SQL_ID_MAX_CONNECTIONS);
471	rs = ps.executeQuery();
472
473	if (rs.next()) {
474	long id = rs.getInt(1);
475	id_connection = (id == 0) ? 1 : ++id;
476	}
477
478	// Insere os telefones do contato na tabela
479	// phpgw_cc_connections
480	ps = con.prepareStatement(SQL_INSERT_INTO_CONTACT_ITEM);
481	ps.setLong(1, id_connection);
482	ps.setString(2, type);
483	ps.setString(3, phoneValue);
484	ps.setBoolean(4, type_phone);
485	ps.executeUpdate();
486
487	type_phone = false; // os demais telefones serao setados
488	// como nao padrao
489
490	// Insere as referencias de telefones do contato na
491	// tabela phpgw_cc_connections
492	ps = con
493	.prepareStatement(SQL_INSERT_INTO_FNBL_CONNECTIONS);
494	ps.setLong(1, id_contact);
495	ps.setLong(2, id_connection);
496	ps.setLong(3, 2);
497	ps.executeUpdate();
498	}
499
500	}
501	}
502
503	con.commit();
504	con.setAutoCommit(true);
505
506	} catch (Exception e) {
507	throw new PIMDBAccessException("\n=> Error adding contact.\n", e);
508	} finally {
509	DBTools.close(con, ps, rs);
510	}
511
512	log.info("\n\n=> Added item with ID " + id_contact + "\n");
513	log.info("\n\n=> PIMContactDAO addItem end\n");
514	}
515
516	/**
517	* Updates a contact.
518	*
519	* @param c
520	* as a ContactWrapper object. If its last update time is null,
521	* then it's set to the current time.
522	* @return the UID of the contact
523	* @throws PIMDBAccessException
524	*
525	* @see ContactWrapper
526	*/
527	public String updateItem(ContactWrapper cw) throws PIMDBAccessException {
528
529	log.info("\n\n=> PIMContactDAO updateItem begin\n");
530
531	Connection con = null;
532	PreparedStatement ps = null;
533	ResultSet rs = null;
534
535	long id_connection = 0;
536	long id_contact = 0;
537
538	PersonalDetail personalDetail = null;
539	BusinessDetail businessDetail = null;
540
541	Name name = null;
542	Phone phone = null;
543	Email email = null;
544	String type = null;
545
546	List<Email> emails = new ArrayList();
547	List<Phone> phones = new ArrayList();
548
549	String phoneType = null;
550	StringBuffer queryUpdateFunPimContact = null;
551
552	String firstName = null;
553	String lastName = null;
554	StringBuffer fullName = null;
555	String birthday = null;
556	String category = null;
557	Date anniversary = null;
558	byte[] imagePhoto = null;
559	String note = null;
560	String alias = null;
561	Address persoAddress = null;
562	Address businAddress = null;
563
564	try {
565
566	Timestamp lastUpdate = (cw.getLastUpdate() == null) ? new Timestamp(
567	System.currentTimeMillis())
568	: cw.getLastUpdate();
569
570	con = getDataSource().getConnection();
571	con.setAutoCommit(false);
572
573	Contact c = cw.getContact();
574
575	try {
576	List<Note> notes = c.getNotes();
577	int i = notes.size() - 1;
578	note = notes.get(i).getPropertyValueAsString();
579	} catch (Exception e) {
580	// log.error(e.getMessage(),e);
581	}
582	if(note==null) {
583	note="";
584	}
585
586	personalDetail = c.getPersonalDetail();
587	businessDetail = c.getBusinessDetail();
588	name = c.getName();
589	id_contact = Long.parseLong(cw.getId());
590
591	if (personalDetail != null) {
592	emails.addAll(personalDetail.getEmails());
593	phones.addAll(personalDetail.getPhones());
594	birthday = personalDetail.getBirthday();
595	category = stringFrom(c.getCategories());
596	imagePhoto = personalDetail.getPhotoObject().getImage();
597	persoAddress = personalDetail.getAddress();
598	}
599
600	if (businessDetail != null) {
601	emails.addAll(businessDetail.getEmails());
602	phones.addAll(businessDetail.getPhones());
603	businAddress = businessDetail.getAddress();
604	}
605
606	if (name != null) {
607	firstName = stringFrom(name.getFirstName());
608	lastName = stringFrom(name.getLastName());
609	alias = stringFrom(name.getNickname());
610	}
611
612	if (firstName != null && firstName.length() > SQL_FIRSTNAME_DIM) {
613	firstName = firstName.substring(0, SQL_FIRSTNAME_DIM);
614	}
615	if (lastName != null && lastName.length() > SQL_LASTNAME_DIM) {
616	lastName = lastName.substring(0, SQL_LASTNAME_DIM);
617	}
618	if (category != null && category.length() > SQL_CATEGORY_DIM) {
619	category = category.substring(0, SQL_CATEGORY_DIM);
620	}
621	if (alias != null && alias.length() > SQL_NICK_DIM) {
622	alias = alias.substring(0, SQL_NICK_DIM);
623	}
624
625	// Formata o nome completo do contato
626	fullName = new StringBuffer();
627
628	if (firstName != null && firstName.length() > 0)
629	fullName.append(truncate(firstName, SQL_FIRSTNAME_DIM));
630	if (lastName != null && lastName.length() > 0) {
631	if (firstName != null && firstName.length() > 0
632	&& lastName != null && lastName.length() > 0)
633	fullName.append(" " + truncate(lastName, SQL_LASTNAME_DIM));
634	else
635	fullName.append(truncate(lastName, SQL_LASTNAME_DIM));
636	}
637
638	// Formata a data de aniversario do contato
639	if ((birthday != null) && (!birthday.equals(""))) {
640	anniversary = new java.sql.Date((new SimpleDateFormat(
641	"yyyy-MM-dd").parse(birthday)).getTime());
642	} else {
643	anniversary = null;
644	}
645
646	// Prepara os dados do contato para serem atualizados no banco de
647	// dados
648	queryUpdateFunPimContact = new StringBuffer();
649	queryUpdateFunPimContact.append(SQL_UPDATE_FNBL_PIM_CONTACT_BEGIN
650	+ SQL_FIELD_LAST_UPDATE + SQL_EQUALS_QUESTIONMARK_COMMA);
651	queryUpdateFunPimContact.append(SQL_FIELD_FIRST_NAME
652	+ SQL_EQUALS_QUESTIONMARK_COMMA);
653	queryUpdateFunPimContact.append(SQL_FIELD_LAST_NAME
654	+ SQL_EQUALS_QUESTIONMARK_COMMA);
655	queryUpdateFunPimContact.append(SQL_FIELD_NAME
656	+ SQL_EQUALS_QUESTIONMARK_COMMA);
657	queryUpdateFunPimContact.append(SQL_FIELD_BIRTHDAY
658	+ SQL_EQUALS_QUESTIONMARK_COMMA);
659	queryUpdateFunPimContact.append(SQL_FIELD_CATEGORY
660	+ SQL_EQUALS_QUESTIONMARK_COMMA);
661	queryUpdateFunPimContact.append(SQL_FIELD_PHOTO
662	+ SQL_EQUALS_QUESTIONMARK_COMMA);
663	queryUpdateFunPimContact.append(SQL_FIELD_NOTES
664	+ SQL_EQUALS_QUESTIONMARK_COMMA);
665	queryUpdateFunPimContact.append(SQL_FIELD_NICK
666	+ SQL_EQUALS_QUESTIONMARK_COMMA);
667	queryUpdateFunPimContact
668	.append(SQL_FIELD_STATUS + SQL_EQUALS_QUESTIONMARK
669	+ SQL_UPDATE_FNBL_PIM_CONTACT_END);
670
671	ps = con.prepareStatement(queryUpdateFunPimContact.toString());
672
673	ps.setLong(1, lastUpdate.getTime()); // last_update
674	ps.setString(2, firstName); // given_names
675	ps.setString(3, lastName); // family_names
676	ps.setString(4, fullName.toString()); // names_ordered
677	ps.setDate(5, anniversary); // birthdate
678	ps.setString(6, category); // category
679	ps.setBytes(7, imagePhoto); // photo
680	ps.setString(8, note); // notes
681	ps.setString(9, alias); // alias
682	ps.setString(10, String.valueOf(Def.PIM_STATE_UPDATED)); // last_status
683	ps.setLong(11, id_contact); // id_contact
684	ps.setLong(12, Long.parseLong(userId)); // id_owner
685	ps.executeUpdate();
686
687	// emails
688
689	// Apaga os emails do contato na tabela phpgw_cc_connections
690	ps = con
691	.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)");
692	ps.setLong(1, id_contact);
693	ps.executeUpdate();
694
695	// Apaga as referencias de emails do contato na tabela
696	// phpgw_cc_contact_conns
697	ps = con
698	.prepareStatement("DELETE FROM phpgw_cc_contact_conns WHERE id_contact = ? AND id_typeof_contact_connection = 1");
699	ps.setLong(1, id_contact);
700	ps.executeUpdate();
701
702	if (!emails.isEmpty()) {
703
704	boolean type_mail = true;
705
706	for (int i = 0, l = emails.size(); i < l; i++) {
707
708	email = emails.get(i);
709
710	if ((FIELD_EMAIL_1_ADDRESS).equals(email.getEmailType())) {
711	type = TYPE_EMAIL_1_ADDRESS;
712	type_mail = true;
713	} else if ((FIELD_EMAIL_2_ADDRESS).equals(email
714	.getEmailType())) {
715	type = TYPE_EMAIL_2_ADDRESS;
716	type_mail = false;
717	} else {
718	continue; // no save unknown property
719	}
720
721	String emailValue = email.getPropertyValueAsString();
722	emailValue = truncate(emailValue, SQL_EMAIL_DIM);
723
724	if (emailValue != null && emailValue.length() != 0) {
725
726	// Verifica o id maximo da tabela phpgw_cc_connections
727	ps = con.prepareStatement(SQL_ID_MAX_CONNECTIONS);
728	rs = ps.executeQuery();
729
730	if (rs.next()) {
731	long id = rs.getInt(1);
732	id_connection = (id == 0) ? 1 : ++id;
733	}
734
735	// Insere os emails do contato na tabela
736	// phpgw_cc_connections
737	ps = con.prepareStatement(SQL_INSERT_INTO_CONTACT_ITEM);
738	ps.setLong(1, id_connection);
739	ps.setString(2, type);
740	ps.setString(3, emailValue);
741	ps.setBoolean(4, type_mail);
742	ps.executeUpdate();
743
744	// Insere as referencias de emails do contato na tabela
745	// phpgw_cc_connections
746	ps = con
747	.prepareStatement(SQL_INSERT_INTO_FNBL_CONNECTIONS);
748	ps.setLong(1, id_contact);
749	ps.setLong(2, id_connection);
750	ps.setLong(3, 1);
751	ps.executeUpdate();
752	}
753	}
754	}
755
756	// phones
757
758	// Apaga os telefones do contato na tabela phpgw_cc_connections
759	ps = con
760	.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)");
761	ps.setLong(1, id_contact);
762	ps.executeUpdate();
763
764	// Apaga as referencias de telefones do contato na tabela
765	// phpgw_cc_contact_conns
766	ps = con
767	.prepareStatement("DELETE FROM phpgw_cc_contact_conns WHERE id_contact = ? AND id_typeof_contact_connection = 2");
768	ps.setLong(1, id_contact);
769	ps.executeUpdate();
770
771	if (!phones.isEmpty()) {
772
773	boolean type_fone = true; // Seta o primeiro telefone como
774	// principal no expresso
775
776	for (int i = 0, l = phones.size(); i < l; i++) {
777
778	phone = phones.get(i);
779	phoneType = phone.getPhoneType();
780
781	if ((FIELD_PRIMARY_TELEPHONE_NUMBER).equals(phoneType)) {
782	type = TYPE_PRIMARY_TELEPHONE_NUMBER;
783	} else if ((FIELD_HOME_TELEPHONE_NUMBER).equals(phoneType)) {
784	type = TYPE_HOME_TELEPHONE_NUMBER;
785	} else if ((FIELD_MOBILE_TELEPHONE_NUMBER)
786	.equals(phoneType)) {
787	type = TYPE_MOBILE_TELEPHONE_NUMBER;
788	} else if ((FIELD_BUSINESS_TELEPHONE_NUMBER)
789	.equals(phoneType)) {
790	type = TYPE_BUSINESS_TELEPHONE_NUMBER;
791	} else if ((FIELD_BUSINESS_FAX_NUMBER).equals(phoneType)) {
792	type = TYPE_BUSINESS_FAX_NUMBER;
793	} else if ((FIELD_PAGER_NUMBER).equals(phoneType)) {
794	type = TYPE_PAGER_NUMBER;
795	} else {
796	continue; // Unknown property: saves nothing
797	}
798
799	String phoneValue = phone.getPropertyValueAsString();
800	phoneValue = truncate(phoneValue, SQL_EMAIL_DIM);
801
802	if (phoneValue != null && phoneValue.length() != 0) {
803
804	// Verifica o id maximo da tabela phpgw_cc_connections
805	ps = con.prepareStatement(SQL_ID_MAX_CONNECTIONS);
806	rs = ps.executeQuery();
807
808	if (rs.next()) {
809	long id = rs.getInt(1);
810	id_connection = (id == 0) ? 1 : ++id;
811	}
812
813	// Insere os telefones do contato na tabela
814	// phpgw_cc_connections
815	ps = con.prepareStatement(SQL_INSERT_INTO_CONTACT_ITEM);
816	ps.setLong(1, id_connection);
817	ps.setString(2, type);
818	ps.setString(3, phoneValue);
819	ps.setBoolean(4, type_fone);
820	ps.executeUpdate();
821
822	type_fone = false; // os demais telefones serao setados
823	// como nao padrao
824
825	// Insere as referencias de telefones do contato na
826	// tabela phpgw_cc_connections
827	ps = con
828	.prepareStatement(SQL_INSERT_INTO_FNBL_CONNECTIONS);
829	ps.setLong(1, id_contact);
830	ps.setLong(2, id_connection);
831	ps.setLong(3, 2);
832	ps.executeUpdate();
833	}
834	}
835	}
836
837
838	// endereco
839	boolean syncAddress = true;
840	if(syncAddress) {
841	// Apaga os contatos na tabela phpgw_cc_addresses
842	ps = con
843	.prepareStatement("DELETE FROM phpgw_cc_addresses WHERE id_address IN (SELECT id_address FROM phpgw_cc_contact_addrs WHERE id_contact = ?)");
844	ps.setLong(1, id_contact);
845	ps.executeUpdate();
846
847	// Apaga os contatos na tabela phpgw_cc_contact_addrs
848	ps = con
849	.prepareStatement("DELETE FROM phpgw_cc_contact_addrs WHERE id_contact = ?");
850	ps.setLong(1, id_contact);
851	ps.executeUpdate();
852
853	// Para cadas tipo de endereco RESIDENCIAL \| COMERCIAL
854	for (TypeAddress typeAddress: TypeAddress.values()) {
855
856	Address address = null;
857	boolean defaultAddress = false;
858
859	// se ambos sao nulos não faz nada aqui
860	if (persoAddress == null && businAddress == null) {
861	break;
862	}
863
864	if (typeAddress.equals(TypeAddress.RESIDENCIAL)) {
865	address=persoAddress;
866	defaultAddress = true;
867	} else if (typeAddress.equals(TypeAddress.COMERCIAL)) {
868	address=businAddress;
869	}
870
871	// Pula para o proximo
872	if(address == null) {
873	continue;
874	}
875
876	String addressValue = address.getStreet().getPropertyValueAsString();
877	addressValue = truncate(addressValue, SQL_ADDRESS_DIM);
878
879	if (addressValue != null && addressValue.length() != 0) {
880
881	// Verifica o id maximo da tabela phpgw_cc_addresses
882	ps = con.prepareStatement(SQL_ID_MAX_ADDRESS);
883	rs = ps.executeQuery();
884	long id_address = 0;
885
886	if (rs.next()) {
887	long id = rs.getInt(1);
888	id_address = (id == 0) ? 1 : ++id;
889	}
890
891	// Insere o endereco do contato na tabela
892	String idCountry;
893	try {
894	idCountry = Country.getCountryByName(address
895	.getCountry().getPropertyValueAsString()).getCode();
896	} catch (Exception e) {
897	idCountry = "BR";
898	}
899	long idState = searchStateId(con, address.getState().getPropertyValueAsString(), idCountry);
900	long idCity = searchCityId(con, address.getCity().getPropertyValueAsString(), idState, idCountry);
901
902	ps = con.prepareStatement(SQL_INSERT_ADDRESS);
903	ps.setLong(1, id_address);
904	ps.setLong(2, idCity);
905	ps.setLong(3, idState);
906	ps.setString(4, idCountry);
907	ps.setString(5, addressValue);
908	ps.setString(6, address.getExtendedAddress().getPropertyValueAsString());
909	ps.setString(7, address.getPostalCode().getPropertyValueAsString());
910	ps.setBoolean(8, defaultAddress);
911	ps.executeUpdate();
912
913	ps = con.prepareStatement(SQL_INSERT_CONTACT_ADDRESS);
914	ps.setLong(1, id_contact);
915	ps.setLong(2, id_address);
916	ps.setInt(3, typeAddress.getId());
917	ps.executeUpdate();
918	}
919	}
920	}
921	con.commit();
922	con.setAutoCommit(true);
923
924	} catch (Exception e) {
925	throw new PIMDBAccessException("\n=> Error updating contact.\n", e);
926	} finally {
927	DBTools.close(con, ps, rs);
928	}
929
930	log.info("\n\n=> Update item with ID " + id_contact + "\n");
931	log.info("\n\n=> PIMContactDAO updateItem end\n");
932
933	return Long.toString(id_contact);
934	}
935
936
937	private long searchStateId(Connection con, String stateName, String idCountry) throws Exception {
938
939	PreparedStatement ps = null;
940	ResultSet rs = null;
941	long stateId = 0;
942
943	try {
944	String SQL_GET_STATE = SQL_GET_STATE_ITEM + " where upper("
945	+ SQL_FIELD_STATE_NAME + ")" + SQL_EQUALS_QUESTIONMARK
946	+ " and " + SQL_FIELD_COUNTRY_ID + SQL_EQUALS_QUESTIONMARK;
947	ps = con.prepareStatement(SQL_GET_STATE);
948	ps.setString(1, stateName.toUpperCase());
949	ps.setString(2, idCountry);
950	rs = ps.executeQuery();
951	if (rs.next()) {
952	stateId = rs.getInt(SQL_FIELD_STATE_ID);
953	return stateId;
954	}
955	} catch (Exception e) {
956	e.printStackTrace();
957	}
958
959	ps = con.prepareStatement(SQL_ID_MAX_STATE);
960	rs = ps.executeQuery();
961
962	if (rs.next()) {
963	long id = rs.getInt(1);
964	stateId = (id == 0) ? 1 : ++id;
965	}
966	else {
967	return 0;
968	}
969
970	ps = con.prepareStatement(SQL_INSERT_STATE);
971	ps.setLong(1, stateId);
972	ps.setString(2, idCountry);
973	ps.setString(3, stateName);
974	ps.setString(4, stateName.substring(0, 2));
975	if(ps.executeUpdate() > 0) {
976	return stateId;
977	}
978
979	return 0;
980	}
981
982	private long searchCityId(Connection con, String cityName, long idState, String idCountry) throws Exception {
983
984	PreparedStatement ps = null;
985	ResultSet rs = null;
986	long cityId = 0;
987
988	try {
989	String SQL_GET_CITY = SQL_GET_CITY_ITEM + " where "
990	+ SQL_FIELD_STATE_ID + SQL_EQUALS_QUESTIONMARK + " and "
991	+ SQL_FIELD_COUNTRY_ID + SQL_EQUALS_QUESTIONMARK
992	+ " and upper(" + SQL_FIELD_CITY_NAME + ")"
993	+ SQL_EQUALS_QUESTIONMARK;
994	ps = con.prepareStatement(SQL_GET_CITY);
995	ps.setLong(1, idState);
996	ps.setString(2, idCountry);
997	ps.setString(3, cityName.toUpperCase());
998	rs = ps.executeQuery();
999	if (rs.next()) {
1000	cityId = rs.getInt(SQL_FIELD_CITY_ID);
1001	return cityId;
1002	}
1003	} catch (Exception e) {
1004	e.printStackTrace();
1005	}
1006	ps = con.prepareStatement(SQL_ID_MAX_CITY);
1007	rs = ps.executeQuery();
1008
1009	if (rs.next()) {
1010	long id = rs.getInt(1);
1011	cityId = (id == 0) ? 1 : ++id;
1012	}
1013	else {
1014	return 0;
1015	}
1016
1017	ps = con.prepareStatement(SQL_INSERT_CITY);
1018	ps.setLong(1, cityId);
1019	ps.setLong(2, idState);
1020	ps.setString(3, idCountry);
1021	ps.setNull(4, 0);
1022	ps.setString(5, null);
1023	ps.setString(6, cityName);
1024	if(ps.executeUpdate() > 0) {
1025	return cityId;
1026	}
1027
1028	return 0;
1029	}
1030
1031	/**
1032	* Removes the contact with given UID and sets its last_update field,
1033	* provided it has the same userId as this DAO. The deletion is soft
1034	* (reversible).
1035	*
1036	* @param uid
1037	* corresponds to the id field in the phpgw_cc_contact table
1038	* @throws PIMDBAccessException
1039	*/
1040	public void removeItem(String uid) throws PIMDBAccessException {
1041
1042	log.info("\n\n=> DAO start removeItem " + uid + "\n");
1043
1044	Connection con = null;
1045	PreparedStatement ps = null;
1046
1047	try {
1048	// Looks up the data source when the first connection is created
1049	con = getDataSource().getConnection();
1050	con.setAutoCommit(false);
1051
1052	ps = con
1053	.prepareStatement("DELETE FROM phpgw_cc_addresses WHERE id_address IN (SELECT id_address FROM phpgw_cc_contact_addrs WHERE id_contact = ?)");
1054	ps.setLong(1, Long.parseLong(uid));
1055	ps.executeUpdate();
1056
1057	ps = con
1058	.prepareStatement("DELETE FROM phpgw_cc_contact_addrs WHERE id_contact = ?");
1059	ps.setLong(1, Long.parseLong(uid));
1060	ps.executeUpdate();
1061
1062	ps = con
1063	.prepareStatement("DELETE FROM phpgw_cc_connections WHERE id_connection IN (SELECT id_connection FROM phpgw_cc_contact_conns WHERE id_contact = ?)");
1064	ps.setLong(1, Long.parseLong(uid));
1065	ps.executeUpdate();
1066
1067	ps = con
1068	.prepareStatement("DELETE FROM phpgw_cc_contact_conns WHERE id_contact = ?");
1069	ps.setLong(1, Long.parseLong(uid));
1070	ps.executeUpdate();
1071
1072	ps = con
1073	.prepareStatement("DELETE FROM phpgw_cc_contact WHERE id_contact = ?");
1074	ps.setLong(1, Long.parseLong(uid));
1075	ps.executeUpdate();
1076
1077	con.commit();
1078	con.setAutoCommit(true);
1079
1080	} catch (Exception e) {
1081
1082	e.printStackTrace();
1083	throw new PIMDBAccessException("\nError deleting contact.\n", e);
1084
1085	} finally {
1086	DBTools.close(con, ps, null);
1087	}
1088	}
1089
1090	/**
1091	* Removes a contact, provided it has the same userId as this DAO. The
1092	* deletion is soft (reversible).
1093	*
1094	* @param contact
1095	* whence the UID and the last update Date are extracted
1096	* @throws PIMDBAccessException
1097	*/
1098	public void removeItem(ContactWrapper contact) throws PIMDBAccessException {
1099
1100	removeItem(contact.getId());
1101	}
1102
1103	/**
1104	* Removes all contacts, provided it has the same userId as this DAO. The
1105	* deletion is soft (reversible).
1106	*
1107	* @throws PIMDBAccessException
1108	*/
1109	public void removeAllItems() throws PIMDBAccessException {
1110
1111	log.info("\n\n =>DAO start removeAllItems\n");
1112
1113	Connection con = null;
1114	PreparedStatement ps = null;
1115
1116	try {
1117	// Looks up the data source when the first connection is created
1118	con = getDataSource().getConnection();
1119	con.setAutoCommit(false);
1120
1121	ps = con
1122	.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 = ?))");
1123	ps.setLong(1, Long.parseLong(userId));
1124	ps.executeUpdate();
1125
1126	ps = con
1127	.prepareStatement("DELETE FROM phpgw_cc_contact_addrs WHERE id_contact IN(SELECT id_contact FROM phpgw_cc_contact WHERE id_owner = ?)");
1128	ps.setLong(1, Long.parseLong(userId));
1129	ps.executeUpdate();
1130
1131	ps = con
1132	.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 = ?))");
1133	ps.setLong(1, Long.parseLong(userId));
1134	ps.executeUpdate();
1135
1136	ps = con
1137	.prepareStatement("DELETE FROM phpgw_cc_contact_conns WHERE id_contact IN(SELECT id_contact FROM phpgw_cc_contact WHERE id_owner = ?)");
1138	ps.setLong(1, Long.parseLong(userId));
1139	ps.executeUpdate();
1140
1141	ps = con
1142	.prepareStatement("DELETE FROM phpgw_cc_contact WHERE id_owner = ?");
1143	ps.setLong(1, Long.parseLong(userId));
1144	ps.executeUpdate();
1145
1146	con.commit();
1147	con.setAutoCommit(true);
1148
1149	} catch (Exception e) {
1150
1151	throw new PIMDBAccessException("\n=> Error deleting contacts.\n", e);
1152
1153	} finally {
1154	DBTools.close(con, ps, null);
1155	}
1156	}
1157
1158	/**
1159	* Retrieves the UID list of all contacts belonging to the user.
1160	*
1161	* @throws PIMDBAccessException
1162	* @return a List of UIDs (as String objects)
1163	*/
1164	public List getAllItems() throws PIMDBAccessException {
1165
1166	log.info("\n\n=> PIMContactDAO getAllItems begin\n");
1167
1168	Connection con = null;
1169	PreparedStatement ps = null;
1170	List contacts = new ArrayList();
1171	ResultSet rs = null;
1172
1173	try {
1174	// Looks up the data source when the first connection is created
1175	con = getDataSource().getConnection();
1176
1177	ps = con.prepareStatement(SQL_GET_CONTACT_ID_LIST_BY_USER
1178	+ SQL_ORDER_BY_ID);
1179	ps.setLong(1, Long.parseLong(userId));
1180
1181	rs = ps.executeQuery();
1182
1183	while (rs.next()) {
1184	contacts.add(Long.toString(rs.getLong(1))); // It's the first
1185	// and only column
1186	}
1187
1188	} catch (Exception e) {
1189	e.printStackTrace();
1190	throw new PIMDBAccessException("\n=> Error listing contacts.\n", e);
1191	} finally {
1192	DBTools.close(con, ps, rs);
1193	}
1194
1195	log.info("\n\n=> PIMContactDAO getAllItems end\n");
1196
1197	return contacts;
1198	}
1199
1200	/**
1201	* Gets the contact with given UID, provided it has the same userId as this
1202	* DAO.
1203	*
1204	* @param uid
1205	* corresponds to the id field in the fnbl_pim_contact table
1206	* @throws PIMDBAccessException
1207	* @return the contact as a ContactWrapper object.
1208	*/
1209	public ContactWrapper getItem(String uid) throws PIMDBAccessException {
1210
1211	log.info("\n\n=> DAO start getItem " + uid + "\n");
1212
1213	Connection con = null;
1214	PreparedStatement ps = null;
1215	ResultSet rs = null;
1216	ContactWrapper c;
1217
1218	try {
1219	// Looks up the data source when the first connection is created
1220	con = getDataSource().getConnection();
1221
1222	ps = con.prepareStatement(SQL_GET_CONTACT_BY_ID_USER);
1223	ps.setLong(1, Long.parseLong(uid));
1224	ps.setLong(2, Long.parseLong(userId));
1225	rs = ps.executeQuery();
1226	c = createContact(uid, rs);
1227
1228	ps = con.prepareStatement(SQL_GET_CONTACT_ITEM_BY_ID);
1229	ps.setLong(1, Long.parseLong(uid));
1230	rs = ps.executeQuery();
1231
1232	try {
1233	addPIMContactItems(c, rs);
1234	} catch (SQLException sqle) {
1235	throw new SQLException(
1236	"\nError while adding extra PIM contact "
1237	+ "information.\n" + sqle.getMessage(), sqle
1238	.getSQLState());
1239	}
1240
1241	try {
1242	ps = con.prepareStatement(SQL_GET_CONTACT_ADDRESS_ITEM);
1243	ps.setLong(1, Long.parseLong(uid));
1244	ps.setLong(2, Long.parseLong(userId));
1245	ps.setInt(3, TypeAddress.RESIDENCIAL.getId());
1246	rs = ps.executeQuery();
1247
1248	Address address = c.getContact().getPersonalDetail().getAddress();
1249	addAddress(address, rs);
1250	} catch (Exception e) {
1251	throw new SQLException("\nError while adding personal address information");
1252	}
1253
1254	try {
1255	ps = con.prepareStatement(SQL_GET_CONTACT_ADDRESS_ITEM);
1256	ps.setLong(1, Long.parseLong(uid));
1257	ps.setLong(2, Long.parseLong(userId));
1258	ps.setInt(3, TypeAddress.COMERCIAL.getId());
1259	rs = ps.executeQuery();
1260
1261	Address address = c.getContact().getBusinessDetail().getAddress();
1262	addAddress(address, rs);
1263	} catch (Exception e) {
1264	throw new SQLException("\nError while adding business address information");
1265	}
1266
1267	} catch (Exception e) {
1268	e.printStackTrace();
1269	throw new PIMDBAccessException("\n=> Error seeking contact.\n", e);
1270	} finally {
1271	DBTools.close(con, ps, rs);
1272	}
1273
1274	return c;
1275	}
1276
1277
1278	private static void addAddress(Address address, ResultSet rs) throws Exception {
1279
1280	ResultSetMetaData rsmd = rs.getMetaData();
1281
1282	String column = null;
1283	int columnCount = 0;
1284
1285	while (rs.next()) {
1286
1287	columnCount = rsmd.getColumnCount();
1288
1289	if (rs.getString(SQL_FIELD_ADDRESS1)==null \|\| (rs.getString(SQL_FIELD_ADDRESS1)!=null && "".equals(rs.getString(SQL_FIELD_ADDRESS1).trim()))) {
1290	break;
1291	}
1292
1293	for (int i = 1; i <= columnCount; ++i) {
1294
1295	column = rsmd.getColumnName(i);
1296	if (SQL_FIELD_TYPEOF.equalsIgnoreCase(column)) {
1297	continue;
1298	} else if (SQL_FIELD_COMPLEMENT.equalsIgnoreCase(column)) {
1299	address.getExtendedAddress().setPropertyValue(rs.getString(i));
1300	} else if (SQL_FIELD_ADDRESS1.equalsIgnoreCase(column)) {
1301	address.getStreet().setPropertyValue(rs.getString(i));
1302	} else if (SQL_FIELD_POSTALCODE.equalsIgnoreCase(column)) {
1303	address.getPostalCode().setPropertyValue(rs.getString(i));
1304	} else if (SQL_FIELD_CITY_NAME.equalsIgnoreCase(column)) {
1305	address.getCity().setPropertyValue(rs.getString(i));
1306	} else if (SQL_FIELD_STATE_NAME.equalsIgnoreCase(column)) {
1307	address.getState().setPropertyValue(rs.getString(i));
1308	} else if (SQL_FIELD_COUNTRY_ID.equalsIgnoreCase(column)) {
1309	Country country = Country.getCountryByCode(rs.getString(i));
1310	address.getCountry().setPropertyValue(country.getName());
1311	} else {
1312	throw new SQLException("\n=> Unhandled column: " + column);
1313	}
1314
1315	}
1316	}
1317	}
1318
1319	/**
1320	* Retrieves the UID list of the contacts considered to be "twins" of a
1321	* given contact.
1322	*
1323	* @param c
1324	* the Contact object representing the contact whose twins need
1325	* be found. In the present implementation, only the following
1326	* data matter:
1327	* <UL>
1328	* <LI>first name
1329	* <LI>last name
1330	* </UL>
1331	* @throws PIMDBAccessException
1332	* @return a List of UIDs (as String objects) that may be empty but not null
1333	*/
1334	public List getTwinItems(Contact c) throws PIMDBAccessException {
1335
1336	log.info("\n\n=> PIMContactDAO getTwinItems begin\n");
1337
1338	List twins = new ArrayList();
1339	Connection con = null;
1340	PreparedStatement ps = null;
1341	ResultSet rs = null;
1342
1343	try {
1344
1345	String firstName = c.getName().getFirstName()
1346	.getPropertyValueAsString();
1347	String lastName = c.getName().getLastName()
1348	.getPropertyValueAsString();
1349
1350	// Looks up the data source when the first connection is created
1351	con = getDataSource().getConnection();
1352
1353	if (firstName == null \|\| ("null".equals(firstName))) {
1354	firstName = "";
1355	}
1356	if (lastName == null \|\| ("null".equals(lastName))) {
1357	lastName = "";
1358	}
1359
1360	String fnSearch = (firstName.length() == 0 ? "<N/A>" : firstName);
1361	String lnSearch = (lastName.length() == 0 ? "<N/A>" : lastName);
1362	StringBuilder sb = new StringBuilder(100);
1363
1364	sb.append("\n\nLooking for items having: ")
1365	.append("\n> first name : '")
1366	.append(fnSearch).append('\'')
1367	.append("\n> last name : '")
1368	.append(lnSearch).append('\'').append("\n");
1369
1370	log.info(sb.toString());
1371
1372	ps = con.prepareStatement(SQL_GET_CONTACT_TWIN_ID_LIST
1373	+ SQL_ORDER_BY_ID);
1374	ps.setLong(1, Long.parseLong(userId));
1375	ps.setString(2, firstName);
1376	ps.setString(3, firstName);
1377	ps.setString(4, lastName);
1378	ps.setString(5, lastName);
1379	rs = ps.executeQuery();
1380
1381	long twinId;
1382
1383	while (rs.next()) {
1384
1385	twinId = rs.getLong(1); // The id is the first and only column
1386	log.info("\n\n=> Twin found: " + twinId + "\n");
1387	twins.add(Long.toString(twinId));
1388	}
1389
1390	} catch (Exception e) {
1391	throw new PIMDBAccessException("\n=> Error retrieving twin.\n", e);
1392	} finally {
1393	DBTools.close(con, ps, rs);
1394	}
1395
1396	log.info("\n\n=> PIMContactDAO getTwinItems end\n");
1397
1398	return twins;
1399	}
1400
1401	/**
1402	* Retrieves the state of the given item, provided it's been modified after
1403	* a certain moment.
1404	*
1405	* @param uid
1406	* the UID of the item to be checked (as a String object)
1407	* @param since
1408	* the Timestamp that the item's lastUpdate field is checked
1409	* against: if the item has been modified before that moment, an
1410	* "unchanged" state marker is returned
1411	* @throws PIMDBAccessException
1412	* @return a char identifying either one of the 3 standard states ("new",
1413	* "deleted", "updated") or the special "unchanged" status, all of
1414	* them as defined in com.funambol.pim.util.Def
1415	*/
1416	public char getItemState(String uid, Timestamp since)
1417	throws PIMDBAccessException {
1418
1419	log.info("\n\n=> DAO start getItemState\n");
1420
1421	Connection con = null;
1422	PreparedStatement ps = null;
1423	ResultSet rs = null;
1424	char status;
1425
1426	try {
1427	// Looks up the data source when the first connection is created
1428	con = getDataSource().getConnection();
1429
1430	ps = con.prepareStatement(SQL_GET_STATUS_BY_ID_USER_TIME);
1431	ps.setLong(1, Long.parseLong(uid));
1432	ps.setLong(2, Long.parseLong(userId));
1433	ps.setLong(3, since.getTime());
1434	rs = ps.executeQuery();
1435
1436	if (!rs.next()) {
1437
1438	status = Def.PIM_STATE_UNCHANGED;
1439
1440	log.info("\n\n=> Item " + uid + "'s status wasn't retrieved "
1441	+ "because the item hasn't been modified since "
1442	+ since + "\n");
1443
1444	} else {
1445
1446	status = rs.getString(1).charAt(0);
1447
1448	log.info("\n\n=> Item " + uid + " has status " + status + "\n");
1449
1450	}
1451
1452	} catch (Exception e) {
1453	throw new PIMDBAccessException(
1454	"\n=> Error retrieving item state.\n", e);
1455	} finally {
1456	DBTools.close(con, ps, rs);
1457	}
1458
1459	return status;
1460	}
1461
1462	// ---------------------------------------------------------- Private
1463	// methods
1464
1465	/**
1466	* Creates a ContactWrapper object from a ResultSet. Only the basic data are
1467	* set.
1468	*
1469	* @param wrapperId
1470	* the UID of the wrapper object to be returned
1471	* @param rs
1472	* the result of the execution of a proper SQL SELECT statement
1473	* on the phpgw_cc_contact table, with the cursor before its
1474	* first row
1475	* @return a newly created ContactWrapper initialized with the fields in the
1476	* result set
1477	* @throws java.sql.SQLException
1478	* @throws NotFoundException
1479	*/
1480	private static ContactWrapper createContact(String wrapperId, ResultSet rs)
1481	throws SQLException, NotFoundException {
1482
1483	if (!rs.next()) {
1484	throw new NotFoundException("\n\n=> No contact found.\n");
1485	}
1486
1487	ResultSetMetaData rsmd = rs.getMetaData();
1488	ContactWrapper cw = null;
1489
1490	String column = null;
1491	String uid = null;
1492	String userId = null;
1493
1494	uid = String.valueOf(rs.getLong(SQL_FIELD_ID));
1495	userId = rs.getString(SQL_FIELD_USERID);
1496
1497	Contact c = new Contact();
1498	cw = new ContactWrapper(wrapperId, userId, c);
1499
1500	int columnCount = rsmd.getColumnCount();
1501
1502	for (int i = 1; i <= columnCount; ++i) {
1503
1504	column = rsmd.getColumnName(i);
1505
1506	// General
1507	if (SQL_FIELD_ID.equalsIgnoreCase(column)) {
1508	// Does nothing: field already set at construction time
1509	} else if (SQL_FIELD_LAST_UPDATE.equalsIgnoreCase(column)) {
1510	cw.setLastUpdate(new Timestamp(rs.getLong(i)));
1511	} else if (SQL_FIELD_USERID.equalsIgnoreCase(column)) {
1512	// Does nothing: field already set at construction time
1513	} else if (SQL_FIELD_STATUS.equalsIgnoreCase(column)) {
1514	cw.setStatus(rs.getString(i).charAt(0));
1515	} else if (SQL_FIELD_FIRST_NAME.equalsIgnoreCase(column)) {
1516	c.getName().getFirstName().setPropertyValue(rs.getString(i));
1517	} else if (SQL_FIELD_LAST_NAME.equalsIgnoreCase(column)) {
1518	c.getName().getLastName().setPropertyValue(rs.getString(i));
1519	} else if (SQL_FIELD_BIRTHDAY.equalsIgnoreCase(column)) {
1520	c.getPersonalDetail().setBirthday(rs.getString(i));
1521	} else if (SQL_FIELD_CATEGORY.equalsIgnoreCase(column)) {
1522	c.getCategories().setPropertyValue(rs.getString(i));
1523	} else if (SQL_FIELD_PHOTO.equalsIgnoreCase(column)) {
1524	if(c.getPersonalDetail()==null) {
1525	PersonalDetail pd = new PersonalDetail();
1526	c.setPersonalDetail(pd);
1527	}
1528	Photo photo = new Photo();
1529	photo.setImage(rs.getBytes(i));
1530	c.getPersonalDetail().setPhotoObject(photo);
1531	} else if (SQL_FIELD_NOTES.equalsIgnoreCase(column)) {
1532
1533	try {
1534	if(rs.getString(i) != null) {
1535	if (c.getNotes() == null
1536	\|\| (c.getNotes() != null && c.getNotes().size() != 1)
1537	\|\| (c.getNotes() != null
1538	&& c.getNotes().size() == 1 && c.getNotes()
1539	.get(0) == null)) {
1540	List<Note> l = new ArrayList<Note>(1);
1541	l.add(new Note());
1542	c.setNotes(l);
1543	}
1544	Note n = (Note) c.getNotes().get(0);
1545	n.setPropertyValue(rs.getString(i));
1546	n.setPropertyType("Body"); // HARD CODE
1547	}
1548	} catch (Exception e) {
1549	log.error(e.getMessage(),e);
1550	}
1551
1552	} else if (SQL_FIELD_NICK.equalsIgnoreCase(column)) {
1553	c.getName().getNickname().setPropertyValue(rs.getString(i));
1554	} else {
1555	throw new SQLException("\n=> Unhandled column: " + column);
1556	}
1557	}
1558
1559	return cw;
1560	}
1561
1562	/**
1563	* Attaches extra information to a contact on the basis of a ResultSet.
1564	*
1565	* @param c
1566	* the contact still lacking extra information
1567	* @param rs
1568	* the result of the execution of a proper SQL SELECT statement
1569	* on the phpgw_cc_connections and phpgw_cc_contact_conns table,
1570	* with the cursor before its first row
1571	* @return the ContactWrapper object with extra information attached
1572	* @throws java.sql.SQLException
1573	*/
1574	private static ContactWrapper addPIMContactItems(ContactWrapper cw,
1575	ResultSet rs) throws SQLException {
1576
1577	ResultSetMetaData rsmd = rs.getMetaData();
1578	Contact c = cw.getContact();
1579
1580	String type = null;
1581	String value = null;
1582	String name = null;
1583	String column = null;
1584
1585	int columnCount = 0;
1586
1587	Phone phone;
1588	Email email;
1589
1590	while (rs.next()) {
1591
1592	columnCount = rsmd.getColumnCount();
1593
1594	for (int i = 1; i <= columnCount; ++i) {
1595
1596	column = rsmd.getColumnName(i);
1597
1598	if (SQL_FIELD_ITEM_NAME.equalsIgnoreCase(column)) {
1599	name = rs.getString(i);
1600	} else if (SQL_FIELD_ITEM_VALUE.equalsIgnoreCase(column)) {
1601	value = rs.getString(i);
1602	} else if (SQL_FIELD_ITEM_TYPE.equalsIgnoreCase(column)) {
1603	type = rs.getString(i);
1604	} else {
1605	throw new SQLException("\n=> Unhandled column: " + column);
1606	}
1607	}
1608
1609	if (type.equals("1")) { // email
1610
1611	if (TYPE_EMAIL_1_ADDRESS.equalsIgnoreCase(name)) {
1612	email = new Email();
1613	email.setEmailType(FIELD_EMAIL_1_ADDRESS);
1614	email.setPropertyValue(value);
1615	c.getPersonalDetail().addEmail(email);
1616	} else if (TYPE_EMAIL_2_ADDRESS.equalsIgnoreCase(name)) {
1617	email = new Email();
1618	email.setEmailType(FIELD_EMAIL_2_ADDRESS);
1619	email.setPropertyValue(value);
1620	c.getPersonalDetail().addEmail(email);
1621	}
1622	}
1623
1624	if (type.equals("2")) { // telefone
1625
1626	if (TYPE_PRIMARY_TELEPHONE_NUMBER.equalsIgnoreCase(name)) {
1627	phone = new Phone();
1628	phone.setPhoneType(FIELD_PRIMARY_TELEPHONE_NUMBER);
1629	phone.setPropertyValue(value);
1630	c.getBusinessDetail().addPhone(phone);
1631	} else if (TYPE_HOME_TELEPHONE_NUMBER.equalsIgnoreCase(name)) {
1632	phone = new Phone();
1633	phone.setPhoneType(FIELD_HOME_TELEPHONE_NUMBER);
1634	phone.setPropertyValue(value);
1635	c.getPersonalDetail().addPhone(phone);
1636	} else if (TYPE_MOBILE_TELEPHONE_NUMBER.equalsIgnoreCase(name)) {
1637	phone = new Phone();
1638	phone.setPhoneType(FIELD_MOBILE_TELEPHONE_NUMBER);
1639	phone.setPropertyValue(value);
1640	c.getPersonalDetail().addPhone(phone);
1641	} else if (TYPE_BUSINESS_TELEPHONE_NUMBER
1642	.equalsIgnoreCase(name)) {
1643	phone = new Phone();
1644	phone.setPhoneType(FIELD_BUSINESS_TELEPHONE_NUMBER);
1645	phone.setPropertyValue(value);
1646	c.getBusinessDetail().addPhone(phone);
1647	} else if (TYPE_BUSINESS_FAX_NUMBER.equalsIgnoreCase(name)) {
1648	phone = new Phone();
1649	phone.setPhoneType(FIELD_BUSINESS_FAX_NUMBER);
1650	phone.setPropertyValue(value);
1651	c.getBusinessDetail().addPhone(phone);
1652	} else if (TYPE_PAGER_NUMBER.equalsIgnoreCase(name)) {
1653	phone = new Phone();
1654	phone.setPhoneType(FIELD_PAGER_NUMBER);
1655	phone.setPropertyValue(value);
1656	c.getBusinessDetail().addPhone(phone);
1657	}
1658	}
1659	}
1660
1661	return cw;
1662	}
1663
1664	/**
1665	* Retrieves the UID list of the contacts belonging to the user filtered
1666	* according to the given time interval and status.
1667	*
1668	* @param since
1669	* the earliest allowed last-update Timestamp
1670	* @param to
1671	* the latest allowed last-update Timestamp
1672	* @param status
1673	* 'D' for deleted items, 'N' for new items, 'U' for updated
1674	* items
1675	* @throws PIMDBAccessException
1676	* @return a List of UIDs (as String objects)
1677	*/
1678	protected List getItemsHavingStatus(Timestamp since, Timestamp to,
1679	char status) throws PIMDBAccessException {
1680
1681	log.info("\n\n=> Seeking '" + status + "' items "
1682	+ "in time interval (" + since + "; " + to + ")\n");
1683
1684	Connection con = null;
1685	PreparedStatement ps = null;
1686	List contacts = new ArrayList();
1687	ResultSet rs = null;
1688
1689	try {
1690	// Looks up the data source when the first connection is created
1691	con = getDataSource().getConnection();
1692
1693	if (status == 'D') {
1694
1695	ps = con
1696	.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");
1697	ps.setLong(1, Long.parseLong(userId));
1698	ps.setLong(2, this.principal.getId());
1699	rs = ps.executeQuery();
1700
1701	} else {
1702
1703	ps = con
1704	.prepareStatement(SQL_GET_FNBL_PIM_CONTACT_ID_LIST_BY_USER_TIME_STATUS
1705	+ SQL_ORDER_BY_ID);
1706	ps.setLong(1, Long.parseLong(userId));
1707	ps.setLong(2, since.getTime());
1708	ps.setLong(3, to.getTime());
1709	ps.setString(4, String.valueOf(status));
1710	rs = ps.executeQuery();
1711	}
1712
1713	while (rs.next()) {
1714	contacts.add(Long.toString(rs.getLong(1))); // It's the first
1715	// and only column
1716	log.info("\n\n=> Item found " + rs.getLong(1) + "\n");
1717	}
1718
1719	} catch (Exception e) {
1720	e.printStackTrace();
1721	throw new PIMDBAccessException("\n=> Error listing contacts.\n", e);
1722	} finally {
1723	DBTools.close(con, ps, rs);
1724	}
1725
1726	return contacts;
1727	}
1728	}

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

Context Navigation

source: contrib/psync/src/main/java/br/com/prognus/psync/items/dao/PIMContactDAO.java @ 1545

Download in other formats: