Be more paranoid with param in list context

[ndwebbie.git] / lib / NDWeb / Controller / Users.pm

diff --git a/lib/NDWeb/Controller/Users.pm b/lib/NDWeb/Controller/Users.pm
index a77d93d9f10402ed8a1b573197671bb8c3066924..13b79c74e0c9e257b525efe264f1fb733f661956 100644 (file)
--- a/lib/NDWeb/Controller/Users.pm
+++ b/lib/NDWeb/Controller/Users.pm
@@ -28,18 +28,15 @@ sub index :Path :Args(0) {
        my ( $self, $c ) = @_;
        my $dbh = $c->model;
 
-       my $query = $dbh->prepare(qq{SELECT uid,username,array_to_string(array_agg(g.groupname),', ') AS groups
-               FROM users u LEFT OUTER JOIN (groupmembers gm NATURAL JOIN groups g) USING (uid)
-               WHERE uid > 0
-               GROUP BY u.uid,username
-               ORDER BY username});
+       my $query = $dbh->prepare(q{
+SELECT uid,username,discord_id,pnick,array_to_string(array_agg(g.groupname),', ') AS groups
+FROM users u LEFT OUTER JOIN (groupmembers gm NATURAL JOIN groups g) USING (uid)
+WHERE uid > 0
+GROUP BY u.uid,username,pnick
+ORDER BY username
+               });
        $query->execute;
-
-       my @users;
-       while (my $user = $query->fetchrow_hashref){
-               push @users, $user;
-       }
-       $c->stash(users => \@users);
+       $c->stash(users => $query->fetchall_arrayref({}));
 }
 
 sub edit : Local {
@@ -130,7 +127,7 @@ sub findUser : Private {
 
        my $query = $dbh->prepare(q{
 SELECT uid,username,hostmask,attack_points,defense_points,scan_points,humor_points,info, email, sms
-       ,COALESCE(coords(x,y,z),'') AS planet
+       ,COALESCE(coords(x,y,z),'') AS planet, pid
 FROM users u LEFT OUTER JOIN current_planet_stats p USING (pid)
 WHERE uid = ?;
                });
@@ -162,35 +159,86 @@ sub postmail : Local {
        my ( $self, $c ) = @_;
        my $dbh = $c->model;
 
+       my $group = $c->req->param('group');
        my $emails = $dbh->prepare(q{SELECT email FROM users
                WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = $1)
                        AND email is not null});
-       $emails->execute($c->req->param('group'));
+       $emails->execute($group);
        my @emails;
        while (my $email = $emails->fetchrow_hashref){
                push @emails,$email->{email};
        }
 
+       my $subject = $c->req->param('subject');
+       my $message = $c->req->param('message');
        my %mail = (
-               smtp => 'ruin.nu',
+               smtp => 'localhost',
                BCC      => (join ',',@emails),
                From    => 'NewDawn Command <nd@ruin.nu>',
                'Content-type' => 'text/plain; charset="UTF-8"',
-               Subject => $c->req->param('subject'),
-               Message => $c->req->param('message'),
+               Subject => $subject,
+               Message => $message,
        );
 
        if (sendmail %mail) {
                $c->flash(ok => \@emails);
        }else {
                $c->flash(error => $Mail::Sendmail::error);
-               $c->flash(subject => $c->req->param('subject'));
-               $c->flash(message => $c->req->param('message'));
+               $c->flash(subject => $subject);
+               $c->flash(message => $message);
        }
 
        $c->res->redirect($c->uri_for('mail'));
 }
 
+sub sms : Local {
+       my ( $self, $c ) = @_;
+       my $dbh = $c->model;
+
+       $c->stash(ok => $c->flash->{ok});
+       $c->stash(error => $c->flash->{error});
+
+       my $query = $dbh->prepare(q{
+SELECT uid,username FROM users
+WHERE uid > 0 AND sms SIMILAR TO '\+\d+'
+       AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
+ORDER BY username
+               });
+       $query->execute;
+
+       $c->stash(users => $query->fetchall_arrayref({}) );
+
+       my $query = $dbh->prepare(q{
+SELECT u.username AS sender, COALESCE(r.username,'unknown?') AS receiver, number,
+       message, status, cost, to_char(time, 'YYYY-MM-DD HH24:MI:SS') AS time
+FROM users u
+       JOIN sms s USING (uid)
+       LEFT JOIN users r ON r.sms = '+' || s.number
+WHERE time > now() - '2 weeks'::interval
+ORDER BY time desc
+               });
+       $query->execute;
+       $c->stash(sms => $query->fetchall_arrayref({}));
+
+       $c->stash(credits => $dbh->selectrow_array(q{SELECT credits FROM clickatell}));
+}
+
+sub postsms : Local {
+       my ( $self, $c ) = @_;
+       my $dbh = $c->model;
+
+       $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
+               unless ref $c->req->parameters->{uid} eq 'ARRAY';
+       my $message = $c->req->param('message');
+
+       my $query = $dbh->prepare(q{INSERT INTO sms (uid,message,number)
+               (SELECT $1,$2, trim(leading '+' FROM sms) FROM users u WHERE uid = ANY ($3) AND sms SIMILAR TO '\+\d+' )});
+
+       $query->execute($c->user->id,$message,$c->req->parameters->{uid});
+
+       $c->res->redirect($c->uri_for('sms'));
+}
+
 =head1 AUTHOR
 
 Micahel Andreen (harv@ruin.nu)