From 3b4a1a4fd8a17ae54a1aae0d889ed607fe6439a7 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 4 Jul 2012 17:02:12 +0200 Subject: [PATCH 1/1] Bug 7986: Export issues for patron In the circulation page, you can now export (as csv or iso2709) a list of items which are currently checked out by a borrower. 3 export types: - iso2709 with items: Export the items list in iso2709 format with item informations. - iso2709 without items: Export the items list in iso2709 format without item informations. - CSV: Export the items list based on a csv profil. 2 new system preferences: - DontExportFields: a list of fields not to be export - CsvProfileForExport: The Csv profile name used for the csv export Test plan: - Fill the CsvProfileForExport syspref - go on the borrower circulation page containing checkouts - Select one or more items and export them to the 3 different formats. - check if the result file is what you expected - Test there is no regression with the export authority - Test there is no regression using tools/export.pl with the command line interface --- C4/Biblio.pm | 6 +- C4/Csv.pm | 13 + C4/Record.pm | 59 ++- circ/circulation.pl | 2 + installer/data/mysql/updatedatabase.pl | 18 + .../prog/en/includes/checkouts-table-footer.inc | 2 +- .../intranet-tmpl/prog/en/includes/prefs-menu.inc | 1 + .../en/modules/admin/preferences/circulation.pref | 6 + .../prog/en/modules/admin/preferences/tools.pref | 4 + .../prog/en/modules/circ/circulation.tt | 89 +++- .../intranet-tmpl/prog/en/modules/tools/export.tt | 2 +- tools/export.pl | 717 ++++++++++++-------- 12 files changed, 613 insertions(+), 306 deletions(-) create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/tools.pref diff --git a/C4/Biblio.pm b/C4/Biblio.pm index c479502..6aba0b2 100644 --- a/C4/Biblio.pm +++ b/C4/Biblio.pm @@ -2791,16 +2791,17 @@ sub GetNoZebraIndexes { =head2 EmbedItemsInMarcBiblio - EmbedItemsInMarcBiblio($marc, $biblionumber); + EmbedItemsInMarcBiblio($marc, $biblionumber, $itemnumbers); Given a MARC::Record object containing a bib record, modify it to include the items attached to it as 9XX per the bib's MARC framework. +if $itemnumbers is defined, only specified itemnumbers are embedded =cut sub EmbedItemsInMarcBiblio { - my ($marc, $biblionumber) = @_; + my ($marc, $biblionumber, $itemnumbers) = @_; croak "No MARC record" unless $marc; my $frameworkcode = GetFrameworkCode($biblionumber); @@ -2813,6 +2814,7 @@ sub EmbedItemsInMarcBiblio { my @item_fields; my ( $itemtag, $itemsubfield ) = GetMarcFromKohaField( "items.itemnumber", $frameworkcode ); while (my ($itemnumber) = $sth->fetchrow_array) { + next if $itemnumbers and not grep { $_ == $itemnumber } @$itemnumbers; require C4::Items; my $item_marc = C4::Items::GetMarcItem($biblionumber, $itemnumber); push @item_fields, $item_marc->field($itemtag); diff --git a/C4/Csv.pm b/C4/Csv.pm index 95cf634..74270ff 100644 --- a/C4/Csv.pm +++ b/C4/Csv.pm @@ -35,6 +35,7 @@ $VERSION = 3.07.00.049; @EXPORT = qw( &GetCsvProfiles &GetCsvProfile + &GetCsvProfileId &GetCsvProfilesLoop &GetMarcFieldsForCsv ); @@ -64,6 +65,18 @@ sub GetCsvProfile { return ($sth->fetchrow_hashref); } +# Returns id of csv profile about a given csv profile name +sub GetCsvProfileId { + my ($name) = @_; + my $dbh = C4::Context->dbh; + my $query = "SELECT export_format_id FROM export_format WHERE profile=?"; + + $sth = $dbh->prepare($query); + $sth->execute($name); + + return ( $sth->fetchrow ); +} + # Returns fields to extract for the given csv profile sub GetMarcFieldsForCsv { diff --git a/C4/Record.pm b/C4/Record.pm index a07fcfc..f75195f 100644 --- a/C4/Record.pm +++ b/C4/Record.pm @@ -358,7 +358,7 @@ sub marc2endnote { =head2 marc2csv - Convert several records from UNIMARC to CSV - my ($csv) = marc2csv($biblios, $csvprofileid); + my ($csv) = marc2csv($biblios, $csvprofileid, $itemnumbers); Pre and postprocessing can be done through a YAML file @@ -368,10 +368,12 @@ C<$biblio> - a list of biblionumbers 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<$itemnumbers> - a list of itemnumbers to export + =cut sub marc2csv { - my ($biblios, $id) = @_; + my ($biblios, $id, $itemnumbers) = @_; my $output; my $csv = Text::CSV::Encoded->new(); @@ -386,9 +388,17 @@ sub marc2csv { eval $preprocess if ($preprocess); my $firstpass = 1; - foreach my $biblio (@$biblios) { - $output .= marcrecord2csv($biblio, $id, $firstpass, $csv, $fieldprocessing) ; - $firstpass = 0; + if ( $itemnumbers ) { + for my $itemnumber ( @$itemnumbers) { + my $biblionumber = GetBiblionumberFromItemnumber $itemnumber; + $output .= marcrecord2csv( $biblionumber, $id, $firstpass, $csv, $fieldprocessing, [$itemnumber] ); + $firstpass = 0; + } + } else { + foreach my $biblio (@$biblios) { + $output .= marcrecord2csv( $biblio, $id, $firstpass, $csv, $fieldprocessing ); + $firstpass = 0; + } } # Postprocessing @@ -411,16 +421,21 @@ C<$header> - true if the headers are to be printed (typically at first pass) C<$csv> - an already initialised Text::CSV object +C<$fieldprocessing> + +C<$itemnumbers> a list of itemnumbers to export + =cut sub marcrecord2csv { - my ($biblio, $id, $header, $csv, $fieldprocessing) = @_; + my ($biblio, $id, $header, $csv, $fieldprocessing, $itemnumbers) = @_; my $output; # Getting the record - my $record = GetMarcBiblio($biblio, 1); + my $record = GetMarcBiblio($biblio); next unless $record; + C4::Biblio::EmbedItemsInMarcBiblio( $record, $biblio, $itemnumbers ); # Getting the framework my $frameworkcode = GetFrameworkCode($biblio); @@ -527,18 +542,28 @@ sub marcrecord2csv { my @fields = ($record->field($marcfield)); my $authvalues = GetKohaAuthorisedValuesFromField($marcfield, undef, $frameworkcode, undef); - my @valuesarray; - foreach (@fields) { - my $value; - - # Getting authorised value - $value = defined $authvalues->{$_->as_string} ? $authvalues->{$_->as_string} : $_->as_string; + my @valuesarray; + foreach (@fields) { + my $value; + + # If it is a control field + if ($_->is_control_field) { + $value = defined $authvalues->{$_->as_string} ? $authvalues->{$_->as_string} : $_->as_string; + } else { + # If it is a field, we gather all subfields, joined by the subfield separator + my @subvaluesarray; + my @subfields = $_->subfields; + foreach my $subfield (@subfields) { + push (@subvaluesarray, defined $authvalues->{$subfield->[1]} ? $authvalues->{$subfield->[1]} : $subfield->[1]); + } + $value = join ($subfieldseparator, @subvaluesarray); + } - # Field processing - eval $fieldprocessing if ($fieldprocessing); + # Field processing + eval $fieldprocessing if ($fieldprocessing); - push @valuesarray, $value; - } + push @valuesarray, $value; + } push (@fieldstab, join($fieldseparator, @valuesarray)); } }; diff --git a/circ/circulation.pl b/circ/circulation.pl index 551ceba..caea2f4 100755 --- a/circ/circulation.pl +++ b/circ/circulation.pl @@ -734,6 +734,8 @@ $template->param( AllowRenewalLimitOverride => C4::Context->preference("AllowRenewalLimitOverride"), dateformat => C4::Context->preference("dateformat"), DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), + dont_export_fields => C4::Context->preference("DontExportFields"), + csv_profile_for_export => C4::Context->preference("CsvProfileForExport"), canned_bor_notes_loop => $canned_notes, ); diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index ed0c674..06f4c4c 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5635,6 +5635,24 @@ if(C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.09.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do(qq{ + INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES('DontExportFields','','List of fields for non export in circulation.pl (separated by a space)','',''); + }); + print "Upgrade to $DBversion done (Add system preference DontExportFields)\n"; + SetVersion($DBversion); +} + +$DBversion = "3.09.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do(qq{ + INSERT IGNORE INTO systempreferences (variable,value,explanation,options,type) VALUES('CsvProfileForExport','','Set a profile name for CSV export','',''); + }); + print "Upgrade to $DBversion done (Adds New System preference CsvProfileForExport)\n"; + SetVersion($DBversion) +} + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/checkouts-table-footer.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/checkouts-table-footer.inc index 0d994c9..f2edfd6 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/checkouts-table-footer.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/checkouts-table-footer.inc @@ -3,7 +3,7 @@ Totals: [% totaldue %] [% totalprice %] - +

Renewal due date:

diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/prefs-menu.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/prefs-menu.inc index 3ecaefc..af23ab7 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/prefs-menu.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/prefs-menu.inc @@ -15,6 +15,7 @@ [% IF ( searching ) %]
  • [% ELSE %]
  • [% END %]Searching
  • [% IF ( serials ) %]
  • [% ELSE %]
  • [% END %]Serials
  • [% IF ( staff_client ) %]
  • [% ELSE %]
  • [% END %]Staff client
  • +[% IF ( tools ) %]
  • [% ELSE %]
  • [% END %]Tools
  • [% IF ( web_services ) %]
  • [% ELSE %]
  • [% END %]Web services
  • diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref index a758b19..eb71489 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/circulation.pref @@ -110,6 +110,12 @@ Circulation: yes: Do no: "Do not" - update a bibliographic record's total issues count whenever an item is issued (WARNING! This increases server load significantly; if performance is a concern, use the update_totalissues.pl cron job to update the total issues count). + - + - pref: DontExportFields + - choices: + yes: Export + no: "Don't export" + - fields for csv or iso2709 export Checkout Policy: - - pref: AllowNotForLoanOverride diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/tools.pref b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/tools.pref new file mode 100644 index 0000000..f5ea186 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/preferences/tools.pref @@ -0,0 +1,4 @@ +Tools: + - + - pref: CsvProfileForExport + - CSV profile for export 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 6f72392..d6ef006 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/circulation.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/circ/circulation.tt @@ -76,6 +76,16 @@ var allcheckboxes = $(".checkboxed"); $(allcheckboxes).unCheckCheckboxes(":input[name*=barcodes]"); return false; }); + $("#CheckAllexports").click(function(){ + $(".checkboxed").checkCheckboxes(":input[name*=biblionumbers]"); + $(".checkboxed").unCheckCheckboxes(":input[name*=items]"); + return false; + }); + $("#CheckNoexports").click(function(){ + $(".checkboxed").unCheckCheckboxes(":input[name*=biblionumbers]"); + return false; + }); + $("#relrenew_all").click(function(){ $(allcheckboxes).checkCheckboxes(":input[name*=items]"); $(allcheckboxes).unCheckCheckboxes(":input[name*=barcodes]"); @@ -138,9 +148,12 @@ var allcheckboxes = $(".checkboxed"); $("#add_message_form").show(); }); - $("input.radio").click(function(){ + $("input.radio").click(function(){ radioCheckBox($(this)); - }); + }); + $("#exportmenuc").empty(); + initExportButton(); + $("#newduedate").datepicker({ minDate: 1 }); // require that renewal date is after today $("#duedatespec").datepicker({ onSelect: function(dateText, inst) { $("#barcode").focus(); } @@ -148,6 +161,56 @@ var allcheckboxes = $(".checkboxed"); }); +function initExportButton() { + var exportmenu = [ + { text: _("ISO2709 with items"), onclick: {fn: function(){export_submit("iso2709_995")}} }, + { text: _("ISO2709 without items"), onclick: {fn: function(){export_submit("iso2709")}} }, + { text: _("CSV"), onclick: {fn: function(){export_submit("csv")}} }, + ]; + new YAHOO.widget.Button({ + type: "menu", + label: _("Export"), + name: "exportmenubutton", + menu: exportmenu, + container: "exportmenuc" + }); +} + +function export_submit(format) { + if ($("input:checkbox[name='biblionumbers'][checked]").length < 1){ + alert(_("You must select a checkout to export")); + return; + } + + $("input:checkbox[name='biblionumbers']").each( function(){ + var input_item = $(this).siblings("input:checkbox"); + if ( $(this).is(":checked") ) { + $(input_item).attr("checked", "checked"); + } else { + $(input_item).attr("checked", ""); + } + } ); + + if (format == 'iso2709_995') { + format = 'iso2709'; + $("#dont_export_item").val(0); + } else if (format == 'iso2709') { + $("#dont_export_item").val(1); + } else { + [% UNLESS ( csv_profile_for_export ) %] + alert(_("You must defined a csv profile for export (in tools>CSV export profiles) and filled the CsvProfileForExport system preference")); + return false; + [% END %] + } + document.issues.action="/cgi-bin/koha/tools/export.pl"; + document.getElementById("export_format").value = format; + document.issues.submit(); + + /* Reset form action to its initial value */ + document.issues.action="/cgi-bin/koha/reserve/renewscript.pl"; + +}; + function validate1(date) { var today = new Date(); if ( date < today ) { @@ -667,7 +730,7 @@ No patron matched [% message %]
    [% IF ( issuecount ) %] -
    + @@ -684,6 +747,7 @@ No patron matched [% message %] Price Renew

    select all | none

    Check in

    select all | none

    + Export

    select all | none

    [% IF ( todayissues ) %] [% INCLUDE 'checkouts-table-footer.inc' %] @@ -757,8 +821,8 @@ No patron matched [% message %] [% END %] [% END %] [% IF ( previssues ) %] -[% IF ( todayissues ) %]Previous checkouts[% ELSE %] -Previous checkouts +[% IF ( todayissues ) %]Previous checkouts[% ELSE %] +Previous checkouts [% INCLUDE 'checkouts-table-footer.inc' %] [% END %] @@ -827,6 +891,10 @@ No patron matched [% message %] [% END %] + + + + [% END %] [% END %] @@ -843,7 +911,14 @@ No patron matched [% message %] [% END %] - +

    + Don't export fields : + Export + + + + + [% END %]
    [% ELSE %] @@ -890,7 +965,7 @@ No patron matched [% message %] [% END %] [% END %] [% IF ( relprevissues ) %] -Previous checkouts +Previous checkouts [% FOREACH relprevissue IN relprevissues %] [% IF ( loop.odd ) %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/export.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/export.tt index 798ab83..0eb0f9f 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/export.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/export.tt @@ -108,7 +108,7 @@ $(document).ready(function() {
  • - + separate by a blank. (e.g., 100a 200 606)
  • diff --git a/tools/export.pl b/tools/export.pl index 95b8185..dbd9de4 100755 --- a/tools/export.pl +++ b/tools/export.pl @@ -16,25 +16,21 @@ # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, # Suite 330, Boston, MA 02111-1307 USA - -use strict; -use warnings; +use Modern::Perl; +use List::MoreUtils qw(uniq); +use Getopt::Long; +use CGI; use C4::Auth; +use C4::AuthoritiesMarc; # GetAuthority +use C4::Biblio; # GetMarcBiblio GetXmlBiblio +use C4::Branch; # GetBranches +use C4::Csv; +use C4::Koha; # GetItemTypes use C4::Output; -use C4::Biblio; # GetMarcBiblio GetXmlBiblio -use C4::AuthoritiesMarc; # GetAuthority -use CGI; -use C4::Koha; # GetItemTypes -use C4::Branch; # GetBranches use C4::Record; -use Getopt::Long; my $query = new CGI; -my $op; -my $filename; -my $dbh = C4::Context->dbh; -my $marcflavour = C4::Context->preference("marcflavour"); my $clean; my $output_format; my $dont_export_items; @@ -42,6 +38,11 @@ my $deleted_barcodes; my $timestamp; my $record_type; my $help; +my $op = $query->param("op") || ''; +my $filename = $query->param("filename") || 'koha.mrc'; +my $dbh = C4::Context->dbh; +my $marcflavour = C4::Context->preference("marcflavour"); +my $format = $query->param("format") || 'iso2709'; # Checks if the script is called from commandline my $commandline = not defined $ENV{GATEWAY_INTERFACE}; @@ -51,14 +52,14 @@ if ( $commandline ) { # Getting parameters $op = 'export'; GetOptions( - 'format=s' => \$output_format, - 'date=s' => \$timestamp, + 'format=s' => \$output_format, + 'date=s' => \$timestamp, 'dont_export_items' => \$dont_export_items, - 'deleted_barcodes' => \$deleted_barcodes, - 'clean' => \$clean, - 'filename=s' => \$filename, - 'record-type=s' => \$record_type, - 'help|?' => \$help + 'deleted_barcodes' => \$deleted_barcodes, + 'clean' => \$clean, + 'filename=s' => \$filename, + 'record-type=s' => \$record_type, + 'help|?' => \$help ); if ($help) { @@ -95,313 +96,337 @@ _USAGE_ # Redirect stdout open STDOUT, '>', $filename if $filename; -} else { +} +else { - $op = $query->param("op") || ''; - $filename = $query->param("filename") || 'koha.mrc'; + $op = $query->param("op") || ''; + $filename = $query->param("filename") || 'koha.mrc'; $filename =~ s/(\r|\n)//; } -my ($template, $loggedinuser, $cookie, $flags) = get_template_and_user( +my ( $template, $loggedinuser, $cookie, $flags ) = get_template_and_user( { - template_name => "tools/export.tmpl", - query => $query, - type => "intranet", + template_name => "tools/export.tmpl", + query => $query, + type => "intranet", authnotrequired => $commandline, - flagsrequired => {tools => 'export_catalog'}, - debug => 1, + flagsrequired => { tools => 'export_catalog' }, + debug => 1, } ); -my $limit_ind_branch = ( - C4::Context->preference('IndependantBranches') && - C4::Context->userenv && - !(C4::Context->userenv->{flags} & 1) && - C4::Context->userenv->{branch} -) ? 1 : 0; +my $limit_ind_branch = + ( C4::Context->preference('IndependantBranches') + && C4::Context->userenv + && !( C4::Context->userenv->{flags} & 1 ) + && C4::Context->userenv->{branch} ) ? 1 : 0; my $branch = $query->param("branch") || ''; -if ( C4::Context->preference("IndependantBranches") && - C4::Context->userenv && - !(C4::Context->userenv->{flags} & 1) ) { +if ( C4::Context->preference("IndependantBranches") + && C4::Context->userenv + && !( C4::Context->userenv->{flags} & 1 ) ) +{ $branch = C4::Context->userenv->{'branch'}; } my $backupdir = C4::Context->config('backupdir'); -if ($op eq "export") { - my $charset = 'utf-8'; - my $mimetype = 'application/octet-stream'; - binmode STDOUT, ':encoding(UTF-8)'; - if ( $filename =~ m/\.gz$/ ) { - $mimetype = 'application/x-gzip'; - $charset = ''; - binmode STDOUT; - } elsif ( $filename =~ m/\.bz2$/ ) { - $mimetype = 'application/x-bzip2'; - binmode STDOUT; - $charset = ''; - } - print $query->header( - -type => $mimetype, - -charset => $charset, - -attachment => $filename - ) unless ($commandline); - - $record_type = $query->param("record_type") unless ($commandline); - $output_format = $query->param("output_format") || 'marc' unless ($commandline); - my $dont_export_fields = $query->param("dont_export_fields"); - my @sql_params; - my $sql_query; - - my $StartingBiblionumber = $query->param("StartingBiblionumber"); - my $EndingBiblionumber = $query->param("EndingBiblionumber"); - my $itemtype = $query->param("itemtype"); - my $start_callnumber = $query->param("start_callnumber"); - my $end_callnumber = $query->param("end_callnumber"); - $timestamp = ($timestamp) ? C4::Dates->new($timestamp) : '' if ($commandline); - my $start_accession = - ( $query->param("start_accession") ) - ? C4::Dates->new( $query->param("start_accession") ) - : ''; - my $end_accession = - ( $query->param("end_accession") ) - ? C4::Dates->new( $query->param("end_accession") ) - : ''; - $dont_export_items = $query->param("dont_export_item") unless ($commandline); - my $strip_nonlocal_items = $query->param("strip_nonlocal_items"); - - my $biblioitemstable = ($commandline and $deleted_barcodes) - ? 'deletedbiblioitems' - : 'biblioitems'; - my $itemstable = ($commandline and $deleted_barcodes) - ? 'deleteditems' - : 'items'; - - my $starting_authid = $query->param('starting_authid'); - my $ending_authid = $query->param('ending_authid'); - my $authtype = $query->param('authtype'); - - if ( $record_type eq 'bibs' ) { - if ($timestamp) { +if ( $op eq "export" ) { + if ( $format eq "iso2709" or $format eq "xml" ) { + my $charset = 'utf-8'; + my $mimetype = 'application/octet-stream'; + binmode STDOUT, ':encoding(UTF-8)'; + if ( $filename =~ m/\.gz$/ ) { + $mimetype = 'application/x-gzip'; + $charset = ''; + binmode STDOUT; + } + elsif ( $filename =~ m/\.bz2$/ ) { + $mimetype = 'application/x-bzip2'; + binmode STDOUT; + $charset = ''; + } + print $query->header( + -type => $mimetype, + -charset => $charset, + -attachment => $filename + ) unless ($commandline); + + $record_type = $query->param("record_type") unless ($commandline); + $output_format = $query->param("output_format") || 'marc' + unless ($commandline); + my $dont_export_fields = $query->param("dont_export_fields"); + my @biblionumbers = $query->param("biblionumbers"); + my @itemnumbers = $query->param("itemnumbers"); + my @sql_params; + my $sql_query; + my @recordids; + + my $StartingBiblionumber = $query->param("StartingBiblionumber"); + my $EndingBiblionumber = $query->param("EndingBiblionumber"); + my $itemtype = $query->param("itemtype"); + my $start_callnumber = $query->param("start_callnumber"); + my $end_callnumber = $query->param("end_callnumber"); + $timestamp = ($timestamp) ? C4::Dates->new($timestamp) : '' + if ($commandline); + my $start_accession = + ( $query->param("start_accession") ) + ? C4::Dates->new( $query->param("start_accession") ) + : ''; + my $end_accession = + ( $query->param("end_accession") ) + ? C4::Dates->new( $query->param("end_accession") ) + : ''; + $dont_export_items = $query->param("dont_export_item") + unless ($commandline); + + my $strip_nonlocal_items = $query->param("strip_nonlocal_items"); + + my $biblioitemstable = + ( $commandline and $deleted_barcodes ) + ? 'deletedbiblioitems' + : 'biblioitems'; + my $itemstable = + ( $commandline and $deleted_barcodes ) + ? 'deleteditems' + : 'items'; + + my $starting_authid = $query->param('starting_authid'); + my $ending_authid = $query->param('ending_authid'); + my $authtype = $query->param('authtype'); + + if ( $record_type eq 'bibs' and not @biblionumbers ) { + if ($timestamp) { + # Specific query when timestamp is used # Actually it's used only with CLI and so all previous filters # are not used. # If one day timestamp is used via the web interface, this part will # certainly have to be rewrited - $sql_query = " ( - SELECT biblionumber - FROM $biblioitemstable - LEFT JOIN items USING(biblionumber) - WHERE $biblioitemstable.timestamp >= ? - OR items.timestamp >= ? - ) UNION ( - SELECT biblionumber - FROM $biblioitemstable - LEFT JOIN deleteditems USING(biblionumber) - WHERE $biblioitemstable.timestamp >= ? - OR deleteditems.timestamp >= ? - ) "; - my $ts = $timestamp->output('iso'); - @sql_params = ($ts, $ts, $ts, $ts); - } else { - my $items_filter = - $branch || $start_callnumber || $end_callnumber || - $start_accession || $timestamp || $end_accession || - ($itemtype && C4::Context->preference('item-level_itypes')); - $sql_query = $items_filter ? - "SELECT DISTINCT $biblioitemstable.biblionumber - FROM $biblioitemstable JOIN $itemstable - USING (biblionumber) WHERE 1" - : - "SELECT $biblioitemstable.biblionumber FROM $biblioitemstable WHERE biblionumber >0 "; - - if ( $StartingBiblionumber ) { - $sql_query .= " AND $biblioitemstable.biblionumber >= ? "; - push @sql_params, $StartingBiblionumber; - } - - if ( $EndingBiblionumber ) { - $sql_query .= " AND $biblioitemstable.biblionumber <= ? "; - push @sql_params, $EndingBiblionumber; - } + my ( $query, $params ) = construct_query( + { + recordtype => $record_type, + timestamp => $timestamp, + biblioitemstable => $biblioitemstable, + } + ); + $sql_query = $query; + @sql_params = @$params; - if ($branch) { - $sql_query .= " AND homebranch = ? "; - push @sql_params, $branch; } - - if ($start_callnumber) { - $sql_query .= " AND itemcallnumber <= ? "; - push @sql_params, $start_callnumber; + else { + my ( $query, $params ) = construct_query( + { + recordtype => $record_type, + biblioitemstable => $biblioitemstable, + itemstable => $itemstable, + StartingBiblionumber => $StartingBiblionumber, + EndingBiblionumber => $EndingBiblionumber, + branch => $branch, + start_callnumber => $start_callnumber, + end_callnumber => $end_callnumber, + start_accession => $start_accession, + end_accession => $end_accession, + itemtype => $itemtype, + } + ); + $sql_query = $query; + @sql_params = @$params; } + } + elsif ( $record_type eq 'auths' ) { + my ( $query, $params ) = construct_query( + { + recordtype => $record_type, + starting_authid => $starting_authid, + ending_authid => $ending_authid, + authtype => $authtype, + } + ); + $sql_query = $query; + @sql_params = @$params; - if ($end_callnumber) { - $sql_query .= " AND itemcallnumber >= ? "; - push @sql_params, $end_callnumber; + } + elsif ( $record_type eq 'db' ) { + my $successful_export; + if ( $flags->{superlibrarian} + && C4::Context->config('backup_db_via_tools') ) + { + $successful_export = download_backup( + { + directory => "$backupdir", + extension => 'sql', + filename => "$filename" + } + ); } - if ($start_accession) { - $sql_query .= " AND dateaccessioned >= ? "; - push @sql_params, $start_accession->output('iso'); + unless ($successful_export) { + my $remotehost = $query->remote_host(); + $remotehost =~ s/(\n|\r)//; + warn +"A suspicious attempt was made to download the db at '$filename' by someone at " + . $remotehost . "\n"; } - - if ($end_accession) { - $sql_query .= " AND dateaccessioned <= ? "; - push @sql_params, $end_accession->output('iso'); + exit; + } + elsif ( $record_type eq 'conf' ) { + my $successful_export; + if ( $flags->{superlibrarian} + && C4::Context->config('backup_conf_via_tools') ) + { + $successful_export = download_backup( + { + directory => "$backupdir", + extension => 'tar', + filename => "$filename" + } + ); } - - if ( $itemtype ) { - $sql_query .= (C4::Context->preference('item-level_itypes')) ? " AND items.itype = ? " : " AND biblioitems.itemtype = ?"; - push @sql_params, $itemtype; + unless ($successful_export) { + my $remotehost = $query->remote_host(); + $remotehost =~ s/(\n|\r)//; + warn +"A suspicious attempt was made to download the configuration at '$filename' by someone at " + . $remotehost . "\n"; } + exit; } - } - elsif ( $record_type eq 'auths' ) { - $sql_query = - "SELECT DISTINCT auth_header.authid FROM auth_header WHERE 1"; - - if ($starting_authid) { - $sql_query .= " AND auth_header.authid >= ? "; - push @sql_params, $starting_authid; + elsif (@biblionumbers) { + push @recordids, (@biblionumbers); } + else { - if ($ending_authid) { - $sql_query .= " AND auth_header.authid <= ? "; - push @sql_params, $ending_authid; + # Someone is trying to mess us up + exit; } - if ($authtype) { - $sql_query .= " AND auth_header.authtypecode = ? "; - push @sql_params, $authtype; - } - } - elsif ( $record_type eq 'db' ) { - my $successful_export; - if ( $flags->{superlibrarian} && C4::Context->config('backup_db_via_tools') ) { - $successful_export = download_backup( { directory => "$backupdir", extension => 'sql', filename => "$filename" } ) - } - unless ( $successful_export ) { - my $remotehost = $query->remote_host(); - $remotehost =~ s/(\n|\r)//; - warn "A suspicious attempt was made to download the db at '$filename' by someone at " . $remotehost . "\n"; - } - exit; - } - elsif ( $record_type eq 'conf' ) { - my $successful_export; - if ( $flags->{superlibrarian} && C4::Context->config('backup_conf_via_tools') ) { - $successful_export = download_backup( { directory => "$backupdir", extension => 'tar', filename => "$filename" } ) - } - unless ( $successful_export ) { - my $remotehost = $query->remote_host(); - $remotehost =~ s/(\n|\r)//; - warn "A suspicious attempt was made to download the configuration at '$filename' by someone at " . $remotehost . "\n"; + unless (@biblionumbers) { + my $sth = $dbh->prepare($sql_query); + $sth->execute(@sql_params); + push @recordids, map { + map { $$_[0] } $_ + } @{ $sth->fetchall_arrayref }; } - exit; - } - else { - # Someone is trying to mess us up - exit; - } - - my $sth = $dbh->prepare($sql_query); - $sth->execute(@sql_params); - - while ( my ($recordid) = $sth->fetchrow ) { - if ( $deleted_barcodes ) { - my $q = " - SELECT DISTINCT barcode - FROM deleteditems - WHERE deleteditems.biblionumber = ? - "; - my $sth = $dbh->prepare($q); - $sth->execute($recordid); - while (my $row = $sth->fetchrow_array) { - print "$row\n"; - } - } else { - my $record; - if ( $record_type eq 'bibs' ) { - $record = eval { GetMarcBiblio($recordid); }; - if ($@) { - next; + for my $recordid ( uniq @recordids ) { + if ($deleted_barcodes) { + my $q = " + SELECT DISTINCT barcode + FROM deleteditems + WHERE deleteditems.biblionumber = ? + "; + my $sth = $dbh->prepare($q); + $sth->execute($recordid); + while ( my $row = $sth->fetchrow_array ) { + print "$row\n"; } - next if not defined $record; - C4::Biblio::EmbedItemsInMarcBiblio( $record, $recordid ) - unless $dont_export_items; - if ( $strip_nonlocal_items || $limit_ind_branch ) { - my ( $homebranchfield, $homebranchsubfield ) = - GetMarcFromKohaField( 'items.homebranch', '' ); - for my $itemfield ( $record->field($homebranchfield) ) { - - # if stripping nonlocal items, use loggedinuser's branch if they didn't select one - $branch = C4::Context->userenv->{'branch'} unless $branch; - $record->delete_field($itemfield) - if ( - $itemfield->subfield($homebranchsubfield) ne $branch ); + } + else { + my $record; + if ( $record_type eq 'bibs' ) { + $record = eval { GetMarcBiblio($recordid); }; + + next if $@; + next if not defined $record; + C4::Biblio::EmbedItemsInMarcBiblio( $record, $recordid, + \@itemnumbers ) + unless $dont_export_items; + if ( $strip_nonlocal_items + || $limit_ind_branch + || $dont_export_items ) + { + my ( $homebranchfield, $homebranchsubfield ) = + GetMarcFromKohaField( 'items.homebranch', '' ); + for my $itemfield ( $record->field($homebranchfield) ) { + +# if stripping nonlocal items, use loggedinuser's branch if they didn't select one + $branch = C4::Context->userenv->{'branch'} + unless $branch; + $record->delete_field($itemfield) + if ( $dont_export_items + || $itemfield->subfield($homebranchsubfield) ne + $branch ); + } } } - } - elsif ( $record_type eq 'auths' ) { - $record = C4::AuthoritiesMarc::GetAuthority($recordid); - next if not defined $record; - } + elsif ( $record_type eq 'auths' ) { + $record = C4::AuthoritiesMarc::GetAuthority($recordid); + next if not defined $record; + } - if ( $dont_export_fields ) { - my @fields = split " ", $dont_export_fields; - foreach ( @fields ) { - /^(\d*)(\w)?$/; - my $field = $1; - my $subfield = $2; - # skip if this record doesn't have this field - next if not defined $record->field($field); - if( $subfield ) { - $record->field($field)->delete_subfields($subfield); + if ($dont_export_fields) { + my @fields = split " ", $dont_export_fields; + foreach (@fields) { + /^(\d*)(\w)?$/; + my $field = $1; + my $subfield = $2; + + # skip if this record doesn't have this field + next if not defined $record->field($field); + if ($subfield) { + $record->field($field)->delete_subfields($subfield); + } + else { + $record->delete_field( $record->field($field) ); + } + } + } + RemoveAllNsb($record) if ($clean); + if ( $output_format eq "xml" ) { + if ( $marcflavour eq 'UNIMARC' && $record_type eq 'auths' ) + { + print $record->as_xml_record('UNIMARCAUTH'); } else { - $record->delete_field($record->field($field)); + print $record->as_xml_record($marcflavour); } } - } - RemoveAllNsb($record) if ($clean); - if ( $output_format eq "xml" ) { - if ($marcflavour eq 'UNIMARC' && $record_type eq 'auths') { - print $record->as_xml_record('UNIMARCAUTH'); - } else { - print $record->as_xml_record($marcflavour); + else { + print $record->as_usmarc(); } } - else { - print $record->as_usmarc(); - } } + exit; + } + elsif ( $format eq "csv" ) { + my @biblionumbers = uniq $query->param("biblionumbers"); + my @itemnumbers = $query->param("itemnumbers"); + my $output = + marc2csv( \@biblionumbers, + GetCsvProfileId( C4::Context->preference('CsvProfileForExport') ), + \@itemnumbers, ); + print $query->header( + -type => 'application/octet-stream', + -'Content-Transfer-Encoding' => 'binary', + -attachment => "export.csv" + ); + print $output; + exit; } - exit; - } # if export else { my $itemtypes = GetItemTypes; my @itemtypesloop; - foreach my $thisitemtype (sort keys %$itemtypes) { - my %row = - ( - value => $thisitemtype, - description => $itemtypes->{$thisitemtype}->{'description'}, - ); - push @itemtypesloop, \%row; + foreach my $thisitemtype ( sort keys %$itemtypes ) { + my %row = ( + value => $thisitemtype, + description => $itemtypes->{$thisitemtype}->{'description'}, + ); + push @itemtypesloop, \%row; } my $branches = GetBranches($limit_ind_branch); my @branchloop; for my $thisbranch ( sort { $branches->{$a}->{branchname} cmp $branches->{$b}->{branchname} } keys %{$branches} - ) { + ) + { push @branchloop, - { value => $thisbranch, + { + value => $thisbranch, selected => $thisbranch eq $branch, branchname => $branches->{$thisbranch}->{'branchname'}, }; @@ -418,14 +443,24 @@ else { push @authtypesloop, \%row; } - if ( $flags->{superlibrarian} && C4::Context->config('backup_db_via_tools') && $backupdir && -d $backupdir ) { + if ( $flags->{superlibrarian} + && C4::Context->config('backup_db_via_tools') + && $backupdir + && -d $backupdir ) + { $template->{VARS}->{'allow_db_export'} = 1; - $template->{VARS}->{'dbfiles'} = getbackupfilelist( { directory => "$backupdir", extension => 'sql' } ); + $template->{VARS}->{'dbfiles'} = getbackupfilelist( + { directory => "$backupdir", extension => 'sql' } ); } - if ( $flags->{superlibrarian} && C4::Context->config('backup_conf_via_tools') && $backupdir && -d $backupdir ) { + if ( $flags->{superlibrarian} + && C4::Context->config('backup_conf_via_tools') + && $backupdir + && -d $backupdir ) + { $template->{VARS}->{'allow_conf_export'} = 1; - $template->{VARS}->{'conffiles'} = getbackupfilelist( { directory => "$backupdir", extension => 'tar' } ); + $template->{VARS}->{'conffiles'} = getbackupfilelist( + { directory => "$backupdir", extension => 'tar' } ); } $template->param( @@ -439,16 +474,139 @@ else { output_html_with_http_headers $query, $cookie, $template->output; } +sub construct_query { + my ($params) = @_; + + my ( $sql_query, @sql_params ); + + if ( $params->{recordtype} eq "bibs" ) { + if ( $params->{timestamp} ) { + my $biblioitemstable = $params->{biblioitemstable}; + $sql_query = " ( + SELECT biblionumber + FROM $biblioitemstable + LEFT JOIN items USING(biblionumber) + WHERE $biblioitemstable.timestamp >= ? + OR items.timestamp >= ? + ) UNION ( + SELECT biblionumber + FROM $biblioitemstable + LEFT JOIN deleteditems USING(biblionumber) + WHERE $biblioitemstable.timestamp >= ? + OR deleteditems.timestamp >= ? + ) "; + my $ts = $timestamp->output('iso'); + @sql_params = ( $ts, $ts, $ts, $ts ); + } + else { + my $biblioitemstable = $params->{biblioitemstable}; + my $itemstable = $params->{itemstable}; + my $StartingBiblionumber = $params->{StartingBiblionumber}; + my $EndingBiblionumber = $params->{EndingBiblionumber}; + my $branch = $params->{branch}; + my $start_callnumber = $params->{start_callnumber}; + my $end_callnumber = $params->{end_callnumber}; + my $start_accession = $params->{star_accession}; + my $end_accession = $params->{end_accession}; + my $itemtype = $params->{itemtype}; + my $items_filter = + $branch + || $start_callnumber + || $end_callnumber + || $start_accession + || $end_accession + || ( $itemtype && C4::Context->preference('item-level_itypes') ); + $sql_query = $items_filter + ? "SELECT DISTINCT $biblioitemstable.biblionumber + FROM $biblioitemstable JOIN $itemstable + USING (biblionumber) WHERE 1" + : "SELECT $biblioitemstable.biblionumber FROM $biblioitemstable WHERE biblionumber >0 "; + + if ($StartingBiblionumber) { + $sql_query .= " AND $biblioitemstable.biblionumber >= ? "; + push @sql_params, $StartingBiblionumber; + } + + if ($EndingBiblionumber) { + $sql_query .= " AND $biblioitemstable.biblionumber <= ? "; + push @sql_params, $EndingBiblionumber; + } + + if ($branch) { + $sql_query .= " AND homebranch = ? "; + push @sql_params, $branch; + } + + if ($start_callnumber) { + $sql_query .= " AND itemcallnumber <= ? "; + push @sql_params, $start_callnumber; + } + + if ($end_callnumber) { + $sql_query .= " AND itemcallnumber >= ? "; + push @sql_params, $end_callnumber; + } + if ($start_accession) { + $sql_query .= " AND dateaccessioned >= ? "; + push @sql_params, $start_accession->output('iso'); + } + + if ($end_accession) { + $sql_query .= " AND dateaccessioned <= ? "; + push @sql_params, $end_accession->output('iso'); + } + + if ($itemtype) { + $sql_query .= + ( C4::Context->preference('item-level_itypes') ) + ? " AND items.itype = ? " + : " AND biblioitems.itemtype = ?"; + push @sql_params, $itemtype; + } + } + } + elsif ( $params->{recordtype} eq "auths" ) { + if ( $params->{timestamp} ) { + + #TODO + } + else { + my $starting_authid = $params->{starting_authid}; + my $ending_authid = $params->{ending_authid}; + my $authtype = $params->{authtype}; + $sql_query = + "SELECT DISTINCT auth_header.authid FROM auth_header WHERE 1"; + + if ($starting_authid) { + $sql_query .= " AND auth_header.authid >= ? "; + push @sql_params, $starting_authid; + } + + if ($ending_authid) { + $sql_query .= " AND auth_header.authid <= ? "; + push @sql_params, $ending_authid; + } + + if ($authtype) { + $sql_query .= " AND auth_header.authtypecode = ? "; + push @sql_params, $authtype; + } + } + } + return ( $sql_query, \@sql_params ); +} + sub getbackupfilelist { - my $args = shift; + my $args = shift; my $directory = $args->{directory}; my $extension = $args->{extension}; my @files; - if ( opendir(my $dir, $directory) ) { - while (my $file = readdir($dir)) { + if ( opendir( my $dir, $directory ) ) { + while ( my $file = readdir($dir) ) { next unless ( $file =~ m/\.$extension(\.(gz|bz2|xz))?/ ); - push @files, $file if ( -f "$directory/$file" && -r "$directory/$file" ); + push @files, $file + if ( -f "$directory/$file" && -r "$directory/$file" ); } closedir($dir); } @@ -456,20 +614,23 @@ sub getbackupfilelist { } sub download_backup { - my $args = shift; + my $args = shift; my $directory = $args->{directory}; my $extension = $args->{extension}; my $filename = $args->{filename}; return unless ( $directory && -d $directory ); - return unless ( $filename =~ m/$extension(\.(gz|bz2|xz))?$/ && not $filename =~ m#|# ); + return + unless ( $filename =~ m/$extension(\.(gz|bz2|xz))?$/ + && not $filename =~ m#|# ); $filename = "$directory/$filename"; return unless ( -f $filename && -r $filename ); - return unless ( open(my $dump, '<', $filename) ); + return unless ( open( my $dump, '<', $filename ) ); binmode $dump; - while (read($dump, my $data, 64 * 1024)) { + + while ( read( $dump, my $data, 64 * 1024 ) ) { print $data; } - close ($dump); + close($dump); return 1; } -- 1.7.7.3