From bd34eaeac9e09277148172dc634c0cd71bff91a8 Mon Sep 17 00:00:00 2001 From: Udo Spallek Date: Thu, 3 May 2007 07:05:24 +0000 Subject: [PATCH] System->Konteneinstellungen ueberarbeitet: MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit * Alles auf das Templatesystem umgestellt * Kontenliste - Klick auf Beschreibung zeigt Details an (Ajax template list_account_details) - Klick auf Kontonummer öffnet Einstellungen (wie gehabt) - Ueberschriften werden per css (accountlistheader) formatiert - Gegenueberstellung von allen Konten mit allen Details - Uebersetzungn * Konteneinstellungen - Steuerautomatik und USTVA Positionen sind einstellbar/loeschbar (max. 10 Steuerkonten) - Folgekonto: Sortierung nach chart.accno - Validierung: + Wenn Kontentyp Ueberschrift, alle anderen Einstellungen entfernen (js/FormManager.js) + Must have: Kontonummer, Beschreibung, Kontentyp, (Kontenart) + Taxkeys: Gueltig, wenn Startdate und mindestens ein Merkmal aus: Steuerverknuepfung oder USTVA Position - Beim loeschen eines Kontos werden alle abhaengigen taxkeys geloescht - Uebersetzungen --- SL/AM.pm | 321 ++++++++--- SL/CA.pm | 55 +- bin/mozilla/am.pl | 530 ++++++++---------- css/lx-office-erp.css | 17 +- js/FormManager.js | 138 +++++ locale/de/all | 78 +-- templates/webpages/am/edit_accounts_de.html | 214 +++++++ .../webpages/am/edit_accounts_master.html | 213 +++++++ .../webpages/am/list_account_details_de.html | 133 +++++ .../am/list_account_details_master.html | 133 +++++ templates/webpages/am/list_accounts_de.html | 281 ++++++---- .../webpages/am/list_accounts_master.html | 280 +++++---- 12 files changed, 1758 insertions(+), 635 deletions(-) create mode 100644 js/FormManager.js create mode 100644 templates/webpages/am/edit_accounts_de.html create mode 100644 templates/webpages/am/edit_accounts_master.html create mode 100644 templates/webpages/am/list_account_details_de.html create mode 100644 templates/webpages/am/list_account_details_master.html diff --git a/SL/AM.pm b/SL/AM.pm index 323556bd4..af52ae9a9 100644 --- a/SL/AM.pm +++ b/SL/AM.pm @@ -47,20 +47,23 @@ sub get_account { # connect to database my $dbh = $form->dbconnect($myconfig); - my $query = - qq!SELECT c.accno, c.description, c.charttype, c.category,! . - qq! c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from, ! . - qq! c.pos_bwa, ! . - qq! tk.taxkey_id, tk.pos_ustva, tk.tax_id, ! . - qq! tk.tax_id || '--' || tk.taxkey_id AS tax, tk.startdate ! . - qq!FROM chart c ! . - qq!LEFT JOIN taxkeys tk ! . - qq!ON (c.id=tk.chart_id AND tk.id = ! . - qq! (SELECT id FROM taxkeys ! . - qq! WHERE taxkeys.chart_id = c.id AND startdate <= current_date ! . - qq! ORDER BY startdate DESC LIMIT 1)) ! . - qq!WHERE c.id = ?!; - + my $query = qq{ + SELECT c.accno, c.description, c.charttype, c.category, + c.link, c.pos_bilanz, c.pos_eur, c.new_chart_id, c.valid_from, + c.pos_bwa, datevautomatik, + tk.taxkey_id, tk.pos_ustva, tk.tax_id, + tk.tax_id || '--' || tk.taxkey_id AS tax, tk.startdate + FROM chart c + LEFT JOIN taxkeys tk + ON (c.id=tk.chart_id AND tk.id = + (SELECT id FROM taxkeys + WHERE taxkeys.chart_id = c.id AND startdate <= current_date + ORDER BY startdate DESC LIMIT 1)) + WHERE c.id = ? + }; + + + $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query"); my $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})"); @@ -75,6 +78,7 @@ sub get_account { # get default accounts $query = qq|SELECT inventory_accno_id, income_accno_id, expense_accno_id FROM defaults|; + $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query"); $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); @@ -84,9 +88,20 @@ sub get_account { $sth->finish; + + # get taxkeys and description - $query = qq§SELECT id, taxkey,id||'--'||taxkey AS tax, taxdescription - FROM tax ORDER BY taxkey§; + $query = qq{ + SELECT + id, + (SELECT accno FROM chart WHERE id=tax.chart_id) AS chart_accno, + taxkey, + id||'--'||taxkey AS tax, + taxdescription, + rate + FROM tax ORDER BY taxkey + }; + $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query"); $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); @@ -100,7 +115,10 @@ sub get_account { if ($form->{id}) { # get new accounts $query = qq|SELECT id, accno,description - FROM chart WHERE link = ?|; + FROM chart + WHERE link = ? + ORDER BY accno|; + $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query"); $sth = $dbh->prepare($query); $sth->execute($form->{link}) || $form->dberror($query . " ($form->{link})"); @@ -110,10 +128,45 @@ sub get_account { } $sth->finish; + + # get the taxkeys of account + + $query = qq{ + SELECT + tk.id, + tk.chart_id, + c.accno, + tk.tax_id, + t.taxdescription, + t.rate, + tk.taxkey_id, + tk.pos_ustva, + tk.startdate + FROM taxkeys tk + LEFT JOIN tax t ON (t.id = tk.tax_id) + LEFT JOIN chart c ON (c.id = t.chart_id) + + WHERE tk.chart_id = ? + ORDER BY startdate DESC + }; + $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query"); + $sth = $dbh->prepare($query); + + $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})"); + + $form->{ACCOUNT_TAXKEYS} = []; + + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { + push @{ $form->{ACCOUNT_TAXKEYS} }, $ref; + } + + $sth->finish; + } # check if we have any transactions $query = qq|SELECT a.trans_id FROM acc_trans a WHERE a.chart_id = ?|; + $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query"); $sth = $dbh->prepare($query); $sth->execute($form->{id}) || $form->dberror($query . " ($form->{id})"); @@ -126,6 +179,7 @@ sub get_account { if ($form->{new_chart_id}) { $query = qq|SELECT current_date-valid_from FROM chart WHERE id = ?|; + $main::lxdebug->message(LXDebug::QUERY, "\$query=\n $query"); my ($count) = selectrow_query($form, $dbh, $query, $form->{id}); if ($count >=0) { $form->{new_chart_valid} = 1; @@ -177,69 +231,189 @@ sub save_account { my @values; - my ($tax_id, $taxkey) = split(/--/, $form->{tax}); - my $startdate = $form->{startdate} ? $form->{startdate} : "1970-01-01"; - if ($form->{id}) { $query = qq|UPDATE chart SET - accno = ?, description = ?, charttype = ?, - category = ?, link = ?, - taxkey_id = ?, - pos_ustva = ?, pos_bwa = ?, pos_bilanz = ?, - pos_eur = ?, new_chart_id = ?, valid_from = ? + accno = ?, + description = ?, + charttype = ?, + category = ?, + link = ?, + pos_bwa = ?, + pos_bilanz = ?, + pos_eur = ?, + new_chart_id = ?, + valid_from = ?, + datevautomatik = ? WHERE id = ?|; - @values = ($form->{accno}, $form->{description}, $form->{charttype}, - $form->{category}, $form->{link}, - conv_i($taxkey), - conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}), - conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}), - conv_i($form->{new_chart_id}), - conv_date($form->{valid_from}), - $form->{id}); - - } elsif ($form->{id} && !$form->{new_chart_valid}) { - $query = qq|UPDATE chart SET new_chart_id = ?, valid_from = ? - WHERE id = ?|; - @values = (conv_i($form->{new_chart_id}), conv_date($form->{valid_from}), - $form->{id}); - } else { - $query = qq|INSERT INTO chart - (accno, description, charttype, - category, link, taxkey_id, - pos_ustva, pos_bwa, pos_bilanz, pos_eur, - new_chart_id, valid_from) - VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; - @values = ($form->{accno}, $form->{description}, $form->{charttype}, - $form->{category}, $form->{link}, conv_i($taxkey), - conv_i($form->{pos_ustva}), conv_i($form->{pos_bwa}), - conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}), - conv_i($form->{new_chart_id}), - conv_date($form->{valid_from})); + + @values = ( + $form->{accno}, + $form->{description}, + $form->{charttype}, + $form->{category}, + $form->{link}, + conv_i($form->{pos_bwa}), + conv_i($form->{pos_bilanz}), + conv_i($form->{pos_eur}), + conv_i($form->{new_chart_id}), + conv_date($form->{valid_from}), + ($form->{datevautomatik} eq 'T') ? 'true':'false', + $form->{id}, + ); + + } + elsif ($form->{id} && !$form->{new_chart_valid}) { + + $query = qq| + UPDATE chart + SET new_chart_id = ?, + valid_from = ? + WHERE id = ? + |; + + @values = ( + conv_i($form->{new_chart_id}), + conv_date($form->{valid_from}), + $form->{id} + ); + } + else { + + $query = qq| + INSERT INTO chart ( + accno, + description, + charttype, + category, + link, + pos_bwa, + pos_bilanz, + pos_eur, + new_chart_id, + valid_from, + datevautomatik ) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) + |; + + @values = ( + $form->{accno}, + $form->{description}, + $form->{charttype}, + $form->{category}, $form->{link}, + conv_i($form->{pos_bwa}), + conv_i($form->{pos_bilanz}), conv_i($form->{pos_eur}), + conv_i($form->{new_chart_id}), + conv_date($form->{valid_from}), + ($form->{datevautomatik} eq 'T') ? 'true':'false', + ); } + do_query($form, $dbh, $query, @values); - #Save Taxes - if (!$form->{id}) { - $query = - qq|INSERT INTO taxkeys | . - qq|(chart_id, tax_id, taxkey_id, pos_ustva, startdate) | . - qq|VALUES ((SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?)|; - do_query($form, $dbh, $query, - $form->{accno}, conv_i($tax_id), conv_i($taxkey), - conv_i($form->{pos_ustva}), conv_date($startdate)); + #Save Taxkeys + + my @taxkeys = (); + + my $MAX_TRIES = 10; # Maximum count of taxkeys in form + my $tk_count; + + READTAXKEYS: + for $tk_count (0 .. $MAX_TRIES) { + + # Loop control + + # Check if the account already exists, else cancel + last READTAXKEYS if ( $form->{'id'} == 0); + + # check if there is a startdate + if ( $form->{"taxkey_startdate_$tk_count"} eq '' ) { + $tk_count++; + next READTAXKEYS; + } - } else { - $query = qq|DELETE FROM taxkeys WHERE chart_id = ? AND tax_id = ?|; - do_query($form, $dbh, $query, $form->{id}, conv_i($tax_id)); + # check if there is at least one relation to pos_ustva or tax_id + if ( $form->{"taxkey_pos_ustva_$tk_count"} eq '' && $form->{"taxkey_tax_$tk_count"} == 0 ) { + $tk_count++; + next READTAXKEYS; + } - $query = - qq|INSERT INTO taxkeys | . - qq|(chart_id, tax_id, taxkey_id, pos_ustva, startdate) | . - qq|VALUES (?, ?, ?, ?, ?)|; - do_query($form, $dbh, $query, - $form->{id}, conv_i($tax_id), conv_i($taxkey), - conv_i($form->{pos_ustva}), conv_date($startdate)); + # Add valid taxkeys into the array + push @taxkeys , + { + id => ($form->{"taxkey_id_$tk_count"} eq 'NEW') ? conv_i('') : conv_i($form->{"taxkey_id_$tk_count"}), + tax_id => conv_i($form->{"taxkey_tax_$tk_count"}), + startdate => conv_date($form->{"taxkey_startdate_$tk_count"}), + chart_id => conv_i($form->{"id"}), + pos_ustva => conv_i($form->{"taxkey_pos_ustva_$tk_count"}), + delete => ( $form->{"taxkey_del_$tk_count"} eq 'delete' ) ? '1' : '', + }; + + $tk_count++; + } + + TAXKEY: + for my $j (0 .. $#taxkeys){ + if ( defined $taxkeys[$j]{'id'} ){ + # delete Taxkey? + + if ($taxkeys[$j]{'delete'}){ + $query = qq{ + DELETE FROM taxkeys WHERE id = ? + }; + + @values = ($taxkeys[$j]{'id'}); + + do_query($form, $dbh, $query, @values); + + next TAXKEY; + } + + # UPDATE Taxkey + + $query = qq{ + UPDATE taxkeys + SET taxkey_id = (SELECT taxkey FROM tax WHERE tax.id = ?), + chart_id = ?, + tax_id = ?, + pos_ustva = ?, + startdate = ? + WHERE id = ? + }; + @values = ( + $taxkeys[$j]{'tax_id'}, + $taxkeys[$j]{'chart_id'}, + $taxkeys[$j]{'tax_id'}, + $taxkeys[$j]{'pos_ustva'}, + $taxkeys[$j]{'startdate'}, + $taxkeys[$j]{'id'}, + ); + do_query($form, $dbh, $query, @values); + } + else { + # INSERT Taxkey + + $query = qq{ + INSERT INTO taxkeys ( + taxkey_id, + chart_id, + tax_id, + pos_ustva, + startdate + ) + VALUES ((SELECT taxkey FROM tax WHERE tax.id = ?), ?, ?, ?, ?) + }; + @values = ( + $taxkeys[$j]{'tax_id'}, + $taxkeys[$j]{'chart_id'}, + $taxkeys[$j]{'tax_id'}, + $taxkeys[$j]{'pos_ustva'}, + $taxkeys[$j]{'startdate'}, + ); + + do_query($form, $dbh, $query, @values); + } + } # commit @@ -289,6 +463,11 @@ sub delete_account { WHERE id = ?|; do_query($form, $dbh, $query, $form->{id}); + # delete account taxkeys + $query = qq|DELETE FROM taxkeys + WHERE chart_id = ?|; + do_query($form, $dbh, $query, $form->{id}); + # commit and redirect my $rc = $dbh->commit; $dbh->disconnect; diff --git a/SL/CA.pm b/SL/CA.pm index 56036e7de..0c2a24f37 100644 --- a/SL/CA.pm +++ b/SL/CA.pm @@ -41,7 +41,7 @@ use SL::DBUtils; sub all_accounts { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form, $chart_id) = @_; my %amount; @@ -61,26 +61,45 @@ sub all_accounts { } $sth->finish; - $query = - qq!SELECT c.accno, c.id, c.description, c.charttype, c.category, ! . - qq! c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from, ! . - qq! c.datevautomatik, comma(tk.startdate) AS startdate, ! . - qq! comma(tk.taxkey_id) AS taxkey, ! . - qq! comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') ! . - qq! AS taxdescription, ! . - qq! comma(tx.taxnumber) AS taxaccount, comma(tk.pos_ustva) ! . - qq! AS tk_ustva, ! . - qq! ( SELECT accno FROM chart c2 WHERE c2.id = c.id ) AS new_account ! . - qq!FROM chart c ! . - qq!LEFT JOIN taxkeys tk ON (c.id = tk.chart_id) ! . - qq!LEFT JOIN tax tx ON (tk.tax_id = tx.id) ! . - qq!GROUP BY c.accno, c.id, c.description, c.charttype, ! . - qq! c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, ! . - qq! c.valid_from, c.datevautomatik ! . - qq!ORDER BY c.accno!; + my $where = "AND c.id = $chart_id" if ($chart_id ne ''); + + $query = qq{ + SELECT + c.accno, + c.id, + c.description, + c.charttype, + c.category, + c.link, + c.pos_bwa, + c.pos_bilanz, + c.pos_eur, + c.valid_from, + c.datevautomatik, + comma(tk.startdate) AS startdate, + comma(tk.taxkey_id) AS taxkey, + comma(tx.taxdescription || to_char (tx.rate, '99V99' ) || '%') AS taxdescription, + comma(tx.taxnumber) AS taxaccount, + comma(tk.pos_ustva) AS tk_ustva, + ( SELECT accno + FROM chart c2 + WHERE c2.id = c.id + ) AS new_account + FROM chart c + LEFT JOIN taxkeys tk ON (c.id = tk.chart_id) + LEFT JOIN tax tx ON (tk.tax_id = tx.id) + WHERE 1=1 + $where + GROUP BY c.accno, c.id, c.description, c.charttype, c.gifi_accno, + c.category, c.link, c.pos_bwa, c.pos_bilanz, c.pos_eur, c.valid_from, + c.datevautomatik + ORDER BY c.accno + }; + my $sth = prepare_execute_query($form, $dbh, $query); $form->{CA} = []; + while (my $ca = $sth->fetchrow_hashref(NAME_lc)) { $ca->{amount} = $amount{ $ca->{accno} }; if ($ca->{amount} < 0) { diff --git a/bin/mozilla/am.pl b/bin/mozilla/am.pl index 3bab6aa00..094e32a7d 100644 --- a/bin/mozilla/am.pl +++ b/bin/mozilla/am.pl @@ -35,6 +35,9 @@ use SL::AM; use SL::CA; use SL::Form; use SL::User; +use SL::USTVA; +use CGI::Ajax; +use CGI; use Data::Dumper; @@ -86,134 +89,108 @@ sub edit_account { sub account_header { $lxdebug->enter_sub(); + if ( $form->{action} eq 'edit_account') { + $form->{account_exists} = '1'; + } + $form->{title} = $locale->text("$form->{title} Account"); - $checked{ $form->{charttype} } = "checked"; - $checked{"$form->{category}_"} = "checked"; - $checked{CT_tax} = ($form->{CT_tax}) ? "" : "checked"; + $form->{"$form->{charttype}_checked"} = "checked"; + $form->{"$form->{category}_checked"} = "checked"; - $form->{description} =~ s/\"/"/g; + $form->{select_tax} = ""; + + my @tax_report_pos = USTVA->report_variables({ + myconfig => \%myconfig, + form => $form, + type => '', + attribute => 'position', + calc => '', + }); if (@{ $form->{TAXKEY} }) { foreach my $item (@{ $form->{TAXKEY} }) { - $item->{rate} = $item->{rate} * 100 . '%'; + } - if ($item->{tax} == $form->{tax}) { - $form->{selecttaxkey} .= - ""; + $form->{selectnewaccount} = qq||; } foreach $item (@{ $form->{NEWACCOUNT} }) { if ($item->{id} == $form->{new_chart_id}) { $form->{selectnewaccount} .= - ""; + qq||; } elsif (!$form->{new_chart_valid}) { $form->{selectnewaccount} .= - ""; + qq||; } } } - $newaccount = qq| - - - - - - - - - -
| . $locale->text('Folgekonto') . qq|| . $locale->text('Gültig ab') . qq|
- - |; - - $form->{selectustva} = "\n|; %eur = (1 => "Umsatzerlöse", 2 => "sonstige Erlöse", 3 => "Privatanteile", @@ -247,21 +224,14 @@ sub account_header { 31 => "Betriebliche Steuern"); foreach $item (sort({ $a <=> $b } keys(%eur))) { if ($item == $form->{pos_eur}) { - $form->{selecteur} .= "\n|; } else { - $form->{selecteur} .= "\n|; } } - $eur = qq| - - | . $locale->text('EUER') . qq| - - - |; - - $form->{selectbwa} = "\n|; %bwapos = (1 => 'Umsatzerlöse', 2 => 'Best.Verdg.FE/UE', @@ -287,193 +257,87 @@ sub account_header { 35 => 'Steuern Eink.u.Ertr.'); foreach $item (sort({ $a <=> $b } keys %bwapos)) { if ($item == $form->{pos_bwa}) { - $form->{selectbwa} .= "\n|; + foreach $item ((1, 2, 3, 4)) { + if ($item == $form->{pos_bilanz}) { + $select_bilanz .= qq|