chiark / gitweb /
~yarrgweb / ypp-sc-tools.db-test.git / commitdiff
? search:
summary | shortlog | log | commit | commitdiff | tree
raw | patch | inline | side by side (from parent 1: dbd70ca)
SQL queries from ceb, including profitable trade calculator
authorIan Jackson <ijackson@chiark.greenend.org.uk>
Sat, 22 Aug 2009 11:50:59 +0000 (12:50 +0100)
committerIan Jackson <ijackson@chiark.greenend.org.uk>
Sat, 22 Aug 2009 11:50:59 +0000 (12:50 +0100)
yarrg/web/best-sell-price [new file with mode: 0755] patch | blob
yarrg/web/pirates [deleted file] patch | blob | history
yarrg/web/profitable_trades [new file with mode: 0755] patch | blob

diff --git a/yarrg/web/best-sell-price b/yarrg/web/best-sell-price
new file mode 100755 (executable)
index 0000000..ca9208f
--- /dev/null
+++ b/yarrg/web/best-sell-price
@@ -0,0 +1,41 @@
+<HTML>
+<HEAD>
+<TITLE>test pirate page</TITLE>
+</HEAD>
+<BODY>
+
+<html><head><title>Best commod offer</title></head><body>
+<%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();
+</%perl>
+<table frame=box rules=all>
+<tr><td>commod</td><td>price</td><td>qty</td><td>Stall</td><td>island</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>
diff --git a/yarrg/web/pirates b/yarrg/web/pirates
deleted file mode 100755 (executable)
index 6a3c478..0000000
--- a/yarrg/web/pirates
+++ /dev/null
@@ -1,58 +0,0 @@
-<HTML>
-<HEAD>
-<TITLE>test pirate page</TITLE>
-</HEAD>
-<BODY>
-
-<html><head><title>Select island</title></head><body>
-<form action="/ucgi/~clareb/mason/something" method="get">
-<select name="islandid">
-<%perl>
-my $sth=$dbh->prepare("SELECT islandid,archipelago,islandname
-                              FROM islands
-                              ORDER BY archipelago,islandname;");
-$sth->execute();
-</%perl>
-% my $row;
-% while ($row=$sth->fetchrow_hashref) {
-<option value="<% $row->{'islandid'} |h %>"><% $row->{'archipelago'} |h %> -
-<% $row->{'islandname'} |h %>
-% }
-<input type=submit name=submit value="Go">
-</form>
-
-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();
-</%perl>
-<table frame=box rules=all>
-<!--
-<tr><td>Island</td><td>Arch</td><td>Stall</td><td>buy price</td><td>sell price</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>
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>
yarrg testing database