Support for database oriented checkboxes as control in LiveGrid tables
authorJoey Schulze <joey@infodrom.org>
Sat, 27 Feb 2010 22:13:23 +0000 (23:13 +0100)
committerJoey Schulze <joey@infodrom.org>
Sat, 27 Feb 2010 22:13:23 +0000 (23:13 +0100)
ajax/ricoUpdateConnection.php [new file with mode: 0644]
lib/ricoTableColumnDB.js [new file with mode: 0644]

diff --git a/ajax/ricoUpdateConnection.php b/ajax/ricoUpdateConnection.php
new file mode 100644 (file)
index 0000000..3c5e2cf
--- /dev/null
@@ -0,0 +1,74 @@
+<?php
+
+require_once('../init.php');
+
+function build_simple_query($details)
+{
+  if ($_POST['value'] == 0)
+    $sql = sprintf("DELETE FROM %s WHERE %s = %d AND %s = %d", $details['table'],
+                  $details['basecol'], $details['baseval'],
+                  $details['refcol'], $_POST['reference']);
+  else
+    $sql = sprintf("INSERT INTO %s (%s,%s,sys_user,sys_edit) VALUES (%d,%d,'%s',now())", $details['table'],
+                  $details['basecol'], $details['refcol'],
+                  $details['baseval'], $_POST['reference'],
+                  pg_escape_string($_SESSION['sys']['login']));
+
+  return $sql;
+}
+
+function build_query($details,$data)
+{
+  $where = '';
+  for ($i=0; $i<count($data); $i++)
+    if (array_key_exists('filter_'.$i, $_POST)) {
+      $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]);
+    }
+
+  if ($_POST['value'] == 0)
+    $sql = sprintf("DELETE FROM %s WHERE %s = %d AND %s IN (SELECT %s FROM %s%s)",
+                  $details['table'],
+                  $details['basecol'], $details['baseval'],
+                  $details['refcol'], $details['refid'], 
+                  $details['reftable'],
+                  strlen($where) ? ' ' . $where : '');
+  else
+    $sql = sprintf("INSERT INTO %s (%s,%s,sys_user,sys_edit) SELECT %s,%s,'%s',now() FROM %s%s",
+                  $details['table'],
+                  $details['basecol'], $details['refcol'],
+                  $details['baseval'], $details['refid'],
+                   pg_escape_string($_SESSION['sys']['login']),
+                  $details['reftable'],
+                  strlen($where) ? ' ' . $where : '');
+
+  return $sql;
+}
+
+if (empty($_POST['table']))
+  exit;
+
+connect_db();
+if (load_mask(substr($_POST['table'],5)) === false) exit;
+
+$fields = array_keys($mask['list']);
+$field = $fields[$_POST['column']];
+
+if (empty($field)) exit;
+
+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']);
+
+$sth = pg_query($sql);
+
+if ($sth === false)
+  error_log($sql . ': ' . pg_last_error());
+
+?>
diff --git a/lib/ricoTableColumnDB.js b/lib/ricoTableColumnDB.js
new file mode 100644 (file)
index 0000000..f0a31be
--- /dev/null
@@ -0,0 +1,98 @@
+/**
+  *  (c) 2008,9 Joey Schulze <joey@infodrom.org>
+  *
+  *  Rico is licensed under the Apache License, Version 2.0 (the "License"); you may not use this
+  *  file except in compliance with the License. You may obtain a copy of the License at
+  *   http://www.apache.org/licenses/LICENSE-2.0
+  **/
+
+if(typeof Rico=='undefined') throw("RicoExtension requires the Rico JavaScript framework");
+if(typeof Rico.TableColumn=='undefined') throw("RicoExtension requires ricoGridCommon.js");
+if(typeof Rico.TableColumn.checkbox=='undefined') throw("RicoExtension requires ricoGridControls.js");
+
+// Checkbox with database connection
+Rico.TableColumn.checkboxDB = Class.create(Rico.TableColumn.checkbox, {
+
+       initialize: function($super, refcol, update) {
+           $super(1, 0, 0, 0);
+           this._referenceColumn = refcol;
+           this._updateUrl = update;
+       },
+
+       _onclick: function($super,e) {
+           $super(e);
+           var elem=Event.element(e);
+           var windowRow=parseInt(elem.id.split(/_/).pop());
+
+           this._update(windowRow);
+       },
+
+       _formbase: function() {
+           return 'table='+this.liveGrid.tableId+'&column='+this.index;
+       },
+
+       _update: function(row) {
+           if (this._referenceColumn == undefined || this._updateUrl == undefined)
+               return false;
+
+            var value = this.getValue(row);
+
+           if (value == undefined)
+               return false;
+
+            var id = this.liveGrid.columns[this._referenceColumn].getValue(row);
+           var formdata = this._formbase() + '&reference='+id+'&value='+value;
+
+           new Ajax.Request(this._updateUrl, {method: 'post', parameters: formdata});
+       },
+
+       _updateall: function(value) {
+           var formdata = this._formbase() + '&value='+value;
+
+           if (this.liveGrid.filterCount() > 0) {
+               var cols = this.liveGrid.columns.length;
+               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;
+                       formdata += '&filter_'+c+'='+filter;
+                   }
+               }
+           }
+
+           new Ajax.Request(this._updateUrl, {method: 'post', parameters: formdata});
+
+           var basename = this.liveGrid.tableId + '_chkbox_' + this.index + '_';
+           var childCnt = this.numRows();
+           var val = value?true:false;
+           for (var r = 0; r < childCnt; r++)
+               if (this.getValue(r) != null) {
+                   var name = basename + r;
+                   $(name).checked = val;
+               }
+
+           var rows = this.liveGrid.buffer.totalRows;
+           for (var i = 0; i < rows; i++)
+               this.liveGrid.buffer.setValue(i, this.index, value);
+       },
+
+       _checkall: function() {
+           this._updateall(1);
+       },
+       _uncheckall: function() {
+           this._updateall(0);
+       },
+
+       _createFilters: function(parent,name) {
+           field_p=RicoUtil.createFormField(parent,'img',null,name+'_p');
+           field_p.src = Rico.imgDir+'p.gif';
+           field_p.tableColumn = this;
+           field_p.onclick = function() { this.tableColumn._checkall(); };
+           field_m=RicoUtil.createFormField(parent,'img',null,name+'_m');
+           field_m.src = Rico.imgDir+'m.gif';
+           field_m.tableColumn = this;
+           field_m.onclick = function() { this.tableColumn._uncheckall(); };
+       }
+});
+
+Rico.includeLoaded('ricoTableColumnDB.js');