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.document.business.portlet;
35
36 import fr.paris.lutece.portal.business.portlet.Portlet;
37 import fr.paris.lutece.util.ReferenceItem;
38 import fr.paris.lutece.util.ReferenceList;
39 import fr.paris.lutece.util.sql.DAOUtil;
40
41 import java.util.ArrayList;
42 import java.util.Collection;
43
44
45
46
47
48 public final class DocumentListPortletDAO implements IDocumentListPortletDAO
49 {
50 private static final String SQL_QUERY_INSERT = "INSERT INTO document_list_portlet ( id_portlet , code_document_type ) VALUES ( ? , ? )";
51 private static final String SQL_QUERY_SELECT = "SELECT id_portlet , code_document_type FROM document_list_portlet WHERE id_portlet = ? ";
52 private static final String SQL_QUERY_UPDATE = "UPDATE document_list_portlet SET id_portlet = ?, code_document_type = ? WHERE id_portlet = ? ";
53 private static final String SQL_QUERY_DELETE = "DELETE FROM document_list_portlet WHERE id_portlet= ? ";
54 private static final String SQL_QUERY_DELETE_PUBLISHED_DOCUMENT_PORTLET = " DELETE FROM document_published WHERE id_portlet = ? ";
55 private static final String SQL_QUERY_SELECT_DOCUMENTS_BY_TYPE_AND_CATEGORY = "SELECT DISTINCT b.id_portlet , a.name, a.date_update " +
56 "FROM document_list_portlet b " +
57 "LEFT JOIN document_published c ON b.id_portlet = c.id_portlet AND c.id_document= ? " +
58 "INNER JOIN core_portlet a ON b.id_portlet = a.id_portlet " +
59 "LEFT OUTER JOIN document_category_list_portlet d ON b.id_portlet = d.id_portlet " +
60 "INNER JOIN core_page f ON a.id_page = f.id_page " +
61 "WHERE c.id_portlet IS NULL AND b.code_document_type = ? AND (d.id_category IN (SELECT e.id_category " +
62 "FROM document_category_link e WHERE e.id_document = ?) OR d.id_category IS NULL) ";
63 private static final String SQL_QUERY_CHECK_IS_ALIAS = "SELECT id_alias FROM core_portlet_alias WHERE id_alias = ?";
64
65
66 private static final String SQL_QUERY_INSERT_CATEGORY_PORTLET = "INSERT INTO document_category_list_portlet ( id_portlet , id_category ) VALUES ( ? , ? )";
67 private static final String SQL_QUERY_DELETE_CATEGORY_PORTLET = " DELETE FROM document_category_list_portlet WHERE id_portlet = ? ";
68 private static final String SQL_QUERY_DELETE_AUTO_PUBLICATION_PORTLET = " DELETE FROM document_auto_publication WHERE id_portlet = ? ";
69 private static final String SQL_QUERY_SELECT_CATEGORY_PORTLET = "SELECT id_category FROM document_category_list_portlet WHERE id_portlet = ? ";
70 private static final String SQL_QUERY_CASE_AND = " AND ";
71
72
73
74
75
76
77
78
79
80 public void insert( Portlet portlet )
81 {
82 DocumentListPortlet/../../../../fr/paris/lutece/plugins/document/business/portlet/DocumentListPortlet.html#DocumentListPortlet">DocumentListPortlet p = (DocumentListPortlet) portlet;
83
84 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT );
85 daoUtil.setInt( 1, p.getId( ) );
86 daoUtil.setString( 2, p.getDocumentTypeCode( ) );
87
88 daoUtil.executeUpdate( );
89 daoUtil.free( );
90
91 insertCategory( portlet );
92 }
93
94
95
96
97
98 private void insertCategory( Portlet portlet )
99 {
100 DocumentListPortlet/../../../../fr/paris/lutece/plugins/document/business/portlet/DocumentListPortlet.html#DocumentListPortlet">DocumentListPortlet p = (DocumentListPortlet) portlet;
101
102 if ( p.getIdCategory( ) != null )
103 {
104 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_CATEGORY_PORTLET );
105
106 for ( int nIdCategory : p.getIdCategory( ) )
107 {
108 daoUtil.setInt( 1, p.getId( ) );
109 daoUtil.setInt( 2, nIdCategory );
110
111 daoUtil.executeUpdate( );
112 }
113
114 daoUtil.free( );
115 }
116 }
117
118
119
120
121
122
123
124 public void delete( int nPortletId )
125 {
126 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE );
127 daoUtil.setInt( 1, nPortletId );
128 daoUtil.executeUpdate( );
129 daoUtil.free( );
130
131 daoUtil = new DAOUtil( SQL_QUERY_DELETE_PUBLISHED_DOCUMENT_PORTLET );
132 daoUtil.setInt( 1, nPortletId );
133 daoUtil.executeUpdate( );
134 daoUtil.free( );
135
136 deleteCategories( nPortletId );
137 deleteAutoPublication( nPortletId );
138 }
139
140
141
142
143
144 private void deleteCategories( int nPortletId )
145 {
146 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_CATEGORY_PORTLET );
147 daoUtil.setInt( 1, nPortletId );
148 daoUtil.executeUpdate( );
149 daoUtil.free( );
150 }
151
152
153
154
155
156 private void deleteAutoPublication( int nPortletId )
157 {
158 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_AUTO_PUBLICATION_PORTLET );
159 daoUtil.setInt( 1, nPortletId );
160 daoUtil.executeUpdate( );
161 daoUtil.free( );
162 }
163
164
165
166
167
168
169
170 public Portlet load( int nPortletId )
171 {
172 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT );
173 daoUtil.setInt( 1, nPortletId );
174 daoUtil.executeQuery( );
175
176 DocumentListPortletbusiness/portlet/DocumentListPortlet.html#DocumentListPortlet">DocumentListPortlet portlet = new DocumentListPortlet( );
177
178 if ( daoUtil.next( ) )
179 {
180 portlet.setId( daoUtil.getInt( 1 ) );
181 portlet.setDocumentTypeCode( daoUtil.getString( 2 ) );
182 }
183
184 daoUtil.free( );
185
186 portlet.setIdCategory( loadCategories( nPortletId ) );
187
188 return portlet;
189 }
190
191
192
193
194
195
196 private int[] loadCategories( int nPortletId )
197 {
198 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_CATEGORY_PORTLET );
199 daoUtil.setInt( 1, nPortletId );
200 daoUtil.executeQuery( );
201
202 Collection<Integer> nListIdCategory = new ArrayList<Integer>( );
203
204 while ( daoUtil.next( ) )
205 {
206 nListIdCategory.add( daoUtil.getInt( 1 ) );
207 }
208
209 daoUtil.free( );
210
211 int[] nArrayIdCategory = new int[nListIdCategory.size( )];
212 int i = 0;
213
214 for ( Integer nIdCategory : nListIdCategory )
215 {
216 nArrayIdCategory[i++] = nIdCategory.intValue( );
217 }
218
219 return nArrayIdCategory;
220 }
221
222
223
224
225
226
227 public void store( Portlet portlet )
228 {
229 DocumentListPortlet/../../../../fr/paris/lutece/plugins/document/business/portlet/DocumentListPortlet.html#DocumentListPortlet">DocumentListPortlet p = (DocumentListPortlet) portlet;
230 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE );
231 daoUtil.setInt( 1, p.getId( ) );
232 daoUtil.setString( 2, p.getDocumentTypeCode( ) );
233 daoUtil.setInt( 3, p.getId( ) );
234
235 daoUtil.executeUpdate( );
236
237 daoUtil.free( );
238
239 deleteCategories( p.getId( ) );
240 insertCategory( p );
241 }
242
243
244
245
246
247
248
249
250 public Collection<ReferenceItem> selectByDocumentIdAndDocumentType( int nDocumentId, String strCodeDocumentType,
251 PortletOrder pOrder, PortletFilter pFilter )
252 {
253 StringBuilder strSQl = new StringBuilder( );
254 strSQl.append( SQL_QUERY_SELECT_DOCUMENTS_BY_TYPE_AND_CATEGORY );
255
256 String strFilter = ( pFilter != null ) ? pFilter.getSQLFilter( ) : null;
257
258 if ( strFilter != null )
259 {
260 strSQl.append( SQL_QUERY_CASE_AND );
261 strSQl.append( strFilter );
262 }
263
264 strSQl.append( pOrder.getSQLOrderBy( ) );
265
266 DAOUtil daoUtil = new DAOUtil( strSQl.toString( ) );
267
268 daoUtil.setInt( 1, nDocumentId );
269 daoUtil.setString( 2, strCodeDocumentType );
270 daoUtil.setInt( 3, nDocumentId );
271
272 if ( strFilter != null )
273 {
274 if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PAGE_NAME ) )
275 {
276 for ( int i = 0; i < pFilter.getPageName( ).length; i++ )
277 {
278 daoUtil.setString( i + 4, "%" + pFilter.getPageName( )[i] + "%" );
279 }
280 }
281 else if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PORTLET_NAME ) )
282 {
283 for ( int i = 0; i < pFilter.getPortletName( ).length; i++ )
284 {
285 daoUtil.setString( i + 4, "%" + pFilter.getPortletName( )[i] + "%" );
286 }
287 }
288 else if ( pFilter.getPortletFilterType( ).equals( PortletFilter.PAGE_ID ) )
289 {
290 daoUtil.setInt( 4, pFilter.getIdPage( ) );
291 }
292 }
293
294 daoUtil.executeQuery( );
295
296 ReferenceList list = new ReferenceList( );
297
298 while ( daoUtil.next( ) )
299 {
300 list.addItem( daoUtil.getInt( 1 ), daoUtil.getString( 2 ) );
301 }
302
303 daoUtil.free( );
304
305 return list;
306 }
307
308
309
310
311
312
313
314 public boolean checkIsAliasPortlet( int nPortletId )
315 {
316 boolean bIsAlias = false;
317 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_CHECK_IS_ALIAS );
318
319 daoUtil.setInt( 1, nPortletId );
320 daoUtil.executeQuery( );
321
322 if ( daoUtil.next( ) )
323 {
324 bIsAlias = true;
325 }
326
327 daoUtil.free( );
328
329 return bIsAlias;
330 }
331 }