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