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

(-)a/C4/Koha.pm (+23 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 1105-1110 sub GetAuthorisedValueCategories { Link Here
1105
    return \@results;
1106
    return \@results;
1106
}
1107
}
1107
1108
1109
=head2 IsAuthorisedValueCategory
1110
1111
    $is_auth_val_category = IsAuthorisedValueCategory($category);
1112
1113
Returns whether a given category name is a valid one
1114
1115
=cut
1116
1117
sub IsAuthorisedValueCategory {
1118
    my $category = shift;
1119
    my $query = '
1120
        SELECT category
1121
        FROM authorised_values
1122
        WHERE BINARY category=?
1123
        LIMIT 1
1124
    ';
1125
    my $sth = C4::Context->dbh->prepare($query);
1126
    $sth->execute($category);
1127
    $sth->fetchrow ? return 1
1128
                   : return 0;
1129
}
1130
1108
=head2 GetAuthorisedValueByCode
1131
=head2 GetAuthorisedValueByCode
1109
1132
1110
$authhorised_value = GetAuthorisedValueByCode( $category, $authvalcode );
1133
$authhorised_value = GetAuthorisedValueByCode( $category, $authvalcode );
(-)a/C4/Reports/Guided.pm (-19 / +108 lines)
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 62-70 C4::Reports::Guided - Module for generating guided reports Link Here
62
66
63
=head1 METHODS
67
=head1 METHODS
64
68
65
=over 2
69
=head2 get_report_areas
66
67
=item get_report_areas()
68
70
69
This will return a list of all the available report areas
71
This will return a list of all the available report areas
70
72
Lines 129-135 if ( C4::Context->preference('item-level_itypes') ) { Link Here
129
    unshift @{ $criteria{'CAT'} }, 'biblioitems.itemtype';
131
    unshift @{ $criteria{'CAT'} }, 'biblioitems.itemtype';
130
}
132
}
131
133
132
=item get_report_types()
134
=head2 get_report_types
133
135
134
This will return a list of all the available report types
136
This will return a list of all the available report types
135
137
Lines 151-157 sub get_report_types { Link Here
151
153
152
}
154
}
153
155
154
=item get_report_groups()
156
=head2 get_report_groups
155
157
156
This will return a list of all the available report areas with groups
158
This will return a list of all the available report areas with groups
157
159
Lines 180-186 sub get_report_groups { Link Here
180
    return \%groups_with_subgroups
182
    return \%groups_with_subgroups
181
}
183
}
182
184
183
=item get_all_tables()
185
=head2 get_all_tables
184
186
185
This will return a list of all tables in the database 
187
This will return a list of all tables in the database 
186
188
Lines 200-206 sub get_all_tables { Link Here
200
202
201
}
203
}
202
204
203
=item get_columns($area)
205
=head2 get_columns($area)
204
206
205
This will return a list of all columns for a report area
207
This will return a list of all columns for a report area
206
208
Lines 244-250 sub _get_columns { Link Here
244
    return (@columns);
246
    return (@columns);
245
}
247
}
246
248
247
=item build_query($columns,$criteria,$orderby,$area)
249
=head2 build_query($columns,$criteria,$orderby,$area)
248
250
249
This will build the sql needed to return the results asked for, 
251
This will build the sql needed to return the results asked for, 
250
$columns is expected to be of the format tablename.columnname.
252
$columns is expected to be of the format tablename.columnname.
Lines 316-322 sub _build_query { Link Here
316
    return ($query);
318
    return ($query);
317
}
319
}
318
320
319
=item get_criteria($area,$cgi);
321
=head2 get_criteria($area,$cgi);
320
322
321
Returns an arraref to hashrefs suitable for using in a tmpl_loop. With the criteria and available values.
323
Returns an arraref to hashrefs suitable for using in a tmpl_loop. With the criteria and available values.
322
324
Lines 388-394 sub get_criteria { Link Here
388
    return ( \@criteria_array );
390
    return ( \@criteria_array );
389
}
391
}
390
392
391
sub nb_rows($) {
393
sub nb_rows {
392
    my $sql = shift or return;
394
    my $sql = shift or return;
393
    my $sth = C4::Context->dbh->prepare($sql);
395
    my $sth = C4::Context->dbh->prepare($sql);
394
    $sth->execute();
396
    $sth->execute();
Lines 396-402 sub nb_rows($) { Link Here
396
    return scalar (@$rows);
398
    return scalar (@$rows);
397
}
399
}
398
400
399
=item execute_query
401
=head2 execute_query
400
402
401
  ($results, $error) = execute_query($sql, $offset, $limit)
403
  ($results, $error) = execute_query($sql, $offset, $limit)
402
404
Lines 422-428 the user in a user-supplied SQL query WILL apply in any case. Link Here
422
#  ~ remove any LIMIT clause
424
#  ~ remove any LIMIT clause
423
#  ~ repace SELECT clause w/ SELECT count(*)
425
#  ~ repace SELECT clause w/ SELECT count(*)
424
426
425
sub select_2_select_count ($) {
427
sub select_2_select_count {
426
    # Modify the query passed in to create a count query... (I think this covers all cases -crn)
428
    # Modify the query passed in to create a count query... (I think this covers all cases -crn)
427
    my ($sql) = strip_limit(shift) or return;
429
    my ($sql) = strip_limit(shift) or return;
428
    $sql =~ s/\bSELECT\W+(?:\w+\W+){1,}?FROM\b|\bSELECT\W\*\WFROM\b/SELECT count(*) FROM /ig;
430
    $sql =~ s/\bSELECT\W+(?:\w+\W+){1,}?FROM\b|\bSELECT\W\*\WFROM\b/SELECT count(*) FROM /ig;
Lines 512-518 sub execute_query ($;$$$) { Link Here
512
    # store_results($id,$xml);
514
    # store_results($id,$xml);
513
}
515
}
514
516
515
=item save_report($sql,$name,$type,$notes)
517
=head2 save_report($sql,$name,$type,$notes)
516
518
517
Given some sql and a name this will saved it so that it can reused
519
Given some sql and a name this will saved it so that it can reused
518
Returns id of the newly created report
520
Returns id of the newly created report
Lines 700-706 sub get_saved_report { Link Here
700
    return $dbh->selectrow_hashref($query, undef, $report_arg);
702
    return $dbh->selectrow_hashref($query, undef, $report_arg);
701
}
703
}
702
704
703
=item create_compound($masterID,$subreportID)
705
=head2 create_compound($masterID,$subreportID)
704
706
705
This will take 2 reports and create a compound report using both of them
707
This will take 2 reports and create a compound report using both of them
706
708
Lines 730-736 sub create_compound { Link Here
730
    return ( $mastertables, $subtables );
732
    return ( $mastertables, $subtables );
731
}
733
}
732
734
733
=item get_column_type($column)
735
=head2 get_column_type($column)
734
736
735
This takes a column name of the format table.column and will return what type it is
737
This takes a column name of the format table.column and will return what type it is
736
(free text, set values, date)
738
(free text, set values, date)
Lines 758-764 sub get_column_type { Link Here
758
	}
760
	}
759
}
761
}
760
762
761
=item get_distinct_values($column)
763
=head2 get_distinct_values($column)
762
764
763
Given a column name, return an arrary ref of hashrefs suitable for use as a tmpl_loop 
765
Given a column name, return an arrary ref of hashrefs suitable for use as a tmpl_loop 
764
with the distinct values of the column
766
with the distinct values of the column
Lines 852-858 sub _get_column_defs { Link Here
852
	return \%columns;
854
	return \%columns;
853
}
855
}
854
856
855
=item build_authorised_value_list($authorised_value)
857
=head2 build_authorised_value_list($authorised_value)
856
858
857
Returns an arrayref - hashref pair. The hashref consists of
859
Returns an arrayref - hashref pair. The hashref consists of
858
various code => name lists depending on the $authorised_value.
860
various code => name lists depending on the $authorised_value.
Lines 917-927 sub build_authorised_value_list { Link Here
917
    return (\@authorised_values, \%authorised_lib);
919
    return (\@authorised_values, \%authorised_lib);
918
}
920
}
919
921
922
=head2 GetReservedAuthorisedValues
923
924
    my %reserved_authorised_values = GetReservedAuthorisedValues();
925
926
Returns a hash containig all reserved words
927
928
=cut
929
930
sub GetReservedAuthorisedValues {
931
    my %reserved_authorised_values =
932
            map { $_ => 1 } ( 'date',
933
                              'branches',
934
                              'itemtypes',
935
                              'cn_source',
936
                              'categorycode' );
937
938
   return \%reserved_authorised_values;
939
}
940
941
942
=head2 IsAuthorisedValueValid
943
944
    my $is_valid_ath_value = IsAuthorisedValueValid($authorised_value)
945
946
Returns 1 if $authorised_value is on the reserved authorised values list or
947
in the authorised value categories defined in
948
949
=cut
950
951
sub IsAuthorisedValueValid {
952
953
    my $authorised_value = shift;
954
    my $reserved_authorised_values = GetReservedAuthorisedValues();
955
956
    if ( exists $reserved_authorised_values->{$authorised_value} ||
957
         IsAuthorisedValueCategory($authorised_value)   ) {
958
        return 1;
959
    }
960
961
    return 0;
962
}
963
964
=head2 GetParametersFromSQL
965
966
    my @sql_parameters = GetParametersFromSQL($sql)
967
968
Returns an arrayref of hashes containing the keys name and authval
969
970
=cut
971
972
sub GetParametersFromSQL {
973
974
    my $sql = shift ;
975
    my @split = split(/<<|>>/,$sql);
976
    my @sql_parameters = ();
977
978
    for ( my $i = 0; $i < ($#split/2) ; $i++ ) {
979
        my ($name,$authval) = split(/\|/,$split[$i*2+1]);
980
        push @sql_parameters, { 'name' => $name, 'authval' => $authval };
981
    }
982
983
    return \@sql_parameters;
984
}
985
986
=head2 ValidateSQLParameters
987
988
    my @problematic_parameters = ValidateSQLParameters($sql)
989
990
Returns an arrayref of hashes containing the keys name and authval of
991
those SQL parameters that do not correspond to valid authorised names
992
993
=cut
994
995
sub ValidateSQLParameters {
996
997
    my $sql = shift;
998
    my @problematic_parameters = ();
999
    my $sql_parameters = GetParametersFromSQL($sql);
1000
1001
    foreach my $sql_parameter (@$sql_parameters) {
1002
        if ( defined $sql_parameter->{'authval'} ) {
1003
            push @problematic_parameters, $sql_parameter unless
1004
                IsAuthorisedValueValid($sql_parameter->{'authval'});
1005
        }
1006
    }
1007
1008
    return \@problematic_parameters;
1009
}
1010
920
1;
1011
1;
921
__END__
1012
__END__
922
1013
923
=back
924
925
=head1 AUTHOR
1014
=head1 AUTHOR
926
1015
927
Chris Cormack <crc@liblime.com>
1016
Chris Cormack <crc@liblime.com>
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-11 / +76 lines)
Lines 654-681 canned reports and writing custom SQL reports.</p> Link Here
654
</form>
654
</form>
655
[% END %]
655
[% END %]
656
656
657
[% IF ( warn_authval_problem ) %]
658
     <form action='/cgi-bin/koha/reports/guided_reports.pl'>
659
        <!--Every parameter the user issued is provided as a hidden field for recovery-->
660
        <input type='hidden' name='id' value='[% id %]' />
661
        <input type='hidden' name='sql' value='[% sql %]' />
662
        <input type='hidden' name='reportname' value='[% reportname %]' />
663
        <input type='hidden' name='group' value='[% group %]' />
664
        <input type='hidden' name='subgroup' value='[% subgroup %]' />
665
        <input type='hidden' name='notes' value='[% notes %]' />
666
        <input type='hidden' name='cache_expiry' value='[% cache_expiry %]' />
667
        <input type='hidden' name='cache_expiry_units' value='[% cache_expiry_units %]' />
668
        <input type='hidden' name='public' value='[% public %]' />
669
670
        <div class="dialog alert">
671
            <h3>Errors found when processing parameters for report: [% name %]</h3>
672
            [% FOREACH problematic_authval IN problematic_authvals %]
673
                <p>
674
                <strong>[% problematic_authval.name %]:</strong> The authorised value category (<strong>[% problematic_authval.authval %]</strong>)
675
                    you selected does not exist.
676
                </p>
677
            [% END %]
678
            <!-- Save Anyway Form -->
679
            <form action='/cgi-bin/koha/reports/guided_reports.pl'>
680
            <!--Every parameter the user issued is provided as a hidden field for recovery-->
681
                <input type='hidden' name='id' value='[% id %]' />
682
                <input type='hidden' name='sql' value='[% sql %]' />
683
                <input type='hidden' name='reportname' value='[% reportname %]' />
684
                <input type='hidden' name='group' value='[% group %]' />
685
                <input type='hidden' name='subgroup' value='[% subgroup %]' />
686
                <input type='hidden' name='notes' value='[% notes %]' />
687
                <input type='hidden' name='cache_expiry' value='[% cache_expiry %]' />
688
                <input type='hidden' name='cache_expiry_units' value='[% cache_expiry_units %]' />
689
                <input type='hidden' name='public' value='[% public %]' />
690
            [% IF ( phase_update) %]
691
                <input type='hidden' name='phase' value='Update SQL' />
692
                <input type="submit" name="save_anyway" class="approve" value="Save anyway" />
693
            [% ELSIF ( phase_save) %]
694
                <input type='hidden' name='area' value='[% area %]' />
695
                <input type='hidden' name='phase' value='Save Report' />
696
                <input type="submit" name="save_anyway" class="approve" value="Save anyway" />
697
            [% END %]
698
            </form>
699
            <!-- Go back to editing -->
700
            <form action='/cgi-bin/koha/reports/guided_reports.pl'>
701
                <input type="button" name='back' class="deny" value="Edit SQL"
702
                                    onclick="javascript:history.back()" />
703
            </form>
704
        </div>
705
    </form>
706
[% END %]
707
657
[% IF ( enter_params ) %]
708
[% IF ( enter_params ) %]
658
    <form action='/cgi-bin/koha/reports/guided_reports.pl'>
709
    <form action='/cgi-bin/koha/reports/guided_reports.pl'>
659
        <input type='hidden' name='phase' value='Run this report' />
660
        <input type='hidden' name='reports' value="[% reports %]" />
710
        <input type='hidden' name='reports' value="[% reports %]" />
711
    [% IF ( auth_val_error ) %]
712
        <input type='hidden' name='phase' value='Edit SQL' />
713
        <div class="dialog alert">
714
            <h3>Errors found when processing parameters for report: [% name %]</h3>
715
            [% FOREACH auth_val_error IN auth_val_errors %]
716
                <p>
717
                    <strong>[% auth_val_error.entry %]:</strong> The authorised value category (<strong>[% auth_val_error.auth_val %]</strong>)
718
                    you selected does not exist.
719
                </p>
720
            [% END %]
721
        </div>
722
        <fieldset class="action"><input type="submit" value="Edit SQL" /></fieldset>
723
    [% ELSE %]
724
        <input type='hidden' name='phase' value='Run this report' />
661
        <h1>Enter parameters for report [% name %]:</h1>
725
        <h1>Enter parameters for report [% name %]:</h1>
662
        [% IF ( notes ) %]<p>[% notes %]</p>[% END %]
726
        [% IF ( notes ) %]<p>[% notes %]</p>[% END %]
663
        <fieldset class="rows">
727
        <fieldset class="rows">
664
            <ol>
728
            <ol>
665
        [% FOREACH sql_param IN sql_params %]
729
            [% FOREACH sql_param IN sql_params %]
666
            [% IF sql_param.input == 'date' %]
730
                [% IF sql_param.input == 'date' %]
667
                <li>
731
                    <li>
668
                <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" />
732
                    <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" />
669
                </li>
733
                    </li>
670
            [% ELSIF ( sql_param.input == 'text' ) %]
734
                [% ELSIF ( sql_param.input == 'text' ) %]
671
                <li><label for="sql_params[% loop.count %]">[% sql_param.entry %]: </label><input id="sql_params[% loop.count %]" type="text" name="sql_params" /></li>
735
                    <li><label for="sql_params[% loop.count %]">[% sql_param.entry %]: </label><input id="sql_params[% loop.count %]" type="text" name="sql_params" /></li>
672
            [% ELSE %]
736
                [% ELSE %]
673
                <li><label for="sql_params_[% sql_param.labelid %]">[% sql_param.entry %]:</label> [% sql_param.input %]</li>
737
                    <li><label for="sql_params_[% sql_param.labelid %]">[% sql_param.entry %]:</label> [% sql_param.input %]</li>
738
                [% END %]
674
            [% END %]
739
            [% END %]
675
        [% END %]
676
            </ol>
740
            </ol>
677
        </fieldset>
741
        </fieldset>
678
        <fieldset class="action"><input type="submit" value="Run the report" /></fieldset>
742
        <fieldset class="action"><input type="submit" value="Run the report" /></fieldset>
743
    [% END %]
679
    </form>
744
    </form>
680
[% END %]
745
[% END %]
681
746
(-)a/reports/guided_reports.pl (-50 / +121 lines)
Lines 27-32 use C4::Output; Link Here
27
use C4::Dates qw/format_date/;
27
use C4::Dates qw/format_date/;
28
use C4::Debug;
28
use C4::Debug;
29
use C4::Branch; # XXX subfield_is_koha_internal_p
29
use C4::Branch; # XXX subfield_is_koha_internal_p
30
use C4::Koha qw/IsAuthorisedValueCategory/;
30
31
31
=head1 NAME
32
=head1 NAME
32
33
Lines 131-137 elsif ( $phase eq 'Show SQL'){ Link Here
131
}
132
}
132
133
133
elsif ( $phase eq 'Edit SQL'){
134
elsif ( $phase eq 'Edit SQL'){
134
	
135
    my $id = $input->param('reports');
135
    my $id = $input->param('reports');
136
    my $report = get_saved_report($id);
136
    my $report = get_saved_report($id);
137
    my $group = $report->{report_group};
137
    my $group = $report->{report_group};
Lines 159-164 elsif ( $phase eq 'Update SQL'){ Link Here
159
    my $cache_expiry = $input->param('cache_expiry');
159
    my $cache_expiry = $input->param('cache_expiry');
160
    my $cache_expiry_units = $input->param('cache_expiry_units');
160
    my $cache_expiry_units = $input->param('cache_expiry_units');
161
    my $public = $input->param('public');
161
    my $public = $input->param('public');
162
    my $save_anyway = $input->param('save_anyway');
162
163
163
    my @errors;
164
    my @errors;
164
165
Lines 185-210 elsif ( $phase eq 'Update SQL'){ Link Here
185
    elsif ($sql !~ /^(SELECT)/i) {
186
    elsif ($sql !~ /^(SELECT)/i) {
186
        push @errors, {queryerr => 1};
187
        push @errors, {queryerr => 1};
187
    }
188
    }
189
188
    if (@errors) {
190
    if (@errors) {
189
        $template->param(
191
        $template->param(
190
            'errors'    => \@errors,
192
            'errors'    => \@errors,
191
            'sql'       => $sql,
193
            'sql'       => $sql,
192
        );
194
        );
193
    } else {
195
    } else {
194
        update_sql( $id, {
196
195
                sql => $sql,
197
        # Check defined SQL parameters for authorised value validity
196
                name => $reportname,
198
        my $problematic_authvals = ValidateSQLParameters($sql);
197
                group => $group,
199
198
                subgroup => $subgroup,
200
        if ( scalar @$problematic_authvals > 0 && not $save_anyway ) {
199
                notes => $notes,
201
            # There's at least one problematic parameter, report to the
200
                cache_expiry => $cache_expiry,
202
            # GUI and provide all user input for further actions
201
                public => $public,
203
            $template->param(
202
        } );
204
                'id' => $id,
203
        $template->param(
205
                'sql' => $sql,
204
            'save_successful'       => 1,
206
                'reportname' => $reportname,
205
            'reportname'            => $reportname,
207
                'group' => $group,
206
            'id'                    => $id,
208
                'subgroup' => $subgroup,
207
        );
209
                'notes' => $notes,
210
                'cache_expiry' => $cache_expiry,
211
                'cache_expiry_units' => $cache_expiry_units,
212
                'public' => $public,
213
                'problematic_authvals' => $problematic_authvals,
214
                'warn_authval_problem' => 1,
215
                'phase_update' => 1
216
            );
217
218
        } else {
219
            # No params problem found or asked to save anyway
220
            update_sql( $id, {
221
                    sql => $sql,
222
                    name => $reportname,
223
                    group => $group,
224
                    subgroup => $subgroup,
225
                    notes => $notes,
226
                    cache_expiry => $cache_expiry,
227
                    public => $public,
228
                } );
229
            $template->param(
230
                'save_successful'       => 1,
231
                'reportname'            => $reportname,
232
                'id'                    => $id,
233
            );
234
        }
208
    }
235
    }
209
}
236
}
210
237
Lines 467-472 elsif ( $phase eq 'Save Report' ) { Link Here
467
    my $cache_expiry = $input->param('cache_expiry');
494
    my $cache_expiry = $input->param('cache_expiry');
468
    my $cache_expiry_units = $input->param('cache_expiry_units');
495
    my $cache_expiry_units = $input->param('cache_expiry_units');
469
    my $public = $input->param('public');
496
    my $public = $input->param('public');
497
    my $save_anyway = $input->param('save_anyway');
470
498
471
499
472
    # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
500
    # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
Lines 493-498 elsif ( $phase eq 'Save Report' ) { Link Here
493
    elsif ($sql !~ /^(SELECT)/i) {
521
    elsif ($sql !~ /^(SELECT)/i) {
494
        push @errors, {queryerr => "No SELECT"};
522
        push @errors, {queryerr => "No SELECT"};
495
    }
523
    }
524
496
    if (@errors) {
525
    if (@errors) {
497
        $template->param(
526
        $template->param(
498
            'errors'    => \@errors,
527
            'errors'    => \@errors,
Lines 503-527 elsif ( $phase eq 'Save Report' ) { Link Here
503
            'cache_expiry' => $cache_expiry,
532
            'cache_expiry' => $cache_expiry,
504
            'public'    => $public,
533
            'public'    => $public,
505
        );
534
        );
506
    }
535
    } else {
507
    else {
536
        # Check defined SQL parameters for authorised value validity
508
        my $id = save_report( {
537
        my $problematic_authvals = ValidateSQLParameters($sql);
509
                borrowernumber => $borrowernumber,
538
510
                sql            => $sql,
539
        if ( scalar @$problematic_authvals > 0 && not $save_anyway ) {
511
                name           => $name,
540
            # There's at least one problematic parameter, report to the
512
                area           => $area,
541
            # GUI and provide all user input for further actions
513
                group          => $group,
542
            $template->param(
514
                subgroup       => $subgroup,
543
                'area' => $area,
515
                type           => $type,
544
                'group' =>  $group,
516
                notes          => $notes,
545
                'subgroup' => $subgroup,
517
                cache_expiry   => $cache_expiry,
546
                'sql' => $sql,
518
                public         => $public,
547
                'reportname' => $name,
519
            } );
548
                'type' => $type,
520
        $template->param(
549
                'notes' => $notes,
521
            'save_successful' => 1,
550
                'cache_expiry' => $cache_expiry,
522
            'reportname'      => $name,
551
                'cache_expiry_units' => $cache_expiry_units,
523
            'id'              => $id,
552
                'public' => $public,
524
        );
553
                'problematic_authvals' => $problematic_authvals,
554
                'warn_authval_problem' => 1,
555
                'phase_save' => 1
556
            );
557
        } else {
558
            # No params problem found or asked to save anyway
559
            my $id = save_report( {
560
                    borrowernumber => $borrowernumber,
561
                    sql            => $sql,
562
                    name           => $name,
563
                    area           => $area,
564
                    group          => $group,
565
                    subgroup       => $subgroup,
566
                    type           => $type,
567
                    notes          => $notes,
568
                    cache_expiry   => $cache_expiry,
569
                    public         => $public,
570
                } );
571
            $template->param(
572
                'save_successful' => 1,
573
                'reportname'      => $name,
574
                'id'              => $id,
575
            );
576
        }
525
    }
577
    }
526
}
578
}
527
579
Lines 553-566 elsif ($phase eq 'Run this report'){ Link Here
553
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
605
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
554
            my @split = split /<<|>>/,$sql;
606
            my @split = split /<<|>>/,$sql;
555
            my @tmpl_parameters;
607
            my @tmpl_parameters;
608
            my @authval_errors;
556
            for(my $i=0;$i<($#split/2);$i++) {
609
            for(my $i=0;$i<($#split/2);$i++) {
557
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
610
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
558
                my $input;
611
                my $input;
559
                my $labelid;
612
                my $labelid;
560
                if ($authorised_value eq "date") {
613
                if ( not defined $authorised_value ) {
561
                   $input = 'date';
614
                    # no authorised value input, provide a text box
562
                }
615
                    $input = "text";
563
                elsif ($authorised_value) {
616
                } elsif ( $authorised_value eq "date" ) {
617
                    # require a date, provide a date picker
618
                    $input = 'date';
619
                } else {
620
                    # defined $authorised_value, and not 'date'
564
                    my $dbh=C4::Context->dbh;
621
                    my $dbh=C4::Context->dbh;
565
                    my @authorised_values;
622
                    my @authorised_values;
566
                    my %authorised_lib;
623
                    my %authorised_lib;
Lines 601-615 elsif ($phase eq 'Run this report'){ Link Here
601
                        #---- "true" authorised value
658
                        #---- "true" authorised value
602
                    }
659
                    }
603
                    else {
660
                    else {
604
                        my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");
661
                        if ( IsAuthorisedValueCategory($authorised_value) ) {
605
662
                            my $query = '
606
                        $authorised_values_sth->execute( $authorised_value);
663
                            SELECT authorised_value,lib
607
664
                            FROM authorised_values
608
                        while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
665
                            WHERE category=?
609
                            push @authorised_values, $value;
666
                            ORDER BY lib
610
                            $authorised_lib{$value} = $lib;
667
                            ';
611
                            # For item location, we show the code and the libelle
668
                            my $authorised_values_sth = $dbh->prepare($query);
612
                            $authorised_lib{$value} = $lib;
669
                            $authorised_values_sth->execute( $authorised_value);
670
671
                            while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
672
                                push @authorised_values, $value;
673
                                $authorised_lib{$value} = $lib;
674
                                # For item location, we show the code and the libelle
675
                                $authorised_lib{$value} = $lib;
676
                            }
677
                        } else {
678
                            # not exists $authorised_value_categories{$authorised_value})
679
                            push @authval_errors, {'entry' => $text,
680
                                                   'auth_val' => $authorised_value };
681
                            # tell the template there's an error
682
                            $template->param( auth_val_error => 1 );
683
                            # skip scrolling list creation and params push
684
                            next;
613
                        }
685
                        }
614
                    }
686
                    }
615
                    $labelid = $text;
687
                    $labelid = $text;
Lines 625-638 elsif ($phase eq 'Run this report'){ Link Here
625
                        -multiple => 0,
697
                        -multiple => 0,
626
                        -tabindex => 1,
698
                        -tabindex => 1,
627
                    );
699
                    );
628
                } else {
629
                    $input = "text";
630
                }
700
                }
701
631
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
702
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
632
            }
703
            }
633
            $template->param('sql'         => $sql,
704
            $template->param('sql'         => $sql,
634
                            'name'         => $name,
705
                            'name'         => $name,
635
                            'sql_params'   => \@tmpl_parameters,
706
                            'sql_params'   => \@tmpl_parameters,
707
                            'auth_val_errors'  => \@authval_errors,
636
                            'enter_params' => 1,
708
                            'enter_params' => 1,
637
                            'reports'      => $report_id,
709
                            'reports'      => $report_id,
638
                            );
710
                            );
639
- 

Return to bug 9659