Revision 60eb41d8
Von Sven Schöling vor etwa 17 Jahren hinzugefügt
| SL/OE.pm | ||
|---|---|---|
| 605 | 605 |
if ($form->{id}) {
|
| 606 | 606 |
|
| 607 | 607 |
# get default accounts and last order number |
| 608 |
$query = |
|
| 609 |
qq|SELECT (SELECT c.accno FROM chart c | . |
|
| 610 |
qq| WHERE d.inventory_accno_id = c.id) AS inventory_accno, | . |
|
| 611 |
qq| (SELECT c.accno FROM chart c | . |
|
| 612 |
qq| WHERE d.income_accno_id = c.id) AS income_accno, | . |
|
| 613 |
qq| (SELECT c.accno FROM chart c | . |
|
| 614 |
qq| WHERE d.expense_accno_id = c.id) AS expense_accno, | . |
|
| 615 |
qq| (SELECT c.accno FROM chart c | . |
|
| 616 |
qq| WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, | . |
|
| 617 |
qq| (SELECT c.accno FROM chart c | . |
|
| 618 |
qq| WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, | . |
|
| 619 |
qq|d.curr AS currencies | . |
|
| 620 |
qq|FROM defaults d|; |
|
| 608 |
$query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno, |
|
| 609 |
(SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno, |
|
| 610 |
(SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno, |
|
| 611 |
(SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, |
|
| 612 |
(SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, |
|
| 613 |
d.curr AS currencies |
|
| 614 |
FROM defaults d|; |
|
| 621 | 615 |
} else {
|
| 622 |
$query = |
|
| 623 |
qq|SELECT (SELECT c.accno FROM chart c | . |
|
| 624 |
qq| WHERE d.inventory_accno_id = c.id) AS inventory_accno, | . |
|
| 625 |
qq| (SELECT c.accno FROM chart c | . |
|
| 626 |
qq| WHERE d.income_accno_id = c.id) AS income_accno, | . |
|
| 627 |
qq| (SELECT c.accno FROM chart c | . |
|
| 628 |
qq| WHERE d.expense_accno_id = c.id) AS expense_accno, | . |
|
| 629 |
qq| (SELECT c.accno FROM chart c | . |
|
| 630 |
qq| WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, | . |
|
| 631 |
qq| (SELECT c.accno FROM chart c | . |
|
| 632 |
qq| WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, | . |
|
| 633 |
qq|d.curr AS currencies, | . |
|
| 634 |
qq|current_date AS transdate, current_date AS reqdate | . |
|
| 635 |
qq|FROM defaults d|; |
|
| 616 |
$query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno, |
|
| 617 |
(SELECT c.accno FROM chart c WHERE d.income_accno_id = c.id) AS income_accno, |
|
| 618 |
(SELECT c.accno FROM chart c WHERE d.expense_accno_id = c.id) AS expense_accno, |
|
| 619 |
(SELECT c.accno FROM chart c WHERE d.fxgain_accno_id = c.id) AS fxgain_accno, |
|
| 620 |
(SELECT c.accno FROM chart c WHERE d.fxloss_accno_id = c.id) AS fxloss_accno, |
|
| 621 |
d.curr AS currencies, current_date AS transdate, current_date AS reqdate |
|
| 622 |
FROM defaults d|; |
|
| 636 | 623 |
} |
| 637 | 624 |
my $sth = $dbh->prepare($query); |
| 638 | 625 |
$sth->execute || $form->dberror($query); |
| ... | ... | |
| 658 | 645 |
# so if any of these infos is important (or even different) for any item, |
| 659 | 646 |
# it will be killed out and then has to be fetched from the item scope query further down |
| 660 | 647 |
$query = |
| 661 |
qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate, | . |
|
| 662 |
qq| o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes, | . |
|
| 663 |
qq| o.curr AS currency, e.name AS employee, o.employee_id, o.salesman_id, | . |
|
| 664 |
qq| o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal, | .
|
|
| 665 |
qq| o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber, | . |
|
| 666 |
qq| d.description AS department, o.payment_id, o.language_id, o.taxzone_id, | . |
|
| 667 |
qq| o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id, | . |
|
| 668 |
qq| o.globalproject_id, o.delivered, o.transaction_description | . |
|
| 669 |
qq|FROM oe o | . |
|
| 670 |
qq|JOIN ${vc} cv ON (o.${vc}_id = cv.id) | .
|
|
| 671 |
qq|LEFT JOIN employee e ON (o.employee_id = e.id) | . |
|
| 672 |
qq|LEFT JOIN department d ON (o.department_id = d.id) | . |
|
| 673 |
($form->{id} ? qq|WHERE o.id = ?| :
|
|
| 674 |
qq|WHERE o.id IN (| . join(', ', map("? ", @ids)) . qq|)|);
|
|
| 648 |
qq|SELECT o.cp_id, o.ordnumber, o.transdate, o.reqdate, |
|
| 649 |
o.taxincluded, o.shippingpoint, o.shipvia, o.notes, o.intnotes, |
|
| 650 |
o.curr AS currency, e.name AS employee, o.employee_id, o.salesman_id, |
|
| 651 |
o.${vc}_id, cv.name AS ${vc}, o.amount AS invtotal,
|
|
| 652 |
o.closed, o.reqdate, o.quonumber, o.department_id, o.cusordnumber, |
|
| 653 |
d.description AS department, o.payment_id, o.language_id, o.taxzone_id, |
|
| 654 |
o.delivery_customer_id, o.delivery_vendor_id, o.proforma, o.shipto_id, |
|
| 655 |
o.globalproject_id, o.delivered, o.transaction_description |
|
| 656 |
FROM oe o |
|
| 657 |
JOIN ${vc} cv ON (o.${vc}_id = cv.id)
|
|
| 658 |
LEFT JOIN employee e ON (o.employee_id = e.id) |
|
| 659 |
LEFT JOIN department d ON (o.department_id = d.id) | . |
|
| 660 |
($form->{id}
|
|
| 661 |
? "WHERE o.id = ?" |
|
| 662 |
: "WHERE o.id IN (" . join(', ', map("? ", @ids)) . ")"
|
|
| 663 |
); |
|
| 675 | 664 |
@values = $form->{id} ? ($form->{id}) : @ids;
|
| 676 | 665 |
$sth = prepare_execute_query($form, $dbh, $query, @values); |
| 677 | 666 |
|
| ... | ... | |
| 694 | 683 |
|
| 695 | 684 |
if ($form->{delivery_customer_id}) {
|
| 696 | 685 |
$query = qq|SELECT name FROM customer WHERE id = ?|; |
| 697 |
($form->{delivery_customer_string}) =
|
|
| 698 |
selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
|
|
| 686 |
($form->{delivery_customer_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_customer_id});
|
|
| 699 | 687 |
} |
| 700 | 688 |
|
| 701 | 689 |
if ($form->{delivery_vendor_id}) {
|
| 702 | 690 |
$query = qq|SELECT name FROM customer WHERE id = ?|; |
| 703 |
($form->{delivery_vendor_string}) =
|
|
| 704 |
selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
|
|
| 691 |
($form->{delivery_vendor_string}) = selectrow_query($form, $dbh, $query, $form->{delivery_vendor_id});
|
|
| 705 | 692 |
} |
| 706 | 693 |
|
| 707 | 694 |
# shipto and pinted/mailed/queued status makes only sense for single id retrieve |
| 708 | 695 |
if (!@ids) {
|
| 709 |
$query = qq|SELECT s.* FROM shipto s | . |
|
| 710 |
qq|WHERE s.trans_id = ? AND s.module = 'OE'|; |
|
| 696 |
$query = qq|SELECT s.* FROM shipto s WHERE s.trans_id = ? AND s.module = 'OE'|; |
|
| 711 | 697 |
$sth = prepare_execute_query($form, $dbh, $query, $form->{id});
|
| 712 | 698 |
|
| 713 | 699 |
$ref = $sth->fetchrow_hashref(NAME_lc); |
| ... | ... | |
| 716 | 702 |
$sth->finish; |
| 717 | 703 |
|
| 718 | 704 |
# get printed, emailed and queued |
| 719 |
$query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname | . |
|
| 720 |
qq|FROM status s | . |
|
| 721 |
qq|WHERE s.trans_id = ?|; |
|
| 705 |
$query = qq|SELECT s.printed, s.emailed, s.spoolfile, s.formname FROM status s WHERE s.trans_id = ?|; |
|
| 722 | 706 |
$sth = prepare_execute_query($form, $dbh, $query, $form->{id});
|
| 723 | 707 |
|
| 724 | 708 |
while ($ref = $sth->fetchrow_hashref(NAME_lc)) {
|
| 725 | 709 |
$form->{printed} .= "$ref->{formname} " if $ref->{printed};
|
| 726 | 710 |
$form->{emailed} .= "$ref->{formname} " if $ref->{emailed};
|
| 727 |
$form->{queued} .= "$ref->{formname} $ref->{spoolfile} "
|
|
| 728 |
if $ref->{spoolfile};
|
|
| 711 |
$form->{queued} .= "$ref->{formname} $ref->{spoolfile} " if $ref->{spoolfile};
|
|
| 729 | 712 |
} |
| 730 | 713 |
$sth->finish; |
| 731 | 714 |
map { $form->{$_} =~ s/ +$//g } qw(printed emailed queued);
|
| ... | ... | |
| 734 | 717 |
my %oid = ('Pg' => 'oid',
|
| 735 | 718 |
'Oracle' => 'rowid'); |
| 736 | 719 |
|
| 737 |
my $transdate = |
|
| 738 |
$form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
|
|
| 720 |
my $transdate = $form->{transdate} ? $dbh->quote($form->{transdate}) : "current_date";
|
|
| 739 | 721 |
|
| 740 | 722 |
$form->{taxzone_id} = 0 unless ($form->{taxzone_id});
|
| 741 | 723 |
|
| ... | ... | |
| 743 | 725 |
# this query looks up all information about the items |
| 744 | 726 |
# stuff different from the whole will not be overwritten, but saved with a suffix. |
| 745 | 727 |
$query = |
| 746 |
qq|SELECT o.id AS orderitems_id, | . |
|
| 747 |
qq| c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid, | . |
|
| 748 |
qq| c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid, | . |
|
| 749 |
qq| c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid, | . |
|
| 750 |
qq| oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe, | . |
|
| 751 |
qq| p.partnumber, p.assembly, o.description, o.qty, | . |
|
| 752 |
qq| o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, | . |
|
| 753 |
qq| o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost, | . |
|
| 754 |
qq| o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription, | . |
|
| 755 |
qq| pr.projectnumber, p.formel, | . |
|
| 756 |
qq| pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup | . |
|
| 757 |
qq|FROM orderitems o | . |
|
| 758 |
qq|JOIN parts p ON (o.parts_id = p.id) | . |
|
| 759 |
qq|JOIN oe ON (o.trans_id = oe.id) | . |
|
| 760 |
qq|LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id) | . |
|
| 761 |
qq|LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id) | .
|
|
| 762 |
qq|LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id) | .
|
|
| 763 |
qq|LEFT JOIN project pr ON (o.project_id = pr.id) | . |
|
| 764 |
qq|LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | . |
|
| 765 |
($form->{id} ? qq|WHERE o.trans_id = ?| :
|
|
| 766 |
qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
|
|
| 728 |
qq|SELECT o.id AS orderitems_id, |
|
| 729 |
c1.accno AS inventory_accno, c1.new_chart_id AS inventory_new_chart, date($transdate) - c1.valid_from as inventory_valid, |
|
| 730 |
c2.accno AS income_accno, c2.new_chart_id AS income_new_chart, date($transdate) - c2.valid_from as income_valid, |
|
| 731 |
c3.accno AS expense_accno, c3.new_chart_id AS expense_new_chart, date($transdate) - c3.valid_from as expense_valid, |
|
| 732 |
oe.ordnumber AS ordnumber_oe, oe.transdate AS transdate_oe, oe.cusordnumber AS cusordnumber_oe, |
|
| 733 |
p.partnumber, p.assembly, o.description, o.qty, |
|
| 734 |
o.sellprice, o.parts_id AS id, o.unit, o.discount, p.bin, p.notes AS partnotes, p.inventory_accno_id AS part_inventory_accno_id, |
|
| 735 |
o.reqdate, o.project_id, o.serialnumber, o.ship, o.lastcost, |
|
| 736 |
o.ordnumber, o.transdate, o.cusordnumber, o.subtotal, o.longdescription, |
|
| 737 |
pr.projectnumber, p.formel, |
|
| 738 |
pg.partsgroup, o.pricegroup_id, (SELECT pricegroup FROM pricegroup WHERE id=o.pricegroup_id) as pricegroup |
|
| 739 |
FROM orderitems o |
|
| 740 |
JOIN parts p ON (o.parts_id = p.id) |
|
| 741 |
JOIN oe ON (o.trans_id = oe.id) |
|
| 742 |
LEFT JOIN chart c1 ON ((SELECT inventory_accno_id FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c1.id) |
|
| 743 |
LEFT JOIN chart c2 ON ((SELECT income_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c2.id)
|
|
| 744 |
LEFT JOIN chart c3 ON ((SELECT expense_accno_id_$form->{taxzone_id} FROM buchungsgruppen WHERE id=p.buchungsgruppen_id) = c3.id)
|
|
| 745 |
LEFT JOIN project pr ON (o.project_id = pr.id) |
|
| 746 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) | . |
|
| 747 |
($form->{id}
|
|
| 748 |
? qq|WHERE o.trans_id = ?| |
|
| 749 |
: qq|WHERE o.trans_id IN (| . join(", ", map("?", @ids)) . qq|)|) .
|
|
| 767 | 750 |
qq|ORDER BY o.$oid{$myconfig->{dbdriver}}|;
|
| 768 | 751 |
|
| 769 | 752 |
@ids = $form->{id} ? ($form->{id}) : @ids;
|
| ... | ... | |
| 819 | 802 |
delete $ref->{orderitems_id} if (@ids);
|
| 820 | 803 |
|
| 821 | 804 |
# get tax rates and description |
| 822 |
$accno_id = |
|
| 823 |
($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
|
|
| 805 |
$accno_id = ($form->{vc} eq "customer") ? $ref->{income_accno} : $ref->{expense_accno};
|
|
| 824 | 806 |
$query = |
| 825 | 807 |
qq|SELECT c.accno, t.taxdescription, t.rate, t.taxnumber | . |
| 826 | 808 |
qq|FROM tax t LEFT JOIN chart c on (c.id = t.chart_id) | . |
| ... | ... | |
| 860 | 842 |
|
| 861 | 843 |
} |
| 862 | 844 |
|
| 863 |
$form->{exchangerate} =
|
|
| 864 |
$form->get_exchangerate($dbh, $form->{currency}, $form->{transdate},
|
|
| 865 |
($form->{vc} eq 'customer') ? "buy" : "sell");
|
|
| 845 |
$form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? "buy" : "sell");
|
|
| 866 | 846 |
|
| 867 | 847 |
Common::webdav_folder($form) if ($main::webdav); |
| 868 | 848 |
|
Auch abrufbar als: Unified diff
Kosmetikmerge aus r5130