Use the LimeSurvey API to automatically get the latest responses from LimeSurvey

[joebillings]

Currently a major part of generating LOD is exporting the LimeSurvey responses and making sure they're in the right location for the generation scripts. We should use the LimeSurvey API to automate this step.

[ColmMassey]

Note:
Export responses export as question code
Complete only

[wu-lee]

How do we do this currently, manually?

[wu-lee]

Seems to be related (duplicated by?) #6

[wu-lee]

Ok, I've cobbled a basic proof of concept together (inlined below). It's a NodeJS script, possibly we need to translate it into Ruby for consistency with our sausage-mechanics. (There were no examples in Ruby.)

To use it we first have to enable the API in RPCJSON mode, by logging in to the LimeSurvey administration, going to "Global settings", choosing the tab "Interfaces" and selecting the JSON-RPC service. I've done this.

The result, when successful, is CSV data on standard output. I've not inspected it in detail, but it looks plausible to my untrained eye.

(See options for the export function here: https://api.limesurvey.org/classes/remotecontrol_handle.html#method_export_responses)

@ColmMassey - once we've got the data, what do we do with it?

/** Exports a Lime Survey as CSV.
 *
 * Usage:
 *
 *    export-survey.js <username> <password> <survey-id>
 *
 * Adapted from the slightly shonky NodeJS example here: 
 * https://manual.limesurvey.org/RemoteControl_2_API#NodeJS_example
 */ 

const http = require('https');
const process = require('process');

const baseOptions = {
    hostname: "solidarityeconomyassociation.limequery.com",
    path: "/index.php/admin/remotecontrol",
    keepAlive: true,
    agent: false,
    method: 'POST',
    headers: {
	'user-agent': 'Apache-HttpClient/c4.2.2 (java 1.5)',
	'host': 'solidarityeconomyassociation.limequery.com',
	'path': '/index.php/admin/remotecontrol',
	'content-type': 'application/json'
    },
};



async function rpc(method, ...params) {
    // Copy/extend the base options
    const options = Object.assign({}, baseOptions);

    return new Promise((resolve, reject) => {
	// console.log("getting", options);
	const req = http.request(options);
	let buf = '';
	req.on('response', res => {
	    //console.log("got", res);
	    if (res.statusCode == 200) {
		res.setEncoding('utf8');
		res.on('data', (chunk) => {
		    // console.log("chunk", chunk);
		    buf += chunk;
		});
		res.on('end', () => {
		    const data = JSON.parse(buf);
 		    if (data.error)
			reject(data.error);
		    resolve(data.result);
		});
	    }
	    else
		reject(res.statusCode);
	});

	req.on('error', e => {
	    reject(e);
	});


	const body = JSON.stringify({
	    method: method,
	    params: params,
	    id: 1,
	});
	// console.log("body", body);
	req.write(body);

	req.end();
    });
};

const user = process.argv[2];
const password = process.argv[3];
const survey_id = process.argv[4];
async function main() {
    try {
	const session_key = await rpc('get_session_key', user, password);
	
	//console.log("session key", session_key);

	const responseb64 = await rpc(
	    'export_responses',
	    session_key,
	    survey_id,
	    'csv', 'en'
	);
	const data = String(Buffer.from(responseb64, 'base64'));
	console.log(data);

	await rpc('release_session_key', session_key).resolve();
    }
    catch(e) {
	console.error("error",e);
    }
}

try {
    main();
}
catch(e) {
    console.error("error",e);
}

[ColmMassey]

once we've got the data, what do we do with it?

It gets saved into the appropriate original-data folder such as

https://github.com/SolidarityEconomyAssociation/open-data/tree/master/newcastle-pilot/2019-07-03/original-data

The Newcastle data has a date stamp in the file name e.g. 2019-09-17-original.csv.

The sausage factory code is then called to act upon this file.

[wu-lee]

A Ruby translation of this script now committed here:

https://github.com/SolidarityEconomyAssociation/open-data/tree/lime-survey-exporter

Not certain if this completely fulfills the requirements of #6, but it should work for manual use, esp. when integrated with the Makefiles (if we're keeping them as is? @dtmakm27 can you comment?)

May also need a log-in stored in the password store. This probably shouldn't be a personal account, but something with limited permissions (as limited as possible).

[ColmMassey]

Not certain if this completely fulfills the requirements of #6,

I think the Limesurvey tickets need a little work to tease apart the different elements, which I have started.

[ColmMassey]

I assume the script gets all the completed survey entries. Was there an option in the API to get all the survey options that have changes since a given timestamp? Not needed for now, but useful to know if it is available.

[ColmMassey]

Can we do the following queries?

Have there been any changes to a specified survey since the last build?
Knowing this we would rebuild whenever there was any change.

Have there been any changes to any of the survey entries that have a specified boolean field set to True, since the last build.
Knowing this we could rebuild whenever any change was made to Approved survey entries.

[ColmMassey]

Both Newcastle and Oxford surveys now have populated approved fields to use.
We also have the new location field, which can hold a lat and long pair, semi-colon seperated. If that data is present it should take presidence over geolocation derived from postcode.

[wu-lee]

Ok, an update: there is a nominally working deployment of open-data on dev-0 which updates the data here:

https://dev.w3id.solidarityeconomy.coop/sea-lod/oxford-pilot/

There are some oddities there, the following of which I can confirm are in the original downloaded data:

• several blank entries with just an ID
• a "Zero Carbon Mapping Project" initiative miles from the others near Liverpool

And I've re-deployed the dev Oxford site to reference this dev data (still using dev.w3id.solidarityeconomy.coop URIs for now, rather than lod.coop URIs of any type):

https://dev.data.solidarityeconomy.coop/sea-lod/oxford-pilot

There was a bunch of permissions mole whacking and fiddling with sudo / cron / stmp relay settings required, but it now runs every 30 minutes, pulls the latest code from the lime-survey-downloads branch, then runs the seod command on the current Oxford data folder (which is refactored, with the latest conversion for lime-survey-data; seod is the command-line replacement for the old makefiles, added in the ica-youth-network-housekeeping branch). Specifically, tools/deploy/cronjob in the open-data repository is the script run.

To sidestep the password-store requirement of being installed in a sibling directory, with a GnuPG keyring available, and gpg-agent running with the right passphrase for decrypting, I'm using a private file containing the passwords in ~seopendata/.env on dev-0.

All this is deployed using an ansible playbook here, which I shall merge into the master branch there when I think I've no more tweaks to make to it:

https://github.com/SolidarityEconomyAssociation/technology-and-infrastructure/blob/seod-service/ansible/seod-service.yml