- $query = qq|SELECT p.id, p.partnumber, p.description, p.onhand, p.unit,
- p.bin, p.sellprice, p.listprice, p.lastcost, p.rop, p.weight,
- p.priceupdate, p.image, p.drawing, p.microfiche,
- pg.partsgroup
- FROM parts p
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE $where
- $group
- ORDER BY $sortorder|;
- }
-
- # rebuild query for bought and sold items
- if ( $form->{bought}
- || $form->{sold}
- || $form->{onorder}
- || $form->{ordered}
- || $form->{rfq}
- || $form->{quoted}) {
-
- my @a = qw(partnumber description bin priceupdate name);
-
- push @a, qw(invnumber serialnumber) if ($form->{bought} || $form->{sold});
- push @a, "ordnumber" if ($form->{onorder} || $form->{ordered});
- push @a, "quonumber" if ($form->{rfq} || $form->{quoted});
-
- my $union = "";
- $query = "";
-
- if ($form->{bought} || $form->{sold}) {
-
- my $invwhere = "$where";
- $invwhere .= " AND i.assemblyitem = '0'";
- $invwhere .= " AND a.transdate >= '$form->{transdatefrom}'"
- if $form->{transdatefrom};
- $invwhere .= " AND a.transdate <= '$form->{transdateto}'"
- if $form->{transdateto};
-
- if ($form->{description}) {
- $var = $form->like(lc $form->{description});
- $invwhere .= " AND lower(i.description) LIKE '$var'";
- }
-
- my $flds = qq|p.id, p.partnumber, i.description, i.serialnumber,
- i.qty AS onhand, i.unit, p.bin, i.sellprice,
- p.listprice, p.lastcost, p.rop, p.weight,
- p.priceupdate, p.image, p.drawing, p.microfiche,
- pg.partsgroup,
- a.invnumber, a.ordnumber, a.quonumber, i.trans_id,
- ct.name, i.deliverydate|;
-
- if ($form->{bought}) {
- $query = qq|
- SELECT $flds, 'ir' AS module, '' AS type,
- 1 AS exchangerate
- FROM invoice i
- JOIN parts p ON (p.id = i.parts_id)
- JOIN ap a ON (a.id = i.trans_id)
- JOIN vendor ct ON (a.vendor_id = ct.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE $invwhere|;
- $union = "
- UNION";
- }
-
- if ($form->{sold}) {
- $query .= qq|$union
- SELECT $flds, 'is' AS module, '' AS type,
- 1 As exchangerate
- FROM invoice i
- JOIN parts p ON (p.id = i.parts_id)
- JOIN ar a ON (a.id = i.trans_id)
- JOIN customer ct ON (a.customer_id = ct.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE $invwhere|;
- $union = "
- UNION";
- }
- }
-
- if ($form->{onorder} || $form->{ordered}) {
- my $ordwhere = "$where
- AND o.quotation = '0'";
- $ordwhere .= " AND o.transdate >= '$form->{transdatefrom}'"
- if $form->{transdatefrom};
- $ordwhere .= " AND o.transdate <= '$form->{transdateto}'"
- if $form->{transdateto};
-
- if ($form->{description}) {
- $var = $form->like(lc $form->{description});
- $ordwhere .= " AND lower(oi.description) LIKE '$var'";
- }
-
- $flds =
- qq|p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
- oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
- p.listprice, p.lastcost, p.rop, p.weight,
- p.priceupdate, p.image, p.drawing, p.microfiche,
- pg.partsgroup,
- '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
- ct.name|;
-
- if ($form->{ordered}) {
- $query .= qq|$union
- SELECT $flds, 'oe' AS module, 'sales_order' AS type,
- (SELECT buy FROM exchangerate ex
- WHERE ex.curr = o.curr
- AND ex.transdate = o.transdate) AS exchangerate
- FROM orderitems oi
- JOIN parts p ON (oi.parts_id = p.id)
- JOIN oe o ON (oi.trans_id = o.id)
- JOIN customer ct ON (o.customer_id = ct.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE $ordwhere
- AND o.customer_id > 0|;
- $union = "
- UNION";
- }
-
- if ($form->{onorder}) {
- $flds =
- qq|p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
- oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
- p.listprice, p.lastcost, p.rop, p.weight,
- p.priceupdate, p.image, p.drawing, p.microfiche,
- pg.partsgroup,
- '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
- ct.name|;
-
- $query .= qq|$union
- SELECT $flds, 'oe' AS module, 'purchase_order' AS type,
- (SELECT sell FROM exchangerate ex
- WHERE ex.curr = o.curr
- AND ex.transdate = o.transdate) AS exchangerate
- FROM orderitems oi
- JOIN parts p ON (oi.parts_id = p.id)
- JOIN oe o ON (oi.trans_id = o.id)
- JOIN vendor ct ON (o.vendor_id = ct.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE $ordwhere
- AND o.vendor_id > 0|;
- }
-
- }
-
- if ($form->{rfq} || $form->{quoted}) {
- my $quowhere = "$where
- AND o.quotation = '1'";
- $quowhere .= " AND o.transdate >= '$form->{transdatefrom}'"
- if $form->{transdatefrom};
- $quowhere .= " AND o.transdate <= '$form->{transdateto}'"
- if $form->{transdateto};
-
- if ($form->{description}) {
- $var = $form->like(lc $form->{description});
- $quowhere .= " AND lower(oi.description) LIKE '$var'";
- }
-
- $flds =
- qq|p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
- oi.qty AS onhand, oi.unit, p.bin, oi.sellprice,
- p.listprice, p.lastcost, p.rop, p.weight,
- p.priceupdate, p.image, p.drawing, p.microfiche,
- pg.partsgroup,
- '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
- ct.name|;
-
- if ($form->{quoted}) {
- $query .= qq|$union
- SELECT $flds, 'oe' AS module, 'sales_quotation' AS type,
- (SELECT buy FROM exchangerate ex
- WHERE ex.curr = o.curr
- AND ex.transdate = o.transdate) AS exchangerate
- FROM orderitems oi
- JOIN parts p ON (oi.parts_id = p.id)
- JOIN oe o ON (oi.trans_id = o.id)
- JOIN customer ct ON (o.customer_id = ct.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE $quowhere
- AND o.customer_id > 0|;
- $union = "
- UNION";
- }
-
- if ($form->{rfq}) {
- $flds =
- qq|p.id, p.partnumber, oi.description, oi.serialnumber AS serialnumber,
- oi.qty * -1 AS onhand, oi.unit, p.bin, oi.sellprice,
- p.listprice, p.lastcost, p.rop, p.weight,
- p.priceupdate, p.image, p.drawing, p.microfiche,
- pg.partsgroup,
- '' AS invnumber, o.ordnumber, o.quonumber, oi.trans_id,
- ct.name|;
-
- $query .= qq|$union
- SELECT $flds, 'oe' AS module, 'request_quotation' AS type,
- (SELECT sell FROM exchangerate ex
- WHERE ex.curr = o.curr
- AND ex.transdate = o.transdate) AS exchangerate
- FROM orderitems oi
- JOIN parts p ON (oi.parts_id = p.id)
- JOIN oe o ON (oi.trans_id = o.id)
- JOIN vendor ct ON (o.vendor_id = ct.id)
- LEFT JOIN partsgroup pg ON (p.partsgroup_id = pg.id)
- WHERE $quowhere
- AND o.vendor_id > 0|;
- }
-
- }
- $query .= qq|
- ORDER BY $sortorder|;
-
- }
- my $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- push @{ $form->{parts} }, $ref;
- }
-
- $sth->finish;
-
- # include individual items for assemblies
- if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
- foreach $item (@{ $form->{parts} }) {
- push @assemblies, $item;
- $query = qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
- p.unit, p.bin,
- p.sellprice, p.listprice, p.lastcost,
- p.rop, p.weight, p.priceupdate,
- p.image, p.drawing, p.microfiche
- FROM parts p, assembly a
- WHERE p.id = a.parts_id
- AND a.id = $item->{id}|;
-
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror($query);
-
- while (my $ref = $sth->fetchrow_hashref(NAME_lc)) {
- $ref->{assemblyitem} = 1;
- push @assemblies, $ref;
- }
- $sth->finish;
-
- push @assemblies, { id => $item->{id} };
-
- }
-
- # copy assemblies to $form->{parts}
- @{ $form->{parts} } = @assemblies;
- }
-
- $dbh->disconnect;
- $main::lxdebug->leave_sub();
+ $transdate = $dbh->quote($transdate);
+ }
+ #/transdate
+ my $inc_exp = $form->{"vc"} eq "customer" ? "income_accno_id" : "expense_accno_id";
+
+ my @part_ids = grep { $_ } values %args;
+ my $in = join ',', ('?') x @part_ids;
+
+ my %accno_by_part = map { $_->{id} => $_ }
+ selectall_hashref_query($form, $dbh, <<SQL, @part_ids);
+ SELECT
+ p.id, p.part_type,
+ bg.inventory_accno_id,
+ tc.income_accno_id AS income_accno_id,
+ tc.expense_accno_id AS expense_accno_id,
+ c1.accno AS inventory_accno,
+ c2.accno AS income_accno,
+ c3.accno AS expense_accno
+ FROM parts p
+ LEFT JOIN buchungsgruppen bg ON p.buchungsgruppen_id = bg.id
+ LEFT JOIN taxzone_charts tc on bg.id = tc.buchungsgruppen_id
+ LEFT JOIN chart c1 ON bg.inventory_accno_id = c1.id
+ LEFT JOIN chart c2 ON tc.income_accno_id = c2.id
+ LEFT JOIN chart c3 ON tc.expense_accno_id = c3.id
+ WHERE
+ tc.taxzone_id = '$form->{taxzone_id}'
+ and
+ p.id IN ($in)
+SQL
+
+ my $query_tax = <<SQL;
+ SELECT c.accno, t.taxdescription AS description, t.rate, t.taxnumber
+ FROM tax t
+ LEFT JOIN chart c ON c.id = t.chart_id
+ WHERE t.id IN
+ (SELECT tk.tax_id
+ FROM taxkeys tk
+ WHERE tk.chart_id = ? AND startdate <= ?
+ ORDER BY startdate DESC LIMIT 1)
+SQL
+ my $sth_tax = prepare_query($::form, $dbh, $query_tax);
+
+ while (my ($index => $part_id) = each %args) {
+ my $ref = $accno_by_part{$part_id} or next;
+
+ $ref->{"inventory_accno_id"} = undef unless $ref->{"part_type"} eq 'part';
+
+ my %accounts;
+ for my $type (qw(inventory income expense)) {
+ next unless $ref->{"${type}_accno_id"};
+ ($accounts{"${type}_accno_id"}, $accounts{"${type}_accno"}) =
+ $self->follow_account_chain($form, $dbh, $transdate, $ref->{"${type}_accno_id"}, $ref->{"${type}_accno"});
+ }
+
+ $form->{"${_}_accno_$index"} = $accounts{"${_}_accno"} for qw(inventory income expense);
+
+ $sth_tax->execute($accounts{$inc_exp}, quote_db_date($transdate)) || $::form->dberror($query_tax);
+ $ref = $sth_tax->fetchrow_hashref or next;
+
+ $form->{"taxaccounts_$index"} = $ref->{"accno"};
+ $form->{"taxaccounts"} .= "$ref->{accno} "if $form->{"taxaccounts"} !~ /$ref->{accno}/;
+
+ $form->{"$ref->{accno}_${_}"} = $ref->{$_} for qw(rate description taxnumber);
+ }
+
+ $sth_tax->finish;
+
+ $::lxdebug->leave_sub;