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.exportuserpreferences.business;
35
36 import fr.paris.lutece.plugins.exportuserpreferences.utils.CsvUtils;
37 import fr.paris.lutece.plugins.exportuserpreferences.utils.export.UserPreferencesExportUtils;
38 import fr.paris.lutece.plugins.exportuserpreferences.web.KeyJspBean;
39 import fr.paris.lutece.portal.service.plugin.Plugin;
40 import fr.paris.lutece.util.sql.DAOUtil;
41
42 import java.util.ArrayList;
43 import java.util.Collection;
44 import java.util.HashMap;
45 import java.util.List;
46 import java.util.Map;
47
48
49
50
51
52 public final class KeyDAO implements IKeyDAO
53 {
54
55 private static final String SQL_QUERY_SELECT = "SELECT pref_key, to_export FROM exportuserpreferences_key WHERE pref_key = ?";
56 private static final String SQL_QUERY_INSERT = "INSERT INTO exportuserpreferences_key ( pref_key, to_export ) VALUES ( ?, ? ) ";
57 private static final String SQL_QUERY_DELETE = "DELETE FROM exportuserpreferences_key WHERE pref_key = ? ";
58 private static final String SQL_QUERY_UPDATE = "UPDATE exportuserpreferences_key SET pref_key = ?, to_export = ? WHERE pref_key = ?";
59 private static final String SQL_QUERY_SELECTALL = "SELECT pref_key, to_export FROM exportuserpreferences_key";
60 private static final String SQL_QUERY_SELECTALL_PREFKEY = "SELECT pref_key FROM exportuserpreferences_key";
61 private static final String SQL_QUERY_SELECT_TOEXPORT = "SELECT pref_key FROM exportuserpreferences_key WHERE to_export = 1";
62 private static final String SQL_SELECT_VALUES = "SELECT DISTINCT " +
63 "core_user_preferences.id_user, core_user_preferences.pref_key, core_user_preferences.pref_value " +
64 "FROM core_user_preferences " +
65 "LEFT JOIN exportuserpreferences_key ON core_user_preferences.pref_key LIKE exportuserpreferences_key.pref_key " +
66 "WHERE exportuserpreferences_key.to_export = 1";
67 private static final String SQL_QUERY_SELECT_AVAILABLEKEYS = "SELECT DISTINCT pref_key FROM core_user_preferences cup " +
68 "WHERE NOT EXISTS (SELECT pref_key FROM exportuserpreferences_key ek WHERE cup.pref_key = ek.pref_key)";
69
70
71
72
73 @Override
74 public void insert( Key key, Plugin plugin )
75 {
76 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
77
78 daoUtil.setString( 1, key.getPrefKey( ) );
79 daoUtil.setBoolean( 2, key.getToExport( ) );
80
81 daoUtil.executeUpdate( );
82 daoUtil.free( );
83 }
84
85
86
87
88 @Override
89 public Key load( String strPrefKey, Plugin plugin )
90 {
91 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
92 daoUtil.setString( 1, strPrefKey );
93 daoUtil.executeQuery( );
94
95 Key key = null;
96
97 if ( daoUtil.next( ) )
98 {
99 key = new Key( );
100 key.setPrefKey( daoUtil.getString( 1 ) );
101 key.setToExport( daoUtil.getBoolean( 2 ) );
102 }
103
104 daoUtil.free( );
105
106 return key;
107 }
108
109
110
111
112 @Override
113 public void delete( String strPrefKey, Plugin plugin )
114 {
115 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
116 daoUtil.setString( 1, strPrefKey );
117 daoUtil.executeUpdate( );
118 daoUtil.free( );
119 }
120
121
122
123
124 @Override
125 public void store( Key key, Plugin plugin )
126 {
127 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
128
129 daoUtil.setString( 1, key.getPrefKey( ) );
130 daoUtil.setBoolean( 2, key.getToExport( ) );
131 daoUtil.setString( 3, key.getPrefKey( ) );
132
133 daoUtil.executeUpdate( );
134 daoUtil.free( );
135 }
136
137
138
139
140 @Override
141 public Collection<Key> selectKeysList( Plugin plugin )
142 {
143 Collection<Key> keyList = new ArrayList<Key>( );
144 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
145 daoUtil.executeQuery( );
146
147 while ( daoUtil.next( ) )
148 {
149 Key key = new Key( );
150
151 key.setPrefKey( daoUtil.getString( 1 ) );
152 key.setToExport( daoUtil.getBoolean( 2 ) );
153
154 keyList.add( key );
155 }
156
157 daoUtil.free( );
158
159 return keyList;
160 }
161
162
163
164
165 @Override
166 public Collection<String> selectPrefKeysList( Plugin plugin )
167 {
168 Collection<String> keyList = new ArrayList<String>( );
169 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_PREFKEY, plugin );
170 daoUtil.executeQuery( );
171
172 while ( daoUtil.next( ) )
173 {
174 keyList.add( daoUtil.getString( 1 ) );
175 }
176
177 daoUtil.free( );
178
179 return keyList;
180 }
181
182
183
184
185 @Override
186 public Collection<String> selectToExportKeysList( Plugin plugin )
187 {
188 Collection<String> keyList = new ArrayList<String>( );
189 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_TOEXPORT, plugin );
190 daoUtil.executeQuery( );
191
192 while ( daoUtil.next( ) )
193 {
194 keyList.add( daoUtil.getString( 1 ) );
195 }
196
197 daoUtil.free( );
198
199 return keyList;
200 }
201
202
203
204
205 @Override
206 public Map<String, ArrayList<String>> getValuesList( Plugin plugin )
207 {
208 Map<String, ArrayList<String>> map = new HashMap<String, ArrayList<String>>( );
209 Map<String, Integer> headers = CsvUtils.getHeaderLineOrder( KeyJspBean.MARK_USERPREFERENCES );
210 DAOUtil daoUtil = new DAOUtil( SQL_SELECT_VALUES, plugin );
211 daoUtil.executeQuery( );
212
213
214 while ( daoUtil.next( ) )
215 {
216 ArrayList<String> values = map.get( daoUtil.getString( 1 ) );
217 String prefValue = daoUtil.getString( 3 );
218 String prefKey = daoUtil.getString( 2 );
219 Integer valuePosition = headers.get( prefKey );
220
221 if ( valuePosition == null )
222 {
223 throw new RuntimeException( UserPreferencesExportUtils.ERROR_MISSING_HEADER );
224 }
225 if ( values == null )
226 {
227 values = new ArrayList<String>( headers.size( ) );
228 }
229
230 if ( valuePosition >= values.size( ) )
231 {
232 for ( int n = values.size( ); n < valuePosition; n++ )
233 {
234 values.add( "" );
235 }
236 values.add( valuePosition, prefValue );
237 }
238 else
239 {
240 values.set( valuePosition, prefValue );
241 }
242
243 map.put( daoUtil.getString( 1 ), values );
244
245 }
246
247 daoUtil.free( );
248
249 return map;
250 }
251
252
253
254
255 @Override
256 public Collection<String> selectAvailableKeysList( Plugin plugin )
257 {
258 Collection<String> keyList = new ArrayList<String>( );
259 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_AVAILABLEKEYS, plugin );
260 daoUtil.executeQuery( );
261
262 while ( daoUtil.next( ) )
263 {
264 keyList.add( daoUtil.getString( 1 ) );
265 }
266
267 daoUtil.free( );
268
269 return keyList;
270 }
271 }