1 package NDWeb::Controller::Rankings;
5 use parent 'Catalyst::Controller';
11 NDWeb::Controller::Rankings - Catalyst Controller
26 #sub index :Path :Args(0) {
27 # my ( $self, $c ) = @_;
29 # $c->response->body('Matched NDWeb::Controller::Rankings in Rankings.');
33 my ( $self, $c, $order, $offset ) = @_;
38 $offset = 0 unless $offset;
39 $c->detach('/default') if $offset < 0;
40 $c->stash(offset => $offset);
42 $c->stash( comma => \&comma_value);
44 if (defined $order && $order =~ /^(scorerank|sizerank|valuerank|xprank|hit_us)$/){
49 my $browse = qq{ORDER BY $order DESC LIMIT 100 OFFSET ?};
50 if ($order =~ /rank$/){
51 $browse = qq{WHERE $order > ? ORDER BY $order ASC LIMIT 100};
53 $c->stash(order => $order);
55 my $extra_columns = '';
56 if ($c->check_user_roles(qw/rankings_planet_intel/)){
57 $c->stash(extracolumns => 1);
58 $extra_columns = ",planet_status,hit_us, alliance,relationship,nick";
61 my $query = $dbh->prepare(qq{SELECT id,x,y,z,ruler,planet,race,
62 size, size_gain, size_gain_day,
63 score,score_gain,score_gain_day,
64 value,value_gain,value_gain_day,
65 xp,xp_gain,xp_gain_day,
66 sizerank,sizerank_gain,sizerank_gain_day,
67 scorerank,scorerank_gain,scorerank_gain_day,
68 valuerank,valuerank_gain,valuerank_gain_day,
69 xprank,xprank_gain,xprank_gain_day
70 $extra_columns FROM current_planet_stats_full
73 $query->execute($offset);
75 while (my $planet = $query->fetchrow_hashref){
76 push @planets,$planet;
78 $c->detach('/default') unless @planets;
79 $c->stash(planets => \@planets);
82 sub galaxies : Local {
83 my ( $self, $c, $order, $offset ) = @_;
88 $offset = 0 unless $offset;
89 $c->detach('/default') if $offset < 0;
90 $c->stash(offset => $offset);
92 $c->stash( comma => \&comma_value);
94 if (defined $order && $order =~ /^(scorerank|sizerank|valuerank|xprank|planets)$/){
99 $c->stash(order => $order);
101 my $browse = qq{ORDER BY $order DESC LIMIT 100 OFFSET ?};
102 if ($order =~ /rank$/){
103 $browse = qq{AND $order > ? ORDER BY $order ASC LIMIT 100};
105 my $query = $dbh->prepare(qq{SELECT x,y,
106 size, size_gain, size_gain_day,
107 score,score_gain,score_gain_day,
108 value,value_gain,value_gain_day,
109 xp,xp_gain,xp_gain_day,
110 sizerank,sizerank_gain,sizerank_gain_day,
111 scorerank,scorerank_gain,scorerank_gain_day,
112 valuerank,valuerank_gain,valuerank_gain_day,
113 xprank,xprank_gain,xprank_gain_day,
114 planets,planets_gain,planets_gain_day
116 WHERE tick = ( SELECT max(tick) AS max FROM galaxies)
119 $query->execute($offset);
121 while (my $galaxy = $query->fetchrow_hashref){
122 push @galaxies,$galaxy;
124 $c->detach('/default') unless @galaxies;
125 $c->stash(galaxies => \@galaxies);
129 sub alliances : Local {
130 my ( $self, $c, $order, $offset ) = @_;
135 $offset = 0 unless $offset;
136 $c->detach('/default') if $offset < 0;
137 $c->stash(offset => $offset);
139 $c->stash( comma => \&comma_value);
141 if (defined $order && $order =~ /^(scorerank|sizerank|valuerank|xprank|avgsize|avgscore|members)$/){
144 $order = 'scorerank';
146 $c->stash(order => $order);
148 my $browse = qq{ORDER BY $order DESC LIMIT 100 OFFSET ?};
149 if ($order =~ /rank$/){
150 $browse = qq{WHERE $order > ? ORDER BY $order ASC LIMIT 100};
152 my $query = $dbh->prepare(qq{SELECT a.name,a.id,
153 size, size_gain, size_gain_day,
154 score,score_gain,score_gain_day,
155 avgsize,avgsize_gain,avgsize_gain_day,
156 avgscore,avgscore_gain,avgscore_gain_day,
157 sizerank,sizerank_gain,sizerank_gain_day,
158 scorerank,scorerank_gain,scorerank_gain_day,
159 members,members_gain,members_gain_day
160 FROM ( SELECT *, (size/members) AS avgsize
161 ,(score/scoremem) AS avgscore
162 ,(size_gain/members) AS avgsize_gain
163 ,(score_gain/scoremem) AS avgscore_gain
164 ,(size_gain_day/members) AS avgsize_gain_day
165 ,(score_gain_day/scoremem) AS avgscore_gain_day
166 FROM (SELECT *,(CASE WHEN members > 60 THEN 60 ELSE members END) AS scoremem
167 FROM alliance_stats WHERE
168 tick = ( SELECT max(tick) AS max FROM alliance_stats)) ast2
170 NATURAL JOIN alliances a
173 $query->execute($offset);
175 while (my $alliance = $query->fetchrow_hashref){
176 push @alliances,$alliance;
178 $c->detach('/default') unless @alliances;
179 $c->stash(alliances => \@alliances);
184 Michael Andreen (harv@ruin.nu)