D-Net

import eu.dnetlib.goldoa.service.dao.RequestDAO;

    private RequestDAO requestDAO;

			request.setId(new SimpleDateFormat("yyyyMMdd-").format(new Date()) + requestDAO.getRequestId());

            request.addStatus(Request.RequestStatus.INVOICE_UPLOADED);

        requestDAO.saveRequest(request);

            return this.getRequestInfo(requestDAO.getRequest(requestId));

        List<Request> requests = requestDAO.getForUser(personId, from, to, requestSortBy, order, requestFilter, "", 0, 0);

		return requestDAO.getForProject(projectId);

        List<Request> requests = requestDAO.getRequests(from, to, requestSortBy, order, requestFilter, term, 0, 0);

    @Override

    public Request submitRequest(Request request) throws PersonManagerException {

        request.addStatus(Request.RequestStatus.SUBMITTED);

        saveRequest(request);

        requestDAO.approveRequest(requestId, comment);

        requestDAO.rejectRequest(requestId, comment);

        requestDAO.invoiceUploaded(requestId);

    private RequestInfo getRequestInfo(Request request) throws PersonManagerException {

        RequestInfo req = new RequestInfo();

        // TODO add missing fields

        req.setId(request.getId());

        req.setUser(personManager.getById(request.getUser()));

        req.setDate(request.getDate());

        req.setResearcher(personManager.getById(request.getResearcher()));

        req.setProject(projectManager.getById(request.getProject()));

        req.setPublication(publicationManager.getPublication(request.getPublication()));

        req.setProjectParticipation(request.getProjectParticipation());

        req.setFundingRequested(request.getFundingRequested());

        req.setEligibility(eligibilityManager.validate(request));

        req.setStatus(getStatus(request));

        return req;

    private RequestInfo getShortRequestInfo(Request request) throws PersonManagerException {

        RequestInfo req = new RequestInfo();

        // TODO add missing fields

        req.setId(request.getId());

        req.setDate(request.getDate());

        req.setPublication(publicationManager.getPublication(request.getPublication()));

        req.setProjectParticipation(request.getProjectParticipation());

        req.setFundingRequested(request.getFundingRequested());

        req.setStatus(getStatus(request));

        return req;

package eu.dnetlib.goldoa.service.dao;

import eu.dnetlib.goldoa.domain.*;

import org.apache.commons.codec.digest.DigestUtils;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

import java.util.Date;

import java.util.List;

/**

 * Created by antleb on 3/30/15.

 */

public class RequestDAO {

    @Autowired

    private DataSource dataSource;

    private final String GET_FOR_PROJECT = "select id, \"user\", date, researcher, project, publication, journal, publisher, budget, invoice, projectparticipation, fundingrequested, status from request where project=?";

    private final String GET_FOR_USER = "select id, \"user\", date, researcher, project, publication, journal, publisher, budget, invoice, projectparticipation, fundingrequested, status from request where \"user\"=?";

    private final String GET_BY_ID = "select id, \"user\", date, researcher, project, publication, journal, publisher, budget, invoice, projectparticipation, fundingrequested, status from request where id=?";

    private final String UPDATE_REQUEST = "update request set \"user\"=?, date=?, researcher=?, project=?, publication=?, journal=?, publisher=?, budget=?, invoice = ?, projectparticipation=?, fundingrequested=?, status=? where id = ?";

    private final String INSERT_REQUEST = "insert into request (\"user\", date, researcher, project, publication, journal, publisher, budget, invoice, projectparticipation, fundingrequested, status, id) values (?, ?, ?, ?, ? ,? ,? ,? ,?, ?, ?, ?, ?)";

    private final String APPROVE_REQUEST = "update request set status = (status & ~" + Request.RequestStatus.REJECTED.getCode() + ") | " + Request.RequestStatus.APPROVED.getCode() + " where id=?";

    private final String REJECT_REQUEST = "update request set status = (status & ~" + Request.RequestStatus.APPROVED.getCode() + ") | " + Request.RequestStatus.REJECTED.getCode() + " where id=?";

    private final String INVOICE_UPLOADED = "update request set status = status | " + Request.RequestStatus.INVOICE_UPLOADED.getCode() + " where id=?";

    public void saveRequest(final Request request) {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        Object[] args = new Object[]{request.getUser(), request.getDate(), request.getResearcher(), request.getProject(),

                request.getPublication(), request.getJournal(), request.getPublisher(), request.getBudget(), request.getInvoice(),

                request.getProjectParticipation(), request.getFundingRequested(), request.getStatus(), request.getId()};

        int[] types = new int[]{Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.REAL, Types.REAL, Types.INTEGER, Types.VARCHAR};

        if (jdbcTemplate.update(UPDATE_REQUEST, args, types) == 0) {

            jdbcTemplate.update(INSERT_REQUEST, args, types);

        }

        ;

    }

    public Request getRequest(String requestId) {

        return new JdbcTemplate(dataSource).queryForObject(GET_BY_ID, new String[]{requestId}, new int[]{

                Types.VARCHAR}, requestRowMapper);

    }

    private RowMapper<Request> requestRowMapper = new RowMapper<Request>() {

        @Override

        public Request mapRow(ResultSet rs, int rowNum) throws SQLException {

            return new Request(

                    rs.getString("id"), rs.getString("user"), rs.getTimestamp("date"), rs.getString("researcher"),

                    rs.getString("project"), rs.getString("publication"), rs.getString("journal"), rs.getString("publisher"),

                    rs.getString("budget"), rs.getString("invoice"), rs.getFloat("projectparticipation"), rs.getFloat("fundingrequested"), rs.getInt("status"));

        }

    };

    /**

     * @param personId

     * @param from          inclusive, starts with 0

     * @param to            non inclusive, < total count

     * @param requestSortBy

     * @param order

     * @param requestFilter

     * @param term

     * @param from

     * @param to

     * @return

     */

    public List<Request> getForUser(String personId, Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, int from, int to) {

        return new JdbcTemplate(dataSource).query(GET_FOR_USER, new String[]{personId}, new int[]{

                Types.VARCHAR}, requestRowMapper);

    }

    public List<Request> getForProject(String projectId) {

        return new JdbcTemplate(dataSource).query(GET_FOR_PROJECT, new String[]{projectId}, new int[]{

                Types.VARCHAR}, requestRowMapper);

    }

    public List<Request> getRequests(Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, int from, int to) {

        String query = "select r.id, r.\"user\", r.date, r.researcher, r.project, r.publication, r.journal, r.publisher, r.budget, r.invoice, r.projectparticipation, r.fundingrequested, r.status from request r";

//        if (requestFilter != null) {

//            switch (requestFilter) {

//                case USER:

//                    query += " join person p on p.email = r.user and (lower(p.firstname) like (lower(?) or lower(p.lastname) like (?) ";

//                    break;

//                case RESEARCHER:

//                    query += " join person p on p.email = r.researcher and (lower(p.firstname) like (lower(?) or lower(p.lastname) like (?) ";

//                    break;

//            }

//        }

        return new JdbcTemplate(dataSource).query(query, new String[]{}, new int[]{}, requestRowMapper);

    }

    public void approveRequest(String requestId, String comment) {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        jdbcTemplate.update(APPROVE_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});

        if (comment != null) {

            if (jdbcTemplate.update("update comment set comment=?, date=? where id = (select comment from request_comment where request=? )", new Object[] {comment, new Date(), requestId}, new int[] {Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR}) == 0) {

                String commentId = "portal::" + DigestUtils.md5Hex(comment + new Date());

                jdbcTemplate.update("with comm as ( insert into comment (id, comment, date) values (?, ?, ?) returning id) insert into request_comment (request, comment) select ?, comm.id",

                        new Object[] {commentId, comment, new Date(), requestId}, new int[] {Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR});

            }

        }

    }

    public void rejectRequest(String requestId, String comment) {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        jdbcTemplate.update(REJECT_REQUEST, new String[]{requestId}, new int[]{Types.VARCHAR});

        if (comment != null) {

            if (jdbcTemplate.update("update comment set comment=?, date=? where id = (select comment from request_comment where request=? )", new Object[] {comment, new Date(), requestId}, new int[] {Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR}) == 0) {

                String commentId = "portal::" + DigestUtils.md5Hex(comment + new Date());

                jdbcTemplate.update("with comm as ( insert into comment (id, comment, date) values (?, ?, ?) returning id) insert into request_comment (request, comment) select ?, comm.id",

                        new Object[] {commentId, comment, new Date(), requestId}, new int[] {Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR});

            }

        }

    }

    public void invoiceUploaded(String requestId) {

        new JdbcTemplate(dataSource).update(INVOICE_UPLOADED, new String[] {requestId}, new int[] {Types.VARCHAR});

    }

    public int getRequestId() {

        return new JdbcTemplate(dataSource).queryForObject("select nextval('request_id_seq') as id", new RowMapper<Integer>() {

            @Override

            public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {

                return rs.getInt("id");

            }

        });

    }

}

package eu.dnetlib.goldoa.domain;

import java.util.List;

/**

 * Created by antleb on 3/30/15.

 */

public class RequestSearchResults {

    private List<RequestInfo> requests;

    private int total;

    private int start;

    public RequestSearchResults(List<RequestInfo> requests, int total, int start) {

        this.requests = requests;

        this.total = total;

        this.start = start;

    }

    public List<RequestInfo> getRequests() {

        return requests;

    }

    public void setRequests(List<RequestInfo> requests) {

        this.requests = requests;

    }

    public int getTotal() {

        return total;

    }

    public void setTotal(int total) {

        this.total = total;

    }

    public int getStart() {

        return start;

    }

    public void setStart(int start) {

        this.start = start;

    }

}