00001 package org.openmobileis.examples.mycrm.data.jdbc;
00002
00003 import org.openmobileis.common.util.database.AbstractQueryManager;
00004 import org.openmobileis.common.util.database.ManagerDB;
00005 import org.openmobileis.common.util.exception.DatabaseException;
00006 import org.openmobileis.common.util.log.LogManager;
00007 import org.openmobileis.modules.common.data.DefaultTerminalUser;
00008 import org.openmobileis.modules.common.data.TerminalUser;
00009 import org.openmobileis.modules.common.database.jdbc.TerminalUserJDBCQuery;
00010 import org.openmobileis.synchro.openmsp.client.db.DBImportFileCoder;
00011
00012 import java.sql.Connection;
00013 import java.sql.DatabaseMetaData;
00014 import java.sql.ResultSet;
00015
00024 public final class MyCrmTerminalUserlQuery extends AbstractQueryManager implements TerminalUserJDBCQuery {
00025
00026 private static String getUserWithId = "SELECT code_rep, nom, prenom, email, type, actif, usePDA, langue FROM user WHERE code_rep='%0%'";
00027 private static String getAllUsers = "SELECT code_rep, nom, prenom, email, type, actif, usePDA, langue FROM user";
00028 protected static String queryInsertUser = "INSERT INTO user (code_rep, nom, prenom, email, type, actif, usePDA, langue)"
00029 +" VALUES ('%0%', '%1%', '%2%', '%3%', '%4%', %5%, %6%, '%7%')";
00030
00031
00032 protected static String queryUpdateUser = "UPDATE user "
00033 +"SET nom='%0%', prenom='%1%', email='%2%', type='%3%', actif=%4%, usePDA=%5%, langue='%6%'"
00034 +" WHERE code_rep='%7%'";
00035
00036 protected static String queryUpdateLoginPass = "UPDATE user "
00037 +"SET login='%0%', password='%1%'"
00038 +" WHERE code_rep='%2%'";
00039
00040 protected static String queryDeleteUser = "DELETE FROM user where code_rep='%0%'";
00041
00042
00043 private static String queryCreateTableUser =
00044 "CREATE TABLE user ("
00045 +" code_rep varchar(5) NOT NULL,"
00046 +" nom varchar(30) NOT NULL,"
00047 +" prenom varchar(30),"
00048 +" email varchar(60),"
00049 +" login varchar(15),"
00050 +" password varchar(50),"
00051 +" type varchar(5), "
00052 +" actif bit, "
00053 +" usePDA bit, "
00054 +" langue varchar(2), "
00055 +" CONSTRAINT rep_id PRIMARY KEY (code_rep)"
00056 +");"
00057 +"CREATE INDEX user_login ON user (login);";
00058
00059 public MyCrmTerminalUserlQuery() {
00060 super();
00061 try {
00062 this.createSyncTable();
00063 } catch (Throwable ex) {
00064 LogManager.traceError(0, ex);
00065 }
00066 }
00067
00068
00069
00070
00071 private void createSyncTable() throws DatabaseException, java.sql.SQLException {
00072 try {
00073 Connection con = this.getDbManager().getConnection();
00074 DatabaseMetaData dbmd = con.getMetaData();
00075 String[] tableType = {"TABLE"};
00076 ResultSet result = dbmd.getTables (null,null,"USER", tableType);
00077 if (!result.next()) {
00078 this.executeUpdate(queryCreateTableUser, null);
00079 String[] parameters = { "1111", "English", "testen", "emailen", "0", "1", "1", "EN" };
00080 this.executeUpdate(queryInsertUser, parameters);
00081 parameters = new String[]{"en", "en", "1111" };
00082 this.executeUpdate(queryUpdateLoginPass, parameters);
00083 parameters = new String[]{ "2222", "Français", "testfr", "emailfr", "0", "1", "1", "FR" };
00084 this.executeUpdate(queryInsertUser, parameters);
00085 parameters = new String[]{"fr", "fr", "2222" };
00086 this.executeUpdate(queryUpdateLoginPass, parameters);
00087 }
00088 } finally {
00089 this.getDbManager().garbageOpenedConnection();
00090 }
00091 }
00092
00093 public ResultSet getTerminalUser (String[] param) throws DatabaseException {
00094 return this.executeQuery(MyCrmTerminalUserlQuery.getUserWithId, param);
00095 }
00096
00097 public ResultSet getAllTerminalUsers () throws DatabaseException {
00098 return this.executeQuery(MyCrmTerminalUserlQuery.getAllUsers, null);
00099 }
00100
00104 public void insertTerminalUser(String[] parameters) throws DatabaseException {
00105 this.executeUpdate(MyCrmTerminalUserlQuery.queryInsertUser, parameters);
00106 }
00107
00111 public void deleteTerminalUser(String[] parameters) throws DatabaseException {
00112 this.executeUpdate(MyCrmTerminalUserlQuery.queryDeleteUser, parameters);
00113 }
00114
00118 public void updateTerminalUser(String[] parameters) throws DatabaseException {
00119 this.executeUpdate(MyCrmTerminalUserlQuery.queryUpdateUser, parameters);
00120 }
00121 public void updateLoginPass(String[] parameters) throws DatabaseException {
00122 this.executeUpdate(MyCrmTerminalUserlQuery.queryUpdateLoginPass, parameters);
00123 }
00124
00125 public ManagerDB getDbManager() {
00126 return super.getDbManager();
00127 }
00128
00129
00130 public TerminalUser convertResultSetToTerminalUser (ResultSet result) throws DatabaseException {
00131 try {
00132
00133 DefaultTerminalUser rep = new DefaultTerminalUser(result.getString(1));
00134 rep.setName(result.getString(2));
00135 rep.setProfil(result.getString(5));
00136 rep.setActif(DBImportFileCoder.getCoder().convertDBDataToBoolean(result.getByte(6)));
00137 rep.setLangue(result.getString(8));
00138 return rep;
00139 } catch (Throwable ex) {
00140 throw new DatabaseException();
00141 }
00142 }
00143 public String[] getInsertParamFromTerminalUser(TerminalUser rep) {return null;}
00144 public String[] getUpdateParamFromTerminalUser(TerminalUser rep) {return null;}
00145
00146
00147 }