From: Ian Jackson <ian@liberator.relativity.greenend.org.uk>
Date: Mon, 21 Sep 2009 00:25:49 +0000 (+0100)
Subject: Do queries and get basic debugging-style results
X-Git-Tag: 5.0^2~125
X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?p=ypp-sc-tools.db-live.git;a=commitdiff_plain;h=d343fff49b802614be47d21b569f43b7d42d4511

Do queries and get basic debugging-style results
---

diff --git a/yarrg/web/query_offers b/yarrg/web/query_offers
index 47c3555..b9befe2 100644
--- a/yarrg/web/query_offers
+++ b/yarrg/web/query_offers
@@ -87,11 +87,75 @@ $emsgokorprint->($emsg) or $cmid=undef;
 return unless defined $cmid and @islandids;
 
 foreach my $wf (@warningfs) { $wf->(); }
-</%perl>
 
+</%perl>
 <pre>
 NOT YET IMPLEMENTED
-
+bs= <% $qa->{BuySell} %>
 cmdid= <% $cmid %>
 islandids= <% join ',', map { defined($_) ? $_ : 'U' } @islandids %>
 </pre>
+<%perl>
+
+my $locdesc;
+if (@islandids>1) {
+	$locdesc= ' at specified locations';
+} elsif (defined $islandids[0]) {
+	my $sth= $dbh->prepare("SELECT islandname FROM islands
+				 WHERE islandid == ?");
+	$sth->execute($islandids[0]);
+	$locdesc= ' at '.($sth->fetchrow_array())[0];
+} else {
+	$locdesc= ' in '.$archipelagoes[0];
+}
+
+my @conds;
+my @condvals;
+push @condvals, $cmid;
+foreach my $ix (0..$#islandids) {
+	my $iid= $islandids[$ix];
+	my $arch= $archipelagoes[$ix];
+	if (defined $iid) {
+		push @conds, 'offers.islandid == ?';
+		push @condvals, $iid;
+	} else {
+		push @conds, 'islands.archipelago == ?';
+		push @condvals, $arch;
+	}
+}
+foreach my $bs (split /_/, $qa->{BuySell}) {
+	die unless grep { $bs eq $_ } qw(buy sell);
+	my $ascdesc= $bs eq 'buy' ? 'DESC' : 'ASC';
+</%perl>
+<h2>Offers to <% uc $bs |h %> <% $commodname |h %> <% $locdesc %></h2>
+<%perl>
+	my $stmt= "
+	    SELECT archipelago, islandname, stallname, price, qty, timestamp
+		FROM $bs AS offers
+		JOIN islands ON offers.islandid==islands.islandid
+		JOIN uploads ON offers.islandid==uploads.islandid
+		JOIN stalls ON offers.stallid==stalls.stallid
+		WHERE offers.commodid == ?
+                 AND ( ".join("
+		    OR ", @conds)."
+		     )
+		ORDER BY archipelago, islandname, price $ascdesc, qty ASC,
+			stallname $ascdesc
+";
+	if ($qa->{'debug'}) {
+</%perl>
+<pre>
+<% $stmt %>
+<% join ',', @condvals |h %>
+</pre>
+<%perl>
+	}
+
+	my $sth= $dbh->prepare($stmt);
+	$sth->execute(@condvals);
+</%perl>
+	<& dumptable, sth => $sth &>
+<%perl>
+}
+
+</%perl>