Mercurial > hg > reproctool-df

comparison reproctool.cgi @ 9:6b98a17fbe22

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
comparison
equal deleted inserted replaced
7:e30154411e63 9:6b98a17fbe22
38 38
39 # If the items string contains the contract info too, strip it out 39 # If the items string contains the contract info too, strip it out
40 $str_items = $1 if ($str_items =~ /The container .+ contains the following items:(.+)/); 40 $str_items = $1 if ($str_items =~ /The container .+ contains the following items:(.+)/);
41 $str_items = $1 if ($str_items =~ /(.+)Are you sure you want to continue?/); 41 $str_items = $1 if ($str_items =~ /(.+)Are you sure you want to continue?/);
42 42
43 my @item_names = split(/\s*,\s*/, $str_items);
44
45 # SQL fragment to match all items
46 my $sql_typenames = '';
47 $sql_typenames = 'types.typeName = ?' if ($#item_names >= 0);
48 $sql_typenames .= " OR types.typeName = ?" foreach (1..$#item_names);
49
43 # SQL lookup for reprocessing amounts 50 # SQL lookup for reprocessing amounts
44 my $sql_reprocess = <<END; 51 my $sql_reprocess = <<END;
45 SELECT 52 SELECT
46 types.typeID, 53 types.typeID,
47 types.typeName, 54 types.typeName,
55 groups.groupName,
48 attrs.valueInt, -- metaLevel 56 attrs.valueInt, -- metaLevel
49 types.basePrice, 57 types.basePrice,
50 graphics.icon, 58 graphics.icon,
51 SUM(CASE WHEN m1.requiredTypeID = 34 THEN m1.quantity ELSE 0 END), -- [Tritanium] 59 SUM(CASE WHEN m1.requiredTypeID = 34 THEN m1.quantity ELSE 0 END), -- [Tritanium]
52 SUM(CASE WHEN m1.requiredTypeID = 35 THEN m1.quantity ELSE 0 END), -- [Pyerite] 60 SUM(CASE WHEN m1.requiredTypeID = 35 THEN m1.quantity ELSE 0 END), -- [Pyerite]
57 SUM(CASE WHEN m1.requiredTypeID = 40 THEN m1.quantity ELSE 0 END), -- [Megacyte] 65 SUM(CASE WHEN m1.requiredTypeID = 40 THEN m1.quantity ELSE 0 END), -- [Megacyte]
58 SUM(CASE WHEN m1.requiredTypeID = 11399 THEN m1.quantity ELSE 0 END) -- [Morphite] 66 SUM(CASE WHEN m1.requiredTypeID = 11399 THEN m1.quantity ELSE 0 END) -- [Morphite]
59 FROM invTypes types 67 FROM invTypes types
60 INNER JOIN dgmTypeAttributes attrs ON types.typeID = attrs.typeID AND attrs.attributeID = 633 68 INNER JOIN dgmTypeAttributes attrs ON types.typeID = attrs.typeID AND attrs.attributeID = 633
61 INNER JOIN typeActivityMaterials m1 ON types.typeID = m1.typeID 69 INNER JOIN typeActivityMaterials m1 ON types.typeID = m1.typeID
70 INNER JOIN invGroups groups ON types.groupID = groups.groupID
62 INNER JOIN eveGraphics graphics ON types.graphicID = graphics.graphicID 71 INNER JOIN eveGraphics graphics ON types.graphicID = graphics.graphicID
63 WHERE types.typeName = ? 72 WHERE $sql_typenames
64 GROUP BY 73 GROUP BY types.typeID
65 types.typeID, 74 ORDER BY groupName ASC, typeName ASC
66 types.typeName,
67 attrs.valueInt,
68 types.basePrice
69 END 75 END
70 my $pre_reprocess = $db->prepare($sql_reprocess); 76 my $pre_reprocess = $db->prepare($sql_reprocess);
71 77
78 # Execute, bring back one row per item
79 my ($tid, $tname, $gname, $meta, $basePrice, $icon,
80 $ttrit, $tpyer, $tmexa, $tisog, $tnocx, $tzydr, $tmega, $tmorp);
81
82 $pre_reprocess->execute(@item_names) or die("Can't lookup items: $DBI::errstr");
83 $pre_reprocess->bind_columns(undef, \$tid, \$tname, \$gname, \$meta, \$basePrice,
84 \$icon, \$ttrit, \$tpyer, \$tmexa, \$tisog,
85 \$tnocx, \$tzydr, \$tmega, \$tmorp);
86
72 my @output = (); 87 my @output = ();
73 for my $sitem (split(/\s*,\s*/, $str_items)) 88 while ($pre_reprocess->fetch())
74 { 89 {
75 my ($tid, $tname, $meta, $basePrice, $icon, 90 my $item = {};
76 $ttrit, $tpyer, $tmexa, $tisog, $tnocx, $tzydr, $tmega, $tmorp); 91 my $isk = ($trit * $ttrit) + ($pyer * $tpyer) + ($mexa * $tmexa) +
92 ($isog * $tisog) + ($nocx * $tnocx) + ($zydr * $tzydr) +
93 ($mega * $tmega) + ($morp * $tmorp);
77 94
78 $pre_reprocess->execute($sitem) or die("Can't lookup $sitem: $DBI::errstr"); 95 $meta = 0 unless defined $meta;
79 $pre_reprocess->bind_columns(undef, \$tid, \$tname, \$meta, \$basePrice, 96 $item = { id => $tid, name => $tname, meta => $meta, icon => $icon,
80 \$icon, \$ttrit, \$tpyer, \$tmexa, \$tisog, 97 price => $basePrice, reprocess => $isk };
81 \$tnocx, \$tzydr, \$tmega, \$tmorp);
82 98
83 my $item = {}; 99 # If this item exists multiple times in the input, then they weren't stacked
84 if ($pre_reprocess->fetch()) 100 # so output it multiple times
85 { 101 my @matching_in = grep({ $_ eq $tname } @item_names);
86 my $isk = ($trit * $ttrit) + ($pyer * $tpyer) + ($mexa * $tmexa) + 102 push @output, $item foreach (0..$#matching_in);
87 ($isog * $tisog) + ($nocx * $tnocx) + ($zydr * $tzydr) +
88 ($mega * $tmega) + ($morp * $tmorp);
89
90 $meta = 0 unless defined $meta;
91 $item = { id => $tid, name => $tname, meta => $meta, icon => $icon,
92 price => $basePrice, reprocess => $isk };
93 }
94 push @output, $item;
95 } 103 }
96 104
97 my $col = 0; 105 my $col = 0;
98 print<<END; 106 print<<END;
99 <html> 107 <html>