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
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
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
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
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
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
00167
00168
00169 public ResultSet getLabel(String[] param) throws DatabaseException{
00170 return this.executeQuery(MyCrmLabelQuery.queryGetLabel, param);
00171 }
00172
00173
00174
00175
00176 public ResultSet getAllLabel() throws DatabaseException{
00177 return this.executeQuery(MyCrmLabelQuery.queryGetALlLabels, null);
00178 }
00179
00180 }