View | Details | Raw Unified | Return to bug 10718
Collapse All | Expand All

(-)a/reports/catalogue_out.pl (-134 / +150 lines)
Lines 24-32 use CGI; Link Here
24
use C4::Auth;
24
use C4::Auth;
25
use C4::Context;
25
use C4::Context;
26
use C4::Debug;
26
use C4::Debug;
27
use C4::Branch; # GetBranchesLoop
27
use C4::Branch;    # GetBranchesLoop
28
use C4::Output;
28
use C4::Output;
29
use C4::Koha;   # GetItemTypes
29
use C4::Koha;      # GetItemTypes
30
# use Date::Manip;  # TODO: add not borrowed since date X criteria
30
# use Date::Manip;  # TODO: add not borrowed since date X criteria
31
use Data::Dumper;
31
use Data::Dumper;
32
32
Lines 36-143 Report that shows unborrowed items. Link Here
36
36
37
=cut
37
=cut
38
38
39
my $input = new CGI;
39
my $input   = new CGI;
40
my $do_it    = $input->param('do_it');
40
my $do_it   = $input->param('do_it');
41
my $limit    = $input->param("Limit") || 10;
41
my $limit   = $input->param("Limit") || 10;
42
my $column   = $input->param("Criteria");
42
my $column  = $input->param("Criteria");
43
my @filters  = $input->param("Filter");
43
my @filters = $input->param("Filter");
44
44
45
my ($template, $borrowernumber, $cookie) = get_template_and_user({
45
my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
46
    template_name => "reports/catalogue_out.tmpl",
46
    {
47
    query => $input,
47
        template_name   => "reports/catalogue_out.tmpl",
48
    type => "intranet",
48
        query           => $input,
49
    authnotrequired => 0,
49
        type            => "intranet",
50
    flagsrequired => {reports => 'execute_reports'},
50
        authnotrequired => 0,
51
    debug => 1,
51
        flagsrequired   => { reports => 'execute_reports' },
52
});
52
        debug           => 1,
53
53
    }
54
$template->param(do_it => $do_it);
54
);
55
56
$template->param( do_it => $do_it );
55
if ($do_it) {
57
if ($do_it) {
56
    my $results = calculate($limit, $column, \@filters);
58
    my $results = calculate( $limit, $column, \@filters );
57
    $template->param(mainloop => $results);
59
    $template->param( mainloop => $results );
58
    output_html_with_http_headers $input, $cookie, $template->output;
60
    output_html_with_http_headers $input, $cookie, $template->output;
59
    exit; # in either case, exit after do_it
61
    exit;    # in either case, exit after do_it
60
}
62
}
61
63
62
# Displaying choices (i.e., not do_it)
64
# Displaying choices (i.e., not do_it)
63
64
my $itemtypes = GetItemTypes();
65
my $itemtypes = GetItemTypes();
65
my @itemtypeloop;
66
my @itemtypeloop;
66
foreach (sort {$itemtypes->{$a}->{description} cmp $itemtypes->{$b}->{description}} keys %$itemtypes) {
67
foreach (
67
	push @itemtypeloop, {
68
    sort { $itemtypes->{$a}->{'description'} cmp $itemtypes->{$b}->{'description'} }
68
		value => $_,
69
    keys %$itemtypes
69
		description => $itemtypes->{$_}->{'description'},
70
  )
70
   };
71
{
72
    push @itemtypeloop,
73
      {
74
        value       => $_,
75
        description => $itemtypes->{$_}->{'description'},
76
      };
71
}
77
}
72
78
73
$template->param(
79
$template->param(
74
	itemtypeloop => \@itemtypeloop,
80
    itemtypeloop => \@itemtypeloop,
75
	branchloop   => GetBranchesLoop(),
81
    branchloop   => GetBranchesLoop(),
76
);
82
);
77
output_html_with_http_headers $input, $cookie, $template->output;
83
output_html_with_http_headers $input, $cookie, $template->output;
78
84
79
80
sub calculate {
85
sub calculate {
81
    my ($limit, $column, $filters) = @_;
86
    my ( $limit, $column, $filters ) = @_;
82
    my @loopline;
87
    my @loopline;
83
    my @looprow;
88
    my @looprow;
84
    my %globalline;
89
    my %globalline;
85
	my %columns = ();
90
    my %columns = ();
86
    my $dbh = C4::Context->dbh;
91
    my $dbh     = C4::Context->dbh;
87
92
88
# Filters
93
    # Filters
89
# Checking filters
94
    # Checking filters
90
#
95
    #
91
    my @loopfilter;
96
    my @loopfilter;
92
    for (my $i=0;$i<=6;$i++) {
97
    for ( my $i = 0 ; $i <= 6 ; $i++ ) {
93
        if ( @$filters[$i] ) {
98
        if ( @$filters[$i] ) {
94
        	my %cell = (filter=>@$filters[$i]);
99
            my %cell = ( filter => @$filters[$i] );
95
            if (($i==1) and (@$filters[$i-1])) {
100
            if ( ( $i == 1 ) and ( @$filters[ $i - 1 ] ) ) {
96
                $cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ;
101
                $cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] );
97
            }
102
            }
98
            $cell{crit} = "Branch"   if ($i==0);
103
            $cell{crit} = "Branch"   if ( $i == 0 );
99
            $cell{crit} = "Doc Type" if ($i==1);
104
            $cell{crit} = "Doc Type" if ( $i == 1 );
100
            push @loopfilter, \%cell;
105
            push @loopfilter, \%cell;
101
        }
106
        }
102
    }
107
    }
103
	push @loopfilter, {crit=>'limit', filter=>$limit} if ($limit);
108
    push @loopfilter, { crit => 'limit', filter => $limit } if ($limit);
104
    if ($column){
109
    if ($column) {
105
		push @loopfilter, {crit=>'by', filter=>$column};
110
        push @loopfilter, { crit => 'by', filter => $column };
106
		my $tablename = ($column =~ /branchcode/) ? 'branches' : 'items';
111
        my $tablename = ( $column =~ /branchcode/ ) ? 'branches' : 'items';
107
		$column = ($column =~ /branchcode/ or $column =~ /itype/) ? "$tablename.$column" : $column;
112
        $column =
108
        my $strsth2 = ($tablename eq 'branches') ?
113
          ( $column =~ /branchcode/ or $column =~ /itype/ )
109
		"SELECT $column as coltitle, count(items.itemnumber) AS coltitle_count FROM $tablename LEFT JOIN items ON items.homebranch=$column " :
114
          ? "$tablename.$column"
110
		"SELECT $column as coltitle, count(*)                AS coltitle_count FROM $tablename " ;
115
          : $column;
111
        if ($tablename eq 'branches') {
116
        my $strsth2 =
112
			my $f = @$filters[0];
117
          ( $tablename eq 'branches' )
118
          ? "SELECT $column as coltitle, count(items.itemnumber) AS coltitle_count FROM $tablename LEFT JOIN items ON items.homebranch=$column "
119
          : "SELECT $column as coltitle, count(*)                AS coltitle_count FROM $tablename ";
120
        if ( $tablename eq 'branches' ) {
121
            my $f = @$filters[0];
113
            $f =~ s/\*/%/g;
122
            $f =~ s/\*/%/g;
114
            $strsth2 .= " AND $column LIKE '$f' " ;
123
            $strsth2 .= " AND $column LIKE '$f' ";
115
        }
124
        }
116
        $strsth2 .=" GROUP BY $column ORDER BY $column ";	# needed for count
125
        $strsth2 .= " GROUP BY $column ORDER BY $column ";    # needed for count
117
		push @loopfilter, {crit=>'SQL', sql=>1, filter=>$strsth2};
126
        push @loopfilter, { crit => 'SQL', sql => 1, filter => $strsth2 };
118
        $debug and warn "catalogue_out SQL: ". $strsth2;
127
        $debug and warn "catalogue_out SQL: " . $strsth2;
119
        my $sth2 = $dbh->prepare($strsth2);
128
        my $sth2 = $dbh->prepare($strsth2);
120
        $sth2->execute;
129
        $sth2->execute;
121
    
130
122
        while (my ($celvalue, $count) = $sth2->fetchrow) {
131
        while ( my ( $celvalue, $count ) = $sth2->fetchrow ) {
123
			($celvalue) or $celvalue = 'UNKNOWN';
132
            ($celvalue) or $celvalue = 'UNKNOWN';
124
			$columns{$celvalue} = $count;
133
            $columns{$celvalue} = $count;
125
        }
134
        }
126
    }
135
    }
127
    
128
	my %tables = (map {$_=>[]} keys %columns);
129
136
130
# preparing calculation
137
    my %tables = ( map { $_ => [] } keys %columns );
131
	my @exe_args = ();
138
132
    my $query = "
139
    # preparing calculation
140
    my @exe_args = ();
141
    my $query    = "
133
        SELECT items.barcode        as barcode,
142
        SELECT items.barcode        as barcode,
134
               items.homebranch     as branch,
143
               items.homebranch     as branch,
135
               items.itemcallnumber as itemcallnumber,
144
               items.itemcallnumber as itemcallnumber,
136
               biblio.title         as title,
145
               biblio.title         as title,
137
               biblio.biblionumber  as biblionumber,
146
               biblio.biblionumber  as biblionumber,
138
               biblio.author        as author";
147
               biblio.author        as author";
139
	($column) and $query .= ",\n$column as col ";
148
    ($column) and $query .= ",\n$column as col ";
140
	$query .= "
149
    $query .= "
141
        FROM items
150
        FROM items
142
        LEFT JOIN biblio      USING (biblionumber)
151
        LEFT JOIN biblio      USING (biblionumber)
143
        LEFT JOIN     issues  USING (itemnumber)
152
        LEFT JOIN     issues  USING (itemnumber)
Lines 145-223 sub calculate { Link Here
145
          WHERE       issues.itemnumber IS NULL
154
          WHERE       issues.itemnumber IS NULL
146
           AND    old_issues.itemnumber IS NULL
155
           AND    old_issues.itemnumber IS NULL
147
	";
156
	";
148
	if ($filters->[0]) {
157
    if ( $filters->[0] ) {
149
    	$filters->[0]=~ s/\*/%/g;
158
        $filters->[0] =~ s/\*/%/g;
150
		push @exe_args, $filters->[0]; 
159
        push @exe_args, $filters->[0];
151
    	$query .= " AND items.homebranch LIKE ?";
160
        $query .= " AND items.homebranch LIKE ?";
152
	}
161
    }
153
	if ($filters->[1]) {
162
    if ( $filters->[1] ) {
154
    	$filters->[1]=~ s/\*/%/g;
163
        $filters->[1] =~ s/\*/%/g;
155
		push @exe_args, $filters->[1]; 
164
        push @exe_args, $filters->[1];
156
    	$query .= " AND items.itype      LIKE ?";
165
        $query .= " AND items.itype      LIKE ?";
157
	}
166
    }
158
	if ($column) {
167
    if ($column) {
159
		$query .= " AND $column = ? GROUP BY items.itemnumber, $column ";	# placeholder handled below
168
        $query .= " AND $column = ? GROUP BY items.itemnumber, $column ";
160
    } else {
169
        # placeholder handled below
161
		$query .= " GROUP BY items.itemnumber ";
170
    }
162
	}
171
    else {
163
	$query .= " ORDER BY items.itemcallnumber DESC, barcode";
172
        $query .= " GROUP BY items.itemnumber ";
173
    }
174
    $query .= " ORDER BY items.itemcallnumber DESC, barcode";
164
    $query .= " LIMIT 0,$limit" if ($limit);
175
    $query .= " LIMIT 0,$limit" if ($limit);
165
    $debug and warn "SQL : $query";
176
    $debug and warn "SQL : $query";
177
166
    # warn "SQL : $query";
178
    # warn "SQL : $query";
167
	push @loopfilter, {crit=>'SQL', sql=>1, filter=>$query};
179
    push @loopfilter, { crit => 'SQL', sql => 1, filter => $query };
168
    my $dbcalc = $dbh->prepare($query);
180
    my $dbcalc = $dbh->prepare($query);
169
181
170
	if ($column) {
182
    if ($column) {
171
		foreach (sort keys %columns) {
183
        foreach ( sort keys %columns ) {
172
			my (@more_exe_args) = @exe_args;	# execute(@exe_args,$_) would fail when the array is empty.
184
            # execute(@exe_args,$_) would fail when the array is empty
173
			push @more_exe_args, $_;			# but @more_exe_args will work
185
            # but @more_exe_args will work
174
			$dbcalc->execute(@more_exe_args) or die "Query execute(@more_exe_args) failed: $query";
186
            my (@more_exe_args) = @exe_args;
175
    		while (my $data = $dbcalc->fetchrow_hashref) {
187
            push @more_exe_args, $_;
176
				my $col = $data->{col} || 'NULL';
188
            $dbcalc->execute(@more_exe_args)
177
				$tables{$col} or $tables{$col} = [];
189
              or die "Query execute(@more_exe_args) failed: $query";
178
				push @{$tables{$col}}, $data;
190
            while ( my $data = $dbcalc->fetchrow_hashref ) {
179
			}
191
                my $col = $data->{col} || 'NULL';
180
		}
192
                $tables{$col} or $tables{$col} = [];
181
	} else {
193
                push @{ $tables{$col} }, $data;
182
    	(scalar @exe_args) ? $dbcalc->execute(@exe_args) : $dbcalc->execute;
194
            }
183
		while (my $data = $dbcalc->fetchrow_hashref) {
195
        }
184
			my $col = $data->{col} || 'NULL';
196
    }
185
			$tables{$col} or $tables{$col} = [];
197
    else {
186
			push @{$tables{$col}}, $data;
198
        ( scalar @exe_args ) ? $dbcalc->execute(@exe_args) : $dbcalc->execute;
187
		}
199
        while ( my $data = $dbcalc->fetchrow_hashref ) {
188
	}
200
            my $col = $data->{col} || 'NULL';
189
    
201
            $tables{$col} or $tables{$col} = [];
190
	foreach my $tablename (sort keys %tables) {
202
            push @{ $tables{$col} }, $data;
191
		my (@temptable);
203
        }
192
		my $i=0;
204
    }
193
		foreach my $cell (@{$tables{$tablename}}) {
205
194
			if (0 == $i++ and $debug) {
206
    foreach my $tablename ( sort keys %tables ) {
195
				my $dump = Dumper($cell);
207
        my (@temptable);
196
				$dump =~ s/\n/ /gs;
208
        my $i = 0;
197
				$dump =~ s/\s+/ /gs;
209
        foreach my $cell ( @{ $tables{$tablename} } ) {
198
				print STDERR "first cell for $tablename: $dump";
210
            if ( 0 == $i++ and $debug ) {
199
			}
211
                my $dump = Dumper($cell);
200
			push @temptable, $cell;
212
                $dump =~ s/\n/ /gs;
201
		}
213
                $dump =~ s/\s+/ /gs;
202
		my $count = scalar(@temptable);
214
                print STDERR "first cell for $tablename: $dump";
203
		my $allitems = $columns{$tablename};
215
            }
204
		$globalline{total_looptable_count} += $count;
216
            push @temptable, $cell;
205
		$globalline{total_coltitle_count}  += $allitems;
217
        }
206
    	push @{$globalline{looptables}}, {
218
        my $count    = scalar(@temptable);
207
			looprow  => \@temptable,
219
        my $allitems = $columns{$tablename};
208
			coltitle => $tablename,
220
        $globalline{total_looptable_count} += $count;
209
			coltitle_count  => $allitems,
221
        $globalline{total_coltitle_count}  += $allitems;
210
			looptable_count => $count,
222
        push @{ $globalline{looptables} },
211
			looptable_first => ($count) ? $temptable[ 0]->{itemcallnumber} : '',
223
          {
212
			looptable_last  => ($count) ? $temptable[-1]->{itemcallnumber} : '',
224
            looprow         => \@temptable,
213
		};
225
            coltitle        => $tablename,
214
	}
226
            coltitle_count  => $allitems,
227
            looptable_count => $count,
228
            looptable_first => ($count) ? $temptable[0]->{itemcallnumber} : '',
229
            looptable_last  => ($count) ? $temptable[-1]->{itemcallnumber} : '',
230
          };
231
    }
215
232
216
    # the header of the table
233
    # the header of the table
217
    $globalline{loopfilter}=\@loopfilter;
234
    $globalline{loopfilter} = \@loopfilter;
218
    $globalline{limit}   = $limit;
235
    $globalline{limit}      = $limit;
219
    $globalline{column}  = $column;
236
    $globalline{column}     = $column;
220
    return [(\%globalline)]; #	reference to array of reference to hash
237
    return [ ( \%globalline ) ];    #	reference to array of reference to hash
221
}
238
}
222
239
223
1;
240
1;
224
- 

Return to bug 10718