From 0fed2b9ab81651006e63659c67940874cbf199d8 Mon Sep 17 00:00:00 2001 From: "G. Richardson" Date: Tue, 8 Oct 2019 15:50:19 +0200 Subject: [PATCH] GLTransaction - Dialogbuchungen per Rose erstellen MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit neue Methoden in GLTransaction zum Erstellen von Dialogbuchungen * post * validate * add_chart_booking An einigen Stellen im Code werden Dialogbuchungen per Hand erstellt, inkl. Steuern, das soll hiermit vereinheitlicht und vereinfacht werden. Acc_trans-Einträge können nun mit wenigen Parametern zu Dialogbuchungen hinzugefügt werden, die Parameter orientieren sich dabei an den Werten, wie sie auch an der Oberfläche eingegeben werden (Konto, Soll/Haben, Steuer). Dabei werden einige der Werte aus der GLTransaction automatisch übernommen. Beim Buchen wird eine neue Transaktion gestartet, die Buchung wird validiert und es wird ein Historieneintrag erstellt. --- SL/DB/GLTransaction.pm | 281 ++++++++++++++++++++++++++++++++++++++++ locale/de/all | 4 + locale/en/all | 4 + t/gl/gl.t | 284 +++++++++++++++++++++++++++++++++++++++++ 4 files changed, 573 insertions(+) create mode 100644 t/gl/gl.t diff --git a/SL/DB/GLTransaction.pm b/SL/DB/GLTransaction.pm index a92271f12..25d4de7e3 100644 --- a/SL/DB/GLTransaction.pm +++ b/SL/DB/GLTransaction.pm @@ -5,6 +5,9 @@ use strict; use SL::DB::MetaSetup::GLTransaction; use SL::Locale::String qw(t8); use List::Util qw(sum); +use SL::DATEV; +use Carp; +use Data::Dumper; # Creates get_all, get_all_count, get_all_iterator, delete_all and update_all. __PACKAGE__->meta->make_manager_class; @@ -57,4 +60,282 @@ sub invnumber { sub date { goto &gldate } +sub post { + my ($self) = @_; + + my @errors = $self->validate; + croak t8("Errors in GL transaction:") . "\n" . join("\n", @errors) . "\n" if scalar @errors; + + # make sure all the defaults are set: + require SL::DB::Employee; + my $employee_id = SL::DB::Manager::Employee->current->id; + $self->type(undef); + $self->employee_id($employee_id) unless defined $self->employee_id || defined $self->employee; + $self->ob_transaction('f') unless defined $self->ob_transaction; + $self->cb_transaction('f') unless defined $self->cb_transaction; + $self->gldate(DateTime->today_local) unless defined $self->gldate; # should user even be allowed to set this manually? + $self->transdate(DateTime->today_local) unless defined $self->transdate; + + $self->db->with_transaction(sub { + $self->save; + + if ($::instance_conf->get_datev_check_on_gl_transaction) { + my $datev = SL::DATEV->new( + dbh => $self->dbh, + trans_id => $self->id, + ); + + $datev->generate_datev_data; + + if ($datev->errors) { + die join "\n", t8('DATEV check returned errors:'), $datev->errors; + } + } + + require SL::DB::History; + SL::DB::History->new( + trans_id => $self->id, + snumbers => 'gltransaction_' . $self->id, + employee_id => $employee_id, + addition => 'POSTED', + what_done => 'gl transaction', + )->save; + + 1; + }) or die t8("Error when saving: #1", $self->db->error); + + return $self; +} + +sub add_chart_booking { + my ($self, %params) = @_; + + require SL::DB::Chart; + die "add_chart_booking needs a transdate" unless $self->transdate; + die "add_chart_booking needs taxincluded" unless defined $self->taxincluded; + die "chart missing" unless $params{chart} && ref($params{chart}) eq 'SL::DB::Chart'; + die t8('Booking needs at least one debit and one credit booking!') + unless $params{debit} or $params{credit}; # must exist and not be 0 + die t8('Cannot have a value in both Debit and Credit!') + if defined($params{debit}) and defined($params{credit}); + + my $chart = $params{chart}; + + my $dec = delete $params{dec} // 2; + + my ($netamount,$taxamount) = (0,0); + my $amount = $params{credit} // $params{debit}; # only one can exist + + croak t8('You cannot use a negative amount with debit/credit!') if $amount < 0; + + require SL::DB::Tax; + my $tax = SL::DB::Manager::Tax->find_by(id => $params{tax_id}) + // croak "Can't find tax with id " . $params{tax_id}; + + if ( $tax and $tax->rate != 0 ) { + ($netamount, $taxamount) = Form->calculate_tax($amount, $tax->rate, $self->taxincluded, $dec); + } else { + $netamount = $amount; + }; + + if ( $params{debit} ) { + $amount *= -1; + $netamount *= -1; + $taxamount *= -1; + }; + + next unless $netamount; # skip entries with netamount 0 + + # initialise transactions if it doesn't exist yet + $self->transactions([]) unless $self->transactions; + + require SL::DB::AccTransaction; + $self->add_transactions( SL::DB::AccTransaction->new( + chart_id => $chart->id, + chart_link => $chart->link, + amount => $netamount, + taxkey => $tax->taxkey, + tax_id => $tax->id, + transdate => $self->transdate, + source => $params{source} // '', + memo => $params{memo} // '', + ob_transaction => $self->ob_transaction, + cb_transaction => $self->cb_transaction, + project_id => $params{project_id}, + )); + + # only add tax entry if amount is >= 0.01, defaults to 2 decimals + if ( $::form->round_amount(abs($taxamount), $dec) > 0 ) { + my $tax_chart = $tax->chart; + if ( $tax->chart ) { + $self->add_transactions(SL::DB::AccTransaction->new( + chart_id => $tax_chart->id, + chart_link => $tax_chart->link, + amount => $taxamount, + taxkey => $tax->taxkey, + tax_id => $tax->id, + transdate => $self->transdate, + ob_transaction => $self->ob_transaction, + cb_transaction => $self->cb_transaction, + source => $params{source} // '', + memo => $params{memo} // '', + project_id => $params{project_id}, + )); + }; + }; + return $self; +}; + +sub validate { + my ($self) = @_; + + my @errors; + + if ( $self->transactions && scalar @{ $self->transactions } ) { + my $debit_count = map { $_->amount } grep { $_->amount > 0 } @{ $self->transactions }; + my $credit_count = map { $_->amount } grep { $_->amount < 0 } @{ $self->transactions }; + + if ( $debit_count > 1 && $credit_count > 1 ) { + push @errors, t8('Split entry detected. The values you have entered will result in an entry with more than one position on both debit and credit. ' . + 'Due to known problems involving accounting software kivitendo does not allow these.'); + } elsif ( $credit_count == 0 && $debit_count == 0 ) { + push @errors, t8('Booking needs at least one debit and one credit booking!'); + } else { + # transactions formally ok, now check for out of balance: + my $sum = sum map { $_->amount } @{ $self->transactions }; + # compare rounded amount to 0, to get around floating point problems, e.g. + # $sum = -2.77555756156289e-17 + push @errors, t8('Out of balance transaction!') unless $::form->round_amount($sum,5) == 0; + }; + } else { + push @errors, t8('Empty transaction!'); + }; + + # fields enforced by interface + push @errors, t8('Reference missing!') unless $self->reference; + push @errors, t8('Description missing!') unless $self->description; + + # date checks + push @errors, t8('Transaction Date missing!') unless $self->transdate && ref($self->transdate) eq 'DateTime'; + + if ( $self->transdate ) { + if ( $::form->date_closed( $self->transdate, \%::myconfig) ) { + if ( !$self->id ) { + push @errors, t8('Cannot post transaction for a closed period!') + } else { + push @errors, t8('Cannot change transaction in a closed period!') + }; + }; + + push @errors, t8('Cannot post transaction above the maximum future booking date!') + if $::form->date_max_future($self->transdate, \%::myconfig); + } + + return @errors; +} + 1; + +__END__ + +=pod + +=encoding UTF-8 + +=head1 NAME + +SL::DB::GLTransaction: Rose model for GL transactions (table "gl") + +=head1 FUNCTIONS + +=over 4 + +=item C + +Takes an unsaved but initialised GLTransaction object and saves it, but first +validates the object, sets certain defaults (e.g. employee), and then also runs +various checks, writes history, runs DATEV check, ... + +Returns C<$self> on success and dies otherwise. The whole process is run inside +a transaction. If it fails then nothing is saved to or changed in the database. +A new transaction is only started if none are active. + +Example of posting a GL transaction from scratch: + + my $tax_0 = SL::DB::Manager::Tax->find_by(taxkey => 0, rate => 0.00); + my $gl_transaction = SL::DB::GLTransaction->new( + taxincluded => 1, + description => 'bar', + reference => 'bla', + transdate => DateTime->today_local, + )->add_chart_booking( + chart => SL::DB::Manager::Chart->find_by( description => 'Kasse' ), + credit => 100, + tax_id => $tax_0->id, + )->add_chart_booking( + chart => SL::DB::Manager::Chart->find_by( description => 'Bank' ), + debit => 100, + tax_id => $tax_0->id, + )->post; + +=item C + +Adds an acc_trans entry to an existing GL transaction, depending on the tax it +will also automatically create the tax entry. The GL transaction already needs +to have certain values, e.g. transdate, taxincluded, ... + +Mandatory params are + +=over 2 + +=item * chart as an RDBO object + +=item * tax_id + +=item * either debit OR credit (positive values) + +=back + +Optional params: + +=over 2 + +=item * dec - number of decimals to round to, defaults to 2 + +=item * source + +=item * memo + +=item * project_id + +=back + +All other values are taken directly from the GL transaction. + +For an example, see C. + +After adding an acc_trans entry the GL transaction shouldn't be modified (e.g. +values affecting the acc_trans entries, such as transdate or taxincluded +shouldn't be changed). There is currently no method for recalculating the +acc_trans entries after they were added. + +Return C<$self>, so it allows chaining. + +=item C + +Runs various checks to see if the GL transaction is ready to be Ced. + +Will return an array of error strings if any necessary conditions aren't met. + +=back + +=head1 TODO + +Nothing here yet. + +=head1 AUTHOR + +Moritz Bunkus Em.bunkus@linet-services.deE, +G. Richardson Egrichardson@kivitec.deE + +=cut diff --git a/locale/de/all b/locale/de/all index 6a49a3de9..30b23d43f 100755 --- a/locale/de/all +++ b/locale/de/all @@ -476,6 +476,7 @@ $self->{texts} = { 'Booking group (database ID)' => 'Buchungsgruppe (database ID)', 'Booking group (name)' => 'Buchungsgruppe (name)', 'Booking groups' => 'Buchungsgruppen', + 'Booking needs at least one debit and one credit booking!' => 'Die Buchung benötigt mindestens eine Buchung im Soll eine im Haben!', 'Bookinggroup/Tax' => 'Buchungsgruppe/Steuer', 'Books are open' => 'Die Bücher sind geöffnet.', 'Books closed up to' => 'Bücher abgeschlossen bis zum', @@ -525,6 +526,7 @@ $self->{texts} = { 'Cancel Accounts Payables Transaction' => 'Kreditorenbuchung stornieren', 'Cancel Accounts Receivables Transaction' => 'Debitorenbuchung stornieren', 'Cancelling is disallowed. Either undo or balance the current payments until the open amount matches the invoice amount' => 'Storno verboten, da Zahlungen zum Beleg vorhanden sind. Entweder die Zahlungen löschen oder mit umgekehrten Vorzeichen ausbuchen, sodass der offene Betrag dem Rechnungsbetrag entspricht.', + 'Cannot change transaction in a closed period!' => 'In einem bereits abgeschlossenen Zeitraum kann keine Buchung verändert werden!', 'Cannot check correct WebDAV folder' => 'Kann nicht den richtigen WebDAV Pfad überprüfen', 'Cannot delete account!' => 'Konto kann nicht gelöscht werden!', 'Cannot delete customer!' => 'Kunde kann nicht gelöscht werden!', @@ -1338,6 +1340,7 @@ $self->{texts} = { 'Error: unknown local bank account id' => 'Fehler: unbekannte Bankkonto-ID', 'Errors during conversion:' => 'Umwandlungsfehler:', 'Errors during printing:' => 'Druckfehler:', + 'Errors in GL transaction:' => 'Fehler in Dialogbuchung:', 'Ertrag' => 'Ertrag', 'Ertrag prozentual' => 'Ertrag prozentual', 'Escape character' => 'Escape-Zeichen', @@ -3935,6 +3938,7 @@ $self->{texts} = { '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 cannot use a negative amount with debit/credit!' => 'Sie dürfen für Soll/Haben keine negativen Werte benutzen!', '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.', diff --git a/locale/en/all b/locale/en/all index e86688b73..fd987d3c2 100644 --- a/locale/en/all +++ b/locale/en/all @@ -476,6 +476,7 @@ $self->{texts} = { 'Booking group (database ID)' => '', 'Booking group (name)' => '', 'Booking groups' => '', + 'Booking needs at least one debit and one credit booking!' => '', 'Bookinggroup/Tax' => '', 'Books are open' => '', 'Books closed up to' => '', @@ -525,6 +526,7 @@ $self->{texts} = { 'Cancel Accounts Payables Transaction' => '', 'Cancel Accounts Receivables Transaction' => '', 'Cancelling is disallowed. Either undo or balance the current payments until the open amount matches the invoice amount' => '', + 'Cannot change transaction in a closed period!' => '', 'Cannot check correct WebDAV folder' => '', 'Cannot delete account!' => '', 'Cannot delete customer!' => '', @@ -1338,6 +1340,7 @@ $self->{texts} = { 'Error: unknown local bank account id' => '', 'Errors during conversion:' => '', 'Errors during printing:' => '', + 'Errors in GL transaction:' => '', 'Ertrag' => '', 'Ertrag prozentual' => '', 'Escape character' => '', @@ -3934,6 +3937,7 @@ $self->{texts} = { 'You cannot create an invoice for delivery orders from different vendors.' => '', 'You cannot modify individual assigments from additional articles to line items.' => '', 'You cannot paste function blocks or sub function blocks if there is no section.' => '', + 'You cannot use a negative amount with debit/credit!' => '', 'You do not have access to any custom data export.' => '', 'You do not have permission to access this entry.' => '', 'You do not have the permissions to access this function.' => '', diff --git a/t/gl/gl.t b/t/gl/gl.t new file mode 100644 index 000000000..8cc0f6afd --- /dev/null +++ b/t/gl/gl.t @@ -0,0 +1,284 @@ +use strict; +use Test::More tests => 4; + +use lib 't'; +use Support::TestSetup; +use Carp; +use Test::Exception; +use SL::DB::Chart; +use SL::DB::TaxKey; +use SL::DB::GLTransaction; +use Data::Dumper; +use SL::DBUtils qw(selectall_hashref_query); + +Support::TestSetup::login(); + +clear_up(); + +my $cash = SL::DB::Manager::Chart->find_by( description => 'Kasse' ); +my $bank = SL::DB::Manager::Chart->find_by( description => 'Bank' ); +my $betriebsbedarf = SL::DB::Manager::Chart->find_by( description => 'Betriebsbedarf' ); + +my $tax_9 = SL::DB::Manager::Tax->find_by(taxkey => 9, rate => 0.19); +my $tax_8 = SL::DB::Manager::Tax->find_by(taxkey => 8, rate => 0.07); +my $tax_0 = SL::DB::Manager::Tax->find_by(taxkey => 0, rate => 0.00); + +my $dbh = SL::DB->client->dbh; + +# example with chaining of add_chart_booking +my $gl_transaction = SL::DB::GLTransaction->new( + taxincluded => 1, + reference => 'bank/cash', + description => 'bank/cash', + transdate => DateTime->today_local, +)->add_chart_booking( + chart => $cash, + credit => 100, + tax_id => $tax_0->id, +)->add_chart_booking( + chart => $bank, + debit => 100, + tax_id => $tax_0->id, +)->post; + +# example where bookings is prepared separately as an arrayref +my $gl_transaction_2 = SL::DB::GLTransaction->new( + reference => 'betriebsbedarf several rows', + description => 'betriebsbedarf', + taxincluded => 1, + transdate => DateTime->today_local, +); + +my $bookings = [ + { + chart => $betriebsbedarf, + memo => 'foo 1', + source => 'foo 1', + debit => 119, + tax_id => $tax_9->id, + }, + { + chart => $betriebsbedarf, + memo => 'foo 2', + source => 'foo 2', + debit => 119, + tax_id => $tax_9->id, + }, + { + chart => $cash, + credit => 238, + memo => 'foo 1+2', + source => 'foo 1+2', + tax_id => $tax_0->id, + }, + ]; +$gl_transaction_2->add_chart_booking(%{$_}) foreach @{ $bookings }; +$gl_transaction_2->post; + + +# example where add_chart_booking is called via a foreach +my $gl_transaction_3 = SL::DB::GLTransaction->new( + reference => 'betriebsbedarf tax included', + description => 'bar', + taxincluded => 1, + transdate => DateTime->today_local, +); +$gl_transaction_3->add_chart_booking(%{$_}) foreach ( + { + chart => $betriebsbedarf, + debit => 119, + tax_id => $tax_9->id, + }, + { + chart => $betriebsbedarf, + debit => 107, + tax_id => $tax_8->id, + }, + { + chart => $betriebsbedarf, + debit => 100, + tax_id => $tax_0->id, + }, + { + chart => $cash, + credit => 326, + tax_id => $tax_0->id, + }, +); +$gl_transaction_3->post; + +my $gl_transaction_4 = SL::DB::GLTransaction->new( + reference => 'betriebsbedarf tax not included', + description => 'bar', + taxincluded => 0, + transdate => DateTime->today_local, +); +$gl_transaction_4->add_chart_booking(%{$_}) foreach ( + { + chart => $betriebsbedarf, + debit => 100, + tax_id => $tax_9->id, + }, + { + chart => $betriebsbedarf, + debit => 100, + tax_id => $tax_8->id, + }, + { + chart => $betriebsbedarf, + debit => 100, + tax_id => $tax_0->id, + }, + { + chart => $cash, + credit => 326, + tax_id => $tax_0->id, + }, +); +$gl_transaction_4->post; + +is(SL::DB::Manager::GLTransaction->get_all_count(), 4, "gl transactions created ok"); + +is_deeply(&get_account_balances, + [ + { + 'accno' => '1000', + 'sum' => '990.00000' + }, + { + 'accno' => '1200', + 'sum' => '-100.00000' + }, + { + 'accno' => '1571', + 'sum' => '-14.00000' + }, + { + 'accno' => '1576', + 'sum' => '-76.00000' + }, + { + 'accno' => '4980', + 'sum' => '-800.00000' + } + ], + "chart balances ok" + ); + + +note('testing subcent'); + +my $gl_transaction_5_taxinc = SL::DB::GLTransaction->new( + taxincluded => 1, + reference => 'subcent tax included', + description => 'subcent tax included', + transdate => DateTime->today_local, +)->add_chart_booking( + chart => $betriebsbedarf, + debit => 0.02, + tax_id => $tax_9->id, +)->add_chart_booking( + chart => $cash, + credit => 0.02, + tax_id => $tax_0->id, +)->post; + +my $gl_transaction_5_taxnoinc = SL::DB::GLTransaction->new( + taxincluded => 0, + reference => 'subcent tax not included', + description => 'subcent tax not included', + transdate => DateTime->today_local, +)->add_chart_booking( + chart => $betriebsbedarf, + debit => 0.02, + tax_id => $tax_9->id, +)->add_chart_booking( + chart => $cash, + credit => 0.02, + tax_id => $tax_0->id, +)->post; + +my $gl_transaction_6_taxinc = SL::DB::GLTransaction->new( + taxincluded => 1, + reference => 'cent tax included', + description => 'cent tax included', + transdate => DateTime->today_local, +)->add_chart_booking( + chart => $betriebsbedarf, + debit => 0.05, + tax_id => $tax_9->id, +)->add_chart_booking( + chart => $cash, + credit => 0.05, + tax_id => $tax_0->id, +)->post; + +my $gl_transaction_6_taxnoinc = SL::DB::GLTransaction->new( + taxincluded => 0, + reference => 'cent tax included', + description => 'cent tax included', + transdate => DateTime->today_local, +)->add_chart_booking( + chart => $betriebsbedarf, + debit => 0.04, + tax_id => $tax_9->id, +)->add_chart_booking( + chart => $cash, + credit => 0.05, + tax_id => $tax_0->id, +)->post; + +is(SL::DB::Manager::GLTransaction->get_all_count(), 8, "gl transactions created ok"); + + +is_deeply(&get_account_balances, + [ + { + 'accno' => '1000', + 'sum' => '990.14000' + }, + { + 'accno' => '1200', + 'sum' => '-100.00000' + }, + { + 'accno' => '1571', + 'sum' => '-14.00000' + }, + { + 'accno' => '1576', + 'sum' => '-76.02000' + }, + { + 'accno' => '4980', + 'sum' => '-800.12000' + } + ], + "chart balances ok" + ); + +done_testing; +clear_up(); + +1; + +sub clear_up { + "SL::DB::Manager::${_}"->delete_all(all => 1) for qw( + AccTransaction + GLTransaction + ); +}; + +sub get_account_balances { + my $query = <