[Koha-patches] [PATCH] Adding Sort1 & Sort2 in overdue report.
Joe Atzberger
joe.atzberger at liblime.com
Tue Jun 30 17:02:11 CEST 2009
This patch is questionable to apply given that Koha generates randomized
sort keys by default. That means the two new query operations will have to
read *every* row in the borrowers table (and sort them all) twice. That
also means that @sort1 and @sort2 will *each* have as many elements as there
are borrowers rows. For a library with 100,000 patrons, that would mean the
starting report page would likely be bigger than actual report.
Consequently it will force prohibitively poor performance on large
installations, possibly timing out before returning any results.
Similar approaches were part of reports for previous versions of Koha, but
were removed for the same reason. Any use of the sort keys like this should
be conditionalized so that the report is still useful for large datasets in
the default configuration.
--Joe Atzberger
On Tue, Jun 30, 2009 at 11:49 AM, Amit Gupta <amit.gupta at osslabs.biz> wrote:
> Adding another filter(Sort1 & Sort2) in the overdue report.
> Adding Sort1, Sort2, Book Price as new column in the circulation overdue
> report.
> ---
> circ/overdue.pl | 37
> ++++++++++++++++++++
> .../prog/en/modules/circ/overdue.tmpl | 19 ++++++++++
> 2 files changed, 56 insertions(+), 0 deletions(-)
>
> diff --git a/circ/overdue.pl b/circ/overdue.pl
> index b1b1be4..62112a2 100755
> --- a/circ/overdue.pl
> +++ b/circ/overdue.pl
> @@ -39,6 +39,8 @@ my $itemtypefilter = $input->param('itemtype') || '';
> my $borflagsfilter = $input->param('borflag') || '';
> my $branchfilter = $input->param('branch') || '';
> my $op = $input->param('op') || '';
> +my $sort1filter = $input->param('sort1');
> +my $sort2filter = $input->param('sort2');
> my $isfiltered = $op =~ /apply/i && $op =~ /filter/i;
> my $noreport = C4::Context->preference('FilterBeforeOverdueReport')
> && ! $isfiltered && $op ne "csv";
>
> @@ -77,6 +79,29 @@ while (my ($itemtype, $description) =$req->fetchrow) {
> itemtypename => $description,
> };
> }
> +$req = $dbh->prepare( "select distinctrow sort1 from borrowers where sort1
> is not null order by sort1 ");
> +$req->execute;
> +my @sort1;
> +while (my ($sort1) =$req->fetchrow) {
> + my $selected = 1 if $sort1 eq $sort1filter;
> + my %row =(value => $sort1,
> + selected => $selected,
> + name => $sort1,
> + );
> + push @sort1, \%row;
> +}
> +$req = $dbh->prepare( "select distinctrow sort2 from borrowers where sort2
> is not null order by sort2");
> +$req->execute;
> +my @sort2;
> +while (my ($sort2) =$req->fetchrow) {
> + my $selected = 1 if $sort2 eq $sort2filter;
> + my %row =(value => $sort2,
> + selected => $selected,
> + name => $sort2,
> + );
> + push @sort2, \%row;
> +}
> +
> my $onlymine=C4::Context->preference('IndependantBranches') &&
> C4::Context->userenv &&
> C4::Context->userenv->{flags} % 2 !=1 &&
> @@ -204,6 +229,8 @@ $template->param(
> branchloop => GetBranchesLoop($branchfilter, $onlymine),
> branchfilter => $branchfilter,
> borcatloop=> \@borcatloop,
> + sort1loop => \@sort1,
> + sort2loop => \@sort2,
> itemtypeloop => \@itemtypeloop,
> patron_attr_filter_loop => \@patron_attr_filter_loop,
> borname => $bornamefilter,
> @@ -231,6 +258,8 @@ if ($noreport) {
> concat(surname,' ', firstname) as borrower,
> borrowers.phone,
> borrowers.email,
> + borrowers.sort1,
> + borrowers.sort2,
> issues.itemnumber,
> items.barcode,
> biblio.title,
> @@ -239,6 +268,7 @@ if ($noreport) {
> biblio.biblionumber,
> borrowers.branchcode,
> items.itemcallnumber,
> + items.price,
> items.replacementprice
> FROM issues
> LEFT JOIN borrowers ON
> (issues.borrowernumber=borrowers.borrowernumber )
> @@ -253,6 +283,8 @@ if ($noreport) {
> $strsth.=" AND biblioitems.itemtype = '" . $itemtypefilter . "' " if
> $itemtypefilter;
> $strsth.=" AND borrowers.flags = '" . $borflagsfilter . "' " if
> $borflagsfilter;
> $strsth.=" AND borrowers.branchcode = '" . $branchfilter . "' " if
> $branchfilter;
> + $strsth.=" AND borrowers.sort1 = '" . $sort1filter . "' " if
> ($sort1filter) ;
> + $strsth.=" AND borrowers.sort2 = '" . $sort2filter . "' " if
> ($sort2filter) ;
> # restrict patrons (borrowers) to those matching the patron attribute
> filter(s), if any
> my $bnlist = $have_pattr_filter_data ? join(',',keys
> %borrowernumber_to_attributes) : '';
> $strsth =~ s/WHERE 1=1/WHERE 1=1 AND borrowers.borrowernumber IN
> ($bnlist)/ if $bnlist;
> @@ -262,6 +294,8 @@ if ($noreport) {
> ($order eq "title" or $order eq "title desc") ? "$order,
> date_due, borrower" :
> ($order eq "barcode" or $order eq "barcode desc") ?
> "items.$order, date_due, borrower" :
> ($order eq "date_due desc") ? "date_due
> DESC, borrower" :
> + ($order eq "sort1" or $order eq "sort1 desc") ? "items.$order,
> date_due DESC, borrower" :
> + ($order eq "sort2" or $order eq "sort2 desc") ? "items.$order,
> date_due DESC, borrower" :
> "date_due,
> borrower" # default sort order
> );
> $template->param(sql=>$strsth);
> @@ -297,6 +331,9 @@ if ($noreport) {
> author => $data->{author},
> branchcode => $data->{branchcode},
> itemcallnumber => $data->{itemcallnumber},
> + sort1 => $data->{sort1},
> + sort2 => $data->{sort2},
> + price => $data->{price},
> replacementprice => $data->{replacementprice},
> patron_attr_value_loop => \@patron_attr_value_loop,
> };
> diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/overdue.tmpl
> b/koha-tmpl/intranet-tmpl/prog/en/modules/circ/overdue.tmpl
> index c643760..75d6698 100644
> --- a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/overdue.tmpl
> +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/circ/overdue.tmpl
> @@ -110,6 +110,9 @@
> <th>Title</th>
> <th>Barcode</th>
> <th>Call number</th>
> + <th>Price of Book</th>
> + <th>Sort1</th>
> + <th>Sort2</th>
> </tr></thead>
>
> <tbody><!-- TMPL_LOOP NAME="overdueloop" -->
> @@ -128,6 +131,9 @@
> </td>
> <td><a
> href="/cgi-bin/koha/catalogue/moredetail.pl?biblionumber=<!-- TMPL_VAR
> NAME="biblionumber" -->&itemnumber=<!-- TMPL_VAR NAME="itemnum"
> -->#item<!-- TMPL_VAR NAME="itemnum" -->"><!-- TMPL_VAR name="barcode"
> --></a></td>
> <td><!-- TMPL_VAR name="itemcallnumber" --></td>
> +<td><!-- TMPL_VAR NAME="price" --></td>
> +<td><!-- TMPL_VAR NAME="sort1" --></td>
> + <td><!-- TMPL_VAR NAME="sort2" --></td>
> </tr>
> <!-- /TMPL_LOOP --></tbody>
> </table>
> @@ -203,6 +209,19 @@
> <!-- /TMPL_LOOP -->
> </select>
> </li>
> +<tr><th scope="row"><label for="sort1">Sort1:</label></th><td><select
> name="sort1" id="sort1">
> + <option value="">Any</option>
> + <!-- TMPL_LOOP name="sort1loop" -->
> + <option value="<!-- TMPL_VAR name="value" -->"
> <!-- TMPL_IF name="selected" --> selected="selected"<!-- /TMPL_IF -->><!--
> TMPL_VAR name="name" --></option>
> + <!-- /TMPL_LOOP -->
> + </select></td></tr>
> + <tr><th scope="row"><label
> for="sort2">Sort2:</label></th><td><select name="sort2" id="sort2">
> + <option value="">Any</option>
> + <!-- TMPL_LOOP name="sort2loop" -->
> + <option value="<!-- TMPL_VAR name="value" -->"
> <!-- TMPL_IF name="selected" --> selected="selected"<!-- /TMPL_IF -->><!--
> TMPL_VAR name="name" --></option>
> + <!-- /TMPL_LOOP -->
> + </select></td></tr>
> +
>
> <li><label for="order">Sort By:</label> <select name="order"
> id="order">
> <!-- TMPL_LOOP NAME="ORDER_LOOP" -->
> --
> 1.5.6
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </pipermail/koha-patches/attachments/20090630/4feb1df6/attachment-0002.htm>
More information about the Koha-patches
mailing list