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 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
    my $query = '
1119
        SELECT category
1120
        FROM authorised_values
1121
        WHERE BINARY category=?
1122
        LIMIT 1
1123
    ';
1124
    my $sth = C4::Context->dbh->prepare($query);
1125
    $sth->execute($category);
1126
    $sth->fetchrow ? return 1
1127
                   : return 0;
1128
}
1129
1107
=head2 GetAuthorisedValueByCode
1130
=head2 GetAuthorisedValueByCode
1108
1131
1109
$authhorised_value = GetAuthorisedValueByCode( $category, $authvalcode );
1132
$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 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 / +121 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/;
32
33
33
=head1 NAME
34
=head1 NAME
34
35
Lines 132-138 elsif ( $phase eq 'Show SQL'){ Link Here
132
}
133
}
133
134
134
elsif ( $phase eq 'Edit SQL'){
135
elsif ( $phase eq 'Edit SQL'){
135
	
136
    my $id = $input->param('reports');
136
    my $id = $input->param('reports');
137
    my $report = get_saved_report($id);
137
    my $report = get_saved_report($id);
138
    my $group = $report->{report_group};
138
    my $group = $report->{report_group};
Lines 160-165 elsif ( $phase eq 'Update SQL'){ Link Here
160
    my $cache_expiry = $input->param('cache_expiry');
160
    my $cache_expiry = $input->param('cache_expiry');
161
    my $cache_expiry_units = $input->param('cache_expiry_units');
161
    my $cache_expiry_units = $input->param('cache_expiry_units');
162
    my $public = $input->param('public');
162
    my $public = $input->param('public');
163
    my $save_anyway = $input->param('save_anyway');
163
164
164
    my @errors;
165
    my @errors;
165
166
Lines 184-209 elsif ( $phase eq 'Update SQL'){ Link Here
184
    elsif ($sql !~ /^(SELECT)/i) {
185
    elsif ($sql !~ /^(SELECT)/i) {
185
        push @errors, {queryerr => 1};
186
        push @errors, {queryerr => 1};
186
    }
187
    }
188
187
    if (@errors) {
189
    if (@errors) {
188
        $template->param(
190
        $template->param(
189
            'errors'    => \@errors,
191
            'errors'    => \@errors,
190
            'sql'       => $sql,
192
            'sql'       => $sql,
191
        );
193
        );
192
    } else {
194
    } else {
193
        update_sql( $id, {
195
194
                sql => $sql,
196
        # Check defined SQL parameters for authorised value validity
195
                name => $reportname,
197
        my $problematic_authvals = ValidateSQLParameters($sql);
196
                group => $group,
198
197
                subgroup => $subgroup,
199
        if ( scalar @$problematic_authvals > 0 && not $save_anyway ) {
198
                notes => $notes,
200
            # There's at least one problematic parameter, report to the
199
                cache_expiry => $cache_expiry,
201
            # GUI and provide all user input for further actions
200
                public => $public,
202
            $template->param(
201
        } );
203
                'id' => $id,
202
        $template->param(
204
                'sql' => $sql,
203
            'save_successful'       => 1,
205
                'reportname' => $reportname,
204
            'reportname'            => $reportname,
206
                'group' => $group,
205
            'id'                    => $id,
207
                'subgroup' => $subgroup,
206
        );
208
                'notes' => $notes,
209
                'cache_expiry' => $cache_expiry,
210
                'cache_expiry_units' => $cache_expiry_units,
211
                'public' => $public,
212
                'problematic_authvals' => $problematic_authvals,
213
                'warn_authval_problem' => 1,
214
                'phase_update' => 1
215
            );
216
217
        } else {
218
            # No params problem found or asked to save anyway
219
            update_sql( $id, {
220
                    sql => $sql,
221
                    name => $reportname,
222
                    group => $group,
223
                    subgroup => $subgroup,
224
                    notes => $notes,
225
                    cache_expiry => $cache_expiry,
226
                    public => $public,
227
                } );
228
            $template->param(
229
                'save_successful'       => 1,
230
                'reportname'            => $reportname,
231
                'id'                    => $id,
232
            );
233
        }
207
    }
234
    }
208
}
235
}
209
236
Lines 466-471 elsif ( $phase eq 'Save Report' ) { Link Here
466
    my $cache_expiry = $input->param('cache_expiry');
493
    my $cache_expiry = $input->param('cache_expiry');
467
    my $cache_expiry_units = $input->param('cache_expiry_units');
494
    my $cache_expiry_units = $input->param('cache_expiry_units');
468
    my $public = $input->param('public');
495
    my $public = $input->param('public');
496
    my $save_anyway = $input->param('save_anyway');
469
497
470
498
471
    # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
499
    # 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) {
517
    elsif ($sql !~ /^(SELECT)/i) {
490
        push @errors, {queryerr => "No SELECT"};
518
        push @errors, {queryerr => "No SELECT"};
491
    }
519
    }
520
492
    if (@errors) {
521
    if (@errors) {
493
        $template->param(
522
        $template->param(
494
            'errors'    => \@errors,
523
            'errors'    => \@errors,
Lines 499-523 elsif ( $phase eq 'Save Report' ) { Link Here
499
            'cache_expiry' => $cache_expiry,
528
            'cache_expiry' => $cache_expiry,
500
            'public'    => $public,
529
            'public'    => $public,
501
        );
530
        );
502
    }
531
    } else {
503
    else {
532
        # Check defined SQL parameters for authorised value validity
504
        my $id = save_report( {
533
        my $problematic_authvals = ValidateSQLParameters($sql);
505
                borrowernumber => $borrowernumber,
534
506
                sql            => $sql,
535
        if ( scalar @$problematic_authvals > 0 && not $save_anyway ) {
507
                name           => $name,
536
            # There's at least one problematic parameter, report to the
508
                area           => $area,
537
            # GUI and provide all user input for further actions
509
                group          => $group,
538
            $template->param(
510
                subgroup       => $subgroup,
539
                'area' => $area,
511
                type           => $type,
540
                'group' =>  $group,
512
                notes          => $notes,
541
                'subgroup' => $subgroup,
513
                cache_expiry   => $cache_expiry,
542
                'sql' => $sql,
514
                public         => $public,
543
                'reportname' => $name,
515
            } );
544
                'type' => $type,
516
        $template->param(
545
                'notes' => $notes,
517
            'save_successful' => 1,
546
                'cache_expiry' => $cache_expiry,
518
            'reportname'      => $name,
547
                'cache_expiry_units' => $cache_expiry_units,
519
            'id'              => $id,
548
                'public' => $public,
520
        );
549
                'problematic_authvals' => $problematic_authvals,
550
                'warn_authval_problem' => 1,
551
                'phase_save' => 1
552
            );
553
        } else {
554
            # No params problem found or asked to save anyway
555
            my $id = save_report( {
556
                    borrowernumber => $borrowernumber,
557
                    sql            => $sql,
558
                    name           => $name,
559
                    area           => $area,
560
                    group          => $group,
561
                    subgroup       => $subgroup,
562
                    type           => $type,
563
                    notes          => $notes,
564
                    cache_expiry   => $cache_expiry,
565
                    public         => $public,
566
                } );
567
            $template->param(
568
                'save_successful' => 1,
569
                'reportname'      => $name,
570
                'id'              => $id,
571
            );
572
        }
521
    }
573
    }
522
}
574
}
523
575
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
601
            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
550
            my @split = split /<<|>>/,$sql;
602
            my @split = split /<<|>>/,$sql;
551
            my @tmpl_parameters;
603
            my @tmpl_parameters;
604
            my @authval_errors;
552
            for(my $i=0;$i<($#split/2);$i++) {
605
            for(my $i=0;$i<($#split/2);$i++) {
553
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
606
                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
554
                my $input;
607
                my $input;
555
                my $labelid;
608
                my $labelid;
556
                if ($authorised_value eq "date") {
609
                if ( not defined $authorised_value ) {
557
                   $input = 'date';
610
                    # no authorised value input, provide a text box
558
                }
611
                    $input = "text";
559
                elsif ($authorised_value) {
612
                } elsif ( $authorised_value eq "date" ) {
613
                    # require a date, provide a date picker
614
                    $input = 'date';
615
                } else {
616
                    # defined $authorised_value, and not 'date'
560
                    my $dbh=C4::Context->dbh;
617
                    my $dbh=C4::Context->dbh;
561
                    my @authorised_values;
618
                    my @authorised_values;
562
                    my %authorised_lib;
619
                    my %authorised_lib;
Lines 597-611 elsif ($phase eq 'Run this report'){ Link Here
597
                        #---- "true" authorised value
654
                        #---- "true" authorised value
598
                    }
655
                    }
599
                    else {
656
                    else {
600
                        my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");
657
                        if ( IsAuthorisedValueCategory($authorised_value) ) {
601
658
                            my $query = '
602
                        $authorised_values_sth->execute( $authorised_value);
659
                            SELECT authorised_value,lib
603
660
                            FROM authorised_values
604
                        while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
661
                            WHERE category=?
605
                            push @authorised_values, $value;
662
                            ORDER BY lib
606
                            $authorised_lib{$value} = $lib;
663
                            ';
607
                            # For item location, we show the code and the libelle
664
                            my $authorised_values_sth = $dbh->prepare($query);
608
                            $authorised_lib{$value} = $lib;
665
                            $authorised_values_sth->execute( $authorised_value);
666
667
                            while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
668
                                push @authorised_values, $value;
669
                                $authorised_lib{$value} = $lib;
670
                                # For item location, we show the code and the libelle
671
                                $authorised_lib{$value} = $lib;
672
                            }
673
                        } else {
674
                            # not exists $authorised_value_categories{$authorised_value})
675
                            push @authval_errors, {'entry' => $text,
676
                                                   'auth_val' => $authorised_value };
677
                            # tell the template there's an error
678
                            $template->param( auth_val_error => 1 );
679
                            # skip scrolling list creation and params push
680
                            next;
609
                        }
681
                        }
610
                    }
682
                    }
611
                    $labelid = $text;
683
                    $labelid = $text;
Lines 621-634 elsif ($phase eq 'Run this report'){ Link Here
621
                        -multiple => 0,
693
                        -multiple => 0,
622
                        -tabindex => 1,
694
                        -tabindex => 1,
623
                    );
695
                    );
624
                } else {
625
                    $input = "text";
626
                }
696
                }
697
627
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
698
                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
628
            }
699
            }
629
            $template->param('sql'         => $sql,
700
            $template->param('sql'         => $sql,
630
                            'name'         => $name,
701
                            'name'         => $name,
631
                            'sql_params'   => \@tmpl_parameters,
702
                            'sql_params'   => \@tmpl_parameters,
703
                            'auth_val_errors'  => \@authval_errors,
632
                            'enter_params' => 1,
704
                            'enter_params' => 1,
633
                            'reports'      => $report_id,
705
                            'reports'      => $report_id,
634
                            );
706
                            );
635
- 

Return to bug 9659