This patch allows the user to use a CSV export profile to create the fields to export the basket as CSV in a basket page.
Test plan:
1) Apply the patch
2) Go to Tools › CSV export profiles and create a profile of type "SQL for basket export in acquisition"
example:
biblionumber=biblio.biblionumber|auteur=biblio.author|titre=biblio.title|date=biblioitems.copyrightdate|editeur=biblioitems.publishercode|isbn=biblioitems.isbn|quantite=aqorders.quantity|prix=aqorders.rrp|panier=aqorders.basketno
3) In acquisition module, create a new basket and add an order to the basket
4) On basket detail page, there should be the split button labelled "Export to CSV"
5) Try to use the button and export CSV with your CSV profile you defined in step 2
6) Validate the CSV file.
7) Repeat 4-6 with a closed basket.
a) close the basket
b) View the basket
c) validate that there is an export button
d) test it with an export
8) prove t/db_dependent/Acquisition/GetBasketAsCSV.t t/db_dependent/Koha/CsvProfiles.t
Initial work:
Sponsored by: CCSR
Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: mehdi <mehdi.hamidi@inlibro.com>
Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
use Koha::Biblios;
use Koha::Number::Price;
use Koha::Libraries;
+use Koha::CsvProfiles;
use C4::Koha;
=cut
sub GetBasketAsCSV {
- my ($basketno, $cgi) = @_;
+ my ($basketno, $cgi, $csv_profile_id) = @_;
my $basket = GetBasket($basketno);
my @orders = GetOrders($basketno);
my $contract = GetContract({
});
my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
-
my @rows;
- foreach my $order (@orders) {
- my $bd = GetBiblioData( $order->{'biblionumber'} );
- my $row = {
- contractname => $contract->{'contractname'},
- ordernumber => $order->{'ordernumber'},
- entrydate => $order->{'entrydate'},
- isbn => $order->{'isbn'},
- author => $bd->{'author'},
- title => $bd->{'title'},
- publicationyear => $bd->{'publicationyear'},
- publishercode => $bd->{'publishercode'},
- collectiontitle => $bd->{'collectiontitle'},
- notes => $order->{'order_vendornote'},
- quantity => $order->{'quantity'},
- rrp => $order->{'rrp'},
- };
- for my $place ( qw( deliveryplace billingplace ) ) {
- if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
- $row->{$place} = $library->branchname
+ if ($csv_profile_id) {
+ my $csv_profile = Koha::CsvProfiles->find( $csv_profile_id );
+ die "There is no valid csv profile given" unless $csv_profile;
+
+ my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$csv_profile->csv_separator,'binary'=>1});
+ my $csv_profile_content = $csv_profile->content;
+ my ( @headers, @fields );
+ while ( $csv_profile_content =~ /
+ ([^=]+) # header
+ =
+ ([^\|]+) # fieldname (table.row or row)
+ \|? /gxms
+ ) {
+ push @headers, $1;
+ my $field = $2;
+ $field =~ s/[^\.]*\.?//; # Remove the table name if exists.
+ push @fields, $field;
+ }
+ for my $order (@orders) {
+ my @row;
+ my $bd = GetBiblioData( $order->{'biblionumber'} );
+ my @biblioitems = GetBiblioItemByBiblioNumber( $order->{'biblionumber'});
+ for my $biblioitem (@biblioitems) {
+ if ($biblioitem->{isbn} eq $order->{isbn}) {
+ $order = {%$order, %$biblioitem};
+ }
+ }
+ if ($contract) {
+ $order = {%$order, %$contract};
+ }
+ $order = {%$order, %$basket, %$bd};
+ for my $field (@fields) {
+ push @row, $order->{$field};
}
+ push @rows, \@row;
}
- foreach(qw(
- contractname author title publishercode collectiontitle notes
- deliveryplace billingplace
- ) ) {
- # Double the quotes to not be interpreted as a field end
- $row->{$_} =~ s/"/""/g if $row->{$_};
+ my $content = join( $csv_profile->csv_separator, @headers ) . "\n";
+ for my $row ( @rows ) {
+ $csv->combine(@$row);
+ my $string = $csv->string;
+ $content .= $string . "\n";
}
- push @rows, $row;
+ return $content;
}
+ else {
+ foreach my $order (@orders) {
+ my $bd = GetBiblioData( $order->{'biblionumber'} );
+ my $row = {
+ contractname => $contract->{'contractname'},
+ ordernumber => $order->{'ordernumber'},
+ entrydate => $order->{'entrydate'},
+ isbn => $order->{'isbn'},
+ author => $bd->{'author'},
+ title => $bd->{'title'},
+ publicationyear => $bd->{'publicationyear'},
+ publishercode => $bd->{'publishercode'},
+ collectiontitle => $bd->{'collectiontitle'},
+ notes => $order->{'order_vendornote'},
+ quantity => $order->{'quantity'},
+ rrp => $order->{'rrp'},
+ };
+ for my $place ( qw( deliveryplace billingplace ) ) {
+ if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
+ $row->{$place} = $library->branchname
+ }
+ }
+ foreach(qw(
+ contractname author title publishercode collectiontitle notes
+ deliveryplace billingplace
+ ) ) {
+ # Double the quotes to not be interpreted as a field end
+ $row->{$_} =~ s/"/""/g if $row->{$_};
+ }
+ push @rows, $row;
+ }
- @rows = sort {
- if(defined $a->{publishercode} and defined $b->{publishercode}) {
- $a->{publishercode} cmp $b->{publishercode};
- }
- } @rows;
+ @rows = sort {
+ if(defined $a->{publishercode} and defined $b->{publishercode}) {
+ $a->{publishercode} cmp $b->{publishercode};
+ }
+ } @rows;
- $template->param(rows => \@rows);
+ $template->param(rows => \@rows);
- return $template->output;
+ return $template->output;
+ }
}
use Date::Calc qw/Add_Delta_Days/;
use Koha::Database;
use Koha::EDI qw( create_edi_order get_edifact_ean );
+use Koha::CsvProfiles;
=head1 NAME
-type => 'text/csv',
-attachment => 'basket' . $basket->{'basketno'} . '.csv',
);
- print GetBasketAsCSV($query->param('basketno'), $query);
+ if ( $query->param('csv_profile') eq 'default'){
+ print GetBasketAsCSV($query->param('basketno'), $query);
+ } else {
+ my $csv_profile_id = $query->param('csv_profile');
+ print GetBasketAsCSV($query->param('basketno'), $query, $csv_profile_id);
+ }
exit;
} elsif ($op eq 'email') {
my $err = eval {
unclosable => @orders ? $basket->{is_standing} : 1,
has_budgets => $has_budgets,
duplinbatch => $duplinbatch,
+ csv_profiles => [ Koha::CsvProfiles->search({ type => 'export_basket' }) ],
);
}
--- /dev/null
+UPDATE export_format SET type = 'late_issues' WHERE type ='sql';
+[% BLOCK csv_export %]
+ <div class="btn-group">
+ <a id="exportbutton" class="btn btn-default btn-sm" href="[% script_name %]?op=export&basketno=[% basketno %]&booksellerid=[% booksellerid %]"><i class="fa fa-download"></i> Export as CSV</a>
+ <a class="btn btn-default btn-sm dropdown-toggle" data-toggle="dropdown"><span class="caret"></span></a>
+ <ul class="dropdown-menu" id="export-csv-menu">
+ <li><a href="#">Default</a></li>
+ [% IF csv_profiles %]
+ [% FOR csv IN csv_profiles %]
+ <li><a href="#" data-value="[% csv.export_format_id %]">[% csv.profile %]</a></li>
+ [% END %]
+ [% END %]
+ </ul>
+ </div>
+[% END %]
[% USE KohaDates %]
[% USE Branches %]
[% USE Price %]
e.preventDefault();
confirm_reopen();
});
+ // Generates a dynamic link for exporting the selections data as CSV
+ $("#exportbutton, #export-csv-menu a").click(function() {
+ // Building the url from currently checked boxes
+ var url = '/cgi-bin/koha/acqui/basket.pl';
+ url += $('#exportbutton').attr('href');
+ if($(this).attr("data-value")) {
+ url += '&csv_profile=' + $(this).attr("data-value");
+ }
+ // And redirecting to the CSV page
+ location.href = url;
+ return false;
+ });
});
function UserSearchPopup(f) {
<a href="/cgi-bin/koha/acqui/basket.pl?op=close&basketno=[% basketno %]&booksellerid=[% booksellerid %]" class="btn btn-default btn-sm" id="closebutton"><i class="fa fa-times-circle"></i> Close this basket</a>
</div>
[% END %]
- <div class="btn-group"><a href="/cgi-bin/koha/acqui/basket.pl?op=export&basketno=[% basketno %]&booksellerid=[% booksellerid %]" class="btn btn-default btn-sm" id="exportbutton"><i class="fa fa-download"></i> Export this basket as CSV</a></div>
+
+ [% PROCESS csv_export %]
+
[% IF ediaccount %]
<div class="btn-group"><a href="/cgi-bin/koha/acqui/edi_ean.pl?op=ediorder&basketno=[% basketno %]&booksellerid=[% booksellerid %]" class="btn btn-default btn-sm" id="ediorderbutton"><i class="fa fa-download"></i> Create EDIFACT order</a></div>
[% END %]
+
[% IF ( active && books_loop ) %]
<div class="btn-group">
<form action="/cgi-bin/koha/acqui/basket.pl" method="post">
[% END %]
</div>
[% END %]
-<!-- Modal for confirm deletion box-->
+
+ <!-- Modal for confirm deletion box-->
<div class="modal" id="deleteBasketModal" tabindex="-1" role="dialog" aria-labelledby="delbasketModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
[% ELSE %]
[% UNLESS ( grouped ) %]
<div id="toolbar" class="btn-toolbar">
+
<div class="btn-group"><a href="#" class="btn btn-default btn-sm" id="reopenbutton"><i class="fa fa-refresh"></i> Reopen this basket</a></div>
- <div class="btn-group"><a href="/cgi-bin/koha/acqui/basket.pl?op=export&basketno=[% basketno %]&booksellerid=[% booksellerid %]" class="btn btn-default btn-sm" id="exportbutton"><i class="fa fa-download"></i> Export this basket as CSV</a></div>
+
+ [% PROCESS csv_export %]
+
</div>
[% END %]
[% END %]
[% END %]
[% END %]
+[% BLOCK type_description %]
+ [% IF type_code == 'marc' %] MARC for export records
+ [% ELSIF type_code == 'late_issues' %] SQL for late serial issues claims
+ [% ELSIF type_code == 'export_basket' %] SQL for basket export in acquisition
+ [% ELSE %] Uknown type
+ [% END %]
+[% END %]
+
[% IF op == 'add_form' %]
[% IF csv_profile %]
<h1>Modify a CSV profile</h1>
<li>
<label for="type" class="required">Profile type: </label>
<select id="type" name="type">
- [% IF csv_profile.type == 'sql' %]
- <option value="marc">MARC</option>
- <option value="sql" selected="selected">SQL</option>
+ [% FOREACH type IN [ 'marc' 'late_issues' 'export_basket'] %]
+ [% IF csv_profile.type == type %]
+ <option value="[% type %]" selected="selected">[% PROCESS type_description type_code = type %]</option>
[% ELSE %]
- <option value="marc" selected="selected">MARC</option>
- <option value="sql">SQL</option>
+ <option value="[% type %]">[% PROCESS type_description type_code = type %]</option>
+ [% END %]
[% END %]
</select>
<span class="required">Required</span>
</li>
<li class="sql_specific">
- <label for="sql_content" class="required">Profile SQL fields: </label>
- <textarea cols="50" rows="2" name="sql_content" id="sql_content">[% csv_profile.content %]</textarea>
- <p>You have to define which fields you want to export, separated by pipes.</p>
- <p>You can also use your own headers (instead of the ones from Koha) by prefixing the field name with an header, followed by the equal sign.</p>
- <p>Example: Name=subscription.name|Title=subscription.title|Issue number=serial.serialseq</p>
+ <label for="late_issues_content" class="required">Profile SQL fields: </label>
+ <textarea cols="50" rows="2" name="sql_content" id="sql_content">[% csv_profile.content %]</textarea>
+ <p>You have to define which fields you want to export, separated by pipes.</p>
+ <p>You can also use your own headers (instead of the ones from Koha) by prefixing the field name with an header, followed by the equal sign.</p>
+ <p>Example: Name=subscription.name|Title=subscription.title|Issue number=serial.serialseq</p>
+ <p>For late issues claims you can use data from following tables: serial, subscription, biblio, biblioitems and aqbookseller.</p>
+ <p>For basket exports you can use data from following tables: biblio, biblioitems, aqorders, aqbudgets and aqbasket.</p>
</li>
</ol>
</fieldset>
<td>[% csv_profile.description %]</td>
<td>[% csv_profile.content %]</td>
<td>[% csv_profile.csv_separator %]</td>
- [% IF csv_profile.type == 'sql' %]
- <td>SQL</td>
- [% ELSE %]
- <td>MARC</td>
- [% END %]
+ <td>[% PROCESS type_description type_code = csv_profile.type %]</td>
<td><a href="/cgi-bin/koha/tools/csv-profiles.pl?op=add_form&export_format_id=[% csv_profile.export_format_id %]">Edit</a></td>
<td><a href="/cgi-bin/koha/tools/csv-profiles.pl?op=delete_confirm&export_format_id=[% csv_profile.export_format_id %]">Delete</a></td>
</tr>
supplierid => $supplierid,
claimletter => $claimletter,
additional_fields_for_subscription => $additional_fields,
- csv_profiles => [ Koha::CsvProfiles->search({ type => 'sql' }) ],
+ csv_profiles => [ Koha::CsvProfiles->search({ type => 'late_issues' }) ],
letters => $letters,
(uc(C4::Context->preference("marcflavour"))) => 1
);
--- /dev/null
+#!/usr/bin/perl
+
+use Modern::Perl;
+
+use CGI;
+
+use Test::More tests => 2;
+
+use C4::Acquisition;
+use C4::Biblio;
+use Koha::Database;
+use Koha::CsvProfile;
+
+use Koha::Acquisition::Order;
+
+my $schema = Koha::Database->new()->schema();
+$schema->storage->txn_begin();
+
+my $dbh = C4::Context->dbh;
+$dbh->{RaiseError} = 1;
+
+my $query = CGI->new();
+
+my $vendor = Koha::Acquisition::Bookseller->new({
+ name => 'my vendor',
+ address1 => 'vendor address',
+ active => 1,
+ deliverytime => 5,
+})->store;
+
+my $budget_id = C4::Budgets::AddBudget({
+ budget_code => 'my_budget_code',
+ budget_name => 'My budget name',
+});
+my $budget = C4::Budgets::GetBudget( $budget_id );
+
+my $csv_profile = Koha::CsvProfile->new({
+ profile => 'my user profile',
+ type => 'export_basket',
+ csv_separator => ',',
+ content => 'autor=biblio.author|title=biblio.title|quantity=aqorders.quantity',
+})->store;
+
+my $basketno;
+$basketno = NewBasket($vendor->id, 1);
+
+my $biblio = MARC::Record->new();
+$biblio->append_fields(
+ MARC::Field->new( '100', ' ', ' ', a => 'King, Stephen' ),
+ MARC::Field->new( '245', ' ', ' ', a => 'Test Record' ),
+);
+my ($biblionumber, $biblioitemnumber) = AddBiblio($biblio, '');
+
+my $order = Koha::Acquisition::Order->new({
+ basketno => $basketno,
+ quantity => 3,
+ biblionumber => $biblionumber,
+ budget_id => $budget_id,
+ entrydate => '2016-01-02',
+})->insert;
+
+# Use user CSV profile
+my $basket_csv1 = C4::Acquisition::GetBasketAsCSV($basketno, $query, $csv_profile->export_format_id);
+is($basket_csv1, 'autor,title,quantity
+"King, Stephen","Test Record",3
+', 'CSV should be generated with user profile');
+
+# Use defautl template
+my $basket_csv2 = C4::Acquisition::GetBasketAsCSV($basketno, $query);
+is($basket_csv2, 'Contract name,Order number,Entry date,ISBN,Author,Title,Publication year,Publisher,Collection title,Note for vendor,Quantity,RRP,Delivery place,Billing place
+
+"",' . $order->{ordernumber} . ',2016-01-02,,"King, Stephen","Test Record",,"","","",3,,"",""
+
+', 'CSV should be generated with default template');
+
+$schema->storage->txn_rollback();
my $new_csv_profile_1 = Koha::CsvProfile->new({
profile => 'my_csv_profile_name_for_test_1',
description => 'my_csv_profile_description_for_test_1',
- type => 'sql'
+ type => 'late_issues'
})->store;
my $new_csv_profile_2 = Koha::CsvProfile->new({
profile => 'my_csv_profile_name_for_test_2',