Revision 2def6461
Von Kivitendo Admin vor mehr als 11 Jahren hinzugefügt
| SL/CT.pm | ||
|---|---|---|
| 617 | 617 |
my %allowed_sort_columns = |
| 618 | 618 |
map { $_, 1 } qw(
|
| 619 | 619 |
id customernumber vendornumber name contact phone fax email street |
| 620 |
taxnumber business invnumber ordnumber quonumber zipcode city |
|
| 620 |
taxnumber business invnumber ordnumber quonumber zipcode city country salesman
|
|
| 621 | 621 |
); |
| 622 | 622 |
my $sortorder = $form->{sort} && $allowed_sort_columns{$form->{sort}} ? $form->{sort} : "name";
|
| 623 | 623 |
$form->{sort} = $sortorder;
|
| ... | ... | |
| 659 | 659 |
push @values, ('%' . $form->{addr_city} . '%') x 2;
|
| 660 | 660 |
} |
| 661 | 661 |
|
| 662 |
if ($form->{addr_country}) {
|
|
| 663 |
$where .= " AND ((lower(ct.country) LIKE lower(?)) |
|
| 664 |
OR |
|
| 665 |
(ct.id IN ( |
|
| 666 |
SELECT trans_id |
|
| 667 |
FROM shipto |
|
| 668 |
WHERE (module = 'CT') |
|
| 669 |
AND (lower(shiptocountry) LIKE lower(?)) |
|
| 670 |
)) |
|
| 671 |
)"; |
|
| 672 |
push @values, ('%' . $form->{addr_country} . '%') x 2;
|
|
| 673 |
} |
|
| 674 |
|
|
| 662 | 675 |
if ( $form->{status} eq 'orphaned' ) {
|
| 663 | 676 |
$where .= |
| 664 | 677 |
qq| AND ct.id NOT IN | . |
| ... | ... | |
| 676 | 689 |
qq| WHERE cv.id = a.vendor_id)|; |
| 677 | 690 |
} |
| 678 | 691 |
$form->{l_invnumber} = $form->{l_ordnumber} = $form->{l_quonumber} = "";
|
| 679 |
} |
|
| 692 |
};
|
|
| 680 | 693 |
|
| 681 | 694 |
if ($form->{obsolete} eq "Y") {
|
| 682 | 695 |
$where .= qq| AND obsolete|; |
| ... | ... | |
| 716 | 729 |
} |
| 717 | 730 |
|
| 718 | 731 |
my $query = |
| 719 |
qq|SELECT ct.*, b.description AS business | . |
|
| 732 |
qq|SELECT ct.*, b.description AS business, e.name as salesman | .
|
|
| 720 | 733 |
(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) . |
| 721 | 734 |
qq|FROM $cv ct | . |
| 722 | 735 |
qq|LEFT JOIN business b ON (ct.business_id = b.id) | . |
| 736 |
qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . |
|
| 723 | 737 |
qq|WHERE $where|; |
| 724 | 738 |
|
| 725 | 739 |
my @saved_values = @values; |
| ... | ... | |
| 733 | 747 |
push(@values, @saved_values); |
| 734 | 748 |
$query .= |
| 735 | 749 |
qq| UNION | . |
| 736 |
qq|SELECT ct.*, b.description AS business, | . |
|
| 750 |
qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
|
|
| 737 | 751 |
qq| a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, | . |
| 738 | 752 |
qq| '$module' AS module, 'invoice' AS formtype, | . |
| 739 | 753 |
qq| (a.amount = a.paid) AS closed | . |
| 740 | 754 |
qq|FROM $cv ct | . |
| 741 | 755 |
qq|JOIN $ar a ON (a.${cv}_id = ct.id) | .
|
| 742 | 756 |
qq|LEFT JOIN business b ON (ct.business_id = b.id) | . |
| 757 |
qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . |
|
| 743 | 758 |
qq|WHERE $where AND (a.invoice = '1')|; |
| 744 | 759 |
} |
| 745 | 760 |
|
| ... | ... | |
| 747 | 762 |
push(@values, @saved_values); |
| 748 | 763 |
$query .= |
| 749 | 764 |
qq| UNION | . |
| 750 |
qq|SELECT ct.*, b.description AS business,| . |
|
| 765 |
qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
|
|
| 751 | 766 |
qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | . |
| 752 | 767 |
qq| 'oe' AS module, 'order' AS formtype, o.closed | . |
| 753 | 768 |
qq|FROM $cv ct | . |
| 754 | 769 |
qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
|
| 755 | 770 |
qq|LEFT JOIN business b ON (ct.business_id = b.id) | . |
| 771 |
qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . |
|
| 756 | 772 |
qq|WHERE $where AND (o.quotation = '0')|; |
| 757 | 773 |
} |
| 758 | 774 |
|
| ... | ... | |
| 760 | 776 |
push(@values, @saved_values); |
| 761 | 777 |
$query .= |
| 762 | 778 |
qq| UNION | . |
| 763 |
qq|SELECT ct.*, b.description AS business, | . |
|
| 779 |
qq|SELECT ct.*, b.description AS business, e.name as salesman, | .
|
|
| 764 | 780 |
qq| ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, | . |
| 765 | 781 |
qq| 'oe' AS module, 'quotation' AS formtype, o.closed | . |
| 766 | 782 |
qq|FROM $cv ct | . |
| 767 | 783 |
qq|JOIN oe o ON (o.${cv}_id = ct.id) | .
|
| 768 | 784 |
qq|LEFT JOIN business b ON (ct.business_id = b.id) | . |
| 785 |
qq|LEFT JOIN employee e ON (ct.salesman_id = e.id) | . |
|
| 769 | 786 |
qq|WHERE $where AND (o.quotation = '1')|; |
| 770 | 787 |
} |
| 771 | 788 |
} |
Auch abrufbar als: Unified diff
Verkauf -> Berichte -> Kunden - Land und Verkäufer
neue Häkchen bei "In Bericht aufnehmen"