From 4000272e5ea3a605f3625934f07033472832de49 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Jan=20B=C3=BCren?= Date: Sun, 16 May 2010 22:51:14 +0200 Subject: [PATCH] Buchungsjournal: Bearbeiter und Buchungsdatum MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit - Buchungsjournal: Auswahl nach Bearbeiter einschränken - Sortierung: Auswahl ob Buchungsjournal nach Buchungsdatum oder Rechnungsdatum sortiert wird (Buchungsdatum ist Default) - Datumsauswahl von-bis berücksichtigt ebenfalls Auswahl Buchungsdatum/Rechnungsdatum --- SL/GL.pm | 52 ++++++++++++++++++++-------- bin/mozilla/gl.pl | 87 ++++++++++++++++++++++++++++++++++++++++------- 2 files changed, 112 insertions(+), 27 deletions(-) diff --git a/SL/GL.pm b/SL/GL.pm index 01b71dbda..feac77c50 100644 --- a/SL/GL.pm +++ b/SL/GL.pm @@ -231,19 +231,22 @@ sub all_transactions { push(@apvalues, '%' . $form->{source} . '%'); } + # default Datumseinschränkung falls nicht oder falsch übergeben (sollte nie passieren) + $form->{datesort} = 'transdate' unless $form->{datesort} =~ /^(transdate|gldate)$/; + if ($form->{datefrom}) { - $glwhere .= " AND ac.transdate >= ?"; - $arwhere .= " AND ac.transdate >= ?"; - $apwhere .= " AND ac.transdate >= ?"; + $glwhere .= " AND ac.$form->{datesort} >= ?"; + $arwhere .= " AND ac.$form->{datesort} >= ?"; + $apwhere .= " AND ac.$form->{datesort} >= ?"; push(@glvalues, $form->{datefrom}); push(@arvalues, $form->{datefrom}); push(@apvalues, $form->{datefrom}); } if ($form->{dateto}) { - $glwhere .= " AND ac.transdate <= ?"; - $arwhere .= " AND ac.transdate <= ?"; - $apwhere .= " AND ac.transdate <= ?"; + $glwhere .= " AND ac.$form->{datesort} <= ?"; + $arwhere .= " AND ac.$form->{datesort} <= ?"; + $apwhere .= " AND ac.$form->{datesort} <= ?"; push(@glvalues, $form->{dateto}); push(@arvalues, $form->{dateto}); push(@apvalues, $form->{dateto}); @@ -257,6 +260,19 @@ sub all_transactions { push(@arvalues, '%' . $form->{description} . '%'); push(@apvalues, '%' . $form->{description} . '%'); } + + if ($form->{employee} =~ /--/) { + ($form->{employee_id},$form->{employee_name}) = split(/--/,$form->{employee}); + $query .= " AND o.employee_id = ?"; + push @values, conv_i($form->{employee_id}); + #if ($form->{employee_id}) { + $glwhere .= " AND g.employee_id = ? "; + $arwhere .= " AND a.employee_id = ? "; + $apwhere .= " AND a.employee_id = ? "; + push(@glvalues, conv_i($form->{employee_id})); + push(@arvalues, conv_i($form->{employee_id})); + push(@apvalues, conv_i($form->{employee_id})); + } if ($form->{notes}) { $glwhere .= " AND g.notes ILIKE ?"; @@ -312,7 +328,7 @@ sub all_transactions { qq|SELECT SUM(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id) - WHERE (c.accno = ?) AND (ac.transdate < ?)|; + WHERE (c.accno = ?) AND (ac.$form->{datesort} < ?)|; ($form->{balance}) = selectrow_query($form, $dbh, $query, $form->{accno}, conv_date($form->{datefrom})); } } @@ -322,6 +338,7 @@ sub all_transactions { my %sort_columns = ( 'id' => [ qw(id) ], 'transdate' => [ qw(transdate id) ], + 'gldate' => [ qw(gldate id) ], 'reference' => [ qw(lower_reference id) ], 'description' => [ qw(lower_description id) ], 'accno' => [ qw(accno transdate id) ], @@ -331,9 +348,10 @@ sub all_transactions { 'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', }, 'description' => { 'gl' => 'g.description', 'arap' => 'ct.name', }, ); - + + # sortdir = sort direction (ascending or descending) my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; - my $sortkey = $sort_columns{$form->{sort}} ? $form->{sort} : 'transdate'; + my $sortkey = $sort_columns{$form->{sort}} ? $form->{sort} : $form->{datesort}; # default used to be transdate my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} }; my %columns_for_sorting = ( 'gl' => '', 'arap' => '', ); @@ -347,8 +365,9 @@ sub all_transactions { $query = qq|SELECT ac.acc_trans_id, g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, c.link, - g.description, ac.transdate, ac.source, ac.trans_id, - ac.amount, c.accno, g.notes, t.chart_id + g.description, ac.transdate, ac.gldate, ac.source, ac.trans_id, + ac.amount, c.accno, g.notes, t.chart_id, + CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee $project_columns $columns_for_sorting{gl} FROM gl g, acc_trans ac $project_join, chart c @@ -360,8 +379,9 @@ sub all_transactions { UNION SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, - ct.name, ac.transdate, ac.source, ac.trans_id, - ac.amount, c.accno, a.notes, t.chart_id + ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id, + ac.amount, c.accno, a.notes, t.chart_id, + CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee $project_columns $columns_for_sorting{arap} FROM ar a, acc_trans ac $project_join, customer ct, chart c @@ -374,8 +394,9 @@ sub all_transactions { UNION SELECT ac.acc_trans_id, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, - ct.name, ac.transdate, ac.source, ac.trans_id, - ac.amount, c.accno, a.notes, t.chart_id + ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id, + ac.amount, c.accno, a.notes, t.chart_id, + CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee $project_columns $columns_for_sorting{arap} FROM ap a, acc_trans ac $project_join, vendor ct, chart c @@ -386,6 +407,7 @@ sub all_transactions { AND (a.id = ac.trans_id) ORDER BY $sortorder, acc_trans_id $sortdir|; +# ORDER BY gldate DESC, id DESC, acc_trans_id DESC my @values = (@glvalues, @arvalues, @apvalues); diff --git a/bin/mozilla/gl.pl b/bin/mozilla/gl.pl index 518e43052..b8c288118 100644 --- a/bin/mozilla/gl.pl +++ b/bin/mozilla/gl.pl @@ -242,7 +242,8 @@ sub search { | if $form->{selectdepartment}; $form->get_lists("projects" => { "key" => "ALL_PROJECTS", - "all" => 1 }); + "all" => 1}, + "employees" => "ALL_EMPLOYEES" ); my %project_labels = (); my @project_values = (""); @@ -256,6 +257,20 @@ sub search { '-values' => \@project_values, '-labels' => \%project_labels)); + my %employee_labels = (); + my @employee_values = (""); + foreach my $item (@{ $form->{"ALL_EMPLOYEES"} }) { + # value in Form "1234--Name" übergeben + my $id = "$item->{'id'}--$item->{'name'}"; + push(@employee_values, "$id"); + $employee_labels{$id} = $item->{"name"}; + } + + my $employeenumber = + NTI($cgi->popup_menu('-name' => "employee", + '-values' => \@employee_values, + '-labels' => \%employee_labels)); + # use JavaScript Calendar or not $form->{jsscript} = 1; my $jsscript = ""; @@ -296,7 +311,7 @@ sub search {
- + @@ -329,6 +344,19 @@ sub search { + + + + + + + + + + $button1 $button2 @@ -357,7 +385,9 @@ sub search { - + + + @@ -380,6 +410,8 @@ sub search { + +
| . $locale->text('From') . qq|
| . $locale->text('Employee') . qq|$employeenumber
| . $locale->text('Date Sorting') . qq| +  | . $locale->text('Booking Date') . qq| +  | . $locale->text('Invoice Date') . qq| +
| . $locale->text('From') . qq|| . $locale->text('To (time)') . qq| | . $locale->text('ID') . qq| | . $locale->text('Date') . qq|| . $locale->text('Invoice Date') . qq|| . $locale->text('Booking Date') . qq| | . $locale->text('Reference') . qq| | . $locale->text('Subtotal') . qq| | . $locale->text('Project Number') . qq|| . $locale->text('Employee') . qq|
@@ -436,7 +468,26 @@ sub generate_report { my %myconfig = %main::myconfig; my $locale = $main::locale; - report_generator_set_default_sort('transdate', 1); + # generate_report wird beim ersten Aufruf per Weiter-Knopf und POST mit der hidden Variablen sort mit Wert "datesort" (früher "transdate" als Defaultsortiervariable) übertragen + + # + # # form->{sort} setzen + # + + # anhand von neuer Variable datesort wird jetzt $form->{sort} auf transdate oder gldate gesetzt + # damit ist die Hidden Variable "sort" wahrscheinlich sogar überflüssig + + # ändert man die Sortierreihenfolge per Klick auf eine der Überschriften wird die Variable "sort" per GET übergeben, z.B. id,transdate, gldate, ... + # gl.pl?action=generate_report&employee=18383--Jan%20B%c3%bcren&datesort=transdate&category=X&l_transdate=Y&l_gldate=Y&l_id=Y&l_reference=Y&l_description=Y&l_source=Y&l_debit=Y&l_credit=Y&sort=gldate&sortdir=0 + + if ( $form->{sort} eq 'datesort' ) { # sollte bei einem Post (Aufruf aus Suchmaske) immer wahr sein + # je nachdem ob in Suchmaske "transdate" oder "gldate" ausgesucht wurde erstes Suchergebnis entsprechend sortieren + $form->{sort} = $form->{datesort}; + }; + + # was passiert hier? + report_generator_set_default_sort("$form->{datesort}", 1); +# report_generator_set_default_sort('transdate', 1); GL->all_transactions(\%myconfig, \%$form); @@ -457,14 +508,18 @@ sub generate_report { my $ml = ($form->{ml} =~ /(A|E|Q)/) ? -1 : 1; my @columns = qw( - transdate id reference description - notes source debit debit_accno + gldate transdate id reference description + notes source debit debit_accno credit credit_accno debit_tax debit_tax_accno credit_tax credit_tax_accno projectnumbers balance ); - my @hidden_variables = qw(accno source reference department description notes project_id datefrom dateto category l_subtotal); + # add employee here, so that variable is still known and passed in url when choosing a different sort order in resulting table + my @hidden_variables = qw(accno source reference department description notes project_id datefrom dateto employee datesort category l_subtotal); push @hidden_variables, map { "l_${_}" } @columns; + foreach ( @hidden_variables ) { + print URL "$_\n"; + }; my (@options, @date_options); push @options, $locale->text('Account') . " : $form->{accno} $form->{account_description}" if ($form->{accno}); @@ -472,7 +527,9 @@ sub generate_report { push @options, $locale->text('Reference') . " : $form->{reference}" if ($form->{reference}); push @options, $locale->text('Description') . " : $form->{description}" if ($form->{description}); push @options, $locale->text('Notes') . " : $form->{notes}" if ($form->{notes}); - + push @options, $locale->text('Employee') . " : $form->{employee_name}" if ($form->{employee_name}); + my $datesorttext = $form->{datesort} eq 'transdate' ? $locale->text('Invoice Date') : $locale->text('Booking Date'); + push @date_options, "$datesorttext" if ($form->{datesort} and ($form->{datefrom} or $form->{dateto})); push @date_options, $locale->text('From'), $locale->date(\%myconfig, $form->{datefrom}, 1) if ($form->{datefrom}); push @date_options, $locale->text('Bis'), $locale->date(\%myconfig, $form->{dateto}, 1) if ($form->{dateto}); push @options, join(' ', @date_options) if (scalar @date_options); @@ -484,18 +541,23 @@ sub generate_report { my $callback = build_std_url('action=generate_report', grep { $form->{$_} } @hidden_variables); + print URL $callback; + close URL; $form->{l_credit_accno} = 'Y'; $form->{l_debit_accno} = 'Y'; $form->{l_credit_tax} = 'Y'; $form->{l_debit_tax} = 'Y'; +# $form->{l_gldate} = 'Y'; # Spalte mit gldate immer anzeigen $form->{l_credit_tax_accno} = 'Y'; + $form->{l_datesort} = 'Y'; $form->{l_debit_tax_accno} = 'Y'; $form->{l_balance} = $form->{accno} ? 'Y' : ''; my %column_defs = ( 'id' => { 'text' => $locale->text('ID'), }, - 'transdate' => { 'text' => $locale->text('Date'), }, + 'transdate' => { 'text' => $locale->text('Invoice Date'), }, + 'gldate' => { 'text' => $locale->text('Booking Date'), }, 'reference' => { 'text' => $locale->text('Reference'), }, 'source' => { 'text' => $locale->text('Source'), }, 'description' => { 'text' => $locale->text('Description'), }, @@ -510,9 +572,10 @@ sub generate_report { 'credit_tax_accno' => { 'text' => $locale->text('Credit Tax Account'), }, 'balance' => { 'text' => $locale->text('Balance'), }, 'projectnumbers' => { 'text' => $locale->text('Project Numbers'), }, + 'employee' => { 'text' => $locale->text('Employee'), }, ); - foreach my $name (qw(id transdate reference description debit_accno credit_accno debit_tax_accno credit_tax_accno)) { + foreach my $name (qw(id transdate gldate reference description debit_accno credit_accno debit_tax_accno credit_tax_accno)) { my $sortname = $name =~ m/accno/ ? 'accno' : $name; my $sortdir = $sortname eq $form->{sort} ? 1 - $form->{sortdir} : $form->{sortdir}; $column_defs{$name}->{link} = $callback . "&sort=$sortname&sortdir=$sortdir"; @@ -523,7 +586,7 @@ sub generate_report { my %column_alignment; map { $column_alignment{$_} = 'right' } qw(balance id debit credit debit_tax credit_tax balance); - map { $column_alignment{$_} = 'center' } qw(reference debit_accno credit_accno debit_tax_accno credit_tax_accno); + map { $column_alignment{$_} = 'center' } qw(transdate gldate reference debit_accno credit_accno debit_tax_accno credit_tax_accno); map { $column_alignment{$_} = 'left' } qw(description source notes); map { $column_defs{$_}->{align} = $column_alignment{$_} } keys %column_alignment; @@ -597,7 +660,7 @@ sub generate_report { map { $row->{$_}->{data} = $ref->{$_} } qw(id reference description notes); - map { $row->{$_}->{data} = \@{ $rows{$_} }; } qw(transdate debit credit debit_accno credit_accno debit_tax_accno credit_tax_accno source); + map { $row->{$_}->{data} = \@{ $rows{$_} }; } qw(transdate gldate debit credit debit_accno credit_accno debit_tax_accno credit_tax_accno source); foreach my $col (qw(debit_accno credit_accno debit_tax_accno credit_tax_accno)) { $row->{$col}->{link} = [ map { "${callback}&accno=" . E($_) } @{ $rows{$col} } ]; -- 2.20.1