From 0865bd7727a69edadb4fb22d02321447fce14b37 Mon Sep 17 00:00:00 2001 From: Matthias Meusburger Date: Fri, 15 Jan 2010 17:42:43 +0100 Subject: [PATCH] MT2116: Addons to the CSV export Add user-defined headers Add CSV, field and subfield separator selection --- C4/Csv.pm | 13 +++ C4/Record.pm | 79 +++++++++++++----- installer/data/mysql/updatedatabase.pl | 12 +++ .../prog/en/modules/tools/csv-profiles.tmpl | 82 ++++++++++++++++++- test.pl | 30 +++++++ tools/csv-profiles.pl | 21 +++-- 6 files changed, 208 insertions(+), 29 deletions(-) create mode 100644 test.pl diff --git a/C4/Csv.pm b/C4/Csv.pm index 3c1c56d535..c010f33521 100644 --- a/C4/Csv.pm +++ b/C4/Csv.pm @@ -31,6 +31,7 @@ $VERSION = 3.00; @EXPORT = qw( &GetCsvProfiles + &GetCsvProfile &GetCsvProfilesLoop &GetMarcFieldsForCsv ); @@ -49,6 +50,18 @@ sub GetCsvProfiles { } +# Returns all informations about a given csv profile +sub GetCsvProfile { + my ($id) = @_; + my $dbh = C4::Context->dbh; + my $query = "SELECT * FROM export_format WHERE export_format_id=?"; + + $sth = $dbh->prepare($query); + $sth->execute($id); + + return ($sth->fetchrow_hashref); +} + # Returns fields to extract for the given csv profile sub GetMarcFieldsForCsv { diff --git a/C4/Record.pm b/C4/Record.pm index 1574af1ba9..4300f9a532 100644 --- a/C4/Record.pm +++ b/C4/Record.pm @@ -341,6 +341,8 @@ 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) +C<$header> - true if the headers are to be printed (typically at first pass) + =back =back @@ -351,36 +353,72 @@ C<$csvprofileid> - the id of the CSV profile to use for the export (see export_f sub marc2csv { my ($record, $id, $header) = @_; my $output; - my $csv = Text::CSV->new(); # Get the information about the csv profile - my $marcfieldslist = GetMarcFieldsForCsv($id); + my $profile = GetCsvProfile($id); + + # Getting separators + my $csvseparator = $profile->{csv_separator} || ','; + my $fieldseparator = $profile->{field_separator} || '#'; + my $subfieldseparator = $profile->{subfield_separator} || '|'; + + # TODO: Be more generic (in case we have to handle other protected chars or more separators) + if ($csvseparator eq '\t') { $csvseparator = "\t" } + if ($fieldseparator eq '\t') { $fieldseparator = "\t" } + if ($subfieldseparator eq '\t') { $subfieldseparator = "\t" } + + # Init CSV + my $csv = Text::CSV->new({ sep_char => $csvseparator }); + + # Getting the marcfields + my $marcfieldslist = $profile->{marcfields}; # Getting the marcfields as an array - my @marcfields = split('\|', $marcfieldslist); + my @marcfieldsarray = split('\|', $marcfieldslist); + + # Separating the marcfields from the the user-supplied headers + my @marcfields; + foreach (@marcfieldsarray) { + my @result = split('=', $_); + if (scalar(@result) == 2) { + push @marcfields, { header => $result[0], field => $result[1] }; + } else { + push @marcfields, { field => $result[0] } + } + } # 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]; + + my $field = $_->{field}; + + # If we have a user-supplied header, we use it + if (exists $_->{header}) { + push @marcfieldsheaders, $_->{header}; } 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]; +warn "else"; + # If not, we get the matching tag name from koha + if (index($field, '$') > 0) { + my ($fieldtag, $subfieldtag) = split('\$', $field); + 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(); +warn "subfield $fieldtag, $subfieldtag"; + push @marcfieldsheaders, $results[0]; + } else { + my $query = "SELECT liblibrarian FROM marc_tag_structure WHERE tagfield=?"; + my $sth = $dbh->prepare($query); + $sth->execute($field); + my @results = $sth->fetchrow_array(); +warn "field $results[0]"; + push @marcfieldsheaders, $results[0]; + } } } $csv->combine(@marcfieldsheaders); @@ -389,7 +427,8 @@ sub marc2csv { # For each marcfield to export my @fieldstab; - foreach my $marcfield (@marcfields) { + foreach (@marcfields) { + my $marcfield = $_->{field}; # If it is a subfield if (index($marcfield, '$') > 0) { my ($fieldtag, $subfieldtag) = split('\$', $marcfield); @@ -405,11 +444,11 @@ sub marc2csv { push @tmpfields, $subfield; } } - push (@fieldstab, join(',', @tmpfields)); + push (@fieldstab, join($subfieldseparator, @tmpfields)); # Or a field } else { my @fields = ($record->field($marcfield)); - push (@fieldstab, join(',', map($_->as_string(), @fields))); + push (@fieldstab, join($fieldseparator, map($_->as_string(), @fields))); } }; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index bcefdccea2..310438fca6 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -2975,6 +2975,18 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { +$DBversion = "3.01.00.101"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do(qq{ + ALTER TABLE `export_format` ADD `csv_separator` VARCHAR( 2 ) NOT NULL AFTER `marcfields` , + ADD `field_separator` VARCHAR( 2 ) NOT NULL AFTER `csv_separator` , + ADD `subfield_separator` VARCHAR( 2 ) NOT NULL AFTER `field_separator` + }); + print "Upgrade done (added separators for csv export)\n"; + SetVersion ($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 index 398ae8dbd2..2d50bdcb8a 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/csv-profiles.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/csv-profiles.tmpl @@ -49,8 +49,48 @@ function reloadPage(p) {

+ + +

+ + + +

+ + + +

+ - + +

You have to define which fields or subfields you want to export, separated by pipes.
+ You can also use your own headers (instead of the ones from koha) by prefixing the field number with an header, followed by the equal sign.
+ Example : Personal name=200|Entry element=210$a|300 +

@@ -59,7 +99,7 @@ function reloadPage(p) {

-

Modify or delete an existing profile

+

Modify or delete an existing profile

@@ -74,6 +114,44 @@ function reloadPage(p) {

+ + +

+ + + + +

+ + + +

+ +

diff --git a/test.pl b/test.pl new file mode 100644 index 0000000000..e4bab7b420 --- /dev/null +++ b/test.pl @@ -0,0 +1,30 @@ +#!/usr/bin/perl + +use strict; +use warnings; + +my $string = $ARGV[0]; + +# Getting the marcfields as an array + my @marcfieldsarray = split('\|', $string); + + # Separating the marcfields from the the user-supplied headers + my @marcfields; + foreach (@marcfieldsarray) { + my @result = split('=', $_); + if (scalar(@result) == 2) { + push @marcfields, { header => $result[0], field => $result[1] }; + } else { + push @marcfields, { field => $result[0] } + } + } + +use Data::Dumper; +print Dumper(@marcfields); + + +foreach (@marcfields) { + print $_->{field}; +} + + diff --git a/tools/csv-profiles.pl b/tools/csv-profiles.pl index 1d54da9e7a..8ba84aee8d 100755 --- a/tools/csv-profiles.pl +++ b/tools/csv-profiles.pl @@ -63,6 +63,9 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user( my $profile_name = $input->param("profile_name"); my $profile_description = $input->param("profile_description"); my $profile_content = $input->param("profile_content"); +my $csv_separator = $input->param("csv_separator"); +my $field_separator = $input->param("field_separator"); +my $subfield_separator = $input->param("subfield_separator"); my $action = $input->param("action"); my $delete = $input->param("delete"); my $id = $input->param("id"); @@ -72,17 +75,17 @@ if ($profile_name && $profile_content && $action) { my $rows; if ($action eq "create") { - my $query = "INSERT INTO export_format(export_format_id, profile, description, marcfields) VALUES (NULL, ?, ?, ?)"; + my $query = "INSERT INTO export_format(export_format_id, profile, description, marcfields, csv_separator, field_separator, subfield_separator) VALUES (NULL, ?, ?, ?, ?, ?, ?)"; my $sth = $dbh->prepare($query); - $rows = $sth->execute($profile_name, $profile_description, $profile_content); + $rows = $sth->execute($profile_name, $profile_description, $profile_content, $csv_separator, $field_separator, $subfield_separator); } if ($action eq "edit") { - my $query = "UPDATE export_format SET description=?, marcfields=? WHERE export_format_id=? LIMIT 1"; + my $query = "UPDATE export_format SET description=?, marcfields=?, csv_separator=?, field_separator=?, subfield_separator=? WHERE export_format_id=? LIMIT 1"; my $sth = $dbh->prepare($query); - $rows = $sth->execute($profile_description, $profile_content, $profile_name); - + $rows = $sth->execute($profile_description, $profile_content, $csv_separator, $field_separator, $subfield_separator, $profile_name); +warn "id $id"; } if ($action eq "delete") { @@ -100,17 +103,21 @@ if ($profile_name && $profile_content && $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 $query = "SELECT export_format_id, profile, description, marcfields, csv_separator, field_separator, subfield_separator FROM export_format WHERE export_format_id = ?"; my $sth; $sth = $dbh->prepare($query); $sth->execute($id); my $selected_profile = $sth->fetchrow_arrayref(); + warn "value : " . $selected_profile->[4]; $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] + selected_profile_marcfields => $selected_profile->[3], + selected_csv_separator => $selected_profile->[4], + selected_field_separator => $selected_profile->[5], + selected_subfield_separator => $selected_profile->[6] ); } -- 2.39.5