Ejegg has uploaded a new change for review. https://gerrit.wikimedia.org/r/175501
Change subject: Add backend Big English config and default filter ...................................................................... Add backend Big English config and default filter * Default filter used if none supplied on querystring * Filters can be defined as functions of columns, not just columns * Running in debug logs parsed filter to node console * Add query for Big English, grouped by hour Change-Id: Ia175b7825c8e1864a54d97dc3861285f227bd7d4 --- M routes/data.js A widgets/big-english.js 2 files changed, 67 insertions(+), 5 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/wikimedia/fundraising/dash refs/changes/01/175501/1 diff --git a/routes/data.js b/routes/data.js index 77a8719..4270e68 100644 --- a/routes/data.js +++ b/routes/data.js @@ -1,7 +1,8 @@ var widgets = require( '../widgets' ), odataParser = require( 'odata-parser' ), mysql = require ( 'mysql'), - config = require( '../config.js' ); + config = require( '../config.js' ), + util = require( 'util'); /** * Throws an error if an value is invalid for the given column @@ -77,7 +78,7 @@ * @returns {String} WHERE clause with '?' placeholders for values */ function buildWhere( filterNode, widget, values, joins ) { - var col, op, rightClause, leftClause, val, i, pattern, ops = { + var col, colText, op, rightClause, leftClause, val, i, pattern, ops = { 'and': 'AND', 'or': 'OR', 'eq': '=', @@ -131,8 +132,13 @@ val = parseFloat( val ); } values.push( val ); //this may get more complex with nesting... + + colText = col.table + '.' + col.column; + if ( col.func ) { + colText = col.func + '(' + colText + ')'; + } - return col.table + '.' + col.column + ' ' + op + ' ?'; + return colText + ' ' + op + ' ?'; case 'fn': pattern = patterns[filterNode.func]; if ( !pattern ) { @@ -166,6 +172,7 @@ connection, sqlQuery = '', parsedFilters, + filter, whereClause = '', values = [], joins = [], @@ -188,8 +195,12 @@ if ( qs && qs !== '' ) { try { parsedFilters = odataParser.parse( decodeURIComponent(qs) ); - if ( parsedFilters.$filter ) { - whereClause = 'WHERE ' + buildWhere( parsedFilters.$filter, widget, values, joins ); + filter = parsedFilters.$filter || widget.defaultFilter; + if ( filter ) { + if ( config.debug ) { + console.log( util.inspect( filter ) ); + } + whereClause = 'WHERE ' + buildWhere( filter, widget, values, joins ); } } catch ( err ) { diff --git a/widgets/big-english.js b/widgets/big-english.js new file mode 100644 index 0000000..1b00331 --- /dev/null +++ b/widgets/big-english.js @@ -0,0 +1,51 @@ +module.exports = { + name: 'big-english', + query: 'select count(*) as donations, sum(total_amount) as usd_total, CEILING(TIMESTAMPDIFF(SECOND, MIN(receive_date), MAX(receive_date))/60) as minutes, sum(total_amount)/CEILING(TIMESTAMPDIFF(SECOND, MIN(receive_date), MAX(receive_date))/60)/60 as usd_per_second, DAY(receive_date) as day, HOUR(receive_date) as hour from civicrm.civicrm_contribution cc [[WHERE]] GROUP BY DAY(receive_date), HOUR(receive_date) ORDER BY day ASC, hour ASC;', + mainTableAlias: 'cc', + optionalJoins: {}, + filters: { + Year: { + table: 'cc', + func: 'YEAR', + column: 'receive_date', + display: 'Year', + type: 'number' + }, + Month: { + table: 'cc', + func: 'MONTH', + column: 'receive_date', + display: 'Month', + type: 'number' + }, + Amount: { + table: 'cc', + column : 'total_amount', + display : 'Amount', + type : 'number', + min : 0, + max : 10000 + } + }, + defaultFilter: { + type: 'and', + left: { + type: 'eq', + left: { type: 'property', name: 'Year' }, + right: { type: 'literal', value: '2014' }, + }, + right: { + type: 'and', + left: { + type: 'eq', + left: { type: 'property', name: 'Month' }, + right: { type: 'literal', value: '12' } + }, + right: { + type: 'lt', + left: { type: 'property', name: 'Amount' }, + right: { type: 'literal', value: '5000' } + } + } + } +} \ No newline at end of file -- To view, visit https://gerrit.wikimedia.org/r/175501 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Ia175b7825c8e1864a54d97dc3861285f227bd7d4 Gerrit-PatchSet: 1 Gerrit-Project: wikimedia/fundraising/dash Gerrit-Branch: master Gerrit-Owner: Ejegg <eeggles...@wikimedia.org> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits