Legend:
- Unmodified
- Added
- Removed
-
trunk/DNSDB.pm
r312 r314 42 42 &getZoneCount &getZoneList 43 43 &addGroup &delGroup &getChildren &groupName 44 &getGroupCount &getGroupList 44 45 &addUser &updateUser &delUser &userFullName &userStatus &getUserData 45 46 &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount … … 64 65 &getZoneCount &getZoneList 65 66 &addGroup &delGroup &getChildren &groupName 67 &getGroupCount &getGroupList 66 68 &addUser &updateUser &delUser &userFullName &userStatus &getUserData 67 69 &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount … … 2147 2149 return $groupname if $groupname; 2148 2150 } # 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. 2160 sub 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 2185 sub 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 2149 2223 2150 2224 -
trunk/dns.cgi
r313 r314 1947 1947 my $childlist = join(',',@childgroups); 1948 1948 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) ) ); 1955 1951 1956 1952 # fill page count and first-previous-next-last-all bits … … 1969 1965 1970 1966 # 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'); 1973 1969 fill_colheads($sortby, $sortorder, \@cols, \%colnames); 1974 1970 … … 1983 1979 $sortby = 'g2.group_name' if $sortby eq 'parent'; 1984 1980 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); 2016 1986 } # end listgroups() 2017 1987 -
trunk/templates/grpman.tmpl
r178 r314 40 40 <TMPL_IF name=grouptable> 41 41 <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>"> 43 43 <td align="left"><TMPL_IF edgrp><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&page=edgroup&gid=<TMPL_VAR NAME=groupid>"><TMPL_VAR NAME=groupname></a><TMPL_ELSE><TMPL_VAR NAME=groupname></TMPL_IF></td> 44 44 <td><TMPL_VAR name=pgroup></td> 45 45 <td><TMPL_VAR name=nusers></td> 46 46 <td><TMPL_VAR name=ndomains></td> 47 <td><TMPL_VAR NAME=nrevzones></td> 47 48 <TMPL_IF delgrp> 48 49 <td align="center"><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&page=delgrp&id=<TMPL_VAR NAME=groupid>"><img src="images/trash2.png" alt="[ Delete ]" /></a></td>
Note:
See TracChangeset
for help on using the changeset viewer.