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