X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=lib%2FNDWeb%2FController%2FAlliances.pm;h=70c6340419e077844d1f4fd531e6d9eeabc0d37d;hb=1d5acf5ba24af54ebce139cddd4aa720847357f5;hp=bd25d9bd2a051ca245610db8079140699b69c996;hpb=c5850fd2283ea0188583075f0e3afdb248307c8c;p=ndwebbie.git

diff --git a/lib/NDWeb/Controller/Alliances.pm b/lib/NDWeb/Controller/Alliances.pm
index bd25d9b..70c6340 100644
--- a/lib/NDWeb/Controller/Alliances.pm
+++ b/lib/NDWeb/Controller/Alliances.pm
@@ -4,8 +4,6 @@ use strict;
 use warnings;
 use parent 'Catalyst::Controller';
 
-use NDWeb::Include;
-
 =head1 NAME
 
 NDWeb::Controller::Alliances - Catalyst Controller
@@ -40,7 +38,7 @@ sub list : Local {
 		$order = "score DESC";
 	}
 	my $query = $dbh->prepare(q{
-		SELECT DISTINCT a.id,name,COALESCE(s.score,SUM(p.score)) AS score
+		SELECT aid AS id,alliance AS name,COALESCE(s.score,SUM(p.score)) AS score
 			,COALESCE(s.size,SUM(p.size)) AS size,s.members,count(p.score) AS kmem
 			,COALESCE(SUM(p.score),-1) AS kscore
 			,COALESCE(SUM(p.size),-1) AS ksize
@@ -54,22 +52,21 @@ sub list : Local {
 			,COALESCE(AVG(p.value)::int,-1) AS kvalavg
 		FROM alliances a
 			LEFT OUTER JOIN (SELECT * FROM alliance_stats
-				WHERE tick = (SELECT max(tick) FROM alliance_stats)) s ON s.id = a.id
-			LEFT OUTER JOIN current_planet_stats p ON p.alliance_id = a.id
-		GROUP BY a.id,a.name,s.score,s.size,s.members
+				WHERE tick = (SELECT max(tick) FROM alliance_stats)) s USING (aid)
+			LEFT OUTER JOIN current_planet_stats p USING (alliance,aid)
+		GROUP BY aid,alliance,s.score,s.size,s.members
 		HAVING s.score IS NOT NULL OR count(p.score) > 0
 		ORDER BY
 		} . $order);
 	$query->execute;
 	$c->stash(alliances => $query->fetchall_arrayref({}) );
-	$c->stash(comma => \&comma_value);
 }
 
 sub edit : Local {
 	my ( $self, $c, $id, $order ) = @_;
 	my $dbh = $c->model;
 
-	my $query = $dbh->prepare(q{SELECT id,name, relationship FROM alliances WHERE id = ?});
+	my $query = $dbh->prepare(q{SELECT aid AS id,alliance AS name, relationship FROM alliances WHERE aid = ?});
 	my $a = $dbh->selectrow_hashref($query,undef,$id);
 	$c->stash(a => $a);
 
@@ -84,65 +81,85 @@ sub edit : Local {
 	$order .= ' DESC' if $order eq 'hit_us';
 
 	my $members = $dbh->prepare(q{
-		SELECT id, coords(x,y,z), nick, ruler, planet, race, size, score, value, xp
+		SELECT pid AS id, coords(x,y,z), nick, ruler, planet, race, size, score, value, xp
 			,planet_status,hit_us, sizerank, scorerank, valuerank, xprank
 		FROM current_planet_stats p
-		WHERE p.alliance_id = ?
+		WHERE p.alliance = ?
 		ORDER BY
 		} . $order);
-	$members->execute($a->{id});
+	$members->execute($a->{name});
 	$c->stash(members => $members->fetchall_arrayref({}) );
 
 	my $ticks = $c->req->param('ticks') || 48;
 	$c->stash(showticks => $ticks);
 
-	$query = $dbh->prepare(intelquery q{
-			o.alliance AS oalliance ,coords(o.x,o.y,o.z) AS ocoords, i.sender
-			,t.alliance AS talliance,coords(t.x,t.y,t.z) AS tcoords, i.target
-		},q{NOT ingal AND (o.alliance_id = $1 OR t.alliance_id = $1)
-			AND (i.mission = 'Defend' OR i.mission = 'AllyDef')
-			AND ((( t.alliance_id != o.alliance_id OR t.alliance_id IS NULL OR o.alliance_id IS NULL)))
-			AND i.sender NOT IN (SELECT planet FROM users u NATURAL JOIN groupmembers gm WHERE gid = 8 AND planet IS NOT NULL)
-			AND NOT (i.back IS NOT NULL AND i.back = i.tick + 4)
-			AND i.tick > (tick() - $2)
+	$query = $dbh->prepare(q{
+SELECT salliance, scoords, sender, talliance, tcoords, target
+	,mission, tick AS landingtick, eta, amount, ingal, username
+FROM full_intel
+WHERE NOT ingal AND (salliance = $1 OR talliance = $1)
+	AND (mission = 'Defend' OR mission = 'AllyDef')
+	AND COALESCE( talliance <> salliance, TRUE)
+	AND tick > (tick() - $2)
+ORDER BY tick DESC, mission
 		});
-	$query->execute($a->{id}, $ticks);
+	$query->execute($a->{name}, $ticks);
 	$c->stash(intel => $query->fetchall_arrayref({}) );
 }
 
+
+sub pscans : Local {
+	my ( $self, $c, $id ) = @_;
+	my $dbh = $c->model;
+
+	my $members = $dbh->prepare(q{
+		SELECT pid AS id, coords(x,y,z), metal, crystal, eonium, ps.tick
+			,planet_status,hit_us, sizerank, scorerank, valuerank, xprank
+		FROM current_planet_stats p left outer join current_planet_scans ps using (pid)
+		WHERE p.aid = ?
+		ORDER BY x,y,z
+		});
+	$members->execute($id);
+	$c->stash(members => $members->fetchall_arrayref({}) );
+
+}
+
 sub postallianceupdate : Local {
 	my ( $self, $c, $id, $order ) = @_;
 	my $dbh = $c->model;
 
+	my $query = $dbh->prepare(q{SELECT aid,alliance, relationship FROM alliances WHERE aid = ?});
+	my $a = $dbh->selectrow_hashref($query,undef,$id);
+
 	$dbh->begin_work;
 	my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
 		(SELECT ftid FROM users WHERE uid = $1),$1,$2)
 		});
 	if ($c->req->param('crelationship')){
 		my $value = $c->req->param('relationship');
-		$dbh->do(q{UPDATE alliances SET relationship = ? WHERE id =?}
+		$dbh->do(q{UPDATE alliances SET relationship = ? WHERE aid =?}
 			,undef,$value,$id);
 		$log->execute($c->user->id
-			,"HC set alliance: $id relationship: $value");
+			,"HC set alliance: $a->{alliance} ($id) relationship: $value");
 	}
 	my $coords = $c->req->param('coords');
-	my $findplanet = $dbh->prepare(q{SELECT id FROM current_planet_stats
+	my $findplanet = $dbh->prepare(q{SELECT pid FROM current_planet_stats
 		WHERE x = ? AND y = ? AND z = ?});
 	my $addplanet = $dbh->prepare(q{
-		UPDATE planets SET alliance_id = $2, nick = coalesce($3,nick)
-		WHERE id = $1;
+		UPDATE planets SET alliance = $2, nick = coalesce($3,nick)
+		WHERE pid = $1;
 		});
 	my $text = '';
 	while ($coords =~ m/(\d+):(\d+):(\d+)(?:\s+nick=(\S+))?/g){
 		my ($planet) = $dbh->selectrow_array($findplanet,undef,$1,$2,$3);
-		$addplanet->execute($planet,$id,$4);
+		$addplanet->execute($planet,$a->{alliance},$4);
 		my $nick = '';
 		$nick = "(nick $4)" if defined $4;
 		$text .= "($planet) $1:$2:$3 $nick\n";
 	}
 	if ($text){
 		$log->execute($c->user->id
-			,"HC added the following planets to alliance $id:\n $text");
+			,"HC added the following planets to alliance $a->{alliance} ($id):\n $text");
 	}
 	$dbh->commit;
 
@@ -153,20 +170,23 @@ sub postremoveallplanets : Local {
 	my ( $self, $c, $id, $order ) = @_;
 	my $dbh = $c->model;
 
+	my $query = $dbh->prepare(q{SELECT aid,alliance, relationship FROM alliances WHERE aid = ?});
+	my $a = $dbh->selectrow_hashref($query,undef,$id);
+
 	$dbh->begin_work;
 	my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
 		(SELECT ftid FROM users WHERE uid = $1),$1,$2)
 		});
-	my ($coords) = $dbh->selectrow_array(q{SELECT CONCAT(coords(x,y,z) || ' ') 
-			FROM current_planet_stats where alliance_id = $1
-		},undef,$id);
+	my ($coords) = $dbh->selectrow_array(q{SELECT array_to_string(array_agg(coords(x,y,z)),' ')
+			FROM current_planet_stats where alliance = $1
+		},undef,$a->{alliance});
 	my $removeplanets = $dbh->prepare(q{
-		UPDATE planets SET alliance_id = NULL
-		WHERE alliance_id = $1;
+		UPDATE planets SET alliance = NULL
+		WHERE alliance = $1;
 	});
-	$removeplanets->execute($id);
+	$removeplanets->execute($a->{alliance});
 	$log->execute($c->user->id
-		,"HC cleaned alliance $id :\n\n$coords");
+		,"HC cleaned alliance $a->{alliance} (id) :\n\n$coords");
 	$dbh->commit;
 
 	$c->res->redirect($c->uri_for('edit',$id));
@@ -186,12 +206,8 @@ sub hostile : Local {
 	}
 
 	my $query = $dbh->prepare(q{
-		SELECT s.alliance_id AS id,s.alliance AS name,count(*) AS hostile_count
-FROM calls c 
-	JOIN incomings i ON i.call = c.id
-	JOIN current_planet_stats s ON i.sender = s.id
-WHERE c.landing_tick - i.eta > $1 and c.landing_tick - i.eta < $2
-GROUP BY s.alliance_id,s.alliance
+SELECT aid, alliance, hostile_count, targeted, targeted_raids
+FROM hostile_alliances($1,$2)
 ORDER BY hostile_count DESC
 		});
 	$query->execute($begintick,$endtick);
@@ -211,32 +227,17 @@ sub resources : Local {
 		$order = "resplanet DESC";
 	}
 
-	my $query = $dbh->prepare(qq{
-		SELECT a.id,a.name,a.relationship,s.members,s.score,s.size
-		,r.resources,r.hidden,r.planets
-		,(resources/planets)::bigint AS resplanet
-		,(hidden/planets)::bigint AS hidplanet
-		,((resources / 300) + (hidden / 100))::bigint AS scoregain
-		,(score + (resources / 300) + (hidden / 100))::bigint AS nscore
-		,((resources/planets*scoremem)/300 + (hidden/planets*scoremem)/100)::bigint AS scoregain2
-		,(score + (resources/planets*scoremem)/300
-			+ (hidden/planets*scoremem)/100)::bigint AS nscore2
-		,((s.size::int8*(1400-tick())*250)/100 + score + (resources/planets*scoremem)/300
-			+ (hidden/planets*scoremem)/100)::bigint AS nscore3
-		,(s.size::int8*(1400-tick())*250)/100 AS scoregain3
-		FROM (SELECT alliance_id AS id,sum(metal+crystal+eonium) AS resources, sum(hidden) AS hidden, count(*) AS planets 
-		FROM planets p join planet_scans c ON p.id = c.planet GROUP by alliance_id) r 
-			NATURAL JOIN alliances a 
-			LEFT OUTER JOIN (SELECT *,LEAST(members,60) AS scoremem FROM alliance_stats
-				WHERE tick = (SELECT max(tick) FROM alliance_stats)) s ON a.id = s.id
-		ORDER BY $order
-		});
+	my $query = $dbh->prepare(q{
+SELECT aid AS id,alliance AS name,relationship,members,score,size
+	,resources,hidden,planets
+	,(resources/planets)::bigint AS resplanet
+	,(hidden/planets)::bigint AS hidplanet
+	, nscore, nscore2, nscore3
+FROM alliance_resources
+ORDER BY } . $order
+	);
 	$query->execute;
-	my @alliances;
-	while (my $alliance = $query->fetchrow_hashref){
-		push @alliances,$alliance;
-	}
-	$c->stash(alliances => \@alliances);
+	$c->stash(alliances => $query->fetchall_arrayref({}));
 }