Project

General

Profile

« Previous | Next » 

Revision 35534

branching version that works with the funding tables of the db. trunk will use the new tables with fundingpaths

View differences:

modules/cnr-openaire-exporter/branches/4.x.x/src/test/java/eu/dnetlib/openaire/exporter/ProjectsControllerTest.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
import static org.junit.Assert.assertEquals;
4
import static org.junit.Assert.assertFalse;
5

  
6
import org.junit.Before;
7
import org.junit.Test;
8

  
9
public class ProjectsControllerTest {
10

  
11
	private ProjectsController controller;
12
	private ProjectQueryParams params;
13
	private String expectedFP7Query = "select 'EC' as funder, 'EU' as jurisdiction, p.acronym as acronym, " + "p.title as title, "
14
			+ "regexp_replace(p.id, '^corda_______::'  , '') as grant_agreement_number, " + "p.startdate as start_date, " + "p.enddate as end_date, "
15
			+ "f1.name as subdivision, " + "f2.name as specificprogramme, " + "f3.name as fundingprogramme " + "from projects p "
16
			+ "left outer join project_funding pf on (p.id = pf.project) " + "left outer join funding_funding ff1 on (pf.funding = ff1.funding1) "
17
			+ "left outer join funding_funding ff2 on (ff1.funding2 = ff2.funding1) " + "left outer join fundings f1 on (f1.id = ff1.funding1) "
18
			+ "left outer join fundings f2 on (f2.id = ff2.funding1) " + "left outer join fundings f3 on (f3.id = ff2.funding2) "
19
			+ "where f3.id = 'corda_______::FP7' ";
20
	private String expectedWTQuery = "select 'WT' as funder, p.acronym as acronym, p.title as title, regexp_replace(p.id, '^welcometrust::'  , '') as grant_agreement_number, "
21
			+ "p.startdate as start_date, p.enddate as end_date, f1.name as specificprogramme, f2.name as fundingprogramme "
22
			+ "from projects p left outer join project_funding pf on (p.id = pf.project) "
23
			+ "left outer join funding_funding ff1 on (pf.funding = ff1.funding1) "
24
			+ "left outer join fundings f1 on (f1.id = ff1.funding1)  "
25
			+ "left outer join fundings f2 on (f2.id = ff1.funding2) " + "where f2.id = 'wt::WT' ";
26
	private String expectedFCTQuery = "select 'PT' as jurisdiction, p.acronym as acronym, p.title as title, regexp_replace(p.id, '^fct_________::'  , '') as grant_agreement_number, "
27
			+ "p.startdate as start_date, p.enddate as end_date, f1.name as fundingprogramme, f2.name as funder "
28
			+ "from projects p left outer join project_funding pf on (p.id = pf.project) "
29
			+ "left outer join funding_funding ff1 on (pf.funding = ff1.funding1) "
30
			+ "left outer join fundings f1 on (f1.id = ff1.funding1)  "
31
			+ "left outer join fundings f2 on (f2.id = ff1.funding2) where f2.id = 'fct_________::FCT' ";
32

  
33
	@Before
34
	public void setup() {
35
		controller = new ProjectsController();
36
		params = new ProjectQueryParams();
37
	}
38

  
39
	@Test
40
	public void testObtainFP7Query() {
41
		params.setFundingProgramme("FP7");
42
		String res = controller.obtainFP7Query(params);
43
		System.out.println(res);
44
		assertEquals(expectedFP7Query, res);
45
	}
46

  
47
	@Test
48
	public void testObtainWellcomeTrustQuery() {
49
		params.setFundingProgramme("WT");
50
		String res = controller.obtainWellcomeTrustQuery(params);
51
		System.out.println(res);
52
		assertEquals(expectedWTQuery, res);
53
	}
54

  
55
	@Test
56
	public void testObtainFCTQuery() {
57
		params.setFundingProgramme("FCT");
58
		String res = controller.obtainFCTQuery(params);
59
		System.out.println(res);
60
		assertEquals(expectedFCTQuery, res);
61
	}
62

  
63
	@Test
64
	public void testQueryWithDateParams() {
65
		params.setFundingProgramme("WT");
66
		params.setStartFrom("2015");
67
		String res = controller.obtainWellcomeTrustQuery(params);
68
		System.out.println(res);
69
		assertFalse(expectedWTQuery.equals(res));
70
	}
71

  
72
}
modules/cnr-openaire-exporter/branches/4.x.x/src/test/java/eu/dnetlib/openaire/exporter/EPrintsTest.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
import java.io.IOException;
4
import java.io.StringWriter;
5

  
6
import org.apache.commons.io.IOUtils;
7
import org.junit.Before;
8
import org.junit.Test;
9
import org.springframework.core.io.ClassPathResource;
10
import org.springframework.core.io.Resource;
11

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

  
14
public class EPrintsTest {
15

  
16
	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>";
17
	private Resource wtProject = new ClassPathResource("eu/dnetlib/openaire/exporter/WT-project.xml");
18
	private Resource fctProject = new ClassPathResource("eu/dnetlib/openaire/exporter/FCT-project.xml");
19
	private Resource fp7Project = new ClassPathResource("eu/dnetlib/openaire/exporter/FP7-project.xml");
20

  
21
	private Resource ePrintsXslt;
22

  
23
	@Before
24
	public void setUp() throws Exception {
25
		ePrintsXslt = new ClassPathResource("xslt/projects_eprints.xslt", getClass());
26
	}
27

  
28
	@Test
29
	public void test() {
30
		ApplyXslt applyXslt = new ApplyXslt(ePrintsXslt);
31
		String result = applyXslt.evaluate(inputXML);
32
		System.out.println(result);
33
		assertTab(result);
34
	}
35

  
36
	@Test
37
	public void testWTePrints() throws IOException {
38
		ApplyXslt applyXslt = new ApplyXslt(ePrintsXslt);
39
		StringWriter w = new StringWriter();
40
		IOUtils.copy(wtProject.getInputStream(), w);
41
		String res = applyXslt.evaluate(w.toString());
42
		assertTab(res);
43
	}
44

  
45
	@Test
46
	public void testFCTePrints() throws IOException {
47
		ApplyXslt applyXslt = new ApplyXslt(ePrintsXslt);
48
		StringWriter w = new StringWriter();
49
		IOUtils.copy(fctProject.getInputStream(), w);
50
		String res = applyXslt.evaluate(w.toString());
51
		assertTab(res);
52
	}
53

  
54
	@Test
55
	public void testFP7ePrints() throws IOException {
56
		ApplyXslt applyXslt = new ApplyXslt(ePrintsXslt);
57
		StringWriter w = new StringWriter();
58
		IOUtils.copy(fp7Project.getInputStream(), w);
59
		String res = applyXslt.evaluate(w.toString());
60
		assertTab(res);
61
	}
62

  
63
	private void assertTab(final String str) {
64
		System.out.println(str);
65
		String[] splitOnTab = str.split("\t");
66
		org.junit.Assert.assertTrue(splitOnTab.length > 1);
67
		System.out.println(splitOnTab[0]);
68
		System.out.println("According to the Java split function, now there is a tab, followed by:");
69
		System.out.println(splitOnTab[1]);
70
	}
71
}
modules/cnr-openaire-exporter/branches/4.x.x/src/test/java/eu/dnetlib/openaire/exporter/DSpaceTest.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
import java.io.IOException;
4
import java.io.StringWriter;
5

  
6
import org.apache.commons.io.IOUtils;
7
import org.junit.Before;
8
import org.junit.Test;
9
import org.springframework.core.io.ClassPathResource;
10
import org.springframework.core.io.Resource;
11

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

  
14
public class DSpaceTest {
15

  
16
	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>";
17
	private Resource wtProject = new ClassPathResource("eu/dnetlib/openaire/exporter/WT-project.xml");
18
	private Resource fctProject = new ClassPathResource("eu/dnetlib/openaire/exporter/FCT-project.xml");
19
	private Resource fp7Project = new ClassPathResource("eu/dnetlib/openaire/exporter/FP7-project.xml");
20

  
21
	private Resource dspaceXslt;
22

  
23
	@Before
24
	public void setUp() throws Exception {
25
		dspaceXslt = new ClassPathResource("xslt/projects_dspace.xslt", getClass());
26
	}
27

  
28
	@Test
29
	public void test() {
30
		ApplyXslt applyXslt = new ApplyXslt(dspaceXslt);
31
		String result = applyXslt.evaluate(inputXML);
32
		System.out.println(result);
33
	}
34

  
35
	@Test
36
	public void testWTdspace() throws IOException {
37
		ApplyXslt applyXslt = new ApplyXslt(dspaceXslt);
38
		StringWriter w = new StringWriter();
39
		IOUtils.copy(wtProject.getInputStream(), w);
40
		String result = applyXslt.evaluate(w.toString());
41
		System.out.println("<?xml version='1.0' encoding='UTF-8'?>\n\n" + "<form-value-pairs>\n"
42
				+ "	<value-pairs value-pairs-name='WTprojects' dc-term='relation'>\n");
43
		System.out.println(result);
44
		System.out.println("	</value-pairs>\n" + "</form-value-pairs>\n");
45
	}
46

  
47
	@Test
48
	public void testFCTdspace() throws IOException {
49
		ApplyXslt applyXslt = new ApplyXslt(dspaceXslt);
50
		StringWriter w = new StringWriter();
51
		IOUtils.copy(fctProject.getInputStream(), w);
52
		String result = applyXslt.evaluate(w.toString());
53
		System.out.println(result);
54
	}
55

  
56
	@Test
57
	public void testFP7dspace() throws IOException {
58
		ApplyXslt applyXslt = new ApplyXslt(dspaceXslt);
59
		StringWriter w = new StringWriter();
60
		IOUtils.copy(fp7Project.getInputStream(), w);
61
		String result = applyXslt.evaluate(w.toString());
62
		System.out.println(result);
63
	}
64

  
65
}
modules/cnr-openaire-exporter/branches/4.x.x/src/test/resources/eu/dnetlib/openaire/exporter/FCT-project.xml
1
<?xml version="1.0" encoding="UTF-8"?>
2
<ROW>
3
	<FIELD name="funder">FCT</FIELD>
4
	<FIELD name="end_date">2003-10-31</FIELD>
5
	<FIELD name="grant_agreement_number">32639</FIELD>
6
	<FIELD name="title">Neural Network Controllers for Pneumatic Actuators</FIELD>
7
	<FIELD name="fundingprogramme">POCI</FIELD>
8
	<FIELD name="acronym">POCTI/EME/32639/2000</FIELD>
9
	<FIELD name="start_date">2000-11-01</FIELD>
10
	<FIELD name="jurisdiction">PT</FIELD>
11
</ROW> 
12

  
modules/cnr-openaire-exporter/branches/4.x.x/src/test/resources/eu/dnetlib/openaire/exporter/FP7-project.xml
1
<?xml version="1.0" encoding="UTF-8"?>
2
<ROW>
3
	<FIELD name="subdivision">PEOPLE</FIELD>
4
	<FIELD name="funder">EC</FIELD>
5
	<FIELD name="end_date">2012-07-31</FIELD>
6
	<FIELD name="grant_agreement_number">255646</FIELD>
7
	<FIELD name="title">Semiconductor lasers for generation of non-diffracting (Bessel) beams.</FIELD>
8
	<FIELD name="fundingprogramme">FP7</FIELD>
9
	<FIELD name="acronym">SENDBEAMS</FIELD>
10
	<FIELD name="specificprogramme">SP3</FIELD>
11
	<FIELD name="start_date">2010-08-01</FIELD>
12
	<FIELD name="jurisdiction">EU</FIELD>
13
</ROW> 
modules/cnr-openaire-exporter/branches/4.x.x/src/test/resources/eu/dnetlib/openaire/exporter/WT-project.xml
1
<ROW>
2
	<FIELD name="funder">WT</FIELD>
3
	<FIELD name="end_date">2013-07-29</FIELD>
4
	<FIELD name="grant_agreement_number">098241</FIELD>
5
	<FIELD name="title">EYEDIO DIGI - DEVELOPMENT OF AN INNOVATIVE, AFFORDABLE, EASY TO USE, HANDHELD RETINAL IMAGING PRODUCT FOR THE DIAGNOSIS OF DIABETIC RETINOPATHY.</FIELD>
6
	<FIELD name="fundingprogramme">WT</FIELD>
7
	<FIELD name="acronym">UNKNOWN</FIELD>
8
	<FIELD name="specificprogramme">Technology Transfer Division</FIELD>
9
	<FIELD name="start_date">2012-01-30</FIELD>
10
</ROW> 
modules/cnr-openaire-exporter/branches/4.x.x/src/main/java/eu/dnetlib/openaire/exporter/ProjectsController.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
import java.io.IOException;
4
import java.io.OutputStream;
5
import java.io.StringReader;
6
import java.text.SimpleDateFormat;
7
import java.util.Date;
8
import java.util.List;
9

  
10
import javax.servlet.ServletOutputStream;
11
import javax.servlet.ServletResponse;
12
import javax.servlet.http.HttpServletRequest;
13
import javax.servlet.http.HttpServletResponse;
14
import javax.xml.ws.wsaddressing.W3CEndpointReference;
15

  
16
import org.antlr.stringtemplate.StringTemplate;
17
import org.apache.commons.io.IOUtils;
18
import org.apache.commons.lang.StringUtils;
19
import org.apache.commons.logging.Log;
20
import org.apache.commons.logging.LogFactory;
21
import org.dom4j.Document;
22
import org.dom4j.io.SAXReader;
23
import org.springframework.beans.factory.annotation.Autowired;
24
import org.springframework.beans.factory.annotation.Value;
25
import org.springframework.core.io.Resource;
26
import org.springframework.stereotype.Controller;
27
import org.springframework.web.bind.annotation.RequestMapping;
28
import org.springframework.web.bind.annotation.RequestParam;
29

  
30
import com.google.common.base.Joiner;
31
import com.google.common.base.Splitter;
32
import com.google.common.collect.Lists;
33

  
34
import eu.dnetlib.enabling.database.rmi.DatabaseService;
35
import eu.dnetlib.enabling.locators.UniqueServiceLocator;
36
import eu.dnetlib.enabling.resultset.client.IterableResultSetClient;
37
import eu.dnetlib.enabling.resultset.client.ResultSetClientFactory;
38
import eu.dnetlib.miscutils.collections.MappedCollection;
39
import eu.dnetlib.miscutils.functional.xml.ApplyXslt;
40

  
41
@Controller
42
public class ProjectsController {
43

  
44
	private enum Funder {
45
		FP7, WT, FCT
46
	}
47

  
48
	@javax.annotation.Resource
49
	private UniqueServiceLocator serviceLocator;
50

  
51
	@Value("${dnet.openaire.db.name}")
52
	private String dbName;
53
	@Value("${openaire.exporter.dspace.xslt}")
54
	private Resource dspaceXslt;
55
	@Value("${openaire.exporter.eprints.xslt}")
56
	private Resource eprintsXslt;
57
	@Value("${openaire.exporter.projects2tsv.sql.template}")
58
	private Resource projects2tsvQueryTemplate;
59
	@Value("${openaire.exporter.projects2tsv.fields}")
60
	private String tsvFields;
61

  
62
	@Autowired
63
	private ResultSetClientFactory resultSetClientFactory;
64
	@Autowired
65
	private ProjectQueryParamsFactory projectQueryParamsFactory;
66

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

  
69
	@RequestMapping(value = "/openaire/export/**/project/dspace.do")
70
	void processDspace(final HttpServletRequest request,
71
			final ServletResponse response,
72
			@RequestParam(value = "startFrom", required = false) final String startFrom,
73
			@RequestParam(value = "startUntil", required = false) final String startUntil,
74
			@RequestParam(value = "endFrom", required = false) final String endFrom,
75
			@RequestParam(value = "endUntil", required = false) final String endUntil) throws Exception {
76

  
77
		response.setContentType("text/xml");
78

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

  
81
		ServletOutputStream out = response.getOutputStream();
82

  
83
		String head = "<?xml version='1.0' encoding='UTF-8'?>\n\n" + "<form-value-pairs>\n" + "	<value-pairs value-pairs-name='" + params.getFundingProgramme()
84
				+ "projects' dc-term='relation'>\n";
85

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

  
88
		IOUtils.copy(new StringReader(head), out);
89
		emit(out, dspaceXslt, obtainQuery(params));
90
		IOUtils.copy(new StringReader(tail), out);
91
	}
92

  
93
	@RequestMapping(value = "/openaire/export/**/project/eprints.do")
94
	void processEprints(final HttpServletRequest request,
95
			final ServletResponse response,
96
			@RequestParam(value = "startFrom", required = false) final String startFrom,
97
			@RequestParam(value = "startUntil", required = false) final String startUntil,
98
			@RequestParam(value = "endFrom", required = false) final String endFrom,
99
			@RequestParam(value = "endUntil", required = false) final String endUntil) throws Exception {
100

  
101
		response.setContentType("text/html");
102
		emit(response.getOutputStream(), eprintsXslt, obtainQuery(projectQueryParamsFactory.generateParams(request, startFrom, startUntil, endFrom, endUntil)));
103
	}
104

  
105
	@RequestMapping(value = "/openaire/export/project2tsv.do")
106
	void processEprints(final HttpServletRequest request,
107
			final HttpServletResponse response,
108
			@RequestParam(value = "funding", required = true) final String funding) throws Exception {
109

  
110
		final String date = new SimpleDateFormat("yyyyMMdd").format(new Date());
111
		response.setContentType("text/tab-separated-values");
112
		response.setHeader("Content-Disposition", "attachment; filename=\"projects_" + funding + "_" + date + ".tsv\"");
113

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

  
116
		if (funding.equalsIgnoreCase("wt")) {
117
			st.setAttribute("fundingprefix", "welcometrust::");
118
		} else if (funding.equalsIgnoreCase("fct")) {
119
			st.setAttribute("fundingprefix", "fct_________::");
120
		} else if (funding.equalsIgnoreCase("h2020")) {
121
			st.setAttribute("fundingprefix", "corda_______::H2020::");
122
		} else if (funding.equalsIgnoreCase("fp7")) {
123
			st.setAttribute("fundingprefix", "corda_______::FP7::");
124
		} else throw new Exception("Invalid funding " + funding + " (valid are: fp7, h2020, wt, fct)");
125

  
126
		emitAsTsv(response.getOutputStream(), st.toString());
127
	}
128

  
129
	/**
130
	 * Creates the query on the fundingProgramme specified in the given parameters.
131
	 * 
132
	 * @param params
133
	 *            request parameters
134
	 * @return the query string
135
	 * @throws IllegalArgumentException
136
	 *             if the funding programme is not recognized
137
	 */
138
	private String obtainQuery(final ProjectQueryParams params) throws IllegalArgumentException {
139
		String funder = params.getFundingProgramme();
140
		switch (Funder.valueOf(funder)) {
141
		case FP7:
142
			return obtainFP7Query(params);
143
		case WT:
144
			return obtainWellcomeTrustQuery(params);
145
		case FCT:
146
			return obtainFCTQuery(params);
147
		default:
148
			throw new IllegalArgumentException("Invalid Funder " + funder);
149

  
150
		}
151
	}
152

  
153
	/**
154
	 * Creates the query on FP7
155
	 * 
156
	 * @param params
157
	 * @return the query string
158
	 */
159
	protected String obtainFP7Query(final ProjectQueryParams params) {
160
		String query = "select 'EC' as funder, 'EU' as jurisdiction, p.acronym as acronym, " + "p.title as title, "
161
				+ "regexp_replace(p.id, '^corda_______::'  , '') as grant_agreement_number, " + "p.startdate as start_date, " + "p.enddate as end_date, "
162
				+ "f1.name as subdivision, " + "f2.name as specificprogramme, " + "f3.name as fundingprogramme " + "from projects p "
163
				+ "left outer join project_funding pf on (p.id = pf.project) " + "left outer join funding_funding ff1 on (pf.funding = ff1.funding1) "
164
				+ "left outer join funding_funding ff2 on (ff1.funding2 = ff2.funding1) " + "left outer join fundings f1 on (f1.id = ff1.funding1) "
165
				+ "left outer join fundings f2 on (f2.id = ff2.funding1) " + "left outer join fundings f3 on (f3.id = ff2.funding2) "
166
				+ "where f3.id = 'corda_______::FP7' ";
167

  
168
		if (StringUtils.isNotBlank(params.getSubdivision())) {
169
			query += "AND f1.name = '" + params.getSubdivision() + "'";
170
		}
171
		if (StringUtils.isNotBlank(params.getSpecificProgramme())) {
172
			query += "AND f2.name = '" + params.getSpecificProgramme() + "'";
173
		}
174
		query = setDateParameters(query, params);
175
		log.info("Executing query: " + query);
176
		return query;
177
	}
178

  
179
	/**
180
	 * Creates the query on WT
181
	 * 
182
	 * @param params
183
	 * @return the query string
184
	 */
185
	protected String obtainWellcomeTrustQuery(final ProjectQueryParams params) {
186
		String query = "select 'WT' as funder, p.acronym as acronym, p.title as title, regexp_replace(p.id, '^welcometrust::'  , '') as grant_agreement_number, "
187
				+ "p.startdate as start_date, p.enddate as end_date, f1.name as specificprogramme, f2.name as fundingprogramme "
188
				+ "from projects p left outer join project_funding pf on (p.id = pf.project) "
189
				+ "left outer join funding_funding ff1 on (pf.funding = ff1.funding1) "
190
				+ "left outer join fundings f1 on (f1.id = ff1.funding1)  "
191
				+ "left outer join fundings f2 on (f2.id = ff1.funding2) " + "where f2.id = 'wt::WT' ";
192

  
193
		if (StringUtils.isNotBlank(params.getSpecificProgramme())) {
194
			query += "AND f1.name = '" + params.getSpecificProgramme() + "'";
195
		}
196
		query = setDateParameters(query, params);
197
		log.info("Executing query: " + query);
198
		return query;
199
	}
200

  
201
	/**
202
	 * Creates the query on FCT (fct_________::FCT)
203
	 * 
204
	 * @param params
205
	 */
206
	protected String obtainFCTQuery(final ProjectQueryParams params) {
207
		String query = "select 'PT' as jurisdiction, p.acronym as acronym, p.title as title, regexp_replace(p.id, '^fct_________::'  , '') as grant_agreement_number, "
208
				+ "p.startdate as start_date, p.enddate as end_date, f1.name as fundingprogramme, f2.name as funder "
209
				+ "from projects p left outer join project_funding pf on (p.id = pf.project) "
210
				+ "left outer join funding_funding ff1 on (pf.funding = ff1.funding1) "
211
				+ "left outer join fundings f1 on (f1.id = ff1.funding1)  "
212
				+ "left outer join fundings f2 on (f2.id = ff1.funding2) where f2.id = 'fct_________::FCT' ";
213

  
214
		if (StringUtils.isNotBlank(params.getSpecificProgramme())) {
215
			query += "AND f1.name = '" + params.getSpecificProgramme() + "'";
216
		}
217
		query = setDateParameters(query, params);
218
		log.info("Executing query: " + query);
219
		return query;
220
	}
221

  
222
	void emit(final OutputStream out, final Resource xslt, final String query) throws IOException {
223
		W3CEndpointReference epr = serviceLocator.getService(DatabaseService.class).searchSQL(dbName, query);
224

  
225
		IterableResultSetClient iter = resultSetClientFactory.getClient(epr);
226

  
227
		for (String s : new MappedCollection<String, String>(iter, new ApplyXslt(xslt))) {
228
			out.write(s.getBytes());
229
		}
230
	}
231

  
232
	void emitAsTsv(final OutputStream out, final String query) throws Exception {
233
		W3CEndpointReference epr = serviceLocator.getService(DatabaseService.class).searchSQL(dbName, query);
234

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

  
238
		final SAXReader reader = new SAXReader();
239
		for (String s : resultSetClientFactory.getClient(epr)) {
240
			final Document doc = reader.read(new StringReader(s));
241
			final List<String> list = Lists.newArrayList();
242
			for (String f : fields) {
243
				list.add(doc.valueOf("//FIELD[@name='" + f + "']").replaceAll("\\n|\\t", " ").replaceAll("\\s+", " ").trim());
244
			}
245
			writeCSVLine(out, list);
246
		}
247
		out.flush();
248
	}
249

  
250
	private void writeCSVLine(final OutputStream out, final List<String> list) throws Exception {
251
		out.write(Joiner.on('\t').useForNull("").join(list).getBytes());
252
		out.write('\n');
253
	}
254

  
255
	private String setDateParameters(final String query, final ProjectQueryParams params) {
256
		String queryWithDates = query;
257
		if (params.getStartFrom() != null) {
258
			queryWithDates += "AND p.startdate >= '" + params.getStartFrom() + "'";
259
		}
260
		if (params.getStartUntil() != null) {
261
			queryWithDates += "AND p.startdate <= '" + params.getStartUntil() + "'";
262
		}
263
		if (params.getEndFrom() != null) {
264
			queryWithDates += "AND p.enddate >= '" + params.getEndFrom() + "'";
265
		}
266
		if (params.getEndUntil() != null) {
267
			queryWithDates += "AND p.enddate <= '" + params.getEndUntil() + "'";
268
		}
269
		return queryWithDates;
270
	}
271

  
272
	public String getDbName() {
273
		return dbName;
274
	}
275

  
276
	public void setDbName(final String dbName) {
277
		this.dbName = dbName;
278
	}
279

  
280
	public Resource getDspaceXslt() {
281
		return dspaceXslt;
282
	}
283

  
284
	public void setDspaceXslt(final Resource dspaceXslt) {
285
		this.dspaceXslt = dspaceXslt;
286
	}
287

  
288
	public Resource getEprintsXslt() {
289
		return eprintsXslt;
290
	}
291

  
292
	public void setEprintsXslt(final Resource eprintsXslt) {
293
		this.eprintsXslt = eprintsXslt;
294
	}
295

  
296
	public ProjectQueryParamsFactory getProjectQueryParamsFactory() {
297
		return projectQueryParamsFactory;
298
	}
299

  
300
	public void setProjectQueryParamsFactory(final ProjectQueryParamsFactory projectQueryParamsFactory) {
301
		this.projectQueryParamsFactory = projectQueryParamsFactory;
302
	}
303
}
modules/cnr-openaire-exporter/branches/4.x.x/src/main/java/eu/dnetlib/openaire/exporter/ProjectQueryParams.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
import java.util.regex.Pattern;
4

  
5
import org.apache.commons.logging.Log;
6
import org.apache.commons.logging.LogFactory;
7

  
8
public class ProjectQueryParams {
9
	private String fundingProgramme = null;
10
	private String fundingStream = null;
11
	private String scientficArea = null;
12
	private String startFrom = null;
13
	private String startUntil = null;
14
	private String endFrom = null;
15
	private String endUntil = null;
16
	
17
	private static final Log log = LogFactory.getLog(ProjectQueryParams.class); // NOPMD by marko on 11/24/08 5:02 PM
18
	
19
	public String getFundingProgramme() {
20
		return fundingProgramme;
21
	}
22
	public void setFundingProgramme(String fundingProgramme) {
23
		this.fundingProgramme = verifyParam(fundingProgramme);
24
	}
25
	public String getSpecificProgramme() {
26
		return fundingStream;
27
	}
28
	public void setSpecificProgramme(String specificProgramme) {
29
		this.fundingStream = verifyParam(specificProgramme);
30
	}
31
	public String getSubdivision() {
32
		return scientficArea;
33
	}
34
	public void setSubdivision(String subdivision) {
35
		this.scientficArea = verifyParam(subdivision);
36
	}
37
	public String getStartFrom() {
38
		return startFrom;
39
	}
40
	public void setStartFrom(String startFrom) {
41
		this.startFrom = verifyParam(startFrom);
42
	}
43
	public String getStartUntil() {
44
		return startUntil;
45
	}
46
	public void setStartUntil(String startUntil) {
47
		this.startUntil = verifyParam(startUntil);
48
	}
49
	public String getEndFrom() {
50
		return endFrom;
51
	}
52
	public void setEndFrom(String endFrom) {
53
		this.endFrom = verifyParam(endFrom);
54
	}
55
	public String getEndUntil() {
56
		return endUntil;
57
	}
58
	public void setEndUntil(String endUntil) {
59
		this.endUntil = verifyParam(endUntil);
60
	}
61

  
62
	private String verifyParam(String p) {
63
		Pattern pattern = Pattern.compile("(\\w|\\-){1,32}");
64

  
65
		log.debug("TESTING SQL PARAM:" + p);
66
		if (p != null && !pattern.matcher(p).matches())
67
			throw new IllegalArgumentException("Parameter contains an invalid character");
68
		log.debug("TEST OK");
69

  
70
		return p;
71
	}
72

  
73
}
modules/cnr-openaire-exporter/branches/4.x.x/src/main/java/eu/dnetlib/openaire/exporter/ProjectQueryParamsFactory.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
import javax.servlet.http.HttpServletRequest;
4

  
5
public class ProjectQueryParamsFactory {
6
	
7
	private static final String BASE_PATH = "/openaire/export/";
8
	private static final String NO_FILTER = "ALL";
9
	
10
	public ProjectQueryParams generateParams(final HttpServletRequest request, final String startFrom, final String startUntil, final String endFrom, final String endUntil) {
11
		ProjectQueryParams params = new ProjectQueryParams();
12
		
13
		String[] arr = request.getPathInfo().replace(BASE_PATH, "").split("\\/");
14
		if (arr.length != 5) throw new IllegalArgumentException("Invalid url");
15

  
16
		params.setFundingProgramme(arr[0]);
17
		params.setSpecificProgramme(NO_FILTER.equals(arr[1]) ? null : arr[1]);
18
		params.setSubdivision(NO_FILTER.equals(arr[2]) ? null : arr[2]);		
19
		// NB: arr[3] should be 'projects'
20
		// NB: arr[4] should be '[file].do'
21
		params.setStartFrom(startFrom);
22
		params.setStartUntil(startUntil);
23
		params.setEndFrom(endFrom);
24
		params.setEndUntil(endUntil);
25

  
26
		return params;
27
	}
28
	
29
}
modules/cnr-openaire-exporter/branches/4.x.x/src/main/resources/eu/dnetlib/openaire/exporter/xslt/projects_dspace.xslt
1
<?xml version="1.0" encoding="UTF-8"?>
2
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
3
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" exclude-result-prefixes="xsl xsi">
4

  
5
	<xsl:output omit-xml-declaration="yes" standalone="omit" indent="yes"/>
6
	
7
	<xsl:template match="/">
8
		<xsl:variable name="jurisdiction" select="//FIELD[@name='jurisdiction']"/>
9
		<xsl:variable name="id" select="//FIELD[@name='grant_agreement_number']" />
10
		<xsl:variable name="title" select="//FIELD[@name='title']" />
11
		<xsl:variable name="acronym" select="//FIELD[@name='acronym']" />
12
		<xsl:variable name="funder" select="//FIELD[@name='funder']" />
13
		<xsl:variable name="fundingProgramme" select="//FIELD[@name='fundingprogramme']" />
14
		<xsl:variable name="idnamespace">
15
		<!-- info:eu-repo/grantAgreement/Funder/FundingProgram/ProjectID /[Jurisdiction]/[ProjectName]/[ProjectAcronym]  -->
16
			<xsl:choose>
17
				<xsl:when test="$funder = 'EC'"><xsl:value-of select="concat('info:eu-repo/grantAgreement/', $funder, '/', $fundingProgramme, '/', $id, '/', $jurisdiction)" /></xsl:when>
18
				<xsl:when test="$funder = 'WT'"><xsl:value-of select="concat('info:eu-repo/grantAgreement/', $funder, '/', $id)" /></xsl:when>
19
				<xsl:when test="$funder = 'FCT'"><xsl:value-of select="concat('info:eu-repo/grantAgreement/', $funder, '/', $fundingProgramme, '/' , $id, '/', $jurisdiction)" /></xsl:when>
20
				<xsl:otherwise>info:eu-repo/grantAgreement/</xsl:otherwise>
21
			</xsl:choose>
22
		</xsl:variable>
23
		<pair>
24
			<displayed-value><xsl:value-of select="concat($id, ' - ', $acronym, ' - ', $title)" /></displayed-value>
25
			<stored-value><xsl:value-of select="$idnamespace" /></stored-value>
26
		</pair>
27
	</xsl:template>
28

  
29
</xsl:stylesheet>
modules/cnr-openaire-exporter/branches/4.x.x/src/main/resources/eu/dnetlib/openaire/exporter/xslt/projects_eprints.xslt
1
<?xml version="1.0" encoding="UTF-8"?>
2
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
3
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" exclude-result-prefixes="xsl xsi">
4

  
5
<!-- DO NOT REINDENT THIS FILE: IT IS IMPORTANT THAT EACH PROJECT IS ON ONE SINGLE LINE -->
6
	<xsl:output omit-xml-declaration="yes" standalone="omit" indent="no"/>
7
	
8
	<xsl:template match="/">
9
		<xsl:variable name="jurisdiction" select="//FIELD[@name='jurisdiction']"/>
10
		<xsl:variable name="id" select="//FIELD[@name='grant_agreement_number']" />
11
		<xsl:variable name="title" select="//FIELD[@name='title']" />
12
		<xsl:variable name="acronym" select="//FIELD[@name='acronym']" />
13
		<xsl:variable name="funder" select="//FIELD[@name='funder']" />
14
		<xsl:variable name="fundingProgramme" select="//FIELD[@name='fundingprogramme']" />
15
		<xsl:variable name="idnamespace">
16
		<!-- info:eu-repo/grantAgreement/Funder/FundingProgram/ProjectID /[Jurisdiction]/[ProjectName]/[ProjectAcronym]  -->
17
			<xsl:choose>
18
				<xsl:when test="$funder = 'EC'"><xsl:value-of select="concat('info:eu-repo/grantAgreement/', $funder, '/', $fundingProgramme, '/', $id, '/', $jurisdiction )" /></xsl:when>
19
				<xsl:when test="$funder = 'WT'"><xsl:value-of select="concat('info:eu-repo/grantAgreement/', $funder, '/', $id)" /></xsl:when>
20
				<xsl:when test="$funder = 'FCT'"><xsl:value-of select="concat('info:eu-repo/grantAgreement/', $funder, '/', $fundingProgramme, '/' , $id, '/', $jurisdiction)" /></xsl:when>
21
				<xsl:otherwise>info:eu-repo/grantAgreement/</xsl:otherwise>
22
			</xsl:choose>
23
		</xsl:variable>
24
		<xsl:variable name="listLabel">
25
		<xsl:choose>
26
				<xsl:when test="$fundingProgramme = 'FP7'">for:value:component:_fp7_project_id</xsl:when>
27
				<xsl:when test="$funder = 'WT'">for:value:component:_wt_project_id</xsl:when>
28
				<xsl:when test="$funder = 'FCT'">for:value:component:_fct_project_id</xsl:when>
29
				<xsl:otherwise>info:eu-repo/grantAgreement/</xsl:otherwise>
30
			</xsl:choose>
31
		</xsl:variable>
32
		<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> 
33
</xsl:text>
34
	</xsl:template>
35
	
36
</xsl:stylesheet>
modules/cnr-openaire-exporter/branches/4.x.x/src/main/resources/eu/dnetlib/openaire/exporter/sql/projects_tsv.sql.st
1
select
2
	pr.code                                                           AS "Grant Agreement Number", 
3
	pr.acronym                                                        AS "Project Acronym",  
4
	pr.title                                                          AS "Project Title", 
5
	pr.call_identifier                                                AS "Call ID", 
6
	pr.enddate                                                        AS "Start Date",
7
	pr.startdate                                                      AS "End Date", 
8
	pr.ec_sc39                                                        AS "ec_sc39",
9
	f.description                                                     AS "Discipline",
10
	org.legalname                                                     AS "Organization",
11
	org.countryclass                                                  AS "Country",
12
	CASE WHEN po.participantnumber = 1 THEN 'coordinator' ELSE '' END AS "Role",
13
	pe.firstname                                                      AS "Person Name", 
14
	pe.secondnames                                                    AS "Person Second Names",  
15
	pe.email                                                          AS "Person Email"
16
from projects pr
17
	left outer join project_organization po on (pr.id = po.project)
18
	left outer join persons pe on (po.contactperson=pe.id)
19
	left outer join organizations org on (po.resporganization = org.id)
20
	left outer join project_funding pf on (pr.id = pf.project)
21
	left outer join fundings f on (pf.funding = f.id)
22
where f.id like '$fundingprefix$%'
23
order by pr.acronym
modules/cnr-openaire-exporter/branches/4.x.x/src/main/resources/eu/dnetlib/openaire/exporter/applicationContext-openaire-exporter.properties
1
openaire.exporter.dspace.xslt=classpath:/eu/dnetlib/openaire/exporter/xslt/projects_dspace.xslt
2
openaire.exporter.eprints.xslt=classpath:/eu/dnetlib/openaire/exporter/xslt/projects_eprints.xslt
3
openaire.exporter.projects2tsv.sql.template=classpath:/eu/dnetlib/openaire/exporter/sql/projects_tsv.sql.st
4
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
modules/cnr-openaire-exporter/branches/4.x.x/src/main/resources/eu/dnetlib/openaire/exporter/webContext-openaire-exporter.xml
1
<beans xmlns="http://www.springframework.org/schema/beans"
2
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
3
	xmlns:context="http://www.springframework.org/schema/context"
4
	xmlns:util="http://www.springframework.org/schema/util"
5
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
6
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
7
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">
8

  
9
	<bean id="projectQueryParamsFactory" class="eu.dnetlib.openaire.exporter.ProjectQueryParamsFactory" />
10
	
11
</beans>
modules/cnr-openaire-exporter/branches/4.x.x/pom.xml
1
<?xml version="1.0" ?>
2
<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">
3
	<parent>
4
		<groupId>eu.dnetlib</groupId>
5
		<artifactId>dnet-parent</artifactId>
6
		<version>1.0.0</version>
7
		<relativePath />
8
	</parent>
9
	<modelVersion>4.0.0</modelVersion>
10
	<groupId>eu.dnetlib</groupId>
11
	<artifactId>cnr-openaire-exporter</artifactId>
12
	<packaging>jar</packaging>
13
	<version>4.1.3-SNAPSHOT</version>
14
	<scm>
15
	  <developerConnection>scm:svn:https://svn.driver.research-infrastructures.eu/driver/dnet40/modules/cnr-openaire-exporter/trunk</developerConnection>
16
	</scm>
17
	<dependencies>
18
		<dependency>
19
			<groupId>eu.dnetlib</groupId>
20
			<artifactId>cnr-service-common</artifactId>
21
			<version>[2.1.2,3.0.0)</version>
22
		</dependency>
23
		<dependency>
24
			<groupId>eu.dnetlib</groupId>
25
			<artifactId>cnr-resultset-client</artifactId>
26
			<version>[2.0.0,3.0.0)</version>
27
		</dependency>
28
		<dependency>
29
			<groupId>eu.dnetlib</groupId>
30
			<artifactId>cnr-enabling-database-api</artifactId>
31
			<version>[1.0.0,2.0.0)</version>
32
		</dependency>
33
		<dependency>
34
			<groupId>javax.servlet</groupId>
35
			<artifactId>javax.servlet-api</artifactId>
36
			<version>${javax.servlet.version}</version>
37
			<scope>provided</scope>
38
		</dependency>
39
		<dependency>
40
			<groupId>junit</groupId>
41
			<artifactId>junit</artifactId>
42
			<version>${junit.version}</version>
43
			<scope>test</scope>
44
		</dependency>
45
	</dependencies>
46
</project>
modules/cnr-openaire-exporter/branches/4.x.x/deploy.info
1
{"type_source": "SVN", 
2
"goal": "package -U source:jar", 
3
"url": "http://svn-public.driver.research-infrastructures.eu/driver/dnet40/modules/cnr-openaire-exporter/trunk/", 
4
"deploy_repository": "dnet4-snapshots", 
5
"version": "4", 
6
"mail": "alessia.bardi@isti.cnr.it", 
7
"deploy_repository_url": "http://maven.research-infrastructures.eu/nexus/content/repositories/dnet4-snapshots", "name": "cnr-openaire-exporter"}

Also available in: Unified diff