delete from designinfo where tablename = 'articles';
delete from designinfo where tablename = 'prices';

INSERT INTO designinfo VALUES ('articles', 'articlenr',  NULL,                      'Part No',  1, 1, NULL,NULL);
INSERT INTO designinfo VALUES ('articles', 'name',       NULL,                      'Name',     2, 1, NULL,NULL);
INSERT INTO designinfo VALUES ('articles', 'info',      'textarea 3 45',            'Info',     3, 1, NULL,NULL);
INSERT INTO designinfo VALUES ('articles', 'category',  'relationpopup articles category',                    'Category', 4, 1, NULL,NULL);
INSERT INTO designinfo VALUES ('articles', 'margin',     NULL,                      'margin',   4, 2, NULL,NULL);

INSERT INTO designinfo VALUES ('prices', 'articlenr',	  NULL,                        'Part No', 1, 1, NULL,NULL);
INSERT INTO designinfo VALUES ('prices', 'vendor',    'relationpopup vendors name', 'Vendor',   2, 1, NULL,NULL);
INSERT INTO designinfo VALUES ('prices', 'instock',    NULL,                         NULL,      2, 2, NULL,NULL);
INSERT INTO designinfo VALUES ('prices', 'price',      NULL,                        'Price',    3, 1, NULL,NULL);
INSERT INTO designinfo VALUES ('prices', 'PRICE10',      NULL,               '10% of Price',    3, 2, NULL,NULL);



delete from relation where parent = 'articles';
delete from relation where parent = 'vendors';
INSERT INTO relation VALUES ('articles', 'articlenr', 'prices', 'articlenr', NULL, 't');
INSERT INTO relation VALUES ('vendors', 'name', 'prices', 'vendor', NULL, 't');



delete from tabledesign where tablename = 'prices';
INSERT INTO tabledesign values ('prices',NULL,'t','"<TABLE BODER=1 WIDTH=50%>
<TH BGCOLOR=$headerbgcol> </THD>
<TH BGCOLOR=$headerbgcol>
<FONT COLOR=$headertxtcol>Part No</TH>
<TH BGCOLOR=$headerbgcol>
<FONT COLOR=$headertxtcol>Vendor</TH>
<TH BGCOLOR=$headerbgcol>
<FONT COLOR=$headertxtcol>Price</TH>
<TH BGCOLOR=$headerbgcol>
<FONT COLOR=$headertxtcol>in stock</TH>
"','if ($values{instock} eq "0") {
  $values{instock}=$buttons{''false''};
} elsif ($values{instock} eq "1") {
  $values{instock}=$buttons{''true''};
} else {
  $values{instock}=$buttons{''undef''};
}

"<TR>
<TD BGCOLOR=$tablebgcol>$_ edit </A>
<TD BGCOLOR=$tablebgcol> $values{articlenr}
<TD BGCOLOR=$tablebgcol> $values{vendor}
<TD BGCOLOR=$tablebgcol><INPUT NAME=\\"$oid:price\\" TYPE=TEXT 
VALUE=\\"$values{price}\\" SIZE=10>
<TD BGCOLOR=$tablebgcol ALIGN=CENTER> 
$values{instock}
</TR>"','','$_."ORDER BY PRICE"');




delete from EQUATION where EQNAME = 'TEN_PERCENT';
INSERT INTO EQUATION values ('TEN_PERCENT', 'local($in) = @_; $in * 0.1;');


delete from virtual where TABLENAME = 'prices';
delete from virtual where TABLENAME = 'articles';
INSERT INTO virtual values ( 'prices','PRICE10','float', 0,'&callDBaseSub (''TEN_PERCENT'', $values{''price''})');
INSERT INTO "virtual" values ('articles','BEST_PRICE','text',1,'#
# first of all select all records matching the current records
# articleNr from our price table
$where  = "WHERE articleNr=''".xChop($values{articlenr})."''";
$cmd    = "SELECT instock, vendor, price FROM prices $where";
$xret   = $dbconn->prepare($cmd);
$xret->execute || $log->debug("$cmd -- could not get items in stock. SQL Error:$DBI::errstr");

$winner = 9999999;

#
# scan them
while(@array = $xret->fetchrow_array) {
        #
        # and only look at those which refer to articles that are in stock
        if(@array[0] eq "1") {

                #
                # use XChop to remove any trailing blanks and keep
                # the vendor in mind
                $vendor = xChop(@array[1]);

                #
                # then get the shippingCost that is usually billed
                # by this vendor
                $cmd     = "SELECT shippingCost FROM vendors ";
                $cmd    .="WHERE name=''$vendor''";
                $lret    = $dbconn->prepare($cmd);
                $lret->execute || $log->debug("could not get shipping cost. SQL Error:$DBI::errstr");

                #
                # and set our $sh variable accordingly
                if(@ldat = $lret->fetchrow_array) {
                        $sh     = @ldat[0];
                } else {
                        $sh     = 0.0;
                }

                #
                # now calculate the total price of the article if purchased
                # from this vendor
                $price = @array[2] + $sh;

                #
                # and if this was the cheapest go ahead and store
                # the reference
                if($price > 0 && $price < $winner) {
                        $winner = $price;
                        $winnm  = $vendor;
                }
        }
}

#
# finally return our result...
unless ($winnm) {
        "0.00 sorry, no price";
} else {
        sprintf("%.2f $winnm", $winner);
}
');

