Revision 2ae62d30
Von Stephan Köhler vor etwa 19 Jahren hinzugefügt
| SL/GL.pm | ||
|---|---|---|
| 38 | 38 |
|
| 39 | 39 |
package GL; |
| 40 | 40 |
|
| 41 |
use Data::Dumper; |
|
| 42 |
|
|
| 43 | 41 |
sub delete_transaction {
|
| 44 |
$main::lxdebug->enter_sub(); |
|
| 45 |
|
|
| 46 | 42 |
my ($self, $myconfig, $form) = @_; |
| 43 |
$main::lxdebug->enter_sub(); |
|
| 47 | 44 |
|
| 48 | 45 |
# connect to database |
| 49 | 46 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| ... | ... | |
| 57 | 54 |
# commit and redirect |
| 58 | 55 |
my $rc = $dbh->commit; |
| 59 | 56 |
$dbh->disconnect; |
| 60 |
|
|
| 61 | 57 |
$main::lxdebug->leave_sub(); |
| 62 | 58 |
|
| 63 |
return $rc; |
|
| 59 |
$rc; |
|
| 60 |
|
|
| 64 | 61 |
} |
| 65 | 62 |
|
| 66 | 63 |
sub post_transaction {
|
| 67 |
$main::lxdebug->enter_sub(); |
|
| 68 |
|
|
| 69 | 64 |
my ($self, $myconfig, $form) = @_; |
| 65 |
$main::lxdebug->enter_sub(); |
|
| 70 | 66 |
|
| 71 | 67 |
my ($debit, $credit) = (0, 0); |
| 72 | 68 |
my $project_id; |
| ... | ... | |
| 75 | 71 |
|
| 76 | 72 |
# check if debit and credit balances |
| 77 | 73 |
|
| 78 |
$debit = abs(int($form->round_amount($form->{debit}, 3) * 1000));
|
|
| 79 |
$credit = abs(int($form->round_amount($form->{credit}, 3) * 1000));
|
|
| 80 |
$tax = abs(int($form->round_amount($form->{tax}, 3) * 1000));
|
|
| 81 |
|
|
| 82 |
if ( (($debit >= $credit) && (abs($debit - ($credit + $tax)) > 4)) |
|
| 83 |
|| (($debit < $credit) && (abs(($debit + $tax) - $credit) > 4))) {
|
|
| 84 |
return -2; |
|
| 85 |
} |
|
| 86 |
|
|
| 87 |
if (($debit + $credit + $tax) == 0) {
|
|
| 88 |
return -3; |
|
| 89 |
} |
|
| 90 |
|
|
| 91 |
$debit = $form->round_amount($form->{debit}, 2);
|
|
| 92 |
$credit = $form->round_amount($form->{credit}, 2);
|
|
| 93 |
$tax = $form->round_amount($form->{tax}, 2);
|
|
| 94 |
debug($debit, $credit, $tax, "Betraege"); |
|
| 95 |
|
|
| 96 | 74 |
if ($form->{storno}) {
|
| 97 | 75 |
$debit = $debit * -1; |
| 98 | 76 |
$credit = $credit * -1; |
| ... | ... | |
| 121 | 99 |
} |
| 122 | 100 |
|
| 123 | 101 |
my ($query, $sth); |
| 102 |
|
|
| 124 | 103 |
if ($form->{id}) {
|
| 125 | 104 |
|
| 126 | 105 |
# delete individual transactions |
| 127 |
$query = qq|DELETE FROM acc_trans |
|
| 106 |
$query = qq|DELETE FROM acc_trans
|
|
| 128 | 107 |
WHERE trans_id = $form->{id}|;
|
| 129 | 108 |
$dbh->do($query) || $form->dberror($query); |
| 130 | 109 |
|
| ... | ... | |
| 146 | 125 |
$sth->finish; |
| 147 | 126 |
|
| 148 | 127 |
} |
| 128 |
|
|
| 149 | 129 |
my ($null, $department_id) = split /--/, $form->{department};
|
| 150 | 130 |
$department_id *= 1; |
| 151 | 131 |
|
| 152 |
$query = qq|UPDATE gl SET |
|
| 132 |
$query = qq|UPDATE gl SET
|
|
| 153 | 133 |
reference = '$form->{reference}',
|
| 154 | 134 |
description = '$form->{description}',
|
| 155 | 135 |
notes = '$form->{notes}',
|
| ... | ... | |
| 162 | 142 |
($taxkey, $rate) = split(/--/, $form->{taxkey});
|
| 163 | 143 |
|
| 164 | 144 |
# insert acc_trans transactions |
| 165 |
foreach $i ((credit, debit)) {
|
|
| 145 |
for $i (1 .. $form->{rowcount}) {
|
|
| 166 | 146 |
|
| 167 | 147 |
# extract accno |
| 168 |
($accno) = split(/--/, $form->{"${i}chartselected"});
|
|
| 148 |
my ($accno) = split(/--/, $form->{"accno_$i"});
|
|
| 149 |
my ($taxkey, $rate) = split(/--/, $form->{"taxchart_$i"});
|
|
| 169 | 150 |
my $amount = 0; |
| 170 |
debug("$accno $i Kontonummer");
|
|
| 171 |
if ($i eq "credit") {
|
|
| 151 |
my $debit = $form->{"debit_$i"};
|
|
| 152 |
my $credit = $form->{"credit_$i"};
|
|
| 153 |
my $tax = $form->{"tax_$i"};
|
|
| 154 |
|
|
| 155 |
if ($credit) {
|
|
| 172 | 156 |
$amount = $credit; |
| 157 |
$posted = 0; |
|
| 173 | 158 |
} |
| 174 |
if ($i eq "debit") {
|
|
| 159 |
if ($debit) {
|
|
| 175 | 160 |
$amount = $debit * -1; |
| 161 |
$tax = $tax * -1; |
|
| 162 |
$posted = 0; |
|
| 176 | 163 |
} |
| 177 | 164 |
|
| 178 |
if ($form->{"${i}_splited"}) {
|
|
| 179 |
|
|
| 180 |
# if there is an amount, add the record |
|
| 181 |
for $j (2 .. $form->{"${i}rowcount"}) {
|
|
| 182 |
($accno) = split(/--/, $form->{"${i}chartselected_$j"});
|
|
| 183 |
|
|
| 184 |
$amount = $form->{"${i}_$j"};
|
|
| 185 |
|
|
| 186 |
($taxkey, $taxrate) = split(/--/, $form->{"taxchartselected_$j"});
|
|
| 187 |
|
|
| 188 |
if ($i eq "debit") {
|
|
| 189 |
$amount *= -1; |
|
| 190 |
} |
|
| 191 |
if ($amount != 0) {
|
|
| 192 |
$project_id = |
|
| 193 |
($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL';
|
|
| 194 |
$query = |
|
| 195 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 196 |
source, project_id, taxkey) |
|
| 197 |
VALUES |
|
| 198 |
($form->{id}, (SELECT c.id
|
|
| 199 |
FROM chart c |
|
| 200 |
WHERE c.accno = '$accno'), |
|
| 201 |
$amount, '$form->{transdate}', '$form->{reference}',
|
|
| 202 |
$project_id, $taxkey)|; |
|
| 203 |
|
|
| 204 |
$dbh->do($query) || $form->dberror($query); |
|
| 205 |
} |
|
| 206 |
|
|
| 207 |
$tax = $form->{"tax_$j"};
|
|
| 208 |
print(STDERR $tax, " Steuer bei Durchlauf $j\n\n"); |
|
| 209 |
if ($tax != 0) {
|
|
| 210 |
|
|
| 211 |
# add taxentry |
|
| 212 |
if ($i eq "debit") {
|
|
| 213 |
$tax = $tax * (-1); |
|
| 214 |
} |
|
| 215 |
$amount = $tax; |
|
| 216 |
|
|
| 217 |
$project_id = |
|
| 218 |
($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL';
|
|
| 219 |
$query = |
|
| 220 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 221 |
source, project_id, taxkey) |
|
| 222 |
VALUES |
|
| 223 |
($form->{id}, (SELECT t.chart_id
|
|
| 224 |
FROM tax t |
|
| 225 |
WHERE t.taxkey = $taxkey), |
|
| 226 |
$amount, '$form->{transdate}', '$form->{reference}',
|
|
| 227 |
$project_id, $taxkey)|; |
|
| 228 |
|
|
| 229 |
$dbh->do($query) || $form->dberror($query); |
|
| 230 |
} |
|
| 231 |
} |
|
| 232 |
} else {
|
|
| 233 |
|
|
| 234 |
# if there is an amount, add the record |
|
| 235 |
($taxkey, $taxrate) = split(/--/, $form->{"taxchartselected"});
|
|
| 236 |
$taxkey *= 1; |
|
| 237 |
debug("$amount auf $accno buchen");
|
|
| 238 |
if ($amount != 0) {
|
|
| 239 |
$project_id = |
|
| 240 |
($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL';
|
|
| 241 |
$query = |
|
| 242 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 243 |
source, project_id, taxkey) |
|
| 244 |
VALUES |
|
| 245 |
($form->{id}, (SELECT c.id
|
|
| 246 |
FROM chart c |
|
| 247 |
WHERE c.accno = '$accno'), |
|
| 248 |
$amount, '$form->{transdate}', '$form->{reference}',
|
|
| 249 |
$project_id, $taxkey)|; |
|
| 250 |
|
|
| 251 |
$dbh->do($query) || $form->dberror($query); |
|
| 252 |
} |
|
| 165 |
# if there is an amount, add the record |
|
| 166 |
if ($amount != 0) {
|
|
| 167 |
$project_id = |
|
| 168 |
($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL';
|
|
| 169 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 170 |
source, memo, project_id, taxkey) |
|
| 171 |
VALUES |
|
| 172 |
($form->{id}, (SELECT c.id
|
|
| 173 |
FROM chart c |
|
| 174 |
WHERE c.accno = '$accno'), |
|
| 175 |
$amount, '$form->{transdate}', |
|
|
| 176 |
. $dbh->quote($form->{"source_$i"}) . qq|, |
|
|
| 177 |
. $dbh->quote($form->{"memo_$i"}) . qq|,
|
|
| 178 |
$project_id, $taxkey)|; |
|
| 179 |
|
|
| 180 |
$dbh->do($query) || $form->dberror($query); |
|
| 253 | 181 |
} |
| 254 |
} |
|
| 255 |
if ($tax != 0 && !($form->{credit_splited} || $form->{debit_splited})) {
|
|
| 256 | 182 |
|
| 257 |
# add taxentry |
|
| 258 |
if ($form->{debittaxkey}) {
|
|
| 259 |
$tax = $tax * (-1); |
|
| 260 |
} |
|
| 261 |
$amount = $tax; |
|
| 262 |
debug("$amount Steuern buchen");
|
|
| 183 |
if ($tax != 0) {
|
|
| 184 |
|
|
| 185 |
# add taxentry |
|
| 186 |
$amount = $tax; |
|
| 263 | 187 |
|
| 264 |
$project_id = |
|
| 265 |
($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL';
|
|
| 266 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 267 |
source, project_id, taxkey) |
|
| 188 |
$project_id =
|
|
| 189 |
($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL';
|
|
| 190 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate,
|
|
| 191 |
source, memo, project_id, taxkey)
|
|
| 268 | 192 |
VALUES |
| 269 | 193 |
($form->{id}, (SELECT t.chart_id
|
| 270 | 194 |
FROM tax t |
| 271 | 195 |
WHERE t.taxkey = $taxkey), |
| 272 |
$amount, '$form->{transdate}', '$form->{reference}',
|
|
| 196 |
$amount, '$form->{transdate}', |
|
|
| 197 |
. $dbh->quote($form->{"source_$i"}) . qq|, |
|
|
| 198 |
. $dbh->quote($form->{"memo_$i"}) . qq|,
|
|
| 273 | 199 |
$project_id, $taxkey)|; |
| 274 | 200 |
|
| 275 |
$dbh->do($query) || $form->dberror($query); |
|
| 201 |
$dbh->do($query) || $form->dberror($query); |
|
| 202 |
} |
|
| 276 | 203 |
} |
| 277 | 204 |
|
| 205 |
my %audittrail = (tablename => 'gl', |
|
| 206 |
reference => $form->{reference},
|
|
| 207 |
formname => 'transaction', |
|
| 208 |
action => 'posted', |
|
| 209 |
id => $form->{id});
|
|
| 210 |
|
|
| 211 |
# $form->audittrail($dbh, "", \%audittrail); |
|
| 212 |
|
|
| 278 | 213 |
# commit and redirect |
| 279 | 214 |
my $rc = $dbh->commit; |
| 280 | 215 |
$dbh->disconnect; |
| 281 |
|
|
| 282 | 216 |
$main::lxdebug->leave_sub(); |
| 283 | 217 |
|
| 284 |
return $rc; |
|
| 218 |
$rc; |
|
| 219 |
|
|
| 285 | 220 |
} |
| 286 | 221 |
|
| 287 | 222 |
sub all_transactions {
|
| 288 |
$main::lxdebug->enter_sub(); |
|
| 289 |
|
|
| 290 | 223 |
my ($self, $myconfig, $form) = @_; |
| 224 |
$main::lxdebug->enter_sub(); |
|
| 291 | 225 |
|
| 292 | 226 |
# connect to database |
| 293 | 227 |
my $dbh = $form->dbconnect($myconfig); |
| ... | ... | |
| 555 | 489 |
($form->{gifi_account_description}) = $sth->fetchrow_array;
|
| 556 | 490 |
$sth->finish; |
| 557 | 491 |
} |
| 492 |
$main::lxdebug->leave_sub(); |
|
| 558 | 493 |
|
| 559 | 494 |
$dbh->disconnect; |
| 560 | 495 |
|
| 561 |
$main::lxdebug->leave_sub(); |
|
| 562 | 496 |
} |
| 563 | 497 |
|
| 564 | 498 |
sub transaction {
|
| 565 |
$main::lxdebug->enter_sub(); |
|
| 566 |
|
|
| 567 | 499 |
my ($self, $myconfig, $form) = @_; |
| 500 |
$main::lxdebug->enter_sub(); |
|
| 568 | 501 |
|
| 569 | 502 |
my ($query, $sth, $ref); |
| 570 | 503 |
|
| 571 | 504 |
# connect to database |
| 572 | 505 |
my $dbh = $form->dbconnect($myconfig); |
| 573 |
$form->{creditrowcount} = 1;
|
|
| 574 |
$form->{debitrowcount} = 1;
|
|
| 506 |
|
|
| 575 | 507 |
if ($form->{id}) {
|
| 576 | 508 |
$query = "SELECT closedto, revtrans |
| 577 | 509 |
FROM defaults"; |
| ... | ... | |
| 584 | 516 |
$query = "SELECT g.reference, g.description, g.notes, g.transdate, |
| 585 | 517 |
d.description AS department, e.name as employee, g.taxincluded, g.gldate |
| 586 | 518 |
FROM gl g |
| 587 |
LEFT JOIN department d ON (d.id = g.department_id) |
|
| 588 |
LEFT JOIN employee e ON (e.id = g.employee_id) |
|
| 519 |
LEFT JOIN department d ON (d.id = g.department_id)
|
|
| 520 |
LEFT JOIN employee e ON (e.id = g.employee_id)
|
|
| 589 | 521 |
WHERE g.id = $form->{id}";
|
| 590 | 522 |
$sth = $dbh->prepare($query); |
| 591 | 523 |
$sth->execute || $form->dberror($query); |
| ... | ... | |
| 594 | 526 |
$sth->finish; |
| 595 | 527 |
|
| 596 | 528 |
# retrieve individual rows |
| 597 |
$query = "SELECT c.accno, a.amount, project_id, |
|
| 529 |
$query = "SELECT c.accno, c.taxkey_id AS accnotaxkey, a.amount, project_id,
|
|
| 598 | 530 |
(SELECT p.projectnumber FROM project p |
| 599 |
WHERE a.project_id = p.id) AS projectnumber, a.taxkey, (SELECT c1.accno FROM chart c1, tax t WHERE t.taxkey=a.taxkey AND c1.id=t.chart_id) AS taxaccno |
|
| 531 |
WHERE a.project_id = p.id) AS projectnumber, a.taxkey, (SELECT c1.accno FROM chart c1, tax t WHERE t.taxkey=a.taxkey AND c1.id=t.chart_id) AS taxaccno, (SELECT t1.rate FROM tax t1 WHERE t1.taxkey=a.taxkey) AS taxrate
|
|
| 600 | 532 |
FROM acc_trans a, chart c |
| 601 | 533 |
WHERE a.chart_id = c.id |
| 602 | 534 |
AND a.trans_id = $form->{id}
|
| 603 |
ORDER BY accno";
|
|
| 535 |
ORDER BY a.oid";
|
|
| 604 | 536 |
$sth = $dbh->prepare($query); |
| 605 | 537 |
$sth->execute || $form->dberror($query); |
| 606 | 538 |
|
| 607 |
$debitcount = 2; |
|
| 608 |
$creditcount = 2; |
|
| 609 |
$taxcount = 2; |
|
| 610 | 539 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 611 |
|
|
| 612 |
if ($ref->{accno} eq $ref->{taxaccno}) {
|
|
| 613 |
$form->{"tax_$taxcount"} = $ref->{amount};
|
|
| 614 |
$form->{"tax"} += $ref->{amount};
|
|
| 615 |
$form->{"taxchartselected_$taxcount"} = $ref->{taxkey};
|
|
| 616 |
$taxcount++; |
|
| 617 |
} else {
|
|
| 618 |
|
|
| 619 |
if ($ref->{amount} < 0) {
|
|
| 620 |
$form->{"debit_$debitcount"} = $ref->{amount} * -1;
|
|
| 621 |
$form->{"debit"} += $ref->{amount} * -1;
|
|
| 622 |
$form->{"debitchartselected_$debitcount"} = $ref->{accno};
|
|
| 623 |
$debitcount++; |
|
| 624 |
} |
|
| 625 |
if ($ref->{amount} > 0) {
|
|
| 626 |
|
|
| 627 |
$form->{"credit_$creditcount"} = $ref->{amount};
|
|
| 628 |
$form->{"credit"} += $ref->{amount};
|
|
| 629 |
$form->{"creditchartselected_$creditcount"} = $ref->{accno};
|
|
| 630 |
$creditcount++; |
|
| 631 |
} |
|
| 632 |
} |
|
| 633 |
|
|
| 634 |
$taxkey = $ref->{taxkey} * 1;
|
|
| 635 |
} |
|
| 636 |
if ($creditcount > 3) {
|
|
| 637 |
$form->{credit_splited} = 1;
|
|
| 638 |
$form->{credit} = $form->{credit} + $form->{tax};
|
|
| 639 |
$form->{creditrowcount} = $creditcount - 1;
|
|
| 640 |
} else {
|
|
| 641 |
$form->{credit} = $form->{credit_2};
|
|
| 642 |
$form->{amount} = $form->{amount_2};
|
|
| 643 |
$form->{creditaccno} = $form->{creditchartselected_2};
|
|
| 644 |
} |
|
| 645 |
if ($debitcount > 3) {
|
|
| 646 |
$form->{debit_splited} = 1;
|
|
| 647 |
$form->{debit} = $form->{debit} + $form->{tax};
|
|
| 648 |
$form->{debitrowcount} = $debitcount - 1;
|
|
| 649 |
} else {
|
|
| 650 |
$form->{debit} = $form->{debit_2};
|
|
| 651 |
$form->{debitaccno} = $form->{debitchartselected_2};
|
|
| 652 |
} |
|
| 653 |
|
|
| 654 |
if ( (($form->{credit} > $form->{debit}) && (!$form->{taxincluded}))
|
|
| 655 |
|| (($form->{credit} > $form->{debit}) && ($form->{taxincluded}))) {
|
|
| 656 |
$form->{amount} = $form->{debit};
|
|
| 657 |
} else {
|
|
| 658 |
$form->{amount} = $form->{credit};
|
|
| 540 |
push @{ $form->{GL} }, $ref;
|
|
| 659 | 541 |
} |
| 660 | 542 |
|
| 661 | 543 |
# get tax description |
| ... | ... | |
| 703 | 585 |
$sth->finish; |
| 704 | 586 |
|
| 705 | 587 |
$sth->finish; |
| 706 |
|
|
| 707 |
$dbh->disconnect; |
|
| 708 |
|
|
| 709 | 588 |
$main::lxdebug->leave_sub(); |
| 710 |
} |
|
| 711 | 589 |
|
| 712 |
sub debug {
|
|
| 713 |
local *OUT; |
|
| 714 |
if (open(OUT, ">>/tmp/linet.log")) {
|
|
| 590 |
$dbh->disconnect; |
|
| 715 | 591 |
|
| 716 |
# chomp(@_); |
|
| 717 |
print(OUT join("\n", @_), "\n");
|
|
| 718 |
close(OUT); |
|
| 719 |
} else {
|
|
| 720 |
print(STDERR "noe: $!\n"); |
|
| 721 |
} |
|
| 722 | 592 |
} |
| 723 | 593 |
|
| 724 | 594 |
1; |
| 595 |
|
|
Auch abrufbar als: Unified diff
Merge von 584-586,588,597 aus unstable: Splitbuchungen
-Buchungsjournal um Anzeige von Splitbuchungen erweitert, Splitbuchungen beim Dialogbuchen zu 80% fertig. Neue Maske fuer Splitbuchungen
Perltidy Lauf der Aenderungen zu Splittbuchungen
-Aenderungen fuer die Unterstuetzung von Splitbuchungen beim Dialogbuchen
-Aenderungen beim Dialogbuchen: Layout der Maske angepasst. Es kann nur eine Seite gesplittet werden, nur eine Seite kann Steuer haben