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',
65 __PACKAGE__->meta->initialize;
67 __PACKAGE__->attr_html('notes');
68 __PACKAGE__->attr_sorted('items');
70 __PACKAGE__->before_save('_before_save_set_invnumber');
74 sub _before_save_set_invnumber {
77 $self->create_trans_number if !$self->invnumber;
84 sub items { goto &invoiceitems; }
85 sub add_items { goto &add_invoiceitems; }
86 sub record_number { goto &invnumber; };
89 # For compatibility with Order, DeliveryOrder
90 croak 'not an accessor' if @_ > 1;
94 # it is assumed, that ordnumbers are unique here.
95 sub first_order_by_ordnumber {
98 my $orders = SL::DB::Manager::Order->get_all(
100 ordnumber => $self->ordnumber,
105 return first { $_->is_type('sales_order') } @{ $orders };
108 sub abschlag_percentage {
110 my $order = $self->first_order_by_ordnumber or return;
111 my $order_amount = $order->netamount or return;
112 return $self->abschlag
113 ? $self->netamount / $order_amount
119 die 'not a setter method' if @_;
121 return ($self->amount || 0) - ($self->netamount || 0);
124 __PACKAGE__->meta->make_attr_helpers(taxamount => 'numeric(15,5)');
128 return $self->paid >= $self->amount;
131 sub _clone_orderitem_delivery_order_item_cvar {
134 my $cloned = $_->clone_and_reset;
135 $cloned->sub_module('invoice');
141 my ($class, $source, %params) = @_;
143 croak("Unsupported source object type '" . ref($source) . "'") unless ref($source) =~ m/^ SL::DB:: (?: Order | DeliveryOrder ) $/x;
144 croak("Cannot create invoices for purchase records") unless $source->customer_id;
146 require SL::DB::Employee;
148 my (@columns, @item_columns, $item_parent_id_column, $item_parent_column);
150 if (ref($source) eq 'SL::DB::Order') {
151 @columns = qw(quonumber delivery_customer_id delivery_vendor_id);
152 @item_columns = qw(subtotal);
154 $item_parent_id_column = 'trans_id';
155 $item_parent_column = 'order';
158 @columns = qw(donumber);
160 $item_parent_id_column = 'delivery_order_id';
161 $item_parent_column = 'delivery_order';
164 my $terms = $source->can('payment_id') ? $source->payment_terms : undef;
165 $terms = $source->customer->payment_terms if !defined $terms && $source->customer;
167 my %args = ( map({ ( $_ => $source->$_ ) } qw(customer_id taxincluded shippingpoint shipvia notes intnotes salesman_id cusordnumber ordnumber department_id
168 cp_id language_id taxzone_id globalproject_id transaction_description currency_id delivery_term_id), @columns),
169 transdate => DateTime->today_local,
170 gldate => DateTime->today_local,
171 duedate => $terms ? $terms->calc_date(reference_date => DateTime->today_local) : DateTime->today_local,
176 employee_id => (SL::DB::Manager::Employee->current || SL::DB::Employee->new(id => $source->employee_id))->id,
179 $args{payment_id} = ( $terms ? $terms->id : $source->payment_id);
181 if ($source->type =~ /_order$/) {
182 $args{deliverydate} = $source->reqdate;
183 $args{orddate} = $source->transdate;
185 $args{quodate} = $source->transdate;
188 # Custom shipto addresses (the ones specific to the sales/purchase
189 # record and not to the customer/vendor) are only linked from shipto
190 # → ar. Meaning ar.shipto_id will not be filled in that
192 if (!$source->shipto_id && $source->id) {
193 $args{custom_shipto} = $source->custom_shipto->clone($class) if $source->can('custom_shipto') && $source->custom_shipto;
196 $args{shipto_id} = $source->shipto_id;
199 my $invoice = $class->new(%args);
200 $invoice->assign_attributes(%{ $params{attributes} }) if $params{attributes};
201 my $items = delete($params{items}) || $source->items_sorted;
205 my $source_item = $_;
206 my $source_item_id = $_->$item_parent_id_column;
207 my @custom_variables = map { _clone_orderitem_delivery_order_item_cvar($_) } @{ $source_item->custom_variables };
209 $item_parents{$source_item_id} ||= $source_item->$item_parent_column;
210 my $item_parent = $item_parents{$source_item_id};
211 my $current_invoice_item =
212 SL::DB::InvoiceItem->new(map({ ( $_ => $source_item->$_ ) }
213 qw(parts_id description qty sellprice discount project_id serialnumber pricegroup_id transdate cusordnumber unit
214 base_qty longdescription lastcost price_factor_id active_discount_source active_price_source), @item_columns),
215 deliverydate => $source_item->reqdate,
216 fxsellprice => $source_item->sellprice,
217 custom_variables => \@custom_variables,
218 ordnumber => ref($item_parent) eq 'SL::DB::Order' ? $item_parent->ordnumber : $source_item->ordnumber,
219 donumber => ref($item_parent) eq 'SL::DB::DeliveryOrder' ? $item_parent->donumber : $source_item->can('donumber') ? $source_item->donumber : '',
222 $current_invoice_item->{"converted_from_orderitems_id"} = $_->{id} if ref($item_parent) eq 'SL::DB::Order';
223 $current_invoice_item->{"converted_from_delivery_order_items_id"} = $_->{id} if ref($item_parent) eq 'SL::DB::DeliveryOrder';
224 $current_invoice_item;
227 @items = grep { $params{item_filter}->($_) } @items if $params{item_filter};
228 @items = grep { $_->qty * 1 } @items if $params{skip_items_zero_qty};
229 @items = grep { $_->qty >=0 } @items if $params{skip_items_negative_qty};
231 $invoice->invoiceitems(\@items);
237 my ($self, %params) = @_;
239 die "not an invoice" unless $self->invoice;
241 require SL::DB::Chart;
242 if (!$params{ar_id}) {
243 my $chart = SL::DB::Manager::Chart->get_all(query => [ SL::DB::Manager::Chart->link_filter('AR') ],
246 croak("No AR chart found and no parameter 'ar_id' given") unless $chart;
247 $params{ar_id} = $chart->id;
251 my %data = $self->calculate_prices_and_taxes;
253 $self->_post_create_assemblyitem_entries($data{assembly_items});
256 $self->_post_add_acctrans($data{amounts_cogs});
257 $self->_post_add_acctrans($data{amounts});
258 $self->_post_add_acctrans($data{taxes});
260 $self->_post_add_acctrans({ $params{ar_id} => $self->amount * -1 });
262 $self->_post_update_allocated($data{allocated});
264 $self->_post_book_rounding($data{rounding});
267 if ($self->db->in_transaction) {
269 } elsif (!$self->db->do_transaction($worker)) {
270 $::lxdebug->message(LXDebug->WARN(), "convert_to_invoice failed: " . join("\n", (split(/\n/, $self->db->error))[0..2]));
277 sub _post_add_acctrans {
278 my ($self, $entries) = @_;
280 my $default_tax_id = SL::DB::Manager::Tax->find_by(taxkey => 0)->id;
283 require SL::DB::AccTransaction;
284 require SL::DB::Chart;
285 while (my ($chart_id, $spec) = each %{ $entries }) {
286 $spec = { taxkey => 0, tax_id => $default_tax_id, amount => $spec } unless ref $spec;
287 $chart_link = SL::DB::Manager::Chart->find_by(id => $chart_id)->{'link'};
290 SL::DB::AccTransaction->new(trans_id => $self->id,
291 chart_id => $chart_id,
292 amount => $spec->{amount},
293 tax_id => $spec->{tax_id},
294 taxkey => $spec->{taxkey},
295 project_id => $self->globalproject_id,
296 transdate => $self->transdate,
297 chart_link => $chart_link)->save;
301 sub _post_book_rounding {
302 my ($self, $rounding) = @_;
304 my $tax_id = SL::DB::Manager::Tax->find_by(taxkey => 0)->id;
305 my $rnd_accno = $rounding == 0 ? 0
306 : $rounding > 0 ? SL::DB::Default->get->rndgain_accno_id
307 : SL::DB::Default->get->rndloss_accno_id
309 if ($rnd_accno != 0) {
310 SL::DB::AccTransaction->new(trans_id => $self->id,
311 chart_id => $rnd_accno,
315 project_id => $self->globalproject_id,
316 transdate => $self->transdate,
317 chart_link => $rnd_accno)->save;
321 sub add_ar_amount_row {
322 my ($self, %params ) = @_;
324 # only allow this method for ar invoices (Debitorenbuchung)
325 die "not an ar invoice" if $self->invoice and not $self->customer_id;
327 die "add_ar_amount_row needs a chart object as chart param" unless $params{chart} && $params{chart}->isa('SL::DB::Chart');
328 die unless $params{chart}->link =~ /AR_amount/;
333 my ($netamount,$taxamount);
335 $netamount = $params{amount} * 1;
336 my $tax = SL::DB::Manager::Tax->find_by(id => $params{tax_id}) || die "Can't find tax with id " . $params{tax_id};
338 if ( $tax and $tax->rate != 0 ) {
339 ($netamount, $taxamount) = Form->calculate_tax($params{amount}, $tax->rate, $self->taxincluded, $roundplaces);
341 next unless $netamount; # netamount mustn't be zero
343 my $sign = $self->customer_id ? 1 : -1;
344 my $acc = SL::DB::AccTransaction->new(
345 amount => $netamount * $sign,
346 chart_id => $params{chart}->id,
347 chart_link => $params{chart}->link,
348 transdate => $self->transdate,
349 taxkey => $tax->taxkey,
351 project_id => $params{project_id},
354 $self->add_transactions( $acc );
355 push( @$acc_trans, $acc );
358 my $acc = SL::DB::AccTransaction->new(
359 amount => $taxamount * $sign,
360 chart_id => $tax->chart_id,
361 chart_link => $tax->chart->link,
362 transdate => $self->transdate,
363 taxkey => $tax->taxkey,
366 $self->add_transactions( $acc );
367 push( @$acc_trans, $acc );
373 my ($self, %params) = @_;
374 # to be called after adding all AR_amount rows, adds an AR row
376 # only allow this method for ar invoices (Debitorenbuchung)
377 die if $self->invoice and not $self->customer_id;
378 die "create_ar_row needs a chart object as a parameter" unless $params{chart} and ref($params{chart}) eq 'SL::DB::Chart';
380 my @transactions = @{$self->transactions};
381 # die "invoice has no acc_transactions" unless scalar @transactions > 0;
382 return 0 unless scalar @transactions > 0;
384 my $chart = $params{chart} || SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_ar_chart_id);
385 die "illegal chart in create_ar_row" unless $chart;
387 die "receivables chart must have link 'AR'" unless $chart->link eq 'AR';
391 # hardcoded entry for no tax: tax_id and taxkey should be 0
392 my $tax = SL::DB::Manager::Tax->find_by(id => 0, taxkey => 0) || die "Can't find tax with id 0 and taxkey 0";
394 my $sign = $self->customer_id ? -1 : 1;
395 my $acc = SL::DB::AccTransaction->new(
396 amount => $self->amount * $sign,
397 chart_id => $params{chart}->id,
398 chart_link => $params{chart}->link,
399 transdate => $self->transdate,
400 taxkey => $tax->taxkey,
403 $self->add_transactions( $acc );
404 push( @$acc_trans, $acc );
408 sub validate_acc_trans {
409 my ($self, %params) = @_;
410 # should be able to check unsaved invoice objects with several acc_trans lines
412 die "validate_acc_trans can't check invoice object with empty transactions" unless $self->transactions;
414 my @transactions = @{$self->transactions};
415 # die "invoice has no acc_transactions" unless scalar @transactions > 0;
416 return 0 unless scalar @transactions > 0;
417 return 0 unless $self->has_loaded_related('transactions');
418 if ( $params{debug} ) {
419 printf("starting validatation of invoice %s with trans_id %s and taxincluded %s\n", $self->invnumber, $self->id, $self->taxincluded);
420 foreach my $acc ( @transactions ) {
421 printf("chart: %s amount: %s tax_id: %s link: %s\n", $acc->chart->accno, $acc->amount, $acc->tax_id, $acc->chart->link);
425 my $acc_trans_sum = sum map { $_->amount } @transactions;
427 unless ( $::form->round_amount($acc_trans_sum, 10) == 0 ) {
428 my $string = "sum of acc_transactions isn't 0: $acc_trans_sum\n";
430 if ( $params{debug} ) {
431 foreach my $trans ( @transactions ) {
432 $string .= sprintf(" %s %s %s\n", $trans->chart->accno, $trans->taxkey, $trans->amount);
438 # only use the first AR entry, so it also works for paid invoices
439 my @ar_transactions = map { $_->amount } grep { $_->chart_link eq 'AR' } @transactions;
440 my $ar_sum = $ar_transactions[0];
441 # my $ar_sum = sum map { $_->amount } grep { $_->chart_link eq 'AR' } @transactions;
443 unless ( $::form->round_amount($ar_sum * -1,2) == $::form->round_amount($self->amount,2) ) {
444 if ( $params{debug} ) {
445 printf("debug: (ar_sum) %s = %s (amount)\n", $::form->round_amount($ar_sum * -1,2) , $::form->round_amount($self->amount, 2) );
446 foreach my $trans ( @transactions ) {
447 printf(" %s %s %s %s\n", $trans->chart->accno, $trans->taxkey, $trans->amount, $trans->chart->link);
450 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));
456 sub recalculate_amounts {
457 my ($self, %params) = @_;
458 # calculate and set amount and netamount from acc_trans objects
460 croak ("Can only recalculate amounts for ar transactions") if $self->invoice;
462 return undef unless $self->has_loaded_related('transactions');
464 my ($netamount, $taxamount);
466 my @transactions = @{$self->transactions};
468 foreach my $acc ( @transactions ) {
469 $netamount += $acc->amount if $acc->chart->link =~ /AR_amount/;
470 $taxamount += $acc->amount if $acc->chart->link =~ /AR_tax/;
473 $self->amount($netamount+$taxamount);
474 $self->netamount($netamount);
478 sub _post_create_assemblyitem_entries {
479 my ($self, $assembly_entries) = @_;
481 my $items = $self->invoiceitems;
485 foreach my $item (@{ $items }) {
486 next if $item->assemblyitem;
488 push @new_items, $item;
491 foreach my $assembly_item (@{ $assembly_entries->[$item_idx] || [ ] }) {
492 push @new_items, SL::DB::InvoiceItem->new(parts_id => $assembly_item->{part},
493 description => $assembly_item->{part}->description,
494 unit => $assembly_item->{part}->unit,
495 qty => $assembly_item->{qty},
496 allocated => $assembly_item->{allocated},
499 assemblyitem => 't');
503 $self->invoiceitems(\@new_items);
506 sub _post_update_allocated {
507 my ($self, $allocated) = @_;
509 while (my ($invoice_id, $diff) = each %{ $allocated }) {
510 SL::DB::Manager::InvoiceItem->update_all(set => { allocated => { sql => "allocated + $diff" } },
511 where => [ id => $invoice_id ]);
518 return 'ar_transaction' if !$self->invoice;
519 return 'credit_note' if $self->type eq 'credit_note' && $self->amount < 0 && !$self->storno;
520 return 'invoice_storno' if $self->type ne 'credit_note' && $self->amount < 0 && $self->storno;
521 return 'credit_note_storno' if $self->type eq 'credit_note' && $self->amount > 0 && $self->storno;
525 sub displayable_state {
528 return $self->closed ? $::locale->text('closed') : $::locale->text('open');
531 sub displayable_type {
534 return t8('AR Transaction') if $self->invoice_type eq 'ar_transaction';
535 return t8('Credit Note') if $self->invoice_type eq 'credit_note';
536 return t8('Invoice') . "(" . t8('Storno') . ")" if $self->invoice_type eq 'invoice_storno';
537 return t8('Credit Note') . "(" . t8('Storno') . ")" if $self->invoice_type eq 'credit_note_storno';
538 return t8('Invoice');
541 sub displayable_name {
542 join ' ', grep $_, map $_[0]->$_, qw(displayable_type record_number);
548 return t8('AR Transaction (abbreviation)') if $self->invoice_type eq 'ar_transaction';
549 return t8('Credit note (one letter abbreviation)') if $self->invoice_type eq 'credit_note';
550 return t8('Invoice (one letter abbreviation)') . "(" . t8('Storno (one letter abbreviation)') . ")" if $self->invoice_type eq 'invoice_storno';
551 return t8('Credit note (one letter abbreviation)') . "(" . t8('Storno (one letter abbreviation)') . ")" if $self->invoice_type eq 'credit_note_storno';
552 return t8('Invoice (one letter abbreviation)');
571 $html = SL::Presenter->get->sales_invoice($self, display => 'inline') if $self->invoice;
572 $html = SL::Presenter->get->ar_transaction($self, display => 'inline') if !$self->invoice;
587 SL::DB::Invoice: Rose model for invoices (table "ar")
593 =item C<new_from $source, %params>
595 Creates a new C<SL::DB::Invoice> instance and copies as much
596 information from C<$source> as possible. At the moment only sales
597 orders and sales quotations are supported as sources.
599 The conversion copies order items into invoice items. Dates are copied
600 as appropriate, e.g. the C<transdate> field from an order will be
601 copied into the invoice's C<orddate> field.
603 C<%params> can include the following options:
609 An optional array reference of RDBO instances for the items to use. If
610 missing then the method C<items_sorted> will be called on
611 C<$source>. This option can be used to override the sorting, to
612 exclude certain positions or to add additional ones.
614 =item C<skip_items_negative_qty>
616 If trueish then items with a negative quantity are skipped. Items with
617 a quantity of 0 are not affected by this option.
619 =item C<skip_items_zero_qty>
621 If trueish then items with a quantity of 0 are skipped.
625 An optional code reference that is called for each item with the item
626 as its sole parameter. Items for which the code reference returns a
627 falsish value will be skipped.
631 An optional hash reference. If it exists then it is passed to C<new>
632 allowing the caller to set certain attributes for the new invoice.
633 For example to set a different transdate (default is the current date),
634 call the method like this:
637 $params{attributes}{transdate} = '28.08.2015';
638 $invoice = SL::DB::Invoice->new_from($self, %params)->post || die;
642 Amounts, prices and taxes are not
643 calculated. L<SL::DB::Helper::PriceTaxCalculator::calculate_prices_and_taxes>
644 can be used for this.
646 The object returned is not saved.
648 =item C<post %params>
650 Posts the invoice. Required parameters are:
656 The ID of the accounts receivable chart the invoice's amounts are
657 posted to. If it is not set then the first chart configured for
658 accounts receivables is used.
662 This function implements several steps:
666 =item 1. It calculates all prices, amounts and taxes by calling
667 L<SL::DB::Helper::PriceTaxCalculator::calculate_prices_and_taxes>.
669 =item 2. A new and unique invoice number is created.
671 =item 3. All amounts for costs of goods sold are recorded in
674 =item 4. All amounts for parts, services and assemblies are recorded
675 in C<acc_trans> with their respective charts. This is determined by
676 the part's buchungsgruppen.
678 =item 5. The total amount is posted to the accounts receivable chart
679 and recorded in C<acc_trans>.
681 =item 6. Items in C<invoice> are updated according to their allocation
682 status (regarding costs of goods sold). Will only be done if
683 kivitendo is not configured to use Einnahmenüberschussrechnungen.
685 =item 7. The invoice and its items are saved.
689 Returns C<$self> on success and C<undef> on failure. The whole process
690 is run inside a transaction. If it fails then nothing is saved to or
691 changed in the database. A new transaction is only started if none are
694 =item C<basic_info $field>
696 See L<SL::DB::Object::basic_info>.
698 =item C<recalculate_amounts %params>
700 Calculate and set amount and netamount from acc_trans objects by summing up the
701 values of acc_trans objects with AR_amount and AR_tax link charts.
702 amount and netamount are set to the calculated values.
704 =item C<validate_acc_trans>
706 Checks if the sum of all associated acc_trans objects is 0 and checks whether
707 the amount of the AR acc_transaction matches the AR amount. Only the first AR
708 line is checked, because the sum of all AR lines is 0 for paid invoices.
712 Can be called with a debug parameter which writes debug info to STDOUT, which is
713 useful in console mode or while writing tests.
715 my $ar = SL::DB::Manager::Invoice->get_first();
716 $ar->validate_acc_trans(debug => 1);
718 =item C<create_ar_row %params>
720 Creates a new acc_trans entry for the receivable (AR) entry of an existing AR
721 invoice object, which already has some income and tax acc_trans entries.
723 The acc_trans entry is also returned inside an array ref.
729 =item * chart as an RDBO object, e.g. for bank. Must be a 'paid' chart.
733 Currently the amount of the invoice object is used for the acc_trans amount.
734 Use C<recalculate_amounts> before calling this method if amount isn't known
735 yet or you didn't set it manually.
737 =item C<add_ar_amount_row %params>
739 Add a new entry for an existing AR invoice object. Creates an acc_trans entry,
740 and also adds an acc_trans tax entry, if the tax has an associated tax chart.
741 Also all acc_trans entries that were created are returned inside an array ref.
747 =item * chart as an RDBO object, should be an income chart (link = AR_amount)
759 As explained in the new_from example, it is possible to set transdate to a new value.
760 From a user / programm point of view transdate is more than holy and there should be
761 some validity checker available for controller code. At least the same logic like in
762 Form.pm from ar.pl should be available:
763 # see old stuff ar.pl post
764 #$form->error($locale->text('Cannot post transaction above the maximum future booking date!'))
765 # if ($form->date_max_future($transdate, \%myconfig));
766 #$form->error($locale->text('Cannot post transaction for a closed period!')) if ($form->date_closed($form->{"transdate"}, \%myconfig));
770 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>