X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=reports%2Fguided_reports.pl;h=b9c49ad023548622d2c953ef4bafcea5ff92ca89;hb=08382876306cfda839637c5f72a107b304458a8e;hp=cb85f39420d9aafc0c3e158ed7c6d58d9dff252d;hpb=6eb021ab0e113e2b6b897c89475f1eadc7a2c15b;p=koha.git

diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index cb85f39420..b9c49ad023 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -17,17 +17,17 @@
 # with Koha; if not, write to the Free Software Foundation, Inc.,
 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
 
-use strict;
-#use warnings; FIXME - Bug 2505
-use CGI;
+
+use CGI qw/-utf8/;
 use Text::CSV;
 use URI::Escape;
 use C4::Reports::Guided;
 use C4::Auth qw/:DEFAULT get_session/;
 use C4::Output;
-use C4::Dates;
+use C4::Dates qw/format_date/;
 use C4::Debug;
 use C4::Branch; # XXX subfield_is_koha_internal_p
+use C4::Koha qw/IsAuthorisedValueCategory/;
 
 =head1 NAME
 
@@ -40,6 +40,7 @@ Script to control the guided report creation
 =cut
 
 my $input = new CGI;
+my $usecache = C4::Context->ismemcached;
 
 my $phase = $input->param('phase');
 my $flagsrequired;
@@ -67,7 +68,7 @@ my $session = $cookie ? get_session($cookie->value) : undef;
 my $filter;
 if ( $input->param("filter_set") ) {
     $filter = {};
-    $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword/;
+    $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword group subgroup/;
     $session->param('report_filter', $filter) if $session;
     $template->param( 'filter_set' => 1 );
 }
@@ -84,54 +85,73 @@ if ( !$phase ) {
 elsif ( $phase eq 'Build new' ) {
     # build a new report
     $template->param( 'build1' => 1 );
-    $template->param( 'areas' => get_report_areas() );
-}
-elsif ( $phase eq 'Use saved' ) {
+    my $areas = get_report_areas();
+    $template->param(
+        'areas' => [map { id => $_->[0], name => $_->[1] }, @$areas],
+        'usecache' => $usecache,
+        'cache_expiry' => 300,
+        'public' => '0',
+    );
+} elsif ( $phase eq 'Use saved' ) {
+
     # use a saved report
     # get list of reports and display them
+    my $group = $input->param('group');
+    my $subgroup = $input->param('subgroup');
+    $filter->{group} = $group;
+    $filter->{subgroup} = $subgroup;
     $template->param(
         'saved1' => 1,
         'savedreports' => get_saved_reports($filter),
+        'usecache' => $usecache,
+        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
     );
-    if ($filter) {
-        while ( my ($k, $v) = each %$filter ) {
-            $template->param( "filter_$k" => $v ) if $v;
-        }
-    }
+}
+
+elsif ( $phase eq 'Delete Multiple') {
+    my @ids = $input->param('ids');
+    delete_report( @ids );
+    print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved");
+    exit;
 }
 
 elsif ( $phase eq 'Delete Saved') {
 	
 	# delete a report from the saved reports list
-	my $id = $input->param('reports');
-	delete_report($id);
+    my $ids = $input->param('reports');
+    delete_report($ids);
     print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved");
 	exit;
 }		
 
 elsif ( $phase eq 'Show SQL'){
 	
-	my $id = $input->param('reports');
-    my ($sql,$type,$reportname,$notes) = get_saved_report($id);
-	$template->param(
+    my $id = $input->param('reports');
+    my $report = get_saved_report($id);
+    $template->param(
         'id'      => $id,
-        'reportname' => $reportname,
-        'notes'      => $notes,
-		'sql'     => $sql,
-		'showsql' => 1,
+        'reportname' => $report->{report_name},
+        'notes'      => $report->{notes},
+	'sql'     => $report->{savedsql},
+	'showsql' => 1,
     );
 }
 
 elsif ( $phase eq 'Edit SQL'){
-	
     my $id = $input->param('reports');
-    my ($sql,$type,$reportname,$notes) = get_saved_report($id);
+    my $report = get_saved_report($id);
+    my $group = $report->{report_group};
+    my $subgroup  = $report->{report_subgroup};
     $template->param(
-	    'sql'        => $sql,
-	    'reportname' => $reportname,
-        'notes'      => $notes,
+        'sql'        => $report->{savedsql},
+        'reportname' => $report->{report_name},
+        'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
+        'notes'      => $report->{notes},
         'id'         => $id,
-	    'editsql'    => 1,
+        'cache_expiry' => $report->{cache_expiry},
+        'public' => $report->{public},
+        'usecache' => $usecache,
+        'editsql'    => 1,
     );
 }
 
@@ -139,29 +159,86 @@ elsif ( $phase eq 'Update SQL'){
     my $id         = $input->param('id');
     my $sql        = $input->param('sql');
     my $reportname = $input->param('reportname');
+    my $group      = $input->param('group');
+    my $subgroup   = $input->param('subgroup');
     my $notes      = $input->param('notes');
+    my $cache_expiry = $input->param('cache_expiry');
+    my $cache_expiry_units = $input->param('cache_expiry_units');
+    my $public = $input->param('public');
+    my $save_anyway = $input->param('save_anyway');
+
     my @errors;
+
+    # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
+    if( $cache_expiry_units ){
+      if( $cache_expiry_units eq "minutes" ){
+        $cache_expiry *= 60;
+      } elsif( $cache_expiry_units eq "hours" ){
+        $cache_expiry *= 3600; # 60 * 60
+      } elsif( $cache_expiry_units eq "days" ){
+        $cache_expiry *= 86400; # 60 * 60 * 24
+      }
+    }
+    # 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
+    if( $cache_expiry >= 2592000 ){
+      push @errors, {cache_expiry => $cache_expiry};
+    }
+
+    create_non_existing_group_and_subgroup($input, $group, $subgroup);
+
     if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
         push @errors, {sqlerr => $1};
     }
     elsif ($sql !~ /^(SELECT)/i) {
         push @errors, {queryerr => 1};
     }
+
     if (@errors) {
         $template->param(
             'errors'    => \@errors,
             'sql'       => $sql,
         );
+    } else {
+
+        # Check defined SQL parameters for authorised value validity
+        my $problematic_authvals = ValidateSQLParameters($sql);
+
+        if ( scalar @$problematic_authvals > 0 && not $save_anyway ) {
+            # There's at least one problematic parameter, report to the
+            # GUI and provide all user input for further actions
+            $template->param(
+                'id' => $id,
+                'sql' => $sql,
+                'reportname' => $reportname,
+                'group' => $group,
+                'subgroup' => $subgroup,
+                'notes' => $notes,
+                'cache_expiry' => $cache_expiry,
+                'cache_expiry_units' => $cache_expiry_units,
+                'public' => $public,
+                'problematic_authvals' => $problematic_authvals,
+                'warn_authval_problem' => 1,
+                'phase_update' => 1
+            );
+
+        } else {
+            # No params problem found or asked to save anyway
+            update_sql( $id, {
+                    sql => $sql,
+                    name => $reportname,
+                    group => $group,
+                    subgroup => $subgroup,
+                    notes => $notes,
+                    cache_expiry => $cache_expiry,
+                    public => $public,
+                } );
+            $template->param(
+                'save_successful'       => 1,
+                'reportname'            => $reportname,
+                'id'                    => $id,
+            );
+        }
     }
-    else {
-        update_sql( $id, $sql, $reportname, $notes );
-        $template->param(
-            'save_successful'       => 1,
-            'reportname'            => $reportname,
-            'id'                    => $id,
-        );
-    }
-    
 }
 
 elsif ($phase eq 'retrieve results') {
@@ -177,17 +254,41 @@ elsif ($phase eq 'retrieve results') {
 }
 
 elsif ( $phase eq 'Report on this Area' ) {
-
-    # they have choosen a new report and the area to report on
-    $template->param(
-        'build2' => 1,
-        'area'   => $input->param('areas'),
-        'types'  => get_report_types(),
-    );
+    my $cache_expiry_units = $input->param('cache_expiry_units'),
+    my $cache_expiry = $input->param('cache_expiry');
+
+    # we need to handle converting units
+    if( $cache_expiry_units eq "minutes" ){
+      $cache_expiry *= 60;
+    } elsif( $cache_expiry_units eq "hours" ){
+      $cache_expiry *= 3600; # 60 * 60
+    } elsif( $cache_expiry_units eq "days" ){
+      $cache_expiry *= 86400; # 60 * 60 * 24
+    }
+    # 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
+    if( $cache_expiry >= 2592000 ){ # oops, over the limit of 30 days
+      # report error to user
+      $template->param(
+        'cache_error' => 1,
+        'build1' => 1,
+        'areas'   => get_report_areas(),
+        'cache_expiry' => $cache_expiry,
+        'usecache' => $usecache,
+        'public' => $input->param('public'),
+      );
+    } else {
+      # they have choosen a new report and the area to report on
+      $template->param(
+          'build2' => 1,
+          'area'   => $input->param('area'),
+          'types'  => get_report_types(),
+          'cache_expiry' => $cache_expiry,
+          'public' => $input->param('public'),
+      );
+    }
 }
 
 elsif ( $phase eq 'Choose this type' ) {
-
     # they have chosen type and area
     # get area and type and pass them to the template
     my $area = $input->param('area');
@@ -197,11 +298,12 @@ elsif ( $phase eq 'Choose this type' ) {
         'area'   => $area,
         'type'   => $type,
         columns  => get_columns($area,$input),
+        'cache_expiry' => $input->param('cache_expiry'),
+        'public' => $input->param('public'),
     );
 }
 
 elsif ( $phase eq 'Choose these columns' ) {
-
     # we now know type, area, and columns
     # next step is the constraints
     my $area    = $input->param('area');
@@ -215,6 +317,9 @@ elsif ( $phase eq 'Choose these columns' ) {
         'column' => $column,
         definitions => get_from_dictionary($area),
         criteria    => get_criteria($area,$input),
+        'cache_expiry' => $input->param('cache_expiry'),
+        'cache_expiry_units' => $input->param('cache_expiry_units'),
+        'public' => $input->param('public'),
     );
 }
 
@@ -222,44 +327,43 @@ elsif ( $phase eq 'Choose these criteria' ) {
     my $area     = $input->param('area');
     my $type     = $input->param('type');
     my $column   = $input->param('column');
-	my @definitions = $input->param('definition');
-	my $definition = join (',',@definitions);
+    my @definitions = $input->param('definition');
+    my $definition = join (',',@definitions);
     my @criteria = $input->param('criteria_column');
-	my $query_criteria;
+    my $query_criteria;
     foreach my $crit (@criteria) {
         my $value = $input->param( $crit . "_value" );
-	
-	# If value is not defined, then it may be range values
-	if (!defined $value) {
-
-	    my $fromvalue = $input->param( "from_" . $crit . "_value" );
-	    my $tovalue   = $input->param( "to_"   . $crit . "_value" );
-	    
-	    # If the range values are dates
-	    if ($fromvalue =~ C4::Dates->regexp('syspref') && $tovalue =~ C4::Dates->regexp('syspref')) { 
-		$fromvalue = C4::Dates->new($fromvalue)->output("iso");
-		$tovalue = C4::Dates->new($tovalue)->output("iso");
-	    }
-
-	    if ($fromvalue && $tovalue) {
-		$query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'";
-	    }
-
-	} else {
-
-	    # If value is a date
-	    if ($value =~ C4::Dates->regexp('syspref')) { 
-		$value = C4::Dates->new($value)->output("iso");
-	    }
-        # don't escape runtime parameters, they'll be at runtime
-        if ($value =~ /<<.*>>/) {
-            $query_criteria .= " AND $crit=$value";
+
+        # If value is not defined, then it may be range values
+        if (!defined $value) {
+
+            my $fromvalue = $input->param( "from_" . $crit . "_value" );
+            my $tovalue   = $input->param( "to_"   . $crit . "_value" );
+
+            # If the range values are dates
+            if ($fromvalue =~ C4::Dates->regexp('syspref') && $tovalue =~ C4::Dates->regexp('syspref')) {
+                $fromvalue = C4::Dates->new($fromvalue)->output("iso");
+                $tovalue = C4::Dates->new($tovalue)->output("iso");
+            }
+
+            if ($fromvalue && $tovalue) {
+                $query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'";
+            }
+
         } else {
-            $query_criteria .= " AND $crit='$value'";
+
+            # If value is a date
+            if ($value =~ C4::Dates->regexp('syspref')) {
+                $value = C4::Dates->new($value)->output("iso");
+            }
+            # don't escape runtime parameters, they'll be at runtime
+            if ($value =~ /<<.*>>/) {
+                $query_criteria .= " AND $crit=$value";
+            } else {
+                $query_criteria .= " AND $crit='$value'";
+            }
         }
-	}
     }
-
     $template->param(
         'build5'         => 1,
         'area'           => $area,
@@ -267,6 +371,9 @@ elsif ( $phase eq 'Choose these criteria' ) {
         'column'         => $column,
         'definition'     => $definition,
         'criteriastring' => $query_criteria,
+        'cache_expiry' => $input->param('cache_expiry'),
+        'cache_expiry_units' => $input->param('cache_expiry_units'),
+        'public' => $input->param('public'),
     );
 
     # get columns
@@ -286,7 +393,7 @@ elsif ( $phase eq 'Choose these criteria' ) {
     $template->param( 'total_by' => \@total_by );
 }
 
-elsif ( $phase eq 'Choose These Operations' ) {
+elsif ( $phase eq 'Choose these operations' ) {
     my $area     = $input->param('area');
     my $type     = $input->param('type');
     my $column   = $input->param('column');
@@ -307,6 +414,8 @@ elsif ( $phase eq 'Choose These Operations' ) {
         'criteriastring' => $criteria,
         'totals'         => $totals,
         'definition'     => $definition,
+        'cache_expiry' => $input->param('cache_expiry'),
+        'public' => $input->param('public'),
     );
 
     # get columns
@@ -325,7 +434,7 @@ elsif ( $phase eq 'Choose These Operations' ) {
     $template->param( 'order_by' => \@order_by );
 }
 
-elsif ( $phase eq 'Build Report' ) {
+elsif ( $phase eq 'Build report' ) {
 
     # now we have all the info we need and can build the sql
     my $area     = $input->param('area');
@@ -355,34 +464,70 @@ elsif ( $phase eq 'Build Report' ) {
       build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition );
     $template->param(
         'showreport' => 1,
+        'area'       => $area,
         'sql'        => $sql,
-        'type'       => $type
+        'type'       => $type,
+        'cache_expiry' => $input->param('cache_expiry'),
+        'public' => $input->param('public'),
     );
 }
 
 elsif ( $phase eq 'Save' ) {
-	# Save the report that has just been built
+    # Save the report that has just been built
+    my $area           = $input->param('area');
     my $sql  = $input->param('sql');
     my $type = $input->param('type');
     $template->param(
         'save' => 1,
+        'area'  => $area,
         'sql'  => $sql,
-        'type' => $type
+        'type' => $type,
+        'cache_expiry' => $input->param('cache_expiry'),
+        'public' => $input->param('public'),
+        'groups_with_subgroups' => groups_with_subgroups($area), # in case we have a report group that matches area
     );
 }
 
 elsif ( $phase eq 'Save Report' ) {
-    # save the sql pasted in by a user 
-    my $sql  = $input->param('sql');
-    my $name = $input->param('reportname');
-    my $type = $input->param('types');
+    # save the sql pasted in by a user
+    my $area  = $input->param('area');
+    my $group = $input->param('group');
+    my $subgroup = $input->param('subgroup');
+    my $sql   = $input->param('sql');
+    my $name  = $input->param('reportname');
+    my $type  = $input->param('types');
     my $notes = $input->param('notes');
+    my $cache_expiry = $input->param('cache_expiry');
+    my $cache_expiry_units = $input->param('cache_expiry_units');
+    my $public = $input->param('public');
+    my $save_anyway = $input->param('save_anyway');
+
+
+    # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
+    if( $cache_expiry_units ){
+      if( $cache_expiry_units eq "minutes" ){
+        $cache_expiry *= 60;
+      } elsif( $cache_expiry_units eq "hours" ){
+        $cache_expiry *= 3600; # 60 * 60
+      } elsif( $cache_expiry_units eq "days" ){
+        $cache_expiry *= 86400; # 60 * 60 * 24
+      }
+    }
+    # 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
+    if( $cache_expiry && $cache_expiry >= 2592000 ){
+      push @errors, {cache_expiry => $cache_expiry};
+    }
+
+    create_non_existing_group_and_subgroup($input, $group, $subgroup);
+
+    ## FIXME this is AFTER entering a name to save the report under
     if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
         push @errors, {sqlerr => $1};
     }
     elsif ($sql !~ /^(SELECT)/i) {
-        push @errors, {queryerr => 1};
+        push @errors, {queryerr => "No SELECT"};
     }
+
     if (@errors) {
         $template->param(
             'errors'    => \@errors,
@@ -390,160 +535,231 @@ elsif ( $phase eq 'Save Report' ) {
             'reportname'=> $name,
             'type'      => $type,
             'notes'     => $notes,
+            'cache_expiry' => $cache_expiry,
+            'public'    => $public,
         );
-    }
-    else {
-        my $id = save_report( $borrowernumber, $sql, $name, $type, $notes );
-        $template->param(
-            'save_successful'       => 1,
-            'reportname'            => $name,
-            'id'                    => $id,
-        );
+    } else {
+        # Check defined SQL parameters for authorised value validity
+        my $problematic_authvals = ValidateSQLParameters($sql);
+
+        if ( scalar @$problematic_authvals > 0 && not $save_anyway ) {
+            # There's at least one problematic parameter, report to the
+            # GUI and provide all user input for further actions
+            $template->param(
+                'area' => $area,
+                'group' =>  $group,
+                'subgroup' => $subgroup,
+                'sql' => $sql,
+                'reportname' => $name,
+                'type' => $type,
+                'notes' => $notes,
+                'cache_expiry' => $cache_expiry,
+                'cache_expiry_units' => $cache_expiry_units,
+                'public' => $public,
+                'problematic_authvals' => $problematic_authvals,
+                'warn_authval_problem' => 1,
+                'phase_save' => 1
+            );
+        } else {
+            # No params problem found or asked to save anyway
+            my $id = save_report( {
+                    borrowernumber => $borrowernumber,
+                    sql            => $sql,
+                    name           => $name,
+                    area           => $area,
+                    group          => $group,
+                    subgroup       => $subgroup,
+                    type           => $type,
+                    notes          => $notes,
+                    cache_expiry   => $cache_expiry,
+                    public         => $public,
+                } );
+            $template->param(
+                'save_successful' => 1,
+                'reportname'      => $name,
+                'id'              => $id,
+            );
+        }
     }
 }
 
 elsif ($phase eq 'Run this report'){
     # execute a saved report
-    my $limit  = 20;    # page size. # TODO: move to DB or syspref?
-    my $offset = 0;
-    my $report = $input->param('reports');
+    my $limit      = $input->param('limit') || 20;
+    my $offset     = 0;
+    my $report_id  = $input->param('reports');
     my @sql_params = $input->param('sql_params');
     # offset algorithm
     if ($input->param('page')) {
         $offset = ($input->param('page') - 1) * $limit;
     }
-    my ($sql,$type,$name,$notes) = get_saved_report($report);
-    unless ($sql) {
-        push @errors, {no_sql_for_id=>$report};   
-    } 
-    my @rows = ();
-    # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
-    if ($sql =~ /<</ && !@sql_params) {
-        # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
-        my @split = split /<<|>>/,$sql;
-        my @tmpl_parameters;
-        for(my $i=0;$i<($#split/2);$i++) {
-            my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
-            my $input;
-            if ($authorised_value eq "date") {
-               $input = 'date';
-            }
-            elsif ($authorised_value) {
-                my $dbh=C4::Context->dbh;
-                my @authorised_values;
-                my %authorised_lib;
-                # builds list, depending on authorised value...
-                if ( $authorised_value eq "branches" ) {
-                    my $branches = GetBranchesLoop();
-                    foreach my $thisbranch (@$branches) {
-                        push @authorised_values, $thisbranch->{value};
-                        $authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname};
+
+    $template->param(
+        'limit'   => $limit,
+        'report_id' => $report_id,
+    );
+
+    my ( $sql, $type, $name, $notes );
+    if (my $report = get_saved_report($report_id)) {
+        $sql   = $report->{savedsql};
+        $name  = $report->{report_name};
+        $notes = $report->{notes};
+
+        my @rows = ();
+        # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
+        if ($sql =~ /<</ && !@sql_params) {
+            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
+            my @split = split /<<|>>/,$sql;
+            my @tmpl_parameters;
+            my @authval_errors;
+            for(my $i=0;$i<($#split/2);$i++) {
+                my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
+                my $input;
+                my $labelid;
+                if ( not defined $authorised_value ) {
+                    # no authorised value input, provide a text box
+                    $input = "text";
+                } elsif ( $authorised_value eq "date" ) {
+                    # require a date, provide a date picker
+                    $input = 'date';
+                } else {
+                    # defined $authorised_value, and not 'date'
+                    my $dbh=C4::Context->dbh;
+                    my @authorised_values;
+                    my %authorised_lib;
+                    # builds list, depending on authorised value...
+                    if ( $authorised_value eq "branches" ) {
+                        my $branches = GetBranchesLoop();
+                        foreach my $thisbranch (@$branches) {
+                            push @authorised_values, $thisbranch->{value};
+                            $authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname};
+                        }
                     }
-                }
-                elsif ( $authorised_value eq "itemtypes" ) {
-                    my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
-                    $sth->execute;
-                    while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
-                        push @authorised_values, $itemtype;
-                        $authorised_lib{$itemtype} = $description;
+                    elsif ( $authorised_value eq "itemtypes" ) {
+                        my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
+                        $sth->execute;
+                        while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
+                            push @authorised_values, $itemtype;
+                            $authorised_lib{$itemtype} = $description;
+                        }
                     }
-                }
-                elsif ( $authorised_value eq "cn_source" ) {
-                    my $class_sources = GetClassSources();
-                    my $default_source = C4::Context->preference("DefaultClassificationSource");
-                    foreach my $class_source (sort keys %$class_sources) {
-                        next unless $class_sources->{$class_source}->{'used'} or
-                                    ($class_source eq $default_source);
-                        push @authorised_values, $class_source;
-                        $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
+                    elsif ( $authorised_value eq "cn_source" ) {
+                        my $class_sources = GetClassSources();
+                        my $default_source = C4::Context->preference("DefaultClassificationSource");
+                        foreach my $class_source (sort keys %$class_sources) {
+                            next unless $class_sources->{$class_source}->{'used'} or
+                                        ($class_source eq $default_source);
+                            push @authorised_values, $class_source;
+                            $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
+                        }
                     }
-                }
-                elsif ( $authorised_value eq "categorycode" ) {
-                    my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description");
-                    $sth->execute;
-                    while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) {
-                        push @authorised_values, $categorycode;
-                        $authorised_lib{$categorycode} = $description;
+                    elsif ( $authorised_value eq "categorycode" ) {
+                        my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description");
+                        $sth->execute;
+                        while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) {
+                            push @authorised_values, $categorycode;
+                            $authorised_lib{$categorycode} = $description;
+                        }
+
+                        #---- "true" authorised value
                     }
-
-                    #---- "true" authorised value
-                }
-                else {
-                    my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");
-
-                    $authorised_values_sth->execute( $authorised_value);
-
-                    while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
-                        push @authorised_values, $value;
-                        $authorised_lib{$value} = $lib;
-                        # For item location, we show the code and the libelle
-                        $authorised_lib{$value} = $lib;
+                    else {
+                        if ( IsAuthorisedValueCategory($authorised_value) ) {
+                            my $query = '
+                            SELECT authorised_value,lib
+                            FROM authorised_values
+                            WHERE category=?
+                            ORDER BY lib
+                            ';
+                            my $authorised_values_sth = $dbh->prepare($query);
+                            $authorised_values_sth->execute( $authorised_value);
+
+                            while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
+                                push @authorised_values, $value;
+                                $authorised_lib{$value} = $lib;
+                                # For item location, we show the code and the libelle
+                                $authorised_lib{$value} = $lib;
+                            }
+                        } else {
+                            # not exists $authorised_value_categories{$authorised_value})
+                            push @authval_errors, {'entry' => $text,
+                                                   'auth_val' => $authorised_value };
+                            # tell the template there's an error
+                            $template->param( auth_val_error => 1 );
+                            # skip scrolling list creation and params push
+                            next;
+                        }
                     }
-                }
-                $input =CGI::scrolling_list(      # FIXME: factor out scrolling_list
-                    -name     => "sql_params",
-                    -values   => \@authorised_values,
+                    $labelid = $text;
+                    $labelid =~ s/\W//g;
+                    $input =CGI::scrolling_list(      # FIXME: factor out scrolling_list
+                        -name     => "sql_params",
+                        -id       => "sql_params_".$labelid,
+                        -values   => \@authorised_values,
 #                     -default  => $value,
-                    -labels   => \%authorised_lib,
-                    -override => 1,
-                    -size     => 1,
-                    -multiple => 0,
-                    -tabindex => 1,
-                );
+                        -labels   => \%authorised_lib,
+                        -override => 1,
+                        -size     => 1,
+                        -multiple => 0,
+                        -tabindex => 1,
+                    );
+                }
 
-            } else {
-                $input = "<input type='text' name='sql_params'/>";
+                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
             }
-            push @tmpl_parameters, {'entry' => $text, 'input' => $input };
-        }
-        $template->param('sql'         => $sql,
-                        'name'         => $name,
-                        'sql_params'   => \@tmpl_parameters,
-                        'enter_params' => 1,
-                        'reports'      => $report,
-                        );
-    } else {
-        # OK, we have parameters, or there are none, we run the report
-        # if there were parameters, replace before running
-        # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
-        my @split = split /<<|>>/,$sql;
-        my @tmpl_parameters;
-        for(my $i=0;$i<$#split/2;$i++) {
-            my $quoted = C4::Context->dbh->quote($sql_params[$i]);
-            # if there are special regexp chars, we must \ them
-            $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
-            $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
-        }
-        my ($sth, $errors) = execute_query($sql, $offset, $limit);
-        my $total = nb_rows($sql) || 0;
-        unless ($sth) {
-            die "execute_query failed to return sth for report $report: $sql";
+            $template->param('sql'         => $sql,
+                            'name'         => $name,
+                            'sql_params'   => \@tmpl_parameters,
+                            'auth_val_errors'  => \@authval_errors,
+                            'enter_params' => 1,
+                            'reports'      => $report_id,
+                            );
         } else {
-            my $headref = $sth->{NAME} || [];
-            my @headers = map { +{ cell => $_ } } @$headref;
-            $template->param(header_row => \@headers);
-            while (my $row = $sth->fetchrow_arrayref()) {
-                my @cells = map { +{ cell => $_ } } @$row;
-                push @rows, { cells => \@cells };
+            # OK, we have parameters, or there are none, we run the report
+            # if there were parameters, replace before running
+            # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
+            my @split = split /<<|>>/,$sql;
+            my @tmpl_parameters;
+            for(my $i=0;$i<$#split/2;$i++) {
+                my $quoted = C4::Context->dbh->quote($sql_params[$i]);
+                # if there are special regexp chars, we must \ them
+                $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
+                $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
+            }
+            my ($sth, $errors) = execute_query($sql, $offset, $limit);
+            my $total = nb_rows($sql) || 0;
+            unless ($sth) {
+                die "execute_query failed to return sth for report $report_id: $sql";
+            } else {
+                my $headers= header_cell_loop($sth);
+                $template->param(header_row => $headers);
+                while (my $row = $sth->fetchrow_arrayref()) {
+                    my @cells = map { +{ cell => $_ } } @$row;
+                    push @rows, { cells => \@cells };
+                }
             }
-        }
 
-        my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
-        my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&amp;phase=Run%20this%20report";
-        if (@sql_params) {
-            $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params);
+            my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
+            my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report_id&amp;phase=Run%20this%20report&amp;limit=$limit";
+            if (@sql_params) {
+                $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params);
+            }
+            $template->param(
+                'results' => \@rows,
+                'sql'     => $sql,
+                'id'      => $report_id,
+                'execute' => 1,
+                'name'    => $name,
+                'notes'   => $notes,
+                'errors'  => $errors,
+                'pagination_bar'  => pagination_bar($url, $totpages, $input->param('page')),
+                'unlimited_total' => $total,
+                'sql_params'      => \@sql_params,
+            );
         }
-        $template->param(
-            'results' => \@rows,
-            'sql'     => $sql,
-            'id'      => $report,
-            'execute' => 1,
-            'name'    => $name,
-            'notes'   => $notes,
-            'errors'  => $errors,
-            'pagination_bar'  => pagination_bar($url, $totpages, $input->param('page')),
-            'unlimited_total' => $total,
-        );
+    }
+    else {
+        push @errors, { no_sql_for_id => $report_id };
     }
 }
 
@@ -593,16 +809,26 @@ elsif ($phase eq 'Export'){
     );
 }
 
-elsif ($phase eq 'Create report from SQL') {
-	# allow the user to paste in sql
-    if ($input->param('sql')) {
+elsif ( $phase eq 'Create report from SQL' ) {
+
+    my ($group, $subgroup);
+    # allow the user to paste in sql
+    if ( $input->param('sql') ) {
+        $group = $input->param('report_group');
+        $subgroup  = $input->param('report_subgroup');
         $template->param(
-            'sql'           => $input->param('sql'),
-            'reportname'    => $input->param('reportname'),
-            'notes'         => $input->param('notes'),
+            'sql'           => $input->param('sql') // '',
+            'reportname'    => $input->param('reportname') // '',
+            'notes'         => $input->param('notes') // '',
         );
     }
-	$template->param('create' => 1);
+    $template->param(
+        'create' => 1,
+        'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
+        'public' => '0',
+        'cache_expiry' => 300,
+        'usecache' => $usecache,
+    );
 }
 
 elsif ($phase eq 'Create Compound Report'){
@@ -624,7 +850,13 @@ elsif ($phase eq 'Save Compound'){
 # pass $sth, get back an array of names for the column headers
 sub header_cell_values {
     my $sth = shift or return ();
-    return @{$sth->{NAME}};
+    my @cols;
+    foreach my $c (@{$sth->{NAME}}) {
+        #FIXME apparently DBI still needs a utf8 fix for this?
+        utf8::decode($c);
+        push @cols, $c;
+    }
+    return @cols;
 }
 
 # pass $sth, get back a TMPL_LOOP-able set of names for the column headers
@@ -637,7 +869,54 @@ foreach (1..6) {
      $template->{VARS}->{'build' . $_} and $template->{VARS}->{'buildx' . $_} and last;
 }
 $template->param(   'referer' => $input->referer(),
-                    'DHTMLcalendar_dateformat' => C4::Dates->DHTMLcalendar(),
                 );
 
 output_html_with_http_headers $input, $cookie, $template->output;
+
+sub groups_with_subgroups {
+    my ($group, $subgroup) = @_;
+
+    my $groups_with_subgroups = get_report_groups();
+    my @g_sg;
+    my @sorted_keys = sort {
+        $groups_with_subgroups->{$a}->{name} cmp $groups_with_subgroups->{$b}->{name}
+    } keys %$groups_with_subgroups;
+    foreach my $g_id (@sorted_keys) {
+        my $v = $groups_with_subgroups->{$g_id};
+        my @subgroups;
+        if (my $sg = $v->{subgroups}) {
+            foreach my $sg_id (sort { $sg->{$a} cmp $sg->{$b} } keys %$sg) {
+                push @subgroups, {
+                    id => $sg_id,
+                    name => $sg->{$sg_id},
+                    selected => ($group && $g_id eq $group && $subgroup && $sg_id eq $subgroup ),
+                };
+            }
+        }
+        push @g_sg, {
+            id => $g_id,
+            name => $v->{name},
+            selected => ($group && $g_id eq $group),
+            subgroups => \@subgroups,
+        };
+    }
+    return \@g_sg;
+}
+
+sub create_non_existing_group_and_subgroup {
+    my ($input, $group, $subgroup) = @_;
+
+    if (defined $group and $group ne '') {
+        my $report_groups = C4::Reports::Guided::get_report_groups;
+        if (not exists $report_groups->{$group}) {
+            my $groupdesc = $input->param('groupdesc') // $group;
+            C4::Koha::AddAuthorisedValue('REPORT_GROUP', $group, $groupdesc);
+        }
+        if (defined $subgroup and $subgroup ne '') {
+            if (not exists $report_groups->{$group}->{subgroups}->{$subgroup}) {
+                my $subgroupdesc = $input->param('subgroupdesc') // $subgroup;
+                C4::Koha::AddAuthorisedValue('REPORT_SUBGROUP', $subgroup, $subgroupdesc, $group);
+            }
+        }
+    }
+}