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