- Timestamp:
- 10/28/09 20:58:21 (15 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
contrib/psync/src/main/java/br/com/prognus/psync/items/dao/PIMContactDAO.java
r1103 r1545 32 32 import br.com.prognus.psync.exception.PIMDBAccessException; 33 33 import br.com.prognus.psync.items.model.ContactWrapper; 34 import br.com.prognus.psync.util.Country; 34 35 import br.com.prognus.psync.util.Def; 35 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; 36 40 import com.funambol.common.pim.contact.BusinessDetail; 37 41 import com.funambol.common.pim.contact.Contact; 38 42 import com.funambol.common.pim.contact.Email; 39 43 import com.funambol.common.pim.contact.Name; 44 import com.funambol.common.pim.contact.Note; 40 45 import com.funambol.common.pim.contact.PersonalDetail; 41 46 import com.funambol.common.pim.contact.Phone; 47 import com.funambol.common.pim.contact.Photo; 42 48 import com.funambol.framework.security.Sync4jPrincipal; 43 49 import com.funambol.framework.server.store.NotFoundException; … … 55 61 + "WHERE id_owner = ?"; 56 62 57 private static final String SQL_GET_CONTACT_BY_ID_USER = "SELECT id_contact, id_owner, last_update, last_status, given_names, family_names, birthdate, category FROM phpgw_cc_contact WHERE id_contact = ? AND id_owner = ? LIMIT 1";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"; 58 64 59 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 = ?"; … … 73 79 74 80 private static final String SQL_INSERT_INTO_FNBL_PIM_CONTACT = "INSERT INTO phpgw_cc_contact " 75 + "(id_contact, id_owner, id_status, given_names, family_names, names_ordered, birthdate, category, last_update, last_status ) "76 + "VALUES " + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";81 + "(id_contact, id_owner, id_status, given_names, family_names, names_ordered, birthdate, category, photo, notes, alias, last_update, last_status ) " 82 + "VALUES " + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "; 77 83 78 84 private static final String SQL_INSERT_INTO_CONTACT_ITEM = "INSERT INTO phpgw_cc_connections " … … 87 93 88 94 private static final String SQL_UPDATE_FNBL_PIM_CONTACT_END = " WHERE id_contact = ? AND id_owner = ? "; 89 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 90 119 private static final String SQL_EQUALS_QUESTIONMARK = " = ?"; 91 120 … … 111 140 112 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"; 113 146 114 147 protected static final int SQL_FIRSTNAME_DIM = 49; … … 121 154 122 155 protected static final int SQL_CATEGORY_DIM = 20; 156 157 protected static final int SQL_NICK_DIM = 30; 123 158 124 159 protected static final String TYPE_EMAIL_1_ADDRESS = "Principal"; … … 159 194 160 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"; 161 214 162 215 private Sync4jPrincipal principal = null; … … 193 246 * @see ContactWrapper 194 247 */ 248 @SuppressWarnings("unchecked") 195 249 public void addItem(ContactWrapper cw) throws PIMDBAccessException { 196 250 … … 223 277 StringBuffer fullName = null; 224 278 Date anniversary = null; 279 byte[] imagePhoto = null; 280 String note = null; 281 String alias = null; 225 282 226 283 try { … … 232 289 Timestamp lastUpdate = cw.getLastUpdate(); 233 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 234 302 personalDetail = c.getPersonalDetail(); 235 303 businessDetail = c.getBusinessDetail(); … … 244 312 birthday = personalDetail.getBirthday(); 245 313 category = stringFrom(c.getCategories()); 314 imagePhoto = personalDetail.getPhotoObject().getImage(); 246 315 } 247 316 … … 254 323 firstName = stringFrom(name.getFirstName()); 255 324 lastName = stringFrom(name.getLastName()); 325 alias = stringFrom(name.getNickname()); 256 326 } 257 327 … … 297 367 ps.setDate(7, anniversary); // birthdate 298 368 ps.setString(8, truncate(category, SQL_CATEGORY_DIM)); // category 299 ps.setLong(9, lastUpdate.getTime()); // last_update 300 ps.setString(10, String.valueOf(Def.PIM_STATE_NEW)); // last_status 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 301 374 ps.executeUpdate(); 302 375 … … 483 556 String category = null; 484 557 Date anniversary = null; 558 byte[] imagePhoto = null; 559 String note = null; 560 String alias = null; 561 Address persoAddress = null; 562 Address businAddress = null; 485 563 486 564 try { … … 495 573 Contact c = cw.getContact(); 496 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 497 586 personalDetail = c.getPersonalDetail(); 498 587 businessDetail = c.getBusinessDetail(); … … 505 594 birthday = personalDetail.getBirthday(); 506 595 category = stringFrom(c.getCategories()); 596 imagePhoto = personalDetail.getPhotoObject().getImage(); 597 persoAddress = personalDetail.getAddress(); 507 598 } 508 599 … … 510 601 emails.addAll(businessDetail.getEmails()); 511 602 phones.addAll(businessDetail.getPhones()); 603 businAddress = businessDetail.getAddress(); 512 604 } 513 605 … … 515 607 firstName = stringFrom(name.getFirstName()); 516 608 lastName = stringFrom(name.getLastName()); 609 alias = stringFrom(name.getNickname()); 517 610 } 518 611 … … 525 618 if (category != null && category.length() > SQL_CATEGORY_DIM) { 526 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); 527 623 } 528 624 … … 563 659 queryUpdateFunPimContact.append(SQL_FIELD_CATEGORY 564 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); 565 667 queryUpdateFunPimContact 566 668 .append(SQL_FIELD_STATUS + SQL_EQUALS_QUESTIONMARK … … 575 677 ps.setDate(5, anniversary); // birthdate 576 678 ps.setString(6, category); // category 577 ps.setString(7, String.valueOf(Def.PIM_STATE_UPDATED)); // last_status 578 ps.setLong(8, id_contact); // id_contact 579 ps.setLong(9, Long.parseLong(userId)); // id_owner 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 580 685 ps.executeUpdate(); 581 686 … … 730 835 } 731 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 } 732 921 con.commit(); 733 922 con.setAutoCommit(true); … … 743 932 744 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; 745 1029 } 746 1030 … … 954 1238 .getSQLState()); 955 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 } 956 1266 957 1267 } catch (Exception e) { … … 963 1273 964 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 } 965 1317 } 966 1318 … … 1169 1521 } else if (SQL_FIELD_CATEGORY.equalsIgnoreCase(column)) { 1170 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)); 1171 1554 } else { 1172 1555 throw new SQLException("\n=> Unhandled column: " + column);
Note: See TracChangeset
for help on using the changeset viewer.
