Index: trunk/DNSDB.pm =================================================================== --- trunk/DNSDB.pm (revision 313) +++ trunk/DNSDB.pm (revision 314) @@ -42,4 +42,5 @@ &getZoneCount &getZoneList &addGroup &delGroup &getChildren &groupName + &getGroupCount &getGroupList &addUser &updateUser &delUser &userFullName &userStatus &getUserData &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount @@ -64,4 +65,5 @@ &getZoneCount &getZoneList &addGroup &delGroup &getChildren &groupName + &getGroupCount &getGroupList &addUser &updateUser &delUser &userFullName &userStatus &getUserData &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount @@ -2147,4 +2149,76 @@ 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 Index: trunk/dns.cgi =================================================================== --- trunk/dns.cgi (revision 313) +++ trunk/dns.cgi (revision 314) @@ -1947,10 +1947,6 @@ 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,6 +1965,6 @@ # 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,35 +1979,9 @@ $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() Index: trunk/templates/grpman.tmpl =================================================================== --- trunk/templates/grpman.tmpl (revision 313) +++ trunk/templates/grpman.tmpl (revision 314) @@ -40,9 +40,10 @@ - + + [ Delete ]