From acbfe26c9ef6e40f4509babd6e1d39e1762e13e3 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 28 Nov 2012 16:36:03 +0100 Subject: [PATCH] Bug 7919 : Display of values depending on the connexion library In a librairies network, we would like to declare specific values just for one (or more) library. Here we implement the ability to associate categories, patron attributes types and/or authorised_values with librairies (branches). This patch adds 3 new association tables: - categories_branches ( association table between categories and branches ) - authorised_values_branches ( association table between authorised_values and branches ) - borrower_attribute_types_branches (association table between borrower_attribute_types and branches ) Plan test: - Create (or modify) categories, patron attributes and authorised_values and link it with one (or more) library. - Set one of these librairies - Go to one of the multiple pages where this specific value must be displayed and check that it does appear. - Set a library not concerned. - Check on the same pages this value is doest not appear. A page list: cataloguing/addbiblio.pl cataloguing/additems.pl members/members-home.pl members/memberentry.pl acqui/neworderempty.pl tools/modborrowers.pl and others :) Please say me if filters don't work on some pages. Signed-off-by: Delaye Stephane Signed-off-by: Koha Team Lyon 3 Signed-off-by: Paul Poulain Signed-off-by: Melia Meggs Signed-off-by: Jared Camins-Esakov --- C4/Budgets.pm | 23 ++++-- C4/Category.pm | 25 +++++-- C4/Input.pm | 67 ++++++++++------- C4/Items.pm | 20 ++++- C4/Koha.pm | 46 +++++++++--- C4/Members.pm | 63 ++++++++++------ C4/Members/AttributeTypes.pm | 74 ++++++++++++++----- C4/Members/Attributes.pm | 2 + admin/authorised_values.pl | 66 ++++++++++++++++- admin/categorie.pl | 58 +++++++++++++-- admin/patron-attr-types.pl | 44 +++++++++-- cataloguing/addbiblio.pl | 22 ++++-- cataloguing/additem.pl | 13 ++-- installer/data/mysql/kohastructure.sql | 38 ++++++++++ installer/data/mysql/updatedatabase.pl | 11 +++ .../en/modules/admin/authorised_values.tt | 32 +++++++- .../prog/en/modules/admin/categorie.tt | 33 ++++++++- .../en/modules/admin/patron-attr-types.tt | 31 ++++++++ members/member.pl | 4 +- tools/batchMod.pl | 12 ++- tools/import_borrowers.pl | 2 +- 21 files changed, 555 insertions(+), 131 deletions(-) diff --git a/C4/Budgets.pm b/C4/Budgets.pm index d07dc75df2..a054ac7006 100644 --- a/C4/Budgets.pm +++ b/C4/Budgets.pm @@ -379,12 +379,25 @@ sub GetBudgetAuthCats { # ------------------------------------------------------------------- sub GetAuthvalueDropbox { my ( $authcat, $default ) = @_; + my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : ""; my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare( - 'SELECT authorised_value,lib FROM authorised_values - WHERE category = ? ORDER BY lib' - ); - $sth->execute( $authcat ); + + my $query = qq{ + SELECT * + FROM authorised_values + }; + $query .= qq{ + LEFT JOIN authorised_values_branches ON ( id = av_id ) + } if $branch_limit; + $query .= qq{ + WHERE category = ? + }; + $query .= " AND ( branchcode = ? OR branchcode IS NULL )" if $branch_limit; + $query .= " GROUP BY lib ORDER BY category, lib, lib_opac"; + my $sth = $dbh->prepare($query); + $sth->execute( $authcat, $branch_limit ? $branch_limit : () ); + + my $option_list = []; my @authorised_values = ( q{} ); while (my ($value, $lib) = $sth->fetchrow_array) { diff --git a/C4/Category.pm b/C4/Category.pm index 8cadc8b612..fcb3ed8168 100644 --- a/C4/Category.pm +++ b/C4/Category.pm @@ -74,13 +74,24 @@ C. =cut sub all { - my $class = shift; - map { - utf8::encode($_->{description}); - $class->new($_); - } @{C4::Context->dbh->selectall_arrayref( - "SELECT * FROM categories ORDER BY description", { Slice => {} } - )}; + my ( $class ) = @_; + my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : ""; + my $dbh = C4::Context->dbh; + # The categories table is small enough for + # `SELECT *` to be harmless. + my $query = "SELECT * FROM categories"; + $query .= qq{ + LEFT JOIN categories_branches ON categories_branches.categorycode = categories.categorycode + WHERE categories_branches.branchcode = ? OR categories_branches.branchcode IS NULL + } if $branch_limit; + $query .= " ORDER BY description"; + return map { $class->new($_) } @{ + $dbh->selectall_arrayref( + $query, + { Slice => {} }, + $branch_limit ? $branch_limit : () + ) + }; } diff --git a/C4/Input.pm b/C4/Input.pm index 2e84dc2a3e..ba304607a7 100644 --- a/C4/Input.pm +++ b/C4/Input.pm @@ -119,33 +119,48 @@ Returns NULL if no authorised values found =cut sub buildCGIsort { - my ($name,$input_name,$data) = @_; - my $dbh=C4::Context->dbh; - my $query=qq{SELECT * FROM authorised_values WHERE category=? order by lib}; - my $sth=$dbh->prepare($query); - $sth->execute($name); - my $CGISort; - if ($sth->rows>0){ - my @values; - my %labels; - - for (my $i =0;$i<$sth->rows;$i++){ - my $results = $sth->fetchrow_hashref; - push @values, $results->{authorised_value}; - $labels{$results->{authorised_value}}=$results->{lib}; - } - $CGISort= CGI::scrolling_list( - -name => $input_name, - -id => $input_name, - -values => \@values, - -labels => \%labels, - -default=> $data, - -size => 1, - -multiple => 0); - } - $sth->finish; - return $CGISort; + my ( $name, $input_name, $data ) = @_; + my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : ""; + + my $dbh=C4::Context->dbh; + my $query = qq{ + SELECT * + FROM authorised_values + }; + $query .= qq{ + LEFT JOIN authorised_values_branches ON ( id = av_id ) + } if $branch_limit; + $query .= qq{ + WHERE category = ? + }; + $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit; + $query .= qq{ GROUP BY lib ORDER BY lib}; + + my $sth=$dbh->prepare($query); + $sth->execute( $name, $branch_limit ? $branch_limit : () ); + my $CGISort; + if ($sth->rows>0){ + my @values; + my %labels; + + for (my $i =0;$i<$sth->rows;$i++){ + my $results = $sth->fetchrow_hashref; + push @values, $results->{authorised_value}; + $labels{$results->{authorised_value}}=$results->{lib}; + } + $CGISort= CGI::scrolling_list( + -name => $input_name, + -id => $input_name, + -values => \@values, + -labels => \%labels, + -default=> $data, + -size => 1, + -multiple => 0); + } + $sth->finish; + return $CGISort; } + END { } # module clean-up code here (global destructor) 1; diff --git a/C4/Items.pm b/C4/Items.pm index 253078d4b5..aa5a954ba0 100644 --- a/C4/Items.pm +++ b/C4/Items.pm @@ -2602,7 +2602,20 @@ sub PrepareItemrecordDisplay { $itemrecord = C4::Items::GetMarcItem( $bibnum, $itemnum ); } my @loop_data; - my $authorised_values_sth = $dbh->prepare( "SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib" ); + + my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : ""; + my $query = qq{ + SELECT authorised_value,lib FROM authorised_values + }; + $query .= qq{ + LEFT JOIN authorised_values_branches ON ( id = av_id ) + } if $branch_limit; + $query .= qq{ + WHERE category = ? + }; + $query .= qq{ AND ( branchcode = ? OR branchcode IS NULL )} if $branch_limit; + $query .= qq{ ORDER BY lib}; + my $authorised_values_sth = $dbh->prepare( $query ); foreach my $tag ( sort keys %{$tagslib} ) { my $previous_tag = ''; if ( $tag ne '' ) { @@ -2734,7 +2747,10 @@ sub PrepareItemrecordDisplay { #---- "true" authorised value } else { - $authorised_values_sth->execute( $tagslib->{$tag}->{$subfield}->{authorised_value} ); + $authorised_values_sth->execute( + $tagslib->{$tag}->{$subfield}->{authorised_value}, + $branch_limit ? $branch_limit : () + ); push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} ); while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) { diff --git a/C4/Koha.pm b/C4/Koha.pm index d38729aa4b..38924689fc 100644 --- a/C4/Koha.pm +++ b/C4/Koha.pm @@ -1036,28 +1036,50 @@ C<$opac> If set to a true value, displays OPAC descriptions rather than normal o =cut sub GetAuthorisedValues { - my ($category,$selected,$opac) = @_; + my ( $category, $selected, $opac ) = @_; + my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : ""; my @results; my $dbh = C4::Context->dbh; - my $query = "SELECT * FROM authorised_values"; - $query .= " WHERE category = '" . $category . "'" if $category; - $query .= " ORDER BY category, lib, lib_opac"; + my $query = qq{ + SELECT * + FROM authorised_values + }; + $query .= qq{ + LEFT JOIN authorised_values_branches ON ( id = av_id ) + } if $branch_limit; + my @where_strings; + my @where_args; + if($category) { + push @where_strings, "category = ?"; + push @where_args, $category; + } + if($branch_limit) { + push @where_strings, "( branchcode = ? OR branchcode IS NULL )"; + push @where_args, $branch_limit; + } + if(@where_strings > 0) { + $query .= " WHERE " . join(" AND ", @where_strings); + } + $query .= " GROUP BY lib ORDER BY category, lib, lib_opac"; + my $sth = $dbh->prepare($query); - $sth->execute; + + $sth->execute( @where_args ); while (my $data=$sth->fetchrow_hashref) { - if ( (defined($selected)) && ($selected eq $data->{'authorised_value'}) ) { - $data->{'selected'} = 1; + if ( defined $selected and $selected eq $data->{authorised_value} ) { + $data->{selected} = 1; } else { - $data->{'selected'} = 0; + $data->{selected} = 0; } - if ($opac && $data->{'lib_opac'}) { - $data->{'lib'} = $data->{'lib_opac'}; + + if ($opac && $data->{lib_opac}) { + $data->{lib} = $data->{lib_opac}; } push @results, $data; } - #my $data = $sth->fetchall_arrayref({}); - return \@results; #$data; + $sth->finish; + return \@results; } =head2 GetAuthorisedValueCategories diff --git a/C4/Members.pm b/C4/Members.pm index b2f45b955b..f381a41745 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -1374,20 +1374,35 @@ to category descriptions. #' sub GetborCatFromCatType { - my ( $category_type, $action ) = @_; - # FIXME - This API seems both limited and dangerous. + my ( $category_type, $action, $no_branch_limit ) = @_; + + my $branch_limit = $no_branch_limit + ? 0 + : C4::Context->userenv ? C4::Context->userenv->{"branch"} : ""; + + # FIXME - This API seems both limited and dangerous. my $dbh = C4::Context->dbh; - my $request = qq| SELECT categorycode,description - FROM categories - $action - ORDER BY categorycode|; - my $sth = $dbh->prepare($request); - if ($action) { - $sth->execute($category_type); - } - else { - $sth->execute(); + + my $request = qq{ + SELECT categories.categorycode, categories.description + FROM categories + }; + $request .= qq{ + LEFT JOIN categories_branches ON categories.categorycode = categories_branches.categorycode + } if $branch_limit; + if($action) { + $request .= " $action "; + $request .= " AND (branchcode = ? OR branchcode IS NULL) GROUP BY description" if $branch_limit; + } else { + $request .= " WHERE branchcode = ? OR branchcode IS NULL GROUP BY description" if $branch_limit; } + $request .= " ORDER BY categorycode"; + + my $sth = $dbh->prepare($request); + $sth->execute( + $action ? $category_type : (), + $branch_limit ? $branch_limit : () + ); my %labels; my @codes; @@ -1396,6 +1411,7 @@ sub GetborCatFromCatType { push @codes, $data->{'categorycode'}; $labels{ $data->{'categorycode'} } = $data->{'description'}; } + $sth->finish; return ( \@codes, \%labels ); } @@ -1454,16 +1470,21 @@ If no category code provided, the function returns all the categories. =cut sub GetBorrowercategoryList { + my $no_branch_limit = @_ ? shift : 0; + my $branch_limit = $no_branch_limit + ? 0 + : C4::Context->userenv ? C4::Context->userenv->{"branch"} : ""; my $dbh = C4::Context->dbh; - my $sth = - $dbh->prepare( - "SELECT * - FROM categories - ORDER BY description" - ); - $sth->execute; - my $data = - $sth->fetchall_arrayref({}); + my $query = "SELECT * FROM categories"; + $query .= qq{ + LEFT JOIN categories_branches ON categories.categorycode = categories_branches.categorycode + WHERE branchcode = ? OR branchcode IS NULL GROUP BY description + } if $branch_limit; + $query .= " ORDER BY description"; + my $sth = $dbh->prepare( $query ); + $sth->execute( $branch_limit ? $branch_limit : () ); + my $data = $sth->fetchall_arrayref( {} ); + $sth->finish; return $data; } # sub getborrowercategory diff --git a/C4/Members/AttributeTypes.pm b/C4/Members/AttributeTypes.pm index 3e33694189..ac4153e8a9 100644 --- a/C4/Members/AttributeTypes.pm +++ b/C4/Members/AttributeTypes.pm @@ -69,12 +69,24 @@ If $all_fields is true, then each hashref also contains the other fields from bo =cut sub GetAttributeTypes { - my ($all) = @_; - my $select = $all ? '*' : 'code, description, class'; + my $all = @_ ? shift : 0; + my $no_branch_limit = @_ ? shift : 0; + my $branch_limit = $no_branch_limit + ? 0 + : C4::Context->userenv ? C4::Context->userenv->{"branch"} : 0; + my $select = $all ? '*' : 'DISTINCT(code), description'; + my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("SELECT $select FROM borrower_attribute_types ORDER by code"); - $sth->execute(); + my $query = "SELECT $select FROM borrower_attribute_types"; + $query .= qq{ + LEFT JOIN borrower_attribute_types_branches ON bat_code = code + WHERE b_branchcode = ? OR b_branchcode IS NULL + } if $branch_limit; + $query .= " ORDER BY code"; + my $sth = $dbh->prepare($query); + $sth->execute( $branch_limit ? $branch_limit : () ); my $results = $sth->fetchall_arrayref({}); + $sth->finish; return @$results; } @@ -166,6 +178,13 @@ sub fetch { $self->{'category_description'} = $row->{'category_description'}; $self->{'class'} = $row->{'class'}; + $sth = $dbh->prepare("SELECT branchcode, branchname FROM borrower_attribute_types_branches, branches WHERE b_branchcode = branchcode AND bat_code = ?;"); + $sth->execute( $code ); + while ( my $data = $sth->fetchrow_hashref ) { + push @{ $self->{branches} }, $data; + } + $sth->finish(); + bless $self, $class; return $self; } @@ -219,6 +238,22 @@ sub store { $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; + } + } + $sth->finish; } =head2 code @@ -250,6 +285,20 @@ sub description { @_ ? $self->{'description'} = shift : $self->{'description'}; } +=head2 branches + +my $branches = $attr_type->branches(); +$attr_type->branches($branches); + +Accessor. + +=cut + +sub branches { + my $self = shift; + @_ ? $self->{branches} = shift : $self->{branches}; +} + =head2 repeatable my $repeatable = $attr_type->repeatable(); @@ -324,13 +373,9 @@ sub staff_searchable { =head2 display_checkout -=over 4 - my $display_checkout = $attr_type->display_checkout(); $attr_type->display_checkout($display_checkout); -=back - Accessor. The C<$display_checkout> argument is interpreted as a Perl boolean. @@ -357,13 +402,9 @@ sub authorised_value_category { =head2 category_code -=over 4 - my $category_code = $attr_type->category_code(); $attr_type->category_code($category_code); -=back - Accessor. =cut @@ -375,13 +416,9 @@ sub category_code { =head2 category_description -=over 4 - my $category_description = $attr_type->category_description(); $attr_type->category_description($category_description); -=back - Accessor. =cut @@ -393,13 +430,9 @@ sub category_description { =head2 class -=over 4 - my $class = $attr_type->class(); $attr_type->class($class); -=back - Accessor. =cut @@ -432,6 +465,7 @@ sub delete { my $dbh = C4::Context->dbh; my $sth = $dbh->prepare_cached("DELETE FROM borrower_attribute_types WHERE code = ?"); $sth->execute($code); + $sth->finish; } =head2 num_patrons diff --git a/C4/Members/Attributes.pm b/C4/Members/Attributes.pm index e175915149..f0ec07fd06 100644 --- a/C4/Members/Attributes.pm +++ b/C4/Members/Attributes.pm @@ -71,6 +71,7 @@ marked for OPAC display are returned. sub GetBorrowerAttributes { my $borrowernumber = shift; my $opac_only = @_ ? shift : 0; + my $branch_limit = @_ ? shift : 0; my $dbh = C4::Context->dbh(); my $query = "SELECT code, description, attribute, lib, password, display_checkout, category_code, class @@ -95,6 +96,7 @@ sub GetBorrowerAttributes { class => $row->{'class'}, } } + $sth->finish; return \@results; } diff --git a/admin/authorised_values.pl b/admin/authorised_values.pl index 17917ce260..9e901821b7 100755 --- a/admin/authorised_values.pl +++ b/admin/authorised_values.pl @@ -22,6 +22,7 @@ use warnings; use CGI; use C4::Auth; +use C4::Branch; use C4::Context; use C4::Koha; use C4::Output; @@ -67,13 +68,32 @@ $template->param( script_name => $script_name, # called by default. Used to create form to add or modify a record if ($op eq 'add_form') { my $data; + my @selected_branches; if ($id) { my $sth=$dbh->prepare("select id, category, authorised_value, lib, lib_opac, imageurl from authorised_values where id=?"); $sth->execute($id); $data=$sth->fetchrow_hashref; + $sth = $dbh->prepare("SELECT b.branchcode, b.branchname FROM authorised_values_branches AS avb, branches AS b WHERE avb.branchcode = b.branchcode AND avb.av_id = ?;"); + $sth->execute( $id ); + while ( my $branch = $sth->fetchrow_hashref ) { + push @selected_branches, $branch; + } } else { $data->{'category'} = $input->param('category'); } + + my $branches = GetBranches; + my @branches_loop; + + foreach my $branch (sort keys %$branches) { + my $selected = ( grep {$_->{branchcode} eq $branch} @selected_branches ) ? 1 : 0; + push @branches_loop, { + branchcode => $branches->{$branch}{branchcode}, + branchname => $branches->{$branch}{branchname}, + selected => $selected, + }; + } + if ($id) { $template->param(action_modify => 1); $template->param('heading_modify_authorized_value_p' => 1); @@ -92,6 +112,7 @@ if ($op eq 'add_form') { id => $data->{'id'}, imagesets => C4::Koha::getImageSets( checked => $data->{'imageurl'} ), offset => $offset, + branches_loop => \@branches_loop, ); ################## ADD_VALIDATE ################################## @@ -102,6 +123,7 @@ if ($op eq 'add_form') { my $imageurl = $input->param( 'imageurl' ) || ''; $imageurl = '' if $imageurl =~ /removeImage/; my $duplicate_entry = 0; + my @branches = $input->param('branches'); if ( $id ) { # Update my $sth = $dbh->prepare( "SELECT category, authorised_value FROM authorised_values WHERE id = ? "); @@ -125,7 +147,21 @@ if ($op eq 'add_form') { my $lib_opac = $input->param('lib_opac'); undef $lib if ($lib eq ""); # to insert NULL instead of a blank string undef $lib_opac if ($lib_opac eq ""); # to insert NULL instead of a blank string - $sth->execute($new_category, $new_authorised_value, $lib, $lib_opac, $imageurl, $id); + $sth->execute($new_category, $new_authorised_value, $lib, $lib_opac, $imageurl, $id); + if ( @branches ) { + $sth = $dbh->prepare("DELETE FROM authorised_values_branches WHERE av_id = ?"); + $sth->execute( $id ); + $sth = $dbh->prepare( + "INSERT INTO authorised_values_branches + ( av_id, branchcode ) + VALUES ( ?, ? )" + ); + for my $branchcode ( @branches ) { + next if not $branchcode; + $sth->execute($id, $branchcode); + } + } + $sth->finish; print "Content-Type: text/html\n\n"; exit; } @@ -137,13 +173,25 @@ if ($op eq 'add_form') { ($duplicate_entry) = $sth->fetchrow_array(); unless ( $duplicate_entry ) { my $sth=$dbh->prepare( 'INSERT INTO authorised_values - ( id, category, authorised_value, lib, lib_opac, imageurl ) - values (?, ?, ?, ?, ?, ?)' ); + ( category, authorised_value, lib, lib_opac, imageurl ) + values (?, ?, ?, ?, ?)' ); my $lib = $input->param('lib'); my $lib_opac = $input->param('lib_opac'); undef $lib if ($lib eq ""); # to insert NULL instead of a blank string undef $lib_opac if ($lib_opac eq ""); # to insert NULL instead of a blank string - $sth->execute($id, $new_category, $new_authorised_value, $lib, $lib_opac, $imageurl ); + $sth->execute( $new_category, $new_authorised_value, $lib, $lib_opac, $imageurl ); + $id = $dbh->{'mysql_insertid'}; + if ( @branches ) { + $sth = $dbh->prepare( + "INSERT INTO authorised_values_branches + ( av_id, branchcode ) + VALUES ( ?, ? )" + ); + for my $branchcode ( @branches ) { + next if not $branchcode; + $sth->execute($id, $branchcode); + } + } print "Content-Type: text/html\n\nparam('category')."&offset=$offset\">"; exit; } @@ -176,6 +224,8 @@ if ($op eq 'add_form') { my $id = $input->param('id'); my $sth=$dbh->prepare("delete from authorised_values where id=?"); $sth->execute($id); + $sth = $dbh->prepare("DELETE FROM authorised_values_branches WHERE id = ?"); + $sth->execute($id); print "Content-Type: text/html\n\n"; exit; # END $OP eq DELETE_CONFIRMED @@ -219,7 +269,14 @@ sub default_form { my $count = scalar(@$results); my @loop_data = (); # builds value list + my $dbh = C4::Context->dbh; + $sth = $dbh->prepare("SELECT b.branchcode, b.branchname FROM authorised_values_branches AS avb, branches AS b WHERE avb.branchcode = b.branchcode AND avb.av_id = ?"); for (my $i=0; $i < $count; $i++){ + $sth->execute( $results->[$i]{id} ); + my @selected_branches; + while ( my $branch = $sth->fetchrow_hashref ) { + push @selected_branches, $branch; + } my %row_data; # get a fresh hash for the row data $row_data{category} = $results->[$i]{'category'}; $row_data{authorised_value} = $results->[$i]{'authorised_value'}; @@ -228,6 +285,7 @@ sub default_form { $row_data{imageurl} = getitemtypeimagelocation( 'intranet', $results->[$i]{'imageurl'} ); $row_data{edit} = "$script_name?op=add_form&id=".$results->[$i]{'id'}."&offset=$offset"; $row_data{delete} = "$script_name?op=delete_confirm&searchfield=$searchfield&id=".$results->[$i]{'id'}."&offset=$offset"; + $row_data{branches} = \@selected_branches; push(@loop_data, \%row_data); } diff --git a/admin/categorie.pl b/admin/categorie.pl index 63d629c366..496415839b 100755 --- a/admin/categorie.pl +++ b/admin/categorie.pl @@ -36,11 +36,12 @@ # with Koha; if not, write to the Free Software Foundation, Inc., # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. -use strict; -#use warnings; FIXME - Bug 2505 +use Modern::Perl; + use CGI; use C4::Context; use C4::Auth; +use C4::Branch; use C4::Output; use C4::Dates; use C4::Form::MessagingPreferences; @@ -90,16 +91,34 @@ if ($op eq 'add_form') { #---- if primkey exists, it's a modify action, so read values to modify... my $data; + my @selected_branches; if ($categorycode) { my $dbh = C4::Context->dbh; my $sth=$dbh->prepare("select categorycode,description,enrolmentperiod,enrolmentperioddate,upperagelimit,dateofbirthrequired,enrolmentfee,issuelimit,reservefee,hidelostitems,overduenoticerequired,category_type from categories where categorycode=?"); $sth->execute($categorycode); $data=$sth->fetchrow_hashref; - $sth->finish; - } + + $sth = $dbh->prepare("SELECT b.branchcode, b.branchname FROM categories_branches AS cb, branches AS b WHERE cb.branchcode = b.branchcode AND cb.categorycode = ?"); + $sth->execute( $categorycode ); + while ( my $branch = $sth->fetchrow_hashref ) { + push @selected_branches, $branch; + } + $sth->finish; + } $data->{'enrolmentperioddate'} = undef if ($data->{'enrolmentperioddate'} eq '0000-00-00'); + my $branches = GetBranches; + my @branches_loop; + foreach my $branch (sort keys %$branches) { + my $selected = ( grep {$$_{branchcode} eq $branch} @selected_branches ) ? 1 : 0; + push @branches_loop, { + branchcode => $$branches{$branch}{branchcode}, + branchname => $$branches{$branch}{branchname}, + selected => $selected, + }; + } + $template->param(description => $data->{'description'}, enrolmentperiod => $data->{'enrolmentperiod'}, enrolmentperioddate => C4::Dates::format_date($data->{'enrolmentperioddate'}), @@ -115,6 +134,7 @@ if ($op eq 'add_form') { SMSSendDriver => C4::Context->preference("SMSSendDriver"), TalkingTechItivaPhone => C4::Context->preference("TalkingTechItivaPhoneNotification"), "type_".$data->{'category_type'} => 1, + branches_loop => \@branches_loop, ); if (C4::Context->preference('EnhancedMessagingPreferences')) { C4::Form::MessagingPreferences::set_form_values({ categorycode => $categorycode } , $template); @@ -133,6 +153,22 @@ if ($op eq 'add_form') { if ($is_a_modif) { my $sth=$dbh->prepare("UPDATE categories SET description=?,enrolmentperiod=?, enrolmentperioddate=?,upperagelimit=?,dateofbirthrequired=?,enrolmentfee=?,reservefee=?,hidelostitems=?,overduenoticerequired=?,category_type=? WHERE categorycode=?"); $sth->execute(map { $input->param($_) } ('description','enrolmentperiod','enrolmentperioddate','upperagelimit','dateofbirthrequired','enrolmentfee','reservefee','hidelostitems','overduenoticerequired','category_type','categorycode')); + my @branches = $input->param("branches"); + if ( @branches ) { + $sth = $dbh->prepare("DELETE FROM categories_branches WHERE categorycode = ?"); + $sth->execute( $input->param( "categorycode" ) ); + $sth = $dbh->prepare( + "INSERT INTO categories_branches + ( categorycode, branchcode ) + VALUES ( ?, ? )" + ); + for my $branchcode ( @branches ) { + next if not $branchcode; + $sth->bind_param( 1, $input->param( "categorycode" ) ); + $sth->bind_param( 2, $branchcode ); + $sth->execute; + } + } $sth->finish; } else { my $sth=$dbh->prepare("INSERT INTO categories (categorycode,description,enrolmentperiod,enrolmentperioddate,upperagelimit,dateofbirthrequired,enrolmentfee,reservefee,hidelostitems,overduenoticerequired,category_type) values (?,?,?,?,?,?,?,?,?,?,?)"); @@ -197,7 +233,14 @@ if ($op eq 'add_form') { $template->param(else => 1); my @loop; my ($count,$results)=StringSearch($searchfield,'web'); + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare("SELECT b.branchcode, b.branchname FROM categories_branches AS cb, branches AS b WHERE cb.branchcode = b.branchcode AND cb.categorycode = ?"); for (my $i=0; $i < $count; $i++){ + $sth->execute( $results->[$i]{'categorycode'} ); + my @selected_branches; + while ( my $branch = $sth->fetchrow_hashref ) { + push @selected_branches, $branch; + } my %row = ( categorycode => $results->[$i]{'categorycode'}, description => $results->[$i]{'description'}, @@ -211,7 +254,9 @@ if ($op eq 'add_form') { reservefee => sprintf("%.2f",$results->[$i]{'reservefee'}), hidelostitems => $results->[$i]{'hidelostitems'}, category_type => $results->[$i]{'category_type'}, - "type_".$results->[$i]{'category_type'} => 1); + "type_".$results->[$i]{'category_type'} => 1, + branches => \@selected_branches, + ); if (C4::Context->preference('EnhancedMessagingPreferences')) { my $brief_prefs = _get_brief_messaging_prefs($results->[$i]{'categorycode'}); $row{messaging_prefs} = $brief_prefs if @$brief_prefs; @@ -220,8 +265,7 @@ if ($op eq 'add_form') { } $template->param(loop => \@loop); # check that I (institution) and C (child) exists. otherwise => warning to the user - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("select category_type from categories where category_type='C'"); + $sth=$dbh->prepare("select category_type from categories where category_type='C'"); $sth->execute; my ($categoryChild) = $sth->fetchrow; $template->param(categoryChild => $categoryChild); diff --git a/admin/patron-attr-types.pl b/admin/patron-attr-types.pl index c0ad1f70a4..3935f40f37 100755 --- a/admin/patron-attr-types.pl +++ b/admin/patron-attr-types.pl @@ -19,12 +19,13 @@ # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. # -use strict; -use warnings; +use Modern::Perl; + use CGI; use List::MoreUtils qw/uniq/; use C4::Auth; +use C4::Branch; use C4::Context; use C4::Output; use C4::Koha; @@ -82,10 +83,20 @@ exit 0; sub add_attribute_type_form { my $template = shift; + my $branches = GetBranches; + my @branches_loop; + foreach my $branch (sort keys %$branches) { + push @branches_loop, { + branchcode => $$branches{$branch}{branchcode}, + branchname => $$branches{$branch}{branchname}, + }; + } + $template->param( attribute_type_form => 1, confirm_op => 'add_attribute_type_confirmed', categories => GetBorrowercategoryList, + branches_loop => \@branches_loop, ); authorised_value_category_list($template); pa_classes($template); @@ -162,6 +173,8 @@ 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')); + my @branches = $input->param('branches'); + $attr_type->branches( \@branches ); if ($op eq 'edit') { $template->param(edited_attribute_type => $attr_type->code()); @@ -244,6 +257,20 @@ sub edit_attribute_type_form { authorised_value_category_list($template, $attr_type->authorised_value_category()); pa_classes( $template, $attr_type->class ); + + my $branches = GetBranches; + my @branches_loop; + my $selected_branches = $attr_type->branches; + foreach my $branch (sort keys %$branches) { + my $selected = ( grep {$$_{branchcode} eq $branch} @$selected_branches ) ? 1 : 0; + push @branches_loop, { + branchcode => $branches->{$branch}{branchcode}, + branchname => $branches->{$branch}{branchname}, + selected => $selected, + }; + } + $template->param( branches_loop => \@branches_loop ); + $template->param ( category_code => $attr_type->category_code ); $template->param ( category_description => $attr_type->category_description ); @@ -259,21 +286,26 @@ sub edit_attribute_type_form { sub patron_attribute_type_list { my $template = shift; - my @attr_types = C4::Members::AttributeTypes::GetAttributeTypes(); - my @classes = uniq( map {$_->{class}} @attr_types ); + my @attr_types = C4::Members::AttributeTypes::GetAttributeTypes( 1, 1 ); + + my @classes = uniq( map { $_->{class} } @attr_types ); @classes = sort @classes; my @attributes_loop; for my $class (@classes) { - my @items; + my ( @items, $branches ); for my $attr (@attr_types) { - push @items, $attr if $attr->{class} eq $class + next if $attr->{class} ne $class; + my $attr_type = C4::Members::AttributeTypes->fetch($attr->{code}); + $attr->{branches} = $attr_type->branches; + push @items, $attr; } my $lib = GetAuthorisedValueByCode( 'PA_CLASS', $class ) || $class; push @attributes_loop, { class => $class, items => \@items, lib => $lib, + branches => $branches, }; } $template->param(available_attribute_types => \@attributes_loop); diff --git a/cataloguing/addbiblio.pl b/cataloguing/addbiblio.pl index 2d591c6ba5..caacd9115a 100755 --- a/cataloguing/addbiblio.pl +++ b/cataloguing/addbiblio.pl @@ -219,8 +219,11 @@ sub build_authorized_values_list { $value = $default_source unless $value; } else { + my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : ""; $authorised_values_sth->execute( - $tagslib->{$tag}->{$subfield}->{authorised_value} ); + $tagslib->{$tag}->{$subfield}->{authorised_value}, + $branch_limit ? $branch_limit : (), + ); push @authorised_values, "" unless ( $tagslib->{$tag}->{$subfield}->{mandatory} ); @@ -230,6 +233,7 @@ sub build_authorized_values_list { $authorised_lib{$value} = $lib; } } + $authorised_values_sth->finish; return CGI::scrolling_list( -name => "tag_".$tag."_subfield_".$subfield."_".$index_tag."_".$index_subfield, -values => \@authorised_values, @@ -508,12 +512,15 @@ sub build_tabs { my @loop_data = (); my $tag; - my $authorised_values_sth = $dbh->prepare( - "select authorised_value,lib - from authorised_values - where category=? order by lib" - ); - + my $branch_limit = C4::Context->userenv ? C4::Context->userenv->{"branch"} : ""; + my $query = "SELECT authorised_value, lib + FROM authorised_values"; + $query .= qq{ LEFT JOIN authorised_values_branches ON ( id = av_id )} if $branch_limit; + $query .= " WHERE category = ?"; + $query .= " AND ( branchcode = ? OR branchcode IS NULL )" if $branch_limit; + $query .= " GROUP BY lib ORDER BY lib, lib_opac"; + my $authorised_values_sth = $dbh->prepare( $query ); + # in this array, we will push all the 10 tabs # to avoid having 10 tabs in the template : they will all be in the same BIG_LOOP my @BIG_LOOP; @@ -698,6 +705,7 @@ sub build_tabs { }; } } + $authorised_values_sth->finish; $template->param( BIG_LOOP => \@BIG_LOOP ); } diff --git a/cataloguing/additem.pl b/cataloguing/additem.pl index 79a717c54f..c9875ca1bd 100755 --- a/cataloguing/additem.pl +++ b/cataloguing/additem.pl @@ -107,8 +107,7 @@ sub generate_subfield_form { my $frameworkcode = &GetFrameworkCode($biblionumber); my %subfield_data; - my $dbh = C4::Context->dbh; - my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib"); + my $dbh = C4::Context->dbh; my $index_subfield = int(rand(1000000)); if ($subfieldtag eq '@'){ @@ -203,11 +202,11 @@ sub generate_subfield_form { #---- "true" authorised value } else { - push @authorised_values, "" unless ( $subfieldlib->{mandatory} ); - $authorised_values_sth->execute( $subfieldlib->{authorised_value} ); - while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) { - push @authorised_values, $value; - $authorised_lib{$value} = $lib; + push @authorised_values, qq{} unless ( $subfieldlib->{mandatory} ); + my $av = GetAuthorisedValues( $subfieldlib->{authorised_value} ); + for my $r ( @$av ) { + push @authorised_values, $r->{authorised_value}; + $authorised_lib{$r->{authorised_value}} = $r->{lib}; } } diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index a814eabf0e..f68c9c7814 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2940,6 +2940,44 @@ CREATE TABLE `quotes` ( PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; +-- +-- Table structure for table categories_branches +-- + +DROP TABLE IF EXISTS categories_branches; +CREATE TABLE categories_branches( -- association table between categories and branches + categorycode VARCHAR(10), + branchcode VARCHAR(10), + FOREIGN KEY (categorycode) REFERENCES categories(categorycode) ON DELETE CASCADE, + FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE +) ENGINE=INNODB DEFAULT CHARSET=utf8; + +-- +-- Table structure for table authorised_values_branches +-- + +DROP TABLE IF EXISTS authorised_values_branches; +CREATE TABLE authorised_values_branches( -- association table between authorised_values and branches + av_id INTEGER, + branchcode VARCHAR(10), + FOREIGN KEY (av_id) REFERENCES authorised_values(id) ON DELETE CASCADE, + FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE +) ENGINE=INNODB DEFAULT CHARSET=utf8; + + +-- +-- Table structure for table borrower_attribute_types_branches +-- + +DROP TABLE IF EXISTS borrower_attribute_types_branches; +CREATE TABLE borrower_attribute_types_branches( -- association table between borrower_attribute_types and branches + bat_code VARCHAR(10), + b_branchcode VARCHAR(10), + FOREIGN KEY (bat_code) REFERENCES borrower_attribute_types(code) ON DELETE CASCADE, + FOREIGN KEY (b_branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE +) ENGINE=INNODB DEFAULT CHARSET=utf8; + + /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index a6af263e73..e93a4470c1 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -6080,6 +6080,17 @@ $DBversion = "3.11.00.001"; if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { $dbh->do("INSERT INTO systempreferences (variable,value,explanation,options,type) VALUES('alphabet','A B C D E F G H I J K L M N O P Q R S T U V W X Y Z','Alphabet that can be expanded into browse links, e.g. on Home > Patrons',NULL,'free')"); print "Upgrade to $DBversion done (Bug 2832 - Add alphabet syspref)\n"; +} + +$DBversion = "3.11.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do(qq{CREATE TABLE borrower_attribute_types_branches(bat_code VARCHAR(10), b_branchcode VARCHAR(10),FOREIGN KEY (bat_code) REFERENCES borrower_attribute_types(code) ON DELETE CASCADE,FOREIGN KEY (b_branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE ) ENGINE=INNODB DEFAULT CHARSET=utf8;}); + + $dbh->do(qq{CREATE TABLE categories_branches(categorycode VARCHAR(10), branchcode VARCHAR(10), FOREIGN KEY (categorycode) REFERENCES categories(categorycode) ON DELETE CASCADE, FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE ) ENGINE=INNODB DEFAULT CHARSET=utf8;}); + + $dbh->do(qq{CREATE TABLE authorised_values_branches(av_id INTEGER, branchcode VARCHAR(10), FOREIGN KEY (av_id) REFERENCES authorised_values(id) ON DELETE CASCADE, FOREIGN KEY (branchcode) REFERENCES branches(branchcode) ON DELETE CASCADE ) ENGINE=INNODB DEFAULT CHARSET=utf8;}); + + print "Upgrade to $DBversion done (Bug 7919: Display of values depending on the connexion library)\n"; SetVersion($DBversion); } diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/authorised_values.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/authorised_values.tt index 8657ec8758..8d604933e6 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/authorised_values.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/authorised_values.tt @@ -13,7 +13,10 @@ sortList: [[1,0]], headers: { 4: { sorter: false}, 5: { sorter: false}} }).tablesorterPager({container: $("#pagertable_authorized_values"),positionFixed: false,size: 50}); - + + if ( $("#branches option:selected").length < 1 ) { + $("#branches option:first").attr("selected", "selected"); + } });