|
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 |
- |
|
|