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 = Rose::DB::Object::Helpers::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;
166 my %args = ( map({ ( $_ => $source->$_ ) } qw(customer_id taxincluded shippingpoint shipvia notes intnotes salesman_id cusordnumber ordnumber department_id
167 cp_id language_id taxzone_id globalproject_id transaction_description currency_id delivery_term_id payment_id), @columns),
168 transdate => DateTime->today_local,
169 gldate => DateTime->today_local,
170 duedate => $terms ? $terms->calc_date(reference_date => DateTime->today_local) : DateTime->today_local,
175 employee_id => (SL::DB::Manager::Employee->current || SL::DB::Employee->new(id => $source->employee_id))->id,
178 if ($source->type =~ /_order$/) {
179 $args{deliverydate} = $source->reqdate;
180 $args{orddate} = $source->transdate;
182 $args{quodate} = $source->transdate;
185 # Custom shipto addresses (the ones specific to the sales/purchase
186 # record and not to the customer/vendor) are only linked from shipto
187 # → ar. Meaning ar.shipto_id will not be filled in that
189 if (!$source->shipto_id && $source->id) {
190 $args{custom_shipto} = $source->custom_shipto->clone($class) if $source->can('custom_shipto') && $source->custom_shipto;
193 $args{shipto_id} = $source->shipto_id;
196 my $invoice = $class->new(%args);
197 $invoice->assign_attributes(%{ $params{attributes} }) if $params{attributes};
198 my $items = delete($params{items}) || $source->items_sorted;
202 my $source_item = $_;
203 my $source_item_id = $_->$item_parent_id_column;
204 my @custom_variables = map { _clone_orderitem_delivery_order_item_cvar($_) } @{ $source_item->custom_variables };
206 $item_parents{$source_item_id} ||= $source_item->$item_parent_column;
207 my $item_parent = $item_parents{$source_item_id};
208 my $current_invoice_item =
209 SL::DB::InvoiceItem->new(map({ ( $_ => $source_item->$_ ) }
210 qw(parts_id description qty sellprice discount project_id serialnumber pricegroup_id transdate cusordnumber unit
211 base_qty longdescription lastcost price_factor_id active_discount_source active_price_source), @item_columns),
212 deliverydate => $source_item->reqdate,
213 fxsellprice => $source_item->sellprice,
214 custom_variables => \@custom_variables,
215 ordnumber => ref($item_parent) eq 'SL::DB::Order' ? $item_parent->ordnumber : $source_item->ordnumber,
216 donumber => ref($item_parent) eq 'SL::DB::DeliveryOrder' ? $item_parent->donumber : $source_item->can('donumber') ? $source_item->donumber : '',
219 $current_invoice_item->{"converted_from_orderitems_id"} = $_->{id} if ref($item_parent) eq 'SL::DB::Order';
220 $current_invoice_item->{"converted_from_delivery_order_items_id"} = $_->{id} if ref($item_parent) eq 'SL::DB::DeliveryOrder';
221 $current_invoice_item;
224 @items = grep { $params{item_filter}->($_) } @items if $params{item_filter};
225 @items = grep { $_->qty * 1 } @items if $params{skip_items_zero_qty};
226 @items = grep { $_->qty >=0 } @items if $params{skip_items_negative_qty};
228 $invoice->invoiceitems(\@items);
234 my ($self, %params) = @_;
236 die "not an invoice" unless $self->invoice;
238 require SL::DB::Chart;
239 if (!$params{ar_id}) {
240 my $chart = SL::DB::Manager::Chart->get_all(query => [ SL::DB::Manager::Chart->link_filter('AR') ],
243 croak("No AR chart found and no parameter 'ar_id' given") unless $chart;
244 $params{ar_id} = $chart->id;
248 my %data = $self->calculate_prices_and_taxes;
250 $self->_post_create_assemblyitem_entries($data{assembly_items});
253 $self->_post_add_acctrans($data{amounts_cogs});
254 $self->_post_add_acctrans($data{amounts});
255 $self->_post_add_acctrans($data{taxes});
257 $self->_post_add_acctrans({ $params{ar_id} => $self->amount * -1 });
259 $self->_post_update_allocated($data{allocated});
262 if ($self->db->in_transaction) {
264 } elsif (!$self->db->do_transaction($worker)) {
265 $::lxdebug->message(LXDebug->WARN(), "convert_to_invoice failed: " . join("\n", (split(/\n/, $self->db->error))[0..2]));
272 sub _post_add_acctrans {
273 my ($self, $entries) = @_;
275 my $default_tax_id = SL::DB::Manager::Tax->find_by(taxkey => 0)->id;
278 require SL::DB::AccTransaction;
279 require SL::DB::Chart;
280 while (my ($chart_id, $spec) = each %{ $entries }) {
281 $spec = { taxkey => 0, tax_id => $default_tax_id, amount => $spec } unless ref $spec;
282 $chart_link = SL::DB::Manager::Chart->find_by(id => $chart_id)->{'link'};
285 SL::DB::AccTransaction->new(trans_id => $self->id,
286 chart_id => $chart_id,
287 amount => $spec->{amount},
288 tax_id => $spec->{tax_id},
289 taxkey => $spec->{taxkey},
290 project_id => $self->globalproject_id,
291 transdate => $self->transdate,
292 chart_link => $chart_link)->save;
296 sub add_ar_amount_row {
297 my ($self, %params ) = @_;
299 # only allow this method for ar invoices (Debitorenbuchung)
300 die "not an ar invoice" if $self->invoice and not $self->customer_id;
302 die "add_ar_amount_row needs a chart object as chart param" unless $params{chart} && $params{chart}->isa('SL::DB::Chart');
303 die unless $params{chart}->link =~ /AR_amount/;
308 my ($netamount,$taxamount);
310 $netamount = $params{amount} * 1;
311 my $tax = SL::DB::Manager::Tax->find_by(id => $params{tax_id}) || die "Can't find tax with id " . $params{tax_id};
313 if ( $tax and $tax->rate != 0 ) {
314 ($netamount, $taxamount) = Form->calculate_tax($params{amount}, $tax->rate, $self->taxincluded, $roundplaces);
316 next unless $netamount; # netamount mustn't be zero
318 my $sign = $self->customer_id ? 1 : -1;
319 my $acc = SL::DB::AccTransaction->new(
320 amount => $netamount * $sign,
321 chart_id => $params{chart}->id,
322 chart_link => $params{chart}->link,
323 transdate => $self->transdate,
324 taxkey => $tax->taxkey,
326 project_id => $params{project_id},
329 $self->add_transactions( $acc );
330 push( @$acc_trans, $acc );
333 my $acc = SL::DB::AccTransaction->new(
334 amount => $taxamount * $sign,
335 chart_id => $tax->chart_id,
336 chart_link => $tax->chart->link,
337 transdate => $self->transdate,
338 taxkey => $tax->taxkey,
341 $self->add_transactions( $acc );
342 push( @$acc_trans, $acc );
348 my ($self, %params) = @_;
349 # to be called after adding all AR_amount rows, adds an AR row
351 # only allow this method for ar invoices (Debitorenbuchung)
352 die if $self->invoice and not $self->customer_id;
353 die "create_ar_row needs a chart object as a parameter" unless $params{chart} and ref($params{chart}) eq 'SL::DB::Chart';
355 my @transactions = @{$self->transactions};
356 # die "invoice has no acc_transactions" unless scalar @transactions > 0;
357 return 0 unless scalar @transactions > 0;
359 my $chart = $params{chart} || SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_ar_chart_id);
360 die "illegal chart in create_ar_row" unless $chart;
362 die "receivables chart must have link 'AR'" unless $chart->link eq 'AR';
366 # hardcoded entry for no tax: tax_id and taxkey should be 0
367 my $tax = SL::DB::Manager::Tax->find_by(id => 0, taxkey => 0) || die "Can't find tax with id 0 and taxkey 0";
369 my $sign = $self->customer_id ? -1 : 1;
370 my $acc = SL::DB::AccTransaction->new(
371 amount => $self->amount * $sign,
372 chart_id => $params{chart}->id,
373 chart_link => $params{chart}->link,
374 transdate => $self->transdate,
375 taxkey => $tax->taxkey,
378 $self->add_transactions( $acc );
379 push( @$acc_trans, $acc );
383 sub validate_acc_trans {
384 my ($self, %params) = @_;
385 # should be able to check unsaved invoice objects with several acc_trans lines
387 die "validate_acc_trans can't check invoice object with empty transactions" unless $self->transactions;
389 my @transactions = @{$self->transactions};
390 # die "invoice has no acc_transactions" unless scalar @transactions > 0;
391 return 0 unless scalar @transactions > 0;
392 return 0 unless $self->has_loaded_related('transactions');
393 if ( $params{debug} ) {
394 printf("starting validatation of invoice %s with trans_id %s and taxincluded %s\n", $self->invnumber, $self->id, $self->taxincluded);
395 foreach my $acc ( @transactions ) {
396 printf("chart: %s amount: %s tax_id: %s link: %s\n", $acc->chart->accno, $acc->amount, $acc->tax_id, $acc->chart->link);
400 my $acc_trans_sum = sum map { $_->amount } @transactions;
402 unless ( $::form->round_amount($acc_trans_sum, 10) == 0 ) {
403 my $string = "sum of acc_transactions isn't 0: $acc_trans_sum\n";
405 if ( $params{debug} ) {
406 foreach my $trans ( @transactions ) {
407 $string .= sprintf(" %s %s %s\n", $trans->chart->accno, $trans->taxkey, $trans->amount);
413 # only use the first AR entry, so it also works for paid invoices
414 my @ar_transactions = map { $_->amount } grep { $_->chart_link eq 'AR' } @transactions;
415 my $ar_sum = $ar_transactions[0];
416 # my $ar_sum = sum map { $_->amount } grep { $_->chart_link eq 'AR' } @transactions;
418 unless ( $::form->round_amount($ar_sum * -1,2) == $::form->round_amount($self->amount,2) ) {
419 if ( $params{debug} ) {
420 printf("debug: (ar_sum) %s = %s (amount)\n", $::form->round_amount($ar_sum * -1,2) , $::form->round_amount($self->amount, 2) );
421 foreach my $trans ( @transactions ) {
422 printf(" %s %s %s %s\n", $trans->chart->accno, $trans->taxkey, $trans->amount, $trans->chart->link);
425 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));
431 sub recalculate_amounts {
432 my ($self, %params) = @_;
433 # calculate and set amount and netamount from acc_trans objects
435 croak ("Can only recalculate amounts for ar transactions") if $self->invoice;
437 return undef unless $self->has_loaded_related('transactions');
439 my ($netamount, $taxamount);
441 my @transactions = @{$self->transactions};
443 foreach my $acc ( @transactions ) {
444 $netamount += $acc->amount if $acc->chart->link =~ /AR_amount/;
445 $taxamount += $acc->amount if $acc->chart->link =~ /AR_tax/;
448 $self->amount($netamount+$taxamount);
449 $self->netamount($netamount);
453 sub _post_create_assemblyitem_entries {
454 my ($self, $assembly_entries) = @_;
456 my $items = $self->invoiceitems;
460 foreach my $item (@{ $items }) {
461 next if $item->assemblyitem;
463 push @new_items, $item;
466 foreach my $assembly_item (@{ $assembly_entries->[$item_idx] || [ ] }) {
467 push @new_items, SL::DB::InvoiceItem->new(parts_id => $assembly_item->{part},
468 description => $assembly_item->{part}->description,
469 unit => $assembly_item->{part}->unit,
470 qty => $assembly_item->{qty},
471 allocated => $assembly_item->{allocated},
474 assemblyitem => 't');
478 $self->invoiceitems(\@new_items);
481 sub _post_update_allocated {
482 my ($self, $allocated) = @_;
484 while (my ($invoice_id, $diff) = each %{ $allocated }) {
485 SL::DB::Manager::InvoiceItem->update_all(set => { allocated => { sql => "allocated + $diff" } },
486 where => [ id => $invoice_id ]);
493 return 'ar_transaction' if !$self->invoice;
494 return 'credit_note' if $self->type eq 'credit_note' && $self->amount < 0 && !$self->storno;
495 return 'invoice_storno' if $self->type ne 'credit_note' && $self->amount < 0 && $self->storno;
496 return 'credit_note_storno' if $self->type eq 'credit_note' && $self->amount > 0 && $self->storno;
500 sub displayable_state {
503 return $self->closed ? $::locale->text('closed') : $::locale->text('open');
506 sub displayable_type {
509 return t8('AR Transaction') if $self->invoice_type eq 'ar_transaction';
510 return t8('Credit Note') if $self->invoice_type eq 'credit_note';
511 return t8('Invoice') . "(" . t8('Storno') . ")" if $self->invoice_type eq 'invoice_storno';
512 return t8('Credit Note') . "(" . t8('Storno') . ")" if $self->invoice_type eq 'credit_note_storno';
513 return t8('Invoice');
516 sub displayable_name {
517 join ' ', grep $_, map $_[0]->$_, qw(displayable_type record_number);
523 return t8('AR Transaction (abbreviation)') if $self->invoice_type eq 'ar_transaction';
524 return t8('Credit note (one letter abbreviation)') if $self->invoice_type eq 'credit_note';
525 return t8('Invoice (one letter abbreviation)') . "(" . t8('Storno (one letter abbreviation)') . ")" if $self->invoice_type eq 'invoice_storno';
526 return t8('Credit note (one letter abbreviation)') . "(" . t8('Storno (one letter abbreviation)') . ")" if $self->invoice_type eq 'credit_note_storno';
527 return t8('Invoice (one letter abbreviation)');
546 $html = SL::Presenter->get->sales_invoice($self, display => 'inline') if $self->invoice;
547 $html = SL::Presenter->get->ar_transaction($self, display => 'inline') if !$self->invoice;
562 SL::DB::Invoice: Rose model for invoices (table "ar")
568 =item C<new_from $source, %params>
570 Creates a new C<SL::DB::Invoice> instance and copies as much
571 information from C<$source> as possible. At the moment only sales
572 orders and sales quotations are supported as sources.
574 The conversion copies order items into invoice items. Dates are copied
575 as appropriate, e.g. the C<transdate> field from an order will be
576 copied into the invoice's C<orddate> field.
578 C<%params> can include the following options:
584 An optional array reference of RDBO instances for the items to use. If
585 missing then the method C<items_sorted> will be called on
586 C<$source>. This option can be used to override the sorting, to
587 exclude certain positions or to add additional ones.
589 =item C<skip_items_negative_qty>
591 If trueish then items with a negative quantity are skipped. Items with
592 a quantity of 0 are not affected by this option.
594 =item C<skip_items_zero_qty>
596 If trueish then items with a quantity of 0 are skipped.
600 An optional code reference that is called for each item with the item
601 as its sole parameter. Items for which the code reference returns a
602 falsish value will be skipped.
606 An optional hash reference. If it exists then it is passed to C<new>
607 allowing the caller to set certain attributes for the new invoice.
608 For example to set a different transdate (default is the current date),
609 call the method like this:
612 $params{attributes}{transdate} = '28.08.2015';
613 $invoice = SL::DB::Invoice->new_from($self, %params)->post || die;
617 Amounts, prices and taxes are not
618 calculated. L<SL::DB::Helper::PriceTaxCalculator::calculate_prices_and_taxes>
619 can be used for this.
621 The object returned is not saved.
623 =item C<post %params>
625 Posts the invoice. Required parameters are:
631 The ID of the accounts receivable chart the invoice's amounts are
632 posted to. If it is not set then the first chart configured for
633 accounts receivables is used.
637 This function implements several steps:
641 =item 1. It calculates all prices, amounts and taxes by calling
642 L<SL::DB::Helper::PriceTaxCalculator::calculate_prices_and_taxes>.
644 =item 2. A new and unique invoice number is created.
646 =item 3. All amounts for costs of goods sold are recorded in
649 =item 4. All amounts for parts, services and assemblies are recorded
650 in C<acc_trans> with their respective charts. This is determined by
651 the part's buchungsgruppen.
653 =item 5. The total amount is posted to the accounts receivable chart
654 and recorded in C<acc_trans>.
656 =item 6. Items in C<invoice> are updated according to their allocation
657 status (regarding costs of goods sold). Will only be done if
658 kivitendo is not configured to use Einnahmenüberschussrechnungen.
660 =item 7. The invoice and its items are saved.
664 Returns C<$self> on success and C<undef> on failure. The whole process
665 is run inside a transaction. If it fails then nothing is saved to or
666 changed in the database. A new transaction is only started if none are
669 =item C<basic_info $field>
671 See L<SL::DB::Object::basic_info>.
673 =item C<recalculate_amounts %params>
675 Calculate and set amount and netamount from acc_trans objects by summing up the
676 values of acc_trans objects with AR_amount and AR_tax link charts.
677 amount and netamount are set to the calculated values.
679 =item C<validate_acc_trans>
681 Checks if the sum of all associated acc_trans objects is 0 and checks whether
682 the amount of the AR acc_transaction matches the AR amount. Only the first AR
683 line is checked, because the sum of all AR lines is 0 for paid invoices.
687 Can be called with a debug parameter which writes debug info to STDOUT, which is
688 useful in console mode or while writing tests.
690 my $ar = SL::DB::Manager::Invoice->get_first();
691 $ar->validate_acc_trans(debug => 1);
693 =item C<create_ar_row %params>
695 Creates a new acc_trans entry for the receivable (AR) entry of an existing AR
696 invoice object, which already has some income and tax acc_trans entries.
698 The acc_trans entry is also returned inside an array ref.
704 =item * chart as an RDBO object, e.g. for bank. Must be a 'paid' chart.
708 Currently the amount of the invoice object is used for the acc_trans amount.
709 Use C<recalculate_amounts> before calling this mehtod if amount it isn't known
710 yet or you didn't set it manually.
712 =item C<add_ar_amount_row %params>
714 Add a new entry for an existing AR invoice object. Creates an acc_trans entry,
715 and also adds an acc_trans tax entry, if the tax has an associated tax chart.
716 Also all acc_trans entries that were created are returned inside an array ref.
722 =item * chart as an RDBO object, should be an income chart (link = AR_amount)
734 As explained in the new_from example, it is possible to set transdate to a new value.
735 From a user / programm point of view transdate is more than holy and there should be
736 some validity checker available for controller code. At least the same logic like in
737 Form.pm from ar.pl should be available:
738 # see old stuff ar.pl post
739 #$form->error($locale->text('Cannot post transaction above the maximum future booking date!'))
740 # if ($form->date_max_future($transdate, \%myconfig));
741 #$form->error($locale->text('Cannot post transaction for a closed period!')) if ($form->date_closed($form->{"transdate"}, \%myconfig));
745 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>