$form->{amount}{IC_expense} = $form->{expense_accno};
$form->{amount}{IC_cogs} = $form->{expense_accno};
- my @pricegroups = ();
- my @pricegroups_not_used = ();
-
# get prices
- $query =
- qq|SELECT p.parts_id, p.pricegroup_id, p.price,
- (SELECT pg.pricegroup
- FROM pricegroup pg
- WHERE pg.id = p.pricegroup_id) AS pricegroup
- FROM prices p
- WHERE (parts_id = ?)
- ORDER BY pricegroup|;
- $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
-
- #for pricegroups
- my $i = 1;
- while (($form->{"klass_$i"}, $form->{"pricegroup_id_$i"},
- $form->{"price_$i"}, $form->{"pricegroup_$i"})
- = $sth->fetchrow_array()) {
- push @pricegroups, $form->{"pricegroup_id_$i"};
- $i++;
- }
-
- $sth->finish;
+ $query = <<SQL;
+ SELECT pg.pricegroup, pg.id AS pricegroup_id, COALESCE(pr.price, 0) AS price
+ FROM pricegroup pg
+ LEFT JOIN prices pr ON (pr.pricegroup_id = pg.id) AND (pr.parts_id = ?)
+ ORDER BY lower(pg.pricegroup)
+SQL
- # get pricegroups
- $query = qq|SELECT id, pricegroup FROM pricegroup|;
- $form->{PRICEGROUPS} = selectall_hashref_query($form, $dbh, $query);
-
- #find not used pricegroups
- while (my $tmp = pop(@{ $form->{PRICEGROUPS} })) {
- my $in_use = 0;
- foreach my $item (@pricegroups) {
- if ($item eq $tmp->{id}) {
- $in_use = 1;
- last;
- }
- }
- push(@pricegroups_not_used, $tmp) unless ($in_use);
+ my $row = 1;
+ foreach $ref (selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}))) {
+ $form->{"${_}_${row}"} = $ref->{$_} for qw(pricegroup_id pricegroup price);
+ $row++;
}
-
- # if not used pricegroups are avaible
- if (@pricegroups_not_used) {
-
- foreach my $name (@pricegroups_not_used) {
- $form->{"klass_$i"} = "$name->{id}";
- $form->{"pricegroup_id_$i"} = "$name->{id}";
- $form->{"pricegroup_$i"} = "$name->{pricegroup}";
+ $form->{price_rows} = $row - 1;
+
+ # get makes
+ if ($form->{makemodel}) {
+ #hli
+ $query = qq|SELECT m.make, m.model,m.lastcost,m.lastcost,m.lastupdate,m.sortorder FROM makemodel m | .
+ qq|WHERE m.parts_id = ? order by m.sortorder asc|;
+ my @values = ($form->{id});
+ $sth = $dbh->prepare($query);
+ $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")");
+
+ my $i = 1;
+
+ while (($form->{"make_$i"}, $form->{"model_$i"}, $form->{"old_lastcost_$i"},
+ $form->{"lastcost_$i"}, $form->{"lastupdate_$i"}, $form->{"sortorder_$i"}) = $sth->fetchrow_array)
+ {
$i++;
}
- }
-
- #correct rows
- $form->{price_rows} = $i - 1;
-
- unless ($form->{item} eq 'service') {
-
- # get makes
- if ($form->{makemodel}) {
- #hli
- $query = qq|SELECT m.make, m.model,m.lastcost,m.lastcost,m.lastupdate,m.sortorder FROM makemodel m | .
- qq|WHERE m.parts_id = ? order by m.sortorder asc|;
- my @values = ($form->{id});
- $sth = $dbh->prepare($query);
- $sth->execute(@values) || $form->dberror("$query (" . join(', ', @values) . ")");
-
- my $i = 1;
-
- while (($form->{"make_$i"}, $form->{"model_$i"}, $form->{"old_lastcost_$i"},
- $form->{"lastcost_$i"}, $form->{"lastupdate_$i"}, $form->{"sortorder_$i"}) = $sth->fetchrow_array)
- {
- $i++;
- }
- $sth->finish;
- $form->{makemodel_rows} = $i - 1;
+ $sth->finish;
+ $form->{makemodel_rows} = $i - 1;
- }
}
# get translations
}
$trq->finish;
- # now get accno for taxes
- $query =
- qq|SELECT c.accno
- FROM chart c, partstax pt
- WHERE (pt.chart_id = c.id) AND (pt.parts_id = ?)|;
- $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id}));
- while (my ($key) = $sth->fetchrow_array) {
- $form->{amount}{$key} = $key;
- }
-
- $sth->finish;
-
# is it an orphan
my @referencing_tables = qw(invoice orderitems inventory rmaitems);
my %column_map = ( );
my $dbh = $form->dbconnect($myconfig);
# get pricegroups
- my $query = qq|SELECT id, pricegroup FROM pricegroup|;
+ my $query = qq|SELECT id, pricegroup FROM pricegroup ORDER BY lower(pricegroup)|;
my $pricegroups = selectall_hashref_query($form, $dbh, $query);
my $i = 1;
}
$sth->finish;
- if ($form->{item} ne 'service') {
- # delete makemodel records
- do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id}));
- }
+ # delete makemodel records
+ do_query($form, $dbh, qq|DELETE FROM makemodel WHERE parts_id = ?|, conv_i($form->{id}));
if ($form->{item} eq 'assembly') {
# delete assembly records
do_query($form, $dbh, qq|DELETE FROM assembly WHERE id = ?|, conv_i($form->{id}));
}
- # delete tax records
- do_query($form, $dbh, qq|DELETE FROM partstax WHERE parts_id = ?|, conv_i($form->{id}));
-
# delete translations
do_query($form, $dbh, qq|DELETE FROM translation WHERE parts_id = ?|, conv_i($form->{id}));
$form->{orphaned} = 1;
}
- my $partsgroup_id = 0;
+ my $partsgroup_id = undef;
if ($form->{partsgroup}) {
(my $partsgroup, $partsgroup_id) = split(/--/, $form->{partsgroup});
# delete price records
do_query($form, $dbh, qq|DELETE FROM prices WHERE parts_id = ?|, conv_i($form->{id}));
+ $query = qq|INSERT INTO prices (parts_id, pricegroup_id, price) VALUES(?, ?, ?)|;
+ $sth = prepare_query($form, $dbh, $query);
+
# insert price records only if different to sellprice
for my $i (1 .. $form->{price_rows}) {
my $price = $form->parse_amount($myconfig, $form->{"price_$i"});
- if ($price == 0) {
- $form->{"price_$i"} = $form->{sellprice};
- }
- if (
- ( $price
- || $form->{"klass_$i"}
- || $form->{"pricegroup_id_$i"})
- and $price != $form->{sellprice}
- ) {
- #$klass = $form->parse_amount($myconfig, $form->{"klass_$i"});
- $query = qq|INSERT INTO prices (parts_id, pricegroup_id, price) | .
- qq|VALUES(?, ?, ?)|;
- @values = (conv_i($form->{id}), conv_i($form->{"pricegroup_id_$i"}), $price);
- do_query($form, $dbh, $query, @values);
- }
+ next unless $price && ($price != $form->{sellprice});
+
+ @values = (conv_i($form->{id}), conv_i($form->{"pricegroup_id_$i"}), $price);
+ do_statement($form, $sth, $query, @values);
}
+ $sth->finish;
+
# insert makemodel records
- unless ($form->{item} eq 'service') {
my $lastupdate = '';
my $value = 0;
for my $i (1 .. $form->{makemodel_rows}) {
do_query($form, $dbh, $query, @values);
}
}
- }
-
- # insert taxes
- foreach my $item (split(/ /, $form->{taxaccounts})) {
- if ($form->{"IC_tax_$item"}) {
- $query =
- qq|INSERT INTO partstax (parts_id, chart_id)
- VALUES (?, (SELECT id FROM chart WHERE accno = ?))|;
- @values = (conv_i($form->{id}), $item);
- do_query($form, $dbh, $query, @values);
- }
- }
# add assembly records
if ($form->{item} eq 'assembly') {
my %columns = ( "assembly" => "id", "parts" => "id" );
- for my $table (qw(prices partstax makemodel inventory assembly translation parts)) {
+ for my $table (qw(prices makemodel inventory assembly translation parts)) {
my $column = defined($columns{$table}) ? $columns{$table} : "parts_id";
do_query($form, $dbh, qq|DELETE FROM $table WHERE $column = ?|, @values);
}
$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 @project_filters = qw(projectnumber projectdescription);
my @makemodel_filters = qw(make model);
my @invoice_oi_filters = qw(serialnumber soldtotal);
my @apoe_filters = qw(transdate);
my @like_filters = (@simple_filters, @invoice_oi_filters);
- my @all_columns = (@simple_filters, @makemodel_filters, @apoe_filters, qw(serialnumber));
- my @simple_l_switches = (@all_columns, qw(listprice sellprice lastcost priceupdate weight unit bin rop image));
+ my @all_columns = (@simple_filters, @makemodel_filters, @apoe_filters, @project_filters, qw(serialnumber));
+ my @simple_l_switches = (@all_columns, qw(notes listprice sellprice lastcost priceupdate weight unit bin rop image));
my @oe_flags = qw(bought sold onorder ordered rfq quoted);
my @qsooqr_flags = qw(invnumber ordnumber quonumber trans_id name module qty);
my @deliverydate_flags = qw(deliverydate);
pfac => 'LEFT JOIN price_factors pfac ON (pfac.id = p.price_factor_id)',
invoice_oi =>
q|LEFT JOIN (
- SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, deliverydate, 'invoice' AS ioi, id FROM invoice UNION
- SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi, id FROM orderitems
+ SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, assemblyitem, deliverydate, 'invoice' AS ioi, project_id, id FROM invoice UNION
+ SELECT parts_id, description, serialnumber, trans_id, unit, sellprice, qty, FALSE AS assemblyitem, NULL AS deliverydate, 'orderitems' AS ioi, project_id, id FROM orderitems
) 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, '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, reqdate AS deliverydate, 'orderitems' AS ioi FROM oe
+ SELECT id, transdate, 'ir' AS module, ordnumber, quonumber, invnumber, FALSE AS quotation, NULL AS customer_id, vendor_id, NULL AS deliverydate, globalproject_id, '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, globalproject_id, 'invoice' AS ioi FROM ar UNION
+ SELECT id, transdate, 'oe' AS module, ordnumber, quonumber, NULL AS invnumber, quotation, customer_id, vendor_id, reqdate AS deliverydate, globalproject_id, '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, 'vendor' AS cv FROM vendor
) AS cv ON cv.id = apoe.customer_id OR cv.id = apoe.vendor_id|,
mv => 'LEFT JOIN vendor AS mv ON mv.id = mm.make',
+ project => 'LEFT JOIN project AS pj ON pj.id = COALESCE(ioi.project_id, apoe.globalproject_id)',
);
- my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac);
+ my @join_order = qw(partsgroup makemodel mv invoice_oi apoe cv pfac project);
my %table_prefix = (
deliverydate => 'apoe.', serialnumber => 'ioi.',
quonumber => 'apoe.', model => 'mm.',
invnumber => 'apoe.', partsgroup => 'pg.',
lastcost => 'p.', , soldtotal => ' ',
- factor => 'pfac.',
- 'SUM(ioi.qty)' => ' ',
+ factor => 'pfac.', projectnumber => 'pj.',
+ 'SUM(ioi.qty)' => ' ', projectdescription => 'pj.',
description => 'p.',
qty => 'ioi.',
serialnumber => 'ioi.',
# of the scecified table will gently override (coalesce actually) the original value
# use it to conditionally coalesce values from subtables
my @column_override = (
- # column name, prefix, joins_needed
+ # column name, prefix, joins_needed, nick name (in case column is named like another)
[ 'description', 'ioi.', 'invoice_oi' ],
[ 'deliverydate', 'ioi.', 'invoice_oi' ],
[ 'transdate', 'apoe.', 'apoe' ],
'SUM(ioi.qty)' => 'soldtotal',
'ioi.id' => 'ioi_id',
'ioi.ioi' => 'ioi',
+ 'projectdescription' => 'projectdescription',
+ );
+
+ my %real_column = (
+ projectdescription => 'description',
);
if (($form->{searchitems} eq 'assembly') && $form->{l_lastcost}) {
my $make_token_builder = sub {
my $joins_needed = shift;
sub {
- my ($col, $alias) = @_;
+ my ($nick, $alias) = @_;
+ my ($col) = $real_column{$nick} || $nick;
my @coalesce_tokens =
map { ($_->[1] || 'p.') . $_->[0] }
grep { !$_->[2] || $joins_needed->{$_->[2]} }
- grep { $_->[0] eq $col }
- @column_override, [ $col, $table_prefix{$col} ];
+ grep { ($_->[3] || $_->[0]) eq $nick }
+ @column_override, [ $col, $table_prefix{$nick}, undef , $nick ];
my $coalesce = scalar @coalesce_tokens > 1;
return ($coalesce
? sprintf 'COALESCE(%s)', join ', ', @coalesce_tokens
: shift @coalesce_tokens)
- . ($alias && ($coalesce || $renamed_columns{$col})
- ? " AS " . ($renamed_columns{$col} || $col)
+ . ($alias && ($coalesce || $renamed_columns{$nick})
+ ? " AS " . ($renamed_columns{$nick} || $nick)
: '');
}
};
$joins_needed{partsgroup} = 1;
$joins_needed{pfac} = 1;
+ $joins_needed{project} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @project_filters;
$joins_needed{makemodel} = 1 if grep { $form->{$_} || $form->{"l_$_"} } @makemodel_filters;
$joins_needed{mv} = 1 if $joins_needed{makemodel};
$joins_needed{cv} = 1 if $bsooqr;
- $joins_needed{apoe} = 1 if $joins_needed{cv} || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
- $joins_needed{invoice_oi} = 1 if $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
-
- # in bsoorq, use qtys instead of onhand
- if ($joins_needed{invoice_oi}) {
- $renamed_columns{onhand} = 'onhand_before_bsooqr';
- $renamed_columns{qty} = 'onhand';
- }
+ $joins_needed{apoe} = 1 if $joins_needed{project} || $joins_needed{cv} || grep { $form->{$_} || $form->{"l_$_"} } @apoe_filters;
+ $joins_needed{invoice_oi} = 1 if $joins_needed{project} || $joins_needed{apoe} || grep { $form->{$_} || $form->{"l_$_"} } @invoice_oi_filters;
# special case for description search.
# up in the simple filter section the description filter got interpreted as something like: WHERE description ILIKE '%$form->{description}%'
map { $_->{onhand} *= 1 } @{ $form->{parts} };
+ # fix qty sign in ap. those are saved negative
+ if ($bsooqr && $form->{bought}) {
+ for my $row (@{ $form->{parts} }) {
+ $row->{qty} *= -1 if $row->{module} eq 'ir';
+ }
+ }
+
# post processing for assembly parts lists (bom)
# for each part get the assembly parts and add them into the partlist.
my @assemblies;
if ($form->{searchitems} eq 'assembly' && $form->{bom}) {
$query =
qq|SELECT p.id, p.partnumber, p.description, a.qty AS onhand,
- p.unit, p.bin,
+ p.unit, p.bin, p.notes,
p.sellprice, p.listprice, p.lastcost,
p.rop, p.weight, p.priceupdate,
p.image, p.drawing, p.microfiche,
$form->{parts} = \@assemblies;
}
+ if ($form->{l_pricegroups} ) {
+ my $query = <<SQL;
+ SELECT parts_id, price, pricegroup_id
+ FROM prices
+ WHERE parts_id = ?
+SQL
+
+ my $sth = prepare_query($form, $dbh, $query);
+
+ foreach my $part (@{ $form->{parts} }) {
+ do_statement($form, $sth, $query, conv_i($part->{id}));
+
+ while (my $ref = $sth->fetchrow_hashref("NAME_lc")) {
+ $part->{"pricegroup_$ref->{pricegroup_id}"} = $ref->{price};
+ }
+ $sth->finish;
+ }
+ };
+
+
$main::lxdebug->leave_sub();
return wantarray ? @{ $form->{parts} } : $form->{parts};
} else {
$transdate = $form->{deliverydate};
}
+ } elsif (($form->{type} eq "credit_note") and $form->{deliverydate}) {
+ # if credit_note has a deliverydate, use this instead of invdate
+ # useful for credit_notes of invoices from an old period with different tax
+ # if there is no deliverydate then invdate is used, old default (see next elsif)
+ $transdate = $form->{deliverydate};
} elsif (($form->{type} eq "credit_note") || ($form->{script} eq 'ir.pl')) {
$transdate = $form->{invdate};
} else {
projects / kivitendo-erp.git / blobdiff