Bei Berichtsfunktion nach Projekten suchen und sich Projekte ausgeben lassen (bisher...

diff --git a/SL/OE.pm b/SL/OE.pm
index 64ba141d5009c5f6b7cb9807bf9577a1c4b4d26f..d471a815c8777cede7b5035f5c2c56647f71755a 100644 (file)
--- a/SL/OE.pm
+++ b/SL/OE.pm
@@ -52,6 +52,15 @@ sub transactions {
   my ($null, $department_id) = split /--/, $form->{department};
 
   my $department = " AND o.department_id = $department_id" if $department_id;
   my ($null, $department_id) = split /--/, $form->{department};
 
   my $department = " AND o.department_id = $department_id" if $department_id;

+  my @values;
+
+  if ($form->{"project_id"}) {
+    $department .=
+      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"});
+  }

 
   my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
 
 
   my $rate = ($form->{vc} eq 'customer') ? 'buy' : 'sell';
 


@@ -65,6 +74,7 @@ sub transactions {
 
   my $query = qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate,
                  o.amount, ct.name, o.netamount, o.$form->{vc}_id,
 
   my $query = qq|SELECT o.id, o.ordnumber, o.transdate, o.reqdate,
                  o.amount, ct.name, o.netamount, o.$form->{vc}_id,

+                 o.globalproject_id, pr.projectnumber AS globalprojectnumber,

                 ex.$rate AS exchangerate,
                 o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia,
                 e.name AS employee
                 ex.$rate AS exchangerate,
                 o.closed, o.delivered, o.quonumber, o.shippingpoint, o.shipvia,
                 e.name AS employee


@@ -73,6 +83,7 @@ sub transactions {
                 LEFT JOIN employee e ON (o.employee_id = e.id)
                 LEFT JOIN exchangerate ex ON (ex.curr = o.curr
                                               AND ex.transdate = o.transdate)
                 LEFT JOIN employee e ON (o.employee_id = e.id)
                 LEFT JOIN exchangerate ex ON (ex.curr = o.curr
                                               AND ex.transdate = o.transdate)

+                 LEFT JOIN project pr ON o.globalproject_id = pr.id

                 WHERE o.quotation = '$quotation'
                 $department|;
 
                 WHERE o.quotation = '$quotation'
                 $department|;
 


@@ -107,7 +118,10 @@ sub transactions {
   $query .= " ORDER by $sortorder";
 
   my $sth = $dbh->prepare($query);
   $query .= " ORDER by $sortorder";
 
   my $sth = $dbh->prepare($query);

-  $sth->execute || $form->dberror($query);
+  $sth->execute(@values) ||
+    $form->dberror($query . " (" . join(", ", @values) . ")");
+
+  dump_query(0, "laqje", $query, @values);

 
   my %id = ();
   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
 
   my %id = ();
   while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {

diff --git a/bin/mozilla/oe.pl b/bin/mozilla/oe.pl
index 0d6c3ce5863fcdc3b897ab33f50cef096f94e850..3baa8799f09f70fb1a73e4cde5bf0328ce6c168f 100644 (file)
--- a/bin/mozilla/oe.pl
+++ b/bin/mozilla/oe.pl
@@ -1375,6 +1375,19 @@ sub search {
                               <td><input name=transdateto id=transdateto size=11 title="$myconfig{dateformat}"></td>|;
   }
 
                               <td><input name=transdateto id=transdateto size=11 title="$myconfig{dateformat}"></td>|;
   }
 

+  $form->get_lists("projects" => { "key" => "ALL_PROJECTS",
+                                   "all" => 1 });
+
+  my %labels = ();
+  my @values = ("");
+  foreach my $item (@{ $form->{"ALL_PROJECTS"} }) {
+    push(@values, $item->{"id"});
+    $labels{$item->{"id"}} = $item->{"projectnumber"};
+  }
+  my $projectnumber =
+    NTI($cgi->popup_menu('-name' => 'project_id', '-values' => \@values,
+                         '-labels' => \%labels));
+

   $form->header;
 
   print qq|
   $form->header;
 
   print qq|


@@ -1399,6 +1412,10 @@ sub search {
           <th align=right>$ordlabel</th>
           <td colspan=3><input name="$ordnumber" size=20></td>
         </tr>
           <th align=right>$ordlabel</th>
           <td colspan=3><input name="$ordnumber" size=20></td>
         </tr>

+        <tr>
+          <th align="right">| . $locale->text("Project Number") . qq|</th>
+          <td colspan="3">$projectnumber</td>
+        </tr>

         <tr>
           <th align=right>| . $locale->text('From') . qq|</th>
           $button1
         <tr>
           <th align=right>| . $locale->text('From') . qq|</th>
           $button1


@@ -1426,6 +1443,7 @@ sub search {
                <td><input name="l_employee" class=checkbox type=checkbox value=Y checked> $employee</td>
                <td><input name="l_shipvia" class=checkbox type=checkbox value=Y> |
     . $locale->text('Ship via') . qq|</td>
                <td><input name="l_employee" class=checkbox type=checkbox value=Y checked> $employee</td>
                <td><input name="l_shipvia" class=checkbox type=checkbox value=Y> |
     . $locale->text('Ship via') . qq|</td>

+               <td><input name="l_employee" class=checkbox type=checkbox value=Y checked> $employee</td>

              </tr>
              <tr>
                <td><input name="l_netamount" class=checkbox type=checkbox value=Y> |
              </tr>
              <tr>
                <td><input name="l_netamount" class=checkbox type=checkbox value=Y> |


@@ -1434,6 +1452,8 @@ sub search {
     . $locale->text('Tax') . qq|</td>
                <td><input name="l_amount" class=checkbox type=checkbox value=Y checked> |
     . $locale->text('Total') . qq|</td>
     . $locale->text('Tax') . qq|</td>
                <td><input name="l_amount" class=checkbox type=checkbox value=Y checked> |
     . $locale->text('Total') . qq|</td>

+          <td><input name="l_globalprojectnumber" class=checkbox type=checkbox value=Y> |
+          . $locale->text('Project Number') . qq|</td>

              </tr>
              <tr>
                <td><input name="l_subtotal" class=checkbox type=checkbox value=Y> |
              </tr>
              <tr>
                <td><input name="l_subtotal" class=checkbox type=checkbox value=Y> |


@@ -1499,8 +1519,8 @@ sub orders {
   @columns =
     $form->sort_columns("transdate", "reqdate",   "id",      "$ordnumber",
                         "name",      "netamount", "tax",     "amount",
   @columns =
     $form->sort_columns("transdate", "reqdate",   "id",      "$ordnumber",
                         "name",      "netamount", "tax",     "amount",

-                        "curr",      "employee",  "shipvia", "open",
-                        "closed",    "delivered");
+                        "curr",      "employee",  "shipvia", "globalprojectnumber",
+                        "open",      "closed",    "delivered");

 
   $form->{l_open} = $form->{l_closed} = "Y"
     if ($form->{open} && $form->{closed});
 
   $form->{l_open} = $form->{l_closed} = "Y"
     if ($form->{open} && $form->{closed});


@@ -1584,6 +1604,8 @@ sub orders {
       qq|<th><a class=listheading href=$href&sort=shipvia>|
     . $locale->text('Ship via')
     . qq|</a></th>|;
       qq|<th><a class=listheading href=$href&sort=shipvia>|
     . $locale->text('Ship via')
     . qq|</a></th>|;

+  $column_header{globalprojectnumber} =
+    qq|<th class="listheading">| . $locale->text('Project Number') . qq|</th>|;

   $column_header{open} =
     qq|<th class=listheading>| . $locale->text('O') . qq|</th>|;
   $column_header{closed} =
   $column_header{open} =
     qq|<th class=listheading>| . $locale->text('O') . qq|</th>|;
   $column_header{closed} =


@@ -1700,6 +1722,7 @@ sub orders {
 
     $column_data{employee} = "<td>$oe->{employee}&nbsp;</td>";
     $column_data{shipvia}  = "<td>$oe->{shipvia}&nbsp;</td>";
 
     $column_data{employee} = "<td>$oe->{employee}&nbsp;</td>";
     $column_data{shipvia}  = "<td>$oe->{shipvia}&nbsp;</td>";

+    $column_data{globalprojectnumber}  = "<td>" . H($oe->{globalprojectnumber}) . "</td>";

 
     if ($oe->{closed}) {
       $column_data{closed} = "<td align=center>X</td>";
 
     if ($oe->{closed}) {
       $column_data{closed} = "<td align=center>X</td>";