lib/NDWeb/Controller/Users.pm

   1 package NDWeb::Controller::Users;
   2
   3 use strict;
   4 use warnings;
   5 use parent 'Catalyst::Controller';
   6
   7 use ND::Include;
   8 use Mail::Sendmail;
   9
  10 =head1 NAME
  11
  12 NDWeb::Controller::Users - Catalyst Controller
  13
  14 =head1 DESCRIPTION
  15
  16 Catalyst Controller.
  17
  18 =head1 METHODS
  19
  20 =cut
  21
  22
  23 =head2 index
  24
  25 =cut
  26
  27 sub index :Path :Args(0) {
  28         my ( $self, $c ) = @_;
  29         my $dbh = $c->model;
  30
  31         my $query = $dbh->prepare(q{
  32 SELECT uid,username,pnick,array_to_string(array_agg(g.groupname),', ') AS groups
  33 FROM users u LEFT OUTER JOIN (groupmembers gm NATURAL JOIN groups g) USING (uid)
  34 WHERE uid > 0
  35 GROUP BY u.uid,username,pnick
  36 ORDER BY username
  37                 });
  38         $query->execute;
  39         $c->stash(users => $query->fetchall_arrayref({}));
  40 }
  41
  42 sub edit : Local {
  43         my ( $self, $c, $user ) = @_;
  44         my $dbh = $c->model;
  45
  46         $c->forward('findUser');
  47         $user = $c->stash->{u};
  48
  49         my $groups = $dbh->prepare(q{
  50 SELECT g.gid,g.groupname,uid
  51 FROM groups g
  52         LEFT OUTER JOIN (SELECT gid,uid FROM groupmembers WHERE uid = ?)
  53         AS gm USING(gid)
  54 WHERE gid <> ''
  55                 });
  56         $groups->execute($user->{uid});
  57
  58
  59         my @addgroups;
  60         my @remgroups;
  61         while (my $group = $groups->fetchrow_hashref){
  62                 if ($group->{uid}){
  63                         push @remgroups,$group;
  64                 }else{
  65                         push @addgroups,$group;
  66                 }
  67         }
  68         $c->stash(membergroups => \@remgroups);
  69         $c->stash(othergroups => \@addgroups);
  70
  71 }
  72
  73 sub updateUser : Local {
  74         my ( $self, $c, $user ) = @_;
  75         my $dbh = $c->model;
  76
  77         $c->forward('findUser');
  78         $user = $c->stash->{u};
  79
  80         $dbh->begin_work;
  81         eval{
  82                 my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
  83                         (SELECT ftid FROM users WHERE uid = $1),$1,$2)
  84                         });
  85
  86                 my $delgroups = $dbh->prepare(q{DELETE FROM groupmembers WHERE uid = $1 AND gid = ANY($2) });
  87                 my $addgroups = $dbh->prepare(q{INSERT INTO groupmembers (uid,gid) (SELECT $1,unnest($2::text[]))});
  88                 for my $param ($c->req->param()){
  89                         if ($param =~ /^c:(planet|\w+_points|hostmask|info|username|email|sms)$/){
  90                                 my $column = $1;
  91                                 my $value = $c->req->param($column);
  92                                 if ($column eq 'planet'){
  93                                         $column = 'pid';
  94                                         if ($value eq ''){
  95                                                 $value = undef;
  96                                         }elsif($value =~ /^(\d+)\D+(\d+)\D+(\d+)$/){
  97                                                 ($value) = $dbh->selectrow_array(q{SELECT pid FROM
  98                                                         current_planet_stats WHERE x = ? and y = ? and z =?}
  99                                                         ,undef,$1,$2,$3);
 100                                         }
 101                                 }
 102                                 $dbh->do(qq{UPDATE users SET $column = ? WHERE uid = ? }
 103                                         ,undef,$value,$user->{uid});
 104                                 $log->execute($c->user->id,"HC changed $column from $user->{$column} to $value for user: $user->{uid} ($user->{username})");
 105                         }elsif ($param eq 'add_group'){
 106                                 my @groups = $c->req->param($param);
 107                                 $addgroups->execute($user->{uid},\@groups);
 108                                 $log->execute($c->user->id,"HC added user: $user->{uid} ($user->{username}) to groups: @groups");
 109                         }elsif ($param eq 'remove_group'){
 110                                 my @groups = $c->req->param($param);
 111                                 $delgroups->execute($user->{uid},\@groups);
 112                                 $log->execute($c->user->id,"HC removed user: $user->{uid} ($user->{username}) from groups: @groups");
 113                         }
 114                 }
 115                 $dbh->commit;
 116         };
 117         if ($@){
 118                 $dbh->rollback;
 119                 die $@;
 120         }
 121         $c->res->redirect($c->uri_for('edit',$user->{uid}));
 122 }
 123
 124 sub findUser : Private {
 125         my ( $self, $c, $user ) = @_;
 126         my $dbh = $c->model;
 127
 128         my $query = $dbh->prepare(q{
 129 SELECT uid,username,hostmask,attack_points,defense_points,scan_points,humor_points,info, email, sms
 130         ,COALESCE(coords(x,y,z),'') AS planet, pid
 131 FROM users u LEFT OUTER JOIN current_planet_stats p USING (pid)
 132 WHERE uid = ?;
 133                 });
 134         $user = $dbh->selectrow_hashref($query,undef,$user);
 135
 136         $c->stash(u => $user);
 137 }
 138
 139 sub mail : Local {
 140         my ( $self, $c ) = @_;
 141         my $dbh = $c->model;
 142
 143         $c->stash(ok => $c->flash->{ok});
 144         $c->stash(error => $c->flash->{error});
 145         $c->stash(subject => $c->flash->{subject});
 146         $c->stash(message => $c->flash->{message});
 147
 148         my $groups = $dbh->prepare(q{SELECT gid,groupname FROM groups WHERE gid <> '' ORDER BY gid});
 149         $groups->execute;
 150         my @groups;
 151         push @groups,{gid => -1, groupname => 'Pick a group'};
 152         while (my $group = $groups->fetchrow_hashref){
 153                 push @groups,$group;
 154         }
 155         $c->stash(groups => \@groups);
 156 }
 157
 158 sub postmail : Local {
 159         my ( $self, $c ) = @_;
 160         my $dbh = $c->model;
 161
 162         my $emails = $dbh->prepare(q{SELECT email FROM users
 163                 WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = $1)
 164                         AND email is not null});
 165         $emails->execute($c->req->param('group'));
 166         my @emails;
 167         while (my $email = $emails->fetchrow_hashref){
 168                 push @emails,$email->{email};
 169         }
 170
 171         my %mail = (
 172                 smtp => 'localhost',
 173                 BCC      => (join ',',@emails),
 174                 From    => 'NewDawn Command <nd@ruin.nu>',
 175                 'Content-type' => 'text/plain; charset="UTF-8"',
 176                 Subject => $c->req->param('subject'),
 177                 Message => $c->req->param('message'),
 178         );
 179
 180         if (sendmail %mail) {
 181                 $c->flash(ok => \@emails);
 182         }else {
 183                 $c->flash(error => $Mail::Sendmail::error);
 184                 $c->flash(subject => $c->req->param('subject'));
 185                 $c->flash(message => $c->req->param('message'));
 186         }
 187
 188         $c->res->redirect($c->uri_for('mail'));
 189 }
 190
 191 sub sms : Local {
 192         my ( $self, $c ) = @_;
 193         my $dbh = $c->model;
 194
 195         $c->stash(ok => $c->flash->{ok});
 196         $c->stash(error => $c->flash->{error});
 197
 198         my $query = $dbh->prepare(q{
 199 SELECT uid,username FROM users
 200 WHERE uid > 0 AND sms SIMILAR TO '\+\d+'
 201         AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
 202 ORDER BY username
 203                 });
 204         $query->execute;
 205
 206         $c->stash(users => $query->fetchall_arrayref({}) );
 207
 208         my $query = $dbh->prepare(q{
 209 SELECT u.username AS sender, COALESCE(r.username,'unknown?') AS receiver, number,
 210         message, status, cost, to_char(time, 'YYYY-MM-DD HH24:MI:SS') AS time
 211 FROM users u
 212         JOIN sms s USING (uid)
 213         LEFT JOIN users r ON r.sms = '+' || s.number
 214 WHERE time > now() - '2 weeks'::interval
 215 ORDER BY time desc
 216                 });
 217         $query->execute;
 218         $c->stash(sms => $query->fetchall_arrayref({}));
 219
 220         $c->stash(credits => $dbh->selectrow_array(q{SELECT credits FROM clickatell}));
 221 }
 222
 223 sub postsms : Local {
 224         my ( $self, $c ) = @_;
 225         my $dbh = $c->model;
 226
 227         $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
 228                 unless ref $c->req->parameters->{uid} eq 'ARRAY';
 229
 230         my $query = $dbh->prepare(q{INSERT INTO sms (uid,message,number)
 231                 (SELECT $1,$2, trim(leading '+' FROM sms) FROM users u WHERE uid = ANY ($3) AND sms SIMILAR TO '\+\d+' )});
 232
 233         $query->execute($c->user->id,$c->req->param('message'),$c->req->parameters->{uid});
 234
 235         $c->res->redirect($c->uri_for('sms'));
 236 }
 237
 238 =head1 AUTHOR
 239
 240 Micahel Andreen (harv@ruin.nu)
 241
 242 =head1 LICENSE
 243
 244 GPL 2.0, or later.
 245
 246 =cut
 247
 248 1;