X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FOE.pm;h=5c6cec6581cb45fd56cff6e7e54591e124e100f3;hb=06af498f71c9424b2d373d12b4dd90cdac58e070;hp=2e7a6bebb10562e14b87ed62af63944313165d32;hpb=b6dc5623d93c1be1c54248d4512e80f495af2899;p=kivitendo-erp.git diff --git a/SL/OE.pm b/SL/OE.pm index 2e7a6bebb..5c6cec658 100644 --- a/SL/OE.pm +++ b/SL/OE.pm @@ -148,7 +148,8 @@ sub transactions { push(@values, '%' . $form->{transaction_description} . '%'); } - my $sortorder = join(', ', ("o.id", $form->sort_columns("transdate", $ordnumber, "name"))); + my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; + my $sortorder = join(', ', map { "${_} ${sortdir} " } ("o.id", $form->sort_columns("transdate", $ordnumber, "name"))); my %allowed_sort_columns = ( "transdate" => "o.transdate", "reqdate" => "o.reqdate", @@ -162,7 +163,7 @@ sub transactions { "transaction_description" => "o.transaction_description" ); if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) { - $sortorder = $allowed_sort_columns{$form->{sort}}; + $sortorder = $allowed_sort_columns{$form->{sort}} . " ${sortdir}"; } $query .= qq| ORDER by | . $sortorder; @@ -184,6 +185,44 @@ sub transactions { $main::lxdebug->leave_sub(); } +sub transactions_for_todo_list { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my $query = qq|SELECT id FROM employee WHERE login = ?|; + my ($e_id) = selectrow_query($form, $dbh, $query, $form->{login}); + + $query = + qq|SELECT oe.id, oe.transdate, oe.reqdate, oe.quonumber, oe.transaction_description, oe.amount, + CASE WHEN (COALESCE(0, oe.customer_id) = 0) THEN 'vendor' ELSE 'customer' END AS vc, + c.name AS customer, + v.name AS vendor, + e.name AS employee + FROM oe + LEFT JOIN customer c ON (oe.customer_id = c.id) + LEFT JOIN vendor v ON (oe.vendor_id = v.id) + LEFT JOIN employee e ON (oe.employee_id = e.id) + WHERE (COALESCE(quotation, FALSE) = TRUE) + AND (COALESCE(closed, FALSE) = FALSE) + AND ((oe.employee_id = ?) OR (oe.salesman_id = ?)) + AND NOT (oe.reqdate ISNULL) + AND (oe.reqdate < current_date) + ORDER BY transdate|; + + my $quotations = selectall_hashref_query($form, $dbh, $query, $e_id, $e_id); + + $main::lxdebug->leave_sub(); + + return $quotations; +} + sub save { $main::lxdebug->enter_sub(); @@ -207,8 +246,6 @@ sub save { if ($form->{id}) { - &adj_onhand($dbh, $form, $ml) if $form->{type} =~ /_order$/; - $query = qq|DELETE FROM orderitems WHERE trans_id = ?|; do_query($form, $dbh, $query, $form->{id}); @@ -347,7 +384,7 @@ sub save { } $query .= qq|?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (SELECT factor FROM price_factors WHERE id = ?), ?)|; - push(@values, + push(@values, conv_i($form->{id}), conv_i($form->{"id_$i"}), $form->{"description_$i"}, $form->{"longdescription_$i"}, $form->{"qty_$i"}, $baseqty, @@ -382,11 +419,11 @@ sub save { $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{transdate}, ($form->{vc} eq 'customer') ? 'buy' : 'sell'); } - $form->{exchangerate} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{exchangerate}); + $form->{exchangerate} = $exchangerate || $form->parse_amount($myconfig, $form->{exchangerate}); my $quotation = $form->{type} =~ /_order$/ ? 'f' : 't'; - ($null, $form->{department_id}) = split(/--/, $form->{department}); + ($null, $form->{department_id}) = split(/--/, $form->{department}) if $form->{department}; # save OE record $query = @@ -432,6 +469,26 @@ sub save { # save printed, emailed, queued $form->save_status($dbh); + # Link this record to the records it was created from. + $form->{convert_from_oe_ids} =~ s/^\s+//; + $form->{convert_from_oe_ids} =~ s/\s+$//; + my @convert_from_oe_ids = split m/\s+/, $form->{convert_from_oe_ids}; + delete $form->{convert_from_oe_ids}; + + if (scalar @convert_from_oe_ids) { + RecordLinks->create_links('dbh' => $dbh, + 'mode' => 'ids', + 'from_table' => 'oe', + 'from_ids' => \@convert_from_oe_ids, + 'to_table' => 'oe', + 'to_id' => $form->{id}, + ); + + $self->_close_quotations_rfqs('dbh' => $dbh, + 'from_id' => \@convert_from_oe_ids, + 'to_id' => $form->{id}); + } + if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { if ($form->{vc} eq 'customer') { $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, $form->{exchangerate}, 0); @@ -441,12 +498,6 @@ sub save { } } - if ($form->{type} =~ /_order$/) { - - # adjust onhand - &adj_onhand($dbh, $form, $ml * -1); - } - $form->{saved_xyznumber} = $form->{$form->{type} =~ /_quotation$/ ? "quonumber" : "ordnumber"}; @@ -460,40 +511,48 @@ sub save { return $rc; } -# this function closes multiple orders given in $form->{ordnumber_#}. -# use this for multiple orders that don't have to be saved back -# single orders should use OE::save instead. -sub close_orders { +sub _close_quotations_rfqs { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my $self = shift; + my %params = @_; - # get ids from $form - map { push @ids, $form->{"ordnumber_$_"} if $form->{"ordnumber_$_"} } - (1 .. $form->{rowcount}); + Common::check_params(\%params, qw(from_id to_id)); - my $dbh = $form->dbconnect($myconfig); - $query = qq|UPDATE oe SET | . - qq|closed = TRUE | . - qq|WHERE ordnumber IN (| - . join(', ', map { $dbh->quote($_) } @ids) . qq|)|; - $dbh->do($query) || $form->dberror($query); - $dbh->disconnect; + my $myconfig = \%main::myconfig; + my $form = $main::form; - $main::lxdebug->leave_sub(); -} + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); -sub close_order { - $main::lxdebug->enter_sub(); + my $query = qq|SELECT quotation FROM oe WHERE id = ?|; + my $sth = prepare_query($form, $dbh, $query); - my ($self, $myconfig, $form) = @_; + do_statement($form, $sth, $query, conv_i($params{to_id})); - return $main::lxdebug->leave_sub() unless ($form->{"id"}); + my ($quotation) = $sth->fetchrow_array(); - my $dbh = $form->dbconnect($myconfig); - do_query($form, $dbh, qq|UPDATE oe SET closed = TRUE where id = ?|, - $form->{"id"}); - $dbh->disconnect; + if ($quotation) { + $main::lxdebug->leave_sub(); + return; + } + + my @close_ids; + + foreach my $from_id (@{ $params{from_id} }) { + $from_id = conv_i($from_id); + do_statement($form, $sth, $query, $from_id); + ($quotation) = $sth->fetchrow_array(); + push @close_ids, $from_id if ($quotation); + } + + $sth->finish(); + + if (scalar @close_ids) { + $query = qq|UPDATE oe SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar @close_ids) . qq|)|; + do_query($form, $dbh, $query, @close_ids); + + $dbh->commit() unless ($params{dbh}); + } $main::lxdebug->leave_sub(); } @@ -521,25 +580,9 @@ sub delete { } $sth->finish; - $query = qq|SELECT o.parts_id, o.ship FROM orderitems o | . - qq|WHERE o.trans_id = ?|; - @values = (conv_i($form->{id})); - $sth = $dbh->prepare($query); - $sth->execute(@values) || $self->dberror($query); - - while (my ($id, $ship) = $sth->fetchrow_array) { - $form->update_balance($dbh, "parts", "onhand", qq|id = $id|, $ship * -1); - } - $sth->finish; - # delete-values @values = (conv_i($form->{id})); - # delete inventory - $query = qq|DELETE FROM inventory | . - qq|WHERE oe_id = ?|; - do_query($form, $dbh, $query, @values); - # delete status entries $query = qq|DELETE FROM status | . qq|WHERE trans_id = ?|; @@ -589,13 +632,22 @@ sub retrieve { if ($form->{"multi_id_$_"} and $form->{"trans_id_$_"}) } (1 .. $form->{"rowcount"}); + if ($form->{rowcount} && scalar @ids) { + $form->{convert_from_oe_ids} = join ' ', @ids; + } + # if called in multi id mode, and still only got one id, switch back to single id if ($form->{"rowcount"} and $#ids == 0) { $form->{"id"} = $ids[0]; undef @ids; } - $query_add = qq|, current_date AS transdate, current_date AS reqdate| if (!$form->{id}); + my $query_add = ''; + if (!$form->{id}) { + my $wday = (localtime(time))[6]; + my $next_workday = $wday == 5 ? 3 : $wday == 6 ? 2 : 1; + $query_add = qq|, current_date AS transdate, date(current_date + interval '${next_workday} days') AS reqdate|; + } # get default accounts $query = qq|SELECT (SELECT c.accno FROM chart c WHERE d.inventory_accno_id = c.id) AS inventory_accno, @@ -609,7 +661,7 @@ sub retrieve { my $ref = selectfirst_hashref_query($form, $dbh, $query); map { $form->{$_} = $ref->{$_} } keys %$ref; - ($form->{currency}) = split(/:/, $form->{currencies}); + ($form->{currency}) = split(/:/, $form->{currencies}) unless ($form->{currency}); # set reqdate if this is an invoice->order conversion. If someone knows a better check to ensure # we come from invoices, feel free. @@ -896,6 +948,8 @@ sub order_details { $form->{"globalprojectnumber"} = $projectnumbers{$form->{"globalproject_id"}}; + $form->{discount} = []; + my @arrays = qw(runningnumber number description longdescription qty ship unit bin partnotes serialnumber reqdate sellprice listprice netprice @@ -956,8 +1010,9 @@ sub order_details { my ($dec) = ($sellprice =~ /\.(\d+)/); my $decimalplaces = max 2, length($dec); - my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $form->{"discount_$i"} / 100 / $price_factor->{factor}, $decimalplaces); - my $linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice * (100 - $form->{"discount_$i"}) / 100 / $price_factor->{factor}, 2); + my $parsed_discount = $form->parse_amount($myconfig, $form->{"discount_$i"}); + my $discount = $form->round_amount($form->{"qty_$i"} * $sellprice * $parsed_discount / 100 / $price_factor->{factor}, $decimalplaces); + my $linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice * (100 - $parsed_discount) / 100 / $price_factor->{factor}, 2); my $nodiscount_linetotal = $form->round_amount($form->{"qty_$i"} * $sellprice / $price_factor->{factor}, 2); $form->{"netprice_$i"} = $form->round_amount($form->{"qty_$i"} ? ($linetotal / $form->{"qty_$i"}) : 0, 2); @@ -965,7 +1020,7 @@ sub order_details { $linetotal = ($linetotal != 0) ? $linetotal : ''; - push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, $decimalplaces) : ''; + push @{ $form->{discount} }, ($discount != 0) ? $form->format_amount($myconfig, $discount * -1, 2) : ''; push @{ $form->{p_discount} }, $form->{"discount_$i"}; $form->{ordtotal} += $linetotal; @@ -1118,63 +1173,4 @@ sub project_description { return $value; } -sub adj_onhand { - $main::lxdebug->enter_sub(); - - my ($dbh, $form, $ml) = @_; - - my $all_units = $form->{all_units}; - - my $query = - qq|SELECT oi.parts_id, oi.ship, oi.unit, p.inventory_accno_id, p.assembly | . - qq| FROM orderitems oi | . - qq| JOIN parts p ON (p.id = oi.parts_id) | . - qq| WHERE oi.trans_id = ?|; - my @values = ($form->{id}); - my $sth = $dbh->prepare($query); - $sth->execute(@values) || $form->dberror($query); - - $query = - qq|SELECT sum(p.inventory_accno_id) | . - qq|FROM parts p | . - qq|JOIN assembly a ON (a.parts_id = p.id) | . - qq|WHERE a.id = ?|; - my $ath = $dbh->prepare($query) || $form->dberror($query); - - my $ispa; - - while (my $ref = $sth->fetchrow_hashref(NAME_lc)) { - if ($ref->{inventory_accno_id} || $ref->{assembly}) { - - # do not update if assembly consists of all services - if ($ref->{assembly}) { - $ath->execute($ref->{parts_id}) || $form->dberror($query); - - ($ispa) = $sth->fetchrow_array; - $ath->finish; - - next unless $ispa; - - } - - # get item baseunit - $query = qq|SELECT unit FROM parts WHERE id = ?|; - my ($item_unit) = selectrow_query($form, $dbh, $query, $ref->{parts_id}); - - my $basefactor = 1; - if (defined($all_units->{$item_unit}->{factor}) && (($all_units->{$item_unit}->{factor} * 1) != 0)) { - $basefactor = $all_units->{$ref->{unit}}->{factor} / $all_units->{$item_unit}->{factor}; - } - my $baseqty = $ref->{ship} * $basefactor; - - # adjust onhand in parts table - $form->update_balance($dbh, "parts", "onhand", qq|id = $ref->{parts_id}|, $baseqty * $ml); - } - } - - $sth->finish; - - $main::lxdebug->leave_sub(); -} - 1;