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

(-)a/C4/Biblio.pm (+33 lines)
Lines 90-95 BEGIN { Link Here
90
      &GetAuthorisedValueDesc
90
      &GetAuthorisedValueDesc
91
      &GetMarcStructure
91
      &GetMarcStructure
92
      &GetMarcFromKohaField
92
      &GetMarcFromKohaField
93
      &GetMarcSubfieldStructureFromKohaField
93
      &GetFrameworkCode
94
      &GetFrameworkCode
94
      &TransformKohaToMarc
95
      &TransformKohaToMarc
95
      &PrepHostMarcField
96
      &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 51-57 Link Here
51
[% IF ( do_it ) %]
51
[% IF ( do_it ) %]
52
	[% FOREACH mainloo IN mainloop %]
52
	[% FOREACH mainloo IN mainloop %]
53
		<h1>Acquisitions statistics </h1>
53
		<h1>Acquisitions statistics </h1>
54
     		[% IF ( mainloo.loopfilter ) %]
54
            [% IF ( mainloo.loopfilter.size ) %]
55
			<p><b>Filtered on:</b></p>
55
			<p><b>Filtered on:</b></p>
56
			[% FOREACH loopfilte IN mainloo.loopfilter %]
56
			[% FOREACH loopfilte IN mainloo.loopfilter %]
57
					<p>[% loopfilte.crit %]: [% loopfilte.filter %]</p>
57
					<p>[% loopfilte.crit %]: [% loopfilte.filter %]</p>
Lines 157-162 Link Here
157
				<td><input type="radio" name="Column" value="aqbooksellers.name" /></td>
157
				<td><input type="radio" name="Column" value="aqbooksellers.name" /></td>
158
				<td>[% CGIBookSeller %]</td>
158
				<td>[% CGIBookSeller %]</td>
159
			</tr>
159
			</tr>
160
            <tr>
161
                <td>Home branch</td>
162
                <td><input type="radio" name="Line" value="items.homebranch" /></td>
163
                <td><input type="radio" name="Column" value="items.homebranch" /></td>
164
                <td>
165
                    <select name="Filter">
166
                        <option value="" selected="selected">All branches</option>
167
                        [% FOREACH branch IN branches %]
168
                            <option value="[% branch.branchcode %]">[% branch.branchname %]</option>
169
                        [% END %]
170
                    </select>
171
                </td>
172
            </tr>
173
            <tr>
174
                <td>
175
                    [% IF ccode_label %]
176
                        [% ccode_label %]
177
                    [% ELSE %]
178
                        Collection
179
                    [% END %]
180
                </td>
181
                <td><input type="radio" name="Line" value="items.ccode" /></td>
182
                <td><input type="radio" name="Column" value="items.ccode" /></td>
183
                <td>
184
                    [% IF ccode_avlist.size %]
185
                        <select name="Filter">
186
                            <option value="" selected="selected">All</option>
187
                            [% FOREACH ccode_av IN ccode_avlist %]
188
                                <option value="[% ccode_av.value %]">[% ccode_av.lib %]</option>
189
                            [% END %]
190
                        </select>
191
                    [% ELSE %]
192
                        <input type="text" name="Filter" />
193
                    [% END %]
194
                </td>
195
            </tr>
160
			<tr>
196
			<tr>
161
				<td>Item Type</td>
197
				<td>Item Type</td>
162
				<td><input type="radio" name="Line" value="biblioitems.itemtype" /></td>
198
				<td><input type="radio" name="Line" value="biblioitems.itemtype" /></td>
Lines 191-199 Link Here
191
	</table><br />
227
	</table><br />
192
	</fieldset>
228
	</fieldset>
193
229
194
<fieldset class="rows">		<legend> Cell value </legend>
230
<fieldset class="rows">
195
			<ol><li><label for="cellvalue1">Count items</label><input type="radio" name="Cellvalue" id="cellvalue1" value="1" checked="checked" /> </li>
231
    <legend> Cell value </legend>
196
			<li><label for="cellvalue2">Amount</label><input type="radio" name="Cellvalue" id="cellvalue2" value="2" /> </li></ol></fieldset>
232
    <ol>
233
        <li>
234
            <label for="cellvalue1">Count items</label><input type="radio" name="Cellvalue" id="cellvalue1" value="1" checked="checked" />
235
        </li>
236
        <li>
237
            <label for="cellvalue2">Count unique biblios</label><input type="radio" name="Cellvalue" id="cellvalue2" value="2" />
238
        </li>
239
        <li>
240
            <label for="cellvalue3">Amount</label><input type="radio" name="Cellvalue" id="cellvalue3" value="3" />
241
        </li>
242
        <li>
243
            <label for="cellvalue4">Ordered amount</label><input type="radio" name="Cellvalue" id="cellvalue4" value="4" />
244
        </li>
245
        <li>
246
            <label for="cellvalue5">Spent amount</label><input type="radio" name="Cellvalue" id="cellvalue5" value="5" />
247
        </li>
248
    </ol>
249
</fieldset>
197
250
198
	<fieldset class="rows">
251
	<fieldset class="rows">
199
	<legend>Output</legend>
252
	<legend>Output</legend>
(-)a/reports/acquisitions_stats.pl (-215 / +210 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]);
307
            }
308
            given ($i) {
309
                when (0)  { $cell{crit} = "Placed On From" }
310
                when (1)  { $cell{crit} = "Placed On To" }
311
                when (2)  { $cell{crit} = "Received On From" }
312
                when (3)  { $cell{crit} = "Received On To" }
313
                when (4)  { $cell{crit} = "Bookseller" }
314
                when (5)  { $cell{crit} = "Home branch" }
315
                when (6)  { $cell{crit} = "Collection" }
316
                when (7)  { $cell{crit} = "Doc Type" }
317
                when (8)  { $cell{crit} = "Budget" }
318
                when (9)  { $cell{crit} = "Sort1" }
319
                when (10) { $cell{crit} = "Sort2" }
320
                default   { $cell{crit} = "" }
285
            }
321
            }
286
            $cell{crit}   .= "Placed On From" if ( $i == 0 );
287
            $cell{crit}   .= "Placed On To" if ( $i == 1 );
288
            $cell{crit}   .= "Received On From" if ( $i == 2 );
289
            $cell{crit}   .= "Received On To" if ( $i == 3 );
290
291
#            $cell{crit} .= "Acquired On From" if ( $i == 4 );
292
#            $cell{crit} .= "Acquired On To"   if ( $i == 5 );
293
294
            $cell{crit} .= "BookSeller" if ( $i == 4 );
295
            $cell{crit} .= "Doc Type"   if ( $i == 5 );
296
            $cell{crit} .= "Budget"     if ( $i == 6 );
297
            $cell{crit} .= "Sort1"      if ( $i == 7 );
298
            $cell{crit} .= "Sort2"      if ( $i == 8 );
299
            push @loopfilter, \%cell;
322
            push @loopfilter, \%cell;
300
        }
323
        }
301
    }
324
    }
302
325
303
    my @linefilter;
326
    my %filter;
304
327
    my %field;
305
    $linefilter[0] = @$filters[0] if ( $line =~ /closedate/ );
328
    foreach ($line, $column) {
306
    $linefilter[1] = @$filters[1] if ( $line =~ /closedate/ );
329
        $filter{$_} = [];
307
    $linefilter[0] = @$filters[2] if ( $line =~ /received/ );
330
        $field{$_} = $_;
308
    $linefilter[1] = @$filters[3] if ( $line =~ /received/ );
331
        given ($_) {
309
332
            when (/closedate/) {
310
    $linefilter[0] = @$filters[4]  if ( $line =~ /bookseller/ );
333
                $filter{$_}->[0] = @$filters[0];
311
    $linefilter[0] = @$filters[5]  if ( $line =~ /itemtype/ );
334
                $filter{$_}->[1] = @$filters[1];
312
    $linefilter[0] = @$filters[6]  if ( $line =~ /budget/ );
335
                my $a = $_;
313
    $linefilter[0] = @$filters[7]  if ( $line =~ /sort1/ );
336
                given ($podsp) {
314
    $linefilter[0] = @$filters[8] if ( $line =~ /sort2/ );
337
                    when (1) { $field{$a} = "concat(hex(weekday($a)+1),'-',dayname($a))" }
315
338
                    when (2) { $field{$a} = "concat(hex(month($a)),'-',monthname($a))" }
316
    my @colfilter;
339
                    when (3) { $field{$a} = "Year($a)" }
317
    $colfilter[0] = @$filters[0] if ( $column =~ /closedate/ );
340
                    default  { $field{$a} = $a }
318
    $colfilter[1] = @$filters[1] if ( $column =~ /closedate/ );
341
                }
319
    $colfilter[0] = @$filters[2] if ( $column =~ /received/ );
342
            }
320
    $colfilter[1] = @$filters[3] if ( $column =~ /received/ );
343
            when (/received/) {
321
344
                $filter{$_}->[0] = @$filters[2];
322
    $colfilter[0] = @$filters[4]  if ( $column =~ /bookseller/ );
345
                $filter{$_}->[1] = @$filters[3];
323
    $colfilter[0] = @$filters[5]  if ( $column =~ /itemtype/ );
346
                my $a = $_;
324
    $colfilter[0] = @$filters[6]  if ( $column =~ /budget/ );
347
                given ($rodsp) {
325
    $colfilter[0] = @$filters[7]  if ( $column =~ /sort1/ );
348
                    when (1) { $field{$a} = "concat(hex(weekday($a)+1),'-',dayname($a))" }
326
    $colfilter[0] = @$filters[8]  if ( $column =~ /sort2/ );
349
                    when (2) { $field{$a} = "concat(hex(month($a)),'-',monthname($a))" }
327
350
                    when (3) { $field{$a} = "Year($a)" }
328
    # 1st, loop rows.
351
                    default  { $field{$a} = $a }
329
    my $linefield;
352
                }
330
    if ( ( $line =~ /closedate/ ) and ( $podsp == 1 ) ) {
353
            }
331
354
            when (/bookseller/) {
332
        #Display by day
355
                $filter{$_}->[0] = @$filters[4];
333
        $linefield .= "concat(hex(weekday($line)+1),'-',dayname($line))";
356
            }
334
    }
357
            when (/homebranch/) {
335
    elsif ( ( $line =~ /closedate/ ) and ( $podsp == 2 ) ) {
358
                $filter{$_}->[0] = @$filters[5];
336
359
            }
337
        #Display by Month
360
            when (/ccode/) {
338
        $linefield .= "concat(hex(month($line)),'-',monthname($line))";
361
                $filter{$_}->[0] = @$filters[6];
339
    }
362
            }
340
    elsif ( ( $line =~ /closedate/ ) and ( $podsp == 3 ) ) {
363
            when (/itemtype/) {
341
364
                $filter{$_}->[0] = @$filters[7];
342
        #Display by Year
365
            }
343
        $linefield .= "Year($line)";
366
            when (/budget/) {
344
367
                $filter{$_}->[0] = @$filters[8];
345
    }
368
            }
346
    elsif ( ( $line =~ /received/ ) and ( $rodsp == 1 ) ) {
369
            when (/sort1/) {
347
370
                $filter{$_}->[0] = @$filters[9];
348
        #Display by day
371
            }
349
        $linefield .= "concat(hex(weekday($line)+1),'-',dayname($line))";
372
            when (/sort2/) {
350
    }
373
                $filter{$_}->[0] = @$filters[10];
351
    elsif ( ( $line =~ /received/ ) and ( $rodsp == 2 ) ) {
374
            }
352
375
        }
353
        #Display by Month
354
        $linefield .= "concat(hex(month($line)),'-',monthname($line))";
355
    }
376
    }
356
    elsif ( ( $line =~ /received/ ) and ( $rodsp == 3 ) ) {
357
377
358
        #Display by Year
378
    my @linefilter = @{ $filter{$line} };
359
        $linefield .= "Year($line)";
379
    my $linefield = $field{$line};
380
    my @colfilter = @{ $filter{$column} };
381
    my $colfield = $field{$column};
360
382
361
    }
383
    # 1st, loop rows.
362
    else {
384
    my $strsth = "
363
        $linefield .= $line;
385
        SELECT DISTINCTROW $linefield
364
    }
386
        FROM aqorders
365
387
          LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno)
366
    my $strsth;
388
          LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber)
367
    $strsth .=
389
          LEFT JOIN items ON (aqorders_items.itemnumber = items.itemnumber)
368
      "SELECT DISTINCTROW $linefield FROM (aqorders, aqbasket )
390
          LEFT JOIN biblioitems ON (aqorders.biblionumber = biblioitems.biblionumber)
369
                LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber)
391
          LEFT JOIN aqbudgets  ON (aqorders.budget_id = aqbudgets.budget_id )
370
                LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber)
392
          LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id)
371
                LEFT JOIN aqbudgets  ON (aqorders.budget_id = aqbudgets.budget_id )
393
        WHERE $line IS NOT NULL AND $line <> '' ";
372
394
373
                LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) WHERE (aqorders.basketno=aqbasket.basketno)
395
    if (@linefilter) {
374
                AND $line IS NOT NULL AND $line <> '' ";
375
    
376
	if (@linefilter) {
377
        if ( $linefilter[1] ) {
396
        if ( $linefilter[1] ) {
378
            if ( $linefilter[0] ) {
397
            if ( $linefilter[0] ) {
379
                $strsth .= " AND $line BETWEEN ? AND ? ";
398
                $strsth .= " AND $line BETWEEN ? AND ? ";
Lines 408-469 sub calculate { Link Here
408
    else {
427
    else {
409
        $sth->execute;
428
        $sth->execute;
410
    }
429
    }
411
	while ( my ($celvalue) = $sth->fetchrow ) {
430
    while ( my ($celvalue) = $sth->fetchrow ) {
412
		my %cell;
431
        my %cell;
413
		if ($celvalue) {
432
        if ($celvalue) {
414
			$cell{rowtitle} = $celvalue;
433
            $cell{rowtitle} = $celvalue;
415
			push @loopline, \%cell;
434
            push @loopline, \%cell;
416
		}
435
        }
417
		$cell{totalrow} = 0;
436
        $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
    }
437
    }
456
438
457
    my $strsth2;
439
    # 2nd, loop cols.
458
    $strsth2 .=
440
    my $strsth2 = "
459
      "SELECT distinctrow $colfield FROM (aqorders, aqbasket )
441
        SELECT DISTINCTROW $colfield
460
                 LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber)
442
        FROM aqorders
461
                 LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber)
443
          LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno)
462
                 LEFT JOIN aqbudgets  ON (aqorders.budget_id = aqbudgets.budget_id )
444
          LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber)
463
445
          LEFT JOIN items ON (aqorders_items.itemnumber = items.itemnumber)
464
                 LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id)
446
          LEFT JOIN biblioitems ON (aqorders.biblionumber = biblioitems.biblionumber)
465
                 WHERE (aqorders.basketno=aqbasket.basketno) AND 
447
          LEFT JOIN aqbudgets  ON (aqorders.budget_id = aqbudgets.budget_id )
466
                 $column IS NOT NULL AND $column <> '' ";
448
          LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id)
449
        WHERE $column IS NOT NULL AND $column <> ''
450
    ";
467
451
468
    if (@colfilter) {
452
    if (@colfilter) {
469
        if ( $colfilter[1] ) {
453
        if ( $colfilter[1] ) {
Lines 488-494 sub calculate { Link Here
488
        }
472
        }
489
    }
473
    }
490
474
491
492
    $strsth2 .= " GROUP BY $colfield";
475
    $strsth2 .= " GROUP BY $colfield";
493
    $strsth2 .= " ORDER BY $colfield";
476
    $strsth2 .= " ORDER BY $colfield";
494
477
Lines 503-515 sub calculate { Link Here
503
    else {
486
    else {
504
        $sth2->execute;
487
        $sth2->execute;
505
    }
488
    }
506
	while ( my $celvalue = $sth2->fetchrow ) {
489
    while ( my $celvalue = $sth2->fetchrow ) {
507
		my %cell;
490
        my %cell;
508
		if ($celvalue) {
491
        if ($celvalue) {
509
			$cell{coltitle} = $celvalue;
492
            $cell{coltitle} = $celvalue;
510
			push @loopcol, \%cell;
493
            push @loopcol, \%cell;
511
		}
494
        }
512
	}
495
    }
513
496
514
    my $i = 0;
497
    my $i = 0;
515
    my @totalcol;
498
    my @totalcol;
Lines 528-545 sub calculate { Link Here
528
    # preparing calculation
511
    # preparing calculation
529
    my $strcalc;
512
    my $strcalc;
530
    $strcalc .= "SELECT $linefield, $colfield, ";
513
    $strcalc .= "SELECT $linefield, $colfield, ";
531
    $strcalc .= "SUM( aqorders.quantity ) " if ( $process == 1 );
514
    given ($process) {
532
    $strcalc .= "SUM( aqorders.quantity * aqorders.listprice ) "
515
        when (1) { $strcalc .= "COUNT(*) " }
533
      if ( $process == 2 );
516
        when (2) { $strcalc .= "COUNT(DISTINCT(aqorders.biblionumber)) " }
534
    $strcalc .= "FROM (aqorders, aqbasket )
517
        when ([3,4,5]) { $strcalc .= "SUM(aqorders.listprice) " }
535
                 LEFT JOIN items ON (aqorders.biblionumber= items.biblionumber)
518
        default { $strcalc .= "NULL " }
536
                 LEFT JOIN biblioitems ON (aqorders.biblionumber= biblioitems.biblionumber)
519
    }
537
                 LEFT JOIN aqbudgets  ON (aqorders.budget_id = aqbudgets.budget_id )
520
    $strcalc .= "
538
521
        FROM aqorders
539
                 LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) 
522
          LEFT JOIN aqbasket ON (aqorders.basketno = aqbasket.basketno)
540
                 WHERE (aqorders.basketno=aqbasket.basketno) ";
523
          LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber)
541
    
524
          LEFT JOIN items ON (aqorders_items.itemnumber = items.itemnumber)
542
	@$filters[0] =~ s/\*/%/g if ( @$filters[0] );
525
          LEFT JOIN biblioitems ON (aqorders.biblionumber = biblioitems.biblionumber)
526
          LEFT JOIN aqbudgets ON (aqorders.budget_id = aqbudgets.budget_id )
527
          LEFT JOIN aqbooksellers ON (aqbasket.booksellerid = aqbooksellers.id)
528
        WHERE aqorders.datecancellationprinted IS NULL ";
529
    $strcalc .= " AND (aqorders.datereceived IS NULL OR aqorders.datereceived = '') "
530
        if ( $process == 4 );
531
    $strcalc .= " AND aqorders.datereceived IS NOT NULL AND aqorders.datereceived <> '' "
532
        if ( $process == 5 );
533
    @$filters[0] =~ s/\*/%/g if ( @$filters[0] );
543
    $strcalc .= " AND aqbasket.closedate >= '" . @$filters[0] . "'"
534
    $strcalc .= " AND aqbasket.closedate >= '" . @$filters[0] . "'"
544
      if ( @$filters[0] );
535
      if ( @$filters[0] );
545
    @$filters[1] =~ s/\*/%/g if ( @$filters[1] );
536
    @$filters[1] =~ s/\*/%/g if ( @$filters[1] );
Lines 554-573 sub calculate { Link Here
554
    @$filters[4] =~ s/\*/%/g if ( @$filters[4] );
545
    @$filters[4] =~ s/\*/%/g if ( @$filters[4] );
555
    $strcalc .= " AND aqbooksellers.name LIKE '" . @$filters[4] . "'"
546
    $strcalc .= " AND aqbooksellers.name LIKE '" . @$filters[4] . "'"
556
      if ( @$filters[4] );
547
      if ( @$filters[4] );
557
    @$filters[5] =~ s/\*/%/g if ( @$filters[5] );
548
    $strcalc .= " AND items.homebranch = '" . @$filters[5] . "'"
558
    $strcalc .= " AND biblioitems.itemtype LIKE '" . @$filters[5] . "'"
559
      if ( @$filters[5] );
549
      if ( @$filters[5] );
560
    @$filters[6] =~ s/\*/%/g if ( @$filters[6] );
550
    @$filters[6] =~ s/\*/%/g if ( @$filters[6] );
561
    $strcalc .= " AND aqbudgets.budget_code LIKE '" . @$filters[6] . "'"
551
    $strcalc .= " AND items.ccode = '" . @$filters[6] . "'"
562
      if ( @$filters[6] );
552
      if ( @$filters[6] );
563
    @$filters[7] =~ s/\*/%/g if ( @$filters[7] );
553
    @$filters[7] =~ s/\*/%/g if ( @$filters[7] );
564
    $strcalc .= " AND aqorders.sort1 LIKE '" . @$filters[7] . "'"
554
    $strcalc .= " AND biblioitems.itemtype LIKE '" . @$filters[7] . "'"
565
      if ( @$filters[7] );
555
      if ( @$filters[7] );
566
    @$filters[8] =~ s/\*/%/g if ( @$filters[8] );
556
    @$filters[8] =~ s/\*/%/g if ( @$filters[8] );
567
    $strcalc .= " AND aqorders.sort2 LIKE '" . @$filters[8] . "'"
557
    $strcalc .= " AND aqbudgets.budget_code LIKE '" . @$filters[8] . "'"
568
      if ( @$filters[8] );
558
      if ( @$filters[8] );
569
559
    @$filters[9] =~ s/\*/%/g if ( @$filters[9] );
570
    $strcalc .= " AND aqorders.datecancellationprinted is NULL ";
560
    $strcalc .= " AND aqorders.sort1 LIKE '" . @$filters[9] . "'"
561
      if ( @$filters[9] );
562
    @$filters[10] =~ s/\*/%/g if ( @$filters[10] );
563
    $strcalc .= " AND aqorders.sort2 LIKE '" . @$filters[10] . "'"
564
      if ( @$filters[10] );
571
565
572
    $strcalc .= " GROUP BY $linefield, $colfield ORDER BY $linefield,$colfield";
566
    $strcalc .= " GROUP BY $linefield, $colfield ORDER BY $linefield,$colfield";
573
    my $dbcalc = $dbh->prepare($strcalc);
567
    my $dbcalc = $dbh->prepare($strcalc);
Lines 575-582 sub calculate { Link Here
575
569
576
    my $emptycol;
570
    my $emptycol;
577
    while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {
571
    while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {
578
		next if ($row eq undef || $col eq undef);
579
580
        $emptycol = 1         if ( !defined($col) );
572
        $emptycol = 1         if ( !defined($col) );
581
        $col      = "zzEMPTY" if ( !defined($col) );
573
        $col      = "zzEMPTY" if ( !defined($col) );
582
        $row      = "zzEMPTY" if ( !defined($row) );
574
        $row      = "zzEMPTY" if ( !defined($row) );
Lines 594-608 sub calculate { Link Here
594
        # and the number matches the number of columns
586
        # and the number matches the number of columns
595
        foreach my $col (@loopcol) {
587
        foreach my $col (@loopcol) {
596
            my $value = $table{$row}->{ ( $col->{coltitle} eq "NULL" ) ? "zzEMPTY" : $col->{coltitle} };
588
            my $value = $table{$row}->{ ( $col->{coltitle} eq "NULL" ) ? "zzEMPTY" : $col->{coltitle} };
589
            $value = sprintf("%.2f", $value) if($value and grep /$process/, (3,4,5));
597
            push @loopcell, { value => $value };
590
            push @loopcell, { value => $value };
598
        }
591
        }
599
        push @looprow,
592
        my $r = {
600
          {
593
            rowtitle => ( $row eq "zzEMPTY" ) ? "NULL" : $row,
601
            'rowtitle' => ( $row eq "zzEMPTY" ) ? "NULL" : $row,
594
            loopcell  => \@loopcell,
602
            'loopcell'  => \@loopcell,
595
            hilighted => ( $hilighted > 0 ),
603
            'hilighted' => ( $hilighted > 0 ),
596
            totalrow  => $table{$row}->{totalrow}
604
            'totalrow'  => $table{$row}->{totalrow}
597
        };
605
          };
598
        $r->{totalrow} = sprintf("%.2f", $r->{totalrow}) if($r->{totalrow} and grep /$process/, (3,4,5));
599
        push @looprow, $r;
606
        $hilighted = -$hilighted;
600
        $hilighted = -$hilighted;
607
    }
601
    }
608
602
Lines 617-633 sub calculate { Link Here
617
                : $col->{coltitle}
611
                : $col->{coltitle}
618
              };
612
              };
619
        }
613
        }
614
        $total = sprintf("%.2f", $total) if($total and grep /$process/, (3,4,5));
620
615
621
        push @loopfooter, { 'totalcol' => $total };
616
        push @loopfooter, { 'totalcol' => $total };
622
    }
617
    }
623
618
624
    # the header of the table
619
    # the header of the table
625
	$globalline{loopfilter}=\@loopfilter;
620
    $globalline{loopfilter} = \@loopfilter;
626
    # the core of the table
621
    # the core of the table
627
    $globalline{looprow} = \@looprow;
622
    $globalline{looprow} = \@looprow;
628
    $globalline{loopcol} = \@loopcol;
623
    $globalline{loopcol} = \@loopcol;
629
624
630
    #       # the foot (totals by borrower type)
625
    #       # the foot (totals by borrower type)
626
    $grantotal = sprintf("%.2f", $grantotal) if ($grantotal and grep /$process/, (3,4,5));
631
    $globalline{loopfooter} = \@loopfooter;
627
    $globalline{loopfooter} = \@loopfooter;
632
    $globalline{total}      = $grantotal;
628
    $globalline{total}      = $grantotal;
633
    $globalline{line}       = $line;
629
    $globalline{line}       = $line;
634
- 

Return to bug 7896