X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=lib%2FNDWeb%2FController%2FUsers.pm;h=bdad4fc9b61c19dac0b1bb4b5bc7c0ee5a709866;hb=e124a881df36f865fe40babaec23b4c4801f0d59;hp=781fc73e58e95a8e8b3cd774fb2238f37c95a287;hpb=a7501170f1ef98a5959db048b92db2e4a12179a1;p=ndwebbie.git

diff --git a/lib/NDWeb/Controller/Users.pm b/lib/NDWeb/Controller/Users.pm
index 781fc73..bdad4fc 100644
--- 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 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 {
@@ -49,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});
 
@@ -84,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;
@@ -131,9 +126,10 @@ 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);
 
@@ -149,7 +145,7 @@ sub mail : Local {
 	$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'};
@@ -182,7 +178,7 @@ sub postmail : Local {
 	);
 
 	if (sendmail %mail) {
-		$c->flash(ok => );
+		$c->flash(ok => \@emails);
 	}else {
 		$c->flash(error => $Mail::Sendmail::error);
 		$c->flash(subject => $c->req->param('subject'));