X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIC.pm;h=6fa45dc10a7a37491ca2b8f00102e1e6e4d8261d;hb=16c66f611b987008d72c4475ce7cf691d517af0f;hp=5dfcad89f5c0c96094b5a4590dbf390db0c49e32;hpb=174fe30ef26a44c8af1fa355496a79e3ed287dde;p=kivitendo-erp.git diff --git a/SL/IC.pm b/SL/IC.pm index 5dfcad89f..6fa45dc10 100644 --- a/SL/IC.pm +++ b/SL/IC.pm @@ -33,7 +33,10 @@ #====================================================================== package IC; + use Data::Dumper; +use YAML; + use SL::DBUtils; sub get_part { @@ -63,6 +66,8 @@ sub get_part { # copy to $form variables map { $form->{$_} = $ref->{$_} } (keys %{$ref}); + $form->{onhand} *= 1; + my %oid = ('Pg' => 'a.oid', 'Oracle' => 'a.rowid'); @@ -183,10 +188,12 @@ sub get_part { # get translations $form->{language_values} = ""; - $query = qq|SELECT language_id, translation FROM translation WHERE parts_id = ?|; + $query = qq|SELECT language_id, translation, longdescription + FROM translation + WHERE parts_id = ?|; my $trq = prepare_execute_query($form, $dbh, $query, conv_i($form->{id})); - while ($tr = $trq->fetchrow_hashref(NAME_lc)) { - $form->{language_values} .= "---+++---".$tr->{language_id}."--++--".$tr->{translation}; + while (my $tr = $trq->fetchrow_hashref(NAME_lc)) { + $form->{language_values} .= "---+++---" . join('--++--', @{$tr}{qw(language_id translation longdescription)}); } $trq->finish; @@ -203,37 +210,25 @@ sub get_part { $sth->finish; # is it an orphan - $query = - qq|SELECT i.parts_id - FROM invoice i - WHERE (i.parts_id = ?) - - UNION - - SELECT o.parts_id - FROM orderitems o - WHERE (o.parts_id = ?) - - UNION + my @referencing_tables = qw(invoice orderitems inventory rmaitems); + my %column_map = ( ); + my $parts_id = conv_i($form->{id}); - SELECT a.parts_id - FROM assembly a - WHERE (a.parts_id = ?)|; - @values = (conv_i($form->{id}), conv_i($form->{id}), conv_i($form->{id})); - ($form->{orphaned}) = selectrow_query($form, $dbh, $query, @values); - $form->{orphaned} = !$form->{orphaned}; + $form->{orphaned} = 1; - $form->{"unit_changeable"} = 1; - foreach my $table (qw(invoice assembly orderitems inventory license)) { - $query = qq|SELECT COUNT(*) FROM $table WHERE parts_id = ?|; - my ($count) = selectrow_query($form, $dbh, $query, conv_i($form->{"id"})); + foreach my $table (@referencing_tables) { + my $column = $column_map{$table} || 'parts_id'; + $query = qq|SELECT $column FROM $table WHERE $column = ? LIMIT 1|; + my ($found) = selectrow_query($form, $dbh, $query, $parts_id); - if ($count) { - $form->{"unit_changeable"} = 0; + if ($found) { + $form->{orphaned} = 0; last; } } + $form->{"unit_changeable"} = $form->{orphaned}; + $dbh->disconnect; $main::lxdebug->leave_sub(); @@ -265,6 +260,8 @@ sub get_pricegroups { $dbh->disconnect; $main::lxdebug->leave_sub(); + + return $pricegroups; } sub retrieve_buchungsgruppen { @@ -302,7 +299,7 @@ sub save { # undo amount formatting map { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) } - qw(rop weight listprice sellprice gv lastcost stock); + qw(rop weight listprice sellprice gv lastcost); my $makemodel = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0; @@ -330,14 +327,8 @@ sub save { } if ($form->{item} eq 'assembly') { - if ($form->{onhand} != 0) { - &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand} * -1); - } - # delete assembly records do_query($form, $dbh, qq|DELETE FROM assembly WHERE id = ?|, conv_i($form->{id})); - - $form->{onhand} += $form->{stock}; } # delete tax records @@ -357,7 +348,6 @@ sub save { do_query($form, $dbh, qq|INSERT INTO parts (id, partnumber) VALUES (?, '')|, $form->{id}); $form->{orphaned} = 1; - $form->{onhand} = $form->{stock} if $form->{item} eq 'assembly'; if ($form->{partnumber} eq "" && $form->{"item"} eq "service") { $form->{partnumber} = $form->update_defaults($myconfig, "servicenumber"); } @@ -537,11 +527,6 @@ sub save { } } - # adjust onhand for the parts - if ($form->{onhand} != 0) { - &adjust_inventory($dbh, $form, $form->{id}, $form->{onhand}); - } - @a = localtime; $a[5] += 1900; $a[4]++; @@ -549,13 +534,6 @@ sub save { $form->get_employee($dbh); - # add inventory record - $query = - qq|INSERT INTO inventory (warehouse_id, parts_id, qty, shippingdate, employee_id) - VALUES (0, ?, ?, '$shippingdate', ?)|; - @values = (conv_i($form->{id}), $form->{stock}, conv_i($form->{employee_id})); - do_query($form, $dbh, $query, @values); - } #set expense_accno=inventory_accno if they are different => bilanz @@ -655,67 +633,6 @@ sub retrieve_assemblies { $main::lxdebug->leave_sub(); } -sub restock_assemblies { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - # connect to database - my $dbh = $form->dbconnect_noauto($myconfig); - - for my $i (1 .. $form->{rowcount}) { - - $form->{"qty_$i"} = $form->parse_amount($myconfig, $form->{"qty_$i"}); - - if ($form->{"qty_$i"} != 0) { - &adjust_inventory($dbh, $form, $form->{"id_$i"}, $form->{"qty_$i"}); - } - - } - - my $rc = $dbh->commit; - $dbh->disconnect; - - $main::lxdebug->leave_sub(); - - return $rc; -} - -sub adjust_inventory { - $main::lxdebug->enter_sub(); - - my ($dbh, $form, $id, $qty) = @_; - - my $query = - qq|SELECT p.id, p.inventory_accno_id, p.assembly, a.qty - FROM parts p, assembly a - WHERE (a.parts_id = p.id) AND (a.id = ?)|; - my $sth = prepare_execute_query($form, $dbh, $query, conv_i($id)); - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - - my $allocate = $qty * $ref->{qty}; - - # is it a service item, then loop - $ref->{inventory_accno_id} *= 1; - next if (($ref->{inventory_accno_id} == 0) && !$ref->{assembly}); - - # adjust parts onhand - $form->update_balance($dbh, "parts", "onhand", - qq|id = $ref->{id}|, - $allocate * -1); - } - - $sth->finish; - - # update assembly - my $rc = $form->update_balance($dbh, "parts", "onhand", qq|id = ?|, $qty, $id); - - $main::lxdebug->leave_sub(); - - return $rc; -} - sub delete { $main::lxdebug->enter_sub(); @@ -724,9 +641,9 @@ sub delete { # connect to database, turn off AutoCommit my $dbh = $form->dbconnect_noauto($myconfig); - my %columns = ( "assembly" => "id", "alternate" => "id", "parts" => "id" ); + my %columns = ( "assembly" => "id", "parts" => "id" ); - for my $table (qw(prices partstax makemodel inventory assembly parts)) { + for my $table (qw(prices partstax makemodel inventory assembly license translation parts)) { my $column = defined($columns{$table}) ? $columns{$table} : "parts_id"; do_query($form, $dbh, qq|DELETE FROM $table WHERE $column = ?|, @values); } @@ -815,9 +732,9 @@ sub assembly_item { # short - NOT IMPLEMENTED as form filter, only as itemstatus option # l_serialnumber - belonges to serialnumber filter # l_deliverydate - displays deliverydate is sold etc. flags are active +# l_soldtotal - aggreg join to display total of sold quantity, works as long as there's no bullshit in soldtotal # # not working: -# l_soldtotal - aggreg join to display total of sold quantity # onhand - as above, but masking the simple itemstatus results (doh!) # masking of onhand in bsooqr mode - ToDO: fixme # @@ -837,7 +754,8 @@ sub all_parts { my ($self, $myconfig, $form) = @_; my $dbh = $form->get_standard_dbh($myconfig); - $form->{parts} = +{ }; + $form->{parts} = +{ }; + $form->{soldtotal} = undef if $form->{l_soldtotal}; # security fix. top100 insists on putting strings in there... my @simple_filters = qw(partnumber ean description partsgroup microfiche drawing onhand); my @makemodel_filters = qw(make model); @@ -862,10 +780,10 @@ sub all_parts { ) AS ioi ON ioi.parts_id = p.id|, apoe => q|LEFT JOIN ( - SELECT id, transdate, 'ir' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, NULL AS customer_id, vendor_id, NULL AS deliverydate FROM ap UNION - SELECT id, transdate, 'is' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, customer_id, NULL AS vendor_id, deliverydate FROM ar UNION - SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, quotation, customer_id, vendor_id, NULL AS deliverydate FROM oe - ) AS apoe ON ioi.trans_id = apoe.id|, + SELECT id, transdate, 'ir' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, NULL AS customer_id, vendor_id, NULL AS deliverydate, 'invoice' AS ioi FROM ap UNION + SELECT id, transdate, 'is' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, customer_id, NULL AS vendor_id, deliverydate, 'invoice' AS ioi FROM ar UNION + SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, quotation, customer_id, vendor_id, NULL AS deliverydate, 'orderitems' AS ioi FROM oe + ) AS apoe ON ((ioi.trans_id = apoe.id) AND (ioi.ioi = apoe.ioi))|, cv => q|LEFT JOIN ( SELECT id, name, 'customer' AS cv FROM customer UNION @@ -1019,6 +937,8 @@ sub all_parts { $form->{parts} = selectall_hashref_query($form, $dbh, $query, @bind_vars); + map { $_->{onhand} *= 1 } @{ $form->{parts} }; + ## my $where = qq|1 = 1|; ## my (@values, $var, $flds, $group, $limit); ## @@ -1391,7 +1311,6 @@ sub all_parts { $form->{parts} = \@assemblies; } - $dbh->disconnect; $main::lxdebug->leave_sub(); } @@ -1839,4 +1758,111 @@ sub retrieve_accounts { $main::lxdebug->leave_sub(2); } +sub get_basic_part_info { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(id)); + + my @ids = 'ARRAY' eq ref $params{id} ? @{ $params{id} } : ($params{id}); + + if (!scalar @ids) { + $main::lxdebug->leave_sub(); + return (); + } + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $form->get_standard_dbh($myconfig); + + my $query = qq|SELECT id, partnumber, description, unit FROM parts WHERE id IN (| . join(', ', ('?') x scalar(@ids)) . qq|)|; + + my $info = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids); + + if ('' eq ref $params{id}) { + $info = $info->[0] || { }; + + $main::lxdebug->leave_sub(); + return $info; + } + + my %info_map = map { $_->{id} => $_ } @{ $info }; + + $main::lxdebug->leave_sub(); + + return %info_map; +} + +sub prepare_parts_for_printing { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my $prefix = $params{prefix} || 'id_'; + my $rowcount = defined $params{rowcount} ? $params{rowcount} : $form->{rowcount}; + + my @part_ids = keys %{ { map { $_ => 1 } grep { $_ } map { $form->{"${prefix}${_}"} } (1 .. $rowcount) } }; + + if (!@part_ids) { + $main::lxdebug->leave_sub(); + return; + } + + my $placeholders = join ', ', ('?') x scalar(@part_ids); + my $query = qq|SELECT parts_id, make, model + FROM makemodel + WHERE parts_id IN ($placeholders)|; + my %makemodel = (); + + my $sth = prepare_execute_query($form, $dbh, $query, @part_ids); + + while (my $ref = $sth->fetchrow_hashref()) { + $makemodel{$ref->{parts_id}} ||= []; + push @{ $makemodel{$ref->{parts_id}} }, $ref; + } + + $sth->finish(); + + my @columns = qw(ean); + + $query = qq|SELECT id, | . join(', ', @columns) . qq| + FROM parts + WHERE id IN ($placeholders)|; + + my %data = selectall_as_map($form, $dbh, $query, 'id', \@columns, @part_ids); + + map { $form->{$_} = [] } (qw(make model), @columns); + + foreach my $i (1 .. $rowcount) { + my $id = $form->{"${prefix}${i}"}; + + next if (!$id); + + foreach (@columns) { + push @{ $form->{$_} }, $data{$id}->{$_}; + } + + push @{ $form->{make} }, []; + push @{ $form->{model} }, []; + + next if (!$makemodel{$id}); + + foreach my $ref (@{ $makemodel{$id} }) { + map { push @{ $form->{$_}->[-1] }, $ref->{$_} } qw(make model); + } + } + + $main::lxdebug->leave_sub(); +} + + 1;