Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision 72528ede

Von Tamino Steinert vor mehr als 2 Jahren hinzugefügt

  • ID 72528edece18f8ac73ff702e83050c674db69b38
  • Vorgänger 72b80f91

Separate Tabelle für onhand (onhands) 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 (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) -%]&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