Revision 532b0d4c
Von Moritz Bunkus vor mehr als 17 Jahren hinzugefügt
| SL/IS.pm | ||
|---|---|---|
| 44 | 44 |
|
| 45 | 45 |
my ($self, $myconfig, $form, $locale) = @_; |
| 46 | 46 |
|
| 47 |
$form->{duedate} = $form->{invdate} unless ($form->{duedate});
|
|
| 47 |
$form->{duedate} ||= $form->{invdate};
|
|
| 48 | 48 |
|
| 49 | 49 |
# connect to database |
| 50 | 50 |
my $dbh = $form->dbconnect($myconfig); |
| 51 |
my $sth; |
|
| 51 | 52 |
|
| 52 |
my $query = qq|SELECT date '$form->{duedate}' - date '$form->{invdate}'
|
|
| 53 |
AS terms |
|
| 54 |
FROM defaults|; |
|
| 55 |
my $sth = $dbh->prepare($query); |
|
| 56 |
$sth->execute || $form->dberror($query); |
|
| 57 |
|
|
| 58 |
($form->{terms}) = $sth->fetchrow_array;
|
|
| 59 |
$sth->finish; |
|
| 53 |
my $query = qq|SELECT date | . conv_dateq($form->{duedate}) . qq| - date | . conv_dateq($form->{invdate}) . qq| AS terms|;
|
|
| 54 |
($form->{terms}) = selectrow_query($form, $dbh, $query);
|
|
| 60 | 55 |
|
| 61 | 56 |
my (@project_ids, %projectnumbers); |
| 62 | 57 |
|
| ... | ... | |
| 178 | 173 |
|
| 179 | 174 |
if ($form->{lizenzen}) {
|
| 180 | 175 |
if ($form->{"licensenumber_$i"}) {
|
| 181 |
$query = |
|
| 182 |
qq|SELECT l.licensenumber, l.validuntil FROM license l WHERE l.id = $form->{"licensenumber_$i"}|;
|
|
| 183 |
$sth = $dbh->prepare($query); |
|
| 184 |
$sth->execute || $form->dberror($query); |
|
| 185 |
|
|
| 186 |
($licensenumber, $validuntil) = $sth->fetchrow_array; |
|
| 176 |
$query = qq|SELECT licensenumber, validuntil FROM license WHERE id = ?|; |
|
| 177 |
($licensenumber, $validuntil) = selectrow_query($form, $dbh, $query, conv_i($form->{"licensenumber_$i"}));
|
|
| 187 | 178 |
push(@{ $form->{licensenumber} }, $licensenumber);
|
| 188 |
push(@{ $form->{validuntil} },
|
|
| 189 |
$locale->date($myconfig, $validuntil, 0)); |
|
| 190 |
$sth->finish; |
|
| 179 |
push(@{ $form->{validuntil} }, $locale->date($myconfig, $validuntil, 0));
|
|
| 180 |
|
|
| 191 | 181 |
} else {
|
| 192 | 182 |
push(@{ $form->{licensenumber} }, "");
|
| 193 | 183 |
push(@{ $form->{validuntil} }, "");
|
| ... | ... | |
| 269 | 259 |
|
| 270 | 260 |
push(@{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}});
|
| 271 | 261 |
|
| 272 |
@taxaccounts = split / /, $form->{"taxaccounts_$i"};
|
|
| 262 |
@taxaccounts = split(/ /, $form->{"taxaccounts_$i"});
|
|
| 273 | 263 |
$taxrate = 0; |
| 274 | 264 |
$taxdiff = 0; |
| 275 | 265 |
|
| ... | ... | |
| 326 | 316 |
$sortorder = qq|ORDER BY a.$oid{$myconfig->{dbdriver}}|;
|
| 327 | 317 |
} |
| 328 | 318 |
|
| 329 |
$query = qq|SELECT p.partnumber, p.description, p.unit, a.qty, |
|
| 330 |
pg.partsgroup |
|
| 331 |
FROM assembly a |
|
| 332 |
JOIN parts p ON (a.parts_id = p.id) |
|
| 333 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 334 |
WHERE a.bom = '1' |
|
| 335 |
AND a.id = '$form->{"id_$i"}'
|
|
| 336 |
$sortorder|; |
|
| 337 |
$sth = $dbh->prepare($query); |
|
| 338 |
$sth->execute || $form->dberror($query); |
|
| 319 |
$query = |
|
| 320 |
qq|SELECT p.partnumber, p.description, p.unit, a.qty, pg.partsgroup |
|
| 321 |
FROM assembly a |
|
| 322 |
JOIN parts p ON (a.parts_id = p.id) |
|
| 323 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 324 |
WHERE (a.bom = '1') AND (a.id = ?) $sortorder|; |
|
| 325 |
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
|
|
| 339 | 326 |
|
| 340 | 327 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 341 | 328 |
if ($form->{groupitems} && $ref->{partsgroup} ne $sameitem) {
|
| ... | ... | |
| 374 | 361 |
for my $i (1 .. $form->{paidaccounts}) {
|
| 375 | 362 |
if ($form->{"paid_$i"}) {
|
| 376 | 363 |
push(@{ $form->{payment} }, $form->{"paid_$i"});
|
| 377 |
my ($accno, $description) = split /--/, $form->{"AR_paid_$i"};
|
|
| 364 |
my ($accno, $description) = split(/--/, $form->{"AR_paid_$i"});
|
|
| 378 | 365 |
push(@{ $form->{paymentaccount} }, $description);
|
| 379 | 366 |
push(@{ $form->{paymentdate} }, $form->{"datepaid_$i"});
|
| 380 | 367 |
push(@{ $form->{paymentsource} }, $form->{"source_$i"});
|
| ... | ... | |
| 415 | 402 |
|
| 416 | 403 |
my ($self, $dbh, $id) = @_; |
| 417 | 404 |
|
| 418 |
my $query = qq|SELECT p.description |
|
| 419 |
FROM project p |
|
| 420 |
WHERE p.id = $id|; |
|
| 421 |
my $sth = $dbh->prepare($query); |
|
| 422 |
$sth->execute || $form->dberror($query); |
|
| 423 |
|
|
| 424 |
($_) = $sth->fetchrow_array; |
|
| 425 |
|
|
| 426 |
$sth->finish; |
|
| 405 |
my $query = qq|SELECT description FROM project WHERE id = ?|; |
|
| 406 |
my ($description) = selectrow_query($form, $dbh, $query, conv_i($id)); |
|
| 427 | 407 |
|
| 428 | 408 |
$main::lxdebug->leave_sub(); |
| 429 | 409 |
|
| ... | ... | |
| 441 | 421 |
# get contact id, set it if nessessary |
| 442 | 422 |
$form->{cp_id} *= 1;
|
| 443 | 423 |
|
| 444 |
$contact = ""; |
|
| 424 |
my @values; |
|
| 425 |
|
|
| 426 |
my $where = ""; |
|
| 445 | 427 |
if ($form->{cp_id}) {
|
| 446 |
$contact = "and cp.cp_id = $form->{cp_id}";
|
|
| 428 |
$where = qq| AND (cp.cp_id = ?) |; |
|
| 429 |
push(@values, conv_i($form->{cp_id}));
|
|
| 447 | 430 |
} |
| 448 | 431 |
|
| 449 | 432 |
# get rest for the customer |
| 450 |
my $query = qq|SELECT ct.*, cp.*, ct.notes as customernotes, ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail |
|
| 451 |
FROM customer ct |
|
| 452 |
LEFT JOIN contacts cp on ct.id = cp.cp_cv_id |
|
| 453 |
WHERE ct.id = $form->{customer_id} $contact order by cp.cp_id limit 1|;
|
|
| 454 |
my $sth = $dbh->prepare($query); |
|
| 455 |
$sth->execute || $form->dberror($query); |
|
| 456 |
|
|
| 457 |
$ref = $sth->fetchrow_hashref(NAME_lc); |
|
| 433 |
my $query = |
|
| 434 |
qq|SELECT ct.*, cp.*, ct.notes as customernotes, |
|
| 435 |
ct.phone AS customerphone, ct.fax AS customerfax, ct.email AS customeremail |
|
| 436 |
FROM customer ct |
|
| 437 |
LEFT JOIN contacts cp on ct.id = cp.cp_cv_id |
|
| 438 |
WHERE (ct.id = ?) $where |
|
| 439 |
ORDER BY cp.cp_id |
|
| 440 |
LIMIT 1|; |
|
| 441 |
push(@values, conv_i($form->{customer_id}));
|
|
| 442 |
my $ref = selectfirst_hashref_query($form, $dbh, $query, @values); |
|
| 458 | 443 |
|
| 459 | 444 |
# remove id and taxincluded before copy back |
| 460 | 445 |
delete @$ref{qw(id taxincluded)};
|
| ... | ... | |
| 467 | 452 |
} |
| 468 | 453 |
|
| 469 | 454 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
| 470 |
$sth->finish; |
|
| 471 | 455 |
|
| 472 | 456 |
if ($form->{delivery_customer_id}) {
|
| 473 |
my $query = qq|SELECT ct.*, ct.notes as customernotes |
|
| 474 |
FROM customer ct |
|
| 475 |
WHERE ct.id = $form->{delivery_customer_id} limit 1|;
|
|
| 476 |
my $sth = $dbh->prepare($query); |
|
| 477 |
$sth->execute || $form->dberror($query); |
|
| 478 |
|
|
| 479 |
$ref = $sth->fetchrow_hashref(NAME_lc); |
|
| 457 |
$query = |
|
| 458 |
qq|SELECT *, notes as customernotes |
|
| 459 |
FROM customer |
|
| 460 |
WHERE id = ? |
|
| 461 |
LIMIT 1|; |
|
| 462 |
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_customer_id}));
|
|
| 480 | 463 |
|
| 481 |
$sth->finish; |
|
| 482 | 464 |
map { $form->{"dc_$_"} = $ref->{$_} } keys %$ref;
|
| 483 | 465 |
} |
| 484 | 466 |
|
| 485 | 467 |
if ($form->{delivery_vendor_id}) {
|
| 486 |
my $query = qq|SELECT ct.*, ct.notes as customernotes |
|
| 487 |
FROM customer ct |
|
| 488 |
WHERE ct.id = $form->{delivery_vendor_id} limit 1|;
|
|
| 489 |
my $sth = $dbh->prepare($query); |
|
| 490 |
$sth->execute || $form->dberror($query); |
|
| 491 |
|
|
| 492 |
$ref = $sth->fetchrow_hashref(NAME_lc); |
|
| 468 |
$query = |
|
| 469 |
qq|SELECT *, notes as customernotes |
|
| 470 |
FROM customer |
|
| 471 |
WHERE id = ? |
|
| 472 |
LIMIT 1|; |
|
| 473 |
$ref = selectfirst_hashref_query($form, $dbh, $query, conv_i($form->{delivery_vendor_id}));
|
|
| 493 | 474 |
|
| 494 |
$sth->finish; |
|
| 495 | 475 |
map { $form->{"dv_$_"} = $ref->{$_} } keys %$ref;
|
| 496 | 476 |
} |
| 497 | 477 |
$dbh->disconnect; |
| ... | ... | |
| 507 | 487 |
# connect to database, turn off autocommit |
| 508 | 488 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| 509 | 489 |
|
| 510 |
my ($query, $sth, $null, $project_id, $deliverydate); |
|
| 490 |
my ($query, $sth, $null, $project_id, $deliverydate, @values);
|
|
| 511 | 491 |
my $exchangerate = 0; |
| 512 | 492 |
|
| 513 |
($null, $form->{employee_id}) = split /--/, $form->{employee};
|
|
| 493 |
($null, $form->{employee_id}) = split(/--/, $form->{employee});
|
|
| 514 | 494 |
unless ($form->{employee_id}) {
|
| 515 | 495 |
$form->get_employee($dbh); |
| 516 | 496 |
} |
| 517 | 497 |
|
| 518 |
$form->{payment_id} *= 1;
|
|
| 519 |
$form->{language_id} *= 1;
|
|
| 520 |
$form->{taxzone_id} *= 1;
|
|
| 521 |
$form->{delivery_customer_id} *= 1;
|
|
| 522 |
$form->{delivery_vendor_id} *= 1;
|
|
| 523 |
$form->{storno} *= 1;
|
|
| 524 |
$form->{shipto_id} *= 1;
|
|
| 525 |
|
|
| 526 |
|
|
| 527 | 498 |
($null, $form->{department_id}) = split(/--/, $form->{department});
|
| 528 |
$form->{department_id} *= 1;
|
|
| 529 |
|
|
| 530 |
my $service_units = AM->retrieve_units($myconfig,$form,"service"); |
|
| 531 |
my $part_units = AM->retrieve_units($myconfig,$form,"dimension"); |
|
| 532 |
|
|
| 533 | 499 |
|
| 500 |
my $all_units = AM->retrieve_units($myconfig, $form); |
|
| 534 | 501 |
|
| 535 | 502 |
if ($form->{id}) {
|
| 536 | 503 |
|
| 537 | 504 |
&reverse_invoice($dbh, $form); |
| 538 | 505 |
|
| 539 | 506 |
} else {
|
| 540 |
my $uid = rand() . time; |
|
| 507 |
$query = qq|SELECT nextval('glid')|;
|
|
| 508 |
($form->{"id"}) = selectrow_query($form, $dbh, $query);
|
|
| 541 | 509 |
|
| 542 |
$uid .= $form->{login};
|
|
| 543 |
|
|
| 544 |
$uid = substr($uid, 2, 75); |
|
| 545 |
|
|
| 546 |
$query = qq|INSERT INTO ar (invnumber, employee_id) |
|
| 547 |
VALUES ('$uid', $form->{employee_id})|;
|
|
| 548 |
$dbh->do($query) || $form->dberror($query); |
|
| 549 |
|
|
| 550 |
$query = qq|SELECT a.id FROM ar a |
|
| 551 |
WHERE a.invnumber = '$uid'|; |
|
| 552 |
$sth = $dbh->prepare($query); |
|
| 553 |
$sth->execute || $form->dberror($query); |
|
| 554 |
|
|
| 555 |
($form->{id}) = $sth->fetchrow_array;
|
|
| 556 |
$sth->finish; |
|
| 510 |
$query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|; |
|
| 511 |
do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"});
|
|
| 557 | 512 |
|
| 558 | 513 |
if (!$form->{invnumber}) {
|
| 559 | 514 |
$form->{invnumber} =
|
| ... | ... | |
| 562 | 517 |
} |
| 563 | 518 |
} |
| 564 | 519 |
|
| 565 |
map { $form->{$_} =~ s/\'/\'\'/g }
|
|
| 566 |
(qw(invnumber shippingpoint shipvia notes intnotes message)); |
|
| 567 |
|
|
| 568 | 520 |
my ($netamount, $invoicediff) = (0, 0); |
| 569 | 521 |
my ($amount, $linetotal, $lastincomeaccno); |
| 570 | 522 |
|
| ... | ... | |
| 583 | 535 |
|
| 584 | 536 |
$form->{expense_inventory} = "";
|
| 585 | 537 |
|
| 538 |
my %baseunits; |
|
| 539 |
|
|
| 586 | 540 |
foreach my $i (1 .. $form->{rowcount}) {
|
| 587 | 541 |
if ($form->{type} eq "credit_note") {
|
| 588 | 542 |
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}) * -1;
|
| 589 |
$form->{shipped} = 1;
|
|
| 543 |
$form->{shipped} = 1;
|
|
| 590 | 544 |
} else {
|
| 591 | 545 |
$form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"});
|
| 592 | 546 |
} |
| ... | ... | |
| 598 | 552 |
} |
| 599 | 553 |
|
| 600 | 554 |
if ($form->{"id_$i"}) {
|
| 555 |
my $item_unit; |
|
| 601 | 556 |
|
| 602 |
# get item baseunit |
|
| 603 |
$query = qq|SELECT p.unit |
|
| 604 |
FROM parts p |
|
| 605 |
WHERE p.id = $form->{"id_$i"}|;
|
|
| 606 |
$sth = $dbh->prepare($query); |
|
| 607 |
$sth->execute || $form->dberror($query); |
|
| 608 |
|
|
| 609 |
my ($item_unit) = $sth->fetchrow_array(); |
|
| 610 |
$sth->finish; |
|
| 611 |
|
|
| 612 |
if ($form->{"inventory_accno_$i"}) {
|
|
| 613 |
if (defined($part_units->{$item_unit}->{factor}) && $part_units->{$item_unit}->{factor} ne '' && $part_units->{$item_unit}->{factor} ne '0') {
|
|
| 614 |
$basefactor = $part_units->{$form->{"unit_$i"}}->{factor} / $part_units->{$item_unit}->{factor};
|
|
| 615 |
} else {
|
|
| 616 |
$basefactor = 1; |
|
| 617 |
} |
|
| 618 |
$baseqty = $form->{"qty_$i"} * $basefactor;
|
|
| 557 |
if (defined($baseunits{$form->{"id_$i"}})) {
|
|
| 558 |
$item_unit = $baseunits{$form->{"id_$i"}};
|
|
| 619 | 559 |
} else {
|
| 620 |
if (defined($service_units->{$item_unit}->{factor}) && $service_units->{$item_unit}->{factor} ne '' && $service_units->{$item_unit}->{factor} ne '0') {
|
|
| 621 |
$basefactor = $service_units->{$form->{"unit_$i"}}->{factor} / $service_units->{$item_unit}->{factor};
|
|
| 622 |
} else {
|
|
| 623 |
$basefactor = 1; |
|
| 624 |
} |
|
| 625 |
$baseqty = $form->{"qty_$i"} * $basefactor;
|
|
| 560 |
# get item baseunit |
|
| 561 |
$query = qq|SELECT unit FROM parts WHERE id = ?|; |
|
| 562 |
($item_unit) = selectrow_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
|
|
| 563 |
$baseunits{$form->{"id_$i"}} = $item_unit;
|
|
| 626 | 564 |
} |
| 627 | 565 |
|
| 628 |
map { $form->{"${_}_$i"} =~ s/\'/\'\'/g }
|
|
| 629 |
(qw(partnumber description unit)); |
|
| 566 |
if (defined($all_units->{$item_unit}->{factor})
|
|
| 567 |
&& ($all_units->{$item_unit}->{factor} ne '')
|
|
| 568 |
&& ($all_units->{$item_unit}->{factor} != 0)) {
|
|
| 569 |
$basefactor = $all_units->{$form->{"unit_$i"}}->{factor} / $all_units->{$item_unit}->{factor};
|
|
| 570 |
} else {
|
|
| 571 |
$basefactor = 1; |
|
| 572 |
} |
|
| 573 |
$baseqty = $form->{"qty_$i"} * $basefactor;
|
|
| 630 | 574 |
|
| 631 | 575 |
# undo discount formatting |
| 632 | 576 |
$form->{"discount_$i"} =
|
| ... | ... | |
| 650 | 594 |
$form->{"sellprice_$i"} = $fxsellprice - $discount;
|
| 651 | 595 |
|
| 652 | 596 |
# add tax rates |
| 653 |
map { $taxrate += $form->{"${_}_rate"} } split / /,
|
|
| 654 |
$form->{"taxaccounts_$i"};
|
|
| 597 |
map({ $taxrate += $form->{"${_}_rate"} } split(/ /,
|
|
| 598 |
$form->{"taxaccounts_$i"}));
|
|
| 655 | 599 |
|
| 656 | 600 |
# round linetotal to 2 decimal places |
| 657 | 601 |
$linetotal = |
| ... | ... | |
| 671 | 615 |
map {
|
| 672 | 616 |
$form->{amount}{ $form->{id} }{$_} +=
|
| 673 | 617 |
$taxamount * $form->{"${_}_rate"} / $taxrate
|
| 674 |
} split / /, $form->{"taxaccounts_$i"};
|
|
| 618 |
} split(/ /, $form->{"taxaccounts_$i"});
|
|
| 675 | 619 |
} |
| 676 | 620 |
|
| 677 | 621 |
# add amount to income, $form->{amount}{trans_id}{accno}
|
| ... | ... | |
| 703 | 647 |
if ($form->{"assembly_$i"}) {
|
| 704 | 648 |
|
| 705 | 649 |
# do not update if assembly consists of all services |
| 706 |
$query = qq|SELECT sum(p.inventory_accno_id)
|
|
| 707 |
FROM parts p
|
|
| 708 |
JOIN assembly a ON (a.parts_id = p.id)
|
|
| 709 |
WHERE a.id = $form->{"id_$i"}|;
|
|
| 710 |
$sth = $dbh->prepare($query);
|
|
| 711 |
$sth->execute || $form->dberror($query);
|
|
| 650 |
$query = |
|
| 651 |
qq|SELECT sum(p.inventory_accno_id)
|
|
| 652 |
FROM parts p
|
|
| 653 |
JOIN assembly a ON (a.parts_id = p.id)
|
|
| 654 |
WHERE a.id = ?|;
|
|
| 655 |
$sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id_$i"}));
|
|
| 712 | 656 |
|
| 713 | 657 |
if ($sth->fetchrow_array) {
|
| 714 | 658 |
$form->update_balance($dbh, "parts", "onhand", qq|id = ?|, |
| ... | ... | |
| 728 | 672 |
} |
| 729 | 673 |
} |
| 730 | 674 |
|
| 731 |
$deliverydate = |
|
| 732 |
($form->{"deliverydate_$i"})
|
|
| 733 |
? qq|'$form->{"deliverydate_$i"}'|
|
|
| 734 |
: "NULL"; |
|
| 735 |
|
|
| 736 | 675 |
# get pricegroup_id and save it |
| 737 |
($null, my $pricegroup_id) = split /--/, $form->{"sellprice_pg_$i"};
|
|
| 676 |
($null, my $pricegroup_id) = split(/--/, $form->{"sellprice_pg_$i"});
|
|
| 738 | 677 |
$pricegroup_id *= 1; |
| 739 | 678 |
my $subtotal = $form->{"subtotal_$i"} * 1;
|
| 740 | 679 |
|
| 741 | 680 |
# save detail record in invoice table |
| 742 |
$query = qq|INSERT INTO invoice (trans_id, parts_id, description,longdescription, qty, |
|
| 743 |
sellprice, fxsellprice, discount, allocated, assemblyitem, |
|
| 744 |
unit, deliverydate, project_id, serialnumber, pricegroup_id, |
|
| 745 |
ordnumber, transdate, cusordnumber, base_qty, subtotal) |
|
| 746 |
VALUES ($form->{id}, $form->{"id_$i"},
|
|
| 747 |
'$form->{"description_$i"}', '$form->{"longdescription_$i"}', $form->{"qty_$i"},
|
|
| 748 |
$form->{"sellprice_$i"}, $fxsellprice,
|
|
| 749 |
$form->{"discount_$i"}, $allocated, 'f',
|
|
| 750 |
'$form->{"unit_$i"}', $deliverydate, | . conv_i($form->{"project_id_$i"}, 'NULL') . qq|,
|
|
| 751 |
'$form->{"serialnumber_$i"}', '$pricegroup_id',
|
|
| 752 |
'$form->{"ordnumber_$i"}', '$form->{"transdate_$i"}', '$form->{"cusordnumber_$i"}', $baseqty, '$subtotal')|;
|
|
| 753 |
$dbh->do($query) || $form->dberror($query); |
|
| 754 |
|
|
| 755 |
if ($form->{lizenzen}) {
|
|
| 756 |
if ($form->{"licensenumber_$i"}) {
|
|
| 757 |
$query = |
|
| 758 |
qq|SELECT i.id FROM invoice i WHERE i.trans_id=$form->{id} ORDER BY i.oid DESC LIMIT 1|;
|
|
| 759 |
$sth = $dbh->prepare($query); |
|
| 760 |
$sth->execute || $form->dberror($query); |
|
| 761 |
|
|
| 762 |
($invoice_row_id) = $sth->fetchrow_array; |
|
| 763 |
$sth->finish; |
|
| 764 |
|
|
| 765 |
$query = |
|
| 766 |
qq|INSERT INTO licenseinvoice (trans_id, license_id) VALUES ($invoice_row_id, $form->{"licensenumber_$i"})|;
|
|
| 767 |
$dbh->do($query) || $form->dberror($query); |
|
| 768 |
} |
|
| 681 |
$query = |
|
| 682 |
qq|INSERT INTO invoice (trans_id, parts_id, description, longdescription, qty, |
|
| 683 |
sellprice, fxsellprice, discount, allocated, assemblyitem, |
|
| 684 |
unit, deliverydate, project_id, serialnumber, pricegroup_id, |
|
| 685 |
ordnumber, transdate, cusordnumber, base_qty, subtotal) |
|
| 686 |
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)|; |
|
| 687 |
|
|
| 688 |
@values = (conv_i($form->{id}), conv_i($form->{"id_$i"}),
|
|
| 689 |
$form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"},
|
|
| 690 |
$form->{"sellprice_$i"}, $fxsellprice,
|
|
| 691 |
$form->{"discount_$i"}, $allocated, 'f',
|
|
| 692 |
$form->{"unit_$i"}, conv_date($form->{"deliverydate_$i"}), conv_i($form->{"project_id_$i"}),
|
|
| 693 |
$form->{"serialnumber_$i"}, conv_i($pricegroup_id),
|
|
| 694 |
$form->{"ordnumber_$i"}, conv_date($form->{"transdate_$i"}),
|
|
| 695 |
$form->{"cusordnumber_$i"}, $baseqty, $subtotal);
|
|
| 696 |
do_query($form, $dbh, $query, @values); |
|
| 697 |
|
|
| 698 |
if ($form->{lizenzen} && $form->{"licensenumber_$i"}) {
|
|
| 699 |
$query = |
|
| 700 |
qq|INSERT INTO licenseinvoice (trans_id, license_id) |
|
| 701 |
VALUES ((SELECT id FROM invoice WHERE trans_id = ? ORDER BY oid DESC LIMIT 1), ?)|; |
|
| 702 |
@values = (conv_i($form->{"id"}), conv_i($form->{"licensenumber_$i"}));
|
|
| 703 |
do_query($form, $dbh, $query, @values); |
|
| 769 | 704 |
} |
| 770 |
|
|
| 771 | 705 |
} |
| 772 | 706 |
} |
| 773 | 707 |
|
| ... | ... | |
| 795 | 729 |
$diff += $amount - $netamount * $form->{exchangerate};
|
| 796 | 730 |
$netamount = $amount; |
| 797 | 731 |
|
| 798 |
foreach my $item (split / /, $form->{taxaccounts}) {
|
|
| 732 |
foreach my $item (split(/ /, $form->{taxaccounts})) {
|
|
| 799 | 733 |
$amount = $form->{amount}{ $form->{id} }{$item} * $form->{exchangerate};
|
| 800 | 734 |
$form->{amount}{ $form->{id} }{$item} = $form->round_amount($amount, 2);
|
| 801 | 735 |
$tax += $form->{amount}{ $form->{id} }{$item};
|
| ... | ... | |
| 812 | 746 |
$amount = $form->round_amount($netamount * $form->{exchangerate}, 2);
|
| 813 | 747 |
$diff = $amount - $netamount * $form->{exchangerate};
|
| 814 | 748 |
$netamount = $amount; |
| 815 |
foreach my $item (split / /, $form->{taxaccounts}) {
|
|
| 749 |
foreach my $item (split(/ /, $form->{taxaccounts})) {
|
|
| 816 | 750 |
$form->{amount}{ $form->{id} }{$item} =
|
| 817 | 751 |
$form->round_amount($form->{amount}{ $form->{id} }{$item}, 2);
|
| 818 | 752 |
$amount = |
| ... | ... | |
| 850 | 784 |
$form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
|
| 851 | 785 |
) != 0 |
| 852 | 786 |
) {
|
| 853 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, |
|
| 854 |
transdate, taxkey, project_id) |
|
| 855 |
VALUES ($trans_id, (SELECT c.id FROM chart c |
|
| 856 |
WHERE c.accno = '$accno'), |
|
| 857 |
$form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
|
|
| 858 |
(SELECT taxkey_id FROM chart WHERE accno = '$accno'), ?)|; |
|
| 859 |
do_query($form, $dbh, $query, $project_id); |
|
| 787 |
$query = |
|
| 788 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) |
|
| 789 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
|
| 790 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 791 |
@values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
|
|
| 792 |
do_query($form, $dbh, $query, @values); |
|
| 860 | 793 |
$form->{amount}{$trans_id}{$accno} = 0;
|
| 861 | 794 |
} |
| 862 | 795 |
} |
| ... | ... | |
| 867 | 800 |
$form->round_amount($form->{amount}{$trans_id}{$accno}, 2)
|
| 868 | 801 |
) != 0 |
| 869 | 802 |
) {
|
| 870 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, |
|
| 871 |
transdate, taxkey, project_id) |
|
| 872 |
VALUES ($trans_id, (SELECT id FROM chart |
|
| 873 |
WHERE accno = '$accno'), |
|
| 874 |
$form->{amount}{$trans_id}{$accno}, '$form->{invdate}',
|
|
| 875 |
(SELECT taxkey_id FROM chart WHERE accno = '$accno'), ?)|; |
|
| 876 |
do_query($form, $dbh, $query, $project_id); |
|
| 803 |
$query = |
|
| 804 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) |
|
| 805 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
|
| 806 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 807 |
@values = (conv_i($trans_id), $accno, $form->{amount}{$trans_id}{$accno}, conv_date($form->{invdate}), $accno, conv_i($project_id));
|
|
| 808 |
do_query($form, $dbh, $query, @values); |
|
| 877 | 809 |
} |
| 878 | 810 |
} |
| 879 | 811 |
} |
| ... | ... | |
| 887 | 819 |
} |
| 888 | 820 |
} |
| 889 | 821 |
|
| 890 |
# force AR entry if 0 |
|
| 891 |
# $form->{amount}{$form->{id}}{$form->{AR}} = 1 if ($form->{amount}{$form->{id}}{$form->{AR}} == 0);
|
|
| 892 |
|
|
| 893 | 822 |
# record payments and offsetting AR |
| 894 | 823 |
if (!$form->{storno}) {
|
| 895 | 824 |
for my $i (1 .. $form->{paidaccounts}) {
|
| 896 |
|
|
| 897 |
if ($form->{"paid_$i"} != 0) {
|
|
| 898 |
my ($accno) = split /--/, $form->{"AR_paid_$i"};
|
|
| 899 |
$form->{"datepaid_$i"} = $form->{invdate}
|
|
| 900 |
unless ($form->{"datepaid_$i"});
|
|
| 901 |
$form->{datepaid} = $form->{"datepaid_$i"};
|
|
| 902 |
|
|
| 903 |
$exchangerate = 0; |
|
| 904 |
|
|
| 905 |
if ($form->{currency} eq $form->{defaultcurrency}) {
|
|
| 906 |
$form->{"exchangerate_$i"} = 1;
|
|
| 907 |
} else {
|
|
| 908 |
$exchangerate =
|
|
| 909 |
$form->check_exchangerate($myconfig, $form->{currency},
|
|
| 910 |
$form->{"datepaid_$i"}, 'buy');
|
|
| 911 |
|
|
| 912 |
$form->{"exchangerate_$i"} =
|
|
| 913 |
($exchangerate)
|
|
| 914 |
? $exchangerate
|
|
| 825 |
|
|
| 826 |
next if ($form->{"paid_$i"} == 0);
|
|
| 827 |
|
|
| 828 |
my ($accno) = split(/--/, $form->{"AR_paid_$i"});
|
|
| 829 |
$form->{"datepaid_$i"} = $form->{invdate}
|
|
| 830 |
unless ($form->{"datepaid_$i"});
|
|
| 831 |
$form->{datepaid} = $form->{"datepaid_$i"};
|
|
| 832 |
|
|
| 833 |
$exchangerate = 0; |
|
| 834 |
|
|
| 835 |
if ($form->{currency} eq $form->{defaultcurrency}) {
|
|
| 836 |
$form->{"exchangerate_$i"} = 1;
|
|
| 837 |
} else {
|
|
| 838 |
$exchangerate =
|
|
| 839 |
$form->check_exchangerate($myconfig, $form->{currency},
|
|
| 840 |
$form->{"datepaid_$i"}, 'buy');
|
|
| 841 |
|
|
| 842 |
$form->{"exchangerate_$i"} =
|
|
| 843 |
$exchangerate ? $exchangerate
|
|
| 915 | 844 |
: $form->parse_amount($myconfig, $form->{"exchangerate_$i"});
|
| 916 |
} |
|
| 917 |
|
|
| 918 |
# record AR |
|
| 919 |
$amount = |
|
| 920 |
$form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff,
|
|
| 921 |
2); |
|
| 922 |
|
|
| 923 |
if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
|
|
| 924 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, |
|
| 925 |
transdate, taxkey, project_id) |
|
| 926 |
VALUES ($form->{id}, (SELECT c.id FROM chart c
|
|
| 927 |
WHERE c.accno = ?), |
|
| 928 |
$amount, '$form->{"datepaid_$i"}',
|
|
| 929 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 930 |
do_query($form, $dbh, $query, $form->{AR}, $form->{AR}, $project_id);
|
|
| 931 |
} |
|
| 932 |
|
|
| 933 |
# record payment |
|
| 934 |
$form->{"paid_$i"} *= -1;
|
|
| 935 |
|
|
| 936 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, |
|
| 937 |
source, memo, taxkey, project_id) |
|
| 938 |
VALUES ($form->{id}, (SELECT c.id FROM chart c
|
|
| 939 |
WHERE c.accno = ?), |
|
| 940 |
$form->{"paid_$i"}, '$form->{"datepaid_$i"}',
|
|
| 941 |
'$form->{"source_$i"}', '$form->{"memo_$i"}',
|
|
| 942 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 943 |
do_query($form, $dbh, $query, $accno, $accno, $project_id); |
|
| 944 |
|
|
| 945 |
# exchangerate difference |
|
| 946 |
$form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
|
|
| 947 |
$form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
|
|
| 948 |
|
|
| 949 |
# gain/loss |
|
| 950 |
$amount = |
|
| 951 |
$form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
|
|
| 952 |
$form->{"exchangerate_$i"};
|
|
| 953 |
if ($amount > 0) {
|
|
| 954 |
$form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
|
|
| 955 |
$amount; |
|
| 956 |
} else {
|
|
| 957 |
$form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
|
|
| 958 |
$amount; |
|
| 959 |
} |
|
| 960 |
|
|
| 961 |
$diff = 0; |
|
| 962 |
|
|
| 963 |
# update exchange rate |
|
| 964 |
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
|
|
| 965 |
$form->update_exchangerate($dbh, $form->{currency},
|
|
| 966 |
$form->{"datepaid_$i"},
|
|
| 967 |
$form->{"exchangerate_$i"}, 0);
|
|
| 968 |
} |
|
| 845 |
} |
|
| 846 |
|
|
| 847 |
# record AR |
|
| 848 |
$amount = $form->round_amount($form->{"paid_$i"} * $form->{exchangerate} + $diff, 2);
|
|
| 849 |
|
|
| 850 |
if ($form->{amount}{ $form->{id} }{ $form->{AR} } != 0) {
|
|
| 851 |
$query = |
|
| 852 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) |
|
| 853 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, |
|
| 854 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 855 |
@values = (conv_i($form->{"id"}), $form->{AR}, $amount, $form->{"datepaid_$i"}, $form->{AR}, $project_id);
|
|
| 856 |
do_query($form, $dbh, $query, @values); |
|
| 857 |
} |
|
| 858 |
|
|
| 859 |
# record payment |
|
| 860 |
$form->{"paid_$i"} *= -1;
|
|
| 861 |
|
|
| 862 |
$query = |
|
| 863 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, taxkey, project_id) |
|
| 864 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, |
|
| 865 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 866 |
@values = (conv_i($form->{"id"}), $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"},
|
|
| 867 |
$form->{"source_$i"}, $form->{"memo_$i"}, $accno, $project_id);
|
|
| 868 |
do_query($form, $dbh, $query, @values); |
|
| 869 |
|
|
| 870 |
# exchangerate difference |
|
| 871 |
$form->{fx}{$accno}{ $form->{"datepaid_$i"} } +=
|
|
| 872 |
$form->{"paid_$i"} * ($form->{"exchangerate_$i"} - 1) + $diff;
|
|
| 873 |
|
|
| 874 |
# gain/loss |
|
| 875 |
$amount = |
|
| 876 |
$form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} *
|
|
| 877 |
$form->{"exchangerate_$i"};
|
|
| 878 |
if ($amount > 0) {
|
|
| 879 |
$form->{fx}{ $form->{fxgain_accno} }{ $form->{"datepaid_$i"} } +=
|
|
| 880 |
$amount; |
|
| 881 |
} else {
|
|
| 882 |
$form->{fx}{ $form->{fxloss_accno} }{ $form->{"datepaid_$i"} } +=
|
|
| 883 |
$amount; |
|
| 884 |
} |
|
| 885 |
|
|
| 886 |
$diff = 0; |
|
| 887 |
|
|
| 888 |
# update exchange rate |
|
| 889 |
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
|
|
| 890 |
$form->update_exchangerate($dbh, $form->{currency},
|
|
| 891 |
$form->{"datepaid_$i"},
|
|
| 892 |
$form->{"exchangerate_$i"}, 0);
|
|
| 969 | 893 |
} |
| 970 | 894 |
} |
| 971 | 895 |
} |
| ... | ... | |
| 979 | 903 |
) != 0 |
| 980 | 904 |
) {
|
| 981 | 905 |
|
| 982 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, |
|
| 983 |
transdate, cleared, fx_transaction, taxkey, project_id) |
|
| 984 |
VALUES ($form->{id},
|
|
| 985 |
(SELECT c.id FROM chart c |
|
| 986 |
WHERE c.accno = ?), |
|
| 987 |
$form->{fx}{$accno}{$transdate}, '$transdate', '0', '1',
|
|
| 988 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 989 |
do_query($form, $dbh, $query, $accno, $accno, $project_id); |
|
| 906 |
$query = |
|
| 907 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, taxkey, project_id) |
|
| 908 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, '0', '1', |
|
| 909 |
(SELECT taxkey_id FROM chart WHERE accno = ?), ?)|; |
|
| 910 |
@values = (conv_i($form->{"id"}), $accno, $form->{fx}{$accno}{$transdate}, conv_date($transdate), $accno, $project_id);
|
|
| 911 |
do_query($form, $dbh, $query, @values); |
|
| 990 | 912 |
} |
| 991 | 913 |
} |
| 992 | 914 |
} |
| ... | ... | |
| 994 | 916 |
$amount = $netamount + $tax; |
| 995 | 917 |
|
| 996 | 918 |
# set values which could be empty to 0 |
| 997 |
$form->{terms} *= 1;
|
|
| 998 |
$form->{taxincluded} *= 1;
|
|
| 999 |
my $datepaid = ($form->{paid}) ? qq|'$form->{datepaid}'| : "NULL";
|
|
| 1000 |
my $duedate = ($form->{duedate}) ? qq|'$form->{duedate}'| : "NULL";
|
|
| 1001 |
my $deliverydate = |
|
| 1002 |
($form->{deliverydate}) ? qq|'$form->{deliverydate}'| : "NULL";
|
|
| 919 |
my $datepaid = conv_date($form->{paid});
|
|
| 920 |
my $duedate = conv_date($form->{duedate});
|
|
| 921 |
$deliverydate = conv_date($form->{deliverydate});
|
|
| 1003 | 922 |
|
| 1004 | 923 |
# fill in subject if there is none |
| 1005 | 924 |
$form->{subject} = qq|$form->{label} $form->{invnumber}|
|
| ... | ... | |
| 1021 | 940 |
|
| 1022 | 941 |
# save AR record |
| 1023 | 942 |
$query = qq|UPDATE ar set |
| 1024 |
invnumber = '$form->{invnumber}',
|
|
| 1025 |
ordnumber = '$form->{ordnumber}',
|
|
| 1026 |
quonumber = '$form->{quonumber}',
|
|
| 1027 |
cusordnumber = '$form->{cusordnumber}',
|
|
| 1028 |
transdate = '$form->{invdate}',
|
|
| 1029 |
orddate = | . conv_dateq($form->{orddate}) . qq|,
|
|
| 1030 |
quodate = | . conv_dateq($form->{quodate}) . qq|,
|
|
| 1031 |
customer_id = $form->{customer_id},
|
|
| 1032 |
amount = $amount, |
|
| 1033 |
netamount = $netamount, |
|
| 1034 |
paid = $form->{paid},
|
|
| 1035 |
datepaid = $datepaid, |
|
| 1036 |
duedate = $duedate, |
|
| 1037 |
deliverydate = $deliverydate, |
|
| 1038 |
invoice = '1', |
|
| 1039 |
shippingpoint = '$form->{shippingpoint}',
|
|
| 1040 |
shipvia = '$form->{shipvia}',
|
|
| 1041 |
terms = $form->{terms},
|
|
| 1042 |
notes = '$form->{notes}',
|
|
| 1043 |
intnotes = '$form->{intnotes}',
|
|
| 1044 |
taxincluded = '$form->{taxincluded}',
|
|
| 1045 |
curr = '$form->{currency}',
|
|
| 1046 |
department_id = $form->{department_id},
|
|
| 1047 |
payment_id = $form->{payment_id},
|
|
| 1048 |
type = '$form->{type}',
|
|
| 1049 |
language_id = $form->{language_id},
|
|
| 1050 |
taxzone_id = $form->{taxzone_id},
|
|
| 1051 |
shipto_id = $form->{shipto_id},
|
|
| 1052 |
delivery_customer_id = $form->{delivery_customer_id},
|
|
| 1053 |
delivery_vendor_id = $form->{delivery_vendor_id},
|
|
| 1054 |
employee_id = $form->{employee_id},
|
|
| 1055 |
salesman_id = | . conv_i($form->{salesman_id}, 'NULL') . qq|,
|
|
| 1056 |
storno = '$form->{storno}',
|
|
| 1057 |
globalproject_id = | . conv_i($form->{"globalproject_id"}, 'NULL') . qq|,
|
|
| 1058 |
cp_id = | . conv_i($form->{"cp_id"}, 'NULL') . qq|
|
|
| 1059 |
WHERE id = $form->{id}
|
|
| 1060 |
|; |
|
| 1061 |
$dbh->do($query) || $form->dberror($query); |
|
| 943 |
invnumber = ?, |
|
| 944 |
ordnumber = ?, |
|
| 945 |
quonumber = ?, |
|
| 946 |
cusordnumber = ?, |
|
| 947 |
transdate = ?, |
|
| 948 |
orddate = ?, |
|
| 949 |
quodate = ?, |
|
| 950 |
customer_id = ?, |
|
| 951 |
amount = ?, |
|
| 952 |
netamount = ?, |
|
| 953 |
paid = ?, |
|
| 954 |
datepaid = ?, |
|
| 955 |
duedate = ?, |
|
| 956 |
deliverydate = ?, |
|
| 957 |
invoice = '1', |
|
| 958 |
shippingpoint = ?, |
|
| 959 |
shipvia = ?, |
|
| 960 |
terms = ?, |
|
| 961 |
notes = ?, |
|
| 962 |
intnotes = ?, |
|
| 963 |
taxincluded = ?, |
|
| 964 |
curr = ?, |
|
| 965 |
department_id = ?, |
|
| 966 |
payment_id = ?, |
|
| 967 |
type = ?, |
|
| 968 |
language_id = ?, |
|
| 969 |
taxzone_id = ?, |
|
| 970 |
shipto_id = ?, |
|
| 971 |
delivery_customer_id = ?, |
|
| 972 |
delivery_vendor_id = ?, |
|
| 973 |
employee_id = ?, |
|
| 974 |
salesman_id = ?, |
|
| 975 |
storno = ?, |
|
| 976 |
globalproject_id = ?, |
|
| 977 |
cp_id = ? |
|
| 978 |
WHERE id = ?|; |
|
| 979 |
@values = ($form->{"invnumber"}, $form->{"ordnumber"}, $form->{"quonumber"}, $form->{"cusordnumber"},
|
|
| 980 |
conv_date($form->{"invdate"}), conv_date($form->{"orddate"}), conv_date($form->{"quodate"}),
|
|
| 981 |
conv_i($form->{"customer_id"}), $amount, $netamount, $form->{"paid"},
|
|
| 982 |
conv_date($form->{"datepaid"}), conv_date($form->{"duedate"}), conv_date($form->{"deliverydate"}),
|
|
| 983 |
$form->{"shippingpoint"}, $form->{"shipvia"}, conv_i($form->{"terms"}),
|
|
| 984 |
$form->{"notes"}, $form->{"intnotes"}, $form->{"taxincluded"} ? 't' : 'f',
|
|
| 985 |
$form->{"currency"}, conv_i($form->{"department_id"}), conv_i($form->{"payment_id"}),
|
|
| 986 |
$form->{"type"}, conv_i($form->{"language_id"}), conv_i($form->{"taxzone_id"}),
|
|
| 987 |
conv_i($form->{"shipto_id"}),
|
|
| 988 |
conv_i($form->{"delivery_customer_id"}), conv_i($form->{"delivery_vendor_id"}),
|
|
| 989 |
conv_i($form->{"employee_id"}), conv_i($form->{"salesman_id"}),
|
|
| 990 |
$form->{"storno"} ? 't' : 'f', conv_i($form->{"globalproject_id"}),
|
|
| 991 |
conv_i($form->{"cp_id"}),
|
|
| 992 |
conv_i($form->{"id"}));
|
|
| 993 |
do_query($form, $dbh, $query, @values); |
|
| 1062 | 994 |
|
| 1063 | 995 |
if ($form->{storno}) {
|
| 1064 |
$query = qq| update ar set paid=paid+amount where id=$form->{storno_id}|;
|
|
| 1065 |
$dbh->do($query) || $form->dberror($query); |
|
| 1066 |
$query = qq| update ar set storno='$form->{storno}' where id=$form->{storno_id}|;
|
|
| 1067 |
$dbh->do($query) || $form->dberror($query); |
|
| 1068 |
$query = qq? update ar set intnotes='Rechnung storniert am $form->{invdate} ' || intnotes where id=$form->{storno_id}?;
|
|
| 1069 |
$dbh->do($query) || $form->dberror($query); |
|
| 1070 |
|
|
| 1071 |
$query = qq| update ar set paid=amount where id=$form->{id}|;
|
|
| 1072 |
$dbh->do($query) || $form->dberror($query); |
|
| 996 |
$query = |
|
| 997 |
qq!UPDATE ar SET |
|
| 998 |
paid = paid + amount, |
|
| 999 |
storno = 't', |
|
| 1000 |
intnotes = ? || intnotes |
|
| 1001 |
WHERE id = ?!; |
|
| 1002 |
do_query($form, $dbh, $query, "Rechnung storniert am $form->{invdate} ", conv_i($form->{"storno_id"}));
|
|
| 1003 |
do_query($form, $dbh, qq|UPDATE ar SET paid = amount WHERE id = ?|, conv_i($form->{"id"}));
|
|
| 1073 | 1004 |
} |
| 1074 | 1005 |
|
| 1075 |
$form->{pago_total} = $amount;
|
|
| 1076 |
|
|
| 1077 | 1006 |
# add shipto |
| 1078 | 1007 |
$form->{name} = $form->{customer};
|
| 1079 | 1008 |
$form->{name} =~ s/--$form->{customer_id}//;
|
| ... | ... | |
| 1104 | 1033 |
$form->{datepaid} = $form->{invdate};
|
| 1105 | 1034 |
|
| 1106 | 1035 |
# total payments, don't move we need it here |
| 1107 |
for my $i (1 .. $form->{paidaccounts}) {
|
|
| 1036 |
for my $i ( 1 .. $form->{paidaccounts} ) {
|
|
| 1108 | 1037 |
$form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"});
|
| 1109 |
$form->{"paid_$i"} *= -1 if ($form->{type} eq "credit_note");
|
|
| 1038 |
$form->{"paid_$i"} *= -1 if $form->{type} eq "credit_note";
|
|
| 1110 | 1039 |
$form->{"paid"} += $form->{"paid_$i"};
|
| 1111 |
$form->{"datepaid"} = $form->{"datepaid_$i"} if ($form->{"datepaid_$i"});
|
|
| 1040 |
$form->{"datepaid"} = $form->{"datepaid_$i"} if $form->{"datepaid_$i"};
|
|
| 1112 | 1041 |
} |
| 1113 | 1042 |
|
| 1114 | 1043 |
$form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
|
| ... | ... | |
| 1132 | 1061 |
# record AR |
| 1133 | 1062 |
$amount = $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"}, 2);
|
| 1134 | 1063 |
|
| 1135 |
$query = qq|DELETE FROM acc_trans WHERE trans_id = ? AND chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) AND amount = ? AND transdate = ?|; |
|
| 1136 |
do_query($form, $dbh, $query, $form->{id}, $form->{AR}, $amount, $form->{"datepaid_$i"});
|
|
| 1137 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey) |
|
| 1138 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|; |
|
| 1064 |
$query = |
|
| 1065 |
qq|DELETE FROM acc_trans |
|
| 1066 |
WHERE (trans_id = ?) |
|
| 1067 |
AND (chart_id = (SELECT id FROM chart WHERE accno = ?)) |
|
| 1068 |
AND (amount = ?) AND (transdate = ?)|; |
|
| 1069 |
do_query($form, $dbh, $query, $form->{id}, $form->{AR}, $amount, conv_date($form->{"datepaid_$i"}));
|
|
| 1070 |
$query = |
|
| 1071 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey) |
|
| 1072 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, |
|
| 1073 |
(SELECT taxkey_id FROM chart WHERE accno = ?))|; |
|
| 1139 | 1074 |
do_query($form, $dbh, $query, $form->{id}, $form->{AR}, $amount, $form->{"datepaid_$i"}, conv_i($form->{"globalproject_id"}), $accno);
|
| 1140 | 1075 |
|
| 1141 | 1076 |
# record payment |
| 1142 | 1077 |
$form->{"paid_$i"} *= -1;
|
| 1143 | 1078 |
|
| 1144 |
$query = qq|DELETE FROM acc_trans WHERE trans_id = ? AND chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) AND amount = ? AND transdate = ? AND source = ? AND memo = ?|; |
|
| 1079 |
$query = |
|
| 1080 |
qq|DELETE FROM acc_trans |
|
| 1081 |
WHERE (trans_id = ?) |
|
| 1082 |
AND (chart_id = (SELECT id FROM chart WHERE accno = ?)) |
|
| 1083 |
AND (amount = ?) AND (transdate = ?) AND (source = ?) AND (memo = ?)|; |
|
| 1145 | 1084 |
do_query($form, $dbh, $query, $form->{id}, $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, $form->{"source_$i"}, $form->{"memo_$i"});
|
| 1146 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id, taxkey) |
|
| 1147 |
VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|; |
|
| 1148 |
do_query($form, $dbh, $query, $form->{id}, $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, $form->{"source_$i"}, $form->{"memo_$i"},
|
|
| 1085 |
|
|
| 1086 |
$query = |
|
| 1087 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id, taxkey) |
|
| 1088 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, |
|
| 1089 |
(SELECT taxkey_id FROM chart WHERE accno = ?))|; |
|
| 1090 |
do_query($form, $dbh, $query, $form->{id}, $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, $form->{"source_$i"}, $form->{"memo_$i"},
|
|
| 1149 | 1091 |
conv_i($form->{"globalproject_id"}), $accno);
|
| 1150 | 1092 |
|
| 1151 | 1093 |
# gain/loss |
| ... | ... | |
| 1167 | 1109 |
foreach my $transdate (keys %{ $form->{fx}{$accno} }) {
|
| 1168 | 1110 |
|
| 1169 | 1111 |
if ($form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2)) { # '=' is no typo, it's an assignment
|
| 1170 |
$query = qq|DELETE FROM acc_trans WHERE trans_id = ? AND chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?) |
|
| 1171 |
AND amount = ? AND transdate = ? AND cleared = ? AND fx_transaction = ?|; |
|
| 1112 |
$query = |
|
| 1113 |
qq|DELETE FROM acc_trans |
|
| 1114 |
WHERE (trans_id = ?) |
|
| 1115 |
AND (chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?)) |
|
| 1116 |
AND (amount = ?) AND (transdate = ?) AND (cleared = ?) AND (fx_transaction = ?)|; |
|
| 1172 | 1117 |
do_query($form, $dbh, $query, $form->{id}, $accno, $form->{fx}{$accno}{$transdate}, $transdate, 0, 1);
|
| 1173 |
$query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, project_id, taxkey) |
|
| 1174 |
VALUES (?, (SELECT c.id FROM chart c WHERE c.accno = ?), ?, ?, ?, ?, ?, (SELECT taxkey_id FROM chart WHERE accno = ?))|; |
|
| 1118 |
$query = |
|
| 1119 |
qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, project_id, taxkey) |
|
| 1120 |
VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, |
|
| 1121 |
(SELECT taxkey_id FROM chart WHERE accno = ?))|; |
|
| 1175 | 1122 |
do_query($form, $dbh, $query, $form->{id}, $accno, $form->{fx}{$accno}{$transdate}, $transdate, 0, 1, conv_i($form->{"globalproject_id"}), $accno);
|
| 1176 | 1123 |
} |
| 1177 | 1124 |
|
| ... | ... | |
| 1181 | 1128 |
# save AR record |
| 1182 | 1129 |
delete $form->{datepaid} unless $form->{paid};
|
| 1183 | 1130 |
|
| 1184 |
my $query = qq|UPDATE ar set paid = ?, datepaid = ? WHERE id = ?|;
|
|
| 1185 |
do_query($form, $dbh, $query, $form->{paid}, $form->{datepaid}, $form->{id});
|
|
| 1131 |
my $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
|
|
| 1132 |
do_query($form, $dbh, $query, $form->{"paid"}, conv_date($form->{"datepaid"}), conv_i($form->{"id"}));
|
|
| 1186 | 1133 |
|
| 1187 | 1134 |
my $rc = $dbh->commit; |
| 1188 | 1135 |
$dbh->disconnect; |
| ... | ... | |
| 1195 | 1142 |
|
| 1196 | 1143 |
my ($dbh, $form, $id, $totalqty) = @_; |
| 1197 | 1144 |
|
| 1198 |
my $query = qq|SELECT a.parts_id, a.qty, p.assembly, |
|
| 1199 |
p.partnumber, p.description, p.unit, |
|
| 1200 |
p.inventory_accno_id, p.income_accno_id, |
|
| 1201 |
p.expense_accno_id |
|
| 1202 |
FROM assembly a |
|
| 1203 |
JOIN parts p ON (a.parts_id = p.id) |
|
| 1204 |
WHERE a.id = $id|; |
|
| 1205 |
my $sth = $dbh->prepare($query); |
|
| 1206 |
$sth->execute || $form->dberror($query); |
|
| 1145 |
my $query = |
|
| 1146 |
qq|SELECT a.parts_id, a.qty, p.assembly, p.partnumber, p.description, p.unit, |
|
| 1147 |
p.inventory_accno_id, p.income_accno_id, p.expense_accno_id |
|
| 1148 |
FROM assembly a |
|
| 1149 |
JOIN parts p ON (a.parts_id = p.id) |
|
| 1150 |
WHERE (a.id = ?)|; |
|
| 1151 |
my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id)); |
|
| 1207 | 1152 |
|
| 1208 | 1153 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 1209 | 1154 |
|
| ... | ... | |
| 1212 | 1157 |
$ref->{inventory_accno_id} *= 1;
|
| 1213 | 1158 |
$ref->{expense_accno_id} *= 1;
|
| 1214 | 1159 |
|
| 1215 |
map { $ref->{$_} =~ s/\'/\'\'/g } (qw(partnumber description unit));
|
|
| 1216 |
|
|
| 1217 | 1160 |
# multiply by number of assemblies |
| 1218 | 1161 |
$ref->{qty} *= $totalqty;
|
| 1219 | 1162 |
|
| ... | ... | |
| 1227 | 1170 |
} |
| 1228 | 1171 |
|
| 1229 | 1172 |
# save detail record for individual assembly item in invoice table |
| 1230 |
$query = qq|INSERT INTO invoice (trans_id, description, parts_id, qty, |
|
| 1231 |
sellprice, fxsellprice, allocated, assemblyitem, unit) |
|
| 1232 |
VALUES |
|
| 1233 |
($form->{id}, '$ref->{description}',
|
|
| 1234 |
$ref->{parts_id}, $ref->{qty}, 0, 0, $allocated, 't',
|
|
| 1235 |
'$ref->{unit}')|;
|
|
| 1236 |
$dbh->do($query) || $form->dberror($query); |
|
| 1173 |
$query = |
|
| 1174 |
qq|INSERT INTO invoice (trans_id, description, parts_id, qty, sellprice, fxsellprice, allocated, assemblyitem, unit) |
|
| 1175 |
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)|; |
|
| 1176 |
@values = (conv_i($form->{id}), $ref->{description}, conv_i($ref->{parts_id}), $ref->{qty}, 0, 0, $allocated, 't', $ref->{unit});
|
|
| 1177 |
do_query($form, $dbh, $query, @values); |
|
| 1237 | 1178 |
|
| 1238 | 1179 |
} |
| 1239 | 1180 |
|
| ... | ... | |
| 1247 | 1188 |
|
| 1248 | 1189 |
my ($dbh, $form, $id, $totalqty, $basefactor, $row) = @_; |
| 1249 | 1190 |
$form->{taxzone_id} *=1;
|
| 1250 |
my $transdate = ($form->{invdate}) ? "'$form->{invdate}'" : "current_date";
|
|
| 1251 |
my $query = qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, |
|
| 1252 |
c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid, |
|
| 1253 |
c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid, |
|
| 1254 |
c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid |
|
| 1255 |
FROM invoice i, parts p |
|
| 1256 |
LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id) |
|
| 1257 |
LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
|
|
| 1258 |
LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
|
|
| 1259 |
WHERE i.parts_id = p.id |
|
| 1260 |
AND i.parts_id = $id |
|
| 1261 |
AND (i.base_qty + i.allocated) < 0 |
|
| 1262 |
ORDER BY trans_id|; |
|
| 1263 |
my $sth = $dbh->prepare($query); |
|
| 1264 |
$sth->execute || $form->dberror($query); |
|
| 1191 |
my $transdate = $form->{invdate} ? $dbh->quote($form->{invdate}) : "current_date";
|
|
| 1192 |
my $taxzone_id = $form->{"taxzone_id"} * 1;
|
|
| 1193 |
my $query = |
|
| 1194 |
qq|SELECT i.id, i.trans_id, i.base_qty, i.allocated, i.sellprice, |
|
| 1195 |
|
|
| 1196 |
c1.accno AS inventory_accno, |
|
| 1197 |
c1.new_chart_id AS inventory_new_chart, |
|
| 1198 |
date($transdate) - c1.valid_from AS inventory_valid, |
|
| 1199 |
|
|
| 1200 |
c2.accno AS income_accno, |
|
| 1201 |
c2.new_chart_id AS income_new_chart, |
|
| 1202 |
date($transdate) - c2.valid_from AS income_valid, |
|
| 1203 |
|
|
| 1204 |
c3.accno AS expense_accno, |
|
| 1205 |
c3.new_chart_id AS expense_new_chart, |
|
| 1206 |
date($transdate) - c3.valid_from AS expense_valid |
|
| 1207 |
|
|
| 1208 |
FROM invoice i, parts p |
|
| 1209 |
LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c1.id) |
|
| 1210 |
LEFT JOIN chart c2 ON ((SELECT income_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c2.id)
|
|
| 1211 |
LEFT JOIN chart c3 ON ((select expense_accno_id_${taxzone_id} FROM buchungsgruppen WHERE id = p.buchungsgruppen_id) = c3.id)
|
|
| 1212 |
WHERE (i.parts_id = p.id) |
|
| 1213 |
AND (i.parts_id = ?) |
|
| 1214 |
AND ((i.base_qty + i.allocated) < 0) |
|
| 1215 |
ORDER BY trans_id|; |
|
| 1216 |
my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id)); |
|
| 1265 | 1217 |
|
| 1266 | 1218 |
my $allocated = 0; |
| 1267 | 1219 |
my $qty; |
| ... | ... | |
| 1271 | 1223 |
$qty = $totalqty; |
| 1272 | 1224 |
} |
| 1273 | 1225 |
|
| 1274 |
$form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|,
|
|
| 1275 |
$qty); |
|
| 1226 |
$form->update_balance($dbh, "invoice", "allocated", qq|id = $ref->{id}|, $qty);
|
|
| 1276 | 1227 |
|
| 1277 | 1228 |
# total expenses and inventory |
| 1278 | 1229 |
# sellprice is the cost of the item |
| ... | ... | |
| 1290 | 1241 |
} |
| 1291 | 1242 |
|
| 1292 | 1243 |
# add allocated |
| 1293 |
$allocated += -$qty;
|
|
| 1244 |
$allocated -= $qty;
|
|
| 1294 | 1245 |
|
| 1295 | 1246 |
last if (($totalqty -= $qty) <= 0); |
| 1296 | 1247 |
} |
| ... | ... | |
| 1308 | 1259 |
my ($dbh, $form) = @_; |
| 1309 | 1260 |
|
| 1310 | 1261 |
# reverse inventory items |
| 1311 |
my $query = qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, |
|
| 1312 |
p.inventory_accno_id |
|
| 1313 |
FROM invoice i |
|
| 1314 |
JOIN parts p ON (i.parts_id = p.id) |
|
| 1315 |
WHERE i.trans_id = $form->{id}|;
|
|
| 1316 |
my $sth = $dbh->prepare($query); |
|
| 1317 |
$sth->execute || $form->dberror($query); |
|
| 1262 |
my $query = |
|
| 1263 |
qq|SELECT i.id, i.parts_id, i.qty, i.assemblyitem, p.assembly, p.inventory_accno_id |
|
| 1264 |
FROM invoice i |
|
| 1265 |
JOIN parts p ON (i.parts_id = p.id) |
|
| 1266 |
WHERE i.trans_id = ?|; |
|
| 1267 |
my $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{"id"}));
|
|
| 1318 | 1268 |
|
| 1319 | 1269 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 1320 | 1270 |
|
| ... | ... | |
| 1324 | 1274 |
if (!$ref->{assemblyitem}) {
|
| 1325 | 1275 |
|
| 1326 | 1276 |
# adjust onhand in parts table |
| 1327 |
$form->update_balance($dbh, "parts", "onhand", |
|
| 1328 |
qq|id = $ref->{parts_id}|,
|
|
| 1329 |
$ref->{qty});
|
|
| 1277 |
$form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|, $ref->{qty});
|
|
| 1330 | 1278 |
} |
| 1331 | 1279 |
|
| 1332 | 1280 |
# loop if it is an assembly |
| 1333 | 1281 |
next if ($ref->{assembly});
|
| 1334 | 1282 |
|
| 1335 | 1283 |
# de-allocated purchases |
| 1336 |
$query = qq|SELECT i.id, i.trans_id, i.allocated |
|
| 1337 |
FROM invoice i |
|
| 1338 |
WHERE i.parts_id = $ref->{parts_id}
|
|
| 1339 |
AND i.allocated > 0 |
|
| 1340 |
ORDER BY i.trans_id DESC|; |
|
| 1341 |
my $sth = $dbh->prepare($query); |
|
| 1342 |
$sth->execute || $form->dberror($query); |
|
| 1343 |
|
|
| 1344 |
while (my $inhref = $sth->fetchrow_hashref(NAME_lc)) {
|
|
| 1284 |
$query = |
|
| 1285 |
qq|SELECT i.id, i.trans_id, i.allocated |
|
| 1286 |
FROM invoice i |
|
| 1287 |
WHERE (i.parts_id = ?) AND (i.allocated > 0) |
|
| 1288 |
ORDER BY i.trans_id DESC|; |
|
| 1289 |
my $sth2 = prepare_execute_query($form, $dbh, $query, conv_i($ref->{"parts_id"}));
|
|
| 1290 |
|
|
| 1291 |
while (my $inhref = $sth2->fetchrow_hashref(NAME_lc)) {
|
|
| 1345 | 1292 |
$qty = $ref->{qty};
|
| 1346 | 1293 |
if (($ref->{qty} - $inhref->{allocated}) > 0) {
|
| 1347 | 1294 |
$qty = $inhref->{allocated};
|
| 1348 | 1295 |
} |
| 1349 | 1296 |
|
| 1350 | 1297 |
# update invoice |
| 1351 |
$form->update_balance($dbh, "invoice", "allocated", |
|
| 1352 |
qq|id = $inhref->{id}|,
|
|
| 1353 |
$qty * -1); |
|
| 1298 |
$form->update_balance($dbh, "invoice", "allocated", qq|id = $inhref->{id}|, $qty * -1);
|
|
| 1354 | 1299 |
|
| 1355 | 1300 |
last if (($ref->{qty} -= $qty) <= 0);
|
| 1356 | 1301 |
} |
| 1357 |
$sth->finish; |
|
| 1302 |
$sth2->finish;
|
|
| 1358 | 1303 |
} |
| 1359 | 1304 |
} |
| 1360 | 1305 |
|
| 1361 | 1306 |
$sth->finish; |
| 1362 | 1307 |
|
| 1363 | 1308 |
# delete acc_trans |
| 1364 |
$query = qq|DELETE FROM acc_trans |
|
| 1365 |
WHERE trans_id = $form->{id}|;
|
|
| 1366 |
$dbh->do($query) || $form->dberror($query); |
|
| 1367 |
|
|
| 1368 |
# delete invoice entries |
|
| 1369 |
$query = qq|DELETE FROM invoice |
|
| 1370 |
WHERE trans_id = $form->{id}|;
|
|
| 1371 |
$dbh->do($query) || $form->dberror($query); |
|
| 1309 |
@values = (conv_i($form->{id}));
|
|
| 1310 |
do_query($form, $dbh, qq|DELETE FROM acc_trans WHERE trans_id = ?|, @values); |
|
| 1311 |
do_query($form, $dbh, qq|DELETE FROM invoice WHERE trans_id = ?|, @values); |
|
| 1372 | 1312 |
|
| 1373 | 1313 |
if ($form->{lizenzen}) {
|
| 1374 |
$query = qq|DELETE FROM licenseinvoice |
|
| 1375 |
WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = $form->{id})|;
|
|
| 1376 |
$dbh->do($query) || $form->dberror($query); |
|
| 1314 |
$query = |
|
| 1315 |
qq|DELETE FROM licenseinvoice |
|
| 1316 |
WHERE trans_id in (SELECT id FROM invoice WHERE trans_id = ?)|; |
|
| 1317 |
do_query($form, $dbh, $query, @values); |
|
| 1377 | 1318 |
} |
| 1378 | 1319 |
|
| 1379 |
$query = qq|DELETE FROM shipto |
|
| 1380 |
WHERE trans_id = $form->{id} AND module = 'AR'|;
|
|
| 1381 |
$dbh->do($query) || $form->dberror($query); |
|
| 1320 |
do_query($form, $dbh, qq|DELETE FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|, @values); |
|
| 1382 | 1321 |
|
| 1383 | 1322 |
$main::lxdebug->leave_sub(); |
| 1384 | 1323 |
} |
| ... | ... | |
| 1393 | 1332 |
|
| 1394 | 1333 |
&reverse_invoice($dbh, $form); |
| 1395 | 1334 |
|
| 1335 |
my @values = (conv_i($form->{id}));
|
|
| 1336 |
|
|
| 1396 | 1337 |
# delete AR record |
| 1397 |
my $query = qq|DELETE FROM ar |
|
| 1398 |
WHERE id = $form->{id}|;
|
|
| 1399 |
$dbh->do($query) || $form->dberror($query); |
|
| 1338 |
do_query($form, $dbh, qq|DELETE FROM ar WHERE id = ?|, @values); |
|
| 1400 | 1339 |
|
| 1401 | 1340 |
# delete spool files |
| 1402 |
$query = qq|SELECT s.spoolfile FROM status s |
|
| 1403 |
WHERE s.trans_id = $form->{id}|;
|
|
| 1404 |
my $sth = $dbh->prepare($query); |
|
| 1405 |
$sth->execute || $self->dberror($query); |
|
| 1406 |
|
|
| 1407 |
my $spoolfile; |
|
| 1408 |
my @spoolfiles = (); |
|
| 1409 |
|
|
| 1410 |
while (($spoolfile) = $sth->fetchrow_array) {
|
|
| 1411 |
push @spoolfiles, $spoolfile; |
|
| 1412 |
} |
|
| 1413 |
$sth->finish; |
|
| 1341 |
my @spoolfiles = selectall_array_query($form, $dbh, qq|SELECT spoolfile FROM status WHERE trans_id = ?|, @values); |
|
| 1414 | 1342 |
|
| 1415 | 1343 |
# delete status entries |
| 1416 |
$query = qq|DELETE FROM status |
|
| 1417 |
WHERE trans_id = $form->{id}|;
|
|
| 1418 |
$dbh->do($query) || $form->dberror($query); |
|
| 1344 |
do_query($form, $dbh, qq|DELETE FROM status WHERE trans_id = ?|, @values); |
|
| 1419 | 1345 |
|
| 1420 | 1346 |
my $rc = $dbh->commit; |
| 1421 | 1347 |
$dbh->disconnect; |
| 1422 | 1348 |
|
| 1423 | 1349 |
if ($rc) {
|
| 1424 |
foreach $spoolfile (@spoolfiles) {
|
|
| 1425 |
unlink "$spool/$spoolfile" if $spoolfile; |
|
| 1426 |
} |
|
| 1350 |
map { unlink "$spool/$_" if -f "$spool/$_"; } @{ $spoolfiles };
|
|
| 1427 | 1351 |
} |
| 1428 | 1352 |
|
| 1429 | 1353 |
$main::lxdebug->leave_sub(); |
| ... | ... | |
| 1439 | 1363 |
# connect to database |
| 1440 | 1364 |
my $dbh = $form->dbconnect_noauto($myconfig); |
| 1441 | 1365 |
|
| 1442 |
my $query; |
|
| 1443 |
|
|
| 1444 |
if ($form->{id}) {
|
|
| 1445 |
|
|
| 1446 |
# get default accounts and last invoice number |
|
| 1447 |
$query = qq|SELECT (SELECT c.accno FROM chart c |
|
| 1448 |
WHERE d.inventory_accno_id = c.id) AS inventory_accno, |
|
| 1449 |
(SELECT c.accno FROM chart c |
|
| 1450 |
WHERE d.income_accno_id = c.id) AS income_accno, |
|
| 1451 |
(SELECT c.accno FROM chart c |
|
| 1452 |
WHERE d.expense_accno_id = c.id) AS expense_accno, |
|
| 1453 |
(SELECT c.accno FROM chart c |
|
| 1454 |
WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, |
|
| 1455 |
(SELECT c.accno FROM chart c |
|
| 1456 |
WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, |
|
| 1457 |
d.curr AS currencies |
|
| 1458 |
FROM defaults d|; |
|
| 1459 |
} else {
|
|
| 1460 |
$query = qq|SELECT (SELECT c.accno FROM chart c |
|
| 1461 |
WHERE d.inventory_accno_id = c.id) AS inventory_accno, |
|
| 1462 |
(SELECT c.accno FROM chart c |
|
| 1463 |
WHERE d.income_accno_id = c.id) AS income_accno, |
|
| 1464 |
(SELECT c.accno FROM chart c |
|
| 1465 |
WHERE d.expense_accno_id = c.id) AS expense_accno, |
|
| 1466 |
(SELECT c.accno FROM chart c |
|
| 1467 |
WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, |
|
| 1468 |
(SELECT c.accno FROM chart c |
|
| 1469 |
WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, |
|
| 1470 |
d.curr AS currencies, current_date AS invdate |
|
| 1471 |
FROM defaults d|; |
|
| 1472 |
} |
|
| 1473 |
my $sth = $dbh->prepare($query); |
|
| 1474 |
$sth->execute || $form->dberror($query); |
|
| 1475 |
|
|
| 1476 |
my $ref = $sth->fetchrow_hashref(NAME_lc); |
|
| 1477 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
|
| 1478 |
$sth->finish; |
|
| 1366 |
my ($sth, $ref, $query); |
|
| 1367 |
|
|
| 1368 |
my $query_transdate = ", current_date AS invdate" if !$form->{id};
|
|
| 1369 |
|
|
| 1370 |
$query = |
|
| 1371 |
qq|SELECT |
|
| 1372 |
(SELECT c.accno FROM chart c |
|
| 1373 |
WHERE d.inventory_accno_id = c.id) AS inventory_accno, |
|
| 1374 |
(SELECT c.accno FROM chart c |
|
| 1375 |
WHERE d.income_accno_id = c.id) AS income_accno, |
|
| 1376 |
(SELECT c.accno FROM chart c |
|
| 1377 |
WHERE d.expense_accno_id = c.id) AS expense_accno, |
|
| 1378 |
(SELECT c.accno FROM chart c |
|
| 1379 |
WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, |
|
| 1380 |
(SELECT c.accno FROM chart c |
|
| 1381 |
WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, |
|
| 1382 |
d.curr AS currencies |
|
| 1383 |
${query_transdate}
|
|
| 1384 |
FROM defaults d|; |
|
| 1385 |
|
|
| 1386 |
$ref = selectfirst_hashref_query($form, $dbh, $query); |
|
| 1387 |
map { $form->{$_} = $ref->{$_} } keys %{ $ref };
|
|
| 1479 | 1388 |
|
| 1480 | 1389 |
if ($form->{id}) {
|
| 1390 |
my $id = conv_i($form->{id});
|
|
| 1481 | 1391 |
|
| 1482 | 1392 |
# retrieve invoice |
| 1483 |
$query = qq|SELECT a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber, |
|
| 1484 |
a.orddate, a.quodate, a.globalproject_id, |
|
| 1485 |
a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate, |
|
| 1486 |
a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id, |
|
| 1487 |
a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id, |
|
| 1488 |
a.employee_id, e.name AS employee, a.salesman_id, a.payment_id, a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type |
|
| 1489 |
FROM ar a |
|
| 1490 |
LEFT JOIN employee e ON (e.id = a.employee_id) |
|
| 1491 |
WHERE a.id = $form->{id}|;
|
|
| 1492 |
$sth = $dbh->prepare($query); |
|
| 1493 |
$sth->execute || $form->dberror($query); |
|
| 1393 |
$query = |
|
| 1394 |
qq|SELECT |
|
| 1395 |
a.invnumber, a.ordnumber, a.quonumber, a.cusordnumber, |
|
| 1396 |
a.orddate, a.quodate, a.globalproject_id, |
|
| 1397 |
a.transdate AS invdate, a.deliverydate, a.paid, a.storno, a.gldate, |
|
| 1398 |
a.shippingpoint, a.shipvia, a.terms, a.notes, a.intnotes, a.taxzone_id, |
|
| 1399 |
a.duedate, a.taxincluded, a.curr AS currency, a.shipto_id, a.cp_id, |
|
| 1400 |
a.employee_id, a.salesman_id, a.payment_id, |
|
| 1401 |
a.language_id, a.delivery_customer_id, a.delivery_vendor_id, a.type, |
|
| 1402 |
e.name AS employee |
|
| 1403 |
FROM ar a |
|
| 1404 |
LEFT JOIN employee e ON (e.id = a.employee_id) |
|
| 1405 |
WHERE a.id = ?|; |
|
| 1406 |
$ref = selectfirst_hashref_query($form, $dbh, $query, $id); |
|
| 1407 |
map { $form->{$_} = $ref->{$_} } keys %{ $ref };
|
|
| 1494 | 1408 |
|
| 1495 |
$ref = $sth->fetchrow_hashref(NAME_lc); |
|
| 1496 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
|
| 1497 |
$sth->finish; |
|
| 1498 | 1409 |
|
| 1499 | 1410 |
$form->{exchangerate} =
|
| 1500 |
$form->get_exchangerate($dbh, $form->{currency}, $form->{invdate},
|
|
| 1501 |
"buy"); |
|
| 1502 |
# get shipto |
|
| 1503 |
$query = qq|SELECT s.* FROM shipto s |
|
| 1504 |
WHERE s.trans_id = $form->{id} AND s.module = 'AR'|;
|
|
| 1505 |
$sth = $dbh->prepare($query); |
|
| 1506 |
$sth->execute || $form->dberror($query); |
|
| 1411 |
$form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy");
|
|
| 1507 | 1412 |
|
| 1508 |
$ref = $sth->fetchrow_hashref(NAME_lc); |
|
| 1509 |
delete($ref->{id});
|
|
| 1510 |
map { $form->{$_} = $ref->{$_} } keys %$ref;
|
|
| 1511 |
$sth->finish; |
|
| 1512 |
|
|
| 1513 |
if ($form->{delivery_customer_id}) {
|
|
| 1514 |
$query = qq|SELECT name FROM customer WHERE id=$form->{delivery_customer_id}|;
|
|
| 1515 |
$sth = $dbh->prepare($query); |
|
| 1516 |
$sth->execute || $form->dberror($query); |
|
| 1517 |
($form->{delivery_customer_string}) = $sth->fetchrow_array();
|
|
| 1518 |
$sth->finish; |
|
| 1519 |
} |
|
| 1520 |
|
|
| 1521 |
if ($form->{delivery_vendor_id}) {
|
|
| 1522 |
$query = qq|SELECT name FROM customer WHERE id=$form->{delivery_vendor_id}|;
|
|
| 1523 |
$sth = $dbh->prepare($query); |
|
| 1524 |
$sth->execute || $form->dberror($query); |
|
| 1525 |
($form->{delivery_vendor_string}) = $sth->fetchrow_array();
|
|
| 1526 |
$sth->finish; |
|
| 1413 |
# get shipto |
|
| 1414 |
$query = qq|SELECT * FROM shipto WHERE (trans_id = ?) AND (module = 'AR')|; |
|
| 1415 |
$ref = selectfirst_hashref_query($form, $dbh, $query, $id); |
|
| 1416 |
delete $ref->{id};
|
|
| 1417 |
map { $form->{$_} = $ref->{$_} } keys %{ $ref };
|
|
| 1418 |
|
|
| 1419 |
foreach my $vc (qw(customer vendor)) {
|
|
| 1420 |
next if !$form->{"delivery_${vc}_id"};
|
|
| 1421 |
($form->{"delivery_${vc}_string"})
|
|
| 1422 |
= selectrow_query($form, $dbh, qq|SELECT name FROM customer WHERE id = ?|, $id); |
|
| 1527 | 1423 |
} |
| 1528 | 1424 |
|
| 1529 | 1425 |
# get printed, emailed |
| 1530 |
$query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname
|
|
| 1531 |
FROM status s
|
|
| 1532 |
WHERE s.trans_id = $form->{id}|;
|
|
| 1533 |
$sth = $dbh->prepare($query);
|
|
| 1534 |
$sth->execute || $form->dberror($query);
|
|
| 1426 |
$query = |
|
| 1427 |
qq|SELECT printed, emailed, spoolfile, formname
|
|
| 1428 |
FROM status
|
|
| 1429 |
WHERE trans_id = ?|;
|
|
| 1430 |
$sth = prepare_execute_query($form, $dbh, $query, $id);
|
|
| 1535 | 1431 |
|
| 1536 | 1432 |
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 1537 | 1433 |
$form->{printed} .= "$ref->{formname} " if $ref->{printed};
|
| ... | ... | |
| 1544 | 1440 |
|
| 1545 | 1441 |
my $transdate = |
| 1546 | 1442 |
$form->{deliverydate} ? $dbh->quote($form->{deliverydate}) :
|
| 1547 |
$form->{invdate} ? $dbh->quote($form->{invdate}) :
|
|
| 1548 |
"current_date"; |
|
| 1443 |
$form->{invdate} ? $dbh->quote($form->{invdate}) :
|
|
| 1444 |
"current_date"; |
|
| 1445 |
|
|
| 1446 |
my $taxzone_id = $form->{taxzone_id} *= 1;
|
|
| 1447 |
$taxzone_id = 0 if (0 > $taxzone_id) || (3 < $taxzone_id); |
|
| 1549 | 1448 |
|
| 1550 |
if (!$form->{taxzone_id}) {
|
|
| 1551 |
$form->{taxzone_id} = 0;
|
|
| 1552 |
} |
|
| 1553 | 1449 |
# retrieve individual items |
| 1554 |
$query = qq|SELECT |
|
| 1555 |
c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid, |
|
| 1556 |
c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid, |
|
| 1557 |
c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid, |
|
| 1558 |
i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, |
|
| 1559 |
i.discount, i.parts_id AS id, i.unit, i.deliverydate, |
|
| 1560 |
i.project_id, pr.projectnumber, i.serialnumber, |
|
| 1561 |
p.partnumber, p.assembly, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, i.id AS invoice_pos, |
|
| 1562 |
pg.partsgroup, i.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=i.pricegroup_id) as pricegroup, |
|
| 1563 |
i.ordnumber, i.transdate, i.cusordnumber, p.formel, i.subtotal |
|
| 1564 |
FROM invoice i |
|
| 1565 |
JOIN parts p ON (i.parts_id = p.id) |
|
| 1566 |
LEFT JOIN project pr ON (i.project_id = pr.id) |
|
| 1567 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1568 |
LEFT JOIN chart c1 ON ((select inventory_accno_id from buchungsgruppen where id=p.buchungsgruppen_id) = c1.id) |
|
| 1569 |
LEFT JOIN chart c2 ON ((select income_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c2.id)
|
|
| 1570 |
LEFT JOIN chart c3 ON ((select expense_accno_id_$form->{taxzone_id} from buchungsgruppen where id=p.buchungsgruppen_id) = c3.id)
|
|
| 1571 |
WHERE i.trans_id = $form->{id}
|
|
| 1572 |
AND NOT i.assemblyitem = '1' |
|
| 1573 |
ORDER BY i.id|; |
|
| 1574 |
$sth = $dbh->prepare($query); |
|
| 1450 |
$query = |
|
| 1451 |
qq|SELECT |
|
| 1452 |
c1.accno AS inventory_accno, |
|
| 1453 |
c1.new_chart_id AS inventory_new_chart, |
|
| 1454 |
date($transdate) - c1.valid_from AS inventory_valid, |
|
| 1455 |
|
|
| 1456 |
c2.accno AS income_accno, |
|
| 1457 |
c2.new_chart_id AS income_new_chart, |
|
| 1458 |
date($transdate) - c2.valid_from as income_valid, |
|
| 1459 |
|
|
| 1460 |
c3.accno AS expense_accno, |
|
| 1461 |
c3.new_chart_id AS expense_new_chart, |
|
| 1462 |
date($transdate) - c3.valid_from AS expense_valid, |
|
| 1463 |
|
|
| 1464 |
i.description, i.longdescription, i.qty, i.fxsellprice AS sellprice, |
|
| 1465 |
i.discount, i.parts_id AS id, i.unit, i.deliverydate, |
|
| 1466 |
i.project_id, i.serialnumber, i.id AS invoice_pos, i.pricegroup_id, |
|
| 1467 |
i.ordnumber, i.transdate, i.cusordnumber, i.subtotal, |
|
| 1468 |
|
|
| 1469 |
p.partnumber, p.assembly, p.bin, p.notes AS partnotes, |
|
| 1470 |
p.inventory_accno_id AS part_inventory_accno_id, p.formel, |
|
| 1471 |
|
|
| 1472 |
pr.projectnumber, |
|
| 1473 |
pg.partsgroup, |
|
| 1474 |
prg.pricegroup |
|
| 1475 |
|
|
| 1476 |
FROM invoice i |
|
| 1477 |
LEFT JOIN parts p ON (i.parts_id = p.id) |
|
| 1478 |
LEFT JOIN project pr ON (i.project_id = pr.id) |
|
| 1479 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1480 |
LEFT JOIN pricegroup prg ON (i.pricegroup_id = prg.id) |
|
| 1481 |
|
|
| 1482 |
LEFT JOIN chart c1 ON |
|
| 1483 |
((SELECT inventory_accno_id |
|
| 1484 |
FROM buchungsgruppen |
|
| 1485 |
WHERE id = p.buchungsgruppen_id) = c1.id) |
|
| 1486 |
LEFT JOIN chart c2 ON |
|
| 1487 |
((SELECT income_accno_id_${taxzone_id}
|
|
| 1488 |
FROM buchungsgruppen |
|
| 1489 |
WHERE id=p.buchungsgruppen_id) = c2.id) |
|
| 1490 |
LEFT JOIN chart c3 ON |
|
| 1491 |
((SELECT expense_accno_id_${taxzone_id}
|
|
| 1492 |
FROM buchungsgruppen |
|
| 1493 |
WHERE id = p.buchungsgruppen_id) = c3.id) |
|
| 1494 |
|
|
| 1495 |
WHERE (i.trans_id = ?) |
|
| 1496 |
AND NOT (i.assemblyitem = '1') |
|
| 1497 |
ORDER BY i.id|; |
|
| 1498 |
|
|
| 1499 |
$sth = prepare_execute_query($form, $dbh, $query, $id); |
|
| 1575 | 1500 |
|
| 1576 |
$sth->execute || $form->dberror($query); |
|
| 1577 | 1501 |
while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 1578 | 1502 |
if (!$ref->{"part_inventory_accno_id"}) {
|
| 1579 | 1503 |
map({ delete($ref->{$_}); } qw(inventory_accno inventory_new_chart inventory_valid));
|
| 1580 | 1504 |
} |
| 1581 | 1505 |
delete($ref->{"part_inventory_accno_id"});
|
| 1582 | 1506 |
|
| 1583 |
while ($ref->{inventory_new_chart} && ($ref->{inventory_valid} >=0)) {
|
|
| 1584 |
my $query = qq| SELECT accno AS inventory_accno, new_chart_id AS inventory_new_chart, date($transdate) - valid_from AS inventory_valid FROM chart WHERE id = $ref->{inventory_new_chart}|;
|
|
| 1585 |
my $stw = $dbh->prepare($query); |
|
| 1586 |
$stw->execute || $form->dberror($query); |
|
| 1587 |
($ref->{inventory_accno}, $ref->{inventory_new_chart}, $ref->{inventory_valid}) = $stw->fetchrow_array;
|
|
| 1588 |
$stw->finish; |
|
| 1589 |
} |
|
| 1590 |
|
|
| 1591 |
while ($ref->{income_new_chart} && ($ref->{income_valid} >=0)) {
|
|
| 1592 |
my $query = qq| SELECT accno AS income_accno, new_chart_id AS income_new_chart, date($transdate) - valid_from AS income_valid FROM chart WHERE id = $ref->{income_new_chart}|;
|
|
| 1593 |
my $stw = $dbh->prepare($query); |
|
| 1594 |
$stw->execute || $form->dberror($query); |
|
| 1595 |
($ref->{income_accno}, $ref->{income_new_chart}, $ref->{income_valid}) = $stw->fetchrow_array;
|
|
| 1596 |
$stw->finish; |
|
| 1597 |
} |
|
| 1598 |
|
|
| 1599 |
while ($ref->{expense_new_chart} && ($ref->{expense_valid} >=0)) {
|
|
| 1600 |
my $query = qq| SELECT accno AS expense_accno, new_chart_id AS expense_new_chart, date($transdate) - valid_from AS expense_valid FROM chart WHERE id = $ref->{expense_new_chart}|;
|
|
| 1601 |
my $stw = $dbh->prepare($query); |
|
| 1602 |
$stw->execute || $form->dberror($query); |
|
| 1603 |
($ref->{expense_accno}, $ref->{expense_new_chart}, $ref->{expense_valid}) = $stw->fetchrow_array;
|
|
| 1604 |
$stw->finish; |
|
| 1605 |
} |
|
| 1507 |
foreach my $type (qw(inventory income expense)) {
|
|
| 1508 |
while ($ref->{"${type}_new_chart"} && ($ref->{"${type}_valid"} >=0)) {
|
|
| 1509 |
my $query = |
|
| 1510 |
qq|SELECT accno, new_chart_id, date($transdate) - valid_from |
|
| 1511 |
FROM chart |
|
| 1512 |
WHERE id = ?|; |
|
| 1513 |
($ref->{"${type}_accno"},
|
|
| 1514 |
$ref->{"${type}_new_chart"},
|
|
| 1515 |
$ref->{"${type}_valid"})
|
|
| 1516 |
= selectrow_query($form, $dbh, $query, $ref->{"${type}_new_chart"});
|
|
| 1517 |
} |
|
| 1518 |
} |
|
| 1606 | 1519 |
|
| 1607 | 1520 |
# get tax rates and description |
| 1608 |
$accno_id = |
|
| 1521 |
my $accno_id =
|
|
| 1609 | 1522 |
($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
|
| 1610 |
$query = qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber |
|
| 1611 |
FROM tax t LEFT JOIN chart c on (c.id=t.chart_id) |
|
| 1612 |
WHERE t.id in (SELECT tk.tax_id from taxkeys tk where tk.chart_id = (SELECT id from chart WHERE accno='$accno_id') AND startdate<=$transdate ORDER BY startdate desc LIMIT 1) |
|
| 1613 |
ORDER BY c.accno|; |
|
| 1614 |
$stw = $dbh->prepare($query); |
|
| 1615 |
$stw->execute || $form->dberror($query); |
|
| 1523 |
$query = |
|
| 1524 |
qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber |
|
| 1525 |
FROM tax t |
|
| 1526 |
LEFT JOIN chart c ON (c.id = t.chart_id) |
|
| 1527 |
WHERE t.id IN |
|
| 1528 |
(SELECT tk.tax_id |
|
| 1529 |
FROM taxkeys tk |
|
| 1530 |
WHERE tk.chart_id = (SELECT id FROM chart WHERE accno = ?) |
|
| 1531 |
AND startdate <= $transdate |
|
| 1532 |
ORDER BY startdate DESC |
|
| 1533 |
LIMIT 1) |
|
| 1534 |
ORDER BY c.accno|; |
|
| 1535 |
my $stw = prepare_execute_query($form, $dbh, $query, $accno_id); |
|
| 1616 | 1536 |
$ref->{taxaccounts} = "";
|
| 1617 | 1537 |
my $i=0; |
| 1618 | 1538 |
while ($ptr = $stw->fetchrow_hashref(NAME_lc)) {
|
| ... | ... | |
| 1634 | 1554 |
} |
| 1635 | 1555 |
|
| 1636 | 1556 |
if ($form->{lizenzen}) {
|
| 1637 |
$query = qq|SELECT l.licensenumber, l.id AS licenseid |
|
| 1638 |
FROM license l, licenseinvoice li |
|
| 1639 |
WHERE l.id = li.license_id AND li.trans_id = $ref->{invoice_pos}|;
|
|
| 1640 |
$stg = $dbh->prepare($query); |
|
| 1641 |
$stg->execute || $form->dberror($query); |
|
| 1642 |
($licensenumber, $licenseid) = $stg->fetchrow_array(); |
|
| 1643 |
$ref->{lizenzen} =
|
|
| 1644 |
"<option value=\"$licenseid\">$licensenumber</option>"; |
|
| 1645 |
$stg->finish(); |
|
| 1646 |
} |
|
| 1647 |
if ($form->{type} eq "credit_note") {
|
|
| 1648 |
$ref->{qty} *= -1;
|
|
| 1557 |
$query = |
|
| 1558 |
qq|SELECT l.licensenumber, l.id AS licenseid |
|
| 1559 |
FROM license l, licenseinvoice li |
|
| 1560 |
WHERE l.id = li.license_id AND li.trans_id = ?|; |
|
| 1561 |
my ($licensenumber, $licenseid) |
|
| 1562 |
= selectrow_query($form, $dbh, $query, conv_i($ref->{invoice_pos}));
|
|
| 1563 |
$ref->{lizenzen} = "<option value=\"$licenseid\">$licensenumber</option>";
|
|
| 1649 | 1564 |
} |
| 1650 | 1565 |
|
| 1566 |
$ref->{qty} *= -1 if $form->{type} eq "credit_note";
|
|
| 1567 |
|
|
| 1651 | 1568 |
chop $ref->{taxaccounts};
|
| 1652 | 1569 |
push @{ $form->{invoice_details} }, $ref;
|
| 1653 | 1570 |
$stw->finish; |
Auch abrufbar als: Unified diff
Alle Queries zur Vermeidung von SQL injections auf die Verwendung von Parametern bzw. ordentliches Quoten umgestellt.