From cd9a53c38fbbc26eff84c969b94ad4d6d4f1f6b4 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Thu, 12 Feb 2015 09:11:50 -0500 Subject: [PATCH] Bug 13704 - Add ability to backup patron attributes on update It would be useful to be able to auto-backup the previous values of some extended patron attributes. For example, if a person's driver's license ( stored in DRV_LC ) has changed, it this would allow the previous license number to be stored in a different attribute ( e.g. DRV_LC_PRV ). --- C4/Members/AttributeTypes.pm | 95 +++++++++---------- C4/Members/Attributes.pm | 58 +++++++++--- Koha/Patrons/Import.pm | 6 +- Koha/Schema/Result/BorrowerAttribute.pm | 24 +++++- Koha/Schema/Result/BorrowerAttributeType.pm | 55 +++++++++++- admin/patron-attr-types.pl | 7 ++- installer/data/mysql/kohastructure.sql | 6 ++ installer/data/mysql/updatedatabase.pl | 9 ++ .../prog/en/modules/admin/patron-attr-types.tt | 22 +++++ .../prog/en/modules/help/tools/import_borrowers.tt | 8 ++ .../prog/en/modules/tools/import_borrowers.tt | 4 + tools/import_borrowers.pl | 1 + 12 files changed, 224 insertions(+), 71 deletions(-) diff --git a/C4/Members/AttributeTypes.pm b/C4/Members/AttributeTypes.pm index 1754099..4af674e 100644 --- a/C4/Members/AttributeTypes.pm +++ b/C4/Members/AttributeTypes.pm @@ -20,6 +20,7 @@ package C4::Members::AttributeTypes; use strict; #use warnings; FIXME - Bug 2505 use C4::Context; +use Koha::Database; use vars qw($VERSION); @@ -135,6 +136,7 @@ sub new { $self->{'category_code'} = ''; $self->{'category_description'} = ''; $self->{'class'} = ''; + $self->{'backup_for'} = ''; bless $self, $class; return $self; @@ -177,6 +179,7 @@ sub fetch { $self->{'category_code'} = $row->{'category_code'}; $self->{'category_description'} = $row->{'category_description'}; $self->{'class'} = $row->{'class'}; + $self->{'backup_for'} = $row->{'backup_for'}; $sth = $dbh->prepare("SELECT branchcode, branchname FROM borrower_attribute_types_branches, branches WHERE b_branchcode = branchcode AND bat_code = ?;"); $sth->execute( $code ); @@ -202,58 +205,36 @@ method, the DB representation of the type is replaced. sub store { my $self = shift; - my $dbh = C4::Context->dbh; - my $sth; - my $existing = __PACKAGE__->fetch($self->{'code'}); - if (defined $existing) { - $sth = $dbh->prepare_cached("UPDATE borrower_attribute_types - SET description = ?, - repeatable = ?, - unique_id = ?, - opac_display = ?, - password_allowed = ?, - staff_searchable = ?, - authorised_value_category = ?, - display_checkout = ?, - category_code = ?, - class = ? - WHERE code = ?"); - } else { - $sth = $dbh->prepare_cached("INSERT INTO borrower_attribute_types - (description, repeatable, unique_id, opac_display, password_allowed, - staff_searchable, authorised_value_category, display_checkout, category_code, class, code) - VALUES (?, ?, ?, ?, ?, - ?, ?, ?, ?, ?, ?)"); - } - $sth->bind_param(1, $self->{'description'}); - $sth->bind_param(2, $self->{'repeatable'}); - $sth->bind_param(3, $self->{'unique_id'}); - $sth->bind_param(4, $self->{'opac_display'}); - $sth->bind_param(5, $self->{'password_allowed'}); - $sth->bind_param(6, $self->{'staff_searchable'}); - $sth->bind_param(7, $self->{'authorised_value_category'}); - $sth->bind_param(8, $self->{'display_checkout'}); - $sth->bind_param(9, $self->{'category_code'} || undef); - $sth->bind_param(10, $self->{'class'}); - $sth->bind_param(11, $self->{'code'}); - $sth->execute; - - if ( defined $$self{branches} ) { - $sth = $dbh->prepare("DELETE FROM borrower_attribute_types_branches WHERE bat_code = ?"); - $sth->execute( $$self{code} ); - $sth = $dbh->prepare( - "INSERT INTO borrower_attribute_types_branches - ( bat_code, b_branchcode ) - VALUES ( ?, ? )" - ); - for my $branchcode ( @{$$self{branches}} ) { - next if not $branchcode; - $sth->bind_param( 1, $$self{code} ); - $sth->bind_param( 2, $branchcode ); - $sth->execute; + my $schema = Koha::Database->new()->schema(); + + my $type = $schema->resultset('BorrowerAttributeType')->update_or_create( + { + + code => $self->{'code'}, + description => $self->{'description'}, + repeatable => $self->{'repeatable'}, + unique_id => $self->{'unique_id'}, + opac_display => $self->{'opac_display'}, + password_allowed => $self->{'password_allowed'}, + staff_searchable => $self->{'staff_searchable'}, + authorised_value_category => $self->{'authorised_value_category'}, + display_checkout => $self->{'display_checkout'}, + category_code => $self->{'category_code'} || undef, + class => $self->{'class'}, + backup_for => $self->{'backup_for'} || undef, + } + ); + + if ( defined $self->{branches} ) { + $type->borrower_attribute_types_branches()->delete(); + + my $rs = $schema->resultset('BorrowerAttributeTypesBranch'); + + for my $branchcode ( @{ $$self{branches} } ) { + next unless $branchcode; + $rs->create( { bat_code => $self->{code}, b_branchcode => $branchcode } ); } } - $sth->finish; } =head2 code @@ -442,6 +423,20 @@ sub class { @_ ? $self->{'class'} = shift : $self->{'class'}; } +=head2 backup_for + +my $backup_for = $attr_type->backup_for(); +$attr_type->backup_for($backup_for); + +Accessor. + +=cut + +sub backup_for { + my $self = shift; + @_ ? $self->{'backup_for'} = shift : $self->{'backup_for'}; +} + =head2 delete diff --git a/C4/Members/Attributes.pm b/C4/Members/Attributes.pm index f0ec07f..d54ba20 100644 --- a/C4/Members/Attributes.pm +++ b/C4/Members/Attributes.pm @@ -23,6 +23,7 @@ use warnings; use Text::CSV; # Don't be tempted to use Text::CSV::Unicode -- even in binary mode it fails. use C4::Context; use C4::Members::AttributeTypes; +use Koha::Database; use vars qw($VERSION @ISA @EXPORT_OK @EXPORT %EXPORT_TAGS); our ($csv, $AttributeTypes); @@ -216,24 +217,55 @@ replacing any that existed previously. =cut sub SetBorrowerAttributes { - my $borrowernumber = shift; - my $attr_list = shift; + my ( $borrowernumber, $attr_list, $backup ) = @_; - my $dbh = C4::Context->dbh; - my $delsth = $dbh->prepare("DELETE FROM borrower_attributes WHERE borrowernumber = ?"); - $delsth->execute($borrowernumber); + my $schema = Koha::Database->new()->schema(); + + my $rs = $schema->resultset('BorrowerAttribute'); + + my @attributes = $rs->search( { borrowernumber => $borrowernumber, backup_for => undef }, { prefetch => 'code' } ); + + warn "TEST"; + if ($backup) { + foreach my $a (@attributes) { + warn "CODE: " . $a->get_column('code'); + warn "VAL: " . $a->attribute(); + my $attribute_type = $a->code(); + + my @backup_attribute_type = $attribute_type->backup_to(); + + foreach my $b (@backup_attribute_type) { + warn "BACKUP CODE: " . $b->code(); + $b->borrower_attributes( { borrowernumber => $borrowernumber } )->delete() unless $b->repeatable(); + + $rs->create( + { + borrowernumber => $borrowernumber, + code => $b->get_column('code'), + attribute => $a->attribute(), + password => $a->password(), + } + ); + } + } + } + + map { $_->delete } @attributes; - my $sth = $dbh->prepare("INSERT INTO borrower_attributes (borrowernumber, code, attribute, password) - VALUES (?, ?, ?, ?)"); foreach my $attr (@$attr_list) { - $attr->{password} = undef unless exists $attr->{password}; - $sth->execute($borrowernumber, $attr->{code}, $attr->{value}, $attr->{password}); - if ($sth->err) { - warn sprintf('Database returned the following error: %s', $sth->errstr); - return; # bail immediately on errors + $attr->{borrowernumber} = $borrowernumber; + + $attr->{attribute} = $attr->{value}; # move 'value' to 'attribute' + delete( $attr->{value} ); + + my $a = $rs->create($attr); + + unless ($a) { + return; # bail immediately on errors } } - return 1; # borower attributes successfully set + + return 1; # borrower attributes successfully set } =head2 DeleteBorrowerAttribute diff --git a/Koha/Patrons/Import.pm b/Koha/Patrons/Import.pm index 1041262..ccefc24 100644 --- a/Koha/Patrons/Import.pm +++ b/Koha/Patrons/Import.pm @@ -21,6 +21,7 @@ use Carp; use Text::CSV; use C4::Members; +use C4::Members::Attributes qw(extended_attributes_code_value_arrayref SetBorrowerAttributes); use C4::Branch; sub import_patrons { @@ -30,6 +31,7 @@ sub import_patrons { my $matchpoint = $params->{matchpoint}; my $defaults = $params->{defaults}; my $ext_preserve = $params->{preserve_extended_attributes}; + my $ext_backup = $params->{backup_extended_attributes}; my $overwrite_cardnumber = $params->{overwrite_cardnumber}; carp("No file handle passed in!") && return unless $handle; @@ -280,7 +282,7 @@ sub import_patrons { $patron_attributes = extended_attributes_merge( $old_attributes, $patron_attributes ); } push @errors, { unknown_error => 1 } - unless SetBorrowerAttributes( $borrower{'borrowernumber'}, $patron_attributes ); + unless SetBorrowerAttributes( $borrower{'borrowernumber'}, $patron_attributes, $ext_backup ); } $overwritten++; push( @@ -311,7 +313,7 @@ sub import_patrons { } if ($extended) { - SetBorrowerAttributes( $borrowernumber, $patron_attributes ); + SetBorrowerAttributes( $borrowernumber, $patron_attributes, $ext_backup ); } if ($set_messaging_prefs) { diff --git a/Koha/Schema/Result/BorrowerAttribute.pm b/Koha/Schema/Result/BorrowerAttribute.pm index 0b7a96c..3ffac2b 100644 --- a/Koha/Schema/Result/BorrowerAttribute.pm +++ b/Koha/Schema/Result/BorrowerAttribute.pm @@ -23,6 +23,12 @@ __PACKAGE__->table("borrower_attributes"); =head1 ACCESSORS +=head2 borrower_attribute_id + + data_type: 'integer' + is_auto_increment: 1 + is_nullable: 0 + =head2 borrowernumber data_type: 'integer' @@ -51,6 +57,8 @@ __PACKAGE__->table("borrower_attributes"); =cut __PACKAGE__->add_columns( + "borrower_attribute_id", + { data_type => "integer", is_auto_increment => 1, is_nullable => 0 }, "borrowernumber", { data_type => "integer", is_foreign_key => 1, is_nullable => 0 }, "code", @@ -61,6 +69,18 @@ __PACKAGE__->add_columns( { data_type => "varchar", is_nullable => 1, size => 64 }, ); +=head1 PRIMARY KEY + +=over 4 + +=item * L + +=back + +=cut + +__PACKAGE__->set_primary_key("borrower_attribute_id"); + =head1 RELATIONS =head2 borrowernumber @@ -94,8 +114,8 @@ __PACKAGE__->belongs_to( ); -# Created by DBIx::Class::Schema::Loader v0.07025 @ 2013-10-14 20:56:21 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:9oRy+X25+b7vB03WSnpFTg +# Created by DBIx::Class::Schema::Loader v0.07040 @ 2015-02-12 10:44:36 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:oqWX6PRIe/RFV2VAImN3bA # You can replace this text with custom content, and it will be preserved on regeneration diff --git a/Koha/Schema/Result/BorrowerAttributeType.pm b/Koha/Schema/Result/BorrowerAttributeType.pm index f2ff811..75c5bac 100644 --- a/Koha/Schema/Result/BorrowerAttributeType.pm +++ b/Koha/Schema/Result/BorrowerAttributeType.pm @@ -90,6 +90,13 @@ __PACKAGE__->table("borrower_attribute_types"); is_nullable: 0 size: 255 +=head2 backup_for + + data_type: 'varchar' + is_foreign_key: 1 + is_nullable: 1 + size: 10 + =cut __PACKAGE__->add_columns( @@ -115,6 +122,8 @@ __PACKAGE__->add_columns( { data_type => "varchar", is_nullable => 1, size => 10 }, "class", { data_type => "varchar", default_value => "", is_nullable => 0, size => 255 }, + "backup_for", + { data_type => "varchar", is_foreign_key => 1, is_nullable => 1, size => 10 }, ); =head1 PRIMARY KEY @@ -131,6 +140,41 @@ __PACKAGE__->set_primary_key("code"); =head1 RELATIONS +=head2 backup_for + +Type: belongs_to + +Related object: L + +=cut + +__PACKAGE__->belongs_to( + "backup_for", + "Koha::Schema::Result::BorrowerAttributeType", + { code => "backup_for" }, + { + is_deferrable => 1, + join_type => "LEFT", + on_delete => "NO ACTION", + on_update => "NO ACTION", + }, +); + +=head2 borrower_attribute_types + +Type: has_many + +Related object: L + +=cut + +__PACKAGE__->has_many( + "borrower_attribute_types", + "Koha::Schema::Result::BorrowerAttributeType", + { "foreign.backup_for" => "self.code" }, + { cascade_copy => 0, cascade_delete => 0 }, +); + =head2 borrower_attribute_types_branches Type: has_many @@ -162,9 +206,14 @@ __PACKAGE__->has_many( ); -# Created by DBIx::Class::Schema::Loader v0.07039 @ 2015-01-13 13:14:54 -# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:9fLFZ/u89xmeCollneyUIg +# Created by DBIx::Class::Schema::Loader v0.07040 @ 2015-02-12 09:12:49 +# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:QNYfvxrDzUhlFIU2p4whLw +__PACKAGE__->has_many( + "backup_to", + "Koha::Schema::Result::BorrowerAttributeType", + { "foreign.backup_for" => "self.code" }, + { cascade_copy => 0, cascade_delete => 0 }, +); -# You can replace this text with custom content, and it will be preserved on regeneration 1; diff --git a/admin/patron-attr-types.pl b/admin/patron-attr-types.pl index 41ca15f..9a37c6c 100755 --- a/admin/patron-attr-types.pl +++ b/admin/patron-attr-types.pl @@ -53,6 +53,7 @@ my $code = $input->param("code"); my $display_list = 0; if ($op eq "edit_attribute_type") { + patron_attribute_type_list($template); edit_attribute_type_form($template, $code); } elsif ($op eq "edit_attribute_type_confirmed") { $display_list = add_update_attribute_type('edit', $template, $code); @@ -74,6 +75,7 @@ if ($display_list) { $template->param(WARNING_extended_attributes_off => 1); } patron_attribute_type_list($template); + $template->param(display_list => 1); } output_html_with_http_headers $input, $cookie, $template->output; @@ -129,6 +131,8 @@ sub error_add_attribute_type_form { $template->param( category_code => $input->param('category_code') ); $template->param( class => $input->param('class') ); + $template->param( backup_for => $input->param('backup_for') ); + $template->param( attribute_type_form => 1, confirm_op => 'add_attribute_type_confirmed', @@ -173,6 +177,7 @@ sub add_update_attribute_type { $attr_type->display_checkout($display_checkout); $attr_type->category_code($input->param('category_code')); $attr_type->class($input->param('class')); + $attr_type->backup_for($input->param('backup_for')); my @branches = $input->param('branches'); $attr_type->branches( \@branches ); @@ -233,6 +238,7 @@ sub edit_attribute_type_form { $template->param(code => $code); $template->param(description => $attr_type->description()); $template->param(class => $attr_type->class()); + $template->param(backup_for => $attr_type->backup_for()); if ($attr_type->repeatable()) { $template->param(repeatable_checked => 1); @@ -309,7 +315,6 @@ sub patron_attribute_type_list { }; } $template->param(available_attribute_types => \@attributes_loop); - $template->param(display_list => 1); } sub authorised_value_category_list { diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index cd83f99..10c1ad4 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -295,8 +295,12 @@ CREATE TABLE `borrower_attribute_types` ( -- definitions for custom patron field `display_checkout` tinyint(1) NOT NULL default 0,-- defines if this field displays in checkout screens `category_code` VARCHAR(10) NULL DEFAULT NULL,-- defines a category for an attribute_type `class` VARCHAR(255) NOT NULL DEFAULT '',-- defines a class for an attribute_type + `backup_for` varchar(10) DEFAULT NULL, -- reference to another type that it can be used as a backup for PRIMARY KEY (`code`), KEY `auth_val_cat_idx` (`authorised_value_category`) + KEY `backup_for` (`backup_for`) + CONSTRAINT `borrower_attribute_types_ibfk_1` FOREIGN KEY (`backup_for`) REFERENCES `borrower_attribute_types` (`code`) + ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- @@ -305,10 +309,12 @@ CREATE TABLE `borrower_attribute_types` ( -- definitions for custom patron field DROP TABLE IF EXISTS `borrower_attributes`; CREATE TABLE `borrower_attributes` ( -- values of custom patron fields known as extended patron attributes linked to patrons/borrowers + `borrower_attribute_id` int(11) NOT NULL AUTO_INCREMENT, `borrowernumber` int(11) NOT NULL, -- foreign key from the borrowers table, defines which patron/borrower has this attribute `code` varchar(10) NOT NULL, -- foreign key from the borrower_attribute_types table, defines which custom field this value was entered for `attribute` varchar(255) default NULL, -- custom patron field value `password` varchar(64) default NULL, -- password associated with this field + PRIMARY KEY (`borrower_attribute_id`), KEY `borrowernumber` (`borrowernumber`), KEY `code_attribute` (`code`, `attribute`), CONSTRAINT `borrower_attributes_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index dab1c62..4bb1a1f 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -9759,6 +9759,15 @@ if ( CheckVersion($DBversion) ) { SetVersion ($DBversion); } +$DBversion = "3.19.00.XXX"; +if ( CheckVersion($DBversion) ) { + $dbh->do("ALTER TABLE borrower_attribute_types ADD backup_for VARCHAR( 10 ) NULL, ADD INDEX ( backup_for )"); + $dbh->do("ALTER TABLE borrower_attribute_types ADD FOREIGN KEY ( backup_for ) REFERENCES borrower_attribute_types ( code ) ON DELETE NO ACTION ON UPDATE NO ACTION"); + $dbh->do("ALTER TABLE borrower_attributes ADD borrower_attribute_id INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST"); + print "Upgrade to $DBversion done (Bug 13704 - Add ability to backup patron attributes when updated)"; + SetVersion ($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/patron-attr-types.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/patron-attr-types.tt index 2fb8a68..230c51e 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/patron-attr-types.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/patron-attr-types.tt @@ -233,6 +233,28 @@ function CheckAttributeTypeForm(f) { Group attributes types with a block title (based on authorized values category 'PA_CLASS') +
  • + + + Use this attribute to backup another patron attribute when updating it. +
  • diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/help/tools/import_borrowers.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/help/tools/import_borrowers.tt index 73f0bc1..60c29c9 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/help/tools/import_borrowers.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/help/tools/import_borrowers.tt @@ -55,6 +55,14 @@ +

    Patron attributes

    + +

    + If the 'save attributes values being replaced' checkbox is checked, and there is an attribute with the same code, but with _B appended to it ( e.g. EXAMPLE => EXAMPLE_B ), Koha will move the current + attribute value to an instance of this _B value. If the _B attribute is repeatable, a new instance of the attribute will be created for each backup. If the _B instance is not repeatable, only the last + value of the attribute will be kept. +

    +

    See the full documentation for Patron Import in the manual (online).

    [% INCLUDE 'help-bottom.inc' %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/import_borrowers.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/import_borrowers.tt index 7a4c8af..ab0a648 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/import_borrowers.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/import_borrowers.tt @@ -222,6 +222,10 @@
  • + +
  • + +
  • [% END %] diff --git a/tools/import_borrowers.pl b/tools/import_borrowers.pl index 183ced1..8b1ef9d 100755 --- a/tools/import_borrowers.pl +++ b/tools/import_borrowers.pl @@ -117,6 +117,7 @@ if ( $uploadborrowers && length($uploadborrowers) > 0 ) { matchpoint => $matchpoint, overwrite_cardnumber => $input->param('overwrite_cardnumber'), preserve_extended_attributes => $input->param('ext_preserve') || 0, + backup_extended_attributes => $input->param('ext_backup') || 0, } ); -- 1.7.2.5