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

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

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