D-Net

<?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>5.1.8</version>

	<scm>

	  <developerConnection>scm:svn:https://svn.driver.research-infrastructures.eu/driver/dnet40/modules/cnr-openaire-exporter/tags/cnr-openaire-exporter-5.1.8</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>cnr-resultset-client</artifactId>

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

		</dependency>

		<dependency>

			<groupId>eu.dnetlib</groupId>

			<artifactId>cnr-enabling-database-api</artifactId>

			<version>[2.0.0,3.0.0)</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>

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 p.startdate >= '2015-05-04'";

		assertEquals(q, res);

	}

}

package eu.dnetlib.openaire.exporter;

import java.io.IOException;

import java.io.StringWriter;

import eu.dnetlib.miscutils.functional.xml.ApplyXslt;

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;

public class DSpaceTest {

	private Resource wtProject = new ClassPathResource("eu/dnetlib/openaire/exporter/WT-project.xml");

	private Resource fctProject = new ClassPathResource("eu/dnetlib/openaire/exporter/FCT-project.xml");

	private Resource fp7Project = new ClassPathResource("eu/dnetlib/openaire/exporter/FP7-project.xml");

	private Resource sfiProject = new ClassPathResource("eu/dnetlib/openaire/exporter/SFI-project.xml");

	private Resource msesProject = new ClassPathResource("eu/dnetlib/openaire/exporter/MSES-project.xml");

	private Resource dspaceXslt;

	@Before

	public void setUp() throws Exception {

		dspaceXslt = new ClassPathResource("xslt/projects_dspace.xslt", getClass());

	}

	@Test

	public void testWTdspace() throws IOException {

		ApplyXslt applyXslt = new ApplyXslt(dspaceXslt);

		StringWriter w = new StringWriter();

		IOUtils.copy(wtProject.getInputStream(), w);

		String result = applyXslt.evaluate(w.toString());

		System.out.println("<?xml version='1.0' encoding='UTF-8'?>\n\n" + "<form-value-pairs>\n"

				+ "	<value-pairs value-pairs-name='WTprojects' dc-term='relation'>\n");

		System.out.println(result);

		System.out.println("	</value-pairs>\n" + "</form-value-pairs>\n");

	}

	@Test

	public void testFCTdspace() throws IOException {

		doTest(fctProject);

	}

	@Test

	public void testFP7dspace() throws IOException {

		doTest(fp7Project);

	}

	@Test

	public void testSFIdspace() throws IOException {

		doTest(sfiProject);

	}

	@Test

	public void testMSESdspace() throws IOException {

		doTest(msesProject);

	}

	private void doTest(Resource resource) throws IOException {

		ApplyXslt applyXslt = new ApplyXslt(dspaceXslt);

		StringWriter w = new StringWriter();

		IOUtils.copy(resource.getInputStream(), w);

		String result = applyXslt.evaluate(w.toString());

		System.out.println(result);

	}

}

package eu.dnetlib.openaire.exporter;

import java.io.IOException;

import java.io.StringWriter;

import eu.dnetlib.miscutils.functional.xml.ApplyXslt;

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;

public class EPrintsTest {

	private Resource wtProject = new ClassPathResource("eu/dnetlib/openaire/exporter/WT-project.xml");

	private Resource fctProject = new ClassPathResource("eu/dnetlib/openaire/exporter/FCT-project.xml");

	private Resource fp7Project = new ClassPathResource("eu/dnetlib/openaire/exporter/FP7-project.xml");

	private Resource sfiProject = new ClassPathResource("eu/dnetlib/openaire/exporter/SFI-project.xml");

	private Resource msesProject = new ClassPathResource("eu/dnetlib/openaire/exporter/MSES-project.xml");

	private Resource ePrintsXslt;

	@Before

	public void setUp() throws Exception {

		ePrintsXslt = new ClassPathResource("xslt/projects_eprints.xslt", getClass());

	}

	@Test

	public void testWTePrints() throws IOException {

		doTest(wtProject);

	}

	@Test

	public void testFCTePrints() throws IOException {

		doTest(fctProject);

	}

	@Test

	public void testFP7ePrints() throws IOException {

		doTest(fp7Project);

	}

	@Test

	public void testSFIePrints() throws IOException {

		doTest(sfiProject);

	}

	@Test

	public void testMSESePrints() throws IOException {

		doTest(msesProject);

	}

	private void assertTab(final String str) {

		System.out.println(str);

		String[] splitOnTab = str.split("\t");

		org.junit.Assert.assertTrue(splitOnTab.length > 1);

		System.out.println(splitOnTab[0]);

		System.out.println("According to the Java split function, now there is a tab, followed by:");

		System.out.println(splitOnTab[1]);

	}

	private void doTest(Resource resource) throws IOException {

		ApplyXslt applyXslt = new ApplyXslt(ePrintsXslt);

		StringWriter w = new StringWriter();

		IOUtils.copy(resource.getInputStream(), w);

		String res = applyXslt.evaluate(w.toString());

		assertTab(res);

	}

}

<?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>

<?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">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> 

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

SELECT

	org.legalshortname as funder, 

	fp.jurisdiction as jurisdiction, 

	p.acronym as acronym, 

	p.title as title, 

	p.code as grant_agreement_number,

	p.startdate as start_date,

	p.enddate as end_date,

	fp.id as fundingpathid

FROM projects p 

	left outer join project_fundingpath pfp  on (p.id = pfp.project) 

	left outer join fundingpaths fp on (pfp.funding=fp.id)

	left outer join organizations org on (org.id = fp.funder)

WHERE fp.id like '$fundingprefix$%'

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;

import java.io.IOException;

import java.io.OutputStream;

import java.io.StringReader;

import java.text.SimpleDateFormat;

import java.util.Arrays;

import java.util.Date;

import java.util.List;

import java.util.Map;

import javax.servlet.ServletOutputStream;

import javax.servlet.ServletResponse;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.xml.ws.wsaddressing.W3CEndpointReference;

import com.google.common.base.Joiner;

import com.google.common.base.Splitter;

import com.google.common.collect.Lists;

import com.google.common.collect.Maps;

import eu.dnetlib.enabling.database.rmi.DatabaseException;

import eu.dnetlib.enabling.database.rmi.DatabaseService;

import eu.dnetlib.enabling.locators.UniqueServiceLocator;

import eu.dnetlib.enabling.resultset.client.IterableResultSetClient;

import eu.dnetlib.enabling.resultset.client.ResultSetClientFactory;

import eu.dnetlib.miscutils.collections.MappedCollection;

import eu.dnetlib.miscutils.functional.xml.ApplyXslt;

import org.antlr.stringtemplate.StringTemplate;

import org.apache.commons.io.IOUtils;

import org.apache.commons.lang.StringUtils;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

import org.dom4j.Document;

import org.dom4j.io.SAXReader;

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

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

import org.springframework.core.io.Resource;

import org.springframework.stereotype.Controller;

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

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

@Controller

public class ProjectsController {

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

	@javax.annotation.Resource

	private UniqueServiceLocator serviceLocator;

	@Value("${dnet.openaire.db.name}")

	private String dbName;

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

	private Resource dspaceXslt;

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

	private Resource eprintsXslt;

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

	private Resource projectsFundingQueryTemplate;

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

	private Resource projects2tsvQueryTemplate;

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

	private String tsvFields;

	@Autowired

	private ResultSetClientFactory resultSetClientFactory;

	@Autowired

	private ProjectQueryParamsFactory projectQueryParamsFactory;

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

		response.setContentType("text/xml");

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

		ServletOutputStream out = response.getOutputStream();

		String head = "<?xml version='1.0' encoding='UTF-8'?>\n\n" + "<form-value-pairs>\n" + "	<value-pairs value-pairs-name='" + params.getFundingProgramme()

				+ "projects' dc-term='relation'>\n";

		String tail = "	</value-pairs>\n" + "</form-value-pairs>\n";

		IOUtils.copy(new StringReader(head), out);

		emit(out, dspaceXslt, obtainQuery(params));

		IOUtils.copy(new StringReader(tail), out);

	}

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

		response.setContentType("text/html");

		emit(response.getOutputStream(), eprintsXslt, obtainQuery(projectQueryParamsFactory.generateParams(request, startFrom, startUntil, endFrom, endUntil)));

	}

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

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

		response.setHeader("Content-Disposition", "attachment; filename=\"projects_" + funding + "_" + date + ".tsv\"");

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

		emitAsTsv(response.getOutputStream(), st.toString());

	}

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

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

		if (article293 != null) {

			res.put("pr.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 MSES:

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

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

		case CSF:

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

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

		case NWO:

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

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

		default:

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

		}

	}

	void emit(final OutputStream out, final Resource xslt, final String query) throws IOException {

		W3CEndpointReference epr = null;

		try {

			epr = serviceLocator.getService(DatabaseService.class).searchSQL(dbName, query);

			IterableResultSetClient iter = resultSetClientFactory.getClient(epr);

			for (String s : new MappedCollection<String, String>(iter, new ApplyXslt(xslt))) {

				out.write(s.getBytes());

		}

		} catch (DatabaseException e) {

			throw new IOException(e);

		}

	}

	void emitAsTsv(final OutputStream out, final String query) throws Exception {

		W3CEndpointReference epr = serviceLocator.getService(DatabaseService.class).searchSQL(dbName, query);

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

		writeCSVLine(out, fields);

		final SAXReader reader = new SAXReader();

		for (String s : resultSetClientFactory.getClient(epr)) {

			final Document doc = reader.read(new StringReader(s));

			final List<String> list = Lists.newArrayList();

			for (String f : fields) {

				list.add(doc.valueOf("//FIELD[@name='" + f + "']").replaceAll("\\n|\\t", " ").replaceAll("\\s+", " ").trim());

			}

			writeCSVLine(out, list);

		}

		out.flush();

	}

	private void writeCSVLine(final OutputStream out, final List<String> list) throws Exception {

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

		out.write('\n');

	}

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

		String queryWithDates = query;

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

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

		}

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

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

		}

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

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

		}

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

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

		}

		return queryWithDates;

	}

	public String getDbName() {

		return dbName;

	}

	public void setDbName(final String dbName) {

		this.dbName = dbName;

	}

	public Resource getDspaceXslt() {

		return dspaceXslt;

	}

	public void setDspaceXslt(final Resource dspaceXslt) {

		this.dspaceXslt = dspaceXslt;

	}

	public Resource getEprintsXslt() {

		return eprintsXslt;

	}

	public void setEprintsXslt(final Resource eprintsXslt) {

		this.eprintsXslt = eprintsXslt;

	}

	public ProjectQueryParamsFactory getProjectQueryParamsFactory() {

		return projectQueryParamsFactory;

	}

	public void setProjectQueryParamsFactory(final ProjectQueryParamsFactory projectQueryParamsFactory) {

		this.projectQueryParamsFactory = projectQueryParamsFactory;

	}

	public Resource getProjectsFundingQueryTemplate() {

		return projectsFundingQueryTemplate;

	}

	public void setProjectsFundingQueryTemplate(final Resource projectsFundingQueryTemplate) {

		this.projectsFundingQueryTemplate = projectsFundingQueryTemplate;

	}

	public Resource getProjects2tsvQueryTemplate() {

		return projects2tsvQueryTemplate;

	}

	public void setProjects2tsvQueryTemplate(final Resource projects2tsvQueryTemplate) {

		this.projects2tsvQueryTemplate = projects2tsvQueryTemplate;

	}

	private enum Funding {

		FP7, WT, FCT, H2020, NHMRC, ARC, SFI, MSES, CSF, NWO

	}

}

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;

	}

}

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

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 

	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" exclude-result-prefixes="xsl xsi">

<!-- DO NOT REINDENT THIS FILE: IT IS IMPORTANT THAT EACH PROJECT IS ON ONE SINGLE LINE -->

	<xsl:output omit-xml-declaration="yes" standalone="omit" indent="no"/>

	<xsl:template match="/">

		<xsl:variable name="jurisdiction" select="//FIELD[@name='jurisdiction']"/>

		<xsl:variable name="unescapedId" select="//FIELD[@name='grant_agreement_number']"/>

		<xsl:variable name="id" select="replace($unescapedId, '/', '%2F')"/>

		<xsl:variable name="title" select="//FIELD[@name='title']" />

		<xsl:variable name="acronym" select="//FIELD[@name='acronym']" />

		<xsl:variable name="funder" select="//FIELD[@name='funder']" />

		<xsl:variable name="fundingProgramme">

			<xsl:value-of select="replace(tokenize(//FIELD[@name='fundingpathid'],'::')[3], '/', '%2F')"/>

		</xsl:variable>

		<xsl:variable name="idnamespace">

		<!-- info:eu-repo/grantAgreement/Funder/FundingProgram/ProjectID /[Jurisdiction]/[ProjectName]/[ProjectAcronym]  -->

			<xsl:choose>

				<xsl:when test="$jurisdiction != ''"><xsl:value-of select="concat('info:eu-repo/grantAgreement/', $funder, '/', $fundingProgramme, '/', $id, '/', $jurisdiction )" /></xsl:when>

				<xsl:otherwise><xsl:value-of select="concat('info:eu-repo/grantAgreement/', $funder, '/', $id)" /></xsl:otherwise>

			</xsl:choose>

		</xsl:variable>

		<xsl:variable name="listLabel">

		<xsl:choose>

			<xsl:when test="$fundingProgramme = 'FP7'">for:value:component:_fp7_project_id</xsl:when>

			<xsl:when test="$fundingProgramme = 'H2020'">for:value:component:_h2020_project_id</xsl:when>

			<xsl:when test="$funder = 'WT'">for:value:component:_wt_project_id</xsl:when>

			<xsl:when test="$funder = 'FCT'">for:value:component:_fct_project_id</xsl:when>

			<xsl:when test="$funder = 'NHMRC'">for:value:component:_nhmrc_project_id</xsl:when>

			<xsl:when test="$funder = 'ARC'">for:value:component:_arc_project_id</xsl:when>

			<xsl:when test="$funder = 'SFI'">for:value:component:_sfi_project_id</xsl:when>

			<xsl:when test="$funder = 'MSES'">for:value:component:_mses_project_id</xsl:when>

			<xsl:when test="$funder = 'CSF'">for:value:component:_csf_project_id</xsl:when>

			<xsl:when test="$funder = 'NWO'">for:value:component:_nwo_project_id</xsl:when>

			<xsl:otherwise>info:eu-repo/grantAgreement/</xsl:otherwise>

			</xsl:choose>

		</xsl:variable>

		<xsl:value-of select="concat($acronym, ' - ', $title,  '&#9;')" /><li style='border-right: solid 50px #30FF30' ><xsl:value-of select="concat($unescapedId, ' - ', $acronym, ' - ', $title)" /><ul><li id="{$listLabel}"><xsl:value-of select="$idnamespace" /></li></ul></li><xsl:text>

</xsl:text>

	</xsl:template>

</xsl:stylesheet>

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

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 

	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" exclude-result-prefixes="xsl xsi">

	<xsl:output omit-xml-declaration="yes" standalone="omit" indent="yes"/>

	<xsl:template match="/">

		<xsl:variable name="jurisdiction" select="//FIELD[@name='jurisdiction']"/>

		<xsl:variable name="unescapedId" select="//FIELD[@name='grant_agreement_number']"/>

		<xsl:variable name="id" select="replace($unescapedId, '/', '%2F')"/>

		<xsl:variable name="title" select="//FIELD[@name='title']" />

		<xsl:variable name="acronym" select="//FIELD[@name='acronym']" />

		<xsl:variable name="funder" select="//FIELD[@name='funder']" />

		<xsl:variable name="fundingProgramme">

			 <xsl:value-of select="replace(tokenize(//FIELD[@name='fundingpathid'],'::')[3], '/', '%2F')"/>

		</xsl:variable>

		<xsl:variable name="idnamespace">

		<!-- info:eu-repo/grantAgreement/Funder/FundingProgram/ProjectID /[Jurisdiction]/[ProjectName]/[ProjectAcronym]  -->

		<xsl:choose>

				<xsl:when test="$jurisdiction != ''"><xsl:value-of select="concat('info:eu-repo/grantAgreement/', $funder, '/', $fundingProgramme, '/', $id, '/', $jurisdiction)" /></xsl:when>

				<xsl:otherwise><xsl:value-of select="concat('info:eu-repo/grantAgreement/', $funder, '/', $id)" /></xsl:otherwise>

			</xsl:choose>

		</xsl:variable>

		<pair>

			<displayed-value>

				<xsl:value-of select="concat($unescapedId, ' - ', $acronym, ' - ', $title)"/>

			</displayed-value>

			<stored-value><xsl:value-of select="$idnamespace" /></stored-value>

		</pair>

	</xsl:template>

</xsl:stylesheet>

select

	pr.code                                                           AS "Grant Agreement Number", 

	pr.acronym                                                        AS "Project Acronym",  

	pr.title                                                          AS "Project Title", 

	pr.call_identifier                                                AS "Call ID", 

	pr.startdate                                                      AS "Start Date",

	pr.enddate                                                        AS "End Date",

	pr.ec_sc39                                                        AS "ec_sc39",

	pr.oa_mandate_for_publications                                    AS "oa_mandate_for_publications",

	pr.ec_article29_3                                                 AS "ec_article29_3",

	f.description                                                     AS "Discipline",

	org.legalname                                                     AS "Organization",

	org.countryclass                                                  AS "Country",

	CASE WHEN po.participantnumber = 1 THEN 'coordinator' ELSE '' END AS "Role",

	pe.firstname                                                      AS "Person Name", 

	pe.secondnames                                                    AS "Person Second Names",  

	pe.email                                                          AS "Person Email"

from projects pr

	left outer join project_organization po on (pr.id = po.project)

	left outer join persons pe on (po.contactperson=pe.id)

	left outer join organizations org on (po.resporganization = org.id)

	left outer join project_fundingpath pf on (pr.id = pf.project)

	left outer join fundingpaths f on (pf.funding = f.id)

where f.id like '$fundingprefix$%'$filters.keys:{k| AND $k$=$filters.(k)$}$

order by pr.acronym

SELECT

	org.legalshortname as funder, 

	fp.jurisdiction as jurisdiction, 

	p.acronym as acronym, 

	p.title as title, 

	p.code as grant_agreement_number,

	p.startdate as start_date,

	p.enddate as end_date,

	fp.id as fundingpathid

FROM projects p 

	left outer join project_fundingpath pfp  on (p.id = pfp.project) 

	left outer join fundingpaths fp on (pfp.funding=fp.id)

	left outer join organizations org on (org.id = fp.funder)

WHERE fp.id like '$fundingprefix$%'

openaire.exporter.dspace.xslt=classpath:/eu/dnetlib/openaire/exporter/xslt/projects_dspace.xslt

openaire.exporter.eprints.xslt=classpath:/eu/dnetlib/openaire/exporter/xslt/projects_eprints.xslt

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

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

</beans>

{"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": "alessia.bardi@isti.cnr.it", 

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