Bug 10855: Search subscriptions by additional fields

Now you will abble to search subscriptions by additional fields.
The additional field values will be displayed in the table results.

Test plan:
- go on the subscriptions advanced search page
  (serials/serials-search.pl).
- verify all searchable additional fields are displayed on the form.
- combine 1 or more values and verify results are consistent.
- verify the values are displayed in new columns of the table.
- for field linked to an authorised value category, the description is
  displayed (not the code).

Signed-off-by: Brendan Gallagher <brendan@bywatersolutions.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>

diff --git a/C4/Serials.pm b/C4/Serials.pm
index 03362fc..e078344 100644 (file)
--- a/C4/Serials.pm
+++ b/C4/Serials.pm
@@ -558,7 +558,17 @@ subscription expiration date.
 sub SearchSubscriptions {
     my ( $args ) = @_;
 
-    my $query = q{
+    my $additional_fields = $args->{additional_fields} // [];
+    my $matching_record_ids_for_additional_fields = [];
+    if ( @$additional_fields ) {
+        $matching_record_ids_for_additional_fields = Koha::AdditionalField->get_matching_record_ids({
+                fields => $args->{additional_fields},
+                tablename => 'subscription',
+        });
+        return () unless @$matching_record_ids_for_additional_fields;
+    }
+
+    my $query = q|
         SELECT
             subscription.notes AS publicnotes,
             subscriptionhistory.*,
@@ -573,13 +583,15 @@ sub SearchSubscriptions {
             LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
             LEFT JOIN biblioitems ON biblioitems.biblionumber = subscription.biblionumber
             LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
-    };
+    |;
+    $query .= q| WHERE 1|;
     my @where_strs;
     my @where_args;
     if( $args->{biblionumber} ) {
         push @where_strs, "biblio.biblionumber = ?";
         push @where_args, $args->{biblionumber};
     }
+
     if( $args->{title} ){
         my @words = split / /, $args->{title};
         my (@strs, @args);
@@ -628,8 +640,14 @@ sub SearchSubscriptions {
         push @where_strs, "subscription.closed = ?";
         push @where_args, "$args->{closed}";
     }
+
     if(@where_strs){
-        $query .= " WHERE " . join(" AND ", @where_strs);
+        $query .= ' AND ' . join(' AND ', @where_strs);
+    }
+    if ( @$additional_fields ) {
+        $query .= ' AND subscriptionid IN ('
+            . join( ', ', @$matching_record_ids_for_additional_fields )
+        . ')';
     }
 
     $query .= " ORDER BY " . $args->{orderby} if $args->{orderby};
@@ -637,12 +655,17 @@ sub SearchSubscriptions {
     my $dbh = C4::Context->dbh;
     my $sth = $dbh->prepare($query);
     $sth->execute(@where_args);
-    my $results = $sth->fetchall_arrayref( {} );
-    $sth->finish;
+    my $results =  $sth->fetchall_arrayref( {} );
 
     for my $subscription ( @$results ) {
         $subscription->{cannotedit} = not can_edit_subscription( $subscription );
         $subscription->{cannotdisplay} = not can_show_subscription( $subscription );
+
+        my $additional_field_values = Koha::AdditionalField->fetch_all_values({
+            record_id => $subscription->{subscriptionid},
+            tablename => 'subscription'
+        });
+        $subscription->{addition_fields} = $additional_field_values->{$subscription->{subscriptionid}};
     }
 
     return @$results;

diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/serials-menu.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/serials-menu.inc
index b6c4b32..21dff3a 100644 (file)
--- a/koha-tmpl/intranet-tmpl/prog/en/includes/serials-menu.inc
+++ b/koha-tmpl/intranet-tmpl/prog/en/includes/serials-menu.inc
@@ -30,4 +30,5 @@
             Manage numbering patterns
         </a>
     </li>
+    <li><a href="/cgi-bin/koha/serials/add_fields.pl">Add subscription fields</a></li>
 </ul>

diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-search.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-search.tt
index 3b97849..31eea9a 100644 (file)
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-search.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/serials-search.tt
@@ -20,6 +20,7 @@
     } ) );
 
     var csrlt = $("#csrlt").dataTable($.extend(true, {}, dataTablesDefaults, {
+        // FIXME sort function of additional_fields!
         "sPaginationType": "four_button",
         "aoColumnDefs": [
             { 'bSortable': false, 'aTargets': [ 'NoSort' ] },
@@ -116,6 +117,21 @@
                   <label for="to">Expires before:</label>
                   <input type="text" id="to" name="expiration_date_filter" value="[% expiration_date_filter | $KohaDates %]" size="10" maxlength="10" class="datepickerto" />
                 </li>
+                [% FOR field IN additional_fields_for_subscription %]
+                  <li>
+                    <label for="additional_field_[% field.id %]"> [% field.name %]: </label>
+                    [% IF field.authorised_value_choices %]
+                      <select id="additional_field_[% field.id %]" name="additional_field_[% field.id %]_filter">
+                        <option value="">All</option>
+                        [% FOREACH av IN field.authorised_value_choices %]
+                          <option value="[% av.authorised_value %]">[% av.lib %]</option>
+                        [% END %]
+                      </select>
+                    [% ELSE %]
+                      <input id="additional_field_[% field.id %]" type="text" value="[% additional_fields.${field.name} %]" name="additional_field_[% field.id %]_filter" />
+                    [% END %]
+                  </li>
+                [% END %]
               </ol>
               <input type="hidden" name="searched" value="1" />
               <fieldset class="action">
@@ -144,6 +160,9 @@
                         <th>Location</th>
                         <th>Call number</th>
                         <th class="title-string">Expiration date</th>
+                        [% FOR field IN additional_fields_for_subscription %]
+                          <th>[% field.name %]</th>
+                        [% END %]
                         <th class="NoSort">Actions</th>
                     </tr>
                   </thead>
@@ -156,6 +175,9 @@
                       <td><input type="text" class="dt-filter" data-column_num="4" placeholder="Search location" /></td>
                       <td><input type="text" class="dt-filter" data-column_num="5" placeholder="Search callnumber" /></td>
                       <td><input type="text" class="dt-filter" data-column_num="6" placeholder="Search expiration date" /></td>
+                      [% FOR field IN additional_fields_for_subscription %]
+                        <td><input type="text" class="dt-filter" data-column_num="[% loop.count + 6 %]" placeholder="Search [% field.name %]" /></td>
+                      [% END %]
                       <td></td>
                     </tr>
                   </tfoot>
@@ -189,6 +211,14 @@
                             [% END %]
                         </td>
 
+                        [% FOR field IN additional_fields_for_subscription %]
+                          [% IF field.authorised_value_category %]
+                            <td>[% AuthorisedValues.GetByCode( field.authorised_value_category, subscription.additional_fields.${field.name} ) %]</td>
+                          [% ELSE %]
+                            <td>[% subscription.additional_fields.${field.name} %]</td>
+                          [% END %]
+                        [% END %]
+
                         <td>
                             <div class="dropdown">
                                 <a class="btn btn-mini dropdown-toggle" id="subactions[% subscription.subscriptionid %]" role="button" data-toggle="dropdown" href="#">
@@ -237,6 +267,7 @@
             <div id="closed">
               [% IF closedsubscriptions %]
                 <table id="csrlt">
+                <!-- FIXME add additional_fields for closed subs -->
                   <thead>
                     <tr>
                         <th>ISSN</th>
@@ -245,6 +276,9 @@
                         <th>Library</th>
                         <th>Location</th>
                         <th>Call number</th>
+                        [% FOR field IN additional_fields_for_subscription %]
+                          <th>[% field.name %]</th>
+                        [% END %]
                         <th class="NoSort">Actions</th>
                     </tr>
                   </thead>
@@ -256,6 +290,9 @@
                       <td><input type="text" class="dt-filter" data-column_num="3" placeholder="Search library" /></td>
                       <td><input type="text" class="dt-filter" data-column_num="4" placeholder="Search location" /></td>
                       <td><input type="text" class="dt-filter" data-column_num="5" placeholder="Search callnumber" /></td>
+                      [% FOR field IN additional_fields_for_subscription %]
+                        <td><input type="text" class="dt-filter" data-column_num="[% loop.count + 5 %]" placeholder="Search [% field.name %]" /></td>
+                      [% END %]
                       <td></td>
                     </tr>
                   </tfoot>
@@ -284,6 +321,13 @@
                         <td>
                           [% IF ( subscription.callnumber ) %][% subscription.callnumber %][% END %]
                         </td>
+                        [% FOR field IN additional_fields_for_subscription %]
+                          [% IF field.authorised_value_category %]
+                            <td>[% KohaAuthorisedValues.GetByCode( field.authorised_value_category, subscription.additional_fields.${field.name} ) %]</td>
+                          [% ELSE %]
+                            <td>[% subscription.additional_fields.${field.name} %]</td>
+                          [% END %]
+                        [% END %]
                         <td>
                             <div class="dropdown">
                                 <a class="btn btn-mini dropdown-toggle" id="closedsubactions[% subscription.subscriptionid %]" role="button" data-toggle="dropdown" href="#">
@@ -394,6 +438,25 @@
                   <input type="text" id="to" name="expiration_date_filter" value="[% expiration_date_filter | $KohaDates %]" size="10" maxlength="10" class="datepickerto" />
                 </li>
 
+                [% FOR field IN additional_fields_for_subscription %]
+                  <li>
+                    <label for="additional_field_[% field.id %]ID"> [% field.name %]: </label>
+                    [% IF field.authorised_value_choices %]
+                      <select id="additional_field_[% field.id %]" name="additional_field_[% field.id %]_filter">
+                        <option value="">All</option>
+                        [% FOREACH av IN field.authorised_value_choices %]
+                          [% IF av.authorised_value == additional_field_filters.${field.name} %]
+                            <option value="[% av.authorised_value %]" selected="selected">[% av.lib %]</option>
+                          [% ELSE %]
+                            <option value="[% av.authorised_value %]">[% av.lib %]</option>
+                          [% END %]
+                        [% END %]
+                      </select>
+                    [% ELSE %]
+                      <input id="additional_field_[% field.id %]" type="text" value="[% additional_field_filters.${field.name} %]" name="additional_field_[% field.id %]_filter" />
+                    [% END %]
+                  </li>
+                [% END %]
               </ol>
               <input type="hidden" name="searched" value="1" />
               <fieldset class="action">

diff --git a/serials/serials-search.pl b/serials/serials-search.pl
index 27ccd42..f27a49f 100755 (executable)
--- a/serials/serials-search.pl
+++ b/serials/serials-search.pl
@@ -33,8 +33,10 @@ use CGI qw ( -utf8 );
 use C4::Auth;
 use C4::Branch;
 use C4::Context;
+use C4::Koha qw( GetAuthorisedValues );
 use C4::Output;
 use C4::Serials;
+use Koha::AdditionalField;
 
 use Koha::DateUtils;
 
@@ -75,6 +77,18 @@ if ( $op and $op eq "close" ) {
     }
 }
 
+
+my $additional_fields = Koha::AdditionalField->all( { table => 'subscription', searchable => 1 } );
+my $additional_field_filters;
+for my $field ( @$additional_fields ) {
+    if ( my $filter_value = $query->param('additional_field_' . $field->{name} . '_filter') ) {
+        $additional_field_filters->{ $field->{name} } = $filter_value;
+    }
+    if ( $field->{authorised_value_category} ) {
+        $field->{authorised_value_choices} = GetAuthorisedValues( $field->{authorised_value_category} );
+    }
+}
+
 my $expiration_date_dt = $expiration_date ? dt_from_string( $expiration_date ) : undef;
 my @subscriptions;
 if ($searched){
@@ -88,6 +102,7 @@ if ($searched){
             publisher    => $publisher,
             bookseller   => $bookseller,
             branch       => $branch,
+            additional_fields => [ map{ { name => $_, value => $additional_field_filters->{$_}} } keys %$additional_field_filters ],
             location     => $location,
             expiration_date => $expiration_date_dt,
         }
@@ -141,6 +156,8 @@ $template->param(
     branches_loop => \@branches_loop,
     done_searched => $searched,
     routing       => $routing,
+    additional_field_filters => $additional_field_filters,
+    additional_fields_for_subscription => $additional_fields,
     marcflavour   => (uc(C4::Context->preference("marcflavour")))
 );