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

(-)a/C4/Reports/Guided.pm (-164 / +268 lines)
Lines 26-32 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); Link Here
26
use C4::Context;
26
use C4::Context;
27
use C4::Dates qw/format_date format_date_in_iso/;
27
use C4::Dates qw/format_date format_date_in_iso/;
28
use C4::Templates qw/themelanguage/;
28
use C4::Templates qw/themelanguage/;
29
use C4::Dates;
29
use C4::Koha;
30
use C4::Output;
30
use XML::Simple;
31
use XML::Simple;
31
use XML::Dumper;
32
use XML::Dumper;
32
use C4::Debug;
33
use C4::Debug;
Lines 34-108 use C4::Debug; Link Here
34
# use Data::Dumper;
35
# use Data::Dumper;
35
36
36
BEGIN {
37
BEGIN {
37
	# set the version for version checking
38
    # set the version for version checking
38
    $VERSION = 3.07.00.049;
39
    $VERSION = 3.07.00.049;
39
	require Exporter;
40
    require Exporter;
40
	@ISA = qw(Exporter);
41
    @ISA    = qw(Exporter);
41
	@EXPORT = qw(
42
    @EXPORT = qw(
42
		get_report_types get_report_areas get_columns build_query get_criteria
43
      get_report_types get_report_areas get_report_groups get_columns build_query get_criteria
43
	    save_report get_saved_reports execute_query get_saved_report create_compound run_compound
44
      save_report get_saved_reports execute_query get_saved_report create_compound run_compound
44
		get_column_type get_distinct_values save_dictionary get_from_dictionary
45
      get_column_type get_distinct_values save_dictionary get_from_dictionary
45
		delete_definition delete_report format_results get_sql
46
      delete_definition delete_report format_results get_sql
46
        nb_rows update_sql
47
      nb_rows update_sql build_authorised_value_list
47
	);
48
    );
48
}
49
}
49
50
50
our %table_areas;
51
=item get_report_areas()
51
$table_areas{'1'} =
52
  [ 'borrowers', 'statistics','items', 'biblioitems' ];    # circulation
53
$table_areas{'2'} = [ 'items', 'biblioitems', 'biblio' ];   # catalogue
54
$table_areas{'3'} = [ 'borrowers' ];        # patrons
55
$table_areas{'4'} = ['aqorders', 'biblio', 'items'];        # acquisitions
56
$table_areas{'5'} = [ 'borrowers', 'accountlines' ];        # accounts
57
our %keys;
58
$keys{'1'} = [
59
    'statistics.borrowernumber=borrowers.borrowernumber',
60
    'items.itemnumber = statistics.itemnumber',
61
    'biblioitems.biblioitemnumber = items.biblioitemnumber'
62
];
63
$keys{'2'} = [
64
    'items.biblioitemnumber=biblioitems.biblioitemnumber',
65
    'biblioitems.biblionumber=biblio.biblionumber'
66
];
67
$keys{'3'} = [ ];
68
$keys{'4'} = [
69
	'aqorders.biblionumber=biblio.biblionumber',
70
	'biblio.biblionumber=items.biblionumber'
71
];
72
$keys{'5'} = ['borrowers.borrowernumber=accountlines.borrowernumber'];
73
52
74
# have to do someting here to know if its dropdown, free text, date etc
53
This will return a list of all the available report areas
75
54
76
our %criteria;
55
=cut
77
# reports on circulation
56
78
$criteria{'1'} = [
57
my @REPORT_AREA = (
79
    'statistics.type',   'borrowers.categorycode',
58
    [CIRC => "Circulation"],
80
    'statistics.branch',
59
    [CAT  => "Catalogue"],
81
    'biblioitems.publicationyear|date',
60
    [PAT  => "Patrons"],
82
    'items.dateaccessioned|date'
61
    [ACQ  => "Acquisition"],
83
];
62
    [ACC  => "Accounts"],
84
# reports on catalogue
63
);
85
$criteria{'2'} =
64
my $AREA_NAME_SQL_SNIPPET
86
  [ 'items.itemnumber|textrange',   'items.biblionumber|textrange',   'items.barcode|textrange', 
65
  = "CASE report_area " .
87
    'biblio.frameworkcode',         'items.holdingbranch',            'items.homebranch', 
66
    join (" ", map "WHEN '$_->[0]' THEN '$_->[1]'", @REPORT_AREA) .
88
  'biblio.datecreated|daterange',   'biblio.timestamp|daterange',     'items.onloan|daterange', 
67
    " END AS areaname";
89
  'items.ccode',                    'items.itemcallnumber|textrange', 'items.itype', 
68
sub get_report_areas {
90
  'items.itemlost',                 'items.location' ];
69
    return \@REPORT_AREA
91
# reports on borrowers
70
}
92
$criteria{'3'} = ['borrowers.branchcode', 'borrowers.categorycode'];
71
93
# reports on acquisition
72
my %table_areas = (
94
$criteria{'4'} = ['aqorders.datereceived|date'];
73
    CIRC => [ 'borrowers', 'statistics', 'items', 'biblioitems' ],
95
74
    CAT  => [ 'items', 'biblioitems', 'biblio' ],
96
# reports on accounting
75
    PAT  => ['borrowers'],
97
$criteria{'5'} = ['borrowers.branchcode', 'borrowers.categorycode'];
76
    ACQ  => [ 'aqorders', 'biblio', 'items' ],
77
    ACC  => [ 'borrowers', 'accountlines' ],
78
);
79
my %keys = (
80
    CIRC => [ 'statistics.borrowernumber=borrowers.borrowernumber',
81
              'items.itemnumber = statistics.itemnumber',
82
              'biblioitems.biblioitemnumber = items.biblioitemnumber' ],
83
    CAT  => [ 'items.biblioitemnumber=biblioitems.biblioitemnumber',
84
              'biblioitems.biblionumber=biblio.biblionumber' ],
85
    PAT  => [],
86
    ACQ  => [ 'aqorders.biblionumber=biblio.biblionumber',
87
              'biblio.biblionumber=items.biblionumber' ],
88
    ACC  => ['borrowers.borrowernumber=accountlines.borrowernumber'],
89
);
90
91
# have to do someting here to know if its dropdown, free text, date etc
92
my %criteria = (
93
    CIRC => [ 'statistics.type', 'borrowers.categorycode', 'statistics.branch',
94
              'biblioitems.publicationyear|date', 'items.dateaccessioned|date' ],
95
    CAT  => [ 'items.itemnumber|textrange', 'items.biblionumber|textrange',
96
              'items.barcode|textrange', 'biblio.frameworkcode',
97
              'items.holdingbranch', 'items.homebranch',
98
              'biblio.datecreated|daterange', 'biblio.timestamp|daterange',
99
              'items.onloan|daterange', 'items.ccode',
100
              'items.itemcallnumber|textrange', 'items.itype', 'items.itemlost',
101
              'items.location' ],
102
    PAT  => [ 'borrowers.branchcode', 'borrowers.categorycode' ],
103
    ACQ  => ['aqorders.datereceived|date'],
104
    ACC  => [ 'borrowers.branchcode', 'borrowers.categorycode' ],
105
);
98
106
99
# Adds itemtypes to criteria, according to the syspref
107
# Adds itemtypes to criteria, according to the syspref
100
if (C4::Context->preference('item-level_itypes')) {
108
if ( C4::Context->preference('item-level_itypes') ) {
101
    unshift @{ $criteria{'1'} }, 'items.itype';
109
    unshift @{ $criteria{'CIRC'} }, 'items.itype';
102
    unshift @{ $criteria{'2'} }, 'items.itype';
110
    unshift @{ $criteria{'CAT'} }, 'items.itype';
103
} else {
111
} else {
104
    unshift @{ $criteria{'1'} }, 'biblioitems.itemtype';
112
    unshift @{ $criteria{'CIRC'} }, 'biblioitems.itemtype';
105
    unshift @{ $criteria{'2'} }, 'biblioitems.itemtype';
113
    unshift @{ $criteria{'CAT'} }, 'biblioitems.itemtype';
106
}
114
}
107
115
108
=head1 NAME
116
=head1 NAME
Lines 145-170 sub get_report_types { Link Here
145
153
146
}
154
}
147
155
148
=item get_report_areas()
156
=item get_report_groups()
149
157
150
This will return a list of all the available report areas
158
This will return a list of all the available report areas with groups
151
159
152
=cut
160
=cut
153
161
154
sub get_report_areas {
162
sub get_report_groups {
155
    my $dbh = C4::Context->dbh();
163
    my $dbh = C4::Context->dbh();
156
164
157
    # FIXME these should be in the database
165
    my $groups = GetAuthorisedValues('REPORT_GROUP');
158
    my @reports = ( 'Circulation', 'Catalog', 'Patrons', 'Acquisitions', 'Accounts');
166
    my $subgroups = GetAuthorisedValues('REPORT_SUBGROUP');
159
    my @reports2;
167
160
    for ( my $i = 0 ; $i < 5 ; $i++ ) {
168
    my %groups_with_subgroups = map { $_->{authorised_value} => {
161
        my %hashrep;
169
                        name => $_->{lib},
162
        $hashrep{id}   = $i + 1;
170
                        groups => {}
163
        $hashrep{name} = $reports[$i];
171
                    } } @$groups;
164
        push @reports2, \%hashrep;
172
    foreach (@$subgroups) {
173
        my $sg = $_->{authorised_value};
174
        my $g = $_->{lib_opac}
175
          or warn( qq{REPORT_SUBGROUP "$sg" without REPORT_GROUP (lib_opac)} ),
176
             next;
177
        my $g_sg = $groups_with_subgroups{$g}
178
          or warn( qq{REPORT_SUBGROUP "$sg" with invalid REPORT_GROUP "$g"} ),
179
             next;
180
        $g_sg->{subgroups}{$sg} = $_->{lib};
165
    }
181
    }
166
    return ( \@reports2 );
182
    return \%groups_with_subgroups
167
168
}
183
}
169
184
170
=item get_all_tables()
185
=item get_all_tables()
Lines 196-203 This will return a list of all columns for a report area Link Here
196
sub get_columns {
211
sub get_columns {
197
212
198
    # this calls the internal fucntion _get_columns
213
    # this calls the internal fucntion _get_columns
199
    my ($area,$cgi) = @_;
214
    my ( $area, $cgi ) = @_;
200
    my $tables = $table_areas{$area};
215
    my $tables = $table_areas{$area}
216
      or die qq{Unsuported report area "$area"};
217
201
    my @allcolumns;
218
    my @allcolumns;
202
    my $first = 1;
219
    my $first = 1;
203
    foreach my $table (@$tables) {
220
    foreach my $table (@$tables) {
Lines 383-389 sub nb_rows($) { Link Here
383
400
384
=item execute_query
401
=item execute_query
385
402
386
  ($results, $total, $error) = execute_query($sql, $offset, $limit)
403
  ($results, $error) = execute_query($sql, $offset, $limit)
387
404
388
405
389
When passed C<$sql>, this function returns an array ref containing a result set
406
When passed C<$sql>, this function returns an array ref containing a result set
Lines 472-508 Returns id of the newly created report Link Here
472
=cut
489
=cut
473
490
474
sub save_report {
491
sub save_report {
475
    my ( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public ) = @_;
492
    my ($fields) = @_;
476
    $cache_expiry ||= 300;
493
    my $borrowernumber = $fields->{borrowernumber};
494
    my $sql = $fields->{sql};
495
    my $name = $fields->{name};
496
    my $type = $fields->{type};
497
    my $notes = $fields->{notes};
498
    my $area = $fields->{area};
499
    my $group = $fields->{group};
500
    my $subgroup = $fields->{subgroup};
501
    my $cache_expiry = $fields->{cache_expiry} || 300;
502
    my $public = $fields->{public};
503
477
    my $dbh = C4::Context->dbh();
504
    my $dbh = C4::Context->dbh();
478
    $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/
505
    $sql =~ s/(\s*\;\s*)$//;    # removes trailing whitespace and /;/
479
    my $query =
506
    my $query = "INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,report_area,report_group,report_subgroup,type,notes,cache_expiry,public)  VALUES (?,now(),now(),?,?,?,?,?,?,?,?,?)";
480
"INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes,cache_expiry, public)  VALUES (?,now(),now(),?,?,?,?,?,?)";
507
    $dbh->do($query, undef, $borrowernumber, $sql, $name, $area, $group, $subgroup, $type, $notes, $cache_expiry, $public);
481
    $dbh->do( $query, undef, $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
508
482
    my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef,
509
    my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef,
483
                                   $borrowernumber, $name);
510
                                   $borrowernumber, $name);
484
    return $id;
511
    return $id;
485
}
512
}
486
513
487
sub update_sql {
514
sub update_sql {
488
    my $id = shift || croak "No Id given";
515
    my $id         = shift || croak "No Id given";
489
    my $sql = shift;
516
    my $fields     = shift;
490
    my $reportname = shift;
517
    my $sql = $fields->{sql};
491
    my $notes = shift;
518
    my $name = $fields->{name};
492
    my $cache_expiry = shift;
519
    my $notes = $fields->{notes};
493
    my $public = shift;
520
    my $group = $fields->{group};
494
521
    my $subgroup = $fields->{subgroup};
495
    # not entirely a magic number, Cache::Memcached::Set assumed any expiry >= (60*60*24*30) is an absolute unix timestamp (rather than relative seconds)
522
    my $cache_expiry = $fields->{cache_expiry};
523
    my $public = $fields->{public};
524
496
    if( $cache_expiry >= 2592000 ){
525
    if( $cache_expiry >= 2592000 ){
497
      die "Please specify a cache expiry less than 30 days\n";
526
      die "Please specify a cache expiry less than 30 days\n";
498
    }
527
    }
499
528
500
    my $dbh = C4::Context->dbh();
529
    my $dbh        = C4::Context->dbh();
501
    $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/
530
    $sql =~ s/(\s*\;\s*)$//;    # removes trailing whitespace and /;/
502
    my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, notes = ?, cache_expiry = ?, public = ? WHERE id = ? ";
531
    my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, report_group = ?, report_subgroup = ?, notes = ?, cache_expiry = ?, public = ? WHERE id = ? ";
503
    my $sth = $dbh->prepare($query);
532
    $dbh->do($query, undef, $sql, $name, $group, $subgroup, $notes, $cache_expiry, $public, $id );
504
    $sth->execute( $sql, $reportname, $notes, $cache_expiry, $public, $id );
505
    $sth->finish();
506
}
533
}
507
534
508
sub store_results {
535
sub store_results {
Lines 549-578 sub format_results { Link Here
549
}	
576
}	
550
577
551
sub delete_report {
578
sub delete_report {
552
	my ( $id ) = @_;
579
    my ($id)  = @_;
553
	my $dbh = C4::Context->dbh();
580
    my $dbh   = C4::Context->dbh();
554
	my $query = "DELETE FROM saved_sql WHERE id = ?";
581
    my $query = "DELETE FROM saved_sql WHERE id = ?";
555
	my $sth = $dbh->prepare($query);
582
    my $sth   = $dbh->prepare($query);
556
	$sth->execute($id);
583
    $sth->execute($id);
557
}	
584
}	
558
585
559
# $filter is either { date => $d, author => $a, keyword => $kw }
586
560
# or $keyword. Optional.
587
my $SAVED_REPORTS_BASE_QRY = <<EOQ;
588
SELECT s.*, r.report, r.date_run, $AREA_NAME_SQL_SNIPPET, av_g.lib AS groupname, av_sg.lib AS subgroupname,
589
b.firstname AS borrowerfirstname, b.surname AS borrowersurname
590
FROM saved_sql s
591
LEFT JOIN saved_reports r ON r.report_id = s.id
592
LEFT OUTER JOIN authorised_values av_g ON (av_g.category = 'REPORT_GROUP' AND av_g.authorised_value = s.report_group)
593
LEFT OUTER JOIN authorised_values av_sg ON (av_sg.category = 'REPORT_SUBGROUP' AND av_sg.lib_opac = s.report_group AND av_sg.authorised_value = s.report_subgroup)
594
LEFT OUTER JOIN borrowers b USING (borrowernumber)
595
EOQ
561
my $DATE_FORMAT = "%d/%m/%Y";
596
my $DATE_FORMAT = "%d/%m/%Y";
562
sub get_saved_reports {
597
sub get_saved_reports {
598
# $filter is either { date => $d, author => $a, keyword => $kw, }
599
# or $keyword. Optional.
563
    my ($filter) = @_;
600
    my ($filter) = @_;
564
    $filter = { keyword => $filter } if $filter && !ref( $filter );
601
    $filter = { keyword => $filter } if $filter && !ref( $filter );
602
    my ($group, $subgroup) = @_;
565
603
566
    my $dbh   = C4::Context->dbh();
604
    my $dbh   = C4::Context->dbh();
605
    my $query = $SAVED_REPORTS_BASE_QRY;
567
    my (@cond,@args);
606
    my (@cond,@args);
568
    my $query = "SELECT saved_sql.id, report_id, report,
569
                        date_run, date_created, last_modified, savedsql, last_run,
570
                        report_name, type, notes,
571
                        borrowernumber, surname as borrowersurname, firstname as borrowerfirstname,
572
                        cache_expiry, public
573
                 FROM saved_sql 
574
                 LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
575
                 LEFT OUTER JOIN borrowers USING (borrowernumber)";
576
    if ($filter) {
607
    if ($filter) {
577
        if (my $date = $filter->{date}) {
608
        if (my $date = $filter->{date}) {
578
            $date = format_date_in_iso($date);
609
            $date = format_date_in_iso($date);
Lines 596-601 sub get_saved_reports { Link Here
596
                         savedsql LIKE ?";
627
                         savedsql LIKE ?";
597
            push @args, $keyword, $keyword, $keyword, $keyword;
628
            push @args, $keyword, $keyword, $keyword, $keyword;
598
        }
629
        }
630
        if ($filter->{group}) {
631
            push @cond, "report_group = ?";
632
            push @args, $filter->{group};
633
        }
634
        if ($filter->{subgroup}) {
635
            push @cond, "report_subgroup = ?";
636
            push @args, $filter->{subgroup};
637
        }
599
    }
638
    }
600
    $query .= " WHERE ".join( " AND ", map "($_)", @cond ) if @cond;
639
    $query .= " WHERE ".join( " AND ", map "($_)", @cond ) if @cond;
601
    $query .= " ORDER by date_created";
640
    $query .= " ORDER by date_created";
Lines 609-615 sub get_saved_reports { Link Here
609
sub get_saved_report {
648
sub get_saved_report {
610
    my $dbh   = C4::Context->dbh();
649
    my $dbh   = C4::Context->dbh();
611
    my $query;
650
    my $query;
612
    my $sth;
613
    my $report_arg;
651
    my $report_arg;
614
    if ($#_ == 0 && ref $_[0] ne 'HASH') {
652
    if ($#_ == 0 && ref $_[0] ne 'HASH') {
615
        ($report_arg) = @_;
653
        ($report_arg) = @_;
Lines 628-637 sub get_saved_report { Link Here
628
    } else {
666
    } else {
629
        return;
667
        return;
630
    }
668
    }
631
    $sth   = $dbh->prepare($query);
669
    return $dbh->selectrow_hashref($query, undef, $report_arg);
632
    $sth->execute($report_arg);
633
    my $data = $sth->fetchrow_hashref();
634
    return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'}, $data->{'cache_expiry'}, $data->{'public'}, $data->{'id'} );
635
}
670
}
636
671
637
=item create_compound($masterID,$subreportID)
672
=item create_compound($masterID,$subreportID)
Lines 641-662 This will take 2 reports and create a compound report using both of them Link Here
641
=cut
676
=cut
642
677
643
sub create_compound {
678
sub create_compound {
644
	my ($masterID,$subreportID) = @_;
679
    my ( $masterID, $subreportID ) = @_;
645
	my $dbh = C4::Context->dbh();
680
    my $dbh = C4::Context->dbh();
646
	# get the reports
681
647
	my ($mastersql,$mastertype) = get_saved_report($masterID);
682
    # get the reports
648
	my ($subsql,$subtype) = get_saved_report($subreportID);
683
    my $master = get_saved_report($masterID);
649
	
684
    my $mastersql = $master->{savedsql};
650
	# now we have to do some checking to see how these two will fit together
685
    my $mastertype = $master->{type};
651
	# or if they will
686
    my $sub = get_saved_report($subreportID);
652
	my ($mastertables,$subtables);
687
    my $subsql = $master->{savedsql};
653
	if ($mastersql =~ / from (.*) where /i){ 
688
    my $subtype = $master->{type};
654
		$mastertables = $1;
689
655
	}
690
    # now we have to do some checking to see how these two will fit together
656
	if ($subsql =~ / from (.*) where /i){
691
    # or if they will
657
		$subtables = $1;
692
    my ( $mastertables, $subtables );
658
	}
693
    if ( $mastersql =~ / from (.*) where /i ) {
659
	return ($mastertables,$subtables);
694
        $mastertables = $1;
695
    }
696
    if ( $subsql =~ / from (.*) where /i ) {
697
        $subtables = $1;
698
    }
699
    return ( $mastertables, $subtables );
660
}
700
}
661
701
662
=item get_column_type($column)
702
=item get_column_type($column)
Lines 706-748 sub get_distinct_values { Link Here
706
}	
746
}	
707
747
708
sub save_dictionary {
748
sub save_dictionary {
709
	my ($name,$description,$sql,$area) = @_;
749
    my ( $name, $description, $sql, $area ) = @_;
710
	my $dbh = C4::Context->dbh();
750
    my $dbh   = C4::Context->dbh();
711
	my $query = "INSERT INTO reports_dictionary (name,description,saved_sql,area,date_created,date_modified)
751
    my $query = "INSERT INTO reports_dictionary (name,description,saved_sql,report_area,date_created,date_modified)
712
  VALUES (?,?,?,?,now(),now())";
752
  VALUES (?,?,?,?,now(),now())";
713
    my $sth = $dbh->prepare($query);
753
    my $sth = $dbh->prepare($query);
714
    $sth->execute($name,$description,$sql,$area) || return 0;
754
    $sth->execute($name,$description,$sql,$area) || return 0;
715
    return 1;
755
    return 1;
716
}
756
}
717
757
758
my $DICTIONARY_BASE_QRY = <<EOQ;
759
SELECT d.*, $AREA_NAME_SQL_SNIPPET
760
FROM reports_dictionary d
761
EOQ
718
sub get_from_dictionary {
762
sub get_from_dictionary {
719
	my ($area,$id) = @_;
763
    my ( $area, $id ) = @_;
720
	my $dbh = C4::Context->dbh();
764
    my $dbh   = C4::Context->dbh();
721
	my $query = "SELECT * FROM reports_dictionary";
765
    my $query = $DICTIONARY_BASE_QRY;
722
	if ($area){
766
    if ($area) {
723
		$query.= " WHERE area = ?";
767
        $query .= " WHERE report_area = ?";
724
	}
768
    } elsif ($id) {
725
	elsif ($id){
769
        $query .= " WHERE id = ?";
726
		$query.= " WHERE id = ?"
770
    }
727
	}
771
    my $sth = $dbh->prepare($query);
728
	my $sth = $dbh->prepare($query);
772
    if ($id) {
729
	if ($id){
773
        $sth->execute($id);
730
		$sth->execute($id);
774
    } elsif ($area) {
731
	}
775
        $sth->execute($area);
732
	elsif ($area) {
776
    } else {
733
		$sth->execute($area);
777
        $sth->execute();
734
	}
778
    }
735
	else {
779
    my @loop;
736
		$sth->execute();
780
    while ( my $data = $sth->fetchrow_hashref() ) {
737
	}
781
        push @loop, $data;
738
	my @loop;
782
    }
739
	my @reports = ( 'Circulation', 'Catalog', 'Patrons', 'Acquisitions', 'Accounts');
783
    return ( \@loop );
740
	while (my $data = $sth->fetchrow_hashref()){
741
		$data->{'areaname'}=$reports[$data->{'area'}-1];
742
		push @loop,$data;
743
		
744
	}
745
	return (\@loop);
746
}
784
}
747
785
748
sub delete_definition {
786
sub delete_definition {
Lines 782-787 sub _get_column_defs { Link Here
782
	close COLUMNS;
820
	close COLUMNS;
783
	return \%columns;
821
	return \%columns;
784
}
822
}
823
824
=item build_authorised_value_list($authorised_value)
825
826
Returns an arrayref - hashref pair. The hashref consists of
827
various code => name lists depending on the $authorised_value.
828
The arrayref is the hashref keys, in appropriate order
829
830
=cut
831
832
sub build_authorised_value_list {
833
    my ( $authorised_value ) = @_;
834
835
    my $dbh = C4::Context->dbh;
836
    my @authorised_values;
837
    my %authorised_lib;
838
839
    # builds list, depending on authorised value...
840
    if ( $authorised_value eq "branches" ) {
841
        my $branches = GetBranchesLoop();
842
        foreach my $thisbranch (@$branches) {
843
            push @authorised_values, $thisbranch->{value};
844
            $authorised_lib{ $thisbranch->{value} } = $thisbranch->{branchname};
845
        }
846
    } elsif ( $authorised_value eq "itemtypes" ) {
847
        my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
848
        $sth->execute;
849
        while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
850
            push @authorised_values, $itemtype;
851
            $authorised_lib{$itemtype} = $description;
852
        }
853
    } elsif ( $authorised_value eq "cn_source" ) {
854
        my $class_sources  = GetClassSources();
855
        my $default_source = C4::Context->preference("DefaultClassificationSource");
856
        foreach my $class_source ( sort keys %$class_sources ) {
857
            next
858
              unless $class_sources->{$class_source}->{'used'}
859
                  or ( $class_source eq $default_source );
860
            push @authorised_values, $class_source;
861
            $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
862
        }
863
    } elsif ( $authorised_value eq "categorycode" ) {
864
        my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description");
865
        $sth->execute;
866
        while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) {
867
            push @authorised_values, $categorycode;
868
            $authorised_lib{$categorycode} = $description;
869
        }
870
871
        #---- "true" authorised value
872
    } else {
873
        my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");
874
875
        $authorised_values_sth->execute($authorised_value);
876
877
        while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
878
            push @authorised_values, $value;
879
            $authorised_lib{$value} = $lib;
880
881
            # For item location, we show the code and the libelle
882
            $authorised_lib{$value} = $lib;
883
        }
884
    }
885
886
    return (\@authorised_values, \%authorised_lib);
887
}
888
785
1;
889
1;
786
__END__
890
__END__
787
891
(-)a/admin/authorised_values.pl (-11 / +12 lines)
Lines 188-204 output_html_with_http_headers $input, $cookie, $template->output; Link Here
188
exit 0;
188
exit 0;
189
189
190
sub default_form {
190
sub default_form {
191
	# build categories list
191
    # build categories list
192
	my $sth = $dbh->prepare("select distinct category from authorised_values");
192
    my $sth = $dbh->prepare("select distinct category from authorised_values");
193
	$sth->execute;
193
    $sth->execute;
194
	my @category_list;
194
    my @category_list;
195
	my %categories;     # a hash, to check that some hardcoded categories exist.
195
    my %categories;    # a hash, to check that some hardcoded categories exist.
196
	while ( my ($category) = $sth->fetchrow_array) {
196
    while ( my ($category) = $sth->fetchrow_array ) {
197
		push(@category_list,$category);
197
        push( @category_list, $category );
198
		$categories{$category} = 1;
198
        $categories{$category} = 1;
199
	}
199
    }
200
	# push koha system categories
200
201
    foreach (qw(Asort1 Asort2 Bsort1 Bsort2 SUGGEST DAMAGED LOST)) {
201
    # push koha system categories
202
    foreach (qw(Asort1 Asort2 Bsort1 Bsort2 SUGGEST DAMAGED LOST REPORT_GROUP REPORT_SUBGROUP)) {
202
        push @category_list, $_ unless $categories{$_};
203
        push @category_list, $_ unless $categories{$_};
203
    }
204
    }
204
205
(-)a/installer/data/mysql/kohastructure.sql (-3 / +8 lines)
Lines 97-103 CREATE TABLE `auth_types` ( Link Here
97
DROP TABLE IF EXISTS `authorised_values`;
97
DROP TABLE IF EXISTS `authorised_values`;
98
CREATE TABLE `authorised_values` ( -- stores values for authorized values categories and values
98
CREATE TABLE `authorised_values` ( -- stores values for authorized values categories and values
99
  `id` int(11) NOT NULL auto_increment, -- unique key, used to identify the authorized value
99
  `id` int(11) NOT NULL auto_increment, -- unique key, used to identify the authorized value
100
  `category` varchar(10) NOT NULL default '', -- key used to identify the authorized value category
100
  `category` varchar(16) NOT NULL default '', -- key used to identify the authorized value category
101
  `authorised_value` varchar(80) NOT NULL default '', -- code use to identify the authorized value
101
  `authorised_value` varchar(80) NOT NULL default '', -- code use to identify the authorized value
102
  `lib` varchar(80) default NULL, -- authorized value description as printed in the staff client
102
  `lib` varchar(80) default NULL, -- authorized value description as printed in the staff client
103
  `lib_opac` VARCHAR(80) default NULL, -- authorized value description as printed in the OPAC
103
  `lib_opac` VARCHAR(80) default NULL, -- authorized value description as printed in the OPAC
Lines 1624-1631 CREATE TABLE reports_dictionary ( -- definitions (or snippets of SQL) stored for Link Here
1624
   `date_created` datetime default NULL, -- date and time this definition was created
1624
   `date_created` datetime default NULL, -- date and time this definition was created
1625
   `date_modified` datetime default NULL, -- date and time this definition was last modified
1625
   `date_modified` datetime default NULL, -- date and time this definition was last modified
1626
   `saved_sql` text, -- SQL snippet for us in reports
1626
   `saved_sql` text, -- SQL snippet for us in reports
1627
   `area` int(11) default NULL, -- Koha module this definition is for (1 = Circulation, 2 = Catalog, 3 = Patrons, 4 = Acquistions, 5 = Accounts)
1627
   report_area varchar(6) DEFAULT NULL, -- Koha module this definition is for Circulation, Catalog, Patrons, Acquistions, Accounts)
1628
   PRIMARY KEY  (`id`)
1628
   PRIMARY KEY  (id),
1629
   KEY dictionary_area_idx (report_area)
1629
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1630
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1630
1631
1631
--
1632
--
Lines 1723-1729 CREATE TABLE saved_sql ( -- saved sql reports Link Here
1723
   `notes` text, -- the notes or description given to this report
1724
   `notes` text, -- the notes or description given to this report
1724
   `cache_expiry` int NOT NULL default 300,
1725
   `cache_expiry` int NOT NULL default 300,
1725
   `public` boolean NOT NULL default FALSE,
1726
   `public` boolean NOT NULL default FALSE,
1727
    report_area varchar(6) default NULL,
1728
    report_group varchar(80) default NULL,
1729
    report_subgroup varchar(80) default NULL,
1726
   PRIMARY KEY  (`id`),
1730
   PRIMARY KEY  (`id`),
1731
   KEY sql_area_group_idx (report_group, report_subgroup),
1727
   KEY boridx (`borrowernumber`)
1732
   KEY boridx (`borrowernumber`)
1728
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1733
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1729
1734
(-)a/installer/data/mysql/updatedatabase.pl (+32 lines)
Lines 5635-5640 if(C4::Context->preference("Version") < TransformToNum($DBversion) ) { Link Here
5635
    SetVersion($DBversion);
5635
    SetVersion($DBversion);
5636
}
5636
}
5637
5637
5638
5639
5640
$DBversion = "3.09.00.XXX";
5641
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
5642
    $dbh->do("ALTER TABLE authorised_values MODIFY category varchar(16) NOT NULL DEFAULT '';");
5643
    $dbh->do("INSERT INTO authorised_values (category, authorised_value, lib) VALUES
5644
              ('REPORT_GROUP', 'CIRC', 'Circulation'),
5645
              ('REPORT_GROUP', 'CAT', 'Catalog'),
5646
              ('REPORT_GROUP', 'PAT', 'Patrons'),
5647
              ('REPORT_GROUP', 'ACQ', 'Acquisitions'),
5648
              ('REPORT_GROUP', 'ACC', 'Accounts');");
5649
5650
    $dbh->do("ALTER TABLE reports_dictionary ADD report_area varchar(6) DEFAULT NULL;");
5651
    $dbh->do("UPDATE reports_dictionary SET report_area = CASE area
5652
                  WHEN 1 THEN 'CIRC'
5653
                  WHEN 2 THEN 'CAT'
5654
                  WHEN 3 THEN 'PAT'
5655
                  WHEN 4 THEN 'ACQ'
5656
                  WHEN 5 THEN 'ACC'
5657
                  END;");
5658
    $dbh->do("ALTER TABLE reports_dictionary DROP area;");
5659
    $dbh->do("ALTER TABLE reports_dictionary ADD KEY dictionary_area_idx (report_area);");
5660
5661
    $dbh->do("ALTER TABLE saved_sql ADD report_area varchar(6) DEFAULT NULL;");
5662
    $dbh->do("ALTER TABLE saved_sql ADD report_group varchar(80) DEFAULT NULL;");
5663
    $dbh->do("ALTER TABLE saved_sql ADD report_subgroup varchar(80) DEFAULT NULL;");
5664
    $dbh->do("ALTER TABLE saved_sql ADD KEY sql_area_group_idx (report_group, report_subgroup);");
5665
5666
    print "Upgrade to $DBversion done saved_sql new fields report_group and report_area; authorised_values.category 16 char \n";
5667
    SetVersion($DBversion);
5668
}
5669
5638
=head1 FUNCTIONS
5670
=head1 FUNCTIONS
5639
5671
5640
=head2 TableExists($table)
5672
=head2 TableExists($table)
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/dictionary.tt (-2 / +2 lines)
Lines 33-39 Link Here
33
		<form action="/cgi-bin/koha/reports/dictionary.pl" method="post">
33
		<form action="/cgi-bin/koha/reports/dictionary.pl" method="post">
34
        <input type="hidden" name="phase" value="View Dictionary" />
34
        <input type="hidden" name="phase" value="View Dictionary" />
35
		[% IF ( areas ) %]
35
		[% IF ( areas ) %]
36
			Filter by area <select name="areas">
36
			Filter by area <select name="area">
37
			<option value="">All</option>
37
			<option value="">All</option>
38
			[% FOREACH area IN areas %]
38
			[% FOREACH area IN areas %]
39
			    [% IF ( area.selected ) %]
39
			    [% IF ( area.selected ) %]
Lines 103-109 Link Here
103
<ol><li><input type="hidden" name="phase" value="New Term step 3" />
103
<ol><li><input type="hidden" name="phase" value="New Term step 3" />
104
<input type="hidden" name="definition_name" value="[% definition_name %]" />
104
<input type="hidden" name="definition_name" value="[% definition_name %]" />
105
<input type="hidden" name="definition_description" value="[% definition_description %]" />
105
<input type="hidden" name="definition_description" value="[% definition_description %]" />
106
<label for="areas">Select table </label><select name="areas" id="areas">
106
<label for="area">Select table </label><select name="area" id="area">
107
[% FOREACH area IN areas %]     
107
[% FOREACH area IN areas %]     
108
<option value="[% area.id %]">[% area.name %]</option>                  
108
<option value="[% area.id %]">[% area.name %]</option>                  
109
[% END %]                
109
[% END %]                
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-1 / +143 lines)
Lines 24-29 Link Here
24
24
25
<script type="text/javascript">
25
<script type="text/javascript">
26
//<![CDATA[
26
//<![CDATA[
27
var group_subgroups = {};
28
var no_subgroup_label = _( "(None)" );
29
function load_group_subgroups () {
30
    var group = $("#group").val();
31
    var sg = $("#subgroup");
32
    var has_subgroups = false;
33
    $(sg).empty().append('<option value="">' + no_subgroup_label + '</option>');
34
    if (group) {
35
        $.each( group_subgroups[group], function(index, value) {
36
                has_subgroups = true;
37
            $('<option value="' + value[0] + '">' + value[1] + '</option>').appendTo(sg);
38
        } );
39
    }
40
    if (has_subgroups) {
41
        $(sg).show();
42
    } else {
43
        $(sg).hide();
44
    }
45
}
27
$(document).ready(function(){
46
$(document).ready(function(){
28
[% IF ( showsql ) %]
47
[% IF ( showsql ) %]
29
    $("#sql").focus(function() {
48
    $("#sql").focus(function() {
Lines 135-140 canned reports and writing custom SQL reports.</p> Link Here
135
  <th>ID</th>
154
  <th>ID</th>
136
  <th>Report name</th>
155
  <th>Report name</th>
137
  <th>Type</th>
156
  <th>Type</th>
157
  <th>Area</th>
158
  <th>Group</th>
159
  <th>Subgroup</th>
138
  <th>Notes</th>
160
  <th>Notes</th>
139
  <th>Author</th>
161
  <th>Author</th>
140
  <th>Creation date</th>
162
  <th>Creation date</th>
Lines 152-157 canned reports and writing custom SQL reports.</p> Link Here
152
<td>[% savedreport.id %]</td>
174
<td>[% savedreport.id %]</td>
153
<td>[% savedreport.report_name %]</td>
175
<td>[% savedreport.report_name %]</td>
154
<td>[% savedreport.type %]</td>
176
<td>[% savedreport.type %]</td>
177
<td>[% savedreport.areaname %]</td>
178
<td>[% savedreport.groupname %]</td>
179
<td>[% savedreport.subgroupname %]</td>
155
<td>[% savedreport.notes %]</td>
180
<td>[% savedreport.notes %]</td>
156
<td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td>
181
<td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td>
157
<td>[% savedreport.date_created %]</td>
182
<td>[% savedreport.date_created %]</td>
Lines 216-222 canned reports and writing custom SQL reports.</p> Link Here
216
<form action="/cgi-bin/koha/reports/guided_reports.pl">
241
<form action="/cgi-bin/koha/reports/guided_reports.pl">
217
<fieldset class="rows">
242
<fieldset class="rows">
218
<legend>Step 1 of 6: Choose a module to report on,[% IF (usecache) %] Set cache expiry, [% END %] and Choose report visibility </legend>
243
<legend>Step 1 of 6: Choose a module to report on,[% IF (usecache) %] Set cache expiry, [% END %] and Choose report visibility </legend>
219
<ol><li><label for="areas">Choose: </label><select name="areas" id="areas">
244
<ol><li><label for="area">Choose: </label><select name="area" id="area">
220
[% FOREACH area IN areas %]
245
[% FOREACH area IN areas %]
221
<option value="[% area.id %]">[% area.name %]</option>
246
<option value="[% area.id %]">[% area.name %]</option>
222
[% END %]
247
[% END %]
Lines 482-493 canned reports and writing custom SQL reports.</p> Link Here
482
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
507
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
483
<input type="hidden" name="sql" value="[% sql |html %]" />
508
<input type="hidden" name="sql" value="[% sql |html %]" />
484
<input type="hidden" name="type" value="[% type %]" />
509
<input type="hidden" name="type" value="[% type %]" />
510
<input type="hidden" name="area" value="[% area %]" />
485
<input type="hidden" name="public" value="[% public %]" />
511
<input type="hidden" name="public" value="[% public %]" />
486
<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
512
<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
487
<fieldset class="rows">
513
<fieldset class="rows">
488
<legend>Save your custom report</legend>
514
<legend>Save your custom report</legend>
489
<ol>
515
<ol>
490
    <li><label for="reportname">Report name: </label><input type="text" id="reportname" name="reportname" /></li>
516
    <li><label for="reportname">Report name: </label><input type="text" id="reportname" name="reportname" /></li>
517
    [% IF groups_with_subgroups %]
518
    <li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
519
        [% FOR g IN groups_with_subgroups %]
520
            [% IF g.selected %]
521
    <option value="[% g.id %]" selected>[% g.name %]</option>
522
            [% ELSE %]
523
    <option value="[% g.id %]">[% g.name %]</option>
524
            [% END %]
525
    <script type="text/javascript">
526
        var g_sg = new Array();
527
            [% FOR sg IN g.subgroups %]
528
        g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
529
                [% IF sg.selected %]
530
        $(document).ready(function() {
531
            $("#subgroup").val("[% sg.id %]");
532
        });
533
                [% END %]
534
            [% END %]
535
        group_subgroups["[% g.id %]"] = g_sg;
536
    </script>
537
        [% END %]
538
    </select></li>
539
    <li><label for="subgroup">Report subgroup: </label><select name="subgroup" id="subgroup">
540
    </select></li>
541
    [% END %]
491
    <li><label for="notes">Notes:</label> <textarea name="notes" id="notes"></textarea></li>
542
    <li><label for="notes">Notes:</label> <textarea name="notes" id="notes"></textarea></li>
492
</ol></fieldset>
543
</ol></fieldset>
493
<fieldset class="action"><input type="hidden" name="phase" value="Save Report" />
544
<fieldset class="action"><input type="hidden" name="phase" value="Save Report" />
Lines 544-549 canned reports and writing custom SQL reports.</p> Link Here
544
[% END %]
595
[% END %]
545
596
546
[% IF ( create ) %]
597
[% IF ( create ) %]
598
<script type="text/javascript">
599
$(document).ready(function() {
600
    load_group_subgroups();
601
});
602
</script>
547
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
603
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
548
<fieldset class="rows">
604
<fieldset class="rows">
549
<legend>Create report from SQL</legend>
605
<legend>Create report from SQL</legend>
Lines 552-557 canned reports and writing custom SQL reports.</p> Link Here
552
        [% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />
608
        [% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />
553
        [% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %] 
609
        [% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %] 
554
    </li>
610
    </li>
611
    [% IF groups_with_subgroups %]
612
    <li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
613
        [% FOR g IN groups_with_subgroups %]
614
            [% IF g.selected %]
615
    <option value="[% g.id %]" selected>[% g.name %]</option>
616
            [% ELSE %]
617
    <option value="[% g.id %]">[% g.name %]</option>
618
            [% END %]
619
    <script type="text/javascript">
620
        var g_sg = new Array();
621
            [% FOR sg IN g.subgroups %]
622
        g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
623
                [% IF sg.selected %]
624
        $(document).ready(function() {
625
            $("#subgroup").val("[% sg.id %]");
626
        });
627
                [% END %]
628
            [% END %]
629
        group_subgroups["[% g.id %]"] = g_sg;
630
    </script>
631
        [% END %]
632
    </select></li>
633
    <li><label for="subgroup">Report subgroup: </label><select name="subgroup" id="subgroup">
634
    </select></li>
635
    [% END %]
555
[% IF (public) %]
636
[% IF (public) %]
556
  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>
637
  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>
557
[% ELSE %]
638
[% ELSE %]
Lines 636-641 Sub report:<select name="subreport"> Link Here
636
[% END %]
717
[% END %]
637
718
638
[% IF ( editsql ) %]
719
[% IF ( editsql ) %]
720
<script type="text/javascript">
721
$(document).ready(function() {
722
    load_group_subgroups();
723
});
724
</script>
639
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
725
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
640
<input type="hidden" name="phase" value="Update SQL" />
726
<input type="hidden" name="phase" value="Update SQL" />
641
<input type="hidden" name="id" value="[% id %]"/>
727
<input type="hidden" name="id" value="[% id %]"/>
Lines 643-648 Sub report:<select name="subreport"> Link Here
643
<legend>Edit SQL report</legend>
729
<legend>Edit SQL report</legend>
644
<ol>
730
<ol>
645
<li><label for="reportname">Report name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" size="50" /></li>
731
<li><label for="reportname">Report name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" size="50" /></li>
732
    [% IF groups_with_subgroups %]
733
    <li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
734
        [% FOR g IN groups_with_subgroups %]
735
            [% IF g.selected %]
736
    <option value="[% g.id %]" selected>[% g.name %]</option>
737
            [% ELSE %]
738
    <option value="[% g.id %]">[% g.name %]</option>
739
            [% END %]
740
    <script type="text/javascript">
741
        var g_sg = new Array();
742
            [% FOR sg IN g.subgroups %]
743
        g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
744
                [% IF sg.selected %]
745
        $(document).ready(function() {
746
            $("#subgroup").val("[% sg.id %]");
747
        });
748
                [% END %]
749
            [% END %]
750
        group_subgroups["[% g.id %]"] = g_sg;
751
    </script>
752
        [% END %]
753
    </select></li>
754
    <li><label for="subgroup">Report subgroup: </label><select name="subgroup" id="subgroup">
755
    </select></li>
756
    [% END %]
646
[% IF (public) %]
757
[% IF (public) %]
647
  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>
758
  <li><label for="public">Report is public:</label><select id="public" name="public"> <option value="0">No (default)</option> <option value="1" selected="selected">Yes</public> </select></li>
648
[% ELSE %]
759
[% ELSE %]
Lines 710-721 Sub report:<select name="subreport"> Link Here
710
821
711
[% IF ( saved1 ) %]
822
[% IF ( saved1 ) %]
712
<div id="saved-reports-filter">
823
<div id="saved-reports-filter">
824
<script type="text/javascript">
825
$(document).ready(function() {
826
    no_subgroup_label = _( "-- All --" );
827
    load_group_subgroups();
828
});
829
</script>
713
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="get">
830
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="get">
714
  <input type="hidden" name="phase" value="Use saved" />
831
  <input type="hidden" name="phase" value="Use saved" />
715
  <input type="hidden" name="filter_set" value="1" />
832
  <input type="hidden" name="filter_set" value="1" />
716
  <fieldset class="brief">
833
  <fieldset class="brief">
717
  <h3>Filter</h3>
834
  <h3>Filter</h3>
718
  <ol>
835
  <ol>
836
    <li><label for="group">Choose Group and Subgroup: </label>
837
    <select name="group" id="group" onChange="load_group_subgroups();">
838
        <option value="">-- All --</option>
839
    [% FOR g IN groups_with_subgroups %]
840
        [% IF g.selected %]
841
        <option value="[% g.id %]" selected>[% g.name %]</option>
842
        [% ELSE %]
843
        <option value="[% g.id %]">[% g.name %]</option>
844
        [% END %]
845
        <script type="text/javascript">
846
            var g_sg = new Array();
847
        [% FOR sg IN g.subgroups %]
848
            g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
849
            [% IF sg.selected %]
850
            $(document).ready(function() {
851
                $("#subgroup").val("[% sg.id %]");
852
            });
853
            [% END %]
854
        [% END %]
855
            group_subgroups["[% g.id %]"] = g_sg;
856
        </script>
857
    [% END %]
858
    </select>
859
    <select name="subgroup" id="subgroup"></select>
860
    </li>
719
    <li><label for="filter_date">Date:</label> <input type="text" id="filter_date" name="filter_date" size="10" value="[% filter_date %]" class="datepicker" />
861
    <li><label for="filter_date">Date:</label> <input type="text" id="filter_date" name="filter_date" size="10" value="[% filter_date %]" class="datepicker" />
720
    <div class="hint">[% INCLUDE 'date-format.inc' %]</div>
862
    <div class="hint">[% INCLUDE 'date-format.inc' %]</div>
721
863
(-)a/misc/cronjobs/runreport.pl (-5 / +17 lines)
Lines 186-199 unless (scalar(@ARGV)) { Link Here
186
($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
186
($verbose) and print scalar(@ARGV), " argument(s) after options: " . join(" ", @ARGV) . "\n";
187
187
188
188
189
foreach my $report (@ARGV) {
189
foreach my $report_id (@ARGV) {
190
    my ($sql, $type) = get_saved_report($report);
190
    my $report = get_saved_report($report_id);
191
    unless ($sql) {
191
    unless ($report) {
192
        carp "ERROR: No saved report $report found";
192
        warn "ERROR: No saved report $report_id found";
193
        next;
193
        next;
194
    }
194
    }
195
    my $sql         => $report->{savedsql};
196
    my $report_name => $report->{report_name};
197
    my $type        => $report->{type};
198
195
    $verbose and print "SQL: $sql\n\n";
199
    $verbose and print "SQL: $sql\n\n";
196
    # my $results = execute_query($sql, undef, 0, 99999, $format, $report); 
200
    if (defined($report_name) and $report_name ne "")
201
    {
202
        $subject = $report_name ;
203
    }
204
    else
205
    {
206
        $subject = 'Koha Saved Report';
207
    }
208
    # my $results = execute_query($sql, undef, 0, 99999, $format, $report_id);
197
    my ($sth) = execute_query($sql);
209
    my ($sth) = execute_query($sql);
198
    # execute_query(sql, , 0, 20, , )
210
    # execute_query(sql, , 0, 20, , )
199
    my $count = scalar($sth->rows);
211
    my $count = scalar($sth->rows);
(-)a/opac/svc/report (-26 / +22 lines)
Lines 31-68 my $query = CGI->new(); Link Here
31
my $report_id = $query->param('id');
31
my $report_id = $query->param('id');
32
my $report_name = $query->param('name');
32
my $report_name = $query->param('name');
33
33
34
my $cache;
34
my $report_rec = get_saved_report( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
35
my $sql;
35
die "Sorry this report is not public\n" unless $report_rec->{public};
36
my $type;
37
my $notes;
38
my $cache_expiry;
39
my $public;
40
36
41
( $sql, $type, $report_name, $notes, $cache_expiry, $public, $report_id ) =
42
  get_saved_report($report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
43
die "Sorry this report is not public\n" unless $public;
44
37
45
if (Koha::Cache->is_cache_active) {
38
my $cache_active = Koha::Cache->is_cache_active;
46
    $cache = Koha::Cache->new(
39
my ($cache_key, $cache, $json_text);
47
    );
40
if ($cache_active) {
48
    my $page = $cache->get_from_cache("opac:report:$report_id");
41
    $cache_key = "opac:report:".($report_name ? "name:$report_name" : "id:$report_id");
49
    if ($page) {
42
    $cache = Koha::Cache->new();
50
        print $query->header;
43
    $json_text = $cache->get_from_cache($cache_key);
51
        print $page;
52
        exit;
53
    }
54
}
44
}
55
45
56
print $query->header;
46
unless ($json_text) {
57
if ($sql) {
58
    my $offset = 0;
47
    my $offset = 0;
59
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
48
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
60
    my ( $sth, $errors ) = execute_query( $sql, $offset, $limit );
49
    my ( $sth, $errors ) = execute_query( $report_rec->{savedsql}, $offset, $limit );
61
    my $lines     = $sth->fetchall_arrayref;
50
    if ($sth) {
62
    my $json_text = to_json($lines);
51
        my $lines     = $sth->fetchall_arrayref;
63
    print $json_text;
52
        $json_text = to_json($lines);
64
53
65
    if (Koha::Cache->is_cache_active) {
54
        if ($cache_active) {
66
        $cache->set_in_cache( "opac:report:$report_id", $json_text, $cache_expiry );
55
            $cache->set_in_cache( $cache_key, $json_text, $report_rec->{cache_expiry} );
56
        }
57
    }
58
    else {
59
        $json_text = to_json($errors);
67
    }
60
    }
68
}
61
}
62
63
print $query->header;
64
print $json_text;
(-)a/reports/dictionary.pl (-145 / +147 lines)
Lines 37-47 my $input = new CGI; Link Here
37
my $referer = $input->referer();
37
my $referer = $input->referer();
38
38
39
my $phase = $input->param('phase') || 'View Dictionary';
39
my $phase = $input->param('phase') || 'View Dictionary';
40
my $area = $input->param('areas') || '';
40
my $definition_name        = $input->param('definition_name');
41
my $no_html = 0; # this will be set if we dont want to print out an html::template
41
my $definition_description = $input->param('definition_description');
42
my 	( $template, $borrowernumber, $cookie ) = get_template_and_user(
42
my $area  = $input->param('area') || '';
43
    {
43
my $no_html = 0;    # this will be set if we dont want to print out an html::template
44
        template_name   => "reports/dictionary.tmpl",
44
my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
45
    {   template_name   => "reports/dictionary.tmpl",
45
        query           => $input,
46
        query           => $input,
46
        type            => "intranet",
47
        type            => "intranet",
47
        authnotrequired => 0,
48
        authnotrequired => 0,
Lines 51-128 my ( $template, $borrowernumber, $cookie ) = get_template_and_user( Link Here
51
	);
52
	);
52
53
53
if ($phase eq 'View Dictionary'){
54
if ($phase eq 'View Dictionary'){
54
	# view the dictionary we use to set up abstract variables such as all borrowers over fifty who live in a certain town
55
    # view the dictionary we use to set up abstract variables such as all borrowers over fifty who live in a certain town
55
	my $areas = get_report_areas();
56
    my $definitions = get_from_dictionary($area);
56
    foreach (@{ $areas }) {
57
    $template->param(
57
        $_->{selected} = 1 if $_->{id} eq $area; # mark active area
58
        'areas'=> areas(),
58
    }
59
        'start_dictionary' => 1,
59
	my $definitions = get_from_dictionary($area);
60
        'definitions'      => $definitions,
60
	$template->param( 'areas' => $areas ,
61
    );
61
		'start_dictionary' => 1,
62
} elsif ( $phase eq 'Add New Definition' ) {
62
		'definitions' => $definitions,
63
63
	);
64
    # display form allowing them to add a new definition
64
}
65
    $template->param( 'new_dictionary' => 1, );
65
elsif ($phase eq 'Add New Definition'){
66
	# display form allowing them to add a new definition
67
	$template->param( 'new_dictionary' => 1,
68
		);
69
}
66
}
70
67
71
elsif ($phase eq 'New Term step 2'){
68
elsif ( $phase eq 'New Term step 2' ) {
72
	# Choosing the area
69
73
	my $areas = C4::Reports::Guided::get_report_areas();
70
    # Choosing the area
74
	my $definition_name=$input->param('definition_name');
71
    $template->param(
75
	my $definition_description=$input->param('definition_description');		
72
        'step_2'                 => 1,
76
	$template->param( 'step_2' => 1,
73
        'areas'                  => areas(),
77
		'areas' => $areas,
74
        'definition_name'        => $definition_name,
78
		'definition_name' => $definition_name,
75
        'definition_description' => $definition_description,
79
		'definition_description' => $definition_description,
76
    );
80
	);
81
}
77
}
82
78
83
elsif ($phase eq 'New Term step 3'){
79
elsif ( $phase eq 'New Term step 3' ) {
84
	# Choosing the columns
80
85
	my $area = $input->param('areas');
81
    # Choosing the columns
86
	my $columns = get_columns($area,$input);
82
    my $columns                = get_columns( $area, $input );
87
	my $definition_name=$input->param('definition_name');
83
    $template->param(
88
	my $definition_description=$input->param('definition_description');		
84
        'step_3'                 => 1,
89
	$template->param( 'step_3' => 1,
85
        'area'                   => $area,
90
		'area' => $area,
86
        'columns'                => $columns,
91
		'columns' => $columns,
87
        'definition_name'        => $definition_name,
92
		'definition_name' => $definition_name,
88
        'definition_description' => $definition_description,
93
		'definition_description' => $definition_description,
89
    );
94
	);
95
}
90
}
96
91
97
elsif ($phase eq 'New Term step 4'){
92
elsif ( $phase eq 'New Term step 4' ) {
98
	# Choosing the values
93
99
	my $area=$input->param('area');
94
    # Choosing the values
100
	my $definition_name=$input->param('definition_name');
95
    my @columns                = $input->param('columns');
101
	my $definition_description=$input->param('definition_description');		
96
    my $columnstring           = join( ',', @columns );
102
    my @columns = $input->param('columns');
97
    my @column_loop;
103
	my $columnstring = join (',',@columns);
98
    foreach my $column (@columns) {
104
	my @column_loop;
99
        my %tmp_hash;
105
	foreach my $column (@columns){
100
        $tmp_hash{'name'} = $column;
106
		my %tmp_hash;
101
        my $type = get_column_type($column);
107
		$tmp_hash{'name'}=$column;
102
        if ( $type eq 'distinct' ) {
108
		my $type =get_column_type($column);
103
            my $values = get_distinct_values($column);
109
		if ($type eq 'distinct'){
104
            $tmp_hash{'values'}   = $values;
110
			my $values = get_distinct_values($column);
105
            $tmp_hash{'distinct'} = 1;
111
			$tmp_hash{'values'} = $values;
106
112
			$tmp_hash{'distinct'} = 1;
107
        }
113
			  
108
        if ( $type eq 'DATE' || $type eq 'DATETIME' ) {
114
		}
109
            $tmp_hash{'date'} = 1;
115
		if ($type eq 'DATE' || $type eq 'DATETIME'){
110
        }
116
			$tmp_hash{'date'}=1;
111
        if ( $type eq 'TEXT' ) {
117
		}
112
            $tmp_hash{'text'} = 1;
118
		if ($type eq 'TEXT' || $type eq 'MEDIUMTEXT'){
113
        }
119
			$tmp_hash{'text'}=1;
114
120
		}
115
        #		else {
121
#		else {
116
        #			warn $type;#
122
#			warn $type;#
117
        #			}
123
#			}
118
        push @column_loop, \%tmp_hash;
124
		push @column_loop,\%tmp_hash;
119
    }
125
		}
126
120
127
	$template->param( 'step_4' => 1,
121
	$template->param( 'step_4' => 1,
128
		'area' => $area,
122
		'area' => $area,
Lines 134-216 elsif ($phase eq 'New Term step 4'){ Link Here
134
	);
128
	);
135
}
129
}
136
130
137
elsif ($phase eq 'New Term step 5'){
131
elsif ( $phase eq 'New Term step 5' ) {
138
	# Confirmation screen
132
    # Confirmation screen
139
	my $areas = C4::Reports::Guided::get_report_areas();
133
    my $columnstring           = $input->param('columnstring');
140
	my $area = $input->param('area');
134
    my @criteria               = $input->param('criteria_column');
141
    my $areaname = $areas->[$area - 1]->{'name'};
135
    my $query_criteria;
142
	my $columnstring = $input->param('columnstring');
136
    my @criteria_loop;
143
	my $definition_name=$input->param('definition_name');
137
144
	my $definition_description=$input->param('definition_description');	
138
    foreach my $crit (@criteria) {
145
	my @criteria = $input->param('criteria_column'); 
139
        my $value = $input->param( $crit . "_value" );
146
	my $query_criteria;
140
        if ($value) {
147
	my @criteria_loop;
141
            my %tmp_hash;
148
	foreach my $crit (@criteria) {
142
            $tmp_hash{'name'}  = $crit;
149
		my $value = $input->param( $crit . "_value" );
143
            $tmp_hash{'value'} = $value;
150
		if ($value) {
144
            push @criteria_loop, \%tmp_hash;
151
                    my %tmp_hash;
145
            if ( $value =~ C4::Dates->regexp( C4::Context->preference('dateformat') ) ) {
152
                    $tmp_hash{'name'}=$crit;
146
                my $date = C4::Dates->new($value);
153
                    $tmp_hash{'value'} = $value;
147
                $value = $date->output("iso");
154
                    push @criteria_loop,\%tmp_hash;
148
            }
155
                    if ($value =~ C4::Dates->regexp(C4::Context->preference('dateformat'))) {    
149
            $query_criteria .= " AND $crit='$value'";
156
                        my $date = C4::Dates->new($value);
150
        }
157
                        $value = $date->output("iso");
151
        $value = $input->param( $crit . "_start_value" );
158
                    }
152
        if ($value) {
159
                    $query_criteria .= " AND $crit='$value'";
153
            my %tmp_hash;
160
		}
154
            $tmp_hash{'name'}  = "$crit Start";
161
		$value = $input->param( $crit . "_start_value" );
155
            $tmp_hash{'value'} = $value;
162
		if ($value) {
156
            push @criteria_loop, \%tmp_hash;
163
                    my %tmp_hash;
157
            if ( $value =~ C4::Dates->regexp( C4::Context->preference('dateformat') ) ) {
164
                    $tmp_hash{'name'}="$crit Start";
158
                my $date = C4::Dates->new($value);
165
                    $tmp_hash{'value'} = $value;
159
                $value = $date->output("iso");
166
                    push @criteria_loop,\%tmp_hash;
160
            }
167
                    if ($value =~ C4::Dates->regexp(C4::Context->preference('dateformat'))) {    
161
            $query_criteria .= " AND $crit >= '$value'";
168
                        my $date = C4::Dates->new($value);
162
        }
169
                        $value = $date->output("iso");
163
        $value = $input->param( $crit . "_end_value" );
170
                    }
164
        if ($value) {
171
                    $query_criteria .= " AND $crit >= '$value'";
165
            my %tmp_hash;
172
		}
166
            $tmp_hash{'name'}  = "$crit End";
173
		$value = $input->param( $crit . "_end_value" );
167
            $tmp_hash{'value'} = $value;
174
		if ($value) {
168
            push @criteria_loop, \%tmp_hash;
175
                    my %tmp_hash;
169
            if ( $value =~ C4::Dates->regexp( C4::Context->preference('dateformat') ) ) {
176
                    $tmp_hash{'name'}="$crit End";
170
                my $date = C4::Dates->new($value);
177
                    $tmp_hash{'value'} = $value;
171
                $value = $date->output("iso");
178
                    push @criteria_loop,\%tmp_hash;
172
            }
179
                    if ($value =~ C4::Dates->regexp(C4::Context->preference('dateformat'))) {    
173
            $query_criteria .= " AND $crit <= '$value'";
180
                        my $date = C4::Dates->new($value);
174
        }
181
                        $value = $date->output("iso");
175
    }
182
                    }
176
    my %report_areas = map @$_, get_report_areas();
183
                    $query_criteria .= " AND $crit <= '$value'";
177
    $template->param(
184
		}		  
178
        'step_5'                 => 1,
185
	}
179
        'area'                   => $area,
186
	$template->param( 'step_5' => 1,
180
        'areaname'               => $report_areas{$area},
187
		'area' => $area,
181
        'definition_name'        => $definition_name,
188
		'areaname' => $areaname,
182
        'definition_description' => $definition_description,
189
		'definition_name' => $definition_name,
183
        'query'                  => $query_criteria,
190
		'definition_description' => $definition_description,
184
        'columnstring'           => $columnstring,
191
		'query' => $query_criteria,
185
        'criteria_loop'          => \@criteria_loop,
192
		'columnstring' => $columnstring,
186
    );
193
		'criteria_loop' => \@criteria_loop,
194
	);
195
}
187
}
196
188
197
elsif ($phase eq 'New Term step 6'){
189
elsif ( $phase eq 'New Term step 6' ) {
198
	# Saving
190
    # Saving
199
	my $area = $input->param('area');
191
    my $area                   = $input->param('area');
200
	my $definition_name=$input->param('definition_name');
192
    my $sql                    = $input->param('sql');
201
	my $definition_description=$input->param('definition_description');		
193
    save_dictionary( $definition_name, $definition_description, $sql, $area );
202
	my $sql=$input->param('sql');
194
    $no_html = 1;
203
	save_dictionary($definition_name,$definition_description,$sql,$area);
195
    print $input->redirect("/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary");
204
	$no_html=1;
196
205
	print $input->redirect("/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary");	
197
} elsif ( $phase eq 'Delete Definition' ) {
206
198
    $no_html = 1;
199
    my $id = $input->param('id');
200
    delete_definition($id);
201
    print $input->redirect("/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary");
207
}
202
}
208
elsif ($phase eq 'Delete Definition'){
209
	$no_html=1;
210
	my $id = $input->param('id');
211
	delete_definition($id);
212
	print $input->redirect("/cgi-bin/koha/reports/dictionary.pl?phase=View%20Dictionary");
213
	}
214
203
215
$template->param( 'referer' => $referer );
204
$template->param( 'referer' => $referer );
216
205
Lines 218-220 $template->param( 'referer' => $referer ); Link Here
218
if (!$no_html){
207
if (!$no_html){
219
	output_html_with_http_headers $input, $cookie, $template->output;
208
	output_html_with_http_headers $input, $cookie, $template->output;
220
}
209
}
210
211
sub areas {
212
    my $areas = get_report_areas();
213
    my @a;
214
    foreach (@$areas) {
215
        push @a, {
216
            id => $_->[0],
217
            name => $_->[1],
218
            selected => ($_->[0] eq $area),
219
        };
220
    }
221
    return \@a;
222
}
(-)a/reports/guided_reports.pl (-200 / +272 lines)
Lines 18-31 Link Here
18
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
18
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
19
19
20
use strict;
20
use strict;
21
#use warnings; FIXME - Bug 2505
21
use warnings;
22
22
use CGI;
23
use CGI;
23
use Text::CSV;
24
use Text::CSV;
24
use URI::Escape;
25
use URI::Escape;
25
use C4::Reports::Guided;
26
use C4::Reports::Guided;
26
use C4::Auth qw/:DEFAULT get_session/;
27
use C4::Auth qw/:DEFAULT get_session/;
27
use C4::Output;
28
use C4::Output;
28
use C4::Dates;
29
use C4::Dates qw/format_date/;
29
use C4::Debug;
30
use C4::Debug;
30
use C4::Branch; # XXX subfield_is_koha_internal_p
31
use C4::Branch; # XXX subfield_is_koha_internal_p
31
32
Lines 68-74 my $session = $cookie ? get_session($cookie->value) : undef; Link Here
68
my $filter;
69
my $filter;
69
if ( $input->param("filter_set") ) {
70
if ( $input->param("filter_set") ) {
70
    $filter = {};
71
    $filter = {};
71
    $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword/;
72
    $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword group subgroup/;
72
    $session->param('report_filter', $filter) if $session;
73
    $session->param('report_filter', $filter) if $session;
73
    $template->param( 'filter_set' => 1 );
74
    $template->param( 'filter_set' => 1 );
74
}
75
}
Lines 85-105 if ( !$phase ) { Link Here
85
elsif ( $phase eq 'Build new' ) {
86
elsif ( $phase eq 'Build new' ) {
86
    # build a new report
87
    # build a new report
87
    $template->param( 'build1' => 1 );
88
    $template->param( 'build1' => 1 );
88
    $template->param( 'areas' => get_report_areas(), 'usecache' => $usecache, 'cache_expiry' => 300, 'public' => '0' );
89
    my $areas = get_report_areas();
89
}
90
    $template->param(
90
elsif ( $phase eq 'Use saved' ) {
91
        'areas' => [map { id => $_->[0], name => $_->[1] }, @$areas],
92
        'usecache' => $usecache,
93
        'cache_expiry' => 300,
94
        'public' => '0',
95
    );
96
} elsif ( $phase eq 'Use saved' ) {
97
91
    # use a saved report
98
    # use a saved report
92
    # get list of reports and display them
99
    # get list of reports and display them
100
    my $group = $input->param('group');
101
    my $subgroup = $input->param('subgroup');
102
    $filter->{group} = $group;
103
    $filter->{subgroup} = $subgroup;
93
    $template->param(
104
    $template->param(
94
        'saved1' => 1,
105
        'saved1' => 1,
95
        'savedreports' => get_saved_reports($filter),
106
        'savedreports' => get_saved_reports($filter),
96
        'usecache' => $usecache,
107
        'usecache' => $usecache,
108
        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
97
    );
109
    );
98
    if ($filter) {
99
        while ( my ($k, $v) = each %$filter ) {
100
            $template->param( "filter_$k" => $v ) if $v;
101
        }
102
    }
103
}
110
}
104
111
105
elsif ( $phase eq 'Delete Saved') {
112
elsif ( $phase eq 'Delete Saved') {
Lines 113-142 elsif ( $phase eq 'Delete Saved') { Link Here
113
120
114
elsif ( $phase eq 'Show SQL'){
121
elsif ( $phase eq 'Show SQL'){
115
	
122
	
116
	my $id = $input->param('reports');
123
    my $id = $input->param('reports');
117
    my ($sql,$type,$reportname,$notes) = get_saved_report($id);
124
    my $report = get_saved_report($id);
118
	$template->param(
125
    $template->param(
119
        'id'      => $id,
126
        'id'      => $id,
120
        'reportname' => $reportname,
127
        'reportname' => $report->{report_name},
121
        'notes'      => $notes,
128
        'notes'      => $report->{notes},
122
		'sql'     => $sql,
129
	'sql'     => $report->{savedsql},
123
		'showsql' => 1,
130
	'showsql' => 1,
124
    );
131
    );
125
}
132
}
126
133
127
elsif ( $phase eq 'Edit SQL'){
134
elsif ( $phase eq 'Edit SQL'){
128
	
135
	
129
    my $id = $input->param('reports');
136
    my $id = $input->param('reports');
130
    my ($sql,$type,$reportname,$notes, $cache_expiry, $public) = get_saved_report($id);
137
    my $report = get_saved_report($id);
138
    my $group = $report->{report_group};
139
    my $subgroup  = $report->{report_subgroup};
131
    $template->param(
140
    $template->param(
132
	    'sql'        => $sql,
141
        'sql'        => $report->{savedsql},
133
	    'reportname' => $reportname,
142
        'reportname' => $report->{report_name},
134
        'notes'      => $notes,
143
        'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
144
        'notes'      => $report->{notes},
135
        'id'         => $id,
145
        'id'         => $id,
136
        'cache_expiry' => $cache_expiry,
146
        'cache_expiry' => $report->{cache_expiry},
137
        'public' => $public,
147
        'public' => $report->{public},
138
        'usecache' => $usecache,
148
        'usecache' => $usecache,
139
	    'editsql'    => 1,
149
        'editsql'    => 1,
140
    );
150
    );
141
}
151
}
142
152
Lines 144-149 elsif ( $phase eq 'Update SQL'){ Link Here
144
    my $id         = $input->param('id');
154
    my $id         = $input->param('id');
145
    my $sql        = $input->param('sql');
155
    my $sql        = $input->param('sql');
146
    my $reportname = $input->param('reportname');
156
    my $reportname = $input->param('reportname');
157
    my $group      = $input->param('group');
158
    my $subgroup   = $input->param('subgroup');
147
    my $notes      = $input->param('notes');
159
    my $notes      = $input->param('notes');
148
    my $cache_expiry = $input->param('cache_expiry');
160
    my $cache_expiry = $input->param('cache_expiry');
149
    my $cache_expiry_units = $input->param('cache_expiry_units');
161
    my $cache_expiry_units = $input->param('cache_expiry_units');
Lines 177-192 elsif ( $phase eq 'Update SQL'){ Link Here
177
            'errors'    => \@errors,
189
            'errors'    => \@errors,
178
            'sql'       => $sql,
190
            'sql'       => $sql,
179
        );
191
        );
180
    }
192
    } else {
181
    else {
193
        update_sql( $id, {
182
        update_sql( $id, $sql, $reportname, $notes, $cache_expiry, $public );
194
                sql => $sql,
195
                name => $reportname,
196
                group => $group,
197
                subgroup => $subgroup,
198
                notes => $notes,
199
                cache_expiry => $cache_expiry,
200
                public => $public,
201
        } );
183
        $template->param(
202
        $template->param(
184
            'save_successful'       => 1,
203
            'save_successful'       => 1,
185
            'reportname'            => $reportname,
204
            'reportname'            => $reportname,
186
            'id'                    => $id,
205
            'id'                    => $id,
187
        );
206
        );
188
    }
207
    }
189
    
190
}
208
}
191
209
192
elsif ($phase eq 'retrieve results') {
210
elsif ($phase eq 'retrieve results') {
Lines 228-234 elsif ( $phase eq 'Report on this Area' ) { Link Here
228
      # they have choosen a new report and the area to report on
246
      # they have choosen a new report and the area to report on
229
      $template->param(
247
      $template->param(
230
          'build2' => 1,
248
          'build2' => 1,
231
          'area'   => $input->param('areas'),
249
          'area'   => $input->param('area'),
232
          'types'  => get_report_types(),
250
          'types'  => get_report_types(),
233
          'cache_expiry' => $cache_expiry,
251
          'cache_expiry' => $cache_expiry,
234
          'public' => $input->param('public'),
252
          'public' => $input->param('public'),
Lines 275-316 elsif ( $phase eq 'Choose these criteria' ) { Link Here
275
    my $area     = $input->param('area');
293
    my $area     = $input->param('area');
276
    my $type     = $input->param('type');
294
    my $type     = $input->param('type');
277
    my $column   = $input->param('column');
295
    my $column   = $input->param('column');
278
	my @definitions = $input->param('definition');
296
    my @definitions = $input->param('definition');
279
	my $definition = join (',',@definitions);
297
    my $definition = join (',',@definitions);
280
    my @criteria = $input->param('criteria_column');
298
    my @criteria = $input->param('criteria_column');
281
	my $query_criteria;
299
    my $query_criteria;
282
    foreach my $crit (@criteria) {
300
    foreach my $crit (@criteria) {
283
        my $value = $input->param( $crit . "_value" );
301
        my $value = $input->param( $crit . "_value" );
284
	
302
285
	# If value is not defined, then it may be range values
303
        # If value is not defined, then it may be range values
286
	if (!defined $value) {
304
        if (!defined $value) {
287
305
288
	    my $fromvalue = $input->param( "from_" . $crit . "_value" );
306
            my $fromvalue = $input->param( "from_" . $crit . "_value" );
289
	    my $tovalue   = $input->param( "to_"   . $crit . "_value" );
307
            my $tovalue   = $input->param( "to_"   . $crit . "_value" );
290
	    
308
291
	    # If the range values are dates
309
            # If the range values are dates
292
	    if ($fromvalue =~ C4::Dates->regexp('syspref') && $tovalue =~ C4::Dates->regexp('syspref')) { 
310
            if ($fromvalue =~ C4::Dates->regexp('syspref') && $tovalue =~ C4::Dates->regexp('syspref')) { 
293
		$fromvalue = C4::Dates->new($fromvalue)->output("iso");
311
                $fromvalue = C4::Dates->new($fromvalue)->output("iso");
294
		$tovalue = C4::Dates->new($tovalue)->output("iso");
312
                $tovalue = C4::Dates->new($tovalue)->output("iso");
295
	    }
313
            }
296
314
297
	    if ($fromvalue && $tovalue) {
315
            if ($fromvalue && $tovalue) {
298
		$query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'";
316
                $query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'";
299
	    }
317
            }
300
318
301
	} else {
302
303
	    # If value is a date
304
	    if ($value =~ C4::Dates->regexp('syspref')) { 
305
		$value = C4::Dates->new($value)->output("iso");
306
	    }
307
        # don't escape runtime parameters, they'll be at runtime
308
        if ($value =~ /<<.*>>/) {
309
            $query_criteria .= " AND $crit=$value";
310
        } else {
319
        } else {
311
            $query_criteria .= " AND $crit='$value'";
320
321
            # If value is a date
322
            if ($value =~ C4::Dates->regexp('syspref')) { 
323
                $value = C4::Dates->new($value)->output("iso");
324
            }
325
            # don't escape runtime parameters, they'll be at runtime
326
            if ($value =~ /<<.*>>/) {
327
                $query_criteria .= " AND $crit=$value";
328
            } else {
329
                $query_criteria .= " AND $crit='$value'";
330
            }
312
        }
331
        }
313
	}
314
    }
332
    }
315
    $template->param(
333
    $template->param(
316
        'build5'         => 1,
334
        'build5'         => 1,
Lines 412-417 elsif ( $phase eq 'Build report' ) { Link Here
412
      build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition );
430
      build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition );
413
    $template->param(
431
    $template->param(
414
        'showreport' => 1,
432
        'showreport' => 1,
433
        'area'       => $area,
415
        'sql'        => $sql,
434
        'sql'        => $sql,
416
        'type'       => $type,
435
        'type'       => $type,
417
        'cache_expiry' => $input->param('cache_expiry'),
436
        'cache_expiry' => $input->param('cache_expiry'),
Lines 420-442 elsif ( $phase eq 'Build report' ) { Link Here
420
}
439
}
421
440
422
elsif ( $phase eq 'Save' ) {
441
elsif ( $phase eq 'Save' ) {
423
	# Save the report that has just been built
442
    # Save the report that has just been built
443
    my $area           = $input->param('area');
424
    my $sql  = $input->param('sql');
444
    my $sql  = $input->param('sql');
425
    my $type = $input->param('type');
445
    my $type = $input->param('type');
426
    $template->param(
446
    $template->param(
427
        'save' => 1,
447
        'save' => 1,
448
        'area'  => $area,
428
        'sql'  => $sql,
449
        'sql'  => $sql,
429
        'type' => $type,
450
        'type' => $type,
430
        'cache_expiry' => $input->param('cache_expiry'),
451
        'cache_expiry' => $input->param('cache_expiry'),
431
        'public' => $input->param('public'),
452
        'public' => $input->param('public'),
453
        'groups_with_subgroups' => groups_with_subgroups($area), # in case we have a report group that matches area
432
    );
454
    );
433
}
455
}
434
456
435
elsif ( $phase eq 'Save Report' ) {
457
elsif ( $phase eq 'Save Report' ) {
436
    # save the sql pasted in by a user 
458
    # save the sql pasted in by a user
437
    my $sql  = $input->param('sql');
459
    my $area  = $input->param('area');
438
    my $name = $input->param('reportname');
460
    my $group = $input->param('group');
439
    my $type = $input->param('types');
461
    my $subgroup = $input->param('subgroup');
462
    my $sql   = $input->param('sql');
463
    my $name  = $input->param('reportname');
464
    my $type  = $input->param('types');
440
    my $notes = $input->param('notes');
465
    my $notes = $input->param('notes');
441
    my $cache_expiry = $input->param('cache_expiry');
466
    my $cache_expiry = $input->param('cache_expiry');
442
    my $cache_expiry_units = $input->param('cache_expiry_units');
467
    my $cache_expiry_units = $input->param('cache_expiry_units');
Lines 454-460 elsif ( $phase eq 'Save Report' ) { Link Here
454
      }
479
      }
455
    }
480
    }
456
    # check $cache_expiry isnt too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
481
    # check $cache_expiry isnt too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
457
    if( $cache_expiry >= 2592000 ){
482
    if( $cache_expiry && $cache_expiry >= 2592000 ){
458
      push @errors, {cache_expiry => $cache_expiry};
483
      push @errors, {cache_expiry => $cache_expiry};
459
    }
484
    }
460
    ## FIXME this is AFTER entering a name to save the report under
485
    ## FIXME this is AFTER entering a name to save the report under
Lines 462-468 elsif ( $phase eq 'Save Report' ) { Link Here
462
        push @errors, {sqlerr => $1};
487
        push @errors, {sqlerr => $1};
463
    }
488
    }
464
    elsif ($sql !~ /^(SELECT)/i) {
489
    elsif ($sql !~ /^(SELECT)/i) {
465
        push @errors, {queryerr => 1};
490
        push @errors, {queryerr => "No SELECT"};
466
    }
491
    }
467
    if (@errors) {
492
    if (@errors) {
468
        $template->param(
493
        $template->param(
Lines 476-632 elsif ( $phase eq 'Save Report' ) { Link Here
476
        );
501
        );
477
    }
502
    }
478
    else {
503
    else {
479
        my $id = save_report( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
504
        save_report( {
480
        $template->param(
505
                borrowernumber => $borrowernumber,
481
            'save_successful'       => 1,
506
                sql            => $sql,
482
            'reportname'            => $name,
507
                name           => $name,
483
            'id'                    => $id,
508
                area           => $area,
484
        );
509
                group          => $group,
510
                subgroup       => $subgroup,
511
                type           => $type,
512
                notes          => $notes,
513
                cache_expiry   => $cache_expiry,
514
                public         => $public,
515
            } );
516
        $template->param( 'save_successful' => 1, );
485
    }
517
    }
486
}
518
}
487
519
488
elsif ($phase eq 'Run this report'){
520
elsif ($phase eq 'Run this report'){
489
    # execute a saved report
521
    # execute a saved report
490
    my $limit  = 20;    # page size. # TODO: move to DB or syspref?
522
    my $limit      = 20; # page size. # TODO: move to DB or syspref?
491
    my $offset = 0;
523
    my $offset     = 0;
492
    my $report = $input->param('reports');
524
    my $report_id  = $input->param('reports');
493
    my @sql_params = $input->param('sql_params');
525
    my @sql_params = $input->param('sql_params');
494
    # offset algorithm
526
    # offset algorithm
495
    if ($input->param('page')) {
527
    if ($input->param('page')) {
496
        $offset = ($input->param('page') - 1) * $limit;
528
        $offset = ($input->param('page') - 1) * $limit;
497
    }
529
    }
498
    my ($sql,$type,$name,$notes) = get_saved_report($report);
530
499
    unless ($sql) {
531
    my ( $sql, $type, $name, $notes );
500
        push @errors, {no_sql_for_id=>$report};   
532
    if (my $report = get_saved_report($report_id)) {
501
    } 
533
        $sql   = $report->{savedsql};
502
    my @rows = ();
534
        $name  = $report->{report_name};
503
    # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
535
        $notes = $report->{notes};
504
    if ($sql =~ /<</ && !@sql_params) {
536
505
        # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
537
        my @rows = ();
506
        my @split = split /<<|>>/,$sql;
538
        # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
507
        my @tmpl_parameters;
539
        if ($sql =~ /<</ && !@sql_params) {
508
        for(my $i=0;$i<($#split/2);$i++) {
540
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
509
            my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
541
            my @split = split /<<|>>/,$sql;
510
            my $input;
542
            my @tmpl_parameters;
511
            if ($authorised_value eq "date") {
543
            for(my $i=0;$i<($#split/2);$i++) {
512
               $input = 'date';
544
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
513
            }
545
                my $input;
514
            elsif ($authorised_value) {
546
                if ($authorised_value eq "date") {
515
                my $dbh=C4::Context->dbh;
547
                   $input = 'date';
516
                my @authorised_values;
517
                my %authorised_lib;
518
                # builds list, depending on authorised value...
519
                if ( $authorised_value eq "branches" ) {
520
                    my $branches = GetBranchesLoop();
521
                    foreach my $thisbranch (@$branches) {
522
                        push @authorised_values, $thisbranch->{value};
523
                        $authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname};
524
                    }
525
                }
548
                }
526
                elsif ( $authorised_value eq "itemtypes" ) {
549
                elsif ($authorised_value) {
527
                    my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
550
                    my $dbh=C4::Context->dbh;
528
                    $sth->execute;
551
                    my @authorised_values;
529
                    while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
552
                    my %authorised_lib;
530
                        push @authorised_values, $itemtype;
553
                    # builds list, depending on authorised value...
531
                        $authorised_lib{$itemtype} = $description;
554
                    if ( $authorised_value eq "branches" ) {
555
                        my $branches = GetBranchesLoop();
556
                        foreach my $thisbranch (@$branches) {
557
                            push @authorised_values, $thisbranch->{value};
558
                            $authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname};
559
                        }
532
                    }
560
                    }
533
                }
561
                    elsif ( $authorised_value eq "itemtypes" ) {
534
                elsif ( $authorised_value eq "cn_source" ) {
562
                        my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
535
                    my $class_sources = GetClassSources();
563
                        $sth->execute;
536
                    my $default_source = C4::Context->preference("DefaultClassificationSource");
564
                        while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
537
                    foreach my $class_source (sort keys %$class_sources) {
565
                            push @authorised_values, $itemtype;
538
                        next unless $class_sources->{$class_source}->{'used'} or
566
                            $authorised_lib{$itemtype} = $description;
539
                                    ($class_source eq $default_source);
567
                        }
540
                        push @authorised_values, $class_source;
541
                        $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
542
                    }
568
                    }
543
                }
569
                    elsif ( $authorised_value eq "cn_source" ) {
544
                elsif ( $authorised_value eq "categorycode" ) {
570
                        my $class_sources = GetClassSources();
545
                    my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description");
571
                        my $default_source = C4::Context->preference("DefaultClassificationSource");
546
                    $sth->execute;
572
                        foreach my $class_source (sort keys %$class_sources) {
547
                    while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) {
573
                            next unless $class_sources->{$class_source}->{'used'} or
548
                        push @authorised_values, $categorycode;
574
                                        ($class_source eq $default_source);
549
                        $authorised_lib{$categorycode} = $description;
575
                            push @authorised_values, $class_source;
576
                            $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
577
                        }
550
                    }
578
                    }
579
                    elsif ( $authorised_value eq "categorycode" ) {
580
                        my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description");
581
                        $sth->execute;
582
                        while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) {
583
                            push @authorised_values, $categorycode;
584
                            $authorised_lib{$categorycode} = $description;
585
                        }
586
587
                        #---- "true" authorised value
588
                    }
589
                    else {
590
                        my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");
551
591
552
                    #---- "true" authorised value
592
                        $authorised_values_sth->execute( $authorised_value);
553
                }
554
                else {
555
                    my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");
556
557
                    $authorised_values_sth->execute( $authorised_value);
558
593
559
                    while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
594
                        while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
560
                        push @authorised_values, $value;
595
                            push @authorised_values, $value;
561
                        $authorised_lib{$value} = $lib;
596
                            $authorised_lib{$value} = $lib;
562
                        # For item location, we show the code and the libelle
597
                            # For item location, we show the code and the libelle
563
                        $authorised_lib{$value} = $lib;
598
                            $authorised_lib{$value} = $lib;
599
                        }
564
                    }
600
                    }
565
                }
601
                    $input =CGI::scrolling_list(      # FIXME: factor out scrolling_list
566
                $input =CGI::scrolling_list(      # FIXME: factor out scrolling_list
602
                        -name     => "sql_params",
567
                    -name     => "sql_params",
603
                        -values   => \@authorised_values,
568
                    -values   => \@authorised_values,
569
#                     -default  => $value,
604
#                     -default  => $value,
570
                    -labels   => \%authorised_lib,
605
                        -labels   => \%authorised_lib,
571
                    -override => 1,
606
                        -override => 1,
572
                    -size     => 1,
607
                        -size     => 1,
573
                    -multiple => 0,
608
                        -multiple => 0,
574
                    -tabindex => 1,
609
                        -tabindex => 1,
575
                );
610
                    );
576
611
                    push @tmpl_parameters, {'entry' => $text, 'input' => $input };
577
            } else {
612
                }
578
                $input = "<input type='text' name='sql_params'/>";
579
            }
613
            }
580
            push @tmpl_parameters, {'entry' => $text, 'input' => $input };
614
            $template->param('sql'         => $sql,
581
        }
615
                            'name'         => $name,
582
        $template->param('sql'         => $sql,
616
                            'sql_params'   => \@tmpl_parameters,
583
                        'name'         => $name,
617
                            'enter_params' => 1,
584
                        'sql_params'   => \@tmpl_parameters,
618
                            'reports'      => $report,
585
                        'enter_params' => 1,
619
                            );
586
                        'reports'      => $report,
587
                        );
588
    } else {
589
        # OK, we have parameters, or there are none, we run the report
590
        # if there were parameters, replace before running
591
        # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
592
        my @split = split /<<|>>/,$sql;
593
        my @tmpl_parameters;
594
        for(my $i=0;$i<$#split/2;$i++) {
595
            my $quoted = C4::Context->dbh->quote($sql_params[$i]);
596
            # if there are special regexp chars, we must \ them
597
            $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
598
            $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
599
        }
600
        my ($sth, $errors) = execute_query($sql, $offset, $limit);
601
        my $total = nb_rows($sql) || 0;
602
        unless ($sth) {
603
            die "execute_query failed to return sth for report $report: $sql";
604
        } else {
620
        } else {
605
            my $headref = $sth->{NAME} || [];
621
            # OK, we have parameters, or there are none, we run the report
606
            my @headers = map { +{ cell => $_ } } @$headref;
622
            # if there were parameters, replace before running
607
            $template->param(header_row => \@headers);
623
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
608
            while (my $row = $sth->fetchrow_arrayref()) {
624
            my @split = split /<<|>>/,$sql;
609
                my @cells = map { +{ cell => $_ } } @$row;
625
            my @tmpl_parameters;
610
                push @rows, { cells => \@cells };
626
            for(my $i=0;$i<$#split/2;$i++) {
627
                my $quoted = C4::Context->dbh->quote($sql_params[$i]);
628
                # if there are special regexp chars, we must \ them
629
                $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
630
                $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
631
            }
632
            my ($sth, $errors) = execute_query($sql, $offset, $limit);
633
            my $total = nb_rows($sql) || 0;
634
            unless ($sth) {
635
                die "execute_query failed to return sth for report $report: $sql";
636
            } else {
637
                my $headref = $sth->{NAME} || [];
638
                my @headers = map { +{ cell => $_ } } @$headref;
639
                $template->param(header_row => \@headers);
640
                while (my $row = $sth->fetchrow_arrayref()) {
641
                    my @cells = map { +{ cell => $_ } } @$row;
642
                    push @rows, { cells => \@cells };
643
                }
611
            }
644
            }
612
        }
613
645
614
        my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
646
            my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
615
        my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&amp;phase=Run%20this%20report";
647
            my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&amp;phase=Run%20this%20report";
616
        if (@sql_params) {
648
            if (@sql_params) {
617
            $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params);
649
                $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params);
650
            }
651
            $template->param(
652
                'results' => \@rows,
653
                'sql'     => $sql,
654
                'id'      => $report,
655
                'execute' => 1,
656
                'name'    => $name,
657
                'notes'   => $notes,
658
                'errors'  => $errors,
659
                'pagination_bar'  => pagination_bar($url, $totpages, $input->param('page')),
660
                'unlimited_total' => $total,
661
            );
618
        }
662
        }
619
        $template->param(
663
    }
620
            'results' => \@rows,
664
    else {
621
            'sql'     => $sql,
665
        push @errors, { no_sql_for_id => $report_id };
622
            'id'      => $report,
623
            'execute' => 1,
624
            'name'    => $name,
625
            'notes'   => $notes,
626
            'errors'  => $errors,
627
            'pagination_bar'  => pagination_bar($url, $totpages, $input->param('page')),
628
            'unlimited_total' => $total,
629
        );
630
    }
666
    }
631
}
667
}
632
668
Lines 676-691 elsif ($phase eq 'Export'){ Link Here
676
    );
712
    );
677
}
713
}
678
714
679
elsif ($phase eq 'Create report from SQL') {
715
elsif ( $phase eq 'Create report from SQL' ) {
680
	# allow the user to paste in sql
716
681
    if ($input->param('sql')) {
717
    my ($group, $subgroup);
718
    # allow the user to paste in sql
719
    if ( $input->param('sql') ) {
720
        $group = $input->param('report_group');
721
        $subgroup  = $input->param('report_subgroup');
682
        $template->param(
722
        $template->param(
683
            'sql'           => $input->param('sql'),
723
            'sql'           => $input->param('sql'),
684
            'reportname'    => $input->param('reportname'),
724
            'reportname'    => $input->param('reportname'),
685
            'notes'         => $input->param('notes'),
725
            'notes'         => $input->param('notes'),
686
        );
726
        );
687
    }
727
    }
688
        $template->param('create' => 1, 'public' => '0', 'cache_expiry' => 300, 'usecache' => $usecache);
728
    $template->param(
729
        'create' => 1,
730
        'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
731
        'public' => '0',
732
        'cache_expiry' => 300,
733
        'usecache' => $usecache,
734
    );
689
}
735
}
690
736
691
elsif ($phase eq 'Create Compound Report'){
737
elsif ($phase eq 'Create Compound Report'){
Lines 724-726 $template->param( 'referer' => $input->referer(), Link Here
724
                );
770
                );
725
771
726
output_html_with_http_headers $input, $cookie, $template->output;
772
output_html_with_http_headers $input, $cookie, $template->output;
773
774
sub groups_with_subgroups {
775
    my ($group, $subgroup) = @_;
776
777
    my $groups_with_subgroups = get_report_groups();
778
    my @g_sg;
779
    while (my ($g_id, $v) = each %$groups_with_subgroups) {
780
        my @subgroups;
781
        if (my $sg = $v->{subgroups}) {
782
            while (my ($sg_id, $n) = each %$sg) {
783
                push @subgroups, {
784
                    id => $sg_id,
785
                    name => $n,
786
                    selected => ($group && $g_id eq $group && $subgroup && $sg_id eq $subgroup ),
787
                };
788
            }
789
        }
790
        push @g_sg, {
791
            id => $g_id,
792
            name => $v->{name},
793
            selected => ($group && $g_id eq $group),
794
            subgroups => \@subgroups,
795
        };
796
    }
797
    return \@g_sg;
798
}
(-)a/svc/report (-36 / +20 lines)
Lines 33-45 my $query = CGI->new(); Link Here
33
my $report_id = $query->param('id');
33
my $report_id = $query->param('id');
34
my $report_name = $query->param('name');
34
my $report_name = $query->param('name');
35
35
36
my $cache;
37
my $sql;
38
my $type;
39
my $notes;
40
my $cache_expiry;
41
my $public;
42
43
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
36
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
44
    {
37
    {
45
        template_name   => "intranet-main.tmpl",
38
        template_name   => "intranet-main.tmpl",
Lines 50-88 my ( $template, $loggedinuser, $cookie ) = get_template_and_user( Link Here
50
    }
43
    }
51
);
44
);
52
45
53
if (Koha::Cache->is_cache_active) {
46
my $cache_active = Koha::Cache->is_cache_active;
54
    if ($report_name) { # When retrieving by name, we have to hit the
47
my ($cache_key, $cache, $json_text);
55
                        # database to get the ID before we can check
48
if ($cache_active) {
56
                        # the cache. Yuck.
49
    $cache_key = "intranet:report:".($report_name ? "name:$report_name" : "id:$report_id");
57
        ( $sql, $type, $report_name, $notes, $cache_expiry, $public, $report_id ) =
58
            get_saved_report( { 'name' => $report_name } );
59
    }
60
61
    $cache = Koha::Cache->new();
50
    $cache = Koha::Cache->new();
62
    my $page = $cache->get_from_cache("intranet:report:$report_id");
51
    $json_text = $cache->get_from_cache($cache_key);
63
    if ($page) {
64
        print $query->header;
65
        print $page;
66
        exit;
67
    }
68
}
52
}
69
53
70
print $query->header;
54
unless ($json_text) {
71
55
    my $report_rec = get_saved_report($report_name ? { 'name' => $report_name } : { 'id' => $report_id });
72
# $public isnt used for intranet
73
unless ($sql) {
74
    ( $sql, $type, $report_name, $notes, $cache_expiry, $public, $report_id ) =
75
        get_saved_report($report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
76
}
77
if ($sql) {
78
    my $offset = 0;
56
    my $offset = 0;
79
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
57
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
80
    my ( $sth, $errors ) = execute_query( $sql, $offset, $limit );
58
    my ( $sth, $errors ) = execute_query( $report_rec->{savedsql}, $offset, $limit );
81
    my $lines     = $sth->fetchall_arrayref;
59
    if ($sth) {
82
    my $json_text = to_json($lines);
60
        my $lines     = $sth->fetchall_arrayref;
83
    print $json_text;
61
        $json_text = to_json($lines);
84
62
85
    if (Koha::Cache->is_cache_active) {
63
        if ($cache_active) {
86
        $cache->set_in_cache( "intranet:report:$report_id", $json_text, $cache_expiry );
64
            $cache->set_in_cache( $cache_key, $json_text, $report_rec->{cache_expiry} );
65
        }
66
    }
67
    else {
68
        $json_text = to_json($errors);
87
    }
69
    }
88
}
70
}
89
- 
71
72
print $query->header;
73
print $json_text;

Return to bug 7993