[ic] Pricing causing hang

[Denis Heinrichs]

Hi,

Through a lot of searching of docs, posting messages, head banging, I 
managed
to hack together the following to help me lookup prices in a system 
where there
are three pricing levels.

Whenever I login as a user with a pricing level other than retail, the 
page hangs
and eventually comes up with a server error.  

I am testing with the following code:

[query type=list sql="select sku, description, thumb from products where 
sku='ACC13801'"
    prefix="sql" list_prefix="sql"]
[sql]
    The price is: [sql-price]
[/sql]
[/query]

I know my code is not very elegant....any suggestions would be greatly 
appreciated.

Here is my code:

CommonAdjust   "[lookup-pricing table='discounts' 'retail']", ;:price, 
==:options

Profile level1 { CommonAdjust => "[lookup-pricing table='discounts' 
'level1'], ;:discounts:level1 ;:level1" }
Profile level2 { CommonAdjust => "[lookup-pricing table='discounts' 
'level2'], ;:discounts:level2 ;:level2" }
Profile level3 { CommonAdjust => "[lookup-pricing table='discounts' 
'level3'], ;:discounts:level3 ;:level3" }
Profile default { CommonAdjust => '[lookup-pricing table="discounts" 
"retail"], ;:price, ==:options' }

With the following usertag:

UserTag lookup-pricing Order table
UserTag lookup-pricing Routine <<EOR
sub {
        my ($price_db, $level) = @_;
        my $code = $item->{code};
        my $qty = $item->{quantity};

        my $db = Vend::Data::database_exists_ref($price_db);
        my $dbh = $db->[$Vend::Table::DBI::DBI];
        my $sql = qq{SELECT retail, level1, level2, level3 FROM 
$price_db WHERE sku='$code'};
        my $sth = $dbh->prepare($sql);
        $sth->execute();

        my $sql1 = qq{SELECT price, level1, level2, level3 FROM products 
WHERE sku='$code'};
        my $sth1 = $dbh->prepare($sql1);
        $sth1->execute();

        if ($sth) {
            my $sql_results = $sth->fetchrow_hashref();
            my $d_price = $$sql_results{retail};
            my $d_level1 = $$sql_results{level1};
            my $d_level2 = $$sql_results{level2};
            my $d_level3 = $$sql_results{level3};


            my $sql_results1 = $sth1->fetchrow_hashref();
            my $p_price = $$sql_results1{price};
            my $p_level1 = $$sql_results1{level1};
            my $p_level2 = $$sql_results1{level2};
            my $p_level3 = $$sql_results1{level3};

            my $price = $p_price;

            if ($level eq "level1") {
                foreach my $v ($d_price, $d_level1, $p_level1) {
                    if ($v) {
                        if ($v < $price ) {
                            $price = $v;
                        }
                    }
                }
            } elsif ($level eq "level2") {
                foreach my $v ($d_price, $d_level1, $p_level1, 
$d_level2, $p_level2 ) {
                    if ($v) {
                        if ($v < $price ) {
                            $price = $v;
                        }
                    }
                }
            } elsif ($level eq "level3") {
                foreach my $v ($d_price, $d_level1, $p_level1, 
$d_level2, $p_level2, $d_level3, $p_level3) {
                    if ($v) {
                        if ($v < $price ) {
                            $price = $v;
                        }
                    }
                }
            } else {
                if ($d_price) {
                    if ($d_price < $price ) {
                        $price = $d_price;
                    }
                }

            }

            return $price;
        } else {
            return 0;
        }
}
EOR