Revision c7f9bb36
Von Philip Reetz vor fast 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) = @_; |
| 47 |
|
|
| 43 |
|
|
| 48 | 44 |
# connect to database |
| 49 | 45 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| 50 | 46 |
|
| ... | ... | |
| 57 | 53 |
# commit and redirect |
| 58 | 54 |
my $rc = $dbh->commit; |
| 59 | 55 |
$dbh->disconnect; |
| 60 |
|
|
| 61 |
$main::lxdebug->leave_sub(); |
|
| 62 |
|
|
| 63 |
return $rc; |
|
| 56 |
|
|
| 57 |
$rc; |
|
| 58 |
|
|
| 64 | 59 |
} |
| 65 | 60 |
|
| 66 |
sub post_transaction {
|
|
| 67 |
$main::lxdebug->enter_sub(); |
|
| 68 | 61 |
|
| 62 |
sub post_transaction {
|
|
| 69 | 63 |
my ($self, $myconfig, $form) = @_; |
| 70 |
|
|
| 64 |
|
|
| 71 | 65 |
my ($debit, $credit) = (0, 0); |
| 72 | 66 |
my $project_id; |
| 73 | 67 |
|
| 74 | 68 |
my $i; |
| 75 |
|
|
| 76 | 69 |
# check if debit and credit balances |
| 77 |
|
|
| 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 |
|
|
| 70 |
|
|
| 96 | 71 |
if ($form->{storno}) {
|
| 97 |
$debit = $debit * -1;
|
|
| 98 |
$credit = $credit * -1;
|
|
| 99 |
$tax = $tax * -1;
|
|
| 100 |
$form->{reference} = "Storno-" . $form->{reference};
|
|
| 101 |
$form->{description} = "Storno-" . $form->{description};
|
|
| 102 |
} |
|
| 72 |
$debit = $debit * -1;
|
|
| 73 |
$credit = $credit * -1;
|
|
| 74 |
$tax = $tax * -1;
|
|
| 75 |
$form->{reference} = "Storno-".$form->{reference};
|
|
| 76 |
$form->{description} = "Storno-".$form->{description};
|
|
| 77 |
}
|
|
| 103 | 78 |
|
| 104 | 79 |
# connect to database, turn off AutoCommit |
| 105 | 80 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| ... | ... | |
| 119 | 94 |
if (!$form->{taxincluded}) {
|
| 120 | 95 |
$form->{taxincluded} = 0;
|
| 121 | 96 |
} |
| 122 |
|
|
| 97 |
|
|
| 123 | 98 |
my ($query, $sth); |
| 99 |
|
|
| 124 | 100 |
if ($form->{id}) {
|
| 125 |
|
|
| 126 | 101 |
# delete individual transactions |
| 127 |
$query = qq|DELETE FROM acc_trans |
|
| 102 |
$query = qq|DELETE FROM acc_trans
|
|
| 128 | 103 |
WHERE trans_id = $form->{id}|;
|
| 129 | 104 |
$dbh->do($query) || $form->dberror($query); |
| 130 |
|
|
| 105 |
|
|
| 131 | 106 |
} else {
|
| 132 | 107 |
my $uid = time; |
| 133 | 108 |
$uid .= $form->{login};
|
| ... | ... | |
| 136 | 111 |
VALUES ('$uid', (SELECT e.id FROM employee e
|
| 137 | 112 |
WHERE e.login = '$form->{login}'))|;
|
| 138 | 113 |
$dbh->do($query) || $form->dberror($query); |
| 139 |
|
|
| 114 |
|
|
| 140 | 115 |
$query = qq|SELECT g.id FROM gl g |
| 141 | 116 |
WHERE g.reference = '$uid'|; |
| 142 | 117 |
$sth = $dbh->prepare($query); |
| ... | ... | |
| 146 | 121 |
$sth->finish; |
| 147 | 122 |
|
| 148 | 123 |
} |
| 124 |
|
|
| 149 | 125 |
my ($null, $department_id) = split /--/, $form->{department};
|
| 150 | 126 |
$department_id *= 1; |
| 151 |
|
|
| 152 |
$query = qq|UPDATE gl SET |
|
| 127 |
|
|
| 128 |
$query = qq|UPDATE gl SET
|
|
| 153 | 129 |
reference = '$form->{reference}',
|
| 154 | 130 |
description = '$form->{description}',
|
| 155 | 131 |
notes = '$form->{notes}',
|
| ... | ... | |
| 157 | 133 |
department_id = $department_id, |
| 158 | 134 |
taxincluded = '$form->{taxincluded}'
|
| 159 | 135 |
WHERE id = $form->{id}|;
|
| 160 |
|
|
| 136 |
|
|
| 161 | 137 |
$dbh->do($query) || $form->dberror($query); |
| 162 | 138 |
($taxkey, $rate) = split(/--/, $form->{taxkey});
|
| 163 | 139 |
|
| 164 |
# insert acc_trans transactions |
|
| 165 |
foreach $i ((credit, debit)) {
|
|
| 166 | 140 |
|
| 141 |
# insert acc_trans transactions |
|
| 142 |
for $i (1 .. $form->{rowcount}) {
|
|
| 167 | 143 |
# extract accno |
| 168 |
($accno) = split(/--/, $form->{"${i}chartselected"});
|
|
| 144 |
my ($accno) = split(/--/, $form->{"accno_$i"});
|
|
| 145 |
my ($taxkey, $rate) = split(/--/, $form->{"taxchart_$i"});
|
|
| 169 | 146 |
my $amount = 0; |
| 170 |
debug("$accno $i Kontonummer");
|
|
| 171 |
if ($i eq "credit") {
|
|
| 147 |
my $debit = $form->{"debit_$i"};
|
|
| 148 |
my $credit = $form->{"credit_$i"};
|
|
| 149 |
my $tax = $form->{"tax_$i"};
|
|
| 150 |
|
|
| 151 |
if ($credit) {
|
|
| 172 | 152 |
$amount = $credit; |
| 153 |
$posted = 0; |
|
| 173 | 154 |
} |
| 174 |
if ($i eq "debit") {
|
|
| 155 |
if ($debit) {
|
|
| 175 | 156 |
$amount = $debit * -1; |
| 157 |
$tax = $tax * -1; |
|
| 158 |
$posted = 0; |
|
| 176 | 159 |
} |
| 177 | 160 |
|
| 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 | 161 |
|
| 162 |
# if there is an amount, add the record |
|
| 163 |
if ($amount != 0) {
|
|
| 164 |
$project_id = ($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL';
|
|
| 165 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 166 |
source, memo, project_id, taxkey) |
|
| 167 |
VALUES |
|
| 168 |
($form->{id}, (SELECT c.id
|
|
| 169 |
FROM chart c |
|
| 170 |
WHERE c.accno = '$accno'), |
|
| 171 |
$amount, '$form->{transdate}', |.
|
|
| 172 |
$dbh->quote($form->{"source_$i"}) .qq|, |.
|
|
| 173 |
$dbh->quote($form->{"memo_$i"}).qq|,
|
|
| 174 |
$project_id, $taxkey)|; |
|
| 175 |
|
|
| 176 |
$dbh->do($query) || $form->dberror($query); |
|
| 177 |
} |
|
| 178 |
|
|
| 179 |
if ($tax !=0) {
|
|
| 211 | 180 |
# add taxentry |
| 212 |
if ($i eq "debit") {
|
|
| 213 |
$tax = $tax * (-1); |
|
| 214 |
} |
|
| 215 | 181 |
$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 |
} |
|
| 253 |
} |
|
| 254 |
} |
|
| 255 |
if ($tax != 0 && !($form->{credit_splited} || $form->{debit_splited})) {
|
|
| 256 |
|
|
| 257 |
# add taxentry |
|
| 258 |
if ($form->{debittaxkey}) {
|
|
| 259 |
$tax = $tax * (-1); |
|
| 260 |
} |
|
| 261 |
$amount = $tax; |
|
| 262 |
debug("$amount Steuern buchen");
|
|
| 263 |
|
|
| 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) |
|
| 182 |
|
|
| 183 |
|
|
| 184 |
$project_id = ($form->{"project_id_$i"}) ? $form->{"project_id_$i"} : 'NULL';
|
|
| 185 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 186 |
source, memo, project_id, taxkey) |
|
| 268 | 187 |
VALUES |
| 269 | 188 |
($form->{id}, (SELECT t.chart_id
|
| 270 | 189 |
FROM tax t |
| 271 | 190 |
WHERE t.taxkey = $taxkey), |
| 272 |
$amount, '$form->{transdate}', '$form->{reference}',
|
|
| 191 |
$amount, '$form->{transdate}', |.
|
|
| 192 |
$dbh->quote($form->{"source_$i"}) .qq|, |.
|
|
| 193 |
$dbh->quote($form->{"memo_$i"}).qq|,
|
|
| 273 | 194 |
$project_id, $taxkey)|; |
| 274 |
|
|
| 275 |
$dbh->do($query) || $form->dberror($query); |
|
| 195 |
|
|
| 196 |
$dbh->do($query) || $form->dberror($query); |
|
| 197 |
} |
|
| 276 | 198 |
} |
| 277 | 199 |
|
| 200 |
my %audittrail = ( tablename => 'gl', |
|
| 201 |
reference => $form->{reference},
|
|
| 202 |
formname => 'transaction', |
|
| 203 |
action => 'posted', |
|
| 204 |
id => $form->{id} );
|
|
| 205 |
|
|
| 206 |
# $form->audittrail($dbh, "", \%audittrail); |
|
| 207 |
|
|
| 278 | 208 |
# commit and redirect |
| 279 | 209 |
my $rc = $dbh->commit; |
| 280 | 210 |
$dbh->disconnect; |
| 281 | 211 |
|
| 282 |
$main::lxdebug->leave_sub();
|
|
| 212 |
$rc;
|
|
| 283 | 213 |
|
| 284 |
return $rc; |
|
| 285 | 214 |
} |
| 286 | 215 |
|
| 287 |
sub all_transactions {
|
|
| 288 |
$main::lxdebug->enter_sub(); |
|
| 289 | 216 |
|
| 217 |
|
|
| 218 |
sub all_transactions {
|
|
| 290 | 219 |
my ($self, $myconfig, $form) = @_; |
| 291 | 220 |
|
| 292 | 221 |
# connect to database |
| ... | ... | |
| 294 | 223 |
my ($query, $sth, $source, $null); |
| 295 | 224 |
|
| 296 | 225 |
my ($glwhere, $arwhere, $apwhere) = ("1 = 1", "1 = 1", "1 = 1");
|
| 297 |
|
|
| 226 |
|
|
| 298 | 227 |
if ($form->{reference}) {
|
| 299 | 228 |
$source = $form->like(lc $form->{reference});
|
| 300 | 229 |
$glwhere .= " AND lower(g.reference) LIKE '$source'"; |
| ... | ... | |
| 353 | 282 |
} |
| 354 | 283 |
|
| 355 | 284 |
if ($form->{accno}) {
|
| 356 |
|
|
| 357 | 285 |
# get category for account |
| 358 | 286 |
$query = qq|SELECT c.category |
| 359 | 287 |
FROM chart c |
| 360 | 288 |
WHERE c.accno = '$form->{accno}'|;
|
| 361 |
$sth = $dbh->prepare($query); |
|
| 362 |
|
|
| 363 |
$sth->execute || $form->dberror($query); |
|
| 364 |
($form->{ml}) = $sth->fetchrow_array;
|
|
| 365 |
$sth->finish; |
|
| 289 |
$sth = $dbh->prepare($query); |
|
| 366 | 290 |
|
| 291 |
$sth->execute || $form->dberror($query); |
|
| 292 |
($form->{ml}) = $sth->fetchrow_array;
|
|
| 293 |
$sth->finish; |
|
| 294 |
|
|
| 367 | 295 |
if ($form->{datefrom}) {
|
| 368 | 296 |
$query = qq|SELECT SUM(ac.amount) |
| 369 | 297 |
FROM acc_trans ac, chart c |
| ... | ... | |
| 378 | 306 |
$sth->finish; |
| 379 | 307 |
} |
| 380 | 308 |
} |
| 381 |
|
|
| 309 |
|
|
| 382 | 310 |
if ($form->{gifi_accno}) {
|
| 383 |
|
|
| 384 | 311 |
# get category for account |
| 385 | 312 |
$query = qq|SELECT c.category |
| 386 | 313 |
FROM chart c |
| 387 | 314 |
WHERE c.gifi_accno = '$form->{gifi_accno}'|;
|
| 388 |
$sth = $dbh->prepare($query); |
|
| 389 |
|
|
| 390 |
$sth->execute || $form->dberror($query); |
|
| 391 |
($form->{ml}) = $sth->fetchrow_array;
|
|
| 392 |
$sth->finish; |
|
| 315 |
$sth = $dbh->prepare($query); |
|
| 393 | 316 |
|
| 317 |
$sth->execute || $form->dberror($query); |
|
| 318 |
($form->{ml}) = $sth->fetchrow_array;
|
|
| 319 |
$sth->finish; |
|
| 320 |
|
|
| 394 | 321 |
if ($form->{datefrom}) {
|
| 395 | 322 |
$query = qq|SELECT SUM(ac.amount) |
| 396 | 323 |
FROM acc_trans ac, chart c |
| ... | ... | |
| 406 | 333 |
} |
| 407 | 334 |
} |
| 408 | 335 |
|
| 409 |
my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE: q|'0'|;
|
|
| 336 |
my $false = ($myconfig->{dbdriver} eq 'Pg') ? FALSE : q|'0'|;
|
|
| 410 | 337 |
|
| 411 |
my $query = |
|
| 412 |
qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, t.taxkey AS sorttax,
|
|
| 338 |
|
|
| 339 |
my $query = qq|SELECT g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, t.taxkey AS sorttax,
|
|
| 413 | 340 |
g.description, ac.transdate, ac.source, ac.trans_id, |
| 414 | 341 |
ac.amount, c.accno, c.gifi_accno, g.notes, t.chart_id, ac.oid |
| 415 | 342 |
FROM gl g, acc_trans ac, chart c LEFT JOIN tax t ON |
| ... | ... | |
| 440 | 367 |
ORDER BY transdate, trans_id, taxkey DESC, sorttax DESC, oid|; |
| 441 | 368 |
my $sth = $dbh->prepare($query); |
| 442 | 369 |
$sth->execute || $form->dberror($query); |
| 443 |
|
|
| 370 |
|
|
| 444 | 371 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 445 | 372 |
print(STDERR $ref->{id}, " Transaction\n");
|
| 446 |
|
|
| 447 | 373 |
# gl |
| 448 | 374 |
if ($ref->{type} eq "gl") {
|
| 449 | 375 |
$ref->{module} = "gl";
|
| ... | ... | |
| 466 | 392 |
$ref->{module} = "ar";
|
| 467 | 393 |
} |
| 468 | 394 |
} |
| 469 |
$balance = $ref->{amount};
|
|
| 470 |
$i = 0;
|
|
| 471 |
$j = 0;
|
|
| 472 |
$k = 0;
|
|
| 473 |
$l = 0;
|
|
| 395 |
$balance=$ref->{amount};
|
|
| 396 |
$i = 0; |
|
| 397 |
$j = 0; |
|
| 398 |
$k = 0; |
|
| 399 |
$l = 0; |
|
| 474 | 400 |
if ($ref->{amount} < 0) {
|
| 475 |
if ($ref->{chart_id} > 0) {
|
|
| 476 |
$ref->{debit_tax}{$i} = $ref->{amount} * -1;
|
|
| 401 |
if ($ref->{chart_id} >0) {
|
|
| 402 |
$ref->{debit_tax}{$i} = $ref->{amount} * -1;
|
|
| 477 | 403 |
$ref->{debit_tax_accno}{$i} = $ref->{accno};
|
| 478 |
} else {
|
|
| 479 |
$ref->{debit}{$k} = $ref->{amount} * -1;
|
|
| 480 |
$ref->{debit_accno}{$k} = $ref->{accno};
|
|
| 404 |
} |
|
| 405 |
else {
|
|
| 406 |
$ref->{debit}{$k} = $ref->{amount} * -1;
|
|
| 407 |
$ref->{debit_accno}{$k} = $ref->{accno};
|
|
| 481 | 408 |
$ref->{debit_taxkey}{$k} = $ref->{taxkey};
|
| 482 |
} |
|
| 409 |
}
|
|
| 483 | 410 |
} else {
|
| 484 |
if ($ref->{chart_id} > 0) {
|
|
| 485 |
$ref->{credit_tax}{$j} = $ref->{amount};
|
|
| 411 |
if ($ref->{chart_id} >0) {
|
|
| 412 |
$ref->{credit_tax}{$j} = $ref->{amount};
|
|
| 486 | 413 |
$ref->{credit_tax_accno}{$j} = $ref->{accno};
|
| 487 |
} else {
|
|
| 488 |
$ref->{credit}{$l} = $ref->{amount};
|
|
| 489 |
$ref->{credit_accno}{$l} = $ref->{accno};
|
|
| 414 |
} |
|
| 415 |
else {
|
|
| 416 |
$ref->{credit}{$l} = $ref->{amount};
|
|
| 417 |
$ref->{credit_accno}{$l} = $ref->{accno};
|
|
| 490 | 418 |
$ref->{credit_taxkey}{$l} = $ref->{taxkey};
|
| 491 |
} |
|
| 419 |
}
|
|
| 492 | 420 |
} |
| 493 | 421 |
|
| 494 |
while (abs($balance) >= 0.015) {
|
|
| 495 |
my $ref2 = $sth->fetchrow_hashref(NAME_lc) |
|
| 496 |
|| $form->error("Unbalanced ledger!");
|
|
| 422 |
while (abs($balance)>=0.015) {
|
|
| 423 |
my $ref2 = $sth->fetchrow_hashref(NAME_lc) || $form->error("Unbalanced ledger!");
|
|
| 497 | 424 |
|
| 498 |
$balance = |
|
| 499 |
(int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000;
|
|
| 500 |
print(STDERR $balance, " BAlance\n"); |
|
| 425 |
$balance = (int($balance * 100000) + int(100000 * $ref2->{amount})) / 100000;
|
|
| 426 |
print(STDERR $balance," BAlance\n"); |
|
| 501 | 427 |
if ($ref2->{amount} < 0) {
|
| 502 |
if ($ref2->{chart_id} > 0) {
|
|
| 428 |
if ($ref2->{chart_id} >0) {
|
|
| 503 | 429 |
if ($ref->{debit_tax_accno}{$i} ne "") {
|
| 504 | 430 |
$i++; |
| 505 | 431 |
} |
| 506 |
$ref->{debit_tax}{$i} = $ref2->{amount} * -1;
|
|
| 432 |
$ref->{debit_tax}{$i} = $ref2->{amount} * -1;
|
|
| 507 | 433 |
$ref->{debit_tax_accno}{$i} = $ref2->{accno};
|
| 508 |
} else {
|
|
| 434 |
} |
|
| 435 |
else {
|
|
| 509 | 436 |
if ($ref->{debit_accno}{$k} ne "") {
|
| 510 | 437 |
$k++; |
| 511 | 438 |
} |
| 512 |
$ref->{debit}{$k} = $ref2->{amount} * -1;
|
|
| 513 |
$ref->{debit_accno}{$k} = $ref2->{accno};
|
|
| 439 |
$ref->{debit}{$k} = $ref2->{amount} * -1;
|
|
| 440 |
$ref->{debit_accno}{$k} = $ref2->{accno};
|
|
| 514 | 441 |
$ref->{debit_taxkey}{$k} = $ref2->{taxkey};
|
| 515 |
} |
|
| 442 |
}
|
|
| 516 | 443 |
} else {
|
| 517 |
if ($ref2->{chart_id} > 0) {
|
|
| 444 |
if ($ref2->{chart_id} >0) {
|
|
| 518 | 445 |
if ($ref->{credit_tax_accno}{$j} ne "") {
|
| 519 | 446 |
$j++; |
| 520 | 447 |
} |
| 521 |
$ref->{credit_tax}{$j} = $ref2->{amount};
|
|
| 448 |
$ref->{credit_tax}{$j} = $ref2->{amount};
|
|
| 522 | 449 |
$ref->{credit_tax_accno}{$j} = $ref2->{accno};
|
| 523 |
} else {
|
|
| 450 |
} |
|
| 451 |
else {
|
|
| 524 | 452 |
if ($ref->{credit_accno}{$l} ne "") {
|
| 525 | 453 |
$l++; |
| 526 | 454 |
} |
| 527 |
$ref->{credit}{$l} = $ref2->{amount};
|
|
| 528 |
$ref->{credit_accno}{$l} = $ref2->{accno};
|
|
| 455 |
$ref->{credit}{$l} = $ref2->{amount};
|
|
| 456 |
$ref->{credit_accno}{$l} = $ref2->{accno};
|
|
| 529 | 457 |
$ref->{credit_taxkey}{$l} = $ref2->{taxkey};
|
| 530 |
} |
|
| 458 |
}
|
|
| 531 | 459 |
} |
| 532 | 460 |
} |
| 533 |
|
|
| 534 |
# print(STDERR Dumper($ref)); |
|
| 461 |
# print(STDERR Dumper($ref)); |
|
| 535 | 462 |
push @{ $form->{GL} }, $ref;
|
| 536 |
$balance = 0;
|
|
| 463 |
$balance=0;
|
|
| 537 | 464 |
} |
| 538 | 465 |
$sth->finish; |
| 539 | 466 |
|
| 540 | 467 |
if ($form->{accno}) {
|
| 541 |
$query = |
|
| 542 |
qq|SELECT c.description FROM chart c WHERE c.accno = '$form->{accno}'|;
|
|
| 468 |
$query = qq|SELECT c.description FROM chart c WHERE c.accno = '$form->{accno}'|;
|
|
| 543 | 469 |
$sth = $dbh->prepare($query); |
| 544 | 470 |
$sth->execute || $form->dberror($query); |
| 545 | 471 |
|
| ... | ... | |
| 547 | 473 |
$sth->finish; |
| 548 | 474 |
} |
| 549 | 475 |
if ($form->{gifi_accno}) {
|
| 550 |
$query = |
|
| 551 |
qq|SELECT g.description FROM gifi g WHERE g.accno = '$form->{gifi_accno}'|;
|
|
| 476 |
$query = qq|SELECT g.description FROM gifi g WHERE g.accno = '$form->{gifi_accno}'|;
|
|
| 552 | 477 |
$sth = $dbh->prepare($query); |
| 553 | 478 |
$sth->execute || $form->dberror($query); |
| 554 | 479 |
|
| 555 | 480 |
($form->{gifi_account_description}) = $sth->fetchrow_array;
|
| 556 | 481 |
$sth->finish; |
| 557 | 482 |
} |
| 558 |
|
|
| 483 |
|
|
| 559 | 484 |
$dbh->disconnect; |
| 560 | 485 |
|
| 561 |
$main::lxdebug->leave_sub(); |
|
| 562 | 486 |
} |
| 563 | 487 |
|
| 564 |
sub transaction {
|
|
| 565 |
$main::lxdebug->enter_sub(); |
|
| 566 | 488 |
|
| 489 |
sub transaction {
|
|
| 567 | 490 |
my ($self, $myconfig, $form) = @_; |
| 568 |
|
|
| 491 |
|
|
| 569 | 492 |
my ($query, $sth, $ref); |
| 570 |
|
|
| 493 |
|
|
| 571 | 494 |
# connect to database |
| 572 | 495 |
my $dbh = $form->dbconnect($myconfig); |
| 573 |
$form->{creditrowcount} = 1;
|
|
| 574 |
$form->{debitrowcount} = 1;
|
|
| 496 |
|
|
| 575 | 497 |
if ($form->{id}) {
|
| 576 | 498 |
$query = "SELECT closedto, revtrans |
| 577 | 499 |
FROM defaults"; |
| ... | ... | |
| 584 | 506 |
$query = "SELECT g.reference, g.description, g.notes, g.transdate, |
| 585 | 507 |
d.description AS department, e.name as employee, g.taxincluded, g.gldate |
| 586 | 508 |
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) |
|
| 509 |
LEFT JOIN department d ON (d.id = g.department_id)
|
|
| 510 |
LEFT JOIN employee e ON (e.id = g.employee_id)
|
|
| 589 | 511 |
WHERE g.id = $form->{id}";
|
| 590 | 512 |
$sth = $dbh->prepare($query); |
| 591 | 513 |
$sth->execute || $form->dberror($query); |
| 592 | 514 |
$ref = $sth->fetchrow_hashref(NAME_lc); |
| 593 | 515 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
| 594 | 516 |
$sth->finish; |
| 595 |
|
|
| 517 |
|
|
| 596 | 518 |
# retrieve individual rows |
| 597 | 519 |
$query = "SELECT c.accno, a.amount, project_id, |
| 598 | 520 |
(SELECT p.projectnumber FROM project p |
| ... | ... | |
| 600 | 522 |
FROM acc_trans a, chart c |
| 601 | 523 |
WHERE a.chart_id = c.id |
| 602 | 524 |
AND a.trans_id = $form->{id}
|
| 603 |
ORDER BY accno";
|
|
| 525 |
ORDER BY a.oid";
|
|
| 604 | 526 |
$sth = $dbh->prepare($query); |
| 605 | 527 |
$sth->execute || $form->dberror($query); |
| 606 |
|
|
| 607 |
$debitcount = 2; |
|
| 608 |
$creditcount = 2; |
|
| 609 |
$taxcount = 2; |
|
| 528 |
|
|
| 610 | 529 |
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};
|
|
| 530 |
push @{ $form->{GL} }, $ref;
|
|
| 659 | 531 |
} |
| 660 | 532 |
|
| 661 |
# get tax description |
|
| 533 |
# get tax description
|
|
| 662 | 534 |
$query = qq| SELECT * FROM tax t|; |
| 663 |
$sth = $dbh->prepare($query);
|
|
| 664 |
$sth->execute || $form->dberror($query); |
|
| 535 |
$sth = $dbh->prepare($query); |
|
| 536 |
$sth->execute || $form->dberror($query);
|
|
| 665 | 537 |
$form->{TAX} = ();
|
| 666 | 538 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 667 | 539 |
push @{ $form->{TAX} }, $ref;
|
| 668 | 540 |
} |
| 669 |
|
|
| 541 |
|
|
| 670 | 542 |
$sth->finish; |
| 671 | 543 |
} else {
|
| 672 | 544 |
$query = "SELECT current_date AS transdate, closedto, revtrans |
| ... | ... | |
| 674 | 546 |
$sth = $dbh->prepare($query); |
| 675 | 547 |
$sth->execute || $form->dberror($query); |
| 676 | 548 |
|
| 677 |
($form->{transdate}, $form->{closedto}, $form->{revtrans}) =
|
|
| 678 |
$sth->fetchrow_array; |
|
| 679 |
|
|
| 680 |
# get tax description |
|
| 549 |
($form->{transdate}, $form->{closedto}, $form->{revtrans}) = $sth->fetchrow_array;
|
|
| 550 |
|
|
| 551 |
# get tax description |
|
| 681 | 552 |
$query = qq| SELECT * FROM tax t order by t.taxkey|; |
| 682 |
$sth = $dbh->prepare($query);
|
|
| 683 |
$sth->execute || $form->dberror($query); |
|
| 553 |
$sth = $dbh->prepare($query); |
|
| 554 |
$sth->execute || $form->dberror($query);
|
|
| 684 | 555 |
$form->{TAX} = ();
|
| 685 | 556 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 686 | 557 |
push @{ $form->{TAX} }, $ref;
|
| ... | ... | |
| 701 | 572 |
push @{ $form->{chart} }, $ref;
|
| 702 | 573 |
} |
| 703 | 574 |
$sth->finish; |
| 575 |
|
|
| 704 | 576 |
|
| 577 |
|
|
| 705 | 578 |
$sth->finish; |
| 706 |
|
|
| 579 |
|
|
| 707 | 580 |
$dbh->disconnect; |
| 708 | 581 |
|
| 709 |
$main::lxdebug->leave_sub(); |
|
| 710 | 582 |
} |
| 711 | 583 |
|
| 712 |
sub debug {
|
|
| 713 |
local *OUT; |
|
| 714 |
if (open(OUT, ">>/tmp/linet.log")) {
|
|
| 715 |
|
|
| 716 |
# chomp(@_); |
|
| 717 |
print(OUT join("\n", @_), "\n");
|
|
| 718 |
close(OUT); |
|
| 719 |
} else {
|
|
| 720 |
print(STDERR "noe: $!\n"); |
|
| 721 |
} |
|
| 722 |
} |
|
| 723 | 584 |
|
| 724 | 585 |
1; |
| 586 |
|
|
Auch abrufbar als: Unified diff
Buchungsjournal um Anzeige von Splitbuchungen erweitert, Splitbuchungen beim Dialogbuchen zu 80% fertig. Neue Maske fuer Splitbuchungen