1 package NDWeb::Controller::Users;
5 use parent 'Catalyst::Controller';
12 NDWeb::Controller::Users - Catalyst Controller
27 sub index :Path :Args(0) {
28 my ( $self, $c ) = @_;
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)
35 GROUP BY u.uid,username,pnick
39 $c->stash(users => $query->fetchall_arrayref({}));
43 my ( $self, $c, $user ) = @_;
46 $c->forward('findUser');
47 $user = $c->stash->{u};
49 my $groups = $dbh->prepare(q{
50 SELECT g.gid,g.groupname,uid
52 LEFT OUTER JOIN (SELECT gid,uid FROM groupmembers WHERE uid = ?)
56 $groups->execute($user->{uid});
61 while (my $group = $groups->fetchrow_hashref){
63 push @remgroups,$group;
65 push @addgroups,$group;
68 $c->stash(membergroups => \@remgroups);
69 $c->stash(othergroups => \@addgroups);
73 sub updateUser : Local {
74 my ( $self, $c, $user ) = @_;
77 $c->forward('findUser');
78 $user = $c->stash->{u};
82 my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
83 (SELECT ftid FROM users WHERE uid = $1),$1,$2)
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)$/){
91 my $value = $c->req->param($column);
92 if ($column eq 'planet'){
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 =?}
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");
121 $c->res->redirect($c->uri_for('edit',$user->{uid}));
124 sub findUser : Private {
125 my ( $self, $c, $user ) = @_;
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)
134 $user = $dbh->selectrow_hashref($query,undef,$user);
136 $c->stash(u => $user);
140 my ( $self, $c ) = @_;
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});
148 my $groups = $dbh->prepare(q{SELECT gid,groupname FROM groups WHERE gid <> '' ORDER BY gid});
151 push @groups,{gid => -1, groupname => 'Pick a group'};
152 while (my $group = $groups->fetchrow_hashref){
155 $c->stash(groups => \@groups);
158 sub postmail : Local {
159 my ( $self, $c ) = @_;
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'));
167 while (my $email = $emails->fetchrow_hashref){
168 push @emails,$email->{email};
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'),
180 if (sendmail %mail) {
181 $c->flash(ok => \@emails);
183 $c->flash(error => $Mail::Sendmail::error);
184 $c->flash(subject => $c->req->param('subject'));
185 $c->flash(message => $c->req->param('message'));
188 $c->res->redirect($c->uri_for('mail'));
192 my ( $self, $c ) = @_;
195 $c->stash(ok => $c->flash->{ok});
196 $c->stash(error => $c->flash->{error});
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')
206 $c->stash(users => $query->fetchall_arrayref({}) );
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
212 JOIN sms s USING (uid)
213 LEFT JOIN users r ON r.sms = '+' || s.number
214 WHERE time > now() - '2 weeks'::interval
218 $c->stash(sms => $query->fetchall_arrayref({}));
221 sub postsms : Local {
222 my ( $self, $c ) = @_;
225 $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
226 unless ref $c->req->parameters->{uid} eq 'ARRAY';
228 my $query = $dbh->prepare(q{INSERT INTO sms (uid,message,number)
229 (SELECT $1,$2, trim(leading '+' FROM sms) FROM users u WHERE uid = ANY ($3) AND sms SIMILAR TO '\+\d+' )});
231 $query->execute($c->user->id,$c->req->param('message'),$c->req->parameters->{uid});
233 $c->res->redirect($c->uri_for('sms'));
238 Micahel Andreen (harv@ruin.nu)