X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIC.pm;h=fece79579b246e721289195d3a1c279450bae71f;hb=af59820cb06ba396ba10ecd88f43a92c331a6066;hp=fe666ec14d15b047d047ebadb19294466f685c4b;hpb=6ca711a5e060318111719f02ce91a4c101e9e812;p=kivitendo-erp.git diff --git a/SL/IC.pm b/SL/IC.pm index fe666ec14..fece79579 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 invoice 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 { @@ -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"); } @@ -422,7 +412,8 @@ sub save { ean = ?, not_discountable = ?, microfiche = ?, - partsgroup_id = ? + partsgroup_id = ?, + price_factor_id = ? WHERE id = ?|; @values = ($form->{partnumber}, $form->{description}, @@ -451,6 +442,7 @@ sub save { $form->{not_discountable} ? 't' : 'f', $form->{microfiche}, conv_i($partsgroup_id), + conv_i($form->{price_factor_id}), conv_i($form->{id}) ); do_query($form, $dbh, $query, @values); @@ -535,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]++; @@ -547,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 @@ -575,7 +555,7 @@ sub save { $form->{taxaccount} = ""; while ($ptr = $stw->fetchrow_hashref(NAME_lc)) { $form->{taxaccount} .= "$ptr->{accno} "; - if (!($form->{taxaccount2} =~ /$ptr->{accno}/)) { + if (!($form->{taxaccount2} =~ /\Q$ptr->{accno}\E/)) { $form->{"$ptr->{accno}_rate"} = $ptr->{rate}; $form->{"$ptr->{accno}_description"} = $ptr->{description}; $form->{"$ptr->{accno}_taxnumber"} = $ptr->{taxnumber}; @@ -653,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(); @@ -722,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); } @@ -792,10 +711,10 @@ sub assembly_item { # top100 # # simple filter strings (every one of those also has a column flag prefixed with 'l_' associated): -# partnumber ean description partsgroup microfiche drawing +# partnumber ean description partsgroup microfiche drawing # # column flags: -# l_partnumber l_description l_listprice l_sellprice l_lastcost l_priceupdate l_weight l_unit l_bin l_rop l_image l_drawing l_microfiche l_partsgroup +# l_partnumber l_description l_listprice l_sellprice l_lastcost l_priceupdate l_weight l_unit l_bin l_rop l_image l_drawing l_microfiche l_partsgroup # # exclusives: # itemstatus = active | onhand | short | obsolete | orphaned @@ -804,18 +723,18 @@ sub assembly_item { # joining filters: # make model - makemodel # serialnumber transdatefrom transdateto - invoice/orderitems -# +# # binary flags: -# bought sold onorder ordered rfq quoted - aggreg joins with invoices/orders +# bought sold onorder ordered rfq quoted - aggreg joins with invoices/orders # l_linetotal l_subtotal - aggreg joins to display totals (complicated) - NOT IMPLEMENTED here, implementation at frontend # l_soldtotal - aggreg join to display total of sold quantity # onhand - as above, but masking the simple itemstatus results (doh!) # 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 +# not working: # onhand - as above, but masking the simple itemstatus results (doh!) # masking of onhand in bsooqr mode - ToDO: fixme # @@ -835,9 +754,10 @@ 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); + my @simple_filters = qw(partnumber ean description partsgroup microfiche drawing onhand); my @makemodel_filters = qw(make model); my @invoice_oi_filters = qw(serialnumber soldtotal); my @apoe_filters = qw(transdate); @@ -850,31 +770,32 @@ sub all_parts { # my @inactive_flags = qw(l_subtotal short l_linetotal); my %joins = ( - partsgroup => 'LEFT JOIN partsgroup pg ON p.partsgroup_id = pg.id', - makemodel => 'LEFT JOIN makemodel mm ON mm.parts_id = p.id', - invoice_oi => - q|LEFT JOIN ( - SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, 'invoice' AS ioi FROM invoice UNION - SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, 'orderitems' AS ioi FROM orderitems + partsgroup => 'LEFT JOIN partsgroup pg ON (pg.id = p.partsgroup_id)', + makemodel => 'LEFT JOIN makemodel mm ON (mm.parts_id = p.id)', + pfac => 'LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)', + invoice_oi => + q|LEFT JOIN ( + SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, 'invoice' AS ioi FROM invoice UNION + SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, 'orderitems' AS ioi FROM orderitems ) AS ioi ON ioi.parts_id = p.id|, - apoe => - q|LEFT JOIN ( + 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|, cv => - q|LEFT JOIN ( - SELECT id, name, 'customer' AS cv FROM customer UNION - SELECT id, name, 'vendor' AS cv FROM vendor + q|LEFT JOIN ( + SELECT id, name, 'customer' AS cv FROM customer UNION + SELECT id, name, 'vendor' AS cv FROM vendor ) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|, ); - my @join_order = qw(partsgroup makemodel invoice_oi apoe cv); - my %joins_needed = (0) x scalar keys %joins; + my @join_order = qw(partsgroup makemodel invoice_oi apoe cv pfac); + my %joins_needed; #===== switches and simple filters ========# - my @select_tokens = qw(id); + my @select_tokens = qw(id factor); my @where_tokens = qw(1=1); my @group_tokens = (); @@ -889,10 +810,14 @@ sub all_parts { } } + my %simple_filter_table_prefix = ( + description => 'p.', + ); + foreach (@simple_filters, @makemodel_filters, @invoice_oi_filters) { next unless $form->{$_}; $form->{"l_$_"} = '1'; # show the column - push @where_tokens, "$_ ILIKE ?"; + push @where_tokens, "$simple_filter_table_prefix{$_}$_ ILIKE ?"; push @bind_vars, "%$form->{$_}%"; } @@ -909,30 +834,40 @@ sub all_parts { } for ($form->{itemstatus}) { - push @where_tokens, 'p.id NOT IN - (SELECT DISTINCT parts_id FROM invoice UNION - SELECT DISTINCT parts_id FROM assembly UNION + push @where_tokens, 'p.id NOT IN + (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, '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/; - } + } my @sort_cols = (@simple_filters, qw(id bin priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate)); $form->{sort} = 'id' unless grep { $form->{"l_$_"} } grep { $form->{sort} eq $_ } @sort_cols; - my $order_clause = " ORDER BY $form->{sort} " . ($form->{revers} ? 'DESC' : 'ASC'); + + my $sort_order = ($form->{revers} ? ' DESC' : ' ASC'); + + # special case: sorting by partnumber + # since partnumbers are expected to be prefixed integers, a special sorting is implemented sorting first lexically by prefix and then by suffix. + # and yes, that expression is designed to hold that array of regexes only once, so the map is kinda messy, sorry about that. + # ToDO: implement proper functional sorting + $form->{sort} = join ', ', map { push @select_tokens, $_; ($table_prefix{$_} = "substring(partnumber,'[") . $_ } qw|^[:digit:]]+') [:digit:]]+')::INTEGER| + if $form->{sort} eq 'partnumber'; + + my $order_clause = " ORDER BY $form->{sort} $sort_order"; my $limit_clause = " LIMIT 100" if $form->{top100}; #=== joins and complicated filters ========# - + my $bsooqr = $form->{bought} || $form->{sold} || $form->{ordered} || $form->{onorder} || $form->{quoted} || $form->{rfq}; - + my @bsooqr; push @select_tokens, @qsooqr_flags if $bsooqr; push @select_tokens, @deliverydate_flags if $bsooqr && $form->{l_deliverydate}; @@ -945,6 +880,7 @@ sub all_parts { push @where_tokens, join ' OR ', map { "($_)" } @bsooqr_tokens if $bsooqr; $joins_needed{partsgroup} = 1; + $joins_needed{pfac} = 1; $joins_needed{makemodel} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters; $joins_needed{cv} = 1 if $bsooqr; $joins_needed{apoe} = 1 if $joins_needed{cv} || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters; @@ -967,31 +903,42 @@ sub all_parts { if ($form->{l_soldtotal}) { push @where_tokens, 'ioi.qty >= 0'; push @group_tokens, @select_tokens; - push @select_tokens, 'SUM(ioi.qty) AS soldtotal'; + push @select_tokens, 'SUM(ioi.qty)'; } #============= build query ================# - my %table_prefix = ( - deliverydate => 'apoe.', serialnumber => 'ioi.', - transdate => 'apoe.', trans_id => 'ioi.', - module => 'apoe.', name => 'cv.', - ordnumber => 'apoe.', make => 'mm.', - quonumber => 'apoe.', model => 'mm.', + %table_prefix = ( + %table_prefix, + deliverydate => 'apoe.', serialnumber => 'ioi.', + transdate => 'apoe.', trans_id => 'ioi.', + module => 'apoe.', name => 'cv.', + ordnumber => 'apoe.', make => 'mm.', + quonumber => 'apoe.', model => 'mm.', invnumber => 'apoe.', partsgroup => 'pg.', - 'SUM(ioi.qty) AS soldtotal' => ' ', + factor => 'pfac.', + 'SUM(ioi.qty)' => ' ', ); - + + my %renamed_columns = ( + 'factor' => 'price_factor', + 'SUM(ioi.qty)' => 'soldtotal', + ); + map { $table_prefix{$_} = 'ioi.' } qw(description serialnumber qty unit) if $joins_needed{invoice_oi}; + map { $renamed_columns{$_} = ' AS ' . $renamed_columns{$_} } keys %renamed_columns; - my $select_clause = join ', ', map { ($table_prefix{$_} || "p.") . $_ } @select_tokens; + my $select_clause = join ', ', map { ($table_prefix{$_} || "p.") . $_ . $renamed_columns{$_} } @select_tokens; my $join_clause = join ' ', @joins{ grep $joins_needed{$_}, @join_order }; my $where_clause = join ' AND ', map { "($_)" } @where_tokens; - my $group_clause = ' GROUP BY ' . join ', ', map { ($table_prefix{$_} || "p.") . $_ } @group_tokens; + my $group_clause = ' GROUP BY ' . join ', ', map { ($table_prefix{$_} || "p.") . $_ } @group_tokens if scalar @group_tokens; my $query = qq|SELECT DISTINCT $select_clause FROM parts p $join_clause WHERE $where_clause $group_clause $order_clause $limit_clause|; + $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); ## @@ -1092,10 +1039,10 @@ sub all_parts { ## if ($form->{l_soldtotal}) { ## $form->{soldtotal} = 'soldtotal'; ## $query = -## qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, -## p.bin, p.sellprice, p.listprice, p.lastcost, -## p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal -## FROM parts p +## qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit, +## p.bin, p.sellprice, p.listprice, p.lastcost, +## p.priceupdate, pg.partsgroup,sum(i.qty) AS soldtotal +## FROM parts p ## LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id), invoice i ## WHERE $where ## $group @@ -1364,7 +1311,6 @@ sub all_parts { $form->{parts} = \@assemblies; } - $dbh->disconnect; $main::lxdebug->leave_sub(); } @@ -1379,8 +1325,6 @@ sub update_prices { my $group; my $limit; - my @where_values; - if ($item ne 'make') { foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) { my $column = $item; @@ -1546,7 +1490,7 @@ sub create_links { my $sth = prepare_execute_query($form, $dbh, $query, @values); while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { foreach my $key (split(/:/, $ref->{link})) { - if ($key =~ /$module/) { + if ($key =~ /\Q$module\E/) { if ( ($ref->{id} eq $ref->{inventory_accno_id}) || ($ref->{id} eq $ref->{income_accno_id}) || ($ref->{id} eq $ref->{expense_accno_id})) { @@ -1665,11 +1609,14 @@ sub retrieve_languages { $query = qq|SELECT l.id, l.description, tr.translation, tr.longdescription FROM language l - LEFT OUTER JOIN translation tr ON (tr.language_id = l.id) AND (tr.parts_id = ?)|; + LEFT OUTER JOIN translation tr ON (tr.language_id = l.id) AND (tr.parts_id = ?) + ORDER BY lower(l.description)|; @values = (conv_i($form->{id})); } else { - $query = qq|SELECT id, description FROM language|; + $query = qq|SELECT id, description + FROM language + ORDER BY lower(description)|; } my $languages = selectall_hashref_query($form, $dbh, $query, @values); @@ -1811,4 +1758,43 @@ 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; +} + + 1;