From c7f9bb36eb122b2d79f5aa8c4d080279705ddc7e Mon Sep 17 00:00:00 2001 From: Philip Reetz Date: Thu, 13 Oct 2005 14:32:05 +0000 Subject: [PATCH] Buchungsjournal um Anzeige von Splitbuchungen erweitert, Splitbuchungen beim Dialogbuchen zu 80% fertig. Neue Maske fuer Splitbuchungen --- SL/GL.pm | 450 ++++------- bin/mozilla/gl.pl | 1914 ++++++++++++++++----------------------------- 2 files changed, 842 insertions(+), 1522 deletions(-) diff --git a/SL/GL.pm b/SL/GL.pm index 9fdb486f9..ef4ec7bda 100644 --- a/SL/GL.pm +++ b/SL/GL.pm @@ -38,13 +38,9 @@ package GL; -use Data::Dumper; - sub delete_transaction { - $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; - + # connect to database my $dbh = $form->dbconnect_noauto($myconfig); @@ -57,49 +53,28 @@ sub delete_transaction { # commit and redirect my $rc = $dbh->commit; $dbh->disconnect; - - $main::lxdebug->leave_sub(); - - return $rc; + + $rc; + } -sub post_transaction { - $main::lxdebug->enter_sub(); +sub post_transaction { my ($self, $myconfig, $form) = @_; - + my ($debit, $credit) = (0, 0); my $project_id; my $i; - # check if debit and credit balances - - $debit = abs(int($form->round_amount($form->{debit}, 3) * 1000)); - $credit = abs(int($form->round_amount($form->{credit}, 3) * 1000)); - $tax = abs(int($form->round_amount($form->{tax}, 3) * 1000)); - - if ( (($debit >= $credit) && (abs($debit - ($credit + $tax)) > 4)) - || (($debit < $credit) && (abs(($debit + $tax) - $credit) > 4))) { - return -2; - } - - if (($debit + $credit + $tax) == 0) { - return -3; - } - - $debit = $form->round_amount($form->{debit}, 2); - $credit = $form->round_amount($form->{credit}, 2); - $tax = $form->round_amount($form->{tax}, 2); - debug($debit, $credit, $tax, "Betraege"); - + if ($form->{storno}) { - $debit = $debit * -1; - $credit = $credit * -1; - $tax = $tax * -1; - $form->{reference} = "Storno-" . $form->{reference}; - $form->{description} = "Storno-" . $form->{description}; - } + $debit = $debit * -1; + $credit = $credit * -1; + $tax = $tax * -1; + $form->{reference} = "Storno-".$form->{reference}; + $form->{description} = "Storno-".$form->{description}; + } # connect to database, turn off AutoCommit my $dbh = $form->dbconnect_noauto($myconfig); @@ -119,15 +94,15 @@ sub post_transaction { if (!$form->{taxincluded}) { $form->{taxincluded} = 0; } - + my ($query, $sth); + if ($form->{id}) { - # delete individual transactions - $query = qq|DELETE FROM acc_trans + $query = qq|DELETE FROM acc_trans WHERE trans_id = $form->{id}|; $dbh->do($query) || $form->dberror($query); - + } else { my $uid = time; $uid .= $form->{login}; @@ -136,7 +111,7 @@ sub post_transaction { VALUES ('$uid', (SELECT e.id FROM employee e WHERE e.login = '$form->{login}'))|; $dbh->do($query) || $form->dberror($query); - + $query = qq|SELECT g.id FROM gl g WHERE g.reference = '$uid'|; $sth = $dbh->prepare($query); @@ -146,10 +121,11 @@ sub post_transaction { $sth->finish; } + my ($null, $department_id) = split /--/, $form->{department}; $department_id *= 1; - - $query = qq|UPDATE gl SET + + $query = qq|UPDATE gl SET reference = '$form->{reference}', description = '$form->{description}', notes = '$form->{notes}', @@ -157,136 +133,89 @@ sub post_transaction { department_id = $department_id, taxincluded = '$form->{taxincluded}' WHERE id = $form->{id}|; - + $dbh->do($query) || $form->dberror($query); ($taxkey, $rate) = split(/--/, $form->{taxkey}); - # insert acc_trans transactions - foreach $i ((credit, debit)) { + # insert acc_trans transactions + for $i (1 .. $form->{rowcount}) { # extract accno - ($accno) = split(/--/, $form->{"${i}chartselected"}); + my ($accno) = split(/--/, $form->{"accno_$i"}); + my ($taxkey, $rate) = split(/--/, $form->{"taxchart_$i"}); my $amount = 0; - debug("$accno $i Kontonummer"); - if ($i eq "credit") { + my $debit = $form->{"debit_$i"}; + my $credit = $form->{"credit_$i"}; + my $tax = $form->{"tax_$i"}; + + if ($credit) { $amount = $credit; + $posted = 0; } - if ($i eq "debit") { + if ($debit) { $amount = $debit * -1; + $tax = $tax * -1; + $posted = 0; } - if ($form->{"${i}_splited"}) { - - # if there is an amount, add the record - for $j (2 .. $form->{"${i}rowcount"}) { - ($accno) = split(/--/, $form->{"${i}chartselected_$j"}); - - $amount = $form->{"${i}_$j"}; - - ($taxkey, $taxrate) = split(/--/, $form->{"taxchartselected_$j"}); - - if ($i eq "debit") { - $amount *= -1; - } - if ($amount != 0) { - $project_id = - ($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL'; - $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, - source, project_id, taxkey) - VALUES - ($form->{id}, (SELECT c.id - FROM chart c - WHERE c.accno = '$accno'), - $amount, '$form->{transdate}', '$form->{reference}', - $project_id, $taxkey)|; - - $dbh->do($query) || $form->dberror($query); - } - - $tax = $form->{"tax_$j"}; - print(STDERR $tax, " Steuer bei Durchlauf $j\n\n"); - if ($tax != 0) { + # if there is an amount, add the record + if ($amount != 0) { + $project_id = ($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL'; + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, + source, memo, project_id, taxkey) + VALUES + ($form->{id}, (SELECT c.id + FROM chart c + WHERE c.accno = '$accno'), + $amount, '$form->{transdate}', |. + $dbh->quote($form->{"source_$i"}) .qq|, |. + $dbh->quote($form->{"memo_$i"}).qq|, + $project_id, $taxkey)|; + + $dbh->do($query) || $form->dberror($query); + } + + if ($tax !=0) { # add taxentry - if ($i eq "debit") { - $tax = $tax * (-1); - } $amount = $tax; - - $project_id = - ($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL'; - $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, - source, project_id, taxkey) - VALUES - ($form->{id}, (SELECT t.chart_id - FROM tax t - WHERE t.taxkey = $taxkey), - $amount, '$form->{transdate}', '$form->{reference}', - $project_id, $taxkey)|; - - $dbh->do($query) || $form->dberror($query); - } - } - } else { - - # if there is an amount, add the record - ($taxkey, $taxrate) = split(/--/, $form->{"taxchartselected"}); - $taxkey *= 1; - debug("$amount auf $accno buchen"); - if ($amount != 0) { - $project_id = - ($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL'; - $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, - source, project_id, taxkey) - VALUES - ($form->{id}, (SELECT c.id - FROM chart c - WHERE c.accno = '$accno'), - $amount, '$form->{transdate}', '$form->{reference}', - $project_id, $taxkey)|; - - $dbh->do($query) || $form->dberror($query); - } - } - } - if ($tax != 0 && !($form->{credit_splited} || $form->{debit_splited})) { - - # add taxentry - if ($form->{debittaxkey}) { - $tax = $tax * (-1); - } - $amount = $tax; - debug("$amount Steuern buchen"); - - $project_id = - ($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL'; - $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, - source, project_id, taxkey) + + + $project_id = ($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL'; + $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, + source, memo, project_id, taxkey) VALUES ($form->{id}, (SELECT t.chart_id FROM tax t WHERE t.taxkey = $taxkey), - $amount, '$form->{transdate}', '$form->{reference}', + $amount, '$form->{transdate}', |. + $dbh->quote($form->{"source_$i"}) .qq|, |. + $dbh->quote($form->{"memo_$i"}).qq|, $project_id, $taxkey)|; - - $dbh->do($query) || $form->dberror($query); + + $dbh->do($query) || $form->dberror($query); + } } + my %audittrail = ( tablename => 'gl', + reference => $form->{reference}, + formname => 'transaction', + action => 'posted', + id => $form->{id} ); + + # $form->audittrail($dbh, "", \%audittrail); + # commit and redirect my $rc = $dbh->commit; $dbh->disconnect; - $main::lxdebug->leave_sub(); + $rc; - return $rc; } -sub all_transactions { - $main::lxdebug->enter_sub(); + +sub all_transactions { my ($self, $myconfig, $form) = @_; # connect to database @@ -294,7 +223,7 @@ sub all_transactions { my ($query, $sth, $source, $null); my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1"); - + if ($form->{reference}) { $source = $form->like(lc $form->{reference}); $glwhere .= " AND lower(g.reference) LIKE '$source'"; @@ -353,17 +282,16 @@ sub all_transactions { } if ($form->{accno}) { - # get category for account $query = qq|SELECT c.category FROM chart c WHERE c.accno = '$form->{accno}'|; - $sth = $dbh->prepare($query); - - $sth->execute || $form->dberror($query); - ($form->{ml}) = $sth->fetchrow_array; - $sth->finish; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + ($form->{ml}) = $sth->fetchrow_array; + $sth->finish; + if ($form->{datefrom}) { $query = qq|SELECT SUM(ac.amount) FROM acc_trans ac, chart c @@ -378,19 +306,18 @@ sub all_transactions { $sth->finish; } } - + if ($form->{gifi_accno}) { - # get category for account $query = qq|SELECT c.category FROM chart c WHERE c.gifi_accno = '$form->{gifi_accno}'|; - $sth = $dbh->prepare($query); - - $sth->execute || $form->dberror($query); - ($form->{ml}) = $sth->fetchrow_array; - $sth->finish; + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); + ($form->{ml}) = $sth->fetchrow_array; + $sth->finish; + if ($form->{datefrom}) { $query = qq|SELECT SUM(ac.amount) FROM acc_trans ac, chart c @@ -406,10 +333,10 @@ sub all_transactions { } } - my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE: q|'0'|; + my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE : q|'0'|; - my $query = - qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, t.taxkey AS sorttax, + + my $query = qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, t.taxkey AS sorttax, g.description, ac.transdate, ac.source, ac.trans_id, ac.amount, c.accno, c.gifi_accno, g.notes, t.chart_id, ac.oid FROM gl g, acc_trans ac, chart c LEFT JOIN tax t ON @@ -440,10 +367,9 @@ sub all_transactions { ORDER BY transdate, trans_id, taxkey DESC, sorttax DESC, oid|; my $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { print(STDERR $ref->{id}, " Transaction\n"); - # gl if ($ref->{type} eq "gl") { $ref->{module} = "gl"; @@ -466,80 +392,80 @@ sub all_transactions { $ref->{module} = "ar"; } } - $balance = $ref->{amount}; - $i = 0; - $j = 0; - $k = 0; - $l = 0; + $balance=$ref->{amount}; + $i = 0; + $j = 0; + $k = 0; + $l = 0; if ($ref->{amount} < 0) { - if ($ref->{chart_id} > 0) { - $ref->{debit_tax}{$i} = $ref->{amount} * -1; + if ($ref->{chart_id} >0) { + $ref->{debit_tax}{$i} = $ref->{amount} * -1; $ref->{debit_tax_accno}{$i} = $ref->{accno}; - } else { - $ref->{debit}{$k} = $ref->{amount} * -1; - $ref->{debit_accno}{$k} = $ref->{accno}; + } + else { + $ref->{debit}{$k} = $ref->{amount} * -1; + $ref->{debit_accno}{$k} = $ref->{accno}; $ref->{debit_taxkey}{$k} = $ref->{taxkey}; - } + } } else { - if ($ref->{chart_id} > 0) { - $ref->{credit_tax}{$j} = $ref->{amount}; + if ($ref->{chart_id} >0) { + $ref->{credit_tax}{$j} = $ref->{amount}; $ref->{credit_tax_accno}{$j} = $ref->{accno}; - } else { - $ref->{credit}{$l} = $ref->{amount}; - $ref->{credit_accno}{$l} = $ref->{accno}; + } + else { + $ref->{credit}{$l} = $ref->{amount}; + $ref->{credit_accno}{$l} = $ref->{accno}; $ref->{credit_taxkey}{$l} = $ref->{taxkey}; - } + } } - while (abs($balance) >= 0.015) { - my $ref2 = $sth->fetchrow_hashref(NAME_lc) - || $form->error("Unbalanced ledger!"); + while (abs($balance)>=0.015) { + my $ref2 = $sth->fetchrow_hashref(NAME_lc) || $form->error("Unbalanced ledger!"); - $balance = - (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000; - print(STDERR $balance, " BAlance\n"); + $balance = (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000; + print(STDERR $balance," BAlance\n"); if ($ref2->{amount} < 0) { - if ($ref2->{chart_id} > 0) { + if ($ref2->{chart_id} >0) { if ($ref->{debit_tax_accno}{$i} ne "") { $i++; } - $ref->{debit_tax}{$i} = $ref2->{amount} * -1; + $ref->{debit_tax}{$i} = $ref2->{amount} * -1; $ref->{debit_tax_accno}{$i} = $ref2->{accno}; - } else { + } + else { if ($ref->{debit_accno}{$k} ne "") { $k++; } - $ref->{debit}{$k} = $ref2->{amount} * -1; - $ref->{debit_accno}{$k} = $ref2->{accno}; + $ref->{debit}{$k} = $ref2->{amount} * -1; + $ref->{debit_accno}{$k} = $ref2->{accno}; $ref->{debit_taxkey}{$k} = $ref2->{taxkey}; - } + } } else { - if ($ref2->{chart_id} > 0) { + if ($ref2->{chart_id} >0) { if ($ref->{credit_tax_accno}{$j} ne "") { $j++; } - $ref->{credit_tax}{$j} = $ref2->{amount}; + $ref->{credit_tax}{$j} = $ref2->{amount}; $ref->{credit_tax_accno}{$j} = $ref2->{accno}; - } else { + } + else { if ($ref->{credit_accno}{$l} ne "") { $l++; } - $ref->{credit}{$l} = $ref2->{amount}; - $ref->{credit_accno}{$l} = $ref2->{accno}; + $ref->{credit}{$l} = $ref2->{amount}; + $ref->{credit_accno}{$l} = $ref2->{accno}; $ref->{credit_taxkey}{$l} = $ref2->{taxkey}; - } + } } } - - # print(STDERR Dumper($ref)); +# print(STDERR Dumper($ref)); push @{ $form->{GL} }, $ref; - $balance = 0; + $balance=0; } $sth->finish; if ($form->{accno}) { - $query = - qq|SELECT c.description FROM chart c WHERE c.accno = '$form->{accno}'|; + $query = qq|SELECT c.description FROM chart c WHERE c.accno = '$form->{accno}'|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); @@ -547,31 +473,27 @@ sub all_transactions { $sth->finish; } if ($form->{gifi_accno}) { - $query = - qq|SELECT g.description FROM gifi g WHERE g.accno = '$form->{gifi_accno}'|; + $query = qq|SELECT g.description FROM gifi g WHERE g.accno = '$form->{gifi_accno}'|; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); ($form->{gifi_account_description}) = $sth->fetchrow_array; $sth->finish; } - + $dbh->disconnect; - $main::lxdebug->leave_sub(); } -sub transaction { - $main::lxdebug->enter_sub(); +sub transaction { my ($self, $myconfig, $form) = @_; - + my ($query, $sth, $ref); - + # connect to database my $dbh = $form->dbconnect($myconfig); - $form->{creditrowcount} = 1; - $form->{debitrowcount} = 1; + if ($form->{id}) { $query = "SELECT closedto, revtrans FROM defaults"; @@ -584,15 +506,15 @@ sub transaction { $query = "SELECT g.reference, g.description, g.notes, g.transdate, d.description AS department, e.name as employee, g.taxincluded, g.gldate FROM gl g - LEFT JOIN department d ON (d.id = g.department_id) - LEFT JOIN employee e ON (e.id = g.employee_id) + LEFT JOIN department d ON (d.id = g.department_id) + LEFT JOIN employee e ON (e.id = g.employee_id) WHERE g.id = $form->{id}"; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); $ref = $sth->fetchrow_hashref(NAME_lc); map { $form->{$_} = $ref->{$_} } keys %$ref; $sth->finish; - + # retrieve individual rows $query = "SELECT c.accno, a.amount, project_id, (SELECT p.projectnumber FROM project p @@ -600,73 +522,23 @@ sub transaction { FROM acc_trans a, chart c WHERE a.chart_id = c.id AND a.trans_id = $form->{id} - ORDER BY accno"; + ORDER BY a.oid"; $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - - $debitcount = 2; - $creditcount = 2; - $taxcount = 2; + while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - - if ($ref->{accno} eq $ref->{taxaccno}) { - $form->{"tax_$taxcount"} = $ref->{amount}; - $form->{"tax"} += $ref->{amount}; - $form->{"taxchartselected_$taxcount"} = $ref->{taxkey}; - $taxcount++; - } else { - - if ($ref->{amount} < 0) { - $form->{"debit_$debitcount"} = $ref->{amount} * -1; - $form->{"debit"} += $ref->{amount} * -1; - $form->{"debitchartselected_$debitcount"} = $ref->{accno}; - $debitcount++; - } - if ($ref->{amount} > 0) { - - $form->{"credit_$creditcount"} = $ref->{amount}; - $form->{"credit"} += $ref->{amount}; - $form->{"creditchartselected_$creditcount"} = $ref->{accno}; - $creditcount++; - } - } - - $taxkey = $ref->{taxkey} * 1; - } - if ($creditcount > 3) { - $form->{credit_splited} = 1; - $form->{credit} = $form->{credit} + $form->{tax}; - $form->{creditrowcount} = $creditcount - 1; - } else { - $form->{credit} = $form->{credit_2}; - $form->{amount} = $form->{amount_2}; - $form->{creditaccno} = $form->{creditchartselected_2}; - } - if ($debitcount > 3) { - $form->{debit_splited} = 1; - $form->{debit} = $form->{debit} + $form->{tax}; - $form->{debitrowcount} = $debitcount - 1; - } else { - $form->{debit} = $form->{debit_2}; - $form->{debitaccno} = $form->{debitchartselected_2}; - } - - if ( (($form->{credit} > $form->{debit}) && (!$form->{taxincluded})) - || (($form->{credit} > $form->{debit}) && ($form->{taxincluded}))) { - $form->{amount} = $form->{debit}; - } else { - $form->{amount} = $form->{credit}; + push @{ $form->{GL} }, $ref; } - # get tax description + # get tax description $query = qq| SELECT * FROM tax t|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); $form->{TAX} = (); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{TAX} }, $ref; } - + $sth->finish; } else { $query = "SELECT current_date AS transdate, closedto, revtrans @@ -674,13 +546,12 @@ sub transaction { $sth = $dbh->prepare($query); $sth->execute || $form->dberror($query); - ($form->{transdate}, $form->{closedto}, $form->{revtrans}) = - $sth->fetchrow_array; - - # get tax description + ($form->{transdate}, $form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array; + + # get tax description $query = qq| SELECT * FROM tax t order by t.taxkey|; - $sth = $dbh->prepare($query); - $sth->execute || $form->dberror($query); + $sth = $dbh->prepare($query); + $sth->execute || $form->dberror($query); $form->{TAX} = (); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { push @{ $form->{TAX} }, $ref; @@ -701,24 +572,15 @@ sub transaction { push @{ $form->{chart} }, $ref; } $sth->finish; + + $sth->finish; - + $dbh->disconnect; - $main::lxdebug->leave_sub(); } -sub debug { - local *OUT; - if (open(OUT, ">>/tmp/linet.log")) { - - # chomp(@_); - print(OUT join("\n", @_), "\n"); - close(OUT); - } else { - print(STDERR "noe: $!\n"); - } -} 1; + diff --git a/bin/mozilla/gl.pl b/bin/mozilla/gl.pl index e0a5811d2..4417615e1 100644 --- a/bin/mozilla/gl.pl +++ b/bin/mozilla/gl.pl @@ -31,17 +31,16 @@ # #====================================================================== + use SL::GL; use SL::PE; -use Data::Dumper; - require "$form->{path}/arap.pl"; 1; - # end of main + # this is for our long dates # $locale->text('January') # $locale->text('February') @@ -70,205 +69,144 @@ require "$form->{path}/arap.pl"; # $locale->text('Nov') # $locale->text('Dec') + sub add { - $lxdebug->enter_sub(); $form->{title} = "Add"; - - $form->{callback} = - "$form->{script}?action=add&path=$form->{path}&login=$form->{login}&password=$form->{password}" - unless $form->{callback}; + + $form->{callback} = "$form->{script}?action=add&path=$form->{path}&login=$form->{login}&password=$form->{password}" unless $form->{callback}; # we use this only to set a default date GL->transaction(\%myconfig, \%$form); - map { - $chart .= - "" - } @{ $form->{chart} }; - map { - $tax .= - qq|" } @{ $form->{chart} }; + map { $tax .= qq|" - } @{ $form->{chart} }; + map { if ($form->{debitaccno} eq $_->{accno}) {$form->{debitchart} .= "