Revision 2efc8cbc
Von Sven Schöling vor mehr als 17 Jahren hinzugefügt
| SL/IC.pm | ||
|---|---|---|
| 782 | 782 |
$main::lxdebug->leave_sub(); |
| 783 | 783 |
} |
| 784 | 784 |
|
| 785 |
# |
|
| 786 |
# Report for Wares. |
|
| 787 |
# Warning, deep magic ahead. |
|
| 788 |
# This function gets all parts from the database according to the filters specified |
|
| 789 |
# |
|
| 790 |
# filter strings: |
|
| 791 |
# partnumber ean description partsgroup serialnumber make model drawing microfiche |
|
| 792 |
# transdatefrom transdateto sort |
|
| 793 |
# |
|
| 794 |
# exclusives: |
|
| 795 |
# itemstatus = active | onhand | short | obsolete | orphaned |
|
| 796 |
# searchitems = part | assembly | service |
|
| 797 |
# |
|
| 798 |
# column flags: |
|
| 799 |
# 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 |
# |
|
| 801 |
# 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 |
|
| 805 |
# |
|
| 785 | 806 |
sub all_parts {
|
| 786 | 807 |
$main::lxdebug->enter_sub(); |
| 787 | 808 |
|
| ... | ... | |
| 792 | 813 |
|
| 793 | 814 |
foreach my $item (qw(partnumber drawing microfiche ean pg.partsgroup)) {
|
| 794 | 815 |
my $column = $item; |
| 795 |
$column =~ s/.*\.//; |
|
| 816 |
$column =~ s/.*\.//; # get rid of table prefixes
|
|
| 796 | 817 |
if ($form->{$column}) {
|
| 797 |
$where .= qq| AND (${item} ILIKE ?)|;
|
|
| 798 |
push(@values, '%' . $form->{$column} . '%');
|
|
| 818 |
$where .= qq| AND ($item ILIKE ?)|;
|
|
| 819 |
push(@values, "%$form->{$column}%");
|
|
| 799 | 820 |
} |
| 800 | 821 |
} |
| 801 | 822 |
|
| ... | ... | |
| 804 | 825 |
&& !( $form->{bought} || $form->{sold} || $form->{onorder}
|
| 805 | 826 |
|| $form->{ordered} || $form->{rfq} || $form->{quoted})) {
|
| 806 | 827 |
$where .= qq| AND (p.description ILIKE ?)|; |
| 807 |
push(@values, '%' . $form->{description} . '%');
|
|
| 828 |
push(@values, "%$form->{description}%");
|
|
| 808 | 829 |
} |
| 809 | 830 |
|
| 810 | 831 |
# special case for serialnumber |
| 811 | 832 |
if ($form->{l_serialnumber} && $form->{serialnumber}) {
|
| 812 |
$where .= qq| AND (p.serialnumber ILIKE ?)|;
|
|
| 813 |
push(@values, '%' . $form->{serialnumber} . '%');
|
|
| 833 |
$where .= qq| AND (serialnumber ILIKE ?)|; |
|
| 834 |
push(@values, "%$form->{serialnumber}%");
|
|
| 814 | 835 |
} |
| 815 | 836 |
|
| 816 | 837 |
if ($form->{searchitems} eq 'part') {
|
| ... | ... | |
| 824 | 845 |
|
| 825 | 846 |
if ($form->{searchitems} eq 'service') {
|
| 826 | 847 |
$where .= qq| AND (p.inventory_accno_id IS NULL) AND NOT (p.assembly = '1')|; |
| 827 |
|
|
| 828 | 848 |
# irrelevant for services |
| 829 |
$form->{make} = $form->{model} = "";
|
|
| 849 |
map { $form->{$_} = '' } qw(make model);
|
|
| 830 | 850 |
} |
| 831 | 851 |
|
| 832 | 852 |
# items which were never bought, sold or on an order |
| 833 | 853 |
if ($form->{itemstatus} eq 'orphaned') {
|
| 834 |
$form->{onhand} = $form->{short} = 0;
|
|
| 835 |
$form->{bought} = $form->{sold} = 0;
|
|
| 836 |
$form->{onorder} = $form->{ordered} = 0;
|
|
| 837 |
$form->{rfq} = $form->{quoted} = 0;
|
|
| 838 |
|
|
| 839 |
$form->{transdatefrom} = $form->{transdateto} = "";
|
|
| 854 |
map { $form->{$_} = 0 } qw(onhand short bought sold onorder ordered rfq quoted);
|
|
| 855 |
map { $form->{$_} = '' } qw(transdatefrom transdateto);
|
|
| 840 | 856 |
|
| 841 | 857 |
$where .= |
| 842 | 858 |
qq| AND (p.onhand = 0) |
| ... | ... | |
| 850 | 866 |
)|; |
| 851 | 867 |
} |
| 852 | 868 |
|
| 853 |
if ($form->{itemstatus} eq 'active') {
|
|
| 854 |
$where .= qq| AND (p.obsolete = '0')|; |
|
| 855 |
} elsif ($form->{itemstatus} eq 'obsolete') {
|
|
| 856 |
$where .= qq| AND (p.obsolete = '1')|; |
|
| 857 |
$form->{onhand} = $form->{short} = 0;
|
|
| 858 |
} elsif ($form->{itemstatus} eq 'onhand') {
|
|
| 859 |
$where .= qq| AND (p.onhand > 0)|; |
|
| 860 |
} elsif ($form->{itemstatus} eq 'short') {
|
|
| 861 |
$where .= qq| AND (p.onhand < p.rop)|; |
|
| 862 |
} |
|
| 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');
|
|
| 863 | 878 |
|
| 864 | 879 |
my @subcolumns; |
| 865 | 880 |
foreach my $column (qw(make model)) {
|
| 866 | 881 |
push @subcolumns, $column if $form->{$column};
|
| 867 | 882 |
} |
| 868 | 883 |
if (@subcolumns) {
|
| 869 |
$where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE |; |
|
| 870 |
$where .= join " AND ", map { "($_ ILIKE ?)"; } @subcolumns;
|
|
| 871 |
$where .= qq|)|; |
|
| 884 |
$where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE | . (join " AND ", map { "($_ ILIKE ?)"; } @subcolumns) . ")";
|
|
| 872 | 885 |
push @values, map { '%' . $form->{$_} . '%' } @subcolumns;
|
| 873 | 886 |
} |
| 874 | 887 |
|
| 875 | 888 |
if ($form->{l_soldtotal}) {
|
| 876 | 889 |
$where .= qq| AND (p.id = i.parts_id) AND (i.qty >= 0)|; |
| 877 |
$group = |
|
| 878 |
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|; |
|
| 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|; |
|
| 879 | 891 |
} |
| 880 | 892 |
|
| 881 | 893 |
$limit = qq| LIMIT 100| if ($form->{top100});
|
| ... | ... | |
| 896 | 908 |
if ($form->{l_soldtotal}) {
|
| 897 | 909 |
$form->{soldtotal} = 'soldtotal';
|
| 898 | 910 |
$query = |
| 899 |
qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, p.bin, p.sellprice, p.listprice, |
|
| 900 |
p.lastcost, p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal FROM parts |
|
| 901 |
p LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id), invoice i |
|
| 902 |
WHERE $where |
|
| 903 |
$group |
|
| 904 |
ORDER BY $sortorder |
|
| 905 |
$limit|; |
|
| 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|; |
|
| 906 | 919 |
} else {
|
| 907 | 920 |
$query = |
| 908 | 921 |
qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, |
| ... | ... | |
| 913 | 926 |
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) |
| 914 | 927 |
WHERE $where |
| 915 | 928 |
$group |
| 916 |
ORDER BY $sortorder |
|
| 917 |
$limit|; |
|
| 929 |
ORDER BY $sortorder $limit|; |
|
| 918 | 930 |
} |
| 919 | 931 |
|
| 920 | 932 |
my @all_values = @values; |
Auch abrufbar als: Unified diff
ic.pl auf use strict umgeschrieben (experimentell)
ic.pl generate_reports konsistenzchecks umgeschirben, und kommentiert, wird im weiteren mit verbesserter datenbanklogik
verwendet.
SL/IC.pm: kosmetik