my ( $self, $c ) = @_;
my $dbh = $c->model;
- my $query = $dbh->prepare(qq{SELECT u.uid,username,TRIM(',' FROM concat(g.groupname||',')) AS groups
- FROM users u LEFT OUTER JOIN (groupmembers gm NATURAL JOIN groups g) ON gm.uid = u.uid
- WHERE u.uid > 0
- GROUP BY u.uid,username
- ORDER BY lower(username)});
+ my $query = $dbh->prepare(q{
+SELECT uid,username,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 {
$c->forward('findUser');
$user = $c->stash->{u};
- my $groups = $dbh->prepare(q{SELECT g.gid,g.groupname,uid
- FROM groups g
- LEFT OUTER JOIN (SELECT gid,uid FROM groupmembers WHERE uid = ?)
- AS gm ON g.gid = gm.gid
+ my $groups = $dbh->prepare(q{
+SELECT g.gid,g.groupname,uid
+FROM groups g
+ LEFT OUTER JOIN (SELECT gid,uid FROM groupmembers WHERE uid = ?)
+ AS gm USING(gid)
+WHERE gid <> ''
});
$groups->execute($user->{uid});
(SELECT ftid FROM users WHERE uid = $1),$1,$2)
});
- my $delgroup = $dbh->prepare(q{DELETE FROM groupmembers WHERE uid = ? AND gid = ?});
- my $addgroup = $dbh->prepare(q{INSERT INTO groupmembers (uid,gid) VALUES(?,?)});
+ my $delgroups = $dbh->prepare(q{DELETE FROM groupmembers WHERE uid = $1 AND gid = ANY($2) });
+ my $addgroups = $dbh->prepare(q{INSERT INTO groupmembers (uid,gid) (SELECT $1,unnest($2::text[]))});
for my $param ($c->req->param()){
if ($param =~ /^c:(planet|\w+_points|hostmask|info|username|email|sms)$/){
my $column = $1;
my $value = $c->req->param($column);
if ($column eq 'planet'){
+ $column = 'pid';
if ($value eq ''){
$value = undef;
}elsif($value =~ /^(\d+)\D+(\d+)\D+(\d+)$/){
- ($value) = $dbh->selectrow_array(q{SELECT id FROM
+ ($value) = $dbh->selectrow_array(q{SELECT pid FROM
current_planet_stats WHERE x = ? and y = ? and z =?}
,undef,$1,$2,$3);
}
}
$dbh->do(qq{UPDATE users SET $column = ? WHERE uid = ? }
,undef,$value,$user->{uid});
- $log->execute($c->user->id,"HC changed $column from $c->{$column} to $value for user: $user->{uid} ($user->{username})");
- }elsif ($param =~ /^gr:(\d+)$/){
- my $query;
- if ($c->req->param($param) eq 'remove'){
- $query = $delgroup;
- }elsif($c->req->param($param) eq 'add'){
- $query = $addgroup;
- }
- if ($query){
- $query->execute($user->{uid},$1);
- my ($action,$a2) = ('added','to');
- ($action,$a2) = ('removed','from') if $c->req->param($param) eq 'remove';
- $log->execute($c->user->id,"HC $action user: $user->{uid} ($user->{username}) $a2 group: $1");
- }
+ $log->execute($c->user->id,"HC changed $column from $user->{$column} to $value for user: $user->{uid} ($user->{username})");
+ }elsif ($param eq 'add_group'){
+ my @groups = $c->req->param($param);
+ $addgroups->execute($user->{uid},\@groups);
+ $log->execute($c->user->id,"HC added user: $user->{uid} ($user->{username}) to groups: @groups");
+ }elsif ($param eq 'remove_group'){
+ my @groups = $c->req->param($param);
+ $delgroups->execute($user->{uid},\@groups);
+ $log->execute($c->user->id,"HC removed user: $user->{uid} ($user->{username}) from groups: @groups");
}
}
$dbh->commit;
my $dbh = $c->model;
my $query = $dbh->prepare(q{
- SELECT uid,username,hostmask,CASE WHEN u.planet IS NULL THEN '' ELSE coords(x,y,z) END AS planet,attack_points,defense_points,scan_points,humor_points,info, email, sms
- FROM users u LEFT OUTER JOIN current_planet_stats p ON u.planet = p.id
- WHERE uid = ?;
+SELECT uid,username,hostmask,attack_points,defense_points,scan_points,humor_points,info, email, sms
+ ,COALESCE(coords(x,y,z),'') AS planet, pid
+FROM users u LEFT OUTER JOIN current_planet_stats p USING (pid)
+WHERE uid = ?;
});
$user = $dbh->selectrow_hashref($query,undef,$user);
$c->stash(subject => $c->flash->{subject});
$c->stash(message => $c->flash->{message});
- my $groups = $dbh->prepare(q{SELECT gid,groupname FROM groups WHERE gid > 0 ORDER BY gid});
+ my $groups = $dbh->prepare(q{SELECT gid,groupname FROM groups WHERE gid <> '' ORDER BY gid});
$groups->execute;
my @groups;
push @groups,{gid => -1, groupname => 'Pick a group'};
}
my %mail = (
- smtp => 'ruin.nu',
+ smtp => 'localhost',
BCC => (join ',',@emails),
From => 'NewDawn Command <nd@ruin.nu>',
'Content-type' => 'text/plain; charset="UTF-8"',
$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 $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,$c->req->param('message'),$c->req->parameters->{uid});
+
+ $c->res->redirect($c->uri_for('sms'));
+}
+
=head1 AUTHOR
Micahel Andreen (harv@ruin.nu)
projects / ndwebbie.git / blobdiff