From 3ef1d4d7a1156a8f2e1f5ede5a9274b37dd801f7 Mon Sep 17 00:00:00 2001
From: Joey Schulze <joey@infodrom.org>
Date: Wed, 3 Mar 2010 16:43:24 +0100
Subject: [PATCH] Support filters for reference tables in checkbox controls

---
 ajax/ricoUpdateConnection.php | 23 ++++++++++++++---------
 lib/ricoTableColumnDB.js      |  2 +-
 2 files changed, 15 insertions(+), 10 deletions(-)

diff --git a/ajax/ricoUpdateConnection.php b/ajax/ricoUpdateConnection.php
index 3c5e2cf..7e8e5b1 100644
--- a/ajax/ricoUpdateConnection.php
+++ b/ajax/ricoUpdateConnection.php
@@ -17,33 +17,38 @@ function build_simple_query($details)
   return $sql;
 }
 
-function build_query($details,$data)
+function build_query($details,$mask)
 {
   $where = '';
-  for ($i=0; $i<count($data); $i++)
+  $keys = array_keys($mask['list']);
+  for ($i=0; $i<count($keys); $i++)
     if (array_key_exists('filter_'.$i, $_POST)) {
-      $filter = explode('%', $_POST['filter_'.$i]);
+      $filter = explode('|', $_POST['filter_'.$i]);
       if (strlen($where)) $where .= ' AND ';
       if ($filter[0] === 'LIKE') $filter[1] = '%'.$filter[1].'%';
       elseif ($filter[0] === 'EQ') $filter[0] = '=';
-      $where .= sprintf("%s %s '%s'", $data[$i]['name'], $filter[0], $filter[1]);
+      $where .= sprintf("%s %s '%s'",
+			empty($mask['list'][$keys[$i]]['sql'])?$keys[$i]:$mask['list'][$keys[$i]]['sql'],
+			$filter[0], $filter[1]);
     }
 
   if ($_POST['value'] == 0)
-    $sql = sprintf("DELETE FROM %s WHERE %s = %d AND %s IN (SELECT %s FROM %s%s)",
+    $sql = sprintf("DELETE FROM %s WHERE %s = %d AND %s IN (SELECT %s FROM %s%s%s)",
 		   $details['table'],
 		   $details['basecol'], $details['baseval'],
 		   $details['refcol'], $details['refid'], 
 		   $details['reftable'],
-		   strlen($where) ? ' ' . $where : '');
+		   array_key_exists('join', $mask) ? ' JOIN ' . join(' JOIN ', $mask['join']) : '',
+		   strlen($where) ? ' WHERE ' . $where : '');
   else
-    $sql = sprintf("INSERT INTO %s (%s,%s,sys_user,sys_edit) SELECT %s,%s,'%s',now() FROM %s%s",
+    $sql = sprintf("INSERT INTO %s (%s,%s,sys_user,sys_edit) SELECT %s,%s,'%s',now() FROM %s%s%s",
 		   $details['table'],
 		   $details['basecol'], $details['refcol'],
 		   $details['baseval'], $details['refid'],
                    pg_escape_string($_SESSION['sys']['login']),
 		   $details['reftable'],
-		   strlen($where) ? ' ' . $where : '');
+		   array_key_exists('join', $mask) ? ' JOIN ' . join(' JOIN ', $mask['join']) : '',
+		   strlen($where) ? ' WHERE ' . $where : '');
 
   return $sql;
 }
@@ -64,7 +69,7 @@ if (!is_array($mask['list'][$field]['update'])) return;
 if (isset($_POST['reference']))
   $sql = build_simple_query($mask['list'][$field]['update']);
 else 
-  $sql = build_query($mask['list'][$field]['update'],$mask['list']);
+  $sql = build_query($mask['list'][$field]['update'],$mask);
 
 $sth = pg_query($sql);
 
diff --git a/lib/ricoTableColumnDB.js b/lib/ricoTableColumnDB.js
index f0a31be..e042e4b 100644
--- a/lib/ricoTableColumnDB.js
+++ b/lib/ricoTableColumnDB.js
@@ -54,7 +54,7 @@ Rico.TableColumn.checkboxDB = Class.create(Rico.TableColumn.checkbox, {
 		for (var c = 0; c < cols; c++) {
 		    var column = this.liveGrid.columns[c]
 		    if (column.filterType == Rico.TableColumn.USERFILTER) {
-			var filter = column.filterOp + '%' + column.filterValues;
+			var filter = column.filterOp + '|' + column.filterValues;
 			formdata += '&filter_'+c+'='+filter;
 		    }
 		}
-- 
2.20.1