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

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/catalogue_out.tt (-33 / +20 lines)
Lines 100-107 Link Here
100
	<select name="Filter" id="branch">
100
	<select name="Filter" id="branch">
101
        <option value="">Any library</option>
101
        <option value="">Any library</option>
102
    [% FOREACH branchloo IN branchloop %]
102
    [% FOREACH branchloo IN branchloop %]
103
        <option value="[% branchloo.value %]">[% branchloo.branchname %]</option>  
103
      [% IF ( branchloo.selected ) %]
104
     [% END %] 
104
        <option value="[% branchloo.value %]" selected="selected">[% branchloo.branchname %]</option>
105
      [% ELSE %]
106
        <option value="[% branchloo.value %]">[% branchloo.branchname %]</option>
107
      [% END %]
108
    [% END %]
105
    </select></li>
109
    </select></li>
106
      <li> 
110
      <li> 
107
        <label for="documenttype">Item type: </label><select name="Filter" id="documenttype">
111
        <label for="documenttype">Item type: </label><select name="Filter" id="documenttype">
Lines 117-137 Link Here
117
	<fieldset class="rows">
121
	<fieldset class="rows">
118
	<legend>Limits</legend>
122
	<legend>Limits</legend>
119
		<ol>
123
		<ol>
120
		<li><label for="numberlimit">Limit to: </label> <select name="Limit" id="numberlimit">
124
        <li>
121
						<option value ="" selected="selected">None</option>
125
            <label for="numberlimit">Limit to: </label>
122
						<option value ="5"> 5</option>
126
            <select name="Limit" id="numberlimit">
123
						<option value ="10">10</option>
127
                <option value ="10">10</option>
124
						<option value ="15">15</option>
128
                <option value ="20">20</option>
125
						<option value ="20">20</option>
129
                <option value ="50">50</option>
126
						<option value ="25">25</option>
130
                <option value ="100">100</option>
127
						<option value ="40">40</option>
131
                <option value ="200">200</option>
128
						<option value ="50">50</option>
132
                <option value ="300">300</option>
129
						<option value ="100">100</option>
133
                <option value ="400">400</option>
130
						<option value ="250">250</option>
134
                <option value ="500">500</option>
131
						<option value ="500">500</option>
135
                <option value ="1000">1000</option>
132
						<option value ="700">750</option>
136
            </select>
133
						<option value ="1000">1000</option>
137
        </li>
134
					</select></li>
135
		<li><label for="criteria">By: </label><select name="Criteria" id="criteria">
138
		<li><label for="criteria">By: </label><select name="Criteria" id="criteria">
136
						<option value ="" selected="selected">None</option>
139
						<option value ="" selected="selected">None</option>
137
						<option value ="homebranch">Library</option>
140
						<option value ="homebranch">Library</option>
Lines 139-160 Link Here
139
					</select></li>
142
					</select></li>
140
		</ol>
143
		</ol>
141
	</fieldset>
144
	</fieldset>
142
<!-- FIXME: Not implemented
143
	<fieldset class="rows">
144
	<legend>Output</legend>
145
<ol><li><label for="outputscreen">To screen into the browser: </label><input type="radio" checked="checked" name="output" id="outputscreen" value="screen" /> </li>
146
    <li><label for="outputfile">To a file: </label><input type="radio" name="output" value="file" id="outputfile" disabled="disabled" />
147
		<label class="inline" for="basename">Named: </label><input type="text" name="basename" id="basename" value="Export" />
148
		<label class="inline" for="MIME">Into an application </label>
149
        <select name="MIME" id="MIME">
150
        [% FOREACH CGIextChoic IN CGIextChoice %]
151
          <option value="[% CGIextChoic.type %]">[% CGIextChoic.type %]</option>
152
        [% END %]
153
        </select>
154
        [% CGIsepChoice %]
155
	</li>
156
</ol>
157
    </fieldset> -->
158
145
159
	<fieldset class="action">
146
	<fieldset class="action">
160
	<input type="submit" value="Submit" />
147
	<input type="submit" value="Submit" />
(-)a/reports/catalogue_out.pl (-180 / +151 lines)
Lines 24-34 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; # GetBranches
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 C4::Reports;    # GetDelimiterChoices
31
use C4::Circulation;
32
# use Date::Manip;  # TODO: add not borrowed since date X criteria
30
# use Date::Manip;  # TODO: add not borrowed since date X criteria
33
use Data::Dumper;
31
use Data::Dumper;
34
32
Lines 38-188 Report that shows unborrowed items. Link Here
38
36
39
=cut
37
=cut
40
38
41
my $input = new CGI;
39
my $input   = new CGI;
42
my $do_it    = $input->param('do_it');
40
my $do_it   = $input->param('do_it');
43
my $limit    = $input->param("Limit");
41
my $limit   = $input->param("Limit") || 10;
44
my $column   = $input->param("Criteria");
42
my $column  = $input->param("Criteria");
45
my @filters  = $input->param("Filter");
43
my @filters = $input->param("Filter");
46
my $output   = $input->param("output");
44
47
my $basename = $input->param("basename") || 'catalogue_out';
45
my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
48
my ($template, $borrowernumber, $cookie) = get_template_and_user({
46
    {
49
    template_name => "reports/catalogue_out.tmpl",
47
        template_name   => "reports/catalogue_out.tmpl",
50
    query => $input,
48
        query           => $input,
51
    type => "intranet",
49
        type            => "intranet",
52
    authnotrequired => 0,
50
        authnotrequired => 0,
53
    flagsrequired => {reports => '*'},
51
        flagsrequired   => { reports => 'execute_reports' },
54
    debug => 1,
52
        debug           => 1,
55
});
53
    }
56
54
);
57
our $sep     = $input->param("sep");
58
$sep = "\t" if ((! defined $sep) or $sep eq 'tabulation');
59
55
60
$template->param(do_it => $do_it);
56
$template->param( do_it => $do_it );
61
if ($do_it) {
57
if ($do_it) {
62
    my $results = calculate($limit, $column, \@filters);
58
    my $results = calculate( $limit, $column, \@filters );
63
    if ($output eq "screen") {
59
    $template->param( mainloop => $results );
64
		# Printing results to screen
60
    output_html_with_http_headers $input, $cookie, $template->output;
65
		$template->param(mainloop => $results);
61
    exit;    # in either case, exit after do_it
66
        output_html_with_http_headers $input, $cookie, $template->output;
67
    } else {
68
		# Printing to a csv file	FIXME: This is broken rather badly, if it ever worked at all here.
69
        print $input->header(
70
			-type => 'application/vnd.sun.xml.calc',
71
            -encoding  => 'utf-8',
72
            -attachment=>"$basename.csv",
73
            -filename  =>"$basename.csv" );
74
        my $cols  = @$results[0]->{loopcol};
75
        my $lines = @$results[0]->{looprow};
76
# header
77
        print "num /". @$results[0]->{column} .$sep;
78
# Other header
79
        foreach my $col ( @$cols ) {
80
            print $col->{coltitle}.$sep;
81
        }
82
        print "Total\n";
83
# Table
84
        foreach my $line ( @$lines ) {
85
            my $x = $line->{loopcell};	# FIXME: No Such thing.
86
            print $line->{rowtitle}.$sep;
87
            foreach my $cell (@$x) {
88
                print $cell->{value}.$sep;
89
            }
90
            print $line->{totalrow}, "\n";
91
        }
92
# footer
93
        print "TOTAL";
94
        foreach my $col ( @$cols ) {
95
            print $sep.$col->{totalcol};
96
        }
97
        print $sep.@$results[0]->{total};
98
    }
99
    exit; # in either case, exit after do_it
100
}
62
}
101
63
102
# Displaying choices (i.e., not do_it)
64
# Displaying choices (i.e., not do_it)
103
my @values;
65
my $itemtypes = GetItemTypes();
104
my %select;
105
106
my @mime  = ( map { +{type =>$_} } (split /[;:]/, 'CSV') ); # FIXME translation
107
my $itemtypes = GetItemTypes;
108
my @itemtypeloop;
66
my @itemtypeloop;
109
foreach (sort {$itemtypes->{$a}->{description} cmp $itemtypes->{$b}->{description}} keys %$itemtypes) {
67
foreach (
110
	push @itemtypeloop, {
68
    sort { $itemtypes->{$a}->{'description'} cmp $itemtypes->{$b}->{'description'} }
111
		value => $_,
69
    keys %$itemtypes
112
		description => $itemtypes->{$_}->{'description'},
70
  )
113
   };
71
{
72
    push @itemtypeloop,
73
      {
74
        value       => $_,
75
        description => $itemtypes->{$_}->{'description'},
76
      };
114
}
77
}
115
78
116
$template->param(
79
$template->param(
117
	CGIextChoice => \@mime,
80
    itemtypeloop => \@itemtypeloop,
118
	CGIsepChoice => GetDelimiterChoices,
81
    branchloop   => GetBranchesLoop(),
119
	itemtypeloop => \@itemtypeloop,
120
	branchloop   => GetBranchesLoop($input->param("branch") || C4::Context->userenv->{branch}),
121
);
82
);
122
output_html_with_http_headers $input, $cookie, $template->output;
83
output_html_with_http_headers $input, $cookie, $template->output;
123
84
124
125
sub calculate {
85
sub calculate {
126
    my ($limit, $column, $filters) = @_;
86
    my ( $limit, $column, $filters ) = @_;
127
    my @loopline;
87
    my @loopline;
128
    my @looprow;
88
    my @looprow;
129
    my %globalline;
89
    my %globalline;
130
	my %columns = ();
90
    my %columns = ();
131
    my $dbh = C4::Context->dbh;
91
    my $dbh     = C4::Context->dbh;
132
92
133
# Filters
93
    # Filters
134
# Checking filters
94
    # Checking filters
135
#
95
    #
136
    my @loopfilter;
96
    my @loopfilter;
137
    for (my $i=0;$i<=6;$i++) {
97
    for ( my $i = 0 ; $i <= 6 ; $i++ ) {
138
        if ( @$filters[$i] ) {
98
        if ( @$filters[$i] ) {
139
        	my %cell = (filter=>@$filters[$i]);
99
            my %cell = ( filter => @$filters[$i] );
140
            if (($i==1) and (@$filters[$i-1])) {
100
            if ( ( $i == 1 ) and ( @$filters[ $i - 1 ] ) ) {
141
                $cell{err} = 1 if (@$filters[$i]<@$filters[$i-1]) ;
101
                $cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] );
142
            }
102
            }
143
            $cell{crit} = "Branch"   if ($i==0);
103
            $cell{crit} = "Branch"   if ( $i == 0 );
144
            $cell{crit} = "Doc Type" if ($i==1);
104
            $cell{crit} = "Doc Type" if ( $i == 1 );
145
            push @loopfilter, \%cell;
105
            push @loopfilter, \%cell;
146
        }
106
        }
147
    }
107
    }
148
	push @loopfilter, {crit=>'limit', filter=>$limit} if ($limit);
108
    push @loopfilter, { crit => 'limit', filter => $limit } if ($limit);
149
    if ($column){
109
    if ($column) {
150
		push @loopfilter, {crit=>'by', filter=>$column};
110
        push @loopfilter, { crit => 'by', filter => $column };
151
		my $tablename = ($column =~ /branchcode/) ? 'branches' : 'items';
111
        my $tablename = ( $column =~ /branchcode/ ) ? 'branches' : 'items';
152
		$column = ($column =~ /branchcode/ or $column =~ /itype/) ? "$tablename.$column" : $column;
112
        $column =
153
        my $strsth2 = ($tablename eq 'branches') ?
113
          ( $column =~ /branchcode/ or $column =~ /itype/ )
154
		"SELECT $column as coltitle, count(items.itemnumber) AS coltitle_count FROM $tablename LEFT JOIN items ON items.homebranch=$column " :
114
          ? "$tablename.$column"
155
		"SELECT $column as coltitle, count(*)                AS coltitle_count FROM $tablename " ;
115
          : $column;
156
        if ($tablename eq 'branches') {
116
        my $strsth2 =
157
			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];
158
            $f =~ s/\*/%/g;
122
            $f =~ s/\*/%/g;
159
            $strsth2 .= " AND $column LIKE '$f' " ;
123
            $strsth2 .= " AND $column LIKE '$f' ";
160
        }
124
        }
161
        $strsth2 .=" GROUP BY $column ORDER BY $column ";	# needed for count
125
        $strsth2 .= " GROUP BY $column ORDER BY $column ";    # needed for count
162
		push @loopfilter, {crit=>'SQL', sql=>1, filter=>$strsth2};
126
        push @loopfilter, { crit => 'SQL', sql => 1, filter => $strsth2 };
163
        $debug and warn "catalogue_out SQL: ". $strsth2;
127
        $debug and warn "catalogue_out SQL: " . $strsth2;
164
        my $sth2 = $dbh->prepare($strsth2);
128
        my $sth2 = $dbh->prepare($strsth2);
165
        $sth2->execute;
129
        $sth2->execute;
166
    
130
167
        while (my ($celvalue, $count) = $sth2->fetchrow) {
131
        while ( my ( $celvalue, $count ) = $sth2->fetchrow ) {
168
			($celvalue) or $celvalue = 'UNKNOWN';
132
            ($celvalue) or $celvalue = 'UNKNOWN';
169
			$columns{$celvalue} = $count;
133
            $columns{$celvalue} = $count;
170
        }
134
        }
171
    }
135
    }
172
    
173
	my %tables = (map {$_=>[]} keys %columns);
174
136
175
# preparing calculation
137
    my %tables = ( map { $_ => [] } keys %columns );
176
	my @exe_args = ();
138
177
    my $query = "
139
    # preparing calculation
140
    my @exe_args = ();
141
    my $query    = "
178
        SELECT items.barcode        as barcode,
142
        SELECT items.barcode        as barcode,
179
               items.homebranch     as branch,
143
               items.homebranch     as branch,
180
               items.itemcallnumber as itemcallnumber,
144
               items.itemcallnumber as itemcallnumber,
181
               biblio.title         as title,
145
               biblio.title         as title,
182
               biblio.biblionumber  as biblionumber,
146
               biblio.biblionumber  as biblionumber,
183
               biblio.author        as author";
147
               biblio.author        as author";
184
	($column) and $query .= ",\n$column as col ";
148
    ($column) and $query .= ",\n$column as col ";
185
	$query .= "
149
    $query .= "
186
        FROM items
150
        FROM items
187
        LEFT JOIN biblio      USING (biblionumber)
151
        LEFT JOIN biblio      USING (biblionumber)
188
        LEFT JOIN     issues  USING (itemnumber)
152
        LEFT JOIN     issues  USING (itemnumber)
Lines 190-268 sub calculate { Link Here
190
          WHERE       issues.itemnumber IS NULL
154
          WHERE       issues.itemnumber IS NULL
191
           AND    old_issues.itemnumber IS NULL
155
           AND    old_issues.itemnumber IS NULL
192
	";
156
	";
193
	if ($filters->[0]) {
157
    if ( $filters->[0] ) {
194
    	$filters->[0]=~ s/\*/%/g;
158
        $filters->[0] =~ s/\*/%/g;
195
		push @exe_args, $filters->[0]; 
159
        push @exe_args, $filters->[0];
196
    	$query .= " AND items.homebranch LIKE ?";
160
        $query .= " AND items.homebranch LIKE ?";
197
	}
161
    }
198
	if ($filters->[1]) {
162
    if ( $filters->[1] ) {
199
    	$filters->[1]=~ s/\*/%/g;
163
        $filters->[1] =~ s/\*/%/g;
200
		push @exe_args, $filters->[1]; 
164
        push @exe_args, $filters->[1];
201
    	$query .= " AND items.itype      LIKE ?";
165
        $query .= " AND items.itype      LIKE ?";
202
	}
166
    }
203
	if ($column) {
167
    if ($column) {
204
		$query .= " AND $column = ? GROUP BY items.itemnumber, $column ";	# placeholder handled below
168
        $query .= " AND $column = ? GROUP BY items.itemnumber, $column ";
205
    } else {
169
        # placeholder handled below
206
		$query .= " GROUP BY items.itemnumber ";
170
    }
207
	}
171
    else {
208
	$query .= " ORDER BY items.itemcallnumber DESC, barcode";
172
        $query .= " GROUP BY items.itemnumber ";
173
    }
174
    $query .= " ORDER BY items.itemcallnumber DESC, barcode";
209
    $query .= " LIMIT 0,$limit" if ($limit);
175
    $query .= " LIMIT 0,$limit" if ($limit);
210
    $debug and warn "SQL : $query";
176
    $debug and warn "SQL : $query";
177
211
    # warn "SQL : $query";
178
    # warn "SQL : $query";
212
	push @loopfilter, {crit=>'SQL', sql=>1, filter=>$query};
179
    push @loopfilter, { crit => 'SQL', sql => 1, filter => $query };
213
    my $dbcalc = $dbh->prepare($query);
180
    my $dbcalc = $dbh->prepare($query);
214
181
215
	if ($column) {
182
    if ($column) {
216
		foreach (sort keys %columns) {
183
        foreach ( sort keys %columns ) {
217
			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
218
			push @more_exe_args, $_;			# but @more_exe_args will work
185
            # but @more_exe_args will work
219
			$dbcalc->execute(@more_exe_args) or die "Query execute(@more_exe_args) failed: $query";
186
            my (@more_exe_args) = @exe_args;
220
    		while (my $data = $dbcalc->fetchrow_hashref) {
187
            push @more_exe_args, $_;
221
				my $col = $data->{col} || 'NULL';
188
            $dbcalc->execute(@more_exe_args)
222
				$tables{$col} or $tables{$col} = [];
189
              or die "Query execute(@more_exe_args) failed: $query";
223
				push @{$tables{$col}}, $data;
190
            while ( my $data = $dbcalc->fetchrow_hashref ) {
224
			}
191
                my $col = $data->{col} || 'NULL';
225
		}
192
                $tables{$col} or $tables{$col} = [];
226
	} else {
193
                push @{ $tables{$col} }, $data;
227
    	(scalar @exe_args) ? $dbcalc->execute(@exe_args) : $dbcalc->execute;
194
            }
228
		while (my $data = $dbcalc->fetchrow_hashref) {
195
        }
229
			my $col = $data->{col} || 'NULL';
196
    }
230
			$tables{$col} or $tables{$col} = [];
197
    else {
231
			push @{$tables{$col}}, $data;
198
        ( scalar @exe_args ) ? $dbcalc->execute(@exe_args) : $dbcalc->execute;
232
		}
199
        while ( my $data = $dbcalc->fetchrow_hashref ) {
233
	}
200
            my $col = $data->{col} || 'NULL';
234
    
201
            $tables{$col} or $tables{$col} = [];
235
	foreach my $tablename (sort keys %tables) {
202
            push @{ $tables{$col} }, $data;
236
		my (@temptable);
203
        }
237
		my $i=0;
204
    }
238
		foreach my $cell (@{$tables{$tablename}}) {
205
239
			if (0 == $i++ and $debug) {
206
    foreach my $tablename ( sort keys %tables ) {
240
				my $dump = Dumper($cell);
207
        my (@temptable);
241
				$dump =~ s/\n/ /gs;
208
        my $i = 0;
242
				$dump =~ s/\s+/ /gs;
209
        foreach my $cell ( @{ $tables{$tablename} } ) {
243
				print STDERR "first cell for $tablename: $dump";
210
            if ( 0 == $i++ and $debug ) {
244
			}
211
                my $dump = Dumper($cell);
245
			push @temptable, $cell;
212
                $dump =~ s/\n/ /gs;
246
		}
213
                $dump =~ s/\s+/ /gs;
247
		my $count = scalar(@temptable);
214
                print STDERR "first cell for $tablename: $dump";
248
		my $allitems = $columns{$tablename};
215
            }
249
		$globalline{total_looptable_count} += $count;
216
            push @temptable, $cell;
250
		$globalline{total_coltitle_count}  += $allitems;
217
        }
251
    	push @{$globalline{looptables}}, {
218
        my $count    = scalar(@temptable);
252
			looprow  => \@temptable,
219
        my $allitems = $columns{$tablename};
253
			coltitle => $tablename,
220
        $globalline{total_looptable_count} += $count;
254
			coltitle_count  => $allitems,
221
        $globalline{total_coltitle_count}  += $allitems;
255
			looptable_count => $count,
222
        push @{ $globalline{looptables} },
256
			looptable_first => ($count) ? $temptable[ 0]->{itemcallnumber} : '',
223
          {
257
			looptable_last  => ($count) ? $temptable[-1]->{itemcallnumber} : '',
224
            looprow         => \@temptable,
258
		};
225
            coltitle        => $tablename,
259
	}
226
            coltitle_count  => $allitems,
227
            looptable_count => $count,
228
            looptable_first => ($count) ? $temptable[0]->{itemcallnumber} : '',
229
            looptable_last  => ($count) ? $temptable[-1]->{itemcallnumber} : '',
230
          };
231
    }
260
232
261
    # the header of the table
233
    # the header of the table
262
    $globalline{loopfilter}=\@loopfilter;
234
    $globalline{loopfilter} = \@loopfilter;
263
    $globalline{limit}   = $limit;
235
    $globalline{limit}      = $limit;
264
    $globalline{column}  = $column;
236
    $globalline{column}     = $column;
265
    return [(\%globalline)]; #	reference to array of reference to hash
237
    return [ ( \%globalline ) ];    #	reference to array of reference to hash
266
}
238
}
267
239
268
1;
240
1;
269
- 

Return to bug 10718