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.newsletter.business;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.ReferenceList;
38 import fr.paris.lutece.util.sql.DAOUtil;
39
40 import java.util.ArrayList;
41 import java.util.Collection;
42 import java.util.List;
43
44
45
46
47 public final class NewsLetterTemplateDAO implements INewsLetterTemplateDAO
48 {
49
50 private static final String SQL_QUERY_SELECT_ALL = "SELECT id_template, description, file_name, picture, workgroup_key, topic_type, sections FROM newsletter_template ORDER BY id_template asc ";
51 private static final String SQL_QUERY_SELECT_ALL_BY_WORKGOUP_KEY = "SELECT id_template, description, file_name, picture, workgroup_key, topic_type, sections FROM newsletter_template WHERE workgroup_key = ?";
52 private static final String SQL_QUERY_SELECT_ALL_REFERENCE = " SELECT id_template, description FROM newsletter_template ";
53 private static final String SQL_QUERY_SELECT = "SELECT id_template, description, file_name, picture, workgroup_key, topic_type, sections FROM newsletter_template WHERE id_template = ? ";
54 private static final String SQL_QUERY_SELECT_TEMPLATES_IDS_BY_TYPE = "SELECT id_template, description FROM newsletter_template WHERE topic_type = ?";
55 private static final String SQL_QUERY_SELECT_TEMPLATES_BY_TYPE = "SELECT id_template, description, file_name, picture, workgroup_key, topic_type, sections FROM newsletter_template WHERE topic_type= ?";
56 private static final String SQL_QUERY_INSERT = "INSERT INTO newsletter_template ( id_template, description, file_name, picture, workgroup_key, topic_type, sections ) VALUES ( ?, ?, ?, ?, ?, ?, ? )";
57 private static final String SQL_QUERY_NEW_PRIMARY_KEY = "SELECT max( id_template ) FROM newsletter_template";
58 private static final String SQL_QUERY_UPDATE = "UPDATE newsletter_template SET description = ?, file_name = ?, picture = ?, workgroup_key = ?, topic_type = ?, sections = ? WHERE id_template = ?";
59 private static final String SQL_QUERY_DELETE = "DELETE FROM newsletter_template WHERE id_template = ? ";
60
61
62
63
64
65
66
67 @Override
68 public Collection<NewsLetterTemplate> selectTemplatesList( Plugin plugin )
69 {
70 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL, plugin );
71 daoUtil.executeQuery( );
72
73 ArrayList<NewsLetterTemplate> list = new ArrayList<NewsLetterTemplate>( );
74
75 while ( daoUtil.next( ) )
76 {
77 NewsLetterTemplateusiness/NewsLetterTemplate.html#NewsLetterTemplate">NewsLetterTemplate template = new NewsLetterTemplate( );
78
79 template.setId( daoUtil.getInt( 1 ) );
80 template.setDescription( daoUtil.getString( 2 ) );
81 template.setFileName( daoUtil.getString( 3 ) );
82 template.setPicture( daoUtil.getString( 4 ) );
83 template.setWorkgroup( daoUtil.getString( 5 ) );
84 template.setTopicType( daoUtil.getString( 6 ) );
85 template.setSectionNumber( daoUtil.getInt( 7 ) );
86
87 list.add( template );
88 }
89
90 daoUtil.free( );
91
92 return list;
93 }
94
95
96
97
98
99
100
101 @Override
102 public ReferenceList selectTemplatesListByType( String strTopicType, Plugin plugin )
103 {
104 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_TEMPLATES_IDS_BY_TYPE, plugin );
105
106 daoUtil.setString( 1, strTopicType );
107
108 daoUtil.executeQuery( );
109
110 ReferenceList list = new ReferenceList( );
111
112 while ( daoUtil.next( ) )
113 {
114 list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
115 }
116
117 daoUtil.free( );
118
119 return list;
120 }
121
122
123
124
125 @Override
126 public List<NewsLetterTemplate> selectTemplatesCollectionByType( String strTopicType, Plugin plugin )
127 {
128 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_TEMPLATES_BY_TYPE, plugin );
129
130 daoUtil.setString( 1, strTopicType );
131
132 daoUtil.executeQuery( );
133
134 List<NewsLetterTemplate> list = new ArrayList<NewsLetterTemplate>( );
135
136 while ( daoUtil.next( ) )
137 {
138 NewsLetterTemplateusiness/NewsLetterTemplate.html#NewsLetterTemplate">NewsLetterTemplate template = new NewsLetterTemplate( );
139
140 template.setId( daoUtil.getInt( 1 ) );
141 template.setDescription( daoUtil.getString( 2 ) );
142 template.setFileName( daoUtil.getString( 3 ) );
143 template.setPicture( daoUtil.getString( 4 ) );
144 template.setWorkgroup( daoUtil.getString( 5 ) );
145 template.setTopicType( daoUtil.getString( 6 ) );
146 template.setSectionNumber( daoUtil.getInt( 7 ) );
147
148 list.add( template );
149 }
150
151 daoUtil.free( );
152
153 return list;
154 }
155
156
157
158
159 @Override
160 public void insert( NewsLetterTemplate newsletter, Plugin plugin )
161 {
162 newsletter.setId( newPrimaryKey( plugin ) );
163
164 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
165
166 daoUtil.setInt( 1, newsletter.getId( ) );
167 daoUtil.setString( 2, newsletter.getDescription( ) );
168 daoUtil.setString( 3, newsletter.getFileName( ) );
169 daoUtil.setString( 4, newsletter.getPicture( ) );
170 daoUtil.setString( 5, newsletter.getWorkgroup( ) );
171 daoUtil.setString( 6, newsletter.getTopicType( ) );
172 daoUtil.setInt( 7, newsletter.getSectionNumber( ) );
173
174 daoUtil.executeUpdate( );
175 daoUtil.free( );
176 }
177
178
179
180
181 @Override
182 public NewsLetterTemplate load( int nTemplateId, Plugin plugin )
183 {
184 NewsLetterTemplateusiness/NewsLetterTemplate.html#NewsLetterTemplate">NewsLetterTemplate template = new NewsLetterTemplate( );
185
186 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
187
188 daoUtil.setInt( 1, nTemplateId );
189
190 daoUtil.executeQuery( );
191
192 if ( daoUtil.next( ) )
193 {
194 template.setId( daoUtil.getInt( 1 ) );
195 template.setDescription( daoUtil.getString( 2 ) );
196 template.setFileName( daoUtil.getString( 3 ) );
197 template.setPicture( daoUtil.getString( 4 ) );
198 template.setWorkgroup( daoUtil.getString( 5 ) );
199 template.setTopicType( daoUtil.getString( 6 ) );
200 template.setSectionNumber( daoUtil.getInt( 7 ) );
201 }
202
203 daoUtil.free( );
204
205 return template;
206 }
207
208
209
210
211 @Override
212 public void store( NewsLetterTemplate newsLetterTemplate, Plugin plugin )
213 {
214 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
215
216 daoUtil.setString( 1, newsLetterTemplate.getDescription( ) );
217 daoUtil.setString( 2, newsLetterTemplate.getFileName( ) );
218 daoUtil.setString( 3, newsLetterTemplate.getPicture( ) );
219 daoUtil.setString( 4, newsLetterTemplate.getWorkgroup( ) );
220 daoUtil.setString( 5, newsLetterTemplate.getTopicType( ) );
221 daoUtil.setInt( 6, newsLetterTemplate.getSectionNumber( ) );
222 daoUtil.setInt( 7, newsLetterTemplate.getId( ) );
223
224 daoUtil.executeUpdate( );
225 daoUtil.free( );
226 }
227
228
229
230
231 @Override
232 public void delete( int nNewsLetterTemplateId, Plugin plugin )
233 {
234 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
235 daoUtil.setInt( 1, nNewsLetterTemplateId );
236 daoUtil.executeUpdate( );
237 daoUtil.free( );
238 }
239
240
241
242
243 @Override
244 public ReferenceList selectTemplatesByRef( Plugin plugin )
245 {
246 ReferenceList listTemplates = new ReferenceList( );
247 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_REFERENCE, plugin );
248 daoUtil.executeQuery( );
249
250 while ( daoUtil.next( ) )
251 {
252 NewsLetterTemplateusiness/NewsLetterTemplate.html#NewsLetterTemplate">NewsLetterTemplate template = new NewsLetterTemplate( );
253 template.setId( daoUtil.getInt( 1 ) );
254 template.setDescription( daoUtil.getString( 2 ) );
255
256 listTemplates.addItem( template.getId( ), template.getDescription( ) );
257 }
258
259 daoUtil.free( );
260
261 return listTemplates;
262 }
263
264
265
266
267 @Override
268 public Collection<NewsLetterTemplate> selectTemplatesListByWorkgoup( String strWorkgroupKey, Plugin plugin )
269 {
270 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_BY_WORKGOUP_KEY, plugin );
271 daoUtil.setString( 1, strWorkgroupKey );
272 daoUtil.executeQuery( );
273
274 ArrayList<NewsLetterTemplate> list = new ArrayList<NewsLetterTemplate>( );
275
276 while ( daoUtil.next( ) )
277 {
278 NewsLetterTemplateusiness/NewsLetterTemplate.html#NewsLetterTemplate">NewsLetterTemplate template = new NewsLetterTemplate( );
279
280 template.setId( daoUtil.getInt( 1 ) );
281 template.setDescription( daoUtil.getString( 2 ) );
282 template.setFileName( daoUtil.getString( 3 ) );
283 template.setPicture( daoUtil.getString( 4 ) );
284 template.setWorkgroup( daoUtil.getString( 5 ) );
285 template.setTopicType( daoUtil.getString( 6 ) );
286 template.setSectionNumber( daoUtil.getInt( 7 ) );
287
288 list.add( template );
289 }
290
291 daoUtil.free( );
292
293 return list;
294 }
295
296
297
298
299
300
301
302
303 private int newPrimaryKey( Plugin plugin )
304 {
305 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PRIMARY_KEY, plugin );
306
307 int nKey;
308
309 daoUtil.executeQuery( );
310
311 if ( !daoUtil.next( ) )
312 {
313
314 nKey = 1;
315 }
316 else
317 {
318 nKey = daoUtil.getInt( 1 ) + 1;
319 }
320
321 daoUtil.free( );
322
323 return nKey;
324 }
325 }