X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?a=blobdiff_plain;f=yarrg%2Fweb%2Froutetrade;h=4885782e58a440b7a773510a049daf363e5e0838;hb=cf9d0277c5d6bfe900a0881a00984f8268a90514;hp=c135e67bc5b16a69452fec1a559f332eebf1a5cd;hpb=c20deee42fbd2d86e976bb6a2e4f8e5f5cf2631b;p=ypp-sc-tools.db-live.git

diff --git a/yarrg/web/routetrade b/yarrg/web/routetrade
index c135e67..4885782 100644
--- a/yarrg/web/routetrade
+++ b/yarrg/web/routetrade
@@ -32,21 +32,6 @@
  This Mason component is the core trade planner for a specific route.
 
 
-========== TODO ==========
-16:36 <ceb> alpha,byrne,papaya,turtle,jorvik,luthien is my example
-
-16:46 <ceb> Also trading plan not functional but I guess you know that :-)
-
-use POST for update.  Hrrm.
-
-LATER OR NOT AT ALL
-
-adjustable potential cost of losses (rather than fixed 1e-BIG per league)
-
-max volume/mass
-
-========== TODO ==========
-
 </%doc>
 <%args>
 $dbh
@@ -54,12 +39,18 @@ $dbh
 @archipelagoes
 $qa
 </%args>
+<&| script &>
+  da_pageload= Date.now();
+</&script>
+
 <%perl>
 
+my $now= time;
 my $loss_per_league= 1e-7;
 
 my @flow_conds;
 my @query_params;
+my %dists;
 
 my $sd_condition= sub {
 	my ($bs, $ix) = @_;
@@ -95,10 +86,12 @@ foreach my $src_i (0..$#islandids) {
 
 		if ($specific && !$confusing &&
 		    # With a circular route, do not carry goods round the loop
-		    !($src_i==0 && $dst_i==$#islandids &&
+		    !(($src_i==0 || $src_i==$#islandids) &&
+		      $dst_i==$#islandids &&
 		      $src_isle == $islandids[$dst_i])) {
 			if ($islandpair{$src_isle,$dst_isle}) {
 				$confusing= 1;
+print "confusing $src_i $src_isle  $dst_i $dst_isle\n";
 			} else {
 				$islandpair{$src_isle,$dst_isle}=
 					[ $src_i, $dst_i ];
@@ -115,19 +108,29 @@ my $stmt= "
 	SELECT  sell_islands.islandname				org_name,
 		sell_islands.islandid				org_id,
 		sell.price					org_price,
+		sell.qty 					org_qty_stall,
+		sell_stalls.stallname				org_stallname,
+		sell.stallid					org_stallid,
+		sell_uploads.timestamp				org_timestamp,
 		buy_islands.islandname				dst_name,
 		buy_islands.islandid				dst_id,
 		buy.price					dst_price,
-".($qa->{ShowStalls} ? "
-		sell.stallid					org_stallid,
-		sell_stalls.stallname				org_stallname,
-		sell.qty 					org_qty,
-		buy.stallid					dst_stallid,
+		buy.qty 					dst_qty_stall,
 		buy_stalls.stallname				dst_stallname,
-		buy.qty 					dst_qty,
+		buy.stallid					dst_stallid,
+		buy_uploads.timestamp				dst_timestamp,
+".($qa->{ShowStalls} ? "
+		sell.qty 					org_qty_agg,
+		buy.qty 					dst_qty_agg,
 " : "
-		sum(sell.qty) 					org_qty,
-		sum(buy.qty) 					dst_qty,
+		(SELECT sum(qty) FROM sell AS sell_agg
+		  WHERE	sell_agg.commodid = commods.commodid
+		  AND	sell_agg.islandid = sell.islandid
+		  AND	sell_agg.price = sell.price)		org_qty_agg,
+		(SELECT sum(qty) FROM buy AS buy_agg
+		  WHERE	buy_agg.commodid = commods.commodid
+		  AND	buy_agg.islandid = buy.islandid
+		  AND	buy_agg.price = buy.price)		dst_qty_agg,
 ")."
 		commods.commodname				commodname,
 		commods.commodid				commodid,
@@ -136,31 +139,43 @@ my $stmt= "
 		dist						dist,
 		buy.price - sell.price				unitprofit
 	FROM commods
-	JOIN buy  ON commods.commodid = buy.commodid
 	JOIN sell ON commods.commodid = sell.commodid
+	JOIN buy  ON commods.commodid = buy.commodid
 	JOIN islands AS sell_islands ON sell.islandid = sell_islands.islandid
 	JOIN islands AS buy_islands  ON buy.islandid  = buy_islands.islandid
-".($qa->{ShowStalls} ? "
+	JOIN uploads AS sell_uploads ON sell.islandid = sell_uploads.islandid
+	JOIN uploads AS buy_uploads  ON buy.islandid  = buy_uploads.islandid
 	JOIN stalls  AS sell_stalls  ON sell.stallid  = sell_stalls.stallid
 	JOIN stalls  AS buy_stalls   ON buy.stallid   = buy_stalls.stallid
-" : "")."
 	JOIN dists ON aiid = sell.islandid AND biid = buy.islandid
 	WHERE	(
 		".join("
 	   OR	", @flow_conds)."
 	)
 	  AND	buy.price > sell.price
-".($qa->{ShowStalls} ? "" : "
-	GROUP BY commods.commodid, org_id, org_price, dst_id, dst_price
-")."
 	ORDER BY org_name, dst_name, commodname, unitprofit DESC,
-		 org_price, dst_price DESC
+		 org_price, dst_price DESC,
+		 org_stallname, dst_stallname
      ";
 
 my $sth= $dbh->prepare($stmt);
 $sth->execute(@query_params);
 my @flows;
 
+my $distquery= $dbh->prepare("
+		SELECT dist FROM dists WHERE aiid = ? AND biid = ?
+		");
+my $distance= sub {
+	my ($from,$to)= @_;
+	my $d= $dists{$from}{$to};
+	return $d if defined $d;
+	$distquery->execute($from,$to);
+	$d = $distquery->fetchrow_array();
+	defined $d or die "$from $to ?";
+	$dists{$from}{$to}= $d;
+	return $d;
+};
+
 my @cols= ({ NoSort => 1 });
 
 my $addcols= sub {
@@ -184,8 +199,13 @@ if ($qa->{ShowStalls}) {
 }
 $addcols->({ Text => 1 }, qw(commodname));
 $addcols->({ DoReverse => 1 },
-	qw(	org_price org_qty dst_price dst_qty
-		Margin unitprofit MaxQty
+	qw(	org_price org_qty_agg dst_price dst_qty_agg
+	));
+$addcols->({ DoReverse => 1, SortColKey => 'MarginSortKey' },
+	qw(	Margin
+	));
+$addcols->({ DoReverse => 1 },
+	qw(	unitprofit dist MaxQty
 		MaxCapital MaxProfit
 	));
 
@@ -200,27 +220,65 @@ $addcols->({ DoReverse => 1 },
 
 <& dumptable:start, qa => $qa, sth => $sth &>
 % {
-%   my $f;
-%   while ($f= $sth->fetchrow_hashref()) {
+%   my $got;
+%   while ($got= $sth->fetchrow_hashref()) {
 <%perl>
 
-	$f->{Ix}= @flows;
-	$f->{Var}= "f$f->{Ix}";
+	my $f= $flows[$#flows];
+	if (	!$f ||
+		$qa->{ShowStalls} ||
+		grep { $f->{$_} ne $got->{$_} }
+			qw(org_id org_price dst_id dst_price commodid)
+	) {
+		# Make a new flow rather than adding to the existing one
+
+		$f= {
+			Ix => scalar(@flows),
+			Var => "f".@flows,
+			%$got
+		};
+		$f->{"org_stallid"}= $f->{"dst_stallid"}= 'all'
+			if !$qa->{ShowStalls};
+		push @flows, $f;
+	}
+	foreach my $od (qw(org dst)) {
+		$f->{"${od}Stalls"}{
+			$got->{"${od}_stallname"}
+		    } =
+			$got->{"${od}_qty_stall"}
+		    ;
+	}
 
-	$f->{MaxQty}= $f->{'org_qty'} < $f->{'dst_qty'}
-		? $f->{'org_qty'} : $f->{'dst_qty'};
+</%perl>
+<& dumptable:row, qa => $qa, sth => $sth, row => $f &>
+%    }
+<& dumptable:end, qa => $qa &>
+% }
+
+<%perl>
+
+if (!@flows) {
+	print 'No profitable trading opportunities were found.';
+	return;
+}
+
+foreach my $f (@flows) {
+
+	$f->{MaxQty}= $f->{'org_qty_agg'} < $f->{'dst_qty_agg'}
+		? $f->{'org_qty_agg'} : $f->{'dst_qty_agg'};
 	$f->{MaxProfit}= $f->{MaxQty} * $f->{'unitprofit'};
 	$f->{MaxCapital}= $f->{MaxQty} * $f->{'org_price'};
 
+	$f->{MarginSortKey}= sprintf "%d",
+		$f->{'dst_price'} * 10000 / $f->{'org_price'};
 	$f->{Margin}= sprintf "%3.1f%%",
 		$f->{'dst_price'} * 100.0 / $f->{'org_price'} - 100.0;
 
-	$f->{"org_stallid"}= $f->{"dst_stallid"}= 'all'
-		if !$qa->{ShowStalls};
-
 	$f->{ExpectedUnitProfit}=
 		$f->{'dst_price'} * (1.0 - $loss_per_league) ** $f->{'dist'}
-		- $f->{'src_price'};
+		- $f->{'org_price'};
+
+	$dists{'org_id'}{'dst_id'}= $f->{'dist'};
 
 	my @uid= $f->{commodid};
 	foreach my $od (qw(org dst)) {
@@ -290,13 +348,8 @@ die "$cmpu $uue ?" if length $cmpu > 20;
 		$f->{Suppress}= 1;
 	}
 
-	push @flows, $f;
-
+}
 </%perl>
-<& dumptable:row, qa => $qa, sth => $sth, row => $f &>
-%   }
-<& dumptable:end, qa => $qa &>
-% }
 
 % my $optimise= $specific && !$confusing && @islandids>1;
 % if (!$optimise) {
@@ -312,7 +365,7 @@ Route contains archipelago(es), not just specific islands.
 Route is complex - it visits the same island several times
 and isn't a simple loop.
 % }
-Therefore, optimal trade pattern not calculated.
+Therefore, optimal voyage trade plan not calculated.
 
 % } else { # ========== OPTMISATION ==========
 <%perl>
@@ -348,7 +401,7 @@ foreach my $flow (@flows) {
 		);
 			
 		push @{ $avail_csts{$cstname}{Flows} }, $flow->{Var};
-		$avail_csts{$cstname}{Qty}= $flow->{"${od}_qty"};
+		$avail_csts{$cstname}{Qty}= $flow->{"${od}_qty_agg"};
 	}
 }
 foreach my $cstname (sort keys %avail_csts) {
@@ -382,7 +435,9 @@ if ($qa->{'debug'}) {
 		qw(glpsol --cpxlp /dev/stdin -o /dev/stdout));
 	print "<pre>\n" if $qa->{'debug'};
 	my $found_section= 0;
+	my $glpsol_out= '';
 	while (<$output>) {
+		$glpsol_out.= $_;
 		print encode_entities($_) if $qa->{'debug'};
 		if (m/^\s*No\.\s+Column name\s+St\s+Activity\s/) {
 			die if $found_section>0;
@@ -403,8 +458,9 @@ if ($qa->{'debug'}) {
 		$flow->{OptCapital}= $flow->{OptQty} * $flow->{'org_price'};
 	}
 	print "</pre>\n" if $qa->{'debug'};
-	pipethrough_run_finish($output, 'glpsol');
-	die unless $found_section;
+	my $prerr= "\n=====\n$cplex\n=====\n$glpsol_out\n=====\n ";
+	pipethrough_run_finish($output,$prerr);
+	die $prerr unless $found_section;
 };
 
 $addcols->({ DoReverse => 1 }, qw(
@@ -430,6 +486,7 @@ $addcols->({ Total => 0, DoReverse => 1 }, qw(
 <colgroup span=2>
 <colgroup span=2>
 <colgroup span=2>
+<colgroup span=1>
 <colgroup span=3>
 %	if ($optimise) {
 <colgroup span=3>
@@ -442,6 +499,7 @@ $addcols->({ Total => 0, DoReverse => 1 }, qw(
 <th colspan=2>Collect
 <th colspan=2>Deliver
 <th colspan=2>Profit
+<th colspan=1>
 <th colspan=3>Max
 %	if ($optimise) {
 <th colspan=3>Planned
@@ -458,6 +516,7 @@ $addcols->({ Total => 0, DoReverse => 1 }, qw(
 <th>Qty
 <th>Margin
 <th>Unit
+<th>Dist
 <th>Qty
 <th>Capital
 <th>Profit
@@ -485,7 +544,9 @@ $addcols->({ Total => 0, DoReverse => 1 }, qw(
 %		my $v= $flow->{$col->{Name}};
 %		$col->{Total} += $v if defined $col->{Total};
 %		$v='' if !$col->{Text} && !$v;
-%		$ts_sortkeys{$ci}{$rowid}= $v;
+%		my $sortkey= $col->{SortColKey} ?
+%			$flow->{$col->{SortColKey}} : $v;
+%		$ts_sortkeys{$ci}{$rowid}= $sortkey;
 <td <% $col->{Text} ? '' : 'align=right' %>><% $v |h %>
 %	}
 % }
@@ -501,15 +562,10 @@ $addcols->({ Total => 0, DoReverse => 1 }, qw(
 % }
 </table>
 
-<& tabsort, cols => \@cols, table => 'trades', rowclass => 'datarow',
+<&| tabsort, cols => \@cols, table => 'trades', rowclass => 'datarow',
 	throw => 'trades_sort', tbrow => 'trades_total' &>
-<&| script &>
   ts_sortkeys= <% to_json_protecttags(\%ts_sortkeys) %>;
-  function all_onload() {
-    ts_onload__trades();
-  }
-  window.onload= all_onload;
-</&script>
+</&tabsort>
 
 <input type=submit name=update value="Update">
 
@@ -517,34 +573,138 @@ $addcols->({ Total => 0, DoReverse => 1 }, qw(
 %
 % my $iquery= $dbh->prepare('SELECT islandname FROM islands
 %				WHERE islandid = ?');
+% my %da_ages;
+% my $total_total= 0;
+% my $total_dist= 0;
 %
 <h1>Voyage trading plan</h1>
-<table>
+<table rules=groups>
 % foreach my $i (0..$#islandids) {
-<tr><td colspan=4><strong>
+<tbody>
+<tr><td colspan=3>
 %	$iquery->execute($islandids[$i]);
 %	my ($islandname) = $iquery->fetchrow_array();
+%	my $this_dist= $distance->($islandids[$i-1],$islandids[$i]);
+%	$total_dist += $this_dist;
 %	if (!$i) {
-Start at <% $islandname |h %>
+<strong>Start at <% $islandname |h %></strong>
 %	} else {
-Sail to <% $islandname |h %>
+<strong>Sail to <% $islandname |h %></strong>
+- <% $this_dist |h %> leagues </td>
+%	}
+<%perl>
+     my $age_reported= 0;
+     my %flowlists;
+     foreach my $od (qw(org dst)) {
+	foreach my $f (@flows) {
+		next if $f->{Suppress};
+		next unless $f->{"${od}_id"} == $islandids[$i];
+		next unless $f->{OptQty};
+		my $arbitrage= $f->{'org_id'} == $f->{'dst_id'};
+		my $loop= $islandids[0] == $islandids[-1] &&
+			  ($i==0 || $i==$#islandids);
+		next if $loop and ($arbitrage ? $i :
+			!!$i == !!($od eq 'org'));
+		my $price= $f->{"${od}_price"};
+		my $stallname= $f->{"${od}_stallname"};
+		my $todo= \$flowlists{$od}{
+				$f->{'commodname'},
+				(sprintf "%07d", ($od eq 'dst' ?
+						9999999-$price : $price)),
+				$stallname
+			};
+		$$todo= {
+			Qty => 0,
+			orgArbitrage => 0,
+			dstArbitrage => 0,
+		} unless $$todo;
+		$$todo->{'commodname'}= $f->{'commodname'};
+		$$todo->{'stallname'}= $stallname;
+		$$todo->{Price}= $price;
+		$$todo->{Timestamp}= $f->{"${od}_timestamp"};
+		$$todo->{Qty} += $f->{OptQty};
+		$$todo->{Total}= $$todo->{Price} * $$todo->{Qty};
+		$$todo->{Stalls}= $f->{"${od}Stalls"};
+		$$todo->{"${od}Arbitrage"}= 1 if $arbitrage;
+	}
+     }
+
+     my $total;
+     my $dline= 0;
+     my $show_flows= sub {
+	my ($od,$arbitrage,$collectdeliver) = @_;
+</%perl>
+%
+%	my $todo= $flowlists{$od};
+%	return unless $todo;
+%	foreach my $tkey (sort keys %$todo) {
+%		my $t= $todo->{$tkey};
+%		next if $t->{"${od}Arbitrage"} != $arbitrage;
+%		if (!$age_reported++) {
+%			my $age= $now - $t->{Timestamp};
+%			my $cellid= "da_${i}";
+%			$da_ages{$cellid}= $age;
+<td colspan=3>\
+(Data age: <span id="<% $cellid %>"><% prettyprint_age($age) %></span>)
+%		} elsif (!defined $total) {
+%			$total= 0;
+<tbody>
+%		}
+%		$total += $t->{Total};
+%		my $span= 0 + keys %{ $t->{Stalls} };
+%		my $td= "td rowspan=$span";
+<tr class="datarow<% $dline %>">
+<<% $td %>><% $collectdeliver %>
+<<% $td %>><% $t->{'commodname'} |h %>
+%
+%		my @stalls= sort keys %{ $t->{Stalls} };
+%		my $pstall= sub {
+%			my $name= $stalls[$_[0]];
+<td><% $name |h %>
+%		};
+%
+%		$pstall->(0);
+<<% $td %> align=right><% $t->{Price} |h %> poe ea.
+<<% $td %> align=right><% $t->{Qty} |h %> unit(s)
+<<% $td %> align=right><% $t->{Total} |h %> total
+%
+%		foreach my $stallix (1..$#stalls) {
+<tr class="datarow<% $dline %>">
+%			$pstall->($stallix);
+%		}
+%
+%		$dline ^= 1;
 %	}
-</strong>
-%    foreach my $od (qw(dst org)) {
-%	my $sign= $od eq 'dst' ? -1 : +1;
-%	foreach my $f (sort {
-%			$a->{'commodname'} cmp $b->{'commodname'}
-%		or $sign * ($a->{"${od}_price"} <=> $b->{"${od}_price"})
-%		or	$a->{"${od}_stallname"} cmp $b->{"${od}_stallname"}
-%		} @flows) {
-%		next if $f->{Suppress};
-%		next unless $f->{"${od}_id"} == $islandids[$i];
-%		next unless $f->{OptQty};
-<tr><td>Buy or sell flow 
+%    };
+%    my $show_total= sub {
+%	my ($totaldesc, $sign)= @_;
+%	if (defined $total) {
+<tr>
+<td colspan=3>
+<td colspan=2 align=right><% $totaldesc %>
+<td align=right><% $total |h %> total
+%		$total_total += $sign * $total;
 %	}
-%    }
-% }
+%	$total= undef;
+%	$dline= 0;
+<%perl>
+     };
+
+     $show_flows->('dst',0,'Deliver'); $show_total->('Proceeds',1);
+     $show_flows->('org',1,'Collect'); $show_total->('(Arbitrage) outlay',-1);
+     $show_flows->('dst',1,'Deliver'); $show_total->('(Arbitrage) proceeds',1);
+     $show_flows->('org',0,'Collect'); $show_total->('Outlay',-1);
+
+}
+</%perl>
+<tbody><tr>
+<td colspan=2>Total distance: <% $total_dist %> leagues.
+<td colspan=3 align=right>Overall net cash flow
+<td align=right><strong><%
+  $total_total < 0 ? -$total_total." loss" : $total_total." gain"
+ %></strong>
 </table>
+<& query_age:dataages, id2age => \%da_ages &>
 %
 % } # ========== TRADING PLAN ==========