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
