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});
265 if ($self->db->in_transaction) {
267 } elsif (!$self->db->do_transaction($worker)) {
268 $::lxdebug->message(LXDebug->WARN(), "convert_to_invoice failed: " . join("\n", (split(/\n/, $self->db->error))[0..2]));
275 sub _post_add_acctrans {
276 my ($self, $entries) = @_;
278 my $default_tax_id = SL::DB::Manager::Tax->find_by(taxkey => 0)->id;
281 require SL::DB::AccTransaction;
282 require SL::DB::Chart;
283 while (my ($chart_id, $spec) = each %{ $entries }) {
284 $spec = { taxkey => 0, tax_id => $default_tax_id, amount => $spec } unless ref $spec;
285 $chart_link = SL::DB::Manager::Chart->find_by(id => $chart_id)->{'link'};
288 SL::DB::AccTransaction->new(trans_id => $self->id,
289 chart_id => $chart_id,
290 amount => $spec->{amount},
291 tax_id => $spec->{tax_id},
292 taxkey => $spec->{taxkey},
293 project_id => $self->globalproject_id,
294 transdate => $self->transdate,
295 chart_link => $chart_link)->save;
299 sub add_ar_amount_row {
300 my ($self, %params ) = @_;
302 # only allow this method for ar invoices (Debitorenbuchung)
303 die "not an ar invoice" if $self->invoice and not $self->customer_id;
305 die "add_ar_amount_row needs a chart object as chart param" unless $params{chart} && $params{chart}->isa('SL::DB::Chart');
306 die unless $params{chart}->link =~ /AR_amount/;
311 my ($netamount,$taxamount);
313 $netamount = $params{amount} * 1;
314 my $tax = SL::DB::Manager::Tax->find_by(id => $params{tax_id}) || die "Can't find tax with id " . $params{tax_id};
316 if ( $tax and $tax->rate != 0 ) {
317 ($netamount, $taxamount) = Form->calculate_tax($params{amount}, $tax->rate, $self->taxincluded, $roundplaces);
319 next unless $netamount; # netamount mustn't be zero
321 my $sign = $self->customer_id ? 1 : -1;
322 my $acc = SL::DB::AccTransaction->new(
323 amount => $netamount * $sign,
324 chart_id => $params{chart}->id,
325 chart_link => $params{chart}->link,
326 transdate => $self->transdate,
327 taxkey => $tax->taxkey,
329 project_id => $params{project_id},
332 $self->add_transactions( $acc );
333 push( @$acc_trans, $acc );
336 my $acc = SL::DB::AccTransaction->new(
337 amount => $taxamount * $sign,
338 chart_id => $tax->chart_id,
339 chart_link => $tax->chart->link,
340 transdate => $self->transdate,
341 taxkey => $tax->taxkey,
344 $self->add_transactions( $acc );
345 push( @$acc_trans, $acc );
351 my ($self, %params) = @_;
352 # to be called after adding all AR_amount rows, adds an AR row
354 # only allow this method for ar invoices (Debitorenbuchung)
355 die if $self->invoice and not $self->customer_id;
356 die "create_ar_row needs a chart object as a parameter" unless $params{chart} and ref($params{chart}) eq 'SL::DB::Chart';
358 my @transactions = @{$self->transactions};
359 # die "invoice has no acc_transactions" unless scalar @transactions > 0;
360 return 0 unless scalar @transactions > 0;
362 my $chart = $params{chart} || SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_ar_chart_id);
363 die "illegal chart in create_ar_row" unless $chart;
365 die "receivables chart must have link 'AR'" unless $chart->link eq 'AR';
369 # hardcoded entry for no tax: tax_id and taxkey should be 0
370 my $tax = SL::DB::Manager::Tax->find_by(id => 0, taxkey => 0) || die "Can't find tax with id 0 and taxkey 0";
372 my $sign = $self->customer_id ? -1 : 1;
373 my $acc = SL::DB::AccTransaction->new(
374 amount => $self->amount * $sign,
375 chart_id => $params{chart}->id,
376 chart_link => $params{chart}->link,
377 transdate => $self->transdate,
378 taxkey => $tax->taxkey,
381 $self->add_transactions( $acc );
382 push( @$acc_trans, $acc );
386 sub validate_acc_trans {
387 my ($self, %params) = @_;
388 # should be able to check unsaved invoice objects with several acc_trans lines
390 die "validate_acc_trans can't check invoice object with empty transactions" unless $self->transactions;
392 my @transactions = @{$self->transactions};
393 # die "invoice has no acc_transactions" unless scalar @transactions > 0;
394 return 0 unless scalar @transactions > 0;
395 return 0 unless $self->has_loaded_related('transactions');
396 if ( $params{debug} ) {
397 printf("starting validatation of invoice %s with trans_id %s and taxincluded %s\n", $self->invnumber, $self->id, $self->taxincluded);
398 foreach my $acc ( @transactions ) {
399 printf("chart: %s amount: %s tax_id: %s link: %s\n", $acc->chart->accno, $acc->amount, $acc->tax_id, $acc->chart->link);
403 my $acc_trans_sum = sum map { $_->amount } @transactions;
405 unless ( $::form->round_amount($acc_trans_sum, 10) == 0 ) {
406 my $string = "sum of acc_transactions isn't 0: $acc_trans_sum\n";
408 if ( $params{debug} ) {
409 foreach my $trans ( @transactions ) {
410 $string .= sprintf(" %s %s %s\n", $trans->chart->accno, $trans->taxkey, $trans->amount);
416 # only use the first AR entry, so it also works for paid invoices
417 my @ar_transactions = map { $_->amount } grep { $_->chart_link eq 'AR' } @transactions;
418 my $ar_sum = $ar_transactions[0];
419 # my $ar_sum = sum map { $_->amount } grep { $_->chart_link eq 'AR' } @transactions;
421 unless ( $::form->round_amount($ar_sum * -1,2) == $::form->round_amount($self->amount,2) ) {
422 if ( $params{debug} ) {
423 printf("debug: (ar_sum) %s = %s (amount)\n", $::form->round_amount($ar_sum * -1,2) , $::form->round_amount($self->amount, 2) );
424 foreach my $trans ( @transactions ) {
425 printf(" %s %s %s %s\n", $trans->chart->accno, $trans->taxkey, $trans->amount, $trans->chart->link);
428 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));
434 sub recalculate_amounts {
435 my ($self, %params) = @_;
436 # calculate and set amount and netamount from acc_trans objects
438 croak ("Can only recalculate amounts for ar transactions") if $self->invoice;
440 return undef unless $self->has_loaded_related('transactions');
442 my ($netamount, $taxamount);
444 my @transactions = @{$self->transactions};
446 foreach my $acc ( @transactions ) {
447 $netamount += $acc->amount if $acc->chart->link =~ /AR_amount/;
448 $taxamount += $acc->amount if $acc->chart->link =~ /AR_tax/;
451 $self->amount($netamount+$taxamount);
452 $self->netamount($netamount);
456 sub _post_create_assemblyitem_entries {
457 my ($self, $assembly_entries) = @_;
459 my $items = $self->invoiceitems;
463 foreach my $item (@{ $items }) {
464 next if $item->assemblyitem;
466 push @new_items, $item;
469 foreach my $assembly_item (@{ $assembly_entries->[$item_idx] || [ ] }) {
470 push @new_items, SL::DB::InvoiceItem->new(parts_id => $assembly_item->{part},
471 description => $assembly_item->{part}->description,
472 unit => $assembly_item->{part}->unit,
473 qty => $assembly_item->{qty},
474 allocated => $assembly_item->{allocated},
477 assemblyitem => 't');
481 $self->invoiceitems(\@new_items);
484 sub _post_update_allocated {
485 my ($self, $allocated) = @_;
487 while (my ($invoice_id, $diff) = each %{ $allocated }) {
488 SL::DB::Manager::InvoiceItem->update_all(set => { allocated => { sql => "allocated + $diff" } },
489 where => [ id => $invoice_id ]);
496 return 'ar_transaction' if !$self->invoice;
497 return 'credit_note' if $self->type eq 'credit_note' && $self->amount < 0 && !$self->storno;
498 return 'invoice_storno' if $self->type ne 'credit_note' && $self->amount < 0 && $self->storno;
499 return 'credit_note_storno' if $self->type eq 'credit_note' && $self->amount > 0 && $self->storno;
503 sub displayable_state {
506 return $self->closed ? $::locale->text('closed') : $::locale->text('open');
509 sub displayable_type {
512 return t8('AR Transaction') if $self->invoice_type eq 'ar_transaction';
513 return t8('Credit Note') if $self->invoice_type eq 'credit_note';
514 return t8('Invoice') . "(" . t8('Storno') . ")" if $self->invoice_type eq 'invoice_storno';
515 return t8('Credit Note') . "(" . t8('Storno') . ")" if $self->invoice_type eq 'credit_note_storno';
516 return t8('Invoice');
519 sub displayable_name {
520 join ' ', grep $_, map $_[0]->$_, qw(displayable_type record_number);
526 return t8('AR Transaction (abbreviation)') if $self->invoice_type eq 'ar_transaction';
527 return t8('Credit note (one letter abbreviation)') if $self->invoice_type eq 'credit_note';
528 return t8('Invoice (one letter abbreviation)') . "(" . t8('Storno (one letter abbreviation)') . ")" if $self->invoice_type eq 'invoice_storno';
529 return t8('Credit note (one letter abbreviation)') . "(" . t8('Storno (one letter abbreviation)') . ")" if $self->invoice_type eq 'credit_note_storno';
530 return t8('Invoice (one letter abbreviation)');
549 $html = SL::Presenter->get->sales_invoice($self, display => 'inline') if $self->invoice;
550 $html = SL::Presenter->get->ar_transaction($self, display => 'inline') if !$self->invoice;
565 SL::DB::Invoice: Rose model for invoices (table "ar")
571 =item C<new_from $source, %params>
573 Creates a new C<SL::DB::Invoice> instance and copies as much
574 information from C<$source> as possible. At the moment only sales
575 orders and sales quotations are supported as sources.
577 The conversion copies order items into invoice items. Dates are copied
578 as appropriate, e.g. the C<transdate> field from an order will be
579 copied into the invoice's C<orddate> field.
581 C<%params> can include the following options:
587 An optional array reference of RDBO instances for the items to use. If
588 missing then the method C<items_sorted> will be called on
589 C<$source>. This option can be used to override the sorting, to
590 exclude certain positions or to add additional ones.
592 =item C<skip_items_negative_qty>
594 If trueish then items with a negative quantity are skipped. Items with
595 a quantity of 0 are not affected by this option.
597 =item C<skip_items_zero_qty>
599 If trueish then items with a quantity of 0 are skipped.
603 An optional code reference that is called for each item with the item
604 as its sole parameter. Items for which the code reference returns a
605 falsish value will be skipped.
609 An optional hash reference. If it exists then it is passed to C<new>
610 allowing the caller to set certain attributes for the new invoice.
611 For example to set a different transdate (default is the current date),
612 call the method like this:
615 $params{attributes}{transdate} = '28.08.2015';
616 $invoice = SL::DB::Invoice->new_from($self, %params)->post || die;
620 Amounts, prices and taxes are not
621 calculated. L<SL::DB::Helper::PriceTaxCalculator::calculate_prices_and_taxes>
622 can be used for this.
624 The object returned is not saved.
626 =item C<post %params>
628 Posts the invoice. Required parameters are:
634 The ID of the accounts receivable chart the invoice's amounts are
635 posted to. If it is not set then the first chart configured for
636 accounts receivables is used.
640 This function implements several steps:
644 =item 1. It calculates all prices, amounts and taxes by calling
645 L<SL::DB::Helper::PriceTaxCalculator::calculate_prices_and_taxes>.
647 =item 2. A new and unique invoice number is created.
649 =item 3. All amounts for costs of goods sold are recorded in
652 =item 4. All amounts for parts, services and assemblies are recorded
653 in C<acc_trans> with their respective charts. This is determined by
654 the part's buchungsgruppen.
656 =item 5. The total amount is posted to the accounts receivable chart
657 and recorded in C<acc_trans>.
659 =item 6. Items in C<invoice> are updated according to their allocation
660 status (regarding costs of goods sold). Will only be done if
661 kivitendo is not configured to use Einnahmenüberschussrechnungen.
663 =item 7. The invoice and its items are saved.
667 Returns C<$self> on success and C<undef> on failure. The whole process
668 is run inside a transaction. If it fails then nothing is saved to or
669 changed in the database. A new transaction is only started if none are
672 =item C<basic_info $field>
674 See L<SL::DB::Object::basic_info>.
676 =item C<recalculate_amounts %params>
678 Calculate and set amount and netamount from acc_trans objects by summing up the
679 values of acc_trans objects with AR_amount and AR_tax link charts.
680 amount and netamount are set to the calculated values.
682 =item C<validate_acc_trans>
684 Checks if the sum of all associated acc_trans objects is 0 and checks whether
685 the amount of the AR acc_transaction matches the AR amount. Only the first AR
686 line is checked, because the sum of all AR lines is 0 for paid invoices.
690 Can be called with a debug parameter which writes debug info to STDOUT, which is
691 useful in console mode or while writing tests.
693 my $ar = SL::DB::Manager::Invoice->get_first();
694 $ar->validate_acc_trans(debug => 1);
696 =item C<create_ar_row %params>
698 Creates a new acc_trans entry for the receivable (AR) entry of an existing AR
699 invoice object, which already has some income and tax acc_trans entries.
701 The acc_trans entry is also returned inside an array ref.
707 =item * chart as an RDBO object, e.g. for bank. Must be a 'paid' chart.
711 Currently the amount of the invoice object is used for the acc_trans amount.
712 Use C<recalculate_amounts> before calling this mehtod if amount it isn't known
713 yet or you didn't set it manually.
715 =item C<add_ar_amount_row %params>
717 Add a new entry for an existing AR invoice object. Creates an acc_trans entry,
718 and also adds an acc_trans tax entry, if the tax has an associated tax chart.
719 Also all acc_trans entries that were created are returned inside an array ref.
725 =item * chart as an RDBO object, should be an income chart (link = AR_amount)
737 As explained in the new_from example, it is possible to set transdate to a new value.
738 From a user / programm point of view transdate is more than holy and there should be
739 some validity checker available for controller code. At least the same logic like in
740 Form.pm from ar.pl should be available:
741 # see old stuff ar.pl post
742 #$form->error($locale->text('Cannot post transaction above the maximum future booking date!'))
743 # if ($form->date_max_future($transdate, \%myconfig));
744 #$form->error($locale->text('Cannot post transaction for a closed period!')) if ($form->date_closed($form->{"transdate"}, \%myconfig));
748 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>