# HG changeset patch
# User Dominic Cleal <dominic@computerkb.co.uk>
# Date 1232888793 0
# Node ID 6b98a17fbe22b5425c69cbe2d134898bafb16907
# Parent  e30154411e6334987d4f140f91e9ec9c582b2833
Changing to a single SQL query for type-based sorting and efficiency

diff -r e30154411e63 -r 6b98a17fbe22 reproctool.cgi
--- a/reproctool.cgi	Sun Jan 25 12:31:34 2009 +0000
+++ b/reproctool.cgi	Sun Jan 25 13:06:33 2009 +0000
@@ -40,11 +40,19 @@
 $str_items = $1 if ($str_items =~ /The container .+ contains the following items:(.+)/);
 $str_items = $1 if ($str_items =~ /(.+)Are you sure you want to continue?/);
 
+my @item_names = split(/\s*,\s*/, $str_items);
+
+# SQL fragment to match all items
+my $sql_typenames = '';
+$sql_typenames = 'types.typeName = ?' if ($#item_names >= 0);
+$sql_typenames .= " OR types.typeName = ?" foreach (1..$#item_names);
+
 # SQL lookup for reprocessing amounts
 my $sql_reprocess = <<END;
 SELECT
 	types.typeID,
 	types.typeName,
+	groups.groupName,
 	attrs.valueInt, -- metaLevel
 	types.basePrice,
 	graphics.icon,
@@ -59,39 +67,39 @@
 FROM invTypes types
 INNER JOIN dgmTypeAttributes attrs ON types.typeID = attrs.typeID AND attrs.attributeID = 633
 INNER JOIN typeActivityMaterials m1 ON types.typeID = m1.typeID
+INNER JOIN invGroups groups ON types.groupID = groups.groupID
 INNER JOIN eveGraphics graphics ON types.graphicID = graphics.graphicID
-WHERE types.typeName = ?
-GROUP BY
-	types.typeID,
-	types.typeName,
-	attrs.valueInt,
-	types.basePrice
+WHERE $sql_typenames
+GROUP BY types.typeID
+ORDER BY groupName ASC, typeName ASC
 END
 my $pre_reprocess = $db->prepare($sql_reprocess);
 
+# Execute, bring back one row per item
+my ($tid, $tname, $gname, $meta, $basePrice, $icon,
+    $ttrit, $tpyer, $tmexa, $tisog, $tnocx, $tzydr, $tmega, $tmorp);
+
+$pre_reprocess->execute(@item_names) or die("Can't lookup items: $DBI::errstr");
+$pre_reprocess->bind_columns(undef, \$tid, \$tname, \$gname, \$meta, \$basePrice,
+                             \$icon, \$ttrit, \$tpyer, \$tmexa, \$tisog,
+                             \$tnocx, \$tzydr, \$tmega, \$tmorp);
+
 my @output = ();
-for my $sitem (split(/\s*,\s*/, $str_items))
+while ($pre_reprocess->fetch())
 {
-    my ($tid, $tname, $meta, $basePrice, $icon,
-        $ttrit, $tpyer, $tmexa, $tisog, $tnocx, $tzydr, $tmega, $tmorp);
-    
-    $pre_reprocess->execute($sitem) or die("Can't lookup $sitem: $DBI::errstr");
-    $pre_reprocess->bind_columns(undef, \$tid, \$tname, \$meta, \$basePrice,
-                                 \$icon, \$ttrit, \$tpyer, \$tmexa, \$tisog,
-                                 \$tnocx, \$tzydr, \$tmega, \$tmorp);
-    
     my $item = {};
-    if ($pre_reprocess->fetch())
-    {
-        my $isk = ($trit * $ttrit) + ($pyer * $tpyer) + ($mexa * $tmexa) +
-                  ($isog * $tisog) + ($nocx * $tnocx) + ($zydr * $tzydr) +
-                  ($mega * $tmega) + ($morp * $tmorp);
-        
-        $meta = 0 unless defined $meta;
-        $item = { id => $tid, name => $tname, meta => $meta, icon => $icon,
-                  price => $basePrice, reprocess => $isk };
-    }
-    push @output, $item;
+    my $isk = ($trit * $ttrit) + ($pyer * $tpyer) + ($mexa * $tmexa) +
+                ($isog * $tisog) + ($nocx * $tnocx) + ($zydr * $tzydr) +
+                ($mega * $tmega) + ($morp * $tmorp);
+    
+    $meta = 0 unless defined $meta;
+    $item = { id => $tid, name => $tname, meta => $meta, icon => $icon,
+              price => $basePrice, reprocess => $isk };
+    
+    # If this item exists multiple times in the input, then they weren't stacked
+    # so output it multiple times
+    my @matching_in = grep({ $_ eq $tname } @item_names);
+    push @output, $item foreach (0..$#matching_in);
 }
 
 my $col = 0;