Erfolgsrechnung

[mfinanz.git] / SL / OE.pm

diff --git a/SL/OE.pm b/SL/OE.pm
index 1c812e24f57548c934478f9bd7f80893fa84caf3..7cd5de158a744b5e6dcbaa6201dae1be31325218 100644 (file)
--- a/SL/OE.pm
+++ b/SL/OE.pm
@@ -50,6 +50,7 @@ use SL::DBUtils;
 use SL::HTML::Restrict;
 use SL::IC;
 use SL::TransNumber;
+use SL::Util qw(trim);
 use Text::ParseWords;
 
 use strict;
@@ -158,7 +159,7 @@ sub transactions {
         WHERE proi.projectnumber ILIKE ? AND oi.trans_id = o.id
       ))
 SQL
-    push @values, "%" . $form->{"projectnumber"} . "%", "%" . $form->{"projectnumber"} . "%" ;
+    push @values, like($form->{"projectnumber"}), like($form->{"projectnumber"});
   }
 
   if ($form->{"business_id"}) {
@@ -172,12 +173,12 @@ SQL
 
   } elsif ($form->{$vc}) {
     $query .= " AND ct.name ILIKE ?";
-    push(@values, '%' . $form->{$vc} . '%');
+    push(@values, like($form->{$vc}));
   }
 
   if ($form->{"cp_name"}) {
     $query .= " AND (cp.cp_name ILIKE ? OR cp.cp_givenname ILIKE ?)";
-    push(@values, ('%' . $form->{"cp_name"} . '%')x2);
+    push(@values, (like($form->{"cp_name"}))x2);
   }
 
   if (!$main::auth->assert('sales_all_edit', 1)) {
@@ -208,12 +209,12 @@ SQL
 
   if ($form->{$ordnumber}) {
     $query .= qq| AND o.$ordnumber ILIKE ?|;
-    push(@values, '%' . $form->{$ordnumber} . '%');
+    push(@values, like($form->{$ordnumber}));
   }
 
   if ($form->{cusordnumber}) {
     $query .= qq| AND o.cusordnumber ILIKE ?|;
-    push(@values, '%' . $form->{cusordnumber} . '%');
+    push(@values, like($form->{cusordnumber}));
   }
 
   if($form->{transdatefrom}) {
@@ -248,7 +249,7 @@ SQL
 
   if ($form->{shippingpoint}) {
     $query .= qq| AND o.shippingpoint ILIKE ?|;
-    push(@values, '%' . $form->{shippingpoint} . '%');
+    push(@values, like($form->{shippingpoint}));
   }
 
   if ($form->{taxzone_id} ne '') { # taxzone_id could be 0
@@ -258,7 +259,7 @@ SQL
 
   if ($form->{transaction_description}) {
     $query .= qq| AND o.transaction_description ILIKE ?|;
-    push(@values, '%' . $form->{transaction_description} . '%');
+    push(@values, like($form->{transaction_description}));
   }
 
   if ($form->{periodic_invoices_active} ne $form->{periodic_invoices_inactive}) {
@@ -273,7 +274,7 @@ SQL
   if (($form->{order_probability_value} || '') ne '') {
     my $op  = $form->{order_probability_value} eq 'le' ? '<=' : '>=';
     $query .= qq| AND (o.order_probability ${op} ?)|;
-    push @values, $form->{order_probability_value};
+    push @values, trim($form->{order_probability_value});
   }
 
   if ($form->{expected_billing_date_from}) {
@@ -286,6 +287,33 @@ SQL
     push @values, conv_date($form->{expected_billing_date_to});
   }
 
+  if ($form->{parts_partnumber}) {
+    $query .= <<SQL;
+      AND EXISTS (
+        SELECT orderitems.trans_id
+        FROM orderitems
+        LEFT JOIN parts ON (orderitems.parts_id = parts.id)
+        WHERE (orderitems.trans_id = o.id)
+          AND (parts.partnumber ILIKE ?)
+        LIMIT 1
+      )
+SQL
+    push @values, like($form->{parts_partnumber});
+  }
+
+  if ($form->{parts_description}) {
+    $query .= <<SQL;
+      AND EXISTS (
+        SELECT orderitems.trans_id
+        FROM orderitems
+        WHERE (orderitems.trans_id = o.id)
+          AND (orderitems.description ILIKE ?)
+        LIMIT 1
+      )
+SQL
+    push @values, like($form->{parts_description});
+  }
+
   if ($form->{all}) {
     my @tokens = parse_line('\s+', 0, $form->{all});
     # ordnumber quonumber customer.name vendor.name transaction_description
@@ -295,7 +323,7 @@ SQL
       ct.name     ILIKE ? OR
       o.transaction_description ILIKE ?
     )| for @tokens;
-    push @values, ("%$_%")x4 for @tokens;
+    push @values, (like($_))x4 for @tokens;
   }
 
   my ($cvar_where, @cvar_values) = CVar->build_filter_query('module'         => 'CT',
@@ -420,6 +448,11 @@ sub save {
   $form->{$number_field} ||= $trans_number->create_unique; # set $form->{ordnumber} or $form->{quonumber}
 
   if ($form->{id}) {
+    $query = qq|DELETE FROM custom_variables
+                WHERE (config_id IN (SELECT id        FROM custom_variable_configs WHERE (module = 'ShipTo')))
+                  AND (trans_id  IN (SELECT shipto_id FROM shipto                  WHERE (module = 'OE') AND (trans_id = ?)))|;
+    do_query($form, $dbh, $query, $form->{id});
+
     $query = qq|DELETE FROM shipto | .
              qq|WHERE trans_id = ? AND module = 'OE'|;
     do_query($form, $dbh, $query, $form->{id});
@@ -657,7 +690,7 @@ SQL
   my $tax = 0;
   map { $tax += $form->round_amount($taxaccounts{$_}, 2) } keys %taxaccounts;
 
-  $amount = $form->round_amount($netamount + $tax, 2, 1);
+  $amount = $form->round_amount($netamount + $tax, 2);
   $netamount = $form->round_amount($netamount, 2);
 
   if ($form->{currency} eq $form->{defaultcurrency}) {
@@ -799,7 +832,7 @@ sub load_periodic_invoice_config {
 
     if ($config_obj) {
       my $config = { map { $_ => $config_obj->$_ } qw(active terminated periodicity order_value_periodicity start_date_as_date end_date_as_date first_billing_date_as_date extend_automatically_by ar_chart_id
-                                                      print printer_id copies direct_debit) };
+                                                      print printer_id copies direct_debit send_email email_recipient_contact_id email_recipient_address email_sender email_subject email_body) };
       $form->{periodic_invoices_config} = YAML::Dump($config);
     }
   }
@@ -923,9 +956,7 @@ 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,
-                     (SELECT c.accno FROM chart c WHERE d.rndgain_accno_id   = c.id) AS rndgain_accno,
-                     (SELECT c.accno FROM chart c WHERE d.rndloss_accno_id   = c.id) AS rndloss_accno
+                     (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);
@@ -1010,6 +1041,15 @@ sub retrieve {
       map { $form->{$_} = $ref->{$_} } keys %$ref;
       $sth->finish;
 
+      if ($form->{shipto_id}) {
+        my $cvars = CVar->get_custom_variables(
+          dbh      => $dbh,
+          module   => 'ShipTo',
+          trans_id => $form->{shipto_id},
+        );
+        $form->{"shiptocvar_$_->{name}"} = $_->{value} for @{ $cvars };
+      }
+
       # get printed, emailed and queued
       $query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|;
       $sth = prepare_execute_query($form, $dbh, $query, $form->{id});