use SL::AM;
use SL::Common;
use SL::DBUtils;
+use SL::RecordLinks;
sub transactions {
$main::lxdebug->enter_sub();
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)|;
"transaction_description" => "dord.transaction_description"
);
- my $sortoder = "dord.id";
+ my $sortdir = $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();
}
# 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};
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);
+
+ $main::lxdebug->message(0, "oe_id $oe_id");
+
+ return $main::lxdebug->leave_sub() if (!$oe_id);
+
+ my $all_units = AM->retrieve_all_units();
+
+ $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 = ();
+
+ do_statement($form, $sth, $query, $oe_id);
+
+ 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();
}
$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;
projects / kivitendo-erp.git / blobdiff