D-Net

package eu.dnetlib.goldoa.service;

import eu.dnetlib.goldoa.domain.*;

import eu.dnetlib.goldoa.service.utils.EmailUtils;

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

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.transaction.annotation.Transactional;

import javax.mail.MessagingException;

import javax.sql.DataSource;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

/**

 * Created by antleb on 3/10/15.

 */

@Transactional

public class RequestManagerImpl implements RequestManager {

    @Autowired

	private DataSource dataSource;

    @Autowired

    private PersonManager personManager;

    @Autowired

    private OrganizationManager organizationManager;

    @Autowired

    private ProjectManager projectManager;

    @Autowired

    private PublicationManager publicationManager;

    @Autowired

    private InvoiceManager invoiceManager;

    @Autowired

    private EligibilityManager eligibilityManager;

    @Autowired

    private EmailUtils emailUtils;

	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.REJECTED + ") | " + Request.APPROVED + " where id=?";

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

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

	@Override

	public Request saveRequest(final Request request) {

		String sql = UPDATE_REQUEST;

		if (request.getId() == null) {

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

			sql = INSERT_REQUEST;

		}

        if (request.getInvoice() != null)

            request.addStatus(Request.INVOICE_UPLOADED);

        new JdbcTemplate(dataSource).update(sql, 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()},

                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});

		return request;

	}

	@Override

	public RequestInfo getById(String requestId) {

        try {

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

                    Types.VARCHAR}, requestRowMapper));

        } catch (PersonManagerException e) {

            e.printStackTrace();

        }

        return null;

	}

    @Override

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

        List<Request> requests = new JdbcTemplate(dataSource).query(GET_FOR_USER, new String[]{personId}, new int[]{

                Types.VARCHAR}, requestRowMapper);

        List<RequestInfo> res = new ArrayList<RequestInfo>();

        for (Request request:requests) {

            try {

                RequestInfo req = getRequestInfo(request);

                res.add(req);

            } catch (Exception e) {

                e.printStackTrace();

            }

        }

        return res;

    }

	@Override

	public List<Request> getForProject(String projectId) {

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

				Types.VARCHAR}, requestRowMapper);

	}

    @Override

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

        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;

//            }

//        }

        List<Request> requests = new JdbcTemplate(dataSource).query(query, new String[] {}, new int[]{}, requestRowMapper);

        List<RequestInfo> res = new ArrayList<RequestInfo>();

        for (Request request:requests) {

            try {

                RequestInfo req = getRequestInfo(request);

                res.add(req);

            } catch (Exception e) {

                e.printStackTrace();

            }

        }

        return res;

    }

    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.setStatusCode(request.getStatus());

        req.setStatus(getStatus(request));

        return req;

    }

    @Override

    public Request submitRequest(Request request) throws PersonManagerException {

        // TODO offline

        RequestInfo requestInfo = this.getRequestInfo(request);

        request.addStatus(Request.SUBMITTED);

        saveRequest(request);

        try {

            switch (requestInfo.getEligibility().getStatus()) {

                case OK:

                    emailUtils.sendRequesterOKEmail(requestInfo);

                    for (Person person : personManager.getModerators())

                        emailUtils.sendModeratorOKEmail(requestInfo, person);

                    break;

                case IFFY:

                    emailUtils.sendRequesterIFFYEmail(requestInfo);

                    for (Person person : personManager.getModerators())

                        emailUtils.sendModeratorIFFYEmail(requestInfo, person);

                    break;

                case NONO:

                    break;

            }

        } catch (MessagingException e) {

            e.printStackTrace();

        }

        return request;

    }

    @Override

    public void approveRequest(String requestId) {

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

        RequestInfo requestInfo = getById(requestId);

        try {

            emailUtils.sendRequesterApprovedEmail(requestInfo);

        } catch (MessagingException e) {

            e.printStackTrace();

        }

    }

    @Override

    public void rejectRequest(String requestId) {

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

        RequestInfo requestInfo = getById(requestId);

        try {

            emailUtils.sendRequesterRejectedEmail(requestInfo);

        } catch (MessagingException e) {

            e.printStackTrace();

        }

    }

    @Override

    public void invoiceUploaded(String requestId) {

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

    }

    private String getStatus(Request request) {

        if (request.getStatus(Request.SUBMITTED)) {

            if (request.getStatus(Request.APPROVED))

                return  "Approved";

            else if (request.getStatus(Request.REJECTED))

                return "Rejected";

            else

                return "Submitted";

        }

        else

            return "Incomplete";

    }

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

        }

    };

    private 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");

            }

        });

    }

    public DataSource getDataSource() {

        return dataSource;

    }

    public void setDataSource(DataSource dataSource) {

        this.dataSource = dataSource;

    }

    public PersonManager getPersonManager() {

        return personManager;

    }

    public void setPersonManager(PersonManager personManager) {

        this.personManager = personManager;

    }

    public OrganizationManager getOrganizationManager() {

        return organizationManager;

    }

    public void setOrganizationManager(OrganizationManager organizationManager) {

        this.organizationManager = organizationManager;

    }

    public ProjectManager getProjectManager() {

        return projectManager;

    }

    public void setProjectManager(ProjectManager projectManager) {

        this.projectManager = projectManager;

    }

    public PublicationManager getPublicationManager() {

        return publicationManager;

    }

    public void setPublicationManager(PublicationManager publicationManager) {

        this.publicationManager = publicationManager;

    }

    public InvoiceManager getInvoiceManager() {

        return invoiceManager;

    }

    public void setInvoiceManager(InvoiceManager invoiceManager) {

        this.invoiceManager = invoiceManager;

    }

    public EligibilityManager getEligibilityManager() {

        return eligibilityManager;

    }

    public void setEligibilityManager(EligibilityManager eligibilityManager) {

        this.eligibilityManager = eligibilityManager;

    }

    public EmailUtils getEmailUtils() {

        return emailUtils;

    }

    public void setEmailUtils(EmailUtils emailUtils) {

        this.emailUtils = emailUtils;

    }

}