X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;f=SL%2FDO.pm;h=40b2c409ce2be6bf5c3dd592f3394cba1727f9a4;hb=ba233a63330839f96a92a8001e13f7e1e120f7d5;hp=79dc341dfecb10926be84c234f48644e3a3075a1;hpb=454df69edeb9bfe5a1a9c06c2788d1e1175470db;p=kivitendo-erp.git diff --git a/SL/DO.pm b/SL/DO.pm index 79dc341df..40b2c409c 100644 --- a/SL/DO.pm +++ b/SL/DO.pm @@ -39,6 +39,7 @@ use YAML; use SL::AM; use SL::Common; use SL::DBUtils; +use SL::RecordLinks; sub transactions { $main::lxdebug->enter_sub(); @@ -62,14 +63,12 @@ sub transactions { dord.transaction_description, pr.projectnumber AS globalprojectnumber, e.name AS employee, - sm.name AS salesman, - oe.id AS oe_id + sm.name AS salesman FROM delivery_orders dord LEFT JOIN $vc ct ON (dord.${vc}_id = ct.id) LEFT JOIN employee e ON (dord.employee_id = e.id) LEFT JOIN employee sm ON (dord.salesman_id = sm.id) - LEFT JOIN project pr ON (dord.globalproject_id = pr.id) - LEFT JOIN oe ON ((dord.ordnumber = oe.ordnumber) AND NOT COALESCE(oe.quotation, FALSE))|; + LEFT JOIN project pr ON (dord.globalproject_id = pr.id)|; push @where, ($form->{type} eq 'sales_delivery_order' ? '' : 'NOT ') . qq|COALESCE(dord.is_sales, FALSE)|; @@ -143,16 +142,34 @@ sub transactions { "transaction_description" => "dord.transaction_description" ); - my $sortoder = "dord.id"; + my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC'; + my $sortorder = "dord.id"; if ($form->{sort} && grep($form->{sort}, keys(%allowed_sort_columns))) { $sortorder = $allowed_sort_columns{$form->{sort}}; } - $query .= qq| ORDER by | . $sortorder; + $query .= qq| ORDER by | . $sortorder . " $sortdir"; $form->{DO} = selectall_hashref_query($form, $dbh, $query, @values); - $main::lxdebug->dump(0, "DO", $form->{DO}); + if (scalar @{ $form->{DO} }) { + $query = + qq|SELECT id + FROM oe + WHERE NOT COALESCE(quotation, FALSE) + AND (ordnumber = ?) + AND (COALESCE(${vc}_id, 0) != 0)|; + + my $sth = prepare_query($form, $dbh, $query); + + foreach my $dord (@{ $form->{DO} }) { + next unless ($dord->{ordnumber}); + do_statement($form, $sth, $query, $dord->{ordnumber}); + ($dord->{oe_id}) = $sth->fetchrow_array(); + } + + $sth->finish(); + } $main::lxdebug->leave_sub(); } @@ -327,6 +344,20 @@ sub save { # save printed, emailed, queued $form->save_status($dbh); + # Link this delivery order to the quotations it was created from. + RecordLinks->create_links('dbh' => $dbh, + 'mode' => 'ids', + 'from_table' => 'oe', + 'from_ids' => $form->{convert_from_oe_ids}, + 'to_table' => 'delivery_orders', + 'to_id' => $form->{id}, + ); + delete $form->{convert_from_oe_ids}; + + $self->mark_orders_if_delivered('do_id' => $form->{id}, + 'type' => $form->{type} eq 'sales_delivery_order' ? 'sales' : 'purchase', + 'dbh' => $dbh,); + my $rc = $dbh->commit(); $form->{saved_donumber} = $form->{donumber}; @@ -338,19 +369,96 @@ sub save { return $rc; } -sub close_order { +sub mark_orders_if_delivered { $main::lxdebug->enter_sub(); - my ($self) = @_; + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(do_id type)); my $myconfig = \%main::myconfig; my $form = $main::form; - return $main::lxdebug->leave_sub() unless ($form->{id}); + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); - my $dbh = $form->get_standard_dbh($myconfig); - do_query($form, $dbh, qq|UPDATE do SET closed = TRUE where id = ?|, conv_i($form->{id})); - $dbh->commit(); + my @links = RecordLinks->get_links('dbh' => $dbh, + 'from_table' => 'oe', + 'to_table' => 'delivery_orders', + 'to_id' => $params{do_id}); + + my ($oe_id) = $links[0]->{from_id} if (scalar @links); + + return $main::lxdebug->leave_sub() if (!$oe_id); + + my $all_units = AM->retrieve_all_units(); + + my $query = qq|SELECT oi.parts_id, oi.qty, oi.unit, p.unit AS partunit + FROM orderitems oi + LEFT JOIN parts p ON (oi.parts_id = p.id) + WHERE (oi.trans_id = ?)|; + my $sth = prepare_execute_query($form, $dbh, $query, $oe_id); + + my %shipped = $self->get_shipped_qty('type' => $params{type}, + 'oe_id' => $oe_id,); + my %ordered = (); + + while (my $ref = $sth->fetchrow_hashref()) { + $ref->{baseqty} = $ref->{qty} * $all_units->{$ref->{unit}}->{factor} / $all_units->{$ref->{partunit}}->{factor}; + + if ($ordered{$ref->{parts_id}}) { + $ordered{$ref->{parts_id}}->{baseqty} += $ref->{baseqty}; + } else { + $ordered{$ref->{parts_id}} = $ref; + } + } + + $sth->finish(); + + map { $_->{baseqty} = $_->{qty} * $all_units->{$_->{unit}}->{factor} / $all_units->{$_->{partunit}}->{factor} } values %shipped; + + my $delivered = 1; + foreach my $part (values %ordered) { + if (!$shipped{$part->{parts_id}} || ($shipped{$part->{parts_id}}->{baseqty} < $part->{baseqty})) { + $delivered = 0; + last; + } + } + + if ($delivered) { + $query = qq|UPDATE oe + SET delivered = TRUE + WHERE id = ?|; + do_query($form, $dbh, $query, $oe_id); + $dbh->commit() if (!$params{dbh}); + } + + $main::lxdebug->leave_sub(); +} + +sub close_orders { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(ids)); + + if (('ARRAY' ne ref $params{ids}) || !scalar @{ $params{ids} }) { + $main::lxdebug->leave_sub(); + return; + } + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my $query = qq|UPDATE delivery_orders SET closed = TRUE WHERE id IN (| . join(', ', ('?') x scalar(@{ $params{ids} })) . qq|)|; + + do_query($form, $dbh, $query, map { conv_i($_) } @{ $params{ids} }); + + $dbh->commit() unless ($params{dbh}); $main::lxdebug->leave_sub(); } @@ -485,6 +593,7 @@ sub retrieve { } map { $form->{$_} = $ref->{$_} } keys %$ref if ($ref); + $form->{donumber_array} .= $form->{donumber} . ' '; } $sth->finish(); @@ -664,16 +773,16 @@ sub order_details { my $price_factor = $price_factors{$form->{"price_factor_id_$i"}} || { 'factor' => 1 }; - push @{ $form->{runningnumber} }, $position; - push @{ $form->{number} }, $form->{"partnumber_$i"}; - push @{ $form->{description} }, $form->{"description_$i"}; - push @{ $form->{longdescription} }, $form->{"longdescription_$i"}; - push @{ $form->{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"}); - push @{ $form->{unit} }, $form->{"unit_$i"}; - push @{ $form->{partnotes} }, $form->{"partnotes_$i"}; - push @{ $form->{serialnumber} }, $form->{"serialnumber_$i"}; - push @{ $form->{reqdate} }, $form->{"reqdate_$i"}; - push @{ $form->{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}}; + push @{ $form->{TEMPLATE_ARRAYS}{runningnumber} }, $position; + push @{ $form->{TEMPLATE_ARRAYS}{number} }, $form->{"partnumber_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{description} }, $form->{"description_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{longdescription} }, $form->{"longdescription_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{qty} }, $form->format_amount($myconfig, $form->{"qty_$i"}); + push @{ $form->{TEMPLATE_ARRAYS}{unit} }, $form->{"unit_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{partnotes} }, $form->{"partnotes_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{serialnumber} }, $form->{"serialnumber_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{reqdate} }, $form->{"reqdate_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{projectnumber} }, $projectnumbers{$form->{"project_id_$i"}}; if ($form->{"assembly_$i"}) { $sameitem = ""; @@ -711,14 +820,14 @@ sub order_details { do_statement($form, $h_bin_wh, $q_bin_wh, conv_i($si->{bin_id}), conv_i($si->{warehouse_id})); my $bin_wh = $h_bin_wh->fetchrow_hashref(); - push @{ $form->{si_runningnumber} }, $num_si; - push @{ $form->{si_number} }, $form->{"partnumber_$i"}; - push @{ $form->{si_description} }, $form->{"description_$i"}; - push @{ $form->{si_warehouse} }, $bin_wh->{warehouse}; - push @{ $form->{si_bin} }, $bin_wh->{bin}; - push @{ $form->{si_chargenumber} }, $si->{chargenumber}; - push @{ $form->{si_qty} }, $form->format_amount($myconfig, $si->{qty} * 1); - push @{ $form->{si_unit} }, $si->{unit}; + push @{ $form->{TEMPLATE_ARRAYS}{si_runningnumber}[$position-1] }, $num_si; + push @{ $form->{TEMPLATE_ARRAYS}{si_number}[$position-1] }, $form->{"partnumber_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{si_description}[$position-1] }, $form->{"description_$i"}; + push @{ $form->{TEMPLATE_ARRAYS}{si_warehouse}[$position-1] }, $bin_wh->{warehouse}; + push @{ $form->{TEMPLATE_ARRAYS}{si_bin}[$position-1] }, $bin_wh->{bin}; + push @{ $form->{TEMPLATE_ARRAYS}{si_chargenumber}[$position-1] }, $si->{chargenumber}; + push @{ $form->{TEMPLATE_ARRAYS}{si_qty}[$position-1] }, $form->format_amount($myconfig, $si->{qty} * 1); + push @{ $form->{TEMPLATE_ARRAYS}{si_unit}[$position-1] }, $si->{unit}; } } } @@ -788,10 +897,10 @@ sub get_item_availability { LEFT JOIN warehouse w ON (i.warehouse_id = w.id) LEFT JOIN bin b ON (i.bin_id = b.id) WHERE (i.parts_id IN (| . join(', ', ('?') x scalar(@parts_ids)) . qq|)) - AND qty > 0 GROUP BY i.warehouse_id, i.bin_id, i.chargenumber, i.parts_id, w.description, b.description - ORDER BY LOWER(w.description), LOWER(b.description), LOWER(i.chargenumber)|; - + HAVING SUM(qty) > 0 + ORDER BY LOWER(w.description), LOWER(b.description), LOWER(i.chargenumber) +|; my $contents = selectall_hashref_query($form, $form->get_standard_dbh($myconfig), $query, @parts_ids); $main::lxdebug->leave_sub(); @@ -813,7 +922,7 @@ sub check_stock_availability { my $dbh = $form->get_standard_dbh($myconfig); - my $units = AM->retrieve_units($myconfig, $form, "dimension"); + my $units = AM->retrieve_units($myconfig, $form); my ($partunit) = selectrow_query($form, $dbh, qq|SELECT unit FROM parts WHERE id = ?|, conv_i($params{parts_id})); my $unit_factor = $units->{$partunit}->{factor} || 1; @@ -889,4 +998,75 @@ sub transfer_in_out { $main::lxdebug->leave_sub(); } +sub get_shipped_qty { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(type oe_id)); + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my @links = RecordLinks->get_links('dbh' => $dbh, + 'from_table' => 'oe', + 'from_id' => $params{oe_id}, + 'to_table' => 'delivery_orders'); + my @values = map { $_->{to_id} } @links; + + if (!scalar @values) { + $main::lxdebug->leave_sub(); + return (); + } + + my $query = + qq|SELECT doi.parts_id, doi.qty, doi.unit, p.unit AS partunit + FROM delivery_order_items doi + LEFT JOIN delivery_orders o ON (doi.delivery_order_id = o.id) + LEFT JOIN parts p ON (doi.parts_id = p.id) + WHERE o.id IN (| . join(', ', ('?') x scalar @values) . qq|)|; + + my %ship = (); + my $entries = selectall_hashref_query($form, $dbh, $query, @values); + my $all_units = AM->retrieve_all_units(); + + foreach my $entry (@{ $entries }) { + $entry->{qty} *= $all_units->{$entry->{unit}}->{factor} / $all_units->{$entry->{partunit}}->{factor}; + + if (!$ship{$entry->{parts_id}}) { + $ship{$entry->{parts_id}} = $entry; + } else { + $ship{$entry->{parts_id}}->{qty} += $entry->{qty}; + } + } + + $main::lxdebug->leave_sub(); + + return %ship; +} + +sub is_marked_as_delivered { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(id)); + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + + my ($delivered) = selectfirst_array_query($form, $dbh, qq|SELECT delivered FROM delivery_orders WHERE id = ?|, conv_i($params{id})); + + $main::lxdebug->leave_sub(); + + return $delivered ? 1 : 0; +} + + 1;