X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDATEV.pm;h=b0e9936e78f554cd375ec9d430f72889bb9e4910;hb=48baa5ebf4441c31d45d0111a0fe8b88d10a4dcd;hp=9418c483970ab13993bffb6b9a633766ed17c059;hpb=84345dad925d4577c9b91aaf2ce7fbd33bcf262c;p=kivitendo-erp.git

diff --git a/SL/DATEV.pm b/SL/DATEV.pm
index 9418c4839..b0e9936e7 100644
--- a/SL/DATEV.pm
+++ b/SL/DATEV.pm
@@ -225,6 +225,16 @@ sub warnings {
   }
 }
 
+sub use_pk {
+ my $self = shift;
+
+ if (@_) {
+   $self->{use_pk} = $_[0];
+ }
+
+ return $self->{use_pk};
+}
+
 sub accnofrom {
  my $self = shift;
 
@@ -440,11 +450,18 @@ sub generate_datev_data {
 
   my %all_taxchart_ids = selectall_as_map($form, $self->dbh, qq|SELECT DISTINCT chart_id, TRUE AS is_set FROM tax|, 'chart_id', 'is_set');
 
+  my $ar_accno = "c.accno";
+  my $ap_accno = "c.accno";
+  if ( $self->use_pk ) {
+    $ar_accno = "CASE WHEN ac.chart_link = 'AR' THEN ct.customernumber ELSE c.accno END as accno";
+    $ap_accno = "CASE WHEN ac.chart_link = 'AP' THEN ct.vendornumber   ELSE c.accno END as accno";
+  }
+
   my $query    =
     qq|SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ar.id, ac.amount, ac.taxkey, ac.memo,
          ar.invnumber, ar.duedate, ar.amount as umsatz, ar.deliverydate, ar.itime::date,
          ct.name, ct.ustid, ct.customernumber AS vcnumber, ct.id AS customer_id, NULL AS vendor_id,
-         c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+         $ar_accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
          ar.invoice,
          t.rate AS taxrate, t.taxdescription,
          'ar' as table,
@@ -473,7 +490,7 @@ sub generate_datev_data {
        SELECT ac.acc_trans_id, ac.transdate, ac.gldate, ac.trans_id,ap.id, ac.amount, ac.taxkey, ac.memo,
          ap.invnumber, ap.duedate, ap.amount as umsatz, ap.deliverydate, ap.itime::date,
          ct.name, ct.ustid, ct.vendornumber AS vcnumber, NULL AS customer_id, ct.id AS vendor_id,
-         c.accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
+         $ap_accno, c.description AS accname, c.taxkey_id as charttax, c.datevautomatik, c.id, ac.chart_link AS link,
          ap.invoice,
          t.rate AS taxrate, t.taxdescription,
          'ap' as table,
@@ -956,6 +973,7 @@ sub generate_datev_lines {
 
     if ($trans_lines >= 2) {
 
+      # Personenkontenerweiterung: accno has already been replaced if use_pk was set
       $datev_data{'gegenkonto'} = $transaction->[$haben]->{'accno'};
       $datev_data{'konto'}      = $transaction->[$soll]->{'accno'};
       if ($transaction->[$haben]->{'invnumber'} ne "") {
@@ -1397,7 +1415,7 @@ sub csv_buchungsexport {
     foreach my $column (@csv_columns) {
       if (exists $column->{max_length} && $column->{kivi_datev_name} ne 'not yet implemented') {
         # check max length
-        die "Incorrect lenght of field" if length($row->{ $column->{kivi_datev_name} }) > $column->{max_length};
+        die "Incorrect length of field" if length($row->{ $column->{kivi_datev_name} }) > $column->{max_length};
       }
       if (exists $column->{valid_check} && $column->{kivi_datev_name} ne 'not yet implemented') {
         # more checks, listed as user warnings
@@ -1442,6 +1460,22 @@ sub _csv_buchungsexport_to_file {
 
   return { download_token => $self->download_token, filenames => $params{filename} };
 }
+
+sub check_vcnumbers_are_valid_pk_numbers {
+  my ($self) = @_;
+
+  my $length_of_accounts = length(SL::DB::Manager::Chart->get_first(where => [charttype => 'A'])->accno) // 4;
+  my $pk_length = $length_of_accounts + 1;
+  my $query = <<"SQL";
+   SELECT customernumber AS vcnumber FROM customer WHERE customernumber !~ '^[[:digit:]]{$pk_length}\$'
+   UNION
+   SELECT vendornumber   AS vcnumber FROM vendor   WHERE vendornumber   !~ '^[[:digit:]]{$pk_length}\$'
+   LIMIT 1;
+SQL
+  my ($has_non_pk_accounts)  = selectrow_query($::form, SL::DB->client->dbh, $query);
+  return defined $has_non_pk_accounts ? 0 : 1;
+}
+
 sub DESTROY {
   clean_temporary_directories();
 }
@@ -1667,6 +1701,19 @@ Generates a CSV-file with the same encodings as defined in DATEV Format CSV 2015
 
 Usage: _csv_buchungsexport_to_file($self, data => $self->csv_buchungsexport);
 
+=item check_vcnumbers_are_valid_pk_numbers
+
+Returns 1 if all vcnumbers are suitable for the DATEV export, 0 if not.
+
+Finds the default length of charts (e.g. 4), adds 1 for the pk chart length
+(e.g. 5), and checks the database for any customers or vendors whose customer-
+or vendornumber doesn't consist of only numbers with exactly that length. E.g.
+for a chart length of four "10001" would be ok, but not "10001b" or "1000".
+
+All vcnumbers are checked, obsolete customers or vendors aren't exempt.
+
+There is also no check for the typical customer range 10000-69999 and the
+typical vendor range 70000-99999.
 
 =back