Revision 8b2c6778
Von Kivitendo Admin vor etwa 8 Jahren hinzugefügt
| SL/DB/MetaSetup/Part.pm | ||
|---|---|---|
| 9 | 9 |
__PACKAGE__->meta->table('parts');
|
| 10 | 10 |
|
| 11 | 11 |
__PACKAGE__->meta->columns( |
| 12 |
assembly => { type => 'boolean', default => 'false' },
|
|
| 13 | 12 |
bin_id => { type => 'integer' },
|
| 14 | 13 |
bom => { type => 'boolean', default => 'false' },
|
| 15 | 14 |
buchungsgruppen_id => { type => 'integer' },
|
| ... | ... | |
| 34 | 33 |
notes => { type => 'text' },
|
| 35 | 34 |
obsolete => { type => 'boolean', default => 'false' },
|
| 36 | 35 |
onhand => { type => 'numeric', default => '0', precision => 25, scale => 5 },
|
| 36 |
part_type => { type => 'enum', check_in => [ 'part', 'service', 'assembly', 'assortment' ], db_type => 'part_type_enum', not_null => 1 },
|
|
| 37 | 37 |
partnumber => { type => 'text', not_null => 1 },
|
| 38 | 38 |
partsgroup_id => { type => 'integer' },
|
| 39 | 39 |
payment_id => { type => 'integer' },
|
| doc/UPGRADE | ||
|---|---|---|
| 23 | 23 |
(letter.tex) angepasst worden. Statt letter.customer muss der Adressat jetzt |
| 24 | 24 |
aus letter.custoemr_vendor erzeugt werden. |
| 25 | 25 |
|
| 26 |
* In der Tabelle parts wurde die Boolean-Spalte "assembly" entfernt. Zur |
|
| 27 |
Erkennung von Waren/Dienstleistungen/Erzeugnissen gibt es nun in parts eine |
|
| 28 |
neue Spalte part_type vom ENUM-Typ, der auf die Werte 'part', 'service', |
|
| 29 |
'assembly' und 'assortment' beschränkt ist. |
|
| 26 | 30 |
|
| 27 | 31 |
Upgrade auf v3.4.1 |
| 28 | 32 |
================== |
| sql/Pg-upgrade2/part_type_enum.sql | ||
|---|---|---|
| 1 |
-- @tag: part_type_enum |
|
| 2 |
-- @description: enums |
|
| 3 |
-- @depends: release_3_4_1 |
|
| 4 |
|
|
| 5 |
CREATE TYPE part_type_enum AS ENUM ('part', 'service', 'assembly', 'assortment');
|
|
| 6 |
ALTER TABLE parts ADD COLUMN part_type part_type_enum; |
|
| 7 |
|
|
| 8 |
UPDATE parts SET part_type = 'assembly' WHERE assembly IS TRUE; |
|
| 9 |
UPDATE parts SET part_type = 'service' WHERE inventory_accno_id IS NULL and part_type IS NULL; |
|
| 10 |
UPDATE parts SET part_type = 'part' WHERE assembly IS FALSE AND inventory_accno_id IS NOT NULL AND part_type IS NULL; |
|
| 11 |
|
|
| 12 |
-- don't set a default for now to help with finding bugs where no part_type is passed |
|
| 13 |
ALTER TABLE parts ALTER COLUMN part_type SET NOT NULL; |
|
| 14 |
|
|
| 15 |
CREATE OR REPLACE FUNCTION update_purchase_price() RETURNS trigger AS ' |
|
| 16 |
BEGIN |
|
| 17 |
if tg_op = ''DELETE'' THEN |
|
| 18 |
UPDATE parts SET lastcost = COALESCE((select sum ((a.qty * (p.lastcost / COALESCE(pf.factor, |
|
| 19 |
1)))) as summe from assembly a left join parts p on (p.id = a.parts_id) |
|
| 20 |
LEFT JOIN price_factors pf on (p.price_factor_id = pf.id) where a.id = parts.id),0) |
|
| 21 |
WHERE part_type = ''assembly'' and id = old.id; |
|
| 22 |
return old; -- old ist eine referenz auf die geloeschte reihe |
|
| 23 |
ELSE |
|
| 24 |
UPDATE parts SET lastcost = COALESCE((select sum ((a.qty * (p.lastcost / COALESCE(pf.factor, |
|
| 25 |
1)))) as summe from assembly a left join parts p on (p.id = a.parts_id) |
|
| 26 |
LEFT JOIN price_factors pf on (p.price_factor_id = pf.id) |
|
| 27 |
WHERE a.id = parts.id),0) where part_type = ''assembly'' and id = new.id; |
|
| 28 |
return new; -- entsprechend new, wird wahrscheinlich benoetigt, um den korrekten Eintrag |
|
| 29 |
-- zu filtern bzw. dann zu aktualisieren |
|
| 30 |
END IF; |
|
| 31 |
END; |
|
| 32 |
' LANGUAGE plpgsql; |
|
Auch abrufbar als: Unified diff
Ware/Erzeugnis/Dienstleistung per parts.part_type unterscheiden
Neuen ENUM-Typ eingeführt, der auf die Werte "part", "service" und
"assembly" beschränkt ist.
Da man enums nicht innerhalb von Transaktionen hinzufügen kann, was der
Default für den kivitendo Upgrade Mechanismus, wird hier auch schon das
Sortiment vorbereitet.
Siehe auch doc/UPGRADE