Converted memberIntel

[ndwebbie.git] / lib / NDWeb / Controller / Intel.pm

diff --git a/lib/NDWeb/Controller/Intel.pm b/lib/NDWeb/Controller/Intel.pm
index f2cd52e44ff555def2d63a0ae39d7d16181410f8..46fe6ce79deaa147f0e326e6904489c5e2a5d69e 100644 (file)
--- a/lib/NDWeb/Controller/Intel.pm
+++ b/lib/NDWeb/Controller/Intel.pm
@@ -180,6 +180,107 @@ sub findPlanet : Private {
        $c->stash(p => $query->fetchrow_hashref);
 }
 
+sub members : Local {
+       my ( $self, $c, $order ) = @_;
+       my $dbh = $c->model;
+
+       if (defined $order && $order =~ /^(attacks|defenses|attack_points|defense_points
+                       |solo|bad_def)$/x){
+               $order = $1;
+       }else{
+               $order = 'attacks';
+       }
+       my $query = $dbh->prepare(q{SELECT u.uid,u.username,u.attack_points, u.defense_points, n.tick
+               ,count(CASE WHEN i.mission = 'Attack' THEN 1 ELSE NULL END) AS attacks
+               ,count(CASE WHEN (i.mission = 'Defend' OR i.mission = 'AllyDef') THEN 1 ELSE NULL END) AS defenses
+               ,count(CASE WHEN i.mission = 'Attack' AND rt.id IS NULL THEN 1 ELSE NULL END) AS solo
+               ,count(CASE WHEN i.mission = 'Defend' OR i.mission = 'AllyDef' THEN NULLIF(i.ingal OR (t.alliance_id = 1),TRUE) ELSE NULL END) AS bad_def
+               FROM users u
+               JOIN groupmembers gm USING (uid)
+               LEFT OUTER JOIN (SELECT DISTINCT ON (planet) planet,tick from scans where type = 'News' ORDER BY planet,tick DESC) n USING (planet)
+               LEFT OUTER JOIN (SELECT DISTINCT name,eta,tick,sender,target,mission,ingal FROM fleets WHERE amount IS NULL) i ON i.sender = u.planet
+               LEFT OUTER JOIN current_planet_stats t ON i.target = t.id
+               LEFT OUTER JOIN (SELECT rt.id,planet,tick FROM raids r 
+                               JOIN raid_targets rt ON r.id = rt.raid) rt ON rt.planet = i.target 
+                       AND (rt.tick + 12) > i.tick AND rt.tick <= i.tick 
+               LEFT OUTER JOIN raid_claims rc ON rt.id = rc.target AND rc.uid = u.uid AND i.tick = rt.tick + rc.wave - 1
+               WHERE gm.gid = 2
+               GROUP BY u.uid,u.username,u.attack_points, u.defense_points,n.tick
+               ORDER BY }. " $order DESC" );
+       $query->execute;
+       $c->stash(members => $query->fetchall_arrayref({}) );
+}
+
+sub member : Local {
+       my ( $self, $c, $uid ) = @_;
+       my $dbh = $c->model;
+
+       my $query = $dbh->prepare(q{
+               SELECT coords(t.x,t.y,t.z), i.eta, i.tick, rt.id AS ndtarget, rc.launched, inc.landing_tick
+               FROM users u
+               LEFT OUTER JOIN (SELECT DISTINCT eta,tick,sender,target,mission,name FROM fleets WHERE amount IS NULL) i ON i.sender = u.planet
+               LEFT OUTER JOIN current_planet_stats t ON i.target = t.id
+               LEFT OUTER JOIN (SELECT rt.id,planet,tick FROM raids r 
+                               JOIN raid_targets rt ON r.id = rt.raid) rt ON rt.planet = i.target 
+                       AND (rt.tick + 12) > i.tick AND rt.tick <= i.tick 
+               LEFT OUTER JOIN raid_claims rc ON rt.id = rc.target AND rc.uid = u.uid AND i.tick = rt.tick + rc.wave - 1
+               LEFT OUTER JOIN (SELECT sender, eta, landing_tick FROM calls c 
+                                       JOIN incomings i ON i.call = c.id) inc ON inc.sender = i.target 
+                               AND (inc.landing_tick + inc.eta) >= i.tick 
+                               AND (inc.landing_tick - inc.eta - 1) <= (i.tick - i.eta) 
+               WHERE u.uid = $1 AND i.mission = 'Attack'
+               ORDER BY (i.tick - i.eta)
+               });
+       $query->execute($uid);
+       my @nd_attacks;
+       my @other_attacks;
+       while (my $intel = $query->fetchrow_hashref){
+               my $attack = {target => $intel->{coords}, tick => $intel->{tick}};
+               if ($intel->{ndtarget}){
+                       if (defined $intel->{launched}){
+                               $attack->{other} = 'Claimed '.($intel->{launched} ? 'and confirmed' : 'but NOT confirmed');
+                       }else{
+                               $attack->{other} = 'Launched at a tick that was not claimed';
+                       }
+                       push @nd_attacks, $attack;
+               }else{
+                       push @other_attacks, $attack;
+               }
+       }
+       my @attacks;
+       push @attacks, {name => 'ND Attacks', missions => \@nd_attacks, class => 'AllyDef'};
+       push @attacks, {name => 'Other', missions => \@other_attacks, class => 'Attack'};
+       $c->stash(attacks => \@attacks);
+
+       $query = $dbh->prepare(q{
+               SELECT coords(t.x,t.y,t.z),t.alliance_id, t.alliance, i.eta, i.tick, i.ingal
+               FROM users u
+               JOIN (SELECT DISTINCT name,eta,tick,sender,target,mission,ingal FROM fleets WHERE amount IS NULL) i ON i.sender = u.planet
+               LEFT OUTER JOIN current_planet_stats t ON i.target = t.id
+               WHERE u.uid = $1 AND (i.mission = 'Defend' OR i.mission = 'AllyDef')
+               ORDER BY (i.tick - i.eta)
+               });
+       $query->execute($uid);
+       my @nd_def;
+       my @ingal_def;
+       my @other_def;
+       while (my $intel = $query->fetchrow_hashref){
+               my $def = {target => $intel->{coords}, other => $intel->{alliance}, tick => $intel->{tick}};
+               if (defined $intel->{alliance_id} && $intel->{alliance_id} == 1){
+                       push @nd_def, $def;
+               }elsif($intel->{ingal}){
+                       push @ingal_def, $def;
+               }else{
+                       push @other_def, $def;
+               }
+       }
+       my @defenses;
+       push @defenses, {name => 'ND Def', missions => \@nd_def, class => 'AllyDef'};
+       push @defenses, {name => 'Ingal Def', missions => \@ingal_def, class => 'Defend'};
+       push @defenses, {name => 'Other', missions => \@other_def, class => 'Attack'};
+       $c->stash(defenses => \@defenses);
+}
+
 
 =head1 AUTHOR