From 2900b936ed4dbed86bd452000671e16a34127af7 Mon Sep 17 00:00:00 2001 From: Alex Buckley Date: Mon, 24 Apr 2017 15:56:02 +1200 Subject: [PATCH] Bug 18212 - Four successfully transferred sql queries to Koha::AuthorisedValue, Koha::ItemType, Koha::Libraries from aqplan.pl administrative script --- Koha/AuthorisedValue.pm | 37 +++++++++++++----------- Koha/ItemType.pm | 4 +-- admin/aqplan.pl | 37 +++++++++++------------- t/db_dependent/AuthorisedValues.t | 60 +++++++++++++++++---------------------- t/db_dependent/Koha/ItemTypes.t | 15 +++++++++- t/db_dependent/Koha/Libraries.t | 13 ++++++++- 6 files changed, 91 insertions(+), 75 deletions(-) diff --git a/Koha/AuthorisedValue.pm b/Koha/AuthorisedValue.pm index 4e5968a..c617e67 100644 --- a/Koha/AuthorisedValue.pm +++ b/Koha/AuthorisedValue.pm @@ -159,33 +159,36 @@ sub _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 +my @AuthValues = Koha::AuthorisedValue->GetAuthValues($authcat); +s subroutine retrieves and returns all fields from the authorised_values table where the category is equal to the parameter $authcat, and orders the records 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; -} + my ($self, $authcat) = @_; + my @Getauthorised_values = + Koha::AuthorisedValues->new()->search({ + category => $authcat, + }, + { + order_by => [qw/ lib /] + }); + return @Getauthorised_values; +}; =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 +my @DistinctAuthValues = Koha::AuthorisedValue->GetDistinctCat(); +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; -} + my @distinctauthorised_values = + Koha::AuthorisedValues->new()->search({ + category => { 'like','%a%'}, + }); + return @distinctauthorised_values; +}; =head3 type diff --git a/Koha/ItemType.pm b/Koha/ItemType.pm index e19ef3a..78165b8 100644 --- a/Koha/ItemType.pm +++ b/Koha/ItemType.pm @@ -92,14 +92,14 @@ sub 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. +This subroutine retrieves and returns all values for the fields: itemtype and description in the itemtypes table. =cut sub GetItemTypes{ my ($self, $dbh) = @_; my $query = qq| SELECT itemtype, description FROM itemtypes |; my $sth = $dbh->prepare($query); - $sth->execute( ); + $sth->execute(); return $sth; } diff --git a/admin/aqplan.pl b/admin/aqplan.pl index 38bfe84..15a9a0c 100755 --- a/admin/aqplan.pl +++ b/admin/aqplan.pl @@ -110,14 +110,14 @@ 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 = Koha::AuthorisedValue->GetDistinctCat($dbh); +my @DistinctAuthValues = Koha::AuthorisedValue->GetDistinctCat(); # the list my @category_list; # a hash, to check that some hardcoded categories exist. my %categories; -while ( my ($category) = $sth->fetchrow_array ) { +while ( my ($category) = each @DistinctAuthValues ) { push( @category_list, $category ); $categories{$category} = 1; } @@ -191,16 +191,14 @@ HideCols($authcat, @hide_cols); } # ------------------------------------------------------------ if ( $authcat =~ m/^Asort/ ) { - 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 @AuthValues = Koha::AuthorisedValue->GetAuthValues($authcat); + if ( @AuthValues > 0 ) { + while ( my ($AuthValue) = each @AuthValues) { + my $results = $AuthValue; + push @authvals, $results->{authorised_value}; + $labels{ $results->{authorised_value} } = $results->{lib}; } } - $sth->finish; @authvals = sort { $a <=> $b } @authvals; } elsif ( $authcat eq 'MONTHS' && $budget_period_startdate && $budget_period_enddate ) { @@ -227,16 +225,15 @@ elsif ( $authcat eq 'MONTHS' && $budget_period_startdate && $budget_period_endda } elsif ( $authcat eq 'ITEMTYPES' ) { - - 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; + 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}; + } + } } elsif ( $authcat eq 'BRANCHES' ) { diff --git a/t/db_dependent/AuthorisedValues.t b/t/db_dependent/AuthorisedValues.t index e3c99e0..9759e3d 100644 --- a/t/db_dependent/AuthorisedValues.t +++ b/t/db_dependent/AuthorisedValues.t @@ -92,6 +92,31 @@ my @authorised_values = Koha::AuthorisedValues->new()->search( { category => 'av_for_testing' } ); is( @authorised_values, 3, "Get correct number of values" ); +sub GetAuthValues { + require Test::More; + my @Getauthorised_values = Koha::AuthorisedValues->new()->search({ + category => 'av_for_testing', + }, + { + order_by => [qw/ lib /] + }); +}; + +my @Getauthorised_values = GetAuthValues(); +is( @Getauthorised_values, 3, "Get correct number of values" ); + +sub GetDistinctCat { + require Test::More; + my @distinctauthorised_values = + Koha::AuthorisedValues->new()->search({ + category => { 'like','%a%'}, + }); +} + +my @distinctauthorised_values = GetDistinctCat(); +is( @distinctauthorised_values, 4, "Correct number of distinct values" ); + + my $branchcode1 = $builder->build({ source => 'Branch' })->{branchcode}; my $branchcode2 = $builder->build({ source => 'Branch' })->{branchcode}; @@ -225,37 +250,4 @@ subtest 'search_by_*_field + find_by_koha_field + get_description' => sub { ); }; }; - - 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; +$schema->storage->txn_rollback; diff --git a/t/db_dependent/Koha/ItemTypes.t b/t/db_dependent/Koha/ItemTypes.t index e1ae330..0c891a6 100755 --- a/t/db_dependent/Koha/ItemTypes.t +++ b/t/db_dependent/Koha/ItemTypes.t @@ -19,10 +19,11 @@ use Modern::Perl; -use Test::More tests => 20; +use Test::More tests => 21; use Data::Dumper; use Koha::Database; use t::lib::Mocks; +use Koha::ItemType; BEGIN { use_ok('Koha::ItemType'); @@ -115,6 +116,18 @@ is( $type->summary, 'summary', 'summary' ); is( $type->checkinmsg, 'checkinmsg', 'checkinmsg' ); is( $type->checkinmsgtype, 'checkinmsgtype', 'checkinmsgtype' ); + +sub GetItemTypes { + my $dbh = C4::Context->dbh; + my $query = qq| SELECT itemtype, description FROM itemtypes |; + my $sth = $dbh->prepare($query); + $sth->execute(); + return $sth->rows; +} + +my $ItemTypes = GetItemTypes(); +is($ItemTypes, 3, "Correct number of item types" ); + t::lib::Mocks::mock_preference('language', 'en'); t::lib::Mocks::mock_preference('opaclanguages', 'en'); my $itemtypes = Koha::ItemTypes->search_with_localization; diff --git a/t/db_dependent/Koha/Libraries.t b/t/db_dependent/Koha/Libraries.t index 8126b8c..cd0f1ea 100644 --- a/t/db_dependent/Koha/Libraries.t +++ b/t/db_dependent/Koha/Libraries.t @@ -19,7 +19,7 @@ use Modern::Perl; -use Test::More tests => 9; +use Test::More tests => 10; use Koha::Library; use Koha::Libraries; @@ -86,5 +86,16 @@ is( Koha::Libraries->search->count, $nb_of_libraries + 1, 'Delete should have de $retrieved_category_2->delete; is( Koha::LibraryCategories->search->count, $nb_of_categories + 2, 'Delete should have deleted the library category' ); +sub GetBranches { + my $dbh = C4::Context->dbh; + my $query = qq| SELECT branchcode, branchname FROM branches |; + my $sth = $dbh->prepare($query); + $sth->execute(); + return $sth->rows; +} + +my $branches = GetBranches(); +is($branches, 13, "correcct number of branches" ); + $schema->storage->txn_rollback; 1; -- 2.1.4