$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;
-
- # 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);
- }
-
- # if not used pricegroups are avaible
- if (@pricegroups_not_used) {
+ $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
- foreach my $name (@pricegroups_not_used) {
- $form->{"klass_$i"} = "$name->{id}";
- $form->{"pricegroup_id_$i"} = "$name->{id}";
- $form->{"pricegroup_$i"} = "$name->{pricegroup}";
- $i++;
- }
+ 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++;
}
-
- #correct rows
- $form->{price_rows} = $i - 1;
+ $form->{price_rows} = $row - 1;
# get makes
if ($form->{makemodel}) {
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;
# 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
my $lastupdate = '';
my $value = 0;
$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{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.
} 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 {