/ - Diff - projekt kivitendo - wissen

     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,
       );
+    }
 ;

     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;
+    }
 ;

     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;
+    }
 ;

     # 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;
 ;

     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',

     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' });
+    }
 ;

     # 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;
 ;

     # 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);
 ;
+    ;

     # 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' },
       },
     );
 ;
+    ;

       '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.',

     ---
     - 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

     # @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;
+    }
 ;

     -- @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();

     [% 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>

     [% 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>

     [% 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 %]

     [% 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 &quot;Jahr&quot;, SUM(oe.amount) AS &quot;Angebotssumme&quot;
     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 = &lt;%Benutzer-Login%&gt;)
     GROUP BY &quot;Jahr&quot;
     ORDER BY &quot;Jahr&quot;
     </pre>

     [% 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>

     [% 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 %]

Projekt

Allgemein

Profil

projekt kivitendo

Revision e713c314

Von Moritz Bunkus vor etwa 7 Jahren hinzugefügt