From 42147667f6d7e399a028742a0682624a671d04bc Mon Sep 17 00:00:00 2001
From: Alex Buckley <alexbuckley@catalyst.net.nz>
Date: Fri, 10 Mar 2017 11:33:25 +0000
Subject: [PATCH] Bug 18212 - Four successfully transferred sql queries to
 Koha::AuthorisedValue, Koha::ItemType, Koha::Libraries from aqplan.pl
 administrative script

All these queries have been placed inside subroutines in the perl
modules outlined above, each subroutine has POD, and a successful unit test
---
 Koha/AuthorisedValue.pm           | 43 ++++++++++++++++++----------
 Koha/ItemType.pm                  | 14 ++++-----
 admin/aqplan.pl                   | 34 +++++++++++-----------
 t/db_dependent/AuthorisedValues.t | 60 ++++++++++++++++++---------------------
 t/db_dependent/Koha/ItemTypes.t   | 15 +++++++++-
 t/db_dependent/Koha/Libraries.t   | 13 ++++++++-
 6 files changed, 105 insertions(+), 74 deletions(-)

diff --git a/Koha/AuthorisedValue.pm b/Koha/AuthorisedValue.pm
index 4e5968a..83d427d 100644
--- a/Koha/AuthorisedValue.pm
+++ b/Koha/AuthorisedValue.pm
@@ -159,33 +159,46 @@ sub _avb_resultset {
 }
 
 =head3 GetAuthValues
-my $sth = Koha::AuthorisedValue->GetAuthValues($authcat,$dbh);
+my @AuthValues = Koha::AuthorisedValue->GetAuthValues($authcat);
 
-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
+This 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);
+my @DistinctAuthValues = Koha::AuthorisedValue->GetDistinctCat();
 
-This subroutine retrieves and returns all the different category values starting with 'A' in the authorised_values table
+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;
+};
+
+
+#    my ($self, $dbh) = @_;
+#    my $sth = $dbh->prepare("select distinct category from authorised_values where category like 'A%' ");
+#    $sth->execute;
+#    return $sth;
+#}
 
 =head3 type
 
diff --git a/Koha/ItemType.pm b/Koha/ItemType.pm
index b3f4438..96ec1f0 100644
--- a/Koha/ItemType.pm
+++ b/Koha/ItemType.pm
@@ -84,15 +84,15 @@ 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(  );
-     return $sth;
+sub GetItemTypes {
+    my ($self, $dbh) = @_;
+    my $query = qq| SELECT itemtype, description FROM itemtypes |;
+    my $sth   = $dbh->prepare($query);
+    $sth->execute();
+    return $sth;
 }
 
 
diff --git a/admin/aqplan.pl b/admin/aqplan.pl
index 38bfe84..8e87c5f 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;
+    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,18 @@ 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};
+          }
+     }
+
+
 
-   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;
 
 } elsif ( $authcat eq 'BRANCHES' ) {
 
diff --git a/t/db_dependent/AuthorisedValues.t b/t/db_dependent/AuthorisedValues.t
index e7403ff..8ecc967 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 => 16;
+use Test::More tests => 17;
 
 use C4::Context;
 use Koha::AuthorisedValue;
@@ -20,6 +20,7 @@ $dbh->do("DELETE FROM authorised_value_categories");
 Koha::AuthorisedValueCategory->new({ category_name => 'av_for_testing' })->store;
 Koha::AuthorisedValueCategory->new({ category_name => 'aaav_for_testing' })->store;
 Koha::AuthorisedValueCategory->new({ category_name => 'restricted_for_testing' })->store;
+
 my $av1 = Koha::AuthorisedValue->new(
     {
         category         => 'av_for_testing',
@@ -81,6 +82,31 @@ ok( $av2->id(), 'AV 2 is inserted' );
 ok( $av3->id(), 'AV 3 is inserted' );
 ok( $av4->id(), 'AV 4 is inserted' );
 
+ 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" );
+
+
+
 is( $av3->opac_description, 'opac display value 3', 'Got correction opac description if lib_opac is set' );
 $av3->lib_opac('');
 is( $av3->opac_description, 'display value 3', 'Got correction opac description if lib_opac is *not* set' );
@@ -225,38 +251,6 @@ 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 ($self, $dbh) = @_;
-        my $sth = $dbh->prepare("select distinct category from authorised_values where category like 'A%' ");
-        $sth->execute;
-        return $sth;
-        my $DistAuth = 0;
-        if ($sth->rows > 0){
-            $DistAuth = 1;
-        }
-        return $DistAuth;
-    };
-    my $DistAuth = GetDistinctCat();
-    is ( $DistAuth, 0, 'Does not exist in the database successful');
-
 
 
 
diff --git a/t/db_dependent/Koha/ItemTypes.t b/t/db_dependent/Koha/ItemTypes.t
index 9ce1ad6..992d82b 100755
--- a/t/db_dependent/Koha/ItemTypes.t
+++ b/t/db_dependent/Koha/ItemTypes.t
@@ -19,9 +19,10 @@
 
 use Modern::Perl;
 
-use Test::More tests => 18;
+use Test::More tests => 19;
 use Data::Dumper;
 use Koha::Database;
+use Koha::ItemType;
 
 BEGIN {
     use_ok('Koha::ItemType');
@@ -76,4 +77,16 @@ 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();
+   warn $sth->rows;
+   return $sth->rows;
+}
+
+my $ItemTypes = GetItemTypes();
+is($ItemTypes, 10, "Correct number of item types" );
+
 $schema->txn_rollback;
diff --git a/t/db_dependent/Koha/Libraries.t b/t/db_dependent/Koha/Libraries.t
index 8126b8c..a05c100 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