MyCrmLabelQuery.java

00001 
00021 package org.openmobileis.examples.mycrm.data.jdbc;
00022 
00023 import java.sql.Connection;
00024 import java.sql.DatabaseMetaData;
00025 import java.sql.ResultSet;
00026 
00027 
00028 
00029 
00030 import org.openmobileis.common.util.database.AbstractQueryManager;
00031 import org.openmobileis.common.util.exception.DatabaseException;
00032 import org.openmobileis.common.util.exception.ServiceException;
00033 import org.openmobileis.common.util.log.LogManager;
00034 import org.openmobileis.examples.mycrm.data.Account;
00035 import org.openmobileis.examples.mycrm.data.Contact;
00036 import org.openmobileis.examples.mycrm.data.Leads;
00037 import org.openmobileis.examples.mycrm.data.Report;
00038 import org.openmobileis.modules.common.data.Label;
00039 import org.openmobileis.modules.common.database.fodb.FODBLabel;
00040 
00041 
00042 
00043 
00044 
00051 public class MyCrmLabelQuery extends AbstractQueryManager {
00052 
00053 
00054         protected static String queryInsertLabel = "INSERT INTO label (labelId, categoryId, label, langue)"
00055                 +" VALUES ('%0%', '%1%', '%2%', '%3%')";
00056         
00057         protected static String queryGetALlIDsLabels = "SELECT labelId from label";
00058         
00059         protected static String queryGetLabel = "SELECT labelId, categoryId, label from label where labelId='%0%' AND categoryId='%1%'";
00060         protected static String queryGetLabelsForCategory = "SELECT labelId, categoryId, label from label where categoryId='%0%'";
00061         protected static String queryGetALlLabels = "SELECT labelId, categoryId, label from label";
00062         protected static String queryDeleteLabel = "DELETE FROM label where labelId='%0%'AND categoryId='%1%'";
00063   
00064   public MyCrmLabelQuery() {
00065     super();
00066     try{
00067       this.init();  
00068     }catch(Throwable ex){
00069       LogManager.traceError(0, ex);
00070     }
00071   }
00072   
00073   public void init() throws ServiceException {
00074     try{
00075       
00076       this.createSyncTable();
00077 
00078     }catch(Throwable ex){
00079       throw new ServiceException(ex);
00080     }
00081     
00082     
00083   }
00084 
00085         protected String getCreateTableQuery()  {
00086                 return "CREATE TABLE label ("
00087                 +"      labelId varchar(5) NOT NULL,"
00088                 +"      categoryId varchar(5) NOT NULL,"
00089                 +"      label varchar(250),"
00090                 +"      langue varchar(5) NOT NULL, "
00091                 +"      CONSTRAINT label_id PRIMARY KEY (labelId, categoryId, langue)"
00092                 +");";
00093         }
00094   
00095   public Label convertLabelFromResult(ResultSet result) throws Exception {
00096                 try     {               
00097                         return new FODBLabel(result.getString(1), result.getString(2), result.getString(3));
00098                 } catch (Throwable ex)  {
00099                         throw new ServiceException(ex);
00100                 }
00101   }
00102   
00103   public void createLabel(String[] parameters) throws DatabaseException {
00104         this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00105   }
00106   
00107 
00108   public ResultSet getAllLabelIds() throws DatabaseException{
00109                 return this.executeQuery(MyCrmLabelQuery.queryGetALlIDsLabels, null);
00110   }
00111   
00112   
00113 
00114         /*
00115         * check if the sync table exists. IF it does not exist, create it
00116         */
00117         private void createSyncTable() throws DatabaseException, java.sql.SQLException {
00118                 try {
00119                         Connection con = this.getDbManager().getConnection();
00120                         DatabaseMetaData dbmd = con.getMetaData();
00121                         String[] tableType = {"TABLE"};
00122                         ResultSet result = dbmd.getTables (null,null,"LABEL", tableType);
00123                         if (!result.next()) {
00124                                 this.executeUpdate(this.getCreateTableQuery(), null);
00125                   //account activity
00126                   String[] parameters = {"0",Account.ACCOUNT_ACTIVITY_LABEL_CATEGORY,"Industry","EN"};
00127                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00128                   parameters = new String[]{"1",Account.ACCOUNT_ACTIVITY_LABEL_CATEGORY,"Service","EN"};
00129                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00130                   parameters = new String[]{"2",Account.ACCOUNT_ACTIVITY_LABEL_CATEGORY,"Pharmaceutique","EN"};
00131                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00132                   parameters = new String[]{"3",Account.ACCOUNT_ACTIVITY_LABEL_CATEGORY,"Gouvernment","EN"};
00133                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00134 
00135                   //contact function
00136                   parameters = new String[]{"0",Contact.CONTACT_FUNCTION_LABEL_CATEGORY,"CEO","EN"};
00137                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00138                   parameters = new String[]{"1",Contact.CONTACT_FUNCTION_LABEL_CATEGORY,"Commercial","EN"};
00139                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00140                   parameters = new String[]{"2",Contact.CONTACT_FUNCTION_LABEL_CATEGORY,"Buyer","EN"};
00141                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00142                   parameters = new String[]{"3",Contact.CONTACT_FUNCTION_LABEL_CATEGORY,"Manager","EN"};
00143                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00144 
00145                   //Leads state
00146                   parameters = new String[]{"0",Leads.LEADS_STATE_LABEL_CATEGORY,"Open","EN"};
00147                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00148                   parameters = new String[]{"1",Leads.LEADS_STATE_LABEL_CATEGORY,"Closed","EN"};
00149                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00150 
00151                   //Report action
00152                   parameters = new String[]{"0",Report.REPORT_ACTION_LABEL_CATEGORY,"Presentation","EN"};
00153                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00154                   parameters = new String[]{"1",Report.REPORT_ACTION_LABEL_CATEGORY,"Demo","EN"};
00155                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00156                   parameters = new String[]{"2",Report.REPORT_ACTION_LABEL_CATEGORY,"Congres","EN"};
00157                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00158                   parameters = new String[]{"3",Report.REPORT_ACTION_LABEL_CATEGORY,"Phoning","EN"};
00159                   this.executeUpdate(MyCrmLabelQuery.queryInsertLabel, parameters);
00160                         }
00161                 } finally {
00162                         this.getDbManager().garbageOpenedConnection();
00163                 }
00164         }
00165 
00166   /* (non-Javadoc)
00167    * @see org.openmobileis.modules.crm.server.synchro.services.common.FODBLabelSynchroQuery#getLabel(java.lang.String[])
00168    */
00169   public ResultSet getLabel(String[] param) throws DatabaseException{
00170                 return this.executeQuery(MyCrmLabelQuery.queryGetLabel, param);
00171   }
00172 
00173   /* (non-Javadoc)
00174    * @see org.openmobileis.modules.crm.server.synchro.services.common.FODBLabelSynchroQuery#getAllLabel()
00175    */
00176   public ResultSet getAllLabel() throws DatabaseException{
00177                 return this.executeQuery(MyCrmLabelQuery.queryGetALlLabels, null);
00178   }
00179 
00180 }

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