X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIC.pm;h=a05c85e493b1d041897cd1ae4c459c797ea19e91;hb=8c7e44938a661e035f62840e1e177353240ace5d;hp=78dfc5f93d27376a9267fcb9c12ce2628a93400d;hpb=a596e63e5ba9d89c2bf2c554e90922c7b5198224;p=kivitendo-erp.git diff --git a/SL/IC.pm b/SL/IC.pm index 78dfc5f93..a05c85e49 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'); @@ -203,37 +208,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 = ?) + my @referencing_tables = qw(invoice orderitems invoice inventory rmaitems); + my %column_map = ( ); + my $parts_id = conv_i($form->{id}); - UNION + $form->{orphaned} = 1; - 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}; + 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); - $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"})); - - 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 +258,8 @@ sub get_pricegroups { $dbh->disconnect; $main::lxdebug->leave_sub(); + + return $pricegroups; } sub retrieve_buchungsgruppen { @@ -422,7 +417,8 @@ sub save { ean = ?, not_discountable = ?, microfiche = ?, - partsgroup_id = ? + partsgroup_id = ?, + price_factor_id = ? WHERE id = ?|; @values = ($form->{partnumber}, $form->{description}, @@ -451,6 +447,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); @@ -575,7 +572,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}; @@ -722,9 +719,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); } @@ -837,7 +834,7 @@ sub all_parts { $form->{parts} = +{ }; - 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,8 +847,9 @@ 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', + 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 @@ -869,12 +867,12 @@ sub all_parts { 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 +887,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->{$_}%"; } @@ -923,7 +925,17 @@ sub all_parts { 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}; @@ -945,6 +957,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,29 +980,38 @@ 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 = ( + %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 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); ## my $where = qq|1 = 1|; @@ -1379,8 +1401,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 +1566,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 +1685,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 +1834,55 @@ 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 ($params{vendor_id}) { + $query = qq|SELECT * FROM parts_vendor WHERE (parts_id = ?) AND (vendor_id = ?)|; + my $sth = prepare_query($form, $dbh, $query); + + foreach my $part (@{ $info }) { + do_statement($form, $sth, $query, $part->{id}, conv_i($params{vendor_id})); + $part->{vendor_info} = $sth->fetchrow_hashref(); + } + + $sth->finish(); + } + + 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;