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.topic;
35
36 import fr.paris.lutece.portal.service.plugin.Plugin;
37 import fr.paris.lutece.util.sql.DAOUtil;
38
39 import java.util.ArrayList;
40 import java.util.List;
41
42
43
44
45 public class NewsletterTopicDao implements INewsletterTopicDAO
46 {
47 private static final String SQL_QUERY_NEW_PRIMARY_KEY = " SELECT MAX(id_topic) FROM newsletter_topic ";
48
49 private static final String SQL_QUERY_SELECT = " SELECT id_topic, id_newsletter, topic_type, title, topic_order, section FROM newsletter_topic WHERE id_topic = ? ";
50 private static final String SQL_QUERY_SELECT_ALL_BY_ID_NEWSLETTER = " SELECT id_topic, id_newsletter, topic_type, title, topic_order, section FROM newsletter_topic WHERE id_newsletter = ? ORDER BY section, topic_order asc ";
51 private static final String SQL_QUERY_SELECT_ALL_BY_ID_NEWSLETTER_AND_ORDER = " SELECT id_topic, id_newsletter, topic_type, title, topic_order, section FROM newsletter_topic WHERE id_newsletter = ? AND topic_order = ? AND section = ? ";
52 private static final String SQL_QUERY_INSERT = " INSERT INTO newsletter_topic (id_topic, id_newsletter, topic_type, title, topic_order, section) VALUES (?,?,?,?,?,?) ";
53 private static final String SQL_QUERY_UPDATE = " UPDATE newsletter_topic SET id_newsletter = ?, topic_type = ?, title = ?, topic_order = ?, section = ? WHERE id_topic = ? ";
54 private static final String SQL_QUERY_DELETE = " DELETE FROM newsletter_topic WHERE id_topic = ? ";
55 private static final String SQL_QUERY_UPDATE_ORDER = " UPDATE newsletter_topic SET topic_order = ? WHERE id_topic = ? ";
56 private static final String SQL_QUERY_FIND_LAST_ORDER = " SELECT MAX(topic_order) FROM newsletter_topic WHERE id_newsletter = ? AND section = ? ";
57 private static final String SQL_QUERY_FILL_ORDER_BLANK = " UPDATE newsletter_topic SET topic_order = topic_order - 1 WHERE id_newsletter = ? AND section = ? and topic_order > ? ";
58 private static final String SQL_QUERY_REMOVE_ALL_BY_ID_NEWSLETTER = " DELETE FROM newsletter_topic WHERE id_newsletter = ? ";
59
60
61
62
63 @Override
64 public NewsletterTopic findByPrimaryKey( int nId, Plugin plugin )
65 {
66 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT, plugin );
67 NewsletterTopic topic = null;
68 daoUtil.setInt( 1, nId );
69 daoUtil.executeQuery( );
70 if ( daoUtil.next( ) )
71 {
72 int nIndex = 1;
73 topic = new NewsletterTopic( );
74 topic.setId( daoUtil.getInt( nIndex++ ) );
75 topic.setIdNewsletter( daoUtil.getInt( nIndex++ ) );
76 topic.setTopicTypeCode( daoUtil.getString( nIndex++ ) );
77 topic.setTitle( daoUtil.getString( nIndex++ ) );
78 topic.setOrder( daoUtil.getInt( nIndex++ ) );
79 topic.setSection( daoUtil.getInt( nIndex ) );
80 }
81 daoUtil.free( );
82 return topic;
83 }
84
85
86
87
88 @Override
89 public void insert( NewsletterTopic newsletterTopic, Plugin plugin )
90 {
91 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
92 newsletterTopic.setId( newPrimaryKey( plugin ) );
93 daoUtil.setInt( 1, newsletterTopic.getId( ) );
94 daoUtil.setInt( 2, newsletterTopic.getIdNewsletter( ) );
95 daoUtil.setString( 3, newsletterTopic.getTopicTypeCode( ) );
96 daoUtil.setString( 4, newsletterTopic.getTitle( ) );
97 daoUtil.setInt( 5, newsletterTopic.getOrder( ) );
98 daoUtil.setInt( 6, newsletterTopic.getSection( ) );
99 daoUtil.executeUpdate( );
100 daoUtil.free( );
101 }
102
103
104
105
106 @Override
107 public void update( NewsletterTopic newsletterTopic, Plugin plugin )
108 {
109 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
110 daoUtil.setInt( 1, newsletterTopic.getIdNewsletter( ) );
111 daoUtil.setString( 2, newsletterTopic.getTopicTypeCode( ) );
112 daoUtil.setString( 3, newsletterTopic.getTitle( ) );
113 daoUtil.setInt( 4, newsletterTopic.getOrder( ) );
114 daoUtil.setInt( 5, newsletterTopic.getSection( ) );
115 daoUtil.setInt( 6, newsletterTopic.getId( ) );
116 daoUtil.executeUpdate( );
117 daoUtil.free( );
118 }
119
120
121
122
123 @Override
124 public void remove( int nId, Plugin plugin )
125 {
126 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE, plugin );
127 daoUtil.setInt( 1, nId );
128 daoUtil.executeUpdate( );
129 daoUtil.free( );
130 }
131
132
133
134
135 @Override
136 public List<NewsletterTopic> findAllByIdNewsletter( int nIdNewsletter, Plugin plugin )
137 {
138 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_BY_ID_NEWSLETTER, plugin );
139 List<NewsletterTopic> listNewsletterTopics = new ArrayList<NewsletterTopic>( );
140 daoUtil.setInt( 1, nIdNewsletter );
141 daoUtil.executeQuery( );
142 while ( daoUtil.next( ) )
143 {
144 int nIndex = 1;
145 NewsletterTopicsletter/business/topic/NewsletterTopic.html#NewsletterTopic">NewsletterTopic topic = new NewsletterTopic( );
146 topic.setId( daoUtil.getInt( nIndex++ ) );
147 topic.setIdNewsletter( daoUtil.getInt( nIndex++ ) );
148 topic.setTopicTypeCode( daoUtil.getString( nIndex++ ) );
149 topic.setTitle( daoUtil.getString( nIndex++ ) );
150 topic.setOrder( daoUtil.getInt( nIndex++ ) );
151 topic.setSection( daoUtil.getInt( nIndex ) );
152 listNewsletterTopics.add( topic );
153 }
154 daoUtil.free( );
155 return listNewsletterTopics;
156 }
157
158
159
160
161 @Override
162 public void updateNewsletterTopicOrder( int nIdNewsletterTopic, int nNewOrder, Plugin plugin )
163 {
164 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_ORDER, plugin );
165 daoUtil.setInt( 1, nNewOrder );
166 daoUtil.setInt( 2, nIdNewsletterTopic );
167 daoUtil.executeUpdate( );
168 daoUtil.free( );
169 }
170
171
172
173
174 @Override
175 public List<NewsletterTopic> findByNewsletterIdAndOrder( int nIdNewsletter, int nOrder, int nSection, Plugin plugin )
176 {
177 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_ALL_BY_ID_NEWSLETTER_AND_ORDER, plugin );
178 List<NewsletterTopic> listNewsletterTopics = new ArrayList<NewsletterTopic>( );
179 daoUtil.setInt( 1, nIdNewsletter );
180 daoUtil.setInt( 2, nOrder );
181 daoUtil.setInt( 3, nSection );
182 daoUtil.executeQuery( );
183 while ( daoUtil.next( ) )
184 {
185 int nIndex = 1;
186 NewsletterTopicsletter/business/topic/NewsletterTopic.html#NewsletterTopic">NewsletterTopic topic = new NewsletterTopic( );
187 topic.setId( daoUtil.getInt( nIndex++ ) );
188 topic.setIdNewsletter( daoUtil.getInt( nIndex++ ) );
189 topic.setTopicTypeCode( daoUtil.getString( nIndex++ ) );
190 topic.setTitle( daoUtil.getString( nIndex++ ) );
191 topic.setOrder( daoUtil.getInt( nIndex++ ) );
192 topic.setSection( daoUtil.getInt( nIndex ) );
193 listNewsletterTopics.add( topic );
194 }
195 daoUtil.free( );
196 return listNewsletterTopics;
197 }
198
199
200
201
202 @Override
203 public int getNewOrder( int nIdNewsletter, int nSection, Plugin plugin )
204 {
205 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_LAST_ORDER, plugin );
206 daoUtil.setInt( 1, nIdNewsletter );
207 daoUtil.setInt( 2, nSection );
208 daoUtil.executeQuery( );
209 int nNewOrder = 1;
210 if ( daoUtil.next( ) )
211 {
212
213 nNewOrder = daoUtil.getInt( 1 ) + 1;
214 }
215 daoUtil.free( );
216 return nNewOrder;
217 }
218
219
220
221
222 @Override
223 public int getLastOrder( int nIdNewsletter, int nSection, Plugin plugin )
224 {
225 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_LAST_ORDER, plugin );
226 daoUtil.setInt( 1, nIdNewsletter );
227 daoUtil.setInt( 2, nSection );
228 daoUtil.executeQuery( );
229 int nLastOrder = 1;
230 if ( daoUtil.next( ) )
231 {
232 nLastOrder = daoUtil.getInt( 1 );
233 }
234 daoUtil.free( );
235 return nLastOrder;
236 }
237
238
239
240
241 @Override
242 public void fillBlankInOrder( int nIdNewsletter, int nOrder, int nSection, Plugin plugin )
243 {
244 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FILL_ORDER_BLANK, plugin );
245 daoUtil.setInt( 1, nIdNewsletter );
246 daoUtil.setInt( 2, nSection );
247 daoUtil.setInt( 3, nOrder );
248 daoUtil.executeUpdate( );
249 daoUtil.free( );
250 }
251
252
253
254
255 @Override
256 public void removeAllByIdNewsletter( int nIdNewsletter, Plugin plugin )
257 {
258 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_ALL_BY_ID_NEWSLETTER, plugin );
259 daoUtil.setInt( 1, nIdNewsletter );
260 daoUtil.executeUpdate( );
261 daoUtil.free( );
262 }
263
264
265
266
267
268
269 private int newPrimaryKey( Plugin plugin )
270 {
271 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PRIMARY_KEY, plugin );
272 daoUtil.executeQuery( );
273 int nId = 1;
274 if ( daoUtil.next( ) )
275 {
276 nId = daoUtil.getInt( 1 ) + 1;
277 }
278 daoUtil.free( );
279 return nId;
280 }
281 }