Mercurial > hg > LGDataverses

comparison src/main/java/edu/harvard/iq/dataverse/GuestbookResponseServiceBean.java @ 10:a50cf11e5178

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rewrite LGDataverse completely upgrading to dataverse4.0
author Zoe Hong <zhong@mpiwg-berlin.mpg.de>
date Tue, 08 Sep 2015 17:00:21 +0200
parents
children
comparison
equal deleted inserted replaced
9:5926d6419569 10:a50cf11e5178
1 /*
2 * To change this license header, choose License Headers in Project Properties.
3 * To change this template file, choose Tools | Templates
4 * and open the template in the editor.
5 */
6 package edu.harvard.iq.dataverse;
7
8 import edu.harvard.iq.dataverse.authorization.providers.builtin.BuiltinUser;
9 import edu.harvard.iq.dataverse.authorization.users.AuthenticatedUser;
10 import edu.harvard.iq.dataverse.authorization.users.User;
11 import java.text.SimpleDateFormat;
12 import java.util.Calendar;
13 import java.util.Date;
14 import java.util.Iterator;
15 import java.util.List;
16 import javax.ejb.Stateless;
17 import javax.ejb.TransactionAttribute;
18 import javax.ejb.TransactionAttributeType;
19 import javax.inject.Named;
20 import javax.persistence.EntityManager;
21 import javax.persistence.PersistenceContext;
22 import javax.persistence.Query;
23
24 /**
25 *
26 * @author skraffmiller
27 */
28 @Stateless
29 @Named
30 public class GuestbookResponseServiceBean {
31
32 @PersistenceContext(unitName = "VDCNet-ejbPU")
33 private EntityManager em;
34
35 public List<GuestbookResponse> findAll() {
36 return em.createQuery("select object(o) from GuestbookResponse as o order by o.responseTime desc").getResultList();
37 }
38
39 public List<Long> findAllIds() {
40 return findAllIds(null);
41 }
42
43 public List<Long> findAllIds(Long dataverseId) {
44 if (dataverseId == null) {
45 return em.createQuery("select o.id from GuestbookResponse as o order by o.responseTime desc").getResultList();
46 }
47 return em.createQuery("select o.id from GuestbookResponse o, Dataset d where o.dataset.id = d.id and d.owner.id = " + dataverseId + " order by o.responseTime desc").getResultList();
48 }
49
50 public List<GuestbookResponse> findAllByGuestbookId(Long guestbookId) {
51
52 if (guestbookId == null) {
53 } else {
54 return em.createQuery("select o from GuestbookResponse as o where o.guestbook.id = " + guestbookId + " order by o.responseTime desc").getResultList();
55 }
56 return null;
57 }
58
59 public Long findCountByGuestbookId(Long guestbookId) {
60
61 if (guestbookId == null) {
62 } else {
63 String queryString = "select count(o) from GuestbookResponse as o where o.guestbook_id = " + guestbookId;
64 Query query = em.createNativeQuery(queryString);
65 return (Long) query.getSingleResult();
66 }
67 return new Long(0);
68 }
69
70 public List<Long> findAllIds30Days() {
71 return findAllIds30Days(null);
72 }
73
74 public List<Long> findAllIds30Days(Long dataverseId) {
75 String beginTime;
76 String endTime;
77 Calendar cal = Calendar.getInstance();
78 cal.add(Calendar.DAY_OF_YEAR, -30);
79 beginTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cal.getTime()); // Use yesterday as default value
80 cal.add(Calendar.DAY_OF_YEAR, 31);
81 endTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cal.getTime());
82 String queryString = "select o.id from GuestbookResponse as o ";
83 if (dataverseId != null) {
84 queryString += ", Dataset d where o.dataset.id = d.id and d.owner.id = " + dataverseId + " and ";
85 } else {
86 queryString += " where ";
87 }
88 queryString += " o.responseTime >='" + beginTime + "'";
89 queryString += " and o.responseTime<='" + endTime + "'";
90 queryString += " order by o.responseTime desc";
91 Query query = em.createQuery(queryString);
92
93 return query.getResultList();
94 }
95
96 public Long findCount30Days() {
97 return findCount30Days(null);
98 }
99
100 public Long findCount30Days(Long dataverseId) {
101 String beginTime;
102 String endTime;
103 Calendar cal = Calendar.getInstance();
104 cal.add(Calendar.DAY_OF_YEAR, -30);
105 beginTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cal.getTime()); // Use yesterday as default value
106 cal.add(Calendar.DAY_OF_YEAR, 31);
107 endTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cal.getTime());
108 String queryString = "select count(o.id) from GuestbookResponse as o ";
109 if (dataverseId != null) {
110 queryString += ", DvObject v where o.dataset_id = v.id and v.owner_id = " + dataverseId + " and ";
111 } else {
112 queryString += " where ";
113 }
114 queryString += " o.responseTime >='" + beginTime + "'";
115 queryString += " and o.responseTime<='" + endTime + "'";
116 Query query = em.createNativeQuery(queryString);
117 return (Long) query.getSingleResult();
118 }
119
120 public Long findCountAll() {
121 return findCountAll(null);
122 }
123
124 public Long findCountAll(Long dataverseId) {
125 String queryString = "";
126 if (dataverseId != null) {
127 queryString = "select count(o.id) from GuestbookResponse o, DvObject v, where o.dataset_id = v.id and v.owner_id = " + dataverseId + " ";
128 } else {
129 queryString = "select count(o.id) from GuestbookResponse o ";
130 }
131
132 Query query = em.createNativeQuery(queryString);
133 return (Long) query.getSingleResult();
134 }
135
136 public List<GuestbookResponse> findAllByDataverse(Long dataverseId) {
137 return em.createQuery("select object(o) from GuestbookResponse o, Dataset d where o.dataset.id = d.id and d.owner.id = " + dataverseId + " order by o.responseTime desc").getResultList();
138 }
139
140 public List<GuestbookResponse> findAllWithin30Days() {
141 return findAllWithin30Days(null);
142 }
143
144 public List<GuestbookResponse> findAllWithin30Days(Long dataverseId) {
145 String beginTime;
146 String endTime;
147 Calendar cal = Calendar.getInstance();
148 cal.add(Calendar.DAY_OF_YEAR, -30);
149 beginTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cal.getTime()); // Use yesterday as default value
150 cal.add(Calendar.DAY_OF_YEAR, 31);
151 endTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cal.getTime());
152 String queryString = "select object(o) from GuestbookResponse as o ";
153 if (dataverseId != null) {
154 queryString += ", Dataset d where o.dataset.id = d.id and d.owner.id = " + dataverseId + " and ";
155 } else {
156 queryString += " where ";
157 }
158 queryString += " o.responseTime >='" + beginTime + "'";
159 queryString += " and o.responseTime<='" + endTime + "'";
160 queryString += " order by o.responseTime desc";
161 Query query = em.createQuery(queryString);
162
163 return query.getResultList();
164 }
165
166 private List<Object[]> convertIntegerToLong(List<Object[]> list, int index) {
167 for (Object[] item : list) {
168 item[index] = new Long((Integer) item[index]);
169 }
170
171 return list;
172 }
173
174 private String generateTempTableString(List<Long> datasetIds) {
175 // first step: create the temp table with the ids
176
177 em.createNativeQuery(" BEGIN; SET TRANSACTION READ WRITE; DROP TABLE IF EXISTS tempid; END;").executeUpdate();
178 em.createNativeQuery(" BEGIN; SET TRANSACTION READ WRITE; CREATE TEMPORARY TABLE tempid (tempid integer primary key, orderby integer); END;").executeUpdate();
179 em.createNativeQuery(" BEGIN; SET TRANSACTION READ WRITE; INSERT INTO tempid VALUES " + generateIDsforTempInsert(datasetIds) + "; END;").executeUpdate();
180 return "select tempid from tempid";
181 }
182
183 private String generateIDsforTempInsert(List idList) {
184 int count = 0;
185 StringBuffer sb = new StringBuffer();
186 Iterator iter = idList.iterator();
187 while (iter.hasNext()) {
188 Long id = (Long) iter.next();
189 sb.append("(").append(id).append(",").append(count++).append(")");
190 if (iter.hasNext()) {
191 sb.append(",");
192 }
193 }
194
195 return sb.toString();
196 }
197
198 public List<Object[]> findDownloadInfoAll(List<Long> gbrIds) {
199 //this query will return multiple rows per response where the study name has changed over version
200 //these multiples are filtered out by the method that actually writes the download csv,
201 String varString = "(" + generateTempTableString(gbrIds) + ") ";
202 String gbrDownloadQueryString = "select u.username, gbr.sessionid, "
203 + " gbr.firstname, gbr.lastname, gbr.email, gbr.institution, "
204 + " vdc.name, s.protocol, s.authority, m.title, fmd.label, gbr.responsetime, gbr.position, gbr.study_id, gbr.id, gbr.downloadType "
205 + " from guestbookresponse gbr LEFT OUTER JOIN vdcuser u ON "
206 + "(gbr.vdcuser_id =u.id), "
207 + " vdc, study s, studyversion sv, metadata m, filemetadata fmd "
208 + "where gbr.study_id = s.id "
209 + "and s.owner_id = vdc.id "
210 + "and s.id = sv.study_id "
211 + "and sv.metadata_id = m.id "
212 + "and gbr.studyfile_id = fmd.studyfile_id "
213 + "and sv.id = fmd.studyversion_id "
214 + "and sv.id = gbr.studyversion_id "
215 + " and gbr.id in " + varString
216 + " group by u.username, gbr.sessionid, "
217 + " gbr.firstname, gbr.lastname, gbr.email, gbr.institution, "
218 + " vdc.name, s.protocol, s.authority, m.title, fmd.label, gbr.responsetime, gbr.position, gbr.study_id, gbr.id, s.id, gbr.downloadType "
219 + "order by s.id, gbr.id";
220 System.out.print(gbrDownloadQueryString);
221 Query query = em.createNativeQuery(gbrDownloadQueryString);
222
223 return convertIntegerToLong(query.getResultList(), 14);
224 }
225
226 public List<Object[]> findCustomResponsePerGuestbookResponse(Long gbrId) {
227
228 String gbrCustomQuestionQueryString = "select response, cq.id "
229 + " from guestbookresponse gbr, customquestion cq, customquestionresponse cqr "
230 + "where gbr.guestbook_id = cq.guestbook_id "
231 + " and gbr.id = cqr.guestbookresponse_id "
232 + "and cq.id = cqr.customquestion_id "
233 + " and cqr.guestbookresponse_id = " + gbrId;
234 Query query = em.createNativeQuery(gbrCustomQuestionQueryString);
235
236 return convertIntegerToLong(query.getResultList(), 1);
237 }
238
239 private Guestbook findDefaultGuestbook() {
240 Guestbook guestbook = new Guestbook();
241 String queryStr = "SELECT object(o) FROM Guestbook as o WHERE o.dataverse.id = null";
242 Query query = em.createQuery(queryStr);
243 List resultList = query.getResultList();
244
245 if (resultList.size() >= 1) {
246 guestbook = (Guestbook) resultList.get(0);
247 }
248 return guestbook;
249
250 }
251
252 public String getUserName(User user) {
253 if (user.isAuthenticated()) {
254 AuthenticatedUser authUser = (AuthenticatedUser) user;
255 return authUser.getName();
256 }
257
258 try {
259 if (user.isBuiltInUser()) {
260 BuiltinUser builtinUser = (BuiltinUser) user;
261 return builtinUser.getDisplayName();
262 }
263 } catch (Exception e) {
264 return "";
265 }
266 return "Guest";
267 }
268
269 public String getUserEMail(User user) {
270 if (user.isAuthenticated()) {
271 AuthenticatedUser authUser = (AuthenticatedUser) user;
272 return authUser.getEmail();
273 }
274 try {
275 if (user.isBuiltInUser()) {
276 BuiltinUser builtinUser = (BuiltinUser) user;
277 return builtinUser.getEmail();
278 }
279 } catch (Exception e) {
280 return "";
281 }
282 return "";
283 }
284
285 public String getUserInstitution(User user) {
286 if (user.isAuthenticated()) {
287 AuthenticatedUser authUser = (AuthenticatedUser) user;
288 return authUser.getAffiliation();
289 }
290
291 try {
292 if (user.isBuiltInUser()) {
293 BuiltinUser builtinUser = (BuiltinUser) user;
294 return builtinUser.getAffiliation();
295 }
296 } catch (Exception e) {
297 return "";
298 }
299 return "";
300 }
301
302 public String getUserPosition(User user) {
303 if (user.isAuthenticated()) {
304 AuthenticatedUser authUser = (AuthenticatedUser) user;
305 return authUser.getPosition();
306 }
307 try {
308 if (user.isBuiltInUser()) {
309 BuiltinUser builtinUser = (BuiltinUser) user;
310 return builtinUser.getPosition();
311 }
312 } catch (Exception e) {
313 return "";
314 }
315
316 return "";
317 }
318
319 public AuthenticatedUser getAuthenticatedUser(User user) {
320 if (user.isAuthenticated()) {
321 AuthenticatedUser authUser = (AuthenticatedUser) user;
322 return authUser;
323 }
324 return null;
325 }
326
327 public GuestbookResponse initDefaultGuestbookResponse(Dataset dataset, DataFile dataFile, User user, DataverseSession session) {
328 GuestbookResponse guestbookResponse = new GuestbookResponse();
329 guestbookResponse.setGuestbook(findDefaultGuestbook());
330 if (dataFile != null){
331 guestbookResponse.setDataFile(dataFile);
332 }
333 guestbookResponse.setDataset(dataset);
334 guestbookResponse.setResponseTime(new Date());
335 guestbookResponse.setSessionId(session.toString());
336
337 if (user != null) {
338 guestbookResponse.setEmail(getUserEMail(user));
339 guestbookResponse.setName(getUserName(user));
340 guestbookResponse.setInstitution(getUserInstitution(user));
341 guestbookResponse.setPosition(getUserPosition(user));
342 guestbookResponse.setAuthenticatedUser(getAuthenticatedUser(user));
343 } else {
344 guestbookResponse.setEmail("");
345 guestbookResponse.setName("");
346 guestbookResponse.setInstitution("");
347 guestbookResponse.setPosition("");
348 guestbookResponse.setAuthenticatedUser(null);
349 }
350 return guestbookResponse;
351 }
352
353 public GuestbookResponse findById(Long id) {
354 return em.find(GuestbookResponse.class, id);
355 }
356
357 @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
358 public void save(GuestbookResponse guestbookResponse) {
359 em.persist(guestbookResponse);
360 }
361
362
363 public Long getCountGuestbookResponsesByDataFileId(Long dataFileId) {
364 // datafile id is null, will return 0
365 Query query = em.createNativeQuery("select count(o.id) from GuestbookResponse o where o.datafile_id = " + dataFileId);
366 return (Long) query.getSingleResult();
367 }
368
369 public Long getCountGuestbookResponsesByDatasetId(Long datasetId) {
370 // dataset id is null, will return 0
371 Query query = em.createNativeQuery("select count(o.id) from GuestbookResponse o where o.dataset_id = " + datasetId);
372 return (Long) query.getSingleResult();
373 }
374
375 public Long getCountOfAllGuestbookResponses() {
376 // dataset id is null, will return 0
377 Query query = em.createNativeQuery("select count(o.id) from GuestbookResponse o;");
378 return (Long) query.getSingleResult();
379 }
380
381 }