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,discord_id,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 $group = $c->req->param('group');
163 my $emails = $dbh->prepare(q{SELECT email FROM users
164 WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = $1)
165 AND email is not null});
166 $emails->execute($group);
168 while (my $email = $emails->fetchrow_hashref){
169 push @emails,$email->{email};
172 my $subject = $c->req->param('subject');
173 my $message = $c->req->param('message');
176 BCC => (join ',',@emails),
177 From => 'NewDawn Command <nd@ruin.nu>',
178 'Content-type' => 'text/plain; charset="UTF-8"',
183 if (sendmail %mail) {
184 $c->flash(ok => \@emails);
186 $c->flash(error => $Mail::Sendmail::error);
187 $c->flash(subject => $subject);
188 $c->flash(message => $message);
191 $c->res->redirect($c->uri_for('mail'));
195 my ( $self, $c ) = @_;
198 $c->stash(ok => $c->flash->{ok});
199 $c->stash(error => $c->flash->{error});
201 my $query = $dbh->prepare(q{
202 SELECT uid,username FROM users
203 WHERE uid > 0 AND sms SIMILAR TO '\+\d+'
204 AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
209 $c->stash(users => $query->fetchall_arrayref({}) );
211 my $query = $dbh->prepare(q{
212 SELECT u.username AS sender, COALESCE(r.username,'unknown?') AS receiver, number,
213 message, status, cost, to_char(time, 'YYYY-MM-DD HH24:MI:SS') AS time
215 JOIN sms s USING (uid)
216 LEFT JOIN users r ON r.sms = '+' || s.number
217 WHERE time > now() - '2 weeks'::interval
221 $c->stash(sms => $query->fetchall_arrayref({}));
223 $c->stash(credits => $dbh->selectrow_array(q{SELECT credits FROM clickatell}));
226 sub postsms : Local {
227 my ( $self, $c ) = @_;
230 $c->req->parameters->{uid} = [$c->req->parameters->{uid}]
231 unless ref $c->req->parameters->{uid} eq 'ARRAY';
232 my $message = $c->req->param('message');
234 my $query = $dbh->prepare(q{INSERT INTO sms (uid,message,number)
235 (SELECT $1,$2, trim(leading '+' FROM sms) FROM users u WHERE uid = ANY ($3) AND sms SIMILAR TO '\+\d+' )});
237 $query->execute($c->user->id,$message,$c->req->parameters->{uid});
239 $c->res->redirect($c->uri_for('sms'));
244 Micahel Andreen (harv@ruin.nu)