X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIC.pm;h=e36cc4fae47efe0fcb24094a6e422daf229c98f4;hb=d4608fb4eff9392728d1287de4df3e865ebe7f6e;hp=a39e8ae8dfe07d4aba31333f3f869014fa983472;hpb=f7b15d4334848fad1bcd0b569eff5139f9a6ba4d;p=kivitendo-erp.git diff --git a/SL/IC.pm b/SL/IC.pm index a39e8ae8d..e36cc4fae 100644 --- a/SL/IC.pm +++ b/SL/IC.pm @@ -49,33 +49,6 @@ use Carp; use strict; -sub get_pricegroups { - $main::lxdebug->enter_sub(); - - my ($self, $myconfig, $form) = @_; - - my $dbh = $form->get_standard_dbh; - - # get pricegroups - my $query = qq|SELECT id, pricegroup FROM pricegroup ORDER BY lower(pricegroup)|; - my $pricegroups = selectall_hashref_query($form, $dbh, $query); - - my $i = 1; - foreach my $pg (@{ $pricegroups }) { - $form->{"price_$i"} = $form->format_amount($myconfig, $form->{"price_$i"}, -2); - $form->{"pricegroup_id_$i"} = "$pg->{id}"; - $form->{"pricegroup_$i"} = "$pg->{pricegroup}"; - $i++; - } - - #correct rows - $form->{price_rows} = $i - 1; - - $main::lxdebug->leave_sub(); - - return $pricegroups; -} - sub retrieve_buchungsgruppen { $main::lxdebug->enter_sub(); @@ -129,6 +102,7 @@ sub assembly_item { 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.price_factor_id, pfac.factor AS price_factor, p.notes as longdescription FROM parts p @@ -154,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 @@ -162,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 @@ -177,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 @@ -193,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... @@ -204,13 +186,14 @@ 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); # my @other_flags = qw(onhand); # ToDO: implement these # my @inactive_flags = qw(l_subtotal short l_linetotal); - my @select_tokens = qw(id factor); + my @select_tokens = qw(id factor part_type classification_id); my @where_tokens = qw(1=1); my @group_tokens = (); my @bind_vars = (); @@ -238,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.', @@ -287,7 +272,7 @@ sub all_parts { insertdate => 'itime::DATE', ); - if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) { + if ($form->{l_assembly} && $form->{l_lastcost}) { @simple_l_switches = grep { $_ ne 'lastcost' } @simple_l_switches; } @@ -377,10 +362,23 @@ sub all_parts { push @select_tokens, $_; } - for ($form->{searchitems}) { - push @where_tokens, "p.part_type = 'part'" if /part/; - push @where_tokens, "p.part_type = 'service'" if /service/; - push @where_tokens, "p.part_type = 'assembly'" if /assembly/; + # Oder Bedingungen fuer Ware Dienstleistung Erzeugnis: + if ($form->{l_part} || $form->{l_assembly} || $form->{l_service} || $form->{l_assortment}) { + my @or_tokens = (); + push @or_tokens, "p.part_type = 'service'" if $form->{l_service}; + push @or_tokens, "p.part_type = 'assembly'" if $form->{l_assembly}; + push @or_tokens, "p.part_type = 'part'" if $form->{l_part}; + push @or_tokens, "p.part_type = 'assortment'" if $form->{l_assortment}; + push @where_tokens, join ' OR ', map { "($_)" } @or_tokens; + } + else { + # gar keine Teile + push @where_tokens, q|'F' = 'T'|; + } + + if ( $form->{classification_id} > 0 ) { + push @where_tokens, "p.classification_id = ?"; + push @bind_vars, $form->{classification_id}; } for ($form->{itemstatus}) { @@ -438,7 +436,7 @@ sub all_parts { push @select_tokens, @qsooqr_flags, 'quotation', 'cv', 'ioi.id', 'ioi.ioi' if $bsooqr; push @select_tokens, @deliverydate_flags if $bsooqr && $form->{l_deliverydate}; - push @select_tokens, $q_assembly_lastcost if ($form->{searchitems} eq 'assembly') && $form->{l_lastcost}; + push @select_tokens, $q_assembly_lastcost if $form->{l_assembly} && $form->{l_lastcost}; push @bsooqr_tokens, q|module = 'ir' AND NOT ioi.assemblyitem| if $form->{bought}; push @bsooqr_tokens, q|module = 'is' AND NOT ioi.assemblyitem| if $form->{sold}; push @bsooqr_tokens, q|module = 'oe' AND NOT quotation AND cv = 'customer'| if $form->{ordered}; @@ -455,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}%' @@ -491,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', @@ -536,7 +546,7 @@ sub all_parts { # post processing for assembly parts lists (bom) # for each part get the assembly parts and add them into the partlist. my @assemblies; - if ($form->{searchitems} eq 'assembly' && $form->{bom}) { + if ($form->{l_assembly} && $form->{bom}) { $query = qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand, p.unit, p.notes, p.itime::DATE as insertdate, @@ -583,210 +593,11 @@ SQL } $sth->finish; } - }; - - - $main::lxdebug->leave_sub(); - - return @{ $form->{parts} }; -} - -sub _create_filter_for_priceupdate { - $main::lxdebug->enter_sub(); - - my $self = shift; - my $myconfig = \%main::myconfig; - my $form = $main::form; - - my @where_values; - my $where = '1 = 1'; - - foreach my $item (qw(partnumber drawing microfiche make model pg.partsgroup)) { - my $column = $item; - $column =~ s/.*\.//; - next unless ($form->{$column}); - - $where .= qq| AND $item ILIKE ?|; - push(@where_values, like($form->{$column})); - } - - foreach my $item (qw(description serialnumber)) { - next unless ($form->{$item}); - - $where .= qq| AND (${item} ILIKE ?)|; - push(@where_values, like($form->{$item})); - } - - - # items which were never bought, sold or on an order - if ($form->{itemstatus} eq 'orphaned') { - $where .= - qq| AND (p.onhand = 0) - AND p.id NOT IN - ( - SELECT DISTINCT parts_id FROM invoice - UNION - SELECT DISTINCT parts_id FROM assembly - UNION - SELECT DISTINCT parts_id FROM orderitems - )|; - - } elsif ($form->{itemstatus} eq 'active') { - $where .= qq| AND p.obsolete = '0'|; - - } elsif ($form->{itemstatus} eq 'obsolete') { - $where .= qq| AND p.obsolete = '1'|; - - } elsif ($form->{itemstatus} eq 'onhand') { - $where .= qq| AND p.onhand > 0|; - - } elsif ($form->{itemstatus} eq 'short') { - $where .= qq| AND p.onhand < p.rop|; - - } - - foreach my $column (qw(make model)) { - next unless ($form->{$column}); - $where .= qq| AND p.id IN (SELECT DISTINCT parts_id FROM makemodel WHERE $column ILIKE ?|; - push(@where_values, like($form->{$column})); } $main::lxdebug->leave_sub(); - return ($where, @where_values); -} - -sub get_num_matches_for_priceupdate { - $main::lxdebug->enter_sub(); - - my $self = shift; - - my $myconfig = \%main::myconfig; - my $form = $main::form; - - my $dbh = $form->get_standard_dbh($myconfig); - - my ($where, @where_values) = $self->_create_filter_for_priceupdate(); - - my $num_updated = 0; - my $query; - - for my $column (qw(sellprice listprice)) { - next if ($form->{$column} eq ""); - - $query = - qq|SELECT COUNT(*) - FROM parts - WHERE id IN - (SELECT p.id - FROM parts p - LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) - WHERE $where)|; - my ($result) = selectfirst_array_query($form, $dbh, $query, @where_values); - $num_updated += $result if (0 <= $result); - } - - $query = - qq|SELECT COUNT(*) - FROM prices - WHERE parts_id IN - (SELECT p.id - FROM parts p - LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) - WHERE $where) AND (pricegroup_id = ?)|; - my $sth = prepare_query($form, $dbh, $query); - - for my $i (1 .. $form->{price_rows}) { - next if ($form->{"price_$i"} eq ""); - - my ($result) = do_statement($form, $sth, $query, @where_values, conv_i($form->{"pricegroup_id_$i"})); - $num_updated += $result if (0 <= $result); - } - $sth->finish(); - - $main::lxdebug->leave_sub(); - - return $num_updated; -} - -sub update_prices { - my ($self, $myconfig, $form) = @_; - $main::lxdebug->enter_sub(); - - my $num_updated = SL::DB->client->with_transaction(\&_update_prices, $self, $myconfig, $form); - - $main::lxdebug->leave_sub(); - return $num_updated; -} - -sub _update_prices { - my ($self, $myconfig, $form) = @_; - - my ($where, @where_values) = $self->_create_filter_for_priceupdate(); - my $num_updated = 0; - - # connect to database - my $dbh = SL::DB->client->dbh; - - for my $column (qw(sellprice listprice)) { - next if ($form->{$column} eq ""); - - my $value = $form->parse_amount($myconfig, $form->{$column}); - my $operator = '+'; - - if ($form->{"${column}_type"} eq "percent") { - $value = ($value / 100) + 1; - $operator = '*'; - } - - my $query = - qq|UPDATE parts SET $column = $column $operator ? - WHERE id IN - (SELECT p.id - FROM parts p - LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) - WHERE $where)|; - my $result = do_query($form, $dbh, $query, $value, @where_values); - $num_updated += $result if (0 <= $result); - } - - my $q_add = - qq|UPDATE prices SET price = price + ? - WHERE parts_id IN - (SELECT p.id - FROM parts p - LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) - WHERE $where) AND (pricegroup_id = ?)|; - my $sth_add = prepare_query($form, $dbh, $q_add); - - my $q_multiply = - qq|UPDATE prices SET price = price * ? - WHERE parts_id IN - (SELECT p.id - FROM parts p - LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id) - WHERE $where) AND (pricegroup_id = ?)|; - my $sth_multiply = prepare_query($form, $dbh, $q_multiply); - - for my $i (1 .. $form->{price_rows}) { - next if ($form->{"price_$i"} eq ""); - - my $value = $form->parse_amount($myconfig, $form->{"price_$i"}); - my $result; - - if ($form->{"pricegroup_type_$i"} eq "percent") { - $result = do_statement($form, $sth_multiply, $q_multiply, ($value / 100) + 1, @where_values, conv_i($form->{"pricegroup_id_$i"})); - } else { - $result = do_statement($form, $sth_add, $q_add, $value, @where_values, conv_i($form->{"pricegroup_id_$i"})); - } - - $num_updated += $result if (0 <= $result); - } - - $sth_add->finish(); - $sth_multiply->finish(); - - return $num_updated; + return $form->{parts}; } # get partnumber, description, unit, sellprice and soldtotal with choice through $sortorder for Top100 @@ -815,7 +626,8 @@ sub get_parts { } my $query = - qq|SELECT id, partnumber, description, unit, sellprice + qq|SELECT id, partnumber, description, unit, sellprice, + classification_id FROM parts WHERE $where ORDER BY $order|; @@ -828,6 +640,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->{"id_$j"} = $ref->{id}; $form->{"partnumber_$j"} = $ref->{partnumber}; $form->{"description_$j"} = $ref->{description}; @@ -971,7 +785,7 @@ 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; @@ -996,7 +811,7 @@ 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"}; @@ -1122,12 +937,16 @@ sub prepare_parts_for_printing { for my $i (1..$rowcount) { my $id = $form->{"${prefix}${i}"}; next unless $id; - - push @{ $template_arrays{part_type} }, $parts_by_id{$id}->type; + my $prt = $parts_by_id{$id}; + my $type_abbr = $::request->presenter->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.$::request->presenter->classification_abbreviation($prt->classification_id); + push @{ $template_arrays{separate} }, $::request->presenter->separate_abbreviation($prt->classification_id); } - return %template_arrays; $main::lxdebug->leave_sub(); + return %template_arrays; } sub normalize_text_blocks {