Bugzilla – Attachment 12945 Details for
Bug 7896
Acquisition statistics wizard: add filters and cell values
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
[SIGNED-OFF] Bug 7896: Acq statistics wizard: add filters and cell values
Bug-7896-Acq-statistics-wizard-add-filters-and-cel.patch (text/plain), 28.12 KB, created by
Kyle M Hall (khall)
on 2012-10-19 13:26:48 UTC
(
hide
)
Description:
[SIGNED-OFF] Bug 7896: Acq statistics wizard: add filters and cell values
Filename:
MIME Type:
Creator:
Kyle M Hall (khall)
Created:
2012-10-19 13:26:48 UTC
Size:
28.12 KB
patch
obsolete
>From 51fb6fb1e6496f765c924fc91e7e7e0dbc65454d Mon Sep 17 00:00:00 2001 >From: Julian Maurice <julian.maurice@biblibre.com> >Date: Tue, 3 Apr 2012 17:30:36 +0200 >Subject: [PATCH] Bug 7896: Acq statistics wizard: add filters and cell values >Content-Type: text/plain; charset="utf-8" > >- Possibility to select for line and column: items.homebranch and > items.ccode >- Possibility to filter on these fields >- Possibility to count unique biblios (count(distinct biblionumber)), > ordered amount and spent amount (based on aqorders.datereceived) > >Filtering on item homebranch and ccode works only on items that were >created at ordering or receiving (ie items are linked to an order) > >Some refactoring is done, mainly replacing switch-like if statements by >given/when > >Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> >--- > C4/Biblio.pm | 33 ++ > .../prog/en/modules/reports/acquisitions_stats.tt | 61 +++- > reports/acquisitions_stats.pl | 424 ++++++++++---------- > 3 files changed, 300 insertions(+), 218 deletions(-) > >diff --git a/C4/Biblio.pm b/C4/Biblio.pm >index 231baf3..40b486d 100644 >--- a/C4/Biblio.pm >+++ b/C4/Biblio.pm >@@ -90,6 +90,7 @@ BEGIN { > &GetAuthorisedValueDesc > &GetMarcStructure > &GetMarcFromKohaField >+ &GetMarcSubfieldStructureFromKohaField > &GetFrameworkCode > &TransformKohaToMarc > &PrepHostMarcField >@@ -1177,6 +1178,38 @@ sub GetMarcFromKohaField { > return (0, undef); > } > >+=head2 GetMarcSubfieldStructureFromKohaField >+ >+ my $subfield_structure = &GetMarcSubfieldStructureFromKohaField($kohafield, $frameworkcode); >+ >+Returns a hashref where keys are marc_subfield_structure column names for the >+row where kohafield=$kohafield for the given framework code. >+ >+$frameworkcode is optional. If not given, then the default framework is used. >+ >+=cut >+ >+sub GetMarcSubfieldStructureFromKohaField { >+ my ($kohafield, $frameworkcode) = @_; >+ >+ return undef unless $kohafield; >+ $frameworkcode //= ''; >+ >+ my $dbh = C4::Context->dbh; >+ my $query = qq{ >+ SELECT * >+ FROM marc_subfield_structure >+ WHERE kohafield = ? >+ AND frameworkcode = ? >+ }; >+ my $sth = $dbh->prepare($query); >+ $sth->execute($kohafield, $frameworkcode); >+ my $result = $sth->fetchrow_hashref; >+ $sth->finish; >+ >+ return $result; >+} >+ > =head2 GetMarcBiblio > > my $record = GetMarcBiblio($biblionumber, [$embeditems]); >diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/acquisitions_stats.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/acquisitions_stats.tt >index 802a82e..34cfbc3 100644 >--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/acquisitions_stats.tt >+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/acquisitions_stats.tt >@@ -51,7 +51,7 @@ > [% IF ( do_it ) %] > [% FOREACH mainloo IN mainloop %] > <h1>Acquisitions statistics </h1> >- [% IF ( mainloo.loopfilter ) %] >+ [% IF ( mainloo.loopfilter.size ) %] > <p><b>Filtered on:</b></p> > [% FOREACH loopfilte IN mainloo.loopfilter %] > <p>[% loopfilte.crit %]: [% loopfilte.filter %]</p> >@@ -157,6 +157,42 @@ > <td><input type="radio" name="Column" value="aqbooksellers.name" /></td> > <td>[% CGIBookSeller %]</td> > </tr> >+ <tr> >+ <td>Home branch</td> >+ <td><input type="radio" name="Line" value="items.homebranch" /></td> >+ <td><input type="radio" name="Column" value="items.homebranch" /></td> >+ <td> >+ <select name="Filter"> >+ <option value="" selected="selected">All branches</option> >+ [% FOREACH branch IN branches %] >+ <option value="[% branch.branchcode %]">[% branch.branchname %]</option> >+ [% END %] >+ </select> >+ </td> >+ </tr> >+ <tr> >+ <td> >+ [% IF ccode_label %] >+ [% ccode_label %] >+ [% ELSE %] >+ Collection >+ [% END %] >+ </td> >+ <td><input type="radio" name="Line" value="items.ccode" /></td> >+ <td><input type="radio" name="Column" value="items.ccode" /></td> >+ <td> >+ [% IF ccode_avlist.size %] >+ <select name="Filter"> >+ <option value="" selected="selected">All</option> >+ [% FOREACH ccode_av IN ccode_avlist %] >+ <option value="[% ccode_av.value %]">[% ccode_av.lib %]</option> >+ [% END %] >+ </select> >+ [% ELSE %] >+ <input type="text" name="Filter" /> >+ [% END %] >+ </td> >+ </tr> > <tr> > <td>Item Type</td> > <td><input type="radio" name="Line" value="biblioitems.itemtype" /></td> >@@ -191,9 +227,26 @@ > </table><br /> > </fieldset> > >-<fieldset class="rows"> <legend> Cell value </legend> >- <ol><li><label for="cellvalue1">Count items</label><input type="radio" name="Cellvalue" id="cellvalue1" value="1" checked="checked" /> </li> >- <li><label for="cellvalue2">Amount</label><input type="radio" name="Cellvalue" id="cellvalue2" value="2" /> </li></ol></fieldset> >+<fieldset class="rows"> >+ <legend> Cell value </legend> >+ <ol> >+ <li> >+ <label for="cellvalue1">Count items</label><input type="radio" name="Cellvalue" id="cellvalue1" value="1" checked="checked" /> >+ </li> >+ <li> >+ <label for="cellvalue2">Count unique biblios</label><input type="radio" name="Cellvalue" id="cellvalue2" value="2" /> >+ </li> >+ <li> >+ <label for="cellvalue3">Amount</label><input type="radio" name="Cellvalue" id="cellvalue3" value="3" /> >+ </li> >+ <li> >+ <label for="cellvalue4">Ordered amount</label><input type="radio" name="Cellvalue" id="cellvalue4" value="4" /> >+ </li> >+ <li> >+ <label for="cellvalue5">Spent amount</label><input type="radio" name="Cellvalue" id="cellvalue5" value="5" /> >+ </li> >+ </ol> >+</fieldset> > > <fieldset class="rows"> > <legend>Output</legend> >diff --git a/reports/acquisitions_stats.pl b/reports/acquisitions_stats.pl >index 7207e64..9a74365 100755 >--- a/reports/acquisitions_stats.pl >+++ b/reports/acquisitions_stats.pl >@@ -20,8 +20,8 @@ > > # test comment > >-use strict; >-#use warnings; FIXME - Bug 2505 >+use Modern::Perl; >+ > use C4::Auth; > use CGI; > use C4::Context; >@@ -30,6 +30,8 @@ use C4::Output; > use C4::Koha; > use C4::Circulation; > use C4::Dates qw/format_date format_date_in_iso/; >+use C4::Branch; >+use C4::Biblio; > > =head1 NAME > >@@ -53,27 +55,30 @@ $filters[2] = format_date_in_iso( $filters[2] ); > $filters[3] = format_date_in_iso( $filters[3] ); > my $podsp = $input->param("PlacedOnDisplay"); > my $rodsp = $input->param("ReceivedOnDisplay"); >-my $aodsp = $input->param("AcquiredOnDisplay"); ##added by mason. > my $calc = $input->param("Cellvalue"); > my $output = $input->param("output"); > my $basename = $input->param("basename"); > >-my ($template, $borrowernumber, $cookie) >- = get_template_and_user({template_name => $fullreportname, >- query => $input, >- type => "intranet", >- authnotrequired => 0, >- flagsrequired => {reports => '*'}, >- debug => 1, >- }); >-our $sep = $input->param("sep"); >+my ( $template, $borrowernumber, $cookie ) = get_template_and_user( >+ { >+ template_name => $fullreportname, >+ query => $input, >+ type => "intranet", >+ authnotrequired => 0, >+ flagsrequired => { reports => '*' }, >+ debug => 1, >+ } >+); >+ >+our $sep = $input->param("sep") // ''; > $sep = "\t" if ($sep eq 'tabulation'); >-$template->param(do_it => $do_it, >- DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), >- ); >+$template->param( >+ do_it => $do_it, >+ DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(), >+); > if ($do_it) { > my $results = >- calculate( $line, $column, $podsp, $rodsp, $aodsp, $calc, \@filters ); >+ calculate( $line, $column, $podsp, $rodsp, $calc, \@filters ); > if ( $output eq "screen" ) { > $template->param( mainloop => $results ); > output_html_with_http_headers $input, $cookie, $template->output; >@@ -117,9 +122,8 @@ else { > my $req; > $req = $dbh->prepare("SELECT distinctrow id,name FROM aqbooksellers ORDER BY name"); > $req->execute; >- my @select; > push @select, ""; >- $select{''} = "All Suppliers"; >+ $select{''} = "All Suppliers"; > while ( my ( $value, $desc ) = $req->fetchrow ) { > push @select, $desc; > $select{$value}=$desc; >@@ -183,11 +187,11 @@ else { > $select{''} = "All"; > my $hassort1; > while ( my ($value) = $req->fetchrow ) { >- if ($value) { >- $hassort1 = 1; >- push @select, $value; >- $select{$value} = $value; >- } >+ if ($value) { >+ $hassort1 = 1; >+ push @select, $value; >+ $select{$value} = $value; >+ } > } > my $CGISort1 = CGI::scrolling_list( > -name => 'Filter', >@@ -211,12 +215,12 @@ else { > my $hglghtsort2; > > while ( my ($value) = $req->fetchrow ) { >- if ($value) { >- $hassort2 = 1; >- $hglghtsort2 = !($hassort1); >- push @select, $value; >- $select{$value} = $value; >- } >+ if ($value) { >+ $hassort2 = 1; >+ $hglghtsort2 = !($hassort1); >+ push @select, $value; >+ $select{$value} = $value; >+ } > } > my $CGISort2 = CGI::scrolling_list( > -name => 'Filter', >@@ -237,25 +241,40 @@ else { > > my $CGIsepChoice = GetDelimiterChoices; > >+ my $branches = GetBranches; >+ my @branches; >+ foreach ( sort keys %$branches ) { >+ push @branches, $branches->{$_}; >+ } >+ >+ my $ccode_subfield_structure = GetMarcSubfieldStructureFromKohaField('items.ccode', ''); >+ my $ccode_label; >+ my $ccode_avlist; >+ if($ccode_subfield_structure) { >+ $ccode_label = $ccode_subfield_structure->{liblibrarian}; >+ $ccode_avlist = GetAuthorisedValues($ccode_subfield_structure->{authorised_value}); >+ } >+ > $template->param( > CGIBookSeller => $CGIBookSellers, > CGIItemType => $CGIItemTypes, > CGIBudget => $CGIBudget, > hassort1 => $hassort1, > hassort2 => $hassort2, >- HlghtSort2 => $hglghtsort2, > CGISort1 => $CGISort1, > CGISort2 => $CGISort2, > CGIextChoice => $CGIextChoice, > CGIsepChoice => $CGIsepChoice, >- date_today => C4::Dates->new()->output() >+ branches => \@branches, >+ ccode_label => $ccode_label, >+ ccode_avlist => $ccode_avlist, > ); > > } > output_html_with_http_headers $input, $cookie, $template->output; > > sub calculate { >- my ( $line, $column, $podsp, $rodsp, $aodsp, $process, $filters ) = @_; >+ my ( $line, $column, $podsp, $rodsp, $process, $filters ) = @_; > my @mainloop; > my @loopfooter; > my @loopcol; >@@ -264,6 +283,9 @@ sub calculate { > my %globalline; > my $grantotal = 0; > >+ $podsp ||= 0; >+ $rodsp ||= 0; >+ > # extract parameters > my $dbh = C4::Context->dbh; > >@@ -271,109 +293,106 @@ sub calculate { > # Checking filters > # > my @loopfilter; >- for ( my $i = 0 ; $i <= 8 ; $i++ ) { >- my %cell; >- if ( @$filters[$i] ) { >+ for ( my $i = 0 ; $i <= @$filters ; $i++ ) { >+ if( defined @$filters[$i] and @$filters[$i] ne '' ) { >+ my %cell; > if ( ( ( $i == 1 ) or ( $i == 3 ) ) and ( @$filters[ $i - 1 ] ) ) { >- $cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] ); >+ $cell{err} = 1 if ( @$filters[$i] lt @$filters[ $i - 1 ] ); > } > # format the dates filters, otherwise just fill as is >- if ($i>=4) { >- $cell{filter} .= @$filters[$i]; >+ if ($i >= 4) { >+ $cell{filter} = @$filters[$i]; > } else { >- $cell{filter} .= format_date(@$filters[$i]); >+ $cell{filter} = format_date(@$filters[$i]); >+ } >+ given ($i) { >+ when (0) { $cell{crit} = "Placed On From" } >+ when (1) { $cell{crit} = "Placed On To" } >+ when (2) { $cell{crit} = "Received On From" } >+ when (3) { $cell{crit} = "Received On To" } >+ when (4) { $cell{crit} = "Bookseller" } >+ when (5) { $cell{crit} = "Home branch" } >+ when (6) { $cell{crit} = "Collection" } >+ when (7) { $cell{crit} = "Doc Type" } >+ when (8) { $cell{crit} = "Budget" } >+ when (9) { $cell{crit} = "Sort1" } >+ when (10) { $cell{crit} = "Sort2" } >+ default { $cell{crit} = "" } > } >- $cell{crit} .= "Placed On From" if ( $i == 0 ); >- $cell{crit} .= "Placed On To" if ( $i == 1 ); >- $cell{crit} .= "Received On From" if ( $i == 2 ); >- $cell{crit} .= "Received On To" if ( $i == 3 ); >- >-# $cell{crit} .= "Acquired On From" if ( $i == 4 ); >-# $cell{crit} .= "Acquired On To" if ( $i == 5 ); >- >- $cell{crit} .= "BookSeller" if ( $i == 4 ); >- $cell{crit} .= "Doc Type" if ( $i == 5 ); >- $cell{crit} .= "Budget" if ( $i == 6 ); >- $cell{crit} .= "Sort1" if ( $i == 7 ); >- $cell{crit} .= "Sort2" if ( $i == 8 ); > push @loopfilter, \%cell; > } > } > >- my @linefilter; >- >- $linefilter[0] = @$filters[0] if ( $line =~ /closedate/ ); >- $linefilter[1] = @$filters[1] if ( $line =~ /closedate/ ); >- $linefilter[0] = @$filters[2] if ( $line =~ /received/ ); >- $linefilter[1] = @$filters[3] if ( $line =~ /received/ ); >- >- $linefilter[0] = @$filters[4] if ( $line =~ /bookseller/ ); >- $linefilter[0] = @$filters[5] if ( $line =~ /itemtype/ ); >- $linefilter[0] = @$filters[6] if ( $line =~ /budget/ ); >- $linefilter[0] = @$filters[7] if ( $line =~ /sort1/ ); >- $linefilter[0] = @$filters[8] if ( $line =~ /sort2/ ); >- >- my @colfilter; >- $colfilter[0] = @$filters[0] if ( $column =~ /closedate/ ); >- $colfilter[1] = @$filters[1] if ( $column =~ /closedate/ ); >- $colfilter[0] = @$filters[2] if ( $column =~ /received/ ); >- $colfilter[1] = @$filters[3] if ( $column =~ /received/ ); >- >- $colfilter[0] = @$filters[4] if ( $column =~ /bookseller/ ); >- $colfilter[0] = @$filters[5] if ( $column =~ /itemtype/ ); >- $colfilter[0] = @$filters[6] if ( $column =~ /budget/ ); >- $colfilter[0] = @$filters[7] if ( $column =~ /sort1/ ); >- $colfilter[0] = @$filters[8] if ( $column =~ /sort2/ ); >- >- # 1st, loop rows. >- my $linefield; >- if ( ( $line =~ /closedate/ ) and ( $podsp == 1 ) ) { >- >- #Display by day >- $linefield .= "concat(hex(weekday($line)+1),'-',dayname($line))"; >- } >- elsif ( ( $line =~ /closedate/ ) and ( $podsp == 2 ) ) { >- >- #Display by Month >- $linefield .= "concat(hex(month($line)),'-',monthname($line))"; >- } >- elsif ( ( $line =~ /closedate/ ) and ( $podsp == 3 ) ) { >- >- #Display by Year >- $linefield .= "Year($line)"; >- >- } >- elsif ( ( $line =~ /received/ ) and ( $rodsp == 1 ) ) { >- >- #Display by day >- $linefield .= "concat(hex(weekday($line)+1),'-',dayname($line))"; >- } >- elsif ( ( $line =~ /received/ ) and ( $rodsp == 2 ) ) { >- >- #Display by Month >- $linefield .= "concat(hex(month($line)),'-',monthname($line))"; >+ my %filter; >+ my %field; >+ foreach ($line, $column) { >+ $filter{$_} = []; >+ $field{$_} = $_; >+ given ($_) { >+ when (/closedate/) { >+ $filter{$_}->[0] = @$filters[0]; >+ $filter{$_}->[1] = @$filters[1]; >+ my $a = $_; >+ given ($podsp) { >+ when (1) { $field{$a} = "concat(hex(weekday($a)+1),'-',dayname($a))" } >+ when (2) { $field{$a} = "concat(hex(month($a)),'-',monthname($a))" } >+ when (3) { $field{$a} = "Year($a)" } >+ default { $field{$a} = $a } >+ } >+ } >+ when (/received/) { >+ $filter{$_}->[0] = @$filters[2]; >+ $filter{$_}->[1] = @$filters[3]; >+ my $a = $_; >+ given ($rodsp) { >+ when (1) { $field{$a} = "concat(hex(weekday($a)+1),'-',dayname($a))" } >+ when (2) { $field{$a} = "concat(hex(month($a)),'-',monthname($a))" } >+ when (3) { $field{$a} = "Year($a)" } >+ default { $field{$a} = $a } >+ } >+ } >+ when (/bookseller/) { >+ $filter{$_}->[0] = @$filters[4]; >+ } >+ when (/homebranch/) { >+ $filter{$_}->[0] = @$filters[5]; >+ } >+ when (/ccode/) { >+ $filter{$_}->[0] = @$filters[6]; >+ } >+ when (/itemtype/) { >+ $filter{$_}->[0] = @$filters[7]; >+ } >+ when (/budget/) { >+ $filter{$_}->[0] = @$filters[8]; >+ } >+ when (/sort1/) { >+ $filter{$_}->[0] = @$filters[9]; >+ } >+ when (/sort2/) { >+ $filter{$_}->[0] = @$filters[10]; >+ } >+ } > } >- elsif ( ( $line =~ /received/ ) and ( $rodsp == 3 ) ) { > >- #Display by Year >- $linefield .= "Year($line)"; >+ my @linefilter = @{ $filter{$line} }; >+ my $linefield = $field{$line}; >+ my @colfilter = @{ $filter{$column} }; >+ my $colfield = $field{$column}; > >- } >- else { >- $linefield .= $line; >- } >- >- my $strsth; >- $strsth .= >- "SELECT DISTINCTROW $linefield FROM (aqorders, aqbasket ) >- LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber) >- LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber) >- LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id ) >- >- LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) WHERE (aqorders.basketno=aqbasket.basketno) >- AND $line IS NOT NULL AND $line <> '' "; >- >- if (@linefilter) { >+ # 1st, loop rows. >+ my $strsth = " >+ SELECT DISTINCTROW $linefield >+ FROM aqorders >+ LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno) >+ LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber) >+ LEFT JOIN items ON (aqorders_items.itemnumber = items.itemnumber) >+ LEFT JOIN biblioitems ON (aqorders.biblionumber = biblioitems.biblionumber) >+ LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id ) >+ LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id) >+ WHERE $line IS NOT NULL AND $line <> '' "; >+ >+ if (@linefilter) { > if ( $linefilter[1] ) { > if ( $linefilter[0] ) { > $strsth .= " AND $line BETWEEN ? AND ? "; >@@ -408,62 +427,27 @@ sub calculate { > else { > $sth->execute; > } >- while ( my ($celvalue) = $sth->fetchrow ) { >- my %cell; >- if ($celvalue) { >- $cell{rowtitle} = $celvalue; >- push @loopline, \%cell; >- } >- $cell{totalrow} = 0; >- } >- # 2nd, loop cols. >- my $colfield; >- if ( ( $column =~ /closedate/ ) and ( $podsp == 1 ) ) { >- >- #Display by day >- $colfield .= "concat(hex(weekday($column)+1),'-',dayname($column))"; >- } >- elsif ( ( $column =~ /closedate/ ) and ( $podsp == 2 ) ) { >- >- #Display by Month >- $colfield .= "concat(hex(month($column)),'-',monthname($column))"; >- } >- elsif ( ( $column =~ /closedate/ ) and ( $podsp == 3 ) ) { >- >- #Display by Year >- $colfield .= "Year($column)"; >- >- } >- elsif ( ( $column =~ /received/ ) and ( $rodsp == 1 ) ) { >- >- #Display by day >- $colfield .= "concat(hex(weekday($column)+1),'-',dayname($column))"; >- } >- elsif ( ( $column =~ /received/ ) and ( $rodsp == 2 ) ) { >- >- #Display by Month >- $colfield .= "concat(hex(month($column)),'-',monthname($column))"; >- } >- elsif ( ( $column =~ /received/ ) and ( $rodsp == 3 ) ) { >- >- #Display by Year >- $colfield .= "Year($column)"; >- >- } >- else { >- $colfield .= $column; >+ while ( my ($celvalue) = $sth->fetchrow ) { >+ my %cell; >+ if ($celvalue) { >+ $cell{rowtitle} = $celvalue; >+ push @loopline, \%cell; >+ } >+ $cell{totalrow} = 0; > } > >- my $strsth2; >- $strsth2 .= >- "SELECT distinctrow $colfield FROM (aqorders, aqbasket ) >- LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber) >- LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber) >- LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id ) >- >- LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) >- WHERE (aqorders.basketno=aqbasket.basketno) AND >- $column IS NOT NULL AND $column <> '' "; >+ # 2nd, loop cols. >+ my $strsth2 = " >+ SELECT DISTINCTROW $colfield >+ FROM aqorders >+ LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno) >+ LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber) >+ LEFT JOIN items ON (aqorders_items.itemnumber = items.itemnumber) >+ LEFT JOIN biblioitems ON (aqorders.biblionumber = biblioitems.biblionumber) >+ LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id ) >+ LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id) >+ WHERE $column IS NOT NULL AND $column <> '' >+ "; > > if (@colfilter) { > if ( $colfilter[1] ) { >@@ -488,7 +472,6 @@ sub calculate { > } > } > >- > $strsth2 .= " GROUP BY $colfield"; > $strsth2 .= " ORDER BY $colfield"; > >@@ -503,13 +486,13 @@ sub calculate { > else { > $sth2->execute; > } >- while ( my $celvalue = $sth2->fetchrow ) { >- my %cell; >- if ($celvalue) { >- $cell{coltitle} = $celvalue; >- push @loopcol, \%cell; >- } >- } >+ while ( my $celvalue = $sth2->fetchrow ) { >+ my %cell; >+ if ($celvalue) { >+ $cell{coltitle} = $celvalue; >+ push @loopcol, \%cell; >+ } >+ } > > my $i = 0; > my @totalcol; >@@ -528,18 +511,26 @@ sub calculate { > # preparing calculation > my $strcalc; > $strcalc .= "SELECT $linefield, $colfield, "; >- $strcalc .= "SUM( aqorders.quantity ) " if ( $process == 1 ); >- $strcalc .= "SUM( aqorders.quantity * aqorders.listprice ) " >- if ( $process == 2 ); >- $strcalc .= "FROM (aqorders, aqbasket ) >- LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber) >- LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber) >- LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id ) >- >- LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) >- WHERE (aqorders.basketno=aqbasket.basketno) "; >- >- @$filters[0] =~ s/\*/%/g if ( @$filters[0] ); >+ given ($process) { >+ when (1) { $strcalc .= "COUNT(*) " } >+ when (2) { $strcalc .= "COUNT(DISTINCT(aqorders.biblionumber)) " } >+ when ([3,4,5]) { $strcalc .= "SUM(aqorders.listprice) " } >+ default { $strcalc .= "NULL " } >+ } >+ $strcalc .= " >+ FROM aqorders >+ LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno) >+ LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber) >+ LEFT JOIN items ON (aqorders_items.itemnumber = items.itemnumber) >+ LEFT JOIN biblioitems ON (aqorders.biblionumber = biblioitems.biblionumber) >+ LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id ) >+ LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id) >+ WHERE aqorders.datecancellationprinted IS NULL "; >+ $strcalc .= " AND (aqorders.datereceived IS NULL OR aqorders.datereceived = '') " >+ if ( $process == 4 ); >+ $strcalc .= " AND aqorders.datereceived IS NOT NULL AND aqorders.datereceived <> '' " >+ if ( $process == 5 ); >+ @$filters[0] =~ s/\*/%/g if ( @$filters[0] ); > $strcalc .= " AND aqbasket.closedate >= '" . @$filters[0] . "'" > if ( @$filters[0] ); > @$filters[1] =~ s/\*/%/g if ( @$filters[1] ); >@@ -554,20 +545,23 @@ sub calculate { > @$filters[4] =~ s/\*/%/g if ( @$filters[4] ); > $strcalc .= " AND aqbooksellers.name LIKE '" . @$filters[4] . "'" > if ( @$filters[4] ); >- @$filters[5] =~ s/\*/%/g if ( @$filters[5] ); >- $strcalc .= " AND biblioitems.itemtype LIKE '" . @$filters[5] . "'" >+ $strcalc .= " AND items.homebranch = '" . @$filters[5] . "'" > if ( @$filters[5] ); > @$filters[6] =~ s/\*/%/g if ( @$filters[6] ); >- $strcalc .= " AND aqbudgets.budget_code LIKE '" . @$filters[6] . "'" >+ $strcalc .= " AND items.ccode = '" . @$filters[6] . "'" > if ( @$filters[6] ); > @$filters[7] =~ s/\*/%/g if ( @$filters[7] ); >- $strcalc .= " AND aqorders.sort1 LIKE '" . @$filters[7] . "'" >+ $strcalc .= " AND biblioitems.itemtype LIKE '" . @$filters[7] . "'" > if ( @$filters[7] ); > @$filters[8] =~ s/\*/%/g if ( @$filters[8] ); >- $strcalc .= " AND aqorders.sort2 LIKE '" . @$filters[8] . "'" >+ $strcalc .= " AND aqbudgets.budget_code LIKE '" . @$filters[8] . "'" > if ( @$filters[8] ); >- >- $strcalc .= " AND aqorders.datecancellationprinted is NULL "; >+ @$filters[9] =~ s/\*/%/g if ( @$filters[9] ); >+ $strcalc .= " AND aqorders.sort1 LIKE '" . @$filters[9] . "'" >+ if ( @$filters[9] ); >+ @$filters[10] =~ s/\*/%/g if ( @$filters[10] ); >+ $strcalc .= " AND aqorders.sort2 LIKE '" . @$filters[10] . "'" >+ if ( @$filters[10] ); > > $strcalc .= " GROUP BY $linefield, $colfield ORDER BY $linefield,$colfield"; > my $dbcalc = $dbh->prepare($strcalc); >@@ -575,8 +569,6 @@ sub calculate { > > my $emptycol; > while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) { >- next if ($row eq undef || $col eq undef); >- > $emptycol = 1 if ( !defined($col) ); > $col = "zzEMPTY" if ( !defined($col) ); > $row = "zzEMPTY" if ( !defined($row) ); >@@ -594,15 +586,17 @@ sub calculate { > # and the number matches the number of columns > foreach my $col (@loopcol) { > my $value = $table{$row}->{ ( $col->{coltitle} eq "NULL" ) ? "zzEMPTY" : $col->{coltitle} }; >+ $value = sprintf("%.2f", $value) if($value and grep /$process/, (3,4,5)); > push @loopcell, { value => $value }; > } >- push @looprow, >- { >- 'rowtitle' => ( $row eq "zzEMPTY" ) ? "NULL" : $row, >- 'loopcell' => \@loopcell, >- 'hilighted' => ( $hilighted > 0 ), >- 'totalrow' => $table{$row}->{totalrow} >- }; >+ my $r = { >+ rowtitle => ( $row eq "zzEMPTY" ) ? "NULL" : $row, >+ loopcell => \@loopcell, >+ hilighted => ( $hilighted > 0 ), >+ totalrow => $table{$row}->{totalrow} >+ }; >+ $r->{totalrow} = sprintf("%.2f", $r->{totalrow}) if($r->{totalrow} and grep /$process/, (3,4,5)); >+ push @looprow, $r; > $hilighted = -$hilighted; > } > >@@ -617,17 +611,19 @@ sub calculate { > : $col->{coltitle} > }; > } >+ $total = sprintf("%.2f", $total) if($total and grep /$process/, (3,4,5)); > > push @loopfooter, { 'totalcol' => $total }; > } > > # the header of the table >- $globalline{loopfilter}=\@loopfilter; >+ $globalline{loopfilter} = \@loopfilter; > # the core of the table > $globalline{looprow} = \@looprow; > $globalline{loopcol} = \@loopcol; > > # # the foot (totals by borrower type) >+ $grantotal = sprintf("%.2f", $grantotal) if ($grantotal and grep /$process/, (3,4,5)); > $globalline{loopfooter} = \@loopfooter; > $globalline{total} = $grantotal; > $globalline{line} = $line; >-- >1.7.2.5
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 7896
:
8863
|
8865
|
11103
|
12945
|
12946
|
13818
|
13819