X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIC.pm;h=2a9460745cac60b075bf1840fc3528b65758b792;hb=1268bf670c06f5a66ad78a75e41ad6c15061d9bc;hp=cf137928b3488a854feeb43572340ee121dab555;hpb=bce08af4ca2b6a160505dedd3a28edb455e1de7a;p=kivitendo-erp.git diff --git a/SL/IC.pm b/SL/IC.pm index cf137928b..2a9460745 100644 --- a/SL/IC.pm +++ b/SL/IC.pm @@ -37,7 +37,6 @@ package IC; use Data::Dumper; use List::MoreUtils qw(all any uniq); -use YAML; use SL::CVar; use SL::DBUtils; @@ -45,6 +44,7 @@ use SL::HTML::Restrict; use SL::TransNumber; use SL::Util qw(trim); use SL::DB; +use SL::Presenter::Part qw(type_abbreviation classification_abbreviation separate_abbreviation); use Carp; use strict; @@ -128,6 +128,7 @@ sub assembly_item { # # column flags: # l_partnumber l_description l_listprice l_sellprice l_lastcost l_priceupdate l_weight l_unit l_rop l_image l_drawing l_microfiche l_partsgroup +# l_warehouse l_bin # # exclusives: # itemstatus = active | onhand | short | obsolete | orphaned @@ -136,6 +137,8 @@ sub assembly_item { # joining filters: # make model - makemodel # serialnumber transdatefrom transdateto - invoice/orderitems +# warehouse - warehouse +# bin - bin # # binary flags: # bought sold onorder ordered rfq quoted - aggreg joins with invoices/orders @@ -151,6 +154,8 @@ sub assembly_item { # onhand - as above, but masking the simple itemstatus results (doh!) # warehouse onhand # search by overrides of description +# soldtotal drops option default warehouse and bin +# soldtotal can not work if there are no documents checked # # disabled sanity checks and changes: # - searchitems = assembly will no longer disable bought @@ -167,6 +172,9 @@ sub all_parts { my ($self, $myconfig, $form) = @_; my $dbh = $form->get_standard_dbh($myconfig); + # sanity backend check + croak "Cannot combine soldtotal with default bin or default warehouse" if ($form->{l_soldtotal} && ($form->{l_bin} || $form->{l_warehouse})); + $form->{parts} = +{ }; $form->{soldtotal} = undef if $form->{l_soldtotal}; # security fix. top100 insists on putting strings in there... @@ -178,6 +186,7 @@ sub all_parts { my @like_filters = (@simple_filters, @invoice_oi_filters); my @all_columns = (@simple_filters, @makemodel_filters, @apoe_filters, @project_filters, qw(serialnumber)); my @simple_l_switches = (@all_columns, qw(notes listprice sellprice lastcost priceupdate weight unit rop image shop insertdate)); + my %no_simple_l_switches = (warehouse => 'wh.description as warehouse', bin => 'bin.description as bin'); my @oe_flags = qw(bought sold onorder ordered rfq quoted); my @qsooqr_flags = qw(invnumber ordnumber quonumber trans_id name module qty); my @deliverydate_flags = qw(deliverydate); @@ -212,8 +221,10 @@ sub all_parts { ) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|, mv => 'LEFT JOIN vendor AS mv ON mv.id = mm.make', project => 'LEFT JOIN project AS pj ON pj.id = COALESCE(ioi.project_id, apoe.globalproject_id)', + warehouse => 'LEFT JOIN warehouse AS wh ON wh.id = p.warehouse_id', + bin => 'LEFT JOIN bin ON bin.id = p.bin_id', ); - my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac project); + my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac project warehouse bin); my %table_prefix = ( deliverydate => 'apoe.', serialnumber => 'ioi.', @@ -235,7 +246,7 @@ sub all_parts { ); # if the join condition in these blocks are met, the column - # of the scecified table will gently override (coalesce actually) the original value + # of the specified table will gently override (coalesce actually) the original value # use it to conditionally coalesce values from subtables my @column_override = ( # column name, prefix, joins_needed, nick name (in case column is named like another) @@ -261,10 +272,6 @@ sub all_parts { insertdate => 'itime::DATE', ); - if ($form->{l_assembly} && $form->{l_lastcost}) { - @simple_l_switches = grep { $_ ne 'lastcost' } @simple_l_switches; - } - my $make_token_builder = sub { my $joins_needed = shift; sub { @@ -302,7 +309,11 @@ sub all_parts { # special case smart search if ($form->{all}) { - $form->{"l_$_"} = 1 for qw(partnumber description unit sellprice lastcost cvar_packaging linetotal); + $form->{"l_$_"} = 1 for qw(partnumber description unit sellprice lastcost linetotal); + $form->{l_service} = 1 if $form->{searchitems} eq 'service' || $form->{searchitems} eq ''; + $form->{l_assembly} = 1 if $form->{searchitems} eq 'assembly' || $form->{searchitems} eq ''; + $form->{l_part} = 1 if $form->{searchitems} eq 'part' || $form->{searchitems} eq ''; + $form->{l_assortment} = 1 if $form->{searchitems} eq 'assortment' || $form->{searchitems} eq ''; push @where_tokens, "p.partnumber ILIKE ? OR p.description ILIKE ?"; push @bind_vars, (like($form->{all})) x 2; } @@ -387,7 +398,7 @@ sub all_parts { FROM assembly a_lc LEFT JOIN parts p_lc ON (a_lc.parts_id = p_lc.id) LEFT JOIN price_factors pfac_lc ON (p_lc.price_factor_id = pfac_lc.id) - WHERE (a_lc.id = p.id)) AS lastcost|; + WHERE (a_lc.id = p.id)) AS assembly_lastcost|; $table_prefix{$q_assembly_lastcost} = ' '; # special case makemodel search @@ -442,6 +453,8 @@ sub all_parts { $joins_needed{cv} = 1 if $bsooqr; $joins_needed{apoe} = 1 if $joins_needed{project} || $joins_needed{cv} || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters; $joins_needed{invoice_oi} = 1 if $joins_needed{project} || $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters; + $joins_needed{bin} = 1 if $form->{l_bin}; + $joins_needed{warehouse} = 1 if $form->{l_warehouse}; # special case for description search. # up in the simple filter section the description filter got interpreted as something like: WHERE description ILIKE '%$form->{description}%' @@ -468,7 +481,7 @@ sub all_parts { my $token_builder = $make_token_builder->(\%joins_needed); - my @sort_cols = (@simple_filters, qw(id priceupdate onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate insertdate shop)); + my @sort_cols = (@simple_filters, qw(id onhand invnumber ordnumber quonumber name serialnumber soldtotal deliverydate insertdate shop)); $form->{sort} = 'id' unless grep { $form->{"l_$_"} } grep { $form->{sort} eq $_ } @sort_cols; # sort by id if unknown or invisible column my $sort_order = ($form->{revers} ? ' DESC' : ' ASC'); my $order_clause = " ORDER BY " . $token_builder->($form->{sort}) . ($form->{revers} ? ' DESC' : ' ASC'); @@ -478,6 +491,16 @@ sub all_parts { my $where_clause = join ' AND ', map { "($_)" } @where_tokens; my $group_clause = @group_tokens ? ' GROUP BY ' . join ', ', map { $token_builder->($_) } @group_tokens : ''; + # key of %no_simple_l_switch is the logical l_switch. + # the assigned value is the 'not so simple + # select token' + my $no_simple_select_clause; + foreach my $no_simple_l_switch (keys %no_simple_l_switches) { + next unless $form->{"l_${no_simple_l_switch}"}; + $no_simple_select_clause .= ', '. $no_simple_l_switches{$no_simple_l_switch}; + } + $select_clause .= $no_simple_select_clause; + my %oe_flag_to_cvar = ( bought => 'invoice', sold => 'invoice', @@ -499,6 +522,24 @@ sub all_parts { push @bind_vars, @cvar_values; } + # simple search for assemblies by items used in assemblies + if ($form->{bom} eq '2' && $form->{l_assembly}) { + # assembly_qty is the column name + $form->{l_assembly_qty} = 1; + # nuke where clause and bind vars + $where_clause = ' 1=1 AND p.id in (SELECT id from assembly where parts_id IN ' . + ' (select id from parts where 1=1'; + @bind_vars = (); + # use only like filter for items used in assemblies + foreach (@like_filters) { + next unless $form->{$_}; + $form->{"l_$_"} = '1'; # show the column + $where_clause .= " AND $_ ILIKE ? "; + push @bind_vars, like($form->{$_}); + } + $where_clause .='))'; + } + my $query = <<" SQL"; SELECT DISTINCT $select_clause FROM parts p @@ -508,7 +549,6 @@ sub all_parts { $order_clause $limit_clause SQL - $form->{parts} = selectall_hashref_query($form, $dbh, $query, @bind_vars); map { $_->{onhand} *= 1 } @{ $form->{parts} }; @@ -525,10 +565,10 @@ sub all_parts { my @assemblies; if ($form->{l_assembly} && $form->{bom}) { $query = - qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand, + qq|SELECT p.id, p.partnumber, p.description, a.qty AS assembly_qty, p.unit, p.notes, p.itime::DATE as insertdate, p.sellprice, p.listprice, p.lastcost, - p.rop, p.weight, p.priceupdate, + p.rop, p.weight, p.image, p.drawing, p.microfiche, pfac.factor FROM parts p @@ -617,8 +657,8 @@ sub get_parts { } $j++; - $form->{"type_and_classific_$j"} = $::request->presenter->type_abbreviation($ref->{part_type}). - $::request->presenter->classification_abbreviation($ref->{classification_id}); + $form->{"type_and_classific_$j"} = type_abbreviation($ref->{part_type}). + classification_abbreviation($ref->{classification_id}); $form->{"id_$j"} = $ref->{id}; $form->{"partnumber_$j"} = $ref->{partnumber}; $form->{"description_$j"} = $ref->{description}; @@ -696,37 +736,14 @@ sub retrieve_accounts { # transdate madness. my $transdate = ""; - if ($form->{type} eq "invoice" or $form->{type} eq "credit_note") { + if ( (any {$form->{type} eq $_} qw(invoice credit_note invoice_for_advance_payment final_invoice)) or ($form->{script} eq 'ir.pl') ) { # use deliverydate for sales and purchase invoice, if it exists # also use deliverydate for credit notes - if (!$form->{deliverydate}) { - $transdate = $form->{invdate}; - } else { - $transdate = $form->{deliverydate}; - } - } elsif ($form->{script} eq 'ir.pl') { - # when a purchase invoice is opened from the report of purchase invoices - # $form->{type} isn't set, but $form->{script} is, not sure why this is or - # whether this distinction matters in some other scenario. Otherwise one - # could probably take out this elsif and add a - # " or $form->{script} eq 'ir.pl' " - # to the above if-statement - if (!$form->{deliverydate}) { - $transdate = $form->{invdate}; - } else { - $transdate = $form->{deliverydate}; - } - } elsif (($form->{type} eq "credit_note") and $form->{deliverydate}) { - # if credit_note has a deliverydate, use this instead of invdate - # useful for credit_notes of invoices from an old period with different tax - # if there is no deliverydate then invdate is used, old default (see next elsif) - # Falls hier der Stichtag für Steuern anders bestimmt wird, - # entsprechend auch bei Taxkeys.pm anpassen - $transdate = $form->{deliverydate}; - } elsif (($form->{type} eq "credit_note") || ($form->{script} eq 'ir.pl')) { - $transdate = $form->{invdate}; + $transdate = $form->{tax_point} || $form->{deliverydate} || $form->{invdate}; } else { - $transdate = $form->{transdate}; + my $deliverydate; + $deliverydate = $form->{reqdate} if any { $_ eq $form->{type} } qw(sales_order request_quotation purchase_order); + $transdate = $form->{tax_point} || $deliverydate || $form->{transdate}; } if ($transdate eq "") { @@ -762,8 +779,9 @@ sub retrieve_accounts { p.id IN ($in) SQL - my $sth_tax = prepare_query($::form, $dbh, < $part_id) = each %args) { my $ref = $accno_by_part{$part_id} or next; @@ -787,13 +806,13 @@ SQL $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} for qw(inventory income expense); - $sth_tax->execute($accounts{$inc_exp}, quote_db_date($transdate)); + $sth_tax->execute($accounts{$inc_exp}, quote_db_date($transdate)) || $::form->dberror($query_tax); $ref = $sth_tax->fetchrow_hashref or next; $form->{"taxaccounts_$index"} = $ref->{"accno"}; $form->{"taxaccounts"} .= "$ref->{accno} "if $form->{"taxaccounts"} !~ /$ref->{accno}/; - $form->{"$ref->{accno}_${_}"} = $ref->{$_} for qw(rate description taxnumber); + $form->{"$ref->{accno}_${_}"} = $ref->{$_} for qw(rate description taxnumber tax_id); } $sth_tax->finish; @@ -877,6 +896,25 @@ sub prepare_parts_for_printing { $sth->finish(); + $query = qq|SELECT + cp.parts_id, + cp.customer_partnumber AS customer_model, + c.name AS customer_make + FROM part_customer_prices cp + LEFT JOIN customer c ON (cp.customer_id = c.id) + WHERE cp.parts_id IN ($placeholders)|; + + my %customermodel = (); + + $sth = prepare_execute_query($form, $dbh, $query, @part_ids); + + while (my $ref = $sth->fetchrow_hashref()) { + $customermodel{$ref->{parts_id}} ||= []; + push @{ $customermodel{$ref->{parts_id}} }, $ref; + } + + $sth->finish(); + my @columns = qw(ean image microfiche drawing); $query = qq|SELECT id, | . join(', ', @columns) . qq| @@ -886,7 +924,7 @@ sub prepare_parts_for_printing { my %data = selectall_as_map($form, $dbh, $query, 'id', \@columns, @part_ids); my %template_arrays; - map { $template_arrays{$_} = [] } (qw(make model), @columns); + map { $template_arrays{$_} = [] } (qw(make model customer_make customer_model), @columns); foreach my $i (1 .. $rowcount) { my $id = $form->{"${prefix}${i}"}; @@ -900,11 +938,21 @@ sub prepare_parts_for_printing { push @{ $template_arrays{make} }, []; push @{ $template_arrays{model} }, []; - next if (!$makemodel{$id}); + if ($makemodel{$id}) { + foreach my $ref (@{ $makemodel{$id} }) { + map { push @{ $template_arrays{$_}->[-1] }, $ref->{$_} } qw(make model); + } + } + + push @{ $template_arrays{customer_make} }, []; + push @{ $template_arrays{customer_model} }, []; - foreach my $ref (@{ $makemodel{$id} }) { - map { push @{ $template_arrays{$_}->[-1] }, $ref->{$_} } qw(make model); + if ($customermodel{$id}) { + foreach my $ref (@{ $customermodel{$id} }) { + push @{ $template_arrays{$_}->[-1] }, $ref->{$_} for qw(customer_make customer_model); + } } + } my $parts = SL::DB::Manager::Part->get_all(query => [ id => \@part_ids ]); @@ -914,34 +962,15 @@ sub prepare_parts_for_printing { my $id = $form->{"${prefix}${i}"}; next unless $id; my $prt = $parts_by_id{$id}; - my $type_abbr = $::request->presenter->type_abbreviation($prt->part_type); - push @{ $template_arrays{part_type} }, $type_abbr; - push @{ $template_arrays{type_and_classific}}, $type_abbr.$::request->presenter->classification_abbreviation($prt->classification_id); - push @{ $template_arrays{separate} }, $::request->presenter->separate_abbreviation($prt->classification_id); + my $type_abbr = type_abbreviation($prt->part_type); + push @{ $template_arrays{part_type} }, $prt->part_type; + push @{ $template_arrays{part_abbreviation} }, $type_abbr; + push @{ $template_arrays{type_and_classific}}, $type_abbr . classification_abbreviation($prt->classification_id); + push @{ $template_arrays{separate} }, separate_abbreviation($prt->classification_id); } $main::lxdebug->leave_sub(); return %template_arrays; } -sub normalize_text_blocks { - $main::lxdebug->enter_sub(); - - my $self = shift; - my %params = @_; - - my $form = $params{form} || $main::form; - - # check if feature is enabled (select normalize_part_descriptions from defaults) - return unless ($::instance_conf->get_normalize_part_descriptions); - - foreach (qw(description notes)) { - $form->{$_} =~ s/\s+$//s; - $form->{$_} =~ s/^\s+//s; - $form->{$_} =~ s/ {2,}/ /g; - } - $main::lxdebug->leave_sub(); -} - - 1;