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 505-541 Returns id of the newly created report Link Here
505
=cut
522
=cut
506
523
507
sub save_report {
524
sub save_report {
508
    my ( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public ) = @_;
525
    my ($fields) = @_;
509
    $cache_expiry ||= 300;
526
    my $borrowernumber = $fields->{borrowernumber};
527
    my $sql = $fields->{sql};
528
    my $name = $fields->{name};
529
    my $type = $fields->{type};
530
    my $notes = $fields->{notes};
531
    my $area = $fields->{area};
532
    my $group = $fields->{group};
533
    my $subgroup = $fields->{subgroup};
534
    my $cache_expiry = $fields->{cache_expiry} || 300;
535
    my $public = $fields->{public};
536
510
    my $dbh = C4::Context->dbh();
537
    my $dbh = C4::Context->dbh();
511
    $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/
538
    $sql =~ s/(\s*\;\s*)$//;    # removes trailing whitespace and /;/
512
    my $query =
539
    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(),?,?,?,?,?,?,?,?,?)";
513
"INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes,cache_expiry, public)  VALUES (?,now(),now(),?,?,?,?,?,?)";
540
    $dbh->do($query, undef, $borrowernumber, $sql, $name, $area, $group, $subgroup, $type, $notes, $cache_expiry, $public);
514
    $dbh->do( $query, undef, $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
541
515
    my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef,
542
    my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef,
516
                                   $borrowernumber, $name);
543
                                   $borrowernumber, $name);
517
    return $id;
544
    return $id;
518
}
545
}
519
546
520
sub update_sql {
547
sub update_sql {
521
    my $id = shift || croak "No Id given";
548
    my $id         = shift || croak "No Id given";
522
    my $sql = shift;
549
    my $fields     = shift;
523
    my $reportname = shift;
550
    my $sql = $fields->{sql};
524
    my $notes = shift;
551
    my $name = $fields->{name};
525
    my $cache_expiry = shift;
552
    my $notes = $fields->{notes};
526
    my $public = shift;
553
    my $group = $fields->{group};
527
554
    my $subgroup = $fields->{subgroup};
528
    # not entirely a magic number, Cache::Memcached::Set assumed any expiry >= (60*60*24*30) is an absolute unix timestamp (rather than relative seconds)
555
    my $cache_expiry = $fields->{cache_expiry};
556
    my $public = $fields->{public};
557
529
    if( $cache_expiry >= 2592000 ){
558
    if( $cache_expiry >= 2592000 ){
530
      die "Please specify a cache expiry less than 30 days\n";
559
      die "Please specify a cache expiry less than 30 days\n";
531
    }
560
    }
532
561
533
    my $dbh = C4::Context->dbh();
562
    my $dbh        = C4::Context->dbh();
534
    $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/
563
    $sql =~ s/(\s*\;\s*)$//;    # removes trailing whitespace and /;/
535
    my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, notes = ?, cache_expiry = ?, public = ? WHERE id = ? ";
564
    my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, report_group = ?, report_subgroup = ?, notes = ?, cache_expiry = ?, public = ? WHERE id = ? ";
536
    my $sth = $dbh->prepare($query);
565
    $dbh->do($query, undef, $sql, $name, $group, $subgroup, $notes, $cache_expiry, $public, $id );
537
    $sth->execute( $sql, $reportname, $notes, $cache_expiry, $public, $id );
538
    $sth->finish();
539
}
566
}
540
567
541
sub store_results {
568
sub store_results {
Lines 582-611 sub format_results { Link Here
582
}	
609
}	
583
610
584
sub delete_report {
611
sub delete_report {
585
	my ( $id ) = @_;
612
    my ($id)  = @_;
586
	my $dbh = C4::Context->dbh();
613
    my $dbh   = C4::Context->dbh();
587
	my $query = "DELETE FROM saved_sql WHERE id = ?";
614
    my $query = "DELETE FROM saved_sql WHERE id = ?";
588
	my $sth = $dbh->prepare($query);
615
    my $sth   = $dbh->prepare($query);
589
	$sth->execute($id);
616
    $sth->execute($id);
590
}	
617
}	
591
618
592
# $filter is either { date => $d, author => $a, keyword => $kw }
619
593
# or $keyword. Optional.
620
my $SAVED_REPORTS_BASE_QRY = <<EOQ;
621
SELECT s.*, r.report, r.date_run, $AREA_NAME_SQL_SNIPPET, av_g.lib AS groupname, av_sg.lib AS subgroupname,
622
b.firstname AS borrowerfirstname, b.surname AS borrowersurname
623
FROM saved_sql s
624
LEFT JOIN saved_reports r ON r.report_id = s.id
625
LEFT OUTER JOIN authorised_values av_g ON (av_g.category = 'REPORT_GROUP' AND av_g.authorised_value = s.report_group)
626
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)
627
LEFT OUTER JOIN borrowers b USING (borrowernumber)
628
EOQ
594
my $DATE_FORMAT = "%d/%m/%Y";
629
my $DATE_FORMAT = "%d/%m/%Y";
595
sub get_saved_reports {
630
sub get_saved_reports {
631
# $filter is either { date => $d, author => $a, keyword => $kw, }
632
# or $keyword. Optional.
596
    my ($filter) = @_;
633
    my ($filter) = @_;
597
    $filter = { keyword => $filter } if $filter && !ref( $filter );
634
    $filter = { keyword => $filter } if $filter && !ref( $filter );
635
    my ($group, $subgroup) = @_;
598
636
599
    my $dbh   = C4::Context->dbh();
637
    my $dbh   = C4::Context->dbh();
638
    my $query = $SAVED_REPORTS_BASE_QRY;
600
    my (@cond,@args);
639
    my (@cond,@args);
601
    my $query = "SELECT saved_sql.id, report_id, report,
602
                        date_run, date_created, last_modified, savedsql, last_run,
603
                        report_name, type, notes,
604
                        borrowernumber, surname as borrowersurname, firstname as borrowerfirstname,
605
                        cache_expiry, public
606
                 FROM saved_sql 
607
                 LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
608
                 LEFT OUTER JOIN borrowers USING (borrowernumber)";
609
    if ($filter) {
640
    if ($filter) {
610
        if (my $date = $filter->{date}) {
641
        if (my $date = $filter->{date}) {
611
            $date = format_date_in_iso($date);
642
            $date = format_date_in_iso($date);
Lines 629-634 sub get_saved_reports { Link Here
629
                         savedsql LIKE ?";
660
                         savedsql LIKE ?";
630
            push @args, $keyword, $keyword, $keyword, $keyword;
661
            push @args, $keyword, $keyword, $keyword, $keyword;
631
        }
662
        }
663
        if ($filter->{group}) {
664
            push @cond, "report_group = ?";
665
            push @args, $filter->{group};
666
        }
667
        if ($filter->{subgroup}) {
668
            push @cond, "report_subgroup = ?";
669
            push @args, $filter->{subgroup};
670
        }
632
    }
671
    }
633
    $query .= " WHERE ".join( " AND ", map "($_)", @cond ) if @cond;
672
    $query .= " WHERE ".join( " AND ", map "($_)", @cond ) if @cond;
634
    $query .= " ORDER by date_created";
673
    $query .= " ORDER by date_created";
Lines 642-648 sub get_saved_reports { Link Here
642
sub get_saved_report {
681
sub get_saved_report {
643
    my $dbh   = C4::Context->dbh();
682
    my $dbh   = C4::Context->dbh();
644
    my $query;
683
    my $query;
645
    my $sth;
646
    my $report_arg;
684
    my $report_arg;
647
    if ($#_ == 0 && ref $_[0] ne 'HASH') {
685
    if ($#_ == 0 && ref $_[0] ne 'HASH') {
648
        ($report_arg) = @_;
686
        ($report_arg) = @_;
Lines 661-670 sub get_saved_report { Link Here
661
    } else {
699
    } else {
662
        return;
700
        return;
663
    }
701
    }
664
    $sth   = $dbh->prepare($query);
702
    return $dbh->selectrow_hashref($query, undef, $report_arg);
665
    $sth->execute($report_arg);
666
    my $data = $sth->fetchrow_hashref();
667
    return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'}, $data->{'cache_expiry'}, $data->{'public'}, $data->{'id'} );
668
}
703
}
669
704
670
=item create_compound($masterID,$subreportID)
705
=item create_compound($masterID,$subreportID)
Lines 674-695 This will take 2 reports and create a compound report using both of them Link Here
674
=cut
709
=cut
675
710
676
sub create_compound {
711
sub create_compound {
677
	my ($masterID,$subreportID) = @_;
712
    my ( $masterID, $subreportID ) = @_;
678
	my $dbh = C4::Context->dbh();
713
    my $dbh = C4::Context->dbh();
679
	# get the reports
714
680
	my ($mastersql,$mastertype) = get_saved_report($masterID);
715
    # get the reports
681
	my ($subsql,$subtype) = get_saved_report($subreportID);
716
    my $master = get_saved_report($masterID);
682
	
717
    my $mastersql = $master->{savedsql};
683
	# now we have to do some checking to see how these two will fit together
718
    my $mastertype = $master->{type};
684
	# or if they will
719
    my $sub = get_saved_report($subreportID);
685
	my ($mastertables,$subtables);
720
    my $subsql = $master->{savedsql};
686
	if ($mastersql =~ / from (.*) where /i){ 
721
    my $subtype = $master->{type};
687
		$mastertables = $1;
722
688
	}
723
    # now we have to do some checking to see how these two will fit together
689
	if ($subsql =~ / from (.*) where /i){
724
    # or if they will
690
		$subtables = $1;
725
    my ( $mastertables, $subtables );
691
	}
726
    if ( $mastersql =~ / from (.*) where /i ) {
692
	return ($mastertables,$subtables);
727
        $mastertables = $1;
728
    }
729
    if ( $subsql =~ / from (.*) where /i ) {
730
        $subtables = $1;
731
    }
732
    return ( $mastertables, $subtables );
693
}
733
}
694
734
695
=item get_column_type($column)
735
=item get_column_type($column)
Lines 739-781 sub get_distinct_values { Link Here
739
}	
779
}	
740
780
741
sub save_dictionary {
781
sub save_dictionary {
742
	my ($name,$description,$sql,$area) = @_;
782
    my ( $name, $description, $sql, $area ) = @_;
743
	my $dbh = C4::Context->dbh();
783
    my $dbh   = C4::Context->dbh();
744
	my $query = "INSERT INTO reports_dictionary (name,description,saved_sql,area,date_created,date_modified)
784
    my $query = "INSERT INTO reports_dictionary (name,description,saved_sql,report_area,date_created,date_modified)
745
  VALUES (?,?,?,?,now(),now())";
785
  VALUES (?,?,?,?,now(),now())";
746
    my $sth = $dbh->prepare($query);
786
    my $sth = $dbh->prepare($query);
747
    $sth->execute($name,$description,$sql,$area) || return 0;
787
    $sth->execute($name,$description,$sql,$area) || return 0;
748
    return 1;
788
    return 1;
749
}
789
}
750
790
791
my $DICTIONARY_BASE_QRY = <<EOQ;
792
SELECT d.*, $AREA_NAME_SQL_SNIPPET
793
FROM reports_dictionary d
794
EOQ
751
sub get_from_dictionary {
795
sub get_from_dictionary {
752
	my ($area,$id) = @_;
796
    my ( $area, $id ) = @_;
753
	my $dbh = C4::Context->dbh();
797
    my $dbh   = C4::Context->dbh();
754
	my $query = "SELECT * FROM reports_dictionary";
798
    my $query = $DICTIONARY_BASE_QRY;
755
	if ($area){
799
    if ($area) {
756
		$query.= " WHERE area = ?";
800
        $query .= " WHERE report_area = ?";
757
	}
801
    } elsif ($id) {
758
	elsif ($id){
802
        $query .= " WHERE id = ?";
759
		$query.= " WHERE id = ?"
803
    }
760
	}
804
    my $sth = $dbh->prepare($query);
761
	my $sth = $dbh->prepare($query);
805
    if ($id) {
762
	if ($id){
806
        $sth->execute($id);
763
		$sth->execute($id);
807
    } elsif ($area) {
764
	}
808
        $sth->execute($area);
765
	elsif ($area) {
809
    } else {
766
		$sth->execute($area);
810
        $sth->execute();
767
	}
811
    }
768
	else {
812
    my @loop;
769
		$sth->execute();
813
    while ( my $data = $sth->fetchrow_hashref() ) {
770
	}
814
        push @loop, $data;
771
	my @loop;
815
    }
772
	my @reports = ( 'Circulation', 'Catalog', 'Patrons', 'Acquisitions', 'Accounts');
816
    return ( \@loop );
773
	while (my $data = $sth->fetchrow_hashref()){
774
		$data->{'areaname'}=$reports[$data->{'area'}-1];
775
		push @loop,$data;
776
		
777
	}
778
	return (\@loop);
779
}
817
}
780
818
781
sub delete_definition {
819
sub delete_definition {
Lines 815-820 sub _get_column_defs { Link Here
815
	close COLUMNS;
853
	close COLUMNS;
816
	return \%columns;
854
	return \%columns;
817
}
855
}
856
857
=item build_authorised_value_list($authorised_value)
858
859
Returns an arrayref - hashref pair. The hashref consists of
860
various code => name lists depending on the $authorised_value.
861
The arrayref is the hashref keys, in appropriate order
862
863
=cut
864
865
sub build_authorised_value_list {
866
    my ( $authorised_value ) = @_;
867
868
    my $dbh = C4::Context->dbh;
869
    my @authorised_values;
870
    my %authorised_lib;
871
872
    # builds list, depending on authorised value...
873
    if ( $authorised_value eq "branches" ) {
874
        my $branches = GetBranchesLoop();
875
        foreach my $thisbranch (@$branches) {
876
            push @authorised_values, $thisbranch->{value};
877
            $authorised_lib{ $thisbranch->{value} } = $thisbranch->{branchname};
878
        }
879
    } elsif ( $authorised_value eq "itemtypes" ) {
880
        my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
881
        $sth->execute;
882
        while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
883
            push @authorised_values, $itemtype;
884
            $authorised_lib{$itemtype} = $description;
885
        }
886
    } elsif ( $authorised_value eq "cn_source" ) {
887
        my $class_sources  = GetClassSources();
888
        my $default_source = C4::Context->preference("DefaultClassificationSource");
889
        foreach my $class_source ( sort keys %$class_sources ) {
890
            next
891
              unless $class_sources->{$class_source}->{'used'}
892
                  or ( $class_source eq $default_source );
893
            push @authorised_values, $class_source;
894
            $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
895
        }
896
    } elsif ( $authorised_value eq "categorycode" ) {
897
        my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description");
898
        $sth->execute;
899
        while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) {
900
            push @authorised_values, $categorycode;
901
            $authorised_lib{$categorycode} = $description;
902
        }
903
904
        #---- "true" authorised value
905
    } else {
906
        my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");
907
908
        $authorised_values_sth->execute($authorised_value);
909
910
        while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
911
            push @authorised_values, $value;
912
            $authorised_lib{$value} = $lib;
913
914
            # For item location, we show the code and the libelle
915
            $authorised_lib{$value} = $lib;
916
        }
917
    }
918
919
    return (\@authorised_values, \%authorised_lib);
920
}
921
818
1;
922
1;
819
__END__
923
__END__
820
924
(-)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 1626-1633 CREATE TABLE reports_dictionary ( -- definitions (or snippets of SQL) stored for Link Here
1626
   `date_created` datetime default NULL, -- date and time this definition was created
1626
   `date_created` datetime default NULL, -- date and time this definition was created
1627
   `date_modified` datetime default NULL, -- date and time this definition was last modified
1627
   `date_modified` datetime default NULL, -- date and time this definition was last modified
1628
   `saved_sql` text, -- SQL snippet for us in reports
1628
   `saved_sql` text, -- SQL snippet for us in reports
1629
   `area` int(11) default NULL, -- Koha module this definition is for (1 = Circulation, 2 = Catalog, 3 = Patrons, 4 = Acquistions, 5 = Accounts)
1629
   report_area varchar(6) DEFAULT NULL, -- Koha module this definition is for Circulation, Catalog, Patrons, Acquistions, Accounts)
1630
   PRIMARY KEY  (`id`)
1630
   PRIMARY KEY  (id),
1631
   KEY dictionary_area_idx (report_area)
1631
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1632
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1632
1633
1633
--
1634
--
Lines 1725-1731 CREATE TABLE saved_sql ( -- saved sql reports Link Here
1725
   `notes` text, -- the notes or description given to this report
1726
   `notes` text, -- the notes or description given to this report
1726
   `cache_expiry` int NOT NULL default 300,
1727
   `cache_expiry` int NOT NULL default 300,
1727
   `public` boolean NOT NULL default FALSE,
1728
   `public` boolean NOT NULL default FALSE,
1729
    report_area varchar(6) default NULL,
1730
    report_group varchar(80) default NULL,
1731
    report_subgroup varchar(80) default NULL,
1728
   PRIMARY KEY  (`id`),
1732
   PRIMARY KEY  (`id`),
1733
   KEY sql_area_group_idx (report_group, report_subgroup),
1729
   KEY boridx (`borrowernumber`)
1734
   KEY boridx (`borrowernumber`)
1730
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1735
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1731
1736
(-)a/installer/data/mysql/updatedatabase.pl (+32 lines)
Lines 5696-5701 if (C4::Context->preference("Version") < TransformToNum($DBversion)) { Link Here
5696
    SetVersion($DBversion);
5696
    SetVersion($DBversion);
5697
}
5697
}
5698
5698
5699
5700
5701
$DBversion = "3.09.00.XXX";
5702
if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
5703
    $dbh->do("ALTER TABLE authorised_values MODIFY category varchar(16) NOT NULL DEFAULT '';");
5704
    $dbh->do("INSERT INTO authorised_values (category, authorised_value, lib) VALUES
5705
              ('REPORT_GROUP', 'CIRC', 'Circulation'),
5706
              ('REPORT_GROUP', 'CAT', 'Catalog'),
5707
              ('REPORT_GROUP', 'PAT', 'Patrons'),
5708
              ('REPORT_GROUP', 'ACQ', 'Acquisitions'),
5709
              ('REPORT_GROUP', 'ACC', 'Accounts');");
5710
5711
    $dbh->do("ALTER TABLE reports_dictionary ADD report_area varchar(6) DEFAULT NULL;");
5712
    $dbh->do("UPDATE reports_dictionary SET report_area = CASE area
5713
                  WHEN 1 THEN 'CIRC'
5714
                  WHEN 2 THEN 'CAT'
5715
                  WHEN 3 THEN 'PAT'
5716
                  WHEN 4 THEN 'ACQ'
5717
                  WHEN 5 THEN 'ACC'
5718
                  END;");
5719
    $dbh->do("ALTER TABLE reports_dictionary DROP area;");
5720
    $dbh->do("ALTER TABLE reports_dictionary ADD KEY dictionary_area_idx (report_area);");
5721
5722
    $dbh->do("ALTER TABLE saved_sql ADD report_area varchar(6) DEFAULT NULL;");
5723
    $dbh->do("ALTER TABLE saved_sql ADD report_group varchar(80) DEFAULT NULL;");
5724
    $dbh->do("ALTER TABLE saved_sql ADD report_subgroup varchar(80) DEFAULT NULL;");
5725
    $dbh->do("ALTER TABLE saved_sql ADD KEY sql_area_group_idx (report_group, report_subgroup);");
5726
5727
    print "Upgrade to $DBversion done saved_sql new fields report_group and report_area; authorised_values.category 16 char \n";
5728
    SetVersion($DBversion);
5729
}
5730
5699
=head1 FUNCTIONS
5731
=head1 FUNCTIONS
5700
5732
5701
=head2 TableExists($table)
5733
=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 138-143 canned reports and writing custom SQL reports.</p> Link Here
138
  <th>ID</th>
157
  <th>ID</th>
139
  <th>Report name</th>
158
  <th>Report name</th>
140
  <th>Type</th>
159
  <th>Type</th>
160
  <th>Area</th>
161
  <th>Group</th>
162
  <th>Subgroup</th>
141
  <th>Notes</th>
163
  <th>Notes</th>
142
  <th>Author</th>
164
  <th>Author</th>
143
  <th>Creation date</th>
165
  <th>Creation date</th>
Lines 155-160 canned reports and writing custom SQL reports.</p> Link Here
155
<td>[% savedreport.id %]</td>
177
<td>[% savedreport.id %]</td>
156
<td>[% savedreport.report_name %]</td>
178
<td>[% savedreport.report_name %]</td>
157
<td>[% savedreport.type %]</td>
179
<td>[% savedreport.type %]</td>
180
<td>[% savedreport.areaname %]</td>
181
<td>[% savedreport.groupname %]</td>
182
<td>[% savedreport.subgroupname %]</td>
158
<td>[% savedreport.notes %]</td>
183
<td>[% savedreport.notes %]</td>
159
<td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td>
184
<td>[% savedreport.borrowersurname %][% IF ( savedreport.borrowerfirstname ) %], [% savedreport.borrowerfirstname %][% END %] ([% savedreport.borrowernumber %])</td>
160
<td>[% savedreport.date_created %]</td>
185
<td>[% savedreport.date_created %]</td>
Lines 219-225 canned reports and writing custom SQL reports.</p> Link Here
219
<form action="/cgi-bin/koha/reports/guided_reports.pl">
244
<form action="/cgi-bin/koha/reports/guided_reports.pl">
220
<fieldset class="rows">
245
<fieldset class="rows">
221
<legend>Step 1 of 6: Choose a module to report on,[% IF (usecache) %] Set cache expiry, [% END %] and Choose report visibility </legend>
246
<legend>Step 1 of 6: Choose a module to report on,[% IF (usecache) %] Set cache expiry, [% END %] and Choose report visibility </legend>
222
<ol><li><label for="areas">Choose: </label><select name="areas" id="areas">
247
<ol><li><label for="area">Choose: </label><select name="area" id="area">
223
[% FOREACH area IN areas %]
248
[% FOREACH area IN areas %]
224
<option value="[% area.id %]">[% area.name %]</option>
249
<option value="[% area.id %]">[% area.name %]</option>
225
[% END %]
250
[% END %]
Lines 485-496 canned reports and writing custom SQL reports.</p> Link Here
485
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
510
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
486
<input type="hidden" name="sql" value="[% sql |html %]" />
511
<input type="hidden" name="sql" value="[% sql |html %]" />
487
<input type="hidden" name="type" value="[% type %]" />
512
<input type="hidden" name="type" value="[% type %]" />
513
<input type="hidden" name="area" value="[% area %]" />
488
<input type="hidden" name="public" value="[% public %]" />
514
<input type="hidden" name="public" value="[% public %]" />
489
<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
515
<input type="hidden" name="cache_expiry" value="[% cache_expiry %]" />
490
<fieldset class="rows">
516
<fieldset class="rows">
491
<legend>Save your custom report</legend>
517
<legend>Save your custom report</legend>
492
<ol>
518
<ol>
493
    <li><label for="reportname">Report name: </label><input type="text" id="reportname" name="reportname" /></li>
519
    <li><label for="reportname">Report name: </label><input type="text" id="reportname" name="reportname" /></li>
520
    [% IF groups_with_subgroups %]
521
    <li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
522
        [% FOR g IN groups_with_subgroups %]
523
            [% IF g.selected %]
524
    <option value="[% g.id %]" selected>[% g.name %]</option>
525
            [% ELSE %]
526
    <option value="[% g.id %]">[% g.name %]</option>
527
            [% END %]
528
    <script type="text/javascript">
529
        var g_sg = new Array();
530
            [% FOR sg IN g.subgroups %]
531
        g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
532
                [% IF sg.selected %]
533
        $(document).ready(function() {
534
            $("#subgroup").val("[% sg.id %]");
535
        });
536
                [% END %]
537
            [% END %]
538
        group_subgroups["[% g.id %]"] = g_sg;
539
    </script>
540
        [% END %]
541
    </select></li>
542
    <li><label for="subgroup">Report subgroup: </label><select name="subgroup" id="subgroup">
543
    </select></li>
544
    [% END %]
494
    <li><label for="notes">Notes:</label> <textarea name="notes" id="notes"></textarea></li>
545
    <li><label for="notes">Notes:</label> <textarea name="notes" id="notes"></textarea></li>
495
</ol></fieldset>
546
</ol></fieldset>
496
<fieldset class="action"><input type="hidden" name="phase" value="Save Report" />
547
<fieldset class="action"><input type="hidden" name="phase" value="Save Report" />
Lines 553-558 canned reports and writing custom SQL reports.</p> Link Here
553
[% END %]
604
[% END %]
554
605
555
[% IF ( create ) %]
606
[% IF ( create ) %]
607
<script type="text/javascript">
608
$(document).ready(function() {
609
    load_group_subgroups();
610
});
611
</script>
556
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
612
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
557
<fieldset class="rows">
613
<fieldset class="rows">
558
<legend>Create report from SQL</legend>
614
<legend>Create report from SQL</legend>
Lines 561-566 canned reports and writing custom SQL reports.</p> Link Here
561
        [% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />
617
        [% IF ( reportname ) %]<input type="text" id="reportname" name="reportname" value="[% reportname %]" />
562
        [% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %] 
618
        [% ELSE %]<input type="text" id="reportname" name="reportname" />[% END %] 
563
    </li>
619
    </li>
620
    [% IF groups_with_subgroups %]
621
    <li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
622
        [% FOR g IN groups_with_subgroups %]
623
            [% IF g.selected %]
624
    <option value="[% g.id %]" selected>[% g.name %]</option>
625
            [% ELSE %]
626
    <option value="[% g.id %]">[% g.name %]</option>
627
            [% END %]
628
    <script type="text/javascript">
629
        var g_sg = new Array();
630
            [% FOR sg IN g.subgroups %]
631
        g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
632
                [% IF sg.selected %]
633
        $(document).ready(function() {
634
            $("#subgroup").val("[% sg.id %]");
635
        });
636
                [% END %]
637
            [% END %]
638
        group_subgroups["[% g.id %]"] = g_sg;
639
    </script>
640
        [% END %]
641
    </select></li>
642
    <li><label for="subgroup">Report subgroup: </label><select name="subgroup" id="subgroup">
643
    </select></li>
644
    [% END %]
564
[% IF (public) %]
645
[% IF (public) %]
565
  <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>
646
  <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>
566
[% ELSE %]
647
[% ELSE %]
Lines 645-650 Sub report:<select name="subreport"> Link Here
645
[% END %]
726
[% END %]
646
727
647
[% IF ( editsql ) %]
728
[% IF ( editsql ) %]
729
<script type="text/javascript">
730
$(document).ready(function() {
731
    load_group_subgroups();
732
});
733
</script>
648
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
734
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
649
<input type="hidden" name="phase" value="Update SQL" />
735
<input type="hidden" name="phase" value="Update SQL" />
650
<input type="hidden" name="id" value="[% id %]"/>
736
<input type="hidden" name="id" value="[% id %]"/>
Lines 652-657 Sub report:<select name="subreport"> Link Here
652
<legend>Edit SQL report</legend>
738
<legend>Edit SQL report</legend>
653
<ol>
739
<ol>
654
<li><label for="reportname">Report name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" size="50" /></li>
740
<li><label for="reportname">Report name:</label><input type="text" id="reportname" name="reportname" value="[% reportname %]" size="50" /></li>
741
    [% IF groups_with_subgroups %]
742
    <li><label for="group">Report group: </label><select name="group" id="group" onChange="load_group_subgroups();">
743
        [% FOR g IN groups_with_subgroups %]
744
            [% IF g.selected %]
745
    <option value="[% g.id %]" selected>[% g.name %]</option>
746
            [% ELSE %]
747
    <option value="[% g.id %]">[% g.name %]</option>
748
            [% END %]
749
    <script type="text/javascript">
750
        var g_sg = new Array();
751
            [% FOR sg IN g.subgroups %]
752
        g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
753
                [% IF sg.selected %]
754
        $(document).ready(function() {
755
            $("#subgroup").val("[% sg.id %]");
756
        });
757
                [% END %]
758
            [% END %]
759
        group_subgroups["[% g.id %]"] = g_sg;
760
    </script>
761
        [% END %]
762
    </select></li>
763
    <li><label for="subgroup">Report subgroup: </label><select name="subgroup" id="subgroup">
764
    </select></li>
765
    [% END %]
655
[% IF (public) %]
766
[% IF (public) %]
656
  <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>
767
  <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>
657
[% ELSE %]
768
[% ELSE %]
Lines 719-730 Sub report:<select name="subreport"> Link Here
719
830
720
[% IF ( saved1 ) %]
831
[% IF ( saved1 ) %]
721
<div id="saved-reports-filter">
832
<div id="saved-reports-filter">
833
<script type="text/javascript">
834
$(document).ready(function() {
835
    no_subgroup_label = _( "-- All --" );
836
    load_group_subgroups();
837
});
838
</script>
722
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="get">
839
<form action="/cgi-bin/koha/reports/guided_reports.pl" method="get">
723
  <input type="hidden" name="phase" value="Use saved" />
840
  <input type="hidden" name="phase" value="Use saved" />
724
  <input type="hidden" name="filter_set" value="1" />
841
  <input type="hidden" name="filter_set" value="1" />
725
  <fieldset class="brief">
842
  <fieldset class="brief">
726
  <h3>Filter</h3>
843
  <h3>Filter</h3>
727
  <ol>
844
  <ol>
845
    <li><label for="group">Choose Group and Subgroup: </label>
846
    <select name="group" id="group" onChange="load_group_subgroups();">
847
        <option value="">-- All --</option>
848
    [% FOR g IN groups_with_subgroups %]
849
        [% IF g.selected %]
850
        <option value="[% g.id %]" selected>[% g.name %]</option>
851
        [% ELSE %]
852
        <option value="[% g.id %]">[% g.name %]</option>
853
        [% END %]
854
        <script type="text/javascript">
855
            var g_sg = new Array();
856
        [% FOR sg IN g.subgroups %]
857
            g_sg.push(["[% sg.id %]", "[% sg.name %]"]);
858
            [% IF sg.selected %]
859
            $(document).ready(function() {
860
                $("#subgroup").val("[% sg.id %]");
861
            });
862
            [% END %]
863
        [% END %]
864
            group_subgroups["[% g.id %]"] = g_sg;
865
        </script>
866
    [% END %]
867
    </select>
868
    <select name="subgroup" id="subgroup"></select>
869
    </li>
728
    <li><label for="filter_date">Date:</label> <input type="text" id="filter_date" name="filter_date" size="10" value="[% filter_date %]" class="datepicker" />
870
    <li><label for="filter_date">Date:</label> <input type="text" id="filter_date" name="filter_date" size="10" value="[% filter_date %]" class="datepicker" />
729
    <div class="hint">[% INCLUDE 'date-format.inc' %]</div>
871
    <div class="hint">[% INCLUDE 'date-format.inc' %]</div>
730
872
(-)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 (-204 / +278 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
use Koha::Cache;
32
use Koha::Cache;
Lines 69-75 my $session = $cookie ? get_session($cookie->value) : undef; Link Here
69
my $filter;
70
my $filter;
70
if ( $input->param("filter_set") ) {
71
if ( $input->param("filter_set") ) {
71
    $filter = {};
72
    $filter = {};
72
    $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword/;
73
    $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword group subgroup/;
73
    $session->param('report_filter', $filter) if $session;
74
    $session->param('report_filter', $filter) if $session;
74
    $template->param( 'filter_set' => 1 );
75
    $template->param( 'filter_set' => 1 );
75
}
76
}
Lines 86-106 if ( !$phase ) { Link Here
86
elsif ( $phase eq 'Build new' ) {
87
elsif ( $phase eq 'Build new' ) {
87
    # build a new report
88
    # build a new report
88
    $template->param( 'build1' => 1 );
89
    $template->param( 'build1' => 1 );
89
    $template->param( 'areas' => get_report_areas(), 'usecache' => $usecache, 'cache_expiry' => 300, 'public' => '0' );
90
    my $areas = get_report_areas();
90
}
91
    $template->param(
91
elsif ( $phase eq 'Use saved' ) {
92
        'areas' => [map { id => $_->[0], name => $_->[1] }, @$areas],
93
        'usecache' => $usecache,
94
        'cache_expiry' => 300,
95
        'public' => '0',
96
    );
97
} elsif ( $phase eq 'Use saved' ) {
98
92
    # use a saved report
99
    # use a saved report
93
    # get list of reports and display them
100
    # get list of reports and display them
101
    my $group = $input->param('group');
102
    my $subgroup = $input->param('subgroup');
103
    $filter->{group} = $group;
104
    $filter->{subgroup} = $subgroup;
94
    $template->param(
105
    $template->param(
95
        'saved1' => 1,
106
        'saved1' => 1,
96
        'savedreports' => get_saved_reports($filter),
107
        'savedreports' => get_saved_reports($filter),
97
        'usecache' => $usecache,
108
        'usecache' => $usecache,
109
        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
98
    );
110
    );
99
    if ($filter) {
100
        while ( my ($k, $v) = each %$filter ) {
101
            $template->param( "filter_$k" => $v ) if $v;
102
        }
103
    }
104
}
111
}
105
112
106
elsif ( $phase eq 'Delete Saved') {
113
elsif ( $phase eq 'Delete Saved') {
Lines 114-143 elsif ( $phase eq 'Delete Saved') { Link Here
114
121
115
elsif ( $phase eq 'Show SQL'){
122
elsif ( $phase eq 'Show SQL'){
116
	
123
	
117
	my $id = $input->param('reports');
124
    my $id = $input->param('reports');
118
    my ($sql,$type,$reportname,$notes) = get_saved_report($id);
125
    my $report = get_saved_report($id);
119
	$template->param(
126
    $template->param(
120
        'id'      => $id,
127
        'id'      => $id,
121
        'reportname' => $reportname,
128
        'reportname' => $report->{report_name},
122
        'notes'      => $notes,
129
        'notes'      => $report->{notes},
123
		'sql'     => $sql,
130
	'sql'     => $report->{savedsql},
124
		'showsql' => 1,
131
	'showsql' => 1,
125
    );
132
    );
126
}
133
}
127
134
128
elsif ( $phase eq 'Edit SQL'){
135
elsif ( $phase eq 'Edit SQL'){
129
	
136
	
130
    my $id = $input->param('reports');
137
    my $id = $input->param('reports');
131
    my ($sql,$type,$reportname,$notes, $cache_expiry, $public) = get_saved_report($id);
138
    my $report = get_saved_report($id);
139
    my $group = $report->{report_group};
140
    my $subgroup  = $report->{report_subgroup};
132
    $template->param(
141
    $template->param(
133
	    'sql'        => $sql,
142
        'sql'        => $report->{savedsql},
134
	    'reportname' => $reportname,
143
        'reportname' => $report->{report_name},
135
        'notes'      => $notes,
144
        'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
145
        'notes'      => $report->{notes},
136
        'id'         => $id,
146
        'id'         => $id,
137
        'cache_expiry' => $cache_expiry,
147
        'cache_expiry' => $report->{cache_expiry},
138
        'public' => $public,
148
        'public' => $report->{public},
139
        'usecache' => $usecache,
149
        'usecache' => $usecache,
140
	    'editsql'    => 1,
150
        'editsql'    => 1,
141
    );
151
    );
142
}
152
}
143
153
Lines 145-150 elsif ( $phase eq 'Update SQL'){ Link Here
145
    my $id         = $input->param('id');
155
    my $id         = $input->param('id');
146
    my $sql        = $input->param('sql');
156
    my $sql        = $input->param('sql');
147
    my $reportname = $input->param('reportname');
157
    my $reportname = $input->param('reportname');
158
    my $group      = $input->param('group');
159
    my $subgroup   = $input->param('subgroup');
148
    my $notes      = $input->param('notes');
160
    my $notes      = $input->param('notes');
149
    my $cache_expiry = $input->param('cache_expiry');
161
    my $cache_expiry = $input->param('cache_expiry');
150
    my $cache_expiry_units = $input->param('cache_expiry_units');
162
    my $cache_expiry_units = $input->param('cache_expiry_units');
Lines 178-193 elsif ( $phase eq 'Update SQL'){ Link Here
178
            'errors'    => \@errors,
190
            'errors'    => \@errors,
179
            'sql'       => $sql,
191
            'sql'       => $sql,
180
        );
192
        );
181
    }
193
    } else {
182
    else {
194
        update_sql( $id, {
183
        update_sql( $id, $sql, $reportname, $notes, $cache_expiry, $public );
195
                sql => $sql,
196
                name => $reportname,
197
                group => $group,
198
                subgroup => $subgroup,
199
                notes => $notes,
200
                cache_expiry => $cache_expiry,
201
                public => $public,
202
        } );
184
        $template->param(
203
        $template->param(
185
            'save_successful'       => 1,
204
            'save_successful'       => 1,
186
            'reportname'            => $reportname,
205
            'reportname'            => $reportname,
187
            'id'                    => $id,
206
            'id'                    => $id,
188
        );
207
        );
189
    }
208
    }
190
    
191
}
209
}
192
210
193
elsif ($phase eq 'retrieve results') {
211
elsif ($phase eq 'retrieve results') {
Lines 229-235 elsif ( $phase eq 'Report on this Area' ) { Link Here
229
      # they have choosen a new report and the area to report on
247
      # they have choosen a new report and the area to report on
230
      $template->param(
248
      $template->param(
231
          'build2' => 1,
249
          'build2' => 1,
232
          'area'   => $input->param('areas'),
250
          'area'   => $input->param('area'),
233
          'types'  => get_report_types(),
251
          'types'  => get_report_types(),
234
          'cache_expiry' => $cache_expiry,
252
          'cache_expiry' => $cache_expiry,
235
          'public' => $input->param('public'),
253
          'public' => $input->param('public'),
Lines 276-317 elsif ( $phase eq 'Choose these criteria' ) { Link Here
276
    my $area     = $input->param('area');
294
    my $area     = $input->param('area');
277
    my $type     = $input->param('type');
295
    my $type     = $input->param('type');
278
    my $column   = $input->param('column');
296
    my $column   = $input->param('column');
279
	my @definitions = $input->param('definition');
297
    my @definitions = $input->param('definition');
280
	my $definition = join (',',@definitions);
298
    my $definition = join (',',@definitions);
281
    my @criteria = $input->param('criteria_column');
299
    my @criteria = $input->param('criteria_column');
282
	my $query_criteria;
300
    my $query_criteria;
283
    foreach my $crit (@criteria) {
301
    foreach my $crit (@criteria) {
284
        my $value = $input->param( $crit . "_value" );
302
        my $value = $input->param( $crit . "_value" );
285
	
303
286
	# If value is not defined, then it may be range values
304
        # If value is not defined, then it may be range values
287
	if (!defined $value) {
305
        if (!defined $value) {
288
306
289
	    my $fromvalue = $input->param( "from_" . $crit . "_value" );
307
            my $fromvalue = $input->param( "from_" . $crit . "_value" );
290
	    my $tovalue   = $input->param( "to_"   . $crit . "_value" );
308
            my $tovalue   = $input->param( "to_"   . $crit . "_value" );
291
	    
309
292
	    # If the range values are dates
310
            # If the range values are dates
293
	    if ($fromvalue =~ C4::Dates->regexp('syspref') && $tovalue =~ C4::Dates->regexp('syspref')) { 
311
            if ($fromvalue =~ C4::Dates->regexp('syspref') && $tovalue =~ C4::Dates->regexp('syspref')) { 
294
		$fromvalue = C4::Dates->new($fromvalue)->output("iso");
312
                $fromvalue = C4::Dates->new($fromvalue)->output("iso");
295
		$tovalue = C4::Dates->new($tovalue)->output("iso");
313
                $tovalue = C4::Dates->new($tovalue)->output("iso");
296
	    }
314
            }
297
315
298
	    if ($fromvalue && $tovalue) {
316
            if ($fromvalue && $tovalue) {
299
		$query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'";
317
                $query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'";
300
	    }
318
            }
301
319
302
	} else {
303
304
	    # If value is a date
305
	    if ($value =~ C4::Dates->regexp('syspref')) { 
306
		$value = C4::Dates->new($value)->output("iso");
307
	    }
308
        # don't escape runtime parameters, they'll be at runtime
309
        if ($value =~ /<<.*>>/) {
310
            $query_criteria .= " AND $crit=$value";
311
        } else {
320
        } else {
312
            $query_criteria .= " AND $crit='$value'";
321
322
            # If value is a date
323
            if ($value =~ C4::Dates->regexp('syspref')) { 
324
                $value = C4::Dates->new($value)->output("iso");
325
            }
326
            # don't escape runtime parameters, they'll be at runtime
327
            if ($value =~ /<<.*>>/) {
328
                $query_criteria .= " AND $crit=$value";
329
            } else {
330
                $query_criteria .= " AND $crit='$value'";
331
            }
313
        }
332
        }
314
	}
315
    }
333
    }
316
    $template->param(
334
    $template->param(
317
        'build5'         => 1,
335
        'build5'         => 1,
Lines 413-418 elsif ( $phase eq 'Build report' ) { Link Here
413
      build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition );
431
      build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition );
414
    $template->param(
432
    $template->param(
415
        'showreport' => 1,
433
        'showreport' => 1,
434
        'area'       => $area,
416
        'sql'        => $sql,
435
        'sql'        => $sql,
417
        'type'       => $type,
436
        'type'       => $type,
418
        'cache_expiry' => $input->param('cache_expiry'),
437
        'cache_expiry' => $input->param('cache_expiry'),
Lines 421-443 elsif ( $phase eq 'Build report' ) { Link Here
421
}
440
}
422
441
423
elsif ( $phase eq 'Save' ) {
442
elsif ( $phase eq 'Save' ) {
424
	# Save the report that has just been built
443
    # Save the report that has just been built
444
    my $area           = $input->param('area');
425
    my $sql  = $input->param('sql');
445
    my $sql  = $input->param('sql');
426
    my $type = $input->param('type');
446
    my $type = $input->param('type');
427
    $template->param(
447
    $template->param(
428
        'save' => 1,
448
        'save' => 1,
449
        'area'  => $area,
429
        'sql'  => $sql,
450
        'sql'  => $sql,
430
        'type' => $type,
451
        'type' => $type,
431
        'cache_expiry' => $input->param('cache_expiry'),
452
        'cache_expiry' => $input->param('cache_expiry'),
432
        'public' => $input->param('public'),
453
        'public' => $input->param('public'),
454
        'groups_with_subgroups' => groups_with_subgroups($area), # in case we have a report group that matches area
433
    );
455
    );
434
}
456
}
435
457
436
elsif ( $phase eq 'Save Report' ) {
458
elsif ( $phase eq 'Save Report' ) {
437
    # save the sql pasted in by a user 
459
    # save the sql pasted in by a user
438
    my $sql  = $input->param('sql');
460
    my $area  = $input->param('area');
439
    my $name = $input->param('reportname');
461
    my $group = $input->param('group');
440
    my $type = $input->param('types');
462
    my $subgroup = $input->param('subgroup');
463
    my $sql   = $input->param('sql');
464
    my $name  = $input->param('reportname');
465
    my $type  = $input->param('types');
441
    my $notes = $input->param('notes');
466
    my $notes = $input->param('notes');
442
    my $cache_expiry = $input->param('cache_expiry');
467
    my $cache_expiry = $input->param('cache_expiry');
443
    my $cache_expiry_units = $input->param('cache_expiry_units');
468
    my $cache_expiry_units = $input->param('cache_expiry_units');
Lines 455-461 elsif ( $phase eq 'Save Report' ) { Link Here
455
      }
480
      }
456
    }
481
    }
457
    # 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
482
    # 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
458
    if( $cache_expiry >= 2592000 ){
483
    if( $cache_expiry && $cache_expiry >= 2592000 ){
459
      push @errors, {cache_expiry => $cache_expiry};
484
      push @errors, {cache_expiry => $cache_expiry};
460
    }
485
    }
461
    ## FIXME this is AFTER entering a name to save the report under
486
    ## FIXME this is AFTER entering a name to save the report under
Lines 463-469 elsif ( $phase eq 'Save Report' ) { Link Here
463
        push @errors, {sqlerr => $1};
488
        push @errors, {sqlerr => $1};
464
    }
489
    }
465
    elsif ($sql !~ /^(SELECT)/i) {
490
    elsif ($sql !~ /^(SELECT)/i) {
466
        push @errors, {queryerr => 1};
491
        push @errors, {queryerr => "No SELECT"};
467
    }
492
    }
468
    if (@errors) {
493
    if (@errors) {
469
        $template->param(
494
        $template->param(
Lines 477-637 elsif ( $phase eq 'Save Report' ) { Link Here
477
        );
502
        );
478
    }
503
    }
479
    else {
504
    else {
480
        my $id = save_report( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public );
505
        save_report( {
481
        $template->param(
506
                borrowernumber => $borrowernumber,
482
            'save_successful'       => 1,
507
                sql            => $sql,
483
            'reportname'            => $name,
508
                name           => $name,
484
            'id'                    => $id,
509
                area           => $area,
485
        );
510
                group          => $group,
511
                subgroup       => $subgroup,
512
                type           => $type,
513
                notes          => $notes,
514
                cache_expiry   => $cache_expiry,
515
                public         => $public,
516
            } );
517
        $template->param( 'save_successful' => 1, );
486
    }
518
    }
487
}
519
}
488
520
489
elsif ($phase eq 'Run this report'){
521
elsif ($phase eq 'Run this report'){
490
    # execute a saved report
522
    # execute a saved report
491
    my $limit  = 20;    # page size. # TODO: move to DB or syspref?
523
    my $limit      = 20; # page size. # TODO: move to DB or syspref?
492
    my $offset = 0;
524
    my $offset     = 0;
493
    my $report = $input->param('reports');
525
    my $report_id  = $input->param('reports');
494
    my @sql_params = $input->param('sql_params');
526
    my @sql_params = $input->param('sql_params');
495
    # offset algorithm
527
    # offset algorithm
496
    if ($input->param('page')) {
528
    if ($input->param('page')) {
497
        $offset = ($input->param('page') - 1) * $limit;
529
        $offset = ($input->param('page') - 1) * $limit;
498
    }
530
    }
499
    my ($sql,$type,$name,$notes) = get_saved_report($report);
531
500
    unless ($sql) {
532
    my ( $sql, $type, $name, $notes );
501
        push @errors, {no_sql_for_id=>$report};   
533
    if (my $report = get_saved_report($report_id)) {
502
    } 
534
        $sql   = $report->{savedsql};
503
    my @rows = ();
535
        $name  = $report->{report_name};
504
    # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
536
        $notes = $report->{notes};
505
    if ($sql =~ /<</ && !@sql_params) {
537
506
        # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
538
        my @rows = ();
507
        my @split = split /<<|>>/,$sql;
539
        # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
508
        my @tmpl_parameters;
540
        if ($sql =~ /<</ && !@sql_params) {
509
        for(my $i=0;$i<($#split/2);$i++) {
541
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
510
            my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
542
            my @split = split /<<|>>/,$sql;
511
            my $input;
543
            my @tmpl_parameters;
512
            my $labelid;
544
            for(my $i=0;$i<($#split/2);$i++) {
513
            if ($authorised_value eq "date") {
545
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
514
               $input = 'date';
546
                my $input;
515
            }
547
                my $labelid;
516
            elsif ($authorised_value) {
548
                if ($authorised_value eq "date") {
517
                my $dbh=C4::Context->dbh;
549
                   $input = 'date';
518
                my @authorised_values;
519
                my %authorised_lib;
520
                # builds list, depending on authorised value...
521
                if ( $authorised_value eq "branches" ) {
522
                    my $branches = GetBranchesLoop();
523
                    foreach my $thisbranch (@$branches) {
524
                        push @authorised_values, $thisbranch->{value};
525
                        $authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname};
526
                    }
527
                }
550
                }
528
                elsif ( $authorised_value eq "itemtypes" ) {
551
                elsif ($authorised_value) {
529
                    my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
552
                    my $dbh=C4::Context->dbh;
530
                    $sth->execute;
553
                    my @authorised_values;
531
                    while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
554
                    my %authorised_lib;
532
                        push @authorised_values, $itemtype;
555
                    # builds list, depending on authorised value...
533
                        $authorised_lib{$itemtype} = $description;
556
                    if ( $authorised_value eq "branches" ) {
557
                        my $branches = GetBranchesLoop();
558
                        foreach my $thisbranch (@$branches) {
559
                            push @authorised_values, $thisbranch->{value};
560
                            $authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname};
561
                        }
534
                    }
562
                    }
535
                }
563
                    elsif ( $authorised_value eq "itemtypes" ) {
536
                elsif ( $authorised_value eq "cn_source" ) {
564
                        my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
537
                    my $class_sources = GetClassSources();
565
                        $sth->execute;
538
                    my $default_source = C4::Context->preference("DefaultClassificationSource");
566
                        while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
539
                    foreach my $class_source (sort keys %$class_sources) {
567
                            push @authorised_values, $itemtype;
540
                        next unless $class_sources->{$class_source}->{'used'} or
568
                            $authorised_lib{$itemtype} = $description;
541
                                    ($class_source eq $default_source);
569
                        }
542
                        push @authorised_values, $class_source;
543
                        $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
544
                    }
570
                    }
545
                }
571
                    elsif ( $authorised_value eq "cn_source" ) {
546
                elsif ( $authorised_value eq "categorycode" ) {
572
                        my $class_sources = GetClassSources();
547
                    my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description");
573
                        my $default_source = C4::Context->preference("DefaultClassificationSource");
548
                    $sth->execute;
574
                        foreach my $class_source (sort keys %$class_sources) {
549
                    while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) {
575
                            next unless $class_sources->{$class_source}->{'used'} or
550
                        push @authorised_values, $categorycode;
576
                                        ($class_source eq $default_source);
551
                        $authorised_lib{$categorycode} = $description;
577
                            push @authorised_values, $class_source;
578
                            $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
579
                        }
552
                    }
580
                    }
581
                    elsif ( $authorised_value eq "categorycode" ) {
582
                        my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description");
583
                        $sth->execute;
584
                        while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) {
585
                            push @authorised_values, $categorycode;
586
                            $authorised_lib{$categorycode} = $description;
587
                        }
588
589
                        #---- "true" authorised value
590
                    }
591
                    else {
592
                        my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");
553
593
554
                    #---- "true" authorised value
594
                        $authorised_values_sth->execute( $authorised_value);
555
                }
556
                else {
557
                    my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");
558
559
                    $authorised_values_sth->execute( $authorised_value);
560
595
561
                    while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
596
                        while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
562
                        push @authorised_values, $value;
597
                            push @authorised_values, $value;
563
                        $authorised_lib{$value} = $lib;
598
                            $authorised_lib{$value} = $lib;
564
                        # For item location, we show the code and the libelle
599
                            # For item location, we show the code and the libelle
565
                        $authorised_lib{$value} = $lib;
600
                            $authorised_lib{$value} = $lib;
601
                        }
566
                    }
602
                    }
567
                }
603
                    $labelid = $text;
568
                $labelid = $text;
604
                    $labelid =~ s/\W//g;
569
                $labelid =~ s/\W//g;
605
                    $input =CGI::scrolling_list(      # FIXME: factor out scrolling_list
570
                $input =CGI::scrolling_list(      # FIXME: factor out scrolling_list
606
                        -name     => "sql_params",
571
                    -name     => "sql_params",
607
                        -id       => "sql_params_".$labelid,
572
                    -id       => "sql_params_".$labelid,
608
                        -values   => \@authorised_values,
573
                    -values   => \@authorised_values,
574
#                     -default  => $value,
609
#                     -default  => $value,
575
                    -labels   => \%authorised_lib,
610
                        -labels   => \%authorised_lib,
576
                    -override => 1,
611
                        -override => 1,
577
                    -size     => 1,
612
                        -size     => 1,
578
                    -multiple => 0,
613
                        -multiple => 0,
579
                    -tabindex => 1,
614
                        -tabindex => 1,
580
                );
615
                    );
581
616
                } else {
582
            } else {
617
                    $input = "text";
583
                $input = "text";
618
                }
619
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
584
            }
620
            }
585
            push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
621
            $template->param('sql'         => $sql,
586
        }
622
                            'name'         => $name,
587
        $template->param('sql'         => $sql,
623
                            'sql_params'   => \@tmpl_parameters,
588
                        'name'         => $name,
624
                            'enter_params' => 1,
589
                        'sql_params'   => \@tmpl_parameters,
625
                            'reports'      => $report_id,
590
                        'enter_params' => 1,
626
                            );
591
                        'reports'      => $report,
592
                        );
593
    } else {
594
        # OK, we have parameters, or there are none, we run the report
595
        # if there were parameters, replace before running
596
        # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
597
        my @split = split /<<|>>/,$sql;
598
        my @tmpl_parameters;
599
        for(my $i=0;$i<$#split/2;$i++) {
600
            my $quoted = C4::Context->dbh->quote($sql_params[$i]);
601
            # if there are special regexp chars, we must \ them
602
            $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
603
            $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
604
        }
605
        my ($sth, $errors) = execute_query($sql, $offset, $limit);
606
        my $total = nb_rows($sql) || 0;
607
        unless ($sth) {
608
            die "execute_query failed to return sth for report $report: $sql";
609
        } else {
627
        } else {
610
            my $headref = $sth->{NAME} || [];
628
            # OK, we have parameters, or there are none, we run the report
611
            my @headers = map { +{ cell => $_ } } @$headref;
629
            # if there were parameters, replace before running
612
            $template->param(header_row => \@headers);
630
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
613
            while (my $row = $sth->fetchrow_arrayref()) {
631
            my @split = split /<<|>>/,$sql;
614
                my @cells = map { +{ cell => $_ } } @$row;
632
            my @tmpl_parameters;
615
                push @rows, { cells => \@cells };
633
            for(my $i=0;$i<$#split/2;$i++) {
634
                my $quoted = C4::Context->dbh->quote($sql_params[$i]);
635
                # if there are special regexp chars, we must \ them
636
                $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
637
                $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
638
            }
639
            my ($sth, $errors) = execute_query($sql, $offset, $limit);
640
            my $total = nb_rows($sql) || 0;
641
            unless ($sth) {
642
                die "execute_query failed to return sth for report $report_id: $sql";
643
            } else {
644
                my $headref = $sth->{NAME} || [];
645
                my @headers = map { +{ cell => $_ } } @$headref;
646
                $template->param(header_row => \@headers);
647
                while (my $row = $sth->fetchrow_arrayref()) {
648
                    my @cells = map { +{ cell => $_ } } @$row;
649
                    push @rows, { cells => \@cells };
650
                }
616
            }
651
            }
617
        }
618
652
619
        my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
653
            my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
620
        my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&amp;phase=Run%20this%20report";
654
            my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report_id&amp;phase=Run%20this%20report";
621
        if (@sql_params) {
655
            if (@sql_params) {
622
            $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params);
656
                $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params);
657
            }
658
            $template->param(
659
                'results' => \@rows,
660
                'sql'     => $sql,
661
                'id'      => $report_id,
662
                'execute' => 1,
663
                'name'    => $name,
664
                'notes'   => $notes,
665
                'errors'  => $errors,
666
                'pagination_bar'  => pagination_bar($url, $totpages, $input->param('page')),
667
                'unlimited_total' => $total,
668
            );
623
        }
669
        }
624
        $template->param(
670
    }
625
            'results' => \@rows,
671
    else {
626
            'sql'     => $sql,
672
        push @errors, { no_sql_for_id => $report_id };
627
            'id'      => $report,
628
            'execute' => 1,
629
            'name'    => $name,
630
            'notes'   => $notes,
631
            'errors'  => $errors,
632
            'pagination_bar'  => pagination_bar($url, $totpages, $input->param('page')),
633
            'unlimited_total' => $total,
634
        );
635
    }
673
    }
636
}
674
}
637
675
Lines 681-696 elsif ($phase eq 'Export'){ Link Here
681
    );
719
    );
682
}
720
}
683
721
684
elsif ($phase eq 'Create report from SQL') {
722
elsif ( $phase eq 'Create report from SQL' ) {
685
	# allow the user to paste in sql
723
686
    if ($input->param('sql')) {
724
    my ($group, $subgroup);
725
    # allow the user to paste in sql
726
    if ( $input->param('sql') ) {
727
        $group = $input->param('report_group');
728
        $subgroup  = $input->param('report_subgroup');
687
        $template->param(
729
        $template->param(
688
            'sql'           => $input->param('sql'),
730
            'sql'           => $input->param('sql'),
689
            'reportname'    => $input->param('reportname'),
731
            'reportname'    => $input->param('reportname'),
690
            'notes'         => $input->param('notes'),
732
            'notes'         => $input->param('notes'),
691
        );
733
        );
692
    }
734
    }
693
        $template->param('create' => 1, 'public' => '0', 'cache_expiry' => 300, 'usecache' => $usecache);
735
    $template->param(
736
        'create' => 1,
737
        'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
738
        'public' => '0',
739
        'cache_expiry' => 300,
740
        'usecache' => $usecache,
741
    );
694
}
742
}
695
743
696
elsif ($phase eq 'Create Compound Report'){
744
elsif ($phase eq 'Create Compound Report'){
Lines 729-731 $template->param( 'referer' => $input->referer(), Link Here
729
                );
777
                );
730
778
731
output_html_with_http_headers $input, $cookie, $template->output;
779
output_html_with_http_headers $input, $cookie, $template->output;
780
781
sub groups_with_subgroups {
782
    my ($group, $subgroup) = @_;
783
784
    my $groups_with_subgroups = get_report_groups();
785
    my @g_sg;
786
    while (my ($g_id, $v) = each %$groups_with_subgroups) {
787
        my @subgroups;
788
        if (my $sg = $v->{subgroups}) {
789
            while (my ($sg_id, $n) = each %$sg) {
790
                push @subgroups, {
791
                    id => $sg_id,
792
                    name => $n,
793
                    selected => ($group && $g_id eq $group && $subgroup && $sg_id eq $subgroup ),
794
                };
795
            }
796
        }
797
        push @g_sg, {
798
            id => $g_id,
799
            name => $v->{name},
800
            selected => ($group && $g_id eq $group),
801
            subgroups => \@subgroups,
802
        };
803
    }
804
    return \@g_sg;
805
}
(-)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