|
1
|
package eu.dnetlib.goldoa.service.dao;
|
|
2
|
|
|
3
|
import com.opencsv.CSVWriter;
|
|
4
|
import eu.dnetlib.goldoa.domain.BankAccount;
|
|
5
|
import eu.dnetlib.goldoa.domain.BankTransferReceipt;
|
|
6
|
import eu.dnetlib.goldoa.domain.Comment;
|
|
7
|
import eu.dnetlib.goldoa.domain.CommentTemplate;
|
|
8
|
import eu.dnetlib.goldoa.domain.Currency;
|
|
9
|
import eu.dnetlib.goldoa.domain.Funder;
|
|
10
|
import eu.dnetlib.goldoa.domain.Person;
|
|
11
|
import eu.dnetlib.goldoa.domain.Request;
|
|
12
|
import eu.dnetlib.goldoa.domain.RequestCoFunder;
|
|
13
|
import eu.dnetlib.goldoa.domain.RequestFilter;
|
|
14
|
import eu.dnetlib.goldoa.domain.RequestPage;
|
|
15
|
import eu.dnetlib.goldoa.domain.RequestSort;
|
|
16
|
import eu.dnetlib.goldoa.domain.RequestSortOrder;
|
|
17
|
import org.apache.commons.codec.digest.DigestUtils;
|
|
18
|
import org.apache.commons.io.IOUtils;
|
|
19
|
import org.apache.commons.lang3.ArrayUtils;
|
|
20
|
import org.springframework.beans.factory.annotation.Autowired;
|
|
21
|
import org.springframework.dao.EmptyResultDataAccessException;
|
|
22
|
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
|
|
23
|
import org.springframework.jdbc.core.JdbcTemplate;
|
|
24
|
import org.springframework.jdbc.core.PreparedStatementSetter;
|
|
25
|
import org.springframework.jdbc.core.RowMapper;
|
|
26
|
|
|
27
|
import javax.sql.DataSource;
|
|
28
|
import java.io.ByteArrayOutputStream;
|
|
29
|
import java.io.IOException;
|
|
30
|
import java.io.InputStream;
|
|
31
|
import java.io.StringWriter;
|
|
32
|
import java.sql.PreparedStatement;
|
|
33
|
import java.sql.ResultSet;
|
|
34
|
import java.sql.SQLException;
|
|
35
|
import java.sql.Types;
|
|
36
|
import java.text.SimpleDateFormat;
|
|
37
|
import java.util.ArrayList;
|
|
38
|
import java.util.Date;
|
|
39
|
import java.util.HashMap;
|
|
40
|
import java.util.List;
|
|
41
|
import java.util.Map;
|
|
42
|
|
|
43
|
/**
|
|
44
|
* Created by antleb on 3/30/15.
|
|
45
|
*/
|
|
46
|
public class RequestDAO {
|
|
47
|
|
|
48
|
@Autowired
|
|
49
|
private DataSource dataSource;
|
|
50
|
|
|
51
|
private final static String GET_FOR_PROJECT = "select id, \"user\", date, researcher, organization, project, publication, journal, publisher, publisher_email, budget, invoice, apc, discount, projectparticipation, fundingrequested, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, apc_paid, transfer_cost, other_cost, date_paid, submissiondate, approvaldate from request where project=?";
|
|
52
|
|
|
53
|
private final static String GET_BY_ID = "select id, \"user\", date, researcher, organization, project, publication, journal, publisher_email, publisher, budget, invoice, apc, discount, projectparticipation, fundingrequested, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, apc_paid, transfer_cost, other_cost, date_paid, submissiondate, approvaldate from request where id=?";
|
|
54
|
|
|
55
|
private final static String UPDATE_REQUEST = "update request set \"user\"=?, date=?, researcher=?, organization=?, project=?, publication=?, journal=?, publisher=?, publisher_email=?, budget=?, invoice = ?, apc=?, discount=?, projectparticipation=?, fundingrequested=?, currency=?, status=?, bank_name=?, bank_address=?, bank_code=?, bank_holder=?, bank_iban=?, apc_paid=?, transfer_cost=?, other_cost=?, date_paid=?, submissiondate=?, approvaldate=? where id = ?";
|
|
56
|
|
|
57
|
private final static String INSERT_REQUEST = "insert into request (\"user\", date, researcher, organization, project, publication, journal, publisher, publisher_email, budget, invoice, apc, discount, projectparticipation, fundingrequested, currency, status, bank_name, bank_address, bank_code, bank_holder, bank_iban, apc_paid, transfer_cost, other_cost, date_paid, submissiondate, approvaldate, id) values (?, ?, ?, ?, ?, ? ,? ,? ,? ,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
|
|
58
|
|
|
59
|
private final static String SUBMIT_REQUEST = "update request set status = " + Request.RequestStatus.SUBMITTED.getCode() + ", submissiondate=now() where id=? and status not in (" + Request.RequestStatus.APPROVED.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PROCESSING.getCode() + ", " + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ", " + Request.RequestStatus.CONDITIONALLY_APPROVED.getCode() + ")";
|
|
60
|
|
|
61
|
private final static String APPROVE_REQUEST = "update request set status = " + Request.RequestStatus.APPROVED.getCode() + ", approvaldate=now() where id=?";
|
|
62
|
|
|
63
|
private final static String CONDITIONALLY_APPROVE_REQUEST = "update request set status = " + Request.RequestStatus.CONDITIONALLY_APPROVED.getCode() + " where id=?";
|
|
64
|
|
|
65
|
private final static String REJECT_REQUEST = "update request set status = " + Request.RequestStatus.REJECTED.getCode() + " where id=?";
|
|
66
|
|
|
67
|
private final static String PAID_REQUEST = "update request set status = " + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ", apc_paid=?, transfer_cost=?, other_cost=?, date_paid=? where id=?";
|
|
68
|
|
|
69
|
private final static String LIBRARY_PAID_REQUEST = "update request set status = " + Request.RequestStatus.LIBRARY_FUND_PAID.getCode() + ", apc_paid=?, transfer_cost=?, other_cost=?, date_paid=? where id=?";
|
|
70
|
|
|
71
|
private final static String PUBLISHER_PAID_REQUEST = "update request set status = " + Request.RequestStatus.PUBLISHER_FUND_PAID.getCode() + ", apc_paid=?, transfer_cost=?, other_cost=?, date_paid=? where id=?";
|
|
72
|
|
|
73
|
private final static String ONHOLD_REQUEST = "update request set status = " + Request.RequestStatus.ACCOUNTING_ON_HOLD.getCode() + " where id=?";
|
|
74
|
|
|
75
|
private final static String PROCESSING_REQUEST = "update request set status = " + Request.RequestStatus.ACCOUNTING_PROCESSING.getCode() + " where id=?";
|
|
76
|
|
|
77
|
private final static String DENIED_REQUEST = "update request set status = " + Request.RequestStatus.ACCOUNTING_DENIED.getCode() + " where id=?";
|
|
78
|
|
|
79
|
private final static String GET_COMMENT_TEMPLATES = "select id, status, name, comment from commenttemplate where status=?";
|
|
80
|
|
|
81
|
private RowMapper<Request> requestRowMapper = new RowMapper<Request>() {
|
|
82
|
@Override
|
|
83
|
public Request mapRow(ResultSet rs, int rowNum) throws SQLException {
|
|
84
|
BankAccount bankAccount = new BankAccount(rs.getString("bank_name"), rs.getString("bank_address"), rs.getString("bank_code"), rs.getString("bank_holder"), rs.getString("bank_iban"));
|
|
85
|
|
|
86
|
Request request = new Request(
|
|
87
|
rs.getString("id"), rs.getString("user"), rs.getTimestamp("date"), rs.getString("researcher"), rs.getString("organization"),
|
|
88
|
rs.getString("project"), rs.getString("publication"), rs.getString("journal"), rs.getString("publisher"), rs.getString("publisher_email"),
|
|
89
|
rs.getString("budget"), rs.getString("invoice"), rs.getFloat("apc"), rs.getFloat("discount"), rs.getFloat("projectparticipation"),
|
|
90
|
rs.getFloat("fundingrequested"), null, bankAccount, new ArrayList<RequestCoFunder>(), rs.getFloat("apc_paid"), rs.getFloat("transfer_cost"),
|
|
91
|
rs.getFloat("other_cost"), rs.getTimestamp("date_paid"), rs.getInt("status"), rs.getDate("submissiondate"), rs.getDate("approvaldate"));
|
|
92
|
|
|
93
|
String currency = rs.getString("currency");
|
|
94
|
if (rs.wasNull())
|
|
95
|
request.setCurrency(null);
|
|
96
|
else
|
|
97
|
request.setCurrency(Currency.valueOf(currency));
|
|
98
|
|
|
99
|
rs.getFloat("apc");
|
|
100
|
if (rs.wasNull())
|
|
101
|
request.setApc(null);
|
|
102
|
rs.getFloat("discount");
|
|
103
|
if (rs.wasNull())
|
|
104
|
request.setDiscount(null);
|
|
105
|
rs.getFloat("projectparticipation");
|
|
106
|
if (rs.wasNull())
|
|
107
|
request.setProjectParticipation(null);
|
|
108
|
rs.getFloat("fundingrequested");
|
|
109
|
if (rs.wasNull())
|
|
110
|
request.setFundingRequested(null);
|
|
111
|
rs.getFloat("apc_paid");
|
|
112
|
if (rs.wasNull())
|
|
113
|
request.setApc_paid(null);
|
|
114
|
rs.getFloat("transfer_cost");
|
|
115
|
if (rs.wasNull())
|
|
116
|
request.setTransfer_cost(null);
|
|
117
|
rs.getFloat("other_cost");
|
|
118
|
if (rs.wasNull())
|
|
119
|
request.setOther_cost(null);
|
|
120
|
|
|
121
|
return request;
|
|
122
|
}
|
|
123
|
};
|
|
124
|
|
|
125
|
|
|
126
|
public void saveRequest(final Request request) {
|
|
127
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
128
|
Object[] args;
|
|
129
|
int[] types = new int[]{Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
|
|
130
|
Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.REAL, Types.REAL, Types.REAL,
|
|
131
|
Types.REAL, Types.VARCHAR, Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
|
|
132
|
Types.VARCHAR, Types.REAL, Types.REAL, Types.REAL, Types.TIMESTAMP, Types.TIMESTAMP, Types.TIMESTAMP, Types.VARCHAR};
|
|
133
|
|
|
134
|
String currency = request.getCurrency() != null ? request.getCurrency().name() : null;
|
|
135
|
|
|
136
|
if (request.getBankAccount() != null) {
|
|
137
|
args = new Object[]{request.getUser(), request.getDate(), request.getResearcher(), request.getOrganization(), request.getProject(),
|
|
138
|
request.getPublication(), request.getJournal(), request.getPublisher(), request.getPublisherEmail(), request.getBudget(), request.getInvoice(),
|
|
139
|
request.getApc(), request.getDiscount(), request.getProjectParticipation(), request.getFundingRequested(), currency,
|
|
140
|
request.getStatus(), request.getBankAccount().getBankName(), request.getBankAccount().getBankAddress(), request.getBankAccount().getBankCode(), request.getBankAccount().getAccountHolder(),
|
|
141
|
request.getBankAccount().getIban(), request.getApc_paid(), request.getTransfer_cost(), request.getOther_cost(), request.getDate_paid(), request.getSubmissionDate(), request.getApprovalDate(), request.getId()};
|
|
142
|
} else {
|
|
143
|
args = new Object[]{request.getUser(), request.getDate(), request.getResearcher(), request.getOrganization(), request.getProject(),
|
|
144
|
request.getPublication(), request.getJournal(), request.getPublisher(), request.getPublisherEmail(), request.getBudget(), request.getInvoice(),
|
|
145
|
request.getApc(), request.getDiscount(), request.getProjectParticipation(), request.getFundingRequested(), currency, request.getStatus(), null, null, null, null, null,
|
|
146
|
request.getApc_paid(), request.getTransfer_cost(), request.getOther_cost(), request.getDate_paid(), request.getSubmissionDate(), request.getApprovalDate(), request.getId()};
|
|
147
|
}
|
|
148
|
|
|
149
|
if (jdbcTemplate.update(UPDATE_REQUEST, args, types) == 0) {
|
|
150
|
jdbcTemplate.update(INSERT_REQUEST, args, types);
|
|
151
|
}
|
|
152
|
}
|
|
153
|
|
|
154
|
public void saveCoFunders(final Request request) {
|
|
155
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
156
|
jdbcTemplate.update("delete from request_cofunder where request=?", new String[]{request.getId()}, new int[]{Types.VARCHAR});
|
|
157
|
jdbcTemplate.batchUpdate("insert into request_cofunder (request, funder, percentage) values (?,?,?)", new BatchPreparedStatementSetter() {
|
|
158
|
|
|
159
|
@Override
|
|
160
|
public void setValues(PreparedStatement ps, int i) throws SQLException {
|
|
161
|
ps.setString(1, request.getId());
|
|
162
|
ps.setString(2, request.getCoFunders().get(i).getFunder().getId());
|
|
163
|
ps.setFloat(3, request.getCoFunders().get(i).getPercentage());
|
|
164
|
}
|
|
165
|
|
|
166
|
@Override
|
|
167
|
public int getBatchSize() {
|
|
168
|
if (request.getCoFunders() != null)
|
|
169
|
return request.getCoFunders().size();
|
|
170
|
else
|
|
171
|
return 0;
|
|
172
|
}
|
|
173
|
});
|
|
174
|
}
|
|
175
|
|
|
176
|
public List<RequestCoFunder> getCoFunders(final Request request) {
|
|
177
|
return new JdbcTemplate(dataSource).query("select rf.percentage, f.id, f.name, f.url, f.source from request_cofunder rf join funder f on rf.funder=f.id where rf.request=?", new String[]{request.getId()}, new int[]{Types.VARCHAR}, new RowMapper<RequestCoFunder>() {
|
|
178
|
@Override
|
|
179
|
public RequestCoFunder mapRow(ResultSet rs, int rowNum) throws SQLException {
|
|
180
|
Funder funder = new Funder(rs.getString("id"), rs.getString("name"), rs.getString("url"), rs.getString("source"));
|
|
181
|
|
|
182
|
return new RequestCoFunder(request, funder, rs.getFloat("percentage"));
|
|
183
|
}
|
|
184
|
});
|
|
185
|
}
|
|
186
|
|
|
187
|
public Request getRequest(String requestId) {
|
|
188
|
return new JdbcTemplate(dataSource).queryForObject(GET_BY_ID, new String[]{requestId}, new int[]{
|
|
189
|
Types.VARCHAR}, requestRowMapper);
|
|
190
|
}
|
|
191
|
|
|
192
|
public RequestPage getForUser(String personId, Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus[] statusFilter, int from, int to) {
|
|
193
|
return this.getRequestPage(personId, null, null, requestSortBy, order, requestFilter, term, getStatusFilter(statusFilter), from, to);
|
|
194
|
}
|
|
195
|
|
|
196
|
public RequestPage getForOrganization(List<String> organizationIds, Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus[] statusFilter, int from, int to) {
|
|
197
|
return this.getRequestPage(null, organizationIds, null, requestSortBy, order, requestFilter, term, getStatusFilter(statusFilter), from, to);
|
|
198
|
}
|
|
199
|
|
|
200
|
public RequestPage getForPublisher(String publisherId, Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus[] statusFilter, int from, int to) {
|
|
201
|
return this.getRequestPage(null, null, publisherId, requestSortBy, order, requestFilter, term, getStatusFilter(statusFilter), from, to);
|
|
202
|
}
|
|
203
|
|
|
204
|
public List<Request> getForProject(String projectId) {
|
|
205
|
return new JdbcTemplate(dataSource).query(GET_FOR_PROJECT, new String[]{projectId}, new int[]{
|
|
206
|
Types.VARCHAR}, requestRowMapper);
|
|
207
|
}
|
|
208
|
|
|
209
|
public RequestPage getRequests(Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus[] statusFilter, int from, int to) {
|
|
210
|
return this.getRequestPage(null, null, null, requestSortBy, order, requestFilter, term, getStatusFilter(statusFilter), from, to);
|
|
211
|
}
|
|
212
|
|
|
213
|
public RequestPage getRequests(Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Integer[] statusFilter, int from, int to) {
|
|
214
|
return this.getRequestPage(null, null, null, requestSortBy, order, requestFilter, term, statusFilter, from, to);
|
|
215
|
}
|
|
216
|
|
|
217
|
public void submitRequest(String requestId) {
|
|
218
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
219
|
|
|
220
|
jdbcTemplate.update(SUBMIT_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});
|
|
221
|
}
|
|
222
|
|
|
223
|
public void approveRequest(String requestId, String personId, String comment, String template) {
|
|
224
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
225
|
|
|
226
|
jdbcTemplate.update(APPROVE_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});
|
|
227
|
|
|
228
|
if (comment != null && !comment.isEmpty()) {
|
|
229
|
saveComment(requestId, personId, comment, template);
|
|
230
|
}
|
|
231
|
}
|
|
232
|
|
|
233
|
public void conditionallyApproveRequest(String requestId, String personId, String comment, String template) {
|
|
234
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
235
|
|
|
236
|
jdbcTemplate.update(CONDITIONALLY_APPROVE_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});
|
|
237
|
|
|
238
|
if (comment != null && !comment.isEmpty()) {
|
|
239
|
saveComment(requestId, personId, comment, template);
|
|
240
|
}
|
|
241
|
}
|
|
242
|
|
|
243
|
private void saveComment(String requestId, String personId, String comment, String template) {
|
|
244
|
String commentId = "portal::" + DigestUtils.md5Hex(comment + new Date());
|
|
245
|
|
|
246
|
new JdbcTemplate(dataSource).update("with comm as ( insert into comment (id, person, comment, date, template) values (?, ?, ?, ?, ?) returning id) insert into request_comment (request, comment) select ?, comm.id from comm",
|
|
247
|
new Object[]{commentId, personId, comment, new Date(), template, requestId}, new int[]{Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR, Types.VARCHAR});
|
|
248
|
}
|
|
249
|
|
|
250
|
public void rejectRequest(String requestId, String personId, String comment, String template) {
|
|
251
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
252
|
|
|
253
|
jdbcTemplate.update(REJECT_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});
|
|
254
|
|
|
255
|
if (comment != null && !comment.isEmpty()) {
|
|
256
|
saveComment(requestId, personId, comment, template);
|
|
257
|
}
|
|
258
|
}
|
|
259
|
|
|
260
|
public void processingRequest(String requestId, String personId, String comment, String template) {
|
|
261
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
262
|
|
|
263
|
jdbcTemplate.update(PROCESSING_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});
|
|
264
|
|
|
265
|
if (comment != null && !comment.isEmpty()) {
|
|
266
|
saveComment(requestId, personId, comment, template);
|
|
267
|
}
|
|
268
|
}
|
|
269
|
|
|
270
|
public void paidRequest(String requestId, String personId, String comment, String template, float apc_paid, float transfer_cost, float other_cost, Date datePaid) {
|
|
271
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
272
|
|
|
273
|
jdbcTemplate.update(PAID_REQUEST, new Object[]{apc_paid, transfer_cost, other_cost, datePaid, requestId}, new int[]{Types.REAL, Types.REAL, Types.REAL, Types.TIMESTAMP, Types.VARCHAR});
|
|
274
|
|
|
275
|
if (comment != null && !comment.isEmpty()) {
|
|
276
|
saveComment(requestId, personId, comment, template);
|
|
277
|
}
|
|
278
|
}
|
|
279
|
|
|
280
|
public void paidLibraryRequest(String requestId, String personId, String comment, String template, float apc_paid, float transfer_cost, float other_cost, Date datePaid) {
|
|
281
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
282
|
|
|
283
|
jdbcTemplate.update(LIBRARY_PAID_REQUEST, new Object[]{apc_paid, transfer_cost, other_cost, datePaid, requestId}, new int[]{Types.REAL, Types.REAL, Types.REAL, Types.TIMESTAMP, Types.VARCHAR});
|
|
284
|
|
|
285
|
if (comment != null && !comment.isEmpty()) {
|
|
286
|
saveComment(requestId, personId, comment, template);
|
|
287
|
}
|
|
288
|
}
|
|
289
|
|
|
290
|
public void paidPublisherRequest(String requestId, String personId, String comment, String template, float apc_paid, float transfer_cost, float other_cost, Date datePaid) {
|
|
291
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
292
|
|
|
293
|
jdbcTemplate.update(PUBLISHER_PAID_REQUEST, new Object[]{apc_paid, transfer_cost, other_cost, datePaid, requestId}, new int[]{Types.REAL, Types.REAL, Types.REAL, Types.TIMESTAMP, Types.VARCHAR});
|
|
294
|
|
|
295
|
if (comment != null && !comment.isEmpty()) {
|
|
296
|
saveComment(requestId, personId, comment, template);
|
|
297
|
}
|
|
298
|
}
|
|
299
|
|
|
300
|
public void onHoldRequest(String requestId, String personId, String comment, String template) {
|
|
301
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
302
|
|
|
303
|
jdbcTemplate.update(ONHOLD_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});
|
|
304
|
|
|
305
|
if (comment != null && !comment.isEmpty()) {
|
|
306
|
saveComment(requestId, personId, comment, template);
|
|
307
|
}
|
|
308
|
}
|
|
309
|
|
|
310
|
public void deniedRequest(String requestId, String personId, String comment, String template) {
|
|
311
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
312
|
|
|
313
|
jdbcTemplate.update(DENIED_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});
|
|
314
|
|
|
315
|
if (comment != null && !comment.isEmpty()) {
|
|
316
|
saveComment(requestId, personId, comment, template);
|
|
317
|
}
|
|
318
|
}
|
|
319
|
|
|
320
|
public int getRequestId() {
|
|
321
|
return new JdbcTemplate(dataSource).queryForObject("select nextval('request_id_seq') as id", new RowMapper<Integer>() {
|
|
322
|
@Override
|
|
323
|
public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
|
|
324
|
return rs.getInt("id");
|
|
325
|
}
|
|
326
|
});
|
|
327
|
}
|
|
328
|
|
|
329
|
public String getCSV(RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Integer[] statuses) throws IOException {
|
|
330
|
List<Object> args = new ArrayList<Object>();
|
|
331
|
List<Integer> types = new ArrayList<Integer>();
|
|
332
|
String requestQuery = this.getQuery(null, null, null, requestSortBy, order, requestFilter, term, statuses, args, types);
|
|
333
|
String query = "select r.id, r.status, r.date::date, u.firstname, u.lastname, o.name as organization, c.name as country, p.grant, p.acronym, p.startdate::date, p.enddate::date, p.scientificarea, pub.title, pub.type, r.date_paid::date, r.apc_paid, r.transfer_cost, r.other_cost, j.title as journal, (case when jp.id is not null then jp.name else mp.name end) as publisher, r.apc as apc, r.approvaldate as approvaldate, r.currency as currency, comment.comment as comment from (" + requestQuery + ") r left join person u on r.researcher = u.id left join project p on r.project=p.id left join publication pub on r.publication=pub.id left join journal j on j.id=r.journal left join publisher jp on j.publisher=jp.id left join publisher mp on mp.id=r.publisher left join organisation o on o.id=r.organization left join country c on c.id=o.country left join (select rid, comments[1] as comment from (select rid, array_agg(comments) as comments from (select rc.request as rid, c.comment as comments from comment c join request_comment rc on rc.comment=c.id join request r on r.id=rc.request and r.status = 8 order by rid, c.date desc) foo group by rid) bar order by rid \n) as comment on r.id=comment.rid";
|
|
334
|
|
|
335
|
List<String[]> requests = new JdbcTemplate(dataSource).query(query, args.toArray(), ArrayUtils.toPrimitive(types.toArray(new Integer[0])), new RowMapper<String[]>() {
|
|
336
|
@Override
|
|
337
|
public String[] mapRow(ResultSet rs, int rowNum) throws SQLException {
|
|
338
|
String[] objs = new String[24];
|
|
339
|
|
|
340
|
objs[0] = rs.getString("id");
|
|
341
|
objs[1] = Request.RequestStatus.forStatus(rs.getInt("status")).getValue();
|
|
342
|
objs[2] = new SimpleDateFormat("yyyy/MM/dd").format(rs.getDate("date"));
|
|
343
|
objs[3] = rs.getString("firstname");
|
|
344
|
objs[4] = rs.getString("lastname");
|
|
345
|
objs[5] = rs.getString("organization");
|
|
346
|
objs[6] = rs.getString("country");
|
|
347
|
objs[7] = rs.getString("grant");
|
|
348
|
objs[8] = rs.getString("acronym");
|
|
349
|
objs[9] = rs.getDate("startdate") != null?new SimpleDateFormat("yyyy/MM/dd").format(rs.getDate("startdate")):null;
|
|
350
|
objs[10] = rs.getDate("enddate") != null?new SimpleDateFormat("yyyy/MM/dd").format(rs.getDate("enddate")):null;
|
|
351
|
objs[11] = rs.getString("scientificarea");
|
|
352
|
objs[12] = rs.getString("title");
|
|
353
|
objs[13] = rs.getString("type");
|
|
354
|
objs[14] = rs.getString("journal");
|
|
355
|
objs[15] = rs.getString("publisher");
|
|
356
|
objs[16] = rs.getDate("approvaldate") != null?new SimpleDateFormat("yyyy/MM/dd").format(rs.getDate("approvaldate")):null;
|
|
357
|
objs[17] = Float.toString(rs.getFloat("apc"));
|
|
358
|
objs[18] = rs.getString("currency");
|
|
359
|
objs[19] = rs.getDate("date_paid")!=null?new SimpleDateFormat("yyyy/MM/dd").format(rs.getDate("date_paid")):null;
|
|
360
|
objs[20] = rs.getFloat("apc_paid") +"";
|
|
361
|
objs[21] = rs.getFloat("transfer_cost") + "";
|
|
362
|
objs[22] = rs.getFloat("other_cost") + "";
|
|
363
|
objs[23] = rs.getString("comment");
|
|
364
|
|
|
365
|
return objs;
|
|
366
|
}
|
|
367
|
});
|
|
368
|
|
|
369
|
StringWriter sw = new StringWriter();
|
|
370
|
CSVWriter csvWriter = new CSVWriter(sw);
|
|
371
|
|
|
372
|
csvWriter.writeNext(new String[]{"id", "Status", "Submission Date", "Firstname", "Lastname", "Organization", "Country", "Project id", "Acronym", "Start Date", "End Date", "Scientific Area", "Title", "Type", "Journal", "Publisher", "Date Approved", "APC requested", "Currency", "Date Paid", "APC paid", "Transfer Cost", "Other Expenses", "Rejection Reason"}, false);
|
|
373
|
csvWriter.writeAll(requests, false);
|
|
374
|
|
|
375
|
csvWriter.close();
|
|
376
|
sw.close();
|
|
377
|
|
|
378
|
return sw.toString();
|
|
379
|
}
|
|
380
|
|
|
381
|
private RequestPage getRequestPage(String personId, List<String> organizationIds, String publisherId, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Integer[] statusFilterCodes, int from, int to) {
|
|
382
|
RequestPage page = new RequestPage();
|
|
383
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
384
|
List<Object> args = new ArrayList<Object>();
|
|
385
|
List<Integer> types = new ArrayList<Integer>();
|
|
386
|
String query = getQuery(personId, organizationIds, publisherId, requestSortBy, order, requestFilter, term, statusFilterCodes, args, types);
|
|
387
|
|
|
388
|
|
|
389
|
page.setRequests(jdbcTemplate.query(applyPaging(query, from, to), args.toArray(), ArrayUtils.toPrimitive(types.toArray(new Integer[0])), requestRowMapper));
|
|
390
|
page.setTotal(jdbcTemplate.queryForObject(getCountQuery(query), args.toArray(), ArrayUtils.toPrimitive(types.toArray(new Integer[0])), Integer.class));
|
|
391
|
page.setFrom(from);
|
|
392
|
page.setTo(to);
|
|
393
|
|
|
394
|
return page;
|
|
395
|
}
|
|
396
|
|
|
397
|
private String getCountQuery(String query) {
|
|
398
|
StringBuilder sb = new StringBuilder();
|
|
399
|
|
|
400
|
sb.append("select count(*) from (").append(query).append(") foo");
|
|
401
|
|
|
402
|
return sb.toString();
|
|
403
|
}
|
|
404
|
|
|
405
|
private String getQuery(String personId, List<String> organizationIds, String publisherId, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Integer[] statusFilterCodes, List<Object> args, List<Integer> types) {
|
|
406
|
Map<String, String> joins = new HashMap<String, String>();
|
|
407
|
List<String> whereClauses = new ArrayList<String>();
|
|
408
|
List<Object> whereArgs = new ArrayList<Object>();
|
|
409
|
List<Integer> whereTypes = new ArrayList<Integer>();
|
|
410
|
|
|
411
|
String orderBy = null;
|
|
412
|
|
|
413
|
if (term != null) {
|
|
414
|
if (requestFilter != null) {
|
|
415
|
switch (requestFilter) {
|
|
416
|
case ID:
|
|
417
|
whereClauses.add("(r.id like ? )");
|
|
418
|
whereArgs.add("%" + term + "%");
|
|
419
|
types.add(Types.VARCHAR);
|
|
420
|
break;
|
|
421
|
case USER:
|
|
422
|
joins.put("user", " join person u on u.id=r.user and (lower(u.firstname) like lower(?) or lower(u.initials) like lower(?) or lower(u.lastname) like lower(?)) ");
|
|
423
|
args.add("%" + term + "%");
|
|
424
|
args.add("%" + term + "%");
|
|
425
|
args.add("%" + term + "%");
|
|
426
|
types.add(Types.VARCHAR);
|
|
427
|
types.add(Types.VARCHAR);
|
|
428
|
types.add(Types.VARCHAR);
|
|
429
|
break;
|
|
430
|
case RESEARCHER:
|
|
431
|
joins.put("researcher", " join person res on res.id=r.researcher and (lower(res.firstname) like lower(?) or lower(res.initials) like lower(?) or lower(res.lastname) like lower(?)) ");
|
|
432
|
args.add("%" + term + "%");
|
|
433
|
args.add("%" + term + "%");
|
|
434
|
args.add("%" + term + "%");
|
|
435
|
types.add(Types.VARCHAR);
|
|
436
|
types.add(Types.VARCHAR);
|
|
437
|
types.add(Types.VARCHAR);
|
|
438
|
break;
|
|
439
|
case PROJECT:
|
|
440
|
joins.put("project", " join project proj on proj.id=r.project and (lower(proj.acronym) like lower(?) or lower(proj.title) like lower(?) or lower(proj.grant) like lower(?)) ");
|
|
441
|
args.add("%" + term + "%");
|
|
442
|
args.add("%" + term + "%");
|
|
443
|
args.add("%" + term + "%");
|
|
444
|
types.add(Types.VARCHAR);
|
|
445
|
types.add(Types.VARCHAR);
|
|
446
|
types.add(Types.VARCHAR);
|
|
447
|
break;
|
|
448
|
case PUBLICATION:
|
|
449
|
joins.put("publication", " join publication pub on pub.id=r.publication and (lower(pub.title) like lower(?)) ");
|
|
450
|
args.add("%" + term + "%");
|
|
451
|
types.add(Types.VARCHAR);
|
|
452
|
break;
|
|
453
|
case JOURNAL:
|
|
454
|
joins.put("journal", " join journal j on j.id=r.journal and (lower(j.title) like lower(?)) ");
|
|
455
|
args.add("%" + term + "%");
|
|
456
|
types.add(Types.VARCHAR);
|
|
457
|
break;
|
|
458
|
case PUBLISHER:
|
|
459
|
joins.put("publisher", " join publisher publ on publ.id=r.publisher and (lower(publ.name) like lower(?)) ");
|
|
460
|
args.add("%" + term + "%");
|
|
461
|
types.add(Types.VARCHAR);
|
|
462
|
break;
|
|
463
|
case INVOICE:
|
|
464
|
joins.put("invoice", "join invoice i on i.id=r.invoice and (lower(i.number) like lower(?) or lower(alternativeid) like lower(?))");
|
|
465
|
args.add("%" + term + "%");
|
|
466
|
args.add("%" + term + "%");
|
|
467
|
types.add(Types.VARCHAR);
|
|
468
|
types.add(Types.VARCHAR);
|
|
469
|
break;
|
|
470
|
}
|
|
471
|
} else {
|
|
472
|
whereClauses.add("(r.id like ? )");
|
|
473
|
whereArgs.add("%" + term + "%");
|
|
474
|
types.add(Types.VARCHAR);
|
|
475
|
|
|
476
|
joins.put("user", " left join person u on u.id=r.user ");
|
|
477
|
whereClauses.add("(lower(u.firstname) like lower(?) or lower(u.initials) like lower(?) or lower(u.lastname) like lower(?))");
|
|
478
|
whereArgs.add("%" + term + "%");
|
|
479
|
whereArgs.add("%" + term + "%");
|
|
480
|
whereArgs.add("%" + term + "%");
|
|
481
|
whereTypes.add(Types.VARCHAR);
|
|
482
|
whereTypes.add(Types.VARCHAR);
|
|
483
|
whereTypes.add(Types.VARCHAR);
|
|
484
|
joins.put("researcher", " left join person res on res.id=r.researcher ");
|
|
485
|
whereClauses.add("(lower(res.firstname) like lower(?) or lower(res.initials) like lower(?) or lower(res.lastname) like lower(?))");
|
|
486
|
whereArgs.add("%" + term + "%");
|
|
487
|
whereArgs.add("%" + term + "%");
|
|
488
|
whereArgs.add("%" + term + "%");
|
|
489
|
whereTypes.add(Types.VARCHAR);
|
|
490
|
whereTypes.add(Types.VARCHAR);
|
|
491
|
whereTypes.add(Types.VARCHAR);
|
|
492
|
joins.put("project", " left join project proj on proj.id=r.project ");
|
|
493
|
whereClauses.add("(lower(proj.acronym) like lower(?) or lower(proj.title) like lower(?) or lower(proj.grant) like lower(?))");
|
|
494
|
whereArgs.add("%" + term + "%");
|
|
495
|
whereArgs.add("%" + term + "%");
|
|
496
|
whereArgs.add("%" + term + "%");
|
|
497
|
whereTypes.add(Types.VARCHAR);
|
|
498
|
whereTypes.add(Types.VARCHAR);
|
|
499
|
whereTypes.add(Types.VARCHAR);
|
|
500
|
joins.put("publication", " left join publication pub on pub.id=r.publication ");
|
|
501
|
whereClauses.add("(lower(pub.title) like lower(?))");
|
|
502
|
whereArgs.add("%" + term + "%");
|
|
503
|
whereTypes.add(Types.VARCHAR);
|
|
504
|
joins.put("journal", " left join journal j on j.id=r.journal ");
|
|
505
|
whereClauses.add("(lower(j.title) like lower(?))");
|
|
506
|
whereArgs.add("%" + term + "%");
|
|
507
|
whereTypes.add(Types.VARCHAR);
|
|
508
|
joins.put("publisher", " left join publisher publ on publ.id=r.publisher ");
|
|
509
|
whereClauses.add("(lower(publ.name) like lower(?))");
|
|
510
|
whereArgs.add("%" + term + "%");
|
|
511
|
whereTypes.add(Types.VARCHAR);
|
|
512
|
joins.put("invoice", " left join invoice i on i.id=r.invoice ");
|
|
513
|
whereClauses.add("(lower(i.number) like lower(?) or lower(alternativeid) like lower(?))");
|
|
514
|
whereArgs.add("%" + term + "%");
|
|
515
|
whereArgs.add("%" + term + "%");
|
|
516
|
whereTypes.add(Types.VARCHAR);
|
|
517
|
whereTypes.add(Types.VARCHAR);
|
|
518
|
}
|
|
519
|
|
|
520
|
}
|
|
521
|
|
|
522
|
if (requestSortBy != null) {
|
|
523
|
switch (requestSortBy) {
|
|
524
|
case DATE:
|
|
525
|
orderBy = " r.date ";
|
|
526
|
break;
|
|
527
|
case FUNDING_REQUESTED:
|
|
528
|
orderBy = " r.fundingrequested ";
|
|
529
|
break;
|
|
530
|
case USER:
|
|
531
|
if (joins.get("user") == null)
|
|
532
|
joins.put("user", " left join person u on u.id=r.user ");
|
|
533
|
|
|
534
|
orderBy = " u.firstname, u.initials, u.lastname ";
|
|
535
|
break;
|
|
536
|
case PUBLICATION:
|
|
537
|
if (joins.get("publication") == null)
|
|
538
|
joins.put("publication", " left join publication pub on r.publication=pub.id ");
|
|
539
|
|
|
540
|
orderBy = " pub.title ";
|
|
541
|
break;
|
|
542
|
case RESEARCHER:
|
|
543
|
if (joins.get("researcher") == null)
|
|
544
|
joins.put("researcher", " left join person res on res.id=r.researcher ");
|
|
545
|
|
|
546
|
orderBy = " res.firstname, res.initials, res.lastname ";
|
|
547
|
break;
|
|
548
|
case PROJECT:
|
|
549
|
if (joins.get("project") == null)
|
|
550
|
joins.put("project", " left join project proj on proj.id=r.project ");
|
|
551
|
|
|
552
|
orderBy = " proj.acronym ";
|
|
553
|
break;
|
|
554
|
case JOURNAL:
|
|
555
|
if (joins.get("journal") == null)
|
|
556
|
joins.put("journal", " left join journal j on j.id=r.journal ");
|
|
557
|
|
|
558
|
orderBy = " j.title ";
|
|
559
|
break;
|
|
560
|
case PUBLISHER:
|
|
561
|
if (joins.get("publisher") == null)
|
|
562
|
joins.put("publisher", " left join publisher publ on publ.id=r.publisher ");
|
|
563
|
|
|
564
|
orderBy = " publ.name ";
|
|
565
|
break;
|
|
566
|
case STATUS:
|
|
567
|
default:
|
|
568
|
orderBy = " r.status ";
|
|
569
|
break;
|
|
570
|
}
|
|
571
|
}
|
|
572
|
|
|
573
|
StringBuilder sb = new StringBuilder("select r.id, r.\"user\", r.date, r.researcher, r.organization, r.project, r.publication, r.journal, r.publisher, r.publisher_email, r.budget, r.invoice, r.apc, r.discount, r.projectparticipation, r.fundingrequested, r.currency, r.bank_name, r.bank_address, r.bank_code, r.bank_holder, r.bank_iban, r.apc_paid, r.transfer_cost, r.other_cost, r.date_paid, r.submissiondate, r.approvaldate, r.status from request r ");
|
|
574
|
|
|
575
|
for (String join : joins.values())
|
|
576
|
sb.append(" ").append(join).append(" ");
|
|
577
|
|
|
578
|
|
|
579
|
sb.append(" where ");
|
|
580
|
|
|
581
|
if (personId != null) {
|
|
582
|
sb.append("r.\"user\"=? ");
|
|
583
|
|
|
584
|
args.add(personId);
|
|
585
|
types.add(Types.VARCHAR);
|
|
586
|
} else if (organizationIds != null) {
|
|
587
|
sb.append("(");
|
|
588
|
|
|
589
|
for (int i = 0; i < organizationIds.size(); i++) {
|
|
590
|
if (i > 0)
|
|
591
|
sb.append(" or ");
|
|
592
|
|
|
593
|
sb.append(" r.organization=? ");
|
|
594
|
|
|
595
|
args.add(organizationIds.get(i));
|
|
596
|
types.add(Types.VARCHAR);
|
|
597
|
}
|
|
598
|
|
|
599
|
sb.append(")");
|
|
600
|
|
|
601
|
} else if (publisherId != null) {
|
|
602
|
|
|
603
|
if (joins.get("journal") == null) {
|
|
604
|
sb.delete(sb.lastIndexOf("where"), sb.length());
|
|
605
|
sb.append(" left join journal j on j.id=r.journal ");
|
|
606
|
sb.append(" where ");
|
|
607
|
}
|
|
608
|
|
|
609
|
sb.append("(r.publisher=? or j.publisher=?)");
|
|
610
|
|
|
611
|
args.add(publisherId);
|
|
612
|
types.add(Types.VARCHAR);
|
|
613
|
args.add(publisherId);
|
|
614
|
types.add(Types.VARCHAR);
|
|
615
|
}
|
|
616
|
|
|
617
|
if (statusFilterCodes != null) {
|
|
618
|
StringBuilder ssb = new StringBuilder();
|
|
619
|
|
|
620
|
for (Integer code:statusFilterCodes) {
|
|
621
|
String clause;
|
|
622
|
|
|
623
|
switch (code) {
|
|
624
|
default:
|
|
625
|
clause = " (r.status = " + code + ")";
|
|
626
|
break;
|
|
627
|
}
|
|
628
|
|
|
629
|
ssb.append(ssb.length()==0?clause:" or " + clause);
|
|
630
|
}
|
|
631
|
|
|
632
|
if (sb.toString().trim().endsWith("where"))
|
|
633
|
sb.append("(").append(ssb.toString()).append(")");
|
|
634
|
else
|
|
635
|
sb.append(" and ").append("(").append(ssb.toString()).append(")");
|
|
636
|
}
|
|
637
|
|
|
638
|
if (whereClauses.size() > 0) {
|
|
639
|
if (!sb.toString().trim().endsWith("where"))
|
|
640
|
sb.append(" and ");
|
|
641
|
sb.append("(");
|
|
642
|
|
|
643
|
for (String clause : whereClauses) {
|
|
644
|
sb.append(clause);
|
|
645
|
sb.append(" or ");
|
|
646
|
}
|
|
647
|
|
|
648
|
sb.delete(sb.lastIndexOf("or"), sb.length());
|
|
649
|
sb.append(") ");
|
|
650
|
|
|
651
|
args.addAll(whereArgs);
|
|
652
|
types.addAll(whereTypes);
|
|
653
|
}
|
|
654
|
|
|
655
|
if (sb.toString().trim().endsWith("where"))
|
|
656
|
sb.delete(sb.lastIndexOf("where"), sb.length());
|
|
657
|
|
|
658
|
if (orderBy != null) {
|
|
659
|
sb.append(" order by ").append(orderBy);
|
|
660
|
|
|
661
|
if (order == RequestSortOrder.ASCENDING)
|
|
662
|
sb.append(" asc");
|
|
663
|
else
|
|
664
|
sb.append(" desc");
|
|
665
|
}
|
|
666
|
|
|
667
|
return sb.toString();
|
|
668
|
}
|
|
669
|
|
|
670
|
private String applyPaging(String query, int from, int to) {
|
|
671
|
StringBuilder sb = new StringBuilder(query);
|
|
672
|
|
|
673
|
if (from > 0 || to > 0) {
|
|
674
|
sb.append(" limit ").append(to - from + 1);
|
|
675
|
sb.append(" offset ").append(from);
|
|
676
|
}
|
|
677
|
|
|
678
|
return sb.toString();
|
|
679
|
}
|
|
680
|
|
|
681
|
private Integer[] getStatusFilter(Request.RequestStatus[] statuses) {
|
|
682
|
if (statuses == null)
|
|
683
|
return null;
|
|
684
|
else {
|
|
685
|
Integer[] res = new Integer[statuses.length];
|
|
686
|
|
|
687
|
for (int i = 0; i < statuses.length; i++)
|
|
688
|
res[i] = statuses[i].getCode();
|
|
689
|
|
|
690
|
return res;
|
|
691
|
}
|
|
692
|
}
|
|
693
|
|
|
694
|
public List<Comment> getComments(String id) {
|
|
695
|
try {
|
|
696
|
return new JdbcTemplate(dataSource).query("select c.comment, c.date, c.person, c.template from comment c join request_comment rc on rc.comment=c.id and rc.request=? order by date",
|
|
697
|
new String[]{id}, new int[]{Types.VARCHAR}, new RowMapper<Comment>() {
|
|
698
|
@Override
|
|
699
|
public Comment mapRow(ResultSet rs, int rowNum) throws SQLException {
|
|
700
|
return new Comment(new Person(rs.getString("person")), rs.getTimestamp("date"), rs.getString("comment"), rs.getString("template"));
|
|
701
|
}
|
|
702
|
});
|
|
703
|
} catch (EmptyResultDataAccessException e) {
|
|
704
|
return null;
|
|
705
|
}
|
|
706
|
}
|
|
707
|
|
|
708
|
public void uploadBankTransferReceipt(final String requestid, final String contentType, InputStream inputStream) {
|
|
709
|
try {
|
|
710
|
final ByteArrayOutputStream baos = new ByteArrayOutputStream();
|
|
711
|
final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
|
712
|
|
|
713
|
IOUtils.copy(inputStream, baos);
|
|
714
|
IOUtils.closeQuietly(baos);
|
|
715
|
|
|
716
|
final PreparedStatementSetter pss = new PreparedStatementSetter() {
|
|
717
|
@Override
|
|
718
|
public void setValues(PreparedStatement ps) throws SQLException {
|
|
719
|
ps.setString(1, contentType);
|
|
720
|
ps.setBytes(2, baos.toByteArray());
|
|
721
|
ps.setString(3, requestid);
|
|
722
|
}
|
|
723
|
};
|
|
724
|
|
|
725
|
jdbcTemplate.update("update request set bank_receipt_contenttype=?, bank_receipt=? where id=?", pss);
|
|
726
|
|
|
727
|
} catch (Exception e) {
|
|
728
|
e.printStackTrace();
|
|
729
|
}
|
|
730
|
}
|
|
731
|
|
|
732
|
public BankTransferReceipt downloadBankTransferReceipt(String requestId) {
|
|
733
|
return new JdbcTemplate(dataSource).queryForObject("select bank_receipt_contenttype, bank_receipt from request where id=?", new String[]{requestId}, new int[]{Types.VARCHAR}, new RowMapper<BankTransferReceipt>() {
|
|
734
|
@Override
|
|
735
|
public BankTransferReceipt mapRow(ResultSet rs, int rowNum) throws SQLException {
|
|
736
|
return new BankTransferReceipt(rs.getString("bank_receipt_contenttype"), rs.getBytes("bank_receipt"));
|
|
737
|
}
|
|
738
|
});
|
|
739
|
}
|
|
740
|
|
|
741
|
public List<CommentTemplate> getCommentTemplates(Request.RequestStatus requestStatus) {
|
|
742
|
return new JdbcTemplate(dataSource).query(GET_COMMENT_TEMPLATES, new Integer[]{requestStatus.getCode()}, new int[]{Types.INTEGER}, new RowMapper<CommentTemplate>() {
|
|
743
|
@Override
|
|
744
|
public CommentTemplate mapRow(ResultSet rs, int rowNum) throws SQLException {
|
|
745
|
return new CommentTemplate(rs.getString("id"), Request.RequestStatus.forStatus(rs.getInt("status")), rs.getString("name"), rs.getString("comment"));
|
|
746
|
}
|
|
747
|
});
|
|
748
|
}
|
|
749
|
}
|