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 shipto_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 my $invoice = $class->new(%args);
186 $invoice->assign_attributes(%{ $params{attributes} }) if $params{attributes};
187 my $items = delete($params{items}) || $source->items_sorted;
191 my $source_item = $_;
192 my $source_item_id = $_->$item_parent_id_column;
193 my @custom_variables = map { _clone_orderitem_delivery_order_item_cvar($_) } @{ $source_item->custom_variables };
195 $item_parents{$source_item_id} ||= $source_item->$item_parent_column;
196 my $item_parent = $item_parents{$source_item_id};
197 my $current_invoice_item =
198 SL::DB::InvoiceItem->new(map({ ( $_ => $source_item->$_ ) }
199 qw(parts_id description qty sellprice discount project_id serialnumber pricegroup_id transdate cusordnumber unit
200 base_qty longdescription lastcost price_factor_id active_discount_source active_price_source), @item_columns),
201 deliverydate => $source_item->reqdate,
202 fxsellprice => $source_item->sellprice,
203 custom_variables => \@custom_variables,
204 ordnumber => ref($item_parent) eq 'SL::DB::Order' ? $item_parent->ordnumber : $source_item->ordnumber,
205 donumber => ref($item_parent) eq 'SL::DB::DeliveryOrder' ? $item_parent->donumber : $source_item->can('donumber') ? $source_item->donumber : '',
208 $current_invoice_item->{"converted_from_orderitems_id"} = $_->{id} if ref($item_parent) eq 'SL::DB::Order';
209 $current_invoice_item->{"converted_from_delivery_order_items_id"} = $_->{id} if ref($item_parent) eq 'SL::DB::DeliveryOrder';
210 $current_invoice_item;
213 @items = grep { $params{item_filter}->($_) } @items if $params{item_filter};
214 @items = grep { $_->qty * 1 } @items if $params{skip_items_zero_qty};
215 @items = grep { $_->qty >=0 } @items if $params{skip_items_negative_qty};
217 $invoice->invoiceitems(\@items);
223 my ($self, %params) = @_;
225 die "not an invoice" unless $self->invoice;
227 require SL::DB::Chart;
228 if (!$params{ar_id}) {
229 my $chart = SL::DB::Manager::Chart->get_all(query => [ SL::DB::Manager::Chart->link_filter('AR') ],
232 croak("No AR chart found and no parameter 'ar_id' given") unless $chart;
233 $params{ar_id} = $chart->id;
237 my %data = $self->calculate_prices_and_taxes;
239 $self->_post_create_assemblyitem_entries($data{assembly_items});
242 $self->_post_add_acctrans($data{amounts_cogs});
243 $self->_post_add_acctrans($data{amounts});
244 $self->_post_add_acctrans($data{taxes});
246 $self->_post_add_acctrans({ $params{ar_id} => $self->amount * -1 });
248 $self->_post_update_allocated($data{allocated});
251 if ($self->db->in_transaction) {
253 } elsif (!$self->db->do_transaction($worker)) {
254 $::lxdebug->message(LXDebug->WARN(), "convert_to_invoice failed: " . join("\n", (split(/\n/, $self->db->error))[0..2]));
261 sub _post_add_acctrans {
262 my ($self, $entries) = @_;
264 my $default_tax_id = SL::DB::Manager::Tax->find_by(taxkey => 0)->id;
267 require SL::DB::AccTransaction;
268 require SL::DB::Chart;
269 while (my ($chart_id, $spec) = each %{ $entries }) {
270 $spec = { taxkey => 0, tax_id => $default_tax_id, amount => $spec } unless ref $spec;
271 $chart_link = SL::DB::Manager::Chart->find_by(id => $chart_id)->{'link'};
274 SL::DB::AccTransaction->new(trans_id => $self->id,
275 chart_id => $chart_id,
276 amount => $spec->{amount},
277 tax_id => $spec->{tax_id},
278 taxkey => $spec->{taxkey},
279 project_id => $self->globalproject_id,
280 transdate => $self->transdate,
281 chart_link => $chart_link)->save;
285 sub add_ar_amount_row {
286 my ($self, %params ) = @_;
288 # only allow this method for ar invoices (Debitorenbuchung)
289 die "not an ar invoice" if $self->invoice and not $self->customer_id;
291 die "add_ar_amount_row needs a chart object as chart param" unless $params{chart} && $params{chart}->isa('SL::DB::Chart');
292 die unless $params{chart}->link =~ /AR_amount/;
297 my ($netamount,$taxamount);
299 $netamount = $params{amount} * 1;
300 my $tax = SL::DB::Manager::Tax->find_by(id => $params{tax_id}) || die "Can't find tax with id " . $params{tax_id};
302 if ( $tax and $tax->rate != 0 ) {
303 ($netamount, $taxamount) = Form->calculate_tax($params{amount}, $tax->rate, $self->taxincluded, $roundplaces);
305 next unless $netamount; # netamount mustn't be zero
307 my $sign = $self->customer_id ? 1 : -1;
308 my $acc = SL::DB::AccTransaction->new(
309 amount => $netamount * $sign,
310 chart_id => $params{chart}->id,
311 chart_link => $params{chart}->link,
312 transdate => $self->transdate,
313 taxkey => $tax->taxkey,
315 project_id => $params{project_id},
318 $self->add_transactions( $acc );
319 push( @$acc_trans, $acc );
322 my $acc = SL::DB::AccTransaction->new(
323 amount => $taxamount * $sign,
324 chart_id => $tax->chart_id,
325 chart_link => $tax->chart->link,
326 transdate => $self->transdate,
327 taxkey => $tax->taxkey,
330 $self->add_transactions( $acc );
331 push( @$acc_trans, $acc );
337 my ($self, %params) = @_;
338 # to be called after adding all AR_amount rows, adds an AR row
340 # only allow this method for ar invoices (Debitorenbuchung)
341 die if $self->invoice and not $self->customer_id;
342 die "create_ar_row needs a chart object as a parameter" unless $params{chart} and ref($params{chart}) eq 'SL::DB::Chart';
344 my @transactions = @{$self->transactions};
345 # die "invoice has no acc_transactions" unless scalar @transactions > 0;
346 return 0 unless scalar @transactions > 0;
348 my $chart = $params{chart} || SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_ar_chart_id);
349 die "illegal chart in create_ar_row" unless $chart;
351 die "receivables chart must have link 'AR'" unless $chart->link eq 'AR';
355 # hardcoded entry for no tax: tax_id and taxkey should be 0
356 my $tax = SL::DB::Manager::Tax->find_by(id => 0, taxkey => 0) || die "Can't find tax with id 0 and taxkey 0";
358 my $sign = $self->customer_id ? -1 : 1;
359 my $acc = SL::DB::AccTransaction->new(
360 amount => $self->amount * $sign,
361 chart_id => $params{chart}->id,
362 chart_link => $params{chart}->link,
363 transdate => $self->transdate,
364 taxkey => $tax->taxkey,
367 $self->add_transactions( $acc );
368 push( @$acc_trans, $acc );
372 sub validate_acc_trans {
373 my ($self, %params) = @_;
374 # should be able to check unsaved invoice objects with several acc_trans lines
376 die "validate_acc_trans can't check invoice object with empty transactions" unless $self->transactions;
378 my @transactions = @{$self->transactions};
379 # die "invoice has no acc_transactions" unless scalar @transactions > 0;
380 return 0 unless scalar @transactions > 0;
381 return 0 unless $self->has_loaded_related('transactions');
382 if ( $params{debug} ) {
383 printf("starting validatation of invoice %s with trans_id %s and taxincluded %s\n", $self->invnumber, $self->id, $self->taxincluded);
384 foreach my $acc ( @transactions ) {
385 printf("chart: %s amount: %s tax_id: %s link: %s\n", $acc->chart->accno, $acc->amount, $acc->tax_id, $acc->chart->link);
389 my $acc_trans_sum = sum map { $_->amount } @transactions;
391 unless ( $::form->round_amount($acc_trans_sum, 10) == 0 ) {
392 my $string = "sum of acc_transactions isn't 0: $acc_trans_sum\n";
394 if ( $params{debug} ) {
395 foreach my $trans ( @transactions ) {
396 $string .= sprintf(" %s %s %s\n", $trans->chart->accno, $trans->taxkey, $trans->amount);
402 # only use the first AR entry, so it also works for paid invoices
403 my @ar_transactions = map { $_->amount } grep { $_->chart_link eq 'AR' } @transactions;
404 my $ar_sum = $ar_transactions[0];
405 # my $ar_sum = sum map { $_->amount } grep { $_->chart_link eq 'AR' } @transactions;
407 unless ( $::form->round_amount($ar_sum * -1,2) == $::form->round_amount($self->amount,2) ) {
408 if ( $params{debug} ) {
409 printf("debug: (ar_sum) %s = %s (amount)\n", $::form->round_amount($ar_sum * -1,2) , $::form->round_amount($self->amount, 2) );
410 foreach my $trans ( @transactions ) {
411 printf(" %s %s %s %s\n", $trans->chart->accno, $trans->taxkey, $trans->amount, $trans->chart->link);
414 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));
420 sub recalculate_amounts {
421 my ($self, %params) = @_;
422 # calculate and set amount and netamount from acc_trans objects
424 croak ("Can only recalculate amounts for ar transactions") if $self->invoice;
426 return undef unless $self->has_loaded_related('transactions');
428 my ($netamount, $taxamount);
430 my @transactions = @{$self->transactions};
432 foreach my $acc ( @transactions ) {
433 $netamount += $acc->amount if $acc->chart->link =~ /AR_amount/;
434 $taxamount += $acc->amount if $acc->chart->link =~ /AR_tax/;
437 $self->amount($netamount+$taxamount);
438 $self->netamount($netamount);
442 sub _post_create_assemblyitem_entries {
443 my ($self, $assembly_entries) = @_;
445 my $items = $self->invoiceitems;
449 foreach my $item (@{ $items }) {
450 next if $item->assemblyitem;
452 push @new_items, $item;
455 foreach my $assembly_item (@{ $assembly_entries->[$item_idx] || [ ] }) {
456 push @new_items, SL::DB::InvoiceItem->new(parts_id => $assembly_item->{part},
457 description => $assembly_item->{part}->description,
458 unit => $assembly_item->{part}->unit,
459 qty => $assembly_item->{qty},
460 allocated => $assembly_item->{allocated},
463 assemblyitem => 't');
467 $self->invoiceitems(\@new_items);
470 sub _post_update_allocated {
471 my ($self, $allocated) = @_;
473 while (my ($invoice_id, $diff) = each %{ $allocated }) {
474 SL::DB::Manager::InvoiceItem->update_all(set => { allocated => { sql => "allocated + $diff" } },
475 where => [ id => $invoice_id ]);
482 return 'ar_transaction' if !$self->invoice;
483 return 'credit_note' if $self->type eq 'credit_note' && $self->amount < 0 && !$self->storno;
484 return 'invoice_storno' if $self->type ne 'credit_note' && $self->amount < 0 && $self->storno;
485 return 'credit_note_storno' if $self->type eq 'credit_note' && $self->amount > 0 && $self->storno;
489 sub displayable_state {
492 return $self->closed ? $::locale->text('closed') : $::locale->text('open');
495 sub displayable_type {
498 return t8('AR Transaction') if $self->invoice_type eq 'ar_transaction';
499 return t8('Credit Note') if $self->invoice_type eq 'credit_note';
500 return t8('Invoice') . "(" . t8('Storno') . ")" if $self->invoice_type eq 'invoice_storno';
501 return t8('Credit Note') . "(" . t8('Storno') . ")" if $self->invoice_type eq 'credit_note_storno';
502 return t8('Invoice');
505 sub displayable_name {
506 join ' ', grep $_, map $_[0]->$_, qw(displayable_type record_number);
512 return t8('AR Transaction (abbreviation)') if $self->invoice_type eq 'ar_transaction';
513 return t8('Credit note (one letter abbreviation)') if $self->invoice_type eq 'credit_note';
514 return t8('Invoice (one letter abbreviation)') . "(" . t8('Storno (one letter abbreviation)') . ")" if $self->invoice_type eq 'invoice_storno';
515 return t8('Credit note (one letter abbreviation)') . "(" . t8('Storno (one letter abbreviation)') . ")" if $self->invoice_type eq 'credit_note_storno';
516 return t8('Invoice (one letter abbreviation)');
535 $html = SL::Presenter->get->sales_invoice($self, display => 'inline') if $self->invoice;
536 $html = SL::Presenter->get->ar_transaction($self, display => 'inline') if !$self->invoice;
551 SL::DB::Invoice: Rose model for invoices (table "ar")
557 =item C<new_from $source, %params>
559 Creates a new C<SL::DB::Invoice> instance and copies as much
560 information from C<$source> as possible. At the moment only sales
561 orders and sales quotations are supported as sources.
563 The conversion copies order items into invoice items. Dates are copied
564 as appropriate, e.g. the C<transdate> field from an order will be
565 copied into the invoice's C<orddate> field.
567 C<%params> can include the following options:
573 An optional array reference of RDBO instances for the items to use. If
574 missing then the method C<items_sorted> will be called on
575 C<$source>. This option can be used to override the sorting, to
576 exclude certain positions or to add additional ones.
578 =item C<skip_items_negative_qty>
580 If trueish then items with a negative quantity are skipped. Items with
581 a quantity of 0 are not affected by this option.
583 =item C<skip_items_zero_qty>
585 If trueish then items with a quantity of 0 are skipped.
589 An optional code reference that is called for each item with the item
590 as its sole parameter. Items for which the code reference returns a
591 falsish value will be skipped.
595 An optional hash reference. If it exists then it is passed to C<new>
596 allowing the caller to set certain attributes for the new invoice.
597 For example to set a different transdate (default is the current date),
598 call the method like this:
601 $params{attributes}{transdate} = '28.08.2015';
602 $invoice = SL::DB::Invoice->new_from($self, %params)->post || die;
606 Amounts, prices and taxes are not
607 calculated. L<SL::DB::Helper::PriceTaxCalculator::calculate_prices_and_taxes>
608 can be used for this.
610 The object returned is not saved.
612 =item C<post %params>
614 Posts the invoice. Required parameters are:
620 The ID of the accounts receivable chart the invoice's amounts are
621 posted to. If it is not set then the first chart configured for
622 accounts receivables is used.
626 This function implements several steps:
630 =item 1. It calculates all prices, amounts and taxes by calling
631 L<SL::DB::Helper::PriceTaxCalculator::calculate_prices_and_taxes>.
633 =item 2. A new and unique invoice number is created.
635 =item 3. All amounts for costs of goods sold are recorded in
638 =item 4. All amounts for parts, services and assemblies are recorded
639 in C<acc_trans> with their respective charts. This is determined by
640 the part's buchungsgruppen.
642 =item 5. The total amount is posted to the accounts receivable chart
643 and recorded in C<acc_trans>.
645 =item 6. Items in C<invoice> are updated according to their allocation
646 status (regarding costs of goods sold). Will only be done if
647 kivitendo is not configured to use Einnahmenüberschussrechnungen.
649 =item 7. The invoice and its items are saved.
653 Returns C<$self> on success and C<undef> on failure. The whole process
654 is run inside a transaction. If it fails then nothing is saved to or
655 changed in the database. A new transaction is only started if none are
658 =item C<basic_info $field>
660 See L<SL::DB::Object::basic_info>.
662 =item C<recalculate_amounts %params>
664 Calculate and set amount and netamount from acc_trans objects by summing up the
665 values of acc_trans objects with AR_amount and AR_tax link charts.
666 amount and netamount are set to the calculated values.
668 =item C<validate_acc_trans>
670 Checks if the sum of all associated acc_trans objects is 0 and checks whether
671 the amount of the AR acc_transaction matches the AR amount. Only the first AR
672 line is checked, because the sum of all AR lines is 0 for paid invoices.
676 Can be called with a debug parameter which writes debug info to STDOUT, which is
677 useful in console mode or while writing tests.
679 my $ar = SL::DB::Manager::Invoice->get_first();
680 $ar->validate_acc_trans(debug => 1);
682 =item C<create_ar_row %params>
684 Creates a new acc_trans entry for the receivable (AR) entry of an existing AR
685 invoice object, which already has some income and tax acc_trans entries.
687 The acc_trans entry is also returned inside an array ref.
693 =item * chart as an RDBO object, e.g. for bank. Must be a 'paid' chart.
697 Currently the amount of the invoice object is used for the acc_trans amount.
698 Use C<recalculate_amounts> before calling this mehtod if amount it isn't known
699 yet or you didn't set it manually.
701 =item C<add_ar_amount_row %params>
703 Add a new entry for an existing AR invoice object. Creates an acc_trans entry,
704 and also adds an acc_trans tax entry, if the tax has an associated tax chart.
705 Also all acc_trans entries that were created are returned inside an array ref.
711 =item * chart as an RDBO object, should be an income chart (link = AR_amount)
723 As explained in the new_from example, it is possible to set transdate to a new value.
724 From a user / programm point of view transdate is more than holy and there should be
725 some validity checker available for controller code. At least the same logic like in
726 Form.pm from ar.pl should be available:
727 # see old stuff ar.pl post
728 #$form->error($locale->text('Cannot post transaction above the maximum future booking date!'))
729 # if ($form->date_max_future($transdate, \%myconfig));
730 #$form->error($locale->text('Cannot post transaction for a closed period!')) if ($form->date_closed($form->{"transdate"}, \%myconfig));
734 Moritz Bunkus E<lt>m.bunkus@linet-services.deE<gt>