From e78b5e09aaf3db88a4bd3c05aefac1faad7914b8 Mon Sep 17 00:00:00 2001 From: Alex Buckley Date: Fri, 10 Mar 2017 00:02:39 +0000 Subject: [PATCH] Bug 18212 - Moved all sql queries out aqplan.pl into Koha::Libraries.pm, Koha::ItemType.pm, Koha::AuthorisedValue.pm files The new subroutines in the perl modules contain POD --- Koha/AuthorisedValue.pm | 29 +++++++++++++++++++++++++ Koha/ItemType.pm | 17 +++++++++++++++ Koha/Libraries.pm | 15 +++++++++++++ admin/aqplan.pl | 57 ++++++++++++++++++++++--------------------------- 4 files changed, 86 insertions(+), 32 deletions(-) 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 48df808..b3f4438 100644 --- a/Koha/ItemType.pm +++ b/Koha/ItemType.pm @@ -81,10 +81,27 @@ 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..acd334f 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 9dac549..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,17 +191,17 @@ HideCols($authcat, @hide_cols); } # ------------------------------------------------------------ if ( $authcat =~ m/^Asort/ ) { - my $categoryauthvalues = C4::Acquisition->GetAuthValuesForCategory($authcat); + my $sth = Koha::AuthorisedValue->GetAuthValues($authcat,$dbh); - if ( $categoryauthvalues->rows > 0 ) { - for ( my $i = 0 ; $i < $categoryauthvalues->rows ; $i++ ) { - my $results = $categoryauthvalues->fetchrow_hashref; - push @authvals, $results->{authorised_value}; - $labels{ $results->{authorised_value} } = $results->{lib}; + 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}; } } - $categoryauthvalues->finish; - @authvals = sort { $a <=> $b } @authvals; + $sth->finish; + @authvals = sort { $a <=> $b } @authvals; } elsif ( $authcat eq 'MONTHS' && $budget_period_startdate && $budget_period_enddate ) { @@ -228,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; -- 2.1.4