Mercurial > hg > reproctool-df
changeset 9:6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
author | Dominic Cleal <dominic@computerkb.co.uk> |
---|---|
date | Sun, 25 Jan 2009 13:06:33 +0000 |
parents | e30154411e63 |
children | de10d02dc715 |
files | reproctool.cgi |
diffstat | 1 files changed, 34 insertions(+), 26 deletions(-) [+] |
line wrap: on
line diff
--- 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;