D-Net

{"type_source": "SVN", 

"goal": "package -U source:jar", 

"url": "http://svn-public.driver.research-infrastructures.eu/driver/dnet40/modules/cnr-openaire-exporter/trunk/", 

"deploy_repository": "dnet4-snapshots", 

"version": "4", 

"mail": "sandro.labruzzo@isti.cnr.it,michele.artini@isti.cnr.it, claudio.atzori@isti.cnr.it, alessia.bardi@isti.cnr.it", 

"deploy_repository_url": "http://maven.research-infrastructures.eu/nexus/content/repositories/dnet4-snapshots", "name": "cnr-openaire-exporter"}

package eu.dnetlib.openaire.exporter;

import org.junit.Before;

import org.junit.Test;

import static org.junit.Assert.assertEquals;

public class ProjectQueryParamsTest {

	private ProjectQueryParams queryParams;

	@Before

	public void setUp() throws Exception {

		queryParams = new ProjectQueryParams();

	}

	@Test

	public void testVerifyParamWhiteSpace() {

		queryParams.verifyParam("Discovery Projects");

	}

	@Test

	public void testVerifyParamPercentage() {

		queryParams.verifyParam("Discovery%20Projects");

	}

	@Test

	public void testVerifyDateParam(){

		String correctDate = "2012-03-04";

		assertEquals(correctDate, queryParams.verifyDateParam(correctDate));

	}

	@Test(expected=java.lang.IllegalArgumentException.class)

	public void testVerifyDateParamException(){

		String wrongDate = "12-12-12";

		queryParams.verifyDateParam(wrongDate);

	}

}

package eu.dnetlib.openaire.exporter;

import java.io.IOException;

import org.antlr.stringtemplate.StringTemplate;

import org.apache.commons.io.IOUtils;

import org.junit.Before;

import org.junit.Test;

import org.springframework.core.io.ClassPathResource;

import org.springframework.core.io.Resource;

import static org.junit.Assert.assertEquals;

public class ProjectsControllerTest {

	private String queryTemplate = "/eu/dnetlib/openaire/exporter/sql/projects_fundings.sql.st";

	private Resource expectedQueryTemplate = new ClassPathResource("/eu/dnetlib/openaire/exporter/sql/expected_projects_fundings.sql.st");

	private ProjectsController controller;

	private ProjectQueryParams params;

	@Before

	public void setup() {

		controller = new ProjectsController();

		Resource template = new ClassPathResource(queryTemplate);

		controller.setProjectsFundingQueryTemplate(template);

		params = new ProjectQueryParams();

	}

	@Test

	public void testObtainFP7Query() throws IllegalArgumentException, IOException {

		params.setFundingProgramme("FP7");

		params.setFundingPath(null);

		String res = controller.obtainQuery(params);

		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream()));

		st.setAttribute("fundingprefix", "ec__________::EC::FP7");

		System.out.println(res);

		System.out.println(st);

		assertEquals(st.toString(), res);

	}

	@Test

	public void testObtainFP7QuerySP1() throws IllegalArgumentException, IOException {

		params.setFundingProgramme("FP7");

		params.setFundingPath("SP1");

		String res = controller.obtainQuery(params);

		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream()));

		st.setAttribute("fundingprefix", "ec__________::EC::FP7::SP1");

		System.out.println(res);

		assertEquals(st.toString(), res);

	}

	@Test

	public void testObtainFP7QueryHealth() throws IllegalArgumentException, IOException {

		params.setFundingProgramme("FP7");

		params.setFundingPath("SP1::HEALTH");

		String res = controller.obtainQuery(params);

		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream()));

		st.setAttribute("fundingprefix", "ec__________::EC::FP7::SP1::HEALTH");

		System.out.println(res);

		assertEquals(st.toString(), res);

	}

	@Test

	public void testObtainFP7QueryHealth2() throws IllegalArgumentException, IOException {

		params.setFundingProgramme("FP7");

		params.setFundingPath("%::HEALTH");

		String res = controller.obtainQuery(params);

		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream()));

		st.setAttribute("fundingprefix", "ec__________::EC::FP7::%::HEALTH");

		System.out.println(res);

		assertEquals(st.toString(), res);

	}

	@Test

	public void testObtainWellcomeTrustQuery() throws IllegalArgumentException, IOException {

		params.setFundingProgramme("WT");

		params.setFundingPath(null);

		String res = controller.obtainQuery(params);

		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream()));

		st.setAttribute("fundingprefix", "wt__________::WT");

		System.out.println(res);

		assertEquals(st.toString(), res);

	}

	@Test

	public void testObtainFCTQuery() throws IllegalArgumentException, IOException {

		params.setFundingProgramme("FCT");

		params.setFundingPath(null);

		String res = controller.obtainQuery(params);

		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream()));

		st.setAttribute("fundingprefix", "fct_________::FCT");

		System.out.println(res);

		assertEquals(st.toString(), res);

	}

	@Test

	public void testQueryWithDateParams() throws IllegalArgumentException, IOException {

		params.setFundingProgramme("WT");

		params.setFundingPath(null);

		params.setStartFrom("2015-05-04");

		String res = controller.obtainQuery(params);

		System.out.println(res);

		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream()));

		st.setAttribute("fundingprefix", "wt__________::WT");

		String q = st.toString() + " AND startdate >= '2015-05-04'";

		assertEquals(q, res);

	}

	@Test

	public void testObtainSNSFQuery() throws IllegalArgumentException, IOException {

		params.setFundingProgramme("SNSF");

		params.setFundingPath(null);

		String res = controller.obtainQuery(params);

		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream()));

		st.setAttribute("fundingprefix", "snsf________::SNSF");

		System.out.println(res);

		assertEquals(st.toString(), res);

	}

}

<?xml version="1.0" encoding="UTF-8"?>

<ROW>

	<FIELD name="funder">FCT</FIELD>

	<FIELD name="jurisdiction">PT</FIELD>

	<FIELD name="end_date">2003-10-31</FIELD>

	<FIELD name="grant_agreement_number">32639</FIELD>

	<FIELD name="title">Social Classes and «lifestyles» in the city of Oporto</FIELD>

	<FIELD name="fundingpathid">fct_________::FCT::Orçamento de Funcionamento/POSC</FIELD>

	<FIELD name="acronym">PTDC/FIL-FIL/109889/2009</FIELD>

	<FIELD name="start_date">2000-11-01</FIELD>

	<FIELD name="description">description of the funding path</FIELD>

</ROW> 

<?xml version="1.0" encoding="UTF-8"?>

<ROW>

	<FIELD name="funder">EC</FIELD>

	<FIELD name="jurisdiction">EU</FIELD>

	<FIELD name="end_date">2012-07-31</FIELD>

	<FIELD name="grant_agreement_number">255646</FIELD>

	<FIELD name="title">Semiconductor lasers for generation of non-diffracting (Bessel) beams.</FIELD>

	<FIELD name="fundingpathid">ec__________::EC::FP7::SP3::PEOPLE</FIELD>

	<FIELD name="acronym">SENDBEAMS</FIELD>

	<FIELD name="start_date">2010-08-01</FIELD>

	<FIELD name="description">description of the funding path</FIELD>

</ROW> 

SELECT

	funder,

	jurisdiction,

	fundingpathid,

	acronym             AS "Project Acronym",

	title               AS "Project Title",

	code                AS "Grant Agreement Number",

	startdate           AS "Start Date",

	enddate             AS "End Date"

FROM projects_api

WHERE fundingpathid like '$fundingprefix$%'

<?xml version="1.0" encoding="UTF-8"?>

<ROW>

	<FIELD name="funder">SFI</FIELD>

	<FIELD name="end_date">2016-08-31</FIELD>

	<FIELD name="grant_agreement_number">14/ADV/RC3022</FIELD>

	<FIELD name="title">Dr. Vesna Jaksic</FIELD>

	<FIELD name="acronym" isNull="true"/>

	<FIELD name="fundingpathid">sfi_________::SFI::Advance Award Programme</FIELD>

	<FIELD name="jurisdiction" isNull="true"/>

	<FIELD name="start_date">2014-09-01</FIELD>

</ROW>

<?xml version="1.0" encoding="UTF-8"?>

<ROW>

	<FIELD name="funder">MSES</FIELD>

	<FIELD name="end_date">2009-12-31</FIELD>

	<FIELD name="grant_agreement_number">001-0000000-3177</FIELD>

	<FIELD name="title">Residence time of bacteria Escherichia coli in seawater and marine organisms</FIELD>

	<FIELD name="acronym" isNull="true"/>

	<FIELD name="fundingpathid">irb_hr______::MSES::fundingStream</FIELD>

	<FIELD name="jurisdiction">HR</FIELD>

	<FIELD name="start_date">2007-01-01</FIELD>

</ROW>

<ROW>

	<FIELD name="funder">WT</FIELD>

	<FIELD name="end_date">2013-07-29</FIELD>

	<FIELD name="grant_agreement_number">098241</FIELD>

	<FIELD name="title">EYEDIO DIGI - DEVELOPMENT OF AN INNOVATIVE, AFFORDABLE, EASY TO USE, HANDHELD RETINAL IMAGING PRODUCT FOR THE DIAGNOSIS OF DIABETIC RETINOPATHY.</FIELD>

	<FIELD name="acronym"></FIELD>

	<FIELD name="fundingpathid">wt__________::WT::Immunology and Infectious Disease</FIELD>

	<FIELD name="start_date">2012-01-30</FIELD>

	<FIELD name="jurisdiction"/>

	<FIELD name="description">description of the funding path</FIELD>

</ROW> 

package eu.dnetlib.openaire.exporter;

import java.io.IOException;

import java.io.OutputStream;

import java.nio.charset.Charset;

import java.sql.*;

import java.util.Arrays;

import java.util.List;

import java.util.zip.GZIPOutputStream;

import java.util.zip.ZipOutputStream;

import com.google.common.base.Function;

import com.google.common.base.Joiner;

import com.google.common.base.Splitter;

import com.google.common.collect.Iterables;

import com.google.common.collect.Lists;

import eu.dnetlib.miscutils.datetime.HumanTime;

import eu.dnetlib.openaire.exporter.model.Project;

import eu.dnetlib.openaire.exporter.model.ProjectDetail;

import org.antlr.stringtemplate.StringTemplate;

import org.apache.commons.dbcp2.BasicDataSource;

import org.apache.commons.lang.StringUtils;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

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

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

/**

 * Created by claudio on 20/09/16.

 */

public class JdbcApiDao {

	public static final Charset UTF8 = Charset.forName("UTF-8");

	private static final Log log = LogFactory.getLog(JdbcApiDao.class);

	@Value("${openaire.exporter.jdbc.maxrows}")

	private int maxRows;

	@Value("${openaire.exporter.projectdetails.flushsize}")

	private int gzipFlushSize;

	@Value("${openaire.exporter.projects2tsv.fields}")

	private String tsvFields;

	@Autowired

	private BasicDataSource apiDataSource;

	public void streamProjects(final String sql, final OutputStream out,

			final String head, final StringTemplate projectTemplate, final String tail,

			final ValueCleaner cleaner) throws IOException, SQLException {

		if (log.isDebugEnabled()) {

			log.debug("Thread " + Thread.currentThread().getId() + " begin");

		}

		final long start = System.currentTimeMillis();

		if (StringUtils.isNotBlank(head)) {

			out.write(head.getBytes(UTF8));

		}

		try(final Connection con = getConn(); final PreparedStatement stm = getStm(sql, con); final ResultSet rs = stm.executeQuery()) {

			while(rs.next()){

				final Project p = new Project()

						.setFunder(cleaner.clean(rs.getString("funder")))

						.setJurisdiction(cleaner.clean(rs.getString("jurisdiction")))

						.setFundingpathid(cleaner.clean(rs.getString("fundingpathid")))

						.setAcronym(cleaner.clean(rs.getString("Project Acronym")))

						.setTitle(cleaner.clean(rs.getString("Project Title")))

						.setCode(cleaner.clean(rs.getString("Grant Agreement Number")))

						.setStartdate(cleaner.clean(rs.getString("Start Date")))

						.setEnddate(cleaner.clean(rs.getString("End Date")));

				projectTemplate.reset();

				projectTemplate.setAttribute("p", p);

				out.write(projectTemplate.toString().getBytes(UTF8));

			}

			if (StringUtils.isNotBlank(tail)) {

				out.write(tail.getBytes(UTF8));

			}

			if (log.isDebugEnabled()) {

				log.debug("Thread " + Thread.currentThread().getId() + " ends, took: " + HumanTime.exactly(System.currentTimeMillis() - start));

			}

		} finally {

			out.close();

		}

	}

	public void streamProjectsTSV(final String sql, final ZipOutputStream out) throws IOException, SQLException {

		if (log.isDebugEnabled()) {

			log.debug("Thread " + Thread.currentThread().getId() + " begin");

		}

		final long start = System.currentTimeMillis();

		final List<String> fields = Lists.newArrayList(Splitter.on(",").omitEmptyStrings().trimResults().split(tsvFields));

		writeCSVLine(out, fields);

		try(final Connection con = getConn(); final PreparedStatement stm = getStm(sql, con); final ResultSet rs = stm.executeQuery()) {

			while(rs.next()) {

				final Project p = new Project()

						.setCode(rs.getString("Grant Agreement Number"))

						.setAcronym(rs.getString("Project Acronym"))

						.setTitle(rs.getString("Project Title"))

						.setCall_identifier(rs.getString("Call ID"))

						.setStartdate(rs.getString("Start Date"))

						.setEnddate(rs.getString("End Date"))

						.setEc_sc39(rs.getBoolean("ec_sc39"))

						.setOa_mandate_for_publications(rs.getBoolean("oa_mandate_for_publications"))

						.setEc_article29_3(rs.getBoolean("ec_article29_3"))

						.setDescription(rs.getString("Discipline"))

						.setLegalname(rs.getString("Organization"))

						.setCountryclass(rs.getString("Country"))

						.setRole(rs.getString("Role"))

						.setFirstname(rs.getString("Person Name"))

						.setSecondnames(rs.getString("Person Second Names"))

						.setEmail(rs.getString("Person Email"));

				writeCSVLine(out, p.asList());

			}

			out.closeEntry();

			if (log.isDebugEnabled()) {

				log.debug("Thread " + Thread.currentThread().getId() + " ends, took: " + HumanTime.exactly(System.currentTimeMillis() - start));

			}

		} finally {

			out.close();

		}

	}

	private void writeCSVLine(final ZipOutputStream out, final List<String> list) throws IOException {

		out.write(Joiner.on('\t').useForNull("").join(list).getBytes(UTF8));

		out.write('\n');

	}

	public void streamProjectDetails(final String sql, final OutputStream out, final String format) throws SQLException, IOException {

		if (log.isDebugEnabled()) {

			log.debug("Thread " + Thread.currentThread().getId() + " begin");

		}

		final long start = System.currentTimeMillis();

		int i = 0;

		try(final Connection con = getConn(); final PreparedStatement stm = getStm(sql, con); final ResultSet rs = stm.executeQuery()) {

			while (rs.next()) {

				final ProjectDetail p = getProjectDetail(rs);

				switch (format) {

				case "csv":

					out.write(p.asCSV().getBytes(UTF8));

					break;

				case "json":

					out.write(p.asJson().getBytes(UTF8));

					break;

				}

				if (++i % gzipFlushSize == 0) {

					log.debug("flushing output stream");

					out.flush();

				}

			}

			if (log.isDebugEnabled()) {

				log.debug("Thread " + Thread.currentThread().getId() + " ends, took: " + HumanTime.exactly(System.currentTimeMillis() - start));

			}

		} finally {

			if (out instanceof GZIPOutputStream) {

				((GZIPOutputStream) out).finish();

			}

			out.close();

		}

	}

	private Connection getConn() throws SQLException {

		final Connection conn = apiDataSource.getConnection();

		conn.setAutoCommit(false);

		return conn;

	}

	private PreparedStatement getStm(final String sql, final Connection con) throws SQLException {

		final PreparedStatement stm = con.prepareStatement(sql);

		stm.setFetchSize(maxRows);

		return stm;

	}

	private ProjectDetail getProjectDetail(final ResultSet rs) throws SQLException {

		return new ProjectDetail()

				.setProjectId(rs.getString("projectid"))

				.setAcronym(rs.getString("acronym"))

				.setCode(rs.getString("code"))

				.setOptional1(rs.getString("optional1"))

				.setOptional2(rs.getString("optional2"))

				.setJsonextrainfo(rs.getString("jsonextrainfo"))

				.setFundingPath(asList(rs.getArray("fundingpath")));

	}

	private List<String> asList(final Array value) throws SQLException {

		if (value != null) {

			final List<Object> list = Arrays.asList((Object[]) value.getArray());

			return Lists.newArrayList(Iterables.transform(list, new Function<Object, String>() {

				@Override

				public String apply(final Object o) {

					return o != null ? o.toString() : null;

				}

			}));

		}

		return Lists.newArrayList();

	}

}

package eu.dnetlib.openaire.exporter;

import java.util.regex.Pattern;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

public class ProjectQueryParams {

	private String fundingProgramme = null;

	/** Whatever is following the fundingProgramme **/

	private String fundingPath = null;

	private String startFrom = null;

	private String startUntil = null;

	private String endFrom = null;

	private String endUntil = null;

	private static final Log log = LogFactory.getLog(ProjectQueryParams.class); // NOPMD by marko on 11/24/08 5:02 PM

	public String getFundingProgramme() {

		return fundingProgramme;

	}

	public void setFundingProgramme(final String fundingProgramme) {

		this.fundingProgramme = verifyParam(fundingProgramme);

	}

	public String getFundingPath() {

		return fundingPath;

	}

	public void setFundingPath(final String fundingPath) {

		this.fundingPath = verifyParam(fundingPath);

	}

	public String getStartFrom() {

		return startFrom;

	}

	public void setStartFrom(final String startFrom) {

		this.startFrom = verifyDateParam(startFrom);

	}

	public String getStartUntil() {

		return startUntil;

	}

	public void setStartUntil(final String startUntil) {

		this.startUntil = verifyDateParam(startUntil);

	}

	public String getEndFrom() {

		return endFrom;

	}

	public void setEndFrom(final String endFrom) {

		this.endFrom = verifyDateParam(endFrom);

	}

	public String getEndUntil() {

		return endUntil;

	}

	public void setEndUntil(final String endUntil) {

		this.endUntil = verifyDateParam(endUntil);

	}

	protected String verifyParam(final String p) {

		Pattern pattern = Pattern.compile("(\\w*(::|%| )*)*");

		log.debug("TESTING SQL PARAM: " + p);

		if ((p != null) && !pattern.matcher(p).matches()) throw new IllegalArgumentException("Parameter contains an invalid character");

		log.debug("TEST OK");

		return p;

	}

	protected String verifyDateParam(final String date){

		Pattern pattern = Pattern.compile("\\d\\d\\d\\d-\\d\\d-\\d\\d");

		log.debug("TESTING SQL DATE PARAM: " + date);

		if ((date != null) && !pattern.matcher(date).matches()) throw new IllegalArgumentException("Parameter date contains an invalid character. Accepted pattern is "+pattern.toString());

		log.debug("TEST OK");

		return date;

	}

}

package eu.dnetlib.openaire.exporter;

/**

 * Created by claudio on 23/09/2016.

 */

public interface ValueCleaner {

	public String clean(String s);

}

package eu.dnetlib.openaire.exporter;

import javax.servlet.http.HttpServletRequest;

public class ProjectQueryParamsFactory {

	private static final String BASE_PATH = "/openaire/export/";

	private static final String NO_FILTER = "ALL";

	public ProjectQueryParams generateParams(final HttpServletRequest request,

			final String startFrom,

			final String startUntil,

			final String endFrom,

			final String endUntil) {

		ProjectQueryParams params = new ProjectQueryParams();

		String[] arr = request.getPathInfo().replace(BASE_PATH, "").split("\\/");

		if (arr.length != 5) throw new IllegalArgumentException("Invalid url");

		params.setFundingProgramme(arr[0]);

		String stream = NO_FILTER.equals(arr[1]) ? null : arr[1];

		String substream = NO_FILTER.equals(arr[2]) ? null : arr[2];

		if (substream == null) {

			params.setFundingPath(stream);

		} else {

			if (stream == null) {

				stream = "%";

			}

			params.setFundingPath(stream + "::" + substream);

		}

		// params.setSpecificProgramme(NO_FILTER.equals(arr[1]) ? null : arr[1]);

		// params.setSubdivision(NO_FILTER.equals(arr[2]) ? null : arr[2]);

		// NB: arr[3] should be 'projects'

		// NB: arr[4] should be '[file].do'

		params.setStartFrom(startFrom);

		params.setStartUntil(startUntil);

		params.setEndFrom(endFrom);

		params.setEndUntil(endUntil);

		return params;

	}

}

package eu.dnetlib.openaire.exporter;

import java.io.BufferedOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.sql.SQLException;

import java.text.SimpleDateFormat;

import java.util.Arrays;

import java.util.Date;

import java.util.Map;

import java.util.zip.GZIPOutputStream;

import java.util.zip.ZipEntry;

import java.util.zip.ZipOutputStream;

import javax.servlet.ServletOutputStream;

import javax.servlet.ServletResponse;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.google.common.collect.Maps;

import com.google.common.xml.XmlEscapers;

import org.antlr.stringtemplate.StringTemplate;

import org.apache.commons.io.IOUtils;

import org.apache.commons.lang.StringUtils;

import org.apache.commons.lang.exception.ExceptionUtils;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

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

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

import org.springframework.core.io.Resource;

import org.springframework.http.HttpStatus;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.*;

@Controller

public class ProjectsController {

	private static final Log log = LogFactory.getLog(ProjectsController.class); // NOPMD by marko on 11/24/08 5:02 PM

	@Value("${openaire.exporter.projectsfundings.sql.template}")

	private Resource projectsFundingQueryTemplate;

	@Value("${openaire.exporter.projects2tsv.sql.template}")

	private Resource projects2tsvQueryTemplate;

	@Value("${openaire.exporter.dspace.template.project}")

	private Resource dspaceTemplate;

	@Value("${openaire.exporter.dspace.template.head}")

	private Resource dspaceHeadTemplate;

	@Value("${openaire.exporter.dspace.template.tail}")

	private Resource dspaceTailTemplate;

	@Value("${openaire.exporter.eprints.template}")

	private Resource eprintsTemplate;

	@Value("${openaire.exporter.projectdetails.sql}")

	private Resource projectDetailsSql;

	@Autowired

	private JdbcApiDao dao;

	@Autowired

	private ProjectQueryParamsFactory projectQueryParamsFactory;

	public enum Funding {

		FP7, WT, FCT, H2020, NHMRC, ARC, SFI, MZOS, HRZZ, NWO, MESTD, FWF, SNSF, RCUK

	}

	@RequestMapping(value = "/openaire/export/**/project/dspace.do")

	void processDspace(final HttpServletRequest request,

			final ServletResponse response,

			@RequestParam(value = "startFrom", required = false) final String startFrom,

			@RequestParam(value = "startUntil", required = false) final String startUntil,

			@RequestParam(value = "endFrom", required = false) final String endFrom,

			@RequestParam(value = "endUntil", required = false) final String endUntil) throws Exception {

		final ProjectQueryParams params = projectQueryParamsFactory.generateParams(request, startFrom, startUntil, endFrom, endUntil);

		final StringTemplate headSt = new StringTemplate(IOUtils.toString(dspaceHeadTemplate.getInputStream()));

		headSt.setAttribute("fundingProgramme", params.getFundingProgramme());

		final StringTemplate tailSt = new StringTemplate(IOUtils.toString(dspaceTailTemplate.getInputStream()));

		response.setContentType("text/xml");

		doProcess(response, params, headSt.toString(), dspaceTemplate, tailSt.toString(), new ValueCleaner() {

			@Override

			public String clean(final String s) {

				return XmlEscapers.xmlContentEscaper().escape(oneLiner(s));

			}

		});

	}

	@RequestMapping(value = "/openaire/export/**/project/eprints.do")

	void processEprints(final HttpServletRequest request,

			final ServletResponse response,

			@RequestParam(value = "startFrom", required = false) final String startFrom,

			@RequestParam(value = "startUntil", required = false) final String startUntil,

			@RequestParam(value = "endFrom", required = false) final String endFrom,

			@RequestParam(value = "endUntil", required = false) final String endUntil) throws Exception {

		final ProjectQueryParams params = projectQueryParamsFactory.generateParams(request, startFrom, startUntil, endFrom, endUntil);

		response.setContentType("text/html");

		doProcess(response, params, null, eprintsTemplate, null, new ValueCleaner() {

			@Override

			public String clean(final String s) {

				return oneLiner(s);

			}

		});

	}

	private String oneLiner(final String s) {

		return StringUtils.isNotBlank(s) ? s.replaceAll("\\n", " ").trim() : "";

	}

	private void doProcess(

			final ServletResponse response,

			final ProjectQueryParams params,

			final String head, final Resource projectTemplate, final String tail,

			final ValueCleaner cleaner) throws IOException, SQLException {

		final StringTemplate st = new StringTemplate(IOUtils.toString(projectTemplate.getInputStream()));

		dao.streamProjects(obtainQuery(params), response.getOutputStream(), head, st, tail, cleaner);

	}

	@RequestMapping(value = "/openaire/export/project2tsv.do")

	void processTsv(final HttpServletRequest request, final HttpServletResponse response,

			@RequestParam(value = "funding", required = true) final String funding,

			@RequestParam(value = "article293", required = false) final Boolean article293) throws Exception {

		final String date = new SimpleDateFormat("yyyyMMdd").format(new Date());

		final String filename = "projects_" + funding + "_" + date + ".tsv";

		response.setContentType("text/tab-separated-values");

		response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + ".zip\"");

		final StringTemplate st = new StringTemplate(IOUtils.toString(projects2tsvQueryTemplate.getInputStream()));

		Funding requestedFunding = Funding.valueOf(funding.toUpperCase());

		String fundingPrefix = getFundingPrefix(requestedFunding, null);

		log.debug("Setting fundingprefix to " + fundingPrefix);

		st.setAttribute("fundingprefix", fundingPrefix);

		st.setAttribute("filters", expandFilters(article293));

		final ZipOutputStream out = new ZipOutputStream(new BufferedOutputStream(response.getOutputStream()));

		out.putNextEntry(new ZipEntry(filename));

		dao.streamProjectsTSV(st.toString(), out);

	}

	@RequestMapping(value = "/openaire/export/streamProjectDetails.do")

	void streamProjectDetails(final HttpServletResponse response,

			@RequestParam(value = "format", required = true) final String format,

			@RequestParam(value = "compress", required = false) final Boolean compress) throws IOException, SQLException {

		final String sql = IOUtils.toString(projectDetailsSql.getInputStream());

		if (compress != null && compress) {

			response.setHeader("Content-Encoding", "gzip");

		}

		switch (format) {

		case "csv":

			response.setContentType("text/csv");

			break;

		case "json":

			response.setContentType("text/plain");

			break;

		default: throw new IllegalArgumentException("unsupported format: " + format);

		}

		final OutputStream outputStream = getOutputStream(response.getOutputStream(), compress);

		dao.streamProjectDetails(sql, outputStream, format);

	}

	private OutputStream getOutputStream(final ServletOutputStream outputStream, final Boolean compress) throws IOException {

		if (compress != null && compress) {

			return new GZIPOutputStream(outputStream);

		}

		return outputStream;

	}

	@ExceptionHandler({Exception.class, Throwable.class})

	@ResponseStatus(value = HttpStatus.INTERNAL_SERVER_ERROR)

	public @ResponseBody ErrorMessage handleSqlException(final Exception e) {

		log.error(e.getMessage());

		return new ErrorMessage(e);

	}

	public class ErrorMessage {

		private final String message;

		private final String stacktrace;

		public ErrorMessage(final Exception e) {

			this(e.getMessage(), ExceptionUtils.getStackTrace(e));

		}

		public ErrorMessage(final String message, final String stacktrace) {

			this.message = message;

			this.stacktrace = stacktrace;

		}

		public String getMessage() {

			return this.message;

		}

		public String getStacktrace() {

			return this.stacktrace;

		}

	}

	private Map<String, String> expandFilters(final Boolean article293) {

		final Map<String, String> res = Maps.newHashMap();

		if (article293 != null) {

			res.put("ec_article29_3", String.valueOf(article293));

		}

		return res;

	}

	/**

	 * Creates the query on the fundingProgramme specified in the given parameters.

	 *

	 * @param params

	 *            request parameters

	 * @return the query string

	 * @throws IllegalArgumentException

	 *             if the funding program is not recognized

	 * @throws IOException

	 *             if there are problem loading the query temlate

	 * @throws IllegalArgumentException

	 *             if the funding program is not recognized

	 */

	protected String obtainQuery(final ProjectQueryParams params) throws IllegalArgumentException, IOException {

		String funding = params.getFundingProgramme();

		String suffix = params.getFundingPath();

		String fundingPrefix = getFundingPrefix(Funding.valueOf(funding.toUpperCase()), suffix);

		final StringTemplate st = new StringTemplate(IOUtils.toString(projectsFundingQueryTemplate.getInputStream()));

		st.setAttribute("fundingprefix", fundingPrefix);

		String theQuery = setDateParameters(st.toString(), params);

		log.debug("Generated query: " + theQuery);

		return theQuery;

	}

	private String getFundingPrefix(final Funding funding, final String suffix) throws IllegalArgumentException {

		switch (funding) {

		case FCT:

			if (StringUtils.isBlank(suffix)) return "fct_________::FCT";

			else return "fct_________::FCT::" + suffix;

		case WT:

			if (StringUtils.isBlank(suffix)) return "wt__________::WT";

			else return "wt__________::WT::" + suffix;

		case FP7:

			if (StringUtils.isBlank(suffix)) return "ec__________::EC::FP7";

			else return "ec__________::EC::FP7::" + suffix;

		case H2020:

			if (StringUtils.isBlank(suffix)) return "ec__________::EC::H2020";

			else return "ec__________::EC::H2020" + suffix;

		case NHMRC:

			if (StringUtils.isBlank(suffix)) return "nhmrc_______::NHMRC";

			else return "nhmrc_______::NHMRC::" + suffix;

		case ARC:

			if (StringUtils.isBlank(suffix)) return "arc_________::ARC";

			else return "arc_________::ARC::" + suffix;

		case SFI:

			if (StringUtils.isBlank(suffix)) return "sfi_________::SFI";

			else return "sfi_________::SFI::" + suffix;

		case MZOS:

			if (StringUtils.isBlank(suffix)) return "irb_hr______::MZOS";

			else return "irb_hr______::MZOS::" + suffix;

		case HRZZ:

			if (StringUtils.isBlank(suffix)) return "irb_hr______::HRZZ";

			else return "irb_hr______::HRZZ::" + suffix;

		case NWO:

			if (StringUtils.isBlank(suffix)) return "nwo_________::NWO";

			else return "nwo_________::NWO::" + suffix;

		case MESTD:

			if (StringUtils.isBlank(suffix)) return "mestd_______::MESTD";

			else return "mestd_______::MESTD::" + suffix;

		case FWF:

			if (StringUtils.isBlank(suffix)) return "fwf_________::FWF";

			else return "fwf_________::FWF::" + suffix;

		case SNSF:

			if (StringUtils.isBlank(suffix)) return "snsf________::SNSF";

			else return "snsf________::SNSF::" + suffix;

		case RCUK:

			if (StringUtils.isBlank(suffix)) return "rcuk________::RCUK";

			else return "rcuk________::RCUK::" + suffix;

		default:

			throw new IllegalArgumentException("Invalid funding " + funding + " (valid are: " + Arrays.asList(Funding.values()) + ") ");

		}

	}

	private String setDateParameters(final String query, final ProjectQueryParams params) {

		String queryWithDates = query;

		if (params.getStartFrom() != null) {

			queryWithDates += " AND startdate >= '" + params.getStartFrom() + "'";

		}

		if (params.getStartUntil() != null) {

			queryWithDates += " AND startdate <= '" + params.getStartUntil() + "'";

		}

		if (params.getEndFrom() != null) {

			queryWithDates += " AND enddate >= '" + params.getEndFrom() + "'";

		}

		if (params.getEndUntil() != null) {

			queryWithDates += " AND enddate <= '" + params.getEndUntil() + "'";

		}

		return queryWithDates;

	}

	public void setProjectsFundingQueryTemplate(final Resource projectsFundingQueryTemplate) {

		this.projectsFundingQueryTemplate = projectsFundingQueryTemplate;

	}

}

SELECT

	code                                                           AS "Grant Agreement Number",

	acronym                                                        AS "Project Acronym",

	title                                                          AS "Project Title",

	call_identifier                                                AS "Call ID",

	startdate                                                      AS "Start Date",

	enddate                                                        AS "End Date",

	ec_sc39                                                        AS "ec_sc39",

	oa_mandate_for_publications                                    AS "oa_mandate_for_publications",

	ec_article29_3                                                 AS "ec_article29_3",

	description                                                    AS "Discipline",

	legalname                                                      AS "Organization",

	countryclass                                                   AS "Country",

	role                                                           AS "Role",

	firstname                                                      AS "Person Name",

	secondnames                                                    AS "Person Second Names",

	email                                                          AS "Person Email"

FROM projects_tsv

WHERE fundingpathid like '$fundingprefix$%'$filters.keys:{k| AND $k$=$filters.(k)$}$

ORDER BY acronym

SELECT

	funder,

	jurisdiction,

	fundingpathid,

	acronym             AS "Project Acronym",

	title               AS "Project Title",

	code                AS "Grant Agreement Number",

	startdate           AS "Start Date",

	enddate             AS "End Date"

FROM projects_api

WHERE fundingpathid like '$fundingprefix$%'

SELECT * FROM project_details;

$p.acronym$ - $p.title$&#9;<li style='border-right: solid 50px #30FF30' >$p.code$ - $p.acronym$ - $p.title$<ul><li id="$p.listLabel$">$p.idnamespace$</li></ul></li>

<pair>

	<displayed-value>$p.code$ - $p.acronym$ - $p.title$</displayed-value>

	<stored-value>$p.idnamespace$</stored-value>

</pair>

<?xml version='1.0' encoding='UTF-8'?>

<form-value-pairs>

	<value-pairs value-pairs-name='$fundingProgramme$projects' dc-term='relation'>

 </value-pairs>

</form-value-pairs>

<beans xmlns="http://www.springframework.org/schema/beans"

       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"

       xmlns:context="http://www.springframework.org/schema/context"

       xmlns:util="http://www.springframework.org/schema/util"

       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd

        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd

        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

	<bean id="projectQueryParamsFactory" class="eu.dnetlib.openaire.exporter.ProjectQueryParamsFactory"/>

	<bean id="jdbcApiDao" class="eu.dnetlib.openaire.exporter.JdbcApiDao" />

	<bean id="apiDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close"

	      p:driverClassName="org.postgresql.Driver"

	      p:url="${openaire.exporter.jdbc.url}"

	      p:username="${openaire.exporter.jdbc.user}"

	      p:password="${openaire.exporter.jdbc.pwd}"

	      p:minIdle="${openaire.exporter.jdbc.minIdle}"

	      p:maxIdle="${openaire.exporter.jdbc.maxIdle}"/>

</beans>

openaire.exporter.dspace.template.head=classpath:/eu/dnetlib/openaire/exporter/st/projects_dspace_header.st

openaire.exporter.dspace.template.project=classpath:/eu/dnetlib/openaire/exporter/st/projects_dspace_project.st

openaire.exporter.dspace.template.tail=classpath:/eu/dnetlib/openaire/exporter/st/projects_dspace_tail.st

openaire.exporter.eprints.template=classpath:/eu/dnetlib/openaire/exporter/st/projects_eprints.st

openaire.exporter.projects2tsv.sql.template=classpath:/eu/dnetlib/openaire/exporter/sql/projects_tsv.sql.st

openaire.exporter.projects2tsv.fields = Grant Agreement Number, Project Acronym, Project Title, Call ID, Start Date, End Date, ec_sc39, oa_mandate_for_publications, ec_article29_3, Discipline, Organization, Country, Role, Person Name, Person Second Names, Person Email

openaire.exporter.projectsfundings.sql.template=classpath:/eu/dnetlib/openaire/exporter/sql/projects_fundings.sql.st

openaire.exporter.projectdetails.sql=classpath:/eu/dnetlib/openaire/exporter/sql/projects_details.sql

openaire.exporter.jdbc.url=jdbc:postgresql://localhost:5432/dnet_openaireplus

openaire.exporter.jdbc.user=dnetapi

openaire.exporter.jdbc.pwd=dnetPwd

openaire.exporter.jdbc.minIdle=1

openaire.exporter.jdbc.maxIdle=20

openaire.exporter.jdbc.maxrows=100

openaire.exporter.projectdetails.flushsize=1000

<?xml version="1.0" ?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

	<parent>

		<groupId>eu.dnetlib</groupId>

		<artifactId>dnet-parent</artifactId>

		<version>1.0.0</version>

		<relativePath />

	</parent>

	<modelVersion>4.0.0</modelVersion>

	<groupId>eu.dnetlib</groupId>

	<artifactId>cnr-openaire-exporter</artifactId>

	<packaging>jar</packaging>

	<version>6.1.4</version>

	<scm>

	  <developerConnection>scm:svn:https://svn.driver.research-infrastructures.eu/driver/dnet40/modules/cnr-openaire-exporter/tags/cnr-openaire-exporter-6.1.4</developerConnection>

	</scm>

	<dependencies>

		<dependency>

			<groupId>eu.dnetlib</groupId>

			<artifactId>cnr-service-common</artifactId>

			<version>[2.1.2,3.0.0)</version>

		</dependency>

		<dependency>

			<groupId>eu.dnetlib</groupId>

			<artifactId>dnet-openaireplus-mapping-utils</artifactId>

			<version>[5.0.0,6.0.0)</version>

			<exclusions>

				<exclusion>

					<groupId>com.sun.jersey</groupId>

					<artifactId>jersey-server</artifactId>

				</exclusion>

			</exclusions>

		</dependency>

		<dependency>

			<groupId>org.postgresql</groupId>

			<artifactId>jdbc4driver</artifactId>

			<version>9.3-1104</version>

		</dependency>

		<dependency>

			<groupId>org.apache.commons</groupId>

			<artifactId>commons-dbcp2</artifactId>

			<version>2.1</version>

		</dependency>

		<dependency>

			<groupId>com.google.code.gson</groupId>

			<artifactId>gson</artifactId>

			<version>${google.gson.version}</version>

		</dependency>

		<dependency>

			<groupId>javax.servlet</groupId>

			<artifactId>javax.servlet-api</artifactId>

			<version>${javax.servlet.version}</version>

			<scope>provided</scope>

		</dependency>

		<dependency>

			<groupId>junit</groupId>

			<artifactId>junit</artifactId>

			<version>${junit.version}</version>

			<scope>test</scope>

		</dependency>

	</dependencies>

</project>