Revision 09306b9f
Von Sven Schöling vor etwa 12 Jahren hinzugefügt
| SL/CT.pm | ||
|---|---|---|
| 598 | 598 |
my $dbh = $form->dbconnect($myconfig); |
| 599 | 599 |
|
| 600 | 600 |
my $cv = $form->{db} eq "customer" ? "customer" : "vendor";
|
| 601 |
my $join_records = $form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber};
|
|
| 601 | 602 |
|
| 602 | 603 |
my $where = "1 = 1"; |
| 603 | 604 |
my @values; |
| ... | ... | |
| 611 | 612 |
$form->{sort} = $sortorder;
|
| 612 | 613 |
my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
|
| 613 | 614 |
|
| 614 |
if ($sortorder !~ /(business|id)/ && 1 >= scalar grep { $form->{$_} } qw(l_ordnumber l_quonumber l_invnumber )) {
|
|
| 615 |
if ($sortorder !~ /(business|id)/ && !$join_records) {
|
|
| 615 | 616 |
$sortorder = "lower($sortorder) ${sortdir}";
|
| 616 | 617 |
} else {
|
| 617 | 618 |
$sortorder .= " ${sortdir}";
|
| ... | ... | |
| 705 | 706 |
|
| 706 | 707 |
my $query = |
| 707 | 708 |
qq|SELECT ct.*, b.description AS business | . |
| 709 |
(qq|, NULL AS invnumber, NULL AS ordnumber, NULL AS quonumber, NULL AS invid, NULL AS module, NULL AS formtype, NULL AS closed | x!! $join_records) . |
|
| 708 | 710 |
qq|FROM $cv ct | . |
| 709 | 711 |
qq|LEFT JOIN business b ON (ct.business_id = b.id) | . |
| 710 | 712 |
qq|WHERE $where|; |
| 711 | 713 |
|
| 712 | 714 |
my @saved_values = @values; |
| 713 | 715 |
# redo for invoices, orders and quotations |
| 714 |
if ($form->{l_invnumber} || $form->{l_ordnumber} || $form->{l_quonumber}) {
|
|
| 715 |
my ($ar, $union, $module); |
|
| 716 |
$query = ""; |
|
| 716 |
if ($join_records) {
|
|
| 717 |
my $union = "UNION"; |
|
| 717 | 718 |
|
| 718 | 719 |
if ($form->{l_invnumber}) {
|
| 719 | 720 |
my $ar = $cv eq 'customer' ? 'ar' : 'ap'; |
| 720 | 721 |
my $module = $ar eq 'ar' ? 'is' : 'ir'; |
| 721 |
|
|
| 722 |
$query = |
|
| 722 |
push(@values, @saved_values); |
|
| 723 |
$query .= |
|
| 724 |
qq| UNION | . |
|
| 723 | 725 |
qq|SELECT ct.*, b.description AS business, | . |
| 724 | 726 |
qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | . |
| 725 | 727 |
qq| '$module' AS module, 'invoice' AS formtype, | . |
| ... | ... | |
| 728 | 730 |
qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
|
| 729 | 731 |
qq|LEFT JOIN business b ON (ct.business_id = b.id) | . |
| 730 | 732 |
qq|WHERE $where AND (a.invoice = '1')|; |
| 731 |
|
|
| 732 |
$union = qq|UNION|; |
|
| 733 | 733 |
} |
| 734 | 734 |
|
| 735 | 735 |
if ( $form->{l_ordnumber} ) {
|
| 736 |
if ($union eq "UNION") {
|
|
| 737 |
push(@values, @saved_values); |
|
| 738 |
} |
|
| 736 |
push(@values, @saved_values); |
|
| 739 | 737 |
$query .= |
| 740 |
qq| $union | .
|
|
| 738 |
qq| UNION | .
|
|
| 741 | 739 |
qq|SELECT ct.*, b.description AS business,| . |
| 742 | 740 |
qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | . |
| 743 | 741 |
qq| 'oe' AS module, 'order' AS formtype, o.closed | . |
| ... | ... | |
| 745 | 743 |
qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
|
| 746 | 744 |
qq|LEFT JOIN business b ON (ct.business_id = b.id) | . |
| 747 | 745 |
qq|WHERE $where AND (o.quotation = '0')|; |
| 748 |
|
|
| 749 |
$union = qq|UNION|; |
|
| 750 | 746 |
} |
| 751 | 747 |
|
| 752 | 748 |
if ( $form->{l_quonumber} ) {
|
| 753 |
if ($union eq "UNION") {
|
|
| 754 |
push(@values, @saved_values); |
|
| 755 |
} |
|
| 749 |
push(@values, @saved_values); |
|
| 756 | 750 |
$query .= |
| 757 |
qq| $union | .
|
|
| 751 |
qq| UNION | .
|
|
| 758 | 752 |
qq|SELECT ct.*, b.description AS business, | . |
| 759 | 753 |
qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | . |
| 760 | 754 |
qq| 'oe' AS module, 'quotation' AS formtype, o.closed | . |
Auch abrufbar als: Unified diff
joins in Kundensuche umgeschrieben
behebt #1894
...wenn auch nicht sonderlich schön, auf Dauer müssen die ganzen aggreg
Funktionen in ein Berichtswesen ausgelagert werden.