D-Net

package eu.dnetlib.openaire.exporter;

import static org.junit.Assert.assertEquals;

import static org.junit.Assert.assertFalse;

import org.junit.Before;

import org.junit.Test;

public class ProjectsControllerTest {

	private ProjectsController controller;

	private ProjectQueryParams params;

	private String expectedFP7Query = "select 'EC' as funder, 'EU' as jurisdiction, p.acronym as acronym, " + "p.title as title, "

			+ "regexp_replace(p.id, '^corda_______::'  , '') as grant_agreement_number, " + "p.startdate as start_date, " + "p.enddate as end_date, "

			+ "f1.name as subdivision, " + "f2.name as specificprogramme, " + "f3.name as fundingprogramme " + "from projects p "

			+ "left outer join project_funding pf on (p.id = pf.project) " + "left outer join funding_funding ff1 on (pf.funding = ff1.funding1) "

			+ "left outer join funding_funding ff2 on (ff1.funding2 = ff2.funding1) " + "left outer join fundings f1 on (f1.id = ff1.funding1) "

			+ "left outer join fundings f2 on (f2.id = ff2.funding1) " + "left outer join fundings f3 on (f3.id = ff2.funding2) "

			+ "where f3.id = 'corda_______::FP7' ";

	private String expectedWTQuery = "select 'WT' as funder, p.acronym as acronym, p.title as title, regexp_replace(p.id, '^welcometrust::'  , '') as grant_agreement_number, "

			+ "p.startdate as start_date, p.enddate as end_date, f1.name as specificprogramme, f2.name as fundingprogramme "

			+ "from projects p left outer join project_funding pf on (p.id = pf.project) "

			+ "left outer join funding_funding ff1 on (pf.funding = ff1.funding1) "

			+ "left outer join fundings f1 on (f1.id = ff1.funding1)  "

			+ "left outer join fundings f2 on (f2.id = ff1.funding2) " + "where f2.id = 'wt::WT' ";

	private String expectedFCTQuery = "select 'PT' as jurisdiction, p.acronym as acronym, p.title as title, regexp_replace(p.id, '^fct_________::'  , '') as grant_agreement_number, "

			+ "p.startdate as start_date, p.enddate as end_date, f1.name as fundingprogramme, f2.name as funder "

			+ "from projects p left outer join project_funding pf on (p.id = pf.project) "

			+ "left outer join funding_funding ff1 on (pf.funding = ff1.funding1) "

			+ "left outer join fundings f1 on (f1.id = ff1.funding1)  "

			+ "left outer join fundings f2 on (f2.id = ff1.funding2) where f2.id = 'fct_________::FCT' ";

	@Before

	public void setup() {

		controller = new ProjectsController();

		params = new ProjectQueryParams();

	}

	@Test

	public void testObtainFP7Query() {

		params.setFundingProgramme("FP7");

		String res = controller.obtainFP7Query(params);

		System.out.println(res);

		assertEquals(expectedFP7Query, res);

	}

	@Test

	public void testObtainWellcomeTrustQuery() {

		params.setFundingProgramme("WT");

		String res = controller.obtainWellcomeTrustQuery(params);

		System.out.println(res);

		assertEquals(expectedWTQuery, res);

	}

	@Test

	public void testObtainFCTQuery() {

		params.setFundingProgramme("FCT");

		String res = controller.obtainFCTQuery(params);

		System.out.println(res);

		assertEquals(expectedFCTQuery, res);

	}

	@Test

	public void testQueryWithDateParams() {

		params.setFundingProgramme("WT");

		params.setStartFrom("2015");

		String res = controller.obtainWellcomeTrustQuery(params);

		System.out.println(res);

		assertFalse(expectedWTQuery.equals(res));

	}

}

package eu.dnetlib.openaire.exporter;

import java.io.IOException;

import java.io.StringWriter;

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 eu.dnetlib.miscutils.functional.xml.ApplyXslt;

public class EPrintsTest {

	private String inputXML = "<projects><project><FIELD name=\"jurisdiction\">JUR</FIELD><FIELD name=\"funder\">funderID</FIELD><FIELD name=\"grant_agreement_number\">GRANTID1</FIELD><FIELD name=\"title\">Project title 1</FIELD><FIELD name=\"acronym\">ACRO1</FIELD></project><project><FIELD name=\"grant_agreement_number\">GRANTID2</FIELD><FIELD name=\"title\">Project title 2</FIELD><FIELD name=\"funder\">funderID2</FIELD><FIELD name=\"acronym\">ACRO 2</FIELD></project></projects>";

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

	@Before

	public void setUp() throws Exception {

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

	}

	@Test

	public void test() {

		ApplyXslt applyXslt = new ApplyXslt(ePrintsXslt);

		String result = applyXslt.evaluate(inputXML);

		System.out.println(result);

		assertTab(result);

	}

	@Test

	public void testWTePrints() throws IOException {

		ApplyXslt applyXslt = new ApplyXslt(ePrintsXslt);

		StringWriter w = new StringWriter();

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

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

		assertTab(res);

	}

	@Test

	public void testFCTePrints() throws IOException {

		ApplyXslt applyXslt = new ApplyXslt(ePrintsXslt);

		StringWriter w = new StringWriter();

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

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

		assertTab(res);

	}

	@Test

	public void testFP7ePrints() throws IOException {

		ApplyXslt applyXslt = new ApplyXslt(ePrintsXslt);

		StringWriter w = new StringWriter();

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

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

		assertTab(res);

	}

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

	}

}

package eu.dnetlib.openaire.exporter;

import java.io.IOException;

import java.io.StringWriter;

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 eu.dnetlib.miscutils.functional.xml.ApplyXslt;

public class DSpaceTest {

	private String inputXML = "<projects><project><FIELD name=\"grant_agreement_number\">GRANTID1</FIELD><FIELD name=\"title\">Project title 1</FIELD><FIELD name=\"acronym\">ACRO1</FIELD></project><project><FIELD name=\"grant_agreement_number\">GRANTID2</FIELD><FIELD name=\"title\">Project title 2</FIELD><FIELD name=\"acronym\">ACRO 2</FIELD></project></projects>";

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

	@Before

	public void setUp() throws Exception {

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

	}

	@Test

	public void test() {

		ApplyXslt applyXslt = new ApplyXslt(dspaceXslt);

		String result = applyXslt.evaluate(inputXML);

		System.out.println(result);

	}

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

		ApplyXslt applyXslt = new ApplyXslt(dspaceXslt);

		StringWriter w = new StringWriter();

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

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

		System.out.println(result);

	}

	@Test

	public void testFP7dspace() throws IOException {

		ApplyXslt applyXslt = new ApplyXslt(dspaceXslt);

		StringWriter w = new StringWriter();

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

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

		System.out.println(result);

	}

}

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

<ROW>

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

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

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

	<FIELD name="title">Neural Network Controllers for Pneumatic Actuators</FIELD>

	<FIELD name="fundingprogramme">POCI</FIELD>

	<FIELD name="acronym">POCTI/EME/32639/2000</FIELD>

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

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

</ROW> 

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

<ROW>

	<FIELD name="subdivision">PEOPLE</FIELD>

	<FIELD name="funder">EC</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="fundingprogramme">FP7</FIELD>

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

	<FIELD name="specificprogramme">SP3</FIELD>

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

	<FIELD name="jurisdiction">EU</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="fundingprogramme">WT</FIELD>

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

	<FIELD name="specificprogramme">Technology Transfer Division</FIELD>

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

</ROW> 

package eu.dnetlib.openaire.exporter;

import java.io.IOException;

import java.io.OutputStream;

import java.io.StringReader;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.List;

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

import com.google.common.base.Joiner;

import com.google.common.base.Splitter;

import com.google.common.collect.Lists;

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;

@Controller

public class ProjectsController {

	private enum Funder {

		FP7, WT, FCT

	}

	@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.projects2tsv.sql.template}")

	private Resource projects2tsvQueryTemplate;

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

	private String tsvFields;

	@Autowired

	private ResultSetClientFactory resultSetClientFactory;

	@Autowired

	private ProjectQueryParamsFactory projectQueryParamsFactory;

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

	@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 processEprints(final HttpServletRequest request,

			final HttpServletResponse response,

			@RequestParam(value = "funding", required = true) final String funding) 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()));

		if (funding.equalsIgnoreCase("wt")) {

			st.setAttribute("fundingprefix", "welcometrust::");

		} else if (funding.equalsIgnoreCase("fct")) {

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

		} else if (funding.equalsIgnoreCase("h2020")) {

			st.setAttribute("fundingprefix", "corda_______::H2020::");

		} else if (funding.equalsIgnoreCase("fp7")) {

			st.setAttribute("fundingprefix", "corda_______::FP7::");

		} else throw new Exception("Invalid funding " + funding + " (valid are: fp7, h2020, wt, fct)");

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

	}

	/**

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

	 * 

	 * @param params

	 *            request parameters

	 * @return the query string

	 * @throws IllegalArgumentException

	 *             if the funding programme is not recognized

	 */

	private String obtainQuery(final ProjectQueryParams params) throws IllegalArgumentException {

		String funder = params.getFundingProgramme();

		switch (Funder.valueOf(funder)) {

		case FP7:

			return obtainFP7Query(params);

		case WT:

			return obtainWellcomeTrustQuery(params);

		case FCT:

			return obtainFCTQuery(params);

		default:

			throw new IllegalArgumentException("Invalid Funder " + funder);

		}

	}

	/**

	 * Creates the query on FP7

	 * 

	 * @param params

	 * @return the query string

	 */

	protected String obtainFP7Query(final ProjectQueryParams params) {

		String query = "select 'EC' as funder, 'EU' as jurisdiction, p.acronym as acronym, " + "p.title as title, "

				+ "regexp_replace(p.id, '^corda_______::'  , '') as grant_agreement_number, " + "p.startdate as start_date, " + "p.enddate as end_date, "

				+ "f1.name as subdivision, " + "f2.name as specificprogramme, " + "f3.name as fundingprogramme " + "from projects p "

				+ "left outer join project_funding pf on (p.id = pf.project) " + "left outer join funding_funding ff1 on (pf.funding = ff1.funding1) "

				+ "left outer join funding_funding ff2 on (ff1.funding2 = ff2.funding1) " + "left outer join fundings f1 on (f1.id = ff1.funding1) "

				+ "left outer join fundings f2 on (f2.id = ff2.funding1) " + "left outer join fundings f3 on (f3.id = ff2.funding2) "

				+ "where f3.id = 'corda_______::FP7' ";

		if (StringUtils.isNotBlank(params.getSubdivision())) {

			query += "AND f1.name = '" + params.getSubdivision() + "'";

		}

		if (StringUtils.isNotBlank(params.getSpecificProgramme())) {

			query += "AND f2.name = '" + params.getSpecificProgramme() + "'";

		}

		query = setDateParameters(query, params);

		log.info("Executing query: " + query);

		return query;

	}

	/**

	 * Creates the query on WT

	 * 

	 * @param params

	 * @return the query string

	 */

	protected String obtainWellcomeTrustQuery(final ProjectQueryParams params) {

		String query = "select 'WT' as funder, p.acronym as acronym, p.title as title, regexp_replace(p.id, '^welcometrust::'  , '') as grant_agreement_number, "

				+ "p.startdate as start_date, p.enddate as end_date, f1.name as specificprogramme, f2.name as fundingprogramme "

				+ "from projects p left outer join project_funding pf on (p.id = pf.project) "

				+ "left outer join funding_funding ff1 on (pf.funding = ff1.funding1) "

				+ "left outer join fundings f1 on (f1.id = ff1.funding1)  "

				+ "left outer join fundings f2 on (f2.id = ff1.funding2) " + "where f2.id = 'wt::WT' ";

		if (StringUtils.isNotBlank(params.getSpecificProgramme())) {

			query += "AND f1.name = '" + params.getSpecificProgramme() + "'";

		}

		query = setDateParameters(query, params);

		log.info("Executing query: " + query);

		return query;

	}

	/**

	 * Creates the query on FCT (fct_________::FCT)

	 * 

	 * @param params

	 */

	protected String obtainFCTQuery(final ProjectQueryParams params) {

		String query = "select 'PT' as jurisdiction, p.acronym as acronym, p.title as title, regexp_replace(p.id, '^fct_________::'  , '') as grant_agreement_number, "

				+ "p.startdate as start_date, p.enddate as end_date, f1.name as fundingprogramme, f2.name as funder "

				+ "from projects p left outer join project_funding pf on (p.id = pf.project) "

				+ "left outer join funding_funding ff1 on (pf.funding = ff1.funding1) "

				+ "left outer join fundings f1 on (f1.id = ff1.funding1)  "

				+ "left outer join fundings f2 on (f2.id = ff1.funding2) where f2.id = 'fct_________::FCT' ";

		if (StringUtils.isNotBlank(params.getSpecificProgramme())) {

			query += "AND f1.name = '" + params.getSpecificProgramme() + "'";

		}

		query = setDateParameters(query, params);

		log.info("Executing query: " + query);

		return query;

	}

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

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

		}

	}

	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;

	}

}

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;

	private String fundingStream = null;

	private String scientficArea = 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(String fundingProgramme) {

		this.fundingProgramme = verifyParam(fundingProgramme);

	}

	public String getSpecificProgramme() {

		return fundingStream;

	}

	public void setSpecificProgramme(String specificProgramme) {

		this.fundingStream = verifyParam(specificProgramme);

	}

	public String getSubdivision() {

		return scientficArea;

	}

	public void setSubdivision(String subdivision) {

		this.scientficArea = verifyParam(subdivision);

	}

	public String getStartFrom() {

		return startFrom;

	}

	public void setStartFrom(String startFrom) {

		this.startFrom = verifyParam(startFrom);

	}

	public String getStartUntil() {

		return startUntil;

	}

	public void setStartUntil(String startUntil) {

		this.startUntil = verifyParam(startUntil);

	}

	public String getEndFrom() {

		return endFrom;

	}

	public void setEndFrom(String endFrom) {

		this.endFrom = verifyParam(endFrom);

	}

	public String getEndUntil() {

		return endUntil;

	}

	public void setEndUntil(String endUntil) {

		this.endUntil = verifyParam(endUntil);

	}

	private String verifyParam(String p) {

		Pattern pattern = Pattern.compile("(\\w|\\-){1,32}");

		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;

	}

}

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

		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="1.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="id" select="//FIELD[@name='grant_agreement_number']" />

		<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" select="//FIELD[@name='fundingprogramme']" />

		<xsl:variable name="idnamespace">

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

			<xsl:choose>

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

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

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

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

			</xsl:choose>

		</xsl:variable>

		<pair>

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

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

		</pair>

	</xsl:template>

</xsl:stylesheet>

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

<xsl:stylesheet version="1.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="id" select="//FIELD[@name='grant_agreement_number']" />

		<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" select="//FIELD[@name='fundingprogramme']" />

		<xsl:variable name="idnamespace">

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

			<xsl:choose>

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

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

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

				<xsl:otherwise>info:eu-repo/grantAgreement/</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="$funder = 'WT'">for:value:component:_wt_project_id</xsl:when>

				<xsl:when test="$funder = 'FCT'">for:value:component:_fct_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($id, ' - ', $acronym, ' - ', $title)" /><ul><li id="{$listLabel}"><xsl:value-of select="$idnamespace" /></li></ul></li><xsl:text> 

</xsl:text>

	</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.enddate                                                        AS "Start Date",

	pr.startdate                                                      AS "End Date", 

	pr.ec_sc39                                                        AS "ec_sc39",

	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_funding pf on (pr.id = pf.project)

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

where f.id like '$fundingprefix$%'

order by pr.acronym

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, Discipline, Organization, Country, Role, Person Name, Person Second Names, Person Email

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

<?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>4.1.3-SNAPSHOT</version>

	<scm>

	  <developerConnection>scm:svn:https://svn.driver.research-infrastructures.eu/driver/dnet40/modules/cnr-openaire-exporter/trunk</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>[1.0.0,2.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>

{"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"}