Support filters for reference tables in checkbox controls

[misc/kostenrechnung] / ajax / ricoUpdateConnection.php

diff --git a/ajax/ricoUpdateConnection.php b/ajax/ricoUpdateConnection.php
index 3c5e2cf..7e8e5b1 100644 (file)
--- 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);