X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FOE.pm;h=d90b6aa000fd1dd35b4291504cd070fc4f23d9c4;hb=d2550089aa9f5d321e025df59815b0eabc7037ab;hp=7a35fa717aedd5b186809b2c4badac97b12372e9;hpb=300f3739cfe93d10a867a778a1430068c00c32a8;p=kivitendo-erp.git

diff --git a/SL/OE.pm b/SL/OE.pm
index 7a35fa717..d90b6aa00 100644
--- a/SL/OE.pm
+++ b/SL/OE.pm
@@ -52,7 +52,7 @@ sub transactions {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = $form->get_standard_dbh;
 
   my $query;
   my $ordnumber = 'ordnumber';
@@ -78,7 +78,7 @@ sub transactions {
 
   $query =
     qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate, | .
-    qq|  o.amount, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
+    qq|  o.amount, ct.${vc}number, ct.name, o.netamount, o.${vc}_id, o.globalproject_id, | .
     qq|  o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia, | .
     qq|  o.transaction_description, | .
     qq|  o.marge_total, o.marge_percent, | .
@@ -91,7 +91,7 @@ sub transactions {
     qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
     qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
     qq|LEFT JOIN employee s ON (o.salesman_id = s.id) | .
-    qq|LEFT JOIN exchangerate ex ON (ex.curr = o.curr | .
+    qq|LEFT JOIN exchangerate ex ON (ex.currency_id = o.currency_id | .
     qq|  AND ex.transdate = o.transdate) | .
     qq|LEFT JOIN project pr ON (o.globalproject_id = pr.id) | .
     qq|$periodic_invoices_joins | .
@@ -115,11 +115,11 @@ sub transactions {
 
   if ($form->{"projectnumber"}) {
     $query .= <<SQL;
-      AND (pr.projectnumber ILIKE ?) OR EXISTS (
+      AND ((pr.projectnumber ILIKE ?) OR EXISTS (
         SELECT * FROM orderitems oi
         LEFT JOIN project proi ON proi.id = oi.project_id
         WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
-      )
+      ))
 SQL
     push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
   }
@@ -190,7 +190,7 @@ SQL
   }
 
   if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
-    my $not  = 'NOT' if ($form->{periodic_invoices_inactive});
+    my $not  = $form->{periodic_invoices_inactive} ? 'NOT' : '';
     $query  .= qq| AND ${not} COALESCE(pcfg.active, 'f')|;
   }
 
@@ -204,7 +204,7 @@ SQL
     "quonumber"               => "o.quonumber",
     "name"                    => "ct.name",
     "employee"                => "e.name",
-    "salesman"                => "e.name",
+    "salesman"                => "s.name",
     "shipvia"                 => "o.shipvia",
     "transaction_description" => "o.transaction_description"
   );
@@ -226,7 +226,6 @@ SQL
   }
 
   $sth->finish;
-  $dbh->disconnect;
 
   $main::lxdebug->leave_sub();
 }
@@ -312,7 +311,7 @@ sub save {
     $query = qq|SELECT nextval('id')|;
     ($form->{id}) = selectrow_query($form, $dbh, $query);
 
-    $query = qq|INSERT INTO oe (id, ordnumber, employee_id) VALUES (?, '', ?)|;
+    $query = qq|INSERT INTO oe (id, ordnumber, employee_id, currency_id) VALUES (?, '', ?, (SELECT currency_id FROM defaults))|;
     do_query($form, $dbh, $query, $form->{id}, $form->{employee_id});
   }
 
@@ -420,9 +419,13 @@ sub save {
 
       $reqdate = ($form->{"reqdate_$i"}) ? $form->{"reqdate_$i"} : undef;
 
-      # get pricegroup_id and save ist
+      # Get pricegroup_id and save it. Unfortunately the interface
+      # also uses ID "0" for signalling that none is selected, but "0"
+      # must not be stored in the database. Therefore we cannot simply
+      # use conv_i().
       ($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
       $pricegroup_id *= 1;
+      $pricegroup_id  = undef if !$pricegroup_id;
 
       # save detail record in orderitems table
       my $orderitems_id = $form->{"orderitems_id_$i"};
@@ -442,7 +445,7 @@ sub save {
            $form->{"qty_$i"}, $baseqty,
            $fxsellprice, $form->{"discount_$i"},
            $form->{"unit_$i"}, conv_date($reqdate), conv_i($form->{"project_id_$i"}),
-           $form->{"serialnumber_$i"}, $form->{"ship_$i"}, conv_i($pricegroup_id),
+           $form->{"serialnumber_$i"}, $form->{"ship_$i"}, $pricegroup_id,
            $form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
            $form->{"cusordnumber_$i"}, $form->{"subtotal_$i"} ? 't' : 'f',
            $form->{"marge_percent_$i"}, $form->{"marge_absolut_$i"},
@@ -491,7 +494,7 @@ sub save {
     qq|UPDATE oe SET
          ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, vendor_id = ?,
          customer_id = ?, amount = ?, netamount = ?, reqdate = ?, taxincluded = ?,
-         shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, curr = ?, closed = ?,
+         shippingpoint = ?, shipvia = ?, notes = ?, intnotes = ?, currency_id = (SELECT id FROM currencies WHERE name=?), closed = ?,
          delivered = ?, proforma = ?, quotation = ?, department_id = ?, language_id = ?,
          taxzone_id = ?, shipto_id = ?, payment_id = ?, delivery_vendor_id = ?, delivery_customer_id = ?,
          globalproject_id = ?, employee_id = ?, salesman_id = ?, cp_id = ?, transaction_description = ?, marge_total = ?, marge_percent = ?
@@ -503,7 +506,7 @@ sub save {
              $amount, $netamount, conv_date($reqdate),
              $form->{taxincluded} ? 't' : 'f', $form->{shippingpoint},
              $form->{shipvia}, $form->{notes}, $form->{intnotes},
-             substr($form->{currency}, 0, 3), $form->{closed} ? 't' : 'f',
+             $form->{currency}, $form->{closed} ? 't' : 'f',
              $form->{delivered} ? "t" : "f", $form->{proforma} ? 't' : 'f',
              $quotation, conv_i($form->{department_id}),
              conv_i($form->{language_id}), conv_i($form->{taxzone_id}),
@@ -658,7 +661,8 @@ sub delete {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect_noauto($myconfig);
+  my $dbh = $form->get_standard_dbh;
+  $dbh->begin_work;
 
   # delete spool files
   my $query = qq|SELECT s.spoolfile FROM status s | .
@@ -687,11 +691,6 @@ sub delete {
            qq|WHERE trans_id = ?|;
   do_query($form, $dbh, $query, @values);
 
-  # delete OE record
-  $query = qq|DELETE FROM oe | .
-           qq|WHERE id = ?|;
-  do_query($form, $dbh, $query, @values);
-
   # delete individual entries
   $query = qq|DELETE FROM orderitems | .
            qq|WHERE trans_id = ?|;
@@ -701,8 +700,12 @@ sub delete {
            qq|WHERE trans_id = ? AND module = 'OE'|;
   do_query($form, $dbh, $query, @values);
 
+  # delete OE record
+  $query = qq|DELETE FROM oe | .
+           qq|WHERE id = ?|;
+  do_query($form, $dbh, $query, @values);
+
   my $rc = $dbh->commit;
-  $dbh->disconnect;
 
   if ($rc) {
     my $spool = $::lx_office_conf{paths}->{spool};
@@ -726,9 +729,6 @@ sub retrieve {
 
   my ($query, $query_add, @values, @ids, $sth);
 
-  my $ic_cvar_configs = CVar->get_configs(module => 'IC',
-                                          dbh    => $dbh);
-
   # translate the ids (given by id_# and trans_id_#) into one array of ids, so we can join them later
   map {
     push @ids, $form->{"trans_id_$_"}
@@ -759,14 +759,13 @@ sub retrieve {
                      (SELECT c.accno FROM chart c WHERE d.income_accno_id    = c.id) AS income_accno,
                      (SELECT c.accno FROM chart c WHERE d.expense_accno_id   = c.id) AS expense_accno,
                      (SELECT c.accno FROM chart c WHERE d.fxgain_accno_id    = c.id) AS fxgain_accno,
-                     (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id    = c.id) AS fxloss_accno,
-              d.curr AS currencies
+                     (SELECT c.accno FROM chart c WHERE d.fxloss_accno_id    = c.id) AS fxloss_accno
               $query_add
               FROM defaults d|;
   my $ref = selectfirst_hashref_query($form, $dbh, $query);
   map { $form->{$_} = $ref->{$_} } keys %$ref;
 
-  ($form->{currency}) = split(/:/, $form->{currencies}) unless ($form->{currency});
+  $form->{currency} = $form->get_default_currency($myconfig);
 
   # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure
   # we come from invoices, feel free.
@@ -785,7 +784,7 @@ sub retrieve {
     $query =
       qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate,
            o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes,
-           o.curr AS currency, e.name AS employee, o.employee_id, o.salesman_id,
+           (SELECT cu.name FROM currencies cu WHERE cu.id=o.currency_id) AS currency, e.name AS employee, o.employee_id, o.salesman_id,
            o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
            o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber,
            d.description AS department, o.payment_id, o.language_id, o.taxzone_id,
@@ -803,14 +802,16 @@ sub retrieve {
     $sth = prepare_execute_query($form, $dbh, $query, @values);
 
     $ref = $sth->fetchrow_hashref("NAME_lc");
-    map { $form->{$_} = $ref->{$_} } keys %$ref;
 
-    $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ?
-                                         "quonumber" : "ordnumber"};
+    if ($ref) {
+      map { $form->{$_} = $ref->{$_} } keys %$ref;
 
-    # set all entries for multiple ids blank that yield different information
-    while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
-      map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
+      $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"};
+
+      # set all entries for multiple ids blank that yield different information
+      while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
+        map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
+      }
     }
 
     # if not given, fill transdate with current_date
@@ -868,7 +869,7 @@ sub retrieve {
            c2.accno AS income_accno,    c2.new_chart_id AS income_new_chart,    date($transdate) - c2.valid_from as income_valid,
            c3.accno AS expense_accno,   c3.new_chart_id AS expense_new_chart,   date($transdate) - c3.valid_from as expense_valid,
            oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe,
-           p.partnumber, p.assembly, o.description, o.qty,
+           p.partnumber, p.assembly, p.listprice, o.description, o.qty,
            o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id,
            o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost,
            o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription,
@@ -1034,7 +1035,7 @@ sub order_details {
   my ($self, $myconfig, $form) = @_;
 
   # connect to database
-  my $dbh = $form->dbconnect($myconfig);
+  my $dbh = $form->get_standard_dbh;
   my $query;
   my @values = ();
   my $sth;
@@ -1106,7 +1107,7 @@ sub order_details {
   $form->{discount} = [];
 
   $form->{TEMPLATE_ARRAYS} = { };
-  IC->prepare_parts_for_printing();
+  IC->prepare_parts_for_printing(myconfig => $myconfig, form => $form);
 
   my $ic_cvar_configs = CVar->get_configs(module => 'IC');
 
@@ -1180,12 +1181,18 @@ sub order_details {
       my ($dec)         = ($sellprice =~ /\.(\d+)/);
       my $decimalplaces = max 2, length($dec);
 
-      my $parsed_discount      = $form->parse_amount($myconfig, $form->{"discount_$i"});
-      my $linetotal_exact      =                     $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
-      my $linetotal            = $form->round_amount($linetotal_exact, 2);
-      my $discount             = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor} - ($linetotal - $linetotal_exact),
-                                                     $decimalplaces);
-      my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2);
+      my $parsed_discount            = $form->parse_amount($myconfig, $form->{"discount_$i"});
+
+      my $linetotal_exact            = $form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor};
+      my $linetotal                  = $form->round_amount($linetotal_exact, 2);
+
+      my $nodiscount_exact_linetotal = $form->{"qty_$i"} * $sellprice                                  / $price_factor->{factor};
+      my $nodiscount_linetotal       = $form->round_amount($nodiscount_exact_linetotal,2);
+
+      my $discount                   = $nodiscount_linetotal - $linetotal; # is always rounded because $nodiscount_linetotal and $linetotal are rounded
+
+      my $discount_round_error       = $discount + ($linetotal_exact - $nodiscount_exact_linetotal); # not used
+
       $form->{"netprice_$i"}   = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2);
 
       push @{ $form->{TEMPLATE_ARRAYS}->{netprice} },       ($form->{"netprice_$i"} != 0) ? $form->format_amount($myconfig, $form->{"netprice_$i"}, $decimalplaces) : '';
@@ -1217,8 +1224,7 @@ sub order_details {
         $subtotal_header     = 0;
 
       } else {
-        push @{ $form->{TEMPLATE_ARRAYS}->{discount_sub} },   "";
-        push @{ $form->{TEMPLATE_ARRAYS}->{nodiscount_sub} }, "";
+        push @{ $form->{TEMPLATE_ARRAYS}->{$_} }, "" for qw(discount_sub nodiscount_sub discount_sub_nofmt nodiscount_sub_nofmt);
       }
 
       if (!$form->{"discount_$i"}) {
@@ -1292,7 +1298,9 @@ sub order_details {
         $sth->finish;
       }
 
-      map { push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} }, $form->{"ic_cvar_$_->{name}_$i"} } @{ $ic_cvar_configs };
+      push @{ $form->{TEMPLATE_ARRAYS}->{"ic_cvar_$_->{name}"} },
+        CVar->format_to_template(CVar->parse($form->{"ic_cvar_$_->{name}_$i"}, $_), $_)
+          for @{ $ic_cvar_configs };
     }
   }