/ - 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 (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
166	166	orderitems => 'order_item',
167	167	oe => 'order',
168	168	oe_version => 'order_version',
	169	onhands => 'Onhand',
169	170	parts => 'part',
170	171	partsgroup => 'parts_group',
171	172	part_classifications => 'PartClassification',

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

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

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

         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 {

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

       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

              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

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

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

Von Tamino Steinert vor fast 2 Jahren hinzugefügt