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.participatorybudget.business.campaign;
35
36 import java.util.ArrayList;
37 import java.util.Collection;
38 import java.util.HashMap;
39 import java.util.List;
40 import java.util.Map;
41
42 import fr.paris.lutece.portal.business.file.File;
43 import fr.paris.lutece.portal.business.file.FileHome;
44 import fr.paris.lutece.portal.service.plugin.Plugin;
45 import fr.paris.lutece.util.sql.DAOUtil;
46
47
48
49
50
51 public final class CampaignThemeDAO implements ICampaignThemeDAO
52 {
53
54 private static final String SQL_QUERY_NEW_PK = "SELECT max( id_campaign_theme ) FROM participatorybudget_campaign_theme";
55 private static final String SQL_QUERY_SELECT = "SELECT id_campaign_theme, code_campaign, code_theme, title, description, active, image_file, front_rgb FROM participatorybudget_campaign_theme WHERE id_campaign_theme = ?";
56 private static final String SQL_QUERY_INSERT = "INSERT INTO participatorybudget_campaign_theme ( id_campaign_theme, code_campaign, code_theme, title, description, active, image_file, front_rgb ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ) ";
57 private static final String SQL_QUERY_DELETE = "DELETE FROM participatorybudget_campaign_theme WHERE id_campaign_theme = ? ";
58 private static final String SQL_QUERY_UPDATE = "UPDATE participatorybudget_campaign_theme SET id_campaign_theme = ?, code_campaign = ?, code_theme = ?, title = ?, description = ?, active = ?, image_file = ?, front_rgb = ? WHERE id_campaign_theme = ?";
59 private static final String SQL_QUERY_CHANGEALL_CAMPAIGN_CODE = "UPDATE participatorybudget_campaign_theme SET code_campaign = ? WHERE code_campaign = ?";
60 private static final String SQL_QUERY_SELECTALL = "SELECT id_campaign_theme, code_campaign, code_theme, title, description, active, image_file, front_rgb FROM participatorybudget_campaign_theme";
61 private static final String SQL_QUERY_SELECTALL_BY_CAMPAIGN = SQL_QUERY_SELECTALL + " WHERE code_campaign = ?";
62 private static final String SQL_QUERY_SELECTALL_ID = "SELECT id_campaign_theme FROM participatorybudget_campaign_theme";
63 private static final String SQL_QUERY_SELECT_BY_TITLETHEME = "SELECT id_campaign_theme, code_campaign, code_theme, title, description, active, image_file, front_rgb FROM participatorybudget_campaign_theme WHERE code_theme = ?";
64
65
66
67
68
69
70
71
72 public int newPrimaryKey( Plugin plugin )
73 {
74 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PK, plugin );
75 daoUtil.executeQuery( );
76
77 int nKey = 1;
78
79 if ( daoUtil.next( ) )
80 {
81 nKey = daoUtil.getInt( 1 ) + 1;
82 }
83
84 daoUtil.free( );
85
86 return nKey;
87 }
88
89
90
91
92 @Override
93 public void insert( CampaignTheme campaignTheme, Plugin plugin )
94 {
95 int nCpt = 1;
96 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
97
98 campaignTheme.setId( newPrimaryKey( plugin ) );
99
100 daoUtil.setInt( nCpt++, campaignTheme.getId( ) );
101 daoUtil.setString( nCpt++, campaignTheme.getCodeCampaign( ) );
102 daoUtil.setString( nCpt++, campaignTheme.getCode( ) );
103 daoUtil.setString( nCpt++, campaignTheme.getTitle( ) );
104 daoUtil.setString( nCpt++, campaignTheme.getDescription( ) );
105 daoUtil.setBoolean( nCpt++, campaignTheme.getActive( ) );
106 File image = campaignTheme.getImage( );
107 if ( image != null )
108 {
109 daoUtil.setInt( nCpt++, image.getIdFile( ) );
110 }
111 else
112 {
113 daoUtil.setIntNull( nCpt++ );
114 }
115 daoUtil.setString( nCpt++, campaignTheme.getFrontRgb( ) );
116
117 daoUtil.executeUpdate( );
118 daoUtil.free( );
119 }
120
121
122
123
124 @Override
125 public CampaignTheme load( int nKey, Plugin plugin )
126 {
127 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
128 daoUtil.setInt( 1, nKey );
129 daoUtil.executeQuery( );
130
131 CampaignTheme campaignTheme = null;
132
133 if ( daoUtil.next( ) )
134 {
135 campaignTheme = getRow( daoUtil );
136 }
137
138 daoUtil.free( );
139 return campaignTheme;
140 }
141
142
143
144
145 @Override
146 public CampaignTheme loadByCodeTheme( String codeTheme, Plugin plugin )
147 {
148 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_TITLETHEME, plugin );
149 daoUtil.setString( 1, codeTheme );
150 daoUtil.executeQuery( );
151
152 CampaignTheme campaignTheme = null;
153
154 if ( daoUtil.next( ) )
155 {
156 campaignTheme = getRow( daoUtil );
157 }
158
159 daoUtil.free( );
160 return campaignTheme;
161 }
162
163
164
165
166 @Override
167 public void changeCampainCode( String oldCampaignCode, String newCampaignCode, Plugin plugin )
168 {
169 try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHANGEALL_CAMPAIGN_CODE, plugin ) )
170 {
171 daoUtil.setString( 1, newCampaignCode );
172 daoUtil.setString( 2, oldCampaignCode );
173 daoUtil.executeUpdate( );
174 }
175 }
176
177
178
179
180 @Override
181 public void delete( int nKey, Plugin plugin )
182 {
183 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
184 daoUtil.setInt( 1, nKey );
185 daoUtil.executeUpdate( );
186 daoUtil.free( );
187 }
188
189
190
191
192 @Override
193 public void store( CampaignTheme campaignTheme, Plugin plugin )
194 {
195 int nCpt = 1;
196 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
197
198 daoUtil.setInt( nCpt++, campaignTheme.getId( ) );
199 daoUtil.setString( nCpt++, campaignTheme.getCodeCampaign( ) );
200 daoUtil.setString( nCpt++, campaignTheme.getCode( ) );
201 daoUtil.setString( nCpt++, campaignTheme.getTitle( ) );
202 daoUtil.setString( nCpt++, campaignTheme.getDescription( ) );
203 daoUtil.setBoolean( nCpt++, campaignTheme.getActive( ) );
204 File image = campaignTheme.getImage( );
205 if ( image != null )
206 {
207 daoUtil.setInt( nCpt++, image.getIdFile( ) );
208 }
209 else
210 {
211 daoUtil.setIntNull( nCpt++ );
212 }
213 daoUtil.setString( nCpt++, campaignTheme.getFrontRgb( ) );
214 daoUtil.setInt( nCpt++, campaignTheme.getId( ) );
215
216 daoUtil.executeUpdate( );
217 daoUtil.free( );
218 }
219
220
221
222
223 @Override
224 public Collection<CampaignTheme> selectCampaignThemesList( Plugin plugin )
225 {
226 Collection<CampaignTheme> campaignThemeList = new ArrayList<CampaignTheme>( );
227 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
228 daoUtil.executeQuery( );
229
230 while ( daoUtil.next( ) )
231 {
232 CampaignTheme campaignTheme = getRow( daoUtil );
233
234 campaignThemeList.add( campaignTheme );
235 }
236
237 daoUtil.free( );
238 return campaignThemeList;
239 }
240
241
242
243
244 @Override
245 public Collection<Integer> selectIdCampaignThemesList( Plugin plugin )
246 {
247 Collection<Integer> campaignThemeList = new ArrayList<Integer>( );
248 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_ID, plugin );
249 daoUtil.executeQuery( );
250
251 while ( daoUtil.next( ) )
252 {
253 campaignThemeList.add( daoUtil.getInt( 1 ) );
254 }
255
256 daoUtil.free( );
257 return campaignThemeList;
258 }
259
260 private CampaignTheme getRow( DAOUtil daoUtil )
261 {
262 int nCpt = 1;
263 CampaignThemetorybudget/business/campaign/CampaignTheme.html#CampaignTheme">CampaignTheme campaignTheme = new CampaignTheme( );
264
265 campaignTheme.setId( daoUtil.getInt( nCpt++ ) );
266 campaignTheme.setCodeCampaign( daoUtil.getString( nCpt++ ) );
267 campaignTheme.setCode( daoUtil.getString( nCpt++ ) );
268 campaignTheme.setTitle( daoUtil.getString( nCpt++ ) );
269 campaignTheme.setDescription( daoUtil.getString( nCpt++ ) );
270 campaignTheme.setActive( daoUtil.getBoolean( nCpt++ ) );
271 campaignTheme.setImage( FileHome.findByPrimaryKey( daoUtil.getInt( nCpt++ ) ) );
272 campaignTheme.setFrontRgb( daoUtil.getString( nCpt++ ) );
273
274 return campaignTheme;
275 }
276
277
278
279
280 @Override
281 public Collection<CampaignTheme> selectCampaignThemesListByCampaign( String campaignCode, Plugin plugin )
282 {
283 Collection<CampaignTheme> campaignThemeList = new ArrayList<CampaignTheme>( );
284 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL_BY_CAMPAIGN, plugin );
285 daoUtil.setString( 1, campaignCode );
286 daoUtil.executeQuery( );
287
288 while ( daoUtil.next( ) )
289 {
290 CampaignTheme campaignTheme = getRow( daoUtil );
291
292 campaignThemeList.add( campaignTheme );
293 }
294
295 daoUtil.free( );
296 return campaignThemeList;
297 }
298
299
300
301
302 @Override
303 public Map<String, List<CampaignTheme>> selectCampaignThemesMapByCampaign( Plugin plugin )
304 {
305 Map<String, List<CampaignTheme>> campaignThemeMap = new HashMap<String, List<CampaignTheme>>( );
306 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECTALL, plugin );
307 daoUtil.executeQuery( );
308
309 while ( daoUtil.next( ) )
310 {
311 CampaignTheme campaignTheme = getRow( daoUtil );
312
313 List<CampaignTheme> campaignThemeList = campaignThemeMap.get( campaignTheme.getCodeCampaign( ) );
314 if ( campaignThemeList == null )
315 {
316 campaignThemeList = new ArrayList<CampaignTheme>( );
317 campaignThemeMap.put( campaignTheme.getCodeCampaign( ), campaignThemeList );
318 }
319 campaignThemeList.add( campaignTheme );
320 }
321
322 daoUtil.free( );
323 return campaignThemeMap;
324 }
325 }