Mercurial > hg > reproctool-df
annotate reproctool.cgi @ 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 |
| rev | line source |
|---|---|
| 0 | 1 #!/usr/bin/perl -T |
| 2 | |
| 3 use warnings; | |
| 4 use strict; | |
| 5 | |
| 6 use CGI; | |
| 7 use DBI; | |
| 8 | |
| 9 # Settings | |
| 10 my $img_http_path = '/itemimgs'; | |
| 11 | |
| 12 my $cgi = new CGI; | |
| 13 print $cgi->header(-type => 'text/html', | |
| 14 -pragma => 'no-cache', | |
| 15 -expires => '-365d'); | |
| 16 | |
| 17 # Inputs | |
| 18 my $str_items = $cgi->param('items') or die('Items missing'); | |
| 19 my $cols = $cgi->param('cols') || 4; | |
| 20 | |
| 21 # Load mineral prices | |
| 22 my $trit = $cgi->param('trit') || die('No trit price'); | |
| 23 my $pyer = $cgi->param('pyer') || die('No pyer price'); | |
| 24 my $mexa = $cgi->param('mexa') || die('No mexa price'); | |
| 25 my $isog = $cgi->param('isog') || die('No isog price'); | |
| 26 my $nocx = $cgi->param('nocx') || die('No nocx price'); | |
| 27 my $zydr = $cgi->param('zydr') || die('No zydr price'); | |
| 28 my $mega = $cgi->param('mega') || die('No mega price'); | |
| 29 my $morp = $cgi->param('morp') || die('No morp price'); | |
| 30 | |
|
3
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
31 our @dbparams; |
|
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
32 require './dbparams.cgi'; |
|
1
c39512d94605
Adding dbparams config
Dominic Cleal <dominic@computerkb.co.uk>
parents:
0
diff
changeset
|
33 my $db = DBI->connect(@dbparams) |
| 0 | 34 or die("Database connection failure: $DBI::errstr"); |
| 35 | |
| 36 # Strip out line endings | |
| 37 $str_items =~ s/[\n\r]+//g; | |
| 38 | |
| 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:(.+)/); | |
| 41 $str_items = $1 if ($str_items =~ /(.+)Are you sure you want to continue?/); | |
| 42 | |
|
9
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
43 my @item_names = split(/\s*,\s*/, $str_items); |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
44 |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
45 # SQL fragment to match all items |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
46 my $sql_typenames = ''; |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
47 $sql_typenames = 'types.typeName = ?' if ($#item_names >= 0); |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
48 $sql_typenames .= " OR types.typeName = ?" foreach (1..$#item_names); |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
49 |
| 0 | 50 # SQL lookup for reprocessing amounts |
| 51 my $sql_reprocess = <<END; | |
| 52 SELECT | |
| 53 types.typeID, | |
| 54 types.typeName, | |
|
9
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
55 groups.groupName, |
|
3
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
56 attrs.valueInt, -- metaLevel |
| 0 | 57 types.basePrice, |
| 58 graphics.icon, | |
|
3
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
59 SUM(CASE WHEN m1.requiredTypeID = 34 THEN m1.quantity ELSE 0 END), -- [Tritanium] |
|
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
60 SUM(CASE WHEN m1.requiredTypeID = 35 THEN m1.quantity ELSE 0 END), -- [Pyerite] |
|
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
61 SUM(CASE WHEN m1.requiredTypeID = 36 THEN m1.quantity ELSE 0 END), -- [Mexallon] |
|
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
62 SUM(CASE WHEN m1.requiredTypeID = 37 THEN m1.quantity ELSE 0 END), -- [Isogen] |
|
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
63 SUM(CASE WHEN m1.requiredTypeID = 38 THEN m1.quantity ELSE 0 END), -- [Nocxium] |
|
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
64 SUM(CASE WHEN m1.requiredTypeID = 39 THEN m1.quantity ELSE 0 END), -- [Zydrine] |
|
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
65 SUM(CASE WHEN m1.requiredTypeID = 40 THEN m1.quantity ELSE 0 END), -- [Megacyte] |
|
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
66 SUM(CASE WHEN m1.requiredTypeID = 11399 THEN m1.quantity ELSE 0 END) -- [Morphite] |
| 0 | 67 FROM invTypes types |
| 68 INNER JOIN dgmTypeAttributes attrs ON types.typeID = attrs.typeID AND attrs.attributeID = 633 | |
| 69 INNER JOIN typeActivityMaterials m1 ON types.typeID = m1.typeID | |
|
9
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
70 INNER JOIN invGroups groups ON types.groupID = groups.groupID |
| 0 | 71 INNER JOIN eveGraphics graphics ON types.graphicID = graphics.graphicID |
|
9
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
72 WHERE $sql_typenames |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
73 GROUP BY types.typeID |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
74 ORDER BY groupName ASC, typeName ASC |
| 0 | 75 END |
| 76 my $pre_reprocess = $db->prepare($sql_reprocess); | |
| 77 | |
|
9
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
78 # Execute, bring back one row per item |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
79 my ($tid, $tname, $gname, $meta, $basePrice, $icon, |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
80 $ttrit, $tpyer, $tmexa, $tisog, $tnocx, $tzydr, $tmega, $tmorp); |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
81 |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
82 $pre_reprocess->execute(@item_names) or die("Can't lookup items: $DBI::errstr"); |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
83 $pre_reprocess->bind_columns(undef, \$tid, \$tname, \$gname, \$meta, \$basePrice, |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
84 \$icon, \$ttrit, \$tpyer, \$tmexa, \$tisog, |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
85 \$tnocx, \$tzydr, \$tmega, \$tmorp); |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
86 |
| 0 | 87 my @output = (); |
|
9
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
88 while ($pre_reprocess->fetch()) |
| 0 | 89 { |
| 90 my $item = {}; | |
|
9
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
91 my $isk = ($trit * $ttrit) + ($pyer * $tpyer) + ($mexa * $tmexa) + |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
92 ($isog * $tisog) + ($nocx * $tnocx) + ($zydr * $tzydr) + |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
93 ($mega * $tmega) + ($morp * $tmorp); |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
94 |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
95 $meta = 0 unless defined $meta; |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
96 $item = { id => $tid, name => $tname, meta => $meta, icon => $icon, |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
97 price => $basePrice, reprocess => $isk }; |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
98 |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
99 # If this item exists multiple times in the input, then they weren't stacked |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
100 # so output it multiple times |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
101 my @matching_in = grep({ $_ eq $tname } @item_names); |
|
6b98a17fbe22
Changing to a single SQL query for type-based sorting and efficiency
Dominic Cleal <dominic@computerkb.co.uk>
parents:
7
diff
changeset
|
102 push @output, $item foreach (0..$#matching_in); |
| 0 | 103 } |
| 104 | |
| 105 my $col = 0; | |
| 106 print<<END; | |
| 107 <html> | |
| 108 <head> | |
| 109 <style type="text/css"> | |
| 110 .meta4, .meta5, .meta6, .meta7, .meta8, .meta9 | |
| 111 { | |
|
5
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
112 color: #FF0000; |
| 0 | 113 } |
|
3
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
114 .item |
|
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
115 { |
|
4
4351c0bf33d4
Improving IGB formatting
Dominic Cleal <dominic@computerkb.co.uk>
parents:
3
diff
changeset
|
116 text-align: center; |
|
3
e640eb6cefc3
Making DB stuff more generic, fixing missing mineral
Dominic Cleal <dominic@computerkb.co.uk>
parents:
1
diff
changeset
|
117 } |
| 0 | 118 </style> |
| 119 </head> | |
|
4
4351c0bf33d4
Improving IGB formatting
Dominic Cleal <dominic@computerkb.co.uk>
parents:
3
diff
changeset
|
120 |
| 0 | 121 <body> |
| 122 <table> | |
| 123 END | |
| 124 | |
| 125 for my $item (@output) | |
| 126 { | |
| 127 if ($col == $cols) | |
| 128 { | |
| 129 print "</tr><tr>\n"; | |
| 130 $col = 0; | |
| 131 } | |
| 132 | |
|
5
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
133 my ($style, $img, $text, $link, $colour); |
| 0 | 134 |
| 135 if (defined $item->{id}) | |
| 136 { | |
| 137 $style = "meta$item->{meta}"; | |
|
5
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
138 $img = "typeicon:$item->{id}"; |
|
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
139 $link = "showinfo:$item->{id}"; |
| 0 | 140 if ($item->{meta} == 4) |
| 141 { | |
| 142 $text = $item->{name}; | |
|
5
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
143 $colour = "#FF0000"; |
| 0 | 144 } |
| 145 else | |
| 146 { | |
| 147 $text = $item->{reprocess}; | |
| 148 } | |
| 149 } | |
| 150 else | |
| 151 { | |
| 152 $text = 'Unknown item'; | |
|
5
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
153 $img = "typeicon:07_15"; |
| 0 | 154 } |
| 155 | |
|
5
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
156 print "<td width='64' class='item'>"; |
|
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
157 print "<a href='$link'>" if defined $link; |
|
7
e30154411e63
Even a more cheapy solution, but like this we re at least not changing db data to fit it to the IGB
df
parents:
6
diff
changeset
|
158 print "<img src='$img' width='64' height='64' border='1' alt=\"$item->{name}\" />"; |
|
5
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
159 print "</a>" if defined $link; |
|
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
160 print "<br />"; |
|
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
161 print "<font color='$colour'>" if defined $colour; |
|
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
162 print $text; |
|
42268fc087a9
Improving IGB integration with links and images
Dominic Cleal <dominic@computerkb.co.uk>
parents:
4
diff
changeset
|
163 print "</font>" if defined $colour; |
| 0 | 164 print "</td>\n"; |
| 165 | |
| 166 $col++; | |
| 167 } | |
| 168 | |
| 169 print<<END; | |
| 170 </tr> | |
| 171 </table> | |
| 172 </body> | |
| 173 </html> | |
| 174 END | |
| 175 |