3 # Copyright 2007 Liblime ltd
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
21 # use warnings; # FIXME
23 use C4::Reports::Guided;
35 Script to control the guided report creation
42 my $referer = $input->referer();
44 my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
46 template_name => "reports/guided_reports_start.tmpl",
50 flagsrequired => { reports => 1 },
55 my $phase = $input->param('phase');
56 my $no_html = 0; # this will be set if we dont want to print out an html::template
59 $template->param( 'start' => 1 );
64 elsif ( $phase eq 'Build new' ) {
67 $template->param( 'build1' => 1 );
70 my $areas = get_report_areas();
71 $template->param( 'areas' => $areas );
75 elsif ( $phase eq 'Used saved' ) {
78 # get list of reports and display them
79 $template->param( 'saved1' => 1 );
80 my $reports = get_saved_reports();
81 $template->param( 'savedreports' => $reports );
84 elsif ( $phase eq 'Delete Saved') {
86 # delete a report from the saved reports list
88 my $id = $input->param('reports');
90 print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Used%20saved");
94 elsif ( $phase eq 'Show SQL'){
96 my $id = $input->param('reports');
97 my $sql = get_sql($id);
104 elsif ($phase eq 'retrieve results') {
105 my $id = $input->param('id');
106 my ($results,$name,$notes) = format_results($id);
110 'results' => $results,
117 elsif ( $phase eq 'Report on this Area' ) {
119 # they have choosen a new report and the area to report on
121 my $area = $input->param('areas');
128 my $types = get_report_types();
129 $template->param( 'types' => $types );
132 elsif ( $phase eq 'Choose this type' ) {
134 # they have chosen type and area
135 # get area and type and pass them to the template
136 my $area = $input->param('area');
137 my $type = $input->param('types');
145 my $columns = get_columns($area,$input);
146 $template->param( 'columns' => $columns );
149 elsif ( $phase eq 'Choose these columns' ) {
151 # we now know type, area, and columns
152 # next step is the constraints
153 my $area = $input->param('area');
154 my $type = $input->param('type');
155 my @columns = $input->param('columns');
156 my $column = join( ',', @columns );
157 my $definitions = get_from_dictionary($area);
164 my $criteria = get_criteria($area,$input);
165 $template->param( 'criteria' => $criteria,
166 'definitions' => $definitions);
169 elsif ( $phase eq 'Choose these criteria' ) {
170 my $area = $input->param('area');
171 my $type = $input->param('type');
172 my $column = $input->param('column');
173 my @definitions = $input->param('definition');
174 my $definition = join (',',@definitions);
175 my @criteria = $input->param('criteria_column');
177 foreach my $crit (@criteria) {
178 my $value = $input->param( $crit . "_value" );
180 if ($value =~ C4::Dates->regexp('syspref')) {
181 $value = C4::Dates->new($value)->output("iso");
183 $query_criteria .= " AND $crit='$value'";
191 'definition' => $definition,
192 'criteriastring' => $query_criteria,
196 my @columns = split( ',', $column );
199 # build structue for use by tmpl_loop to choose columns to order by
200 # need to do something about the order of the order :)
201 # we also want to use the %columns hash to get the plain english names
202 foreach my $col (@columns) {
203 my %total = (name => $col);
204 my @selects = map {+{ value => $_ }} (qw(sum min max avg count));
205 $total{'select'} = \@selects;
206 push @total_by, \%total;
209 $template->param( 'total_by' => \@total_by );
212 elsif ( $phase eq 'Choose These Operations' ) {
213 my $area = $input->param('area');
214 my $type = $input->param('type');
215 my $column = $input->param('column');
216 my $criteria = $input->param('criteria');
217 my $definition = $input->param('definition');
218 my @total_by = $input->param('total_by');
220 foreach my $total (@total_by) {
221 my $value = $input->param( $total . "_tvalue" );
222 $totals .= "$value($total),";
230 'criteriastring' => $criteria,
232 'definition' => $definition,
236 my @columns = split( ',', $column );
239 # build structue for use by tmpl_loop to choose columns to order by
240 # need to do something about the order of the order :)
241 foreach my $col (@columns) {
242 my %order = (name => $col);
243 my @selects = map {+{ value => $_ }} (qw(asc desc));
244 $order{'select'} = \@selects;
245 push @order_by, \%order;
248 $template->param( 'order_by' => \@order_by );
251 elsif ( $phase eq 'Build Report' ) {
253 # now we have all the info we need and can build the sql
254 my $area = $input->param('area');
255 my $type = $input->param('type');
256 my $column = $input->param('column');
257 my $crit = $input->param('criteria');
258 my $totals = $input->param('totals');
259 my $definition = $input->param('definition');
260 # my @criteria = split( ',', $crit );
261 my $query_criteria=$crit;
262 # split the columns up by ,
263 my @columns = split( ',', $column );
264 my @order_by = $input->param('order_by');
267 foreach my $order (@order_by) {
268 my $value = $input->param( $order . "_ovalue" );
269 if ($query_orderby) {
270 $query_orderby .= ",$order $value";
273 $query_orderby = " ORDER BY $order $value";
279 build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition );
287 elsif ( $phase eq 'Save' ) {
288 # Save the report that has just been built
289 my $sql = $input->param('sql');
290 my $type = $input->param('type');
298 elsif ( $phase eq 'Save Report' ) {
299 # save the sql pasted in by a user
300 my $sql = $input->param('sql');
301 my $name = $input->param('reportname');
302 my $type = $input->param('types');
303 my $notes = $input->param('notes');
306 if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
307 $error->{'sqlerr'} = $1;
308 push @errors, $error;
310 elsif ($sql !~ /^(SELECT)/i) {
311 $error->{'queryerr'} = 1;
312 push @errors, $error;
316 'save_successful' => 1,
317 'errors' => \@errors,
319 'reportname'=> $name,
325 save_report( $sql, $name, $type, $notes );
327 'save_successful' => 1,
332 # This condition is not used currently
333 #elsif ( $phase eq 'Execute' ) {
334 # # run the sql, and output results in a template
335 # my $sql = $input->param('sql');
336 # my $type = $input->param('type');
337 # my ($results, $total, $errors) = execute_query($sql, $type);
339 # 'results' => $results,
345 elsif ($phase eq 'Run this report'){
346 # execute a saved report
347 # FIXME The default limit should not be hardcoded...
350 my $report = $input->param('reports');
352 if ($input->param('page')) {
353 $offset = ($input->param('page') - 1) * 20;
358 my ($sql,$type,$name,$notes) = get_saved_report($report);
359 my ($results, $total, $errors) = execute_query($sql, $type, $offset, $limit);
360 my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
361 my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&phase=Run%20this%20report";
363 'results' => $results,
368 'pagination_bar' => pagination_bar($url, $totpages, $input->param('page'), "page"),
373 elsif ($phase eq 'Export'){
374 binmode STDOUT, ':utf8';
376 # export results to tab separated text
377 my $sql = $input->param('sql');
378 my $format = $input->param('format');
379 my ($results, $total, $errors) = execute_query($sql,1,0,0,$format);
382 print $input->header( -type => 'application/octet-stream',
383 -attachment=>'reportresults.csv'
388 'results' => $results,
391 'name' => 'Error exporting report!',
393 'pagination_bar' => '',
399 elsif ($phase eq 'Create report from SQL') {
400 # allow the user to paste in sql
401 if ($input->param('sql')) {
403 'sql' => $input->param('sql'),
404 'reportname' => $input->param('reportname'),
405 'notes' => $input->param('notes'),
408 $template->param('create' => 1);
409 my $types = get_report_types();
410 if (my $type = $input->param('type')) {
411 for my $i ( 0 .. $#{@$types}) {
412 @$types[$i]->{'selected'} = 1 if @$types[$i]->{'id'} eq $type;
415 $template->param( 'types' => $types );
418 elsif ($phase eq 'Create Compound Report'){
419 my $reports = get_saved_reports();
420 $template->param( 'savedreports' => $reports,
425 elsif ($phase eq 'Save Compound'){
426 my $master = $input->param('master');
427 my $subreport = $input->param('subreport');
428 # my $compound_report = create_compound($master,$subreport);
429 # my $results = run_compound($compound_report);
430 my ($mastertables,$subtables) = create_compound($master,$subreport);
431 $template->param( 'save_compound' => 1,
432 master=>$mastertables,
437 $template->param( 'referer' => $referer,
438 'DHTMLcalendar_dateformat' => C4::Dates->DHTMLcalendar(),
442 output_html_with_http_headers $input, $cookie, $template->output;