1 package SL::DB::Invoice;
6 use List::Util qw(first sum);
8 use Rose::DB::Object::Helpers qw(has_loaded_related forget_related);
9 use SL::DB::MetaSetup::Invoice;
10 use SL::DB::Manager::Invoice;
11 use SL::DB::Helper::Payment qw(:ALL);
12 use SL::DB::Helper::AttrHTML;
13 use SL::DB::Helper::AttrSorted;
14 use SL::DB::Helper::FlattenToForm;
15 use SL::DB::Helper::LinkedRecords;
16 use SL::DB::Helper::PriceTaxCalculator;
17 use SL::DB::Helper::PriceUpdater;
18 use SL::DB::Helper::TransNumberGenerator;
19 use SL::Locale::String qw(t8);
20 use SL::DB::CustomVariable;
22 __PACKAGE__->meta->add_relationship(
24 type => 'one to many',
25 class => 'SL::DB::InvoiceItem',
26 column_map => { id => 'trans_id' },
28 with_objects => [ 'part' ]
32 type => 'one to many',
33 class => 'SL::DB::Invoice',
34 column_map => { id => 'storno_id' },
36 sepa_export_items => {
37 type => 'one to many',
38 class => 'SL::DB::SepaExportItem',
39 column_map => { id => 'ar_id' },
40 manager_args => { with_objects => [ 'sepa_export' ] }
43 type => 'many to many',
44 map_class => 'SL::DB::SepaExportItem',
46 map_to => 'sepa_export',
50 class => 'SL::DB::Shipto',
51 column_map => { id => 'trans_id' },
52 query_args => [ module => 'AR' ],
55 type => 'one to many',
56 class => 'SL::DB::AccTransaction',
57 column_map => { id => 'trans_id' },
59 with_objects => [ 'chart' ],
60 sort_by => 'acc_trans_id ASC',
64 type => 'one to many',
65 class => 'SL::DB::Dunning',
66 column_map => { id => 'trans_id' },
67 manager_args => { with_objects => [ 'dunnings' ] }
71 __PACKAGE__->meta->initialize;
73 __PACKAGE__->attr_html('notes');
74 __PACKAGE__->attr_sorted('items');
76 __PACKAGE__->before_save('_before_save_set_invnumber');
80 sub _before_save_set_invnumber {
83 $self->create_trans_number if !$self->invnumber;
90 sub items { goto &invoiceitems; }
91 sub add_items { goto &add_invoiceitems; }
92 sub record_number { goto &invnumber; };
95 # For compatibility with Order, DeliveryOrder
96 croak 'not an accessor' if @_ > 1;
100 # it is assumed, that ordnumbers are unique here.
101 sub first_order_by_ordnumber {
104 my $orders = SL::DB::Manager::Order->get_all(
106 ordnumber => $self->ordnumber,
111 return first { $_->is_type('sales_order') } @{ $orders };
114 sub abschlag_percentage {
116 my $order = $self->first_order_by_ordnumber or return;
117 my $order_amount = $order->netamount or return;
118 return $self->abschlag
119 ? $self->netamount / $order_amount
125 die 'not a setter method' if @_;
127 return ($self->amount || 0) - ($self->netamount || 0);
130 __PACKAGE__->meta->make_attr_helpers(taxamount => 'numeric(15,5)');
134 return $self->paid >= $self->amount;
137 sub _clone_orderitem_delivery_order_item_cvar {
140 my $cloned = $_->clone_and_reset;
141 $cloned->sub_module('invoice');
147 my ($class, $source, %params) = @_;
149 croak("Unsupported source object type '" . ref($source) . "'") unless ref($source) =~ m/^ SL::DB:: (?: Order | DeliveryOrder ) $/x;
150 croak("Cannot create invoices for purchase records") unless $source->customer_id;
152 require SL::DB::Employee;
154 my (@columns, @item_columns, $item_parent_id_column, $item_parent_column);
156 if (ref($source) eq 'SL::DB::Order') {
157 @columns = qw(quonumber delivery_customer_id delivery_vendor_id);
158 @item_columns = qw(subtotal);
160 $item_parent_id_column = 'trans_id';
161 $item_parent_column = 'order';
164 @columns = qw(donumber);
166 $item_parent_id_column = 'delivery_order_id';
167 $item_parent_column = 'delivery_order';
170 my $terms = $source->can('payment_id') ? $source->payment_terms : undef;
171 $terms = $source->customer->payment_terms if !defined $terms && $source->customer;
173 my %args = ( map({ ( $_ => $source->$_ ) } qw(customer_id taxincluded shippingpoint shipvia notes intnotes salesman_id cusordnumber ordnumber department_id
174 cp_id language_id taxzone_id globalproject_id transaction_description currency_id delivery_term_id), @columns),
175 transdate => DateTime->today_local,
176 gldate => DateTime->today_local,
177 duedate => $terms ? $terms->calc_date(reference_date => DateTime->today_local) : DateTime->today_local,
182 employee_id => (SL::DB::Manager::Employee->current || SL::DB::Employee->new(id => $source->employee_id))->id,
185 $args{payment_id} = ( $terms ? $terms->id : $source->payment_id);
187 if ($source->type =~ /_order$/) {
188 $args{deliverydate} = $source->reqdate;
189 $args{orddate} = $source->transdate;
191 $args{quodate} = $source->transdate;
194 # Custom shipto addresses (the ones specific to the sales/purchase
195 # record and not to the customer/vendor) are only linked from shipto
196 # → ar. Meaning ar.shipto_id will not be filled in that
198 if (!$source->shipto_id && $source->id) {
199 $args{custom_shipto} = $source->custom_shipto->clone($class) if $source->can('custom_shipto') && $source->custom_shipto;
202 $args{shipto_id} = $source->shipto_id;
205 my $invoice = $class->new(%args);
206 $invoice->assign_attributes(%{ $params{attributes} }) if $params{attributes};
207 my $items = delete($params{items}) || $source->items_sorted;
211 my $source_item = $_;
212 my $source_item_id = $_->$item_parent_id_column;
213 my @custom_variables = map { _clone_orderitem_delivery_order_item_cvar($_) } @{ $source_item->custom_variables };
215 $item_parents{$source_item_id} ||= $source_item->$item_parent_column;
216 my $item_parent = $item_parents{$source_item_id};
217 my $current_invoice_item =
218 SL::DB::InvoiceItem->new(map({ ( $_ => $source_item->$_ ) }
219 qw(parts_id description qty sellprice discount project_id serialnumber pricegroup_id transdate cusordnumber unit
220 base_qty longdescription lastcost price_factor_id active_discount_source active_price_source), @item_columns),
221 deliverydate => $source_item->reqdate,
222 fxsellprice => $source_item->sellprice,
223 custom_variables => \@custom_variables,
224 ordnumber => ref($item_parent) eq 'SL::DB::Order' ? $item_parent->ordnumber : $source_item->ordnumber,
225 donumber => ref($item_parent) eq 'SL::DB::DeliveryOrder' ? $item_parent->donumber : $source_item->can('donumber') ? $source_item->donumber : '',
228 $current_invoice_item->{"converted_from_orderitems_id"} = $_->{id} if ref($item_parent) eq 'SL::DB::Order';
229 $current_invoice_item->{"converted_from_delivery_order_items_id"} = $_->{id} if ref($item_parent) eq 'SL::DB::DeliveryOrder';
230 $current_invoice_item;
233 @items = grep { $params{item_filter}->($_) } @items if $params{item_filter};
234 @items = grep { $_->qty * 1 } @items if $params{skip_items_zero_qty};
235 @items = grep { $_->qty >=0 } @items if $params{skip_items_negative_qty};
237 $invoice->invoiceitems(\@items);
243 my ($self, %params) = @_;
245 die "not an invoice" unless $self->invoice;
247 require SL::DB::Chart;
248 if (!$params{ar_id}) {
250 if ($::instance_conf->get_ar_chart_id) {
251 $chart = SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_ar_chart_id);
253 $chart = SL::DB::Manager::Chart->get_all(query => [ SL::DB::Manager::Chart->link_filter('AR') ],
257 croak("No AR chart found and no parameter 'ar_id' given") unless $chart;
258 $params{ar_id} = $chart->id;
261 if (!$self->db->with_transaction(sub {
262 my %data = $self->calculate_prices_and_taxes;
264 $self->_post_create_assemblyitem_entries($data{assembly_items});
267 $self->_post_add_acctrans($data{amounts_cogs});
268 $self->_post_add_acctrans($data{amounts});
269 $self->_post_add_acctrans($data{taxes});
271 $self->_post_add_acctrans({ $params{ar_id} => $self->amount * -1 });
273 $self->_post_update_allocated($data{allocated});
275 $self->_post_book_rounding($data{rounding});
279 $::lxdebug->message(LXDebug->WARN(), "convert_to_invoice failed: " . join("\n", (split(/\n/, $self->db->error))[0..2]));
286 sub _post_add_acctrans {
287 my ($self, $entries) = @_;
289 my $default_tax_id = SL::DB::Manager::Tax->find_by(taxkey => 0)->id;
292 require SL::DB::AccTransaction;
293 require SL::DB::Chart;
294 while (my ($chart_id, $spec) = each %{ $entries }) {
295 $spec = { taxkey => 0, tax_id => $default_tax_id, amount => $spec } unless ref $spec;
296 $chart_link = SL::DB::Manager::Chart->find_by(id => $chart_id)->{'link'};
299 SL::DB::AccTransaction->new(trans_id => $self->id,
300 chart_id => $chart_id,
301 amount => $spec->{amount},
302 tax_id => $spec->{tax_id},
303 taxkey => $spec->{taxkey},
304 project_id => $self->globalproject_id,
305 transdate => $self->transdate,
306 chart_link => $chart_link)->save;
310 sub _post_book_rounding {
311 my ($self, $rounding) = @_;
313 my $tax_id = SL::DB::Manager::Tax->find_by(taxkey => 0)->id;
314 my $rnd_accno = $rounding == 0 ? 0
315 : $rounding > 0 ? SL::DB::Default->get->rndgain_accno_id
316 : SL::DB::Default->get->rndloss_accno_id
318 if ($rnd_accno != 0) {
319 SL::DB::AccTransaction->new(trans_id => $self->id,
320 chart_id => $rnd_accno,
324 project_id => $self->globalproject_id,
325 transdate => $self->transdate,
326 chart_link => $rnd_accno)->save;
330 sub add_ar_amount_row {
331 my ($self, %params ) = @_;
333 # only allow this method for ar invoices (Debitorenbuchung)
334 die "not an ar invoice" if $self->invoice and not $self->customer_id;
336 die "add_ar_amount_row needs a chart object as chart param" unless $params{chart} && $params{chart}->isa('SL::DB::Chart');
337 die "chart must be an AR_amount chart" unless $params{chart}->link =~ /AR_amount/;
342 my ($netamount,$taxamount);
344 $netamount = $params{amount} * 1;
345 my $tax = SL::DB::Manager::Tax->find_by(id => $params{tax_id}) || die "Can't find tax with id " . $params{tax_id};
347 if ( $tax and $tax->rate != 0 ) {
348 ($netamount, $taxamount) = Form->calculate_tax($params{amount}, $tax->rate, $self->taxincluded, $roundplaces);
350 next unless $netamount; # netamount mustn't be zero
352 my $sign = $self->customer_id ? 1 : -1;
353 my $acc = SL::DB::AccTransaction->new(
354 amount => $netamount * $sign,
355 chart_id => $params{chart}->id,
356 chart_link => $params{chart}->link,
357 transdate => $self->transdate,
358 taxkey => $tax->taxkey,
360 project_id => $params{project_id},
363 $self->add_transactions( $acc );
364 push( @$acc_trans, $acc );
367 my $acc = SL::DB::AccTransaction->new(
368 amount => $taxamount * $sign,
369 chart_id => $tax->chart_id,
370 chart_link => $tax->chart->link,
371 transdate => $self->transdate,
372 taxkey => $tax->taxkey,
375 $self->add_transactions( $acc );
376 push( @$acc_trans, $acc );
382 my ($self, %params) = @_;
383 # to be called after adding all AR_amount rows, adds an AR row
385 # only allow this method for ar invoices (Debitorenbuchung)
386 die if $self->invoice and not $self->customer_id;
387 die "create_ar_row needs a chart object as a parameter" unless $params{chart} and ref($params{chart}) eq 'SL::DB::Chart';
389 my @transactions = @{$self->transactions};
390 # die "invoice has no acc_transactions" unless scalar @transactions > 0;
391 return 0 unless scalar @transactions > 0;
393 my $chart = $params{chart} || SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_ar_chart_id);
394 die "illegal chart in create_ar_row" unless $chart;
396 die "receivables chart must have link 'AR'" unless $chart->link eq 'AR';
400 # hardcoded entry for no tax: tax_id and taxkey should be 0
401 my $tax = SL::DB::Manager::Tax->find_by(id => 0, taxkey => 0) || die "Can't find tax with id 0 and taxkey 0";
403 my $sign = $self->customer_id ? -1 : 1;
404 my $acc = SL::DB::AccTransaction->new(
405 amount => $self->amount * $sign,
406 chart_id => $params{chart}->id,
407 chart_link => $params{chart}->link,
408 transdate => $self->transdate,
409 taxkey => $tax->taxkey,
412 $self->add_transactions( $acc );
413 push( @$acc_trans, $acc );
417 sub validate_acc_trans {
418 my ($self, %params) = @_;
419 # should be able to check unsaved invoice objects with several acc_trans lines
421 die "validate_acc_trans can't check invoice object with empty transactions" unless $self->transactions;
423 my @transactions = @{$self->transactions};
424 # die "invoice has no acc_transactions" unless scalar @transactions > 0;
425 return 0 unless scalar @transactions > 0;
426 return 0 unless $self->has_loaded_related('transactions');
427 if ( $params{debug} ) {
428 printf("starting validatation of invoice %s with trans_id %s and taxincluded %s\n", $self->invnumber, $self->id, $self->taxincluded);
429 foreach my $acc ( @transactions ) {
430 printf("chart: %s amount: %s tax_id: %s link: %s\n", $acc->chart->accno, $acc->amount, $acc->tax_id, $acc->chart->link);
434 my $acc_trans_sum = sum map { $_->amount } @transactions;
436 unless ( $::form->round_amount($acc_trans_sum, 10) == 0 ) {
437 my $string = "sum of acc_transactions isn't 0: $acc_trans_sum\n";
439 if ( $params{debug} ) {
440 foreach my $trans ( @transactions ) {
441 $string .= sprintf(" %s %s %s\n", $trans->chart->accno, $trans->taxkey, $trans->amount);
447 # only use the first AR entry, so it also works for paid invoices
448 my @ar_transactions = map { $_->amount } grep { $_->chart_link eq 'AR' } @transactions;
449 my $ar_sum = $ar_transactions[0];
450 # my $ar_sum = sum map { $_->amount } grep { $_->chart_link eq 'AR' } @transactions;
452 unless ( $::form->round_amount($ar_sum * -1,2) == $::form->round_amount($self->amount,2) ) {
453 if ( $params{debug} ) {
454 printf("debug: (ar_sum) %s = %s (amount)\n", $::form->round_amount($ar_sum * -1,2) , $::form->round_amount($self->amount, 2) );
455 foreach my $trans ( @transactions ) {
456 printf(" %s %s %s %s\n", $trans->chart->accno, $trans->taxkey, $trans->amount, $trans->chart->link);
459 die sprintf("sum of ar (%s) isn't equal to invoice amount (%s)", $::form->round_amount($ar_sum * -1,2), $::form->round_amount($self->amount,2));
465 sub recalculate_amounts {
466 my ($self, %params) = @_;
467 # calculate and set amount and netamount from acc_trans objects
469 croak ("Can only recalculate amounts for ar transactions") if $self->invoice;
471 return undef unless $self->has_loaded_related('transactions');
473 my ($netamount, $taxamount);
475 my @transactions = @{$self->transactions};
477 foreach my $acc ( @transactions ) {
478 $netamount += $acc->amount if $acc->chart->link =~ /AR_amount/;
479 $taxamount += $acc->amount if $acc->chart->link =~ /AR_tax/;
482 $self->amount($netamount+$taxamount);
483 $self->netamount($netamount);
487 sub _post_create_assemblyitem_entries {
488 my ($self, $assembly_entries) = @_;
490 my $items = $self->invoiceitems;
494 foreach my $item (@{ $items }) {
495 next if $item->assemblyitem;
497 push @new_items, $item;
500 foreach my $assembly_item (@{ $assembly_entries->[$item_idx] || [ ] }) {
501 push @new_items, SL::DB::InvoiceItem->new(parts_id => $assembly_item->{part},
502 description => $assembly_item->{part}->description,
503 unit => $assembly_item->{part}->unit,
504 qty => $assembly_item->{qty},
505 allocated => $assembly_item->{allocated},
508 assemblyitem => 't');
512 $self->invoiceitems(\@new_items);
515 sub _post_update_allocated {
516 my ($self, $allocated) = @_;
518 while (my ($invoice_id, $diff) = each %{ $allocated }) {
519 SL::DB::Manager::InvoiceItem->update_all(set => { allocated => { sql => "allocated + $diff" } },
520 where => [ id => $invoice_id ]);
527 return 'ar_transaction' if !$self->invoice;
528 return 'credit_note' if $self->type eq 'credit_note' && $self->amount < 0 && !$self->storno;
529 return 'invoice_storno' if $self->type ne 'credit_note' && $self->amount < 0 && $self->storno;
530 return 'credit_note_storno' if $self->type eq 'credit_note' && $self->amount > 0 && $self->storno;
534 sub displayable_state {
537 return $self->closed ? $::locale->text('closed') : $::locale->text('open');
540 sub displayable_type {
543 return t8('AR Transaction') if $self->invoice_type eq 'ar_transaction';
544 return t8('Credit Note') if $self->invoice_type eq 'credit_note';
545 return t8('Invoice') . "(" . t8('Storno') . ")" if $self->invoice_type eq 'invoice_storno';
546 return t8('Credit Note') . "(" . t8('Storno') . ")" if $self->invoice_type eq 'credit_note_storno';
547 return t8('Invoice');
550 sub displayable_name {
551 join ' ', grep $_, map $_[0]->$_, qw(displayable_type record_number);
557 return t8('AR Transaction (abbreviation)') if $self->invoice_type eq 'ar_transaction';
558 return t8('Credit note (one letter abbreviation)') if $self->invoice_type eq 'credit_note';
559 return t8('Invoice (one letter abbreviation)') . "(" . t8('Storno (one letter abbreviation)') . ")" if $self->invoice_type eq 'invoice_storno';
560 return t8('Credit note (one letter abbreviation)') . "(" . t8('Storno (one letter abbreviation)') . ")" if $self->invoice_type eq 'credit_note_storno';
561 return t8('Invoice (one letter abbreviation)');
564 sub oneline_summary {
567 return sprintf("%s: %s %s %s (%s)", $self->abbreviation, $self->invnumber, $self->customer->name,
568 $::form->format_amount(\%::myconfig, $self->amount,2), $self->transdate->to_kivitendo);
587 $html = $self->presenter->sales_invoice(display => 'inline') if $self->invoice;
588 $html = $self->presenter->ar_transaction(display => 'inline') if !$self->invoice;
596 $self->update_attributes(paid => $self->amount);
609 SL::DB::Invoice: Rose model for invoices (table "ar")
615 =item C<new_from $source, %params>
617 Creates a new C<SL::DB::Invoice> instance and copies as much
618 information from C<$source> as possible. At the moment only sales
619 orders and sales quotations are supported as sources.
621 The conversion copies order items into invoice items. Dates are copied
622 as appropriate, e.g. the C<transdate> field from an order will be
623 copied into the invoice's C<orddate> field.
625 C<%params> can include the following options:
631 An optional array reference of RDBO instances for the items to use. If
632 missing then the method C<items_sorted> will be called on
633 C<$source>. This option can be used to override the sorting, to
634 exclude certain positions or to add additional ones.
636 =item C<skip_items_negative_qty>
638 If trueish then items with a negative quantity are skipped. Items with
639 a quantity of 0 are not affected by this option.
641 =item C<skip_items_zero_qty>
643 If trueish then items with a quantity of 0 are skipped.
647 An optional code reference that is called for each item with the item
648 as its sole parameter. Items for which the code reference returns a
649 falsish value will be skipped.
653 An optional hash reference. If it exists then it is passed to C<new>
654 allowing the caller to set certain attributes for the new invoice.
655 For example to set a different transdate (default is the current date),
656 call the method like this:
659 $params{attributes}{transdate} = '28.08.2015';
660 $invoice = SL::DB::Invoice->new_from($self, %params)->post || die;
664 Amounts, prices and taxes are not
665 calculated. L<SL::DB::Helper::PriceTaxCalculator::calculate_prices_and_taxes>
666 can be used for this.
668 The object returned is not saved.
670 =item C<post %params>
672 Posts the invoice. Required parameters are:
678 The ID of the accounts receivable chart the invoice's amounts are
679 posted to. If it is not set then the first chart configured for
680 accounts receivables is used.
684 This function implements several steps:
688 =item 1. It calculates all prices, amounts and taxes by calling
689 L<SL::DB::Helper::PriceTaxCalculator::calculate_prices_and_taxes>.
691 =item 2. A new and unique invoice number is created.
693 =item 3. All amounts for costs of goods sold are recorded in
696 =item 4. All amounts for parts, services and assemblies are recorded
697 in C<acc_trans> with their respective charts. This is determined by
698 the part's buchungsgruppen.
700 =item 5. The total amount is posted to the accounts receivable chart
701 and recorded in C<acc_trans>.
703 =item 6. Items in C<invoice> are updated according to their allocation
704 status (regarding costs of goods sold). Will only be done if
705 kivitendo is not configured to use Einnahmenüberschussrechnungen.
707 =item 7. The invoice and its items are saved.
711 Returns C<$self> on success and C<undef> on failure. The whole process
712 is run inside a transaction. If it fails then nothing is saved to or
713 changed in the database. A new transaction is only started if none are
716 =item C<basic_info $field>
718 See L<SL::DB::Object::basic_info>.
722 Returns 1 or 0, depending on whether the invoice is closed or not. Currently
723 invoices that are overpaid also count as closed and credit notes in general.
725 =item C<recalculate_amounts %params>
727 Calculate and set amount and netamount from acc_trans objects by summing up the
728 values of acc_trans objects with AR_amount and AR_tax link charts.
729 amount and netamount are set to the calculated values.
731 =item C<validate_acc_trans>
733 Checks if the sum of all associated acc_trans objects is 0 and checks whether
734 the amount of the AR acc_transaction matches the AR amount. Only the first AR
735 line is checked, because the sum of all AR lines is 0 for paid invoices.
739 Can be called with a debug parameter which writes debug info to STDOUT, which is
740 useful in console mode or while writing tests.
742 my $ar = SL::DB::Manager::Invoice->get_first();
743 $ar->validate_acc_trans(debug => 1);
745 =item C<create_ar_row %params>
747 Creates a new acc_trans entry for the receivable (AR) entry of an existing AR
748 invoice object, which already has some income and tax acc_trans entries.
750 The acc_trans entry is also returned inside an array ref.
756 =item * chart as an RDBO object, e.g. for bank. Must be a 'paid' chart.
760 Currently the amount of the invoice object is used for the acc_trans amount.
761 Use C<recalculate_amounts> before calling this method if amount isn't known
762 yet or you didn't set it manually.
764 =item C<add_ar_amount_row %params>
766 Add a new entry for an existing AR invoice object. Creates an acc_trans entry,
767 and also adds an acc_trans tax entry, if the tax has an associated tax chart.
768 Also all acc_trans entries that were created are returned inside an array ref.
774 =item * chart as an RDBO object, should be an income chart (link = AR_amount)
782 =item C<mark_as_paid>
784 Marks the invoice as paid by setting its C<paid> member to the value of C<amount>.
790 As explained in the new_from example, it is possible to set transdate to a new value.
791 From a user / programm point of view transdate is more than holy and there should be
792 some validity checker available for controller code. At least the same logic like in
793 Form.pm from ar.pl should be available:
794 # see old stuff ar.pl post
795 #$form->error($locale->text('Cannot post transaction above the maximum future booking date!'))
796 # if ($form->date_max_future($transdate, \%myconfig));
797 #$form->error($locale->text('Cannot post transaction for a closed period!')) if ($form->date_closed($form->{"transdate"}, \%myconfig));
801 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>