[Koha-patches] [RFC DBIx::Class] C4::Members DBIx::Class updates
Allen Reinmeyer
allen.reinmeyer at liblime.com
Sun Feb 1 04:18:41 CET 2009
Updates for various members using DBIx::Class.
Not all methods are converted yet, but this should
give many a feel for more complex queries and in many
cases the ease of readability converting to DBIx::Class.
This patch builds off of what Andrew Moore did late last year
in setting up the Schema objects for Koha. Some of the syntax
for the more complex SQL conversion can be better explained through
understanding how SQL::Abstract works
(http://search.cpan.org/~mstrout/SQL-Abstract-1.24/lib/SQL/Abstract.pm#WHERE_CLAUSES).
The basic idea is that the search method takes 2 hashrefs: one for conditions(i.e., the
'WHERE' clauses, and another for attributes, these generally taking the form of
'ORDER BY' or 'JOIN' clauses, or specifying the columns to select from the database.
Converting the Result classes returned into hash refs, which occurs in several methods,
allows the scope of the changes to be limited to methods that call SQL directly. I have
used a example of how the changes would look if this is not used for GetGuarantees. You
can see the changes in members/moremember.pl. Minor to be sure, but multiplied out to
the entire codebase and using the HashRefInflator could be a great timesaver.
---
C4/Members.pm | 431 ++++++++++++++++--------------------------------
members/moremember.pl | 10 +-
2 files changed, 149 insertions(+), 292 deletions(-)
diff --git a/C4/Members.pm b/C4/Members.pm
index e0da7a4..972b6cd 100644
--- a/C4/Members.pm
+++ b/C4/Members.pm
@@ -19,6 +19,7 @@ package C4::Members;
use strict;
+use warnings;
use C4::Context;
use C4::Dates qw(format_date_in_iso);
use Digest::MD5 qw(md5_base64);
@@ -110,6 +111,7 @@ BEGIN {
}
use C4::Schema;
+use DBIx::Class::ResultClass::HashRefInflator;
our $schema = C4::Schema->connect();
=head1 NAME
@@ -358,42 +360,46 @@ sub GetMemberDetails {
my $dbh = C4::Context->dbh;
my $query;
my $sth;
+ my ($borrowers, $borrower);
if ($borrowernumber) {
- $sth = $dbh->prepare("select borrowers.*,category_type,categories.description from borrowers left join categories on borrowers.categorycode=categories.categorycode where borrowernumber=?");
- $sth->execute($borrowernumber);
+ $borrowers = $schema->resultset('Borrowers')->search( {borrowernumber => $borrowernumber}, {join => 'categorycode', prefetch => 'categorycode'} );
}
elsif ($cardnumber) {
- $sth = $dbh->prepare("select borrowers.*,category_type,categories.description from borrowers left join categories on borrowers.categorycode=categories.categorycode where cardnumber=?");
- $sth->execute($cardnumber);
+ $borrowers = $schema->resultset('Borrowers')->search( {cardnumber => $cardnumber}, {join => 'categorycode', prefetch => 'categorycode'} );
}
else {
return undef;
}
- my $borrower = $sth->fetchrow_hashref;
+
+ $borrowers->result_class('DBIx::Class::ResultClass::HashRefInflator');
+ $borrower = $borrowers->first;
+ $borrower->{'description'} = $borrower->{categorycode}->{description};
+ $borrower->{'category_type'} = $borrower->{categorycode}->{category_type};
+ $borrower->{'categorycode'} = $borrower->{categorycode}->{categorycode};
my ($amount) = GetMemberAccountRecords( $borrowernumber);
$borrower->{'amountoutstanding'} = $amount;
# FIXME - patronflags calls GetMemberAccountRecords... just have patronflags return $amount
my $flags = patronflags( $borrower);
my $accessflagshash;
- $sth = $dbh->prepare("select bit,flag from userflags");
- $sth->execute;
- while ( my ( $bit, $flag ) = $sth->fetchrow ) {
- if ( $borrower->{'flags'} && $borrower->{'flags'} & 2**$bit ) {
- $accessflagshash->{$flag} = 1;
+ #$sth = $dbh->prepare("select bit,flag from userflags");
+ my @bitflags = $schema->resultset('Userflags')->search( undef, {columns => ['bit','flag']} );
+ foreach my $userflag (@bitflags) {
+ #while ( my ( $bit, $flag ) = $) {
+ if ( $borrower->{'flags'} && $borrower->{'flags'} & 2**$userflag->bit ) {
+ $accessflagshash->{$userflag->flag} = 1;
}
}
- $sth->finish;
$borrower->{'flags'} = $flags;
$borrower->{'authflags'} = $accessflagshash;
# find out how long the membership lasts
- $sth =
- $dbh->prepare(
- "select enrolmentperiod from categories where categorycode = ?");
- $sth->execute( $borrower->{'categorycode'} );
- my $enrolment = $sth->fetchrow;
- $borrower->{'enrolmentperiod'} = $enrolment;
+ my $enrolment =
+ $schema->resultset('Categories')->search(
+ {categorycode => ($borrower->{'categorycode'}) },
+ {columns => ['enrolmentperiod']} )->first;
+
+ $borrower->{'enrolmentperiod'} = $enrolment->get_column('enrolmentperiod');
return ($borrower); #, $flags, $accessflagshash);
}
@@ -524,28 +530,28 @@ the C<borrowers> table in the Koha database.
#'
sub GetMember {
my ( $information, $type ) = @_;
- my $dbh = C4::Context->dbh;
- my $sth;
- my $select = "
-SELECT borrowers.*, categories.category_type, categories.description
-FROM borrowers
-LEFT JOIN categories on borrowers.categorycode=categories.categorycode
-";
+
if (defined($type) and ( $type eq 'cardnumber' || $type eq 'firstname'|| $type eq 'userid'|| $type eq 'borrowernumber' ) ){
- $information = uc $information;
- $sth = $dbh->prepare("$select WHERE $type=?");
- } else {
- $sth = $dbh->prepare("$select WHERE borrowernumber=?");
- }
- $sth->execute($information);
- my $data = $sth->fetchrow_hashref;
- ($data) and return ($data);
+ my $member = $schema->resultset('Borrowers')->search( {$type => uc $information}, {join => 'categorycode', prefetch => 'categorycode'});
+ $member->result_class('DBIx::Class::ResultClass::HashRefInflator');
+ while( my $data = $member->next ) {
+ $data->{description} = $data->{categorycode}->{description};
+ $data->{category_type} = $data->{categorycode}->{category_type};
+ $data->{categorycode} = $data->{categorycode}->{categorycode};
+ return $data;
+ }
+ }
if (defined($type) and ($type eq 'cardnumber' || $type eq 'firstname')) { # otherwise, try with firstname
- $sth = $dbh->prepare("$select WHERE firstname like ?");
- $sth->execute($information);
- $data = $sth->fetchrow_hashref;
- ($data) and return ($data);
+ # $sth = $dbh->prepare("$select WHERE firstname like ?");
+ my $member = $schema->resultset('Borrowers')->search( [$type => $information], {join => 'categorycode'});
+ $member->result_class('DBIx::Class::ResultClass::HashRefInflator');
+ while( my $data = $member->next ) {
+ $data->{description} = $data->{categorycode}->{description};
+ $data->{category_type} = $data->{categorycode}->{category_type};
+ $data->{categorycode} = $data->{categorycode}->{categorycode};
+ return $data;
+ }
}
return undef;
}
@@ -567,30 +573,23 @@ the total fine currently due by the borrower.
#'
sub GetMemberIssuesAndFines {
my ( $borrowernumber ) = @_;
- my $dbh = C4::Context->dbh;
- my $query = "SELECT COUNT(*) FROM issues WHERE borrowernumber = ?";
- $debug and warn $query."\n";
- my $sth = $dbh->prepare($query);
- $sth->execute($borrowernumber);
- my $issue_count = $sth->fetchrow_arrayref->[0];
- $sth->finish;
+ my $issue_count = $schema->resultset('Issues')->count( {borrowernumber => $borrowernumber} );
- $sth = $dbh->prepare(
- "SELECT COUNT(*) FROM issues
- WHERE borrowernumber = ?
- AND date_due < now()"
- );
- $sth->execute($borrowernumber);
- my $overdue_count = $sth->fetchrow_arrayref->[0];
- $sth->finish;
+ my $date_now = C4::Dates->new()->output('iso');
+ my $overdue_count =
+ $schema->resultset('Issues')->count(
+ {borrowernumber => $borrowernumber,
+ date_due => {'<',C4::Dates->new()->output('iso')}} );
- $sth = $dbh->prepare("SELECT SUM(amountoutstanding) FROM accountlines WHERE borrowernumber = ?");
- $sth->execute($borrowernumber);
- my $total_fines = $sth->fetchrow_arrayref->[0];
- $sth->finish;
+ my $total_fines =
+ $schema->resultset('Accountlines')->search(
+ {borrowernumber => $borrowernumber},
+ {select => [{sum => 'amountoutstanding'}], as => ['amtdue']} );
+ my $borrower_due = $total_fines->first();
+ my $total_due = $borrower_due->get_column('amtdue');
- return ($overdue_count, $issue_count, $total_fines);
+ return ($overdue_count, $issue_count, $total_due);
}
sub columns(;$) {
@@ -780,16 +779,20 @@ sub AddMember {
return $data{'borrowernumber'};
}
+=head3 Check_Userid
+
+Checks a userid against the database to see if it is unique to the member.
+Also validates that the userid is not empty.
+
+=cut
+
sub Check_Userid {
my ($uid,$member) = @_;
- my $dbh = C4::Context->dbh;
+
# Make sure the userid chosen is unique and not theirs if non-empty. If it is not,
# Then we need to tell the user and have them create a new one.
- my $sth =
- $dbh->prepare(
- "SELECT * FROM borrowers WHERE userid=? AND borrowernumber != ?");
- $sth->execute( $uid, $member );
- if ( ( $uid ne '' ) && ( my $row = $sth->fetchrow_hashref ) ) {
+ my $rows_returned = $schema->resultset('Borrowers')->count( {userid => $uid, borrowernumber => { '!=', $member } } );
+ if ( ( $uid ne '' ) && $rows_returned > 0 ) {
return 0;
}
else {
@@ -815,24 +818,10 @@ sub Generate_Userid {
sub changepassword {
my ( $uid, $member, $digest ) = @_;
- my $dbh = C4::Context->dbh;
-
-#Make sure the userid chosen is unique and not theirs if non-empty. If it is not,
-#Then we need to tell the user and have them create a new one.
- my $resultcode;
- my $sth =
- $dbh->prepare(
- "SELECT * FROM borrowers WHERE userid=? AND borrowernumber != ?");
- $sth->execute( $uid, $member );
- if ( ( $uid ne '' ) && ( my $row = $sth->fetchrow_hashref ) ) {
- $resultcode=0;
- }
- else {
+ my $resultcode = 0;
+ if ( Check_Userid( $uid, $member) ) {
#Everything is good so we can update the information.
- $sth =
- $dbh->prepare(
- "update borrowers set userid=?, password=? where borrowernumber=?");
- $sth->execute( $uid, $digest, $member );
+ $schema->resultset('Borrowers')->find( $member )->update( {userid=>$uid,password=>$digest} );
$resultcode=1;
}
@@ -945,17 +934,12 @@ of references to hash, which gives the actual results.
#'
sub GetGuarantees {
my ($borrowernumber) = @_;
- my $dbh = C4::Context->dbh;
- my $sth =
- $dbh->prepare(
-"select cardnumber,borrowernumber, firstname, surname from borrowers where guarantorid=?"
- );
- $sth->execute($borrowernumber);
- my @dat;
- my $data = $sth->fetchall_arrayref({});
- $sth->finish;
- return ( scalar(@$data), $data );
+ my @data =
+ $schema->resultset('Borrowers')->search(
+ {guarantorid=>$borrowernumber},
+ {columns => [qw(cardnumber borrowernumber firstname surname)]} );
+ return ( scalar(@data), \@data );
}
=head2 UpdateGuarantees
@@ -1444,7 +1428,6 @@ If no category code provided, the function returns all the categories.
=cut
sub GetBorrowercategoryList {
- my $dbh = C4::Context->dbh;
my $sth =
$dbh->prepare(
"SELECT *
@@ -1454,7 +1437,6 @@ sub GetBorrowercategoryList {
$sth->execute;
my $data =
$sth->fetchall_arrayref({});
- $sth->finish();
return $data;
} # sub getborrowercategory
@@ -1472,16 +1454,13 @@ descriptions.
#'
sub ethnicitycategories {
- my $dbh = C4::Context->dbh;
- my $sth = $dbh->prepare("Select code,name from ethnicity order by name");
- $sth->execute;
+ my $results = $schema->resultset('Ethnicity')->search( undef, {order_by => ['name']} );
my %labels;
my @codes;
- while ( my $data = $sth->fetchrow_hashref ) {
- push @codes, $data->{'code'};
- $labels{ $data->{'code'} } = $data->{'name'};
+ while ( my $data = $results->next ) {
+ push @codes, $data->code;
+ $labels{ $data->code } = $data->name;
}
- $sth->finish;
return ( \@codes, \%labels );
}
@@ -1495,18 +1474,12 @@ Koha database ("European" or "Pacific Islander").
=cut
-#'
-
sub fixEthnicity {
my $ethnicity = shift;
return unless $ethnicity;
- my $dbh = C4::Context->dbh;
- my $sth = $dbh->prepare("Select name from ethnicity where code = ?");
- $sth->execute($ethnicity);
- my $data = $sth->fetchrow_hashref;
- $sth->finish;
- return $data->{'name'};
-} # sub fixEthnicity
+ my $data = $schema->resultset('Ethnicity')->search( {code => $ethnicity}, {columns => ['name']} )->first;
+ return $data->name;
+}
=head2 GetAge
@@ -1544,17 +1517,14 @@ Just returns a list of all the borrowers of type I, borrownumber and name
#'
sub get_institutions {
- my $dbh = C4::Context->dbh();
- my $sth =
- $dbh->prepare(
-"SELECT borrowernumber,surname FROM borrowers WHERE categorycode=? ORDER BY surname"
- );
- $sth->execute('I');
my %orgs;
- while ( my $data = $sth->fetchrow_hashref() ) {
- $orgs{ $data->{'borrowernumber'} } = $data;
+ my $results =
+ $schema->resultset('Borrowers')->search(
+ {categorycode => 'I'},
+ {columns => [qw(borrowernumber surname)], order_by => 'surname'} );
+ while ( my $data = $results->next ) {
+ $orgs{ $data->borrowernumber } = $data;
}
- $sth->finish();
return ( \%orgs );
} # sub get_institutions
@@ -1568,6 +1538,7 @@ Takes a borrowernumber and a list of other borrowernumbers and inserts them into
=cut
#'
+#FIXME need to create BorrowersToBorrowers Schema object
sub add_member_orgs {
my ( $borrowernumber, $otherborrowers ) = @_;
my $dbh = C4::Context->dbh();
@@ -1593,29 +1564,19 @@ WHERE =>OUEST PROVENCE OR EXTERIEUR
=cut
sub GetCities {
-
- #my ($type_city) = @_;
- my $dbh = C4::Context->dbh;
- my $query = qq|SELECT cityid,city_zipcode,city_name
- FROM cities
- ORDER BY city_name|;
- my $sth = $dbh->prepare($query);
-
- #$sth->execute($type_city);
- $sth->execute();
+ my $results = $schema->resultset('Cities')->search( undef, {columns => [qw(cityid city_zipcode city_name)], order_by => 'city_name'} );
my %city;
my @id;
# insert empty value to create a empty choice in cgi popup
push @id, " ";
$city{""} = "";
- while ( my $data = $sth->fetchrow_hashref ) {
- push @id, $data->{'city_zipcode'}."|".$data->{'city_name'};
- $city{ $data->{'city_zipcode'}."|".$data->{'city_name'} } = $data->{'city_name'};
+ while ( my $data = $results->next) {
+ push @id, $data->city_zipcode."|".$data->city_name;
+ $city{ $data->city_zipcode."|".$data->city_name } = $data->city_name;
}
#test to know if the table contain some records if no the function return nothing
my $id = @id;
- $sth->finish;
if ( $id == 1 ) {
# all we have is the one blank row
return ();
@@ -1639,14 +1600,9 @@ C<&$category>this is the value of authorized value category
sub GetSortDetails {
my ( $category, $sortvalue ) = @_;
- my $dbh = C4::Context->dbh;
- my $query = qq|SELECT lib
- FROM authorised_values
- WHERE category=?
- AND authorised_value=? |;
- my $sth = $dbh->prepare($query);
- $sth->execute( $category, $sortvalue );
- my $lib = $sth->fetchrow;
+ my $results = $schema->resultset('AuthorisedValues')->search( {category => $category, authorised_value => $sortvalue}, {columns => [qw(lib)]} );
+ my $sort_detail = $results->next;
+ my $lib = $results->get_column('lib');
return ($lib) if ($lib);
return ($sortvalue) unless ($lib);
}
@@ -1662,21 +1618,13 @@ C<&$member>this is the borrowernumber
sub MoveMemberToDeleted {
my ($member) = @_;
- my $dbh = C4::Context->dbh;
- my $query;
- $query = qq|SELECT *
- FROM borrowers
- WHERE borrowernumber=?|;
- my $sth = $dbh->prepare($query);
- $sth->execute($member);
- my @data = $sth->fetchrow_array;
- $sth->finish;
- $sth =
- $dbh->prepare( "INSERT INTO deletedborrowers VALUES ("
- . ( "?," x ( scalar(@data) - 1 ) )
- . "?)" );
- $sth->execute(@data);
- $sth->finish;
+ my $borrowers = $schema->resultset('Borrowers')->search( {borrowernumber => $member} );
+ $borrowers->result_class('DBIx::Class::ResultClass::HashRefInflator');
+ my $borrower = $borrowers->next;
+
+ #Should deletedborrowers match borrowers? Why is deletedborrowers missing smsalertnumber?
+ delete $borrower->{smsalertnumber};
+ $schema->resultset('Deletedborrowers')->create( $borrower );
}
=head2 DelMember
@@ -1694,22 +1642,14 @@ sub DelMember {
#warn "in delmember with $borrowernumber";
return unless $borrowernumber; # borrowernumber is mandatory.
- my $query = qq|DELETE
- FROM reserves
- WHERE borrowernumber=?|;
- my $sth = $dbh->prepare($query);
- $sth->execute($borrowernumber);
- $sth->finish;
- $query = "
- DELETE
- FROM borrowers
- WHERE borrowernumber = ?
- ";
- $sth = $dbh->prepare($query);
- $sth->execute($borrowernumber);
- $sth->finish;
+ #my $query = qq|DELETE
+ # FROM reserves
+ # WHERE borrowernumber=?|;
+ $schema->resultset('Reserves')->search( {borrowernumber => $borrowernumber} )->delete;
+ #$query = " DELETE FROM borrowers WHERE borrowernumber = ? ";
+ my $result = $schema->resultset('Borrowers')->find( $borrowernumber )->delete;
logaction("MEMBERS", "DELETE", $borrowernumber, "") if C4::Context->preference("BorrowersLog");
- return $sth->rows;
+ return $result;
}
=head2 ExtendMemberSubscriptionTo (OUEST-PROVENCE)
@@ -1723,27 +1663,22 @@ Returns ISO date.
sub ExtendMemberSubscriptionTo {
my ( $borrowerid,$date) = @_;
- my $dbh = C4::Context->dbh;
my $borrower = GetMember($borrowerid,'borrowernumber');
unless ($date){
$date=POSIX::strftime("%Y-%m-%d",localtime());
my $borrower = GetMember($borrowerid,'borrowernumber');
$date = GetExpiryDate( $borrower->{'categorycode'}, $date );
}
- my $sth = $dbh->do(<<EOF);
-UPDATE borrowers
-SET dateexpiry='$date'
-WHERE borrowernumber='$borrowerid'
-EOF
+
+ my $date_updated = $schema->resultset('Borrowers')->find($borrowerid)->update( {dateexpiry => $date} );
# add enrolmentfee if needed
- $sth = $dbh->prepare("SELECT enrolmentfee FROM categories WHERE categorycode=?");
- $sth->execute($borrower->{'categorycode'});
- my ($enrolmentfee) = $sth->fetchrow;
+ my $fees = $schema->resultset('Categories')->search( {categorycode => $borrower->{categorycode}}, {columns => ['enrolmentfee']} );
+ my $enrolmentfee = $fees->enrolmentfee;
if ($enrolmentfee) {
# insert fee in patron debts
manualinvoice($borrower->{'borrowernumber'}, '', '', 'A', $enrolmentfee);
}
- return $date if ($sth);
+ return $date if ($date_updated);
return 0;
}
@@ -1758,27 +1693,20 @@ codes, and a reference-to-hash, which maps the road type of the road .
=cut
sub GetRoadTypes {
- my $dbh = C4::Context->dbh;
- my $query = qq|
-SELECT roadtypeid,road_type
-FROM roadtype
-ORDER BY road_type|;
- my $sth = $dbh->prepare($query);
- $sth->execute();
+ my $types = $schema->resultset('Roadtype')->search( undef, {order_by => 'road_type'} );
my %roadtype;
my @id;
# insert empty value to create a empty choice in cgi popup
-
- while ( my $data = $sth->fetchrow_hashref ) {
-
- push @id, $data->{'roadtypeid'};
- $roadtype{ $data->{'roadtypeid'} } = $data->{'road_type'};
+ while ( my $data = $types->next) {
+ my $roadid = $data->roadtypeid;
+ my $roadtype = $data->road_type;
+ push @id, $roadid;
+ $roadtype{ $roadid } = $roadtype;
}
-#test to know if the table contain some records if no the function return nothing
+ #test to know if the table contain some records if no the function return nothing
my $id = @id;
- $sth->finish;
if ( $id eq 0 ) {
return ();
}
@@ -1812,7 +1740,7 @@ sub GetTitles {
=head2 GetPatronImage
- my ($imagedata, $dberror) = GetPatronImage($cardnumber);
+ my $imagedata = GetPatronImage($cardnumber);
Returns the mimetype and binary image data of the image for the patron with the supplied cardnumber.
@@ -1821,15 +1749,10 @@ Returns the mimetype and binary image data of the image for the patron with the
sub GetPatronImage {
my ($cardnumber) = @_;
warn "Cardnumber passed to GetPatronImage is $cardnumber" if $debug;
- my $dbh = C4::Context->dbh;
my $query = 'SELECT mimetype, imagefile FROM patronimage WHERE cardnumber = ?';
- my $sth = $dbh->prepare($query);
- $sth->execute($cardnumber);
- my $imagedata = $sth->fetchrow_hashref;
- my $dberror = $sth->errstr;
- warn "Database error!" if $sth->errstr;
- $sth->finish;
- return $imagedata, $dberror;
+ my $imagedata = $schema->resultset('Patronimage')->search( {cardnumber => $cardnumber}, {columns => [qw(mimetyp imagefile)]} );
+ $imagedata->result_class('DBIx::Class::ResultClass::HashRefInflator');
+ return $imagedata;
}
=head2 PutPatronImage
@@ -1844,14 +1767,13 @@ NOTE: This function is good for updating images as well as inserting new images
sub PutPatronImage {
my ($cardnumber, $mimetype, $imgfile) = @_;
warn "Parameters passed in: Cardnumber=$cardnumber, Mimetype=$mimetype, " . ($imgfile ? "Imagefile" : "No Imagefile") if $debug;
- my $dbh = C4::Context->dbh;
- my $query = "INSERT INTO patronimage (cardnumber, mimetype, imagefile) VALUES (?,?,?) ON DUPLICATE KEY UPDATE imagefile = ?;";
- my $sth = $dbh->prepare($query);
- $sth->execute($cardnumber,$mimetype,$imgfile,$imgfile);
- warn "Error returned inserting $cardnumber.$mimetype." if $sth->errstr;
- my $dberror = $sth->errstr;
- $sth->finish;
- return $dberror;
+
+ my $result =
+ $schema->resultset('Patronimage')->update_or_create(
+ {cardnumber => $cardnumber, mimetype => $mimetype, imagefile => $imgfile},
+ {key => 'primary'}
+ );
+ return $result;
}
=head2 RmPatronImage
@@ -1865,14 +1787,8 @@ Removes the image for the patron with the supplied cardnumber.
sub RmPatronImage {
my ($cardnumber) = @_;
warn "Cardnumber passed to GetPatronImage is $cardnumber" if $debug;
- my $dbh = C4::Context->dbh;
- my $query = "DELETE FROM patronimage WHERE cardnumber = ?;";
- my $sth = $dbh->prepare($query);
- $sth->execute($cardnumber);
- my $dberror = $sth->errstr;
- warn "Database error!" if $sth->errstr;
- $sth->finish;
- return $dberror;
+ my $result = $schema->resultset('Patronimage')->find( $cardnumber )->delete;
+ return $result;
}
=head2 GetRoadTypeDetails (OUEST-PROVENCE)
@@ -1887,14 +1803,8 @@ C<&$roadtypeid>this is the value of roadtype s
sub GetRoadTypeDetails {
my ($roadtypeid) = @_;
- my $dbh = C4::Context->dbh;
- my $query = qq|
-SELECT road_type
-FROM roadtype
-WHERE roadtypeid=?|;
- my $sth = $dbh->prepare($query);
- $sth->execute($roadtypeid);
- my $roadtype = $sth->fetchrow;
+ my $roadtype_obj = $schema->resultset('Roadtype')->search( {roadtypeid => $roadtypeid} )->first;
+ my $roadtype = $roadtype_obj->road_type if defined $roadtype_obj;
return ($roadtype);
}
@@ -1951,53 +1861,6 @@ sub GetBorrowersWhoHaveNotBorrowedSince {
return \@results;
}
-=head2 GetBorrowersWhoHaveNeverBorrowed
-
-$results = &GetBorrowersWhoHaveNeverBorrowed
-
-this function get all borrowers who have never borrowed.
-
-I<$result> is a ref to an array which all elements are a hasref.
-
-=cut
-
-sub GetBorrowersWhoHaveNeverBorrowed {
- my $filterbranch = shift ||
- ((C4::Context->preference('IndependantBranches')
- && C4::Context->userenv
- && C4::Context->userenv->{flags}!=1
- && C4::Context->userenv->{branch})
- ? C4::Context->userenv->{branch}
- : "");
- my $dbh = C4::Context->dbh;
- my $query = "
- SELECT borrowers.borrowernumber,max(timestamp) as latestissue
- FROM borrowers
- LEFT JOIN issues ON borrowers.borrowernumber = issues.borrowernumber
- WHERE issues.borrowernumber IS NULL
- ";
- my @query_params;
- if ($filterbranch && $filterbranch ne ""){
- $query.=" AND borrowers.branchcode= ?";
- push @query_params,$filterbranch;
- }
- warn $query if $debug;
-
- my $sth = $dbh->prepare($query);
- if (scalar(@query_params)>0){
- $sth->execute(@query_params);
- }
- else {
- $sth->execute;
- }
-
- my @results;
- while ( my $data = $sth->fetchrow_hashref ) {
- push @results, $data;
- }
- return \@results;
-}
-
=head2 GetBorrowersWithIssuesHistoryOlderThan
$results = &GetBorrowersWithIssuesHistoryOlderThan($date)
@@ -2045,27 +1908,21 @@ sub GetBorrowersWithIssuesHistoryOlderThan {
=head2 GetBorrowersNamesAndLatestIssue
-$results = &GetBorrowersNamesAndLatestIssueList(@borrowernumbers)
+$results = &GetBorrowersNamesAndLatestIssueList()
this function get borrowers Names and surnames and Issue information.
-I<@borrowernumbers> is an array which all elements are borrowernumbers.
-This hashref is containt the number of time this borrowers has borrowed before I<$date> and the borrowernumber.
-
=cut
sub GetBorrowersNamesAndLatestIssue {
- my $dbh = C4::Context->dbh;
- my @borrowernumbers=@_;
- my $query = "
- SELECT surname,lastname, phone, email,max(timestamp)
- FROM borrowers
- LEFT JOIN issues ON borrowers.borrowernumber=issues.borrowernumber
- GROUP BY borrowernumber
- ";
- my $sth = $dbh->prepare($query);
- $sth->execute;
- my $results = $sth->fetchall_arrayref({});
+ my $results =
+ $schema->resultset('Borrowers')->
+ search( undef,
+ { columns => [qw(surname firstname phone email issues.timestamp)],
+ join => 'issues',
+ prefetch => 'issues',
+ group_by => 'me.borrowernumber'}
+ );
return $results;
}
diff --git a/members/moremember.pl b/members/moremember.pl
index 941272d..ae1a6fa 100755
--- a/members/moremember.pl
+++ b/members/moremember.pl
@@ -160,13 +160,13 @@ if ( $category_type eq 'A' ) {
#
my ( $count, $guarantees ) = GetGuarantees( $data->{'borrowernumber'} );
my @guaranteedata;
- for ( my $i = 0 ; $i < $count ; $i++ ) {
+ foreach my $guarantee (@$guarantees ) {
push(@guaranteedata,
{
- borrowernumber => $guarantees->[$i]->{'borrowernumber'},
- cardnumber => $guarantees->[$i]->{'cardnumber'},
- name => $guarantees->[$i]->{'firstname'} . " "
- . $guarantees->[$i]->{'surname'}
+ borrowernumber => $guarantee->borrowernumber,
+ cardnumber => $guarantee->cardnumber,
+ name => $guarantee->firstname . " "
+ . $guarantee->surname
}
);
}
--
1.5.5.GIT
More information about the Koha-patches
mailing list