projekt kivitendo

our @EXPORT_OK = qw(skonto_date skonto_charts amount_less_skonto within_skonto_period percent_skonto reference_account reference_amount open_amount open_percent remaining_skonto_days skonto_amount check_skonto_configuration valid_skonto_amount get_payment_suggestions validate_payment_type open_sepa_transfer_amount get_payment_select_options_for_bank_transaction create_bank_transaction exchangerate);

use SL::DB::Exchangerate;

use SL::DB::Currency;

  my $fx_gain_loss_amount = 0; # for fx_gain and fx_loss

        # if invoice exchangerate differs from exchangerate of payment

        # deal with fxloss and fxamount

        if ($self->exchangerate and $self->exchangerate != 1 and $self->exchangerate != $exchangerate) {

          my $fxgain_chart = SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_fxgain_accno_id) || die "Can't determine fxgain chart";

          my $fxloss_chart = SL::DB::Manager::Chart->find_by(id => $::instance_conf->get_fxloss_accno_id) || die "Can't determine fxloss chart";

          my $gain_loss_amount = _round($amount * ($exchangerate - $self->exchangerate ) * -1,2);

          my $gain_loss_chart = $gain_loss_amount > 0 ? $fxgain_chart : $fxloss_chart;

          $fx_gain_loss_amount = $gain_loss_amount;

          $new_acc_trans = SL::DB::AccTransaction->new(trans_id       => $self->id,

                                                       chart_id       => $gain_loss_chart->id,

                                                       chart_link     => $gain_loss_chart->link,

                                                       amount         => $gain_loss_amount,

                                                       transdate      => $transdate_obj,

                                                       source         => $source,

                                                       memo           => $memo,

                                                       taxkey         => 0,

                                                       fx_transaction => 0,

                                                       tax_id         => SL::DB::Manager::Tax->find_by(taxkey => 0)->id);

          $new_acc_trans->save;

        };

                                                  amount     => _round($arap_amount * $mult * $exchangerate - $fx_gain_loss_amount),

    $fx_gain_loss_amount *= -1 if $self->is_sales;

    $self->paid($self->paid + _round($paid_amount) + $fx_gain_loss_amount) if $paid_amount;

sub exchangerate {

  my ($self) = @_;

  return 1 if $self->currency_id == $::instance_conf->get_currency_id;

  my $rate = SL::DB::Manager::Exchangerate->find_by(currency_id => $self->currency_id,

                                                    transdate   => $self->transdate,

                                                   );

  return undef unless $rate;

  $self->is_sales ? return $rate->sell : return $rate->buy;

};

=item C<exchangerate>

Returns the exchangerate in database format for the invoice according to that invoice's transdate.

Returns 'sell' for sales, 'buy' for purchases.

use SL::DBUtils qw(selectfirst_array_query);

my ($transdate1, $transdate2, $transdate3, $transdate4, $currency, $exchangerate, $exchangerate2, $exchangerate3, $exchangerate4);

my ($ar_chart,$bank,$ar_amount_chart, $ap_chart, $ap_amount_chart, $fxloss_chart, $fxgain_chart);

  $transdate1 = DateTime->today;

  $transdate3 = DateTime->today->add(days => 2);

  $transdate4 = DateTime->today->add(days => 3);

  $fxgain_chart = SL::DB::Manager::Chart->find_by(accno => '2660') or die "Can't find fxgain_chart in test";

  $fxloss_chart = SL::DB::Manager::Chart->find_by(accno => '2150') or die "Can't find fxloss_chart in test";

  $currency->db->dbh->do('UPDATE defaults SET fxgain_accno_id = ' . $fxgain_chart->id);

  $currency->db->dbh->do('UPDATE defaults SET fxloss_accno_id = ' . $fxloss_chart->id);

  $::instance_conf->reload->data;

  is($fxgain_chart->id,  $::instance_conf->get_fxgain_accno_id, "fxgain_chart was updated in defaults");

  is($fxloss_chart->id,  $::instance_conf->get_fxloss_accno_id, "fxloss_chart was updated in defaults");

  $exchangerate  = SL::DB::Exchangerate->new(transdate   => $transdate1,

                                             buy         => '0.8',

                                             sell        => '0.8',

                                             currency_id => $currency->id,

                                            )->save;

  $exchangerate3 = SL::DB::Exchangerate->new(transdate   => $transdate3,

                                             buy         => '1.55557',

                                             sell        => '1.55557',

                                             currency_id => $currency->id,

                                            )->save;

  $exchangerate4 = SL::DB::Exchangerate->new(transdate   => $transdate4,

                                             buy         => '0.77777',

                                             sell        => '0.77777',

    gldate      => $transdate1,

    transdate   => $transdate1,

    gldate      => $transdate1,

    transdate   => $transdate1,

                                        transdate  => $transdate1,

                                        transdate  => $transdate1,

                                        transdate  => $transdate1,

                                         transdate  => $transdate1,

                                                transdate  => $transdate1,

      transdate    => $transdate1,

                        transdate  => $transdate1->to_kivitendo,

                        transdate  => $transdate1->to_kivitendo,

      amount       => 119,

      netamount    => 100,

      transdate    => $transdate1,

                        transdate  => $transdate1->to_kivitendo,

  my $netamount = $::form->round_amount(75 * $exchangerate->buy,2); #  75 in CUR, 100.00 in EUR

      transdate    => $transdate1,

                        transdate  => $transdate1->to_kivitendo,

                        transdate  => $transdate1->to_kivitendo,

  my $amount    = $::form->round_amount(75 * $exchangerate->buy * 1.19);

      transdate    => $transdate1,

                        transdate  => $transdate1->to_kivitendo,

                       );

};

sub test_ar_currency_tax_not_included_and_payment_2 {

  my $title = 'test_ar_currency_tax_not_included_and_payment_2';

  my $netamount = $::form->round_amount(125 * $exchangerate2->sell,2); # 125.00 in CUR, 100.00 in EUR

  my $amount    = $::form->round_amount($netamount * 1.19,2);          # 148.75 in CUR, 119.00 in EUR

  my $invoice   = SL::DB::Invoice->new(

      invoice      => 0,

      amount       => $amount,

      netamount    => $netamount,

      transdate    => $transdate2,

      taxincluded  => 0,

      customer_id  => $customer->id,

      taxzone_id   => $customer->taxzone_id,

      currency_id  => $currency->id,

      transactions => [],

      notes        => 'test_ar_currency_tax_not_included_and_payment 0.8',

      invnumber    => 'test_ar_currency_tax_not_included_and_payment 0.8',

  );

  $invoice->add_ar_amount_row(

    amount     => $invoice->netamount,

    chart      => $ar_amount_chart,

    tax_id     => $tax->id,

  );

  $invoice->create_ar_row(chart => $ar_chart);

  $invoice->save;

  is($invoice->currency_id , $currency->id , "$title: currency_id has been saved");

  is($invoice->netamount   , 100           , "$title: ar amount has been converted");

  is($invoice->amount      , 119           , "$title: ar amount has been converted");

  is($invoice->taxincluded ,   0           , "$title: ar transaction doesn\"t have taxincluded");

  is(SL::DB::Manager::AccTransaction->find_by(chart_id => $ar_amount_chart->id, trans_id => $invoice->id)->amount, '100.00000', $title . " " . $ar_amount_chart->accno . ": has been converted for currency");

  is(SL::DB::Manager::AccTransaction->find_by(chart_id => $ar_chart->id, trans_id => $invoice->id)->amount, '-119.00000', $title  . " " . $ar_chart->accno . ': has been converted for currency');

  $invoice->pay_invoice(chart_id   => $bank->id,

                        amount     => 123.45,

                        currency   => 'CUR',

                        transdate  => $transdate2->to_kivitendo,

                       );

  $invoice->pay_invoice(chart_id   => $bank->id,

                        amount     => 15.30,

                        currency   => 'CUR',

                        transdate  => $transdate3->to_kivitendo,

                       );

  $invoice->pay_invoice(chart_id   => $bank->id,

                        amount     => 10.00,

                        currency   => 'CUR',

                        transdate  => $transdate4->to_kivitendo,

                       );

  # $invoice->pay_invoice(chart_id   => $bank->id,

  #                       amount     => 30,

  #                       transdate  => $transdate2->to_kivitendo,

  #                      );

  my $fx_transactions = SL::DB::Manager::AccTransaction->get_all(where => [ trans_id => $invoice->id, fx_transaction => 1 ], sort_by => ('acc_trans_id'));

  is(scalar @{$fx_transactions}, 3, "$title: ar transaction has 3 fx transactions");

  is($fx_transactions->[0]->amount, '24.69000', "$title fx transactions 1: 123.45-(123.45*0.8) = 24.69");

  is(scalar @{$invoice->transactions}, 14, "$title ar transaction has 14 transactions (incl. fxtransactions and fx_gain)");

  is($invoice->paid, $invoice->amount, "$title ar transaction paid = amount in default currency");

};

sub test_ar_currency_tax_not_included_and_payment_2_credit_note {

  my $netamount = $::form->round_amount(-125 * $exchangerate2->sell,2); # 125.00 in CUR, 100.00 in EUR

  my $amount    = $::form->round_amount($netamount * 1.19,2);          # 148.75 in CUR, 119.00 in EUR

  my $invoice   = SL::DB::Invoice->new(

      invoice      => 0,

      amount       => $amount,

      netamount    => $netamount,

      transdate    => $transdate2,

      taxincluded  => 0,

      customer_id  => $customer->id,

      taxzone_id   => $customer->taxzone_id,

      currency_id  => $currency->id,

      transactions => [],

      notes        => 'test_ar_currency_tax_not_included_and_payment credit note 0.8',

      invnumber    => 'test_ar_currency_tax_not_included_and_payment credit note 0.8',

  );

  $invoice->add_ar_amount_row(

    amount     => $invoice->netamount,

    chart      => $ar_amount_chart,

    tax_id     => $tax->id,

  );

  $invoice->create_ar_row(chart => $ar_chart);

  $invoice->save;

  is($invoice->currency_id , $currency->id , 'currency_id has been saved');

  is($invoice->netamount   , -100          , 'ar amount has been converted');

  is($invoice->amount      , -119          , 'ar amount has been converted');

  is($invoice->taxincluded ,   0           , 'ar transaction doesn\'t have taxincluded');

  is(SL::DB::Manager::AccTransaction->find_by(chart_id => $ar_amount_chart->id, trans_id => $invoice->id)->amount, '-100.00000', $ar_amount_chart->accno . ': has been converted for currency');

  is(SL::DB::Manager::AccTransaction->find_by(chart_id => $ar_chart->id, trans_id => $invoice->id)->amount, '119.00000', $ar_chart->accno . ': has been converted for currency');

  $invoice->pay_invoice(chart_id   => $bank->id,

                        amount     => -123.45,

                        currency   => 'CUR',

                        transdate  => $transdate2->to_kivitendo,

                       );

  $invoice->pay_invoice(chart_id   => $bank->id,

                        amount     => -25.30,

                        currency   => 'CUR',

                        transdate  => $transdate2->to_kivitendo,

                       );

  my $fx_transactions = SL::DB::Manager::AccTransaction->get_all(where => [ trans_id => $invoice->id, fx_transaction => 1 ], sort_by => ('acc_trans_id'));

  is(scalar @{$fx_transactions}, 2, 'ar transaction has 2 fx transactions');

  is($fx_transactions->[0]->amount, '-24.69000', 'fx transactions 1: 123.45-(123.45*0.8) = 24.69');

  is(scalar @{$invoice->transactions}, 9, 'ar transaction has 9 transactions (incl. fxtransactions)');

  is($invoice->paid, $invoice->amount, 'ar transaction paid = amount in default currency');

};

sub test_ap_currency_tax_not_included_and_payment_2 {

  my $title = 'test_ap_currency_tax_not_included_and_payment_2';

  my $netamount = $::form->round_amount(125 * $exchangerate2->sell,2); # 125.00 in CUR, 100.00 in EUR

  my $amount    = $::form->round_amount($netamount * 1.19,2);          # 148.75 in CUR, 119.00 in EUR

  my $invoice   = SL::DB::PurchaseInvoice->new(

      invoice      => 0,

      amount       => $amount,

      netamount    => $netamount,

      transdate    => $transdate2,

      taxincluded  => 0,

      vendor_id    => $vendor->id,

      taxzone_id   => $vendor->taxzone_id,

      currency_id  => $currency->id,

      transactions => [],

      notes        => 'test_ap_currency_tax_not_included_and_payment_2 0.8 + 1.33333',

      invnumber    => 'test_ap_currency_tax_not_included_and_payment_2 0.8 + 1.33333',

  );

  $invoice->add_ap_amount_row(

    amount     => $invoice->netamount,

    chart      => $ap_amount_chart,

    tax_id     => $tax_9->id,

  );

  $invoice->create_ap_row(chart => $ap_chart);

  $invoice->save;

  is($invoice->currency_id , $currency->id , "$title: currency_id has been saved");

  is($invoice->netamount   ,  100          , "$title: ap amount has been converted");

  is($invoice->amount      ,  119          , "$title: ap amount has been converted");

  is($invoice->taxincluded ,    0          , "$title: ap transaction doesn\'t have taxincluded");

  is(SL::DB::Manager::AccTransaction->find_by(chart_id => $ap_amount_chart->id, trans_id => $invoice->id)->amount, '-100.00000', $ap_amount_chart->accno . ': has been converted for currency');

  is(SL::DB::Manager::AccTransaction->find_by(chart_id => $ap_chart->id, trans_id => $invoice->id)->amount, '119.00000', $ap_chart->accno . ': has been converted for currency');

  $invoice->pay_invoice(chart_id   => $bank->id,

                        amount     => 10,

                        currency   => 'CUR',

                        transdate  => $transdate2->to_kivitendo,

                       );

  $invoice->pay_invoice(chart_id   => $bank->id,

                        amount     => 123.45,

                        currency   => 'CUR',

                        transdate  => $transdate3->to_kivitendo,

  $invoice->pay_invoice(chart_id   => $bank->id,

                        amount     => 15.30,

                        currency   => 'CUR',

                        transdate  => $transdate4->to_kivitendo,

                       );

  my $fx_transactions = SL::DB::Manager::AccTransaction->get_all(where => [ trans_id => $invoice->id, fx_transaction => 1 ], sort_by => ('acc_trans_id'));

  is(scalar @{$fx_transactions}, 3, "$title: ap transaction has 3 fx transactions");

  is($fx_transactions->[0]->amount,  '-2.00000', "$title: fx transaction 1:  10.00-( 10.00*0.80000) =   2.00000");

  is($fx_transactions->[1]->amount,  '68.59000', "$title: fx transaction 2: 123.45-(123.45*1.55557) = -68.58511");

  is($fx_transactions->[2]->amount,  '-3.40000', "$title: fx transaction 3:  15.30-(15.30 *0.77777) =   3.40012");

  my $fx_loss_transactions = SL::DB::Manager::AccTransaction->get_all(where => [ trans_id => $invoice->id, chart_id => $fxloss_chart->id ], sort_by => ('acc_trans_id'));

  my $fx_gain_transactions = SL::DB::Manager::AccTransaction->get_all(where => [ trans_id => $invoice->id, chart_id => $fxgain_chart->id ], sort_by => ('acc_trans_id'));

  is($fx_gain_transactions->[0]->amount,   '0.34000', "$title: fx gain amount ok");

  is($fx_loss_transactions->[0]->amount, '-93.28000', "$title: fx loss amount ok");

  is(scalar @{$invoice->transactions}, 14, "$title: ap transaction has 14 transactions (incl. fxtransactions and gain_loss)");

  is($invoice->paid, $invoice->amount, "$title: ap transaction paid = amount in default currency");

  is(total_amount($invoice), 0,   "$title: even balance");

};

sub test_ap_currency_tax_not_included_and_payment_2_credit_note {

  my $title = 'test_ap_currency_tax_not_included_and_payment_2_credit_note';

  my $netamount = $::form->round_amount(-125 * $exchangerate2->sell,2); # 125.00 in CUR, 100.00 in EUR

  my $amount    = $::form->round_amount($netamount * 1.19,2);          # 148.75 in CUR, 119.00 in EUR

  my $invoice   = SL::DB::PurchaseInvoice->new(

      invoice      => 0,

      amount       => $amount,

      netamount    => $netamount,

      transdate    => $transdate2,

      taxincluded  => 0,

      vendor_id    => $vendor->id,

      taxzone_id   => $vendor->taxzone_id,

      currency_id  => $currency->id,

      transactions => [],

      notes        => 'test_ap_currency_tax_not_included_and_payment credit note 0.8 + 1.33333',

      invnumber    => 'test_ap_currency_tax_not_included_and_payment credit note 0.8 + 1.33333',

  );

  $invoice->add_ap_amount_row(

    amount     => $invoice->netamount,

    chart      => $ap_amount_chart,

    tax_id     => $tax_9->id,

  );

  $invoice->create_ap_row(chart => $ap_chart);

  $invoice->save;

  is($invoice->currency_id , $currency->id , "$title: currency_id has been saved");

  is($invoice->netamount   , -100          , "$title: ap amount has been converted");

  is($invoice->amount      , -119          , "$title: ap amount has been converted");

  is($invoice->taxincluded ,   0           , "$title: ap transaction doesn\'t have taxincluded");

  is(SL::DB::Manager::AccTransaction->find_by(chart_id => $ap_amount_chart->id, trans_id => $invoice->id)->amount, '100.00000', $ap_amount_chart->accno . ': has been converted for currency');

  is(SL::DB::Manager::AccTransaction->find_by(chart_id => $ap_chart->id, trans_id => $invoice->id)->amount, '-119.00000', $ap_chart->accno . ': has been converted for currency');

  $invoice->pay_invoice(chart_id   => $bank->id,

                        amount     => -10,

                        currency   => 'CUR',

                        transdate  => $transdate2->to_kivitendo,

                       );

  $invoice->pay_invoice(chart_id   => $bank->id,

                        amount     => -123.45,

                        currency   => 'CUR',

                        transdate  => $transdate3->to_kivitendo,

                       );

  $invoice->pay_invoice(chart_id   => $bank->id,

                        amount     => -15.30,

                        currency   => 'CUR',

                        transdate  => $transdate4->to_kivitendo,

                       );

  my $fx_transactions = SL::DB::Manager::AccTransaction->get_all(where => [ trans_id => $invoice->id, fx_transaction => 1 ], sort_by => ('acc_trans_id'));

  is(scalar @{$fx_transactions}, 3, "$title: ap transaction has 3 fx transactions");

  is($fx_transactions->[0]->amount,   '2.00000', "$title: fx transaction 1:  10.00-( 10.00*0.80000) =   2.00000");

  is($fx_transactions->[1]->amount, '-68.59000', "$title: fx transaction 2: 123.45-(123.45*1.55557) = -68.58511");

  is($fx_transactions->[2]->amount,   '3.40000', "$title: fx transaction 3:  15.30-(15.30 *0.77777) =   3.40012");

  my $fx_gain_loss_transactions = SL::DB::Manager::AccTransaction->get_all(where => [ trans_id => $invoice->id, chart_id => $fxgain_chart->id ], sort_by => ('acc_trans_id'));

  is($fx_gain_loss_transactions->[0]->amount, '93.28000', "$title: fx gain loss amount ok");

  is(scalar @{$invoice->transactions}, 14, "$title: ap transaction has 14 transactions (incl. fxtransactions and gain_loss)");

  is($invoice->paid, $invoice->amount, "$title: ap transaction paid = amount in default currency");

  is(total_amount($invoice), 0,   "$title: even balance");

test_default_invoice_one_item_19_without_skonto();

test_default_invoice_two_items_19_7_tax_with_skonto();

test_default_invoice_two_items_19_7_without_skonto();

test_default_invoice_two_items_19_7_without_skonto_incomplete_payment();

test_default_invoice_two_items_19_7_tax_without_skonto_multiple_payments();

test_default_purchase_invoice_two_charts_19_7_without_skonto();

test_default_purchase_invoice_two_charts_19_7_tax_partial_unrounded_payment_without_skonto();

test_default_invoice_one_item_19_without_skonto_overpaid();

test_default_invoice_two_items_19_7_tax_without_skonto_multiple_payments_final_difference_as_skonto();

test_default_invoice_two_items_19_7_tax_without_skonto_multiple_payments_final_difference_as_skonto_1cent();

test_default_invoice_two_items_19_7_tax_without_skonto_multiple_payments_final_difference_as_skonto_2cent();

test_default_invoice_one_item_19_multiple_payment_final_difference_as_skonto();

test_default_invoice_one_item_19_multiple_payment_final_difference_as_skonto_1cent();

test_default_purchase_invoice_two_charts_19_7_tax_without_skonto_multiple_payments_final_difference_as_skonto();

test_default_invoice_two_items_19_7_tax_with_skonto_50_50();

test_default_invoice_four_items_19_7_tax_with_skonto_4x_25();

test_default_invoice_four_items_19_7_tax_with_skonto_4x_25_multiple();

test_default_purchase_invoice_two_charts_19_7_with_skonto();

test_default_invoice_four_items_19_7_tax_with_skonto_4x_25_tax_included();

test_default_invoice_two_items_19_7_tax_with_skonto_tax_included();

# exchangerate = 1.33333

test_ar_currency_tax_not_included_and_payment();

test_ar_currency_tax_included();

test_ap_currency_tax_not_included_and_payment();

test_ap_currency_tax_included();

test_ar_currency_tax_not_included_and_payment_2();              # exchangerate 0.8

test_ar_currency_tax_not_included_and_payment_2_credit_note();  # exchangerate 0.8

test_ap_currency_tax_not_included_and_payment_2();             # two exchangerates, with fx_gain_loss

test_ap_currency_tax_not_included_and_payment_2_credit_note(); # two exchangerates, with fx_gain_loss

{ local $TODO = "currently this test fails because the code writing the invoice is buggy, the calculation of skonto is correct";

  my ($acc_trans_sum)  = selectfirst_array_query($::form, $currency->db->dbh, 'SELECT SUM(amount) FROM acc_trans'); is($acc_trans_sum, '0.00000', "sum of all acc_trans is 0");

}