X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/ff159a4d47b9a2d10744dcfc23da2c63605c8a32..eeb5375ee7727c956cc357cc8f90b19d1bfe80b9:/SL/IS.pm

diff --git a/SL/IS.pm b/SL/IS.pm
index fdc70ab3a..a2bfac665 100644
--- a/SL/IS.pm
+++ b/SL/IS.pm
@@ -39,29 +39,35 @@ use List::Util qw(max sum0);
 use List::MoreUtils qw(any);
 
 use Carp;
+use Data::Dumper;
+
 use SL::AM;
 use SL::ARAP;
 use SL::CVar;
 use SL::Common;
 use SL::DATEV qw(:CONSTANTS);
+use SL::Util qw(trim);
 use SL::DBUtils;
 use SL::DO;
 use SL::GenericTranslations;
 use SL::HTML::Restrict;
+use SL::Locale::String qw(t8);
 use SL::MoreCommon;
 use SL::IC;
 use SL::IO;
 use SL::TransNumber;
 use SL::DB::Chart;
+use SL::DB::Customer;
 use SL::DB::Default;
 use SL::DB::Draft;
 use SL::DB::Tax;
 use SL::DB::TaxZone;
+use SL::DB::ValidityToken;
 use SL::TransNumber;
 use SL::DB;
 use SL::Presenter::Part qw(type_abbreviation classification_abbreviation);
-use Data::Dumper;
-
+use SL::Helper::QrBillFunctions qw(get_qrbill_account assemble_ref_number);
+use SL::Helper::ISO3166;
 use strict;
 use constant PCLASS_OK             =>   0;
 use constant PCLASS_NOTFORSALE     =>   1;
@@ -620,6 +626,32 @@ sub invoice_details {
   $form->{iap_final_amount_nofmt} = $form->{invtotal_nofmt} - $form->{iap_amount_nofmt};
   $form->{iap_final_amount}       = $form->format_amount($myconfig, $form->{iap_final_amount_nofmt}, 2);
 
+  # set variables for swiss QR bill, if feature enabled
+  # handling errors gracefully (don't die if undef)
+  my $create_qrbill_invoices = $::instance_conf->get_create_qrbill_invoices;
+  if ($::instance_conf->get_create_qrbill_invoices && $form->{formname} eq 'invoice') {
+    my ($qr_account, $error) = get_qrbill_account();
+
+    # case 1: QR-Reference number and QR-IBAN
+    # case 2: without reference number and regular IBAN
+    if ($create_qrbill_invoices == 1) {
+      $form->{qrbill_iban} = $qr_account->{qr_iban};
+    } elsif ($create_qrbill_invoices == 2) {
+      $form->{qrbill_iban} = $qr_account->{iban};
+    }
+
+    my $biller_country = $::instance_conf->get_address_country() || 'CH';
+    my $biller_countrycode = SL::Helper::ISO3166::map_name_to_alpha_2_code($biller_country);
+    $form->{qrbill_biller_countrycode} = $biller_countrycode;
+
+    my $customer_country = $form->{billing_address_id} ?
+                            $form->{billing_address_country} || 'CH' :
+                            $form->{country} || 'CH';
+    my $customer_countrycode = SL::Helper::ISO3166::map_name_to_alpha_2_code($customer_country);
+    $form->{qrbill_customer_countrycode} = $customer_countrycode;
+
+    $form->{qrbill_amount} = sprintf("%.2f", $form->parse_amount($myconfig, $form->{'total'}));
+  }
   $main::lxdebug->leave_sub();
 }
 
@@ -741,6 +773,16 @@ sub post_invoice {
 sub _post_invoice {
   my ($self, $myconfig, $form, $provided_dbh, %params) = @_;
 
+  my $validity_token;
+  if (!$form->{id}) {
+    $validity_token = SL::DB::Manager::ValidityToken->fetch_valid_token(
+      scope => SL::DB::ValidityToken::SCOPE_SALES_INVOICE_POST(),
+      token => $form->{form_validity_token},
+    );
+
+    die $::locale->text('The form is not valid anymore.') if !$validity_token;
+  }
+
   my $payments_only = $params{payments_only};
   my $dbh = $provided_dbh || SL::DB->client->dbh;
   my $restricter = SL::HTML::Restrict->create;
@@ -793,14 +835,23 @@ sub _post_invoice {
   if ($form->{currency} eq $defaultcurrency) {
     $form->{exchangerate} = 1;
   } else {
-    $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
+    $exchangerate         = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, 'buy');
+    $form->{exchangerate} = $form->parse_amount($myconfig, $form->{exchangerate}, 5);
+
+    # if default exchangerate is not defined, define one
+    unless ($exchangerate) {
+      $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, $form->{exchangerate}, 0);
+      # delete records exchangerate -> if user sets new invdate for record
+      $query = qq|UPDATE ar set exchangerate = NULL where id = ?|;
+      do_query($form, $dbh, $query, $form->{"id"});
+    }
+    # update record exchangerate, if the default is set and differs from current
+    if ($exchangerate && ($form->{exchangerate} != $exchangerate)) {
+      $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
+                                 $form->{exchangerate}, 0, $form->{id}, 'ar');
+    }
   }
 
-  $form->{exchangerate} =
-    ($exchangerate)
-    ? $exchangerate
-    : $form->parse_amount($myconfig, $form->{exchangerate});
-
   $form->{expense_inventory} = "";
 
   my %baseunits;
@@ -916,7 +967,11 @@ sub _post_invoice {
 
       next if $payments_only;
 
-      if ($form->{"inventory_accno_$i"} || $form->{"part_type_$i"} eq 'assembly') {
+
+
+      # do cogs only if inventory_sytem perpetual is active
+      if  ($::instance_conf->get_inventory_system eq 'perpetual'
+        && $form->{"inventory_accno_$i"} || $form->{"part_type_$i"} eq 'assembly') {
 
         if ($form->{"part_type_$i"} eq 'assembly') {
           # record assembly item as allocated
@@ -969,7 +1024,7 @@ SQL
                  $form->{"sellprice_$i"}, $fxsellprice,
                  $form->{"discount_$i"}, $allocated, 'f',
                  $form->{"unit_$i"}, conv_date($form->{"reqdate_$i"}), conv_i($form->{"project_id_$i"}),
-                 $form->{"serialnumber_$i"}, $pricegroup_id,
+                 trim($form->{"serialnumber_$i"}), $pricegroup_id,
                  $baseqty, $form->{"subtotal_$i"} ? 't' : 'f',
                  $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
                  $form->{"lastcost_$i"},
@@ -990,7 +1045,7 @@ SQL
                                   dbh          => $dbh);
     }
     # link previous items with invoice items
-    foreach (qw(delivery_order_items orderitems invoice)) {
+    foreach (qw(delivery_order_items orderitems invoice reclamation_items)) {
       if (!$form->{useasnew} && $form->{"converted_from_${_}_id_$i"}) {
         RecordLinks->create_links('dbh'        => $dbh,
                                   'mode'       => 'ids',
@@ -1077,12 +1132,6 @@ SQL
   # reverse AR
   $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1;
 
-  # update exchangerate
-  if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
-    $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate},
-                               $form->{exchangerate}, 0);
-  }
-
   $project_id = conv_i($form->{"globalproject_id"});
   # entsprechend auch beim Bestimmen des Steuerschlüssels in Taxkey.pm berücksichtigen
   my $taxdate = $form->{tax_point} ||$form->{deliverydate} || $form->{invdate};
@@ -1137,6 +1186,8 @@ SQL
           }
           # no tax, no prob
           if ($tax and $tax->rate != 0) {
+            die "Need a valid chart id for table defaults column advance_payment_taxable_7"  if $tax->taxkey == 2 && ! $::instance_conf->get_advance_payment_taxable_7_id;
+            die "Need a valid chart id for table defaults column advance_payment_taxable_19" if $tax->taxkey == 3 && ! $::instance_conf->get_advance_payment_taxable_19_id;
             my $transfer_chart = $tax->taxkey == 2 ? SL::DB::Chart->new(id => $::instance_conf->get_advance_payment_taxable_7_id)->load
                               :  $tax->taxkey == 3 ? SL::DB::Chart->new(id => $::instance_conf->get_advance_payment_taxable_19_id)->load
                               :  undef;
@@ -1411,8 +1462,10 @@ SQL
 
       $diff = 0;
 
-      # update exchange rate
+      # update exchange rate for PAYMENTS
+      # exchangerate contains a new exchangerate of the payment date
       if (($form->{currency} ne $defaultcurrency) && !$exchangerate) {
+        $form->{script} = 'is.pl';
         $form->update_exchangerate($dbh, $form->{currency},
                                    $form->{"datepaid_$i"},
                                    $form->{"exchangerate_$i"}, 0);
@@ -1467,6 +1520,43 @@ SQL
 
   $amount = $form->round_amount( $netamount + $tax, 2, 1);
 
+  # qr reference
+  my $qr_reference;
+  if ($form->{has_qr_reference}) {
+    # (re-)generate reference number
+
+    # get qr-account data
+    my ($qr_account, $error) = get_qrbill_account();
+    die $error if !$qr_account;
+
+    # get customer object
+    my $customer_obj = SL::DB::Customer->load_cached(conv_i($form->{customer_id}));
+
+    # assemble reference number with check digit
+    ($qr_reference, $error) = assemble_ref_number($qr_account->{bank_account_id},
+                                                  $customer_obj->{customernumber},
+                                                  $form->{invnumber});
+    die $error if !$qr_reference;
+  } else {
+    # if the reference number has been previously defined keep it
+    if (defined $form->{qr_reference}) {
+      $qr_reference = $form->{qr_reference};
+    } else {
+      $qr_reference = undef;
+    }
+  }
+
+  # add invoice number to unstructured message if feature enabled in client config
+  my $qr_unstructured_message = $form->{"qr_unstructured_message"};
+  if ($::instance_conf->get_qrbill_copy_invnumber && $form->{formname} eq 'invoice') {
+    my $invnumber = $form->{"invnumber"};
+    if ($qr_unstructured_message eq '') {
+      $qr_unstructured_message = $invnumber;
+    } elsif (rindex($qr_unstructured_message, $invnumber) == -1) {
+      $qr_unstructured_message .= '; ' . $invnumber;
+    }
+  }
+
   # save AR record
   #erweiterung fuer lieferscheinnummer (donumber) 12.02.09 jb
 
@@ -1484,7 +1574,7 @@ SQL
                 globalproject_id               = ?, delivery_customer_id             = ?,
                 transaction_description        = ?, delivery_vendor_id               = ?,
                 donumber    = ?, invnumber_for_credit_note = ?,        direct_debit  = ?, qrbill_without_amount = ?,
-                delivery_term_id = ?
+                qr_reference = ?, qr_unstructured_message = ?, delivery_term_id = ?
               WHERE id = ?|;
   @values = (          $form->{"invnumber"},           $form->{"ordnumber"},             $form->{"quonumber"},          $form->{"cusordnumber"},
              conv_date($form->{"invdate"}),  conv_date($form->{"orddate"}),    conv_date($form->{"quodate"}), conv_date($form->{tax_point}), conv_i($form->{"customer_id"}),
@@ -1498,7 +1588,7 @@ SQL
                 conv_i($form->{"globalproject_id"}),                              conv_i($form->{"delivery_customer_id"}),
                        $form->{transaction_description},                          conv_i($form->{"delivery_vendor_id"}),
                        $form->{"donumber"}, $form->{"invnumber_for_credit_note"},        $form->{direct_debit} ? 't' : 'f', $form->{qrbill_without_amount} ? 't' : 'f',
-                conv_i($form->{delivery_term_id}),
+                $qr_reference, $qr_unstructured_message, conv_i($form->{delivery_term_id}),
                 conv_i($form->{"id"}));
   do_query($form, $dbh, $query, @values);
 
@@ -1512,6 +1602,14 @@ SQL
          WHERE id = ?!;
     do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
     do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
+
+    $query = <<SQL;
+      UPDATE orderitems
+      SET recurring_billing_invoice_id = NULL
+      WHERE recurring_billing_invoice_id = ?
+SQL
+
+    do_query($form, $dbh, $query, conv_i($form->{"storno_id"}));
   }
 
   # maybe we are in a larger transaction and the current
@@ -1529,27 +1627,18 @@ SQL
 
   Common::webdav_folder($form);
 
-  if ($form->{convert_from_ar_ids}) {
-    RecordLinks->create_links('dbh'        => $dbh,
-                              'mode'       => 'ids',
-                              'from_table' => 'ar',
-                              'from_ids'   => $form->{convert_from_ar_ids},
-                              'to_table'   => 'ar',
-                              'to_id'      => $form->{id},
-    );
-    delete $form->{convert_from_ar_ids};
-  }
-
   # Link this record to the records it was created from.
-  if ($form->{convert_from_oe_ids}) {
-    RecordLinks->create_links('dbh'        => $dbh,
-                              'mode'       => 'ids',
-                              'from_table' => 'oe',
-                              'from_ids'   => $form->{convert_from_oe_ids},
-                              'to_table'   => 'ar',
-                              'to_id'      => $form->{id},
+  foreach (qw(oe ar reclamations)) {
+    if ($form->{"convert_from_${_}_ids"}) {
+      RecordLinks->create_links('dbh'        => $dbh,
+                                'mode'       => 'ids',
+                                'from_table' => $_,
+                                'from_ids'   => $form->{"convert_from_${_}_ids"},
+                                'to_table'   => 'ar',
+                                'to_id'      => $form->{id},
       );
-    delete $form->{convert_from_oe_ids};
+      delete $form->{"convert_from_${_}_ids"};
+    }
   }
 
   my @convert_from_do_ids = map { $_ * 1 } grep { $_ } split m/\s+/, $form->{convert_from_do_ids};
@@ -1616,6 +1705,9 @@ SQL
     $shop->connector->set_orderstatus($shop_order->shop_trans_id, "completed");
   }
 
+  $validity_token->delete if $validity_token;
+  delete $form->{form_validity_token};
+
   return 1;
 }
 
@@ -1684,7 +1776,7 @@ sub transfer_out {
     my ($err, $qty, $wh_id, $bin_id, $chargenumber);
 
     if ($::instance_conf->get_sales_serial_eq_charge && $form->{"serialnumber_$i"}) {
-      my @serials = split(" ", $form->{"serialnumber_$i"});
+      my @serials = split(" ", trim($form->{"serialnumber_$i"}));
       if (scalar @serials != $form->{"qty_$i"}) {
         push @errors, $::locale->text("Cannot transfer #1 qty with #2 serial number(s)", $form->{"qty_$i"}, scalar @serials);
         last;
@@ -2251,7 +2343,7 @@ sub _retrieve_invoice {
            a.mtime, a.itime,
            a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type,
            a.transaction_description, a.donumber, a.invnumber_for_credit_note,
-           a.marge_total, a.marge_percent, a.direct_debit, a.qrbill_without_amount, a.delivery_term_id,
+           a.marge_total, a.marge_percent, a.direct_debit, a.qrbill_without_amount, a.qr_reference, a.qr_unstructured_message, a.delivery_term_id,
            dc.dunning_description,
            e.name AS employee
          FROM ar a
@@ -2263,11 +2355,12 @@ sub _retrieve_invoice {
     $form->{mtime} = $form->{itime} if !$form->{mtime};
     $form->{lastmtime} = $form->{mtime};
 
-    $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
+    ($form->{exchangerate}, $form->{record_forex}) = $form->check_exchangerate($myconfig, $form->{currency}, $form->{invdate}, "buy", $id, 'ar');
 
     foreach my $vc (qw(customer vendor)) {
       next if !$form->{"delivery_${vc}_id"};
-      ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id);
+      ($form->{"delivery_${vc}_string"}) = selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|,
+                                                           $form->{"delivery_${vc}_id"});
     }
 
     # get shipto
@@ -2445,7 +2538,7 @@ sub get_customer {
        LEFT JOIN currencies cu ON (c.currency_id=cu.id)
        WHERE 1 = 1 $where|;
   $ref = selectfirst_hashref_query($form, $dbh, $query, @values);
-
+  die t8("Cannot find a single customer. Maybe there is no customer yet?") unless $ref;
   delete $ref->{salesman_id} if !$ref->{salesman_id};
   delete $ref->{payment_id}  if !$ref->{payment_id};
 
@@ -2492,7 +2585,7 @@ sub get_customer {
             AND e.transdate = o.transdate)
        FROM oe o
        WHERE o.customer_id = ?
-         AND o.quotation = '0'
+         AND o.record_type = 'sales_order'
          AND o.closed = '0'|;
   my $sth = prepare_execute_query($form, $dbh, $query, $cid);