Changeset 314


Ignore:
Timestamp:
04/24/12 16:43:39 (13 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

Location:
trunk
Files:
3 edited

Legend:

Unmodified
Added
Removed

  • trunk/DNSDB.pm

    r312 r314  
    4242        &getZoneCount &getZoneList
    4343        &addGroup &delGroup &getChildren &groupName
     44        &getGroupCount &getGroupList
    4445        &addUser &updateUser &delUser &userFullName &userStatus &getUserData
    4546        &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
    … …  
    6465                &getZoneCount &getZoneList
    6566                &addGroup &delGroup &getChildren &groupName
     67                &getGroupCount &getGroupList
    6668                &addUser &updateUser &delUser &userFullName &userStatus &getUserData
    6769                &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
    … …  
    21472149  return $groupname if $groupname;
    21482150} # end groupName
     2151
     2152
     2153## DNSDB::getGroupCount()
     2154# Get count of subgroups in group or groups
     2155# Takes a database handle and hash containing:
     2156#  - the "current" group
     2157#  - an array of "acceptable" groups
     2158#  - Optionally accept a "starts with" and/or "contains" filter argument
     2159# Returns an integer count of the resulting group list.
     2160sub getGroupCount {
     2161  my $dbh = shift;
     2162
     2163  my %args = @_;
     2164
     2165  my @filterargs;
     2166
     2167  $args{startwith} = undef if $args{startwith} && $args{startwith} !~ /^(?:[a-z]|0-9)$/;
     2168  push @filterargs, "^$args{startwith}" if $args{startwith};
     2169  push @filterargs, $args{filter} if $args{filter};
     2170
     2171  my $sql = "SELECT count(*) FROM groups ".
     2172        "WHERE parent_group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
     2173        ($args{startwith} ? " AND group_name ~* ?" : '').
     2174        ($args{filter} ? " AND group_name ~* ?" : '');
     2175  my ($count) = $dbh->selectrow_array($sql, undef, (@filterargs) );
     2176  $errstr = $dbh->errstr if !$count;
     2177  return $count;
     2178} # end getGroupCount
     2179
     2180
     2181## DNSDB::getGroupList()
     2182# Get a list of sub^n-groups in the specified group(s)
     2183# Takes the same arguments as getGroupCount() above
     2184# Returns an arrayref containing hashrefs suitable for feeding straight to HTML::Template
     2185sub getGroupList {
     2186  my $dbh = shift;
     2187
     2188  my %args = @_;
     2189
     2190  my @filterargs;
     2191
     2192  $args{startwith} = undef if $args{startwith} && $args{startwith} !~ /^(?:[a-z]|0-9)$/;
     2193  push @filterargs, "^$args{startwith}" if $args{startwith};
     2194  push @filterargs, $args{filter} if $args{filter};
     2195
     2196  # protection against bad or missing arguments
     2197  $args{sortorder} = 'ASC' if !$args{sortorder};
     2198  $args{offset} = 0 if !$args{offset};
     2199
     2200  # munge sortby for columns in database
     2201  $args{sortby} = 'g.group_name' if $args{sortby} eq 'group';
     2202  $args{sortby} = 'g2.group_name' if $args{sortby} eq 'parent';
     2203
     2204  my $sql = q(SELECT g.group_id AS groupid, g.group_name AS groupname, g2.group_name AS pgroup,
     2205        count(distinct(u.username)) AS nusers, count(distinct(d.domain)) AS ndomains,
     2206        count(distinct(r.revnet)) AS nrevzones
     2207        FROM groups g
     2208        INNER JOIN groups g2 ON g2.group_id=g.parent_group_id
     2209        LEFT OUTER JOIN users u ON u.group_id=g.group_id
     2210        LEFT OUTER JOIN domains d ON d.group_id=g.group_id
     2211        LEFT OUTER JOIN revzones r ON r.group_id=g.group_id
     2212        ).
     2213        "WHERE g.parent_group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
     2214        ($args{startwith} ? " AND g.group_name ~* ?" : '').
     2215        ($args{filter} ? " AND g.group_name ~* ?" : '').
     2216        " GROUP BY g.group_id, g.group_name, g2.group_name ".
     2217        " ORDER BY $args{sortby} $args{sortorder} ".
     2218        ($args{offset} eq 'all' ? '' : " LIMIT $config{perpage} OFFSET ".$args{offset}*$config{perpage});
     2219  my $glist = $dbh->selectall_arrayref($sql, { Slice => {} }, (@filterargs) );
     2220  $errstr = $dbh->errstr if !$glist;
     2221  return $glist;
     2222} # end getGroupList
    21492223
    21502224

  • trunk/dns.cgi

    r313 r314  
    19471947  my $childlist = join(',',@childgroups);
    19481948
    1949   my $sql = "SELECT count(*) FROM groups WHERE parent_group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
    1950         ($startwith ? " AND group_name ~* ?" : '').
    1951         ($filter ? " AND group_name ~* ?" : '');
    1952   my $sth = $dbh->prepare($sql);
    1953   $sth->execute(@filterargs);
    1954   my ($count) = ($sth->fetchrow_array);
     1949  my ($count) = getGroupCount($dbh, (childlist => $childlist, curgroup => $curgroup,
     1950        filter => ($filter ? $filter : undef), startwith => ($startwith ? $startwith : undef) ) );
    19551951
    19561952# fill page count and first-previous-next-last-all bits
    … …  
    19691965
    19701966# set up the headers
    1971   my @cols = ('group','parent','nusers','ndomains');
    1972   my %colnames = (group => 'Group', parent => 'Parent Group', nusers => 'Users', ndomains => 'Domains');
     1967  my @cols = ('group','parent','nusers','ndomains','nrevzones');
     1968  my %colnames = (group => 'Group', parent => 'Parent Group', nusers => 'Users', ndomains => 'Domains', nrevzones => 'Reverse Zones');
    19731969  fill_colheads($sortby, $sortorder, \@cols, \%colnames);
    19741970
    … …  
    19831979  $sortby = 'g2.group_name' if $sortby eq 'parent';
    19841980
    1985   my @grouplist;
    1986   $sql = "SELECT g.group_id, g.group_name, g2.group_name, ".
    1987         "count(distinct(u.username)) AS nusers, count(distinct(d.domain)) AS ndomains ".
    1988         "FROM groups g ".
    1989         "INNER JOIN groups g2 ON g2.group_id=g.parent_group_id ".
    1990         "LEFT OUTER JOIN users u ON u.group_id=g.group_id ".
    1991         "LEFT OUTER JOIN domains d ON d.group_id=g.group_id ".
    1992         "WHERE g.parent_group_id IN ($curgroup".($childlist ? ",$childlist" : '').") ".
    1993         ($startwith ? " AND g.group_name ~* ?" : '').
    1994         ($filter ? " AND g.group_name ~* ?" : '').
    1995         " GROUP BY g.group_id, g.group_name, g2.group_name ".
    1996         " ORDER BY $sortby $sortorder ".
    1997         ($offset eq 'all' ? '' : " LIMIT $perpage OFFSET ".$offset*$perpage);
    1998   $sth = $dbh->prepare($sql);
    1999   $sth->execute(@filterargs);
    2000 
    2001   my $rownum = 0;
    2002   while (my @data = $sth->fetchrow_array) {
    2003     my %row;
    2004     $row{groupid} = $data[0];
    2005     $row{groupname} = $data[1];
    2006     $row{pgroup} = $data[2];
    2007     $row{nusers} = $data[3];
    2008     $row{ndomains} = $data[4];
    2009     $row{bg} = ($rownum++)%2;
    2010     $row{sid} = $sid;
    2011     $row{edgrp} = ($permissions{admin} || $permissions{group_edit});
    2012     $row{delgrp} = ($permissions{admin} || $permissions{group_delete});
    2013     push @grouplist, \%row;
    2014   }
    2015   $page->param(grouptable => \@grouplist);
     1981  my $glist = getGroupList($dbh, (childlist => $childlist, curgroup => $curgroup,
     1982        filter => ($filter ? $filter : undef), startwith => ($startwith ? $startwith : undef),
     1983        sortby => $sortby, sortorder => $sortorder) );
     1984
     1985  $page->param(grouptable => $glist);
    20161986} # end listgroups()
    20171987

  • trunk/templates/grpman.tmpl

    r178 r314  
    4040<TMPL_IF name=grouptable>
    4141<TMPL_LOOP name=grouptable>
    42 <tr class="row<TMPL_VAR name=bg>">
     42<tr class="row<TMPL_IF __odd__>1<TMPL_ELSE>0</TMPL_IF>">
    4343        <td align="left"><TMPL_IF edgrp><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=edgroup&amp;gid=<TMPL_VAR NAME=groupid>"><TMPL_VAR NAME=groupname></a><TMPL_ELSE><TMPL_VAR NAME=groupname></TMPL_IF></td>
    4444        <td><TMPL_VAR name=pgroup></td>
    4545        <td><TMPL_VAR name=nusers></td>
    4646        <td><TMPL_VAR name=ndomains></td>
     47        <td><TMPL_VAR NAME=nrevzones></td>
    4748<TMPL_IF delgrp>
    4849        <td align="center"><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=delgrp&amp;id=<TMPL_VAR NAME=groupid>"><img src="images/trash2.png" alt="[ Delete ]" /></a></td>
Note: See TracChangeset for help on using the changeset viewer.