my $self = shift;
if (@_) {
- $self->{warnings} = \@_;
+ $self->{warnings} = [@_];
} else {
return $self->{warnings};
}
}
+sub use_pk {
+ my $self = shift;
+
+ if (@_) {
+ $self->{use_pk} = $_[0];
+ }
+
+ return $self->{use_pk};
+}
+
sub accnofrom {
my $self = shift;
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,
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,
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 "") {
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
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();
}
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