bernhard

Preview: RockDataTables

Recommended Posts

hi everybody,

this is a preview of a module that i'm working on for quite a long time. I needed it for an intranet office management application that i'm still working on. It makes it very easy to create very customized Datatables using the awesome jquery datatables plugin (www.datatables.net)

 

EARLY ALPHA release!

It's very likely that there will lots of changes to this module that may cause breaking your implementations.

 

Module source

sorry, removed as it changes too frequently; closed alpha - contact me if you think you can contribute

 

 

Remarks:

The module is intended to be used on the backend. Loading styles is at the moment only done via adding files to the $config->styles/scripts arrays. Also the communicaton to javascript is done via the $config->js() method that is built into the admin and would have to be implemented manually on frontend use. But it would not be difficult of course :)

Installation:

Nothing special here, just download + install

 

edit: removed some parts, because i made a complete rewrite of the module! (see last posting in the thread)

 

Customization example:

in the screencast you see an example that i'm using in a process module. i put the table inside an InputfieldMarkup just to have the same look&feel all around the admin. you see that you could also use this module to create tables quickly and easily using @kongondo s runtime markup module.

complete PHP code:

$this->headline('Kundenliste');
$form = $modules->get('InputfieldForm');

// init datatables module
    $dt2 = $modules->get('RockDataTables2');

// setup columns
    // name
        $col = new dtCol();
        $col->name = 'name';
        $col->title = 'Name';
        $col->data = function($page) {
            return $page->title;
        };
        $dt2->cols->add($col);

    // type
        $col = new dtCol();
        $col->name = 'type';
        $col->data = function($page) {
            return $page->rockcontact_type->title;
        };
        $dt2->cols->add($col);

    // modified
        $col = new dtCol();
        $col->name = 'modified';
        $col->data = function($page) {
            $obj = new stdClass();
            $obj->timestamp = $page->modified;
            $obj->display = date('d.m.Y', $page->modified);
            return $obj;
        };
        $dt2->cols->add($col);

// setup table
    $dt2->id = 'dt_kundenliste';
    $dt2->js('/site/modules/ProcessRockProjects/dt_kundenliste.js');
    
    $f = $modules->get('InputfieldMarkup');
    $f->value = $dt2->render();
    $form->add($f);

// ##################################
// ajax request -> return data
// non-ajax -> render form + table
// ##################################
if($config->ajax) {
    echo $dt2->getJSON($pages->find('template=rockcontact, sort=random, limit=10000'));
    die();
}
else $out .= $form->render();

return $out;

what is interesting here is this part:

// modified
    $col = new dtCol();
    $col->name = 'modified';
    $col->data = function($page) {
        $obj = new stdClass();
        $obj->timestamp = $page->modified;
        $obj->display = date('d.m.Y', $page->modified);
        return $obj;
    };
    $dt2->cols->add($col);

datatables support orthogonal data (https://datatables.net/extensions/buttons/examples/html5/outputFormat-orthogonal.html). a date column is a good example, because you need to DISPLAY a formatted date (like 10.02.2017) but you need to be able to SORT this column by a different value (timestamp). its very easy to accomplish this by providing both values in your json. Btw: You could also just transfer the timestamp and do the formatting on the client-side via javascript. Next Example will show ho this would work. Both cases can be necessary, it's just an example here. :)

[...] removed

Why i created this module:

  • of course i know @Soma s module but i needed a lot more features and the newer datatables version. also i like to define all the columns as objects and have everything on one place.
  • lister & markupadmindatatable: nice for basic tables but lacks of features to modify the appearance of the cell values (like rendering icons, background colors and so on)
  • datatables provides a great frontend API for filtering, showing/hiding columns, getting data, modifying it...
  • it also plays well together with frontend charts like google chart api in this case:
    googlechart.gif

 

todo / roadmap:

  • all kinds of column filters (like seen in the example above that shows an older and bloated version of this module)
  • support for ajax filters and pagination (currently all filtering and sorting is done on the client side. i tried it with up to 50.000 rows and got reasonable results. initial loading took around 10sec. but of course this heavily depends on the complexity of your table and your data.

 

 

 

  • Like 17

Share this post


Link to post
Share on other sites

need to setup tables via custom sql queries?

PHP

// init datatables module
    $dt2 = $modules->get('RockDataTables2');

// setup columns
    $sql = 'SELECT * FROM invoicetable';
    $dt2->setupColsBySql($sql);

// setup table
    $dt2->id = 'dt_finanzbuch';
    $dt2->js('/site/modules/ProcessRockFinance/dt_finanzbuch.js');
    
    $f = $modules->get('InputfieldMarkup');
    $f->label = 'Tabelle';
    $f->value = $dt2->render();
    $form->add($f);

// ##################################
// ajax request -> return data
// non-ajax -> render form + table
// ##################################
if($config->ajax) {
    echo $dt2->getJSON($dt2->getDataBySql($sql));
    die();
}

JS

$(document).ready(function() {
    // setup variables
        var opt = ProcessWire.config.dt_finanzbuch; // options from backend
        var colDefs = []; // column definitions

    // custom column definitions here

    // load default column definitions
        colDefs = colDefs.concat(dtGetDefaultDefs(opt));

    // initialise table
        $('#dt_finanzbuch').DataTable({
            ajax: { url: './book/', type: 'post' },
            columnDefs: colDefs,
            pageLength: 10,
            footerCallback: columnSums,
        });
});

Result

2017-02-16 17_52_11-.png

As you can see the sum function is not ready. it shows euros for my usecase...

  • Like 5

Share this post


Link to post
Share on other sites

Very nice! Do you think it is feasible to use this - or a future version - on the front end?

  • Like 1

Share this post


Link to post
Share on other sites

hi kathep,

sure it is also usable in the frontend. but it is no click-click solution. the module just provides some helpers to define the columns like i showed in the example. to make the code more clear to read, write and maintain. you would just have to include the right scripts (i'm currently loading all the plugins via cdn) and you would be fine. maybe you would also have to do some styling. but all of that is - at least for now - not intended to be handled by my module.

hope that answer helps you :)

  • Like 1

Share this post


Link to post
Share on other sites

plus DataTables are so easy to setup on the front end, you really don't need a module... just make your table, and then load the datatables scripts and init; the instructions are all there on the datatables site, and it is really well documented..

  • Like 1

Share this post


Link to post
Share on other sites

current version has properly formatted currency fields (atm only euro) and supports colorbars and column sums (also from selected rows)

had to remove this video, sorry

  • Like 2

Share this post


Link to post
Share on other sites

did a total rewrite of the module and will release it the next weeks when i'm done

 

  • Like 9

Share this post


Link to post
Share on other sites

Hi @bernhard Thanks for putting all the work into sharing this module. What came to my mind was that I do not really like providing code in admin input fields, except when it is a few lines of code. So wouldn't it be possible (optionally maybe) to provide paths to files instead?

  • Like 1

Share this post


Link to post
Share on other sites

Already done ;)

The fields are there for really simple tables and the files for more complex ones. The module will include all files related to a field automatically (PHP, js and CSS).

  • Like 1

Share this post


Link to post
Share on other sites

making progress on this but there's still a lot to do until it is releasable as a module (proper documentation mostly)...

sneak peak what's easily doable: a todo-app

5979eafe2b221_2017-07-2715_25_26-BaumrockAdminProcessWirebaumrock_dev.thumb.png.5ea6b9a66bc50536acc38be52edbeba4.png

definition is as easy as that:

	/**
	 * show table of todos
	 */
	public function executeTodos() {
		
    // create form
      $form = modules('InputfieldForm');
      $form->action = './';

      // add new project
        $b = modules('InputfieldButton');
        $b->attr("id+name", "addTodo");
        $b->addClass("ui-priority-primary pw-panel pw-panel-reload");
        $b->value = __("Neues Todo");
        $b->attr('data-href', pages(2)->url . 'page/add/?parent_id=' . pages('template=todos'));
        $b->icon = "plus";

        $f = modules('InputfieldMarkup');
        $f->value = $b->render();
        $form->add($f);

      // table
        $t = modules('InputfieldRockDatatables');
        $t->attr('id+name', 'manageTodos');

        $t->rows = pages('template=todo'); // define the table's source rows with one selector
        $t->ajax = 1; // set table mode to ajax loading making it possible to reload data via a simple $table.ajax.reload();

      [...]

        // remaining
        $col = new dtCol;
        $col->name = "remaining";
        $col->title = 'Tage';
        $col->className = 'minwidth';
        $col->data = function($page) {
          if(!$page->deadline) return '';

          $now = new \DateTime('now');
          $then = new \DateTime();
          $then->setTimestamp($page->deadline);
          $days = $now->diff($then)->format('%R%a');

          $color = config()->colors->lightred;
          if($days > 0) $color = config()->colors->lightorange;
          if($days > 7) $color = config()->colors->lightgreen;

          $obj = new \stdClass();
          $obj->display = $days;
          $obj->colorBars = [ [1, $color] ];

          return $obj;
        };
        $t->cols->add($col);

      [...]

      // add field to form

        $f = modules('InputfieldMarkup');
        $f->value = $t->render();
        $form->add($f);
    
    $out = $form->render();
    return $out;
	}

 

  • Like 7

Share this post


Link to post
Share on other sites

just to show that this module can render any kind of field (seems that this was not clear enough: https://processwire.com/talk/topic/16929-choose-custom-repeater-template/?do=findComment&comment=148803 )

a simple column showing images:

// pic
$col = new dtCol;
$col->name = "pic";
$col->title = 'pic';
$col->className = 'minwidth';
$col->data = function($page) {
  if(!$page->pics->count()) return '';
  return $page->pics->each(function($img) {
    return "<img src='{$img->maxSize(200,200)->url}' alt=''><br>";
  });
};
$t->cols->add($col);

2017-08-08 11_06_16-BaumrockAdmin • ProcessWire • baumrock.dev.png

so you can do whatever you want and whatever the API + PHP can do...

the module even supports a plugin-system where we can easily build cell-renderers that handle common tasks and make rendering even easier.

this for example is the js file of the column filter that shows a filter input on top of each column:

/**
 * this plugin creates filter inputs for every column
 * 
 * #### usage ####
 * just set your table's settings: { colfilter: true }
 * 
 * #### issues ####
 * the column filter breaks the table's column alignment on window resize
 * 
 * #### roadmap ####
 * make it possible to have different kind of inputs like range sliders and dropdowns
 * 
 */

/**
 * make sure that the tables searching setting is set to true
 */
$(document).on('beforeInit.rdt', '.RockDatatable', function(e, dt) {
  if(!dt.settings.colfilter) return;

  $table = $(dt.el);
  if(dt.settings.searching == false) console.log('overwriting setting "searching" to true for table ' + $table.attr('id'));
  dt.settings.searching = true;
});

/**
 * add colfilter row to table header
 */
$(document).on('afterInit.rdt', '.RockDatatable', function(e, dt) {
  if(!dt.settings.colfilter) return;

  var $table = $(e.target); // table dom element
  var table = $table.DataTable();
  var $thead = table.table().header();

  // prepare the new header row
  var $tr = $("<tr role='row' class='colfilter' />");
  $.each(dt.columnNames, function() {
    $tr.append($('<th><input type="text" class="colfilter"></th>'));
  });

  // add it to the table header
  $tr.prependTo($thead);
});

/**
 * handle filterinputs
 */
colfiltertimer = 0;
$(document).on('keypress keyup change', 'input.colfilter', function(e) {
  var $input = $(this);
  var $tr = $input.closest('tr');
  var $th = $input.closest('th');
  // datatables splits the table in 2 divs when scrollX is used! thats why we need to find the table via the wrapper
  var table = $input.closest('.dataTables_wrapper').find('table.RockDatatable').DataTable();
  var regex = false;

  // disable enter form submit
  var keyCode = e.keyCode || e.which;
  if (keyCode === 13) { 
    e.preventDefault();
    return false;
  }

  // delay search while typing
  clearTimeout(colfiltertimer);
  colfiltertimer = setTimeout(function() {
    table.columns($th.index()+':visIdx').search($input.val(), regex, false).draw();
  }, 500);
});

this may look complex in the beginning but once we have such a plugin we only have to set one option of the table and the filter inputs appear :)

 

  • Like 2

Share this post


Link to post
Share on other sites

datatable connected to the pagefield :)

asmdemo.thumb.gif.8856771cdd6bcbc3a7bc2b487fe2caa3.gif

updating the pageautocomplete field seemed a little tricky in the beginning but the final approach was quite easy:

// handle buttonclicks
$('#addcompetences').click(function(e) {
  e.preventDefault();
  var table = $table.DataTable(); // datatable instance
  var $field = $('#wrap_Inputfield_competences'); // asm select field
  var $template = $field.find('li.itemTemplate'); // selected page template
  var $ol = $template.closest('ol');
  
  // get selected competences from table
  var selectedtable = $.map(
    table.rows({selected:true}).data(),
    function(val, i) { return [[ val.id.sort, val.title + ' (' + val.cluster.filter + ')' ]]; }
  );

  // update ASM
  // clone template and populate values
  $.each(selectedtable, function(i,val) {
    // check double
    if($ol.find('.itemValue:contains("' + val[0] + '")').length) return;

    // add clone
    var $new = $template.clone().appendTo($template.parent());
    $new.find('.itemValue').text(val[0]);
    $new.find('.itemLabel').text(val[1]);
    $new.removeClass('itemTemplate');
  });
  
  // add state changed class
  $field.addClass('InputfieldStateChanged');

  // update original input
  InputfieldPageAutocomplete.rebuildInput($ol);
  
  return false;
});

 

  • Like 3

Share this post


Link to post
Share on other sites

very nice - a good alternative to other page select options out there, and intuitive for the user;

  • Like 1

Share this post


Link to post
Share on other sites

looks like i could use this module pretty soon on a project - LMK if you need someone to test it...

Share this post


Link to post
Share on other sites

another example how versatile and extendable this module is: you can write your own plugins to show custom column statistics. here i'm showing max, average and minimum values of some collected feedbacks. X means no answer and is excluded from the stats:

colstats.thumb.gif.21d09aa4282454f3e33ea9b216d3be4d.gif

plugincode is quite simple once you know the basics of datatables api:

$(document).on('update.rdt', 'div.colstats, div.colstatsselected', function(e, table, col) {
  var $div = $(e.target);
  var $table = $(table);
  var table = $table.DataTable();

  // get table settings
  var id = $table.attr('id').replace('RockDatatable_','');
  var dt = ProcessWire.config.RockDatatables[id];
  var colname = dt.columnNames[col.index()];

  var selector = {search: 'applied'};
  if($div.hasClass('colstatsselected')) selector.selected = true;

  // get data
  var data = table
    .rows(selector)
    .data()
    .filter(function(row) {
      // return true if it is a number
      // don't count empty cells or "noanswer" cells that have an X
      return row[colname]*1;
    })
    .pluck(colname);

  // get sum + rows
  var sum = data.sum();
  var rows = data.length;

  // early exit?
  if(!sum || !rows) {
    $div.html('');
    return;
  }

  // add stats for each column
  var avg = sum / rows;
  $div.html(
    ' ↑ ' + Math.max.apply(null, data) + 
    ' ø ' + avg.toFixed(2) +
    ' ↓ ' + Math.min.apply(null, data)
  );
});

:)

  • Like 4

Share this post


Link to post
Share on other sites

Hey @bernhard - I love what I see here, but I am curious how the data is stored? I assume it links to a custom db table that you create manually? 

As an aside in my pre-PW life I used SigmaGrid (which was ahead of its time) in the admin of my custom CMS to handle display/editing of purchases from an online store, so I am excited to play around with this when it's ready.

Share this post


Link to post
Share on other sites

hi adrian,

data is stored just as normal pw pages. nothing fancy here. this makes it very easy to use! the current setup is that you need 3 files to define your field:

// yourfield_data.php
<?php
// return all relations that belong to this project
return $pages->find(
  "template=feedback". // find all relations
  ",has_parent=$page" // project must be the current project
);
// yourfield_columns.php
<?php namespace ProcessWire;
// sample column showing the ID of the page
$col = new dtCol();
$col->name = "id";
$col->title = 'Columns ID';
$col->data = function($page) {
  return $page->id;
};
$this->cols->add($col);
// yourfield_js.js (optional, but in most cases necessary to do advanced stuff)
var settings = {
  select: true,
  pageLength: 100,
};
11 minutes ago, adrian said:

I am excited to play around with this when it's ready.

I'm also very excited to take this further. I cannot release it at the current stage though because major changes are not only likely to come but almost planned :)

 

i have to work more on performance. I'm thinking about some kind of caching technique and maybe also providing AJAX pagination.

some things like column statistics would only work on the client side when all the data is loaded, though. but i know that datatables can handle several thousands of rows quite efficiently so i think i'll find a way to work with those amounts of data.

  • Like 3

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By flydev
      PulsewayPush
      Send "push" from ProcessWire to Pulseway.
       
      Description
      PulsewayPush simply send a push to a Pulseway instance. If you are using this module, you probably installed Pulseway on your mobile device: you will receive notification on your mobile.
      To get more information about Pulseway, please visit their website.
      Note
      They have a free plan which include 10 notifications (push) each day.
       
      Usage
      Install the PulsewayPush module.
      Then call the module where you like in your module/template code :
      <?php $modules->get("PulsewayPush")->push("The title", "The notification message.", "elevated"); ?>  
      Hookable function
      ___push() ___notify() (the two function do the same thing)
       
      Download
      Github:  https://github.com/flydev-fr/PulsewayPush Modules Directory: https://modules.processwire.com/modules/pulseway-push/  
      Examples of use case
      I needed for our work a system which send notification to mobile device in case of a client request immediate support. Pulseway was choosen because it is already used to monitor our infrastructure.
      An idea, you could use the free plan to monitor your blog or website regarding the number of failed logins attempts (hooking Login/Register?), the automated tool then block the attacker's IP with firewall rules and send you a notification.
       

       
      - - - - - - - - - - - - - -
      11-22-2017: added the module to the modules directory
       
    • By netcarver
      Part 1 of a 2 part Module & Service Reveal.
      I'm currently working on a new module: ModuleReleaseNotes that was inspired by the work I originally did on making Ryan's ProcessWireUpgrades module "release" aware. In the end, I decided to ditch the approach I was originally taking and instead work on a module that hooked in to the UpgradeConfirmation dialog and the module edit page.
      Aims
      My aims for this module are as follows...
      Make discovery of a module's changes prior to an upgrade a trivial task. Make breaking changes very obvious. Make reading of a module's support documentation post-install a trivial task. Make module authors start to think about how they can improve the change discovery process for their modules. Make sure the display of information from the module support files/commit messages doesn't introduce a vulnerability. Looking at these in turn...
      Making discovery of a module's changes prior to upgrade a trivial task.
      This is done by adding a "What's changed section" to the upgrade confirmation dialog.  This section takes a best-effort approach to showing what's changed between the installed version and the updated version that's available via the module repository.
      At present, it is only able to talk to github-hosted repositories in order to ask them for the release notes, the changelog file (if present) and a list of commits between the git tag that matches the installed version and the tag matching the latest version.
      It will display the Release Notes (if the author is using the feature), else it will display the commits between the tags (if tagging is used by the module author) else it will show the changelog file (if present) else it will show the latest N commits on the master branch (N, of course, being configurable to your liking.)
      An example of the Github Release Notes pulled in for you, taken from Mike Rockett's TextformatterTypographer Module...

      An example of a tag-to-tag commit list from the same module...

      An example of a changelog - formatted to show just the changes (formatting styles will change)...

      Finally, an example of a fallback list of commits - sorry Adrian ...

       
      Making breaking changes obvious.
      This is currently done by searching for a set of configurable search strings. Later versions may be able to support breaking change detection via use of Semantic Versioning - but this may require some way of signalling the use of this versioning standard on a module-by-module basis.
      For now, then, you can customise the default set of change markers. Here I have added my own alias to the list of breaking change markers and the changes section of the changelog is styled accordingly (these will be improved)...

       
      Make reading of a module's support documentation, post-install, a trivial task.
      This is done by making some of the support files (like the README, CHANGELOG and LICENSE files) readable from the module's information/settings screen. There is an option to control the initial open/closed state of this section...

      Here is Tracy's README file from within the module settings page... 

       
      Make module authors start to think about how they can improve the change discovery process for their modules.
      There are notes in each of the sections displayed on the upgrade confirmation page that help authors use each of the features...

       
      Make sure display of external information doesn't introduce a vulnerability.
      This is an ongoing concern, and is the thing that is most likely to delay or prevent this module's release lead to this module's withdrawl should a vulnerability be found. Currently, output is formatted either via Markdown + HTML Purifier (if it was originally a Markdown file) or via htmlspecialchars() if it has come from a plaintext file.
      If you discover a vulnerability, please get in contact with me via the forum PM system.
       
      Ongoing...
      For now, I've concentrated on integration with GitHub, as most people use that platform to host their code. I know a few people are hosting their repositories with BitBucket (PWFoo comes to mind) and some with GitLab (Mike Rockett?) and I would eventually like to have adaptor implementations for these providers (and perhaps GitKraken) - but for now, GitHub rules and the other hosts are unsupported.
       
      Links
      Github: ModuleReleaseNotes
      PW Module Repository: Here
    • By FrancisChung
      Hi,
      I was wondering if anyone knew what this did? 

      If so, do you know what modules it actually impacts? Is there another log that lists the action it took?

      I'm having some weird problems with PHP duplicate declaration per link below, since this module was run.
       
       
    • By tpr
      AdminOnSteroids
      Various admin tweaks to enhance ProcessWire admin.
      http://modules.processwire.com/modules/admin-on-steroids/
      https://github.com/rolandtoth/AdminOnSteroids