Revision 72528ede
Von Tamino Steinert vor mehr als 2 Jahren hinzugefügt
- ID 72528edece18f8ac73ff702e83050c674db69b38
- Vorgänger 72b80f91
| SL/Controller/PartsPriceUpdate.pm | ||
|---|---|---|
|
# items which were never bought, sold or on an order
|
||
|
if ($filter->{itemstatus} eq 'orphaned') {
|
||
|
$where .=
|
||
|
qq| AND (p.onhand = 0)
|
||
|
qq| AND (onhands.onhand = 0)
|
||
|
AND p.id NOT IN
|
||
|
(
|
||
|
SELECT DISTINCT parts_id FROM invoice
|
||
| ... | ... | |
|
$where .= qq| AND p.obsolete = '1'|;
|
||
|
|
||
|
} elsif ($filter->{itemstatus} eq 'onhand') {
|
||
|
$where .= qq| AND p.onhand > 0|;
|
||
|
$where .= qq| AND onhands.onhand > 0|;
|
||
|
|
||
|
} elsif ($filter->{itemstatus} eq 'short') {
|
||
|
$where .= qq| AND p.onhand < p.rop|;
|
||
|
$where .= qq| AND onhands.onhand < p.rop|;
|
||
|
|
||
|
}
|
||
|
|
||
| ... | ... | |
|
(SELECT p.id
|
||
|
FROM parts p
|
||
|
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
|
||
|
LEFT JOIN onhands ON (onhands.parts_id = p.id)
|
||
|
WHERE $where)|;
|
||
|
my ($result) = selectfirst_array_query($::form, $dbh, $query, @where_values);
|
||
|
$num_updated += $result if (0 <= $result);
|
||
| ... | ... | |
|
(SELECT p.id
|
||
|
FROM parts p
|
||
|
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
|
||
|
LEFT JOIN onhands ON (onhands.parts_id = p.id)
|
||
|
WHERE $where)
|
||
|
AND pricegroup_id IN (@{[ join ',', ('?')x@ids ]})|;
|
||
|
|
||
| ... | ... | |
|
(SELECT p.id
|
||
|
FROM parts p
|
||
|
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
|
||
|
LEFT JOIN onhands ON (onhands.parts_id = p.id)
|
||
|
WHERE $where)|;
|
||
|
my $result = do_query($::form, $dbh, $query, $value, @where_values);
|
||
|
$num_updated += $result if 0 <= $result;
|
||
| ... | ... | |
|
(SELECT p.id
|
||
|
FROM parts p
|
||
|
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
|
||
|
LEFT JOIN onhands ON (onhands.parts_id = p.id)
|
||
|
WHERE $where) AND (pricegroup_id = ?)|;
|
||
|
my $sth_add = prepare_query($::form, $dbh, $q_add);
|
||
|
|
||
| ... | ... | |
|
(SELECT p.id
|
||
|
FROM parts p
|
||
|
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
|
||
|
LEFT JOIN onhands ON (onhands.parts_id = p.id)
|
||
|
WHERE $where) AND (pricegroup_id = ?)|;
|
||
|
my $sth_multiply = prepare_query($::form, $dbh, $q_multiply);
|
||
|
|
||
| SL/DB/Helper/Mappings.pm | ||
|---|---|---|
|
orderitems => 'order_item',
|
||
|
oe => 'order',
|
||
|
oe_version => 'order_version',
|
||
|
onhands => 'Onhand',
|
||
|
parts => 'part',
|
||
|
partsgroup => 'parts_group',
|
||
|
part_classifications => 'PartClassification',
|
||
| SL/DB/Manager/Onhand.pm | ||
|---|---|---|
|
# This file has been auto-generated only because it didn't exist.
|
||
|
# Feel free to modify it at will; it will not be overwritten automatically.
|
||
|
|
||
|
package SL::DB::Manager::Onhand;
|
||
|
|
||
|
use strict;
|
||
|
|
||
|
use parent qw(SL::DB::Helper::Manager);
|
||
|
|
||
|
sub object_class { 'SL::DB::Onhand' }
|
||
|
|
||
|
__PACKAGE__->make_manager_methods;
|
||
|
|
||
|
1;
|
||
| SL/DB/MetaSetup/Onhand.pm | ||
|---|---|---|
|
# This file has been auto-generated. Do not modify it; it will be overwritten
|
||
|
# by rose_auto_create_model.pl automatically.
|
||
|
package SL::DB::Onhand;
|
||
|
|
||
|
use strict;
|
||
|
|
||
|
use parent qw(SL::DB::Object);
|
||
|
|
||
|
__PACKAGE__->meta->table('onhands');
|
||
|
|
||
|
__PACKAGE__->meta->columns(
|
||
|
id => { type => 'integer', not_null => 1, sequence => 'id' },
|
||
|
onhand => { type => 'numeric', precision => 25, scale => 5 },
|
||
|
parts_id => { type => 'integer' },
|
||
|
);
|
||
|
|
||
|
__PACKAGE__->meta->primary_key_columns([ 'id' ]);
|
||
|
|
||
|
__PACKAGE__->meta->unique_keys([ 'parts_id' ]);
|
||
|
|
||
|
__PACKAGE__->meta->foreign_keys(
|
||
|
parts => {
|
||
|
class => 'SL::DB::Part',
|
||
|
key_columns => { parts_id => 'id' },
|
||
|
rel_type => 'one to one',
|
||
|
},
|
||
|
);
|
||
|
|
||
|
1;
|
||
|
;
|
||
| SL/DB/MetaSetup/Part.pm | ||
|---|---|---|
|
not_discountable => { type => 'boolean', default => 'false' },
|
||
|
notes => { type => 'text' },
|
||
|
obsolete => { type => 'boolean', default => 'false' },
|
||
|
onhand => { type => 'numeric', default => '0', precision => 25, scale => 5 },
|
||
|
part_type => { type => 'enum', check_in => [ 'part', 'service', 'assembly', 'assortment' ], db_type => 'part_type_enum', not_null => 1 },
|
||
|
partnumber => { type => 'text', not_null => 1 },
|
||
|
partsgroup_id => { type => 'integer' },
|
||
| SL/DB/Onhand.pm | ||
|---|---|---|
|
# This file has been auto-generated only because it didn't exist.
|
||
|
# Feel free to modify it at will; it will not be overwritten automatically.
|
||
|
|
||
|
package SL::DB::Onhand;
|
||
|
|
||
|
use strict;
|
||
|
|
||
|
use SL::DB::MetaSetup::Onhand;
|
||
|
use SL::DB::Manager::Onhand;
|
||
|
|
||
|
__PACKAGE__->meta->initialize;
|
||
|
|
||
|
1;
|
||
| SL/DB/Part.pm | ||
|---|---|---|
|
column_map => { id => 'part_id' },
|
||
|
manager_args => { sort_by => 'valid_from DESC', limit => 1 },
|
||
|
},
|
||
|
onhands => {
|
||
|
type => 'one to one',
|
||
|
class => 'SL::DB::Onhand',
|
||
|
column_map => {id => 'parts_id' },
|
||
|
},
|
||
|
);
|
||
|
|
||
|
__PACKAGE__->meta->initialize;
|
||
| ... | ... | |
|
return $stock || 0; # never return undef
|
||
|
};
|
||
|
|
||
|
sub onhand {
|
||
|
my ($self) = @_;
|
||
|
return 0 unless $self->id;
|
||
|
return $self->onhands->onhand;
|
||
|
}
|
||
|
|
||
|
# this is designed to ignore chargenumbers, expiration dates and just give a list of how much <-> where
|
||
|
sub get_simple_stock {
|
||
| 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);
|
||
|
|
||
| SL/IR.pm | ||
|---|---|---|
|
my $query =
|
||
|
qq|SELECT
|
||
|
p.id, p.partnumber, p.description, p.lastcost AS sellprice, p.listprice,
|
||
|
p.unit, p.part_type, p.onhand, p.formel,
|
||
|
p.unit, p.part_type, onhands.onhand, p.formel,
|
||
|
p.notes AS partnotes, p.notes AS longdescription, p.not_discountable,
|
||
|
p.price_factor_id,
|
||
|
p.ean,
|
||
| ... | ... | |
|
pg.partsgroup
|
||
|
|
||
|
FROM parts p
|
||
|
LEFT JOIN onhands ON (onhands.parts_id = p.id)
|
||
|
LEFT JOIN chart c1 ON
|
||
|
((SELECT inventory_accno_id
|
||
|
FROM buchungsgruppen
|
||
| SL/IS.pm | ||
|---|---|---|
|
c3.new_chart_id AS expense_new_chart,
|
||
|
date($transdate) - c3.valid_from AS expense_valid,
|
||
|
|
||
|
p.unit, p.part_type, p.onhand,
|
||
|
p.unit, p.part_type, onhands.onhand,
|
||
|
p.notes AS partnotes, p.notes AS longdescription,
|
||
|
p.not_discountable, p.formel, p.payment_id AS part_payment_id,
|
||
|
p.price_factor_id, p.weight,
|
||
| ... | ... | |
|
pg.partsgroup
|
||
|
|
||
|
FROM parts p
|
||
|
LEFT JOIN onhands ON (onhands.parts_id = p.id)
|
||
|
LEFT JOIN chart c1 ON
|
||
|
((SELECT inventory_accno_id
|
||
|
FROM buchungsgruppen
|
||
| sql/Pg-upgrade2/seperate_table_for_onhand.sql | ||
|---|---|---|
|
-- @tag: seperate_table_for_onhand
|
||
|
-- @description: Verschiebe onhand in extra Tabelle
|
||
|
-- @depends: release_3_6_1
|
||
|
CREATE TABLE onhands (
|
||
|
id INT NOT NULL DEFAULT nextval('id'),
|
||
|
parts_id INT UNIQUE references parts(id) ON DELETE CASCADE,
|
||
|
onhand NUMERIC(25,5),
|
||
|
PRIMARY KEY (id)
|
||
|
);
|
||
|
-- lock all tables while updating values
|
||
|
LOCK TABLE onhands IN EXCLUSIVE MODE;
|
||
|
LOCK TABLE inventory IN EXCLUSIVE MODE;
|
||
|
LOCK TABLE parts IN EXCLUSIVE MODE;
|
||
|
|
||
|
CREATE OR REPLACE FUNCTION public.update_onhand()
|
||
|
RETURNS trigger
|
||
|
LANGUAGE plpgsql
|
||
|
AS '
|
||
|
BEGIN
|
||
|
IF tg_op = "INSERT" THEN
|
||
|
UPDATE onhands SET onhand = COALESCE(onhand, 0) + new.qty WHERE parts_id = new.parts_id;
|
||
|
RETURN new;
|
||
|
ELSIF tg_op = "DELETE" THEN
|
||
|
UPDATE onhands SET onhand = COALESCE(onhand, 0) - old.qty WHERE parts_id = old.parts_id;
|
||
|
RETURN old;
|
||
|
ELSE
|
||
|
UPDATE onhands SET onhand = COALESCE(onhand, 0) - old.qty + new.qty WHERE parts_id = old.parts_id;
|
||
|
RETURN new;
|
||
|
END IF;
|
||
|
END;
|
||
|
';
|
||
|
|
||
|
-- All parts get a onhand value;
|
||
|
CREATE OR REPLACE FUNCTION public.create_onhand()
|
||
|
RETURNS trigger
|
||
|
LANGUAGE plpgsql
|
||
|
AS '
|
||
|
BEGIN
|
||
|
INSERT INTO onhands (parts_id, onhand) values (new.parts_id, 0);
|
||
|
END;
|
||
|
';
|
||
|
|
||
|
CREATE TRIGGER trig_create_onhand
|
||
|
AFTER INSERT ON parts
|
||
|
FOR EACH ROW EXECUTE PROCEDURE create_onhand();
|
||
|
|
||
|
|
||
|
INSERT INTO onhands (parts_id, onhand) SELECT id, onhand FROM parts;
|
||
|
|
||
|
ALTER TABLE parts DROP COLUMN onhand;
|
||
| templates/design40_webpages/order/tabs/_second_row.html | ||
|---|---|---|
|
[% END %]
|
||
|
<b>[% 'On Hand' | $T8 %]</b>
|
||
|
<span class="numeric[% IF ITEM.part.onhand < ITEM.part.rop %] plus0[% END %]">
|
||
|
[% ITEM.part.onhand_as_number %] [% ITEM.part.unit %]
|
||
|
[%- LxERP.format_amount(ITEM.part.onhand, 2) -%] [%- ITEM.part.unit -%]
|
||
|
</span>
|
||
|
</td>
|
||
|
</tr>
|
||
| templates/webpages/delivery_order/tabs/_second_row.html | ||
|---|---|---|
|
[%- END %]
|
||
|
<b>[%- 'On Hand' | $T8 %]</b>
|
||
|
<span[%- IF ITEM.part.onhand < ITEM.part.rop -%] class="numeric plus0"[%- END -%]>
|
||
|
[%- ITEM.part.onhand_as_number -%] [%- ITEM.part.unit -%]
|
||
|
[%- LxERP.format_amount(ITEM.part.onhand, 2) -%] [%- ITEM.part.unit -%]
|
||
|
</span>
|
||
|
</td></tr>
|
||
|
|
||
| templates/webpages/order/tabs/_second_row.html | ||
|---|---|---|
|
[%- END %]
|
||
|
<b>[%- 'On Hand' | $T8 %]</b>
|
||
|
<span class="numeric[%- IF ITEM.part.onhand < ITEM.part.rop -%] plus0[%- END -%]">
|
||
|
[%- ITEM.part.onhand_as_number -%] [%- ITEM.part.unit -%]
|
||
|
[%- LxERP.format_amount(ITEM.part.onhand, 2) -%] [%- ITEM.part.unit -%]
|
||
|
</span>
|
||
|
<b>[%- 'Optional' | $T8 %]</b>
|
||
|
[%- L.yes_no_tag("order.orderitems[].optional", ITEM.optional
|
||
| templates/webpages/reclamation/tabs/basic_data/_second_row.html | ||
|---|---|---|
|
[%- END %]
|
||
|
<b>[%- 'On Hand' | $T8 %]</b>
|
||
|
<span[%- IF ITEM.part.onhand < ITEM.part.rop -%] class="numeric plus0"[%- END -%]>
|
||
|
[%- ITEM.part.onhand_as_number -%] [%- ITEM.part.unit -%]
|
||
|
[%- LxERP.format_amount(ITEM.part.onhand, 2) -%] [%- ITEM.part.unit -%]
|
||
|
</span>
|
||
|
</td></tr>
|
||
|
|
||
Auch abrufbar als: Unified diff
Separate Tabelle für onhand (onhands) für Artikel