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
169 cp_id language_id taxzone_id globalproject_id transaction_description currency_id delivery_term_id), @columns),
170 transdate => DateTime->today_local,
171 gldate => DateTime->today_local,
172 duedate => $terms ? $terms->calc_date(reference_date => DateTime->today_local) : DateTime->today_local,
177 employee_id => (SL::DB::Manager::Employee->current || SL::DB::Employee->new(id => $source->employee_id))->id,
180 $args{payment_id} = ( $terms ? $terms->id : $source->payment_id);
182 if ($source->type =~ /_order$/) {
183 $args{deliverydate} = $source->reqdate;
184 $args{orddate} = $source->transdate;
186 $args{quodate} = $source->transdate;
189 # Custom shipto addresses (the ones specific to the sales/purchase
190 # record and not to the customer/vendor) are only linked from shipto
191 # → ar. Meaning ar.shipto_id will not be filled in that
193 if (!$source->shipto_id && $source->id) {
194 $args{custom_shipto} = $source->custom_shipto->clone($class) if $source->can('custom_shipto') && $source->custom_shipto;
197 $args{shipto_id} = $source->shipto_id;
200 my $invoice = $class->new(%args);
201 $invoice->assign_attributes(%{ $params{attributes} }) if $params{attributes};
202 my $items = delete($params{items}) || $source->items_sorted;
206 my $source_item = $_;
207 my $source_item_id = $_->$item_parent_id_column;
208 my @custom_variables = map { _clone_orderitem_delivery_order_item_cvar($_) } @{ $source_item->custom_variables };
210 $item_parents{$source_item_id} ||= $source_item->$item_parent_column;
211 my $item_parent = $item_parents{$source_item_id};
212 my $current_invoice_item =
213 SL::DB::InvoiceItem->new(map({ ( $_ => $source_item->$_ ) }
214 qw(parts_id description qty sellprice discount project_id serialnumber pricegroup_id transdate cusordnumber unit
215 base_qty longdescription lastcost price_factor_id active_discount_source active_price_source), @item_columns),
216 deliverydate => $source_item->reqdate,
217 fxsellprice => $source_item->sellprice,
218 custom_variables => \@custom_variables,
219 ordnumber => ref($item_parent) eq 'SL::DB::Order' ? $item_parent->ordnumber : $source_item->ordnumber,
220 donumber => ref($item_parent) eq 'SL::DB::DeliveryOrder' ? $item_parent->donumber : $source_item->can('donumber') ? $source_item->donumber : '',
223 $current_invoice_item->{"converted_from_orderitems_id"} = $_->{id} if ref($item_parent) eq 'SL::DB::Order';
224 $current_invoice_item->{"converted_from_delivery_order_items_id"} = $_->{id} if ref($item_parent) eq 'SL::DB::DeliveryOrder';
225 $current_invoice_item;
228 @items = grep { $params{item_filter}->($_) } @items if $params{item_filter};
229 @items = grep { $_->qty * 1 } @items if $params{skip_items_zero_qty};
230 @items = grep { $_->qty >=0 } @items if $params{skip_items_negative_qty};
232 $invoice->invoiceitems(\@items);
238 my ($self, %params) = @_;
240 die "not an invoice" unless $self->invoice;
242 require SL::DB::Chart;
243 if (!$params{ar_id}) {
245 if ($::instance_conf->get_ar_chart_id) {
246 $chart = SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_ar_chart_id);
248 $chart = SL::DB::Manager::Chart->get_all(query => [ SL::DB::Manager::Chart->link_filter('AR') ],
252 croak("No AR chart found and no parameter 'ar_id' given") unless $chart;
253 $params{ar_id} = $chart->id;
256 if (!$self->db->with_transaction(sub {
257 my %data = $self->calculate_prices_and_taxes;
259 $self->_post_create_assemblyitem_entries($data{assembly_items});
262 $self->_post_add_acctrans($data{amounts_cogs});
263 $self->_post_add_acctrans($data{amounts});
264 $self->_post_add_acctrans($data{taxes});
266 $self->_post_add_acctrans({ $params{ar_id} => $self->amount * -1 });
268 $self->_post_update_allocated($data{allocated});
270 $self->_post_book_rounding($data{rounding});
274 $::lxdebug->message(LXDebug->WARN(), "convert_to_invoice failed: " . join("\n", (split(/\n/, $self->db->error))[0..2]));
281 sub _post_add_acctrans {
282 my ($self, $entries) = @_;
284 my $default_tax_id = SL::DB::Manager::Tax->find_by(taxkey => 0)->id;
287 require SL::DB::AccTransaction;
288 require SL::DB::Chart;
289 while (my ($chart_id, $spec) = each %{ $entries }) {
290 $spec = { taxkey => 0, tax_id => $default_tax_id, amount => $spec } unless ref $spec;
291 $chart_link = SL::DB::Manager::Chart->find_by(id => $chart_id)->{'link'};
294 SL::DB::AccTransaction->new(trans_id => $self->id,
295 chart_id => $chart_id,
296 amount => $spec->{amount},
297 tax_id => $spec->{tax_id},
298 taxkey => $spec->{taxkey},
299 project_id => $self->globalproject_id,
300 transdate => $self->transdate,
301 chart_link => $chart_link)->save;
305 sub _post_book_rounding {
306 my ($self, $rounding) = @_;
308 my $tax_id = SL::DB::Manager::Tax->find_by(taxkey => 0)->id;
309 my $rnd_accno = $rounding == 0 ? 0
310 : $rounding > 0 ? SL::DB::Default->get->rndgain_accno_id
311 : SL::DB::Default->get->rndloss_accno_id
313 if ($rnd_accno != 0) {
314 SL::DB::AccTransaction->new(trans_id => $self->id,
315 chart_id => $rnd_accno,
319 project_id => $self->globalproject_id,
320 transdate => $self->transdate,
321 chart_link => $rnd_accno)->save;
325 sub add_ar_amount_row {
326 my ($self, %params ) = @_;
328 # only allow this method for ar invoices (Debitorenbuchung)
329 die "not an ar invoice" if $self->invoice and not $self->customer_id;
331 die "add_ar_amount_row needs a chart object as chart param" unless $params{chart} && $params{chart}->isa('SL::DB::Chart');
332 die "chart must be an AR_amount chart" unless $params{chart}->link =~ /AR_amount/;
337 my ($netamount,$taxamount);
339 $netamount = $params{amount} * 1;
340 my $tax = SL::DB::Manager::Tax->find_by(id => $params{tax_id}) || die "Can't find tax with id " . $params{tax_id};
342 if ( $tax and $tax->rate != 0 ) {
343 ($netamount, $taxamount) = Form->calculate_tax($params{amount}, $tax->rate, $self->taxincluded, $roundplaces);
345 next unless $netamount; # netamount mustn't be zero
347 my $sign = $self->customer_id ? 1 : -1;
348 my $acc = SL::DB::AccTransaction->new(
349 amount => $netamount * $sign,
350 chart_id => $params{chart}->id,
351 chart_link => $params{chart}->link,
352 transdate => $self->transdate,
353 taxkey => $tax->taxkey,
355 project_id => $params{project_id},
358 $self->add_transactions( $acc );
359 push( @$acc_trans, $acc );
362 my $acc = SL::DB::AccTransaction->new(
363 amount => $taxamount * $sign,
364 chart_id => $tax->chart_id,
365 chart_link => $tax->chart->link,
366 transdate => $self->transdate,
367 taxkey => $tax->taxkey,
370 $self->add_transactions( $acc );
371 push( @$acc_trans, $acc );
377 my ($self, %params) = @_;
378 # to be called after adding all AR_amount rows, adds an AR row
380 # only allow this method for ar invoices (Debitorenbuchung)
381 die if $self->invoice and not $self->customer_id;
382 die "create_ar_row needs a chart object as a parameter" unless $params{chart} and ref($params{chart}) eq 'SL::DB::Chart';
384 my @transactions = @{$self->transactions};
385 # die "invoice has no acc_transactions" unless scalar @transactions > 0;
386 return 0 unless scalar @transactions > 0;
388 my $chart = $params{chart} || SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_ar_chart_id);
389 die "illegal chart in create_ar_row" unless $chart;
391 die "receivables chart must have link 'AR'" unless $chart->link eq 'AR';
395 # hardcoded entry for no tax: tax_id and taxkey should be 0
396 my $tax = SL::DB::Manager::Tax->find_by(id => 0, taxkey => 0) || die "Can't find tax with id 0 and taxkey 0";
398 my $sign = $self->customer_id ? -1 : 1;
399 my $acc = SL::DB::AccTransaction->new(
400 amount => $self->amount * $sign,
401 chart_id => $params{chart}->id,
402 chart_link => $params{chart}->link,
403 transdate => $self->transdate,
404 taxkey => $tax->taxkey,
407 $self->add_transactions( $acc );
408 push( @$acc_trans, $acc );
412 sub validate_acc_trans {
413 my ($self, %params) = @_;
414 # should be able to check unsaved invoice objects with several acc_trans lines
416 die "validate_acc_trans can't check invoice object with empty transactions" unless $self->transactions;
418 my @transactions = @{$self->transactions};
419 # die "invoice has no acc_transactions" unless scalar @transactions > 0;
420 return 0 unless scalar @transactions > 0;
421 return 0 unless $self->has_loaded_related('transactions');
422 if ( $params{debug} ) {
423 printf("starting validatation of invoice %s with trans_id %s and taxincluded %s\n", $self->invnumber, $self->id, $self->taxincluded);
424 foreach my $acc ( @transactions ) {
425 printf("chart: %s amount: %s tax_id: %s link: %s\n", $acc->chart->accno, $acc->amount, $acc->tax_id, $acc->chart->link);
429 my $acc_trans_sum = sum map { $_->amount } @transactions;
431 unless ( $::form->round_amount($acc_trans_sum, 10) == 0 ) {
432 my $string = "sum of acc_transactions isn't 0: $acc_trans_sum\n";
434 if ( $params{debug} ) {
435 foreach my $trans ( @transactions ) {
436 $string .= sprintf(" %s %s %s\n", $trans->chart->accno, $trans->taxkey, $trans->amount);
442 # only use the first AR entry, so it also works for paid invoices
443 my @ar_transactions = map { $_->amount } grep { $_->chart_link eq 'AR' } @transactions;
444 my $ar_sum = $ar_transactions[0];
445 # my $ar_sum = sum map { $_->amount } grep { $_->chart_link eq 'AR' } @transactions;
447 unless ( $::form->round_amount($ar_sum * -1,2) == $::form->round_amount($self->amount,2) ) {
448 if ( $params{debug} ) {
449 printf("debug: (ar_sum) %s = %s (amount)\n", $::form->round_amount($ar_sum * -1,2) , $::form->round_amount($self->amount, 2) );
450 foreach my $trans ( @transactions ) {
451 printf(" %s %s %s %s\n", $trans->chart->accno, $trans->taxkey, $trans->amount, $trans->chart->link);
454 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));
460 sub recalculate_amounts {
461 my ($self, %params) = @_;
462 # calculate and set amount and netamount from acc_trans objects
464 croak ("Can only recalculate amounts for ar transactions") if $self->invoice;
466 return undef unless $self->has_loaded_related('transactions');
468 my ($netamount, $taxamount);
470 my @transactions = @{$self->transactions};
472 foreach my $acc ( @transactions ) {
473 $netamount += $acc->amount if $acc->chart->link =~ /AR_amount/;
474 $taxamount += $acc->amount if $acc->chart->link =~ /AR_tax/;
477 $self->amount($netamount+$taxamount);
478 $self->netamount($netamount);
482 sub _post_create_assemblyitem_entries {
483 my ($self, $assembly_entries) = @_;
485 my $items = $self->invoiceitems;
489 foreach my $item (@{ $items }) {
490 next if $item->assemblyitem;
492 push @new_items, $item;
495 foreach my $assembly_item (@{ $assembly_entries->[$item_idx] || [ ] }) {
496 push @new_items, SL::DB::InvoiceItem->new(parts_id => $assembly_item->{part},
497 description => $assembly_item->{part}->description,
498 unit => $assembly_item->{part}->unit,
499 qty => $assembly_item->{qty},
500 allocated => $assembly_item->{allocated},
503 assemblyitem => 't');
507 $self->invoiceitems(\@new_items);
510 sub _post_update_allocated {
511 my ($self, $allocated) = @_;
513 while (my ($invoice_id, $diff) = each %{ $allocated }) {
514 SL::DB::Manager::InvoiceItem->update_all(set => { allocated => { sql => "allocated + $diff" } },
515 where => [ id => $invoice_id ]);
522 return 'ar_transaction' if !$self->invoice;
523 return 'credit_note' if $self->type eq 'credit_note' && $self->amount < 0 && !$self->storno;
524 return 'invoice_storno' if $self->type ne 'credit_note' && $self->amount < 0 && $self->storno;
525 return 'credit_note_storno' if $self->type eq 'credit_note' && $self->amount > 0 && $self->storno;
529 sub displayable_state {
532 return $self->closed ? $::locale->text('closed') : $::locale->text('open');
535 sub displayable_type {
538 return t8('AR Transaction') if $self->invoice_type eq 'ar_transaction';
539 return t8('Credit Note') if $self->invoice_type eq 'credit_note';
540 return t8('Invoice') . "(" . t8('Storno') . ")" if $self->invoice_type eq 'invoice_storno';
541 return t8('Credit Note') . "(" . t8('Storno') . ")" if $self->invoice_type eq 'credit_note_storno';
542 return t8('Invoice');
545 sub displayable_name {
546 join ' ', grep $_, map $_[0]->$_, qw(displayable_type record_number);
552 return t8('AR Transaction (abbreviation)') if $self->invoice_type eq 'ar_transaction';
553 return t8('Credit note (one letter abbreviation)') if $self->invoice_type eq 'credit_note';
554 return t8('Invoice (one letter abbreviation)') . "(" . t8('Storno (one letter abbreviation)') . ")" if $self->invoice_type eq 'invoice_storno';
555 return t8('Credit note (one letter abbreviation)') . "(" . t8('Storno (one letter abbreviation)') . ")" if $self->invoice_type eq 'credit_note_storno';
556 return t8('Invoice (one letter abbreviation)');
575 $html = SL::Presenter->get->sales_invoice($self, display => 'inline') if $self->invoice;
576 $html = SL::Presenter->get->ar_transaction($self, display => 'inline') if !$self->invoice;
591 SL::DB::Invoice: Rose model for invoices (table "ar")
597 =item C<new_from $source, %params>
599 Creates a new C<SL::DB::Invoice> instance and copies as much
600 information from C<$source> as possible. At the moment only sales
601 orders and sales quotations are supported as sources.
603 The conversion copies order items into invoice items. Dates are copied
604 as appropriate, e.g. the C<transdate> field from an order will be
605 copied into the invoice's C<orddate> field.
607 C<%params> can include the following options:
613 An optional array reference of RDBO instances for the items to use. If
614 missing then the method C<items_sorted> will be called on
615 C<$source>. This option can be used to override the sorting, to
616 exclude certain positions or to add additional ones.
618 =item C<skip_items_negative_qty>
620 If trueish then items with a negative quantity are skipped. Items with
621 a quantity of 0 are not affected by this option.
623 =item C<skip_items_zero_qty>
625 If trueish then items with a quantity of 0 are skipped.
629 An optional code reference that is called for each item with the item
630 as its sole parameter. Items for which the code reference returns a
631 falsish value will be skipped.
635 An optional hash reference. If it exists then it is passed to C<new>
636 allowing the caller to set certain attributes for the new invoice.
637 For example to set a different transdate (default is the current date),
638 call the method like this:
641 $params{attributes}{transdate} = '28.08.2015';
642 $invoice = SL::DB::Invoice->new_from($self, %params)->post || die;
646 Amounts, prices and taxes are not
647 calculated. L<SL::DB::Helper::PriceTaxCalculator::calculate_prices_and_taxes>
648 can be used for this.
650 The object returned is not saved.
652 =item C<post %params>
654 Posts the invoice. Required parameters are:
660 The ID of the accounts receivable chart the invoice's amounts are
661 posted to. If it is not set then the first chart configured for
662 accounts receivables is used.
666 This function implements several steps:
670 =item 1. It calculates all prices, amounts and taxes by calling
671 L<SL::DB::Helper::PriceTaxCalculator::calculate_prices_and_taxes>.
673 =item 2. A new and unique invoice number is created.
675 =item 3. All amounts for costs of goods sold are recorded in
678 =item 4. All amounts for parts, services and assemblies are recorded
679 in C<acc_trans> with their respective charts. This is determined by
680 the part's buchungsgruppen.
682 =item 5. The total amount is posted to the accounts receivable chart
683 and recorded in C<acc_trans>.
685 =item 6. Items in C<invoice> are updated according to their allocation
686 status (regarding costs of goods sold). Will only be done if
687 kivitendo is not configured to use Einnahmenüberschussrechnungen.
689 =item 7. The invoice and its items are saved.
693 Returns C<$self> on success and C<undef> on failure. The whole process
694 is run inside a transaction. If it fails then nothing is saved to or
695 changed in the database. A new transaction is only started if none are
698 =item C<basic_info $field>
700 See L<SL::DB::Object::basic_info>.
704 Returns 1 or 0, depending on whether the invoice is closed or not. Currently
705 invoices that are overpaid also count as closed.
707 =item C<recalculate_amounts %params>
709 Calculate and set amount and netamount from acc_trans objects by summing up the
710 values of acc_trans objects with AR_amount and AR_tax link charts.
711 amount and netamount are set to the calculated values.
713 =item C<validate_acc_trans>
715 Checks if the sum of all associated acc_trans objects is 0 and checks whether
716 the amount of the AR acc_transaction matches the AR amount. Only the first AR
717 line is checked, because the sum of all AR lines is 0 for paid invoices.
721 Can be called with a debug parameter which writes debug info to STDOUT, which is
722 useful in console mode or while writing tests.
724 my $ar = SL::DB::Manager::Invoice->get_first();
725 $ar->validate_acc_trans(debug => 1);
727 =item C<create_ar_row %params>
729 Creates a new acc_trans entry for the receivable (AR) entry of an existing AR
730 invoice object, which already has some income and tax acc_trans entries.
732 The acc_trans entry is also returned inside an array ref.
738 =item * chart as an RDBO object, e.g. for bank. Must be a 'paid' chart.
742 Currently the amount of the invoice object is used for the acc_trans amount.
743 Use C<recalculate_amounts> before calling this method if amount isn't known
744 yet or you didn't set it manually.
746 =item C<add_ar_amount_row %params>
748 Add a new entry for an existing AR invoice object. Creates an acc_trans entry,
749 and also adds an acc_trans tax entry, if the tax has an associated tax chart.
750 Also all acc_trans entries that were created are returned inside an array ref.
756 =item * chart as an RDBO object, should be an income chart (link = AR_amount)
768 As explained in the new_from example, it is possible to set transdate to a new value.
769 From a user / programm point of view transdate is more than holy and there should be
770 some validity checker available for controller code. At least the same logic like in
771 Form.pm from ar.pl should be available:
772 # see old stuff ar.pl post
773 #$form->error($locale->text('Cannot post transaction above the maximum future booking date!'))
774 # if ($form->date_max_future($transdate, \%myconfig));
775 #$form->error($locale->text('Cannot post transaction for a closed period!')) if ($form->date_closed($form->{"transdate"}, \%myconfig));
779 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>