From 8e7335e0b07a04bf405b608eb5808c869d30c5d3 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 18 Sep 2013 13:55:13 +0200 Subject: [PATCH] Bug 10212: Columns configuration for tables This development introduces ColVis into Koha and provides a configuration page for columns visibility. ColVis is a plugin for DataTables. It allows to change the visibility of the columns in the table. * This development adds: - the js and css file for ColVis - a new DB table 'columns_settings' - a new template plugin 'ColumnsSettings' - a new package C4::Utils::DataTables::ColumnsSettings - a new admin page admin/columns_settings.pl * How it works: A yaml file is created (admin/columns_settings.yml) in order to take an inventory of all tables where ColVis is implemented. This file is read to create the list of modules, pages and tables in the configuration page. There are 3 possible keys in the yml: - is_hidden: default is 0 The column will be hidden. - cannot_be_toggled: default is 0. ColVis will allow to hide/show the column. - cannot_be_modified: default is 0 Default values (in the yml) won't be modifiable. When a user changes (or saves) the configuration for one module, all columns are added to the DB table. The values in the DB get the upper hand on the yaml values. * Humm, strange? It seems weird to have 2 storages for the same values. But I think it will be easy to add an entry and maintain the yaml rather than adding a new row (and new entry in updatedatabase script) in the DB. * Test plan: 1/ Execute the updatedatabase in order to create the new table. 2/ Take a look to the yml structure. 3/ Go on the checkouts page (circ/circulation.pl). 4/ Check that you cannot hide the 3 last columns on the issues table. 5/ Check that you cannot hide the 2 last columns on the holds table. 6/ Try to hide/show columns. 7/ Go on the columns configuration page (admin/columns_settings.pl). 8/ Only the last tab are filled with data. Check/uncheck checkboxes and save. 9/ Go on the checkouts/holds page and check that the behavior is what you expected. 10/ Give me some feedback :) * To go further: We can imagine that the configuration is saved for each user (and not globally like it is made with this patch). --- C4/Utils/DataTables/ColumnsSettings.pm | 123 ++++++++++++++++++ Koha/Template/Plugin/ColumnsSettings.pm | 67 ++++++++++ admin/columns_settings.pl | 59 +++++++++ admin/columns_settings.yml | 52 ++++++++ circ/circulation.pl | 1 + installer/data/mysql/kohastructure.sql | 14 +++ installer/data/mysql/updatedatabase.pl | 24 ++++ .../prog/en/includes/columns_settings.inc | 27 ++++ koha-tmpl/intranet-tmpl/prog/en/js/datatables.js | 2 +- .../prog/en/modules/admin/admin-home.tt | 2 + .../prog/en/modules/admin/columns_settings.tt | 131 ++++++++++++++++++++ .../prog/en/modules/circ/circulation.tt | 66 +++++++--- 12 files changed, 550 insertions(+), 18 deletions(-) create mode 100644 C4/Utils/DataTables/ColumnsSettings.pm create mode 100644 Koha/Template/Plugin/ColumnsSettings.pm create mode 100755 admin/columns_settings.pl create mode 100644 admin/columns_settings.yml create mode 100644 koha-tmpl/intranet-tmpl/prog/en/includes/columns_settings.inc create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/admin/columns_settings.tt diff --git a/C4/Utils/DataTables/ColumnsSettings.pm b/C4/Utils/DataTables/ColumnsSettings.pm new file mode 100644 index 0000000..e40d8d7 --- /dev/null +++ b/C4/Utils/DataTables/ColumnsSettings.pm @@ -0,0 +1,123 @@ +package C4::Utils::DataTables::ColumnsSettings; + +use Modern::Perl; +use List::Util qw( first ); +use C4::Context; + +sub get_yaml { + my $yml_path = C4::Context->config('intranetdir') . '/admin/columns_settings.yml'; + my $yaml = eval { YAML::LoadFile( $yml_path ) }; + warn "ERROR: the yaml file for DT::ColumnsSettings is not correctly formated: $@" if $@; + return $yaml; +} +sub get_tables { + my ( $module, $page ) = @_; + my $list = get_yaml; + return $list->{modules}{$module}{$page}; +} + +sub get_excluded { + my ( $module, $page, $table ) = @_; + + my $list = get_yaml; + return $list->{modules}{$module}{$page}{$table}{cannot_be_toggled}; +} + +sub get_columns { + my ( $module, $page, $tablename ) = @_; + + my $list = get_yaml; + + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare(q{ + SELECT * + FROM columns_settings + WHERE module = ? + AND page = ? + AND tablename = ? + }); + + $sth->execute( $module, $page, $tablename ); + + while ( my $c = $sth->fetchrow_hashref ) { + my $column = first { $c->{columnname} eq $_->{columnname} } + @{ $list->{modules}{$c->{module}}{$c->{page}}{$c->{tablename}} }; + $column->{is_hidden} = $c->{is_hidden}; + $column->{cannot_be_toggled} = $c->{cannot_be_toggled}; + } + + return $list->{modules}{$module}{$page}{$tablename}; +} + +sub get_modules { + my $list = get_yaml; + + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare(q{ + SELECT * + FROM columns_settings + }); + + $sth->execute; + while ( my $c = $sth->fetchrow_hashref ) { + my $column = first { $c->{columnname} eq $_->{columnname} } + @{ $list->{modules}{$c->{module}}{$c->{page}}{$c->{tablename}} }; + $column->{is_hidden} = $c->{is_hidden}; + $column->{cannot_be_toggled} = $c->{cannot_be_toggled}; + } + + return $list->{modules}; +} + +sub update_columns { + my ( $params ) = @_; + my $columns = $params->{columns}; + my $dbh = C4::Context->dbh; + my $sth_check = $dbh->prepare(q{ + SELECT COUNT(*) + FROM columns_settings + WHERE module = ? + AND page = ? + AND tablename = ? + AND columnname = ? + }); + my $sth_update = $dbh->prepare(q{ + UPDATE columns_settings + SET is_hidden = ?, cannot_be_toggled = ? + WHERE module = ? + AND page = ? + AND tablename = ? + AND columnname = ? + }); + + my $sth_insert = $dbh->prepare(q{ + INSERT INTO columns_settings (is_hidden, cannot_be_toggled, module, page, tablename, columnname ) + VALUES ( ?, ?, ?, ?, ?, ? ) + }); + + + for my $c ( @$columns ) { + $sth_check->execute( $c->{module}, $c->{page}, $c->{tablename}, $c->{columnname} ); + if ( $sth_check->fetchrow ) { + $sth_update->execute( + $c->{is_hidden}, + $c->{cannot_be_toggled}, + $c->{module}, + $c->{page}, + $c->{tablename}, + $c->{columnname} + ); + } else { + $sth_insert->execute( + $c->{is_hidden}, + $c->{cannot_be_toggled}, + $c->{module}, + $c->{page}, + $c->{tablename}, + $c->{columnname} + ); + } + } +} + +1; diff --git a/Koha/Template/Plugin/ColumnsSettings.pm b/Koha/Template/Plugin/ColumnsSettings.pm new file mode 100644 index 0000000..5c81d2a --- /dev/null +++ b/Koha/Template/Plugin/ColumnsSettings.pm @@ -0,0 +1,67 @@ +package Koha::Template::Plugin::ColumnsSettings; + +# Copyright BibLibre 2013 + +# This file is part of Koha. +# +# Koha is free software; you can redistribute it and/or modify it under the +# terms of the GNU General Public License as published by the Free Software +# Foundation; either version 2 of the License, or (at your option) any later +# version. +# +# Koha is distributed in the hope that it will be useful, but WITHOUT ANY +# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +use Modern::Perl; + +use Template::Plugin; +use base qw( Template::Plugin ); + +use YAML qw( LoadFile ); +use JSON qw( to_json ); + +use C4::Context qw( config ); +use C4::Utils::DataTables::ColumnsSettings; + +=pod + +This plugin allows to get the column configuration for a table. + +First, include the line '[% USE Tables %]' at the top +of the template to enable the plugin. + +To use, call ColumnsSettings.GetTables with the module and the page where the template is called. + +For example: [% ColumnsSettings.GetTables( 'circ', 'circulation' ) %] + +=cut + +sub GetTables { + my ( $self, $module, $page, $format ) = @_; + $format //= q{}; + + my $columns = C4::Utils::DataTables::ColumnsSettings::get_tables( $module, $page ); + + return $format eq 'json' + ? to_json( $columns ) + : $columns +} + +sub GetColumns { + my ( $self, $module, $page, $table, $format ) = @_; + $format //= q{}; + + my $columns = C4::Utils::DataTables::ColumnsSettings::get_columns( $module, $page, $table ); + + return $format eq 'json' + ? to_json( $columns ) + : $columns +} + +1; + diff --git a/admin/columns_settings.pl b/admin/columns_settings.pl new file mode 100755 index 0000000..8872440 --- /dev/null +++ b/admin/columns_settings.pl @@ -0,0 +1,59 @@ +#!/usr/bin/perl + +use Modern::Perl; +use CGI; +use YAML qw( LoadFile ); +use C4::Auth; +use C4::Context; +use C4::Output; +use C4::Utils::DataTables::ColumnsSettings qw( get_modules ); +my $input = new CGI; + +my ($template, $loggedinuser, $cookie) + = get_template_and_user({template_name => "admin/columns_settings.tt", + query => $input, + type => "intranet", + authnotrequired => 0, + flagsrequired => {parameters => 'parameters_remaining_permissions'}, + debug => 1, + }); + + +my $action = $input->param('action') // 'list'; + +if ( $action eq 'save' ) { + my $module = $input->param("module"); + + my @columnids = $input->param("columnid"); + my @columns; + for my $columnid ( @columnids ) { + next unless $columnid =~ m|^([^_]*)_([^_]*)_(.*)$|; + my $is_hidden = $input->param($columnid.'_hidden') // 0; + my $cannot_be_toggled = $input->param($columnid.'_cannot_be_toggled') // 0; + push @columns, + { + module => $module, + page => $1, + tablename => $2, + columnname => $3, + is_hidden => $is_hidden, + cannot_be_toggled => $cannot_be_toggled, + }; + } + + + C4::Utils::DataTables::ColumnsSettings::update_columns( + { + columns => \@columns, + } + ); + + $action = 'list'; +} + +if ( $action eq 'list' ) { + my $modules = C4::Utils::DataTables::ColumnsSettings::get_modules; + $template->param( modules => $modules ); +} + +output_html_with_http_headers $input, $cookie, $template->output; diff --git a/admin/columns_settings.yml b/admin/columns_settings.yml new file mode 100644 index 0000000..86efe73 --- /dev/null +++ b/admin/columns_settings.yml @@ -0,0 +1,52 @@ +modules: + circ: + circulation: + issuest: + - + columnname: due_date + - + columnname: title + - + columnname: item_type + - + columnname: checked_out_on + - + columnname: checked_out_from + - + columnname: call_no + - + columnname: charge + - + columnname: price + - + columnname: renew + cannot_be_toggled: 1 + cannot_be_modified: 1 + - + columnname: check_in + cannot_be_toggled: 1 + cannot_be_modified: 1 + - + columnname: export + cannot_be_toggled: 1 + cannot_be_modified: 1 + + holdst: + - + columnname: hold_date + - + columnname: title + - + columnname: call_number + - + columnname: barcode + - + columnname: priority + - + columnname: delete + cannot_be_toggled: 1 + cannot_be_modified: 1 + - + columnname: info + cannot_be_toggled: 1 + cannot_be_modified: 1 diff --git a/circ/circulation.pl b/circ/circulation.pl index 9769731..26ea992 100755 --- a/circ/circulation.pl +++ b/circ/circulation.pl @@ -388,6 +388,7 @@ if ($borrowernumber) { $getreserv{transfered} = 0; $getreserv{nottransfered} = 0; + $getreserv{reservedate_sort} = $num_res->{'reservedate'}; $getreserv{reservedate} = format_date( $num_res->{'reservedate'} ); $getreserv{reserve_id} = $num_res->{'reserve_id'}; $getreserv{title} = $getiteminfo->{'title'}; diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 136104f..145885d 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -3393,6 +3393,20 @@ CREATE TABLE IF NOT EXISTS marc_modification_template_actions ( CONSTRAINT `mmta_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `marc_modification_templates` (`template_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table `columns_settings` +-- + +CREATE TABLE IF NOT EXISTS columns_settings ( + module varchar(255) NOT NULL, + page varchar(255) NOT NULL, + tablename varchar(255) NOT NULL, + columnname varchar(255) NOT NULL, + cannot_be_toggled int(1) NOT NULL DEFAULT 0, + is_hidden int(1) NOT NULL DEFAULT 0, + PRIMARY KEY(module, page, tablename, columnname) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 80ab210..39a4e40 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -7912,6 +7912,30 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } + + + + + + +$DBversion = "3.15.00.XXX"; +if ( CheckVersion($DBversion) ) { + $dbh->do(q{ + CREATE TABLE IF NOT EXISTS columns_settings ( + module varchar(255) NOT NULL, + page varchar(255) NOT NULL, + tablename varchar(255) NOT NULL, + columnname varchar(255) NOT NULL, + cannot_be_toggled int(1) NOT NULL DEFAULT 0, + is_hidden int(1) NOT NULL DEFAULT 0, + PRIMARY KEY(module, page, tablename, columnname) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8 + }); + print "Upgrade to $DBversion done (Bug 10212 - Create new table columns_settings)\n"; + SetVersion ($DBversion); +} + + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/columns_settings.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/columns_settings.inc new file mode 100644 index 0000000..5a62030 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/columns_settings.inc @@ -0,0 +1,27 @@ +[% USE ColumnsSettings %] + + diff --git a/koha-tmpl/intranet-tmpl/prog/en/js/datatables.js b/koha-tmpl/intranet-tmpl/prog/en/js/datatables.js index c2a2cf8..dfa7caf 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/js/datatables.js +++ b/koha-tmpl/intranet-tmpl/prog/en/js/datatables.js @@ -23,7 +23,7 @@ var dataTablesDefaults = { "sSearch" : window.MSG_DT_SEARCH || "Search:", "sZeroRecords" : window.MSG_DT_ZERO_RECORDS || "No matching records found" }, - "sDom": '<"top pager"ilpf>t<"bottom pager"ip>' + "sDom": 'C<"top pager"ilpf>t<"bottom pager"ip>' }; diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt index 46a3b3a..5bcaf83 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/admin-home.tt @@ -107,6 +107,8 @@
Define which servers to query for MARC data in the integrated Z39.50 client.
Did you mean?
Choose which plugins to use to suggest searches to patrons and staff.
+
Configure columns
+
Hide or show columns for tables.
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/columns_settings.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/columns_settings.tt new file mode 100644 index 0000000..139d034 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/columns_settings.tt @@ -0,0 +1,131 @@ +[% BLOCK pagelist %] +
+
+ + + [% FOR pagename IN module.keys %] +
[% pagename %]
+ [% SET tables = module %] + [% FOR tablename IN tables.$pagename.keys.sort %] + + + + + [% FOR column IN tables.$pagename.$tablename %] + + + + + + [% END %] + +
[% tablename %]
Column nameIs Hidden by defaultCannot be toggled
+ [% column.columnname %] + + + [% IF column.is_hidden %] + [% IF column.cannot_be_modified %] + + + [% ELSE %] + + [% END %] + [% ELSE %] + [% IF column.cannot_be_modified %] + + + [% ELSE %] + + [% END %] + [% END %] + + [% IF column.cannot_be_toggled %] + [% IF column.cannot_be_modified %] + + + [% ELSE %] + + [% END %] + [% ELSE %] + [% IF column.cannot_be_modified %] + + + [% ELSE %] + + [% END %] + [% END %] +
+ [% END %] + [% END %] + +
+
+[% END %] + +[% INCLUDE 'doc-head-open.inc' %] +Koha › Administration › Tables +[% INCLUDE 'doc-head-close.inc' %] + + + +[% INCLUDE 'header.inc' %] +[% INCLUDE 'cat-search.inc' %] + + +
+
+
+
+

Columns settings

+
+

Acquisition

+
+ Acquisition tables +
+ +

Administration

+
+ Administration tables +
+ +

Authorities

+
+ Authorities tables +
+ +

Acquisition

+
+ Acquisition tables +
+ +

Catalogue

+
+ Catalogue tables +
+ +

Cataloguing

+
+ Cataloguing tables +
+ +

Circulation

+
+

Circulation tables

+ [% PROCESS pagelist module=modules.circ modulename="circ" %] +
+
+
+
+ +
+ [% INCLUDE 'admin-menu.inc' %] +
+
+[% INCLUDE 'intranet-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/circulation.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/circ/circulation.tt index a522236..6ae351d 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/circulation.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/circ/circulation.tt @@ -1,5 +1,6 @@ [% USE Branches %] [% USE KohaDates %] +[% USE ColumnsSettings %] [% IF ( export_remove_fields OR export_with_csv_profile ) %] [% SET exports_enabled = 1 %] [% END %] @@ -13,10 +14,15 @@ [% INCLUDE 'doc-head-close.inc' %] [% INCLUDE 'calendar.inc' %] -[% IF ( UseTablesortForCirc ) %] - -[% INCLUDE 'datatables-strings.inc' %] -[% END %] +[% IF ( UseTablesortForCirc ) %] + + + + [% INCLUDE 'datatables-strings.inc' %] + + + [% INCLUDE 'columns_settings.inc' %] +[% END %] @@ -27,7 +33,7 @@ var MSG_EXPORT_SELECT_CHECKOUTS = _("You must select checkout(s) to export"); [% IF ( UseTablesortForCirc && dateformat == 'metric' ) %]dt_add_type_uk_date();[% END %] [% IF ( borrowernumber ) %]if($.cookie("holdfor") != [% borrowernumber %]){ $.cookie("holdfor",null, { path: "/", expires: 0 }); }[% ELSE %]$.cookie("holdfor",null, { path: "/", expires: 0 });[% END %] [% UNLESS ( borrowernumber ) %][% UNLESS ( CGIselectborrower ) %]window.onload=function(){ $('#findborrower').focus(); };[% END %][% END %] - $(document).ready(function() { + $(document).ready(function() { $('#patronlists').tabs([% IF ( UseTablesortForCirc ) %]{ // Correct table sizing for tables hidden in tabs // http://www.datatables.net/examples/api/tabs_and_scrolling.html @@ -38,18 +44,44 @@ var MSG_EXPORT_SELECT_CHECKOUTS = _("You must select checkout(s) to export"); } } }[% END %]); + + [% IF ( UseTablesortForCirc ) %] - $("#issuest").dataTable($.extend(true, {}, dataTablesDefaults, { - "sDom": 't', - "aaSorting": [], - "aoColumnDefs": [ - { "aTargets": [ -1, -2[% IF ( exports_enabled ) %], -3[% END %] ], "bSortable": false, "bSearchable": false } - ], - "aoColumns": [ - { "sType": "title-string" },{ "sType": "html" },null,{ "sType": "title-string" },null,null,null,null,null,null[% IF ( exports_enabled ) %],null[% END %] - ], - "bPaginate": false - })); + + if ( $("#issuest").length > 0 ) { + columns_settings = [% ColumnsSettings.GetColumns( 'circ', 'circulation', 'issuest', 'json' ) %] + [% UNLESS exports_enabled %] + columns_settings = jQuery.grep(columns_settings, function(column) { + return column['columnname'] != 'export'; + } ); + [% END %] + var issuest = KohaTable("#issuest", { + "sDom": 'C<"clearfix">t', + "aaSorting": [], + "aoColumnDefs": [ + { "aTargets": [ -1, -2[% IF ( exports_enabled ) %], -3[% END %] ], "bSortable": false, "bSearchable": false }, + ], + "aoColumns": [ + { "sType": "title-string" },{ "sType": "html" },null,{ "sType": "title-string" },null,null,null,null,null,null[% IF ( exports_enabled ) %],null[% END %] + ], + "bPaginate": false, + }, columns_settings); + } + + if ( $("#holdst").length > 0 ) { + columns_settings = [% ColumnsSettings.GetColumns( 'circ', 'circulation', 'holdst', 'json' ) %] + var holdst = KohaTable("#holdst", { + "sDom": 'C<"clearfix">t', + "aaSorting": [], + "aoColumnDefs": [ + { "aTargets": [ -1, -2 ], "bSortable": false, "bSearchable": false }, + ], + "aoColumns": [ + { "sType": "natural" },{ "sType": "title-string" },{ "sType": "html" },null,null,null,null,null + ], + "bPaginate": false, + }, columns_settings); + } $("#relissuest").dataTable($.extend(true, {}, dataTablesDefaults, { "sDom": 't', @@ -1035,7 +1067,7 @@ No patron matched [% message %] [% FOREACH reservloo IN reservloop %] - [% reservloo.reservedate %] + [% reservloo.reservedate %] [% reservloo.title |html %][% FOREACH subtitl IN reservloo.subtitle %] [% subtitl.subfield %][% END %][% IF ( reservloo.author ) %], by [% reservloo.author %][% END %] [% reservloo.itemcallnumber %] [% IF ( reservloo.barcodereserv ) %]Item [% reservloo.barcodereserv %] -- 1.7.10.4