SQL queries from ceb, including profitable trade calculator

[ypp-sc-tools.db-live.git] / yarrg / web / profitable_trades
diff --git a/yarrg/web/profitable_trades b/yarrg/web/profitable_trades

new file mode 100755 (executable)

index 0000000..6aab676
--- /dev/null
+++ b/yarrg/web/profitable_trades
@@ -0,0 +1,46 @@
+<HTML>
+<HEAD>
+<TITLE>test pirate page</TITLE>
+</HEAD>
+<BODY>
+
+<html><head><title>Profitable trades</title></head><body>
+<%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();
+</%perl>
+<table frame=box rules=all>
+<tr><td>commod</td><td>buy island</td><td>buy stall</td><td>buy price</td><td>buy quantity</td><td>sell island</td><td>sell stall</td><td>sell price</td><td>sell qty</td></tr>
+% my @row;
+% while (@row=$sth->fetchrow_array) {
+<tr>
+% foreach my $cell (@row) {
+<td>
+<% $cell %>
+</td>
+% }
+</tr>
+% }
+</table>
+
+</BODY>
+</HTML>
+
+<%init>
+use CommodsWeb;
+my $dbh= dbw_connect('Midnight');
+</%init>