From 1a3b9961184e9321c4e6e034643fdfc6a640603e Mon Sep 17 00:00:00 2001 From: "G. Richardson" Date: Tue, 21 Mar 2017 17:16:59 +0100 Subject: [PATCH] DATEV-Export nach Erfassungsdatum filtern MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Hat man einen DATEV-Export schon für einen bestimmten Zeitraum exportiert, und muß nachträglich noch ein paar Buchungen in dem Zeitraum tätigen, kann man diese nachträglichen Buchungen nun gesondert exportieren, indem man ein Datum nach dem letzten Exportdatum angibt. --- SL/DATEV.pm | 24 +++++++++- doc/changelog | 6 +++ t/datev/invoices.t | 42 ++++++++++++++--- .../webpages/datev/export_bewegungsdaten.html | 47 ++++++++++++------- 4 files changed, 95 insertions(+), 24 deletions(-) diff --git a/SL/DATEV.pm b/SL/DATEV.pm index c39ba3407..ed636d403 100644 --- a/SL/DATEV.pm +++ b/SL/DATEV.pm @@ -383,6 +383,13 @@ sub generate_datev_data { $gl_department_id_filter = " AND gl.department_id = ? "; } + my ($gl_itime_filter, $ar_itime_filter, $ap_itime_filter); + if ( $form->{gldatefrom} ) { + $gl_itime_filter = " AND gl.itime >= ? "; + $ar_itime_filter = " AND ar.itime >= ? "; + $ap_itime_filter = " AND ap.itime >= ? "; + } + if ( $self->{trans_id} ) { # ignore dates when trans_id is passed so that the entire transaction is # checked, not just either the initial bookings or the subsequent payments @@ -419,6 +426,7 @@ sub generate_datev_data { WHERE (ar.id IS NOT NULL) AND $fromto $trans_id_filter + $ar_itime_filter $ar_department_id_filter $filter @@ -443,6 +451,7 @@ sub generate_datev_data { WHERE (ap.id IS NOT NULL) AND $fromto $trans_id_filter + $ap_itime_filter $ap_department_id_filter $filter @@ -466,14 +475,25 @@ sub generate_datev_data { WHERE (gl.id IS NOT NULL) AND $fromto $trans_id_filter + $gl_itime_filter $gl_department_id_filter $filter ORDER BY trans_id, acc_trans_id|; my @query_args; - if ( $form->{department_id} ) { - push(@query_args, ($form->{department_id}) x 3); + if ( $form->{gldatefrom} or $form->{department_id} ) { + + for ( 1 .. 3 ) { + if ( $form->{gldatefrom} ) { + my $glfromdate = $::locale->parse_date_to_object($form->{gldatefrom}); + die "illegal data" unless ref($glfromdate) eq 'DateTime'; + push(@query_args, $glfromdate); + } + if ( $form->{department_id} ) { + push(@query_args, $form->{department_id}); + } + } } my $sth = prepare_execute_query($form, $self->dbh, $query, @query_args); diff --git a/doc/changelog b/doc/changelog index d10b4c89b..623aa81b7 100644 --- a/doc/changelog +++ b/doc/changelog @@ -11,6 +11,12 @@ kleinere neue Features und Detailverbesserungen: - Stammdaten -> Berichte -> Artikel: Standardlager und Lagerplatz optional anzeigen - Vorbelegte Texte inkl. Ansprechpartner für den E-Mail-Versand bei allen Workflows hinzugefügt. + - DATEV-Export: Buchungen für einen bestimmten Zeitraum ab einem + Buchungsdatum filtern. Z.B. wenn man einen DATEV-Export für Januar schon + exportiert hat, und im Juni noch ein Buchung für Januar nachbucht, kann man + mit "Erfassungsdatum Von: 01.06.2017" nur diese eine Buchung aus Januar + exportieren. + 2017-07-17 - Release 3.5.0 große Features: diff --git a/t/datev/invoices.t b/t/datev/invoices.t index 5a33d5f0c..45c68ca6d 100644 --- a/t/datev/invoices.t +++ b/t/datev/invoices.t @@ -16,10 +16,13 @@ Support::TestSetup::login(); clear_up(); +my $dbh = SL::DB->client->dbh; + my $buchungsgruppe7 = SL::DB::Manager::Buchungsgruppe->find_by(description => 'Standard 7%') || die "No accounting group for 7\%"; my $bank = SL::DB::Manager::Chart->find_by(description => 'Bank') || die 'Can\'t find chart "Bank"'; my $date = DateTime->new(year => 2017, month => 1, day => 1); my $payment_date = DateTime->new(year => 2017, month => 1, day => 5); +my $gldate = DateTime->new(year => 2017, month => 2, day => 9); # simulate bookings for Jan being made in Feb my $part1 = SL::Dev::Part::create_part(partnumber => '19', description => 'Part 19%')->save; my $part2 = SL::Dev::Part::create_part( @@ -30,6 +33,9 @@ my $part2 = SL::Dev::Part::create_part( my $invoice = SL::Dev::Record::create_sales_invoice( invnumber => "1 sales invoice", + itime => $gldate, + gldate => $gldate, + intnotes => 'booked in February', taxincluded => 0, transdate => $date, invoiceitems => [ SL::Dev::Record::create_invoice_item(part => $part1, qty => 3, sellprice => 70), @@ -78,8 +84,12 @@ cmp_bag $datev1->generate_datev_lines, [ }, ], "trans_id datev check ok"; +my $march_9 = DateTime->new(year => 2017, month => 3, day => 9); my $invoice2 = SL::Dev::Record::create_sales_invoice( invnumber => "2 sales invoice", + itime => $march_9, + gldate => $march_9, + intnotes => 'booked in March', taxincluded => 0, transdate => $date, invoiceitems => [ SL::Dev::Record::create_invoice_item(part => $part1, qty => 6, sellprice => 70), @@ -89,6 +99,9 @@ my $invoice2 = SL::Dev::Record::create_sales_invoice( my $credit_note = SL::Dev::Record::create_credit_note( invnumber => 'Gutschrift 34', + itime => $gldate, + gldate => $gldate, + intnotes => 'booked in February', taxincluded => 0, transdate => $date, invoiceitems => [ SL::Dev::Record::create_invoice_item(part => $part1, qty => 3, sellprice => 70), @@ -96,18 +109,37 @@ my $credit_note = SL::Dev::Record::create_credit_note( ] ); -my $startdate = DateTime->new(year => 2017, month => 1, day => 1); +my $startdate = DateTime->new(year => 2017, month => 1, day => 1); my $enddate = DateTime->new(year => 2017, month => 12, day => 31); my $datev = SL::DATEV->new( - dbh => $credit_note->db->dbh, + dbh => $dbh, + from => $startdate, + to => $enddate, +); +$datev->generate_datev_data(from_to => $datev->fromto); +my $datev_lines = $datev->generate_datev_lines; +my $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines }; +cmp_ok($::form->round_amount($umsatzsumme,2), '==', 3924.5, "Sum of all bookings ok"); + +note('testing gldatefrom'); +my $datev = SL::DATEV->new( + dbh => $dbh, from => $startdate, - to => $enddate + to => DateTime->new(year => 2017, month => 01, day => 31), ); + +$::form = Support::TestSetup->create_new_form; +$::form->{gldatefrom} = DateTime->new(year => 2017, month => 3, day => 1)->to_kivitendo; + $datev->generate_datev_data(from_to => $datev->fromto); my $datev_lines = $datev->generate_datev_lines; my $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines }; -is($umsatzsumme, 3924.50, "umsatzsumme ok"); +cmp_ok($umsatzsumme, '==', 1569.8, "Sum of bookings made after March 1st (only invoice2) ok"); + +$::form->{gldatefrom} = DateTime->new(year => 2017, month => 5, day => 1)->to_kivitendo; +$datev->generate_datev_data(from_to => $datev->fromto); +cmp_bag $datev->generate_datev_lines, [], "no bookings for January made after May 1st: ok"; done_testing(); clear_up(); @@ -120,6 +152,4 @@ sub clear_up { SL::DB::Manager::Part->delete_all( all => 1); }; - 1; - diff --git a/templates/webpages/datev/export_bewegungsdaten.html b/templates/webpages/datev/export_bewegungsdaten.html index 441d83df9..ae9c13edd 100644 --- a/templates/webpages/datev/export_bewegungsdaten.html +++ b/templates/webpages/datev/export_bewegungsdaten.html @@ -54,25 +54,40 @@ - [% IF ALL_DEPARTMENTS.as_list.size %] - - [% 'Department' | $T8 %] - - - - - - - - -
[% 'Department' | $T8 %][% L.select_tag('department_id', ALL_DEPARTMENTS, title_key = 'description', with_empty = 1) %]
- +
+ + + + + + + + + +
[% 'Gldate' | $T8 %] [% 'From' | $T8 %][% L.date_tag('gldatefrom') %]
+ + + + +
+ + [% IF ALL_DEPARTMENTS.as_list.size %] + + + + + + + + +
[% 'Department' | $T8 %][% L.select_tag('department_id', ALL_DEPARTMENTS, title_key = 'description', with_empty = 1) %]
+ + + +
[% END %] - -
- -- 2.20.1