| 1 |
35828
|
antonis.le
|
package eu.dnetlib.goldoa.service.dao;
|
| 2 |
|
|
|
| 3 |
39076
|
antonis.le
|
import eu.dnetlib.goldoa.domain.BankAccount;
|
| 4 |
|
|
import eu.dnetlib.goldoa.domain.BankTransferReceipt;
|
| 5 |
|
|
import eu.dnetlib.goldoa.domain.Comment;
|
| 6 |
36876
|
antonis.le
|
import eu.dnetlib.goldoa.domain.Currency;
|
| 7 |
39076
|
antonis.le
|
import eu.dnetlib.goldoa.domain.Funder;
|
| 8 |
|
|
import eu.dnetlib.goldoa.domain.Person;
|
| 9 |
|
|
import eu.dnetlib.goldoa.domain.Request;
|
| 10 |
|
|
import eu.dnetlib.goldoa.domain.RequestCoFunder;
|
| 11 |
|
|
import eu.dnetlib.goldoa.domain.RequestFilter;
|
| 12 |
|
|
import eu.dnetlib.goldoa.domain.RequestSort;
|
| 13 |
|
|
import eu.dnetlib.goldoa.domain.RequestSortOrder;
|
| 14 |
35828
|
antonis.le
|
import org.apache.commons.codec.digest.DigestUtils;
|
| 15 |
38128
|
antonis.le
|
import org.apache.commons.io.IOUtils;
|
| 16 |
35919
|
antonis.le
|
import org.apache.commons.lang3.ArrayUtils;
|
| 17 |
35828
|
antonis.le
|
import org.springframework.beans.factory.annotation.Autowired;
|
| 18 |
36112
|
antonis.le
|
import org.springframework.dao.EmptyResultDataAccessException;
|
| 19 |
37000
|
antonis.le
|
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
|
| 20 |
35828
|
antonis.le
|
import org.springframework.jdbc.core.JdbcTemplate;
|
| 21 |
38128
|
antonis.le
|
import org.springframework.jdbc.core.PreparedStatementSetter;
|
| 22 |
35828
|
antonis.le
|
import org.springframework.jdbc.core.RowMapper;
|
| 23 |
|
|
|
| 24 |
|
|
import javax.sql.DataSource;
|
| 25 |
38128
|
antonis.le
|
import java.io.ByteArrayOutputStream;
|
| 26 |
|
|
import java.io.InputStream;
|
| 27 |
37000
|
antonis.le
|
import java.sql.PreparedStatement;
|
| 28 |
35828
|
antonis.le
|
import java.sql.ResultSet;
|
| 29 |
|
|
import java.sql.SQLException;
|
| 30 |
|
|
import java.sql.Types;
|
| 31 |
39076
|
antonis.le
|
import java.util.ArrayList;
|
| 32 |
|
|
import java.util.Date;
|
| 33 |
|
|
import java.util.HashMap;
|
| 34 |
|
|
import java.util.List;
|
| 35 |
|
|
import java.util.Map;
|
| 36 |
35828
|
antonis.le
|
|
| 37 |
|
|
/**
|
| 38 |
|
|
* Created by antleb on 3/30/15.
|
| 39 |
|
|
*/
|
| 40 |
|
|
public class RequestDAO {
|
| 41 |
|
|
|
| 42 |
39076
|
antonis.le
|
@Autowired
|
| 43 |
|
|
private DataSource dataSource;
|
| 44 |
35828
|
antonis.le
|
|
| 45 |
39076
|
antonis.le
|
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 from request where project=?";
|
| 46 |
35828
|
antonis.le
|
|
| 47 |
39076
|
antonis.le
|
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 from request where id=?";
|
| 48 |
35828
|
antonis.le
|
|
| 49 |
39076
|
antonis.le
|
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=? where id = ?";
|
| 50 |
35828
|
antonis.le
|
|
| 51 |
39076
|
antonis.le
|
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, id) values (?, ?, ?, ?, ?, ? ,? ,? ,? ,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
|
| 52 |
35828
|
antonis.le
|
|
| 53 |
39076
|
antonis.le
|
private final static String APPROVE_REQUEST = "update request set status = (status & ~" + (Request.RequestStatus.REJECTED.getCode() | Request.RequestStatus.INITIALLY_APPROVED.getCode()) + ") | " + Request.RequestStatus.APPROVED.getCode() + " where id=?";
|
| 54 |
35828
|
antonis.le
|
|
| 55 |
39076
|
antonis.le
|
private final static String INITIALLY_APPROVE_REQUEST = "update request set status = (status & ~" + (Request.RequestStatus.REJECTED.getCode() | Request.RequestStatus.APPROVED.getCode()) + ") | " + Request.RequestStatus.INITIALLY_APPROVED.getCode() + " where id=?";
|
| 56 |
35828
|
antonis.le
|
|
| 57 |
39076
|
antonis.le
|
private final static String REJECT_REQUEST = "update request set status = (status & ~" + (Request.RequestStatus.APPROVED.getCode() | Request.RequestStatus.INITIALLY_APPROVED.getCode()) + ") | " + Request.RequestStatus.REJECTED.getCode() + " where id=?";
|
| 58 |
39075
|
antonis.le
|
|
| 59 |
39076
|
antonis.le
|
private final static String PAID_REQUEST = "update request set status = (status & ~" + (Request.RequestStatus.ACCOUNTING_ON_HOLD.getCode() | Request.RequestStatus.ACCOUNTING_DENIED.getCode()) + ") | " + Request.RequestStatus.ACCOUNTING_PAID.getCode() + ", apc_paid=?, transfer_cost=?, other_cost=?, date_paid=? where id=?";
|
| 60 |
36847
|
antonis.le
|
|
| 61 |
39076
|
antonis.le
|
private final static String ONHOLD_REQUEST = "update request set status = (status & ~" + (Request.RequestStatus.ACCOUNTING_PAID.getCode() | Request.RequestStatus.ACCOUNTING_DENIED.getCode()) + ") | " + Request.RequestStatus.ACCOUNTING_ON_HOLD.getCode() + " where id=?";
|
| 62 |
36847
|
antonis.le
|
|
| 63 |
39076
|
antonis.le
|
private final static String DENIED_REQUEST = "update request set status = (status & ~" + (Request.RequestStatus.ACCOUNTING_ON_HOLD.getCode() | Request.RequestStatus.ACCOUNTING_PAID.getCode()) + ") | " + Request.RequestStatus.ACCOUNTING_DENIED.getCode() + " where id=?";
|
| 64 |
36847
|
antonis.le
|
|
| 65 |
39076
|
antonis.le
|
private final static String INVOICE_UPLOADED = "update request set invoice=?, status = status | " + Request.RequestStatus.INVOICE_UPLOADED.getCode() + " where id=?";
|
| 66 |
35828
|
antonis.le
|
|
| 67 |
39076
|
antonis.le
|
private RowMapper<Request> requestRowMapper = new RowMapper<Request>() {
|
| 68 |
|
|
@Override
|
| 69 |
|
|
public Request mapRow(ResultSet rs, int rowNum) throws SQLException {
|
| 70 |
|
|
BankAccount bankAccount = new BankAccount(rs.getString("bank_name"), rs.getString("bank_address"), rs.getString("bank_code"), rs.getString("bank_holder"), rs.getString("bank_iban"));
|
| 71 |
36861
|
antonis.le
|
|
| 72 |
39076
|
antonis.le
|
Request request = new Request(
|
| 73 |
|
|
rs.getString("id"), rs.getString("user"), rs.getTimestamp("date"), rs.getString("researcher"), rs.getString("organization"),
|
| 74 |
|
|
rs.getString("project"), rs.getString("publication"), rs.getString("journal"), rs.getString("publisher"), rs.getString("publisher_email"),
|
| 75 |
|
|
rs.getString("budget"), rs.getString("invoice"), rs.getFloat("apc"), rs.getFloat("discount"), rs.getFloat("projectparticipation"),
|
| 76 |
|
|
rs.getFloat("fundingrequested"), null, bankAccount, new ArrayList<RequestCoFunder>(), rs.getFloat("apc_paid"), rs.getFloat("transfer_cost"), rs.getFloat("other_cost"), rs.getTimestamp("date_paid"), rs.getInt("status"));
|
| 77 |
36082
|
antonis.le
|
|
| 78 |
39076
|
antonis.le
|
String currency = rs.getString("currency");
|
| 79 |
|
|
if (rs.wasNull())
|
| 80 |
|
|
request.setCurrency(null);
|
| 81 |
|
|
else
|
| 82 |
|
|
request.setCurrency(Currency.valueOf(currency));
|
| 83 |
36950
|
antonis.le
|
|
| 84 |
39076
|
antonis.le
|
rs.getFloat("apc");
|
| 85 |
|
|
if (rs.wasNull())
|
| 86 |
|
|
request.setApc(null);
|
| 87 |
|
|
rs.getFloat("discount");
|
| 88 |
|
|
if (rs.wasNull())
|
| 89 |
|
|
request.setDiscount(null);
|
| 90 |
|
|
rs.getFloat("projectparticipation");
|
| 91 |
|
|
if (rs.wasNull())
|
| 92 |
|
|
request.setProjectParticipation(null);
|
| 93 |
|
|
rs.getFloat("fundingrequested");
|
| 94 |
|
|
if (rs.wasNull())
|
| 95 |
|
|
request.setFundingRequested(null);
|
| 96 |
|
|
rs.getFloat("apc_paid");
|
| 97 |
|
|
if (rs.wasNull())
|
| 98 |
|
|
request.setApc_paid(null);
|
| 99 |
|
|
rs.getFloat("transfer_cost");
|
| 100 |
|
|
if (rs.wasNull())
|
| 101 |
|
|
request.setTransfer_cost(null);
|
| 102 |
|
|
rs.getFloat("other_cost");
|
| 103 |
|
|
if (rs.wasNull())
|
| 104 |
|
|
request.setOther_cost(null);
|
| 105 |
36082
|
antonis.le
|
|
| 106 |
39076
|
antonis.le
|
return request;
|
| 107 |
|
|
}
|
| 108 |
|
|
};
|
| 109 |
35828
|
antonis.le
|
|
| 110 |
36071
|
antonis.le
|
|
| 111 |
39076
|
antonis.le
|
public void saveRequest(final Request request) {
|
| 112 |
|
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
| 113 |
|
|
Object[] args;
|
| 114 |
|
|
int[] types = new int[]{Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
|
| 115 |
|
|
Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.REAL, Types.REAL, Types.REAL,
|
| 116 |
|
|
Types.REAL, Types.VARCHAR, Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
|
| 117 |
|
|
Types.VARCHAR, Types.REAL, Types.REAL, Types.REAL, Types.TIMESTAMP, Types.VARCHAR};
|
| 118 |
35828
|
antonis.le
|
|
| 119 |
39076
|
antonis.le
|
String currency = request.getCurrency() != null ? request.getCurrency().name() : null;
|
| 120 |
36944
|
antonis.le
|
|
| 121 |
39076
|
antonis.le
|
if (request.getBankAccount() != null) {
|
| 122 |
|
|
args = new Object[]{request.getUser(), request.getDate(), request.getResearcher(), request.getOrganization(), request.getProject(),
|
| 123 |
|
|
request.getPublication(), request.getJournal(), request.getPublisher(), request.getPublisherEmail(), request.getBudget(), request.getInvoice(),
|
| 124 |
|
|
request.getApc(), request.getDiscount(), request.getProjectParticipation(), request.getFundingRequested(), currency,
|
| 125 |
|
|
request.getStatus(), request.getBankAccount().getBankName(), request.getBankAccount().getBankAddress(), request.getBankAccount().getBankCode(), request.getBankAccount().getAccountHolder(),
|
| 126 |
|
|
request.getBankAccount().getIban(), request.getApc_paid(), request.getTransfer_cost(), request.getOther_cost(), request.getDate_paid(), request.getId()};
|
| 127 |
|
|
} else {
|
| 128 |
|
|
args = new Object[]{request.getUser(), request.getDate(), request.getResearcher(), request.getOrganization(), request.getProject(),
|
| 129 |
|
|
request.getPublication(), request.getJournal(), request.getPublisher(), request.getPublisherEmail(), request.getBudget(), request.getInvoice(),
|
| 130 |
|
|
request.getApc(), request.getDiscount(), request.getProjectParticipation(), request.getFundingRequested(), currency, request.getStatus(), null, null, null, null, null,
|
| 131 |
38128
|
antonis.le
|
request.getApc_paid(), request.getTransfer_cost(), request.getOther_cost(), request.getDate_paid(), request.getId()};
|
| 132 |
39076
|
antonis.le
|
}
|
| 133 |
36861
|
antonis.le
|
|
| 134 |
39076
|
antonis.le
|
if (jdbcTemplate.update(UPDATE_REQUEST, args, types) == 0) {
|
| 135 |
|
|
jdbcTemplate.update(INSERT_REQUEST, args, types);
|
| 136 |
|
|
}
|
| 137 |
|
|
}
|
| 138 |
35828
|
antonis.le
|
|
| 139 |
39076
|
antonis.le
|
public void saveCoFunders(final Request request) {
|
| 140 |
|
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
| 141 |
|
|
jdbcTemplate.update("delete from request_cofunder where request=?", new String[]{request.getId()}, new int[]{Types.VARCHAR});
|
| 142 |
|
|
jdbcTemplate.batchUpdate("insert into request_cofunder (request, funder, percentage) values (?,?,?)", new BatchPreparedStatementSetter() {
|
| 143 |
37000
|
antonis.le
|
|
| 144 |
39076
|
antonis.le
|
@Override
|
| 145 |
|
|
public void setValues(PreparedStatement ps, int i) throws SQLException {
|
| 146 |
|
|
ps.setString(1, request.getId());
|
| 147 |
|
|
ps.setString(2, request.getCoFunders().get(i).getFunder().getId());
|
| 148 |
|
|
ps.setFloat(3, request.getCoFunders().get(i).getPercentage());
|
| 149 |
|
|
}
|
| 150 |
37000
|
antonis.le
|
|
| 151 |
39076
|
antonis.le
|
@Override
|
| 152 |
|
|
public int getBatchSize() {
|
| 153 |
|
|
if (request.getCoFunders() != null)
|
| 154 |
|
|
return request.getCoFunders().size();
|
| 155 |
|
|
else
|
| 156 |
|
|
return 0;
|
| 157 |
|
|
}
|
| 158 |
|
|
});
|
| 159 |
|
|
}
|
| 160 |
37000
|
antonis.le
|
|
| 161 |
39076
|
antonis.le
|
public List<RequestCoFunder> getCoFunders(final Request request) {
|
| 162 |
|
|
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>() {
|
| 163 |
|
|
@Override
|
| 164 |
|
|
public RequestCoFunder mapRow(ResultSet rs, int rowNum) throws SQLException {
|
| 165 |
|
|
Funder funder = new Funder(rs.getString("id"), rs.getString("name"), rs.getString("url"), rs.getString("source"));
|
| 166 |
37000
|
antonis.le
|
|
| 167 |
39076
|
antonis.le
|
return new RequestCoFunder(request, funder, rs.getFloat("percentage"));
|
| 168 |
|
|
}
|
| 169 |
|
|
});
|
| 170 |
|
|
}
|
| 171 |
37000
|
antonis.le
|
|
| 172 |
39076
|
antonis.le
|
public Request getRequest(String requestId) {
|
| 173 |
|
|
return new JdbcTemplate(dataSource).queryForObject(GET_BY_ID, new String[]{requestId}, new int[]{
|
| 174 |
|
|
Types.VARCHAR}, requestRowMapper);
|
| 175 |
|
|
}
|
| 176 |
35828
|
antonis.le
|
|
| 177 |
39076
|
antonis.le
|
public List<Request> getForUser(String personId, Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus[] statusFilter, int from, int to) {
|
| 178 |
|
|
List<Object> args = new ArrayList<Object>();
|
| 179 |
|
|
List<Integer> types = new ArrayList<Integer>();
|
| 180 |
35919
|
antonis.le
|
|
| 181 |
39076
|
antonis.le
|
String query = getQuery(personId, null, null, requestSortBy, order, requestFilter, term, statusFilter, args, types, from, to);
|
| 182 |
35945
|
antonis.le
|
|
| 183 |
39076
|
antonis.le
|
return new JdbcTemplate(dataSource).query(query, args.toArray(), ArrayUtils.toPrimitive(types.toArray(new Integer[0])), requestRowMapper);
|
| 184 |
|
|
}
|
| 185 |
35945
|
antonis.le
|
|
| 186 |
39076
|
antonis.le
|
public List<Request> getForOrganization(List<String> organizationIds, Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus[] statusFilter, int from, int to) {
|
| 187 |
|
|
List<Object> args = new ArrayList<Object>();
|
| 188 |
|
|
List<Integer> types = new ArrayList<Integer>();
|
| 189 |
36198
|
antonis.le
|
|
| 190 |
39076
|
antonis.le
|
String query = getQuery(null, organizationIds, null, requestSortBy, order, requestFilter, term, statusFilter, args, types, from, to);
|
| 191 |
36198
|
antonis.le
|
|
| 192 |
39076
|
antonis.le
|
return new JdbcTemplate(dataSource).query(query, args.toArray(), ArrayUtils.toPrimitive(types.toArray(new Integer[0])), requestRowMapper);
|
| 193 |
|
|
}
|
| 194 |
36198
|
antonis.le
|
|
| 195 |
39076
|
antonis.le
|
public List<Request> getForPublisher(String publisherId, Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus[] statusFilter, int from, int to) {
|
| 196 |
|
|
List<Object> args = new ArrayList<Object>();
|
| 197 |
|
|
List<Integer> types = new ArrayList<Integer>();
|
| 198 |
36429
|
antonis.le
|
|
| 199 |
39076
|
antonis.le
|
String query = getQuery(null, null, publisherId, requestSortBy, order, requestFilter, term, statusFilter, args, types, from, to);
|
| 200 |
36431
|
antonis.le
|
// System.out.println(query);
|
| 201 |
39076
|
antonis.le
|
return new JdbcTemplate(dataSource).query(query, args.toArray(), ArrayUtils.toPrimitive(types.toArray(new Integer[0])), requestRowMapper);
|
| 202 |
|
|
}
|
| 203 |
35945
|
antonis.le
|
|
| 204 |
39076
|
antonis.le
|
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 |
35945
|
antonis.le
|
|
| 209 |
39076
|
antonis.le
|
public List<Request> getRequests(Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus[] statusFilter, int from, int to) {
|
| 210 |
|
|
List<Object> args = new ArrayList<Object>();
|
| 211 |
|
|
List<Integer> types = new ArrayList<Integer>();
|
| 212 |
35945
|
antonis.le
|
|
| 213 |
39076
|
antonis.le
|
String query = getQuery(null, null, null, requestSortBy, order, requestFilter, term, statusFilter, args, types, from, to);
|
| 214 |
35945
|
antonis.le
|
|
| 215 |
39076
|
antonis.le
|
return new JdbcTemplate(dataSource).query(query, args.toArray(), ArrayUtils.toPrimitive(types.toArray(new Integer[0])), requestRowMapper);
|
| 216 |
|
|
}
|
| 217 |
35945
|
antonis.le
|
|
| 218 |
39076
|
antonis.le
|
public void approveRequest(String requestId, String personId, String comment) {
|
| 219 |
|
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
| 220 |
35945
|
antonis.le
|
|
| 221 |
39076
|
antonis.le
|
jdbcTemplate.update(APPROVE_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});
|
| 222 |
35945
|
antonis.le
|
|
| 223 |
39076
|
antonis.le
|
if (comment != null && !comment.isEmpty()) {
|
| 224 |
|
|
saveComment(requestId, personId, comment);
|
| 225 |
|
|
}
|
| 226 |
|
|
}
|
| 227 |
35945
|
antonis.le
|
|
| 228 |
39076
|
antonis.le
|
public void initiallyApproveRequest(String requestId, String personId, String comment) {
|
| 229 |
|
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
| 230 |
39075
|
antonis.le
|
|
| 231 |
39076
|
antonis.le
|
jdbcTemplate.update(INITIALLY_APPROVE_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});
|
| 232 |
39075
|
antonis.le
|
|
| 233 |
39076
|
antonis.le
|
if (comment != null && !comment.isEmpty()) {
|
| 234 |
|
|
saveComment(requestId, personId, comment);
|
| 235 |
|
|
}
|
| 236 |
|
|
}
|
| 237 |
39075
|
antonis.le
|
|
| 238 |
39076
|
antonis.le
|
private void saveComment(String requestId, String personId, String comment) {
|
| 239 |
|
|
String commentId = "portal::" + DigestUtils.md5Hex(comment + new Date());
|
| 240 |
39070
|
antonis.le
|
|
| 241 |
39076
|
antonis.le
|
new JdbcTemplate(dataSource).update("with comm as ( insert into comment (id, person, comment, date) values (?, ?, ?, ?) returning id) insert into request_comment (request, comment) select ?, comm.id from comm",
|
| 242 |
|
|
new Object[]{commentId, personId, comment, new Date(), requestId}, new int[]{Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR});
|
| 243 |
|
|
}
|
| 244 |
39070
|
antonis.le
|
|
| 245 |
39076
|
antonis.le
|
public void rejectRequest(String requestId, String personId, String comment) {
|
| 246 |
|
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
| 247 |
35945
|
antonis.le
|
|
| 248 |
39076
|
antonis.le
|
jdbcTemplate.update(REJECT_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});
|
| 249 |
35945
|
antonis.le
|
|
| 250 |
39076
|
antonis.le
|
if (comment != null && !comment.isEmpty()) {
|
| 251 |
|
|
saveComment(requestId, personId, comment);
|
| 252 |
|
|
}
|
| 253 |
|
|
}
|
| 254 |
35945
|
antonis.le
|
|
| 255 |
39076
|
antonis.le
|
public void paidRequest(String requestId, String personId, String comment, float apc_paid, float transfer_cost, float other_cost, Date datePaid) {
|
| 256 |
|
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
| 257 |
36847
|
antonis.le
|
|
| 258 |
39076
|
antonis.le
|
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});
|
| 259 |
36847
|
antonis.le
|
|
| 260 |
39076
|
antonis.le
|
if (comment != null && !comment.isEmpty()) {
|
| 261 |
|
|
saveComment(requestId, personId, comment);
|
| 262 |
|
|
}
|
| 263 |
|
|
}
|
| 264 |
36847
|
antonis.le
|
|
| 265 |
39076
|
antonis.le
|
public void onHoldRequest(String requestId, String personId, String comment) {
|
| 266 |
|
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
| 267 |
36847
|
antonis.le
|
|
| 268 |
39076
|
antonis.le
|
jdbcTemplate.update(ONHOLD_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});
|
| 269 |
36847
|
antonis.le
|
|
| 270 |
39076
|
antonis.le
|
if (comment != null && !comment.isEmpty()) {
|
| 271 |
|
|
saveComment(requestId, personId, comment);
|
| 272 |
|
|
}
|
| 273 |
|
|
}
|
| 274 |
36847
|
antonis.le
|
|
| 275 |
39076
|
antonis.le
|
public void deniedRequest(String requestId, String personId, String comment) {
|
| 276 |
|
|
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
| 277 |
36847
|
antonis.le
|
|
| 278 |
39076
|
antonis.le
|
jdbcTemplate.update(DENIED_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});
|
| 279 |
36847
|
antonis.le
|
|
| 280 |
39076
|
antonis.le
|
if (comment != null && !comment.isEmpty()) {
|
| 281 |
|
|
saveComment(requestId, personId, comment);
|
| 282 |
|
|
}
|
| 283 |
|
|
}
|
| 284 |
36847
|
antonis.le
|
|
| 285 |
39076
|
antonis.le
|
public void invoiceUploaded(String requestId, String invoiceId) {
|
| 286 |
|
|
new JdbcTemplate(dataSource).update(INVOICE_UPLOADED, new String[]{invoiceId, requestId}, new int[]{Types.VARCHAR, Types.VARCHAR});
|
| 287 |
|
|
}
|
| 288 |
35945
|
antonis.le
|
|
| 289 |
39076
|
antonis.le
|
public int getRequestId() {
|
| 290 |
|
|
return new JdbcTemplate(dataSource).queryForObject("select nextval('request_id_seq') as id", new RowMapper<Integer>() {
|
| 291 |
|
|
@Override
|
| 292 |
|
|
public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
|
| 293 |
|
|
return rs.getInt("id");
|
| 294 |
|
|
}
|
| 295 |
|
|
});
|
| 296 |
|
|
}
|
| 297 |
35945
|
antonis.le
|
|
| 298 |
39076
|
antonis.le
|
private String getQuery(String personId, List<String> organizationIds, String publisherId, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus[] statusFilters, List<Object> args, List<Integer> types, int from, int to) {
|
| 299 |
|
|
Map<String, String> joins = new HashMap<String, String>();
|
| 300 |
|
|
List<String> whereClauses = new ArrayList<String>();
|
| 301 |
|
|
List<Object> whereArgs = new ArrayList<Object>();
|
| 302 |
|
|
List<Integer> whereTypes = new ArrayList<Integer>();
|
| 303 |
35925
|
stefania.m
|
|
| 304 |
39076
|
antonis.le
|
String orderBy = null;
|
| 305 |
35945
|
antonis.le
|
|
| 306 |
39076
|
antonis.le
|
if (term != null) {
|
| 307 |
|
|
if (requestFilter != null) {
|
| 308 |
|
|
switch (requestFilter) {
|
| 309 |
|
|
case USER:
|
| 310 |
|
|
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(?)) ");
|
| 311 |
|
|
args.add("%" + term + "%");
|
| 312 |
|
|
args.add("%" + term + "%");
|
| 313 |
|
|
args.add("%" + term + "%");
|
| 314 |
|
|
types.add(Types.VARCHAR);
|
| 315 |
|
|
types.add(Types.VARCHAR);
|
| 316 |
|
|
types.add(Types.VARCHAR);
|
| 317 |
|
|
break;
|
| 318 |
|
|
case RESEARCHER:
|
| 319 |
|
|
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(?)) ");
|
| 320 |
|
|
args.add("%" + term + "%");
|
| 321 |
|
|
args.add("%" + term + "%");
|
| 322 |
|
|
args.add("%" + term + "%");
|
| 323 |
|
|
types.add(Types.VARCHAR);
|
| 324 |
|
|
types.add(Types.VARCHAR);
|
| 325 |
|
|
types.add(Types.VARCHAR);
|
| 326 |
|
|
break;
|
| 327 |
|
|
case PROJECT:
|
| 328 |
|
|
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(?)) ");
|
| 329 |
|
|
args.add("%" + term + "%");
|
| 330 |
|
|
args.add("%" + term + "%");
|
| 331 |
|
|
args.add("%" + term + "%");
|
| 332 |
|
|
types.add(Types.VARCHAR);
|
| 333 |
|
|
types.add(Types.VARCHAR);
|
| 334 |
|
|
types.add(Types.VARCHAR);
|
| 335 |
|
|
break;
|
| 336 |
|
|
case PUBLICATION:
|
| 337 |
|
|
joins.put("publication", " join publication pub on pub.id=r.publication and (lower(pub.title) like lower(?)) ");
|
| 338 |
|
|
args.add("%" + term + "%");
|
| 339 |
|
|
types.add(Types.VARCHAR);
|
| 340 |
|
|
break;
|
| 341 |
|
|
case JOURNAL:
|
| 342 |
|
|
joins.put("journal", " join journal j on j.id=r.journal and (lower(j.title) like lower(?)) ");
|
| 343 |
|
|
args.add("%" + term + "%");
|
| 344 |
|
|
types.add(Types.VARCHAR);
|
| 345 |
|
|
break;
|
| 346 |
|
|
case PUBLISHER:
|
| 347 |
|
|
joins.put("publisher", " join publisher publ on publ.id=r.publisher and (lower(publ.name) like lower(?)) ");
|
| 348 |
|
|
args.add("%" + term + "%");
|
| 349 |
|
|
types.add(Types.VARCHAR);
|
| 350 |
|
|
break;
|
| 351 |
|
|
}
|
| 352 |
|
|
} else {
|
| 353 |
|
|
joins.put("user", " left join person u on u.id=r.user ");
|
| 354 |
|
|
whereClauses.add("(lower(u.firstname) like lower(?) or lower(u.initials) like lower(?) or lower(u.lastname) like lower(?))");
|
| 355 |
|
|
whereArgs.add("%" + term + "%");
|
| 356 |
|
|
whereArgs.add("%" + term + "%");
|
| 357 |
|
|
whereArgs.add("%" + term + "%");
|
| 358 |
|
|
whereTypes.add(Types.VARCHAR);
|
| 359 |
|
|
whereTypes.add(Types.VARCHAR);
|
| 360 |
|
|
whereTypes.add(Types.VARCHAR);
|
| 361 |
|
|
joins.put("researcher", " left join person res on res.id=r.researcher ");
|
| 362 |
|
|
whereClauses.add("(lower(res.firstname) like lower(?) or lower(res.initials) like lower(?) or lower(res.lastname) like lower(?))");
|
| 363 |
|
|
whereArgs.add("%" + term + "%");
|
| 364 |
|
|
whereArgs.add("%" + term + "%");
|
| 365 |
|
|
whereArgs.add("%" + term + "%");
|
| 366 |
|
|
whereTypes.add(Types.VARCHAR);
|
| 367 |
|
|
whereTypes.add(Types.VARCHAR);
|
| 368 |
|
|
whereTypes.add(Types.VARCHAR);
|
| 369 |
|
|
joins.put("project", " left join project proj on proj.id=r.project ");
|
| 370 |
|
|
whereClauses.add("(lower(proj.acronym) like lower(?) or lower(proj.title) like lower(?) or lower(proj.grant) like lower(?))");
|
| 371 |
|
|
whereArgs.add("%" + term + "%");
|
| 372 |
|
|
whereArgs.add("%" + term + "%");
|
| 373 |
|
|
whereArgs.add("%" + term + "%");
|
| 374 |
|
|
whereTypes.add(Types.VARCHAR);
|
| 375 |
|
|
whereTypes.add(Types.VARCHAR);
|
| 376 |
|
|
whereTypes.add(Types.VARCHAR);
|
| 377 |
|
|
joins.put("publication", " left join publication pub on pub.id=r.publication ");
|
| 378 |
|
|
whereClauses.add("(lower(pub.title) like lower(?))");
|
| 379 |
|
|
whereArgs.add("%" + term + "%");
|
| 380 |
|
|
whereTypes.add(Types.VARCHAR);
|
| 381 |
|
|
joins.put("journal", " left join journal j on j.id=r.journal ");
|
| 382 |
|
|
whereClauses.add("(lower(j.title) like lower(?))");
|
| 383 |
|
|
whereArgs.add("%" + term + "%");
|
| 384 |
|
|
whereTypes.add(Types.VARCHAR);
|
| 385 |
|
|
joins.put("publisher", " left join publisher publ on publ.id=r.publisher ");
|
| 386 |
|
|
whereClauses.add("(lower(publ.name) like lower(?))");
|
| 387 |
|
|
whereArgs.add("%" + term + "%");
|
| 388 |
|
|
whereTypes.add(Types.VARCHAR);
|
| 389 |
|
|
}
|
| 390 |
35919
|
antonis.le
|
|
| 391 |
39076
|
antonis.le
|
}
|
| 392 |
35919
|
antonis.le
|
|
| 393 |
39076
|
antonis.le
|
if (requestSortBy != null) {
|
| 394 |
|
|
switch (requestSortBy) {
|
| 395 |
|
|
case DATE:
|
| 396 |
|
|
orderBy = " r.date ";
|
| 397 |
|
|
break;
|
| 398 |
|
|
case FUNDING_REQUESTED:
|
| 399 |
|
|
orderBy = " r.fundingrequested ";
|
| 400 |
|
|
break;
|
| 401 |
|
|
case USER:
|
| 402 |
|
|
if (joins.get("user") == null)
|
| 403 |
|
|
joins.put("user", " left join person u on u.id=r.user ");
|
| 404 |
35919
|
antonis.le
|
|
| 405 |
39076
|
antonis.le
|
orderBy = " u.firstname, u.initials, u.lastname ";
|
| 406 |
|
|
break;
|
| 407 |
|
|
case PUBLICATION:
|
| 408 |
|
|
if (joins.get("publication") == null)
|
| 409 |
|
|
joins.put("publication", " left join publication pub on r.publication=pub.id ");
|
| 410 |
35919
|
antonis.le
|
|
| 411 |
39076
|
antonis.le
|
orderBy = " pub.title ";
|
| 412 |
|
|
break;
|
| 413 |
|
|
case RESEARCHER:
|
| 414 |
|
|
if (joins.get("researcher") == null)
|
| 415 |
|
|
joins.put("researcher", " left join person res on res.id=r.researcher ");
|
| 416 |
35992
|
antonis.le
|
|
| 417 |
39076
|
antonis.le
|
orderBy = " res.firstname, res.initials, res.lastname ";
|
| 418 |
|
|
break;
|
| 419 |
|
|
case PROJECT:
|
| 420 |
|
|
if (joins.get("project") == null)
|
| 421 |
|
|
joins.put("project", " left join project proj on proj.id=r.project ");
|
| 422 |
35992
|
antonis.le
|
|
| 423 |
39076
|
antonis.le
|
orderBy = " proj.acronym ";
|
| 424 |
|
|
break;
|
| 425 |
|
|
case JOURNAL:
|
| 426 |
|
|
if (joins.get("journal") == null)
|
| 427 |
|
|
joins.put("journal", " left join journal j on j.id=r.journal ");
|
| 428 |
35992
|
antonis.le
|
|
| 429 |
39076
|
antonis.le
|
orderBy = " j.title ";
|
| 430 |
|
|
break;
|
| 431 |
|
|
case PUBLISHER:
|
| 432 |
|
|
if (joins.get("publisher") == null)
|
| 433 |
|
|
joins.put("publisher", " left join publisher publ on publ.id=r.publisher ");
|
| 434 |
35992
|
antonis.le
|
|
| 435 |
39076
|
antonis.le
|
orderBy = " publ.name ";
|
| 436 |
|
|
break;
|
| 437 |
|
|
case STATUS:
|
| 438 |
|
|
default:
|
| 439 |
|
|
orderBy = " r.status ";
|
| 440 |
|
|
break;
|
| 441 |
|
|
}
|
| 442 |
|
|
}
|
| 443 |
35919
|
antonis.le
|
|
| 444 |
39076
|
antonis.le
|
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.status from request r ");
|
| 445 |
35919
|
antonis.le
|
|
| 446 |
39076
|
antonis.le
|
for (String join : joins.values())
|
| 447 |
|
|
sb.append(" ").append(join).append(" ");
|
| 448 |
35919
|
antonis.le
|
|
| 449 |
|
|
|
| 450 |
39076
|
antonis.le
|
sb.append(" where ");
|
| 451 |
35919
|
antonis.le
|
|
| 452 |
39076
|
antonis.le
|
if (personId != null) {
|
| 453 |
|
|
sb.append("r.\"user\"=? ");
|
| 454 |
35945
|
antonis.le
|
|
| 455 |
39076
|
antonis.le
|
args.add(personId);
|
| 456 |
|
|
types.add(Types.VARCHAR);
|
| 457 |
|
|
} else if (organizationIds != null) {
|
| 458 |
|
|
sb.append("(");
|
| 459 |
36198
|
antonis.le
|
|
| 460 |
39076
|
antonis.le
|
for (int i = 0; i < organizationIds.size(); i++) {
|
| 461 |
|
|
if (i > 0)
|
| 462 |
|
|
sb.append(" or ");
|
| 463 |
36229
|
antonis.le
|
|
| 464 |
39076
|
antonis.le
|
sb.append(" r.organization=? ");
|
| 465 |
36229
|
antonis.le
|
|
| 466 |
39076
|
antonis.le
|
args.add(organizationIds.get(i));
|
| 467 |
|
|
types.add(Types.VARCHAR);
|
| 468 |
|
|
}
|
| 469 |
36229
|
antonis.le
|
|
| 470 |
39076
|
antonis.le
|
sb.append(")");
|
| 471 |
36229
|
antonis.le
|
|
| 472 |
39076
|
antonis.le
|
} else if (publisherId != null) {
|
| 473 |
36431
|
antonis.le
|
|
| 474 |
39076
|
antonis.le
|
if (joins.get("journal") == null) {
|
| 475 |
|
|
sb.delete(sb.lastIndexOf("where"), sb.length());
|
| 476 |
|
|
sb.append(" left join journal j on j.id=r.journal ");
|
| 477 |
|
|
sb.append(" where ");
|
| 478 |
|
|
}
|
| 479 |
36431
|
antonis.le
|
|
| 480 |
39076
|
antonis.le
|
sb.append("(r.publisher=? or j.publisher=?)");
|
| 481 |
36429
|
antonis.le
|
|
| 482 |
39076
|
antonis.le
|
args.add(publisherId);
|
| 483 |
|
|
types.add(Types.VARCHAR);
|
| 484 |
|
|
args.add(publisherId);
|
| 485 |
|
|
types.add(Types.VARCHAR);
|
| 486 |
|
|
} else
|
| 487 |
|
|
sb.append(" 1 = 1 ");
|
| 488 |
35945
|
antonis.le
|
|
| 489 |
39076
|
antonis.le
|
if (statusFilters != null) {
|
| 490 |
|
|
sb.append(" and ");
|
| 491 |
35992
|
antonis.le
|
|
| 492 |
39076
|
antonis.le
|
if (statusFilters.length == 1) {
|
| 493 |
|
|
switch (statusFilters[0]) {
|
| 494 |
|
|
case INCOMPLETE:
|
| 495 |
|
|
sb.append(" r.status = 0 ");
|
| 496 |
|
|
break;
|
| 497 |
|
|
case SUBMITTED:
|
| 498 |
|
|
sb.append(" r.status=" + Request.RequestStatus.SUBMITTED.getCode());
|
| 499 |
|
|
break;
|
| 500 |
|
|
default:
|
| 501 |
|
|
sb.append(" r.status&" + statusFilters[0].getCode() + " = " + statusFilters[0].getCode());
|
| 502 |
|
|
break;
|
| 503 |
|
|
}
|
| 504 |
|
|
} else {
|
| 505 |
|
|
int status = 0;
|
| 506 |
36846
|
antonis.le
|
|
| 507 |
39076
|
antonis.le
|
for (int i = 0; i < statusFilters.length; i++) {
|
| 508 |
|
|
status |= statusFilters[i].getCode();
|
| 509 |
|
|
}
|
| 510 |
36846
|
antonis.le
|
|
| 511 |
39076
|
antonis.le
|
sb.append(" r.status&" + status + " = " + status);
|
| 512 |
|
|
}
|
| 513 |
|
|
}
|
| 514 |
35992
|
antonis.le
|
|
| 515 |
39076
|
antonis.le
|
if (whereClauses.size() > 0) {
|
| 516 |
|
|
sb.append(" and (");
|
| 517 |
35927
|
antonis.le
|
|
| 518 |
39076
|
antonis.le
|
for (String clause : whereClauses) {
|
| 519 |
|
|
sb.append(clause);
|
| 520 |
|
|
sb.append(" or ");
|
| 521 |
|
|
}
|
| 522 |
35927
|
antonis.le
|
|
| 523 |
39076
|
antonis.le
|
sb.delete(sb.lastIndexOf("or"), sb.length());
|
| 524 |
|
|
sb.append(") ");
|
| 525 |
35927
|
antonis.le
|
|
| 526 |
39076
|
antonis.le
|
args.addAll(whereArgs);
|
| 527 |
|
|
types.addAll(whereTypes);
|
| 528 |
|
|
}
|
| 529 |
35927
|
antonis.le
|
|
| 530 |
39076
|
antonis.le
|
if (orderBy != null) {
|
| 531 |
|
|
sb.append(" order by ").append(orderBy);
|
| 532 |
35919
|
antonis.le
|
|
| 533 |
39076
|
antonis.le
|
if (order == RequestSortOrder.ASCENDING)
|
| 534 |
|
|
sb.append(" asc");
|
| 535 |
|
|
else
|
| 536 |
|
|
sb.append(" desc");
|
| 537 |
|
|
}
|
| 538 |
35919
|
antonis.le
|
|
| 539 |
39076
|
antonis.le
|
if (from > 0 && to > 0) {
|
| 540 |
|
|
sb.append(" limit ").append(to - from + 1);
|
| 541 |
|
|
sb.append(" offset ").append(from - 1);
|
| 542 |
|
|
}
|
| 543 |
36591
|
antonis.le
|
|
| 544 |
39076
|
antonis.le
|
return sb.toString();
|
| 545 |
|
|
}
|
| 546 |
36112
|
antonis.le
|
|
| 547 |
39076
|
antonis.le
|
public List<Comment> getComments(String id) {
|
| 548 |
|
|
try {
|
| 549 |
|
|
return new JdbcTemplate(dataSource).query("select c.comment, c.date, c.person from comment c join request_comment rc on rc.comment=c.id and rc.request=? order by date",
|
| 550 |
|
|
new String[]{id}, new int[]{Types.VARCHAR}, new RowMapper<Comment>() {
|
| 551 |
|
|
@Override
|
| 552 |
|
|
public Comment mapRow(ResultSet rs, int rowNum) throws SQLException {
|
| 553 |
|
|
return new Comment(new Person(rs.getString("person")), rs.getTimestamp("date"), rs.getString("comment"));
|
| 554 |
|
|
}
|
| 555 |
|
|
});
|
| 556 |
|
|
} catch (EmptyResultDataAccessException e) {
|
| 557 |
|
|
return null;
|
| 558 |
|
|
}
|
| 559 |
|
|
}
|
| 560 |
38128
|
antonis.le
|
|
| 561 |
|
|
public void uploadBankTransferReceipt(final String requestid, final String contentType, InputStream inputStream) {
|
| 562 |
|
|
try {
|
| 563 |
|
|
final ByteArrayOutputStream baos = new ByteArrayOutputStream();
|
| 564 |
|
|
final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
|
| 565 |
|
|
|
| 566 |
|
|
IOUtils.copy(inputStream, baos);
|
| 567 |
|
|
IOUtils.closeQuietly(baos);
|
| 568 |
|
|
|
| 569 |
|
|
final PreparedStatementSetter pss = new PreparedStatementSetter() {
|
| 570 |
|
|
@Override
|
| 571 |
|
|
public void setValues(PreparedStatement ps) throws SQLException {
|
| 572 |
|
|
ps.setString(1, contentType);
|
| 573 |
|
|
ps.setBytes(2, baos.toByteArray());
|
| 574 |
|
|
ps.setString(3, requestid);
|
| 575 |
|
|
}
|
| 576 |
|
|
};
|
| 577 |
|
|
|
| 578 |
|
|
jdbcTemplate.update("update request set bank_receipt_contenttype=?, bank_receipt=? where id=?", pss);
|
| 579 |
|
|
|
| 580 |
|
|
} catch (Exception e) {
|
| 581 |
|
|
e.printStackTrace();
|
| 582 |
|
|
}
|
| 583 |
|
|
}
|
| 584 |
|
|
|
| 585 |
|
|
public BankTransferReceipt downloadBankTransferReceipt(String requestId) {
|
| 586 |
|
|
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>() {
|
| 587 |
|
|
@Override
|
| 588 |
|
|
public BankTransferReceipt mapRow(ResultSet rs, int rowNum) throws SQLException {
|
| 589 |
|
|
return new BankTransferReceipt(rs.getString("bank_receipt_contenttype"), rs.getBytes("bank_receipt"));
|
| 590 |
|
|
}
|
| 591 |
|
|
});
|
| 592 |
|
|
}
|
| 593 |
35828
|
antonis.le
|
}
|