X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FIC.pm;h=74cfe17023fa683234f52db6ad368d424a4ece55;hb=e770cd180600810fecd3554e104546236a9c9597;hp=47c5fea4155513db6963a0169a38b43c78174204;hpb=5c111f6cc47536287ff150a543b9191f0c703376;p=kivitendo-erp.git diff --git a/SL/IC.pm b/SL/IC.pm index 47c5fea41..74cfe1702 100644 --- a/SL/IC.pm +++ b/SL/IC.pm @@ -109,83 +109,40 @@ sub get_part { $form->{amount}{IC_expense} = $form->{expense_accno}; $form->{amount}{IC_cogs} = $form->{expense_accno}; - my @pricegroups = (); - my @pricegroups_not_used = (); - # get prices - $query = - qq|SELECT p.parts_id, p.pricegroup_id, p.price, - (SELECT pg.pricegroup - FROM pricegroup pg - WHERE pg.id = p.pricegroup_id) AS pricegroup - FROM prices p - WHERE (parts_id = ?) - ORDER BY pricegroup|; - $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id})); - - #for pricegroups - my $i = 1; - while (($form->{"klass_$i"}, $form->{"pricegroup_id_$i"}, - $form->{"price_$i"}, $form->{"pricegroup_$i"}) - = $sth->fetchrow_array()) { - push @pricegroups, $form->{"pricegroup_id_$i"}; - $i++; - } - - $sth->finish; + $query = <{PRICEGROUPS} = selectall_hashref_query($form, $dbh, $query); - - #find not used pricegroups - while (my $tmp = pop(@{ $form->{PRICEGROUPS} })) { - my $in_use = 0; - foreach my $item (@pricegroups) { - if ($item eq $tmp->{id}) { - $in_use = 1; - last; - } - } - push(@pricegroups_not_used, $tmp) unless ($in_use); + my $row = 1; + foreach $ref (selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}))) { + $form->{"${_}_${row}"} = $ref->{$_} for qw(pricegroup_id pricegroup price); + $row++; } - - # if not used pricegroups are avaible - if (@pricegroups_not_used) { - - foreach my $name (@pricegroups_not_used) { - $form->{"klass_$i"} = "$name->{id}"; - $form->{"pricegroup_id_$i"} = "$name->{id}"; - $form->{"pricegroup_$i"} = "$name->{pricegroup}"; + $form->{price_rows} = $row - 1; + + # get makes + if ($form->{makemodel}) { + #hli + $query = qq|SELECT m.make, m.model,m.lastcost,m.lastcost,m.lastupdate,m.sortorder FROM makemodel m | . + qq|WHERE m.parts_id = ? order by m.sortorder asc|; + my @values = ($form->{id}); + $sth = $dbh->prepare($query); + $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")"); + + my $i = 1; + + while (($form->{"make_$i"}, $form->{"model_$i"}, $form->{"old_lastcost_$i"}, + $form->{"lastcost_$i"}, $form->{"lastupdate_$i"}, $form->{"sortorder_$i"}) = $sth->fetchrow_array) + { $i++; } - } - - #correct rows - $form->{price_rows} = $i - 1; - - unless ($form->{item} eq 'service') { - - # get makes - if ($form->{makemodel}) { - #hli - $query = qq|SELECT m.make, m.model,m.lastcost,m.lastcost,m.lastupdate,m.sortorder FROM makemodel m | . - qq|WHERE m.parts_id = ? order by m.sortorder asc|; - my @values = ($form->{id}); - $sth = $dbh->prepare($query); - $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")"); - - my $i = 1; - - while (($form->{"make_$i"}, $form->{"model_$i"}, $form->{"old_lastcost_$i"}, - $form->{"lastcost_$i"}, $form->{"lastupdate_$i"}, $form->{"sortorder_$i"}) = $sth->fetchrow_array) - { - $i++; - } - $sth->finish; - $form->{makemodel_rows} = $i - 1; + $sth->finish; + $form->{makemodel_rows} = $i - 1; - } } # get translations @@ -199,20 +156,8 @@ sub get_part { } $trq->finish; - # now get accno for taxes - $query = - qq|SELECT c.accno - FROM chart c, partstax pt - WHERE (pt.chart_id = c.id) AND (pt.parts_id = ?)|; - $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id})); - while (my ($key) = $sth->fetchrow_array) { - $form->{amount}{$key} = $key; - } - - $sth->finish; - # is it an orphan - my @referencing_tables = qw(invoice orderitems inventory rmaitems); + my @referencing_tables = qw(invoice orderitems inventory); my %column_map = ( ); my $parts_id = conv_i($form->{id}); @@ -244,7 +189,7 @@ sub get_pricegroups { my $dbh = $form->dbconnect($myconfig); # get pricegroups - my $query = qq|SELECT id, pricegroup FROM pricegroup|; + my $query = qq|SELECT id, pricegroup FROM pricegroup ORDER BY lower(pricegroup)|; my $pricegroups = selectall_hashref_query($form, $dbh, $query); my $i = 1; @@ -330,19 +275,14 @@ sub save { } $sth->finish; - if ($form->{item} ne 'service') { - # delete makemodel records - do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id})); - } + # delete makemodel records + do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id})); if ($form->{item} eq 'assembly') { # delete assembly records do_query($form, $dbh, qq|DELETE FROM assembly WHERE id = ?|, conv_i($form->{id})); } - # delete tax records - do_query($form, $dbh, qq|DELETE FROM partstax WHERE parts_id = ?|, conv_i($form->{id})); - # delete translations do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id})); @@ -366,7 +306,7 @@ sub save { $form->{orphaned} = 1; } - my $partsgroup_id = 0; + my $partsgroup_id = undef; if ($form->{partsgroup}) { (my $partsgroup, $partsgroup_id) = split(/--/, $form->{partsgroup}); @@ -476,26 +416,20 @@ sub save { # delete price records do_query($form, $dbh, qq|DELETE FROM prices WHERE parts_id = ?|, conv_i($form->{id})); + $query = qq|INSERT INTO prices (parts_id, pricegroup_id, price) VALUES(?, ?, ?)|; + $sth = prepare_query($form, $dbh, $query); + # insert price records only if different to sellprice for my $i (1 .. $form->{price_rows}) { my $price = $form->parse_amount($myconfig, $form->{"price_$i"}); - if ($price == 0) { - $form->{"price_$i"} = $form->{sellprice}; - } - if ( - ( $price - || $form->{"klass_$i"} - || $form->{"pricegroup_id_$i"}) - and $price != $form->{sellprice} - ) { - #$klass = $form->parse_amount($myconfig, $form->{"klass_$i"}); - $query = qq|INSERT INTO prices (parts_id, pricegroup_id, price) | . - qq|VALUES(?, ?, ?)|; - @values = (conv_i($form->{id}), conv_i($form->{"pricegroup_id_$i"}), $price); - do_query($form, $dbh, $query, @values); - } + next unless $price && ($price != $form->{sellprice}); + + @values = (conv_i($form->{id}), conv_i($form->{"pricegroup_id_$i"}), $price); + do_statement($form, $sth, $query, @values); } + $sth->finish; + # insert makemodel records my $lastupdate = ''; my $value = 0; @@ -521,17 +455,6 @@ sub save { } } - # insert taxes - foreach my $item (split(/ /, $form->{taxaccounts})) { - if ($form->{"IC_tax_$item"}) { - $query = - qq|INSERT INTO partstax (parts_id, chart_id) - VALUES (?, (SELECT id FROM chart WHERE accno = ?))|; - @values = (conv_i($form->{id}), $item); - do_query($form, $dbh, $query, @values); - } - } - # add assembly records if ($form->{item} eq 'assembly') { @@ -668,7 +591,7 @@ sub delete { my %columns = ( "assembly" => "id", "parts" => "id" ); - for my $table (qw(prices partstax makemodel inventory assembly translation parts)) { + for my $table (qw(prices makemodel inventory assembly translation parts)) { my $column = defined($columns{$table}) ? $columns{$table} : "parts_id"; do_query($form, $dbh, qq|DELETE FROM $table WHERE $column = ?|, @values); } @@ -798,7 +721,7 @@ sub all_parts { my @apoe_filters = qw(transdate); 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(listprice sellprice lastcost priceupdate weight unit bin rop image)); + my @simple_l_switches = (@all_columns, qw(notes listprice sellprice lastcost priceupdate weight unit bin rop image)); 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); @@ -1013,7 +936,7 @@ sub all_parts { $joins_needed{makemodel} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters; $joins_needed{mv} = 1 if $joins_needed{makemodel}; $joins_needed{cv} = 1 if $bsooqr; - $joins_needed{apoe} = 1 if $joins_needed{cv} || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters; + $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; # special case for description search. @@ -1099,7 +1022,7 @@ sub all_parts { if ($form->{searchitems} eq 'assembly' && $form->{bom}) { $query = qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand, - p.unit, p.bin, + p.unit, p.bin, p.notes, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight, p.priceupdate, p.image, p.drawing, p.microfiche, @@ -1126,6 +1049,26 @@ sub all_parts { $form->{parts} = \@assemblies; } + if ($form->{l_pricegroups} ) { + my $query = <{parts} }) { + do_statement($form, $sth, $query, conv_i($part->{id})); + + while (my $ref = $sth->fetchrow_hashref("NAME_lc")) { + $part->{"pricegroup_$ref->{pricegroup_id}"} = $ref->{price}; + } + $sth->finish; + } + }; + + $main::lxdebug->leave_sub(); return wantarray ? @{ $form->{parts} } : $form->{parts}; @@ -1544,12 +1487,33 @@ sub retrieve_accounts { # transdate madness. my $transdate = ""; - if ($form->{type} eq "invoice") { - if (($form->{vc} eq "vendor") || !$form->{deliverydate}) { + if ($form->{type} eq "invoice" or $form->{type} eq "credit_note") { + # 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}; } else { @@ -1668,8 +1632,8 @@ sub prepare_parts_for_printing { my $self = shift; my %params = @_; - my $myconfig = \%main::myconfig; - my $form = $main::form; + my $myconfig = $params{myconfig} || \%main::myconfig; + my $form = $params{form} || $main::form; my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);