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',    'intasbool',                         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, 1);
INSERT INTO RELATION VALUES ('VENDORS', 'NAME', 'PRICES', 'VENDOR', NULL, 1);



delete from tabledesign where tablename = 'PRICES';
INSERT INTO tabledesign values ('PRICES',NULL,1,'"<TABLE BODER=1 WIDTH=50%> <TH BGCOLOR=$headerbgcol> </TH> <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','1.0','&callDBaseSub (''TEN_PERCENT'', $values{''PRICE''})');
INSERT INTO VIRTUAL VALUES  ( 'ARTICLES','BEST_PRICE','text','1.0','$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; while(@array = $xret->fetchrow_array) { if(@array[0] eq "1") { $vendor = xChop(@array[1]); $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"); if(@ldat = $lret->fetchrow_array) { $sh     = @ldat[0]; } else { $sh     = 0.0; } $price = @array[2] + $sh; if($price > 0 && $price < $winner) { $winner = $price; $winnm  = $vendor; } } } unless ($winnm) { "0.00 sorry, no price"; } else { sprintf("%.2f $winnm", $winner); } ');

