- my $strcalc ;
-
-# Processing average loanperiods
- $strcalc .= "SELECT items.barcode, biblio.title, biblio.biblionumber, biblio.author";
- $strcalc .= " , $colfield " if ($colfield);
- $strcalc .= " FROM (items
- LEFT JOIN biblioitems ON biblioitems.biblioitemnumber = items.biblioitemnumber
- LEFT JOIN biblio ON biblio.biblionumber=items.biblionumber)
- LEFT JOIN old_issues ON old_issues.itemnumber=items.itemnumber
- WHERE old_issues.itemnumber is null";
- @$filters[0]=~ s/\*/%/g if (@$filters[0]);
- $strcalc .= " AND items.homebranch like '" . @$filters[0] ."'" if ( @$filters[0] );
- @$filters[1]=~ s/\*/%/g if (@$filters[1]);
- $strcalc .= " AND biblioitems.itemtype like '" . @$filters[1] ."'" if ( @$filters[1] );
-
- $strcalc .= " group by items.itemnumber";
- $strcalc .= ", $colfield" if ($column);
- $strcalc .= " order by $colfield " if ($colfield);
- my $max = (@loopcol) ? $line*@loopcol : $line ;
- $strcalc .= " LIMIT 0,$max" if ($line);
- warn "SQL :". $strcalc;
-
- my $dbcalc = $dbh->prepare($strcalc);
- $dbcalc->execute;
-# warn "filling table";
- my $previous_col;
- $i=1;
- while (my @data = $dbcalc->fetchrow) {
- my ($barcode,$title,$biblionumber,$author, $col )=@data;
- $col = "zzEMPTY" if ($col eq undef);
- $i=1 if (($previous_col) and not($col eq $previous_col));
- $table[$i]->{$col}->{'barcode'}=$barcode;
- $table[$i]->{$col}->{'title'}=$title;
- $table[$i]->{$col}->{'biblionumber'}=$biblionumber;
- $table[$i]->{$col}->{'author'}=$author;
-# warn " ".$i." ".$col. " ".$row;
- $i++;
- $previous_col=$col;
- }
-
- push @loopcol,{coltitle => "Global"} if not($column);
-
- $max =(($line)?$line:@table);
- for ($i=1; $i<=$max;$i++) {
- my @loopcell;
- #@loopcol ensures the order for columns is common with column titles
- # and the number matches the number of columns
- my $colcount=0;
- foreach my $col ( @loopcol ) {
- my ($barcode, $author, $title, $biblionumber);
- if (@loopcol){
- $barcode =$table[$i]->{(($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))?"zzEMPTY":$col->{coltitle}}->{'barcode'};
- $title =$table[$i]->{(($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))?"zzEMPTY":$col->{coltitle}}->{'title'};
- $author =$table[$i]->{(($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))?"zzEMPTY":$col->{coltitle}}->{'author'};
- $biblionumber =$table[$i]->{(($col->{coltitle} eq "NULL") or ($col->{coltitle} eq "Global"))?"zzEMPTY":$col->{coltitle}}->{'biblionumber'};
- } else {
- $barcode =$table[$i]->{"zzEMPTY"}->{'barcode'};
- $title =$table[$i]->{"zzEMPTY"}->{'title'};
- $author =$table[$i]->{"zzEMPTY"}->{'author'};
- $biblionumber =$table[$i]->{"zzEMPTY"}->{'biblionumber'};
- }
- push @loopcell, {author=> $author, title=>$title,biblionumber=>$biblionumber,barcode=>$barcode} ;
- }
- push @looprow,{ 'rowtitle' => $i ,
- 'loopcell' => \@loopcell,
- 'hilighted' => ($hilighted >0),
- };
- $hilighted = -$hilighted;
- }
+ my @exe_args = ();
+ my $query = "
+ SELECT items.barcode as barcode,
+ items.homebranch as branch,
+ items.itemcallnumber as itemcallnumber,
+ biblio.title as title,
+ biblio.biblionumber as biblionumber,
+ biblio.author as author";
+ ($column) and $query .= ",\n$column as col ";
+ $query .= "
+ FROM items
+ LEFT JOIN biblio USING (biblionumber)
+ LEFT JOIN issues USING (itemnumber)
+ LEFT JOIN old_issues USING (itemnumber)
+ WHERE issues.itemnumber IS NULL
+ AND old_issues.itemnumber IS NULL
+ ";
+ if ($filters->[0]) {
+ $filters->[0]=~ s/\*/%/g;
+ push @exe_args, $filters->[0];
+ $query .= " AND items.homebranch LIKE ?";
+ }
+ if ($filters->[1]) {
+ $filters->[1]=~ s/\*/%/g;
+ push @exe_args, $filters->[1];
+ $query .= " AND items.itype LIKE ?";
+ }
+ if ($column) {
+ $query .= " AND $column = ? GROUP BY items.itemnumber, $column "; # placeholder handled below
+ } else {
+ $query .= " GROUP BY items.itemnumber ";
+ }
+ $query .= " ORDER BY items.itemcallnumber DESC, barcode";
+ $query .= " LIMIT 0,$limit" if ($limit);
+ $debug and warn "SQL : $query";
+ # warn "SQL : $query";
+ push @loopfilter, {crit=>'SQL', sql=>1, filter=>$query};
+ my $dbcalc = $dbh->prepare($query);
+
+ if ($column) {
+ foreach (sort keys %columns) {
+ my (@more_exe_args) = @exe_args; # execute(@exe_args,$_) would fail when the array is empty.
+ push @more_exe_args, $_; # but @more_exe_args will work
+ $dbcalc->execute(@more_exe_args) or die "Query execute(@more_exe_args) failed: $query";
+ while (my $data = $dbcalc->fetchrow_hashref) {
+ my $col = $data->{col} || 'NULL';
+ $tables{$col} or $tables{$col} = [];
+ push @{$tables{$col}}, $data;
+ }
+ }
+ } else {
+ (scalar @exe_args) ? $dbcalc->execute(@exe_args) : $dbcalc->execute;
+ while (my $data = $dbcalc->fetchrow_hashref) {
+ my $col = $data->{col} || 'NULL';
+ $tables{$col} or $tables{$col} = [];
+ push @{$tables{$col}}, $data;
+ }
+ }