D-Net

package eu.dnetlib.goldoa.service.dao;

import com.opencsv.CSVWriter;

import com.sun.org.apache.regexp.internal.RE;

import eu.dnetlib.goldoa.domain.*;

import eu.dnetlib.goldoa.domain.RequestPage;

import eu.dnetlib.goldoa.service.EligibilityManager;

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

import org.apache.commons.io.IOUtils;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import org.hibernate.Criteria;

import org.hibernate.Query;

import org.hibernate.criterion.*;

import org.hibernate.transform.AliasToBeanResultTransformer;

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

import org.springframework.stereotype.Repository;

import org.hibernate.type.Type;

import java.io.ByteArrayOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.StringWriter;

import java.math.BigInteger;

import java.sql.*;

import java.text.SimpleDateFormat;

import java.util.*;

import java.util.Date;

import org.hibernate.type.*;

/*

 * Created by antleb on 3/30/15.

*/

@Repository

public class RequestDAO extends AbstractDao<String,Request>{

	private Log logger = LogFactory.getLog(RequestDAO.class);

	private final static String GET_COMMENT_TEMPLATES = "select id, status, name, comment from commenttemplate where status=?";

	@Autowired

	private EligibilityManager eligibilityManager;

	public void saveRequest(final Request request) {

		Request r = getRequest(request.getId());

		Request r_merged = null;

		if(request.getRequestCoFunders().size() > 0  ){

			logger.debug("Submition request co funders list size -> " + request.getRequestCoFunders().size());

			for(RequestCoFunder rc:request.getRequestCoFunders())

				rc.getPk().setRequest(request);

		}

		if(r != null){

			logger.debug("Request submition with id -> " + r.getId());

			logger.debug("Submition request co funders list size -> " + r.getRequestCoFunders().size());

			System.out.println("Request submition with id -> " + r.getId());

			System.out.println("Submition request co funders list size -> " + r.getRequestCoFunders().size());

			for(RequestCoFunder rc:r.getRequestCoFunders())

				rc.getPk().setRequest(r);

			r_merged = (Request)getSession().merge(request);

		}

		else{

			logger.debug("First save of request with id -> " + request.getId());

			logger.debug("Request co funders list size -> " + request.getRequestCoFunders().size());

			for(RequestCoFunder rc:request.getRequestCoFunders())

				rc.getPk().setRequest(request);

			Date date = new Date();

			request.setDate(new Timestamp(date.getTime()));

			persist(request);

		}

	}

	public List<RequestCoFunder> getCoFunders(final Request request) {

	    return request.getRequestCoFunders();

	}

	@SuppressWarnings("unchecked")

	public Request getRequest(String requestId) {

	   List<Request> r = createEntityCriteria().add(Restrictions.eq("id",requestId)).list();

	   if(r.size() > 0){

		   r.get(0).setEligibility(eligibilityManager.validate(r.get(0)));

		   return r.get(0);

	   }

	   return null;

	}

	public void submitRequest(Request r) {

        if(r.getStatus() != Request.RequestStatus.APPROVED && r.getStatus() != Request.RequestStatus.ACCOUNTING_PROCESSING &&

                r.getStatus() != Request.RequestStatus.ACCOUNTING_PAID && r.getStatus() != Request.RequestStatus.CONDITIONALLY_APPROVED){

            r.setStatus(Request.RequestStatus.SUBMITTED);

            r.setSubmissiondate(new Timestamp(new Date().getTime()));

            saveRequest(r);

        }

	}

	public void approveRequest(String requestId, User user, String comment, String template) {

		Request r = getRequest(requestId);

		r.setStatus(Request.RequestStatus.APPROVED);

		r.setApprovaldate(new Timestamp(new Date().getTime()));

		saveRequest(r);

		if (comment != null && !comment.isEmpty()) {

			saveComment(requestId, user, comment, template);

		}

	}

	public void conditionallyApproveRequest(String requestId, User personId, String comment, String template) {

		Request r = getRequest(requestId);

		r.setStatus(Request.RequestStatus.CONDITIONALLY_APPROVED);

		saveRequest(r);

		if (comment != null && !comment.isEmpty()) {

			saveComment(requestId, personId, comment, template);

		}

	}

	private void saveComment(String requestId, User user, String comment, String template) {

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

		Comment com = new Comment();

		com.setComment(comment);

		com.setId(commentId);

		com.setPerson(user);

		com.setTemplate(null);

		Request r = getRequest(requestId);

		List<Comment> cms = r.getRequestComments();

		cms.add(com);

		saveRequest(r);

	}

	public void rejectRequest(String requestId, User personId, String comment, String template) {

		Request r = getRequest(requestId);

		r.setStatus(Request.RequestStatus.REJECTED);

		saveRequest(r);

		if (comment != null && !comment.isEmpty()) {

			saveComment(requestId, personId, comment, template);

		}

	}

	public void processingRequest(String requestId, User user, String comment, String template) {

		Request r = getRequest(requestId);

		r.setStatus(Request.RequestStatus.ACCOUNTING_PROCESSING);

		saveRequest(r);

		if (comment != null && !comment.isEmpty()) {

			saveComment(requestId, user, comment, template);

		}

	}

	public void paidRequest(String requestId, User user, String comment, String template, float apc_paid, float transfer_cost, float other_cost, Date datePaid) {

		Request r = getRequest(requestId);

		r.setStatus(Request.RequestStatus.ACCOUNTING_PAID);

		r.setApcPaid(apc_paid);

		r.setOtherCost(other_cost);

		r.setDatePaid(new Timestamp(datePaid.getTime()));

		saveRequest(r);

		if (comment != null && !comment.isEmpty()) {

			saveComment(requestId, user, comment, template);

		}

	}

	public void paidLibraryRequest(String requestId, User user, String comment, String template,

								   float apc_paid, float transfer_cost, float other_cost, Date datePaid) {

		Request r = getRequest(requestId);

		r.setStatus(Request.RequestStatus.LIBRARY_FUND_PAID);

		r.setApcPaid(apc_paid);

		r.setTransferCost(transfer_cost);

		r.setOtherCost(other_cost);

		r.setDatePaid(new Timestamp(datePaid.getTime()));

		saveRequest(r);

		if (comment != null && !comment.isEmpty()) {

			saveComment(requestId, user, comment, template);

		}

	}

	public void paidPublisherRequest(String requestId, User user, String comment, String template,

									 float apc_paid, float transfer_cost, float other_cost, Date datePaid) {

		Request r = getRequest(requestId);

		r.setStatus(Request.RequestStatus.PUBLISHER_FUND_PAID);

		r.setApcPaid(apc_paid);

		r.setTransferCost(transfer_cost);

		r.setOtherCost(other_cost);

		r.setDatePaid(new Timestamp(datePaid.getTime()));

		saveRequest(r);

		if (comment != null && !comment.isEmpty()) {

			saveComment(requestId, user, comment, template);

		}

	}

	public void onHoldRequest(String requestId, User user, String comment, String template) {

		Request r = getRequest(requestId);

		r.setStatus(Request.RequestStatus.ACCOUNTING_ON_HOLD);

		saveRequest(r);

		if (comment != null && !comment.isEmpty()) {

			saveComment(requestId, user, comment, template);

		}

	}

	public void deniedRequest(String requestId, User user, String comment, String template) {

		Request r = getRequest(requestId);

		r.setStatus(Request.RequestStatus.ACCOUNTING_DENIED);

		saveRequest(r);

		if (comment != null && !comment.isEmpty()) {

			saveComment(requestId, user, comment, template);

		}

	}

	@SuppressWarnings("unchecked")

	public List<Request> getForProject(String projectId) {

		return createEntityCriteria().add(Restrictions.eq("project.id",projectId)).list();

	}

    public RequestPage getForUser(String personId, Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus[] statusFilter, int from, int to) {

        return this.getRequestPage(personId, null, null, requestSortBy, order, requestFilter, term, getStatusFilter(statusFilter), from, to);

    }

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

        return this.getRequestPage(null, organizationIds, null, requestSortBy, order, requestFilter, term, getStatusFilter(statusFilter), from, to);

    }

    public RequestPage getForPublisher(String publisherId, Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus[] statusFilter, int from, int to) {

        return this.getRequestPage(null, null, publisherId, requestSortBy, order, requestFilter, term, getStatusFilter(statusFilter), from, to);

    }

    public RequestPage getRequests(Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Request.RequestStatus[] statusFilter, int from, int to) {

        return this.getRequestPage(null, null, null, requestSortBy, order, requestFilter, term, getStatusFilter(statusFilter), from, to);

    }

    public RequestPage getRequests(Date fromDate, Date toDate, RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Integer[] statusFilter, int from, int to) {

        return this.getRequestPage(null, null, null, requestSortBy, order, requestFilter, term, statusFilter, from, to);

    }

	public BigInteger getRequestId() {

		return (BigInteger) getSession().createSQLQuery("select nextval('request_id_seq') as id").list().get(0);

	}

	@SuppressWarnings("unchecked")

	public String getCSV(RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter, String term, Integer[] statuses) throws IOException {

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

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

		String requestQuery = this.getQuery(null, null, null, requestSortBy, order, requestFilter, term, statuses, args, types);

		String query = "select r.id as rid, r.status, r.date, u.firstname, u.lastname, o.name as organization," +

				" c.name as country, p.id as pid, p.acronym, p.startdate, p.enddate, p.scientificarea, " +

				"pub.title, pub.type, j.title as journal,(case when jp.id is not null then jp.name else mp.name end) as publisher, " +

				" r.approvaldate as approvaldate, r.apc as apc,r.currency as currency,r.datepaid, r.apcpaid, r.transfercost, " +

				" r.othercost, comment.comment as comment from (" + requestQuery + ") r " +

				"left join \"user\" u on r.researcher = u.email " +

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

		System.out.println(query);

		List<String[]> requests = new ArrayList<>();

		Type[] t = new Type[types.size()];

		t = types.toArray(t);

		List<Object[]> resultSet = getSession().createSQLQuery(query)

				.setParameters(args.toArray(), t).list();

		for(Object rs[] : resultSet){

			String[] objs = new String[24];

			objs[0] = rs[0].toString();

			objs[1] = String.valueOf(Request.RequestStatus.forStatus(Integer.parseInt(rs[1].toString())));

			objs[2] = rs[2]!=null?new SimpleDateFormat("yyyy/MM/dd").format(rs[2]):null;

			objs[3] = rs[3]!=null?rs[3].toString():null;

			objs[4] = rs[4]!=null?rs[4].toString():null;

			objs[5] = rs[5]!=null?rs[5].toString():null;

			objs[6] = rs[6]!=null?rs[6].toString():null;

			//project id

			objs[7] = rs[7]!=null?rs[7].toString():null;

			objs[8] = rs[8]!=null?rs[8].toString():null;

			objs[9] = rs[9]!=null?new SimpleDateFormat("yyyy/MM/dd").format(rs[9]):null;

			objs[10] = rs[10]!=null?new SimpleDateFormat("yyyy/MM/dd").format(rs[10]):null;

			objs[11] = rs[11]!=null?rs[11].toString():null;

			objs[12] = rs[12]!=null?rs[12].toString():null;

			objs[13] = rs[13]!=null?rs[13].toString():null;

			objs[14] = rs[14]!=null?rs[14].toString():null;

			objs[15] = rs[15]!=null?rs[15].toString():null;

			objs[16] = rs[16]!=null?new SimpleDateFormat("yyyy/MM/dd").format(rs[16]):null;

			objs[17] = rs[20]!=null?Float.toString(Float.parseFloat(rs[17].toString())):null;

			objs[18] = rs[18]!=null?rs[18].toString():null;

			objs[19] = rs[19]!=null?new SimpleDateFormat("yyyy/MM/dd").format(rs[19]):null;

			objs[20] = rs[20]!=null?rs[20].toString() + "":null;

			objs[21] = rs[21]!=null?rs[21].toString() + "":null;

			objs[22] = rs[22]!=null?rs[22].toString() + "":null;

			objs[23] = rs[23]!=null?rs[23].toString():null;

			requests.add(objs);

		}

		StringWriter sw = new StringWriter();

		CSVWriter csvWriter = new CSVWriter(sw);

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

		csvWriter.writeAll(requests, false);

		csvWriter.close();

		sw.close();

		return sw.toString();

	}

	@SuppressWarnings("unchecked")

	private RequestPage getRequestPage(String personId, List<String> organizationIds, String publisherId,

                                       RequestSort requestSortBy, RequestSortOrder order, RequestFilter requestFilter,

                                       String term, Integer[] statusFilterCodes, int from, int to) {

		RequestPage page = new RequestPage();

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

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

		String query = getQuery(personId, organizationIds, publisherId, requestSortBy, order,

				requestFilter, term, statusFilterCodes, args, types);

		Type[] t = new Type[types.size()];

		t = types.toArray(t);

		page.setRequests(getSession().createSQLQuery(applyPaging(query, from, to))

				.addEntity(Request.class)

				.setParameters(args.toArray(),t)

				.list());

		BigInteger bi = (BigInteger) getSession().createSQLQuery(getCountQuery(query))

				.setParameters(args.toArray(),t)

				.list().get(0);

		page.setTotal(bi.intValue());

		page.setFrom(from);

		page.setTo(to);

		return page;

	}

	private String getCountQuery(String query) {

		StringBuilder sb = new StringBuilder();

		sb.append("select count(*) from (").append(query).append(") foo");

		return sb.toString();

	}

	private String getQuery(String email, List<String> organizationIds, String publisherId, RequestSort requestSortBy, RequestSortOrder order,

							RequestFilter requestFilter, String term, Integer[] statusFilterCodes, List<Object> args, List<Type> types) {

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

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

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

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

		String orderBy = null;

		if (term != null) {

			if (requestFilter != null) {

				switch (requestFilter) {

					case ID:

						whereClauses.add("(r.id like ? )");

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

						types.add(StringType.INSTANCE);

						break;

					case USER:

						joins.put("user", " join \"user\" u on u.email=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(StringType.INSTANCE);

						types.add(StringType.INSTANCE);

						types.add(StringType.INSTANCE);

						break;

					case RESEARCHER:

						joins.put("researcher", " join \"user\" res on res.email=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(StringType.INSTANCE);

						types.add(StringType.INSTANCE);

						types.add(StringType.INSTANCE);

						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(StringType.INSTANCE);

						types.add(StringType.INSTANCE);

						types.add(StringType.INSTANCE);

						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(StringType.INSTANCE);

						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(StringType.INSTANCE);

						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(StringType.INSTANCE);

						break;

					case INVOICE:

						joins.put("invoice", "join invoice i on i.id=r.invoice and (lower(i.number) like lower(?) or lower(alternativeid) like lower(?))");

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

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

						types.add(StringType.INSTANCE);

						types.add(StringType.INSTANCE);

						break;

				}

			} else {

				whereClauses.add("(r.id like ? )");

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

				types.add(StringType.INSTANCE);

				joins.put("user", " left join \"user\" u on u.email=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(StringType.INSTANCE);

				whereTypes.add(StringType.INSTANCE);

				whereTypes.add(StringType.INSTANCE);

				joins.put("researcher", " left join \"user\" res on res.email=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(StringType.INSTANCE);

				whereTypes.add(StringType.INSTANCE);

				whereTypes.add(StringType.INSTANCE);

				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(StringType.INSTANCE);

				whereTypes.add(StringType.INSTANCE);

				whereTypes.add(StringType.INSTANCE);

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

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

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

				whereTypes.add(StringType.INSTANCE);

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

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

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

				whereTypes.add(StringType.INSTANCE);

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

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

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

				whereTypes.add(StringType.INSTANCE);

				joins.put("invoice", " left join invoice i on i.id=r.invoice ");

				whereClauses.add("(lower(i.number) like lower(?) or lower(alternativeid) like lower(?))");

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

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

				whereTypes.add(StringType.INSTANCE);

				whereTypes.add(StringType.INSTANCE);

			}

		}

		if (requestSortBy != null) {

			switch (requestSortBy) {

				case DATE:

					orderBy = " r.date ";

					break;

				case FUNDING_REQUESTED:

					orderBy = " r.fundingrequested ";

					break;

				case USER:

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

						joins.put("user", " left join \"user\" u on u.email=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 RESEARCHER:

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

						joins.put("researcher", " left join \"user\" res on res.email=r.researcher ");

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

					break;

				case PROJECT:

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

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

					orderBy = " proj.acronym ";

					break;

				case JOURNAL:

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

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

					orderBy = " j.title ";

					break;

				case PUBLISHER:

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

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

					orderBy = " publ.name ";

					break;

				case STATUS:

				default:

					orderBy = " r.status ";

					break;

			}

		}

		//TODO fix bank fields and publisher_email

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

		StringBuilder sb = new StringBuilder("select r.id as id, " +

				"r.\"user\" as \"user\", r.date as date, r.researcher as researcher, r.organization as organization, " +

				"r.project as project, r.publication as publication, r.journal as journal, r.publisher as publisher,  " +

				"r.budget as budget, r.invoice as invoice , r.apc as apc, r.discount as discount, " +

				"r.projectparticipation as projectparticipation, r.fundingrequested as fundingrequested, r.currency as currency, " +

				"r.apcPaid as apcPaid, r.transferCost as transferCost, r.otherCost as otherCost, r.datePaid as datePaid, " +

				"r.submissiondate as submissiondate, r.approvaldate as approvaldate, r.status as status ," +

				"r.bankAccount as bankAccount , r.bankTransferReceipt as bankTransferReceipt ," +

				"r.publisher_email as publisher_email from request r ");

		for (String join : joins.values())

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

		sb.append(" where ");

		if (email != null) {

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

			args.add(email);

			types.add(StringType.INSTANCE);

		} else if (organizationIds != null) {

			sb.append("(");

			for (int i = 0; i < organizationIds.size(); i++) {

				if (i > 0)

					sb.append(" or ");

				sb.append(" r.organization=? ");

				args.add(organizationIds.get(i));

				types.add(StringType.INSTANCE);

			}

			sb.append(")");

		} else if (publisherId != null) {

			if (joins.get("journal") == null) {

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

				sb.append(" left join journal j on j.id=r.journal ");

				sb.append(" where ");

			}

			sb.append("(r.publisher=? or j.publisher=?)");

			args.add(publisherId);

			types.add(StringType.INSTANCE);

			args.add(publisherId);

			types.add(StringType.INSTANCE);

		}

		if (statusFilterCodes != null) {

			StringBuilder ssb = new StringBuilder();

			for (Integer code:statusFilterCodes) {

				String clause;

				switch (code) {

					default:

						clause = " (r.status = " + code + ")";

						break;

				}

				ssb.append(ssb.length()==0?clause:" or " + clause);

			}

			if (sb.toString().trim().endsWith("where"))

				sb.append("(").append(ssb.toString()).append(")");

			else

				sb.append(" and ").append("(").append(ssb.toString()).append(")");

		}

		if (whereClauses.size() > 0) {

			if (!sb.toString().trim().endsWith("where"))

				sb.append(" and ");

			sb.append("(");

			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 (sb.toString().trim().endsWith("where"))

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

		if (orderBy != null) {

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

			if (order == RequestSortOrder.ASCENDING)

				sb.append(" asc");

			else

				sb.append(" desc");

		}

		return sb.toString();

	}

	private String applyPaging(String query, int from, int to) {

		StringBuilder sb = new StringBuilder(query);

		if (from > 0 || to > 0) {

			sb.append(" limit ").append(to - from + 1);

			sb.append(" offset ").append(from);

		}

		return sb.toString();

	}

	private Integer[] getStatusFilter(Request.RequestStatus[] statuses) {

		if (statuses == null)

			return null;

		else {

			Integer[] res = new Integer[statuses.length];

			for (int i = 0; i < statuses.length; i++)

				res[i] = statuses[i].getCode();

			return res;

		}

	}

	public List<Comment> getComments(String id) {

		return getRequest(id).getRequestComments();

	}

	public void uploadBankTransferReceipt(final String requestid, final String contentType, InputStream inputStream) {

		try {

			final ByteArrayOutputStream baos = new ByteArrayOutputStream();

			Request r = getRequest(requestid);

			IOUtils.copy(inputStream, baos);

			IOUtils.closeQuietly(baos);

			r.setBankTransferReceipt(new BankTransferReceipt(contentType,baos.toByteArray()));

			saveRequest(r);