# retrieve assembly items
$query =
qq|SELECT p.id, p.partnumber, p.description,
- p.sellprice, p.weight, a.qty, a.bom, p.unit,
+ p.sellprice, p.lastcost, p.weight, a.qty, a.bom, p.unit,
pg.partsgroup
FROM parts p
JOIN assembly a ON (a.parts_id = p.id)
# get translations
$form->{language_values} = "";
- $query = qq|SELECT language_id, translation FROM translation WHERE parts_id = ?|;
+ $query = qq|SELECT language_id, translation, longdescription
+ FROM translation
+ WHERE parts_id = ?|;
my $trq = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
- while ($tr = $trq->fetchrow_hashref(NAME_lc)) {
- $form->{language_values} .= "---+++---".$tr->{language_id}."--++--".$tr->{translation};
+ while (my $tr = $trq->fetchrow_hashref(NAME_lc)) {
+ $form->{language_values} .= "---+++---" . join('--++--', @{$tr}{qw(language_id translation longdescription)});
}
$trq->finish;
$sth->finish;
# is it an orphan
- my @referencing_tables = qw(invoice orderitems invoice inventory rmaitems);
+ my @referencing_tables = qw(invoice orderitems inventory rmaitems);
my %column_map = ( );
my $parts_id = conv_i($form->{id});
# undo amount formatting
map { $form->{$_} = $form->parse_amount($myconfig, $form->{$_}) }
- qw(rop weight listprice sellprice gv lastcost stock);
+ qw(rop weight listprice sellprice gv lastcost);
my $makemodel = (($form->{make_1}) || ($form->{model_1})) ? 1 : 0;
unless ($form->{item} eq 'service') {
for my $i (1 .. $form->{makemodel_rows}) {
if (($form->{"make_$i"}) || ($form->{"model_$i"})) {
- map { $form->{"${_}_$i"} =~ s/\'/\'\'/g } qw(make model);
$query = qq|INSERT INTO makemodel (parts_id, make, model) | .
qq|VALUES (?, ?, ?)|;
- @values = (conv_i($form->{id}), $form->{"make_$i"}, $form->{"model_$i"});
+ @values = (conv_i($form->{id}), conv_i($form->{"make_$i"}), $form->{"model_$i"});
+
do_query($form, $dbh, $query, @values);
}
}
# short - NOT IMPLEMENTED as form filter, only as itemstatus option
# l_serialnumber - belonges to serialnumber filter
# l_deliverydate - displays deliverydate is sold etc. flags are active
+# l_soldtotal - aggreg join to display total of sold quantity, works as long as there's no bullshit in soldtotal
#
# not working:
-# l_soldtotal - aggreg join to display total of sold quantity
# onhand - as above, but masking the simple itemstatus results (doh!)
# masking of onhand in bsooqr mode - ToDO: fixme
#
my ($self, $myconfig, $form) = @_;
my $dbh = $form->get_standard_dbh($myconfig);
- $form->{parts} = +{ };
+ $form->{parts} = +{ };
+ $form->{soldtotal} = undef if $form->{l_soldtotal}; # security fix. top100 insists on putting strings in there...
my @simple_filters = qw(partnumber ean description partsgroup microfiche drawing onhand);
my @makemodel_filters = qw(make model);
) AS ioi ON ioi.parts_id = p.id|,
apoe =>
q|LEFT JOIN (
- SELECT id, transdate, 'ir' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, NULL AS customer_id, vendor_id, NULL AS deliverydate FROM ap UNION
- SELECT id, transdate, 'is' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, customer_id, NULL AS vendor_id, deliverydate FROM ar UNION
- SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, quotation, customer_id, vendor_id, NULL AS deliverydate FROM oe
- ) AS apoe ON ioi.trans_id = apoe.id|,
+ SELECT id, transdate, 'ir' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, NULL AS customer_id, vendor_id, NULL AS deliverydate, 'invoice' AS ioi FROM ap UNION
+ SELECT id, transdate, 'is' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, customer_id, NULL AS vendor_id, deliverydate, 'invoice' AS ioi FROM ar UNION
+ SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, quotation, customer_id, vendor_id, NULL AS deliverydate, 'orderitems' AS ioi FROM oe
+ ) AS apoe ON ((ioi.trans_id = apoe.id) AND (ioi.ioi = apoe.ioi))|,
cv =>
q|LEFT JOIN (
SELECT id, name, 'customer' AS cv FROM customer UNION
$form->{parts} = \@assemblies;
}
- $dbh->disconnect;
$main::lxdebug->leave_sub();
}
my $info = selectall_hashref_query($form, $dbh, $query, map { conv_i($_) } @ids);
- if ($params{vendor_id}) {
- $query = qq|SELECT * FROM parts_vendor WHERE (parts_id = ?) AND (vendor_id = ?)|;
- my $sth = prepare_query($form, $dbh, $query);
-
- foreach my $part (@{ $info }) {
- do_statement($form, $sth, $query, $part->{id}, conv_i($params{vendor_id}));
- $part->{vendor_info} = $sth->fetchrow_hashref();
- }
-
- $sth->finish();
- }
-
if ('' eq ref $params{id}) {
$info = $info->[0] || { };
return %info_map;
}
+sub prepare_parts_for_printing {
+ $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 $prefix = $params{prefix} || 'id_';
+ my $rowcount = defined $params{rowcount} ? $params{rowcount} : $form->{rowcount};
+
+ my @part_ids = keys %{ { map { $_ => 1 } grep { $_ } map { $form->{"${prefix}${_}"} } (1 .. $rowcount) } };
+
+ if (!@part_ids) {
+ $main::lxdebug->leave_sub();
+ return;
+ }
+
+ my $placeholders = join ', ', ('?') x scalar(@part_ids);
+ my $query = qq|SELECT mm.parts_id, mm.model, v.name AS make
+ FROM makemodel mm
+ LEFT JOIN vendor v ON (mm.make = cast (v.id as text))
+ WHERE mm.parts_id IN ($placeholders)|;
+
+ my %makemodel = ();
+
+ my $sth = prepare_execute_query($form, $dbh, $query, @part_ids);
+
+ while (my $ref = $sth->fetchrow_hashref()) {
+ $makemodel{$ref->{parts_id}} ||= [];
+ push @{ $makemodel{$ref->{parts_id}} }, $ref;
+ }
+
+ $sth->finish();
+
+ my @columns = qw(ean);
+
+ $query = qq|SELECT id, | . join(', ', @columns) . qq|
+ FROM parts
+ WHERE id IN ($placeholders)|;
+
+ my %data = selectall_as_map($form, $dbh, $query, 'id', \@columns, @part_ids);
+
+ map { $form->{$_} = [] } (qw(make model), @columns);
+
+ foreach my $i (1 .. $rowcount) {
+ my $id = $form->{"${prefix}${i}"};
+
+ next if (!$id);
+
+ foreach (@columns) {
+ push @{ $form->{$_} }, $data{$id}->{$_};
+ }
+
+ push @{ $form->{make} }, [];
+ push @{ $form->{model} }, [];
+
+ next if (!$makemodel{$id});
+
+ foreach my $ref (@{ $makemodel{$id} }) {
+ map { push @{ $form->{$_}->[-1] }, $ref->{$_} } qw(make model);
+ }
+ }
+
+ $main::lxdebug->leave_sub();
+}
+
1;
projects / kivitendo-erp.git / blobdiff