00001
00025 package org.openmobileis.examples.mycrm.data.jdbc;
00026
00027 import java.sql.Connection;
00028 import java.sql.DatabaseMetaData;
00029 import java.sql.ResultSet;
00030 import java.util.Calendar;
00031 import java.util.Date;
00032
00033 import org.openmobileis.common.user.UserNotFoundException;
00034 import org.openmobileis.common.util.collection.Array;
00035 import org.openmobileis.common.util.database.AbstractQueryManager;
00036 import org.openmobileis.common.util.exception.DatabaseException;
00037 import org.openmobileis.common.util.exception.ServiceException;
00038 import org.openmobileis.common.util.exception.SynchroException;
00039 import org.openmobileis.database.fastobjectdb.FastObjectDB;
00040 import org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncTarget;
00041 import org.openmobileis.examples.mycrm.data.Account;
00042 import org.openmobileis.examples.mycrm.data.Contact;
00043 import org.openmobileis.examples.mycrm.data.Report;
00044 import org.openmobileis.synchro.algo.replication.AlwaysUpdateServerSynchroConflicResolver;
00045 import org.openmobileis.synchro.algo.replication.SynchroAtomicObject;
00046 import org.openmobileis.synchro.algo.replication.SynchroConflicResolver;
00047 import org.openmobileis.synchro.algo.replication.utils.DefaultSynchroAtomicObject;
00048 import org.openmobileis.synchro.algo.replication.utils.SynchroAtomicObjectManager;
00049 import org.openmobileis.synchro.openmsp.OpenMSPException;
00050 import org.openmobileis.synchro.openmsp.client.db.DBImportFileCoder;
00051 import org.openmobileis.synchro.security.auth.Credential;
00052
00053
00054
00055
00056
00057
00058
00059
00060
00061 public final class MyCrmAccountSynchroTarget extends AbstractQueryManager implements FODBSyncTarget {
00062
00063 private static String queryCreateAccountTable = "CREATE TABLE ACCOUNT (" + " ID varchar(30) NOT NULL," + " NAME varchar(50) NOT NULL,"
00064 + " ADDRESS varchar(100) NOT NULL," + " CITY varchar(50) NOT NULL," + " ACTIVITY varchar(5) NOT NULL," + " CONSTRAINT idaccount PRIMARY KEY (ID)" + ")";
00065
00066 private static String queryCreateContactTable = "CREATE TABLE CONTACT (" + " ID varchar(30) NOT NULL," + " IDCLIENT varchar(30) NOT NULL,"
00067 + " FIRSTNAME varchar(50) NOT NULL," + " LASTNAME varchar(100) NOT NULL," + " FUNCTION int ," + " CONSTRAINT idcontact PRIMARY KEY (ID)" + ")";
00068
00069 private static String queryCreateLeadsTable = "CREATE TABLE LEADS (" + " ID varchar(30) NOT NULL," + " IDCLIENT varchar(30) NOT NULL," + " STATE int ,"
00070 + " AMOUNT int ," + " DATE bigint ," + " DESCRIPTION varchar(150) NOT NULL," + " CONSTRAINT idleads PRIMARY KEY (ID)" + ")";
00071
00072 private static String queryCreateReportTable = "CREATE TABLE REPORT ("
00073 + " ID varchar(30) NOT NULL,"
00074 + " IDCONTACT varchar(30) NOT NULL,"
00075 + " ACTION int ,"
00076 + " DATE bigint ,"
00077 + " DESCRIPTION varchar(150) NOT NULL,"
00078 + " TERMINALUSERID varchar(5) NOT NULL,"
00079 + " CONSTRAINT idreport PRIMARY KEY (ID)"
00080 + ")";
00081
00082 private static String queryInsertAccount = "INSERT INTO ACCOUNT (ID,NAME,ADDRESS,CITY,ACTIVITY )" + " VALUES ('%0%', '%1%', '%2%', '%3%', %4%)";
00083
00084
00085
00086 private static String queryDeleteAccount = "DELETE FROM ACCOUNT where ID='%0%'";
00087
00088 private static String queryGetAccount = "SELECT ID,NAME,ADDRESS,CITY,ACTIVITY from ACCOUNT where ID='%0%'";
00089
00090 private static String queryGetAllAccountId = "SELECT ID from ACCOUNT";
00091
00092 private static String queryInsertContact = "INSERT INTO CONTACT (ID,IDCLIENT,FIRSTNAME,LASTNAME,FUNCTION )" + " VALUES ('%0%', '%1%', '%2%', '%3%', %4%)";
00093
00094
00095
00096 private static String queryDeleteContact = "DELETE FROM CONTACT where IDCLIENT='%0%'";
00097
00098 private static String queryGetContact = "SELECT ID,IDCLIENT,FIRSTNAME,LASTNAME,FUNCTION from CONTACT where IDCLIENT='%0%'";
00099
00100 private static String queryInsertLeads = "INSERT INTO LEADS (ID,IDCLIENT,STATE,AMOUNT,DATE,DESCRIPTION )" + " VALUES ('%0%', '%1%', '%2%', %3%, %4%, '%5%')";
00101
00102
00103
00104
00105
00106
00107
00108 private static String queryInsertReport = "INSERT INTO REPORT (ID,IDCONTACT,ACTION,DATE,DESCRIPTION,TERMINALUSERID )" + " VALUES ('%0%', '%1%', %2%, %3%, '%4%', '%5%')";
00109
00110
00111
00112 private static String queryDeleteReport = "DELETE FROM REPORT where REPORT.IDCONTACT IN (SELECT CONTACT.ID FROM CONTACT WHERE CONTACT.IDCLIENT ='%0%')";
00113
00114 private static String queryGetReport = "SELECT ID,IDCONTACT,ACTION,DATE,DESCRIPTION,TERMINALUSERID from REPORT where IDCONTACT='%0%'";
00115
00119 public MyCrmAccountSynchroTarget() throws ServiceException {
00120 super();
00121 try {
00122 this.createSyncTable();
00123
00124 } catch (Throwable ex) {
00125 throw new ServiceException(ex);
00126 }
00127 }
00128
00136 public void setSendSynchroMetaData(Object metadata)throws OpenMSPException {
00137
00138 }
00139
00140
00141
00142
00143 private void createSyncTable() throws DatabaseException, java.sql.SQLException, SynchroException {
00144 try {
00145 Connection con = this.getDbManager().getConnection();
00146 DatabaseMetaData dbmd = con.getMetaData();
00147 String[] tableType = { "TABLE" };
00148 ResultSet result = dbmd.getTables(null, null, "ACCOUNT", tableType);
00149 if (!result.next()) {
00150 this.executeUpdate(MyCrmAccountSynchroTarget.queryCreateAccountTable, null);
00151 this.executeUpdate(MyCrmAccountSynchroTarget.queryCreateContactTable, null);
00152 this.executeUpdate(MyCrmAccountSynchroTarget.queryCreateLeadsTable, null);
00153 this.executeUpdate(MyCrmAccountSynchroTarget.queryCreateReportTable, null);
00154
00155 String[] parameters = { "1111", "Green fields LTD", "123 yellow grass", "London", "0" };
00156 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertAccount, parameters);
00157 SynchroAtomicObjectManager.getManager().updateAtomicObject(new DefaultSynchroAtomicObject("1111", ""), this.getCollectionName(), null);
00158 parameters = new String[] { "2222", "Blue Red", "23 Action Lake", "London", "1" };
00159 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertAccount, parameters);
00160 SynchroAtomicObjectManager.getManager().updateAtomicObject(new DefaultSynchroAtomicObject("2222", ""), this.getCollectionName(), null);
00161 parameters = new String[] { "3333", "ADGR", "34 Open Garden", "London", "1" };
00162 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertAccount, parameters);
00163 SynchroAtomicObjectManager.getManager().updateAtomicObject(new DefaultSynchroAtomicObject("3333", ""), this.getCollectionName(), null);
00164 parameters = new String[] { "4444", "EFFECT VIEW", "78 Red House", "London", "2" };
00165 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertAccount, parameters);
00166 SynchroAtomicObjectManager.getManager().updateAtomicObject(new DefaultSynchroAtomicObject("4444", ""), this.getCollectionName(), null);
00167
00168
00169 parameters = new String[] { "1111", "1111", "Phil", "Gartner", "1" };
00170 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertContact, parameters);
00171 parameters = new String[] { "2222", "1111", "Mac", "Urland", "2" };
00172 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertContact, parameters);
00173 parameters = new String[] { "3333", "2222", "Tom", "Petite", "3" };
00174 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertContact, parameters);
00175
00176
00177 Calendar cal = Calendar.getInstance();
00178 cal.add(Calendar.MONTH, 1);
00179 long time = cal.getTimeInMillis();
00180 String date = Long.toString(time);
00181 parameters = new String[] { "1111", "1111", "0", "250000", date, "Boat construction" };
00182 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertLeads, parameters);
00183 parameters = new String[] { "2222", "1111", "0", "50000", date, "Boat transport" };
00184 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertLeads, parameters);
00185 parameters = new String[] { "3333", "1111", "0", "100000", date, "Main store construction" };
00186 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertLeads, parameters);
00187 parameters = new String[] { "4444", "2222", "0", "10000", date, "Server architecture" };
00188 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertLeads, parameters);
00189 parameters = new String[] { "5555", "3333", "0", "150000", date, "Architecture update" };
00190 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertLeads, parameters);
00191 }
00192 } finally {
00193 this.getDbManager().garbageOpenedConnection();
00194 }
00195 }
00196
00197
00198
00199
00200 public String getCollectionName() {
00201 return "account";
00202 }
00203
00204
00205
00206
00207 public Object getCollectionObjectWithId(String id) throws OpenMSPException {
00208 try {
00209 Account account = null;
00210 String[] param = { id };
00211 ResultSet result = this.executeQuery(MyCrmAccountSynchroTarget.queryGetAccount, param);
00212 if (result.next()) {
00213
00214 account = new Account(result.getString(1));
00215 account.setName(DBImportFileCoder.getCoder().removeAntiSlash(result.getString(2)));
00216 account.setAddress(DBImportFileCoder.getCoder().removeAntiSlash(result.getString(3)));
00217 account.setCity(DBImportFileCoder.getCoder().removeAntiSlash(result.getString(4)));
00218 account.setActivity(result.getString(5));
00219 } else {
00220 return null;
00221 }
00222 result = this.executeQuery(MyCrmAccountSynchroTarget.queryGetContact, param);
00223 while (result.next()) {
00224
00225 Contact c = new Contact(result.getString(1));
00226 c.setFirstname(DBImportFileCoder.getCoder().removeAntiSlash(result.getString(3)));
00227 c.setLastname(DBImportFileCoder.getCoder().removeAntiSlash(result.getString(4)));
00228 c.setFunction(result.getInt(5));
00229 account.addContact(c);
00230 }
00231 Array contactList = account.getAllContacts();
00232 for (int i = 0; i < contactList.size(); i++) {
00233 Contact c = (Contact) contactList.get(i);
00234 param = new String[] { c.getId() };
00235 result = this.executeQuery(MyCrmAccountSynchroTarget.queryGetReport, param);
00236 while (result.next()) {
00237
00238 Report r = new Report(result.getString(1));
00239 r.setAction(result.getInt(3));
00240 r.setReportDate(new Date(result.getLong(4)));
00241 r.setDescription(DBImportFileCoder.getCoder().removeAntiSlash(result.getString(5)));
00242 r.setTerminalUserId(result.getString(6));
00243 c.addReport(r);
00244 }
00245 }
00246 return account;
00247 } catch (Throwable ex) {
00248 throw new OpenMSPException(ex);
00249 } finally {
00250 this.getDbManager().garbageOpenedConnection();
00251 }
00252 }
00253
00254
00255
00256
00257 public void updateCollectionObject(Object obj) throws OpenMSPException {
00258 try {
00259 Account account = (Account) obj;
00260 String[] param = { account.getId() };
00261 this.getDbManager().getConnection().setAutoCommit(false);
00262 try {
00263
00264 this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteReport, param);
00265 this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteContact, param);
00266 this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteAccount, param);
00267
00268
00269
00270 param = new String[] {
00271 account.getId()
00272 , DBImportFileCoder.getCoder().cleanAndFormatStringforDB(account.getName())
00273 , DBImportFileCoder.getCoder().cleanAndFormatStringforDB(account.getAddress())
00274 , DBImportFileCoder.getCoder().cleanAndFormatStringforDB(account.getCity())
00275 , account.getActivity()
00276 };
00277 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertAccount, param);
00278 Array contactList = account.getAllContacts();
00279 for (int i = 0; i < contactList.size(); i++) {
00280 Contact c = (Contact) contactList.get(i);
00281 Array reportList = c.getAllReports();
00282 for (int j = 0; j < reportList.size(); j++) {
00283 Report r = (Report) reportList.get(j);
00284
00285 param = new String[] {
00286 r.getId()
00287 , c.getId()
00288 , Integer.toString(r.getAction())
00289 , Long.toString(r.getReportDate().getTime())
00290 , DBImportFileCoder.getCoder().cleanAndFormatStringforDB(r.getDescription())
00291 , r.getTerminalUserId()
00292 };
00293 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertReport, param);
00294 }
00295
00296 param = new String[] {
00297 c.getId()
00298 , account.getId()
00299 , DBImportFileCoder.getCoder().cleanAndFormatStringforDB(c.getFirstname())
00300 , DBImportFileCoder.getCoder().cleanAndFormatStringforDB(c.getLastname())
00301 , Integer.toString(c.getFunction()) };
00302 this.executeUpdate(MyCrmAccountSynchroTarget.queryInsertContact, param);
00303 }
00304
00305
00306 SynchroAtomicObjectManager.getManager().updateAtomicObject(new DefaultSynchroAtomicObject(account.getId(), ""), this.getCollectionName(), null);
00307
00308 this.getDbManager().getConnection().commit();
00309 } finally {
00310 this.getDbManager().getConnection().setAutoCommit(true);
00311 }
00312 } catch (Throwable ex) {
00313 throw new OpenMSPException(ex);
00314 } finally {
00315 this.getDbManager().garbageOpenedConnection();
00316 }
00317
00318 }
00319
00320
00321
00322
00323 public void deleteCollectionObject(String id) throws OpenMSPException {
00324 try {
00325 String[] param = { id };
00326 this.getDbManager().getConnection().setAutoCommit(false);
00327 try {
00328
00329 this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteReport, param);
00330 this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteContact, param);
00331 this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteAccount, param);
00332
00333
00334 DefaultSynchroAtomicObject ao = new DefaultSynchroAtomicObject(id, "");
00335 ao.setModificationType(SynchroAtomicObject.DELETE);
00336 SynchroAtomicObjectManager.getManager().updateAtomicObject(ao, this.getCollectionName(), null);
00337
00338 this.getDbManager().getConnection().commit();
00339 } finally {
00340 this.getDbManager().getConnection().setAutoCommit(true);
00341 }
00342 } catch (Throwable ex) {
00343 throw new OpenMSPException(ex);
00344 } finally {
00345 this.getDbManager().garbageOpenedConnection();
00346 }
00347 }
00348
00349
00350
00351
00352 public SynchroConflicResolver getConflicResolver() {
00353 return new AlwaysUpdateServerSynchroConflicResolver();
00354 }
00355
00356
00357
00358
00359 public Array getAllCollectionObject() throws OpenMSPException {
00360
00361 Array ret = new Array();
00362 try {
00363 Array list = new Array();
00364 ResultSet result = this.executeQuery(MyCrmAccountSynchroTarget.queryGetAllAccountId, null);
00365 while (result.next()) {
00366 list.add(result.getString(1));
00367 }
00368
00369 for (int i = 0; i < list.size(); i++) {
00370 ret.add(this.getCollectionObjectWithId((String) list.get(i)));
00371 }
00372
00373 } catch (Throwable ex) {
00374 throw new OpenMSPException(ex);
00375 } finally {
00376 this.getDbManager().garbageOpenedConnection();
00377 }
00378 return ret;
00379 }
00380
00381
00382
00383
00384 public String getCollectionObjectId(Object obj) {
00385 return ((Account) obj).getId();
00386 }
00387
00388
00389
00390
00391 public SynchroAtomicObject[] getAllModifiedAtomicObjectSince(long date) throws OpenMSPException {
00392 try {
00393 return SynchroAtomicObjectManager.getManager().getAllModifiedAtomicObjectForServiceSince(this.getCollectionName(), date, null);
00394 } catch (SynchroException ex) {
00395 throw new OpenMSPException(ex);
00396 }
00397 }
00398
00399
00400
00401
00402 public int getUpdateMaxNbRow() {
00403 return 10;
00404 }
00405
00406
00407
00408
00409 public void updateSynchroDB(FastObjectDB db) throws OpenMSPException {
00410 }
00411
00412
00413
00414
00415 public void connect(Credential cred) throws UserNotFoundException, ServiceException {
00416
00417 }
00418
00419
00420
00421
00422 public void disconnect() {
00423
00424 }
00425
00426 }