Changeset 314 for trunk/dns.cgi

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/dns.cgi (modified) (3 diffs)

trunk/dns.cgi

@@ -1947 +1947 @@
   my $childlist = join(',',@childgroups);
 
-  my $sql = "SELECT count(*) FROM groups WHERE parent_group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
-        ($startwith ? " AND group_name ~* ?" : '').
-        ($filter ? " AND group_name ~* ?" : '');
-  my $sth = $dbh->prepare($sql);
-  $sth->execute(@filterargs);
-  my ($count) = ($sth->fetchrow_array);
+  my ($count) = getGroupCount($dbh, (childlist => $childlist, curgroup => $curgroup,
+        filter => ($filter ? $filter : undef), startwith => ($startwith ? $startwith : undef) ) );
 
 # fill page count and first-previous-next-last-all bits
@@ -1969 +1965 @@
 
 # set up the headers
-  my @cols = ('group','parent','nusers','ndomains');
-  my %colnames = (group => 'Group', parent => 'Parent Group', nusers => 'Users', ndomains => 'Domains');
+  my @cols = ('group','parent','nusers','ndomains','nrevzones');
+  my %colnames = (group => 'Group', parent => 'Parent Group', nusers => 'Users', ndomains => 'Domains', nrevzones => 'Reverse Zones');
   fill_colheads($sortby, $sortorder, \@cols, \%colnames);
 
@@ -1983 +1979 @@
   $sortby = 'g2.group_name' if $sortby eq 'parent';
 
-  my @grouplist;
-  $sql = "SELECT g.group_id, g.group_name, g2.group_name, ".
-        "count(distinct(u.username)) AS nusers, count(distinct(d.domain)) AS ndomains ".
-        "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 ".
-        "WHERE g.parent_group_id IN ($curgroup".($childlist ? ",$childlist" : '').") ".
-        ($startwith ? " AND g.group_name ~* ?" : '').
-        ($filter ? " AND g.group_name ~* ?" : '').
-        " GROUP BY g.group_id, g.group_name, g2.group_name ".
-        " ORDER BY $sortby $sortorder ".
-        ($offset eq 'all' ? '' : " LIMIT $perpage OFFSET ".$offset*$perpage);
-  $sth = $dbh->prepare($sql);
-  $sth->execute(@filterargs);
-
-  my $rownum = 0;
-  while (my @data = $sth->fetchrow_array) {
-    my %row;
-    $row{groupid} = $data[0];
-    $row{groupname} = $data[1];
-    $row{pgroup} = $data[2];
-    $row{nusers} = $data[3];
-    $row{ndomains} = $data[4];
-    $row{bg} = ($rownum++)%2;
-    $row{sid} = $sid;
-    $row{edgrp} = ($permissions{admin} || $permissions{group_edit});
-    $row{delgrp} = ($permissions{admin} || $permissions{group_delete});
-    push @grouplist, \%row;
-  }
-  $page->param(grouptable => \@grouplist);
+  my $glist = getGroupList($dbh, (childlist => $childlist, curgroup => $curgroup,
+        filter => ($filter ? $filter : undef), startwith => ($startwith ? $startwith : undef),
+        sortby => $sortby, sortorder => $sortorder) );
+
+  $page->param(grouptable => $glist);
 } # end listgroups()