1 package fr.ifremer.quadrige3.core.dao.administration.program;
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 import com.google.common.base.Joiner;
27 import com.google.common.collect.ImmutableList;
28 import com.google.common.collect.Lists;
29 import com.google.common.collect.Maps;
30 import fr.ifremer.quadrige3.core.dao.administration.user.DepartmentJdbcDao;
31 import fr.ifremer.quadrige3.core.dao.administration.user.QuserJdbcDao;
32 import fr.ifremer.quadrige3.core.dao.referential.StatusCode;
33 import fr.ifremer.quadrige3.core.dao.technical.Assert;
34 import fr.ifremer.quadrige3.core.dao.technical.Daos;
35 import fr.ifremer.quadrige3.core.dao.technical.jdbc.OptionalDataSourceJdbcDaoSupport;
36 import fr.ifremer.quadrige3.core.exception.QuadrigeTechnicalException;
37 import fr.ifremer.quadrige3.core.vo.administration.program.ProgramVO;
38 import fr.ifremer.quadrige3.core.vo.administration.strategy.StrategyVO;
39 import fr.ifremer.quadrige3.core.vo.administration.user.QuserVO;
40 import org.apache.commons.collections4.CollectionUtils;
41 import org.apache.commons.io.IOUtils;
42 import org.apache.commons.lang3.StringUtils;
43 import org.springframework.beans.factory.InitializingBean;
44 import org.springframework.beans.factory.annotation.Autowired;
45 import org.springframework.context.annotation.Lazy;
46 import org.springframework.stereotype.Repository;
47
48 import javax.annotation.Resource;
49 import javax.sql.DataSource;
50 import java.io.IOException;
51 import java.io.InputStream;
52 import java.sql.ResultSet;
53 import java.sql.SQLException;
54 import java.util.*;
55
56
57
58
59 @Repository("programStrategyJdbcDao")
60 @Lazy
61 public class ProgramStrategyJdbcDaoImpl
62 extends OptionalDataSourceJdbcDaoSupport
63 implements ProgramStrategyJdbcDao, InitializingBean {
64
65 private final static String QUERIES_FILE_PATH = "queries.jdbc.xml";
66
67 @Resource(name = "queriesJdbcProperties")
68 private Properties queriesJdbcProperties;
69
70 @Resource(name = "quserJdbcDao")
71 private QuserJdbcDao quserJdbcDao;
72
73 @Resource(name = "departmentJdbcDao")
74 private DepartmentJdbcDao departmentJdbcDao;
75
76 protected final Properties connectionProperties;
77
78
79
80
81
82
83
84 @Autowired
85 public ProgramStrategyJdbcDaoImpl(DataSource dataSource) {
86 super(dataSource);
87 this.connectionProperties = null;
88 }
89
90
91
92
93 public ProgramStrategyJdbcDaoImpl() {
94 this((Properties) null);
95 }
96
97
98
99
100
101
102
103 public ProgramStrategyJdbcDaoImpl(Properties connectionProperties) {
104 super();
105 this.connectionProperties = connectionProperties;
106
107
108 Properties properties = new Properties();
109 InputStream is = null;
110 try {
111 is = getClass().getClassLoader().getResourceAsStream(QUERIES_FILE_PATH);
112 properties.loadFromXML(is);
113
114 this.queriesJdbcProperties = properties;
115 } catch (IOException e) {
116 throw new QuadrigeTechnicalException(
117 String.format("Unable to read file [%s] from classpath", QUERIES_FILE_PATH),
118 e);
119 } finally {
120 IOUtils.closeQuietly(is);
121 }
122
123
124 checkAllQueriesExists();
125 }
126
127
128 @Override
129 public void afterPropertiesSet() {
130
131 checkAllQueriesExists();
132 }
133
134
135 @Override
136 public List<ProgramVO> getProgramsByCodes(List<String> codes) {
137 String sql = queriesJdbcProperties.getProperty("programsByCodes");
138
139 sql = sql.replace(":codes", "'" + Joiner.on("','").skipNulls().join(codes) + "'");
140
141 Map<String, Object> paramMap = Maps.newHashMap();
142 return query(connectionProperties, sql, paramMap, (rs, rowNum) -> toProgramVO(rs));
143 }
144
145
146 @Override
147 public ProgramVO getProgramByCode(String code) {
148 String sql = queriesJdbcProperties.getProperty("programsByCodes");
149
150 Map<String, Object> paramMap = Maps.newHashMap();
151 paramMap.put("codes", code);
152
153 return query(connectionProperties, sql, paramMap, this::toProgramVO);
154 }
155
156 @Override
157 public List<String> getAllProgramCodes() {
158
159 return getProgramCodesByStatus(StatusCode.literals());
160 }
161
162
163 @Override
164 public List<String> getLocalProgramCodes() {
165
166 return getProgramCodesByStatus(Lists.newArrayList(StatusCode.LOCAL_ENABLE.getValue(), StatusCode.LOCAL_DISABLE.getValue()));
167 }
168
169 private List<String> getProgramCodesByStatus(Iterable<String> statusCodes) {
170 String sql = queriesJdbcProperties.getProperty("allProgramCodesByStatus");
171
172 sql = sql.replace(":status_codes",
173 "'" + Joiner.on("','").skipNulls().join(statusCodes) + "'");
174
175 return query(connectionProperties, sql, null, (resultSet, i) -> resultSet.getString(1));
176 }
177
178
179 @Override
180 public List<StrategyVO> getStrategiesByIds(List<Integer> ids) {
181 String sql = queriesJdbcProperties.getProperty("strategiesByIds");
182
183 sql = sql.replace(":ids", Joiner.on(",").skipNulls().join(ids));
184
185 Map<String, Object> paramMap = Maps.newHashMap();
186 return query(connectionProperties, sql, paramMap, (rs, rowNum) -> toStrategyVO(rs));
187 }
188
189
190 @Override
191 public StrategyVO getStrategyById(int id) {
192 String sql = queriesJdbcProperties.getProperty("strategiesByIds");
193
194 Map<String, Object> paramMap = Maps.newHashMap();
195 paramMap.put("ids", id);
196
197 return query(connectionProperties, sql, paramMap, this::toStrategyVO);
198 }
199
200 @Override
201 public List<ProgramVO> getReadableProgramsByUserId(int userId) {
202 return getReadableProgramsByUserId(connectionProperties, userId);
203 }
204
205 @Override
206 public List<ProgramVO> getReadableProgramsByUserId(Properties connectionProperties, int userId) {
207 return getProgramsByUserIdAndPrivilegeIds(connectionProperties, userId,
208 ImmutableList.of(
209 ProgramPrivilegeIds.MANAGER.getValue(),
210 ProgramPrivilegeIds.RECORDER.getValue(),
211 ProgramPrivilegeIds.FULL_VIEWER.getValue(),
212 ProgramPrivilegeIds.VIEWER.getValue(),
213 ProgramPrivilegeIds.VALIDATOR.getValue()
214 )
215 );
216 }
217
218
219 @Override
220 public List<ProgramVO> getWritableProgramsByUserId(int userId) {
221 return getWritableProgramsByUserId(connectionProperties, userId);
222 }
223
224
225 @Override
226 public List<ProgramVO> getWritableProgramsByUserId(Properties connectionProperties, int userId) {
227 return getProgramsByUserIdAndPrivilegeIds(connectionProperties, userId,
228 ImmutableList.of(
229 ProgramPrivilegeIds.MANAGER.getValue(),
230 ProgramPrivilegeIds.RECORDER.getValue(),
231 ProgramPrivilegeIds.VALIDATOR.getValue()
232 )
233 );
234 }
235
236
237 @Override
238 public List<ProgramVO> getManagedProgramsByUserId(int userId) {
239 return getManagedProgramsByUserId(null, userId);
240 }
241
242
243 @Override
244 public List<ProgramVO> getManagedProgramsByUserId(Properties connectionProperties, int userId) {
245 return getProgramsByUserIdAndPrivilegeIds(connectionProperties, userId, ImmutableList.of(ProgramPrivilegeIds.MANAGER.getValue()));
246 }
247
248
249
250 protected List<ProgramVO> getProgramsByUserIdAndPrivilegeIds(Properties connectionProperties, int userId, Collection<Integer> privilegeIds) {
251
252
253 QuserVO quserVO = quserJdbcDao.getUserById(connectionProperties, userId);
254 Assert.notNull(quserVO);
255 Assert.notNull(quserVO.getDepId());
256 int departmentId = quserVO.getDepId();
257
258
259 List<Integer> inheritedDepartmentIds = departmentJdbcDao.getInheritedRecorderDepartmentIdsFrom(departmentId, new Date());
260
261 List<Integer> allDepartmentIds = new ArrayList<>(CollectionUtils.emptyIfNull(inheritedDepartmentIds));
262 allDepartmentIds.add(departmentId);
263
264
265 String sql = queriesJdbcProperties.getProperty("programsByUserIdAndPrivilegeIds");
266
267 Map<String, Object> paramMap = Maps.newHashMap();
268 paramMap.put("userId", userId);
269
270 sql = sql.replace(":progPrivIds", Daos.getInStatementFromIntegerCollection(privilegeIds));
271 sql = sql.replace(":depIds", Daos.getInStatementFromIntegerCollection(allDepartmentIds));
272
273 final List<ProgramVO> result = Lists.newArrayList();
274
275
276 query(connectionProperties, sql, paramMap, source -> {
277 ProgramVO target = toProgramVO(source);
278 result.add(target);
279 });
280
281 return result;
282 }
283
284
285
286
287 protected void checkAllQueriesExists() {
288 Assert.notNull(queriesJdbcProperties);
289
290 checkQueryExists("allProgramCodesByStatus");
291 checkQueryExists("programsByCodes");
292 checkQueryExists("strategiesByIds");
293 checkQueryExists("programsByUserIdAndPrivilegeIds");
294 }
295
296
297
298
299
300
301
302 protected void checkQueryExists(String queryName) {
303 if (StringUtils.isBlank(queriesJdbcProperties.getProperty(queryName))) {
304 throw new QuadrigeTechnicalException(String.format("Property with name [%s] not exists on JDBC queries file", queryName));
305 }
306 }
307
308
309
310
311
312
313
314
315
316
317
318
319 protected ProgramVO toProgramVO(ResultSet rs) throws SQLException {
320 ProgramVO target = new ProgramVO();
321
322 int index = 0;
323 target.setProgCd(rs.getString(++index));
324 target.setProgNm(rs.getString(++index));
325
326 return target;
327 }
328
329
330
331
332
333
334
335
336
337
338
339
340 protected StrategyVO toStrategyVO(ResultSet rs) throws SQLException {
341 StrategyVO target = new StrategyVO();
342
343 int index = 0;
344 target.setStratId(rs.getInt(++index));
345 target.setStratNm(rs.getString(++index));
346
347
348 ProgramVO program = new ProgramVO();
349 program.setProgCd(rs.getString(++index));
350 program.setProgNm(rs.getString(++index));
351 target.setProgramVO(program);
352 target.setProgCd(program.getProgCd());
353
354 return target;
355 }
356 }