--- /dev/null
+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) ],
+ 'scalar --get_set_init' => [ qw(query queries parameters today) ],
+);
+
+__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_today { DateTime->today_local }
+
+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;
--- /dev/null
+package SL::Controller::CustomDataExportDesigner;
+
+use strict;
+use utf8;
+
+use parent qw(SL::Controller::Base);
+
+use List::UtilsBy qw(sort_by);
+
+use SL::DB::CustomDataExportQuery;
+use SL::Helper::Flash qw(flash_later);
+use SL::Locale::String qw(t8);
+
+use Rose::Object::MakeMethods::Generic
+(
+ 'scalar --get_set_init' => [ qw(query queries access_rights) ],
+);
+
+__PACKAGE__->run_before('check_auth');
+__PACKAGE__->run_before('setup_javascripts');
+
+#
+# actions
+#
+
+sub action_list {
+ my ($self) = @_;
+
+ $self->setup_list_action_bar;
+ $self->render('custom_data_export_designer/list', title => $::locale->text('Design custom data export queries'));
+}
+
+sub action_edit {
+ my ($self) = @_;
+
+ my $title = $self->query->id ? t8('Edit custom data export query') : t8('Add custom data export query');
+
+ $self->setup_edit_action_bar;
+ $self->render('custom_data_export_designer/edit', title => $title);
+}
+
+sub action_edit_parameters {
+ my ($self) = @_;
+
+ my $title = $self->query->id ? t8('Edit custom data export query') : t8('Add custom data export query');
+ my @parameters = $self->gather_query_data;
+
+ $self->setup_edit_parameters_action_bar;
+ $self->render('custom_data_export_designer/edit_parameters', title => $title, PARAMETERS => \@parameters);
+}
+
+sub action_save {
+ my ($self) = @_;
+
+ my @parameters = $self->gather_query_data;
+
+ $self->query->parameters(\@parameters);
+
+ $self->query->save;
+
+ flash_later('info', t8('The custom data export has been saved.'));
+
+ $self->redirect_to($self->url_for(action => 'list'));
+}
+
+sub action_delete {
+ my ($self) = @_;
+
+ $self->query->delete;
+
+ flash_later('info', t8('The custom data export has been deleted.'));
+
+ $self->redirect_to($self->url_for(action => 'list'));
+}
+
+#
+# filters
+#
+
+sub check_auth {
+ $::auth->assert('custom_data_export_designer');
+}
+
+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_queries { scalar SL::DB::Manager::CustomDataExportQuery->get_all_sorted }
+
+sub init_access_rights {
+ my @rights = ([ '', t8('Available to all users') ]);
+ my $category;
+
+ foreach my $right ($::auth->all_rights_full) {
+ # name, description, category
+
+ if ($right->[2]) {
+ $category = t8($right->[1]);
+ } elsif ($category) {
+ push @rights, [ $right->[0], sprintf('%s → %s [%s]', $category, t8($right->[1]), $right->[0]) ];
+ }
+ }
+
+ return \@rights;
+}
+
+sub setup_list_action_bar {
+ my ($self) = @_;
+
+ for my $bar ($::request->layout->get('actionbar')) {
+ $bar->add(
+ link => [
+ t8('Add'),
+ link => $self->url_for(action => 'edit'),
+ accesskey => 'enter',
+ ],
+ );
+ }
+}
+
+sub setup_edit_action_bar {
+ my ($self) = @_;
+
+ for my $bar ($::request->layout->get('actionbar')) {
+ $bar->add(
+ action => [
+ t8('Continue'),
+ submit => [ '#form', { action => 'CustomDataExportDesigner/edit_parameters' } ],
+ checks => [ 'kivi.validate_form' ],
+ accesskey => 'enter',
+ ],
+ action => [
+ t8('Delete'),
+ submit => [ '#form', { action => 'CustomDataExportDesigner/delete' } ],
+ confirm => t8('Do you really want to delete this object?'),
+ disabled => !$self->query->id ? t8('This object has not been saved yet.')
+ : undef,
+ ],
+ action => [
+ t8('Back'),
+ call => [ 'kivi.history_back' ],
+ ],
+ );
+ }
+}
+
+sub setup_edit_parameters_action_bar {
+ my ($self) = @_;
+
+ for my $bar ($::request->layout->get('actionbar')) {
+ $bar->add(
+ action => [
+ t8('Save'),
+ submit => [ '#form', { action => 'CustomDataExportDesigner/save' } ],
+ checks => [ 'kivi.validate_form' ],
+ accesskey => 'enter',
+ ],
+ action => [
+ t8('Back'),
+ call => [ 'kivi.history_back' ],
+ ],
+ );
+ }
+}
+
+sub gather_query_data {
+ my ($self) = @_;
+
+ $self->query->$_($::form->{query}->{$_}) for qw(name description sql_query access_right);
+ return $self->gather_query_parameters;
+}
+
+sub gather_query_parameters {
+ my ($self) = @_;
+
+ my %used_parameter_names = map { ($_ => 1) } $self->query->used_parameter_names;
+ my @existing_parameters = grep { $used_parameter_names{$_->name} } @{ $self->query->parameters // [] };
+ my %parameters_by_name = map { ($_->name => $_) } @existing_parameters;
+ $parameters_by_name{$_} //= SL::DB::CustomDataExportQueryParameter->new(name => $_, parameter_type => 'text') for keys %used_parameter_names;
+
+ foreach my $parameter_data (@{ $::form->{parameters} // [] }) {
+ my $parameter_obj = $parameters_by_name{ $parameter_data->{name} };
+ next unless $parameter_obj;
+
+ $parameter_obj->$_($parameter_data->{$_}) for qw(parameter_type description);
+ }
+
+ return sort_by { lc $_->name } values %parameters_by_name;
+}
+
+1;
--- /dev/null
+package SL::DB::CustomDataExportQuery;
+
+use strict;
+
+use SL::DB::MetaSetup::CustomDataExportQuery;
+use SL::DB::Manager::CustomDataExportQuery;
+
+__PACKAGE__->meta->add_relationship(
+ parameters => {
+ type => 'one to many',
+ class => 'SL::DB::CustomDataExportQueryParameter',
+ column_map => { id => 'query_id' },
+ },
+);
+
+__PACKAGE__->meta->initialize;
+
+sub used_parameter_names {
+ my ($self) = @_;
+
+ my %parameters;
+
+ my $sql_query = $self->sql_query // '';
+ $parameters{$1} = 1 while $sql_query =~ m{<\%(.+?)\%>}g;
+
+ return sort keys %parameters;
+}
+
+1;
--- /dev/null
+# This file has been auto-generated only because it didn't exist.
+# Feel free to modify it at will; it will not be overwritten automatically.
+
+package SL::DB::CustomDataExportQueryParameter;
+
+use strict;
+
+use SL::DB::MetaSetup::CustomDataExportQueryParameter;
+use SL::DB::Manager::CustomDataExportQueryParameter;
+
+__PACKAGE__->meta->initialize;
+
+1;
use SL::DB::CsvImportReportRow;
use SL::DB::CsvImportReportStatus;
use SL::DB::Currency;
+use SL::DB::CustomDataExportQuery;
+use SL::DB::CustomDataExportQueryParameter;
use SL::DB::CustomVariable;
use SL::DB::CustomVariableConfig;
use SL::DB::CustomVariableConfigPartsgroup;
csv_import_report_rows => 'csv_import_report_row',
csv_import_report_status => 'csv_import_report_status',
currencies => 'currency',
+ custom_data_export_queries => 'CustomDataExportQuery',
+ custom_data_export_query_parameters => 'CustomDataExportQueryParameter',
custom_variable_config_partsgroups => 'custom_variable_config_partsgroup',
custom_variable_configs => 'custom_variable_config',
custom_variables => 'custom_variable',
--- /dev/null
+package SL::DB::Manager::CustomDataExportQuery;
+
+use strict;
+
+use parent qw(SL::DB::Helper::Manager);
+
+use SL::DB::Helper::Sorted;
+
+sub object_class { 'SL::DB::CustomDataExportQuery' }
+
+__PACKAGE__->make_manager_methods;
+
+sub _sort_spec {
+ return ( default => [ 'name', 1 ],
+ name => 'lower(custom_data_export_queries.name)',
+ columns => { SIMPLE => 'ALL' });
+}
+
+1;
--- /dev/null
+# This file has been auto-generated only because it didn't exist.
+# Feel free to modify it at will; it will not be overwritten automatically.
+
+package SL::DB::Manager::CustomDataExportQueryParameter;
+
+use strict;
+
+use parent qw(SL::DB::Helper::Manager);
+
+sub object_class { 'SL::DB::CustomDataExportQueryParameter' }
+
+__PACKAGE__->make_manager_methods;
+
+1;
--- /dev/null
+# This file has been auto-generated. Do not modify it; it will be overwritten
+# by rose_auto_create_model.pl automatically.
+package SL::DB::CustomDataExportQuery;
+
+use strict;
+
+use parent qw(SL::DB::Object);
+
+__PACKAGE__->meta->table('custom_data_export_queries');
+
+__PACKAGE__->meta->columns(
+ access_right => { type => 'text' },
+ description => { type => 'text', not_null => 1 },
+ id => { type => 'serial', not_null => 1 },
+ itime => { type => 'timestamp', default => 'now()', not_null => 1 },
+ mtime => { type => 'timestamp', default => 'now()', not_null => 1 },
+ name => { type => 'text', not_null => 1 },
+ sql_query => { type => 'text', not_null => 1 },
+);
+
+__PACKAGE__->meta->primary_key_columns([ 'id' ]);
+
+__PACKAGE__->meta->allow_inline_column_values(1);
+
+1;
+;
--- /dev/null
+# This file has been auto-generated. Do not modify it; it will be overwritten
+# by rose_auto_create_model.pl automatically.
+package SL::DB::CustomDataExportQueryParameter;
+
+use strict;
+
+use parent qw(SL::DB::Object);
+
+__PACKAGE__->meta->table('custom_data_export_query_parameters');
+
+__PACKAGE__->meta->columns(
+ description => { type => 'text' },
+ id => { type => 'serial', not_null => 1 },
+ itime => { type => 'timestamp', default => 'now()', not_null => 1 },
+ mtime => { type => 'timestamp', default => 'now()', not_null => 1 },
+ name => { type => 'text', not_null => 1 },
+ parameter_type => { type => 'enum', check_in => [ 'text', 'number', 'date', 'timestamp' ], db_type => 'custom_data_export_query_parameter_type_enum', not_null => 1 },
+ query_id => { type => 'integer', not_null => 1 },
+);
+
+__PACKAGE__->meta->primary_key_columns([ 'id' ]);
+
+__PACKAGE__->meta->allow_inline_column_values(1);
+
+__PACKAGE__->meta->foreign_keys(
+ query => {
+ class => 'SL::DB::CustomDataExportQuery',
+ key_columns => { query_id => 'id' },
+ },
+);
+
+1;
+;
'Add booking group' => 'Buchungsgruppe erfassen',
'Add business' => 'Kunden-/Lieferantentyp hinzufügen',
'Add complexity' => 'Komplexitätsgrad hinzufügen',
+ 'Add custom data export query' => 'Benutzerdefinierte Datenexport-Abfrage erfassen',
'Add custom variable' => 'Benutzerdefinierte Variable erfassen',
'Add department' => 'Abteilung hinzufügen',
'Add empty line (csv_import)' => 'Leere Zeile einfügen',
'Available Prices' => 'Mögliche Preise',
'Available identity fields' => 'Verfügbare Felder',
'Available qty' => 'Lagerbestand',
+ 'Available to all users' => 'Für alle BenutzerInnen verfügbar',
'BALANCE SHEET' => 'BILANZ',
'BB Balance' => 'Saldo Bank',
'BIC' => 'BIC',
'Currently #1 delivery orders can be converted into invoices and printed.' => 'Momentan können #1 Lieferscheine in Rechnungen umgewandelt werden.',
'Custom CSV format' => 'Eigenes CSV-Format',
'Custom Variables' => 'Benutzerdefinierte Variablen',
+ 'Custom data export' => 'Benutzerdefinierter Datenexport',
'Custom shipto' => 'Individuelle Lieferadresse',
'Custom variables for module' => 'Benutzerdefinierte Variablen für Modul',
'Customer' => 'Kunde',
'Description (translation for #1)' => 'Beschreibung (Übersetzung für #1)',
'Description missing!' => 'Beschreibung fehlt.',
'Description of #1' => 'Beschreibung von #1',
+ 'Design custom data export queries' => 'Benutzerdefinierte Datenexport-Abfragen designen',
'Destination BIC' => 'Ziel-BIC',
'Destination IBAN' => 'Ziel-IBAN',
'Destination bin' => 'Ziellagerplatz',
'Edit booking group' => 'Buchungsgruppe bearbeiten',
'Edit business' => 'Kunden-/Lieferantentyp bearbeiten',
'Edit complexity' => 'Komplexitätsgrad bearbeiten',
+ 'Edit custom data export query' => 'Benutzerdefinierte Datenexport-Abfrage bearbeiten',
'Edit custom shipto' => 'Individuelle Lieferadresse bearbeiten',
'Edit custom variable' => 'Benutzerdefinierte Variable bearbeiten',
'Edit delivery term' => 'Lieferbedingungen bearbeiten',
'Exchangerate for payment missing!' => 'Es fehlt der Wechselkurs für die Bezahlung!',
'Exchangerate missing!' => 'Es fehlt der Wechselkurs!',
'Execute' => 'Ausführen',
+ 'Execute a custom data export query' => 'Benutzerdefinierte Datenexport-Abfrage ausführen',
+ 'Execute custom data export \'#1\'' => 'Benutzerdefinierter Datenexport »#1« ausführen',
'Executed' => 'Ausgeführt',
'Execution date' => 'Ausführungsdatum',
'Execution date from' => 'Ausführungsdatum von',
'No clients have been created yet.' => 'Es wurden noch keine Mandanten angelegt.',
'No contact selected to delete' => 'Keine Ansprechperson zum Löschen ausgewählt',
'No contra account selected!' => 'Kein Gegenkonto ausgewählt!',
+ 'No custom data exports have been created yet.' => 'Es wurden noch keine benutzerdefinierten Datenexporte angelegt.',
'No customer has been selected yet.' => 'Es wurde noch kein Kunde ausgewählt.',
'No data was found.' => 'Es wurden keine Daten gefunden.',
'No default currency' => 'Keine Standardwährung',
'Not done yet' => 'Noch nicht fertig',
'Not obsolete' => 'Gültig',
'Note' => 'Hinweis',
+ 'Note that parameter names must not be quoted.' => 'Beachten Sie, dass Parameternamen nicht in Anführungszeichen stehen dürfen.',
'Note: Taxkeys must have a "valid from" date, and will not behave correctly without.' => 'Hinweis: Steuerschlüssel sind fehlerhaft ohne "Gültig ab" Datum',
'Note: the object is already in use. Therefore some values cannot be changed.' => 'Anmerkung: das Objekt ist bereits in Benutzung. Einige Werte können daher nicht geändert werden.',
'Notes' => 'Bemerkungen',
'On' => 'An',
'On Hand' => 'Auf Lager',
'On Order' => 'Ist bestellt',
+ 'On the next page the type of all variables can be set.' => 'Auf der folgenden Seite können die Typen aller Variablen gesetzt werden.',
'One OB-transaction' => 'Eine EB-Buchung',
'One SB-transaction' => 'Eine SB-Buchung',
'One of the columns "qty" or "target_qty" must be given. If "target_qty" is given, the quantity to transfer for each transfer will be calculate, so that the quantity for this part, warehouse and bin will result in the given "target_qty" after each transfer.' => 'Eine der Spalten "qty" oder "target_qty" muss angegeben werden. Wird "target_qty" angegeben, so wird die zu bewegende Menge für jede Lagerbewegung so berechnet, dass die Lagermenge für diesen Artikel, Lager und Lagerplatz nach jeder Lagerbewegung der angegebenen Zielmenge entspricht.',
'Quarter' => 'Quartal',
'Quarterly' => 'quartalsweise',
'Query Type' => 'Art der Abfrage',
+ 'Query parameters' => 'Abfrageparameter',
'Queue' => 'Warteschlange',
'Quick Search' => 'Schnellsuche',
'Quick Searches that will be shown in the header in this client' => 'Schnellsuchen, die in der Kopfzeile in diesem Mandanten gezeigt werden sollen',
'Requests for Quotation' => 'Preisanfragen',
'Require a transaction description in purchase and sales records' => 'Vorgangsbezeichnung in Einkaufs- und Verkaufsbelegen erzwingen',
'Require stock out to consider a delivery order position delivered?' => 'Muss eine Lieferscheinposition ausgelagert sein um als geliefert zu gelten?',
+ 'Required access right' => 'Benötigtes Zugriffsrecht',
'Required by' => 'Lieferdatum',
'Requirement Spec Status' => 'Pflichtenheftstatus',
'Requirement Spec Statuses' => 'Pflichtenheftstatus',
'SEPA message ID' => 'SEPA-Nachrichten-ID',
'SEPA message IDs' => 'SEPA-Nachrichten-IDs',
'SEPA strings' => 'SEPA-Überweisungen',
+ 'SQL query' => 'SQL-Abfrage',
'SWIFT MT940 format' => 'SWIFT-MT940-Format',
'Saldo' => 'Saldo',
'Saldo Credit' => 'Saldo Haben',
'Status' => 'Status',
'Status Shoptransfer' => 'Status Shoptransfer',
'Status Shopupload' => 'Status Shopupload',
+ 'Step #1/#2' => 'Schritt #1/#2',
'Step 1 -- limit number of delivery orders to process' => 'Schritt 1 -- Anzahl zu verarbeitender Lieferscheine begrenzen',
'Step 2' => 'Schritt 2',
'Step 2 -- Watch status' => 'Schritt 2 -- Status beobachten',
'Templates' => 'Vorlagen',
'Terms missing in row ' => '+Tage fehlen in Zeile ',
'Test database connectivity' => 'Datenbankverbindung testen',
+ 'Text' => 'Text',
'Text block actions' => 'Textblockaktionen',
'Text block picture actions' => 'Aktionen für Textblockbilder',
'Text blocks' => 'Textblöcke',
'The PDF has been printed' => 'Das PDF-Dokument wurde gedruckt.',
'The SEPA export has been created.' => 'Der SEPA-Export wurde erstellt',
'The SEPA strings have been saved.' => 'Die bei SEPA-Überweisungen verwendeten Begriffe wurden gespeichert.',
+ 'The SQL query can be parameterized with variables named as follows: <%name%>.' => 'Die SQL-Abfrage kann mittels Variablen wie folgt parametrisiert werden: <%Variablenname%>.',
+ 'The SQL query does not contain any parameter that need to be configured.' => 'Die SQL-Abfrage enthält keine Parameter, die angegeben werden müssten.',
'The URL is missing.' => 'URL fehlt',
'The WebDAV feature has been used.' => 'Das WebDAV-Feature wurde benutzt.',
'The abbreviation is missing.' => 'Abkürzung fehlt',
'The contact person attribute "birthday" is converted from a free-form text field into a date field.' => 'Das Kontaktpersonenfeld "Geburtstag" wird von einem freien Textfeld auf ein Datumsfeld umgestellt.',
'The creation of the authentication database failed:' => 'Das Anlegen der Authentifizierungsdatenbank schlug fehl:',
'The credentials (username & password) for connecting database are wrong.' => 'Die Daten (Benutzername & Passwort) für das Login zur Datenbank sind falsch.',
+ 'The custom data export has been deleted.' => 'Der benutzerdefinierte Datenexport wurde gelöscht.',
+ 'The custom data export has been saved.' => 'Der benutzerdefinierte Datenexport wurde gespeichert.',
'The custom variable has been created.' => 'Die benutzerdefinierte Variable wurde erfasst.',
'The custom variable has been deleted.' => 'Die benutzerdefinierte Variable wurde gelöscht.',
'The custom variable has been saved.' => 'Die benutzerdefinierte Variable wurde gespeichert.',
'The project link has been updated.' => 'Die Projektverknüpfung wurde aktualisiert.',
'The project number is already in use.' => 'Die Projektnummer wird bereits verwendet.',
'The project number is missing.' => 'Die Projektnummer fehlt.',
+ 'The query did not return any data.' => 'Die Abfrage lieferte keine Daten',
'The receivables chart isn\'t a valid chart.' => 'Das Forderungskonto ist kein gültiges Konto',
'The recipient, subject or body is missing.' => 'Der Empfäger, der Betreff oder der Text ist leer.',
'The record template \'#1\' has been loaded.' => 'Die Belegvorlage »#1« wurde geladen.',
'You cannot create an invoice for delivery orders from different vendors.' => 'Sie können keine Rechnung aus Lieferscheinen von verschiedenen Lieferanten erstellen.',
'You cannot modify individual assigments from additional articles to line items.' => 'Eine individuelle Zuordnung der zusätzlichen Artikel zu Positionen kann nicht vorgenommen werden.',
'You cannot paste function blocks or sub function blocks if there is no section.' => 'Sie können keine Funktionsblöcke oder Unterfunktionsblöcke einfügen, wenn es noch keinen Abschnitt gibt.',
+ 'You do not have access to any custom data export.' => 'Sie haben auf keine benutzerdefinierten Datenexporte Zugriff.',
'You do not have permission to access this entry.' => 'Sie verfügen nicht über die Berechtigung, auf diesen Eintrag zuzugreifen.',
'You do not have the permissions to access this function.' => 'Sie verfügen nicht über die notwendigen Rechte, um auf diese Funktion zuzugreifen.',
'You don\'t have the rights to edit this customer.' => 'Sie verfügen nicht über die erforderlichen Rechte, um diesen Kunden zu bearbeiten.',
--- /dev/null
+---
+- parent: reports
+ id: custom_data_export
+ name: Custom data export
+ order: 9000
+ params:
+ action: CustomDataExport/list
+- parent: system
+ id: custom_data_export_designer
+ name: Custom data export
+ order: 2250
+ access: custom_data_export_designer
+ params:
+ action: CustomDataExportDesigner/list
--- /dev/null
+# @tag: custom_data_export_rights
+# @description: Rechte für benutzerdefinierten Datenexport
+# @depends: release_3_5_0
+package SL::DBUpgrade2::Auth::custom_data_export_rights;
+
+use strict;
+use utf8;
+
+use parent qw(SL::DBUpgrade2::Base);
+
+sub run {
+ my ($self) = @_;
+ my $right = 'custom_data_export_designer';
+
+ $self->db_query("INSERT INTO auth.master_rights (position, name, description) VALUES (4275, '${right}', 'Custom data export')");
+
+ my $groups = $::auth->read_groups;
+
+ foreach my $group (grep { $_->{rights}->{admin} } values %{$groups}) {
+ $group->{rights}->{$right} = 1;
+ $::auth->save_group($group);
+ }
+
+ return 1;
+}
+
+1;
--- /dev/null
+-- @tag: custom_data_export
+-- @description: Benutzerdefinierter Datenexport
+-- @depends: release_3_5_0
+CREATE TYPE custom_data_export_query_parameter_type_enum AS ENUM ('text', 'number', 'date', 'timestamp');
+
+CREATE TABLE custom_data_export_queries (
+ id SERIAL,
+ name TEXT NOT NULL,
+ description TEXT NOT NULL,
+ sql_query TEXT NOT NULL,
+ access_right TEXT,
+ itime TIMESTAMP NOT NULL DEFAULT now(),
+ mtime TIMESTAMP NOT NULL DEFAULT now(),
+
+ PRIMARY KEY (id)
+);
+
+CREATE TABLE custom_data_export_query_parameters (
+ id SERIAL,
+ query_id INTEGER NOT NULL,
+ name TEXT NOT NULL,
+ description TEXT,
+ parameter_type custom_data_export_query_parameter_type_enum NOT NULL,
+ itime TIMESTAMP NOT NULL DEFAULT now(),
+ mtime TIMESTAMP NOT NULL DEFAULT now(),
+
+ PRIMARY KEY (id),
+ FOREIGN KEY (query_id) REFERENCES custom_data_export_queries (id) ON DELETE CASCADE
+);
+
+CREATE TRIGGER mtime_custom_data_export_queries
+BEFORE UPDATE ON custom_data_export_queries
+FOR EACH ROW EXECUTE PROCEDURE set_mtime();
+
+CREATE TRIGGER mtime_custom_data_export_query_parameters
+BEFORE UPDATE ON custom_data_export_query_parameters
+FOR EACH ROW EXECUTE PROCEDURE set_mtime();
--- /dev/null
+[% USE HTML %][% USE L %][% USE LxERP %]
+
+<h1>[% FORM.title %]</h1>
+
+[%- INCLUDE 'common/flash.html' %]
+
+<p>
+ [% LxERP.t8("The query did not return any data.") %]
+</p>
--- /dev/null
+[% USE HTML %][% USE L %][% USE LxERP %]
+
+<h1>[% FORM.title %]</h1>
+
+[%- INCLUDE 'common/flash.html' %]
+
+<form method="post" action="controller.pl" id="form">
+ [% L.hidden_tag("id", SELF.query.id) %]
+ [% L.hidden_tag("format", "csv") %]
+
+ [% IF !SELF.parameters.size %]
+ <p>
+ [% LxERP.t8("The SQL query does not contain any parameter that need to be configured.") %]
+ </p>
+
+ [% ELSE %]
+
+ <table>
+ <thead>
+ <tr class="listheading">
+ <th>[% LxERP.t8("Variable Name") %]</th>
+ <th>[% LxERP.t8("Type") %]</th>
+ <th>[% LxERP.t8("Value") %]</th>
+ <th>[% LxERP.t8("Description") %]</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ [% FOREACH parameter = SELF.parameters %]
+ <tr class="listrow">
+ <td>
+ [% HTML.escape(parameter.name) %]
+ </td>
+
+ [% IF parameter.parameter_type == "number" %]
+ <td>[% LxERP.t8("Number") %]</td>
+ <td>[% L.input_tag("parameters." _ parameter.name, "", style="width: 300px", "data-validate"="required") %]</td>
+
+ [% ELSIF parameter.parameter_type == "date" %]
+ <td>[% LxERP.t8("Date") %]</td>
+ <td>[% L.date_tag("parameters." _ parameter.name, SELF.today.to_kivitendo, style="width: 300px", "data-validate"="required") %]</td>
+
+ [% ELSE %]
+ <td>[% LxERP.t8("Text") %]</td>
+ <td>[% L.input_tag("parameters." _ parameter.name, "", style="width: 300px", "data-validate"="required") %]</td>
+ [% END %]
+
+ <td>[% HTML.escape(parameter.description) %]</td>
+ </tr>
+ [% END %]
+ </tbody>
+ [% END %]
+</form>
--- /dev/null
+[% USE HTML %][% USE L %][% USE LxERP %]
+
+<h1>[% FORM.title %]</h1>
+
+[%- INCLUDE 'common/flash.html' %]
+
+[% IF !SELF.queries.size %]
+ <p>
+ [%- LxERP.t8("You do not have access to any custom data export.") %]
+ </p>
+
+[%- ELSE %]
+ <table width="100%">
+ <thead>
+ <tr class="listheading">
+ <th>[% LxERP.t8("Name") %]</th>
+ <th>[% LxERP.t8("Description") %]</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ [%- FOREACH query = SELF.queries %]
+ <tr class="listrow">
+ <td>[% L.link(SELF.url_for(action="export", id=query.id), query.name) %]</td>
+ <td>[% IF query.description %][% L.link(SELF.url_for(action="export", id=query.id), query.description) %][% END %]</td>
+ </tr>
+ [%- END %]
+ </tbody>
+ </table>
+[%- END %]
--- /dev/null
+[% USE HTML %][% USE L %][% USE LxERP %]
+
+<h1>[% FORM.title %] — [% LxERP.t8("Step #1/#2", 1, 2) %] — [% LxERP.t8("Basic Data") %]</h1>
+
+[%- INCLUDE 'common/flash.html' %]
+
+<form method="post" action="controller.pl" id="form">
+ [% L.hidden_tag("id", SELF.query.id) %]
+
+ <table>
+ <tr>
+ <th align="right">[%- LxERP.t8("Name") %]</th>
+ <td>[% L.input_tag("query.name", SELF.query.name, style="width: 800px", "data-validate"="required") %]</td>
+ </tr>
+
+ <tr>
+ <th align="right">[%- LxERP.t8("Required access right") %]</th>
+ <td>[% L.select_tag("query.access_right", SELF.access_rights, default=SELF.query.access_right, style="width: 800px") %]</td>
+ </tr>
+
+ <tr>
+ <th valign="top" align="right">[%- LxERP.t8("Description") %]</th>
+ <td valign="top">[% L.textarea_tag("query.description", SELF.query.description, rows=5, style="width: 800px") %]</td>
+ </tr>
+
+ <tr>
+ <th valign="top" align="right">[%- LxERP.t8("SQL query") %]</th>
+ <td valign="top">[% L.textarea_tag("query.sql_query", SELF.query.sql_query, rows=20, style="width: 800px", "data-validate"="required") %]</td>
+ </tr>
+ </table>
+</form>
+
+<p>
+ [% LxERP.t8("The SQL query can be parameterized with variables named as follows: <%name%>.") %]
+ [% LxERP.t8("On the next page the type of all variables can be set.") %]
+ [% LxERP.t8("Note that parameter names must not be quoted.") %]
+ [% LxERP.t8("Example") %]:
+</p>
+
+<pre>
+SELECT extract(YEAR FROM oe.transdate) AS "Jahr", SUM(oe.amount) AS "Angebotssumme"
+FROM oe
+LEFT JOIN employee ON (oe.employee_id = employee.id)
+WHERE (oe.customer_id IS NOT NULL)
+ AND COALESCE(oe.quotation, FALSE)
+ AND (employee.login = <%Benutzer-Login%>)
+GROUP BY "Jahr"
+ORDER BY "Jahr"
+</pre>
--- /dev/null
+[% USE HTML %][% USE L %][% USE LxERP %]
+
+<h1>[% FORM.title %] — [% LxERP.t8("Step #1/#2", 2, 2) %] — [% LxERP.t8("Query parameters") %]</h1>
+
+[%- INCLUDE 'common/flash.html' %]
+
+<form method="post" action="controller.pl" id="form">
+ [% L.hidden_tag("id", SELF.query.id) %]
+ [% L.hidden_tag("query.name", SELF.query.name) %]
+ [% L.hidden_tag("query.access_right", SELF.query.access_right) %]
+ [% L.hidden_tag("query.description", SELF.query.description) %]
+ [% L.hidden_tag("query.sql_query", SELF.query.sql_query) %]
+
+ [% IF !PARAMETERS.size %]
+ <p>
+ [% LxERP.t8("The SQL query does not contain any parameter that need to be configured.") %]
+ </p>
+
+ [% ELSE %]
+
+ <table>
+ <thead>
+ <tr class="listheading">
+ <th>[% LxERP.t8("Variable Name") %]</th>
+ <th>[% LxERP.t8("Type") %]</th>
+ <th>[% LxERP.t8("Description") %]</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ [% FOREACH parameter = PARAMETERS %]
+ <tr class="listrow">
+ <td>
+ [% L.hidden_tag("parameters[+].name", parameter.name) %]
+ [% HTML.escape(parameter.name) %]
+ </td>
+ <td>
+ [% L.select_tag("parameters[].parameter_type", [ [ "text", LxERP.t8("Text") ], [ "number", LxERP.t8("Number") ], [ "date", LxERP.t8("Date") ] ], default=parameter.parameter_type) %]
+ </td>
+ <td>[% L.input_tag("parameters[].description", parameter.description, size=100) %]</td>
+ </tr>
+ [% END %]
+ </tbody>
+ [% END %]
+</form>
--- /dev/null
+[% USE HTML %][% USE L %][% USE LxERP %]
+
+<h1>[% FORM.title %]</h1>
+
+[%- INCLUDE 'common/flash.html' %]
+
+[% IF !SELF.queries.size %]
+ <p>
+ [%- LxERP.t8("No custom data exports have been created yet.") %]
+ </p>
+
+[%- ELSE %]
+ <table width="100%">
+ <thead>
+ <tr class="listheading">
+ <th>[% LxERP.t8("Name") %]</th>
+ <th>[% LxERP.t8("Description") %]</th>
+ </tr>
+ </thead>
+
+ <tbody>
+ [%- FOREACH query = SELF.queries %]
+ <tr class="listrow">
+ <td>[% L.link(SELF.url_for(action="edit", id=query.id), query.name) %]</td>
+ <td>[% IF query.description %][% L.link(SELF.url_for(action="edit", id=query.id), query.description) %][% END %]</td>
+ </tr>
+ [%- END %]
+ </tbody>
+ </table>
+[%- END %]
projects / kivitendo-erp.git / commitdiff