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.greetingscard.business;
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.Collection;
41 import java.util.List;
42
43
44
45
46
47 public class GreetingsCardArchiveDAO implements IGreetingsCardArchiveDAO
48 {
49 private static final String SQL_QUERY_NEW_PRIMARY_KEY = "SELECT max( id_archive ) FROM greetings_card_archive";
50 private static final String SQL_QUERY_INSERT = " INSERT INTO greetings_card_archive (id_archive, id_gct, domain_name, nb_card, nb_card_red, year_archive) VALUES (?,?,?,?,?,?) ";
51 private static final String SQL_QUERY_FIND_BY_ID = " SELECT id_archive, id_gct, domain_name, domain_name, nb_card_red, year_archive FROM greetings_card_archive WHERE id_archive = ? ";
52 private static final String SQL_QUERY_FIND_BY_ID_TEMPLATE = " SELECT id_archive, id_gct, domain_name, nb_card, nb_card_red, year_archive FROM greetings_card_archive WHERE id_gct = ? ";
53 private static final String SQL_QUERY_FIND_BY_YEAR = " SELECT id_archive, id_gct, domain_name, nb_card, nb_card_red, year_archive FROM greetings_card_archive WHERE year_archive = ? ";
54 private static final String SQL_QUERY_REMOVE = " DELETE FROM greetings_card_archive WHERE id_archive = ? ";
55 private static final String SQL_QUERY_REMOVE_ALL = " DELETE FROM greetings_card_archive ";
56 private static final String SQL_QUERY_UPDATE = " UPDATE greetings_card_archive SET id_gct = ?, domain_name = ?, nb_card = ?, nb_card_red = ?, year_archive = ? WHERE id_archive = ? ";
57 private static final String SQL_QUERY_REMOVE_BY_ID_TEMPLATE = " DELETE FROM greetings_card_archive WHERE id_gct = ? ";
58 private static final String SQL_QUERY_GET_YEARS = " SELECT DISTINCT year_archive FROM greetings_card_archive ORDER BY year_archive desc ";
59 private static final String SQL_QUERY_FILTER_YEAR = " year_archive = ? ";
60 private static final String SQL_QUERY_FILTER_AND = " AND ";
61
62
63
64
65
66
67 private int newPrimaryKey( Plugin plugin )
68 {
69 int nKey;
70
71 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_NEW_PRIMARY_KEY, plugin );
72
73 daoUtil.executeQuery( );
74
75 if ( !daoUtil.next( ) )
76 {
77
78 nKey = 1;
79 }
80 else
81 {
82 nKey = daoUtil.getInt( 1 ) + 1;
83 }
84
85 daoUtil.free( );
86
87 return nKey;
88 }
89
90
91
92
93 @Override
94 public void insert( GreetingsCardArchive greetingsCardArchive, Plugin plugin )
95 {
96 if ( greetingsCardArchive != null )
97 {
98 greetingsCardArchive.setIdArchive( newPrimaryKey( plugin ) );
99
100 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, plugin );
101 daoUtil.setInt( 1, greetingsCardArchive.getIdArchive( ) );
102 daoUtil.setInt( 2, greetingsCardArchive.getIdGCT( ) );
103 daoUtil.setString( 3, greetingsCardArchive.getDomainName( ) );
104 daoUtil.setInt( 4, greetingsCardArchive.getNbCard( ) );
105 daoUtil.setInt( 5, greetingsCardArchive.getNbCardRed( ) );
106 daoUtil.setInt( 6, greetingsCardArchive.getYearArchive( ) );
107 daoUtil.executeUpdate( );
108 daoUtil.free( );
109 }
110 }
111
112
113
114
115 @Override
116 public Collection<GreetingsCardArchive> findByTemplateIdAndYear( int nIdGreetingsCardTemplate, int nYear, Plugin plugin )
117 {
118 StringBuilder sbSql = new StringBuilder( SQL_QUERY_FIND_BY_ID_TEMPLATE );
119 if ( nYear > 0 )
120 {
121 sbSql.append( SQL_QUERY_FILTER_AND );
122 sbSql.append( SQL_QUERY_FILTER_YEAR );
123 }
124 DAOUtil daoUtil = new DAOUtil( sbSql.toString( ), plugin );
125 daoUtil.setInt( 1, nIdGreetingsCardTemplate );
126 if ( nYear > 0 )
127 {
128 daoUtil.setInt( 2, nYear );
129 }
130 daoUtil.executeQuery( );
131 List<GreetingsCardArchive> listArchive = new ArrayList<GreetingsCardArchive>( );
132 while ( daoUtil.next( ) )
133 {
134 GreetingsCardArchive archive = new GreetingsCardArchive( );
135 archive = new GreetingsCardArchive( );
136 archive.setIdArchive( daoUtil.getInt( 1 ) );
137 archive.setIdGCT( daoUtil.getInt( 2 ) );
138 archive.setDomainName( daoUtil.getString( 3 ) );
139 archive.setNbCard( daoUtil.getInt( 4 ) );
140 archive.setNbCardRed( daoUtil.getInt( 5 ) );
141 archive.setYearArchive( daoUtil.getInt( 6 ) );
142 listArchive.add( archive );
143 }
144 daoUtil.free( );
145 return listArchive;
146 }
147
148
149
150
151 @Override
152 public Collection<GreetingsCardArchive> findByYear( int year, Plugin plugin )
153 {
154 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_YEAR, plugin );
155 daoUtil.setInt( 1, year );
156
157 List<GreetingsCardArchive> listArchive = new ArrayList<GreetingsCardArchive>( );
158 while ( daoUtil.next( ) )
159 {
160 GreetingsCardArchive archive = new GreetingsCardArchive( );
161 archive = new GreetingsCardArchive( );
162 archive.setIdArchive( daoUtil.getInt( 1 ) );
163 archive.setIdGCT( daoUtil.getInt( 2 ) );
164 archive.setDomainName( daoUtil.getString( 3 ) );
165 archive.setNbCard( daoUtil.getInt( 4 ) );
166 archive.setNbCardRed( daoUtil.getInt( 5 ) );
167 archive.setYearArchive( daoUtil.getInt( 6 ) );
168 listArchive.add( archive );
169 }
170 daoUtil.free( );
171 return listArchive;
172 }
173
174
175
176
177 @Override
178 public GreetingsCardArchive findById( int nIdArchive, Plugin plugin )
179 {
180 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_FIND_BY_ID, plugin );
181 daoUtil.setInt( 1, nIdArchive );
182
183 GreetingsCardArchive archive = null;
184 if ( daoUtil.next( ) )
185 {
186 archive = new GreetingsCardArchive( );
187 archive.setIdArchive( daoUtil.getInt( 1 ) );
188 archive.setIdGCT( daoUtil.getInt( 2 ) );
189 archive.setDomainName( daoUtil.getString( 3 ) );
190 archive.setNbCard( daoUtil.getInt( 4 ) );
191 archive.setNbCardRed( daoUtil.getInt( 5 ) );
192 archive.setYearArchive( daoUtil.getInt( 6 ) );
193 }
194 daoUtil.free( );
195 return archive;
196 }
197
198
199
200
201 @Override
202 public void remove( int nIdArchive, Plugin plugin )
203 {
204 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE, plugin );
205 daoUtil.setInt( 1, nIdArchive );
206 daoUtil.executeUpdate( );
207 daoUtil.free( );
208 }
209
210
211
212
213 @Override
214 public void removeAllArchives( Plugin plugin )
215 {
216 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_ALL, plugin );
217 daoUtil.executeUpdate( );
218 daoUtil.free( );
219 }
220
221
222
223
224 @Override
225 public void update( GreetingsCardArchive greetingsCardArchive, Plugin plugin )
226 {
227 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_UPDATE, plugin );
228 daoUtil.setInt( 1, greetingsCardArchive.getIdGCT( ) );
229 daoUtil.setString( 2, greetingsCardArchive.getDomainName( ) );
230 daoUtil.setInt( 3, greetingsCardArchive.getNbCard( ) );
231 daoUtil.setInt( 4, greetingsCardArchive.getNbCardRed( ) );
232 daoUtil.setInt( 5, greetingsCardArchive.getYearArchive( ) );
233 daoUtil.setInt( 6, greetingsCardArchive.getIdArchive( ) );
234 daoUtil.executeUpdate( );
235 daoUtil.free( );
236 }
237
238
239
240
241 @Override
242 public void removeByIdTemplate( int nGreetingsCardTemplate, Plugin plugin )
243 {
244 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_REMOVE_BY_ID_TEMPLATE, plugin );
245 daoUtil.setInt( 1, nGreetingsCardTemplate );
246 daoUtil.executeUpdate( );
247 daoUtil.free( );
248 }
249
250
251
252
253 @Override
254 public Collection<Integer> getYearList( Plugin plugin )
255 {
256 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_GET_YEARS, plugin );
257 daoUtil.executeQuery( );
258
259 List<Integer> listYear = new ArrayList<Integer>( );
260
261 while ( daoUtil.next( ) )
262 {
263 listYear.add( daoUtil.getInt( 1 ) );
264 }
265 daoUtil.free( );
266 return listYear;
267 }
268
269 }