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

(-)a/C4/Reports/Guided.pm (-14 / +44 lines)
Lines 24-30 use Carp; Link Here
24
24
25
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
25
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
26
use C4::Context;
26
use C4::Context;
27
use C4::Dates qw/format_date/;
27
use C4::Dates qw/format_date format_date_in_iso/;
28
use C4::Output;
28
use C4::Output;
29
use C4::Dates;
29
use C4::Dates;
30
use XML::Simple;
30
use XML::Simple;
Lines 546-567 sub delete_report { Link Here
546
	$sth->execute($id);
546
	$sth->execute($id);
547
}	
547
}	
548
548
549
# $filter is either { date => $d, author => $a, keyword => $kw }
550
# or $keyword. Optional.
551
my $DATE_FORMAT = "%d/%m/%Y";
549
sub get_saved_reports {
552
sub get_saved_reports {
553
    my ($filter) = @_;
554
    $filter = { keyword => $filter } if $filter && !ref( $filter );
555
550
    my $dbh   = C4::Context->dbh();
556
    my $dbh   = C4::Context->dbh();
551
    my $query = "SELECT *,saved_sql.id AS id FROM saved_sql 
557
    my (@cond,@args);
552
    LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
558
    my $query = "SELECT saved_sql.id, report_id, report,
553
    ORDER by date_created";
559
                        date_run, date_created, last_modified, savedsql, last_run,
554
    my $sth   = $dbh->prepare($query);
560
                        report_name, type, notes,
555
    $sth->execute();
561
                        borrowernumber, surname as borrowersurname, firstname as borrowerfirstname
556
    
562
                 FROM saved_sql 
557
    my $result = $sth->fetchall_arrayref({});
563
                 LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
558
    foreach (@$result){
564
                 LEFT OUTER JOIN borrowers USING (borrowernumber)";
559
        $_->{date_created} = format_date($_->{date_created}); 
565
    if ($filter) {
560
        
566
        if (my $date = $filter->{date}) {
561
        my $member = C4::Members::GetMember(borrowernumber=>$_->{borrowernumber});
567
            $date = format_date_in_iso($date);
562
        $_->{borrowerfirstname} = $member->{firstname};
568
            push @cond, "DATE(date_run) = ? OR
563
        $_->{borrowersurname}   = $member->{surname};
569
                         DATE(date_created) = ? OR
570
                         DATE(last_modified) = ? OR
571
                         DATE(last_run) = ?";
572
            push @args, $date, $date, $date, $date;
573
        }
574
        if (my $author = $filter->{author}) {
575
            $author = "%$author%";
576
            push @cond, "surname LIKE ? OR
577
                         firstname LIKE ?";
578
            push @args, $author, $author;
579
        }
580
        if (my $keyword = $filter->{keyword}) {
581
            $keyword = "%$keyword%";
582
            push @cond, "report LIKE ? OR
583
                         report_name LIKE ? OR
584
                         notes LIKE ? OR
585
                         savedsql LIKE ?";
586
            push @args, $keyword, $keyword, $keyword, $keyword;
587
        }
564
    }
588
    }
589
    $query .= " WHERE ".join( " AND ", map "($_)", @cond ) if @cond;
590
    $query .= " ORDER by date_created";
591
    
592
    my $result = $dbh->selectall_arrayref($query, {Slice => {}}, @args);
593
    $_->{date_created} = format_date($_->{date_created}) foreach @$result;
594
565
    return $result;
595
    return $result;
566
}
596
}
567
597
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl (+21 lines)
Lines 125-130 canned reports and writing custom SQL reports.</p> Link Here
125
<!-- TMPL_IF NAME="savedreports" --><h1>Saved Reports</h1>
125
<!-- TMPL_IF NAME="savedreports" --><h1>Saved Reports</h1>
126
<p>Choose the report to run from the list</p>
126
<p>Choose the report to run from the list</p>
127
127
128
<div style="margin: 5px">
129
<form action="?">
130
  <input type="hidden" name="phase" value="Use saved" />
131
  <input type="hidden" name="filter_set" value="1" />
132
  <table>
133
    <tr>
134
      <td rowspan="3"><h3>Filter</h3><br/><input type="submit" value="Go" /></td>
135
      <td align="right">Date:</td>
136
      <td><input type="text" name="filter_date" size="10" value="<!-- TMPL_VAR NAME="filter_date" ESCAPE="HTML"-->" /></td>
137
    </tr>
138
    <tr>
139
      <td align="right">Author:</td>
140
      <td><input type="text" name="filter_author" value="<!-- TMPL_VAR NAME="filter_author" ESCAPE="HTML"-->" /></td>
141
    </tr>
142
    <tr>
143
      <td align="right">Keyword:</td>
144
      <td><input type="text" name="filter_keyword" value="<!-- TMPL_VAR NAME="filter_keyword" ESCAPE="HTML"-->" /></td>
145
    </tr>
146
  </table>
147
</form>
148
</div>
128
<span id="pagertable_reports" class="pager">
149
<span id="pagertable_reports" class="pager">
129
 <form class="formpager">&nbsp;<strong>page(s)</strong>&nbsp;:
150
 <form class="formpager">&nbsp;<strong>page(s)</strong>&nbsp;:
130
  <img src="<!-- TMPL_VAR name="interface" -->/prog/img/first.png" class="first"/>
151
  <img src="<!-- TMPL_VAR name="interface" -->/prog/img/first.png" class="first"/>
(-)a/reports/guided_reports.pl (-5 / +23 lines)
Lines 22-28 use strict; Link Here
22
use CGI;
22
use CGI;
23
use Text::CSV;
23
use Text::CSV;
24
use C4::Reports::Guided;
24
use C4::Reports::Guided;
25
use C4::Auth;
25
use C4::Auth qw/:DEFAULT get_session/;
26
use C4::Output;
26
use C4::Output;
27
use C4::Dates;
27
use C4::Dates;
28
use C4::Debug;
28
use C4::Debug;
Lines 61-68 my ( $template, $borrowernumber, $cookie ) = get_template_and_user( Link Here
61
        debug           => 1,
61
        debug           => 1,
62
    }
62
    }
63
);
63
);
64
my $session = $cookie ? get_session($cookie->value) : undef;
64
65
65
    my @errors = ();
66
my $filter;
67
if ( $input->param("filter_set") ) {
68
    $filter = {};
69
    $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword/;
70
    $session->param('report_filter', $filter) if $session;
71
}
72
elsif ($session) {
73
    $filter = $session->param('report_filter');
74
}
75
76
77
my @errors = ();
66
if ( !$phase ) {
78
if ( !$phase ) {
67
    $template->param( 'start' => 1 );
79
    $template->param( 'start' => 1 );
68
    # show welcome page
80
    # show welcome page
Lines 75-82 elsif ( $phase eq 'Build new' ) { Link Here
75
elsif ( $phase eq 'Use saved' ) {
87
elsif ( $phase eq 'Use saved' ) {
76
    # use a saved report
88
    # use a saved report
77
    # get list of reports and display them
89
    # get list of reports and display them
78
    $template->param( 'saved1' => 1 );
90
    $template->param(
79
    $template->param( 'savedreports' => get_saved_reports() ); 
91
        'saved1' => 1,
92
        'savedreports' => get_saved_reports($filter),
93
    );
94
    if ($filter) {
95
        while ( my ($k, $v) = each %$filter ) {
96
            $template->param( "filter_$k" => $v ) if $v;
97
        }
98
    }
80
}
99
}
81
100
82
elsif ( $phase eq 'Delete Saved') {
101
elsif ( $phase eq 'Delete Saved') {
83
- 

Return to bug 6033