X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=lib%2FNDWeb%2FController%2FMembers.pm;h=440c51f47021a778f690c3e5a1400b6c23fc83e8;hb=93a978b42c10736ee4cda392001aea0adb351958;hp=8c44e056590cb0e5832c4ab3281c1f79a79b3a71;hpb=5b6c3c7d0003d636c1c3da6d25734e4aad5736a5;p=ndwebbie.git

diff --git a/lib/NDWeb/Controller/Members.pm b/lib/NDWeb/Controller/Members.pm
index 8c44e05..440c51f 100644
--- a/lib/NDWeb/Controller/Members.pm
+++ b/lib/NDWeb/Controller/Members.pm
@@ -32,8 +32,9 @@ sub index : Path : Args(0) {
 	$c->stash(comma => \&comma_value);
 	$c->stash(u => $dbh->selectrow_hashref(q{SELECT planet,defense_points
 			,attack_points,scan_points,humor_points
-			, (attack_points+defense_points+scan_points/20) as total_points
-			, sms,rank,hostmask FROM users WHERE uid = ?
+			, (attack_points+defense_points+scan_points/20)::NUMERIC(5,1) as total_points
+			, sms,rank,hostmask,call_if_needed,sms_note
+		FROM users WHERE uid = ?
 			},undef,$c->user->id)
 	);
 
@@ -82,32 +83,7 @@ sub index : Path : Args(0) {
 	$calls->execute($c->user->id);
 	$c->stash(calls => $calls->fetchall_arrayref({}) );
 
-	my $query = $dbh->prepare(q{SELECT f.id, coords(x,y,z), target, mission
-		, f.amount, tick, back
-FROM fleets f
-LEFT OUTER JOIN current_planet_stats p ON f.target = p.id
-WHERE NOT ingal AND f.uid = ? AND f.sender = ? AND
-	(back >= ? OR (tick >= tick() -  24 AND name = 'Main'))
-GROUP BY f.id, x,y,z, mission, tick,back,f.amount,f.target
-ORDER BY x,y,z,mission,tick
-		});
-
-	my $ships = $dbh->prepare(q{SELECT ship,amount FROM fleet_ships
-		WHERe id = ? ORDER BY num
-		});
-
-	$query->execute($c->user->id,$c->user->planet,$c->stash->{TICK});
-	my @fleets;
-	while (my $fleet = $query->fetchrow_hashref){
-		my @ships;
-		$ships->execute($fleet->{id});
-		while (my $ship = $ships->fetchrow_hashref){
-			push @ships,$ship;
-		}
-		$fleet->{ships} = \@ships;
-		push @fleets,$fleet;
-	}
-	$c->stash(fleets => \@fleets);
+	$c->stash(fleets => member_fleets($dbh, $c->user->id,$c->user->planet));
 
 	my $announcements = $dbh->prepare(q{SELECT ft.ftid, u.username,ft.subject,
 		count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts,
@@ -139,8 +115,35 @@ sub postsmsupdate : Local {
 	my ( $self, $c ) = @_;
 	my $dbh = $c->model;
 
-	$dbh->do(q{UPDATE users SET sms = ? WHERE uid = ?
-		},undef, html_escape $c->req->param('sms'), $c->user->id);
+	my $callme = $c->req->param('callme') || 0;
+	$dbh->do(q{
+UPDATE users SET sms = $1, call_if_needed =  $2, sms_note = $3 WHERE uid = $4
+		},undef, html_escape $c->req->param('sms'),$callme
+		,$c->req->param('smsnote'), $c->user->id);
+
+	$c->res->redirect($c->uri_for(''));
+}
+
+sub postowncoords : Local {
+	my ( $self, $c ) = @_;
+	my $dbh = $c->model;
+
+	if ($c->user->planet){
+		$c->flash(error => 'You already have a planet set.'
+			.' Contact a HC if they need to be changed');
+	}elsif ($c->req->param('planet') =~ m/(\d+)\D+(\d+)\D+(\d+)/){
+		my $planet = $dbh->selectrow_array(q{SELECT planetid($1,$2,$3,TICK())
+			},undef,$1,$2,$3);
+
+		if ($planet){
+			$dbh->do(q{UPDATE users SET planet = ? WHERE uid = ?
+				},undef, $planet , $c->user->id);
+		}else{
+			$c->flash(error => "No planet at coords: $1:$2:$3");
+		}
+	}else{
+		$c->flash(error => $c->req->param('planet') . " are not valid coords.");
+	}
 
 	$c->res->redirect($c->uri_for(''));
 }
@@ -161,16 +164,19 @@ sub postfleetupdate : Local {
 		$dbh->begin_work;
 		eval{
 			my $insert = $dbh->prepare(q{INSERT INTO fleets
-				(uid,sender,name,mission,tick,amount)
-				VALUES (?,?,'Main','Full fleet',tick(),?) RETURNING id});
-			my ($id) = $dbh->selectrow_array($insert,undef,$c->user->id
+				(planet,name,mission,tick,amount)
+				VALUES (?,'Main','Full fleet',tick(),?) RETURNING fid});
+			my ($id) = $dbh->selectrow_array($insert,undef
 				,$c->user->planet,$amount);
 			$insert = $dbh->prepare(q{INSERT INTO fleet_ships
-				(id,ship,amount) VALUES (?,?,?)});
+				(fid,ship,amount) VALUES (?,?,?)});
 			for my $s (@ships){
 				unshift @{$s},$id;
 				$insert->execute(@{$s});
 			}
+			$insert = $dbh->prepare(q{INSERT INTO full_fleets
+				(fid,uid) VALUES (?,?)});
+			$insert->execute($id,$c->user->id);
 			$dbh->commit;
 		};
 		if ($@){
@@ -197,9 +203,9 @@ sub postfleetsupdates : Local {
 		});
 	$dbh->begin_work;
 	if ($c->req->param('cmd') eq 'Recall Fleets'){
-		my $updatefleets = $dbh->prepare(q{UPDATE fleets
-			SET back = tick() + (tick() - (tick - eta))
-			WHERE uid = ? AND id = ? AND back > tick()+eta
+		my $updatefleets = $dbh->prepare(q{UPDATE launch_confirmations
+			SET back = tick() + (tick() - (landing_tick - eta))
+			WHERE uid = ? AND fid = ? AND back > tick()+eta
 		});
 
 		for my $param ($c->req->param()){
@@ -209,8 +215,8 @@ sub postfleetsupdates : Local {
 			}
 		}
 	}elsif ($c->req->param('cmd') eq 'Change Fleets'){
-		my $updatefleets = $dbh->prepare(q{UPDATE fleets
-			SET back = ? WHERE uid = ? AND id = ?});
+		my $updatefleets = $dbh->prepare(q{UPDATE launch_confirmations
+			SET back = ? WHERE uid = ? AND fid = ?});
 
 		for my $param ($c->req->param()){
 			if ($param =~ /^change:(\d+)$/){
@@ -241,6 +247,7 @@ sub postircrequest : Local {
 		(uid,channel,message) VALUES($1,$2,$3)
 		});
 	$query->execute($c->user->id,$c->req->param('channel'),$c->req->param('message'));
+	system 'killall','-USR1', 'irssi';
 
 	$c->flash(reply => "Msg sent to: ".$c->req->param('channel'));
 	$c->res->redirect($c->uri_for('ircrequest'));
@@ -261,7 +268,7 @@ sub points : Local {
 
 	my $query = $dbh->prepare(qq{SELECT username,defense_points,attack_points
 		,scan_points,humor_points
-		,(attack_points+defense_points+scan_points/20) as total_points
+		,(attack_points+defense_points+scan_points/20)::NUMERIC(4,0) as total_points
 		, count(NULLIF(rc.launched,FALSE)) AS raid_points
 		FROM users u LEFT OUTER JOIN raid_claims rc USING (uid)
 		WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 2)
@@ -315,17 +322,17 @@ sub insertintel : Private {
 
 	$dbh->begin_work;
 	my $findscan = $dbh->prepare(q{SELECT scan_id FROM scans
-		WHERE scan_id = ? AND tick >= tick() - 168 AND groupscan = ?
+		WHERE scan_id = LOWER(?) AND tick >= tick() - 168 AND groupscan = ?
 		});
 	my $addscan = $dbh->prepare(q{INSERT INTO scans (scan_id,tick,uid,groupscan)
-		VALUES (?,tick(),?,?)
+		VALUES (LOWER(?),tick(),?,?)
 		});
 	my $addpoint = $dbh->prepare(q{UPDATE users SET scan_points = scan_points + 1
 		WHERE uid = ?
 		});
 	my @scans;
 	my $intel = $c->req->param('message');
-	while ($intel =~ m{http://[\w.]+/.+?scan(_id|_grp)?=(\d+)}g){
+	while ($intel =~ m{http://[\w.]+/.+?scan(_id|_grp)?=(\w+)}g){
 		my $groupscan = (defined $1 && $1 eq '_grp') || 0;
 		my %scan;
 		$scan{id} = $2;
@@ -345,7 +352,7 @@ sub insertintel : Private {
 	unless ($tick =~ /^(\d+)$/){
 		$tick = $c->stash->{game}->{tick};
 	}
-	my $addintel = $dbh->prepare(q{INSERT INTO fleets 
+	my $addintel = $dbh->prepare(q{INSERT INTO intel
 		(name,mission,tick,target,sender,eta,amount,ingal,back,uid)
 		VALUES($1,$2,$3,planetid($4,$5,$6,$10),planetid($7,$8,$9,$10)
 			,$11,$12,$13,$14,$15)
@@ -374,6 +381,7 @@ sub insertintel : Private {
 sub launchConfirmation : Local {
 	my ( $self, $c ) = @_;
 
+	$c->stash(error => $c->flash->{error});
 	$c->stash(missions => $c->flash->{missions});
 }
 
@@ -405,11 +413,14 @@ sub postconfirmation : Local {
 			WHERE uid = ? AND target = ? AND wave = ?
 			});
 		my $addfleet = $dbh->prepare(q{INSERT INTO fleets
-			(uid,name,mission,sender,target,tick,eta,back,amount)
-			VALUES ($1,$2,$3,(SELECT planet FROM users WHERE uid = $1),$4,$5,$6,$7,$8)
-			RETURNING id
+			(name,mission,planet,tick,amount)
+			VALUES ($2,$3,(SELECT planet FROM users WHERE uid = $1),tick(),$4)
+			RETURNING fid
+			});
+		my $addconfirmation = $dbh->prepare(q{INSERT INTO launch_confirmations
+			(fid,uid,target,landing_tick,eta,back) VALUES ($1,$2,$3,$4,$5,$6)
 			});
-		my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (id,ship,amount)
+		my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (fid,ship,amount)
 			VALUES (?,?,?)
 			});
 		my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
@@ -430,17 +441,14 @@ sub postconfirmation : Local {
 			my $name = $1;
 			my $tick = $c->stash->{TICK}+$9;
 			$tick += $8 if defined $8;
+			$tick = $13 if defined $13;
 			my $eta = $9;
+			$eta += $14 if defined $14;
 			my $mission = $10;
 			my $x = $5;
 			my $y = $6;
 			my $z = $7;
 			my $back = $tick + $eta - 1;
-			if ($13){
-				$tick = $13;
-			}elsif ($14){
-				$back += $14;
-			}
 			$mission{tick} = $tick;
 			$mission{mission} = $mission;
 			$mission{target} = "$x:$y:$z";
@@ -470,8 +478,9 @@ sub postconfirmation : Local {
 				warn "No ships in: $ships";
 				next;
 			}
-			my $fleet = $dbh->selectrow_array($addfleet,undef,$c->user->id,$name,$mission
-				,$planet_id,$tick,$eta,$back,$amount);
+			my $fleet = $dbh->selectrow_array($addfleet,undef,$c->user->id,$name
+				,$mission,$amount);
+			$addconfirmation->execute($fleet,$c->user->id,$planet_id,$tick,$eta,$back);
 			$mission{fleet} = $fleet;
 			for my $ship (@ships){
 				$addships->execute($fleet,$ship->{ship},$ship->{amount});
@@ -502,12 +511,92 @@ sub postconfirmation : Local {
 	};
 	if ($@){
 		$dbh->rollback;
-		die $@;
+		if ($@ =~ m/insert or update on table "fleet_ships" violates foreign key constraint "fleet_ships_ship_fkey"\s+DETAIL:\s+Key \(ship\)=\(([^)]+)\)/){
+			$c->flash( error => "'$1' is NOT a valid ship");
+		}else{
+			$c->flash( error => $@);
+		}
 	}
 
 	$c->res->redirect($c->uri_for('launchConfirmation'));
 }
 
+sub defenders : Local {
+	my ( $self, $c, $order ) = @_;
+	my $dbh = $c->model;
+
+	my $defenders = $dbh->prepare(q{
+SELECT uid,u.planet,username, to_char(NOW() AT TIME ZONE timezone,'HH24:MI') AS time
+	,sms_note, call_if_needed, race, timezone
+FROM users u
+	JOIN current_planet_stats p ON p.id = u.planet
+WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 2)
+ORDER BY call_if_needed DESC, LOWER(username)
+		});
+	$defenders->execute;
+
+	my $available = $dbh->prepare(q{
+SELECT ship,amount FROM available_ships WHERE planet = $1
+		});
+
+	my @members;
+	while (my $member = $defenders->fetchrow_hashref){
+
+		$member->{fleets} = member_fleets($dbh, $member->{uid}, $member->{planet});
+		$available->execute($member->{planet});
+		my $fleet = {fid => $member->{username}, mission => 'Available', name => 'At home'
+			, ships => $available->fetchall_arrayref({})
+		};
+		push @{$member->{fleets}}, $fleet;
+		push @members,$member;
+	}
+	$c->stash(members => \@members);
+}
+
+sub member_fleets {
+	my ( $dbh, $uid, $planet ) = @_;
+
+	my $query = $dbh->prepare(q{
+(
+	SELECT DISTINCT ON (mission,name) fid,name,tick, NULL AS eta
+		,amount, NULL AS coords, planet AS target, NULL AS back
+		,NULL AS recalled, mission
+	FROM fleets f
+	WHERE planet = $2 AND tick <= tick() AND tick >= tick() -  24
+		AND name IN ('Main','Advanced Unit') AND mission = 'Full fleet'
+	ORDER BY mission,name,tick DESC, fid DESC
+) UNION (
+	SELECT fid,name,landing_tick AS tick, eta, amount
+		, coords(x,y,z), target, back
+		, (back <> landing_tick + eta - 1) AS recalled
+		,CASE WHEN landing_tick <= tick() OR (back <> landing_tick + eta - 1)
+			THEN 'Returning' ELSE mission END AS mission
+	FROM fleets f
+		JOIN launch_confirmations USING (fid)
+	LEFT OUTER JOIN current_planet_stats t ON target = t.id
+	WHERE uid = $1 AND f.planet = $2 AND back > tick()
+		AND landing_tick - eta - 12 < tick()
+)
+		});
+
+	my $ships = $dbh->prepare(q{SELECT ship,amount FROM fleet_ships
+		WHERE fid = ? ORDER BY num
+		});
+
+	$query->execute($uid,$planet);
+	my @fleets;
+	while (my $fleet = $query->fetchrow_hashref){
+		my @ships;
+		$ships->execute($fleet->{fid});
+		while (my $ship = $ships->fetchrow_hashref){
+			push @ships,$ship;
+		}
+		$fleet->{ships} = \@ships;
+		push @fleets,$fleet;
+	}
+	return \@fleets;
+}
+
 =head1 AUTHOR
 
 Michael Andreen (harv@ruin.nu)