X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=lib%2FNDWeb%2FController%2FIntel.pm;h=0a683c0618a57caa1fbbace638eee97660ec652d;hb=492509a0eb89744961a1d40c25ffdd3b645910d3;hp=f2cd52e44ff555def2d63a0ae39d7d16181410f8;hpb=6e1e01771cd86b3b58f4932aacb0cc496b5c376d;p=ndwebbie.git

diff --git a/lib/NDWeb/Controller/Intel.pm b/lib/NDWeb/Controller/Intel.pm
index f2cd52e..0a683c0 100644
--- a/lib/NDWeb/Controller/Intel.pm
+++ b/lib/NDWeb/Controller/Intel.pm
@@ -180,6 +180,127 @@ 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);
+}
+
+sub naps : Local {
+	my ( $self, $c ) = @_;
+	my $dbh = $c->model;
+
+	my $query = $dbh->prepare(q{SELECT p.id,coords(x,y,z)
+		,ruler, p.planet,race, size, score, value
+		, xp, sizerank, scorerank, valuerank, xprank, p.value - p.size*200 
+			- COALESCE(ps.metal+ps.crystal+ps.eonium,0)/150
+			- COALESCE(ss.total ,(SELECT COALESCE(avg(total),0)
+				FROM structure_scans)::int)*1500 AS fleetvalue
+		,(metal+crystal+eonium)/100 AS resvalue, planet_status,hit_us
+		, alliance,relationship,nick
+		FROM current_planet_stats p
+			LEFT OUTER JOIN planet_scans ps ON p.id = ps.planet
+			LEFT OUTER JOIN structure_scans ss ON p.id = ss.planet
+		WHERE planet_status IN ('Friendly','NAP') order by x,y,z asc
+		});
+	$query->execute;
+	$c->stash(planets => $query->fetchall_arrayref({}) );
+}
 
 =head1 AUTHOR