From 4c0fe82716fdf19f385f1f190de80beb6ea3c9c4 Mon Sep 17 00:00:00 2001 From: Ian Jackson Date: Sat, 22 Aug 2009 12:50:59 +0100 Subject: [PATCH] SQL queries from ceb, including profitable trade calculator --- yarrg/web/best-sell-price | 41 ++++++++++++++++++++++++++ yarrg/web/pirates | 58 ------------------------------------- yarrg/web/profitable_trades | 46 +++++++++++++++++++++++++++++ 3 files changed, 87 insertions(+), 58 deletions(-) create mode 100755 yarrg/web/best-sell-price delete mode 100755 yarrg/web/pirates create mode 100755 yarrg/web/profitable_trades diff --git a/yarrg/web/best-sell-price b/yarrg/web/best-sell-price new file mode 100755 index 0000000..ca9208f --- /dev/null +++ b/yarrg/web/best-sell-price @@ -0,0 +1,41 @@ + + +test pirate page + + + +Best commod offer +<%perl> +my $sth=$dbh->prepare( + "SELECT a.commodname,b.price,b.qty,d.stallname,e.islandname + FROM commods as a + JOIN buy as b on a.commodid=b.commodid + JOIN stalls as d on d.stallid=b.stallid + JOIN islands as e on e.islandid=d.islandid + GROUP BY d.islandid + HAVING b.price=MAX(b.price) + "); + +$sth->execute(); + + + +% my @row; +% while (@row=$sth->fetchrow_array) { + +% foreach my $cell (@row) { + +% } + +% } +
commodpriceqtyStallisland
+<% $cell %> +
+ + + + +<%init> +use CommodsWeb; +my $dbh= dbw_connect('Midnight'); + diff --git a/yarrg/web/pirates b/yarrg/web/pirates deleted file mode 100755 index 6a3c478..0000000 --- a/yarrg/web/pirates +++ /dev/null @@ -1,58 +0,0 @@ - - -test pirate page - - - -Select island -
- -
- -my $sth=$dbh->prepare( - "SELECT a.commodname,b.price,c.price,d.stallname,e.islandname - FROM commods as a - JOIN buy as b on a.commodid=b.commodid - JOIN buy as c on a.commodid=c.commodid - JOIN stalls as d on d.stallid=b.stallid - JOIN islands as e on e.islandid=d.islandid - WHERE a.commodname like 'Wood' - "); - -$sth->execute(); - - - -% my @row; -% while (@row=$sth->fetchrow_array) { - -% foreach my $cell (@row) { - -% } - -% } -
-<% $cell %> -
- - - - -<%init> -use CommodsWeb; -my $dbh= dbw_connect('Midnight'); - diff --git a/yarrg/web/profitable_trades b/yarrg/web/profitable_trades new file mode 100755 index 0000000..6aab676 --- /dev/null +++ b/yarrg/web/profitable_trades @@ -0,0 +1,46 @@ + + +test pirate page + + + +Profitable trades +<%perl> +my $sth=$dbh->prepare( + "SELECT commods.commodname, + sell_islands.islandname,sell_stalls.stallname,sell.price,sell.qty, + buy_islands.islandname,buy_stalls.stallname,buy.price,buy.qty + FROM commods + JOIN buy on commods.commodid=buy.commodid + JOIN sell on commods.commodid=sell.commodid + JOIN islands as buy_islands on buy.islandid = buy_islands.islandid + JOIN stalls as buy_stalls on buy.stallid = buy_stalls.stallid + JOIN islands as sell_islands on sell.islandid = sell_islands.islandid + JOIN stalls as sell_stalls on sell.stallid = sell_stalls.stallid + WHERE buy.islandid like '36' and sell.islandid like '5' + and buy.price > sell.price + "); + +$sth->execute(); + + + +% my @row; +% while (@row=$sth->fetchrow_array) { + +% foreach my $cell (@row) { + +% } + +% } +
commodbuy islandbuy stallbuy pricebuy quantitysell islandsell stallsell pricesell qty
+<% $cell %> +
+ + + + +<%init> +use CommodsWeb; +my $dbh= dbw_connect('Midnight'); + -- 2.30.2