From a7ccfbc629821e37833ae658443db64d293ce94b Mon Sep 17 00:00:00 2001 From: Henri-Damien LAURENT Date: Wed, 16 Sep 2009 21:31:30 +0200 Subject: [PATCH] MT 1587 : CSV export for cart and shelves, with the ability to define different export profiles Signed-off-by: Henri-Damien LAURENT --- C4/Csv.pm | 80 ++++++++++++ C4/Record.pm | 98 ++++++++++++++ .../mysql/en/mandatory/userpermissions.sql | 3 +- .../fr-FR/1-Obligatoire/userpermissions.sql | 3 + installer/data/mysql/updatedatabase.pl | 8 ++ .../prog/en/modules/tools/csv-profiles.tmpl | 98 ++++++++++++++ .../prog/en/modules/tools/tools-home.tmpl | 6 + .../prog/en/modules/opac-downloadcart.tmpl | 4 + .../prog/en/modules/opac-downloadshelf.tmpl | 4 + opac/opac-downloadcart.pl | 10 ++ opac/opac-downloadshelf.pl | 11 +- tools/csv-profiles.pl | 121 ++++++++++++++++++ 12 files changed, 444 insertions(+), 2 deletions(-) create mode 100644 C4/Csv.pm create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/tools/csv-profiles.tmpl create mode 100755 tools/csv-profiles.pl diff --git a/C4/Csv.pm b/C4/Csv.pm new file mode 100644 index 0000000000..3c1c56d535 --- /dev/null +++ b/C4/Csv.pm @@ -0,0 +1,80 @@ +package C4::Csv; + +# Copyright 2008 BibLibre +# +# 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., 59 Temple Place, +# Suite 330, Boston, MA 02111-1307 USA +# +# + +use C4::Context; +use vars qw($VERSION @ISA @EXPORT); + +# set the version for version checking +$VERSION = 3.00; + +@ISA = qw(Exporter); + +# only export API methods + +@EXPORT = qw( + &GetCsvProfiles + &GetCsvProfilesLoop + &GetMarcFieldsForCsv +); + +my $dbh = C4::Context->dbh; + +# Returns all informations about csv profiles +sub GetCsvProfiles { + + my $query = "SELECT * FROM export_format"; + + $sth = $dbh->prepare($query); + $sth->execute; + + $sth->fetchall_arrayref({}); + +} + +# Returns fields to extract for the given csv profile +sub GetMarcFieldsForCsv { + + my ($id) = @_; + + my $query = "SELECT marcfields FROM export_format WHERE export_format_id=?"; + + $sth = $dbh->prepare($query); + $sth->execute($id); + + return ($sth->fetchrow_hashref)->{marcfields}; + + +} + +# Returns informations aboout csv profiles suitable for html templates +sub GetCsvProfilesLoop { + # List of existing profiles + my $sth; + my $query = "SELECT export_format_id, profile FROM export_format"; + $sth = $dbh->prepare($query); + $sth->execute(); + return $sth->fetchall_arrayref({}); + +} + + + +1; diff --git a/C4/Record.pm b/C4/Record.pm index 2c96756aeb..a892c5c2fa 100644 --- a/C4/Record.pm +++ b/C4/Record.pm @@ -29,6 +29,9 @@ use Biblio::EndnoteStyle; use Unicode::Normalize; # _entity_encode use XML::LibXSLT; use XML::LibXML; +use C4::Biblio; #marc2bibtex +use C4::Csv; #marc2csv +use Text::CSV; #marc2csv use vars qw($VERSION @ISA @EXPORT); @@ -46,6 +49,8 @@ $VERSION = 3.00; &marcxml2marc &marc2dcxml &marc2modsxml + &marc2bibtex + &marc2csv &html2marcxml &html2marc @@ -322,6 +327,99 @@ sub marc2endnote { } +=head2 marc2csv - Convert from UNIMARC to CSV + +=over 4 + +my ($csv) = marc2csv($record, $csvprofileid); + +Returns a CSV scalar + +=over 2 + +C<$record> - a MARC::Record object + +C<$csvprofileid> - the id of the CSV profile to use for the export (see export_format.export_format_id and the GetCsvProfiles function in C4::Csv) + +=back + +=back + +=cut + + +sub marc2csv { + my ($record, $id, $header) = @_; + my $output; + my $csv = Text::CSV->new(); + + # Get the information about the csv profile + my $marcfieldslist = GetMarcFieldsForCsv($id); + + # Getting the marcfields as an array + my @marcfields = split('\|', $marcfieldslist); + + # If we have to insert the headers + if ($header) { + my @marcfieldsheaders; + + my $dbh = C4::Context->dbh; + + # For each field or subfield + foreach (@marcfields) { + # We get the matching tag name + if (index($_, '$') > 0) { + my ($fieldtag, $subfieldtag) = split('\$', $_); + my $query = "SELECT liblibrarian FROM marc_subfield_structure WHERE tagfield=? AND tagsubfield=?"; + my $sth = $dbh->prepare($query); + $sth->execute($fieldtag, $subfieldtag); + my @results = $sth->fetchrow_array(); + push @marcfieldsheaders, @results[0]; + } else { + my $query = "SELECT liblibrarian FROM marc_tag_structure WHERE tagfield=?"; + my $sth = $dbh->prepare($query); + $sth->execute($_); + my @results = $sth->fetchrow_array(); + push @marcfieldsheaders, @results[0]; + } + } + $csv->combine(@marcfieldsheaders); + $output = $csv->string() . "\n"; + } + + # For each marcfield to export + my @fieldstab; + foreach my $marcfield (@marcfields) { + # If it is a subfield + if (index($marcfield, '$') > 0) { + my ($fieldtag, $subfieldtag) = split('\$', $marcfield); + my @fields = $record->field($fieldtag); + my @tmpfields; + + # For each field + foreach my $field (@fields) { + + # We take every matching subfield + my @subfields = $field->subfield($subfieldtag); + foreach my $subfield (@subfields) { + push @tmpfields, $subfield; + } + } + push (@fieldstab, join(',', @tmpfields)); + # Or a field + } else { + my @fields = ($record->field($marcfield)); + push (@fieldstab, join(',', map($_->as_string(), @fields))); + } + }; + + $csv->combine(@fieldstab); + $output .= $csv->string() . "\n"; + + return $output; + +} + =head2 html2marcxml diff --git a/installer/data/mysql/en/mandatory/userpermissions.sql b/installer/data/mysql/en/mandatory/userpermissions.sql index 80f060dd62..6f4c23004f 100644 --- a/installer/data/mysql/en/mandatory/userpermissions.sql +++ b/installer/data/mysql/en/mandatory/userpermissions.sql @@ -18,6 +18,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES (13, 'schedule_tasks', 'Schedule tasks to run'), (13, 'batchmod', 'Perform batch modification of items'), (13, 'batchdel', 'Perform batch deletion of items'), + (13, 'manage_csv_profiles', 'Manage CSV export profiles') (11, 'vendors_manage', 'Manage vendors'), (11, 'contracts_manage', 'Manage contracts'), (11, 'period_manage', 'Manage periods'), @@ -28,4 +29,4 @@ INSERT INTO permissions (module_bit, code, description) VALUES (11, 'group_manage', 'Manage orders & basketgroups'), (11, 'order_receive', 'Manage orders & basket'), (11, 'budget_add_del', 'Add and delete budgets (but cant modify budgets)') -; \ No newline at end of file +; diff --git a/installer/data/mysql/fr-FR/1-Obligatoire/userpermissions.sql b/installer/data/mysql/fr-FR/1-Obligatoire/userpermissions.sql index 96f2577eaf..e54dc5d159 100644 --- a/installer/data/mysql/fr-FR/1-Obligatoire/userpermissions.sql +++ b/installer/data/mysql/fr-FR/1-Obligatoire/userpermissions.sql @@ -26,4 +26,7 @@ INSERT INTO permissions (module_bit, code, description) VALUES (11, 'group_manage', 'Gérer les commandes et les bons de commande'), (11, 'order_receive', 'Gérer les réceptions') (11, 'budget_add_del', 'Ajouter et supprimer les budgets (mais pas modifier)') + (13, 'manage_csv_profiles', 'Manage CSV export profiles') + (13, 'batch_mod', 'Modification Par lot des exemplaires') + (13, 'batch_del', 'Suppression par lot des exemplaires') ; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 5009cb9287..7dba9d5fc5 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -2688,6 +2688,13 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { print "Upgrade to $DBversion done ( Added ShowPatronImageInWebBasedSelfCheck system preference )\n"; SetVersion ($DBversion); } + +$DBversion = "3.01.00.062"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 13, 'manage_csv_profiles', 'Manage CSV export profiles')"); + print "Upgrade to $DBversion done (added permissions for csv export profiles)\n"; +} + =item Acquisitions update @@ -3032,6 +3039,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { } + =item DropAllForeignKeys($table) Drop all foreign keys of the table $table diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/csv-profiles.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/csv-profiles.tmpl new file mode 100644 index 0000000000..f0a072ee7b --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/csv-profiles.tmpl @@ -0,0 +1,98 @@ + + + Koha › Catalog › Profile for CSV export + + + + + + + + + + + + + +
+
+
+
+ + +

The new CSV profile "" has been successfully created.

+

The CSV profile has been successfully modified.

+

The CSV profile has been successfully deleted.

+ +

The new CSV profile "" has not been created.

+

The CSV profile has not been modified.

+

The CSV profile has not been deleted.

+ + + +

New profile for CSV export

+ +
+
+ +

+ + +

+ + + +
+ + +
+ + + +

+

Modify or delete an existing profile

+ +
+
+ +

+ + +

+ + +

+ + + + +
+ + " /> + + +
+ + +
+
+
+ + +
+
+ diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/tools-home.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/tools-home.tmpl index 54e9ce4909..090e172423 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/tools-home.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/tools-home.tmpl @@ -44,6 +44,12 @@
Tags
Moderate patron tags
+ + +
CSV Profiles
+
Manage CSV export profiles
+ +
diff --git a/koha-tmpl/opac-tmpl/prog/en/modules/opac-downloadcart.tmpl b/koha-tmpl/opac-tmpl/prog/en/modules/opac-downloadcart.tmpl index aa71a3d473..85a01368f2 100644 --- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-downloadcart.tmpl +++ b/koha-tmpl/opac-tmpl/prog/en/modules/opac-downloadcart.tmpl @@ -11,6 +11,10 @@ + + + + " /> diff --git a/koha-tmpl/opac-tmpl/prog/en/modules/opac-downloadshelf.tmpl b/koha-tmpl/opac-tmpl/prog/en/modules/opac-downloadshelf.tmpl index a36caf0e22..52203f5037 100644 --- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-downloadshelf.tmpl +++ b/koha-tmpl/opac-tmpl/prog/en/modules/opac-downloadshelf.tmpl @@ -11,6 +11,10 @@ + + + + " /> diff --git a/opac/opac-downloadcart.pl b/opac/opac-downloadcart.pl index 25fe7ce3da..fe80f3008c 100755 --- a/opac/opac-downloadcart.pl +++ b/opac/opac-downloadcart.pl @@ -31,6 +31,7 @@ use C4::Output; use C4::VirtualShelves; use C4::Record; use C4::Ris; +use C4::Csv; use utf8; use open qw( :std :utf8); my $query = new CGI; @@ -57,6 +58,7 @@ if ($bib_list && $format) { my $output; # retrieve biblios from shelf + my $firstpass = 1; foreach my $biblio (@bibs) { my $record = GetMarcBiblio($biblio); @@ -65,9 +67,16 @@ if ($bib_list && $format) { case "iso2709" { $output .= $record->as_usmarc(); } case "ris" { $output .= marc2ris($record); } case "bibtex" { $output .= marc2bibtex($record, $biblio); } + # We're in the case of a csv profile (firstpass is used for headers printing) : + case /^\d+$/ { $output .= marc2csv($record, $format, $firstpass); } } + $firstpass = 0; + } + # If it was a CSV export we change the format after the export so the file extension is fine + $format = "csv" if ($format =~ m/^\d+$/); + print $query->header( -type => 'application/octet-stream', -'Content-Transfer-Encoding' => 'binary', @@ -75,6 +84,7 @@ if ($bib_list && $format) { print $output; } else { + $template->param(csv_profiles => GetCsvProfilesLoop()); $template->param(bib_list => $bib_list); output_html_with_http_headers $query, $cookie, $template->output; } diff --git a/opac/opac-downloadshelf.pl b/opac/opac-downloadshelf.pl index b377109ca2..976412b1de 100755 --- a/opac/opac-downloadshelf.pl +++ b/opac/opac-downloadshelf.pl @@ -31,6 +31,7 @@ use C4::Output; use C4::VirtualShelves; use C4::Record; use C4::Ris; +use C4::Csv; use utf8; use open qw( :std :utf8); my $query = new CGI; @@ -57,6 +58,7 @@ if ($shelfid && $format) { my $output; # retrieve biblios from shelf + my $firstpass = 1; foreach my $biblio (@$items) { my $biblionumber = $biblio->{biblionumber}; @@ -66,16 +68,23 @@ if ($shelfid && $format) { case "iso2709" { $output .= $record->as_usmarc(); } case "ris" { $output .= marc2ris($record); } case "bibtex" { $output .= marc2bibtex($record, $biblionumber); } + # We're in the case of a csv profile (firstpass is used for headers printing) : + case /^\d+$/ { $output .= marc2csv($record, $format, $firstpass); } } + $firstpass = 0; } + # If it was a CSV export we change the format after the export so the file extension is fine + $format = "csv" if ($format =~ m/^\d+$/); + print $query->header( -type => 'application/octet-stream', -'Content-Transfer-Encoding' => 'binary', -attachment=>"shelf.$format"); print $output; -} else { +} else { + $template->param(csv_profiles => GetCsvProfilesLoop()); $template->param(shelfid => $shelfid); output_html_with_http_headers $query, $cookie, $template->output; } diff --git a/tools/csv-profiles.pl b/tools/csv-profiles.pl new file mode 100755 index 0000000000..1e037f3aab --- /dev/null +++ b/tools/csv-profiles.pl @@ -0,0 +1,121 @@ +#!/usr/bin/perl + +# Copyright 2009 BibLibre +# +# 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., 59 Temple Place, +# Suite 330, Boston, MA 02111-1307 USA + +=head1 NAME + +csv-profile.pl : Defines a CSV export profile + +=head1 SYNOPSIS + + +=head1 DESCRIPTION + +This script allow the user to define a new profile for CSV export + +=head1 FUNCTIONS + +=over 2 + +=cut + +use strict; +use Data::Dumper; + +use C4::Auth; +use C4::Context; +use C4::Output; +use CGI; +use C4::Koha; +use C4::Csv; + +my $input = new CGI; +my $dbh = C4::Context->dbh; + +# open template +my ( $template, $loggedinuser, $cookie ) = get_template_and_user( + { + template_name => "tools/csv-profiles.tmpl", + query => $input, + type => "intranet", + authnotrequired => 0, + flagsrequired => { tools => 'manage_csv_profiles' }, + debug => 1, + } +); + + +my $profile_name = $input->param("profile_name"); +my $profile_description = $input->param("profile_description"); +my $profile_content = $input->param("profile_content"); +my $action = $input->param("action"); +my $delete = $input->param("delete"); +my $id = $input->param("id"); +if ($delete) { $action = "delete"; } + +if ($profile_name && $profile_content && $profile_description && $action) { + my $rows; + + if ($action eq "create") { + my $query = "INSERT INTO export_format(export_format_id, profile, description, marcfields) VALUES (NULL, ?, ?, ?)"; + my $sth = $dbh->prepare($query); + $rows = $sth->execute($profile_name, $profile_description, $profile_content); + + } + + if ($action eq "edit") { + my $query = "UPDATE export_format SET description=?, marcfields=? WHERE export_format_id=? LIMIT 1"; + my $sth = $dbh->prepare($query); + $rows = $sth->execute($profile_description, $profile_content, $profile_name); + + } + + if ($action eq "delete") { + my $query = "DELETE FROM export_format WHERE export_format_id=? LIMIT 1"; + my $sth = $dbh->prepare($query); + $rows = $sth->execute($profile_name); + + } + + $rows ? $template->param(success => 1) : $template->param(error => 1); + $template->param(profile_name => $profile_name); + $template->param(action => $action); + +} + + # If a profile has been selected for modification + if ($id) { + my $query = "SELECT export_format_id, profile, description, marcfields FROM export_format WHERE export_format_id = ?"; + my $sth; + $sth = $dbh->prepare($query); + + $sth->execute($id); + my $selected_profile = $sth->fetchrow_arrayref(); + $template->param( + selected_profile_id => $selected_profile->[0], + selected_profile_name => $selected_profile->[1], + selected_profile_description => $selected_profile->[2], + selected_profile_marcfields => $selected_profile->[3] + ); + + } + + # List of existing profiles + $template->param(existing_profiles => GetCsvProfilesLoop()); + +output_html_with_http_headers $input, $cookie, $template->output; -- 2.39.5