From 17e5f505a0eaf2d92cc6ffb603e130c66c8bdb20 Mon Sep 17 00:00:00 2001
From: Kyle M Hall <kyle@bywatersolutions.com>
Date: Wed, 6 Nov 2013 14:04:21 -0500
Subject: [PATCH] Bug 9303 - relative's checkouts in the opac

This patch gives patrons the option of allowing other
linked relatives to view his or her checkouts from
opac-user.pl in a manner similiar to the patron's own
checkouts tab. By default the setting is not enabled.

Test Plan:
1) Apply this patch
2) Run updatedatabase.pl
3) Create three borrowers, link them as relatives
4) In the OPAC, go to "my privacy" and choose to allow
   relatives to view current checkouts for borrowers A and B
5) Check out some items to each borrower
6) Log into the OPAC as borrower A, you should see a "Relatives'
   checkouts" tab. In this tab you should see the checkouts for
   borrower B
7) Log into the OPAC as borrower C, you should be able to view
   the current checkouts for both borrower A and borrower B
---
 C4/Members.pm                                      |   26 -------
 Koha/Schema/Result/Issue.pm                        |   80 +++++++------------
 installer/data/mysql/kohastructure.sql             |    3 +
 installer/data/mysql/updatedatabase.pl             |   12 +++
 .../opac-tmpl/prog/en/modules/opac-privacy.tt      |   21 +++++-
 koha-tmpl/opac-tmpl/prog/en/modules/opac-user.tt   |   50 ++++++++++++-
 opac/opac-privacy.pl                               |   38 ++++++----
 opac/opac-user.pl                                  |   25 ++++--
 8 files changed, 153 insertions(+), 102 deletions(-)

diff --git a/C4/Members.pm b/C4/Members.pm
index b04cc64..fe2131c 100644
--- a/C4/Members.pm
+++ b/C4/Members.pm
@@ -112,7 +112,6 @@ BEGIN {
     push @EXPORT, qw(
         &ModMember
         &changepassword
-         &ModPrivacy
     );
 
     #Delete data
@@ -2216,31 +2215,6 @@ sub GetBorrowersNamesAndLatestIssue {
     return $results;
 }
 
-=head2 ModPrivacy
-
-=over 4
-
-my $success = ModPrivacy( $borrowernumber, $privacy );
-
-Update the privacy of a patron.
-
-return :
-true on success, false on failure
-
-=back
-
-=cut
-
-sub ModPrivacy {
-    my $borrowernumber = shift;
-    my $privacy = shift;
-    return unless defined $borrowernumber;
-    return unless $borrowernumber =~ /^\d+$/;
-
-    return ModMember( borrowernumber => $borrowernumber,
-                      privacy        => $privacy );
-}
-
 =head2 AddMessage
 
   AddMessage( $borrowernumber, $message_type, $message, $branchcode );
diff --git a/Koha/Schema/Result/Issue.pm b/Koha/Schema/Result/Issue.pm
index 282c802..5c834a8 100644
--- a/Koha/Schema/Result/Issue.pm
+++ b/Koha/Schema/Result/Issue.pm
@@ -1,21 +1,17 @@
-use utf8;
 package Koha::Schema::Result::Issue;
 
 # Created by DBIx::Class::Schema::Loader
 # DO NOT MODIFY THE FIRST PART OF THIS FILE
 
-=head1 NAME
-
-Koha::Schema::Result::Issue
-
-=cut
-
 use strict;
 use warnings;
 
 use base 'DBIx::Class::Core';
 
-=head1 TABLE: C<issues>
+
+=head1 NAME
+
+Koha::Schema::Result::Issue
 
 =cut
 
@@ -23,6 +19,12 @@ __PACKAGE__->table("issues");
 
 =head1 ACCESSORS
 
+=head2 issue_id
+
+  data_type: 'integer'
+  is_auto_increment: 1
+  is_nullable: 0
+
 =head2 borrowernumber
 
   data_type: 'integer'
@@ -38,7 +40,6 @@ __PACKAGE__->table("issues");
 =head2 date_due
 
   data_type: 'datetime'
-  datetime_undef_if_invalid: 1
   is_nullable: 1
 
 =head2 branchcode
@@ -56,13 +57,11 @@ __PACKAGE__->table("issues");
 =head2 returndate
 
   data_type: 'datetime'
-  datetime_undef_if_invalid: 1
   is_nullable: 1
 
 =head2 lastreneweddate
 
   data_type: 'datetime'
-  datetime_undef_if_invalid: 1
   is_nullable: 1
 
 =head2 return
@@ -79,63 +78,47 @@ __PACKAGE__->table("issues");
 =head2 timestamp
 
   data_type: 'timestamp'
-  datetime_undef_if_invalid: 1
   default_value: current_timestamp
   is_nullable: 0
 
 =head2 issuedate
 
   data_type: 'datetime'
-  datetime_undef_if_invalid: 1
   is_nullable: 1
 
 =cut
 
 __PACKAGE__->add_columns(
+  "issue_id",
+  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
   "borrowernumber",
   { data_type => "integer", is_foreign_key => 1, is_nullable => 1 },
   "itemnumber",
   { data_type => "integer", is_foreign_key => 1, is_nullable => 1 },
   "date_due",
-  {
-    data_type => "datetime",
-    datetime_undef_if_invalid => 1,
-    is_nullable => 1,
-  },
+  { data_type => "datetime", is_nullable => 1 },
   "branchcode",
   { data_type => "varchar", is_nullable => 1, size => 10 },
   "issuingbranch",
   { data_type => "varchar", is_nullable => 1, size => 18 },
   "returndate",
-  {
-    data_type => "datetime",
-    datetime_undef_if_invalid => 1,
-    is_nullable => 1,
-  },
+  { data_type => "datetime", is_nullable => 1 },
   "lastreneweddate",
-  {
-    data_type => "datetime",
-    datetime_undef_if_invalid => 1,
-    is_nullable => 1,
-  },
+  { data_type => "datetime", is_nullable => 1 },
   "return",
   { data_type => "varchar", is_nullable => 1, size => 4 },
   "renewals",
   { data_type => "tinyint", is_nullable => 1 },
   "timestamp",
   {
-    data_type => "timestamp",
-    datetime_undef_if_invalid => 1,
+    data_type     => "timestamp",
     default_value => \"current_timestamp",
-    is_nullable => 0,
+    is_nullable   => 0,
   },
   "issuedate",
-  {
-    data_type => "datetime",
-    datetime_undef_if_invalid => 1,
-    is_nullable => 1,
-  },
+  { data_type => "datetime", is_nullable => 1 },
 );
+__PACKAGE__->set_primary_key("issue_id");
 
 =head1 RELATIONS
 
@@ -151,12 +134,7 @@ __PACKAGE__->belongs_to(
   "borrowernumber",
   "Koha::Schema::Result::Borrower",
   { borrowernumber => "borrowernumber" },
-  {
-    is_deferrable => 1,
-    join_type     => "LEFT",
-    on_delete     => "CASCADE",
-    on_update     => "CASCADE",
-  },
+  { join_type => "LEFT", on_delete => "CASCADE", on_update => "CASCADE" },
 );
 
 =head2 itemnumber
@@ -171,17 +149,12 @@ __PACKAGE__->belongs_to(
   "itemnumber",
   "Koha::Schema::Result::Item",
   { itemnumber => "itemnumber" },
-  {
-    is_deferrable => 1,
-    join_type     => "LEFT",
-    on_delete     => "CASCADE",
-    on_update     => "CASCADE",
-  },
+  { join_type => "LEFT", on_delete => "CASCADE", on_update => "CASCADE" },
 );
 
 
-# Created by DBIx::Class::Schema::Loader v0.07025 @ 2013-10-14 20:56:21
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ZEh31EKBmURMKxDxI+H3EA
+# Created by DBIx::Class::Schema::Loader v0.07000 @ 2013-11-06 14:03:30
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:x90AnyA32VcLbXMb7hfA9g
 
 __PACKAGE__->belongs_to(
   "borrower",
@@ -190,4 +163,11 @@ __PACKAGE__->belongs_to(
   { join_type => "LEFT", on_delete => "CASCADE", on_update => "CASCADE" },
 );
 
+__PACKAGE__->belongs_to(
+  "item",
+  "Koha::Schema::Result::Item",
+  { itemnumber => "itemnumber" },
+  { join_type => "LEFT", on_delete => "CASCADE", on_update => "CASCADE" },
+);
+
 1;
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index fefc985..afcfd1a 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -265,6 +265,7 @@ CREATE TABLE `borrowers` ( -- this table includes information about your patrons
   `altcontactphone` varchar(50) default NULL, -- the phone number for the alternate contact for the patron/borrower
   `smsalertnumber` varchar(50) default NULL, -- the mobile phone number where the patron/borrower would like to receive notices (if SNS turned on)
   `privacy` integer(11) DEFAULT '1' NOT NULL, -- patron/borrower's privacy settings related to their reading history
+  `privacy_relative_checkouts` tinyint(1) NOT NULL DEFAULT '0', -- controls if relatives can see this patron's checkouts
   UNIQUE KEY `cardnumber` (`cardnumber`),
   PRIMARY KEY `borrowernumber` (`borrowernumber`),
   KEY `categorycode` (`categorycode`),
@@ -1107,6 +1108,7 @@ CREATE TABLE `import_items` (
 
 DROP TABLE IF EXISTS `issues`;
 CREATE TABLE `issues` ( -- information related to check outs or issues
+  `issue_id` int(11) NOT NULL AUTO_INCREMENT, -- primary key for issues table
   `borrowernumber` int(11), -- foreign key, linking this to the borrowers table for the patron this item was checked out to
   `itemnumber` int(11), -- foreign key, linking this to the items table for the item that was checked out
   `date_due` datetime default NULL, -- datetime the item is due (yyyy-mm-dd hh:mm::ss)
@@ -1118,6 +1120,7 @@ CREATE TABLE `issues` ( -- information related to check outs or issues
   `renewals` tinyint(4) default NULL, -- lists the number of times the item was renewed
   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, -- the date and time this record was last touched
   `issuedate` datetime default NULL, -- date the item was checked out or issued
+  PRIMARY KEY (`issue_id`),
   KEY `issuesborridx` (`borrowernumber`),
   KEY `itemnumber_idx` (`itemnumber`),
   KEY `branchcode_idx` (`branchcode`),
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 6162bc5..8e1b494 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -7743,6 +7743,18 @@ if(CheckVersion($DBversion)) {
     SetVersion($DBversion);
 }
 
+$DBversion = "3.15.00.XXX";
+if(CheckVersion($DBversion)) {
+    $dbh->do(q{
+        ALTER TABLE borrowers ADD privacy_relative_checkouts BOOLEAN NOT NULL DEFAULT '0'
+    });
+    $dbh->do(q{
+        ALTER TABLE issues ADD issue_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
+    });
+    print "Upgrade to $DBversion done (Bug 9303 - relative's checkouts in the opac)\n";
+    SetVersion($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
diff --git a/koha-tmpl/opac-tmpl/prog/en/modules/opac-privacy.tt b/koha-tmpl/opac-tmpl/prog/en/modules/opac-privacy.tt
index 1106973..632c9c7 100644
--- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-privacy.tt
+++ b/koha-tmpl/opac-tmpl/prog/en/modules/opac-privacy.tt
@@ -35,7 +35,8 @@
     </div>
     <form action="/cgi-bin/koha/opac-privacy.pl" method="post" id="opac-privacy-update-form">
             <input type="hidden" name="op" value="update_privacy" />
-            <label for:"privacy">Please choose your privacy rule:</label>
+
+            <label for="privacy">Please choose your privacy rule:</label>
             <select name="privacy">
                 [% IF ( privacy0 ) %]
                     <option value="0" selected="1" class="privacy0">Forever</option>
@@ -53,7 +54,23 @@
                     <option value="2" class="privacy2">Never</option>
                 [% END %]
             </select>
-            <input type="Submit" value="Submit" />
+
+            <p/>
+
+            <label for="privacy_relative_checkouts">Allow relatives to view your current checkouts?</label>
+            <select name="privacy_relative_checkouts">
+                [% IF borrower.privacy_relative_checkouts %]
+                    <option value="0">No</option>
+                    <option value="1" selected>Yes</option>
+                [% ELSE %]
+                    <option value="0" selected>No</option>
+                    <option value="1">Yes</option>
+                [% END %]
+            </select>
+
+            <p/>
+
+            <input type="Submit" value="Save" />
         </form>
         <h2>Immediate deletion</h2>
         <form action="/cgi-bin/koha/opac-privacy.pl" method="post" id="opac-privacy-delete-form">
diff --git a/koha-tmpl/opac-tmpl/prog/en/modules/opac-user.tt b/koha-tmpl/opac-tmpl/prog/en/modules/opac-user.tt
index 12b9f23..374000b 100644
--- a/koha-tmpl/opac-tmpl/prog/en/modules/opac-user.tt
+++ b/koha-tmpl/opac-tmpl/prog/en/modules/opac-user.tt
@@ -1,5 +1,6 @@
 [% USE Koha %]
 [% USE KohaDates %]
+[% USE EncodeUTF8 %]
 [% INCLUDE 'doc-head-open.inc' %]
 [% IF ( LibraryNameTitle ) %][% LibraryNameTitle %][% ELSE %]Koha online[% END %] catalog &rsaquo; Your library home
 [% INCLUDE 'doc-head-close.inc' %]
@@ -14,7 +15,7 @@ var MSG_CONFIRM_RESUME_HOLDS  = _("Are you sure you want to resume all suspended
             $(function() {
             $('#opac-user-views').tabs();
 
-        var dTables = $("#checkoutst,#holdst,#overduest");
+        var dTables = $("#checkoutst,#holdst,#overduest,#opac-user-relative-issues-table");
         dTables.each(function(){
             var thIndex = $(this).find("th.psort").index();
             $(this).dataTable($.extend(true, {}, dataTablesDefaults, {
@@ -117,6 +118,7 @@ var MSG_CONFIRM_RESUME_HOLDS  = _("Are you sure you want to resume all suspended
 <div id="opac-user-views" class="toptabs">
         <ul>
             <li><a href="#opac-user-checkouts">Checked out</a></li>
+            [% IF relatives %]<li><a href="#opac-user-relative-issues">Relatives' checkouts</a></li>[% END %]
             [% IF ( overdues_count ) %]<li><a href="#opac-user-overdues">Overdue</a></li>[% END %]
 [% IF ( OPACFinesTab ) %]
             [% IF ( BORROWER_INF.amountoverfive ) %]<li><a href="#opac-user-fines">Fines</a></li>[% END %]
@@ -285,6 +287,52 @@ var MSG_CONFIRM_RESUME_HOLDS  = _("Are you sure you want to resume all suspended
         [% END %]
     [% END %]
 
+    [% IF relatives %]
+        <div id="opac-user-relative-issues">
+            <table id="opac-user-relative-issues-table">
+                <thead>
+                    <tr>
+                        <th class="anti-the">Title</th>
+                        <th>Due</th>
+                        <th>Barcode</th>
+                        <th>Call No.</th>
+                        <th class="psort">Relative</th>
+                    </tr>
+                </thead>
+
+                <tbody>
+                    [% FOREACH r IN relatives %]
+                        [% FOREACH i IN r.issues %]
+                            <tr>
+                                <td>
+                                    <a href="/cgi-bin/koha/opac-detail.pl?biblionumber=[% i.item.biblio.biblionumber %]">
+                                        [% i.item.biblio.title | $EncodeUTF8 %]
+                                    </a>
+                                </td>
+
+                                <td>
+                                    [% i.date_due | $KohaDates %]
+                                </td>
+
+                                <td>
+                                    [% i.item.barcode %]
+                                </td>
+
+                                <td>
+                                    [% i.itemcallnumber | $EncodeUTF8 %]
+                                </td>
+
+                                <td>
+                                    [% r.firstname | $EncodeUTF8 %] [% r.surname | $EncodeUTF8 %]
+                                </td>
+                            </tr>
+                        [% END %]
+                    [% END %]
+                </tbody>
+            </table>
+        </div>
+    [% END %]
+
 [% IF ( overdues_count ) %]
 <div id="opac-user-overdues"><table id="overduest">
 <caption>Overdues <span class="count">([% overdues_count %] total)</span></caption>
diff --git a/opac/opac-privacy.pl b/opac/opac-privacy.pl
index 22fe694..c41e394 100755
--- a/opac/opac-privacy.pl
+++ b/opac/opac-privacy.pl
@@ -40,18 +40,25 @@ my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
     }
 );
 
-my $op = $query->param("op");
-my $privacy = $query->param("privacy");
+my $op                         = $query->param("op");
+my $privacy                    = $query->param("privacy");
+my $privacy_relative_checkouts = $query->param("privacy_relative_checkouts");
 
-if ($op eq "update_privacy")
-{
-    ModPrivacy($borrowernumber,$privacy);
-    $template->param('privacy_updated' => 1);
+if ( $op eq "update_privacy" ) {
+    ModMember(
+        borrowernumber             => $borrowernumber,
+        privacy                    => $privacy,
+        privacy_relative_checkouts => $privacy_relative_checkouts,
+    );
+    $template->param( 'privacy_updated' => 1 );
 }
-if ($op eq "delete_record") {
+elsif ( $op eq "delete_record" ) {
+
     # delete all reading records for items returned
     # uses a hardcoded date ridiculously far in the future
-    my ($rows,$err_history_not_deleted) = AnonymiseIssueHistory('2999-12-12',$borrowernumber);
+    my ( $rows, $err_history_not_deleted ) =
+      AnonymiseIssueHistory( '2999-12-12', $borrowernumber );
+
     # confirm the user the deletion has been done
     if ( !$err_history_not_deleted ) {
         $template->param( 'deleted' => 1 );
@@ -60,14 +67,17 @@ if ($op eq "delete_record") {
         $template->param( 'err_history_not_deleted' => 1 );
     }
 }
+
 # get borrower privacy ....
-my ( $borr ) = GetMemberDetails( $borrowernumber );
+my $borrower = GetMemberDetails( $borrowernumber );
 
-$template->param( 'Ask_data'       => '1',
-                    'privacy'.$borr->{'privacy'} => 1,
-                    'firstname' => $borr->{'firstname'},
-                    'surname' => $borr->{'surname'},
-                    'privacyview' => 1,
+$template->param(
+    'Ask_data'                         => 1,
+    'privacy' . $borrower->{'privacy'} => 1,
+    'privacyview'                      => 1,
+    'borrower'                         => $borrower,
+    'surname'                          => $borrower->{surname},
+    'firstname'                        => $borrower->{firstname},
 );
 
 output_html_with_http_headers $query, $cookie, $template->output;
diff --git a/opac/opac-user.pl b/opac/opac-user.pl
index 02002a3..af4218a 100755
--- a/opac/opac-user.pl
+++ b/opac/opac-user.pl
@@ -35,6 +35,7 @@ use C4::Items;
 use C4::Letters;
 use C4::Branch; # GetBranches
 use Koha::DateUtils;
+use Koha::Database;
 
 use constant ATTRIBUTE_SHOW_BARCODE => 'SHOW_BCODE';
 
@@ -352,18 +353,24 @@ if ( $borr->{'opacnote'} ) {
   );
 }
 
-$template->param(
-    bor_messages_loop    => GetMessages( $borrowernumber, 'B', 'NONE' ),
-    waiting_count      => $wcount,
-    patronupdate => $patronupdate,
-    OpacRenewalAllowed => C4::Context->preference("OpacRenewalAllowed"),
-    userview => 1,
+my @borrowernumbers = GetMemberRelatives($borrowernumber);
+my @relatives = Koha::Database->new()->schema()->resultset("Borrower")->search(
+    { privacy_relative_checkouts => 1,  'me.borrowernumber' => { -in => \@borrowernumbers, } },
+    { prefetch => [ { 'issues' => { 'item' => 'biblio' } } ] }
 );
+$template->param( relatives => \@relatives );
 
 $template->param(
-    SuspendHoldsOpac => C4::Context->preference('SuspendHoldsOpac'),
-    AutoResumeSuspendedHolds => C4::Context->preference('AutoResumeSuspendedHolds'),
-    OpacHoldNotes => C4::Context->preference('OpacHoldNotes'),
+    borrower           => $borr,
+    bor_messages_loop  => GetMessages( $borrowernumber, 'B', 'NONE' ),
+    waiting_count      => $wcount,
+    patronupdate       => $patronupdate,
+    OpacRenewalAllowed => C4::Context->preference("OpacRenewalAllowed"),
+    userview           => 1,
+    SuspendHoldsOpac   => C4::Context->preference('SuspendHoldsOpac'),
+    OpacHoldNotes      => C4::Context->preference('OpacHoldNotes'),
+    AutoResumeSuspendedHolds =>
+      C4::Context->preference('AutoResumeSuspendedHolds'),
 );
 
 output_html_with_http_headers $query, $cookie, $template->output;
-- 
1.7.2.5