Revision 193c7381
Von Sven Schöling vor mehr als 17 Jahren hinzugefügt
| SL/IC.pm | ||
|---|---|---|
| 787 | 787 |
# Warning, deep magic ahead. |
| 788 | 788 |
# This function gets all parts from the database according to the filters specified |
| 789 | 789 |
# |
| 790 |
# filter strings:
|
|
| 791 |
# partnumber ean description partsgroup serialnumber make model drawing microfiche
|
|
| 792 |
# transdatefrom transdateto sort
|
|
| 790 |
# specials:
|
|
| 791 |
# sort revers - sorting field + direction
|
|
| 792 |
# top100
|
|
| 793 | 793 |
# |
| 794 |
# exclusives: |
|
| 795 |
# itemstatus = active | onhand | short | obsolete | orphaned |
|
| 796 |
# searchitems = part | assembly | service |
|
| 794 |
# simple filter strings (every one of those also has a column flag prefixed with 'l_' associated): |
|
| 795 |
# partnumber ean description partsgroup microfiche drawing |
|
| 797 | 796 |
# |
| 798 | 797 |
# column flags: |
| 799 | 798 |
# l_partnumber l_description l_listprice l_sellprice l_lastcost l_priceupdate l_weight l_unit l_bin l_rop l_image l_drawing l_microfiche l_partsgroup |
| 800 | 799 |
# |
| 800 |
# exclusives: |
|
| 801 |
# itemstatus = active | onhand | short | obsolete | orphaned |
|
| 802 |
# searchitems = part | assembly | service |
|
| 803 |
# |
|
| 804 |
# joining filters: |
|
| 805 |
# make model - makemodel |
|
| 806 |
# serialnumber transdatefrom transdateto - invoice/orderitems |
|
| 807 |
# |
|
| 801 | 808 |
# binary flags: |
| 802 |
# bought sold onorder ordered rfq quoted onhand short |
|
| 803 |
# l_serialnumber l_linetotal l_subtotal l_soldtotal l_deliverydate |
|
| 804 |
# revers top100 |
|
| 809 |
# bought sold onorder ordered rfq quoted - aggreg joins with invoices/orders |
|
| 810 |
# l_linetotal l_subtotal - aggreg joins to display totals (complicated) - NOT IMPLEMENTED here, implementation at frontend |
|
| 811 |
# l_soldtotal - aggreg join to display total of sold quantity |
|
| 812 |
# onhand - as above, but masking the simple itemstatus results (doh!) |
|
| 813 |
# short - NOT IMPLEMENTED as form filter, only as itemstatus option |
|
| 814 |
# l_serialnumber - belonges to serialnumber filter |
|
| 815 |
# l_deliverydate - displays deliverydate is sold etc. flags are active |
|
| 816 |
# |
|
| 817 |
# not working: |
|
| 818 |
# l_soldtotal - aggreg join to display total of sold quantity |
|
| 819 |
# onhand - as above, but masking the simple itemstatus results (doh!) |
|
| 820 |
# masking of onhand in bsooqr mode - ToDO: fixme |
|
| 821 |
# |
|
| 822 |
# disabled sanity checks and changes: |
|
| 823 |
# - searchitems = assembly will no longer disable bought |
|
| 824 |
# - searchitems = service will no longer disable make and model, although services don't have make/model, it doesn't break the query |
|
| 825 |
# - itemstatus = orphaned will no longer disable onhand short bought sold onorder ordered rfq quoted transdate[from|to] |
|
| 826 |
# - itemstatus = obsolete will no longer disable onhand, short |
|
| 827 |
# - allow sorting by ean |
|
| 828 |
# - serialnumber filter also works if l_serialnumber isn't ticked |
|
| 829 |
# - onhand doesn't get masked by it's oi or invoice counterparts atm. ToDO: fix this |
|
| 830 |
# - sorting will now change sorting if the requested sorting column isn't checked and doesn't get checked as a side effect |
|
| 805 | 831 |
# |
| 806 | 832 |
sub all_parts {
|
| 807 | 833 |
$main::lxdebug->enter_sub(); |
| 808 | 834 |
|
| 809 | 835 |
my ($self, $myconfig, $form) = @_; |
| 810 |
|
|
| 811 |
my $where = qq|1 = 1|; |
|
| 812 |
my (@values, $var, $flds, $group, $limit); |
|
| 813 |
|
|
| 814 |
foreach my $item (qw(partnumber drawing microfiche ean pg.partsgroup)) {
|
|
| 815 |
my $column = $item; |
|
| 816 |
$column =~ s/.*\.//; # get rid of table prefixes |
|
| 817 |
if ($form->{$column}) {
|
|
| 818 |
$where .= qq| AND ($item ILIKE ?)|; |
|
| 819 |
push(@values, "%$form->{$column}%");
|
|
| 836 |
my $dbh = $form->get_standard_dbh($myconfig); |
|
| 837 |
|
|
| 838 |
$form->{parts} = +{ };
|
|
| 839 |
|
|
| 840 |
my @simple_filters = qw(partnumber ean description partsgroup microfiche drawing); |
|
| 841 |
my @makemodel_filters = qw(make model); |
|
| 842 |
my @invoice_oi_filters = qw(serialnumber soldtotal); |
|
| 843 |
my @apoe_filters = qw(transdate); |
|
| 844 |
my @all_columns = (@simple_filters, @makemodel_filters, @apoe_filters, qw(serialnumber)); |
|
| 845 |
my @simple_l_switches = (@all_columns, qw(listprice sellprice lastcost priceupdate weight unit bin rop image)); |
|
| 846 |
my @oe_flags = qw(bought sold onorder ordered rfq quoted); |
|
| 847 |
my @qsooqr_flags = qw(invnumber ordnumber quonumber trans_id name module); |
|
| 848 |
my @deliverydate_flags = qw(deliverydate); |
|
| 849 |
# my @other_flags = qw(onhand); # ToDO: implement these |
|
| 850 |
# my @inactive_flags = qw(l_subtotal short l_linetotal); |
|
| 851 |
|
|
| 852 |
my %joins = ( |
|
| 853 |
partsgroup => 'LEFT JOIN partsgroup pg ON p.partsgroup_id = pg.id', |
|
| 854 |
makemodel => 'LEFT JOIN makemodel mm ON mm.parts_id = p.id', |
|
| 855 |
invoice_oi => |
|
| 856 |
q|LEFT JOIN ( |
|
| 857 |
SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, 'invoice' AS ioi FROM invoice UNION |
|
| 858 |
SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, 'orderitems' AS ioi FROM orderitems |
|
| 859 |
) AS ioi ON ioi.parts_id = p.id|, |
|
| 860 |
apoe => |
|
| 861 |
q|LEFT JOIN ( |
|
| 862 |
SELECT id, transdate, 'ir' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, NULL AS customer_id, vendor_id, NULL AS deliverydate FROM ap UNION |
|
| 863 |
SELECT id, transdate, 'is' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, customer_id, NULL AS vendor_id, deliverydate FROM ar UNION |
|
| 864 |
SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, quotation, customer_id, vendor_id, NULL AS deliverydate FROM oe |
|
| 865 |
) AS apoe ON ioi.trans_id = apoe.id|, |
|
| 866 |
cv => |
|
| 867 |
q|LEFT JOIN ( |
|
| 868 |
SELECT id, name, 'customer' AS cv FROM customer UNION |
|
| 869 |
SELECT id, name, 'vendor' AS cv FROM vendor |
|
| 870 |
) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|, |
|
| 871 |
); |
|
| 872 |
my @join_order = qw(partsgroup makemodel invoice_oi apoe cv); |
|
| 873 |
my %joins_needed = (0) x scalar keys %joins; |
|
| 874 |
|
|
| 875 |
#===== switches and simple filters ========# |
|
| 876 |
|
|
| 877 |
my @select_tokens = qw(id); |
|
| 878 |
my @where_tokens = qw(1=1); |
|
| 879 |
my @group_tokens = (); |
|
| 880 |
|
|
| 881 |
# special case transdate |
|
| 882 |
if (grep { $form->{$_} } qw(transdatefrom transdateto)) {
|
|
| 883 |
$form->{"l_transdate"} = 1;
|
|
| 884 |
push @select_tokens, 'transdate'; |
|
| 885 |
for (qw(transdatefrom transdateto)) {
|
|
| 886 |
next unless $form->{$_};
|
|
| 887 |
push @where_tokens, sprintf "transdate %s ?", /from$/ ? '>=' : '<='; |
|
| 888 |
push @bind_vars, $form->{$_};
|
|
| 820 | 889 |
} |
| 821 | 890 |
} |
| 822 | 891 |
|
| 823 |
# special case for description |
|
| 824 |
if ($form->{description}
|
|
| 825 |
&& !( $form->{bought} || $form->{sold} || $form->{onorder}
|
|
| 826 |
|| $form->{ordered} || $form->{rfq} || $form->{quoted})) {
|
|
| 827 |
$where .= qq| AND (p.description ILIKE ?)|; |
|
| 828 |
push(@values, "%$form->{description}%");
|
|
| 829 |
} |
|
| 830 |
|
|
| 831 |
# special case for serialnumber |
|
| 832 |
if ($form->{l_serialnumber} && $form->{serialnumber}) {
|
|
| 833 |
$where .= qq| AND (serialnumber ILIKE ?)|; |
|
| 834 |
push(@values, "%$form->{serialnumber}%");
|
|
| 835 |
} |
|
| 836 |
|
|
| 837 |
if ($form->{searchitems} eq 'part') {
|
|
| 838 |
$where .= qq| AND (p.inventory_accno_id > 0) |; |
|
| 839 |
} |
|
| 840 |
|
|
| 841 |
if ($form->{searchitems} eq 'assembly') {
|
|
| 842 |
$form->{bought} = "";
|
|
| 843 |
$where .= qq| AND p.assembly|; |
|
| 844 |
} |
|
| 845 |
|
|
| 846 |
if ($form->{searchitems} eq 'service') {
|
|
| 847 |
$where .= qq| AND (p.inventory_accno_id IS NULL) AND NOT (p.assembly = '1')|; |
|
| 848 |
# irrelevant for services |
|
| 849 |
map { $form->{$_} = '' } qw(make model);
|
|
| 892 |
foreach (@simple_filters, @makemodel_filters, @invoice_oi_filters) {
|
|
| 893 |
next unless $form->{$_};
|
|
| 894 |
$form->{"l_$_"} = '1'; # show the column
|
|
| 895 |
push @where_tokens, "$_ ILIKE ?"; |
|
| 896 |
push @bind_vars, "%$form->{$_}%";
|
|
| 850 | 897 |
} |
| 851 | 898 |
|
| 852 |
# items which were never bought, sold or on an order |
|
| 853 |
if ($form->{itemstatus} eq 'orphaned') {
|
|
| 854 |
map { $form->{$_} = 0 } qw(onhand short bought sold onorder ordered rfq quoted);
|
|
| 855 |
map { $form->{$_} = '' } qw(transdatefrom transdateto);
|
|
| 856 |
|
|
| 857 |
$where .= |
|
| 858 |
qq| AND (p.onhand = 0) |
|
| 859 |
AND p.id NOT IN |
|
| 860 |
( |
|
| 861 |
SELECT DISTINCT parts_id FROM invoice |
|
| 862 |
UNION |
|
| 863 |
SELECT DISTINCT parts_id FROM assembly |
|
| 864 |
UNION |
|
| 865 |
SELECT DISTINCT parts_id FROM orderitems |
|
| 866 |
)|; |
|
| 899 |
foreach (@simple_l_switches) {
|
|
| 900 |
next unless $form->{"l_$_"};
|
|
| 901 |
push @select_tokens, $_; |
|
| 867 | 902 |
} |
| 868 | 903 |
|
| 869 |
my %status2condition = ( |
|
| 870 |
active => " AND (p.obsolete = '0')", |
|
| 871 |
obsolete => " AND (p.obsolete = '1')", |
|
| 872 |
onhand => " AND (p.onhand > 0)", |
|
| 873 |
short => " AND (p.onhand < p.rop)", |
|
| 874 |
); |
|
| 875 |
$where .= $status2condition{$form->{itemstatus}};
|
|
| 876 |
|
|
| 877 |
$form->{onhand} = $form->{short} = 0 if ($form->{itemstatus} eq 'obsolete');
|
|
| 878 |
|
|
| 879 |
my @subcolumns; |
|
| 880 |
foreach my $column (qw(make model)) {
|
|
| 881 |
push @subcolumns, $column if $form->{$column};
|
|
| 882 |
} |
|
| 883 |
if (@subcolumns) {
|
|
| 884 |
$where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE | . (join " AND ", map { "($_ ILIKE ?)"; } @subcolumns) . ")";
|
|
| 885 |
push @values, map { '%' . $form->{$_} . '%' } @subcolumns;
|
|
| 904 |
for ($form->{searchitems}) {
|
|
| 905 |
push @where_tokens, 'p.inventory_accno_id > 0' if /part/; |
|
| 906 |
push @where_tokens, 'p.inventory_accno_id IS NULL' if /service/; |
|
| 907 |
push @where_tokens, 'NOT p.assembly' if /service/; |
|
| 908 |
push @where_tokens, ' p.assembly' if /assembly/; |
|
| 886 | 909 |
} |
| 887 | 910 |
|
| 888 |
if ($form->{l_soldtotal}) {
|
|
| 889 |
$where .= qq| AND (p.id = i.parts_id) AND (i.qty >= 0)|; |
|
| 890 |
$group = qq| GROUP BY p.id, p.partnumber, p.description, p.onhand, p.unit, p.bin, p.sellprice, p.listprice, p.lastcost, p.priceupdate, pg.partsgroup|; |
|
| 911 |
for ($form->{itemstatus}) {
|
|
| 912 |
push @where_tokens, 'p.id NOT IN |
|
| 913 |
(SELECT DISTINCT parts_id FROM invoice UNION |
|
| 914 |
SELECT DISTINCT parts_id FROM assembly UNION |
|
| 915 |
SELECT DISTINCT parts_id FROM orderitems)' if /orphaned/; |
|
| 916 |
push @where_tokens, 'p.onhand = 0' if /orphaned/; |
|
| 917 |
push @where_tokens, 'NOT p.obsolete' if /active/; |
|
| 918 |
push @where_tokens, ' p.obsolete', if /obsolete/; |
|
| 919 |
push @where_tokens, 'p.onhand > 0', if /onhand/; |
|
| 920 |
push @where_tokens, 'p.onhand < p.rop', if /short/; |
|
| 921 |
} |
|
| 922 |
|
|
| 923 |
|
|
| 924 |
my @sort_cols = (@simple_filters, qw(id bin priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate)); |
|
| 925 |
$form->{sort} = 'id' unless grep { $form->{"l_$_"} } grep { $form->{sort} eq $_ } @sort_cols;
|
|
| 926 |
my $order_clause = " ORDER BY $form->{sort} " . ($form->{revers} ? 'DESC' : 'ASC');
|
|
| 927 |
|
|
| 928 |
my $limit_clause = " LIMIT 100" if $form->{top100};
|
|
| 929 |
|
|
| 930 |
#=== joins and complicated filters ========# |
|
| 931 |
|
|
| 932 |
my $bsooqr = $form->{bought} || $form->{sold}
|
|
| 933 |
|| $form->{ordered} || $form->{onorder}
|
|
| 934 |
|| $form->{quoted} || $form->{rfq};
|
|
| 935 |
|
|
| 936 |
my @bsooqr; |
|
| 937 |
push @select_tokens, @qsooqr_flags if $bsooqr; |
|
| 938 |
push @select_tokens, @deliverydate_flags if $bsooqr && $form->{l_deliverydate};
|
|
| 939 |
push @bsooqr_tokens, q|module = 'ir' AND NOT ioi.assemblyitem| if $form->{bought};
|
|
| 940 |
push @bsooqr_tokens, q|module = 'is' AND NOT ioi.assemblyitem| if $form->{sold};
|
|
| 941 |
push @bsooqr_tokens, q|module = 'oe' AND NOT quotation AND cv = 'customer'| if $form->{ordered};
|
|
| 942 |
push @bsooqr_tokens, q|module = 'oe' AND NOT quotation AND cv = 'vendor'| if $form->{onorder};
|
|
| 943 |
push @bsooqr_tokens, q|module = 'oe' AND quotation AND cv = 'customer'| if $form->{quoted};
|
|
| 944 |
push @bsooqr_tokens, q|module = 'oe' AND quotation AND cv = 'vendor'| if $form->{rfq};
|
|
| 945 |
push @where_tokens, join ' OR ', map { "($_)" } @bsooqr_tokens if $bsooqr;
|
|
| 946 |
|
|
| 947 |
$joins_needed{partsgroup} = 1;
|
|
| 948 |
$joins_needed{makemodel} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
|
|
| 949 |
$joins_needed{cv} = 1 if $bsooqr;
|
|
| 950 |
$joins_needed{apoe} = 1 if $joins_needed{cv} || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
|
|
| 951 |
$joins_needed{invoice_oi} = 1 if $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
|
|
| 952 |
|
|
| 953 |
# special case for description search. |
|
| 954 |
# up in the simple filter section the description filter got interpreted as something like: WHERE description ILIKE '%$form->{description}%'
|
|
| 955 |
# now we'd like to search also for the masked description entered in orderitems and invoice, so... |
|
| 956 |
# find the old entries in of @where_tokens and @bind_vars, and adjust them |
|
| 957 |
if ($joins_needed{invoice_oi}) {
|
|
| 958 |
for (my ($wi, $bi) = (0)x2; $wi <= $#where_tokens; $bi++ if $where_tokens[$wi++] =~ /\?/) {
|
|
| 959 |
next unless $where_tokens[$wi] =~ /^description ILIKE/; |
|
| 960 |
splice @where_tokens, $wi, 1, 'p.description ILIKE ? OR ioi.description ILIKE ?'; |
|
| 961 |
splice @bind_vars, $bi, 0, $bind_vars[$bi]; |
|
| 962 |
last; |
|
| 963 |
} |
|
| 891 | 964 |
} |
| 892 | 965 |
|
| 893 |
$limit = qq| LIMIT 100| if ($form->{top100});
|
|
| 894 |
|
|
| 895 |
# connect to database |
|
| 896 |
my $dbh = $form->dbconnect($myconfig); |
|
| 897 |
|
|
| 898 |
my @sort_cols = qw(id partnumber description partsgroup bin priceupdate onhand |
|
| 899 |
invnumber ordnumber quonumber name drawing microfiche |
|
| 900 |
serialnumber soldtotal deliverydate); |
|
| 901 |
|
|
| 902 |
my $sortorder = "partnumber"; |
|
| 903 |
$sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } @sort_cols));
|
|
| 904 |
$sortorder .= " DESC" if ($form->{revers});
|
|
| 905 |
|
|
| 906 |
my $query = ""; |
|
| 907 |
|
|
| 966 |
# now the master trick: soldtotal. |
|
| 908 | 967 |
if ($form->{l_soldtotal}) {
|
| 909 |
$form->{soldtotal} = 'soldtotal';
|
|
| 910 |
$query = |
|
| 911 |
qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, |
|
| 912 |
p.bin, p.sellprice, p.listprice, p.lastcost, |
|
| 913 |
p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal |
|
| 914 |
FROM parts p |
|
| 915 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id), invoice i |
|
| 916 |
WHERE $where |
|
| 917 |
$group |
|
| 918 |
ORDER BY $sortorder $limit|; |
|
| 919 |
} else {
|
|
| 920 |
$query = |
|
| 921 |
qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, |
|
| 922 |
p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight, |
|
| 923 |
p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 924 |
pg.partsgroup |
|
| 925 |
FROM parts p |
|
| 926 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 927 |
WHERE $where |
|
| 928 |
$group |
|
| 929 |
ORDER BY $sortorder $limit|; |
|
| 930 |
} |
|
| 931 |
|
|
| 932 |
my @all_values = @values; |
|
| 933 |
|
|
| 934 |
# rebuild query for bought and sold items |
|
| 935 |
if ( $form->{bought}
|
|
| 936 |
|| $form->{sold}
|
|
| 937 |
|| $form->{onorder}
|
|
| 938 |
|| $form->{ordered}
|
|
| 939 |
|| $form->{rfq}
|
|
| 940 |
|| $form->{quoted}) {
|
|
| 941 |
my $union = ""; |
|
| 942 |
$query = ""; |
|
| 943 |
@all_values = (); |
|
| 944 |
|
|
| 945 |
if ($form->{bought} || $form->{sold}) {
|
|
| 946 |
|
|
| 947 |
my @invvalues = @values; |
|
| 948 |
my $invwhere = "$where"; |
|
| 949 |
$invwhere .= qq| AND i.assemblyitem = '0'|; |
|
| 950 |
|
|
| 951 |
if ($form->{transdatefrom}) {
|
|
| 952 |
$invwhere .= qq| AND a.transdate >= ?|; |
|
| 953 |
push(@invvalues, $form->{transdatefrom});
|
|
| 954 |
} |
|
| 955 |
|
|
| 956 |
if ($form->{transdateto}) {
|
|
| 957 |
$invwhere .= qq| AND a.transdate <= ?|; |
|
| 958 |
push(@invvalues, $form->{transdateto});
|
|
| 959 |
} |
|
| 960 |
|
|
| 961 |
if ($form->{description}) {
|
|
| 962 |
$invwhere .= qq| AND i.description ILIKE ?|; |
|
| 963 |
push(@invvalues, '%' . $form->{description} . '%');
|
|
| 964 |
} |
|
| 965 |
|
|
| 966 |
$flds = |
|
| 967 |
qq|p.id, p.partnumber, i.description, i.serialnumber, |
|
| 968 |
i.qty AS onhand, i.unit, p.bin, i.sellprice, |
|
| 969 |
p.listprice, p.lastcost, p.rop, p.weight, |
|
| 970 |
p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 971 |
pg.partsgroup, |
|
| 972 |
a.invnumber, a.ordnumber, a.quonumber, i.trans_id, |
|
| 973 |
ct.name, i.deliverydate|; |
|
| 974 |
|
|
| 975 |
if ($form->{bought}) {
|
|
| 976 |
$query = |
|
| 977 |
qq|SELECT $flds, 'ir' AS module, '' AS type, 1 AS exchangerate |
|
| 978 |
FROM invoice i |
|
| 979 |
JOIN parts p ON (p.id = i.parts_id) |
|
| 980 |
JOIN ap a ON (a.id = i.trans_id) |
|
| 981 |
JOIN vendor ct ON (a.vendor_id = ct.id) |
|
| 982 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 983 |
WHERE $invwhere|; |
|
| 984 |
|
|
| 985 |
$union = qq| UNION |; |
|
| 986 |
|
|
| 987 |
push(@all_values, @invvalues); |
|
| 988 |
} |
|
| 989 |
|
|
| 990 |
if ($form->{sold}) {
|
|
| 991 |
$query .= |
|
| 992 |
qq|$union |
|
| 993 |
|
|
| 994 |
SELECT $flds, 'is' AS module, '' AS type, 1 As exchangerate |
|
| 995 |
FROM invoice i |
|
| 996 |
JOIN parts p ON (p.id = i.parts_id) |
|
| 997 |
JOIN ar a ON (a.id = i.trans_id) |
|
| 998 |
JOIN customer ct ON (a.customer_id = ct.id) |
|
| 999 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1000 |
WHERE $invwhere|; |
|
| 1001 |
$union = qq| UNION |; |
|
| 1002 |
|
|
| 1003 |
push(@all_values, @invvalues); |
|
| 1004 |
} |
|
| 1005 |
} |
|
| 1006 |
|
|
| 1007 |
if ($form->{onorder} || $form->{ordered}) {
|
|
| 1008 |
my @ordvalues = @values; |
|
| 1009 |
my $ordwhere = $where . qq| AND o.quotation = '0'|; |
|
| 1010 |
|
|
| 1011 |
if ($form->{transdatefrom}) {
|
|
| 1012 |
$ordwhere .= qq| AND o.transdate >= ?|; |
|
| 1013 |
push(@ordvalues, $form->{transdatefrom});
|
|
| 1014 |
} |
|
| 1015 |
|
|
| 1016 |
if ($form->{transdateto}) {
|
|
| 1017 |
$ordwhere .= qq| AND o.transdate <= ?|; |
|
| 1018 |
push(@ordvalues, $form->{transdateto});
|
|
| 1019 |
} |
|
| 1020 |
|
|
| 1021 |
if ($form->{description}) {
|
|
| 1022 |
$ordwhere .= qq| AND oi.description ILIKE ?|; |
|
| 1023 |
push(@ordvalues, '%' . $form->{description} . '%');
|
|
| 1024 |
} |
|
| 1025 |
|
|
| 1026 |
if ($form->{ordered}) {
|
|
| 1027 |
$query .= |
|
| 1028 |
qq|$union |
|
| 1029 |
|
|
| 1030 |
SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber, |
|
| 1031 |
oi.qty AS onhand, oi.unit, p.bin, oi.sellprice, |
|
| 1032 |
p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1033 |
p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1034 |
pg.partsgroup, |
|
| 1035 |
'' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id, |
|
| 1036 |
ct.name, NULL AS deliverydate, |
|
| 1037 |
'oe' AS module, 'sales_order' AS type, |
|
| 1038 |
(SELECT buy FROM exchangerate ex |
|
| 1039 |
WHERE ex.curr = o.curr AND ex.transdate = o.transdate) AS exchangerate |
|
| 1040 |
FROM orderitems oi |
|
| 1041 |
JOIN parts p ON (oi.parts_id = p.id) |
|
| 1042 |
JOIN oe o ON (oi.trans_id = o.id) |
|
| 1043 |
JOIN customer ct ON (o.customer_id = ct.id) |
|
| 1044 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1045 |
WHERE $ordwhere AND (o.customer_id > 0)|; |
|
| 1046 |
$union = qq| UNION |; |
|
| 1047 |
|
|
| 1048 |
push(@all_values, @ordvalues); |
|
| 1049 |
} |
|
| 1050 |
|
|
| 1051 |
if ($form->{onorder}) {
|
|
| 1052 |
$query .= |
|
| 1053 |
qq|$union |
|
| 1054 |
|
|
| 1055 |
SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber, |
|
| 1056 |
oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice, |
|
| 1057 |
p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1058 |
p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1059 |
pg.partsgroup, |
|
| 1060 |
'' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id, |
|
| 1061 |
ct.name, NULL AS deliverydate, |
|
| 1062 |
'oe' AS module, 'purchase_order' AS type, |
|
| 1063 |
(SELECT sell FROM exchangerate ex |
|
| 1064 |
WHERE ex.curr = o.curr AND (ex.transdate = o.transdate)) AS exchangerate |
|
| 1065 |
FROM orderitems oi |
|
| 1066 |
JOIN parts p ON (oi.parts_id = p.id) |
|
| 1067 |
JOIN oe o ON (oi.trans_id = o.id) |
|
| 1068 |
JOIN vendor ct ON (o.vendor_id = ct.id) |
|
| 1069 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1070 |
WHERE $ordwhere AND (o.vendor_id > 0)|; |
|
| 1071 |
$union = qq| UNION |; |
|
| 1072 |
|
|
| 1073 |
push(@all_values, @ordvalues); |
|
| 1074 |
} |
|
| 1075 |
|
|
| 1076 |
} |
|
| 1077 |
|
|
| 1078 |
if ($form->{rfq} || $form->{quoted}) {
|
|
| 1079 |
my $quowhere = $where . qq| AND o.quotation = '1'|; |
|
| 1080 |
my @quovalues = @values; |
|
| 1081 |
|
|
| 1082 |
if ($form->{transdatefrom}) {
|
|
| 1083 |
$quowhere .= qq| AND o.transdate >= ?|; |
|
| 1084 |
push(@quovalues, $form->{transdatefrom});
|
|
| 1085 |
} |
|
| 1086 |
|
|
| 1087 |
if ($form->{transdateto}) {
|
|
| 1088 |
$quowhere .= qq| AND o.transdate <= ?|; |
|
| 1089 |
push(@quovalues, $form->{transdateto});
|
|
| 1090 |
} |
|
| 1091 |
|
|
| 1092 |
if ($form->{description}) {
|
|
| 1093 |
$quowhere .= qq| AND oi.description ILIKE ?|; |
|
| 1094 |
push(@quovalues, '%' . $form->{description} . '%');
|
|
| 1095 |
} |
|
| 1096 |
|
|
| 1097 |
if ($form->{quoted}) {
|
|
| 1098 |
$query .= |
|
| 1099 |
qq|$union |
|
| 1100 |
|
|
| 1101 |
SELECT |
|
| 1102 |
p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber, |
|
| 1103 |
oi.qty AS onhand, oi.unit, p.bin, oi.sellprice, |
|
| 1104 |
p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1105 |
p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1106 |
pg.partsgroup, |
|
| 1107 |
'' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id, |
|
| 1108 |
ct.name, NULL AS deliverydate, 'oe' AS module, 'sales_quotation' AS type, |
|
| 1109 |
(SELECT buy FROM exchangerate ex |
|
| 1110 |
WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate |
|
| 1111 |
FROM orderitems oi |
|
| 1112 |
JOIN parts p ON (oi.parts_id = p.id) |
|
| 1113 |
JOIN oe o ON (oi.trans_id = o.id) |
|
| 1114 |
JOIN customer ct ON (o.customer_id = ct.id) |
|
| 1115 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1116 |
WHERE $quowhere |
|
| 1117 |
AND o.customer_id > 0|; |
|
| 1118 |
$union = qq| UNION |; |
|
| 1119 |
|
|
| 1120 |
push(@all_values, @quovalues); |
|
| 1121 |
} |
|
| 1122 |
|
|
| 1123 |
if ($form->{rfq}) {
|
|
| 1124 |
$query .= |
|
| 1125 |
qq|$union |
|
| 1126 |
|
|
| 1127 |
SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber, |
|
| 1128 |
oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice, |
|
| 1129 |
p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1130 |
p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1131 |
pg.partsgroup, |
|
| 1132 |
'' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id, |
|
| 1133 |
ct.name, NULL AS deliverydate, |
|
| 1134 |
'oe' AS module, 'request_quotation' AS type, |
|
| 1135 |
(SELECT sell FROM exchangerate ex |
|
| 1136 |
WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate |
|
| 1137 |
FROM orderitems oi |
|
| 1138 |
JOIN parts p ON (oi.parts_id = p.id) |
|
| 1139 |
JOIN oe o ON (oi.trans_id = o.id) |
|
| 1140 |
JOIN vendor ct ON (o.vendor_id = ct.id) |
|
| 1141 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1142 |
WHERE $quowhere |
|
| 1143 |
AND o.vendor_id > 0|; |
|
| 1144 |
|
|
| 1145 |
push(@all_values, @quovalues); |
|
| 1146 |
} |
|
| 1147 |
|
|
| 1148 |
} |
|
| 1149 |
$query .= qq| ORDER BY | . $sortorder; |
|
| 1150 |
|
|
| 968 |
push @where_tokens, 'ioi.qty >= 0'; |
|
| 969 |
push @group_tokens, @select_tokens; |
|
| 970 |
push @select_tokens, 'SUM(ioi.qty) AS soldtotal'; |
|
| 1151 | 971 |
} |
| 1152 | 972 |
|
| 1153 |
$form->{parts} = selectall_hashref_query($form, $dbh, $query, @all_values);
|
|
| 973 |
#============= build query ================#
|
|
| 1154 | 974 |
|
| 975 |
my %table_prefix = ( |
|
| 976 |
deliverydate => 'apoe.', serialnumber => 'ioi.', |
|
| 977 |
transdate => 'apoe.', trans_id => 'ioi.', |
|
| 978 |
module => 'apoe.', name => 'cv.', |
|
| 979 |
ordnumber => 'apoe.', make => 'mm.', |
|
| 980 |
quonumber => 'apoe.', model => 'mm.', |
|
| 981 |
invnumber => 'apoe.', |
|
| 982 |
'SUM(ioi.qty) AS soldtotal' => ' ', |
|
| 983 |
); |
|
| 984 |
|
|
| 985 |
map { $table_prefix{$_} = 'ioi.' } qw(description serialnumber qty unit) if $joins_needed{invoice_oi};
|
|
| 986 |
|
|
| 987 |
my $select_clause = join ', ', map { ($table_prefix{$_} || "p.") . $_ } @select_tokens;
|
|
| 988 |
my $join_clause = join ' ', @joins{ grep $joins_needed{$_}, @join_order };
|
|
| 989 |
my $where_clause = join ' AND ', map { "($_)" } @where_tokens;
|
|
| 990 |
my $group_clause = ' GROUP BY ' . join ', ', map { ($table_prefix{$_} || "p.") . $_ } @group_tokens;
|
|
| 991 |
|
|
| 992 |
my $query = qq|SELECT DISTINCT $select_clause FROM parts p $join_clause WHERE $where_clause $group_clause $order_clause $limit_clause|; |
|
| 993 |
$form->{parts} = selectall_hashref_query($form, $dbh, $query, @bind_vars);
|
|
| 994 |
|
|
| 995 |
## my $where = qq|1 = 1|; |
|
| 996 |
## my (@values, $var, $flds, $group, $limit); |
|
| 997 |
## |
|
| 998 |
## foreach my $item (qw(partnumber drawing microfiche ean pg.partsgroup)) {
|
|
| 999 |
## my $column = $item; |
|
| 1000 |
## $column =~ s/.*\.//; # get rid of table prefixes |
|
| 1001 |
## if ($form->{$column}) {
|
|
| 1002 |
## $where .= qq| AND ($item ILIKE ?)|; |
|
| 1003 |
## push(@values, "%$form->{$column}%");
|
|
| 1004 |
## } |
|
| 1005 |
## } |
|
| 1006 |
## |
|
| 1007 |
## # special case for description |
|
| 1008 |
## if ($form->{description}
|
|
| 1009 |
## && !( $form->{bought} || $form->{sold} || $form->{onorder}
|
|
| 1010 |
## || $form->{ordered} || $form->{rfq} || $form->{quoted})) {
|
|
| 1011 |
## $where .= qq| AND (p.description ILIKE ?)|; |
|
| 1012 |
## push(@values, "%$form->{description}%");
|
|
| 1013 |
## } |
|
| 1014 |
## |
|
| 1015 |
## # special case for serialnumber |
|
| 1016 |
## if ($form->{l_serialnumber} && $form->{serialnumber}) {
|
|
| 1017 |
## $where .= qq| AND (serialnumber ILIKE ?)|; |
|
| 1018 |
## push(@values, "%$form->{serialnumber}%");
|
|
| 1019 |
## } |
|
| 1020 |
## |
|
| 1021 |
## if ($form->{searchitems} eq 'part') {
|
|
| 1022 |
## $where .= qq| AND (p.inventory_accno_id > 0) |; |
|
| 1023 |
## } |
|
| 1024 |
## |
|
| 1025 |
## if ($form->{searchitems} eq 'assembly') {
|
|
| 1026 |
## $form->{bought} = "";
|
|
| 1027 |
## $where .= qq| AND p.assembly|; |
|
| 1028 |
## } |
|
| 1029 |
## |
|
| 1030 |
## if ($form->{searchitems} eq 'service') {
|
|
| 1031 |
## $where .= qq| AND (p.inventory_accno_id IS NULL) AND NOT (p.assembly = '1')|; |
|
| 1032 |
## # irrelevant for services |
|
| 1033 |
## map { $form->{$_} = '' } qw(make model);
|
|
| 1034 |
## } |
|
| 1035 |
## |
|
| 1036 |
## # items which were never bought, sold or on an order |
|
| 1037 |
## if ($form->{itemstatus} eq 'orphaned') {
|
|
| 1038 |
## map { $form->{$_} = 0 } qw(onhand short bought sold onorder ordered rfq quoted);
|
|
| 1039 |
## map { $form->{$_} = '' } qw(transdatefrom transdateto);
|
|
| 1040 |
## |
|
| 1041 |
## $where .= |
|
| 1042 |
## qq| AND (p.onhand = 0) |
|
| 1043 |
## AND p.id NOT IN |
|
| 1044 |
## ( |
|
| 1045 |
## SELECT DISTINCT parts_id FROM invoice |
|
| 1046 |
## UNION |
|
| 1047 |
## SELECT DISTINCT parts_id FROM assembly |
|
| 1048 |
## UNION |
|
| 1049 |
## SELECT DISTINCT parts_id FROM orderitems |
|
| 1050 |
## )|; |
|
| 1051 |
## } |
|
| 1052 |
## |
|
| 1053 |
## my %status2condition = ( |
|
| 1054 |
## active => " AND (p.obsolete = '0')", |
|
| 1055 |
## obsolete => " AND (p.obsolete = '1')", |
|
| 1056 |
## onhand => " AND (p.onhand > 0)", |
|
| 1057 |
## short => " AND (p.onhand < p.rop)", |
|
| 1058 |
## ); |
|
| 1059 |
## $where .= $status2condition{$form->{itemstatus}};
|
|
| 1060 |
## |
|
| 1061 |
## $form->{onhand} = $form->{short} = 0 if ($form->{itemstatus} eq 'obsolete');
|
|
| 1062 |
## |
|
| 1063 |
## my @subcolumns; |
|
| 1064 |
## foreach my $column (qw(make model)) {
|
|
| 1065 |
## push @subcolumns, $column if $form->{$column};
|
|
| 1066 |
## } |
|
| 1067 |
## if (@subcolumns) {
|
|
| 1068 |
## $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE | . (join " AND ", map { "($_ ILIKE ?)"; } @subcolumns) . ")";
|
|
| 1069 |
## push @values, map { '%' . $form->{$_} . '%' } @subcolumns;
|
|
| 1070 |
## } |
|
| 1071 |
## |
|
| 1072 |
## if ($form->{l_soldtotal}) {
|
|
| 1073 |
## $where .= qq| AND (p.id = i.parts_id) AND (i.qty >= 0)|; |
|
| 1074 |
## $group = qq| GROUP BY p.id, p.partnumber, p.description, p.onhand, p.unit, p.bin, p.sellprice, p.listprice, p.lastcost, p.priceupdate, pg.partsgroup|; |
|
| 1075 |
## } |
|
| 1076 |
## |
|
| 1077 |
## $limit = qq| LIMIT 100| if ($form->{top100});
|
|
| 1078 |
## |
|
| 1079 |
## # connect to database |
|
| 1080 |
## my $dbh = $form->dbconnect($myconfig); |
|
| 1081 |
## |
|
| 1082 |
## my @sort_cols = qw(id partnumber description partsgroup bin priceupdate onhand |
|
| 1083 |
## invnumber ordnumber quonumber name drawing microfiche |
|
| 1084 |
## serialnumber soldtotal deliverydate); |
|
| 1085 |
## |
|
| 1086 |
## my $sortorder = "partnumber"; |
|
| 1087 |
## $sortorder = $form->{sort} if ($form->{sort} && grep({ $_ eq $form->{sort} } @sort_cols));
|
|
| 1088 |
## $sortorder .= " DESC" if ($form->{revers});
|
|
| 1089 |
## |
|
| 1090 |
## my $query = ""; |
|
| 1091 |
## |
|
| 1092 |
## if ($form->{l_soldtotal}) {
|
|
| 1093 |
## $form->{soldtotal} = 'soldtotal';
|
|
| 1094 |
## $query = |
|
| 1095 |
## qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, |
|
| 1096 |
## p.bin, p.sellprice, p.listprice, p.lastcost, |
|
| 1097 |
## p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal |
|
| 1098 |
## FROM parts p |
|
| 1099 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id), invoice i |
|
| 1100 |
## WHERE $where |
|
| 1101 |
## $group |
|
| 1102 |
## ORDER BY $sortorder $limit|; |
|
| 1103 |
## } else {
|
|
| 1104 |
## $query = |
|
| 1105 |
## qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, |
|
| 1106 |
## p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1107 |
## p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1108 |
## pg.partsgroup |
|
| 1109 |
## FROM parts p |
|
| 1110 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1111 |
## WHERE $where |
|
| 1112 |
## $group |
|
| 1113 |
## ORDER BY $sortorder $limit|; |
|
| 1114 |
## } |
|
| 1115 |
## |
|
| 1116 |
## my @all_values = @values; |
|
| 1117 |
## |
|
| 1118 |
## # rebuild query for bought and sold items |
|
| 1119 |
## if ( $form->{bought}
|
|
| 1120 |
## || $form->{sold}
|
|
| 1121 |
## || $form->{onorder}
|
|
| 1122 |
## || $form->{ordered}
|
|
| 1123 |
## || $form->{rfq}
|
|
| 1124 |
## || $form->{quoted}) {
|
|
| 1125 |
## my $union = ""; |
|
| 1126 |
## $query = ""; |
|
| 1127 |
## @all_values = (); |
|
| 1128 |
## |
|
| 1129 |
## if ($form->{bought} || $form->{sold}) {
|
|
| 1130 |
## |
|
| 1131 |
## my @invvalues = @values; |
|
| 1132 |
## my $invwhere = "$where"; |
|
| 1133 |
# $invwhere .= qq| AND i.assemblyitem = '0'|; |
|
| 1134 |
## |
|
| 1135 |
## if ($form->{transdatefrom}) {
|
|
| 1136 |
## $invwhere .= qq| AND a.transdate >= ?|; |
|
| 1137 |
## push(@invvalues, $form->{transdatefrom});
|
|
| 1138 |
## } |
|
| 1139 |
## |
|
| 1140 |
## if ($form->{transdateto}) {
|
|
| 1141 |
## $invwhere .= qq| AND a.transdate <= ?|; |
|
| 1142 |
## push(@invvalues, $form->{transdateto});
|
|
| 1143 |
## } |
|
| 1144 |
## |
|
| 1145 |
## if ($form->{description}) {
|
|
| 1146 |
## $invwhere .= qq| AND i.description ILIKE ?|; |
|
| 1147 |
## push(@invvalues, '%' . $form->{description} . '%');
|
|
| 1148 |
## } |
|
| 1149 |
## |
|
| 1150 |
## $flds = |
|
| 1151 |
## qq|p.id, p.partnumber, i.description, i.serialnumber, |
|
| 1152 |
# i.qty AS onhand, i.unit, p.bin, i.sellprice, |
|
| 1153 |
## p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1154 |
## p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1155 |
## pg.partsgroup, |
|
| 1156 |
## a.invnumber, a.ordnumber, a.quonumber, i.trans_id, |
|
| 1157 |
## ct.name, i.deliverydate|; |
|
| 1158 |
## |
|
| 1159 |
## if ($form->{bought}) {
|
|
| 1160 |
## $query = |
|
| 1161 |
## qq|SELECT $flds, 'ir' AS module, '' AS type, 1 AS exchangerate |
|
| 1162 |
## FROM invoice i |
|
| 1163 |
## JOIN parts p ON (p.id = i.parts_id) |
|
| 1164 |
## JOIN ap a ON (a.id = i.trans_id) |
|
| 1165 |
## JOIN vendor ct ON (a.vendor_id = ct.id) |
|
| 1166 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1167 |
## WHERE $invwhere|; |
|
| 1168 |
## |
|
| 1169 |
## $union = qq| UNION |; |
|
| 1170 |
## |
|
| 1171 |
## push(@all_values, @invvalues); |
|
| 1172 |
## } |
|
| 1173 |
## |
|
| 1174 |
## if ($form->{sold}) {
|
|
| 1175 |
## $query .= |
|
| 1176 |
## qq|$union |
|
| 1177 |
## |
|
| 1178 |
## SELECT $flds, 'is' AS module, '' AS type, 1 As exchangerate |
|
| 1179 |
## FROM invoice i |
|
| 1180 |
## JOIN parts p ON (p.id = i.parts_id) |
|
| 1181 |
## JOIN ar a ON (a.id = i.trans_id) |
|
| 1182 |
## JOIN customer ct ON (a.customer_id = ct.id) |
|
| 1183 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1184 |
## WHERE $invwhere|; |
|
| 1185 |
## $union = qq| UNION |; |
|
| 1186 |
## |
|
| 1187 |
## push(@all_values, @invvalues); |
|
| 1188 |
## } |
|
| 1189 |
## } |
|
| 1190 |
## |
|
| 1191 |
## if ($form->{onorder} || $form->{ordered}) {
|
|
| 1192 |
## my @ordvalues = @values; |
|
| 1193 |
## my $ordwhere = $where . qq| AND o.quotation = '0'|; |
|
| 1194 |
## |
|
| 1195 |
## if ($form->{transdatefrom}) {
|
|
| 1196 |
## $ordwhere .= qq| AND o.transdate >= ?|; |
|
| 1197 |
## push(@ordvalues, $form->{transdatefrom});
|
|
| 1198 |
## } |
|
| 1199 |
## |
|
| 1200 |
## if ($form->{transdateto}) {
|
|
| 1201 |
## $ordwhere .= qq| AND o.transdate <= ?|; |
|
| 1202 |
## push(@ordvalues, $form->{transdateto});
|
|
| 1203 |
## } |
|
| 1204 |
## |
|
| 1205 |
## if ($form->{description}) {
|
|
| 1206 |
## $ordwhere .= qq| AND oi.description ILIKE ?|; |
|
| 1207 |
## push(@ordvalues, '%' . $form->{description} . '%');
|
|
| 1208 |
## } |
|
| 1209 |
## |
|
| 1210 |
## if ($form->{ordered}) {
|
|
| 1211 |
## $query .= |
|
| 1212 |
## qq|$union |
|
| 1213 |
## |
|
| 1214 |
## SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber, |
|
| 1215 |
## oi.qty AS onhand, oi.unit, p.bin, oi.sellprice, |
|
| 1216 |
## p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1217 |
## p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1218 |
## pg.partsgroup, |
|
| 1219 |
## '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id, |
|
| 1220 |
## ct.name, NULL AS deliverydate, |
|
| 1221 |
## 'oe' AS module, 'sales_order' AS type, |
|
| 1222 |
## (SELECT buy FROM exchangerate ex |
|
| 1223 |
## WHERE ex.curr = o.curr AND ex.transdate = o.transdate) AS exchangerate |
|
| 1224 |
## FROM orderitems oi |
|
| 1225 |
## JOIN parts p ON (oi.parts_id = p.id) |
|
| 1226 |
## JOIN oe o ON (oi.trans_id = o.id) |
|
| 1227 |
## JOIN customer ct ON (o.customer_id = ct.id) |
|
| 1228 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1229 |
## WHERE $ordwhere AND (o.customer_id > 0)|; |
|
| 1230 |
## $union = qq| UNION |; |
|
| 1231 |
## |
|
| 1232 |
## push(@all_values, @ordvalues); |
|
| 1233 |
## } |
|
| 1234 |
## |
|
| 1235 |
## if ($form->{onorder}) {
|
|
| 1236 |
## $query .= |
|
| 1237 |
## qq|$union |
|
| 1238 |
## |
|
| 1239 |
## SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber, |
|
| 1240 |
## oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice, |
|
| 1241 |
## p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1242 |
## p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1243 |
## pg.partsgroup, |
|
| 1244 |
## '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id, |
|
| 1245 |
## ct.name, NULL AS deliverydate, |
|
| 1246 |
## 'oe' AS module, 'purchase_order' AS type, |
|
| 1247 |
## (SELECT sell FROM exchangerate ex |
|
| 1248 |
## WHERE ex.curr = o.curr AND (ex.transdate = o.transdate)) AS exchangerate |
|
| 1249 |
## FROM orderitems oi |
|
| 1250 |
## JOIN parts p ON (oi.parts_id = p.id) |
|
| 1251 |
## JOIN oe o ON (oi.trans_id = o.id) |
|
| 1252 |
## JOIN vendor ct ON (o.vendor_id = ct.id) |
|
| 1253 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1254 |
## WHERE $ordwhere AND (o.vendor_id > 0)|; |
|
| 1255 |
## $union = qq| UNION |; |
|
| 1256 |
## |
|
| 1257 |
## push(@all_values, @ordvalues); |
|
| 1258 |
## } |
|
| 1259 |
## |
|
| 1260 |
## } |
|
| 1261 |
## |
|
| 1262 |
## if ($form->{rfq} || $form->{quoted}) {
|
|
| 1263 |
## my $quowhere = $where . qq| AND o.quotation = '1'|; |
|
| 1264 |
## my @quovalues = @values; |
|
| 1265 |
## |
|
| 1266 |
## if ($form->{transdatefrom}) {
|
|
| 1267 |
## $quowhere .= qq| AND o.transdate >= ?|; |
|
| 1268 |
## push(@quovalues, $form->{transdatefrom});
|
|
| 1269 |
## } |
|
| 1270 |
## |
|
| 1271 |
## if ($form->{transdateto}) {
|
|
| 1272 |
## $quowhere .= qq| AND o.transdate <= ?|; |
|
| 1273 |
## push(@quovalues, $form->{transdateto});
|
|
| 1274 |
## } |
|
| 1275 |
## |
|
| 1276 |
## if ($form->{description}) {
|
|
| 1277 |
## $quowhere .= qq| AND oi.description ILIKE ?|; |
|
| 1278 |
## push(@quovalues, '%' . $form->{description} . '%');
|
|
| 1279 |
## } |
|
| 1280 |
## |
|
| 1281 |
## if ($form->{quoted}) {
|
|
| 1282 |
## $query .= |
|
| 1283 |
## qq|$union |
|
| 1284 |
## |
|
| 1285 |
## SELECT |
|
| 1286 |
## p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber, |
|
| 1287 |
## oi.qty AS onhand, oi.unit, p.bin, oi.sellprice, |
|
| 1288 |
## p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1289 |
## p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1290 |
## pg.partsgroup, |
|
| 1291 |
## '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id, |
|
| 1292 |
## ct.name, NULL AS deliverydate, 'oe' AS module, 'sales_quotation' AS type, |
|
| 1293 |
## (SELECT buy FROM exchangerate ex |
|
| 1294 |
## WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate |
|
| 1295 |
## FROM orderitems oi |
|
| 1296 |
## JOIN parts p ON (oi.parts_id = p.id) |
|
| 1297 |
## JOIN oe o ON (oi.trans_id = o.id) |
|
| 1298 |
## JOIN customer ct ON (o.customer_id = ct.id) |
|
| 1299 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1300 |
## WHERE $quowhere |
|
| 1301 |
## AND o.customer_id > 0|; |
|
| 1302 |
## $union = qq| UNION |; |
|
| 1303 |
## |
|
| 1304 |
## push(@all_values, @quovalues); |
|
| 1305 |
## } |
|
| 1306 |
## |
|
| 1307 |
## if ($form->{rfq}) {
|
|
| 1308 |
## $query .= |
|
| 1309 |
## qq|$union |
|
| 1310 |
## |
|
| 1311 |
## SELECT p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber, |
|
| 1312 |
## oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice, |
|
| 1313 |
## p.listprice, p.lastcost, p.rop, p.weight, |
|
| 1314 |
## p.priceupdate, p.image, p.drawing, p.microfiche, |
|
| 1315 |
## pg.partsgroup, |
|
| 1316 |
## '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id, |
|
| 1317 |
## ct.name, NULL AS deliverydate, |
|
| 1318 |
## 'oe' AS module, 'request_quotation' AS type, |
|
| 1319 |
## (SELECT sell FROM exchangerate ex |
|
| 1320 |
## WHERE (ex.curr = o.curr) AND (ex.transdate = o.transdate)) AS exchangerate |
|
| 1321 |
## FROM orderitems oi |
|
| 1322 |
## JOIN parts p ON (oi.parts_id = p.id) |
|
| 1323 |
## JOIN oe o ON (oi.trans_id = o.id) |
|
| 1324 |
## JOIN vendor ct ON (o.vendor_id = ct.id) |
|
| 1325 |
## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
|
| 1326 |
## WHERE $quowhere |
|
| 1327 |
## AND o.vendor_id > 0|; |
|
| 1328 |
## |
|
| 1329 |
## push(@all_values, @quovalues); |
|
| 1330 |
## } |
|
| 1331 |
## |
|
| 1332 |
## } |
|
| 1333 |
## $query .= qq| ORDER BY | . $sortorder; |
|
| 1334 |
## |
|
| 1335 |
## } |
|
| 1336 |
## |
|
| 1337 |
## $form->{parts} = selectall_hashref_query($form, $dbh, $query, @all_values);
|
|
| 1338 |
# |
|
| 1155 | 1339 |
my @assemblies; |
| 1156 | 1340 |
# include individual items for assemblies |
| 1157 | 1341 |
if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
|
Auch abrufbar als: Unified diff
Neues Warenberichte Backend.
Von Grund auf neu geschrieben, unter Beruecksichtigung der folgenden Grundsaetze:
+ ein Query fuer alles
+ Query wird aus Tokens gebaut -> weniger anfaellig fuer SQL Fehler
+ Kombinationen die vorher nicht erlaubt waren und per Blacklist gefiltert wurden produzieren jetzt ein Ergebnis, dass in vielen Faellen sogar interpretiert werden kann.
+ Durch Tabledesign (hoffentlich) leichter erweiterbar als die alte Version
+ fixt viele Bugs die mit der alten Version bestanden.
Folgende Funktionen sind noch unzureichend getestet oder noch nicht implementiert:
- Assembly Unterstuetzung ist uebernommen aus der alten Version und wird nachgereicht.
- Summe der orderitem.qty / invoice.qty als onhand zu interpretieren ist sehr gewagt solange nicht auf die Einheiten eingegangen wird.
Die Funktionalitaet ist zwar erhalten, aber nicht vollstaendig.