From d8e1ba44b180d09aa70bc90c8a0e618d5ca6ef2f Mon Sep 17 00:00:00 2001 From: Alex Buckley Date: Sun, 19 Feb 2017 03:07:03 +0000 Subject: [PATCH] Bug 18212 - Moved all sql queries out aqplan.pl into Koha::Libraries.pm, Koha::ItemType.pm, Koha::AuthorisedValue.pm files MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit The new subroutines in the perl modules contain POD Tested both patches together, works as expected Amended with blank line preceding a =cut to make qa tools pass for first patch Signed-off-by: Marc VĂ©ron --- C4/Acquisition.pm | 18 ++++++++++++++ Koha/AuthorisedValue.pm | 29 ++++++++++++++++++++++ Koha/ItemType.pm | 18 ++++++++++++++ Koha/Libraries.pm | 15 +++++++++++ admin/aqplan.pl | 52 +++++++++++++++++---------------------- t/db_dependent/AuthorisedValues.t | 36 +++++++++++++++++++++++++-- 6 files changed, 136 insertions(+), 32 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 2bb97a9..fa30735 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -2978,6 +2978,24 @@ sub GetOrderUsers { return @borrowernumbers; } +=head3 GetAuthorisedValues + + + +=cut + +sub GetAuthValuesForCategory{ + my ($self, $category) = @_; + my $schema = Koha::Database->new()->schema(); + my $authvalues = Koha::AuthorisedValues->search({ + 'category' => $category, + order_by => { -asc => 'lib'} + }); + return $authvalues; +} + + + =head3 ModOrderUsers my @order_users_ids = (1, 2, 3); diff --git a/Koha/AuthorisedValue.pm b/Koha/AuthorisedValue.pm index 898b630..4e5968a 100644 --- a/Koha/AuthorisedValue.pm +++ b/Koha/AuthorisedValue.pm @@ -158,6 +158,35 @@ sub _avb_resultset { $self->{_avb_resultset}; } +=head3 GetAuthValues +my $sth = Koha::AuthorisedValue->GetAuthValues($authcat,$dbh); + +This subroutine retrieves and returns all fields from the authorised_values table where the category is equal to the parameter $authcat, and orders therecords by the value of the lib field + +=cut + +sub GetAuthValues { + my ($self, $authcat, $dbh) = @_; + my $query = qq{ SELECT * FROM authorised_values WHERE category=? order by lib }; + my $sth = $dbh->prepare($query); + $sth->execute($authcat); + return $sth; +} + +=head3 GetDistinctCat +my $sth = Koha::AuthorisedValue->GetDistinctCat($dbh); + +This subroutine retrieves and returns all the different category values starting with 'A' in the authorised_values table + +=cut + +sub GetDistinctCat { + my ($self, $dbh) = @_; + my $sth = $dbh->prepare("select distinct category from authorised_values where category like 'A%' "); + $sth->execute; + return $sth; +} + =head3 type =cut diff --git a/Koha/ItemType.pm b/Koha/ItemType.pm index 8f2279b..fb0f9ad 100644 --- a/Koha/ItemType.pm +++ b/Koha/ItemType.pm @@ -89,10 +89,28 @@ sub translated_descriptions { } @translated_descriptions ]; } +=head3 GetItemTypes +my $sth = Koha::ItemType->GetItemTypes($dbh); + +This subroutine retrieves and returns all the values for the fields: itemtype and description from the itemtypes table. + +=cut + +sub GetItemTypes{ + my ($self, $dbh) = @_; + my $query = qq| SELECT itemtype, description FROM itemtypes |; + my $sth = $dbh->prepare($query); + $sth->execute( ); + return $sth; +} + + + =head3 type =cut + sub _type { return 'Itemtype'; } diff --git a/Koha/Libraries.pm b/Koha/Libraries.pm index fa2bf17..06933a8 100644 --- a/Koha/Libraries.pm +++ b/Koha/Libraries.pm @@ -52,6 +52,21 @@ sub search_filtered { return $self->SUPER::search( $params, $attributes ); } +=head3 +my $sth = Koha::Libraries->GetBranches($dbh); + +This subroutine retrieves and returns all the values for the fields: branchcode and branchname from the branches table. + +=cut + +sub GetBranches { + my ($self, $dbh) = @_; + my $query = qq| SELECT branchcode, branchname FROM branches |; + my $sth = $dbh->prepare($query); + $sth->execute(); + return $sth; +} + =head3 type =cut diff --git a/admin/aqplan.pl b/admin/aqplan.pl index a27fb0a..38bfe84 100755 --- a/admin/aqplan.pl +++ b/admin/aqplan.pl @@ -110,8 +110,7 @@ my $op = $input->param("op"); my $budgets_ref = GetBudgetHierarchy( $budget_period_id, $show_mine?$template->{VARS}->{'USER_INFO'}->{'branchcode'}:'', $show_mine?$template->{VARS}->{'USER_INFO'}->{'borrowernumber'}:'' ); # build categories list -my $sth = $dbh->prepare("select distinct category from authorised_values where category like 'A%' "); -$sth->execute; +my $sth = Koha::AuthorisedValue->GetDistinctCat($dbh); # the list my @category_list; @@ -192,14 +191,13 @@ HideCols($authcat, @hide_cols); } # ------------------------------------------------------------ if ( $authcat =~ m/^Asort/ ) { - my $query = qq{ SELECT * FROM authorised_values WHERE category=? order by lib }; - my $sth = $dbh->prepare($query); - $sth->execute($authcat ); + my $sth = Koha::AuthorisedValue->GetAuthValues($authcat,$dbh); + if ( $sth->rows > 0 ) { for ( my $i = 0 ; $i < $sth->rows ; $i++ ) { - my $results = $sth->fetchrow_hashref; - push @authvals, $results->{authorised_value}; - $labels{ $results->{authorised_value} } = $results->{lib}; + my $results = $sth->fetchrow_hashref; + push @authvals, $results->{authorised_value}; + $labels{ $results->{authorised_value} } = $results->{lib}; } } $sth->finish; @@ -229,37 +227,31 @@ elsif ( $authcat eq 'MONTHS' && $budget_period_startdate && $budget_period_endda } elsif ( $authcat eq 'ITEMTYPES' ) { - my $query = qq| SELECT itemtype, description FROM itemtypes |; - my $sth = $dbh->prepare($query); - $sth->execute( ); - if ( $sth->rows > 0 ) { - for ( my $i = 0 ; $i < $sth->rows ; $i++ ) { + my $sth = Koha::ItemType->GetItemTypes($dbh); + if ( $sth->rows > 0 ) { + for ( my $i = 0 ; $i < $sth->rows ; $i++ ) { my $results = $sth->fetchrow_hashref; push @authvals, $results->{itemtype}; - $labels{ $results->{itemtype} } = $results->{description}; - } - } - $sth->finish; + $labels{ $results->{itemtype} } = $results->{description}; + } + } + $sth->finish; } elsif ( $authcat eq 'BRANCHES' ) { - my $query = qq| SELECT branchcode, branchname FROM branches |; - my $sth = $dbh->prepare($query); - $sth->execute(); + my $sth = Koha::Libraries->GetBranches($dbh); if ( $sth->rows > 0 ) { - for ( my $i = 0 ; $i < $sth->rows ; $i++ ) { - my $results = $sth->fetchrow_hashref; - push @authvals, $results->{branchcode}; - $labels{ $results->{branchcode} } = $results->{branchname}; - } - } - $sth->finish; + for ( my $i = 0 ; $i < $sth->rows ; $i++ ) { + my $results = $sth->fetchrow_hashref; + push @authvals, $results->{branchcode}; + $labels{ $results->{branchcode} } = $results->{branchname}; + } + } } elsif ($authcat) { - my $query = qq{ SELECT * FROM authorised_values WHERE category=? order by lib }; - my $sth = $dbh->prepare($query); - $sth->execute($authcat); + + my $sth = Koha::AuthorisedValue->GetAuthValues($authcat,$dbh); if ( $sth->rows > 0 ) { for ( my $i = 0 ; $i < $sth->rows ; $i++ ) { my $results = $sth->fetchrow_hashref; diff --git a/t/db_dependent/AuthorisedValues.t b/t/db_dependent/AuthorisedValues.t index ad5ec60..e3c99e0 100644 --- a/t/db_dependent/AuthorisedValues.t +++ b/t/db_dependent/AuthorisedValues.t @@ -1,7 +1,7 @@ #!/usr/bin/perl use Modern::Perl; -use Test::More tests => 15; +use Test::More tests => 17; use t::lib::TestBuilder; @@ -226,4 +226,36 @@ subtest 'search_by_*_field + find_by_koha_field + get_description' => sub { }; }; -$schema->storage->txn_rollback; + sub GetAuthValues { + require Test::More; + my $dbh = C4::Context->dbh; + my ($self, $authcat) = @_; + my $query = qq{ SELECT * FROM authorised_values WHERE category=? order by lib }; + my $sth = $dbh->prepare($query); + $sth->execute($authcat); + my $AuthValue = 0; + if ($sth->rows > 0) { + $AuthValue = 1; + } + return $AuthValue; + }; + my $AuthValues = GetAuthValues('av_for_testing'); + is ( $AuthValues,0, 'Does not exist in the database: Test successful'); + + sub GetDistinctCat { + require Test::More; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare("select distinct category from authorised_values where category like 'A%' "); + $sth->execute; + my $DistAuth = 0; + warn $sth->rows; + if ($sth->rows == 3){ + $DistAuth = 1; + } + warn return $DistAuth; + }; + my $DistAuth = GetDistinctCat(); + is ( $DistAuth, 1, '3 Distict categories with a name starting with a exist in the database'); + + + $schema->storage->txn_rollback; -- 2.1.4