/ - Diff - projekt kivitendo - wissen

       # 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
140	140	use SL::DB::ShopOrderItem;
141	141	use SL::DB::ShopPart;
142	142	use SL::DB::Status;
	143	use SL::DB::Stock;
143	144	use SL::DB::Stocktaking;
144	145	use SL::DB::Tax;
145	146	use SL::DB::TaxKey;

SL/DB/Helper/Mappings.pm
220	220	shop_parts => 'shop_part',
221	221	status => 'status',
222	222	stocktakings => 'stocktaking',
	223	stocks => 'Stock',
223	224	tax => 'tax',
224	225	taxkeys => 'tax_key',
225	226	tax_zones => 'tax_zone',

     # 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;
 ;

SL/DB/MetaSetup/Part.pm
30	30	not_discountable => { type => 'boolean', default => 'false' },
31	31	notes => { type => 'text' },
32	32	obsolete => { type => 'boolean', default => 'false' },
33		onhand => { type => 'numeric', default => '0', precision => 25, scale => 5 },
34	33	part_type => { type => 'enum', check_in => [ 'part', 'service', 'assembly', 'assortment' ], db_type => 'part_type_enum', not_null => 1 },
35	34	partnumber => { type => 'text', not_null => 1 },
36	35	partsgroup_id => { type => 'integer' },

     # 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',
       },
     );
 ;
+    ;

         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 {

     # 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;
 ;

       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);

       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

              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

     -- @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;

           [% 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>

         [%- 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>

         [%- 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

         [%- 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>

Projekt

Allgemein

Profil

projekt kivitendo

Revision 4b8e5682

Von Tamino Steinert vor fast 2 Jahren hinzugefügt