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

(-)a/reports/acquisitions_stats.pl (-91 / +12 lines)
Lines 47-56 my $fullreportname = "reports/acquisitions_stats.tmpl"; Link Here
47
my $line           = $input->param("Line");
47
my $line           = $input->param("Line");
48
my $column         = $input->param("Column");
48
my $column         = $input->param("Column");
49
my @filters        = $input->param("Filter");
49
my @filters        = $input->param("Filter");
50
$filters[0]= (($line =~ /closedate/ || $column =~ /closedate/) ? format_date_in_iso($filters[0]) : undef);
50
$filters[0] = format_date_in_iso( $filters[0] );
51
$filters[1]= (($line =~ /closedate/ || $column =~ /closedate/) ? format_date_in_iso($filters[1]) : undef);
51
$filters[1] = format_date_in_iso( $filters[1] );
52
$filters[2]= (($line =~ /delivery/ || $column =~ /delivery/) ? format_date_in_iso($filters[2]) : undef);
52
$filters[2] = format_date_in_iso( $filters[2] );
53
$filters[3]= (($line =~ /delivery/ || $column =~ /delivery/) ? format_date_in_iso($filters[3]) : undef);
53
$filters[3] = format_date_in_iso( $filters[3] );
54
my $podsp          = $input->param("PlacedOnDisplay");
54
my $podsp          = $input->param("PlacedOnDisplay");
55
my $rodsp          = $input->param("ReceivedOnDisplay");
55
my $rodsp          = $input->param("ReceivedOnDisplay");
56
my $aodsp          = $input->param("AcquiredOnDisplay");    ##added by mason.
56
my $aodsp          = $input->param("AcquiredOnDisplay");    ##added by mason.
Lines 58-64 my $calc = $input->param("Cellvalue"); Link Here
58
my $output         = $input->param("output");
58
my $output         = $input->param("output");
59
my $basename       = $input->param("basename");
59
my $basename       = $input->param("basename");
60
60
61
#warn "calcul : ".$calc;
62
my ($template, $borrowernumber, $cookie)
61
my ($template, $borrowernumber, $cookie)
63
	= get_template_and_user({template_name => $fullreportname,
62
	= get_template_and_user({template_name => $fullreportname,
64
				query => $input,
63
				query => $input,
Lines 303-358 sub calculate { Link Here
303
302
304
    my @linefilter;
303
    my @linefilter;
305
304
306
    #       warn "filtres ".@filters[0];
307
    #       warn "filtres ".@filters[1];
308
    #       warn "filtres ".@filters[2];
309
    #       warn "filtres ".@filters[3];
310
311
    $linefilter[0] = @$filters[0] if ( $line =~ /closedate/ );
305
    $linefilter[0] = @$filters[0] if ( $line =~ /closedate/ );
312
    $linefilter[1] = @$filters[1] if ( $line =~ /closedate/ );
306
    $linefilter[1] = @$filters[1] if ( $line =~ /closedate/ );
313
    $linefilter[0] = @$filters[2] if ( $line =~ /received/ );
307
    $linefilter[0] = @$filters[2] if ( $line =~ /received/ );
314
    $linefilter[1] = @$filters[3] if ( $line =~ /received/ );
308
    $linefilter[1] = @$filters[3] if ( $line =~ /received/ );
315
309
316
#    $linefilter[0] = @$filters[4] if ( $line =~ /acquired/ );
317
#    $linefilter[1] = @$filters[5] if ( $line =~ /acquired/ );
318
319
    $linefilter[0] = @$filters[4]  if ( $line =~ /bookseller/ );
310
    $linefilter[0] = @$filters[4]  if ( $line =~ /bookseller/ );
320
    $linefilter[0] = @$filters[5]  if ( $line =~ /itemtype/ );
311
    $linefilter[0] = @$filters[5]  if ( $line =~ /itemtype/ );
321
    $linefilter[0] = @$filters[6]  if ( $line =~ /budget/ );
312
    $linefilter[0] = @$filters[6]  if ( $line =~ /budget/ );
322
    $linefilter[0] = @$filters[7]  if ( $line =~ /sort1/ );
313
    $linefilter[0] = @$filters[7]  if ( $line =~ /sort1/ );
323
    $linefilter[0] = @$filters[8] if ( $line =~ /sort2/ );
314
    $linefilter[0] = @$filters[8] if ( $line =~ /sort2/ );
324
315
325
    #warn "filtre lignes".$linefilter[0]." ".$linefilter[1];
326
    #
327
    my @colfilter;
316
    my @colfilter;
328
    $colfilter[0] = @$filters[0] if ( $column =~ /closedate/ );
317
    $colfilter[0] = @$filters[0] if ( $column =~ /closedate/ );
329
    $colfilter[1] = @$filters[1] if ( $column =~ /closedate/ );
318
    $colfilter[1] = @$filters[1] if ( $column =~ /closedate/ );
330
    $colfilter[0] = @$filters[2] if ( $column =~ /received/ );
319
    $colfilter[0] = @$filters[2] if ( $column =~ /received/ );
331
    $colfilter[1] = @$filters[3] if ( $column =~ /received/ );
320
    $colfilter[1] = @$filters[3] if ( $column =~ /received/ );
332
321
333
#    $colfilter[0] = @$filters[4] if ( $column =~ /acquired/ );
334
#    $colfilter[1] = @$filters[5] if ( $column =~ /acquired/ );
335
    $colfilter[0] = @$filters[4]  if ( $column =~ /bookseller/ );
322
    $colfilter[0] = @$filters[4]  if ( $column =~ /bookseller/ );
336
    $colfilter[0] = @$filters[5]  if ( $column =~ /itemtype/ );
323
    $colfilter[0] = @$filters[5]  if ( $column =~ /itemtype/ );
337
    $colfilter[0] = @$filters[6]  if ( $column =~ /budget/ );
324
    $colfilter[0] = @$filters[6]  if ( $column =~ /budget/ );
338
    $colfilter[0] = @$filters[7]  if ( $column =~ /sort1/ );
325
    $colfilter[0] = @$filters[7]  if ( $column =~ /sort1/ );
339
    $colfilter[0] = @$filters[8]  if ( $column =~ /sort2/ );
326
    $colfilter[0] = @$filters[8]  if ( $column =~ /sort2/ );
340
327
341
    #warn "filtre col ".$colfilter[0]." ".$colfilter[1];
342
343
#    warn "line=$line, podsp=$podsp, rodsp=$rodsp, aodsp=$aodsp\n";
344
345
    # 1st, loop rows.
328
    # 1st, loop rows.
346
    my $linefield;
329
    my $linefield;
347
    if ( ( $line =~ /closedate/ ) and ( $podsp == 1 ) ) {
330
    if ( ( $line =~ /closedate/ ) and ( $podsp == 1 ) ) {
348
331
349
        #Display by day
332
        #Display by day
350
        $linefield .= "dayname($line)";
333
        $linefield .= "concat(hex(weekday($line)+1),'-',dayname($line))";
351
    }
334
    }
352
    elsif ( ( $line =~ /closedate/ ) and ( $podsp == 2 ) ) {
335
    elsif ( ( $line =~ /closedate/ ) and ( $podsp == 2 ) ) {
353
336
354
        #Display by Month
337
        #Display by Month
355
        $linefield .= "monthname($line)";
338
        $linefield .= "concat(hex(month($line)),'-',monthname($line))";
356
    }
339
    }
357
    elsif ( ( $line =~ /closedate/ ) and ( $podsp == 3 ) ) {
340
    elsif ( ( $line =~ /closedate/ ) and ( $podsp == 3 ) ) {
358
341
Lines 363-374 sub calculate { Link Here
363
    elsif ( ( $line =~ /received/ ) and ( $rodsp == 1 ) ) {
346
    elsif ( ( $line =~ /received/ ) and ( $rodsp == 1 ) ) {
364
347
365
        #Display by day
348
        #Display by day
366
        $linefield .= "dayname($line)";
349
        $linefield .= "concat(hex(weekday($line)+1),'-',dayname($line))";
367
    }
350
    }
368
    elsif ( ( $line =~ /received/ ) and ( $rodsp == 2 ) ) {
351
    elsif ( ( $line =~ /received/ ) and ( $rodsp == 2 ) ) {
369
352
370
        #Display by Month
353
        #Display by Month
371
        $linefield .= "monthname($line)";
354
        $linefield .= "concat(hex(month($line)),'-',monthname($line))";
372
    }
355
    }
373
    elsif ( ( $line =~ /received/ ) and ( $rodsp == 3 ) ) {
356
    elsif ( ( $line =~ /received/ ) and ( $rodsp == 3 ) ) {
374
357
Lines 376-397 sub calculate { Link Here
376
        $linefield .= "Year($line)";
359
        $linefield .= "Year($line)";
377
360
378
    }
361
    }
379
#    elsif ( ( $line =~ /acquired/ ) and ( $aodsp == 1 ) ) {
380
#
381
#        #Display by day
382
#        $linefield .= "dayname($line)";
383
#    }
384
#    elsif ( ( $line =~ /acquired/ ) and ( $aodsp == 2 ) ) {
385
#
386
#        #Display by Month
387
#        $linefield .= "monthname($line)";
388
#    }
389
#    elsif ( ( $line =~ /acquired/ ) and ( $aodsp == 3 ) ) {
390
#
391
#        #Display by Year
392
#        $linefield .= "Year($line)";
393
#
394
#    }
395
    else {
362
    else {
396
        $linefield .= $line;
363
        $linefield .= $line;
397
    }
364
    }
Lines 405-412 sub calculate { Link Here
405
372
406
                LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) WHERE (aqorders.basketno=aqbasket.basketno)
373
                LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) WHERE (aqorders.basketno=aqbasket.basketno)
407
                AND $line IS NOT NULL AND $line <> '' ";
374
                AND $line IS NOT NULL AND $line <> '' ";
408
409
#				LEFT JOIN aqorderdelivery ON (aqorders.ordernumber =aqorderdelivery.ordernumber )
410
    
375
    
411
	if (@linefilter) {
376
	if (@linefilter) {
412
        if ( $linefilter[1] ) {
377
        if ( $linefilter[1] ) {
Lines 421-427 sub calculate { Link Here
421
            ( $linefilter[0] )
386
            ( $linefilter[0] )
422
            and (  ( $line =~ /closedate/ )
387
            and (  ( $line =~ /closedate/ )
423
                or ( $line =~ /received/ ))
388
                or ( $line =~ /received/ ))
424
#                or ( $line =~ /acquired/ ) )
425
          )
389
          )
426
        {
390
        {
427
            $strsth .= " AND $line >= ? ";
391
            $strsth .= " AND $line >= ? ";
Lines 434-441 sub calculate { Link Here
434
    $strsth .= " GROUP BY $linefield";
398
    $strsth .= " GROUP BY $linefield";
435
    $strsth .= " ORDER BY $line";
399
    $strsth .= " ORDER BY $line";
436
400
437
    #warn "377:strsth= $strsth";
438
439
    my $sth = $dbh->prepare($strsth);
401
    my $sth = $dbh->prepare($strsth);
440
    if ( (@linefilter) and ( $linefilter[1] ) ) {
402
    if ( (@linefilter) and ( $linefilter[1] ) ) {
441
        $sth->execute( $linefilter[0], $linefilter[1] );
403
        $sth->execute( $linefilter[0], $linefilter[1] );
Lines 454-472 sub calculate { Link Here
454
		}
416
		}
455
		$cell{totalrow} = 0;
417
		$cell{totalrow} = 0;
456
	}
418
	}
457
#    warn "column=$column, podsp=$podsp, rodsp=$rodsp, aodsp=$aodsp\n";
458
459
    # 2nd, loop cols.
419
    # 2nd, loop cols.
460
    my $colfield;
420
    my $colfield;
461
    if ( ( $column =~ /closedate/ ) and ( $podsp == 1 ) ) {
421
    if ( ( $column =~ /closedate/ ) and ( $podsp == 1 ) ) {
462
422
463
        #Display by day
423
        #Display by day
464
        $colfield .= "dayname($column)";
424
        $colfield .= "concat(hex(weekday($column)+1),'-',dayname($column))";
465
    }
425
    }
466
    elsif ( ( $column =~ /closedate/ ) and ( $podsp == 2 ) ) {
426
    elsif ( ( $column =~ /closedate/ ) and ( $podsp == 2 ) ) {
467
427
468
        #Display by Month
428
        #Display by Month
469
        $colfield .= "monthname($column)";
429
        $colfield .= "concat(hex(month($column)),'-',monthname($column))";
470
    }
430
    }
471
    elsif ( ( $column =~ /closedate/ ) and ( $podsp == 3 ) ) {
431
    elsif ( ( $column =~ /closedate/ ) and ( $podsp == 3 ) ) {
472
432
Lines 477-488 sub calculate { Link Here
477
    elsif ( ( $column =~ /received/ ) and ( $rodsp == 1 ) ) {
437
    elsif ( ( $column =~ /received/ ) and ( $rodsp == 1 ) ) {
478
438
479
        #Display by day
439
        #Display by day
480
        $colfield .= "dayname($column)";
440
        $colfield .= "concat(hex(weekday($column)+1),'-',dayname($column))";
481
    }
441
    }
482
    elsif ( ( $column =~ /received/ ) and ( $rodsp == 2 ) ) {
442
    elsif ( ( $column =~ /received/ ) and ( $rodsp == 2 ) ) {
483
443
484
        #Display by Month
444
        #Display by Month
485
        $colfield .= "monthname($column)";
445
        $colfield .= "concat(hex(month($column)),'-',monthname($column))";
486
    }
446
    }
487
    elsif ( ( $column =~ /received/ ) and ( $rodsp == 3 ) ) {
447
    elsif ( ( $column =~ /received/ ) and ( $rodsp == 3 ) ) {
488
448
Lines 490-511 sub calculate { Link Here
490
        $colfield .= "Year($column)";
450
        $colfield .= "Year($column)";
491
451
492
    }
452
    }
493
#    elsif ( ( $column =~ /dateaccessioned/ ) and ( $aodsp == 1 ) ) {
494
#
495
#        #Display by day
496
#        $colfield .= "dayname($column)";
497
#    }
498
#    elsif ( ( $column =~ /dateaccessioned/ ) and ( $aodsp == 2 ) ) {
499
#
500
#        #Display by Month
501
#        $colfield .= "monthname($column)";
502
#    }
503
#    elsif ( ( $column =~ /dateaccessioned/ ) and ( $aodsp == 3 ) ) {
504
#
505
#        #Display by Year
506
#        $colfield .= "Year($column)";
507
#
508
#    }
509
    else {
453
    else {
510
        $colfield .= $column;
454
        $colfield .= $column;
511
    }
455
    }
Lines 521-528 sub calculate { Link Here
521
                 WHERE (aqorders.basketno=aqbasket.basketno) AND 
465
                 WHERE (aqorders.basketno=aqbasket.basketno) AND 
522
                 $column IS NOT NULL AND $column <> '' ";
466
                 $column IS NOT NULL AND $column <> '' ";
523
467
524
#				LEFT JOIN aqorderdelivery ON (aqorders.ordernumber =aqorderdelivery.ordernumber )
525
526
    if (@colfilter) {
468
    if (@colfilter) {
527
        if ( $colfilter[1] ) {
469
        if ( $colfilter[1] ) {
528
            if ( $colfilter[0] ) {
470
            if ( $colfilter[0] ) {
Lines 536-542 sub calculate { Link Here
536
            ( $colfilter[0] )
478
            ( $colfilter[0] )
537
            and (  ( $column =~ /closedate/ )
479
            and (  ( $column =~ /closedate/ )
538
                or ( $line =~ /received/ ))
480
                or ( $line =~ /received/ ))
539
#                or ( $line =~ /acquired/ ) )
540
          )
481
          )
541
        {
482
        {
542
            $strsth2 .= " AND $column >= ? ";
483
            $strsth2 .= " AND $column >= ? ";
Lines 579-585 sub calculate { Link Here
579
    #Initialization of cell values.....
520
    #Initialization of cell values.....
580
    my %table;
521
    my %table;
581
522
582
#	warn "init table...\n";
583
    foreach my $row (@loopline) {
523
    foreach my $row (@loopline) {
584
        foreach my $col (@loopcol) {
524
        foreach my $col (@loopcol) {
585
            $table{ $row->{rowtitle} }->{ $col->{coltitle} } = 0;
525
            $table{ $row->{rowtitle} }->{ $col->{coltitle} } = 0;
Lines 600-607 sub calculate { Link Here
600
540
601
                 LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) 
541
                 LEFT JOIN aqbooksellers ON (aqbasket.booksellerid=aqbooksellers.id) 
602
                 WHERE (aqorders.basketno=aqbasket.basketno) ";
542
                 WHERE (aqorders.basketno=aqbasket.basketno) ";
603
604
#                 LEFT JOIN aqorderdelivery ON (aqorders.ordernumber =aqorderdelivery.ordernumber )
605
    
543
    
606
	@$filters[0] =~ s/\*/%/g if ( @$filters[0] );
544
	@$filters[0] =~ s/\*/%/g if ( @$filters[0] );
607
    $strcalc .= " AND aqbasket.closedate >= '" . @$filters[0] . "'"
545
    $strcalc .= " AND aqbasket.closedate >= '" . @$filters[0] . "'"
Lines 615-626 sub calculate { Link Here
615
    @$filters[3] =~ s/\*/%/g if ( @$filters[3] );
553
    @$filters[3] =~ s/\*/%/g if ( @$filters[3] );
616
    $strcalc .= " AND aqorders.datereceived <= '" . @$filters[3] . "'"
554
    $strcalc .= " AND aqorders.datereceived <= '" . @$filters[3] . "'"
617
      if ( @$filters[3] );
555
      if ( @$filters[3] );
618
#    @$filters[4] =~ s/\*/%/g if ( @$filters[4] );
619
#    $strcalc .= " AND aqbasket.closedate >= '" . @$filters[4] . "'"
620
#      if ( @$filters[4] );
621
#    @$filters[5] =~ s/\*/%/g if ( @$filters[5] );
622
#    $strcalc .= " AND aqbasket.closedate <= '" . @$filters[5] . "'"
623
#      if ( @$filters[5] );
624
    @$filters[4] =~ s/\*/%/g if ( @$filters[4] );
556
    @$filters[4] =~ s/\*/%/g if ( @$filters[4] );
625
    $strcalc .= " AND aqbooksellers.name LIKE '" . @$filters[4] . "'"
557
    $strcalc .= " AND aqbooksellers.name LIKE '" . @$filters[4] . "'"
626
      if ( @$filters[4] );
558
      if ( @$filters[4] );
Lines 640-658 sub calculate { Link Here
640
    $strcalc .= " AND aqorders.datecancellationprinted is NULL ";
572
    $strcalc .= " AND aqorders.datecancellationprinted is NULL ";
641
573
642
    $strcalc .= " GROUP BY $linefield, $colfield ORDER BY $linefield,$colfield";
574
    $strcalc .= " GROUP BY $linefield, $colfield ORDER BY $linefield,$colfield";
643
644
#	warn $strcalc . "\n";
645
646
    my $dbcalc = $dbh->prepare($strcalc);
575
    my $dbcalc = $dbh->prepare($strcalc);
647
    $dbcalc->execute;
576
    $dbcalc->execute;
648
577
649
    #       warn "filling table";
650
    my $emptycol;
578
    my $emptycol;
651
    while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {
579
    while ( my ( $row, $col, $value ) = $dbcalc->fetchrow ) {
652
		next if ($row eq undef || $col eq undef);
580
		next if ($row eq undef || $col eq undef);
653
581
654
# warn "filling table $row / $col / $value ";
655
656
        $emptycol = 1         if ( !defined($col) );
582
        $emptycol = 1         if ( !defined($col) );
657
        $col      = "zzEMPTY" if ( !defined($col) );
583
        $col      = "zzEMPTY" if ( !defined($col) );
658
        $row      = "zzEMPTY" if ( !defined($row) );
584
        $row      = "zzEMPTY" if ( !defined($row) );
Lines 682-688 sub calculate { Link Here
682
        $hilighted = -$hilighted;
608
        $hilighted = -$hilighted;
683
    }
609
    }
684
610
685
    #       warn "footer processing";
686
    foreach my $col (@loopcol) {
611
    foreach my $col (@loopcol) {
687
        my $total = 0;
612
        my $total = 0;
688
        foreach my $row (@looprow) {
613
        foreach my $row (@looprow) {
Lines 693-703 sub calculate { Link Here
693
                ( $col->{coltitle} eq "NULL" ) ? "zzEMPTY"
618
                ( $col->{coltitle} eq "NULL" ) ? "zzEMPTY"
694
                : $col->{coltitle}
619
                : $col->{coltitle}
695
              };
620
              };
696
697
#                       warn "value added ".$table{$row->{rowtitle}}->{$col->{coltitle}}. "for line ".$row->{rowtitle};
698
        }
621
        }
699
622
700
        #               warn "summ for column ".$col->{coltitle}."  = ".$total;
701
        push @loopfooter, { 'totalcol' => $total };
623
        push @loopfooter, { 'totalcol' => $total };
702
    }
624
    }
703
625
704
- 

Return to bug 6115