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

(-)a/C4/Reports/Guided.pm (-53 / +4 lines)
Lines 43-49 BEGIN { Link Here
43
      get_report_types get_report_areas get_report_groups get_columns build_query get_criteria
43
      get_report_types get_report_areas get_report_groups get_columns build_query get_criteria
44
      save_report get_saved_reports execute_query get_saved_report create_compound run_compound
44
      save_report get_saved_reports execute_query get_saved_report create_compound run_compound
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 get_sql
47
      nb_rows update_sql build_authorised_value_list
47
      nb_rows update_sql build_authorised_value_list
48
      GetReservedAuthorisedValues
48
      GetReservedAuthorisedValues
49
      GetParametersFromSQL
49
      GetParametersFromSQL
Lines 537-546 sub execute_query { Link Here
537
    $sth->execute(@$sql_params, $offset, $limit);
537
    $sth->execute(@$sql_params, $offset, $limit);
538
    return ( $sth, { queryerr => $sth->errstr } ) if ($sth->err);
538
    return ( $sth, { queryerr => $sth->errstr } ) if ($sth->err);
539
    return ( $sth );
539
    return ( $sth );
540
    # my @xmlarray = ... ;
541
    # my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id";
542
    # my $xml = XML::Dumper->new()->pl2xml( \@xmlarray );
543
    # store_results($id,$xml);
544
}
540
}
545
541
546
=head2 save_report($sql,$name,$type,$notes)
542
=head2 save_report($sql,$name,$type,$notes)
Lines 594-642 sub update_sql { Link Here
594
    $dbh->do($query, undef, $sql, $name, $group, $subgroup, $notes, $cache_expiry, $public, $id );
590
    $dbh->do($query, undef, $sql, $name, $group, $subgroup, $notes, $cache_expiry, $public, $id );
595
}
591
}
596
592
597
sub store_results {
598
	my ($id,$xml)=@_;
599
	my $dbh = C4::Context->dbh();
600
	my $query = "SELECT * FROM saved_reports WHERE report_id=?";
601
	my $sth = $dbh->prepare($query);
602
	$sth->execute($id);
603
	if (my $data=$sth->fetchrow_hashref()){
604
		my $query2 = "UPDATE saved_reports SET report=?,date_run=now() WHERE report_id=?";
605
		my $sth2 = $dbh->prepare($query2);
606
	    $sth2->execute($xml,$id);
607
	}
608
	else {
609
		my $query2 = "INSERT INTO saved_reports (report_id,report,date_run) VALUES (?,?,now())";
610
		my $sth2 = $dbh->prepare($query2);
611
		$sth2->execute($id,$xml);
612
	}
613
}
614
615
sub format_results {
616
	my ($id) = @_;
617
	my $dbh = C4::Context->dbh();
618
	my $query = "SELECT * FROM saved_reports WHERE report_id = ?";
619
	my $sth = $dbh->prepare($query);
620
	$sth->execute($id);
621
	my $data = $sth->fetchrow_hashref();
622
	my $dump = new XML::Dumper;
623
	my $perl = $dump->xml2pl( $data->{'report'} );
624
	foreach my $row (@$perl) {
625
		my $htmlrow="<tr>";
626
		foreach my $key (keys %$row){
627
			$htmlrow .= "<td>$row->{$key}</td>";
628
		}
629
		$htmlrow .= "</tr>";
630
		$row->{'row'} = $htmlrow;
631
	}
632
	$sth->finish;
633
	$query = "SELECT * FROM saved_sql WHERE id = ?";
634
	$sth = $dbh->prepare($query);
635
	$sth->execute($id);
636
	$data = $sth->fetchrow_hashref();
637
	return ($perl,$data->{'report_name'},$data->{'notes'});	
638
}	
639
640
sub delete_report {
593
sub delete_report {
641
    my (@ids) = @_;
594
    my (@ids) = @_;
642
    return unless @ids;
595
    return unless @ids;
Lines 653-662 sub delete_report { Link Here
653
sub get_saved_reports_base_query {
606
sub get_saved_reports_base_query {
654
    my $area_name_sql_snippet = get_area_name_sql_snippet;
607
    my $area_name_sql_snippet = get_area_name_sql_snippet;
655
    return <<EOQ;
608
    return <<EOQ;
656
SELECT s.*, r.report, r.date_run, $area_name_sql_snippet, av_g.lib AS groupname, av_sg.lib AS subgroupname,
609
SELECT s.*, $area_name_sql_snippet, av_g.lib AS groupname, av_sg.lib AS subgroupname,
657
b.firstname AS borrowerfirstname, b.surname AS borrowersurname
610
b.firstname AS borrowerfirstname, b.surname AS borrowersurname
658
FROM saved_sql s
611
FROM saved_sql s
659
LEFT JOIN saved_reports r ON r.report_id = s.id
660
LEFT OUTER JOIN authorised_values av_g ON (av_g.category = 'REPORT_GROUP' AND av_g.authorised_value = s.report_group)
612
LEFT OUTER JOIN authorised_values av_g ON (av_g.category = 'REPORT_GROUP' AND av_g.authorised_value = s.report_group)
661
LEFT OUTER JOIN authorised_values av_sg ON (av_sg.category = 'REPORT_SUBGROUP' AND av_sg.lib_opac = s.report_group AND av_sg.authorised_value = s.report_subgroup)
613
LEFT OUTER JOIN authorised_values av_sg ON (av_sg.category = 'REPORT_SUBGROUP' AND av_sg.lib_opac = s.report_group AND av_sg.authorised_value = s.report_subgroup)
662
LEFT OUTER JOIN borrowers b USING (borrowernumber)
614
LEFT OUTER JOIN borrowers b USING (borrowernumber)
Lines 676-686 sub get_saved_reports { Link Here
676
    if ($filter) {
628
    if ($filter) {
677
        if (my $date = $filter->{date}) {
629
        if (my $date = $filter->{date}) {
678
            $date = format_date_in_iso($date);
630
            $date = format_date_in_iso($date);
679
            push @cond, "DATE(date_run) = ? OR
631
            push @cond, "DATE(date_created) = ? OR
680
                         DATE(date_created) = ? OR
681
                         DATE(last_modified) = ? OR
632
                         DATE(last_modified) = ? OR
682
                         DATE(last_run) = ?";
633
                         DATE(last_run) = ?";
683
            push @args, $date, $date, $date, $date;
634
            push @args, $date, $date, $date;
684
        }
635
        }
685
        if (my $author = $filter->{author}) {
636
        if (my $author = $filter->{author}) {
686
            $author = "%$author%";
637
            $author = "%$author%";
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-3 / +1 lines)
Lines 81-87 $(document).ready(function(){ Link Here
81
            { "aTargets": [ 1, 2 ], "sType": "natural"  }
81
            { "aTargets": [ 1, 2 ], "sType": "natural"  }
82
        ],
82
        ],
83
        "aoColumns": [
83
        "aoColumns": [
84
            null,null,null,null,null,null,null,null,{ "sType": "title-string" },null,[% IF (usecache) %]null,[% END %]null,null
84
            null,null,null,null,null,null,null,null,{ "sType": "title-string" },null,[% IF (usecache) %]null,[% END %]null
85
        ],
85
        ],
86
        'oLanguage': {
86
        'oLanguage': {
87
            'sZeroRecords': _("No matching reports found")
87
            'sZeroRecords': _("No matching reports found")
Lines 315-321 canned reports and writing custom SQL reports.</p> Link Here
315
                    <th>Creation date</th>
315
                    <th>Creation date</th>
316
                    <th>Public</th>
316
                    <th>Public</th>
317
                    [% IF (usecache) %] <th>Cache expiry (seconds)</th> [% END %]
317
                    [% IF (usecache) %] <th>Cache expiry (seconds)</th> [% END %]
318
                    <th>Saved results</th>
319
                    <th>&nbsp;</th>
318
                    <th>&nbsp;</th>
320
                </tr>
319
                </tr>
321
            </thead>
320
            </thead>
Lines 343-349 canned reports and writing custom SQL reports.</p> Link Here
343
                        <td>No</td>
342
                        <td>No</td>
344
                        [% END %]
343
                        [% END %]
345
                        [% IF (usecache) %] <td>[% savedreport.cache_expiry %]</td> [% END %]
344
                        [% IF (usecache) %] <td>[% savedreport.cache_expiry %]</td> [% END %]
346
                        <td>[% IF ( savedreport.date_run ) %]<a href="/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&amp;id=[% savedreport.id %]">[% savedreport.date_run %]</a>[% END %]
347
                        </td>
345
                        </td>
348
                        <td>
346
                        <td>
349
                            <div class="dropdown">
347
                            <div class="dropdown">
(-)a/misc/cronjobs/runreport.pl (-4 lines)
Lines 296-303 foreach my $report_id (@ARGV) { Link Here
296
    } else {
296
    } else {
297
        print $message;
297
        print $message;
298
    }
298
    }
299
    # my @xmlarray = ... ;
300
    # my $url = "/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=$id";
301
    # my $xml = XML::Dumper->new()->pl2xml( \@xmlarray );
302
    # store_results($id,$xml);
303
}
299
}
(-)a/reports/guided_reports.pl (-13 lines)
Lines 250-267 elsif ( $phase eq 'Update SQL'){ Link Here
250
    }
250
    }
251
}
251
}
252
252
253
elsif ($phase eq 'retrieve results') {
254
	my $id = $input->param('id');
255
	my ($results,$name,$notes) = format_results($id);
256
	# do something
257
	$template->param(
258
		'retresults' => 1,
259
		'results' => $results,
260
		'name' => $name,
261
		'notes' => $notes,
262
    );
263
}
264
265
elsif ( $phase eq 'Report on this Area' ) {
253
elsif ( $phase eq 'Report on this Area' ) {
266
    my $cache_expiry_units = $input->param('cache_expiry_units'),
254
    my $cache_expiry_units = $input->param('cache_expiry_units'),
267
    my $cache_expiry = $input->param('cache_expiry');
255
    my $cache_expiry = $input->param('cache_expiry');
268
- 

Return to bug 14435