X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FOE.pm;h=3f7dbaa75d3f14c97337b88ca245225cd4cc5437;hb=1465da30be12244675541d59b22e94a0f90e0b33;hp=a4d9a2905e1fee8bcb20d910487df987199d83fa;hpb=0af7a3a57559268dba5c22e1e6a64100622d9d1d;p=kivitendo-erp.git

diff --git a/SL/OE.pm b/SL/OE.pm
index a4d9a2905..3f7dbaa75 100644
--- a/SL/OE.pm
+++ b/SL/OE.pm
@@ -41,6 +41,22 @@ use SL::CVar;
 use SL::DBUtils;
 use SL::IC;
 
+use strict;
+
+=head1 NAME
+
+OE.pm - Order entry module
+
+=head1 DESCRIPTION
+
+OE.pm is part of the OE module. OE is responsible for sales and purchase orders, as well as sales quotations and purchase requests. This file abstracts the database tables C<oe> and C<orderitems>.
+
+=head1 FUNCTIONS
+
+=over 4
+
+=cut
+
 sub transactions {
   $main::lxdebug->enter_sub();
 
@@ -73,7 +89,8 @@ sub transactions {
     qq|  o.marge_total, o.marge_percent, | .
     qq|  ex.$rate AS exchangerate, | .
     qq|  pr.projectnumber AS globalprojectnumber, | .
-    qq|  e.name AS employee, s.name AS salesman | .
+    qq|  e.name AS employee, s.name AS salesman, | .
+    qq|  ct.${vc}number AS vcnumber, ct.country, ct.ustid  | .
     qq|FROM oe o | .
     qq|JOIN $vc ct ON (o.${vc}_id = ct.id) | .
     qq|LEFT JOIN employee e ON (o.employee_id = e.id) | .
@@ -96,7 +113,18 @@ sub transactions {
       qq|AND ((globalproject_id = ?) OR EXISTS | .
       qq|  (SELECT * FROM orderitems oi | .
       qq|   WHERE oi.project_id = ? AND oi.trans_id = o.id))|;
-    push(@values, $form->{"project_id"}, $form->{"project_id"});
+    push(@values, conv_i($form->{"project_id"}), conv_i($form->{"project_id"}));
+  }
+
+  if ($form->{"projectnumber"}) {
+    $query .= <<SQL;
+      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"} . "%" ;
   }
 
   if ($form->{"${vc}_id"}) {
@@ -185,7 +213,7 @@ sub transactions {
 
   my %id = ();
   $form->{OE} = [];
-  while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+  while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
     $ref->{exchangerate} = 1 unless $ref->{exchangerate};
     push @{ $form->{OE} }, $ref if $ref->{id} != $id{ $ref->{id} };
     $id{ $ref->{id} } = $ref->{id};
@@ -288,6 +316,8 @@ sub save {
   my $project_id;
   my $reqdate;
   my $taxrate;
+  my $taxbase;
+  my $taxdiff;
   my $taxamount = 0;
   my $fxsellprice;
   my %taxbase;
@@ -359,7 +389,7 @@ sub save {
 
       if ($form->round_amount($taxrate, 7) == 0) {
         if ($form->{taxincluded}) {
-          foreach $item (@taxaccounts) {
+          foreach my $item (@taxaccounts) {
             $taxamount = $form->round_amount($linetotal * $form->{"${item}_rate"} / (1 + abs($form->{"${item}_rate"})), 2);
             $taxaccounts{$item} += $taxamount;
             $taxdiff            += $taxamount;
@@ -367,13 +397,13 @@ sub save {
           }
           $taxaccounts{ $taxaccounts[0] } += $taxdiff;
         } else {
-          foreach $item (@taxaccounts) {
+          foreach my $item (@taxaccounts) {
             $taxaccounts{$item} += $linetotal * $form->{"${item}_rate"};
             $taxbase{$item}     += $taxbase;
           }
         }
       } else {
-        foreach $item (@taxaccounts) {
+        foreach my $item (@taxaccounts) {
           $taxaccounts{$item} += $taxamount * $form->{"${item}_rate"} / $taxrate;
           $taxbase{$item} += $taxbase;
         }
@@ -593,7 +623,7 @@ sub delete {
   my $query = qq|SELECT s.spoolfile FROM status s | .
               qq|WHERE s.trans_id = ?|;
   my @values = (conv_i($form->{id}));
-  $sth = $dbh->prepare($query);
+  my $sth = $dbh->prepare($query);
   $sth->execute(@values) || $self->dberror($query);
 
   my $spoolfile;
@@ -669,7 +699,9 @@ sub retrieve {
     undef @ids;
   }
 
-  my $query_add = '';
+  # and remember for the rest of the function
+  my $is_collective_order = scalar @ids;
+
   if (!$form->{id}) {
     my $wday         = (localtime(time))[6];
     my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1;
@@ -724,14 +756,14 @@ sub retrieve {
     @values = $form->{id} ? ($form->{id}) : @ids;
     $sth = prepare_execute_query($form, $dbh, $query, @values);
 
-    $ref = $sth->fetchrow_hashref(NAME_lc);
+    $ref = $sth->fetchrow_hashref("NAME_lc");
     map { $form->{$_} = $ref->{$_} } 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)) {
+    while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
       map { $form->{$_} = '' if ($ref->{$_} ne $form->{$_}) } keys %$ref;
     }
 
@@ -756,7 +788,7 @@ sub retrieve {
       $query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|;
       $sth = prepare_execute_query($form, $dbh, $query, $form->{id});
 
-      $ref = $sth->fetchrow_hashref(NAME_lc);
+      $ref = $sth->fetchrow_hashref("NAME_lc");
       delete($ref->{id});
       map { $form->{$_} = $ref->{$_} } keys %$ref;
       $sth->finish;
@@ -765,7 +797,7 @@ sub retrieve {
       $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});
 
-      while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+      while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
         $form->{printed} .= "$ref->{formname} " if $ref->{printed};
         $form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
         $form->{queued}  .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
@@ -813,7 +845,7 @@ sub retrieve {
     @ids = $form->{id} ? ($form->{id}) : @ids;
     $sth = prepare_execute_query($form, $dbh, $query, @values);
 
-    while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
+    while ($ref = $sth->fetchrow_hashref("NAME_lc")) {
       # Retrieve custom variables.
       my $cvars = CVar->get_custom_variables(dbh        => $dbh,
                                              module     => 'IC',
@@ -872,7 +904,7 @@ sub retrieve {
       delete $ref->{orderitems_id} if (@ids);
 
       # get tax rates and description
-      $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
+      my $accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
       $query =
         qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | .
         qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | .
@@ -880,10 +912,10 @@ sub retrieve {
         qq|               WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) | .
         qq|                 AND startdate <= $transdate ORDER BY startdate DESC LIMIT 1) | .
         qq|ORDER BY c.accno|;
-      $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
+      my $stw = prepare_execute_query($form, $dbh, $query, $accno_id);
       $ref->{taxaccounts} = "";
       my $i = 0;
-      while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
+      while (my $ptr = $stw->fetchrow_hashref("NAME_lc")) {
         if (($ptr->{accno} eq "") && ($ptr->{rate} == 0)) {
           $i++;
           $ptr->{accno} = $i;
@@ -925,6 +957,46 @@ sub retrieve {
   return $rc;
 }
 
+=item retrieve_simple PARAMS
+
+simple OE retrieval by id. does not look up customer, vendor, units or any other stuff. only oe and orderitems.
+
+  my $order = retrieve_simple(id => 2);
+
+  $order => {
+    %_OE_CONTENT,
+    orderitems => [
+      %_ORDERITEM_ROW_1,
+      %_ORDERITEM_ROW_2,
+      ...
+    ]
+  }
+
+=cut
+sub retrieve_simple {
+  $main::lxdebug->enter_sub();
+
+  my $self     = shift;
+  my %params   = @_;
+
+  Common::check_params(\%params, qw(id));
+
+  my $myconfig    = \%main::myconfig;
+  my $form        = $main::form;
+
+  my $dbh         = $params{dbh} || $form->get_standard_dbh($myconfig);
+
+  my $oe_query    = qq|SELECT * FROM oe         WHERE id = ?|;
+  my $oi_query    = qq|SELECT * FROM orderitems WHERE trans_id = ?|;
+
+  my $order            = selectfirst_hashref_query($form, $dbh, $oe_query, conv_i($params{id}));
+  $order->{orderitems} = selectall_hashref_query(  $form, $dbh, $oi_query, conv_i($params{id}));
+
+  $main::lxdebug->leave_sub();
+
+  return $order;
+}
+
 sub order_details {
   $main::lxdebug->enter_sub();
 
@@ -946,6 +1018,11 @@ sub order_details {
   my $position = 0;
   my $subtotal_header = 0;
   my $subposition = 0;
+  my %taxaccounts;
+  my %taxbase;
+  my $tax_rate;
+  my $taxamount;
+
 
   my %oid = ('Pg'     => 'oid',
              'Oracle' => 'rowid');
@@ -995,6 +1072,7 @@ sub order_details {
 
   $form->{discount} = [];
 
+  $form->{TEMPLATE_ARRAYS} = { };
   IC->prepare_parts_for_printing();
 
   my $ic_cvar_configs = CVar->get_configs(module => 'IC');
@@ -1010,7 +1088,7 @@ sub order_details {
 
   my @tax_arrays = qw(taxbase tax taxdescription taxrate taxnumber);
 
-  $form->{TEMPLATE_ARRAYS} = { map { $_ => [] } (@arrays, @tax_arrays) };
+  map { $form->{TEMPLATE_ARRAYS}->{$_} = [] } (@arrays, @tax_arrays);
 
   my $sameitem = "";
   foreach $item (sort { $a->[1] cmp $b->[1] } @partsgroup) {
@@ -1159,7 +1237,7 @@ sub order_details {
         $sth = $dbh->prepare($query);
         $sth->execute(@values) || $form->dberror($query);
 
-        while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
+        while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
           if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
             map({ push(@{ $form->{TEMPLATE_ARRAYS}->{$_} }, "") } grep({ $_ ne "description" } @arrays));
             $sameitem = ($ref->{partsgroup}) ? $ref->{partsgroup} : "--";
@@ -1222,11 +1300,44 @@ sub project_description {
   my ($self, $dbh, $id) = @_;
 
   my $query = qq|SELECT description FROM project WHERE id = ?|;
-  my ($value) = selectrow_query($form, $dbh, $query, $id);
+  my ($value) = selectrow_query($main::form, $dbh, $query, $id);
 
   $main::lxdebug->leave_sub();
 
   return $value;
 }
 
+##########################
+# Get data for the submitted order id
+# from database
+#
+sub get_order_data_by_ordnumber {
+  $main::lxdebug->enter_sub();
+
+  my $self      = shift;
+  my %params    = @_;
+
+  Common::check_params(\%params, qw(ordnumber));
+
+  my $form     = $main::form;
+  my %myconfig = %main::myconfig;
+  my $dbh      = $form->get_standard_dbh();
+
+  my @values = ($params{ordnumber});
+
+  # We query the database for the fields we need using the submitted "ordnumber"
+  my $query = <<SQL;
+    SELECT o.payment_id, o.salesman_id, o.transdate AS orddate, o.taxzone_id, o.quonumber
+    FROM oe o
+    WHERE o.ordnumber = ?;
+SQL
+
+  # Do the actual query and return the results for later processing by our "frontend"
+  my $result = selectfirst_hashref_query($form, $dbh, $query, @values);
+
+  $main::lxdebug->leave_sub();
+
+  return $result;
+}
+
 1;