- my $query = "SELECT *,saved_sql.id AS id FROM saved_sql
- LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
- ORDER by date_created";
- my $sth = $dbh->prepare($query);
- $sth->execute();
-
- my $result = $sth->fetchall_arrayref({});
- foreach (@$result){
- $_->{date_created} = format_date($_->{date_created});
-
- my $member = C4::Members::GetMember($_->{borrowernumber});
- $_->{borrowerfirstname} = $member->{firstname};
- $_->{borrowersurname} = $member->{surname};
+ my (@cond,@args);
+ my $query = "SELECT saved_sql.id, report_id, report,
+ date_run, date_created, last_modified, savedsql, last_run,
+ report_name, type, notes,
+ borrowernumber, surname as borrowersurname, firstname as borrowerfirstname
+ FROM saved_sql
+ LEFT JOIN saved_reports ON saved_reports.report_id = saved_sql.id
+ LEFT OUTER JOIN borrowers USING (borrowernumber)";
+ if ($filter) {
+ if (my $date = $filter->{date}) {
+ $date = format_date_in_iso($date);
+ push @cond, "DATE(date_run) = ? OR
+ DATE(date_created) = ? OR
+ DATE(last_modified) = ? OR
+ DATE(last_run) = ?";
+ push @args, $date, $date, $date, $date;
+ }
+ if (my $author = $filter->{author}) {
+ $author = "%$author%";
+ push @cond, "surname LIKE ? OR
+ firstname LIKE ?";
+ push @args, $author, $author;
+ }
+ if (my $keyword = $filter->{keyword}) {
+ $keyword = "%$keyword%";
+ push @cond, "report LIKE ? OR
+ report_name LIKE ? OR
+ notes LIKE ? OR
+ savedsql LIKE ?";
+ push @args, $keyword, $keyword, $keyword, $keyword;
+ }