/** * This class implements methods to access PIM contact data in a data store. * @author Diorgenes Felipe Grzesiuk * @copyright Copyright 2007-2008 Prognus * * This program is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License version 2 as published by * the Free Software Foundation. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with Foobar; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ package br.com.prognus.psync.items.dao; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import br.com.prognus.psync.exception.PIMDBAccessException; import br.com.prognus.psync.items.model.ContactWrapper; import br.com.prognus.psync.util.Country; import br.com.prognus.psync.util.Def; import br.com.prognus.psync.util.TypeAddress; import com.funambol.common.pim.common.Property; import com.funambol.common.pim.contact.Address; import com.funambol.common.pim.contact.BusinessDetail; import com.funambol.common.pim.contact.Contact; import com.funambol.common.pim.contact.Email; import com.funambol.common.pim.contact.Name; import com.funambol.common.pim.contact.Note; import com.funambol.common.pim.contact.PersonalDetail; import com.funambol.common.pim.contact.Phone; import com.funambol.common.pim.contact.Photo; import com.funambol.framework.security.Sync4jPrincipal; import com.funambol.framework.server.store.NotFoundException; import com.funambol.framework.tools.DBTools; public class PIMContactDAO extends PIMEntityDAO { // --------------------------------------------------------------- Constants private static final String SQL_ORDER_BY_ID = "ORDER BY id_contact"; private static final String SQL_GET_CONTACT_ID_LIST = "SELECT id_contact FROM phpgw_cc_contact "; private static final String SQL_GET_CONTACT_ID_LIST_BY_USER = SQL_GET_CONTACT_ID_LIST + "WHERE id_owner = ?"; 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"; 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 = ?"; 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 "; private static final String SQL_GET_FNBL_PIM_CONTACT_ID_LIST_BY_USER_TIME_STATUS = SQL_GET_CONTACT_ID_LIST + "WHERE id_owner = ? AND last_update > ? AND last_update < ? AND last_status = ? "; private static final String SQL_GET_CONTACT_TWIN_ID_LIST = "SELECT id_contact " + "FROM phpgw_cc_contact " + "WHERE (id_owner = ?) " + "AND ((given_names is null AND (?) = '') " + "OR (given_names = ?)) " + "AND ((family_names is null AND (?) = '') " + "OR (family_names = ?)) "; private static final String SQL_INSERT_INTO_FNBL_PIM_CONTACT = "INSERT INTO phpgw_cc_contact " + "(id_contact, id_owner, id_status, given_names, family_names, names_ordered, birthdate, category, photo, notes, alias, last_update, last_status ) " + "VALUES " + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "; private static final String SQL_INSERT_INTO_CONTACT_ITEM = "INSERT INTO phpgw_cc_connections " + "(id_connection, connection_name, connection_value, connection_is_default) " + "VALUES (?, ?, ?, ?) "; private static final String SQL_ID_MAX_CONNECTIONS = "select max(id_connection) from phpgw_cc_connections"; private static final String SQL_INSERT_INTO_FNBL_CONNECTIONS = "INSERT INTO phpgw_cc_contact_conns (id_contact, id_connection, id_typeof_contact_connection) VALUES (?, ?, ?)"; private static final String SQL_UPDATE_FNBL_PIM_CONTACT_BEGIN = "UPDATE phpgw_cc_contact SET "; private static final String SQL_UPDATE_FNBL_PIM_CONTACT_END = " WHERE id_contact = ? AND id_owner = ? "; 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 = ? )"; private static final String SQL_GET_STATE_ITEM = "SELECT id_state, id_country, state_name, state_symbol FROM phpgw_cc_state "; 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 "; private static final String SQL_INSERT_STATE = "INSERT INTO phpgw_cc_state(id_state, id_country, state_name, state_symbol) VALUES (?, ?, ?, ?)"; 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 (?, ?, ?, ?, ?, ?)"; 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 (?, ?, ?, ?, ?, ?, ?, ?)"; private static final String SQL_INSERT_CONTACT_ADDRESS = "INSERT INTO phpgw_cc_contact_addrs(id_contact, id_address, id_typeof_contact_address) VALUES (?, ?, ?)"; private static final int SQL_ADDRESS_DIM = 60; private static final String SQL_ID_MAX_ADDRESS = "select max(id_address) FROM phpgw_cc_addresses"; private static final String SQL_ID_MAX_STATE = "select max(id_state) FROM phpgw_cc_state"; private static final String SQL_ID_MAX_CITY = "select max(id_city) FROM phpgw_cc_city"; private static final String SQL_EQUALS_QUESTIONMARK = " = ?"; private static final String SQL_EQUALS_QUESTIONMARK_COMMA = " = ?, "; protected static final String SQL_FIELD_ID = "id_contact"; protected static final String SQL_FIELD_USERID = "id_owner"; protected static final String SQL_FIELD_LAST_UPDATE = "last_update"; protected static final String SQL_FIELD_STATUS = "last_status"; protected static final String SQL_FIELD_FIRST_NAME = "given_names"; protected static final String SQL_FIELD_LAST_NAME = "family_names"; protected static final String SQL_FIELD_NAME = "names_ordered"; protected static final String SQL_FIELD_NICK = "alias"; protected static final String SQL_FIELD_BIRTHDAY = "birthdate"; protected static final String SQL_FIELD_CATEGORY = "category"; protected static final String SQL_FIELD_PHOTO = "photo"; protected static final String SQL_FIELD_NOTES = "notes"; protected static final int SQL_FIRSTNAME_DIM = 49; protected static final int SQL_LASTNAME_DIM = 50; protected static final int SQL_EMAIL_DIM = 50; protected static final int SQL_PHONE_DIM = 50; protected static final int SQL_CATEGORY_DIM = 20; protected static final int SQL_NICK_DIM = 30; protected static final String TYPE_EMAIL_1_ADDRESS = "Principal"; protected static final String FIELD_EMAIL_1_ADDRESS = "Email1Address"; protected static final String TYPE_EMAIL_2_ADDRESS = "Alternativo"; protected static final String FIELD_EMAIL_2_ADDRESS = "OtherEmail2Address"; protected static final String TYPE_PRIMARY_TELEPHONE_NUMBER = "Principal"; protected static final String FIELD_PRIMARY_TELEPHONE_NUMBER = "PrimaryTelephoneNumber"; protected static final String TYPE_HOME_TELEPHONE_NUMBER = "Residencial"; protected static final String FIELD_HOME_TELEPHONE_NUMBER = "HomeTelephoneNumber"; protected static final String TYPE_MOBILE_TELEPHONE_NUMBER = "Celular"; protected static final String FIELD_MOBILE_TELEPHONE_NUMBER = "MobileTelephoneNumber"; protected static final String TYPE_BUSINESS_TELEPHONE_NUMBER = "Comercial"; protected static final String FIELD_BUSINESS_TELEPHONE_NUMBER = "BusinessTelephoneNumber"; protected static final String TYPE_BUSINESS_FAX_NUMBER = "Fax"; protected static final String FIELD_BUSINESS_FAX_NUMBER = "BusinessFaxNumber"; protected static final String TYPE_PAGER_NUMBER = "Pager"; protected static final String FIELD_PAGER_NUMBER = "PagerNumber"; protected static final String SQL_FIELD_ITEM_NAME = "connection_name"; protected static final String SQL_FIELD_ITEM_VALUE = "connection_value"; protected static final String SQL_FIELD_ITEM_TYPE = "id_typeof_contact_connection"; protected static final String SQL_FIELD_TYPEOF = "id_typeof_contact_address"; protected static final String SQL_FIELD_COMPLEMENT = "complement"; protected static final String SQL_FIELD_ADDRESS1 = "address1"; protected static final String SQL_FIELD_POSTALCODE = "postal_code"; protected static final String SQL_FIELD_CITY_NAME = "city_name"; protected static final String SQL_FIELD_STATE_NAME = "state_name"; protected static final String SQL_FIELD_COUNTRY_ID = "id_country"; protected static final String SQL_FIELD_STATE_ID = "id_state"; protected static final String SQL_FIELD_CITY_ID = "id_city"; private Sync4jPrincipal principal = null; // ------------------------------------------------------------- // Constructors /** * @see PIMEntityDAO#PIMEntityDAO(String, String) */ public PIMContactDAO(String jndiDataSourceName, Sync4jPrincipal principal) { super(jndiDataSourceName, principal.getUsername()); log.info("\n\n=> Created new PIMContactDAO for data source " + jndiDataSourceName + " and user ID LDAP " + userId + " user login " + principal.getUsername() + "\n"); this.principal = principal; } // ----------------------------------------------------------- Public // methods /** * Adds a contact. If necessary, a new ID is generated and set in the * ContactWrapper. * * @param c * as a ContactWrapper object, usually without an ID set. * @throws PIMDBAccessException * @throws SQLException * * @see ContactWrapper */ @SuppressWarnings("unchecked") public void addItem(ContactWrapper cw) throws PIMDBAccessException { log.info("\n\n=> PIMContactDAO addItem begin\n"); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; long id_contact = 0; long id_connection = 0; PersonalDetail personalDetail = null; BusinessDetail businessDetail = null; Name name = null; Phone phone = null; Email email = null; List emails = new ArrayList(); List phones = new ArrayList(); String phoneType = null; String firstName = null; String lastName = null; String birthday = null; String type = null; String category = null; StringBuffer fullName = null; Date anniversary = null; byte[] imagePhoto = null; String note = null; String alias = null; try { con = getDataSource().getConnection(); con.setAutoCommit(false); Contact c = cw.getContact(); Timestamp lastUpdate = cw.getLastUpdate(); try { List notes = c.getNotes(); int i = notes.size() - 1; note = notes.get(i).getPropertyValueAsString(); } catch (Exception e) { // log.error(e.getMessage(),e); } if(note==null) { note=""; } personalDetail = c.getPersonalDetail(); businessDetail = c.getBusinessDetail(); name = c.getName(); lastUpdate = (lastUpdate == null) ? new Timestamp(System .currentTimeMillis()) : lastUpdate; if (personalDetail != null) { emails.addAll(personalDetail.getEmails()); phones.addAll(personalDetail.getPhones()); birthday = personalDetail.getBirthday(); category = stringFrom(c.getCategories()); imagePhoto = personalDetail.getPhotoObject().getImage(); } if (businessDetail != null) { emails.addAll(businessDetail.getEmails()); phones.addAll(businessDetail.getPhones()); } if (name != null) { firstName = stringFrom(name.getFirstName()); lastName = stringFrom(name.getLastName()); alias = stringFrom(name.getNickname()); } // consulta o id maximo da tabela contatos ps = con .prepareStatement("select max(id_contact) from phpgw_cc_contact limit 1"); rs = ps.executeQuery(); if (rs.next()) { long id = rs.getInt(1); id_contact = (id == 0) ? 1 : ++id; } cw.setId(Long.toString(id_contact)); // Formata o nome completo do contato fullName = new StringBuffer(); if (firstName != null && firstName.length() > 0) fullName.append(truncate(firstName, SQL_FIRSTNAME_DIM)); if (lastName != null && lastName.length() > 0) { if (firstName != null && firstName.length() > 0 && lastName != null && lastName.length() > 0) fullName.append(" " + truncate(lastName, SQL_LASTNAME_DIM)); else fullName.append(truncate(lastName, SQL_LASTNAME_DIM)); } // Formata a data de aniversario do contato if ((birthday != null) && (!birthday.equals(""))) { anniversary = new java.sql.Date((new SimpleDateFormat( "yyyy-MM-dd").parse(birthday)).getTime()); } // Prepara os dados para inserir o contato no banco de dados ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT); ps.setLong(1, id_contact); // id_contact ps.setLong(2, Long.parseLong(userId)); // id_owner ps.setLong(3, 1); // id_status ps.setString(4, truncate(firstName, SQL_FIRSTNAME_DIM)); // given_names ps.setString(5, truncate(lastName, SQL_LASTNAME_DIM)); // family_names ps.setString(6, fullName.toString()); // names_ordered ps.setDate(7, anniversary); // birthdate ps.setString(8, truncate(category, SQL_CATEGORY_DIM)); // category ps.setBytes(9, imagePhoto); // photo ps.setString(10, note); // note ps.setString(11, truncate(alias, SQL_NICK_DIM)); // alias ps.setLong(12, lastUpdate.getTime()); // last_update ps.setString(13, String.valueOf(Def.PIM_STATE_NEW)); // last_status ps.executeUpdate(); // emails if (!emails.isEmpty()) { for (int i = 0, l = emails.size(); i < l; i++) { email = emails.get(i); Boolean type_email; if ((FIELD_EMAIL_1_ADDRESS).equals(email.getEmailType())) { type = TYPE_EMAIL_1_ADDRESS; type_email = true; } else if ((FIELD_EMAIL_2_ADDRESS).equals(email .getEmailType())) { type = TYPE_EMAIL_2_ADDRESS; type_email = false; } else { continue; // Unknown property: saves nothing } String emailValue = email.getPropertyValueAsString(); if (emailValue != null && emailValue.length() != 0) { if (emailValue.length() > SQL_EMAIL_DIM) { emailValue = emailValue.substring(0, SQL_EMAIL_DIM); } // Verifica o id maximo da tabela phpgw_cc_connections ps = con.prepareStatement(SQL_ID_MAX_CONNECTIONS); rs = ps.executeQuery(); if (rs.next()) { long id = rs.getInt(1); id_connection = (id == 0) ? 1 : ++id; } // Insere os emails do contato na tabela // phpgw_cc_connections ps = con.prepareStatement(SQL_INSERT_INTO_CONTACT_ITEM); ps.setLong(1, id_connection); ps.setString(2, type); ps.setString(3, emailValue); ps.setBoolean(4, type_email); ps.executeUpdate(); // Insere as referencias de emails do contato na tabela // phpgw_cc_connections ps = con .prepareStatement(SQL_INSERT_INTO_FNBL_CONNECTIONS); ps.setLong(1, id_contact); ps.setLong(2, id_connection); ps.setLong(3, 1); ps.executeUpdate(); } } } // phones if (!phones.isEmpty()) { Boolean type_phone = true; for (int i = 0, l = phones.size(); i < l; i++) { phone = phones.get(i); phoneType = phone.getPhoneType(); if ((FIELD_PRIMARY_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_PRIMARY_TELEPHONE_NUMBER; } else if ((FIELD_HOME_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_HOME_TELEPHONE_NUMBER; } else if ((FIELD_MOBILE_TELEPHONE_NUMBER) .equals(phoneType)) { type = TYPE_MOBILE_TELEPHONE_NUMBER; } else if ((FIELD_BUSINESS_TELEPHONE_NUMBER) .equals(phoneType)) { type = TYPE_BUSINESS_TELEPHONE_NUMBER; } else if ((FIELD_BUSINESS_FAX_NUMBER).equals(phoneType)) { type = TYPE_BUSINESS_FAX_NUMBER; } else if ((FIELD_PAGER_NUMBER).equals(phoneType)) { type = TYPE_PAGER_NUMBER; } else { continue; // Unknown property: saves nothing } String phoneValue = phone.getPropertyValueAsString(); if (phoneValue != null && phoneValue.length() != 0) { if (phoneValue.length() > SQL_PHONE_DIM) { phoneValue = phoneValue.substring(0, SQL_PHONE_DIM); } ps = con.prepareStatement(SQL_ID_MAX_CONNECTIONS); rs = ps.executeQuery(); if (rs.next()) { long id = rs.getInt(1); id_connection = (id == 0) ? 1 : ++id; } // Insere os telefones do contato na tabela // phpgw_cc_connections ps = con.prepareStatement(SQL_INSERT_INTO_CONTACT_ITEM); ps.setLong(1, id_connection); ps.setString(2, type); ps.setString(3, phoneValue); ps.setBoolean(4, type_phone); ps.executeUpdate(); type_phone = false; // os demais telefones serao setados // como nao padrao // Insere as referencias de telefones do contato na // tabela phpgw_cc_connections ps = con .prepareStatement(SQL_INSERT_INTO_FNBL_CONNECTIONS); ps.setLong(1, id_contact); ps.setLong(2, id_connection); ps.setLong(3, 2); ps.executeUpdate(); } } } con.commit(); con.setAutoCommit(true); } catch (Exception e) { throw new PIMDBAccessException("\n=> Error adding contact.\n", e); } finally { DBTools.close(con, ps, rs); } log.info("\n\n=> Added item with ID " + id_contact + "\n"); log.info("\n\n=> PIMContactDAO addItem end\n"); } /** * Updates a contact. * * @param c * as a ContactWrapper object. If its last update time is null, * then it's set to the current time. * @return the UID of the contact * @throws PIMDBAccessException * * @see ContactWrapper */ public String updateItem(ContactWrapper cw) throws PIMDBAccessException { log.info("\n\n=> PIMContactDAO updateItem begin\n"); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; long id_connection = 0; long id_contact = 0; PersonalDetail personalDetail = null; BusinessDetail businessDetail = null; Name name = null; Phone phone = null; Email email = null; String type = null; List emails = new ArrayList(); List phones = new ArrayList(); String phoneType = null; StringBuffer queryUpdateFunPimContact = null; String firstName = null; String lastName = null; StringBuffer fullName = null; String birthday = null; String category = null; Date anniversary = null; byte[] imagePhoto = null; String note = null; String alias = null; Address persoAddress = null; Address businAddress = null; try { Timestamp lastUpdate = (cw.getLastUpdate() == null) ? new Timestamp( System.currentTimeMillis()) : cw.getLastUpdate(); con = getDataSource().getConnection(); con.setAutoCommit(false); Contact c = cw.getContact(); try { List notes = c.getNotes(); int i = notes.size() - 1; note = notes.get(i).getPropertyValueAsString(); } catch (Exception e) { // log.error(e.getMessage(),e); } if(note==null) { note=""; } personalDetail = c.getPersonalDetail(); businessDetail = c.getBusinessDetail(); name = c.getName(); id_contact = Long.parseLong(cw.getId()); if (personalDetail != null) { emails.addAll(personalDetail.getEmails()); phones.addAll(personalDetail.getPhones()); birthday = personalDetail.getBirthday(); category = stringFrom(c.getCategories()); imagePhoto = personalDetail.getPhotoObject().getImage(); persoAddress = personalDetail.getAddress(); } if (businessDetail != null) { emails.addAll(businessDetail.getEmails()); phones.addAll(businessDetail.getPhones()); businAddress = businessDetail.getAddress(); } if (name != null) { firstName = stringFrom(name.getFirstName()); lastName = stringFrom(name.getLastName()); alias = stringFrom(name.getNickname()); } if (firstName != null && firstName.length() > SQL_FIRSTNAME_DIM) { firstName = firstName.substring(0, SQL_FIRSTNAME_DIM); } if (lastName != null && lastName.length() > SQL_LASTNAME_DIM) { lastName = lastName.substring(0, SQL_LASTNAME_DIM); } if (category != null && category.length() > SQL_CATEGORY_DIM) { category = category.substring(0, SQL_CATEGORY_DIM); } if (alias != null && alias.length() > SQL_NICK_DIM) { alias = alias.substring(0, SQL_NICK_DIM); } // Formata o nome completo do contato fullName = new StringBuffer(); if (firstName != null && firstName.length() > 0) fullName.append(truncate(firstName, SQL_FIRSTNAME_DIM)); if (lastName != null && lastName.length() > 0) { if (firstName != null && firstName.length() > 0 && lastName != null && lastName.length() > 0) fullName.append(" " + truncate(lastName, SQL_LASTNAME_DIM)); else fullName.append(truncate(lastName, SQL_LASTNAME_DIM)); } // Formata a data de aniversario do contato if ((birthday != null) && (!birthday.equals(""))) { anniversary = new java.sql.Date((new SimpleDateFormat( "yyyy-MM-dd").parse(birthday)).getTime()); } else { anniversary = null; } // Prepara os dados do contato para serem atualizados no banco de // dados queryUpdateFunPimContact = new StringBuffer(); queryUpdateFunPimContact.append(SQL_UPDATE_FNBL_PIM_CONTACT_BEGIN + SQL_FIELD_LAST_UPDATE + SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimContact.append(SQL_FIELD_FIRST_NAME + SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimContact.append(SQL_FIELD_LAST_NAME + SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimContact.append(SQL_FIELD_NAME + SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimContact.append(SQL_FIELD_BIRTHDAY + SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimContact.append(SQL_FIELD_CATEGORY + SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimContact.append(SQL_FIELD_PHOTO + SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimContact.append(SQL_FIELD_NOTES + SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimContact.append(SQL_FIELD_NICK + SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimContact .append(SQL_FIELD_STATUS + SQL_EQUALS_QUESTIONMARK + SQL_UPDATE_FNBL_PIM_CONTACT_END); ps = con.prepareStatement(queryUpdateFunPimContact.toString()); ps.setLong(1, lastUpdate.getTime()); // last_update ps.setString(2, firstName); // given_names ps.setString(3, lastName); // family_names ps.setString(4, fullName.toString()); // names_ordered ps.setDate(5, anniversary); // birthdate ps.setString(6, category); // category ps.setBytes(7, imagePhoto); // photo ps.setString(8, note); // notes ps.setString(9, alias); // alias ps.setString(10, String.valueOf(Def.PIM_STATE_UPDATED)); // last_status ps.setLong(11, id_contact); // id_contact ps.setLong(12, Long.parseLong(userId)); // id_owner ps.executeUpdate(); // emails // Apaga os emails do contato na tabela phpgw_cc_connections ps = con .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)"); ps.setLong(1, id_contact); ps.executeUpdate(); // Apaga as referencias de emails do contato na tabela // phpgw_cc_contact_conns ps = con .prepareStatement("DELETE FROM phpgw_cc_contact_conns WHERE id_contact = ? AND id_typeof_contact_connection = 1"); ps.setLong(1, id_contact); ps.executeUpdate(); if (!emails.isEmpty()) { boolean type_mail = true; for (int i = 0, l = emails.size(); i < l; i++) { email = emails.get(i); if ((FIELD_EMAIL_1_ADDRESS).equals(email.getEmailType())) { type = TYPE_EMAIL_1_ADDRESS; type_mail = true; } else if ((FIELD_EMAIL_2_ADDRESS).equals(email .getEmailType())) { type = TYPE_EMAIL_2_ADDRESS; type_mail = false; } else { continue; // no save unknown property } String emailValue = email.getPropertyValueAsString(); emailValue = truncate(emailValue, SQL_EMAIL_DIM); if (emailValue != null && emailValue.length() != 0) { // Verifica o id maximo da tabela phpgw_cc_connections ps = con.prepareStatement(SQL_ID_MAX_CONNECTIONS); rs = ps.executeQuery(); if (rs.next()) { long id = rs.getInt(1); id_connection = (id == 0) ? 1 : ++id; } // Insere os emails do contato na tabela // phpgw_cc_connections ps = con.prepareStatement(SQL_INSERT_INTO_CONTACT_ITEM); ps.setLong(1, id_connection); ps.setString(2, type); ps.setString(3, emailValue); ps.setBoolean(4, type_mail); ps.executeUpdate(); // Insere as referencias de emails do contato na tabela // phpgw_cc_connections ps = con .prepareStatement(SQL_INSERT_INTO_FNBL_CONNECTIONS); ps.setLong(1, id_contact); ps.setLong(2, id_connection); ps.setLong(3, 1); ps.executeUpdate(); } } } // phones // Apaga os telefones do contato na tabela phpgw_cc_connections ps = con .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)"); ps.setLong(1, id_contact); ps.executeUpdate(); // Apaga as referencias de telefones do contato na tabela // phpgw_cc_contact_conns ps = con .prepareStatement("DELETE FROM phpgw_cc_contact_conns WHERE id_contact = ? AND id_typeof_contact_connection = 2"); ps.setLong(1, id_contact); ps.executeUpdate(); if (!phones.isEmpty()) { boolean type_fone = true; // Seta o primeiro telefone como // principal no expresso for (int i = 0, l = phones.size(); i < l; i++) { phone = phones.get(i); phoneType = phone.getPhoneType(); if ((FIELD_PRIMARY_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_PRIMARY_TELEPHONE_NUMBER; } else if ((FIELD_HOME_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_HOME_TELEPHONE_NUMBER; } else if ((FIELD_MOBILE_TELEPHONE_NUMBER) .equals(phoneType)) { type = TYPE_MOBILE_TELEPHONE_NUMBER; } else if ((FIELD_BUSINESS_TELEPHONE_NUMBER) .equals(phoneType)) { type = TYPE_BUSINESS_TELEPHONE_NUMBER; } else if ((FIELD_BUSINESS_FAX_NUMBER).equals(phoneType)) { type = TYPE_BUSINESS_FAX_NUMBER; } else if ((FIELD_PAGER_NUMBER).equals(phoneType)) { type = TYPE_PAGER_NUMBER; } else { continue; // Unknown property: saves nothing } String phoneValue = phone.getPropertyValueAsString(); phoneValue = truncate(phoneValue, SQL_EMAIL_DIM); if (phoneValue != null && phoneValue.length() != 0) { // Verifica o id maximo da tabela phpgw_cc_connections ps = con.prepareStatement(SQL_ID_MAX_CONNECTIONS); rs = ps.executeQuery(); if (rs.next()) { long id = rs.getInt(1); id_connection = (id == 0) ? 1 : ++id; } // Insere os telefones do contato na tabela // phpgw_cc_connections ps = con.prepareStatement(SQL_INSERT_INTO_CONTACT_ITEM); ps.setLong(1, id_connection); ps.setString(2, type); ps.setString(3, phoneValue); ps.setBoolean(4, type_fone); ps.executeUpdate(); type_fone = false; // os demais telefones serao setados // como nao padrao // Insere as referencias de telefones do contato na // tabela phpgw_cc_connections ps = con .prepareStatement(SQL_INSERT_INTO_FNBL_CONNECTIONS); ps.setLong(1, id_contact); ps.setLong(2, id_connection); ps.setLong(3, 2); ps.executeUpdate(); } } } // endereco boolean syncAddress = true; if(syncAddress) { // Apaga os contatos na tabela phpgw_cc_addresses ps = con .prepareStatement("DELETE FROM phpgw_cc_addresses WHERE id_address IN (SELECT id_address FROM phpgw_cc_contact_addrs WHERE id_contact = ?)"); ps.setLong(1, id_contact); ps.executeUpdate(); // Apaga os contatos na tabela phpgw_cc_contact_addrs ps = con .prepareStatement("DELETE FROM phpgw_cc_contact_addrs WHERE id_contact = ?"); ps.setLong(1, id_contact); ps.executeUpdate(); // Para cadas tipo de endereco RESIDENCIAL | COMERCIAL for (TypeAddress typeAddress: TypeAddress.values()) { Address address = null; boolean defaultAddress = false; // se ambos sao nulos não faz nada aqui if (persoAddress == null && businAddress == null) { break; } if (typeAddress.equals(TypeAddress.RESIDENCIAL)) { address=persoAddress; defaultAddress = true; } else if (typeAddress.equals(TypeAddress.COMERCIAL)) { address=businAddress; } // Pula para o proximo if(address == null) { continue; } String addressValue = address.getStreet().getPropertyValueAsString(); addressValue = truncate(addressValue, SQL_ADDRESS_DIM); if (addressValue != null && addressValue.length() != 0) { // Verifica o id maximo da tabela phpgw_cc_addresses ps = con.prepareStatement(SQL_ID_MAX_ADDRESS); rs = ps.executeQuery(); long id_address = 0; if (rs.next()) { long id = rs.getInt(1); id_address = (id == 0) ? 1 : ++id; } // Insere o endereco do contato na tabela String idCountry; try { idCountry = Country.getCountryByName(address .getCountry().getPropertyValueAsString()).getCode(); } catch (Exception e) { idCountry = "BR"; } long idState = searchStateId(con, address.getState().getPropertyValueAsString(), idCountry); long idCity = searchCityId(con, address.getCity().getPropertyValueAsString(), idState, idCountry); ps = con.prepareStatement(SQL_INSERT_ADDRESS); ps.setLong(1, id_address); ps.setLong(2, idCity); ps.setLong(3, idState); ps.setString(4, idCountry); ps.setString(5, addressValue); ps.setString(6, address.getExtendedAddress().getPropertyValueAsString()); ps.setString(7, address.getPostalCode().getPropertyValueAsString()); ps.setBoolean(8, defaultAddress); ps.executeUpdate(); ps = con.prepareStatement(SQL_INSERT_CONTACT_ADDRESS); ps.setLong(1, id_contact); ps.setLong(2, id_address); ps.setInt(3, typeAddress.getId()); ps.executeUpdate(); } } } con.commit(); con.setAutoCommit(true); } catch (Exception e) { throw new PIMDBAccessException("\n=> Error updating contact.\n", e); } finally { DBTools.close(con, ps, rs); } log.info("\n\n=> Update item with ID " + id_contact + "\n"); log.info("\n\n=> PIMContactDAO updateItem end\n"); return Long.toString(id_contact); } private long searchStateId(Connection con, String stateName, String idCountry) throws Exception { PreparedStatement ps = null; ResultSet rs = null; long stateId = 0; try { String SQL_GET_STATE = SQL_GET_STATE_ITEM + " where upper(" + SQL_FIELD_STATE_NAME + ")" + SQL_EQUALS_QUESTIONMARK + " and " + SQL_FIELD_COUNTRY_ID + SQL_EQUALS_QUESTIONMARK; ps = con.prepareStatement(SQL_GET_STATE); ps.setString(1, stateName.toUpperCase()); ps.setString(2, idCountry); rs = ps.executeQuery(); if (rs.next()) { stateId = rs.getInt(SQL_FIELD_STATE_ID); return stateId; } } catch (Exception e) { e.printStackTrace(); } ps = con.prepareStatement(SQL_ID_MAX_STATE); rs = ps.executeQuery(); if (rs.next()) { long id = rs.getInt(1); stateId = (id == 0) ? 1 : ++id; } else { return 0; } ps = con.prepareStatement(SQL_INSERT_STATE); ps.setLong(1, stateId); ps.setString(2, idCountry); ps.setString(3, stateName); ps.setString(4, stateName.substring(0, 2)); if(ps.executeUpdate() > 0) { return stateId; } return 0; } private long searchCityId(Connection con, String cityName, long idState, String idCountry) throws Exception { PreparedStatement ps = null; ResultSet rs = null; long cityId = 0; try { String SQL_GET_CITY = SQL_GET_CITY_ITEM + " where " + SQL_FIELD_STATE_ID + SQL_EQUALS_QUESTIONMARK + " and " + SQL_FIELD_COUNTRY_ID + SQL_EQUALS_QUESTIONMARK + " and upper(" + SQL_FIELD_CITY_NAME + ")" + SQL_EQUALS_QUESTIONMARK; ps = con.prepareStatement(SQL_GET_CITY); ps.setLong(1, idState); ps.setString(2, idCountry); ps.setString(3, cityName.toUpperCase()); rs = ps.executeQuery(); if (rs.next()) { cityId = rs.getInt(SQL_FIELD_CITY_ID); return cityId; } } catch (Exception e) { e.printStackTrace(); } ps = con.prepareStatement(SQL_ID_MAX_CITY); rs = ps.executeQuery(); if (rs.next()) { long id = rs.getInt(1); cityId = (id == 0) ? 1 : ++id; } else { return 0; } ps = con.prepareStatement(SQL_INSERT_CITY); ps.setLong(1, cityId); ps.setLong(2, idState); ps.setString(3, idCountry); ps.setNull(4, 0); ps.setString(5, null); ps.setString(6, cityName); if(ps.executeUpdate() > 0) { return cityId; } return 0; } /** * Removes the contact with given UID and sets its last_update field, * provided it has the same userId as this DAO. The deletion is soft * (reversible). * * @param uid * corresponds to the id field in the phpgw_cc_contact table * @throws PIMDBAccessException */ public void removeItem(String uid) throws PIMDBAccessException { log.info("\n\n=> DAO start removeItem " + uid + "\n"); Connection con = null; PreparedStatement ps = null; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); con.setAutoCommit(false); ps = con .prepareStatement("DELETE FROM phpgw_cc_addresses WHERE id_address IN (SELECT id_address FROM phpgw_cc_contact_addrs WHERE id_contact = ?)"); ps.setLong(1, Long.parseLong(uid)); ps.executeUpdate(); ps = con .prepareStatement("DELETE FROM phpgw_cc_contact_addrs WHERE id_contact = ?"); ps.setLong(1, Long.parseLong(uid)); ps.executeUpdate(); ps = con .prepareStatement("DELETE FROM phpgw_cc_connections WHERE id_connection IN (SELECT id_connection FROM phpgw_cc_contact_conns WHERE id_contact = ?)"); ps.setLong(1, Long.parseLong(uid)); ps.executeUpdate(); ps = con .prepareStatement("DELETE FROM phpgw_cc_contact_conns WHERE id_contact = ?"); ps.setLong(1, Long.parseLong(uid)); ps.executeUpdate(); ps = con .prepareStatement("DELETE FROM phpgw_cc_contact WHERE id_contact = ?"); ps.setLong(1, Long.parseLong(uid)); ps.executeUpdate(); con.commit(); con.setAutoCommit(true); } catch (Exception e) { e.printStackTrace(); throw new PIMDBAccessException("\nError deleting contact.\n", e); } finally { DBTools.close(con, ps, null); } } /** * Removes a contact, provided it has the same userId as this DAO. The * deletion is soft (reversible). * * @param contact * whence the UID and the last update Date are extracted * @throws PIMDBAccessException */ public void removeItem(ContactWrapper contact) throws PIMDBAccessException { removeItem(contact.getId()); } /** * Removes all contacts, provided it has the same userId as this DAO. The * deletion is soft (reversible). * * @throws PIMDBAccessException */ public void removeAllItems() throws PIMDBAccessException { log.info("\n\n =>DAO start removeAllItems\n"); Connection con = null; PreparedStatement ps = null; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); con.setAutoCommit(false); ps = con .prepareStatement("DELETE FROM phpgw_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 = ?))"); ps.setLong(1, Long.parseLong(userId)); ps.executeUpdate(); ps = con .prepareStatement("DELETE FROM phpgw_cc_contact_addrs WHERE id_contact IN(SELECT id_contact FROM phpgw_cc_contact WHERE id_owner = ?)"); ps.setLong(1, Long.parseLong(userId)); ps.executeUpdate(); ps = con .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 = ?))"); ps.setLong(1, Long.parseLong(userId)); ps.executeUpdate(); ps = con .prepareStatement("DELETE FROM phpgw_cc_contact_conns WHERE id_contact IN(SELECT id_contact FROM phpgw_cc_contact WHERE id_owner = ?)"); ps.setLong(1, Long.parseLong(userId)); ps.executeUpdate(); ps = con .prepareStatement("DELETE FROM phpgw_cc_contact WHERE id_owner = ?"); ps.setLong(1, Long.parseLong(userId)); ps.executeUpdate(); con.commit(); con.setAutoCommit(true); } catch (Exception e) { throw new PIMDBAccessException("\n=> Error deleting contacts.\n", e); } finally { DBTools.close(con, ps, null); } } /** * Retrieves the UID list of all contacts belonging to the user. * * @throws PIMDBAccessException * @return a List of UIDs (as String objects) */ public List getAllItems() throws PIMDBAccessException { log.info("\n\n=> PIMContactDAO getAllItems begin\n"); Connection con = null; PreparedStatement ps = null; List contacts = new ArrayList(); ResultSet rs = null; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); ps = con.prepareStatement(SQL_GET_CONTACT_ID_LIST_BY_USER + SQL_ORDER_BY_ID); ps.setLong(1, Long.parseLong(userId)); rs = ps.executeQuery(); while (rs.next()) { contacts.add(Long.toString(rs.getLong(1))); // It's the first // and only column } } catch (Exception e) { e.printStackTrace(); throw new PIMDBAccessException("\n=> Error listing contacts.\n", e); } finally { DBTools.close(con, ps, rs); } log.info("\n\n=> PIMContactDAO getAllItems end\n"); return contacts; } /** * Gets the contact with given UID, provided it has the same userId as this * DAO. * * @param uid * corresponds to the id field in the fnbl_pim_contact table * @throws PIMDBAccessException * @return the contact as a ContactWrapper object. */ public ContactWrapper getItem(String uid) throws PIMDBAccessException { log.info("\n\n=> DAO start getItem " + uid + "\n"); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; ContactWrapper c; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); ps = con.prepareStatement(SQL_GET_CONTACT_BY_ID_USER); ps.setLong(1, Long.parseLong(uid)); ps.setLong(2, Long.parseLong(userId)); rs = ps.executeQuery(); c = createContact(uid, rs); ps = con.prepareStatement(SQL_GET_CONTACT_ITEM_BY_ID); ps.setLong(1, Long.parseLong(uid)); rs = ps.executeQuery(); try { addPIMContactItems(c, rs); } catch (SQLException sqle) { throw new SQLException( "\nError while adding extra PIM contact " + "information.\n" + sqle.getMessage(), sqle .getSQLState()); } try { ps = con.prepareStatement(SQL_GET_CONTACT_ADDRESS_ITEM); ps.setLong(1, Long.parseLong(uid)); ps.setLong(2, Long.parseLong(userId)); ps.setInt(3, TypeAddress.RESIDENCIAL.getId()); rs = ps.executeQuery(); Address address = c.getContact().getPersonalDetail().getAddress(); addAddress(address, rs); } catch (Exception e) { throw new SQLException("\nError while adding personal address information"); } try { ps = con.prepareStatement(SQL_GET_CONTACT_ADDRESS_ITEM); ps.setLong(1, Long.parseLong(uid)); ps.setLong(2, Long.parseLong(userId)); ps.setInt(3, TypeAddress.COMERCIAL.getId()); rs = ps.executeQuery(); Address address = c.getContact().getBusinessDetail().getAddress(); addAddress(address, rs); } catch (Exception e) { throw new SQLException("\nError while adding business address information"); } } catch (Exception e) { e.printStackTrace(); throw new PIMDBAccessException("\n=> Error seeking contact.\n", e); } finally { DBTools.close(con, ps, rs); } return c; } private static void addAddress(Address address, ResultSet rs) throws Exception { ResultSetMetaData rsmd = rs.getMetaData(); String column = null; int columnCount = 0; while (rs.next()) { columnCount = rsmd.getColumnCount(); if (rs.getString(SQL_FIELD_ADDRESS1)==null || (rs.getString(SQL_FIELD_ADDRESS1)!=null && "".equals(rs.getString(SQL_FIELD_ADDRESS1).trim()))) { break; } for (int i = 1; i <= columnCount; ++i) { column = rsmd.getColumnName(i); if (SQL_FIELD_TYPEOF.equalsIgnoreCase(column)) { continue; } else if (SQL_FIELD_COMPLEMENT.equalsIgnoreCase(column)) { address.getExtendedAddress().setPropertyValue(rs.getString(i)); } else if (SQL_FIELD_ADDRESS1.equalsIgnoreCase(column)) { address.getStreet().setPropertyValue(rs.getString(i)); } else if (SQL_FIELD_POSTALCODE.equalsIgnoreCase(column)) { address.getPostalCode().setPropertyValue(rs.getString(i)); } else if (SQL_FIELD_CITY_NAME.equalsIgnoreCase(column)) { address.getCity().setPropertyValue(rs.getString(i)); } else if (SQL_FIELD_STATE_NAME.equalsIgnoreCase(column)) { address.getState().setPropertyValue(rs.getString(i)); } else if (SQL_FIELD_COUNTRY_ID.equalsIgnoreCase(column)) { Country country = Country.getCountryByCode(rs.getString(i)); address.getCountry().setPropertyValue(country.getName()); } else { throw new SQLException("\n=> Unhandled column: " + column); } } } } /** * Retrieves the UID list of the contacts considered to be "twins" of a * given contact. * * @param c * the Contact object representing the contact whose twins need * be found. In the present implementation, only the following * data matter: *
    *
  • first name *
  • last name *
* @throws PIMDBAccessException * @return a List of UIDs (as String objects) that may be empty but not null */ public List getTwinItems(Contact c) throws PIMDBAccessException { log.info("\n\n=> PIMContactDAO getTwinItems begin\n"); List twins = new ArrayList(); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { String firstName = c.getName().getFirstName() .getPropertyValueAsString(); String lastName = c.getName().getLastName() .getPropertyValueAsString(); // Looks up the data source when the first connection is created con = getDataSource().getConnection(); if (firstName == null || ("null".equals(firstName))) { firstName = ""; } if (lastName == null || ("null".equals(lastName))) { lastName = ""; } String fnSearch = (firstName.length() == 0 ? "" : firstName); String lnSearch = (lastName.length() == 0 ? "" : lastName); StringBuilder sb = new StringBuilder(100); sb.append("\n\nLooking for items having: ") .append("\n> first name : '") .append(fnSearch).append('\'') .append("\n> last name : '") .append(lnSearch).append('\'').append("\n"); log.info(sb.toString()); ps = con.prepareStatement(SQL_GET_CONTACT_TWIN_ID_LIST + SQL_ORDER_BY_ID); ps.setLong(1, Long.parseLong(userId)); ps.setString(2, firstName); ps.setString(3, firstName); ps.setString(4, lastName); ps.setString(5, lastName); rs = ps.executeQuery(); long twinId; while (rs.next()) { twinId = rs.getLong(1); // The id is the first and only column log.info("\n\n=> Twin found: " + twinId + "\n"); twins.add(Long.toString(twinId)); } } catch (Exception e) { throw new PIMDBAccessException("\n=> Error retrieving twin.\n", e); } finally { DBTools.close(con, ps, rs); } log.info("\n\n=> PIMContactDAO getTwinItems end\n"); return twins; } /** * Retrieves the state of the given item, provided it's been modified after * a certain moment. * * @param uid * the UID of the item to be checked (as a String object) * @param since * the Timestamp that the item's lastUpdate field is checked * against: if the item has been modified before that moment, an * "unchanged" state marker is returned * @throws PIMDBAccessException * @return a char identifying either one of the 3 standard states ("new", * "deleted", "updated") or the special "unchanged" status, all of * them as defined in com.funambol.pim.util.Def */ public char getItemState(String uid, Timestamp since) throws PIMDBAccessException { log.info("\n\n=> DAO start getItemState\n"); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; char status; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); ps = con.prepareStatement(SQL_GET_STATUS_BY_ID_USER_TIME); ps.setLong(1, Long.parseLong(uid)); ps.setLong(2, Long.parseLong(userId)); ps.setLong(3, since.getTime()); rs = ps.executeQuery(); if (!rs.next()) { status = Def.PIM_STATE_UNCHANGED; log.info("\n\n=> Item " + uid + "'s status wasn't retrieved " + "because the item hasn't been modified since " + since + "\n"); } else { status = rs.getString(1).charAt(0); log.info("\n\n=> Item " + uid + " has status " + status + "\n"); } } catch (Exception e) { throw new PIMDBAccessException( "\n=> Error retrieving item state.\n", e); } finally { DBTools.close(con, ps, rs); } return status; } // ---------------------------------------------------------- Private // methods /** * Creates a ContactWrapper object from a ResultSet. Only the basic data are * set. * * @param wrapperId * the UID of the wrapper object to be returned * @param rs * the result of the execution of a proper SQL SELECT statement * on the phpgw_cc_contact table, with the cursor before its * first row * @return a newly created ContactWrapper initialized with the fields in the * result set * @throws java.sql.SQLException * @throws NotFoundException */ private static ContactWrapper createContact(String wrapperId, ResultSet rs) throws SQLException, NotFoundException { if (!rs.next()) { throw new NotFoundException("\n\n=> No contact found.\n"); } ResultSetMetaData rsmd = rs.getMetaData(); ContactWrapper cw = null; String column = null; String uid = null; String userId = null; uid = String.valueOf(rs.getLong(SQL_FIELD_ID)); userId = rs.getString(SQL_FIELD_USERID); Contact c = new Contact(); cw = new ContactWrapper(wrapperId, userId, c); int columnCount = rsmd.getColumnCount(); for (int i = 1; i <= columnCount; ++i) { column = rsmd.getColumnName(i); // General if (SQL_FIELD_ID.equalsIgnoreCase(column)) { // Does nothing: field already set at construction time } else if (SQL_FIELD_LAST_UPDATE.equalsIgnoreCase(column)) { cw.setLastUpdate(new Timestamp(rs.getLong(i))); } else if (SQL_FIELD_USERID.equalsIgnoreCase(column)) { // Does nothing: field already set at construction time } else if (SQL_FIELD_STATUS.equalsIgnoreCase(column)) { cw.setStatus(rs.getString(i).charAt(0)); } else if (SQL_FIELD_FIRST_NAME.equalsIgnoreCase(column)) { c.getName().getFirstName().setPropertyValue(rs.getString(i)); } else if (SQL_FIELD_LAST_NAME.equalsIgnoreCase(column)) { c.getName().getLastName().setPropertyValue(rs.getString(i)); } else if (SQL_FIELD_BIRTHDAY.equalsIgnoreCase(column)) { c.getPersonalDetail().setBirthday(rs.getString(i)); } else if (SQL_FIELD_CATEGORY.equalsIgnoreCase(column)) { c.getCategories().setPropertyValue(rs.getString(i)); } else if (SQL_FIELD_PHOTO.equalsIgnoreCase(column)) { if(c.getPersonalDetail()==null) { PersonalDetail pd = new PersonalDetail(); c.setPersonalDetail(pd); } Photo photo = new Photo(); photo.setImage(rs.getBytes(i)); c.getPersonalDetail().setPhotoObject(photo); } else if (SQL_FIELD_NOTES.equalsIgnoreCase(column)) { try { if(rs.getString(i) != null) { if (c.getNotes() == null || (c.getNotes() != null && c.getNotes().size() != 1) || (c.getNotes() != null && c.getNotes().size() == 1 && c.getNotes() .get(0) == null)) { List l = new ArrayList(1); l.add(new Note()); c.setNotes(l); } Note n = (Note) c.getNotes().get(0); n.setPropertyValue(rs.getString(i)); n.setPropertyType("Body"); // HARD CODE } } catch (Exception e) { log.error(e.getMessage(),e); } } else if (SQL_FIELD_NICK.equalsIgnoreCase(column)) { c.getName().getNickname().setPropertyValue(rs.getString(i)); } else { throw new SQLException("\n=> Unhandled column: " + column); } } return cw; } /** * Attaches extra information to a contact on the basis of a ResultSet. * * @param c * the contact still lacking extra information * @param rs * the result of the execution of a proper SQL SELECT statement * on the phpgw_cc_connections and phpgw_cc_contact_conns table, * with the cursor before its first row * @return the ContactWrapper object with extra information attached * @throws java.sql.SQLException */ private static ContactWrapper addPIMContactItems(ContactWrapper cw, ResultSet rs) throws SQLException { ResultSetMetaData rsmd = rs.getMetaData(); Contact c = cw.getContact(); String type = null; String value = null; String name = null; String column = null; int columnCount = 0; Phone phone; Email email; while (rs.next()) { columnCount = rsmd.getColumnCount(); for (int i = 1; i <= columnCount; ++i) { column = rsmd.getColumnName(i); if (SQL_FIELD_ITEM_NAME.equalsIgnoreCase(column)) { name = rs.getString(i); } else if (SQL_FIELD_ITEM_VALUE.equalsIgnoreCase(column)) { value = rs.getString(i); } else if (SQL_FIELD_ITEM_TYPE.equalsIgnoreCase(column)) { type = rs.getString(i); } else { throw new SQLException("\n=> Unhandled column: " + column); } } if (type.equals("1")) { // email if (TYPE_EMAIL_1_ADDRESS.equalsIgnoreCase(name)) { email = new Email(); email.setEmailType(FIELD_EMAIL_1_ADDRESS); email.setPropertyValue(value); c.getPersonalDetail().addEmail(email); } else if (TYPE_EMAIL_2_ADDRESS.equalsIgnoreCase(name)) { email = new Email(); email.setEmailType(FIELD_EMAIL_2_ADDRESS); email.setPropertyValue(value); c.getPersonalDetail().addEmail(email); } } if (type.equals("2")) { // telefone if (TYPE_PRIMARY_TELEPHONE_NUMBER.equalsIgnoreCase(name)) { phone = new Phone(); phone.setPhoneType(FIELD_PRIMARY_TELEPHONE_NUMBER); phone.setPropertyValue(value); c.getBusinessDetail().addPhone(phone); } else if (TYPE_HOME_TELEPHONE_NUMBER.equalsIgnoreCase(name)) { phone = new Phone(); phone.setPhoneType(FIELD_HOME_TELEPHONE_NUMBER); phone.setPropertyValue(value); c.getPersonalDetail().addPhone(phone); } else if (TYPE_MOBILE_TELEPHONE_NUMBER.equalsIgnoreCase(name)) { phone = new Phone(); phone.setPhoneType(FIELD_MOBILE_TELEPHONE_NUMBER); phone.setPropertyValue(value); c.getPersonalDetail().addPhone(phone); } else if (TYPE_BUSINESS_TELEPHONE_NUMBER .equalsIgnoreCase(name)) { phone = new Phone(); phone.setPhoneType(FIELD_BUSINESS_TELEPHONE_NUMBER); phone.setPropertyValue(value); c.getBusinessDetail().addPhone(phone); } else if (TYPE_BUSINESS_FAX_NUMBER.equalsIgnoreCase(name)) { phone = new Phone(); phone.setPhoneType(FIELD_BUSINESS_FAX_NUMBER); phone.setPropertyValue(value); c.getBusinessDetail().addPhone(phone); } else if (TYPE_PAGER_NUMBER.equalsIgnoreCase(name)) { phone = new Phone(); phone.setPhoneType(FIELD_PAGER_NUMBER); phone.setPropertyValue(value); c.getBusinessDetail().addPhone(phone); } } } return cw; } /** * Retrieves the UID list of the contacts belonging to the user filtered * according to the given time interval and status. * * @param since * the earliest allowed last-update Timestamp * @param to * the latest allowed last-update Timestamp * @param status * 'D' for deleted items, 'N' for new items, 'U' for updated * items * @throws PIMDBAccessException * @return a List of UIDs (as String objects) */ protected List getItemsHavingStatus(Timestamp since, Timestamp to, char status) throws PIMDBAccessException { log.info("\n\n=> Seeking '" + status + "' items " + "in time interval (" + since + "; " + to + ")\n"); Connection con = null; PreparedStatement ps = null; List contacts = new ArrayList(); ResultSet rs = null; try { // Looks up the data source when the first connection is created con = getDataSource().getConnection(); if (status == 'D') { ps = con .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"); ps.setLong(1, Long.parseLong(userId)); ps.setLong(2, this.principal.getId()); rs = ps.executeQuery(); } else { ps = con .prepareStatement(SQL_GET_FNBL_PIM_CONTACT_ID_LIST_BY_USER_TIME_STATUS + SQL_ORDER_BY_ID); ps.setLong(1, Long.parseLong(userId)); ps.setLong(2, since.getTime()); ps.setLong(3, to.getTime()); ps.setString(4, String.valueOf(status)); rs = ps.executeQuery(); } while (rs.next()) { contacts.add(Long.toString(rs.getLong(1))); // It's the first // and only column log.info("\n\n=> Item found " + rs.getLong(1) + "\n"); } } catch (Exception e) { e.printStackTrace(); throw new PIMDBAccessException("\n=> Error listing contacts.\n", e); } finally { DBTools.close(con, ps, rs); } return contacts; } }