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

(-)a/C4/Biblio.pm (+33 lines)
Lines 88-93 BEGIN { Link Here
88
      &GetAuthorisedValueDesc
88
      &GetAuthorisedValueDesc
89
      &GetMarcStructure
89
      &GetMarcStructure
90
      &GetMarcFromKohaField
90
      &GetMarcFromKohaField
91
      &GetMarcSubfieldStructureFromKohaField
91
      &GetFrameworkCode
92
      &GetFrameworkCode
92
      &TransformKohaToMarc
93
      &TransformKohaToMarc
93
      &PrepHostMarcField
94
      &PrepHostMarcField
Lines 1177-1182 sub GetMarcFromKohaField { Link Here
1177
    return (0, undef);
1178
    return (0, undef);
1178
}
1179
}
1179
1180
1181
=head2 GetMarcSubfieldStructureFromKohaField
1182
1183
    my $subfield_structure = &GetMarcSubfieldStructureFromKohaField($kohafield, $frameworkcode);
1184
1185
Returns a hashref where keys are marc_subfield_structure column names for the
1186
row where kohafield=$kohafield for the given framework code.
1187
1188
$frameworkcode is optional. If not given, then the default framework is used.
1189
1190
=cut
1191
1192
sub GetMarcSubfieldStructureFromKohaField {
1193
    my ($kohafield, $frameworkcode) = @_;
1194
1195
    return undef unless $kohafield;
1196
    $frameworkcode //= '';
1197
1198
    my $dbh = C4::Context->dbh;
1199
    my $query = qq{
1200
        SELECT *
1201
        FROM marc_subfield_structure
1202
        WHERE kohafield = ?
1203
          AND frameworkcode = ?
1204
    };
1205
    my $sth = $dbh->prepare($query);
1206
    $sth->execute($kohafield, $frameworkcode);
1207
    my $result = $sth->fetchrow_hashref;
1208
    $sth->finish;
1209
1210
    return $result;
1211
}
1212
1180
=head2 GetMarcBiblio
1213
=head2 GetMarcBiblio
1181
1214
1182
  my $record = GetMarcBiblio($biblionumber, [$embeditems]);
1215
  my $record = GetMarcBiblio($biblionumber, [$embeditems]);
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/acquisitions_stats.tt (-4 / +57 lines)
Lines 18-24 Link Here
18
[% IF ( do_it ) %]
18
[% IF ( do_it ) %]
19
	[% FOREACH mainloo IN mainloop %]
19
	[% FOREACH mainloo IN mainloop %]
20
		<h1>Acquisitions statistics </h1>
20
		<h1>Acquisitions statistics </h1>
21
     		[% IF ( mainloo.loopfilter ) %]
21
            [% IF ( mainloo.loopfilter.size ) %]
22
			<p><b>Filtered on:</b></p>
22
			<p><b>Filtered on:</b></p>
23
			[% FOREACH loopfilte IN mainloo.loopfilter %]
23
			[% FOREACH loopfilte IN mainloo.loopfilter %]
24
					<p>[% loopfilte.crit %]: [% loopfilte.filter %]</p>
24
					<p>[% loopfilte.crit %]: [% loopfilte.filter %]</p>
Lines 244-249 Link Here
244
				<td><input type="radio" name="Column" value="aqbooksellers.name" /></td>
244
				<td><input type="radio" name="Column" value="aqbooksellers.name" /></td>
245
				<td>[% CGIBookSeller %]</td>
245
				<td>[% CGIBookSeller %]</td>
246
			</tr>
246
			</tr>
247
            <tr>
248
                <td>Home branch</td>
249
                <td><input type="radio" name="Line" value="items.homebranch" /></td>
250
                <td><input type="radio" name="Column" value="items.homebranch" /></td>
251
                <td>
252
                    <select name="Filter">
253
                        <option value="" selected="selected">All branches</option>
254
                        [% FOREACH branch IN branches %]
255
                            <option value="[% branch.branchcode %]">[% branch.branchname %]</option>
256
                        [% END %]
257
                    </select>
258
                </td>
259
            </tr>
260
            <tr>
261
                <td>
262
                    [% IF ccode_label %]
263
                        [% ccode_label %]
264
                    [% ELSE %]
265
                        Collection
266
                    [% END %]
267
                </td>
268
                <td><input type="radio" name="Line" value="items.ccode" /></td>
269
                <td><input type="radio" name="Column" value="items.ccode" /></td>
270
                <td>
271
                    [% IF ccode_avlist.size %]
272
                        <select name="Filter">
273
                            <option value="" selected="selected">All</option>
274
                            [% FOREACH ccode_av IN ccode_avlist %]
275
                                <option value="[% ccode_av.value %]">[% ccode_av.lib %]</option>
276
                            [% END %]
277
                        </select>
278
                    [% ELSE %]
279
                        <input type="text" name="Filter" />
280
                    [% END %]
281
                </td>
282
            </tr>
247
			<tr>
283
			<tr>
248
				<td>Item Type</td>
284
				<td>Item Type</td>
249
				<td><input type="radio" name="Line" value="biblioitems.itemtype" /></td>
285
				<td><input type="radio" name="Line" value="biblioitems.itemtype" /></td>
Lines 278-286 Link Here
278
	</table><br />
314
	</table><br />
279
	</fieldset>
315
	</fieldset>
280
316
281
<fieldset class="rows">		<legend> Cell value </legend>
317
<fieldset class="rows">
282
			<ol><li><label for="cellvalue1">Count items</label><input type="radio" name="Cellvalue" id="cellvalue1" value="1" checked="checked" /> </li>
318
    <legend> Cell value </legend>
283
			<li><label for="cellvalue2">Amount</label><input type="radio" name="Cellvalue" id="cellvalue2" value="2" /> </li></ol></fieldset>
319
    <ol>
320
        <li>
321
            <label for="cellvalue1">Count items</label><input type="radio" name="Cellvalue" id="cellvalue1" value="1" checked="checked" />
322
        </li>
323
        <li>
324
            <label for="cellvalue2">Count unique biblios</label><input type="radio" name="Cellvalue" id="cellvalue2" value="2" />
325
        </li>
326
        <li>
327
            <label for="cellvalue3">Amount</label><input type="radio" name="Cellvalue" id="cellvalue3" value="3" />
328
        </li>
329
        <li>
330
            <label for="cellvalue4">Ordered amount</label><input type="radio" name="Cellvalue" id="cellvalue4" value="4" />
331
        </li>
332
        <li>
333
            <label for="cellvalue5">Spent amount</label><input type="radio" name="Cellvalue" id="cellvalue5" value="5" />
334
        </li>
335
    </ol>
336
</fieldset>
284
337
285
	<fieldset class="rows">
338
	<fieldset class="rows">
286
	<legend>Output</legend>
339
	<legend>Output</legend>
(-)a/reports/acquisitions_stats.pl (-215 / +216 lines)
Lines 20-27 Link Here
20
20
21
# test comment
21
# test comment
22
22
23
use strict;
23
use Modern::Perl;
24
#use warnings; FIXME - Bug 2505
24
25
use C4::Auth;
25
use C4::Auth;
26
use CGI;
26
use CGI;
27
use C4::Context;
27
use C4::Context;
Lines 30-35 use C4::Output; Link Here
30
use C4::Koha;
30
use C4::Koha;
31
use C4::Circulation;
31
use C4::Circulation;
32
use C4::Dates qw/format_date format_date_in_iso/;
32
use C4::Dates qw/format_date format_date_in_iso/;
33
use C4::Branch;
34
use C4::Biblio;
33
35
34
=head1 NAME
36
=head1 NAME
35
37
Lines 53-79 $filters[2] = format_date_in_iso( $filters[2] ); Link Here
53
$filters[3] = format_date_in_iso( $filters[3] );
55
$filters[3] = format_date_in_iso( $filters[3] );
54
my $podsp          = $input->param("PlacedOnDisplay");
56
my $podsp          = $input->param("PlacedOnDisplay");
55
my $rodsp          = $input->param("ReceivedOnDisplay");
57
my $rodsp          = $input->param("ReceivedOnDisplay");
56
my $aodsp          = $input->param("AcquiredOnDisplay");    ##added by mason.
57
my $calc           = $input->param("Cellvalue");
58
my $calc           = $input->param("Cellvalue");
58
my $output         = $input->param("output");
59
my $output         = $input->param("output");
59
my $basename       = $input->param("basename");
60
my $basename       = $input->param("basename");
60
61
61
my ($template, $borrowernumber, $cookie)
62
my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
62
	= get_template_and_user({template_name => $fullreportname,
63
    {
63
				query => $input,
64
        template_name   => $fullreportname,
64
				type => "intranet",
65
        query           => $input,
65
				authnotrequired => 0,
66
        type            => "intranet",
66
				flagsrequired => {reports => '*'},
67
        authnotrequired => 0,
67
				debug => 1,
68
        flagsrequired   => { reports => '*' },
68
				});
69
        debug           => 1,
69
our $sep     = $input->param("sep");
70
    }
71
);
72
73
our $sep     = $input->param("sep") // '';
70
$sep = "\t" if ($sep eq 'tabulation');
74
$sep = "\t" if ($sep eq 'tabulation');
71
$template->param(do_it => $do_it,
75
$template->param(
72
        DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
76
    do_it                    => $do_it,
73
		);
77
    DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
78
);
74
if ($do_it) {
79
if ($do_it) {
75
    my $results =
80
    my $results =
76
      calculate( $line, $column, $podsp, $rodsp, $aodsp, $calc, \@filters );
81
      calculate( $line, $column, $podsp, $rodsp, $calc, \@filters );
77
    if ( $output eq "screen" ) {
82
    if ( $output eq "screen" ) {
78
        $template->param( mainloop => $results );
83
        $template->param( mainloop => $results );
79
        output_html_with_http_headers $input, $cookie, $template->output;
84
        output_html_with_http_headers $input, $cookie, $template->output;
Lines 117-125 else { Link Here
117
    my $req;
122
    my $req;
118
    $req = $dbh->prepare("SELECT distinctrow id,name FROM aqbooksellers ORDER BY name");
123
    $req = $dbh->prepare("SELECT distinctrow id,name FROM aqbooksellers ORDER BY name");
119
    $req->execute;
124
    $req->execute;
120
    my @select;
121
    push @select, "";
125
    push @select, "";
122
	$select{''} = "All Suppliers";
126
    $select{''} = "All Suppliers";
123
    while ( my ( $value, $desc ) = $req->fetchrow ) {
127
    while ( my ( $value, $desc ) = $req->fetchrow ) {
124
        push @select, $desc;
128
        push @select, $desc;
125
        $select{$value}=$desc;
129
        $select{$value}=$desc;
Lines 183-193 else { Link Here
183
    $select{''} = "All";
187
    $select{''} = "All";
184
    my $hassort1;
188
    my $hassort1;
185
    while ( my ($value) = $req->fetchrow ) {
189
    while ( my ($value) = $req->fetchrow ) {
186
		if ($value) {
190
        if ($value) {
187
			$hassort1 = 1;
191
            $hassort1 = 1;
188
			push @select, $value;
192
            push @select, $value;
189
			$select{$value} = $value;
193
            $select{$value} = $value;
190
		}
194
        }
191
    }
195
    }
192
    my $CGISort1 = CGI::scrolling_list(
196
    my $CGISort1 = CGI::scrolling_list(
193
        -name     => 'Filter',
197
        -name     => 'Filter',
Lines 211-222 else { Link Here
211
    my $hglghtsort2;
215
    my $hglghtsort2;
212
216
213
    while ( my ($value) = $req->fetchrow ) {
217
    while ( my ($value) = $req->fetchrow ) {
214
		if ($value) {
218
        if ($value) {
215
			$hassort2 = 1;
219
            $hassort2    = 1;
216
			$hglghtsort2 = !($hassort1);
220
            $hglghtsort2 = !($hassort1);
217
			push @select, $value;
221
            push @select, $value;
218
			$select{$value} = $value;
222
            $select{$value} = $value;
219
		}
223
        }
220
    }
224
    }
221
    my $CGISort2 = CGI::scrolling_list(
225
    my $CGISort2 = CGI::scrolling_list(
222
        -name     => 'Filter',
226
        -name     => 'Filter',
Lines 237-261 else { Link Here
237
241
238
    my $CGIsepChoice = GetDelimiterChoices;
242
    my $CGIsepChoice = GetDelimiterChoices;
239
243
244
    my $branches = GetBranches;
245
    my @branches;
246
    foreach ( sort keys $branches ) {
247
        push @branches, $branches->{$_};
248
    }
249
250
    my $ccode_subfield_structure = GetMarcSubfieldStructureFromKohaField('items.ccode', '');
251
    my $ccode_label;
252
    my $ccode_avlist;
253
    if($ccode_subfield_structure) {
254
        $ccode_label = $ccode_subfield_structure->{liblibrarian};
255
        $ccode_avlist = GetAuthorisedValues($ccode_subfield_structure->{authorised_value});
256
    }
257
240
    $template->param(
258
    $template->param(
241
        CGIBookSeller => $CGIBookSellers,
259
        CGIBookSeller => $CGIBookSellers,
242
        CGIItemType   => $CGIItemTypes,
260
        CGIItemType   => $CGIItemTypes,
243
        CGIBudget     => $CGIBudget,
261
        CGIBudget     => $CGIBudget,
244
        hassort1      => $hassort1,
262
        hassort1      => $hassort1,
245
        hassort2      => $hassort2,
263
        hassort2      => $hassort2,
246
        HlghtSort2    => $hglghtsort2,
247
        CGISort1      => $CGISort1,
264
        CGISort1      => $CGISort1,
248
        CGISort2      => $CGISort2,
265
        CGISort2      => $CGISort2,
249
        CGIextChoice  => $CGIextChoice,
266
        CGIextChoice  => $CGIextChoice,
250
        CGIsepChoice  => $CGIsepChoice,
267
        CGIsepChoice  => $CGIsepChoice,
251
		date_today => C4::Dates->new()->output()
268
        branches      => \@branches,
269
        ccode_label   => $ccode_label,
270
        ccode_avlist  => $ccode_avlist,
252
    );
271
    );
253
272
254
}
273
}
255
output_html_with_http_headers $input, $cookie, $template->output;
274
output_html_with_http_headers $input, $cookie, $template->output;
256
275
257
sub calculate {
276
sub calculate {
258
    my ( $line, $column, $podsp, $rodsp, $aodsp, $process, $filters ) = @_;
277
    my ( $line, $column, $podsp, $rodsp, $process, $filters ) = @_;
259
    my @mainloop;
278
    my @mainloop;
260
    my @loopfooter;
279
    my @loopfooter;
261
    my @loopcol;
280
    my @loopcol;
Lines 264-269 sub calculate { Link Here
264
    my %globalline;
283
    my %globalline;
265
    my $grantotal = 0;
284
    my $grantotal = 0;
266
285
286
    $podsp ||= 0;
287
    $rodsp ||= 0;
288
267
    # extract parameters
289
    # extract parameters
268
    my $dbh = C4::Context->dbh;
290
    my $dbh = C4::Context->dbh;
269
291
Lines 271-379 sub calculate { Link Here
271
    # Checking filters
293
    # Checking filters
272
    #
294
    #
273
    my @loopfilter;
295
    my @loopfilter;
274
    for ( my $i = 0 ; $i <= 8 ; $i++ ) {
296
    for ( my $i = 0 ; $i <= @$filters ; $i++ ) {
275
        my %cell;
297
        if( defined @$filters[$i] and @$filters[$i] ne '' ) {
276
        if ( @$filters[$i] ) {
298
            my %cell;
277
            if ( ( ( $i == 1 ) or ( $i == 3 ) ) and ( @$filters[ $i - 1 ] ) ) {
299
            if ( ( ( $i == 1 ) or ( $i == 3 ) ) and ( @$filters[ $i - 1 ] ) ) {
278
                $cell{err} = 1 if ( @$filters[$i] < @$filters[ $i - 1 ] );
300
                $cell{err} = 1 if ( @$filters[$i] lt @$filters[ $i - 1 ] );
279
            }
301
            }
280
            # format the dates filters, otherwise just fill as is
302
            # format the dates filters, otherwise just fill as is
281
            if ($i>=4) {
303
            if ($i >= 4) {
282
                $cell{filter} .= @$filters[$i];
304
                $cell{filter} = @$filters[$i];
283
            } else {
305
            } else {
284
                $cell{filter} .= format_date(@$filters[$i]);
306
                $cell{filter} = format_date(@$filters[$i]);
285
            }
307
            }
286
            $cell{crit}   .= "Placed On From" if ( $i == 0 );
308
            $cell{crit} = do {
287
            $cell{crit}   .= "Placed On To" if ( $i == 1 );
309
                given ($i) {
288
            $cell{crit}   .= "Received On From" if ( $i == 2 );
310
                    when (0)  { "Placed On From" }
289
            $cell{crit}   .= "Received On To" if ( $i == 3 );
311
                    when (1)  { "Placed On To" }
290
312
                    when (2)  { "Received On From" }
291
#            $cell{crit} .= "Acquired On From" if ( $i == 4 );
313
                    when (3)  { "Received On To" }
292
#            $cell{crit} .= "Acquired On To"   if ( $i == 5 );
314
                    when (4)  { "Bookseller" }
293
315
                    when (5)  { "Home branch" }
294
            $cell{crit} .= "BookSeller" if ( $i == 4 );
316
                    when (6)  { "Collection" }
295
            $cell{crit} .= "Doc Type"   if ( $i == 5 );
317
                    when (7)  { "Doc Type" }
296
            $cell{crit} .= "Budget"     if ( $i == 6 );
318
                    when (8)  { "Budget" }
297
            $cell{crit} .= "Sort1"      if ( $i == 7 );
319
                    when (9)  { "Sort1" }
298
            $cell{crit} .= "Sort2"      if ( $i == 8 );
320
                    when (10) { "Sort2" }
321
                    default   { "" }
322
                }
323
            };
299
            push @loopfilter, \%cell;
324
            push @loopfilter, \%cell;
300
        }
325
        }
301
    }
326
    }
302
327
303
    my @linefilter;
328
    my %filter;
304
329
    my %field;
305
    $linefilter[0] = @$filters[0] if ( $line =~ /closedate/ );
330
    foreach ($line, $column) {
306
    $linefilter[1] = @$filters[1] if ( $line =~ /closedate/ );
331
        $filter{$_} = [];
307
    $linefilter[0] = @$filters[2] if ( $line =~ /received/ );
332
        $field{$_} = $_;
308
    $linefilter[1] = @$filters[3] if ( $line =~ /received/ );
333
        given ($_) {
309
334
            when (/closedate/) {
310
    $linefilter[0] = @$filters[4]  if ( $line =~ /bookseller/ );
335
                $filter{$_}->[0] = @$filters[0];
311
    $linefilter[0] = @$filters[5]  if ( $line =~ /itemtype/ );
336
                $filter{$_}->[1] = @$filters[1];
312
    $linefilter[0] = @$filters[6]  if ( $line =~ /budget/ );
337
                $field{$_} = do {
313
    $linefilter[0] = @$filters[7]  if ( $line =~ /sort1/ );
338
                    my $a = $_;
314
    $linefilter[0] = @$filters[8] if ( $line =~ /sort2/ );
339
                    given ($podsp) {
315
340
                        when (1) { "concat(hex(weekday($a)+1),'-',dayname($a))" }
316
    my @colfilter;
341
                        when (2) { "concat(hex(month($a)),'-',monthname($a))" }
317
    $colfilter[0] = @$filters[0] if ( $column =~ /closedate/ );
342
                        when (3) { "Year($a)" }
318
    $colfilter[1] = @$filters[1] if ( $column =~ /closedate/ );
343
                        default  { $a }
319
    $colfilter[0] = @$filters[2] if ( $column =~ /received/ );
344
                    }
320
    $colfilter[1] = @$filters[3] if ( $column =~ /received/ );
345
                };
321
346
            }
322
    $colfilter[0] = @$filters[4]  if ( $column =~ /bookseller/ );
347
            when (/received/) {
323
    $colfilter[0] = @$filters[5]  if ( $column =~ /itemtype/ );
348
                $filter{$_}->[0] = @$filters[2];
324
    $colfilter[0] = @$filters[6]  if ( $column =~ /budget/ );
349
                $filter{$_}->[1] = @$filters[3];
325
    $colfilter[0] = @$filters[7]  if ( $column =~ /sort1/ );
350
                $field{$_} = do {
326
    $colfilter[0] = @$filters[8]  if ( $column =~ /sort2/ );
351
                    my $a = $_;
327
352
                    given ($rodsp) {
328
    # 1st, loop rows.
353
                        when (1) { "concat(hex(weekday($a)+1),'-',dayname($a))" }
329
    my $linefield;
354
                        when (2) { "concat(hex(month($a)),'-',monthname($a))" }
330
    if ( ( $line =~ /closedate/ ) and ( $podsp == 1 ) ) {
355
                        when (3) { "Year($a)" }
331
356
                        default  { $a }
332
        #Display by day
357
                    }
333
        $linefield .= "concat(hex(weekday($line)+1),'-',dayname($line))";
358
                };
334
    }
359
            }
335
    elsif ( ( $line =~ /closedate/ ) and ( $podsp == 2 ) ) {
360
            when (/bookseller/) {
336
361
                $filter{$_}->[0] = @$filters[4];
337
        #Display by Month
362
            }
338
        $linefield .= "concat(hex(month($line)),'-',monthname($line))";
363
            when (/homebranch/) {
339
    }
364
                $filter{$_}->[0] = @$filters[5];
340
    elsif ( ( $line =~ /closedate/ ) and ( $podsp == 3 ) ) {
365
            }
341
366
            when (/ccode/) {
342
        #Display by Year
367
                $filter{$_}->[0] = @$filters[6];
343
        $linefield .= "Year($line)";
368
            }
344
369
            when (/itemtype/) {
345
    }
370
                $filter{$_}->[0] = @$filters[7];
346
    elsif ( ( $line =~ /received/ ) and ( $rodsp == 1 ) ) {
371
            }
347
372
            when (/budget/) {
348
        #Display by day
373
                $filter{$_}->[0] = @$filters[8];
349
        $linefield .= "concat(hex(weekday($line)+1),'-',dayname($line))";
374
            }
350
    }
375
            when (/sort1/) {
351
    elsif ( ( $line =~ /received/ ) and ( $rodsp == 2 ) ) {
376
                $filter{$_}->[0] = @$filters[9];
352
377
            }
353
        #Display by Month
378
            when (/sort2/) {
354
        $linefield .= "concat(hex(month($line)),'-',monthname($line))";
379
                $filter{$_}->[0] = @$filters[10];
380
            }
381
        }
355
    }
382
    }
356
    elsif ( ( $line =~ /received/ ) and ( $rodsp == 3 ) ) {
357
383
358
        #Display by Year
384
    my @linefilter = @{ $filter{$line} };
359
        $linefield .= "Year($line)";
385
    my $linefield = $field{$line};
386
    my @colfilter = @{ $filter{$column} };
387
    my $colfield = $field{$column};
360
388
361
    }
389
    # 1st, loop rows.
362
    else {
390
    my $strsth = "
363
        $linefield .= $line;
391
        SELECT DISTINCTROW $linefield
364
    }
392
        FROM aqorders
365
393
          LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno)
366
    my $strsth;
394
          LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber)
367
    $strsth .=
395
          LEFT JOIN items ON (aqorders_items.itemnumber = items.itemnumber)
368
      "SELECT DISTINCTROW $linefield FROM (aqorders, aqbasket )
396
          LEFT JOIN biblioitems ON (aqorders.biblionumber = biblioitems.biblionumber)
369
                LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber)
397
          LEFT JOIN aqbudgets  ON (aqorders.budget_id = aqbudgets.budget_id )
370
                LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber)
398
          LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id)
371
                LEFT JOIN aqbudgets  ON (aqorders.budget_id = aqbudgets.budget_id )
399
        WHERE $line IS NOT NULL AND $line <> '' ";
372
400
373
                LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) WHERE (aqorders.basketno=aqbasket.basketno)
401
    if (@linefilter) {
374
                AND $line IS NOT NULL AND $line <> '' ";
375
    
376
	if (@linefilter) {
377
        if ( $linefilter[1] ) {
402
        if ( $linefilter[1] ) {
378
            if ( $linefilter[0] ) {
403
            if ( $linefilter[0] ) {
379
                $strsth .= " AND $line BETWEEN ? AND ? ";
404
                $strsth .= " AND $line BETWEEN ? AND ? ";
Lines 408-469 sub calculate { Link Here
408
    else {
433
    else {
409
        $sth->execute;
434
        $sth->execute;
410
    }
435
    }
411
	while ( my ($celvalue) = $sth->fetchrow ) {
436
    while ( my ($celvalue) = $sth->fetchrow ) {
412
		my %cell;
437
        my %cell;
413
		if ($celvalue) {
438
        if ($celvalue) {
414
			$cell{rowtitle} = $celvalue;
439
            $cell{rowtitle} = $celvalue;
415
			push @loopline, \%cell;
440
            push @loopline, \%cell;
416
		}
441
        }
417
		$cell{totalrow} = 0;
442
        $cell{totalrow} = 0;
418
	}
419
    # 2nd, loop cols.
420
    my $colfield;
421
    if ( ( $column =~ /closedate/ ) and ( $podsp == 1 ) ) {
422
423
        #Display by day
424
        $colfield .= "concat(hex(weekday($column)+1),'-',dayname($column))";
425
    }
426
    elsif ( ( $column =~ /closedate/ ) and ( $podsp == 2 ) ) {
427
428
        #Display by Month
429
        $colfield .= "concat(hex(month($column)),'-',monthname($column))";
430
    }
431
    elsif ( ( $column =~ /closedate/ ) and ( $podsp == 3 ) ) {
432
433
        #Display by Year
434
        $colfield .= "Year($column)";
435
436
    }
437
    elsif ( ( $column =~ /received/ ) and ( $rodsp == 1 ) ) {
438
439
        #Display by day
440
        $colfield .= "concat(hex(weekday($column)+1),'-',dayname($column))";
441
    }
442
    elsif ( ( $column =~ /received/ ) and ( $rodsp == 2 ) ) {
443
444
        #Display by Month
445
        $colfield .= "concat(hex(month($column)),'-',monthname($column))";
446
    }
447
    elsif ( ( $column =~ /received/ ) and ( $rodsp == 3 ) ) {
448
449
        #Display by Year
450
        $colfield .= "Year($column)";
451
452
    }
453
    else {
454
        $colfield .= $column;
455
    }
443
    }
456
444
457
    my $strsth2;
445
    # 2nd, loop cols.
458
    $strsth2 .=
446
    my $strsth2 = "
459
      "SELECT distinctrow $colfield FROM (aqorders, aqbasket )
447
        SELECT DISTINCTROW $colfield
460
                 LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber)
448
        FROM aqorders
461
                 LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber)
449
          LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno)
462
                 LEFT JOIN aqbudgets  ON (aqorders.budget_id = aqbudgets.budget_id )
450
          LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber)
463
451
          LEFT JOIN items ON (aqorders_items.itemnumber = items.itemnumber)
464
                 LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id)
452
          LEFT JOIN biblioitems ON (aqorders.biblionumber = biblioitems.biblionumber)
465
                 WHERE (aqorders.basketno=aqbasket.basketno) AND 
453
          LEFT JOIN aqbudgets  ON (aqorders.budget_id = aqbudgets.budget_id )
466
                 $column IS NOT NULL AND $column <> '' ";
454
          LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id)
455
        WHERE $column IS NOT NULL AND $column <> ''
456
    ";
467
457
468
    if (@colfilter) {
458
    if (@colfilter) {
469
        if ( $colfilter[1] ) {
459
        if ( $colfilter[1] ) {
Lines 488-494 sub calculate { Link Here
488
        }
478
        }
489
    }
479
    }
490
480
491
492
    $strsth2 .= " GROUP BY $colfield";
481
    $strsth2 .= " GROUP BY $colfield";
493
    $strsth2 .= " ORDER BY $colfield";
482
    $strsth2 .= " ORDER BY $colfield";
494
483
Lines 503-515 sub calculate { Link Here
503
    else {
492
    else {
504
        $sth2->execute;
493
        $sth2->execute;
505
    }
494
    }
506
	while ( my $celvalue = $sth2->fetchrow ) {
495
    while ( my $celvalue = $sth2->fetchrow ) {
507
		my %cell;
496
        my %cell;
508
		if ($celvalue) {
497
        if ($celvalue) {
509
			$cell{coltitle} = $celvalue;
498
            $cell{coltitle} = $celvalue;
510
			push @loopcol, \%cell;
499
            push @loopcol, \%cell;
511
		}
500
        }
512
	}
501
    }
513
502
514
    my $i = 0;
503
    my $i = 0;
515
    my @totalcol;
504
    my @totalcol;
Lines 528-545 sub calculate { Link Here
528
    # preparing calculation
517
    # preparing calculation
529
    my $strcalc;
518
    my $strcalc;
530
    $strcalc .= "SELECT $linefield, $colfield, ";
519
    $strcalc .= "SELECT $linefield, $colfield, ";
531
    $strcalc .= "SUM( aqorders.quantity ) " if ( $process == 1 );
520
    $strcalc .= do { given ($process) {
532
    $strcalc .= "SUM( aqorders.quantity * aqorders.listprice ) "
521
        when (1) { "COUNT(*) " }
533
      if ( $process == 2 );
522
        when (2) { "COUNT(DISTINCT(aqorders.biblionumber)) " }
534
    $strcalc .= "FROM (aqorders, aqbasket )
523
        when ([3,4,5]) { "SUM(aqorders.listprice) " }
535
                 LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber)
524
        default { "NULL " }
536
                 LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber)
525
    } };
537
                 LEFT JOIN aqbudgets  ON (aqorders.budget_id = aqbudgets.budget_id )
526
    $strcalc .= "
538
527
        FROM aqorders
539
                 LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) 
528
          LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno)
540
                 WHERE (aqorders.basketno=aqbasket.basketno) ";
529
          LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber)
541
    
530
          LEFT JOIN items ON (aqorders_items.itemnumber = items.itemnumber)
542
	@$filters[0] =~ s/\*/%/g if ( @$filters[0] );
531
          LEFT JOIN biblioitems ON (aqorders.biblionumber = biblioitems.biblionumber)
532
          LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id )
533
          LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id)
534
        WHERE aqorders.datecancellationprinted IS NULL ";
535
    $strcalc .= " AND (aqorders.datereceived IS NULL OR aqorders.datereceived = '') "
536
        if ( $process == 4 );
537
    $strcalc .= " AND aqorders.datereceived IS NOT NULL AND aqorders.datereceived <> '' "
538
        if ( $process == 5 );
539
    @$filters[0] =~ s/\*/%/g if ( @$filters[0] );
543
    $strcalc .= " AND aqbasket.closedate >= '" . @$filters[0] . "'"
540
    $strcalc .= " AND aqbasket.closedate >= '" . @$filters[0] . "'"
544
      if ( @$filters[0] );
541
      if ( @$filters[0] );
545
    @$filters[1] =~ s/\*/%/g if ( @$filters[1] );
542
    @$filters[1] =~ s/\*/%/g if ( @$filters[1] );
Lines 554-573 sub calculate { Link Here
554
    @$filters[4] =~ s/\*/%/g if ( @$filters[4] );
551
    @$filters[4] =~ s/\*/%/g if ( @$filters[4] );
555
    $strcalc .= " AND aqbooksellers.name LIKE '" . @$filters[4] . "'"
552
    $strcalc .= " AND aqbooksellers.name LIKE '" . @$filters[4] . "'"
556
      if ( @$filters[4] );
553
      if ( @$filters[4] );
557
    @$filters[5] =~ s/\*/%/g if ( @$filters[5] );
554
    $strcalc .= " AND items.homebranch = '" . @$filters[5] . "'"
558
    $strcalc .= " AND biblioitems.itemtype LIKE '" . @$filters[5] . "'"
559
      if ( @$filters[5] );
555
      if ( @$filters[5] );
560
    @$filters[6] =~ s/\*/%/g if ( @$filters[6] );
556
    @$filters[6] =~ s/\*/%/g if ( @$filters[6] );
561
    $strcalc .= " AND aqbudgets.budget_code LIKE '" . @$filters[6] . "'"
557
    $strcalc .= " AND items.ccode = '" . @$filters[6] . "'"
562
      if ( @$filters[6] );
558
      if ( @$filters[6] );
563
    @$filters[7] =~ s/\*/%/g if ( @$filters[7] );
559
    @$filters[7] =~ s/\*/%/g if ( @$filters[7] );
564
    $strcalc .= " AND aqorders.sort1 LIKE '" . @$filters[7] . "'"
560
    $strcalc .= " AND biblioitems.itemtype LIKE '" . @$filters[7] . "'"
565
      if ( @$filters[7] );
561
      if ( @$filters[7] );
566
    @$filters[8] =~ s/\*/%/g if ( @$filters[8] );
562
    @$filters[8] =~ s/\*/%/g if ( @$filters[8] );
567
    $strcalc .= " AND aqorders.sort2 LIKE '" . @$filters[8] . "'"
563
    $strcalc .= " AND aqbudgets.budget_code LIKE '" . @$filters[8] . "'"
568
      if ( @$filters[8] );
564
      if ( @$filters[8] );
569
565
    @$filters[9] =~ s/\*/%/g if ( @$filters[9] );
570
    $strcalc .= " AND aqorders.datecancellationprinted is NULL ";
566
    $strcalc .= " AND aqorders.sort1 LIKE '" . @$filters[9] . "'"
567
      if ( @$filters[9] );
568
    @$filters[10] =~ s/\*/%/g if ( @$filters[10] );
569
    $strcalc .= " AND aqorders.sort2 LIKE '" . @$filters[10] . "'"
570
      if ( @$filters[10] );
571
571
572
    $strcalc .= " GROUP BY $linefield, $colfield ORDER BY $linefield,$colfield";
572
    $strcalc .= " GROUP BY $linefield, $colfield ORDER BY $linefield,$colfield";
573
    my $dbcalc = $dbh->prepare($strcalc);
573
    my $dbcalc = $dbh->prepare($strcalc);
Lines 575-582 sub calculate { Link Here
575
575
576
    my $emptycol;
576
    my $emptycol;
577
    while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {
577
    while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {
578
		next if ($row eq undef || $col eq undef);
579
580
        $emptycol = 1         if ( !defined($col) );
578
        $emptycol = 1         if ( !defined($col) );
581
        $col      = "zzEMPTY" if ( !defined($col) );
579
        $col      = "zzEMPTY" if ( !defined($col) );
582
        $row      = "zzEMPTY" if ( !defined($row) );
580
        $row      = "zzEMPTY" if ( !defined($row) );
Lines 594-608 sub calculate { Link Here
594
        # and the number matches the number of columns
592
        # and the number matches the number of columns
595
        foreach my $col (@loopcol) {
593
        foreach my $col (@loopcol) {
596
            my $value = $table{$row}->{ ( $col->{coltitle} eq "NULL" ) ? "zzEMPTY" : $col->{coltitle} };
594
            my $value = $table{$row}->{ ( $col->{coltitle} eq "NULL" ) ? "zzEMPTY" : $col->{coltitle} };
595
            $value = sprintf("%.2f", $value) if($value and grep /$process/, (3,4,5));
597
            push @loopcell, { value => $value };
596
            push @loopcell, { value => $value };
598
        }
597
        }
599
        push @looprow,
598
        my $r = {
600
          {
599
            rowtitle => ( $row eq "zzEMPTY" ) ? "NULL" : $row,
601
            'rowtitle' => ( $row eq "zzEMPTY" ) ? "NULL" : $row,
600
            loopcell  => \@loopcell,
602
            'loopcell'  => \@loopcell,
601
            hilighted => ( $hilighted > 0 ),
603
            'hilighted' => ( $hilighted > 0 ),
602
            totalrow  => $table{$row}->{totalrow}
604
            'totalrow'  => $table{$row}->{totalrow}
603
        };
605
          };
604
        $r->{totalrow} = sprintf("%.2f", $r->{totalrow}) if($r->{totalrow} and grep /$process/, (3,4,5));
605
        push @looprow, $r;
606
        $hilighted = -$hilighted;
606
        $hilighted = -$hilighted;
607
    }
607
    }
608
608
Lines 617-633 sub calculate { Link Here
617
                : $col->{coltitle}
617
                : $col->{coltitle}
618
              };
618
              };
619
        }
619
        }
620
        $total = sprintf("%.2f", $total) if($total and grep /$process/, (3,4,5));
620
621
621
        push @loopfooter, { 'totalcol' => $total };
622
        push @loopfooter, { 'totalcol' => $total };
622
    }
623
    }
623
624
624
    # the header of the table
625
    # the header of the table
625
	$globalline{loopfilter}=\@loopfilter;
626
    $globalline{loopfilter} = \@loopfilter;
626
    # the core of the table
627
    # the core of the table
627
    $globalline{looprow} = \@looprow;
628
    $globalline{looprow} = \@looprow;
628
    $globalline{loopcol} = \@loopcol;
629
    $globalline{loopcol} = \@loopcol;
629
630
630
    #       # the foot (totals by borrower type)
631
    #       # the foot (totals by borrower type)
632
    $grantotal = sprintf("%.2f", $grantotal) if ($grantotal and grep /$process/, (3,4,5));
631
    $globalline{loopfooter} = \@loopfooter;
633
    $globalline{loopfooter} = \@loopfooter;
632
    $globalline{total}      = $grantotal;
634
    $globalline{total}      = $grantotal;
633
    $globalline{line}       = $line;
635
    $globalline{line}       = $line;
634
- 

Return to bug 7896