D-Net

package eu.dnetlib.goldoa.service.dao;

import eu.dnetlib.goldoa.domain.*;

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

import org.apache.commons.lang3.ArrayUtils;

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.*;

/**

 * 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_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"));

        }

    };

    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) {

        List<Object> args = new ArrayList<Object>();

        List<Integer> types = new ArrayList<Integer>();

        String query = getQuery(personId, requestSortBy, order, requestFilter, term, statusFilter, args, types);

//        return null;

        return new JdbcTemplate(dataSource).query(query, args.toArray(), ArrayUtils.toPrimitive(types.toArray(new Integer[0])), requestRowMapper);

    }

    public static void main(String[] args) {

        System.out.println(new RequestDAO().getForUser("dfsdf", new Date(), new Date(), RequestSort.PUBLICATION, RequestSortOrder.ASCENDING, null, "breast", Request.RequestStatus.APPROVED, 0, 12));

    }

    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, Request.RequestStatus statusFilter, int from, int to) {

        List<Object> args = new ArrayList<Object>();

        List<Integer> types = new ArrayList<Integer>();

        String query = getQuery(null, requestSortBy, order, requestFilter, term, statusFilter, args, types);

        return new JdbcTemplate(dataSource).query(query, args.toArray(), ArrayUtils.toPrimitive(types.toArray(new Integer[0])), 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");

            }

        });

    }

    private String getQuery(String personId, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus statusFilter, List<Object> args, List<Integer> types) {

        Map<String, String> joins = new HashMap<String, String>();

        List<String> whereClauses = new ArrayList<String>();

        List<Object> whereArgs = new ArrayList<Object>();

        List<Integer> whereTypes = new ArrayList<Integer>();

        String orderBy = null;

        if (term != null) {

            if (requestFilter != null) {

                switch (requestFilter) {

                    case USER:

                        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(?)) ");

                        args.add("%" + term + "%");

                        args.add("%" + term + "%");

                        args.add("%" + term + "%");

                        types.add(Types.VARCHAR);

                        types.add(Types.VARCHAR);

                        types.add(Types.VARCHAR);

                        break;

                    case RESEARCHER:

                        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(?)) ");

                        args.add("%" + term + "%");

                        args.add("%" + term + "%");

                        args.add("%" + term + "%");

                        types.add(Types.VARCHAR);

                        types.add(Types.VARCHAR);

                        types.add(Types.VARCHAR);

                        break;

                    case PROJECT:

                        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(?)) ");

                        args.add("%" + term + "%");

                        args.add("%" + term + "%");

                        args.add("%" + term + "%");

                        types.add(Types.VARCHAR);

                        types.add(Types.VARCHAR);

                        types.add(Types.VARCHAR);

                        break;

                    case PUBLICATION:

                        joins.put("publication", " join publication pub on pub.id=r.publication and (lower(pub.title) like lower(?)) ");

                        args.add("%" + term + "%");

                        types.add(Types.VARCHAR);

                        break;

                    case JOURNAL:

                        joins.put("journal", " join journal j on j.id=r.journal and (lower(j.title) like lower(?)) ");

                        args.add("%" + term + "%");

                        types.add(Types.VARCHAR);

                        break;

                    case PUBLISHER:

                        joins.put("publisher", " join publisher publ on publ.id=r.publisher and (lower(publ.name) like lower(?)) ");

                        args.add("%" + term + "%");

                        types.add(Types.VARCHAR);

                        break;

                }

            } else {

                joins.put("user", " left join person u on u.id=r.user ");

                whereClauses.add("(lower(u.firstname) like lower(?) or lower(u.initials) like lower(?) or lower(u.lastname) like lower(?))");

                whereArgs.add("%" + term + "%");

                whereArgs.add("%" + term + "%");

                whereArgs.add("%" + term + "%");

                whereTypes.add(Types.VARCHAR);

                whereTypes.add(Types.VARCHAR);

                whereTypes.add(Types.VARCHAR);

                joins.put("researcher", " left join person res on res.id=r.researcher ");

                whereClauses.add("(lower(res.firstname) like lower(?) or lower(res.initials) like lower(?) or lower(res.lastname) like lower(?))");

                whereArgs.add("%" + term + "%");

                whereArgs.add("%" + term + "%");

                whereArgs.add("%" + term + "%");

                whereTypes.add(Types.VARCHAR);

                whereTypes.add(Types.VARCHAR);

                whereTypes.add(Types.VARCHAR);

                joins.put("project", " left join project proj on proj.id=r.project ");

                whereClauses.add("(lower(proj.acronym) like lower(?) or lower(proj.title) like lower(?) or lower(proj.grant) like lower(?))");

                whereArgs.add("%" + term + "%");

                whereArgs.add("%" + term + "%");

                whereArgs.add("%" + term + "%");

                whereTypes.add(Types.VARCHAR);

                whereTypes.add(Types.VARCHAR);

                whereTypes.add(Types.VARCHAR);

                joins.put("publication", " left join publication pub on pub.id=r.publication ");

                whereClauses.add("(lower(pub.title) like lower(?))");

                whereArgs.add("%" + term + "%");

                whereTypes.add(Types.VARCHAR);

                joins.put("journal", " left join journal j on j.id=r.journal ");

                whereClauses.add("(lower(j.title) like lower(?))");

                whereArgs.add("%" + term + "%");

                whereTypes.add(Types.VARCHAR);

                joins.put("publisher", " left join publisher publ on publ.id=r.publisher ");

                whereClauses.add("(lower(publ.name) like lower(?))");

                whereArgs.add("%" + term + "%");

                whereTypes.add(Types.VARCHAR);

            }

        }

        if (requestSortBy != null) {

            switch (requestSortBy) {

                case DATE:

                    orderBy = "r.date";

                    break;

                case USER:

                    if (joins.get("user") == null)

                        joins.put("user", " left join person u on u.id=r.user ");

                    orderBy = " u.firstname, u.initials, u.lastname ";

                    break;

                case PUBLICATION:

                    if (joins.get("publication") == null)

                        joins.put("publication", " left join publication pub on r.publication=pub.id ");

                    orderBy = " pub.title ";

                    break;

                case STATUS:

                default:

                    orderBy = " r.status ";

                    break;

            }

        }

        StringBuilder sb = new StringBuilder("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 ");

        for (String join:joins.values())

            sb.append(" ").append(join).append(" ");

        sb.append(" where ");

        if (personId != null) {

            sb.append("r.\"user\"=? ");

            args.add(personId);

            types.add(Types.VARCHAR);

        }

        else

            sb.append(" r.status > 0 ");

        if (whereClauses.size() > 0) {

            sb.append(" and (");

            for (String clause:whereClauses) {

                sb.append(clause);

                sb.append(" or ");

            }

            sb.delete(sb.lastIndexOf("or"), sb.length());

            sb.append(") ");

            args.addAll(whereArgs);

            types.addAll(whereTypes);

        }

        if (orderBy != null) {

            sb.append("order by ").append(orderBy);

            if (order == RequestSortOrder.ASCENDING)

                sb.append(" asc");

            else

                sb.append(" desc");

        }

        return sb.toString();

    }

}