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

(-)a/C4/Koha.pm (+21 lines)
Lines 57-62 BEGIN { Link Here
57
		&getitemtypeimagelocation
57
		&getitemtypeimagelocation
58
		&GetAuthorisedValues
58
		&GetAuthorisedValues
59
		&GetAuthorisedValueCategories
59
		&GetAuthorisedValueCategories
60
                &IsAuthorisedValueCategory
60
		&GetKohaAuthorisedValues
61
		&GetKohaAuthorisedValues
61
		&GetKohaAuthorisedValuesFromField
62
		&GetKohaAuthorisedValuesFromField
62
    &GetKohaAuthorisedValueLib
63
    &GetKohaAuthorisedValueLib
Lines 1104-1109 sub GetAuthorisedValueCategories { Link Here
1104
    return \@results;
1105
    return \@results;
1105
}
1106
}
1106
1107
1108
=head2 IsAuthorisedValueCategory
1109
1110
    $is_auth_val_category = IsAuthorisedValueCategory($category);
1111
1112
Returns whether a given category name is a valid one
1113
1114
=cut
1115
1116
sub IsAuthorisedValueCategory($) {
1117
    my $category = shift;
1118
    # Build a hash of authorised value categories for quick search
1119
    my %authorised_value_categories = map { $_ => 1 } (GetAuthorisedValueCategories());
1120
1121
    if ( exists $authorised_value_categories{$category}) {
1122
        return 1;
1123
    }
1124
1125
    return 0; 
1126
} 
1127
1107
=head2 GetAuthorisedValueByCode
1128
=head2 GetAuthorisedValueByCode
1108
1129
1109
$authhorised_value = GetAuthorisedValueByCode( $category, $authvalcode );
1130
$authhorised_value = GetAuthorisedValueByCode( $category, $authvalcode );
(-)a/C4/Reports/Guided.pm (-1 / +94 lines)
Lines 32-38 use XML::Simple; Link Here
32
use XML::Dumper;
32
use XML::Dumper;
33
use C4::Debug;
33
use C4::Debug;
34
# use Smart::Comments;
34
# use Smart::Comments;
35
# use Data::Dumper;
35
use Data::Dumper;
36
36
37
BEGIN {
37
BEGIN {
38
    # set the version for version checking
38
    # set the version for version checking
Lines 45-50 BEGIN { Link Here
45
      get_column_type get_distinct_values save_dictionary get_from_dictionary
45
      get_column_type get_distinct_values save_dictionary get_from_dictionary
46
      delete_definition delete_report format_results get_sql
46
      delete_definition delete_report format_results get_sql
47
      nb_rows update_sql build_authorised_value_list
47
      nb_rows update_sql build_authorised_value_list
48
      GetReservedAuthorisedValues
49
      GetParametersFromSQL
50
      IsAuthorisedValueValid
51
      ValidateSQLParameters
48
    );
52
    );
49
}
53
}
50
54
Lines 917-922 sub build_authorised_value_list { Link Here
917
    return (\@authorised_values, \%authorised_lib);
921
    return (\@authorised_values, \%authorised_lib);
918
}
922
}
919
923
924
=head2 GetReservedAuthorisedValues
925
926
    my %reserved_authorised_values = GetReservedAuthorisedValues();
927
928
Returns a hash containig all reserved words
929
930
=cut
931
932
sub GetReservedAuthorisedValues() {
933
    my %reserved_authorised_values =
934
            map { $_ => 1 } ( "date",
935
                              "branches",
936
                              "itemtypes",
937
                              "cn_source",
938
                              "categorycode" );
939
940
   return \%reserved_authorised_values;
941
}
942
943
944
=head2 IsAuthorisedValueValid
945
946
    my $is_valid_ath_value = IsAuthorisedValueValid($authorised_value)
947
948
Returns 1 if $authorised_value is on the reserved authorised values list or
949
in the authorised value categories defined in 
950
951
=cut
952
953
sub IsAuthorisedValueValid($) {
954
955
    my $authorised_value = shift;
956
    my %reserved_authorised_values = GetReservedAuthorisedValues();
957
958
    if ( exists $reserved_authorised_values{$authorised_value} ||
959
         IsAuthorisedValueCategory($authorised_value)   ) {
960
        return 1;
961
    }
962
963
    return 0;
964
}
965
966
=head2 GetParametersFromSQL
967
968
    my @sql_parameters = GetParametersFromSQL($sql)
969
970
Returns an arrayref of hashes containing the keys name and authval
971
972
=cut
973
974
sub GetParametersFromSQL($) {
975
976
    my $sql = shift ;
977
    my @split = split(/<<|>>/,$sql);
978
    my @sql_parameters = ();
979
    
980
    for ( my $i = 0; $i < ($#split/2) ; $i++ ) {
981
        my ($name,$authval) = split(/\|/,$split[$i*2+1]);
982
        push @sql_parameters, { 'name' => $name, 'authval' => $authval };
983
    }
984
985
    return \@sql_parameters;
986
}
987
988
=head2 ValidateSQLParameters
989
990
    my @problematic_parameters = ValidateSQLParameters($sql)
991
992
Returns an arrayref of hashes containing the keys name and authval of
993
those SQL parameters that do not correspond to valid authorised names
994
995
=cut
996
997
sub ValidateSQLParameters($) {
998
999
    my $sql = shift;
1000
    my @problematic_parameters = ();
1001
    my $sql_parameters = GetParametersFromSQL($sql);
1002
1003
    foreach my $sql_parameter (@$sql_parameters) {
1004
        if ( defined $sql_parameter->{'authval'} ) {
1005
            push @problematic_parameters, $sql_parameter unless
1006
                IsAuthorisedValueValid($sql_parameter->{'authval'});
1007
        }
1008
    }
1009
1010
    return \@problematic_parameters;
1011
}
1012
920
1;
1013
1;
921
__END__
1014
__END__
922
1015
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-11 / +76 lines)
Lines 549-576 canned reports and writing custom SQL reports.</p> Link Here
549
</form>
549
</form>
550
[% END %]
550
[% END %]
551
551
552
[% IF ( warn_authval_problem ) %]
553
     <form action='/cgi-bin/koha/reports/guided_reports.pl'>
554
        <!--Every parameter the user issued is provided as a hidden field for recovery-->
555
        <input type='hidden' name='id' value='[% id %]' />
556
        <input type='hidden' name='sql' value='[% sql %]' />
557
        <input type='hidden' name='reportname' value='[% reportname %]' />
558
        <input type='hidden' name='group' value='[% group %]' />
559
        <input type='hidden' name='subgroup' value='[% subgroup %]' />
560
        <input type='hidden' name='notes' value='[% notes %]' />
561
        <input type='hidden' name='cache_expiry' value='[% cache_expiry %]' />
562
        <input type='hidden' name='cache_expiry_units' value='[% cache_expiry_units %]' />
563
        <input type='hidden' name='public' value='[% public %]' />
564
565
        <div class="dialog alert">
566
            <h3>Errors found when processing parameters for report: [% name %]</h3>
567
            [% FOREACH problematic_authval IN problematic_authvals %]
568
                <p>
569
                <strong>[% problematic_authval.name %]:</strong> The authorised value category (<strong>[% problematic_authval.authval %]</strong>)
570
                    you selected does not exist.
571
                </p>
572
            [% END %]
573
            <!-- Save Anyway Form -->
574
            <form action='/cgi-bin/koha/reports/guided_reports.pl'>
575
            <!--Every parameter the user issued is provided as a hidden field for recovery-->
576
                <input type='hidden' name='id' value='[% id %]' />
577
                <input type='hidden' name='sql' value='[% sql %]' />
578
                <input type='hidden' name='reportname' value='[% reportname %]' />
579
                <input type='hidden' name='group' value='[% group %]' />
580
                <input type='hidden' name='subgroup' value='[% subgroup %]' />
581
                <input type='hidden' name='notes' value='[% notes %]' />
582
                <input type='hidden' name='cache_expiry' value='[% cache_expiry %]' />
583
                <input type='hidden' name='cache_expiry_units' value='[% cache_expiry_units %]' />
584
                <input type='hidden' name='public' value='[% public %]' />
585
            [% IF ( phase_update) %]
586
                <input type='hidden' name='phase' value='Update SQL' />
587
                <input type="submit" name="save_anyway" class="approve" value="Save anyway" />
588
            [% ELSIF ( phase_save) %]
589
                <input type='hidden' name='area' value='[% area %]' />
590
                <input type='hidden' name='phase' value='Save Report' />
591
                <input type="submit" name="save_anyway" class="approve" value="Save anyway" />
592
            [% END %]
593
            </form>
594
            <!-- Go back to editing -->
595
            <form action='/cgi-bin/koha/reports/guided_reports.pl'>
596
                <input type="button" name='back' class="deny" value="Edit SQL"
597
                                    onclick="javascript:history.back()" />
598
            </form>
599
        </div>
600
    </form>
601
[% END %]
602
552
[% IF ( enter_params ) %]
603
[% IF ( enter_params ) %]
553
    <form action='/cgi-bin/koha/reports/guided_reports.pl'>
604
    <form action='/cgi-bin/koha/reports/guided_reports.pl'>
554
        <input type='hidden' name='phase' value='Run this report' />
555
        <input type='hidden' name='reports' value="[% reports %]" />
605
        <input type='hidden' name='reports' value="[% reports %]" />
606
    [% IF ( auth_val_error ) %]
607
        <input type='hidden' name='phase' value='Edit SQL' />
608
        <div class="dialog alert">
609
            <h3>Errors found when processing parameters for report: [% name %]</h3>
610
            [% FOREACH auth_val_error IN auth_val_errors %]
611
                <p>
612
                    <strong>[% auth_val_error.entry %]:</strong> The authorised value category (<strong>[% auth_val_error.auth_val %]</strong>)
613
                    you selected does not exist.
614
                </p>
615
            [% END %]
616
        </div>
617
        <fieldset class="action"><input type="submit" value="Edit SQL" /></fieldset>
618
    [% ELSE %]
619
        <input type='hidden' name='phase' value='Run this report' />
556
        <h1>Enter parameters for report [% name %]:</h1>
620
        <h1>Enter parameters for report [% name %]:</h1>
557
        [% IF ( notes ) %]<p>[% notes %]</p>[% END %]
621
        [% IF ( notes ) %]<p>[% notes %]</p>[% END %]
558
        <fieldset class="rows">
622
        <fieldset class="rows">
559
            <ol>
623
            <ol>
560
        [% FOREACH sql_param IN sql_params %]
624
            [% FOREACH sql_param IN sql_params %]
561
            [% IF sql_param.input == 'date' %]
625
                [% IF sql_param.input == 'date' %]
562
                <li>
626
                    <li>
563
                <label for="date_[% sql_param_entry %][% loop.count %]">[% sql_param.entry %]:</label> <input id="date_[% sql_param_entry %][% loop.count %]" type="text" value="" size="10" name="sql_params" class="datepicker" />
627
                    <label for="date_[% sql_param_entry %][% loop.count %]">[% sql_param.entry %]:</label> <input id="date_[% sql_param_entry %][% loop.count %]" type="text" value="" size="10" name="sql_params" class="datepicker" />
564
                </li>
628
                    </li>
565
            [% ELSIF ( sql_param.input == 'text' ) %]
629
                [% ELSIF ( sql_param.input == 'text' ) %]
566
                <li><label for="sql_params[% loop.count %]">[% sql_param.entry %]: </label><input id="sql_params[% loop.count %]" type="text" name="sql_params" /></li>
630
                    <li><label for="sql_params[% loop.count %]">[% sql_param.entry %]: </label><input id="sql_params[% loop.count %]" type="text" name="sql_params" /></li>
567
            [% ELSE %]
631
                [% ELSE %]
568
                <li><label for="sql_params_[% sql_param.labelid %]">[% sql_param.entry %]:</label> [% sql_param.input %]</li>
632
                    <li><label for="sql_params_[% sql_param.labelid %]">[% sql_param.entry %]:</label> [% sql_param.input %]</li>
633
                [% END %]
569
            [% END %]
634
            [% END %]
570
        [% END %]
571
            </ol>
635
            </ol>
572
        </fieldset>
636
        </fieldset>
573
        <fieldset class="action"><input type="submit" value="Run the report" /></fieldset>
637
        <fieldset class="action"><input type="submit" value="Run the report" /></fieldset>
638
    [% END %]
574
    </form>
639
    </form>
575
[% END %]
640
[% END %]
576
641
(-)a/reports/guided_reports.pl (-50 / +123 lines)
Lines 29-34 use C4::Output; Link Here
29
use C4::Dates qw/format_date/;
29
use C4::Dates qw/format_date/;
30
use C4::Debug;
30
use C4::Debug;
31
use C4::Branch; # XXX subfield_is_koha_internal_p
31
use C4::Branch; # XXX subfield_is_koha_internal_p
32
use C4::Koha qw/IsAuthorisedValueCategory/;
33
use Carp;
34
use Data::Dumper;
32
35
33
=head1 NAME
36
=head1 NAME
34
37
Lines 132-138 elsif ( $phase eq 'Show SQL'){ Link Here
132
}
135
}
133
136
134
elsif ( $phase eq 'Edit SQL'){
137
elsif ( $phase eq 'Edit SQL'){
135
	
136
    my $id = $input->param('reports');
138
    my $id = $input->param('reports');
137
    my $report = get_saved_report($id);
139
    my $report = get_saved_report($id);
138
    my $group = $report->{report_group};
140
    my $group = $report->{report_group};
Lines 160-165 elsif ( $phase eq 'Update SQL'){ Link Here
160
    my $cache_expiry = $input->param('cache_expiry');
162
    my $cache_expiry = $input->param('cache_expiry');
161
    my $cache_expiry_units = $input->param('cache_expiry_units');
163
    my $cache_expiry_units = $input->param('cache_expiry_units');
162
    my $public = $input->param('public');
164
    my $public = $input->param('public');
165
    my $save_anyway = $input->param('save_anyway');
163
166
164
    my @errors;
167
    my @errors;
165
168
Lines 184-209 elsif ( $phase eq 'Update SQL'){ Link Here
184
    elsif ($sql !~ /^(SELECT)/i) {
187
    elsif ($sql !~ /^(SELECT)/i) {
185
        push @errors, {queryerr => 1};
188
        push @errors, {queryerr => 1};
186
    }
189
    }
190
187
    if (@errors) {
191
    if (@errors) {
188
        $template->param(
192
        $template->param(
189
            'errors'    => \@errors,
193
            'errors'    => \@errors,
190
            'sql'       => $sql,
194
            'sql'       => $sql,
191
        );
195
        );
192
    } else {
196
    } else {
193
        update_sql( $id, {
197
194
                sql => $sql,
198
        # Check defined SQL parameters for authorised value validity
195
                name => $reportname,
199
        my $problematic_authvals = ValidateSQLParameters($sql);
196
                group => $group,
200
        
197
                subgroup => $subgroup,
201
        if ( scalar @$problematic_authvals > 0 && not $save_anyway ) {
198
                notes => $notes,
202
            # There's at least one problematic parameter, report to the
199
                cache_expiry => $cache_expiry,
203
            # GUI and provide all user input for further actions
200
                public => $public,
204
            $template->param(
201
        } );
205
                'id' => $id,
202
        $template->param(
206
                'sql' => $sql,
203
            'save_successful'       => 1,
207
                'reportname' => $reportname,
204
            'reportname'            => $reportname,
208
                'group' => $group,
205
            'id'                    => $id,
209
                'subgroup' => $subgroup,
206
        );
210
                'notes' => $notes,
211
                'cache_expiry' => $cache_expiry,
212
                'cache_expiry_units' => $cache_expiry_units,
213
                'public' => $public,
214
                'problematic_authvals' => $problematic_authvals,
215
                'warn_authval_problem' => 1,
216
                'phase_update' => 1
217
            );
218
219
        } else {
220
            # No params problem found or asked to save anyway
221
            update_sql( $id, {
222
                    sql => $sql,
223
                    name => $reportname,
224
                    group => $group,
225
                    subgroup => $subgroup,
226
                    notes => $notes,
227
                    cache_expiry => $cache_expiry,
228
                    public => $public,
229
                } );
230
            $template->param(
231
                'save_successful'       => 1,
232
                'reportname'            => $reportname,
233
                'id'                    => $id,
234
            );
235
        }
207
    }
236
    }
208
}
237
}
209
238
Lines 466-471 elsif ( $phase eq 'Save Report' ) { Link Here
466
    my $cache_expiry = $input->param('cache_expiry');
495
    my $cache_expiry = $input->param('cache_expiry');
467
    my $cache_expiry_units = $input->param('cache_expiry_units');
496
    my $cache_expiry_units = $input->param('cache_expiry_units');
468
    my $public = $input->param('public');
497
    my $public = $input->param('public');
498
    my $save_anyway = $input->param('save_anyway');
469
499
470
500
471
    # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
501
    # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
Lines 489-494 elsif ( $phase eq 'Save Report' ) { Link Here
489
    elsif ($sql !~ /^(SELECT)/i) {
519
    elsif ($sql !~ /^(SELECT)/i) {
490
        push @errors, {queryerr => "No SELECT"};
520
        push @errors, {queryerr => "No SELECT"};
491
    }
521
    }
522
492
    if (@errors) {
523
    if (@errors) {
493
        $template->param(
524
        $template->param(
494
            'errors'    => \@errors,
525
            'errors'    => \@errors,
Lines 499-523 elsif ( $phase eq 'Save Report' ) { Link Here
499
            'cache_expiry' => $cache_expiry,
530
            'cache_expiry' => $cache_expiry,
500
            'public'    => $public,
531
            'public'    => $public,
501
        );
532
        );
502
    }
533
    } else {
503
    else {
534
        # Check defined SQL parameters for authorised value validity
504
        my $id = save_report( {
535
        my $problematic_authvals = ValidateSQLParameters($sql);
505
                borrowernumber => $borrowernumber,
536
506
                sql            => $sql,
537
        if ( scalar @$problematic_authvals > 0 && not $save_anyway ) {
507
                name           => $name,
538
            # There's at least one problematic parameter, report to the
508
                area           => $area,
539
            # GUI and provide all user input for further actions
509
                group          => $group,
540
            $template->param(
510
                subgroup       => $subgroup,
541
                'area' => $area,
511
                type           => $type,
542
                'group' =>  $group,
512
                notes          => $notes,
543
                'subgroup' => $subgroup,
513
                cache_expiry   => $cache_expiry,
544
                'sql' => $sql,
514
                public         => $public,
545
                'reportname' => $name,
515
            } );
546
                'type' => $type,
516
        $template->param(
547
                'notes' => $notes,
517
            'save_successful' => 1,
548
                'cache_expiry' => $cache_expiry,
518
            'reportname'      => $name,
549
                'cache_expiry_units' => $cache_expiry_units,
519
            'id'              => $id,
550
                'public' => $public,
520
        );
551
                'problematic_authvals' => $problematic_authvals,
552
                'warn_authval_problem' => 1,
553
                'phase_save' => 1
554
            );
555
        } else {
556
            # No params problem found or asked to save anyway
557
            my $id = save_report( {
558
                    borrowernumber => $borrowernumber,
559
                    sql            => $sql,
560
                    name           => $name,
561
                    area           => $area,
562
                    group          => $group,
563
                    subgroup       => $subgroup,
564
                    type           => $type,
565
                    notes          => $notes,
566
                    cache_expiry   => $cache_expiry,
567
                    public         => $public,
568
                } );
569
            $template->param(
570
                'save_successful' => 1,
571
                'reportname'      => $name,
572
                'id'              => $id,
573
            );
574
        }
521
    }
575
    }
522
}
576
}
523
577
Lines 549-562 elsif ($phase eq 'Run this report'){ Link Here
549
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
603
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
550
            my @split = split /<<|>>/,$sql;
604
            my @split = split /<<|>>/,$sql;
551
            my @tmpl_parameters;
605
            my @tmpl_parameters;
606
            my @authval_errors;
552
            for(my $i=0;$i<($#split/2);$i++) {
607
            for(my $i=0;$i<($#split/2);$i++) {
553
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
608
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
554
                my $input;
609
                my $input;
555
                my $labelid;
610
                my $labelid;
556
                if ($authorised_value eq "date") {
611
                if ( not defined $authorised_value ) {
557
                   $input = 'date';
612
                    # no authorised value input, provide a text box
558
                }
613
                    $input = "text";
559
                elsif ($authorised_value) {
614
                } elsif ( $authorised_value eq "date" ) {
615
                    # require a date, provide a date picker
616
                    $input = 'date';
617
                } else {
618
                    # defined $authorised_value, and not 'date'
560
                    my $dbh=C4::Context->dbh;
619
                    my $dbh=C4::Context->dbh;
561
                    my @authorised_values;
620
                    my @authorised_values;
562
                    my %authorised_lib;
621
                    my %authorised_lib;
Lines 597-611 elsif ($phase eq 'Run this report'){ Link Here
597
                        #---- "true" authorised value
656
                        #---- "true" authorised value
598
                    }
657
                    }
599
                    else {
658
                    else {
600
                        my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");
659
                        if ( IsAuthorisedValueCategory($authorised_value) ) {
601
660
                            my $query = '
602
                        $authorised_values_sth->execute( $authorised_value);
661
                            SELECT authorised_value,lib
603
662
                            FROM authorised_values
604
                        while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
663
                            WHERE category=?
605
                            push @authorised_values, $value;
664
                            ORDER BY lib
606
                            $authorised_lib{$value} = $lib;
665
                            ';
607
                            # For item location, we show the code and the libelle
666
                            my $authorised_values_sth = $dbh->prepare($query);
608
                            $authorised_lib{$value} = $lib;
667
                            $authorised_values_sth->execute( $authorised_value);
668
669
                            while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
670
                                push @authorised_values, $value;
671
                                $authorised_lib{$value} = $lib;
672
                                # For item location, we show the code and the libelle
673
                                $authorised_lib{$value} = $lib;
674
                            }
675
                        } else {
676
                            # not exists $authorised_value_categories{$authorised_value})
677
                            push @authval_errors, {'entry' => $text,
678
                                                   'auth_val' => $authorised_value };
679
                            # tell the template there's an error
680
                            $template->param( auth_val_error => 1 );
681
                            # skip scrolling list creation and params push
682
                            next;
609
                        }
683
                        }
610
                    }
684
                    }
611
                    $labelid = $text;
685
                    $labelid = $text;
Lines 621-634 elsif ($phase eq 'Run this report'){ Link Here
621
                        -multiple => 0,
695
                        -multiple => 0,
622
                        -tabindex => 1,
696
                        -tabindex => 1,
623
                    );
697
                    );
624
                } else {
625
                    $input = "text";
626
                }
698
                }
699
627
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
700
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
628
            }
701
            }
629
            $template->param('sql'         => $sql,
702
            $template->param('sql'         => $sql,
630
                            'name'         => $name,
703
                            'name'         => $name,
631
                            'sql_params'   => \@tmpl_parameters,
704
                            'sql_params'   => \@tmpl_parameters,
705
                            'auth_val_errors'  => \@authval_errors,
632
                            'enter_params' => 1,
706
                            'enter_params' => 1,
633
                            'reports'      => $report_id,
707
                            'reports'      => $report_id,
634
                            );
708
                            );
635
- 

Return to bug 9659