1 package fr.paris.lutece.plugins.htmldocs.business.portlet;
2
3 import java.sql.Timestamp;
4 import java.util.ArrayList;
5 import java.util.Collection;
6 import java.util.Date;
7 import java.util.List;
8
9 import fr.paris.lutece.portal.service.plugin.Plugin;
10 import fr.paris.lutece.util.sql.DAOUtil;
11
12 public class HtmlDocPublicationDAO implements IHtmlDocPublicationDAO {
13
14
15
16
17 private static final String SQL_QUERY_INSERT_HTMLDOCS_PORTLET = "INSERT INTO htmldocs_list_portlet_htmldocs ( id_portlet , id_html_doc, date_begin_publishing, date_end_publishing, status, document_order ) VALUES ( ? , ?, ?, ?, ?, ? )";
18 private static final String SQL_QUERY_DELETE_HTMLDOCS_PORTLET = " DELETE FROM htmldocs_list_portlet_htmldocs WHERE id_html_doc = ? ";
19 private static final String SQL_QUERY_DELETE_HTMLDOCS_PORTLET_BY_ID_PORTLET = " DELETE FROM htmldocs_list_portlet_htmldocs WHERE id_portlet = ? ";
20 private static final String SQL_QUERY_SELECT_CATEGORY_PORTLET = "SELECT id_portlet , id_html_doc, date_begin_publishing, date_end_publishing, status, document_order FROM htmldocs_list_portlet_htmldocs WHERE id_html_doc = ? order by document_order ";
21 private static final String SQL_QUERY_REMOVE_HTMLDOCS_PORTLET = " DELETE FROM htmldocs_list_portlet_htmldocs WHERE id_portlet = ? and id_html_doc= ?";
22
23
24 private static final String SQL_QUERY_UPDATE_HTMLDOCS_PORTLET = "UPDATE htmldocs_list_portlet_htmldocs set id_portlet= ?, id_html_doc= ?, date_begin_publishing= ?, date_end_publishing= ?, status= ?, document_order= ? WHERE id_html_doc= ?";
25 private static final String SQL_QUERY_SELECT_PUBLICATION_PORTLET = "SELECT id_portlet , id_html_doc, date_begin_publishing, date_end_publishing, status, document_order FROM htmldocs_list_portlet_htmldocs WHERE id_html_doc = ? and id_portlet = ? order by document_order";
26 private static final String SQL_QUERY_SELECT_PUBLICATION_ALL = "SELECT id_portlet , id_html_doc, date_begin_publishing, date_end_publishing, status, document_order FROM htmldocs_list_portlet_htmldocs order by document_order";
27 private static final String SQL_QUERY_SELECT_DOC_PUBLICATION_BY_PORTLET = "SELECT id_portlet , id_html_doc, date_begin_publishing, date_end_publishing, status, document_order FROM htmldocs_list_portlet_htmldocs WHERE id_portlet = ? order by document_order ";
28 private static final String SQL_QUERY_SELECT_BY_DATE_PUBLISHING_AND_STATUS = " SELECT id_portlet, id_html_doc, document_order, date_begin_publishing FROM htmldocs_list_portlet_htmldocs WHERE date_begin_publishing <= ? AND date_end_publishing >= ? AND status = ? ORDER BY document_order ";
29 private static final String SQL_QUERY_SELECT_BY_PORTLET_ID_AND_STATUS = " SELECT DISTINCT pub.id_html_doc FROM htmldocs_list_portlet_htmldocs pub WHERE pub.status = ? AND pub.date_begin_publishing <= ? AND pub.date_end_publishing >= ? AND pub.id_portlet IN ";
30
31 private static final String SQL_FILTER_BEGIN = " (";
32 private static final String SQL_TAGS_END = ") ";
33 private static final String CONSTANT_QUESTION_MARK = "?";
34 private static final String CONSTANT_COMMA = ",";
35
36
37
38
39
40
41
42 @Override
43 public void insertHtmlsDocsId( HtmlDocPublication htmlDocPublication, Plugin plugin )
44 {
45
46 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_HTMLDOCS_PORTLET , plugin);
47
48
49 daoUtil.setInt( 1, htmlDocPublication.getIdPortlet( ) );
50 daoUtil.setInt( 2, htmlDocPublication.getIdDocument( ) );
51 daoUtil.setDate(3, htmlDocPublication.getDateBeginPublishing( ));
52 daoUtil.setDate(4, htmlDocPublication.getDateEndPublishing( ));
53 daoUtil.setInt( 5, htmlDocPublication.getStatus( ) );
54 daoUtil.setInt( 6, htmlDocPublication.getDocumentOrder( ) );
55
56 daoUtil.executeUpdate( );
57 daoUtil.free( );
58
59 }
60
61
62
63
64 @Override
65 public void store( HtmlDocPublication htmlDocPublication, Plugin plugin )
66 {
67
68 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_HTMLDOCS_PORTLET, plugin );
69 daoUtil.setInt( 1, htmlDocPublication.getIdPortlet( ) );
70 daoUtil.setInt( 2, htmlDocPublication.getIdDocument( ) );
71 daoUtil.setDate(3, htmlDocPublication.getDateBeginPublishing( ));
72 daoUtil.setDate(4, htmlDocPublication.getDateEndPublishing( ));
73 daoUtil.setInt( 5, htmlDocPublication.getStatus( ) );
74 daoUtil.setInt( 6, htmlDocPublication.getDocumentOrder( ) );
75
76 daoUtil.setInt( 7, htmlDocPublication.getIdDocument( ) );
77
78
79 daoUtil.executeUpdate( );
80
81 daoUtil.free( );
82
83 }
84
85
86
87
88 @Override
89 public void deleteHtmlsDocsId( int nDocId, Plugin plugin )
90 {
91 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_HTMLDOCS_PORTLET, plugin );
92 daoUtil.setInt( 1, nDocId );
93 daoUtil.executeUpdate( );
94 daoUtil.free( );
95 }
96
97
98
99
100 @Override
101 public void deleteHtmlsDocByIdPortlet( int nIdPortlet, Plugin plugin )
102 {
103 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_HTMLDOCS_PORTLET_BY_ID_PORTLET, plugin );
104 daoUtil.setInt( 1, nIdPortlet );
105 daoUtil.executeUpdate( );
106 daoUtil.free( );
107 }
108
109
110
111 @Override
112 public void remove( int nDocId, int nIdPortlet, Plugin plugin )
113 {
114 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_HTMLDOCS_PORTLET, plugin );
115 daoUtil.setInt( 1, nIdPortlet );
116 daoUtil.setInt( 2, nDocId );
117
118 daoUtil.executeUpdate( );
119 daoUtil.free( );
120 }
121
122
123
124
125 @Override
126 public List<HtmlDocPublication> loadHtmlsDocsId( int nDocId, Plugin plugin )
127 {
128 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CATEGORY_PORTLET, plugin );
129 daoUtil.setInt( 1, nDocId );
130 daoUtil.executeQuery( );
131
132 List<HtmlDocPublication> nListIdCategory = new ArrayList<HtmlDocPublication>( );
133
134 while ( daoUtil.next( ) )
135 {
136
137 HtmlDocPublication htmldocPub= new HtmlDocPublication();
138 htmldocPub.setIdPortlet(daoUtil.getInt( 1 ));
139 htmldocPub.setIdDocument(daoUtil.getInt( 2 ));
140 htmldocPub.setDateBeginPublishing(daoUtil.getDate( 3 ));
141 htmldocPub.setDateEndPublishing(daoUtil.getDate( 4 ));
142 htmldocPub.setStatus(daoUtil.getInt( 5 ));
143 htmldocPub.setDocumentOrder(daoUtil.getInt( 6 ));
144
145 nListIdCategory.add( htmldocPub );
146 }
147
148 daoUtil.free( );
149
150 return nListIdCategory;
151 }
152
153
154
155
156 @Override
157 public List<HtmlDocPublication> loadHtmlsDocsByPortlet( int nIdPortlet, Plugin plugin )
158 {
159 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_DOC_PUBLICATION_BY_PORTLET, plugin );
160 daoUtil.setInt( 1, nIdPortlet );
161 daoUtil.executeQuery( );
162
163 List<HtmlDocPublication> nListIdCategory = new ArrayList<HtmlDocPublication>( );
164
165 while ( daoUtil.next( ) )
166 {
167
168 HtmlDocPublication htmldocPub= new HtmlDocPublication();
169 htmldocPub.setIdPortlet(daoUtil.getInt( 1 ));
170 htmldocPub.setIdDocument(daoUtil.getInt( 2 ));
171 htmldocPub.setDateBeginPublishing(daoUtil.getDate( 3 ));
172 htmldocPub.setDateEndPublishing(daoUtil.getDate( 4 ));
173 htmldocPub.setStatus(daoUtil.getInt( 5 ));
174 htmldocPub.setDocumentOrder(daoUtil.getInt( 6 ));
175
176 nListIdCategory.add( htmldocPub );
177 }
178
179 daoUtil.free( );
180
181 return nListIdCategory;
182 }
183
184
185
186
187
188
189 @Override
190 public HtmlDocPublication loadHtmlsDocsPublication( int nDocId, int nPortletId, Plugin plugin )
191 {
192 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PUBLICATION_PORTLET, plugin );
193 daoUtil.setInt( 1, nDocId );
194 daoUtil.setInt( 2, nPortletId );
195 daoUtil.executeQuery( );
196
197 HtmlDocPublication htmldocPub= new HtmlDocPublication();
198
199 if ( daoUtil.next( ) )
200 {
201
202 htmldocPub.setIdPortlet(daoUtil.getInt( 1 ));
203 htmldocPub.setIdDocument(daoUtil.getInt( 2 ));
204 htmldocPub.setDateBeginPublishing(daoUtil.getDate( 3 ));
205 htmldocPub.setDateEndPublishing(daoUtil.getDate( 4 ));
206 htmldocPub.setStatus(daoUtil.getInt( 5 ));
207 htmldocPub.setDocumentOrder(daoUtil.getInt( 6 ));
208
209
210 }
211
212 daoUtil.free( );
213
214 return htmldocPub;
215 }
216
217
218
219
220 @Override
221 public List<HtmlDocPublication> loadAllHtmlsDocsPublication( Plugin plugin )
222 {
223 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_PUBLICATION_ALL, plugin );
224 daoUtil.executeQuery( );
225
226 List<HtmlDocPublication> nListIdCategory = new ArrayList<HtmlDocPublication>( );
227
228 while ( daoUtil.next( ) )
229 {
230
231 HtmlDocPublication htmldocPub= new HtmlDocPublication();
232 htmldocPub.setIdPortlet(daoUtil.getInt( 1 ));
233 htmldocPub.setIdDocument(daoUtil.getInt( 2 ));
234 htmldocPub.setDateBeginPublishing(daoUtil.getDate( 3 ));
235 htmldocPub.setDateEndPublishing(daoUtil.getDate( 4 ));
236 htmldocPub.setStatus(daoUtil.getInt( 5 ));
237 htmldocPub.setDocumentOrder(daoUtil.getInt( 6 ));
238
239 nListIdCategory.add( htmldocPub );
240 }
241
242 daoUtil.free( );
243
244 return nListIdCategory;
245 }
246
247
248
249
250 @Override
251 public Collection<HtmlDocPublication> selectSinceDatePublishingAndStatus( Date datePublishing, Date dateEndPublication, int nStatus, Plugin plugin )
252 {
253 Collection<HtmlDocPublication> listDocumentPublication = new ArrayList<HtmlDocPublication>( );
254 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_BY_DATE_PUBLISHING_AND_STATUS, plugin );
255 daoUtil.setTimestamp( 1, new Timestamp( datePublishing.getTime( ) ) );
256 daoUtil.setTimestamp( 2, new Timestamp( dateEndPublication.getTime( ) ) );
257
258 daoUtil.setInt( 3, nStatus );
259 daoUtil.executeQuery( );
260
261 while ( daoUtil.next( ) )
262 {
263 HtmlDocPublication documentPublication = new HtmlDocPublication( );
264 documentPublication.setIdPortlet( daoUtil.getInt( 1 ) );
265 documentPublication.setIdDocument( daoUtil.getInt( 2 ) );
266 documentPublication.setDocumentOrder( daoUtil.getInt( 3 ) );
267 documentPublication.setStatus( nStatus );
268 documentPublication.setDateBeginPublishing( daoUtil.getDate( 4 ) );
269 listDocumentPublication.add( documentPublication );
270 }
271
272 daoUtil.free( );
273
274 return listDocumentPublication;
275 }
276
277
278
279
280 @Override
281 public List<Integer> getPublishedDocumentsIdsListByPortletIds( int[] nPortletsIds, Date datePublishing,
282 Date dateEndPublishing, Plugin plugin )
283 {
284 List<Integer> listIds = new ArrayList<Integer>( );
285 if ( nPortletsIds == null || nPortletsIds.length == 0 )
286 {
287 return listIds;
288 }
289 StringBuilder sbSql = new StringBuilder( SQL_QUERY_SELECT_BY_PORTLET_ID_AND_STATUS );
290 sbSql.append( SQL_FILTER_BEGIN );
291
292 for ( int i = 0; i < nPortletsIds.length; i++ )
293 {
294 sbSql.append( CONSTANT_QUESTION_MARK );
295 if ( i + 1 < nPortletsIds.length )
296 {
297 sbSql.append( CONSTANT_COMMA );
298 }
299 }
300 sbSql.append( SQL_TAGS_END + "order by document_order" );
301
302 DAOUtil daoUtil = new DAOUtil( sbSql.toString( ), plugin );
303 int nIndex = 1;
304 daoUtil.setInt( nIndex++, 1 );
305 daoUtil.setTimestamp( nIndex++, new Timestamp( datePublishing.getTime( ) ) );
306 daoUtil.setTimestamp( nIndex++, new Timestamp( dateEndPublishing.getTime( ) ) );
307
308 for ( int nPortletId : nPortletsIds )
309 {
310 daoUtil.setInt( nIndex++, nPortletId );
311 }
312 daoUtil.executeQuery( );
313 while ( daoUtil.next( ) )
314 {
315 listIds.add( daoUtil.getInt( 1 ) );
316 }
317 daoUtil.free( );
318 return listIds;
319
320 }
321
322
323
324 }