Projekt

Allgemein

Profil

Herunterladen (4,52 KB) Statistiken
| Zweig: | Markierung: | Revision:

kivitendo/SL/Controller/CustomDataExport.pm @ a9107c90

e713c314 Moritz Bunkus
package SL::Controller::CustomDataExport;

use strict;
use utf8;

use parent qw(SL::Controller::Base);

use DBI qw(:sql_types);
use File::Temp ();
use List::UtilsBy qw(sort_by);
use POSIX qw(strftime);
use Text::CSV_XS;

use SL::DB::CustomDataExportQuery;
use SL::Locale::String qw(t8);

use Rose::Object::MakeMethods::Generic
(
scalar => [ qw(rows) ],
09c8c053 Moritz Bunkus
'scalar --get_set_init' => [ qw(query queries parameters) ],
e713c314 Moritz Bunkus
);

__PACKAGE__->run_before('check_auth');
__PACKAGE__->run_before('setup_javascripts');

#
# actions
#

sub action_list {
my ($self) = @_;

$self->render('custom_data_export/list', title => $::locale->text('Execute a custom data export query'));
}

sub action_export {
my ($self) = @_;

if (!$::form->{format}) {
$self->setup_export_action_bar;
return $self->render('custom_data_export/export', title => t8("Execute custom data export '#1'", $self->query->name));
}

$self->execute_query;

if (scalar(@{ $self->rows // [] }) == 1) {
$self->setup_empty_result_set_action_bar;
return $self->render('custom_data_export/empty_result_set', title => t8("Execute custom data export '#1'", $self->query->name));
}


my $method = "export_as_" . $::form->{format};
$self->$method;
}

#
# filters
#

sub check_auth {
my ($self) = @_;
$::auth->assert($self->query->access_right) if $self->query->access_right;
}

sub setup_javascripts {
$::request->layout->add_javascripts('kivi.Validator.js');
}

#
# helpers
#

sub init_query { $::form->{id} ? SL::DB::CustomDataExportQuery->new(id => $::form->{id})->load : SL::DB::CustomDataExportQuery->new }
sub init_parameters { [ sort_by { lc $_->name } @{ $_[0]->query->parameters // [] } ] }

sub init_queries {
my %rights_map = %{ $::auth->load_rights_for_user($::form->{login}) };
my @granted_rights = grep { $rights_map{$_} } keys %rights_map;

return scalar SL::DB::Manager::CustomDataExportQuery->get_all_sorted(
where => [
or => [
access_right => undef,
access_right => '',
(access_right => \@granted_rights) x !!@granted_rights,
],
],
)
}

sub setup_export_action_bar {
my ($self) = @_;

for my $bar ($::request->layout->get('actionbar')) {
$bar->add(
action => [
t8('Export'),
submit => [ '#form', { action => 'CustomDataExport/export' } ],
checks => [ 'kivi.validate_form' ],
accesskey => 'enter',
],
action => [
t8('Back'),
call => [ 'kivi.history_back' ],
],
);
}
}

sub setup_empty_result_set_action_bar {
my ($self) = @_;

for my $bar ($::request->layout->get('actionbar')) {
$bar->add(
action => [
t8('Back'),
call => [ 'kivi.history_back' ],
],
);
}
}

sub prepare_query {
my ($self) = @_;

my $sql_query = $self->query->sql_query;
my @values;

my %values_by_name;

foreach my $parameter (@{ $self->query->parameters // [] }) {
my $value = ($::form->{parameters} // {})->{ $parameter->name };
$values_by_name{ $parameter->name } = $parameter->parameter_type eq 'number' ? $::form->parse_amount(\%::myconfig, $value) : $value;
}

while ($sql_query =~ m{<\%(.+?)\%>}) {
push @values, $values_by_name{$1};
substr($sql_query, $-[0], $+[0] - $-[0], '?');
}

return ($sql_query, @values);
}

sub execute_query {
my ($self) = @_;

my ($sql_query, @values) = $self->prepare_query;
my $sth = $self->query->db->dbh->prepare($sql_query) || $::form->dberror;
$sth->execute(@values) || $::form->dberror;

my @names = @{ $sth->{NAME} };
my @types = @{ $sth->{TYPE} };
my @data = @{ $sth->fetchall_arrayref };

$sth->finish;

foreach my $row (@data) {
foreach my $col (0..$#types) {
my $type = $types[$col];

if ($type == SQL_NUMERIC) {
$row->[$col] = $::form->format_amount(\%::myconfig, $row->[$col]);
}
}
}

$self->rows([
\@names,
@data,
]);
}

sub export_as_csv {
my ($self) = @_;

my $csv = Text::CSV_XS->new({
binary => 1,
sep_char => ';',
eol => "\n",
});

my ($file_handle, $file_name) = File::Temp::tempfile;

binmode $file_handle, ":encoding(utf8)";

$csv->print($file_handle, $_) for @{ $self->rows };

$file_handle->close;

my $report_name = $self->query->name;
$report_name =~ s{[^[:word:]]+}{_}ig;
$report_name .= strftime('_%Y-%m-%d_%H-%M-%S.csv', localtime());

$self->send_file(
$file_name,
content_type => 'text/csv',
name => $report_name,
);
}

1;