From a4b22a8f008d6431597fa80de26bcb0fa10e8d7b Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Tue, 20 Feb 2007 09:10:36 +0000 Subject: [PATCH] =?utf8?q?Einkaufs-=20und=20Verkaufsrechnungen:=20Filterm?= =?utf8?q?=C3=B6glichkeiten=20nach=20Projektnummer=20sowie=20Anzeigeoption?= =?utf8?q?=20der=20Projektnummer=20hinzugef=C3=BCgt.?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/AP.pm | 12 +++++++++++- SL/AR.pm | 20 ++++++++++++++++---- bin/mozilla/ap.pl | 34 +++++++++++++++++++++++++++++++--- bin/mozilla/ar.pl | 35 ++++++++++++++++++++++++++++++++--- locale/de/ap | 1 + locale/de/ar | 1 + 6 files changed, 92 insertions(+), 11 deletions(-) diff --git a/SL/AP.pm b/SL/AP.pm index 4c705410a..f20d61a41 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -404,10 +404,13 @@ sub ap_transactions { my $query = qq|SELECT a.id, a.invnumber, a.transdate, a.duedate, a.amount, a.paid, | . qq| a.ordnumber, v.name, a.invoice, a.netamount, a.datepaid, a.notes, | . + qq| a.globalproject_id, | . + qq| pr.projectnumber AS globalprojectnumber, | . qq| e.name AS employee | . qq|FROM ap a | . qq|JOIN vendor v ON (a.vendor_id = v.id) | . - qq|LEFT JOIN employee e ON (a.employee_id = e.id)|; + qq|LEFT JOIN employee e ON (a.employee_id = e.id) | . + qq|LEFT JOIN project pr ON (a.globalproject_id = pr.id) |; my $where; my @values; @@ -436,6 +439,13 @@ sub ap_transactions { $where .= " AND lower(a.notes) LIKE ?"; push(@values, $form->like($form->{notes})); } + if ($form->{project_id}) { + $where .= + qq|AND ((a.globalproject_id = ?) OR EXISTS | . + qq| (SELECT * FROM invoice i | . + qq| WHERE i.project_id = ? AND i.trans_id = a.id))|; + push(@values, $form->{project_id}, $form->{project_id}); + } if ($form->{transdatefrom}) { $where .= " AND a.transdate >= ?"; diff --git a/SL/AR.pm b/SL/AR.pm index 65eb3f03c..a38259a2a 100644 --- a/SL/AR.pm +++ b/SL/AR.pm @@ -552,14 +552,18 @@ sub ar_transactions { # connect to database my $dbh = $form->dbconnect($myconfig); + my @values; + my $query = qq|SELECT a.id, a.invnumber, a.ordnumber, a.transdate, a.duedate, a.netamount, a.amount, a.paid, c.name, a.invoice, a.datepaid, a.terms, a.notes, a.shipvia, - a.shippingpoint, a.storno, + a.shippingpoint, a.storno, a.globalproject_id, + pr.projectnumber AS globalprojectnumber, e.name AS employee FROM ar a JOIN customer c ON (a.customer_id = c.id) - LEFT JOIN employee e ON (a.employee_id = e.id)|; + LEFT JOIN employee e ON (a.employee_id = e.id) + LEFT JOIN project pr ON a.globalproject_id = pr.id|; my $where = "1 = 1"; if ($form->{customer_id}) { @@ -586,6 +590,13 @@ sub ar_transactions { my $notes = $form->like(lc $form->{notes}); $where .= " AND lower(a.notes) LIKE '$notes'"; } + if ($form->{"project_id"}) { + $where .= + qq|AND ((a.globalproject_id = ?) OR EXISTS | . + qq| (SELECT * FROM invoice i | . + qq| WHERE i.project_id = ? AND i.trans_id = a.id))|; + push(@values, $form->{"project_id"}, $form->{"project_id"}); + } $where .= " AND a.transdate >= '$form->{transdatefrom}'" if $form->{transdatefrom}; @@ -603,11 +614,12 @@ sub ar_transactions { my $sortorder = join ', ', $form->sort_columns(@a); $sortorder = $form->{sort} if $form->{sort}; - $query .= "WHERE $where + $query .= " WHERE $where ORDER by $sortorder"; my $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth->execute(@values) || + $form->dberror($query . " (" . join(", ", @values) . ")"); while (my $ar = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{AR} }, $ar; diff --git a/bin/mozilla/ap.pl b/bin/mozilla/ap.pl index 2cfff88d6..34b285de3 100644 --- a/bin/mozilla/ap.pl +++ b/bin/mozilla/ap.pl @@ -36,6 +36,7 @@ use SL::IR; use SL::PE; require "$form->{path}/arap.pl"; +require "bin/mozilla/common.pl"; 1; @@ -1101,6 +1102,19 @@ sub search { |; } + $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| @@ -1133,6 +1147,10 @@ sub search { | . $locale->text('Notes') . qq| + + | . $locale->text("Project Number") . qq| + $projectnumber + | . $locale->text('From') . qq| $button1 @@ -1197,6 +1215,8 @@ sub search { | . $locale->text('Subtotal') . qq| + + | . $locale->text('Project Number') . qq| @@ -1299,10 +1319,14 @@ sub ap_transactions { $option .= "\n
" if ($option); $option .= $locale->text('Closed'); } + if ($form->{globalproject_id}) { + $callback .= "&globalproject_id=" . E($form->{globalproject_id}); + $href .= "&globalproject_id=" . E($form->{globalproject_id}); + } - @columns = $form->sort_columns( - qw(transdate id invnumber ordnumber name netamount tax amount paid datepaid due duedate notes employee) - ); + @columns = + qw(transdate id invnumber ordnumber name netamount tax amount paid datepaid + due duedate notes employee globalprojectnumber); foreach $item (@columns) { if ($form->{"l_$item"} eq "Y") { @@ -1362,6 +1386,8 @@ sub ap_transactions { $column_header{employee} = "" . $locale->text('Employee') . ""; + $column_header{globalprojectnumber} = + qq|| . $locale->text('Project Number') . qq||; $form->{title} = $locale->text('AP Transactions'); @@ -1452,6 +1478,8 @@ sub ap_transactions { $ap->{notes} =~ s/\r\n/
/g; $column_data{notes} = "$ap->{notes} "; $column_data{employee} = "$ap->{employee} "; + $column_data{globalprojectnumber} = + "" . H($ap->{globalprojectnumber}) . ""; $i++; $i %= 2; diff --git a/bin/mozilla/ar.pl b/bin/mozilla/ar.pl index 45c0b59ab..291ad068d 100644 --- a/bin/mozilla/ar.pl +++ b/bin/mozilla/ar.pl @@ -37,6 +37,7 @@ use SL::PE; use Data::Dumper; require "$form->{path}/arap.pl"; +require "bin/mozilla/common.pl"; 1; @@ -1129,6 +1130,19 @@ sub search { |; } + $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->{fokus} = "search.customer"; $form->header; @@ -1160,6 +1174,10 @@ sub search { | . $locale->text('Notes') . qq| + + | . $locale->text("Project Number") . qq| + $projectnumber + | . $locale->text('From') . qq| $button1 @@ -1226,6 +1244,8 @@ sub search { | . $locale->text('Subtotal') . qq| + + | . $locale->text('Project Number') . qq| @@ -1331,10 +1351,15 @@ sub ar_transactions { $option .= "\n
" if ($option); $option .= $locale->text('Closed'); } + if ($form->{globalproject_id}) { + $callback .= "&globalproject_id=" . E($form->{globalproject_id}); + $href .= "&globalproject_id=" . E($form->{globalproject_id}); + } - @columns = $form->sort_columns( - qw(transdate id type invnumber ordnumber name netamount tax amount paid datepaid due duedate notes employee shippingpoint shipvia) - ); + @columns = + qw(transdate id type invnumber ordnumber name netamount tax amount paid + datepaid due duedate notes employee shippingpoint shipvia + globalprojectnumber); $form->{"l_type"} = "Y"; @@ -1407,6 +1432,8 @@ sub ar_transactions { "" . $locale->text('Ship via') . ""; + $column_header{globalprojectnumber} = + qq|| . $locale->text('Project Number') . qq||; $form->{title} = $locale->text('AR Transactions'); @@ -1505,6 +1532,8 @@ sub ar_transactions { $column_data{shippingpoint} = "$ar->{shippingpoint} "; $column_data{shipvia} = "$ar->{shipvia} "; $column_data{employee} = "$ar->{employee} "; + $column_data{globalprojectnumber} = + "" . H($ar->{globalprojectnumber}) . ""; $i++; $i %= 2; diff --git a/locale/de/ap b/locale/de/ap index 194ece44c..d6d3d6326 100644 --- a/locale/de/ap +++ b/locale/de/ap @@ -75,6 +75,7 @@ $self->{texts} = { 'Post' => 'Buchen', 'Post Payment' => 'Zahlung buchen', 'Project' => 'Projekt', + 'Project Number' => 'Projektnummer', 'Project not on file!' => 'Dieses Projekt ist nicht in der Datenbank!', 'Remaining' => 'Rest', 'Select from one of the names below' => 'Wählen Sie einen der untenstehenden Namen', diff --git a/locale/de/ar b/locale/de/ar index 11f3bc202..aab9c8af6 100644 --- a/locale/de/ar +++ b/locale/de/ar @@ -78,6 +78,7 @@ $self->{texts} = { 'Post' => 'Buchen', 'Post Payment' => 'Zahlung buchen', 'Project' => 'Projekt', + 'Project Number' => 'Projektnummer', 'Project not on file!' => 'Dieses Projekt ist nicht in der Datenbank!', 'Remaining' => 'Rest', 'Sales Invoice' => 'Rechnung', -- 2.20.1