Das Erstellen von Schluss- und Eröffnungsbuchungen (mit Saldovortrag auf 9000-Konten)
wird erleichtern bzw. automatisieren.
- Neuer Menüpunkt "Finanzbuchhaltung -> SB/EB buchen"
- Buchungsdatum:
SB: dem 31.12. des Vorjahres
EB: dem 01.01. des aktuellen Jahre
- Referenz und Beschreibung
- Auswahl eines 9000 Kontos
- Danach Liste der unausgeglichenen Konten, per Checkbox auswählbar.
- Iteratives Erzeugen der SB / EB Buchungen
- Start der Saldoberechnung abhängig von Mandantenkonfig Einstellungen "get_balance_startdate_method" und "closedto"
- Erweiterung der zweiten Seite (SB/EB buchen)
1. Zusätzliche Spalte "Summe SB-Buchungen" welche die Summe aller SB-Buchungen im betroffenen Zeitraum anzeigt.
2. Zusätzliche Spalte "Summe EB-Buchungen" welche die Summe aller EB-Buchungen im Zeitraum des nächsten Jahres anzeigt.
3. Die Anzeige "Salden von $datum bis $datum" als gemeinsame Spaltenüberschrift "Zeitraum: $datum - $datum" für die Spalten "Soll", "Haben", "Summe SB-Buchungen"
4. Eine ähnliche zweite Überschrift "Zeitraum: $datum2 - $datum2" für die Spalte "Summe EB-Buchungen", wobei $datum2 hier das "folgejahr" darstellt.
- Nun auch S und H bei Summenspalten
- Test eingebaut
--- /dev/null
+package SL::Controller::YearlyTransactions;
+
+use strict;
+
+use parent qw(SL::Controller::Base);
+
+use DateTime;
+use SL::Locale::String qw(t8);
+use SL::ReportGenerator;
+use SL::Helper::Flash;
+use SL::DBUtils;
+
+use SL::DB::Chart;
+use SL::DB::GLTransaction;
+use SL::DB::AccTransaction;
+use SL::DB::Helper::AccountingPeriod qw(get_balance_starting_date);
+
+use Rose::Object::MakeMethods::Generic (
+ 'scalar --get_set_init' => [ qw(charts charts9000 cbob_chart cb_date cb_startdate ob_date cb_reference ob_reference cb_description ob_description) ],
+);
+
+__PACKAGE__->run_before('check_auth');
+
+sub action_filter {
+ my ($self) = @_;
+
+ $self->ob_date(DateTime->today->truncate(to => 'year')) if !$self->ob_date;
+ $self->cb_date(DateTime->today->truncate(to => 'year')->add(days => -1)) if !$self->cb_date;
+ $self->ob_reference(t8('OB Transaction')) if !$self->ob_reference;
+ $self->cb_reference(t8('CB Transaction')) if !$self->cb_reference;
+ $self->ob_description(t8('OB Transaction')) if !$self->ob_description;
+ $self->cb_description(t8('CB Transaction')) if !$self->cb_description;
+ $self->render('gl/yearly_filter',
+ title => t8('CB/OB Transactions'),
+ make_title_of_chart => sub { $_[0]->accno.' '.$_[0]->description }
+ );
+
+}
+
+sub action_list {
+ my ($self) = @_;
+ $main::lxdebug->enter_sub();
+
+ my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
+
+ $self->prepare_report($report);
+
+ $report->set_options(
+ output_format => 'HTML',
+ raw_top_info_text => $::form->parse_html_template('gl/yearly_top', { SELF => $self }),
+ raw_bottom_info_text => $::form->parse_html_template('gl/yearly_bottom', { SELF => $self }),
+ allow_pdf_export => 0,
+ allow_csv_export => 0,
+ title => $::locale->text('CB/OB Transactions'),
+ );
+ $report->generate_with_headers();
+ $main::lxdebug->leave_sub();
+}
+
+sub action_generate {
+ my ($self) = @_;
+
+ my $cnt = $self->make_booking();
+
+ flash('info', $::locale->text('#1 CB transactions and #1 OB transactions generated.',$cnt)) if $cnt > 0;
+
+ $self->action_list;
+}
+
+sub check_auth {
+ $::auth->assert('general_ledger');
+}
+
+#
+# helpers
+#
+
+sub make_booking {
+ my ($self) = @_;
+ $main::lxdebug->enter_sub();
+ my @ids = map { $::form->{"trans_id_$_"} } grep { $::form->{"multi_id_$_"} } (1..$::form->{rowcount});
+ my $cnt = 0;
+ $main::lxdebug->message(LXDebug->DEBUG2(),"generate for ".$::form->{cbob_chart}." # ".scalar(@ids)." charts");
+ if (scalar(@ids) && $::form->{cbob_chart}) {
+ my $carryoverchart = SL::DB::Manager::Chart->get_first( query => [ id => $::form->{cbob_chart} ] );
+ my $charts = SL::DB::Manager::Chart->get_all( query => [ id => \@ids ] );
+ foreach my $chart (@{ $charts }) {
+ $main::lxdebug->message(LXDebug->DEBUG2(),"chart_id=".$chart->id." accno=".$chart->accno);
+ my $balance = $self->get_balance($chart);
+ if ( $balance != 0 ) {
+ # SB
+ $self->gl_booking($balance,$self->cb_date,$::form->{cb_reference},$::form->{cb_description},$chart,$carryoverchart,0,1);
+ # EB
+ $self->gl_booking($balance,$self->ob_date,$::form->{ob_reference},$::form->{ob_description},$carryoverchart,$chart,1,0);
+ $cnt++;
+ }
+ }
+ }
+ $main::lxdebug->leave_sub();
+ return $cnt;
+}
+
+
+sub prepare_report {
+ my ($self,$report) = @_;
+ $main::lxdebug->enter_sub();
+ my $idx = 1;
+ my $cgi = $::request->{cgi};
+
+ my %column_defs = (
+ 'ids' => { 'text' => '<input type="checkbox" id="multi_all" value="1">', 'align' => 'center' },
+ 'chart' => { 'text' => $::locale->text('Account'), },
+ 'description' => { 'text' => $::locale->text('Description'), },
+ 'saldo' => { 'text' => $::locale->text('Saldo'), 'align' => 'right'},
+ 'sum_cb' => { 'text' => $::locale->text('Sum CB Transactions'), 'align' => 'right'}, ##close == Schluss
+ 'sum_ob' => { 'text' => $::locale->text('Sum OB Transactions'), 'align' => 'right'}, ##open == Eingang
+ );
+ my @columns = qw(ids chart description saldo sum_cb sum_ob);
+ map { $column_defs{$_}->{visible} = 1 } @columns;
+ my $ob_next_date = $::locale->parse_date_to_object($self->ob_date)->add(years => 1)->add(days => -1)->to_kivitendo;
+
+ $self->cb_startdate($self->get_balance_starting_date($self->cb_date));
+
+ my @custom_headers = ();
+ # Zeile 1:
+ push @custom_headers, [
+ { 'text' => ' ', 'colspan' => 3 },
+ { 'text' => $::locale->text("Timerange")."<br />".$self->cb_startdate." - ".$self->cb_date, 'colspan' => 2, 'align' => 'center'},
+ { 'text' => $::locale->text("Timerange")."<br />".$self->ob_date." - ".$ob_next_date, 'align' => 'center'},
+ ];
+
+ # Zeile 2:
+ my @line_2 = ();
+ map { push @line_2 , $column_defs{$_} } grep { $column_defs{$_}->{visible} } @columns;
+ push @custom_headers, [ @line_2 ];
+
+ $report->set_custom_headers(@custom_headers);
+ $report->set_columns(%column_defs);
+ $report->set_column_order(@columns);
+
+ my $chart9actual = SL::DB::Manager::Chart->get_first( query => [ id => $self->cbob_chart ] );
+ $self->{cbob_chartaccno} = $chart9actual->accno.' '.$chart9actual->description;
+
+ foreach my $chart (@{ $self->charts }) {
+ my $balance = $self->get_balance($chart);
+ if ( $balance != 0 ) {
+ my $chart_id = $chart->id;
+ my $row = { map { $_ => { 'data' => '' } } @columns };
+ $row->{ids} = {
+ 'raw_data' => $cgi->hidden('-name' => "trans_id_${idx}", '-value' => $chart_id)
+ . $cgi->checkbox('-name' => "multi_id_${idx}",' id' => "multi_id_id_".$chart_id, '-value' => 1, '-label' => ''),
+ 'valign' => 'center',
+ 'align' => 'center',
+ };
+ $row->{chart}->{data} = $chart->accno;
+ $row->{description}->{data} = $chart->description;
+ if ( $balance > 0 ) {
+ $row->{saldo}->{data} = $::form->format_amount(\%::myconfig, $balance, 2)." H";
+ } elsif ( $balance < 0 ) {
+ $row->{saldo}->{data} = $::form->format_amount(\%::myconfig,-$balance, 2)." S";
+ } else {
+ $row->{saldo}->{data} = $::form->format_amount(\%::myconfig,0, 2)." ";
+ }
+ my $sum_cb = 0;
+ foreach my $acc ( @{ SL::DB::Manager::AccTransaction->get_all(where => [ chart_id => $chart->id, cb_transaction => 't',
+ transdate => { ge => $self->cb_startdate},
+ transdate => { le => $self->cb_date }]) }) {
+ $sum_cb += $acc->amount;
+ }
+ my $sum_ob = 0;
+ foreach my $acc ( @{ SL::DB::Manager::AccTransaction->get_all(where => [ chart_id => $chart->id, ob_transaction => 't',
+ transdate => { ge => $self->ob_date},
+ transdate => { le => $ob_next_date }]) }) {
+ $sum_ob += $acc->amount;
+ }
+ if ( $sum_cb > 0 ) {
+ $row->{sum_cb}->{data} = $::form->format_amount(\%::myconfig, $sum_cb, 2)." H";
+ } elsif ( $sum_cb < 0 ) {
+ $row->{sum_cb}->{data} = $::form->format_amount(\%::myconfig,-$sum_cb, 2)." S";
+ } else {
+ $row->{sum_cb}->{data} = $::form->format_amount(\%::myconfig,0, 2)." ";
+ }
+ if ( $sum_ob > 0 ) {
+ $row->{sum_ob}->{data} = $::form->format_amount(\%::myconfig, $sum_ob, 2)." H";
+ } elsif ( $sum_ob < 0 ) {
+ $row->{sum_ob}->{data} = $::form->format_amount(\%::myconfig,-$sum_ob, 2)." S";
+ } else {
+ $row->{sum_ob}->{data} = $::form->format_amount(\%::myconfig,0, 2)." ";
+ }
+ $report->add_data($row);
+ }
+ $idx++;
+ }
+
+ $self->{row_count} = $idx;
+ $main::lxdebug->leave_sub();
+}
+
+sub get_balance {
+ $main::lxdebug->enter_sub();
+ my ($self,$chart) = @_;
+
+## eigene Abfrage da SL:DB:Chart->get_balance keine cb_transactions mitzählt
+## Alternative in Chart cb_transaction abfrage per neuem Parameter 'with_cb' disablen:
+# my %balance_params = ( fromdate => $self->startdate,
+# todate => $self->cb_date,
+# accounting_method => 'accrual',
+# with_cb => 1, ## in Chart cb_transaction abfrage disablen
+# );
+# return $chart->get_balance(%balance_params);
+
+ $main::lxdebug->message(LXDebug->DEBUG2(),"get_balance from=".$self->cb_startdate." to=".$self->cb_date);
+ my $query = qq|SELECT SUM(amount) AS sum FROM acc_trans WHERE chart_id = ? | .
+ qq| AND transdate >= ? AND transdate <= ? |;
+ my @query_args = ( $chart->id, $self->cb_startdate, $self->cb_date);
+ my ($balance) = selectfirst_array_query($::form, $chart->db->dbh, $query, @query_args);
+
+ $main::lxdebug->leave_sub();
+ return 0 unless $balance != 0;
+ return $balance;
+}
+
+sub gl_booking {
+ my ($self, $amount, $transdate, $reference, $description, $konto, $gegenkonto, $ob, $cb) = @_;
+ $::form->get_employee();
+ my $employee_id = $::form->{employee_id};
+ $main::lxdebug->message(LXDebug->DEBUG2(),"employee_id=".$employee_id." ob=".$ob." cb=".$cb);
+ my $gl_entry = SL::DB::GLTransaction->new();
+ $gl_entry->assign_attributes(
+ employee_id => $employee_id,
+ transdate => $transdate,
+ reference => $reference,
+ description => $description,
+ ob_transaction => $ob,
+ cb_transaction => $cb,
+ );
+ $gl_entry->save;
+ my $kto_trans1 = SL::DB::AccTransaction->new();
+ $kto_trans1->assign_attributes(
+ trans_id => $gl_entry->id,
+ transdate => $transdate,
+ ob_transaction => $ob,
+ cb_transaction => $cb,
+ chart_id => $gegenkonto->id,
+ chart_link => $konto->link,
+ tax_id => 0,
+ taxkey => 0,
+ amount => $amount,
+ );
+ $kto_trans1->save;
+ my $kto_trans2 = SL::DB::AccTransaction->new();
+ $kto_trans2->assign_attributes(
+ trans_id => $gl_entry->id,
+ transdate => $transdate,
+ ob_transaction => $ob,
+ cb_transaction => $cb,
+ chart_id => $konto->id,
+ chart_link => $konto->link,
+ tax_id => 0,
+ taxkey => 0,
+ amount => -$amount,
+ );
+ $kto_trans2->save;
+}
+
+sub init_cbob_chart { $::form->{cbob_chart} }
+sub init_ob_date { $::form->{ob_date} }
+sub init_ob_reference { $::form->{ob_reference} }
+sub init_ob_description { $::form->{ob_description} }
+sub init_cb_startdate { $::form->{cb_startdate} }
+sub init_cb_date { $::form->{cb_date} }
+sub init_cb_reference { $::form->{cb_reference} }
+sub init_cb_description { $::form->{cb_description} }
+
+sub init_charts9000 {
+ # wie geht prüfen von länge auf 4 in rose ?
+ SL::DB::Manager::Chart->get_all( query => [ \ "accno like '9%' and length(accno) = 4"] );
+ #SL::DB::Manager::Chart->get_all( query => [ accno => { like => '9%'}] );
+}
+
+sub init_charts {
+ # wie geht 'not like' in rose ?
+ SL::DB::Manager::Chart->get_all( query => [ \ "accno not like '9%'"], sort_by => 'accno ASC' );
+}
+
+1;
' Part Number missing!' => ' Artikelnummer fehlt!',
' missing!' => ' fehlt!',
'#1 (custom variable)' => '#1 (benutzerdefinierte Variable)',
+ '#1 CB transactions and #1 OB transactions generated.' => '#1 Schluss- und #1 Eröffnungsbuchungen wurden erstellt.',
'#1 MD' => '#1 PT',
'#1 additional part(s)' => '#1 zusätzliche(r) Artikel',
'#1 dunnings have been deleted' => '#1 Mahnung(en) wurden gelöscht',
'Apr' => 'Apr',
'April' => 'April',
'Ar aging on %s' => 'Offene Forderungen zum %s',
+ 'Are you sure to generate cb/ob transactions?' => 'Sollen die EB/SB Buchungen wirklich erzeugt werden?',
'Are you sure you want to delete Invoice Number' => 'Soll die Rechnung mit folgender Nummer wirklich gelöscht werden:',
'Are you sure you want to delete Transaction' => 'Buchung wirklich löschen?',
'Are you sure you want to delete this background job?' => 'Sind Sie sicher, dass Sie diesen Hintergrund-Job löschen möchten?',
'Attachment name' => 'Name des Anhangs',
'Attachments' => 'Dateianhänge',
'Attempt to call an undefined sub named \'%s\'' => 'Es wurde versucht, eine nicht definierte Unterfunktion namens \'%s\' aufzurufen.',
+ 'Attention: Here will be generated a lot of CB/OB transactions.' => 'Hiermit werden Buchungen für den Schlussbestand (SB-Buchung) und den Eröffnungsbestand (EB-Buchung) für mehrere Konten gleichzeitig gebucht.<br>In diesem Schritt wird das Datum der Buchungen sowie das Saldovortragskonto festgelegt.<br>Das Datum der SB-Buchung wird außerdem verwendet um das Saldo der Konten zu ermitteln, welche im nächsten Schritt (nach "Weiter") angezeigt werden.',
'Audit Control' => 'Bücherkontrolle',
'Aug' => 'Aug',
'August' => 'August',
'CANCELED' => 'Storniert',
'CB Transaction' => 'SB-Buchung',
'CB Transactions' => 'SB-Buchungen',
+ 'CB/OB Transactions' => 'SB/EB buchen',
'CR' => 'H',
'CSS style for pictures' => 'CSS Style für Bilder',
'CSV' => 'CSV',
'No requirement spec templates have been created yet.' => 'Es wurden noch keine Pflichtenheftvorlagen angelegt.',
'No requirement spec type has been created yet.' => 'Es wurden noch keine Pflichtenhefttypen angelegt.',
'No results.' => 'Keine Artikel',
+ 'No revert available.' => 'Dieser Vorgang kann nicht rückgängig gemacht werden, ggf. falsch erstellte Buchungen müssen einzeln manuell korrigiert werden.',
'No risks level has been created yet.' => 'Es wurden noch keine Risikograde angelegt.',
'No sections created yet' => 'Keine Abschnitte erstellt',
'No sections have been created so far.' => 'Bisher wurden noch keine Abschnitte angelegt.',
'Number pages' => 'Seiten nummerieren',
'Number variables: \'PRECISION=n\' forces numbers to be shown with exactly n decimal places.' => 'Zahlenvariablen: Mit \'PRECISION=n\' erzwingt man, dass Zahlen mit n Nachkommastellen formatiert werden.',
'OB Transaction' => 'EB-Buchung',
+ 'OB Transactions' => 'EB-Buchungen',
'Objects have been imported.' => 'Objekte wurden importiert.',
'Obsolete' => 'Ungültig',
'Oct' => 'Okt',
'On' => 'An',
'On Hand' => 'Auf Lager',
'On Order' => 'Ist bestellt',
+ '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.',
'One or more Perl modules missing' => 'Ein oder mehr Perl-Module fehlen',
'Onhand only sets the quantity in master data, not in inventory. This is only a legacy info field and will be overwritten as soon as a inventory transfer happens.' => 'Das Import-Feld Auf Lager setzt nur die Menge in den Stammdaten, nicht im Lagerbereich. Dies ist historisch gewachsen nur ein Informationsfeld was mit dem tatsächlichen Wert überschrieben wird, sobald eine wirkliche Lagerbewegung stattfindet (DB-Trigger).',
'SEPA message ID' => 'SEPA-Nachrichten-ID',
'SEPA message IDs' => 'SEPA-Nachrichten-IDs',
'SEPA strings' => 'SEPA-Überweisungen',
+ 'Saldo' => 'Saldo',
'Saldo Credit' => 'Saldo Haben',
'Saldo Debit' => 'Saldo Soll',
'Saldo neu' => 'Saldo neu',
'Select a period' => 'Bitte Zeitraum auswählen',
'Select a vendor' => 'Einen Lieferanten auswählen',
'Select all' => 'Alle auswählen',
+ 'Select charts for which the CB/OB transactions want to be posted.' => 'Wählen Sie Konten aus, zu welchen SB/EB-Buchungen erstellt werden sollen.',
'Select federal state...' => 'Bundesland auswählen...',
'Select file to upload' => 'Datei zum Hochladen auswählen',
'Select from one of the items below' => 'Wählen Sie einen der untenstehenden Einträge',
'Subtotals per quarter' => 'Zwischensummen pro Quartal',
'Such entries cannot be exported into the DATEV format and have to be fixed as well.' => 'Solche Einträge sind aber nicht DATEV-exportiertbar und müssen ebenfalls korrigiert werden.',
'Suggested invoice' => 'Rechnungsvorschlag',
+ 'Sum CB Transactions' => 'Summe SB',
'Sum Credit' => 'Summe Haben',
'Sum Debit' => 'Summe Soll',
+ 'Sum OB Transactions' => 'Summe EB',
'Sum for' => 'Summe für',
'Sum for #1' => 'Summe für #1',
'Sum for section' => 'Summe für Abschnitt',
'There was an error saving the draft' => 'Beim Speichern des Entwurfs ist ein Fehler aufgetretetn',
'There was an error saving the letter' => 'Ein Fehler ist aufgetreten. Der Brief konnte nicht gespeichert werden.',
'There was an error saving the letter draft' => 'Ein Fehler ist aufgetreten. Der Briefentwurf konnte nicht gespeichert werden.',
+ 'There will be two transactions done:' => 'Zu jedem ausgewählten Konto werden jeweils zwei Buchungen erstellt:',
'There you can let kivitendo create the basic tables for you, even in an already existing database.' => 'Dort können Sie kivitendo diese grundlegenden Tabellen erstellen lassen, selbst in einer bereits existierenden Datenbank.',
'Therefore several settings that had to be made for each user in the past have been consolidated into the client configuration.' => 'Dazu wurden gewisse Einstellungen, die vorher bei jedem Benutzer vorgenommen werden mussten, in die Konfiguration eines Mandanten verschoben.',
'Therefore the definition of "kg" with the base unit "g" and a factor of 1000 is valid while defining "g" with a base unit of "kg" and a factor of "0.001" is not.' => 'So ist die Definition von "kg" mit der Basiseinheit "g" und dem Faktor 1000 zulässig, die Definition von "g" mit der Basiseinheit "kg" und dem Faktor "0,001" hingegen nicht.',
'Time estimate' => 'Zeitschätzung',
'Time period for the analysis:' => 'Analysezeitraum:',
'Time/cost estimate actions' => 'Aktionen für Kosten-/Zeitabschätzung',
+ 'Timerange' => 'Zeitraum',
'Timestamp' => 'Uhrzeit',
'Title' => 'Titel',
'To' => 'An',
'cleared' => 'Abgeglichen',
'click here to edit cvars' => 'Klicken Sie hier, um nach benutzerdefinierten Variablen zu suchen',
'close' => 'schließen',
+ 'close chart' => 'Saldovortragskonto',
'closed' => 'geschlossen',
'companylogo_subtitle' => 'Lizenziert für',
'config/kivitendo.conf: Key "DB_config" is missing.' => 'config/kivitendo.conf: Das Schlüsselwort "DB_config" fehlt.',
'found' => 'Gefunden',
'from (time)' => 'von',
'general_ledger_list' => 'buchungsjournal',
+ 'generate cb/ob transactions for selected charts' => 'Buchungen erstellen',
'h' => 'h',
'history' => 'Historie',
'history search engine' => 'Historien Suchmaschine',
--- /dev/null
+# This is the main menu config file for user space menu entries.
+#
+# opendynamic features
+#
+---
+#
+# SB/EB-Buchungen
+#
+- parent: general_ledger
+ id: general_ledger_cbob_transactions
+ name: CB/OB Transactions
+ icon: cbob
+ order: 470
+ access: general_ledger
+ params:
+ action: YearlyTransactions/filter
--- /dev/null
+use Test::More;
+
+use strict;
+
+use lib 't';
+use utf8;
+
+use Carp;
+use Support::TestSetup;
+use Test::Exception;
+use List::Util qw(sum);
+
+use SL::DB::Buchungsgruppe;
+use SL::DB::Currency;
+use SL::DB::Exchangerate;
+use SL::DB::Customer;
+use SL::DB::Vendor;
+use SL::DB::Employee;
+use SL::DB::Invoice;
+use SL::DB::Part;
+use SL::DB::Unit;
+use SL::DB::TaxZone;
+use SL::DB::BankAccount;
+use SL::DB::PaymentTerm;
+use SL::DB::PurchaseInvoice;
+use SL::DB::BankTransaction;
+use SL::DB::AccTransaction;
+use SL::Controller::YearlyTransactions;
+use Data::Dumper;
+
+my ($customer, $vendor, $currency_id, @parts, $unit, $employee, $tax, $tax7, $tax_9, $taxzone, $payment_terms, $bank_account);
+my ($transdate1, $transdate2, $currency);
+my ($ar_chart,$bank,$ar_amount_chart, $ap_chart, $ap_amount_chart, $saldo_chart);
+my ($ar_transaction, $ap_transaction);
+
+sub clear_up {
+
+ SL::DB::Manager::BankTransaction->delete_all(all => 1);
+ SL::DB::Manager::InvoiceItem->delete_all(all => 1);
+ SL::DB::Manager::InvoiceItem->delete_all(all => 1);
+ SL::DB::Manager::Invoice->delete_all(all => 1);
+ SL::DB::Manager::PurchaseInvoice->delete_all(all => 1);
+ SL::DB::Manager::Part->delete_all(all => 1);
+ SL::DB::Manager::Customer->delete_all(all => 1);
+ SL::DB::Manager::Vendor->delete_all(all => 1);
+ SL::DB::Manager::BankAccount->delete_all(all => 1);
+ SL::DB::Manager::AccTransaction->delete_all(all => 1);
+ SL::DB::Manager::PaymentTerm->delete_all(all => 1);
+ SL::DB::Manager::Currency->delete_all(where => [ name => 'CUR' ]);
+};
+
+
+# starting test:
+Support::TestSetup::login();
+
+reset_state(); # initialise customers/vendors/bank/currency/...
+
+test1();
+
+# remove all created data at end of test
+clear_up();
+
+done_testing();
+
+###### functions for setting up data
+
+sub reset_state {
+ my %params = @_;
+
+ $params{$_} ||= {} for qw(unit customer part tax vendor);
+
+ clear_up();
+
+ $transdate1 = DateTime->today;
+ $transdate2 = DateTime->today->add(days => 5);
+
+ $employee = SL::DB::Manager::Employee->current || croak "No employee";
+ $tax = SL::DB::Manager::Tax->find_by(taxkey => 3, rate => 0.19, %{ $params{tax} }) || croak "No tax";
+ $tax7 = SL::DB::Manager::Tax->find_by(taxkey => 2, rate => 0.07) || croak "No tax for 7\%";
+ $taxzone = SL::DB::Manager::TaxZone->find_by( description => 'Inland') || croak "No taxzone";
+ $tax_9 = SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.19, %{ $params{tax} }) || croak "No tax";
+
+ $currency_id = $::instance_conf->get_currency_id;
+
+ $bank_account = SL::DB::BankAccount->new(
+ account_number => '123',
+ bank_code => '123',
+ iban => '123',
+ bic => '123',
+ bank => '123',
+ chart_id => SL::DB::Manager::Chart->find_by(description => 'Bank')->id,
+ name => SL::DB::Manager::Chart->find_by(description => 'Bank')->description,
+ )->save;
+
+ $customer = SL::DB::Customer->new(
+ name => 'Test Customer',
+ currency_id => $currency_id,
+ taxzone_id => $taxzone->id,
+ iban => 'DE12500105170648489890',
+ bic => 'TESTBIC',
+ account_number => '648489890',
+ mandate_date_of_signature => $transdate1,
+ mandator_id => 'foobar',
+ bank => 'Geizkasse',
+ depositor => 'Test Customer',
+ %{ $params{customer} }
+ )->save;
+
+ $payment_terms = SL::DB::PaymentTerm->new(
+ description => 'payment',
+ description_long => 'payment',
+ terms_netto => '30',
+ terms_skonto => '5',
+ percent_skonto => '0.05',
+ auto_calculation => 1,
+ )->save;
+
+ $vendor = SL::DB::Vendor->new(
+ name => 'Test Vendor',
+ currency_id => $currency_id,
+ taxzone_id => $taxzone->id,
+ payment_id => $payment_terms->id,
+ iban => 'DE12500105170648489890',
+ bic => 'TESTBIC',
+ account_number => '648489890',
+ bank => 'Geizkasse',
+ depositor => 'Test Vendor',
+ %{ $params{vendor} }
+ )->save;
+
+ $ar_chart = SL::DB::Manager::Chart->find_by( accno => '1400' ); # Forderungen
+ $ap_chart = SL::DB::Manager::Chart->find_by( accno => '1600' ); # Verbindlichkeiten
+ $bank = SL::DB::Manager::Chart->find_by( accno => '1200' ); # Bank
+ $ar_amount_chart = SL::DB::Manager::Chart->find_by( accno => '8400' ); # Erlöse
+ $ap_amount_chart = SL::DB::Manager::Chart->find_by( accno => '3400' ); # Wareneingang 19%
+ $saldo_chart = SL::DB::Manager::Chart->find_by( accno => '9000' ); # Saldenvorträge
+
+}
+
+sub test_ar_transaction {
+ my (%params) = @_;
+ my $netamount = 100;
+ my $amount = $params{amount} || $::form->round_amount(100 * 1.19,2);
+ my $invoice = SL::DB::Invoice->new(
+ invoice => 0,
+ invnumber => $params{invnumber} || undef, # let it use its own invnumber
+ amount => $amount,
+ netamount => $netamount,
+ transdate => $transdate1,
+ taxincluded => 0,
+ customer_id => $customer->id,
+ taxzone_id => $customer->taxzone_id,
+ currency_id => $currency_id,
+ transactions => [],
+ payment_id => $params{payment_id} || undef,
+ notes => 'test_ar_transaction',
+ );
+ $invoice->add_ar_amount_row(
+ amount => $invoice->netamount,
+ chart => $ar_amount_chart,
+ tax_id => $tax->id,
+ );
+
+ $invoice->create_ar_row(chart => $ar_chart);
+ $invoice->save;
+
+ is($invoice->currency_id , $currency_id , 'currency_id has been saved');
+ is($invoice->netamount , 100 , 'ar amount has been converted');
+ is($invoice->amount , 119 , 'ar amount has been converted');
+ is($invoice->taxincluded , 0 , 'ar transaction doesn\'t have taxincluded');
+
+ is(SL::DB::Manager::AccTransaction->find_by(chart_id => $ar_amount_chart->id , trans_id => $invoice->id)->amount , '100.00000' , $ar_amount_chart->accno . ': has been converted for currency');
+ is(SL::DB::Manager::AccTransaction->find_by(chart_id => $ar_chart->id , trans_id => $invoice->id)->amount , '-119.00000' , $ar_chart->accno . ': has been converted for currency');
+
+ return $invoice;
+};
+
+sub test_ap_transaction {
+ my (%params) = @_;
+ my $netamount = 100;
+ my $amount = $::form->round_amount($netamount * 1.19,2);
+ my $invoice = SL::DB::PurchaseInvoice->new(
+ invoice => 0,
+ invnumber => $params{invnumber} || 'test_ap_transaction',
+ amount => $amount,
+ netamount => $netamount,
+ transdate => $transdate1,
+ taxincluded => 0,
+ vendor_id => $vendor->id,
+ taxzone_id => $vendor->taxzone_id,
+ currency_id => $currency_id,
+ transactions => [],
+ notes => 'test_ap_transaction',
+ );
+ $invoice->add_ap_amount_row(
+ amount => $invoice->netamount,
+ chart => $ap_amount_chart,
+ tax_id => $tax_9->id,
+ );
+
+ $invoice->create_ap_row(chart => $ap_chart);
+ $invoice->save;
+
+ is($invoice->currency_id , $currency_id , 'currency_id has been saved');
+ is($invoice->netamount , 100 , 'ap amount has been converted');
+ is($invoice->amount , 119 , 'ap amount has been converted');
+ is($invoice->taxincluded , 0 , 'ap transaction doesn\'t have taxincluded');
+
+ is(SL::DB::Manager::AccTransaction->find_by(chart_id => $ap_amount_chart->id , trans_id => $invoice->id)->amount , '-100.00000' , $ap_amount_chart->accno . ': has been converted for currency');
+ is(SL::DB::Manager::AccTransaction->find_by(chart_id => $ap_chart->id , trans_id => $invoice->id)->amount , '119.00000' , $ap_chart->accno . ': has been converted for currency');
+
+ return $invoice;
+};
+
+###### test cases
+
+sub test1 {
+
+ my $testname = 'test1';
+
+ $ar_transaction = test_ar_transaction(invnumber => 'salesinv1');
+ $ap_transaction = test_ap_transaction(invnumber => 'purchaseinv1');
+ my $ar_transaction_2 = test_ar_transaction(invnumber => 'salesinv_2');
+
+ my $yt_controller = SL::Controller::YearlyTransactions->new;
+ my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
+
+ $::form->{"ob_date"} = DateTime->today->truncate(to => 'year')->add(years => 1)->to_kivitendo;
+ $::form->{"cb_date"} = DateTime->today->truncate(to => 'year')->add(years => 1)->add(days => -1)->to_kivitendo;
+ #print "ob_date=".$::form->{"ob_date"}." cb_date=".$::form->{"cb_date"}."\n";
+ $::form->{"cb_reference"} = 'SB-Buchung';
+ $::form->{"ob_reference"} = 'EB-Buchung';
+ $::form->{"cb_description"} = 'SB-Buchung Beschreibung';
+ $::form->{"ob_description"} = 'EB-Buchung Beschreibung';
+ $::form->{"cbob_chart"} = $saldo_chart->id;
+
+ $yt_controller->prepare_report($report);
+
+ ## check balance of charts
+
+ my $idx = 1;
+ foreach my $chart (@{ $yt_controller->charts }) {
+ my $balance = $yt_controller->get_balance($chart);
+ if ( $balance != 0 ) {
+ #print "chart_id=".$chart->id."balance=".$balance."\n";
+ is($balance , '-238.00000' , $chart->accno.' has right balance') if $chart->accno eq '1400';
+ is($balance , '-19.00000' , $chart->accno.' has right balance') if $chart->accno eq '1576';
+ is($balance , '119.00000' , $chart->accno.' has right balance') if $chart->accno eq '1600';
+ is($balance , '38.00000' , $chart->accno.' has right balance') if $chart->accno eq '1776';
+ is($balance , '-100.00000' , $chart->accno.' has right balance') if $chart->accno eq '3400';
+ is($balance , '200.00000' , $chart->accno.' has right balance') if $chart->accno eq '8400';
+ $::form->{"trans_id_${idx}"} = $chart->id;
+ $::form->{"multi_id_${idx}"} = 1;
+ $idx++ ;
+ }
+ }
+ $::form->{"rowcount"} = $idx-1;
+ #print "rowcount=". $::form->{"rowcount"}."\n";
+ $::form->{"login"}="unittests";
+
+ $yt_controller->make_booking;
+
+ ## no check cb ob booking :
+
+ my $sum_cb_p = 0;
+ my $sum_cb_m = 0;
+ foreach my $acc ( @{ SL::DB::Manager::AccTransaction->get_all(where => [ chart_id => $saldo_chart->id, cb_transaction => 't' ]) }) {
+ #print "cb amount=".$acc->amount."\n";
+ $sum_cb_p += $acc->amount if $acc->amount > 0;
+ $sum_cb_m += -$acc->amount if $acc->amount < 0;
+ }
+ #print "chart_id=".$saldo_chart->id." sum_cb_p=".$sum_cb_p." sum_cb_m=".$sum_cb_m."\n";
+ is($sum_cb_p , '357' , 'chart '.$saldo_chart->accno.' has right positive close saldo');
+ is($sum_cb_m , '357' , 'chart '.$saldo_chart->accno.' has right negative close saldo');
+ my $sum_ob_p = 0;
+ my $sum_ob_m = 0;
+ foreach my $acc ( @{ SL::DB::Manager::AccTransaction->get_all(where => [ chart_id => $saldo_chart->id, ob_transaction => 't' ]) }) {
+ #print "ob amount=".$acc->amount."\n";
+ $sum_ob_p += $acc->amount if $acc->amount > 0;
+ $sum_ob_m += -$acc->amount if $acc->amount < 0;
+ }
+ #print "chart_id=".$saldo_chart->id." sum_ob_p=".$sum_ob_p." sum_ob_m=".$sum_ob_m."\n";
+ is($sum_ob_p , '357' , 'chart '.$saldo_chart->accno.' has right positive open saldo');
+ is($sum_ob_m , '357' , 'chart '.$saldo_chart->accno.' has right negative open saldo');
+}
+
+
+
+1;
--- /dev/null
+[%- USE L %]
+[%- USE LxERP %]
+<table width="100%">
+ <tr><td>
+ [%- L.hidden_tag("action","YearlyTransactions/dispatch") %]
+ [%- L.hidden_tag("cb_date",SELF.cb_date) %]
+ [%- L.hidden_tag("cb_startdate",SELF.cb_startdate) %]
+ [%- L.hidden_tag("cb_reference",SELF.cb_reference) %]
+ [%- L.hidden_tag("cb_description",SELF.cb_description) %]
+ [%- L.hidden_tag("ob_date",SELF.ob_date) %]
+ [%- L.hidden_tag("ob_reference",SELF.ob_reference) %]
+ [%- L.hidden_tag("ob_description",SELF.ob_description) %]
+ [%- L.hidden_tag("cbob_chart",SELF.cbob_chart) %]
+ [%- L.hidden_tag("rowcount",SELF.row_count) %]
+ [%- L.submit_tag("action_generate", LxERP.t8('generate cb/ob transactions for selected charts'),
+ confirm=LxERP.t8('Are you sure to generate cb/ob transactions?')) %]
+ [%- L.submit_tag("action_filter", LxERP.t8('back')) %]
+ </td>
+ </tr>
+</table></form>
+<script type='text/javascript'>
+$(function(){
+ $('#multi_all').checkall("input[name^='multi_id']");
+});
+</script>
--- /dev/null
+[%- USE HTML %]
+[%- USE T8 %]
+[%- USE L %]
+[%- USE LxERP %]
+
+<h1>[% title | html %]</h1>
+
+[%- PROCESS 'common/flash.html' %]
+
+<form id='filter_form'>
+
+<table>
+ <tr>
+ <td width="20px"></td>
+ <td align="left" colspan="5">[% 'Attention: Here will be generated a lot of CB/OB transactions.' | $T8 %]</td>
+ </tr>
+ <tr>
+ <td><p></p></td>
+ </tr>
+ <tr>
+ <th></th>
+ <th width="400px" colspan="2" align="center">[% 'CB Transactions' | $T8 %]</th>
+ <th width="400px"colspan="2" align="center">[% 'OB Transactions' | $T8 %]</th>
+ <th> </th>
+ </tr>
+ <tr>
+ <td></td>
+ <td align="right">[% 'Date' | $T8 %]</td>
+ <td>[% L.date_tag('cb_date', SELF.cb_date) %]</td>
+ <td align="right">[% 'Date' | $T8 %]</td>
+ <td>[% L.date_tag('ob_date', SELF.ob_date) %]</td>
+ <td></td>
+ </tr>
+ <tr>
+ <td></td>
+ <td align="right">[% 'Reference' | $T8 %]</td>
+ <td>[% L.input_tag('cb_reference', SELF.cb_reference) %]</td>
+ <td align="right">[% 'Reference' | $T8 %]</td>
+ <td>[% L.input_tag('ob_reference', SELF.ob_reference) %]</td>
+ <td></td>
+ </tr>
+ <tr>
+ <td></td>
+ <td align="right">[% 'Description' | $T8 %]</td>
+ <td>[% L.input_tag('cb_description', SELF.cb_description) %]</td>
+ <td align="right">[% 'Description' | $T8 %]</td>
+ <td>[% L.input_tag('ob_description', SELF.ob_description) %]</td>
+ <td></td>
+ </tr>
+ <tr>
+ <td><p></p></td>
+ </tr>
+ <tr>
+ <th colspan="2"></th>
+ <th align=right>[% 'close chart' | $T8 %]</th>
+ <td colspan="3">[% L.select_tag('cbob_chart', SELF.charts9000, title_sub=\make_title_of_chart, default=SELF.cbob_chart, style="width: 400px") %]</td>
+ </tr>
+</table>
+
+[% L.hidden_tag('action', 'YearlyTransactions/dispatch') %]
+<hr size=3 noshade><br>
+[% L.submit_tag('action_list', LxERP.t8('Continue')) %]
+
+</form>
--- /dev/null
+[%- USE LxERP %]
+[%- INCLUDE 'common/flash.html' %]
+<form method="post" action="controller.pl">
+<table>
+ <tr>
+ <td width="20px"></td>
+ <td colspan="6" align="left">
+ [%- LxERP.t8('Select charts for which the CB/OB transactions want to be posted.') %]<br>
+ [%- LxERP.t8('There will be two transactions done:') %]<br>
+ - [%- LxERP.t8('One SB-transaction') %] ( [% SELF.cb_date %], [% SELF.cb_reference %], [% SELF.cb_description %], [% SELF.cbob_chartaccno %] )<br>
+ - [%- LxERP.t8('One OB-transaction') %] ( [% SELF.ob_date %], [% SELF.ob_reference %], [% SELF.ob_description %], [% SELF.cbob_chartaccno %] )<br>
+ [%- LxERP.t8('No revert available.') %]
+ </td>
+ </tr>
+ <tr>
+ <td><p></p></td>
+ </tr>
+ <tr>
+ <th></th>
+ <th align="right">[% LxERP.t8('close chart') %]</th>
+ <td>[% SELF.cbob_chartaccno %]</td>
+ </tr>
+</table>
projects / kivitendo-erp.git / commitdiff