MyCrmAccountSynchroTarget.java

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  * Title:        OpenMobileIS project source <BR>
00055  * Description:
00056  * @author      Philippe Delrieu
00057  * @since       JDK 1.1
00058  * @version 1.0.
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  // private static String queryUpdateAccount = "UPDATE ACCOUNT SET NAME='%0%', ADDRESS='%1%', CITY ='%2%' , ACTIVITY =%3% " + " WHERE ID='%4%'";
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  // private static String queryUpdateContact = "UPDATE CONTACT SET IDCLIENT='%0%', FIRSTNAME='%1%', LASTNAME ='%2%' , FUNCTION =%3% " + " WHERE ID='%4%'";
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  // private static String queryUpdateLeads = "UPDATE LEADS SET IDCLIENT=%0%, STATE=%1%, AMOUNT =%2%, DATE =%3%, DESCRIPTION='%4%' " + " WHERE ID='%5%'";
00103 
00104  // private static String queryDeleteLeads = "DELETE FROM LEADS where ID='%0%'";
00105 
00106 //  private static String queryGetLeads = "SELECT ID,IDCLIENT,STATE,AMOUNT,DATE,DESCRIPTION from LEADS where ID='%0%'";
00107 
00108   private static String queryInsertReport = "INSERT INTO REPORT (ID,IDCONTACT,ACTION,DATE,DESCRIPTION,TERMINALUSERID )" + " VALUES ('%0%', '%1%', %2%, %3%, '%4%', '%5%')";
00109 
00110  // private static String queryUpdateReport = "UPDATE REPORT SET IDCONTACT='%0%', ACTION=%1%, DATE =%2% , DESCRIPTION ='%3%' ,TERMINALUSERID='%4%'" + " WHERE ID='%5%'";
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    * check if the sync table exists. IF it does not exist, create it
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         //create default account
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         //create default contact ID,IDCLIENT,FIRSTNAME,LASTNAME,FUNCTION
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         //create default Leads ID,IDCLIENT,STATE,AMOUNT,DATE,DESCRIPTION
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   /* (non-Javadoc)
00198    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getCollectionName()
00199    */
00200   public String getCollectionName() {
00201     return "account";
00202   }
00203 
00204   /* (non-Javadoc)
00205    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getCollectionObjectWithId(java.lang.String)
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         //ID,NAME,ADDRESS,CITY,ACTIVITY 
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         //ID,IDCLIENT,FIRSTNAME,LASTNAME,FUNCTION
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           //ID,IDCONTACT,ACTION,DATE,DESCRIPTION
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   /* (non-Javadoc)
00255    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#updateCollectionObject(java.lang.Object)
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         //first delete all
00264         this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteReport, param);
00265         this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteContact, param);
00266         this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteAccount, param);
00267 
00268         //create all
00269         //ID,NAME,ADDRESS,CITY,ACTIVITY
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             //ID,IDCONTACT,ACTION,DATE,DESCRIPTION
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           //ID,IDCLIENT,FIRSTNAME,LASTNAME,FUNCTION
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         //update modification notification in Atomic Object table
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   /* (non-Javadoc)
00321    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#deleteCollectionObject(java.lang.String)
00322    */
00323   public void deleteCollectionObject(String id) throws OpenMSPException {
00324     try {
00325       String[] param = { id };
00326       this.getDbManager().getConnection().setAutoCommit(false);
00327       try {
00328         //first delete all
00329         this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteReport, param);
00330         this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteContact, param);
00331         this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteAccount, param);
00332 
00333         //update modification notification in Atomic Object table
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   /* (non-Javadoc)
00350    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getConflicResolver()
00351    */
00352   public SynchroConflicResolver getConflicResolver() {
00353     return new AlwaysUpdateServerSynchroConflicResolver(); //PDA WIN always update server
00354   }
00355 
00356   /* (non-Javadoc)
00357    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getAllCollectionObject()
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   /* (non-Javadoc)
00382    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getCollectionObjectId(java.lang.Object)
00383    */
00384   public String getCollectionObjectId(Object obj) {
00385     return ((Account) obj).getId();
00386   }
00387 
00388   /* (non-Javadoc)
00389    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getAllModifiedAtomicObjectForServiceSince(java.lang.String, long)
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   /* (non-Javadoc)
00400    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getUpdateMaxNbRow()
00401    */
00402   public int getUpdateMaxNbRow() {
00403     return 10; //start complete when there is 2 or more account modifiation
00404   }
00405 
00406   /* (non-Javadoc)
00407    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#updateSynchroDB(org.openmobileis.database.fastobjectdb.FastObjectDB)
00408    */
00409   public void updateSynchroDB(FastObjectDB db) throws OpenMSPException {
00410   }
00411 
00412   /* (non-Javadoc)
00413    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncTarget#connect(org.openmobileis.synchro.security.auth.Credential)
00414    */
00415   public void connect(Credential cred) throws UserNotFoundException, ServiceException {
00416     // do nothing
00417   }
00418 
00419   /* (non-Javadoc)
00420    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncTarget#disconnect()
00421    */
00422   public void disconnect() {
00423     // do nothing
00424   }
00425 
00426 }

Generated on Mon Dec 4 11:03:28 2006 for OpenMobileIS by  doxygen 1.5.1-p1