From 5ca7cefbaab2deaccacbfd69ce23065034c07732 Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Fri, 18 Jul 2008 12:41:23 +0200 Subject: [PATCH] Converted memberIntel --- NDWeb/Pages/MemberIntel.pm | 157 ---------------------------------- database/group_roles.sql | 6 ++ lib/NDWeb.pm | 2 + lib/NDWeb/Controller/Intel.pm | 101 ++++++++++++++++++++++ root/lib/site/leftbar.tt2 | 2 +- root/src/intel/member.tt2 | 26 ++++++ root/src/intel/members.tt2 | 23 +++++ templates/memberIntel.tmpl | 57 ------------ 8 files changed, 159 insertions(+), 215 deletions(-) delete mode 100644 NDWeb/Pages/MemberIntel.pm create mode 100644 root/src/intel/member.tt2 create mode 100644 root/src/intel/members.tt2 delete mode 100644 templates/memberIntel.tmpl diff --git a/NDWeb/Pages/MemberIntel.pm b/NDWeb/Pages/MemberIntel.pm deleted file mode 100644 index 292d985..0000000 --- a/NDWeb/Pages/MemberIntel.pm +++ /dev/null @@ -1,157 +0,0 @@ -#************************************************************************** -# Copyright (C) 2006 by Michael Andreen * -# * -# This program is free software; you can redistribute it and/or modify * -# it under the terms of the GNU General Public License as published by * -# the Free Software Foundation; either version 2 of the License, or * -# (at your option) any later version. * -# * -# This program is distributed in the hope that it will be useful, * -# but WITHOUT ANY WARRANTY; without even the implied warranty of * -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * -# GNU General Public License for more details. * -# * -# You should have received a copy of the GNU General Public License * -# along with this program; if not, write to the * -# Free Software Foundation, Inc., * -# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. * -#**************************************************************************/ - -package NDWeb::Pages::MemberIntel; -use strict; -use warnings; -use CGI qw/:standard/; -use NDWeb::Include; - -use base qw/NDWeb::XMLPage/; - -$NDWeb::Page::PAGES{memberIntel} = __PACKAGE__; - -sub render_body { - my $self = shift; - my ($BODY) = @_; - $self->{TITLE} = 'Member Intel'; - my $DBH = $self->{DBH}; - my $error; - - return $self->noAccess unless $self->isHC; - - my $showticks = 'AND i.tick > tick()'; - if (defined param('show')){ - if (param('show') eq 'all'){ - $showticks = ''; - }elsif (param('show') =~ /^(\d+)$/){ - $showticks = "AND (i.tick - i.eta) > (tick() - $1)"; - } - } - - my $user; - if (defined param('uid') && param('uid') =~ /^(\d+)$/){ - my $query = $DBH->prepare(q{SELECT username,uid FROM users WHERE uid = ? - }); - $user = $DBH->selectrow_hashref($query,undef,$1); - } - - if ($user){ - $BODY->param(UID => $user->{uid}); - 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($user->{uid}) or $error .= $DBH->errstr; - 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', list => \@nd_attacks, class => 'AllyDef'}; - push @attacks, {name => 'Other', list => \@other_attacks, class => 'Attack'}; - $BODY->param(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($user->{uid}) or $error .= $DBH->errstr; - my @nd_def; - my @ingal_def; - my @other_def; - while (my $intel = $query->fetchrow_hashref){ - my $def = {target => $intel->{coords}.(defined $intel->{alliance} ? " ($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', list => \@nd_def, class => 'AllyDef'}; - push @defenses, {name => 'Ingal Def', list => \@ingal_def, class => 'Defend'}; - push @defenses, {name => 'Other', list => \@other_def, class => 'Attack'}; - $BODY->param(Defenses => \@defenses); - - }else{ - my $order = "attacks"; - if (defined param('order') && param('order') =~ /^(attacks|defenses|attack_points|defense_points|solo|bad_def)$/){ - $order = $1; - } - - my $query = $DBH->prepare(qq{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() or $error .= $DBH->errstr; - my @members; - while (my $intel = $query->fetchrow_hashref){ - $intel->{OLD} = 'OLD' if (!defined $intel->{tick} || $self->{TICK} > $intel->{tick} + 60); - delete $intel->{tick}; - push @members,$intel; - } - $BODY->param(Members => \@members); - } - $BODY->param(Error => $error); - return $BODY; -} -1; diff --git a/database/group_roles.sql b/database/group_roles.sql index bbb5448..7547715 100644 --- a/database/group_roles.sql +++ b/database/group_roles.sql @@ -34,6 +34,8 @@ INSERT INTO roles VALUES('stats_missions'); INSERT INTO roles VALUES('stats_scans'); INSERT INTO roles VALUES('stats_planetdata'); INSERT INTO roles VALUES('intel'); +INSERT INTO roles VALUES('intel_members'); +INSERT INTO roles VALUES('intel_member'); INSERT INTO group_roles (gid,role) VALUES(2,'member_menu'); INSERT INTO group_roles (gid,role) VALUES(2,'attack_menu'); @@ -76,6 +78,8 @@ INSERT INTO group_roles (gid,role) VALUES(1,'calls_leeches'); INSERT INTO group_roles (gid,role) VALUES(1,'raids_info'); INSERT INTO group_roles (gid,role) VALUES(1,'raids_edit'); INSERT INTO group_roles (gid,role) VALUES(1,'intel'); +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(3,'dc_menu'); INSERT INTO group_roles (gid,role) VALUES(3,'bc_menu'); @@ -92,3 +96,5 @@ INSERT INTO group_roles (gid,role) VALUES(3,'calls_leeches'); INSERT INTO group_roles (gid,role) VALUES(3,'raids_info'); INSERT INTO group_roles (gid,role) VALUES(3,'raids_edit'); INSERT INTO group_roles (gid,role) VALUES(3,'intel'); +INSERT INTO group_roles (gid,role) VALUES(3,'intel_members'); +INSERT INTO group_roles (gid,role) VALUES(3,'intel_member'); diff --git a/lib/NDWeb.pm b/lib/NDWeb.pm index 819f70e..7e3a63b 100644 --- a/lib/NDWeb.pm +++ b/lib/NDWeb.pm @@ -88,6 +88,8 @@ __PACKAGE__->allow_access_if('/raids/view',1); __PACKAGE__->allow_access_if('/raids/findRaid',1); __PACKAGE__->allow_access_if('/raids/log',1); __PACKAGE__->deny_access_unless('/intel',[qw/intel/]); +__PACKAGE__->deny_access_unless('/intel/members',[qw/intel_members/]); +__PACKAGE__->deny_access_unless('/intel/member',[qw/intel_member/]); =head1 NAME diff --git a/lib/NDWeb/Controller/Intel.pm b/lib/NDWeb/Controller/Intel.pm index f2cd52e..46fe6ce 100644 --- 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 diff --git a/root/lib/site/leftbar.tt2 b/root/lib/site/leftbar.tt2 index 1159831..12794f0 100644 --- a/root/lib/site/leftbar.tt2 +++ b/root/lib/site/leftbar.tt2 @@ -98,7 +98,7 @@
  • List users
  • Alliances
  • Hostile Alliances
    • -
  • Member Intel
    • +
  • Member Intel
  • Alliance resources
  • Planet Naps
  • Mail
    • diff --git a/root/src/intel/member.tt2 b/root/src/intel/member.tt2 new file mode 100644 index 0000000..012c84e --- /dev/null +++ b/root/src/intel/member.tt2 @@ -0,0 +1,26 @@ +[% META title = 'Member intel' %] + +[% BLOCK missionlists %] +[% FOR l IN lists %] + [% l.name %] + [% FOR m IN l.missions %] + + [% m.target %] + [% m.tick %] + [% m.other %] + + [% END %] +[% END %] +[% END %] + + + + +[% PROCESS missionlists lists=attacks %] +
    AttackTick
    + + + + +[% PROCESS missionlists lists=defenses %] +
    DefenseTick
    diff --git a/root/src/intel/members.tt2 b/root/src/intel/members.tt2 new file mode 100644 index 0000000..f90453e --- /dev/null +++ b/root/src/intel/members.tt2 @@ -0,0 +1,23 @@ +[% META title = 'Member intel' %] + + + + + + + + + + +[% FOR m IN members %] + + + + + + + + + +[% END %] +
    MemberAttacksDefensesAttack PointsDefense PointsSolo attacksDef outside ND/Gal
    [% m.username %][% m.attacks %][% m.defenses %][% m.attack_points %][% m.defense_points %][% m.solo %][% m.bad_def %]
    diff --git a/templates/memberIntel.tmpl b/templates/memberIntel.tmpl deleted file mode 100644 index a290f10..0000000 --- a/templates/memberIntel.tmpl +++ /dev/null @@ -1,57 +0,0 @@ - - - - - - - - - - - - - - - - - -
    AttackPt
    - - - - - - - - - - - - - - -
    DefensePt
    - - - - - - - - - - - - - - - - - - - - - - -
    MemberAttacksDefensesAttack PointsDefense PointsSolo attacksDef outside ND/Gal
    -     -- 2.39.2