Changeset 325 for trunk/DNSDB.pm

Timestamp:

05/02/12 15:03:07 (12 years ago)

Author:

Kris Deugau

Message:

/trunk

Move SQL from user list code into DNSDB.pm. See #1

File:

• trunk/DNSDB.pm (modified) (3 diffs)

trunk/DNSDB.pm

@@ -44 +44 @@
         &getGroupCount &getGroupList
         &addUser &updateUser &delUser &userFullName &userStatus &getUserData
+        &getUserCount &getUserList
         &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
         &addRec &updateRec &delRec
@@ -68 +69 @@
                 &getGroupCount &getGroupList
                 &addUser &updateUser &delUser &userFullName &userStatus &getUserData
+                &getUserCount &getUserList
                 &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
                 &addRec &updateRec &delRec
@@ -2356 +2358 @@
   return ('OK',"User $username ($fname $lname) added");
 } # end addUser
+
+
+## DNSDB::getUserCount()
+# Get count of users in group
+# Takes a database handle and hash containing at least the current group, and optionally:
+# - a reference list of secondary groups
+# - a filter string
+# - a "Starts with" string
+sub getUserCount {
+  my $dbh = shift;
+
+  my %args = @_;
+
+  my @filterargs;
+
+  $args{startwith} = undef if $args{startwith} && $args{startwith} !~ /^(?:[a-z]|0-9)$/;
+  push @filterargs, "^$args{startwith}" if $args{startwith};
+  push @filterargs, $args{filter} if $args{filter};
+
+
+  my $sql = "SELECT count(*) FROM users ".
+        "WHERE group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
+        ($args{startwith} ? " AND username ~* ?" : '').
+        ($args{filter} ? " AND username ~* ?" : '');
+  my ($count) = $dbh->selectrow_array($sql, undef, (@filterargs) );
+  $errstr = $dbh->errstr if !$count;
+  return $count;
+} # end getUserCount()
+
+
+## DNSDB::getUserList()
+# Get list of users
+# Takes the same arguments as getUserCount() above, plus optional:
+# - sort field
+# - sort order
+# - offset/return-all-everything flag (defaults to $perpage records)
+sub getUserList {
+  my $dbh = shift;
+
+  my %args = @_;
+
+  my @filterargs;
+
+  $args{startwith} = undef if $args{startwith} && $args{startwith} !~ /^(?:[a-z]|0-9)$/;
+  push @filterargs, "^$args{startwith}" if $args{startwith};
+  push @filterargs, $args{filter} if $args{filter};
+
+  # better to request sorts on "simple" names, but it means we need to map it to real columns
+  my %sortmap = (user => 'u.username', type => 'u.type', group => 'g.group_name', status => 'u.status',
+        fname => 'fname');
+  $args{sortby} = $sortmap{$args{sortby}};
+
+  # protection against bad or missing arguments
+  $args{sortorder} = 'ASC' if !$args{sortorder};
+  $args{sortby} = 'u.username' if !$args{sortby};
+  $args{offset} = 0 if !$args{offset};
+
+  my $sql = "SELECT u.user_id, u.username, u.firstname || ' ' || u.lastname AS fname, u.type, g.group_name, u.status ".
+        "FROM users u ".
+        "INNER JOIN groups g ON u.group_id=g.group_id ".
+        "WHERE u.group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
+        ($args{startwith} ? " AND u.username ~* ?" : '').
+        ($args{filter} ? " AND u.username ~* ?" : '').
+        " ORDER BY $args{sortby} $args{sortorder} ".
+        ($args{offset} eq 'all' ? '' : " LIMIT $config{perpage} OFFSET ".$args{offset}*$config{perpage});
+  my $ulist = $dbh->selectall_arrayref($sql, { Slice => {} }, (@filterargs) );
+  $errstr = $dbh->errstr if !$ulist;
+  return $ulist;
+} # end getUserList()