Changeset 314 for trunk/DNSDB.pm

Timestamp:

04/24/12 16:43:39 (12 years ago)

Author:

Kris Deugau

Message:

/trunk

Move SQL for "Manage groups" to DNSDB.pm. See #1
Extend new group list sub to return a revzone count. See #26

File:

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

trunk/DNSDB.pm

@@ -42 +42 @@
         &getZoneCount &getZoneList
         &addGroup &delGroup &getChildren &groupName
+        &getGroupCount &getGroupList
         &addUser &updateUser &delUser &userFullName &userStatus &getUserData
         &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
@@ -64 +65 @@
                 &getZoneCount &getZoneList
                 &addGroup &delGroup &getChildren &groupName
+                &getGroupCount &getGroupList
                 &addUser &updateUser &delUser &userFullName &userStatus &getUserData
                 &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
@@ -2147 +2149 @@
   return $groupname if $groupname;
 } # end groupName
+
+
+## DNSDB::getGroupCount()
+# Get count of subgroups in group or groups
+# Takes a database handle and hash containing:
+#  - the "current" group
+#  - an array of "acceptable" groups
+#  - Optionally accept a "starts with" and/or "contains" filter argument
+# Returns an integer count of the resulting group list.
+sub getGroupCount {
+  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 groups ".
+        "WHERE parent_group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
+        ($args{startwith} ? " AND group_name ~* ?" : '').
+        ($args{filter} ? " AND group_name ~* ?" : '');
+  my ($count) = $dbh->selectrow_array($sql, undef, (@filterargs) );
+  $errstr = $dbh->errstr if !$count;
+  return $count;
+} # end getGroupCount
+
+
+## DNSDB::getGroupList()
+# Get a list of sub^n-groups in the specified group(s)
+# Takes the same arguments as getGroupCount() above
+# Returns an arrayref containing hashrefs suitable for feeding straight to HTML::Template
+sub getGroupList {
+  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};
+
+  # protection against bad or missing arguments
+  $args{sortorder} = 'ASC' if !$args{sortorder};
+  $args{offset} = 0 if !$args{offset};
+
+  # munge sortby for columns in database
+  $args{sortby} = 'g.group_name' if $args{sortby} eq 'group';
+  $args{sortby} = 'g2.group_name' if $args{sortby} eq 'parent';
+
+  my $sql = q(SELECT g.group_id AS groupid, g.group_name AS groupname, g2.group_name AS pgroup,
+        count(distinct(u.username)) AS nusers, count(distinct(d.domain)) AS ndomains,
+        count(distinct(r.revnet)) AS nrevzones
+        FROM groups g
+        INNER JOIN groups g2 ON g2.group_id=g.parent_group_id
+        LEFT OUTER JOIN users u ON u.group_id=g.group_id
+        LEFT OUTER JOIN domains d ON d.group_id=g.group_id
+        LEFT OUTER JOIN revzones r ON r.group_id=g.group_id
+        ).
+        "WHERE g.parent_group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
+        ($args{startwith} ? " AND g.group_name ~* ?" : '').
+        ($args{filter} ? " AND g.group_name ~* ?" : '').
+        " GROUP BY g.group_id, g.group_name, g2.group_name ".
+        " ORDER BY $args{sortby} $args{sortorder} ".
+        ($args{offset} eq 'all' ? '' : " LIMIT $config{perpage} OFFSET ".$args{offset}*$config{perpage});
+  my $glist = $dbh->selectall_arrayref($sql, { Slice => {} }, (@filterargs) );
+  $errstr = $dbh->errstr if !$glist;
+  return $glist;
+} # end getGroupList