1 package fr.paris.lutece.plugins.newsletter.modules.htmldocs.business;
2
3 import fr.paris.lutece.portal.service.plugin.Plugin;
4 import fr.paris.lutece.util.sql.DAOUtil;
5
6 import java.util.ArrayList;
7 import java.util.List;
8
9
10
11
12 public class NewsletterHtmlDocDAO implements INewsletterHtmlDocDAO
13 {
14 private static final String SQL_QUERY_SELECT_NEWSLETTER_DOCUMENT_TOPIC = " SELECT id_topic, id_template, use_tags FROM newsletter_htmldocs_topic WHERE id_topic = ? ";
15 private static final String SQL_QUERY_INSERT_NEWSLETTER_DOCUMENT_TOPIC = " INSERT INTO newsletter_htmldocs_topic(id_topic, id_template, use_tags) VALUES (?,?,?) ";
16 private static final String SQL_QUERY_UPDATE_NEWSLETTER_DOCUMENT_TOPIC = " UPDATE newsletter_htmldocs_topic SET id_template = ?, use_tags = ? WHERE id_topic = ? ";
17 private static final String SQL_QUERY_DELETE_NEWSLETTER_DOCUMENT_TOPIC = " DELETE FROM newsletter_htmldocs_topic WHERE id_topic = ? ";
18
19 private static final String SQL_QUERY_ASSOCIATE_NEWSLETTER_CATEGORY_LIST = "INSERT INTO newsletter_htmldocs_tag ( id_topic , id_tag ) VALUES ( ?, ? ) ";
20 private static final String SQL_QUERY_DELETE_NEWSLETTER_CATEGORY_LIST = "DELETE FROM newsletter_htmldocs_tag WHERE id_topic = ?";
21 private static final String SQL_QUERY_SELECT_NEWSLETTER_CATEGORY_IDS = "SELECT id_tag FROM newsletter_htmldocs_tag WHERE id_topic = ?";
22 private static final String SQL_QUERY_ASSOCIATE_NEWSLETTER_PORTLET = "INSERT INTO newsletter_htmldocs_portlet ( id_topic , id_portlet ) VALUES ( ?, ? ) ";
23 private static final String SQL_QUERY_SELECT_NEWSLETTER_PORTLET_IDS = " SELECT id_portlet FROM newsletter_htmldocs_portlet WHERE id_topic = ? ";
24 private static final String SQL_QUERY_DELETE_NEWSLETTER_PORTLET = "DELETE FROM newsletter_htmldocs_portlet WHERE id_topic = ?";
25 private static final String SQL_QUERY_FIND_TEMPLATE = " SELECT count(id_template) FROM newsletter_htmldocs_portlet WHERE id_template = ? ";
26
27
28
29
30
31 @Override
32 public NewsletterHtmlDoc findByPrimaryKey( int nIdTopic, Plugin plugin )
33 {
34 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NEWSLETTER_DOCUMENT_TOPIC, plugin );
35 daoUtil.setInt( 1, nIdTopic );
36 daoUtil.executeQuery( );
37 NewsletterHtmlDoc topic = null;
38 if ( daoUtil.next( ) )
39 {
40 topic = new NewsletterHtmlDoc( );
41 topic.setId( daoUtil.getInt( 1 ) );
42 topic.setIdTemplate( daoUtil.getInt( 2 ) );
43 topic.setUseDocumentTags( daoUtil.getBoolean( 3 ) );
44 }
45 daoUtil.free( );
46 return topic;
47 }
48
49
50
51
52 @Override
53 public void updateDocumentTopic( NewsletterHtmlDoc topic, Plugin plugin )
54 {
55 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE_NEWSLETTER_DOCUMENT_TOPIC, plugin );
56 daoUtil.setInt( 1, topic.getIdTemplate( ) );
57 daoUtil.setBoolean( 2, topic.getUseDocumentTags( ) );
58 daoUtil.setInt( 3, topic.getId( ) );
59 daoUtil.executeUpdate( );
60 daoUtil.free( );
61 }
62
63
64
65
66 @Override
67 public void deleteDocumentTopic( int nIdTopic, Plugin plugin )
68 {
69 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_NEWSLETTER_DOCUMENT_TOPIC, plugin );
70 daoUtil.setInt( 1, nIdTopic );
71 daoUtil.executeUpdate( );
72 daoUtil.free( );
73 }
74
75
76
77
78 @Override
79 public void createDocumentTopic( NewsletterHtmlDoc topic, Plugin plugin )
80 {
81 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_NEWSLETTER_DOCUMENT_TOPIC, plugin );
82 daoUtil.setInt( 1, topic.getId( ) );
83 daoUtil.setInt( 2, topic.getIdTemplate( ) );
84 daoUtil.setBoolean( 3, topic.getUseDocumentTags( ) );
85 daoUtil.executeUpdate( );
86 daoUtil.free( );
87 }
88
89
90
91
92
93
94
95 @Override
96 public void associateNewsLetterDocumentTag( int nTopicId, int nCategoryId, Plugin plugin )
97 {
98 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_ASSOCIATE_NEWSLETTER_CATEGORY_LIST, plugin );
99 daoUtil.setInt( 1, nTopicId );
100 daoUtil.setInt( 2, nCategoryId );
101
102 daoUtil.executeUpdate( );
103 daoUtil.free( );
104 }
105
106
107
108
109 @Override
110 public void deleteNewsLetterDocumentTags( int nTopicId, Plugin plugin )
111 {
112 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_NEWSLETTER_CATEGORY_LIST, plugin );
113
114 daoUtil.setInt( 1, nTopicId );
115
116 daoUtil.executeUpdate( );
117 daoUtil.free( );
118 }
119
120
121
122
123 @Override
124 public int [ ] selectNewsletterTagIds( int nTopicId, Plugin plugin )
125 {
126 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NEWSLETTER_CATEGORY_IDS, plugin );
127 daoUtil.setInt( 1, nTopicId );
128 daoUtil.executeQuery( );
129
130 List<Integer> list = new ArrayList<Integer>( );
131
132 while ( daoUtil.next( ) )
133 {
134 int nResultId = daoUtil.getInt( 1 );
135 list.add( Integer.valueOf( nResultId ) );
136 }
137
138 int [ ] nIdsArray = new int [ list.size( )];
139
140 for ( int i = 0; i < list.size( ); i++ )
141 {
142 Integer nId = list.get( i );
143 nIdsArray [i] = nId.intValue( );
144 }
145
146 daoUtil.free( );
147
148 return nIdsArray;
149 }
150
151
152
153
154 @Override
155 public void associateNewsLetterDocumentPortlet( int nTopicId, int nPortletId, Plugin plugin )
156 {
157 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_ASSOCIATE_NEWSLETTER_PORTLET, plugin );
158 daoUtil.setInt( 1, nTopicId );
159 daoUtil.setInt( 2, nPortletId );
160
161 daoUtil.executeUpdate( );
162 daoUtil.free( );
163 }
164
165
166
167
168 @Override
169 public void deleteNewsLetterDocumentPortlet( int nTopicId, Plugin plugin )
170 {
171 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_DELETE_NEWSLETTER_PORTLET, plugin );
172
173 daoUtil.setInt( 1, nTopicId );
174
175 daoUtil.executeUpdate( );
176 daoUtil.free( );
177 }
178
179
180
181
182 @Override
183 public int [ ] selectNewsletterPortletsIds( int nTopicId, Plugin plugin )
184 {
185 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_SELECT_NEWSLETTER_PORTLET_IDS, plugin );
186 daoUtil.setInt( 1, nTopicId );
187 daoUtil.executeQuery( );
188
189 List<Integer> list = new ArrayList<Integer>( );
190
191 while ( daoUtil.next( ) )
192 {
193 int nResultId = daoUtil.getInt( 1 );
194 list.add( Integer.valueOf( nResultId ) );
195 }
196
197 int [ ] nIdsArray = new int [ list.size( )];
198
199 for ( int i = 0; i < list.size( ); i++ )
200 {
201 Integer nId = list.get( i );
202 nIdsArray [i] = nId.intValue( );
203 }
204
205 daoUtil.free( );
206
207 return nIdsArray;
208 }
209
210
211
212
213 @Override
214 public boolean findTemplate( int nIdNewsletterTemplate, Plugin plugin )
215 {
216 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_TEMPLATE, plugin );
217 daoUtil.setInt( 1, nIdNewsletterTemplate );
218 daoUtil.executeQuery( );
219
220 boolean bRes = false;
221 if ( daoUtil.next( ) )
222 {
223 bRes = daoUtil.getInt( 1 ) > 0;
224 }
225
226 daoUtil.free( );
227
228 return bRes;
229 }
230 }