1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 package fr.paris.lutece.plugins.crm.business.user;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.sql.Timestamp;
40
41 import java.util.ArrayList;
42 import java.util.List;
43
44
45
46
47
48
49 public class CRMUserDAO implements ICRMUserDAO
50 {
51
52 private static final String SQL_QUERY_NEW_PK = " SELECT max( id_crm_user ) FROM crm_user ";
53 private static final String SQL_QUERY_INSERT = " INSERT INTO crm_user (id_crm_user, user_guid, status,must_be_updated) VALUES (?,?,?,?) ";
54 private static final String SQL_QUERY_SELECT_ALL = " SELECT id_crm_user, user_guid, status, must_be_updated,last_login FROM crm_user ";
55 private static final String SQL_QUERY_SELECT = SQL_QUERY_SELECT_ALL + " WHERE id_crm_user = ? ";
56 private static final String SQL_QUERY_SELECT_BY_USER_GUID = SQL_QUERY_SELECT_ALL + " WHERE user_guid = ? ";
57 private static final String SQL_QUERY_UPDATE = " UPDATE crm_user SET user_guid = ?, status = ?,must_be_updated= ? WHERE id_crm_user = ? ";
58 private static final String SQL_QUERY_DELETE = " DELETE FROM crm_user WHERE id_crm_user = ? ";
59 private static final String SQL_QUERY_SELECT_ID_CRM_USER = " SELECT cu.id_crm_user FROM crm_user AS cu ";
60 private static final String SQL_QUERY_FILTER_BY_LIST_IDS = " WHERE id_crm_user IN ( ";
61
62
63 private static final String INTERROGATION_MARK = "?";
64 private static final String COMMA = ",";
65 private static final String CLOSED_BRACKET = ")";
66
67
68
69
70 @Override
71 public int newPrimaryKey( Plugin plugin )
72 {
73 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
74 daoUtil.executeQuery( );
75
76 int nKey = 1;
77
78 if ( daoUtil.next( ) )
79 {
80 nKey = daoUtil.getInt( 1 ) + 1;
81 }
82
83 daoUtil.free( );
84
85 return nKey;
86 }
87
88
89
90
91 @Override
92 public int insert( CRMUser user, Plugin plugin )
93 {
94 int nKey = -1;
95
96 if ( user != null )
97 {
98 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
99
100 int nIndex = 1;
101 user.setIdCRMUser( newPrimaryKey( plugin ) );
102
103 daoUtil.setInt( nIndex++, user.getIdCRMUser( ) );
104 daoUtil.setString( nIndex++, user.getUserGuid( ) );
105 daoUtil.setInt( nIndex++, user.getStatus( ) );
106 daoUtil.setBoolean( nIndex, user.isMustBeUpdated( ) );
107
108 daoUtil.executeUpdate( );
109 daoUtil.free( );
110
111 nKey = user.getIdCRMUser( );
112 }
113
114 return nKey;
115 }
116
117
118
119
120 @Override
121 public CRMUser load( int nIdCRMUser, Plugin plugin )
122 {
123 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
124 daoUtil.setInt( 1, nIdCRMUser );
125 daoUtil.executeQuery( );
126
127 CRMUser user = null;
128
129 if ( daoUtil.next( ) )
130 {
131 int nIndex = 1;
132 user = new CRMUser( );
133 user.setIdCRMUser( daoUtil.getInt( nIndex++ ) );
134 user.setUserGuid( daoUtil.getString( nIndex++ ) );
135 user.setStatus( daoUtil.getInt( nIndex++ ) );
136 user.setMustBeUpdated( daoUtil.getBoolean( nIndex++ ) );
137 try
138 {
139 Timestamp dateLastLogin = daoUtil.getTimestamp( nIndex++ );
140
141 if ( ( dateLastLogin != null ) && !dateLastLogin.equals( CRMUser.DEFAULT_DATE_LAST_LOGIN ) )
142 {
143 user.setDateLastLogin( dateLastLogin );
144 }
145 }
146 catch( Exception e )
147 {
148 user.setDateLastLogin( null );
149 }
150 }
151
152 daoUtil.free( );
153
154 return user;
155 }
156
157
158
159
160 @Override
161 public CRMUser loadByUserGuid( String strUserGuid, Plugin plugin )
162 {
163 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_USER_GUID, plugin );
164 daoUtil.setString( 1, strUserGuid );
165 daoUtil.executeQuery( );
166
167 CRMUser user = null;
168
169 if ( daoUtil.next( ) )
170 {
171 int nIndex = 1;
172 user = new CRMUser( );
173 user.setIdCRMUser( daoUtil.getInt( nIndex++ ) );
174 user.setUserGuid( daoUtil.getString( nIndex++ ) );
175 user.setStatus( daoUtil.getInt( nIndex++ ) );
176 user.setMustBeUpdated( daoUtil.getBoolean( nIndex++ ) );
177
178 try
179 {
180 Timestamp dateLastLogin = daoUtil.getTimestamp( nIndex++ );
181
182 if ( ( dateLastLogin != null ) && !dateLastLogin.equals( CRMUser.DEFAULT_DATE_LAST_LOGIN ) )
183 {
184 user.setDateLastLogin( dateLastLogin );
185 }
186 }
187 catch( Exception e )
188 {
189 user.setDateLastLogin( null );
190 }
191 }
192
193 daoUtil.free( );
194
195 return user;
196 }
197
198
199
200
201 @Override
202 public void store( CRMUser user, Plugin plugin )
203 {
204 if ( user != null )
205 {
206 int nIndex = 1;
207
208 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
209
210 daoUtil.setString( nIndex++, user.getUserGuid( ) );
211 daoUtil.setInt( nIndex++, user.getStatus( ) );
212 daoUtil.setBoolean( nIndex++, user.isMustBeUpdated( ) );
213
214 daoUtil.setInt( nIndex, user.getIdCRMUser( ) );
215 daoUtil.executeUpdate( );
216 daoUtil.free( );
217 }
218 }
219
220
221
222
223 @Override
224 public void delete( int nIdCRMUser, Plugin plugin )
225 {
226 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
227 daoUtil.setInt( 1, nIdCRMUser );
228 daoUtil.executeUpdate( );
229 daoUtil.free( );
230 }
231
232
233
234
235 @Override
236 public List<CRMUser> selectAll( Plugin plugin )
237 {
238 List<CRMUser> listUsers = new ArrayList<CRMUser>( );
239 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin );
240 daoUtil.executeQuery( );
241
242 while ( daoUtil.next( ) )
243 {
244 int nIndex = 1;
245 CRMUserugins/crm/business/user/CRMUser.html#CRMUser">CRMUser user = new CRMUser( );
246 user.setIdCRMUser( daoUtil.getInt( nIndex++ ) );
247 user.setUserGuid( daoUtil.getString( nIndex++ ) );
248 user.setStatus( daoUtil.getInt( nIndex++ ) );
249 user.setMustBeUpdated( daoUtil.getBoolean( nIndex++ ) );
250
251 try
252 {
253 Timestamp dateLastLogin = daoUtil.getTimestamp( nIndex++ );
254
255 if ( ( dateLastLogin != null ) && !dateLastLogin.equals( CRMUser.DEFAULT_DATE_LAST_LOGIN ) )
256 {
257 user.setDateLastLogin( dateLastLogin );
258 }
259 }
260 catch( Exception e )
261 {
262 user.setDateLastLogin( null );
263 }
264
265 listUsers.add( user );
266 }
267
268 daoUtil.free( );
269
270 return listUsers;
271 }
272
273
274
275
276 @Override
277 public List<Integer> selectListIdsCRMUserByFilter( CRMUserFilter filter, Plugin plugin )
278 {
279 List<Integer> listIds = new ArrayList<Integer>( );
280 String strSQL = filter.buildSQLQuery( SQL_QUERY_SELECT_ID_CRM_USER );
281 DAOUtil daoUtil = new DAOUtil( strSQL, plugin );
282 filter.setFilterValues( daoUtil );
283 daoUtil.executeQuery( );
284
285 while ( daoUtil.next( ) )
286 {
287 listIds.add( daoUtil.getInt( 1 ) );
288 }
289
290 daoUtil.free( );
291
292 return listIds;
293 }
294
295
296
297
298 @Override
299 public List<CRMUser> selectByListIds( List<Integer> listIdsCRMUser, Plugin plugin )
300 {
301 List<CRMUser> listUsers = new ArrayList<CRMUser>( );
302
303
304 StringBuilder sbSQL = new StringBuilder( SQL_QUERY_SELECT_ALL );
305
306 if ( !listIdsCRMUser.isEmpty( ) )
307 {
308 sbSQL.append( SQL_QUERY_FILTER_BY_LIST_IDS );
309
310 for ( int nIndex = 0; nIndex < listIdsCRMUser.size( ); nIndex++ )
311 {
312 sbSQL.append( INTERROGATION_MARK );
313
314 if ( nIndex < ( listIdsCRMUser.size( ) - 1 ) )
315 {
316 sbSQL.append( COMMA );
317 }
318 }
319
320 sbSQL.append( CLOSED_BRACKET );
321 }
322
323 DAOUtil daoUtil = new DAOUtil( sbSQL.toString( ), plugin );
324
325
326 int nIndex = 1;
327
328 for ( int nIdCRMUser : listIdsCRMUser )
329 {
330 daoUtil.setInt( nIndex, nIdCRMUser );
331 nIndex++;
332 }
333
334 daoUtil.executeQuery( );
335
336 while ( daoUtil.next( ) )
337 {
338 nIndex = 1;
339
340 CRMUserugins/crm/business/user/CRMUser.html#CRMUser">CRMUser user = new CRMUser( );
341 user.setIdCRMUser( daoUtil.getInt( nIndex++ ) );
342 user.setUserGuid( daoUtil.getString( nIndex++ ) );
343 user.setStatus( daoUtil.getInt( nIndex++ ) );
344 user.setMustBeUpdated( daoUtil.getBoolean( nIndex++ ) );
345 listUsers.add( user );
346 }
347
348 daoUtil.free( );
349
350 return listUsers;
351 }
352 }