X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=lib%2FNDWeb%2FController%2FUsers.pm;h=4ae8d54450f033fe1dbdd5a6d62d666d93e1339c;hb=2c227a364191710527b3baa3529ca23a0cf1df6f;hp=36b94c30256ad15172984ff6001220185ccd83ca;hpb=32bf807f4c912062de85f9beee5228a23014484d;p=ndwebbie.git diff --git a/lib/NDWeb/Controller/Users.pm b/lib/NDWeb/Controller/Users.pm index 36b94c3..4ae8d54 100644 --- a/lib/NDWeb/Controller/Users.pm +++ b/lib/NDWeb/Controller/Users.pm @@ -5,6 +5,7 @@ use warnings; use parent 'Catalyst::Controller'; use ND::Include; +use Mail::Sendmail; =head1 NAME @@ -27,18 +28,15 @@ sub index :Path :Args(0) { 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 { @@ -48,10 +46,12 @@ 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}); @@ -83,37 +83,33 @@ sub updateUser : Local { (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; @@ -130,15 +126,115 @@ sub findUser : Private { 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(u => $user); } +sub mail : Local { + my ( $self, $c ) = @_; + my $dbh = $c->model; + + $c->stash(ok => $c->flash->{ok}); + $c->stash(error => $c->flash->{error}); + $c->stash(subject => $c->flash->{subject}); + $c->stash(message => $c->flash->{message}); + + 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'}; + while (my $group = $groups->fetchrow_hashref){ + push @groups,$group; + } + $c->stash(groups => \@groups); +} + +sub postmail : Local { + my ( $self, $c ) = @_; + my $dbh = $c->model; + + 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')); + my @emails; + while (my $email = $emails->fetchrow_hashref){ + push @emails,$email->{email}; + } + + my %mail = ( + smtp => 'localhost', + BCC => (join ',',@emails), + From => 'NewDawn Command ', + 'Content-type' => 'text/plain; charset="UTF-8"', + Subject => $c->req->param('subject'), + Message => $c->req->param('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->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)