Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision 4b8e5682

Von Tamino Steinert vor etwa 2 Jahren hinzugefügt

  • ID 4b8e56821eac8b83856dca20e2ca8371081c13f5
  • Vorgänger 72b80f91

Separate Tabelle für onhand (stocks) für Artikel

Jetzt führt Ein- und Auslagern nicht mehr zu einer Änderung des
    Artikels in der Datenbank.

Unterschiede anzeigen:

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 (stocks.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 stocks.onhand > 0|;
} elsif ($filter->{itemstatus} eq 'short') {
$where .= qq| AND p.onhand < p.rop|;
$where .= qq| AND stocks.onhand < p.rop|;
}
......
(SELECT p.id
FROM parts p
LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
LEFT JOIN stocks ON (stocks.part_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 stocks ON (stocks.part_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 stocks ON (stocks.part_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 stocks ON (stocks.part_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 stocks ON (stocks.part_id = p.id)
WHERE $where) AND (pricegroup_id = ?)|;
my $sth_multiply = prepare_query($::form, $dbh, $q_multiply);
SL/DB/Helper/ALL.pm
use SL::DB::ShopOrderItem;
use SL::DB::ShopPart;
use SL::DB::Status;
use SL::DB::Stock;
use SL::DB::Stocktaking;
use SL::DB::Tax;
use SL::DB::TaxKey;
SL/DB/Helper/Mappings.pm
shop_parts => 'shop_part',
status => 'status',
stocktakings => 'stocktaking',
stocks => 'Stock',
tax => 'tax',
taxkeys => 'tax_key',
tax_zones => 'tax_zone',
SL/DB/Manager/Stock.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::Stock;
use strict;
use parent qw(SL::DB::Helper::Manager);
sub object_class { 'SL::DB::Stock' }
__PACKAGE__->make_manager_methods;
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/MetaSetup/Stock.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::Stock;
use strict;
use parent qw(SL::DB::Object);
__PACKAGE__->meta->table('stocks');
__PACKAGE__->meta->columns(
id => { type => 'integer', not_null => 1, sequence => 'id' },
onhand => { type => 'numeric', precision => 25, scale => 5 },
part_id => { type => 'integer' },
);
__PACKAGE__->meta->primary_key_columns([ 'id' ]);
__PACKAGE__->meta->unique_keys([ 'part_id' ]);
__PACKAGE__->meta->foreign_keys(
part => {
class => 'SL::DB::Part',
key_columns => { part_id => 'id' },
rel_type => 'one to one',
},
);
1;
;
SL/DB/Part.pm
column_map => { id => 'part_id' },
manager_args => { sort_by => 'valid_from DESC', limit => 1 },
},
stock => {
type => 'one to one',
class => 'SL::DB::Stock',
column_map => {id => 'part_id' },
},
);
__PACKAGE__->meta->initialize;
......
return $stock || 0; # never return undef
};
sub onhand {
my ($self) = @_;
return 0 unless $self->id;
return $self->stock->onhand;
}
# this is designed to ignore chargenumbers, expiration dates and just give a list of how much <-> where
sub get_simple_stock {
SL/DB/Stock.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::Stock;
use strict;
use SL::DB::MetaSetup::Stock;
use SL::DB::Manager::Stock;
__PACKAGE__->meta->initialize;
1;
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, stocks.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 stocks ON (stocks.part_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 => 'stocks.',
);
# 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, 'stocks.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, 'stocks.onhand > 0', if /onhand/;
push @where_tokens, 'stocks.onhand < p.rop', if /short/;
}
my $q_assembly_lastcost =
......
my $query = <<" SQL";
SELECT DISTINCT $select_clause
FROM parts p
LEFT JOIN stocks ON (stocks.part_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.* stocks.onhand FROM parts p LEFT JOIN stocks ON (stocks.part_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, stocks.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 stocks ON (stocks.part_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, stocks.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 stocks ON (stocks.part_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 stocks (
id INT NOT NULL DEFAULT nextval('id'),
part_id INT UNIQUE references parts(id) ON DELETE CASCADE,
onhand NUMERIC(25,5),
PRIMARY KEY (id)
);
-- lock all tables while updating values
LOCK TABLE stocks IN EXCLUSIVE MODE;
LOCK TABLE inventory IN EXCLUSIVE MODE;
LOCK TABLE parts IN EXCLUSIVE MODE;
-- delete old trigger
DROP TRIGGER IF EXISTS trig_update_onhand ON inventory;
DROP FUNCTION IF EXISTS update_onhand();
CREATE OR REPLACE FUNCTION update_stock()
RETURNS trigger
LANGUAGE plpgsql
AS '
BEGIN
IF tg_op = ''INSERT'' THEN
UPDATE stocks SET onhand = COALESCE(onhand, 0) + new.qty WHERE part_id = new.parts_id;
RETURN new;
ELSIF tg_op = ''DELETE'' THEN
UPDATE stocks SET onhand = COALESCE(onhand, 0) - old.qty WHERE part_id = old.parts_id;
RETURN old;
ELSE
UPDATE stocks SET onhand = COALESCE(onhand, 0) - old.qty + new.qty WHERE part_id = old.parts_id;
RETURN new;
END IF;
END;
';
CREATE OR REPLACE TRIGGER trig_update_stock
AFTER INSERT OR UPDATE OR DELETE ON inventory
FOR EACH ROW EXECUTE PROCEDURE update_stock();
-- All parts get a onhand value;
CREATE OR REPLACE FUNCTION create_stock()
RETURNS trigger
LANGUAGE plpgsql
AS '
BEGIN
INSERT INTO stocks (part_id, onhand) values (new.id, 0);
RETURN new;
END;
';
CREATE OR REPLACE TRIGGER trig_create_stock
AFTER INSERT ON parts
FOR EACH ROW EXECUTE PROCEDURE create_stock();
INSERT INTO stocks (part_id, onhand) SELECT id, onhand FROM parts;
-- neu berechnen? UPDATE parts SET onhand = COALESCE((SELECT SUM(qty) FROM inventory WHERE inventory.parts_id = parts.id), 0);
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) -%]&nbsp;[%- ITEM.part.unit -%]
</span>
</td>
</tr>
templates/webpages/delivery_order/tabs/_second_row.html
[%- END %]
<b>[%- 'On Hand' | $T8 %]</b>&nbsp;
<span[%- IF ITEM.part.onhand < ITEM.part.rop -%] class="numeric plus0"[%- END -%]>
[%- ITEM.part.onhand_as_number -%]&nbsp;[%- ITEM.part.unit -%]
[%- LxERP.format_amount(ITEM.part.onhand, 2) -%]&nbsp;[%- ITEM.part.unit -%]
</span>&nbsp;
</td></tr>
templates/webpages/order/tabs/_second_row.html
[%- END %]
<b>[%- 'On Hand' | $T8 %]</b>&nbsp;
<span class="numeric[%- IF ITEM.part.onhand < ITEM.part.rop -%] plus0[%- END -%]">
[%- ITEM.part.onhand_as_number -%]&nbsp;[%- ITEM.part.unit -%]
[%- LxERP.format_amount(ITEM.part.onhand, 2) -%]&nbsp;[%- ITEM.part.unit -%]
</span>&nbsp;
<b>[%- 'Optional' | $T8 %]</b>&nbsp;
[%- L.yes_no_tag("order.orderitems[].optional", ITEM.optional
templates/webpages/reclamation/tabs/basic_data/_second_row.html
[%- END %]
<b>[%- 'On Hand' | $T8 %]</b>&nbsp;
<span[%- IF ITEM.part.onhand < ITEM.part.rop -%] class="numeric plus0"[%- END -%]>
[%- ITEM.part.onhand_as_number -%]&nbsp;[%- ITEM.part.unit -%]
[%- LxERP.format_amount(ITEM.part.onhand, 2) -%]&nbsp;[%- ITEM.part.unit -%]
</span>&nbsp;
</td></tr>

Auch abrufbar als: Unified diff