Revision 72528ede
Von Tamino Steinert vor mehr als 2 Jahren hinzugefügt
- ID 72528edece18f8ac73ff702e83050c674db69b38
- Vorgänger 72b80f91
| SL/IC.pm | ||
|---|---|---|
|
my $query =
|
||
|
qq|SELECT p.id, p.partnumber, p.description, p.sellprice,
|
||
|
p.classification_id,
|
||
|
p.weight, p.onhand, p.unit, pg.partsgroup, p.lastcost,
|
||
|
p.weight, onhands.onhand, p.unit, pg.partsgroup, p.lastcost,
|
||
|
p.price_factor_id, pfac.factor AS price_factor, p.notes as longdescription
|
||
|
FROM parts p
|
||
|
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
|
||
|
LEFT JOIN price_factors pfac ON pfac.id = p.price_factor_id
|
||
|
LEFT JOIN onhands ON (onhands.parts_id = p.id)
|
||
|
WHERE $where|;
|
||
|
$form->{item_list} = selectall_hashref_query($form, SL::DB->client->dbh, $query, @values);
|
||
|
|
||
| ... | ... | |
|
cv => 'cv.',
|
||
|
"ioi.id" => ' ',
|
||
|
"ioi.ioi" => ' ',
|
||
|
onhand => 'onhands.',
|
||
|
);
|
||
|
|
||
|
# if the join condition in these blocks are met, the column
|
||
| ... | ... | |
|
(SELECT DISTINCT parts_id FROM invoice UNION
|
||
|
SELECT DISTINCT parts_id FROM assembly UNION
|
||
|
SELECT DISTINCT parts_id FROM orderitems)' if /orphaned/;
|
||
|
push @where_tokens, 'p.onhand = 0' if /orphaned/;
|
||
|
push @where_tokens, 'onhands.onhand = 0' if /orphaned/;
|
||
|
push @where_tokens, 'NOT p.obsolete' if /active/;
|
||
|
push @where_tokens, ' p.obsolete', if /obsolete/;
|
||
|
push @where_tokens, 'p.onhand > 0', if /onhand/;
|
||
|
push @where_tokens, 'p.onhand < p.rop', if /short/;
|
||
|
push @where_tokens, 'onhands.onhand > 0', if /onhand/;
|
||
|
push @where_tokens, 'onhands.onhand < p.rop', if /short/;
|
||
|
}
|
||
|
|
||
|
my $q_assembly_lastcost =
|
||
| ... | ... | |
|
my $query = <<" SQL";
|
||
|
SELECT DISTINCT $select_clause
|
||
|
FROM parts p
|
||
|
LEFT JOIN onhands ON (onhands.parts_id = p.id)
|
||
|
$join_clause
|
||
|
WHERE $where_clause
|
||
|
$group_clause
|
||
| ... | ... | |
|
|
||
|
my $dbh = $form->get_standard_dbh($myconfig);
|
||
|
|
||
|
my $query = qq|SELECT * FROM parts WHERE id IN (| . join(', ', ('?') x scalar(@ids)) . qq|)|;
|
||
|
my $query = qq|SELECT p.* onhands.onhand FROM parts p LEFT JOIN onhands ON (onhands.parts_id = p.id) WHERE p.id IN (| . join(', ', ('?') x scalar(@ids)) . qq|)|;
|
||
|
|
||
|
my $info = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
|
||
|
|
||
Auch abrufbar als: Unified diff
Separate Tabelle für onhand (onhands) für Artikel