From: Michael Andreen Date: Mon, 12 Jan 2009 00:09:17 +0000 (+0100) Subject: List of members with fleets and other information X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=be99623fcecb41431020f1cd46a2dca56f9c467a List of members with fleets and other information Members can decide if they want to be waken up and also give a note to DCs with more detailed instructions. --- diff --git a/database/defenders.sql b/database/defenders.sql new file mode 100644 index 0000000..6e6d18e --- /dev/null +++ b/database/defenders.sql @@ -0,0 +1,2 @@ +ALTER TABLE users ADD COLUMN call_if_needed BOOLEAN NOT NULL DEFAULT FALSE; +ALTER TABLE users ADD COLUMN sms_note TEXT NOT NULL DEFAULT ''; diff --git a/database/group_roles.sql b/database/group_roles.sql index e14d9d1..e1c81b1 100644 --- a/database/group_roles.sql +++ b/database/group_roles.sql @@ -39,6 +39,7 @@ INSERT INTO roles VALUES('intel_member'); INSERT INTO roles VALUES('intel_naps'); INSERT INTO roles VALUES('textexport_alliance'); INSERT INTO roles VALUES('stats_find_nick'); +INSERT INTO roles VALUES('members_defenders'); INSERT INTO group_roles (gid,role) VALUES(2,'member_menu'); INSERT INTO group_roles (gid,role) VALUES(2,'attack_menu'); @@ -53,6 +54,7 @@ INSERT INTO group_roles (gid,role) VALUES(6,'dc_menu'); INSERT INTO group_roles (gid,role) VALUES(6,'calls_list'); INSERT INTO group_roles (gid,role) VALUES(6,'calls_edit'); INSERT INTO group_roles (gid,role) VALUES(6,'calls_leeches'); +INSERT INTO group_roles (gid,role) VALUES(6,'members_defenders'); INSERT INTO group_roles (gid,role) VALUES(4,'bc_menu'); INSERT INTO group_roles (gid,role) VALUES(4,'raids_info'); @@ -86,6 +88,7 @@ INSERT INTO group_roles (gid,role) VALUES(1,'intel_members'); INSERT INTO group_roles (gid,role) VALUES(1,'intel_member'); INSERT INTO group_roles (gid,role) VALUES(1,'intel_naps'); INSERT INTO group_roles (gid,role) VALUES(1,'textexport_alliance'); +INSERT INTO group_roles (gid,role) VALUES(1,'members_defenders'); INSERT INTO group_roles (gid,role) VALUES(3,'dc_menu'); INSERT INTO group_roles (gid,role) VALUES(3,'bc_menu'); @@ -106,3 +109,4 @@ INSERT INTO group_roles (gid,role) VALUES(3,'intel_members'); INSERT INTO group_roles (gid,role) VALUES(3,'intel_member'); INSERT INTO group_roles (gid,role) VALUES(3,'intel_naps'); INSERT INTO group_roles (gid,role) VALUES(3,'textexport_alliance'); +INSERT INTO group_roles (gid,role) VALUES(3,'members_defenders'); diff --git a/lib/NDWeb.pm b/lib/NDWeb.pm index 13cf2b6..5e57683 100644 --- a/lib/NDWeb.pm +++ b/lib/NDWeb.pm @@ -83,6 +83,7 @@ __PACKAGE__->deny_access_unless('/alliances/resources',[qw/alliances_resources/] __PACKAGE__->deny_access_unless('/graphs/alliancevsintel',[qw/graphs_intel/]); __PACKAGE__->deny_access_unless('/graphs/avgalliancevsintel',[qw/graphs_intel/]); __PACKAGE__->deny_access_unless('/members',[qw/members/]); +__PACKAGE__->deny_access_unless('/members/defenders',[qw/members_defenders/]); __PACKAGE__->deny_access_unless('/covop',[qw/covop/]); __PACKAGE__->deny_access_unless('/calls',[qw/calls_edit/]); __PACKAGE__->allow_access_if('/calls/index',[qw/calls_list/]); diff --git a/lib/NDWeb/Controller/Members.pm b/lib/NDWeb/Controller/Members.pm index 164a0d9..59becdf 100644 --- a/lib/NDWeb/Controller/Members.pm +++ b/lib/NDWeb/Controller/Members.pm @@ -33,7 +33,8 @@ sub index : Path : Args(0) { $c->stash(u => $dbh->selectrow_hashref(q{SELECT planet,defense_points ,attack_points,scan_points,humor_points , (attack_points+defense_points+scan_points/20)::NUMERIC(5,1) as total_points - , sms,rank,hostmask FROM users WHERE uid = ? + , sms,rank,hostmask,call_if_needed,sms_note + FROM users WHERE uid = ? },undef,$c->user->id) ); @@ -82,42 +83,7 @@ sub index : Path : Args(0) { $calls->execute($c->user->id); $c->stash(calls => $calls->fetchall_arrayref({}) ); - my $query = $dbh->prepare(q{ -( - SELECT DISTINCT ON (mission,name) fid,mission,name,tick, NULL AS eta - ,amount, NULL AS coords, planet AS target, NULL AS back - FROM fleets f - JOIN full_fleets USING (fid) - WHERE uid = $1 AND planet = $2 AND tick >= tick() - 24 - AND name = 'Main' AND mission = 'Full fleet' - ORDER BY mission,name,tick DESC -) UNION ( - SELECT fid,mission,name,landing_tick AS tick, eta, amount - , coords(x,y,z), target, back - 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($c->user->id,$c->user->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; - } - $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, @@ -149,8 +115,11 @@ 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('')); } @@ -552,6 +521,73 @@ sub postconfirmation : Local { $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 +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 @members; + while (my $member = $defenders->fetchrow_hashref){ + + $member->{fleets} = member_fleets($dbh, $member->{uid}, $member->{planet}); + 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) diff --git a/root/lib/site/leftbar.tt2 b/root/lib/site/leftbar.tt2 index b3f05c8..59b2cc3 100644 --- a/root/lib/site/leftbar.tt2 +++ b/root/lib/site/leftbar.tt2 @@ -97,6 +97,7 @@ [% IF c.check_user_roles("dc_menu") %]

DC menu