Main Page | Packages | Class Hierarchy | Class List | Directories | File List | Class Members | Related Pages

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.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  * Title:        OpenMobileIS project source <BR>
00056  * Description:
00057  * @author      Philippe Delrieu
00058  * @since       JDK 1.1
00059  * @version 1.0.
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    * check if the sync table exists. IF it does not exist, create it
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         //create default account
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         //create default contact ID,IDCLIENT,FIRSTNAME,LASTNAME,FUNCTION
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         //create default Leads ID,IDCLIENT,STATE,AMOUNT,DATE,DESCRIPTION
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   /* (non-Javadoc)
00188    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getCollectionName()
00189    */
00190   public String getCollectionName() {
00191     return "account";
00192   }
00193 
00194   /* (non-Javadoc)
00195    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getCollectionObjectWithId(java.lang.String)
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         //ID,NAME,ADDRESS,CITY,ACTIVITY 
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         //ID,IDCLIENT,FIRSTNAME,LASTNAME,FUNCTION
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           //ID,IDCONTACT,ACTION,DATE,DESCRIPTION
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   /* (non-Javadoc)
00245    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#updateCollectionObject(java.lang.Object)
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         //first delete all
00254         this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteReport, param);
00255         this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteContact, param);
00256         this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteAccount, param);
00257 
00258         //create all
00259         //ID,NAME,ADDRESS,CITY,ACTIVITY
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             //ID,IDCONTACT,ACTION,DATE,DESCRIPTION
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           //ID,IDCLIENT,FIRSTNAME,LASTNAME,FUNCTION
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         //update modification notification in Atomic Object table
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   /* (non-Javadoc)
00311    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#deleteCollectionObject(java.lang.String)
00312    */
00313   public void deleteCollectionObject(String id) throws OpenMSPException {
00314     try {
00315       String[] param = { id };
00316       this.getDbManager().getConnection().setAutoCommit(false);
00317       try {
00318         //first delete all
00319         this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteReport, param);
00320         this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteContact, param);
00321         this.executeUpdate(MyCrmAccountSynchroTarget.queryDeleteAccount, param);
00322 
00323         //update modification notification in Atomic Object table
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   /* (non-Javadoc)
00340    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getConflicResolver()
00341    */
00342   public SynchroConflicResolver getConflicResolver() {
00343     return new AlwaysUpdateServerSynchroConflicResolver(); //PDA WIN always update server
00344   }
00345 
00346   /* (non-Javadoc)
00347    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getAllCollectionObject()
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   /* (non-Javadoc)
00372    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getCollectionObjectId(java.lang.Object)
00373    */
00374   public String getCollectionObjectId(Object obj) {
00375     return ((Account) obj).getId();
00376   }
00377 
00378   /* (non-Javadoc)
00379    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getAllModifiedAtomicObjectForServiceSince(java.lang.String, long)
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   /* (non-Javadoc)
00390    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#getUpdateMaxNbRow()
00391    */
00392   public int getUpdateMaxNbRow() {
00393     return 10; //start complete when there is 2 or more account modifiation
00394   }
00395 
00396   /* (non-Javadoc)
00397    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncService#updateSynchroDB(org.openmobileis.database.fastobjectdb.FastObjectDB)
00398    */
00399   public void updateSynchroDB(FastObjectDB db) throws OpenMSPException {
00400   }
00401 
00402   /* (non-Javadoc)
00403    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncTarget#connect(org.openmobileis.synchro.security.auth.Credential)
00404    */
00405   public void connect(Credential cred) throws UserNotFoundException, ServiceException {
00406     // do nothing
00407   }
00408 
00409   /* (non-Javadoc)
00410    * @see org.openmobileis.database.fastobjectdb.synchro.server.FODBSyncTarget#disconnect()
00411    */
00412   public void disconnect() {
00413     // do nothing
00414   }
00415 
00416 }

Generated on Mon Jul 10 10:29:31 2006 for OpenMobileIS by  doxygen 1.4.4