From 4727258f52be2614eb6103d1e4869c80a7b876cc Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Fri, 13 Jun 2008 17:34:39 +0000
Subject: [PATCH] add "is blank" and "is not blank" ops, similar to "is null"
 and "is not null"

git-svn-id: svn://svn.open-ils.org/ILS/trunk@9832 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 .../src/perlmods/OpenILS/Reporter/SQLBuilder.pm    | 14 +++++++++++++-
 Open-ILS/web/reports/oils_rpt_builder.js           |  4 ++--
 Open-ILS/web/reports/oils_rpt_param_editor.js      |  2 ++
 Open-ILS/web/reports/xul/operators.js              |  8 ++++++++
 Open-ILS/web/reports/xul/template-config.js        |  2 +-
 5 files changed, 26 insertions(+), 4 deletions(-)

diff --git a/Open-ILS/src/perlmods/OpenILS/Reporter/SQLBuilder.pm b/Open-ILS/src/perlmods/OpenILS/Reporter/SQLBuilder.pm
index f5307ef425..5aa6b2969e 100644
--- a/Open-ILS/src/perlmods/OpenILS/Reporter/SQLBuilder.pm
+++ b/Open-ILS/src/perlmods/OpenILS/Reporter/SQLBuilder.pm
@@ -927,7 +927,7 @@ sub toSQL {
 
 	return $self->{_sql} if ($self->{_sql});
 
-	my $sql;
+	my $sql = '';
 
 	my $rel = $self->find_relation();
 	if ($rel && $rel->is_nullable) {
@@ -941,12 +941,22 @@ sub toSQL {
 
 	if (lc($op) eq 'in') {
 		$sql .= " IN (". join(",", map { $_->toSQL } @$val).")";
+
 	} elsif (lc($op) eq 'not in') {
 		$sql .= " NOT IN (". join(",", map { $_->toSQL } @$val).")";
+
+	} elsif (lc($op) eq 'is blank') {
+		$sql = '('. $self->SUPER::toSQL ." IS NULL OR ". $self->SUPER::toSQL ." = '')";
+
+	} elsif (lc($op) eq 'is not blank') {
+		$sql = '('. $self->SUPER::toSQL ." IS NOT NULL AND ". $self->SUPER::toSQL ." <> '')";
+
 	} elsif (lc($op) eq 'between') {
 		$sql .= " BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
+
 	} elsif (lc($op) eq 'not between') {
 		$sql .= " NOT BETWEEN ". join(" AND ", map { $_->toSQL } @$val);
+
 	} elsif (lc($op) eq 'like') {
 		$val = $$val[0] if (ref($val) eq 'ARRAY');
 		$val = $val->toSQL;
@@ -954,6 +964,7 @@ sub toSQL {
 		$val =~ s/%/\\\\%/o;
 		$val =~ s/_/\\\\_/o;
 		$sql .= " LIKE '\%$val\%'";
+
 	} elsif (lc($op) eq 'ilike') {
 		$val = $$val[0] if (ref($val) eq 'ARRAY');
 		$val = $val->toSQL;
@@ -961,6 +972,7 @@ sub toSQL {
 		$val =~ s/%/\\\\%/o;
 		$val =~ s/_/\\\\_/o;
 		$sql .= " ILIKE '\%$val\%'";
+
 	} else {
 		$val = $$val[0] if (ref($val) eq 'ARRAY');
 		$sql .= " $op " . $val->toSQL;
diff --git a/Open-ILS/web/reports/oils_rpt_builder.js b/Open-ILS/web/reports/oils_rpt_builder.js
index 380d1127e0..285807a946 100644
--- a/Open-ILS/web/reports/oils_rpt_builder.js
+++ b/Open-ILS/web/reports/oils_rpt_builder.js
@@ -518,7 +518,7 @@ function oilsAddRptFilterItem(path, tform, filter) {
    //_debug('NEXT PARAM = ' + oilsRptID2);
    //_debug('NEXT PARAM = ' + oilsRptNextParam());
 
-	if( filter == 'is' || filter == 'is not' )
+	if( filter == 'is' || filter == 'is not' || filter == 'is blank' || filter == 'is not blank' )
 		where.condition[filter] = null;
 	else where.condition[filter] = oilsRptNextParam();
 
@@ -557,7 +557,7 @@ function oilsAddRptHavingItem(path, tform, filter) {
 		column:   { transform: tform, colname: oilsRptPathCol(path) },
 		condition : {}
 	};
-	if( filter == 'is' || filter == 'is not' )
+	if( filter == 'is' || filter == 'is not' || filter == 'is blank' || filter == 'is not blank' )
 		having.condition[filter] = null;
 	else having.condition[filter] = oilsRptNextParam();
 
diff --git a/Open-ILS/web/reports/oils_rpt_param_editor.js b/Open-ILS/web/reports/oils_rpt_param_editor.js
index 8455217ab7..d99c7b0775 100644
--- a/Open-ILS/web/reports/oils_rpt_param_editor.js
+++ b/Open-ILS/web/reports/oils_rpt_param_editor.js
@@ -194,6 +194,8 @@ oilsRptParamEditor.prototype.buildWidget = function(param, node) {
 			return new oilsRptSetWidget(widgetArgs);
         case 'is':
         case 'is not':
+        case 'is blank':
+        case 'is not blank':
             return new oilsRptNullWidget(widgetArgs);
 		case 'between':
 		case 'not between':
diff --git a/Open-ILS/web/reports/xul/operators.js b/Open-ILS/web/reports/xul/operators.js
index 81982fd569..8c643e4620 100644
--- a/Open-ILS/web/reports/xul/operators.js
+++ b/Open-ILS/web/reports/xul/operators.js
@@ -55,6 +55,14 @@ var OILS_RPT_FILTERS = {
 
 	'is not' : {
 		label : 'Is not NULL'
+	},
+
+	'is blank' : {
+		label : 'Is NULL or Blank'
+	},
+
+	'is not blank' : {
+		label : 'Is not NULL or Blank'
 	}
 }
 
diff --git a/Open-ILS/web/reports/xul/template-config.js b/Open-ILS/web/reports/xul/template-config.js
index b112595536..24681e8d49 100644
--- a/Open-ILS/web/reports/xul/template-config.js
+++ b/Open-ILS/web/reports/xul/template-config.js
@@ -920,7 +920,7 @@ function fleshTemplateField ( template, rel, tab_name, field ) {
 
 	if (tab_name.match(/filter/)) {
 		element.condition = {};
-		if (tab[field].op == 'is' || tab[field].op == 'is not') {
+		if (tab[field].op == 'is' || tab[field].op == 'is not' || tab[field].op == 'is blank' || tab[field].op == 'is not blank') {
 			element.condition[tab[field].op] = null;
 		} else {
 			element.condition[tab[field].op] =
-- 
2.43.2