+sub price_matrix_query {
+ my ($dbh, $form) = @_;
+
+ my $query = qq|SELECT p.*, g.pricegroup
+ FROM partscustomer p
+ LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
+ WHERE p.parts_id = ?
+ AND p.customer_id = $form->{customer_id}
+
+ UNION
+
+ SELECT p.*, g.pricegroup
+ FROM partscustomer p
+ LEFT JOIN pricegroup g ON (g.id = p.pricegroup_id)
+ JOIN customer c ON (c.pricegroup_id = g.id)
+ WHERE p.parts_id = ?
+ AND c.id = $form->{customer_id}
+
+ UNION
+
+ SELECT p.*, '' AS pricegroup
+ FROM partscustomer p
+ WHERE p.customer_id = 0
+ AND p.pricegroup_id = 0
+ AND p.parts_id = ?
+
+ ORDER BY customer_id DESC, pricegroup_id DESC, pricebreak
+
+ |;
+ my $sth = $dbh->prepare($query) || $form->dberror($query);
+
+ $sth;
+
+}
+
+
+sub price_matrix {
+ my ($pmh, $ref, $transdate, $decimalplaces, $form, $myconfig, $init) = @_;
+
+ $pmh->execute($ref->{id}, $ref->{id}, $ref->{id});
+
+ $ref->{pricematrix} = "";
+ my $customerprice;
+ my $pricegroup;
+ my $sellprice;
+ my $mref;
+
+ while ($mref = $pmh->fetchrow_hashref(NAME_lc)) {
+
+ $customerprice = 0;
+ $pricegroup = 0;
+
+ # check date
+ if ($mref->{validfrom}) {
+ next if $transdate < $form->datetonum($mref->{validfrom}, $myconfig);
+ }
+ if ($mref->{validto}) {
+ next if $transdate > $form->datetonum($mref->{validto}, $myconfig);
+ }
+
+ # convert price
+ $sellprice = $form->round_amount($mref->{sellprice} * $form->{$mref->{curr}}, $decimalplaces);
+
+ if ($mref->{customer_id}) {
+ $ref->{sellprice} = $sellprice unless $mref->{pricebreak};
+ $ref->{pricematrix} .= "$mref->{pricebreak}:$sellprice ";
+ $customerprice = 1;
+ }
+
+ if ($mref->{pricegroup_id}) {
+ if (! $customerprice) {
+ $ref->{sellprice} = $sellprice unless $mref->{pricebreak};
+ $ref->{pricematrix} .= "$mref->{pricebreak}:$sellprice ";
+ $pricegroup = 1;
+ }
+ }
+
+ if (! $customerprice && ! $pricegroup) {
+ $ref->{sellprice} = $sellprice unless $mref->{pricebreak};
+ $ref->{pricematrix} .= "$mref->{pricebreak}:$sellprice ";
+ }
+
+ if ($form->{tradediscount}) {
+ $ref->{sellprice} = $form->round_amount($ref->{sellprice} / (1 - $form->{tradediscount}), $decimalplaces);
+ }
+
+ }
+ $pmh->finish;
+
+ if ($ref->{pricematrix} !~ /^0:/) {
+ if ($init) {
+ $sellprice = $form->round_amount($ref->{sellprice}, $decimalplaces);
+ } else {
+ $sellprice = $form->round_amount($ref->{sellprice} * (1 - $form->{tradediscount}), $decimalplaces);
+ }
+ $ref->{pricematrix} = "0:$sellprice ".$ref->{pricematrix};
+ }
+ chop $ref->{pricematrix};
+
+}
+
+
+sub exchangerate_defaults {
+ my ($dbh, $form) = @_;
+
+ my $var;
+
+ # get default currencies
+ my $query = qq|SELECT substr(curr,1,3), curr FROM defaults|;
+ my $eth = $dbh->prepare($query) || $form->dberror($query);
+ $eth->execute;
+ ($form->{defaultcurrency}, $form->{currencies}) = $eth->fetchrow_array;
+ $eth->finish;
+
+ $query = qq|SELECT buy
+ FROM exchangerate
+ WHERE curr = ?
+ AND transdate = ?|;
+ my $eth1 = $dbh->prepare($query) || $form->dberror($query);
+
+ $query = qq~SELECT max(transdate || ' ' || buy || ' ' || curr)
+ FROM exchangerate
+ WHERE curr = ?~;
+ my $eth2 = $dbh->prepare($query) || $form->dberror($query);
+
+ # get exchange rates for transdate or max
+ foreach $var (split /:/, substr($form->{currencies},4)) {
+ $eth1->execute($var, $form->{transdate});
+ ($form->{$var}) = $eth1->fetchrow_array;
+ if (! $form->{$var} ) {
+ $eth2->execute($var);
+
+ ($form->{$var}) = $eth2->fetchrow_array;
+ ($null, $form->{$var}) = split / /, $form->{$var};
+ $form->{$var} = 1 unless $form->{$var};
+ $eth2->finish;
+ }
+ $eth1->finish;
+ }
+
+ $form->{$form->{defaultcurrency}} = 1;
+
+}
+
+