DATEV-Export nach Erfassungsdatum filtern

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.

diff --git a/SL/DATEV.pm b/SL/DATEV.pm
index c39ba34074f21e2107f4e205e7456e0c447de92c..ed636d4038a663b817e94d2884ce514af406977b 100644 (file)
--- a/SL/DATEV.pm
+++ b/SL/DATEV.pm
@@ -383,6 +383,13 @@ sub generate_datev_data {
     $gl_department_id_filter = " AND gl.department_id = ? ";
   }
 
     $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
   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
        WHERE (ar.id IS NOT NULL)
          AND $fromto
          $trans_id_filter

+         $ar_itime_filter

          $ar_department_id_filter
          $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
        WHERE (ap.id IS NOT NULL)
          AND $fromto
          $trans_id_filter

+         $ap_itime_filter

          $ap_department_id_filter
          $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
        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;
          $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);
   }
 
   my $sth = prepare_execute_query($form, $self->dbh, $query, @query_args);

diff --git a/doc/changelog b/doc/changelog
index d10b4c89b5508158e0e93aa8640f769a5d25274b..623aa81b76ee6e8293c0112a2d290ce11b52e758 100644 (file)
--- 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.
 
   - 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:
 2017-07-17 - Release 3.5.0
 
 große Features:

diff --git a/t/datev/invoices.t b/t/datev/invoices.t
index 5a33d5f0cf29fa709c58b795b366d9058c0d0d9d..45c68ca6d9794f1159e340c2ec0e12c4f546497e 100644 (file)
--- a/t/datev/invoices.t
+++ b/t/datev/invoices.t
@@ -16,10 +16,13 @@ Support::TestSetup::login();
 
 clear_up();
 
 
 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 $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(
 
 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",
 
 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),
   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";
 
                                          },
                                        ], "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",
 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),
   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',
 
 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),
   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(
 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,
   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 };
 $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();
 
 done_testing();
 clear_up();


@@ -120,6 +152,4 @@ sub clear_up {
   SL::DB::Manager::Part->delete_all(          all => 1);
 };
 
   SL::DB::Manager::Part->delete_all(          all => 1);
 };
 

-

 1;
 1;

-

diff --git a/templates/webpages/datev/export_bewegungsdaten.html b/templates/webpages/datev/export_bewegungsdaten.html
index 441d83df9ebcc9f83f9d58d2416d217024751230..ae9c13eddca7af44b615e306c9dcf3958e131c35 100644 (file)
--- a/templates/webpages/datev/export_bewegungsdaten.html
+++ b/templates/webpages/datev/export_bewegungsdaten.html
@@ -54,25 +54,40 @@
       </table>
     </td>
   </tr>
       </table>
     </td>
   </tr>

-  [% IF ALL_DEPARTMENTS.as_list.size %]
-  <tr>
-    <th align=left>[% 'Department' | $T8 %]</th>
-  </tr>

   <tr>
   <tr>

-    <td>
-       <table>
-        <tr>
-          <td align=left>[% 'Department' | $T8 %]</td>
-          <td align=left></td>
-          <td>[% L.select_tag('department_id', ALL_DEPARTMENTS, title_key = 'description', with_empty = 1) %]</td>
-        </tr>
-       </table>
-    </td>
+   <td><hr size=1 noshade></td>
+   </tr>
+   <tr>
+     <td>
+        <table>
+         <tr>
+           <td align=left>[% 'Gldate' | $T8 %] [% 'From' | $T8 %]</td>
+           <td align=left></td>
+           <td>[% L.date_tag('gldatefrom') %]</td>
+         </tr>
+        </table>
+     </td>
+   </tr>
+   <tr>
+   <tr>
+    <td><hr size=1 noshade></td>
+   </tr>
+  [% IF ALL_DEPARTMENTS.as_list.size %]
+   <tr>
+     <td>
+        <table>
+         <tr>
+           <td align=left>[% 'Department' | $T8 %]</td>
+           <td align=left></td>
+           <td>[% L.select_tag('department_id', ALL_DEPARTMENTS, title_key = 'description', with_empty = 1) %]</td>
+         </tr>
+        </table>
+     </td>
+   </tr>
+   <tr>
+  <td><hr size=3 noshade></td>

   </tr>
   [% END %]
   </tr>
   [% END %]

-  <tr>
-    <td><hr size=3 noshade></td>
-  </tr>

 </table>
 
 <input type=hidden name=beraternr value="[% beraternr %]">
 </table>
 
 <input type=hidden name=beraternr value="[% beraternr %]">